Excel како база на податоци 4 совети за повеќе перформанси - компјутер Magazin

Користењето на Excel како база на податоци може да биде погодно решение. Сепак, како што датотеката станува се поголема и поголема, заштеденото време исчезнува. Со соодветна технологија, можете да ја надоместите загубата на перформансите.

совети

Excel како база на податоци? Зошто да не. Од Excel 2007 година, корисникот има вкупно 1.048.576 редови и 16.384 колони по лист. Ова е огромна мрежа и значително проширување на оригиналните 65.536 редови и 256 колони. За споредба, Access нуди само 256 колони. Ова е една од причините зошто Excel се злоупотребува како база на податоци. Корисниците прифаќаат дека програмата станува релативно бавна со толку многу податоци. Но, тоа може да се направи и побрзо.

Оваа статија опишува четири техники како да обработувате големи количини на податоци во Excel користејќи функции на базата на податоци, полиња за податоци, SQL и модерни алгоритми. Овие техники траат многу малку време затоа што ја заобиколувате одликата за автоматско пресметување и ја правите целата пресметка во меморијата.

Техника 1: Техника на пинг-понг

Секогаш кога обработувате податоци во табела во Excel, Excel ги пресметува повторно ќелиите и областите вклучени за секој поединечен запис. Ова трае некое време. Затоа, пресметката на Excel е целосно заобиколена во првата техника. Содржината на табелата Excel првично се пренесува во главната меморија на вашиот компјутер со една команда. Вистинската обработка тогаш се одвива овде.

Табелата за клиенти со 6000 записи за податоци, која е подредена според фреквенцијата на нарачката, служи како почетна точка. Со техниката на пинг-понг можете брзо да филтрирате за клиенти кои порачале повеќе од пет пати.

За таа цел, полето за податоци е поставено во главната меморија во која податоците се обработуваат понатаму. По обработката, целосната содржина на податоците се пренесува од главната меморија во целната табела. Повторно, потребна ви е само една команда за оваа акција. Повеќето работи базирани на оваа технологија со многу големи количини на податоци траат помалку од една секунда.

Во следниот пример, содржината на табелата (податоци за клиентите) на табелата tbl_Gesamt се внесуваат во работната меморија. После тоа, одредени линии се бришат од залихите на податоци, а потоа остатокот од преостанатата количина на податоци се емитува во табелата tbl_Erresult.

Во табелата tbl_Gesamt, сите податоци на клиентите кои имаат фреквенција на нарачки, како што е наведено во ќелијата I1, треба да се пренесат во табелата tbl_Result. Изворниот код (Список 1 Техника на пинг-понг.docx) и примерочни датотеки (Quelle.xlsx, Result.xlsx и PerformanceTechniken.xlsm) може да се најдат на ДВД под Топ-софтвер/Дополнителни податоци за брошурата/технологиите на базата на податоци на Excel.

Прво, времето на започнување на постапката се бележи со употреба на инструкциите Debug.Print. Тогаш табелата tbl_Erresult се испразнува со користење на методот ClearContents. Големината на површината што се користи се одредува со користење на имотот Usedrange за колоните и за редовите.

Потоа користената област на табелата се транспортира директно во полето за податоци. Податоците сега се во меморија. Откако таму, се создава поле за податоци со иста големина со името ВардатЗиел. Податоците што одговараат на критериумот за количина се префрлаат од полето за податоци Var-Dat во полето за податоци VardatZiel преку јамка.

Наведени се сите клиенти кои порачале повеќе од пет пати.

Во последниот чекор, полето за податоци VardatZiel се додава во табелата tbl_Erresult. За да го направите ова, големината на полето за податоци мора да биде резервирана во табелата. Колоните во табелата автоматски се прилагодуваат со методот AutoFit. Во примерот со тест, на оваа техника и треба помалку од една секунда за да ги намали 6000 записи на податоци (вкупно tbl_) на 3.619 записи на податоци (tbl_резултат).

Техника 2: Користете SQL за да процените големи количини на податоци со молскавична брзина

Јазикот за барање на бази на податоци SQL се користи во оваа техника. Може да се започнат пребарувања, кои потоа земаат податоци од активната или сè уште затворена работна книга и ги емитуваат во табелата на целта. Можете да ја користите постапката (Наведување 2 пристап до податоци преку изјава SQL.docx) за да ја решите истата задача како што е опишано во техниката 1.

Користејќи изјава SQL, сите податоци на клиентите од табелата tbl_Gesamt треба да бидат увезени во табелата tbl_SQL што има фреквенција на нарачки што е потребна во ќелијата I1.

Вкупната табела (6000 записи за податоци) треба да се распредели на 25 табели според колоната 6 (= F). Нашето макро филтер за податоци трае четири секунди за да го направите ова.

Прво, за да бидете на безбедна страна, целната табела tbl_SQL се испразнува со методот ClearContents. Потоа се создава ADO објект (Извори на податоци за пристап), што овозможува пристап до SQL командите. После тоа, вашата сопствена, моментално отворена работна книга е одредена како цел и врската се отвора со методот Отвори.

Тогаш е составена SQL изјавата. Ова е местото каде лежи вистинската интелигенција на постапката. Имињата на полињата (овде заглавија на табелата во Excel tbl_Gesamt) се специфицирани одделени со запирки со употреба на клучниот збор SELECT.

Изворната табела е наведена во командата ОД. Состојба е формулирана со употреба на терминот КАДЕ. Изјавата SQL ORDER BY го дефинира подредувањето според кое податоците треба да бидат зачувани во целната табела tbl_SQL:

strSQL = "Изберете име, име, улица, поштенски код, град, [број на нарачки]" & "ОД [tbl_Gesamt $] КАДЕ [број на нарачки]> = 5" & "НАРАЧКА ОД [број на нарачки] DESC"

И еве го резултатот: 25-те табели беа автоматски креирани во нова папка и податоците беа дистрибуирани во неа.

Податоците утврдени потоа се пренесуваат од меморијата во целната табела со методот Copy-FromRecordset. За да се работи на оваа технологија, треба малку повеќе време. Намалувањето од 6000 записи за податоци на 3619 записи за податоци сепак траеше помалку од две секунди.

совет: Можете исто така да ја користите истата техника за пристап до затворени работни книги. Сè што треба да направите е да прилагодите една линија:

strConnection = "DRIVER =; DBQ =" & Оваа работна книга.Пат и "\ NameDerMappe.xls"

Техника 3: Користење на филтерот за податоци на Excel преку макро

Со оваа техника се отвора работна книга во Excel и првата маса во неа е целосно обработена. Податоците во табелата се дистрибуираат до нови табели засновани на налозите (број 1 до 25) во новосоздадената работна книга на Excel Резултат.xlsx. За ова се користи филтерот за податоци во Excel.

Само податоците на клиентите од Швајцарија треба да бидат увезени од оваа текстуална датотека.

Како прелиминарна работа, табелата tbl_DatenVerteilen ја дефинира колоната според која податоците треба да бидат дистрибуирани во табелите. Изворните датотеки за ова ќе ги најдете на ДВД-брошурата (Список 3 Користете го Excel AutoFilter неколку пати за да дистрибуирате data.docx). Како прво, вознемирувачките сопирачки се привремено исклучени во Excel со оваа технологија.

Апликација. Пресметка = xl Пресметка Мануелна апликација. Екранско ажурирање = Лажна апликација. Известувања за приказ = лажно

Карактеристиката Калкулација привремено ја исклучува пресметката со доделување на xlCalculationManual константа на ова својство. Ажурирањето на екранот се исклучува преку својството ScreenUpdating со доделување на вредноста False.

Дијалогот за избор на датотека се прикажува и оценува на екранот со методот GetOpenFileName. Пред реалната обработка, вкупниот број на записи за податоци прво се снима со цел подоцна да се провери дали сите записи за податоци навистина биле обработени и дистрибуирани.

Единствената листа потоа се одредува од наведената колона (тука колона F = број на нарачки). Бројот на уникатни нарачки утврдени овде претставува основа на табелите во Excel што треба да се вметнат. За ова се користи методот AdvancedFilter.

Range.Ad AdvancedFilterAction: = xlFilterCopy, _CriteriaRange: = Опсег, CopyToRange: = tbl_DatenVerteilen.Range ("H1"), Уникатно: = Вистина

Оваа единствена листа потоа се извршува во јамка. Во рамките на јамката, соодветната група се филтрира, придружните податоци се копираат и се лепат во нова табела. На крајот на постапката, се спроведува тест за веродостојност во кој вкупниот број на оригинални записи на податоци се споредува со бројот на дистрибуирани записи на податоци.

Со помош на објектот на датотечниот систем во Excel, сите швајцарски записи за податоци беа извлечени од текстуална датотека.

Распределбата на 6000 записи за податоци на 25 табели трае помалку од 4 секунди. Ако сакате рачно да ја извршите оваа задача со помош на филтерот за податоци на Excel, ќе ви требаат 30 секунди по табела (ако сте брзи) и ќе добиете 25 минути. Математички, ова резултира во подобрување од 18,750 проценти!

Техника 4: Користење на објектот на датотечниот систем за брз увоз на податоци

Во последната техника претставена овде, текстуалната датотека се увезува во Excel. Сепак, само одредени записи за податоци се извлекуваат од текстуалната датотека и се увезуваат во табелата tbl_Suchen. Поврзаниот изворен код може да се најде во списокот со датотеки 4 Отворено, филтер и излез преку FileSystem Object.docx.

Со помош на методот ClearContents, целната табела се чисти, освен заглавието. Потоа се креира објектот Filesystemobject, кој автоматски обезбедува команди за обработка на датотеки и директориум.

Постави FSO = CreateObject ("Scripting.filesystemobject")

Една од нив е командата OpenTextFile, која можете да ја користите за да отворите текстуална датотека (тука Sales.txt). Методот ReadAll се користи за читање и разделување на целата содржина во работната меморија преку поле за податоци користејќи ја функцијата Сплит.

Поставете FsoDat = FSO.OpenTextFile (Оваа работна книга.Пат и "\ Продај.txt")

VarDat = Сплит (FsoDat.ReadAll, vbCrLf)

Потоа се користи командата Filter, која го филтрира целото поле за податоци за терминот за пребарување и го пренесува резултатот во полето за помошни податоци VardatZiel. Ова поле за податоци сега се испразнува линија по линија во табелата tbl_Suchen и потоа се дистрибуира до колоните со употреба на методот TextToColumns врз основа на сепараторот (тука точка-точка и точка). Оваа технологија трае помалку од една секунда за да филтрира околу 7000 записи на податоци во текстуални датотеки на 36 записи на податоци!

Заклучок

Користејќи специјални техники, можете исто така да обработувате големи количини на податоци во Excel без да го осакатите Excel. И покрај сите овие техники, Excel не е ниту база на податоци ниту замена за базата на податоци. Техниките презентирани овде сепак ќе ви помогнат ако сакате да продолжите да работите со Excel како омилена алатка дури и со големи количини на податоци.