合計範囲を自動更新する−INDIRECT関数・COUNTA関数・SUM関数

INDEX関数とCOUNTA関数を組み合わせると、SUM関数で合計する範囲を自動的に更新できることをご紹介しました。

「合計範囲を自動的に更新するには？」
というご質問は、新たなデータが随時追加されるようなワークシートを作成している方から、いただく定番的な質問で、その実現方法もいろいろあります。

先日ご紹介した、INDIRECT関数とCOUNTA関数を使って一番下のセルを表示する方法を組み合わせても、合計範囲を自動的に更新することができます。

▼サンプルファイル（003445.xls 24KByte）ダウンロード

サンプルファイルには、上記の例を「合計範囲の自動更新」シート、考え方をご理解いただくための「合計範囲の自動更新_導き方」シート、参考までに、INDIRECT関数とCOUNTA関数を組み合わせて一番下のセルを表示する例を「一番下のデータを取得」シート、その計算式の導き方を「一番下のデータを取得_導き方」シートに、それぞれ作成してあります。

「合計範囲の自動更新_導き方」シートのE2:E5セルに、「=SUM(B1:INDIRECT("B" & COUNTA(B:B)))」という数式の意味をご理解いただくために、
　「=SUM(B1:B11)」
　「=SUM(B1:INDIRECT("B11"))」
　「=SUM(B1:INDIRECT("B" & "11"))」
　「=SUM(B1:INDIRECT("B" & COUNTA(B:B)))」
という数式を入力してあります。

以下の説明と合わせてご確認ください。

まず、データがB11セルまで入力されているときにB列の合計を求めるには
　「=SUM(B1:B11)」
という数式でOKです。

この数式は、INDIRECT関数を使うと
　「=SUM(B1:INDIRECT("B11"))」
と記述できます。

更にこの数式は、IDIRECT関数の引数「"B11"」を分解して
　「=SUM(B1:INDIRECT("B" & "11"))」
と記述することができます。

ここで、INDIRECT関数の引数に出てくる「11」という値は、B列に入力されているデータと見出しセルを合計した数です。これはCOUNTA関数を使えば取得できます。COUNTA関数の引数を「B:B」として「COUNTA(B:B)」にしてやれば、常にB列全体をカウントできます。

「COUNTA(B:B)」を
　「=SUM(B1:INDIRECT("B" & "11"))」
の「"11"」と置き換えたのが
　「=SUM(B1:INDIRECT("B" & COUNTA(B:B)))」
という数式なのです。

考え方は、一番下のデータを取得する数式と同じです。（「一番下のデータを取得_導き方」シートをご確認ください。）

サンプルファイルのデータを変更したり、データを追加したりして
　「=SUM(B1:INDIRECT("B" & COUNTA(B:B)))」
という計算式の意味をしっかりご理解ください。