Adatok áthelyezése az Excelből az Accessbe

Ez a cikk bemutatja, hogyan helyezheti át az adatokat az Excelből az Access alkalmazásba, és hogyan konvertálhatja őket relációs táblázatokká, hogy együtt használhassa a Microsoft Excelt és az Accesst. Összegzésképpen elmondjuk, hogy az Access az adatok rögzítésére, tárolására, lekérdezésére és megosztására, az Excel pedig az adatok kiszámítására, elemzésére és vizualizációjára.

Az adatok kezelése az Access vagy az Excel segítségével két cikkből ( 10 legfontosabb érv az Access és az Excel együttes használata mellett) megvitatjuk, hogy melyik program a legalkalmasabb egy adott feladatra, és hogy hogyan használható együtt az Excel és az Access egy praktikus megoldás.

Ha adatokat helyez át az Excelből az Access alkalmazásba, három alapvető lépésből áll a folyamat.

1. lépés: Adatok importálása az Excelből az Accessbe

Az adatok importálása sokkal zökkenőmentesebb művelet, ha időt szán az adatok előkészítésére és tisztázására. Az adatok importálása olyan, mintha új otthonba költözne. Ha költözés előtt kitakarítja és rendszerezi vagyonát, sokkal könnyebb beilleszkedni új otthonába.

Az adatok tisztítása importálás előtt

Mielőtt adatokat importálna az Access alkalmazásba, az Excelben az alábbiakat teheti:

• A nem atomi adatot tartalmazó cellákat (vagyis ha több érték van egy cellában) több oszloppá konvertálhatja. A "Képességek" oszlop egy celláját, amely több képességértéket tartalmaz (például "C# programozás", "VBA programozás" és "webdesign"), különálló oszlopokba kell bontani, amelyek mindegyike csak egy képességértéket tartalmaz.
• A KIMETSZ paranccsal eltávolíthatja a szóközöket a kezdő és a záró szóközök, valamint a beágyazott szóközök többszörös beágyazásához.
• Távolítsa el a nem nyomtatható karaktereket.
• Megkeresheti és kijavíthatja a helyesírási és írásjeles hibákat.
• Távolítsa el az ismétlődő sorokat vagy mezőket.
• Ügyeljen arra, hogy az adatoszlopok ne tartalmazzanak vegyes formátumokat, különösen a szövegként formázott számokat és a dátumokat.

További információt az Excel következő súgótémaköreiben talál:

• 10 leghasznosabb módja az adatok letisztázásának
• Egyedi értékek szűrése és ismétlődő értékek eltávolítása
• Szövegként tárolt számok számmá alakítása
• Szövegként tárolt dátumok dátummá alakítása

A legjobb adattípus kiválasztása importáláskor

Az importálási művelet során érdemes jó döntéseket hozni, hogy kevés (vagy egyáltalán előfordulhat) manuális beavatkozást igénylő konverziós hiba jelenjen meg. Az alábbi táblázat összefoglalja, hogy a program hogyan konvertálja az Excel számformátumait és az Access-adattípusokat, amikor adatokat importál az Excelből az Accessbe, és tippeket ad a Táblázat importálása varázslóban választható legjobb adattípusokhoz.

Excel-számformátum	Access-adattípus	Megjegyzések	Ajánlott eljárások
Text (Szöveg)	Szöveg, feljegyzés	Az Access szöveg adattípusa legfeljebb 255 karakter hosszúságú alfanumerikus adatokat tárol. Az Access Feljegyzés adattípus legfeljebb 65 535 karakter hosszúságú alfanumerikus adatokat tárol.	Válassza a Feljegyzés lehetőséget, ha el szeretné kerülni az adatok csonkolását.
Szám, százalék, tört, tudományos	Szám:	Az Access egy Szám adattípussal rendelkezik, amely a Mezőméret tulajdonság alapján változik (Bájt, Egész szám, Hosszú egész, Egyszeres, Dupla, Decimális).	Válassza a Dupla lehetőséget az adatkonverziós hibák elkerülése érdekében.
Dátum	Dátum	Az Access és az Excel is ugyanazt a dátumszámot használja a dátumok tárolására. Az Accessben a dátumtartomány nagyobb: -657 434 (Kr. u. 100. január 1.) és 2 958 465 (Kr. u. 9999. december 31.) között. Mivel az Access nem ismeri fel az 1904-es dátumrendszert (amelyet a Macintosh Excelben használ), a dátumokat vagy az Excelben, vagy az Accessben kell konvertálni az összezavarok elkerülése érdekében. További információt A dátumrendszer, a dátumformátum vagy a kétjegyű évértelmezés megváltoztatása és Az Excel-munkafüzetben tárolt adatok importálása vagy csatolása című témakörben talál.	Válassza a Dátum lehetőséget.
Idő	Idő	Az Access és az Excel is ugyanazt az adattípust használva tárolja az időértékeket.	Válassza ki az alapértelmezett időpontot.
Pénznem, könyvelés	Pénznem	Az Accessben a Pénznem adattípus 8 bájtos számok formájában tárolja az adatokat négy tizedesjegy pontossággal, és pénzügyi adatok tárolására, valamint az értékek kerekítésének megakadályozására szolgál.	Válassza a Pénznemet, amely általában az alapértelmezett.
logikai változó	Igen/Nem	Az Access a -1-et használja minden Igen értékhez és 0-t a Nem értékekhez, míg az Excel 1-et használ az összes IGAZ és 0-t a HAMIS értékekhez.	Válassza az Igen/Nem lehetőséget, amely automatikusan konvertálja a mögöttes értékeket.
Hivatkozás	Hivatkozás	Az Excelben és az Accessben a hivatkozások egy kattintásra használható URL-címet vagy webcímet tartalmaznak.	Válassza a Hivatkozás típust, ellenkező esetben az Access alapértelmezés szerint a Szöveg adattípust használja.

Miután az adatok az Accessbe kerültek, törölheti az adatokat. Ne felejtsen el biztonsági másolatot készíteni az eredeti Excel-munkafüzetről, mielőtt törölné azt.

További információt az Access súgójának Az Excel-munkafüzetben tárolt adatok importálása vagy csatolása című témakörében talál.

Adatok automatikus hozzáfűzése egyszerűen

Az Excel-felhasználók gyakori problémája, hogy az azonos oszlopokból származó adatokat egyetlen nagy munkalapra fűzik hozzá. Tegyük fel például, hogy van egy eszköznyilvántartó megoldása, amely az Excelben indult, de mostanra számos munkacsoportból és részlegből származó fájlokat tartalmaz. Ezek az adatok lehetnek más munkalapokon és munkafüzetekben, illetve más rendszerek adatcsatornáiban lévő szövegfájlokban. Az Excelben nincs felhasználói felületi parancs, illetve hasonló adatok hozzáfűzésére nincs egyszerű mód.

A legjobb megoldás az Access használata, ahol a Táblázat importálása varázslóval egyszerűen importálhat és fűzhet hozzá adatokat egyetlen táblához. Ezenkívül nagy mennyiségű adatot fűzhet egyetlen táblázathoz. Az importálási műveleteket mentheti, ütemezett Microsoft Outlook-feladatként hozzáadhatja, sőt makrók használatával automatizálhatja a folyamatot.

2. lépés: Adatok normalizálása a Táblaanalizáló varázslóval

Első pillantásra ijesztőnek tűnhet az adatok normalizálásának lépése. Szerencsére a táblák normalizálása az Accessben sokkal egyszerűbb a Táblaanalizáló varázslónak köszönhetően.

1. A kijelölt oszlopok új táblába húzása és kapcsolatok automatikus létrehozása

2. Gombparancsokkal átnevezheti a táblákat, elsődleges kulcsot vehet fel, meglévő oszlopot tehet elsődleges kulcská, valamint vonhatja vissza a legutóbbi műveletet

A varázslóval az alábbi műveleteket végezheti el:

• A táblákat kisebb táblákká alakíthatja, és automatikusan létrehozhat elsődleges és idegen kulcsú kapcsolatot a táblák között.
• Adjon elsődleges kulcsot egy egyedi értékeket tartalmazó meglévő mezőhöz, vagy hozzon létre egy Számláló adattípust használó új azonosítómezőt.
• Hozzon létre automatikusan kapcsolatokat a hivatkozási integritás megőrzéséhez lépcsőzetes frissítésekkel. Az adatok véletlen törlésének megakadályozása érdekében nem történik meg automatikusan kaszkádolt törlés hozzáadása, később azonban egyszerűen hozzáadhat kaszkádolt törléseket.
• Keressen felesleges vagy ismétlődő adatokat az új táblákban (például ugyanaz az ügyfél két különböző telefonszámmal), és szükség szerint frissítse őket.
• Készítsen biztonsági másolatot az eredeti tábláról, és nevezze át úgy, hogy hozzáfűzi a "_OLD" nevet a táblához. Ezután létrehoz egy lekérdezést, amely újraépíti az eredeti táblát az eredeti tábla nevével, hogy az eredeti táblán alapuló űrlapok és jelentések az új táblaszerkezettel működjenek.

További információt az Adatok normalizálása a Táblaanalizálóval című témakörben talál.

3. lépés: Csatlakozás az Access-adatokhoz az Excelből

Miután normalizálta az adatokat az Accessben, és létrehozott egy lekérdezést vagy táblát, amely rekonstruálja az eredeti adatokat, egyszerűen csatlakoznia kell az Access-adatokhoz az Excelből. Az adatok most külső adatforrásként kerülnek az Accessbe, így egy adatkapcsolaton keresztül csatlakoztathatók a munkafüzethez, amely a külső adatforrás megkeresésére, az abba való bejelentkezésre és az abba való bejelentkezésre használható, információtároló. A munkafüzet tárolja a kapcsolatadatokat, valamint tárolhatja őket egy kapcsolatfájl, például egy Office-adatkapcsolatfájl (ODC) vagy egy adatforrásnévfájl (.dsn kiterjesztés). Miután csatlakozott külső adatokhoz, azt is megteheti, hogy automatikusan frissíti (vagy frissíti) az Excel-munkafüzetet az Accessből, amikor az adatok frissülnek az Accessben.

További információ: Adatok importálása külső adatforrásokból (Power Query).

Adatok beolvasása az Accessbe

Ez a szakasz az adatok normalizálásának következő fázisain vezeti végig: az Üzletkötő és a Cím oszlop értékeinek lebontása a legatomibb részekre, a kapcsolódó témák elkülönítése saját táblákba, a táblák másolása és beillesztése az Excelből az Accessbe, kulcskapcsolatok létrehozása az újonnan létrehozott Access-táblák között, valamint egy egyszerű lekérdezés létrehozása és futtatása az Accessben adatok visszaadásához.

Példaadatok nem normalizált formában

A következő munkalap nem atomi értékeket tartalmaz az Üzletkötő és a Cím oszlopban. Mindkét oszlopot két vagy több külön oszlopra kell osztani. A munkalap értékesítőkkel, termékekkel, vevőkkel és megrendelésekkel kapcsolatos információkat is tartalmaz. Ezeket az információkat tovább kell osztani téma szerint külön táblákba.

Értékesítő	Rendelés azonosítója	Rendelés dátuma	Termékazonosító	Mennyiség	Ár	Ügyfél neve	Address (Cím)	Telefon
Li, Yale	2349	3/4/09	C-789	3	$7.00 ($7.00)	Babszem Kávézó	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	$9.75 ($9.75)	Babszem Kávézó	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 Ft	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	4,50 Ft (USD4.50)	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	$9.75 ($9.75)	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 ($7.25)	Adventure Works	1025 Columbia Circle Kirkland, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	A-2275	6	$16.75	Babszem Kávézó	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	$7.00 ($7.00)	Babszem Kávézó	7007 Cornell St Redmond, WA 98199	425-555-0201

Információ a legkisebb részeiben: atomi adatok

Ha az alábbi példában szereplő adatokkal dolgozik, az Excel Szövegből oszlop parancsával különálló oszlopokba választhatja a cellák "atomi" részeit (például a címet, a települést, az országot és az irányítószámot).

Az alábbi táblázatban láthatók az új oszlopok ugyanabban a munkalapban, miután felosztotta őket, hogy az összes érték atomikussá váljon. Figyelje meg, hogy az Üzletkötő oszlopban lévő adatok Vezetéknév és Utónév oszlopokra, a Cím oszlopban lévő adatok pedig Utca, Település, Házszám és Irányítószám oszlopokra lettek felosztva. Ezek az adatok "első normálalakban" vannak.

Utónév	Vezetéknév	Utca, házszám	Város	Állam	Irányítószám
Li	Yale	2302 Harvard Ave	Verőce	WA	98227
Bálint	Ellen	1025 Columbia Circle	Kiskunfélegyháza	WA	98234
Hance	Jim	2302 Harvard Ave	Verőce	WA	98227
Koch	Reed	7007 Cornell St Redmond	Redmond	WA	98199

Adatok felosztása rendezett témákra az Excelben

A következő példaadatokat tartalmazó táblázatok ugyanazokat az információkat mutatják be az Excel-munkalapról, miután azt értékesítők, termékek, vevők és megrendelések szerinti táblázatokba osztotta. A táblaterv nem végleges, de jó úton halad.

Az Üzletkötők tábla csak az értékesítők adatait tartalmazza. Vegye figyelembe, hogy minden rekord egyedi azonosítóval (SalesPerson ID) rendelkezik. Az Üzletkötőazonosító érték a Rendelések táblában használható a rendelések és az értékesítők összekapcsolásához.

Üzletkötők
Üzletkötő azonosítója	Utónév	Vezetéknév
101	Li	Yale
103	Bálint	Ellen
105	Hance	Jim
107	Koch	Reed

A Termékek tábla csak a termékekkel kapcsolatos adatokat tartalmazza. Vegye figyelembe, hogy minden rekord egyedi azonosítóval (Product ID) rendelkezik. A rendszer a Termékazonosító értéket használja a termékadatok és a Rendelési adatok táblához való kapcsolásához.

Termékek
Termékazonosító	Ár
A-2275	16.75
B-205	4.50
C-789	7,00
C-795	9.75
D-4420	7.25
F-198	5,25%

A Vevők tábla csak az ügyfelek adatait tartalmazza. Vegye figyelembe, hogy minden rekord egyedi azonosítóval (vevőazonosítóval) rendelkezik. A rendszer a vevőazonosító értéket használja az ügyféladatok Rendelések táblához való kapcsolásához.

Ügyfelek
Vevőkód	Név	Utca, házszám	Város	Állam	Irányítószám	Telefon
1001	Contoso, Ltd.	2302 Harvard Ave	Verőce	WA	98227	425-555-0222
1003	Adventure Works	1025 Columbia Circle	Kiskunfélegyháza	WA	98234	425-555-0185
1005	Babszem Kávézó	7007 Cornell St	Redmond	WA	98199	425-555-0201

A Rendelések tábla rendelésekkel, értékesítőkkel, vevőkkel és termékekkel kapcsolatos adatokat tartalmaz. Vegye figyelembe, hogy minden rekord egyedi azonosítóval (Order ID) rendelkezik. A táblázatban szereplő adatok egy részét fel kell osztani egy további táblába, amely a megrendelések részleteit tartalmazza, hogy a Rendelések tábla csak négy oszlopot tartalmazzon – az egyedi rendelésazonosítót, a rendelés dátumát, az üzletkötő azonosítóját és az ügyfél azonosítóját. Az itt látható táblázat még nincs felbontva a Rendelés részletei táblára.

Rendelések
Rendelés azonosítója	Rendelés dátuma	Értékesítőazonosító	Vevőkód	Termékazonosító	Mennyiség
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

A rendelés részleteit, például a termékazonosítót és a mennyiséget a rendszer áthelyezi a Rendelések táblából, és a Rendelés részletei nevű táblába menti. Ne feledje, hogy 9 megrendelés van, így logikus, hogy 9 rekord van ebben a táblában. Vegye figyelembe, hogy a Rendelések táblának van egy egyedi azonosítója (Rendelésazonosító), amelyre a Rendelés részletei táblából fog hivatkozni.

A Rendelések tábla végső terve így néz ki:

Rendelések
Rendelés azonosítója	Rendelés dátuma	Értékesítőazonosító	Vevőkód
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

A Rendelés részletei táblában nincsenek egyedi értéket igénylő oszlopok (azaz nincs elsődleges kulcs), ezért nem okoz gondot, ha bármelyik vagy akár az összes oszlop tartalmaz "redundáns" adatokat. A tábla azonban nem lehet két teljesen azonos rekordja (a szabály az adatbázisok bármely táblájára érvényes). Ebben a táblázatban 17 rekordnak kell lennie – mindegyik egy-egy megrendelésben szereplő terméknek felel meg. Például a 2349-es megrendelésben három C-789-es termék alkotja a teljes megrendelés két részének egyikét.

A Rendelés részletei táblának ezért az alábbihoz hasonlóan kell kinéznie:

Rendelés részletei
Rendeléskód	Termékazonosító	Mennyiség
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

Adatok másolása és beillesztése az Excelből az Accessbe

Most, hogy az értékesítőkre, vásárlókra, termékekre, megrendelésekre és megrendelések részleteire vonatkozó adatokat külön témákba osztottuk az Excelben, ezeket az adatokat közvetlenül az Accessbe másolhatja, ahol táblák lesznek belőlük.

Kapcsolatok létrehozása az Access-táblák között és lekérdezés futtatása

Miután áthelyezte az adatokat az Accessbe, kapcsolatokat hozhat létre a táblák között, majd lekérdezéseket hozhat létre a különböző témákkal kapcsolatos információk visszaadása céljából. Létrehozhat például egy olyan lekérdezést, amely a rendelésazonosítót és az értékesítők nevét adja vissza a 2009.03.05. és 2009.03.08. közötti rendelések esetén.

Ezenkívül űrlapok és jelentések készítésével megkönnyítheti az adatbevitelt és az értékesítési elemzést.

További segítségre van szüksége?

Kérdéseivel mindig felkeresheti az Excel technikai közösség egyik szakértőjét, vagy segítséget kérhet a közösségekben.