✅ АГРЕГАТИКА в Excel (формула, приклади)

АГРЕГАТИКА в Excel

АГРЕГАТИКА в Excel (Зміст)

АГРЕГАТИКА в Excel
АГРЕГАТА Формула в Excel
Як використовувати функцію AGGREGATE?

АГРЕГАТИКА в Excel

Функція AGGREGATE була введена в Microsoft Excel 2010, недоступна у версіях Excel 2003 або 2007
Він виконує функцію робочого листа і є вбудованою функцією в Excel, яка класифікується за функцією Math / Trig.
Excel AGGREGATE має додатковий вбудований параметр і функцію, яка дозволяє ігнорувати приховані рядки, помилки та вкладені підсумкові відомості, це більш потужна та розширена форма порівняно з іншими
Він настільки універсальний, що може замінити згадані нижче 19 найбільш часто використовуваних функцій Excel у статистиці за один раз (згадується на сторінці 2)

Визначення

"Повертає АГРЕГАТУ в базі даних значень або списку"

Це означає, що він виконує ряд обчислень (19 статистичних функцій Excel)

АГРЕГАТА Формула в Excel

Функції AGGREGATE мають формулу двох типів

Це можна помітити, коли ви вводите функцію AGGREGATE у комірці excel

1) АГРЕГАТИКА у довідковій формі

2) АГРЕГАТИКА у формулі масиву

Function_num: це число, воно може бути від 1 до 19, це залежить від того, яку конкретну функцію ви хочете використовувати у наведеному нижче списку

Тут кожне число являє собою функцію, це обов'язковий аргумент

1 - 13 - форма довідки, а 14 - 19 - форма масиву

1: СРЕДНА

2: COUNT

3: КОУНТА

4: МАКС

5: МІН

6: ПРОДУКТ

7: СТДЕВ.С

8: СТДЕВ.П

9: СУМ

10: VAR.S

11: VAR.P

12: МЕДІАН

13: РЕЖИМ.SNGL

14: ВЕЛИКИЙ

15: МАЛИЙ

16: PERCENTILE.INC

17: QUARTILE.INC

18: PERCENTILE.EXC

19: QUARTILE.EXC

Параметри: це число від 0 до 7, яке вказує, які значення ігнорувати для функції сукупності Примітка: Якщо параметр параметрів пропущено, за замовчуванням параметри встановлюються на 0

0: Ігнорувати вкладені функції SUBTOTAL та AGGREGATE

1: Ігнорувати вкладені функції SUBTOTAL, AGGREGATE та приховані рядки

2: Ігноруйте вкладені функції SUBTOTAL, AGGREGATE та значення помилок

3: Ігноруйте вкладені функції SUBTOTAL, AGGREGATE, приховані рядки та значення помилок

4: Ігнорувати нічого

5: Ігноруйте приховані рядки

6: Ігнорувати значення помилок

7: Ігноруйте приховані рядки та значення помилок

ref1, ref2, ref (3): Це перший числовий аргумент для функції при використанні синтаксису REFERENCE. це значення або числове значення, на якому ми хочемо виконати обчислення, тут потрібні мінімум два аргументи, аргументи відпочинку - необов'язкові, для другого посилання, числові аргументи повинні бути від 2 до 253, для яких потрібно сукупне значення

Масив: масив або посилається на діапазон комірок при використанні синтаксису ARRAY

(k): Останні 6 функцій (під 1 до 19 списку функцій): k значення як четвертий аргумент

Це необов'язковий аргумент, він використовується, якщо нам потрібно з'ясувати великі, малі, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC або QUARTILE.EXC при використанні синтаксису ARRAY

Як використовувати функцію AGGREGATE в Excel?

Ця функція AGGREGATE дуже проста у використанні. Давайте тепер подивимося, як використовувати функцію AGGREGATE за допомогою деяких прикладів.

Ви можете завантажити цей шаблон Excel з функцією AGGREGATE тут - шаблон AGGREGATE Function Excel

Приклад №1

Наступна таблиця містить щорічні дані продажів (2015)

Якщо ви запускаєте функцію = SUM (B8: B16) безпосередньо в комірці B17,

він дає правильне значення, оскільки цей стовпець не містить прихованих рядків, помилок та вкладених підзаданих

Тут буде обчислено SUM за допомогою функції AGGREGATE у комірці B19.

= АГРЕГАТИ (9, 4, B8: B16),

Результат буде 487

Function_ num: Для функції SUM функція_ число дорівнює 9

Варіант: У стовпці B, тобто для даних про продажі за 2015 рік, вказано всі значення, і нам не доведеться ігнорувати будь-які значення, воно не містить прихованих рядків, помилок та вкладених підсумкових даних. тому ми виберемо варіант 4 (нічого не ігноруй)

Масив: це діапазон, для якого потрібно обчислити сукупні функції. Тут контрольний діапазон значень B8: B16, він обраний у вигляді масиву числових значень

'k' - необов’язковий аргумент, використовується лише для такої функції, як LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC або QUARTILE.EXC. ми обчислюємо тут SUM, тому опустимо значення k.

Приклад №2

Наступна таблиця містить щорічні дані продажів (2016)

У графі С за даними щорічних продажів за 2016 рік. У діапазоні C8: C16 комірка C11 & C12 містить значення помилки (# DIV / 0! & # N / A). У формулі AGGREGATE, коли використовується відповідна опція, AGGREGATE в Excel дає правильне значення SUM, нехтуючи значенням помилки.

Якщо ви запускаєте функцію = SUM (C8: C16) безпосередньо в комірці C17,

він повертає значення помилки через помилку в цьому діапазоні. тому що цей стовпець містить # DIV / 0! & # N / A помилки.

Для того, щоб ігнорувати значення помилок, ми повинні використовувати варіант 6 у функції AGGREGATE

= АГРЕГАТИ (9, 6, C8: C16).

Результат або результат буде 334

Function_ num: Для функції SUM функція_ число дорівнює 9

Варіант: У стовпці C, тобто для даних про продажі за 2016 рік, у діапазоні C8: C16 комірка C11 та C12 містить значення помилки (# DIV / 0! & # N / A). Щоб ігнорувати ці помилки, виберемо Варіант 6 (Ігнорувати значення помилок)

Масив: це діапазон, для якого потрібно обчислити сукупні функції. Тут контрольний діапазон значень становить C8: C16, він обраний у вигляді масиву числових значень

коли в функції AGGREGATE використовується відповідна опція, AGGREGATE в Excel повертається або видає SUM решти значень, що нехтують значенням помилки в комірці C11 і C12. тобто 334

Приклад №3

Наступна таблиця містить щорічні дані продажів (2017)

У графі D, за даними щорічних продажів за 2017 рік. У діапазоні D8: D16 комірка D9 - це порожня комірка або прихований рядок, а D12 містить значення помилки ( # N / A). У формулі AGGREGATE, коли використовується відповідна опція, AGGREGATE в Excel дає правильну SUM значення, нехтуючи прихованим значенням рядка та помилки.

Якщо ви запускаєте функцію = SUM (D8 : D16) безпосередньо в комірці D17,

він повертає значення помилки # N / A через помилку в цьому діапазоні. оскільки цей стовпець містить # N / A помилку та прихований рядок або порожнє значення.

= АГРЕГАТИ (9, 7, D8: D16),

Результат або вихід буде 262

Function_ num: Для функції SUM функція_ число дорівнює 9

Варіант: У графі D для щорічних даних про продажі за 2017 рік. У діапазоні D8: D16 комірка D9 - це порожня комірка або прихований рядок, а D12 містить значення помилки (# N / A). Щоб ігнорувати ці помилки, виберемо варіант 7 (ігнорувати приховані рядки та значення помилок)

Примітка. Ви можете приховати рядки, вибравши рядок (и)> клацніть правою кнопкою миші на певному рядку> Сховати

Масив: це діапазон, для якого потрібно обчислити сукупні функції. Тут контрольний діапазон значень D8: D16, він обраний у вигляді масиву числових значень

коли в функції AGGREGATE використовується відповідна опція, AGGREGATE в Excel повертається або видає SUM решти значень, що нехтують значенням помилки в комірці D9 & D12 . тобто 262

Що слід пам’ятати про функцію АГРЕГАТА

Функція AGGREGATE застосована лише для вертикальних діапазонів або стовпців даних. Він не розроблений для горизонтального діапазону або рядків даних.
Він має обмеження; він ігнорує лише приховані рядки, не ігнорує приховані стовпці
Функція AGGREGATE застосовна лише для числового значення
Функція _ число аргументу значення не повинно бути більше 19 або менше 1, аналогічно для параметра аргумент не повинно бути більше 7, інакше воно дасть #VALUE! помилка
У функції AGGREGATE, якщо в аргументі номера функції, якщо ви використовуєте від 14 до 19 (ВЕЛИКИЙ, МАЛИЙ, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC або QUARTILE.EXC), слід використовувати аргумент "K". г. = АГРЕГАТИ (15, 6, A1: A9, 3). Якщо у випадку ігнорування значення "K" або другого опорного аргументу, це призведе до # VALUE! помилка

АГРЕГАТИКА в Excel (формула, приклади) - Як користуватися AGGREGATE?