＜SUM+INDIRECT+COUNT関数＞
合計を算出する値が上にある。
そして、データが増減しても自動的に計算対象範囲が、
その増減に合わせて変更させるような計算式を作りたいんだけど、
どうやったらいいの？というご質問をいただきました。
このような場合、テーブルにしてあげると意外と簡単に、作ることができるのですが、
今回は、テーブルにしたくないということでして…。
そういうことになりますと、関数でどうにか作ってみましょうということで、
早速作っていくことにしましょう。
下記のような表があります。
B2に合計値が算出できる式を作りたいわけですが、
その範囲はデータがあるところまでを自動的に判断させたいというのが今回も目的です。
そこで、先にB2に入る数式をご紹介しておきます。
=SUM(C5:INDIRECT("C" & COUNT(C:C)+4))
このように、Excelの経験値が少ない方だと、お手上げってなってしまうかと思われます。
ですから、本来は、一つずつ作業結果のセルを作ってあげるほうがいいと思います。
それを踏まえたうえで、この数式の説明をしていきます。
まず出だしのSUM関数。これは、おなじみなので、割愛。
それで、範囲がC5から。データのあるセルの最後までということになります。
そこが、INDIRECT関数以降にあたります。
このINDIRECT関数はその結果そのものを、数値や文字として使うことができる関数です。
このINDIRECT関数の中を確認してみると、
”C”& これは、Cという文字に次の文字を接続させるということになります。
では、接続されるものはというと、それがCOUNT関数の登場となるわけです。
COUNT関数は、数値のあるセルを数えます。
COUNT(C:C)とは、C列全体にある数値の数ということになります。
今回は、10が算出されます。
それでCと10を結合するとC10になるのですが、これですと、合計範囲が、
C5:C10となってしまいます。
そこで、
COUNT(C:C)+4とプラス4をする必要があります。
これで、C14という文字が作れました。
これで、=SUM(C5:C14)という数式が作成できたことになります。
よって、合計値は640を算出できたわけです。
INDIRECT関数は何かと重宝しますので、覚えておくといい関数の一つですね。
では、データを1件追加してみましょう。
自動的に合計値が変わりましたね。
ちなみに、現場で煩雑な数式がある場合には、【数式の検証】を行うといいかもしれませんね。
ためしにやってみましょう。
B2をクリックしておいて、数式タブの【数式の検証】をクリックしてみると、
検証ボタンをクリックすると、まず下線がある箇所。COUNT(C:C)を確認します。
次に、11+4を確認しますので、検証ボタンをクリックします。
そして、次のステップを確認していきます。
この手順でどんどん検証を進めていきましょう。
すると、最後に検証結果、すなわち算出されるものが確認できましたね。
なお、全くひっくり返してしまうのですが…
テーブルにしてしまうと、このような関数は全く不要になります。
テーブルには、計算表というテーブル名が設定されています。
テーブルにすれば、構造化参照になるため、SUM関数だけでこの問題は解決できるのです。
ということで、ネストを繰り返す関数で算出するのもいいですが、
様々なものを使ってあげるともっと、簡単に算出できることもありますので、
あまり、関数にはこだわらないというのもいいのかもしれませんね。
