SQLでデータを抽出するユーザー定義関数を作ってみた

Office365やExcel2019で新しく搭載された「スピル」と言う機能があります。
スピル機能を使うと複数の答えを返す関数を従来よりも圧倒的に簡単にセルに展開することが出来ます。

この機能と相性が良いのでは思っていた、ExcelのデータをSQL文で集計した結果をセルに表示する関数を作ってみた。

※スピル機能自体はこちらをご参考ください。
スピルについて｜エクセル入門

excel-ubara.com

関数の動作

完成した関数を入力してみるとこのようになる。
f:id:ExcelLover:20200922201203g:plain

C1セルに関数を入力すると関数を入力したセルだけではなく、周りのセルにも答えが入力される。
関数を確定した際に青線で囲われている部分がスピル機能で答えが入力された範囲。

今回入力した関数は以下の通り。

=SQLSearch("select キャリア,sum(年齢) as 合計 from [sheet1$] group by キャリア order by sum(年齢) desc")

SQLSearchが今回作成した関数の名前で、カッコの中が引数になる。
引数は全てデータベースを操作するための言語、SQLの文章。

SQLの詳細な内容は割愛させていただくがこのSQL文をExcelの機能に置き換えて説明してみると、

・ピボットテーブルでキャリアを行項目にして、年齢の合計を求める。
・SUMIFS関数でキャリアごとの年齢の合計を求める。

と言う意味になる。
※ちなみに元データみんな大好き「なんちゃって個人情報」を利用させて頂きました。

SQLSearch関数のコード

Option Explicit
Public Function SQLSearch(SQL文 As String) As Variant
'###Microsoft ActiveX Data Objects XX Libraryの参照設定を行う
'###Excel2007以降に接続出来るように設定。設定を変更すれば別のデータベースに接続も可能。

Application.Volatile True
     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"
     
     Dim DBFullPath As String
     DBFullPath = ActiveWorkbook.FullName
     
     myCON.Open DBFullPath
     
     Dim myRS As ADODB.Recordset
     Set myRS = New ADODB.Recordset
     myRS.Open SQL文, myCON, adOpenStatic
  

'###RecordSetの行列並び替え→配列化
     Dim tmp As Variant
     If myRS.RecordCount = 0 Then
          tmp = "解無し"
          GoTo Ans
     End If
     
     Dim vvレコードセット件数 As Long
     Dim vvレコードセット項目数 As Long
     vvレコードセット件数 = myRS.RecordCount
     vvレコードセット項目数 = myRS.Fields.Count
     
     'Recordsetから配列に置き換え
     Dim vv行列並替前配列 As Variant
     ReDim vv行列並替前配列(vvレコードセット項目数 - 1, vvレコードセット件数 - 1)
     vv行列並替前配列 = myRS.GetRows
     
     '配列内での行・列置き換え
     Dim vv行列並替後配列 As Variant
     Dim i As Long
     Dim c As Long
     
     '行列並替前より配列件数を１つ増やすことでヘッダー情報を受けられるようにしている
     ReDim vv行列並替後配列(vvレコードセット件数, vvレコードセット項目数 - 1)
     For i = 0 To myRS.Fields.Count - 1
          'Recordsetのヘッダー情報を配列に代入
          vv行列並替後配列(0, i) = myRS.Fields(i).Name
     Next i
     For i = 0 To UBound(vv行列並替前配列)
          For c = 0 To UBound(vv行列並替前配列, 2)
               vv行列並替後配列(c + 1, i) = vv行列並替前配列(i, c)
          Next c
     Next i

     tmp = vv行列並替後配列
Ans:
     SQLSearch = tmp
End Function

From句の指定方法

この関数はExcel2007以降のバージョンのデータ集計を想定している。
SQLのFrom句にシート名や名前定義範囲を指定するときは以下のようにすれば良い。

シート名の場合

"select * from [sheet1$]"

シート名を[]で囲って、シート名の後ろに$をつける。

シートの特定のセル範囲の場合

"select * from [sheet1$A1:B10]"

シート名の指定の後に相対参照でセル範囲を記入。
絶対参照・複合参照($付)ではエラーになる。

名前定義の場合

"select * from 名前定義"

名前定義の場合はそのまま名前定義を書けばOK。

Excel2007以降ではもう１つセル範囲を表現する方法がある。
テーブル機能である。

テーブル名を名前定義範囲と同じ様に指定してもエラーになる。

その対応として別の関数を１つ作ることにしました。
※VBAのコード内で記載する場合はこちらの記事を参照ください。

www.excellovers.com

テーブル用関数

上記で紹介したVBAコード内で利用する方法の関数版です。

Public Function Table範囲アドレス取得(テーブル名 As String) As String
     Dim tmp As Variant
     tmp = Range(テーブル名).ListObject.Range.Address(False, False)
     Table範囲アドレス取得 = tmp
End Function

この関数を最初に紹介した関数のFrom句に使用することでテーブルをFrom句に指定できるようになります。

"select * from [" & Table範囲アドレス取得("テーブル名") & "]"

まとめ

取り敢えず、思ったようにスピルで動作するSQL検索関数を作成することができました。

実際に使ってみたところ、ブラッシュアップしたくなるような要素が既に何点か発生していますので、それについてはまた後日。

Excelが大好きだ！

Excel大好き経理マンがExcelの事を書き綴っていきます。