§15 Использование стандартных функций

15.1. Функции в Excel

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

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

Чаще всего употребляют функцию для суммирования значений ячеек. Эта функция вынесена на панель инструментов в виде кнопки Автосумма: .
Чтобы воспользоваться данной кнопкой, нужно установить курсор в ячейку, в которой должен быть результат. Затем нажать на саму кнопку. Excel автоматически попытается определить диапазон значений для суммирования. Если выделенный диапазон не устраивает пользователя, то нужно изменить выделение, затем нажать клавишу Enter или кнопку .

Рядом с кнопкой Автосумма находится значок выпадающего списка, с помощью которого можно ввести еще несколько часто встречающихся функций (пример 15.3). Используются эти функции аналогично функции вычисления суммы (пример 15.4).

15.2. Категории функций

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

Список всех категорий функций можно увидеть в окне Вставка функции (пример 15.5), если выполнить одно из следующих действий:

  1. Нажать на кнопку на вкладке Формулы или в строке формул.
  • Выбрать ссылку Другие функции в выпадающем списке кнопки Автосумма.
  • Выбрать ссылку Вставить функцию в выпадающем списке любой категории.

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

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

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

  1. В поле Поиск функции введем описание: сумма квадратов.
  2. Просмотрим рекомендованные функции.
  3. Нужная функция: СУММКВ(число1; число2; …) (пример 15.8).

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

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

Пример 15.11*. Результаты решения задачи на олимпиаде по информатике зависят от количества пройденных тестов. Каждому участнику ставят 1, если соответствующий тест пройден, и 0 — в противном случае. Известно количество баллов за каждый тест. Вычислить результат каждого участника.

Для вычисления количества баллов Галкиной Веры, полученных за задачи, можно воспользоваться функцией СУММПРОИЗВ, вычисляющей сумму произведений соответствующих значений диапазонов. В нашем случае необходимо вычислить сумму произведений диапазона C3:G3 на C5:G5.
Результат записывается в ячейку H5. Ссылки на ячейки третьей строки фиксированные, поскольку баллы за тесты одинаковы для всех участников. Затем формулу из ячейки H5 копируем для всех участников.

Часто открывающееся окно Аргументы функции закрывает расчетную часть электронной таблицы. Возле каждого поля для ввода аргумента есть кнопка , позволяющая свернуть окно (пример 15.12).
Пример 15.1. Функции в Excel.
Пример 15.2. Вычисление значения выражения.
Найти значение выражения

Пусть значение переменной x хранится в ячейке A2, а значение переменной y — в ячейке B2. Формула для
вычисления выражения в Excel: =КОРЕНЬ((A2+B2)/A2/B2)+(SIN(A2))^2+(COS(B2))^2

Пример 15.3. Функции из выпадающего списка Автосумма.

Пример 15.4. Вычисление минимального, максимального и среднего значения температуры за 7 дней января.

Пример 15.5. Окно Вставка функции.

Пример 15.6. Список категорий функций.

Пример 15.7. Описание функции ДЕНЬНЕД

Пример 15.8. Поиск функции.

Пример 15.9. Возможные аргументы функции СУММКВ.

Пример 15.10. Ошибочный ввод аргументов.

Справка по этой функции ОК Отмена В ячейке записано отрицательное число, корень из него не вычисляется.

Пример 15.11*. Решение задачи. Исходная таблица:

Выбираем функцию СУММПРОИЗВ. Поля в окне аргументов функции заполняются следующим образом:

Результат:

Пример 15.12. Свернутое окно Аргументы функции.

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

 
1.  Что может быть аргументом функции в Excel?
2.  Какой знак служит разделителем для нескольких аргументов функции?
3.  Для чего предназначена кнопка Автосумма?
4.  Какие функции можно выбрать из выпадающего списка Автосумма?
5.  Какие категории функций в Excel вы можете назвать?
6.  В какой категории находятся все функции Excel?
7.  Как открыть окно Вставка функции?
8.  Как использовать поиск функции?
9.  Как можно вводить аргументы функций?

Упражнения

1.   По таблице Высота некоторых известных зданий определите высоту самого высокого здания и среднюю высоту зданий в списке.

2. Результаты соревнований по бегу и прыжкам представлены в виде таблицы. Определите лучшие результаты в беге и прыжках (лучший результат в беге — минимальное время в секундах, а в прыжках — максимальная длина в метрах).

3. Создайте таблицу для перевода градусов в радианы. Функция для перевода — Радианы из категории Математические.

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

5. Используя функции ДЕНЬНЕД, ДНИ, ДНЕЙ360 и СЕГОДНЯ из категории Дата и время, определите, в какой день недели вы родились и сколько дней прошло со дня вашего рождения. (Найдите в описании отличие функций ДНИ и ДНЕЙ360.)

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

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

7. Создайте таблицу для решения квадратного уравнения.