Як додати та використовувати обчислюване поле у зведеній таблиці

Часто після створення зведеної таблиці виникає необхідність розширити аналіз та включити до нього більше даних/обчислень.

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

Що таке обчислюване поле у зведеній таблиці?

Почнемо з базового прикладу зведеної таблиці.

Допустимо, у нас є набір даних про роздрібні продажі, і ви створюєте зведену таблицю, як показано нижче:

Як додати та використовувати обчислюване поле у зведеній таблиці

А тепер, скажімо, ви хочете дізнатися, якою була маржа прибутку цих роздрібних торговців (де маржа прибутку — це «Прибуток», поділена на «Продажі»).

Можна зробити це по-різному:

  1. Повернутися до початкової таблиці та додати ці дані. Таким чином, ви можете вставити новий стовпець у вихідні дані та розрахувати в ньому маржу прибутку.
    • Можна зробити так, але вам доведеться зробити все це вручну. Вам може знадобитися ще один стовпець для розрахунку середнього продажу на одиницю (Sales/Quantity).
    • Істотний мінус такого методу — розширення таблиці, коли даних дуже багато може бути незручно.
  2. Ви можете додати обчислення поза зведеною таблицею, а в ній відобразити результат і робити розрахунки далі. Але в цьому способі існує ризик того, що якщо ви зміните вихідні дані, таблиця не зможе оновитися і видасть помилку.
  3. А ще є варіант створити поле в зведеній таблиці. На мій погляд, це найефективніший спосіб робити розрахунки. Обчислюване поле – це як віртуальний стовпець, ви бачите лише результат, а розрахунки виробляються “всередині”. Цей метод має багато переваг:
    • Він не вимагає від вас роботи з формулами чи оновлення вихідних даних.
    • Він масштабуємо, оскільки автоматично враховує всі нові дані, які ви додаватимете до зведеної таблиці.
    • У ньому легко змінювати дані. Наприклад, вам потрібно змінити розрахунок, ви можете легко зробити це з самої зведеної таблиці.

Як додати поле, що обчислюється

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

Допустимо, у нас є зведена таблиця, як на малюнку нижче, і ви хочете розрахувати маржу прибутку для кожного магазину продажів:

Як додати та використовувати обчислюване поле у зведеній таблиці

Як це зробити:

  • Виберіть будь-яку комірку у зведеній таблиці;
  • Перейдіть до розділу «Аналіз» -> «Поля, елементи та набори»;

Як додати та використовувати обчислюване поле у зведеній таблиці

  • З списку, що випадає, виберіть «Обчислюване поле»;

Як додати та використовувати обчислюване поле у зведеній таблиці

  • У вікні «Вставка поля, що обчислюється»:
    • Введіть назву поля
    • І напишіть формулу для нього. Ви можете вибирати з імен полів, наведених ліворуч. У разі формула буде '= Прибуток/Продажи'. Ви можете ввести імена полів вручну, або вибрати поле та натиснути «Додати поле».

Як додати та використовувати обчислюване поле у зведеній таблиці

  • Натисніть кнопку «Додати» та підтвердіть.

Як тільки ви створите поле, що обчислюється, воно з'явиться в списку полів зведеної таблиці.

Тепер ви можете використовувати створене поле, що обчислюється.

Як додати та використовувати обчислюване поле у зведеній таблиці

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

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

Як додати та використовувати обчислюване поле у зведеній таблиці

У наведеному вище прикладі я використав просту формулу (Прибуток/Продажі) для поля, що обчислюється. Однак ви також можете використовувати деякі складніші формули.

Перш ніж я покажу вам приклад використання складних формул, ось деякі речі, які ви повинні знати:

  • При створенні поля, що обчислюється, не можна використовувати посилання або діапазони елементів. Це виключає багато формул, таких як VLOOKUP, INDEX, OFFSET і так далі. Однак, ви можете використовувати формули, які можуть працювати без посилань (наприклад, SUM, IF, COUNT тощо).
  • У формулі можна використати константу. Наприклад, якщо ви хочете дізнатися про прогнозований обсяг продажів, де прогнозується його зростання на 10%, ви можете використовувати формулу = Sales*1.1 (де 1.1 — константа).
  • У формулі, що становить поле, що обчислюється, дотримується порядок математики, при проведенні розрахунків (перша дія буде в дужках, потім множення / поділ і так далі).

Тепер розглянемо приклад використання більш складних формул.

Припустимо, нам необхідно розрахувати прогнозоване значення продажів.

Для прогнозованого значення вам потрібно використовувати 5% зростання продажів для великих роздрібних магазинів (у яких продажу понад 3 мільйони) і 10% зростання продажів для дрібних і середніх роздрібних магазинів (продажу менше 3 мільйонів).

Ось як це зробити:

  • Виберіть будь-яку комірку у зведеній таблиці;
  • Перейдіть до розділу «Аналіз» -> «Поля, елементи та набори»;

Як додати та використовувати обчислюване поле у зведеній таблиці

  • З списку, що випадає, виберіть Обчислюване поле;

Як додати та використовувати обчислюване поле у зведеній таблиці

  • У діалоговому вікні «Вставка поля, що обчислюється»:
    • Введіть назву.
    • І наберіть таку формулу: =ЯКЩО(Sales > 3000000,Sales *1.05,Sales *1.1);

Як додати та використовувати обчислюване поле у зведеній таблиці

  • Натисніть кнопку «Додати» та підтвердіть.

Це додасть новий стовпчик із прогнозованим значенням продажів.

Як додати та використовувати обчислюване поле у зведеній таблиці

Проблема з полем, що обчислюється

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

Однак вона не ідеальна і з нею бувають проблеми.

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

Як додати та використовувати обчислюване поле у зведеній таблиці

Зверніть увагу, що проміжні та загальні результати неправильні.

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

Так, для South Total значення має бути 22824000, але в South Total коштує цифра 22287000. Це сталося тому, що для отримання результату використовується формула 21,225,800 * 1.05.

Як додати та використовувати обчислюване поле у зведеній таблиці

На жаль, виправити це неможливо.

Найкращим вирішенням цієї проблеми буде видалення проміжних підсумків з таблиці.

Як змінити або видалити обчислюване поле?

На стадії, коли ми вже створили поле, ви можете змінювати формулу або взагалі видалити її, таким чином:

  • Виберіть будь-яку комірку в таблиці Pivot Table;
  • Перейдіть до розділу «Аналіз» -> «Поля, елементи та набори»;
  • У списку, що розкривається, виберіть Обчислюване поле;
  • У полі Ім'я натисніть на стрілку, що випадає (маленька стрілка вниз);
  • Зі списку виберіть поле, яке ви бажаєте видалити або змінити;

Як додати та використовувати обчислюване поле у зведеній таблиці

  • Якщо ви хочете змінити формулу, спочатку поміняйте її, а потім натисніть кнопку “Змінити” або натисніть “Видалити”, якщо ви хочете видалити її.

Як додати та використовувати обчислюване поле у зведеній таблиці

Як переглянути всі формули використані в полі, що обчислюється?

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

Excel дозволяє швидко відкрити список всіх формул, що використовуються у таблиці.

Як це зробити:

  • Виберіть будь-яку комірку у зведеній таблиці;
  • Перейдіть до розділу «Аналіз» -> «Поля, елементи та набори»;
  • Натисніть кнопку «Вивести формули».

Як додати та використовувати обчислюване поле у зведеній таблиці

Як тільки ви натискаєте на «Вивести формули», Excel автоматично створює новий робочий лист, в якому містяться відомості про всі обчислювані поля/елементи, які ви створили в цій зведеній таблиці.

Як додати та використовувати обчислюване поле у зведеній таблиці

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

nBook
Додати коментар