Чистка данных в Excel: пробелы, регистр, замена символов

В этом модуле вы узнаете, как удалить задублированные или другие лишние пробелы, заменять символы по шаблону и привести в порядок регистр.

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

Открыть учебную Google-таблицу.

С помощью Excel можно сделать почти все — дашборды, аналитику, регрессии, SQL-запросы и даже нейросети. И его достаточно, чтобы построить систему аналитики, для которой обычно требуется несколько инструментов, Python, какие-то сложные BI-приложения, база данных и т.п. Ведь гораздо проще выгрузить данные в Excel, сделать сводную таблицу, почистить их и найти ответы на все свои вопросы. 

Однако в Excel очень сложно строить глобальную аналитику — мешают ограничения по объемам данных (свежие версии вмещают чуть больше миллиона строк, Google Sheets все сложнее, но ограничений не меньше). 

В Excel, как и в языках программирования, есть базовые арифметические операции: сложение, умножение, деление, вычитание, округление, типы данных, операции, которые мы можем над ними производить. Есть формулы, фильтры, сортировка.

Чистка данных

Когда мы говорим о чистке данных, то чаще всего имеем в виду работу со строками. Например, у нас есть текст: город, название компании, артикул товара. Если текст вводят люди, появляются проблемы: например, в одной колонке может появиться несколько вариантов написания одного и того же города — Санкт-Петербург, Санкт Петербург. Для компьютера это два разных значения, а значит, когда вы начнете агрегировать данные, для этого города получится два фильтра. Чтобы этого не случилось, необходимо предварительно почистить данные.

Этап 1. Сжатие пробелов

Первым делом уберем лишние пробелы. Делать это будем автоматически — вы ведь не станете просматривать каждую ячейку в файле на 10 тысяч строк и удалять их вручную? В Excel есть функция «сжатие пробелов» по-английски — TRIM. Она убирает повторяющиеся проблемы, а также пробелы перед началом данных и после данных. Эту функцию можно запускать сразу, как только вы получаете данные — чтобы в вашей таблице точно не осталось лишних пробелов.

Если вы не знаете, как работает та или иная функция, посмотрите справку — обычно там достаточно подробно описываются все случаи и примеры применения. 

Однако в слове «Санкт Петербург» пробел не сожмется — ведь функция сжимает только повторные проблемы и пробелы в начале и в конце данных. 

Этап 2. Работа с верхним и нижним регистром

У нас в массиве данных есть Москва — и встречаются ячейки, в которых название города написано со строчной буквы. Для анализа данных это не помеха, а вот если вы хотите сделать таблицу красивой и опрятной (например, использовать ее в отчетах и т.п.), это придется исправить.

Для этого в Excel и Google Spreadsheets есть три функции 

• LOWER (СТРОЧН) — сделать все буквы строчными;
• UPPER (ПРОПИСН) — сделать все буквы прописными; 
• PROPER (ПРОПНАЧ) — сделать первые буквы прописными.

Последовательно применим их ко всем ячейкам — так таблица станет опрятной

На скриншотах — примеры использования функций.

Этап 3. Замена символов

Замену символов удобно использовать, когда, например, есть какая-то типичная опечатка, лишние пробелы (как в слове «Санкт Петербург»), или человек почему-то поставил везде точку с запятой вместо запятой. 

Конечно, можно сделать это с помощью функции «Найти и заменить». Но это не поможет, если вам надо поменять точку с запятой на запятую в одном столбике. Для этого можно использовать функцию SUBSTITE (ПОДСТАВИТЬ). Она принимает три аргумента:

1. Строку, в которой надо произвести замену (например, «Санкт Петербург»). 
2. Фрагмент текста, который вы хотите заменить. 
3. Текст, на который вы замените тот кусочек, который указали во втором аргументе.