Kato Ryo Official Site

抽象度とエネルギーを上げる

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

2017/01/22
 
この記事を書いている人 - WRITER -
Pocket

例えば以下のシートの例のように、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ファイルはコチラ

 

この記事を書いている人 - WRITER -

- Comments -

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

Copyright© 抽象度とエネルギーを上げる , 2017 All Rights Reserved.