|
▼MoritaKさん、Hirofumi さん:
こんにちは。
>Accessの環境では「グループ化」集計を簡単に行えますけれど、
Access にお慣れでしたら、SQL で解決も可能ですね。
Sub SQLで()
Dim strSQL As String
Dim ADORS As Object
Dim ADOCon As Object
Dim i As Long
On Error Resume Next
Set ADOCon = CreateObject("ADODB.Connection")
ADOCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
If Err.Number <> 0 Then MsgBox "ADO接続に失敗": Exit Sub
On Error GoTo 0
strSQL = "SELECT 年月日, 検疫所, 検疫サンプル名, " _
& "Sum([24:00]) AS [24:00の合計], " _
& "Sum([6:00]) AS [6:00の合計], " _
& "Sum([12:00]) AS [12:00の合計], " _
& "Sum([18:00]) AS [18:00の合計], " _
& "Sum(計) AS 計の合計 " _
& "From [Sheet1$] " _
& "GROUP BY 年月日, 検疫所, 検疫サンプル名;"
Set ADORS = ADOCon.Execute(strSQL)
With Sheets("Sheet2")
For i = 0 To ADORS.Fields.Count - 1
.Cells(1, i + 1).Value = ADORS(i).Name
Next
.Range("A2").CopyFromRecordset ADORS
End With
ADORS.Close: Set ADORS = Nothing
ADOCon.Close: Set ADOCon = Nothing
End Sub
|
|