Excel。行を非表示にしても条件を満たす件数を数えたい場合どうしたらいい？
＜SUBTOTAL関数＞
以前累計を算出するときに、行が非表示になっても、
ちゃんと累計を算出できるようにする方法をご紹介したことはありますが、
今回のリクエストは、
「行を非表示にしても、完成してあるものの件数を数えたい」
というものでした。下記の表をご覧ください。
求めたいものは、D列の完成済みの列にある、
完了という文字が入力されているセルの数なのです。
一見、前回の累計と同じように、SUBTOTAL関数でいいだろうと思うかもしれませんが、
このSUBTOTAL関数。計算の種類でCOUNT関数やCOUNTA関数があるのですが、
残念ながら、COUNTIF関数が無いのです。
つまり、完了と空白セルでしたら、文字数を数えればいいので、
COUNTA関数でもいいのですが、
今回は、全部のセルに文字が入力されているわけなので、
COUNTA関数では対応しきれない訳なのです。
よく、このような場合、計算式一発算出をしちゃいたくなるのですが、
逆に関数が煩雑になってしまう傾向があります。
このようなケースの場合は、
使いやすい・作りやすい・メンテナンスしやすいということを考慮して、
作業列を作ってあげたほうが、いいと思います。
では、B1の登録数から作成していきましょう。
B1をクリックして、SUBTOTAL関数を作成してきますが、このSUBTOTAL関数。
ダイアログボックスで作成すると、計算方法をどの番号にしたらいいのかが、
わからないので、できたら、
【手入力】
することをお勧めします。
結果B1には、
=SUBTOTAL(102,A6:A16)
という数式が設定されました。なお102のCOUNTを使いますが、100番台でないと、行の非表示には対応をしてくれません。
結果は、11件と算出されました。
で、ここからが、本番。
B2には、完了という文字が入力されているセルの件数を数えたいわけです。
しかしながら、SUBTOTAL関数ではCOUNTIFの集計方法はない。
となると、様々な関数を駆使して結果を求めるよりも、
計算列を作ってあげたほうがわかりやすく、スマートだと思いますので、
F列に数値変換という計算列を設けておきます。
このF列には、D列が完了だったら1。そうでなければ0というIF関数を作ってあげます。
その結果に対してSUBTOTAL関数を使って、合計してあげればいいわけです。
では、F6をクリックして、IF関数ダイアログボックスを表示しましょう。
論理式には、D6="完了"
真の場合は、1
偽の場合は、0
OKボタンをクリックしましょう。あとは、オートフィルで数式をコピーします。
なお、数式は、
=IF(D6="完了",1,0)
ですね。そして、B2をクリックして、SUBTOTAL関数を作成していきます。
今度は、合計ですので、集計方法は109を使います。
結果、B2の数式は、
=SUBTOTAL(109,F6:F16)
あとB3は、単純に減算の式ですから、=B1-B2
これで完成しました。
では、適当に行を非表示にしてみます。
ちゃんと、非表示に合わせて計算結果が変わりましたよね。
このように、関数ばかりに頼るのではなくて、
計算列などを使ったほうがいいケースも多々ありますので、
ケースバイケースで作っていくといいですね。
