Быстрые переходы
Слышали о Power Query в Excel и думали, что это инструмент только для продвинутых пользователей? Пора развеять этот миф: Power Query — простой и мощный помощник, который значительно облегчит вашу работу с данными.
Это руководство для тех, кто только начинает знакомство с Microsoft Excel и Power Query. Не нужно знать язык формул Power Query M — я покажу базовые команды, чтобы вы сразу же смогли автоматизировать обработку данных.
1 Разделяем ячейки по разделителю: как отделить данные в столбцах
В Excel есть несколько способов разбить содержимое ячейки на несколько столбцов — например, «Текст по столбцам» или Flash Fill. Но самый удобный и универсальный способ — использовать Power Query.
Допустим, вы нашли таблицу на Википедии, которую хотите обработать через Power Query. Сначала нажмите на вкладке «Главная» кнопку «Получить данные», выберите «Из других источников» > «Из Интернета».
Как быстро импортировать таблицы из Интернета в Excel 365
Не тратьте время на ручное копирование и забудьте про ошибки.
В окне «Из Интернета» вставьте URL и нажмите «ОК». Затем выберите нужную таблицу в левой части Навигатора и нажмите «Преобразовать данные».
В редакторе Power Query вы увидите, что столбец «Career Span» содержит два значения — год начала и год окончания карьеры. Их нужно разделить на отдельные столбцы.
Нажмите правой кнопкой на заголовок столбца, наведите курсор на «Разделить столбец» и выберите «По разделителю».
Разделитель — это символ или пробел, который разделяет элементы внутри ячейки.
Power Query обычно сам определяет дефис как разделитель и предлагает его использовать, но при необходимости можно выбрать любой другой из списка.
В нашем примере в каждой ячейке только один разделитель, поэтому просто нажмите «ОК» и посмотрите, как данные автоматически разбились на два столбца.
Чтобы переименовать столбец, достаточно дважды кликнуть по заголовку и ввести новое имя.
Обратите внимание, что в двух новых столбцах выравнивание значений разное: «First played» — числа, «Last played» — сочетание текста и чисел. Значит, Excel определил разные типы данных.
Как правильно выбрать числовой формат в Excel и избежать ошибок
Установите подходящий формат для каждого столбца с числами.
Исправить это просто: нажмите на значок «ABC» в заголовке столбца «Last played» и поменяйте формат на «Целое число».
Теперь оба столбца правильно распознались как числовые.
Однако там, где раньше был текст, теперь отображаются ошибки — не переживайте, я расскажу, как с ними справиться!
2 Как заменить ошибки для корректных вычислений
Power Query отлично работает с ошибками в данных.
В нашем случае в столбце «Last played» появились ошибки из-за текста «Present» — он не подходит для числового формата.
Если закрыть редактор сейчас, эти ячейки станут пустыми, что может вызвать сбои при фильтрации или расчетах. Лучше заменить ошибки на осмысленное значение.
Для этого щелкните правой кнопкой по заголовку столбца и выберите «Заменить ошибки».
Пункт «Удалить ошибки» удалит всю строку, а не только ошибочные ячейки, так что используйте его осторожно.
В открывшемся окне введите значение, которым хотите заменить ошибки. Поскольку ошибка возникла там, где было «Present», лучше поставить текущий год.
Нажмите «ОК» — все ошибки заменятся на указанный год.
Теперь создадим новый столбец, который посчитает общее количество лет карьеры каждого игрока.
На вкладке «Добавить столбец» выберите «Пользовательский столбец», назовите его «Годы игры» и введите простую формулу вычитания.
После подтверждения новый столбец появится справа без единой ошибки.
Чтобы вернуть результат в таблицу Excel, нажмите «Закрыть и загрузить» на вкладке «Главная» редактора.
3 Как преобразовать данные, чтобы сделать таблицу удобной для анализа
Когда я работаю с новыми наборами данных, я всегда стараюсь привести их к формату «запись — поле»: каждая запись — отдельный объект (страна, игрок, компания), а поля — информация о нем.
Вот пример таблицы, где для каждой страны указаны континент, население и валюта.
Такой формат упрощает фильтрацию, расчёты и визуализацию.
А вот таблица, где в заголовках столбцов — годы, а каждая строка — компания.
В таком виде трудно отфильтровать данные по году или быстро сравнить прибыль. Чтобы исправить это, таблицу нужно «распрямить» (unpivot) — превратить каждый год в отдельную запись, то есть объединить финансовые показатели в одно поле.
Выделите любую ячейку и на вкладке «Данные» выберите «Из таблицы/диапазона».
Если диапазон не оформлен как таблица, Excel предложит это исправить — для работы Power Query это обязательно.
В редакторе выделите заголовки столбцов с годами, например с 2020 по 2024, удерживая Shift.
На вкладке «Преобразовать» откройте меню «Снять сводку» и выберите «Только для выделенных столбцов».
Теперь таблица стала длинной: каждая строка — запись с компанией, годом и прибылью.
Переименуйте заголовки и выставьте правильный формат чисел по столбцам.
Нажмите «Закрыть и загрузить» — и в Excel появится новая таблица, удобная для анализа.
Теперь можно легко фильтровать данные по году, компании и быстро сортировать показатели.
Как эффективно сортировать и фильтровать данные в Excel
Используйте встроенные инструменты для удобного управления таблицами.
Если в исходной таблице появятся данные за новый год, просто нажмите «Обновить» на вкладке «Запрос» — Power Query автоматически добавит их в уже «распрямлённую» таблицу.
4 Заполняем пустые ячейки значениями из соседних строк
Пустые ячейки создают проблемы при фильтрации и формулах с ссылками, поэтому лучше их заполнить.
В моём примере только первый игрок каждой команды имеет номер команды, остальные ячейки пусты, из-за чего неудобно фильтровать по команде.
Идеально, когда номер команды повторяется для всех игроков. Вводить вручную долго, но Power Query справится с этим за секунды.
Выделите любую ячейку данных и выберите «Из таблицы/диапазона» на вкладке «Данные».
В редакторе пустые ячейки отображаются как «null». Чтобы заполнить их, кликните на заголовок столбца, перейдите на вкладку «Преобразовать» и нажмите «Заполнить».
Выберите направление заполнения — вниз или вверх. В нашем случае — «Вниз», чтобы пустые ячейки заполнились значением сверху.
Microsoft Office 2024
Получите постоянный доступ к Word, Excel, PowerPoint и OneNote с пакетом Office Home 2024. Это не подписка Microsoft 365 — здесь меньше приложений и обновлений, зато одна покупка и вы сразу начинаете работать.
Теперь у каждого игрока есть номер команды, и, нажав «Закрыть и загрузить», мы отправим обновлённые данные на новый лист в книге Excel.
Этот простой приём позволит быстро сортировать и фильтровать игроков по командам без риска потерять соответствие данных.
Если у вас несколько таблиц на разных листах с одинаковыми заголовками, Power Query позволит легко объединить их в одну таблицу. Достаточно создать связи между таблицами и объединить запросы в редакторе — это просто и очень удобно.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете найти наши материалы в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru