У цій статті розглянуто основи створення формул обчислення для обчислюваних стовпців і мір у надбудові Power Pivot. Якщо ви не користувалися DAX, обов'язково ознайомтеся з коротким посібником: вивчення основ мови DAX за 30 хвилин.
Основи формул
У надбудові Power Pivot передбачено вирази аналізу даних (DAX), які дають змогу виконувати настроювані обчислення в таблицях Power Pivot і в зведених таблицях Excel. DAX містить деякі функції, які використовуються у формулах Excel, а також додаткові функції, призначені для роботи з реляційними даними та динамічного агрегування.
Нижче наведено кілька основних формул, які можна використовувати в обчислюваному стовпці.
| Формула | Опис |
|---|---|
| =TODAY() | Вставляє в кожен рядок стовпця сьогоднішню дату. |
| =3 | Вставляє значення 3 в кожен рядок стовпця. |
| =[Стовпець1] + [Стовпець2] | Додає значення в тих самих рядках [Стовпець1] і [Стовпець2] і поміщає результати в той самий рядок обчислюваного стовпця. |
Формули Power Pivot для обчислюваних стовпців можна створювати так само, як і для формул у програмі Microsoft Excel.
Під час створення формули виконайте такі дії:
- Кожна формула має починатися зі знака рівності.
- Можна ввести або ім'я функції, або вираз.
- Почніть вводити кілька перших букв потрібної функції або імені, а функція автозаповнення відобразить список доступних функцій, таблиць і стовпців. Натисніть клавішу табуляції, щоб додати елемент зі списку автозавершення до формули.
- Натисніть кнопку Fx , щоб відобразити список доступних функцій. Щоб вибрати функцію з розкривного списку, виділіть її за допомогою клавіш зі стрілками та натисніть кнопку "OK ", щоб додати функцію до формули.
- Укажіть аргументи функції, вибравши їх із розкривного списку можливих таблиць і стовпців або ввівши значення чи іншу функцію.
- Перевірте наявність синтаксичних помилок: переконайтеся, що всі дужки закрито, а посилання на стовпці, таблиці та значення вказано правильно.
- Щоб прийняти формулу, натисніть клавішу ENTER.
Примітка.
Щойно ви приймете формулу в обчислюваному стовпці, стовпець заповниться значеннями. У мірі натискання клавіші ENTER зберігає визначення міри.
Створення простої формули
| Створення обчислюваного стовпця за допомогою простої формули Дата_продажуПідкатегоріяПродуктПродажіКількість1/5/2009АксесуариФутляр для транспортування254995681/5/2009АксесуариМіні-зарядний пристрій1099.56441/5/2009ЦифровийТонкий цифровий6512441/6/2009АксесуариОб'єктив для конвертації1662.5181/6/2009АксесуариШтатив938.34181/6/2009АксесуариUSB-кабель1230.2526
|
|---|
Поради з використання автозаповнення
- Функцію автозаповнення формул можна використовувати посередині наявної формули з вкладеними функціями. Текст безпосередньо перед місцем вставлення використовується для відображення значень у розкривному списку, а весь текст після місця вставлення залишається без змін.
- У надбудові Power Pivot дужки для функції не додаються та дужки автоматично не збігаються. Необхідно переконатися, що кожна функція синтаксично правильна, інакше не можна зберегти чи використати формулу. У надбудові Power Pivot виділяються дужки, що спрощує перевірку їх замикання.
Робота з таблицями та стовпцями
Таблиці Power Pivot схожі на таблиці Excel, але відрізняються принципом роботи з даними та формулами.
- Формули в надбудові Power Pivot працюють лише з таблицями та стовпцями, а не з окремими клітинками, посиланнями на діапазони або масивами.
- Формули можуть використовувати зв'язки, щоб отримувати значення з пов'язаних таблиць. Значення, які отримуються, завжди пов'язані з поточним значенням рядка.
- Формули Power Pivot не можна вставляти в аркуш Excel і навпаки.
- Тут не можна мати неправильні або рвані дані, як на аркуші Excel. Кожен рядок у таблиці має містити однакову кількість стовпців. Проте деякі стовпці можуть містити пусті значення. Таблиці даних Excel і Power Pivot не взаємозамінні, але можна зв'язати таблиці Excel із надбудови Power Pivot і вставити дані Excel у надбудову Power Pivot. Докладні відомості див. у статтях "Додавання даних аркуша до моделі даних за допомогою зв'язаної таблиці" та "Копіювання та вставлення рядків у модель даних у надбудові Power Pivot".
Посилання на таблиці та стовпці у формулах і виразах
Використовуючи ім'я будь-якої таблиці або стовпця, можна посилатися на нього. Наприклад, у наведеній нижче формулі показано, як посилатися на стовпці з двох таблиць за допомогою повного імені:
=SUM('Нові продажі'[Сума]) + SUM('Минулі продажі'[Сума])
Коли обчислюється формула, надбудова Power Pivot спочатку перевіряє загальний синтаксис, а потім порівнює назви вказаних стовпців і таблиць із можливими стовпцями й таблицями в поточному контексті. Якщо ім'я неоднозначне або стовпець чи таблицю не вдається знайти, формула матиме помилку (у клітинках, де сталася помилка, відображатиметься рядок #ERROR замість значення даних). Докладні відомості про вимоги до імен для таблиць, стовпців та інших об'єктів див. в розділі "Вимоги до іменування в специфікації синтаксису DAX для Power Pivot.
Примітка.
Контекст – це важлива функція моделей даних Power Pivot, яка дає змогу створювати динамічні формули. Контекст визначається таблицями в моделі даних, зв'язками між таблицями та застосованими фільтрами. Докладні відомості див. в статті "Контекст у формулах DAX".
Зв'язки між таблицями
Таблиці може бути пов'язано з іншими таблицями. Створюючи зв'язки, ви отримуєте можливість шукати дані в іншій таблиці та використовувати пов'язані значення для складних обчислень. Наприклад, за допомогою обчислюваного стовпця можна знайти всі записи доставки, пов'язані з поточним торговельним партнером, а потім підсумувати вартість доставки для кожного з них. Ефект подібний до параметризованого запиту: можна обчислити різну суму для кожного рядка поточної таблиці.
Для багатьох функцій DAX необхідний зв'язок між таблицями або між кількома таблицями, щоб знайти стовпці, на які ви посилаєтеся, і отримати логічні результати. Інші функції намагатимуться визначити зв'язок; Однак для досягнення найкращих результатів завжди слід створювати відносини, де це можливо.
Під час роботи зі зведеними таблицями особливо важливо зв'язати всі таблиці, які використовуються у зведеній таблиці, щоб можна було правильно обчислити зведені дані. Докладні відомості див. в статті "Робота зі зв'язками у зведених таблицях".
Виправлення помилок у формулах
Якщо під час визначення обчислюваного стовпця з'являється повідомлення про помилку, можливо, формула містить синтаксичну або семантичну помилку.
Синтаксичні помилки виправляються найпростіше. Зазвичай у них пропущені дужки або коми. Докладні відомості про синтаксис окремих функцій див. в довіднику з функцій DAX.
Помилка іншого типу виникає, коли синтаксис правильний, але значення або стовпець, на які посилається формула, не мають сенсу в контексті формули. Такі семантичні помилки можуть бути викликані будь-якою з наступних проблем:
- Формула посилається на ненаявний стовпець, таблицю або функцію.
- Формула здається правильною, але коли надбудова Power Pivot знаходить невідповідність типу та виникає помилка.
- Формула передає у функцію неправильний номер або тип параметрів.
- Формула посилається на інший стовпець, який містить помилку, тому його значення неприпустимі.
- Формула посилається на стовпець, який не оброблювався. Це може статися, якщо ви перевели книгу в ручний режим, внесли зміни, а потім не оновлювали дані й не оновлювали обчислення.
У перших чотирьох випадках формули DAX позначають увесь стовпець, який містить неприпустиму формулу. В останньому випадку формули DAX виділяють стовпець сірим кольором, щоб указати, що стовпець перебуває в необробленому стані.