Материал: Литература по теме Тема Алгоритмы и программы Вопрос Понятие алгоритма
Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам
Аналогичное явление можно наблюдать и при сдвиге по столбцам. Например, вставим ту же скопированную формулу в ячейку Е3. Новое выражение будет иметь вид =В3+D3. То есть изменились и имена исходных столбцов, и номер исходной строки в соответствии со сдвигом. И в этом случае смысл вычисления потерян, поскольку в измененных адресах ячеек хранятся совсем другие данные.
Поэтому в ряде случаев необходимо зафиксировать исходные адреса ячеек в копируемых и переносимых формулах. Например, для создания таблицы умножения следует зафиксировать имя столбца (левого), содержащего значения первого сомножителя, и номер строки (верхней), содержащей значения второго сомножителя. Для фиксации адреса в формулу вставляется знак $ перед именем того столбца или номером той строки, которые в формуле не должны изменяться. Так, для создаваемой таблицы умножения формула для первой (результатной) ячейки таблицы (1х1) будет выглядеть так: =$A2*B$1 (рис. 65).
Рис. 65. Фиксация адресов ячеек в формуле таблицы умножения
Далее следует завершить ввод формулы и протянуть курсором маркер заполнения по всем ячейкам таблицы умножения вниз и вправо.
Адреса ячеек, содержащие знак $, называются абсолютными ссылками в отличие от обычных адресов, которые называются относительными ссылками.
Встроенные или стандартные функции MS Excel.
Табличный процессор MS Excel имеет огромное количество готовых стандартных функций (математических, логических, финансовых и др.), которые можно использовать при проведении вычислений. Чтобы вызвать необходимую стандартную функцию следует установить курсор в ту ячейку, куда будет помещен результат вычисления, и щелкнуть кнопку fx (Вставить функцию) в строке формул. При этом открывается диалоговое окно Вставка функции (рис. 66).
Рис. 66. Диалоговое окно Вставка функции
Все встроенные функции распределены по категориям: математические, статистические, финансовые и т.д. (см. рис. 66). После выбора категории в одноименном поле окна Вставка функции (в рассматриваемом примере выбрали категорию Математические), в разделе
Выберите функцию открывается список имеющихся функций выбранной категории. После выбора требуемой функции (например, десятичный логарифм) нажатие ОК открывает окно Аргументы функции, в котором следует ввести необходимые значения аргументов (рис. 67).
Рис. 67. Диалоговое окно Аргументы функции
После ввода значений аргументов функции результат вычисления показывается в окне Аргументы функции (см. рис. 67), а после закрытия окна кнопкой ОК получаем результат вычисления в активной ячейке.
Исчисление временных интервалов в Excel.
Вычисление значений временных интервалов в Excel имеет некоторые особенности. Так, даты хранятся в виде последовательных номеров. Например, день 1 января 1900 г. имеет номер 1, а 1 января 2017 г. – 42736, интервал между этими датами составляет 42736 дней. При вводе в ячейку таблицы значения некоторой даты (в формате даты), Excel переводит ее в число (дней), равное разности между вводимой датой и 01.01.1900 г. То есть вычисляет число дней, прошедших с 01.01.1900 г. до вводимой даты, и хранит в памяти именно это число. Его можно увидеть, если ячейке, содержащей дату, присвоить числовой формат.
Чтобы вычислить возраст человека (в днях) в Excel надо из сегодняшней даты вычесть дату рождения. Если полученный результат поделить на число дней в году, получим значение возраста в годах. Точно также вычисляют стаж работы и другие временные интервалы.
Значение сегодняшней даты дает встроенная функция Сегодня из категории Дата и время в диалоговом окне Вставка функции (рис. 68).
Рис. 68. Функция Сегодня в категории Дата и время
Продемонстрируем использование функции Сегодня на примере вычисления возраста сотрудников во вновь созданной таблице, содержащей следующие столбцы: №, ФИО (Фамилия и инициалы), Дата рождения, Возраст. Ячейкам столбца Возраст назначим формат Числовой. Выполним следующие действия:
1. Щелкнем курсором в ячейке первого сотрудника в столбце «Возраст», чтобы сделать ее активной.
2. Щелкнем кнопку fx (Вставить функцию) в строке формул.
3. В открывшемся диалоговом окне Вставка функции (см. рис. 68) выберем категорию Дата и время, а затем функцию
Рис. 67. Диалоговое окно Аргументы функции
После ввода значений аргументов функции результат вычисления показывается в окне Аргументы функции (см. рис. 67), а после закрытия окна кнопкой ОК получаем результат вычисления в активной ячейке.
Исчисление временных интервалов в Excel.
Вычисление значений временных интервалов в Excel имеет некоторые особенности. Так, даты хранятся в виде последовательных номеров. Например, день 1 января 1900 г. имеет номер 1, а 1 января 2017 г. – 42736, интервал между этими датами составляет 42736 дней. При вводе в ячейку таблицы значения некоторой даты (в формате даты), Excel переводит ее в число (дней), равное разности между вводимой датой и 01.01.1900 г. То есть вычисляет число дней, прошедших с 01.01.1900 г. до вводимой даты, и хранит в памяти именно это число. Его можно увидеть, если ячейке, содержащей дату, присвоить числовой формат.
Чтобы вычислить возраст человека (в днях) в Excel надо из сегодняшней даты вычесть дату рождения. Если полученный результат поделить на число дней в году, получим значение возраста в годах. Точно также вычисляют стаж работы и другие временные интервалы.
Значение сегодняшней даты дает встроенная функция Сегодня из категории Дата и время в диалоговом окне Вставка функции (рис. 68).
Рис. 68. Функция Сегодня в категории Дата и время
Продемонстрируем использование функции Сегодня на примере вычисления возраста сотрудников во вновь созданной таблице, содержащей следующие столбцы: №, ФИО (Фамилия и инициалы), Дата рождения, Возраст. Ячейкам столбца Возраст назначим формат Числовой. Выполним следующие действия:
1. Щелкнем курсором в ячейке первого сотрудника в столбце «Возраст», чтобы сделать ее активной.
2. Щелкнем кнопку fx (Вставить функцию) в строке формул.
3. В открывшемся диалоговом окне Вставка функции (см. рис. 68) выберем категорию Дата и время, а затем функцию
Сегодня и щелкнем кнопку ОК.
4. Заметим, что функция Сегодня не имеет аргументов, что подтверждается содержимым диалогового окна Аргументы функции, в котором нужно просто подтвердить ввод, щелкнув ОК (рис. 69).
Рис. 69. Подтверждение ввода функции Сегодня в диалоговом окне «Аргументы функции»
5. В активной ячейке, а также в строке формул появится запись выражения =Сегодня(). Щелкнем курсором в строке формул после скобок. Дальнейшие действия по созданию окончательного математического выражения будем производить в строке формул.
6. Вставим знак «минус» с клавиатуры и щелкнем в ячейке, соответствующей дате рождения первого сотрудника. В строке формул появится следующая запись выражения: =Сегодня()-С2. Это выражение дает возраст сотрудника в днях.
7. Полученный результат надо поделить на число дней в году (365,25 с учетом високосного года). Окончательное математическое выражение должно иметь следующий вид: =(Сегодня()-С2)/365,25.
8. Завершим ввод щелкнув кнопку Ввод (галочка) в строке формул. В ячейке D2 появится окончательный результат – возраст сотрудника в годах. Убедитесь, что ячейкам столбца Возраст назначен формат Числовой.
9. Чтобы заполнить все ячейки столбца Возраст надо скопировать формулу вычисления возраста из активной ячейки D2 и вставить ее во все остальные ячейки столбца. Для этого следует подцепить курсором и протащить вниз до конца столбца маркер заполнения активной ячейки D2 (рис. 70).
Рис. 70. Таблица сотрудников с вычислением возраста
Точно таким же способом вычисляется и стаж работы сотрудника, исходя из даты поступления на работу.
Вопрос 5. Примеры решения вычислительных задач.
Расчет заработной платы.
Рассмотрим метод расчета заработной платы при сдельной системе оплаты труда. Пусть R работников выполняют одинаковую работу по изготовлению продукции. Плановое задание – Р изделий в месяц. За эту работу выплачивается базовая ставка зарплаты В рублей. Конечная оплата Z производится пропорционально фактической выработке V изделий за месяц, т.е. Z = B*V/P. Подоходный налог N составляет 13 % от начисленной зарплаты, поэтому на руки работник получает (Z – N) рублей.
4. Заметим, что функция Сегодня не имеет аргументов, что подтверждается содержимым диалогового окна Аргументы функции, в котором нужно просто подтвердить ввод, щелкнув ОК (рис. 69).
Рис. 69. Подтверждение ввода функции Сегодня в диалоговом окне «Аргументы функции»
5. В активной ячейке, а также в строке формул появится запись выражения =Сегодня(). Щелкнем курсором в строке формул после скобок. Дальнейшие действия по созданию окончательного математического выражения будем производить в строке формул.
6. Вставим знак «минус» с клавиатуры и щелкнем в ячейке, соответствующей дате рождения первого сотрудника. В строке формул появится следующая запись выражения: =Сегодня()-С2. Это выражение дает возраст сотрудника в днях.
7. Полученный результат надо поделить на число дней в году (365,25 с учетом високосного года). Окончательное математическое выражение должно иметь следующий вид: =(Сегодня()-С2)/365,25.
8. Завершим ввод щелкнув кнопку Ввод (галочка) в строке формул. В ячейке D2 появится окончательный результат – возраст сотрудника в годах. Убедитесь, что ячейкам столбца Возраст назначен формат Числовой.
9. Чтобы заполнить все ячейки столбца Возраст надо скопировать формулу вычисления возраста из активной ячейки D2 и вставить ее во все остальные ячейки столбца. Для этого следует подцепить курсором и протащить вниз до конца столбца маркер заполнения активной ячейки D2 (рис. 70).
Рис. 70. Таблица сотрудников с вычислением возраста
Точно таким же способом вычисляется и стаж работы сотрудника, исходя из даты поступления на работу.
Вопрос 5. Примеры решения вычислительных задач.
Расчет заработной платы.
Рассмотрим метод расчета заработной платы при сдельной системе оплаты труда. Пусть R работников выполняют одинаковую работу по изготовлению продукции. Плановое задание – Р изделий в месяц. За эту работу выплачивается базовая ставка зарплаты В рублей. Конечная оплата Z производится пропорционально фактической выработке V изделий за месяц, т.е. Z = B*V/P. Подоходный налог N составляет 13 % от начисленной зарплаты, поэтому на руки работник получает (Z – N) рублей.
1. Создадим «заготовку» таблицы на листе Excel, указав произвольные исходные данные: Р = 300 шт., В = 30000 р. (рис. 71).
Рис. 71. Таблица расчета зарплаты
2. В активной ячейке D4 введем формулу для вычисления зарплаты: =$D$1*C4/$B$1 и завершим ввод кнопкой-галочкой в строке формул. Следует заметить, что в формуле использован знак $ для фиксации адресов ячеек, которые не должны изменяться при копировании.
3. Подцепим курсором маркер заполнения активной ячейки и протянем его вниз до конца столбца таблицы.
4. Поместим курсор в ячейку Е4 и введем формулу вычисления подоходного налога =D4*13 %. Завершим ввод.
5. Маркером заполнения скопируем введенную формулу в остальные ячейки столбца.
6. Поместим курсор в ячейку F4 и введем формулу вычисления суммы к выдаче: =D4–E4. Завершим ввод и протянем маркер заполнения до конца столбца.
Округление числовых значений.
Рассмотрим пример использования встроенных математических функций, позволяющих округлять числовые значения до ближайших величин вверх или вниз. Обратимся еще раз к созданной ранее таблице сотрудников и произведем округление вниз возраста. Заметим, что величина возраста в таблице выражена нецелым числом, полученным в результате расчета. Иногда в некоторых организациях (например, в медицинских учреждениях) учитывается возраст «полных лет». Для его нахождения мы и воспользуемся встроенной функцией.
1. Добавим новый столбец в ранее созданную таблицу (см. рис. 70, вставив его после столбца Возраст и назовем Полных лет (формат числовой).
2. Установим курсор в ячейку первого сотрудника в новом столбце.
3. Щелкнем курсором кнопку fx (Вставить функцию) в строке формул.
4. В открывшемся диалоговом окне Вставка функции выберем категорию Математические, а в ней функцию ОкруглВниз и щелкнем кнопку ОК.
5. В диалоговом окне Аргументы функции курсор находится в поле Число. Чтобы ввести округляемое число, щелкнем курсором в ячейке таблицы, содержащей возраст первого сотрудника.
6. Перенесем курсор в поле Число разрядов и введем 0 (ноль). Тем самым мы округляем до ближайшего целого числа.
7. Щелкнем кнопку ОК, чтобы закрыть диалоговое окно. В активной ячейке таблицы появится результат округления (рис. 72).
Рис. 72. Результат округления дробного числа до целого
8. Подцепим курсором маркер заполнения активной ячейки и протянем его вниз до конца столбца таблицы.
Расчет платежей по кредиту.
Часто возникает задача рассчитать сумму возврата и ежемесячных платежей по взятому в банке кредиту, исходя из годовой процентной ставки. Рассмотрим эту задачу сначала на основе формулы простых процентов.
Если обозначить S сумму кредита, Pr – годовая процентная ставка (годовой процент), Sвоз – сумму возврата и T – срок кредита, то можно написать следующее очевидное равенство
Sвоз= S + S*Pr*T,
где
* означает знак умножения.
Другими словами, сумма возврата равна сумме кредита плюс накопленные проценты с этой суммы за каждый год.
1. Создадим таблицу Excel с такими столбцами: Сумма кредита, Срок (лет), Годовой процент, Сумма возврата, Ежемесячный платеж.
2. Установим ячейкам столбцов форматы (денежный, числовой, процентный).
3. Введем произвольные числовые значения, например, S = 1000000 р., Т = 5 лет, Pr = 15 %.
4. В активную ячейку D2 вставим выражение =A2+A2*B2*C2. Завершим ввод, например, клавишей Tab.
5. В активной ячейке Е2 произведем деление найденного значения суммы возврата на количество месяцев: =D2/(12*Т) и получим значение ежемесячного платежа (рис. 73).
Рис. 73. Таблица расчета платежей по кредиту (формула простых процентов)
Существует и другой способ вычисления платежей по кредиту на основе формулы сложных процентов, учитывающей тот факт, что в процессе погашения долга его сумма уменьшается, следовательно, и проценты за пользование кредитом берутся с меньшей суммы. Для расчета платежей по кредиту в этом случае имеется специальная встроенная функция ПЛТ в категории Финансовые (рис. 74).
Рис. 74. Выбор функции ПЛТ в диалоговом окне Вставка функции
Аргументами этой функции являются:
Источник: https://tut-files.ru/previewfile/13430