Формула на оборотна таблица в Excel (Съдържание)
- Формула на оборотна таблица в Excel
- Персонализирано поле за изчисляване на сумата на печалбата
- Разширена формула в изчислено поле
Формула на оборотна таблица в Excel
Pivot Table е инструмент, който ни позволява да анализираме големи диапазони от данни. Можем да анализираме, интерпретираме и правим много други неща, без да си счупваме голяма част от главата и потта. Той може да даде почти всичко, което има в изходните данни.
Ако част от информацията има в изходните данни, може да се наложи да изчислим това за себе си. Например, ако имаме обща сума на продажбите и обща стойност, може да се наложи да изчислим общата печалба или загуба сами.
Не е необходимо да правим това в източника, но можем да направим това вътре в самата въртяща се таблица, те се наричат изчислени полета вътре в въртящата се таблица. Можем да използваме персонализирани формули, за да направим данните, за да разкажем повече история от данните. Изчислените полета ни позволяват да изградим нова изчислена колона, която не съществува в действителния източник на данни.
В тази статия ще демонстрираме начините за използване на изчислени полета на оборотна таблица за изграждане на нови колони въз основа на нашето изискване.
Персонализирано поле за изчисляване на сумата на печалбата
Можете да изтеглите този шаблон на формула на оборотна таблица тук - шаблон на формула на оборотна таблицаТова е най-често използваното изчислено поле в въртящата се таблица. Погледнете по-долу данните, имам име на държава, име на продукта, продадени единици, единична цена, брутни продажби, COGS (цена на продадените стоки), дата и година.
Позволете ми да приложа въртящата се таблица, за да намеря общите продажби и общите разходи за всяка страна. По-долу е въртящата се таблица за горните данни.
Проблемът е, че нямам колона за печалба в изходните данни. Трябва да открия процента на печалба и печалба за всяка страна. Можем да добавим тези две колони в самата въртяща се таблица.
Стъпка 1: Изберете клетка в въртящата се таблица. Отидете на раздел Анализ в лентата и изберете Полета, Елементи и Набори. Под това изберете Изчислено поле.
Стъпка 2: В диалоговия прозорец по-долу дайте име на новото си изчислено поле.
Стъпка 3: В раздела Формула приложите формулата, за да намерите печалбата. Формулата за намиране на печалбата е Брутни продажби - COGS.
Влезте вътре в лентата с формули> Изберете брутните продажби от полето по-долу и щракнете двукратно върху лентата с формули.
Сега напишете минус символ (-) и изберете COGS> Двойно щракване.
Стъпка 4: Щракнете върху ADD и OK, за да завършите формулата.
Стъпка 5: Сега имаме колоната с ОБЩ ПЕЧАЛБА в въртящата се таблица.
Това изчислено поле е гъвкаво, не се ограничава само до анализ на страната, но можем да го използваме за всякакъв вид анализ. Ако искам да видя анализа на страната и на продукта, трябва просто да плъзнете и пускате колоната с продукти в полето ROW, това ще покаже разделянето на печалбата за всеки продукт във всяка страна.
Стъпка 6: Сега трябва да изчислим процента на печалбата. Формулата за изчисляване на процента на печалбата е общата печалба / брутни продажби.
Отидете на Анализ и отново изберете Изчислено поле под полета, елементи и набори.
Стъпка 7: Сега трябва да видим ново вмъкнатото изчислено поле Обща печалба в списъка с полета. Въведете това поле във формулата.
Стъпка 8: Въведете символ за разделител (/) и поставете поле за брутни продажби.
Стъпка 9: Именувайте това изчислено поле като процент на печалба.
Стъпка 10: Щракнете върху ADD и OK, за да завършите формулата. Имаме процент на печалба като новата колона.
Разширена формула в изчислено поле
Каквото показах сега са основните неща на изчисленото поле. В този пример ще ви покажа разширените формули в изчислените полета на въртящата се таблица. Сега искам да изчисля сумата на стимулите въз основа на процента на печалбата.
Ако печалбата% е> 15%, стимулът трябва да бъде 6% от общата печалба.
Ако печалбата% е> 10% стимулът трябва да бъде 5% от общата печалба.
Ако печалбата% е <10%, стимулът трябва да бъде 3% от общата печалба.
Стъпка 1: Отидете на изчислено поле и отворете диалоговия прозорец по-долу. Дайте името като стимулираща сума.
Стъпка 2: Сега ще използвам условието IF, за да изчисля сумата на стимулите. Приложете по-долу формулите, както е показано на изображението.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
Стъпка 3: Щракнете върху ADD & OK, за да завършите. Сега имаме графа за стимулираща сума.
Ограничение на изчислено поле
Видяхме чудесата на изчислените полета, но има и някои от ограниченията. Сега погледнете изображението по-долу, ако искам да видя разпадането на стимулиращата сума за Продукт, ще имаме грешно SUB TOTAL & GRAND ОБЩО НА ИНТЕНТИВНА СУМА.
Затова бъдете внимателни, докато показвате сумата на изчислените полета. Ще ви покаже грешните суми.
Вземете списък на всички изчислени формули на полета
Ако не знаете колко формули има в изчисленото поле на въртящата се таблица, можете да получите обобщението на всички тях в отделен работен лист.
Отидете на Анализиране> Полета, елементи и набори -> Списък формули.
Той ще ви даде обобщение на всички формули в нов работен лист.
Неща, които трябва да запомните за формулата на оборотна таблица в Excel
- Можем да изтрием, променим всички изчислени полета.
- Не можем да използваме формули като VLOOKUP, SUMIF и много други формули, включени в обхвата в изчислени полета, т.е. всички формули, които изискват диапазон, не могат да бъдат използвани.
Препоръчителни статии
Това е ръководство за формулата на оборотна таблица в Excel. Тук обсъдихме стъпките за използване на формула на Pivot таблицата в Excel, заедно с примери и шаблон за изтегляне на excel. Можете също да разгледате тези полезни функции в excel -
- Уроци по Pivot Chart в Excel
- Създаване на оборотна таблица в Excel
- Урок за VLOOKUP в Excel
- Excel Създаване на база данни