Excelの集計をSQLでおこなう方法。VLOOKUP()やSUBTOTAL()の限界を超えろ!!

| コメント(2) | トラックバック(0)

最近、ちょっとしたデータを集計することがあったのですがエクセル関数 だけでは手間がかかりすぎるため。自動化をかねSQL文できるよう VBAで作ってみました。

サンプルDLESQL.xls


下記AとBというシートがあるとします。

 /* Aシート(販売履歴) */
 No |   CID |   Products  |  Cost  | date
 ------------------------------------------
 1  |  001  |  リンゴ     | 100    | 2010/9/1
 2  |  002  |  ナシ       | 150    | 2010/9/3
 3  |  003  |  バナナ     | 110    | 2010/9/3
 4  |  004  |  バナナ     | 110    | 2010/9/4
 4  |  005  |  ブドウ     | 200    | 2010/9/20
 5  |  001  |  リンゴ     | 100    | 2010/9/25


 /* Bシート(顧客情報) */
 CID    | NAME
 -----------------
 001    | 織田信長
 002    | 豊臣秀吉
 003    | 徳川家康
 004    | 坂本龍馬
 005    | 夏目漱石</pre>

ここで取り出したい内容として

  • 各「Products」は何個うれたのか?
  • また売上高はいくらになるのか?

従来の集計では、オートフィルター設定をし、 フィルター結果を集計するSUBTOTAL()関数を使っていました。

しかしながら、 Productsの数が増えれば増えるほど ちまちまオートフィルタをいじっていたので時間がかかります。

そこで、SQLだと下記文で簡単に集計できるので作ってみました。

sql
SELECT
        Products,
        count(*) as [cnt],
        sum(cost) as [sum]
FROM
        [A$]
GROUP BY
        Products

操作は簡単、「SQL」シートのB1セルにに上記文を貼りつけして ボタンを押すだけです!!

なんと、シート同士を結合することも可能なのでvlookup()を 指定せずともお手軽便利!!

他にもいろんな集計方法をサンプルで載せておきます。

sql
/* Aシート一覧 */
 select * from [A$]

/* A,Bシート一覧 */
 select * from [A$],[B$] where  [A$].CID = [B$].CID

/* バナナの販売数 */
 select count(*) from [A$] where Products = 'バナナ'

/* バナナの売上 */
SELECT
        Products,
        sum(cost) as [sum]
FROM
        [A$]
WHERE
        Products = 'バナナ'
GROUP BY
        Products

/* 各商品の販売数 */
SELECT
        Products,
        count(*) as [cnt],
        sum(cost) as [sum]
FROM
        [A$]
GROUP BY
        Products

/* 顧客ごとの商品購入数と売上 */
SELECT
        [B$].CID,
        [B$].NAME,
        Products,
        count(*) as [cnt],
        sum(cost) as [sum]
FROM
        [A$],
        [B$]
WHERE
        [A$].CID = [B$].CID
GROUP BY
        [B$].CID,
        [B$].NAME,
        Products

/* 上記のコストが300以上の商品 */
SELECT
        [B$].CID,
        [B$].NAME,
        Products,
        count(*) as [cnt],
        sum(cost) as [sum]
FROM
        [A$],
        [B$]
WHERE
        [A$].CID = [B$].CID
GROUP BY
        [B$].CID,
        [B$].NAME,
        Products
HAVING
        sum(cost) >= 300

実装方法としては下記のVBAのコードです。

vba
Sub ESQL()
    Dim sql
    Dim str

    sql = Range(SQLCell).Value

    'ツール → 参照設定 →'
    ' Microsoft ActiveX Data Objects 2.8 Library'
    'チェック'
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim xl_file As String

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

    Set cn = New ADODB.Connection
    cn.Provider = "MSDASQL"
    cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & "DBQ=" & xl_file & "; ReadOnly=False;"
    cn.Open


    Set rs = New ADODB.Recordset

    rs.Open sql, cn, adOpenStatic


    'フィールドNAME表示'
    For i = 0 To rs.Fields.Count - 1
        Cells(oRow, i + 1).Value = rs(i).Name
    Next

    'カラム表示'
    j = oRow + 1
    Do Until rs.EOF
      '1 レコード毎の処理'

        For i = 0 To rs.Fields.Count - 1
            Cells(j, i + 1).Value = rs(i).Value
        Next

        j = j + 1
        rs.MoveNext
    Loop

    rs.Close
    cn.Close

End Sub

サンプルDLESQL.xls

トラックバック(0)

トラックバックURL: http://mukaer.com/cgi-bin/mt/mt-tb.cgi/34

コメント(2)

これはイイ!(・∀・)
活用させていただきます!

SQLで直接Excelのデータを検索!

コメントする

PR

PR





検索

Loading

メニュー

twitter