Повертає сукупність списку або бази даних. Використовуючи функцію AGGREGATE, ви зможете застосовувати різні агрегатні функції до списку або бази даних за допомогою параметра ігнорування прихованих рядків і значень помилок.
Синтаксис
Форма посилання
AGGREGATE(номер_функції, параметри, посилання1, [посилання2], …)
Форма масиву
AGGREGATE(номер_функції, параметри, масив, [k])
Синтаксис функції AGGREGATE має такі аргументи:
- Function_num Необхідні. Номери з 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 (масив,частка) |
Примітки
Function_num:
- Як тільки вводиться "номер_функції" при введенні функції 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! |