Учебное пособие по Excel — очистка данных с помощью функций TRIM, PROPER и Text to Columns

В этом руководстве по Excel вы узнаете, как очищать данные с помощью функций СЖИЖ, ПРОПНАЧ и Текст по столбцам (и других).

Автор: http://breakingintowallstreet.com/ «Обучение финансовому моделированию и карьерные ресурсы для начинающих инвестиционных банкиров»
Зачем нужно «очищать» данные? Часто, вставляя данные с веб-сайтов, PDF-файлов или других источников, вы получаете множество некрасивого форматирования и других проблем, таких как лишние пробелы, непечатаемые символы и т. д.

Кроме того, данные могут быть сгруппированы, хотя их лучше разделить (как в случае с адресными данными).

Это происходит постоянно на работе, и очистка данных упрощает вашу жизнь и в сто раз упрощает обработку и анализ данных.

МОЖНО было бы исправить это вручную, но после этого вам, возможно, захочется спрыгнуть с крыши высотного здания.

Вместо этого мы будем использовать следующие функции для автоматизации процесса:

Формулы для работы с текстом (для всех версий для ПК и Mac):

=TRIM Удаляет лишние пробелы
=PROPER Делает первую букву каждого слова заглавной
=CLEAN Удаляет все непечатаемые символы из текста

=UPPER Делает все буквы во всех словах заглавными
=LOWER Делает все буквы во всех словах строчными

Alt + A + E / Alt + D + E Текст в столбцы
Ctrl + C Копировать (CMD + C на Mac)

Alt + E + S + F Вставить формулы (Ctrl + CMD + V, CMD + F на Mac)
Alt + E + S + V Вставить значения (Ctrl + CMD + V, CMD + V на Mac)

Alt + O + C + A Автоподбор ширины столбца
Alt + H + C + A Центрировать текст

Как очистить эти данные за 5 шагов:

1. Сначала удалите все лишние Пробелы и каждое слово с заглавной буквы, используя TRIM и PROPER. Можно также добавить CLEAN для пущего эффекта.

2. Затем разделите всё на отдельные столбцы с помощью функции «Текст в столбец».

Возможно, придётся применить это несколько раз, если разные типы данных разделяются разными символами (запятыми или пробелами).

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

Также может потребоваться применить дополнительные TRIM.

Требуется быть очень осторожным при копировании и вставке данных в качестве значений — это необходимо сделать, чтобы избежать ошибок!

4. Добавьте заголовки столбцов вверху, основываясь на копировании и вставке исходного заголовка.

5. Удалите лишние строки/столбцы и сдвиньте всё вверх или вниз.

Что дальше?

Примените это к реальным данным, с которыми вы работаете... всё зависит от конкретных проблем с данными, но TRIM, PROPER и Text to Columns никогда не ошибётесь!

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