Kako usporediti dvije liste u Excelu

Excel je učinkovit program za obradu podataka. A jedna od metoda analize informacija je usporedba dva popisa. Ako ispravno usporedite dva popisa u Excelu, organiziranje ovog procesa bit će vrlo jednostavno. Dovoljno je samo slijediti neke od točaka o kojima će danas biti riječi. Praktična implementacija ove metode u potpunosti ovisi o potrebama osobe ili organizacije u određenom trenutku. Stoga treba razmotriti nekoliko mogućih slučajeva.

Usporedba dva popisa u Excelu

Naravno, možete ručno usporediti dva popisa. Ali to će trajati dugo. Excel ima svoj vlastiti inteligentni alat koji će vam omogućiti ne samo brzu usporedbu podataka, već i dobivanje informacija koje nije tako lako dobiti očima. Pretpostavimo da imamo dva stupca s koordinatama A i B. Neke vrijednosti se ponavljaju u njima.

Formulacija problema

Stoga moramo usporediti ove stupce. Postupak usporedbe dva dokumenta je sljedeći:

1. Ako su jedinstvene ćelije svake od ovih lista iste, i ukupan broj jedinstvenih ćelija je isti, a ćelije su iste, tada se te liste mogu smatrati istima. Redoslijed kojim su vrijednosti na ovom popisu naslagane nije toliko bitan.
2. Možemo govoriti o djelomičnoj podudarnosti popisa ako su same jedinstvene vrijednosti iste, ali je broj ponavljanja različit. Stoga takve liste mogu sadržavati različit broj elemenata.
3. Činjenica da se dva popisa ne podudaraju označena je različitim skupom jedinstvenih vrijednosti.

Sva ova tri uvjeta istovremeno su uvjeti našeg problema.

Rješenje problema

Generirajmo dva dinamička raspona kako bismo olakšali usporedbu popisa. Svaki od njih će odgovarati svakom od popisa.

Da biste usporedili dva popisa, učinite sljedeće:

1. U zasebnom stupcu stvaramo popis jedinstvenih vrijednosti koje su specifične za oba popisa. Za to koristimo formulu: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Sama formula mora biti napisana kao formula polja.
2. Odredimo koliko se puta svaka jedinstvena vrijednost pojavljuje u nizu podataka. Evo formula za to: =COUNTI(List1,D5) i =COUNTI(List2,D5).
3. Ako su i broj ponavljanja i broj jedinstvenih vrijednosti isti na svim popisima koji su uključeni u te raspone, tada funkcija vraća vrijednost 0. To znači da je podudaranje XNUMX%. U tom će slučaju naslovi tih popisa dobiti zelenu pozadinu.
4. Ako je sav jedinstveni sadržaj na oba popisa, tada se vraća formulama =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) vrijednost će biti nula. Ako E1 ne sadrži nulu, ali je takva vrijednost sadržana u ćelijama E2 i F2, tada će rasponi biti prepoznati kao podudarni, ali samo djelomično. U tom će slučaju naslovi odgovarajućih popisa postati narančasti.
5. A ako jedna od gore opisanih formula vrati vrijednost različitu od nule, popisi se neće podudarati.

Ovo je odgovor na pitanje kako analizirati stupce za podudaranja pomoću formula. Kao što vidite, korištenjem funkcija možete implementirati gotovo svaki zadatak koji na prvi pogled nije povezan s matematikom.

Primjer testiranja

U našoj verziji tablice postoje tri vrste popisa od svake gore opisane vrste. Ima djelomično i potpuno podudaranje, kao i nepodudaranje.

Za usporedbu podataka koristimo raspon A5:B19 u koji naizmjenično ubacujemo te parove lista. Što će biti rezultat usporedbe, shvatit ćemo bojom izvornih popisa. Ako su potpuno različite, onda će to biti crvena pozadina. Ako je dio podataka isti, onda žuto. U slučaju potpunog identiteta, odgovarajući naslovi bit će zeleni. Kako napraviti boju ovisno o tome kakav je rezultat? Ovo zahtijeva uvjetno oblikovanje.

Pronalaženje razlika u dva popisa na dva načina

Opišimo još dvije metode za pronalaženje razlika, ovisno o tome jesu li liste sinkrone ili ne.

Opcija 1. Sinkroni popisi

Ovo je jednostavna opcija. Pretpostavimo da imamo takve liste.

Da biste odredili koliko puta vrijednosti nisu konvergirale, možete koristiti formulu: =SUMPROIZVOD(—(A2:A20<>B2:B20)). Ako smo kao rezultat dobili 0, to znači da su dvije liste iste.

Opcija 2: Pomiješani popisi

Ako popisi nisu identični u redoslijedu objekata koje sadrže, trebate primijeniti značajku kao što je uvjetno oblikovanje i obojati duplicirane vrijednosti. Ili upotrijebite funkciju COUNTIFpomoću kojeg određujemo koliko se puta element s jedne liste pojavljuje u drugoj.

Kako usporediti 2 stupca red po red

Kada uspoređujemo dva stupca, često moramo usporediti informacije koje se nalaze u različitim recima. Da bismo to učinili, operater će nam pomoći AKO. Pogledajmo kako to funkcionira u praksi. Da bismo to učinili, predstavljamo nekoliko ilustrativnih situacija.

Primjer. Kako usporediti 2 stupca za podudaranja i razlike u jednom retku

Da bismo analizirali jesu li vrijednosti koje se nalaze u istom retku, ali u različitim stupcima iste, pišemo funkciju IF. Formula se upisuje u svaki redak koji se nalazi u pomoćnom stupcu gdje će biti prikazani rezultati obrade podataka. Ali uopće ga nije potrebno propisati u svakom retku, samo ga kopirajte u preostale ćelije ovog stupca ili upotrijebite oznaku za automatsko dovršavanje.

Trebali bismo zapisati takvu formulu da bismo razumjeli jesu li vrijednosti u oba stupca iste ili ne: =IF(A2=B2, “Podudaranje”, “”). Logika ove funkcije je vrlo jednostavna: uspoređuje vrijednosti u ćelijama A2 i B2, i ako su iste, prikazuje vrijednost "Coincide". Ako su podaci drugačiji, ne vraća nikakvu vrijednost. Također možete provjeriti ćelije da biste vidjeli postoji li podudaranje između njih. U ovom slučaju, formula koja se koristi je: =IF(A2<>B2, “Ne podudaraju se”, “”). Princip je isti, prvo se provodi provjera. Ako se ispostavi da ćelije zadovoljavaju kriterij, tada se prikazuje vrijednost "Ne odgovara".

Također je moguće koristiti sljedeću formulu u polju formule za prikaz "Podudaranje" ako su vrijednosti iste i "Ne podudaranje" ako su različite: =IF(A2=B2; “Podudaranje”, “Ne podudaranje”). Također možete koristiti operator nejednakosti umjesto operatora jednakosti. Samo će redoslijed vrijednosti koje će biti prikazane u ovom slučaju biti malo drugačiji: =IF(A2<>B2, “Ne podudaraju se”, “Podudaraju se”). Nakon korištenja prve verzije formule, rezultat će biti sljedeći.

Ova varijacija formule ne razlikuje velika i mala slova. Stoga, ako se vrijednosti u jednom stupcu razlikuju od drugih samo po tome što su napisane velikim slovima, tada program neće primijetiti tu razliku. Da bi usporedba bila osjetljiva na velika i mala slova, trebate upotrijebiti funkciju u kriterijima TOČNO. Ostali argumenti ostaju nepromijenjeni: =IF(TOČNO(A2,B2), “Podudaranje”, “Jedinstveno”).

Kako usporediti više stupaca za podudaranja u jednom retku

Moguće je analizirati vrijednosti na popisima prema čitavom nizu kriterija:

1. Pronađite one retke koji posvuda imaju iste vrijednosti.
2. Pronađite one retke u kojima postoje podudaranja u samo dva popisa.

Pogledajmo nekoliko primjera kako postupiti u svakom od ovih slučajeva.

Primjer. Kako pronaći podudaranja u jednom retku u više stupaca tablice

Pretpostavimo da imamo niz stupaca koji sadrže informacije koje su nam potrebne. Suočeni smo sa zadatkom određivanja onih redaka u kojima su vrijednosti iste. Da biste to učinili, morate koristiti sljedeću formulu: =IF(AND(A2=B2,A2=C2), “podudaranje”, ” “).

Ako u tablici ima previše stupaca, samo ga trebate koristiti zajedno s funkcijom IF operater COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”podudaranje”;” “). Broj koji se koristi u ovoj formuli označava broj stupaca za provjeru. Ako se razlikuje, onda morate napisati onoliko koliko je istinito za vašu situaciju.

Primjer. Kako pronaći podudaranja u jednom retku u bilo koja 2 stupca tablice

Recimo da trebamo provjeriti podudaraju li se vrijednosti u jednom retku u dva stupca s onima u tablici. Da biste to učinili, trebate koristiti funkciju kao uvjet OR, gdje naizmjenično upišite jednakost svakog od stupaca s drugim. Evo primjera.

Koristimo ovu formulu: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Može doći do situacije kada u tablici postoji mnogo stupaca. U ovom slučaju, formula će biti ogromna i može potrajati puno vremena za odabir svih potrebnih kombinacija. Da biste riješili ovaj problem, morate koristiti funkciju COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Jedinstveni niz”; “Nije jedinstven niz”)

Vidimo da ukupno imamo dvije funkcije COUNTIF. S prvim naizmjenično utvrđujemo koliko stupaca ima sličnosti s A2, a s drugim provjeravamo broj sličnosti s vrijednošću B2. Ako kao rezultat izračuna prema ovoj formuli dobijemo nultu vrijednost, to znači da su svi redovi u ovom stupcu jedinstveni, ako ih je više, postoje sličnosti. Stoga, ako kao rezultat izračuna po dvije formule i zbrajanja konačnih rezultata dobijemo nultu vrijednost, tada se vraća tekstualna vrijednost "Jedinstveni niz", ako je taj broj veći, piše se da ovaj niz nije jedinstven.

Kako usporediti 2 stupca u Excelu za podudaranja

Sada uzmimo primjer. Recimo da imamo tablicu s dva stupca. Morate provjeriti odgovaraju li. Da biste to učinili, morate primijeniti formulu, gdje će se funkcija također koristiti IF, i operater COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “Nema rezultata u stupcu B”, “Ima rezultata u stupcu B”)

Nije potrebna nikakva daljnja radnja. Nakon što izračunamo rezultat ovom formulom, dobivamo if vrijednost trećeg argumenta funkcije IF šibice. Ako ih nema, tada je sadržaj drugog argumenta.

Kako usporediti 2 stupca u Excelu za podudaranja i označiti bojom

Kako biste lakše vizualno prepoznali podudarne stupce, možete ih označiti bojom. Da biste to učinili, morate koristiti funkciju "Uvjetno oblikovanje". Da vidimo u praksi.

Pronalaženje i označavanje podudaranja po boji u više stupaca

Da biste odredili podudaranja i označili ih, prvo morate odabrati raspon podataka u kojem će se izvršiti provjera, a zatim otvoriti stavku "Uvjetno oblikovanje" na kartici "Početna". Tamo odaberite "Duplicate Values" kao pravilo odabira ćelija.

Nakon toga pojavit će se novi dijaloški okvir u kojem u lijevom pop-up popisu nalazimo opciju “Ponavljanje”, a u desnom popisu odabiremo boju koja će se koristiti za odabir. Nakon što kliknemo gumb “OK”, bit će odabrana pozadina svih ćelija sa sličnostima. Zatim samo usporedite stupce na oko.

Pronalaženje i označavanje odgovarajućih redaka

Tehnika za provjeru podudaranja nizova malo je drugačija. Prvo, moramo stvoriti dodatni stupac, a tamo ćemo koristiti kombinirane vrijednosti koristeći & operator. Da biste to učinili, morate napisati formulu oblika: =A2&B2&C2&D2.

Odaberemo stupac koji je stvoren i sadrži kombinirane vrijednosti. Zatim izvodimo isti slijed radnji koji je gore opisan za stupce. Dvostruke linije bit će istaknute bojom koju odredite.

Vidimo da nema ništa teško u traženju ponavljanja. Excel sadrži sve potrebne alate za to. Važno je samo vježbati prije nego što sva ta znanja stavite u praksu.