Premještanje podataka iz programa Excel u Access

U ovom se članku prikazuje kako premjestiti podatke iz programa Excel u Access i pretvoriti ih u relacijske tablice da biste mogli koristiti Microsoft Excel i Access zajedno. Ukratko, Access je najbolji za hvatanje, pohranjivanje, postavljanje upita i zajedničko korištenje podataka, a Excel je najbolji za izračunavanje, analizu i vizualizaciju podataka.

U dva članka, Upravljanje podacima pomoću programa Access ili Excel i 10 glavnih razloga zašto koristiti Access s programom Excel, raspravlja se o tome koji je program najprikladniji za određeni zadatak i kako zajedno koristiti Excel i Access da biste stvorili praktično rješenje.

Kada premjestite podatke iz programa Excel u Access, postupak se sastoji od tri osnovna koraka.

1. korak: uvoz podataka iz programa Excel u Access

Uvoz podataka operacija je koja može proći mnogo jednostavnije ako odvojite malo vremena za pripremu i čišćenje podataka. Uvoz podataka je poput selidbe u novi dom. Ako očistite i organizirate svoju imovinu prije preseljenja, smjestiti se u svoj novi dom puno je lakše.

Čišćenje podataka prije uvoza

Prije uvoza podataka u Access u programu Excel preporučuje se sljedeće:

• Pretvaranje ćelija koje sadrže neatomske podatke (tj. više vrijednosti u jednoj ćeliji) u više stupaca. Ćelija u stupcu "Vještine", koja sadrži više vrijednosti vještina, kao što su "Programiranje u jeziku C#", "Programiranje u VBA" i "Web-dizajn", primjerice, trebala bi biti podijeljena u zasebne stupce od kojih svaki sadrži samo jednu vrijednost vještine.
• Pomoću naredbe TRIM uklonite početni, završni i višestruko ugrađeni razmak.
• Uklonite znakove koji se ne ispisuju.
• Pronađite i ispravite pravopisne i interpunkcijske pogreške.
• Uklonite duplicirane retke ili duplicirana polja.
• Pazite da stupci podataka ne sadrže mješovita oblikovanja, osobito brojeve oblikovane kao tekst ili datume oblikovane kao brojeve.

Dodatne informacije potražite u sljedećim temama pomoći za Excel:

• Deset najboljih načina za čišćenje podataka
• Filtriranje jedinstvenih vrijednosti i uklanjanje duplikata vrijednosti
• Pretvorba brojeva spremljenih kao tekst u brojeve
• Pretvaranje datuma pohranjenih kao tekst u datume

Odabir najbolje vrste podataka prilikom uvoza

Tijekom operacije uvoza u programu Access želite odabrati dobro odabire da biste dobili malo (ako uopće postoje) pogrešaka pretvorbe za koje će biti potrebna ručna intervencija. U sljedećoj je tablici prikazan način na koji se oblici brojeva i vrste podataka programa Excel pretvaraju kada uvozite podatke iz programa Excel u Access te nekoliko savjeta o najboljim vrstama podataka koje možete odabrati u čarobnjaku za uvoz proračunske tablice.

Oblik broja u programu Excel	Vrsta podataka programa Access	Komentari	Najbolja praksa
Tekst	Tekst, dopis	Vrsta podataka Tekst u programu Access pohranjuje alfanumeričke podatke duljine od 255 znakova. Vrsta podataka Access Memo pohranjuje alfanumeričke podatke do 65 535 znakova.	Odaberite Dopis da biste izbjegli rezanje podataka.
Broj, postotak, razlomak, znanstvena	Broj	Access sadrži vrstu podataka Broj koja ovisi o svojstvu veličine polja (Bajt, Cijeli broj, Dugi cijeli broj, Jednostruko, Dvostruko, Decimalno).	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. poslije Krista) do 2 958 465 (31. prosinca 9999.). Budući da Access ne prepoznaje datumski sustav 1904 (koji se koristi u programu Excel za Macintosh), morate pretvoriti datume u programu Excel ili Access da ne bi došlo do zabune. Dodatne informacije potražite u člancima Promjena sustava i oblika datuma ili tumačenja godine u dvije znamenke te Uvoz podataka u radnu knjigu programa Excel ili povezivanje s njima.	Odaberite Datum.
Time	Time	Access i Excel pohranjuju vremenske vrijednosti pomoću iste vrste podataka.	Odaberite vrijeme, koje je obično zadano.
Valuta, računovodstvo	Valuta	U programu Access vrsta podataka Valuta pohranjuje podatke u obliku 8-bajtnih brojeva s preciznošću na četiri decimalna mjesta i koristi se za pohranu financijskih podataka i onemogućivanje zaokruživanja vrijednosti.	Odaberite valutu, što je obično zadana postavka.
booleovski	Da/ne	Access koristi -1 za sve vrijednosti Da i 0 za sve vrijednosti Ne, dok Excel koristi 1 za sve vrijednosti TRUE, a 0 za sve vrijednosti FALSE.	Odaberite Da/Ne, čime će se automatski pretvoriti temeljne vrijednosti.
Hiperveza	Hiperveza	Hiperveza u programima Excel i Access sadrži URL ili web-adresu koju možete kliknuti i pratiti.	Odaberite Hiperveza jer će Access u suprotnom možda koristiti vrstu podataka Tekst po zadanom.

Kada se podaci nalaze u programu Access, možete ih izbrisati. Nemojte zaboraviti sigurnosno kopirati izvornu radnu knjigu programa Excel prije brisanja.

Dodatne informacije potražite u temi pomoći programa Access Uvoz podataka iz radne knjige programa Excel ili povezivanje s njima.

Jednostavno automatsko dodavanje podataka

Jedan od najčešćih problema s kojima se susreću korisnici programa Excel jest dodavanje podataka iz istih stupaca na jedan veliki radni list. Možda, primjerice, imate rješenje za praćenje imovine koje je započelo u programu Excel, a sada je prošireno i obuhvaća datoteke iz mnogih radnih grupa i odjela. Ti se podaci mogu nalaziti na različitim radnim listovima i knjigama ili u tekstnim datotekama koje su sažeci sadržaja podataka iz drugih sustava. Ne postoji naredba korisničkog sučelja ni jednostavan način dodavanja sličnih podataka u programu Excel.

Najbolje je rješenje korištenje programa Access u kojem možete jednostavno uvoziti i dodavati podatke u jednu tablicu pomoću čarobnjaka za uvoz proračunske tablice. Osim toga, u jednu tablicu možete dodati mnogo podataka. Operacije uvoza možete spremiti, dodati ih kao zakazane zadatke programa Microsoft Outlook, pa čak i koristiti makronaredbe za automatizaciju postupka.

Drugi korak: normalizacija podataka pomoću čarobnjaka za analizu tablica

Na prvi se pogled postupak normalizacije podataka može činiti zamornim zadatkom. Srećom, normalizacija tablica u programu Access postupak je koji je mnogo jednostavniji zahvaljujući čarobnjaku za analizu tablica.

1. Povucite odabrane stupce u novu tablicu i automatski stvorite odnose

2. Pomoću naredbi gumba preimenujte tablicu, dodajte primarni ključ, postojeći stupac pretvorite u primarni ključ i poništite zadnju radnju

Taj čarobnjak možete koristiti za 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 ID polje koje koristi vrstu podataka s automatskim numeriranjem.
• Automatski stvarajte odnose da biste nametnuli referencijalni integritet uz kaskadna ažuriranja. Kaskadna brisanja ne dodaju se automatski kao sprječavanje slučajnog brisanja podataka, ali možete naknadno jednostavno dodati kaskadna brisanja.
• Potražite nove tablice da biste pronašli suvišne ili duplicirane podatke (npr. o istom klijentu s dva različita telefonska broja) i ažurirajte ih po želji.
• Sigurnosno kopirajte izvornu tablicu i promijenite njezin naziv dodavanjem "_OLD". Zatim stvorite upit koji rekonstruira izvornu tablicu s njezinim nazivom tako da 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 analizatora tablica.

Treći korak: povezivanje s podacima programa Access iz programa Excel

Nakon normalizacije podataka u programu Access i stvaranja upita ili tablice koji rekonstruiraju izvorne podatke, potrebno je jednostavno povezati se s podacima programa Access iz programa Excel. Vaši se podaci sada nalaze u programu Access kao vanjski izvor podataka, pa se s radnom knjigom mogu povezati putem podatkovne veze, tj. spremnika informacija koji se koristi za pronalaženje vanjskog izvora podataka, prijavu na njega i pristupanje mu. Informacije o vezi pohranjuju se u radnu knjigu, a mogu se pohranjivati i u datoteku veze, primjerice datoteku podatkovne veze sustava Office (ODC) (datotečni nastavak .odc) ili datoteku naziva izvora podataka (nastavak .dsn). Kada se povežete s vanjskim podacima, možete i automatski osvježiti (ili ažurirati) radnu knjigu programa Excel iz programa Access kad god se podaci ažuriraju u programu Access.

Dodatne informacije potražite u članku Uvoz podataka iz vanjskih izvora podataka (Power Query)).

Prijenos podataka u Access

Ovaj vas odjeljak vodi kroz sljedeće faze normalizacije podataka: razdvajanje vrijednosti u stupcima Prodavač i Adresa na najsloženije dijelove, razdvajanje povezanih predmeta 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 podataka.

Ogledni podaci u nenormaliziranom obliku

Sljedeći radni list sadrži neatomske vrijednosti u stupcima Prodavač i Adresa. Oba je stupca potrebno podijeliti na dva ili više zasebnih stupaca. Taj radni list sadrži i podatke o prodavačima, proizvodima, kupcima i narudžbama. Te bi podatke trebalo i dodatno podijeliti po predmetu u zasebne tablice.

Prodavač	ID narudžbe	Datum narudžbe	ID proizvoda	Količina	Cijena	Ime klijenta	Adresa	Telefon
Li, Yale	2349	3/4/09	C-789	3	7,00 kn	Četiri ugla	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	9,75 kn	Četiri ugla	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	A-2275	2	$16.75	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	$5.25	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	4,50 kn	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	9,75 kn	Contoso, Ltd.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	A-2275	2	$16.75	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	Četiri ugla	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	7,00 kn	Četiri ugla	7007 Cornell St Redmond, WA 98199	425-555-0201

Informacije u najmanjim dijelovima: atomski podaci

Prilikom rada 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, država i poštanski broj) u zasebne stupce.

U sljedećoj su tablici prikazani novi stupci na istom radnom listu nakon što su podijeljeni da bi sve vrijednosti bile atomske. Imajte na umu da su podaci u stupcu Prodavač podijeljeni na stupce Prezime i Ime te da su podaci u stupcu Adresa podijeljeni u stupce Adresa, Grad, Država i Poštanski broj. Ovi su podaci u "prvom normalnom obliku".

Prezime	Ime	Ulica	Grad	Županija	Poštanski broj
Li	Yale	2302 Harvard Ave	Bellevue	Krapinsko-zagorska županija	98227
Adams	Ellen	1025 Kolumbijski krug	Pula	Krapinsko-zagorska županija	98234
Hance	Viktor	2302 Harvard Ave	Bellevue	Krapinsko-zagorska županija	98227
Kut	Reed	7007 Cornell St Redmond	Zagreb	Krapinsko-zagorska županija	98199

Razdvajanje podataka u organizirane predmete u programu Excel

Nekoliko tablica oglednih podataka koje slijede prikazuju iste podatke s radnog lista programa Excel nakon podjele na tablice za prodavače, proizvode, kupce i narudžbe. Dizajn tablice nije konačan, ali ide na dobrom putu.

Tablica Prodavači sadrži samo podatke 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 radi povezivanja narudžbi s prodavačima.

Prodavači
ID prodavača	Prezime	Ime
101	Li	Yale
103	Adams	Ellen
105	Hance	Viktor
107	Kut	Reed

Tablica Proizvodi sadrži samo informacije o proizvodima. Imajte na umu da svaki zapis ima jedinstveni ID (ID proizvoda). Vrijednost ID-ja proizvoda koristit će se za povezivanje informacija o proizvodu s tablicom Pojedinosti o narudžbi.

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 podatke o kupcima. Imajte na umu da svaki zapis ima jedinstveni ID (Customer ID). Vrijednost ID-a kupca koristit će se za povezivanje informacija o klijentu s tablicom Narudžbe.

Klijenti
ID kupca	Naziv	Ulica	Grad	Županija	Poštanski broj	Telefon
1001	Contoso, Ltd.	2302 Harvard Ave	Bellevue	Krapinsko-zagorska županija	98227	425-555-0222
1003	Adventure Works	1025 Kolumbijski krug	Pula	Krapinsko-zagorska županija	98234	425-555-0185
1005	Četiri ugla	7007 Cornell St	Zagreb	Krapinsko-zagorska županija	98199	425-555-0201

Tablica Narudžbe sadrži informacije o narudžbama, prodavačima, kupcima i proizvodima. Imajte na umu da svaki zapis ima jedinstveni ID (ID narudžbe). Neke informacije u toj tablici potrebno je podijeliti u dodatnu tablicu koja sadrži detalje o narudžbi 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 Pojedinosti narudžbe.

Narudžbe
ID narudžbe	Datum narudžbe	ID prodavača	ID kupca	ID proizvoda	Količina
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 o narudžbi, kao što su ID proizvoda i količina, premještaju se iz tablice Narudžbe i spremaju u tablicu pod nazivom Detalji narudžbe. Imajte na umu da se u tablici nalazi 9 narudžbi, stoga ima smisla da se u ovoj tablici nalazi 9 zapisa. Imajte na umu da tablica Narudžbe sadrži jedinstveni ID (ID narudžbe) koji će upućivati na tablicu Pojedinosti o narudžbi.

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 (tj. nema primarnog ključa), stoga može bilo koji ili svi stupci sadržavati "suvišne" podatke. No ne smiju dva zapisa u toj tablici biti potpuno identična (ovo se pravilo odnosi na sve tablice u bazi podataka). U toj bi tablici trebalo biti 17 zapisa — svaki odgovara pojedinačnoj narudžbi proizvoda Na primjer, u narudžbi 2349, tri proizvoda C-789 čine jedan od dva dijela cijele narudžbe.

Tablica Pojedinosti o narudžbi stoga bi trebala izgledati ovako:

Pojedinosti o narudžbi
ID narudžbe	ID proizvoda	Količina
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

Sad kada su podaci o prodavačima, kupcima, proizvodima, narudžbama i detaljima narudžbe podijeljeni u zasebne predmete u programu Excel, možete ih kopirati izravno u Access, gdje će postati tablice.

Stvaranje odnosa između tablica programa Access i pokretanje upita

Nakon što premjestite podatke u Access, možete stvoriti odnose između tablica, a zatim stvoriti upite za vraćanje informacija o različitim predmetima. Možete, primjerice, stvoriti upit koji vraća ID narudžbe i imena prodavača za narudžbe unesene između 5. 3. 2009. i 8. 3. 2009.

Osim toga, možete stvarati obrasce i izvješća da biste pojednostavnili unos podataka i analizu prodaje.

Je li vam potrebna dodatna pomoć?

Uvijek možete postaviti pitanje stručnjaku u tehničkoj zajednici za Excel ili zatražiti podršku u zajednicama.