Excel。オートフィルターの抽出に対応したデータが最大値のとき行全体を塗りつぶすには【Fill】

Excel。オートフィルターの抽出に対応したデータが最大値のとき行全体を塗りつぶすには

＜条件付き書式＋AGGREGATE関数＞

条件付き書式をつかって、データが最大値のとき、データ全体（行全体）を塗りつぶしたい場合には、MAX関数をつかうことで、対応することができます。

条件付き書式の条件を確認してみましょう。

書式ルールのダイアログボックスには、MAX関数をつかった条件が設定されています。

=MAX($F$2:$F$10)=$F2

これで、F列の合計が最大の場合、その行全体を塗りつぶすことができるわけです。

ところが、オートフィルターをつかって、抽出をすると対応してくれないことがわかります。

Readingを80以上の条件で抽出してみましょう。

最大値は259なので、その行が塗りつぶし対象になっているはずですが、対応していません。

原因は、先程の条件でMAX関数をつかったからです。

MAX関数は、データ抽出に伴う、行の非表示に対応しておりません。

要するに、可視データのみが対象になっているわけではありません。

そのため、MAX関数では対応することができないというわけです。

では、どのようにしたらいいのでしょうか。

非表示に対応することができるAGGREGATE関数をつかって条件を設定します。

オートフィルターの抽出条件をクリアして、条件式を修正します。

=AGGREGATE(4,5,$F$2:$F$6)=$F2

これで、オートフィルターの抽出にも対応することができます。

それでは、改めて、Readingが80以上の条件で抽出します。

このように、可視データのみが対象となって、合計の最大値259のデータが塗りつぶされていることがわかります。

オートフィルターと条件付き書式を組み合わせるならば、AGGREGATE関数をお勧めします。

条件式の説明をします。

=AGGREGATE(4,5,$F$2:$F$6)=$F2

AGGREGATE関数の最初の引数は、「集計方法」です。

集計方法の4番は、最大値です。

2つめの引数は、「オプション」。

5を採用したのは、「非表示の行を無視します」をつかうためです。

別に非表示を無視してくれる条件ならば、なんでもOKです。

3つ目の引数は、「配列」。範囲なので、F2:F6を絶対参照で設定します。

これで、非表示に対応した最大値を算出することができます。

この値と、F列のデータが合致しているならば、塗りつぶすというわけです。

なお、「=$F2」と列固定の複合参照にすることで、行全体を対象にして塗りつぶすことができます。

Excel。オートフィルターで抽出した最大値の行を塗りつぶししたい【MAX-DATA】

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に変わったことに連動して、条件付き書式が機能していることがわかります。

フィルター機能と条件付き書式を組み合わせて使うときには、非表示に対応するということを念頭に置いて対応することが大切のようですね。

Excel。AGGREGATE関数は、19種類の集計方法で小計を算出します 配列形式【AGGREGATE】

Excel。AGGREGATE関数は、19種類の集計方法で小計を算出します　配列形式

＜関数辞典:AGGREGATE関数＞

AGGREGATE関数

読み方： アグリゲイト　　

分類： 数学/三角 

AGGREGATE(集計方法,オプション,配列,[順位])

19種類の集計方法で小計を算出します　配列形式

Excel。AGGREGATE関数は、19種類の集計方法で小計を算出します セル範囲形式【AGGREGATE】

Excel。AGGREGATE関数は、19種類の集計方法で小計を算出します　セル範囲形式

＜関数辞典:AGGREGATE関数＞

AGGREGATE関数

読み方： アグリゲイト　　

分類： 数学/三角 

AGGREGATE(集計方法,オプション,範囲1,[範囲2],…)

19種類の集計方法で小計を算出します　セル範囲形式

Excel。オートフィルターで抽出しても条件付き書式が有効になるにはどうしたらいい？【filter】

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番の「非表示の行を無視します」を選ぶことで、オートフィルターにも対応してくれるようになります。

Excel。非表示対応の連番は、SUBTOTAL関数だと注意が必要。AGGREGATE関数を使います。【Sequential number】

Excel。非表示対応の連番は、SUBTOTAL関数だと注意が必要。AGGREGATE関数を使います。

＜オートフィルター+SUBTOTAL関数とAGGREGATE関数＞

オートフィルターをつかって、データを抽出しても、連番にしたいことがあります。

その場合、SUBTOTAL関数をつかうことで、対応することができるのですが、ある欠陥がありますので、注意が必要になります。

次のようなデータがあります。

 

今は、A列のNOは単なる数値の連番になっています。

オートフィルターをつかって、C列の売上高が2000以上のデータを抽出してみます。

売上高のオートフィルターにある「数値フィルター」の「指定の値以上」を使います。

なお、（すべて選択）の下に、四谷の売上高である。1227が表示されているのを覚えておくといいです。

オートフィルターオプションダイアログボックスが表示されます。

2000以上と設定して、OKボタンをクリックします。

2000以上のデータが抽出されました。

NOフィールドを確認すると、当然のことながら、「連番」ではありません。

これを連番で表示したいというわけです。

非表示に対応するには、SUBTOTAL+COUNTA関数というのがお馴染みなのですが、欠陥があるのです。

A2に、次の数式を設定して、オートフィルター機能でA11まで数式をコピーしています。

=SUBTOTAL(103,$B$2:B2)

この数式を説明すると、SUBTOTAL関数の集計方法「103」というのは、COUNTA関数と同じことをするのですが、行が非表示になるとそれを除いて、数えてくれるわけです。

そして、範囲を、B2を起点とするために、開始のほうだけ、絶対参照を設定します。

こうすることで、データの範囲の拡張するにあわせて、参照する範囲を延ばすことができます。

では、通常の行の非表示を行って、動きを確認してみましょう。

非表示が対象外になるので、繰り上がる形でNOフィールドは「連番」になっていることが確認できました。

別に問題はないように思えますが、先程のオートフィルターをつかって、売上高2000円以上のデータを抽出してみましょう。

非表示にした行は戻しておきます。

NOフィールドは連番にはなっていますが、何か変ですよね。

最終データの四谷。

2000円以上でないのに表示されています？

おかしな現象は、オートフィルターオプションを表示させる前に見えていました。

四谷の売上高は1227と最小なので、本来ならば、（すべて選択）の下に表示されていなければなりません。

ところが表示されていない。

SUBTOTAL関数を使う前は、表示されていました。

どうやら、SUBTOTAL関数は、「小計」を算出する関数なので、最終行を「合計行」という認識になっているようです。

つまりデータ行ではないので、含めないで処理をしてしまうようです。

四谷の下に、合計を算出した行を追加してみると、理解できます。

C12には、SUM関数を設定しております。

これで、先程と同じようにオートフィルターでデータを抽出してみましょう。

今回は、四谷のデータが表示されていないことが確認できました。

このように、SUBTOTAL関数をつかった表でオートフィルターを使うときには注意が必要です。

では、非表示に対応した連番はつくることができないのでしょうか？

実は、SUBTOTAL関数の進化版というべきAGGREGATE関数を使うことで対応することができます。

A2には、

=AGGREGATE(3,5,$B$2:B2)

という数式を設定しています。

引数の3は、集計方法で、COUNTA関数と同じ動きをします。

引数の5は、オプション設定で、非表示に対応することができるようになります。

改めて、売上高2000円以上で抽出してみましょう。

AGGREGATE関数だと、問題はありません。

この差はなんなのかというと、

SUBTOTAL関数は、「小計」でAGGREGATE関数は、「集計」をします。

微妙ですが、全く異なっていますので、SUBTOTAL関数で希望通りにならない時には、AGGREGATE関数をつかって確認してみるというのもいいかもしれませんね。

Excel。19種類の集計ができる。AGGREGATE関数をもっと使ってみよう！【function:AGGREGATE】

Excel。19種類の集計ができる。AGGREGATE関数をもっと使ってみよう！

＜AGGREGATE関数＞

SUBTOTAL関数をグレードアップというか、機能拡張した関数があります。

それが、AGGREGATE関数。

SUBTOTALは「小計」という意味ですね。

それに対して、AGGREGATEは「集計」という意味ですね。

なんで、SUBTOTAL関数があるのに、AGGREGATE関数を使うのと思うかもしれませんが、「小計」と「集計」ということで、似て非なる関数なわけです。

結果的に、19種類の集計を行うことができるというのが、AGGREGATE関数の強みとなったわけです。

AGGREGATE関数の読み方は「アグリゲイト」です。

AGGREGATE関数の引数も確認しておきましょう。

AGGREGATE（集計方法,オプション,参照1,…）

AGGREGATE（集計方法,オプション,配列,[順位])

19種類の集計を行えるということで、2種類の引数を持っているのが特徴です。

集計の種類で使い分けます。

そして、このAGGREGATE関数は、『手入力』で数式を作ることをおススメします。

その理由は、集計方法やオプションに集計の種類ごとなどに番号が割り振られています。

関数挿入ダイアログボックスだと、その番号がわかりません。

また、関数の分類は、「数学・三角」に所属しています。

次の表を使って確認してみましょう。

今回は、H1に、データが非表示になっても連動して、売上高の第3位を算出してみます。

関数を作る前に、G1の「3位」は、ユーザー定義をつかって、表示形式で「位」を表示させています。G1の内容は、3です。

ポイントなのは、上位何番目のデータを算出できる。

LARGE関数は行の非表示には対応していませんし、SUBTOTAL関数では、LARGE関数のような「上位何番目」を算出する方法はもっていません。

なぜならば、SUBTOTAL関数は「小計」を算出する関数ですから。

それでは、H1に入力していきましょう。

最初に設定する引数が、「集計方法」。今回は、LARGEの「14」をつかいます。

次が、「オプション」。

「非表示の行を無視します」をクリックすることで、行が非表示になった時でもリアクションしてくれるようになります。

配列には、売上高のD2:D11を設定し、順位はG1を設定します。

完成した数式は、

=AGGREGATE(14,5,D2:D11,G1)

結果を確認してみましょう。

上位3番目の数値が算出することができました。

では、データを非表示にしてみましょう。

このように、数値が連動して変わったことが確認できました。

用途によって、SUBTOTAL関数とAGGREGATE関数が使い分けられるようになるといいのかもしれませんが、算出結果があっていれば、どっちでもいいというのが、現場レベルというところなんでしょうね。

最後に、集計方法とオプションを紹介しておきます。

『集計方法』

1　AVERAGE　平均

2　COUNT　数値の件数

3　COUNTA　データ件数

4　MAX　最大値

5　MIN　最小値

6　PRODUCT　積

7　STDEV.S　標本標準偏差

8　STDEV.P　母集団標準偏差

9　SUM　合計

10　VAR.S　不偏分散

11　VAR.P　母集団の分散

12　MEDIAN　中央値

13　MODE.SNGL　最頻値

14　LARGE　何番目に大きな値

15　SMALL　何番目の小さな値

16　PERCENTILE.INC　百分位数 0%以上～100%以下

17　QUARTILE.INC　四分位数 0%以上～100%以下

18　PERCENTILE.EXC　百分位数 0%超～100%未満

19　QUARTILE.EXC　四分位数 0%超～100%未満

こんな番号。覚えられないよね。

『オプション』

0　集計値の無視

1　集計値の無視と非表示行の無視

2　集計値の無視とエラーの無視

3　集計値の無視と非表示の無視とエラーの無視

4　何も対象外にしない

5　非表示行の無視

6　エラーの無視

7　非表示行の無視とエラーの無視

Excel。範囲内に小計があると最大値を算出するのが面倒なので、どうにかしたい【MAX】

Excel。範囲内に小計があると最大値を算出するのが面倒なので、どうにかしたい

＜SUBTOTAL関数・AGGREGATE関数＞

簡単そうな処理でも、ちょっと表の条件が変わると面倒になることが多いのもExcelの特徴といえば特徴ですが、次の表もそのパターン。

四半期ごとの集計が途中に表示されている表なのですが、半期の売上高の最高金額を算出したい場合、意外と面倒なことが発生します。

何が面倒なのかというと、B10の数式は、
=MAX(B2:B4,B6:B8)
というように、小計値を含めてしまうと、合算値なので、一番大きな数値なのは決まっていますから、いちいち範囲選択をわけて設定しないといけないわけです。

当然、第2四半期までですが、これが、第3・第4というように増えるとさらに範囲選択を繰り返す必要が発生します。

まして、四半期ではなく、さらに大きなデータの場合は、面倒です。

できれば、範囲選択を一度で済ませたい。

なんで、そうなってしまうのかというと、小計の数式に問題があるのです。

B5の数式を確認してみると、
=SUM(B2:B4)
別に何の問題もない、SUM関数で合計値を算出しています。

B9も同じようにSUM関数を使っています。

このSUM関数をある関数に変更するだけで、そして、最大値を算出するのもMAX関数ではなくて、ある関数に変更するだけで、範囲選択に小計を含めても最大値を算出することができます。

【SUBTOTAL関数かAGGREGATE関数で小計を算出】

Excelには、小計を算出するための『SUBTOTAL関数』というのが用意されています。

B5にSUBTOTAL関数をつかって小計を算出していきます。

SUBTOTAL関数は手入力で設定するほうが引数の設定が楽です。

最初の引数、集計方法は、109のSUMを選択します。先頭に”1”が付いている集計方法は、行の非表示にも対応しています。

今回非表示にすることはありませんが、109のSUMで設定してきます。

次の参照1には、B2:B4を範囲選択します。

B5の数式は、
=SUBTOTAL(109,B2:B4)
と設定しております。

同じように、第2四半期のB9も設定します。

算出結果を確認すると、先程のSUM関数と同じ結果になっていますね。

そして、ここからが本題。

最大値をMAX関数ではなくて、SUBTOTAL関数をつかって算出していきます。

B10にSUBTOTAL関数で最大値を算出します。
今回は、104の最大値を使用します。

そして、参照の範囲は、B2:B9と小計を含めて範囲選択を設定します。
B10の数式は、
=SUBTOTAL(104,B2:B9)

では、確認してみましょう。

MAX関数の時のように、わざわざ範囲選択を分けて設定する必要はなく、小計を含めて範囲選択して算出することができました。

このように、SUM関数だけでなく、SUBTOTAL関数を使うことで、他の作業を追加していくときに利便性が向上します。

また、SUBTOTAL関数ではなく「AGGREGATE関数」を使っても同じように最大値を求めることができます。