четверг, 10 ноября 2016 г.

Функция AGGREGATE в LibreOffice Calc

В прошлом году я написал статью Сумма, автосумма и некоторые другие секреты LibreOffice Calc, в которой попытался показать основные возможности суммирования в LibreOffice Calc. Тогда, в той статье, я говорил что нет справочного материала по функции AGGREGATE в справке LibreOffice, и отсылал в справку MS Office к функции АГРЕГАТ, так как она совместима с функцией Calc. Позже уже, я сделал патч в английскую версию Справки, но, к сожалению, у команды локализации пока нет времени заниматься переводом. Эта статья, хотя и основана на моём патче, не является его переводом, но пытается предоставить больше информации в более простом, понятном виде об этой функции.

Основная задача функции AGGREGATE вернуть значение из диапазона, который может иметь скрытые строки, ошибки, результаты других функции SUBTOTAL и AGGREGATE. Я говорю «значение», потому что AGGREGATE может возвращать не только сумму видимых ячеек, но и находить минимальное/максимальное значение, подсчитывать количество ячеек и многое другое.

Сразу хочу заметить, что функция предназначена для работы с Автофильтром и при использовании её для других целей, можно получать неожиданные результаты. Например, если вы используете её для вертикального диапазона, но без Автофильтра, то пересчет значения при скрытии строк не будет работать. При применении к горизонтальному диапазону, она не определяет скрытые столбцы, хотя может игнорировать ошибки в ячейках или результаты SUBTOTAL и AGGREGATE.

Синтаксис

AGGREGATE(Функция; Условие; Ссылка1 [; Ссылка2 [; …]])
или
AGGREGATE(Функция; Условие; Массив [; k])

Функция – это обязательный аргумент. Индекс или ссылка на ячейку содержащую значение от 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 Возвращает k-ое по величине наибольшее значение
15 SMALL Возвращает k-ое по величине наименьшее значение
16 PERCENTILE.INC Возвращает персентиль от 0 до 1 включительно
17 QUARTILE.INC Возвращает квартиль от 0 до 1 включительно
18 PERCENTILE.EXC Возвращает персентиль от 0 до 1 исключая крайние значения
19 QUARTILE.EXC Возвращает квартиль от 0 до 1 исключая крайние значения

Как видно из таблицы, все функции присутствуют в отдельном виде в LibreOffice Calc. И если вам нужно уточнить их действие, то вы можете обратится к справке.

Условие – это обязательный аргумент. Индекс условия или ссылка на ячейку со значением от 0 до 7, определяющий какие значения будут игнорироваться при подсчете.

Индекс Применяемое условие
0 Игнорируются только встроенные функции SUBTOTAL и AGGREGATE
1 Игнорируются только скрытые строки и встроенные функции SUBTOTAL и AGGREGATE
2 Игнорируются только ошибки и встроенные функции SUBTOTAL и AGGREGATE
3 Игнорируются скрытые строки, ошибки, встроенные функции SUBTOTAL и AGGREGATE
4 Ничего не игнорируется
5 Игнорируются только скрытые строки
6 Игнорируются только ошибки
7 Игнорируются только скрытые строки и ошибки


Ссылка1 – это обязательный аргумент. Первый числовой аргумент (если диапазон задается списком значений внутри функции) или ссылка на ячейку, которая содержит этот числовой аргумент.

Ссылка2, 3, ... – это не обязательные аргументы, которые представляют собой числовой значения или ссылки на ячейку (до 253 аргументов), для которых вам необходимо рассчитать функцию AGGREGATE.
Массив – это обязательный аргумент. Массив может быть указан границами диапазона, именем именованного диапазона или заголовком столбца.

Замечу, что для использования названий столбцов, должна быть включена функция "Автоматический поиск заголовков столбцов и строк» (Сервис → Параметры → Calc → Вычисления).

k – Обязательный аргумент для следующих функций: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC. Это числовой аргумент, который должен соответствовать второму аргументу этих функций.

Следующие ошибки могут появляться при использовании функции:
Если аргумент k является необходимым, но не указан, то функция возвращает ошибку Err:511.

Если аргументы Функция и/или Условие указан не правильно (индекс не соответствует табличному), то функция возвращает ошибку Err:502.

Примеры




Файл с примерами

=AGGREGATE(4;2;A2:A9)
Возвращает максимальное значение в диапазоне A2:A9 =34, в то время как =MAX(A2:A9) возвращает ошибку.

=AGGREGATE(9;5;A5:C5)
Возвращает сумму в строке A5:C5 =29, даже если некоторые из столбцов скрыты.

=AGGREGATE(9;5;B2:B9)
Возвращает сумму в столбце B =115. Если какая-либо строка скрыта, то функция опустить её значение, например, если 7-ая строка скрыта, функция вернёт 95.

Если вам нужно применить функцию с диапазоном 3D, этот пример показывает, как это сделать.

=AGGREGATE(13;3;Sheet1.B2:B9:Sheet3.B2:B9)
Функция возвращает значения второго столбца сквозного (3D) диапазона листов 1:3 (который содержит те же данные) =8.

Вы можете использовать ссылку на ячейку или диапазон для любого аргумента в функции. В следующем примере показано, как это работает. Кроме того, это показывает, что вы можете использовать названия столбцов, чтобы задать массив.

=AGGREGATE(E3;E5;'Второй')
Если E3 =13 и E5 =5, функция возвращает моду из второго столбца, которая равна 10.
Обратите внимание, что если в столбце нет повторяющихся значений, функция MODE.SNGL возвращает ошибку.


Пример на листе 4 в фале с примерами не очень типичен, так как в нём нет ошибок и мы могли бы для суммы в этом случае использовать SUBTOTAL, но сам смысл я думаю понятен. Если у таблица является сборкой из разных таблиц, и в ней могут быть ошибки, то функция AGGREGATE — это именно то что вам нужно.

Дополнительная литература и источники: