Обединяване на шийтове с Power Query

В практиката много често имаме следния казус – един файл с еднотипни шийтове, които искаме да обединим в един, за да изготвим отчет или анализ. Например данните за музеите в България за периода 2011-2016, публикувани от НСИ, изглеждат така:

Шест шийта, всеки от които съдържа таблица с данни. Таблиците са еднакви като структура, само данните са различни. Така подредена информацията не е във вид, удобен за изготвяне на отчети и анализи. Не може дори елементарна графика за посещенията по години да се направи, защото данните са в различни шийтове.

Преди да направим каквото и да е обобщение трябва да приведем данните в такъв вид:

Всяка колона съдържа определен тип информация – година, област, брой на музеите и т.н. А всеки ред представлява уникален запис. Например на ред 2 са данните за област Благоевград през 2011 г.

Това е най-удобният вид за анализ на данните, който ни позволява да правим различни обобщения и изчисления, да филтрираме и подреждаме данните според както ни е удобно и потребно.

Привеждането на данните от състояние 1, до състояние 2 става бързо и лесно с помощта на Power Query. Ето как:

Започваме като отворим нов файл и там отиваме на Power Query > From File > From Excel*

*Това важи за Ексел 2010 и 2013. В Ексел 2016 началото е Data > New Query> From File > From Excel. От там нататък стъпките са едни и същи.

Навигираме до папката, където се намира файлът с данните, маркираме го и даваме ОК (или двоен клик).

Появява се меню, в което виждаме името на файла. От ляво на него е символът за файл – жълта папка, а отдясно в квадратни скоби виждаме колко шийта има в него. Самите шийтове са показани отдолу и пред всеки стои иконката за шийт.

Ако кликнем на Select multiple items имаме възможност да изберем само няколко от всички шийтове във файла. В случая съм избрал 2011 и 2015. Обърнете внимание, че отдясно се появява визуализация на част от таблицата.

В случая ние искаме да заредим всички шийтове, затова не слагаме отметка на Select multiple items, а избираме целия файл, след което кликаме Edit.

Следващата стъпка е да „разтворим“ данните като натиснем бутона, ограден в червено.

Тук Power Query ни дава възможност да си изберем кои колони да заредим. В случая той е присвоил служебни имена Column1, Column2 и т.н. и не е много ясно кои ни трябват и кои не. Затова просто даваме ОК.

Ето я нашата таблица заредена в Power Query. Доближаваме се до вида, който целим, но все още е твърде студено.

Ако натиснем бутона за филтър на най-лявата колона, можем да се уверим, че са заредени данните за всичките години.

Най-горните два реда не са ни необходими. Нека да ги махнем като отидем на Home > Remove Rows > Remove Top Rows.

Отбелязваме, че искаме да премахнем 2 реда

А след това натискаме Use First Row As headers, за да укажем на Power Query, че искаме колоните да се казват така, както е записано в първия ред.

Чудесно се получи, с изключение на най-лявата и най-дясната колона. Първата приема стойността 2011, защото това беше записано на първия ред, а втората – служебното наименование Custom7, защото първият ред в нея беше празен (null).

Това е много лесно поправимо. Просто кликаме два пъти на заглавието на лявата колона и то се маркира. Сега вече можем да заменим „2011“ с нещо по-подходящо, например „Година“.

За най-дясната колона можем да го направим по друг начин – десен бутон някъде в колоната и избираме Rename…. След това записваме новото заглавие. Аз избрах „ в т.ч. специалисти“.

Става все по-топло и нашата таблица е вече почти във вида, в който я искаме.

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

За да ги премахнем най-напред трябва да натиснем бутона за филтър на колона „Области“ и да махнем отметките от (null), Области, и Общо.

 

Не е толкова трудно да махнем отметките и пред „МУЗЕИ ПО ОБЛАСТИ ПРЕЗ …. ГОДИНА“, но това не е разумно. Защото, когато НСИ публикува данните за 2017, ще се появи шийт 2017 със запис „МУЗЕИ ПО ОБЛАСТИ ПРЕЗ 2017 ГОДИНА“ и филтърът на Power Query няма да го хване.

Затова е по-добре да приложим още един път филтър като този път изберем Text Filters > Does Not Contain…

И след това да запишем МУЗЕИ ПО ОБЛАСТИ ПРЕЗ.

По този начин казваме на Power Query след като отстрани ненужни записи като (null), Области, и Общо, да приложи и втори филтър. В Ексел това не можем да го направим.

И така вече е съвсем горещо, таблицата ни изглежда точно така, както ни трябва. Но не и данните в нея. Обърнете внимание на иконките отляво на заглавието на всяка колона. Те показват какви са данните в нея.

В случая всички иконки са АВС и под тях 123. Това означава, че в тях данните са форматирани като текст, дори и да ви приличат на числа. Това се оправя много лесно – просто натискаме съответната иконка и си избираме подходящ формат от менюто, което ще се появи.

В случая е удачно да изберем 123 (Whole Number) за почти всички колони. Само колоната „Области“ остава с формат ABC Text, защото съдържа имената на областите.

Вече сме почти готови. Остава само да натиснем Close & Load бутона и Power Query ще зареди таблицата с данните в Ексел.

Ето я нашата таблица в Ексел:

Като бонус, Power Query задължително форматира данните като Ексел Таблица. Какво е Ексел Таблица и какви са нейните предимства – можете да прочетете в тази статия

И ако натиснем бутона за филтър на колона „Година“, ще се уверим, че са заредени данните за всичките години.

 

Може би ви се струва сложно, но уверявам ви, когато му свикнете ще се влюбите в Power Query. Всъщност обединяването на 6 шийта с данни и привеждането им във вид, удобен за анализ, отнема по-малко от минута и 20-на клика с мишката.

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

 

 

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