Kako združiti podatke iz več Excelovih delovnih zvezkov. Recimo, da imate mapo polno datotek Excel. Vsak ima en delovni list z različnim številom vrstic. Vse datoteke v mapi lahko uvozite v en Excelov delovni zvezek.
Oglejte si video
- Ponovno obiskovanje čistega podatkov s podcastom Power Query # 2037
- Power Query lahko zdaj združi vse datoteke Excel v mapi.
- Izboljšano: glave samodejno izbrišejo iz vseh datotek, razen iz prve.
- Izberete datoteko, ki jo želite uporabiti kot vzorčno datoteko.
- Izberite, kateri delovni list, tabelo, poimenovani obseg želite uvoziti
- Preoblikovanje uporabite z urejevalnikom poizvedb
- Zapri in naloži, da združi vse datoteke
- Kasneje osvežite poizvedbo, da se posodobi
Video zapis
Naučite se Excel iz podcasta, epizoda 2077: Združi delovne zvezke
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Pred samo 40 epizodami smo govorili o čiščenju podatkov z Power Query, kako vzeti cel kup datotek CSV in jih spraviti v eno datoteko Excel. No, ta funkcija se je pravkar močno izboljšala.
Torej imam tukaj danes mapo in v tej mapi je cela vrsta mesečnih Excelovih datotek, od tega 22 skoraj dve leti. Vse te datoteke moram združiti. Vsi imajo enako postavitev stolpcev, v redu? Vsi imajo torej 9 stolpcev, vsi imajo enake glave in tako naprej, v redu. Torej bomo vse to kombinirali brez makra.
Če uporabljate Excel 2016, boste odprli zavihek Podatki za pridobivanje in preoblikovanje podatkov. Če uporabljate Excel 2010 ali 2013, boste šli ven in prenesli odličen izdelek iz Microsofta, imenovan Power Query. Celotno stvar lahko prenesete in jo namestite v manj kot minuti, prav.
Torej, tukaj bomo prišli po podatke iz datoteke, ne pa iz ene delovne knjige. Želimo ga dobiti iz take mape. V redu, tukaj so. Prosijo vas, da poiščete mapo, vendar bom samo kliknil in prilepil Ctrl + V, ker sem prilepil imenik, kliknite V redu. Tako so mi pokazali predogled podatkov, ki jih imam. Tu odprem ta spustni meni Kombiniraj in izberem Kombiniraj in uredi. V redu, potem pa je korak, da bomo izbrali eno datoteko, ki je dobra predstavniška datoteka, v redu, in samo izberejo prvo, ki je po abecedi. Vseeno je, katerega boste izbrali. In potem ta vzorčna poizvedba, ki jo gradimo iz prve datoteke, ki smo jo izbrali, v tem primeru želim Sheet1.Če imate več listov, lahko rečete List2 ali list z imenom Izkaz poslovnega izida ali Območje imen ali tabela. Pokazali so mi, kako izgleda, in sem kliknil V redu.
Zdaj se na tem zaslonu veliko dogaja. To, kar so dejansko storili tukaj, je, da so ustvarili vzorčno poizvedbo, ahh, vzorčno poizvedbo in jo nato ustvarili kot funkcijo in jo zagnali proti vsem v mapi. Dodajo nov stolpec, ki mi pove, od kod datoteka, v tem primeru pa že vem, od kod, ker imamo tukaj izpolnjen datum. Torej tega ne rabim, z desno miškino tipko bom to odstranil. In morda je tam nekaj podatkov, ki jih ne rabim. Mogoče bodo ti podatki šli stranki, zato ne želim prikazovati dobička, da lahko odstranim ta stolpec in celo odstranim stolpec Stroški, morda pa je nekaj motečih stvari, kot so, veste, uporabili so slabe glave in jaz želite preimenovati te glave. Torej z desno miškino tipko in Preimenuj jo bomo poimenovali QTY za količino.Mogoče so stvari v napačnem zaporedju, morda bi rad vzel ta predmet in ga premaknil na drugo mesto, v redu? Prav vse te majhne sitne stvari, ki jih morda imate, in seveda tukaj, izvajajo vse uporabljene korake, nato pa lahko še naprej izvajate vse stvari Power Query, ki jih morate storiti. Toda v tem primeru je preprosto. Želim samo vnesti podatke. Torej bom zaprl in naložil, čez nekaj sekund bo minilo in tam je. Imam svoj zadnji. Razvrstimo to tukaj po datumu; vidite, gremo od januarja 2018 pa vse do oktobra 2019. Kako kul je to.ponovite vse uporabljene korake, nato pa lahko nadaljujete z vsemi stvarmi Power Query, ki jih morate storiti. Toda v tem primeru je preprosto. Želim samo vnesti podatke. Torej bom zaprl in naložil, čez nekaj sekund bo minilo in tam je. Imam svoj zadnji. Razvrstimo to tukaj po datumu; vidite, gremo od januarja 2018 pa vse do oktobra 2019. Kako kul je to.ponovite vse uporabljene korake, nato pa lahko nadaljujete z vsemi stvarmi Power Query, ki jih morate storiti. Toda v tem primeru je preprosto. Želim samo vnesti podatke. Torej grem Zapri in naloži, čez nekaj sekund mine in tam je. Imam svoj zadnji. Razvrstimo to tukaj po datumu; vidite, gremo od januarja 2018 pa vse do oktobra 2019. Kako kul je to.
Zdaj je tukaj neverjetna, neverjetna stvar. Recimo, da se v naši mapi nekaj spremeni. Torej, nazaj v datotekah z mesecem, dobim dve novi datoteki, Prilepi, zdaj pa sta tu še dva zapisa, še dve Excelovi datoteki, ki ju moram združiti. Samo vrnite se v mojo datoteko Excel, ki ima trenutno 516 vrstic, poiščite MonthFiles in kliknite gumb Osveži. In to hitro imam zdaj nove zapise iz novembra in decembra. Power Query je neverjetno novo orodje, brezplačno za Excel 2010, brezplačno za Excel 2013. Bilo je čudovito, ko so lahko kombinirali datoteke CSV, zdaj pa lahko združijo tudi datoteke Excel, prav fenomenalno.
Tako sem posodobil svojo knjigo Power Excel z izdajo 2017, vključuje Power Query, Power BI in vsa nova orodja. Preveri. V zgornjem desnem kotu kliknite »i«.
V redu, zaključek te epizode: Ponovno bomo obiskali Clean Data s podcastom Power Query št. 2037, kjer bomo združili več datotek CSV. Zdaj lahko isto orodje, izboljšano različico tega orodja, uporabimo za združevanje vseh datotek Excel v mapi. Še ena izboljšava, sprememba od leta 2037, samodejno izbrišejo glave iz vseh datotek, razen iz prve, in te glave promovirajo v prvi datoteki. Izberete datoteko, ki jo želite uporabiti kot vzorčno datoteko. V mojem primeru sem uporabil januar. Delo - Izberite, kateri delovni list, tabelo ali poimenovani obseg želite uvoziti, nato pa v urejevalniku poizvedb izvedite kakršne koli preoblikovanja. Kliknite Zapri in naloži, v njej bodo združene vse datoteke. In potem, če v mapo spustite več datotek ali nekatere datoteke odnesete ali nekatere datoteke spremenite, samo osvežite poizvedbo in znova bo ponovil vse te korake. Power Query,popolnoma neverjetno orodje.
Želim se vam zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2077.xlsm
