Всем привет! Ленивый аналитик на связи, а это значит, что мы продолжаем облегчать себе рабоче-эксельную рутину.
Лирическое отступление
Когда я говорю "облегчить Excel-ную рутину", чаще всего имею в виду Power Query. Я уже практически амбассадор этой надстройки, но тут я задумалась - а так ли нужен Power Query всем, кто работает в Excel? Ведь на изучение мы тоже тратим время...
Моими выводами о том, кому действительно нужен Power Query, поделались в телеграмм-канале "Ленивый аналитик".
>>> 3 признака, что тебе пора в Power Query <<<
Если вы нашли себя среди тех, кому "надо" - то начнем.
Одной из основных функций Power Query является очистка данных. И самой, пожалуй, базовой задачей - удаление дубликатов.
Рассмотрим несколько примеров - от самого простого к более "замудренным".
Удаляем дубликаты по одному столбцу
Например, у нас есть столбец с данными по ID клиента.
Необходимо оставить только уникальные значения.
Эту задачу, кстати, мы вполне можем решить без Power Query: выделить столбец, перейти на вкладку Данные - блок Работа с данными - Удалить дубликаты.
Но - это одноразовая история. Если дубликаты снова появятся - нужно это отследить и снова удалить. К тому же, в данном (весьма примитивном - для наглядности) примере это окей, но если ID будут содержать пробелы или буквы... где перепутаны латиница и кириллица - будет "весело"нет
Поэтому сразу воспользуемся Power Query
Загрузим данные в Power Query. Для этого щелкнем в любой ячейке столбца и перейдем на вкладку Данные - блок Получить и преобразовать данные - Из таблицы/диапазона.
Для загрузки автоматически будет создана "умная" таблица:
В этом окне нужно поставить "галочку" Таблица с заголовками, если они есть. Если заголовка нет - галочку убираем! Иначе первая ячейка не попадет в диапазон данных и станет заголовком.
Нажимаем Ок и попадаем в окно Power Query.
Теперь правой кнопкой мыши щелкаем на заголовке столбца и выбираем Удалить дубликаты.
Готово.
Осталось загрузить этот список уникальных значений на лист excel (если нужно). Для этого на вкладке Главная выбираем Закрыть и Загрузить - Загрузить в...
И выбираем, например, Таблица и на Новый лист
Теперь список уникальных значений без дубликатов можно обновлять, щелкнув на нем правой кнопкой мыши и нажав Обновить.
Где же здесь автоматизация, спросите вы, если все равно список нужно обновлять?
Этот список - это запрос (считай - программа с записанными "навсегда" шагами), который может обновляться по команде "Обновить все" на вкладке Данные, вместе со всеми остальными запросами. А также можно настроить обновление при открытии файла.
В любом случае, этот способ быстрее и эффективнее, чем "ручное" удаление дубликатов.
Удаляем дубликаты по нескольким столбцам (простой случай)
Усложним задачу: к ID клиента добавился столбец Товар (каждый клиент в разное время приобретал разные товары).
Необходимо: составить список уникальных пар значений ID клиента - Товар (нужно понять, какие товары покупал каждый клиент). Для этого нужно удалить дубликаты по двум столбцам.
Дубликаты для наглядности подкрашены разными цветами.
Сначала удалим дубликаты привычным способом в Excel.
Выделим оба столбца, перейдем на вкладку Данные - Удалить дубликаты.
Получилось, дубликаты удалены и список содержит только уникальные пары значений.
Сделаем то же самое в Power Query.
Загрузим таблицу в Power Query (как в предыдущем примере). Далее в выпадающем меню в верхнем левом углу таблицы выберем Удалить дубликаты.
Готово - мы получили список уникальных пар значений.
В этом примере наша "выгода" также в том, что эту манипуляцию мы проделываем один раз - а далее при обновлении результата запроса дубликаты будут удаляться автоматически.
Удаляем дубликаты по нескольким столбцам (сложный случай)
(спойлер: вот где будет понятна вся мощь Power Query по сравнению с Excel)
Пример похож на предыдущий, но в нем (как и реальной работе) есть нюансы...
Необходимо так же, как и в предыдущем примере: составить список уникальных пар значений ID клиента - Товар (нужно понять, какие товары покупал каждый клиент). Для этого нужно удалить дубликаты по двум столбцам.
Дубликаты так же подкрашены разными цветами.
Но - если мы будем удалять дубликаты стандартным способом в Excel (выделим оба столбца, вкладка Данные - Удалить дубликаты) - то увидим, что не все дубликаты были удалены.
Excel по умолчанию удалил только явные дубликаты - те пары значений, где оба значения совпадают полностью: отсутствие лишних пробелов, сами буквы.
Дубликат для пары 111-Мёд не был удален, потому что во втором случае слово "Мед" написано через е, а не ё (вполне реальный вариант в русском языке). А для пары 222-Масло - не из-за маленькой буквы, как могло показаться, а из-за невидимого пробела в конца строки.
Чтобы пофиксить это в Excel, нам пришлось бы:
- создать вспомогательный столбец Товар1, в котором бы удалялись лишние пробелы функций СЖПРОБЕЛЫ
- сделать замену всех букв Ё на Е, чтобы привести все слова к единому виду
- скопировать столбец Товар1 и вставить его значениями. Это нужно, потому что далее нужно...
- удалить исходный столбец Товар (если не скопировать значениями Товар1, то после удаления столбца Товар в Товар1 будет ошибка)
- теперь уже можно выделить оба столбца и удалить дубликаты через Данные - Удалить дубликаты.
И все это нужно проделать каждый раз, когда обновились данные!
И ведь этих шагов может быть намного больше, в зависимости от ваших данных.
Воспользуемся Power Query для удаления дубликатов
Загрузим исходную таблицу в Power Query.
Теперь проделаем почти все то же самое, что сделали бы для Excel (да-да 😆 чувствую, сейчас вы начинаете меня ненавидеть и недоумевать, для чего это все? но не останавливаемся!)
Поработаем со столбцом Товары:
- Приведем все значения к нижнему (или верхнему, разницы нет) регистру. Для этого выделим столбец, вкладка Преобразование - столбец "Текст" - Формат - нижний регистр.
(это нужно сделать, т.к. Power Query, в отличие от Excel, различает регистр букв)
- Удалим лишние пробелы. Не снимая выделения со столбца Товар, так же вкладка Преобразование - столбец "Текст" - Формат - Усечь.
Эта функция удаляет лишние пробелы в начале и конце строки.
- Осталось заменить все буквы ё на е.
Выделим столбец Товар - правой кнопкой мыши на заголовке - Замена значений.
И укажем, что заменяем ё на е
Теперь осталось просто удалить дубликаты через выпадающее меню в левом верхнем углу таблицы
Готово. Загружаем результат на лист через вкладку Главная - Закрыть и Загрузить - Закрыть и загрузить в... - указать куда.
Почему же этот способ удаления дубликатов по нескольким столбцам лучше, чем в Excel? Ведь мы проделали такое же количество шагов.
Те, кто знаком с Power Query, уже догадались - это все нужно проделать 1 раз!
В отличие от Excel, где к этой операции нужно постоянно возвращаться...
Надеюсь, эти приемы для удаления дубликатов и создания уникальных списов сэкономят вам часы работы (читай - жизни)!
А еще больше полезного, насущного и жизненного в телеграмм-канале Ленивый аналитик.
Присоединяйтесь 💚