ExcelのSUM関数やVLOOKUP関数などで「セル範囲を参照する数式」を作成した後、データ追加によりセル範囲を修正する作業は非効率です。
修正が漏れると、集計対象に含まれないなどのミスも発生します。
数式作成後のデータ追加に備えて「数式のセル範囲修正」を省力化するため、
- セル範囲を「列」で指定
- セル範囲を「テーブル」で指定
を活用できないか検討します。
内容：
セル範囲を「列」で指定
VLOOKUP関数での事例
下図の上側のVLOOKUP関数の数式では、範囲を「$B$2:$C$7」で指定しています。
この場合、数式作成後のデータ追加（事例で、$B$8:$C$9の箇所）を数式に反映するために、セル範囲の修正が必要です。
下図の下側のVLOOKUP関数は、範囲を「$B:$C」と「列」で指定しています。
「列」で指定しているので、「$B:$C」列にデータ追加がある場合も同じセル範囲となり、数式のセル範囲修正は不要です。
セル範囲を「列」で指定する場合の注意点
セル範囲を「列」で指定する場合の前提は以下です。
- 指定した「列」に不要なデータが存在しない
例えば、全く異なる表（データ）が同じ列にあると、そのデータも含めた計算結果となる。 - 「列」の参照でも適切に計算される数式である
適切に計算される数式であるかは、セル範囲を「列」の指定有無で計算結果が同じかなどを個別に確認する（指定した「列」に意図しないデータが入力されている場合もあるため、その影響を理解して使用する）。
数式の仕様の一例
SUM関数は、範囲 (セルのグループ) の”数値”を合計する関数
”数値”を合計する関数のため、セル範囲に文字列等が含まれていても計算されない。
範囲 (セルのグループ) の数値を合計できます
AVERAGE 関数は、文字列や空白は無視されるが、値 0 (ゼロ) は計算の対象
文字列や空白は無視されるので良いが、値 0 (ゼロ)がある場合は注意が必要。
引数として指定したセル範囲またはセル参照に文字列、論理値、または空白セルが含まれている場合、これらは無視されます。ただし、値 0 (ゼロ) を含むセルは計算の対象となります。
（参考）AVERAGEA関数は、文字列や空白も計算の対象で、「列」で指定に不向き。
初心者で使用場面はない見込だが、”A”がつくAVERAGEA関数は、今回のセル範囲を「列」で指定する方法は不向きな場合が多いと思います。このような関数もあるので参考に紹介。
引数として指定した配列またはセル参照に文字列が含まれる場合、これらは 0 (ゼロ) と見なされます。 空白文字列 ("") も 0 (ゼロ) として評価されます。
VLOOKUP関数は先に一致した値の行を取得
VLOOKUP関数の計算結果は先に一致した値の行を取得します。
セル範囲を「テーブル」で指定
Excelには「テーブル」という機能があります。
範囲を「テーブル」で指定すると、データ追加時に範囲の自動拡張等を利用できたり、修正が容易というメリットがあります。
ただし、利用者が「テーブル」に不慣れなケースなどは他への説明コストも必要となり、利用するかは目的や状況によります。
下図は、セル$B$2:$C$9が「テーブル1」という名前のテーブルで、
=VLOOKUP(E3,テーブル1,2,FALSE)
といった数式で範囲指定した事例です。
テーブルの概要は以下です。
以上、Excelでデータ追加時に「数式のセル範囲修正」を省力化する方法でした。
