Poređenje Excel podataka. Kako uporediti dvije kolone u Excelu radi podudaranja

Za čitanje ovog članka potrebno vam je oko 10 minuta. U sljedećih 5 minuta možete jednostavno usporediti dva stupca u Excelu i saznati postoje li u njima duplikati, izbrisati ih ili označiti bojom. Došlo je vreme!

Excel je vrlo moćna i zaista cool aplikacija za stvaranje i rukovanje velikom količinom podataka. Ako imate nekoliko radnih knjiga s podacima (ili samo jednu ogromnu tablicu), vjerojatno biste željeli usporediti 2 stupca, pronaći duplicirane vrijednosti, a zatim poduzeti neke radnje s njima, na primjer, izbrisati, označiti ili obrisati sadržaj. ... Kolone mogu biti u istoj tablici, biti susjedne ili ne susjedne, mogu se nalaziti na 2 različita lista ili čak u različitim knjigama.

Zamislite da imamo 2 kolone imena ljudi - 5 imena u koloni A i 3 imena u koloni B... Potrebno je uporediti imena u ove dvije kolone i pronaći duplikate. Kako razumijete, ovo su izmišljeni podaci, uzeti samo kao primjer. U stvarnim tabelama imamo posla sa hiljadama, ako ne i sa desetinama hiljada zapisa.

Opcija A: obje kolone su na istom listu. Na primjer stupac A i kolona B.

Opcija B: Stupci se nalaze na različitim listovima. Na primjer stupac A na listu List2 i kolona A na listu List3.

Excel 2013, 2010 i 2007 imaju ugrađen alat Ukloni Duplicate(Uklonite duplikate), ali je nemoćan u takvoj situaciji jer ne može usporediti podatke u 2 kolone. Štoviše, može ukloniti samo duplikate. Ne postoje druge opcije, poput isticanja ili mijenjanja boja. I poenta!

Uporedite 2 kolone u Excelu i pronađite dupli zapis pomoću formula

Opcija A: obje kolone su na istom listu

Odziv: U velikim proračunskim tablicama kopiranje formule bit će brže ako koristite prečice na tipkovnici. Odaberite ćeliju C1 i pritisnite Ctrl + C(za kopiranje formule u međuspremnik), zatim pritisnite Ctrl + Shift + End(za odabir svih praznih ćelija u koloni C) i na kraju pritisnite Ctrl + V(za umetanje formule u sve odabrane ćelije).

Opcija B: dvije kolone su na različitim listovima (u različitim knjigama)

Obrada pronađenih duplikata

Odlično, pronašli smo unose u prvoj koloni koji su takođe prisutni u drugoj koloni. Sada moramo nešto učiniti s njima. Ručni pregled svih dupliciranih zapisa u tablici prilično je neučinkovit i oduzima puno vremena. Postoje bolji načini.

Prikaži samo duple redove u koloni A

Ako vaši stupci nemaju zaglavlja, morate ih dodati. Da biste to učinili, postavite kursor na broj koji predstavlja prvi redak i on će se pretvoriti u crnu strelicu, kao što je prikazano na donjoj slici:

Desnom tipkom miša kliknite i odaberite Insert(Umetni):

Navedite nazive stupaca, na primjer, “ Ime"i" Duplikat?”Zatim otvorite karticu Podaci(Podaci) i pritisnite Filter(Filter):

Zatim kliknite malu sivu strelicu pored “ Duplikat?„Da biste proširili meni filtera; poništite sve stavke na ovoj listi osim Duplikat i pritisnite uredu.

To je sve, sada vidite samo one elemente kolone A koji su duplicirani u koloni V... U našoj tablici za proučavanje postoje samo dvije takve ćelije, ali, kako razumijete, u praksi ih je mnogo više.

Za ponovno prikazivanje svih redova kolone A, kliknite simbol filtera u koloni V koji sada izgleda kao lijevak s malom strelicom i odaberite Označi sve(Označi sve). Alternativno, možete učiniti isto kroz Feed klikom Podaci(Podaci)> Odaberite & Filtriraj(Sortiraj i filtriraj)> Clear(Obriši) 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 i želite označiti ćelije koje se ponavljaju drugom bojom fonta, bojom ispune ili na neki drugi način ...

U tom slučaju filtrirajte duplikate kao što je prikazano gore, odaberite sve filtrirane ćelije i kliknite Ctrl + 1 za otvaranje dijaloškog okvira Oblikovanje ćelija(Format ćelije). Kao primjer, promijenimo boju ispune ćelija u dupliciranim redovima u svijetlo žutu. Naravno, pomoću alata možete promijeniti boju ispune Fill(Boja ispune) na kartici Dom(Početna) ali prednost dijaloškog okvira Oblikovanje ćelija(Format ćelije) je da možete konfigurirati sve opcije oblikovanja istovremeno.

Sada definitivno nećete propustiti nijednu ćeliju s duplikatima:

Uklanjanje dupliciranih vrijednosti iz prve kolone

Filtrirajte tablicu tako da se prikazuju samo ćelije s dvostrukim vrijednostima i odaberite te ćelije.

Ako se dvije kolone koje upoređujete nalaze na različitim listovima, to jest, u različitim tablicama, desnom tipkom miša kliknite odabrani raspon i odaberite Izbriši red(Izbriši red):

Kliknite na uredu kada vas Excel zatraži da potvrdite da zaista želite izbrisati cijeli red radnog lista, a zatim obrišite filtar. Kao što vidite, preostali su samo redovi s jedinstvenim vrijednostima:

Ako su 2 kolone na istom listu, blizu jedan drugom (susjedni) ili ne blizu jedan drugom (nisu susjedni), tada će proces uklanjanja duplikata biti malo teži. Ne možemo izbrisati cijeli red s dupliciranim vrijednostima, jer će ovo izbrisati ćelije i iz druge kolone. Dakle, ostaviti samo jedinstvene unose u koloni A, uradite ovo:

Kao što vidite, uklanjanje duplikata iz dva stupca u Excelu pomoću formula nije tako teško.

Često se korisnici programa Excel susreću sa zadatkom da usporede dvije tablice ili liste kako bi identificirali razlike ili stavke koje nedostaju. Svaki se korisnik s ovim zadatkom nosi na svoj način, ali najčešće se na rješavanje ovog problema troši prilično mnogo vremena, jer nisu svi pristupi ovom problemu racionalni. Istodobno, postoji nekoliko provjerenih algoritama za radnje koji će vam omogućiti da usporedite liste ili nizove tablica u prilično kratkom vremenu uz minimalan napor. Pogledajmo pobliže ove opcije.

Postoji nekoliko načina za usporedbu područja tablica u Excelu, ali svi se mogu podijeliti u tri velike grupe:

poređenje lista na istom listu;

poređenje tabela koje se nalaze na različitim listovima;

poređenje raspona tabela u različitim datotekama.

Na temelju ove klasifikacije odabiru se, prije svega, metode usporedbe i određuju određene radnje i algoritmi za izvršavanje zadatka. Na primjer, prilikom uspoređivanja u različitim radnim knjigama morate otvoriti dvije Excel datoteke istovremeno.

Osim toga, treba reći da ima smisla uspoređivati tablične prostore samo ako imaju sličnu strukturu.

Metoda 1: jednostavna formula

Najlakši način za usporedbu podataka u dvije tablice je pomoću jednostavne formule jednakosti. Ako se podaci podudaraju, tada vraća indikator TRUE, a ako ne, onda - FALSE. Možete usporediti i numeričke podatke i tekst. Nedostatak ove metode je što se može koristiti samo ako su podaci u tablici poredani ili sortirani isti, sinhronizirani i imaju jednak broj redova. Pogledajmo kako primijeniti ovu metodu u praksi na primjeru dvije tablice postavljene na jedan list.

Dakle, imamo dvije jednostavne tabele sa spiskovima zaposlenih u preduzeću i njihovim platama. Potrebno je uporediti spiskove zaposlenih i identifikovati nedosljednosti između kolona u kojima su imena smještena.

Za to nam je potrebna dodatna kolona na listu. Tamo unosimo znak «=» ... Zatim kliknemo na prvu stavku koju ćemo uporediti na prvoj listi. Ponovo stavite simbol «=» sa tastature. Zatim kliknite na prvu ćeliju kolone koju poredimo u drugoj tabeli. Rezultat je izraz sljedeće vrste:

Iako će, naravno, u svakom konkretnom slučaju koordinate biti različite, ali suština će ostati ista.
Kliknite na ključ Enter da biste dobili rezultate poređenja. Kao što vidite, prilikom usporedbe prvih ćelija s obje liste, program je naznačio indikator "ISTINITO", što znači podudaranje podataka.

Sada moramo izvršiti sličnu operaciju s ostalim ćelijama obje tablice u stupcima koje uspoređujemo. Ali formulu možete jednostavno kopirati, što će značajno uštedjeti vrijeme. Ovaj faktor je posebno važan pri usporedbi lista s velikim brojem nizova.
Postupak kopiranja najjednostavnije je izvršiti ručkom za popunjavanje. Zadržite pokazivač iznad donjeg desnog kuta ćelije gdje smo dobili indikator "ISTINITO"... U ovom slučaju treba ga pretvoriti u crni križ. Ovo je oznaka punjenja. Pritisnite lijevu tipku miša i povucite kursor prema dolje na broj redova u upoređenim nizovima tablica.

Kao što vidite, sada dodatna kolona prikazuje sve rezultate poređenja podataka u dvije kolone nizova tabela. U našem slučaju, podaci se ne podudaraju samo u jednom retku. Kada ih usporedimo, formula je dala rezultat "LAŽE"... Za sve ostale redove, kao što vidite, usporedna formula dala je pokazatelj "ISTINITO".

Osim toga, moguće je izračunati broj neusklađenosti pomoću posebne formule. Da biste to učinili, odaberite element lista na kojem će biti prikazan. Zatim kliknite na ikonu "Umetni funkciju".

U prozoru Čarobnjaci za funkcije u grupi operatera "Matematički" označite ime SUMPRODUCT... Kliknite na dugme "UREDU".

Prozor argumenata funkcija je aktiviran SUMPRODUCT, čiji je glavni zadatak izračunati zbroj proizvoda odabranog asortimana. Ali ova funkcija se može koristiti i u naše svrhe. Njegova sintaksa je prilično jednostavna:
SUMPRODUCT (niz1; niz2; ...)

Ukupno, adrese do 255 nizova mogu se koristiti kao argumenti. Ali u našem slučaju, koristit ćemo samo dva niza, štoviše, kao jedan argument.

Stavili smo kursor u polje "Polje1" i odaberite na listu upoređeni raspon podataka u prvom području. Nakon toga na polje stavljamo znak "nije jednako" (<> ) i odaberite upoređeni raspon druge regije. Zatim umotavamo rezultirajući izraz u zagrade, prije čega stavljamo dva znaka «-» ... U našem slučaju dobili smo sljedeći izraz:

- (A2: A7<>D2: D7)

Kliknite na dugme "UREDU".

Operater izračunava i prikazuje rezultat. Kao što vidite, u našem slučaju rezultat je jednak broju "1" to znači da je pronađeno jedno neslaganje u upoređenim listama. Da su liste potpuno identične, rezultat bi bio jednak broju «0» .

Na isti način možete uporediti podatke u tablicama koje se nalaze na različitim listovima. No, u ovom je slučaju poželjno da su redovi u njima numerirani. Inače, postupak usporedbe je gotovo potpuno isti kao što je gore opisano, osim činjenice da ćete prilikom unosa formule morati prebacivati između listova. U našem slučaju izraz će izgledati ovako:

B2 = List2! B2

Odnosno, kao što vidimo, prije koordinata podataka koji se nalaze na drugim listovima, za razliku od mjesta na kojem je prikazan rezultat usporedbe, označeni su broj lista i uskličnik.

Metoda 2: odabir grupa ćelija

Usporedba se može napraviti pomoću alata za odabir grupe ćelija. Takođe vam omogućava da uporedite samo sinhronizovane i poređane liste. Osim toga, u ovom slučaju liste se trebaju nalaziti jedna do druge na istom listu.

Metoda 3: uslovno oblikovanje

Poređenja se mogu napraviti metodom uslovnog oblikovanja. Kao i u prethodnoj metodi, upoređena područja moraju biti na istom radnom listu programa Excel i međusobno sinhronizirana.

Postoji još jedan način korištenja uvjetnog oblikovanja za izvršavanje trenutnog zadatka. Kao i prethodne opcije, zahtijeva lokaciju obje usporedbe područja na istom listu, ali za razliku od prethodno opisanih metoda, uvjet sinkronizacije ili razvrstavanja podataka neće biti potreban, što ovu opciju povoljno razlikuje od prethodno opisanih.

Po želji, možete, naprotiv, obojiti neusklađene elemente i ostaviti one indikatore koji se podudaraju s ispunom u istoj boji. U ovom slučaju, algoritam radnji je praktički isti, ali u prozoru postavki za isticanje dupliciranih vrijednosti u prvom polju umjesto parametra "Ponavlja se" parametar treba odabrati "Jedinstven"... Nakon toga kliknite na dugme "UREDU".

Tako će se istaknuti pokazatelji koji se ne podudaraju.

Metoda 4: složena formula

Takođe možete uporediti podatke koristeći složenu formulu zasnovanu na funkciji COUNTIF... Pomoću ovog alata možete izračunati koliko se svaki element iz odabrane kolone druge tablice ponavlja u prvoj.

Operater COUNTIF pripada statističkoj grupi funkcija. Njegov je zadatak brojati broj ćelija u kojima vrijednosti zadovoljavaju zadani uvjet. Sintaksa za ovaj operator je sljedeća:

COUNTIF (raspon, kriterij)

Argument "Raspon" je adresa niza u kojoj se broje odgovarajuće vrijednosti.

Argument "Kriterij" navodi uslov podudaranja. U našem slučaju, ona će predstavljati koordinate određenih ćelija u prvom području tablice.

Naravno, radi usporedbe tabelarnih pokazatelja, ovaj izraz se može primijeniti u postojećem obliku, ali postoji mogućnost njegovog poboljšanja.

Učinimo tako da se vrijednosti koje se nalaze u drugoj tablici, ali ne i u prvoj, prikažu na zasebnoj listi.

Prije svega, hajde da malo promijenimo formulu. COUNTIF, naime, učinit ćemo to jednim od argumenata operatora IF... Da biste to učinili, odaberite prvu ćeliju u kojoj se nalazi operator COUNTIF... U traku s formulama, prije toga, dodajte izraz "AKO" bez navodnika i otvorite zagrade. Zatim, kako biste nam olakšali rad, odaberite vrijednost u traci s formulama "AKO" i kliknite na ikonu "Umetni funkciju".

Otvara prozor argumenata funkcije IF... Kao što vidite, prvo polje prozora već je ispunjeno vrijednošću operatora COUNTIF... Ali moramo dodati još nešto ovom polju. Tu smo postavili kursor i dodali već postojeći izraz «=0» bez navodnika.
Nakon toga idite na teren "Vrijednost ako je tačno"... Ovdje ćemo koristiti još jednu ugniježđenu funkciju - LINE... Unosimo reč "LINIJA" bez navodnika, zatim otvorite zagrade i označite koordinate prve ćelije s prezimenom u drugoj tabeli, a zatim zatvorite zagrade. Konkretno, u našem slučaju, na terenu "Vrijednost ako je tačno" dobio sledeći izraz:

RED (D2)

Sada operater LINEće komunicirati funkcije IF broj retka u kojem se nalazi određeno prezime, a u slučaju kada će biti ispunjen uvjet naveden u prvom polju, funkcija IFće ovaj broj prenijeti u ćeliju. Kliknite na dugme "UREDU".

Kao što vidite, prvi rezultat je prikazan kao "LAŽE"... To znači da vrijednost ne ispunjava uvjete operatora IF... Odnosno, prvo prezime prisutno je na obje liste.

Koristeći oznaku ispune, kopirajte izraz operatora na uobičajen način IF celu kolonu. Kao što vidite, za dvije pozicije koje su prisutne u drugoj tabeli, ali ne i u prvoj, formula daje brojeve redova.

Odstupamo od područja tablice udesno i popunjavamo kolonu rednim brojevima, počevši od 1 ... Broj brojeva mora odgovarati broju redova u drugoj uporednoj tabeli. Također možete koristiti oznaku za popunjavanje da biste ubrzali proces numeriranja.

Nakon toga odaberite prvu ćeliju desno od stupca s brojevima i kliknite ikonu "Umetni funkciju".

Otvara se Čarobnjak za funkcije... Idite na kategoriju "Statistika" i odaberite ime "NAJMANJE"... Kliknite na dugme "UREDU".

Funkcija NAJMANJE, čiji je prozor sa argumentima otvoren, ima namjeru prikazati najnižu vrijednost koju navodi count.
Na terenu "Polje" trebali biste navesti koordinate raspona dodatne kolone "Broj slučajnosti" koje smo prethodno transformirali pomoću funkcije IF... Sve linkove činimo apsolutnim.

Na terenu "K" označava najmanju vrijednost koja se prikazuje. Ovdje označavamo koordinate prve ćelije stupca s numeriranjem, koje smo nedavno dodali. Ostavite adresu relativnom. Kliknite na dugme "UREDU".

Operater ispisuje rezultat - broj 3 ... Ovo je najmanji broj nepodudarnih redova u nizovima tablica. Koristeći ručicu za popunjavanje, kopirajte formulu na samo dno.

Sada, znajući brojeve redaka nepodudarnih elemenata, pomoću funkcije možemo umetnuti u ćeliju i njihove vrijednosti INDEKS... Odaberite prvi element lista koji sadrži formulu NAJMANJE... Nakon toga idite na traku s formulama i prije imena "NAJMANJE" dodajte ime "INDEX" bez navodnika, odmah otvorite zagrade i stavite točku -zarez ( ; ). Zatim odaberite ime na traci s formulama "INDEX" i kliknite na ikonu "Umetni funkciju".

Nakon toga otvara se mali prozor u kojem trebate odrediti treba li funkcija imati obrazac reference INDEKS ili dizajnirani za rad s nizovima. Treba nam druga opcija. Standardno je instaliran, pa u ovom prozoru samo kliknite gumb "UREDU".

Pokreće se prozor argumenata funkcija INDEKS... Ovaj operator ima namjeru prikazati vrijednost koja se nalazi u određenom nizu u navedenom retku.
Kao što vidite, polje "Broj linije" već ispunjene vrijednostima funkcija NAJMANJE... Razliku između numeriranja Excel lista i internog numeriranja područja tablice treba oduzeti od već postojeće vrijednosti. Kao što vidite, imamo samo zaglavlje iznad vrijednosti tablice. To znači da je razlika jedna linija. Stoga dodajemo u polje "Broj linije" značenje "-1" bez navodnika.

Na terenu "Polje" navedite adresu raspona vrijednosti druge tablice. U ovom slučaju sve koordinate činimo apsolutnim, odnosno stavljamo znak dolara ispred njih na način koji smo već opisali.

Kliknite na dugme "UREDU".

Nakon prikaza rezultata na ekranu, proširite funkciju pomoću markera za popunjavanje do kraja stupca prema dolje. Kao što vidite, oba prezimena koja su prisutna u drugoj tabeli, ali ih nema u prvoj, prikazana su u zasebnom rasponu.

Metoda 5: poređenje niza u različitim knjigama

Prilikom usporedbe raspona u različitim radnim knjigama možete koristiti gore navedene metode, osim onih opcija koje zahtijevaju da se oba tabelarna prostora postave na isti list. Glavni uvjet za provođenje postupka usporedbe u ovom slučaju je otvaranje prozora obje datoteke istovremeno. Za verzije programa Excel 2013 i novije verzije, kao i za verzije prije programa Excel 2007, nema problema s ovim stanjem. No, u programima Excel 2007 i Excel 2010, za otvaranje oba prozora istovremeno, potrebne su dodatne manipulacije. Kako to učiniti opisano je u zasebnoj lekciji.

Kao što vidite, postoji niz mogućnosti za međusobno upoređivanje tabela. Koja će se opcija koristiti ovisi o tome gdje se tabelarni podaci nalaze međusobno (na istom listu, u različitim knjigama, na različitim listovima), kao i od toga kako korisnik želi da se ovo poređenje prikaže na ekranu.

Recimo da želite uporediti verzije radne sveske, analizirati radnu svesku na probleme ili nedosljednosti ili vidjeti veze između radnih knjiga ili radnih listova. Ako je na vašem računaru instaliran Microsoft Office 365 ili Office Professional Plus 2013, dodatak Spreadheet Inquire dostupan je u Excelu.

Za izvršavanje svih ovih zadataka i više možete koristiti naredbe na kartici Upit. Kartica Upit na Excel vrpci ima gumbe za naredbe opisane u nastavku.

Ako ne vidite Raspitati se karticu u Excelovoj vrpci potražite u odjeljku Uključivanje dodatka Upit za proračunske tablice.

Uporedite dve radne sveske

The Uporedi datoteke naredba vam omogućuje da vidite razlike, dvije po dvije ćelije, između ćelija. Da biste pokrenuli ovu naredbu, morate imati otvorene dvije radne knjige u Excelu.

Rezultati su kodirani bojom prema vrsti sadržaja, kao što su unesene vrijednosti, formule, imenovani rasponi i formati. Postoji čak i prozor koji može prikazati izmjene VBA koda redak po redak. Razlike između ćelija prikazane su u rasporedu mreže koji se lako čita, poput ovog:

The Uporedi datoteke naredba koristi Microsoft Spreadsheet Compare za usporedbu dvije datoteke. U sustavu Windows 8 možete pokrenuti usporedbu proračunske tablice izvan programa Excel klikom na Uporedi proračunsku tablicu na Apps ekran. U operativnom sistemu Windows 7 kliknite na Windows Počni dugme, a zatim> Svi programi > Microsoft Office 2013 > Alati za Office 2013 > Tabela Uporedi 2013.

Da biste saznali više o Tablici za usporedbu i usporedbu datoteka, pročitajte Uporedite dvije verzije radne knjige.

Analizirajte radnu svesku

The Analiza radne sveske naredba stvara interaktivni izvještaj koji prikazuje detaljne informacije o radnoj knjizi i njenoj strukturi, formulama, ćelijama, rasponima i upozorenjima. Slika ovdje prikazuje vrlo jednostavnu radnu knjigu koja sadrži dvije formule i podatkovne veze s Access bazom podataka i tekstualnom datotekom.

Pokaži veze radne knjige

Radne sveske povezane sa drugim radnim sveskama putem referenci ćelija mogu da zbune. Koristite dugme za kreiranje interaktivne, grafičke mape zavisnosti radne sveske kreirane vezama (vezama) između datoteka. Vrste veza u dijagramu mogu uključivati druge radne knjige, Accessove baze podataka, tekstualne datoteke, HTML stranice, SQL Server baze podataka i druge izvore podataka. U dijagramu odnosa možete odabrati elemente i pronaći više informacija o njima, a zatim povući linije veze da biste promijenili oblik dijagrama.

Ovaj dijagram prikazuje trenutnu radnu knjigu s lijeve strane i veze između nje i drugih radnih knjiga i izvora podataka. Takođe prikazuje dodatne nivoe veza radne sveske, dajući vam sliku porekla podataka za radnu svesku.

Prikaži veze do radnog lista

Imate li puno radnih listova koji zavise jedan od drugog? Koristite dugme za kreiranje interaktivne, grafičke mape veza (veza) između radnih listova u istoj radnoj svesci i u drugim radnim sveskama. Ovo vam pomaže da dobijete jasniju sliku o tome kako vaši podaci mogu ovisiti o ćelijama na drugim mjestima.

Ovaj dijagram prikazuje odnose između radnih listova u četiri različite radne sveske, sa zavisnostima između radnih listova u istoj radnoj svesci, kao i veze između radnih listova u različitim radnim sveskama. Kada postavite pokazivač iznad čvora u dijagramu, poput radnog lista pod nazivom "Zapad" u dijagramu, pojavit će se balončić s informacijama.

Pokažite ćelijske odnose

Da biste dobili detaljan, interaktivni dijagram svih veza iz odabrane ćelije do ćelija na drugim radnim listovima ili čak u drugim radnim knjigama, koristite Odnos ćelija alat. Ovi odnosi s drugim ćelijama mogu postojati u formulama ili referencama na imenovane raspone. Dijagram može prekrižiti radne listove i radne bilježnice.

Ovaj dijagram prikazuje dva nivoa ćelijskih odnosa za ćeliju A10 na listu 5 u Book1.xlsx. Ova ćelija ovisi o ćeliji C6 na listu 1 u drugoj radnoj knjizi, Book2.xlsx. Ova ćelija je presedan za nekoliko ćelija na drugim radnim listovima u istoj datoteci.

Da biste saznali više o gledanju ćelijskih odnosa, pročitajte Pogledajte veze između ćelija.

Očistite suvišno oblikovanje ćelija

Jeste li ikada otvorili radnu knjigu i otkrili da se polako učitava, ili je postala ogromna? Možda je oblikovanje primijenjeno na redove ili stupce za koje niste svjesni. Koristite Očistite suvišno oblikovanje ćelija naredba za uklanjanje viška oblikovanja i značajno smanjenje veličine datoteke. Ovo vam pomaže da izbjegnete "nadutost proračunske tablice", što poboljšava brzinu programa Excel.

Upravljajte lozinkama

Ako koristite funkcije Upit za analizu ili upoređivanje radnih knjiga zaštićenih lozinkom, morat ćete dodati lozinku radne knjige na listu lozinki kako bi Inquire mogao otvoriti spremljenu kopiju vaše radne knjige. Koristiti Lozinke za radnu svesku naredba na Raspitati se karticu za dodavanje lozinki koje će biti spremljene na vašem računaru. Ove lozinke su šifrirane i samo vama možete pristupiti.

Članak daje odgovore na sljedeća pitanja:

Kako uporediti dvije tablice u Excelu?
Kako usporediti složene proračunske tablice u Excelu?
Kako uporediti tablice u Excelu pomoću funkcije VLOOKUP ()?
Kako generirati jedinstvene identifikatore reda ako je njihova jedinstvenost u početku određena skupom vrijednosti u nekoliko stupaca?
Kako zamrznuti 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 tabelarna izvora podataka. Prilikom pohranjivanja podataka u jedan računovodstveni sistem (na primjer, sistemi zasnovani na 1C Enterprise, sistemi koji koriste SQL baze podataka), mogućnosti ugrađene u sistem ili DBMS mogu se koristiti za usporedbu podataka. U pravilu je za to dovoljno uključiti programera koji će upisati upit u bazu podataka ili mehanizam softverskog izvješća. Iskusni korisnik koji ima vještinu pisanja 1C ili SQL upita također može upravljati upitom.

Problemi počinju kada morate hitno izvršiti zadatak usporedbe podataka, a angažiranje programera i njegovo pisanje zahtjeva ili izvještaja o programu na vrijeme može premašiti rokove koji su postavljeni za rješavanje zadatka. Drugi podjednako čest problem je potreba za upoređivanjem informacija iz različitih izvora. U ovom slučaju, izjava problema za programera će zvučati kao integracija dva sistema. Rješenje takvog zadatka zahtijevat će višu kvalifikaciju programera, a također će oduzeti više vremena nego razvoj u jednom sistemu.

Za rješavanje navedenih problema, idealna tehnika je upotreba uređivača proračunskih tablica Microsoft Excel za usporedbu podataka. Većina uobičajenih upravljačkih i regulatornih računovodstvenih sistema podržava izvoz u Excel format. Ovaj zadatak će zahtijevati samo određenu korisničku kvalifikaciju za rad s ovim uredskim paketom i neće zahtijevati vještine programiranja.

Razmotrimo rješenje problema usporedbe tablica u Excelu na primjeru. Imamo dvije tablice koje sadrže popis stanova. Izvori istovara - 1C Enterprise (građevinsko računovodstvo) i tablica u Excelu (računovodstvo prodaje). Tabele se nalaze u radnoj svesci programa Excel na prvom i drugom listu.

Naš je zadatak uporediti ove liste prema adresi. Prva tablica sadrži sve stanove u kući. Druga tabela sadrži samo prodate stanove i ime kupca. Krajnji cilj je prikazati ime kupca u prvoj tabeli za svaki stan (za one stanove koji su prodani). Zadatak je kompliciran činjenicom da je adresa stana u svakoj tablici građevinska i da se sastoji od nekoliko polja: 1) adresa zgrade (kuće), 2) odjeljak (ulaz), 3) kat, 4) broj na podu (na primjer, od 1 do 4) ...

Za usporedbu dvije Excel tablice, moramo osigurati da je svaki red u obje tablice identificiran jednim polom, a ne četiri. Takvo polje možete dobiti spajanjem vrijednosti četiri polja adrese s funkcijom Concatenate (). Svrha funkcije Concatenate () je kombiniranje nekoliko tekstualnih vrijednosti u jedan red. Vrijednosti u funkciji navedene su kroz simbol ";". Vrijednosti mogu biti adrese ćelija ili proizvoljan tekst naveden pod navodnicima.

Korak 1. Umetnimo praznu kolonu "A" na početak prve tablice i upišemo formulu u ćeliju ove kolone nasuprot prvom retku s podacima:
= CONCATENATE (B3; "-"; C3; "-"; D3; "-"; E3)
Radi praktičnosti vizualne percepcije, između vrijednosti ćelija koje se spajaju postavljamo simbole "-".

Korak 2. Kopirajte formulu u sljedeće ćelije u stupcu A.

Korak 4. Za usporedbu Excel tablica prema vrijednostima, upotrijebite funkciju VLOOKUP (). Svrha funkcije VLOOKUP () je pronaći vrijednost u krajnjem lijevom stupcu tablice i vratiti vrijednost ćelije koja se nalazi u navedenom stupcu istog reda. Prvi parametar je željena vrijednost. Drugi parametar je tablica u kojoj će se tražiti vrijednost. Treći parametar je broj stupca iz kojeg će se vrijednost vratiti u pronađenom retku. Četvrti parametar je vrsta pretraživanja: false je potpuno podudaranje, true je približno podudaranje. Budući da izlazne informacije treba staviti u prvu tablicu (upravo u ovoj tablici su morala biti dodana imena kupaca), tada će formula biti zapisana u njoj. Oblikujmo formulu u slobodnom stupcu desno od tablice nasuprot prvom retku podataka:
= VLOOKUP (A3; List2! $ A $ 3: $ F $ 10; 6; FALSE)
Prilikom kopiranja formula, pametni Excel automatski mijenja adresiranje ćelije. U našem slučaju, željena vrijednost za svaki red će se promijeniti: A3, A4, itd., A adresa tablice u kojoj se traži pretraživanje mora ostati nepromijenjena. Da biste to učinili, popravite ćelije u parametru adrese tablice sa simbolima "$". Umjesto "List2! A3: F10" napravimo "List 2! $ A $ 3: $ F $ 10".

Nakon instaliranja dodatka, imat ćete novu karticu s naredbom za pozivanje funkcije. Kada kliknete na naredbu Poređenje raspona pojavljuje se dijaloški okvir za unos parametara.

Ovaj makro vam omogućuje usporedbu tablica bilo koje veličine i s bilo kojim brojem stupaca. Poređenje tabela može se vršiti u jednoj, dvije ili tri kolone istovremeno.

Dijalog je podijeljen u dva dijela: lijevo za prvu tablicu i desno za drugu.

Za usporedbu tablica slijedite ove korake:

Navedite raspone tablica.
Postavite polje za potvrdu (polje za potvrdu / polje za potvrdu) ispod izabranog opsega tabela ako tabela sadrži zaglavlje (red zaglavlja).
Odaberite stupce lijeve i desne tablice po kojima će se izvršiti usporedba (ako rasponi tablica ne uključuju zaglavlja, stupci će biti numerirani).
Odredite vrstu poređenja.
Odaberite opciju za prikaz rezultata.

Uporedni tip tabela

Program vam omogućuje odabir nekoliko vrsta usporedbe tablica:

Pronađite redove iz jedne tablice koji nedostaju u drugoj tablici

Kada se odabere ova vrsta usporedbe, program traži redove iz jedne tablice koji nedostaju u drugoj. Ako usporedite tablice po nekoliko stupaca, rezultat rada bit će redovi u kojima postoji razlika u barem jednoj od kolona.

Pronađite odgovarajuće linije

Prilikom odabira ove vrste usporedbe, program pronalazi redove koji se podudaraju u prvoj i drugoj tablici. Redovi se smatraju podudarnima ako se vrijednosti u odabranim usporednim stupcima (1, 2, 3) jedne tablice u potpunosti podudaraju sa vrijednostima stupaca druge tablice.

Primjer rada programa u ovom načinu rada prikazan je desno na slici.

Tablice podudaranja na osnovu odabranih

U ovom načinu usporedbe, nasuprot svakog retka prve tablice (odabrane kao glavne), kopiraju se podaci odgovarajućeg retka druge tablice. Ako nema podudarnih redova, red nasuprot glavne tablice ostaje prazan.

Upoređivanje tabela na četiri ili više kolona

Ako vam nedostaje funkcionalnost programa i trebate preslikati tablice u četiri ili više stupaca, tada se možete izvući iz situacije na sljedeći način: