§ 7 Расчёты с использованием функций Excel 2000

Для выполнения сложных расчётов про­грамма включает около 400 различных функций, некоторые из которых будут описаны ниже. Нужную функцию можно вызвать, указав её имя и список аргументов в информационной строке, а также выбрать из списка, который выводится на экран либо при помощи команды Вставка—› Функция, либо нажатием соот­ветствующей кнопки на стандартной панели инструментов. Далее откры­вается окно диалога, в левой части которого перечислены категории функций (Математические, Статистические, Финансовые и т.д.), а в правой части – названия функций, относящихся к выбранной категории. В последнем случае пользователю помогает «Мастер функций».

Математические функции

Одна из наиболее часто используемых математических функций суммирование. Вызов функции суммирования с использованием мастера функций сопровождается появлением окна, которое даёт возможность правильно ввести функцию и записать её аргументы. Аналогичные окна используются и при вызове других функций Excel 2000. После ввода всех аргументов нажимают кнопку «Готово». Допускается вводить слагаемые, либо как числа, либо как адреса ячеек, в которых эти числа содержатся. На стандартной панели инструментов имеется кнопка автосуммирования, помеченная знаком «S», упрощающая процедуру вызова указанной функции. При автосуммировании аргументами функции СУММ() являются ячейки, расположенные либо сверху, либо слева от выделенной ячейки и содержащие числовые данные.

Среди прочих математических функций чаще других используются:

СУММКВ(...) - сумма квадратов, аргументами, которой являются числа или адреса ячеек таблицы. Функция возвращает значение суммы квадратов аргументов.

СУММПРОИЗВ(...) - сумма произведений. Её аргументами являются массивы чисел или адресов ячеек таблицы. функция СУММПРОИЗВ(...) возвращает сумму произведений элементов массивов-аргументов, имеющих одинаковые номера (a1b1c1+a2b2c2+...).

Расчёт величины определителя квадратной матрицы производится с использованием функции МОПРЕД. Аргументом является массив ячеек, в котором находятся элементы матрицы. Например, МОПРЕД(А1:С3).

Среди математических функций следует отметить также округление, перевод градусной меры углов в радианную и радианной в градусную, тригонометрические функции и т.д, выполняемые в соответствии с обычными математическими формулами.

Пример с использованием функций показан в таблице 8 и в таблице 9, где вычисляются расстояния от начала координат до ряда точек, заданных координатами Х и У.

Т аблица 8.

Таблица 9.

Здесь формула вычисления расстояния вводится в ячейку D3, а затем копируется в ячейки D4:D9.

В следующем столбце рассчитайте самостоятельно расстояние от i-ой точки (i – ваш номер в журнале) до каждой точки.

Функции даты и времени

Другая группа часто используемых функций - функции даты и времени. Самая простая из этой группы функций - функция «Сегодня()», возвращающая дату, определяемую системными часами компьютера. Эта функция не имеет аргументов. Формат даты можно выбирать при помощи команды Формат —› Ячейки. Если в ячейке вместо даты появится бессмысленная комбинация символов, следует поменять формат или увеличить ширину ячейки. Другая аналогичная функция - «ТДАТА()». В отличие от «Сегодня()», «ТДАТА()» наряду с текущей датой возвращает и время. Имеются функции, возвращающие год, месяц, день недели и т.д. Для определения интервала между двумя датами достаточно из конечной даты вычесть начальную. Например, «08.04.97»-«06.04.97»—›2. Если даты поменять местами, ответ получится отрицательный. Допускается использование адресов ячеек, содержащих исходную информацию.

Логические функции.

Наиболее распространённой логической функцией является функция «ЕСЛИ». По своим возможностям она соответствует условному оператору в обычных алгоритмических языках. Форма записи этой функции следующая ЕСЛИ(логическое_ выражение; значение_ если_ истина; значение_ если_ ложь).

Используются также функции:

логическое сложение ИЛИ(логическое_ выражение1; логическое_ выражение2; ..;логическое_ выражениеN);

логическое умножение И(логическое_ выражение1; логическое_ выражение2;…; логическое_ выражениеN);

отрицание НЕ(логическое_ выражение).

Ниже приведен пример с использованием логической функции ЕСЛИ, в котором отбираются лица, достигшие 18 лет и не достигшие 18 лет (таблица 10, 11).

Таблица 10

Таблица 11

Столбцы «От 20 до 30» и «От 30 до 40» рассчитать самостоятельно. Вывести фамилии в случае попадания в диапазон и вывести пробел в случае не попадания в него. Использовать логическую функцию «И» для определения попадания в диапазон. Например, И(E4>=20;E4<30).

Допускается использование вложенных логических функций.

Пусть заданы рост и вес человека. В качестве нормального примем вес равный 0,9 от роста в сантиметрах минус 100. При весе, отличающемся от нормы не более чем на 2 кг, будем выдавать сообщение ”Норма”, при весе, отличающемся от нормы более чем на 20 кг, – “Обратиться к врачу”, в остальных случаях при весе меньшем нормы - “Надо поправиться”, при весе большем нормы - ”Надо похудеть”,.

Соответствующий пример приведен в таблицах 12 и 13.

Таблица 12

Таблица 13

Здесь формулы набираются в ячейках E3 и F3, а затем копируются в диапазон E4:F7.

В качестве еще одного примера рассмотрим расчёт суммы налога на стоимость имущества, которое переходит в порядке наследования и дарения, для уплаты наследниками или другими лицами. Ставки налога на имущество, переходящее в порядке наследования, в зависимости от его стоимости и степени родства наследника приведены в таблице 14. Структура таблицы расчёта налога приведена в таблицах 15 и 16.

Таблица 14

	Наследование		Дарение
0) Стоимость имущества менее 850 ММОТ
Вне зависимости от степени родства		Ставка налога равна 0%		От 80 до 850 -ММОТ Детям , - 3 % стоимости родителям им-ва превыш. 80-кр. ММОТ Другим -10 % ст-сти им-ва, физичес- превыш. 80-кр. ким ли- ММОТ цам
1) Стоимость имущества от 850 до 1700 ММОТ
Наследники 1- й очереди (дети, родители)		5% стоимости имущества, превышающего 850 ММОТ		23,1-кр. ММОТ + 7% стоимости имущества, превышающей.. 850- ММОТ
Наследники 2-й очереди (дед, бабка, братья, сёстры)		10% стоимости имущества, превышающего 850 ММОТ		77 ММОТ + 20 % стоимости имущества, превышающей. 850 ММОТ
Другие наследники		15% стоимости имущества, превышающего 850 ММОТ
2) Стоимость имущества от 1700 до 2550 ММОТ
Наследники 1-й очереди		42,5 ММОТ +10% стоимости имущества, превышающего 1700 ММОТ		82,6 ММОТ +11% стоимости имущества, превышающей. 1700 ММОТ
Наследники 2-й очереди		85 ММОТ +20% стоимости имущества, превышающего 1700 ММОТ		247. ММОТ +30% стоимости имущества, превышающей 850. ММОТ.
Другие наследники		170 ММОТ +30% стоимости имущества, превышающего 1700 ММОТ
3) Стоимость имущества превышает 2550 ММОТ
Наследники 1-й очереди		127,5 ММОТ +15% стоимости имущества, превышающего 2550 ММОТ		176,1 ММОТ+ 15% стоимости имущества, превышающей 2550 ММОТ
Наследники 2-й очереди		255 ММОТ +30% стоимости имущества, превышающего 2550 ММОТ		502. ММОТ + 40 % стоимости имущества, превышающей 2550 ММОТ
Другие наследники		425 ММОТ +40% стоимости имущества, превышающего 2550 ММОТ

Таблица15

Таблица 16

Здесь для перевода стоимости имущества из ММОТ в рубли в ячейку В5 вводится формула =А5*В$4 и копируется в ячейки В6 и В7.

Для вычисления величины налога на второе граничное значение стоимости имущества (1700 ММОТ) для наследников первой очереди, в ячейку D6 вводится формула =D5+($B6-$B5)*C5/100. Для вычисления величины налога на следующее граничное значение стоимости имущества (2250 ММОТ) для наследников первой очереди, эта формула копируется из ячейки D6 в ячейку D7. Для вычисления соответствующих граничных величин налога для наследников 2 очереди и других наследников содержимое ячеек D6:D7 копируется последовательно в F6:F7 и H6:H7.

Для вычисления налога на наследство для наследников первой очереди с любой конкретной стоимости имущества, помещаемой в ячейку В9, в D9 вводится соответствующая формула (см. таблицу 16).

Для вычисления налога для наследников второй очереди и других наследников, формула из ячейки D9 копируется последовательно в ячейки F9 и H9. Формула для получения налога на наследство в зависимости от номера очереди наследников вводится в ячейку В11.

Рассмотрим ещё один пример использования вложенных логических функций - расчёт заработной платы. Структура таблицы представлена в табл. 17.

Столбец «Сумма1» в таблице вычисляется по формуле:

Сумма1=Оклад/25.6*Рабочие дни.

Сумма2=Сумма1+Другие начисления.

Удержание в пенсионный фонд составляет 1% от «Суммы 2».

Для расчёта льгот на подоходный налог используются данные из таблицы учёта льгот, количество детей и ММОТ.

Т аблица 17

Таблица 18

Общий вид формулы вычисления льгот:

Л = если(Сумма2 – ПФ + СД <= 15000;2*MМОТ + 2*Д*ММОТ; если(Сумма2 – ПФ + СД <=50000; MМОТ+ Д*ММОТ;0)) , где

ПФ – налог в пенсионный фонд,

СД – совокупный доход за предыдущий период.

Формула вычисления льгот в табл. 18:

=ЕСЛИ(F3-G3+J2) <= 15000;2*F$19+2*F$20*F$19; ЕСЛИ (F3-G3+J2<=50000;F$19*1+F$19*F$20*1;0))

Облагаемая сумма = Сумма2-Пенсионный фонд- Льготы.

Совокупный доход = Обл. Сумма + Совок. Доход.

Подоходный налог рассчитывается на основе шкалы подоходного налога (табл.18), совокупного дохода и облагаемой суммы.

В данном примере ставка ПН и ОС меняется при СД+ОС>50000 рублей и при СД+ОС>150000 рублей. При этом возможны следующие шесть вариантов исчисления ПН.

Если СД (за предыдущий период)+ОС<=50000 руб., ПН составляет 12% от ОС.

Если СД больше 150000 руб., ПН составляет 30% от ОС.

Если СД >50000 и СД+ОС<=150000, ПН составляет 20% от ОС.

Если СД <=50000 и СД+ОС<=150000, то ПН с части СД+ОС, превышающей 50000, берётся по ставке 20%, а с остальной части ОС – по ставке 12%.

Если СД >50000 и СД+ОС>150000, то ПН с части СД+ОС, превышающей 150000, берётся по ставке 30%, а с остальной части ОС – по ставке 20%.

Наконец, в последнем из возможных случаев (его можно уже формально не записывать), если СД <50000 и СД+ОС>150000, то ПН с части ОС, где СД+ОС больше 150000, берётся по ставке 30%, ПН с части от 50 до 150000 берётся по ставке 20%, а с остальной части ОС по ставке 12%.

Формула расчёта подоходного налога в табл. 18 :

=ЕСЛИ(J2+I3<=50000;I3*0,12;ЕСЛИ(J2>=150000;I3*0,3;ЕСЛИ(И(J2>50000;J2+I3<=150000);I3*0,2;ЕСЛИ(И(J2<=50000;(J2+I3)<=150000);(J2+I3-50000)*0,2+(I3-(J2+I3-50000))*0,12;ЕСЛИ(И(J2>50000;J2+I3>150000);(J2+I3-150000)*0,3+(I3-(J2+I3-150000))*0,2;(J2+I3-150000)*0,3+100000*0,2+(I3-(J2+I3-150000)-100000)*0,12))))).

Формулы расчета показаны в табл. 18.