セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）

最終更新日：2020-04-07

セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）

数式の参照範囲を可変対応させます。
つまり、入力データにより自動で拡張・縮小されるように関数を設定します。

エクセルの表に、単純に集計等の計算式を入れてしまうと、
集計範囲は固定になっているため、データの追加時に計算範囲が違ってきてしまう事になります。。

数式が参照しているセル範囲の途中に、行挿入、列挿入した場合は、計算式の参照範囲は自動で拡張されますが、
数式が参照しているセル範囲の直ぐ下にデータを追加した場合は、セルの参照範囲は広がってくれません。
これは非常に都合が悪いです。
入っている数式を意識しなければ、データの追加もままならないという事になってしまいます。

そこで、数式のセル参照範囲を可変にして、参照範囲を自動的に拡張・縮小されるようにしたくなります。
セルの参照範囲を可変にする為に使用する関数は、

OFFSET関数

基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある、指定の高さと幅のセル範囲の参照を返します。つまり、基準セルから、指定数だけ移動したセルを起点として、指定の大きさのセル範囲を取得出来ます。OFFSET関数の書式 OFFSET(基準,行数,列数,[高さ],[幅]) 基準基準となるセル範囲の参照を指定します。

COUNTA関数

セル範囲に含まれる、空白ではないセルの個数を数えます。引数には、セル参照、またはセル範囲が指定できます。COUNTA関数の書式 COUNTA(値1[,値2,…]) ※[]で囲まれている部分は省略可能です。

MATCH関数

セルの範囲内で指定された項目を検索し、その項目の相対的な位置を返します。セル範囲は、縦方向・横方向のどちらでも指定可能です。MATCH関数の書式 MATCH(検査値,検査範囲,照合の型) 検査値値(数値、文字列、または論理値)、またはこれらの値に対するセル参照を指定できます。

これらを組み合わせることで、セルの参照範囲を可変にする事ができます。
以下の解説は、上記の関数についてある程度理解していることを前提にしていますのでね
これらの関数が良く分からない場合は、まずは上記のページをお読みください。
また、

徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)

検索されるキーワードで最も多いのが、MATCH関数 INDEX関数セル範囲から、指定された行と列が交差する位置にあるセルの参照を返します。INDEX関数の書式INDEX(範囲,行番号[,列番号])範囲セル範囲を指定します。セル範囲が1行または1列である場合、行番号または列番号はそれぞれ省略することができます。

こちらも参考にして下さい。

説明で使用するエクセル表

E2には、全期間の売上合計
E3、E4には、日付の、入力規則のリストを設定
E5には、指定期間の売上合計

これらを、12行目以降にデータを追加しても、自動で参照範囲が変更されるように設定します。

まずは、使用する関数の簡単な説明からです。

OFFSET関数

基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。

OFFSET(基準,行数,列数,高さ,幅)

基準

基準となるセル範囲の参照を指定します。

行数

基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。

列数

基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。

高さ

オフセット参照の行数を指定します。高さは正の数である必要があります。

幅

オフセット参照の列数を指定します。幅は正の数である必要があります。

MATCH関数

指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。

MATCH(検査値,検査範囲,照合の型)

検査値

表の中で必要な項目を検索するために使用する値を指定します。

検査範囲

検査する隣接したセル範囲を指定します。

検索の型

-1、0、1 の数値のいずれかを指定します。
1を指定すると、検査値以下の最大の値が検索されます。
0を指定すると、検査値に一致する値のみが検索の対象となります。
-1を指定すると、検査値以上の最小の値が検索されます。
省略すると1を指定したものと見なされます。

COUNTA関数

セル範囲に含まれる空白ではないセルの個数を返します。
COUNTA 関数では、エラー値や空の文字列 ("") を含め、すべての種類のデータを含むセルが計算の対象となります。

COUNTA(値1, [値2], ...)

値1

必須。計算対象として含める値を表す 1 つ目の引数。

値2, ...

省略可能。計算対象として含める値を表す追加の引数。引数は、最大 255 個まで指定できます。

セルの参照範囲を可変にする数式の解説

では、実際に
全合計（E3）
開始日(E4)
終了日(E5)
期間合計(E5)
それぞれの設定方法について、以下で順に説明します。

全合計（E3）の数式

全合計なので単純に
=SUM(B:B)
これでもちろん良いのですが、
今回セル範囲を可変にする方法の解説として、
データ範囲だけをSUMするようにしてみました。

=SUM(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1))

$B$2,0,0
B2から、下に0、右に0移動、
つまり、B2になります。

COUNTA($A:$A)
A列にあるデータの個数です。
1行目が見出しになっていますので、1引いています。
上の図では、11になります。
従って11-1=10が、OFFSETの高さになります。
B列でもよいです、ここでは日付を基準にしているだけです。
つまり、
B2から、高さ10、幅1のセル範囲になります。
つまり、
B2～B11になります。
結局は、
=SUM(B2:B11)
となるわけです。

COUNTAでデータの個数を取得し、セル範囲を決定していますので、
データの追加・削除に自動で対応されるようになります。

開始日(E4)と終了日(E5)に名前定義とリストを設定

日付の範囲に名前定義を作成

「数式」タブの「名前定義」
Ctrl+F3で名前定義を起動し、新規作成。

「名前」に「日付」と入力。
2007以降の場合、範囲は「ブック」、2003にはありません。
「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」
OK」

=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

COUNTAでA列のデータの個数を取得し、
A2からデータ個数分のセル範囲を決定しています。

E4とE5にリストを設定

入力規則を設定します。

E4、E5セルを選択
[データ]-[入力規則]
[設定]の[入力値の種類]で[リスト]を選択
[元の値] に「日付」と入力。F3を押すと名前定義の一覧から選択できます。
「OK」

期間合計(E5)の数式

=SUM(OFFSET(B2,MATCH(E3,日付,0)-1,0,MATCH(E4,日付,0)-MATCH(E3,日付,0)+1,1))

少々長い数式ですね。
この一番外側のOFFSETは、
B2のセルを、開始日までずらし、終了日までの高さのセル範囲を求めています。

作成手順を詳しく見てみましょう。
作る時は、以下のように順番に作成します。
=SUM(B4:B8)

まずは、手作業で、SUM関数を入れてみます。
このB4:B8を可変にすれば良いわけです。
B4:B8のセル範囲は適当で良いです、この範囲を可変にするのですから。

=SUM(B4:B8)
↓
=SUM(OFFSET(B2,2,0,5,1))

B4:B8をOFFSETに置き換えます。
引数は、とりあえず定数で入れてみます。
計算結果を見て、正しいことを確認しておきます。

=SUM(OFFSET(B2,2,0,5,1))
↓
=SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0,5,1))

OFFSETの引数で開始位置である2をMATCHに置き換えます。
つねに、計算結果を見て、正しいことを確認します。

=SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0,5,1))
↓
=SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0,
MATCH(E4,A2:A11,0)-MATCH(E3,A2:A11,0)+1,1))

OFFSETの引数で開始位置である5をMATCHに置き換えます。
5=終了日の位置-終了日の位置+1

=SUM(OFFSET(B2,MATCH(E3,A2:A11,0)-1,0,
MATCH(E4,A2:A11,0)-MATCH(E3,A2:A11,0)+1,1))
↓
=SUM(OFFSET(B2,MATCH(E3,日付,0)-1,0,
MATCH(E4,日付,0)-MATCH(E3,日付,0)+1,1))

A2:A11を、日付の可変範囲指定に変更します。
日付の可変範囲の数式は、
OFFSET(A2,0,0,COUNTA(A:A)-1,1)
ですが、これは、
日付の範囲に名前定義を作成
ここで、「日付」と名前定義してあります。
つまり、A2:A11を日付に置き換えてやれば良いという事になります。

これで期間合計(E5)の数式が完成です。

上記説明では、
数式を見やすくするために、絶対参照である$を省略しています。
絶対参照は、最初から入れられれば良いですが、
$をいれると、どうしても数式が見づらくなるので後回しにしても良いでしょう。
数式が完成した後に、まとめて$を入れても良いでしょう。

短く易しい数式で

途中の数式では、長く複雑に感じる場合も出てきますが、
関数のネスト（入れ子）が多いだけで、一つ一つの関数は難しいものではありません。
エクセルを使う一人一人にとって必要な関数は、そんなに多くないはずです。
必要なことは、その関数をいかに組み合わせるかにかかっています。
整理して、順序よく、考えれば、難しいく見える数式も作成できるようになります。

ただし、難しく長い数式を作ったからと言って、決してそれがすごい事ではありません。
本来は、難しい数式を作らなくても良いようにシートを工夫するべきことです。
同じことを行っているのなら、なるべく短く易しい数式の方が良いはずです。
その為には、
作業列を使って数式を細かい単位で完成させる等の工夫をしてください。

数式.作業セル、計算セル

関数の引数に、さらに関数を入れ、関数をネスト（入れ子）にしますが、このネストが多くなると、数式が複雑になりすぎてしまい、他人が見たときに数式の意味するところが分からなくなってしまいます。数式を書いた本人であっても、時が経つと解読が困難になる場合もあります。

同じテーマ「エクセル関数超技」の記事

VLOOKUPを他の関数でやる方法
 誕生日一覧から、指定誕生月の人を全員取り出す
 徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）
グラフのデータ範囲を可変にする
 セルの個数を数える関数
 【奥義】大量データでの高速VLOOKUP
数値を時刻に変換
 関数のネスト方法
 ワイルドカードが使える関数
 グラフで特定の横軸の色を変更し基準線を引く

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.マクロって何？VBAって何？｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.とにかく書いてみよう（Sub,End Sub）｜VBA入門
10.マクロはどこに書くの（VBEの起動）｜VBA入門

ホーム
エクセル全般
エクセル関数超技
セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

エクセル関数超技
セルの参照範囲を可変にする（OFFSET,COUNTA,MATCH）