Napomena: Microsoft Access ne podržava uvoz Excel podataka sa primenjenim oznakom osetljivosti. Kao privremeno rešenje možete da uklonite oznaku pre uvoza, a zatim ponovo primenite oznaku nakon uvoza. Više informacija potražite u članku Primena oznaka osetljivosti na datoteke i e-poštu u sistemu Office.
Ovaj članak vam pokazuje kako da premestite podatke iz programa Excel u Access i konvertujete podatke u relacione tabele kako biste mogli da koristite Microsoft Excel i Access zajedno. Da rezimirate, Access je najbolji za hvatanje, skladištenje, izvršavanje upita i deljenje podataka, a Excel je najbolji za izračunavanje, analizu i vizuelizaciju podataka.
U dva članka, korišćenje programa Access ili Excel za upravljanje podacima i prvih 10 razloga za korišćenje programa Access sa programom Excel, objašnjavaju koji program je najprikladniji za određeni zadatak i kako da koristite Excel i Access zajedno da biste kreirali praktično rešenje.
Kada premestite podatke iz programa Excel u Access, postoje tri osnovna koraka procesa.
Napomena: Informacije o modeloingu podataka i relacijama u programu Access potražite u članku Osnove dizajniranja baze podataka.
1. korak: Uvoz podataka iz programa Excel u Access
Uvoz podataka je operacija koja može mnogo nesmetano da proteže ako vam bude potrebno neko vreme da pripremite i očistite podatke. Uvoz podataka je kao premeštanje na novu kuću. Ako očistite i organizujete svoj posed pre nego što se preselite, mnogo je lakše da se uklonite u novi dom.
Čišćenje podataka pre uvoza
Pre nego što uvezete podatke u Access, u programu Excel je dobra ideja da:
-
Konvertujte ćelije koje sadrže neatomske podatke (to jest, više vrednosti u jednoj ćeliji) u više kolona. Na primer, ćeliju u koloni "Veštine" koja sadrži više vrednosti veština, kao što je "C# programiranje", "VBA programiranje" i "Veb dizajn" trebalo bi da se razdvoje da bi se razdvojile kolone od kojih svaka sadrži samo jednu vrednost veštine.
-
Koristite komandu TRIM da biste uklonili početni, završni i više ugrađenih razmaka.
-
Uklonite znakove koji se ne štampaju.
-
Pronađite i ispravite pravopisne i interpunkcijske greške.
-
Uklonite duplirane redove ili duplirana polja.
-
Uverite se da kolone podataka ne sadrže mešovite formate, naročito brojeve oblikovane kao tekst ili datume oblikovane kao brojevi.
Dodatne informacije potražite u sledećim Excel temama pomoći:
Napomena: Ako su vaše potrebe za čišćenjem podataka složene ili nemate vremena ili resursa da automatizujete proces sami, možete da razmotrite korišćenje nezavisnog prodavca. Za više informacija potražite termin "softver za čišćenje podataka" ili "kvalitet podataka" od strane omiljenog pretraživača u veb pregledaču.
Izbor najboljeg tipa podataka prilikom uvoza
Tokom operacije uvoza u programu Access, želite da napravite dobre izbore kako biste primili nekoliko (ako postoje) grešaka u konvertovanju koje zahtevaju ručnu intervenciju. Sledeća tabela rezimira kako se Excel formati brojeva i Access tipovi podataka konvertuju kada uvozite podatke iz programa Excel u Access i nudi neke savete o najboljim tipovima podataka koje možete odabrati u čarobnjaku za uvoz unakrsnih tabela.
Excel format broja |
Access tip podataka |
Komentari |
Najbolja praksa |
---|---|---|---|
Tekst |
Tekst, memorandum |
Tip podataka Access tekst skladišti alfanumerične podatke do 255 znakova. Tip podataka Access memo skladišti alfanumerične podatke do 65.535 znakova. |
Odaberite stavku Memorandum da biste izbegli skraćivanje podataka. |
Broj, procenat, razlomak, naučni |
Broj |
Access ima jedan tip podataka "Broj" koji se razlikuje u zavisnosti od svojstva Veličina polja (Bajt, Ceo broj, Dugački ceo broj, Jedan, Dvostruki, Decimalni). |
Odaberite opciju Dvostruko da biste izbegli greške u konvertovanju podataka. |
Datum |
Datum |
Access i Excel koriste isti redni broj datuma za skladištenje datuma. U programu Access opseg datuma je veći: od -657.434 (1. januar, 100. aprila) do 2.958.465 (31. decembar 9999.). Pošto Access ne prepoznaje datumski sistem 1904 (koji se koristi u programu Excel za Macintosh), morate da konvertujete datume u programu Excel ili Access da biste izbegli zabunu. Više informacija potražite u člancima Promena sistema datuma, formata ili dvocifrene interpretacije godine i Uvoz ili povezivanje sa podacima u Excel radnoj svesci. |
Odaberite stavku Datum. |
Vreme |
Time |
Access i Excel skladište vrednosti vremena pomoću istog tipa podataka. |
Odaberite stavku Vreme, što je obično podrazumevano. |
Valuta, računovodst. |
Valuta |
U programu Access tip podataka "Valuta" skladišti podatke kao brojeve od 8 bajta precizno na četiri decimalna mesta i koristi se za skladištenje finansijskih podataka i sprečavanje zaokruživanja vrednosti. |
Odaberite stavku Valuta, što je obično podrazumevana vrednost. |
Bulov |
Da/ne |
Access koristi -1 za sve vrednosti "Da" i 0 za sve vrednosti "Ne", dok Excel koristi 1 za sve TRUE vrednosti i 0 za sve FALSE vrednosti. |
Odaberite da/ne, koje automatski konvertuje osnovne vrednosti. |
Hiperveza |
Hiperveza |
Hiperveza u programima Excel i Access sadrži URL ili veb adresu na koju možete da kliknete i pratite je. |
Odaberite stavku Hiperveza, u suprotnom Access podrazumevano može da koristi tekstualni tip podataka. |
Kada podaci u programu Access, možete da izbrišete Excel podatke. Ne zaboravite da prvo napravite rezervnu kopiju originalne Excel radne sveske pre nego što je izbrišete.
Više informacija potražite u temi pomoći za Access Uvoz podataka u Excel radnoj svesci ili povezivanje sa podacima.
Automatsko dodavanje podataka na lakši način
Uobičajeni problem koji korisnici programa Excel imaju jeste dodavanje podataka sa istim kolonama u jedan veliki radni list. Na primer, možda imate rešenje za praćenje imovine koje je počelo u programu Excel, ali je sada naraslo da uključuje datoteke iz mnogih radnih grupa i odeljenja. Ovi podaci se mogu nalaziti u različitim radnim listovima i radnim svescima ili u tekstualnim datotekama koje su feedovi podataka iz drugih sistema. Ne postoji komanda korisničkog interfejsa niti jednostavan način za dodavanje sličnih podataka u Excel.
Najbolje rešenje je da koristite Access, gde lako možete da uvezete i dodate podatke u jednu tabelu pomoću čarobnjaka za uvoz unakrsnih tabela. Pored toga, možete da dodate mnogo podataka u jednu tabelu. Možete da sačuvate operacije uvoza, dodate ih kao planirane Microsoft Outlook zadatke, pa čak i da koristite makroe da biste automatizovali proces.
2. korak: Normalizacija podataka pomoću čarobnjaka za analizator tabele
Na prvi pogled, prolaženje kroz proces normalizacije podataka može da izgleda kao omažavajući zadatak. Srećom, normalizovanje tabela u programu Access je proces koji je mnogo lakši, zahvaljujući čarobnjaku za analizu tabela.
1. Prevucite izabrane kolone u novu tabelu i automatski kreirajte relacije
2. Koristite komande na dugmetu da biste preimenovali tabelu, dodali primarni ključ, postavili postojeću kolonu kao primarni ključ i opozvali poslednju radnju
Ovaj čarobnjak možete da koristite da biste uradili sledeće:
-
Konvertujte tabelu u skup manjih tabela i automatski kreirajte relaciju primarnog i sporednog ključa između tabela.
-
Dodajte primarni ključ u postojeće polje koje sadrži jedinstvene vrednosti ili kreirajte novo polje sa ID-om koje koristi tip podataka "Automatsko numerisanje".
-
Automatski kreirajte relacije da biste nametnuli referencijalni integritet pomoću kaskadnih ispravki. Kaskadna brisanja se ne dodaju automatski da bi se sprečilo slučajno brisanje podataka, ali kasnije možete lako da dodate kaskadna brisanja.
-
Pretražite nove tabele da biste potražili suvišne ili duplirane podatke (kao što je isti klijent sa dva različita broja telefona) i ažurirajte ih na željeni način.
-
Napravite rezervnu kopiju originalne tabele i preimenujte je tako što ćete dodati "_OLD" u njeno ime. Zatim kreirate upit koji rekonstruiše originalnu tabelu sa imenom originalne tabele tako da svi postojeći obrasci ili izveštaji zasnovani na originalnoj tabeli rade sa novom strukturom tabele.
Dodatne informacije potražite u članku Normalizovanje podataka pomoću alatke za analizu tabele.
3. korak: Povezivanje sa Access podacima iz programa Excel
Kada se podaci normalizuju u programu Access i napravi upit ili tabela koji rekonstruišu originalne podatke, jednostavno se povezujete sa Access podacima iz programa Excel. Podaci su sada u programu Access kao spoljni izvor podataka i mogu da se povezu sa radnom sveskom putem podataka za povezivanje, što predstavlja kontejner informacija koji se koristi za pronalaženje i prijavljivanje u spoljni izvor podataka i pristupanje spoljnom izvoru podataka. Informacije o vezi se skladište u radnoj svesci i mogu se uskladištiti i u datoteci veze, kao što je Office Data Connection (ODC) datoteka (oznaka tipa datoteke.odc) ili datoteka sa imenom izvora podataka (.dsn oznaka tipa datoteke). Kada se povežete sa spoljnim podacima, Excel radnu svesku možete da osvežite (ili ažurirate) iz programa Access svaki put kada se podaci ažuriraju u programu Access.
Više informacija potražite u članku Uvoz podataka iz spoljnih izvora podataka (Power Query).
Prenesite podatke u Access
Ovaj odeljak vas vodi kroz sledeće faze normalizovanja podataka: Prelamanje vrednosti u kolonama "Prodavac" i "Adresa" u njihove najuspešnije atomske delove, odvajanje srodnih tema u sopstvene tabele, kopiranje i lepljenje tih tabela iz programa Excel u Access, kreiranje ključnih relacija između novokreiranih Access tabela i kreiranje i pokretanje jednostavnog upita u programu Access radi dobijanja informacija.
Example data in non-normalized form
Sledeći radni list sadrži vrednosti koje nisu atomske u koloni "Prodavac" i koloni "Adresa". Obe kolone bi trebalo da budu razdeljene na dve ili više zasebnih kolona. Ovaj radni list takođe sadrži informacije o prodavcima, proizvodima, kupcima i porudžbinama. Ove informacije bi, po temi, trebalo dalje da se razdeljuju u zasebne tabele.
Prodavac |
ID porudžbine |
Datum porudžbine |
ID proizvoda |
Kol |
Cenić |
Ime klijenta |
Adresa |
Telefon |
---|---|---|---|---|---|---|---|---|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 USD |
Četvrta kafa |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Četvrta kafa |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adams, Elen |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 DIN. |
Avanturistički poduhti |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Elen |
2350 |
3/4/09 |
F-198 |
6 |
5,25 DIN. |
Avanturistički poduhti |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Elen |
2350 |
3/4/09 |
B-205 |
1 |
4,50 USD |
Avanturistički poduhti |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hans, Džim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Contoso d.o.o. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
Hans, Džim |
2352 |
3/5/09 |
A-2275 |
2 |
16,75 DIN. |
Avanturistički poduhti |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hans, Džim |
2352 |
3/5/09 |
D-4420 |
3 |
7,25 DIN. |
Avanturistički poduhti |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 DIN. |
Četvrta kafa |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 USD |
Četvrta kafa |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informacije u najmanjim delovima: atomički podaci
Radeći sa podacima u ovom primeru, možete da koristite komandu Tekst u kolonu u programu Excel da biste razdvojili "atomske" delove ćelije (kao što su ulica i adresa, grad, država i poštanski broj) u diskretne kolone.
Sledeća tabela prikazuje nove kolone u istom radnom listu nakon što su razdeljene da bi sve vrednosti učinile atomimačnim. Imajte na umu da su informacije u koloni "Prodavac" razdeljene na kolone "Prezime" i "Ime" i da su informacije u koloni "Adresa" razdeljene u kolone "Ulica i grad", "Država" i "Poštanski broj". Ti podaci su u "prvom normalnom obliku".
Prezime |
Ime |
|
ulicu i broj |
Grad |
Država |
Poštanski broj |
---|---|---|---|---|---|---|
Li (Li) |
Jejl |
2302 Harvard Ave |
Novi Sad |
Vašington |
98227 |
|
Adams |
Elen |
Krug 1025 Kolumbija |
Sombor |
Vašington |
98234 |
|
Hans (Hance) |
Bojan |
2302 Harvard Ave |
Novi Sad |
Vašington |
98227 |
|
Koch (Koch) |
Rid |
Cornell St Redmond 7007 |
Kragujevac |
Vašington |
98199 |
Razbijanje podataka u organizovane teme u programu Excel
Nekoliko tabela primera podataka koje slede pokazuju iste informacije iz Excel radnog lista nakon razdeljivanje u tabele za prodavce, proizvode, klijente i porudžbine. Dizajn tabele nije konačni, ali je na pravom putu.
Tabela "Prodavci" sadrži samo informacije o osoblju za prodaju. Imajte na umu da svaki zapis ima jedinstveni ID (ID prodavca). Vrednost ID-a prodavca će se koristiti u tabeli "Porudžbine" za povezivanje porudžbina sa prodavcima.
Prodavci |
||
---|---|---|
ID prodavca |
Prezime |
Ime |
101 |
Li (Li) |
Jejl |
103 |
Adams |
Elen |
105 |
Hans (Hance) |
Bojan |
107 |
Koch (Koch) |
Rid |
Tabela "Proizvodi" sadrži samo informacije o proizvodima. Imajte na umu da svaki zapis ima jedinstveni ID (ID proizvoda). Vrednost ID-a proizvoda će se koristiti za povezivanje informacija o proizvodu sa tabelom "Detalji porudžbine".
Proizvoda |
|
---|---|
ID proizvoda |
Cenić |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7.00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5.25 |
Tabela "Klijenti" sadrži samo informacije o klijentima. Imajte na umu da svaki zapis ima jedinstveni ID (ID klijenta). Vrednost "ID klijenta" koristiće se za povezivanje informacija o klijentima sa tabelom "Porudžbine".
Klijenti |
||||||
---|---|---|---|---|---|---|
ID kupca |
Ime |
ulicu i broj |
Grad |
Država |
Poštanski broj |
Telefon |
1001 |
Contoso d.o.o. |
2302 Harvard Ave |
Novi Sad |
Vašington |
98227 |
425-555-0222 |
1003 |
Avanturistički poduhti |
Krug 1025 Kolumbija |
Sombor |
Vašington |
98234 |
425-555-0185 |
1005 |
Četvrta kafa |
Cornell 7007 |
Kragujevac |
Vašington |
98199 |
425-555-0201 |
Tabela "Porudžbine" sadrži informacije o porudžbinama, prodavcima, klijentima i proizvodima. Imajte na umu da svaki zapis ima jedinstveni ID (ID porudžbine). Neke od informacija u ovoj tabeli treba razdeliti u dodatnu tabelu koja sadrži detalje porudžbine tako da tabela "Porudžbine" sadrži samo četiri kolone – jedinstveni ID porudžbine, datum porudžbine, ID prodavca i ID kupca. Tabela koja je ovde prikazana još nije razdeljena u tabelu sa detaljima porudžbine.
Porudžbine |
|||||
---|---|---|---|---|---|
ID porudžbine |
Datum porudžbine |
ID prodavca |
ID kupca |
ID proizvoda |
Kol |
2349 |
3/4/09 |
101 |
1005 |
C-789 |
3 |
2349 |
3/4/09 |
101 |
1005 |
C-795 |
6 |
2350 |
3/4/09 |
103 |
1003 |
A-2275 |
2 |
2350 |
3/4/09 |
103 |
1003 |
F-198 |
6 |
2350 |
3/4/09 |
103 |
1003 |
B-205 |
1 |
2351 |
3/4/09 |
105 |
1001 |
C-795 |
6 |
2352 |
3/5/09 |
105 |
1003 |
A-2275 |
2 |
2352 |
3/5/09 |
105 |
1003 |
D-4420 |
3 |
2353 |
3/7/09 |
107 |
1005 |
A-2275 |
6 |
2353 |
3/7/09 |
107 |
1005 |
C-789 |
5 |
Detalji porudžbine, kao što su ID proizvoda i količina, premeštaju se iz tabele "Porudžbine" i skladište se u tabeli pod imenom "Detalji porudžbine". Imajte na umu da postoji 9 porudžbina, pa imajte na umu da u ovoj tabeli postoji 9 zapisa. Imajte na umu da tabela "Porudžbine" ima jedinstveni ID (ID porudžbine) na koji se upućuje iz tabele "Detalji porudžbine".
Konačni dizajn tabele "Porudžbine" trebalo bi da izgleda ovako:
Porudžbine |
|||
---|---|---|---|
ID porudžbine |
Datum porudžbine |
ID prodavca |
ID kupca |
2349 |
3/4/09 |
101 |
1005 |
2350 |
3/4/09 |
103 |
1003 |
2351 |
3/4/09 |
105 |
1001 |
2352 |
3/5/09 |
105 |
1003 |
2353 |
3/7/09 |
107 |
1005 |
Tabela "Detalji porudžbine" ne sadrži kolone koje zahtevaju jedinstvene vrednosti (to jest, ne postoji primarni ključ), tako da je u redu da neke ili sve kolone sadrže "suvišne" podatke. Međutim, dva zapisa u ovoj tabeli ne bi trebalo da budu potpuno identična (ovo pravilo se primenjuje na bilo koju tabelu u bazi podataka). U ovoj tabeli bi trebalo da postoji 17 zapisa – svaki koji odgovara proizvodu pojedinačnom porudžbinom. Na primer, po redu 2349, tri proizvoda C-789 sačinjena su od dva dela cele porudžbine.
Stoga tabela "Detalji porudžbine" treba da izgleda ovako:
Detalji porudžbine |
||
---|---|---|
ID porudžbine |
ID proizvoda |
Kol |
2349 |
C-789 |
3 |
2349 |
C-795 |
6 |
2350 |
A-2275 |
2 |
2350 |
F-198 |
6 |
2350 |
B-205 |
1 |
2351 |
C-795 |
6 |
2352 |
A-2275 |
2 |
2352 |
D-4420 |
3 |
2353 |
A-2275 |
6 |
2353 |
C-789 |
5 |
Kopiranje i lepljenje podataka iz programa Excel u Access
Sada kada su informacije o prodavcima, klijentima, proizvodima, porudžbinama i detaljima porudžbine razbijene u zasebne teme u programu Excel, te podatke možete da kopirate direktno u Access, gde će postati tabele.
Kreiranje relacija između Access tabela i pokretanje upita
Kada premestite podatke u Access, možete da kreirate relacije između tabela, a zatim da kreirate upite da biste dobili informacije o različitim temama. Na primer, možete da kreirate upit koji daje ID porudžbine i imena prodavaca za porudžbine unete između 05.05.2009. i 08.3.2009.
Pored toga, možete da kreirate obrasce i izveštaje kako biste olakšali unos podataka i analizu prodaje.
Potrebna vam je dodatna pomoć?
Možete uvek da postavite pitanje stručnjaku u Excel Tech zajednici ili da potražite pomoć u Zajednicama.