エクセル関数でちょっと悩んでます。

質問

エクセル関数でちょっと悩んでます。
sheet1にセルB4、C4、D4、B5、C5、D5(グループ1)とI4、J4、K4、I5、J5、K5(グループ2)にデータが入っているとします。

sheet2のB3、C3、D3、E3、F3、G3にグループ1のデータを記入します。
B4、C4、D4、E4、F4、G4にはグループ2を入れたいのですが、sheet1からみて列7つ移動したものとして記入したいです。どうやればいいですか？
例えば、sheet2のB3には、=Sheet1!B4を入れれば反映されます。
sheet2のB4にはsheet1のI4を入れたいのですが、sheet1からみて列7つ移動した形でエクセル関数を使って表現したいのです。どうやればいいですか？
=Sheet1!B4＋？

回答No.5

bunjii · ベストアンサー

回答No.3の追加です。
INDEX関数の時は次の数式で右と下へコピーできます。
Sheet2!B3=INDEX(Sheet1!$B$4:$O$5,INT((COLUMN()-2)/3)+1,MOD((COLUMN()-2),3)+1+(ROW()-3)*7)
元のデータは1つのグループが2行×7列（B4:H5）として、提示された2行×3列が内包しているものと考えれば良いでしょう。
OFFSET関数の場合はSheet1!$A$1からの隔たりを上記数式を参考に行位置と列位置を算出してください。（あなたの応用力で解決することをお勧めします）

回答No.4

imogasi

面倒な式になります。趣味以外はやらない方がよい。
Sheet1　B4:D5
1 2 3
4 5 6
Sheet1　I4:K5
7 8 9
10 11 12
ーーー
一部に、思考過程の説明も入ってます。
Sheet2
Sheet1の　A1からの
オフセット（行、列） 3,1 3,2 3,3 4,1 4,2 4,3 ＜－－参考までに書き上げ
行 3 3 3 4 4 4
列 1 2 3 1 2 3
関数式（セルの値利用） 1 2 3 4 5 6
全部関数式で 1 2 3 4 5 6

全部関数式で 7 8 9 10 11 12
I4-K５を横並べ
関数式は
B3 =INT((COLUMN()-2)/3)+3
B4 =MOD(COLUMN()-2,3)+1
B5 =OFFSET(Sheet1!$A$1,B3,B4)
B6 =OFFSET(Sheet1!$A$1,INT((COLUMN()-2)/3)+3,MOD(COLUMN()-2,3)+1)
B8 =OFFSET(Sheet1!$A$1,INT((COLUMN()-2)/3)+3,MOD(COLUMN()-2,3)+1+7)
そして右方向に式を複写する。

エクセル関数による、表の組換えは、かくの如く複雑な式になる。
ーー
本件は、如何にVBA向きの問題かと思う。
VBAでは、標準モジュールに
Sub test01()
c = 2　 'B列から右列に出す
For Each cl In Worksheets("Sheet1").Range(" b4:D5") 　　’第１ブロックの例
Worksheets("Sheet3").Cells(3, c) = cl
c = c + 1
Next
End Sub
実行するとSheet1からSheet３のB3:G3に数字が並ぶ。
第２ブロックの　Ｉ4：K5も上記の数か所変えるだけ。

回答No.3

bunjii

考え方の問題ですが、「Sheet1のB4:d5の値をSheet2のB3:G3へ代入するための数式をSheet2!B3セルに作成して、オートフィルでG3セルまでコピーしたい」ということでしょうか？
Sheet1の元データは3列×2行であり、代入先のSheet2は6列×1行なので少々面倒です。
INDEX関数またはOFFSET関数で行位置と列位置を指定すれば可能です。
しかし、グループ1とグループ2の位置関係を同時に1つの数式で対応させるにはトライ＆エラーの繰り返しが多くなり簡単にはできないでしょう。
グループ1とグループ2を別々に考えれば次のようになります。
グループ1の場合
Sheet2!B3=INDEX(Sheet1!$B$4:$D$5,INT((COLUMN()-2)/3)+1,MOD((COLUMN()-2),3)+1)
　　　　　=OFFSET(Sheet1!$A$1,ROW()+INT((COLUMN()-2)/3),MOD(COLUMN()-2,3)+1)
グループ2の場合
Sheet2!B4=INDEX(Sheet1!$I$4:$K$5,INT((COLUMN()-2)/3)+1,MOD((COLUMN()-2),3)+1)
　　　　　=OFFSET(Sheet1!$H$1,ROW()-1+INT((COLUMN()-2)/3),MOD(COLUMN()-2,3)+1)

回答No.2

aokii

INDIRECT関数でできるかもしれませんが、できれば、面倒な式は止めて、式を入れている最中に、参照したいセルをクリックすることをお勧めします。

回答No.1

mshr1962

>sheet1からみて列7つ移動した形
=OFFSET(Sheet1!B4,,7)

OFFSET関数・・・基準セルから行移動(↓)、列移動分(→)ずらしたセルを左上端とした行高さ、列幅の範囲を取得します。
OFFSET(基準セル,行移動,列移動,行高さ,列幅)