Dobar dan
Ovaj članak je posvećen rješavanju pitanja kako usporediti dvije tablice u Excelu ili barem dvije kolone. Da, rad sa tabelama je zgodan i dobar, ali kada ih trebate uporediti, prilično je teško to učiniti vizualno. Možda možete vizuelno sortirati tabelu od desetak ili dve, ali kada pređu hiljade, onda će vam trebati dodatni alati za analizu.
Jao, ne postoji čarobni štapić kojim će se sve obaviti jednim klikom i provjeriti informacije potrebno je pripremiti podatke, napisati formule i druge procedure koje vam omogućavaju da uporedite svoje tabele.
Pogledajmo nekoliko opcija i mogućnosti za poređenje tabela u Excel-u:
Najlakši način
Ovo je najjednostavniji i najosnovniji način za poređenje dvije tabele. Na ovaj način moguće je uporediti i numeričke i tekstualne vrijednosti. Na primjer, uporedimo dva raspona brojčanih vrijednosti, samo tako što ćemo u sljedeću ćeliju napisati formulu za njihovu jednakost =C2=E2, kao rezultat, ako su ćelije jednake, dobijamo odgovor "ISTINITO“, a ako ne bude utakmica, biće ih "LAŽ". Sada, jednostavnim automatskim kopiranjem, kopiramo ga u našu formulu koja nam omogućava da uporedimo dvije kolone u Excelu i vidimo razliku.
Brzo istaknite vrijednosti koje su različite
Ovo takođe nije glomazna metoda. Ako samo trebate pronaći i provjeriti prisustvo ili odsustvo razlika između tabela, potrebno je da odete na karticu "Početna", odaberite dugme menija "Pronađi i odaberi", nakon što ste prethodno odabrali opseg u kojem želite da uporedite dve tabele u Excelu. U meniju koji se otvori izaberite “Odaberi grupu ćelija...” i u dijaloškom okviru koji se pojavi odaberite "razlike po liniji".
Uporedite dve tabele u Excel-u koristeći uslovno formatiranje
Vrlo dobar način na koji možete vidjeti vrijednosti istaknute bojom koje se razlikuju kada uporedite dvije tabele. Možete se prijaviti na kartici "Dom" pritiskom na dugme "Uslovno formatiranje" i izaberite sa ponuđene liste "Upravljanje pravilima".
U dijaloškom okviru "Upravitelj pravila uvjetnog formatiranja", pritisnite dugme "Kreiraj pravilo" i u novom dijaloškom okviru "Kreiraj pravilo formatiranja", odaberite pravilo. Na terenu "Promijeni opis pravila" unesite formulu =$C2<>$E2 da odredite ćeliju koju treba formatirati i pritisnite dugme "Format".
Određujemo stil kako će naša vrijednost koja ispunjava kriterij biti formatirana. 
Sada se naše novo kreirano pravilo pojavilo na listi pravila, vi ga odaberete, kliknete "UREDU".
I cijelo pravilo je primijenjeno na naš raspon, gdje pokušavamo provjeriti sličnost dvije tabele, a razlike su postale vidljive, na koje je primijenjeno uvjetno formatiranje.
Kako uporediti dvije tabele u Excelu koristeći funkciju COUNTIF i pravila
Sve gore navedene metode su dobre za uređene tabele, ali kada podaci nisu uređeni, potrebne su druge metode, od kojih ćemo sada razmotriti jednu. Zamislimo, na primjer, imamo 2 tablice čije se vrijednosti malo razlikuju i trebamo uporediti ove tablice da bismo odredili vrijednost koja se razlikuje. Odaberite vrijednost u rasponu prve tablice i na kartici "Dom", stavka menija "Uslovno formatiranje" i kliknite na stavku na listi "Kreiraj pravilo...", odaberite pravilo "Koristite formulu da odredite koje ćelije formatirati", unesite formulu = ($C$1:$C$7;C1)=0 i izaberite format uslovnog formatiranja. 
Formula provjerava vrijednost iz određene ćelije C1 i upoređuje je sa navedenim rasponom $C$1:$C$7 iz druge kolone. Kopiramo pravilo u cijeli raspon u kojem upoređujemo tablice i dobivamo vrijednosti istaknute u ćelijama koje se ne ponavljaju.
Kako uporediti dvije tabele u Excelu koristeći funkciju VLOOKUP
U ovoj opciji ćemo koristiti , što će nam omogućiti uporedi dve tabele za slučajnosti. Da biste uporedili dve trake, unesite formulu =VLOOKUP(C2,$D$2:$D$7,1,0) i kopirajte je na ceo opseg koji se poredi. Ova formula sekvencijalno počinje provjeravati ima li ponavljanja vrijednosti iz stupca A u koloni B, i shodno tome vraća vrijednost elementa, ako je tamo pronađen, ako vrijednost nije pronađena, dobijamo .
Kako uporediti dvije tabele u Excelu IF funkcije
Ova opcija uključuje korištenje logičke, a razlika između ove metode je u tome što se za poređenje dvije kolone neće koristiti cijeli stupac, već samo onaj njegov dio koji je potreban za poređenje.
Na primjer, uporedimo dva stupca A i B na radnom listu u susjedni stupac C upisujemo formulu: =IF( (MACH(C2,$E$2:$E$7,0));"";C2) i kopirajte ga na cijeli . Ova formula vam omogućava da vidite sekvencijalno da li se određeni elementi iz navedene kolone A nalaze u koloni B i vraća vrijednost ako je pronađena u koloni B.
Uporedite dve tabele koristeći VBA makro
Postoji mnogo načina da provjerite sličnost dvije tabele , ali neke opcije su moguće samo pomoću VBA makroa. Makroi za poređenje dve tabele , objedinjuje ovaj proces i značajno smanjuje vrijeme utrošeno na pripremu podataka. Na osnovu problema koji rješavate i vašeg znanja o VBA makroima, možete kreirati bilo koju varijantu makroa. U nastavku sam dao metodu naznačenu na zvaničnoj Microsoft stranici. Morate kreirati VBA kodni modul i unijeti kod:
Sub Find_Matches() Dim CompareRange kao varijanta, x kao varijanta, y kao varijanta " Postavite varijablu CompareRange jednaku rasponu koji se poredi Postavite CompareRange = Range("B1:B11") " Ako je raspon koji se upoređuje na drugom listu ili radna sveska, " koristite sljedeću sintaksu " Postavite CompareRange = Workbooks("Book2"). _ " Worksheets("Sheet2").Range("B1:B11") " " Uporedite svaki element u odabranom rasponu sa svakim elementom " varijable CompareRange Za svaki x U izboru Za svaki y U CompareRange Ako je x = y Tada je x .Offset( 0, 2) = x Sljedeći y Sljedeći x Kraj sub
Sub Find_Matches() Dim CompareRange kao varijanta , x kao varijanta , y kao varijanta " Postavljanje varijable CompareRange na raspon koji se poredi Postavite CompareRange = Raspon("B1:B11") " Ako je raspon koji se upoređuje na drugom listu ili radnoj knjizi, " koristite sljedeću sintaksu " Postavite CompareRange = Radne knjige ("Knjiga2"). _ Prečica prečice Alt+F8. U novom dijaloškom okviru odaberite svoj makro Find_similar i uradi to. Poređenjepomoću dodatka za upit
Ova opcija usporedbe postala je dostupna s izdavanjem verzije Excel-a iz 2013. godine, dodan je uređivač Pitajte dodatak, koji će vam omogućiti da analizirate i uporedite dvije Excel datoteke. Ova metoda je dobra kada treba da uporedite dva fajla, u slučaju da je vaš kolega radio na knjizi i napravio neke izmene. Za utvrđivanje ovih promjena potreban vam je alat WorkbookCompare u dodatku za upit. Pa, pogledali smo 8 načina za upoređivanje dvije tablice u Excelu, ove opcije će vam pomoći da riješite svoje analitičke probleme i pojednostavite svoj rad. Bilo mi je drago da vam pomognem!
|
Ponekad postoji potreba da se uporede dva MS Excel fajla. To može biti pronalaženje odstupanja u cijenama za određene artikle ili promjena indikacija, nije bitno, najvažnije je da je potrebno pronaći određena odstupanja.
Ne bi bilo loše spomenuti da ako postoji nekoliko zapisa u MS Excel datoteci, onda nema smisla pribjeći automatizaciji. Ako datoteka sadrži nekoliko stotina, pa čak i hiljada zapisa, onda je nemoguće bez pomoći računarske snage računara.
Hajde da simuliramo situaciju u kojoj dva fajla imaju isti broj redova, a neslaganje se mora tražiti u određenoj koloni ili u nekoliko kolona. Ovakva situacija je moguća, na primjer, ako trebate uporediti cijenu robe po dva cjenovnika, ili uporediti mjere sportista prije i nakon sezone treninga, iako ih za takvu automatizaciju mora biti dosta.
Kao radni primjer, uzmimo fajl sa performansama fiktivnih učesnika: trčanje na 100 metara, trčanje na 3000 metara i zgibovi. Prvi fajl je mjerenje na početku sezone, a drugi kraj sezone.
Prvi način rješavanja problema. Rješenje je samo korištenje MS Excel formula.
Pošto 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,list2!$B$2:$F$13,3,TRUE)<>D2;D2-VLOOKUP($B2;Sheet2!$B$2:$F$13,3,TRUE);"Nema razlike")
Ako nema razlike, prikazuje se poruka da nema razlike, tada se vrijednost na kraju sezone oduzima od vrijednosti na početku sezone.
Formula za trčanje na 3000 metara je sljedeća:
=IF(VLOOKUP($B2,list2!$B$2:$F$13,4,TRUE)<>E2;"Postoji razlika";"Nema razlike")
Ako konačne i početne vrijednosti nisu jednake, prikazuje se odgovarajuća poruka. Formula za zgibove može biti slična bilo kojoj od prethodnih; Konačni fajl sa pronađenim odstupanjima prikazan je ispod.
Malo pojašnjenje. Da bi se formule lakše čitale, podaci iz dva fajla su premešteni u jedan (na različitim listovima), ali to se nije moglo učiniti.
Video koji upoređuje dvije MS Excel datoteke pomoću i funkcija.
Drugi način rješavanja problema. Rješenje koristeći MS Access.
Ovaj problem se može riješiti ako prvo uvezete MS Excel datoteke u Access. Što se tiče samog načina uvoza eksternih podataka, nema razlike u pronalaženju različitih polja (bilo koja od predstavljenih opcija će poslužiti).
Potonji je veza između Excel i Access datoteka, tako da kada promijenite podatke u Excel datotekama, neslaganja će se automatski pronaći kada pokrenete upit u MS Accessu.
Sljedeći korak nakon uvoza je kreiranje odnosa između tabela. Kao polje za povezivanje odaberite jedinstveno polje „Br.
Treći korak je kreiranje jednostavnog upita odabira koristeći Query Builder.
U prvoj koloni označavamo koji zapisi treba da budu prikazani, au drugoj - pod kojim uslovima će zapisi biti prikazani. Naravno, za drugo i treće polje akcije će biti slične.
Video koji upoređuje MS datoteke sa Excelom koristeći MS Access.
Kao rezultat izvršenih manipulacija, prikazuju se svi zapisi, sa različitim podacima u polju: „Trčanje 100 metara“. MS Access fajl je predstavljen u nastavku (nažalost, SkyDrive ne dozvoljava ugrađivanje kao Excel fajl)
Ove dvije metode postoje za pronalaženje neslaganja u MS Excel tabelama. Svaki od njih ima i prednosti i nedostatke. Očigledno, ovo nije potpuna lista poređenja između dvije Excel datoteke. Čekamo vaše prijedloge u komentarima.
Dodatak Inquire za Excel 2013 vam omogućava da uporedite i analizirate Excel dokumente kako biste potražili odnose, pogrešne formule i razlike između .xlsx datoteka. Pogledajmo trenutke kada bi vam ovaj dodatak mogao biti koristan i kako ga koristiti.
Pokretanje dodatka za upit
Dodatak Inquire za Excel standardno dolazi uz Excel 2013 i ne zahtijeva dodatne instalacione pakete. Dovoljno je to omogućiti u dodacima. Ranije verzije programa Excel ne podržavaju ovaj dodatak. Osim toga, u vrijeme pisanja, dodatak je bio dostupan samo na engleskom jeziku.
Da pokrenete Upit, idite na karticu File –> Opcije. U dijaloškom okviru koji se pojavi odaberite karticu dodaci, u padajućem meniju Kontrola izaberite DodaciCOM i kliknite na dugme Idi. Pojavit će se prozor Dodaci za komponentni objektni model (COM), gde ćete morati da potvrdite izbor u polju Raspitati se i pritisnite dugme UREDU.
Nakon pokretanja dodatka, nova kartica će se pojaviti na traci Raspitati se.
Hajde da vidimo koje nam prednosti ovaj dodatak daje.
Analiza radne sveske
Analiza radne sveske koristi se za identifikaciju strukture radne sveske, formula, grešaka, skrivenih listova itd. Da biste koristili ovaj alat, idite u grupu Izvještaj i kliknite na dugme Analiza radne sveske. Rezultat dodatka je prikazan u nastavku.
Sigurno su mnogi ljudi obratili pažnju na stvar Veomaskrivenolistovi(Veoma skriveni listovi). Ovo nije šala, u Excelu zapravo možete sakriti list "dobro" koristeći VisualBasic editor. O tome ćemo više govoriti u našim narednim člancima.
Povezivanje sa radnim listovima
U grupi dijagram, Postoje tri alata za definiranje veza između radnih knjiga, listova i ćelija. Oni vam omogućavaju da naznačite odnose između Excel elemenata. Ova funkcija može biti korisna kada imate veliki broj ćelija sa vezama do drugih knjiga. Pokušaj da se razriješi ova zavrzlama može potrajati dosta vremena, dok dodatak Inquire omogućava vizualizaciju ovisnosti podataka.
Za izgradnju dijagrama zavisnosti u grupi Dijagram odaberite jednu od stavki WorkbookRelationship, Radni listVeza ili CellVeza. Izbor će zavisiti od vrste zavisnosti koju želite da vidite: između radnih knjiga, listova ili ćelija.
Na slici ispod vidjet ćete dijagram odnosa radne knjige koji je Excel napravio kada sam kliknuo na dugme Radna sveskaVeza.
Poređenje dva fajla
Sljedeći programski dodatak Inquire za Excel je Uporedite– omogućava vam da uporedite dva fajla ćeliju po ćeliju i ukažete na sve razlike između njih. Ovaj alat može biti potreban kada imate nekoliko revizija iste datoteke i trebate razumjeti koje su promjene napravljene u najnovijim verzijama.
Za korištenje ovog alata trebat će vam dvije datoteke. U grupi Uporedite izabrati UporediteFajlovi. U dijaloškom okviru koji se pojavi moramo odabrati datoteke koje želimo uporediti i kliknuti na dugme Uporedite.
U našem slučaju, to su dva identična fajla, u jednom od kojih sam namjerno napravio neke izmjene.
Nakon malo razmišljanja, Excel će proizvesti rezultat poređenja, gdje će razlike između dvije tabele biti označene bojom. U ovom slučaju, boja ćelije će se razlikovati ovisno o vrsti razlike ćelija (razlike se mogu generirati zbog vrijednosti, formula, proračuna itd.).
Čišćenje nepotrebnog formatiranja
Ovaj alat vam omogućava da očistite nepotrebno oblikovanje iz ćelija u radnoj knjizi, kao što su ćelije koje su formatirane, ali ne sadrže vrijednosti. Alat Očistite višak formatiranja ćelije pomoći će “amaterima” da popune cijeli red radne sveske bojom, umjesto da popunjavaju određene redove tabele.
Da biste koristili alat, idite na karticu Raspitati se grupi Razno i odaberite Očistite višak formatiranja ćelije. U prozoru koji se pojavi morate odabrati područje za brisanje viška formatiranja - cijelu radnu knjigu ili aktivni list - kliknite UREDU.
Brisanje nepotrebnog formatiranja će smanjiti veličinu datoteke i povećati produktivnost.
Lozinke radne knjige
Ako namjeravate analizirati radne knjige zaštićene lozinkom, morat ćete ih navesti u Lozinke radne knjige.
Zaključak
Dodatak Inquire za Excel sadrži nekoliko zanimljivih alata koji će vam pomoći da poboljšate točnost i integritet vaših radnih knjiga. Ako koristite Excel 2013, ima smisla obratiti pažnju na ovaj dodatak.
Recimo da želite da uporedite verzije radne sveske, da analizirate radnu svesku na probleme ili nedoslednosti ili da vidite 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 Upit za proračunsku tabelu dostupan je u Excel-u.
Možete koristiti naredbe na kartici Upit za obavljanje svih ovih zadataka i više. Kartica Upit na Excel traci ima dugmad za naredbe opisane u nastavku.
Ako ne vidite Raspitati se karticu na traci programa Excel, pogledajte Uključivanje dodatka za upit u proračunskoj tablici.
Uporedite dve radne sveske
The Uporedite datoteke komanda vam omogućava da vidite razlike, ćeliju po ćeliju, između dvije radne knjige. Morate imati dvije otvorene radne knjige u programu Excel da biste pokrenuli ovu naredbu.
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 promjene VBA koda red po red. Razlike između ćelija su prikazane u lako čitljivom rasporedu mreže, poput ovoga:
The Uporedite datoteke naredba koristi Microsoft Spreadsheet Compare da uporedi dvije datoteke. U operativnom sistemu Windows 8, možete da pokrenete poredjenje proračunskih tabela izvan Excela tako što ćete kliknuti Uporedi tabelu na aplikacije ekran. U operativnom sistemu Windows 7 kliknite na Windows Počni dugme, a zatim > Svi programi > Microsoft Office 2013 > Office 2013 alati > Uporedi tabelu za 2013.
Da biste saznali više o usporedbi proračunskih tablica i upoređivanju datoteka, pročitajte Uporedi dvije verzije radne knjige.
Analizirajte radnu svesku
The Analiza radne sveske naredba kreira 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 veze podataka sa Access bazom podataka i tekstualnom datotekom.
Prikaži veze radne sveske
Radne sveske povezane sa drugim radnim sveskama putem referenci na ćelije mogu postati zbunjujuće. Koristite za kreiranje interaktivne, grafičke mape zavisnosti radne sveske kreirane vezama (vezama) između datoteka. Tipovi veza u dijagramu mogu uključivati druge radne knjige, Access 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, te povući linije veze da promijenite oblik dijagrama.
Ovaj dijagram prikazuje trenutnu radnu knjigu na lijevoj strani 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 radnog lista
Imate mnogo radnih listova koji zavise jedan od drugog? Koristite da biste kreirali interaktivnu, grafičku mapu veza (veza) između radnih listova iu istoj radnoj svesci iu 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 knjige, 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 na dijagramu, kao što je radni list pod nazivom "Zapad" na dijagramu, pojavljuje se balon koji sadrži informacije.
Prikaži ćelijske odnose
Da biste dobili detaljan, interaktivni dijagram svih veza od odabrane ćelije do ćelija u drugim radnim listovima ili čak drugim radnim knjigama, koristite Cell Relationship alat. Ovi odnosi sa drugim ćelijama mogu postojati u formulama ili referencama na imenovane opsege. Dijagram može ukrštati radne listove i radne sveske.
Ovaj dijagram prikazuje dva nivoa odnosa ćelija za ćeliju A10 na Sheet5 u Book1.xlsx. Ova ćelija zavisi od ćelije C6 na listu 1 u drugoj radnoj svesci, Book2.xlsx. Ova ćelija je presedan za nekoliko ćelija na drugim radnim listovima u istoj datoteci.
Da biste saznali više o gledanju odnosa ćelija, pročitajte Vidite veze između ćelija.
Očistite višak formatiranja ćelije
Jeste li ikada otvorili radnu svesku i otkrili da se sporo učitava ili je postala ogromna? Možda je formatiranje primijenjeno na redove ili kolone kojih niste svjesni Očistite višak formatiranja ćelije naredba za uklanjanje viška formatiranja i značajno smanjenje veličine datoteke. Ovo vam pomaže da izbjegnete "naduvavanje proračunskih tablica", što poboljšava brzinu Excela.
Upravljajte lozinkama
Ako koristite funkcije upita za analizu ili upoređivanje radnih knjiga koje su zaštićene lozinkom, morat ćete dodati lozinku radne knjige na listu lozinki kako bi Inquire mogao otvoriti sačuvanu kopiju vaše radne knjige. Koristiti Lozinke radne knjige komanda na Raspitati se karticu za dodavanje lozinki, koje će biti sačuvane na vašem računaru. Ove lozinke su šifrirane i dostupne samo vama.
Compare Spreadsheets for Excel je moćan i praktičan alat za poređenje datoteka Microsoft Excel-a.
Zašto je ovo potrebno i kako funkcioniše:
Na primjer, od svojih partnera dobijate mjesečne cjenovnike sljedećeg tipa:
septembra
oktobar
Kratkim pogledom na njih čini se da se cjenovnici nisu promijenili. Naravno, možete potrošiti duže vrijeme na detaljnu usporedbu svih artikala i cijena proizvoda kako biste shvatili ima li promjena ili ne. Međutim, pokušajmo delegirati ovaj zadatak Uporedite tabele za Excel::
- Pokrenite program (nije potrebno otvarati tabele koje se porede u Microsoft Excel-u).
- Odredite tabele ili opsege ćelija za poređenje;
- Odaberite opcije poravnanja za poređenje (po redovima, kolonama ili bez poravnanja);
- Odredite šta želite da uporedite: vrednosti ćelija ili formule;
- Postavite opcije za isticanje različitih ćelija (boja pozadine i/ili boja i stil ivice ćelije).
Pet jednostavnih koraka, nekoliko sekundi vremena i imate sljedeći izvještaj:
Program je pronašao sve promjene u posljednjem cjenovniku: novi proizvod (označen svijetlo zelenom bojom) i promijenjene cijene (ćelije sa crvenim rubom).
Sada zamislite da vaše datoteke nemaju 20 redova i 8 kolona. Ispravno upoređivanje dva velika dokumenta ručno je vrlo težak zadatak. Ali sve poteškoće nestaju ako uporedite Microsoft Excel tabele koristeći Compare Spreadsheets for Excel!
Karakteristike programa
- Radite sa datotekama, tabelama ili odabranim rasponom ćelija.
- Rad sa datotekama bez otvaranja u programu Microsoft Excel.
- Jednostavan i zgodan interfejs programa u obliku čarobnjaka.
- Prikažite rezultate poređenja u obliku prilagodljivog, praktičnog i vizuelnog izveštaja.
- Sposobnost poređenja proizvoljnih ćelija u rezultirajućem izvještaju.
- Poređenje po vrijednostima ćelija ili formulama.
Preuzmite demo verziju
Možete preuzeti demo verziju Compare Spreadsheets for Excel (17906 KB) da biste je testirali prije kupovine:
Registracija programa
Demo verzija Compare Spreadsheets za Excel nema ograničenja. Ako želite nakon isteka 20-dnevnog demo perioda, onda morate registrirati program. Možete registrirati Compare Spreadsheets za Excel na mreži odabirom načina plaćanja koji vam odgovara.