Подстановка в формулах Power Pivot

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

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

Вычисляемые поля в Power Pivot

Вычисляемые столбцы в PowerPivot

В этом разделе описаны функции DAX, предоставленные для подстановки, а также некоторые примеры использования функций.

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

Общие сведения о функциях просмотра

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

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

Примечание: Если вы знакомы с реляционными базами данных, подстановки можно представить в Power Pivot так же, как и вложенная инструкция SELECT в Transact-SQL.

Получение одного связанного значения

СВЯЗАНная функция возвращает одно значение из другой таблицы, связанной с текущим значением в текущей таблице. Вы задаете столбец, содержащий необходимые данные, и функция вызывается существующими связями между таблицами для получения значения из указанного столбца в связанной таблице. В некоторых случаях функция должна следовать за цепью связей для получения данных.

Например, предположим, что у вас есть список текущих отгрузок в Excel. Тем не менее, список включает только ИДЕНТИФИКАЦИОНный номер сотрудника, ИДЕНТИФИКАЦИОНный номер заказа и ИДЕНТИФИКАЦИОНный номер, что затрудняет чтение отчета. Чтобы получить дополнительные сведения, вы можете преобразовать этот список в Power Pivot связанную таблицу, а затем создать связи с таблицами сотрудников и торговых посредников, совпадающее значение EmployeeID с полем EmployeeKey и Реселлерид в поле Реселлеркэй.

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

= "Связанные" ("сотрудники" [Емплойинаме
]) = РОДСТВЕННые ("торговых посредников" [CompanyName])

Сегодня отгрузки перед подстановкой

Код заказа

Код сотрудника

реселлерид

100314

230

445

100315

15

445

100316

76

108

Таблица Employees

Код сотрудника

ФИО

Торгового посредника

230

Куппа Вамси

Системы модульных циклов

15

Почтового Акерман

Системы модульных циклов

76

Kim Раллс

Сопоставленные велосипеды

Сегодняшние отгрузки с подстановками

Код заказа

Код сотрудника

реселлерид

ФИО

Торгового посредника

100314

230

445

Куппа Вамси

Системы модульных циклов

100315

15

445

Почтового Акерман

Системы модульных циклов

100316

76

108

Kim Раллс

Сопоставленные велосипеды

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

Получение списка связанных значений

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

= COUNTROWS (RELATEDTABLE (ResellerSales_USD))

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

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

Торгового посредника

Записи в таблице продаж для этого торгового посредника

Системы модульных циклов

Идентификатор торгового посредника

салесордернумбер

445

SO53494

445

SO71872

445

SO65233

445

SO59000

Идентификатор торгового посредника

салесордернумбер

Сопоставленные велосипеды

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

К началу страницы

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×