Excel。11種類の集計方法で小計を算出するのがSUBTOTAL関数
＜関数辞典：SUBTOTAL関数＞
SUBTOTAL関数
読み方： サブトータル
分類： 数学/三角
SUBTOTAL(集計方法,参照1,…)
11種類の集計方法で小計を算出します
2/14/2024
Excel。途中に集計行がある表。集計を除いた最大値を算出したい。【subtotal】
Excel。途中に集計行がある表。集計を除いた最大値を算出したい。
＜SUBTOTAL関数＞
四半期集計などが表の途中にある表で、最大値を算出したい場合、SUM関数とMAX関数だと上手く算出することができません。
表を用意しましたので、確認していきます。
日々の集計である小計が算出されている表です。
C列の場合からみていきましょう。
小計は、SUM関数とつかって、算出しています。
そして、最大値を算出したいわけですから、MAX関数と考えますが、範囲選択が面倒です。
どうしてかというと、小計を含めて範囲選択すれば、当然、小計の値の方が日々のデータよりも大きくなるからです。
そのため、小計を含めて最大値を算出すると、最大値が算出できないので、小計を除いて範囲選択する必要があります。
ただ、イチイチ、小計を除いて範囲選択するのは、面倒です。
実は、SUM関数やMAX関数とつかって、小計を除いた最大値を算出するよりも、別の関数をつかうと、手早く算出することができます。
先程の表を数式で表示してみます。
C列の小計はSUM関数。そして、最大値はMAX関数をつかっています。
D列は、小計も最大値もSUBTOTAL関数をつかって算出しています。
なお、SUBTOTAL関数ではなくて、AGGREGATE関数をつかってもOKです。
ただ、AGGREGATE関数は引数が、SUBTOTAL関数よりも、多いので、今回は、SUBTOTAL関数をつかって説明しております。
D10のSUBTOTAL関数をつかった最大値の数式ですが、範囲選択は、D2:D9で設定しています。
SUBTOTAL関数は、範囲選択にあるSUBTOTAL関数の結果は除外するようになっています。
そのため、範囲選択を必要なデータのみにする必要はないというわけです。
それでは、小計のD5に設定したSUBTOTAL関数を確認します。
最初の引数は、集計方法です。109は非表示にも対応した合計値を求めることができる番号です。
最大値は、104と割り振られています。
2つ目の引数は、参照なので、範囲選択ということですから、それぞれのデータを設定します。
今回のように、途中に小計とかの集計行が含まれている場合、SUM関数とMAX関数で算出よりも、SUBTOTAL関数をつかうことで、手早く最大値を算出することができます。
2/25/2023
Excelの様々な関数の読み方や引数などを紹介。今回は、STEYX関数～SUBTOTAL関数です。【dictionary】
Excelの様々な関数の読み方や引数などを紹介。今回は、STEYX関数～SUBTOTAL関数です。
＜Excel関数辞典：VOL.76＞
今回は、STEYX関数～SUBTOTAL関数までをご紹介しております。
STEYX関数
読み方： スタンダードエラーワイエックス
STEYX(既知のy,既知のx)
回帰直線の標準誤差を算出する
STOCKHISTORY関数
読み方： ストックヒストリー
STOCKHISTORY(stock,start_date,[end_date],[interval],[headers],[properties1],…)
指定された銘柄と日付範囲の過去の相場データの配列を返す
SUBSTITUTE関数
読み方： サブスティチュート
SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])
文字列中の特定の文字を別の文字に置換する
SUBTOTAL関数
読み方： サブトータル
SUBTOTAL(集計方法,参照1,…)
11種類の集計方法で小計を算出します
11/24/2022
Excel。範囲内に集計値がある場合、楽に最大値を算出するにはどうしたらいいの【MAX】
Excel。範囲内に集計値がある場合、楽に最大値を算出するにはどうしたらいいの
＜SUBTOTAL関数＞
四半期集計がある表の場合、最大値を算出するとなると、四半期集計を除いて範囲選択しないと算出することができません。
B11の数式は、最大値を算出したいので、MAX関数をつかっています。
=MAX(B2:B9)
ただ、B5やB9に四半期集計があるので、これら集計行を除かないと、当然集計値の方が大きいため、きちんとした最大値を算出することができません。
当然、範囲選択で集計行を除けばいいわけですが、集計行が増えれば増えるほど、面倒になります。
このような集計表を作る場合、実は、四半期集計などの途中集計や、最大値を算出するにあたり、「SUBTOTAL関数かAGGREGATE関数」をつかうことで、範囲選択が面倒にならず、手早く算出することができます。
まず、B5とB9の四半期集計をSUM関数からSUBTOTAL関数に変更します。
B5の数式は、
=SUBTOTAL(9,B2:B4)
B9の数式は、
=SUBTOTAL(9,B6:B8)
このように、数式を設定します。
なお、SUBTOTAL関数は集計をする関数です。
最初の引数は「集計方法」です。
「9」は合計を算出する番号です。
今回は、行の非表示がないので、「109」の100番台はつかわなくて大丈夫です。
B11の最大値もMAX関数ではなくて、SUBTOTAL関数で数式をつくります。
=SUBTOTAL(4,B2:B9)
計算方法「4」番は、最大値を設定する番号です。
そして、2つ目の引数は、範囲ですが、集計行を除く必要はありません。
すると、集計行を除いて、最大値を算出することができます。
SUBTOTAL関数は、範囲選択に、SUBTOTAL関数をつかった算出結果がある場合には、それを除外して、算出してくれるという、便利な機能があります。
それにより四半期集計を除いた最大値を算出することができたというわけです。
ちょっとした関数の違いですが、手早く算出できるかもしれませんので、調べてみるといいかもしれませんね。
10/08/2021
Excel。小計を除いて最大値を求めたいのに範囲選択が面倒なのでどうにかしたい【Exclude subtotals】
Excel。小計を除いて最大値を求めたいのに範囲選択が面倒なのでどうにかしたい
＜SUBTOTAL関数＞
帳票と同じようにExcelで表を作ってしまうと、意外と面倒な処理が発生することがあります。
例えば次のような表。
小計が含まれている帳票ですね。
最大値を算出しているB14。
おかしいですよね。
合計値の値を算出しています。
範囲選択をB3:B11までにすると、今度は、1782という小計値を算出してしまいます。
要するに、範囲選択内の最大値を算出してしまうわけです。
となると、範囲選択は、「B3:B5,B7:B8,B10:B11」というように、何度も区切らなければいけません。
これでは、件数が増えた場合、かなり面倒な作業をしないと、数式を作ることができないということになってしまいます。
では、どうしたらいいのでしょうか？
まずは、合計値の場合からみてみましょう。
B6・B9・B12には、SUM関数をつかった数式で小計値を算出しています。
B13もSUM関数をつかっていますが、オートSUMボタンをつかって、合計をつかうと、その範囲選択内にあるデータ内でSUM関数の算出結果のみを合算してくれます。
B13の数式は、自動的に、
=SUM(B12,B9,B6)
と生成してくれます。
SUM関数をつかうと、B3:B12をドラッグしても、SUM関数のところだけをつかってくれるので、便利です。
では、最大値はどうなのでしょうか？
オートSUMボタンにある、最大値をつかって、B3:B13までを範囲選択してみると、データであろうが、小計値であろうが、合計値だろうが、関係なく範囲選択内の、最大値を算出してしまいます。
SUM関数をつかったところを除外して算出してくれることはありません。
実は、SUM関数やMAX関数だと、このような帳票には不向きな関数なのです。
今回のような場合は、SUBTOTAL関数かAGGREGATE関数をつかうことで、一気に問題を解決することができ、合理的に数式を作ることができます。
SUBTOTAL関数をつかってみた場合をみてみましょう。
最大値のC14は、小計や合計の値ではなく、店舗の最大値が表示されています。
まずは、小計の数式を確認してみます。
C6の数式は、
=SUBTOTAL(109,C3:C5)
と設定しています。
引数の109をつかうことで、合計を算出することができます。
残りも同じようにSUBTOTAL関数をつかっています。
C13の数式は、
=SUBTOTAL(109,C3:C12)
と設定しています。
範囲選択も小計のセルのみを選択するわけではなく、全体を選択してもSUM関数どうように、SUBTOTAL関数で算出した値のところだけを合算してくれいます。
問題の最大値を算出しているC14の数式は、
=SUBTOTAL(104,C3:C13)
引数の104は、最大値を算出する番号です。
範囲選択は、C3:C13とデータも小計値も合計値も関係なく範囲選択していますが、最大値がきちんと算出されています。
このように、途中に小計や合計が含まれる帳票のような表の場合には、SUM関数やMAX関数ではなくて、SUBTOTAL関数やAGGREGATE関数をつかうことで、結果的に効率よく数式をつくることができます。
ケースバイケースで、どの関数をつかうのが効率的なのかを考えながら帳票をつくるといいかもしれませんね。
5/26/2021
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関数をつかって確認してみるというのもいいかもしれませんね。
3/20/2020
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関数」を使っても同じように最大値を求めることができます。
12/30/2019
Excel。条件付き書式を設定がオートフィルターで抽出しても連動させたい【Conditional formatting】
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関数でも問題はありません。
9/10/2019
Excel。列を非表示にしても、合計値が変わるようにしたい【Hide columns】
Excel。列を非表示にしても、合計値が変わるようにしたい
＜CELL+SUMIF関数＞
計算表で、列を非表示にしても、合計値が連動して変わるようにしたいと思うわけですが、簡単に算出できないようです。
【行の非表示対応は？】
行を非表示した時に連動して合計値を算出するには、SUBTOTAL関数かAGGREGATE関数を使うことで算出することができます。今回は、B6にSUBTOTAL関数を使って、確認してみましょう。
B6をクリックして、直接手入力で、SUBTOTAL関数を作っていきましょう。
関数挿入ダイアログボックスを使うと、集計方法の引数を設定するのがわかりにくくなってしまいます。
B6に作る数式は、
=SUBTOTAL(109,B2:B5)
集計方法の引数でSUMが9と109の２つが用意されていますが、非表示に対応しているのが、109なので、引数は109を使用します。
範囲選択は、B2:B5です。
オートフィルを使って数式をコピーしたら、3行目を非表示にしてみましょう。
6行目の合計行の数値が変わったことがわかります。
これを列でも行いたいのが今回の目的です。
【列には対応していない！】
H2にSUBTOTAL関数で算出すればいいのではと考えると思いますが、そう簡単にいきません。SUBTOTAL関数かAGGREGATE関数ともに、列の非表示には対応していません。
この2つの関数とも、レコードの小計を算出する関数なので、基本、上から下へのデータ、つまりレコードに対応しているので、左から右へのフィールドの計算には対応していません。
H2にSUBTOTAL関数を作って確認してみましょう。
H2には、次の数式を作ります。
=SUBTOTAL(109,B2:G2)
では、4月を非表示にしてみましょう。
合計値に変化は見られません。
Excelでは、列の非表示に対応した数式を作ることは出来ないのでしょうか？
自力で対応しなければならないのでしょうか？
【CELL関数を使ってみる】
非表示といっても、消えてしまうわけではありません。列幅が0（ゼロ）になっているだけです。
だから計算されてしまうわけです。
ここで着目したいのは、列幅が0（ゼロ）ということ。
つまり、0（ゼロ）より大きければ表示されているわけですから、列幅が0より大きかったら計算する計算式を作れればいいわけです。
条件付きで合計値を算出するので、SUMIF関数を使えばいいことに気づきます。
ただ、列幅はどうやったら算出することができるのでしょうか？
そこで登場するのが、CELL関数。
CELL関数は、セルの情報を算出してくれる関数です。
CELL関数をつかって、列幅を算出してくれれば、なんとかなりそうです。
【列幅を算出する】
B7に=CELL("width",B1)
という数式を作って、オートフィルを使って数式をコピーしましょう。
このCELL関数も直接入力で数式を作ることをお勧めします。
すると、列幅を表示してくれます。
このCELL関数は、算出した時点の情報を算出していますので、数式を設定してから、列幅を変えても、算出された数値は変動されません。
その場合は、F9キーか、数式タブにある「再計算実行」をクリックする必要があります。
H2にSUMIF関数で数式を作っていきます。
範囲には、$B$7:$G$7
検索条件は、”>0”
合計範囲は、B2:G2
として、OKボタンをクリックします。
H2の数式は、
=SUMIF($B$7:$G$7,">0",B2:G2)
それでは、列を非表示にして、必ず、再計算実行しましょう。
ちょっと、面倒くさい方法ですが、列の非表示に対応して算出することができました。
5/19/2019
Excel。今再びの関数基本。数える関数全員集合！COUNT関数～AGGREGATE関数【COUNT】
Excel。今再びの関数基本。数える関数全員集合！COUNT関数～AGGREGATE関数
＜COUNT関数～AGGREGATE関数＞
現場でお馴染みの数える関数ですが、色々ありまして、まとめて一度確認しておきましょう。次のような表があります。
【数値を数えるならCOUNT関数】
参加人数などを数えたい場合、もしNoなどの数値の列があれば、とても簡単に算出することができるのが、COUNT関数です。C9に算出してみましょう。オートSUMボタンの▼をクリックして、数値の個数を選び範囲選択して確定させます。
ココでポイントになるのは、A列のように数値でなければなりません。
今回は、A2:A7を範囲選択します。
C9の数式は、
=COUNT(A2:A7)
とても簡単に算出することができますが、問題なのは、「数値の個数」であること。
つまり、B列のように『文字』は数えてくれないわけです。
【空白以外を数えるのがCOUNTA関数】
B列のような文字も数えたい場合は、COUNTA関数を使います。COUNTA関数は、文字も数値も対象になります。テキストなどには、空白以外が対象になるように書かれているものが多いですが、計算式の算出結果が空白の場合は、数える対象になってしまいます。
C10に算出していきます。
COUNT関数と同じように、途中まで作っていきます。
残念ながら、0件と算出されました。
数式は、
=COUNT(B2:B7)
と出来上がっていますので、「T」と「(」の間に「A」と入力すればCOUNTA関数に変わり算出することができます。
C10の数式は、
=COUNTA(B2:B7)
これで、算出することができました。
【条件が一つの場合はCOUNTIF関数】
得点が80点以上の件数を知りたい場合は、COUNT関数やCOUNTA関数では対応することができません。そこで条件付きで対応している関数が、COUNTIF関数とCOUNTIFS関数です。
今回は、80点以上と条件が一つだけ（単数条件）なので、COUNTIF関数を使います。
慣れれば、手入力で数式を作るのが楽なのですが、まだExcelの手入力になれていない人は、ダイアログボックスで作っていくといいでしょう。
C11をクリックして、COUNTIF関数ダイアログボックスを表示しましょう。
範囲には、次の検索条件が含まれている範囲なので、C列の得点である、C2:C7を設定します。
検索条件ですが、80点以上なので、「”>=80”」と入力します。比較演算子と数値を合わせて使うときには、「””(ダブルコーテーション)」が必要になります。
なお、比較演算子とセル番地を使うときには、「”>=”&セル番地」（例：”>=”&C3）という表示の仕方になります。
あとは、OKボタンをクリックして数式は完成です。
直接でなくても、80点以上の件数を数える方法はあります。
たとえば、E列の80点以上という列のように該当するデータをわかるようにしておいて、その結果の数を数えることでも求めることができます。
今回、E2には、
=IF(C2>=80,"○","")
というIF関数を使って、80点以上なら○。そうでなければ、空白。という数式を作っています。
なので、○の個数で何名該当しているかがわかるわけです。
そこで、空白以外ということで、COUNTA関数を使ってみると、どういう結果が算出されるのでしょうか？
結果は、6。
なんと空白も数えてしまっています。確認できたように、COUNTA関数は、数式を使って算出された結果の『空白』も数えてします。
このような場合にも、COUNTIF関数をつかって、「○」を数えるようにすればいいわけです。
【複数条件は、COUNTIFS関数】
条件が複数になった場合は、複数形ではありませんが、IFにSをつけた、COUNTIFS関数を使うことで算出することができます。今回は、80点以上で、セミナー参加希望の件数を求めていきます。
C13に結果を出しますので、COUNTIFS関数ダイアログボックスを表示しましょう。
検索条件範囲1には、得点のC2:C7
検索条件1には、80点以上ということで、”>=80”
検索条件範囲2には、セミナー希望のD列で、D2:D7
検索条件2には、”○”と入力します。
数式はというと、
=COUNTIFS(C2:C7,">=80",D2:D7,"○")
【空白を数えるのは、COUNTBLANK関数】
COUNTIF関数で検索条件を「””（空白）」で設定しても空白セルを数えることはできますが、空白セルを数える専用の関数があります。それが、COUNTBLANK関数。
このCOUNTBLANK関数は、引数に範囲を設定するだけで、空白を数えてくれますので、COUNTIF関数よりも簡単に空白を数えることができますし、計算結果が空白だったものも、空白として数えてくれます。
C14の数式は、
=COUNTBLANK(D2:D7)
【非表示に対応するにはSUBTOTAL関数・AGGREGATE関数】
行が非表示になると、COUNT系の関数では、対応されません。非表示になった行を除いて算出させるには、SUBTOTAL関数・AGGREGATE関数を使う必要があります。
SUBTOTAL関数・AGGREGATE関数ともに、手入力することをお勧めします。
SUBTOTAL関数。
手入力していくと、入力補助が出てきますので、今回は、2がCOUNTとなっていますので、選択したくなりますが、非表示に対応しておりませんので、102のCOUNTを選択するようにします。100番台は、非表示に対応してくれます。
C15の数式は、
=SUBTOTAL(102,A2:A7)
AGGREGATE関数。
最初の入力補助は、集計方法。今回は2番のCOUNTを選択します。
オプションですが、今回は、単純に非表示の行に対応する、5番を採用します。
C16の数式は、
=AGGREGATE(2,5,A2:A7)
では、非表示にしてみましょう。
非表示に合わせて、対応してくれましたね。
このように、数えるだけでも様々な関数がありますので、現場に沿った関数を見つけられるといいですね。
登録:
投稿 (Atom)