Функции рабочего листа Excel

Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

В формулах Excel используются функции. Функции принимают на входе аргументы (в подавляющем большинстве случаев; есть несколько функций без аргументов, об этом ниже) — параметры, ссылки на ячейки и диапазоны — и возвращают на выходе некий результат в соответствии со своим назначением. Например, одна из самых простых и известных функций СУММ / SUM возвращает сумму всех своих аргументов (чисел, введенных вручную, чисел из отдельных ячеек и из диапазонов). В Excel более 500 функций — есть специализированные, используемые редко, есть актуальные для большинства пользователей.

Следующая функция будет возвращать сумму чисел из ячейки B5, диапазона A1:B2 и числа 1200:

=СУММ(B5; 1200; A1:B2)

А следующая функция будет возвращать среднее значение чисел из диапазона B2:B12:

=СРЗНАЧ(B2:B12)

СУММ / SUM и СРЗНАЧ / AVERAGE — пример функций с переменным количеством аргументов (обязательно требуется только один аргумент, чтобы было что суммировать или усреднять, но может быть и больше).

Бывают функции вообще без аргументов: например, ТДАТА / NOW — она возвращает текущие дату и время, а они ни от чего не зависят, у них не может быть параметров. Поэтому у таких функций скобки остаются пустыми ввиду отсутствия аргументов:

=ТДАТА()

Скобки в любом случае нужно указать, чтобы Excel понимал, что ТДАТА — это функция (а не имя диапазона, например).

ВВОД ФУНКЦИЙ

Автосумма

Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.

Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ / SUM).

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

После нажатия Alt + = получаем суммы во всех пустых ячейках выделенного диапазона.

Вместо сочетания клавиш можно нажать кнопку на ленте инструментов на вкладке «Формулы» (Formulas).

Здесь доступны еще несколько базовых функций: среднее арифметическое (СРЗНАЧ / AVERAGE), счетчик (количество чисел; функция СЧЁТ / COUNT), максимум (МАКС / MAX), минимум (МИН / MIN). Чтобы открыть этот список, нужно щелкнуть на стрелку справа от слова «Автосумма» (AutoSum).

Инструменты для ввода функций

В автосумме доступно лишь несколько базовых функций. Что, если мы хотим вставить другую функцию? Здесь есть несколько вариантов.

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

Конечно, можно ввести название функции целиком вручную. Тогда нужно будет открыть скобку. А вот в случае нажатия Tab или щелчка скобка будет открыта автоматически.

Аналогично в Google Таблицах (что в них, что в Excel подсказки работают на том языке интерфейса, который включен в настройках).

При этом в Google Таблицах можно ввести название функции на другом языке, и она все равно будет работать (если вы введете СУММ, а у вас английский язык интерфейса Google Таблиц, то она автоматически преобразуется в SUM), а вот в Excel будет ошибка #ИМЯ? (#NAME?): приложение не распознает функцию на другом языке.

В Excel можно выбрать функцию на вкладке ленты «Формулы» в одной из категорий.

Нажмите на кнопку f_x справа от строки формул, чтобы открыть окно «Вставка функции».

Также его можно открыть с помощью сочетания клавиш Shift + F3.

Отличие в том, что с окном «Вставка функции» вы будете видеть отдельные поля с аргументами функции. Многим, особенно начинающим, пользователям так проще вводить функции.

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

Она тоже может помочь, если знать несколько нюансов.

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

2. Сам список аргументов можно переносить: иногда он мешает при вводе формулы, закрывая данные. Это можно сделать с зажатой левой кнопкой мыши. Главное — тянуть за пустое пространство, а не за названия аргументов.

3. Необязательные аргументы отображаются в квадратных скобках.

4. Если есть многоточие, значит, аргументов может быть больше. Например, у функции СУММПРОИЗВ / SUMPRODUCT (о которой мы поговорим прямо сейчас) могут перемножаться два диапазона, а могут больше.

ФУНКЦИЯ СУММПРОИЗВ / SUMPRODUCT: СУММА ПРОИЗВЕДЕНИЙ

Файл с примером: СУММПРОИЗВ.xlsx

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

Можно сразу получить результат с помощью функции СУММПРОИЗВ / SUMPRODUCT. Эта функция возвращает сумму произведений значений из нескольких (как минимум двух, как в нашем случае) массивов. Аргументы функции — несколько диапазонов одинакового размера, значения из которых нужно попарно перемножить и потом сложить произведения.

=СУММПРОИЗВ(B2:B36;C2:C36)

Каждая пара значений из двух диапазонов перемножается (1600 умножается на 9, 1600 на 6 и так далее), а затем полученные результаты складываются, но эти вычисления не приходится вводить в виде отдельных формул.

Назад: Ссылки на ячейки в формулах

Дальше: Таблицы Excel (Tables)