Ponekad je potrebno usporediti dvije MS Excel datoteke. To može biti pronalaženje odstupanja u cijenama za određene artikle ili promjena bilo kakvih indikacija, nije važno, glavna stvar je da je potrebno pronaći određena odstupanja.
Ne bi bilo na odmet spomenuti da ako postoji par zapisa u MS Excel datoteci, onda nema smisla posezati za automatizacijom. Ako datoteka sadrži nekoliko stotina, pa čak i tisuća zapisa, tada je nemoguće učiniti 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 nekoliko stupaca. Ova situacija je moguća, primjerice, ako trebate usporediti cijene robe prema dva cjenika ili usporediti mjere sportaša prije i nakon sezone treninga, iako ih za takvu automatizaciju mora 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 raspoređeni okomito (najlogičniji raspored), potrebno je koristiti funkciju. Ako koristite horizontalno postavljanje zapisa, morat ćete koristiti funkciju.
Za usporedbu performansi trčanja na 100 metara, 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 nema razlike, ako postoji razlika se oduzima vrijednost na kraju sezone od vrijednosti na početku 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 konačna 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 pojašnjenje. Radi lakšeg čitanja 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 korištenje i funkcije dviju MS Excel datoteka.
Drugi način rješavanja problema. Rješenje pomoću MS Accessa.
Ovaj se problem može riješiti ako prvo uvezete 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 poslužiti).
Potonji je veza između Excel i Access datoteka, 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 spojno polje odaberite jedinstveno polje “Br.
Treći korak je stvaranje jednostavan zahtjev za odabir pomoću dizajnera upita.
U prvom stupcu označavamo koji zapisi se trebaju prikazati, au drugom - pod kojim uvjetima će se zapisi prikazati. Naravno, za drugo i treće polje akcije ć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 ne dopušta ugrađivanje kao Excel datoteka)
Ove dvije metode postoje za pronalaženje odstupanja u MS Excel tablicama. Svaki ima i prednosti i nedostatke. Očito, ovo nije iscrpan popis usporedbi između dvije Excel datoteke. Čekamo vaše prijedloge u komentarima.
Svaki mjesec osoba iz ljudskih resursa dobiva popis zaposlenika zajedno s njihovim plaćama. Kopira popis na novi list u Excel radnoj knjizi. Zadatak je sljedeći: usporedite plaće zaposlenika koje su se promijenile u odnosu na prethodni mjesec. Da biste to učinili, morate usporediti podatke u Excelu na različitim listovima. Upotrijebimo uvjetno oblikovanje. Na taj način ne samo da ćemo automatski pronaći sve razlike u vrijednostima ćelija, već ih i istaknuti bojom.
Usporedba dva lista u Excelu
Tvrtka može imati više od stotinu zaposlenih, među kojima neki daju otkaz, drugi se zapošljavaju, treći odlaze na godišnji odmor ili bolovanje itd. Kao rezultat toga, može biti teško usporediti podatke o plaćama. Na primjer, prezimena zaposlenika uvijek će biti u različitim sekvencama. Kako napraviti usporedbu između dva Excel tablice na različitim listovima?
Uvjetno oblikovanje pomoći će nam riješiti ovaj težak problem. Na primjer, uzmimo podatke za veljaču i ožujak, kao što je prikazano na slici:
Da biste pronašli promjene na platnim listićima:
Nakon unosa svih uvjeta za Excel formatiranje automatski su bojom označeni zaposlenici kojima su se plaće promijenile u odnosu na prethodni mjesec.
Princip usporedbe dva raspona podataka u Excelu na različitim listovima:
U određenim uvjetima funkcija MATCH je neophodna. Njegov prvi argument sadrži par vrijednosti koje bi se trebale naći u izvornom listu sljedećeg mjeseca, odnosno "ožujak". Raspon koji se može pregledavati definiran je kao par vrijednosti raspona definiranih imenima. Na ovaj način stringovi se uspoređuju na temelju dva obilježja: prezimena i plaće. Za pronađena podudaranja vraća se broj, što je u biti točno za Excel. Stoga biste trebali koristiti funkciju =NOT(), koja vam omogućuje da vrijednost TRUE zamijenite FALSE. U suprotnom, oblikovanje će se primijeniti na ćelije čije se vrijednosti podudaraju. Za svaki par vrijednosti koji nije pronađen (tj. neslaganje) &B2&$C2 u rasponu Prezime&Plaća, funkcija MATCH vraća pogrešku. Vrijednost pogreške nije Booleova vrijednost. Stoga koristimo funkciju IFERROR koja će svakoj pogrešci dodijeliti logičku vrijednost - TRUE. Ovo olakšava dodjelu novog formata samo za ćelije bez podudaranja vrijednosti plaće u odnosu na sljedeći mjesec - ožujak.
Često je zadatak usporediti dvije liste elemenata. Ručno obavljanje toga je previše zamorno, a također se ne može isključiti mogućnost pogrešaka. Excel olakšava ovu operaciju. Ovaj savjet opisuje metodu koja koristi uvjetno oblikovanje.
Na sl. Slika 164.1 prikazuje primjer dva popisa imena u više stupaca. Korištenje uvjetnog oblikovanja može učiniti razlike u popisima očiglednima. Ovi primjeri popisa sadrže tekst, ali predmetna metoda radi i s numeričkim podacima.
Prva lista je A2:B31, ovaj raspon se zove OldList. Druga lista je D2:E31, raspon se zove NewList. Rasponi su imenovani pomoću naredbe Formule Definirani nazivi Dodijelite naziv. Raspone nije potrebno imenovati, ali to olakšava rad s njima.
Počnimo s dodavanjem uvjetnog oblikovanja starom popisu.
- Odaberite ćelije u rasponu OldList.
- Izaberi.
- U prozoru Stvorite pravilo oblikovanja odaberite pozvanu stavku Koristite formulu
- Unesite ovu formulu u polje prozora (Sl. 164.2): =COUNTIF(NewList;A2)=0.
- Pritisnite gumb Format i odredite oblikovanje koje će se primijeniti kada je uvjet istinit. Najbolje je odabrati različite boje ispune.
- Pritisnite OK.
Ćelije u dometu NewList koristite sličnu formulu uvjetnog oblikovanja.
- Odaberite ćelije u rasponu NewList.
- Izaberi Početna Uvjetno oblikovanje Stvorite pravilo za otvaranje dijaloškog okvira Stvorite pravilo oblikovanja.
- U prozoru Stvorite pravilo oblikovanje odaberite stavku Koristite formulu za definiranje ćelija koje treba formatirati.
- Unesite ovu formulu u polje prozora: =COUNTIF(OldList;D2)=0 .
- Pritisnite gumb Format i postavite oblikovanje koje će se primijeniti kada je uvjet istinit (različita boja ispune).
- Pritisnite OK.
Kao rezultat toga, imena koja se nalaze na starom popisu, ali ne i na novom, bit će istaknuta (Sl. 164.3). Osim toga, imena u novom popisu koja nisu u starom popisu također su označena, ali drugom bojom. Imena koja se pojavljuju na oba popisa nisu istaknuta.
Obje formule uvjetnog oblikovanja koriste funkciju COUNTIF. Izračunava koliko se puta određena vrijednost pojavljuje u rasponu. Ako formula vrati 0, to znači da stavka nije u rasponu. Na taj se način uvjetno oblikovanje preuzima i mijenja se boja pozadine ćelije.
Čitanje ovog članka oduzet će vam oko 10 minuta. U sljedećih 5 minuta možete jednostavno usporediti dva stupca u Excelu i otkriti postoje li duplikati u njima, izbrisati ih ili označiti bojom. Dakle, došlo je vrijeme!
Excel je vrlo moćna i stvarno cool aplikacija za stvaranje i obradu velikih količina podataka. Ako imate više radnih knjiga s podacima (ili samo jednu ogromnu tablicu), tada ćete vjerojatno htjeti usporediti 2 stupca, pronaći duplicirane vrijednosti i zatim učiniti nešto s njima, poput brisanja, označavanja ili brisanja sadržaja. Stupci mogu biti u istoj tablici, susjedni ili nesusjedni, smješteni na 2 različita lista ili čak u različitim radnim knjigama.
Zamislite da imamo 2 stupca imena ljudi - 5 imena po stupcu A i 3 imena u stupcu B. Morate usporediti imena u ova dva stupca i pronaći duplikate. Kao što razumijete, ovo su fiktivni podaci uzeti samo u ilustrativne svrhe. U stvarnim tablicama imamo posla s tisućama ili čak desecima tisuća zapisa.
Opcija A: oba su stupca na istom listu. Na primjer, stupac A i stupac B.
Opcija B: Stupci se nalaze na različitim listovima. Na primjer, stupac A na plahti List2 i stupac A na plahti List3.
Excel 2013, 2010 i 2007 imaju ugrađeni alat Ukloni duplikat(Remove Duplicates) ali je u ovoj situaciji nemoćan jer ne može usporediti podatke u 2 stupca. Štoviše, može ukloniti samo duplikate. Nema drugih opcija, poput isticanja ili mijenjanja boja. I točka!
Usporedite 2 stupca u Excelu i pronađite dvostruke unose pomoću formula
Opcija A: oba su stupca na istom listu
Trag: U velikim tablicama kopiranje formule bit će brže ako koristite tipkovničke prečace. Odaberite ćeliju C1 i pritisnite Ctrl+C(za kopiranje formule u međuspremnik), zatim kliknite Ctrl+Shift+End(za odabir svih ćelija koje nisu prazne u stupcu C) i na kraju kliknite Ctrl+V(za lijepljenje formule u sve odabrane ćelije).
Opcija B: dva stupca su na različitim listovima (u različitim knjigama)
Obrada pronađenih duplikata
Odlično, pronašli smo unose u prvom stupcu koji su prisutni i u drugom stupcu. Sada moramo nešto učiniti s njima. Ručno prolaženje kroz sve duplicirane unose u tablici prilično je neučinkovito i oduzima previše vremena. Ima boljih načina.
Prikaži samo duplicirane retke u stupcu A
Ako vaši stupci nemaju naslove, morate ih dodati. Da biste to učinili, postavite kursor na broj koji označava prvi redak i on će se pretvoriti u crnu strelicu, kao što je prikazano na slici ispod:
Desni klik i kontekstni izbornik Izaberi Umetnuti(Umetnuti):
Dajte nazive stupcima, na primjer, " Ime"I" Duplikat?” Zatim otvorite karticu Podaci(Podaci) i pritisnite filtar(Filtar):
Nakon toga kliknite malu sivu strelicu pored " Duplikat?“ za proširenje izbornika filtera; poništite odabir svih stavki na ovom popisu osim Duplikat, i pritisnite u redu.
To je to, sada vidite samo te elemente stupaca A, koji se dupliciraju u stupcu U. U našem stolu za vježbanje postoje samo dvije takve ćelije, ali, kao što razumijete, u praksi će ih biti mnogo više.
Za ponovno prikazivanje svih redaka stupca A, kliknite simbol filtra u stupcu U, koji sada izgleda kao lijevak s malom strelicom i odaberite Odaberi sve(Odaberi sve). Ili možete učiniti isto putem vrpce klikom Podaci(Podaci) > Odaberite i filtrirajte(Sortiraj i filtriraj) > Čisto(Očisti) kao što je prikazano na slici ispod:
Promijenite boju ili označite pronađene duplikate
Ako su oznake " Duplikat” nije dovoljno za vaše potrebe, a vi želite označiti duplicirane ćelije drugom bojom fonta, bojom ispune ili na neki drugi način...
U tom slučaju filtrirajte duplikate kao što je gore prikazano, odaberite sve filtrirane ćelije i kliknite Ctrl+1 za otvaranje dijaloškog okvira Formatiraj ćelije(Format ćelije). Na primjer, promijenimo boju ispune ćelija u redovima s duplikatima u svijetložutu. Naravno, možete promijeniti boju ispune pomoću alata Ispunite(Boja ispune) kartica Dom(Home), ali prednost dijaloškog okvira Formatiraj ćelije(Format Cells) znači da možete konfigurirati sve opcije oblikovanja odjednom.
Sada sigurno nećete propustiti nijednu ćeliju s duplikatima:
Uklanjanje dupliciranih vrijednosti iz prvog stupca
Filtrirajte tablicu tako da prikazuje samo ćelije s dupliciranim vrijednostima i odaberite te ćelije.
Ako su 2 stupca koja uspoređujete na različitim listovima, odnosno u različitim tablicama desnom tipkom miša kliknite odabrani raspon i odaberite iz kontekstnog izbornika Izbriši redak(Izbriši redak):
Klik u redu kada vas Excel pita da potvrdite da stvarno želite izbrisati cijeli red radnog lista i zatim poništite filtar. Kao što vidite, preostali su samo redovi s jedinstvenim vrijednostima:
Ako se na jednom listu nalaze 2 stupca, blizu jedan drugome (susjedni) ili ne blizu (nisu susjedni), tada će postupak uklanjanja duplikata biti malo teži. Ne možemo izbrisati cijeli redak s dupliciranim vrijednostima jer bismo time izbrisali i ćelije iz drugog stupca. Dakle, da zadržite samo jedinstvene unose u stupcu A, učinite sljedeće:
Kao što vidite, uklanjanje duplikata iz dva stupca u Excelu pomoću formula nije tako teško.
Č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 generirati 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 velikim količinama informacija, korisnik se može suočiti sa zadatkom kao što je usporedba dvaju tabelarnih izvora podataka. Prilikom pohranjivanja podataka u jedinstveni sustav računovodstvo (na primjer, sustavi temeljeni na 1C Enterprise, sustavi koji koriste SQL baza podataka podaci), mogućnosti ugrađene u sustav ili DBMS mogu se koristiti za usporedbu podataka. U pravilu, da biste to učinili, dovoljno je uključiti programera koji će napisati upit u bazu podataka ili softverski mehanizam za izvješćivanje. Iskusni korisnik koji ima vještinu pisanja 1C ili SQL upita može obraditi zahtjev.
Problemi počinju kada se hitno treba izvršiti zadatak usporedbe podataka, a angažiranje programera i pisanje upita 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 problema za programera će zvučati kao integracija dvaju sustava. Rješavanje takvog problema zahtijevat će više kvalificiranog programera i također će oduzeti više vremena nego razvoj u jednom sustavu.
Za rješavanje ovih problema, idealna tehnika je korištenje uređivača proračunskih tablica za usporedbu podataka. Microsoft Excel. Većina uobičajenih upravljačkih i regulatornih računovodstvenih sustava podržava učitavanje u Excel format. Za ovaj će zadatak biti potrebna samo određena kvalifikacija korisnika za rad s ovim uredsko odijelo i ne zahtijeva vještine programiranja.
Pogledajmo rješenje problema usporedbe tablica u Excelu na primjeru. Imamo dvije tablice s popisima stanova. Izvori za učitavanje - 1C Enterprise (računovodstvo izgradnje) i Excel tablica (računovodstvo prodaje). Tablice se u radnoj knjizi programa Excel nalaze na prvom, odnosno drugom listu.
Naš zadatak je usporediti te popise po adresi. Prva tablica sadrži sve stanove u zgradi. U drugoj tablici nalaze se samo prodani stanovi i ime kupca. Krajnji cilj je ispisati ime kupca u prvoj tablici za svaki stan (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 kat (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 kombinirati nekoliko tekstualne vrijednosti u jednom redu. Vrijednosti u funkciji navedene su odvojene simbolom ";". Vrijednosti mogu biti ili adrese ćelija ili proizvoljni tekst naveden u navodnicima.
Korak 1. Umetnimo prazan stupac "A" na početku prve tablice i napišimo formulu u ćeliju tog stupca nasuprot prvom redu s podacima:
=SPAJANJE(B3;"-";C3;"-";D3;"-";E3)
Radi lakše vizualne percepcije, postavili smo simbole "-" između vrijednosti ćelija koje se spajaju.
Korak 2. Kopirajmo formulu u sljedeće ćelije u stupcu A.
Korak 4. Za usporedbu Excel tablica po vrijednostima koristite funkciju VLOOKUP(). Svrha funkcije VLOOKUP() je tražiti vrijednost u krajnjem lijevom stupcu tablice i vratiti vrijednost ćelije koja se nalazi 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 u pronađenom retku biti vraćena vrijednost. Č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 smo trebali dodati imena kupaca), u nju ćemo napisati formulu. Kreirajmo formulu u slobodnom stupcu desno od tablice nasuprot prvom redu podataka:
=VLOOKUP(A3,Sheet2!$A$3:$F$10,6,FALSE)
Kada kopirate formule, Smart Excel automatski mijenja adresu ćelije. U našem slučaju mijenjat će se tražena vrijednost za svaki red: A3, A4 itd., ali adresa tablice u kojoj se traži mora ostati nepromijenjena. Da bismo to učinili, popravljamo ćelije u parametru adrese tablice sa simbolima "$". Umjesto "Sheet2!A3:F10" napravimo "Sheet2!$A$3:$F$10".
