【Googleスプレッドシート】条件付き書式とフィルタの併用!見えるデータだけ条件評価

フィルタ機能で必要な行だけを表示しながら、条件付き書式でデータを視覚化したい場面は多いです。しかし、通常の条件付き書式は非表示の行にも適用されてしまい、見えているデータだけを評価できません。この記事では、SUBTOTAL関数を使ったカスタム数式で、表示されている行だけに条件付き書式を適用する方法を解説します。フィルタと条件付き書式を共存させる実践的なテクニックです。

フィルタと条件付き書式の併用で起きる問題

条件付き書式は、シート上のすべてのセルに対してルールを評価します。フィルタで行を非表示にしても、その行のセルは書式ルールの対象から外れません。その結果、非表示の行にも色が付いたままになり、見た目と実際のデータが一致しないという混乱が生じます。この問題は、フィルタでデータを絞り込むたびに条件付き書式が正しく動作しない原因となります。

解決策として、SUBTOTAL関数を使って「その行がフィルタで表示されているかどうか」を判定する方法があります。SUBTOTAL関数は、可視セルだけを対象に計算を行えるため、条件付き書式のカスタム数式と組み合わせることで、表示行のみに書式を適用できます。SUBTOTAL関数の第1引数には、集計方法を指定するコードを入力します。103は「非表示行を無視したCOUNTA」を意味します。この関数を各行の識別列に適用し、結果が1(可視)かどうかを条件にすることで、フィルタで表示されている行だけを特定できます。

表示行だけに条件付き書式を適用する手順

手順1: 適用範囲を選択する

1. 範囲を選択します
   条件付き書式を適用したいセル範囲(例:A2:C100)をドラッグして選択します。見出し行を含めるかどうかは状況に応じて決めてください。通常はデータが始まる行から選択します。
2. 条件付き書式ルールを開きます
   メニューから「書式」→「条件付き書式」をクリックします。右側に条件付き書式ルールのパネルが表示されます。このパネルで新しいルールを作成します。

手順2: カスタム数式を設定する

1. ルールの種類を「カスタム数式」に変更します
   条件付き書式ルールパネルで、「セルの書式設定条件」のドロップダウンを「カスタム数式」に切り替えます。これにより、任意の数式で条件を指定できるようになります。
2. 数式を入力します
   数式欄に「=SUBTOTAL(103, $A2)=1」と入力します。ここで$A2は各行の識別列(通常は先頭列)のセルを指定します。範囲がA2:C100の場合、A列を基準にします。列の絶対参照($A)と行の相対参照(2)を組み合わせることで、各行に対して正しく評価されます。例えば、売上データであればA列に商品名が入っていると想定します。
3. 書式スタイルを設定します
   「書式スタイル」で、表示行に適用したい塗りつぶし色や文字色を選択します。例えば、背景色を薄い黄色に設定します。フォントの色や太字なども必要に応じて指定してください。
4. ルールを保存します
   「完了」ボタンをクリックしてルールを保存します。これで、フィルタで表示されている行だけに書式が適用されます。もし複数の条件を組み合わせたい場合は、さらにルールを追加することもできます。

手順3: フィルタを有効にして動作を確認する

1. フィルタを追加します
   データ範囲にフィルタを設定します。メニューから「データ」→「フィルタを作成」をクリックします。または、ツールバーのフィルタアイコンをクリックしても同じです。
2. フィルタ条件を変更して確認します
   任意の列でフィルタ条件を変更すると、非表示行の色が消え、表示行だけに色が付くことを確認してください。フィルタを解除するとすべての行に色が戻ります。これが正常な動作です。

応用: 複数の条件を組み合わせる

1. 数式にAND条件を追加する
   例えば、表示行かつ特定の値を持つ行だけに書式を適用したい場合、数式を「=AND(SUBTOTAL(103, $A2)=1, $B2=”完了”)」のようにします。これにより、フィルタで表示されている行の中で、B列が「完了」の行だけが色付けされます。
2. OR条件で複数の値を評価する
   「=AND(SUBTOTAL(103, $A2)=1, OR($B2=”高”, $B2=”中”))」のようにORを組み合わせれば、表示行の中でB列が「高」または「中」の行をハイライトできます。
3. 数値条件との組み合わせ
   売上データで、表示行かつ売上が100以上の場合に色を付けるには、「=AND(SUBTOTAL(103, $A2)=1, $B2>=100)」とします。これでフィルタで絞り込んだ高売上商品だけを視覚化できます。

条件付き書式とフィルタ併用時の注意点

SUBTOTALの第1引数103の意味を理解する

SUBTOTAL関数の第1引数には、集計方法を示すコードを指定します。103は「非表示行を無視したCOUNTA」です。これにより、可視セルだけがカウントされます。条件付き書式で「=SUBTOTAL(103, $A2)=1」とすると、各行のA列セルが可視であれば1(可視行)、非表示なら0(またはエラー)となり、可視行だけに書式が適用されます。他のコード(101: AVERAGE, 102: COUNT, 104: MAXなど)も使えますが、表示行判定には103が最も適しています。

適用範囲と基準列の設定ミスに注意

カスタム数式の基準となるセル(例:$A2)は、条件付き書式の適用範囲の先頭行に対応する必要があります。範囲がA2:C100の場合、数式は$A2を基準にします。$A2のように列を絶対参照、行を相対参照にすることで、各行に対して正しく評価されます。範囲の先頭行が2行目でない場合は、それに合わせて調整してください。例えば、範囲がB5:D50なら、数式は「=SUBTOTAL(103, $B5)=1」とします。

フィルタ解除後に書式が残る問題

SUBTOTALを使った条件付き書式は、フィルタが有効でないときはすべての行が可視行と判定されるため、全行に書式が適用されます。これは正常な動作ですが、フィルタ解除時に書式を消したい場合は、別の条件(例:フィルタが有効かどうか)を追加する必要があります。ただし、実用上は問題になることは少ないです。フィルタを解除した後も色が付いていて気になる場合は、手動で書式をクリアするか、別のルールで上書きしてください。

パフォーマンスへの影響

SUBTOTAL関数は大量のデータに対して使用すると計算が重くなる可能性があります。数千行以上のデータでは、条件付き書式の再計算に時間がかかる場合があります。その場合は、データ範囲を必要最小限に絞るか、スクリプトによる代替を検討してください。また、フィルタを頻繁に変更する場合は、計算が遅くならないよう注意が必要です。

空白セルがある場合の挙動

基準列(例:A列)に空白セルがある場合、SUBTOTAL(103, 空白)は0を返します。そのため、空白行は常に非表示行と判定され、条件付き書式が適用されません。これにより、データのない行に誤って色が付くのを防げます。ただし、意図的に空白セルをデータとして扱いたい場合は、他の列を基準にするか、IF関数で空白を処理する必要があります。

表示行のみに書式を適用する方法の比較

上記の比較から、手軽さと動的更新のバランスに優れたSUBTOTAL関数を使う方法が最も実用的です。フィルタビューを使う方法は、ビューごとに異なる書式を設定したい場合に有効です。スクリプトは高度なカスタマイズが必要な場合に検討してください。

この記事では、条件付き書式とフィルタを併用する際に、表示されている行だけに書式を適用する方法を解説しました。SUBTOTAL関数を使ったカスタム数式「=SUBTOTAL(103, $A2)=1」を設定することで、フィルタ条件の変更に応じて動的に書式が切り替わります。このテクニックを応用すれば、AND条件で複数の評価を組み合わせたり、他の列の値と連動させることも可能です。ぜひ実際のデータ分析で活用し、スプレッドシートの操作効率を高めてください。