Adatáthelyezés az Excelből az Access alkalmazásba

Ebből a cikkből megtudhatja, hogy miként helyezheti át az adatait az Excel alkalmazásból az Accessben, és hogyan alakíthatja át őket relációs tábláiba, így a Microsoft Excel és az Access együtt is használható. Összegezve: az Access a legmegfelelőbb az adatok rögzítéséhez, tárolásához, lekérdezéséhez és megosztásához, és az Excel a legmegfelelőbb az adatok kiszámításához, elemzéséhez és megjelenítéséhez.

Két cikk, az Access és az Excel használata az adatok kezeléséhez és a 10 legfontosabb érv az Access Excelalkalmazásban való használatához, a program megbeszéli, hogy melyik program a legmegfelelőbb egy adott tevékenységhez, és hogy miként hozhat létre gyakorlati megoldást az Excel és az Access alkalmazással együtt.

Amikor az Excelből Access-adatokkal áthelyezi az adatokat, a folyamatnak három alapvető lépése van.

three basic steps

Megjegyzés: Az Accessben az adatmodellezésről és a kapcsolatokról az adatbázisok tervezésének alapjaicímű témakörben olvashat bővebben.

Első lépés: az Excel alkalmazásból az Access alkalmazásba történő importálás

Az importálási művelet olyan művelet, amely sokkal simábban működik, ha időt vesz igénybe az adatkészítéshez és az adattisztításhoz. Az adatimportálás olyan, mint az új otthoni verzióra való áttérés. Ha az áthelyezés előtt kitisztítja és rendszerezi a birtokait, még egyszerűbben rendezheti az új otthonába.

Az importálás előtt megtisztítja az adatait

Mielőtt az Accessben importálja az adatait, az Excelben érdemes lehet a következőket tennie:

  • Nem atomi adatokat (vagyis egy cellában több értéket) tartalmazó cellák konvertálása több oszlopra. Ha például egy "Skills" oszlopban egy olyan cellát tartalmaz, amely több képzettségi értéket tartalmaz, például "C#-programozás", "VBA-programozás" és "web design", akkor külön oszlopokra kell bontani, amelyek mindegyike csak egy képzettséget tartalmaz

  • A kimetsz parancs segítségével eltávolíthatja a vezető, a záró és a több beágyazott szóközt.

  • Nem nyomtatható karakterek eltávolítása

  • A helyesírási és írásjelek hibáinak megkeresése és kijavítása.

  • Ismétlődő sorok vagy ismétlődő mezők eltávolítása

  • Győződjön meg arról, hogy az adatoszlopok nem tartalmaznak kevert formátumot, különösen a számokként formázott szövegként vagy dátumként formázott számokat.

További információt az Excel súgójának alábbi témaköreiben találhat:

Megjegyzés: Ha az adattisztítási szükségletek bonyolultak, vagy nincs ideje vagy erőforrása a folyamat automatikus automatizálására, érdemes lehet külső gyártót használni. További információ: "Adattisztító szoftver" vagy "adatminőség" a kedvenc keresőmotorja a böngészőben.

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

Az Accessben az importálási művelet során jó döntéseket szeretne tenni, hogy csak néhány (ha van ilyen) konverziós hibát kell beírnia, amely kézi beavatkozást igényel. Az alábbi táblázat összefoglalja, hogy miként történik az Excel számformátumok és az Access-adattípusok konvertálása az Excelből az Access alkalmazásba történő importáláskor, valamint tippeket nyújt a legjobb adattípusokhoz a számolótábla importálása varázslóban.

Excel-számformátum

Access-adattípus

Megjegyzések

Ajánlott eljárások

Szöveg

Szöveg, feljegyzés

Az Access szöveg adattípusa legfeljebb 255 karakterből álló alfanumerikus adatot tárol. Az Access-feljegyzés adattípusa legfeljebb 65 535 karakterből álló alfanumerikus adatot tárol.

A feljegyzés lehetőséget választva elkerülhető az Adatcsonkítás.

Szám, százalék, tört, tudományos

Szám:

Az Accessnek van egy szám adattípusa, amely a Mezőméret tulajdonságtól (bájt, egész, hosszú egész, egyszeres, dupla, decimális) függően változik.

Válassza a dupla lehetőséget az adatkonverziós hibák elkerüléséhez.

Dátum

Dátum

Az Access és az Excel egyaránt ugyanazt a sorozatszámot használja a dátumok tárolásához. Az Accessben a dátumtartomány nagyobb: from-657 434 (január 1, 100 A.D.) a 2 958 465-ig (december 31, 9999 A.D.).

Mivel az Access nem ismeri fel a 1904-as dátumot (a Macintosh Excelben használatos), a félreértések elkerülése érdekében át kell alakítania a dátumokat az Excelben vagy az Accessben.

További információt a dátumrendszer, a formátum vagy a kétjegyű év értelmezésének módosítása , valamint az Excel-munkafüzetben tárolt adatok importálása vagy csatolásacímű témakörben talál.

Válassza a dátumlehetőséget.

Idő

Időpont

Az Access és az Excel egyaránt ugyanazt az adattípust használja az időértékek tárolásához.

Válassza az időpontlehetőséget, ami általában az alapértelmezett.

Pénznem, könyvelési

Pénznem

Az Accessben a Pénznem adattípus 8 bájtos számokkal, négy tizedesjegy pontossággal tárolja az adatokat, és a pénzügyi adatok tárolására és az értékek kerekítésének megelőzésére szolgál.

Válassza a Pénznemgombot, amely általában az alapértelmezett.

logikai változó

Igen/Nem

Az Access a-1 értéket használja az összes Igen értékre és a 0 értékre, míg az Excel a minden igaz érték és a 0 értéket az összes hamis értékhez használja.

Válassza az Igen/nemlehetőséget, amely automatikusan átalakítja az alapul szolgáló értékeket.

Hivatkozás

Hivatkozás

Az Excelben és az Accessben a hivatkozás egy URL-címet vagy webcímet tartalmaz, amelyet a kattintásra és a követésre használhat.

Válassza a hivatkozáslehetőséget, máskülönben az Access alapértelmezés szerint használhatja a szöveg adattípus-típust.

Az Accessben az Accessben törölheti az Excel-adatforrást. A törlés előtt ne felejtse el menteni az eredeti Excel-munkafüzetet.

További információt az Access-Súgó az Excel-munkafüzetben tárolt adatok importálása vagy csatolásacímű témakörben talál.

Automatikusan hozzáfűzi az adatforrást az egyszerű módon

Gyakori probléma az Excel-felhasználóknak az ugyanazon oszlopokat tartalmazó adatok egy nagy munkalapon való hozzáfűzésével. Előfordulhat például, hogy van egy eszköz-nyomkövetési megoldás, amely az Excel alkalmazásban elindult, de mára sok munkacsoportból és részlegből származó fájlokat tartalmaz. Ezek az adatlapok eltérő munkalapokon és munkafüzetekben, illetve más rendszerekről származó adatcsatornákban tárolt szövegfájlokban lehetnek. Nincs felhasználói felület parancs vagy egyszerű módszer a hasonló adatkapcsolatok Excelben való hozzáfűzésére.

A legjobb megoldás az Access használata, ahol egyszerűen importálhatja és hozzáfűzheti az adatait egy táblába a számolótábla importálása varázslóval. Ezenkívül a sok adatot is hozzáfűzheti egy táblázathoz. Az importálási műveleteket mentheti, hozzáadhatja őket ütemezett Microsoft Outlook-feladatokként, és akár makrókat is használhat a folyamat automatizálásához.

Második lépés: az adatnormalizálás a táblázat analizátor varázslóval

Első pillantásra az adatok normalizálása a folyamat során ijesztő feladat lehet. Szerencsére az Access-táblázatok normalizálása sokkal egyszerűbb folyamat, köszönhetően a táblázat analizátor varázslójának.

the table analyzer wizard

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

2. a táblázat átnevezése, elsődleges kulcs hozzáadása, meglévő oszlop elsődleges kulcs létrehozása és az utolsó művelet visszavonása gomb parancsaival

A varázsló segítségével az alábbi műveleteket végezheti el:

  • Táblázat konvertálása kisebb táblázatok halmazára és a táblák közötti elsődleges és idegenkulcs-kapcsolat automatikus létrehozása

  • Adjon hozzá egy elsődleges kulcsot egy olyan meglévő mezőhöz, amely egyedi értékeket tartalmaz, vagy hozzon létre egy új azonosító mezőt, amely a számláló adattípust használja.

  • Kapcsolatokat automatikusan hozhat létre a hivatkozási integritás megőrzése érdekében lépcsőzetes frissítésekkel. A kaszkádolt törlések nem kerülnek automatikusan hozzáadásra az adatvesztés elkerülése érdekében, de később egyszerűen hozzáadhat lépcsőzetes törléseket.

  • Keressen új táblákat redundáns vagy ismétlődő adatokra (például két különböző telefonszámot tartalmazó ügyfelet), és frissítse a kívánt módon.

  • Készítsen biztonsági másolatot az eredeti tábláról, és nevezze el úgy, hogy az "_OLD" nevet hozzáfűzi a nevéhez. Ezután hozzon létre egy olyan lekérdezést, amely rekonstruálja az eredeti táblázatot, az eredeti táblázat nevével, hogy az eredeti táblán alapuló összes űrlap vagy jelentés együtt fog működni az új tábla szerkezetével.

További információt az adatok normalizálása a táblázat analizátor használatávalcímű témakörben talál.

3. lépés: csatlakozás Access-adatforrásokhoz az Excel alkalmazásból

Miután az adatot normalizálták az Accessben, és létrehoztak egy lekérdezést vagy táblázatot, amely az eredeti adatot rekonstruálta, az Excelből az Access-adatkapcsolathoz való csatlakozás egyszerű kérdés. Az adatok mostantól elérhetők külső adatforrásként, így a munkafüzethez adatkapcsolaton keresztül kapcsolódhat, amely a külső adatforrás megtalálásához, bejelentkezéséhez és eléréséhez használt információ. A kapcsolati adatok a munkafüzetben tárolódnak, és tárolhatók egy kapcsolatfájl (például Office-adatkapcsolat (ODC-fájl) (. odc fájlnév-kiterjesztés) vagy egy adatforrásnevet tartalmazó fájl (. DSN kiterjesztés) is. A külső adatokhoz való csatlakozást követően automatikusan frissítheti (vagy frissítheti) az Excel-munkafüzetet az Accessből, amikor az adatok frissültek az Accessben.

További tudnivalókat az adatok importálása külső adatforrásokból (Power Query)című témakörben talál.

Az adatforrások elérése az Accessben

Ebben a szakaszban az adatok normalizálása a következő fázisokban halad el: az Üzletkötő és a cím oszlopában lévő értékek törése a legtöbb atomi darabba, a kapcsolódó témák másolása és beillesztése az Excel alkalmazásból Access – kulcsos kapcsolatok létrehozása az újonnan létrehozott Access-táblák között, és egyszerű lekérdezés létrehozása és futtatása az Accessben az adatok visszaadásához.

Példa nem normalizált űrlapon lévő adattípusokra

Az alábbi munkalap nem atomi értékeket tartalmaz az Üzletkötő oszlopban és a cím oszlopban. Mindkét oszlopot két vagy több külön oszlopba kell osztani. Ebben a munkalapon az üzletkötők, a termékek, a vevők és a rendelések adatai is szerepelnek. Ezeket az információkat tovább kell osztani a tárgy szerint, külön táblázatokba is.

Értékesítő

Rendelés azonosítója

Rendelés dátuma

Termékazonosító

Mennyiség

Ár

Ügyfél neve

Cím

Telefon

Li, Yale

2349

3/4/09

C-789

3

$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

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

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Adams, ellen

2350

3/4/09

B-205

1

$4,50

Adventure Works

1025 Columbia Circle Kirkland, WA 98234

425-555-0185

Hance, Jim

2351

3/4/09

C-795

6

$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

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

Babszem Kávézó

7007 Cornell St Redmond, WA 98199

425-555-0201

A legkisebb részen lévő adatok: Atomic Data

Az alábbi példában szereplő adatokkal dolgozhat, ha az Excelben a szövegből oszlop parancsra kattint, kiválaszthatja a cella "atomi" részeit (például az utca címét, a települést és az irányítószámot) különálló oszlopokba.

Az alábbi táblázat azokat az új oszlopokat jeleníti meg ugyanazon a munkalapon, amelyek felosztása után az összes értéket az Atomic értékre osztották. Figyelje meg, hogy az Üzletkötő oszlopban lévő információkat osztották a vezetéknévre és az utónév oszlopokra, és hogy a cím oszlopban szereplő adatok az utcára, a városra, az államra és az IRÁNYÍTÓSZÁMokra bontva vannak. Ezek az adatforrások "első normál formában" jelennek meg.

Utónév

Vezetéknév

 

Postacím

Város

State

irányítószám

Li

Yale

2302 Harvard Ave

Verőce

WA

98227

Bálint

Dobándi

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

Az adatcsere az Excelben szervezett témákba

Az alábbi, többek között az üzletkötők, a termékek, a vevők és a rendelések táblájára való felosztását követően az Excel-munkalapon ugyanazokat az adatokat ábrázoló táblázatok láthatók. A táblázat látványterve nem végleges, de a megfelelő pályán van.

Az üzletkötők tábla csak az értékesítési munkatársakról tartalmaz információkat. Fontos tudni, hogy minden rekordhoz tartozik egy egyedi azonosító (Üzletkötő-azonosító). Az Üzletkötő azonosító értékét a Rendelések táblában fogja használni a rendelések üzletkötőknek való összekapcsolásához.

Üzletkötők

Üzletkötő azonosítója

Utónév

Vezetéknév

101

Li

Yale

103

Bálint

Dobándi

105

Hance

Jim

107

Koch

Reed

A Products (termékek) tábla csak a termékekkel kapcsolatos információkat tartalmazza. Fontos tudni, hogy minden rekordhoz tartozik egy egyedi azonosító (termékazonosító). A termékazonosító értékét a program az adatoknak a Rendelés részletei táblához való összekapcsolásához használja.

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 ügyfelekkel kapcsolatos információkat tartalmazza. Fontos tudni, hogy minden rekordhoz tartozik egy egyedi azonosító (ügyfél-azonosító). Az ügyfél AZONOSÍTÓjának értékét a program az ügyfél adatainak a rendelések táblával való összekapcsolására használja.

Ügyfelek

Vevőkód

Név

Postacím

Város

State

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 információkat tartalmaz a rendelésekről, üzletkötőről, vevőkről és termékekről. Fontos tudni, hogy minden rekordhoz tartozik egy egyedi azonosító (rendelés-azonosító). Az alábbi táblázat néhány adatát meg kell osztani a rendelési adatokat tartalmazó további táblákkal, hogy a rendelések tábla csak négy oszlopot tartalmazzon: az egyedi megrendelést, a rendelési dátumot, 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 felosztva a Rendelés részletei táblára.

Rendelések

Rendelés azonosítója

Rendelés dátuma

Üzletkötő azonosítója

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észletei (például a termékazonosító és a mennyiség) a Rendelések táblából kerülnek, és a Rendelés részletei nevű táblázatban találhatók. Tartsa szem előtt, hogy 9 megrendelés van, ezért érdemes az ebben a táblázatban szereplő 9 rekordot választani. Fontos tudni, hogy a rendelések tábla egyedi AZONOSÍTÓval (AZONOSÍTÓval) rendelkezik, amelyre a Rendelés részletei táblából hivatkozni fog.

A rendelések tábla végleges terve az alábbihoz hasonlóan néz ki:

Rendelések

Rendelés azonosítója

Rendelés dátuma

Üzletkötő azonosítója

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ábla olyan oszlopokat tartalmaz, amelyek egyedi értékeket igényelnek (azaz nincs elsődleges kulcs), ezért minden oszlop esetében célszerű a "redundáns" adatokat tartalmazni. Az ebben a táblázatban szereplő két rekordnak azonban teljesen egyeznie kell (ez a szabály az adatbázis bármely táblájára érvényes). Ebben a táblázatban 17 rekordnak kell lennie – mindegyiknek egy adott rendeléshez tartozónak kell lennie. Az 2349-ban például a három C-789 termékek a teljes rendelés két részének egyikét alkotják.

A Rendelés részletei táblának ezért a következőképpen 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 Excel alkalmazásból az Access alkalmazásba

Most, hogy az üzletkötők, a vevők, a termékek, a rendelések és a rendelés részleteiről szóló információkat az Excel külön-külön kibontotta, az adatokat közvetlenül az Accessbe másolhatja, ahol a táblázatokat fogja tartalmazni.

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

Miután áthelyezte az adatokat az Access alkalmazásba, létrehozhat kapcsolatokat 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 megadásához. Létrehozhat például egy olyan lekérdezést, amely visszaadja a rendelés AZONOSÍTÓját és az üzletkötők nevét az 3/05/09 és a 3/08/09 között bevitt rendelésekhez.

Ezenkívül létrehozhat űrlapokat és jelentéseket az adatbevitel és az értékesítési elemzés megkönnyítéséhez.

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

Bármikor segítséget kérhet az Excel technikai közösségétől és az Answers-közösségtől, az Excel User Voice webhelyen pedig új funkciókra vagy fejlesztésekre tehet javaslatot.

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×