Створення формул для обчислень у надбудові Power Pivot

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

У цій статті, ми розглянемо основи створення формул обчислення для обох обчислювані стовпці та міри в Power Pivot. Якщо ви не знайомі з DAX, не забудьте перевірити швидкий запуск: Дізнайтеся основ мови DAX за 30 хвилин.

Основи формул

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

Нижче наведено кілька основних формул, які можна використовувати в обчислюваному стовпці.

Формула

Опис

=TODAY()

Вставляє сьогоднішню дату в кожний рядок стовпця.

=3

Вставляє значення 3 в кожний рядок стовпця.

=[Column1] + [Column2]

Додає значення в той же самий рядок стовпців [Стовпець1] та [Стовпець2] та розміщує результати в тому ж рядку обчислюваного стовпця.

Формули Power Pivot для обчислюваних стовпців можна створювати майже так само, як і формули в програмі Microsoft Excel.

Під час створення формули дотримуйтеся таких вимог.

  • Кожна формула повинна починатися знаком рівності.

  • Можна ввести чи вибрати ім’я функції, або ж ввести вираз.

  • Почніть вводити перші кілька літер потрібної функції чи імені, а функція автозаповнення відобразить список доступних функцій, таблиць та стовпців. Натисніть клавішу TAB, щоб додати до формули елемент зі списку автозаповнення.

  • Натисніть кнопку Fx, щоб відобразити список доступних функцій. Для вибору функції з розкривного списку використовуйте клавіші зі стрілками, щоб вибрати елемент, а потім натисніть кнопку ОК, щоб додати функцію до формули.

  • Щоб додати аргументи до функції, виберіть їх з розкривного списку можливих таблиць та стовпців або ж введіть значення чи іншу функцію.

  • Перевірте наявність помилок синтаксису: переконайтеся, що всі дужки закрито, а стовпці, таблиці та значення посилаються правильно.

  • Щоб прийняти формулу, натисніть клавішу ENTER.

Примітка : В обчислюваному стовпці щойно ви прийняти формулу, стовпець буде заповнено значень. У міру після натискання клавіші ENTER зберігає визначення елемента.

Створення простої формули

Створення обчислюваного стовпця з простою формулою

Дата продажу

Підкатегорія

Продукт

Збут

Кількість

05.01.2009

Аксесуари

Чохол

254995

68

05.01.2009

Аксесуари

Міні зарядний пристрій

1099,56

44

05.01.2009

Цифрові

Тонкі цифрові

6512

44

06.01.2009

Аксесуари

Телеоб’єктиви

1662,5

18

06.01.2009

Аксесуари

Штатив

938,34

18

06.01.2009

Аксесуари

USB-кабель

1230,25

26

  1. Виберіть та скопіюйте дані з таблиці вище включно з заголовками таблиці.

  2. У надбудові Power Pivot на вкладці Головна виберіть команду Вставити.

  3. Натисніть кнопку OK у діалоговому вікні Попередній перегляд вставлення.

  4. Перейдіть на вкладку Конструктор> стовпці> Додати.

  5. Введіть наведену нижче формулу в рядок формул над таблицею.

    = [Обсяг продажів] / [Кількість]

  6. Щоб прийняти формулу, натисніть клавішу ENTER.

Усі рядки нового обчислюваного стовпця заповняться значеннями.

Поради щодо використання функції автозаповнення

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

  • Надбудова Power Pivot не додає до функцій закриту дужку та не забезпечує однакову кількість відкритих і закритих дужок автоматично. Необхідно переконатися, що в жодній функції немає синтаксичних помилок, тому що інакше формулу не можна буде зберегти та використати. Дужки в надбудові Power Pivot виділяються, завдяки чому легше визначити, чи закрито їх належним чином.

Робота з таблицями та стовпцями

Таблиці Power Pivot схожі на таблиці Excel, але інакше працюють із даними та формулами.

  • Формули в надбудові Power Pivot працюють лише з таблицями та стовпцями, а не з окремими клітинками, посиланнями на діапазони або масивами.

  • Формули можуть використовувати зв’язки для отримання значень з пов’язаних таблиць. Значення, що отримуються, завжди пов’язані з поточним значенням рядка.

  • Формули Power Pivot не можна вставляти на аркуш Excel та навпаки.

  • На відміну від аркуша Excel, нерегулярні або "уривчасті" дані неприпустимі. Кожний рядок у таблиці має містити однакову кількість стовпців. Проте деякі стовпці можуть мати пусті значення. Таблиці даних Excel і таблиці даних Power Pivot не взаємозамінні, але можна з надбудови Power Pivot встановити зв’язок із таблицями Excel і вставити дані Excel до Power Pivot. Докладні відомості див. в статтях Додавання даних аркуша до моделі даних за допомогою зв’язаної таблиці та  Копіювання та вставлення рядків у модель даних у надбудові Power Pivot.

Посилання на таблиці та стовпці в формулах та виразах

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

= SUM ("новий Sales'[Amount]) + SUM('Past Sales'[Amount])

Коли обчислюється формула, надбудова Power Pivot спершу перевіряє загальний синтаксис, а потім – надані імена стовпців і таблиць у поточному контексті. Якщо ім’я неоднозначне, або не вдалося знайти стовпець чи таблицю, формула поверне помилку (рядок "#ERROR" замість значення даних у клітинках, де сталася помилка). Докладні відомості про вимоги до імен для таблиць, стовпців та інших об’єктів див. в розділі "Вимоги до імен" у статті Специфікація синтаксису DAX для Power Pivot (DAX).

Примітка : Контекст – це важлива особливість моделей даних Power Pivot, яка дає змогу створювати динамічні формули. Контекст визначають таблиці в моделі даних, зв’язки між таблицями й усі застосовані фільтри. Докладні відомості див. в статті Контекст у формулах DAX.

Зв’язки таблиць

Таблиці можна пов’язати з іншими таблицями. За допомогою створення зв’язків з’являється можливість шукати дані в іншій таблиці та використовувати пов’язані значення для виконання складних обчислень. Наприклад, можна використовувати обчислюваний стовпець для пошуку всіх записів доставки, пов’язаних з поточним торговельним партнером, а потім підрахувати суму вартості доставки для кожного. Ефект схожий на параметризований запит: можна обчислити різну суму для кожного рядка у поточній таблиці.

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

Під час роботи зі зведеними таблицями особливо важливо підключити всі таблиці, які використовуються у зведеній таблиці, щоб можна було правильно обчислити зведені дані. Докладніше див. у статті Робота зі зв’язками у зведених таблицях.

Виправлення помилок у формулах

Якщо під час визначення обчислюваного стовпця виникає помилка, формула може містити синтаксичну помилку чи семантичну помилку.

Синтаксичні помилки виправити простіше за все. Зазвичай вони стосуються відсутньої дужки чи коми. Довідку із синтаксису окремих функцій див. за посиланням Довідкові матеріали з функцій DAX.

Інший тип помилок виникає, коли синтаксис правильний, але значення стовпця, на який наводиться посилання, не має сенсу в контексті формули. Такі семантичні помилки можуть виникати у зв’язку з будь-якою з наведених нижче проблем:

  • Формула посилається на відсутній стовпець, таблицю чи функцію.

  • Формула виглядає правильно, але коли надбудова Power Pivot отримує дані, виявляється невідповідність типу й виникає помилка.

  • Формула містить неправильну кількість або тип параметрів функції.

  • Формула посилається на інший стовпець, який містить помилку, і тому її значення неприпустиме.

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

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

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

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

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

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

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

×