Recimo da želite da uporedite verzije radne sveske, analizirate radnu svesku za probleme ili nedoslednosti ili 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 programu Excel.
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 pokrenuti uporedi proračunske tablice izvan Excela tako što ćete kliknuti Uporedi proračunske tablice na aplikacije ekran. U operativnom sistemu Windows 7 kliknite na Windows start dugme, a zatim > Svi programi > Microsoft Office 2013 > Office 2013 alati > Uporedi tabelu za 2013.
Da biste saznali više o upoređivanju 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 Pogledajte 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 Excel-a.
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.
Trebate uporediti dva Microsoft Excel fajla? Evo dva laka načina da to učinite.
Postoji mnogo razloga zašto biste mogli uzeti jedan Excel dokument i uporediti ga s drugim. Ovo može biti radno intenzivan zadatak.
zahtijeva puno koncentracije, ali postoje načini da si olakšate život.
Da li treba da pažljivo pogledate rukom ili želite da Excel uradi nešto teško
u vaše ime, evo dva laka načina da uporedite više listova.
Kako uporediti Excel datoteke
Excel omogućava korisnicima da prikažu dvije verzije dokumenta odjednom kako bi brzo razlikovali između njih:
- Prvo otvorite radne sveske za upoređivanje.
- Prebaciti na Pogled > Prozor > Pogled sa strane.
Poređenje Excel datoteka na oko
Da biste započeli, otvorite Excel i sve radne knjige koje želite da uporedite. Možemo koristiti istu tehniku za upoređivanje listova u istom dokumentu
ili potpuno različite datoteke.
Ako se dobije više od jednog lista iz iste knjige, mora se prethodno odvojiti. Da biste to učinili, idite na Pogled > Prozor > Novi prozor.
Ovo neće trajno podijeliti pojedinačne listove, samo otvorite novu instancu vašeg dokumenta.
Ovaj meni će prikazati sve tabele koje su trenutno otvorene. Ako imate samo dva otvorena, oni će biti automatski odabrani.
Odaberite i kliknite Dobro. Videćete da se obe tabele pojavljuju na ekranu.
Ako je zgodnije možete koristiti Rasporedite sve dugme za prebacivanje između vertikalne i horizontalne konfiguracije.
Jedna važna opcija koje treba biti svjestan je Sinhrono skrolovanje prebacivanje.
Omogućavanje ove postavke osigurava da kada se jedan prozor pomiče, drugi će se kretati sinhronizirano. Ovo je važno ako radite sa velikim stolom.
i želite da nastavite testirati jedno protiv drugog. Ako iz nekog razloga dva lista nisu poravnata, samo kliknite Poništi poziciju prozora.
Poređenje Excel datoteka pomoću uvjetnog oblikovanja
U mnogim slučajevima, najbolji način za poređenje dvije proračunske tablice može biti da ih jednostavno prikažete u isto vrijeme. Međutim, moguće je donekle automatizirati proces.
Korištenje uvjetnog formatiranja
Možemo provjeriti u Excelu ima li neslaganja između dva lista. Ovo može uštedjeti mnogo vremena ako sve što trebate pronaći su razlike između jedne i druge verzije.
Za ovu metodu, moramo biti sigurni da su dva lista s kojima radimo dio iste radne knjige. Da biste to učinili, desnom tipkom miša kliknite naziv lista koji želite prenijeti i odaberite Premjestite ili kopirajte.
Ovdje možete koristiti padajući meni da odlučite u koji dokument će biti zalijepljen.
Odaberite sve ćelije koje su popunjene na radnom listu gdje želite da se istaknu sve razlike. Brz način da to učinite je da kliknete na ćeliju u gornjem lijevom kutu, a zatim koristite prečicu
Ctrl + Shift + End.
Prebaciti na Početna> Stilovi> Uvjetno oblikovanje> Novo pravilo.
Odaberite Koristite formulu da odredite koje ćelije treba formatirati i unesite sljedeće:
A1sheet_name!A1
Samo ne zaboravite postaviti "sheet_name" za bilo koji drugi naziv lista. Ova formula provjerava samo kada ćelija na jednom listu ne odgovara tačno odgovarajućoj ćeliji na drugom listu i označava svaku instancu.
Iznad možete vidjeti rezultate. Sve ćelije koje sadrže promjene su označene crvenom bojom, što čini brzim i lakim upoređivanje dva lista.
Neka Excel obavi težak posao
Gornja tehnika demonstrira jedan način na koji možete dopustiti Excelu da se nosi sa nekim teškim dizanjem. Čak i ako obratite veliku pažnju, postoji šansa da ćete propustiti promjenu ako ovaj zadatak obavite ručno. Zahvaljujući uslovnom formatiranju, možete biti sigurni da ništa ne curi na mrežu.
Excel je dobar u monotonom i detaljno orijentisanom radu. Kada se snađete, možete uštedjeti malo vremena i truda korištenjem tehnika kao što su uvjetno formatiranje i malo domišljatosti.
Imate li savjet za upoređivanje dokumenata u Excelu? Ili vam je potrebna pomoć s procesima u ovom vodiču? U svakom slučaju, zašto se ne biste pridružili razgovoru u odeljku za komentare ispod?
Pitanje korisnika
Zdravo!
Imam jedan problem, a već treći dan mucam mozak - ne znam kako da ga završim. Postoje 2 tabele (oko 500-600 redova u svakoj), potrebno je iz jedne tabele uzeti kolonu sa nazivom proizvoda i uporediti je sa nazivom proizvoda iz druge, a ako se proizvodi poklapaju kopirati i zamijenite vrijednost iz tabele 2 u tabelu 1. Zamršeno objašnjeno, ali mislim da ćete razumjeti zadatak sa fotografije ( cca. : fotografiju su izrezali cenzori, uostalom lični podaci).
Hvala unapred. Andrej, Moskva.
Dobar dan svima!
Ono što ste opisali je prilično popularan zadatak koji se relativno lako i brzo rješava korištenjem Excela. Dovoljno je ubaciti svoje dvije tabele u program i koristiti funkciju VLOOKUP. Više o njenom radu u nastavku...
Primjer rada sa funkcijom VLOOKUP
Kao primjer, uzeo sam dvije male ploče, prikazane su na slici ispod. U prvoj tabeli (kolone A, B- proizvod i cijena) nema podataka za kolonu B; u drugom - popunjene su obje kolone (proizvod i cijena). Sada morate provjeriti prve stupce u obje tabele i automatski, ako se pronađe podudaranje, kopirati cijenu na prvu ploču. Izgleda kao jednostavan problem...
Kako uraditi...
Postavite pokazivač miša u ćeliju B2- odnosno u prvu ćeliju kolone, gdje nemamo vrijednost i napišemo formulu:
=VLOOKUP(A2 ;$E$1:$F$7 ;2 ;FALSE )
A2- vrijednost iz prve kolone prve tabele (ono što ćemo tražiti u prvoj koloni druge tabele);
$E$1:$F$7- potpuno odabrana druga tabela (u kojoj želimo nešto pronaći i kopirati). Obratite pažnju na znak "$" - potrebno je da se prilikom kopiranja formule ćelije odabrane druge tablice ne mijenjaju;
2 - broj kolone iz koje ćemo kopirati vrijednost (imajte na umu da naša odabrana druga tabela ima samo 2 kolone. Ako bi imala 3 kolone, tada bi vrijednost mogla biti kopirana iz 2. ili 3. kolone);
LAŽEŠ- tražimo tačnu utakmicu (inače će prva slična biti zamijenjena, što nam očito ne odgovara).
Zapravo, gotovu formulu možete prilagoditi svojim potrebama tako što ćete je lagano promijeniti. Rezultat formule je prikazan na slici ispod: cijena je pronađena u drugoj tabeli i zamijenjena u automatskom načinu rada. Sve radi!
Da biste postavili cijenu za druge artikle - samo rastegnite (kopirajte) formulu u druge ćelije. Primjer je ispod.
Nakon toga će se, kao što vidite, uporediti prve kolone tabela: iz redova u kojima su se podudarale vrijednosti ćelija, kopirat će se i zamijeniti potrebni podaci. Generalno, jasno je da tabele mogu biti mnogo veće!
Bilješka: Moram reći da je funkcija VLOOKUP prilično zahtjevna za računarske resurse. U nekim slučajevima, s pretjerano velikim dokumentom, može potrajati dosta vremena za upoređivanje tabela. U tim slučajevima vrijedi razmotriti ili druge formule ili potpuno drugačija rješenja (svaki slučaj je individualan).
To je sve, sretno!
Nakon instaliranja dodatka, imat ćete novu karticu sa komandom za poziv funkcije. Kada kliknete na komandu Poređenje dometa pojavljuje se dijaloški okvir za unos parametara.
Ovaj makro vam omogućava da uporedite tabele bilo koje veličine i sa bilo kojim brojem kolona. Tabele se mogu porediti na jednoj, dvije ili tri kolone istovremeno.
Okvir za dijalog je podijeljen na dva dijela: lijevo za prvu tablicu i desno za drugu.
Da uporedite tabele, uradite sledeće:
- Odredite opsege tabele.
- Postavite potvrdni okvir (kvačica/kvačica) ispod odabranog raspona tabela ako tabela sadrži zaglavlje (red zaglavlja).
- Odaberite kolone lijeve i desne tablice koje želite uporediti (ako rasponi tablica ne sadrže zaglavlja, kolone će biti numerisane).
- Odredite vrstu poređenja.
- Odaberite izlaznu opciju.
Tip poređenja tabele
Program vam omogućava da odaberete nekoliko vrsta poređenja tabela:
Pronađite redove u jednoj tabeli koji nedostaju u drugoj tabeli
Kada je odabrana ova vrsta poređenja, program traži redove u jednoj tabeli koji nedostaju u drugoj. Ako uporedite tabele po nekoliko kolona, onda će rezultat rada biti redovi u kojima postoji razlika u barem jednoj od kolona.
Pronađite odgovarajuće linije
Kada je odabrana ova vrsta poređenja, program pronalazi redove koji se podudaraju u prvoj i drugoj tablici. Podudarni redovi su oni u kojima se vrijednosti u odabranim kolonama za poređenje (1, 2, 3) jedne tabele potpuno poklapaju sa vrijednostima kolona druge tabele.
Primer programa u ovom režimu prikazan je desno na slici.
Uparite tabele na osnovu odabranog
U ovom režimu poređenja, ispred svakog reda prve tabele (odabrane kao glavne) kopiraju se podaci odgovarajućeg reda druge tabele. Ako nema odgovarajućih redova, red nasuprot glavne tabele ostaje prazan.
Poređenje tabela sa četiri ili više kolona
Ako vam nedostaje funkcionalnost programa i trebate uskladiti tabele na četiri ili više kolona, to možete zaobići na sljedeći način:
- Napravite praznu kolonu u tabelama.
- U novim stupcima koristeći formulu = SPOJKA spojite kolone sa kojima želite da uporedite.
Tako ćete završiti sa 1 kolonom koja sadrži vrijednosti više kolona. Pa, već znate kako uskladiti jednu kolonu.
Možda se svi koji rade s podacima u Excelu susreću s pitanjem kako uporediti dvije kolone u Excelu za podudarnosti i razlike. Postoji nekoliko načina da to učinite. Pogledajmo pobliže svaki od njih.
Kako uporediti dvije kolone u Excelu red po red
Prilikom poređenja dvije kolone podataka, često je potrebno usporediti podatke u svakom pojedinačnom redu radi podudarnosti ili razlika. Takvu analizu možemo napraviti pomoću funkcije . Pogledajmo kako to funkcionira u primjerima u nastavku.
Primjer 1: Kako uporediti dvije kolone za podudaranja i razlike u istom redu
Kako bismo uporedili podatke u svakom redu od dvije kolone u Excelu, pišemo jednostavnu formulu. Formulu treba umetnuti u svaki red u susjednoj koloni, pored tabele u kojoj se nalaze glavni podaci. Nakon što smo kreirali formulu za prvi red tabele, možemo je rastegnuti / kopirati u ostale redove.
Da bismo provjerili da li dvije kolone jednog reda sadrže iste podatke, potrebna nam je formula:
=IF(A2=B2, “Podudaranje”, “”)
Formula koja određuje razlike između podataka dva stupca u jednom redu će izgledati ovako:
=IF(A2<>B2; "Ne podudaraju"; “”)
Provjeru podudaranja i razlika između dvije kolone možemo uklopiti u jedan red u jednu formulu:
=IF(A2=B2, “podudaranje”, “ne podudaranje”)
=IF(A2<>B2; "Ne podudaraju"; "Uparite se")
Primjer rezultata izračuna može izgledati ovako:
Za upoređivanje podataka u dvije kolone istog reda, osjetljivih na velika i mala slova, koristite formulu:
=IF(TAČNO(A2,B2), “Podudaranje”, “Jedinstveno”)
Kako usporediti više stupaca za podudaranja u jednom redu u Excelu
Excel ima mogućnost da uporedi podatke u više kolona istog reda koristeći sljedeće kriterije:
- Pronađite redove sa istim vrijednostima u svim stupcima tabele;
- Pronađite redove sa istim vrijednostima u bilo koje dvije kolone tabele;
Primjer1. Kako pronaći podudaranja u jednom redu u više kolona tabele
Zamislite da se naša tabela sastoji od nekoliko kolona sa podacima. Naš zadatak je pronaći redove u kojima se vrijednosti podudaraju u svim stupcima. Funkcije programa Excel i . Formula za određivanje podudaranja bi bila:
=IF(AND(A2=B2,A2=C2), “podudaranje”, “ “)
Ako naša tablica ima puno stupaca, bit će lakše koristiti funkciju u kombinaciji sa:
=IF(COUNTIF($A2:$C2,$A2)=3;”podudaranje”;” “)
U formuli, “5” označava broj kolona u tabeli za koju smo kreirali formulu. Ako vaša tabela ima više ili manje kolona, tada bi ova vrijednost trebala biti jednaka broju kolona.
Primjer 2: Kako pronaći podudaranja u jednom redu u bilo koje dvije kolone tabele
Zamislite da je naš zadatak da iz tabele sa podacima u nekoliko kolona identifikujemo one redove u kojima se podaci podudaraju ili se ponavljaju u najmanje dve kolone. Funkcije i pomoći će nam u tome. Napišimo formulu za tabelu koja se sastoji od tri kolone sa podacima:
=IF(OR(A2=B2;B2=C2;A2=C2);”podudaranje”;” “)
U slučajevima kada u našoj tabeli ima previše kolona, naša formula sa funkcijom će biti veoma velika, jer u njenim parametrima treba da navedemo kriterijume podudaranja između svake kolone tabele. Lakši način, u ovom slučaju, je korištenje .
=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Jedinstveni niz”; “Nije jedinstven niz”)
=IF(COUNTIF($B:$B,$A5)=0, “Nema podudaranja u koloni B”, “Postoje podudaranja u koloni B”)
Ova formula provjerava vrijednosti u koloni B u odnosu na podatke ćelije u koloni A.
Ako vaša tabela ima fiksni broj redova, možete odrediti jasan raspon u formuli (na primjer, $B2:$B10). Ovo će ubrzati formulu.
Kako uporediti dvije kolone u Excelu za podudaranja i istaknuti bojom
Kada tražimo podudaranja između dvije kolone u Excelu, možda ćemo morati vizualizirati pronađena podudaranja ili razlike u podacima, na primjer korištenjem isticanja boja. Najlakši način za isticanje podudaranja i razlika je korištenje uvjetnog oblikovanja u Excelu. Pogledajmo kako to učiniti u primjerima u nastavku.
Pronalaženje i isticanje podudaranja po boji u više kolona u programu Excel
U slučajevima kada trebamo pronaći podudaranja u nekoliko kolona, za to nam je potrebno:
- Odaberite kolone s podacima u kojima želite izračunati podudaranja;
- Na kartici "Početna" na traci sa alatkama kliknite na stavku menija "Uslovno oblikovanje" -> "Pravila odabira ćelija" -> "Duplicate vrijednosti";
- U iskačućem dijaloškom okviru odaberite stavku “Duplicate” na lijevoj padajućoj listi, u desnoj padajućoj listi odaberite kojom će bojom biti istaknute duplirane vrijednosti. Kliknite na dugme "OK":
- Nakon toga, utakmice će biti istaknute u odabranoj koloni:
Pronađite i označite odgovarajuće redove u Excelu
Traženje podudaranja ćelija s podacima u dvije ili više kolona i traženje podudaranja cijelih redova s podacima su različiti koncepti. Obratite pažnju na dvije tabele u nastavku:
Gornje tabele sadrže iste podatke. Njihova razlika je u tome što smo u primjeru na lijevoj strani tražili odgovarajuće ćelije, a na desnoj smo pronašli čitave redove sa podacima koji se ponavljaju.
Razmislite kako pronaći odgovarajuće redove u tabeli:
- Desno od tabele sa podacima kreirajte pomoćnu kolonu u kojoj, nasuprot svakog retka sa podacima, stavljamo formulu koja kombinuje sve vrednosti reda tabele u jednu ćeliju:
= A2 & B2 & C2 & D2
U pomoćnoj koloni vidjet ćete objedinjene podatke tabele:
Sada, da odredite podudarne redove u tabeli, uradite sledeće korake:
- Odaberite područje s podacima u pomoćnoj koloni (u našem primjeru, ovo je raspon ćelija E2: E15 );
- Na kartici "Početna" na traci sa alatkama kliknite na stavku menija "Uslovno oblikovanje" -> "Pravila odabira ćelija" -> "Duplicate vrijednosti";
- U iskačućem dijaloškom okviru odaberite “Duplicate” na lijevoj padajućoj listi, u desnoj padajućoj listi odaberite kojom će bojom biti istaknute duplirane vrijednosti. Kliknite na dugme "OK":
- Nakon toga, dupli redovi će biti istaknuti u odabranoj koloni:
