Функция АГРЕГАТ в Excel
Функция AGGREGATE в Excel возвращает совокупность заданной таблицы данных или списков данных, эта функция также имеет первый аргумент в качестве номера функции, а дополнительные аргументы относятся к диапазону наборов данных, номер функции следует запомнить, чтобы знать, какую функцию использовать .
Синтаксис
Формула AGGREGATE имеет два синтаксиса:
- Справочный синтаксис
= АГРЕГАТ (номер_функции; параметры; ссылка1; ссылка2; ссылка [3],…)
- Синтаксис массива
= АГРЕГАТ (номер_функции; параметры; массив; [k])
Function_num - это число, обозначающее конкретную функцию, которую мы хотим использовать, это число от 1 до 19.
Опция: это также числовое значение от 0 до 7, определяющее, какие значения следует игнорировать при вычислениях.
Ref1, ref2, ref [3]: аргумент при использовании ссылочного синтаксиса, это числовое значение или значения, для которых мы хотим выполнить вычисление, по крайней мере два аргумента являются обязательными, остальные аргументы являются необязательными.
Массив: это массив значений, над которыми мы хотим выполнить операцию, он используется в синтаксисе массива функции AGGREGATE в excel
K: необязательный аргумент и числовое значение, оно используется, когда в Excel используются такие функции, как LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC.
Примеры
Вы можете скачать этот шаблон Excel с функцией AGGREGATE здесь - Шаблон Excel с функцией AGGREGATEПример - №1
Предположим, у нас есть список чисел, и мы вычислим Среднее значение, Счетчик, то есть количество ячеек, содержащих значение, Счетчик - количество непустых ячеек, Максимум, Минимум, произведение и сумму заданных числовых значений. Значения приведены ниже в таблице:
Давайте сначала вычислим среднее значение в строке 9 для всех заданных значений. Для среднего значение function_ num равно
В столбце C указаны все значения, и нам не придется игнорировать какие-либо значения, поэтому мы выберем вариант 4 (ничего не игнорировать)
И выбрав диапазон значений C1: C8 как массив числовых значений
Поскольку ' k' является необязательным аргументом и используется, когда используются такие функции, как LARGE, SMALL в Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC, но в этом случае мы вычисляем среднее значение, поэтому мы будет опускать значение k.
Итак, среднее значение
Аналогично, для диапазона D1: D8 мы снова выберем вариант 4.
Для диапазона E1: E8 ячейка E6 содержит значение ошибки, если мы будем использовать ту же формулу AGGREGATE, мы получим ошибку, но когда используется соответствующий параметр, AGGREGATE в Excel дает среднее значение оставшихся значений без учета ошибки значение в E6.
Чтобы игнорировать значения ошибок, у нас есть вариант 6.
Аналогично, для диапазона G1: G8 мы будем использовать опцию 6 (игнорировать значения ошибок)
Теперь для диапазона H3, если мы поместим значение 64 и скроем третью строку и воспользуемся опцией 5, чтобы игнорировать скрытую строку, AGGREGATE в Excel мы дадим среднее значение только для видимых числовых значений.
Вывод без скрытия строки 3
Вывод после скрытия строки 3
Применяя формулу АГРЕГАТ для других операций, мы имеем
Пример - # 2
Предположим, у нас есть таблица доходов, полученных в разные даты из разных каналов, как показано ниже.
Теперь мы хотим проверить доход, полученный по разным каналам. Итак, когда мы применяем функцию суммы, мы получаем общий сгенерированный доход, но в случае, если мы хотим проверить доход, полученный для органического канала или прямого канала или любого другого, когда мы применяем фильтры в Excel для того же самого, функция суммы всегда будет дать общую сумму
Мы хотим, чтобы при фильтрации канала мы получали сумму видимых значений, поэтому вместо использования функции SUM мы будем использовать функцию AGGREGATE, чтобы получить сумму значений, которые видны, когда фильтр применяется.
Итак, заменив формулу СУММ на функцию АГРЕГАТ с кодом опции 5 (игнорируя скрытые строки и значения), мы имеем,
Теперь, когда мы применим фильтр к разным каналам, он покажет доход только для этого канала, поскольку остальные строки будут скрыты.
Общий доход от прямого канала:
Общий доход от органического канала:
Общий доход от платного канала:
Итак, мы видим, что функция AGGREGATE вычисляет различные значения Sum для дохода, генерируемого для разных каналов после их фильтрации. Таким образом, функция АГРЕГАТ может динамически использоваться для замены разных функций на разные условия без использования условной формулы.
Предположим, что для одного и того же канала таблицы и дохода некоторые из наших значений дохода содержат ошибку, теперь нам нужно игнорировать ошибки, и в то же время, если мы хотим применить фильтр, функция AGGREGATE также должна игнорировать значения скрытых строк.
Когда мы используем вариант 5, мы получаем ошибку для СУММЫ общего дохода, теперь, чтобы игнорировать ошибки, мы должны использовать вариант 6
Используя вариант 6, мы получаем сумму, игнорируя значения ошибок, но когда мы применяем фильтр, например, фильтруем по значению канала Direct, мы получаем ту же сумму, игнорируя ошибки, но в то же время мы также должны игнорировать скрытые значения.
Итак, в этом случае мы будем использовать вариант 7, который игнорирует значения ошибок и в то же время скрытые строки
То, что нужно запомнить
- Функция AGGREGATE не распознает значение function _ num больше 19 или меньше 1, и аналогично для Option number она не распознает значения больше 7 и меньше 1, если мы предоставим какие-либо другие значения, она даст #VALUE ! ошибка
- Он всегда принимает числовое значение и всегда возвращает числовое значение в качестве вывода.
- АГРЕГАТ в Excel имеет ограничение; он игнорирует только скрытые строки, но не игнорирует скрытые столбцы.