Повертає сукупність списку або бази даних. Використовуючи функцію AGGREGATE, ви зможете застосовувати різні агрегатні функції до списку або бази даних за допомогою параметра ігнорування прихованих рядків і значень помилок.
Синтаксис
Форма посилання
AGGREGATE(номер_функції, параметри, посилання1, [посилання2], …)
Форма масиву
AGGREGATE(номер_функції, параметри, масив, [k])
Синтаксис функції AGGREGATE має такі аргументи:
-
номер_функції – обов’язковий аргумент. Номери з 1 до 19, що визначають, яка функція має використовуватися.
|
Параметр "номер_функції" |
Функція |
|
1 |
AVERAGE |
|
2 |
COUNT |
|
3 |
COUNTA |
|
4 |
MAX |
|
5 |
MIN |
|
6 |
PRODUCT |
|
7 |
STDEV.S |
|
8 |
STDEV.P |
|
9 |
SUM |
|
10 |
VAR.S |
|
11 |
VAR.P |
|
12 |
MEDIAN |
|
13 |
MODE.SNGL |
|
14 |
LARGE |
|
15 |
SMALL |
|
16 |
PERCENTILE.INC |
|
17 |
QUARTILE.INC |
|
18 |
PERCENTILE.EXC |
|
19 |
QUARTILE.EXC |
-
параметри – обов’язковий аргумент. Числове значення, яке визначає, які значення слід ігнорувати в діапазоні обчислення функції.
Примітка.: Функція не ігноруватиме приховані рядки, вкладені проміжні й агреговані підсумки, якщо аргумент масиву передбачає обчислення, наприклад =AGGREGATE(14;3;A1:A100*(A1:A100>0);1).
|
Параметр |
Поведінка |
|
0 або не вказано |
Пропустити вкладені функції SUBTOTAL і AGGREGATE |
|
1 |
Пропустити приховані рядки, вкладені функції SUBTOTAL і AGGREGATE |
|
2 |
Пропустити значення помилок, вкладені функції SUBTOTAL і AGGREGATE |
|
3 |
Пропустити приховані рядки, значення помилок, вкладені функції SUBTOTAL і AGGREGATE |
|
4 |
Нічого не пропускати |
|
5 |
Пропустити приховані рядки |
|
6 |
Пропустити значення помилок |
|
7 |
Пропустити приховані рядки та значення помилок |
-
посилання1 – обов’язковий аргумент. Перший числовий аргумент для функцій, що приймають кілька числових аргументів, для яких потрібне сукупне значення.
-
посилання2, … – необов'язковий параметр. Числові аргументи з 2 до 253, для яких потрібне сукупне значення.
Для функцій, які охоплюють масив, "посилання1" – це масив, формула масиву або посилання на діапазон клітинок, для яких потрібне сукупне значення. Аргумент "посилання2" – це другий аргумент, який вимагають певні функції. Наведені нижче функції потребують аргументу "посилання2".
|
Функція |
|
LARGE(масив;k) |
|
SMALL(масив;k) |
|
PERCENTILE.INC(масив;k) |
|
QUARTILE.INC(масив;кварт) |
|
PERCENTILE.EXC (масив,k) |
|
QUARTILE.EXC (масив,частка) |
Примітки
Параметр "номер_функції" :
-
Як тільки вводиться "номер_функції" при введенні функції AGGREGATE у клітинку на аркуші, відображається список функцій, які можна використовувати як аргументи.
Помилки
-
Якщо другий аргумент посилання обов'язковий, але не наданий, функція AGGREGATE повертає #VALUE! помилку #REF!.
-
Якщо одне або кілька посилань містять об'ємні посилання, функція AGGREGATE повертає #VALUE! .
Тип діапазону
-
Функція AGGREGATE використовується для стовпців даних або вертикальних діапазонів. Вона не використовується для рядків даних або горизонтальних діапазонів. Наприклад, коли підводиться проміжний підсумок горизонтального діапазону з використанням параметра 1, наприклад AGGREGATE(1;1;посилання1), приховання стовпця не впливає на значення загальної суми. Але приховання рядка у вертикальному діапазоні впливає на значення сукупної суми.
Приклад
Скопіюйте дані прикладу з наведеної нижче таблиці та вставте їх у клітинку A1 нового аркуша Excel. Щоб відобразити результат обчислення формул, виберіть їх, натисніть клавішу F2, а потім – клавішу Enter. За потреби можна змінити ширину стовпців, щоб відобразити всі дані.
|
#DIV/0! |
82 |
|
|
72 |
65 |
|
|
30 |
95 |
|
|
#NUM! |
63 |
|
|
31 |
53 |
|
|
96 |
71 |
|
|
32 |
55 |
|
|
81 |
83 |
|
|
33 |
100 |
|
|
53 |
91 |
|
|
34 |
89 |
|
|
Формула |
Опис |
Результат |
|
=AGGREGATE(4, 6, A1:A11) |
Обчислює максимальне значення, ігноруючи помилкові значення в діапазоні |
96 |
|
=AGGREGATE(14, 6, A1:A11, 3) |
Обчислює третє найбільше значення, ігноруючи помилкові значення в діапазоні |
72 |
|
=AGGREGATE(15, 6, A1:A11) |
Повернеться #VALUE! помилку #REF!. Це відбувається тому, що функція AGGREGATE очікує другий аргумент посилання, оскільки для функції (SMALL) потрібен один аргумент. |
#VALUE! |
|
=AGGREGATE(12, 6, A1:A11, B1:B11) |
Обчислює медіану, ігноруючи помилкові значення в діапазоні |
68 |
|
=MAX(A1:A2) |
Поверне значення помилки, тому що в діапазоні обчислення є помилкові значення. |
#DIV/0! |
