Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Импорт данных из интернета
Дальше: Power Pivot

Импорт папки с файлами

Пример импорта данных из другого источника — папки с несколькими файлами, например несколькими книгами Excel.
Файлы с примерами:
• Сводная из папки.xlsx
• Папка «Филиалы»
Если данные хранятся в разных книгах Excel, их не обязательно предварительно собирать вручную — можно воспользоваться надстройкой Power Query.

 

 

В появившемся окне выберите папку с файлами для загрузки.
Таблицы в файлах должны быть с одинаковой структурой (по столбцам), хотя число строк может отличаться. Конечно, данные должны подходить для создания сводной, ведь наша конечная цель именно в этом!

 

 

Далее нужно выбрать элементы, из которых будут загружаться данные. Power Query видит таблицы и листы, они помечены своими иконками (важно, чтобы в разных книгах при загрузке целой папки они назывались одинаково).

 

Если вам нужно сначала посмотреть список всех объектов в файлах, а не выбирать сразу листы или таблицы, то нажмите на «Параметр1» на этом шаге

 

После выбора объекта (таблицы или листа) откроется окно редактора Power Query. В нем можно преобразовать данные, почистить их, отфильтровать, поменять форматы — все, что вы сделаете здесь, будет каждый раз происходить с данными при их обновлении.

 

Чтобы перенести данные отсюда на лист Excel в виде таблицы или сводной, выбирайте «Закрыть и загрузить в» (Close & Load To…) — далее можно будет выбрать, в каком виде данные можно загрузить

 

 

После чего будет построена обычная сводная, но источником для нее будет выступать подключение к четырем файлам.

 

 

При обновлении сводной будет обновляться подключение: будут загружены данные из всех файлов-источников и над ними будут проделаны те операции, которые записаны в редакторе Power Query (например, замена какого-то текста на другой, изменение форматов, фильтрация данных и так далее).
Обработка данных в Power Query
Power Query позволяет не только загружать данные из внешних источников, но и преобразовывать их, подготавливая к дальнейшему анализу с помощью сводных таблиц Excel или использованию как одного из источников в модели данных Power Pivot.
Если при импорте вы выберете «Преобразовать» (Transform Data), а не «Загрузить» (Load), откроется отдельное окно редактора Power Query, в котором можно производить манипуляции с данными.

 

 

Вот так выглядит окно Power Query:

 

 

Манипуляции с данными можно осуществлять как через ленту, так и с помощью контекстного меню.
В пункте «Преобразование» есть ряд операций, позволяющих изменять числа или текст.

 

 

Многие преобразования имеют аналоги в Excel — в виде инструментов («Найти и заменить» в Excel, «Замена значений» в Power Query) или функций (как ПРОПИСН / UPPER — здесь ВЕРХНИЙ РЕГИСТР). Но преимущество здесь и в быстродействии, и в том, что все преобразования будут осуществляться каждый раз при обновлении связи с источником. Не нужно вводить функции, делать что-то вручную, эти действия будут происходить автоматически.
Что можно делать с помощью Power Query с импортируемыми данными:
• удалять столбцы;
• переименовывать столбцы;
• заполнять столбцы по образцу (по аналогии с мгновенным заполнением Excel);
• сортировать и фильтровать данные (например, убирать пустые строки);
• изменять регистр текстовых значений;
• заменять один текст/символ на другой;
• очищать данные от лишних пробелов;
• округлять числа и производить с ними другие операции;
• группировать данные по значениям из какого-то столбца (по аналогии со сводными таблицами);
• удалять дубликаты;
• извлекать первые/последние символы из текстовых строк или же значения до и после определенных разделителей;
• добавлять префиксы и суффиксы к текстовым значениям;
• объединять две таблицы по тому или иному параметру (по аналогии с ВПР / VLOOKUP) — об этом уже через пару строк;
• объединять несколько таблиц в одну (вертикально, то есть таблицы с одинаковой структурой, а строки собираются друг под другом);
• и многое другое.
Объединение запросов (таблиц) в Power Query
Файл с примером: Объединение запросов.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