ОБЩАЯ функция Excel

Функция АГРЕГАТ в Excel

Функция AGGREGATE в Excel возвращает совокупность данной таблицы данных или списков данных, эта функция также имеет первый аргумент в качестве номера функции, а дополнительные аргументы относятся к диапазону наборов данных, номер функции следует запомнить, чтобы знать, какую функцию использовать.

Синтаксис

Формула AGGREGATE имеет два синтаксиса:

  1. Справочный синтаксис

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

  1. Синтаксис массива

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

Function_num это число, обозначающее конкретную функцию, которую мы хотим использовать; это число от 1-19

Агрегатная функция (номер функции)

Вариант: это также числовое значение от 0 до 7, которое определяет, какие значения следует игнорировать во время вычислений.

Опция агрегатной функции)

Ссылка 1, ссылка 2, ссылка[3]: является аргументом при использовании ссылочного синтаксиса; это числовое значение или значения, для которых мы хотим выполнить вычисление, требуются как минимум два аргумента. Остальные аргументы необязательны.

Массив: это массив значений, с которыми мы хотим работать; он используется в синтаксисе массива функции AGGREGATE в excel.

K: — необязательный аргумент и числовое значение; он используется, когда в Excel используются такие функции, как LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC.

Примеры

Вы можете скачать этот шаблон Excel с функцией AGGREGATE здесь — Шаблон Excel с функцией AGGREGATE

Пример — №1

Предположим, у нас есть список чисел, и мы вычислим Среднее, Счетчик, то есть количество ячеек, содержащих значение, Счетчик — количество ячеек, которые не являются пустыми, Максимум, Минимум, произведение и сумму заданных числовых значений. . Значения приведены ниже в таблице:

Пример 1 функции агрегирования Excel

Пример 1.1 агрегированной функции Excel

Давайте сначала вычислим среднее значение в строке 9 для всех заданных значений. Для среднего значение function_ num равно 1

Пример функции агрегирования Excel 1-2

В столбце C указаны все значения, и нам не придется игнорировать какие-либо значения, поэтому мы выберем вариант 4 (ничего не игнорировать).

Пример функции агрегирования Excel 1-3

И выбрав диапазон значений C1: C8 как массив числовых значений

Пример функции агрегирования Excel 1-4

Поскольку ‘k ‘ является необязательным аргументом и используется, когда используются такие функции, как LARGE, SMALL в Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC, но в этом случае мы вычисляем Среднее, чтобы опустить значение k.

Итак, среднее значение

Пример функции агрегирования Excel 1-5

Точно так же для диапазона D1: D8 мы снова выберем вариант 4.

Для диапазона E1: E8 ячейка E6 содержит значение ошибки. Если мы воспользуемся той же формулой AGGREGATE, мы получим ошибку. Тем не менее, когда используется соответствующая опция, СОВМЕСТНОЕ в Excel дает среднее значение оставшихся значений без учета значения ошибки в E6.

Чтобы игнорировать значения ошибок, у нас есть вариант 6.

Агрегатный пример 1-6

Агрегатный пример 1-7

Точно так же для диапазона G1: G8 мы будем использовать вариант 6 (игнорировать значения ошибок)

Агрегатный пример 1-8

Теперь для диапазона H3, если мы поместим значение 64 и скроем третью строку и воспользуемся опцией 5, чтобы игнорировать скрытую строку, AGGREGATE в Excel, мы дадим среднее значение только для видимых числовых значений.

Вывод без скрытия строки 3

Агрегатный пример 1-9

Вывод после скрытия строки 3

Агрегатный пример 1-10

Применяя формулу АГРЕГАТ для других операций, мы имеем

Агрегатный пример 1-11

Пример — # 2

Предположим, у нас есть таблица доходов, полученных в разные даты из разных каналов, как показано ниже.

Пример 2 агрегатной функции Excel

Теперь мы хотим проверить доход, полученный по разным каналам. Итак, когда мы применяем функцию суммы, мы получаем общий сгенерированный доход, но в случае, если мы хотим проверить доход, полученный для органического канала, прямого канала или любого другого, когда мы применяем фильтры в Excel для того же самого, функция суммы всегда будет давать общую сумму

Пример функции агрегирования Excel 2-1

Пример функции агрегирования Excel 2-2

Мы хотим, чтобы при фильтрации канала мы получали сумму видимых значений, поэтому вместо использования функции SUM мы будем использовать функцию AGGREGATE для получения суммы значений, видимых при применении фильтра.

Итак, заменив формулу SUM функцией AGGREGATE с кодом опции 5 (игнорируя скрытые строки и значения), мы имеем,

Агрегатный пример 2-3

Теперь, когда мы применяем фильтр для разных каналов, он будет показывать доход только для этого канала, поскольку остальные строки будут скрыты.

Общий доход от прямого канала:

Общий доход от прямого канала

Общий доход от органического канала:

Общий доход от органического канала

Общий доход от платного канала:

Общий доход от платного канала

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

Предположим, что для той же таблицы некоторые из наших значений дохода содержат ошибку, теперь нам нужно игнорировать ошибки, и в то же время, если мы хотим применить фильтр, функция AGGREGATE также должна игнорировать значения скрытых строк.

Агрегатный пример 2-7

Когда мы используем вариант 5, мы получаем ошибку для СУММЫ общего дохода. Чтобы игнорировать ошибки, мы должны использовать вариант 6

ошибка СУММЫ от общего дохода

Используя вариант 6, мы получаем сумму без учета значений ошибок. Тем не менее, когда мы применяем фильтр, например, фильтр по значению канала Direct, мы получаем ту же сумму, игнорируя ошибки, но в то же время мы также должны игнорировать скрытые значения.

сумма без учета значений ошибок

Итак, в этом случае мы будем использовать вариант 7, который игнорирует значения ошибок и, в то же время, скрытые строки.

вариант 7, который игнорирует значения ошибок

То, что нужно запомнить

  • Функция AGGREGATE не распознает значение function _ num больше 19 или меньше 1. Точно так же для Option number оно не определяет значения больше 7 и меньше 1; если мы предоставим другие значения, будет получено # ЗНАЧЕНИЕ! ошибка
  • Он всегда принимает числовое значение и всегда возвращает числовое значение в качестве вывода.
  • АГРЕГАТ в Excel имеет ограничение; он игнорирует только скрытые строки, но не игнорирует скрытые столбцы.

Видео о функции AGGREGATE Excel

УЗНАТЬ БОЛЬШЕ >>

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *