Excel ca bază de date 4 sfaturi pentru mai multă performanță - PC Magazin
Utilizarea Excel ca bază de date poate fi o soluție convenabilă. Cu toate acestea, pe măsură ce fișierul crește din ce în ce mai mult, timpul economisit dispare. Cu tehnologia potrivită, puteți compensa pierderea de performanță.
Excel ca bază de date? De ce nu. Din Excel 2007, utilizatorul are un total de 1.048.576 rânduri și 16.384 coloane pe foaie. Aceasta este o grilă uriașă și o extindere semnificativă a celor 65.536 de rânduri și 256 de coloane originale. În comparație, Access oferă doar 256 de coloane. Acesta este unul dintre motivele pentru care Excel este abuzat ca bază de date. Utilizatorii acceptă că programul devine relativ lent cu atât de multe date. Dar se poate face și mai repede.
Acest articol descrie patru tehnici despre cum să procesați cantități mari de date în Excel folosind funcțiile bazei de date, câmpurile de date, SQL și algoritmi moderni. Aceste tehnici necesită foarte puțin timp deoarece ocoliți funcția de calcul automat și efectuați toate calculele în memorie.
Tehnica 1: Tehnica ping-pong
Ori de câte ori prelucrați datele într-un tabel Excel, Excel recalculează celulele și zonele implicate pentru fiecare intrare individuală. Acest lucru durează ceva timp. Prin urmare, calculul Excel este complet ocolit în prima tehnică. Conținutul unui tabel Excel este transferat inițial în memoria principală a computerului dvs. cu o singură comandă. Prelucrarea efectivă are loc aici.
Un tabel de clienți cu 6000 de înregistrări de date, care este sortat în funcție de frecvența comenzii, servește ca punct de plecare. Cu tehnica ping-pong puteți filtra rapid pentru clienții care au comandat de peste cinci ori.
În acest scop, un câmp de date este configurat în memoria principală în care datele sunt prelucrate în continuare. După procesare, conținutul complet al datelor este transferat din memoria principală într-un tabel țintă. Din nou, aveți nevoie doar de o singură comandă pentru această acțiune. Majoritatea lucrărilor bazate pe această tehnologie cu cantități foarte mari de date durează mai puțin de o secundă.
În exemplul următor, conținutul tabelului (datele clienților) al tabelului tbl_Gesamt este adus în memoria de lucru. Apoi, anumite rânduri sunt șterse din baza de date, iar restul cantității de date rămase este afișat în tabelul tbl_result.
În tabelul tbl_Gesamt, toate datele despre clienți care au o frecvență de comandă specificată în celula I1 ar trebui transferate în tabelul tbl_Erresult. Codul sursă (Listarea 1 The Ping-Pong Technique.docx) și exemplele de fișiere (Quelle.xlsx, Result.xlsx și PerformanceTechniken.xlsm) pot fi găsite pe DVD sub Top-Software/Extras pentru broșura/tehnici de baze de date Excel.
În primul rând, ora de începere a procedurii este înregistrată folosind instrucțiunea Debug.Print. Tabelul tbl_Erresult este apoi golit folosind metoda ClearContents. Dimensiunea zonei utilizate este determinată folosind proprietatea Usedrange pentru coloane și rânduri.
Apoi, zona utilizată a tabelului este transportată direct într-un câmp de date. Datele sunt acum în memorie. Odată ajuns acolo, este creat un câmp de date de aceeași dimensiune cu numele VardatZiel. Datele corespunzătoare criteriului cantității sunt mutate din câmpul de date Var-Dat în câmpul de date VardatZiel printr-o buclă.
Sunt afișați toți clienții care au comandat de peste cinci ori.
În ultimul pas, câmpul de date VardatZiel este introdus în tabelul tbl_Erresult. Pentru a face acest lucru, dimensiunea câmpului de date trebuie rezervată în tabel. Coloanele din tabel sunt ajustate automat folosind metoda AutoFit. În testul de exemplu, această tehnică are nevoie de mai puțin de o secundă pentru a reduce 6.000 de înregistrări de date (total tbl_) la 3.619 înregistrări de date (tbl_result).
Tehnica 2: Utilizați SQL pentru a evalua cantități mari de date la viteza fulgerului
Limbajul de interogare a bazei de date SQL este utilizat în această tehnică. Pot fi pornite interogări, care apoi preluează date din registrul de lucru activ sau încă închis și le trimit într-un tabel țintă. Puteți utiliza procedura (Listarea 2 acces la date printr-o declarație SQL.docx) pentru a rezolva aceeași sarcină descrisă în tehnica 1.
Folosind o declarație SQL, toate datele clienților din tabelul tbl_Gesamt ar trebui importate în tabelul tbl_SQL care are o frecvență de comandă necesară în celula I1.
Tabelul total (6000 înregistrări de date) trebuie distribuit în 25 de tabele conform coloanei 6 (= F). Macro-ul nostru de filtrare a datelor durează patru secunde pentru a face acest lucru.
Pentru a fi sigur, tabelul țintă tbl_SQL este mai întâi golit folosind metoda ClearContents. Apoi este creat un obiect ADO (Access Data Sources), care permite accesul la comenzile SQL. Ulterior, propriul registru de lucru deschis în prezent este specificat ca destinație și conexiunea este deschisă utilizând metoda Deschidere.
Apoi, instrucțiunea SQL este compusă. Aici se află adevărata inteligență a procedurii. Numele câmpurilor (aici titlurile tabelului Excel tbl_Gesamt) sunt specificate separate prin virgule folosind cuvântul cheie SELECT.
Tabelul sursă este specificat în comanda FROM. O condiție este formulată folosind termenul WHERE. Instrucțiunea SQL ORDER BY definește sortarea conform căreia datele vor fi stocate în tabelul țintă tbl_SQL:
strSQL = "SELECTAți numele, prenumele, strada, codul poștal, orașul, [numărul de comenzi]" & "FROM [tbl_Gesamt $] WHERE [numărul de comenzi]> = 5" & "ORDER BY [numărul de comenzi] DESC"
Și iată rezultatul: cele 25 de tabele au fost create automat într-un folder nou și datele au fost distribuite în acesta.
Datele determinate sunt apoi transferate din memorie în tabelul țintă folosind metoda Copy-FromRecordset. Această tehnologie durează puțin mai mult pentru a rula. Reducerea de la 6000 de înregistrări de date la 3619 de înregistrări de date a durat încă mai puțin de două secunde.
bacsis: Puteți utiliza, de asemenea, aceeași tehnică pentru a accesa registre de lucru închise. Tot ce trebuie să faceți este să reglați o linie:
strConnection = "DRIVER =; DBQ =" & This Workbook.Path & "\ NameDerMappe.xls"
Tehnica 3: Utilizarea filtrului de date Excel prin macro
Cu această tehnică, se deschide un registru de lucru Excel și primul tabel din acesta este procesat complet. Datele din tabel sunt distribuite în tabele noi pe baza comenzilor (numărul 1 până la 25) din registrul de lucru Excel nou creat.Resultat.xlsx. Filtrul de date din Excel este utilizat pentru aceasta.
Doar datele clienților din Elveția ar trebui importate din acest fișier text.
Ca lucrare preliminară, tabelul tbl_DatenVerteilen definește coloana conform căreia datele ar trebui distribuite în tabele. Veți găsi fișierele sursă pentru acest lucru pe DVD-ul broșurii (Lista 3 Folosiți AutoFilter-ul Excel de mai multe ori pentru a distribui data.docx). În primul rând, frânele enervante sunt oprite temporar în Excel cu această tehnologie.
Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False
Proprietatea Calculation oprește temporar calculul prin atribuirea constantei xlCalculationManual acestei proprietăți. Actualizarea ecranului este dezactivată prin intermediul proprietății ScreenUpdating atribuindu-i valoarea False.
Un dialog de selectare a fișierelor este afișat pe ecran și evaluat folosind metoda GetOpenFileName. Înainte de procesarea efectivă, numărul total de înregistrări de date este înregistrat mai întâi pentru a verifica ulterior dacă toate înregistrările de date au fost într-adevăr procesate și distribuite.
O listă unică este apoi determinată din coloana specificată (aici coloana F = numărul de comenzi). Numărul de comenzi unice determinat aici formează baza tabelelor Excel care urmează să fie inserate. Pentru aceasta se folosește metoda AdvancedFilter.
Range.AdvancedFilterAction: = xlFilterCopy, _CriteriaRange: = Range, CopyToRange: = tbl_DatenVerteilen.Range ("H1"), Unic: = True
Această listă unică este apoi rulată într-o buclă. În cadrul buclei, grupul respectiv este filtrat, datele asociate sunt copiate și lipite într-un nou tabel. La sfârșitul procedurii există un test de plauzibilitate în care numărul total de înregistrări de date originale este comparat cu numărul de înregistrări de date distribuite.
Cu ajutorul obiectului sistemului de fișiere din Excel, toate înregistrările de date elvețiene au fost extrase dintr-un fișier text.
Distribuirea a 6000 de înregistrări de date în 25 de tabele durează mai puțin de 4 secunde. Dacă doriți să efectuați această sarcină manual folosind filtrul de date Excel, va dura 30 de secunde pe tabel (dacă sunteți rapid) și veți obține 25 de minute. Din punct de vedere matematic, acest lucru duce la o îmbunătățire de 18.750 la sută!
Tehnica 4: Utilizarea obiectului sistemului de fișiere pentru importul rapid de date
În ultima tehnică prezentată aici, un fișier text este importat în Excel. Cu toate acestea, numai anumite înregistrări de date sunt extrase din fișierul text și importate în tabelul tbl_Suchen. Codul sursă asociat poate fi găsit în fișierul Listarea 4 Deschidere, filtrare și ieșire prin FileSystem Object.docx.
Cu ajutorul metodei ClearContents, tabelul țintă este șters, cu excepția titlului. Apoi este creat obiectul Filesystemobject, care furnizează automat comenzi pentru procesarea fișierelor și a directorului.
Setați FSO = CreateObject ("Scripting.filesystemobject")
Una dintre ele este comanda OpenTextFile, pe care o puteți utiliza pentru a deschide un fișier text (aici Sales.txt). Metoda ReadAll este utilizată pentru a citi și împărți întregul conținut în memoria de lucru printr-un câmp de date folosind funcția Split.
Setați FsoDat = FSO.OpenTextFile (This Workbook.Path & "\ Sales.txt")
VarDat = Split (FsoDat.ReadAll, vbCrLf)
Apoi este utilizată comanda Filter, care filtrează întregul câmp de date pentru termenul de căutare și transferă rezultatul în câmpul de date auxiliare VardatZiel. Acest câmp de date este acum golit rând cu rând în tabelul tbl_Suchen și apoi distribuit coloanelor folosind metoda TextToColumns pe baza separatorului (aici punct și virgulă). Această tehnologie durează mai puțin de o secundă pentru a filtra aproximativ 7000 de înregistrări de date în fișiere text în 36 de înregistrări de date!
Concluzie
Folosind tehnici speciale, puteți procesa, de asemenea, cantități mari de date în Excel, fără a paraliza Excel. În ciuda tuturor acestor tehnici, Excel nu este nici o bază de date și nici un substitut pentru o bază de date. Tehnicile prezentate aici vă vor ajuta în continuare dacă doriți să continuați să lucrați cu Excel ca instrument preferat chiar și cu cantități mari de date.