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

Зміст

Що таке обчислюване поле зведеної таблиці?
Додавання обчислюваного поля до зведеної таблиці
Проблема з обчисленими полями зведеної таблиці
Як змінити або видалити розрахункове поле зведеної таблиці?
Як отримати список усіх обчислених формул поля?

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

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

Завантажте набір даних і продовжуйте.

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

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

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

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

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

Є кілька способів зробити це:

Поверніться до вихідного набору даних і додайте цю нову точку даних. Таким чином, ви можете вставити новий стовпець у вихідні дані і розрахувати в ньому маржу прибутку. Після цього вам потрібно оновити вихідні дані зведеної таблиці, щоб отримати цей новий стовпець як його частину.
- Хоча цей метод є можливим, вам доведеться вручну повернутися до набору даних і провести розрахунки. Наприклад, вам може знадобитися додати інший стовпець для розрахунку середнього обсягу продажу за одиницю (Продажі/Кількість). Знову вам доведеться додати цей стовпець до вихідних даних, а потім оновити зведену таблицю.
- Цей метод також збільшує вашу зведену таблицю, коли ви додаєте до неї нові дані.
Додайте обчислення поза зведеною таблицею. Це може бути варіантом, якщо структура зведеної таблиці навряд чи зміниться. Але якщо ви зміните зведену таблицю, розрахунок може не оновлюватися відповідно і може дати вам неправильні результати або помилки. Як показано нижче, я розрахував маржу прибутку, коли в ряду були роздрібні торговці. Але коли я змінив її з клієнтів на регіони, формула дала помилку.
Використання поля обчислення зведеної таблиці. Це найефективніший спосіб використовувати наявні дані зведеної таблиці та обчислити потрібну метрику. Розгляньте обчислюване поле як віртуальний стовпець, який ви додали за допомогою наявних стовпців зведеної таблиці. Використання обчислюваного поля зведеної таблиці (як ми побачимо за хвилину) має багато переваг:
- Для цього не потрібно обробляти формули або оновлювати вихідні дані.
- Він масштабований, оскільки автоматично враховуватиме нові дані, які ви можете додати до зведеної таблиці. Додавши поле обчислення, ви можете використовувати його, як і будь -яке інше поле у зведеній таблиці.
- Його легко оновлювати та керувати ним. Наприклад, якщо показники змінюються або вам потрібно змінити обчислення, ви можете легко це зробити із самої зведеної таблиці.

Додавання обчислюваного поля до зведеної таблиці

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

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

Нижче описано, як додати обчислюване поле зведеної таблиці:

Виберіть будь -яку клітинку у зведеній таблиці.
Перейдіть до Інструменти зведеної таблиці -> Аналіз -> Розрахунки -> Поля, елементи та набори.
У спадному меню виберіть Обчислюване поле.
У діалоговому вікні Вставити обчислені файли:
- Дайте йому ім’я, ввівши його у поле Ім'я.
- У полі Формула створіть потрібну формулу для обчислюваного поля. Зауважте, що ви можете вибирати з імен полів, зазначених під ним. У цьому випадку формула виглядає як "= Прибуток/ Продажі". Ви можете або вручну ввести назви полів, або двічі клацнути ім’я поля, зазначене у полі Поля.
Натисніть Додати і закрийте діалогове вікно.

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

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

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

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

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

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

Ви НЕ МОЖЕТЕ використовувати посилання або іменовані діапазони під час створення обчислюваного поля зведеної таблиці. Це виключає багато формул, таких як VLOOKUP, INDEX, OFFSET тощо. Однак ви можете використовувати формули, які можуть працювати без посилань (наприклад, SUM, IF, COUNT тощо).
У формулі можна використовувати константу. Наприклад, якщо ви хочете знати прогнозовані продажі, де прогнозується зростання на 10%, ви можете скористатися формулою = Продажі*1,1 (де 1,1 є постійним).
Порядок пріоритету дотримується у формулі, яка складає обчислюване поле. Найкраще застосовувати дужки, щоб переконатися, що вам не потрібно пам’ятати порядок пріоритету.

Тепер давайте розглянемо приклад використання розширеної формули для створення обчислюваного поля.

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

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

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

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

Виберіть будь -яку клітинку у зведеній таблиці.
Перейдіть до Інструменти зведеної таблиці -> Аналіз -> Розрахунки -> Поля, елементи та набори.
У спадному меню виберіть Обчислюване поле.
У діалоговому вікні Вставити обчислені файли:
- Дайте йому ім’я, ввівши його у поле Ім'я.
- У полі Формула використовуйте таку формулу: = IF (Регіон = "Південь", Продажі *1,05, Продажі *1,1)
Натисніть Додати і закрийте діалогове вікно.

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

Натисніть тут, щоб завантажити набір даних.

Проблема з обчисленими полями зведеної таблиці

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

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

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

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

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

Тож для South Total, хоча значення має бути 22,824,000, South Total помилково повідомляє про це як 22,287,000. Це відбувається, коли він використовує формулу 21,225,800*1,05, щоб отримати значення.

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

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

Ви також можете ознайомитися з деякими інноваційними способами вирішення, які показала Дебра для вирішення цієї проблеми.

Як змінити або видалити розрахункове поле зведеної таблиці?

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

Виберіть будь -яку клітинку у зведеній таблиці.
Перейдіть до Інструменти зведеної таблиці -> Аналіз -> Розрахунки -> Поля, елементи та набори.
У спадному меню виберіть Обчислюване поле.
У полі «Ім'я» натисніть стрілку спадного меню (маленька стрілка вниз у кінці поля).
У списку виберіть обчислюване поле, яке потрібно видалити або змінити.
Змініть формулу, якщо ви хочете її змінити, або натисніть Видалити, якщо ви хочете її видалити.

Як отримати список усіх обчислених формул поля?

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

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

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

Виберіть будь -яку клітинку у зведеній таблиці.
Перейдіть до Інструменти зведеної таблиці -> Аналіз -> Поля, елементи та набори -> Формули списку.

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