EXCELでOFFSETとCOUNTAを用いて範囲を可変にする

今回ご紹介するのは、通常の合計、平均など表の一番下に計算式を置いてあり、それが追加されて範囲が可変するという事ではなく、そうではない場合の範囲を可変にする使い方などについてご紹介をしたいと思います。

範囲を可変にする必要性

色々とあるのですが、私が普段使用する場合ではあるデータをダウンロードするのですが、そのデータはダウンロードする日付によってデータ数が変更します。
そうなると、以前の計算式の範囲を毎回ずらす必要があり、非常に手間ですのでデータを貼りつけるだけでその合計を出せるようになると楽なので使ったりします。
その場合に良く使われるのが、COUNTA関数を使ってOFFSET関数かINDIRECT関数の2つが良く使われていると思います。

COUINTA関数とは

一度は使った事があると思いますが、空白セル以外の個数を返してくれます。範囲指定も可能なので選択範囲の中で、空白以外のセル数を返してくれるので、他の関数と組み合わせる事も多い関数になります。
注意は空白セルは対象としないと言う点になります。

COUNTA 関数では、エラー値や空の文字列 (“”) を含め、すべての種類のデータを含むセルが計算の対象となります。たとえば、セル範囲に空の文字列を返す数式が含まれている場合、COUNTA 関数はその値を計算の対象とします。COUNTA 関数は空白セルを計算の対象にしません。
Microsoftオフィス、COUNTA関数解説より引用

OFFSET関数とは

Microsoft社のヘルプファイルを引用しようと思いましたが、[幅]の部分の説明が太さとかになっておりちょっと不安になったので控えておきます。
ザックリと理解するのであれば、基準から何行何列ずらした先のどの程度の範囲を参照するのか?という事をやってくれる関数になります。

OFFSET関数の引数について
OFFSET(基準, 行数, 列数, [高さ], [幅])
Microsoftオフィスサポート、OFFSET関数より引用

OFFSET関数の動きについて

A1を基準としてOFFSET関数を使った場合に、どの様な動きになるのかちょっと確認してみます。
OFFSET(A1,0,0,0,0)だと正常にならないので、高さと幅は「1」をする事でセルを参照する事になるのでその様にしています。
それぞれ、色がついている所が関数で指定した場所になります。

OFFSET(A1,0,0,1,1)

A1を指定します。

OFFSET(A1,0,1,1,1)

A1の位置から、1列右へ移動させたセルを指定します。

OFFSET(A1,1,1,1,1)

A1の位置から、1行下に、そこから1列右へ移動させたセルを指定します。

OFFSET(A1,1,1,2,1)

A1の位置から、1行下に、そこから1列右へ移動させ、高さが「2」であるのでB2、とB3の範囲セルを指定します。

OFFSET(A1,1,1,2,2)

A1の位置から、1行下に、そこから1列右へ移動させ、高さが「2」幅が「2」であるのでB2~C3の範囲セルを指定します。 OFFSET関数単体の所は参照が出来ないので「#VALUE」で表示されていますが、範囲の合計を求めているSUMではちゃんと計算をされています。

実際の範囲を可変にする

実際に下記の様な表があり、B1(青色)B2(赤色)セルには右に記載あるような関数を指定しております。

関数の説明としては、こんな感じです。
=SUM(OFFSET(G2,0,0,COUNTA(G:G)-1,1))
①G2を基準点にする。
②行・列は0なので動かさない。
③高さが「COUNTA(G:G)-1」の指定。
COUNTA(G:G)でG列に空白以外のセルの個数を確認するG1~G12までの12個あります。
このままだと、G2から12個目データ範囲、G2:G13の範囲を指定してしまいます。
そのため「-1」をする事でG2から「11」個目までの高さ(G12まで)をOFFSETに返しています。
④幅は「1」で他の列までは含まないようにする。
これだと下にデータが増えていったとしてもキチンと追加をされるようになります。

SUM関数で、現在の範囲を指定しておりますが、行を増やしてもSUM関数部分は更新されませんが、可変で作った関数は追加されます。

まとめ

OFFSET関数を使用した場合の範囲を可変にする方法をご紹介しました。
どういった動きをする関数であるか理解をすると、今まで使用している表でも活用が可能になる事があります。
他にもINDIRECT関数でも同じような事が可能ですが、次回以降でまたご紹介をしていこうかと思います。

  • シェア
  • twitter
The following two tabs change content below.

岩原正樹

コメントはこちらをクリック

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

  • twitter
  • このエントリーをはてなブックマークに追加
  • Pocket
PAGE TOP
LINE it!