Функция АГРЕГАТ [AGGREGATE]
| Категория: Без рубрики, Приемы и советы, Формулы и функции | Опубликовано 30-09-2015
18
Исходные данные не всегда выглядят идеальными. В результате вычислений могут возникать различные типы ошибок, которые не всегда есть возможность заменить на какой-то альтернативный вариант решения. Плюс часто бывают ситуации, когда расчет надо вести только по отображенным (видимым) ячейкам. Всё это существенно усложняет процесс вычисления.
Функция АГРЕГАТ [AGGREGATE] стала доступна впервые в Excel 2010 версии. Можно считать ее расширенным вариантом множества статистических функций, которые определяют среднее, максимальное, минимальное и т.п. значения, т.к. она позволяет делать вычисления, игнорируя не только значения ошибок (эту проблему можно легко решить с использованием функции ЕСЛИОШИБКА [IFERROR] и формулы массива), но и скрытые ячейки.
Синтаксис функции:
АГРЕГАТ(Номер_функции;Параметры;Массив;[k])
[AGGREGATE(function_num;options;array;[k])]
- Номер_функции [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] - Параметры [options] – способ обработки ошибок и скрытых ячеек, изменяется от 0 до 7:
0 (по умолчанию) – Пропускать вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
1 – Пропускать скрытые строки и вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
2 – Пропускать значения ошибок, вложенные функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
3 – Пропускать скрытые строки, значения ошибок, вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
4 – Ничего не пропускать
5 – Пропускать скрытые строки
6 – Пропускать значений ошибок
7 – Пропускать скрытые строки и значения ошибок - Массив [array]- обрабатываемый диапазон данных
- [k] – позиция в массиве для функций: наибольшее, наименьшее, процентиль, квадратиль
30.09.2015 (22:11)
Нужная функция! Спасибо огромное!
04.10.2015 (00:14)
Пожалуйста, Ефим, пользуйтесь успешно!
05.10.2015 (14:29)
Да, поинтереснее Subtotal
12.10.2015 (18:53)
Спасибо Вам. Я бы никогда на такое название функции не обратил внимание. А функция нужная.
22.12.2015 (10:46)
Красота! Спасибо, я даже и не подозревала, что есть что-то большее, чем промежуточные итоги
02.01.2016 (17:00)
Очень нужная функция. Столько плюсов и жаль, что она не так часто встречается в чьих-то файлах. Вероятно, по незнанию
13.01.2016 (20:12)
Я прям зачитался Вашим блогом. Спасибо, Вы расширяете мои знания!
26.02.2016 (18:58)
Оказывается я этой функцией могла бы как минимум 5 лет пользоваться. Лучше поздно, чем уж совсем никогда
07.09.2016 (18:30)
Первый раз увидел такую полезную формулу. Я в восторге!
09.09.2016 (15:25)
У меня при виде названия такой функции никогда таких и ассоциаций не возникало. А зря! Очень полезная
23.06.2017 (00:40)
Интересная функция. Увидел в первый раз. Думаю, что будет полезна
16.03.2018 (14:38)
Спасибо за просвещение, порой и не догадаешься что искать
09.12.2018 (22:57)
Каждый первый не знает о существовании это функции, а жаль. Я стала первой в отдела использовать и остальных осчастливила знанием данной функцией
13.12.2018 (09:50)
Да уж, это покруче всяких промежуточных итогов!
18.02.2019 (09:35)
Функция явно как-то в тени осталась!
22.02.2020 (11:53)
Интересная функция, но малоизвестная
27.02.2020 (15:04)
Чаще всего функцией Агрегат пользуюсь для выборки.
Например, получить данные отделов ОТД или ТКБ с окладом более 50000. Протянуть вниз и вправо
=ЕСЛИОШИБКА(ИНДЕКС(A$1:A$15;АГРЕГАТ(15;6;СТРОКА($A$1:$A$15)/(($C$1:$C$15=$C$2)+($C$1:$C$15=$C$7))/($D$1:$D$15>50000);СТРОКА(A1)));””)
28.02.2020 (00:34)
Симпатичная формула!