Si të krahasoni dy lista në Excel

Excel është një program efikas për përpunimin e të dhënave. Dhe një nga metodat e analizës së informacionit është krahasimi i dy listave. Nëse krahasoni saktë dy lista në Excel, organizimi i këtij procesi do të jetë shumë i lehtë. Mjafton vetëm të ndiqni disa nga pikat që do të diskutohen sot. Zbatimi praktik i kësaj metode varet tërësisht nga nevojat e personit ose organizatës në një moment të caktuar. Prandaj, duhet të merren parasysh disa raste të mundshme.

Krahasimi i dy listave në Excel

Sigurisht, ju mund të krahasoni dy lista me dorë. Por do të duhet shumë kohë. Excel ka paketën e vet inteligjente të veglave që do t'ju lejojë të krahasoni të dhënat jo vetëm shpejt, por edhe të merrni informacione që nuk janë aq të lehta për t'u marrë me sytë tuaj. Supozoni se kemi dy kolona me koordinatat A dhe B. Disa vlera përsëriten në to.

Formulimi i problemit

Pra, ne duhet të krahasojmë këto kolona. Procedura për krahasimin e dy dokumenteve është si më poshtë:

1. Nëse qelizat unike të secilës prej këtyre listave janë të njëjta, dhe numri i përgjithshëm i qelizave unike është i njëjtë, dhe qelizat janë të njëjta, atëherë këto lista mund të konsiderohen të njëjta. Rendi në të cilin janë grumbulluar vlerat në këtë listë nuk ka aq rëndësi.
2. Mund të flasim për koincidencë të pjesshme të listave nëse vetë vlerat unike janë të njëjta, por numri i përsëritjeve është i ndryshëm. Prandaj, lista të tilla mund të përmbajnë një numër të ndryshëm elementësh.
3. Fakti që dy listat nuk përputhen tregohet nga një grup i ndryshëm vlerash unike.

Të tre këto kushte janë njëkohësisht kushtet e problemit tonë.

Zgjidhja e problemit

Le të gjenerojmë dy vargje dinamike për ta bërë më të lehtë krahasimin e listave. Secila prej tyre do të korrespondojë me secilën prej listave.

Për të krahasuar dy lista, bëni sa më poshtë:

1. Në një kolonë të veçantë, ne krijojmë një listë me vlera unike që janë specifike për të dyja listat. Për këtë përdorim formulën: ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). Vetë formula duhet të shkruhet si një formulë grupi.
2. Le të përcaktojmë se sa herë ndodh secila vlerë unike në grupin e të dhënave. Këtu janë formulat për ta bërë këtë: =COUNTIF(Lista1,D5) dhe =COUNTI(Lista2,D5).
3. Nëse si numri i përsëritjeve ashtu edhe numri i vlerave unike janë të njëjta në të gjitha listat që përfshihen në këto vargje, atëherë funksioni kthen vlerën 0. Kjo tregon që përputhja është XNUMX%. Në këtë rast, titujt e këtyre listave do të kenë një sfond të gjelbër.
4. Nëse e gjithë përmbajtja unike është në të dyja listat, atëherë kthehet sipas formulave =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) vlera do të jetë zero. Nëse E1 nuk përmban zero, por një vlerë e tillë përmbahet në qelizat E2 dhe F2, atëherë në këtë rast vargjet do të njihen si përputhëse, por vetëm pjesërisht. Në këtë rast, titujt e listave përkatëse do të kthehen në portokalli.
5. Dhe nëse një nga formulat e përshkruara më sipër kthen një vlerë jo zero, listat nuk do të përputhen plotësisht.

Kjo është përgjigjja e pyetjes se si të analizohen kolonat për ndeshjet duke përdorur formula. Siç mund ta shihni, me përdorimin e funksioneve, mund të zbatoni pothuajse çdo detyrë që, në shikim të parë, nuk lidhet me matematikën.

Shembull Testimi

Në versionin tonë të tabelës, ekzistojnë tre lloje të listave të secilit lloj të përshkruar më sipër. Ka pjesërisht dhe plotësisht përputhje, si dhe jopërputhje.

Për të krahasuar të dhënat, ne përdorim diapazonin A5:B19, në të cilin fusim në mënyrë alternative këto çifte listash. Se cili do të jetë rezultati i krahasimit, do ta kuptojmë me ngjyrën e listave origjinale. Nëse ato janë krejtësisht të ndryshme, atëherë do të jetë një sfond i kuq. Nëse një pjesë e të dhënave është e njëjtë, atëherë e verdhë. Në rastin e identitetit të plotë, titujt përkatës do të jenë të gjelbër. Si të bëni një ngjyrë në varësi të rezultatit? Kjo kërkon formatim të kushtëzuar.

Gjetja e dallimeve në dy lista në dy mënyra

Le të përshkruajmë dy metoda të tjera për gjetjen e dallimeve, në varësi të faktit nëse listat janë sinkrone apo jo.

Opsioni 1. Listat sinkrone

Ky është një opsion i lehtë. Supozoni se kemi lista të tilla.

Për të përcaktuar se sa herë vlerat nuk u konvergjuan, mund të përdorni formulën: =SUMPRODUCT(—(A2:A20<>B2:B20)). Nëse kemi marrë 0 si rezultat, kjo do të thotë se të dy listat janë të njëjta.

Opsioni 2: Listat e përziera

Nëse listat nuk janë identike në rendin e objekteve që përmbajnë, ju duhet të aplikoni një veçori të tillë si formatimi i kushtëzuar dhe ngjyrosni vlerat e kopjuara. Ose përdorni funksionin countif, duke përdorur të cilën ne përcaktojmë sa herë një element nga një listë ndodh në të dytën.

Si të krahasoni 2 kolona rresht pas rreshti

Kur krahasojmë dy kolona, ​​shpesh na duhet të krahasojmë informacionin që është në rreshta të ndryshëm. Për ta bërë këtë, operatori do të na ndihmojë IF. Le të hedhim një vështrim se si funksionon në praktikë. Për ta bërë këtë, ne paraqesim disa situata ilustruese.

Shembull. Si të krahasoni 2 kolona për ndeshjet dhe dallimet në një rresht

Për të analizuar nëse vlerat që janë në të njëjtin rresht, por kolona të ndryshme janë të njëjta, ne shkruajmë funksionin IF. Formula futet në çdo rresht të vendosur në kolonën ndihmëse ku do të shfaqen rezultatet e përpunimit të të dhënave. Por nuk është aspak e nevojshme ta përshkruani atë në çdo rresht, thjesht kopjoni atë në qelizat e mbetura të kësaj kolone ose përdorni shënuesin e plotësimit automatik.

Duhet të shkruajmë një formulë të tillë për të kuptuar nëse vlerat në të dyja kolonat janë të njëjta apo jo: =IF(A2=B2, “Përputhje”, “”). Logjika e këtij funksioni është shumë e thjeshtë: krahason vlerat në qelizat A2 dhe B2, dhe nëse janë të njëjta, shfaq vlerën "Përkon". Nëse të dhënat janë të ndryshme, ato nuk kthejnë asnjë vlerë. Ju gjithashtu mund të kontrolloni qelizat për të parë nëse ka një përputhje mes tyre. Në këtë rast, formula e përdorur është: =IF(A2<>B2, "Mos përputhen", ""). Parimi është i njëjtë, fillimisht bëhet kontrolli. Nëse rezulton se qelizat plotësojnë kriterin, atëherë shfaqet vlera "Nuk përputhet".

Është gjithashtu e mundur të përdoret formula e mëposhtme në fushën e formulës për të shfaqur të dyja "Përputhja" nëse vlerat janë të njëjta dhe "Mos përputhen" nëse janë të ndryshme: =IF(A2=B2; "Përputh", "Mos përputhet"). Ju gjithashtu mund të përdorni operatorin e pabarazisë në vend të operatorit të barazisë. Vetëm rendi i vlerave që do të shfaqen në këtë rast do të jetë paksa i ndryshëm: =IF(A2<>B2, "Mos përputhen", "Përkon"). Pas përdorimit të versionit të parë të formulës, rezultati do të jetë si më poshtë.

Ky variacion i formulës është i pandjeshëm ndaj shkronjave të vogla. Prandaj, nëse vlerat në një kolonë ndryshojnë nga të tjerët vetëm në atë që ato janë shkruar me shkronja të mëdha, atëherë programi nuk do ta vërejë këtë ndryshim. Për ta bërë krahasimin të ndjeshëm ndaj rasteve, duhet të përdorni funksionin në kritere saktë. Pjesa tjetër e argumenteve janë lënë të pandryshuara: =NËSE (EXACT(A2,B2), "Përputhje", "Unik").

Si të krahasoni kolona të shumta për ndeshjet në një rresht

Është e mundur të analizohen vlerat në lista sipas një grupi të tërë kriteresh:

1. Gjeni ato rreshta që kanë të njëjtat vlera kudo.
2. Gjeni ato rreshta ku ka përputhje në vetëm dy lista.

Le të shohim disa shembuj se si të veprohet në secilin prej këtyre rasteve.

Shembull. Si të gjeni ndeshjet në një rresht në kolona të shumta të një tabele

Supozoni se kemi një seri kolonash që përmbajnë informacionin që na nevojitet. Ne jemi përballur me detyrën për të përcaktuar ato rreshta në të cilat vlerat janë të njëjta. Për ta bërë këtë, duhet të përdorni formulën e mëposhtme: =IF(AND(A2=B2,A2=C2), “përputhje”, ” “).

Nëse ka shumë kolona në tabelë, atëherë thjesht duhet ta përdorni atë së bashku me funksionin IF operator countif: =IF(COUNTIF($A2:$C2,$A2)=3;"përputhje";" "). Numri i përdorur në këtë formulë tregon numrin e kolonave për të kontrolluar. Nëse ndryshon, atëherë duhet të shkruani aq sa është e vërtetë për situatën tuaj.

Shembull. Si të gjeni ndeshjet në një rresht në çdo 2 kolona të një tabele

Le të themi se duhet të kontrollojmë nëse vlerat në një rresht përputhen në dy kolona nga ato në tabelë. Për ta bërë këtë, duhet të përdorni funksionin si kusht OR, ku në mënyrë alternative shkruani barazinë e secilës prej kolonave me tjetrën. Këtu është një shembull.

Ne përdorim këtë formulë: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Mund të ketë një situatë kur ka shumë kolona në tabelë. Në këtë rast, formula do të jetë e madhe dhe mund të duhet shumë kohë për të zgjedhur të gjitha kombinimet e nevojshme. Për të zgjidhur këtë problem, duhet të përdorni funksionin countif: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; "varg unik"; "Star jo unik")

Shohim që në total kemi dy funksione countif. Me të parën, në mënyrë alternative përcaktojmë se sa kolona kanë ngjashmëri me A2, dhe me të dytën kontrollojmë numrin e ngjashmërive me vlerën e B2. Nëse, si rezultat i llogaritjes me këtë formulë, marrim një vlerë zero, kjo tregon që të gjitha rreshtat në këtë kolonë janë unike, nëse më shumë, ka ngjashmëri. Prandaj, nëse si rezultat i llogaritjes me dy formula dhe shtimit të rezultateve përfundimtare marrim një vlerë zero, atëherë vlera e tekstit "String unik" kthehet, nëse ky numër është më i madh, shkruhet se ky varg nuk është unik.

Si të krahasoni 2 kolona në Excel për ndeshjet

Tani le të marrim një shembull. Le të themi se kemi një tabelë me dy kolona. Duhet të kontrolloni nëse përputhen. Për ta bërë këtë, duhet të aplikoni formulën, ku do të përdoret edhe funksioni IF, dhe operatori countif: =IF(COUNTIF($B:$B,$A5)=0, "Nuk ka përputhje në kolonën B", "Ka përputhje në kolonën B")

Nuk kërkohet asnjë veprim i mëtejshëm. Pas llogaritjes së rezultatit me këtë formulë, marrim nëse vlerën e argumentit të tretë të funksionit IF ndeshjet. Nëse nuk ka asnjë, atëherë përmbajtja e argumentit të dytë.

Si të krahasoni 2 kolona në Excel për ndeshjet dhe të theksoni me ngjyra

Për ta bërë më të lehtë identifikimin vizual të kolonave që përputhen, mund t'i theksoni ato me një ngjyrë. Për ta bërë këtë, duhet të përdorni funksionin "Formatimi i kushtëzuar". Le të shohim në praktikë.

Gjetja dhe nënvizimi i ndeshjeve sipas ngjyrës në kolona të shumta

Për të përcaktuar përputhjet dhe për t'i theksuar ato, së pari duhet të zgjidhni gamën e të dhënave në të cilën do të kryhet kontrolli dhe më pas të hapni artikullin "Formatimi i kushtëzuar" në skedën "Home". Atje, zgjidhni "Dublicate Values" si rregullin e zgjedhjes së qelizave.

Pas kësaj, do të shfaqet një kuti e re dialogu, në të cilën në listën e majtë pop-up gjejmë opsionin "Përsëritje", dhe në listën e djathtë zgjedhim ngjyrën që do të përdoret për zgjedhje. Pasi të klikojmë butonin "OK", do të zgjidhet sfondi i të gjitha qelizave me ngjashmëri. Pastaj thjesht krahasoni kolonat me sy.

Gjetja dhe nënvizimi i linjave që përputhen

Teknika për të kontrolluar nëse vargjet përputhen është paksa e ndryshme. Së pari, duhet të krijojmë një kolonë shtesë, dhe atje do të përdorim vlerat e kombinuara duke përdorur operatorin &. Për ta bërë këtë, duhet të shkruani një formulë të formës: =A2&B2&C2&D2.

Ne zgjedhim kolonën që u krijua dhe përmban vlerat e kombinuara. Më pas, ne kryejmë të njëjtën sekuencë veprimesh që përshkruhet më lart për kolonat. Linjat e kopjuara do të theksohen në ngjyrën që specifikoni.

Shohim që nuk ka asgjë të vështirë në kërkimin e përsëritjeve. Excel përmban të gjitha mjetet e nevojshme për këtë. Është e rëndësishme vetëm të praktikoni përpara se të vini në praktikë gjithë këtë njohuri.