Съдържание
Формулиране на проблема
Нека да разгледаме красиво решение за една от много стандартните ситуации, с които повечето потребители на Excel се сблъскват рано или късно: трябва бързо и автоматично да съберете данни от голям брой файлове в една финална таблица.
Да предположим, че имаме следната папка, която съдържа няколко файла с данни от градове-клонове:
Броят на файловете няма значение и може да се промени в бъдеще. Всеки файл има лист с име Продажбикъдето се намира таблицата с данни:
Броят на редовете (поръчките) в таблиците, разбира се, е различен, но наборът от колони е стандартен навсякъде.
Задача: събиране на данни от всички файлове в една книга с последваща автоматична актуализация при добавяне или изтриване на градски файлове или редове в таблици. Според окончателната консолидирана таблица тогава ще бъде възможно да се изграждат всякакви отчети, обобщени таблици, данни за филтриране и т.н. Основното е да можете да събирате.
Избираме оръжия
За решението се нуждаем от най-новата версия на Excel 2016 (необходимата функционалност вече е вградена в нея по подразбиране) или предишни версии на Excel 2010-2013 с инсталирана безплатна добавка Запитване за захранване от Microsoft (изтеглете го тук). Power Query е супер гъвкав и супер мощен инструмент за зареждане на данни в Excel от външния свят, след което ги премахва и обработва. Power Query поддържа почти всички съществуващи източници на данни – от текстови файлове до SQL и дори Facebook 🙂
Ако нямате Excel 2013 или 2016, тогава не можете да прочетете повече (шегувам се). В по-старите версии на Excel подобна задача може да се изпълни само чрез програмиране на макрос във Visual Basic (което е много трудно за начинаещи) или чрез монотонно ръчно копиране (което отнема много време и генерира грешки).
Стъпка 1. Импортирайте един файл като проба
Първо, нека импортираме данни от една работна книга като пример, така че Excel да „вземе идеята“. За да направите това, създайте нова празна работна книга и...
- ако имате Excel 2016, отворете раздела Дата и след това Създаване на заявка – От файл – От книга (Данни — Нова заявка — От файл — От Excel)
- ако имате Excel 2010-2013 с инсталирана добавка Power Query, отворете раздела Запитване за захранване и изберете върху него От файл – От книга (От файл — От Excel)
След това в прозореца, който се отваря, отидете в нашата папка с отчети и изберете някой от градските файлове (няма значение кой, защото всички те са типични). След няколко секунди трябва да се появи прозорецът на навигатора, където трябва да изберете листа, от който се нуждаем (Продажби) от лявата страна, а съдържанието му ще се покаже от дясната страна:
Ако щракнете върху бутона в долния десен ъгъл на този прозорец Изтегли (Зареждане), тогава таблицата ще бъде незабавно импортирана в листа в оригиналния си вид. За един файл това е добре, но трябва да заредим много такива файлове, така че ще отидем малко по-различно и ще щракнете върху бутона Корекция (Edit). След това редакторът на заявки на Power Query трябва да се покаже в отделен прозорец с нашите данни от книгата:
Това е много мощен инструмент, който ви позволява да „завършите“ таблицата до изгледа, от който се нуждаем. Дори едно повърхностно описание на всички негови функции би отнело около сто страници, но ако е много накратко, с помощта на този прозорец можете:
- филтрирайте ненужните данни, празни редове, редове с грешки
- сортиране на данни по една или повече колони
- отървете се от повторението
- разделяне на лепкав текст по колони (по разделители, брой знаци и т.н.)
- подредете текста (премахнете излишните интервали, поправете малки и големи букви и т.н.)
- преобразувайте типове данни по всеки възможен начин (превръщайте числа като текст в нормални числа и обратно)
- транспониране (завъртане) на таблици и разширяване на двумерни кръстосани таблици в плоски
- добавете допълнителни колони към таблицата и използвайте формули и функции в тях с помощта на езика M, вграден в Power Query.
- ...
Например, нека добавим колона с текстовото име на месеца към нашата таблица, така че по-късно да бъде по-лесно да създаваме отчети с обобщени таблици. За да направите това, щракнете с десния бутон върху заглавието на колоната датаи изберете командата Дублирана колона (Дублирана колона)и след това щракнете с десния бутон върху заглавката на дублиращата се колона, която се появява, и изберете Команди Трансформация – Месец – Име на месец:
За всеки ред трябва да се оформи нова колона с текстовите имена на месеците. Като щракнете двукратно върху заглавието на колона, можете да я преименувате от Дата на копиране към по-удобен месец, напр.
Ако в някои колони програмата не е разпознала правилно типа данни, тогава можете да й помогнете, като щракнете върху иконата за формат от лявата страна на всяка колона:
Можете да изключите редове с грешки или празни редове, както и ненужни мениджъри или клиенти, като използвате прост филтър:
Освен това всички извършени трансформации са фиксирани в десния панел, където винаги могат да бъдат върнати назад (кръст) или да променят параметрите си (предавка):
Леко и елегантно, нали?
Стъпка 2. Нека трансформираме нашата заявка във функция
За да повторим впоследствие всички трансформации на данни, направени за всяка импортирана книга, трябва да преобразуваме нашата създадена заявка във функция, която след това ще бъде приложена на свой ред към всички наши файлове. Да направите това всъщност е много просто.
В редактора на заявки отидете в раздела Изглед и щракнете върху бутона Разширен редактор (Преглед — Разширен редактор). Трябва да се отвори прозорец, където всичките ни предишни действия ще бъдат написани под формата на код на езика M. Моля, обърнете внимание, че пътят до файла, който импортирахме за примера, е твърдо кодиран в кода:
Сега нека направим няколко корекции:
Значението им е просто: първият ред (път към файла)=> превръща нашата процедура във функция с аргумент файлова пътека, а по-долу променяме фиксирания път към стойността на тази променлива.
Всичко. Кликнете върху завършеност и трябва да видите това:
Не се страхувайте, че данните са изчезнали – всъщност всичко е наред, всичко трябва да изглежда така 🙂 Ние успешно създадохме нашата персонализирана функция, където целият алгоритъм за импортиране и обработка на данни се запомня, без да е обвързан с конкретен файл . Остава да му дадем по-разбираемо име (напр getData) в панела вдясно в полето Име и можете да жънете Начало — Затваряне и изтегляне (Начало — затвори и зареди). Моля, обърнете внимание, че пътят до файла, който импортирахме за примера, е твърдо кодиран в кода. Ще се върнете към главния прозорец на Microsoft Excel, но вдясно трябва да се появи панел със създадената връзка към нашата функция:
Стъпка 3. Събиране на всички файлове
Всичко най-трудно е зад гърба, остава приятното и лесно. Отидете в раздела Данни – Създаване на заявка – От файл – От папка (Данни — Нова заявка — От файл — От папка) или, ако имате Excel 2010-2013, подобно на раздела Запитване за захранване. В прозореца, който се показва, посочете папката, където се намират всички наши изходни градски файлове, и щракнете OK. Следващата стъпка трябва да отвори прозорец, в който ще бъдат изброени всички Excel файлове, намерени в тази папка (и нейните подпапки) и подробности за всеки от тях:
Кликнете промяна (Edit) и отново влизаме в познатия прозорец на редактора на заявки.
Сега трябва да добавим още една колона към нашата таблица с нашата създадена функция, която ще "изтегли" данните от всеки файл. За да направите това, отидете на раздела Добавяне на колона – Персонализирана колона (Добавяне на колона — Добавяне на персонализирана колона) и в прозореца, който се показва, въведете нашата функция getData, указвайки за него като аргумент пълния път до всеки файл:
След като кликнете върху OK създадената колона трябва да се добави към нашата таблица вдясно.
Сега нека изтрием всички ненужни колони (както в Excel, като използвате десния бутон на мишката – Премахване), оставяйки само добавената колона и колоната с името на файла, тъй като това име (по-точно градът) ще е полезно да има в общите данни за всеки ред.
И сега „уау моментът“ – щракнете върху иконата със собствени стрелки в горния десен ъгъл на добавената колона с нашата функция:
… премахнете отметката Използвайте оригиналното име на колона като префикс (Използвайте оригиналното име на колона като префикс)и кликнете OK. И нашата функция ще зареди и обработи данните от всеки файл, следвайки записания алгоритъм и събирайки всичко в обща таблица:
За пълна красота можете също да премахнете разширенията .xlsx от първата колона с имена на файлове – чрез стандартна замяна с „нищо“ (щракнете с десния бутон върху заглавката на колоната – заместител) и преименувайте тази колона на град. И също така коригирайте формата на данните в колоната с датата.
Всичко! Кликнете върху Начало – затвори и зареди (Начало — затвори и зареди). Всички данни, събрани от заявката за всички градове, ще бъдат качени в текущия лист на Excel във формат „интелигентна таблица“:
Създадената връзка и нашата функция за сглобяване не е необходимо да се записват отделно по никакъв начин – те се записват заедно с текущия файл по обичайния начин.
В бъдеще, при всякакви промени в папката (добавяне или премахване на градове) или във файлове (промяна на броя на редовете), ще бъде достатъчно да щракнете с десния бутон директно върху таблицата или върху заявката в десния панел и да изберете команда Актуализиране и запазване (Опресняване) – Power Query ще „възстанови“ всички данни отново след няколко секунди.
PS
Изменение. След актуализациите от януари 2017 г. Power Query се научи как да събира работни книги на Excel сам, т.е. вече няма нужда да правите отделна функция – това става автоматично. По този начин втората стъпка от тази статия вече не е необходима и целият процес става значително по-прост:
- Изберете Създаване на заявка – От файл – От папка – Изберете папка – OK
- След като се появи списъкът с файлове, натиснете промяна
- В прозореца на редактора на заявки разгънете двоичната колона с двойна стрелка и изберете името на листа, което да бъде взето от всеки файл
И това е всичко! песен!
- Редизайн на кръстосаната таблица в плоска, подходяща за изграждане на обобщени таблици
- Изграждане на анимирана балонна диаграма в Power View
- Макрос за сглобяване на листове от различни Excel файлове в един