Premeštanje podataka iz programa Excel u Access

Ovaj članak vam pokazuje kako da premestite podatke iz programa Excel u Access i konvertujete ih u relacione tabele kako biste mogli koristiti Microsoft Excel i Access zajedno. Da rezimiramo, Access je najbolji za hvatanje, skladištenje, izvršavanje upita i deljenje podataka, a Excel je najbolji za izračunavanje, analiziranje i vizuelizaciju podataka.

U dva članka, Korišćenje programa Access ili Excel za upravljanje podacima i 10 najvažnijih razloga za korišćenje programa Access sa programom Excel, govore se o tome koji program je najprikladniji za određeni zadatak i kako da koristite Excel i Access zajedno da biste pronašli praktično rešenje.

Kada premeštate podatke iz programa Excel u Access, postoje tri osnovna koraka procesa.

1. korak: Uvoz podataka iz programa Excel u Access

Uvoz podataka je operacija koja može da prođe mnogo glatko ako odvojite malo vremena da pripremite i očistite podatke. Uvoz podataka je poput preseljenja u novi dom. Ako očistite i organizujete svoju imovinu pre nego što se preselite, naseljavanje u vašem novom domu je mnogo lakše.

Čišćenje podataka pre uvoza

Pre nego što uvezete podatke u Access, u programu Excel bi trebalo da:

• Konvertovanje ćelija koje sadrže neatomske podatke (to jest, više vrednosti u jednoj ćeliji) u više kolona. Na primer, ćelija u koloni "Veštine" koja sadrži više vrednosti veština, kao što su "C# programiranje", "VBA programiranje" i "Veb dizajn", trebalo bi da bude podeljena u odvojene kolone od kojih svaka sadrži samo jednu vrednost veštine.
• Koristite komandu TRIM da biste uklonili ugrađene razmake na početku, kraju i više njih.
• Uklanjanje znakova koji neće biti odštampani.
• Pronađite i ispravite pravopisne greške i greške interpunkcije.
• Uklonite duplirane redove ili duplirana polja.
• Uverite se da kolone sa podacima ne sadrže mešovite formate, posebno brojeve oblikovane kao tekst ili datume oblikovane kao brojeve.

Više informacija potražite u sledećim temama pomoći za Excel:

• Deset najvažnijih načina za pospremanje podataka
• Filtriranje u cilju pronalaženja jedinstvenih vrednosti i uklanjanje dupliranih vrednosti
• Konvertovanje brojeva uskladištenih kao tekst u brojeve
• Konvertovanje datuma uskladištenih kao tekst u datume

Odaberite najbolji tip podataka prilikom uvoza

Tokom operacije uvoza u programu Access želite da napravite dobre izbore kako biste dobili nekoliko (ako ih ima) grešaka u konverziji koje će zahtevati 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 pruža neke savete o najboljim tipovima podataka koje možete odabrati u čarobnjaku za uvoz unakrsnih tabela.

Format broja u programu Excel	Access tip podataka	Komentari	Najbolja praksa
Tekst	Tekst, memorandum	Tip podataka Access tekst skladišti alfanumeričke podatke sa najviše 255 znakova. Tip podataka Access Memo skladišti alfanumeričke podatke sa najviše 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 na osnovu svojstva "Veličina polja" ("Bajt", "Ceo broj", "Dugački ceo broj", "Jednostruki", "Dvostruki", "Decimalni").	Odaberite "Duplo" da biste izbegli greške u konverziji 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. A.D.) do 2.958.465 (31. decembar 9999. A.D.). Budući da 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. Dodatne informacije potražite u člancima "Promena datumskog sistema, formata ili dvocifrenog tumačenja godine" i "Uvoz podataka iz Excel radne sveske ili povezivanje sa njima".	Odaberite datum.
Vreme	Time	Access i Excel skladište vrednosti vremena pomoću istog tipa podataka.	Odaberite vreme, što je obično podrazumevano.
Valuta, Računovodstvo	Valuta	U programu Access, tip podataka "Valuta" skladišti podatke kao brojeve od 8 bajtova sa preciznošću na četiri decimalna mesta i koristi se za skladištenje finansijskih podataka i sprečavanje zaokruživanja vrednosti.	Odaberite valutu, koja je obično podrazumevana.
Bulov	Da/ne	Access koristi -1 za sve vrednosti "Da" i 0 za sve vrednosti "Ne", dok Excel koristi 1 za sve vrednosti TRUE i 0 za sve vrednosti FALSE.	Odaberite opciju "Da/ne" koja automatski pretvara 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 će podrazumevano koristiti tekstualni tip podataka.

Kada podaci budu u programu Access, možete izbrisati Excel podatke. Ne zaboravite da napravite rezervnu kopiju originalne Excel radne sveske pre nego što je izbrišete.

Za više informacija pogledajte temu pomoći programa Access: Uvoz podataka ili povezivanje sa njima u Excel radnoj svesci.

Automatsko dodavanje podataka na jednostavan način

Uobičajeni problem sa kojim se susreću korisnici programa Excel je 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, a sada je postalo široko i obuhvata datoteke iz mnogih radnih grupa i odeljenja. Ti podaci mogu biti u različitim radnim listovima i radnim sveskama ili u tekstualnim datotekama koje predstavljaju feedove podataka iz drugih sistema. Ne postoji komanda korisničkog interfejsa niti jednostavan način za dodavanje sličnih podataka u programu Excel.

Najbolje rešenje je da koristite Access gde možete lako 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: normalizovanje podataka pomoću čarobnjaka za analizator tabele

Na prvi pogled, prolazak kroz proces normalizacije podataka može izgledati kao obeshrabrujući zadatak. Srećom, normalizacija tabela u programu Access je proces koji je mnogo lakši zahvaljujući čarobnjaku za analizator tabele.

1. Prevlačenje izabranih kolona u novu tabelu i automatsko kreiranje relacija

2. Korišćenje komandi dugmadi za preimenovanje tabele, dodavanje primarnog ključa, pretvaranje postojeće kolone u primarni ključ i opoziv poslednje radnje

Ovaj čarobnjak možete da koristite za 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 napravite novo polje "ID" koje koristi tip podataka "Automatsko numerisanje".
• Automatsko kreiranje relacija da biste nametnuli referencijalni integritet sa kaskadnim ažuriranjima. Kaskadna brisanja se ne dodaju automatski radi sprečavanja slučajnog brisanja podataka, ali kaskadna brisanja možete lako dodati kasnije.
• Pretražite nove tabele za suvišne ili duplirane podatke (kao što je isti klijent sa dva različita broja telefona) i ažurirajte ih po želji.
• Napravite rezervnu kopiju originalne tabele i preimenujte je tako što ćete njenom imenu dodati "_OLD". 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.

Više informacija potražite u članku "Normalizovanje podataka pomoću analizatora tabele".

3. korak: povezivanje sa Access podacima iz programa Excel

Nakon normalizacije podataka u programu Access i kreiranja upita ili tabele koji rekonstruišu originalne podatke, jednostavno se treba povezati sa Access podacima iz programa Excel. Podaci se sada nalaze u programu Access kao spoljni izvor podataka pa mogu da se povežu sa radnom sveskom pomoću veze sa podacima koja predstavlja kontejner informacija koji se koristi za pronalaženje, prijavljivanje i pristupanje spoljnom izvoru podataka. Informacije o vezi uskladištene su u radnoj svesci i takođe mogu da se skladište u datoteci veze, kao što je Office Data Connection (ODC) datoteka (oznaka tipa datoteke .odc) ili datoteka imena izvora podataka (oznaka tipa datoteke .dsn). Pored toga, kada se povežete sa spoljnim podacima, Excel radnu svesku možete automatski 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).

Prebacite vaše podatke u Access

Ovaj odeljak vas vodi kroz sledeće faze normalizacije podataka: Razdvajanje vrednosti u kolonama "Prodavac" i "Adresa" na najobičnije delove, razdvajanje srodnih tema u sopstvene tabele, kopiranje i lepljenje tih tabela iz programa Excel u Access, kreiranje ključnih relacija između novokreiranih tabela u programu Access i pravljenje i pokretanje jednostavnog upita u programu Access radi dobijanja informacija.

Example data in non-normalized form

Sledeći radni list sadrži neatomske vrednosti u kolonama "Prodavac" i "Adresa". Obe kolone treba podeliti u dve ili više zasebnih kolona. Ovaj radni list takođe sadrži informacije o prodavcima, proizvodima, kupcima i porudžbinama. Ove informacije bi takođe trebalo dodatno podeliti po temi u zasebne tabele.

Prodavac	ID porudžbine	Datum porudžbine	ID proizvoda	Količina	Цena	Ime klijenta	Adresa	Broj telefona
Li, Jejl	2349	3/4/09	SU-789	3	$ 7.00	Četvrta kafa	7007 Kornel St Redmond, WA 98199	425-555-0201
Li, Jejl	2349	3/4/09	C-795	6	$9.75	Četvrta kafa	7007 Kornel St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	SU-2275	2	$ 16.75	Avanturistički radovi	1025 Kolumbija krug Kirkland, Vašington 98234	425-555-0185
Adams, Ellen	2350	3/4/09	P-198	6	$5.25	Avanturistički radovi	1025 Kolumbija krug Kirkland, Vašington 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	$4.50	Avanturistički radovi	1025 Kolumbija krug Kirkland, Vašington 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	$9.75	Contoso d.o.o.	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	SU-2275	2	$ 16.75	Avanturistički radovi	1025 Kolumbija krug Kirkland, Vašington 98234	425-555-0185
Hance, Jim	2352	3/5/09	SU-4420	3	$ 7.25	Avanturistički radovi	1025 Kolumbija krug Kirkland, Vašington 98234	425-555-0185
Koch, Reed	2353	3/7/09	SU-2275	6	$ 16.75	Četvrta kafa	7007 Kornel St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	SU-789	5	$ 7.00	Četvrta kafa	7007 Kornel St Redmond, WA 98199	425-555-0201

Informacije u najmanjim delovima: atomski 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, grad, država i poštanski broj) u odvojene kolone.

Sledeća tabela prikazuje nove kolone u istom radnom listu pošto se razdele kako bi sve vrednosti postale atomske. Imajte na umu da su informacije u koloni "Prodavac" podeljene na kolone "Prezime" i "Ime", kao i da su informacije u koloni "Adresa" podeljene u kolone "Ulica", "Grad", "Država" i "Poštanski broj". Ovi podaci su u "prvom normalnom obrascu".

Prezime	Ime	Ulica i broj	Grad	Država	Poštanski broj
Li	Yale	Avenija Harvard 2302	Novi Sad	Vašington	98227
Bilten	Elena	1025 Kolumbija krug	Sombor	Vašington	98234
Hance	Bojan	Avenija Harvard 2302	Novi Sad	Vašington	98227
Omiljeno	Trska	7007 Kornel St Redmond	Kragujevac	Vašington	98199

Razbijanje podataka u organizovane teme u programu Excel

Nekoliko tabela sa primerima podataka koje slede prikazuju iste informacije iz Excel radnog lista kada se razdeli na tabele za prodavce, proizvode, kupce i porudžbine. Dizajn tabele nije konačan, ali je na dobrom putu.

Tabela "Prodavci" sadrži samo informacije o osoblju za prodaju. Imajte na umu da svaki zapis ima jedinstveni ID (ID prodavca). Vrednost "ID prodavca" koristiće se u tabeli "Porudžbine" za povezivanje porudžbina sa prodavcima.

Prodavci
ID prodavca	Prezime	Ime
101	Li	Yale
103	Bilten	Elena
105	Hance	Bojan
107	Omiljeno	Trska

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".

Proizvodi
ID proizvoda	Цena
SU-2275	16.75
B-205	4.50
SU-789	7.00
C-795	9.75
SU-4420	7.25
P-198	5.25

Tabela "Kupci" sadrži samo informacije o klijentima. Imajte u vidu da svaki zapis ima jedinstveni ID (ID klijenta). Vrednost "ID kupca" će se koristiti za povezivanje informacija o klijentu sa tabelom "Porudžbine".

Klijenti
ID kupca	Ime	Ulica i broj	Grad	Država	Poštanski broj	Broj telefona
1001	Contoso d.o.o.	Avenija Harvard 2302	Novi Sad	Vašington	98227	425-555-0222
1003	Avanturistički radovi	1025 Kolumbija krug	Sombor	Vašington	98234	425-555-0185
1005	Četvrta kafa	Ulica Kornel 7007	Kragujevac	Vašington	98199	425-555-0201

Tabela "Porudžbine" sadrži informacije o porudžbinama, prodavcima, kupcima i proizvodima. Imajte na umu da svaki zapis ima jedinstveni ID (ID porudžbine). Neke od informacija u ovoj tabeli treba podeliti u dodatnu tabelu koja sadrži detalje porudžbine kako bi tabela "Porudžbine" sadržala samo četiri kolone – jedinstveni ID porudžbine, datum porudžbine, ID prodavca i ID klijenta. Tabela koja je ovde prikazana još nije razdeljena na tabelu sa detaljima porudžbine.

Porudžbine
ID porudžbine	Datum porudžbine	ID prodavca	ID kupca	ID proizvoda	Količina
2349	3/4/09	101	1005	SU-789	3
2349	3/4/09	101	1005	C-795	6
2350	3/4/09	103	1003	SU-2275	2
2350	3/4/09	103	1003	P-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	SU-2275	2
2352	3/5/09	105	1003	SU-4420	3
2353	3/7/09	107	1005	SU-2275	6
2353	3/7/09	107	1005	SU-789	5

Detalji porudžbine, kao što su ID proizvoda i količina, premeštaju se iz tabele "Porudžbine" i skladište u tabeli pod imenom "Detalji porudžbine". Imajte na umu da ima 9 porudžbina, tako da ima smisla da u ovoj tabeli postoji 9 zapisa. Imajte na umu da tabela "Porudžbine" ima jedinstveni ID (ID porudžbine) na koji će upućivati 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 sve kolone sadrže "suvišne" podatke. Međutim, u ovoj tabeli ne bi trebalo da dva zapisa budu potpuno identična (ovo pravilo se odnosi na bilo koju tabelu u bazi podataka). U ovoj tabeli bi trebalo da bude 17 zapisa – svaki odgovara nekom proizvodu u pojedinačnoj porudžbini. Na primer, u porudžbini 2349, tri C-789 proizvoda čine jedan od dva dela cele porudžbine.

Prema tome, tabela "Detalji porudžbine" trebalo bi da izgleda ovako:

Detalji porudžbine
ID porudžbine	ID proizvoda	Količina
2349	SU-789	3
2349	C-795	6
2350	SU-2275	2
2350	P-198	6
2350	B-205	1
2351	C-795	6
2352	SU-2275	2
2352	SU-4420	3
2353	SU-2275	6
2353	SU-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 podeljene na zasebne teme u programu Excel, te podatke možete kopirati 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 za vraćanje informacija o raznim temama. Na primer, možete da kreirate upit koji vraća ID porudžbine i imena prodavaca za porudžbine unete između 05.03.2009. i 08.03.2009.

Pored toga, možete da kreirate obrasce i izveštaje da 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.