本ページには広告が含まれています。

Excel VBA ピボットテーブルを操作する【数値書式設定編】

VBAでピボットテーブルを操作する【数値書式設定編】
2021.03.21

ピボットテーブルの書式設定といてもたくさんありすぎるので、自分がこれだけは設定しておきたい項目だけに絞って解説します。

くるみこ
くるみこ

今回も、毎回手動で設定している手間を解消するための解説です。いろいろと応用できるように勉強しましょう(^^)/

わかりました(^^)/ 今回もよろしくお願いします！

【この記事でわかることは】
・ピボットテーブルの数値データを3桁ごとのカンマ区切りとマイナス数値は赤色表示にする方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel VBA ピボットテーブルを操作する【列幅自動調整編】
Excel VBA ピボットテーブルを更新する際に列幅が自動調整されないようにしたいという要望をマクロで解決します。せっかく調整していた列幅が勝手に変更されないように「更新時に列幅を自動調整する」チェックボックスをオフにするマクロの紹介です
www.kurumico.com
くるみこ
くるみこ

前回記事は、ボピボットテーブル更新時に列幅を自動調整させない方法の解説記事でした。よければ覗いてみてね(^^)/

スポンサーリンク

ボピボットテーブルの書式設定をしてみます

書式設定といっても、たくさんありすぎて困るので、一番設定したい数値フィールドの設定について見て行きます。数値フィールドは、３桁ごとにカンマが入っている表示の方が見やすいいですよね。でも、デフォルトでは書式が設定されていません。そして、面倒なのはピボットテーブルは数値フィールドの書式設定は、各フィールドごとにひとつづつ「フィールドの設定」で変更しなければなりません。

数値フィールドの書式を一括設定する

色々調べたところ、方法は３つありました。

1.各数値フィールド毎に設定していく
2.セル範囲に書式設定する
3.ピボットテーブルでも「DataBodyRange」でデータエリアだけを選択して設定する

２番目はフィールドの表示範囲が変更されることもあるため除外します。
ということで、１と３の方法を検証します。

数値フィールド毎に設定していく方法

'ピボットテーブルの数値フィールドの書式を変更する
Sub pvtNumFormat()
    Dim pvt     As PivotTable
    Dim pvi     As PivotItem
    Set pvt = ActiveSheet.PivotTables(1)
    For Each pvi In pvt.DataPivotField.PivotItems
        With pvt.PivotFields(pvi.Name)
            .NumberFormat = "#,##0;△#,##0"
        End With
    Next
End Sub

・６行目、数値フィールド DataPivotField の各フィールドアイテム PivotItems 毎にループ処理しています。
・８行目が、数値の書式設定部分です。
・実は、”#,##0;[赤]△#,##0” のようにマイナスを赤色で表示したかったけどできませんでした。
・この方法では、どうしても赤色表示させたい場合には、条件付き書式で文字色を変更する必要がありますね。

DataBodyRangeエリアを一括設定する方法

・ピボットテーブルもテーブル同様に「DataBodyRange」が使えるのでそれを利用します。

'ピボットテーブルのDataBodyRangeエリアを書式設定する
Sub pvtDataBRFormat()
    'データをカンマ区切りでマイナスを赤色表示
    With ActiveSheet.PivotTables(1).DataBodyRange
        .NumberFormatLocal = "#,##0;[赤]△#,##0" '
    End With
End Sub

・すごくシンプルに設定できました。マイナスの赤色表示も問題なく設定できました。
・「NumberFormatLocal」はピボットフィールドの設定には使えませんでした。
・セル範囲の書式設定と違って、データ増減でも書式がしっかり反映できて問題ないようです。

条件付き書式する場合のコード

・１番目のコードの最後にこのプロシージャを呼び出せば条件付き書式を設定できます。

'DataBodyRangeの範囲に条件付き書式設定する
Sub FormatConditionsSet()
    'DataBodyRangeの条件付き書式を初期化（削除）する
    ActiveSheet.PivotTables(1).DataBodyRange.FormatConditions.Delete
    ' データエリアを選択
    ActiveSheet.PivotTables(1).PivotSelect "", xlDataOnly
    '条件設定 = 0より小さい場合
    Selection.FormatConditions.Add Type:=xlCellValue, _
        Operator:=xlLess, Formula1:="0"    '「0」より小さい場合
    '文字を赤色に変更する
    Selection.FormatConditions(1).Font.ColorIndex = 3
End Sub

・４行目で、既存の条件付き書式を削除して初期化います。
・６行目で、データ範囲を選択しています。
・８～11行目で、「0」より小さい場合という条件を設定し、条件に合う場合は文字を赤くするように設定しています。

結論は

3.「DataBodyRange」でデータエリアだけを選択して設定する方法を使うようにします！
【理由】なんといってもコードもシンプルで、一瞬で全部の設定が完了することです。

「1.各数値フィールド毎に設定していく方法」については、この方法が正当なんだろうなと思います。でも、どうしてもマイナスを「赤文字」で表示したかったのと、各フィールドを一つづつ設定するのは項目数が多ければ時間がかかりますよね。
今回見つけられなかった「赤文字」にする方法が見つかれば再度検討したいなと思います。

スポンサーリンク

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

どうですか。なんだかちょっとスッキリしませんでしたが、数値データフィールドの書式設定方法がわかりましたね。条件付き書式設定の方法もわかったので良かったと思います(^^)

書式設定って難しいですね！結構苦労しました。もっといろいろ試して勉強しなくちゃいけないですね(^^;

くるみこ
くるみこ

次回は、ピボットテーブルのオートフォーマットを使う方法について勉強したいと思います。楽しみにしていてね(^^)/

【今回分かったことは】
・ピボットテーブルの数値データを3桁ごとカンマ区切りにする方法とマイナスの数値を赤色表示に設定する方法がわかりました
・条件付き書式を設定する方法がわかりました

★★★ ランキング参加中！ クリックしてね(^^)/ ★★★

PVアクセスランキング にほんブログ村

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

人気ブログランキングアイコン
Visual Basicランキング

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

スポンサーリンク
エックスサーバーバナー広告
スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようリンク先に登録しています！

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com