Функция АГРЕГАТ впервые появилась в 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)), скрытие столбца не повлияет на результат, однако на него повлияет скрытие строки в вертикальном диапазоне.
Пример
Видео работы функции
Использование функции АГРЕГАТ