Excel. Часть 4. Очистка данных

Часто журналистам приходится работать с данными, представленными в виде, непригодном для анализа. Мы называем их «грязными» данными. В таблицах встречаются опечатки и ошибки, объединенные ячейки, продублированные значения, названия написаны по-разному – где-то с большой, где-то с маленькой буквы, где-то пропущены значения, эти пропуски могут кодироваться по-разному – где-то стоит прочерк, где-то 0 – все это признаки «грязных» данных. В этом выпуске – про функции, с помощью которых можно очистить данные, чтобы подготовить их к анализу.

• Для примера мы будем использовать несколько наборов данных, собранных в этой таблице: от деклараций депутатов Госдумы до реестра камер наблюдения за москвичами.

Объединенные ячейки

В некоторых датасетах объединенные ячейки могут мешать анализу данных: они не позволяют, например, выполнять фильтрацию. В наборе данных с декларациями депутатов Госдумы объединенные ячейки мешают быстро выяснить, кто задекларировал самый высокий годовой доход. Чтобы избавиться от этой проблемы, необходимо выделить столбец с объединенными ячейками, нажать на специальный символ в меню и выбрать опцию «Отменить объединение ячеек».

Фильтрация для очистки данных

Следующая проблема – лишние данные. Например, в наборе данных с годовыми доходами депутатов отображены доходы супруг и супругов, а также строка «ребенок». Чтобы оставить только депутатов и их доходы, отфильтруем данные по значению: уберем галочки с опций «Пустые», «супруг», «супруга», «ребенок». Останется таблица с ФИО депутата и его задекларированным доходом. Подробнее о том, как выполнить фильтрацию, мы рассказывали в первом выпуске курса.

«Найти и заменить»

Еще один признак «грязных» данных – значения из одной категории записаны в разных форматах. Например, в датасете с декларациями у одного депутата доходы записаны с запятой: «988498353,84» (и Google Таблицы воспринимают их как числа), а у другого с точкой: «4870390.49» (таблицы воспринимают их как текст). Такие данные невозможно анализировать автоматически, например, получить правильные результаты после сортировки. Самый легкий способ исправить эту проблему – выделить столбец с доходами, в меню «Изменить» выбрать «Найти и заменить» и заменить точки на запятые.

Преобразование регистра

Иногда нам требуется изменить регистр текста. Например, данные в декларациях депутатов записаны в таком виде: «СКОЧ Андрей Владимирович». Если бы нам понадобилось автоматически сопоставить эти данные с данными за другой год, в котором было бы верное написание, регистр этого текста помешал бы это сделать. В этом случае нам пригодится функция ПРОПНАЧ (PROPER), которая преобразует все первые буквы слов в заглавные. Подробнее о том, как работают функции, мы рассказывали в этом выпуске. Функция ПРОПИСН (UPPER) приведет все первые буквы слов в верхний регистр. Функция СТРОЧН (LOWER) – в нижний регистр.

Убрать переносы строк

Часто в ячейки закрадываются лишние переносы строк. Например, в датасете с декларациями фамилия написана на первой строке ячейки, а имя и отчество на второй. Чтобы избавиться от переносов строк, нужно ввести формулу =ПОДСТАВИТЬ(A2;СИМВОЛ(10);" "), в которой сперва указать адрес ячейки, из которой надо удалить переносы строк, затем символ, который мы хотим заменить (СИМВОЛ(10) – это переносы строк), а затем в кавычках символ, который мы хотим подставить вместо старого. В данном случае – пробел. В нашем случае мы получим запись ФИО депутатов в виде «Скоч Андрей Владимирович» без переносов строк.

Соединение нескольких таблиц с пересекающимися данными

Если бы мы хотели сравнить доходы депутатов за разные годы, нам понадобилось бы объединить два датасета в один. Сделать это можно с помощью функции ВПР(VLOOKUP). В нашем случае формула выглядит так: =ВПР(D7;A$1:B$447;2;0). В скобках на первом месте прописывается ячейка с искомым содержанием (D7 – имя депутата) из первой таблицы, затем диапазон поиска – вторая таблица, номер столбца, значение из которого мы хотим извлечь (2 столбец – с доходами депутата), а в конце 0, если нам требуется поиск точного соответствия. Тогда ячейка с формулой подставит рядом с именем депутата его доход за предшествующий год. После этого можно будет автоматически проводить сравнение данных за несколько лет. Если где-то отобразится ошибка #Н/Д (нет данных), значит искомое значение не было найдено во второй таблице. В данном случае это может означать, что депутат в тот год еще не вступил в должность и не подавал декларацию о доходах.

Соединение данных из разных ячеек в одну

Иногда перед выполнением анализа нам требуется соединить данные из нескольких ячеек в одну. Например, в датасете ФНС о физических лицах, являющихся руководителями нескольких юридических лиц, фамилия, имя и отчество одного человека размещены в разных ячейках, а не в одной. Чтобы исправить это, используем функцию =JOIN(" ";B2:D2). Внутри в кавычках прописываем разделитель, которым будут отделены данные в новой ячейке (в данном случае это пробел) и диапазон строк, которые надо соединить.

Разделение данных из одной ячейки на несколько

В других случаях нам наоборот надо разделить содержимое ячейки на несколько столбцов. Например, чтобы посчитать, на какой улице Москвы «в местах массового скопления людей» установлено больше всего камер наблюдения, из ячейки с адресом нам нужно вычленить только улицу. Сделать это можно с помощью функции =SPLIT(A2;","), которой надо задать ячейку с адресом и разделитель, которым разделены значения в адресе. В данном случае это запятая: «город Москва, Никольская улица, дом 25». В таком случае мы получим отдельные столбцы с городом, улицей и домом.

Удаление лишних пробелов

Часто работать с данными мешают лишние пробелы в ячейке. Например, в датасете с адресами камер наблюдения, в столбце с улицей после разделения ячейки с адресом образовался пробел перед названием улицы. Чтобы убрать из ячейки все пробелы кроме одиночных пробелов между словами, можно воспользоваться функцией СЖПРОБЕЛЫ(TRIM). Например: =TRIM(A2): после названия функции надо указать нужную ячейку.

Удаление дубликатов данных

В некоторых случаях в данных попадаются продублированные значения. Если бы в датасете с улицами с камерами наблюдения нам понадобилось бы оставить только уникальные улицы, необходимо было бы удалить дубли. Для этого надо выделить диапазон данных, выбрать в меню «Данные» опцию «Удалить дубликаты».

Преобразование дат

Чтобы извлечь из строки с датой только год – например, чтобы посчитать, в каком году происходило больше всего закупок какого-либо товара, воспользуйтесь функцией ГОД(YEAR). Чтобы извлечь только месяц – МЕСЯЦ (MONTH), день – ДЕНЬ(DAY).

Извлечение части содержимого ячейки

Часто для очистки данных требуется выделить несколько символов из ячейки в отдельную. В наборе данных по военным расходам разных стран, годы записаны в таком виде: «2015 [YR2015»]. Чтобы преобразовать это в запись вида «2015», можно воспользоваться функцией ЛЕВСИМВ(LEFT), которая возвращает несколько первых символов строки. В нашем случае: =ЛЕВСИМВ(C1;4). Сперва мы указываем адрес ячейки, а затем количество символов, которые хотим оставить. В итоге в ячейке останется только «2015». Чтобы извлечь символы с правой стороны, подойдет функция ПРАВСИМВ(RIGHT).

Перевод данных с другого языка

Журналисты часто работают с данными на разных языках. Чтобы перевести текст в таблице с одного языка на другой, нужно прописать в ячейке функцию GOOGLETRANSLATE. После нее указывается ячейка, которую надо перевести, язык оригинала и язык перевода. Например: =GOOGLETRANSLATE(A2;"en";"ru"), чтобы перевести текст из ячейки A2 с английского на русский. После этого важно проверить переведенные данные на предмет ошибок: перевод может быть дословным.

Транспонирование таблицы

Иногда данные представлены в виде, неудобном для анализа: например, заголовки данных расположены в первом столбце, а не в первой строке таблицы. Если вы хотите поменять местами строки и столбцы в таблице, выделите пустую ячейку под старой таблицей, выберите функцию ТРАНСП(TRANSPOSE) и укажите ей диапазон старой таблицы.