Работата с pivot таблици се счита за висш пилотаж и високо ниво на владеене на Ексел. В тази статия ще ви покажа 8 неща за тях, които съм забелязал, че са познати на много малък кръг потребители.
За примерите ще използвам тази таблица с абсолютно измислени данни за продажби на някакви продукти на въображаеми клиенти. За по-просто имената на продуктите са думата „здравей“ на различни езици – английски, сръбски, италиански, хавайски, чешки и т.н.
1. Pivot таблици без данни
Мисля, че повечето потребители знаят повече от добре как се създават pivot таблици от менюто Insert > Pivot Table (ако случайно не знаете – можете да си видите как става в тази статия). Но малко са хората, които знаят, че всяка пивот таблица се състои от две части – да ги наречем видима и невидима. Видимата част е това, което виждаме и с което работим. А невидимата е т.нар. пивот кеш (pivot cache). Най-просто обяснено, пивот кешът е копие на изходните данни, но подредени и оптимизирани така, че изчисленията и групиранията в пивот таблицата да се случват много бързо. Ето защо дори и изходните данни да са десетки хиляди редове pivot таблиците работят бързо.
Наличието на пивот кеш има един съществен недостатък – той буквално удвоява размера на файла. Просто защото дублира изходните данни.
Но дава и някои предимства и едно от тях е, че можем да имаме във файла пивот таблица без да са налични изходните данни. Ето така:
Тук нарочно съм показал, че нямаме скрити работни листове, за да видите, че няма лъжа, няма измама – файлът съдържа само пивот таблица без изходни данни.
Без значение дали ще копираме работния лист с пивот таблицата в друг файл (Move or copy sheet), дали ще изтрием изходните данни или просто с Copy/Paste ще пейстнем пивот таблицата в друг файл. Резултатът е един и същ – имаме нов файл, който съдържа само пивот таблицата, но не и данните, върху които е създадена. При това можем да работим с нея съвсем нормално – да махаме и слагаме полета в колоните и редовете, да филтрираме, дори да създаваме изчисляеми полета.
Така можем да направим интерактивен отчет, който включва различни сечения и възможност за филтриране по различни признаци без обаче да изпращаме суровите данни.
Освен всичко друго това е и метод да намалим обема на файла.
Повече за пивот кеша тук: Pivot Cache in Excel – What Is It and How to Best Use It
2. Drill Down
Веднага показвам следващия номер, който учудващо за мен е сравнително малко познат. Той е в пряка връзка с пивот кеша.
От пивот таблицата по-горе се вижда, че клиентът 1936 Clowns е закупил общо 10,390 броя от различните продукти през третото тримесечие на 2021г. Един бърз начин да извадим списък на всички продажби, които стоят зад това число е просто като кликнем два пъти с левия бутон на мишката върху него. При това действие Ексел създава нов шийт, който съдържа всички редове от таблицата отговарящи на условието – клиент 1936 Clowns и трето тримесечие на 2021 година.
Следователно, ако направим пивот таблица, която съдържа всички продажби без значение от клиент, дата и продукт и използваме Drill down на тази клетка, това ще създаде нов работен лист с всички изходни данни.
Извод:
Не е добра идея да се възползваме от първия трик по-горе като изпратим само pivot таблици, ако изходните данни съдържат чувствителна информация.
Пример от живия живот
Преди няколко години нашумя случай с една болница в Англия, която публикувала отчет с обобщени данни за персонала. Това всъщност била пивот таблица, която „работела“ върху данни за над 6500 служителя на болницата. Отчетът бил публично достъпен на сайта на болницата около 10 месеца. През това време всеки е можел да си го свали и с два клика да се добере до данни като имена, заплати, етническа принадлежност и сексуална ориентация на служителите. Болницата беше глобена със 185 хил. паунда от Британската Агенция за защита на личните данни.
Надявам се тази статия да помогне да избегнете подобни глоби за нарушение на GDPR
Изключване на опцията за Drill Down за всички pivot таблици
Става като кликнете с десния бутон на мишката върху пивот таблицата. След това изберете Pivot Table Options
А след това в Data махнете отметката пред Enable show details
От тук нататък, когато потребител кликне два пъти върху клетка от
пивот таблицата ще получава ето това объркващо съобщение:
Разбира се, ако е чел тази или някоя от стотиците други статии в интернет, където се споменава за тази опция, той може много лесно да си възстанови възможността да достъпва изходните данни чрез Drill down. Така че не разчитайте много на тази забрана.
3. Подреждане на вкус
Нека да видим пак пивот таблицата с продажбите по клиенти.
По подразбиране Ексел подрежда данните по азбучен ред на имената на клиентите. В тази статия съм показал как можем да ги сортираме в зависимост от количеството (или сумата) на продажбите. Но освен това Ексел ни позволява да подредим доставчиците и по наш вкус. Например, всеки истински пловдивчанин ще ви каже, че мястото на Yellow-Black Power е най-отгоре, а 1936 Clowns – най-отдолу. Останалите могат да се подреждат както си желаят.
За да ги подредим по пловдивските критерии, трябва да кликнем с десния бутон върху името Yellow-Black Power и в менюто, което се показва отиваме на Move.
Това изважда ново меню, което съдържа няколко възможности за преместване на точно този клиент. В случая са активни само Move to beginning (Премести в началото) и Move Up (Премести едно нагоре). Това е така, защото по азбучен ред клиентът Yellow-Black Power е най-долу и няма как да бъде преместен по-долу.
След като преместихме Yellow-Black Power, ако кликнем на 1936 Clowns – вече са активни и другите възможности
И можем да преместим този клиент най-отдолу
Ето сега клиентите са подредени, както желаем – Yellow-Black Power най-отгоре, 1936 Clowns най-отдолу, а другите подредени по азбучен ред (заб. използваните цветове са за по-ясно открояване на двата клиента)
4. Slicers
Слайсърите (моля за извинение за чуждицата, но и българската дума, която се използва не е много българска – „сегментатор“) са бутони за филтриране на данните в pivot таблици. Те бяха въведени във версия 2010 на Ексел, но все още хората, които ги използват са малцинство. Което е жалко, защото предлагат страхотни възможности за създаване на интерактивни отчети в Ексел. Тук ще разгледам само базовите функции колкото да загатна какво може да се постигне с тях. За повече подробности може да видите линковете в края на тази точка
Създаване на слайсър
За да го направим първо трябва да кликнем някъде в пивот таблицата и след това отиваме на PivotTable Analyze > Inert Slicer
Отваря се меню, в което можем да кликнем върху всички полета в пивот таблицата, за да създадем слайсъри. Аз съм избрал Product (Продукт) и Years (Години)
Когато дадем ОК – Ексел създава два правоъгълника, в които има бутони.
С влачене можем да ги наместим в работния лист и да променим техните размери.
Както виждате всеки един от тези бутони отговаря на една уникална стойност в съответната колона. Това предполага, че е удачно да слагаме слайсъри на колони с обозрим брой стойности.
Каква е ползата от използване на слайсъри в pivot таблици?
Първо – филтрирането така е много по-удобно за потребителя. Например, ако трябва да изберем 2021 година от филтъра на пивот таблицата това изисква поне три кликания, а със слайсър – само 1
Второ – много по-ясно си личи какъв филтър е приложен. Ето на долната картинка веднага се вижда, че пивот таблицата показва данни за 2021 година и продуктите Aloha, Ciao и Merhaba.
За третото предимство вижте долната картинка. Слайсърите са вляво и управляват едновременно две pivot таблици. Така имаме възможност да създаваме отчети, представящи едновременно различни разрези и сечения на нашите данни.
Как да направим един слайсър да управлява две pivot таблици
Това става като кликнем с десен бутон върху съответния слайсър и изберем Report Connections.
Появява се списък с всички pivot таблици във файла и съответния работен лист, в който се намират. Там слагаме отметка пред таблиците, които искаме да управляваме с този сегментатор. Интересното е, че не е задължително pivot таблиците да са върху една и съща изходна таблица. Достатъчно е да съвпадат името на колоната и стойностите вътре.
Обърнете внимание също така, че Ексел позволява самите слайсъри да се форматират по най-различни начини.
Малко информация и примери за използването на слайсъри в pivot таблици:
Филтри и сегментатор в пивот таблици в MS Excel
How To Use Slicers in Excel: User Guide for Beginners
Преди да преминем към следващите опции за майстори на пивот таблиците – предлагам ви за почивка да отскочите до Индия и да видите нещо интересно – как Тадж Махал НЕ е построен за един ден.
5. GETPIVOTDATA
Това е една малко позната, малко използвана и доста подценявана функция в Ексел. Тя се различава от другите функции по това, че извлича данни само от pivot таблици. За да можем да я пишем по-лесно трябва да активираме Generate GetPivotData по следния начин (като първо сме кликнали в някоя пивот таблица)
След като сме активирали тази опция вече можем да създаваме формули с GETPIVOTDATA (внимавайте защото е много сложно):
В клетка извън пивот таблицата първо пишем знака равно (=) и след това избираме клетка в пивот таблицата, която ни интересува. Например тази, която отговаря на продадените количества за продукта Ahoj през 2021 година. Ексел автоматично създава тази формула вместо нас:
На долната картинка съм представил какъв е синтаксисът на тази функция:
Добре, сега знаем как работи GETPIVOTDATA, въпросът е
За какво можем да я използваме?
Един от най-честите случаи е когато искаме да комбинираме данните от пивот таблицата с други данни както на тази картинка:
В случая в ограденото каре сме създали един малък дашборд, който показва продажбите на продукт Aloha за двете години и колко пъти е имало промоция на този продукт. Продажбите са „извикани“ от пивот таблицата с помощта на GETPIVOTDATA. Обърнете внимание, че стойностите за Product и Years не са зададени с текст, а чрез референция съответно към клетки D3 и D6. По този начин можем да променяме продуктите (например с падащо меню) и това ще доведе до промяна на стойността на GETPIVOTDATA.
Данните за промоциите се извикват с друга формула (в случая е VLOOKUP, но може да бъде и INDEX/MATCH) от втората таблица (в колони J:L). Това е една съвсем обикновена таблица, поддържана ръчно от отдел Маркетинг и няма връзка с данните в пивот таблицата за продажбите.
Накратко GETPIVOTDATA извиква точно определена стойност от пивот таблица, която се описва посредством стойностите в колоните и редовете на тази пивот таблицата. Това може да се използва за създаване на различни отчети и графики. Също така е добра алтернатива за SUMIF, когато изходните данни са няколко десетки хиляди реда и използването на SUMIF води до бавно преизчисляване на формулите.
След като свикнете със синтаксиса на GETPIVOTDATA, можете директно да пишете формули с нея по подобие на другите формули в Ексел, не е необходимо да активирате опцията Generate GetPivotData.
Повече за GETPIVOTDATA може да научите на долните линкове:
GETPIVOTDATA function (microsoft.com)
I 💓 GETPIVOTDATA and why you should too!
Excel GetPivotData Function from scratch with examples
5. Distinct count
С помощта на пивот таблица лесно можем да изброим датите, на които клиентите са направили покупка:
Проблемът обаче е, че ако клиент е закупил 5 продукта на една и съща дата, Count на пивот таблицата ще отчете 5. За Търговския отдел обаче би било интересно да види колко са уникалните дати на покупка, т.е. в горния пример да отчете 1 вместо 5. Това може да се направи с т.нар. Distinct Count. За да се възползвате от него има две условия. Първото е да разполагате с версия на Ексел 2013 и нагоре. А второто, когато правите пивот таблица да сложите отметка срещу Add this data to the Data Model
След това можем да добавим полето Date два пъти в областта Values. Ексел автоматично избира формулата Count и за двете.
Нека оставим първото както си е и да кликнем върху второто с десния бутон. След това кликаме на Summarize Value By и в менюто, което се появява Distinct Count е най-долу
Ето я пивот таблицата, в която имаме обикновено и уникално изброяване на датите. Лесно се забелязва, че за повечето клиенти те са почти еднакви, т.е. те купуват по един продукт няколко пъти в месеца. Но при два от клиентите се забелязва нещо итересно:
BomPack рязко е променил начина си на купуване през 2021 – купува по-рядко но повече продукти. А JMJ по принцип си пазарува така и в двете години. Моят опит с анализ на данни ми подсказва, че си заслужава да се разгледат по-подробно тези двама клиенти. Обикновено когато нещо рязко се отличава от масовия случай това е индикация за нередност, проблем или пък интересна тенденция, от която можем да се възползваме по някакъв начин.
7. CUBE functions
По подобие на GETPIVOTDATA CUBE функциите се използват за извличане на данни само, че вече директно от изходните данни. По подобие на GETPIVOTDATA най-удобно е да ги създадем, като първо направим пивот таблица. Задължително условие е, при нейното създаване да сме сложили отметката на Add this data to the Data Model.
Ето тук една стандартна пивот таблица, която показва приходи (Revenue), разходи (Cost) и продадени количества (Quantity) по продукти. Имаме слайсъри за годините и за клиентите.
Кликаме някъде в пивот таблицата и отиваме на PivotTable Analyze > OLAP Tools > Convert to Formulas
Изведнъж хубавата пивот таблица се превръща в набор от клетки, всяка от която е формула
Тук формулите се виждат по-добре
И понеже са клетки с формули можем да ги копираме, пействаме, форматираме както ни е удобно. Можем да вмъкваме редове и колони между тях или да пишем обикновени формули, които реферират към тях.
Само като пример – ето един отчет, форматиран по начин, който не може да се направи с пивот таблица. Освен това е вмъкната формула за Gross Margin (формулата може да видите горе в лентата за формули).
При това този отчет се държи като пивот таблица – можем да го управляваме със слайсърите. В случая виждаме данните за клиента 1936 Clowns през 2021г. А когато се добавят нови данни в изходната таблица – можем да рефрешнем, за да се обнови отчетът.
Недостатъци на този метод:
- Веднъж като превърнем пивот таблица във формули, няма връщане назад
- Губим възможността за Drill down
- Ако се появи нов клиент или продукт например – той няма да се добави автоматично, както при пивот таблица. Ще трябва да си го добавим ръчно като напишем съответната формула
Повече за CUBE формулите тук:
Tips & Tricks for Writing CUBEVALUE Formulas
Cube Functions, the good the bad and how to make them great
8. Power Pivot
Последните два трика – Distinct Count и Cube формулите са един лесен начин потребители на Ексел, които са свикнали да работят с пивот таблици да се възползват от възможностите на Power Pivot. Но това е само една много малка част от това, което може да правите с Power Pivot. Съвсем накратко казано Power Pivot ви дава възможност да:
- обработвате милиони редове от данни
- комбинирате в една пивот таблица данните от няколко изходни таблици
- използвате езика DAX за анализ на данните. Например в DAX има готови формули за изчисляване стойността на един показател през миналото тримесечие или миналата година. Както и много други
- освен това самите данни могат да бъдат не само в ексел или текстови файлове, а в много различни източници – бази данни, JSON или XML файлове, дори интернет страници
Всичко това не може да се демонстрира в една статия, която и без това стана доста дълга. Но 2022 година е пред нас – следете блога, ще има статии за Power Pivot.