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

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

Синтаксис функции:

АГРЕГАТ(Номер_функции;Параметры;Массив;[k])
[AGGREGATE(function_num;options;array;[k])]

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

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

Однако, представим такую ситуацию, что сперва у нас данные нескольких месяцев, а затем данные постоянно добавляются в конец таблицы и, совсем не хотелось бы всякий раз заниматься изменением данным. В ситуации, когда построение диаграммы происходит по данным всей таблицы, которые затем могут изменяться – добавляться или удаляться строки/столбцы таблицы, можно построить диаграмму, которая будет сама определять размер диапазона (таблицы).

Эту ситуацию можно легко решить без каких-либо формул. Необходимо исходный диапазон преобразовать в Таблицу:

1. Выделить любую ячейку таблицы данных.
2. На вкладке Главная [Home], в группе Стили [Styles] воспользоваться командой Форматировать как таблицу [Format As Tables].
3. Указать расположение данных таблицы. ОК.

Построение диаграммы произвести стандартным способом: выделить ячейку таблицы и на вкладке Вставка [Insert] в группе Диаграммы [Charts] выбрать нужный тип диаграммы. Например, гистограмма:

По мере изменения диапазона таблицы, данные на диаграмме автоматически будут изменяться: