3. 4. Функции

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

Для введения функции в ячейку необходимо выделить ячейку для формулы и либо ввести формулу с функцией вручную, либо:

1) вызвать Мастер функций с помощью команды Функция меню Вставка или кнопки ;

2) в диалоговом окне Мастер функций, выбрать тип функции в поле Категория, затем функцию в списке Функция.

3) щелкнуть кнопку ОК.

4) в полях Число1, Число2 и т. д. следующего окна ввести аргументы функции (числовые значения или ссылки на ячейки);

5) чтобы указать аргументы, можно щелкнуть кнопку , находящуюся справа от поля, и выделить мышью ячейки, содержащие аргументы функции; для выхода из этого режима следует щелкнуть кнопку , которая находится под строкой формул;

6) щелкнуть кнопку ОК.

В электронной таблице Excel представлены функции следующих групп:

• математические, предназначенные для выполнения различных математических операций;

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

• статистические, используемые для обработки множеств случайных величин;

• финансовые, позволяющие выполнять вычисления, характерные для финансовых расчетов;

• текстовые, предназначенные для выполнения операций над текстовыми строками;

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

Полный перечень функций с их описаниями можно найти в документации на табличный процессор Excel или воспользоваться для этой цели системой помощи.

Каждая функция имеет свое уникальное имя и в общем виде обращение к функции может быть записано следующим образом:

имя_функции (список_аргументов)

Количество аргументов у разных функций может быть различным, что определяется назначением функции. Аргументы в списке отделяются друг от друга точкой с запятой. В качестве аргументов функции, в зависимости от ее назначения, могут использоваться числовые или текстовые константы, ссылки на ячейки и блоки, выражения. Допускается вложенность одной функции в другую, т. е. использование в качестве аргумента одной функции другой или той же самой (непосредственно или в выражениях).

Перечислим некоторые функции, которые используются чаще всего:

• ABS(выражение) – возвращает абсолютную величину арифметического выражения;

• МАКС(выражение 1; выражение 2; ...) – возвращает значение максимального из аргументов (в данной функции и в ряде других в качестве аргументов могут использоваться и ссылки на блоки – в этом случае каждая ячейка блока рассматривается как отдельный аргумент);

• МИН(выражение 1; выражение 2; ...) – возвращает значение минимального из аргументов;

• ОКРУГЛ(выражение, число_разрядов) – возвращает значение выражения (арифметического), округленное до указанной точности, определяемой значением второго аргумента. Если значение аргумента число_разрядов положительно, то оно определяет число десятичных разрядов, которые требуется оставить в округляемом значении; если отрицательно, то – количество нулей в младших цифрах целой части округляемого значения; если равно нулю, то результат округляется до целого;

• СРЗНАЧ(выражение 1; выражение 2; ...) – возвращает среднее арифметическое всех аргументов;

• СУММ(выражение 1; выражение 2; ...) – возвращает сумму всех аргументов;

• ЦЕЛОЕ(число) – возвращает ближайшее к числу снизу целое значение;

• ГОД(число) – возвращает полный номер года (например, из даты 21.11.98 результатом, возвращаемым функцией, будет значение 1998). Напомним, что значения типа "дата" есть частный случай числового значения;

• МЕСЯЦ, ДЕНЬ – действие функций аналогично функции ГОД;

• СЕГОДНЯ() – возвращает текущую дату в формате даты;

• ДАТА(год, месяц, день) – возвращает число, представляющее дату в коде даты – времени Microsoft Excel;

• ЗНАК – определяет знак числа. Возвращает 1, если число положительное, ноль (0), если число равно 0, и –1, если число отрицательное.

Использование функций позволяет упрощать формулы и расчеты в целом. Так, в электронном документе, приведенном на рис. 5, в качестве формулы, заносимой в ячейку D7 для подсчета суммарной заработной платы, можно использовать такую:

=СУММ(D3:D6)

Примечание: вставить в ячейку функцию суммирования СУММ можно с помощью кнопки .

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

• ЕСЛИ(логическое_выражение; выражение 1; выражение 2) – возвращает в качестве результата значение выражения 1, если логи­ческое_выражение истинно, и выражения 2 в противном случае.

Логическое_выражение – это в простейшем случае два арифметических выражения, связанных одной из операций отношения (>, <, >=, <=, =, <>). Результатом логического выражения является одно из двух возможных значений – либо ИСТИНА, если логическое выражение справедливо, либо ЛОЖЬ в противном случае. Например, логическое выражение

СУММ(B3:F8)/3>10

будет истинным, если значение арифметического выражения СУММ(B3:F8)/3 больше 10, и ложным во всех остальных случаях. Возможно формирование и более сложных логических выражений, примеры которых будут рассмотрены далее.

	A	B	C	D
1	Ф. И. О.	Оклад	Стаж работы (лет)	Зарплата
2	Иванов И. П.	300	3	321,00
3	Петров П. А.	500	12	575,00
4	Мольков В. А.	800	0,5	800,00
5	Яшин Ю. Я.	400	7	460,00
6			Итого:	2156,00

Рис. 7. Пример модифицированной ведомости на зарплату, где требуются вариантные расчеты

Проиллюстрируем использование функции ЕСЛИ. Модифицируем еще раз документ, представленный на рис. 3. Пусть процент надбавок зависит от стажа работы и составляет 0%, если стаж менее 1 года, 7% при стаже от 1 года до 5 лет и 15% при стаже более 5 лет (рис. 7).

Для выполнения расчета зарплаты по новым правилам необходимо поместить в ячейку D2, а затем скопировать в остальные ячейки столбца D следующую формулу:

=ЕСЛИ(C2<1; B2; ЕСЛИ(C2<5; B2*1,07; B2*1,15))

Этим примером одновременно демонстрируется возможность вложения одной функции в другую.

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

1. логическое отрицание (НЕ);

2. логическое умножение, конъюнкция (И);

3. логическое сложение, дизъюнкция (ИЛИ).

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

Таблица истинности

Значения операндов		Результат операции
A	B	не A	A и B	A или B
ЛОЖЬ	ЛОЖЬ	ИСТИНА	ЛОЖЬ	ЛОЖЬ
ИСТИНА	ЛОЖЬ	ЛОЖЬ	ЛОЖЬ	ИСТИНА
ЛОЖЬ	ИСТИНА	ИСТИНА	ЛОЖЬ	ИСТИНА
ИСТИНА	ИСТИНА	ЛОЖЬ	ИСТИНА	ИСТИНА

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

• НЕ(логическое_выражение) – возвращает в качестве результата значение ЛОЖЬ, если логическое_выражение имеет значение ИСТИНА, и значение ИСТИНА в противном случае;

• И(логическое_условие 1; логическое_условие 2; ...) – возвращает в качестве результата логическое значение ИСТИНА, если все аргументы имеют значение ИСТИНА. В противном случае результатом будет ЛОЖЬ;

• ИЛИ(логическое_условие 1; логическое_условие 2; ...) – возвращает в качестве результата логическое значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА. В противном случае результатом будет ЛОЖЬ.

Для примера использования логических функций рассмотрим документ, предназначенный для определения фамилий абитуриентов, зачисляемых в вуз после сдачи вступительных экзаменов. Абитуриент зачисляется в том случае, когда он не имеет неудовлетворительных оценок и сумма набранных баллов не менее проходного балла или сумма баллов на единицу меньше проходного (полупроходной балл), но математика сдана на пять (рис. 8).

	A	B	C	D	E	F	G
1	Фамилия	Экзаменационные оценки			Итог		Проходной
2	абитуриента	матем.	физика	сочин.	сдачи		балл
3	Арефьев К. Р.	4	5	3	Зачислен		12
4	Борисов Г. Н.	5	5	2	-
5	. . .	. . .	. . .	. . .	. . .

Рис. 8. Документ, предназначенный для определения фамилий зачисляемых абитуриентов

Возможны несколько вариантов формулы, которую следует занести в ячейку E3 для получения нужного результата. Например:

=ЕСЛИ(И(МИН(B3:D3)>2; ИЛИ(СУММ(B3:D3)>=$G$3; И(СУММ(B3:D3)=$G$3–1; В3=5))); "Зачислен"; "–")

или

=ЕСЛИ(ИЛИ(И(МИН(B3:D3)>2; СУММ(B3:D3)>=$G$3); И(В3=5;СУММ(B3:D3)=$G$3–1)); "Зачислен"; "–")

Вариантные расчеты выполняются не только с помощью перечисленных функций – существуют и другие возможности. Примером может служить функция СУММЕСЛИ, позволяющая рассчитать сумму только тех из множества значений, которые удовлетворяют некоторому условию (рис. 9).

	A	B	C	D	E
1	10	20	30	40	50
2	7	3	12	27	3

=СУММЕСЛИ(A2:E2; 3; A1:E1) = 70 =СУММЕСЛИ(A2:E2; "3") = 6 =СУММЕСЛИ(A2:E2; ">3"; A1:E1) = 80

Рис. 9. Примеры использования функции СУММЕСЛИ

В общем виде функция выглядит так: