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

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.

20 comments on “Porovnání dvou seznamů a nalezení odlišností
  1. Jiří Beran napsal:

    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. Dan P napsal:

    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. Jiří Beran napsal:

    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:
    http://www.excelentnitriky.com/2013/11/dohledani-hodnot-na-zaklade-casti-textu.html
    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. Dan P napsal:

    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. Jiří Beran napsal:

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

  6. Dan P napsal:

    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. Barbora napsal:

    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. Jiří Beran napsal:

    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. xlnc napsal:

    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. Ondřej napsal:

    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.

  11. Jiří Vašíček napsal:

    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

  12. Jiří Beran napsal:

    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.

  13. Jaroslav Ševčík napsal:

    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.

  14. Jiří Beran napsal:

    Dobrý den,
    na tohle se používá např. rozšířený filtr:
    http://vyuka-excelu.cz/navody/filtry-razeni-souhrny-atd/filtr-upresnit-rozsireny-filtr/

  15. ts napsal:

    Mas tam chybu

  16. ts napsal:

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

  17. Jiří Beran napsal:

    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ě?

  18. Zdeněk napsal:

    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á. 🙁

  19. Petr napsal:

    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

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář
TOPlist