Excel：随時追加されるデータを自動的に計算の対象にするには（OFFSET関数）

リストを対象に計算を行うことは多々あります。

しかしそのリストにデータが増え続けると、その範囲を再度指定しなおす必要があります。

これは面倒なので、入力データが増えると自動的に参照範囲も更新される方法をご紹介します。

■OFFSET関数

• OFFSET (基準値 , 行数 , 列数 , 高さ , 幅 ）
  OFFSET(基準とするセルを指定 , ○行移動 , ○列移動 ,セル範囲の高さ（行数） , セル範囲の幅（列数））
  　

例（1） 随時データが追加されるリストの合計金額を出す

= SUM (OFFSET (A1 , 1 ,1 , COUNT（B:B）, 1 )

A1を基準値として1行1列移動し（→B2に移動）、COUNT関数でB列にあるデータの個数を出し（→行数が出る）その範囲を合計する。

　

つまり、B2から5行（データ個数）1列分の範囲を合計する

答え 2251 （A1から1行1列移動し、5行1列分のセル範囲の合計を返す）

こ れにより今後データが増えてもCOUNT関数によって対象セルの範囲も更新されるので、合計金額も更新される。
（もし、COUNT関数で常に数えないセルなどがある場合は、COUNT（B:B)-1 などとしてください。）

例（2） 随時データが追加されるリストをVLOOKUP関数で参照するには

D列にNO.を入力すると、E列に商品名、F列に価格が表示されるよう、E列／F列にVLOOKUP関数を設定します。

＝ VLOOKUP （D2, OFFSET ($A$1 , 0 , 0 , COUNTA（A:A）, 3 ） , 2 , 0 ）

参照先範囲として、A1を基点に0行0列の位置から、A列に入力されたデータ数（行）：3（列）分を常に対象範囲とする。

この式により、E列はVLOOKUPの参照先範囲として、『A列に入力されている個数分の行』と『3列』の範囲を常に指定できます。
追加でデータが入力されれば参照範囲も更新される為、数式を変更する必要がなくなります。

例（3）  随時更新されるデータを入力規則の「リスト」として使用したい

↑例（2）のようにD列にNo.を入力する場合、ドロップダウンリストから選択した方が素早く入力できます。

入力規則の「リスト」も、随時更新されるデータを参照元にすることが可能です。

1. ドロップダウンリストとして設定したい範囲（D列のセル）を選択

2. 「データ」−「入力規則」をクリックし、「入力値の種類」＝「リスト」とする

3. 元の値ボックスに「=OFFSET ($A$2 , 0 , 0 , COUNTA (A:A) -1 , 1) 」と入力し、「OK」をクリック

　

この式により、A2を基点とし、A列にある個数分だけの「行」（※ 「−1」はA1セルの「No.」を数えないようにする為）と、1列分を常にリストの参照元とすることができます。