Как использовать функцию АГРЕГАТ в Excel для уточнения вычислений

Функция АГРЕГАТ в Excel позволяет выполнять вычисления, игнорируя скрытые строки, ошибки или другие функции, которые появляются в данных. Она похожа на функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, но предоставляет больше возможностей для вычислений и дает вам больше контроля над тем, что вы хотите исключить из вычислений.

Синтаксис AGGREGATE

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

Синтаксис справочной формы

Синтаксис ссылочной формы функции АГРЕГАТ следующий:

=АГРЕГАТ(a,b,c,d)

в котором

• a (обязательно) — число, представляющее функцию, которую вы хотите использовать в расчетах,
• b (обязательно) — число, определяющее, что именно должно игнорироваться при расчете,
• c (обязательно) — диапазон ячеек, к которым будет применена функция, и
• d (необязательно) — первый из 252 дополнительных аргументов, которые определяют дополнительные диапазоны.

Синтаксис формы массива

С другой стороны, если вы работаете с массивами, синтаксис будет следующим:

=АГРЕГАТ(a,b,c,d)

в котором

• a (обязательно) — число, представляющее функцию, которую вы хотите использовать в расчетах,
• b (обязательно) — число, определяющее, что именно должно игнорироваться при расчете,
• c (обязательно) — массив значений, к которым будет применена функция, и
• d — второй аргумент, необходимый для функций массива, таких как LARGE, SMALL, PERCENTILE.INC и других.

Функции и исключения (Аргументы) a и b)

При вводе аргументов a и b в любой из приведенных выше форм синтаксиса у вас будет несколько вариантов выбора.

В таблице ниже показаны различные функции, которые можно использовать при вычислении АГРЕГАТА (аргумент a). Даже если у вас возникнет соблазн ввести имя функции, помните, что этот аргумент должен быть числом, представляющим функцию, которую вы хотите использовать. Функции с 1 по 13 предназначены для использования с синтаксисом ссылочной формы, а функции с 14 по 19 — для использования с синтаксисом формы массива.

В этой таблице перечислены числа, которые вы можете ввести, чтобы исключить определенные значения при создании формулы АГРЕГАТ (аргумент b):

Теперь давайте рассмотрим несколько примеров того, как можно использовать функцию АГРЕГАТ в реальных сценариях.

Пример 1: использование AGGREGATE для игнорирования ошибок

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

Если бы вы использовали только функцию СРЗНАЧ, введя:

=СРЕДНЕЕ(Голы_Игрока[Игры за гол])

в ячейку C1, это вернет ошибку, поскольку указанный диапазон содержит ошибки #DIV/0!.

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

=AGGREGATE(1,6,Голы_Игрока[Игры за гол])

в котором

• 1 (аргумент a) представляет собой функцию СРЗНАЧ,
• 6 (аргумент b) сообщает Excel, что нужно игнорировать ошибки, и
• Player_Goals[Игры за гол] является ссылкой.

Альтернативным способом достижения того же результата было бы использование функции ЕСЛИОШИБКА в столбце D для замены любых ошибок пустым значением.

Пример 2: использование AGGREGATE для игнорирования скрытых строк (ссылка)

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

Один из способов отображения итогов — установить флажок «Total Row» на вкладке Table Design на ленте, что поместит итоги в нижнюю часть таблицы. Однако если вы работаете с большим набором данных, постоянная прокрутка вниз для просмотра итогов может привести к потере времени. Вместо этого рассмотрите возможность размещения итогов в верхней части электронной таблицы за пределами отформатированной таблицы, чтобы они всегда были на виду.

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

Чтобы рассчитать общую сумму, в ячейке C1 введите:

=СУММ(Голы_Игрока[Забитые голы])

Теперь, даже после применения фильтра к одному из столбцов, например, для отображения только игроков, сыгравших 15 игр или более, формула СУММ, которую вы только что применили, по-прежнему будет включать отфильтрованные строки.

Вот где функция AGGREATE спасет день, так как она позволяет вашим вычислениям игнорировать отфильтрованные строки. Фактически, функция AGGREGATE также будет работать, если вы хотите игнорировать строки, которые вы скрыли, щелкнув правой кнопкой мыши заголовок строки и нажав «Скрыть».

В ячейке C2 введите:

=АГРЕГАТ(9,5,Голы_Игрока[Забитые голы])

в котором

• 9 (аргумент a) представляет функцию СУММ,
• 5 (аргумент b) сообщает Excel, что нужно игнорировать скрытые строки, и
• Player_Goals[Забитые голы] является ссылкой.

Теперь обратите внимание, что результат этой формулы отличается от результата формулы СУММ, которую вы использовали в ячейке C1, поскольку она учитывает только отображаемые строки.

​​​​

Пример 3: использование AGGREGATE для игнорирования скрытых строк (массив)

Далее, предположим, вы хотите составить список двух игроков с наибольшим количеством голов, сыгравших 20 игр или меньше.

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

В ячейке C1 введите:

=AGGREGATE(14,5,Player_Goals[Забитые голы],{1;2})

в котором

• 14 (аргумент a) представляет функцию LARGE,
• 5 (аргумент b) сообщает Excel, что нужно игнорировать скрытые строки,
• Player_Goals[Забитые голы] это массив значений, и
• {1; 2} сообщает Excel, что вы хотите вернуть наибольшее (1) и вторые по величине значения (2) в отдельных строках (;).

Когда вы нажмете Enter, обратите внимание, что результатом станет заполненный массив, охватывающий ячейки C1 и C2, поскольку вы указали Excel вернуть верхний два значения.

Теперь отфильтруйте столбец «Сыграно игр», чтобы включить только тех игроков, которые сыграли 20 игр или меньше, и увидите, что результат формулы АГРЕГАТ, которую вы ввели ранее, изменится, чтобы игнорировать скрытые строки.

Что следует учитывать при использовании функции АГРЕГАТ

Прежде чем приступить к использованию функции АГРЕГАТ в своих собственных книгах Excel, уделите время и обратите внимание на следующие моменты:

• Функция АГРЕГАТ Excel работает только с вертикальными диапазонами, а не с горизонтальными. Поэтому, когда вы ссылаетесь на горизонтальный диапазон, АГРЕГАТ не будет игнорировать строки в скрытых столбцах.
• Аргумент c в формуле AGGREGATE не может быть одной и той же ячейки или диапазона ячеек на нескольких листах (также известных как 3D-ссылки).
• Хотя функция AGGREGATE — отличный способ обойти ошибки в вычислениях, не привыкайте игнорировать ошибки вообще. Они существуют не просто так и могут помочь вам устранить проблемы с данными.
• Форма массива функции AGGREGATE не будет игнорировать скрытые строки, вложенные промежуточные итоги или вложенные агрегаты, если аргумент массива включает вычисление.

Еще один способ скрыть строки в таблицах Excel, чтобы функция АГРЕГАТ включала только отображаемые данные, — это вставка срезов, интерактивных кнопок, нажимая на которые, можно значительно упростить фильтрацию.