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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

Одна з найпотужніших функцій Power Pivot – це можливість створювати зв’язки між таблицями, а потім використовувати зв’язані таблиці для підстановки, або щоб фільтрувати зв’язані дані. Щоб отримувати зв’язані значення з таблиць, використовується мова формул, що входить до Power Pivot, – вирази аналізу даних (DAX). У DAX використовується реляційна модель, тому можна легко й точно отримувати зв’язані або відповідні значення в іншій таблиці чи іншому стовпці. Тим, хто знайомий із функцією VLOОKUP у програмі Excel, ці можливості Power Pivot здадуться схожими на неї, але простішими в застосуванні.

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

Обчислювані поля в надбудові Power Pivot

Обчислювані стовпці в надбудові Power Pivot

У цьому розділі описані функції DAX для підстановок із кількома прикладами щодо використання цих функцій.

Примітка : Залежно від типу використовуваної операції підстановки або формули підстановки, можливо, спочатку необхідно буде створити зв'язок між таблицями.

Докладні відомості про функції підстановки

Можливість підстановки відповідних або пов'язаних даних з іншої таблиці особливо корисна в ситуаціях, коли поточна таблиця містить лише певний ідентифікатор, а необхідні дані (наприклад, ціна продукту, назва чи інші значення) зберігаються у пов'язаній таблиці. Це також корисно, коли є кілька рядків у іншій таблиці, які пов'язані з поточним рядком чи поточним значенням. Наприклад, можна легко отримати всі продажі, прив'язані до певного регіону, магазина чи продавця.

На відміну від функцій підстановки Excel, таких як VLOОKUP, що засновані на масивах, чи LOОKUP, яка повертає перше з кількох відповідних значень, мова DAX працює відповідно до пов'язаних ключами наявних зв'язків між таблицями, і повертає одне пов'язане значення, яке найкраще відповідає пошуку. За допомогою мови DAX можна також отримати таблицю записів, пов'язаних із поточним записом.

Примітка : Тим, хто знайомий із реляційними базами даних, підстановки в Power Pivot здаватимуться схожими на вкладені інструкції вибору в Transact-SQL.

Отримання одного пов'язаного значення

Функція RELATED повертає одне значення з іншої таблиці, пов'язане з поточним значенням у поточній таблиці. Ви вказуєте стовпець, що містить необхідні дані, а функція відповідно до зв'язків між таблицями шукає значення в зазначеному стовпці у зв'язаній таблиці. У деяких випадках функція має відстежити ланцюжок зв'язків, щоб отримати дані.

Скажімо, є список сьогоднішніх поставок у книзі Excel. Проте він містить лише ідентифікаційний номер працівника, ідентифікаційний номер замовлення та ідентифікаційний номер поставки, тому звіт складно читати. Щоб отримати додаткову необхідну інформацію, можна перетворити список на зв’язану таблицю Power Pivot, а потім створити зв’язки з таблицями Employee і Reseller, зіставивши поле EmployeeID з полем EmployeeKey, а поле ResellerID – з полем ResellerKey.

Щоб відобразити інформацію підстановки у зв'язаній таблиці, необхідно додати два нових обчислюваних стовпця з такими формулами:

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

Сьогодні відправлення до підстановки

Ідентифікатор замовлення

EmployeeID

ResellerID

100314

230

445

100315

15

445

100316

76

108

Таблиця працівників

EmployeeID

Працівник

ТорговельнийПартнер

230

Олександр Щепка

Modular Cycle Systems

15

Володимир Чук

Modular Cycle Systems

76

Оксана Колодяжна

Associated Bikes

Сьогодні відправлення шаблону

Ідентифікатор замовлення

EmployeeID

ResellerID

Працівник

ТорговельнийПартнер

100314

230

445

Олександр Щепка

Modular Cycle Systems

100315

15

445

Володимир Чук

Modular Cycle Systems

100316

76

108

Оксана Колодяжна

Associated Bikes

Функція використовує зв'язки між зв'язаною таблицею та таблицями Employees і Resellers, щоб отримати правильну назву для кожного рядка у звіті. Пов'язані значення можна також використовувати в розрахунках. Докладні відомості та приклади див. у статті Функція RELATED.

Отримання списку пов'язаних значень

Функція RELATEDTABLE відповідно до наявного зв'язку повертає таблицю, яка містить усі відповідні рядки з вибраної таблиці. Наприклад, вам потрібно дізнатися, скільки замовлень зробив кожний торговельний партнер цього року. Можна створити новий обчислюваний стовпець у таблиці Resellers, що міститиме наступну формулу, яка шукатиме записи для кожного торговельного посередника в таблиці 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

Reseller ID

SalesOrderNumber

445

SO53494

445

SO71872

445

SO65233

445

SO59000

Reseller ID

SalesOrderNumber

Associated Bikes

Примітка : Оскільки функція RELATEDTABLE повертає таблицю, а не окреме значення, її необхідно використовувати як аргумент до функції, яка виконує операції над таблицями. Докладні відомості див. у статті Функція RELATEDTABLE.

На початок сторінки

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×