Excel。オートフィルターで抽出しても条件付き書式が有効になるにはどうしたらいい？
＜条件付き書式+AGGREGATE関数＞
Excelには、便利な機能がたくさんあります。
例えば、条件付き書式をつかえば、視覚的にデータの特徴を知ることも出来ます。
また、オートフィルターを使えば、簡単に希望する条件でデータを抽出することも出来ます。
ただ、この2つを組み合わせて使うと、思ったように機能してくれません。
次の表を使って説明します。
F列の合計で一番数値がいいデータにセルの塗りつぶしをする条件付き書式を設定してみます。
F2:F6を範囲選択して、ホームタブの条件付き書式にある「上位/下位ルール」の「上位10%項目」をクリックします。
上位10項目ダイアログボックスが表示されます。
数値を「1」と設定すれば、最高値のデータのセルに塗りつぶしをする条件付き書式を設定することができます。
書式を設定したらOKボタンをクリックします。
設定後に、英語の点数が70点以上のデータをオートフィルターで抽出したら、どうなるか、確認してみましょう。
英語の見出しにある、オートフィルターの数値フィルターにある「指定の値以上」をクリックします。
カスタムオートフィルターダイアログボックスが表示されますので、「70」と入力します。
設定したら、OKボタンをクリックします。
英語が70以上のデータのみが抽出されましたが、条件付き書式は、連動してません。
要するに、表示されているデータに対して、条件付き書式が対応しているわけではないのです。
このように、データが非表示になっても対応させるには、設定されている条件付き書式の機能では対応できないので、新しいルールを作る必要があります。
つまり、数式をつかった条件付き書式をつくるわけです。
非表示のデータとなれば、SUBTOTAL関数やAGGREGATE関数の登場ですね。
オートフィルターをクリアして、F列の条件付き書式も削除したら、F2:F6を範囲選択します。
新しい書式ルールダイアログボックスが表示されます。
「数式を使用して、書式設定するセルを決定」を選択して、ルールを設定してきます。
=F2=AGGREGATE(4,5,F2:F6)
書式を設定したら、OKボタンをクリックします。
先程と同じようにセルが塗りつぶしされています。
では、オートフィルターをつかって、同じ条件で抽出してみます。
このように、表示されているデータを対象にして、条件付き書式が反映されていることが確認できました。
数式で設定した、
=F2=AGGREGATE(4,5,F2:F6) のAGGREGATE関数も確認しておきましょう。
最初の引数は、集計方法です。
4番は、MAX。最大値を算出することができます。
次の引数は、オプション。
5番の「非表示の行を無視します」を選ぶことで、オートフィルターにも対応してくれるようになります。
