U ovom se članku pokazuje kako premjestiti podatke iz programa Excel Access i pretvoriti podatke u relacijske tablice da biste mogli koristiti Microsoft Excel Access zajedno. Da biste saželi sažetak, Access je najbolji za snimanje, pohranu, slanje upita i zajedničko korištenje podataka, Excel najbolje je za izračun, analizu i vizualizaciju podataka.

Dva članka, korištenje programa Access ili Excel za upravljanje podacima i deset najboljih razloga za korištenje programa Access s programom Excel, raspravlja 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 podatke premjestite iz Excel u Access, postupak se temelji na tri osnovna koraka.

three basic steps

Napomena: Informacije o modeli i odnosima podataka u programu Access potražite u članku Osnove dizajna baze podataka.

Prvi korak: uvoz podataka iz programa Excel u Access

Uvoz podataka operacija je koja može biti mnogo omeđena ako vam treba neko vrijeme da pripremite i očistite podatke. Uvoz podataka je poput premještanja na novu kuću. Ako očistite i organizirate imovinu prije nego što se pomaknete, lakše ćete se smještati u novi dom.

Čišćenje podataka prije uvoza

Prije uvoza podataka u Access u Excel bilo bi dobro:

  • Pretvorite ćelije koje sadrže neatomske podatke (to jest više vrijednosti u jednoj ćeliji) u više stupaca. Na primjer, ćelija u stupcu "Vještine" koja sadrži više vrijednosti vještina, kao što je "C# programiranje", "VBA programiranje" i "web-dizajn" trebali bi biti podijeljeni na zasebne stupce koji sadrže samo jednu vrijednost vještina.

  • Pomoću naredbe TRIM uklonite početne, prateće i više ugrađenih razmaka.

  • Uklonite znakove koji se ne ispisuju.

  • Pronađite i ispravite pogreške u pravopisu i interpunkcijskim interpunkcijskim znakovima.

  • Uklonite duplicirane retke ili duplicirane polja.

  • Provjerite 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 Excel pomoći:

Napomena: Ako su vaše potrebe za čišćenjem podataka složene ili nemate vremena ni resursa za automatizaciju postupka sami, razmislite o korištenju proizvođača treće strane. Dodatne informacije potražite u "softveru za čišćenje podataka" ili "kvaliteti podataka" od strane omiljene tražilice u web-pregledniku.

Odabir najbolje vrste podataka prilikom uvoza

Tijekom operacije uvoza u programu Access želite donijeti dobre odluke da biste primili nekoliko pogrešaka pretvorbe (ako ih ima) koje će zahtijevati ručnu intervenciju. U sljedećoj su tablici sažeti Excel oblici brojeva i vrste podataka programa Access prilikom uvoza podataka iz programa Excel u Access te se nude savjeti o najboljim vrstama podataka koje možete odabrati u čarobnjaku za uvoz proračunskih tablica.

Excel oblik broja

Vrsta podataka programa Access

Komentari

Najbolja praksa

Tekst

Tekst, dopis

Vrsta podataka Tekst programa Access pohranjuje alfanumeričke podatke do 255 znakova. Vrsta podataka dopisa programa Access pohranjuje alfanumeričke podatke do 65 535 znakova.

Odaberite Dopis da biste izbjegli odsjećivanje podataka.

Broj, postotak, razlomak, znanstvena

Broj

Access ima jednu vrstu podataka Broj koja se razlikuje ovisno o svojstva Veličina polja (bajt, cijeli broj, dugi cijeli broj, jednostruki, dvostruki, decimalni).

Odaberite Dvostruko da biste izbjegli pogreške pretvorbe podataka.

Date

Datum

Access i Excel koriste isti serijski broj datuma za pohranu datuma. U programu Access raspon datuma veći je: od -657 434 (1. siječnja 100. A.D.) do 2958 465 (31. prosinca 9999.).

Budući da Access ne prepoznaje datumski sustav 1904 (koristi se u programu Excel za Macintosh), morate pretvoriti datume u programu Excel access da biste izbjegli zbrku.

Dodatne informacije potražite u članku Promjena datumskog sustava, oblikovanje ili dvoznamenkasto tumačenje godine te Uvoz ili povezivanje s podacima u radnoj Excel radnoj knjizi.

Odaberite Datum.

Time

Time

Pristup i Excel vrijednosti vremena pohrane 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 Bez, dok Excel koristi 1 za sve VRIJEDNOSTI TRUE i 0 za sve VRIJEDNOSTI FALSE.

Odaberite Da/Ne, koji automatski pretvara temeljne vrijednosti.

Hiperveza

Hiperveza

Hiperveza u Excel access sadrži URL ili web-adresu koju možete kliknuti i pratiti.

Odaberite Hiperveza, u suprotnom Access po zadanom može koristiti vrstu podataka Tekst.

Kada se podaci u programu Access ugnjete, možete izbrisati Excel podataka. Nemojte zaboraviti najprije sigurnosno kopirati izvornu Excel prije brisanja.

Dodatne informacije potražite u temi pomoći programa Access Uvoz ili povezivanje s podacima u radnoj Excel.

Automatsko dodavanje podataka na jednostavan način

Uobičajeni problem koji Excel korisnicima jest dodavanje podataka s istim stupcima na jedan veliki radni list. Možda, primjerice, imate rješenje za praćenje imovine koje je započelo u programu Excel ali sada je naraslo tako da obuhvaća datoteke iz mnogih radnih grupa i odjela. Ti podaci mogu biti na različitim radnim listovima i radnim knjigama ili u tekstnim datotekama koje su sažeci podataka iz drugih sustava. Nema naredbe korisničkog sučelja ni jednostavnog načina za dodavanje sličnih podataka u 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. Operacije uvoza možete spremiti, 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 proces 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. Pomoću naredbi gumba preimenujte tablicu, dodajte primarni ključ, učinite postojeći stupac primarnim ključem i poništite zadnju akciju

Pomoću ovog čarobnjaka možete učiniti sljedeće:

  • Pretvorite tablicu u skup manjih tablica i automatski stvorite odnos primarnog i stranog 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 Samonumeriranje.

  • Automatski stvorite odnose da biste referencijalni integritet nametnuti 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 korisnik s dva različita telefonska broja) i ažurirajte ih po želji.

  • Vratite izvornu tablicu i preimenujte je dodavanjem "_OLD" nazivu. Zatim stvorite upit koji rekonstruira izvornu tablicu s izvornim nazivom tablice da bi svi postojeći obrasci ili izvješća utemeljeni na izvornoj tablici funkcionirali s novom strukturom tablice.

Dodatne informacije potražite u članku Normalizacija podataka pomoću alata za analizu tablica.

Treći korak: Povezivanje pristup podacima programa Access iz Excel

Kada se podaci normaliziraju u programu Access, a stvori se upit ili tablica koja rekonstruizira izvorne podatke, jednostavno je povezati se s podacima programa Access iz Excel. Vaši se podaci sada nalaze u programu Access kao vanjski izvor podataka, pa se mogu povezati s radnom knjigom putem podatkovne veze, što je spremnik podataka koji se koristi za pronalaženje, prijavu i pristup vanjskom izvoru podataka. Podaci o vezi pohranjuju se u radnu knjigu i mogu se pohraniti i u datoteku za povezivanje, npr. datoteku Office Data Connection (ODC) (datotečni nastavak.odc) ili datoteku naziva izvora podataka (.dsn nastavak). Kada se povežete s vanjskim podacima, možete i automatski osvježiti (ili ažurirati) radnu Excel iz programa Access svaki put kada se podaci ažuriraju u programu Access.

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

Unos podataka u Access

U ovom ćete odjeljku proći kroz sljedeće faze normalizacije podataka: razdvajanje vrijednosti u stupcima Prodavač i Adresa u njihove 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 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. Ti bi se podaci trebali dodatno podijeliti, prema 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 USD

Četiri ugla

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams, Ellen

2350

3/4/09

A-2275

2

16,75 KN

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, Ellen

2350

3/4/09

F-198

6

5,25 kn

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 USD

Contoso, Ltd.

2302 Harvard Ave Bellevue, WA 98227

425-555-0222

Hance, Jim

2352

3/5/09

A-2275

2

16,75 KN

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2352

3/5/09

D-4420

3

7,25 kn

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Koch, Reed

2353

3/7/09

A-2275

6

16,75 KN

Č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

Rad s podacima u ovom primjeru možete koristiti naredbu Tekst u stupac u programu Excel da biste odvojili "atomske" dijelove ćelije (npr. adresu, grad, državu i poštanski broj) u diskretne stupce.

U sljedećoj su tablici prikazani novi stupci na istom radnom listu nakon što su podijeljeni da bi sve vrijednosti bilo atomsko. 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. Ti su podaci u "prvom normalnom 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 predmete u Excel

Nekoliko tablica oglednih podataka koji slijede prikazuju iste podatke s radnog lista Excel nakon što su podijeljeni u tablice za prodavače, proizvode, klijente i narudžbe. Dizajn tablice nije konačan, ali je na pravom 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 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 proizvoda koristit će se za povezivanje podataka 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 (Customer ID). Vrijednost ID-a klijenta 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

7007 Cornell St

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 podatke u ovoj tablici potrebno je podijeliti u dodatnu tablicu koja sadrži detalje 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

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

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 pozivati 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. No dva zapisa u ovoj tablici ne bi trebala biti potpuno identična (to se pravilo odnosi na bilo koju tablicu u bazi podataka). U ovoj bi tablici trebalo biti 17 zapisa – od kojih svaki odgovara proizvodu pojedinačnom narudžbom. Primjerice, u redoslijedu 2349, tri proizvoda C-789 sastoje se od dva dijela cijele narudžbe.

Tablica Detalji narudžbe stoga bi trebala izgledati ovako:

Detalji narudžbe

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

Sada kada su podaci o prodavačima, klijentima, proizvodima, narudžbama i narudžbi podijeljeni u zasebne predmete 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 temama. Možete, primjerice, stvoriti upit koji vraća ID narudžbe i nazive prodavača za narudžbe unesene između 3.05.09. i 3. 08.09.

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

Je li vam potrebna dodatna pomoć?

Postavite pitanje stručnjaku u tehničkoj zajednici za Excel, zatražite podršku u zajednici za odgovore ili predložite novu značajku ili poboljšanje na forumu za Excel User Voice.

Potrebna vam je dodatna pomoć?

Proširite svoje vještine
Istražite osposobljavanje
Prvi koristite nove značajke
Pridružite se Microsoft Insidere

Jesu li ove informacije bile korisne?

Koliko ste zadovoljni kvalitetom prijevoda?
Što je utjecalo na vaše iskustvo?

Hvala vam na povratnim informacijama!

×