В прошлом году я написал статью Сумма, автосумма и некоторые другие секреты LibreOffice Calc, в которой попытался показать основные возможности суммирования в LibreOffice Calc. Тогда, в той статье, я говорил что нет справочного материала по функции AGGREGATE в справке LibreOffice, и отсылал в справку MS Office к функции АГРЕГАТ, так как она совместима с функцией Calc. Позже уже, я сделал патч в английскую версию Справки, но, к сожалению, у команды локализации пока нет времени заниматься переводом. Эта статья, хотя и основана на моём патче, не является его переводом, но пытается предоставить больше информации в более простом, понятном виде об этой функции.
или
Функция – это обязательный аргумент. Индекс или ссылка на ячейку содержащую значение от 1 до 19, задающее применяемую функцию для возвращаемого значения в соответствии со следующей таблицей.
Как видно из таблицы, все функции присутствуют в отдельном виде в LibreOffice Calc. И если вам нужно уточнить их действие, то вы можете обратится к справке.
Условие – это обязательный аргумент. Индекс условия или ссылка на ячейку со значением от 0 до 7, определяющий какие значения будут игнорироваться при подсчете.
Ссылка1 – это обязательный аргумент. Первый числовой аргумент (если диапазон задается списком значений внутри функции) или ссылка на ячейку, которая содержит этот числовой аргумент.
Ссылка2, 3, ... – это не обязательные аргументы, которые представляют собой числовой значения или ссылки на ячейку (до 253 аргументов), для которых вам необходимо рассчитать функцию AGGREGATE.
Массив – это обязательный аргумент. Массив может быть указан границами диапазона, именем именованного диапазона или заголовком столбца.
Замечу, что для использования названий столбцов, должна быть включена функция "Автоматический поиск заголовков столбцов и строк» (Сервис → Параметры → Calc → Вычисления).
k – Обязательный аргумент для следующих функций: LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, QUARTILE.EXC. Это числовой аргумент, который должен соответствовать второму аргументу этих функций.
Следующие ошибки могут появляться при использовании функции:
Если аргумент k является необходимым, но не указан, то функция возвращает ошибку Err:511.
Если аргументы Функция и/или Условие указан не правильно (индекс не соответствует табличному), то функция возвращает ошибку Err:502.
Основная задача функции AGGREGATE вернуть значение из диапазона, который может иметь скрытые строки, ошибки, результаты других функции SUBTOTAL и AGGREGATE. Я говорю «значение», потому что AGGREGATE может возвращать не только сумму видимых ячеек, но и находить минимальное/максимальное значение, подсчитывать количество ячеек и многое другое.
Сразу хочу заметить, что функция предназначена для работы с Автофильтром и при использовании её для других целей, можно получать неожиданные результаты. Например, если вы используете её для вертикального диапазона, но без Автофильтра, то пересчет значения при скрытии строк не будет работать. При применении к горизонтальному диапазону, она не определяет скрытые столбцы, хотя может игнорировать ошибки в ячейках или результаты SUBTOTAL и 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 возвращает ошибку.
=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 — это именно то что вам нужно.
Комментариев нет:
Отправить комментарий