У цій статті ми розглянемо основи створення формул обчислень як для обчислюваних стовпців , так і для мір у Power Pivot. Якщо ви ще не маєте доступу до DAX, обов'язково перегляньте короткий посібник. Дізнайтеся про основи DAX за 30 хвилин.
Основи формул
Power Pivot надає вирази аналізу даних (DAX) для створення настроюваних обчислень у Power Pivot таблицях і зведених таблицях Excel. DaX містить деякі функції, які використовуються у формулах Excel, а також додаткові функції, призначені для роботи з реляційними даними та динамічного агрегації.
Нижче наведено кілька основних формул, які можна використовувати в обчислюваному стовпці.
|
Формула |
Опис |
|
Вставляє сьогоднішню дату в кожен рядок стовпця. |
|
Вставляє значення 3 в кожному рядку стовпця. |
|
Додає значення в тому самому рядку [Стовпець1] і [Стовпець2] і ставить результати в тому ж рядку обчислюваного стовпця. |
Формули для обчислюваних стовпців можна створювати Power Pivot так само, як у програмі Microsoft Excel.
Створюючи формулу, виконайте такі дії:
-
Кожна формула має починатися зі знака рівності.
-
Можна ввести або вибрати ім'я функції або ввести вираз.
-
Почніть вводити кілька перших букв потрібної функції або імені, а функція автозаповнення відображає список доступних функцій, таблиць і стовпців. Натисніть клавішу Tab, щоб додати елемент зі списку автозаповнення до формули.
-
Натисніть кнопку Fx , щоб відобразити список доступних функцій. Щоб вибрати функцію з розкривного списку, виділіть елемент за допомогою клавіш зі стрілками, а потім натисніть кнопку OK , щоб додати функцію до формули.
-
Укажіть аргументи функції, вибравши їх із розкривного списку можливих таблиць і стовпців або ввівши значення або іншу функцію.
-
Перевірте наявність синтаксичних помилок: переконайтеся, що всі дужки закрито, а стовпці, таблиці та значення мають правильне посилання.
-
Натисніть клавішу Enter, щоб прийняти формулу.
Примітка.: В обчислюваному стовпці, щойно ви приймете формулу, стовпець заповниться значеннями. Якщо ввести міру, натискання клавіші Enter збереже визначення міри.
Створення просту формулу
|
Створення обчислюваного стовпця за допомогою простої формули
Потім значення заповнюються в новому обчислюваному стовпці для всіх рядків. |
Поради з використання автозаповнення
-
Автозаповнення формул можна використовувати посередині наявної формули з вкладеними функціями. Текст безпосередньо перед точкою вставлення використовується для відображення значень у розкривному списку, а весь текст після місця вставлення залишається незмінним.
-
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 затіяє стовпець, щоб позначити, що стовпець перебуває в необробленому стані.
