Dobrá databáze je vždy strukturovaná. To znamená, že různé entity mají různé tabulky. Ačkoli Excel není databázový nástroj, ale často se používá k udržování malých částí dat.
Mnohokrát dostaneme potřebu sloučit tabulky, abychom také viděli vztah a vychrlili několik užitečných informací.
V databázích jako SQL, Oracle, Microsoft Access atd. Je snadné spojovat tabulky pomocí jednoduchých dotazů. Ale v aplikaci Excel nemáme JOIN, ale stále můžeme spojovat tabulky v aplikaci Excel. Ke sloučení a připojení datových tabulek používáme funkce Excelu. Možná je to přizpůsobitelnější sloučení než SQL. Podívejme se na techniky sloučení excelových tabulek.
Sloučit data v aplikaci Excel pomocí funkce VLOOKUP
Chcete -li sloučit data v aplikaci Excel, měli bychom mít v obou tabulkách alespoň jeden společný faktor/ID, abychom je mohli použít jako relaci a sloučit tyto tabulky.
Zvažte tyto dvě tabulky níže. Jak můžeme tyto datové tabulky zkombinovat v Excelu?
Tabulka objednávek obsahuje podrobnosti objednávky a tabulka zákazníků obsahuje podrobnosti o zákazníkovi. Musíme připravit tabulku, která řekne, ke které objednávce patří jméno zákazníka, body zákazníka, číslo domu a datum připojení.
Společné ID v obou tabulkách je Vlastní ID které lze použít pro sloučení tabulek v aplikaci Excel.
Existují tři způsoby sloučení tabulek pomocí funkce VLOOKUP.
Načíst každý sloupec pomocí indexu sloupce
V této metodě použijeme jednoduché VLOOKUP k přidání těchto tabulek do jedné tabulky. Chcete -li získat jméno, napište tento vzorec.
[Zákazníci je list 1! $ I $ 3: $ M $ 15]
= VLOOKUP (B3,Zákazníci,2,0)
Chcete -li sloučit body v tabulce, napište tento vzorec.
= VLOOKUP (B3,Zákazníci,3,0)
Chcete -li sloučit číslo domu v tabulce, napište tento vzorec.
= VLOOKUP (B3,Zákazníci,4,0)
Zde jsme sloučili dvě tabulky v Excelu, každý sloupec jeden po druhém v tabulce. To je užitečné, pokud máte jen několik sloupců ke sloučení. Ale když máte více sloupců ke sloučení, může to být hektický úkol. Abychom mohli sloučit více tabulek, máme různé přístupy.
Sloučit tabulky pomocí VLOOKUP a Funkce COLUMN.
Pokud chcete načíst více sousedních sloupců, použijte tento vzorec.
=VLOOKUP(lookup_value,tabulka_pole,SLOUPEC()-n, 0)
Zde funkce COLUMN vrací pouze čísla sloupců, ve kterých je vzorec zapisován.
n je libovolné číslo, které upravuje číslo sloupce v poli tabulky,
V našem příkladu bude vzorec pro slučování tabulek:
=VLOOKUP($ B3,Zákazníci,SLOUPEC()-2,0)
Jakmile tento vzorec napíšete, nebudete muset vzorec psát znovu pro ostatní sloupce. Stačí jej zkopírovat do všech ostatních buněk a sloupců.
Jak to funguje
OK! V prvním sloupci potřebujeme jméno, což je druhý sloupec v tabulce zákazníků.
Zde je hlavní faktor SLOUPEC()-2. Funkce COLUMN vrací číslo sloupce aktuální buňky. Píšeme vzorec v D3, proto dostaneme 4 z COLUMN (). Potom odečteme 2, což znamená 2. Proto se náš vzorec nakonec zjednoduší na =VLOOKUP($ B3,Zákazníci,2,0).
Když zkopírujeme tento vzorec do E sloupců, dostaneme vzorec jako =VLOOKUP($ B3,Zákazníci,3, 0). Který načte 3. sloupec z tabulky zákazníků.
Sloučit tabulky pomocí funkce VLOOKUP-MATCH
Toto je můj oblíbený způsob sloučení tabulek v aplikaci Excel pomocí funkce VLOOKUP. Ve výše uvedeném příkladu jsme získali sloupec v sérii. Ale co kdybychom museli načíst náhodné sloupce. V takovém případě budou techniky zbytečné. Technika VLOOKUP-MATCH používá ke sloučení buněk záhlaví sloupců. Toto se také nazývá VLOOKUP s Dynamic Col Index.
Pro náš příklad zde bude vzorec tento.
= VLOOKUP ($ B3,Zákazníci, MATCH (D $ 2,$ J $ 2: $ N $ 2,0),0)
Zde jednoduše používáme funkci MATCH k získání příslušného čísla sloupce. Toto se nazývá Dynamic VLOOKUP.
Použití INDEX-MATCH ke sloučení tabulek v aplikaci Excel
INDEX-MATCH je velmi účinný vyhledávací nástroj a mnohokrát je označován jako lepší funkce VLOOKUP. Toto můžete použít ke kombinaci dvou nebo více tabulek. To vám také umožní sloučit sloupce zleva od tabulky.
Toto byl rychlý návod ke sloučení a spojování tabulek v Excelu. Prozkoumali jsme několik způsobů sloučení dvou nebo více tabulek v Excelech. Neváhejte se zeptat na tento článek nebo jakýkoli jiný dotaz týkající se Excelu 2019, 2016, 2013 nebo 2010 v sekci komentáře níže.
Funkce IF, ISNA a VLOOKUP v Excelu
Funkce IFERROR a VLOOKUP v Excelu
Jak načíst celý řádek odpovídající hodnoty v aplikaci Excel
Oblíbené články:
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.