Я всегда использовал Excel для быстрых вычислений и создания простых таблиц. Но, помимо распространённых формул и базовых методов обработки данных, я никогда не чувствовал необходимости изучать дополнительные функции Excel, пока мои проекты не стали более сложными.
Быстрые ссылки
Проблема, которая наконец заставила меня обратить внимание
Из-за ряда рыночных факторов и импортных пошлин покупка компьютерных комплектующих в моём регионе часто обходится дороже, чем в США. Мне хотелось узнать, насколько дороже я плачу за те же комплектующие и стоит ли заказывать напрямую на Amazon или Newegg, а не у местных розничных продавцов. Поэтому я собрал данные о ценах за несколько месяцев на основные компьютерные комплектующие (процессоры, видеокарты и оперативную память), которые обычно импортируют местные магазины. Простой проект по отслеживанию, правда? Неправильно.
У меня быстро образовался полный бардак в данных. Каждый ритейлер экспортировал информацию, используя разные правила форматирования, что делало объединение файлов практически невозможным. Amazon использовал формат даты ММ/ДД/ГГГГ, Newegg — ГГГГММДД, а Shopee (мой местный магазин) — ДД-ММ-ГГГГ.
Несоответствия на этом не закончились. Названия столбцов сильно различались. Newegg обозначил цены как «retail_price», Amazon — «unit_price_usd», а Shopee — «price_php». Форматирование цен также было проблематичным: в некоторых файлах отображалось «₱18,600» с символами валют, в то время как в других отображались обычные числа, например, «320». Даже названия брендов были непоследовательными: в разных файлах один и тот же производитель обозначался как «gigabyte», «GIGABYTE INC.» или «Gigabyte Tech».
Ручная очистка и объединение этих данных уже заняли у меня несколько часов. Пришлось копировать и вставлять данные между файлами, искать и заменять несоответствующие значения и удалять пустые строки одну за другой. Конвертация PHP в доллары США для сравнения цен означала необходимость постоянно смотреть на другой экран, чтобы узнать обменные курсы. В целом, работа была утомительной, подверженной ошибкам, и я почти сдался.
Вот тогда я наконец задумался об использовании одной из функций, о которой так много говорят энтузиасты Excel, — Power Query. Множество других мощных функций, предлагаемых ExcelНо я слышал, что Power Query — идеальный инструмент для решения моей конкретной задачи. Поэтому, посмотрев несколько обучающих роликов на YouTube, я сразу понял, сколько времени смогу сэкономить, начав использовать Power Query Editor для очистки всех беспорядочных данных, собранных мной в интернете. С Power Query я теперь могу легко импортировать данные из различных источников, преобразовывать их в стандартизированный формат и эффективно анализировать, экономя драгоценное время и силы на моих проектах по анализу цен на компьютерные компоненты.
Как использовать Power Query для очистки неструктурированных данных?
Через некоторое время я остановился на простом пошаговом процессе в редакторе Power Query. Вот как я навёл порядок в своих запутанных экспортированных CSV-файлах и превратил их в целостную, хорошо структурированную электронную таблицу.
Сначала я импортировал данные в редактор Power Query, открыв пустую книгу и нажав Цены В ленте выберите Из текста / CSV.Затем я выбрал свой CSV-файл и нажал Преобразовать данные Чтобы открыть его, используйте редактор Power Query.
Я начал с исправления столбца даты. Поскольку я собирал данные из двух источников с разницей во времени в 12 часов, мне нужно было унифицировать даты. Это оказалось довольно просто. Я определил столбец Время, щелкните правой кнопкой мыши, чтобы открыть контекстное меню, и выберите Изменить тип > Использование локалиВ выпадающем меню я устанавливаю тип Время и определил Английский (США) Для обеспечения единообразного форматирования Power Query автоматически распознает различные форматы, такие как ММ/ДД/ГГГГ, ГГГГ/ММ/ДД, и переменные, использующие символы, такие как ДД-ММ-ГГ, а затем объединяет их все в единый формат даты.
Теперь, когда я исправил формат даты, мне оставалось только очистить столбец. هناك Различные способы очистки таблицы ExcelНо поскольку все ошибки были вызваны плохими записями, сгенерированными моим парсером, я просто решил использовать фильтр. Удалить ошибки Чтобы удалить эти записи. На этом этапе были удалены нулевые значения и все оставшиеся проблемные данные, которые не были записаны должным образом, что позволило мне получить четкие и согласованные даты во всех моих файлах.
Затем я решил проблему перегруженности названия бренда, придав ей функциональную направленность. Заменить значенияКак и прежде, я выбрал целевой столбец, затем щелкнул правой кнопкой мыши, чтобы открыть контекстное меню, и выбрал Заменить значенияВо всплывающем окне введите в поле несоответствующее значение. Значение для поиска и мое стандартное значение в поле Заменить полем.
Я проделал это ещё два раза и наконец преобразовал все записи «gigabyte» и «GIGABTYE Inc.» в единое «GIGABYTE» во всех моих файлах. Я проделал то же самое с AMD, и теперь во всей колонке «Бренд» для графических процессоров используются стандартные названия брендов.
Power Query: как он сэкономил мне часы работы
Одна из причин, по которой я избегал Power Query, заключалась в том, что я думал, что это будет ещё одна сложная функция, изучение которой займёт много времени. Но всё оказалось гораздо проще, чем я ожидал. Вместо того, чтобы бесконечно выполнять команды поиска и замены, я могу использовать Power Query для быстрой и автоматической очистки данных из моих инструментов сбора данных.
Больше всего в Power Query меня удивило то, что каждая выполненная мной команда записывалась и могла повторяться снова и снова. По сути, это автоматический скрипт очистки, который может превратить захламлённые CSV-файлы в чистые, структурированные электронные таблицы — идеально, если вы работаете над… Создавайте пользовательские наборы данных с помощью веб-скрапинга, поскольку эти инструменты часто выдают некорректные данные.
Для тех, кто сталкивается с повторяющейся очисткой данных, несогласованными форматами или несколькими источниками данных, Power Query превращает эти хлопоты в простой автоматизированный процесс. Вместо того, чтобы тратить часы каждую неделю на ручное исправление, вы можете просто нажать кнопку «Обновить» и начать анализ. Это функция Excel, которую я бы хотел использовать давным-давно. Как только вы ощутите мощь автоматизированного, повторяемого скрипта очистки, пути назад уже не будет. Power Query — мощный инструмент для экономии времени и усилий при обработке данных, предлагающий передовые решения для эффективной очистки и преобразования данных.
