以下のような数式の場合、データを追加した際に数式の参照範囲を毎回修正する必要があります。

=SUM(B2:B6)
// 対象範囲がB2~B6の場合

今後、データが追加されることを考慮した数式の作成方法について３通りで解説していきます。

📘 Excel小技１００選（５００円）

方法１

予め参照範囲を広めに指定することで、データの追加に対応することができます。

=SUM(B2:B100)
// 予め100行目まで指定する

この方法のデメリットは、行の削除などがあった際に、対応範囲が変わってしまうという点になります。
例えば、以下の表の６行目のデータを行ごと削除してみます。

そのようにすると、以下のように数式の参照範囲まで小さくなってしまいます。

=SUM(B2:B99)
// B100→B99

このような作業を繰り返すことで、気が付いた時には参照範囲外になり、正しく集計されない場合もあるかと思います。

方法２

先ほどとは異なり、対象の列（行）全体を指定することで、データの追加に対応することができます。

=SUM(B:B)
// SUM関数では参照範囲内の数値のセル以外は集計対象外になる

この方法の場合は、列指定では列を削除した時にも参照範囲が変わらなくなります。

ただ、この方法にもデメリットが存在します。
それは、参照範囲が膨大になることによる処理速度の低下です。
SUM関数のように、参照範囲が広くても処理速度に大きく影響しない関数も存在しますが、SUMIF関数などの条件付き関数やVLOOKUP関数などの検索関数など、関数によっては、処理速度に影響する可能性があります。
そのため、列（行）全体の指定を多用する場合は、注意する必要があります。

方法３

テーブルを活用することで、データの追加に対応することができます。
この方法の場合、対象の範囲をテーブルに変換する必要があります。
テーブルに変換するには、対象の範囲内のセルを選択し、[挿入]タブの[テーブル]を選択します。

作成したテーブルを選択すると、[テーブルデザイン]タブが表示されるため、必要に応じてテーブルのデザインやテーブル名を変更してください。

テーブルに変換した状態で、数式にてテーブルの対象範囲を指定すること、自動的に構造化参照で参照式が入力されます。

=SUM(テーブル1[売上])
// テーブル名[項目名]

この参照方法の場合は、テーブルのサイズに合わせて参照範囲が設定されるため、データの追加時にも対応することができます。

おすすめの方法

表を参照する場合は、３つ目の方法で参照を活用することを推奨します。
テーブルと構造化参照については、以下の記事にて解説しております。

＞テーブルの活用方法
＞構造化参照とは

リンクの共有はこちらから行えます。

  リンクコピー    X Facebook はてなブックマーク Pocket