Функция АГРЕГАТ для работы с ошибками в ячейках таблицы Excel

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

Примеры использования функции АГРЕГАТ в Excel

Пример 1. В классе провели контрольную по математике. Необходимо определить среднюю оценку для учеников, которые присутствовали на контрольной. Напротив некоторых фамилий оценка не указана по причине неn допуска к написанию или отсутствия.

Вид таблицы с данными:

Пример 1.

Для расчета используем следующую формулу:

=АГРЕГАТ(1;3;B3:B13)

Описание параметров:

  • 1 – число, соответствующее функции СРЗНАЧ;
  • 3 – число, указывающее на способ расчета (не учитывать скрытые строки и коды ошибок);
  • B3:B13 – диапазон ячеек с данными для определения среднего значения.

Полученный результат:

АГРЕГАТ.

В результате формула вернула правильное число среднего значения в обход значениям с ошибками #Н/Д.



Как пропустить ошибки в ячейках при суммировании в Excel

Пример 2. В таблицу Excel были выгружены данные о работе двух счетчиков электроэнергии (записи по дням). Если в какой-либо из дней потребления электроэнергии не было, передаваемое значение из БД интерпретируется табличным процессором как ошибка #Н/Д. Определить, какой из счетчиков зафиксировал наибольшее общее потребление энергии за определенный период.

Вид таблицы с данными:

Пример 2.

Для решения удобно использовать функцию АГРЕГАТ, поскольку она позволяет исключить коды ошибок из расчетов.

Определим показатели первого счетчика по формуле:

Описание параметров:

  • 9 – указатель на функцию СУММ;
  • 2 – указатель на способ расчета (игнорирование значений ошибок);
  • B3:B13 – ссылка на диапазон со значениями кВт.

Произведем аналогичный расчет для второго счетчика, передав в качестве ссылки ячейки C3:C13. В результате получим следующие значения:

функция АГРЕГАТ при суммировании.

Например, для расчета этих значений с использованием обычной функции СУММ потребуется более сложная и громоздкая запись (формула массива – для выполнения нужно нажать ctrl+shift+enter):

СУММ ЕСЛИ ЕОШИБКА.

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

Рассматриваемая функция имеет две формы синтаксической записи:

Ссылочная:

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

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

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

Описание аргументов:

  • номер_функции – обязательный для заполнения, принимает числовые значения от 1 до 19, где каждое значение соответствует определенной функции (например, 1 – СРЗНАЧ, 2 – СЧЁТ и т. д.) Полный перечень номеров и соответствующих им функций указан в справочной информации к функции АГРЕГАТ.
  • параметры – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 7, где:
  1. значения от 0 до 3 – пропуск вложенных функций АГРЕГАТ, промежуточные итоги (для всех случаев), а также скрытых строк (1), значений ошибок (2), скрытых строк и значений ошибок (3);
  2. число 4 – все значения учитываются;
  3. 5-7 – пропуск только скрытых строк, значений ошибок и скрытых строк и значений ошибок соответственно.
  • ссылка1 – обязательный для заполнения, принимает ссылку на диапазон ячеек с данными, над которыми требуется выполнить вычисления.
  • [ссылка2] – необязательный аргумент, принимает ссылку на диапазон ячеек с данными, требуемыми для вычислений некоторыми функциями (например, НАИБОЛЬШИЙ, КВАРТИЛЬ.ВКЛ и прочие).
  • массив – обязательный аргумент функции АГРЕГАТ формы массива, принимающий диапазон вычисляемых данных.
  • [k] – не обязательный для заполнения аргумент, принимающий числовое значение, указывающее на позицию в массиве для некоторых функций (например, НАИМЕНЬШЕЕ, НАИБОЛЬШЕЕ, ПРОЦЕНТИЛЬ.ВКЛ и прочие).

Примечания:

  1. Если [ссылка2] и последующие необязательные аргументы требуются для вычислений, но не указаны явно, функция АГРЕГАТ вернет код ошибки #ЧИСЛО!
  2. Если в качестве любого ссылочного параметра была передана трехмерная ссылка, результатом выполнения функции АГРЕГАТ будет код ошибки #ЧИСЛО!
  3. Функция АГРЕГАТ была добавлена в Excel начиная с версии 2010 года и предназначена для расширения функционала функций, определяемых первым аргументом (от 1 до 19), в частности для проведения расчета по видимым значениям, игнорирования возникающих ошибок.

Примечание: Если в качестве массива или ссылки на диапазон было передано выражение с промежуточными вычислениями (например, «B1:B10*(B1:B10<5)»), вложенные итоги, агрегаты или скрытые строки игнорироваться не будут.


en ru