Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » 合計範囲を自動更新する−INDIRECT関数・COUNTA関数・SUM関数

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

対象:Excel2002, Excel2003, Excel2007

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

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

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

[スポンサードリンク]

INDIRECT関数とCOUNTA関数を組み合わせて一番下のセルを表示する方法がしっかり理解できてる方にとっては、決して難しくありません。


▼操作方法:範囲を自動的に更新して合計する
※B列のデータをE1セルに合計する例
E1セルに
「=SUM(B1:INDIRECT("B" & COUNTA(B:B)))」
という数式を入力する

▼サンプルファイル(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)))」
という計算式の意味をしっかりご理解ください。

[スポンサードリンク]

Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » 合計範囲を自動更新する−INDIRECT関数・COUNTA関数・SUM関数

TrackBack:0

TrackBack URL

Home » Excel(エクセル)の関数・数式の使い方 » 検索・行列 » 合計範囲を自動更新する−INDIRECT関数・COUNTA関数・SUM関数

「検索・行列」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.