АГРЕГАТ Функція в Excel
АГРЕГАТ Функція в Excel повертає сукупність даної таблиці даних або списків даних; ця функція також має перший аргумент як номер функції, а подальші аргументи стосуються діапазону наборів даних; номер функції слід запам'ятати, щоб знати, яку функцію використовувати .
Синтаксис
Існує два синтаксиси формули АГРЕГАТ:
- Довідковий синтаксис
= АГРЕГАТ (номер_функції, параметри, ref1, ref2, ref [3],…)
- Синтаксис масиву
= АГРЕГАТ (номер_функції, параметри, масив, [k])
Function_num - це число, яке позначає конкретну функцію, яку ми хочемо використовувати, це число від 1-19
Варіант: це також числове значення в діапазоні від 0 до 7 і визначає, які значення слід ігнорувати під час обчислень
Ref1, ref2, ref [3]: це аргумент під час використання посилального синтаксису, це числове значення або значення, за якими ми хочемо виконати обчислення, принаймні два аргументи потрібні, аргументи відпочинку не є обов’язковими.
Масив: це масив значень, над якими ми хочемо виконати операцію, він використовується в синтаксисі масиву функції AGGREGATE у excel
K: є необов'язковим аргументом і є числовим значенням, він використовується, коли в Excel використовується така функція, як LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC або QUARTILE.EXC.
Приклади
Ви можете завантажити цей АГРЕГАТНИЙ Шаблон функцій Excel тут - АГРЕГАТНИЙ Шаблон функцій ExcelПриклад - No1
Припустимо, у нас є список чисел, і ми обчислимо Середнє, Графік, яке є кількістю комірок, що містять значення, Лічильник-кількість клітинок, які не є порожніми, Максимум, Мінімум, добуток та суму даних числових значень. Значення наведені нижче в таблиці:
Спершу обчислимо середнє значення в рядку 9 для всіх заданих значень. Для середнього значення function_ num дорівнює
У стовпці C вказані всі значення, і нам не доведеться ігнорувати будь-які значення, тому ми оберемо варіант 4 (нічого не ігнорувати)
І вибір діапазону значень C1: C8 як масиву числових значень
Оскільки ' k' є необов'язковим аргументом і використовується, коли використовується така функція, як LARGE, SMALL в Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC або QUARTILE.EXC, але в цьому випадку ми обчислюємо середнє значення, тому опустить значення k.
Отже, середнє значення становить
Подібним чином для діапазону D1: D8 ми знову обрамо варіант 4.
Для діапазону E1: E8 клітинка E6 містить значення помилки, якщо ми будемо використовувати ту саму формулу AGGREGATE, ми отримаємо помилку, але при використанні відповідної опції, AGGREGATE в Excel дає середнє значення всіх інших значень, нехтуючи помилкою значення в E6.
Щоб ігнорувати значення помилок, ми маємо варіант 6.
Аналогічно, для діапазону G1: G8 ми будемо використовувати опцію 6 (ігнорувати значення помилок)
Тепер для діапазону H3, якщо ми ставимо значення 64, а третій рядок приховуємо та використовуємо варіант 5, щоб ігнорувати прихований рядок, АГРЕГАТ в Excel ми дамо середнє значення лише для видимих числових значень.
Вихід без приховування рядка 3
Вихід після приховування рядка 3
Ми застосовуємо формулу АГРЕГАТ для інших операцій
Приклад - No2
Припустимо, у нас є таблиця доходів, отриманих за різні дати від різних каналів, як наведено нижче
Тепер ми хочемо перевірити дохід, отриманий за різними каналами. Отже, коли ми застосовуємо функцію суми, ми отримуємо загальний дохід, але якщо ми хочемо перевірити дохід, створений для органічного каналу, прямого каналу чи будь-якого іншого, коли ми застосовуємо фільтри в Excel для того самого, функція суми завжди буде дайте загальну суму
Ми хочемо, щоб, коли ми фільтруємо канал, ми отримували суму значень, які видно, тому замість використання функції SUM, ми будемо використовувати функцію AGGREGATE, щоб отримати суму значень, які видно, коли фільтр застосовується.
Отже, замінивши формулу SUM на функцію AGGREGATE з кодом опції 5 (ігноруючи приховані рядки та значення), ми маємо,
Тепер, коли ми застосуємо фільтр для різних каналів, він покаже дохід за цим каналом лише тоді, коли решта рядків буде приховано.
Загальний дохід, отриманий за прямим каналом:
Загальний дохід, отриманий від органічного каналу:
Загальний дохід, отриманий від платного каналу:
Отже, ми можемо бачити, що функція AGGREGATE обчислює різні значення Суми для доходу, що генерується для різних каналів, як тільки вони фільтруються. Отже, функцію AGGREGATE можна динамічно використовувати для заміни різних функцій для різних умов без використання умовної формули.
Припустимо, для одного і того ж каналу таблиці та доходу деякі з наших значень доходу містять помилку, тепер нам потрібно ігнорувати помилки і одночасно, якщо ми хочемо застосувати фільтр, функція AGGREGATE також повинна ігнорувати значення прихованих рядків.
Коли ми використовуємо варіант 5, ми отримуємо помилку для суми загального доходу, тепер для того, щоб ігнорувати помилки, ми маємо використовувати варіант 6
Використовуючи варіант 6, ми отримуємо суму, ігноруючи значення помилок, але коли ми застосовуємо фільтр, наприклад, фільтр за значенням каналу Direct, ми отримуємо ту саму суму, ігноруючи помилки, але в той же час ми також повинні ігнорувати приховані значення.
Отже, у цьому випадку ми будемо використовувати варіант 7, який ігнорує значення помилок і одночасно приховані рядки
Що слід пам’ятати
- Функція AGGREGATE не розпізнає функцію _ число, яке перевищує 19 або менше 1, і аналогічно для параметра Option вона не розпізнає значення більше 7 і менше 1, якщо ми надаємо будь-які інші значення, які дають #VALUE ! Помилка
- Він завжди приймає числове значення і завжди повертає числове значення як вихідне
- АГРЕГАТ в Excel має обмеження; він лише ігнорує приховані рядки, але не ігнорує приховані стовпці.