Adatbázis építés Excelből Microsoft Query segítségével

2016. február 12. - Office Guru

Általában ha relációs adatbázisokról, táblákról beszélünk vagy ilyen jellegű feladattal állunk szemben, akkor az Office Access modulja jut többnyire eszünkbe kapásból, pedig a maga módján Excelből is elérhetünk ilyen opciót, ha közvetlenül már nem is az Excel segítségével. Ismét egy olvasói kérdést fogunk körbejárni, amelynek célja egyébként roppant érthető és a megoldási javaslatok között az, amiről most írni fogok, nem biztos, hogy előkerül, de vonatkoztassunk majd el példám egyszerűségétől és lássuk meg benne azt a lehetőséget, amelyet sok ezer adatot sok-sok sheeten tartalmazó táblázatunk kezelésére nyújt.

A kérdés roppant egyszerű: adott a lenti képen látható két sheet, mindkettőn különböző adatok, amelyeket egyetlen elsődleges kulcs, az Azonosító köt össze. Hogyan érjük el azt, hogy egy harmadik sheeten az azonosító alapján összekötve az általunk kiválasztott oszlopok adatai az általunk meghatározott sorrendben jelenjenek meg? Példám egyszerűségétől ismételten csak tekintsünk el.

y01.jpg

y02.jpgAz én megoldási javaslatom első lépéseként a Ribbonunk Data füle alatt található Get External Data szekcióból kattinsunk a From Other Sources opcióra, azon belül pedig válasszuk ki a From Microsoft Query opciót:

y03.jpgItt válasszuk az Excel fájl opciót, hiszen Excelben található mindkettő sheetünk (vagy bármennyi sheetünk):

y04.jpgA felugró kis Browse ablakban keressük be azt a fájlt, amelyben az egyesíteni kívánt sheetjeink vannak, majd kattintsunk az OK gombra. A következő ablakhoz jutunk:

y05.jpgA bal oldalt látható Available tables and columns struktúrából szépen pakoljuk át azokat az oszlopokat a Columns in your query nevet viselő jobb oldali mezőbe, amelyeket szeretnénk egy sheeten látni együtt:

y06.jpgHa megvagyunk, Nextre kattintva egy felugró üzenettel szembesülünk, amely félkövér betűtípusával sokkol minket és figyelmeztet, hogy egy azonosítóval össze kell majd kapcsolnunk tábláinkat (sheetjeinket) - ezt simán üssük el egy okéval és már egy szuperül kezelhető kis adatbázis-struktúrába jutunk:

y07.jpgAz egyik legfontosabb lépés jön most, össze kell kapcsolnunk tábláinkat (pontosan mintha Accessben ügyködnénk), egyszerűen fogjuk meg az egyik tábla Azonosító mezőjét és húzzuk rá a másik tábla Azonosító mezőjére:

y08.jpgMár majdnem kész is vagyunk, hiszen az előnézetben látjuk, hogy mi lesz az eredmény és itt még értelemszerűen módosíthatunk is, de ha egyetértünk azzal, amit látunk, akkor a File menü Return Data to Microsoft Excel utasításával visszavihetjük az egészet Excelbe:

y09.jpgTegyük ezt aztán új sheetre és már láthatjuk is az eredményt, amit szerettünk volna elérni:

y10.jpg

y11.jpgA poszt végén csak ismételten hangsúlyozni szeretném példám egyszerűségét, egy ilyen jellegű megoldásnak komplexebb fájlok, adatstruktúrák esetén van értelme, hiszen azért a VLOOKUP/INDEX formulák hasznosságát sem szabad elfelejteni.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr608385342

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Atzs · https://adatrendezo.hu 2016.02.13. 07:14:12

Az INDEX/HOL.VAN elvérzik akkor, ha ugyanahhoz az azonosítóhoz mindkét táblában szerepelhet több sor. Ez a módszer akkor is működik.

Ha Guru példájánál sokkal bonyolultabb adataink vannak, akkor nem érdemes az Excellel küzdeni. Megoldja, csak iszonyatosan lassan. Olyankor át kell térni Accessre vagy MySQL-re.

Office Guru 2016.02.13. 07:51:15

@Atzs: Teljesen jogos a komment, egy megfelelően komplikált adatstruktúránál már komolyabb adatbázis-kezelésre van szükségünk.

Dzsontra Volta 2016.02.13. 22:42:19

Egy viszonylag kevés táblát tartalmazó adatbázis talán még Accesben is kivitelezhető (feltéve, ha elég valami egyszerű kimenet), de komolyabb feladatokhoz inkább illik az MS sql.

I_Isti 2016.02.15. 12:47:01

@Office Guru: Az egyszerűbbeknél is javasolt. Én a magam részéről az accesst bottal sem piszkálnám, még véletlenül sem. Ha jót akarsz magadnak, akkor Oracle. Az ifjú titánoknak meg esetleg MySql. (Bár az enyhén szólva fapad szerény véleményem szerint).

Access és az ő select [kreténtáblanév].[kreténmezőnév] from [kreténtáblanév] where
[kreténtáblanév].[kreténmezőnév] = "valami"
szintaktikája brr... Márpedig ha táblanév lehet [Ez egy kretén táblanév sps-ből linkelve] akkor előbb utóbb valamelyik okos júzer fog ilyen táblanevet adni, ha másnak nem, akkor egy sps-es listának.

Szóval: ha lehetőséged van egy kicsit normalizált adattáblákat használni, akkor inkább 1000x azt, még akkor is, ha elsőre egy picivel több gondolkodást igényel.