Excel Power Pivot — Обзор

Excel Power Pivot — это эффективный и мощный инструмент, который поставляется вместе с Excel в качестве надстройки. С Power Pivot вы можете загружать сотни миллионов строк данных из внешних источников и эффективно управлять данными с помощью мощного механизма xVelocity в сильно сжатой форме. Это позволяет выполнять расчеты, анализировать данные и получать отчет для выработки выводов и решений. Таким образом, человек, имеющий практический опыт работы с Excel, сможет выполнить анализ данных высокого уровня и принять решение за считанные минуты.

Этот урок будет охватывать следующее —

Power Pivot Особенности

Что делает Power Pivot сильным инструментом, так это набор его функций. Вы узнаете о различных функциях Power Pivot в главе «Функции Power Pivot».

Данные Power Pivot из различных источников

Power Pivot может сопоставлять данные из различных источников данных для выполнения необходимых расчетов. Вы узнаете, как получить данные в Power Pivot, в главе «Загрузка данных в Power Pivot».

Модель данных Power Pivot

Сила Power Pivot заключается в его базе данных — модели данных. Данные хранятся в виде таблиц данных в модели данных. Вы можете создать отношения между таблицами данных, чтобы объединить данные из разных таблиц данных для анализа и составления отчетов. Глава — Понимание модели данных (Power Pivot Database) дает вам подробную информацию о модели данных.

Управление моделью данных и отношениями

Вам необходимо знать, как вы можете управлять таблицами данных в модели данных и связями между ними. Подробнее об этом вы узнаете в главе «Управление моделью данных Power Pivot».

Создание Power Pivot Tables и Power Pivot Charts

Power PivotTables и Power Pivot Chart предоставляют вам возможность проанализировать данные, чтобы прийти к выводам и / или решениям.

Вы узнаете, как создавать сводные таблицы Power в главах — Создание сводных таблиц Power и плоских сводных таблиц.

Вы узнаете, как создавать Power PivotCharts в главе — Power PivotCharts.

Основы DAX

DAX — это язык, используемый в Power Pivot для выполнения вычислений. Формулы в DAX аналогичны формулам Excel, но с одним отличием: формулы Excel основаны на отдельных ячейках, а формулы DAX — на столбцах (полях).

Вы поймете основы DAX в главе — Основы DAX.

Изучение и представление данных Power Pivot

Вы можете изучить данные Power Pivot, которые находятся в модели данных, с помощью Power PivotTables и Power Pivot Charts. В этом уроке вы узнаете, как вы можете исследовать данные и сообщать о них.

Иерархии

Вы можете определить иерархии данных в таблице данных, чтобы было легко обрабатывать связанные поля данных вместе в Power PivotTables. Вы узнаете подробности создания и использования Иерархий в главе «Иерархии в Power Pivot».

Эстетические отчеты

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

Эти подробности вы узнаете в главе «Эстетические отчеты с данными Power Pivot».

Excel Power Pivot — Установка

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

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

Power Pivot на ленте

Первый шаг для продолжения Power Pivot — убедиться, что на ленте доступна вкладка POWERPIVOT. Если у вас Excel 2013 или более поздние версии, на ленте появится вкладка POWERPIVOT.

Если у вас Excel 2010, вкладка POWERPIVOT может не отображаться на ленте, если вы еще не включили надстройку Power Pivot.

Надстройка Power Pivot

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

Шаг 1 — Перейдите на вкладку ФАЙЛ на ленте.

Шаг 2 — Нажмите Параметры в раскрывающемся списке. Откроется диалоговое окно «Параметры Excel».

Шаг 3 — Следуйте инструкциям следующим образом.

• Нажмите Надстройки.

• В поле Управление выберите Надстройки COM из раскрывающегося списка.

• Нажмите кнопку «Перейти». Откроется диалоговое окно «Надстройки COM».

• Проверьте Power Pivot и нажмите ОК.

Нажмите Надстройки.

В поле Управление выберите Надстройки COM из раскрывающегося списка.

Нажмите кнопку «Перейти». Откроется диалоговое окно «Надстройки COM».

Проверьте Power Pivot и нажмите ОК.

Что такое Power Pivot?

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

Лента Power Pivot, как показано ниже, содержит различные команды — от управления моделью данных до создания отчетов.

Окно Power Pivot будет иметь ленту, как показано ниже —

Почему Power Pivot — сильный инструмент?

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

Удобный интерфейс Power Pivot в окне PowerPivot позволяет выполнять операции с данными без знания какого-либо языка запросов к базе данных. Затем вы можете создать отчет о вашем анализе в течение нескольких секунд. Отчеты являются универсальными, динамичными и интерактивными и позволяют вам дополнительно исследовать данные, чтобы получить представление и прийти к выводам / решениям.

Данные, с которыми вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных внутри книги Excel, а мощный локальный механизм загружает, запрашивает и обновляет данные в этой базе данных. Поскольку данные находятся в Excel, они сразу доступны для сводных таблиц, сводных диаграмм, Power View и других функций Excel, которые вы используете для агрегирования и взаимодействия с данными. Представление данных и интерактивность обеспечиваются Excel, а объекты данных и презентации Excel содержатся в одном файле рабочей книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с 4 ГБ данных в памяти.

Мощные функции в Excel с Power Pivot

Функции Power Pivot бесплатны в Excel. Power Pivot повысил производительность Excel благодаря мощным функциям, которые включают следующее:

• Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.

• Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.

• Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.

• Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.

• Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.

• Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.

• Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.

• Создайте KPI для использования в сводных таблицах и отчетах Power View.

Способность обрабатывать большие объемы данных, сжатые в небольшие файлы, с удивительной скоростью.

Фильтруйте данные и переименовывайте столбцы и таблицы при импорте.

Организовать таблицы в отдельные страницы с вкладками в окне Power Pivot по сравнению с таблицами Excel, распределенными по всей книге или по нескольким таблицам в одной рабочей таблице.

Создайте связи между таблицами, чтобы совместно анализировать данные в таблицах. До Power Pivot приходилось полагаться на интенсивное использование функции VLOOKUP, чтобы объединить данные в одну таблицу перед таким анализом. Раньше это было трудоемким и подверженным ошибкам.

Добавьте мощность в простую сводную таблицу со многими дополнительными функциями.

Предоставить язык выражений анализа данных (DAX) для написания расширенных формул.

Добавьте вычисляемые поля и вычисляемые столбцы в таблицы данных.

Создайте KPI для использования в сводных таблицах и отчетах Power View.

Вы поймете особенности Power Pivot подробно в следующей главе.

Использование Power Pivot

Вы можете использовать Power Pivot для следующих целей:

• Для выполнения мощного анализа данных и создания сложных моделей данных.

• Быстрое объединение больших объемов данных из нескольких разных источников.

• Для анализа информации и обмена знаниями в интерактивном режиме.

• Для написания расширенных формул на языке выражений анализа данных (DAX).

• Создать ключевые показатели эффективности (KPI).

Для выполнения мощного анализа данных и создания сложных моделей данных.

Быстрое объединение больших объемов данных из нескольких разных источников.

Для анализа информации и обмена знаниями в интерактивном режиме.

Для написания расширенных формул на языке выражений анализа данных (DAX).

Создать ключевые показатели эффективности (KPI).

Моделирование данных с помощью Power Pivot

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

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

Вы можете определить иерархии в данных, чтобы использовать их везде в книге, включая Power View. Вы можете создавать KPI для использования в сводных таблицах и отчетах Power View, чтобы сразу увидеть, является ли производительность включенной или выключенной для одной или нескольких метрик.

Бизнес-аналитика с Power Pivot

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

Вы можете предоставить доступ к своей книге в среду SharePoint, в которой включены службы Excel. На сервере SharePoint службы Excel обрабатывают и отображают данные в окне браузера, где другие могут анализировать данные.

Excel Power Pivot — Возможности

Наиболее важной и мощной особенностью Power Pivot является его база данных — модель данных. Следующей важной особенностью является механизм аналитики в памяти xVelocity, который позволяет работать с несколькими большими базами данных в течение нескольких минут. Есть несколько более важных функций, которые поставляются с надстройкой PowerPivot.

В этой главе вы получите краткий обзор функций Power Pivot, которые подробно будут показаны позже.

Загрузка данных из внешних источников

Вы можете загрузить данные в модель данных из внешних источников двумя способами:

• Загрузите данные в Excel, а затем создайте модель данных Power Pivot.

• Загрузка данных непосредственно в модель данных Power Pivot.

Загрузите данные в Excel, а затем создайте модель данных Power Pivot.

Загрузка данных непосредственно в модель данных Power Pivot.

Второй способ более эффективен благодаря эффективному способу обработки данных в памяти Power Pivot.

Для получения более подробной информации см. Главу «Загрузка данных в Power Pivot».

Окно Excel и Power Pivot Window

Когда вы начнете работать с Power Pivot, одновременно откроются два окна — окно Excel и окно Power Pivot. Именно через окно PowerPivot вы можете напрямую загружать данные в модель данных, просматривать данные в представлениях данных и представлениях диаграмм, создавать связи между таблицами, управлять связями и создавать отчеты Power PivotTable и / или PowerPivot Chart.

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

При создании сводной таблицы или сводной диаграммы из окна Power Pivot они создаются в окне Excel. Однако данные по-прежнему управляются из модели данных.

Вы всегда можете легко переключаться между окном Excel и окном Power Pivot в любое время.

Модель данных

Модель данных — самая мощная функция Power Pivot. Данные, полученные из различных источников данных, поддерживаются в модели данных в виде таблиц данных. Вы можете создать отношения между таблицами данных, чтобы объединить данные в таблицах для анализа и составления отчетов.

Подробнее о модели данных вы узнаете в главе «Общие сведения о модели данных (Power Pivot Database)».

Оптимизация памяти

Модель данных Power Pivot использует хранилище xVelocity, которое сильно сжимается при загрузке данных в память, что позволяет хранить в памяти сотни миллионов строк.

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

Компактный размер файла

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

Power PivotTables

Вы можете создать Power PivotTables из окна Power Pivot. Созданные таким образом сводные таблицы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов.

Power PivotCharts

Вы можете создать Power PivotCharts из окна Power Pivot. Созданные таким образом сводные диаграммы основаны на таблицах данных в модели данных, что позволяет объединять данные из связанных таблиц для анализа и составления отчетов. Power PivotCharts обладает всеми функциями сводных диаграмм Excel и многими другими, такими как кнопки полей.

Вы также можете иметь комбинации Power PivotTable и Power PivotChart.

DAX Language

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

Excel Power Pivot — загрузка данных

В этой главе мы научимся загружать данные в Power Pivot.

Вы можете загрузить данные в Power Pivot двумя способами:

• Загрузить данные в Excel и добавить их в модель данных

• Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.

Загрузить данные в Excel и добавить их в модель данных

Загружайте данные непосредственно в PowerPivot, заполняя модель данных, которая является базой данных PowerPivot.

Если вам нужны данные для Power Pivot, сделайте это вторым способом, даже если Excel даже не знает об этом. Это потому, что вы будете загружать данные только один раз, в сильно сжатом формате. Чтобы понять разницу, предположим, что вы загружаете данные в Excel, сначала добавляя их в модель данных, размер файла, скажем, 10 МБ.

Если вы загружаете данные в PowerPivot и, следовательно, в модель данных, пропуская дополнительный шаг Excel, размер вашего файла может составлять всего 1 МБ.

Источники данных, поддерживаемые Power Pivot

Вы можете либо импортировать данные в модель данных Power Pivot из различных источников данных, либо устанавливать соединения и / или использовать существующие соединения. Power Pivot поддерживает следующие источники данных —

• Реляционная база данных SQL Server

• База данных Microsoft Access

• Службы анализа SQL Server

• Службы отчетов SQL Server (SQL 2008 R2)

• Каналы данных ATOM

• Текстовые файлы

• Microsoft SQL Azure

• оракул

• Teradata

• Sybase

• Informix

• IBM DB2

• Связывание объектов и встраивание базы данных / Открытая связь с базой данных

• (OLEDB / ODBC) источники

• Файл Microsoft Excel

• Текстовый файл

Реляционная база данных SQL Server

База данных Microsoft Access

Службы анализа SQL Server

Службы отчетов SQL Server (SQL 2008 R2)

Каналы данных ATOM

Текстовые файлы

Microsoft SQL Azure

оракул

Teradata

Sybase

Informix

IBM DB2

Связывание объектов и встраивание базы данных / Открытая связь с базой данных

Файл Microsoft Excel

Текстовый файл

Загрузка данных непосредственно в PowerPivot

Чтобы загрузить данные непосредственно в Power Pivot, выполните следующее:

• Откройте новую рабочую книгу.

• Нажмите на вкладку POWERPIVOT на ленте.

• Нажмите «Управление» в группе «Модель данных».

Откройте новую рабочую книгу.

Нажмите на вкладку POWERPIVOT на ленте.

Нажмите «Управление» в группе «Модель данных».

Откроется окно PowerPivot. Теперь у вас есть два окна — окно книги Excel и окно PowerPivot для Excel, которое связано с вашей книгой.

• Перейдите на вкладку « Главная » в окне PowerPivot.

• Нажмите Из базы данных в группе Получить внешние данные.

• Выберите из доступа .

Перейдите на вкладку « Главная » в окне PowerPivot.

Нажмите Из базы данных в группе Получить внешние данные.

Выберите из доступа .

Появится мастер импорта таблиц.

• Перейдите к файлу базы данных Access.

• Укажите имя дружественного соединения.

• Если база данных защищена паролем, заполните эти данные также.

Перейдите к файлу базы данных Access.

Укажите имя дружественного соединения.

Если база данных защищена паролем, заполните эти данные также.

Нажмите кнопку Далее →. Мастер импорта таблиц отображает варианты выбора способа импорта данных.

Нажмите Выбрать из списка таблиц и представлений, чтобы выбрать данные для импорта.

Нажмите кнопку Далее →. Мастер импорта таблиц отображает таблицы и представления в выбранной вами базе данных Access.

Установите флажок Медали.

Как вы можете заметить, вы можете выбрать таблицы, установив флажки, просмотреть и отфильтровать таблицы перед добавлением в сводную таблицу и / или выбрать связанные таблицы.

Нажмите кнопку « Просмотр и фильтр» .

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

• Нажмите ОК.

• Нажмите кнопку « Выбрать связанные таблицы» .

• Power Pivot проверяет, какие другие таблицы связаны с выбранной таблицей медалей, если существует отношение.

Нажмите ОК.

Нажмите кнопку « Выбрать связанные таблицы» .

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

Вы можете видеть, что Power Pivot обнаружил, что таблица Disciplines связана с таблицей Медали, и выбрал ее. Нажмите Готово.

Отображается мастер импорта таблиц — Импорт и отображается состояние импорта. Это займет несколько минут, и вы можете остановить импорт, нажав кнопку « Остановить импорт» .

После импорта данных мастер импорта таблиц отображает — Успешно и показывает результаты импорта, как показано на снимке экрана ниже. Нажмите Закрыть.

Power Pivot отображает две импортированные таблицы в двух вкладках.

Вы можете прокручивать записи (строки таблицы), используя стрелки записи под вкладками.

Мастер импорта таблиц

В предыдущем разделе вы узнали, как импортировать данные из Access с помощью мастера импорта таблиц.

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

Нажмите Из других источников в окне Power Pivot.

Появится мастер импорта таблиц — подключение к источнику данных . Вы можете создать соединение с источником данных или использовать уже существующее.

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

• Прокрутите вниз до текстовых файлов.

• Выберите файл Excel .

Прокрутите вниз до текстовых файлов.

Выберите файл Excel .

• Нажмите кнопку Далее →. Появится мастер импорта таблиц — подключиться к файлу Microsoft Excel.

• Перейдите к файлу Excel в поле «Путь к файлу Excel».

• Установите флажок — Использовать первую строку в качестве заголовков столбцов .

Нажмите кнопку Далее →. Появится мастер импорта таблиц — подключиться к файлу Microsoft Excel.

Перейдите к файлу Excel в поле «Путь к файлу Excel».

Установите флажок — Использовать первую строку в качестве заголовков столбцов .

• Нажмите кнопку Далее →. Отобразится мастер импорта таблиц: выберите таблицы и представления .

• Установите флажок Каталог продукции $ . Нажмите кнопку Готово .

Нажмите кнопку Далее →. Отобразится мастер импорта таблиц: выберите таблицы и представления .

Установите флажок Каталог продукции $ . Нажмите кнопку Готово .

Вы увидите следующее сообщение об успехе . Нажмите Закрыть.

Вы импортировали одну таблицу, а также создали соединение с файлом Excel, который содержит несколько других таблиц.

Открытие существующих подключений

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

Нажмите «Существующие подключения» в окне PowerPivot.

Откроется диалоговое окно «Существующие подключения». Выберите Excel Sales Data из списка.

Нажмите кнопку Открыть. Мастер импорта таблиц отображает таблицы и представления.

Выберите таблицы, которые вы хотите импортировать, и нажмите Готово .

Выбранные пять таблиц будут импортированы. Нажмите Закрыть .

Вы можете видеть, что пять таблиц добавляются в Power Pivot, каждая на новой вкладке.

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

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

Вы можете связать таблицу Excel с Power Pivot в несколько шагов следующим образом:

• Создайте таблицу Excel с данными.

• Нажмите вкладку POWERPIVOT на ленте.

• Нажмите Добавить в модель данных в группе Таблицы.

Создайте таблицу Excel с данными.

Нажмите вкладку POWERPIVOT на ленте.

Нажмите Добавить в модель данных в группе Таблицы.

Таблица Excel связана с соответствующей таблицей данных в PowerPivot.

Вы можете видеть, что Table Tools с вкладкой — Связанная таблица добавляется в окно Power Pivot. Если вы нажмете Перейти к таблице Excel , вы переключитесь на лист Excel. Если вы нажмете « Управление» , вы вернетесь к связанной таблице в окне Power Pivot.

Вы можете обновить связанную таблицу автоматически или вручную.

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

Загрузка из файлов Excel

Если вы хотите загрузить данные из книг Excel, имейте в виду следующее:

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

• Power Pivot загружает каждый лист в виде таблицы.

• Power Pivot не может распознавать отдельные таблицы. Следовательно, Power Pivot не может распознать, если на листе есть несколько таблиц.

• Power Pivot не может распознать любую дополнительную информацию, кроме таблицы на листе.

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

Power Pivot загружает каждый лист в виде таблицы.

Power Pivot не может распознавать отдельные таблицы. Следовательно, Power Pivot не может распознать, если на листе есть несколько таблиц.

Power Pivot не может распознать любую дополнительную информацию, кроме таблицы на листе.

Следовательно, держите каждую таблицу в отдельном рабочем листе.

Когда ваши данные в рабочей книге готовы, вы можете импортировать данные следующим образом:

• Нажмите Из других источников в группе Получить внешние данные в окне Power Pivot.

• Выполните действия, указанные в разделе «Мастер импорта таблиц».

Нажмите Из других источников в группе Получить внешние данные в окне Power Pivot.

Выполните действия, указанные в разделе «Мастер импорта таблиц».

Ниже приведены различия между связанными таблицами Excel и импортированными таблицами Excel.

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

• Функция импорта в Excel позволяет загружать данные из разных книг Excel.

• Загрузка данных из книги Excel не создает связь между двумя файлами. Power Pivot создает только копию данных при импорте.

• При обновлении исходного файла Excel данные в Power Pivot обновляться не будут. Вам нужно либо установить автоматический режим обновления, либо обновить данные вручную, на вкладке Связанная таблица окна Power Pivot.

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

Функция импорта в Excel позволяет загружать данные из разных книг Excel.

Загрузка данных из книги Excel не создает связь между двумя файлами. Power Pivot создает только копию данных при импорте.

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

Загрузка из текстовых файлов

Один из популярных стилей представления данных имеет формат, известный как значения, разделенные запятыми (csv). Каждая строка / запись данных представлена ​​текстовой строкой, в которой столбцы / поля разделены запятыми. Многие базы данных предоставляют возможность сохранения в файл формата csv.

Если вы хотите загрузить CSV-файл в Power Pivot, вы должны использовать опцию Текстовый файл. Предположим, у вас есть следующий текстовый файл в формате CSV —

• Откройте вкладку PowerPivot.

• Перейдите на вкладку «Главная» в окне PowerPivot.

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

• Прокрутите вниз до текстовых файлов.

Откройте вкладку PowerPivot.

Перейдите на вкладку «Главная» в окне PowerPivot.

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

Прокрутите вниз до текстовых файлов.

• Нажмите Текстовый файл.

• Нажмите кнопку Далее →. На экране появится Мастер импорта таблиц — Подключиться к плоскому файлу.

• Найдите текстовый файл в поле «Путь к файлу». Файлы csv обычно имеют первую строку, представляющую заголовки столбцов.

• Установите флажок Использовать первую строку в качестве заголовков столбцов, если в первой строке есть заголовки.

• В поле Разделитель столбцов по умолчанию используется запятая (,), но в случае, если в вашем текстовом файле есть какой-либо другой оператор, такой как Tab, точка с запятой, пробел, двоеточие или вертикальная черта, выберите этот оператор.

Нажмите Текстовый файл.

Нажмите кнопку Далее →. На экране появится Мастер импорта таблиц — Подключиться к плоскому файлу.

Найдите текстовый файл в поле «Путь к файлу». Файлы csv обычно имеют первую строку, представляющую заголовки столбцов.

Установите флажок Использовать первую строку в качестве заголовков столбцов, если в первой строке есть заголовки.

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

Как вы можете заметить, есть предварительный просмотр вашей таблицы данных. Нажмите Готово.

Power Pivot создает таблицу данных в модели данных.

Загрузка из буфера обмена

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

• Скопируйте данные в файл Excel и используйте файл Excel в качестве источника данных для Power Pivot.

• Скопируйте данные, чтобы они были в буфере обмена, и вставьте их в Power Pivot.

Скопируйте данные в файл Excel и используйте файл Excel в качестве источника данных для Power Pivot.

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

Вы уже изучили первый вариант в предыдущем разделе. И это предпочтительнее второго варианта, как вы найдете в конце этого раздела. Однако вы должны знать, как копировать данные из буфера обмена в Power Pivot.

Предположим, у вас есть данные в текстовом документе следующим образом:

Word не является источником данных для Power Pivot. Поэтому выполните следующее —

• Выберите таблицу в документе Word.

• Скопируйте и вставьте его в окно PowerPivot.

Выберите таблицу в документе Word.

Скопируйте и вставьте его в окно PowerPivot.

Откроется диалоговое окно Paste Preview .

• Дайте имя в виде таблицы Word-Employee .

• Установите флажок Использовать первую строку в качестве заголовков столбцов и нажмите ОК.

Дайте имя в виде таблицы Word-Employee .

Установите флажок Использовать первую строку в качестве заголовков столбцов и нажмите ОК.

Данные, скопированные в буфер обмена, будут вставлены в новую таблицу данных в Power Pivot с вкладкой — Таблица Word-Employee.

Предположим, вы хотите заменить эту таблицу новым содержанием.

• Скопируйте таблицу из Word.

• Нажмите Вставить и заменить.

Скопируйте таблицу из Word.

Нажмите Вставить и заменить.

Откроется диалоговое окно Paste Preview. Проверьте содержимое, которое вы используете для замены.

Нажмите ОК.

Как вы можете видеть, содержимое таблицы данных в Power Pivot заменяется содержимым в буфере обмена.

Предположим, вы хотите добавить две новые строки данных в таблицу данных. В таблице в документе Word у вас есть две строки новостей.

• Выберите две новые строки.

• Нажмите Копировать.

• Нажмите Вставить приложение в окне Power Pivot. Откроется диалоговое окно Paste Preview.

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

Выберите две новые строки.

Нажмите Копировать.

Нажмите Вставить приложение в окне Power Pivot. Откроется диалоговое окно Paste Preview.

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

Нажмите OK, чтобы продолжить.

Как вы можете заметить, содержимое таблицы данных в Power Pivot добавляется к содержимому в буфере обмена.

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

Это из-за следующих причин —

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

• В файле Word имеется информация об отслеживании, например, когда данные заменяются и когда данные добавляются. Однако скопировать эту информацию в Power Pivot невозможно. Если вы сначала скопируете данные в файл Excel, вы можете сохранить эту информацию для последующего использования.

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

• Нет способа обновить данные, скопированные из буфера обмена. Если данные взяты из связанной таблицы, вы всегда можете убедиться, что данные обновлены.

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

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

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

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

Обновление данных в Power Pivot

Вы можете обновить данные, импортированные из внешних источников данных, в любой момент времени.

Если вы хотите обновить только одну таблицу данных в Power Pivot, выполните следующие действия:

• Нажмите на вкладку таблицы данных.

• Нажмите Обновить.

• Выберите Обновить из выпадающего списка.

Нажмите на вкладку таблицы данных.

Нажмите Обновить.

Выберите Обновить из выпадающего списка.

Если вы хотите обновить все таблицы данных в Power Pivot, выполните следующие действия:

• Нажмите кнопку Обновить.

• Выберите Обновить все из выпадающего списка.

Нажмите кнопку Обновить.

Выберите Обновить все из выпадающего списка.

Excel Power Pivot — модель данных

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

Основное использование модели данных в Excel — это использование Power Pivot. Модель данных может рассматриваться как база данных Power Pivot, а все функции управления питанием Power Pivot управляются с помощью модели данных. Все операции с данными с Power Pivot носят явный характер и могут быть визуализированы в модели данных.

В этой главе вы подробно разберетесь в модели данных.

Excel и модель данных

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

Если вы хотите взглянуть на модель данных, вы можете сделать это следующим образом:

• Нажмите вкладку POWERPIVOT на ленте.

• Нажмите Управление.

Нажмите вкладку POWERPIVOT на ленте.

Нажмите Управление.

Модель данных, если она существует в книге, будет отображаться в виде таблиц, каждая из которых имеет вкладку.

Примечание. Если вы добавите таблицу Excel в модель данных, вы не преобразуете таблицу Excel в таблицу данных. Копия таблицы Excel добавляется в качестве таблицы данных в модель данных, и между ними создается связь. Следовательно, если изменения вносятся в таблицу Excel, таблица данных также обновляется. Однако с точки зрения хранения есть две таблицы.

Power Pivot и модель данных

Модель данных по своей сути является базой данных Power Pivot. Даже когда вы создаете модель данных из Excel, она создает только базу данных Power Pivot. Создание модели данных и / или добавление данных выполняется явно в Power Pivot.

Фактически вы можете управлять моделью данных из окна Power Pivot. Вы можете добавлять данные в модель данных, импортировать данные из разных источников данных, просматривать модель данных, создавать связи между таблицами, создавать вычисляемые поля и вычисляемые столбцы и т. Д.

Создание модели данных

Вы можете добавить таблицы в модель данных из Excel или напрямую импортировать данные в Power Pivot, создавая таким образом таблицы модели данных Power Pivot. Вы можете просмотреть модель данных, нажав Управление в окне Power Pivot.

Вы поймете, как добавить таблицы из Excel в модель данных в главе «Загрузка данных через Excel». Вы поймете, как загрузить данные в модель данных в главе «Загрузка данных в Power Pivot».

Таблицы в модели данных

Таблицы в модели данных могут быть определены как набор таблиц, содержащих взаимосвязи между ними. Отношения позволяют объединять связанные данные из разных таблиц для анализа и составления отчетов.

Таблицы в модели данных называются таблицами данных.

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

Таблицы Excel и таблицы данных

Таблицы Excel — это просто набор отдельных таблиц. На рабочем листе может быть несколько таблиц. Доступ к каждой таблице возможен отдельно, но невозможно получить доступ к данным из более чем одной таблицы Excel одновременно. Это причина того, что при создании сводной таблицы она основана только на одной таблице. Если вам нужно использовать данные из двух таблиц Excel вместе, вам необходимо сначала объединить их в одну таблицу Excel.

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

Таблицы данных в модели данных можно просматривать двумя способами:

• Просмотр данных.

• Диаграмма

Просмотр данных.

Диаграмма

Представление данных модели данных

В представлении данных модели данных каждая таблица данных находится на отдельной вкладке. Строки таблицы данных являются записями, а столбцы представляют собой поля. Вкладки содержат имена таблиц, а заголовки столбцов являются полями в этой таблице. Вы можете выполнять вычисления в представлении данных с использованием языка выражений анализа данных (DAX).

Представление схемы модели данных

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

Отношения в модели данных

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

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

Вы также можете явно создавать отношения в любом из двух представлений —

• Просмотр данных — Использование диалогового окна «Создать связь».

• Представление схемы — нажав и перетащив, чтобы соединить две таблицы.

Просмотр данных — Использование диалогового окна «Создать связь».

Представление схемы — нажав и перетащив, чтобы соединить две таблицы.

Диалоговое окно «Создать связь»

В отношениях участвуют четыре объекта —

• Таблица — таблица данных, с которой начинается связь.

• Столбец — поле в таблице, которое также присутствует в соответствующей таблице.

• Связанная таблица — таблица данных, где заканчивается связь.

• Связанный столбец — поле в связанной таблице, такое же, как поле, представленное столбцом в таблице. Обратите внимание, что значения связанных столбцов должны быть уникальными.

Таблица — таблица данных, с которой начинается связь.

Столбец — поле в таблице, которое также присутствует в соответствующей таблице.

Связанная таблица — таблица данных, где заканчивается связь.

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

В виде диаграммы вы можете создать взаимосвязь, щелкнув поле в таблице и перетащив в связанную таблицу.

Подробнее об отношениях вы узнаете в главе «Управление таблицами данных и отношениями с Power Pivot».

Excel Power Pivot — управление моделью данных

Основное использование Power Pivot — это его способность управлять таблицами данных и связями между ними, чтобы облегчить анализ данных из нескольких таблиц. Вы можете добавить таблицу Excel в модель данных во время создания сводной таблицы или непосредственно из ленты PowerPivot.

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

Добавление таблиц Excel в модель данных с помощью сводной таблицы

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

Предположим, у вас есть две таблицы в вашей книге —

• Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.

• Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.

Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Вы можете суммировать продажи в зависимости от продавца, как указано ниже.

• Нажмите на таблицу — Продажа.

• Нажмите вкладку INSERT на ленте.

• Выберите Сводная таблица в группе Таблицы.

Нажмите на таблицу — Продажа.

Нажмите вкладку INSERT на ленте.

Выберите Сводная таблица в группе Таблицы.

Будет создана пустая сводная таблица с полями из таблицы продаж — Регион, Месяц и Сумма заказа. Как вы можете заметить, под списком полей сводной таблицы есть команда MORE TABLES .

• Нажмите на БОЛЬШЕ СТОЛОВ.

Нажмите на БОЛЬШЕ СТОЛОВ.

Откроется окно сообщения Создать новую сводную таблицу. Отображаемое сообщение: чтобы использовать несколько таблиц в анализе, необходимо создать новую сводную таблицу с помощью модели данных. Нажмите Да

Новая сводная таблица будет создана, как показано ниже —

Под полями сводной таблицы вы можете видеть две вкладки — ACTIVE и ALL .

• Нажмите вкладку ВСЕ.

• Две таблицы — Sales и Salesperson с соответствующими полями отображаются в списке полей сводной таблицы.

• Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.

• Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.

• Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.

Нажмите вкладку ВСЕ.

Две таблицы — Sales и Salesperson с соответствующими полями отображаются в списке полей сводной таблицы.

Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.

Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.

Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.

Сводная таблица создана. В полях сводной таблицы появляется сообщение — могут потребоваться взаимосвязи между таблицами .

Нажмите кнопку СОЗДАТЬ рядом с сообщением. Откроется диалоговое окно « Создать связь ».

• Под таблицей выберите Продажи.

• Под полем Column (Foreign) выберите Region.

• Под связанной таблицей выберите продавца.

• В поле « Связанный столбец (основной)» выберите «Регион».

• Нажмите ОК.

Под таблицей выберите Продажи.

Под полем Column (Foreign) выберите Region.

Под связанной таблицей выберите продавца.

В поле « Связанный столбец (основной)» выберите «Регион».

Нажмите ОК.

Ваша сводная таблица из двух таблиц на двух листах готова.

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

• Нажмите вкладку POWERPIVOT на ленте.

• Нажмите « Управление» в группе «Модель данных». Появится представление данных Power Pivot.

Нажмите вкладку POWERPIVOT на ленте.

Нажмите « Управление» в группе «Модель данных». Появится представление данных Power Pivot.

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

Добавление таблиц Excel из другой книги в модель данных

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

Вы можете добавить таблицу Excel из другой книги в модель данных следующим образом:

• Нажмите на таблицу продаж.

• Нажмите вкладку INSERT.

• Нажмите Сводная таблица в группе Таблицы. Откроется диалоговое окно « Создание сводной таблицы ».

Нажмите на таблицу продаж.

Нажмите вкладку INSERT.

Нажмите Сводная таблица в группе Таблицы. Откроется диалоговое окно « Создание сводной таблицы ».

• В поле Таблица / Диапазон введите Продажи.

• Нажмите на новый лист.

• Установите флажок Добавить эти данные в модель данных.

• Нажмите ОК.

В поле Таблица / Диапазон введите Продажи.

Нажмите на новый лист.

Установите флажок Добавить эти данные в модель данных.

Нажмите ОК.

Вы получите пустую сводную таблицу на новом листе только с полями, соответствующими таблице продаж.

Вы добавили данные таблицы продаж в модель данных. Затем вы должны получить данные таблицы Salesperson также в Data Model следующим образом:

• Нажмите на лист, содержащий таблицу продаж.

• Нажмите вкладку ДАННЫЕ на ленте.

• Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».

• Нажмите на вкладку Таблицы.

Нажмите на лист, содержащий таблицу продаж.

Нажмите вкладку ДАННЫЕ на ленте.

Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».

Нажмите на вкладку Таблицы.

Под этой моделью данных рабочей книги отображается 1 таблица (это таблица продаж, которую вы добавили ранее). Вы также найдете две рабочие книги с таблицами в них.

• Нажмите «Продавец» в разделе «Salesperson.xlsx».

• Нажмите Открыть. Откроется диалоговое окно « Импорт данных ».

• Нажмите на отчет сводной таблицы.

• Нажмите на новый лист.

Нажмите «Продавец» в разделе «Salesperson.xlsx».

Нажмите Открыть. Откроется диалоговое окно « Импорт данных ».

Нажмите на отчет сводной таблицы.

Нажмите на новый лист.

Вы можете видеть, что флажок — Добавить эти данные в модель данных отмечен и неактивен. Нажмите ОК.

Сводная таблица будет создана.

Как вы можете видеть, две таблицы находятся в модели данных. Возможно, вам придется создать связь между двумя таблицами, как в предыдущем разделе.

Добавление таблиц Excel в модель данных с ленты PowerPivot

Еще один способ добавления таблиц Excel в модель данных — сделать это с ленты PowerPivot .

Предположим, у вас есть две таблицы в вашей книге —

• Один, содержащий данные о продавцах и регионах, которые они представляют, в таблице — продавец.

• Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Один, содержащий данные о продавцах и регионах, которые они представляют, в таблице — продавец.

Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Вы можете добавить эти таблицы Excel в модель данных, прежде чем выполнять какой-либо анализ.

• Нажмите на таблицу Excel — Продажи.

• Нажмите вкладку POWERPIVOT на ленте.

• Нажмите Добавить в модель данных в группе Таблицы.

Нажмите на таблицу Excel — Продажи.

Нажмите вкладку POWERPIVOT на ленте.

Нажмите Добавить в модель данных в группе Таблицы.

Появится окно Power Pivot с добавленной в него таблицей данных Salesperson. Далее на ленте в окне Power Pivot появляется вкладка — Связанная таблица.

• Нажмите на вкладку Связанная таблица на ленте.

• Нажмите на таблицу Excel: продавец.

Нажмите на вкладку Связанная таблица на ленте.

Нажмите на таблицу Excel: продавец.

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

Нажмите Перейти к таблице Excel .

Откроется окно Excel с рабочим листом, содержащим таблицу Salesperson.

• Нажмите вкладку листа продаж.

• Нажмите на таблицу продаж.

• Нажмите Добавить в модель данных в группе Таблицы на ленте.

Нажмите вкладку листа продаж.

Нажмите на таблицу продаж.

Нажмите Добавить в модель данных в группе Таблицы на ленте.

Таблица продаж Excel также добавлена ​​в модель данных.

Если вы хотите провести анализ на основе этих двух таблиц, как вам известно, вам нужно создать связь между этими двумя таблицами данных. В Power Pivot вы можете сделать это двумя способами:

• Из представления данных

• Из представления схемы

Из представления данных

Из представления схемы

Создание отношений из представления данных

Как вы знаете, в Data View вы можете просматривать таблицы данных с записями в виде строк и полей в виде столбцов.

• Нажмите на вкладку «Дизайн» в окне Power Pivot.

• Нажмите «Создать отношение» в группе «Отношения». Откроется диалоговое окно « Создать связь ».

Нажмите на вкладку «Дизайн» в окне Power Pivot.

Нажмите «Создать отношение» в группе «Отношения». Откроется диалоговое окно « Создать связь ».

• Нажмите на Продажи в поле Таблица. Это таблица, с которой начинаются отношения. Как вам известно, столбец должен быть полем, присутствующим в связанной таблице Salesperson, содержащей уникальные значения.

• Нажмите на регион в поле столбца.

• Нажмите на продавца в поле Связанная связанная таблица.

Нажмите на Продажи в поле Таблица. Это таблица, с которой начинаются отношения. Как вам известно, столбец должен быть полем, присутствующим в связанной таблице Salesperson, содержащей уникальные значения.

Нажмите на регион в поле столбца.

Нажмите на продавца в поле Связанная связанная таблица.

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

Нажмите кнопку Создать. Отношения созданы.

Создание отношений из представления схемы

Создание отношений из представления диаграммы относительно проще. Следуйте данным шагам.

• Перейдите на вкладку «Главная» в окне Power Pivot.

• Нажмите «Вид диаграммы» в группе «Вид».

Перейдите на вкладку «Главная» в окне Power Pivot.

Нажмите «Вид диаграммы» в группе «Вид».

Представление схемы модели данных появится в окне Power Pivot.

• Нажмите на регион в таблице продаж. Регион в таблице продаж выделен.

• Перетащите в Регион в таблице Salesperson. Регион в таблице продавца также выделен. Линия появляется в направлении, которое вы перетащили.

• Из таблицы Sales в таблицу Salesperson появляется строка с указанием взаимосвязи.

Нажмите на регион в таблице продаж. Регион в таблице продаж выделен.

Перетащите в Регион в таблице Salesperson. Регион в таблице продавца также выделен. Линия появляется в направлении, которое вы перетащили.

Из таблицы Sales в таблицу Salesperson появляется строка с указанием взаимосвязи.

Как вы можете видеть, из таблицы Sales в таблицу Salesperson появляется линия, указывающая взаимосвязь и направление.

Если вы хотите знать поле, которое является частью отношения, щелкните по строке отношения. Строка и поле в обеих таблицах выделены.

Управление отношениями

Вы можете редактировать или удалять существующие отношения в модели данных.

• Перейдите на вкладку «Дизайн» в окне Power Pivot.

• Нажмите «Управление отношениями» в группе «Отношения». Откроется диалоговое окно «Управление отношениями».

Перейдите на вкладку «Дизайн» в окне Power Pivot.

Нажмите «Управление отношениями» в группе «Отношения». Откроется диалоговое окно «Управление отношениями».

Будут отображены все отношения, существующие в модели данных.

Редактировать отношения

• Нажмите на Отношения.

• Нажмите кнопку Изменить . Откроется диалоговое окно « Редактировать отношения ».

Нажмите на Отношения.

Нажмите кнопку Изменить . Откроется диалоговое окно « Редактировать отношения ».

• Сделайте необходимые изменения в отношениях.

• Нажмите ОК. Изменения отражаются в отношениях.

Сделайте необходимые изменения в отношениях.

Нажмите ОК. Изменения отражаются в отношениях.

Чтобы удалить отношения

• Нажмите на Отношения.

• Нажмите на кнопку Удалить. Появится предупреждающее сообщение о том, как таблицы, на которые повлияло удаление отношения, повлияют на отчеты.

• Нажмите OK, если вы уверены, что хотите удалить. Выбранное отношение будет удалено.

Нажмите на Отношения.

Нажмите на кнопку Удалить. Появится предупреждающее сообщение о том, как таблицы, на которые повлияло удаление отношения, повлияют на отчеты.

Нажмите OK, если вы уверены, что хотите удалить. Выбранное отношение будет удалено.

Обновление данных Power Pivot

Предположим, вы изменили данные в таблице Excel. Вы можете добавить / изменить / удалить данные в таблице Excel.

Чтобы обновить данные PowerPivot, выполните следующие действия:

• Перейдите на вкладку «Связанная таблица» в окне Power Pivot.

• Нажмите Обновить все.

Перейдите на вкладку «Связанная таблица» в окне Power Pivot.

Нажмите Обновить все.

Таблица данных обновляется с учетом изменений, внесенных в таблицу Excel.

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

Сводная таблица Excel Power — создание

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

Power PivotTable по своей структуре напоминает сводную таблицу со следующими отличиями:

• Сводная таблица основана на таблицах Excel, тогда как Power PivotTable основана на таблицах данных, которые являются частью модели данных.

• Сводная таблица основана на одной таблице Excel или диапазоне данных, тогда как Power PivotTable может основываться на нескольких таблицах данных, если они добавлены в модель данных.

• Сводная таблица создается из окна Excel, тогда как Power PivotTable создается из окна PowerPivot.

Сводная таблица основана на таблицах Excel, тогда как Power PivotTable основана на таблицах данных, которые являются частью модели данных.

Сводная таблица основана на одной таблице Excel или диапазоне данных, тогда как Power PivotTable может основываться на нескольких таблицах данных, если они добавлены в модель данных.

Сводная таблица создается из окна Excel, тогда как Power PivotTable создается из окна PowerPivot.

Создание Power PivotTable

Предположим, у вас есть две таблицы данных — продавец и продажи в модели данных. Чтобы создать таблицу PowerPivot из этих двух таблиц данных, выполните следующие действия:

• Перейдите на вкладку «Главная» в окне «Лента в PowerPivot».

• Нажмите Сводная таблица на ленте.

• Выберите сводную таблицу из выпадающего списка.

Перейдите на вкладку «Главная» в окне «Лента в PowerPivot».

Нажмите Сводная таблица на ленте.

Выберите сводную таблицу из выпадающего списка.

Откроется диалоговое окно «Создать сводную таблицу». Как вы можете заметить, это простое диалоговое окно без каких-либо запросов к данным. Это связано с тем, что Power PivotTable всегда основана на модели данных, то есть на таблицах данных с определенными отношениями между ними.

Выберите «Новый лист» и нажмите «ОК».

Новый лист создается в окне Excel, и появляется пустая сводная таблица.

Как вы можете заметить, макет Power PivotTable похож на макет PivotTable. На ленте появляются СЛОВОЙ ИНСТРУМЕНТ , с вкладками АНАЛИЗ и ДИЗАЙН , идентичные Сводной таблице.

Список полей сводной таблицы появится в правой части листа. Здесь вы найдете некоторые отличия от сводной таблицы.

Поля Power PivotTable

Список полей сводной таблицы имеет две вкладки — ACTIVE и ALL, которые отображаются под заголовком и над списком полей. Вкладка ВСЕ выделена.

Обратите внимание, что на вкладке « ВСЕ » отображаются все таблицы данных на вкладке «Модель данных», а на вкладке «АКТИВНЫЙ» отображаются все таблицы данных, выбранные для Power PivotTable. Поскольку сводная таблица Power пуста, это означает, что таблица данных еще не выбрана; следовательно, по умолчанию выбрана вкладка ВСЕ и отображаются две таблицы, которые в настоящее время находятся в модели данных. На этом этапе, если вы нажмете вкладку ACTIVE , список полей будет пустым.

• Нажмите на имена таблиц в списке Поля сводной таблицы в разделе ВСЕ. Появятся соответствующие поля с флажками.

• Каждое имя таблицы будет иметь символ с левой стороны.

• Если вы поместите курсор на этот символ, отобразятся Источник данных и Имя таблицы модели этой таблицы данных.

Нажмите на имена таблиц в списке Поля сводной таблицы в разделе ВСЕ. Появятся соответствующие поля с флажками.

Каждое имя таблицы будет иметь символ с левой стороны.

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

• Перетащите Продавца из таблицы Продавца в область ROWS.

• Нажмите вкладку ACTIVE .

Перетащите Продавца из таблицы Продавца в область ROWS.

Нажмите вкладку ACTIVE .

Как вы можете заметить, поле «Продавец» отображается в сводной таблице, а таблица «Продавец» отображается на вкладке « АКТИВНО », как и ожидалось.

• Нажмите вкладку ВСЕ .

• Нажмите на месяц и сумму заказа в таблице продаж.

Нажмите вкладку ВСЕ .

Нажмите на месяц и сумму заказа в таблице продаж.

Снова, нажмите вкладку ACTIVE. Обе таблицы — Sales и Salesperson отображаются на вкладке ACTIVE .

• Перетащите Месяц в область КОЛОННЫ.

• Перетащите область в область ФИЛЬТРЫ.

Перетащите Месяц в область КОЛОННЫ.

Перетащите область в область ФИЛЬТРЫ.

• Нажмите на стрелку рядом с ALL в окне фильтра Region.

• Нажмите «Выбрать несколько элементов».

• Выберите Север и Юг и нажмите ОК.

Нажмите на стрелку рядом с ALL в окне фильтра Region.

Нажмите «Выбрать несколько элементов».

Выберите Север и Юг и нажмите ОК.

Сортируйте метки столбцов в порядке возрастания.

Power PivotTable можно изменять динамически исследовать и сообщать данные.

Excel Power Pivot — Основы DAX

Язык DAX (Data Analysis eXpression) является языком Power Pivot. DAX используется Power Pivot для моделирования данных, и его удобно использовать для самообслуживания BI. DAX основан на таблицах данных и столбцах в таблицах данных. Обратите внимание, что он не основан на отдельных ячейках таблицы, как в случае с формулами и функциями в Excel.

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

Расчетный столбец

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

Расширение модели данных с использованием вычисляемых столбцов

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

Создайте сводную таблицу Power с этими данными.

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

Однако если вам нужно рассчитать валовую прибыль, вам нужно иметь еще два столбца в каждой из таблиц данных регионов — Общая цена продукта и Валовая прибыль. Это связано с тем, что для сводной таблицы сводных таблиц требуются столбцы в таблицах данных.

Как вы знаете, общая цена продукта — это цена продукта * Количество единиц, а валовая прибыль — общая сумма — общая цена продукта.

Вам нужно использовать выражения DAX, чтобы добавить вычисляемые столбцы следующим образом:

• Выберите вкладку East_Sales в представлении данных окна Power Pivot, чтобы просмотреть таблицу данных East_Sales.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

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

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

Столбец справа с заголовком — Добавить столбец выделен.

Тип = [Цена продукта] * [№ из единиц] в строке формул и нажмите Enter .

Новый столбец с заголовком CalculatedColumn1 вставляется со значениями, рассчитанными по введенной вами формуле.

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок как TotalProductPrice .

Дважды щелкните заголовок нового вычисляемого столбца.

Переименуйте заголовок как TotalProductPrice .

Добавьте еще один рассчитанный столбец для валовой прибыли следующим образом:

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

• Столбец справа с заголовком — Добавить столбец выделен.

• Тип = [TotalSalesAmount] — [TotaProductPrice] в строке формул.

• Нажмите Ввод.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

Столбец справа с заголовком — Добавить столбец выделен.

Тип = [TotalSalesAmount] — [TotaProductPrice] в строке формул.

Нажмите Ввод.

Новый столбец с заголовком CalculatedColumn1 вставляется со значениями, рассчитанными по введенной вами формуле.

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок как Валовая прибыль.

Дважды щелкните заголовок нового вычисляемого столбца.

Переименуйте заголовок как Валовая прибыль.

Добавьте вычисляемые столбцы в таблицу данных North_Sales аналогичным образом. Объединяя все шаги, выполните следующие действия:

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить. Столбец справа с заголовком — Добавить столбец выделен.

• Тип = [Цена продукта] * [№ из единиц] в строке формул и нажмите Enter.

• Новый столбец с заголовком CalculatedColumn1 вставляется со значениями, рассчитанными по введенной вами формуле.

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок как TotalProductPrice .

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить. Столбец справа с заголовком — Добавить столбец выделен.

• Введите = [TotalSalesAmount] — [TotaProductPrice] в строке формул и нажмите Enter. Новый столбец с заголовком CalculatedColumn1 вставляется со значениями, рассчитанными по введенной вами формуле.

• Дважды щелкните заголовок нового вычисляемого столбца.

• Переименуйте заголовок как Валовая прибыль .

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить. Столбец справа с заголовком — Добавить столбец выделен.

Тип = [Цена продукта] * [№ из единиц] в строке формул и нажмите Enter.

Новый столбец с заголовком CalculatedColumn1 вставляется со значениями, рассчитанными по введенной вами формуле.

Дважды щелкните заголовок нового вычисляемого столбца.

Переименуйте заголовок как TotalProductPrice .

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить. Столбец справа с заголовком — Добавить столбец выделен.

Введите = [TotalSalesAmount] — [TotaProductPrice] в строке формул и нажмите Enter. Новый столбец с заголовком CalculatedColumn1 вставляется со значениями, рассчитанными по введенной вами формуле.

Дважды щелкните заголовок нового вычисляемого столбца.

Переименуйте заголовок как Валовая прибыль .

Повторите приведенные выше шаги для таблицы данных South Sales и таблицы данных West Sales.

У вас есть необходимые столбцы для суммирования валовой прибыли. Теперь создайте сводную таблицу Power.

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

Вы можете суммировать это по регионам для продуктов, как указано ниже также —

Расчетное поле

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

• Нажмите под столбцом «Валовая прибыль» в таблице East_Sales в окне Power Pivot.

• Введите EastProfit: = SUM ([Валовая прибыль]) / сумма ([TotalSalesAmount]) в строке формул.

• Нажмите Ввод.

Нажмите под столбцом «Валовая прибыль» в таблице East_Sales в окне Power Pivot.

Введите EastProfit: = SUM ([Валовая прибыль]) / сумма ([TotalSalesAmount]) в строке формул.

Нажмите Ввод.

Рассчитанное поле EastProfit вставляется под столбцом «Валовая прибыль».

• Щелкните правой кнопкой мыши по рассчитанному полю — EastProfit.

• Выберите Формат из выпадающего списка.

Щелкните правой кнопкой мыши по рассчитанному полю — EastProfit.

Выберите Формат из выпадающего списка.

Откроется диалоговое окно «Форматирование».

• Выберите номер под категорией.

• В поле «Формат» выберите «Процент» и нажмите «ОК».

Выберите номер под категорией.

В поле «Формат» выберите «Процент» и нажмите «ОК».

Рассчитанное поле EastProfit форматируется в процентах.

Повторите шаги, чтобы вставить следующие вычисляемые поля —

• NorthProfit в таблице данных North_Sales.

• SouthProfit в таблице данных South_Sales.

• WestProfit в таблице данных West_Sales.

NorthProfit в таблице данных North_Sales.

SouthProfit в таблице данных South_Sales.

WestProfit в таблице данных West_Sales.

Примечание. Вы не можете определить более одного вычисляемого поля с данным именем.

Нажмите на Power PivotTable. Вы можете видеть, что вычисленные поля появляются в таблицах.

• Выберите поля — EastProfit, NorthProfit, SouthProfit и WestProfit из таблиц в списке Поля сводной таблицы.

• Расположите поля так, чтобы валовая прибыль и процентная прибыль отображались вместе. Сводная таблица Power выглядит следующим образом —

Выберите поля — EastProfit, NorthProfit, SouthProfit и WestProfit из таблиц в списке Поля сводной таблицы.

Расположите поля так, чтобы валовая прибыль и процентная прибыль отображались вместе. Сводная таблица Power выглядит следующим образом —

Примечание . Поля вычисления в предыдущих версиях Excel назывались « Меры» .

Excel Power Pivot — изучение данных

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

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

Загрузка данных из базы данных Access

Чтобы загрузить данные из базы данных Access, выполните следующие действия:

• Откройте новую пустую книгу в Excel.

• Нажмите «Управление» в группе «Модель данных».

• Нажмите вкладку POWERPIVOT на ленте.

Откройте новую пустую книгу в Excel.

Нажмите «Управление» в группе «Модель данных».

Нажмите вкладку POWERPIVOT на ленте.

Откроется окно Power Pivot.

• Перейдите на вкладку «Главная» в окне Power Pivot.

• Нажмите Из базы данных в группе Получить внешние данные.

• Выберите From Access из выпадающего списка.

Перейдите на вкладку «Главная» в окне Power Pivot.

Нажмите Из базы данных в группе Получить внешние данные.

Выберите From Access из выпадающего списка.

Появится мастер импорта таблиц.

• Укажите имя дружественного соединения .

• Найдите файл базы данных Access, Events.accdb, файл базы данных событий.

• Нажмите на кнопку Далее>.

Укажите имя дружественного соединения .

Найдите файл базы данных Access, Events.accdb, файл базы данных событий.

Нажмите на кнопку Далее>.

В мастере импорта таблиц отображаются параметры для выбора способа импорта данных.

Нажмите « Выбрать» из списка таблиц и представлений, чтобы выбрать данные для импорта, и нажмите « Далее» .

Мастер импорта таблиц отображает все таблицы в базе данных Access, которые вы выбрали. Установите все флажки, чтобы выбрать все таблицы, и нажмите «Готово».

Мастер импорта таблиц отображает — Импорт и показывает состояние импорта. Это может занять несколько минут, и вы можете остановить импорт, нажав кнопку « Остановить импорт» .

После завершения импорта данных в мастере импорта таблиц отображается « Успешно» и отображаются результаты импорта. Нажмите Закрыть .

Power Pivot отображает все импортированные таблицы на разных вкладках в представлении данных.

Нажмите на представление схемы.

Вы можете заметить, что между таблицами существует связь — Дисциплины и Медали . Это связано с тем, что при импорте данных из реляционной базы данных, такой как Access, отношения, существующие в базе данных, также импортируются в модель данных в Power Pivot.

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

Создайте сводную таблицу с таблицами, которые вы импортировали в предыдущем разделе, следующим образом:

• Нажмите Сводная таблица на ленте.

• Выберите сводную таблицу из выпадающего списка.

• Выберите «Новый лист» в открывшемся диалоговом окне «Создать сводную таблицу» и нажмите кнопку «ОК».

Нажмите Сводная таблица на ленте.

Выберите сводную таблицу из выпадающего списка.

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

Пустая сводная таблица создается на новом листе в окне Excel.

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

• Перетащите поле NOC_CountryRegion в таблице медалей в область COLUMNS.

• Перетащите Discipline из таблицы Disciplines в область ROWS.

• Фильтр Дисциплина отображает только пять видов спорта: стрельба из лука, дайвинг, фехтование, фигурное катание и конькобежный спорт. Это можно сделать либо в области полей сводной таблицы, либо из фильтра меток строк в самой сводной таблице.

• Перетащите медаль из таблицы медалей в область ЗНАЧЕНИЯ.

• Снова выберите медаль из таблицы медалей и перетащите ее в область ФИЛЬТРЫ.

Перетащите поле NOC_CountryRegion в таблице медалей в область COLUMNS.

Перетащите Discipline из таблицы Disciplines в область ROWS.

Фильтр Дисциплина отображает только пять видов спорта: стрельба из лука, дайвинг, фехтование, фигурное катание и конькобежный спорт. Это можно сделать либо в области полей сводной таблицы, либо из фильтра меток строк в самой сводной таблице.

Перетащите медаль из таблицы медалей в область ЗНАЧЕНИЯ.

Снова выберите медаль из таблицы медалей и перетащите ее в область ФИЛЬТРЫ.

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

Изучение данных с помощью сводной таблицы

Возможно, вы захотите отобразить только эти значения с Medal Count> 80. Для этого выполните следующие шаги —

• Нажмите на стрелку справа от метки столбца.

• Выберите Value Filters из выпадающего списка.

• Выберите Больше чем… . из второго выпадающего списка.

• Нажмите ОК.

Нажмите на стрелку справа от метки столбца.

Выберите Value Filters из выпадающего списка.

Выберите Больше чем… . из второго выпадающего списка.

Нажмите ОК.

Откроется диалоговое окно « Фильтр значений ». Введите 80 в крайнем правом поле и нажмите ОК.

Сводная таблица отображает только те регионы с общим количеством медалей более 80.

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

Обобщение данных из разных источников в Power Pivot

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

• Создайте новый лист в книге.

• Создать таблицу в Excel — Спорт.

Создайте новый лист в книге.

Создать таблицу в Excel — Спорт.

Добавьте спортивный стол в модель данных.

Создайте связь между таблицами Disciplines и Sports с полем SportID .

Добавьте поле Спорт в сводную таблицу.

Перемешайте поля — Дисциплина и Спорт в области ROWS.

Расширение исследования данных

Вы можете получить таблицу « События» и в дальнейшем исследовании данных.

Создайте связь между таблицами « События» и « Медали» с полем DisciplineEvent .

Добавьте таблицу Hosts в рабочую книгу и модель данных.

Расширение модели данных с использованием вычисляемых столбцов

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

• Перейдите к таблице Hosts в представлении данных окна PowerPivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

Перейдите к таблице Hosts в представлении данных окна PowerPivot.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

Крайний правый столбец с заголовком Добавить столбец выделен.

• Введите следующую формулу DAX в строке формул = CONCATENATE ([Edition], [Season])

• Нажмите Ввод.

Введите следующую формулу DAX в строке формул = CONCATENATE ([Edition], [Season])

Нажмите Ввод.

Создается новый столбец с заголовком CalculatedColumn1, и столбец заполняется значениями, полученными из приведенной выше формулы DAX.

Щелкните правой кнопкой мыши новый столбец и выберите «Переименовать столбец» в раскрывающемся списке.

Введите EditionID в заголовке нового столбца.

Как видите, столбец EditionID имеет уникальные значения в таблице Hosts.

Создание отношения с использованием вычисляемых столбцов

Если вам необходимо создать связь между таблицей Hosts и таблицей Медали , столбец EditionID также должен существовать в таблице Медали. Создайте вычисляемый столбец в таблице медалей следующим образом:

• Нажмите на таблицу медалей в представлении данных Power Pivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

Нажмите на таблицу медалей в представлении данных Power Pivot.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

Введите формулу DAX в формуле bar = YEAR ([EDITION]) и нажмите Enter.

Переименуйте новый столбец, который создается как Год, и нажмите Добавить .

• Введите следующую формулу DAX в строке формул = CONCATENATE ([Год], [Сезон])

• Переименуйте новый столбец, созданный как EditionID .

Введите следующую формулу DAX в строке формул = CONCATENATE ([Год], [Сезон])

Переименуйте новый столбец, созданный как EditionID .

Как вы можете заметить, столбец EditionID в таблице медалей имеет значения, идентичные столбцу EditionID в таблице Hosts. Таким образом, вы можете создать связь между таблицами — Медали и Спорт с полем EditionID.

• Переключитесь на представление схемы в окне PowerPivot.

• Создайте связь между таблицами-медалями и хостами с полем, полученным из вычисляемого столбца, т.е. EditionID .

Переключитесь на представление схемы в окне PowerPivot.

Создайте связь между таблицами-медалями и хостами с полем, полученным из вычисляемого столбца, т.е. EditionID .

Теперь вы можете добавлять поля из таблицы Hosts в Power PivotTable.

Excel Power Pivot — Плоский

Когда данные имеют много уровней, иногда становится сложно читать отчет сводной таблицы.

Например, рассмотрим следующую модель данных.

Мы создадим Power PivotTable и Power Flatlined PivotTable, чтобы получить представление о макетах.

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

Вы можете создать Power PivotTable следующим образом:

• Перейдите на вкладку «Главная» на ленте в окне PowerPivot.

• Нажмите Сводная таблица.

• Выберите сводную таблицу из выпадающего списка.

Перейдите на вкладку «Главная» на ленте в окне PowerPivot.

Нажмите Сводная таблица.

Выберите сводную таблицу из выпадающего списка.

Будет создана пустая сводная таблица.

• Перетащите поля — Продавец, Регион и Продукт из списка Поля сводной таблицы в область ROWS.

• Перетащите поле — TotalSalesAmount из таблиц — Восток, Север, Юг и Запад в область ∑ VALUES.

Перетащите поля — Продавец, Регион и Продукт из списка Поля сводной таблицы в область ROWS.

Перетащите поле — TotalSalesAmount из таблиц — Восток, Север, Юг и Запад в область ∑ VALUES.

Как видите, читать такой отчет немного громоздко. Чем больше будет количество записей, тем сложнее будет.

Power Pivot предоставляет решение для лучшего представления данных с помощью Flatlined PivotTable.

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

Вы можете создать Power Flatlined PivotTable следующим образом:

• Перейдите на вкладку «Главная» на ленте в окне PowerPivot.

• Нажмите Сводная таблица.

• Выберите Сводная сводная таблица из выпадающего списка.

Перейдите на вкладку «Главная» на ленте в окне PowerPivot.

Нажмите Сводная таблица.

Выберите Сводная сводная таблица из выпадающего списка.

Откроется диалоговое окно « Создать сводную сводную таблицу». Выберите «Новый лист» и нажмите «ОК».

Как вы можете видеть, данные сведены в эту сводную таблицу.

Примечание. В этом случае продавец, регион и продукт находятся в области ROWS, как и в предыдущем случае. Однако в макете сводной таблицы эти три поля отображаются в виде трех столбцов.

Изучение данных в сводной сводной таблице

Предположим, вы хотите обобщить данные о продажах продукта — Кондиционер. Вы можете сделать это простым способом с помощью Сводной сводной таблицы следующим образом:

• Нажмите на стрелку рядом с заголовком столбца — Продукт.

• Установите флажок «Кондиционер» и снимите флажки в других полях. Нажмите ОК.

Нажмите на стрелку рядом с заголовком столбца — Продукт.

Установите флажок «Кондиционер» и снимите флажки в других полях. Нажмите ОК.

Сводная сводная таблица фильтруется по данным о продажах кондиционера.

Вы можете сделать его более сглаженным, перетащив AL VALUES в область ROWS из области COLUMNS.

Переименуйте пользовательские имена значений суммирования в области AL VALUES, чтобы сделать их более значимыми следующим образом:

• Нажмите на значение суммирования, скажем, Sum of TotalSalesAmount для Востока.

• Выберите Value Field Settings из выпадающего списка.

• Измените пользовательское имя на East TotalSalesAmount.

• Повторите шаги для других трех значений суммирования.

Нажмите на значение суммирования, скажем, Sum of TotalSalesAmount для Востока.

Выберите Value Field Settings из выпадающего списка.

Измените пользовательское имя на East TotalSalesAmount.

Повторите шаги для других трех значений суммирования.

Вы также можете суммировать количество проданных единиц.

• Перетащите № единиц в область ∑ VALUES из каждой таблицы — East_Sales, North_Sales, South_Sales и West_Sales.

• Переименуйте значения в Восток Общее количество единиц, Север Общее количество единиц, Юг Общее количество единиц и Запад Общее количество единиц соответственно.

Перетащите № единиц в область ∑ VALUES из каждой таблицы — East_Sales, North_Sales, South_Sales и West_Sales.

Переименуйте значения в Восток Общее количество единиц, Север Общее количество единиц, Юг Общее количество единиц и Запад Общее количество единиц соответственно.

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

• Выберите строки с пустыми значениями.

• Щелкните правой кнопкой мыши и выберите «Скрыть» в раскрывающемся списке.

Выберите строки с пустыми значениями.

Щелкните правой кнопкой мыши и выберите «Скрыть» в раскрывающемся списке.

Все строки с пустыми значениями будут скрыты.

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

• Нажмите на заголовок столбца — Продавец.

• Нажмите вкладку ANALYZE на ленте.

• Нажмите Настройки поля. Откроется диалоговое окно «Настройки поля».

• Перейдите на вкладку «Макет и печать».

• Установите флажок — Повторять метки предметов .

• Нажмите ОК.

Нажмите на заголовок столбца — Продавец.

Нажмите вкладку ANALYZE на ленте.

Нажмите Настройки поля. Откроется диалоговое окно «Настройки поля».

Перейдите на вкладку «Макет и печать».

Установите флажок — Повторять метки предметов .

Нажмите ОК.

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

Перетащите поле Регионы из области.

В обратном порядке полей — продавец и продукт в области ROWS.

Вы получили краткий отчет, объединяющий данные из шести таблиц в Power Pivot.

Excel Power Pivot Charts — Создание

Сводная диаграмма, основанная на модели данных и созданная из окна Power Pivot, является Power PivotChart. Хотя он имеет некоторые функции, аналогичные Excel PivotChart, есть и другие функции, которые делают его более мощным.

В этой главе вы узнаете о Power PivotCharts. Отныне мы будем называть их PivotCharts для простоты.

Создание сводной диаграммы

Предположим, вы хотите создать сводную диаграмму на основе следующей модели данных.

• Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».

• Нажмите Сводная таблица.

• Выберите PivotChart из выпадающего списка.

Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».

Нажмите Сводная таблица.

Выберите PivotChart из выпадающего списка.

Откроется диалоговое окно « Создание сводной диаграммы ». Выберите «Новый лист» и нажмите «ОК».

Пустая сводная диаграмма создается на новом листе в окне Excel.

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

• Щелкните по таблице «Продавец» в списке «Поля сводной диаграммы».

• Перетащите поля — Продавец и Регион в область AXIS.

Щелкните по таблице «Продавец» в списке «Поля сводной диаграммы».

Перетащите поля — Продавец и Регион в область AXIS.

На сводной диаграмме отображаются две кнопки для двух выбранных полей. Это полевые кнопки Оси. Использование полевых кнопок предназначено для фильтрации данных, отображаемых в сводной диаграмме.

Перетащите TotalSalesAmount из каждой из четырех таблиц — East_Sales, North_Sales, South_Sales и West_Sales в область VALUES.

Следующее появляется на листе —

• В сводной диаграмме столбчатая диаграмма отображается по умолчанию.

• В области LEGEND добавлены ∑ VALUES.

• Значения отображаются в легенде в сводной диаграмме с заголовком «Значения».

• Кнопки поля значения отображаются на сводной диаграмме. Вы можете удалить легенду и кнопки полей значений для более аккуратного представления сводной диаграммы.

• Нажми на Кнопка в правом верхнем углу сводной диаграммы. Появится раскрывающийся список « Элементы диаграммы» .

В сводной диаграмме столбчатая диаграмма отображается по умолчанию.

В области LEGEND добавлены ∑ VALUES.

Значения отображаются в легенде в сводной диаграмме с заголовком «Значения».

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

Нажми на Кнопка в правом верхнем углу сводной диаграммы. Появится раскрывающийся список « Элементы диаграммы» .

Снимите флажок Легенда в списке элементов диаграммы. Легенда удалена из сводной диаграммы.

• Щелкните правой кнопкой мыши на кнопках поля значения.

• В раскрывающемся списке выберите «Скрыть кнопки полей значений на диаграмме».

Щелкните правой кнопкой мыши на кнопках поля значения.

В раскрывающемся списке выберите «Скрыть кнопки полей значений на диаграмме».

Кнопки поля значений на графике удалены.

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

Список полей сводной диаграммы

Как и в случае Power PivotTable, список полей Power PivotChart также содержит две вкладки — ACTIVE и ALL. На вкладке ВСЕ отображаются все таблицы данных в модели данных Power Pivot. На вкладке ACTIVE отображаются таблицы, из которых поля добавляются в сводную диаграмму.

Аналогично, области такие же, как и в случае с сводной диаграммой Excel. Там четыре области —

• ОСЬ (Категории)

• ЛЕГЕНДА (серия)

• ∑ ЦЕННОСТИ

• ФИЛЬТРЫ

ОСЬ (Категории)

ЛЕГЕНДА (серия)

∑ ЦЕННОСТИ

ФИЛЬТРЫ

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

Фильтры в сводной диаграмме

Вы можете использовать полевые кнопки Оси на графике для фильтрации отображаемых данных. Нажмите на стрелку на кнопке поля Ось — Регион.

Раскрывающийся список выглядит следующим образом:

Вы можете выбрать значения, которые вы хотите отобразить. Кроме того, вы можете поместить поле в область ФИЛЬТРЫ для фильтрации значений.

Перетащите поле Регион в область ФИЛЬТРЫ. Кнопка «Фильтр отчетов» — область отображается на сводной диаграмме.

Нажмите на стрелку на кнопке «Фильтр отчетов» — «Регион». Раскрывающийся список выглядит следующим образом:

Вы можете выбрать значения, которые вы хотите отобразить.

Слайсеры в сводной диаграмме

Использование срезов — это еще одна опция для фильтрации данных в Power PivotChart.

• Нажмите вкладку ANALYZE в разделе Инструменты PIVOTCHART на ленте.

• Нажмите Вставить слайсер в группе Фильтр. Откроется диалоговое окно « Вставить слайсер ».

Нажмите вкладку ANALYZE в разделе Инструменты PIVOTCHART на ленте.

Нажмите Вставить слайсер в группе Фильтр. Откроется диалоговое окно « Вставить слайсер ».

Все таблицы и соответствующие поля отображаются в диалоговом окне «Вставить слайсер».

Щелкните поле Region в таблице Salesperson в диалоговом окне Insert Slicer.

На листе появится слайсер для поля Region.

Как вы можете видеть, поле Region все еще существует как поле оси. Вы можете выбрать значения, которые вы хотите отобразить, нажав на кнопки слайсера.

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

Инструменты сводной диаграммы

В Power PivotChart инструменты PIVOTCHART имеют три вкладки на ленте по сравнению с двумя вкладками в сводной диаграмме Excel —

• ПРОАНАЛИЗИРУЙТЕ

• ДИЗАЙН

• ФОРМАТ

ПРОАНАЛИЗИРУЙТЕ

ДИЗАЙН

ФОРМАТ

Третья вкладка — FORMAT — это дополнительная вкладка в Power PivotChart.

Нажмите вкладку FORMAT на ленте.

Все параметры ленты на вкладке «ФОРМАТ» предназначены для добавления блеска в сводную диаграмму. Вы можете использовать эти опции разумно, не скучая.

Комбинации таблиц и диаграмм

Power Pivot предоставляет вам различные комбинации Power PivotTable и Power PivotChart для исследования данных, визуализации и создания отчетов. Вы изучили сводные таблицы и сводные диаграммы в предыдущих главах.

В этой главе вы узнаете, как создавать комбинации таблиц и диаграмм в окне Power Pivot.

Рассмотрим следующую модель данных в Power Pivot, которую мы будем использовать для иллюстраций:

Диаграмма и таблица (горизонтальные)

С помощью этой опции вы можете создать Power PivotChart и Power PivotTable, расположенные один за другим горизонтально в одном листе.

• Перейдите на вкладку «Главная» в окне Power Pivot.

• Нажмите Сводная таблица.

• Выберите Chart and Table (Horizontal) из выпадающего списка.

Перейдите на вкладку «Главная» в окне Power Pivot.

Нажмите Сводная таблица.

Выберите Chart and Table (Horizontal) из выпадающего списка.

Появится диалоговое окно «Создание сводной диаграммы и сводной таблицы (горизонтальная)». Выберите «Новый лист» и нажмите «ОК».

Пустая сводная диаграмма и пустая сводная таблица появятся на новом листе.

• Нажмите на сводную диаграмму.

• Перетащите NOC_CountryRegion из таблицы Медали в область AXIS.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Щелкните правой кнопкой мыши Chart и выберите Change Chart Type из выпадающего списка.

• Выберите диаграмму области.

• Измените название диаграммы на Общее количество медалей — в зависимости от страны .

Нажмите на сводную диаграмму.

Перетащите NOC_CountryRegion из таблицы Медали в область AXIS.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Щелкните правой кнопкой мыши Chart и выберите Change Chart Type из выпадающего списка.

Выберите диаграмму области.

Измените название диаграммы на Общее количество медалей — в зависимости от страны .

Как видите, в США больше всего медалей (> 4500).

• Нажмите на сводную таблицу.

• Перетащите Спорт со Спортивного стола в область ROWS.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Перетащите NOC_CountryRegion из таблицы Медали в область ФИЛЬТРЫ.

• Отфильтруйте поле NOC_CountryRegion по значению США.

Нажмите на сводную таблицу.

Перетащите Спорт со Спортивного стола в область ROWS.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Перетащите NOC_CountryRegion из таблицы Медали в область ФИЛЬТРЫ.

Отфильтруйте поле NOC_CountryRegion по значению США.

Измените формат отчета сводной таблицы на контурную форму.

• Отмените выбор Спорт со спортивного стола.

• Перетащите Gender из таблицы медалей в область ROWS.

Отмените выбор Спорт со спортивного стола.

Перетащите Gender из таблицы медалей в область ROWS.

Диаграмма и таблица (по вертикали)

С помощью этой опции вы можете создать Power PivotChart и Power PivotTable, расположенные один под другим вертикально в одном листе.

• Перейдите на вкладку «Главная» в окне Power Pivot.

• Нажмите Сводная таблица.

• Выберите Chart and Table (Vertical) из выпадающего списка.

Перейдите на вкладку «Главная» в окне Power Pivot.

Нажмите Сводная таблица.

Выберите Chart and Table (Vertical) из выпадающего списка.

Откроется диалоговое окно « Создание сводной диаграммы и сводной таблицы (по вертикали) ». Выберите «Новый лист» и нажмите «ОК».

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

• Нажмите на сводную диаграмму.

• Перетащите Год из таблицы медалей в область ОСей.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Щелкните правой кнопкой мыши Chart и выберите Change Chart Type из выпадающего списка.

• Выберите линейный график.

• Установите флажок Метки данных в элементах диаграммы.

• Измените название диаграммы на общее количество медалей — год мудрый .

Нажмите на сводную диаграмму.

Перетащите Год из таблицы медалей в область ОСей.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Щелкните правой кнопкой мыши Chart и выберите Change Chart Type из выпадающего списка.

Выберите линейный график.

Установите флажок Метки данных в элементах диаграммы.

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

Как вы можете видеть, 2008 год имеет наибольшее количество медалей (2450).

• Нажмите на сводную таблицу.

• Перетащите Спорт со Спортивного стола в область ROWS.

• Перетащите Gender из таблицы медалей в область ROWS.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Перетащите Год из таблицы медалей в область ФИЛЬТРЫ.

• Отфильтруйте поле Год до значения 2008.

• Измените формат отчета сводной таблицы на контурную форму.

• Отфильтруйте поле Спорт со значениями фильтров на значение, большее или равное 80.

Нажмите на сводную таблицу.

Перетащите Спорт со Спортивного стола в область ROWS.

Перетащите Gender из таблицы медалей в область ROWS.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Перетащите Год из таблицы медалей в область ФИЛЬТРЫ.

Отфильтруйте поле Год до значения 2008.

Измените формат отчета сводной таблицы на контурную форму.

Отфильтруйте поле Спорт со значениями фильтров на значение, большее или равное 80.

Excel Power Pivot — Иерархии

Иерархия в модели данных представляет собой список вложенных столбцов в таблице данных, которые рассматриваются как один элемент при использовании в сводной таблице Power. Например, если в таблице данных есть столбцы — Страна, Штат, Город, можно определить иерархию, чтобы объединить три столбца в одно поле.

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

Рассмотрим следующую модель данных для иллюстраций в этой главе.

Создание Иерархии

Вы можете создавать иерархии в виде диаграммы модели данных. Обратите внимание, что вы можете создать иерархию, основанную только на одной таблице данных.

• Нажмите на столбцы — Спорт, DisciplineID и Событие в таблице данных Medal в указанном порядке. Помните, что порядок важен для создания значимой иерархии.

• Щелкните правой кнопкой мыши по выбору.

• Выберите Создать иерархию из выпадающего списка.

Нажмите на столбцы — Спорт, DisciplineID и Событие в таблице данных Medal в указанном порядке. Помните, что порядок важен для создания значимой иерархии.

Щелкните правой кнопкой мыши по выбору.

Выберите Создать иерархию из выпадающего списка.

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

Переименование Иерархии

Чтобы переименовать поле иерархии, выполните следующие действия:

• Щелкните правой кнопкой мыши на Hierarchy1.

• Выберите Rename из выпадающего списка.

Щелкните правой кнопкой мыши на Hierarchy1.

Выберите Rename из выпадающего списка.

Введите EventHierarchy .

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

Вы можете создать Power PivotTable, используя иерархию, созданную в модели данных.

• Перейдите на вкладку «Сводная таблица» на ленте в окне Power Pivot.

• Нажмите Сводная таблица на ленте.

Перейдите на вкладку «Сводная таблица» на ленте в окне Power Pivot.

Нажмите Сводная таблица на ленте.

Откроется диалоговое окно « Создание сводной таблицы ». Выберите «Новый лист» и нажмите «ОК».

Пустая сводная таблица создается на новом листе.

В списке Поля сводной таблицы EventHierarchy отображается как поле в таблице медалей. Другие поля в таблице медалей свернуты и показаны как Дополнительные поля.

• Нажмите на стрелку перед EventHierarchy.

• Нажмите на стрелку перед больше полей.

Нажмите на стрелку перед EventHierarchy.

Нажмите на стрелку перед больше полей.

Поля в EventHierarchy будут отображаться. Все поля в таблице медалей будут отображаться в разделе «Дополнительные поля».

Как вы можете заметить, три поля, которые вы добавили в иерархию, также отображаются в разделе Дополнительные поля с флажками. Если вы не хотите, чтобы они отображались в списке полей сводной таблицы в разделе « Дополнительные поля» , необходимо скрыть столбцы в таблице данных — «Медали» в представлении данных в окне Power Pivot. Вы всегда можете показать их, когда захотите.

Добавьте поля в сводную таблицу следующим образом:

• Перетащите EventHierarchy в область ROWS.

• Перетащите Медаль в область ∑ VALUES.

Перетащите EventHierarchy в область ROWS.

Перетащите Медаль в область ∑ VALUES.

Значения поля «Спорт» отображаются в сводной таблице со знаком «+» перед ними. Количество медалей для каждого вида спорта отображается.

• Нажмите на знак + перед водными видами спорта. Будут отображены значения поля DisciplineID в разделе «Водные виды спорта».

• Нажмите на ребенка D22, который появляется. Будут отображены значения поля события под D22.

Нажмите на знак + перед водными видами спорта. Будут отображены значения поля DisciplineID в разделе «Водные виды спорта».

Нажмите на ребенка D22, который появляется. Будут отображены значения поля события под D22.

Как вы можете заметить, количество медалей указано для событий, которые суммируются на родительском уровне — DisciplineID , которые далее суммируются на родительском уровне — спорт.

Создание иерархии на основе нескольких таблиц

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

Как вы знаете, таблицы — медали и дисциплины связаны между собой. Вы можете добавить поле Discipline из таблицы Disciplines в таблицу медалей, создав столбец, используя связь с DAX.

• Нажмите на представление данных в окне Power Pivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

Нажмите на представление данных в окне Power Pivot.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

Столбец — Добавить столбец с правой стороны таблицы будет подсвечен.

Введите = RELATED (Disciplines [Discipline]) в строке формул. Новый столбец — CalculatedColumn1 создается со значениями в качестве значений поля Discipline в таблице Disciplines.

Переименуйте новый столбец, полученный таким образом в таблице медалей, в Discipline. Затем вы должны удалить DisciplineID из Иерархии и добавить Discipline, что вы узнаете в следующих разделах.

Удаление уровня ребенка из иерархии

Как вы можете заметить, иерархия видна только в виде диаграммы, а не в виде данных. Следовательно, вы можете редактировать иерархию только в виде диаграммы.

• Нажмите на представление схемы в окне Power Pivot.

• Щелкните правой кнопкой мыши DisciplineID в EventHierarchy.

• Выберите Удалить из иерархии в раскрывающемся списке.

Нажмите на представление схемы в окне Power Pivot.

Щелкните правой кнопкой мыши DisciplineID в EventHierarchy.

Выберите Удалить из иерархии в раскрывающемся списке.

Откроется диалоговое окно подтверждения. Нажмите Удалить из иерархии .

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

Далее вам нужно добавить поле Discipline в EventHierarchy.

Добавление дочернего уровня в иерархию

Вы можете добавить поле Discipline к существующей иерархии — EventHierarchy следующим образом —

• Нажмите на поле в таблице медалей.

• Перетащите его в поле «События» ниже в иерархии событий.

Нажмите на поле в таблице медалей.

Перетащите его в поле «События» ниже в иерархии событий.

Поле Discipline добавляется в EventHierarchy.

Как вы можете видеть, порядок полей в EventHierarchy имеет вид Sport – Event – ​​Discipline. Но, как вы знаете, это должен быть Sport-Discipline-Event. Следовательно, вам нужно изменить порядок полей.

Изменение порядка дочернего уровня в иерархии

Чтобы переместить поле «Дисциплина» в положение после поля «Спорт», выполните следующие действия:

• Щелкните правой кнопкой мыши на поле Discipline в EventHierarchy.

• Выберите Move Up из выпадающего списка.

Щелкните правой кнопкой мыши на поле Discipline в EventHierarchy.

Выберите Move Up из выпадающего списка.

Порядок полей меняется на Sport-Discipline-Event.

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

Чтобы просмотреть изменения, внесенные в EventHierarchy в сводную таблицу, вам не нужно создавать новую сводную таблицу. Вы можете просмотреть их в самой существующей сводной таблице.

Нажмите на лист с окном сводной таблицы в Excel.

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

Нажмите на знак + перед Aquatics в сводной таблице. Дочерние уровни отображаются как значения поля Discipline.

Скрытие и Отображение Иерархий

Вы можете скрыть иерархии и показывать их в любое время.

• Снимите флажок Иерархии в верхнем меню представления схемы, чтобы скрыть иерархии.

• Установите флажок Иерархии, чтобы показать иерархии.

Снимите флажок Иерархии в верхнем меню представления схемы, чтобы скрыть иерархии.

Установите флажок Иерархии, чтобы показать иерархии.

Создание иерархии другими способами

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

1. Нажмите кнопку «Создать иерархию» в верхнем правом углу таблицы данных «Медали» в виде диаграммы.

Новая иерархия создается в таблице без каких-либо полей в ней.

Перетащите поля Год и Сезон в указанном порядке в новую иерархию. Иерархия показывает дочерние уровни.

2. Другой способ создания той же иерархии заключается в следующем:

• Щелкните правой кнопкой мыши поле Год в таблице данных Медали в виде диаграммы.

• Выберите Создать иерархию из выпадающего списка.

Щелкните правой кнопкой мыши поле Год в таблице данных Медали в виде диаграммы.

Выберите Создать иерархию из выпадающего списка.

В таблице создается новая иерархия с дочерним полем Year.

Перетащите сезон поля в иерархию. Иерархия показывает дочерние уровни.

Удаление иерархии

Вы можете удалить иерархию из модели данных следующим образом:

• Щелкните правой кнопкой мыши по иерархии.

• Выберите Удалить из выпадающего списка.

Щелкните правой кнопкой мыши по иерархии.

Выберите Удалить из выпадающего списка.

Откроется диалоговое окно подтверждения . Нажмите Удалить из модели .

Иерархия удаляется.

Расчеты с использованием иерархии

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

• Щелкните правой кнопкой мыши по значению «Количество медалей» события.

• Выберите Value Field Settings из выпадающего списка.

Щелкните правой кнопкой мыши по значению «Количество медалей» события.

Выберите Value Field Settings из выпадающего списка.

Откроется диалоговое окно «Настройки поля значений».

• Перейдите на вкладку « Показать значения как ».

• Выберите% от общего количества родительских строк в списке и нажмите OK.

Перейдите на вкладку « Показать значения как ».

Выберите% от общего количества родительских строк в списке и нажмите OK.

Дочерние уровни отображаются в процентах от родительских итогов. Вы можете убедиться в этом, суммируя процентные значения дочернего уровня родителя. Сумма будет 100%.

Развертывание и сверление иерархии

Вы можете быстро переходить вверх и вниз по уровням в иерархии, используя инструмент Quick Explore.

• Щелкните значение поля «Событие» в сводной таблице.

• Нажмите на инструмент Quick Explore — который появляется в правом нижнем углу ячейки, содержащей выбранное значение.

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

Нажмите на инструмент Quick Explore — который появляется в правом нижнем углу ячейки, содержащей выбранное значение.

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

Нажмите Drill Up .

Данные сводной таблицы развертываются до дисциплины.

Нажмите на инструмент Quick Explore — который появляется в правом нижнем углу ячейки, содержащей значение.

Появится окно «Исследовать» с отображенными параметрами «Детализация вверх» и «Детализация» Это потому, что из Дисциплины вы можете перейти к Спорту или к Событию.

Таким образом, вы можете быстро перемещаться вверх и вниз по иерархии.

Excel Power Pivot — эстетические отчеты

Вы можете создавать эстетические отчеты по анализу данных с помощью Power Pivot Data, которая находится в модели данных.

Важные особенности —

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

• Вы можете вставить слайсеры для фильтрации данных в отчете.

• Вы можете использовать общий слайсер как для сводной диаграммы, так и для сводной таблицы, которые находятся в одном отчете.

• Как только ваш окончательный отчет будет готов, вы можете скрыть слайсеры с экрана.

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

Вы можете вставить слайсеры для фильтрации данных в отчете.

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

Как только ваш окончательный отчет будет готов, вы можете скрыть слайсеры с экрана.

В этой главе вы узнаете, как получать отчеты с помощью параметров, доступных в Power Pivot.

Рассмотрим следующую модель данных для иллюстраций в этой главе.

Отчеты на основе Power PivotChart

Создайте Power PivotChart следующим образом —

• Перейдите на вкладку «Главная» в окне «Лента в PowerPivot».

• Нажмите Сводная таблица.

• Выберите PivotChart из выпадающего списка.

• Нажмите « Новый лист» в диалоговом окне «Создание сводной диаграммы».

Перейдите на вкладку «Главная» в окне «Лента в PowerPivot».

Нажмите Сводная таблица.

Выберите PivotChart из выпадающего списка.

Нажмите « Новый лист» в диалоговом окне «Создание сводной диаграммы».

Пустая сводная диаграмма создается на новом листе в окне Excel.

• Перетащите Спорт со стола Медали в область Оси.

• Перетащите Медаль из Таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Перетащите Спорт со стола Медали в область Оси.

Перетащите Медаль из Таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Перейдите на вкладку «АНАЛИЗ» в «Сводные инструменты» на ленте.

• Нажмите Вставить слайсер в группе фильтров. Откроется диалоговое окно Inset Slicers.

• Щелкните поле NOC_CountryRegion в таблице медалей.

• Нажмите ОК.

Перейдите на вкладку «АНАЛИЗ» в «Сводные инструменты» на ленте.

Нажмите Вставить слайсер в группе фильтров. Откроется диалоговое окно Inset Slicers.

Щелкните поле NOC_CountryRegion в таблице медалей.

Нажмите ОК.

Появится слайсер NOC_CountryRegion.

• Выберите США.

• Перетащите Пол из таблицы медалей в область ГЕНДЕР.

Выберите США.

Перетащите Пол из таблицы медалей в область ГЕНДЕР.

• Щелкните правой кнопкой мыши на сводной диаграмме.

• Выберите Изменить тип диаграммы из раскрывающегося списка.

Щелкните правой кнопкой мыши на сводной диаграмме.

Выберите Изменить тип диаграммы из раскрывающегося списка.

Откроется диалоговое окно «Изменить тип диаграммы».

Нажмите на столбец с накоплением.

• Вставьте слайсер для спортивного поля.

• Перетащите дисциплину из таблицы дисциплин в область ОСей.

• Уберите поле Спорт из зоны ОСи.

• Выберите Водный спорт в слайсере — Спорт.

Вставьте слайсер для спортивного поля.

Перетащите дисциплину из таблицы дисциплин в область ОСей.

Уберите поле Спорт из зоны ОСи.

Выберите Водный спорт в слайсере — Спорт.

Макет отчета

Создайте сводную таблицу следующим образом —

• Нажмите на вкладку «Главная» в окне «Лента в PowerPivot».

• Нажмите на сводную таблицу.

• Нажмите на сводную таблицу в раскрывающемся списке. Откроется диалоговое окно «Создать сводную таблицу».

• Нажмите на новый рабочий лист и нажмите ОК. Пустая сводная таблица создается на новом листе.

• Перетащите NOC_CountryRegion из таблицы Медали в область AXIS.

• Перетащите Спорт со стола Медали в область КОЛОННЫ.

• Перетащите Discipline из таблицы Disciplines в область COLUMNS.

• Перетащите Медаль в область ∑ VALUES.

Нажмите на вкладку «Главная» в окне «Лента в PowerPivot».

Нажмите на сводную таблицу.

Нажмите на сводную таблицу в раскрывающемся списке. Откроется диалоговое окно «Создать сводную таблицу».

Нажмите на новый рабочий лист и нажмите ОК. Пустая сводная таблица создается на новом листе.

Перетащите NOC_CountryRegion из таблицы Медали в область AXIS.

Перетащите Спорт со стола Медали в область КОЛОННЫ.

Перетащите Discipline из таблицы Disciplines в область COLUMNS.

Перетащите Медаль в область ∑ VALUES.

Нажмите кнопку со стрелкой рядом с метками столбца и выберите «Водный спорт».

• Нажмите на кнопку со стрелкой рядом с метками строк.

• Выберите Value Filters из выпадающего списка.

• Выберите «Больше или равно» во втором раскрывающемся списке.

Нажмите на кнопку со стрелкой рядом с метками строк.

Выберите Value Filters из выпадающего списка.

Выберите «Больше или равно» во втором раскрывающемся списке.

Введите 80 в поле рядом с Count of Medal больше или равно в диалоговом окне Value Filter.

• Перейдите на вкладку «ДИЗАЙН» в «Сводные инструменты» на ленте.

• Нажмите на промежуточные итоги.

• Выберите Не показывать промежуточные итоги в раскрывающемся списке.

Перейдите на вкладку «ДИЗАЙН» в «Сводные инструменты» на ленте.

Нажмите на промежуточные итоги.

Выберите Не показывать промежуточные итоги в раскрывающемся списке.

Столбец промежуточных итогов — Всего водных видов спорта удаляется.

Нажмите «Макет отчета» и выберите « Показать в форме плана» в раскрывающемся списке.

Поставьте галочку в окошках.

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

Использование общего слайсера

Создайте сводную диаграмму и сводную таблицу рядом друг с другом.

• Перейдите на вкладку «Главная» на вкладке «Лента в PowerPivot».

• Нажмите Сводная таблица.

• Выберите Chart and Table (Horizontal) из выпадающего списка.

Перейдите на вкладку «Главная» на вкладке «Лента в PowerPivot».

Нажмите Сводная таблица.

Выберите Chart and Table (Horizontal) из выпадающего списка.

Откроется диалоговое окно «Создание сводной диаграммы и сводной таблицы (горизонтальная)».

Выберите «Новый лист» и нажмите «ОК». Пустая сводная диаграмма и пустая сводная таблица появляются рядом друг с другом на новом листе.

• Нажмите Сводная диаграмма.

• Перетащите дисциплину из таблицы дисциплин в область ОСей.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Нажмите Сводная таблица.

• Перетащите Discipline из таблицы Disciplines в область ROWS.

• Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Нажмите Сводная диаграмма.

Перетащите дисциплину из таблицы дисциплин в область ОСей.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

Нажмите Сводная таблица.

Перетащите Discipline из таблицы Disciplines в область ROWS.

Перетащите медаль из таблицы медалей в область ∑ ЗНАЧЕНИЯ.

• Перейдите на вкладку «АНАЛИЗ» в «Сводные инструменты» на ленте.

• Нажмите Вставить слайсер. Откроется диалоговое окно «Вставить слайсеры».

• Нажмите на NOC_CountryRegion и Спорт в таблице медалей.

• Нажмите ОК.

Перейдите на вкладку «АНАЛИЗ» в «Сводные инструменты» на ленте.

Нажмите Вставить слайсер. Откроется диалоговое окно «Вставить слайсеры».

Нажмите на NOC_CountryRegion и Спорт в таблице медалей.

Нажмите ОК.

Появятся два слайсера — NOC_CountryRegion и Sport. Расположите их по размеру, чтобы они правильно выровнялись рядом со сводной таблицей.

• Выберите США в слайсере NOC_CountryRegion.

• Выберите Водный спорт в Спортивном слайсере. Сводная таблица фильтруется по выбранным значениям.

Выберите США в слайсере NOC_CountryRegion.

Выберите Водный спорт в Спортивном слайсере. Сводная таблица фильтруется по выбранным значениям.

Как вы можете видеть, сводная диаграмма не фильтруется. Чтобы фильтровать PivotChart с теми же фильтрами, вам не нужно снова вставлять слайсеры для PivotChart. Вы можете использовать те же слайсеры, что и для сводной таблицы.

• Нажмите на слайсер NOC_CountryRegion .

• Перейдите на вкладку « OPTIONS » в « SLICER TOOLS» на ленте.

• Нажмите Сообщить о соединениях в группе слайсеров. Откроется диалоговое окно « Соединения отчетов » для слайсера NOC_CountryRegion.

Нажмите на слайсер NOC_CountryRegion .

Перейдите на вкладку « OPTIONS » в « SLICER TOOLS» на ленте.

Нажмите Сообщить о соединениях в группе слайсеров. Откроется диалоговое окно « Соединения отчетов » для слайсера NOC_CountryRegion.

Вы можете видеть, что все сводные таблицы и сводные диаграммы в книге перечислены в диалоговом окне.

• Нажмите на сводную диаграмму, которая находится на том же рабочем листе, что и выбранная сводная таблица, и нажмите кнопку «ОК».

• Повторите для Sport Slicer.

Нажмите на сводную диаграмму, которая находится на том же рабочем листе, что и выбранная сводная таблица, и нажмите кнопку «ОК».

Повторите для Sport Slicer.

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

Далее вы можете добавить детали в сводную диаграмму и сводную таблицу.

• Нажмите на сводную диаграмму.

• Перетащите Пол в область LEGEND.

• Щелкните правой кнопкой мыши на сводной диаграмме.

• Выберите Изменить тип диаграммы.

• Выберите столбец с накоплением в диалоговом окне «Изменить тип диаграммы».

• Нажмите на сводную таблицу.

• Перетащите событие в область строк.

• Перейдите на вкладку «ДИЗАЙН» в «Сводные инструменты» на ленте.

• Нажмите Макет отчета.

• Выберите Outline Form из выпадающего списка.

Нажмите на сводную диаграмму.

Перетащите Пол в область LEGEND.

Щелкните правой кнопкой мыши на сводной диаграмме.

Выберите Изменить тип диаграммы.

Выберите столбец с накоплением в диалоговом окне «Изменить тип диаграммы».

Нажмите на сводную таблицу.

Перетащите событие в область строк.

Перейдите на вкладку «ДИЗАЙН» в «Сводные инструменты» на ленте.

Нажмите Макет отчета.

Выберите Outline Form из выпадающего списка.

Выбор объектов для отображения в отчете

Вы можете не отображать срезы в окончательном отчете.

• Перейдите на вкладку « OPTIONS » в « SLICER TOOLS» на ленте.

• Нажмите «Панель выбора» в группе «Упорядочить». Панель выбора появляется в правой части окна.

Перейдите на вкладку « OPTIONS » в « SLICER TOOLS» на ленте.

Нажмите «Панель выбора» в группе «Упорядочить». Панель выбора появляется в правой части окна.

Как вы можете наблюдать, символ появляется рядом с объектами в области выбора. Это означает, что эти объекты видны.

• Нажмите на символ рядом с NOC_CountryRegion.

• Нажмите на символ рядом со спортом. символ изменен на для обоих. Это означает, что видимость двух слайсеров отключена.

Нажмите на символ рядом с NOC_CountryRegion.

Нажмите на символ рядом со спортом. символ изменен на для обоих. Это означает, что видимость двух слайсеров отключена.

Закройте панель выбора.

Вы можете видеть, что два слайсера не видны в отчете.