Obsah
Máme dvě tabulky (například stará a nová verze ceníku), které potřebujeme porovnat a rychle najít rozdíly:
Hned je jasné, že do nového ceníku něco přibylo (datle, česnek…), něco zmizelo (ostružiny, maliny…), u některého zboží se změnily ceny (fíky, melouny…). Všechny tyto změny musíte rychle najít a zobrazit.
Pro jakýkoli úkol v Excelu existuje téměř vždy více než jedno řešení (obvykle 4-5). Pro náš problém lze použít mnoho různých přístupů:
- funkce VPR (VYHLEDAT) — vyhledejte názvy produktů z nového ceníku ve starém a zobrazte starou cenu vedle nové a poté zachyťte rozdíly
- sloučit dva seznamy do jednoho a poté na jeho základě vytvořit kontingenční tabulku, kde budou rozdíly jasně viditelné
- použijte doplněk Power Query pro Excel
Vezměme je všechny popořadě.
Metoda 1. Porovnání tabulek pomocí funkce VLOOKUP
Pokud tuto úžasnou funkci vůbec neznáte, pak se nejprve podívejte sem a přečtěte si nebo se podívejte na videonávod – ušetříte si pár let života.
Obvykle se tato funkce používá k získávání dat z jedné tabulky do druhé pomocí shody s některým společným parametrem. V tomto případě jej použijeme k přesunutí starých cen do nové ceny:
Produkty, u kterých se ukázala chyba #N/A, nejsou ve starém seznamu, tj. byly přidány. Změny cen jsou také jasně viditelné.
Klady tato metoda: jednoduchá a jasná, „klasika žánru“, jak se říká. Funguje v jakékoli verzi Excelu.
Nevýhody je tam také. Chcete-li vyhledat produkty přidané do nového ceníku, budete muset provést stejný postup v opačném směru, tj. natáhnout nové ceny na starou cenu pomocí VLOOKUP. Pokud se zítra změní velikosti tabulek, bude nutné upravit vzorce. No a na opravdu velkých stolech (> 100 tisíc řádků) se všechno to štěstí slušně zpomalí.
Metoda 2: Porovnání tabulek pomocí pivotu
Zkopírujeme naše tabulky jednu pod druhou a přidáme sloupec s názvem ceníku, abyste později pochopili, ze kterého seznamu který řádek:
Nyní na základě vytvořené tabulky vytvoříme souhrn skrz Vložit – kontingenční tabulka (Vložit — kontingenční tabulka). Hodíme pole Produktový do oblasti čar, pole Cena na plochu sloupce a pole Цena do rozsahu:
Jak vidíte, kontingenční tabulka automaticky vygeneruje obecný seznam všech produktů ze starého a nového ceníku (žádné opakování!) a seřadí produkty podle abecedy. Jasně vidíte přidané produkty (nemají starou cenu), odebrané produkty (nemají novou cenu) a případné změny cen.
Celkové součty v takové tabulce nedávají smysl a lze je na kartě deaktivovat Konstruktor – Celkové součty – Zakázat pro řádky a sloupce (Design – celkové součty).
Pokud se změní ceny (ale ne množství zboží!), pak stačí vytvořený přehled jednoduše aktualizovat kliknutím pravým tlačítkem myši – Obnovit.
Klady: Tento přístup je u velkých tabulek řádově rychlejší než VLOOKUP.
Nevýhody: je potřeba ručně zkopírovat údaje pod sebe a přidat sloupec s názvem ceníku. Pokud se změní velikosti stolů, musíte vše opakovat.
Metoda 3: Porovnání tabulek s Power Query
Power Query je bezplatný doplněk pro Microsoft Excel, který umožňuje načítat data do Excelu z téměř jakéhokoli zdroje a poté tato data libovolným způsobem transformovat. V Excelu 2016 je tento doplněk již ve výchozím nastavení zabudován na kartě Data (Data), a pro Excel 2010-2013 si jej musíte stáhnout samostatně z webu společnosti Microsoft a nainstalovat – získat novou kartu Dotaz na napájení.
Před načtením našich ceníků do Power Query je třeba je nejprve převést na chytré tabulky. Chcete-li to provést, vyberte rozsah s daty a stiskněte kombinaci na klávesnici Ctrl+T nebo vyberte kartu na pásu karet Domů – Formát jako tabulka (Domů — Formátovat jako tabulku). Na záložce lze opravit názvy vytvořených tabulek Stavitel (Standard nechám Tabulka 1 и Tabulka 2, které se získávají ve výchozím nastavení).
Načtěte starou cenu v Power Query pomocí tlačítka Z tabulky/rozsahu (Z tabulky/rozsahu) ze záložky Data (Datum) nebo ze záložky Dotaz na napájení (v závislosti na verzi Excelu). Po načtení se s příkazem vrátíme zpět do Excelu z Power Query Zavřít a načíst – Zavřít a načíst… (Zavřít a načíst — Zavřít a načíst do…):
… a v zobrazeném okně vyberte Stačí vytvořit spojení (Pouze připojení).
Totéž opakujte s novým ceníkem.
Nyní vytvoříme třetí dotaz, který bude kombinovat a porovnávat data z předchozích dvou. Chcete-li to provést, vyberte v Excelu na kartě Data – Získat data – Kombinovat požadavky – Kombinovat (Data — Získat data — Sloučit dotazy — Sloučit) nebo stiskněte tlačítko Kombinovat (Spojit) Karta Dotaz na napájení.
V okně spojení vyberte v rozbalovacích seznamech naše tabulky, vyberte sloupce s názvy zboží v nich a dole nastavte způsob spojení – Kompletní externí (Úplný vnější):
Po kliknutí na tlačítko OK měla by se objevit tabulka o třech sloupcích, kde ve třetím sloupci je třeba rozbalit obsah vnořených tabulek pomocí dvojité šipky v záhlaví:
Výsledkem je sloučení dat z obou tabulek:
Názvy sloupců v záhlaví je samozřejmě lepší přejmenovat dvojklikem na srozumitelnější:
A teď to nejzajímavější. Přejděte na kartu Přidat sloupec (Přidat sloupec) a klikněte na tlačítko Podmíněný sloupec (Podmíněný sloupec). A pak v okně, které se otevře, zadejte několik testovacích podmínek s odpovídajícími výstupními hodnotami:
Zbývá kliknout OK a nahrajte výslednou sestavu do Excelu pomocí stejného tlačítka zavřít a stáhnout (Zavřít a načíst) Karta Domů (Domov):
Krása.
Navíc, pokud v budoucnu dojde k nějakým změnám v cenících (přidají se nebo se smažou řádky, změní se ceny atd.), pak bude stačit pouze aktualizovat naše požadavky klávesovou zkratkou Ctrl+Další+F5 nebo tlačítkem Obnovit vše (Obnovit vše) Karta Data (Datum).
Klady: Možná nejkrásnější a nejpohodlnější způsob ze všech. Chytře pracuje s velkými stoly. Nevyžaduje ruční úpravy při změně velikosti tabulek.
Nevýhody: Vyžaduje instalaci doplňku Power Query (v Excelu 2010-2013) nebo Excelu 2016. Názvy sloupců ve zdrojových datech se nesmí měnit, jinak se zobrazí chyba „Sloupec takový a takový nebyl nalezen!“ při pokusu o aktualizaci dotazu.
- Jak shromáždit data ze všech souborů Excelu v dané složce pomocí Power Query
- Jak najít shodu mezi dvěma seznamy v Excelu
- Sloučení dvou seznamů bez duplikátů