Imamo dvije tablice naloga kopirane u jedan radni list. Potrebno je usporediti podatke dviju tablica u Excelu i provjeriti koje su pozicije u prvoj tablici, a ne u drugoj. Nema smisla ručno uspoređivati vrijednost svake ćelije.
Usporedite dva stupca za podudaranja u Excelu
Kako usporediti vrijednosti u Excelu dva stupca? Da biste riješili ovaj problem, preporučujemo korištenje uvjetnog oblikovanja, koje brzo ističe pozicije koje se nalaze u samo jednom stupcu. Radni list s tablicama:
Prvi korak je imenovanje obje tablice. To olakšava razumijevanje koji se rasponi ćelija uspoređuju:
- Odaberite alat FORMULA - Definirani nazivi - Dodijeli naziv.
- U prozoru koji se pojavi, u polje "Naziv:" unesite vrijednost - Tablica_1.
- Kliknite lijevom tipkom miša na polje za unos "Raspon:" i odaberite raspon: A2:A15. I kliknite OK.
Za drugu listu slijedite iste korake, samo dodijelite naziv - Table_2. I odredite raspon C2:C15 - respektivno.
Koristan savjet! Imena raspona mogu se brže dodijeliti pomoću polja imena. Nalazi se lijevo od trake formule. Samo odaberite raspone ćelija, au polje za naziv unesite odgovarajući naziv za raspon i pritisnite Enter.
Sada upotrijebimo uvjetno oblikovanje za usporedbu dva popisa u Excelu. Moramo dobiti sljedeći rezultat:
Bit će prikazane stavke koje su u Tablici_1, ali ne i u Tablici_2 u zelenoj boji. Istovremeno će pozicije koje se nalaze u Tablici_2, ali ne i u Tablici_1 biti označene plavom bojom.
Princip usporedbe podataka dva stupca u Excelu
Prilikom definiranja uvjeta za oblikovanje ćelija stupca koristili smo funkciju COUNTIF. U ovom primjeru ova funkcija provjerava koliko se puta vrijednost drugog argumenta (na primjer, A2) pojavljuje na popisu prvog argumenta (na primjer, Tablica_2). Ako je broj puta = 0, tada formula vraća TRUE. U tom slučaju ćeliji se dodjeljuje prilagođeni format naveden u opcijama uvjetnog oblikovanja.
Veza u drugom argumentu je relativna, što znači da će sve ćelije u odabranom rasponu biti provjerene jedna po jedna (na primjer, A2:A15). Na primjer, usporediti dva cjenika u Excelu, čak i na različitim listovima. Druga formula radi na sličan način. Isti princip može se primijeniti na razne slične zadatke.
Informacije predstavljene u obliku tablica puno je prikladnije analizirati i koristiti u raznim izračunima, ali kada je potrebno usporediti podatke iz nekoliko sličnih tablica, vrlo je teško to učiniti vizualno. Prikladno softver uvijek može pomoći u sličnoj situaciji, a zatim ćemo pogledati kako usporediti dvije tablice u Excelu pomoću različite metode analiza.
Nažalost, nećete uspjeti usporediti tablice u Excelu jednim klikom, a štoviše, za usporedbu ćete možda morati na neki način pripremiti podatke, ali i napisati formulu.
Ovisno o željenom rezultatu odabire se i način usporedbe podataka iz tablica. Najlakši način je usporediti dva naizgled identična stupca kako biste identificirali retke koji još uvijek imaju tu razliku. Na ovaj način možete usporediti i numeričke vrijednosti i tekst.
Usporedimo dva stupca brojčanih vrijednosti u kojima je razlika samo u nekoliko ćelija. Zapisivanjem jednostavne formule u susjedni stupac, uvjet za jednakost dviju ćelija "=B3=C3", dobivamo rezultat "PRAVI" ako je sadržaj ćelija isti, i "LOG" ako je sadržaj stanica različit. Protezanjem formule preko cijele visine stupca uspoređivanih vrijednosti bit će vrlo lako pronaći drugu ćeliju.
Ako samo trebate provjeriti prisutnost ili odsutnost razlika u stupcima, možete koristiti stavku izbornika "Pronađi i označi", na kartici "Dom". Da biste to učinili, prvo morate odabrati uspoređene stupce, a zatim odabrati potrebnu stavku izbornika. Na padajućem popisu odaberite "Odaberi grupu ćelija...", a zatim u prozoru koji se pojavi odaberite "linijske razlike".
Razlike uvjetnog oblikovanja u uređenim vrijednostima
Ako želite, možete primijeniti uvjetno oblikovanje na različite ćelije, ispuniti ćeliju, promijeniti boju teksta i tako dalje. U ovom slučaju morate odabrati "Uvjetno oblikovanje", na padajućem popisu koje odabiremo "Upravljanje pravilima".
U upravitelju pravila odaberite "Stvori pravilo", au stvaranju pravila odaberite . Sada možemo postaviti formulu "=$B3<>$C3" da biste odredili ćeliju koju želite formatirati i postavite joj format klikom na gumb "Format".
Sada imamo pravilo odabira ćelija, formatiranje je postavljeno i definiran je raspon ćelija za usporedbu. Nakon pritiska na tipku u redu, primijenit će se pravilo koje smo postavili.
Usporedba i oblikovanje razlika u neuređenim vrijednostima
Usporedba Excel tablica nije ograničena na usporedbu poredanih vrijednosti. Ponekad morate usporediti raspone pomiješanih vrijednosti u kojima trebate utvrditi je li jedna vrijednost unutar raspona drugih vrijednosti.
Na primjer, imamo skup vrijednosti, raspoređenih u dva stupca, i drugi skup istih vrijednosti. U prvom skupu imamo sve vrijednosti od 1 do 20, au drugom neke vrijednosti nedostaju i duplicirane su drugim vrijednostima. Naš zadatak je koristiti uvjetno oblikovanje u prvom skupu vrijednosti koje nisu u drugom skupu.
Postupak je sljedeći, odabiremo prvi skup podataka koji pozivamo "1. stupac", i u izborniku "Uvjetno oblikovanje" odaberite stavku "Stvori pravilo...". U prozoru koji se pojavi odaberite , unesite traženu formulu "=COUNTIF($C$3:$D$12,A3)=0" i odaberite način oblikovanja.
Naša formula koristi funkciju "BROJENJE", koji broji broj ponavljanja vrijednosti iz određene ćelije A3 u zadanom rasponu "$C$3:$D$12", što je naša druga kolona. Ćelija koja se uspoređuje mora biti prva ćelija u rasponu vrijednosti na koje će se primijeniti oblikovanje.
Nakon primjene stvorenog pravila, sve ćelije s nedupliciranim vrijednostima u drugom skupu vrijednosti bit će označene navedenom bojom.
Naravno, postoje i složenije opcije za usporedbu dviju tablica u Excelu, poput usporedbe centi robe u novom i starom cjeniku. Pretpostavimo da postoje dvije tablice s cijenama, a pored cijena u novoj tablici trebate navesti stare cijene za svaki proizvod, a redoslijed proizvoda u listama se ne poštuje.
Pored cijena u novoj tablici, u ćeliju sljedećeg stupca potrebno je napisati formulu koja će odabrati vrijednosti. U formuli ćemo koristiti funkciju "VPR" A koji može vratiti vrijednost iz bilo kojeg stupca u retku u kojem je ispunjen uvjet pretraživanja. Da bi funkcija radila ispravno, potrebno je da stupac u svakom redu sadrži jedinstvene vrijednosti koje će se pretraživati. Ako se vrijednosti ponavljaju, u obzir će se uzeti samo prva pronađena.
Formula koja nam treba izgledat će ovako: "=VLOOKUP(B18,$B$3:$C$10,2,FALSE)". Prva vrijednost B18 odgovara prvoj ćeliji traženog naziva proizvoda. Druga vrijednost "$B$3:$C$10" znači trajnu adresu raspona stare tablice cijena, vrijednosti iz koje trebamo. Treće značenje "2" označava broj stupca iz odabranog raspona, u čiju ćeliju ćemo uzeti staru cijenu robe. I posljednje značenje "LAŽ" određuje traženje samo točnih vrijednosti podudaranja. Nakon povlačenja formule preko cijelog stupca nove tablice, dobit ćemo stare vrijednosti cijena u ovom stupcu za svaku poziciju u novoj tablici. Pored naziva posljednjeg proizvoda, formula prikazuje poruku o pogrešci "#N/A", što ukazuje na nepostojanje ovog naziva u starom cjeniku.
Postoji bezbroj opcija za usporedbu tablica u Excelu, a neke od njih moguće je napraviti samo pomoću VBA dodatka.
Ponekad je potrebno usporediti dvije MS Excel datoteke. To može biti pronalaženje odstupanja u cijenama za određene pozicije ili promjena bilo kakvih očitanja, nije važno, glavna stvar je pronaći određena odstupanja.
Ne bi bilo suvišno napomenuti da ako postoji nekoliko zapisa u MS Excel datoteci, onda nema smisla pribjegavati automatizaciji. Ako datoteka ima nekoliko stotina ili čak tisuća zapisa, tada ne možete bez pomoći računalne snage računala.
Simulirajmo situaciju u kojoj dvije datoteke imaju isti broj redaka, a odstupanje treba tražiti u određenom stupcu ili u više stupaca. Takva situacija je moguća, primjerice, ako je potrebno usporediti cijene robe prema dva cjenika ili usporediti mjere sportaša prije i nakon sezone treninga, iako bi ih za takvu automatizaciju trebalo biti puno. .
Kao radni primjer, uzmimo datoteku s učinkom fiktivnih sudionika: trčanje na 100 metara, trčanje na 3000 metara i zgibovi. Prva datoteka je mjerenje na početku sezone, a druga na kraju sezone.
Prvi način rješavanja problema. Rješenje je samo korištenje MS Excel formula.
Budući da su zapisi poredani okomito (najlogičnija konstrukcija), potrebno je koristiti . U slučaju korištenja horizontalnog postavljanja zapisa, morat ćete koristiti funkciju.
Za usporedbu performansi trčanja na 100 m, formula je sljedeća:
=IF(VLOOKUP($B2;Sheet2!$B$2:$F$13;3;TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13;3;TRUE);"Nema razlike")
Ako nema razlike, ispisuje se poruka da razlike nema, ako je ima, onda se indikator početka sezone oduzima od vrijednosti na kraju sezone.
Formula za trčanje na 3000 metara je sljedeća:
=IF(VLOOKUP($B2;Sheet2!$B$2:$F$13;4;TRUE)<>E2;"Postoji razlika";"Nema razlike")
Ako krajnja i početna vrijednost nisu jednake, prikazuje se odgovarajuća poruka. Formula za zgibove može biti slična bilo kojoj od prethodnih, nema smisla davati je dodatno. Konačna datoteka s pronađenim nedosljednostima prikazana je u nastavku.
Malo objašnjenje. Radi lakše čitljivosti formula, podaci iz dvije datoteke premješteni su u jednu (na različite listove), ali to se nije moglo učiniti.
Video koji uspoređuje dvije MS Excel datoteke pomoću funkcija i .
Drugi način rješavanja problema. Rješenje pomoću MS Accessa.
Zadatak se može riješiti tako da se prvo uvezu MS Excel datoteke u Access. Što se tiče same metode uvoza vanjskih podataka, nema razlike u pronalaženju različitih polja (bilo koja od prikazanih opcija će funkcionirati).
Ovo posljednje je veza Excel datoteke i Access, tako da kada promijenite podatke u Excel datotekama, odstupanja će se automatski pronaći kada pokrenete upit u MS Accessu.
Sljedeći korak nakon uvoza je stvaranje odnosa između tablica. Kao obvezujuće polje odaberite jedinstveno polje "Br. p / p".
Treći korak je stvaranje jednostavan zahtjev na odabir pomoću alata za sastavljanje upita.
U prvom stupcu označavamo koje zapise je potrebno prikazati, au drugom - pod kojim uvjetima će se zapisi prikazati. Naravno, za drugo i treće polje radnje će biti slične.
Video koji uspoređuje MS datoteke s Excelom pomoću MS Accessa.
Kao rezultat izvršenih manipulacija, prikazuju se svi zapisi, s različitim podacima u polju: "Trčanje na 100 metara." MS Access datoteka je predstavljena u nastavku (nažalost, SkyDrive vam ne dopušta ugradnju kao Excel datoteku)
Evo dva načina za pronalaženje odstupanja u MS Excel tablicama. Svaki ima i prednosti i nedostatke. Jasno je da ovo nije iscrpan popis usporedbi između dvije Excel datoteke. Čekamo vaše prijedloge u komentarima.
Usporedimo dvije tablice s gotovo istom strukturom. Tablice se razlikuju u vrijednostima u zasebnim recima, neki nazivi redaka nalaze se u jednoj tablici, ali ne moraju biti u drugoj.
Neka na plahtama siječnja I veljača postoje dvije tablice s prometima za razdoblje za dotične račune.
Kao što se može vidjeti na slikama, tablice se razlikuju:
- Prisutnost (odsutnost) linija (imena računa). Na primjer, u tablici na listu siječnja nema ocjene 26 (vidi primjer datoteke), au tablici na listu veljača Nedostaje račun 10 i njegovi podračuni.
- Različite vrijednosti u linijama. Na primjer, na kontu 57 ne poklapaju se prometi za siječanj i veljaču.
Ako su strukture tablice približno iste (većina naziva računa (redova) je ista, broj i nazivi stupaca su isti), tada se mogu usporediti dvije tablice. Usporedimo na dva načina: jedan je lakši za implementaciju, drugi je jasniji.
Jednostavan način za usporedbu 2 tablice
Prvo, odredimo koji su redovi (nazivi računa) prisutni u jednoj tablici, ali ih nema u drugoj. Zatim, u tablici gdje manje redaka nedostaje (u najpotpunijoj tablici), prikazat ćemo usporedni izvještaj, a to je razlika u stupcima (razlika u prometu za siječanj i veljaču).
Glavni nedostatak ovog pristupa je da izvješće o usporedbi tablice ne uključuje retke koji nedostaju u najpotpunijoj tablici. Na primjer, u našem slučaju, najpotpunija tablica je tablica na listu siječnja, u kojoj nema ocjene 26 iz veljačke tablice.
Da biste utvrdili koja je od dvije tablice najpotpunija, potrebno je odgovoriti na 2 pitanja: Koji računi u tablici za veljaču nedostaju u onoj za siječanj? i Koji računi u tablici za siječanj nedostaju u tablici za siječanj?
To se može učiniti pomoću formula (vidi stupac E): = IF(END(VLOOKUP(A7,siječanj!$A$7:$A$81,1,0)),"Ne","Da") i = IF(END(VLOOKUP(A7,Feb!$A$7:$A$77,1,0)),"Ne","Da")
Usporedba prometa na računima vršit će se pomoću formula: = IF(END(VLOOKUP($A7,feb!$A$7:$C77,2,0)),0,VLOOKUP($A7,feb!$A$7:$C77,2,0))-B7 i = IF(END(VLOOKUP($A7,feb!$A$7:$C77,3,0)),0,VLOOKUP($A7,feb!$A$7:$C77,3,0))-C7
Ako ne postoji odgovarajući red, funkcija VLOOKUP() vraća pogrešku #N/A, koja se rješava kombinacijom funkcija UND() i IF(), zamjenjujući pogrešku s 0 (ako nema retka) ili s vrijednost iz odgovarajućeg stupca.
Koristite za označavanje odstupanja (na primjer, crvenom bojom).
Vizualniji način za usporedbu 2 tablice (ali složeniji)
Analogno problemu riješenom u članku, možete napraviti popis naziva računa koji uključuje SVE nazive računa iz obje tablice (bez ponavljanja). Zatim prikažite razliku po stupcima.
Za ovo vam je potrebno:
- Uz = IFERROR(IFERROR(INDEX(siječanj,MATCH(0,COUNTIF(A$4:$A4,siječanj),0)), INDEX(veljača,MATCH(0,COUNTIF(A$4:$A4,veljača),0))) ;"") formirati u stupcu A popis računa iz obje tablice (bez ponavljanja);
- Uz = IFERROR(INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , gdje je popis -
Članak daje odgovore na sljedeća pitanja:
- Kako usporediti dvije tablice u Excelu?
- Kako usporediti složene tablice u Excelu?
- Kako usporediti tablice u Excelu pomoću funkcije VLOOKUP()?
- Kako formirati jedinstvene identifikatore reda ako je njihova jedinstvenost inicijalno određena skupom vrijednosti u nekoliko stupaca?
- Kako popraviti vrijednosti ćelija u formulama prilikom kopiranja formula?
Prilikom rada s velikom količinom informacija, korisnik se može suočiti s takvim zadatkom kao što je usporedba dva tabelarnih izvora podataka. Prilikom pohranjivanja podataka u jedinstveni računovodstveni sustav (na primjer, sustavi temeljeni na 1C Enterprise, sustavi koji koriste SQL baze podataka), mogućnosti ugrađene u sustav ili DBMS mogu se koristiti za usporedbu podataka. U pravilu je za to dovoljno uključiti programera koji će napisati upit u bazu podataka ili softverski mehanizam za izvješća. Iskusni korisnik koji ima vještinu pisanja 1C ili SQL upita također može obraditi upit.
Problemi počinju kada je potrebno hitno izvršiti zadatak usporedbe podataka, a uključivanje programera i pravovremeno pisanje zahtjeva ili programskog izvješća može prekoračiti rokove postavljene za rješavanje zadatka. Drugi jednako čest problem je potreba za usporedbom informacija iz različitih izvora. U ovom slučaju, izjava zadatka za programera zvučat će kao integracija dvaju sustava. Rješenje takvog problema zahtijevat će veću kvalifikaciju programera i također će oduzeti više vremena nego razvoj u jednom sustavu.
Za rješavanje navedenih problema idealna tehnika je korištenje uređivača proračunskih tablica Microsoft Excel za usporedbu podataka. Većina uobičajenih upravljačkih i reguliranih računovodstvenih sustava podržava izvoz u Excel format. Ovaj zadatak će zahtijevati samo određenu kvalifikaciju korisnika za rad s ovim uredskim paketom i neće zahtijevati vještine programiranja.
Razmislite o rješavanju problema usporedbe tablica u Excelu pomoću primjera. Imamo dvije tablice s popisima stanova. Izvori istovara - 1C Enterprise (računovodstvo za izgradnju) i tablica u Excelu (računovodstvo za prodaju). Tablice se u Excel radnoj knjizi nalaze na prvom, odnosno drugom listu.
Pred nama je zadatak usporedbe tih popisa po adresama. U prvoj tablici - svi stanovi u kući. U drugoj tablici - samo prodani stanovi i ime kupca. Krajnji cilj je da se u prvoj tablici za svaki stan ispiše ime kupca (za one stanove koji su prodani). Zadatak je kompliciran činjenicom da je adresa stana u svakoj tablici adresa zgrade i sastoji se od nekoliko polja: 1) adresa zgrade (kuće), 2) dio (ulaz), 3) kat, 4) broj na podu (na primjer, od 1 do 4) .
Da bismo usporedili dvije Excel tablice, moramo osigurati da u obje tablice svaki redak bude identificiran s jednim poljem, a ne s četiri. Takvo polje možete dobiti kombiniranjem vrijednosti četiri adresna polja s funkcijom Concatenate(). Svrha funkcije Concatenate() je spojiti više tekstualnih vrijednosti u jedan niz. Vrijednosti u funkciji navedene su kroz simbol ";". Kao vrijednosti mogu djelovati i adrese ćelija i proizvoljni tekst navedeni u navodnicima.
Korak 1. Umetnite prazan stupac "A" na početku prve tablice i u ćeliju tog stupca nasuprot prvog retka s podacima upišite formulu:
=SPAJANJE(B3,"-";C3;"-";D3;"-";E3)
Za praktičnost vizualne percepcije između vrijednosti spojenih ćelija postavili smo simbole "-".
Korak 2 Kopirajte formulu u sljedeće ćelije u stupcu A.
Korak 4 Za usporedbu Excel tablica po vrijednostima koristite funkciju VLOOKUP(). Svrha funkcije VLOOKUP() je traženje vrijednosti u krajnjem lijevom stupcu tablice i vraćanje vrijednosti ćelije u navedenom stupcu istog retka. Prvi parametar je željena vrijednost. Drugi parametar je tablica u kojoj će se vrijednost tražiti. Treći parametar je broj stupca iz čije će ćelije biti vraćena vrijednost u pronađenom retku. Četvrti parametar je vrsta pretraživanja: false - točno podudaranje, true - približno podudaranje.
Budući da bi izlazne informacije trebale biti smještene u prvu tablicu (u njoj su bila potrebna imena kupaca), u nju ćemo napisati formulu. Formulirajmo formulu u slobodnom stupcu desno od tablice nasuprot prvom redu podataka:
=VLOOKUP(A3;Sheet2!$A$3:$F$10;6;FALSE)
Prilikom kopiranja formula, "pametni" Excel automatski mijenja adresiranje ćelija. U našem slučaju mijenjat će se željena vrijednost za svaki red: A3, A4 itd., a adresa tablice u kojoj se traži mora ostati nepromijenjena. Da bismo to učinili, popravljamo ćelije u adresnom parametru tablice sa simbolima "$". Umjesto "Sheet2!A3:F10" radimo "Sheet2!$A$3:$F$10".
