8. Использование в формулах встроенных функций Excel

8.1. Вводить встроенные функции Excel в формулу можно непосредственным набором названия функции в строке формул, например,

=SIN(A4)

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

=СУММ(А1;В2) – сумма значений в ячейках А1 и В2.

Аргументом функции может быть блок ячеек (диапазон), например,

=СУММ(А1:В2) – сумма значений в четырех ячейках блока А1:В2.

8.2. Также для ввода функций можно использовать Мастер функций, который вызывается командой Вставить функцию с вкладки Формулы или пиктограммой f_x в строке формул.

По умолчанию, пользователю предоставляется список последних 10 вызванных функций. Если нужной функции нет, то нужно изменить категорию функций с 10 недавно использовавшихся на Полный алфавитный перечень. В алфавитном перечне сначала идут функции, обозначенные латинскими буквами (SIN, др.), затем «русскоязычные» (СУММ, СРЗНАЧ, МАКС, МИН и др.). Если точно знать класс функции, то можно выбирать не Полный алфавитный перечень, а конкретную категорию, например, логические (ЕСЛИ, И, ИЛИ, НЕ и др.).

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

Заканчивается ввод встроенной функции щелчком по кнопке ОК в окне Аргументы функции или нажатием клавиши <Enter> на клавиатуре.

8.3. Аргументами функции могут быть:

Компоненты	Примеры
Числовые данные	=SIN(20)
Ссылки на ячейки	=SIN(F3) =СУММ(А2;В3) =МАКС(А2;В3;D5)
Ссылки на блоки ячеек (диапазоны)	=СУММ(А2:В3) =МАКС(А2:В3)
Другие встроенные функции Excel	=СУММ(ABS(А2;ABS(В3)) =МАКС(SIN(А2);COS(В3))

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

Пример 1:

Введите в ячейки блока А2:А7 числовые данные, первое и последнее число блока сделайте отрицательным, остальные - положительными. В ячейках В2, В3 вызовите с помощью Мастера функций встроенную функцию расчета максимума МАКС, задайте аргументы так, чтобы в ячейках В2, В3 получились соответственно формулы:

=МАКС(A2;A7) - (два отдельных аргумента – ссылки на ячейки, выбираются щелчком мыши каждая);

=МАКС(A2:A7) - (один аргумент – блок ячеек, зачерчивается мышью при нажатой левой кнопке).

В ячейке В2 рассчитывается и выводится наибольшее из значений чисел в двух ячейках: А2 и А7.

В ячейке В3 рассчитывается и выводится наибольшее из числовых данных всего блока из шести ячеек А2:А7.

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

Обратите внимание, что некоторые функции, такие как СУММ – сумма, СРЗНАЧ – среднее значение, - обозначаются русскими буквами, а некоторые, такие как ABS – модуль, SIN – синус – латинскими. Точное название каждой встроенной функции можно выписать из списка функций, вызываемого кнопкой f_x - Вставка функции.

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

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

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

а) набирать названия функций вручную в строке формул.

б) Сначала вызвать Мастер функций пиктограммой f_x – Вставка функции и выбрать первую функцию в формуле, затем ввести численные аргументы и аргументы-ссылки на другие ячейки. Для ввода аргумента-функции нужно поставить курсор в место ввода очередной функции и нажать маленький треугольник слева от строки формул.

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

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

Пример 2:

Поставьте курсор в ячейку В4. Вызовите с помощью Мастера функций встроенную функцию МАКС. В открывшемся окне поставьте курсор в строку первого аргумента, нажмите черный треугольник слева от строки формул и выберите функцию вычисления модуля числа ABS. Аргументом функции ABS сделайте ссылку на ячейку A2. Нажмите пиктограмму ОК. В строке формул в полученной формуле поставьте курсор после первого аргумента функции МАКС перед круглой скобкой, наберите знак разделителя - точку с запятой, нажмите черный треугольник слева от строки формул и выберите снова функцию ABS, с аргументом из ячейки A7. В ячейке В4 должна получиться формула:

=МАКС(ABS(A2);ABS(A7))

В ячейке В4 рассчитывается и выводится наибольшее по модулю число из двух ячеек А2 и А7.

8.7. При вводе формул удобно вводить ссылки на ячейки не набором их имен, а щелчком мыши по соответствующей ячейке, а диапазоны (блоки) ячеек зачерчивать мышью при нажатой левой кнопке.

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

Для устранения этого неудобства нужно сделать следующее:

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

б) После этого можно без помех щелкнуть по нужной ячейке или зачертить диапазон (блок) ячеек.

в) Для возврата к конструированию функции нужно щелкнуть по пиктограмме с красной стрелкой в свернутом окне Аргументы функции.

Заканчивается работа с Мастером функций нажатием экранной кнопки ОК в окне Аргументы функции или нажатием клавиши <Enter> на клавиатуре.

8.8. Суммирование содержимого ячеек, вычисление максимума, минимума или среднего значения встречается в практических задачах очень часто. Поэтому для этих операций предусмотрена специальная команда Автосумма на вкладке Формулы.

Для ее использования нужно поставить курсор в ячейку, в которой будет вычисляться сумма (максимум, среднее и др.), выбрать команду Автосумма (треугольник справа от команды с выбором нужного варианта) на вкладке Формулы. Затем зачертить мышью прямоугольный блок ячеек и закончить ввод формулы нажатием клавиши <Enter>. Аналогичного результата можно добиться, используя встроенную функцию СУММ (МАКС, СРЗНАЧ и др.).

Задание 8. 1) Откройте чистый лист рабочей книги, назовите его Функции. Введите положительные числовые значения в ячейки А1, А3, А5, А7, А9. Постройте с помощью Мастера функций в ячейке С1 формулу расчета минимального значения из 5 заполненных числами ячеек. Измените знак числа в ячейке А1, проследите за изменением значения в ячейке С1. Замените в формуле в ячейке С1 аргументы А1, А3 и А7 соответственно на ABS(A1), ABS(A3) и ABS(A7), проследите за изменением значения в ячейке С1.

2) Поместите в ячейку С3 формулу расчета минимального значения по блоку из 9 ячеек с А1:А9. Сравните результат со значением в ячейке С1. Введите в ячейки А2, А4, А6, А8 текст, дату, время, проценты, введите 0 (ноль) в ячейки А1, А3, А5, А7, А9. Изучите изменения значений в ячейках С1 и С3.

3) Заполните числовыми значениями блок ячеек D3:F8. В ячейках Е10, Е11 рассчитайте сумму значений по указанному блоку двумя способами: с помощью Автосуммы и используя встроенную функцию СУММ().

4) В ячейке Е12 и Е13 рассчитайте среднее арифметическое по блоку D3:F8. двумя способами: с помощью Автосуммы и используя встроенную функцию СРЗНАЧ()

5) Сохраните файл Технология.