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

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

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

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

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

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

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

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

Основные сведения о функциях уточняющегося запроса

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

В отличие от функций подстановки Excel, например VLOOKUP, которая основана на массивах, или функции ПОДСТАНОВКИ, которая возвращает первое из нескольких совпадающих значений, DAX следует по существующим связям между таблицами, соединенными ключами, чтобы получить единое, полностью совпадающее связанное значение. DAX также возвращает таблицу записей, связанных с текущей записью.

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

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

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

Например, допустим, что список сегодняшних поставок содержится в Excel. Тем не менее список, содержащий только идентификатор сотрудника, идентификатор заказа и идентификатор грузоотправителя, делает отчет неудобным для чтения. Чтобы получить нужные дополнительные данные, можно преобразовать список в связанную таблицу Power Pivot, а затем создать связи с таблицами Employee и Reseller, сопоставив EmployeeID с полем EmployeeKey, а ResellerID — с полем ResellerKey.

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

= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])

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

OrderID

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

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

100314

230

445

100315

15

445

100316

76

108

Таблица «Сотрудники»

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

Сотрудника

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

230

Куппа Вамси

Modular Cycle Systems

15

Акерман Пилар

Modular Cycle Systems

76

Ким Раллс

Компания Associated Bikes

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

OrderID

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

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

Сотрудника

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

100314

230

445

Куппа Вамси

Modular Cycle Systems

100315

15

445

Акерман Пилар

Modular Cycle Systems

100316

76

108

Ким Раллс

Компания Associated Bikes

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

Извлечение списка связанных значений

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

=COUNTROWS(RELATEDTABLE(ResellerSales_USD))

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

Для посредника Modular Cycle Systems в нашем образце базы данных имеется четыре заказа в таблице продаж, поэтому функция возвращает значение 4. Для посредника Associated Bikes продаж нет, поэтому функция возвращает пустое значение.

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

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

Modular Cycle Systems

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

SalesOrderNumber

445

SO53494

445

SO71872

445

SO65233

445

SO59000

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

SalesOrderNumber

Компания Associated Bikes

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

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

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

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

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

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

×