Функция АГРЕГАТ (AGGREGATE)

Функция АГРЕГАТ впервые появилась в Excel версии 2010, поэтому является сравнительно молодой, чрезвычайно полезной и, как часто бывает, недооцененной пользователями табличного процессора, как правило, из-за непонимания ее применения на практике.

Описание функции АГРЕГАТ

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

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

Синтаксис

Ссылочная форма

=АГРЕГАТ(номер_функции; параметры; ссылка1; [ссылка2]; …)

Форма массива

=АГРЕГАТ (номер_функции; параметры; массив; [k])

Аргументы

номер_функциипараметрыссылка1ссылка2,...массивk
Обязательный аргумент. Число от 1 до 19, определяющее функцию, которую необходимо использовать.

Номер_функции Функция
1 СРЗНАЧ
2 СЧЁТ
3 СЧЁТЗ
4 МАКС
5 МИН
6 ПРОИЗВЕД
7 СТАНДОТКЛОН.В
8 СТАНДОТКЛОН.Г
9 СУММ
10 ДИСП.В
11 ДИСПР
12 МЕДИАНА
13 МОДА.ОДН
14 НАИБОЛЬШИЙ
15 НАИМЕНЬШИЙ
16 ПРОЦЕНТИЛЬ.ВКЛ
17 КВАРТИЛЬ.ВКЛ
18 ПРОЦЕНТИЛЬ.ИСКЛ
19 КВАРТИЛЬ.ИСКЛ
Обязательный аргумент. Числовое значение, определяющее, какие значения при вычислении функции следует пропускать.

Параметр Поведение
0 или опущен Пропуск вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
1 Пропуск скрытых строк, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
2 Пропуск значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
3 Пропуск скрытых строк, значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
4 Без пропуска
5 Пропуск скрытых строк
6 Пропуск значений ошибок
7 Пропуск скрытых строк и значений ошибок
Обязательный аргумент. Первый числовой аргумент для функций с несколькими числовыми аргументами, для которых необходимо найти агрегатное значение.
Необязательные аргументы. Числовые аргументы от 2 до 253, для которых необходимо вычислить агрегатное значение.
​В случае функций, принимающих массив, “ссылка1” — это массив, формула массива или ссылка на диапазон ячеек, для которых необходимо вычислить агрегатное значение.
Второй аргумент, требуемый определенными функциями. Функции, которым необходим аргумент, следующие:

НАИБОЛЬШИЙ(массив;k)
НАИМЕНЬШИЙ(массив;k)
ПРОЦЕНТИЛЬ.ВКЛ(массив;k)
КВАРТИЛЬ.ВКЛ(массив;кварт)
ПРОЦЕНТИЛЬ.ИСКЛ(массив;k)
КВАРТИЛЬ.ИСКЛ(массив;кварт)

Замечания

  • При вводе аргумента “номер_функции” для функции АГРЕГАТ в ячейке листа появляется список всех функций, которые можно использовать в качестве аргументов.
  • Если второй ссылочный аргумент необходим, но не указан, функция АГРЕГАТ возвращает ошибку “#ЗНАЧ!”.
  • Если одна или более ссылок являются трехмерными, функция АГРЕГАТ возвращает ошибку “#ЗНАЧ!”.
  • Функция АГРЕГАТ применяется к столбцам данных или вертикальным диапазонам. Она не предназначена для использования со строками данных или горизонтальными диапазонами. Так, при определении агрегата горизонтального диапазона данных с использованием параметра 1 (например, АГРЕГАТ(1;1;ссылка1)), скрытие столбца не повлияет на результат, однако на него повлияет скрытие строки в вертикальном диапазоне.

Пример

Видео работы функции

Использование функции АГРЕГАТ

Дополнительные материалы

Использование функции АГРЕГАТ (AGGREGATE) в Excel

Scroll Up