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

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

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

  5. 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. 🙂

  6. 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!

  7. 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)“

  8. 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.

  9. 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.

  10. 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

  11. 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.

  12. 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.

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

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

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

  16. 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 publikována.

smazat formulářOdeslat komentář