Наскоро ми зададоха интересен въпрос.
„Ежемесечно получаваме данни в следния вид:
Искам да ги обединя и да получа следната база данни, за да мога от нея да си правя различни справки и разрези?
Има ли някой трик в ексел, с който това може да стане бързо и лесно?“
Това е един много често срещан проблем за хора, които се занимават с анализ и отчетност. Но да се реши със стандартните инструменти на Ексел (дори и с макроси) не е лесна задача. Допълнителна трудност идва от там, че данните рядко са толкова добре подредени, както в показания пример.
И като капак на всичко, обикновено всеки месец има различен брой клиенти и продукти. Като и продуктите, и клиентите могат да бъдат различни.
„Завъртането“ на такива таблици може да се направи с помощта на формули, както подробно е обяснил първият сериозен Ексел блогър в България Юрий Кузнецов.
Аз ще представя решение с Power Query, с който тази задача се решава буквално с няколко клика. И по-точно – функционалността Unpivot. (в интерес на истината Юрий Кузнецов е представил и такова решение)
Ако не знаете какво е Power Query – може да прочетете в тази статия или на сайта на Майкрософт.
Ще започна с най-простия случай, в който имаме една таблица с данните и просто трябва да я „завъртим“. След това ще покажа как можем да добавим името на месеца, ако то фигурира някъде в таблицата. И накрая – ще създадем един цикъл, с който данните за всеки нов месец ще се натрупват към предходните, за да оформим една хубава базичка с данни, които да анализираме.
1. Базов сценарий
Най-простия вариант е когато имаме един файл, който съдържа само и единствено таблицата с данните:
Отваряме нов файл в Ексел и отиваме на Power Query > From File > From Excel
Навигираме до папката с файла и го избираме с двоен клик.
Отваря се менюто Navigator, в което се вижда кой файл отваряме, колко шийта съдържа и кои са те. Когато кликнем върху името на шийта, отдясно се появява визуализация на данните в него.
Натискаме Edit, за да продължим. И вече сме в т.нар. Query Editor на Power Query. Повече подробности за менютата в него можете да прочетете в тази статия. Тук само ще маркирам четирите основни „зони“ в Query Editor:
- Ribbon Tab: Тук се намират всички менюта на Power Query със съответните икони
- Тук се появяват всички заявки (queries), които сме създали във файла. В момента е само едно и по подразбиране то носи името на шийта, който обработваме от файла (Sheet1)
- Тук се показва част (първите 1000 реда) от таблицата, която обработваме. Виждат се всички колони, така както са във файла
- В тази зона са някои от 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,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 реда, колкото е максималният брой редове в Ексел.
Дали има начин да надвишим този брой? Сигурно, но за това друг път.