Napomena: Microsoft Access ne podržava uvoz podataka programa Excel s primijenjenom oznakom osjetljivosti. Kao zaobilazno rješenje možete ukloniti oznaku prije uvoza, a zatim ponovno primijeniti oznaku nakon uvoza. Dodatne informacije potražite u članku Primjena oznaka osjetljivosti na datoteke i e-poštu u sustavu Office.
U ovom se članku objašnjava kako premjestiti podatke iz programa Excel u Access i pretvoriti podatke u relacijske tablice da biste mogli koristiti Microsoft Excel i Access zajedno. Da biste saželi sažetak, Access je najbolji za snimanje, pohranu, slanje upita i zajedničko korištenje podataka, a Excel je najbolji za izračun, analizu i vizualizaciju podataka.
Dva članka, Korištenje programa Access ili Excel za upravljanje podacima i 10 najboljih razloga za korištenje programa Access s programom Excel, rasprava o tome koji je program najprikladniji za određeni zadatak i kako koristiti Excel i Access zajedno da biste stvorili praktično rješenje.
Kada premjestite podatke iz programa Excel u Access, postupak se sastoji od tri osnovna koraka.
Napomena: Informacije o modeli i odnosima podataka u programu Access potražite u članku Osnove dizajna baza podataka.
Prvi korak: uvoz podataka iz programa Excel u Access
Uvoz podataka operacija je koja može ići mnogo jednostavnije ako vam je potrebno neko vrijeme za pripremu i čišćenje podataka. Uvoz podataka je poput premještanja na novu početnu stranicu. Ako očistite i organizirate svoje posjede prije premještanja, lakše ćete se smještati u novi dom.
Čišćenje podataka prije uvoza
Prije uvoza podataka u Access dobro je u programu Excel učiniti sljedeće:
-
Pretvorite ćelije koje sadrže neatomske podatke (to jest, više vrijednosti u jednoj ćeliji) u više stupaca. Primjerice, ćeliju u stupcu "Vještine" koja sadrži više vrijednosti vještina, kao što je "C#programiranje", "VBA programiranje" i "Web-dizajn" trebalo bi izošteti da bi se razdvoji stupci koji sadrže samo jednu vrijednost vještine.
-
Pomoću naredbe TRIM uklonite početne, završne i više ugrađenih razmaka.
-
Uklonite znakove koji se ne ispisuju.
-
Pronađite i ispravite pogreške pravopisa i interpunkcije.
-
Uklonite duplicirane retke ili duplicirana polja.
-
Provjerite ne sadrže li stupci podataka mješovita oblikovanja, osobito brojevi oblikovani kao tekst ili datumi oblikovani kao brojevi.
Dodatne informacije potražite u sljedećim temama pomoći za Excel:
Napomena: Ako su vaše potrebe za čišćenjem podataka složene ili nemate vremena ili resursa za automatiziranje postupka sami, razmislite o korištenju drugog proizvođača. Dodatne informacije potražite u odjeljku "Softver za čišćenje podataka" ili "kvaliteta podataka" koju koristi vaša omiljena tražilica u web-pregledniku.
Odabir najbolje vrste podataka prilikom uvoza
Tijekom operacije uvoza u programu Access želite odabrati nekoliko pogrešaka pretvorbe koje će zahtijevati ručnu intervenciju. U sljedećoj se tablici opisuje kako se oblici brojeva programa Excel i vrste podataka programa Access pretvaraju prilikom uvoza podataka iz programa Excel u Access te se nude savjeti za najbolje vrste podataka koje možete odabrati u čarobnjaku za uvoz proračunskih tablica.
|
Oblik broja programa Excel |
Vrsta podataka programa Access |
Komentari |
Najbolja praksa |
|---|---|---|---|
|
Tekst |
Tekst, dopis |
Vrsta podataka Tekst programa Access pohranjuje alfanumeričke podatke do 255 znakova. Vrsta podataka Dopis programa Access pohranjuje alfanumeričke podatke do 65 535 znakova. |
Odaberite Dopis da biste izbjegli odbacujući podatke. |
|
Broj, postotak, razlomak, znanstveno |
Broj |
Access sadrži jednu vrstu podataka Broj koja se razlikuje ovisno o svojstvu Veličina polja (Bajt, Cijeli broj, Dugi cijeli broj, Jednostruki, Dvostruki, Decimalni). |
Odaberite Dvostruko da biste izbjegli pogreške pri pretvorbi podataka. |
|
Date |
Datum |
Access i Excel koriste isti serijski broj datuma za pohranu datuma. U programu Access raspon datuma je veći: od -657 434 (1. siječnja 100. A.D.) do 2958.465 (31. prosinca 9999. A.D.). Budući da Access ne prepoznaje datumski sustav verzije 1904 (koristi se u programu Excel za Macintosh), morate pretvoriti datume u programu Excel ili Access da biste izbjegli zbrku. Dodatne informacije potražite u člancima Promjena datuma, oblika ili dvoznamenkastog tumačenja godine i Uvoz podataka u radnoj knjizi programa Excel ili povezivanje s njima. |
Odaberite Datum. |
|
Time |
Time |
Access i Excel pohranjuju vrijednosti vremena pomoću iste vrste podataka. |
Odaberite Vrijeme, što je obično zadano. |
|
Valuta, računovodstvo |
Valuta |
U programu Access vrsta podataka Valuta pohranjuje podatke kao 8-bajtne brojeve s preciznošću do četiri decimalna mjesta, a koristi se za pohranu financijskih podataka i sprječavanje zaokruživanja vrijednosti. |
Odaberite Valuta, što je obično zadano. |
|
booleovski |
Da/ne |
Access koristi -1 za sve vrijednosti Da i 0 za sve vrijednosti Ne, dok Excel koristi 1 za sve TRUE vrijednosti i 0 za sve FALSE vrijednosti. |
Odaberite Da/Ne, čime se automatski pretvaraju temeljne vrijednosti. |
|
Hiperveza |
Hiperveza |
Hiperveza u programima Excel i Access sadrži URL ili web-adresu koju možete kliknuti i pratiti. |
Odaberite Hiperveza, u suprotnom Access po zadanom može koristiti tekstnu vrstu podataka. |
Kada podaci otvorite Access, možete izbrisati podatke programa Excel. Nemojte zaboraviti najprije sigurnosno kopiranje izvorne radne knjige programa Excel prije brisanja.
Dodatne informacije potražite u temi pomoći programa Access Uvoz podataka u radnoj knjizi programa Excel ili povezivanje s njima.
Automatsko dodavanje podataka na jednostavan način
Uobičajeni problem koji korisnici programa Excel imaju jest dodavanje podataka s istim stupcima na jedan veliki radni list. Na primjer, možda imate rješenje za praćenje imovine koje je započelo u programu Excel, ali sada je naraslo na obuhvaćanje datoteka iz mnogih radnih grupa i odjela. Ti se podaci mogu nalaziti u različitim radnim listovima i radnim knjigama ili u tekstnim datotekama koje su sažeci sadržaja podataka iz drugih sustava. Ne postoji naredba korisničkog sučelja ili jednostavan način dodavanja sličnih podataka u programu Excel.
Najbolje je rješenje koristiti Access, gdje možete jednostavno uvesti i dodati podatke u jednu tablicu pomoću čarobnjaka za uvoz proračunskih tablica. Osim toga, u jednu tablicu možete dodati mnogo podataka. Možete spremiti operacije uvoza, dodati ih kao zakazane zadatke programa Microsoft Outlook, pa čak i koristiti makronaredbe da biste automatizirali postupak.
Drugi korak: normalizacija podataka pomoću čarobnjaka za analizu tablica
Na prvi pogled, korak kroz postupak normalizacije podataka može se činiti zastrašujućim zadatkom. Srećom, normalizacija tablica u programu Access mnogo je jednostavniji postupak zahvaljujući čarobnjaku za analizu tablica.
1. Povucite odabrane stupce u novu tablicu i automatski stvorite odnose
2. Koristite naredbe gumba za preimenovanje tablice, dodavanje primarnog ključa, postavljanje postojećeg stupca kao primarnog ključa i poništavanje zadnje akcije
Pomoću ovog čarobnjaka možete učiniti sljedeće:
-
Pretvorite tablicu u skup manjih tablica i automatski stvorite odnos primarnog i vanjskog ključa između tablica.
-
Dodajte primarni ključ u postojeće polje koje sadrži jedinstvene vrijednosti ili stvorite novo polje ID-a koje koristi vrstu podataka Automatsko numeriranje.
-
Automatski stvorite odnose da biste referencijalni integritet nametnuli kaskadnim ažuriranjima. Kaskadna brisanja ne dodaju se automatski da bi se spriječilo slučajno brisanje podataka, ali kasnije možete jednostavno dodati kaskadna brisanja.
-
U novim tablicama potražite suvišne ili duplicirane podatke (kao što su isti klijent s dva različita telefonska broja) i ažurirajte ih po želji.
-
Sigurnosno kopirajte izvornu tablicu i preimenujte je dodavanjem naziva "_OLD". Zatim stvorite upit koji rekonstruiraju izvornu tablicu s izvornim nazivom tablice tako da svi postojeći obrasci ili izvješća utemeljeni na izvornoj tablici funkcioniraju s novom strukturom tablice.
Dodatne informacije potražite u članku Normalizacija podataka pomoću alata za analizu tablica.
Treći korak: povezivanje s podacima programa Access iz programa Excel
Nakon normaliziranja podataka u programu Access i stvaranja upita ili tablice koji rekonstruiraju izvorne podatke, jednostavno je povezati se s podacima programa Access iz programa Excel. Podaci se sada nalaze u programu Access kao vanjski izvor podataka, pa se mogu povezati s radnom knjigom putem podatkovne veze, što je spremnik informacija koji se koristi za pronalaženje, prijavu u vanjski izvor podataka i pristup vanjskom izvoru podataka. Podaci o vezi pohranjuju se u radnu knjigu i mogu se pohraniti i u datoteci za povezivanje, kao što je datoteka podatkovne veze sustava Office (ODC) (datotečni nastavak.odc) ili datoteka naziva izvora podataka (.dsn nastavak). Kada se povežete s vanjskim podacima, možete i automatski osvježiti (ili ažurirati) radnu knjigu programa Excel iz programa Access prilikom svakog ažuriranja podataka u programu Access.
Dodatne informacije potražite u članku Uvoz podataka iz vanjskih izvora podataka (Power Query).
Dohvaćanje podataka u Access
Ovaj odjeljak vodi vas kroz sljedeće faze normalizacije podataka: prekidanje vrijednosti u stupcima Prodavač i Adresa u svoje najautomatičnije dijelove, razdvajanje povezanih subjekata u vlastite tablice, kopiranje i lijepljenje tih tablica iz programa Excel u Access, stvaranje ključnih odnosa između novostvorenih tablica programa Access te stvaranje i pokretanje jednostavnog upita u programu Access radi vraćanja informacija.
Ogledni podaci u obliku koji nije normaliziran
Sljedeći radni list sadrži neautomatske vrijednosti u stupcu Prodavač i u stupcu Adresa. Oba stupca moraju biti podijeljena u dva ili više zasebnih stupaca. Ovaj radni list sadrži i informacije o prodavačima, proizvodima, klijentima i narudžbama. Te bi podatke trebalo dodatno podijeliti, prema predmetu, u zasebne tablice.
|
Prodavač |
ID narudžbe |
Datum narudžbe |
ID proizvoda |
Kol |
Cijena |
Ime klijenta |
Adresa |
Telefon |
|---|---|---|---|---|---|---|---|---|
|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 USD |
Četiri ugla |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Četiri ugla |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Adams, Ellen |
2350 |
3/4/09 |
A – 2275 |
2 |
16,75 dolara |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
5,25 dolara |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Adams, Ellen |
2350 |
3/4/09 |
B - 205 |
1 |
4,50 USD |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 USD |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
|
Hance, Jim |
2352 |
3/5/09 |
A – 2275 |
2 |
16,75 dolara |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
7,25 dolara |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
|
Koch, Reed |
2353 |
3/7/09 |
A – 2275 |
6 |
16,75 dolara |
Četiri ugla |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
|
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 USD |
Četiri ugla |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informacije u najmanjim dijelovima: atomski podaci
Rad s podacima u ovom primjeru pomoću naredbe Tekst u stupac u programu Excel možete razdvojiti "atomske" dijelove ćelije (kao što su adresa, grad, savezna država i poštanski broj) u zasebne stupce.
U sljedećoj su tablici prikazani novi stupci na istom radnom listu nakon podjele da bi sve vrijednosti bilo atomsko. Imajte na umu da su podaci u stupcu Prodavač podijeljeni na stupce Prezime i Ime te da su informacije u stupcu Adresa podijeljene u stupce Adresa, Grad, Država i Poštanski broj. Ti su podaci u "prvom običnom obliku".
|
Prezime |
Ime |
|
kućnu adresu |
Grad |
Županija |
Poštanski broj |
|---|---|---|---|---|---|---|
|
Li |
Yale |
2302 Harvard Ave |
Bellevue |
Krapinsko-zagorska županija |
98227 |
|
|
Adams |
Ellen |
1025 Columbia Circle |
Pula |
Krapinsko-zagorska županija |
98234 |
|
|
Hance |
Viktor |
2302 Harvard Ave |
Bellevue |
Krapinsko-zagorska županija |
98227 |
|
|
Koch |
Reed |
7007 Cornell St Redmond |
Zagreb |
Krapinsko-zagorska županija |
98199 |
Razlamanje podataka u organizirane teme u programu Excel
Nekoliko tablica oglednih podataka koji slijede prikazuju iste podatke s radnog lista programa Excel nakon što su podijeljeni u tablice za prodavače, proizvode, klijente i narudžbe. Dizajn tablice nije konačan, ali je na pravom planu.
Tablica Prodavači sadrži samo informacije o prodajnom osoblju. Imajte na umu da svaki zapis ima jedinstveni ID (ID prodavača). Vrijednost ID-a prodavača koristit će se u tablici Narudžbe za povezivanje narudžbi s prodavačima.
|
Prodavači |
||
|---|---|---|
|
ID prodavača |
Prezime |
Ime |
|
101 |
Li |
Yale |
|
103 |
Adams |
Ellen |
|
105 |
Hance |
Viktor |
|
107 |
Koch |
Reed |
Tablica Proizvodi sadrži samo informacije o proizvodima. Imajte na umu da svaki zapis ima jedinstveni ID (ID proizvoda). Vrijednost ID-a proizvoda koristit će se za povezivanje informacija o proizvodu s tablicom Detalji narudžbe.
|
Proizvodi |
|
|---|---|
|
ID proizvoda |
Cijena |
|
A – 2275 |
16.75 |
|
B - 205 |
4.50 |
|
C-789 |
7.00 |
|
C-795 |
9.75 |
|
D-4420 |
7.25 |
|
F-198 |
5.25 |
Tablica Kupci sadrži samo informacije o klijentima. Imajte na umu da svaki zapis ima jedinstveni ID (ID kupca). Vrijednost ID-a kupca koristit će se za povezivanje podataka o klijentu s tablicom Narudžbe.
|
Klijenti |
||||||
|---|---|---|---|---|---|---|
|
ID kupca |
Naziv |
kućnu adresu |
Grad |
Županija |
Poštanski broj |
Telefon |
|
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Bellevue |
Krapinsko-zagorska županija |
98227 |
425-555-0222 |
|
1003 |
Adventure Works |
1025 Columbia Circle |
Pula |
Krapinsko-zagorska županija |
98234 |
425-555-0185 |
|
1005 |
Četiri ugla |
St. 7007 Cornell |
Zagreb |
Krapinsko-zagorska županija |
98199 |
425-555-0201 |
Tablica Narudžbe sadrži informacije o narudžbama, prodavačima, klijentima i proizvodima. Imajte na umu da svaki zapis ima jedinstveni ID (ID narudžbe). Neke informacije u ovoj tablici potrebno je podijeliti u dodatnu tablicu koja sadrži pojedinosti narudžbe tako da tablica Narudžbe sadrži samo četiri stupca – jedinstveni ID narudžbe, datum narudžbe, ID prodavača i ID kupca. Ovdje prikazana tablica još nije podijeljena u tablicu Detalji narudžbe.
|
Narudžbe |
|||||
|---|---|---|---|---|---|
|
ID narudžbe |
Datum narudžbe |
ID prodavača |
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 |
Pojedinosti narudžbe, kao što su ID proizvoda i količina, premještaju se iz tablice Narudžbe i pohranjuju u tablicu s nazivom Detalji narudžbe. Imajte na umu da postoji 9 narudžbi, pa ima smisla da u ovoj tablici postoji 9 zapisa. Imajte na umu da tablica Narudžbe ima jedinstveni ID (ID narudžbe), na koji će se odnositi iz tablice Detalji narudžbe.
Konačni dizajn tablice Narudžbe trebao bi izgledati ovako:
|
Narudžbe |
|||
|---|---|---|---|
|
ID narudžbe |
Datum narudžbe |
ID prodavača |
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 |
Tablica Detalji narudžbe ne sadrži stupce za koje su potrebne jedinstvene vrijednosti (odnosno nema primarnog ključa), pa je u redu da svi stupci sadrže "suvišne" podatke. Međutim, dva zapisa u ovoj tablici ne bi trebala biti potpuno identična (to se pravilo primjenjuje na bilo koju tablicu u bazi podataka). U ovoj bi tablici trebalo biti 17 zapisa – svaki koji odgovara proizvodu pojedinačnim redoslijedom. Primjerice, redoslijedom 2349, tri proizvoda C-789 sastoje se od jednog od dva dijela cijelog redoslijeda.
Tablica Detalji narudžbe trebala bi stoga izgledati ovako:
|
Pojedinosti o narudžbi |
||
|---|---|---|
|
ID narudžbe |
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 lijepljenje podataka iz programa Excel u Access
Sada kada su informacije o prodavačima, klijentima, proizvodima, narudžbama i pojedinostima o narudžbi podijeljene u zasebne teme u programu Excel, te podatke možete kopirati izravno u Access, gdje će postati tablice.
Stvaranje odnosa između tablica programa Access i pokretanje upita
Kada premjestite podatke u Access, možete stvoriti odnose između tablica, a zatim stvarati upite da biste vratili informacije o raznim predmetima. Možete, primjerice, stvoriti upit koji vraća ID narudžbe i nazive prodavača za narudžbe unesene između 05. 3. 3. 2009. i 3. 8. 2009.
Osim toga, možete stvarati obrasce i izvješća da biste pojednostavnili unos podataka i analizu prodaje.
Treba li vam dodatna pomoć?
Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.
