ОБЩАЯ функция Excel
Функция АГРЕГАТ в Excel
Функция AGGREGATE в Excel возвращает совокупность данной таблицы данных или списков данных, эта функция также имеет первый аргумент в качестве номера функции, а дополнительные аргументы относятся к диапазону наборов данных, номер функции следует запомнить, чтобы знать, какую функцию использовать.
Синтаксис
Формула AGGREGATE имеет два синтаксиса:
- Справочный синтаксис
= АГРЕГАТ (номер_функции; параметры; ссылка1; ссылка2; ссылка[3],…)
- Синтаксис массива
= АГРЕГАТ (номер_функции; параметры; массив;[k])
Function_num это число, обозначающее конкретную функцию, которую мы хотим использовать; это число от 1-19
Вариант: это также числовое значение от 0 до 7, которое определяет, какие значения следует игнорировать во время вычислений.
Ссылка 1, ссылка 2, ссылка[3]: является аргументом при использовании ссылочного синтаксиса; это числовое значение или значения, для которых мы хотим выполнить вычисление, требуются как минимум два аргумента. Остальные аргументы необязательны.
Массив: это массив значений, с которыми мы хотим работать; он используется в синтаксисе массива функции AGGREGATE в excel.
K: — необязательный аргумент и числовое значение; он используется, когда в Excel используются такие функции, как LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC.
Примеры
Пример — №1
Предположим, у нас есть список чисел, и мы вычислим Среднее, Счетчик, то есть количество ячеек, содержащих значение, Счетчик — количество ячеек, которые не являются пустыми, Максимум, Минимум, произведение и сумму заданных числовых значений. . Значения приведены ниже в таблице:
Давайте сначала вычислим среднее значение в строке 9 для всех заданных значений. Для среднего значение function_ num равно 1
В столбце C указаны все значения, и нам не придется игнорировать какие-либо значения, поэтому мы выберем вариант 4 (ничего не игнорировать).
И выбрав диапазон значений C1: C8 как массив числовых значений
Поскольку ‘k ‘ является необязательным аргументом и используется, когда используются такие функции, как LARGE, SMALL в Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC или QUARTILE.EXC, но в этом случае мы вычисляем Среднее, чтобы опустить значение k.
Итак, среднее значение
Точно так же для диапазона D1: D8 мы снова выберем вариант 4.
Для диапазона E1: E8 ячейка E6 содержит значение ошибки. Если мы воспользуемся той же формулой AGGREGATE, мы получим ошибку. Тем не менее, когда используется соответствующая опция, СОВМЕСТНОЕ в Excel дает среднее значение оставшихся значений без учета значения ошибки в E6.
Чтобы игнорировать значения ошибок, у нас есть вариант 6.
Точно так же для диапазона G1: G8 мы будем использовать вариант 6 (игнорировать значения ошибок)
Теперь для диапазона H3, если мы поместим значение 64 и скроем третью строку и воспользуемся опцией 5, чтобы игнорировать скрытую строку, AGGREGATE в Excel, мы дадим среднее значение только для видимых числовых значений.
Вывод без скрытия строки 3
Вывод после скрытия строки 3
Применяя формулу АГРЕГАТ для других операций, мы имеем
Пример — # 2
Предположим, у нас есть таблица доходов, полученных в разные даты из разных каналов, как показано ниже.
Теперь мы хотим проверить доход, полученный по разным каналам. Итак, когда мы применяем функцию суммы, мы получаем общий сгенерированный доход, но в случае, если мы хотим проверить доход, полученный для органического канала, прямого канала или любого другого, когда мы применяем фильтры в Excel для того же самого, функция суммы всегда будет давать общую сумму
Мы хотим, чтобы при фильтрации канала мы получали сумму видимых значений, поэтому вместо использования функции SUM мы будем использовать функцию AGGREGATE для получения суммы значений, видимых при применении фильтра.
Итак, заменив формулу SUM функцией AGGREGATE с кодом опции 5 (игнорируя скрытые строки и значения), мы имеем,
Теперь, когда мы применяем фильтр для разных каналов, он будет показывать доход только для этого канала, поскольку остальные строки будут скрыты.
Общий доход от прямого канала:
Общий доход от органического канала:
Общий доход от платного канала:
Мы видим, что функция AGGREGATE вычисляет различные значения Sum для дохода, генерируемого для разных каналов после их фильтрации. Таким образом, функция АГРЕГАТ может динамически использоваться для замены разных функций для разных условий без использования условной формулы.
Предположим, что для той же таблицы некоторые из наших значений дохода содержат ошибку, теперь нам нужно игнорировать ошибки, и в то же время, если мы хотим применить фильтр, функция AGGREGATE также должна игнорировать значения скрытых строк.
Когда мы используем вариант 5, мы получаем ошибку для СУММЫ общего дохода. Чтобы игнорировать ошибки, мы должны использовать вариант 6
Используя вариант 6, мы получаем сумму без учета значений ошибок. Тем не менее, когда мы применяем фильтр, например, фильтр по значению канала Direct, мы получаем ту же сумму, игнорируя ошибки, но в то же время мы также должны игнорировать скрытые значения.
Итак, в этом случае мы будем использовать вариант 7, который игнорирует значения ошибок и, в то же время, скрытые строки.
То, что нужно запомнить
- Функция AGGREGATE не распознает значение function _ num больше 19 или меньше 1. Точно так же для Option number оно не определяет значения больше 7 и меньше 1; если мы предоставим другие значения, будет получено # ЗНАЧЕНИЕ! ошибка
- Он всегда принимает числовое значение и всегда возвращает числовое значение в качестве вывода.
- АГРЕГАТ в Excel имеет ограничение; он игнорирует только скрытые строки, но не игнорирует скрытые столбцы.
Видео о функции AGGREGATE Excel
УЗНАТЬ БОЛЬШЕ >>
Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)
