Наскоро имах интересен казус, който решихме елегантно с изчисления в пивот таблица. Потърсиха ме за помощ, защото една проста справка непременно трябваше да стане с пивот таблица, което се оказало много трудно. Всъщност не е трудно, но трябва да знаеш къде да цъкнеш, което ще покажа в настоящата статия
Казусът
На снимката виждате една проста таблица с данни (напълно измислени). За примера таблицата е 25 реда, но в живия живот може да е дори десетки хиляди. За простота в първата колона е дадена година, но в действителност са дати.
Целта е от данните в тази таблица да се попълни следната справка:
По принцип най-удобно е това да стане с формули като SUMIFS или с Power Query, но условието беше изрично да се използват пивот таблици. Честно казано – не разбрах защо. Трябвало да се вижда от къде идват данните. И мен ме озадачи това обяснение, но нямаше време за подробности.
При използване на пивот таблица, обаче, има няколко проблема. Първо, видовете проценти ДДС да се подредят един под друг. Второ – продажбите с 0% и освободените продажби трябва да се представят на един ред (сумарно). Трето – как да направим разликата между двете години в отделна колона.
Excel предлага елегантно решение за всеки един от тези проблеми и позволява да направим необходимите изчисления в пивот таблица, без да променяме изходните данни. Нека да ги видим едно по едно.
Проблем 1: Видовете проценти един под друг
Започваме рутинно като кликнем някъде в таблицата с данните и след това Insert -> Tables -> Pivot Table.
След това слагаме полето „година“ в Columns
Следващата стъпка е да сложим полетата „Продажби с 20% ДДС“ и „Продажби с 9% ДДС“ в полето Values на пивот таблицата ето така:
В тази таблица обаче, колоните за продажби с ДДС се появяват една до друга като подколони на годините. А ние искаме да ги разположим като редове на таблицата. Забележете обаче как в полето Columns на пивот таблицата се появява едно ново поле „∑ Values”. За да прехвърлим видовете ДДС от колони в редове просто трябва да провлачим това поле от колоните в редовете.
И нашата пивот таблица започва да придобива вида, който ни трябва.
Проблем 2: 0% ДДС и освободени от ДДС в един общ ред
За да обединим „0% ДДС“ и „Освободени от ДДС“ в един ред ще използваме Calculated field (изчисляемо поле). За целта кликаме някъде в пивот таблицата и в лентата с бутони отиваме на PivotTable Analyze -> Fields, Items&Sets -> Calculated Field
В менюто, което се появява можем да създадем формули с основните математически действия от числовите полета в пивот таблицата. За целта в полето Name въвеждаме име за изчисляемото поле – аз съм сложил „0% и освободени от ДДС“. В полето Formula създаваме формулата. Математическите знаци – =, +, -, /, * и скоби ги пишем на ръка, а полетата от пивот таблицата ги избираме от списъка по-долу (секция Fields) с двойно кликане върху тях.
Виждате как изглежда формулата за нашите цели.
Когато дадем ОК – се получава това:
Създаденото току-що изчисляемо поле се появява в пивот таблицата като нов ред, точно както искахме. Но се появява и вдясно в Pivot Table Fields. По същество ние създадохме една нова колона в изходните данни на пивот таблицата, само че виртуална. Тя не съществува физически в изходната таблица, но Ексел си я добавя „на ум“ и може да борави с нея. И вече можем да я използваме както всяка друга колона. Удобно, а?
Проблем 3: Разликата между годините
Тук първо нека да се освободим от колоната Grand Total, която събира стойностите в колоните за отделните години. Нас не ни трябва сбор, а точно обратното – разликата между тях. Това става в следната последователност:
- Кликаме с десен бутон някъде в пивот таблицата и от менюто, което се появява избираме Pivot Table Options.
- Отиваме на Totals & Filters и махаме отметката на Show grand totals for rows.
Аз съм махнал и отметката за колоните за всеки случай
И ето – нашата пивот таблица вече няма тотални редове.
Сега вече можем да създадем разликата между 2025 и 2024 година. За целта ще използваме Calculated Item (Изчисляема категория).
Каква е разликата между Calculated Field и Calculated Item?
Първото е, когато правим изчисления върху полетата с данни – в нашия случай колоните за различни видове ДДС. Calculated Item създаваме, когато в една колона имаме различни видове категории и ние искаме да създадем нова категория от тях без да пипаме в изходните данни. Накратко – Calculated Field работи с полета, а Calculated Item работи с елементи (items) вътре в едно поле.
В нашия случай колоната Година съдържа две категории – 2024 и 2025. Но представете си, че може да имате колона в която едни числа са отбелязани с „отчет“, други с „бюджет“, а трети с „текуща прогноза“. С помощта на Calculated Item можем лесно да изчисляваме разликите между отчет, бюджет и прогноза.
Когато създаваме Calculated Item, за разлика от Calculated Field, е много важно да кликнем в пивот таблицата именно на колоната, върху която ще го създаваме. В случая трябва да кликнем точно върху клетката, в която пише „Година“ или на някоя от клетките с годините 2024 и 2025. Едва тогава като отидем на PivotTable Analyze -> Fields, Items&Sets можем да изберем Calculated Item. Иначе е сиво и не може да се избира
Менюто за Calculated Item е малко по-различно от това за изчисляемо поле, но се работи по същия начин. Задаваме име на категорията и формула в съответните полета. При писането на формулата математическите оператори ги въвеждаме на ръка, полетата ги избираме с двойно кликане на мишката от секцията Items. На картинката виждате как съм създал формулата за разлика между двете години.
Нашата пивот таблица е почти готова. Липсва само редът „Общо“. Той не се създава автоматично от пивот таблицата, защото всъщност тя събира стойностите в колоните с различни видове ДДС. Няма друг тотал за тях.
Но ние лесно можем да си го създадем като направим ново изчисляемо поле. Резултатът е на долната картинка. Няма да показвам как се прави.
Ако ви дразни това ‘Sum of’ може да го махнете (най-лесно чрез Find & Replace) като оставите един интервал пред наименованието на полетата, защото пивот таблицата не може да поддържа два елемента с едно и също име.
Важно уточнение
Calculated Field и Calculated Item са функционалности на обикновените пивот таблици. При изчисления в пивот таблица, създадена върху Data Model/Power Pivot, подходът е различен и изчисленията обикновено се правят чрез т.нар. measures. Последните се създават с помощта на езика DAX, с който могат да се създадат доста сложни изчисления, а не само простите математически операции.