Ako porovnať dva stĺpce v programe Excel (pre zhody a rozdiely)

Pozrite si video - Porovnajte dva stĺpce v programe Excel, či neobsahujú zhody a rozdiely

Jeden dotaz, ktorý dostávam často, je - „Ako porovnať dva stĺpce v programe Excel?“.

To sa dá urobiť mnohými rôznymi spôsobmi a spôsob použitia bude závisieť od dátovej štruktúry a toho, čo od nej používateľ chce.

Môžete napríklad porovnať dva stĺpce a nájsť alebo zvýrazniť všetky zodpovedajúce dátové body (ktoré sú v oboch stĺpcoch) alebo iba rozdiely (kde je dátový bod v jednom stĺpci a nie v druhom) atď.

Keďže sa ma na to toľko pýta, rozhodol som sa napísať tento rozsiahly návod s cieľom pokryť väčšinu (ak nie všetky) možné scenáre.

Ak to považujete za užitočné, odovzdajte to ďalším používateľom Excelu.

Všimnite si toho, že techniky na porovnanie stĺpcov zobrazené v tomto návode nie sú jediné.

Na základe vašej množiny údajov bude možno potrebné túto metódu zmeniť alebo upraviť. Základné princípy by však zostali rovnaké.

Ak si myslíte, že je niečo, čo je možné pridať k tomuto tutoriálu, dajte mi vedieť v sekcii komentárov

Porovnajte dva stĺpce s presnou zhodou riadkov

Toto je najľahšia forma porovnania. V takom prípade musíte vykonať porovnanie po riadkoch a určiť, ktoré riadky majú rovnaké údaje a ktoré nie.

Príklad: Porovnajte bunky v rovnakom riadku

Nasleduje súbor údajov, kde musím skontrolovať, či je meno v stĺpci A rovnaké v stĺpci B alebo nie.

Ak existuje zhoda, potrebujem výsledok ako „PRAVDIVÝ“ a ak sa nezhoduje, potom potrebujem výsledok ako „NEPRAVDIVÝ“.

Nasledujúci vzorec by to urobil:

= A2 = B2

Príklad: Porovnajte bunky v rovnakom riadku (pomocou vzorca IF)

Ak chcete získať popisnejší výsledok, môžete použiť jednoduchý vzorec IF na vrátenie výrazu „zhoda“, ak sú názvy rovnaké, a výrazu „nezhoda“, ak sa názvy líšia.

= IF (A2 = B2, „zhoda“, „nezhoda“)

Poznámka: V prípade, že chcete porovnanie rozlišovať na malé a veľké písmená, použite nasledujúci vzorec IF:

= IF (PRESNÉ (A2; B2), "zhoda", "nesúlad")

S vyššie uvedeným vzorcom by sa „IBM“ a „ibm“ považovali za dva rôzne názvy a vyššie uvedený vzorec by vrátil „nesúlad“.

Príklad: Zvýraznite riadky so zhodnými údajmi

Ak chcete zvýrazniť riadky, ktoré majú zodpovedajúce údaje (namiesto toho, aby ste získali výsledok v samostatnom stĺpci), môžete to urobiť pomocou podmieneného formátovania.

Tu sú kroky, ako to urobiť:

1. Vyberte celú množinu údajov.
2. Kliknite na kartu „Domov“.
3. V skupine Štýly kliknite na možnosť „Podmienené formátovanie“.
4. V rozbaľovacej ponuke kliknite na položku „Nové pravidlo“.
5. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“.
6. Do poľa vzorca zadajte vzorec: = $ A1 = $ B1
7. Kliknite na tlačidlo Formát a zadajte formát, ktorý chcete použiť na zodpovedajúce bunky.
8. Kliknite na tlačidlo OK.

Tým sa zvýraznia všetky bunky, v ktorých sú názvy v každom riadku rovnaké.

Porovnajte dva stĺpce a zvýraznite zhody

Ak chcete porovnať dva stĺpce a zvýrazniť zodpovedajúce údaje, môžete použiť funkciu duplikátu pri podmienenom formátovaní.

Všimnite si toho, že sa to líši od toho, čo sme videli pri porovnávaní jednotlivých riadkov. V tomto prípade nebudeme robiť porovnanie po riadkoch.

Príklad: Porovnajte dva stĺpce a zvýraznite zodpovedajúce údaje

Súbory údajov často získate tam, kde sú zhody, ale nemusia byť v rovnakom riadku.

Niečo, ako je uvedené nižšie:

Zoznam v stĺpci A je väčší ako zoznam v B. V niektorých zoznamoch sú aj niektoré mená, ale nie v rovnakom riadku (napríklad IBM, Adobe, Walmart).

Ak chcete zvýrazniť všetky zodpovedajúce názvy spoločností, môžete to urobiť pomocou podmieneného formátovania.

Tu sú kroky, ako to urobiť:

1. Vyberte celý súbor údajov.
2. Kliknite na kartu Domov.
3. V skupine Štýly kliknite na možnosť „Podmienené formátovanie“.
4. Ukážte kurzorom na možnosť Zvýrazniť pravidlá bunky.
5. Kliknite na položku Duplikovať hodnoty.
6. V dialógovom okne Duplicitné hodnoty skontrolujte, či je vybratá možnosť Duplikovať.
7. Zadajte formátovanie.
8. Kliknite na tlačidlo OK.

Vyššie uvedené kroky vám poskytnú výsledok, ako je uvedené nižšie.

Poznámka: V pravidle duplikátu podmieneného formátovania sa nerozlišujú malé a veľké písmená. „Apple“ a „jablko“ sa teda považujú za rovnaké a zvýraznia sa ako duplikáty.

Príklad: Porovnajte dva stĺpce a zvýraznite nezhodné údaje

V prípade, že chcete zvýrazniť názvy, ktoré sú uvedené v jednom zozname, a nie v druhom, môžete na to použiť aj podmienené formátovanie.

1. Vyberte celý súbor údajov.
2. Kliknite na kartu Domov.
3. V skupine Štýly kliknite na možnosť „Podmienené formátovanie“.
4. Ukážte kurzorom na možnosť Zvýrazniť pravidlá bunky.
5. Kliknite na položku Duplikovať hodnoty.
6. V dialógovom okne Duplicitné hodnoty skontrolujte, či je vybratá možnosť „Jedinečné“.
7. Zadajte formátovanie.
8. Kliknite na tlačidlo OK.

To vám poskytne výsledok, ako je uvedené nižšie. Zvýrazní všetky bunky, ktoré majú názov, ktorý sa v inom zozname nenachádza.

Porovnajte dva stĺpce a nájdite chýbajúce údajové body

Ak chcete zistiť, či je dátový bod z jedného zoznamu prítomný v druhom zozname, musíte použiť vyhľadávacie vzorce.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete identifikovať spoločnosti, ktoré sú prítomné v stĺpci A, ale nie v stĺpci B,

Na to môžem použiť nasledujúci vzorec VLOOKUP.

= ISERROR (VLOOKUP (A2, $ B $ 2: $ B $ 10,1,0))

Tento vzorec používa funkciu VLOOKUP na kontrolu, či je názov spoločnosti v A uvedený v stĺpci B alebo nie. Ak je prítomný, vráti tento názov zo stĺpca B, v opačnom prípade vráti chybu #N/A.

Tieto názvy, ktoré vracajú chybu #N/A, chýbajú v stĺpci B.

Funkcia ISERROR vráti hodnotu TRUE, ak dôjde k výsledku VLOOKUP je chyba, a FALSE, ak nejde o chybu.

Ak chcete získať zoznam všetkých názvov, kde nie je zhoda, môžete filtrovať stĺpček výsledkov a získať všetky bunky s hodnotou TRUE.

Na to isté môžete použiť aj funkciu MATCH;

= NIE (ČÍSLO (ZÁPAS (A2, $ B $ 2: $ B $ 10,0)))

Poznámka: Osobne dávam prednosť použitiu funkcie Match (alebo kombinácie INDEX/MATCH) namiesto VLOOKUP. Považujem to za flexibilnejšie a výkonnejšie. Rozdiel medzi Vlookup a Index/Match si môžete prečítať tu.

Porovnajte dva stĺpce a vytiahnite zodpovedajúce údaje

Ak máte dve množiny údajov a chcete porovnávať položky v jednom zozname s druhým a načítať zodpovedajúci údajový bod, musíte použiť vzorce vyhľadávania.

Príklad: Vytiahnite zodpovedajúce údaje (presné)

V nižšie uvedenom zozname chcem napríklad načítať hodnotu trhového ocenenia pre stĺpec 2. Na to musím vyhľadať túto hodnotu v stĺpci 1 a potom načítať zodpovedajúcu hodnotu trhového ocenenia.

Nasleduje vzorec, ktorý to urobí:

= VLOOKUP (D2, $ A $ 2: $ B $ 14,2,0)

alebo

= INDEX ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)

Príklad: Vytiahnite zodpovedajúce údaje (čiastočné)

V prípade, že získate množinu údajov, v ktorej je malý rozdiel v názvoch v dvoch stĺpcoch, vyššie uvedené vyhľadávacie vzorce nebudú fungovať.

Tieto vzorce vyhľadávania vyžadujú presnú zhodu, aby poskytli správny výsledok. Vo funkciách VLOOKUP alebo MATCH existuje možnosť približného zhody, ale tu sa nedá použiť.

Predpokladajme, že máte súbor údajov, ako je uvedené nižšie. Upozorňujeme, že v stĺpci 2 existujú názvy, ktoré nie sú úplné (napríklad JPMorgan namiesto JPMorgan Chase a Exxon namiesto ExxonMobil).

V takom prípade môžete použiť čiastočné vyhľadávanie pomocou zástupných znakov.

Nasledujúci vzorec poskytne v tomto prípade správny výsledok:

= VLOOKUP ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)

alebo

= INDEX ($ A $ 2: $ B $ 14, MATCH („*“ & D2 & „*“, $ A $ 2: $ A $ 14,0), 2)

Vo vyššie uvedenom príklade je hviezdička (*) zástupným znakom, ktorý môže predstavovať ľubovoľný počet znakov. Keď je hodnota vyhľadávania ohraničená na oboch stranách, akákoľvek hodnota v stĺpci 1, ktorá obsahuje hodnotu vyhľadávania v stĺpci 2, by sa považovala za zhodu.

Napríklad * Exxon * by zodpovedal ExxonMobil (pretože * môže predstavovať ľubovoľný počet znakov).

Tiež by sa vám mohli páčiť nasledujúce tipy a návody pre Excel:

• Ako porovnať dva listy programu Excel (pre rozdiely)
• Ako zvýrazniť prázdne bunky v programe Excel.
• Zvýraznite KAŽDÝ ďalší riadok v programe Excel.
• Rozšírený filter programu Excel: Kompletný sprievodca s príkladmi.
• Zvýraznite riadky na základe hodnoty bunky v programe Excel.