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

Удобно строить вложенные логические функции так, чтобы последующие ЕСЛИстояли на месте значение_ если_ ложь.

Например, ЕСЛИ(логическое_ выражение; значение_ если_ истина; ЕСЛИ(логическое_ выражение; значение_ если_ истина; значение_ если_ ложь)).

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

Соответствующий пример приведен на рис. 3.16 и 3.17.

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

Рис.3.16 Контроль веса

Рис.3.17 Контроль веса с формулами

Задание 5

1. Добавьте в таблицу на рис. 3.15 столбец «Возраст», в котором люди, не достигшие 18 лет, должны характеризоваться как «дети», люди от 18 до 20 лет – как «юноши», от 20 до 30 лет – как «молодые люди», от 30 лет до 60 – как «взрослые» и от 60 лет и старше – как пенсионеры. Добавьте в таблицу на рис. 3.15 ряд записей так, чтобы в каждую категорию попал хотя бы один человек.

Соответствующие характеристики возраста должны быть получены с помощью вложенной логической функции с условиями попадания в диапазон (логическое умножениеИ). Функцию нужно ввести в ячейку I3 и скопировать по столбцу.

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

2. Добавьте в таблицу 3.17 столбец G1 «пол», содержащий указания М – мужчины и Ж – женщины. Расчет нормального веса выполнить с учетом пола: если G1=”М” то 0,9(Рост – 100), иначе 0,8(Рост – 100). Рекомендации тоже выполните с учетом пола: если М, рекомендации в соответствии с таблицей 3.17, иначе «норма» это норма +4, «обращение к врачу» это ABS(вес-норма) >25.

В качестве еще одного примера рассмотрим расчет суммы налога на стоимость имущества, которое переходит в порядке наследования или дарения.

Ставки налога на имущество, переходящего в порядке наследования или дарения, зависят от стоимости имущества, определяемой в единицах МРОТ (минимальный размер оплаты труда) и степени родства наследника (рис. 3.18).

	Наследование	Дарение
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 МРОТ

Рис.3.18 Ставки налога на имущество, переходящего в порядке
наследования или дарения

Структура таблицы расчета налога на наследство приведена на рис. 3.19 и 3.20.

Рис.3.19 Расчет налога на наследство

Здесь столбцы A, C, E и G заполняются в соответствии с данными таблицы на рис. 3.18. В ячейку В4 вводится действующая величина МРОТ (в 2012 г. величина МРОТ 4611 руб.). Для перевода стоимости имущества из МРОТ в рубли в ячейку В5 вводится формула =А5*В$4 и копируется в ячейки В6 и В7. Налог на первое граничное значение (850 МРОТ) равен 0 (D5).

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

Рис.3.20 Расчета налога на наследство с формулами

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

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

Для контроля правильности расчетов полезно ввести в B9 стоимость имущества, соответствующую второй (1700 МРОТ) либо третьей (2250 МРОТ) границам. При этом величина налога, вычисленная по общей формуле, должна равняться величине налога, вычисленного для соответствующего граничного значения. Например, для стоимости имущества, равной 2250 МРОТ, должны совпадать налоги, определенные в ячейках D9 и D7, F9 и F7, H9 и H7.

Задание 6

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

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

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

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

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

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

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

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

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

где

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

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

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

=ЕСЛИ((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 – Пенсионный фонд - Льготы.

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

В методических целях подоходный налог рассчитывается на основе шкалы подоходного налога (рис. 3.22), совокупного дохода и облагаемой суммы. (После 2002 г. реальный подоходный налог составляет 13% от облагаемой суммы).

Рис.3.21Расчет заработной платы

Рис.3.22 Расчет заработной платы с формулами

В данном примере ставка ПН и ОС меняется при СД+ОС>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%.

Формула расчета подоходного налога в табл. на рис. 3.22 :

=ЕСЛИ(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))))).

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

Контролем правильности расчетов в табл. на рис. 3.21 служит равенство суммы подоходных налогов по месяцам (K17) и подоходного налога на сумму облагаемых сумм (K16).

Формулы массива

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

А В С D Е Рис.3.23 Динамика цен на персональные компьютеры

В таблице на рис. 3.23 проиллюстрирована динамика цен на персональные компьютеры различных моделей. Во 2-м и 3-м столбцах приведены цены 2011 и 2012 годов, а в последних двух столбцах–изменение цен в абсолютных величинах и в процентах. Во всех ячейках столбца Dпроизводятся одинаковые арифметические операции. При этом можно не вписывать формулу в каждую ячейку, а выделить D2:D5 и записать формулу один раз в активную ячейку. Такая формула называется формулой массива. После набора формулы массива надо нажать одновременно3 клавиши «Ctrl»–»Shift»–»Enter». Тогда будут выполнены преобразования данных всех ячеек массива. То же в столбце Е.

В информационной строке формулы массива автоматически заключаются в фигурные скобки. Изменение части результирующего массива недопустимо.

В простейшем случае, проиллюстрированном выше, формула массива решает практически ту же задачу, что и автозаполнение ячеек (копирование). Однако, например, в матричных операциях формулы массива незаменимы.

Умножение матриц

Для выполнения этой операции необходимо

1. Ввести в соответствующие поля массивы ячеек, содержащих матрицы–сомножители.

2. Выделить односвязную область, в которой должен располагаться результат.

3. Вызвать (или вписать в информационную строку) функцию МУМНОЖ с нужными аргументами.

4. Одновременно нажать клавиши «Ctrl»–»Shift»–»Enter».

Если просто нажать кнопку Готово в окне диалога вызова функции, то на экран будет выведен только первый элемент матрицы–произведения.

Аналогичные действия следует выполнить для определения элементов матрицы, обратной заданной, с использованием функции МОБР.

В качестве примера рассмотрим решение системы линейных уравнений методом обратной матрицы:

Ах = в

х = А^-1в.

Пусть элементы матрицы А (коэффициенты системы уравнений) записаны в диапазоне А1:D4, а элементы вектора B (свободные члены уравнений)–в диапазоне F1:F4 (рис. 3.24).

Для получения значений неизвестных х₁, …, х₄ выделим диапазон H1:H4. Наберем в активной ячейке H1 формулу

= МУМНОЖ(МОБР(A1:D4);F1:F4)

и введем ее одновременным нажатием «Ctrl»–»Shift»–»Enter». В ячейках H1:H4 получим величины х₁, …, х₄.

Рис.3.24 Умножение матриц

Задание 7

1. Решите систему линейных уравнений 5-го порядка, где коэффициенты при неизвестных по главной диагонали равны номеру студента по журналу.

Проверьте правильность решения, вычислив исходные свободные члены по формуле

b=Ax.

Убедитесь, что произведение A·A^-1 даёт единичную матрицу Е.

2. Получите решение той же системы уравнений по формулам Крамера:

где – определитель системы уравнений, составленный из коэффициентов при неизвестных, а –j-й дополнительный минор, т.е. определитель системы, составленный из коэффициентов при неизвестных, где j-й столбец заменен столбцом свободных членов. Убедитесь в совпадении решений системы линейных уравнений полученных методом обратной матрицы и по формулам Крамера.

Порядок решения:

• Ввести матрицу коэффициентов (А1:D4) и свободные члены (F1:F4) системы уравнений.

• Вычислить определитель матрицы в G7 по формуле =МОПРЕД(А1:D4).

• Скопировать матрицу коэффициентов в (А7:D10).

• Заменить первый столбец (А7:А10) свободными членами системы (F1:F4) и вычислить первый дополнительный минор в ячейке G8.

• Вычислить в ячейке G9.

• Скопировать ячейку G9 и вставить (специальная вставка, значение) в ячейку J1.

• Восстановить первый столбец матрицы коэффициентов (А7:А10) по исходным значениям (А1:А4).

• Заменить следующий столбец коэффициентов (В7:В10) свободными членами системы (F1:F4). При этом в G8 и G9 появятся значения, соответствующие ∆₂ и .

• Скопировать и вставить его в J2.

• Аналогично получитьв G9 и вставить в J3-J4 остальные неизвестные.