Ako porovnať dva zoznamy v Exceli

Excel je efektívny program na spracovanie údajov. A jednou z metód analýzy informácií je porovnanie dvoch zoznamov. Ak správne porovnáte dva zoznamy v Exceli, organizácia tohto procesu bude veľmi jednoduchá. Stačí sa riadiť niektorými bodmi, o ktorých sa dnes bude diskutovať. Praktická implementácia tejto metódy úplne závisí od potrieb osoby alebo organizácie v konkrétnom okamihu. Preto je potrebné zvážiť niekoľko možných prípadov.

Porovnanie dvoch zoznamov v Exceli

Samozrejme, môžete manuálne porovnať dva zoznamy. Ale bude to trvať dlho. Excel má vlastnú inteligentnú súpravu nástrojov, ktorá vám umožní nielen rýchlo porovnávať údaje, ale aj získať informácie, ktoré nie je také ľahké získať očami. Predpokladajme, že máme dva stĺpce so súradnicami A a B. Niektoré hodnoty sa v nich opakujú.

Formulácia problému

Musíme teda tieto stĺpce porovnať. Postup pri porovnávaní dvoch dokumentov je nasledujúci:

1. Ak sú jedinečné bunky každého z týchto zoznamov rovnaké a celkový počet jedinečných buniek je rovnaký a bunky sú rovnaké, tieto zoznamy možno považovať za rovnaké. Na poradí, v akom sú hodnoty v tomto zozname naukladané, až tak nezáleží.
2. Môžeme hovoriť o čiastočnej zhode zoznamov, ak sú samotné jedinečné hodnoty rovnaké, ale počet opakovaní je odlišný. Preto môžu takéto zoznamy obsahovať rôzny počet prvkov.
3. Skutočnosť, že sa tieto dva zoznamy nezhodujú, je indikovaná odlišnou množinou jedinečných hodnôt.

Všetky tieto tri podmienky sú súčasne podmienkami nášho problému.

Riešenie problému

Vygenerujme dva dynamické rozsahy, aby sme uľahčili porovnávanie zoznamov. Každý z nich bude zodpovedať každému zo zoznamov.

Ak chcete porovnať dva zoznamy, postupujte takto:

1. V samostatnom stĺpci vytvoríme zoznam jedinečných hodnôt, ktoré sú špecifické pre oba zoznamy. Na to použijeme vzorec: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Samotný vzorec musí byť napísaný ako vzorec poľa.
2. Poďme určiť, koľkokrát sa každá jedinečná hodnota vyskytuje v dátovom poli. Tu sú vzorce, ako to urobiť: =COUNTIF(Zoznam1,D5) a =COUNTI(Zoznam2,D5).
3. Ak je počet opakovaní aj počet jedinečných hodnôt rovnaký vo všetkých zoznamoch, ktoré sú zahrnuté v týchto rozsahoch, potom funkcia vráti hodnotu 0. To znamená, že zhoda je XNUMX %. V tomto prípade nadpisy týchto zoznamov získajú zelené pozadie.
4. Ak je všetok jedinečný obsah v oboch zoznamoch, vráti sa pomocou vzorcov =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) hodnota bude nula. Ak E1 neobsahuje nulu, ale takáto hodnota je obsiahnutá v bunkách E2 a F2, potom sa v tomto prípade rozsahy rozpoznajú ako zhodné, ale iba čiastočne. V tomto prípade sa nadpisy príslušných zoznamov sfarbia na oranžovo.
5. A ak niektorý zo vzorcov opísaných vyššie vráti nenulovú hodnotu, zoznamy sa úplne nezhodujú.

Toto je odpoveď na otázku, ako analyzovať stĺpce pre zhody pomocou vzorcov. Ako vidíte, pomocou funkcií môžete realizovať takmer akúkoľvek úlohu, ktorá na prvý pohľad nesúvisí s matematikou.

Príklad testovania

V našej verzii tabuľky existujú tri typy zoznamov každého typu opísaného vyššie. Má čiastočnú a úplnú zhodu, ako aj nezhodu.

Na porovnanie údajov používame rozsah A5:B19, do ktorého striedavo vkladáme tieto dvojice zoznamov. Čo bude výsledkom porovnania, pochopíme podľa farby pôvodných zoznamov. Ak sú úplne odlišné, potom to bude červené pozadie. Ak je časť údajov rovnaká, potom žltá. V prípade úplnej identity budú príslušné nadpisy zelené. Ako urobiť farbu v závislosti od toho, aký je výsledok? Vyžaduje si to podmienené formátovanie.

Hľadanie rozdielov v dvoch zoznamoch dvoma spôsobmi

Opíšme si ďalšie dve metódy hľadania rozdielov v závislosti od toho, či sú zoznamy synchrónne alebo nie.

Možnosť 1. Synchrónne zoznamy

Toto je jednoduchá možnosť. Predpokladajme, že máme takéto zoznamy.

Ak chcete zistiť, koľkokrát sa hodnoty nekonvergovali, môžete použiť vzorec: =SÚČET (-(A2:A20<>B2:B20)). Ak vo výsledku dostaneme 0, znamená to, že tieto dva zoznamy sú rovnaké.

Možnosť 2: Premiešané zoznamy

Ak zoznamy nie sú identické v poradí objektov, ktoré obsahujú, musíte použiť funkciu, ako je podmienené formátovanie a zafarbiť duplicitné hodnoty. Alebo použite funkciu COUNTIF, pomocou ktorého určíme, koľkokrát sa prvok z jedného zoznamu vyskytuje v druhom.

Ako porovnať 2 stĺpce riadok po riadku

Keď porovnávame dva stĺpce, často potrebujeme porovnať informácie, ktoré sú v rôznych riadkoch. K tomu nám pomôže operátor IF. Poďme sa pozrieť, ako to funguje v praxi. Na tento účel uvádzame niekoľko názorných situácií.

Príklad. Ako porovnať 2 stĺpce pre zhody a rozdiely v jednom riadku

Aby sme analyzovali, či hodnoty, ktoré sú v rovnakom riadku, ale rôzne stĺpce sú rovnaké, napíšeme funkciu IF. Vzorec sa vloží do každého riadku umiestneného v pomocnom stĺpci, kde sa zobrazia výsledky spracovania údajov. Vôbec ho však nie je potrebné predpisovať v každom riadku, stačí ho skopírovať do zvyšných buniek tohto stĺpca alebo použiť značku automatického dopĺňania.

Mali by sme si zapísať takýto vzorec, aby sme pochopili, či sú hodnoty v oboch stĺpcoch rovnaké alebo nie: =IF(A2=B2, “Zhoda”, “”). Logika tejto funkcie je veľmi jednoduchá: porovnáva hodnoty v bunkách A2 a B2 a ak sú rovnaké, zobrazí hodnotu „Coincid“. Ak sú údaje odlišné, nevracia žiadnu hodnotu. Môžete tiež skontrolovať bunky a zistiť, či medzi nimi existuje zhoda. V tomto prípade je použitý vzorec: =IF(A2<>B2, “Nezhoduje sa”, “”). Princíp je rovnaký, najprv sa vykoná kontrola. Ak sa ukáže, že bunky spĺňajú kritérium, zobrazí sa hodnota „Nezhoduje sa“.

V poli vzorca je tiež možné použiť nasledujúci vzorec na zobrazenie „Zhody“, ak sú hodnoty rovnaké, a „Nezhodovať sa“, ak sú odlišné: =IF(A2=B2; „Zhoda“, „Nezhoda“). Namiesto operátora rovnosti môžete použiť aj operátor nerovnosti. Len poradie hodnôt, ktoré sa v tomto prípade zobrazia, sa bude mierne líšiť: =IF(A2<>B2; „Nezhoduje sa“, „Zhoduje sa“). Po použití prvej verzie vzorca bude výsledok nasledovný.

Táto variácia vzorca nerozlišuje veľké a malé písmená. Preto, ak sa hodnoty v jednom stĺpci líšia od ostatných iba tým, že sú napísané veľkými písmenami, program si tento rozdiel nevšimne. Aby sa pri porovnávaní rozlišovali malé a veľké písmená, musíte použiť funkciu v kritériách EXACT. Zvyšné argumenty sú ponechané nezmenené: =AK(PRESNÁ(A2,B2), “Zhoda”, “Jedinečné”).

Ako porovnať viacero stĺpcov pre zhody v jednom riadku

Hodnoty v zoznamoch je možné analyzovať podľa celého súboru kritérií:

1. Nájdite tie riadky, ktoré majú všade rovnaké hodnoty.
2. Nájdite tie riadky, v ktorých sú zhody iba v dvoch zoznamoch.

Pozrime sa na niekoľko príkladov, ako postupovať v každom z týchto prípadov.

Príklad. Ako nájsť zhody v jednom riadku vo viacerých stĺpcoch tabuľky

Predpokladajme, že máme sériu stĺpcov, ktoré obsahujú informácie, ktoré potrebujeme. Stojíme pred úlohou určiť tie riadky, v ktorých sú hodnoty rovnaké. Ak to chcete urobiť, musíte použiť nasledujúci vzorec: =AK(A(A2=B2,A2=C2), “zhoda”, “ “).

Ak je v tabuľke príliš veľa stĺpcov, stačí ju použiť spolu s funkciou IF prevádzkovateľ COUNTIF: =AK(COUNTIF($A2:$C2,$A2)=3;”zhoda”;” “). Číslo použité v tomto vzorci udáva počet stĺpcov na kontrolu. Ak sa líši, musíte napísať toľko, koľko platí pre vašu situáciu.

Príklad. Ako nájsť zhody v jednom riadku v ľubovoľných 2 stĺpcoch tabuľky

Povedzme, že musíme skontrolovať, či sa hodnoty v jednom riadku zhodujú v dvoch stĺpcoch s hodnotami v tabuľke. Ak to chcete urobiť, musíte použiť funkciu ako podmienku OR, kde striedavo píšte rovnosť každého zo stĺpcov k druhému. Tu je príklad.

Používame tento vzorec: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Môže nastať situácia, keď je v tabuľke veľa stĺpcov. V tomto prípade bude vzorec obrovský a výber všetkých potrebných kombinácií môže trvať veľa času. Ak chcete tento problém vyriešiť, musíte použiť funkciu COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Jedinečný reťazec”; “Nie je jedinečný reťazec”)

Vidíme, že celkovo máme dve funkcie COUNTIF. Pri prvom striedavo určujeme, koľko stĺpcov má podobnosť s A2 a pri druhom kontrolujeme počet podobností s hodnotou B2. Ak v dôsledku výpočtu podľa tohto vzorca dostaneme nulovú hodnotu, znamená to, že všetky riadky v tomto stĺpci sú jedinečné, ak viac, existujú podobnosti. Ak teda v dôsledku výpočtu podľa dvoch vzorcov a sčítania konečných výsledkov dostaneme nulovú hodnotu, vráti sa textová hodnota „Unikátny reťazec“, ak je toto číslo väčšie, je napísané, že tento reťazec nie je jedinečný.

Ako porovnať 2 stĺpce v Exceli pre zhody

Teraz si uveďme príklad. Povedzme, že máme tabuľku s dvoma stĺpcami. Musíte skontrolovať, či sa zhodujú. Ak to chcete urobiť, musíte použiť vzorec, kde sa použije aj funkcia IFa prevádzkovateľa COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “Žiadne zhody v stĺpci B”, “V stĺpci B sú zhody”)

Nevyžadujú sa žiadne ďalšie kroky. Po vypočítaní výsledku týmto vzorcom dostaneme if hodnotu tretieho argumentu funkcie IF zápasy. Ak neexistujú žiadne, potom obsah druhého argumentu.

Ako porovnať 2 stĺpce v Exceli pre zhody a zvýrazniť ich farbou

Na uľahčenie vizuálnej identifikácie zodpovedajúcich stĺpcov ich môžete zvýrazniť farbou. Ak to chcete urobiť, musíte použiť funkciu „Podmienené formátovanie“. Pozrime sa v praxi.

Hľadanie a zvýraznenie zhôd podľa farby vo viacerých stĺpcoch

Ak chcete určiť zhody a zvýrazniť ich, musíte najskôr vybrať rozsah údajov, v ktorom sa bude kontrola vykonávať, a potom na karte „Domov“ otvoriť položku „Podmienené formátovanie“. Ako pravidlo výberu buniek vyberte „Duplicitné hodnoty“.

Potom sa zobrazí nové dialógové okno, v ktorom v ľavom rozbaľovacom zozname nájdeme možnosť „Opakovať“ a v pravom zozname vyberieme farbu, ktorá sa použije na výber. Po kliknutí na tlačidlo „OK“ sa vyberie pozadie všetkých buniek s podobnosťou. Potom už len porovnávajte stĺpce podľa oka.

Nájdenie a zvýraznenie zodpovedajúcich čiar

Technika kontroly zhody reťazcov je mierne odlišná. Najprv musíme vytvoriť ďalší stĺpec a tam použijeme kombinované hodnoty pomocou operátora &. Ak to chcete urobiť, musíte napísať vzorec formulára: =A2&B2&C2&D2.

Vyberieme stĺpec, ktorý bol vytvorený a obsahuje kombinované hodnoty. Ďalej vykonáme rovnakú postupnosť akcií, ktorá je popísaná vyššie pre stĺpce. Duplicitné riadky budú zvýraznené farbou, ktorú určíte.

Vidíme, že pri hľadaní opakovaní nie je nič ťažké. Excel obsahuje všetky potrebné nástroje na to. Pred uvedením všetkých týchto vedomostí do praxe je dôležité len cvičiť.