С помощью Проверки данных [Data Validation] можно организовать ввод данных путем выбора из предлагаемого списка, значения которого зависят от другого списка. Причем, используя функцию СМЕЩ [OFFSET] можно создать вариант, когда добавленные исходные значения будут отображаться в списках для выбора нужных значений. Если значения списка зависят от выбранного значения из другого списка, то можно создать связанный (зависимый) список нужных значений. Это позволит в значительной степени избежать не корректных комбинаций вводимых значений.

Например, в поле Европа происходит выбор одного из двух значений: Западная или Восточная, после этого в поле Страна предлагается список с соответствующими значениями.

Последовательность создания:

• Выделить ячейку F2, где будет выбираться Европа.
  На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника выделить ячейки B2 и C2

• Ячейкам значений стран (данные в столбцах B и C) необходимо присвоить имена – Западная и Восточная, с возможностью автоматического определения диапазона ячеек по мере изменения количества значений в соответствующих столбцах:
  На вкладке Формулы [Formulas] выбрать Диспетчер имен [Name Manager] или нажать клавиши Ctrl+F3.
  Создать имена с использованием функции СМЕЩ:

• Выделить ячейку F3, где будет выбираться Страна.
  На вкладке Данные [Data], в группе Работа с данными [Data Tools], выбрать Проверка данных [Data Validation] и на вкладке Параметры [Option], задать Условие проверки [Validation criteria] – Список [List], в качестве источника ввести формулу:
  =ЕСЛИ($F$2=»Западная»;Западная;Восточная)
  [=IF ($F$2=»Западная»;Западная;Восточная)], где F2 – ячейка, которая содержит значение, выбираемого из первого списка.

При добавлении новых данных, они будут сразу показаны в выпадающем списке:

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

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

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

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

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] и т.д.