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

Microsoft Excel

трюки • приёмы • решения

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

Одна из новых функций, представленных в 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 Пропускать скрытые строки и ошибочные значения

Третий аргумент функции АГРЕГАТ — ссылка на диапазон ячеек для данных, которые будут агрегированы.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ всегда пропускает скрытые данные, но только если скрытие является результатом автоматической фильтрации или сжатия очертания. Функция АГРЕГАТ работает подобным образом, но игнорирует данные в строках, которые были скрыты вручную. Заметьте, что эта функция не игнорирует данные в скрытых столбцах. Другими словами, функция АГРЕГАТ была предназначена для работы только с вертикальными диапазонами.

На рис. 113.1 показан пример того, как может быть использована функция АГРЕГАТ. Лист содержит предварительные и полученные на тестах оценки для восьми студентов. Обратите внимание, что Диана не прошла тест, поэтому ячейка С8 содержит ошибочное значение #Н/Д (указывающее на недоступность).

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

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

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

Источник: excelexpert.ru

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

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

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

  • Номер_функции [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] — позиция в массиве для функций: наибольшее, наименьшее, процентиль, квадратиль

Оставьте комментарий!

На сообщение “Функция АГРЕГАТ [AGGREGATE]” комментариев 18

Нужная функция! Спасибо огромное!

Читайте также:  В эксель функция если то

Пожалуйста, Ефим, пользуйтесь успешно!

Да, поинтереснее Subtotal

Спасибо Вам. Я бы никогда на такое название функции не обратил внимание. А функция нужная.

Красота! Спасибо, я даже и не подозревала, что есть что-то большее, чем промежуточные итоги

Очень нужная функция. Столько плюсов и жаль, что она не так часто встречается в чьих-то файлах. Вероятно, по незнанию

Я прям зачитался Вашим блогом. Спасибо, Вы расширяете мои знания!

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

Первый раз увидел такую полезную формулу. Я в восторге!

У меня при виде названия такой функции никогда таких и ассоциаций не возникало. А зря! Очень полезная

Интересная функция. Увидел в первый раз. Думаю, что будет полезна

Спасибо за просвещение, порой и не догадаешься что искать

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

Да уж, это покруче всяких промежуточных итогов!

Функция явно как-то в тени осталась!

Интересная функция, но малоизвестная

Чаще всего функцией Агрегат пользуюсь для выборки.
Например, получить данные отделов ОТД или ТКБ с окладом более 50000. Протянуть вниз и вправо

Источник: mirexcel.ru

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

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

Синтаксис

Ссылочная форма

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

Аргументы функции АГРЕГАТ описаны ниже.

Номер_функции — обязательный аргумент. Число от 1 до 19, определяющее функцию, которую необходимо использовать.

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

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

Пропуск вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ

Пропуск скрытых строк, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ

Пропуск значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ

Пропуск скрытых строк, значений ошибок, вложенных функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ

Пропуск скрытых строк

Пропуск значений ошибок

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

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

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

В случае функций, принимающих массив, “ссылка1” — это массив, формула массива или ссылка на диапазон ячеек, для которых необходимо вычислить агрегатное значение. “Ссылка2” — это второй аргумент, требуемый определенными функциями. Функции, которым необходим аргумент “ссылка2”, указаны ниже.

Замечания

При вводе аргумента “номер_функции” для функции АГРЕГАТ в ячейке листа появляется список всех функций, которые можно использовать в качестве аргументов.

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

Если одна или несколько ссылок представляют собой трехмерные ссылки, функция агрегат возвращает #VALUE! значение ошибки #ЗНАЧ!.

СТАТИСТИЧЕСКая функция разработана для столбцов данных или вертикальных диапазонов. Она не предназначена для строк данных или горизонтальных наборов данных. Например, если промежуточный итог по горизонтали используется с параметром 1, например Статистическая функция (1, 1, ссылка1;), то скрытие столбца не повлияет на статистическое значение итогового значения. Но скрытие строки в вертикальном диапазоне влияет на статистическое выражение.

Пример

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

Источник: support.office.com

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

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

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

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

Вид таблицы с данными:

Читайте также:  Функция суммеслимн в excel примеры

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

  • 1 – число, соответствующее функции СРЗНАЧ;
  • 3 – число, указывающее на способ расчета (не учитывать скрытые строки и коды ошибок);
  • B3:B13 – диапазон ячеек с данными для определения среднего значения.

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

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

Вид таблицы с данными:

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

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

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

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

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

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

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

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

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

  • номер_функции – обязательный для заполнения, принимает числовые значения от 1 до 19, где каждое значение соответствует определенной функции (например, 1 – СРЗНАЧ, 2 – СЧЁТ и т. д.) Полный перечень номеров и соответствующих им функций указан в справочной информации к функции АГРЕГАТ.
  • параметры – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 7, где:
  1. значения от 0 до 3 – пропуск вложенных функций АГРЕГАТ, промежуточные итоги (для всех случаев), а также скрытых строк (1), значений ошибок (2), скрытых строк и значений ошибок (3);
  2. число 4 – все значения учитываются;
  3. 5-7 – пропуск только скрытых строк, значений ошибок и скрытых строк и значений ошибок соответственно.
  • ссылка1 – обязательный для заполнения, принимает ссылку на диапазон ячеек с данными, над которыми требуется выполнить вычисления.
  • [ссылка2] – необязательный аргумент, принимает ссылку на диапазон ячеек с данными, требуемыми для вычислений некоторыми функциями (например, НАИБОЛЬШИЙ, КВАРТИЛЬ.ВКЛ и прочие).
  • массив – обязательный аргумент функции АГРЕГАТ формы массива, принимающий диапазон вычисляемых данных.
  • [k] – не обязательный для заполнения аргумент, принимающий числовое значение, указывающее на позицию в массиве для некоторых функций (например, НАИМЕНЬШЕЕ, НАИБОЛЬШЕЕ, ПРОЦЕНТИЛЬ.ВКЛ и прочие).
  1. Если [ссылка2] и последующие необязательные аргументы требуются для вычислений, но не указаны явно, функция АГРЕГАТ вернет код ошибки #ЧИСЛО!
  2. Если в качестве любого ссылочного параметра была передана трехмерная ссылка, результатом выполнения функции АГРЕГАТ будет код ошибки #ЧИСЛО!
  3. Функция АГРЕГАТ была добавлена в Excel начиная с версии 2010 года и предназначена для расширения функционала функций, определяемых первым аргументом (от 1 до 19), в частности для проведения расчета по видимым значениям, игнорирования возникающих ошибок.

Источник: exceltable.com

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

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

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

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

Сумма в строке состояния

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

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

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

Функция СУММ

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

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

Читайте также:  Excel функция поиск

Сумма произведений в Эксель

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

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

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

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

Сумма с условием

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

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

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

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

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

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

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

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

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

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

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

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

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

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

А следующие посты снова очень важные – это функции поиска и выбора данных. Если вы читаете этот пост – обязательно прочтите и следующий, это будет очень полезно!

Не забыли подписаться на блог? Сделайте это прямо сейчас, чтобы всегда первыми читать новые статьи!

Источник: officelegko.com

Использование функции АГРЕГАТ (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 Пропуск скрытых строк и значений ошибок

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

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

Источник: msoffice-prowork.com