【Excel】ピボットテーブルの書式が更新で消える!Excelのレイアウト保持オプションで書式を維持する

ピボットテーブルの書式設定が、データの更新時にリセットされてしまう問題に直面していませんか。

せっかく見やすく整形したのに、更新するたびに元に戻ってしまうのは非効率です。

この記事では、Excelの「レイアウト保持オプション」を使用して、ピボットテーブルの書式を更新後も維持する方法を解説します。

これにより、ピボットテーブルのメンテナンスにかかる時間を大幅に削減できます。

ピボットテーブルの書式が更新で消える原因

ピボットテーブルの書式がデータの更新によって消えてしまうのは、Excelのピボットテーブルのデフォルト設定に原因があります。

Excelは、ピボットテーブルのデータソースが変更された場合、元のレイアウトや書式を「リセット」して、新しいデータ構造に合わせた初期状態に戻そうとします。

これは、新しいフィールドが追加されたり、既存のフィールドが削除されたりした場合に、予期せぬ表示崩れを防ぐための動作です。

しかし、ユーザーが手動で設定した数値の桁区切り、通貨表示、条件付き書式などの書式も一緒にリセットされてしまうため、作業の効率を著しく低下させます。

レイアウト保持オプションで書式を維持する手順

ピボットテーブルの書式を更新後も維持するには、「レイアウト保持オプション」を使用します。

この設定を行うことで、データソースが更新されても、ユーザーが適用した書式が保持されるようになります。

1. ピボットテーブルの選択
   書式を維持したいピボットテーブル内の任意のセルをクリックして選択します。
2. ピボットテーブル分析タブの表示
   Excelのリボンに「ピボットテーブル分析」(または「オプション」)タブが表示されます。このタブをクリックします。
3. ピボットテーブルオプションの表示
   「ピボットテーブル分析」タブの中にある「オプション」グループから、「オプション」ボタンをクリックします。
4. レイアウトと書式タブの選択
   「ピボットテーブルオプション」ダイアログボックスが表示されます。この中から「レイアウトと書式」タブを選択します。
5. 書式設定のオプション設定
   「レイアウトと書式」タブの中に、「更新時に書式をクリア」というチェックボックスがあります。このチェックボックスのチェックを外します。
6. 設定の適用
   ダイアログボックスの下部にある「OK」ボタンをクリックして設定を保存します。

これで、ピボットテーブルのデータソースを更新しても、適用した書式が維持されるようになります。

更新時に書式が消える場合の追加設定

上記の設定を行っても書式がリセットされる場合、他の関連オプションが影響している可能性があります。

特に、「レイアウトと書式」タブにある他の設定項目を確認することで、問題が解決することがあります。

「更新時にレイアウトを変更する」オプションの影響

「レイアウトと書式」タブには、「更新時にレイアウトを変更する」というチェックボックスもあります。

このオプションが有効になっていると、データソースの構造変更(フィールドの追加・削除など)に伴って、ピボットテーブルのレイアウト自体も自動的に変更されます。

このレイアウト変更の過程で、ユーザーが設定した書式が意図せずリセットされることがあります。

対処法:

1. ピボットテーブルオプションの表示
   対象のピボットテーブルを選択し、「ピボットテーブル分析」タブから「オプション」を開きます。
2. レイアウトと書式タブの確認
   「レイアウトと書式」タブを選択します。
3. 「更新時にレイアウトを変更する」のチェックを外す
   このチェックボックスのチェックを外します。
4. 設定の適用
   「OK」ボタンをクリックして設定を保存します。

この設定により、データ更新時にレイアウトが自動変更されることを防ぎ、書式設定の保持に役立ちます。

「ネストされたフィールドのメンバーを折りたたむ」オプションの影響

ピボットテーブルで複数のフィールドを階層的に配置している場合、「ネストされたフィールドのメンバーを折りたたむ」というオプションが書式に影響を与えることがあります。

このオプションは、更新時にネストされたフィールドの展開状態をリセットします。

展開状態のリセットは、場合によっては書式設定の再適用を促すことがあります。

対処法:

1. ピボットテーブルオプションの表示
   対象のピボットテーブルを選択し、「ピボットテーブル分析」タブから「オプション」を開きます。
2. レイアウトと書式タブの確認
   「レイアウトと書式」タブを選択します。
3. 「ネストされたフィールドのメンバーを折りたたむ」のチェックを外す
   このチェックボックスのチェックを外します。
4. 設定の適用
   「OK」ボタンをクリックして設定を保存します。

このオプションを無効にすることで、展開状態が維持され、書式設定の安定性が向上します。

VBAマクロによる書式設定の自動適用

「レイアウト保持オプション」でも書式が維持されない、あるいはより複雑な書式設定を自動化したい場合は、VBAマクロを使用する方法も有効です。

VBAマクロを使えば、ピボットテーブルの更新イベントに紐づけて、書式設定を自動的に適用させることができます。

これは、特定の条件に基づいた条件付き書式や、複雑な数値フォーマットなどを確実に適用したい場合に強力な手段となります。

ピボットテーブル更新時に書式を適用するVBAコード例

以下は、ピボットテーブルの更新イベントをトリガーとして、特定の書式を適用するVBAコードの例です。

注意: VBAコードを実行するには、Excelファイルの拡張子を.xlsm(マクロ有効ブック)にする必要があります。

1. VBAエディタの起動
   ExcelでAlt + F11キーを同時に押して、VBAエディタ(Microsoft Visual Basic for Applications)を開きます。
2. シートモジュールの選択
   左側のプロジェクトエクスプローラーウィンドウで、ピボットテーブルが存在するシートをダブルクリックします。
3. コードの貼り付け
   右側のコードウィンドウに、以下のVBAコードをコピーして貼り付けます。

コード例:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    ' 対象のピボットテーブル名を指定します
    Const PIVOT_TABLE_NAME As String = "ピボットテーブル1" ' ここに対象ピボットテーブル名を入力

    If Target.Name = PIVOT_TABLE_NAME Then
        ' ここに適用したい書式設定コードを記述します
        ' 例: 数値フィールドの桁区切りを設定
        On Error Resume Next ' エラーが発生しても処理を続行
        Target.PivotFields("金額").NumberFormat = "#,##0"
        Target.PivotFields("数量").NumberFormat = "0"
        ' 例: 特定のセルに条件付き書式を適用
        Dim pc As PivotCell
        For Each pc In Target.DataBodyRange.Cells
            If pc.Value > 1000 Then
                pc.Interior.Color = RGB(255, 200, 200) ' 薄い赤色
            Else
                pc.Interior.ColorIndex = xlNone ' 色をクリア
            End If
        Next pc
        On Error GoTo 0 ' エラーハンドリングを元に戻す
    End If
End Sub

コードの説明:

上記のコードでは、`Worksheet_PivotTableUpdate`というイベントプロシージャを使用しています。

これは、ワークシート上のピボットテーブルが更新されたときに自動的に実行されるプロシージャです。

`PIVOT_TABLE_NAME`定数に、書式設定を適用したいピボットテーブルの名前を指定してください。

「金額」や「数量」といったフィールド名は、実際のピボットテーブルのフィールド名に合わせて変更する必要があります。

`Target.PivotFields(“フィールド名”).NumberFormat = “#,##0″` の部分で、数値の書式を設定しています。

また、`DataBodyRange`を使用してデータ範囲内の各セルをループし、条件に応じてセルの背景色を変更する例も示しています。

VBAエディタの保存と閉じる
コードを貼り付けたら、VBAエディタの「ファイル」メニューから「保存」を選択し、エディタを閉じます。

Excelファイルの保存
Excelファイル自体も、.xlsm(マクロ有効ブック)形式で保存してください。

このVBAコードを設定しておけば、ピボットテーブルを更新するたびに、指定した書式が自動的に適用されます。

ピボットテーブルの書式設定とレイアウトオプションの比較

ピボットテーブルの書式設定とレイアウトオプションの挙動を理解するために、それぞれの役割を比較します。

項目	レイアウト保持オプション	更新時の書式クリア(チェックあり)	VBAマクロによる書式適用
目的	更新後もユーザー設定の書式を維持する	更新時に書式を初期状態に戻す	更新時に指定した書式を自動適用する
設定方法	ピボットテーブルオプションの「レイアウトと書式」タブ	ピボットテーブルオプションの「レイアウトと書式」タブ	VBAコードの記述とマクロ有効ブックでの保存
適用範囲	ユーザーが手動で設定した書式全般	ユーザー設定の書式をリセットする	コードで指定した書式(数値、条件付き書式など)
柔軟性	標準機能で設定可能	標準機能で設定可能	非常に高い(複雑な条件や書式に対応可能)
メンテナンス	一度設定すれば維持される	一度設定すれば維持される	VBAコードの修正が必要になる場合がある
データ構造変化への対応	書式は維持されるが、レイアウト変更オプションに注意	書式はリセットされる	フィールド名変更などに対応できるようコードを修正する必要がある

「レイアウト保持オプション」は、手動で適用した書式を維持するための最も簡単な方法です。

しかし、より高度な自動化や、特定の条件に基づく書式設定を行いたい場合は、VBAマクロが強力な選択肢となります。

まとめ

ピボットテーブルの書式が更新で消える問題は、「レイアウト保持オプション」の設定を見直すことで解決できます。

具体的には、ピボットテーブルオプションの「レイアウトと書式」タブから「更新時に書式をクリア」のチェックを外すことで、適用した書式を維持できます。

もし、さらに複雑な書式設定や自動化が必要な場合は、VBAマクロの利用を検討してください。

これにより、ピボットテーブルのメンテナンスにかかる手間を大幅に削減し、より効率的にデータを分析できます。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】