Excel power pivot изменить связь
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Связь – это соединение между двумя таблицами с данными, которое определяет зависимость между ними. Аналогом связи в PowerPivot являются функции подстановки в Excel (ВПР, ГПР, ИНДЕКС). К примеру, если вернуться к предыдущей статье об импорте данных в PowerPivot, у нас имеется основная таблица с данными о продажах в каждом магазине и подстановочная таблица, с информацией о каждом магазине (Название, регион и т.д.). Обе эти таблицы могут быть связаны через номер магазина. Если бы мы работали в Excel и нам понадобилось напротив каждой записи о продаже проставить в каком регионе это произошло, одним из вариантов решения данной задачи было бы использование формулы подстановки ВПР:
=ВПР(номер_магазина; таблица_StoreInfo; 6; ЛОЖЬ)
В PowerPivot подобные задачи решаются путём установления связей между таблицами.
Связи в PowerPivot могут быть созданы двумя путями: вручную, соединив две таблицы в окне PowerPivot или поля в окне Диаграмм, или автоматически, если PowerPivot обнаружит существующие связи во время импорта. Для создания связей вручную с помощью соединения колонок двух разных таблиц необходимо, чтобы данные в колонках были идентичными. К примеру, колонка с номером магазина (StoreID) в основной таблице и колонка с номером магазина в подстановочной таблице (Store) имеют одинаковые данные. Не обязательно при этом чтобы названия колонок в таблицах совпадали.
Зачем создавать связи
Для того чтобы выполнить какой-нибудь значимый анализ, необходимо, чтобы источники данных были связаны. В частности, связи позволяют вам:
- Фильтровать данные таблиц с колонками из связанных таблиц
- Интегрировать столбцы из нескольких таблиц в отчете сводной таблицы
- Находить значения в связанных таблицах с помощью формул DAX
Создание связи с помощью Конструктора
- Вы будете связывать одну колонку в основной таблице с колонкой в подстановочной таблице. Для упрощения процесса создания связи, выделите ячейку в колонке, которую вы хотите связать, в основной таблице.
- Перейдите по вкладке Конструктор в группу Связи –> Создание связи
- В появившемся диалоговом окне Создание связи вы увидите выбранную таблицу и колонку в первых двух полях
- Если вы пропустили первый шаг, в полях Таблица и Столбцы могут быть некорректные данные. Выберите таблицу Demo и столбец StoreID.
- В поле Связанная таблица подстановки в выпадающем меню выберите StoreInfo, в поле Связанный столбец подстановки выберите Store.
- После щелчка Создать, две таблицы будут связаны.
Создание связей в представлении диаграммы
Представление диаграммы создано для упрощения создания связей между двумя таблицами. Здесь можно визуально увидеть, как все таблицы соотносятся друг с другом.
СОВЕТ: Для быстрого создания связи выберите столбец, который необходимо связать, и перетащите его в столбец другой таблицы, с которым необходимо его связать.
Работа с несколькими таблицами позволяет сделать данные более интересными и релевантными для сводных таблиц и отчетов, которые их используют. При работе с данными в надстройке Power Pivot вы можете использовать представление диаграммы для создания подключений между импортированными таблицами и управления ими.
Создание связей между таблицами необходимо для того, чтобы каждая таблица имела столбец, содержащий совпадающие значения. Например, при соединении разделов «Клиенты» и «Заказы» каждая запись раздела «Заказ» должна будет содержать код клиента или идентификатор, разрешенные для одного клиента.
В окне Power Pivot выберите Представление диаграммы. Макет электронной таблицы "Представление данных" изменится на макет визуальной диаграммы, а все таблицы будут автоматически упорядочены на основе их связей.
Щелкните правой кнопкой диаграмму таблицы и выберите пункт Создание связи. Откроется диалоговое окно «Создание связи».
Если таблица из реляционной базы данных, то столбец будет предустановлен. Если не выбран ни один столбец, выберите один из таблицы, содержащей данные, которые будут использоваться для корреляции строк в каждой таблице.
В поле Связанная таблица подстановки выберите таблицу, содержащую хотя бы один столбец данных, связанный с таблицей, выбранной в поле Таблица.
В поле Столбец выберите столбец, содержащий данные, относящиеся к столбцу в поле Связанный столбец подстановки.
Примечание: Хотя Excel проверяет соответствие типов данных между каждым столбцом, он не проверяет наличие в столбцах соответствующих данных и создает отношение, даже если значения не соответствуют. Для проверки связи создайте сводную таблицу, содержащую поля из обеих таблиц. Если данные неправильные (например, пустые ячейки или одинаковые значения повторяются в каждой строке), необходимо выбрать разные поля и, возможно, разные таблицы.
Найдите связанный столбец
Если модели данных содержат много таблиц или таблицы содержат большое количество полей, может быть сложно выбрать столбцы для использования в связях таблицы. Одним из способов нахождения связанного столбца является нахождение его в модели. Этот метод удобен, если известно какой столбец (или ключ) необходимо использовать, но вы не уверены, включает ли в себя столбец другие таблицы. Например, таблицы фактов в хранилище данных обычно содержат много ключей. Можно начать с ключа в этой таблице и затем приступить к поиску модели для таблиц, содержащих этот же ключ. Любую таблицу, содержащую соответствующий ключ, можно использовать в связях для таблицы.
В окне Power Pivot нажмите кнопку Найти.
В окне функции Найти введите ключ или столбец в качестве условия поиска. Элементы поиска должны состоять из имени поля. Нельзя выполнять поиск по характеристикам столбца или типам данных, содержащихся в них.
Щелкните поле Показать скрытые поля во время поиска метаданных. Если ключ был скрыт для уменьшения помех в модели, он, возможно, не отобразится в окне функции «Представление диаграммы».
Изменение активной связи
Таблицы могут иметь несколько связей, но только одна может быть активной. Активные связи используются по умолчанию в вычислениях DAX и навигации по сводному отчету. Неактивные связи могут быть использованы в вычислениях DAX посредством функции USERELATIONSHIP. Дополнительные сведения см. в записи функции USERELATIONSHIP (DAX).
Многочисленные связи существуют, если таблицы были импортированы способом, при котором в исходном источнике данных были заданы многочисленные отношения для этой таблицы, или если вручную были созданы дополнительные связи для поддержки вычислений DAX.
Для изменения активной связи используйте неактивное отношение. Текущая активная связь автоматически станет неактивной.
Наведите указатель на линию связей между таблицами. Неактивная связь отобразится в виде пунктирной линии. (Связь неактивна, потому что между двумя столбцами уже существует косвенная связь.)
Щелкните правой кнопкой линию и выберите функцию Пометить как активную.
Примечание: Активировать отношение можно, только если нет других отношений между двумя таблицами. Если таблицы уже связаны, но нужно изменить режим соотношения, необходимо сначала пометить текущую связь как неактивную, а затем активировать новую.
Размещение таблицы в представлении диаграммы.
Чтобы увидеть все таблицы на экране, щелкните значок По размеру экрана, находящийся в правом верхнем углу представления диаграммы.
Для настройки удобного отображение используйте элемент управления Перетащите для увеличения и мини-карту и перетащите таблицы в необходимый макет. Для прокрутки экрана также можно использовать полосы прокрутки и колесо мыши.
Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.
В DAX связи очень важны. Но некоторые связи, как бы это сказать, сложнее, чем другие))
Несколько связей между двумя таблицами
Рассмотрим таблицы Sales и Calendar. Они связаны Calendar[Date] –> Sales[OrderDate]. Файл примера – ch22A_ComplicatedRelationships.xlsx.
Рис. 22.1. Могут ли эти две таблицы иметь более одной связи?
Дата заказа [OrderDate] – не единственное поле даты в таблице продаж Sales. Есть еще дата доставки. Что делать, если мы иногда хотим проанализировать наши данные о продажах по дате доставки? Как мы с этим справимся? Создадим ли мы еще одну связь между этими двумя таблицами? Это вообще сработает?
Рис. 22.2. Попытка создать связь Calendar[Date] –> Sales[ShipDate]; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Возможно, вы ожидали ошибки при нажатии кнопки Ok в диалоговом окне выше. Но на самом деле это работает. На приведенной ниже диаграмме можно заметить, что вновь созданная связь представлена пунктирной линией, а не сплошной.
Рис. 22.3. Двойные свидания не всегда проходят гладко))
Легче понять, что происходит, если в окне Power Pivot перейти на вкладку Конструктор и кликнуть на копке Управление связями (рис. 22.4) Обратите внимание на столбец Активно для связей между таблицами продаж Sales и календарем Calendar. В один и тот же момент может быть активна только одна связь между двумя таблицами.
Рис. 22.4. В любой момент времени может быть активна только одна связь между двумя таблицами
Если нам нужно проанализировать данные о продажах по дате доставки, то можем перевернуть активные связи, нажав на кнопку Изменить (см. верхнюю часть рис. 22.4). Если у вас есть сводная таблица, показывающая меру [Total Sales], вот как она изменится при изменении активной связи:
Рис. 22.5. Мера [Total Sales] показывает объем продаж на основе даты заказа или даты доставки, в зависимости от того, какая связь активна
Это неудобно, поэтому давайте рассмотрим несколько более элегантных способов сделать то же самое.
Надстройка Power Pivot в Excel
Power Pivot – это надстройка Excel, с помощью которой можно работать с данными в несколько миллионов строк, объединять таблицы в модель данных и создавать аналитические вычисления.
В «обычном» Excel пользователи ограничены количеством строк в таблице – не более размера листа в 1 048 тысяч строк, но в Power Pivot такого ограничения нет. Надстройка может подключаться к данным из внешних источников и работать с большими объемами информации в миллионы строк.
Открыть надстройку Power Pivot можно, нажав на вкладке меню Power Pivot кнопку Управление. Эта вкладка выглядит одинаково во всех версиях Excel.
Если такой вкладки у вас меню нет, проверьте, та ли у вас версия Excel . Так как Power Pivot представляет собой надстройку COM, то перед первым применением вам может потребоваться добавить её в меню (как это сделать, читайте в предыдущей статье ).
Хорошая новость: начиная c версий после 2019 года компания Microsoft анонсировала включение Power Pivot во все версии Excel.
Работа с данными в Power Pivot
Как правило, разработка отчетов в Power Pivot происходит в следующем порядке:
- Подключение к внешним источникам данных. При загрузке в Power Pivot данные сжимаются в несколько раз с помощью специальных механизмов оптимизации.
- Объединение таблиц в модель данных с помощью создания связей между ними.
- Аналитические вычисления с помощью DAX-формул.
- Построение сводных таблиц и диаграмм на основе модели данных.
Подключения к источникам, связи и вычисления настраиваются в отчете один раз. При изменении исходных данных отчеты можно обновить в меню Данные → Обновить все. Давайте разберем подробнее, как это работает.
Добавление данных в Power Pivot
Чтобы начать работать с Power Pivot, перейдите на вкладку меню Power Pivot → нажмите Управление. Добавить данные в открывшейся надстройке можно несколькими способами:
- С помощью встроенных инструментов импорта.
- Добавить данные из Power Query.
- Также таблицу с данными можно просто скопировать и вставить в Power Pivot из буфера обмена в меню Главная → Вставить.
Способ 1. Подключение к данным с помощью встроенных инструментов импорта.
В Power Pivot есть свои инструменты для импорта внешних данных, которые можно найти на вкладке Главная → кнопки Из базы данных, Из службы данных, Из других источников.
С помощью встроенных инструментов настраивается подключение к 15 видам источников данных.
Увидеть весь список можно в окне «Мастер импорта таблиц», которое открывается в меню Главная → Из других источников.
Настроим подключение к данным на примере файла Excel. Укажите путь к файлу, поставьте галочку «Использовать первую строку в качестве заголовков столбцов», выберите таблицы, жмем «Готово». У вас в окне включится счетчик импорта строк — работает довольно быстро. В результате импорта в окне Power Pivot появятся вкладки с таблицами.
Способ 2. Добавить данные из Power Query.
Загрузка данных с помощью инструментов Power Pivot делается легко, но Power Query лучше подходит для импорта и значительно расширяет возможности аналитики. В нем намного больше доступных источников и возможностей для обработки таблиц произвольного вида.
К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query и этот новый функционал в старом Excel сильно ограничен.
Интерфейс Power Pivot
Разберем подробнее интерфейс Power Pivot.
В окне Power Pivot есть:
- Лента редактора для вкладок меню Главная, Конструктор, Дополнительно.
- Строка формул на языке DAX.
- Область данных и вычисляемых столбцов.
- Добавление нового вычисляемого столбца.
- Область вычислений, в которой можно писать меры.
- Меню, которое появляется при нажатии правой кнопкой мышки.
- Ярлычки с названиями таблиц данных для переключения между ними (как между листами в «обычном» Excel).
Модель данных и связи
Чтобы перейти к настройке связей между таблицами, выберите в меню Главная → Представление диаграммы (вернутся обратно к просмотру таблиц можно, нажав Представление данных).
Модель данных в Power Pivot – это набор таблиц, объединенных связями.
Графически связь таблиц обозначается линией между ними, как в примере на рисунке. Чтобы создать связь, выделите мышкой поле в одной таблице и «перетащите» его на соответствующее ему поле в области другой таблицы.
Power Pivot поддерживает типы связей «один к одному», «один ко многим».
Если выделить мышкой линию связи в модели данных, то можно увидеть, с помощью каких полей задана связь. Выделенные линии можно удалять. Или, щелкнув по ним дважды, менять связи в открывшемся окне. Также управление связями доступно в окне, которое открывается в меню Конструктор → Управление связями.
Вычисления в Power Pivot
Формулы Power Pivot пишут на языке DAX (Data Analysis Expressions, выражения для анализа данных). DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и/или настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX впервые появился в 2010 году вместе с надстройкой Power Pivot. В этом языке сотни функций, с помощью которых можно создавать аналитические расчеты. Кроме Power Pivot в Excel, DAX-формулы также доступны в Power BI и Analysis Services. То есть эти формулы вам точно пригодятся.
Вычисления с помощью DAX-формул создаются в виде:
- вычисляемых столбцов, как в обычных таблицах Excel.
- мер, которые пишут в области вычислений под таблицей.
Вычисляемые столбцы представляют собой столбцы в таблицах данных, созданные с помощью формул. Чтобы добавить такой столбец, щелкните мышкой дважды по столбцу слева «Добавление столбца», введите название вычисления, а затем знак «=» и формулу в строке формул.
Вычисляемый столбец похож на любой другой столбец в таблице. Также с помощью таких столбцов можно создавать связи в модели. Вычисляемые столбцы в Excel рассчитываются в момент загрузки (обновления) данных, а значения в самом столбце сохраняются, увеличивая размер файла. То есть столбцы – это статические вычисления.
Как и вычисляемые столбцы, меры тоже создаются с помощью DAX-формул. В отличие от столбцов, меры – это динамические вычисления, результат которых зависит от контекста – его можно увидеть в отчете, где мы задаем в разрезе каких полей, фильтров и др. вычислить меру. Меры записываются под таблицей в области вычислений.
Меры в Power Pivot можно превратить в KPI – ключевые показатели эффективности. Для этого выделите меру и нажмите на кнопку Создать KPI в меню Главная. Кроме мер, созданных пользователями, в Excel также есть неявные меры. Они создаются автоматически при формировании сводной таблицы, когда пользователь помещает данные в область значений. Чтобы посмотреть, есть ли у вас в Power Pivot неявные меры, выберите на вкладке Главная → Показать скрытые.
Как Вы могли убедиться из моих предыдущих постов, меры являются мощными инструментами анализа данных и позволяют производить немыслимые до этого виды расчётов. Однако, до сих пор при знакомствами с мерами мы использовали только одну таблицу t_sales. Но вся прелесть Power Pivot в том, что с его помощью можно производить расчёты, комбинируя данные из нескольких таблиц. По моему личному мнению, если бы даже Powe Pivot не имел встроенного движка функций DAX, одна только способность связывания таблиц, уже оправдывала бы его существование.
Создание связей
Так как же создаются связи между таблицами? Всё очень просто. Заходим в окно Power Pivot и в правом нижнем углу, нажимаем на иконку со всплывающей надписью "Диаграмма".
Либо по кнопке "Представление диаграммы" на вкладке "Главная".
Откроется окно представления диаграммы, в котором все таблицы нашей модели данных показаны в виде отдельных окошек, со списком колонок.
Как видим, эти таблицы между собою пока никак не связаны. Чтобы создать между ними связь, нам сначала нужно определить идентичные колонки. Идентичными колонками, называют колонки содержащие одинаковые данные. К примеру, и в таблице t_sales и в таблице t_products есть колонки КодПродукта, содержащие одинаковые данные (при этом не обязательно, чтобы названия колонок в обоих таблицах были одинаковыми). Свяжем эти две таблицы между собою кликнув по названию колонки КодПродукта в t_sales и удерживая левую кнопку мыши нажатой, перетащим эту колонку к другой колонке КодПродукта в t_products.
Точно также, связь между таблицами можно создать через команду "Создание связи" на вкладке "Конструктор".
Но можно ли создавать связь между таблицами по любым идентичным столбцам? Например столбцы "ЦенаЗаШтуку" в t_sales и "Цена" в t_products содержат одинаковые данные. Попробуем создать между ними связь путём перетаскивания. Power Pivot выдаст ошибку: "Не удалось создать связь, поскольку в каждом столбце содержатся повторяющиеся значения. Выберите по крайней мере один столбец, содержащий только уникальные значения."
То есть для того, чтобы установить связь между таблицами, один из связывающих столбцов должен содержать только уникальные, не повторяющиеся значения. К примеру, цена у нескольких продуктов может быть одинаковой (повторяться), поэтому использовать эти столбцы для создания связи между таблицами не получится. А вот "КодПродукта" в t_products содержит только уникальные значения, поэтому мы и смогли использовать его для создания связи.
Таблицы, содержащие столбцы с уникальными значениями, по которым устанавливается связывание, называются "таблицами поиска" (lookup tables).
Ниже представлена сводная таблица на основе данных таблицы t_sales.
Как это работает
Давайте рассмотрим как работает связь между таблицами, на примере ячейки данных следующей сводной таблицы:
Фильтры, применённые к таблице поиска передаются через установленную связь основной таблице.
Однако, фильтры применённые к основной таблице, не передаются таблице поиска.
Функция CALCULATE () и связанные таблицы
Давайте создадим ещё одну связь между таблицами. Свяжем таблицу t_sales с таблицей t_clients.
В таблице t_sales, в столбце КоличествоДетейНаПопечении, имеется информация о том сколько отпрысков находятся на попечении каждого клиента. Создадим меру, которая бы на основании этих данных, рассчитывала сумму продаж клиентам с детьми:
Читайте также: