Материал: 1480

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

=SIN(2).

Пробелы между круглой скобкой и именем функции или аргументом не допускаются. Если пробел установить, в ячейку будет возвращено ошибочное значение #ИМЯ? Некоторые функции не имеют аргумента, например ПИ, ЛОЖЬ, ИСТИНА. Тем не менее, круглые скобки после имени такие функции должны содержать, например:

=Н12+ПИ().

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

=СТЕПЕНЬ(Е56;4/9).

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

=СУММПРОИЗВЕД(А1:А4;А5:В6;D24:E26).

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

=ABS(ГРАДУСЫ(ASIN(F890)+ACOS(2)+R456*ПИ()-ATAN(2/3))).

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

Аргументы некоторых функций должны иметь определённый тип. Например, аргументом функции КОРЕНЬ может быть только положительное число, или аргументом функции ATAN число в диапазоне от /2 до + /2.

При вводе функций с клавиатуры, без использования мастера функций, обратите внимание на то, что при ссылке на ячейку должен быть включен латинский шрифт. В противном случае в ячейку будет возвращено ошибочное значение #ИМЯ?

71

Ввод функций можно производить с использованием диалогового окна Мастер функций – шаг 1 из 2. Вызвать это окно можно с помощью пункта меню Вставка-Функция…

или кнопки панели инструментов Стандартная fx .

Это диалоговое окно содержит два поля с вертикальными линейками прокрутки: в левом поле расположены Категории (группы) функции, в правом

– собственно Функции. После выбора требуемой функции и нажатии кнопки OK, появляется второе окно мастера функций. Это окно содержит по одному полю для каждого аргумента. Число полей увеличивается автоматически после ввода очередного аргумента, если функция имеет несколько аргументов. Справа от каждого поля отображается текущее значение каждого аргумента, а внизу под последним аргументом вычисленное значение функции. Круглые скобки и знак равенства вводятся автоматически.

Ошибочные значения. Я уже дважды в пособии упоминал ошибочное значение #ИМЯ?. Ошибочные значения в ячейке появляются тогда, когда Microsoft Excel не может вычислить формулу. Таких ошибочных значений семь:

1.#ДЕЛ/0! Попытка деления на ноль. Может возникать и в том случае, в делителе формулы имеется ссылка на пустую ячейку.

2.#ИМЯ? В формуле используется имя, отсутствующее в списке имён диалогового окна Присвоение имени. Кроме того, ссылка на ячейку введена не латинским шрифтом или строка символов не заключена в двойные кавычки, или между круглой скобкой и функцией или аргументом установлен пробел.

3.#ЗНАЧ! Введена математическая формула, которая ссылается на текстовое значение.

4.#ССЫЛКА! Отсутствует диапазон ячеек, на который ссылается формула. Возможно, вы его удалили и забыли об этом.

5.#Н/Д Нет данных для вычислений. После внесения данных вместо ошибочного значения в ячейку будет возвращён результат вычислений.

6.#ЧИСЛО! Задан неправильный аргумент функции. Такое ошибочное значение возникает и в том случае, если значение формулы слишком велико или слишком мало и не может быть представлено на листе.

7.#ПУСТО! В формуле представлено пересечение диапазонов, но эти диапазоны не имеют общих ячеек.

72

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

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

Арифметические функции

СУММ. Эта функция суммирует аргументы. Максимальное количество аргументов 30. Синтаксис этой функции следующий:

=СУММ(аргумент1;аргумент2;…аргумент30).

Аргументом этой функции может быть число (константа), ссылка на ячейку или диапазон ячеек (массив), вложенная функция, формула, то есть всё, что возвращает числовое значение. СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения, значения ошибок.

=СУММ(25,89; В23;F15:L89;КОРЕНЬ(А11/5);С45*8+90).

На панели инструментов Стандартная имеется кнопка автосуммирования с символом . Выберите пустую ячейку справа от строки или снизу от столбца с числовыми значениями и щёлкните по этой кнопке. Microsoft Excel предложит вам диапазон для суммирования, выделив его подвижной пунктирной рамкой. Если этот диапазон устраивает вас, для вычисления щёлкните ещё раз по этой кнопке или нажмите клавишу Enter. Если вы хотели просуммировать другой диапазон, выделите его известным вам способом. Необходимый диапазон можно выделить сразу.

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

ПРОИЗВЕД возвращает произведение аргументов. Синтаксис этой функции:

=ПРОИЗВЕД(аргумент1;аргумент2;…аргумент30).

Она похожа на функцию СУММ:

73

её аргументом может быть число (константа), ссылка на ячейку или диапазон ячеек (массив), вложенная функция, формула, то есть всё, что возвращает числовое значение;

она игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения, значения ошибок;

максимальное количество аргументов 30. В массиве или ссылке на диапазон ячеек учитываются только числа.

СУММПРОИЗВЕД перемножает элементы заданных массивов и возвращает в ячейку сумму этих произведений. Синтаксис функции:

=СУММПРОИЗВЕД(массив1;массив2;…массив30).

Максимальное количество массивов – 30. Пи этом размерность всех массив должна быть одинаковой. Если это не так, то функция возвращает значение ошибки #ЗНАЧ!. Нечисловые элементы массивов функция воспринимает как нулевые.

Функции =СУММПРОИЗВЕД(A1:C12;D1:F12) и =СУММ(A1:C12*D1:F12) возвращают один и тот же результат.

СУММКВ возвращает сумму квадратов аргументов. Синтаксис:

=СУММКВ(аргумент1;аргумент2;…аргумент30).

Максимальное количество аргументов 30. Можно использовать отдельный массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой. Например, =СУММКВ(А4:В15).

В Microsoft Excel имеются функции СУММКВРАЗН, СУММСУММКВ, СУММКВРАЗН. Нетрудно догадаться о том, что возвращают в ячейку эти функции. Уравнения для них имеют следующий вид:

СУММРАЗНКВ = (x2 – y2),

СУММСУММКВ = (x2 + y2),

СУММКВРАЗН = (x – y)2,

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

Примеры этих функций:

= СУММРАЗНКВ({2;4;7;9};{3;5;8;10}),

74

= СУММКВРАЗН(A1:C12;D1:F12).

КОРЕНЬ возвращает положительный квадратный корень числа и имеет следующий синтаксис:

=КОРЕНЬ(аргумент).

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

ФАКТР возвращает факториал числа. Факториал числа – это произведение всех положительных целых чисел, начиная от 1 до заданного числа. Например, 5 факториал или 5! означает 1*2*3*4*5. Синтаксис функции:

=ФАКТР(аргумент).

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

ЧИСЛКОМБ определяет число возможных комбинаций для заданного числа элементов. Синтаксис функции следующий:

=ЧИСЛКОМБ(число;число_выбраных).

Аргумент1 число – общее количество элементов, аргумент 2 – количество элементов в каждой комбинации. Например, для определения количества команд с 11 игроками в команде может быть создано из 17 игроков используется формула

=ЧИСЛКОМБ(17;11).

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

дробные числовые аргументы усекаются до целых, то есть десятичные знаки отбрасываются без округления;

если любой из аргументов не число, то функция возвращает ошибочное значение #ИМЯ?;

если аргумент 1 отрицательное число, или аргумент 2 отрицательное число, или аргумент 1< аргумента 2, то функция возвращает ошибочное значение #ЧИСЛО!;

число комбинаций определяется следующим образом:

75