Excel ピボットテーブルの条件付き書式は無効になります – この非表示の設定を有効にするまで

条件付き書式とピボットテーブルは Excel の 2 つの最も強力な機能ですが、常にうまく連携するとは限りません。標準のカラー スケールまたはデータ バーをピボットテーブルに適用すると、更新、フィルター、またはレイアウトの変更によってすぐに問題が発生する可能性があります。幸いなことに、Excel には、書式設定ルールの範囲をワークシートの固定範囲ではなくフィールドに限定する、あまり知られていないピボットテーブル対応モードが含まれています。

ピボットテーブルの値フィールドに組み込みルールを適用するのは、思っているよりも簡単です

コンテキストアクションタグがすべてを変える

次のピボットテーブルがあるとします。 部門 で 行 フィールドと 利益の合計 で 価値観 フィールドにカラー スケールを適用したいとします。 利益の合計 カラム。

これを行うには:

1. を選択してください 単一値セル 内で 利益の合計 カラム。

2. を開きます 家 タブ。

3. を展開します。 条件付き書式設定 ドロップダウン メニュー。

4. マウスオーバーします カラースケールを選択し、 緑-黄-赤 オプション。

この時点では、選択したセルのスコープがまだピボットテーブル フィールドに設定されていないため、書式設定は選択したセルにのみ適用されます。

書式設定されたセルをクリックすると、Excel に 書式設定オプション アクションタグ。デフォルトでは、 選択されたセル はアクティブですが、重要なのはこの選択を変更することです。

• (フィールド名) の値を示すすべてのセル 合計を含む列内のすべてのセルに書式設定を適用します。これは、分散分析など、合計を計算の一部として含める必要がある場合に便利ですが、比較のコンテキストでは混乱を引き起こす可能性があります。
• (行/列フィールド名) の (フィールド名) 値を表示するすべてのセル 総計と小計は除外されます。合計には基になるデータとは異なるスケールが使用されることが多いため、これはほとんどのダッシュボードにとってより良い選択です。

ワークシートにさらに変更を加えると、すぐに「書式設定オプション」アクション タグが表示されなくなります。オプションに再度アクセスするには、をクリックします。 ホーム > 条件付き書式設定 > ルールの管理をクリックし、ルールを選択して、 ルールの編集 同じピボットテーブルのフィールド レベルのオプションにアクセスします。

これらのオプションが機能するのは、Excel がピボットテーブルの値フィールドを静的なセル範囲ではなく構造化オブジェクトとして扱うためです。その結果、ピボットテーブルの更新、フィールドの移動、レポート レイアウトの切り替え、行ラベルと列ラベルの名前変更など、ほとんどの日常的な操作を通じて書式設定が保持されます。

さらに良いことに、スライサーを使用したり、他のフィルターを適用したりすると、書式設定が現在画面上に表示されているものに適応するため、この機能はインタラクティブなダッシュボードに特に役立ちます。

注意すべき点：構造変化

ピボットテーブル対応の条件付き書式設定は一般に安定していますが、ルールの動作に影響を与える可能性のある構造上の変更がいくつかあります。

• フィールドの削除と再追加: ピボットテーブルからフィールドを削除して再度追加すると、Excel はそのフィールドを新しいオブジェクトとして扱うため、条件付き書式ルールを再作成する必要があります。
• 新しい階層レベルの追加: 追加の行フィールドまたは列フィールドを挿入すると、既存の条件付き書式設定が変更またはリセットされる可能性があるため、ルールの再適用または再ターゲットが必要になる場合があります。
• マルチレベル階層の動作: 親レベルと子レベルは別々に処理されるため、一方のレベルに適用された条件付き書式設定は、もう一方のレベルに自動的に引き継がれません。

[新しいルール]ダイアログを使用したピボットテーブルの書式設定はさらに直接的です

すべては 1 か所で起こります

Excel の[新しい書式ルール]ダイアログを使用して条件付き書式を適用する場合は、ピボットテーブルのコンテキストでワークフローが若干変更されます。 「書式設定オプション」アクションタグをクリックするのではなく 後 書式設定を適用すると、フィールドレベルのターゲティングが確立されます。 初めに:

1. を選択してください 単一値セル ピボットテーブル内で視覚的なキューを配置したい場所に配置します。

2. クリック 家 > 条件付き書式設定 > 新しいルール。

3. ウィンドウの上部には、同じ 2 つのピボットテーブル ターゲティング オプションがあります。 (フィールド名) の値を示すすべてのセル そして (行/列フィールド名) の (フィールド名) 値を表示するすべてのセル。最初のオプションには合計行が含まれますが、2 番目のオプションには含まれていないため、データに最も適したものを選択してください。

たとえ ルールの適用先 ボックスに絶対セル参照が表示されている場合、選択したピボットテーブル ターゲット オプションが優先され、ルールは特定のワークシート座標ではなく、選択したピボットテーブル フィールドに従います。

ここで、通常どおり書式スタイルを設定し、 わかりました 動的ルールを適用します。

[新しい書式設定ルール]ダイアログの最後のオプションは次のとおりです。 数式を使用して書式設定するセルを決定する。これは、Excel のパワー ユーザーが通常、組み込みルールの種類では十分な柔軟性がない場合、特にセルの値または条件に基づくカスタム ロジックが必要な場合に使用する選択肢です。

同じフィールド レベルのターゲティング オプションは数式ベースのルールでも機能しますが、数式では追加の考慮事項がいくつかあります。組み込みルールの種類とは異なり、数式ルールはセル参照に依存するため、数式の作成方法は、Excel がピボットテーブル全体にその数式を適用する方法に直接影響します。

最も重要な要件は、次のような混合参照を使用することです。

=$B4>=LARGE($B$5:$B$100,4)

次のような絶対参照ではなく、

=$B$4>=LARGE($B$5:$B$100,4)

したがって、ルールはピボットテーブル内の行の位置を基準にして各セルを評価します。列と行の両方をロックすると、Excel は単一の固定比較値を使用します。つまり、行ごとに調整するのではなく、範囲内のすべてのセルに同じ条件が適用されます。これにより、設定したフィールドレベルの動作が事実上無効になります。

また、ピボットテーブルでは、標準範囲と同様に行全体の条件付き書式設定がサポートされていないことにも注意してください。この制約を回避するには:

1. 数式ルールを 最初の値フィールド 上記の手順を使用します。

2. 作成したら、クリックします ホーム > 条件付き書式設定 > ルールの管理。

3. で ルールマネージャーをクリックし、作成したばかりのルールを選択して、 重複ルール。

4. ダブルクリック 複製されたルールを編集します。

5. で ルールの適用先 ボックスで既存の参照をクリアし、 最初のセル で 2 番目の値フィールド クリックする前に わかりました。

これで、両方の値フィールドが同じ数式を独立して評価するようになり、条件付き書式設定を両方の列に表示できるようになります。

この回避策は、行レベルではなく値フィールド レベルで機能します。後で追加された新しい値フィールドはルールを自動的に継承しないため、追加フィールドごとに書式設定を複製して再ターゲットする必要があります。また、Excel では、ピボットテーブル対応の条件付き書式設定の範囲を行ラベル列に設定することはできません。つまり、行見出しを同じ方法で書式設定することはできません。

---

小さな設定により、Excel の機能の動作が完全に変わる可能性があります

Excel の最も便利な機能の多くは目に見えないところに隠されており、ピボットテーブル対応の条件付き書式設定は、見落とされている小さな設定によって信頼性が大幅に向上することを示す好例です。これらの機能がどこに存在するかを理解すると、毎日の Excel ワークフローがはるかに一貫したものになります。