Доброго времени суток, форумчане! Небольшой вопрос. Существует таблица, из которой нужно сделать выборку по многим (ну очень многим) критериям. Критерии задаются с помощью OptionButton на UserForm. Пробовал переносить таблицу в массив и прописывать условия с помощью If и Select Case, и все это прогонять через итерации. Получается очень громоздкая и долгодумающая конструкция. Наверняка существует более простое решение (с помощью SQL запросов, как в Access,например). Не подскажете строку подключения к книге Excel как к базе данных через ADO? Результат выборки достаточно прописать в Debug.Print в виде количества отобранных по заданному условию записей. Например "Условию соответствует N записей". Заранее спасибо! Примерчик прилагаю, условия по минимуму, взято из Борея (жмем на кнопочку).
SQL запросы к книгам Excell
|
02.09.2010 18:38:11
|
|
|
|
|
|
02.09.2010 19:40:25
<BR> <BR> <BR> <BR> <BR>
KL
|
|
|
|
|
|
02.09.2010 19:52:30
To R Dmitry: Да. Именно так. Сами запросы я формировать умею. Только как обратиться к таблице Excel как к БД?
To KL: Спасибо! Пробегусь по ссылкам (давно хотел сбросить пару кило:)). Возможно решение там найдется. |
|
|
|
|
|
02.09.2010 20:03:50
{quote}{login=KlerKK}{date=02.09.2010 07:52}{thema=Re: SQL запросы к книгам Excell}{post}To R Dmitry: Да. Именно так. Сами запросы я формировать умею. Только как обратиться к таблице Excel как к БД?
To KL: Спасибо! Пробегусь по ссылкам (давно хотел сбросить пару кило:)). Возможно решение там найдется.{/post}{/quote} попробуйте рекордером записать создание подключения OLEDB к xls да и ссылки хорошие вам дали
|
|||
|
|
|
|
02.09.2010 20:21:06
{quote}{login=KlerKK}{date=02.09.2010 07:52}{thema=Re: SQL запросы к книгам Excell}{post}To KL: Спасибо! Пробегусь по ссылкам (давно хотел сбросить пару кило:)). Возможно решение там найдется.{/post}{/quote}
В ссылках ответ на ваш вопрос лежит на самом видном месте, так что даже не запыхаетесь. Но если страдаете одышкой, то вот пример в вашем файле ;-)
KL
|
|
|
|
|
|
02.09.2010 20:36:01
Еще раз спасибо!!! Тему можно сворачивать, по-моему. Придется, видимо, как-то мирится со своей полнеющей тушкой на ТАКИХ форумах.:)
|
|
|
|
|
|
02.09.2010 20:36:28
Пардон, в коде была ошибка. Вот исправленный файл.
KL
|
|
|
|
|
|
03.09.2010 06:56:22
А я на таком запросе успокоился
SELECT 'Лист1$'.'Счет ГК','Лист1$'.'Материал','Лист1$'.'Наименование материала',Sum('Лист1$'.'Сумма в ВВ' ) AS 'СУММА' FROM `D:\TEMP\inv\s90`.`Лист1$` `Лист1$` GROUP BY 'Лист1$'.'Счет ГК','Лист1$'.'Материал','Лист1$'.'Наименование материала' !!! Здесь видно как идет адресация - название листа+$.столбец и в клаузе from полный путь к книге без расширения + имя листа с данными. Вроде можно и еще диапазон указать откуда данные тянуть ( не с первой строчки если надо - там шаманство со скобками [] ) но надобности не было ну а далее If Val(Mid(Application.Version, 1, InStr(1, Application.Version, ".") - 1)) > 11 Then namProv = "Microsoft.ACE.OLEDB.12.0" Else namProv = "Microsoft.Jet.OLEDB.4.0" End If и через ActiveSheet.QueryTables.Add с требуемыми параметрами добавить данные Сразу предупреждаю что если в столбце и текст и цифры то ничего хорошего из запроса не выйдет - Excel сканирует несколько строк и автоматом присваевает ему тип текст или число и не подходящие выкидывает |
|
|
|
|
|
03.09.2010 09:24:50
могу понять, что sql-запросы более гибки, кому-то проще...
но чтобы они были быстрее?..
Живи и дай жить..
|
|
|
|
|
|
03.09.2010 10:17:05
{quote}{login=слэн}{date=03.09.2010 09:24}{thema=}{post}могу понять, что sql-запросы более гибки, кому-то проще...
но чтобы они были быстрее?..{/post}{/quote} Они гибче - это кусок из разработаного шаблона который обращается к разным книгам (разной структуры ), выдергивает информацию из определенных столбцов (по названию) и формирует отчет. |
|
|
|
|
|
03.09.2010 10:47:58
с этим я и не спорю, только насчет скорости..
Живи и дай жить..
|
|
|
|
|
|
03.09.2010 11:50:09
{quote}{login=слэн}{date=03.09.2010 09:24}{thema=}{post}могу понять, что sql-запросы более гибки, кому-то проще...
но чтобы они были быстрее?..{/post}{/quote} Думаю, в данном примере sql вообще не требуется, а самое быстрое и адекватное - это продвинутый фильтр, но мы не знаем, что автор задумал в реальности раз он просит именно sql :-)
KL
|
|
|
|
|
|
03.09.2010 12:09:33
Мне кажется, что если загнать в массив и выбирать через If - тоже должно быстро получиться. Итерации не вижу, зачем нужны - один цикл перебора массива, выбор совпадений в другой массив и его вывод на лист.
|
|
|
|
|
|
03.09.2010 12:20:23
А агрегирование, фильтр и сортировка в SQL одним оператором?
|
|
|
|
|
|
03.09.2010 12:27:59
только записать легко, а выполняется..
Живи и дай жить..
|
|
|
|
|
|
03.09.2010 12:33:20
{quote}{login=Nicks}{date=03.09.2010 12:20}{thema=}{post}А агрегирование, фильтр и сортировка в SQL одним оператором?{/post}{/quote}
Агрегирования, трансформации, комбинирования, объединения, сортировки и группировки в задаче нет :-) Т.ч. фильтр - наиболее адекватный инструмент в данном случае. Думаю, что перебор массивов будет сильно помедленнее фильтра. Если же нужны вышеперечисленные манипуляции с данными, то SQL едва ли не самый медленный способ, но возможно наиболее компактный с точки зрения кода.
KL
|
|
|
|
|
|
03.09.2010 22:30:44
"Пробовал переносить таблицу в массив и прописывать условия с помощью If и Select Case, и все это прогонять через итерации. Получается очень громоздкая и долгодумающая конструкция. Наверняка существует более простое решение " - сделал это ради интереса, правда не придумал, куда итерации приткнуть. Получилось весьма шустрая конструкция - вероятно как раз то "более простое решение".
Для сравнения с SQL добавил в тот же код, да и вообще на его основе всё сделал, только поменял в своей части выбор условий - те не удачные, выбирать нечего. Ну и таймер, как положено... |
|
|
|
|
|
03.09.2010 23:46:43
Пожалуй, с массивами побыстрее фильтра будет :-)
KL
|
|
|
|
|
|
03.09.2010 23:52:17
Файл для сравнения.
KL
|
|
|
|
|
|
03.09.2010 23:58:26
Hugo, говорят, что Like медленный оператор, быстрее работает If InStr(...) > 0 then. Так что, если немного переделать, то должно быть ещё быстрее
P.S. Мне кажется, вам необходимо установить на своём компьютере надстройку Smart Indenter v. 3.5. Она автоматически расставляет (по нажатию Ctrl+Shift+M) отступы в коде, что позволяет легче читать код. |
|
|
|
|
|
04.09.2010 00:25:39
Первый :), спасибо за надстройку - действительно удобно. Чего я её не устанавливал?...
А насчёт Like - как-то особо за скоростью и не гнался, сделал просто чтоб показать ТС, что на массивах быстро :) Просто оставил от прежнего SQL :) Сам обычно Insr использую. |
|
|
|
|
|
04.09.2010 00:27:16
... конечно же InStr(...) ... слепой совсем... :)
|
|
|
|
|
|
04.09.2010 01:25:38
Немножко привёл коды в порядок и подписал формы, чтобы было понятно, что где ищется.
P.S. Что-то у меня не получилось прироста скорости с InStr. Получается, что Like чуть быстрее в данном случае (либо одинаково). Так же на равное с ним Расширенный фильтр.... |
|
|
|
|
|
04.09.2010 01:30:01
ещё чуть подправил, чтобы было понятнее
|
|
|
|
|
|
04.09.2010 01:37:43
Вот результаты:
Если искать Все - все: 1 место - через массивы 2 место - через SQL (в 2 раза медленнее массивов) 3 место - расширенный фильтр (в 4,5 раза медленнее массивов) Если искать, например, Продавец - Лондон 1 место - расширенный фильтр 2 место - через массивы 3 место - через SQL ВЫВОД: Если предполагается, что выборка будет небольшая, то лучше делать через расширенный фильтр. Если же выборка будет большая, то лучше делать через массивы |
|
|
|
|
|
04.09.2010 01:42:21
При чём, при поиске Продавец - Лондон разница между расширенным фильтром и массивами незначительная, то разница между расширенным фильтром и SQL почти 3-х кратная (SQL в 3 раза медленнее)
|
|
|
|
|
|
04.09.2010 01:49:02
переместил строку с замером времени после кода добавления листа "Отчёт", иначе создание листа будет входить в замер времени, а этого нам не нужно.
|
|
|
|
|
|
04.09.2010 02:07:02
Немножко подправил названия переменных, а то можно было запутаться.
|
|
|
|
|
|
04.09.2010 02:29:56
1 = Pavel55?
KL
|
||||
|
|
|
|||
Читают тему
