【Excel】n列おきの、飛び飛びになっているセルを参照する方法

例えば以下のシートの例のように、3列置きなど、飛び飛びに入力されているデータがある。これを飛び飛びではない、連続したセルで参照するには、どんな関数式を使えばよいだろうか？

（数値の2015、2016…は、例えば西暦と思ってもらえばよい。年度毎のデータが、例えば見た目の関係で、n列おきにデータが入力されているようなことは、実務ではしばしばあると思う。）

 

もちろん、一つ一つ「=B2」「=E2」…という風に式を入れていけばできるのは当たり前だが、これだと入力が面倒だ。一つにセルに式を入力して、それをコピーすることで参照できる方が良い。

やり方としては、OFFSET関数と列番号を返す関数である COLUMN を使えばよい。

 

OFFSET関数について

構文：OFFSET関数（基準となるセル, 行数, 列数, （高さ）, （幅））

OFFSET関数は、行数、列数で、基準となるセルからどれだけ移動するか指定し、そのセルの内容を返す。
「高さ」「幅」を指定すると、単一のセルではなく、セル範囲を示す。（ここでは詳述しない。）

【例】
OFFSET(A1,0,0) … A1セルの内容を返す
OFFSET(A1,1,1) … A1セルから下に1行、右に1列移動したセルということで、B2セルの内容を返す

 

以下、C9セルを例とする。ここに以下のように入れればよい。

=OFFSET($B2,0,((COLUMN()-COLUMN($B9))*3))

引数なしのCOLUMN()では、関数が入力されているセルの列番号が数値で返されるので、COLUMN()はこの場合、3 となる（C列＝3列目）。また、COLUMN($B9)ではB9セルの列番号が返されるので、COLUMN($B9)は 2 を返す。

ということで、(COLUMN() – COLUMN($B9))*3=3 となるので、実質的にC9セルには「=OFFSET($B2,0,3)」と入力したのと同じことになる。これはB2セルを基準として、0行下に移動し（=行移動はなし）、3列右に移動したセルの内容を参照しているので、数値としては、E2の値が返される。

なお、画像に既に答えが書いてあるし、実際に入力してもらえばわかるが、B9～F9には全て同じ式が入っているし、B10～F12のセルの内容も、B9やC9に入れた式をコピーすればよい。

OFFSET式の中で、$B2、$B9と、行：相対参照、列：絶対参照としているので、コピーをすれば一気に適切に式が展開されるのだ。（Excelの絶対参照、相対参照が良くわからないという方は、コチラなどがわかりやすい。）

今回は n列置きのデータを例としたが、もちろん n行置きのデータのコピーも同じように行えるので、そんな時はこのやり方を思い出していただけば幸いである。（参考のExcelファイルはコチラ）