sadržaj
Excel je efikasan program za obradu podataka. A jedna od metoda analize informacija je poređenje dvije liste. Ako pravilno uporedite dvije liste u Excelu, organiziranje ovog procesa bit će vrlo jednostavno. Dovoljno je samo pratiti neke od tačaka o kojima će danas biti riječi. Praktična primjena ove metode u potpunosti ovisi o potrebama osobe ili organizacije u određenom trenutku. Stoga bi trebalo razmotriti nekoliko mogućih slučajeva.
Poređenje dve liste u Excel-u
Naravno, dvije liste možete uporediti ručno. Ali to će potrajati. Excel ima svoj vlastiti inteligentni alat koji će vam omogućiti da ne samo brzo uporedite podatke, već i da dobijete informacije koje nije tako lako doći vašim očima. Pretpostavimo da imamo dva stupca s koordinatama A i B. Neke vrijednosti se ponavljaju u njima.
Formulacija problema
Dakle, treba da uporedimo ove kolone. Procedura za poređenje dva dokumenta je sljedeća:
- Ako su jedinstvene ćelije svake od ovih lista iste, a ukupan broj jedinstvenih ćelija je isti, a ćelije su iste, onda se ove liste mogu smatrati istim. Redoslijed kojim su vrijednosti na ovoj listi naslagane nije toliko bitan.
- Možemo govoriti o djelomičnoj podudarnosti lista ako su same jedinstvene vrijednosti iste, ali je broj ponavljanja različit. Stoga takve liste mogu sadržavati različit broj elemenata.
- Činjenica da se dvije liste ne podudaraju je označena različitim skupom jedinstvenih vrijednosti.
Sva ova tri uslova istovremeno su uslovi našeg problema.
Rješenje problema
Hajde da generišemo dva dinamička opsega da bismo olakšali poređenje lista. Svaki od njih će odgovarati svakoj od lista. 
Da uporedite dve liste, uradite sledeće:
- U posebnoj koloni kreiramo listu jedinstvenih vrijednosti koje su specifične za obje liste. 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 niza.
- Odredimo koliko puta se svaka jedinstvena vrijednost pojavljuje u nizu podataka. Evo formula za to: =COUNTIF(Lista1,D5) i =COUNTI(Lista2,D5).
- Ako su i broj ponavljanja i broj jedinstvenih vrijednosti isti na svim listama koje su uključene u ove opsege, tada funkcija vraća vrijednost 0. To ukazuje da je podudaranje XNUMX%. U ovom slučaju, naslovi ovih lista će dobiti zelenu pozadinu.
- Ako se sav jedinstveni sadržaj nalazi na obje liste, onda 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 u ovom slučaju rasponi biti prepoznati kao podudarni, ali samo djelomično. U ovom slučaju, naslovi odgovarajućih lista će postati narandžasti.
- A ako jedna od gore opisanih formula vrati vrijednost različitu od nule, liste će biti potpuno nepodudarne.
Ovo je odgovor na pitanje kako analizirati stupce za podudaranja koristeći formule. Kao što vidite, uz korištenje funkcija možete implementirati gotovo svaki zadatak koji na prvi pogled nije povezan s matematikom.
Primjer testiranja
U našoj verziji tabele postoje tri vrste lista svake gore opisane vrste. Ima djelimično i potpuno podudaranje, kao i nepodudaranje.
Za poređenje podataka koristimo raspon A5:B19, u koji naizmjenično ubacujemo ove parove lista. Što će biti rezultat poređenja, razumjet ćemo po boji originalnih lista. Ako su potpuno različite, onda će to biti crvena pozadina. Ako je dio podataka isti, onda žuti. U slučaju potpunog identiteta, odgovarajući naslovi će biti zelene boje. Kako napraviti boju ovisno o tome kakav je rezultat? Ovo zahtijeva uvjetno formatiranje.
Pronalaženje razlika u dvije liste na dva načina
Hajde da opišemo još dve metode za pronalaženje razlika, u zavisnosti od toga da li su liste sinhrone ili ne.
Opcija 1. Sinhrone liste
Ovo je laka opcija. Pretpostavimo da imamo takve liste.
Da biste odredili koliko puta se 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: Promiješane liste
Ako liste nisu identične po redoslijedu objekata koje sadrže, morate primijeniti funkciju kao što je uvjetno oblikovanje i kolorizirati duple vrijednosti. Ili koristite funkciju COUNTIF, pomoću koje određujemo koliko puta se element s jedne liste pojavljuje u drugoj.
Kako uporediti 2 kolone red po red
Kada upoređujemo dvije kolone, često moramo uporediti informacije koje se nalaze u različitim redovima. Da to učinimo, operater će nam pomoći IF. Pogledajmo kako to funkcionira u praksi. Da bismo to učinili, predstavljamo nekoliko ilustrativnih situacija.
Primjer. Kako uporediti 2 kolone za podudaranja i razlike u jednom redu
Da bismo analizirali jesu li vrijednosti koje se nalaze u istom redu ali različitim stupcima iste, pišemo funkciju IF. Formula se ubacuje u svaki red koji se nalazi u pomoćnoj koloni u kojoj će biti prikazani rezultati obrade podataka. Ali uopće nije potrebno propisivati ga u svakom redu, samo ga kopirajte u preostale ćelije ove kolone ili koristite marker za autodovršavanje.
Trebali bismo zapisati takvu formulu da shvatimo 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, a ako su iste, prikazuje vrijednost "Poklapaju". Ako su podaci drugačiji, ne vraćaju nikakvu vrijednost. Također možete provjeriti ćelije da vidite postoji li podudaranje između njih. U ovom slučaju, formula koja se koristi je: =IF(A2<>B2, “Ne podudaraju”, “”). Princip je isti, prvo se vrši provjera. Ako se ispostavi da ćelije ispunjavaju kriterij, tada se prikazuje vrijednost "Ne odgovara".
Također je moguće koristiti sljedeću formulu u polju formule da prikažete i “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”, “Poklapaju”). Nakon upotrebe prve verzije formule, rezultat će biti sljedeći.
Ova varijacija formule ne razlikuje velika i mala slova. Stoga, ako se vrijednosti u jednoj koloni razlikuju od drugih samo po tome što su napisane velikim slovima, tada program neće primijetiti ovu razliku. Da bi usporedba bila osjetljiva na velika i mala slova, trebate koristiti funkciju u kriterijima TOČNO. Ostali argumenti ostaju nepromijenjeni: =IF(TAČNO(A2,B2), “Podudaranje”, “Jedinstveno”).
Kako uporediti više kolona za podudaranja u jednom redu
Moguće je analizirati vrijednosti u listama prema čitavom nizu kriterija:
- Pronađite one redove koji imaju iste vrijednosti svuda.
- Nađite one redove u kojima postoje podudaranja u samo dvije liste.
Pogledajmo nekoliko primjera kako postupiti u svakom od ovih slučajeva.
Primjer. Kako pronaći podudaranja u jednom redu u više kolona tabele
Pretpostavimo da imamo niz kolona koje sadrže informacije koje su nam potrebne. Suočeni smo sa zadatkom da odredimo one redove u kojima su vrijednosti iste. Da biste to učinili, trebate koristiti sljedeću formulu: =IF(AND(A2=B2,A2=C2), “podudaranje”, “ “).
Ako u tabeli ima previše kolona, samo je trebate koristiti zajedno s funkcijom IF operator COUNTIF: =IF(COUNTIF($A2:$C2,$A2)=3;”podudaranje”;” “). Broj koji se koristi u ovoj formuli označava broj kolona koje treba provjeriti. Ako se razlikuje, onda morate napisati onoliko koliko je istinito za vašu situaciju.
Primjer. Kako pronaći podudaranja u jednom redu u bilo koje 2 kolone tabele
Recimo da trebamo provjeriti da li se vrijednosti u jednom redu podudaraju u dvije kolone od onih u tabeli. Da biste to učinili, trebate upotrijebiti funkciju kao uvjet OR, gdje se naizmjenično upisuje jednakost svakog od stupaca drugom. Evo primjera.
Koristimo ovu formulu: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Može doći do situacije kada u tabeli ima mnogo kolona. U ovom slučaju, formula će biti ogromna i može potrajati dosta vremena za odabir svih potrebnih kombinacija. Da biste riješili ovaj problem, trebate 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. Kod prvog naizmjenično određujemo koliko kolona ima sličnost sa A2, a kod drugog provjeravamo broj sličnosti vrijednošću B2. Ako, kao rezultat izračunavanja po ovoj formuli, dobijemo nultu vrijednost, to znači da su svi redovi u ovoj koloni jedinstveni, ako ih ima više, postoje sličnosti. Dakle, ako kao rezultat izračunavanja po dvije formule i zbrajanja konačnih rezultata dobijemo nultu vrijednost, onda se vraća tekstualna vrijednost „Jedinstveni niz“, ako je ovaj broj veći, piše da ovaj niz nije jedinstven.
Kako uporediti 2 kolone u Excelu za podudaranja
Sada uzmimo primjer. Recimo da imamo tabelu sa dve kolone. Morate provjeriti da li se poklapaju. Da biste to učinili, morate primijeniti formulu, gdje će se koristiti i funkcija IF, i operatera COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, “Nema podudaranja u koloni B”, “Postoje podudaranja u koloni B”)
Nisu potrebne dodatne radnje. Nakon izračunavanja rezultata po ovoj formuli, dobijamo if vrijednost trećeg argumenta funkcije IF utakmice. Ako ih nema, onda sadržaj drugog argumenta.
Kako uporediti 2 kolone u Excelu za podudaranja i istaknuti bojom
Da biste lakše vizualno prepoznali odgovarajuće stupce, možete ih istaknuti bojom. Da biste to učinili, trebate koristiti funkciju "Uvjetno formatiranje". Da vidimo u praksi.
Pronalaženje i isticanje podudaranja po boji u više kolona
Da biste odredili podudaranja i istakli 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 ćelije.
Nakon toga će se pojaviti novi dijalog box u kojem u lijevoj iskačućoj listi nalazimo opciju „Ponavljanje“, au desnoj listi biramo boju koja će se koristiti za odabir. Nakon što kliknemo na dugme "OK", biće odabrana pozadina svih ćelija sa sličnostima. Zatim samo uporedite kolone na oko.
Pronalaženje i isticanje odgovarajućih linija
Tehnika za provjeru podudaranja nizova je malo drugačija. Prvo, trebamo kreirati dodatni stupac i tamo ćemo koristiti kombinirane vrijednosti pomoću & operatora. Da biste to učinili, morate napisati formulu u obliku: =A2&B2&C2&D2.
Odabiremo kolonu koja je kreirana i koja sadrži kombinirane vrijednosti. Zatim izvodimo isti slijed radnji koji je gore opisan za stupce. Duplicirane linije će biti 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 svo ovo znanje primijenite u praksu.