Excel mint adatbázis 4 tipp a nagyobb teljesítmény érdekében - PC Magazin
Az Excel használata adatbázisként kényelmes megoldás lehet. Amint azonban a fájl egyre nagyobb, a megtakarított idő eltűnik. A megfelelő technológiával kompenzálhatja a teljesítményvesztést.
Excel mint adatbázis? Miért ne. Az Excel 2007 óta a felhasználónak összesen 1 048 576 sora és 16 384 oszlopa van laponként. Ez egy hatalmas rács, és az eredeti 65 536 sor és 256 oszlop jelentős bővítése. Ehhez képest az Access csak 256 oszlopot kínál. Ez az egyik oka annak, hogy az Excel-t adatbázisként visszaélik. A felhasználók elfogadják, hogy a program ennyi adat mellett viszonylag lassúvá válik. De gyorsabban is megtehető.
Ez a cikk négy technikát ismertet, hogyan lehet nagy mennyiségű adatot feldolgozni az Excelben adatbázis-függvények, adatmezők, SQL és modern algoritmusok használatával. Ezek a technikák nagyon kevés időt vesznek igénybe, mert megkerülik az automatikus számítási funkciót, és az összes számítást a memóriában hajtják végre.
1. technika: A ping-pong technika
Amikor az adatokat Excel táblázatban dolgozza fel, az Excel újraszámolja az egyes bejegyzésekhez tartozó cellákat és területeket. Ez eltart egy ideig. Ezért az Excel számítása az első technikában teljesen megkerülhető. Az Excel tábla tartalma először egyetlen paranccsal kerül a számítógép fő memóriájába. A tényleges feldolgozás itt történik.
Kiindulópontként egy 6000 adatrekordot tartalmazó ügyféltáblázat szolgál, amelyet megrendelési gyakoriság szerint rendeznek. A ping-pong technikával gyorsan szűrhet azoknak az ügyfeleknek, akik több mint ötször rendeltek.
Erre a célra egy adatmezőt állítanak be a fő memóriában, amelyben az adatokat tovább feldolgozzák. Feldolgozás után az adatok teljes tartalma átkerül a fő memóriából a céltáblába. Ehhez a művelethez ismét csak egyetlen parancsra van szükség. A legtöbb, ezen a technológián alapuló munka nagyon nagy adatmennyiséggel kevesebb, mint egy másodpercig tart.
A következő példában a tbl_Gesamt tábla táblázat tartalma (ügyféladatai) kerülnek a munkamemóriába. Ezt követően bizonyos sorok törlődnek az adatállományból, majd a fennmaradó adatmennyiség többi részét kimeneti a tbl_Erresult táblázat.
A tbl_Gesamt táblázatban minden ügyféladatot, amelynek rendelési gyakorisága az I1 cellában meghatározott, át kell vinni a tbl_Erresult táblába. A forráskód (1. lista: A pingpong technika.docx) és a példafájlok (Quelle.xlsx, Result.xlsx és PerformanceTechniken.xlsm) megtalálhatók a DVD-n a Füzet/Excel füzetek Top-Software/Extras részében.
Először az eljárás kezdési idejét rögzítjük a Debug.Print utasítással. A tbl_Erresult tábla ezután a ClearContents módszerrel kiürül. A használatban lévő terület méretét az oszlopok és a sorok Usedrange tulajdonságának felhasználásával határozzuk meg.
Ezután a táblázat használt területe közvetlenül egy adatmezőbe kerül. Az adatok most a memóriában vannak. Odaérve egy ugyanolyan méretű VardatZiel névvel rendelkező adatmező jön létre. A mennyiségi kritériumnak megfelelő adatok a Var-Dat adatmezőből a VardatZiel adatmezőbe hurok útján kerülnek.
Az összes ügyfél, aki több mint ötször rendelt, fel van sorolva.
Az utolsó lépésben a VardatZiel adatmező be van billentve a tbl_Erresult táblába. Ehhez az adatmező méretét le kell foglalni a táblázatban. A táblázat oszlopai automatikusan beállítódnak az AutoFit módszerrel. A példa tesztben ennek a technikának kevesebb, mint egy másodpercre van szüksége ahhoz, hogy 6000 adatrekordot (tbl_ összesen) 3619 adatrekordig (tbl_result) redukáljon.
2. technika: Az SQL segítségével nagy mennyiségű adatot értékelhet villámgyorsan
Ebben a technikában az SQL adatbázis-lekérdező nyelvet használják. Indíthatók olyan lekérdezések, amelyek azután lekérik az adatokat az aktív vagy egy még bezárt munkafüzetből, és egy céltáblában adják ki. Az eljárással (2. lista az adatokhoz való hozzáférés felsorolása egy SQL utasításon keresztül.docx) az 1. technikában leírt feladat megoldására használható.
SQL utasítás használatával a tbl_Gesamt táblából származó összes ügyféladatot importálni kell a tbl_SQL táblába, amelynek rendelési gyakorisága megköveteli az I1 cellát.
A teljes táblázatot (6000 adatrekord) 25 táblára kell felosztani a 6. oszlop szerint (= F). Adatszűrő makrónk négy másodpercet vesz igénybe.
Először is, a biztonság kedvéért a tbl_SQL céltáblát kiürítjük a ClearContents módszerrel. Ezután létrejön egy ADO objektum (Access Data Sources), amely lehetővé teszi az SQL parancsokhoz való hozzáférést. Ezt követően a saját, jelenleg nyitott munkafüzetet adjuk meg célként, és a kapcsolatot az Open módszerrel nyitjuk meg.
Ezután összeáll az SQL utasítás. Itt rejlik az eljárás valódi intelligenciája. A mezőneveket (itt az Excel táblázat tbl_Gesamt fejlécei) vesszővel elválasztva adjuk meg a SELECT kulcsszóval.
A forrás táblázatot a FROM parancs adja meg. A feltétel megfogalmazása a WHERE kifejezés használatával történik. Az ORDER BY SQL utasítás meghatározza a rendezést, amely szerint az adatokat a tbl_SQL céltáblában kell tárolni:
strSQL = "SELECT név, keresztnév, utca, irányítószám, város, [megrendelések száma]" & "FROM [tbl_Gesamt $] WHERE [megrendelések száma]> = 5" & "ORDER BY [megrendelések száma] DESC"
És itt van az eredmény: A 25 táblát automatikusan létrehozták egy új mappában, és az adatokat elosztották benne.
A meghatározott adatokat ezután a Copy-FromRecordset módszerrel átviszik a memóriából a céltáblába. Ennek a technológiának a működése kicsit hosszabb ideig tart. A 6000 adatrekordról a 3619 adatrekordra való csökkentés még mindig kevesebb, mint két másodpercig tartott.
tipp: Ugyanazt a technikát használhatja a zárt munkafüzetek eléréséhez is. Csak egy sort kell beállítania:
strConnection = "DRIVER =; DBQ =" & Ez a munkafüzet.Path & "\ NameDerMappe.xls"
3. technika: Az Excel adatszűrő használata makrón keresztül
Ezzel a technikával megnyílik egy Excel munkafüzet, és az első tábla teljesen feldolgozásra kerül. A táblázatban szereplő adatokat az újonnan létrehozott Result.xlsx Excel munkafüzet megrendelései (1–25. Szám) alapján osztják el új táblákon. Ehhez az Excelből származó adatszűrőt használják.
Csak a svájci ügyfelek adatait szabad importálni ebből a szövegfájlból.
Előzetes munkaként a tbl_DatenVerteilen táblázat meghatározza azt az oszlopot, amely szerint az adatokat el kell osztani a táblákban. Az ehhez tartozó forrásfájlokat a DVD füzetben találja (3. lista: Az adatok terjesztéséhez használja az Excel automatikus szűrőt többször is.docx). Először is a bosszantó fékeket ideiglenesen kikapcsolják az Excelben ezzel a technológiával.
Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayAlerts = Hamis
A Calculation tulajdonság ideiglenesen kikapcsolja a számítást az xlCalculationManual konstans hozzárendelésével ehhez a tulajdonsághoz. A képernyőfrissítés a ScreenUpdating tulajdonságon keresztül kikapcsol, hamis érték hozzárendelésével.
Egy fájlválasztó párbeszédpanel jelenik meg a képernyőn, és a GetOpenFileName módszerrel értékeli ki. A tényleges feldolgozás előtt először rögzítik az adatrekordok teljes számát, hogy később ellenőrizzék, valóban feldolgozták-e és elosztották-e az összes adatrekordot.
Ezután a megadott oszlopból meghatározunk egy egyedi listát (itt F oszlop = megrendelések száma). Az itt meghatározott egyedi rendelések száma képezi a beillesztendő Excel táblák alapját. Ehhez az AdvancedFilter módszert alkalmazzák.
Range.AdvancedFilterAction: = xlFilterCopy, _CriteriaRange: = Tartomány, CopyToRange: = tbl_DatenVerteilen.Range ("H1"), Egyedi: = Igaz
Ez az egyedi lista ezután egy hurokban fut keresztül. A cikluson belül a megfelelő csoport szűrésre kerül, a társított adatokat átmásolja és új táblába illeszti. Az eljárás végén van egy hitelességi teszt, amelyben az eredeti adatrekordok teljes számát összehasonlítják az elosztott adatrekordok számával.
Az Excel fájlrendszer objektumának segítségével az összes svájci adatrekordot kivonták egy szöveges fájlból.
A 6000 adatrekord 25 táblára történő elosztása kevesebb, mint 4 másodpercet vesz igénybe. Ha ezt a feladatot manuálisan szeretné végrehajtani az Excel adatszűrőjének használatával, táblánként 30 másodpercet vesz igénybe (ha gyors), és 25 percet kap. Matematikailag ez 18 750 százalékos javulást eredményez!
4. technika: A fájlrendszer objektum használata az adatok gyors importálásához
Az itt bemutatott utolsó technikában egy szöveges fájlt importálunk az Excel programba. A szövegfájlból azonban csak bizonyos adatrekordokat veszünk le és importálunk a tbl_Suchen táblába. A társított forráskód megtalálható a 4 Listing Open, Filter and Output fájlban a FileSystem Object.docx fájlon keresztül.
A ClearContents módszer segítségével a céltábla törlődik, a fejléc kivételével. Ezután létrejön a Filesystemobject objektum, amely automatikusan parancsokat ad a fájlok és könyvtárak feldolgozásához.
Állítsa be az FSO = CreateObject ("Scripting.filesystemobject")
Az egyik az OpenTextFile parancs, amellyel megnyithat egy szöveges fájlt (itt Sales.txt). A ReadAll metódust a teljes tartalom beolvasására és felosztására használják a fő memóriába egy adatmezőn keresztül a Split funkció segítségével.
Állítsa be az FsoDat = FSO.OpenTextFile (Ez a munkafüzet.Path & "\ Sales.txt")
VarDat = Felosztás (FsoDat.ReadAll, vbCrLf)
Ezután a Filter parancsot használják, amely kiszűri a keresési kifejezés teljes adatmezőjét, és az eredményt a VardatZiel segédadatok mezőjébe továbbítja. Ez az adatmező most soronként kiürül a tbl_Suchen táblába, majd az elválasztó (itt pontosvessző) alapján a TextToColumns módszerrel oszlik el az oszlopokban. Ez a technológia kevesebb, mint egy másodpercet vesz igénybe, amikor mintegy 7000 szöveges fájlban található adatrekordot kiszűrnek 36 adatrekordra!
Következtetés
Speciális technikák segítségével nagy mennyiségű adatot is feldolgozhat az Excelben anélkül, hogy megbénítaná az Excel programot. Mindezen technikák ellenére az Excel sem adatbázis, sem nem helyettesíti az adatbázist. Az itt bemutatott technikák továbbra is segítenek abban, ha továbbra is az Excel programmal szeretne kedvenc eszközként dolgozni, még nagy mennyiségű adat esetén is.