Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

Финансы в Excel

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

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

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

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

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

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

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

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

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

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

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

Используя встроенные средства автоматизации Excel, можно решить множество нестандартных задач. Пример реализации редактирования для сводных таблиц 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, это хорошо.

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

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

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

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

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

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

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

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

Как Добавить Таблицу в Модель Данных Excel • Сводная диаграмма

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

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Чтобы отформатировать итоговые значения сводной таблицы с помощью любого из числовых форматов Excel, выполните следующие действия. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Сводные таблицы в Excel позволяют экспериментировать с итоговыми данными в динамическом режиме. Кроме того, можно воспользоваться графическим образом сводной таблицы — сводной диаграммой, которая позволяет увидеть результаты анализа данных.

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

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

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

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

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

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

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: