Porovnání dvou seznamů a nalezení odlišností

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

Tento článek je o tom, jak porovnat dva seznamy a najít, ve kterých konkrétních položkách se odlišují – tedy které položky jsou v jednom seznamu, ale ne ve druhém.

Příklad

Mám dva sloupečky s hodnotami. Potřebuji zjistit, které hodnoty jsou v jedné tabulce, ale nejsou ve druhé.

Např. v první tabulce mám seznam zákazníků, ve druhé pak seznam zákazníků, kterým jsem poslal vánoční přání. A potřebuji zjistit, kterým zákazníkům jsem přáníčko zatím neposlal.

Zjednodudšeně – ve sloupci A potřebuji identifikovat Beátu a Danu, protože ty nejsou ve sloupci D.

původní tabulka

Toto porovnání je možné udělat různými způsoby. Na této stránce si ukážeme dva z nich – jeden využívá funkci SVYHLEDAT / VLOOKUP a druhý funkci COUNTIFS.

Návod s funkcí SVYHLEDAT / VLOOKUP 

Asi nejjednodušší varianta je, že ve sloupci B zapíšeme vzorec, kterým se pokusíme najít hodnoty ze sloupce A ve sloupci D.

  • =SVYHLEDAT(A:A;D:D;1;0)

první svyhledat

Vzorec pak roztáhneme. U položek, které jsou v A, ale nejsou v D, se objeví chyba – nenalezeno. To jsou tedy ty, kde se seznamy liší.
Toto řešení je funkčí, ale dost neelegantní. Hezčí výsledek dostaneme, pokud funkci SVYHLEDAT obalíme funkcemi KDYŽ (IF) a JE.CHYBHODN (ISERROR).

  • =KDYŽ(JE.CHYBHODN(SVYHLEDAT(A:A;D:D;1;0));”tomu jsme přání zatím neodeslali”;”ok”)

druha svyhledat

Návod s funkcí COUNTIFS

Na tento úkol můžeme jít logicky i tak, že se pokusíme spočítat, kolikrát se která hodnota ze sloupce A vyskytuje ve sloupci D, a posoudíme, jestli je tam jednou nebo nulakrát. 

Vzorec pak bude vypadat buď takto (nuly znamenají, že v tomto se položky liší):

  • =COUNTIFS(D:D;A:A)

první countifs

nebo, elegantněji, takto:

  • =KDYŽ(COUNTIFS(D:D;A:A);”ok”;”tomuhle jsme přání zatím neodeslali”)

druha countifs

O kombinaci KDYŽ a COUNTIFS více tady.

Návod s Power Query:

Tento úkol lze elegantně řešit také pomocí Power Query Řešení je popsané tady.

Videonávod:

Funkci SVYHLEDAT se můžete naučit i z našeho elearningu:

Elearning středně pokročilý Excel:

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

1 Komentář

  1. Dá se to řešit mnoha způsoby, klidně i přes COUNTIF. Já jsem to v článku chtěl ukázat tak, abychom si mohli navolit dvě možnosti výsledků.
    Troufnul bych si odhadnout, že COUNTIF může být trochu pomalejší – protože Excel musí najít všechny výskyty a nestačí jen první. Ale to jenom spekuluju 🙂

  2. Dobrý den, jak mám postupovat, pokud je v obou tabulkách shodná pouze část textu. Mám dva ceníky o dvou sloupcích. První sloupec je kód zboží, druhý sloupec je cena toho zboží. Kódy zboží mají v obou cenících vždy shodnou jen část (např. “Zboží 0001” v jednom ceníku a “Položka 0001” v druhém ceníku). Potřebuji spočítat průměrnou cenu zboží v jednom i druhém ceníku, ale najít do průměrů použít jen ceny zboží, které je v obou cenících (tedy má shodnou číselnou část kódu). Děkuji za pomoc.

  3. Dobrý den Dane, pokud by se vám podařilo alespoň v jednom sloupci text “ořezat”, např. ze “Zboží 001” na “OO1”, dá se použít tento trik:
    https://exceltown.com/navody/funkce/svyhledat-vlookup-funkce-pro-propojovani-vice-tabulek/prirazovani-hodnot-na-zaklade-casti-textu/
    Pokud se to nepodaří, je možné zkusit např. doplněk Fuzzy Lookup:
    http://www.vyuka-excelu.cz/navody/postupy-a-spinave-triky/priblizne-prirazovani-textu-fuzzy-lookup/

  4. Děkuji za radu. Mezičasem jsem to vyřešil přejmenováním/nahrazením textu v názvu zboží a pak použitím svyhledat. Máte ještě tip, jak potom pří počítání průměrů cen pouze z těch položek zboží, které se vyskytují v obou cenících, nezahrnout omylem do výpočtu i ceny, které v obou cenících současně nejsou?

  5. Tohle už bych asi potřeboval vidět na konkrétních tabulkách…

  6. Děkuji, nakonec to nebylo třeba složitě řešit. Svyhledat v podstatě tu práci udělá, protože vrátí právě ty hodnoty, které jsou zastoupené v obou cenících. 🙂

  7. Dobrý den, fce COUNTIF je mnohem sympatičtější, ale nefunguje. Po zadaání vzorce u Adama, dobrý, zobrazí se mi 1, ale jakmile použiju ten samý vzorec pro další jméno, háže mi to vždy 0 i když vidím, že je to blbost. Moc prosím o radu!

    Děkuji!

  8. Barbora – jak ten vzorec zadáváte?
    Myslím že tady by bylo správně zadat do B2 (a dále) “=COUNTIFS(A:A;A:A)”

  9. SVYHLEDAT je v daném případě funkce, která stojí před vraty k tématu databází . A ačkoliv Excel/sešit/list/tabulka není zdaleka ideální coby úložiště dat, je to jedna z možností (bohužel do výčtu nepatří Tabulka, tj. List, ListObject, dříve Seznam). Pokud se tedy podíváme na dvě tabulky jako na data databáze, pak je mnohem účinnější způsob jakéhokoliv porovnávání SQL jazyk. Zprostředkovatelem na listu je MS Query. Tahle vykopávka má masařky, ale přesto v ní lze aplikovat SQL příkazy typu JOIN. Ostatně SQL přístup je pravděpodobně JEDINÝ, který díky UNION ALL zvládne prosté sloučení dat ze dvou a více zdrojů bez VBA. Tuhle triviální věc neumí ani souhrny, ani kontingenční tabulky. Jestli to tedy myslíte vážně, odpoutejte se od vyhledávacích funkcí pro porovnávání nějakých ceníku atp.

  10. Jsem jen základní uživatel Excellu. Mám dvě velké jednosloupcové tabulky (seznamy) Tabulka1 a Tabulka2, které se částečně překrývají.
    Potřebuji z Tabulky2 vyloučit položky, které se vyskytují v Tabulce1 1.Podle mne primitivní úkol, se kterým si ale nevím rady.
    Děkuji předem za radu.

  12. Dobrý den,
    potřebujeme použít tuto funkci mezi dvěma soubory excelu. Je to možné. Popřípadě jak.
    Děkuji
    Jiří Vašíček

  13. Jednoduše. Otevřete oba soubory, a ve chvíli, kdy chcete zadat odkaz na tabulku ve druhém listu, se prokliknete a vložíte odkaz… Skoro stejně jako v rámci jednoho souboru.

  14. Já řeším, že mám v sloupci A vypsané týmy a v sloupci B jména hráčů. Takže např. A1 = Praha B1= Jaroslav, A2 = Praha, B2 = Mirek. Jde mi o to, jak udělat na druhém listu to, že když zadám tým Praha, tak mi to vyjede všechny hráče Prahy (hráči svých týmů nebudou hned pod sebou, ale náhodně). Netuším jak. Svyhledat vrátí vždy jen toho prvního.

  17. =KDYŽ((JE.CHYBHODN(SVYHLEDAT(A2;$D$2:$D$6;1;0)));”V druhé tabulce není”;””)

  18. ts – já tam chybu nevidím. Váš zápis se liší jen v tom, že přibyla jedna závorka kolem prvního argumentu VLOOKUPU, ale ta přece není nutná… Nebo se dívám blbě?

  19. Dobrý den, nemohu domyslet, jak provést:
    tři tabulky-dva sloupce-v jednom hledam shodu čísla mezi dalšímy dvěma tabulkami a pokud je, potřebuji přečíst počet z druhého sloupce a zapsat ho do čtvrté tabulky společně s tou hodnotou z prvního sloupce. Prostě něco jako čísla produktů vs jejich počet. A různé tabulky jsou z různých skladů. Pokud číslo existuje jen v prní tabulce, tak ve čtvrté prostě zapsat číslo a k němu 0. Nevím jak na to? Postup. Každá rada dobrá. 🙁

  20. dobrý den, porovnávám dvě tabulky, jako ve vašem příkladu, pomocí funkce VLOOKUP.
    Existuje doplňující funkce, či jiný způsob zápisu IF, kdy v případě, že dojde k nalezení shody dat v obou tabulkách mi vepíše nikoliv OK, ale další hodnotu vztahující se k jedné z tabulek. Přesněji, porovnávám-li tab ve sloupci A s tabulkou ve sloupci D, přičemž tabulka pravá má kromě křestních jmen ve sloupci D ještě i příjmení uvedená např. ve sloupci E, tak mi funkce do sloupce B napíše v případě shody právě dané příjmení ze sloupce E. děkuji za radu

  21. Dobrý den,

    mám prosím dva soubory, jeden z roku 2010, druhý z roku 2014. Potřebuji je porovnat, vytvořit nový soubor, jehož základem bude soubor z roku 2010 a vše, co bude v souboru 2014 jinak, tak se v souboru 2010 přemaže novou informací, která zároveň bude barevně označena, aby se poznalo, že je nová. Je toto možné nějak v excelu udělat?

    Děkuji, Simona

  22. Dobrý den,
    řeším potíž, jak co nezdárněji aktualizovat tabulku se seznam smluv a klientů.
    V průběhu času totiž klienti stále přibývají a já si mohu z našeho systému vždy nechat vytvořit aktuální tabulku.
    Jenže mám už uloženou stávající tabulku, do které si k jednotlivým smlouvám dopisuji své poznámky, které nechci ztratit, ale zároveň bych tuto tabulku potřebovala průběžně aktualizovat o nové smlouvy, tedy nové řádky…
    Jaká funkce lze na toto použít, aby mi do stávající tabulky doplnila nové řádky s novými smlouvami, které jsou jen ve druhé (aktuální) tabulce?
    Mockrát děkuji za pomoc, jsem už zoufalá.
    Nikol

  23. Dobrý den, prosím, pokud potřebuji prohledat, více listů, popřípadě souborů a najít stejné hodnoty. Lze to nějak udělat? Duplicitním obarvením v podmíněném formátování to asi nejde, že?
    Zkoušela jsem funkci COUNTIFS, tak aby mi Excel prohledal, kolikrát se výraz z listu 3 nachází v listu 1, nebo listu 2. Zadala jsem to následně:
    =COUNTIFS(List2!$A$1:$A$4;List3!A1;List1!$A$1:$A$4;List3!A1)
    Bohužel vidím, že Excel prohledal pouze list 2, ale list 1 již ignoroval. Navíc jak ošetřit, když se něco opakuje v listu 1 a 2, ale listu 3 již ne.
    Předem velice děkuji za odpověď.
    Martina
    pejrova@seznam.cz

  24. Dobrý den, pokud hledáte ve více listech, musíte to sčítat, např. takto:
    =COUNTIFS z prvního listu + COUNTIFS ze druhého + COUNTIFS ze třetího….
    nejde to spojit do jedné funkce

  25. Moc děkuji za odpověď, bohužel asi jsem to špatně pochopila. Když budu mít např. tři listy a budu potřebovat prohledat, co se v nich opakuje, tak jaké stanovím kritérium? Potřebuji porovnat listy mezi sebou navzájem. A jde to i mezi sešity?
    Děkuji

  26. V tom případě je myslím nejjednodušší spojit je dohromady (nejlépe dynamicky přes Power Query) a pak s tím pracovat jako s jednou tabulkou. Pak už můžete dupicity hledat třeba i podmíněným formátováním nebo jakkoliv jinak…

Komentáře není možné přidávat