エクセル関数超技 | セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH) | Excel関数の解説、関数サンプルと高等テクニック



最終更新日:2015-03-16

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


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


エクセルの表には、集計等の計算式を入れますが、

その集計範囲は固定になっているため、

データの追加時には注意が必要になります。


参照しているセル範囲の途中に、行挿入、列挿入しなければ、参照範囲は広がりません。

しかし、これはあまりにも操作性が良くないです。


参照範囲を可変にする為に使用する関数は、

OFFSET関数
COUNTA関数
MATCH関数


上記の関数について良く理解してからお読みください。

また、
徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)
こちらも参考にして下さい。

以下の表を作成します。


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 個まで指定できます。


SUM関数

これは省略します、よろしいですよね。


では、実際に
全合計(E3)
開始日(E4)
終了日(E5)

期間合計(E5)
それぞれの設定方法です。

全合計(E3)

=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)

まずは、名前定義を作成します。
  1. Ctrl+F3で名前定義を起動し、新規作成。メニュー等からの起動はバージョン毎に違います。
  2. 「名前」に「日付」と入力。
  3. 2007以降の場合、範囲は「ブック」、2003にはありません。
  4. 「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)
  5. OK」
=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

COUNTAでA列のデータの個数を取得し、
A2からデータ個数分のセル範囲を決定しています。
E4、E5にリストを設定
  1. E4、E5セルを選択
  2. [データ]-[入力規則]
  3. [設定]の[入力値の種類]で[リスト]を選択
  4. [元の値] に「日付」と入力。F3を押すと名前定義の一覧から選択できます。
  5. 「OK」


期間合計(E5)

=SUM(OFFSET($B$2,
MATCH($E$3,OFFSET($A$2,0,0,COUNTA($A:$A)-1,1),0)-1,0,
MATCH($E$4,OFFSET($A$2,0,0,COUNTA($A:$A)-1,1),0)-
MATCH($E$3,OFFSET($A$2,0,0,COUNTA($A:$A)-1,1),0)+1,1))


かなり長い数式ですね、ちょっと笑えます。
この一番外側のOFFSETは、
B2のセルを、開始日までずらし、終了日までの高さのセル範囲を求めています。

作る時は、以下のように順番に作成します。
=SUM(B4:B8)
 まずは、手作業で、SUM関数を入れてみます。
 このB4:B8を可変にすれば良いわけです。

=SUM(OFFSET(B2,2,0,5,1))
 B4:B8をOFFSETに置き換えます。
 引数は、とりあえず定数で入れてみます。
 計算結果を見て、正しいことを確認しておきます。

=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,MATCH(E4,A2:A11,0)-MATCH(E3,A2:A11,0)+1,1))
 OFFSETの引数で開始位置である5をMATCHに置き換えます。
 5=終了日の位置-終了日の位置+1

=SUM(OFFSET(B2,MATCH(E3,OFFSET(A2,0,0,COUNTA(A:A)-1,1),0)-1,0,
MATCH(E4,FFSET(A2,0,0,COUNTA(A:A)-1,1),0)-
MATCH(E3,OFFSET(A2,0,0,COUNTA(A:A)-1,1),0)+1,1))

 A2:A11
 OFFSET(A2,0,0,COUNTA(A:A)-1,1)
 で置き換えます。

見やすくするために、絶対参照である$を省略しています。
絶対参照は、最初から入れられれば良いですが、
難しければ、すべて作成した後に、まとめて入れても良いでしょう。

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



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

グラフのデータ範囲を可変にする
セルの個数を数える関数
【奥義】大量データでの高速VLOOKUP
数値を時刻に変換
関数のネスト方法
ワイルドカードが使える関数
グラフで特定の横軸の色を変更する

新着記事 ・・・新着記事一覧を見る

空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
6.変数とデータ型(Dim)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.定数と型宣言文字(Const)|ExcelマクロVBA入門



  • >
  • >
  • >
  • セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)

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


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




    ↑ PAGE TOP