Domov Android

Jak sloučit 2 buňky v excelové tabulce.

25.07.2024

V tomto článku se dozvíte, jak rychle sloučit data ze dvou excelových tabulek, když se klíčové sloupce přesně neshodují. Například když jedinečný identifikátor z první tabulky je prvních pět znaků identifikátoru z druhé tabulky. Všechna řešení navrhovaná v tomto článku jsem testoval v Excelu 2013, 2010 a 2007.

Existují tedy dva listy Excelu, které je třeba zkombinovat pro další analýzu dat. Předpokládejme, že jedna tabulka obsahuje ceny (sloupec Cena) a popis zboží (sloupec Pivo), které prodáváte, a druhá tabulka zobrazuje údaje o dostupnosti zboží na skladě (sloupec Skladem). Pokud jste vy nebo vaši kolegové sestavili obě tabulky pro katalog, pak obě musí obsahovat alespoň jeden klíčový sloupec s jedinečnými identifikátory produktu. Popis produktu nebo cena se mohou změnit, ale jedinečný identifikátor zůstává vždy stejný.

Potíže začínají, když od výrobce nebo z jiných oddělení společnosti obdržíte nějaké tabulky. Věci se mohou ještě zkomplikovat, pokud se náhle zavede nový formát jedinečných identifikátorů nebo pokud se skladové skladové jednotky (SKU) změní byť jen nepatrně. A vy stojíte před úkolem kombinovat nové a staré tabulky s daty v Excelu. Tak či onak nastává situace, kdy existuje pouze částečná shoda záznamů v klíčových sloupcích, například „ 12345 " A " 12345-nová_přípona“. Chápete, že se jedná o stejné SKU, ale počítač není tak chytrý! Tato nepřesná shoda znemožňuje použití běžných vzorců Excelu ke kombinaci dat ze dvou tabulek.

A co je opravdu špatné, je to, že korespondence mohou být zcela nejasné a „ Nějaká společnost“ v jedné tabulce se může změnit na “ CJSC "Nějaká společnost"“ v jiné tabulce a „ Nová společnost (dříve Some Company)" A " Stará společnost“ se také ukáže jako záznam stejné společnosti. Víte to, ale jak to může Excel vysvětlit?

Komentář:Řešení popsaná v tomto článku jsou univerzální. Můžete je přizpůsobit pro další použití s ​​libovolnými standardními recepturami jako např SVYHLEDAT(VYHLEDAT) VYHLEDÁVÁNÍ(ZÁPAS) GPR(HLOOKUP) a tak dále.

Vyberte vhodný příklad a přejděte přímo k řešení, které potřebujete:

Klíčový sloupec v jedné z tabulek obsahuje další znaky

Podívejme se na dvě tabulky. Sloupce první tabulky obsahují číslo produktu (SKU), název piva (Beer) a jeho cenu (Price). Druhá tabulka zaznamenává SKU a počet lahví na skladě (Skladem). Místo piva může být jakýkoli produkt a počet sloupců v reálném životě může být mnohem větší.

V tabulce s dalšími symboly vytvořte pomocný sloupec. Můžete jej přidat na konec tabulky, ale je nejlepší jej vložit vpravo za sloupec klíče, aby byl viditelný.

Klíčový sloupec v tabulce v našem příkladu je A s daty SKU a musíte z něj extrahovat prvních 5 znaků. Přidáme pomocný sloupec a pojmenujeme jej Pomocník SKU:

Připraveno! Nyní máme klíčové sloupce s přesnou shodou hodnot - sloupec Pomocník SKU v hlavní tabulce a sloupci SKU v tabulce, kde bude vyhledávání provedeno.

Nyní pomocí funkce SVYHLEDAT(VLOOKUP) získáme požadovaný výsledek:

Jiné vzorce

  • Nejprve extrahujte X znaků vpravo: například 6 znaků vpravo od položky „DSFH-164900“. Vzorec bude vypadat takto:

    RIGHT(A2;6)
    =RIGHT(A2;6)

  • Nejprve přeskočte X znaků, extrahujte následující Y znaky: například potřebujete extrahovat „0123“ ze záznamu „PREFIX_0123_SUFF“. Zde musíme přeskočit prvních 8 znaků a extrahovat další 4 znaky. Vzorec bude vypadat takto:

    PSTR(A2;8;4)
    =MID(A2;8;4)

  • Extrahujte všechny znaky až do oddělovače délka výsledné sekvence se může lišit. Například potřebujete extrahovat „123456“ a „0123“ ze záznamů „123456-suffix“ a „0123-suffix“. Vzorec bude vypadat takto:

    LEFTSIMB(A2;FIND("-;A2)-1)
    =LEFT(A2,FIND("-",A2)-1)

Zkrátka můžete využít funkce Excelu jako např LEVSIMV(VLEVO, ODJET), ŽE JO(ŽE JO), PSTR(STŘEDNÍ), NALÉZT(FIND) k načtení jakýchkoli částí složeného indexu. Pokud s tím budete mít nějaké potíže, uděláme vše pro to, abychom vám pomohli.

Data z klíčového sloupce v první tabulce jsou rozdělena do dvou nebo více sloupců ve druhé tabulce

Předpokládejme, že tabulka, kterou hledáte, obsahuje sloupec s identifikátory. Buňky v tomto sloupci obsahují položky jako XXXX-YYYY, Kde XXXX– jedná se o kódové označení skupiny zboží (mobilní telefony, televize, videokamery, fotoaparáty) a YYYY– toto je kód produktu v rámci skupiny. Hlavní tabulka se skládá ze dvou sloupců: jeden obsahuje kódy skupiny produktů (Group), druhý obsahuje kódy produktů (ID). Nemůžeme jednoduše zahodit kódy skupin produktů, protože stejný kód produktu se může opakovat v různých skupinách.

V buňce C2 Napíšeme si následující vzorec:

CONCATENATE(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Tady A2– toto je adresa buňky obsahující kód skupiny; symbol" – ” je oddělovač; B2– toto je adresa buňky obsahující kód produktu. Zkopírujeme vzorec na zbývající řádky.

Nyní zkombinovat data z našich dvou tabulek nebude těžké. Sloupec sladíme Úplné ID první tabulka se sloupcem ID druhý stůl. Pokud je nalezena shoda, záznamy ze sloupců Popis A Cena druhá tabulka bude přidána k první tabulce.

Údaje v klíčových sloupcích se neshodují

Zde je příklad: Jste majitelem malého obchodu, dostáváte zboží od jednoho nebo více dodavatelů. Každý z nich má svou vlastní nomenklaturu, odlišnou od té vaší. V důsledku toho nastávají situace, kdy váš záznam „Case-Ip4S-01“ odpovídá záznamu „SPK-A1403“ v souboru Excel obdrženém od dodavatele. Takové nesrovnalosti se vyskytují náhodně a neexistuje žádné obecné pravidlo pro automatický převod „SPK-A1403“ na „Case-Ip4S-01“.

Špatné zprávy: Data obsažená v těchto dvou excelových tabulkách budou muset být zpracována ručně, aby je bylo možné později zkombinovat.

Dobré zprávy: Toto stačí provést pouze jednou a výslednou pomocnou tabulku lze uložit pro budoucí použití. Dále můžete tyto tabulky automaticky sloučit a ušetřit tak spoustu času

1. Vytvořte pomocnou tabulku pro vyhledávání.

Vytvořte nový list aplikace Excel a pojmenujte jej SKU převodník. Kopírování celého sloupce Naše.SKU z listu Obchod na nový list, odstraňte duplikáty a ponechte v něm pouze jedinečné hodnoty.

Přidejte vedle něj sloupec Supp.SKU a ručně hledat shodu mezi hodnotami sloupce Naše.SKU A Supp.SKU(s tím nám pomohou popisy ze sloupce Popis). To je nudná práce, ať máte radost z pomyšlení, že to musíte udělat jen jednou :-).

V důsledku toho máme následující tabulku:

2. Aktualizujte hlavní tabulku pomocí dat z vyhledávací tabulky.

Vložit nový sloupec do hlavní tabulky (list obchodu) Supp.SKU.

Dále pomocí funkce SVYHLEDAT(VLOOKUP) porovnat listy Obchod A SKU převodník Naše.SKU a pro aktualizované údaje – sloupec Supp.SKU.

Sloupec Supp.SKU vyplněny originálními kódy výrobce.

Komentář: Pokud ve sloupci Supp.SKU objeví se prázdné buňky, pak musíte vzít všechny kódy SKU odpovídající těmto prázdným buňkám, přidejte je do tabulky SKU převodník a vyhledejte odpovídající kód v tabulce dodavatelů. Poté opakujte krok 2.

3. Přeneste data z vyhledávací tabulky do hlavní tabulky

Naše hlavní tabulka má klíčový sloupec s přesnou shodou pro záznamy vyhledávací tabulky, takže tento úkol je nyní snadný

Pomocí funkce SVYHLEDAT(VLOOKUP) sloučit data listu Obchod s daty listu Velkoobchodní dodavatel 1 pomocí sloupce k nalezení shod Supp.SKU.

Zde je příklad aktualizovaných dat ve sloupci Velkoobchodní cena:

Je to jednoduché, že? Ptejte se v komentářích k článku, pokusím se odpovědět co nejdříve.

Začínající uživatelé se obávají některých případů, které jim brání v práci v balíku kancelářského softwaru. Jednou z těchto situací je, když nastane problém, jak propojit dvě tabulky ve Wordu. Koneckonců, algoritmus akcí se liší v závislosti na verzi aplikace Microsoft Word.

Představení aplikace Microsoft Word

Pravděpodobně každý uživatel již zná program jako je Word z kancelářského balíku Microsoft Office. Tento textový editor je nejoblíbenější na celém světě. Jsem rád, že Word poskytuje mnoho možností pro psaní a formátování textu. A pokud máte otázky týkající se „nebo jak vložit obrázek“, můžete najít odpověď, která je přístupná a srozumitelná všem.

Jaké jsou tedy hlavní vlastnosti Wordu:

  • Vytvářejte seznamy s odrážkami a číslované seznamy.
  • Zobrazit netisknutelné znaky.
  • Zarovnání fragmentu textu do různých pozic.
  • Vkládání zalomení stránek a odstavců.
  • Účel záhlaví a zápatí.
  • Vkládání obrázků, čísel stránek, různých objektů z jiných programů, symbolů, které nejsou na klávesnici.
  • Tvorba tabulek (matric) a uměleckých předmětů.

A to nejsou všechny možnosti textového editoru.

Spojování tabulek ve Wordu 2003

Pro kombinaci dvou matic ve Wordu musí být splněna jedna hlavní podmínka: počet sloupců musí být stejný. Chcete-li se dostat z této situace a odpovědět na otázku, jak propojit dvě tabulky v aplikaci Word 2003, musíte:

  • umístěte kurzor na čáru přerušení mezi dva objekty;
  • Stiskněte několikrát tlačítko „Smazat“, dokud nedojde ke sloučení.

Pokud se šířka sloupců jedné matice liší od dat indikátorů v jiném objektu, musíte použít příkaz „Zarovnat šířky sloupců“. Chcete-li ji vyvolat, vyberte tabulku, klikněte pravým tlačítkem a vyberte požadovanou položku. Není třeba se bát, že by podle tabulkových údajů měl být jeden sloupec širší, druhý užší. Po sloučení objektů můžete tento indikátor ručně upravit.

Spojování tabulek ve Wordu 2007 a dalších verzích

V situaci, kdy není jasné, jak propojit dvě tabulky ve Wordu 2007, existuje velmi jednoduché řešení. Tuto operaci zvládne i začínající uživatel. Chcete-li to provést, stačí dodržovat následující algoritmus:

  1. Vyberte objekt, který bude kombinován s jeho „druhou“ polovinou.
  2. Zkopírujte fragment pomocí jedné ze známých metod:
    • stiskněte CTRL + C;
    • klikněte pravým tlačítkem (pravým tlačítkem myši) na zadaný fragment a vyberte „Kopírovat“;
    • Klikněte na tlačítko „Kopírovat“ na panelu nástrojů.
  3. Umístěte kurzor nad nebo pod původní matici, se kterou bude sloučena.
  4. Na tlačítku „Vložit“ na panelu nástrojů je šipka. Musíte na něj kliknout a vybrat „Vložit tabulku přidáním“. Případně se při vkládání tabulky objeví malé tlačítko s kontextovou nabídkou, ze které lze vybrat jeden z potřebných příkazů.

Tento algoritmus je vhodný nejen pro verzi 2007, ale i pro ostatní, protože následné verze Wordu nepřinesly globální změny do rozhraní programu. Proto při přemýšlení o tom, jak propojit dvě tabulky ve Wordu 2010, postupujte podle zadaného algoritmu. Hlavní věc je nezapomenout odstranit další znaky a mezery mezi dvěma objekty.

Nyní by nikoho neměla trápit otázka, jak propojit dvě tabulky ve Wordu.

Textový editor společnosti Microsoft má obrovský seznam funkcí a nástrojů, takže něco tak jednoduchého, jako je spojení tabulky, lze ve Wordu 2007 snadno provést. Tomuto tématu bude věnován tento článek.

Jak spojit tabulky ve Wordu

Než se pustíte přímo do návodu, jak spojit dvě samostatné tabulky, stojí za zmínku, že existuje několik způsobů, jak tento úkol splnit. První zahrnuje použití funkce "Vyjmout". Mimochodem, s jeho pomocí můžete kombinovat stůl vertikálně a horizontálně. Druhá možnost využívá další možnost textového editoru – „Zobrazení“.

Metoda 1: Použití možnosti "Vyjmout".

Pojďme tedy přímo k tomu, jak propojit tabulky ve Wordu pomocí možnosti "Vyjmout". Připomeňme, že pomocí této metody můžete provádět horizontální i vertikální slučování.

Vertikální asociace

V programu otevřeme textový dokument a uvidíme dvě tabulky. Naším úkolem je spojit je do jednoho tak, aby druhý byl pod prvním. Chcete-li to provést, budete muset provést následující:

  1. Proveďte úplný výběr druhé tabulky. K tomu nebude stačit zvýraznit obsah v buňkách. Musíte kliknout na ikonu v pravém horním rohu.
  2. Vystřihněte stůl umístěním do schránky. To lze provést dvěma způsoby: stisknutím horkých kláves Ctrl+X nebo kliknutím na tlačítko „Vyjmout“, které se nachází na panelu nástrojů ve skupině „Schránka“.
  3. Umístěte kurzor bezprostředně pod první tabulku. Měl by se objevit přesně pod levým okrajem.
  4. Vložte prvek, který jste zkopírovali dříve. To lze také provést dvěma způsoby: stisknutím horkých kláves Ctrl+V nebo tlačítka "Vložit" na panelu nástrojů.

Ihned poté dojde k vertikálnímu sloučení první a druhé tabulky. Podle toho lze zadaný úkol považovat za splněný.

Horizontální fúze

Vertikálně spojovat tabulky ve Wordu už víme, ale co když potřebujeme umístit druhou napravo od první. Chcete-li to provést, můžete použít stejné pokyny a provést některé úpravy.

  1. Zcela vyberte tabulku, kterou chcete sloučit s jinou.
  2. Vystřihněte to.
  3. Umístěte kurzor na pravou stranu tabulky. Pokud vše uděláte správně, mělo by blikat hned po prvním řádku.
  4. Vložte tabulku, kterou jste zkopírovali dříve.

Zjistíte, že obě tabulky byly sloučeny do jedné.

Metoda 2: prostřednictvím možnosti "Zobrazit neviditelné znaky"

Jak již bylo zmíněno dříve, existuje druhý způsob připojení tabulek ve Wordu. Mimochodem, bude to vyžadovat méně času a úsilí, ale má to jednu významnou nevýhodu - horizontální sloučení se neprovádí. Pokud jste s tím spokojeni, postupujte podle následujícího algoritmu akcí:

  1. Spusťte textový editor a přejděte na hlavní kartu.
  2. Na panelu nástrojů klikněte na tlačítko "Zobrazit neviditelné znaky", které se nachází ve skupině nástrojů "Odstavec".
  3. Poté se na všech listech objeví další symboly, které odpovídají jejich roli v textu. Zajímá nás znak odstavce (¶).
  4. Najděte tento symbol mezi první a druhou tabulkou a umístěte kurzor hned napravo od něj.
  5. Stiskněte Backspace.

Bezprostředně poté mezera mezi tabulkami zmizí a dojde k jejich sloučení.

Závěr

Nyní víte, jak propojit 2 tabulky ve Wordu. Stojí za zmínku, že všechny akce se provádějí velmi jednoduše, takže si je každý uživatel snadno zapamatuje. Dalším důležitým faktem zůstává, že vývojáři textového editoru poskytli právo zvolit si provedení úkolu: provést sloučení odstraněním značek odstavců nebo pomocí možnosti „Vyjmout“.

Řekněme, že máte dvě tabulky ve dvou různých listech, jedna je hlavní tabulka a druhá je nová tabulka dat. Nyní chcete spojit tyto dvě tabulky pomocí odpovídajícího sloupce a aktualizovat data, jak je uvedeno níže, jak můžete tento problém rychle vyřešit v aplikaci Excel? V tomto článku se podělím o triky, jak rychle spojit dvě tabulky podle sloupce.

Snadno sloučte dvě tabulky a aktualizujte nové položky v Excelu

V některých případech můžete sloučit dvě tabulky do jedné a aktualizovat je a přidávat nové. Kutools pro Excel"s Sloučit tabulky Obslužný program může sloučit hlavní tabulku s vyhledávací tabulkou do jedné a aktualizovat hlavní tabulku podle zobrazeného náhledu. Klikněte pro plně funkční 60denní bezplatnou zkušební verzi!
Kutools pro Excel: S více než 300 uživatelsky přívětivými doplňky Excelu, bez 60denního limitu.

Umožňuje vám pracovat chytřeji, rychleji a lépe v Excelu

Sloučení dvou tabulek pomocí sloupce pomocí funkce VLOOKUP

Chcete-li spojit dvě tabulky podle sloupce, můžete použít funkci VLOOUP.

1. Vyberte prázdnou buňku vedle hlavní tabulky, například C2. Viz snímek obrazovky:

2. Zadejte tento vzorec =IFERROR(VLOOKUP($A2,List2!$A$1:$C$6,SLOUPEC(A1);NEPRAVDA),"") do něj, pak přetáhněte úchyt automatického vyplňování, dokud se neobjeví prázdná buňka, a poté přetáhněte úchyt výplně dolů do buněk potřebných pro tento vzorec. Viz snímek obrazovky:

3. Do sloučené tabulky můžete přidat nová záhlaví sloupců. Viz snímek obrazovky:

Sloučit dvě tabulky pomocí sloupce pomocí funkce Sloučení tabulek

Funkce LOOKUP pro vás bude trochu oříšek, ale naštěstí existuje užitečná funkce Slučování tabulek v Kutools pro Excel který může rychle přidávat a aktualizovat nové prvky v hlavní tabulce na základě jiné tabulky.

1. Vyberte hlavní tabulku, kterou chcete použít k aktualizaci a sloučení s jinou, stiskněte K nástroje Plus > Slučování tabulek.

2. Poté dovnitř Slučování tabulek dialogovém okně průvodce vyberte vyhledávací tabulku Vyberte vyhledávací tabulku textové pole, zkontrolujte možnosti, pokud tabulky mají záhlaví.

3. Klikněte další, v kroku 2 průvodce zaškrtněte sloupec klíče, na základě kterého se chcete připojit.

4. Klikněte další, v kroku 3 průvodce zaškrtněte sloupce, které chcete aktualizovat.

5. Klikněte další V dalším kroku zaškrtněte nové sloupce, které chcete přidat do kmenových dat.

6. Klikněte další, v posledním kroku je potřeba u nových prvků nastavit některé parametry, například ty aktualizované zvýraznit barvou pozadí.

7. Klikněte Konec Hlavní tabulka byla aktualizována a doplněna o nové prvky.

Demo

Sloučit více tabulek podle záhlaví sloupců do jednoho listu pomocí Kutools pro Excel

Pokud chcete spojit více tabulek na listech nebo sešitech podle záhlaví sloupců do jednoho listu, jako je uvedeno níže, můžete použít výkonné Kombajn Funkce Kutools pro Excel k rychlému vyřešení tohoto problému.

Po instalaci Kutools pro Excel proveďte následující:( Stáhněte si Kutools pro Excel nyní!)

1. Zahrňte knihu (nebo knihy), které chcete sloučit, podle záhlaví sloupců, klikněte Společnost > Kombajn a v dialogovém okně vyberte Sloučení více listů ze sešitů do jednoho listu Viz snímek obrazovky:



2. Poté klikněte další jít do Krok 2 ze 3 a zaškrtněte názvy sešitů a listů, které chcete sloučit, ze dvou seznamů. Viz snímek obrazovky:

3. Klikněte další jít do Krok 3 ze 3 Dialog. V posledním kroku dialogu v něm zadejte číslo titulního řádku, zde je, můžete také zadat další nastavení podle potřeby. Viz snímek obrazovky:

4. Klikněte Konec, zobrazí se dialogové okno, ve kterém můžete vybrat složku, do které chcete sloučený nový sešit umístit. A stiskněte Uložit jít na první Kutools pro Excel klepněte na dialogové okno, které vám připomene, zda chcete otevřít sloučený sešit Ano otevřít nebo ne a pak ten druhý Kutools pro Excel Zobrazí se dialogové okno, které vám připomene uložit tento kombinovaný skript, podle potřeby vyberte libovolné možnosti.





Listy byly nyní sloučeny do jednoho listu na základě záhlaví sloupců.

Spropitné. Pokud chcete mít zkušební verzi zdarma Kombajn Funkce, nejprve si zdarma stáhněte Kutools pro Excel a poté pokračujte v použití operace podle výše uvedených kroků.

Nástroje produktivity Excel

  • Přináší do Excelu více než 300 výkonných pokročilých funkcí, které vám umožní pracovat chytřeji, rychleji a lépe.
  • 18 funkcí vkládání, 12 textových funkcí, 22 funkcí listu, 12 tiskových nástrojů, 3 vyhledávací nástroje, 3 nové grafy, 16 nových funkcí a tak dále...
  • Staňte se expertem na Excel za 3 minuty a dokončete složitou a opakující se práci během několika sekund.
  • Omezte každý den tisíce operací klávesnice a myši, což zdaleka není nemoc z povolání.
  • 60denní plně funkční bezplatná zkušební verze. 60denní záruka vrácení peněz. 2 roky bezplatných aktualizací a podpory.
  • V současné době používá 120 000 vysoce výkonných lidí a 310 světově uznávaných společností.
  • Povolit úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu , Publisher, Access, Visio a Project.
  • Otevírejte a vytvářejte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50 % a snižuje za vás stovky kliknutí myší každý den!