Связи и уточняющие запросы в формулах
Одной из наиболее мощных функций PowerPivot для Excel является возможность создания связей между таблицами и использования связанных таблиц для уточняющих запросов или фильтрации взаимосвязанных данных. Для получения связанных значений из таблиц используется язык формул DAX (выражения анализа данных), предоставляемый в PowerPivot для Excel. В языке DAX используется реляционная модель, что позволяет легко и точно получать связанные или соответствующие значения из другой таблицы или столбца.
Формулы, выполняющие уточняющие запросы, можно создавать в составе вычисляемых столбцов или в составе меры для использования в сводной таблице либо сводной диаграмме. Дополнительные сведения см. в разделах:
В этом разделе описываются функции языка DAX, которые поддерживаются для поиска, а также ряд примеров использования этих функций.
.gif "Примечание") Примечание |
|---|
В зависимости от типа используемой операции уточняющего запроса или формулы уточняющего запроса сначала может понадобиться создать связь между таблицами. Сведения о создании связей см. в разделе Связи между таблицами. |
Основные сведения о функциях поиска
Возможность поиска совпадающих или взаимосвязанных данных из другой таблицы особенно полезна в случаях, когда текущая таблица содержит только идентификатор некоторого типа, а необходимые данные (например, цена товара, наименование или другие необходимые конкретные значения) хранятся в связанной таблице. Это также полезно, если в другой таблице содержится несколько строк, связанных с текущей строкой или текущим значением. Например, можно легко получить все продажи, связанные с определенным регионом, магазином или менеджером по продажам.
В отличие от функций поиска Excel, например VLOOKUP, которые основаны на массивах, или LOOKUP, которые возвращают первое из нескольких совпадающих значений, DAX проходит по существующим связям между таблицами, соединенных по ключам, чтобы получить единственное связанное значение, обеспечивающее точное совпадение. DAX также возвращает таблицу записей, связанных с текущей записью.
| Примечание |
|---|
Пользователь, знакомый с реляционными базами данных, может представить уточняющие запросы в PowerPivot в виде вложенной инструкции SELECT в языке Transact-SQL. |
Дополнительные сведения о реляционной модели, используемой в PowerPivot, см. в разделе Общие сведения о связях.
Получение единственного связанного значения
Функция RELATED возвращает одно значение из другой таблицы, которое связано с текущим значением в текущей таблице. Пользователь задает столбец, содержащий нужные данные, а функция проходит по существующим связям между таблицами, чтобы выбрать значение из указанного столбца в связанной таблице. В некоторых случаях функции необходимо пройти по цепочке связей, чтобы получить данные.
Например, допустим, что список отгрузок на текущую дату содержится в Excel. Однако список, который содержит только идентификатор сотрудника, идентификатор заказа и идентификатор отправителя, делает отчет неудобным для чтения. Чтобы получить нужные дополнительные данные, можно преобразовать список в связанную таблицу PowerPivot, а затем создать связи с таблицами Employee и Reseller, сопоставив EmployeeID с полем EmployeeKey, а ResellerID с полем ResellerKey.
Чтобы отобразить данные уточняющих запросов в связанной таблице, добавляются два новых вычисляемых столбца со следующими формулами:
= RELATED('Employees'[EmployeeName])
= RELATED('Resellers'[CompanyName])
Перед поиском |
После поиска |
||||||||||||||||||||||||||||||||||||||||||||
|
|
Функция использует связи между связанной таблицей и таблицами Employees и Resellers для получения соответствующего имени для каждой строки в отчете. Связанные значения также можно использовать в вычислениях. Дополнительные сведения и примеры см. в разделе Функция RELATED.
Получение списка связанных значений
Функция RELATEDTABLE проходит по существующей связи и возвращает таблицу, содержащую все совпадающие строки из указанной таблицы. Например, пусть нужно выяснить, сколько заказов поместил в этом году каждый посредник. В таблице Resellers можно создать новый вычисляемый столбец, содержащий следующую формулу, которая находит записи для каждого посредника в таблице ResellerSales_USD и определяет количество отдельных заказов по каждому посреднику. Эти таблицы являются частью образца книги DAX. Дополнительные сведения об образцах данных см. в разделе Получение образца данных для PowerPivot.
=COUNTROWS(RELATEDTABLE(ResellerSales_USD))
В этой формуле функция RELATEDTABLE сначала возвращает значение ResellerKey для каждого посредника в текущей таблице. (Указывать столбец идентификаторов в формуле не нужно, так как PowerPivot использует существующую связь между таблицами.) Затем функция RELATEDTABLE возвращает все строки из таблицы ResellerSales_USD, которые связаны с каждым посредником, и определяет количество строк. Обратите внимание, что если связи (прямой или косвенной) между двумя таблицами нет, то из таблицы ResellerSales_USD будут получены все строки.
Для посредника Modular Cycle Systems в нашем образце базы данных имеется четыре заказа в таблице продаж, поэтому функция возвращает значение 4. Для посредника Associated Bikes продаж нет, поэтому функция возвращает пустое значение.
Reseller |
Записи в таблице продаж для этого посредника |
||||||||||
Modular Cycle Systems |
|
||||||||||
Associated Bikes |
|
| Примечание |
|---|
Поскольку функция RELATEDTABLE возвращает не единичное значение, а таблицу, она должна использоваться в качестве аргумента для функции, выполняющей операции с таблицами. Дополнительные сведения см. в разделе Функция RELATEDTABLE. |
См. также
Основные понятия
Добавление вычислений в отчеты, диаграммы и сводные таблицы
Создание формул для вычислений
Общие сведения по выражениям анализа данных (DAX)