Комбинирайте данни от множество работни книги в Excel (използвайки Power Query)

Power Query може да бъде от голяма помощ, когато искате да комбинирате няколко работни книги в една работна книга.

Да предположим например, че имате данни за продажбите за различни региони (Изток, Запад, Север и Юг). Можете да комбинирате тези данни от различни работни книги в един работен лист с помощта на Power Query.

Ако имате тези работни книги на различни места/папки, добра идея е да ги преместите в една папка (или да създадете копие и да поставите това копие в същата папка).

Така че за начало имам четири работни книги в папка (както е показано по -долу).

Сега в този урок обхващам три сценария, при които можете да комбинирате данните от различни работни книги, използвайки Power Query:

• Всяка работна книга съдържа данните в таблица на Excel и всички имена на таблици са еднакви.
• Всяка работна книга има данни със същото име на работен лист. Това може да се случи, когато във всички работни книги има лист с име „обобщение“ или „данни“ и искате да комбинирате всичко това.
• Всяка работна книга има много листове и таблици и искате да комбинирате конкретни таблици/листове. Този метод може да бъде полезен и когато искате да комбинирате таблица/листове, които нямат последователно име.

Нека да видим как да комбинираме данните от тези работни книги във всеки отделен случай.

Всяка работна книга съдържа данните в таблица на Excel със същата структура

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

Броят на редовете във всяка таблица може да варира.

Не се притеснявайте, ако някои от таблиците на Excel имат допълнителни колони. Можете да изберете една от таблиците като шаблон (или като „ключ“, както го нарича Power Query) и Power Query ще го използва, за да комбинира всички останали таблици на Excel с него.

В случай, че в други таблици има допълнителни колони, те ще бъдат игнорирани и ще бъдат комбинирани само тези, посочени в шаблона/ключа. Например, ако избраният от вас шаблон/таблица с ключове има 5 колони, а една от таблиците в друга работна книга има 2 допълнителни колони, тези допълнителни колони ще бъдат игнорирани.

Сега имам четири работни книги в папка, които искам да комбинирам.

По -долу е снимка на таблицата, която имам в една от работните книги.

Ето стъпките за комбиниране на данните от тези работни книги в една работна книга (като една таблица).

1. Отидете в раздела Данни.
2. В групата Получаване и преобразуване щракнете върху падащото меню Нова заявка.
3. Задръжте курсора на мишката върху „От файл“ и кликнете върху „От папка“.
4. В диалоговия прозорец Папка въведете пътя на файла на папката, която съдържа файловете, или щракнете върху Преглед и намерете папката.
5. Щракнете върху OK.
6. В диалоговия прозорец, който се отваря, щракнете върху бутона за комбиниране.
7. Кликнете върху „Комбиниране и зареждане“.
8. В диалоговия прозорец „Комбиниране на файлове“, който се отваря, изберете таблицата в левия прозорец. Обърнете внимание, че Power Query ви показва таблицата от първия файл. Този файл ще действа като шаблон (или ключ) за комбиниране на други файлове. Power Query сега ще търси „Таблица 1“ в други работни книги и ще я комбинира с тази.
9. Щракнете върху OK.

Това ще зареди крайния резултат (комбинирани данни) във вашия активен работен лист.

Имайте предвид, че заедно с данните Power Query автоматично добавя името на работната книга като първата колона на комбинираните данни. Това помага да се следи какви данни са дошли от коя работна книга.

В случай, че искате първо да редактирате данните, преди да ги заредите в Excel, в стъпка 6 изберете „Комбиниране и редактиране“. Това ще отвори крайния резултат в редактора на Power Query, където можете да редактирате данните.

Няколко неща, които трябва да знаете:

• Ако изберете таблица на Excel като шаблон (в стъпка 7), Power Query ще използва имената на колоните в тази таблица, за да комбинира данните от други таблици. Ако други таблици имат допълнителни колони, те ще бъдат игнорирани. В случай, че тези други таблици нямат колона, която се намира във вашата таблица с шаблони, Power Query просто ще постави „нула“ за нея.
• Не е необходимо колоните да са в същия ред, тъй като Power Query използва заглавки на колони за картографиране на колони.
• Тъй като сте избрали Table1 като ключ, Power Query ще търси Table1 във всички работни книги и ще комбинира всичко това. В случай, че не намери таблица на Excel със същото име (Таблица1 в този пример), Power Query ще ви даде грешка.

Добавяне на нови файлове към папката

Сега нека да отделим минута и да разберем какво направихме с горните стъпки (което ни отне само няколко секунди).

Комбинирахме данните от четири различни работни книги в една единствена таблица за няколко секунди, без дори да отваряме някоя от работните книги.

Но това не е всичко.

Истинското POWER на Power Query е, че сега, когато добавяте повече файлове към папката, не е нужно да повтаряте нито една от тези стъпки.

Всичко, което трябва да направите, е да преместите новата работна книга в папката, да опресните заявката и тя автоматично ще комбинира данните от всички работни книги в тази папка.

Например, в горния пример, ако добавя нова работна книга - „Mid-West.xlsx“ в папката и опресняване на заявката, тя незабавно ще ми даде новия комбиниран набор от данни.

Ето как да опресните заявката:

• Щракнете с десния бутон върху таблицата на Excel, която сте заредили в работния лист, и щракнете върху Опресняване.
• Щракнете с десния бутон върху заявката в прозореца „Заявка за работна книга“ и щракнете върху Опресняване
• Отидете в раздела Данни и кликнете върху Опресняване.

Всяка работна книга има данни със същото име на работен лист

В случай, че нямате данни в таблица на Excel, но всички имена на листове (от които искате да комбинирате данните) са еднакви, тогава можете да използвате метода, показан в този раздел.

Има няколко неща, за които трябва да внимавате, когато това са само таблични данни, а не таблица на Excel.

• Имената на работния лист трябва да са еднакви. Това ще помогне на Power Query да прегледа вашите работни книги и да комбинира данните от работните листове, които имат едно и също име във всяка работна книга.
• Power Query е чувствителен към регистър. Това означава, че работен лист с име „данни“ и „данни“ се считат за различни. По същия начин колона с заглавието „Store“ и една с „store“ се считат за различни.
• Въпреки че е важно да имате едни и същи заглавки на колони, не е важно да имате един и същ ред. Ако колона 2 в „East.xlsx“ е колона 4 в „West.xlsx“, Power Query ще я съпостави правилно, като картографира заглавките.

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

В този пример имам папка с четири файла.

Във всяка работна книга имам работен лист с името „Данни“, който съдържа данните в следния формат (имайте предвид, че това не е таблица на Excel).

Ето стъпките за комбиниране на данни от множество работни книги в един работен лист:

1. Отидете в раздела Данни.
2. В групата Получаване и преобразуване щракнете върху падащото меню Нова заявка.
3. Задръжте курсора на мишката върху „От файл“ и кликнете върху „От папка“.
4. В диалоговия прозорец Папка въведете пътя на файла на папката, която съдържа файловете, или щракнете върху Преглед и намерете папката.
5. Щракнете върху OK.
6. В диалоговия прозорец, който се отваря, щракнете върху бутона за комбиниране.
7. Кликнете върху „Комбиниране и зареждане“.
8. В диалоговия прозорец „Комбиниране на файлове“, който се отваря, изберете „Данни“ в левия прозорец. Обърнете внимание, че Power Query ви показва името на работния лист от първия файл. Този файл ще действа като ключ/шаблон за комбиниране на други файлове. Power Query ще премине през всяка работна книга, ще намери листа с име „Данни и ще комбинира всичко това.
9. Щракнете върху OK. Сега Power Query ще премине през всяка работна книга, ще потърси в нея работния лист, наречен „Данни“, и след това ще комбинира всички тези набори от данни.

Това ще зареди крайния резултат (комбинирани данни) във вашия активен работен лист.

В случай, че искате първо да редактирате данните, преди да ги заредите в Excel, в стъпка 6 изберете „Комбиниране и редактиране“. Това ще отвори крайния резултат в редактора на Power Query, където можете да редактирате данните.

Всяка работна книга има данни с различни имена на таблици или имена на листове

Понякога може да не получите структурирани и последователни данни (като например таблици със същото име или работен лист със същото име).

Да предположим например, че получавате данните от някой, който е създал тези набори от данни, но е посочил работните листове като Източни данни, Западни данни, Северни данни и Южни данни.

Или човекът може да е създал таблици в Excel, но с различни имена.

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

1. Отидете в раздела Данни.
2. В групата Получаване и преобразуване щракнете върху падащото меню Нова заявка.
3. Задръжте курсора на мишката върху „От файл“ и кликнете върху „От папка“.
4. В диалоговия прозорец Папка въведете пътя на файла на папката, която съдържа файловете, или щракнете върху Преглед и намерете папката.
5. Щракнете върху OK.
6. В диалоговия прозорец, който се отваря, щракнете върху бутона Редактиране. Това ще отвори редактора на Power Query, където ще видите подробностите за всички файлове в папката.
7. Задръжте клавиша за управление и изберете колоните „Съдържание“ и „Име“, щракнете с десния бутон и изберете „Премахване на други колони“. Това ще премахне всички останали колони, с изключение на избраните колони.
8. В лентата на редактора на заявки щракнете върху „Добавяне на колона“ и след това върху „Персонализирана колона“.
9. В диалоговия прозорец Добавяне на персонализирана колона дайте име на новата колона като „Импортиране на данни“ и използвайте следната формула = Excel.Работна книга ([СЪДЪРЖАНИЕ]). Обърнете внимание, че тази формула е чувствителна към регистъра и трябва да я въведете точно по начина, който съм показал тук.
10. Сега ще видите нова колона, в която има написана таблица. Сега да обясня какво се е случило тук. Предоставихте имената на работните книги на Power Query и Power Query извлече обектите като работни листове, таблици и именови диапазони от всяка работна книга (която се намира в клетката Таблица към момента). Можете да кликнете върху бялото поле до текстовата таблица и ще видите информацията в долната част. В този случай, тъй като имаме само една таблица и един работен лист във всяка работна книга, можете да видите само два реда.
11. Кликнете върху иконата с двойна стрелка в горната част на колоната „Импортиране на данни“.
12. В полето за данни на колоната, което се отваря, премахнете отметката от „Използване на оригиналната колона като префикс“ и след това щракнете върху OK.
13. Сега ще видите разширена таблица, където виждате един ред за всеки обект в таблицата. В този случай за всяка работна книга обектът лист и обектът таблица са изброени отделно.
14. В колоната „Вид“ филтрирайте списъка, за да показвате само таблицата.
15. Задръжте клавиша за управление и изберете колоната Име и данни. Сега щракнете с десния бутон и премахнете всички останали колони.
16. В колоната Данни щракнете върху иконата с двойна стрелка в горния десен ъгъл на Заглавката на данните.
17. В полето за данни на колоната, което се отваря, щракнете върху OK. Това ще комбинира данните във всички таблици и ще се покаже в Power Query.
18. Сега можете да направите всяка необходима трансформация и след това отидете в раздела Начало и щракнете върху Затвори и зареждане.

Сега нека се опитам бързо да обясня какво направихме тук. Тъй като нямаше последователност в имената на листове или имена на таблици, използвахме формулата = Excel.Workbook, за да извлечем всички обекти на работните книги в Power Query. Тези обекти могат да включват листове, таблици и именувани диапазони. След като получихме всички обекти от всички файлове, ние ги филтрирахме, за да вземем предвид само Excel таблици. След това разширихме данните в таблиците и комбинирахме всичко това.

В този пример филтрирахме данните, за да използваме само Excel таблици (в Стъпка 13). В случай, че искате да комбинирате листове, а не таблици, можете да филтрирате листове.

Забележка - тази техника ще ви даде комбинираните данни, дори когато има несъответствие в имената на колони. Например, ако в East.xlsx имате колона, която е грешно написана, ще получите 5 колони. Power Query ще попълни данни в колони, ако ги намери, а ако не може да намери колона, ще отчете стойността като „нула“.

По същия начин, ако имате някои допълнителни колони в някой от работните листове с таблици, те ще бъдат включени в крайния резултат.

Сега, ако получите повече работни книги, от които трябва да комбинирате данни, просто ги копирайте и поставете в папката и опреснете Power Query