VBAでピボットテーブルに日付データを表示

ショートカット

VBAでピボットテーブルに日付データを表示

VBAでピボットテーブルに日付データを表示

ピボットテーブルに日付を表示する方法です。VBAでピボットテーブルを作成する方法は下記の記事を参照してください。

VBAでピボットテーブルを作成するピボットテーブルは挿入タブのピボットテーブルから手動で作成できますが、VBAを使って自動で作成することもできます。例えば、データシートからピボットテーブルを作成するVBAコード例は下のような感じです。 ...

ピボットテーブルは基本的にデータの集計に使うものなので、日付をデータに含めるとピボットテーブル上では下のような表示になってしまいます。

excel1

このように、日付が表示されるのではなく、日付が個数として集計されてしまいます。そもそもピボットテーブルに日付を表示させるべきではない気もしますが、そういった要望があることもあります…

取り込む日付データをシリアル値に変換する

これを無理やり日付として表示させるには、ピボットテーブルに取り込む日付データをシリアル値にします。日付文字列をシリアル値に変換する方法は下を参考にしてください。DATEVALUE関数を使います。

Excel：日付文字列をシリアル値に変換するには（DATEVALUE関数） - 教えて！HELPDESK

文字列として入力されている日付データ（日付文字列）をシリアル値に変換するには、DATEVALUE関数を使います・・・

DATEVALUEに指定する値のカラムは「文字列」形式になっていないとエラーになるので注意してください。

シリアル値に変換すると下のような数値になると思います。

excel2

シリアル値をピボットテーブルに取り込み日付として表示

これでピボットテーブルに取り込む準備ができました。

あとは取り込む際に、文字列形式で表示できるようにVBAで整形します。整形のコードは下記です。

Set pivot = stockSheet.PivotTables("ピボットテーブル")

'ピボットテーブルに追加する行、値のリストを設定
RowFieldsArray = Array("品名")
columnFieldsArray = Array("売上高", "在庫数", "在庫金額", "集計日")

'ピボットテーブルに行フィールドを追加
pivot.AddFields RowFields:=RowFieldsArray

'ピボットテーブルに値フィールドを追加
For i = 0 To UBound(columnFieldsArray)
    '日付データの場合
    If columnFieldsArray(i) = "集計日" Then
        pivot.AddDataField _
            Field:=pivot.PivotFields(columnFieldsArray(i)), _
            Caption:=columnFieldsArray(i) + " ", _
            Function:=xlSum '合計として集計
        'データフォーマットを日付に設定
        pivot.PivotFields(columnFieldsArray(i) + " ").NumberFormat = "yyyy/mm/dd"
    'それ以外のデータ
    Else
        pivot.AddDataField _
            Field:=pivot.PivotFields(columnFieldsArray(i)), _
            Caption:=columnFieldsArray(i) + " "
    End If
Next

上のコードの詳細は下記記事で詳しく説明しています。

VBAを使ってピボットテーブルに行と列を設定する

VBAでピボットテーブルに行と列を設定 VBAで作成したピボットテーブルに行と列を設定する方法です。VBAでピボットテーブルを作成する方法は下記を参照してください。ピボットテーブルに行と列を設定するコード例は下のような感じです。（※この記

合計で集計しフォーマットを日付に設定

ポイントはピボットテーブルにセットする際に、集計方法を合計に、表示フォーマットを日付に設定する下記の部分です。

pivot.AddDataField _
    Field:=pivot.PivotFields(columnFieldsArray(i)), _
    Caption:=columnFieldsArray(i) + " ", _
    Function:=xlSum '合計として集計
'データフォーマットを日付に設定
pivot.PivotFields(columnFieldsArray(i) + " ").NumberFormat = "yyyy/mm/dd"

Functionで集計方法を指定します。また、NumberFormatを指定して日付表示するようにしています。

うまくいっていれば、下のように日付として表示されます。

excel3