Kombinujte na základě společného sloupce - Excel tipy

David z Floridy se ptá na dnešní otázku:

Mám dva sešity. Oba mají ve sloupci A stejná data, ale zbývající sloupce se liší. Jak mohu sloučit tyto dva sešity?

Zeptal jsem se Davida, zda je možné, že jeden sešit má více záznamů než druhý. A odpověď je ano. Zeptal jsem se Davida, zda se klíčové pole v každém souboru objevuje pouze jednou. Odpověď je také ano. Dnes to vyřeším pomocí Power Query. Nástroje Power Query najdete ve verzích Windows 2016 Excel+ v části Získat a transformovat na kartě Data. Pokud máte verze Excel 2010 nebo Excel 2013 pro Windows, můžete si pro tyto verze stáhnout doplněk Power Query.

Tady je Davidův sešit 1. Obsahuje Produkt a poté tři sloupce dat.

První sešit

Tady je Davidův sešit 2. Obsahuje kód produktu a další sloupce. V tomto příkladu jsou v sešitu2 další produkty, ale řešení budou fungovat, pokud některý sešit obsahuje další sloupce.

Druhý sešit

Tady jsou kroky:

  1. Vyberte Data, Načíst data, Ze souboru, Ze sešitu:

    Načíst data ze souboru
  2. Přejděte k prvnímu sešitu a klikněte na OK
  3. V dialogovém okně Navigátor vyberte list vlevo. (I když existuje pouze jeden list, musíte jej vybrat.) Data uvidíte vpravo.
  4. V dialogovém okně Navigátor otevřete rozevírací nabídku Načíst a zvolte Načíst do …
  5. Zvolte Pouze vytvořit připojení a stiskněte OK.

  6. Opakujte kroky 1–5 pro druhý sešit.

    Vytvořte připojení k sešitu

    Pokud jste provedli oba sešity, měli byste vidět dvě připojení na panelu Dotazy a připojení v pravé části obrazovky aplikace Excel.

    Připojení k oběma sešitům

    Pokračujte kroky ke sloučení sešitů:

  7. Data, získávání dat, kombinace dotazů, sloučení.

    Sloučit dva dotazy s různými sloupci
  8. Z horního rozevíracího seznamu v dialogovém okně Sloučit vyberte první dotaz.
  9. Z druhého rozevíracího seznamu v dialogovém okně Sloučit vyberte druhý dotaz.
  10. Klikněte na záhlaví produktu v horním náhledu (toto je klíčové pole. Všimněte si, že můžete vybrat více nebo více klíčových polí pomocí Ctrl + kliknutí)
  11. Ve druhém náhledu klikněte na nadpis Kód produktu.

  12. Otevřete typ spojení a vyberte Úplný vnější (všechny řádky z obou)

    Zde jsou znázorněny kroky 8 - 12

  13. Klikněte na OK. Náhled dat nezobrazuje další řádky a v posledním sloupci zobrazuje pouze „Tabulka“.

    To nevypadá slibně
  14. Všimněte si, že v záhlaví DavidTwo je ikona „Rozbalit“. Klikněte na tuto ikonu.

  15. Volitelné, ale vždy zruším výběr „Použít původní název sloupce jako předponu“. Klikněte na OK.

    Rozbalte pole ze sešitu 2

    Výsledky jsou uvedeny v tomto náhledu:

    Všechny záznamy z jednoho sešitu
  16. V Power Query použijte Domů, Zavřít a načíst.

Tady je krásná funkce: pokud se podkladová data v jednom sešitu změní, můžete klepnutím na ikonu Obnovit načíst nová data do sešitu výsledků.

Zopakujte kroky 1–16 kliknutím na tuto ikonu Obnovit.

Poznámka

Ikona Obnovit je obvykle skrytá. Ikonu zobrazíte přetažením levého okraje podokna Dotazy a připojení doleva.

Sledovat video

Přepis videa

Naučte se Excel z Podcastu, epizoda 2216: Zkombinujte dva sešity založené na společném sloupci.

Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka je od Davida, který byl na mém semináři v Melbourne na Floridě pro kapitolu IIA týkající se vesmírného pobřeží.

David má dva různé sešity, kde je sloupec A společný mezi oběma. Tady je sešit 1, tady sešit 2 - oba mají kód produktu. Tenhle má položky, které první nemá, nebo naopak, a David chce kombinovat všechny sloupce. Máme zde tedy tři sloupce a čtyři sloupce zde. Oba jsem vložil do stejného sešitu, pro případ, že si sešit stáhnete a budete spolu pracovat. Vezměte každou z nich, přesuňte ji do svého vlastního sešitu a uložte ji.

Dobře, abychom tyto soubory spojili, použijeme Power Query. Power Query je integrován do aplikace Excel 2016. Pokud používáte verzi Windows 10 nebo 13, můžete přejít do Microsoftu a stáhnout si Power Query. Můžete začít z nového prázdného sešitu s prázdným listem. Chystáte se uložit tento soubor-- Uložit jako, víte, možná sešit, aby se zobrazily výsledky kombinovaných souborů .xlsx. V pořádku? A to, co uděláme, je, že uděláme dva dotazy. Půjdeme na Data, Získat data, Ze souboru, Ze sešitu a pak vybereme první soubor. V náhledu vyberte list, který obsahuje vaše data, a my s těmito daty nemusíme nic dělat. Stačí tedy otevřít pole pro načítání a zvolit Načíst, Pouze vytvořit připojení, kliknout na OK. Perfektní. Nyní to zopakujeme pro druhou položku - Data, Ze souboru,V sešitu zvolte DavidTwo, zvolte název listu a poté otevřete načtení, Načíst, Pouze vytvořit připojení. Uvidíte zde v tomto panelu, máme obě připojení k dispozici. V pořádku.

Nyní skutečná práce - Data, Získat data, Zkombinovat dotazy, Sloučit a poté v dialogovém okně Sloučit zvolit DavidOne, DavidTwo a tento další krok je zcela neintuitivní. Musíte to udělat. Vyberte sloupec nebo sloupce společné - tedy Produkt a Produkt. V pořádku. A zde buďte velmi opatrní u typu spojení. Chci všechny řádky z obou, protože jeden může mít další řádek a musím to vidět, a pak klikneme na OK. V pořádku. A tady je první výsledek. Nevypadá to, že to fungovalo; nevypadá to, že by přidal další položky, které byly v souboru 2. A máme tento sloupec 5 - nyní je neplatný. Chystám se kliknout pravým tlačítkem na sloupec 5 a říct: Odebrat tento sloupec. Takže otevřete tuto ikonu rozbalení a zrušte zaškrtnutí tohoto políčka Použít jako předponu původní název sloupce a BAM! funguje to. Takže další položky, které byly v souboru 2, které nejsou v souboru 1,se objeví.

V pořádku. Nyní v dnešním souboru to vypadá, že tento sloupec Kód produktu je lepší než tento sloupec Produkt, protože má další řádky. Ale v budoucnu může existovat den, kdy sešit 1 obsahuje věci, které sešit 2 nemá. Takže je tam nechám oba a nebudu se zbavovat žádných nul, protože stejně, i když se tento řádek v dolní části jeví jako zcela null, může v budoucnu nastat situace, kdy máme tady pár nul, protože něco chybí. V pořádku? Takže konečně Zavřít a načíst a máme šestnáct řádků.

Nyní, v budoucnu, řekněme, že se něco změní. Dobře, takže se vrátíme k jednomu z těchto dvou souborů a změním třídu pro Apple na 99 a pojďme dokonce vložit něco nového a uložit tento sešit. V pořádku. A pak, pokud chceme, aby se náš slučovací soubor aktualizoval, pojď sem - teď pozor, když to uděláš poprvé, nevidíš ikonu Obnovit - musíš chytit tento pruh a přetáhnout ho přes . A uděláme Refresh a načteno 17 řádků, objeví se meloun, Apple se změní na 99 - je to krásná věc. Ahoj, chceš se dozvědět něco o Power Query? Kupte si tuto knihu od Kena Pulse a Miguela Escobara, M je pro (DATA) MONKEY. Dostanu vás do rychlosti.

Shrnutí dnes: David z Floridy má dva sešity, které chce kombinovat; oba mají ve sloupci A stejná pole, ale ostatní sloupce jsou různé; jeden sešit může mít další položky, které nejsou v tom druhém, a David je chce; v žádném souboru nejsou duplikáty; k vyřešení toho použijeme mocninový dotaz, takže začněte v novém prázdném sešitu na prázdném listu; uděláte tři dotazy, první - Data, Ze souboru, Sešit a potom Načíst pouze vytvořené připojení; totéž pro druhý sešit a poté Data, Získat data, Sloučit, vyberte dvě připojení, vyberte sloupec, který je v obou běžný - v mém případě Produkt - a poté z Typu připojení se chcete plně připojit vše ze souboru 1, vše ze souboru 2. A pak je krásné, že se změní podkladová data,stačí obnovit dotaz.

Chcete-li sešit stáhnout z dnešního videa, přejděte na adresu URL v popisu YouTube.

No, hej, chci, aby se David objevil na mém semináři, chci ti poděkovat, že jsi se zastavil. Uvidíme se příště na dalším netcastu z.

Stáhněte si soubor Excel

Stažení souboru aplikace Excel: combine-based-on-common-column.xlsx

Power Query je úžasný nástroj v aplikaci Excel.

Excel myšlenka dne

Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:

"Při čtení rozsahu nebo matice ve funkci vždy stiskněte klávesu F4"

Tanja Kuhn

Zajímavé články...