David z Floridy si kladie dnešnú otázku:
Mám dva pracovné zošity. Oba majú rovnaké údaje v stĺpci A, ale zvyšné stĺpce sú odlišné. Ako môžem zlúčiť tieto dva zošity?
Spýtal som sa Davida, či je možné, že jeden zošit má viac záznamov ako druhý. A odpoveď je Áno. Spýtal som sa Davida, či sa kľúčové pole v každom súbore zobrazuje iba raz. Odpoveď je tiež áno. Dnes to vyriešim pomocou Power Query. Nástroje Power Query sa nachádzajú vo verziách programu Excel 2016+ pre Windows v sekcii Získanie a transformácia na karte Údaje. Ak máte verzie systému Excel 2010 alebo Excel 2013 pre Windows, môžete si pre tieto verzie stiahnuť doplnok Power Query.
Tu je Davidov zošit 1. Obsahuje Produkt a potom tri stĺpce s údajmi.
Tu je Davidov zošit 2. Obsahuje produktový kód a potom ďalšie stĺpce. V tomto príklade sú v zošite2 ďalšie produkty, ale riešenia budú fungovať, ak bude mať ktorýkoľvek zošit ďalšie stĺpce.
Tu sú kroky:
-
Vyberte údaje, získať údaje, zo súboru, zo zošita:
Načítajte údaje zo súboru - Prejdite na prvý zošit a kliknite na tlačidlo OK
- V dialógovom okne Navigátor vyberte pracovný hárok vľavo. (Aj keď existuje iba jeden pracovný hárok, musíte ho vybrať.) Údaje sa zobrazia vpravo.
- V dialógovom okne Navigátor otvorte rozbaľovaciu ponuku Načítať a vyberte možnosť Načítať do …
- Vyberte iba Vytvoriť pripojenie a stlačte OK.
-
Zopakujte kroky 1 - 5 pre druhý zošit.
Vytvorte pripojenie k zošitu Ak ste vytvorili obidva zošity, mali by sa vám na paneli Dotazy a spojenia napravo od obrazovky programu Excel zobraziť dve spojenia.
Pripojenia k obom zošitom Pokračujte podľa krokov na zlúčenie zošitov:
-
Dáta, získať dáta, kombinovať dotazy, zlúčiť.
Zlúčte dva dotazy s rôznymi stĺpcami - Z rozbaľovacej hornej časti v dialógovom okne Zlúčiť vyberte prvý dopyt.
- Z druhej rozbaľovacej ponuky v dialógovom okne Zlúčiť vyberte druhý dotaz.
- Kliknite na nadpis Produkt v hornom náhľade (toto je kľúčové pole. Upozorňujeme, že viac alebo viac kľúčových polí môžete vybrať pomocou klávesovej skratky Ctrl + kliknutie).
- V druhom náhľade kliknite na nadpis Kód produktu.
-
Otvorte typ spojenia a vyberte možnosť Úplný vonkajší (všetky riadky z oboch)
Tu sú zobrazené kroky 8 - 12 -
Kliknite na tlačidlo OK. Ukážka údajov nezobrazuje ďalšie riadky a v poslednom stĺpci sa zobrazuje iba opakovane „Tabuľka“.
To nevyzerá nádejne - Všimnite si, že v nadpise pre DavidTwo je ikona „Rozbaliť“. Kliknite na túto ikonu.
-
Voliteľné, ale vždy zruším začiarknutie možnosti „Použiť pôvodný názov stĺpca ako predponu“. Kliknite na tlačidlo OK.
Rozbaľte polia zo zošita 2 Výsledky sú zobrazené v tomto náhľade:
Všetky záznamy z jedného zošita - V Power Query použite Domov, Zavrieť a načítať.
Tu je krásna vlastnosť: ak sa zmenia podkladové údaje v niektorom zo zošite, kliknutím na ikonu Obnoviť stiahnete nové údaje do zošita s výsledkami.
Poznámka
Ikona Obnoviť je zvyčajne skrytá. Ikonu zobrazíte presunutím ľavého okraja tably Dotazy a pripojenia doľava.
Pozeraj video
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2216: Spojte dva zošity založené na spoločnom stĺpci.
Ahoj, vitaj naspäť na netcast, som Bill Jelen. Dnešné otázky od Davida, ktorý bol na mojom seminári v Melbourne na Floride pre kapitolu IIA o pobrežnom vesmíre.
David má dva rôzne zošity, v ktorých je stĺpec A spoločný pre obidve z nich. Takže tu je zošit 1, tu je zošit 2 - obe majú kód produktu. Tento má položky, ktoré prvý nemá, alebo naopak, a David chce skombinovať všetky stĺpce. Máme tu teda tri stĺpce a štyri stĺpce tu. Obidve tieto slová som vložil do rovnakého zošita pre prípad, že si zošit stiahnete, aby ste spolu mohli pracovať. Vezmite každý z nich, presuňte ho do svojho vlastného zošita a uložte ho.
Dobre, na spojenie týchto súborov použijeme Power Query. Power Query je zabudovaný do Excelu 2016. Ak máte Windows vo verzii 10 alebo 13, môžete ísť do spoločnosti Microsoft a stiahnuť si Power Query. Môžete začať od nového prázdneho zošita s prázdnym listom. Chystáte sa uložiť tento súbor - Uložiť ako, viete, možno pracovný zošit, aby sa zobrazili výsledky kombinovaných súborov .xlsx. Dobre? A čo urobíme, urobíme dva dotazy. Prejdeme k údajom, získaniu údajov, zo súboru, zo zošita a potom vyberieme prvý súbor. V ukážke vyberte hárok, ktorý obsahuje vaše údaje, a my s týmito údajmi nemusíme nič robiť. Stačí teda otvoriť pole načítania a zvoliť možnosť Načítať, Iba vytvoriť pripojenie, kliknite na tlačidlo OK. Perfektné. Teraz to zopakujeme pre druhú položku - Data, From File,V zošite zvoľte DavidTwo, vyberte názov hárka a potom otvorte načítanie, Načítať, Iba vytvoriť pripojenie. Uvidíte tu na tomto paneli, sú tu prítomné obe spojenia. Dobre.
Teraz skutočná práca - Dáta, Získať údaje, Kombinovať dotazy, Zlúčiť a potom v dialógovom okne Zlúčiť zvoliť DavidOne, DavidTwo a tento ďalší krok je úplne neintuitívny. Musíte to urobiť. Vyberte spoločný stĺpec alebo stĺpce - teda Produkt a Produkt. Dobre. A potom tu buďte veľmi opatrní pri type spojenia. Chcem všetky riadky z oboch, pretože jeden môže mať ďalší riadok a ten musím vidieť, a potom klikneme na OK. Dobre. A tu je prvý výsledok. Nevyzerá to, že to fungovalo; nevyzerá to, že by sa pridali ďalšie položky, ktoré boli v súbore 2. A máme tento stĺpec 5 - teraz je nulový. Idem pravým tlačidlom myši na stĺpec 5 a poviem: Odstrániť tento stĺpec. Takže otvorte túto ikonu rozbalenia a zrušte začiarknutie tohto políčka Ako predponu použiť pôvodný názov stĺpca a BAM! funguje to. Takže ďalšie položky, ktoré boli v súbore 2, ktoré sa nenachádzajú v súbore 1,sa objavia.
Dobre. V dnešnom súbore to vyzerá, že tento stĺpec Kód produktu je lepší ako tento stĺpec Produkt, pretože obsahuje ďalšie riadky. Ale môže sa stať, že v budúcnosti bude mať Workbook 1 veci, ktoré Workbook 2 nemá. Takže ich tam oboch nechám a nezbavím sa žiadnych nul, pretože, aj keď sa tento riadok v dolnej časti javí ako úplne neplatný, v budúcnosti môže nastať situácia, keď máme tu niekoľko nulových hodnôt, pretože niečo chýba. Dobre? Takže konečne Zatvorte a načítajte a máme šestnásť riadkov.
Teraz, v budúcnosti, povedzme, že sa niečo mení. Dobre, vrátime sa teda k jednému z týchto dvoch súborov a zmením triedu pre Apple na 99 a vložme dokonca niečo nové a uložme tento zošit. Dobre. A potom, ak chceme, aby sa náš zlúčený súbor aktualizoval, poď sem - teraz, dávaj pozor, keď to urobíš prvýkrát, nevidíš ikonu Obnoviť - musíš chytiť tento panel a pretiahnuť ho cez . A urobíme Obnoviť a po načítaní 17 riadkov sa objaví melón, Apple sa zmení na 99 - je to krásna vec. Teraz, hej, chceš sa dozvedieť niečo o Power Query? Kúpte si túto knihu od Kena Pulsa a Miguela Escobara, M je pre (DATA) MONKEY. Naštartujem ťa.
Zbalenie dnes: David z Floridy má dva pracovné zošity, ktoré chce skombinovať; obidve majú rovnaké stĺpce v stĺpci A, ale ostatné stĺpce sú odlišné; jeden zošit môže obsahovať ďalšie položky, ktoré nie sú v tom druhom, a David ich chce; v žiadnom súbore nie sú duplikáty; na vyriešenie tohto problému použijeme power query, takže začneme v novom prázdnom zošite na prázdnom pracovnom hárku; urobíte tri dotazy, prvý - Data, Zo súboru, Zošit a potom Načítať iba vytvorené pripojenie; to isté pre druhý zošit, a potom Údaje, Získať údaje, Zlúčiť, vyberte dve spojenia, vyberte stĺpec, ktorý je v oboch spoločný - v mojom prípade Produkt - a potom z typu spojenia sa chcete úplne spojiť všetko zo súboru 1, všetko zo súboru 2. A potom je krásne, že sa zmenia základné údaje,môžete iba obnoviť dopyt.
Ak si chcete zošit stiahnuť z dnešného videa, navštívte adresu URL v popise služby YouTube.
No, hej, chcem ako Dávid, že sa objavil na mojom seminári, chcem ti poďakovať, že si sa zastavil. Uvidíme sa nabudúce na ďalšom netcaste od.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: combine-based-on-common-column.xlsx
Power Query je úžasný nástroj v programe Excel.
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Pri načítaní rozsahu alebo matice vo funkcii vždy stlačte kláves F4“
Tanja Kuhn
