Обчислення значень у зведеній таблиці

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

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

• Буде створено зведену таблицю й зведену діаграму, як на знімку екрана нижче. Якщо створити зведену діаграму на основі даних зі зведеної таблиці, значення в цій зведеній діаграмі відображатимуть обчислення у зв’язаній зведеній таблиці.

• У зведеній таблиці в полі стовпця Місяць містяться елементи Березень і Квітень. У полі рядка Регіон містяться елементи Північ, Південь, Схід і Захід. Значення на перетині стовпця Квітень і рядка Північ – це загальний дохід від збуту, визначений на основі вихідних даних, де в полі Місяць указано Квітень, а в полі Регіон – Північ.

• У зведеній діаграмі поле Регіон може бути полем категорій, у якому відображаються категорії Північ, Південь, Схід і Захід. Поле Місяць може бути полем рядів, у якому відображаються елементи Березень, Квітень і Травень як ряди з легенди. Поле значень з іменем Дохід від збуту може містити маркери даних, які представляють загальний дохід у кожному регіоні за кожний місяць. Наприклад, один маркер даних, розташований на вертикальній осі (ординат), представлятиме обсяги збуту за квітень у північному регіоні.

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

  Функція	Зведення
  Сума	Сума значень. Це стандартна функція для числових даних.
  Кількість	Кількість значень даних. Функція зведення "Кількість" працює так само, як функція COUNTA. "Кількість" – це стандартна функція для нечислових даних.
  Середнє	Середнє значення даних.
  Максимум	Найбільше значення.
  Мінімум	Найменше значення.
  Добуток	Добуток значень.
  Кількість чисел	Кількість числових значень. Функція зведення "Кількість чисел" працює так само, як функція COUNT.
  Зсунуте відхилення	Обчислення стандартного відхилення генеральної сукупності, у якому вибірка – підмножина всієї генеральної сукупності.
  Незсунуте відхилення	Стандартне відхилення сукупності, коли сукупність повністю складається з даних, які потрібно підсумувати.
  Зсунута дисперсія	Обчислення дисперсії генеральної сукупності, у якому вибірка – підмножина всієї генеральної сукупності.
  Незсунута дисперсія	Обчислення дисперсії генеральної сукупності, коли сукупність повністю складається з даних, які потрібно підсумувати.

• Спеціальні обчислення    настроюване обчислення показує значення на основі інших елементів або клітинок в області даних. Наприклад, ви можете відобразити значення в полі даних Сума продажу як відсоток від продажів за березень або як зростаючий підсумок елементів у полі Місяць.

  Для спеціальних обчислень у полях значень доступні наведені нижче функції.

  Функція	Результат
  Без обчислення	Відображає значення, введене в полі.
  Відсоток від загальної суми	Відображає значення як відсоток загального підсумка всіх значень або точок даних у звіті.
  Частка суми стовпців	Відображає всі значення в кожному стовпці або ряді як відсоток загального підсумка для стовпця або ряду.
  Частка суми рядків	Відображає значення в кожному рядку або категорії як відсоток загального підсумка для рядка або категорії.
  Частка	Відображає значення як відсоток значення базового елемента в базовому полі.
  % від підсумка за батьківським рядком	Обчислює значення в такий спосіб: (значення елемента) / (значення батьківського елемента в рядках)
  % від підсумка за батьківським стовпцем	Обчислює значення в такий спосіб: (значення елемента) / (значення батьківського елемента в стовпцях)
  % від батьківського підсумка	Обчислює значення в такий спосіб: (значення елемента) / (значення батьківського елемента вибраного базового поля)
  Відмінність	Відображає значення як відмінність відносно значення базового елемента в базовому полі.
  Зведена відмінність	Відображає значення як відсоткову різницю відносно значення базового елемента в базовому полі.
  Зі зростаючим підсумком у	Відображає значення зростаючого підсумка для послідовних елементів у базовому полі.
  % від зростаючого підсумка у	Обчислює значення зростаючого підсумка у відсотках для послідовних елементів у базовому полі.
  Сортування від найменшого до найбільшого	Відображає ранг вибраних значень у певному полі, де найменшому елементу в полі надається значення 1, а кожному більшому значенню – значення вищого рангу.
  Сортування від найбільшого до найменшого	Відображає ранг вибраних значень у певному полі, де найбільшому елементу в полі надається значення 1, а кожному меншому значенню – вищий ранг.
  Індекс	Обчислює значення в такий спосіб: ((значення в клітинці) x (загальна сума загальних підсумків)) / ((загальний підсумок рядка) x (загальний підсумок стовпця))

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

Наявність обчислень і можливостей у звіті залежить від того, чи отримано вихідні дані з бази даних OLAP або іншого джерела.

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

  Крім того, якщо сервер OLAP надає обчислювані поля, які називаються обчислюваними членами, вони відображатимуться в списку полів зведеної таблиці. Ви також бачитимете обчислювані поля й елементи, створені макросами, які написано на Visual Basic for Applications (VBA) і зберігаються у вашій книзі, але не зможете змінити їх. Якщо вам потрібні додаткові типи обчислень, зверніться до свого адміністратора бази даних OLAP.

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

• Обчислення на основі даних, отриманих не з OLAP.    У зведених таблицях на основі інших типів зовнішніх даних або даних з аркуша Excel обчислює поля значень із числовими даними за допомогою функції зведення "Сума" й поля даних із текстом за допомогою функції зведення "Кількість". Для подальшого аналізу й обробки даних можна залучити інші функції зведення, наприклад "Середнє", "Максимум" або "Мінімум". Ви також можете створювати власні формули, у яких використовуються елементи звіту або інші дані з аркуша, за допомогою обчислюваного поля або обчислюваного елемента в полі.

Формули можна створювати лише у звітах на основі даних, отриманих не з OLAP. Формули не можна додавати до звітів на основі баз даних OLAP. Використовуючи формули у зведених таблицях, необхідно дотримуватися наведених нижче правил синтаксису формул і поведінки формул.

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

• Імена полів і елементів    Excel використовує імена полів і елементів, щоб ідентифікувати ці елементи звіту у формулах. У наведеному нижче прикладі для даних у діапазоні C3:C9 використовується ім’я поля Молочні продукти. Для обчислюваного елемента в полі Тип, який оцінює збут для нового продукту на основі даних про збут молочних продуктів, можна використовувати формулу =Молочні продукти * 115%.

  

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

• Формули працюють із підсумковими значеннями, а не окремими записами    Формули для обчислюваних полів оперують сумами базових даних для полів у формулі. Наприклад, формула обчислюваного поля =Збут * 1,2 перемножує суму продажів для кожного типу та регіону на 1,2; вона не перемножує кожне окреме значення продажу на 1,2 з подальшим підсумовуванням цих добутків.

  Формули для обчислюваних елементів працюють з окремими записами. Наприклад, формула обчислюваного елемента =Молочні продукти * 115% перемножує кожне окреме значення продажів молочних продуктів на 115%, після чого отримані добутки підсумовуються в області "Значення".

• Пробіли, числа та символи в іменах    В іменах, які містять більше одного поля, порядок полів не має значення. У наведеному вище прикладі клітинки C6:D6 можуть називатися 'Квітень Північ' або 'Північ Квітень'. Імена, які містять кілька слів або числа чи символи, беріть в одинарні лапки.

• Підсумки    Формули не можуть посилатися на підсумки (у прикладі вище: Підсумок за березень, Підсумок за квітень і Загальний підсумок).

• Імена полів у посиланнях на елементи    Ім’я поля можна включити в посилання на елемент. Ім’я елемента має бути у квадратних дужках, наприклад Регіон[Північ]. Використовуйте цей формат, щоб уникнути помилки #NAME?, коли два елементи у двох різних полях звіту мають однакові імена. Наприклад, якщо звіт містить елемент "М’ясо" як в полі "Тип", так і в полі "Категорія", помилки #NAME? можна уникнути за допомогою посилань Тип[М’ясо] та Категорія[М’ясо].

• Посилання на елементи за позицією    Ви можете посилатися на елемент за його позицією у звіті з поточним сортуванням і відображенням. Тип[1] – це Молочні продукти, а Тип[2] – це Морепродукти. Елемент, на який зроблено посилання таким чином, може змінитися, наприклад, коли зміняться позиції елементів або буде показано чи приховано різні елементи. Приховані елементи не враховуються в цьому індексі.

  Для посилання на елементи можна використовувати відносні позиції. Позиції визначаються відносно обчислюваного елемента, який містить формулу. Якщо поточний регіон Південь, то Регіон[–1] – це Північ; якщо поточний регіон Північ, то Регіон[+1] – це Південь. Наприклад, для обчислюваного елемента можна використовувати формулу =Регіон[–1] * 3%. Якщо вказана позиція розташована в полі перед першим елементом або після останнього, формула поверне помилку #REF!.

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

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

Щоб з’ясувати, як позначиться на діаграмі збільшення збуту на 10 відсотків, можна створити обчислюване поле у зв’язаній зведеній таблиці з такою формулою, як =Збут * 110%.

Результат одразу відображається у зведеній діаграмі, як показано на знімку екрана нижче.

Щоб переглянути окремий маркер даних для продажів у північному регіоні без транспортних витрат, які складають 8 відсотків, можна створити обчислюваний елемент у полі "Регіон" із такою формулою, як = Північ – (Північ * 8%).

Отримана діаграма матиме такий вигляд:

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