|
リストを対象に計算を行うことは多々あります。
しかしそのリストにデータが増え続けると、その範囲を再度指定しなおす必要があります。
これは面倒なので、入力データが増えると自動的に参照範囲も更新される方法をご紹介します。
 |
OFFSET関数 |
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.を入力する場合、ドロップダウンリストから選択した方が素早く入力できます。
入力規則の「リスト」も、随時更新されるデータを参照元にすることが可能です。
-
ドロップダウンリストとして設定したい範囲（D列のセル）を選択
-
「データ」−「入力規則」をクリックし、「入力値の種類」＝「リスト」とする
-
元の値ボックスに「=OFFSET ($A$2 , 0 , 0
, COUNTA (A:A)
-1
, 1) 」と入力し、「OK」をクリック
この式により、A2を基点とし、A列にある個数分だけの「行」（※
「−1」はA1セルの「No.」を数えないようにする為）と、1列分を常にリストの参照元とすることができます。
|