VLOOKUPで他のシートを参照する場合

質問

数式に間違いがあったので、前回の質問をいったん締め切って、再度投稿いたします。

シートが(1)「値表示」と(2)「データ」の２種類があります。
(2)にはB1:D4、B5:D8、B9:D12というように、セル１２個分にそれぞれ独立したデータ表があり、このデータが全部で１０個あります。
そして、このデータから任意の値を(1)「値表示」シートに表示したいので、(1)のセルA1に「＝VLOOKUP（B1,データ!,B1:D4,2,FALSE）という式を入れます。
これで、(1)のA1(2)のシートのデータ表から任意の値が表示されるのですが、このA1に入力した式をコピーして、A10までそれぞれの表に対応したVLOOKUPの数式を入れたいのですが、普通にコピーするとA2以降に入力される式はデータの参照範囲がA1の範囲から１セル分だけずれたB2：D5となっています。A2が参照する範囲は本来はB5:D8にしたいので、一つずつデータの参照範囲を修正しないとならないのですが、このような場合は、やはり一つずつ修正するしか方法がないのでしょうか。
もっと、素早く参照先を変更できる方法がありましたら教えてください。

回答No.3

kagakusuki · ベストアンサー

回答No.2様の関数は、その関数を1行目から入力して行く場合に対応する関数ですが、もし、関数を最初に入力するセルが(1)「値表示」シートのA1セルではなく、検索範囲がB1:D4、B5:D8、B9:D12となっているVLOOKUP関数をA2セルから入力して行くという場合には、次の様な関数をA2セルに入力してから、A2セルをコピーして、A3以下に貼り付けて下さい。

=VLOOKUP($B2,OFFSET(データ!$B$1:$D$4,(ROWS(A$2:A2)-1)*4,0),2,FALSE)

　或いは

=VLOOKUP($B2,OFFSET(データ!$B$1:$D$4,(ROW()-ROW(A$2))*4,0),2,FALSE)

　上記はA2セルのVLOOKUP関数の検索範囲がB1:D4となる様にする場合の例ですが、これと同じ考え方で、A1セルのVLOOKUP関数の検索範囲がB1:D4となる様にする場合には、A1セルに入力する関数を次の様にして下さい。

=VLOOKUP($B1,OFFSET(データ!$B$1:$D$4,(ROWS(A$2:A2)-1)*4,0),2,FALSE)

　或いは

=VLOOKUP($B1,OFFSET(データ!$B$1:$D$4,(ROW()-ROW(A$2))*4,0),2,FALSE)

補足

ご回答ありがとうございます。
詳細に様々な方法を教えていただきありがとうございました。ROW関数は初めてな上に、回答No２のchie65535さんの数式よりちょっと複雑なので、理解するのに時間がかかりました。
ちなみに、VLOOKUP関数をA2セルから入力する場合に、回答No２のchie65535さんから教えていただいた関数である

=VLOOKUP(B1,OFFSET(データ!$B$1,(ROW()-1)*4,0,4,3),2,FALSE)

上記の式で、A2から入力する場合はROW()から引く数を「-1」から「-2」に変更、またA3から入力する場合は「-3」にするだけでも同じ結果になるようですが、この単純な変更だと何か不都合が起きるケースがありますか。

回答No.6

kagakusuki

回答No.3です。

>=VLOOKUP(B1,OFFSET(データ!$B$1,(ROW()-1)*4,0,4,3),2,FALSE)

>上記の式で、A2から入力する場合はROW()から引く数を「-1」から「-2」に変更、またA3から入力する場合は「-3」にするだけでも同じ結果になるようですが、この単純な変更だと何か不都合が起きるケースがありますか。

　例えば「値表示」シートのレイアウトが変更になって、「値表示」シートのA2セルに入力されていた「B1:D4を検索範囲とするVLOOKUP関数」を移動させて、「B1:D4を検索範囲とした場合の結果」をA列の別の行のセルに表示させる様にする必要が生じた際に、

=VLOOKUP($B2,OFFSET(データ!$B$1:$D$4,(ROWS(A$2:A2)-1)*4,0),2,FALSE)

や

=VLOOKUP($B2,OFFSET(データ!$B$1:$D$4,(ROW()-ROW(A$2))*4,0),2,FALSE)

を使っていた場合には、A2:B2の上に一行を挿入するだけで、「B1:D4を検索範囲とした場合の結果」がA3セルに表示される様になりますし、A2:B2よりも上の行を一行削除するだけで、「B1:D4を検索範囲とした場合の結果」がA1セルに表示される様になります。
　これに対し、「値表示」シートのA2セルに

=VLOOKUP(B2,OFFSET(データ!$B$2,(ROW()-2)*4,0,4,3),2,FALSE)

と入力されていた場合には、一々、ROW()-2の後半部の「-2」の値を移動先の行番号に合わせて変更せねばなりません。

　そして、もっと重要なのは、「-2」がセル参照ではないために「何故、『-2』になっているのか」という事が解かり難いという点です。
　例えば、この御質問で得られた回答の関数を質問者様がお仕事で使われた場合、後日になって修正が必要になった際に、質問者様が「何故、『-2』になっているのか」という事を忘れてしまわれたり、質問者様の仕事を引き継いだ方が関数を修正しようとされた際に、「-2」の部分を修正せねばならない事が一見しただけでは解り難く、その部分を修正しないまま単純に「値表示」シートのレイアウトだけを変更してしまう事により、表示結果がおかしくなってしまう恐れが若干あるかと思います。

お礼

夜遅い時間に、ご丁寧な解説をしていただきありがとうございました。
大変分かりやすかったです。
数式は少し複雑になりますが、後々の事を考えると必要な事ですね。
ありがとうございました。

回答No.5

msMike

いっそのこと、
=VLOOKUP(B1,データ!B$1:D$40,2,FALSE)
では如何？
それでは駄目な理由を教えてください。

補足

回答No２のchie65535さんの数式で解決しました。ありがとうございました。
ちなみに、参照先がデータ!B$1:D$40ではダメな理由は、B列のセルに入力されているデータがB1:D4、B5:D8、B9:D12もすべて同じ内容なので、範囲を明確に指定しないと正確な数値が表示できません。

回答No.4

bunjii

参照範囲をOFFSET関数で指定してください。
A1=VLOOKUP(B1,OFFSET(データ!$A$1,(ROW()-1)*4,0,4,2),2,FALSE)
OFFSET関数は参照位置のセルから離れたセル数を左上として高さと幅の数値で複数セルの範囲を指定できます。
提示のデータ範囲では添付画像のようになります。

お礼

画像を付けていただき非常にわかりやすかったです。ありがとうございました。
これで、一つ一つ参照先を修正していた手間から解放されます。

回答No.2

chie65536（@chie65535）

A1セルに
=VLOOKUP(B1,OFFSET(データ!$B$1,(ROW()-1)*4,0,4,3),2,FALSE)
と入力して、下方向にコピーして下さい。

「(ROW()-1)*4」と言う計算式は「１行目にあれば０、２行目にあれば４、３行目にあれば８…」という計算をします。

「OFFSET(データ!$B$1,0,0,4,3)」と言う計算式は「データ!$B$1から０行、０列移動した場所の、４行×３列の12個のセル」を意味します。つまり「データ!B1:D4」です。

「OFFSET(データ!$B$1,4,0,4,3)」と言う計算式は「データ!$B$1から４行、０列移動した場所の、４行×３列の12個のセル」を意味します。つまり「データ!B5:D8」です。

「OFFSET(データ!$B$1,8,0,4,3)」と言う計算式は「データ!$B$1から８行、０列移動した場所の、４行×３列の12個のセル」を意味します。つまり「データ!B9:D12」です。

従って、この式は「１行目に書いたらデータ!B1:D4を参照し、２行目に書いたらデータ!B5:D8を参照し、３行目に書いたらデータ!B9:D12を参照し…」となります。

なので
=VLOOKUP(B1,OFFSET(データ!$B$1,(ROW()-1)*4,0,4,3),2,FALSE)
は、１行目に書いたら
=VLOOKUP(B1,データ!B1:D4,2,FALSE)
と同じ意味、２行目に書いたら
=VLOOKUP(B2,データ!B5:D8,2,FALSE)
と同じ意味、３行目に書いたら
=VLOOKUP(B3,データ!B9:D12,2,FALSE)
と同じ意味（以下繰り返し）になります。

お礼

大変わかりやすく教えていただきありがとうございました。
これで、地道にセルの指定範囲を一つずつ修正していた手間から解放されます。
ROW関数という便利な関数も新たに覚えることができました。ありがとうございました。

回答No.1

aokii

＝VLOOKUP（B1,データ!,B$1:D$4,2,FALSE）