Финансы в Excel

Главная Статьи Сводные таблицы Сводные таблицы

Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «OLAP-технологии» и т.п. Но при детальном разговоре обычно выясняется, что почти все не очень представляют, о чем идет речь. То есть люди подразумевают нечто сложное и обычно не имеющее отношение к их повседневной деятельности. На самом деле это не так.

Многомерные данные, измерения

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

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

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

Виртуальный куб данных

Сводная таблица

Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.

Редактирование сводных таблиц

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

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

Например, в нашей сводной таблице, в качестве поля фильтрации используется Дата. В результате можно отображать данные о расходах и доходах за конкретный период. Для этого сделайте следующее.

Сводные таблицы в Excel: Создание, Фильтрация, Форматирование

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

Подключение Power Query к модели данных Excel Power Pivot

Мы можем подключить практически любой источник данных в Power Query, но модель данных PowerPivot не включена в этот обширный список источников.
С помощью потрясающего DAX Studio мы можем сделать это (хотя, на наш взгляд, это все еще неудобно и сложно) по крайней мере локально — из той же книги Excel или из Power BI Desktop.

Все, что вам нужно, это открыть вашу книгу Excel, запустить надстройку DAX Studio и подключить ее к этой книге. Затем вы можете просто подключиться к модели PowerPivot, как к кубу служб аналитики SQL Server.
Но это недокументированная и крайне ограниченная функция, не поддерживаемая Microsoft, которую можно использовать только на свой страх и риск.

Шаг 1: Старт
Во-первых, нам нужен Источник. Другими словами, книга Excel с некоторыми данными, загруженными в модель данных PowerPivot. Если вы видите таблицу в окне PowerPivot, это хорошо.

Исходные таблицы загружены в модель данных
Которые формируют эту простую модель данных:

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

Только что рассчитанный столбец
И простая мера:

Источником таблиц в модели PowerPivot являются таблицы из этой книги, но они также могут быть из Power Query.

Шаг 2
Чтобы подключить Power Query к вашей модели данных рабочей книги, откройте рабочую книгу. По некоторым причинам мы настаиваем на том, чтобы была открыта только одна рабочая книга! Если одновременно открыть несколько рабочих книг, могут возникнуть проблемы.

Запустите надстройку DAX Studio из группы лент «Надстройки» в Excel. После запуска DAX Studio вы увидите окно подключения, где вы должны выбрать первый вариант:

Подключите DAX Studio к файлу Excel
Нажмите «Подключиться». После того, как соединение установлено, не закрывайте DAX Studio, программа должна быть открыта.
Теперь все готово для подключения.

Шаг 3
Теперь перейдите к Power Query и подключитесь к «базе данных служб аналитики SQL Server (импорт)».

Для этого нам нужно знать адрес сервера. Если бы это подключение было к какому-либо серверу или Power BI Desktop, мы могли бы увидеть его адрес в правом нижнем углу окна DAX Studio:

Адрес открытого файла PBIX в DAX Studio
Но при подключении к файлу Excel мы видим только это:

Нет сервера: адрес порта для файла Excel!
Теперь мы раскрываем главный секрет:
Вам необходимо использовать http://localhost:9000/xmla в качестве имени/адреса сервера. Вот и все.

Используйте вашу текущую аутентификацию Windows для подключения
Ура!

Power Query подключен к модели данных PowerPivot как к кубу
Теперь вы видите модель, все ее вычисляемые столбцы и показатели как куб OLAP. Из него вы можете создавать любые нужные вам таблицы.

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

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

Сводная таблица с текстовыми полями в области значений

В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).

Использование Мастера сводных таблиц

Сводная таблица может быть создана с помощью инструмента под названием “Мастер сводных таблиц”. Но предварительно нужно вынести значок Мастера на Панель быстрого доступа. Для этого выполняем следующую цепочку действий:

Итак, сводную таблицу в Excel можно создать, пользуясь двумя методами: классическим и с помощью Мастера сводных таблиц. Последний охватывает более широкий спектр альтернатив на этапе выбора источников данных, однако, в большинстве случаев первый способ более чем достаточен для для конструирования сводной таблицы.

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

Импорт данных SQL в Excel

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