サイトアイコン あきらちんの技術メモ

EXCEL VBAでSQL文を使って集計する

akiratin

EXCELを利用していて、これSELECT文でGROUP BYが使えたら楽なのになぁなどと思っておりまして、調べてみましたら使えるじゃありませんか！！

そんなわけでサンプル掲載です。

追記（2019/01/25）　複数ブックの対応について、こちらに対応案を掲載しました。

応用（2019/06/38）　オリジナルカレンダーを作成する

追記（2020/08/07）　INSERT、UPDATE、DELETEを試してみました。こちら。

動作確認

EXCEL2013

１．EXCELで一覧表を用意します

サンプルなので簡単なものですが、A1から順にE1までタイトルを付けます。これが列名になります。

データはA2からセットします。

給料のセルには、=C2*D2 （勤務時間×時給）の計算式が入っています。

２．集計用のシートを用意します

Sheet2を追加してボタンを設置します。

  1. 開発タブから挿入を選択し、ActiveXコントロールのコマンドボタンを選択します。
  2. ボタンを右クリックしてプロパティを表示します。
  3. プロパティの(オブジェクト名)とCaptionにそれぞれ集計と入力します。

集計結果を出力するセルのタイトルを入力します。A9～D9のセル

集計ボタンをダブルクリックすると Visual BASIC Editor が表示されます。

３．集計ボタンをクリックしたときの処理を記述します

Private Sub 集計_Click() と End Sub の間に以下のコードを貼り付けます。

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim xl_file As String
    Dim sql As String
    Dim curRow As Integer
    
    'ツールメニューの参照設定'
    ' Microsoft ActiveX Data Objects 2.8 Library'
    'チェック'
    xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'

    Set cn = New ADODB.Connection
    cn.Provider = "MSDASQL"
    #If Win64 Then
        ' 64bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #Else
        ' 32bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #End If
    cn.Open

    Set rs = New ADODB.Recordset

    sql = "SELECT" _
        & "  名前, COUNT(*) AS 出勤日数, SUM(勤務時間) AS 時間計, SUM(給料) AS 給料計" _
        & "  FROM [Sheet1$]" _
        & "  GROUP BY 名前" _
        & "  ORDER BY 名前"
    rs.Open sql, cn, adOpenStatic

    ' 表示データクリア
    Sheets("Sheet2").Range("A10:D100").Value = ""

    curRow = 10
    Do Until rs.EOF
        Sheets("Sheet2").Range("A" & curRow).Value = rs!名前
        Sheets("Sheet2").Range("B" & curRow).Value = rs!出勤日数
        Sheets("Sheet2").Range("C" & curRow).Value = rs!時間計
        Sheets("Sheet2").Range("D" & curRow).Value = rs!給料計
        rs.MoveNext
        curRow = curRow + 1
    Loop

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

ツールメニューの参照設定から Microsoft ActiveX Data Objects 2.8 Library にチェックを付けてOKボタンをクリックします。

４．出来上がったEXCELファイルを保存します

早く実行したいと思うかもしれませんが、一度保存しないと正常に動作しませんので注意が必要です。

ファイル＞名前を付けて保存から

ファイル名はお好きな名前を付けてください。

ファイルの種類を 「EXCELマクロ有効ブック（*.xlsm）」にして保存します。

５．それでは集計してみましょう

開発タブを選択して「デザインモード」が選択状態になっていますのでクリックして解除します。

「集計」ボタンをクリックします。上手く集計されたでしょうか？

６．ポイント解説

集計の元になるSheet1が別のEXCELファイルの場合は、以下の部分を書き換えます。

xl_file = ThisWorkbook.FullName '他のブックを指定しても良い'

一覧表.xlsx ファイルが、この集計のファイルと同じフォルダに保存されている場合は、次の様に書き換えます。

xl_file = ThisWorkbook.Path & "\一覧表.xlsx"

EXCELが32ビット版か64ビット版かで記述が変わります。

#If Win64 Thenで判定しcn.ConnectionStringを切り替えています。

    #If Win64 Then
        ' 64bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #Else
        ' 32bit
        cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    #End If

Sheet1のA1～E1列のタイトルが列名になります。

名前毎に、件数をカウントし勤務時間と給料をそれぞれ集計し名前順に結果を出力します。

ASの後ろにカウントや集計した結果の名前を付けます。

テーブル名に、[Sheet1$] と指定するのがポイントです。

    sql = "SELECT" _
        & "  名前, COUNT(*) AS 出勤日数, SUM(勤務時間) AS 時間計, SUM(給料) AS 給料計" _
        & "  FROM [Sheet1$]" _
        & "  GROUP BY 名前" _
        & "  ORDER BY 名前"

 

＜＜＜　特記事項　＞＞＞

別ファイルとなっているEXCELブックを参照する場合に、EXCELブックがパスワード保護されていると以下の様なエラーが発生します。パスワードを解除してから再度お試しください。また、ネットワーク上のEXCELブックにアクセスする場合にもエラーが発生する場合がありますので、EXCELブックをPCに保存してからお試しください。

実行時エラー’2147467259(80004005)’:
[Microsoft][ODBC Excel Driver]一般エラー レジストリ　キー’Temporary(volatile)
Ace DSN for process ～～～ Thread ～～～ DBC ～～～
Excelを開く事ができません。

 

最後に

複数シートをJOINすることも出来るので、EXCELの関数を駆使しなくてもSQLの知識があれば便利に使えます。

給料明細のフォーマットを別のシートに用意しておき連続で印刷させることもできるのでVBAが上手く利用出来ればEXCELが格段に便利なツールになります。

モバイルバージョンを終了