sadržaj
Tema poređenja ove dvije liste već je pokretana više puta i sa različitih strana, ali ostaje jedna od najrelevantnijih svuda i uvijek. Pogledajmo jedan od njegovih aspekata – brojanje broja i prikazivanje odgovarajućih vrijednosti u dvije liste. Recimo da imamo dva raspona podataka koje želimo uporediti:
Radi praktičnosti, možete im dati imena kako biste ih kasnije mogli koristiti u formulama i referencama. Da biste to učinili, odaberite ćelije sa stavkama liste i na kartici formula kliknite gumb Name Manager – Kreiraj (Formule — Menadžer imena — Kreirajte). Tablice također možete pretvoriti u "pametne" koristeći prečice na tastaturi Ctrl+T ili dugmad Formatirajte kao tabelu tabulator Početna (Početna — Format kao tabela).
Brojanje šibica
Da biste izbrojali broj podudaranja na dvije liste, možete koristiti sljedeću elegantnu formulu:
U engleskoj verziji to će biti =SUMPRODUCT(COUNTIF(Lista1;List2))
Pogledajmo ga izbliza, jer se u njemu krije nekoliko neočiglednih čipova.
Prvo, funkcija COUNTIF (COUNTIF). Obično broji broj traženih vrijednosti u rasponu ćelija i koristi se u sljedećoj konfiguraciji:
=COUNTIF(Gdje tražiti; Šta tražiti)
Tipično, prvi argument je raspon, a drugi je ćelija, vrijednost ili uvjet (jedan!) za koji tražimo podudaranja u rasponu. U našoj formuli, drugi argument je također raspon. U praksi, to znači da prisiljavamo Excel da redom prođe kroz sve ćelije na drugoj listi i prebroji broj pojavljivanja svake od njih na prvoj listi. Zapravo, ovo je ekvivalentno cijeloj koloni dodatnih proračuna, presavijenih u jednu formulu:
Drugo, funkcija SUMPRODUCT (SUMPROIZVOD) ovdje obavlja dvije funkcije – zbraja izračunata COUNTIF podudaranja i u isto vrijeme pretvara našu formulu u formulu niza bez pritiskanja prečice na tipkovnici Ctrl+smjena+ući. Formula niza je neophodna da bi funkcija COUNTIF u modu sa dva argumenta raspona radila ispravno.
Listing se podudara s formulom niza
Ako trebate ne samo prebrojati broj podudaranja, već i prikazati odgovarajuće elemente na zasebnoj listi, tada će vam trebati ne najjednostavnija formula niza:
U engleskoj verziji to će biti, odnosno:
=INDEX(Список1;MATCH(1;COUNTIF(Список2;Список1)*NOT(COUNTIF($E$1:E1;Список1));0))
Logika iza ove formule je sljedeća:
- fragment COUNTIF(List2;List1), kao u prethodnom primjeru, traži podudaranja elemenata s prve liste u drugoj
- isječak NOT(COUNTIF($E$1:E1;List1)) provjerava da li je trenutno gore navedeno podudaranje već pronađeno
- i konačno lanac funkcija INDEX i MATCH dohvaća podudarni element
Ne zaboravite da pritisnete prečicu na tastaturi na kraju unosa ove formule Ctrl+smjena+ući, jer se mora unijeti kao formula niza.
#N/A greške koje se javljaju na redundantnim ćelijama mogu se dodatno presresti i zamijeniti razmacima ili praznim nizovima “” pomoću funkcije IFERROR (UKOLIKO GREŠKA).
Listing se podudara sa Power Query Query Mergeom
Na velikim tablicama, formula niza iz prethodne metode može prilično usporiti, pa će biti mnogo praktičnije koristiti Power Query. Ovo je besplatni Microsoftov dodatak koji se može učitati u Excel 2010-2013 i transformirati gotovo sve podatke. Snaga i mogućnosti Power Queryja su toliko velike da je Microsoft uključio sve njegove funkcije po defaultu u Excel od verzije iz 2016.
Prvo, moramo učitati naše tabele u Power Query. Da biste to učinili, odaberite prvu listu i na kartici podaci (u Excel 2016) ili tab power query (ako je instaliran kao poseban dodatak u programu Excel 2010-2013) kliknite na dugme Iz tabele/opseg (iz tabele):
Excel će našu tabelu pretvoriti u „pametnu“ i dati joj generički naziv Tabela 1. Nakon toga, podaci će pasti u uređivač upita Power Query. Ne moramo raditi nikakve transformacije sa stolom, tako da možete bezbedno pritisnuti dugme u gornjem levom uglu Zatvori i učitaj – Zatvori i učitaj u… (Zatvori i učitaj za…) i izaberite u prozoru koji se pojavi Samo stvorite vezu (Kreiraj samo vezu):
Zatim isto ponavljamo sa drugim opsegom.
I konačno, prelazimo na identifikaciju poklapanja. Da biste to učinili, na kartici Podaci ili na kartici Power Query nalazimo naredbu Dobijte podatke – Kombinirajte zahtjeve – Kombinirajte (Dobivanje podataka — Objedinjavanje upita — Spajanje):
U prozoru koji se otvori radimo tri stvari:
- izaberite naše tabele sa padajućih lista
- odaberite kolone za poređenje
- Izabrati Tip veze = interni (Unutarnji spoj)
Posle klika na OK na ekranu će biti prikazane samo odgovarajuće linije:
Nepotrebna kolona Tabela 2 možete kliknuti desnim tasterom miša za brisanje i preimenovati naslov prve kolone u nešto razumljivije (npr. šibice). I zatim ispraznite rezultujuću tabelu na list koristeći istu naredbu zatvorite i preuzmite (Zatvori i učitaj):
Ako se vrijednosti u izvornim tablicama promijene u budućnosti, morate zapamtiti da ažurirate rezultirajuću listu podudaranja desnom tipkom miša ili prečicom na tipkovnici Ctrl+alt+F5.
Makro za popis utakmica
Naravno, da biste riješili problem pronalaženja podudaranja, možete koristiti i makro. Da biste to uradili, kliknite na dugme Visual Basic tabulator Developer (programer). Ako nije vidljiv, možete ga prikazati kroz Datoteka – Opcije – Podešavanje trake (Datoteka — Opcije — Prilagodi traku).
U prozor uređivača Visual Basica potrebno je da dodate novi prazan modul kroz meni Umetak – Modul a zatim tamo kopirajte naš makro kod:
Sub Find_Matches_In_Two_Lists() Dim coll As New Collection Dim rng1 As Range, rng2 As Range, rngOut As Range Dim i As Long, j As Long, k As Long Set rng1 = Selection.Areas(1) Set rng2 = Selection.Areas(2) ) Postavite rngOut = Application.InputBox(Prompt:="Odaberite ćeliju iz koje želite ispisati podudaranja", Type:=8) 'Učitajte prvi raspon u kolekciju Za i = 1 Za rng1.Cells.Count coll.Add rng1.Cells (i), CStr(rng1.Cells(i)) Zatim i 'provjeravam da li su drugi elementi raspona u kolekciji k = 0 Pri grešci Nastavi dalje Za j = 1 Do rng2.Cells.Count Err.Clear elem = coll.Item(CStr (rng2.Cells(j))) Ako je CLng(Err.Number) = 0 Onda 'ako je pronađeno podudaranje, onda ispis sa pomakom prema dolje rngOut.Offset(k, 0) = rng2.Cells (j) k = k + 1 End If Next j End Sub
Korištenje dodanog makroa je vrlo jednostavno. Označite tako što ćete držati pritisnutu tipku Ctrl, oba opsega i pokrenite makro pomoću dugmeta Makroi na kartici Developer (programer) ili prečica na tastaturi alt+F8. Makro će od vas tražiti da odredite ćeliju, počevši od koje želite da prikažete listu podudaranja i nakon što kliknete na OK obaviće sav posao:
Napredniji makro ovog tipa je, inače, u mom PLEX dodatku za Microsoft Excel.
- Pronalaženje razlika u dvije Excel liste
- Spajanje dvije liste bez duplikata (3 načina)
- Šta su makroi, kako ih koristiti, gdje kopirati makro kod u Visual Basicu