教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

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

   

 

 

 

 

 

 

 


 

 

 

 

 


 

 

 

 

 

 

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

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

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

 

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.を入力する場合、ドロップダウンリストから選択した方が素早く入力できます。

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

 

 

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

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

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

 

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

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows