07.04.2013 28203

Как в таблицах Excel применять функцию АГРЕГАТ

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

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

Таблица 113.1. Значения первого аргумента функции АГРЕГАТ

Значение	Функция
1	СРЗНАЧ
2	СЧЁТ
3	СЧЁТЗ
4	МАКС
5	МИН
6	ПРОИЗВЕД
7	СТАНДОТКЛОН.В
8	СТАНДОТКЛОН.Г
9	СУММ
10	ДИСП.В
11	ДИСП.Г
12	МЕДИАНА
13	МОДА.ОДН
14	НАИБОЛЬШИЙ
15	НАИМЕНЬШИЙ
16	ПРОЦЕНТИЛЬ.ВКЛ
17	КВАРТИЛЬ.ВКЛ
18	ПРОЦЕНТИЛЬ.ИСКЛ
19	КВАРТИЛЬ.ИСКЛ

Второй аргумент функции АГРЕГАТ — это целое число от 0 до 7, которое указывает, как обрабатывать скрытые ячейки и ошибки. В табл. 113.2 содержится описание всех вариантов.

Таблица 113.2. Значения второго аргумента функции АГРЕГАТ

Опция	Поведение
0 или пропущен	Пропускать вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
1	Пропускать скрытые строки, а также вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
2	Пропускать ошибочные значения, а также вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
3	Пропускать скрытые строки, ошибочные значения, а также вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
4	Ничего не пропускать
5	Пропускать скрытые строки
6	Пропускать ошибочные значения
7	Пропускать скрытые строки и ошибочные значения

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

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

На рис. 113.1 показан пример того, как может быть использована функция АГРЕГАТ. Лист содержит предварительные и полученные на тестах оценки для восьми студентов. Обратите внимание, что Диана не прошла тест, поэтому ячейка С8 содержит ошибочное значение #Н/Д (указывающее на недоступность).

Ячейка D11 хранит формулу, которая использует функцию СРЗНАЧ для расчета среднего изменения. Эта формула возвращает ошибку: =СРЗНАЧ(D2:D8). Формула в ячейке D12 использует функцию АГРЕГАТ с возможностью игнорировать ошибочные значения: =АГРЕГАТ(1;6;D2:D8).

Рис. 113.1. Функция АГРЕГАТ может применяться для расчета среднего, когда диапазон содержит ошибочные значения

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