Ссылки
Функция AGGREGATE в Excel позволяет проводить расчеты, исключая скрытые строки, ошибки или другие функции, которые могут присутствовать в данных. Она схожа с функцией SUBTOTAL, но предоставляет больше возможностей для расчетов и дает вам больше контроля над тем, что исключать из анализа.
Синтаксис функции AGGREGATE
Перед тем как перейти к примерам использования функции AGGREGATE, давайте разберемся, как она работает. У этой функции существует два синтаксиса — один для ссылок и один для массивов. Но не беспокойтесь о том, какой именно синтаксис использовать — Excel сам выберет нужный в зависимости от ваших параметров. Я в скором времени покажу вам оба варианта на примерах.
Синтаксис для ссылки
Синтаксис для формы ссылки функции AGGREGATE выглядит так:
где
Синтаксис для массива
С другой стороны, если вы работаете с массивами, синтаксис будет таким:
где
Функции и исключения (аргументы a и b)
При введении аргументов a и b в любом из вышеуказанных синтаксисов вам будет доступно несколько вариантов на выбор.
В таблице ниже представлены различные функции, которые можно использовать при расчете AGGREGATE (аргумент a). Несмотря на соблазн ввести название функции, помните, что этот аргумент должен быть числом, представляющим используемую вами функцию. Функции с 1 по 13 предназначены для синтаксиса формы ссылки, а с 14 по 19 — для синтаксиса формы массива.
Номер
Функция
Что она вычисляет
1
СРЗНАЧ
Среднее арифметическое
2
СЧЕТ
Количество ячеек с числовыми значениями
3
СЧЕТЕСЛИ
Количество непустых ячеек
4
МАКС
Наибольшее значение
5
МИН
Наименьшее значение
6
ПРОИЗВЕДЕНИЕ
Умножение
7
СТАНДОТКЛОН.С
Выборочная стандартная девиация
8
СТАНДОТКЛОН.И
Стандартная девиация для всей генеральной совокупности
9
СУММ
Сумма
10
ДИСП.C
Выборочная дисперсия
11
ДИСП.И
Дисперсия для всей генеральной совокупности
12
МЕДИАНА
Среднее значение
13
МОДА
Наиболее часто встречающееся значение
14
БОЛЬШИЙ
Наибольшее n значение
15
МЕНЬШИЙ
Наименьшее n значение
16
ПРОЦЕНТИЛЬ.НА
Наибольший n процентиль, включая крайние значения
17
КВАРТИЛЬ.НА
Наибольший n квартиль, включая крайние значения
18
ПРОЦЕНТИЛЬ.ИС
Наибольший n процентиль, исключая крайние значения
19
КВАРТИЛЬ.ИС
Наибольший n квартиль, исключая крайние значения
Эта таблица перечисляет числа, которые вы можете ввести, чтобы исключить определенные значения при создании вашей формулы AGGREGATE (аргумент b):
Номер
Что игнорируется
0
Вложенные функции SUBTOTAL и AGGREGATE
1
Скрытые строки и вложенные функции SUBTOTAL и AGGREGATE
2
Ошибки и вложенные функции SUBTOTAL и AGGREGATE
3
Скрытые строки, ошибки и вложенные функции SUBTOTAL и AGGREGATE
4
Ничего
5
Только скрытые строки
6
Только ошибки
7
Скрытые строки и ошибки
Теперь давайте рассмотрим несколько примеров применения функции AGGREGATE в реальных ситуациях.
Пример 1: Использование AGGREGATE для игнорирования ошибок
В этой таблице Excel представлен список футболистов, где указано количество сыгранных ими матчей, количество голов, которые они забили, и их соотношение игр к головам. Ваша задача — вычислить среднее соотношение игр к головам для всех игроков.
Если бы вы использовали функцию СРЗНАЧ, просто введя:
в ячейку C1, это привело бы к ошибке, так как диапазон ссылок содержит ошибку #ДЕЛ/0!.
Как исправить распространенные ошибки формул в Microsoft Excel
Узнайте, что означает эта ошибка и как ее исправить.
Вместо этого, использование функции AGGREGATE позволяет игнорировать эти ошибки и вычислить среднее по оставшимся данным. Для этого в ячейке C2 вам нужно ввести:
где
Другой способ получить тот же результат — использовать функцию ЕСЛИОШИБКА в колонке D, чтобы подменить любые ошибки пустым значением.
Пример 2: Использование AGGREGATE для игнорирования скрытых строк (ссылка)
Используя ту же таблицу, вашей следующей задачей будет подсчитать общее количество голов, забитых командой.
Один из способов отобразить итоги — отметить "Итоговая строка" на вкладке Дизайн таблицы на ленте, что разместит итоги внизу таблицы. Однако, если вы работаете с большими наборами данных, постоянная прокрутка вниз для просмотра итогов может занять много времени. Вместо этого будет удобно разместить итоги вверху таблицы вне форматированной таблицы, чтобы они всегда были на виду.
Конкретно вам нужно отобразить два итога. Первый — общий итог, показывающий количество голов, забитых всеми игроками, а второй — итог только для игроков, видимых в таблице после применения фильтров.
Для расчета общего итога в ячейке C1 введите:
Теперь, даже после применения фильтра к одному из столбцов, например, если отобразить только игроков, сыгравших 15 и более игр, формула СУММ, которую вы только что применили, все равно включает строки, которые были отфильтрованы.
Вот здесь функция AGGREGATE вам поможет, так как она позволяет вашему расчету игнорировать строки, которые были отфильтрованы. На самом деле функция AGGREGATE также будет работать, если вы захотите игнорировать строки, которые вы скрыли, щелкнув правой кнопкой мыши на заголовке строки и выбрав "Скрыть".
В ячейке C2 введите:
где
Теперь обратите внимание, что результат этой формулы отличается от результата функции СУММ, которую вы использовали в ячейке C1, так как она принимает во внимание только видимые строки.
Пример 3: Использование AGGREGATE для игнорирования скрытых строк (массив)
Теперь давайте предположим, что вы хотите перечислить два лучших результата по количеству голов для игроков, которые сыграли 20 игр или меньше.
Вы могли бы сначала применить фильтр, а затем создать формулу, но для целей этой демонстрации давайте сначала создадим формулу.
В ячейке C1 введите:
где
Когда вы нажмете Enter, обратите внимание, что результат будет в виде развертываемого массива, охватывающего ячейки C1 и C2, так как вы указали Excel вернуть два наивысших значения.
Все, что вам нужно знать о развертывании в Excel
Не стоит переживать о развернутых ссылках.
Теперь отфильтруйте столбец "Игры", чтобы оставить только тех игроков, которые сыграли 20 игр или меньше, и вы увидите, что результат формулы AGGREGATE, которую вы ввели ранее, изменится и начнет игнорировать скрытые строки.
Важные моменты при использовании функции AGGREGATE
Перед тем как применять функцию AGGREGATE в своих Excel-таблицах, уделите минуту, чтобы обратить внимание на следующие моменты:
Существует другой способ скрыть строки в таблицах Excel так, чтобы функция AGGREGATE учитывала только видимые, — это вставить срезы — интерактивные кнопки, которые можно нажимать для упрощения фильтрации.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете найти наши материалы в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru