Перейти к основному контенту
Office

Подстановка в формулах 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.

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

= "Связанные" ("сотрудники" [Емплойинаме
]) = "Связанные" ("реселлеры" [название])

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

OrderID

Идентификатор сотрудника

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

100314

230

445

100315

15

445

100316

76

108

Таблица Employees

Идентификатор сотрудника

Сотрудник

Reseller

230

Куппа Вамси

Modular Cycle Systems

15

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

Modular Cycle Systems

76

Ким Раллс

Компания Associated Bikes

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

OrderID

Идентификатор сотрудника

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

Сотрудник

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

100314

230

445

Kuppa Vamsi

Modular Cycle Systems

100315

15

445

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

Modular Cycle Systems

100316

76

108

Ким Раллс

Компания Associated Bikes

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

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

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

= COUNTROWS (RELATEDTABLE (Реселлерсалес_усд))

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

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

Reseller

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

Modular Cycle Systems

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

SalesOrderNumber

445

SO53494

445

SO71872

445

SO65233

445

SO59000

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

SalesOrderNumber

Компания Associated Bikes

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

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

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

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

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

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

×