MovieMEMO

　参照設定

Excelマクロを使ってSQLサーバーと通信をする方法はいくつかあるかと思いますが、私がいつも行ってる方法をご紹介します。

1.まず[ツール]-[参照設定]から「参照設定」画面を起動します。

2.「Microsoft ActiveX Data Object x.x Library」にチェックが無ければチェックをし、[OK]ボタンを押します。(x.xはライブラリのバージョンです)

　SQLサーバーへの接続処理

下記のようにコーディングします。

'■ 変数設定
Dim oCon As ADODB.Connection

'■ オブジェクト生成
Set oCon = New ADODB.Connection

'■ SQLサーバーへ接続
oCon.Provider = "SQLOLEDB"
oCon.ConnectionString = "Persist Security Info=False;" & _
　　　　　　　　　　　　"Data Source=ホスト名 or IPアドレス;" & _
　　　　　　　　　　　　"Initial Catalog=データベース名;" & _
　　　　　　　　　　　　"User ID=ユーザID;" & _
　　　　　　　　　　　　"Password=パスワード"

　SQL文の発行処理

下記のようにコーディングします。(テーブル名：BumonTB をselectする)

'■ 変数設定
Dim oRS As ADODB.Recordset

'■ オブジェクト生成
Set oRS = New ADODB.Recordset

'■ SQL文の発行
oRS.ActiveConnection = oCon
oRS.Source = "select * from BumonTB"
oRS.Open

　値の取得

値を変数に代入する際、値が NULL の場合にエラーとなるため、NULLのチェックをした方が良いです。NULLのチェックをし、値を変数へ代入するには下記のようにコーディングします。

'変数定義
Type Bumon
　　Code As String
　　Name As String
End Type

Global BumonData(10000) As Bumon
Global MaxBumonCnt As Integer

'カウンターの初期化
MaxBumonCnt = 0
Do Until oRS.EOF
　　'値の代入 oRS.Fields("項目名")
　　If Not (IsNull(oRS.Fields("BumonCD"))) Then
　　　　BumonData(MaxBumonCnt).Code = oRS.Fields("BumonCD")
　　End If

　　If Not (IsNull(oRS.Fields("Bumon"))) Then
　　　　BumonData(MaxBumonCnt).Name = oRS.Fields("Bumon")
　　End If

　　'カウンターの更新
　　MaxBumonCnt = MaxBumonCnt + 1

　　'次のレコードに移る
　　oRS.MoveNext
Loop

　SQLサーバーの切断処理

SQLサーバーの切断方法は下記のようにコーディングします。

'■ SQLサーバーの切断
oRS.Close
oCon.Close

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing

　ピボットテーブルを作成するには(マニュアル編)

下記の様なデータが存在し、項目(各品目を年毎)で集計をしたい場合にピボットテーブルを使って集計すると便利です。

　　　　　　　　　↓
[挿入] - [ピボットテーブル] をクリックし、データの範囲を指定して[OK]ボタンを押します

　　　　　　　　　↓
下記画面が表示されるので、[行ラベル]に【年】、[列ラベル]に【品目】、[値]に【数量】をドラッグ＆ドロップします。

　　　　　　　　　↓
結果、下記のように各品目を年毎で集計した結果が出力する事ができました。

　ピボットテーブルを作成するには(マクロ編)

これをマクロで書くと、下記となります。

上記マクロの処理結果は下記となります。

　ピボットテーブル(マクロ編)の機能：集計方法

集計方法は「合計」だけではなく、下記のピボットテーブルの値フィールドのように「個数」「平均」・・など色々あります。

集計方法	値
合計	xlSum
データの個数	xlCount
平均	xlAverage
最大値	xlMax
最小値	xlMin
積	xlProduct
数値の個数	xlCountNums
標本標準偏差	xlStDev
標準偏差	xlStDevP
標本分散	xlVar
分散	xlVarP

例えば「合計」と「平均」それぞれで集計したい場合は、下記の様に書きます。

Dim objTable As PivotTable　　'ピボットテーブル
Dim objField As PivotField　　'ピボットテーブルのフィールド

'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select

'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard

'「年」の項目指定
Set objField = objTable.PivotFields("年")
With objField
　　.Orientation = xlRowField
End With

'「品目」の項目指定
Set objField = objTable.PivotFields("品目")
With objField
　　.Orientation = xlColumnField
End With

'「数量」の【合計】項目指定
Set objField = objTable.PivotFields("数量")
With objField
　　.Orientation = xlDataField
　　.Function = xlSum　　　　'集計方法を「合計」とする
　　.Caption = "合計"　　　　'項目名を「合計」とする
　　.NumberFormat = "#,##0"
End With

'「数量」の【平均】項目指定
Set objField = objTable.PivotFields("数量")
With objField
　　.Orientation = xlDataField
　　.Function = xlAverage　　　　'集計方法を「平均」とする
　　.Caption = "平均"　　　　'項目名を「平均」とする
　　.NumberFormat = "#,##0"
End With

上記マクロの処理結果は下記となります。

尚、上記のように合計・平均を縦並びではなく、下記のように横並びで見たいという要望があるかもしれません。また、タイトル行に色を付けたい、ソートで「年」を降順にしたい・・という要望もあるかもしれません。

この場合は、上記コードに下記コードを追記する事で実現できます。

'データフィールドの設定
With objTable.DataPivotField
　　.Orientation = xlColumnField '行に設定
　　.LabelRange.Interior.Color = 16777164 'データラベルの色
　　.DataRange.Interior.Color = 16777164 'データ項目の色
End With

'「年」の装飾
Set objField = objTable.PivotFields("年") '「年」の項目指定
With objField
　　.AutoSort xlDescending, "年" '降順の設定
　　.LabelRange.Interior.Color = 10092543 'ラベルの色
　　.DataRange.Interior.Color = 10092543 '項目の色
End With

'「品目」の装飾
Set objField = objTable.PivotFields("品目")
With objField
　　.LabelRange.Interior.Color = 16777164 'ラベルの色
　　.DataRange.Interior.Color = 16777164 '項目の色
End With

各項目の合計・平均ではなく、下記のように合計毎・平均毎に取り纏めて見たいという要望があるかもしれません。

この場合は、上記コードのデータフィールドの設定で、下記コードのようにPositionの位置を指定する事で実現できます。

'データフィールドの設定
With objTable.DataPivotField
　　.Orientation = xlColumnField '行に設定
　　.Position = 1
　　.LabelRange.Interior.Color = 16777164 'データラベルの色
　　.DataRange.Interior.Color = 16777164 'データ項目の色
End With

　ピボットテーブル(マクロ編)の機能：小計を非表示

行項目が１つだけであれば良いのですが、行項目が複数の場合、それぞれの項目毎に小計が自動集計されます。

下記サンプルは「年」の後に「年月」を入れる事により、「年」の小計が自動集計されてしまい、同じデータが重複して表示される事により見難くなってしまいました。(通常であれば12ヶ月なので年毎・月毎で表示した方が見やすいですが、あくまでサンプルとして記載してます)

このような場合に小計を非表示(下記ご参照)すると、見やすくなったと思います。

小計の設定は、Subtotals(インデックス) = True / Falseで行います。
インデックスの内容は下記となります。

インデックス	集計内容
1	自動
2	合計
3	データの個数
4	平均
5	最大値
6	最小値
7	積
8	数値の個数
9	標本標準偏差
10	標準偏差
11	標本分散
12	分散

デフォルトが1(自動)の場合、他の項目はFalseが設定されています。(MS「デベロッパーセンター」)

従ってSubtotals(1)=Falseを設定する事により小計を非表示にする事ができます。(下記サンプルの赤字ご参照)

Dim objTable As PivotTable　　'ピボットテーブル
Dim objField As PivotField　　'ピボットテーブルのフィールド

'集計データのシート・セルを指定する
ActiveWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Range("A1").Select

'ピボットテーブルのウィザード
Set objTable = ActiveSheet.PivotTableWizard

'「年」の項目指定
Set objField = objTable.PivotFields("年")
With objField
　　.Orientation = xlRowField
　　.Subtotals(1) = False
End With

'「年月」の項目指定
Set objField = objTable.PivotFields("年月")
With objField
　　.Orientation = xlRowField
　　.Subtotals(1) = False
End With

'「品目」の項目指定
Set objField = objTable.PivotFields("品目")
With objField
　　.Orientation = xlColumnField
　　.Subtotals(1) = False
End With

'「数量」の【合計】項目指定
Set objField = objTable.PivotFields("数量")
With objField
　　.Orientation = xlDataField
　　.Function = xlSum　　　　'集計方法を「合計」とする
　　.Caption = "合計"　　　　'項目名を「合計」とする
　　.NumberFormat = "#,##0"
End With

'「数量」の【平均】項目指定
Set objField = objTable.PivotFields("数量")
With objField
　　.Orientation = xlDataField
　　.Function = xlAverage　　　　'集計方法を「平均」とする
　　.Caption = "平均"　　　　'項目名を「平均」とする
　　.NumberFormat = "#,##0"
End With

　ピボットテーブル(マクロ編)の機能：全体の合計・平均を非表示

上記のようにピボットテーブルを作成すると、デフォルトで「総計」「全体の合計」「全体の平均」・・という集計がされます。必要な時もありますが、別々のカテゴリを集計する場合、意味ない情報が表示されるので、非表示にしたい！という要望があります。

非表示にする場合、行や列を非表示にする方法もありますが、行数や列数を考慮すると、非常に複雑なマクロを組むことになるので、単純に下記のように記述しましょう。

'全体の合計・平均を表示しない(行)
ActiveSheet.PivotTables(objTable.Name).ColumnGrand = False

'全体の合計・平均を非表示にする(列)
ActiveSheet.PivotTables(objTable.Name).RowGrand = False