V dnešním videu z minisérie „Revoluce v Excelu“ se podíváme na jeden z nejhorších problémů v Excelu, a tím je, jak spojit dohromady dvě zdrojové tabulky. V předešlých dílech z minisérie Revoluce v Excelu jsme se podívali na to, jak ze seznamu pomocí excelových funkcí odstranit duplicity nebo jak pomocí excelových funkcí seřadit hodnoty v tabulce. Představte si, že máme dvě tabulky, ve kterých máme zdrojová data. Jedna tabulka obsahuje prodejní data, třeba hodnoty ze leden a druhá za únor. Z těchto dat chceme určit nejvyšší tržbu nebo třeba sečíst tržby pro vybranou pobočku. Pokud máte data ve dvou tabulkách, tak se žádných kloudných výpočtů nedopočítáte. Nejprve musíme tabulky spojit dohromady.
Excelový soubor ke stažení
Sloučení tabulek
Tabulky musíme nejprve spojit do jedné. Pomineme řešení, že budeme tabulky pod sebe pouze kopírovat a rovněž pomineme, že můžeme tabulky spojit pomocí Power Query. My si ukážeme, jak se tabulky spojovaly dříve pomocí excelových funkcí a jak to dělají teď ti, co mají licenci Office 365.
Začneme již tradičně s klasickými excelovými funkcemi.
První co musíme udělat je vytvořit si dva pomocné sloupce ve zdrojových tabulkách, které budou určovat pořadí položek. Začneme u první tabulky. Vytvoříme sloupec Pořadí, kde nejprve napíšeme jedničku.
Na druhém řádku už použijeme pro pořadová čísla funkce. Začneme s funkcí KDYŽ, kde chceme v podmínce stanovit, že pokud buňka s produktem není prázdná, že chceme vrátit pořadovou hodnotu o jedno číslo vyšší než je předešlé číslo. Ve funkci KDYŽ tedy použijeme funkci JE.PRÁZDNÉ, kde označíme druhou buňku produktu, nicméně nás zajímá, pokud buňka není prázdná, takže funkci JE.PRÁZDNÉ zabalíme do funkce NE. A pokud buňka není prázdná, tak chceme vrátit předešlou hodnotu plus jeden, a pokud podmínka splněná nebude, tak chceme vrátit nic. Funkce ukončíme a stáhneme ji dolů a abychom zajistili, že se do sloučené tabulky přidají i nové hodnoty, tak funkci klidně protáhneme o několik řádků dolů. Teď máme vytvořený sloupec s pořadovým číslem hodnot. Pokud přidáme nový produkt, tak se pořadové číslo protáhne.
Teď musíme určit pořadí ještě ve druhé tabulce. Tam musíme pořadová čísla navázat na předchozí čísla u první tabulky. Takže do sloupce pořadí napíšeme funkci MAX, kde označíme celý pomocný sloupec pořadí u první tabulky a k němu přičteme jedničku. Funkce MAX najde nejvyšší hodnotu z prvního sloupce a přičte k němu jedničku.
Funkce na druhém řádku bude stejná jako v prvním sloupci. Podmínková funkce KDYŽ, kde podmínkou je, že pokud produktová buňka není prázdná, tak, že chceme vrátit pořadové číslo o jedno větší a pokud podmínka splněná není, tak chceme vrátit prázdnou buňku. Funkci ukončíme a stáhneme ji dolů a opět ji stáhneme na více řádků.
Teď se vrhneme na tvorbu sloučené tabulky. Do pomocného sloupce (sloupec M) nejprve napíšeme pořadová čísla, a to od 1 třeba do 19. V tabulkách máme dohromady 13 řádků, takže s 19 budeme mít rezervu.
Klikneme do první buňky a začneme tvořit funkci. Základem řešení funkce pro sloučení tabulek jsou kombinace funkcí KDYŽ, anglicky funkce IF, INDEX & POZVYHLEDAT, anglicky funkce INDEX & MATCH s funkcí POSUN neboli anglicky funkce OFFSET. Naším úkolem je, že musíme vyhledat, který produkt v tabulce náleží pořadovému číslu jedna, pak dva atd. Když vyhledávací funkce dojdou na konec první tabulky, tak je funkce POSUN posune do druhé tabulky, kde začne vyhledávat další pořadové čísla.
Začneme podmínkovou funkcí KDYŽ, ve funkci KDYŽ nejprve ověříme, že se první pořadové číslo shoduje s prvním pořadovým číslem u první tabulky. Obě buňky zafixujeme pro sloupec klávesou F4. Pokud je podmínka splněná, tak chceme vrátit první pořadové číslo, opět zafixované pro sloupec. A pokud podmínka splněná nebude, tak se chceme posunout do druhé tabulky. Takže funkce POSUN, kde zvolíme styčný bod, což bude buňka záhlaví s pořadím, plně zafixovaná. Následuje parametr řádky, zde musíme vytvořit dynamické rozpětí buněk, ve které se právě nacházíme, takže funkce ŘÁDKY, kde označíme buňku k té samé buňce, a první buňku plně zafixujeme. Od toho ale ještě musíme odečíst počet řádků v pomocném sloupci u první tabulky, buňky musí být plně zafixované. Nechceme se v rámci sloupců posouvat, takže v parametru sloupce vyplníme nulu. Ukončíme funkce a potvrdíme a stáhneme ji dolů. Funkce vrátila správnou řadu pořadových čísel. Jak to? Funkce KDYŽ ověřuje na každém řádku, zda se pořadové číslo shoduje s pořadovým číslem v první tabulce, pokud ano, vrací pořadové číslo. Takto funkce vrací pořadová čísla jedna až sedm z první tabulky. Když dojde na prázdný řádek, tak najednou podmínka neplatí a tím pádem se aktivuje funkce POSUN, která začne v buňce záhlaví. A začne se posouvat dolů, čímž vrací správná pořadová čísla.
Teď musíme pořadová čísla vyhledat pomocí funkce POZVYHLEDAT, anglicky funkce MATCH. Zabalíme funkci KDYŽ do funkce POZVYHLEDAT. Co hledáme je celá funkce KDYŽ a kde tato pořadová čísla hledáme? Hledáme je v prvním pomocném sloupci u první tabulky, rozpětí buněk plně zafixované a hledáme přesnou shodu. Ukončíme funkci a pošleme dolů. Vrátilo se prvních sedm hodnot správně. Tyto chybové hodnoty dořešíme za chvíli. Teď pomocí funkce INDEX, dohledáme správný název produktu. Funkce INDEX, kde označíme co hledáme, tedy sloupec s produkty a označíme stejný počet řádků jako máme ve funkci pořadí. Buňky plně zafixujeme. Parametr řádky je celá funkce POZVYHLEDAT. Funkci ukončíme, potvrdíme a funkci stáhneme dolů. Máme správně doplněno prvních sedm produktů.
Teď dořešíme tyto chybové hlášky. A to pomocí funkce IFERROR. Ve funkci IFERROR bude parametrem hodnota celá tato první funkce, a v parametru pokud chyba specifikujeme, co se má stát, když první funkce INDEX vrátí chybu. Pokud funkce INDEX vrátí chybu, tak se mají dohledat a doplnit hodnoty z druhé tabulky. Zabalíme to do funkce IFERROR a abychom si ušetřili práci, tak celou tuto funkci INDEX zkopírujeme a vložíme ji do parametru pokud chyba. Teď ji jen upravíme. Ve druhé funkci INDEX chceme vrátit názvy produktů z druhé tabulky. Ve druhé podmínce KDYŽ porovnáváme pořadová čísla s čísly ve druhé tabulce. Pokud bude podmínka splněná, chceme vrátit první pořadové číslo ze druhé tabulky a ještě musíme upravit prohledávanou oblast ve funkci POZVYHLEDAT, tedy kde pořadová čísla hledáme, a hledáme je ve druhé tabulce.
Funkce ukončíme a potvrdíme a stáhneme je dolů. A máme doplněnou sloučenou řadu produktů z obou tabulek. Teď jelikož máme buňky správně zafixované je můžeme posunout do ostatních
sloupců A máme doplněnou sloučenou tabulku. Zkusíme přidat nový produkt do obou tabulek a jak vidíme, tak se tabulka aktualizuje.
To bylo náročné i pro pokročilé znalce excelových funkcí.
A jak to vyřeší ten, kdo používá Office 365? Využije nejnovější excelovou funkci SROVNAT.SVISLE. Zapínáme stopky. Do první buňky napíšeme funkci SROVNAT.SVISLE. Ve funkci SROVNAT.SVISLE se označují jen oblasti buněk, které chceme sloučit dohromady. Označíme první tabulku, středník a druhou tabulku. Potvrdíme.
Pokud chceme zajistit, aby se přidaly i nové produkty, tak ve funkci SROVNAT.SVISLE označíme opět více řádků. Potvrdíme, tím se vrátí nuly. Zabalíme to do funkce FILTER, kde pole bude celá funkce SROVNAT.SVISLE a parametr zahrnuje bude ta samá funkce, kde akorát změníme odkaz na první sloupec ve funkci SROVNAT.SVISLE a podmínka je, že se tyto první sloupce nerovnají nule. Ukončíme funkci, potvrdíme. Hotovo.
Pokud se chcete dozvědět více o nových dynamických funkcích v Excelu, a jak vám práce s dynamickými poli a funkcemi zefektivní vaši práci v Excelu, tak na Akademii Excelu spouštíme kurz, který je celý zaměřený na práci s dynamickými poli. V kurzu Excel 365 Masterclass se podíváme nejen na to, jak pracovat s novými dynamickými funkcemi jako UNIQUE, FILTER nebo SORT a XLOOKUP, ale rovněž jsou v kurzu zařazené nejnovější funkce jako ROZDĚLIT.TEXT, SROVNAT.SVISLE, ZAHODIT, VZÍT a pokročilé funkce jako LET a LAMBDA. Kromě toho si ukážeme, jak potenciál dynamických polí zkombinovat s funkcemi jako SVYHLEDAT, KDYŽ nebo SUMIFS a mnohými dalšími. Nebo jak využít dynamická pole při tvorbě rozbalovacích seznamů, v excelových grafech nebo při práci s kontingenčními tabulkami. Pro více informací navštivte web Akademie Excelu. A já se na vás zatím budu těšit u dalšího videa, kde si ukážeme, jaký je rozdíl mezi vyhledáváním v Excelu s duplicitami ve starých Excelech a nových Excelech 365.
Jedna odpověď
👍