✅ AGGREGATE в Excel (формула, примеры) - Как использовать AGGREGATE?

АГРЕГАТ в Excel

AGGREGATE в Excel (Содержание)

АГРЕГАТ в Excel
AGGREGATE Формула в Excel
Как использовать функцию AGGREGATE?

АГРЕГАТ в Excel

Функция AGGREGATE была представлена в Microsoft Excel 2010, недоступна в версиях Excel 2003 или 2007
Он действует как функция рабочего листа и является встроенной функцией в Excel, которая классифицируется как Math / Trig Function.
Excel AGGREGATE имеет дополнительную встроенную опцию и функцию, которая позволяет игнорировать скрытые строки, ошибки и вложенные промежуточные итоги, это более мощная и продвинутая форма по сравнению с другими
Он настолько универсален, что может за один раз заменить 19 наиболее часто используемых функций Excel в статистике (упомянуто на странице 2).

Определение

«Возвращает AGGREGATE в базу данных значений или список»

Это означает, что он выполняет ряд вычислений (19 статистических функций Excel)

AGGREGATE Формула в Excel

Функция AGGREGATE имеет два типа формул

Это можно заметить, когда вы набираете функцию AGGREGATE в ячейке Excel.

1) АГРЕГАТ в справочной форме

2) AGGREGATE в формуле массива

Function_num: это число, оно может быть от 1 до 19, зависит от того, какую конкретную функцию вы хотите использовать в приведенном ниже списке

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

От 1 до 13 - справочная форма, а от 14 до 19 - массив

1: СРЕДНИЙ

2: COUNT

3: COUNTA

4: Макс

5: MIN

6: ПРОДУКТ

7: STDEV.S

8: STDEV.P

9: СУММА

10: VAR.S

11: VAR.P

12: МЕДИАН

13: MODE.SNGL

14: БОЛЬШОЙ

15: МАЛЕНЬКИЙ

16: PERCENTILE.INC

17: QUARTILE.INC

18: PERCENTILE.EXC

19: QUARTILE.EXC

Параметры: это число от 0 до 7, указывающее, какие значения следует игнорировать для агрегатной функции. Примечание. Если параметр параметров не указан, по умолчанию для параметров установлено значение 0

0: игнорировать вложенные функции SUBTOTAL и AGGREGATE

1: игнорировать вложенные функции SUBTOTAL, AGGREGATE и скрытые строки

2: игнорировать вложенные функции SUBTOTAL, AGGREGATE и значения ошибок

3: игнорировать вложенные функции SUBTOTAL, AGGREGATE, скрытые строки и значения ошибок

4: ничего не игнорировать

5: игнорировать скрытые строки

6: игнорировать значения ошибок

7: игнорировать скрытые строки и значения ошибок

ref1, ref2, ref (3): это первый числовой аргумент для функции при использовании синтаксиса REFERENCE. это значения или числовое значение, для которого мы хотим выполнить вычисление. Здесь требуется минимум два аргумента, остальные аргументы необязательны. Для второй ссылки числовые аргументы должны быть в диапазоне от 2 до 253, для которых нужно указать совокупное значение

Массив: массив или относится к диапазону ячеек при использовании синтаксиса ARRAY.

(k): последние 6 функций (от 1 до 19 списка функций): значение k в качестве четвертого аргумента

Это необязательный аргумент, он используется, если нам нужно выяснить LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC или QUARTILE.EXC при использовании синтаксиса ARRAY

Как использовать функцию AGGREGATE в Excel?

Эта функция AGGREGATE очень проста в использовании. Давайте теперь посмотрим, как использовать функцию AGGREGATE с помощью нескольких примеров.

Вы можете скачать этот шаблон Excel функции AGGREGATE здесь - AGGREGATE Шаблон функции Excel

Пример № 1

Следующая таблица содержит данные о годовых продажах (2015)

Если вы запустите функцию = SUM (B8: B16) непосредственно в ячейке B17,

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

Здесь мы рассчитаем сумму, используя функцию AGGREGATE в ячейке B19.

= AGGREGATE (9, 4, B8: B16),

Результат будет 487

Function_ num: для функции SUM, function_ num равен 9

Вариант: в столбце B, т. Е. Для данных о продажах за 2015 год указаны все значения, и нам не нужно будет игнорировать какие-либо значения, в нем нет скрытых строк, ошибок и вложенных промежуточных итогов. Итак, мы выберем вариант 4 (ничего не игнорировать)

Массив: это диапазон, для которого вы хотите вычислить агрегатные функции. Здесь эталонный диапазон значений - B8: B16, он выбран в виде массива числовых значений.

'k' является необязательным аргументом, используется только для таких функций, как LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC. мы рассчитываем СУММУ здесь, поэтому мы будем опускать значение к.

Пример № 2

Следующая таблица содержит данные о годовых продажах (2016)

В столбце C - данные о годовых продажах за 2016 год. В диапазоне C8: C16 ячейка C11 и C12 содержит значение ошибки (# DIV / 0! & # N / A). В формуле AGGREGATE, когда используется соответствующая опция, AGGREGATE в Excel дает правильное значение SUM, пренебрегая значением ошибки.

Если вы запустите функцию = SUM (C8: C16) непосредственно в ячейке C17,

он возвращает значение ошибки из-за ошибки в этом диапазоне. потому что этот столбец содержит # DIV / 0! & # N / A ошибки.

Чтобы игнорировать значения ошибок, мы должны использовать опцию 6 в функции AGGREGATE.

= СОВОКУПНАЯ (9, 6, С8: С16).

Результат или результат будет 334

Function_ num: для функции SUM, function_ num равен 9

Опция: в столбце C, т. Е. Для данных о продажах за 2016 год, в диапазоне C8: C16 ячейка C11 и C12 содержит значение ошибки (# DIV / 0! & # N / A). Чтобы игнорировать эти ошибки, мы выберем опцию 6 (игнорировать значения ошибок)

Массив: это диапазон, для которого вы хотите вычислить агрегатные функции. Здесь эталонный диапазон значений - C8: C16, он выбран в виде массива числовых значений.

когда соответствующая опция используется в функции AGGREGATE, AGGREGATE в Excel возвращает или возвращает сумму оставшихся значений, игнорируя значение ошибки в ячейке C11 и C12. т.е. 334

Пример № 3

Следующая таблица содержит данные о годовых продажах (2017)

В столбце D приведены данные о годовых продажах за 2017 год. В диапазоне D8: D16 ячейка D9 является пустой ячейкой или скрытой строкой, а D12 содержит значение ошибки ( # N / A). В формуле AGGREGATE, когда используется соответствующая опция, AGGREGATE в Excel дает правильную сумму значение, пренебрегая скрытой строкой и значением ошибки.

Если вы запустите функцию = SUM (D8 : D16) непосредственно в ячейке D17,

он возвращает значение ошибки # N / A из-за ошибки в этом диапазоне. потому что этот столбец содержит # Н / Д ошибки и скрытые строки или пустое значение.

= СОВОКУПНАЯ (9, 7, D8: D16),

Результат или вывод будет 262

Function_ num: для функции SUM, function_ num равен 9

Опция: в столбце D, для данных о годовых продажах за 2017 год. В диапазоне D8: D16 ячейка D9 является пустой ячейкой или скрытой строкой, а D12 содержит значение ошибки (# N / A). Чтобы игнорировать эти ошибки, мы выберем опцию 7 (игнорировать скрытые строки и значения ошибок)

Примечание: вы можете скрыть строки, выбрав строку (и)> щелкнув правой кнопкой мыши по определенной строке> Скрыть

Массив: это диапазон, для которого вы хотите вычислить агрегатные функции. Здесь эталонный диапазон значений - D8: D16, он выбирается как массив числовых значений.

когда соответствующая опция используется в функции AGGREGATE, AGGREGATE в Excel возвращает или выдает сумму оставшихся значений, игнорируя значение ошибки в ячейке D9 и D12 . т.е. 262

Что следует помнить о функции AGGREGATE

Функция AGGREGATE применима только для вертикальных диапазонов или столбцов данных. Он не предназначен для горизонтального диапазона или строк данных.
Это имеет ограничение; он игнорирует только скрытые строки, он не игнорирует скрытые столбцы
Функция AGGREGATE применима только для числового значения
Значение аргумента функции _ num не должно быть больше 19 или меньше 1, аналогично, для аргумента option не должно быть больше 7, в противном случае он выдаст #VALUE! ошибка
В функции AGGREGATE, если в аргументе номера функции, если вы используете от 14 до 19 (LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC), следует использовать аргумент «K». грамм. = AGGREGATE (15, 6, A1: A9, 3). Если в этом случае значение «K» или второй ссылочный аргумент игнорируются, это приведет к # ЗНАЧЕНИЮ! ошибка

AGGREGATE в Excel (формула, примеры) - Как использовать AGGREGATE?