Функция в excel агрегат

Microsoft Excel

хитрости • приемы • решения

Как в таблицах Excel применять функцию АГРЕГАТ

Одна из новых функций, представленных в Excel 2010, называется АГРЕГАТ. Вы можете использовать эту многоцелевую функцию для сложения значений, вычисления среднего, подсчета количества записей и т.д. Чем полезна эта функция? Что может игнорировать скрытые ячейки и значения ошибок.

Первый аргумент функции АГРЕГАТ — это значение от 1 до 19, которое указывает тип вычисления. Тип расчета — это, по сути, одна из функций Excel. В таблице 113.1 перечислены эти значения и функции, которые они имитируют.

Таблица 113.1. Значения первого аргумента функции АГРЕГАТ

Второй аргумент функции AGGREGATE — это целое число от 0 до 7, которое указывает, как обрабатывать скрытые ячейки и ошибки. Таблица 113.2 содержит описание всех опций.

Таблица 113.2. Значения второго аргумента функции АГРЕГАТ

Третий аргумент функции AGGREGATE — это ссылка на диапазон ячеек для агрегированных данных.

INTERMEDIATE.TOTALS всегда пропускает скрытые данные, но только если скрытие является результатом автоматической фильтрации или сжатия контуров. Функция AGGREGATE работает аналогичным образом, но игнорирует данные в строках, которые были вручную скрыты. Обратите внимание, что эта функция не игнорирует данные в скрытых столбцах. Другими словами, функция UNIT была разработана для работы только с вертикальными диапазонами.

На рис. 113.1 показывает пример того, как можно использовать функцию UNIT. В листе указаны предварительные и контрольные оценки восьми студентов. Обратите внимание, что Диана не прошла тест, поэтому ячейка C8 содержит неправильное значение # N / A (указывающее на недоступность).

В ячейке D11 хранится формула, в которой для вычисления среднего изменения используется функция СРЕДНЕЕ. Эта формула возвращает ошибку: = СРЕДНЕЕ (D2: D8). Формула в ячейке D12 использует функцию АГРЕГАТ с возможностью игнорировать неправильные значения: = АГРЕГАТ (1; 6; D2: D8) .

Рис. 113.1. Функцию АГРЕГАТ можно использовать для вычисления среднего значения, если диапазон содержит неверные значения

Помните, что функция АГРЕГАТ работает только в Excel 2010. Если книга, в которой используется эта функция, открыта в более ранней версии Excel, формула выдаст ошибку.

Функция в excel агрегат

Необработанные данные не всегда выглядят идеально. В результате расчетов могут возникать ошибки разного типа, заменить которые альтернативным решением не всегда возможно. Кроме того, часто возникают ситуации, когда расчет нужно производить только для отображаемых (видимых) ячеек. Все это сильно усложняет процесс расчета.

Функция АГРЕГАТ впервые доступна в Excel 2010. Ее можно рассматривать как расширенную версию набора статистических функций, определяющих среднее, максимальное, минимальное и т.д. Значения, поскольку она позволяет выполнять вычисления, игнорируя не только значения ошибок (эту проблему легко решить с помощью функции ЕСЛИ ОШИБКА и формулы массива), а также скрытых ячеек.

• Function_num [function_num] находится в диапазоне от 1 до 19:
  
  1 — СМИ
  
  2 — COUNT
  
  3 — COUNT
  
  4 — МАКСИМУМ [МАКСИМУМ]
  
  5 — MIN [MIN]
  
  6 — ПРОДУКТ
  
  7 — СТАНДОТКЛОН [СТАНДАРТНЫЙ СТАНДАРТ]
  
  8 — STDEVP [STDEVP]
  
  9 — SUM [SUM]
  
  10 — DISP.B [VAR.S]
  
  11 — DISP.G [VAR.P]
  
  12 — МЕДИАНА
  
  13 — РЕЖИМ ПЕРВЫЙ [MODE.SNGL]
  
  14 — БОЛЬШОЙ [БОЛЬШОЙ]
  
  15 — МАЛЕНЬКИЙ
  
  16 — PERCENTILE INC. [PERCENTILE INC]
  
  17 — КВАРТИЛЬ НА [QUARTILE.INC]
  
  18 — ПРОЦЕНТИЛЬНОЕ ИЗБЫТОЕ [ПРОЦЕНТИЛЬ ИСКЛ]
  
  19 — QUARTILE.EXC [QUARTILE.EXC]
• Параметры [параметры] — способ обработки ошибок и скрытых ячеек, диапазон от 0 до 7:
  
  0 (по умолчанию) — пропустить вложенные ИТОГИ ПРОМЕЖУТОЧНЫЙ и ЕДИНИЦЫ
  
  1 — пропускать скрытые строки и вложенные функции INTERMEDIATE.TOTAL и AGGREGATE
  
  2 — Игнорировать вложенные значения ошибок в INTERMEDIATE.TOTALS и UNITS
  
  3 — Пропускать скрытые строки, значения ошибок, ПРОМЕЖУТОЧНЫЕ функции. ИТОГО вложенных и СОВМЕСТНО
  
  4 — Ничего не упускайте
  
  5 — Пропустить скрытые строки
  
  6 — Пропустить значения ошибок
  
  7 — Пропускать скрытые строки и значения ошибок
• Array [array] — диапазон обрабатываемых данных
• [k] — позиция в массиве для функций: наибольшая, наименьшая, процентиль, квадратичная

Функция АГРЕГАТ

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

Форма массива

Аргументы функции AGGREGATE описаны ниже.

FunctionNumber Обязательно. Число от 1 до 19, обозначающее используемую функцию.

Обязательные параметры. Числовое значение, указывающее, какие значения пропускать при оценке функции.

Примечание. Функция не игнорирует скрытые строки, вложенные итоги или вложенные агрегаты, если аргумент массива включает вычисления, например: = AGGREGATE (14; 3; A1: A100 * (A1: A100> 0); 1)

Пропуск вложенных функций TOTAL.INTERMEDIATES и UNITS

Пропускать скрытые строки, вложенные функции INTERMEDIATE.TOTAL и AGGREGATE

Пропуск значений ошибок, вложенные функции INTERMEDIATE.TOTAL и UNITS

Пропускать скрытые строки, значения ошибок, вложенные функции INTERMEDIATE.TOTALS и AGGREGATE

Пропускать скрытые строки

Пропустить значения ошибок

Пропускать скрытые строки и значения ошибок

Ref1 Обязательно. Первый числовой аргумент функций, которые принимают несколько числовых аргументов, для которых вы хотите получить агрегированное значение.

Подключение2,. — необязательные аргументы. Числовые аргументы от 2 до 253, для которых вы хотите вычислить совокупное значение.

Для функций, которые принимают массив, «ссылка1» — это массив, формула массива или ссылка на диапазон ячеек, для которого вы хотите вычислить агрегированное значение. Ref2 — второй аргумент, требуемый некоторыми функциями. Функции, для которых требуется аргумент «ссылка2», перечислены ниже.

Замечания

Когда вы вводите аргумент function_number для функции AGGREGATE, в ячейке рабочего листа появляется список всех функций, которые могут использоваться в качестве аргументов.

Если второй аргумент ref требуется, но не указан, агрегат возвращает #VALUE! Если указанная позиция находится перед первым или после последнего элемента в поле, формула возвращает ошибку # ССЫЛКА!.

Если одна или несколько ссылок являются 3D-ссылками, агрегирование возвращает #VALUE! #VALUE! Значение ошибки!.

Функция STATISTICA предназначена для вертикальных столбцов данных или диапазонов. Он не предназначен для горизонтальных строк данных или наборов данных. Например, если используется горизонтальный промежуточный итог с параметром 1, такой как Aggregate (1, 1, ref1;), скрытие столбца не повлияет на совокупное значение итога. Но скрытие строки в вертикальном диапазоне влияет на агрегат.

Пример

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы просмотреть результаты формул, выберите их и нажмите F2, затем нажмите Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.

Функция АГРЕГАТ для работы с ошибками в ячейках таблицы Excel

Функция АГРЕГАТ в Excel предназначена для выполнения различных вычислений с использованием встроенных функций (определяемых на основе одного из параметров) и возвращает агрегированный результат. Эта функция объединяет самые популярные функции Excel для работы с массивами данных. Синтаксис рассматриваемой функции предусматривает возможность пропуска элементов массива со значениями ошибок или скрытыми строками.

Примеры использования функции АГРЕГАТ в Excel

Пример 1. На занятиях проводился тест по математике. Необходимо определить среднюю оценку учащимся, прошедшим тестирование. В отличие от некоторых фамилий, оценка за недопустимость или отсутствие не выставляется.

Просмотр таблицы данных:

Для расчета используем следующую формулу:

• 1 — номер, соответствующий функции МЕДИА;
• 3 — число, обозначающее метод расчета (без учета скрытых линий и кодов ошибок);
• B3: B13 — это диапазон ячеек данных для определения среднего значения.

В результате формула вернула правильное среднее значение, игнорируя ошибки # N/A.

Как пропустить ошибки в ячейках при суммировании в Excel

Просмотр таблицы данных:

Для ее решения удобно использовать функцию АГРЕГАТ, поскольку она позволяет исключить коды ошибок из расчетов.

Определим показатели первого счетчика по формуле:

• 9 — указатель на функцию СУММ;
• 2 — указатель на метод расчета (без учета значений ошибок);
• B3: B13 — это ссылка на диапазон значений в кВт.

Сделаем аналогичный расчет для второго счетчика, передав ячейки C3: C13 в качестве ссылки. В результате получаем следующие значения:

Например, чтобы вычислить эти значения с помощью обычной функции СУММ, вам понадобится более сложный и громоздкий ввод (формула массива — для выполнения нужно нажать ctrl + shift + enter):

Описание синтаксиса функции АГРЕГАТ в Excel

Рассматриваемая функция имеет две формы синтаксиса:

= UNIT (номер_функции; параметры; ссылка1; [ссылка2];.)

= АГРЕГАТ (номер_функции; параметры; массив; [k])

• function_number — обязательный, принимает числовые значения от 1 до 19, где каждое значение соответствует определенной функции (например, 1 — AVERAGE, 2 — COUNT и т д.). Полный список чисел и соответствующих им функций указан в файле справочная информация для функции UNIT.
• параметры — обязательный, принимает числовые значения от 0 до 7, где:

1. значения от 0 до 3 — пропуск вложенных функций АГРЕГАТ, промежуточных итогов (для всех случаев), а также скрытых строк (1), значений ошибок (2), скрытых строк и значений ошибок (3);
2. цифра 4 — учитываются все значения;
3. 5-7: Пропускайте только скрытые строки, значения ошибок и скрытые строки и значения ошибок соответственно.

• ref1 — обязательный, принимает ссылку на диапазон ячеек с данными, по которым вы хотите произвести вычисления.
• [ref2] — необязательный аргумент, принимает ссылку на диапазон ячеек с данными, необходимыми для вычислений некоторыми функциями (например, LARGE, QUARTILE.INC и другими).
• array — обязательный аргумент функции AGGREGATE в виде массива, который принимает диапазон вычисляемых данных.
• [k] — необязательный аргумент, который принимает числовое значение, указывающее позицию в массиве для некоторых функций (например, LOWEST, LARGEST, PERCENTILE.INC и других).

1. Если для вычислений требуются [ref2] и следующие необязательные аргументы, но они не указаны явно, функция АГРЕГАТ возвращает код ошибки # ЧИСЛО!
2. Если в качестве ссылочного параметра была передана трехмерная ссылка, результатом выполнения функции АГРЕГАТ будет код ошибки # ЧИСЛО!
3. Функция АГРЕГАТ была добавлена ​​в Excel начиная с версии 2010 года и предназначена для расширения функциональности функций, определенных первым аргументом (с 1 до 19), в частности, для выполнения вычислений на основе видимых значений, игнорируя ошибки что происходит проверить.

Сумма в Excel: от простого к эффективному

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

Как новички рассчитывают сумму значений? Они пишут формулу, в которой все термины указаны со знаком «+», и довольны результатом. Но работает, если есть 5-10 терминов. Но что, если их больше?

Самый простой способ — выделить все термины мышью и увидеть результат в строке состояния.

Количество в строке состояния

Этот способ хорош, если вам нужно узнать сумму, не используя ее в других формулах и таблицах. А если результат сложения должен отображаться на листе, вы можете использовать функции суммирования.

Функции суммирования в Эксель

Простейшая функция СУММ выглядит так: = СУММ (аргумент1; аргумент2;…). Его можно использовать, когда есть список ячеек, один массив, несколько массивов для добавления. Аргументами могут быть числа, ссылки на ячейки с числами, диапазоны. В следующем примере я суммировал заработную плату сотрудников отделов экономики и продаж организации:

СУММ (функция СУММ)

Если вам нужно вычислить сумму произведений определенных чисел, используйте функцию = СУММПРОИЗВ (массив1; [массив2];…). Функция умножит соответствующие элементы массивов, и результаты умножения будут добавлены. Понятно, что все массивы, указанные в функции, должны иметь одинаковое количество строк и столбцов.

В приведенном ниже примере зарплаты сотрудников указаны в долларах США и по обменному курсу. Вы должны рассчитать общую зарплату всех сотрудников в валюте их страны. Эта формула будет выглядеть так: = СУММПРОИЗВ (зарплаты; курсы). Функция умножит каждую зарплату на ставку и сложит все части друг друга:

Сумма вакансий в Excel

Суммирование с условием в Excel

Часто необходимо добавлять только те значения, которые удовлетворяют определенным условиям. Для этого используйте функцию = СУММЕСЛИ (Диапазон, Критерий, [Диапазон суммирования]). Как видите, для этого можно указать 3 аргумента:

• Диапазон — это массив, в котором проверяется условие. Это обязательная тема;
• Критерий: условие выбора значений (обязательный аргумент). Ячейки «Диапазон» будут проверены по этому критерию. Если вы ищете равенство определенному числу, просто укажите это число в качестве аргумента. Остальные критерии заключены в кавычки. Например:
  • 12 — значение двенадцать
  • «> 12» — значение больше двенадцати
  • «Обувь» — значение равно «Обувь»
• Диапазон суммирования — это массив суммируемых значений. Он должен иметь такое же количество строк и столбцов, что и «Диапазон». Это необязательный аргумент. Если не указан, значения массива Range будут суммированы».

В этом примере я добавил зарплаты всех сотрудников отдела логистики:

Добавить с условием

Но что, если вам нужно задать несколько условий? Excel отлично справится с этой задачей, воспользуемся функцией:

СУММЕСЛИ (диапазон суммы; диапазон условия1; условие1; диапазон условия2; условие2 ; …) .

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

Сумма с несколькими условиями

Мощная и гибкая функция АГРЕГАТ

А теперь представляю вам обещанную новинку. Функция АГРЕГАТ впервые появилась в Excel 2010 и сразу решила многие мои проблемы. Что, если вы просто хотите просуммировать видимые ячейки? Функция СУММ суммирует все ячейки в диапазоне, даже если они скрыты. Что делать?

Что делать, если в массиве есть просчеты, которые нужно игнорировать? Эти и многие другие задачи решает функция: = АГРЕГАТ (номер функции; параметр; матрица;…). У функции есть аргументы:

1. Номер функции: указывает, что именно необходимо вычислить. Например, количество значений, сумма, максимум и т.д. При вводе формулы появится подсказка:

Функция устройства, первая подсказка

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

Функция устройства, согласно подсказке

1. Массив — группа ячеек, для которой производится расчет
2. Другие параметры: в зависимости от значения первого аргумента функции могут потребоваться дополнительные аргументы

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

Использование функции АГРЕГАТ (AGGREGATE) в Excel

Функция АГРЕГАТ впервые появилась в Excel 2010, поэтому она относительно молода, чрезвычайно полезна и, как это часто бывает, недооценивается пользователями процессора электронных таблиц, обычно из-за плохого понимания ее практического применения.

Эта функция на самом деле является многоцелевым решением, которое можно использовать для суммирования и подсчета записей вместо сложных формул массива.

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

Действительно есть, но в отличие от функций, которые дает UNIT, он позволяет пользователю устанавливать дополнительные параметры (таблица также взята из справочника).

Если вам нужно рассчитать среднюю оценку для группы на основе теста, который написали ученики, и некоторые ячейки могут содержать ошибки, например, если нет данных по результатам теста, функция ЕДИНИЦЫ автоматически пропустит неверные значения.

Точно так же вы можете установить пропуск скрытых строк или указать другие параметры.