David s Floride postavlja današnje vprašanje:
Imam dva delovna zvezka. Oba imata enake podatke v stolpcu A, preostali stolpci pa so drugačni. Kako lahko združim ta dva delovna zvezka?
Davida sem vprašal, ali je mogoče, da ima en delovni zvezek več zapisov kot drugi. In odgovor je Da. Davida sem vprašal, ali se ključno polje v vsaki datoteki prikaže le enkrat. Odgovor je tudi pritrdilen. Danes bom to rešil z Power Query. Orodja Power Query najdete v različicah sistema Excel 2016+ za Windows v razdelku Pridobivanje in preoblikovanje na zavihku Podatki. Če imate različice sistema Excel 2010 za Excel 2010 ali Excel 2013, lahko za te različice prenesete dodatek Power Query.
Tukaj je Davidov delovni zvezek 1. Ima izdelek in nato tri stolpce podatkov.
Tu je Davidov delovni zvezek 2. Ima kodo izdelka in nato druge stolpce. V tem primeru so v delovnem zvezku2 dodatni izdelki, vendar bodo rešitve delovale, če ima kateri koli delovni zvezek dodatne stolpce.
Tu so koraki:
-
Izberite Podatki, Pridobi podatke, Iz datoteke, Iz delovnega zvezka:
Naloži podatke iz datoteke - Poiščite prvi delovni zvezek in kliknite V redu
- V pogovornem oknu Navigatorja izberite delovni list na levi. (Tudi če je samo en delovni list, ga morate izbrati.) Na desni boste videli podatke.
- V pogovornem oknu Navigatorja odprite spustni meni Naloži in izberite Naloži v…
- Izberite Only Create a Connection in pritisnite OK.
-
Ponovite korake 1–5 za drugi delovni zvezek.
Ustvarite povezavo z delovnim zvezkom Če ste naredili obe delovni zvezki, bi morali videti dve povezavi na plošči Poizvedbe in povezave na desni strani zaslona Excel.
Povezave do obeh delovnih zvezkov Nadaljujte s koraki za združitev delovnih zvezkov:
-
Podatki, Pridobi podatke, Združi poizvedbe, Združi.
Združite dve poizvedbi z različnimi stolpci - V zgornjem spustnem meniju v pogovornem oknu Združi izberite prvo poizvedbo.
- V drugem spustnem meniju v pogovornem oknu Spajanje izberite drugo poizvedbo.
- Kliknite naslov izdelka v zgornjem predogledu (to je ključno polje. Upoštevajte, da lahko večkrat izberete dve ali več ključnih polj s kombinacijo tipk Ctrl + Klik)
- V drugem predogledu kliknite naslov Koda izdelka.
-
Odprite vrsto pridružitve in izberite Full Outer (Vse vrstice od obeh)
Koraki 8 - 12 so prikazani tukaj -
Kliknite V redu. Predogled podatkov ne prikazuje odvečnih vrstic in v zadnjem stolpcu večkrat prikaže le "Tabela".
To se ne zdi obetavno - Upoštevajte, da je v naslovu DavidTwo ikona "Razširi". Kliknite to ikono.
-
Neobvezno, vendar vedno počistim izbiro možnosti »Uporabi izvirno ime stolpca kot predpono«. Kliknite V redu.
Razširite polja iz delovnega zvezka 2 Rezultati so prikazani v tem predogledu:
Vsi zapisi iz katerega koli delovnega zvezka - V Power Query uporabite Domov, Zapri in naloži.
Tu je čudovita lastnost: če se osnovni podatki v katerem koli delovnem zvezku spremenijo, lahko kliknete ikono Osveži, da v delovni zvezek vnesete nove podatke.
Opomba
Ikona za osvežitev je običajno skrita. Povlecite levi rob podokna Poizvedbe in povezave v levo, da se prikaže ikona.
Oglejte si video
Video zapis
Naučite se Excel iz podcasta, epizoda 2216: Združite dve delovni zvezki na podlagi skupnega stolpca.
Hej, dobrodošli nazaj na netcastu, jaz sem Bill Jelen. Današnje vprašanje je poslal David, ki je bil na mojem seminarju v Melbournu na Floridi, za poglavje o vesoljski obali IIA.
David ima dva različna delovna zvezka, kjer je stolpec A skupen obema. Torej, tukaj je Delovni zvezek 1, tukaj je Delovni zvezek 2 - oba imata kodo izdelka. Ta ima predmete, ki jih prvi nima, ali obratno, David pa želi združiti vse stolpce. Torej, tukaj imamo tri stolpce in tukaj štiri stolpce. Oboje sem dal v isti delovni zvezek, če delovni zvezek nalagate za delo. Vzemite vsako od teh, jo premaknite v svoj delovni zvezek in shranite.
V redu, za združitev teh datotek bomo uporabili Power Query. Power Query je vgrajen v Excel 2016. Če uporabljate različico Windows 10 ali 13, lahko obiščete Microsoft in prenesete Power Query. Začnete lahko z novega praznega delovnega zvezka s praznim delovnim listom. Shranili boste to datoteko - shranite kot, veste, morda Delovni zvezek, da prikažete rezultate kombiniranih datotek .xlsx. Vredu? In kar bomo storili, bomo naredili dve poizvedbi. Odpravili se bomo na Podatki, Pridobi podatke, Iz datoteke, Iz delovnega zvezka, nato pa bomo izbrali prvo datoteko. V predogledu izberite list z vašimi podatki in tem podatkom ni treba storiti ničesar. Torej samo odprite polje za nalaganje in izberite Naloži v, Samo ustvari povezavo, kliknite V redu. Odlično. Zdaj bomo to ponovili za drugi element - Podatki iz datoteke,V delovnem zvezku izberite DavidTwo, izberite ime lista in nato odprite tovor Load to, Only Create a Connection. Na tej plošči boste videli tukaj, prisotni smo obe povezavi. Vredu.
Zdaj dejansko delo - Podatki, Pridobi podatke, Združi poizvedbe, Združi, nato pa v pogovornem oknu Združi izberite DavidOne, DavidTwo in ta naslednji korak je popolnoma neintuitiven. To moraš storiti. Izberite skupni stolpec ali stolpce - torej izdelek in izdelek. Vredu. In potem bodite zelo previdni pri tipu združevanja. Želim vse vrstice iz obeh, ker ima ena lahko dodatno vrstico in to moram videti, nato pa kliknemo V redu. Vredu. In tu je začetni rezultat. Zdi se, da ni uspelo; ni videti, da je dodal dodatne elemente, ki so bili v datoteki 2. In imamo ta stolpec 5 - zdaj je nič. Desni klik na stolpec 5 bom rekel: Odstrani ta stolpec. Torej odprite to ikono za razširitev in počistite to polje za Uporabi izvirno ime stolpca kot predpono in BAM! deluje. Torej, dodatni elementi, ki so bili v datoteki 2, ki niso v datoteki 1,se pojavijo.
Vredu. Zdaj je v današnji datoteki videti, da je ta stolpec kode izdelka boljši od tega stolpca izdelka, ker ima dodatne vrstice. Toda v prihodnosti se lahko zgodi, da bo Delovni zvezek 1 imel stvari, ki jih Delovni zvezek 2 nima. Torej bom pustil oba tam in se ne bom znebil nobenih ničel, ker, četudi se zdi, da je ta vrstica na dnu popolnoma nična, lahko v prihodnosti pride do situacije, ko tukaj imamo nekaj ničel, ker nekaj manjka. Vredu? Torej, končno, Close & Load in imamo šestnajst vrstic.
Zdaj, v prihodnosti, recimo, da se nekaj spremeni. V redu, torej se bomo vrnili k eni od teh dveh datotek, razred za Apple pa bom spremenil na 99 in vstavimo celo nekaj novega in shranimo ta delovni zvezek. Vredu. In potem, če želimo, da se naša datoteka za spajanje posodobi, pridite sem - zdaj, pazite, ko to storite prvič, ne vidite ikone za osvežitev - morate zajeti to vrstico in jo povleči čez . In naredili bomo osvežitev, naložili pa smo 17 vrstic, pojavila se je lubenica, Apple se spremeni v 99 - to je čudovita stvar. Zdaj, hej, bi radi izvedeli več o Power Query? Kupite to knjigo Ken Puls in Miguel Escobar, M is for (DATA) MONKEY. Pripeljal vas bom do hitrosti.
Zaključek danes: David s Floride ima dva delovna zvezka, ki ju želi združiti; oba imata enaka polja v stolpcu A, vsi drugi stolpci pa so različni; en delovni zvezek ima morda dodatne predmete, ki jih ni v drugem, David pa si jih želi; v nobeni datoteki ni dvojnikov; za rešitev tega bomo uporabili napajalno poizvedbo, zato začnite v novem praznem delovnem zvezku na praznem delovnem listu; naredili boste tri poizvedbe, najprej eno - Podatki, Iz datoteke, Delovni zvezek in nato Naloži v samo ustvarjeno povezavo; isto stvar za drugi delovni zvezek, nato pa Podatki, Pridobi podatke, Združi, izberite dve povezavi, izberite stolpec, ki je pogost v obeh - v mojem primeru Izdelek - in nato iz vrste Pridruži se želite v celoti pridružiti vsi iz datoteke 1, vsi iz datoteke 2. In potem je čudovito, če se osnovni podatki spremenijo,poizvedbo lahko samo osvežite.
Če želite prenesti delovni zvezek iz današnjega videoposnetka, obiščite URL v opisu YouTube.
No, hej, želim, da se David, kot da se je pojavil na mojem seminarju, želim zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo od.
Prenesite datoteko Excel
Za prenos datoteke excel: združite-temelji-na-skupnem-stolpcu.xlsx
Power Query je neverjetno orodje v Excelu.
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Vedno pritisnite F4, ko berete obseg ali matriko v funkciji"
Tanja Kuhn
