Excel2013,2016…伝票形式の表内のデータを参照するオートフィル可能な関数埋め込み

別表のデータを参照するというと、vlookup関数、hlookup関数、index関数などがありますが、一覧表形式でなければデータを参照することが出来ません。伝票形式の表内のデータを参照する場合は、offset関数とrow関数を組み合わせることで、オートフィル可能な関数として埋め込むことが出来ます。

このような表1と表2があります。

表1に表2内の各項目を参照してデータをセットします。
項目1(B3)=OFFSET($H$2,(ROW(B3)-3)*3,2)
項目2(C3)=OFFSET($H$2,(ROW(C3)-3)*3,4)
項目3(D3)=OFFSET($H$2,(ROW(D3)-3)*3+1,3)
項目4(E3)=OFFSET($H$2,(ROW(E3)-3)*3+1,5)
項目5(F3)=OFFSET($H$2,(ROW(F3)-3)*3+2,5)

1. 表1のセルの行番号をROW関数で取得して初期値を0とするため、-3（行番号）します。
2. 表2の先頭の番号が書かれたセルを起点とするため、絶対参照にします。
3. 表2の起点から、CELL関数で各項目の内容を取得します。その際、行の引数に1で求めた値×3（行間隔）とします。

※これで、オートフィル可能な関数を埋め込むことが出来ました。