Praktikum_2008

Рис. 3.11

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

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

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

(a1b1c1+a2b2c2+...).

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

мер, МОПРЕД(А1:С3).

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

111

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

Рис. 3.12

Рис. 3.13

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

112

Задание

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

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

n−1

L = ∑ (xi+1 − xi )2 +(( yi+1 − yi )2

i=1

n

2P = ∑xk ( yk +1 − yk −1 )

k =1

Контроль расчета площади выполните по формуле:

n

2P = ∑yk (xk −1 − xk +1 ).

k =1

Постройте график полигона по координатам его вершин.

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

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

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

113

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

ние; значение_ если_ истина; значение_ если_ ложь).

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

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

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

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

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

Задание

Столбцы «От 20 до 30» и «От 30 до 40» рассчитайте самостоятельно. Выведите фамилии в случае попадания в диапазон и выведите пробел в случае не попадания в него. Используйте логическую функцию И для определения попадания в диапазон. Напри-

мер, И(E4>=20;E4<30).

Рис. 3.14

114

Рис. 3.15

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

шем нормы — «Надо похудеть».

Соответствующий пример приведен на рис. 3.16 и 3.17. Здесь формулы набираются в ячейках E3 и F3, а затем копи-

руются в диапазон E4:F7.

Рис. 3.16

115

Рис. 3.17

Задание

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

2.Добавить в табл. 3.16 столбец «пол», содержащий указания

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

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

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

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

116

117

Рис. 3.19

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

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

118

ков содержимое ячеек 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.

119

Рассмотрим ещё один пример использования вложенных логических функций — расчёт заработной платы. Структура таблицы представлена на рис. 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 - Пенсионный фонд - Льготы. Совокупный доход = Совок. Доход за предыдущий период +

Обл.Сумма.

120