Зашёл у нас разговор о том, как строить запросы, аналогичные функционалу, имеющемуся в Excel (например, сводные). Или такие, результат которых в Excel очевидным и быстрым образом не получишь.
Зашёл у нас разговор о том, как строить запросы, аналогичные функционалу, имеющемуся в Excel (например, сводные). Или такие, результат которых в Excel очевидным и быстрым образом не получишь.AndreTM
Skype: andre.tm.007 Donate: Qiwi: 9517375010
Сообщение отредактировал AndreTM - Четверг, 06.06.2013, 01:07
Автоматизатор выражается в терминах Excel, а я - SQL.
Задача звучит так: есть две (три...сорок) таблиц. Ключевое поле - [Столбец 1]. Нужно просуммировать значения поля [Столбец 2] во всех таблицах по совпадению ключа.
Пишем запрос (в Jet использование [] аналогично заключению в апострофы - это строковая константа; * используем, поскольку известно, что количество столбцов в таблицах - одинаковое): [vba]
Код
SELECT * FROM `Лист 1$` t1 UNION ALL SELECT * FROM `Лист 2$` t2
[/vba] Этим мы получим полное объединение строк двух таблиц, как если бы скопировали их друг за другом. В Jet проблематично использовать вложенные запросы, поэтому промежуточный результат выгружаем на Лист4.
Пишем второй запрос - уже к объединённым данным. Сгруппируем данные по [Столбец 1], применим к [Столбец 2] групповую операцию "Сумма": [vba]
Код
SELECT t.Столбец1 AS [Столбец 1], Sum(t.Столбец2) AS [Столбец 2] FROM `Лист4$` t GROUP BY t.Столбец1
[/vba]Обратите внимание, что пришлось: - обозначить источник через алиас "t", для удобства работы с полями - дать определенное имя агрегатному полю (мы присвоили заново то, что было) - обращаться к имени поля можно, исключив пробелы и без апострофов - но лучше поля со спецсиволами в имени явно заключать в ограничители Ну и возвращаем результат запроса на Лист3.
Итоговые данные в той же таблице - это отдельная тема. Но при поддержке провайдером (OLE DB или ODBC) соответствующих фраз в выражении SELECT - это не представляет труда сделать в том же запросе. Или дописать отдельно уже формулами листа
(Обязательно при первом открытии файла нажмите на "Init" - инициализация источников повешена на кнопочку, не стал её прикручивать на WB_Open)
Автоматизатор выражается в терминах Excel, а я - SQL.
Задача звучит так: есть две (три...сорок) таблиц. Ключевое поле - [Столбец 1]. Нужно просуммировать значения поля [Столбец 2] во всех таблицах по совпадению ключа.
Пишем запрос (в Jet использование [] аналогично заключению в апострофы - это строковая константа; * используем, поскольку известно, что количество столбцов в таблицах - одинаковое): [vba]
Код
SELECT * FROM `Лист 1$` t1 UNION ALL SELECT * FROM `Лист 2$` t2
[/vba] Этим мы получим полное объединение строк двух таблиц, как если бы скопировали их друг за другом. В Jet проблематично использовать вложенные запросы, поэтому промежуточный результат выгружаем на Лист4.
Пишем второй запрос - уже к объединённым данным. Сгруппируем данные по [Столбец 1], применим к [Столбец 2] групповую операцию "Сумма": [vba]
Код
SELECT t.Столбец1 AS [Столбец 1], Sum(t.Столбец2) AS [Столбец 2] FROM `Лист4$` t GROUP BY t.Столбец1
[/vba]Обратите внимание, что пришлось: - обозначить источник через алиас "t", для удобства работы с полями - дать определенное имя агрегатному полю (мы присвоили заново то, что было) - обращаться к имени поля можно, исключив пробелы и без апострофов - но лучше поля со спецсиволами в имени явно заключать в ограничители Ну и возвращаем результат запроса на Лист3.
Итоговые данные в той же таблице - это отдельная тема. Но при поддержке провайдером (OLE DB или ODBC) соответствующих фраз в выражении SELECT - это не представляет труда сделать в том же запросе. Или дописать отдельно уже формулами листа
(Обязательно при первом открытии файла нажмите на "Init" - инициализация источников повешена на кнопочку, не стал её прикручивать на WB_Open)AndreTM
Просматривая форум последнии несколько недель, обратил внимание, что действительно много задач можно решить используя запросы. Возникла идея написать надстройку для excel для автоматизации этого. Идея такая, задаются несколько областей данных между которыми устанавливаютя связи, затем по "волшебной" кнопке данные переносятся, попутно фильтруясь, сортируясь, группируясь и т.д. Несколько дней назад начал реализацию, как Вы думаете взлетит такая идея?
Просматривая форум последнии несколько недель, обратил внимание, что действительно много задач можно решить используя запросы. Возникла идея написать надстройку для excel для автоматизации этого. Идея такая, задаются несколько областей данных между которыми устанавливаютя связи, затем по "волшебной" кнопке данные переносятся, попутно фильтруясь, сортируясь, группируясь и т.д. Несколько дней назад начал реализацию, как Вы думаете взлетит такая идея?PowerBoy
Nervнедавно опубликовал свой класс-обёртку для ADODB. Используя её, вам остается только написать сам текст запроса (естественно, "несколько областей и связи" при этом можно передать в один запрос обычным для синтаксиса JetSQL порядком).
PowerBoy,
Nervнедавно опубликовал свой класс-обёртку для ADODB. Используя её, вам остается только написать сам текст запроса (естественно, "несколько областей и связи" при этом можно передать в один запрос обычным для синтаксиса JetSQL порядком).AndreTM
Я делаю на DAO, невижу между ADO и DAO большой разницы, все равно запрос будет передан JetSQL. Я хочу написать свой построитель запросов, так как многие пользователи не знают SQL.
Я делаю на DAO, невижу между ADO и DAO большой разницы, все равно запрос будет передан JetSQL. Я хочу написать свой построитель запросов, так как многие пользователи не знают SQL.PowerBoy
Я обычно использую SQL к данным в Excel, чтобы поразить воображение собеседника - такого, который не подозревал о такой возможности И это в основном на уровне развлекухи или совсем несложного, но эффектного, боевого запроса к данным пользователя, когда сделал один раз, помог пользователю и забыл.
Я использую Range.CopyFromRecordset, когда мне надо вставить в Excel данные, полученные SQL-запросом из других, не Excel, источников. И это на уровне повседневной тиражируемой профессиональной работы для пользователей.
Более-менее серьезную работу с данными Excel в Excel посредством SQL-запросов считаю экономически нецелесообразной. Для этих целей существует Access, в который можно либо импортировать нужные таблицы Excel, сделав их "родными" таблицами Access, либо связать оригинальные таблицы Excel c файлом Access - способы работы при этом не отличаются от способов работы с родными таблицами.
На этом фоне пытаться писать свой движок для работы с данными Excel посредством SQL - будь ты хоть гений VBA или даже больше - выглядит донкихотством в борьбе с ветряными мельницами. И в конце концов напишется, грубо говоря, "еще один Access". НО! Пользователям по-любому имело бы смысл познакомиться именно с оригинальным (и официальным) Access - это увеличит их стоимость на рынке.
Переубедить меня могло бы появление SQL-запросов к НЕСОХРАНЕННЫМ книгам Excel - вот это была б сила! Но этого пока нет...
Поделюсь сокровенным, так сказать, ИМХО.
Я обычно использую SQL к данным в Excel, чтобы поразить воображение собеседника - такого, который не подозревал о такой возможности И это в основном на уровне развлекухи или совсем несложного, но эффектного, боевого запроса к данным пользователя, когда сделал один раз, помог пользователю и забыл.
Я использую Range.CopyFromRecordset, когда мне надо вставить в Excel данные, полученные SQL-запросом из других, не Excel, источников. И это на уровне повседневной тиражируемой профессиональной работы для пользователей.
Более-менее серьезную работу с данными Excel в Excel посредством SQL-запросов считаю экономически нецелесообразной. Для этих целей существует Access, в который можно либо импортировать нужные таблицы Excel, сделав их "родными" таблицами Access, либо связать оригинальные таблицы Excel c файлом Access - способы работы при этом не отличаются от способов работы с родными таблицами.
На этом фоне пытаться писать свой движок для работы с данными Excel посредством SQL - будь ты хоть гений VBA или даже больше - выглядит донкихотством в борьбе с ветряными мельницами. И в конце концов напишется, грубо говоря, "еще один Access". НО! Пользователям по-любому имело бы смысл познакомиться именно с оригинальным (и официальным) Access - это увеличит их стоимость на рынке.
Переубедить меня могло бы появление SQL-запросов к НЕСОХРАНЕННЫМ книгам Excel - вот это была б сила! Но этого пока нет...Gustav
Я обычно использую SQL к данным в Excel, чтобы поразить воображение собеседника
Ага
Цитата (Gustav)
На этом фоне пытаться писать свой движок для работы с данными Excel посредством SQL - ...
Тоже полностью согласен. Писать движок - смысла нет. Сделать несколько небольших удобных обёрток для запросов - смысл есть. И не обучать "массы пользователей" работе с запросами, а просто использовать их в собственном коде, по принципу "вот я вам сделал так, пользуйтесь, разбирать код по косточкам не обязательно". Что же касается направления пользователей по адресу Access... Подавляющее большинство [юзеров] на нём разработку делать не будет, поэтому использование Jet в Excel, вообще-то, целесообразно. Да и юзеры обычно пользуют в повседневной практике только "малый набор" (Word, Excel (+ Outlook, OneNote)), акцесса может и просто не быть.
Цитата (PowerBoy)
Я хочу написать свой построитель запросов, так как многие пользователи не знают SQL.
А чем пользователей перестал устраивать MSQuery?
Цитата (Gustav)
Я обычно использую SQL к данным в Excel, чтобы поразить воображение собеседника
Ага
Цитата (Gustav)
На этом фоне пытаться писать свой движок для работы с данными Excel посредством SQL - ...
Тоже полностью согласен. Писать движок - смысла нет. Сделать несколько небольших удобных обёрток для запросов - смысл есть. И не обучать "массы пользователей" работе с запросами, а просто использовать их в собственном коде, по принципу "вот я вам сделал так, пользуйтесь, разбирать код по косточкам не обязательно". Что же касается направления пользователей по адресу Access... Подавляющее большинство [юзеров] на нём разработку делать не будет, поэтому использование Jet в Excel, вообще-то, целесообразно. Да и юзеры обычно пользуют в повседневной практике только "малый набор" (Word, Excel (+ Outlook, OneNote)), акцесса может и просто не быть.
Цитата (PowerBoy)
Я хочу написать свой построитель запросов, так как многие пользователи не знают SQL.
А чем пользователей перестал устраивать MSQuery? AndreTM
Skype: andre.tm.007 Donate: Qiwi: 9517375010
Сообщение отредактировал AndreTM - Четверг, 06.06.2013, 11:16
Он собирает данные с внешних источников и в качестве таблиц показывает листы, я же хочу данные брать с текущей книги и в качестве таблиц хочу задавать произвольные области. Также не нашел в нем поддержки новых форматов книг excel.
Цитата
А чем пользователей перестал устраивать MSQuery?
Он собирает данные с внешних источников и в качестве таблиц показывает листы, я же хочу данные брать с текущей книги и в качестве таблиц хочу задавать произвольные области. Также не нашел в нем поддержки новых форматов книг excel.PowerBoy
PowerBoy, странный вы человек... "Если вы не нашли - значит этого нет".
В моем первом посте лежит файл, сделанный в Excel10 и тестируемый с помощью MSQuery. Конечно, в текущем виде запрос графически не отображается... ну так откройте построитель, исправьте команду запроса на, например, "SELECT * FROM `Лист3$A10:B12`" и посмотрите, что получится...
PowerBoy, странный вы человек... "Если вы не нашли - значит этого нет".
В моем первом посте лежит файл, сделанный в Excel10 и тестируемый с помощью MSQuery. Конечно, в текущем виде запрос графически не отображается... ну так откройте построитель, исправьте команду запроса на, например, "SELECT * FROM `Лист3$A10:B12`" и посмотрите, что получится... AndreTM
Skype: andre.tm.007 Donate: Qiwi: 9517375010
Сообщение отредактировал AndreTM - Четверг, 06.06.2013, 12:14
Товарищи, тема весьма любопытная (не знаю уж насколько практически применимая). Я к ней тоже некоторое время назад приложился. Основные проблемы из-за которых не пошло, это различия между разными версиями SQL-движков, а также их общая скудость (может, конечно, мало покопал), плюс привязка к абсолютному пути к источнику (с чем и попытался бороться макросами). Но и преимуществ хватает: половина проблем заявляемых на форуме решается простейшим запросом с одним JOIN'ом. Применимость запросов через ADO сильно ограничена, из-за того, что макросы надо включать, т.е. применимы практически только для внутреннего пользования (да и библиотеки могут не найтись). Так что, если б разобраться совместными усилиями со встроенными средствами очень былоб пользительно.
ЗЫ. И почему вся эта красота в "Общении"? Давайте её хотя б в "Другие приложения" переселим.
Товарищи, тема весьма любопытная (не знаю уж насколько практически применимая). Я к ней тоже некоторое время назад приложился. Основные проблемы из-за которых не пошло, это различия между разными версиями SQL-движков, а также их общая скудость (может, конечно, мало покопал), плюс привязка к абсолютному пути к источнику (с чем и попытался бороться макросами). Но и преимуществ хватает: половина проблем заявляемых на форуме решается простейшим запросом с одним JOIN'ом. Применимость запросов через ADO сильно ограничена, из-за того, что макросы надо включать, т.е. применимы практически только для внутреннего пользования (да и библиотеки могут не найтись). Так что, если б разобраться совместными усилиями со встроенными средствами очень былоб пользительно.
ЗЫ. И почему вся эта красота в "Общении"? Давайте её хотя б в "Другие приложения" переселим.Формуляр
Я был немного неправ насчёт вложенных запросов. Уже стал подзабывать ограничения Jet, и поспешил просто - лень было перечислять все столбцы и т.п. Выкладываю новый пример - получение сводных данных из нескольких источников - листов книг Excel одним запросом.
Заодно:
Цитата (Формуляр)
Основные проблемы из-за которых не пошло, это различия между разными версиями SQL-движков, а также их общая скудость (может, конечно, мало покопал), плюс привязка к абсолютному пути к источнику (с чем и попытался бороться макросами). Но и преимуществ хватает: половина проблем заявляемых на форуме решается простейшим запросом с одним JOIN'ом. Применимость запросов через ADO сильно ограничена, из-за того, что макросы надо включать, т.е. применимы практически только для внутреннего пользования (да и библиотеки могут не найтись).
Различий в синтаксисе самих команд запроса нет, поскольку выполняются они одним движком - Jet. Может быть, вы имели в виду различие в запросах при обращении к различным по форматам источникам данных, особенно к серверным СУБД? - тогда да. Привязка к абсолютному пути в источнике? - так это фича, а не бага, просто нужно это знать и учитывать. Кроме того, существуют такая вещь, как DSN - и при их использовании проблема источника выходит за рамки Excel-таблицы, что и позволяет создавать запросы без привязки к физическому пути Что касается "применимости запросов через ADO" и "библиотеки могут не найтись" - вообще не понял... ADO запросы не выполняет, он только организует адаптер (коннект к источнику данных), запрос же исполняется соответствующим движком. "Библиотеки" же - это проблема не Excel, а ОС или преинсталляции соответствующего драйвера...
Я был немного неправ насчёт вложенных запросов. Уже стал подзабывать ограничения Jet, и поспешил просто - лень было перечислять все столбцы и т.п. Выкладываю новый пример - получение сводных данных из нескольких источников - листов книг Excel одним запросом.
Заодно:
Цитата (Формуляр)
Основные проблемы из-за которых не пошло, это различия между разными версиями SQL-движков, а также их общая скудость (может, конечно, мало покопал), плюс привязка к абсолютному пути к источнику (с чем и попытался бороться макросами). Но и преимуществ хватает: половина проблем заявляемых на форуме решается простейшим запросом с одним JOIN'ом. Применимость запросов через ADO сильно ограничена, из-за того, что макросы надо включать, т.е. применимы практически только для внутреннего пользования (да и библиотеки могут не найтись).
Различий в синтаксисе самих команд запроса нет, поскольку выполняются они одним движком - Jet. Может быть, вы имели в виду различие в запросах при обращении к различным по форматам источникам данных, особенно к серверным СУБД? - тогда да. Привязка к абсолютному пути в источнике? - так это фича, а не бага, просто нужно это знать и учитывать. Кроме того, существуют такая вещь, как DSN - и при их использовании проблема источника выходит за рамки Excel-таблицы, что и позволяет создавать запросы без привязки к физическому пути Что касается "применимости запросов через ADO" и "библиотеки могут не найтись" - вообще не понял... ADO запросы не выполняет, он только организует адаптер (коннект к источнику данных), запрос же исполняется соответствующим движком. "Библиотеки" же - это проблема не Excel, а ОС или преинсталляции соответствующего драйвера...AndreTM
PowerBoy, не буду даже уточнять, что и где повреждено...
"Ваша надстройка" имеет право на существование, как и любая другая. В этой теме ещё ни одной "надстройки" не было показано, с чем вы сравниваете свою - непонятно.
Если вы не знали, то MSQuery - это построитель запросов. Зачем ему параметры "из ячейки листа" - неясно, ведь конечным продуктом работы MSQuery все равно будет либо объект коллекции Application.Connections, либо plain-text, описывающий параметры подключения и строку запроса. Нет, конечно, можно сделать запрос параметров - достаточно указать фразу [?имяпараметра] в любом месте инcтрукции SQL. Можно изменить текст запроса (который - обычная строка), дополнив его подстановкой нужного вам значения. Можно вообще поступить "по-взрослому" - т.е. организовать подзапрос к нужной ячейке листа прямо в запросе...
Может, вы всё же прочитаете что-либо на тему "JetSQL"?
PowerBoy, не буду даже уточнять, что и где повреждено...
"Ваша надстройка" имеет право на существование, как и любая другая. В этой теме ещё ни одной "надстройки" не было показано, с чем вы сравниваете свою - непонятно.
Если вы не знали, то MSQuery - это построитель запросов. Зачем ему параметры "из ячейки листа" - неясно, ведь конечным продуктом работы MSQuery все равно будет либо объект коллекции Application.Connections, либо plain-text, описывающий параметры подключения и строку запроса. Нет, конечно, можно сделать запрос параметров - достаточно указать фразу [?имяпараметра] в любом месте инcтрукции SQL. Можно изменить текст запроса (который - обычная строка), дополнив его подстановкой нужного вам значения. Можно вообще поступить "по-взрослому" - т.е. организовать подзапрос к нужной ячейке листа прямо в запросе...
Может, вы всё же прочитаете что-либо на тему "JetSQL"?AndreTM
спасибо! ого, подросло... это теперь мое актуальное кол-во сообщений, сосчитанное по тем разделам, где ведётся учёт? или абсолютно все мои сообщения форума?
[offtop]
Цитата (Serge_007)
Пересчитал
спасибо! ого, подросло... это теперь мое актуальное кол-во сообщений, сосчитанное по тем разделам, где ведётся учёт? или абсолютно все мои сообщения форума?Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
