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

Резюме

Функція Excel AGGREGATE повертає сукупний розрахунок, як AVERAGE, COUNT, MAX тощо, за бажанням ігноруючи приховані рядки та помилки. Доступно 19 операцій, вказаних номером функції в першому аргументі (параметри див. У таблиці).

Призначення

Повернення сукупного розрахунку

Повернене значення

Залежить від зазначеної функції

Синтаксис

= АГРЕГАТ (номер_функції, параметри, ref1, ref2)

Аргументи

  • function_num - Операція для виконання (1-19).
  • options - Значення, які слід ігнорувати (0-7).
  • ref1 - перший аргумент.
  • ref2 - Другий аргумент (k).

Версія

Excel 2010

Примітки щодо використання

Функція AGGREGATE повертає результат сукупного обчислення, наприклад AVERAGE, COUNT, MAX, MIN тощо. Всього доступно 19 операцій, і операція, яку потрібно виконати, вказується як число, яке відображається як перший аргумент у функції. Другий аргумент, параметри, визначає, як AGGREGATE обробляє помилки та значення у прихованих рядках. Усі доступні варіанти наведено в таблицях нижче.

Приклад №1

Щоб повернути значення MAX в діапазоні A1: A10, ігноруючи помилки та приховані рядки, вкажіть 4 для номера функції та 7 для параметрів:

=AGGREGATE(4,7,A1:A10) // max value

Щоб повернути значення MIN з тими ж параметрами, змініть номер функції на 5:

=AGGREGATE(5,7,A1:A10) // min value

Приклад №2

У наведеному вище прикладі формула в D5 має вигляд:

=AGGREGATE(4,6,values)

де "значення" - іменований діапазон B5: B14. Номер функції - 4, що вказує MAX. Опцій надано як 6, щоб ігнорувати лише помилки.

Приклад №3 - n-й за величиною

Формули в D8: D10 демонструють, як повернути "n-те за величиною" значення:

=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest

Номер функції - 14, який запускає функцію LARGE. Оскільки функція LARGE вимагає аргументу ak, вона відображається як останній аргумент у трьох формулах вище.

Приклад №4 - операція з масивом

Що робить AGGREGATE особливо корисним для більш складних формул, це те, що він може обробляти масиви безпосередньо, коли номер функції 14-19. Наприклад, щоб знайти МАКС значення у понеділок, з даними, що включають дати та значення, ви можете використовувати АГРЕГАТ так:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Тут ми вказуємо 14 для функції (LARGE) і 6 для опції (ігнорувати помилки). Потім ми будуємо логічний вираз за допомогою функції TEXT для перевірки всіх дат понеділка. Результатом цієї операції є масив значень TRUE / FALSE, які стають знаменником вихідних значень. FALSE вважає нулем і видає # DIV / 0! помилка. ІСТИНА обчислюється як 1 і повертає початкове значення. Остаточний масив значень та помилок діє як фільтр. AGGREGATE ігнорує всі помилки і повертає найбільше (максимум) із збережених значень. Повніший приклад тут.

Номери функцій

У таблиці нижче перелічені номери функцій, доступних для функції АГРЕГАТ, разом із назвою пов'язаної функції. Третій стовпець, Ref2, вказує друге значення аргументу, яке очікується за потреби.

Функція Функція Посилання2
1 СЕРЕДНІЙ
2 РАХУВАТИ
3 КОНТА
4 МАКС
5 ХВ
6 ПРОДУКТ
7 СТДЕВ.С
8 СТДЕВ.П
9 СУММА
10 VAR.S
11 VAR.P
12 МЕДІАН
13 MODE.SNGL
14 ВЕЛИКИЙ k
15 МАЛЕНЬКИЙ k
16 PERCENTILE.INC k
17 КВАРТИЛЬ.INC кварта
18 PERCENTILE.EXC k
19 КВАРТИЛЬНИЙ. EXC кварта

Варіанти поведінки

Функція AGGREGATE має безліч варіантів ігнорування помилок, прихованих рядків та інших функцій. Можливі значення - 0-7, як показано в таблиці нижче.

Варіант Поведінка
0 Ігнорувати функції SUBTOTAL та AGGREGATE
1 Ігнорувати приховані рядки, функції SUBTOTAL та AGGREGATE
2 Ігнорувати значення помилок, функції SUBTOTAL та AGGREGATE
3 Ігнорувати приховані рядки, значення помилок, функції SUBTOTAL та AGGREGATE
4 Нічого не ігноруйте
5 Ігнорувати приховані рядки
6 Ігнорувати значення помилок
7 Ігнорувати приховані рядки та значення помилок

Примітки

  • AGGREGATE повертає #VALUE! помилка, якщо потрібен другий аргумент функції, але не наданий.
  • 3D-посилання не працюють з AGGREGATE.
  • Функція AGGREGATE призначена для вертикальних діапазонів, а не для горизонтальних діапазонів.

Цікаві статті...