Excelで参照範囲を可変指定したい

Excelで参照範囲を可変指定する

ExcelでVLOOKUP、よく使うんです。

月次や週次の処理として使うので決まったフォーマットがあったりして、その上で処理しています。
Sheet1に抽出先項目があって、Sheet2やSheet3からマッチしたデータを・・・って感じです。

SQLで抜いたデータを抽出元としてSheet2・Sheet3に張り付けるとSheet1に記述されているVLOKUP関数でデータが抽出されるわけです。

が、毎回データ数が違うので参照範囲にデータ張り付けるたびに参照範囲を変更しなければならないのが面倒です。
しかも、決して少なくはないデータ数なので、いちいち再計算入ったりして超イライラです。

そこで、本日のデジタルストック。
データが増えたり減ったりしても大丈夫なようにExcelで参照範囲を可変指定したい時の指定方法!!

=====
2019.6.17 追記
実は、”A1:C300″ のように検索範囲を指定してやるのってあまり意味はないんだそうです。
“A:C” のようにざっくり列で指定するのは遅くなるって、ずっと思ってました。
どちらも速度は変わらないんだそうです。

可変にしたいからと数式で範囲指定していたら、それが理由で遅くなってしまう可能性も大です。
ていうか、OFFSET使ってるんで再計算されまくりですよねorz

知識として記事は残しておきますが、今後は素直に列で指定して参照するか、構造化参照を使うことにします。
=====

スポンサーリンク
Simplicityレクタングル(大)

参照範囲を数式で指定する方法

Excelの数式タブから名前の定義を開きます。
名前の定義-新しい名前

参照範囲にOFFSET関数を使った数式を入力します。
名前の定義-参照範囲の入力

  • 名前:(任意の名前)
  • 参照範囲:=OFFSET(Sheet3!$A$2,0,0,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$1:$1))

シート名は「ブック」になっていると思います。特に変更することはありません。

OFFSET関数とは

OFFSET関数は基準となるセルから行と列とを指定した数だけ移動した位置にあるセルを取得します。

OFFSET関数の書式:OFFSET(基準,行数,列数,[高さ],[幅])

引数については以下のとおりです。

  • 基準:必ず指定する必要があります。基準となるセル範囲の参照を指定します。
  • 行数:必ず指定する必要があります。基準の左上隅のセルを上方向または方向へシフトする距離を行数単位で指定します。行数に正の数を指定すると下方向へシフトし、負の数を指定すると上方向へシフトします。列数 必ず指定する必要があります。基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。列数に正の数を指定すると右方向へシフトし、負の数を指定すると左方向へシフトします。
  • 高さ:省略可能です。オフセット参照の行数を指定します。高さは正の数である必要があります。
  • 幅:省略可能です。オフセット参照の列数を指定します。幅は正の数である必要があります。

ちょっと解説~参照範囲を数式で指定する方法~

では、ここで今回私が設定した「Reference_range」という名前の参照範囲について解説です。

参照範囲の始まりとなる「基準」は”Sheet3のAの2″です。1行目には項目名が入っていて、データは2行目から入っています。
ダミーデータ表
(ぼかしてはいますが、ダミーデータです)

数式

データの参照範囲を四角くとらえた時の一番左上の角は基準セルと重なっていますので行数は0です。

列数についても参照範囲を四角くとらえた時の一番左上角になる場所は基準セルと同じなので列数も0になります。

高さと幅は省略可能とありますが、今回は範囲を指定したいので省略せずに入力します。

高さはCOUNTA(Sheet3!$A:$A)
これはA列にあるデータの数を数えて高さとしています
A列には全部データが入ってるという前提ですので空白の行がない列を選択しなくてはいけません
必ずデータが入っている列から高さを測定します。

幅はCOUNTA(Sheet3!$1:$1)
これは1行目にあるデータの数を数えて幅としています。
1行目は項目欄になっているので必ずデータはあるはずですので大丈夫ですね。
以後項目が追加になった時にも必ず項目名は入れましょう。

スポンサーリンク
Simplicityレクタングル(大)
Simplicityレクタングル(大)

フォローする