Unpivot или как да завъртим цяла таблица

Наскоро ми зададоха интересен въпрос.

„Ежемесечно получаваме данни в следния вид:

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

Има ли някой трик в ексел, с който това може да стане бързо и лесно?“

Това е един много често срещан проблем за хора, които се занимават с анализ и отчетност. Но да се реши със стандартните инструменти на Ексел (дори и с макроси) не е лесна задача. Допълнителна трудност идва от там, че данните рядко са толкова добре подредени, както в показания пример.

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

„Завъртането“ на такива таблици може да се направи с помощта на формули, както подробно е обяснил първият сериозен Ексел блогър в България Юрий Кузнецов.

Аз ще представя решение с Power Query, с който тази задача се решава буквално с няколко клика.  И по-точно – функционалността Unpivot. (в интерес на истината Юрий Кузнецов е представил и такова решение)

Ако не знаете какво е Power Query – може да прочетете в тази статия или на сайта на Майкрософт.

Ще започна с най-простия случай, в който имаме една таблица с данните и просто трябва да я „завъртим“. След това ще покажа как можем да добавим името на месеца, ако то фигурира някъде в таблицата. И накрая – ще създадем един цикъл, с който данните за всеки нов месец ще се натрупват към предходните, за да оформим една хубава базичка с данни, които да анализираме.

1. Базов сценарий

Най-простия вариант е когато имаме един файл, който съдържа само и единствено таблицата с данните:

Отваряме нов файл в Ексел и отиваме на Power Query > From File > From Excel

 

Навигираме до папката с файла и го избираме с двоен клик.

Отваря се менюто Navigator, в което се вижда кой файл отваряме, колко шийта съдържа и кои са те. Когато кликнем върху името на шийта, отдясно се появява визуализация на данните в него.

Натискаме Edit, за да продължим. И вече сме в т.нар. Query Editor на Power Query. Повече подробности за менютата в него можете да прочетете в тази статия. Тук само ще маркирам четирите основни „зони“ в Query Editor:

  1. Ribbon Tab: Тук се намират всички менюта на Power Query със съответните икони
  2. Тук се появяват всички заявки (queries), които сме създали във файла. В момента е само едно и по подразбиране то носи името на шийта, който обработваме от файла (Sheet1)
  3. Тук се показва част (първите 1000 реда) от таблицата, която обработваме. Виждат се всички колони, така както са във файла
  4. В тази зона са някои от properties на заявката като нейното име например. Тук също се появяват и всички действия (Applied Steps), които сме направили.

На фона на всичките тези обяснения, това което ще направим, за да завъртим таблицата е изключително просто. Първо в Home натискаме Use First Row as Headers. С това казваме на Power Query а направи антетка на таблицата от стойностите в първия ред.

Второ, маркираме колоната „продукт“ > десен бутон и избираме Unpivot Other Columns.

И това е. Таблицата ни вече е във вида, в който я искаме.

 

Остава само да дадем подходящи наименования на колоните – кликаме два пъти върху тях, за да ги маркираме и след това пишем новото заглавие. Същото както в Ексел. Аз съм задал „Клиент“ и „Количество“

След това натискаме бутона Close&Load (най-горе и най-вляво) и Power Query зарежда нашата таблица в Ексел. Тя е удобно форматирана като ексел таблица.

Сега можем да добавим колона с месеца, да я копираме и да я пейстнем в друг файл, който ще ни служи за база. А следващия път, когато получим файла, трябва просто да го запишем в съответната папка със същото име и да дадем Refresh на Power Query (десен бутон върху таблицата и избираме Refresh).

Накратко – инструментът Unpivot на Power Query  ни позволява за секунда да „завъртим“ една таблица и да я приведем във вид, удобен за анализ. Можете да спрете да четете до тук и да започнете да експериментирате с Power Query.

Но аз все пак ви съветвам да продължите, защото има два очевидни проблема, които не сме решили все още: (1) когато получим файла за следващия месец, трябва ръчно да си добавим колона за месеца и (2) отново ръчно трябва да пейстнем данните във файла-база под тези от предходния месец.

2. Добавяне на колона за месеца

Най-логичното нещо е месецът (или денят, годината), за който се отнасят данните да е отбелязан някъде във файла или в неговото име. Да приемем, че то е отбелязано в клетка над таблицата с данните, както е на долната картинка.

Първите няколко стъпки от заявката са същите – започваме с Power Query > From file > From Excel, намираме пътечката до файла, отваряме го и влизаме в Query Editor. Обърнете внимание как Power Query даде име на новата заявка подобно на нов шийт в Ексел – с (2) накрая.

Добрата практика е да даваме на заявките някакви говорящи имена, затова нека да отделим 10 секунди да ги преименоваме. Става по същия начин както променяме име на шийт в Ексел – десен бутон и Rename. Sheet1 го заменям с Base Case (Базов сценарий), а Sheet1 (2) с Month Added (Добавен месец).

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

Появява се нова заявка, точно копие на Month Added, като Power Query автоматично добавя (2) към името, за да се различават.

Нека да я оставим за момент и да се върнем към „оригиналната“ Month Added. Там отиваме на Home > Remove Rows > Remove Top Rows

И след това задаваме 1

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

Таблица досущ като в базовия сценарий. От тук нататък повтаряме същите действия като при него – правим първия ред заглавие на колоните, маркираме колоната продукт, десен бутон + Unpivot Other Columns и слагаме подходящи имена на колоните. Таблицата придобива вида от преди малко.

Сега остава само да добавим месеца. За целта отиваме в заявката-дубликат, която създадохме преди малко. Кликаме върху клетката с името на месеца, така че само тя да бъде със зелен фон. Десен бутон и избираме Drill Down.

Резултатът е малко странен, но доверете ми се, точно това искаме да се получи:

Следващото действие е много важно. Трябва да дадем някакво име на заявката, в което да няма интервали. Аз избрах MonthName. Обърнете внимание как пред името на заявката символът не е като на другите две.

Сега се връщаме в заявката Month Added, за да довършим започнатото. За да добавим нова колона към заявката, къде мислите, че отиваме? Ами в менюто Add Column > Add Custom Column

И в появилото се меню даваме име на новата колона (Месец), а в Custom column formula записваме =MonthName

След това даваме ОК и …

О, чудо! Нашата таблица вече има колона с месеца.

Какво се случи? С действието Drill Down по-горе ние създадохме параметър, който е равен на името на месеца в най-горната клетка на файла с данните. Този параметър можем да си го извикваме във всякакви формули във всички останали заявки. И съвсем не е задължително името на месеца да бъде в най-горната и най-лява кетка. Може да бъде на всяко едно място във файла. Може дори да се повтаря. На нас ни трябва просто една клетка с месеца, за да направим drill down и да създадем параметъра. Ето защо казах, че този метод е най-гъвкав и универсален.

Единственото, което ни остава е да дадем Close&Load и нашата таблица ще се зареди в Ексел

Така успяхме да извлечем името на месеца от една клетка и да го запишем в отделна колона напълно автоматично. Сега можем спокойно да си копираме таблицата и да я пейстнем в нашата база.

Но, може би желаете да си спестите и тази стъпка и да работите напълно автоматично? Няма проблем, с Power Query можем да постигнем и това

3. Автоматично добавяне на следващия месец

По принцип Power Query работи така, че взима един файл с данни, обработва ги, създава таблица и я зарежда в Ексел. Но това става файл за файл. Ако заменим файла за Месец1 с този за Месец2, Power Query ще подмени с данните за Месец2 тези за Месец1, а няма да ги натрупа към тях.

Но има начин да го изхитрим. Методът работи добре, но при три условия:

  1. Записваме файла с данните за всеки нов месец на едно и също място
  2. Той винаги носи едно и също име
  3. Общият брой редове на всички данни е под 1,048,576 реда, колкото е максималният брой редове в един шийт

За да го постигнем, тръгваме от там, до където сме стигнали в т. 2 – от таблицата с данните за първия месец. Избираме произволна клетка от нея и отиваме на Power Query > From Table

Power Query автоматично създава нова заявка, която носи името на таблицата

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

 

Това ще бъде една виртуална таблица, тя ще съществува и ще се пълни само в паметта на Power Query и няма да ни трябва да работим с нея. Затова отиваме на Home > Close & Load > Close & Load To

Избираме Only Create Connection и след това Load

С това казваме на Power Query да не зарежда таблицата от тази заявка в Ексел, а просто да поддържа заявката „виртуално“. Ако сте правили бази данни в Аксес, веднага ще забележите приликата.

Следващата стъпка е да отидем в Power Query, да изберем заявката Month Added (тази, която обработва файла за съответния месец и го зарежда в Ексел) и от секцията Combine на таб Home да изберем Append Queries.

(отварям една скоба, за да поясня, че в Power Query можем да комбинираме по два начина различни заявки. Първият е Merge, т.е. обединяване. При него към дадена заявка добавяме различни колони от друга, което много прилича на това, което правим с VLOOKUP в Ексел. Вторият начин е Append, т.е. долепяне на редовете от една заявка към тези на друга. Аналогът в Ексел е простият копи/пейст на редовете от едната таблица под тези на другата. Това предполага таблиците, които са създали двете заявки да са с една и съща структура и наименование на колоните)

В менюто, което се появява, указваме, че към заявката Month Added, в която работим в момента (и затова зад името й в скоби е уточнено (Current)) ще долепим таблицата от заявка Database

Когато дадем ОК, се получава таблица, в която записите за Месец1 са дублирани. Това е така, защото в Database в момента е точно това, което е и в Month Added – данните за Месец1.

Разбира се, нещата ще се променят следващия месец, но за всеки случай нека да премахнем дублиращите се стойности. За целта маркираме всички колони на таблицата, десен бутон и избираме Remove Duplicates. Това е един бърз и удобен начин да кажем на Power Query, когато намери два или повече реда, на които стойностите във всички колони, които са маркирани, да остави само един от тях, а другите да ги премахне.

Сега, отиваме в папката, в която сме записали файла за Месец1, изтриваме го и записваме със същото име файла за Месец2. Подчертавам – със същото име. Това е важно и затова е добре да си изберете подходящо име като например „Current Month data“.

Каквото и да е името, когато дадем Refresh на таблицата в Ексел, получаваме това:

А тук вече са заредени и данните за Месец3.

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

Най-напред по алгоритъма в т.2 създадохме заявка (Month Added), която зарежда в Ексел таблица с продажбите за един месец. За този месец, за който сме сложили изходния файл в съответната папка.

След това от тази същата таблица създадохме нова заявка (Database) в Power Query. Тя просто копира данните от таблицата с продажбите и я поддържа виртуално.

Като трета стъпка добавихме таблицата Database към Month Added и така създадохме един цикъл, в който Month Added зарежда данните за новия месец в Ексел и те се добавят към данните от предишните и така, докато данните не достигнат 1,048,576 реда, колкото е максималният брой редове в Ексел.

Дали има начин да надвишим този брой? Сигурно, но за това друг път.

Публикувано в PowerExcel с етикети , , . Постоянна връзка.