Aggregate excel как пользоваться
Функция АГРЕГАТ (AGGREGATE) впервые появилась в Excel версии 2010, поэтому является сравнительно молодой, чрезвычайно полезной и, как часто бывает, недооцененной пользователями табличного процессора, как правило, из-за непонимания ее применения на практике.
Эта функция является собой фактически многоцелевое решение, которое можно использовать для суммирования, подсчета записей, вместо сложных формул массива.
Если посмотреть на информацию по данной функции из справочника, то можно сделать вывод, что функция для расчета использует другие функции Excel.
| Номер_функции | Функция |
| 1 | СРЗНАЧ |
| 2 | СЧЁТ |
| 3 | СЧЁТЗ |
| 4 | МАКС |
| 5 | МИН |
| 6 | ПРОИЗВЕД |
| 7 | СТАНДОТКЛОН.В |
| 8 | СТАНДОТКЛОН.Г |
| 9 | СУММ |
| 10 | ДИСП.В |
| 11 | ДИСПР |
| 12 | МЕДИАНА |
| 13 | МОДА.ОДН |
| 14 | НАИБОЛЬШИЙ |
| 15 | НАИМЕНЬШИЙ |
| 16 | ПРОЦЕНТИЛЬ.ВКЛ |
| 17 | КВАРТИЛЬ.ВКЛ |
| 18 | ПРОЦЕНТИЛЬ.ИСКЛ |
| 19 | КВАРТИЛЬ.ИСКЛ |
Это действительно так, но в отличие от функций, которые выступают донорами для АГРЕГАТ, она позволяет пользователю задавать дополнительные параметры (таблица также взята из справочника).
| Параметр | Поведение |
| 0 или опущен | Пропуск вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 1 | Пропуск скрытых строк, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 2 | Пропуск значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 3 | Пропуск скрытых строк, значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 4 | Без пропуска |
| 5 | Пропуск скрытых строк |
| 6 | Пропуск значений ошибок |
| 7 | Пропуск скрытых строк и значений ошибок |
Если необходимо рассчитать среднюю оценку по группе, на основе контрольной работы, которую написали студенты, а некоторые ячейки могут содержать ошибки, например, если нет данных о результатах контрольной, то функция АГРЕГАТ, позволит в автоматическом режиме пропускать ошибочные значения.
Аналогично можно установить пропуск скрытых строк, или указать иные параметры.
Returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.
Syntax
Reference form
AGGREGATE(function_num, options, ref1, [ref2], …)
Array form
AGGREGATE(function_num, options, array, [k])
The AGGREGATE function syntax has the following arguments:
Function_num Required. A number 1 to 19 that specifies which function to use.
Function_num
Options Required. A numerical value that determines which values to ignore in the evaluation range for the function.
Note: The function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, for example: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)
Ignore nested SUBTOTAL and AGGREGATE functions
Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
Ignore error values, nested SUBTOTAL and AGGREGATE functions
Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
Ignore hidden rows
Ignore error values
Ignore hidden rows and error values
Ref1 Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value.
Ref2. Optional. Numeric arguments 2 to 253 for which you want the aggregate value.
For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:
Remarks
Function_num :
As soon as you type the function_num argument when you enter the AGGREGATE function into a cell on the worksheet, you will see a list of all functions that you can use as arguments.
The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
Одна из новых функций, представленных в Excel 2010, называется АГРЕГАТ. Вы можете использовать эту многоцелевую функцию для суммирования значений, вычисления среднего, подсчета количества записей и многого другого. Что делает эту функцию полезной? То, что она может игнорировать скрытые ячейки и значения ошибок.
Первый аргумент функции АГРЕГАТ представляет собой значение от 1 до 19, определяющее тип вычисления. Тип вычисления, в сущности, является одной из функций Excel. В табл. 113.1 приведены список этих значений и имитируемые ими функции.
Таблица 113.1. Значения первого аргумента функции АГРЕГАТ
| Значение | Функция |
|---|---|
| 1 | СРЗНАЧ |
| 2 | СЧЁТ |
| 3 | СЧЁТЗ |
| 4 | МАКС |
| 5 | МИН |
| 6 | ПРОИЗВЕД |
| 7 | СТАНДОТКЛОН.В |
| 8 | СТАНДОТКЛОН.Г |
| 9 | СУММ |
| 10 | ДИСП.В |
| 11 | ДИСП.Г |
| 12 | МЕДИАНА |
| 13 | МОДА.ОДН |
| 14 | НАИБОЛЬШИЙ |
| 15 | НАИМЕНЬШИЙ |
| 16 | ПРОЦЕНТИЛЬ.ВКЛ |
| 17 | КВАРТИЛЬ.ВКЛ |
| 18 | ПРОЦЕНТИЛЬ.ИСКЛ |
| 19 | КВАРТИЛЬ.ИСКЛ |
Второй аргумент функции АГРЕГАТ — это целое число от 0 до 7, которое указывает, как обрабатывать скрытые ячейки и ошибки. В табл. 113.2 содержится описание всех вариантов.
Таблица 113.2. Значения второго аргумента функции АГРЕГАТ
| Опция | Поведение |
|---|---|
| 0 или пропущен | Пропускать вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 1 | Пропускать скрытые строки, а также вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 2 | Пропускать ошибочные значения, а также вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 3 | Пропускать скрытые строки, ошибочные значения, а также вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 4 | Ничего не пропускать |
| 5 | Пропускать скрытые строки |
| 6 | Пропускать ошибочные значения |
| 7 | Пропускать скрытые строки и ошибочные значения |
Третий аргумент функции АГРЕГАТ — ссылка на диапазон ячеек для данных, которые будут агрегированы.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ всегда пропускает скрытые данные, но только если скрытие является результатом автоматической фильтрации или сжатия очертания. Функция АГРЕГАТ работает подобным образом, но игнорирует данные в строках, которые были скрыты вручную. Заметьте, что эта функция не игнорирует данные в скрытых столбцах. Другими словами, функция АГРЕГАТ была предназначена для работы только с вертикальными диапазонами.
Ячейка D11 хранит формулу, которая использует функцию СРЗНАЧ для расчета среднего изменения. Эта формула возвращает ошибку: =СРЗНАЧ(D2:D8) . Формула в ячейке D12 использует функцию АГРЕГАТ с возможностью игнорировать ошибочные значения: =АГРЕГАТ(1;6;D2:D8) .
Рис. 113.1. Функция АГРЕГАТ может применяться для расчета среднего, когда диапазон содержит ошибочные значения
Помните, что функция АГРЕГАТ работает только в Excel 2010. Если книга, использующая эту функцию, будет открыта в какой-либо из предыдущих версий Excel, формула выдаст ошибку.
Функция АГРЕГАТ впервые появилась в Excel версии 2010, поэтому является сравнительно молодой, чрезвычайно полезной и, как часто бывает, недооцененной пользователями табличного процессора, как правило, из-за непонимания ее применения на практике.
Описание функции АГРЕГАТ
Возвращает агрегатный результат вычислений по списку или базе данных. С помощью функции АГРЕГАТ можно применять различные агрегатные функции к списку или базе данных с возможностью пропускать скрытые строки и значения ошибок.
Например, если необходимо рассчитать среднюю оценку по группе, на основе контрольной работы, которую написали студенты, а некоторые ячейки могут содержать ошибки, например, если нет данных о результатах контрольной, то функция АГРЕГАТ, позволит в автоматическом режиме пропускать ошибочные значения.
Синтаксис
Ссылочная форма
Форма массива
Аргументы
Обязательный аргумент. Число от 1 до 19, определяющее функцию, которую необходимо использовать.
| Номер_функции | Функция |
|---|---|
| 1 | СРЗНАЧ |
| 2 | СЧЁТ |
| 3 | СЧЁТЗ |
| 4 | МАКС |
| 5 | МИН |
| 6 | ПРОИЗВЕД |
| 7 | СТАНДОТКЛОН.В |
| 8 | СТАНДОТКЛОН.Г |
| 9 | СУММ |
| 10 | ДИСП.В |
| 11 | ДИСПР |
| 12 | МЕДИАНА |
| 13 | МОДА.ОДН |
| 14 | НАИБОЛЬШИЙ |
| 15 | НАИМЕНЬШИЙ |
| 16 | ПРОЦЕНТИЛЬ.ВКЛ |
| 17 | КВАРТИЛЬ.ВКЛ |
| 18 | ПРОЦЕНТИЛЬ.ИСКЛ |
| 19 | КВАРТИЛЬ.ИСКЛ |
Обязательный аргумент. Числовое значение, определяющее, какие значения при вычислении функции следует пропускать.
| Параметр | Поведение |
|---|---|
| 0 или опущен | Пропуск вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 1 | Пропуск скрытых строк, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 2 | Пропуск значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 3 | Пропуск скрытых строк, значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ |
| 4 | Без пропуска |
| 5 | Пропуск скрытых строк |
| 6 | Пропуск значений ошибок |
| 7 | Пропуск скрытых строк и значений ошибок |
Обязательный аргумент. Первый числовой аргумент для функций с несколькими числовыми аргументами, для которых необходимо найти агрегатное значение.
Необязательные аргументы. Числовые аргументы от 2 до 253, для которых необходимо вычислить агрегатное значение.
Второй аргумент, требуемый определенными функциями. Функции, которым необходим аргумент, следующие:
Читайте также: