Комбинујте податке из више радних свезака у Екцелу (користећи Повер Куери)

Повер Куери може бити од велике помоћи ако желите да комбинујете више радних свезака у једну радну свеску.

На пример, претпоставимо да имате податке о продаји за различите регионе (исток, запад, север и југ). Ове податке из различитих радних свезака можете комбиновати у један радни лист помоћу програма Повер Куери.

Ако имате ове радне свеске на различитим локацијама/фасциклама, добра је идеја да их преместите у једну фасциклу (или направите копију и ту копију радне свеске ставите у исту фасциклу).

Дакле, за почетак, имам четири радне свеске у фасцикли (као што је приказано испод).

Сада у овом водичу покривам три сценарија у којима можете комбиновати податке из различитих радних свезака помоћу програма Повер Куери:

• Свака радна свеска има податке у Екцел табели и сви називи табела су исти.
• Свака радна свеска има податке са истим именом радног листа. То може бити случај када у свим радним свескама постоји лист под називом „сажетак“ или „подаци“, а све то желите да комбинујете.
• Свака радна свеска има много листова и табела и желите да комбинујете одређене табеле/листове. Ова метода такође може бити корисна када желите да комбинујете табелу/листове који немају доследан назив.

Хајде да видимо како да комбинујемо податке из ових радних свезака у сваком случају.

Свака радна свеска има податке у Екцел табели са истом структуром

Доња техника би радила када су ваше Екцел табеле структуриране на исти начин (исти називи колона).

Број редова у свакој табели може варирати.

Не брините ако неке од Екцел табела имају додатне колоне. Можете изабрати једну од табела као предложак (или као „кључ“ како га назива Повер Куери), а Повер Куери би га користио за комбиновање свих осталих Екцел табела са њим.

У случају да у другим табелама постоје додатне колоне, оне ће се занемарити и комбиновати само оне наведене у предлошку/кључу. На пример, ако предложак/кључна табела коју изаберете има 5 колона, а једна од табела у некој другој радној свесци има 2 додатне колоне, те додатне колоне би биле занемарене.

Сада имам четири радне свеске у фасцикли коју желим да комбинујем.

Испод је снимак табеле коју имам у једној од радних свезака.

Ево корака за комбиновање података из ових радних свезака у једну радну свеску (као јединствену табелу).

1. Идите на картицу Подаци.
2. У групи Гет & Трансформ кликните на падајући мени Нев Куери.
3. Задржите показивач миша на „Из датотеке“ и кликните на „Из фасцикле“.
4. У дијалогу Фолдер унесите путању до фасцикле која садржи датотеке или кликните на Бровсе и пронађите фасциклу.
5. Притисните ОК.
6. У дијалогу који се отвори кликните на дугме за комбиновање.
7. Кликните на „Комбинуј и учитај“.
8. У дијалогу „Комбинуј датотеке“ који се отвори изаберите табелу у левом окну. Имајте на уму да вам Повер Куери приказује табелу из прве датотеке. Ова датотека ће деловати као шаблон (или кључ) за комбиновање других датотека. Повер Куери би сада тражио „Табелу 1“ у другим радним свескама и комбиновао је са овом.
9. Притисните ОК.

Ово ће учитати коначни резултат (комбиноване податке) у ваш активни радни лист.

Имајте на уму да заједно са подацима, Повер Куери аутоматски додаје назив радне свеске као прву колону комбинованих података. Ово помаже у праћењу података из које радне свеске.

У случају да желите прво да уредите податке пре него што их учитате у Екцел, у 6. кораку изаберите „Комбинуј и уреди“. Ово ће отворити коначни резултат у уређивачу програма Повер Куери где можете уредити податке.

Неколико ствари које треба знати:

• Ако изаберете Екцел табелу као предложак (у кораку 7), Повер Куери ће користити називе колона у овој табели за комбиновање података из других табела. Ако друге табеле имају додатне колоне, оне ће се занемарити. У случају да те друге табеле немају колону која се налази у вашој табели шаблона, Повер Куери би за њу ставио „нулл“.
• Колоне не морају бити у истом редоследу као што Повер Куери користи заглавља колона за мапирање колона.
• Пошто сте изабрали Табелу1 као кључ, Повер Куери ће потражити Табелу1 у свим радним свескама и све то комбиновати. У случају да не пронађе Екцел табелу са истим именом (Табела1 у овом примеру), Повер Куери ће вам дати грешку.

Додавање нових датотека у фасциклу

Сада одвојимо минут и схватимо шта смо урадили са горњим корацима (који су нам одузели само неколико секунди).

Комбиновали смо податке из четири различите радне свеске у једној табели за неколико секунди, а да нисмо ни отворили ниједну радну свеску.

Али то није све.

Права МОЋ Повер Куери -а је да сада, када додате више датотека у фасциклу, не морате да понављате ниједан од ових корака.

Све што треба да урадите је да преместите нову радну свеску у фасциклу, освежите упит и она ће аутоматски комбиновати податке из свих радних свески у тој фасцикли.

На пример, у горњем примеру, ако додам нову радну свеску - „Средњи запад.клск“ у фасциклу и освежи упит, одмах ће ми дати нови комбиновани скуп података.

Ево како можете да освежите упит:

• Десним тастером миша кликните на Екцел табелу коју сте учитали на радни лист и кликните на Рефресх.
• Десним тастером миша кликните на Упит у окну „Упит радне свеске“ и кликните на Освежи
• Идите на картицу Подаци и кликните на Освежи.

Свака радна свеска има податке са истим именом радног листа

У случају да немате податке у Екцел табели, али су сви називи листова (са којих желите да комбинујете податке) исти, можете користити методу приказану у овом одељку.

Требате бити опрезни око неколико ствари када су то само табеларни подаци, а не Екцел табела.

• Називи радних листова треба да буду исти. Ово ће помоћи Повер Куерију да прегледа ваше радне свеске и комбинује податке са радних листова који имају исто име у свакој радној свесци.
• Повер Куери разликује велика и мала слова. То значи да се радни лист под називом „подаци“ и „подаци“ сматрају различитим. Слично, колоне са заглављем „Сторе“ и једна са „сторе“ сматрају се различитим.
• Иако је важно имати иста заглавља колона, није важно имати исти редослед. Ако је колона 2 у „Еаст.клск“ колона 4 у „Вест.клск“, Повер Куери ће се исправно подударати мапирањем заглавља.

Хајде сада да видимо како брзо да комбинујемо податке из различитих радних свезака где је назив радног листа исти.

У овом примеру имам фасциклу са четири датотеке.

У свакој радној свесци имам радни лист са именом „Подаци“ који садржи податке у следећем формату (имајте на уму да ово није Екцел табела).

Ево корака за комбиновање података из више радних свезака у један радни лист:

1. Идите на картицу Подаци.
2. У групи Гет & Трансформ кликните на падајући мени Нев Куери.
3. Задржите показивач миша на „Из датотеке“ и кликните на „Из фасцикле“.
4. У дијалогу Фолдер унесите путању до фасцикле која садржи датотеке или кликните на Бровсе и пронађите фасциклу.
5. Притисните ОК.
6. У дијалогу који се отвори кликните на дугме за комбиновање.
7. Кликните на „Комбинуј и учитај“.
8. У дијалогу „Комбинуј датотеке“ који се отвори, изаберите „Подаци“ у левом окну. Имајте на уму да вам Повер Куери приказује назив радног листа из прве датотеке. Ова датотека ће деловати као кључ/шаблон за комбиновање других датотека. Повер Куери ће проћи кроз сваку радну свеску, пронаћи лист под називом „Подаци и комбиновати све ово.
9. Притисните ОК. Сада ће Повер Куери проћи кроз сваку радну свеску, потражити радни лист под називом „Подаци“ у њему, а затим комбиновати све ове скупове података.

Ово ће учитати коначни резултат (комбиноване податке) у ваш активни радни лист.

У случају да желите прво да уредите податке пре него што их учитате у Екцел, у 6. кораку изаберите „Комбинуј и уреди“. Ово ће отворити коначни резултат у уређивачу програма Повер Куери где можете уредити податке.

Свака радна свеска има податке са различитим именима табела или листова

Понекад можда нећете добити структуриране и доследне податке (као што су табеле са истим именом или радни лист са истим именом).

На пример, претпоставимо да податке добијате од некога ко је направио ове скупове података, али је радне листове назвао Источни подаци, Западни подаци, Северни подаци и Јужни подаци.

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

У таквим случајевима и даље можете користити Повер Куери, али то морате учинити с неколико додатних корака.

1. Идите на картицу Подаци.
2. У групи Гет & Трансформ кликните на падајући мени Нев Куери.
3. Задржите показивач миша на „Из датотеке“ и кликните на „Из фасцикле“.
4. У дијалогу Фолдер унесите путању до фасцикле која садржи датотеке или кликните на Бровсе и пронађите фасциклу.
5. Притисните ОК.
6. У дијалогу који се отвори кликните на дугме Уреди. Ово ће отворити уређивач Повер Куери у којем ћете видети детаље свих датотека у фасцикли.
7. Држите контролни тастер и изаберите колоне „Садржај“ и „Назив“, кликните десним тастером миша и изаберите „Уклони друге колоне“. Ово ће уклонити све остале колоне осим изабраних колона.
8. На траци уређивача упита кликните на „Додај колону“, а затим на „Прилагођена колона“.
9. У дијалогу Додавање прилагођене колоне именујте нову колону као „Увоз података“ и користите следећу формулу = Екцел.Воркбоок ([ЦОНТЕНТ]). Имајте на уму да ова формула разликује велика и мала слова и морате да је унесете тачно онако како сам овде приказао.
10. Сада ћете видети нову колону у којој је написана Табела. Дозволите ми да објасним шта се овде догодило. Навели сте називе радних свезака Повер Куери, а Повер Куери је дохватио објекте као што су радни листови, табеле и именовани опсези из сваке радне свеске (која се од сада налази у ћелији Табела). Можете кликнути на празан простор поред текстуалне табеле и видели бисте податке при дну. У овом случају, пошто у свакој радној свесци имамо само једну табелу и један радни лист, можете видети само два реда.
11. Кликните на икону двоструке стрелице при врху колоне „Увоз података“.
12. У оквиру са подацима о колони који се отвори поништите ознаку „Користи оригиналну колону као префикс“, а затим кликните на дугме У реду.
13. Сада ћете видети проширену табелу у којој видите један ред за сваки објекат у табели. У овом случају, за сваку радну свеску, објекат листа и објекат табеле наведени су засебно.
14. У колони Врста филтрирајте листу тако да приказује само Табелу.
15. Држите контролни тастер и изаберите колону Име и подаци. Сада кликните десним тастером миша и уклоните све остале колоне.
16. У колони Подаци кликните на икону двоструке стрелице у горњем десном углу заглавља података.
17. У оквиру са подацима о колони који се отвори кликните на дугме У реду. Ово ће комбиновати податке у свим табелама и приказати их у програму Повер Куери.
18. Сада можете направити било коју трансформацију која вам је потребна, а затим идите на картицу Почетна и кликните Затвори и учитај.

Дозволите ми да покушам и брзо објасним шта смо овде урадили. Пошто није било доследности у именима листова или називима табела, користили смо формулу = Екцел.Воркбоок за преузимање свих објеката радних свезака у Повер Куери -у. Ови објекти могу укључивати листове, табеле и именоване опсеге. Када смо имали све објекте из свих датотека, филтрирали смо их да бисмо узели у обзир само Екцел табеле. Затим смо проширили податке у табелама и све ово спојили.

У овом примеру смо филтрирали податке само за коришћење Екцел табела (у кораку 13). У случају да желите да комбинујете листове, а не табеле, можете филтрирати листове.

Напомена - ова техника ће вам дати комбиноване податке чак и када постоји неслагање у именима колона. На пример, ако у Еаст.клск имате колону која је погрешно написана, завршићете са 5 колона. Повер Куери ће попунити податке у колоне ако их пронађе, а ако не може да пронађе колону, пријавиће вредност као „нулл“.

Слично томе, ако имате неке додатне колоне у било ком од табела, оне ће бити укључене у коначни резултат.

Сада, ако добијете више радних свезака из којих морате да комбинујете податке, једноставно их копирајте и налепите у фасциклу и освежите Повер Куери