Excel za poređenje dvije tabele. Kreiranje uzoraka listova sa podacima

Ponekad želite da vidite samo one zapise u Access tabeli koji odgovaraju zapisima iz druge tabele koji sadrže polja sa podudarnim podacima. Na primjer, možda ćete htjeti pogledati evidenciju zaposlenih koji su izvršili barem jednu narudžbu kako biste utvrdili ko zaslužuje unapređenje. Ili ćete možda morati pogledati kontakt podatke kupaca koji žive u istom gradu kao i zaposlenik kako biste dogovorili lični sastanak.

Ako treba da uporedite dva Pristupni stolovi i pronaći odgovarajuće podatke, postoje dvije opcije.

Kreirajte upit koji spaja polja iz svake tablice koja sadrži podudarne podatke, koristeći postojeći odnos ili spoj kreiran za upit. Ova metoda je optimalna u smislu brzine vraćanja rezultata upita, ali ne dozvoljava kombinovanje polja sa podacima različitih tipova.

Kreirajte upit za poređenje polja koji koristi jedno polje kao uslov za drugo. Ova metoda obično traje više vremena jer spajanje isključuje redove iz rezultata upita prije čitanja osnovnih tablica, dok se uvjeti primjenjuju na rezultate upita nakon čitanja tih tablica. Međutim, polje se može koristiti kao uslov za poređenje polja sa podacima različitih tipova, što se ne može uraditi pomoću spojeva.

Ovaj članak govori o tome kako uporediti dvije tabele da biste identificirali podudarne podatke i pruža uzorke podataka koji se mogu koristiti u uzorcima procedura.

U ovom članku

Poređenje dvije tablice pomoću spojeva

Da biste uporedili dvije tabele pomoću spajanja, morate kreirati upit za odabir koji uključuje obje tablice. Ako tabele još nemaju odnos na poljima koja sadrže željene podatke, potrebno je da na njima kreirate spojeve. Možete kreirati koliko god želite spojeva, ali svaki par spojenih polja mora sadržavati podatke istog ili kompatibilnog tipa.

Recimo da radite na univerzitetu i želite znati kako su nedavne promjene u nastavnom planu i programu matematike utjecale na ocjene studenata. Posebno vas zanimaju ocjene onih učenika čiji je glavni predmet matematika. Već imate tabelu koja sadrži podatke o smjerovima i tabelu koja sadrži podatke o studentima koji ih studiraju. Podaci o ocjenama se pohranjuju u tabeli Studenti, a podaci o smjerovima u tabeli Specijalizacije. Da biste vidjeli kako su se ocjene matematičkih smjerova promijenile od nedavnih promjena nastavnog plana i programa, trebate pogledati unose u tabeli Studenti koji odgovaraju unosima u tabeli Specijalizacije.

Priprema uzoraka podataka

AT ovaj primjer kreirate upit koji određuje kako su nedavne promjene u nastavnom planu i programu matematike utjecale na ocjene učenika u tom smjeru. Koristite dvije tabele ispod: "Specijalizacije" i "Studenti". Dodajte ih u bazu podataka.

Access nudi nekoliko načina za dodavanje ovih tabela u uzorak baze podataka. Možete ručno unijeti podatke, kopirati svaku tabelu u program za proračunske tablice (na primjer, Microsoft Office Excel 2007), a zatim uvezite listove u Access ili možete u njih zalijepiti podatke uređivač teksta, kao što je Notepad, a zatim uvezite podatke iz rezultirajućih tekstualnih datoteka.

AT upute korak po korak ovaj odeljak objašnjava kako ručno uneti podatke prazan list, kao i kako kopirati uzorke tabela u Excel i zatim ih uvesti u Access.

Specijalizacije

studenti

Ako ćete unijeti uzorke podataka u tabela, može .

Ručni unos podataka uzorka

Ako niste zainteresovani za kreiranje lista na osnovu uzorka podataka, preskočite sljedeći odjeljak("Kreiranje uzoraka listova sa podacima").

Kreiranje uzoraka listova sa podacima

Kreiranje tabela baze podataka iz listova

Poređenje tabela uzoraka i pronalaženje podudarnih zapisa pomoću spojeva

Sada ste spremni da uporedite tabele studenata i specijalizacija. Budući da odnosi između dvije tablice nisu definirani, potrebno je kreirati spojeve na odgovarajućim poljima u upitu. Tabele sadrže više polja i moraćete da kreirate spoj za svaki par zajedničkih polja: ID učenika, godina i nastavni plan i program (u tabeli Studenti) i specijalizacija (u tabeli specijalizacije). AT ovaj slučaj zanima nas samo matematika, tako da možemo ograničiti rezultate upita sa uslovom polja.

Na kartici Kreacija pritisnite dugme Query Builder.

U dijaloškom okviru Dodavanje tabele dvaput kliknite na tabelu koja sadrži željene zapise (studenti), a zatim dvaput kliknite na tabelu s kojom je upoređujete ( Specijalizacije).

Zatvorite dijalog Dodavanje tabele.

Povucite kutiju Studentski kod sa stola studenti u polju Studentski kod stolovi Specijalizacije. Između dvije tablice u dizajnu upita pojavit će se linija koja označava da je spoj kreiran. Dvaput kliknite na liniju da otvorite dijalog Opcije spajanja.

Obratite pažnju na tri opcije u okviru za dijalog Opcije spajanja. Podrazumevano je odabrana opcija 1. U nekim slučajevima morate dodati dodatne linije sa jednog stola. Pošto želite da pronađete samo podudarne podatke, ostavite spajanje postavljeno na 1. Zatvorite dijaloški okvir Opcije spajanja pritiskom na dugme Otkaži.

Morate kreirati još dva spoja. Da biste to učinili, prevucite polje Godina sa stola studenti u polju Godina stolovi Specijalizacije, a zatim polje Akademski plan sa stola studenti u polju Specijalizacija stolovi Specijalizacije.

Table studenti dvaput kliknite na zvjezdicu ( * ) za dodavanje svih polja tabele u obrazac zahtjeva.

Bilješka: Studenti.*.

Table Specijalizacije dvaput kliknite na polje Specijalizacija da ga dodate u obrazac.

Pokaži kolona Specijalizacija.

U redu Uvjet odabira kolona Specijalizacija enter MATH.

Na kartici Constructor u grupi rezultate pritisnite dugme Trči.

Poređenje dvije tabele koristeći polje kao uslov

Ponekad je potrebno porediti tabele na osnovu polja sa odgovarajućim vrednostima, ali različite vrste podaci. Na primjer, polje u jednoj tabeli može imati numerički tip podataka, ali želite da ga uporedite sa poljem u drugoj tabeli koje ima tekstualni tip podataka. Polja koja sadrže slične podatke različitih tipova mogu se pojaviti kada se brojevi pohranjuju kao tekst (na primjer, prilikom uvoza podataka iz drugog programa). Pošto nije moguće kreirati spojeve polja sa podacima različitih tipova, moraćete da koristite drugu metodu. Da biste uporedili dva polja sa različitim tipovima podataka, možete koristiti jedno polje kao uslov za drugo.

Recimo da radite na univerzitetu i želite znati kako su nedavne promjene u nastavnom planu i programu matematike utjecale na ocjene studenata. Posebno vas zanimaju ocjene onih učenika čiji je glavni predmet matematika. Već imate tabele "Specijalizacije" i "Studenti". Podaci o ocjenama se pohranjuju u tabeli Studenti, a podaci o smjerovima u tabeli Specijalizacije. Da biste vidjeli kako su se ocjene promijenile za matematičke smjerove, trebate pogledati zapise u tabeli Studenti koji odgovaraju zapisima u tabeli smjerova. Međutim, jedno od polja koje želite da koristite za poređenje tabela nema isti tip podataka kao polje sa kojim se uparuje.

Da biste uporedili dvije tabele koristeći polje kao uslov, morate kreirati upit za odabir koji uključuje obje tablice. U upit uključite polja koja želite da prikažete, kao i polje koje odgovara polju koje će se koristiti kao uslov. Zatim kreirajte uslov za upoređivanje tabela. Možete kreirati onoliko uslova koliko vam je potrebno da uporedite polja.

Za ilustraciju ove metode koristićemo , ali ćemo u polju "Šifra učenika" tabele "Specijalizacije" promijeniti numerički tip podataka u tekstualni. Pošto ne možemo da kreiramo uniju dva polja sa različitim tipovima podataka, moraćemo da uporedimo dva polja studentskog ID-a, koristeći jedno polje kao uslov za drugo.

Promenite tip podataka u polju ID studenta u tabeli Specijalizacije

Otvorite bazu podataka u koju ste spremili primjere tablica.

AT oblasti navigacije kliknite na tabelu "Specijalizacije". desni klik miša i odaberite Constructor.

Tabela Specijalizacije otvara se u prikazu dizajna.

U koloni Tip podataka promjena za polje Studentski kod tip podataka Broj na Tekst.

Zatvorite tabelu "Specijalizacije". Kliknite na dugme Da kada se od vas zatraži da sačuvate promjene.

Upoređivanje uzoraka tablica i pronalaženje odgovarajućih zapisa korištenjem uvjeta polja

U nastavku je prikazano kako uporediti dva polja studentskog ID-a koristeći polje iz tabele Studenti kao uslov za polje iz tabele Specijalizacije. Via ključna riječ Sviđa mi se možete uporediti dva polja čak i ako sadrže različite vrste podataka.

Na kartici Stvoriti u grupi Ostalo pritisnite dugme Query Builder.

U dijaloškom okviru Dodavanje tabele dvaput kliknite na tabelu studenti a zatim sto Specijalizacije.

Zatvorite dijalog Dodavanje tabele.

Povucite kutiju Godina sa stola studenti u polju Godina stolovi Specijalizacije, a zatim polje Akademski plan sa stola studenti u polju Specijalizacija stolovi Specijalizacije. Ova polja sadrže podatke istog tipa, tako da se spojevi mogu koristiti za njihovo poređenje. Spajanja se preporučuju za poređenje polja sa podacima istog tipa.

Dvaput kliknite na zvjezdicu ( * ) u tabeli studenti da dodate sva polja tabele u obrazac zahteva.

Bilješka: Kada koristite zvjezdicu za dodavanje svih polja u obrazac, prikazuje se samo jedna kolona. Naziv ove kolone uključuje ime tabele praćeno tačkom (.) i zvjezdicom (*). U ovom primjeru stupac je imenovan Studenti.*.

Table Specijalizacije dvaput kliknite na polje Studentski kod da ga dodate u obrazac.

U obrascu zahtjeva poništite oznaku linije Pokaži kolona Studentski kod. U redu Uvjet odabira kolona Studentski kod enter Sviđa mi se [Studenti].[Student ID].

Table Specijalizacije dvaput kliknite na polje Specijalizacija da ga dodate u obrazac.

U obrascu zahtjeva poništite oznaku linije Pokaži kolona Specijalizacija. U redu Uvjet odabira enter MATH.

Na kartici Constructor u grupi rezultate pritisnite dugme Trči.

Upit se izvršava i prikazuju se samo ocjene iz matematike onih učenika koji imaju ovaj predmet kao glavni.

Imamo dvije tabele naloga kopirane u jedan radni list. Potrebno je uporediti podatke dvije tabele u Excel-u i provjeriti koje su pozicije u prvoj, a ne u drugoj. Nema smisla ručno upoređivati ​​vrijednost svake ćelije.

Uporedite dvije kolone za podudaranja u Excelu

Kako uporediti vrijednosti u Excelu dvije kolone? Da biste riješili ovaj problem, preporučujemo korištenje uvjetnog oblikovanja, koje brzo ističe pozicije koje se nalaze u samo jednoj koloni. Radni list sa tabelama:

Prvi korak je imenovanje obje tabele. Ovo olakšava razumijevanje koji se rasponi ćelija upoređuju:

1. Odaberite alat FORMULA - Definirana imena - Dodijeli ime.
2. U prozoru koji se pojavi, u polje "Naziv:" unesite vrijednost - Table_1.
3. Kliknite lijevom tipkom miša na polje za unos "Range:" i odaberite raspon: A2:A15. I kliknite OK.

Za drugu listu slijedite iste korake, samo dodijelite ime - Tabela_2. I navedite raspon C2:C15 - respektivno.

Koristan savjet! Nazivi raspona se mogu brže dodijeliti pomoću polja za naziv. Nalazi se lijevo od trake formule. Samo odaberite opsege ćelija i u polje za ime unesite odgovarajuće ime za raspon i pritisnite Enter.

Sada koristimo uslovno formatiranje da uporedimo dve liste u Excel-u. Moramo dobiti sljedeći rezultat:

Stavke koje se nalaze u Tabeli_1, ali ne u Tabeli_2 će biti prikazane u zelenoj boji. Istovremeno, pozicije koje se nalaze u Tabeli_2, a ne u Tabeli_1 će biti istaknute plavom bojom.

Princip poređenja podataka dve kolone u Excel-u

Prilikom definiranja uvjeta za formatiranje ćelija kolone koristili smo funkciju COUNTIF. U ovom primjeru, ova funkcija provjerava koliko puta se vrijednost drugog argumenta (na primjer, A2) pojavljuje na listi prvog argumenta (na primjer, Table_2). Ako je broj puta = 0 onda formula vraća TRUE. U ovom slučaju, ćeliji se dodjeljuje prilagođeni format naveden u opcijama uvjetnog oblikovanja.

Veza u drugom argumentu je relativna, što znači da će sve ćelije u odabranom rasponu biti provjerene redom (na primjer, A2:A15). Na primjer, da uporedite dva cjenika u Excelu, čak i na različitim listovima. Druga formula radi na sličan način. Isti princip se može primijeniti na različite slične zadatke.

Ponekad je potrebno uporediti dva MS Excel fajla. To može biti pronalaženje odstupanja cijena za određene pozicije ili promjena bilo kojeg očitavanja, nije važno, najvažnije je pronaći određena odstupanja.

Ne bi bilo suvišno napomenuti da ako postoji nekoliko zapisa u MS Excel datoteci, onda nema smisla pribjeći automatizaciji. Ako datoteka ima nekoliko stotina, pa čak i hiljada zapisa, onda ne možete 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. Takva situacija je moguća, na primjer, ako je potrebno uporediti cijenu robe po dva cjenovnika, ili uporediti mjere sportista prije i poslije sezone treninga, iako bi za takvu automatizaciju trebalo da ih ima 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 za rješavanje problema. Rješenje je samo korištenjem MS Excel formula.

Pošto su zapisi raspoređeni okomito (najlogičnija konstrukcija), potrebno je koristiti . U slučaju korištenja horizontalnog postavljanja zapisa, morat ćete koristiti funkciju.

Za usporedbu performansi trčanja na 100m, formula je sljedeća:
=IF(VLOOKUP($B2;Stab2!$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, ako postoji onda se indikator početka sezone oduzima od vrijednosti na kraju 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 je konačno 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. Odredišni fajl sa uočenim neslaganjima dat je u nastavku.

Malo objašnjenje. Radi lakše čitljivosti formula, podaci iz dva fajla su premješteni u jedan (na različite listove), ali to nije moglo biti učinjeno.

Video koji upoređuje dvije MS Excel datoteke pomoću funkcija i.

Drugi način rješavanja problema. Rješenje koristeći MS Access.

Zadatak se može riješiti tako što ćete prvo uvesti 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 raditi).

Ovo posljednje je veza Excel fajlovi i Access, tako da kada promijenite podatke u Excel datotekama, neslaganja će se automatski pronaći kada pokrenete upit u MS Access-u.

Sljedeći korak nakon uvoza je kreiranje odnosa između tabela. Kao polje za vezivanje odaberite jedinstveno polje "Br. p / p".
Treći korak je stvaranje jednostavan zahtjev na odabiru pomoću alata za pravljenje upita.

U prvoj koloni označavamo koji zapisi treba da se prikazuju, au drugoj - pod kojim uslovima će zapisi biti prikazani. Naravno, za drugo i treće polje radnje ć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 na 100 metara." MS Access datoteka je predstavljena u nastavku (nažalost, SkyDrive vam ne dozvoljava da ugradite kao Excel datoteku)

Evo dva načina da pronađete odstupanja u MS Excel tabelama. Svaki od njih ima i prednosti i nedostatke. Jasno je da ovo nije potpuna lista poređenja između dva Excel fajla. Čekamo vaše prijedloge u komentarima.

Često je zadatak uporediti dvije liste elemenata. Ručno to raditi previše je zamorno, a osim toga, ne može se isključiti mogućnost greške. Excel olakšava ovu operaciju. AT ovog Saveta opisuje metodu koristeći uslovno formatiranje.

Na sl. 164.1 je primjer dvije liste imena sa više kolona. Primjena uvjetnog oblikovanja može učiniti razlike u listama očiglednijima. Ovi primjeri lista sadrže tekst, ali ovaj metod radi i sa numeričkim podacima.

Prva lista je A2:B31, ovaj raspon se zove stara lista. Druga lista je D2:E31, opseg se zove nova lista. Opsezi su imenovani naredbom Formule Definirana imena Dodijelite ime. Imenovanje raspona nije obavezno, ali olakšava rad s njima.

Počnimo s dodavanjem uvjetnog oblikovanja na staru listu.

1. Odaberite ćelije raspona stara lista.
2. Odaberite .
3. U prozoru Kreirajte pravilo oblikovanja izaberite stavku po imenu Koristite formulu
4. Unesite ovu formulu u polje prozora (Sl. 164.2): =COUNTIF(NewList;A2)=0 .
5. Kliknite na dugme Format i postavite da se formatiranje primjenjuje kada je uvjet istinit. Najbolje izabrati razne boje puni.
6. Kliknite OK.

Ćelije u rasponu nova lista koristite sličnu formulu za uvjetno oblikovanje.

1. Odaberite ćelije raspona nova lista.
2. Odaberite Dom Uslovno formatiranje Kreiraj pravilo da otvorite dijaloški okvir Kreirajte pravilo oblikovanja.
3. U prozoru Kreirajte pravilo formatiranje odaberite stavku Koristite formulu za definiranje ćelija koje će se formatirati.
4. Unesite ovu formulu u okvir prozora: =COUNTIF(OldList;D2)=0 .
5. Kliknite na dugme Format i postavite formatiranje koje će se primijeniti kada je uvjet istinit (drugačija boja ispune).
6. Kliknite OK.

Kao rezultat, imena koja se nalaze na staroj listi, ali koja nisu u novoj, biće istaknuta (Sl. 164.3). Osim toga, istaknuta su i imena na novoj listi koja nisu na staroj, ali drugom bojom. Imena koja se pojavljuju na obje liste nisu istaknuta.

Obje formule za uvjetno oblikovanje koriste funkciju COUNTIF. Broji koliko se puta određena vrijednost pojavljuje u rasponu. Ako formula vrati 0, to znači da element nije u rasponu. Dakle, uslovno oblikovanje preuzima i boja pozadine ćelije se mijenja.

Danas ću vam reći kako da tražite podudaranje u Excel kolone. Analizirajmo sve suptilnosti s primjerima.

Zadatak 1: Ima 6 tekstova u 6 ćelija. Potrebno je otkriti koji su od njih jedinstveni, a koji se ponavljaju.

Koristićemo Uslovno formatiranje.

• Odaberite ćelije koje želite uporediti;
• U kartici Dom idite na "Uvjetno oblikovanje -> Pravila odabira ćelije -> Duplicirane vrijednosti";

Pojavljuje se novi prozor i ćelije koje se ponavljaju počinju da se naglašavaju u tabeli.

U ovom prozoru možete odabrati dvije postavke: Istakni ponavljajući ili Jedinstvene ćelije, kao i kakvu vrstu isticanja koristiti - obrub, tekst, boju teksta, pozadinu ili svoj jedinstveni.

Razmotrimo još jedan primjer. Morate uporediti dvije kolone u Excelu za podudaranja. Postoji tabela u kojoj se takođe nalaze podudaranja, ali već numerička.

Odaberite sto i idite na Duplicirane vrijednosti. Sve utakmice će biti istaknute.

Možete, na primjer, pronaći podudaranja u jednoj koloni. Da biste to učinili, dovoljno je odabrati samo nju prije primjene opcije.

Pa, kao što sam rekao gore, sa padajuće liste možete odabrati ne samo ponavljajuće ćelije, već i jedinstvene.

Na ovaj način možete tražiti slova, riječi, simbole, tekstove itd.

U ovoj lekciji ću vam reći kako popraviti šešir na svakom od njih Excel stranica. Ovo može biti potrebno ako radite sa velikim stolovima. Obično, kada se štampaju, dokumenti sa velika količina stranice, a kako bi rad sa njima bio pogodan, potrebno je sačuvati zaglavlje tabele na svakom listu.

U ovom vodiču pokazat ću vam kako podijeliti tekst u stupce u Excelu. Ova lekcija je pogodan za vas ako želite podijeliti tekst iz jedne kolone u nekoliko. Sada ću dati primjer. Recimo da imate ćeliju "A", koja sadrži ime, prezime i srednje ime. Morate biti sigurni da je u prvoj ćeliji "A" samo prezime, u ćeliji "B" - ime, au ćeliji "C" patronim.

U ovom članku ću vam reći kako ukloniti duplikate u excelu. Razmotrit ćemo najjednostavnije redovnim putem, koji postoji od Excela 2007.