Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Для версий Excel 2010 и 2013 ее можно скачать бесплатно на сайте Microsoft (https://www.microsoft.com/ru-RU/download/details.aspx?id=39379) в качестве отдельной надстройки (она будет отображаться как отдельная вкладка на ленте инструментов с названием Power Query, а не внутри вкладки «Данные», как в новых версиях).

С помощью Power Query можно импортировать данные из десятков типов внешних источников и загрузить в виде таблицы Excel или сводной таблицы. Кроме того, можно не только загрузить, но и преобразовать данные, чтобы облегчить их дальнейшую обработку в Excel.

Импорт данных с помощью Power Query

Файлы с примерами:

2 Загружаем данные из внешних источников.xlsx

Продажи. txt

Для начала импорта необходимо нажать на «Получить данные» (Get Data) и выбрать необходимый тип источника в одном из списков.

Импорт данных из интернета

Разберем процесс загрузки на примере данных из интернета. Для этого нужно выбрать соответствующий пункт:

Данные → Получить данные → Из других источников → Из интернета

(Data → Get Data → From Other Sources → From Web).

В случае отдельной вкладки с надстройкой Power Query на ленте (в старых версиях):

Power Query → Из других источников → Из интернета

(Power Query → From Other Sources → From Web).

После этого откроется диалоговое окно, в котором нужно указать адрес страницы — источника данных.

После нажатия на кнопку «ОК» появится диалоговое окно «Навигатор» (Navigator). Слева в нем будет список источников данных (таблиц) на странице. Методом перебора можно найти необходимую — содержимое таблицы будет отображаться справа, в окне предварительного просмотра.

После этого у нас есть несколько возможных действий.

• «Загрузить» (Load). Данные загрузятся на новый лист Excel в виде таблицы без изменений.

• «Загрузить в» (Load To) — кнопка справа от «Загрузить». Откроется новое окно, в котором можно будет выбрать не таблицу, а сводную таблицу, — тогда данные не будут отображаться на листе Excel как есть, а будет построена сводная таблица на их основе. Если в этом окне выбрать «Добавить в модель данных», то данные сразу попадут в Power Pivot (модель данных) как один из источников.

• «Преобразовать данные» (Transform Data). Откроется редактор Power Query, в котором можно будет изменить данные — убрать некоторые столбцы, изменить значения, отсортировать или отфильтровать их и проделать многие другие действия.

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

Пример импорта данных из другого источника — папки с несколькими файлами, например несколькими книгами Excel.

Файлы с примерами:

• Сводная из папки.xlsx

• Папка «Филиалы»

Если данные хранятся в разных книгах Excel, их не обязательно предварительно собирать вручную — можно воспользоваться надстройкой Power Query.

В появившемся окне выберите папку с файлами для загрузки.

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

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

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

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

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

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

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

Обработка данных в Power Query

Power Query позволяет не только загружать данные из внешних источников, но и преобразовывать их, подготавливая к дальнейшему анализу с помощью сводных таблиц Excel или использованию как одного из источников в модели данных Power Pivot.