✅ Как да добавите и използвате изчислено поле на обобщена таблица на 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, за да получи стойността.

За съжаление няма как да поправите това.

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

Можете също така да преминете през някои иновативни решения, които Debra показа, че се справя с този проблем.

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

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

Изберете всяка клетка в обобщената таблица.
Отидете на Инструменти за обобщена таблица -> Анализ -> Изчисления -> Полета, елементи и набори.
От падащото меню изберете Изчислено поле.
В полето Име щракнете върху падащата стрелка (малка стрелка надолу в края на полето).
От списъка изберете изчисленото поле, което искате да изтриете или промените.
Променете формулата в случай, че искате да я промените, или щракнете върху Изтрий в случай, че искате да я изтриете.

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

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

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

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

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

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