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_Erresult.
У таблиці tbl_Gesamt усі дані клієнта, які мають частоту замовлення, як зазначено в комірці I1, повинні бути передані до таблиці tbl_Result. Вихідний код (лістинг 1 Техніка пінг-понгу.docx) та файли прикладів (Quelle.xlsx, Result.xlsx та PerformanceTechniken.xlsm) можна знайти на DVD у розділі Найкращі програмні засоби/Додатки для буклетів/Методи баз даних Excel.
Спочатку час початку процедури реєструється за допомогою інструкції Debug.Print. Потім таблиця tbl_Erresult спорожняється за допомогою методу ClearContents. Розмір використовуваної області визначається за допомогою властивості Usedrange для стовпців та рядків.
Потім використана область таблиці транспортується безпосередньо в поле даних. Дані зараз у пам'яті. Опинившись там, створюється поле даних однакового розміру з назвою VardatZiel. Дані, що відповідають критерію кількості, переміщуються із поля даних Var-Dat у поле даних VardatZiel через цикл.
Перераховані всі клієнти, які замовляли більше п’яти разів.
На останньому кроці поле даних VardatZiel відображається у таблиці tbl_Erresult. Для цього розмір поля даних повинен бути зарезервований у таблиці. Стовпці в таблиці автоматично коригуються методом AutoFit. У прикладі тесту цьому методу потрібно менше секунди, щоб зменшити 6000 записів даних (загальний обсяг) до 3619 записів даних (tbl_result).
Техніка 2: Використовуйте SQL для оцінки великих обсягів даних із швидкістю блискавки
У цій техніці використовується мова запитів до бази даних SQL. Можна запускати запити, які потім отримують дані з активної або ще закритої книги та виводять їх у цільову таблицю. Ви можете використовувати процедуру (перелік 2 доступу до даних через оператор SQL.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. Умова формулюється з використанням терміну ДЕ. Оператор 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 кілька разів для розповсюдження 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 використовується для зчитування та розподілу всього вмісту в робочу пам’ять через поле даних за допомогою функції Split.
Встановіть FsoDat = FSO.OpenTextFile (Ця робоча книга. Шлях & "\ Sales.txt")
VarDat = Спліт (FsoDat.ReadAll, vbCrLf)
Потім використовується команда «Фільтр», яка фільтрує все поле даних відповідно до пошукового терміну і передає результат у поле допоміжних даних VardatZiel. Тепер це поле даних порожньо очищається в таблиці tbl_Suchen, а потім розподіляється по стовпцях за допомогою методу TextToColumns на основі роздільника (тут крапка з комою). Ця технологія займає менше секунди, щоб відфільтрувати близько 7000 записів даних у текстових файлах на 36 записів даних!
Висновок
За допомогою спеціальних методів ви також можете обробляти великі обсяги даних у Excel, не скалічуючи Excel. Незважаючи на всі ці методи, Excel не є ані базою даних, ані заміною бази даних. Представлені тут методи все одно допоможуть вам, якщо ви хочете продовжувати працювати з Excel як улюбленим інструментом, навіть маючи великий обсяг даних.