Внутренняя модель данных Power Pivot

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

#PowerPivot

​​Отображение интерфейса надстройки Power Pivot на ленте

Вкладка надстройки Power Pivot на ленте программы может появиться только в том случае, если эта надстройка была активизирована. Важно также отметить, что надстройка Power Pivot включена в состав не всех версий дистрибутивов пакета Microsoft Office. Например, если у вас Microsoft Office Home edition, в своем приложении Excel вы не сможете найти и активизировать надстройку Power Pivot, а значит, у вас не будет и доступа к ее интерфейсу на ленте приложения.

Чтобы активизировать ленточный интерфейс надстройки Power Pivot, выполните следующую последовательность действий:
1.  Откройте программу Excel и поищите на ленте вкладку Power Pivot. Если она там уже присутствует, пропустите все остальные этапы - вам ничего не нужно делать.
2.  На ленте Excel выберите команду Файл => Параметры. Откроется диалоговое окно Параметры Excel.
3.  В диалоговом окне Параметры Excel перейдите на вкладку Настроить ленту.
4.  В списке Основные вкладки в правой части окна найдите флажок Power Pivot и установите его.
5.  Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Параметры Excel. Вкладка Power Pivot должна появиться на ленте приложения. Если вкладка Power Pivot на ленте не появилась, закройте программу и вновь запустите Excel.

#PowerPivot

​​Предварительная подготовка таблиц Excel

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

Чтобы преобразовать каждый из наборов данных в таблицу Excel, выполните следующие действия:
1.  Откройте рабочий лист и щелкните на любой его ячейке с данными;
2.  Нажмите комбинацию клавиш <Ctrl+T> (или выберите на ленте команду Вставка => Таблицы => Таблица). В результате откроется диалоговое окно Создание таблицы;
3.  В диалоговом окне Создание таблицы убедитесь, что предлагаемый программой диапазон точно соответствует диапазону расположения данных, а флажок Таблица с заголовками установлен, после чего щелкните на кнопке ОК. На ленте появится контекстная вкладка Работа с таблицами => Конструктор.
4. На ленте откройте вкладку Работа с таблицами => Конструктор и в группе Свойства введите в поле Имя таблицы более подходящее для этой таблицы имя. Таким образом вы получите гарантию, что при работе с внутренней моделью данных вы всегда будете знать, с какой именно таблицей имеете дело.

#PowerPivot

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

После преобразования обычных диапазонов данных в таблицы Excel все готово для их добавления в модель данных надстройки Power Pivot. Чтобы добавить вновь созданные таблицы Excel в модель данных, откройте на ленте вкладку Power Pivot и выполните следующее:
1. Щелкните на любой ячейке в таблице Excel Клиенты.
2. На ленте выберите команду Power Pivot => Таблицы => Добавить в модель данных. Надстройка Power Pivot создаст копию указанной таблицы и откроет свое окно Power Pivot для Excel.

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

#PowerPivot

​​Создание отношений между таблицами в модели данных Power Pivot

Чтобы связать таблицы, необходимо определить отношения между таблицами Клиенты, Заголовок_счета и Детали_счета. Сделать это можно непосредственно в окне надстройки Power Pivot.
1.  Перейдите в окно надстройки Power Pivot и выберите на ее ленте команду Главная => Просмотр => Представление диаграммы. Надстройка Power Pivot отобразит окно с графическим представлением всех таблиц, входящих в ее модель данных.
В окне представления диаграммы таблицы можно произвольно перемещать, просто перетаскивая их мышью за строку заголовка. Основное назначение этого представления - указание первичных ключей индексов в каждой из таблиц с последующим их соединением для установления связей. В нашем примере таблицы Клиенты и Заголовок_счета могут быть соединены с помощью поля ID_клиента. В свою очередь, таблицы Заголовок_счета и Детали_счета могут быть соединены с помощью поля Номер_счета.
2.  Щелкните на поле ID_клиента в таблице Клиенты, чтобы выделить его, а затем протащите указатель мыши от этого поля к одноименному полю в таблице Заголовок_счета
3. Щелкните на поле Номер_счета в таблице Заголовок_счета и перетащите указатель мыши к полю Номер_счета в таблице Детали_счета.
Обратите внимание, что надстройка Power Pivot вывела особые линии между таблицами, которые только что были соединены. В терминологии баз данных эти линии называют связями.

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

#PowerPivot

​​Управление созданными отношениями

Чтобы отредактировать или удалить связь, установленную между двумя таблицами в модели данных, достаточно выполнить следующие действия:
1. Перейдите в окно надстройки Power Pivot и выберите на ленте команду Конструктор => Связи => Управление связями. Откроется диалоговое окно Управление связями.
2.  В этом окне щелкните на строке связи, которую требуется отредактировать или удалить, а затем щелкните на кнопке Изменить или Удалить соответственно. После щелчка на кнопке Изменить откроется диалоговое окно Изменение связи.
3.  В этом окне воспользуйтесь имеющимися элементами управления для выбора требуемой таблицы и того поля, которое будет использоваться в данной связи, после чего щелкните на кнопке ОК.

#PowerPivot

​​Использование данных из модели надстройки Power Pivot при создании отчетов

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

Чтобы получить в Excel доступ к данным надстройки Power Pivot, выполните следующие действия:
1.  Откройте окно надстройки Power Pivot и выберите на ее ленте команду Главная => Сводная таблица. Откроется диалоговое окно Создать сводную таблицу;
2.  В этом окне укажите, где требуется создать сводную таблицу - на новом листе или на уже существующем, после чего щелкните на кнопке ОК;
3.  Воспользовавшись открывшейся панелью задач Поля сводной таблицы, создайте такой макет сводной таблицы, который необходим для проведения требуемого анализа, - точно так, как это делается при создании стандартных сводных таблиц.

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

#PowerPivot

​​​Лень разбираться в Excel?

Для вас уже всё сделали.
📌 Канал Эксельщик рассказывает обо всех возможностях, приёмах и лайфхаках по работе в excel.

Подписывайтесь и прокачивайте свои навыки: @excel

​​Загрузка данных из буфера обмена

Надстройка Power Pivot предусматривает интересный вариант загрузки данных непосредственно из буфера обмена. Иначе говоря, речь идет о возможности вставить в модель данные, предварительно скопированные где-то в другом месте. Этот вариант получения данных предполагается использовать как разовое средство, позволяющее быстро внести в модель данных надстройки Power Pivot некоторую полезную информацию.
Если вас заинтересовал этот вариант импорта данных, примите к сведению, что в этом случае не существует никакого реального источника данных. Вы вручную просто копируете некоторые данные в буфер обмена, а затем вставляете их в модель. Эта процедура ничем не отличается от копирования и вставки любых других объектов в среде Windows. Естественно, в дальнейшем у вас не будет никакой возможности обновить эти данные, а также выяснить, откуда именно они были скопированы.
Предположим, что у вас есть документ Word, в котором имеется таблица с названиями компаний, которые можно рассматривать как потенциальных партнеров. Вам кажется полезным скопировать этот фиксированный список и включить его в модель данных настройки Power Pivot в таком же виде.

Вы можете скопировать эту таблицу в буфер обмена, а затем перейти в окно надстройки Power Pivot и выбрать на ее ленте команду Главная => Буфер обмена => Вставить. Откроется диалоговое окно Просмотр вставки. В этом окне можно проанализировать, что именно будет вставлено в модель данных надстройки.
В этом окне можно изменить совсем немного параметров. Можно определить имя создаваемой таблицы, под которым она будет представлена на диаграмме надстройки Power Pivot, а также уточнить, содержит ли первая строка этих данных наименования столбцов создаваемой таблицы.
После щелчка на кнопке ОК эти данные будут импортированы в модель данных надстройки Power Pivot без каких-либо промежуточных этапов. Далее можно будет применить к ним желаемое форматирование, а также определить необходимые связи с другими таблицами модели.

#PowerPivot

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

Откройте окно надстройки Power Pivot и выберите на ее ленте команду Главная => Получение внешних данных => Из других источников. Откроется диалоговое окно Мастер импорта таблиц. Прокрутите список в этом окне вниз и выберите вариант Текстовый файл, после чего щелкните на кнопке Далее.
Мастер импорта таблиц откроет следующее окно, Соединение с неструктурированным файлом, в котором вам нужно будет указать информацию, необходимую для установления соединения с целевым текстовым файлом. В этом окне нужно ввести следующее:
▪️ Понятное имя соединения. В этом поле можно ввести любое выбранное вами название для этого источника данных. Как правило, лучше использовать описательное название, достаточно простое для прочтения и запоминания.
▪️ Путь к файлу. Введите в это поле полный путь к целевому текстовому файлу. Для поиска и выбора того файла, из которого следует извлечь информацию, можно воспользоваться кнопкой Обзор...
▪️ Разделитель столбцов. В этом раскрывающемся списке выберите тот символ, который в целевом текстовом файле был использован в качестве разделителя столбцов. Понятно, что прежде чем сделать это, необходимо выяснить, какой именно символ использовался в данном файле для этих целей. Например, в файлах формата CSV для разделения значений полей в записи всегда используется запятая. Данный список содержит шесть наиболее распространенных вариантов разделителей полей в тестовых файлах: Табуляция, Запятая, Точка с запятой, Пробел, Двоеточие и Вертикальная черта.
▪️ Использовать первую строку в качестве заголовков столбцов. Если целевой текстовый файл содержит строку заголовков столбцов, обязательно убедитесь, что флажок Использовать первую строку в качестве заголовков столбцов установлен, чтобы надстройка Power Pivot смогла извлечь эти заголовки и правильно их обработать при импорте.
Закончив ввод указанной выше информации, щелкните на кнопке Далее, и мастер импорта таблиц выведет следующее окно, предоставляющее возможность предварительно просмотреть и проанализировать загружаемую информацию. Так же имеется возможность отфильтровать лишние столбцы данных, просто сбросив флажки рядом с их названиями в строке заголовков. Кроме того, в строке заголовков для каждого столбца дополнительно выведена кнопка фильтра, воспользовавшись которой, можно отфильтровать и ненужные строки данных.
Завершив подготовку, щелкните на кнопе Готово, чтобы запустить процесс импорта. После его завершения данные из указанного текстового файла станут частью модели данных надстройки Power Pivot.

#PowerPivot

​​Загрузка данных из внешних файлов Excel

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

Итак, откройте окно надстройки Power Pivot и на ее ленте выберите команду Главная => Получение внешних данных => Из других источников. Откроется диалоговое окно Мастер импорта таблиц. В этом окне прокрутите список вниз и выберите вариант Файл Excel, а затем щелкните на кнопке Далее.
В следующем окне мастер импорта таблиц запросит у вас всю информацию, необходимую ему для подключения к целевой рабочей книге. В этом окне нужно будет ввести следующие данные:
▪️ Понятное имя соединения. В этом поле можно ввести любое выбранное вами название для этого источника данных. Как правило, лучше использовать описательное название, достаточно простое для прочтения и запоминания.
▪️ Путь к файлу Excel. Введите в это поле полный путь к файлу целевой рабочей книги Excel. Для поиска и выбора того файла рабочей книги, из которого следует извлечь информацию, можно воспользоваться кнопкой Обзор...
▪️ Использовать первую строку в качестве заголовков столбцов. В большинстве случаев данные в программе Excel уже содержат заголовки столбцов. Если это так, убедитесь, что флажок Использовать первую строку в качестве заголовков столбцов установлен, чтобы надстройка Power Pivot смогла правильно их обработать при импорте.

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

#PowerPivot

Обновление соединений с внешними источниками данных и управление ими

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

#PowerPivot

​​Обновление данных в модели Power Pivot вручную

В окне надстройки Power Pivot на вкладке Главная ленты есть кнопка Обновить. Если щелкнуть на стрелке раскрытия списка в нижней части этой кнопки, откроется меню с двумя командами: Обновить и Обновить все.

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

Если выбрать команду Главная => Обновить => Обновить все, то это приведет к обновлению информации во всей модели данных надстройки данных Power Pivot.

#PowerPivot

Хочешь с нуля освоить Excel и узнать все тонкости Google-таблиц? Тогда поспеши на курс от Skillbox, который научит тебя всем необходимым навыкам всего за 4 месяца.
Занимайся в любое удобное время всего несколько часов в неделю

👉 Все подробности по ссылке: https://clc.am/XKK9HA

Чему ты научишься в процессе обучения:
⚡️ строить прогнозы;
⚡️ правильно оформлять данные и наглядно их предоставлять;
⚡️ создавать макросы для языка VBA;
⚡️ работать с внешними источниками данных и инструментами фильтрации.

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

​​Настройка процедуры автоматического обновления

Существует возможность настроить соединение с источником данных таким образом, что извлечение из него данных с обновлением информации в модели данных надстройки Power Pivot будет происходить автоматически. Чтобы реализовать эту возможность, в окне программы Excel выберите на ленте команду Данные => Запросы и подключения => 3апросы и подключения. Откроется панель задач Запросы и подключения.
В открывшемся диалоговом окне Свойства соединения перейдите на вкладку Использование. Для настройки автоматического обновления выбранного источника данных на этой вкладке необходимо задать значения следующих параметров:
▪️ Обновлять каждые X минут. Если установить этот флажок, то программа Excel будет автоматически обновлять выбранный источник данных с интервалом, заданном в поле счетчика этой строки (в минутах). В этом случае также будут автоматически обновляться и все другие таблицы, связанные с этим соединением.
▪️ Обновление при открытии файла. Если установить этот флажок, то программа Excel будет автоматически обновлять выбранное соединение при каждом открытии данной рабочей книги. В этом случае при каждом открытии рабочей книги будут автоматически обновляться и все другие таблицы, связанные с этим соединением.
▪️ Обновлять это подключение по команде "Обновить все". Выше в этом разделе сообщалось о том, что при выборе в окне надстройки Power Pivot команды Главная => Обновить => Обновить все можно одновременно обновить все соединения, посредством которых информация передается в модель данных надстройки. Однако, если через определенное соединение из внешнего источника в модель импортируются миллионы строк данных, вызванный этим фактом приостанов нормальной работы машины при каждом выборе команды Обновить все может оказаться крайне нежелательным. Чтобы решить эту проблему, достаточно просто сбросить данный флажок для выбранного соединения. В результате при выполнении команды Обновить все это соединение будет игнорироваться.

#PowerPivot

​​Редактирование свойств соединения

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

В окне надстройки Power Pivot выберите на ее ленте команду Главная => Получение внешних данных => Существующие соединения. Откроется диалоговое окно Существующие соединения. Соединения относящиеся к модели данных надстройки Power Pivot, будут собраны в группе Подключения к данным Power Pivot. Выберите в этой группе то соединение, которое требуется отредактировать.
Выбрав в окне Подключения к данным Power Pivot требуемое соединение, для изменения его параметров можно воспользоваться кнопками Изменить и Открыть. Какую именно кнопку следует выбрать зависит от того, что именно необходимо изменить.
▪️ Кнопка Изменить. Позволяет переопределить адрес сервера, путь к файлу и параметры учетной записи.
▪️ Кнопка Открыть. Позволяет организовать импорт новых таблиц в уже существующем соединении. Этот вариант пригодится вам в том случае, если при исходном определении параметров соединения случайно была пропущена одна из требуемых для работы таблиц.

#PowerPivot

Всем подписчикам Выгодное предложение!

Только до 25 мая действует Скидка 70% на 3 мощных курса по Excel (Базовый+Продвинутый+VBA)
Всего 1990 рублей вместо 6590 руб!

💪 Прокачаешь свои навыки от уровня Новичка до Мастера
👑 Уверенно освоишь сводные таблицы, сложные диаграммы, ВПР, PowerQuery и продвинутое программирование на VBA
🆘 Любая консультация в процессе прохождения курсов!

Хотите быть профи в Excel? Жми сюда и забирай курсы быстрей!👇