Excel ako databáza 4 tipy na vyšší výkon - PC Magazin
Použitie programu Excel ako databázy môže byť pohodlným riešením. S rastúcim a zväčšovaným súborom však ušetrený čas zmizne. Správnou technológiou môžete kompenzovať stratu výkonu.
Excel ako databáza? Prečo nie. Od verzie Excel 2007 má používateľ celkovo 1 048 576 riadkov a 16 384 stĺpcov na jeden hárok. Jedná sa o obrovskú mriežku a výrazné rozšírenie pôvodných 65 536 riadkov a 256 stĺpcov. Pre porovnanie, Access ponúka iba 256 stĺpcov. To je jeden z dôvodov, prečo sa Excel zneužíva ako databáza. Užívatelia akceptujú, že program je s toľkými dátami pomerne pomalý. Dá sa to však urobiť aj rýchlejšie.
Tento článok popisuje štyri techniky spracovania veľkého množstva údajov v programe Excel pomocou databázových funkcií, údajových polí, SQL a moderných algoritmov. Tieto techniky trvajú veľmi málo času, pretože obídete funkciu automatického výpočtu a všetky výpočty vykonáte v pamäti.
Technika 1: Technika ping-pongu
Kedykoľvek spracúvate údaje v tabuľke programu Excel, program Excel prepočíta bunky a oblasti zahrnuté pre každú jednotlivú položku. To chvíľu trvá. Preto je výpočet Excel v prvej technike úplne obídený. Obsah tabuľky programu Excel sa spočiatku prenesie do hlavnej pamäte vášho počítača pomocou jediného príkazu. To je miesto, kde prebieha skutočné spracovanie.
Ako východiskový bod slúži zákaznícka tabuľka s 6000 dátovými záznamami, ktorá je zoradená podľa frekvencie objednávok. Pomocou techniky ping-pong môžete rýchlo filtrovať zákazníkov, ktorí si objednali viac ako päťkrát.
Za týmto účelom je v hlavnej pamäti nastavené dátové pole, v ktorom sú dáta ďalej spracovávané. Po spracovaní sa kompletný obsah dát prenesie z hlavnej pamäte do cieľovej tabuľky. Pre túto akciu budete opäť potrebovať iba jeden príkaz. Väčšina prác založených na tejto technológii s veľmi veľkým objemom údajov trvá menej ako sekundu.
V nasledujúcom príklade sa do pracovnej pamäte prenesie obsah tabuľky (údaje o zákazníkovi) tabuľky tbl_Gesamt. Potom sa z databázy odstránia určité riadky a zvyšok zvyšného množstva údajov sa vyvedie do tabuľky tbl_result.
V tabuľke tbl_Gesamt sa všetky zákaznícke údaje, ktoré majú frekvenciu objednávok uvedenú v bunke 11, majú preniesť do tabuľky tbl_Result. Zdrojový kód (zoznam 1 Ping-Pong Technique.docx) a vzorové súbory (Quelle.xlsx, Result.xlsx a PerformanceTechniken.xlsm) nájdete na DVD v časti Top-Software/Extras pre brožúru/databázové technológie Excel.
Najskôr sa pomocou inštrukcie Debug.Print zaznamená čas začiatku procedúry. Tabuľka tbl_Erresult sa potom vyprázdni pomocou metódy ClearContents. Veľkosť používanej oblasti sa určuje pomocou vlastnosti Usedrange pre stĺpce a riadky.
Potom sa použitá oblasť tabuľky prenesie priamo do dátového poľa. Dáta sú teraz v pamäti. Až tam sa vytvorí dátové pole rovnakej veľkosti s názvom VardatZiel. Údaje zodpovedajúce kritériu množstva sa pomocou slučky posúvajú z dátového poľa Var-Dat do dátového poľa VardatZiel.
V zozname sú všetci zákazníci, ktorí si objednali viac ako päťkrát.
V poslednom kroku sa dátové pole VardatZiel vyklopí do tabuľky tbl_Erresult. Za týmto účelom musí byť v tabuľke vyhradená veľkosť dátového poľa. Stĺpce v tabuľke sa automaticky upravujú pomocou metódy AutoFit. V príkladovom teste táto technika potrebuje menej ako sekundu na zníženie 6000 dátových záznamov (tbl_ celkom) na 3 619 dátových záznamov (tbl_result).
Technika 2: Použite SQL na vyhodnotenie veľkého množstva údajov rýchlosťou blesku
V tejto technike sa používa databázový dotazovací jazyk SQL. Môžu sa spustiť dotazy, ktoré potom načítajú údaje z aktívneho alebo uzavretého zošita a odošlú ich do cieľovej tabuľky. Môžete použiť postup (Zoznam 2 prístupu k údajom prostredníctvom príkazu SQL.docx) na vyriešenie rovnakej úlohy, ako je popísaná v technike 1.
Pomocou príkazu SQL by sa všetky údaje o zákazníkoch z tabuľky tbl_Gesamt mali importovať do tabuľky tbl_SQL, ktorá má požadovanú frekvenciu objednávok v bunke I1.
Celková tabuľka (6000 dátových záznamov) by sa mala rozdeliť na 25 tabuliek podľa stĺpca 6 (= F). Naše makro filtra údajov to urobí štyri sekundy.
Najskôr sa pre istotu vyprázdni cieľová tabuľka tbl_SQL pomocou metódy ClearContents. Potom sa vytvorí objekt ADO (Access Data Sources), ktorý umožňuje prístup k príkazom SQL. Potom je ako cieľ zadaný váš vlastný, momentálne otvorený zošit, a pripojenie je otvorené pomocou metódy Open.
Potom je zostavený príkaz SQL. V tom spočíva skutočná inteligencia postupu. Názvy polí (tu nadpisy excelovej tabuľky tbl_Gesamt) sú zadané oddelené čiarkami pomocou kľúčového slova SELECT.
Zdrojová tabuľka je uvedená v príkaze FROM. Podmienka je formulovaná pomocou výrazu WHERE. Príkaz SQL ORDER BY definuje triedenie, podľa ktorého sa majú údaje uložiť v cieľovej tabuľke tbl_SQL:
strSQL = "VYBERTE meno, meno, ulicu, PSČ, mesto, [počet objednávok]" & "OD [tbl_Gesamt $] KDE [počet objednávok]> = 5" & "OBJEDNAŤ [počet objednávok] DESC"
A tu je výsledok: 25 tabuliek bolo automaticky vytvorených v novom priečinku a boli v ňom distribuované údaje.
Zistené údaje sa potom prenesú z pamäte do cieľovej tabuľky pomocou metódy Copy-FromRecordset. Spustenie tejto technológie trvá trochu dlhšie. Zníženie z 6000 dátových záznamov na 3619 dátových záznamov stále trvalo menej ako dve sekundy.
tip: Rovnakou technikou môžete tiež získať prístup k uzavretým zošitom. Musíte len upraviť jeden riadok:
strConnection = "DRIVER =; DBQ =" & This Workbook.Path & "\ NameDerMappe.xls"
Technika 3: Používanie filtra údajov Excel pomocou makra
Pomocou tejto techniky sa otvorí zošit programu Excel a prvá tabuľka v ňom sa úplne spracuje. Údaje v tabuľke sa distribuujú do nových tabuliek na základe objednávok (číslo 1 až 25) v novovytvorenom excelovskom zošite Result.xlsx. Na to slúži dátový filter Excel.
Z tohto textového súboru by sa mali importovať iba údaje zákazníkov zo Švajčiarska.
Ako predbežná práca tabuľka tbl_DatenVerteilen definuje stĺpec, podľa ktorého majú byť údaje v tabuľkách distribuované. Zdrojové súbory k tomu nájdete na brožúre DVD (Zoznam 3 Na distribúciu údajov.docx použite niekoľkokrát automatický filter Excel.). Najskôr sú touto technológiou v programe Excel dočasne vypnuté nepríjemné brzdy.
Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False
Vlastnosť Výpočet dočasne vypne výpočet tak, že tejto vlastnosti priradí konštantu xlCalculationManual. Aktualizácia obrazovky sa vypne cez vlastnosť ScreenUpdating priradením hodnoty False.
Na obrazovke sa zobrazí dialógové okno na výber súboru a vyhodnotí sa pomocou metódy GetOpenFileName. Pred samotným spracovaním sa najskôr zaznamená celkový počet dátových záznamov, aby sa mohla neskôr skontrolovať, či boli všetky dátové záznamy skutočne spracované a distribuované.
Zo zadaného stĺpca sa potom určí jedinečný zoznam (tu stĺpec F = počet objednávok). Počet tu určených jedinečných objednávok tvorí základ tabuliek programu Excel, ktoré sa majú vložiť. Používa sa na to metóda AdvancedFilter.
Range.AdvancedFilterAction: = xlFilterCopy, _CriteriaRange: = Range, CopyToRange: = tbl_DatenVerteilen.Range ("H1"), jedinečný: = True
Tento jedinečný zoznam sa potom vedie v slučke. V rámci cyklu je príslušná skupina filtrovaná, súvisiace údaje sú skopírované a vložené do novej tabuľky. Na konci postupu je test dôveryhodnosti, v ktorom sa porovnáva celkový počet pôvodných dátových záznamov s počtom distribuovaných dátových záznamov.
Pomocou objektu súborového systému v programe Excel boli všetky textové záznamy zo Švajčiarska extrahované z textového súboru.
Distribúcia 6000 dátových záznamov do 25 tabuliek trvá menej ako 4 sekundy. Ak chcete túto úlohu vykonať manuálne pomocou dátového filtra programu Excel, bude to trvať 30 sekúnd na tabuľku (ak ste rýchli) a získate 25 minút. Matematicky to má za následok zlepšenie o 18 750 percent!
Technika 4: Použitie objektu súborového systému na rýchly import údajov
Podľa poslednej tu predstavenej techniky sa textový súbor importuje do programu Excel. Z textového súboru sa však čerpajú iba určité dátové záznamy a importujú sa do tabuľky tbl_Suchen. Priradený zdrojový kód nájdete v súbore Výpis 4 otvorených, filtrovaných a výstupných súborov cez FileSystem Object.docx.
Pomocou metódy ClearContents sa cieľová tabuľka vymaže okrem nadpisu. Potom sa vytvorí objekt Filesystemobject, ktorý automaticky poskytuje príkazy na spracovanie súborov a adresárov.
Nastaviť FSO = CreateObject ("Scripting.filesystemobject")
Jedným z nich je príkaz OpenTextFile, pomocou ktorého môžete otvoriť textový súbor (tu Sales.txt). Metóda ReadAll slúži na čítanie a rozdelenie celého obsahu do pracovnej pamäte prostredníctvom dátového poľa pomocou funkcie Split.
Nastaviť FsoDat = FSO.OpenTextFile (tento Workbook.Path & "\ Sales.txt")
VarDat = Split (FsoDat.ReadAll, vbCrLf)
Potom sa použije príkaz Filter, ktorý filtruje celé údajové pole pre hľadaný výraz a výsledok prenesie do pomocného údajového poľa VardatZiel. Toto údajové pole sa teraz vyprázdňuje riadok po riadku do tabuľky tbl_Suchen a potom sa distribuuje do stĺpcov pomocou metódy TextToColumns založenej na oddeľovači (tu bodkočiarka). Táto technológia trvá odfiltrovanie asi 7 000 dátových záznamov v textových súboroch do 36 dátových záznamov menej ako sekundu!
Záver
Pomocou špeciálnych techník môžete tiež spracovať veľké množstvo údajov v programe Excel bez toho, aby ste ochromili program Excel. Napriek všetkým týmto technikám program Excel nie je ani databázou, ani náhradou databázy. Tu uvedené techniky vám stále pomôžu, ak chcete pokračovať v práci s programom Excel ako s vašim obľúbeným nástrojom aj pri veľkom množstve údajov.