Excel。条件付き書式を設定がオートフィルターで抽出しても連動させたい
＜条件付き書式＋SUBTOTAL関数＞
条件に合致した場合、視覚的にわかりやすくすることができる「条件付き書式」ですが、ちょっとプラスアルファすると、連動してくれないことが多々あります。例えば、最高値がわかるようにした場合でも、オートフィルターをつかって、抽出すると最高値のデータが抽出条件外だと、条件付き書式が反映されていないように見えてしまいます。
そのような場合、どう対応したらいいのでしょうか？
次のデータがあります。
最大値がわかるように、条件付き書式を設定してみます。
B2:B15を範囲選択します。
ホームタブの条件付き書式にある、上位/下位ルールにある、「上位10項目」をクリックします。
上位10項目ダイアログボックスが表示されます。
項目数を「1」に設定して、書式を設定したら、OKボタンをクリックします。
これで、条件付き書式をつかって、上位1位のデータ。
つまり最高値のデータを視覚的にわかるようにできました。
男性・女性それぞれのデータを見たいと思い、オートフィルターを使ってみることにしました。
データタブのフィルターをクリックして、オートフィルターを表示させます。
性別を「男」で抽出してみます。
男性だけが表示されましたが、条件付き書式は、きちんと反映されていることが確認できます。
今度は、性別が「女」のデータで抽出してみます。
抽出は、女性のみを抽出していますので、問題はないのですが、条件付き書式は連動することなくそのままなので、条件付き書式が設定されていないように見えてしまいます。
このように抽出条件がかわっても、常に上位1位のデータを条件付き書式でわかるようにしたい場合、どのようにしたらいいのでしょうか？
そこで、SUBTOTAL関数を使うことで、対応することができます。
一度設定した条件付き書式をクリアしておきます。
改めて、B2:B15を範囲選択します。
ホームタブの条件付き書式にある、「新しいルール」をクリックします。
新しい書式ルールダイアログボックスが表示されます。
ルールの種類は、「数式を使用して、書式設定するセルを決定」にして、次の数機を満たす場合に値を書式設定のボックスには、
=b2=SUBTOTAL(104,$B$2:$B$15)
と設定します。
書式ボタンをクリックして、セルの書式設定ダイアログボックスが表示されますので、書式を設定します。
あとは、OKボタンをクリックします。
先程の条件付き書式と同様に、最高値に塗りつぶしが反映されました。
改めて、性別を「女」で抽出してみます。
このように、抽出条件がわかっても、常に最高値が塗りつぶしされるようになったことが確認できます。
=SUBTOTAL(104,$B$2:$B$15)のSUBTOTAL関数の100番台は、非表示になったデータを除外することができます。
今回使った、104番は、最高値を見つけるのだけど、非表示なったものは除外するとことができるので、抽出など非表示になっても、連動して条件付き書式を反映させてくれます。
なお、
=AGGREGATE(4,5,$B$2:$B$15)
というように、AGGREGATE関数でも問題はありません。
