Recimo da želite usporediti verzije radne knjige, analizirati radnu knjigu na probleme ili nedosljednosti ili vidjeti veze između radnih knjiga ili radnih listova. Ako je na vašem računalu instaliran Microsoft Office 365 ili Office Professional Plus 2013, dodatak Upit za proračunsku tablicu dostupan je u Excelu.
Možete koristiti naredbe na kartici Upit za obavljanje svih ovih zadataka i više. Kartica Upit na vrpci Excel ima gumbe za naredbe opisane u nastavku.
Ako ne vidite Raspitajte se karticu na vrpci programa Excel, pogledajte Uključivanje dodatka za upit proračunske tablice.
Usporedite dvije radne bilježnice
The Usporedite datoteke naredba vam omogućuje da vidite razlike, ćeliju po ćeliju, između dvije radne knjige. Da biste pokrenuli ovu naredbu, trebate imati dvije otvorene 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 promjene VBA koda redak po redak. Razlike između ćelija prikazane su u lako čitljivom rasporedu mreže, poput ovoga:
The Usporedite datoteke naredba koristi Microsoftovu usporedbu proračunskih tablica za usporedbu dviju datoteka. U sustavu Windows 8 možete pokrenuti usporedbu proračunskih tablica izvan Excela klikom Usporedi proračunske tablice na aplikacije zaslon. U sustavu Windows 7 kliknite na Windows Početak gumb i zatim> Svi programi > Microsoft Office 2013 > Alati za Office 2013 > Usporedi proračunsku tablicu 2013.
Da biste saznali više o usporedbi proračunskih tablica i usporedbi datoteka, pročitajte Usporedba dviju verzija radne knjige.
Analizirajte radnu bilježnicu
The Analiza radne bilježnice naredba stvara interaktivno izvješće koje 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.
Prikaži veze radne knjige
Radne knjige povezane s drugim radnim knjigama putem referenci na ćelije mogu postati zbunjujuće. Koristite za stvaranje interaktivne, grafičke karte ovisnosti radne knjige stvorene vezama (vezama) između datoteka. Vrste veza u dijagramu mogu uključivati druge radne knjige, Access baze podataka, tekstualne datoteke, HTML stranice, baze podataka SQL Servera i druge izvore podataka. U dijagramu odnosa možete odabrati elemente i pronaći više informacija o njima te povući linije povezivanja kako 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đer prikazuje dodatne razine povezivanja radne knjige, dajući vam sliku porijekla podataka za radnu knjigu.
Prikaži veze radnog lista
Imate li puno radnih listova koji ovise jedan o drugom? Koristite za stvaranje interaktivne, grafičke karte veza (veza) između radnih listova iu istoj radnoj knjizi iu drugim radnim knjigama. To 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, s ovisnostima između radnih listova u istoj radnoj knjizi, kao i veze između radnih listova u različitim radnim knjigama. 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 odnose između stanica
Da biste dobili detaljan, interaktivni dijagram svih veza od odabrane ćelije do ćelija u drugim radnim listovima ili čak drugim radnim knjigama, koristite Odnos stanica alat. Ti odnosi s drugim ćelijama mogu postojati u formulama ili referencama na imenovane raspone. Dijagram može križati radne listove i radne knjige.
Ovaj dijagram prikazuje dvije razine odnosa ćelija za ćeliju A10 na Sheet5 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 odnosa ćelija, pročitajte Pogledajte veze između ćelija.
Očistite višak formatiranja ćelije
Jeste li ikada otvorili radnu knjigu i otkrili da se sporo učitava ili je postala ogromna? Možda je oblikovanje primijenjeno na retke ili stupce kojih niste svjesni Očistite višak formatiranja ćelije naredba za uklanjanje prekomjernog oblikovanja i uvelike smanjenje veličine datoteke. To vam pomaže izbjeći "napuhavanje proračunske tablice", što poboljšava brzinu Excela.
Upravljajte lozinkama
Ako "koristite značajke upita za analizu ili usporedbu radnih knjiga koje su zaštićene lozinkom, morat ćete dodati lozinku radne knjige na popis zaporki kako bi Inquire mogao otvoriti spremljenu kopiju vaše radne knjige. Koristiti Lozinke radne knjige zapovijed na Raspitajte se karticu za dodavanje lozinki, koje će biti spremljene na vašem računalu. Ove lozinke su šifrirane i dostupne samo vama.
Trebate usporediti dvije datoteke programa Microsoft Excel? Evo dva jednostavna načina da to učinite.
Postoji mnogo razloga zašto biste mogli uzeti jedan Excel dokument i usporediti ga s drugim. Ovo može biti dugotrajan zadatak
potrebno je puno koncentracije, ali postoje načini da si olakšate život.
Trebate li pomno pogledati rukom ili želite da Excel obavi neke teške poslove
u vaše ime, evo dva jednostavna načina za usporedbu više listova.
Kako usporediti Excel datoteke
Excel omogućuje korisnicima da prikažu dvije verzije dokumenta odjednom kako bi ih brzo razlikovali:
- Prvo otvorite radne knjige koje želite usporediti.
- Prebaciti na Pogled> Prozor> Pogled sa strane.
Usporedite Excel datoteke na oko
Za početak otvorite Excel i sve radne knjige koje želite usporediti. Istu tehniku možemo koristiti za usporedbu listova u istom dokumentu
ili potpuno različite datoteke.
Ako se iz iste knjige primi više listova, mora se unaprijed odvojiti. Da biste to učinili, idite na Pogled> Prozor> Novi prozor.
Ovo neće trajno podijeliti pojedinačne listove, samo će otvoriti novu instancu vašeg dokumenta.
Ovaj izbornik prikazuje sve tablice koje su trenutno otvorene. Ako imate samo dva otvorena, oni će biti automatski odabrani.
Odaberite i kliknite Dobro... Vidjet ćete da se obje tablice pojavljuju na ekranu.
Ako je prikladnije, možete koristiti Sve posložiti gumb za prebacivanje između vertikalne i horizontalne konfiguracije.
Jedna važna opcija koju treba biti svjestan je Sinkrono pomicanje prebacivanje.
Omogućavanje ove opcije osigurava da će se drugi sinkronizirati kada se pomiče jedan prozor. Ovo je važno ako radite s velikim stolom.
i želite nastaviti provjeravati jedno s drugim. Ako iz nekog razloga dva lista nisu poravnata, samo pritisnite Poništi položaj prozora.
Usporedite Excel datoteke pomoću uvjetnog oblikovanja
U mnogim slučajevima, najbolji način za usporedbu dviju proračunskih tablica mogao bi biti da ih jednostavno prikažete na zaslonu u isto vrijeme. Međutim, moguće je donekle automatizirati proces.
Korištenje uvjetnog oblikovanja
Možemo provjeriti u Excelu ima li odstupanja između dva lista. To vam može uštedjeti puno 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 izbornik da odlučite u koji će dokument biti umetnut.
Odaberite sve ćelije koje su ispunjene na radnom listu gdje želite da se sve razlike istaknu. Brz način da to učinite je da kliknete na ćeliju u gornjem lijevom kutu, a zatim upotrijebite prečac
Ctrl + Shift + End.
Prebaciti na Početna> Stilovi> Uvjetno oblikovanje> Novo pravilo.
Izaberi Upotrijebite formulu da odredite koje ćelije oblikovati i unesite sljedeće:
A1sheet_name! A1
Samo ne zaboravite staviti "sheet_name" za bilo koji drugi naziv lista. Ova formula provodi provjeru valjanosti samo kada ćelija na jednom listu ne odgovara točno odgovarajućoj ćeliji na drugom listu i označava svaku instancu.
Rezultate možete vidjeti iznad. Sve ćelije koje sadrže promjene označene su crvenom bojom, što omogućuje brzu i laku usporedbu dvaju listova.
Neka Excel obavi težak posao
Gornja tehnika pokazuje jedan od načina na koji možete dopustiti Excelu da radi neke teške poslove. Čak i ako dobro pazite, velika je vjerojatnost da ćete propustiti promjenu ako ovaj zadatak obavite ručno. Uz uvjetno formatiranje, možete biti sigurni da ništa ne klizi u mrežu
Excel je dobar u ponavljajućem i detaljnom radu. Nakon što shvatite njegove mogućnosti, možete uštedjeti malo vremena i truda korištenjem tehnika poput uvjetnog oblikovanja i malo domišljatosti.
Imate li kakav savjet o usporedbi 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 odjeljku za komentare ispod?
Pitanje korisnika
Zdravo!
Imam jedan zadatak, a već treći dan se zbunjujem - ne znam kako da ga završim. Postoje 2 tablice (u svakoj oko 500-600 redaka), potrebno je uzeti stupac s nazivom proizvoda iz jedne tablice i usporediti ga s nazivom proizvoda iz druge, a ako se proizvodi podudaraju, kopirati i zamijeniti vrijednost iz tablice 2 u tablicu 1. Zbunjujuće objašnjeno, ali mislim da ćete razumjeti problem sa fotografije ( cca. : fotografija izrezana cenzurom, još uvijek osobni podaci).
Hvala unaprijed. Andrej, Moskva.
Dobar dan svima!
Ono što ste opisali odnosi se na prilično popularne zadatke koje je relativno jednostavno i brzo riješiti pomoću Excela. Dovoljno je ubaciti svoje dvije tablice u program i koristiti funkciju VLOOKUP. O njenom radu u nastavku...
Primjer rada s funkcijom VLOOKUP
Kao primjer, uzeo sam dvije male ploče, prikazane su na slici ispod. U prvoj tablici (stupci A, B- proizvod i cijena) nema podataka o stupcu B; u drugom se popunjavaju oba stupca (proizvod i cijena). Sada trebate provjeriti prve stupce u obje tablice i automatski, ako se pronađe podudaranje, kopirati cijenu na prvu ploču. Čini se da je zadatak jednostavan...
Kako to učiniti...
Stavili smo pokazivač miša u ćeliju B2- odnosno u prvu ćeliju stupca, gdje nemamo vrijednost i upisujemo formulu:
= VLOOKUP (A2, $ E $ 1: $ F $ 7,2, FALSE)
A2- vrijednost iz prvog stupca prve tablice (ono što ćemo tražiti u prvom stupcu druge tablice);
$ E $ 1: $ F $ 7- potpuno odabrana druga tablica (u kojoj želimo nešto pronaći i kopirati). Obratite pažnju na ikonu "$" - potrebno je da se ćelije odabrane druge tablice ne mijenjaju prilikom kopiranja formule;
2 - broj stupca iz kojeg ćemo kopirati vrijednost (imajte na umu da naša odabrana druga tablica ima samo 2 stupca. Da ima 3 stupca, tada se vrijednost može kopirati iz 2. ili 3. stupca);
LAGANJE- tražimo točnu utakmicu (inače će prvi slični biti zamijenjen, što nam očito ne odgovara).
Zapravo, gotovu formulu možete prilagoditi svojim potrebama tako što ćete je lagano promijeniti. Rezultat formule prikazan je na donjoj slici: cijena je pronađena u drugoj tablici i umetnuta u automatskom načinu rada. Sve radi!
Da biste postavili cijenu za druge nazive proizvoda, samo povucite (kopirajte) formulu u druge ćelije. Pogledajte primjer u nastavku.
Nakon toga, kao što vidite, uspoređuju se prvi stupci tablica: iz redaka u kojima se podudaraju vrijednosti ćelija, kopirat će se i zamijeniti potrebni podaci. Općenito, jasno je da stolovi mogu biti puno veći!
Bilješka: Moram reći da je funkcija VLOOKUP prilično zahtjevna za računalne resurse. U nekim slučajevima, s pretjerano velikim dokumentom, može potrajati dosta vremena za usporedbu tablica. 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 instalacije dodatka, imat ćete novu karticu s naredbom za pozivanje funkcije. Kada kliknete na naredbu Uspoređivanje raspona pojavljuje se dijaloški okvir za unos parametara.
Ova makronaredba omogućuje vam usporedbu tablica bilo koje veličine i s bilo kojim brojem stupaca. Usporedba tablica može se vršiti u jednom, dva ili tri stupca u isto vrijeme.
Dijaloški okvir je podijeljen u dva dijela: lijevo za prvu tablicu i desno za drugu.
Za usporedbu tablica slijedite ove korake:
- Navedite raspone tablice.
- Postavite potvrdni okvir (potvrdni okvir / okvir za potvrdu) ispod odabranog raspona tablica ako tablica sadrži zaglavlje (redak zaglavlja).
- Odaberite stupce lijeve i desne tablice po kojima će se izvršiti usporedba (ako rasponi tablica ne sadrže zaglavlja, stupci će biti numerirani).
- Navedite vrstu usporedbe.
- Odaberite opciju za prikaz rezultata.
Tip usporedbe tablica
Program vam omogućuje odabir nekoliko vrsta usporedbe tablica:
Pronađite retke u jednoj tablici koji nedostaju u drugoj tablici
Kada je odabrana ova vrsta usporedbe, program traži retke iz jedne tablice koji nedostaju u drugoj. Ako usporedite tablice po nekoliko stupaca, tada će rezultat rada biti redovi u kojima postoji razlika u barem jednom od stupaca.
Pronađite odgovarajuće linije
Prilikom odabira ove vrste usporedbe, program pronalazi retke koji se podudaraju u prvoj i drugoj tablici. Redovi se smatraju podudarnim ako se vrijednosti u odabranim stupcima za usporedbu (1, 2, 3) jedne tablice potpuno podudaraju s vrijednostima stupaca druge tablice.
Primjer rada programa u ovom načinu rada prikazan je desno na slici.
Tablice karti na temelju odabranih
U ovom načinu usporedbe, nasuprot svakog retka prve tablice (odabrane kao glavne), kopiraju se podaci odgovarajućeg retka druge tablice. Ako nema odgovarajućih redaka, red nasuprot glavne tablice ostaje prazan.
Usporedba tablica na četiri ili više stupaca
Ako vam nedostaje funkcionalnost programa i trebate uskladiti tablice u četiri ili više stupaca, tada se možete izvući iz situacije na sljedeći način:
- Napravite prazan stupac u tablicama.
- Na novim stupcima pomoću formule = SPOJKA spojite stupce koje želite usporediti.
Na taj način ćete završiti s 1 stupcem koji sadrži vrijednosti više stupaca. Pa, već znate kako uskladiti jedan stupac.
Možda se svi koji rade s podacima u Excelu susreću s pitanjem kako usporediti dva stupca u Excelu za slučajnosti i razlike. Postoji nekoliko načina da to učinite. Pogledajmo pobliže svaki od njih.
Kako usporediti dva stupca u Excelu red po red
Kada se dva stupca uspoređuju s podacima, često je potrebno usporediti podatke u svakom zasebnom retku radi podudarnosti ili razlika. Ovu analizu možemo napraviti pomoću funkcije. Pogledajmo kako to radi u primjerima u nastavku.
Primjer 1. Kako usporediti dva stupca za podudaranja i razlike u jednom retku
Kako bismo usporedili podatke u svakom retku od dva stupca u Excelu, napisat ćemo jednostavnu formulu. Formulu treba umetnuti u svaki redak u susjednom stupcu, pored tablice u kojoj se nalaze osnovni podaci. Nakon što smo stvorili formulu za prvi red tablice, možemo je rastegnuti / kopirati na ostale retke.
Da bismo provjerili sadrže li dva stupca istog retka iste podatke, potrebna nam je formula:
= IF (A2 = B2, "Podudaranje", "")
Formula koja određuje razlike između podataka dvaju stupaca u jednom retku izgledat će ovako:
= IF (A2<>B2; "Ne podudaraju"; "")
Provjeru podudaranja i razlika između dva stupca možemo smjestiti u jedan redak u jednu formulu:
= IF (A2 = B2, “Podudaranje”, “Ne podudaranje”)
= IF (A2<>B2; "Ne podudaraju"; "Podudarati se")
Primjer rezultata izračuna može izgledati ovako:
Da biste usporedili podatke u dva stupca istog retka na način osjetljiv na velika i mala slova, koristite formulu:
= IF (podudaranje (A2, B2), "podudaranje", "jedinstveno")
Kako usporediti više stupaca za podudaranja u jednom retku Excel
Excel ima mogućnost usporedbe podataka u više stupaca istog retka prema sljedećim kriterijima:
- Pronađite retke s istim vrijednostima u svim stupcima tablice;
- Pronađite retke s istim vrijednostima u bilo koja dva stupca tablice;
Primjer 1. Kako pronaći podudaranja u jednom retku u više stupaca tablice
Zamislimo da se naša tablica sastoji od nekoliko stupaca podataka. Naš zadatak je pronaći retke u kojima su vrijednosti iste u svim stupcima. Funkcije Excel i. Formula za određivanje podudaranja bit će sljedeća:
= 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" je broj stupaca u tablici za koju smo kreirali formulu. Ako vaša tablica ima više ili manje stupaca, tada bi ova vrijednost trebala biti jednaka broju stupaca.
Primjer 2. Kako pronaći podudaranja u jednom retku u bilo koja dva stupca tablice
Zamislite da je naš zadatak identificirati iz tablice s podacima u nekoliko stupaca one retke u kojima su podaci isti ili se ponavljaju u najmanje dva stupca. Funkcije i pomoći će nam u tome. Napišimo formulu za tablicu koja se sastoji od tri stupca podataka:
= IF (ILI (A2 = B2; B2 = C2; A2 = C2); "Podudaranje"; "")
U slučajevima kada u našoj tablici ima previše stupaca, naša formula s funkcijom bit će vrlo velika, jer u njenim parametrima trebamo odrediti kriterije za podudaranje između svakog stupca tablice. Lakši način, u ovom slučaju, je korištenje funkcije.
= IF (COUNTIF (B2: D2; A2) + COUNTIF (C2: D2; B2) + (C2 = D2) = 0; "Jedinstveni niz"; "Nije jedinstveni niz")
= IF (COUNTIF ($ B: $ B; $ A5) = 0; "Nema podudaranja u stupcu B"; "Postoje podudaranja u stupcu B")
Ova formula provjerava vrijednosti u stupcu B kako bi provjerila odgovaraju li podacima u ćelijama u stupcu A.
Ako se vaša tablica sastoji od fiksnog broja redaka, možete odrediti jasan raspon u formuli (na primjer, $ B2: $ B10). To će ubrzati formulu.
Kako usporediti dva stupca u Excelu za podudaranja i istaknuti bojom
Kada tražimo podudaranja između dva stupca u Excelu, možda ćemo trebati vizualizirati pronađena podudaranja ili razlike u podacima, na primjer, pomoću isticanja boja. Najlakši način za isticanje podudarnosti i razlika bojom je korištenje "Uvjetnog oblikovanja" u Excelu. Pogledajmo kako to učiniti u primjerima u nastavku.
Pronađite i označite podudaranja u više stupaca u Excelu
U slučajevima kada trebamo pronaći podudaranja u nekoliko stupaca, za to nam je potrebno:
- Odaberite stupce s podacima u kojima želite izračunati podudaranja;
- Na kartici "Početna" na alatnoj traci kliknite na stavku izbornika "Uvjetno oblikovanje" -> "Pravila odabira ćelije" -> "Duplicirane vrijednosti";
- U skočnom dijaloškom okviru odaberite stavku "Duplicate" na lijevom padajućem popisu, u desnom padajućem popisu odaberite kojom će bojom biti istaknute duplicirane vrijednosti. Kliknite gumb "OK":
- Nakon toga, u odabranom stupcu, odgovarajuća boja će biti istaknuta:
Pronađite i označite odgovarajuće retke u Excelu
Traženje odgovarajućih ćelija s podacima u dva ili više stupaca i pronalaženje podudaranja za cijele retke s podacima različiti su koncepti. Obratite pažnju na dvije tablice u nastavku:
Gornje tablice sadrže iste podatke. Njihova razlika je u tome što smo u primjeru s lijeve strane tražili odgovarajuće ćelije, a na desnoj strani nalazili smo cijele retke podataka koji se ponavljaju.
Pogledajmo kako pronaći odgovarajuće retke u tablici:
- Desno od tablice s podacima stvorite pomoćni stupac u koji nasuprot svakog retka s podacima stavljamo formulu koja kombinira sve vrijednosti retka tablice u jednu ćeliju:
= A2 & B2 & C2 & D2
U pomoćnom stupcu vidjet ćete podatke kombinirane tablice:
Sada, da biste odredili podudarne retke u tablici, slijedite ove korake:
- Odaberite područje s podacima u pomoćnom stupcu (u našem primjeru, ovo je raspon ćelija E2: E15 );
- Na kartici "Početna" na alatnoj traci kliknite na stavku izbornika "Uvjetno oblikovanje" -> "Pravila odabira ćelije" -> "Duplicirane vrijednosti";
- U skočnom dijaloškom okviru odaberite "Duplicate" na lijevom padajućem popisu, u desnom padajućem popisu odaberite kojom će bojom biti istaknute duplicirane vrijednosti. Kliknite gumb "OK":
- Nakon toga u odabranom stupcu bit će istaknuti duplikati redaka:
