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

После изменения кодировки корректные данные будут отображаться в предпросмотре. Также нужно выбрать правильный разделитель (сейчас все данные — в один столбец).

После выбора верного разделителя данные разобьются по столбцам.

После импорта мастер сообщит о количестве строк в источнике.

И данные появятся в редакторе 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 они никак не отобразятся.

Настройка модели данных

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

«Главная» → группа «Просмотр» → «Представление диаграммы» (Home → View → Diagram View).

В этом режиме отображаются все загруженные в Power Pivot источники данных со списком их полей (столбцов). Здесь можно установить связь между ними, для этого достаточно потянуть левой кнопкой мыши за поле в одной таблице и соединить его линией с другой. Разумеется, нужно, чтобы в обеих таблицах был столбец с ключом, по которому можно установить связь (проводя аналогии с ВПР / VLOOKUP и другими функциями поиска, это искомое значение, по которому мы можем связать две таблицы просто на листе Excel с помощью формул).

Вычисляемые столбцы в Power Pivot

В Power Pivot нельзя редактировать отдельные ячейки (здесь нет уровня ячеек, как в Excel), но есть возможность создавать свои вычисляемые столбцы (с формулами).

В таких столбцах ссылаться можно на другие столбцы. В каком-то смысле это похоже на ссылки в рамках таблиц в Excel.

Язык функций здесь свой — DAX (Data Analysis Expressions, выражения для анализа данных). Впрочем, большинство функций будут знакомы вам по Excel — как по логике и синтаксису, так и по названиям (но в DAX они всегда на английском). Здесь есть и логические, и текстовые функции, и функции для работы с датой. Для ссылок на столбцы в других таблицах в рамках модели данных используется функция RELATED.

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

Формулы, как и в Excel, вводятся со знака «равно».

Меры в Power Pivot

Меры — это аналоги вычисляемых полей в сводных таблицах, вычисления, которые производятся в контексте сводной, в ее отчете.

Если в обычных значениях сводной есть совсем небольшой набор операций агрегирования — всего 11 (напомним, по умолчанию это суммирование для чисел и количество для текстовых значений), то в мерах в Power Pivot используются функции DAX, а это означает намного большее количество операций. Например, в обычной сводной нельзя посчитать количество уникальных значений или медианное значение, а в Power Pivot можно — с помощью функций DISCTINCTCOUNT и MEDIAN соответственно.

Меры вводятся в нижней части окна Power Pivot.

Достаточно поставить знак «равно» и начать вводить функцию в любой ячейке.

До двоеточия и знака равно указывается название меры — его можно изменить, по умолчанию будет создано название вида "Мера1". Кроме того, меры можно создавать со вкладки Power Pivot на ленте Excel.

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

После создания меры она будет доступна в сводной, построенной по модели данных.

Создание сводной таблицы на основе модели данных

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

Создать сводную можно с помощью соответствующей кнопки на ленте Power Pivot.

Эту сводную, как и обычную в Excel, можно разместить на отдельном или на существующем листе.

При обновлении сводных, построенных на основе модели данных, будут обновляться связи со всеми источниками в модели Power Pivot, а затем и данные в сводной таблице.

Визуализация данных

Спарклайны

Файл с примерами: Спарклайны.xlsx