Excel。オートフィルターで抽出した最大値の行を塗りつぶししたい
＜条件付き書式＋AGGREGATE関数＞
Excelの機能を組み合わせることで、思ったような結果にならないことが、見受けられます。
例えば、オートフィルターと条件付き書式の組み合わせです。
条件付き書式をつかうことで、データの状況をわかりやすくできます。
ただ、オートフィルターで抽出した結果に連動して、条件付き書式が機能してくれないことがあります。
次のような表を用意しました。
D列の合計フィールド内で、最大値のデータがある行全体を塗りつぶす、条件付き書式を設定しています。
行全体で塗りつぶしたいので、数式を使用して、条件付き書式を設定する必要があります。
今、設定している数式は、
=MAX($D$2:$D$11)=$D2
としてあります。
MAX関数をつかっていますが、MAX関数を使うことで最大値を算出することができます。
その結果と同じならば、最大値のデータが含まれている行ということがわかります。
さて、問題はここからで、B列の「Leading」が70点以上という条件で、オートフィルターをつかって抽出してみます。
すると、このような結果になりました。
残念ながら、抽出結果に連動して、条件付き書式が動いていません。
なぜ、このような結果になってしまったのかというと、MAX関数が、引数の範囲内が非表示になった時に対応していないのが原因です。
つまり、MAX関数は、可視データのみというわけにはいかないというわけです。
そのため、最大値＝MAX関数というよりも、行の非表示に対応にしたAGGREGATE関数を使う必要があるわけです。
今回のような最大値の場合は、SUBTOTAL関数でも対応できますが、上位の順位を算出できるLARGEという条件が含まれていませんので、AGGREGATE関数のほうが、様々なケースに対応しやすいかと思います。
では、条件付き書式の数式を変更していきます。
次の数式を満たす場合に値を書式設定の数式を
=AGGREGATE(4,5,$D$2:$D$11)=$D2
と変更しました。
先程と同じ条件で抽出してみます。
最大値が167に変わったことに連動して、条件付き書式が機能していることがわかります。
フィルター機能と条件付き書式を組み合わせて使うときには、非表示に対応するということを念頭に置いて対応することが大切のようですね。
