集計表をダブルクリックすると内訳を表示するようにしてみた(コード編)

以前にこの様な記事を公開した。

www.excellovers.com

どうやら実現できてヒャッハーと報告しただけで終わっていたようなので、今更ながらコードの紹介。

完成品

以前とほぼ同じだが、このような元データを用意した。

これをこのように集計している。

この集計データの詳細を見たい箇所をダブルクリックすると。

このように元データが表示される。

コード

集計表があるシートのイベント

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     
     If Not Intersect(Range("B2:C4"), Target) Is Nothing Then
          Dim CurryType As String: CurryType = Target.End(xlToLeft).Value
          Dim YearMonth As String: YearMonth = Format(Target.End(xlUp).Value, "yyyymm")
          
          DoubleClickDataReport CurryType, YearMonth
     End If
End Sub

標準モジュール

Sub DoubleClickDataReport(CurryType As String, YearMonth As String)
'参照設定でMicrosoft Active X Data Objects XX Libraryを設定
     Dim myCON As ADODB.Connection: Set myCON = New ADODB.Connection
     myCON.Provider = "Microsoft.ACE.OLEDB.12.0"
     myCON.Properties("Extended Properties") = "Excel 12.0;IMEX=1"
     
     myCON.Open ThisWorkbook.FullName
     
     Dim myRS As ADODB.Recordset: Set myRS = New ADODB.Recordset
     Dim mySQL As String
     
     mySQL = "select * from [Data$] where カレー = '" & CurryType & "' and "
     mySQL = mySQL & "format(日付,'yyyymm') = " & YearMonth
     myRS.Open mySQL, myCON, adOpenStatic
     
     Dim myWB As Workbook: Set myWB = Workbooks.Add
     Dim myWS As Worksheet: Set myWS = myWB.Worksheets(1)
     myWS.Range("A:A").NumberFormatLocal = "yyyy/mm/dd"
     Dim i As Long
     For i = 1 To myRS.Fields.Count
          myWS.Cells(1, i).Value = myRS.Fields(i - 1).Name
     Next
     myWS.Range("A2").CopyFromRecordset myRS
     myRS.Close
     Set myRS = Nothing
     myCON.Close
     Set myCON = Nothing
End Sub

シートイベント

Interscet関数を利用して集計表の値以外の部分をダブルクリックした時は処理を行わないように判定しています。

ダブルクリックしたセルが変数Targetに格納されています。

そのセルから左端まで移動した先のセルの値をカレーの種類。
上端まで移動した先のセルの値を年月情報としてそれぞれ変数に格納。

メイン処理であるDoubleClickDataReportに引数として渡しています。

標準モジュール(DoubleClickDataReport)

上記処理から引き継いだ引数を利用してSQL文を作成しています。

VBAでSQLを使用するには参照設定でMicrosoft Active X Data Objects XX Libraryを設定する必要があります。

ExcelでSQL文を使用する際にはテーブル名の記載に注意が必要です。

こちらの過去記事をご参照ください。

www.excellovers.com

SQLで抽出したデータを新しいブックに貼り付けて処理完了です。

同一ブック内に新しいシートを作成してデータを貼り付ける処理でも良いのですが、データを確認して閉じることが多いのでシートを削除するよりかはブックを閉じるほうが楽だと思うので、この様な処理にしています。

まとめ

ピボットテーブルでのダブルクリックのような挙動をどのような表でも実現できるこの機能は気に入っています。

もちろんこの機能を実現するには集計表を直打ちで作成するのではなく、元データをリスト形式で用意して集計する形式にする必要があります。

一見手間がかかるように思うかもしれませんが、このように元データを保持しておくと色々な場面でメリットを享受できます。