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

В параметрах нечеткого соответствия можно установить коэффициент подобия (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) и выбрать файл на диске.

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