добавить эти данные в модель данных excel что это
Модели данных в службах Excel
Службы Excel позволяет просматривать и использовать книги, опубликованные в SharePoint. Эти книги могут включать отчеты, системы показателей, панели мониторинга и модели данных. Модель данных — часть возможностей для самостоятельной бизнес-аналитики в Office профессиональный плюс. Вы собираете данные в модель данных, а затем применяете ее как источник для публикуемых и совместно используемых отчетов и систем показателей.
Что такое модель данных?
По сути, модель данных — это набор данных, собираемых и упорядочиваемых с помощью Power Pivot для Excel. Как правило, она состоит из одной или нескольких таблиц. При создании модели данных вы объединяете информацию из разных источников, включая Microsoft Access 2013, Microsoft SQL Server, веб-каналы данных OData, базу SQL Microsoft Azure, XML-файлы, Oracle, Teradata и другие источники. Вы можете сортировать и упорядочивать данные, а также создавать связи между разными таблицами с помощью Power Pivot для Excel.
Дополнительные сведения о Power Pivot для Excel и моделях данных см. в указанных ниже ресурсах.
Зачем использовать модели данных?
С помощью моделей данных в Excel можно собирать данные из разных источников, создавать согласованные наборы данных и использовать их для создания отчетов, систем показателей и панелей мониторинга. Модели данных обеспечивают некоторые преимущества в сравнении с простым подключением к внешним источникам данных.
Можно собирать данные из разных источников в один набор данных.
С помощью функции мгновенного заполнения в Excel 2013 можно форматировать столбцы для упрощения чтения и использования данных.
Можно просматривать, сортировать и организовывать данные.
Созданную модель данных в Excel можно использовать для создания диаграмм, таблиц, систем показателей и панелей мониторинга. Например, вы можете создавать в Excel 2013 интерактивные отчеты сводных диаграмм и сводных таблиц, а в Power View — гибридные веб-приложения, включающие интерактивные представления, такие как графики, линейчатые и пузырьковые диаграммы и т. д.
Дополнительные сведения о создании и использовании моделей данных см. в указанных ниже ресурсах.
Как службы Excel работают с моделями данных?
Службы Excel позволяет просматривать и использовать книги, опубликованные в библиотека SharePoint, в том числе содержащие модели данных. В зависимости от конкретной среды Службы Excel может сохранять подключение к внешним источникам данных, чтобы информация оставалась актуальной. Это может сэкономить много времени и усилий. Кроме того, с помощью разрешений SharePoint вы можете контролировать просмотр и использование данных.
Дополнительные сведения о Службы Excel см. в указанных ниже ресурсах.
Добавление данных с листа в модель данных с помощью связанной таблицы
Связанная таблица — это Excel, которая содержит ссылку на таблицу в модели данных. Преимущество создания и обслуживания данных в таблице Excel, а не импорта данных из файла заключается в том, что вы можете продолжать изменять значения на Excel, используя связанную таблицу в модели данных в качестве основы для отчета в сводная диаграмма или сводная диаграмма.
Чтобы добавить связанную таблицу, достаточно выбрать диапазон и нажать кнопку Добавить в модель данных. Кроме того, следует отформатирование диапазона как таблицы и придать ему правильное имя. Гораздо проще выполнять вычисления и управлять отношениями с помощью именуемой связанной таблицы.
Чтобы связать данные с таблицей, выполните указанные ниже действия.
Вы можете выбрать диапазон строк и столбцов, которые вы хотите использовать в связанной таблице.
Отформатирование строк и столбцов в виде таблицы
На ленте > в формате таблицы, а затем выберите стиль таблицы. Вы можете выбрать любой стиль, но не забудьте выбрать таблицу с заглавами. Если в таблице нет заглавных таблиц, можно создать их прямо сейчас. В противном Excel будут использовать произвольные имена (столбец1, столбец2 и так далее), которые не передают осмысленные сведения о содержимом столбца.
Присвойте таблице имя. В окне Excel нажмите кнопку Конструктор средств работы с таблицами. В группе Свойства введите имя таблицы.
Поместите курсор на любую ячейку таблицы.
Щелкните Power Pivot > добавить в модель данных, чтобы создать связанную таблицу. В окне Power Pivot вы увидите таблицу со значком ссылки, указывающим на то, что таблица связана с таблицей-источником в Excel:
Если модель уже содержит таблицы, то нужно сделать еще один шаг. Необходимо создать связь между новой таблицей, которую вы только что добавили, и другими таблицами в модели. Инструкции см. в таблицах Создание связи между двумя таблицами и Создание связей в представлении диаграммы.
Если книга ранее не содержала ее, теперь она содержит модель данных. Модель создается автоматически при создании связи между двумя таблицами или нажатии кнопки Добавить в модель данных в Power Pivot. Дополнительные сведения см. в этойExcel.
Теперь, когда у вас есть связанная таблица, вы можете изменить любую ячейку на этом столе, включая добавление и удаление строк и столбцов. Модель данных в Power Pivot сразу же синхронизируется.
Имена таблиц являются исключением. Если переименовать таблицу в Excel, потребуется обновить ее вручную в Power Pivot.
Совет: Дополнительные сведения о связанных таблицах см. в учебнике Импорт данных в Excel и Создание модели данных.
Синхронизация изменений между таблицей и моделью
По умолчанию связанная таблица является активным подключением, которое сохраняется между диапазоном или именоваемой таблицей, содержаной значения данных, и моделью данных, которая является диском отчета. При добавлении или удалите данные либо переименуйте столбцы и таблицы, модель данных будет обновляться автоматически.
В некоторых случаях может потребоваться контролировать поведение обновления. Переключение в режим обновления вручную с помощью надстройки Power Pivot.
Чтобы переключиться в режим обновления вручную, выполните указанные здесь действия.
Убедитесь, что книга со связанной таблицей открыта в Excel.
Откройте окно Power Pivot.
На вкладке внизу щелкните связанную таблицу. Любая связанная таблица обозначена маленьким значком ссылки рядом с ее именем.
В верхней части ленты щелкните Связанная таблица.
В режиме обновления выберите Вручную или Автоматически. По умолчанию этот режим является автоматическим. Если переключиться на ручное, обновления будут происходить только при использовании команд Обновить все или Обновить выбранные на ленте Связанной таблицы в окне Power Pivot или команды Обновить все на ленте Power Pivot в Excel.
Преимущества сводной по Модели Данных
К сожалению, очень многие даже давно знакомые со сводными таблицами и успешно применяющие их в работе пользователи, порой не очень понимают смысл этих опций и никогда их не используют. И зря. Ведь создание сводной по Модели Данных даёт нам несколько очень важных преимуществ по сравнению с классической сводной таблицей Excel.
Однако, перед тем, как рассматривать эти «плюшки» вблизи, давайте сначала разберёмся с тем, что такое, собственно, эта Модель Данных?
Что такое Модель Данных
Важное замечание по ходу: книга Excel может содержать только одну Модель Данных.
Грузим таблицы в Модель Данных
Затем можно использовать любой из трех способов, на выбор:
Строим сводную по Модели Данных
Чтобы построить сводную Модели Данных можно использовать любой из трёх подходов:
Теперь, когда мы разобрались с тем, как загружать данные в Модель Данных и строить по ним сводную, давайте изучем те выгоды и преимущества, которые нам это даёт.
Преимущество 1. Связи между таблицами без помощи формул
Преимущество 2. Подсчёт количества уникальных значений
Обычная сводная таблица даёт нам возможность выбрать одну из нескольких встроенных функций расчёта: сумму, среднее, количество, минимум, максимум и т.д. В сводной по Модели Данных к этому стандартному списку добавляется весьма полезная функция подсчёта количества уникальных (неповторяющихся значений). С её помощью, например, можно легко посчитать количество уникальных наименований товаров (ассортимент), который мы продаём в каждом городе.
Преимущество 3. Свои формулы на языке DAX
Иногда в сводных таблицах приходится выполнять различные дополнительные вычисления. В обычных сводных это делается с помощью вычисляемых полей и объектов, а сводной по Модели Данных для этого используются меры на специальном языке DAX (DAX = Data Analysis Expressions).
В открывшемся окне задаём:
Язык DAX не всегда прост для понимания, т.к. оперирует не отдельными значениями, а целыми столбцами и таблицами, т.е. требует некоторой перестройки мышления после классических формул Excel. Однако же, оно того стоит, ибо мощь его возможностей при обработке больших объемов данных трудно переоценить.
Преимущество 4. Свои иерархии полей
Созданную иерархию можно переименовать и перетащить в неё мышью требуемые поля, чтобы потом в одно движение забрасывать их в сводную:
Преимущество 5. Свои наборы элементов
Продолжая идею предыдущего пункта, в сводной по Модели Данных можно создавать ещё и свои наборы элементов для каждого поля. Например, из всего списка городов можно легко сделать набор только из тех, которые входят в зону вашей ответственности. Или собрать в специальный набор только своих клиентов, свои товары и т.п.
В открывшемся окне можно выборочно удалить, добавить или поменять положение любых элементов и сохранить получившийся набор под новым именем:
Все созданные наборы будут отображаться в панели полей сводной таблицы в отдельной папке, откуда их можно свободно перетаскивать в области строк и столбцов любой новой сводной таблицы:
Преимущество 6. Выборочное скрытие таблиц и столбцов
Это хоть и небольшое, но весьма приятное в некоторых случаях преимущество. Щёлкнув правой кнопкой мыши по названию поля или по ярлычку таблицы в окне Power Pivot, можно выбрать команду Скрыть из набора клиентских средств (Hide from Client Tools) :
Скрытый столбец или таблица пропадут из панели со списком полей сводной таблицы. Очень удобно, если вам требуется скрыть от пользователя некоторые вспомогательные столбцы (например, расчетные или столбцы с ключевыми значениями для создания связей) или даже целые таблицы.
Преимущество 7. Продвинутый drill-down
Если в обычной сводной таблице сделать двойной щелчок левой кнопкой мыши по любой ячейке в области значений, то Excel выводит на отдельном листе копию фрагмента исходных данных, которые участвовали в расчёте этой ячейки. Это очень удобная штука, официально называющаяся Drill-down (на русском обычно говорят «провалиться»).
В сводной по Модели Данных этот удобный инструмент работает более тонко. Встав на любую интересующую нас ячейку с результатом, можно щёлкнуть по всплывающему рядом значку с лупой (он называется Экспресс-тенденции) и выбрать затем любое интересующее вас поле в любой связанной таблице:
После этого текущее значение (Модель = Explorer) уйдет в область фильтра, а сводная будет построена уже по офисам:
Само-собой, такую процедуру можно повторять многократно, последовательно углубляясь в ваши данные в интересующем вас направлении.
Преимущество 8. Преобразование сводной в функции кубов
Технически, это означает, что теперь мы имеем дело не со сводной, а с несколькими ячейками с формулами, т.е. спокойно можем делать с нашим отчетом любые преобразования недоступные в сводных, например, вставлять в середину отчета новые строки или столбцы, делать внутри сводной любые доп.вычисления, оформлять их любым желаемым образом и т.д.
При этом связь с исходными данными, само-собой, остается и в будущем эти формулы будут обновляться при изменении источников. Красота!
Добавить эти данные в модель данных excel что это
Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.
Проблема
Бывает так, что анализируемые данные попадают к нам в виде отдельных таблиц, которые, тем не менее, нужно связать. Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP). Однако, начиная с Excel 2013, у нас появилась возможность при построении сводной таблицы в качестве источника использовать несколько таблиц, связанных между собой по ключевым полям.
Пример
Таблица Строк заказов:
Скачать пример
Создание модели данных
Создадим сводную таблицу на основе любой из имеющихся таблиц.
В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ.
Нажмём её. Появится такой вопрос:
Отвечаем Да и видим, что в список полей добавились все наши таблицы:
Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ.
Аналогично создаём другие связи.
В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения
Результат будет таким:
В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:
Excel Power Pivot — управление моделью данных
Основное использование Power Pivot — это его способность управлять таблицами данных и связями между ними, чтобы облегчить анализ данных из нескольких таблиц. Вы можете добавить таблицу Excel в модель данных во время создания сводной таблицы или непосредственно из ленты PowerPivot.
Вы можете анализировать данные из нескольких таблиц, только если между ними существуют отношения. С Power Pivot вы можете создавать отношения из представления данных или представления схемы. Более того, если вы решили добавить таблицу в Power Pivot, вам также необходимо добавить отношения.
Добавление таблиц Excel в модель данных с помощью сводной таблицы
При создании сводной таблицы в Excel она основана только на одной таблице / диапазоне. Если вы хотите добавить больше таблиц в сводную таблицу, вы можете сделать это с помощью модели данных.
Предположим, у вас есть две таблицы в вашей книге —
Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.
Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.
Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.
Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.
Вы можете суммировать продажи в зависимости от продавца, как указано ниже.
Нажмите на таблицу — Продажа.
Нажмите вкладку INSERT на ленте.
Выберите Сводная таблица в группе Таблицы.
Нажмите на таблицу — Продажа.
Нажмите вкладку INSERT на ленте.
Выберите Сводная таблица в группе Таблицы.
Нажмите на БОЛЬШЕ СТОЛОВ.
Нажмите на БОЛЬШЕ СТОЛОВ.
Откроется окно сообщения Создать новую сводную таблицу. Отображаемое сообщение: чтобы использовать несколько таблиц в анализе, необходимо создать новую сводную таблицу с помощью модели данных. Нажмите Да
Новая сводная таблица будет создана, как показано ниже —
Нажмите вкладку ВСЕ.
Две таблицы — 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 следующим образом:
Нажмите на лист, содержащий таблицу продаж.
Нажмите вкладку ДАННЫЕ на ленте.
Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».
Нажмите на вкладку Таблицы.
Нажмите на лист, содержащий таблицу продаж.
Нажмите вкладку ДАННЫЕ на ленте.
Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».