Как да добавяте и използвате изчислени полета на обобщена таблица на Excel

Обикновено, след като създадете обобщена таблица, трябва да разширите анализа си и да включите повече данни/изчисления в нея.

Ако имате нужда от нова точка от данни, която може да бъде получена с помощта на съществуваща точка от данни в централната таблица, няма нужда да се връщате назад и да я добавяте към изходните данни.Вместо това можете да използватеИзчислени полета на обобщена таблицада го направя.

Какво е изчислено поле на обобщена таблица?

Нека започнем с основен пример за централна таблица.

Да предположим, че имате набор от данни за търговци на дребно и създавате въртяща таблица по следния начин:

Опорната таблица по-горе обобщава стойностите на продажбите и печалбата на търговеца на дребно.

Сега, какво ще стане, ако искате също да знаете какви са маржовете на печалба на тези търговци на дребно (маржът на печалбата е „печалба“, разделена на „продажби“)?

Има няколко начина да направите това:

1. Върнете се към оригиналния набор от данни и добавете тази нова точка от данни.Така че можете да вмъкнете нова колона в изходните данни и да изчислите маржа на печалбата в нея.След като това е направено, трябва да актуализирате изходните данни на централната таблица, за да получите тази нова колона като част от нея.
   • Докато този подход работи, трябва ръчно да се върнете към набора от данни и да извършите изчисленията.Например, може да искате да добавите друга колона, за да изчислите средните продажби на единица (продажби/количество).Отново трябва да добавите тази колона към изходните данни и след това да актуализирате централната таблица.
   • Този метод също така раздува вашата въртяща се таблица, докато добавяте нови данни към нея.
2. Добавете изчисления извън централните таблици.Изберете тази опция, ако структурата на вашата централна таблица е малко вероятно да се промени.Въпреки това, ако промените централната таблица, изчисленията може да не се актуализират съответно и да ви дадат неверни резултати или грешки.Както е показано по-долу, изчислих маржа на печалбата, когато има търговци на дребно в реда.Но когато го променя от клиент на област, формулата се обърка.
3. Изчислете полета с помощта на централни таблици.това еИзползвайте съществуващите данни от обобщена таблица и изчислетенай-ефективният начин.Мислете за изчислените полета като виртуални колони, които добавяте с помощта на съществуващи колони във вашата централна таблица.Използването на изчислени полета в централната таблица има много предимства (както ще видим по-късно):
   • Не е необходимо да работите с формули или да актуализирате изходните данни.
   • Той е разширяем, защото автоматично отчита всички нови данни, които можете да добавите към обобщената таблица.След като добавите изчислено поле, можете да го използвате като всяко друго поле в обобщена таблица.
   • Лесно се актуализира и управлява.Например, ако даден показател се промени или трябва да промените изчисление, можете лесно да го направите от самата обобщена таблица.

Добавете изчислени полета към обобщена таблица

Нека да видим как да добавим изчислено поле на обобщена таблица към съществуваща обобщена таблица.

Да предположим, че имате въртяща се таблица, която изглежда така и искате да изчислите маржа на печалба за всеки търговец на дребно:

Ето стъпките за добавяне на изчислено поле на обобщена таблица:

След добавяне на изчисленото поле, то ще се появи като едно от полетата в списъка с полета на обобщената таблица.

Вече можете да използвате това изчислено поле като всяко друго поле на обобщена таблица (обърнете внимание, че не можете да използвате изчислено поле на обобщена таблица като филтър за отчет или разделител).

Както споменах по-рано, ползата от използването на изчислено поле на въртяща се таблица е, че можете да промените структурата на въртящата се таблица и тя ще се коригира автоматично.

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

 

В примера по-горе използвах проста формула (=Печалба/Продажби), за да вмъкна изчисленото поле.Можете обаче да използвате и някои усъвършенствани формули.

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

• Не можете да използвате препратки или именувани диапазони, когато създавате изчислени полета на обобщена таблица.Това ще изключи много формули като VLOOKUP, INDEX, OFFSET и т.н.Въпреки това, можете да използвате формули, които работят без препратки (напр. SUM, IF, COUNT и т.н.).
• Можете да използвате константи във формули.Например, ако искате да знаете прогнозния ръст на продажбите, който се очаква да се увеличи с 10%, ще използвате формулата =Продажби*1.1 (където 1.1 е константа).
• Следвайте реда на приоритет във формулите, които генерират изчислени полета.Като най-добра практика използвайте скоби, за да сте сигурни, че не е нужно да помните приоритета.

Сега нека разгледаме пример за създаване на изчислено поле с помощта на разширена формула.

Да предположим, че имате набора от данни, показан по-долу, и трябва да покажете прогнозираните продажби в обобщена таблица.

За прогнозни стойности бихте използвали 300% увеличение на продажбите за големи търговци на дребно (над 5 милиона продажби) и 300% увеличение на продажбите за малки и средни търговци на дребно (по-малко от 10 милиона продажби).

Забележка: Данните за продажбите тук са фалшиви и са използвани за илюстриране на примерите в този урок.

Ето как да го направите:

Това ще добави нова колона към обобщената таблица със стойности на прогнозите за продажбите.

Проблем с изчисленото поле на обобщена таблица

Изчислените полета са невероятна функция, която наистина увеличава стойността на централните таблици с изчисления на полета, като същевременно поддържа всичко мащабируемо и управляемо.

Има обаче проблем с изчислените полета на обобщена таблица, които трябва да разберете, преди да ги използвате.

Да предположим, че имам централна таблица като по-долу и използвам изчислено поле, за да получа прогнозни числа за продажби.

Имайте предвид, че междинните и общите суми са неправилни.

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

Така че за South Total, докато стойността трябва да бъде 22,824,000 22,287,000 XNUMX, South Total неправилно я отчита като XNUMX XNUMX XNUMX.Това се случва, защото използва формулата 21,225,800*1.05, за да получи стойността.

За съжаление не можете да коригирате този проблем.

Най-добрият начин да се справите с това е да премахнете междинните и общите суми от обобщената таблица.

Можете също да минете Представено от ДебраНякои иновативни решения за справяне с този проблем.

Как да промените или изтриете изчислено поле на централна таблица?

След като създадете изчислено поле на обобщена таблица, можете да промените формулата или да я изтриете, като използвате следните стъпки:

Как да получите списък с всички изчислени формули на полета?

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

Excel ви позволява бързо да създадете списък с всички формули, използвани за създаване на изчислени полета.

Ето стъпките за бързо получаване на списък с всички изчислени формули на полето:

• Изберете произволна клетка в централната таблица.
• Отидете на Инструменти за обобщена таблица -> Анализ -> Полета, елементи и набори -> Списък с формули.

След като щракнете върху формулата на списъка, Excel автоматично ще вмъкне нов работен лист с подробности за всички изчислени полета/елементи, които сте използвали в обобщената таблица.

Това може да бъде много полезен инструмент, ако трябва да изпратите работа на клиент или да я споделите с екипа си.