• удалять дубликаты;
• извлекать первые/последние символы из текстовых строк или же значения до и после определенных разделителей;
• добавлять префиксы и суффиксы к текстовым значениям;
• объединять две таблицы по тому или иному параметру (по аналогии с ВПР / VLOOKUP) — об этом уже через пару строк;
• объединять несколько таблиц в одну (вертикально, то есть таблицы с одинаковой структурой, а строки собираются друг под другом);
• и многое другое.
Файл с примером: Объединение запросов.xlsx
Power Query позволяет объединять запросы, то есть соединять таблицы, связывать их по тому или иному столбцу: делать то, что делают функции и формулы рабочего листа Excel (как ВПР / VLOOKUP и другие), но делать это быстрее и эффективнее.
Сначала нужно импортировать те данные, которые мы будем объединять (в нашем примере — прайс-лист и форму заказа).
Достаточно выделить таблицу и выбрать команду «Из таблицы/диапазона» на вкладке «Данные» (Data — From Table / Range).
После этого откроется редактор Power Query, где появится соответствующий запрос.
После этого необходимо создать запрос к другой таблице для объединения. Если вторая таблица находится в другой книге Excel, можно импортировать ее с помощью команды на ленте редактора Power Query.
Если таблица в той же книге, можно закрыть редактор Power Query и снова воспользоваться командой «Из таблицы/диапазона» на вкладке «Данные».
Когда вы создали запросы ко всем нужным таблицам, выбирайте команду «Объединить» — «Объединить запросы» на вкладке «Главная» в окне Power Query.
В появившемся диалоговом окне будут предпросмотр первого запроса (из которого вы вызвали команду «Объединить») и возможность выбора второй таблицы.
Тип соединения для того, чтобы подтянуть в первую таблицу данные из второй, — «Внешнее соединение слева» (Left Outer Join), это аналог функции ВПР / VLOOKUP. После выбора второй таблицы в списке появится предпросмотр с ее столбцами. Щелкните на те столбцы, по которым будут объединяться запросы.
После нажатия ОК в первом запросе появится новый столбец с данными из второй таблицы (в данном случае — прайса).
Выберите те столбцы из таблицы «Прайс», которые нужно добавить к первой (форме заказа).
После нажатия ОК появятся данные из прайса. Если на предыдущем этапе вы оставили флажок «Использовать исходное имя столбца как префикс» (Use original column name as prefix), то в названиях добавленных столбцов будет имя таблицы вместе с именем столбца, то есть «Прайс. Цена», а не просто «Цена».
Теперь можно загрузить эти данные в Excel — как таблицу или сразу как сводную таблицу для дальнейшего анализа. Для этого нажмите на «Закрыть и загрузить» (Close & Load). Если вам нужна таблица, то выберите далее пункт «Закрыть и загрузить».
Для создания сводной на основе объединенных данных выберите «Закрыть и загрузить в…» (Close & Load to…) и далее в диалоговом окне — «Отчет сводной таблицы».
Файл с примером: Нечеткий поиск.xlsx
Особым преимуществом объединения таблиц в Power Query является опция поиска нечетких соответствий, которая появилась в 2020 году и будет доступна в последней версии Excel и у подписчиков Microsoft 365, получающих обновления.
Нечеткий поиск — поиск похожих строк, а не только полностью совпадающих. Например, строк, в которых слова переставлены или есть ошибки/опечатки/сокращения. С помощью формул такой поиск реализовать практически невозможно.
Чтобы использовать нечеткий поиск, включите опцию «Использовать нечеткие соответствия при слиянии» (Use fuzzy matching to perform the merge) при объединении запросов.
В параметрах нечеткого соответствия можно установить коэффициент подобия (Similarity Threshold; насколько похожими должны быть текстовые значения, где 1 = точное совпадение), включить или отключить учет регистра при поиске. Если в ваших данных есть перестановки слов (Фамилия Имя Отчество и Имя Фамилия Отчество, например), убедитесь, что включена опция «Сопоставление путем объединения текстовых фрагментов» (Match by combining text parts).
Загрузка данных в Excel
Чтобы загрузить данные на лист Excel после всех преобразований, нажмите «Закрыть и загрузить» (Close & Load) — «Закрыть и загрузить в…» (Close & Load To…).
Откроется окно импорта данных, в котором можно выбрать, в каком виде представить данные: в виде таблицы, сводной, сводной таблицы с диаграммой или только в виде подключения (в этом случае данные пока не будут отображаться на листе Excel, но их можно использовать позднее).
Как и в случае со стандартными сводными, можно выбрать расположение — новый лист или имеющийся.
Если выбрать «Добавить эти данные в модель данных» — они попадут в Power Pivot.
При нажатии «Закрыть и загрузить» (Close & Load) окно импорта не открывается, а данные сразу загружаются в таблицу на новом листе.
В контекстном меню (по правой кнопке мыши) у таблиц, загруженных из внешних источников, будет опция «Обновить» (Refresh): при нажатии будет обновляться связь с источником и будут выполняться все шаги по преобразованию данных, записанные в Power Query (если они были применены). Аналогично с обновлением сводных, созданных на основе внешних источников.
Power Pivot
Power Pivot — система управления базами данных, встроенная в Excel, или, другими словами, очень мощные сводные таблицы (если посмотреть на название этой надстройки).
Надстройка позволяет устанавливать связи между разными источниками данных (загруженными с помощью самой Power Pivot или Power Query), в том числе намного превышающими по объему миллион строк (максимальный в рабочих листах Excel) для последующего анализа в виде сводной. С Power Pivot можно сделать то, что нельзя в самом Excel, — обрабатывать десятки миллионов строк из нескольких источников, которые будут связаны между собой без функций рабочего листа (как ВПР / VLOOKUP или ПРОСМОТРX / XLOOKUP).
Надстройка Power Pivot есть не во всех версиях Excel. На сайте Microsoft можно посмотреть, в каких она имеется:
Где есть Power Pivot?https://mif.to/rfIJH
Для Excel 2010 надстройку можно скачать отдельно на сайте Microsoft:
Download Microsoft® SQL Server® 2012 SP2 PowerPivot для Microsoft Excel® 2010 from Official Microsoft Download Center.
https://www.microsoft.com/ru-RU/download/details.aspx?id=43348
Для работы с Power Pivot ее необходимо активировать.
Это делается в параметрах Excel:
Файл → Параметры → Надстройки → Управление: Надстройки COM → Перейти
(File → Options → Add-ins → Manage: COM Add-ins → Go).
В появившемся диалоговом окне «Надстройки COM» необходимо включить галочки у Power Pivot, можно также сделать это с Power Map — надстройкой, которая позволяет визуализировать данные из модели данных (то есть Power Pivot) на картах.
После активации на ленте Excel появится отдельная вкладка Power Pivot.
А 3D-карта (надстройка Power Map) открывается из вкладки «Вставка» (Insert), как и другие диаграммы.
Загрузка данных в Power Pivot с помощью встроенного импорта
Файлы с примерами:
Строим модель данных.xlsx
Папка «Источники — модель данных»
В Power Pivot можно импортировать данные из внешних источников с помощью встроенного импорта, но список возможных источников ограничен: гораздо больше вариантов в Power Query, которая позволяет загружать данные в Power Pivot.
В самом Power Pivot можно импортировать:
• из Access и других систем управления базами данных;
• из SQL Server;
• данные по протоколу OData, поддерживаемому в том числе 1С;
• из книг Excel;
• из текстовых файлов;
• из буфера обмена (просто вставить скопированные данные через Ctrl + V, но в таком случае не будет связи с источником, то есть данные не будут обновляться при изменении источника).
Для импорта данных нужно зайти в окно Power Pivot через вкладку этой надстройки на ленте, нажав кнопку «Управление» (Manage), а далее:
Power Pivot → Главная → Получение внешних данных
(Power Pivot → Home → Get External Data).
После выбора типа источника появится мастер импорта, в котором необходимо будет нажать «Обзор» (Browse) и выбрать файл на диске.
Для текстовых файлов бывает необходимо выбрать правильную кодировку (это можно сделать, просто перебирая варианты и глядя на результат в предпросмотре).
После изменения кодировки корректные данные будут отображаться в предпросмотре. Также нужно выбрать правильный разделитель (сейчас все данные — в один столбец).
После выбора верного разделителя данные разобьются по столбцам.
После импорта мастер сообщит о количестве строк в источнике.
И данные появятся в редакторе Power Pivot. Здесь их можно просматривать, но нельзя редактировать отдельные ячейки.
Загрузка данных в Power Pivot с помощью Power Query
Чтобы использовать загруженные через Power Query данные в Power Pivot, после импорта (и при необходимости преобразования в Power Query) нажмите «Загрузить в» (Load To) и активируйте галочку «Добавить эти данные в модель данных» (Add this data to the Data Model).
Если при этом вам не нужно выводить эти данные на лист Excel как сводную таблицу, а только загрузить в Power Pivot для дальнейшей обработки в модели данных, выберите способ «Только создать подключение» (Only Create Connection). Тогда вы загрузите данные в модель Power Pivot, а на листах книги Excel они никак не отобразятся.