エクセル 行を列に参照する NO.2

質問

お世話になります。

昨日
http://sp.okwave.jp/qa/q9264221.html
で質問させていただきましたもののアレンジです。

昨日の質問では、
Sheet1のA1からF500...の値を
Sheet2のA列に参照させたい。

Sheet1 Sheet2
A1 →A1
B1 →A2
C1 →A3
D1 →A4
E1 →A5
F1 →A6
A2 →A7
B2 →A8
C2 →A9
.
.
.
で、
Sheet2のA1に
=OFFSET(Sheet1!$A$1,(ROW()-1)/6,MOD(ROW()-1,6))
と入力して，下にコピー
という完璧な回答をいただきました。

これを、違う表でアレンジしたかったのです。
Sheet1のG2からN500...
の値を
Sheet2のE7から下に表示したかったのです。

あれこれ試して
=OFFSET(Sheet1!$G$2,(ROW()-7)/8,MOD(ROW()-7,8))
で、値の参照ができました。
この中の、
-7
の部分がわからないのです。

どなたか、わかりやすく教えていただけませんでしょうか？

お忙しいとは思いますが、よろしくお願いします。

回答No.1

kagakusuki · ベストアンサー

=OFFSET(Sheet1!$A$1,(ROW()-1)/6,MOD(ROW()-1,6))

の中の1はSheet2のA1というSheet2において転記し始めるセルの行番号で、同じく6はSheet1のA列～F列という、Sheet1において参照する列範囲内に含まれる列の列数です。

　同様に

=OFFSET(Sheet1!$G$2,(ROW()-7)/8,MOD(ROW()-7,8))

の中の7はSheet2のE7というSheet2において転記し始めるセルの行番号で、同じく8はSheet1のG2～N500という、Sheet1において参照するセル範囲内に含まれる列の列数です。

　ですから、

=OFFSET(Sheet1!$G$2,(ROW()-7)/8,MOD(ROW()-7,8))

を次の様に書き換えると解りやすく、今回の様に違う表でアレンジする際にも、一々、1にするのか7にするのかという事を考えなくとも済む様になります。

=OFFSET(Sheet1!$G$2,(ROW()-ROW(E$7))/COLUMNS($G:$N),MOD(ROW()-ROW(E$7),COLUMNS($G:$N)))

お礼

すごくわかりやすいです！
ありがとうございました。

回答No.2

bunjii

>どなたか、わかりやすく教えていただけませんでしょうか？
=OFFSET(Sheet1!$G$2,(ROW()-7)/8,MOD(ROW()-7,8))
上記の数式の「ROW()-7」の初期値はROW(E7)-7→7-7→0になります。
数式を設定したセルの番地によってROW()の戻り値が変化しますのでOFFSET関数の参照セルからの隔たりを求めるための変数として使うときに初期値を0にする目的で使ったと思います。
OFFSET関数の使い方が理解できていれば参照セルの番地から行方向と列方向の隔たりをどのように算出するかを考えて分かり易い数式を自分で考えることがレベルアップになります。
目的の値を求めるには幾つかの組み込み関数を使って1つの数式を組み立てれば他のセルへコピーすることで数式の入力を省略できます。
あなたの質問がその典型的な例になります。
OFFSET関数以外にINDEX関数でも同様な処理が可能です。
E7=INDEX(Sheet1!G:N,(ROW()-7)/8+2,MOD(ROW()-7,8)+1)