Информатика Синергия. информатика. Литература по теме Тема Алгоритмы и программы Вопрос Понятие алгоритма
 Скачать 7.74 Mb.
|
 Рис. 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) выберем категорию Дата и время, а затем функцию Сегодня и щелкнем кнопку ОК. 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. Выбор функции ПЛТ в диалоговом окне Вставка функции Аргументами этой функции являются: · Ставка – процентная ставка, приведенная к одному периоду между платежами. Например, при годовой ставке 12 % и ежемесячном погашении долга, значение аргумента Ставка равно 12 % / 12 = 1 %. · Кпер – общее число выплат по кредиту. · ПС – приведенная сумма, т.е. сумма кредита. Дополнительные аргументы БС и Тип можно для простоты не учитывать. Будем использовать ту же таблицу и те же данные, что и в предыдущем примере. Только заполним исходные данные в следующей строке. 1. Вычисления начнем, установив курсор в ячейке ежемесячного платежа Е3. 2. Щелкнем кнопку fx (Вставить функцию) в строке формул. 3. В диалоговом окне Вставка функции выберем категорию Финансовые и функцию ПЛТ (см. рис. 74). Щелкнем кнопку ОК. 4. В открывшемся окне Аргументы функции заполним: · в поле Ставка: С3/12; · в поле Кпер: B3*12; · в поле ПС: А3. 5. Щелкнем ОК, чтобы закрыть окно. 6. В активной ячейке Е3 появится результат расчета ежемесячного платежа. Знак «минус» означает, что это наши расходы. 7. Поставим курсор в ячейке D3 и рассчитаем сумму возврата путем умножения ежемесячного платежа на общее число платежей: =Е3*В3*12. Окончательный вид таблицы представлен на рис. 75.  Рис. 75. Таблица расчета платежей по кредиту Вопрос 6. Логические переменные, функции и выражения. Логические переменные (A, B, C, …X, Y, Z) могут принимать только одно из двух возможных значений – ИСТИНА или ЛОЖЬ. Логические функции имеют в качестве аргументов логические переменные и их выражения и тоже могут принимать значения ИСТИНА или ЛОЖЬ. Значения логических функций задаются таблицами истинности. Для вычисления значений логических функций в Excel имеются встроенные функции, объединенные в категорию Логические. Логическое выражение – это выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае – значение ЛОЖЬ. Функция ИЛИ (логическое сложение). Функция ИЛИ принимает значение ИСТИНА, если хотя бы один из аргументов («слагаемых») имеет значение ИСТИНА, и только в единственном случае, когда все «слагаемые» имеют значение ЛОЖЬ, функция ИЛИ принимает значение ЛОЖЬ (см. таблицу 3). Таблица 3.
Создадим таблицу истинности для случая двух аргументов функции ИЛИ на листе Excel. 1. Заполним ячейки диапазона А1:В5 так же, как на приведенной выше таблице 3. 2. «Встанем» курсором в ячейку С3, куда будет помещен результат для первой строки исходных данных. 3. Щелкнем кнопку fx (Вставить функцию) в строке формул. 4. В диалоговом окне Вставка функций выберем категорию Логические, а в ней функцию ИЛИ. Закроем окно кнопкой ОК. 5. В открывшемся окне Аргументы функции заполним поле Логическое_значение1, щелкнув курсором в ячейке А2. 6. Переместим курсор в поле Логическое_значение2 и щелкнем курсором в ячейке В2. Закроем окно кнопкой ОК. 7. В активной ячейке результата С2 появится значение функции ИЛИ (ИСТИНА). 8. Зацепив маркер заполнения активной ячейки, протянем его вниз до конца таблицы (рис. 76).  Рис. 76. Таблица истинности функции ИЛИ Функция И (логическое умножение) и функция НЕ (логическое отрицание). Функция И принимает значение ИСТИНА в единственном случае, когда все аргументы («сомножители») имеют значение ИСТИНА, а во всех остальных случаях принимает значение ЛОЖЬ. Таблица истинности функции И для случая двух аргументов выглядит следующим образом.
Таблица истинности функции И создается в Excel аналогично описанному выше, только в диалоговом окне Вставка функции выбирается функция И. Следует заметить, что функции ИЛИ и И могут иметь любое число аргументов. Функция НЕ (логическое отрицание) имеет всего один аргумент. И если аргумент имеет значение ИСТИНА, функция НЕ принимает значение ЛОЖЬ. Логическая функция ЕСЛИ. Функция ЕСЛИ принимает одно из двух заданных пользователем значений (числовых, текстовых или логических) в зависимости от истинности аргумента, который обычно представляет собой логическую переменную или целое выражение (математическое или логическое). Например, если возраст человека больше 18 лет, он совершеннолетний, в противном случае несовершеннолетний. Создадим в качестве упражнения новую таблицу для записи данных 6 человек разного возраста, причем величина возраста рассчитывается по формуле, приведенной в разделе Исчисление временных интервалов (Тема 11, Вопрос 4) (рис. 77). |