Функция в excel агрегат – Все про Эксель

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

Синтаксис

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

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

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

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

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

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

Номер_функции

Функция

1

СРЗНАЧ

2

СЧЁТ

3

СЧЁТЗ

4

МАКС

5

МИН

6

ПРОИЗВЕД

7

СТАНДОТКЛОН.В

8

СТАНДОТКЛОН.Г

9

СУММ

10

ДИСП.В

11

ДИСПР

12

МЕДИАНА

13

МОДА.ОДН

14

НАИБОЛЬШИЙ

15

НАИМЕНЬШИЙ

16

ПРОЦЕНТИЛЬ.ВКЛ

17

КВАРТИЛЬ.ВКЛ

18

ПРОЦЕНТИЛЬ.ИСКЛ

19

КВАРТИЛЬ.ИСКЛ

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

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

Параметр

Поведение

0 или опущен

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

1

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

2

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

3

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

4

Без пропуска

5

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

6

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

7

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

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

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

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

Функция

НАИБОЛЬШИЙ(массив;k)

НАИМЕНЬШИЙ(массив;k)

ПРОЦЕНТИЛЬ.ВКЛ(массив;k)

КВАРТИЛЬ.ВКЛ(массив;кварт)

ПРОЦЕНТИЛЬ.ИСКЛ(массив;k)

КВАРТИЛЬ.ИСКЛ(массив;кварт)

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

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

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

Пример 1.

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

=АГРЕГАТ(1;3;B3:B13)

Описание параметров:

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

Полученный результат:

АГРЕГАТ.

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

Описание

Microsoft Excel функция АГРЕГАТ позволяет применять такие функции, как СРЕДНИЙ, СУММ, СЧЁТ, МАКС или МИН, и игнорировать ошибки или скрытые строки.
Функция АГРЕГАТ – встроенная функция в Excel, относящаяся к категории математических / тригонометрических функций.
Её можно использовать как функцию рабочего листа (WS) в Excel.
Как функцию рабочего листа, функция АГРЕГАТ может быть введена как часть формулы в ячейке рабочего листа. Она была создана Microsoft для устранения ограничений условного форматирования. Некоторое условное форматирование не может применяться, если в диапазоне есть ошибки.
Функция Excel АГРЕГАТ позволяет игнорировать ошибки или скрытые строки.

АГРЕГАТ (функция АГРЕГАТ)

​Смотрите также​ использовать формулу массива.​ клавишу ВВОД. При​ периодов, за которые​CLOSINGBALANCEYEAR​ функция возвращает значения​AVERAGEX​ Excel, и указываются​ данные, с таблицей​ “Автосумма” или путем​

Синтаксис

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

​ период профилактического обслуживания​

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

​ часто хранятся в​

​ работающая для всех​ ее текст может​

  • ​ столбцов, чтобы видеть​​ вычислить агрегатное значение.​КВАРТИЛЬ.ИСКЛ​Возвращает агрегатный результат вычислений​ Вот такую:​ необходимости измените ширину​

​ собственность амортизируется (иногда​

​Функция вычисляет значение на​

​ из одного неотфильтрованного​

​Функция определяет среднее арифметическое​

​ ссылки на реализацию​

​ категорий, а затем​

​ создания формулы. Также​

​ в конце месяца?​

​ таблицу, отличную фактов​

​ данных в группе.​

​ содержать неточности и​

​ все данные.​

​ “Ссылка2” — это​

​Параметры​

​ по списку или​

​=​

​ столбцов, чтобы видеть​

​ называемое периодом амортизации).​

​ конечную дату календаря​

​ столбца, и результат​

​ для набора выражений,​

​ этих функций в​

​ создать формулу.​

​ можно создавать Неявные​

​Соотношения, использующие средние значения​

​ или Таблица значений.​

​ Например, если создаются​

​ грамматические ошибки. Для​

​#ДЕЛ/0!​

​ второй аргумент, требуемый​

​     — обязательный аргумент.​

​ базе данных. С​

​ИНДЕКС​

​ все данные.​

​Период​

​ данного периода.​

​ будет таким же,​

​ вычисленных в таблице.​

​ Power Pivot. DAX-версия​

​Дополнительные сведения о создании​

  • ​ меры с помощью​​ за период времени​ Обычно таблице «категории»​ итоговые показатели за​ нас важно, чтобы​82​

    ​ определенными функциями. Функции,​​ Числовое значение, определяющее,​ помощью функции АГРЕГАТ​($B$2:$B$16;​Данные​    — обязательный аргумент. Период,​​OPENINGBALANCEMONTH​

​ как в приложении​

​COUNTAX​

​ этих функций во​

​ формул с подстановками​ стандартных агрегатов в​

​   Каков был процентный рост​

​ будет связана с​ год, то это​ эта статья была​

​72​

​ которым необходим аргумент​ какие значения при​ можно применять различные​

​НАИМЕНЬШИЙ​

​Описание​ для которого требуется​OPENINGBALANCEQUARTER​

​ Excel, в котором​

​Функция подсчитывает набор выражений,​

​ многом похожа на​

​ см. в статье​

​ сводной таблице в​

​ или снижение продаж​

​ данными фактов по​

​ агрегат. Если создается​ вам полезна. Просим​

  • ​65​​ “ссылка2”, указаны ниже.​ вычислении функции следует​ агрегатные функции к​(​1 000 000р.​ вычислить амортизацию. Для​

  • ​OPENINGBALANCEYEAR​​ всегда просто суммируются​ вычисленных в таблице.​ Excel-версию с незначительными​ Подстановка в формулах​ Excel.​

    ​ по сравнению с​ какой-либо ключ. Например​ соотношение этого года​ вас уделить пару​30​Функция​ пропускать.​ списку или базе​ЕСЛИ​Начальная стоимость​ представления периода должна​Функция вычисляет значение на​

​ значения в столбце​

​COUNTBLANK​

​ различиями в синтаксисе​

​ PowerPivot.​

​Во время разработки сводной​

​ тем же периодом​

​ предположим, что вы​

Замечания

​ с предыдущим годом​ ​ секунд и сообщить,​

  • ​95​НАИБОЛЬШИЙ(массив;k)​Примечание:​ данных с возможностью​($E$2=A2:A16;​100 000р.​ использоваться та же​

​ конечную дату календаря​

  • ​ Amount. Тем не​Функция подсчитывает количество пустых​ и обработке некоторых​Новая функция Power Pivot​

  • ​ таблицы в раздел​ прошлого года?​ найдете, что данные​ и данные представляются​

​ помогла ли она​

  • ​#ЧИСЛО!​НАИМЕНЬШИЙ(массив;k)​ Функция не игнорирует скрытые​ пропускать скрытые строки​СТРОКА​Остаточная стоимость​ единица измерения, что​ периода, предшествующего данному.​ менее в Power​ значений в столбце.​ типов данных.​ является возможность применить​ столбцов и строк​Сгруппированные минимальные и максимальные​ содержат коды продуктов,​ в виде процентов,​

Пример

​ вам, с помощью​63​ПРОЦЕНТИЛЬ.ВКЛ(массив;k)​ строки, вложенные итоги​ и значения ошибок.​(B2:B16)-1;””);​15​ и для аргумента​TOTALMTD​ Pivot формула интерпретируется​COUNTX​Функция​ фильтры к столбцам​

​ сводной таблицы для​

​ значения​

​ но не имена​

​ то это другой​

​ кнопок внизу страницы.​

​31​

​КВАРТИЛЬ.ВКЛ(массив;кварт)​

​ или вложенные агрегаты,​

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

​СТРОКА​

​Срок эксплуатации в годах​

​ “время_эксплуатации”.​

​TOTALYTD​

​ как “Получить значение​

​Функция подсчитывает общее количество​

​Использование​

​ и таблицам данных,​

​ группирования данных перетаскиваются​

​   Какие районы сбыта были​

​ продуктов или их​

​ тип агрегата.​

​ Для удобства также​

​53​

​ПРОЦЕНТИЛЬ.ИСКЛ(массив;k)​

​ если аргумент массива​

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

​()-5))​Формула​Месяцы​

​TOTALQTD​

​ в столбце Amount​

​ строк в таблице.​AVERAGE​ не только в​

​ поля, представляющие группировки,​

​ ведущими для каждой​

​ категории. Чтобы добавить​Метод группировки данных определяется​ приводим ссылку на​96​КВАРТИЛЬ.ИСКЛ(массив;кварт)​

​ включает вычисления, например:​

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

​Ее надо ввести следующим​Описание​

​    — необязательный аргумент. Количество​

​Функция вычисляет значение для​

​ для каждой строки​COUNTROWS​Возвращает среднее арифметическое всех​

​ пользовательском интерфейсе и​

support.office.com>

#2. СЧЁТ

Синтаксис: =СЧЁТ(значение1;[значение2];…)

значение1 — обязательный аргумент.

С помощью этой функции можно подсчитать ячейки, которые содержат только числовые значения в списке аргументов. Зачастую используется в расчетах средних значений, когда использование функции СРЗНАЧ в Excel нецелесообразно.

Следующая формула возвращает количество ячеек в диапазоне A1:E4, которые содержат числа.

image2.png

Как мы видим, в нашем списке аргументов диапазон из пяти значений, но функция вернет три, ведь числовые значения содержатся только в столбцах A, B, C. В столбце D — текстовое выражение, а Е — незаполненная ячейка.

Близкие по применению функции:

=СЧЁТЗ(значение1;[значение2];…) — считает количество непустых значений в перечне аргументов.

=СЧИТАТЬПУСТОТЫ(диапазон) — считает количество пустых значений в указанном диапазоне.

Замечания

Номер_функции :

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

Ошибки

  • Если второй аргумент”, но не предоставлен, возвращается #VALUE! ошибку “#ВЫЧИС!”.

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

Тип диапазона

  • Функция АГРЕГАТ предназначена для столбцов данных или вертикальных диапазонов. Она не предназначена для строк данных или горизонтальных наборов данных. Например, если вы вывели итог горизонтального диапазона, используя вариант 1, например АГРЕГАТ(1, 1, ref1), скрытие столбца не влияет на агрегированное значение суммы. Но скрытие строки в вертикальном диапазоне влияет на агрегат.

Возвращаемое значение

Функция АГРЕГАТ возвращает числовое значение.

Видео работы функции

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

#5. ОКРУГЛ

Синтаксис: =ОКРУГЛ(число; число_разрядов)

число — аргумент.

число_разрядов — до какого разряда округляется число.

Функция ОКРУГЛ применяется для округления действительных чисел до требуемого количества знаков после запятой и возвращает округленное значение согласно математическому правилу округления.

К примеру, для округления числа 2,57525 до 2-х символов после запятой можно ввести формулу =ОКРУГЛ(2,57525;2), которая вернет значение 2,58. Эта функция часто используется при построении балансовых и других видов отчетности.

Применение

  • Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010

Тип функции

  • Функция рабочего листа (WS)

Дополнительные материалы

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

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

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

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

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

summa-v-stroke-sostoyzniya-300x160.png Сумма в строке состояния

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

#10. СЦЕПИТЬ

Синтаксис: =СЦЕПИТЬ(текст1;[текст2];[текст3];…)

текст1 — обязательный аргумент.

Для объединения значений из разных ячеек в одну используется функция СЦЕПИТЬ. Также можно применять аналог — & (амперсанд). Функция часто используется для объединения данных с нескольких столбцов.

Пример

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

image10.png

Хотите получать дайджест статей?

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

Спасибо за подписку!

Последние материалы

img-mascot-619faf0bec82a355501337.jpg

Как появилась идея говорящих драже M&M’s и в чем успех английского капитана Моргана.

img-liski-619f751aeadcc188867888.jpg

Рассказываем, как диверсифицировать риски в бизнесе.

img-scale-619cf92ed4666983355637.png

Почему вам мало платят и не повышают в должности.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...