Excel като база данни 4 съвета за по-голяма производителност - PC Magazin
Използването на Excel като база данни може да бъде удобно решение. С нарастването и увеличаването на файла обаче, спестеното време изчезва. С правилната технология можете да компенсирате загубата на производителност.
Excel като база данни? Защо не. От Excel 2007 потребителят има общо 1048 576 реда и 16 384 колони на лист. Това е огромна мрежа и значително разширяване на оригиналните 65 536 реда и 256 колони. За сравнение Access предлага само 256 колони. Това е една от причините, поради които Excel се злоупотребява като база данни. Потребителите приемат, че програмата става относително бавна с толкова много данни. Но може да се направи и по-бързо.
Тази статия описва четири техники за обработка на големи количества данни в Excel с помощта на функции на база данни, полета за данни, SQL и съвременни алгоритми. Тези техники отнемат много малко време, тъй като заобикаляте функцията за автоматично изчисление и правите всички изчисления в паметта.
Техника 1: Техниката за пинг-понг
Всеки път, когато обработвате данни в таблица на Excel, Excel преизчислява засегнатите клетки и области за всеки отделен запис. Това отнема известно време. Следователно изчисляването на Excel е напълно заобиколено при първата техника. Съдържанието на таблица на Excel първоначално се прехвърля в основната памет на вашия компютър с една команда. Тогава действителната обработка се извършва тук.
Клиентска таблица с 6000 записани данни, която е сортирана според честотата на поръчката, служи като отправна точка. С техниката на пинг-понг можете бързо да филтрирате за клиенти, които са поръчали повече от пет пъти.
За тази цел в основната памет се създава поле за данни, в което данните се обработват допълнително. След обработката пълното съдържание на данните се прехвърля от основната памет в целевата таблица. Отново, за това действие ви е необходима само една команда. Повечето работи, базирани на тази технология с много големи количества данни, отнемат по-малко от секунда.
В следващия пример съдържанието на таблицата (клиентски данни) на таблицата tbl_Gesamt се въвежда в работната памет. След това определени редове се изтриват от базата данни и след това останалата част от останалото количество данни се извежда в таблицата tbl_result.
В таблицата tbl_Gesamt всички клиентски данни, които имат честота на поръчка, както е посочено в клетка I1, трябва да бъдат прехвърлени в таблицата tbl_Erresult. Изходният код (Листинг 1 The Ping-Pong Technique.docx) и примерните файлове (Quelle.xlsx, Result.xlsx и PerformanceTechniken.xlsm) могат да бъдат намерени на DVD под Top-Software/Extras за брошурата/Excel бази данни технологии.
Първо, началното време на процедурата се записва с помощта на инструкцията Debug.Print. След това таблицата tbl_Erresult се изпразва, използвайки метода ClearContents. Размерът на използваната площ се определя, като се използва свойството Usedrange за колоните и редовете.
След това използваната област на таблицата се транспортира директно в поле за данни. Данните вече са в паметта. Веднъж там се създава поле за данни със същия размер с името VardatZiel. Данните, съответстващи на критерия за количество, се преместват от полето за данни Var-Dat в полето за данни VardatZiel чрез цикъл.
Изброени са всички клиенти, които са поръчали повече от пет пъти.
В последната стъпка полето за данни VardatZiel се извежда в таблицата tbl_Erresult. За целта размерът на полето за данни трябва да бъде запазен в таблицата. Колоните в таблицата се коригират автоматично с помощта на метода AutoFit. В примерния тест тази техника се нуждае от по-малко от секунда, за да намали 6000 записа с данни (общо tbl_) до 3619 записа с данни (tbl_result).
Техника 2: Използвайте SQL за оценка на големи количества данни със светкавична скорост
В тази техника се използва езикът за заявки към базата данни SQL. Могат да се стартират заявки, които след това извличат данни от активната или затворена работна книга и ги извеждат в целевата таблица. Можете да използвате процедурата (изброяване 2 достъп до данни чрез SQL statement.docx), за да разрешите същата задача, както е описана в техника 1.
Използвайки SQL израз, всички клиентски данни от таблицата tbl_Gesamt трябва да бъдат импортирани в таблицата tbl_SQL, която има честота на поръчка, която се изисква в клетка I1.
Общата таблица (6000 записа с данни) трябва да бъде разпределена в 25 таблици съгласно колона 6 (= F). Нашият макрос за филтриране на данни отнема четири секунди, за да направи това.
Първо, за да бъде в безопасност, целевата таблица tbl_SQL се изпразва, използвайки метода ClearContents. След това се създава ADO обект (Access Data Source), който позволява достъп до SQL команди. След това като цел се посочва вашата собствена, отворена в момента работна книга и връзката се отваря чрез метода Open.
След това се съставя SQL изразът. Тук се крие истинската интелигентност на процедурата. Имената на полетата (тук заглавията на таблицата на Excel tbl_Gesamt) са посочени, разделени със запетаи с помощта на ключовата дума SELECT.
Изходната таблица е посочена в командата FROM. Условие се формулира с помощта на термина WHERE. 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 Result.xlsx. За това се използва филтърът за данни от Excel.
От този текстов файл трябва да се импортират само данните на клиенти от Швейцария.
Като предварителна работа таблицата tbl_DatenVerteilen дефинира колоната, според която данните трябва да бъдат разпределени в таблиците. Ще намерите изходните файлове за това в DVD брошурата (Листинг 3 Използвайте Excel AutoFilter няколко пъти, за да разпространявате data.docx). На първо място, досадните спирачки временно се изключват в Excel с тази технология.
Application.Calculation = xlCalculationManualApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False
Свойството Calculation временно изключва изчислението, като присвоява константата xlCalculationManual на това свойство. Актуализацията на екрана се изключва чрез свойството ScreenUpdating чрез присвояване на стойността False.
Диалогов прозорец за избор на файл се показва и оценява на екрана чрез метода GetOpenFileName. Преди реалната обработка първо се записва общият брой записи на данни, за да се провери по-късно дали всички записи на данни наистина са обработени и разпределени.
След това от определената колона се определя уникален списък (тук колона F = брой поръчки). Броят на уникалните поръчки, определен тук, е в основата на таблиците на Excel, които трябва да се вмъкнат. За това се използва методът AdvancedFilter.
Range.AdvancedFilterAction: = xlFilterCopy, _CriteriaRange: = Range, CopyToRange: = tbl_DatenVerteilen.Range ("H1"), Unique: = True
След това този уникален списък се изпълнява в цикъл. В рамките на цикъла съответната група се филтрира, свързаните данни се копират и поставят в нова таблица. В края на процедурата има тест за правдоподобност, при който общият брой на оригиналните записи на данни се сравнява с броя на разпределените записи на данни.
С помощта на обекта на файловата система в 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 (Този Workbook.Path & "\ Sales.txt")
VarDat = разделяне (FsoDat.ReadAll, vbCrLf)
След това се използва командата Filter, която филтрира цялото поле с данни според думата за търсене и прехвърля резултата в полето за спомагателни данни VardatZiel. Това поле с данни вече се изпразва ред по ред в таблицата tbl_Suchen и след това се разпределя в колоните, използвайки метода TextToColumns на базата на разделителя (тук точка и запетая). Тази технология отнема по-малко от секунда за филтриране на около 7000 записа с данни в текстови файлове върху 36 записа с данни!
Заключение
Използвайки специални техники, можете също да обработвате големи количества данни в Excel, без да осакатявате Excel. Въпреки всички тези техники, Excel не е нито база данни, нито заместител на база данни. Техниките, представени тук, пак ще ви помогнат, ако искате да продължите да работите с Excel като ваш любим инструмент дори с големи количества данни.