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

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

OrderID

«Ідентифікатор працівника»

ResellerID

100314

230

445

100315

15

445

100316

76

108

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

«Ідентифікатор працівника»

Працівника

Торговельного партнера

230

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

Modular Cycle Systems

15

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

Modular Cycle Systems

76

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

Associated Bikes

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

OrderID

«Ідентифікатор працівника»

ResellerID

Працівника

Торговельного партнера

100314

230

445

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

Modular Cycle Systems

100315

15

445

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

Modular Cycle Systems

100316

76

108

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

Associated Bikes

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

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

За допомогою функції 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

Reseller ID

SalesOrderNumber

445

SO53494

445

SO71872

445

SO65233

445

SO59000

Reseller ID

SalesOrderNumber

Associated Bikes

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

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

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

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

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

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

×