Typickým úkolem, který se pravidelně objevuje před každým uživatelem Excelu, je porovnat dva rozsahy s daty a najít mezi nimi rozdíly. Metoda řešení je v tomto případě určena typem počátečních dat.
Možnost 1. Synchronní seznamy
Pokud jsou seznamy synchronizovány (tříděny), pak se vše dělá velmi jednoduše, protože je ve skutečnosti nutné porovnávat hodnoty v sousedních buňkách každého řádku. Jako nejjednodušší možnost používáme vzorec pro porovnávání hodnot, který na výstupu produkuje booleovské hodnoty TRUE (SKUTEČNÝ) or LEŽÍCÍ (NEPRAVDIVÉ):
Počet neshod lze vypočítat podle vzorce:
=SUMPRODUCT(—(A2:A20<>B2:B20))
nebo v angličtině =SUMPRODUCT(—(A2:A20<>B2:B20))
Pokud je výsledek nula, jsou seznamy totožné. Jinak mají rozdíly. Vzorec je nutné zadat jako maticový vzorec, tj. po zadání vzorce do buňky netisknout vstoupit, A Ctrl + Shift + Enter.
Pokud potřebujete něco udělat s různými buňkami, udělá to další rychlá metoda: vyberte oba sloupce a stiskněte klávesu F5, poté v otevřeném okně tlačítko Zdůraznit (Speciální) - Rozdíly v liniích (Rozdíly v řádcích). V nejnovějších verzích Excelu 2007/2010 můžete také použít tlačítko Najděte a vyberte (Najít a vybrat) – Výběr skupiny buněk (Přejít na speciál) Karta Domů (Domov)
Excel zvýrazní buňky, které se liší obsahem (podle řádku). Následně je lze zpracovat např.
- vyplnit barvou nebo nějak vizuálně formátovat
- vyčistit klíčem Vymazat
- naplňte vše najednou stejnou hodnotou zadáním a stisknutím Ctrl + Enter
- smažte všechny řádky s vybranými buňkami pomocí příkazu Domů — Odstranit — Odstranit řádky z listu (Domů — Smazat — Smazat řádky)
- atd.
Možnost 2: Zamíchané seznamy
Pokud jsou seznamy různé velikosti a nejsou seřazeny (prvky jsou v jiném pořadí), musíte jít jinou cestou.
Nejjednodušším a nejrychlejším řešením je umožnit barevné zvýraznění rozdílů pomocí podmíněného formátování. Vyberte oba rozsahy s daty a vyberte na kartě home – Podmíněné formátování – Zvýraznit pravidla buněk – Duplicitní hodnoty:
Pokud vyberete možnost Opakující se, pak Excel zvýrazní shody v našich seznamech, pokud je možnost Unikátní – rozdíly.
Barevné zvýraznění však není vždy vhodné, zvláště u velkých stolů. Také pokud se prvky mohou opakovat uvnitř samotných seznamů, pak tato metoda nebude fungovat.
Případně můžete použít funkci COUNTIF (COUNTIF) z kategorie Statistický, který počítá, kolikrát se každý prvek z druhého seznamu vyskytuje v prvním:
Výsledná nula označuje rozdíly.
A nakonec „akrobacie“ – rozdíly si můžete zobrazit v samostatném seznamu. Chcete-li to provést, budete muset použít maticový vzorec:
Vypadá děsivě, ale svou práci plní perfektně 😉
- Zvýrazněte duplikáty v seznamu barvou
- Porovnání dvou řad s doplňkem PLEX
- Zákaz zadávání duplicitních hodnot