Расчет заработной платы.Рассмотрим метод расчета заработной платы при сдельной системе оплаты труда. Пусть R работников выполняют одинаковую работу по изготовлению продукции. Плановое задание – Р изделий в месяц. За эту работу выплачивается базовая ставка зарплаты В рублей. Конечная оплата Z производится пропорционально фактической выработке V изделий за месяц, то есть Z = B*V/P. Подоходный налог N составляет 13 % от начисленной зарплаты, поэтому на руки работник получает (Z – N) рублей.1. Создадим «заготовку» таблицы на листе Excel, указав произвольные исходные данные: Р = 300 шт., В = 30000 р. (рис. 96).Рис. 96. Таблица расчета зарплаты2. В активной ячейке D4 введем формулу для вычисления зарплаты: =$D$1*C4/$B$1 и завершим ввод кнопкой-галочкой в строке формул. Следует заметить, что в формуле использован знак $ для фиксации адресов ячеек, которые не должны изменяться при копировании.3. Подцепим курсором маркер заполнения активной ячейки и протянем его вниз до конца столбца таблицы.4. Поместим курсор в ячейку Е4 и введем формулу вычисления подоходного налога =D4*13 %. Завершим ввод.5. Маркером заполнения скопируем введенную формулу в остальные ячейки столбца.6. Поместим курсор в ячейку F4 и введем формулу вычисления суммы к выдаче: =D4–E4. Завершим ввод и протянем маркер заполнения до конца столбца. Округление числовых значений.Рассмотрим пример использования встроенных математических функций, позволяющих округлять числовые значения до ближайших величин вверх или вниз. Обратимся еще раз к созданной ранее таблице сотрудников и произведем округление вниз возраста. Заметим, что величина возраста в таблице выражена нецелым числом, полученным в результате расчета. Иногда в некоторых организациях (например, в медицинских учреждениях) учитывается возраст «полных лет». Для его нахождения мы и воспользуемся встроенной функцией.1. Добавим новый столбец в ранее созданную таблицу (см. рис. 95), вставив его после столбца «Возраст» и назовем «Полных лет» (формат числовой).
2. Установим курсор в ячейку первого сотрудника в новом столбце.3. Щелкнем курсором кнопку fx (Вставить функцию) в строке формул.4. В открывшемся диалоговом окне «Вставка функции» выберем категорию Математические, а в ней функцию ОкруглВниз и щелкнем кнопкуОК.5. В диалоговом окне «Аргументы функции» курсор находится в поле «Число». Чтобы ввести округляемое число, щелкнем курсором в ячейке таблицы, содержащей возраст первого сотрудника.6. Перенесем курсор в поле «Число разрядов» и введем 0 (ноль). Тем самым мы округляем до ближайшего целого числа.7. Щелкнем кнопку ОК, чтобы закрыть диалоговое окно. В активной ячейке таблицы появится результат округления (рис. 97). Рис. 97. Результат округления дробного числа до целого 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*Т) и получим значение ежемесячного платежа (рис. 98).
Рис. 98. Таблица расчета платежей по кредиту (формула простых процентов) Существует и другой способ вычисления платежей по кредиту на основе формулы сложных процентов, учитывающей тот факт, что в процессе погашения долга его сумма уменьшается, следовательно, и проценты за пользование кредитом берутся с меньшей суммы. Для расчета платежей по кредиту в этом случае имеется специальная встроенная функция ПЛТ в категории Финансовые (рис. 99).Рис. 99. Выбор функции ПЛТ в диалоговом окне «Вставка функции»Аргументами этой функции являются: Ставка – процентная ставка, приведенная к одному периоду между платежами. Например, при годовой ставке 12 % и ежемесячном погашении долга, значение аргумента Ставка равно 12 % / 12 = 1 %. Кпер – общее число выплат по кредиту. ПС – приведенная сумма, то есть сумма кредита. Дополнительные аргументы БС и Тип можно для простоты не учитывать. Будем использовать ту же таблицу и те же данные, что и в предыдущем примере. Только заполним исходные данные в следующей строке.1. Вычисления начнем, установив курсор в ячейке ежемесячного платежа Е3.2. Щелкнем кнопку fx (Вставить функцию) в строке формул.3. В диалоговом окне Вставка функции выберем категорию Финансовые и функцию ПЛТ (см. рис. 99). Щелкнем кнопку ОК.4. В открывшемся окне Аргументы функции заполним: в поле Ставка: С3/12; в поле Кпер: B3*12; в поле ПС: А3. 5. Щелкнем ОК, чтобы закрыть окно.6. В активной ячейке Е3 появится результат расчета ежемесячного платежа. Знак «минус» означает, что это наши расходы.7. Поставим курсор в ячейкe D3 и рассчитаем сумму возврата путем умножения ежемесячного платежа на общее число платежей: =Е3*В3*12. Окончательный вид таблицы представлен на рис. 100.Рис. 100. Таблица расчета платежей по кредиту
Логические переменные (A, B, C, …X, Y, Z) могут принимать только одно из двух возможных значений – ИСТИНА или ЛОЖЬ. Логические функции имеют в качестве аргументов логические переменные и их выражения и тоже могут принимать значения ИСТИНА или ЛОЖЬ. Значения логических функций задаются таблицами истинности. Для вычисления значений логических функций в Excel имеются встроенные функции, объединенные в категорию Логические.
Логическое выражение – это выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае – значение ЛОЖЬ.
Функция ИЛИ (логическое сложение).
Функция ИЛИ принимает значение ИСТИНА, если хотя бы один из аргументов («слагаемых») имеет значение ИСТИНА, и только в единственном случае, когда все «слагаемые» имеют значение ЛОЖЬ, функция ИЛИ принимает значение ЛОЖЬ (см. таблицу).
X
|
Y
|
X или Y
|
ИСТИНА
|
ИСТИНА
|
ИСТИНА
|
ИСТИНА
|
ЛОЖЬ
|
ИСТИНА
|
ЛОЖЬ
|
ИСТИНА
|
ИСТИНА
|
ЛОЖЬ
|
ЛОЖЬ
|
ЛОЖЬ
|
Создадим таблицу истинности для случая двух аргументов функции ИЛИ на листе Excel:1. Заполним ячейки диапазона А1:В5 так же, как на приведенной выше таблице.2. «Встанем» курсором в ячейку С3, куда будет помещен результат для первой строки исходных данных.3. Щелкнем кнопку f
x (Вставить функцию) в строке формул.4. В диалоговом окне «Вставка функций» выберем категорию Логические, а в ней функцию ИЛИ. Закроем окно кнопкой ОК.5. В открывшемся окне Аргументы функции заполним поле Логическое_значение1, щелкнув курсором в ячейке А2.6. Переместим курсор в поле Логическое_значение2ищелкнем курсором в ячейке В2. Закроем окно кнопкой ОК.
7. В активной ячейке результата С2 появится значение функции ИЛИ (ИСТИНА).8. Зацепив маркер заполнения активной ячейки, протянем его вниз до конца таблицы (рис. 101).
Рис. 101. Таблица истинности функции ИЛИФункция И (логическое умножение) и функция НЕ (логическое отрицание).Функция И принимает значение ИСТИНА в единственном случае, когда все аргументы («сомножители») имеют значение ИСТИНА, а во всех остальных случаях принимает значение ЛОЖЬ. Таблица истинности функции И для случая двух аргументов выглядит следующим образом:
X
|
Y
|
X и Y
|
ИСТИНА
|
ИСТИНА
|
ИСТИНА
|
ИСТИНА
|
ЛОЖЬ
|
ЛОЖЬ
|
ЛОЖЬ
|
ИСТИНА
|
ЛОЖЬ
|
ЛОЖЬ
|
ЛОЖЬ
|
ЛОЖЬ
|
Таблица истинности функции И создается в Excel аналогично описанному выше (раздел), только в диалоговом окне «Вставка функций» выбирается функция И. Следует заметить, что функции ИЛИ и И могут иметь любое число аргументов.Функция НЕ (логическое отрицание) имеет всего один аргумент. И если аргумент имеет значение ИСТИНА, функция НЕ принимает значение ЛОЖЬ.
Задания для самостоятельного выполнения:1. Создать на листе Excel таблицу истинности для функции НЕ.2. Создать на листе Excel таблицы истинности функции ИЛИ и функции И в случае трех и четырех аргументов.
Логическая функция ЕСЛИ.Функция ЕСЛИ принимает одно из двух заданных пользователем значений (числовых, текстовых или логических) в зависимости от истинности аргумента, который обычно представляет собой логическую переменную или целое выражение (математическое или логическое).Например, если возраст человека больше 18 лет, он совершеннолетний, в противном случае несовершеннолетний. Создадим в качестве упражнения новую таблицу для записи данных шести человек разного возраста, причем величина возраста рассчитывается по приведенной в разделе формуле (рис. 102).