Выполнение выборки Select на листе Excel

@shark555 · Регистрация: 03.02.2013

Author24 — интернет-сервис помощи студентам

Очень нужна помощь
есть таблица на фиксированном листе Worksheets("1704") cо столбца А по столбец Т, где первая строка название
- требуется создать лист отчет, куда в последствии вывести информацию
- выбрать записи где в столбце A нет записи "NAT MINSK", а в столбце E <> "RUB"
- в таблице могут быть одинаковые записи по полям N-Q, в таком случае надо просуммировать поле X1, а поле X2 определяется как суммированное X3 / X1

Visual Basic

Sub SSS_1()
Const N = 90
Dim i, p, g, iter As Integer
Dim c As Object
Dim sSql As String
 
 
 
    If IsWorkSheetExist("отчет") = True Then
        Worksheets("отчет").Delete
        Set NewSheet = Worksheets.Add
        NewSheet.Name = "отчет"
    Else
        Set NewSheet = Worksheets.Add
        NewSheet.Name = "отчет"
    End If
 
 
    Range("B2").Select
    Selection.End(xlDown).Select
    EndRow = ActiveCell.Row
       
    Worksheets("1704").Range("A2:T" & EndRow).Name = t01
    Worksheets("1704").Range("N").Name = "K1"
    Worksheets("1704").Range("O").Name = "K2"
    Worksheets("1704").Range("P").Name = "K3"
    Worksheets("1704").Range("Q").Name = "K4"
    Worksheets("1704").Range("R").Name = "X1"
    Worksheets("1704").Range("S").Name = "X2"
    Worksheets("1704").Range("T").Name = "X3"
    
 
    Sql = "select * from t01 where Worksheets("1704").Cells("A") <> "NAT MINSK" Or Worksheets("1704").Cells("E") <> "RUB" _
    Group by "K1", "K2", "K3", "K4" _
    Order by "K1", "K2", "K3", "K4", "X1", "X2", "X3" 
 
End Sub

@ikki · 10.02.2013, 22:22

вот этот приведенный вами код - к чему?
и почему его нет в прикрепленном файле?

Добавлено через 25 минут

Сообщение от shark555

в таблице могут быть одинаковые записи по полям N-Q, в таком случае надо просуммировать поле X1, а поле X2 определяется как суммированное X3 / X1

не уверен, что понял правильно.

Visual Basic

Sub t()
    Dim sCon$, rs As Object, cn As Object, w As Worksheet
    Dim sSQL$
    Set rs = CreateObject("ADODB.Recordset")
    Set cn = CreateObject("ADODB.Connection")
    Select Case CLng(Split(Application.Version, ".")(0))
        Case Is < 12
            sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName _
              & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
        Case Is >= 12
            sCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
            & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    End Select ' thnx R Dmitry
    
    sSQL = "SELECT COUNTERPARTY, CPTY_TCID, DIRECTION, INSTR_1, VOLUME," _
        & " RATE, START_DATE, END_DATE, PERIOD, INTEREST, BASIS, КУРС_НБРБ," _
        & " DEAL_ID, K1, K2, K3, sum(X1), sum(X3)/sum(X2), X4" _
        & " FROM [1704$A:T] WHERE COUNTERPARTY<>""NAT MINSK"" AND INSTR_1<>""RUB""" _
        & " GROUP BY COUNTERPARTY, CPTY_TCID, DIRECTION, INSTR_1, VOLUME," _
        & " RATE, START_DATE, END_DATE, PERIOD, INTEREST, BASIS, КУРС_НБРБ," _
        & " DEAL_ID, K1, K2, K3, X4"
    cn.Open sCon
    Set rs = cn.Execute(sSQL)
    Set w = ThisWorkbook.Worksheets.Add
    w.[a2].CopyFromRecordset rs
End Sub

заголовки на новом листе нарисуйте сами.

@shark555 · 10.02.2013, 22:35 **[ТС]**

Выдается сообщение
Compile Error Syntax Eror

@Watcher_1 · 10.02.2013, 22:39

У меня все отлично отработало...

@shark555 · 10.02.2013, 22:42 **[ТС]**

В таблице поля
K1 K2 K3 X1 X2 X3 X4
могут быть одинаковыми в этом случае требуется вывести
поле K1 K2 K3 X1 и просуммировать поле X2 и поле X3 должно быть взвешенным, т.е. X3 = X4/X2

Добавлено через 2 минуты
перегрузил Excel все работает

@ikki · 10.02.2013, 22:47

Сообщение от shark555

в этом случае требуется

ну переделайте по аналогии.
агрегатные функции в нужные места и список полей в предложении GROUP BY поменять соответственно

@shark555 · 10.02.2013, 22:56 **[ТС]**

к сожалению немного не то получилось
лишние строки удалены COUNTERPARTY<>"NAT MINSK" AND INSTR_1<>"RUB"
а вот с остальным не получилось
есть строки 6,7,8,9 где поля
K1 K2 K3 X1 идентичны
0645 001 2
0645 001 2
0645 001 2
0645 001 2

в этом случае строка должна быть один раз напечатана, а поле X2 суммироваться, поле X3 = X4/X2 (так называемая средне взвешенная ставка поле X4 ранее определялся, как произведение X2 * ставку)

@ikki · 10.02.2013, 23:05

Сообщение от shark555

есть строки 6,7,8,9 где поля
K1 K2 K3 X1 идентичны

гм... это больше становится похоже на работу, а не на помочь/подсказать...
ладно.
давайте по порядку.
у приведенных вами строк другие поля различны.
напр., COUNTERPARTY, CPTY_TCID, INSTR_1.
да почти все.
а в результат они попадают.
что выводить тогда?

возможно, вам имеет смысл нарисовать вручную пример результата для исходных данных?
иначе мне, например, задача до сих пор непонятна.
впрочем, возможно, это именно мои тараканы.

@shark555 · 10.02.2013, 23:32 **[ТС]**

Лист 1704 - первичный лист
Лист 1 промежуточный где видно какие столбцы анализируются и удаляются
Лист 2 результат

@shark555 · 10.02.2013, 23:33 **[ТС]**

"возможно, вам имеет смысл нарисовать вручную пример результата для исходных данных?
иначе мне, например, задача до сих пор непонятна.
впрочем, возможно, это именно мои тараканы. "
Очень разумно и большое спасибо

@ikki · 10.02.2013, 23:51

меняется только строка запроса. проверяйте.

Visual Basic

1
2
3

    sSQL = "SELECT K1, K2, K3, X1, sum(X2), sum(X4)/sum(X2), sum(X4)" _
        & " FROM [1704$A:T] WHERE COUNTERPARTY<>""NAT MINSK"" AND INSTR_1<>""RUB""" _
        & " GROUP BY K1, K2, K3, X1"

остальное то же самое

@shark555 · 11.02.2013, 01:31 **[ТС]**

если можно поясните что вот это

Visual Basic

Dim sSQL$
    Set rs = CreateObject("ADODB.Recordset")
    Set cn = CreateObject("ADODB.Connection")
    Select Case CLng(Split(Application.Version, ".")(0))
        Case Is < 12
            sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName _
              & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
        Case Is >= 12
            sCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
            & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
    End Select ' thnx R Dmitry

@ikki · 11.02.2013, 06:23

1) создание объектов Connection и Recordset биб-ки работы с БД.
2) формирование строки подключения к БД, в зависимости от версии вашего Excel

@b0s0ta · 21.12.2015, 13:28

Добрый день! Возникла похожая задача.
только в секции where указывается считаная дата код выглядит так:
Sub Netting()
Dim sCon$, rs As Object, cn As Object, w As Worksheet, d As String
d = Worksheets("Расчет").Range("B1").Value
Dim sSQL$
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")
Select Case CLng(Split(Application.Version, ".")(0))
Case Is < 12
sCon = "Provider=Microsoft.Jet.OLEDB.4.0

ata Source=" & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Case Is >= 12
sCon = "Provider=Microsoft.ACE.OLEDB.12.0

ata Source=" & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
End Select ' thnx R Dmitry

sSQL = "SELECT Sum(Покупка), Sum(Продажа)" _
& " FROM [Данные$A:H]" _
& " WHERE Валюта_1=""USD"" AND Дата_валютирования=""" & d & """" _
& " GROUP BY Валюта_1, Дата_валютирования"
cn.Open sCon
Set rs = cn.Execute(sSQL)
Set w = ThisWorkbook.Worksheets.Add
w.[a2].CopyFromRecordset rs
End Sub

дата считывается корректно, строка sql выглядит так: "SELECT Sum(Покупка), Sum(Продажа) FROM [Данные$A:H] WHERE Валюта_1="USD" AND Дата_валютирования="24.03.2015" GROUP BY Валюта_1, Дата_валютирования"

но при выполнении кода выдается ошибка "Несоответствие типов данных в выражении условия отбора"

Не могу понять что чему не соответствует.

@Казанский · 21.12.2015, 13:52

b0s0ta, дату он не поймет в таком виде. Измените две строки

Visual Basic

    Dim sCon$, rs As Object, cn As Object, w As Worksheet, d ' As String
'...
    sSQL = "SELECT Sum(Покупка), Sum(Продажа)" _
        & " FROM [Данные$A:H]" _
        & " WHERE Валюта_1=""USD"" AND Дата_валютирования=" & Format(d, "\#yyyy\/mm\/dd\#") _
        & " GROUP BY Валюта_1, Дата_валютирования"

@shark555 0 / 0 / 0 Регистрация: 03.02.2013 Сообщений: 17
	10.02.2013, 22:35 [ТС]	3
	Выдается сообщение Compile Error Syntax Eror 0

@Watcher_1 357 / 163 / 27 Регистрация: 21.06.2011 Сообщений: 350
	10.02.2013, 22:39	4
	У меня все отлично отработало... 0

@shark555 0 / 0 / 0 Регистрация: 03.02.2013 Сообщений: 17
	10.02.2013, 22:42 [ТС]	5
	В таблице поля K1 K2 K3 X1 X2 X3 X4 могут быть одинаковыми в этом случае требуется вывести поле K1 K2 K3 X1 и просуммировать поле X2 и поле X3 должно быть взвешенным, т.е. X3 = X4/X2 Добавлено через 2 минуты перегрузил Excel все работает 0

@ikki призрак 3263 / 891 / 119 Регистрация: 11.05.2012 Сообщений: 1,702 Записей в блоге: 2
	10.02.2013, 22:47	6
	Сообщение от shark555 в этом случае требуется ну переделайте по аналогии. агрегатные функции в нужные места и список полей в предложении GROUP BY поменять соответственно 0

@shark555 0 / 0 / 0 Регистрация: 03.02.2013 Сообщений: 17
	10.02.2013, 22:56 [ТС]	7
	к сожалению немного не то получилось лишние строки удалены COUNTERPARTY<>"NAT MINSK" AND INSTR_1<>"RUB" а вот с остальным не получилось есть строки 6,7,8,9 где поля K1 K2 K3 X1 идентичны 0645 001 2 0645 001 2 0645 001 2 0645 001 2 в этом случае строка должна быть один раз напечатана, а поле X2 суммироваться, поле X3 = X4/X2 (так называемая средне взвешенная ставка поле X4 ранее определялся, как произведение X2 * ставку) 0

@ikki призрак 3263 / 891 / 119 Регистрация: 11.05.2012 Сообщений: 1,702 Записей в блоге: 2
	10.02.2013, 23:05	8
	Сообщение от shark555 есть строки 6,7,8,9 где поля K1 K2 K3 X1 идентичны гм... это больше становится похоже на работу, а не на помочь/подсказать... ладно. давайте по порядку. у приведенных вами строк другие поля различны. напр., COUNTERPARTY, CPTY_TCID, INSTR_1. да почти все. а в результат они попадают. что выводить тогда? возможно, вам имеет смысл нарисовать вручную пример результата для исходных данных? иначе мне, например, задача до сих пор непонятна. впрочем, возможно, это именно мои тараканы. 1

@shark555 0 / 0 / 0 Регистрация: 03.02.2013 Сообщений: 17
	10.02.2013, 23:33 [ТС]	10
	"возможно, вам имеет смысл нарисовать вручную пример результата для исходных данных? иначе мне, например, задача до сих пор непонятна. впрочем, возможно, это именно мои тараканы. " Очень разумно и большое спасибо 0

@ikki призрак 3263 / 891 / 119 Регистрация: 11.05.2012 Сообщений: 1,702 Записей в блоге: 2
	11.02.2013, 06:23	13
	1) создание объектов Connection и Recordset биб-ки работы с БД. 2) формирование строки подключения к БД, в зависимости от версии вашего Excel 0