Часто після створення зведеної таблиці виникає необхідність розширити аналіз та включити до нього більше даних/обчислень.
Якщо вам потрібний новий стовпець, який можна отримати за допомогою вже існуючих стовпців з даними в таблиці, вам не потрібно додавати його у вихідні дані. Для таких цілей ви можете створити поле, що обчислюється.
Що таке обчислюване поле у зведеній таблиці?
Почнемо з базового прикладу зведеної таблиці.
Допустимо, у нас є набір даних про роздрібні продажі, і ви створюєте зведену таблицю, як показано нижче:
А тепер, скажімо, ви хочете дізнатися, якою була маржа прибутку цих роздрібних торговців (де маржа прибутку — це «Прибуток», поділена на «Продажі»).
Можна зробити це по-різному:
- Повернутися до початкової таблиці та додати ці дані. Таким чином, ви можете вставити новий стовпець у вихідні дані та розрахувати в ньому маржу прибутку.
- Можна зробити так, але вам доведеться зробити все це вручну. Вам може знадобитися ще один стовпець для розрахунку середнього продажу на одиницю (Sales/Quantity).
- Істотний мінус такого методу — розширення таблиці, коли даних дуже багато може бути незручно.
- Ви можете додати обчислення поза зведеною таблицею, а в ній відобразити результат і робити розрахунки далі. Але в цьому способі існує ризик того, що якщо ви зміните вихідні дані, таблиця не зможе оновитися і видасть помилку.
- А ще є варіант створити поле в зведеній таблиці. На мій погляд, це найефективніший спосіб робити розрахунки. Обчислюване поле – це як віртуальний стовпець, ви бачите лише результат, а розрахунки виробляються “всередині”. Цей метод має багато переваг:
- Він не вимагає від вас роботи з формулами чи оновлення вихідних даних.
- Він масштабуємо, оскільки автоматично враховує всі нові дані, які ви додаватимете до зведеної таблиці.
- У ньому легко змінювати дані. Наприклад, вам потрібно змінити розрахунок, ви можете легко зробити це з самої зведеної таблиці.
Як додати поле, що обчислюється
Давайте подивимося, як додати обчислюване поле в існуючу зведену таблицю.
Допустимо, у нас є зведена таблиця, як на малюнку нижче, і ви хочете розрахувати маржу прибутку для кожного магазину продажів:
Як це зробити:
- Виберіть будь-яку комірку у зведеній таблиці;
- Перейдіть до розділу «Аналіз» -> «Поля, елементи та набори»;
- З списку, що випадає, виберіть «Обчислюване поле»;
- У вікні «Вставка поля, що обчислюється»:
- Введіть назву поля
- І напишіть формулу для нього. Ви можете вибирати з імен полів, наведених ліворуч. У разі формула буде '= Прибуток/Продажи'. Ви можете ввести імена полів вручну, або вибрати поле та натиснути «Додати поле».
- Натисніть кнопку «Додати» та підтвердіть.
Як тільки ви створите поле, що обчислюється, воно з'явиться в списку полів зведеної таблиці.
Тепер ви можете використовувати створене поле, що обчислюється.
Як я вже казав, перевага використання обчислюваного поля полягає в тому, що ви можете змінити структуру зведеної таблиці, і вона автоматично підлаштовується.
Наприклад, якщо я перенесу регіон у рядки, ви отримаєте результат, як показано нижче, де маржа прибутку автоматично розрахується як для магазинів, так і для регіонів.
У наведеному вище прикладі я використав просту формулу (Прибуток/Продажі) для поля, що обчислюється. Однак ви також можете використовувати деякі складніші формули.
Перш ніж я покажу вам приклад використання складних формул, ось деякі речі, які ви повинні знати:
- При створенні поля, що обчислюється, не можна використовувати посилання або діапазони елементів. Це виключає багато формул, таких як 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 автоматично створює новий робочий лист, в якому містяться відомості про всі обчислювані поля/елементи, які ви створили в цій зведеній таблиці.
Це може вам знадобиться, якщо, припустимо, ви отримали зведену таблицю від когось і потрібно швидко в ній розібратися.
