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

Функция АГРЕГАТ [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] выбрать нужный тип диаграммы. Например, гистограмма:

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

Наличие лишних пробелов затрудняют вычисления и сравнение данных. Порой необходимо удалить все пробелы вообще, а где-то привести текст к правильному написанию. Здесь возможны варианты в зависимости от того меняются исходные данные или это разовая “чистка” значений.

1-я ситуация: Разово необходимо удалить все пробелы. 

Это самая простая ситуация, действуем следующим образом:

• Выделяем диапазон с данными.
•  Нажимаем клавиши CTRL+H (или на вкладке Главная [Home] в группе Редактирование [Editing] в списке кнопки Найти и выделить [Find & Select] выбрать Заменить [Replace])
• в поле Найти [Find what] ввести с клавиатуры пробел, поле Заменить на [Replace with] оставить пустым. Нажать кнопку Заменить все [Replace All].
  

Результат:

2-й ситуация: Постоянно удалять все пробелы.

Например, данные импортируются из другого источника и в дальнейшем постоянно обновляются, но при этом содержать не нужные пробелы. Чтобы каждый раз вручную не проделывать удаление способом описанным выше (1-я ситуация), следует воспользоваться текстовой функцией ПОДСТАВИТЬ [SUBSTITUTE].

Синтаксис функции: ПОДСТАВИТЬ(Текст;Стар_текст;Нов_текст;[Номер_вхождения]).

С помощью этой функции в тексте пробел ” “ меняем на пустоту “”.  Можно удалять не только пробел, но и любой другой непечатаемый символ. Алгоритм действий описан в статье.

3-я ситуация: Удалить лишние пробелы, т.е. сделать написание правильным.

Часто бывает, что выгружаемые данные содержат много лишних пробелов. Однако нужно удалить не все, а только лишние. Т.е. сделать написание текста правильным – удалить пробелы в начале и в конце и сделать по одному пробелу между словами (символами). Этой цели можно достичь с использованием текстовой функции СЖПРОБЕЛЫ [TRIM]. Функция очень проста в использовании, т.к. имеет всего один аргумент СЖПРОБЕЛЫ(Текст). Результат:

 Понятно, что вряд ли кто будет с помощью формул (2-я и 3-я ситуации) создавать отдельные столбцы с данными. Поэтому целесообразно данные функции использовать внутри других функций. Например, искомое значение в функции ВПР [VLOOKUP], ПОИСКПОЗ [MATCH], в сравнении в функции ЕСЛИ [IF], в критериях функций СУММЕСЛИМН [SUMIFS], СЧЁТЕСЛИМН [COUNTIFS], СРЗНАЧЕСЛИМН [AVERAGEIFS] и т.д.