EXCEL関数／別シートのセル番地をセルの値で指定

質問

お世話になります。
どうしてもわかりません…
エクセル名人の皆様、どうかお知恵をお貸しくださいませ。

Sheet1に下記のようなデータが約5000件あります。
　　A　　B　　C　　　D　…　BP　　…
１　No.　ID.　品目　月日…状況　…
２　１　a33　　A　　3/4　　　０　　　
３　２　a87　　B　　5/11　　１　　

このうち、BP列が１のもの約900件のみがSheet2に抽出されています。
（このためのマクロについて前回質問させていただきました）
Sheet2：
　　A　　B　　C　　　D　　　E　　F　　G　　…
１　No.　ID　品目　月日　　　　　　　　　　　　
２　２　 a87 　B　　5/11　　　　　　　　　　　　　
３　７　 b3 　K　　2/5
４　18　c44　 F　　12/1
５　11　r93 J 7/28

ちなみにA列のNo.は1,2,3,…と続く通し番号で、だぶりはありません。

Sheet1のデータの項目数は大変多いのですが、Sheet2にはその項目すべては必要でないため、
Sheet2には上記のとおりAからD列までの必須情報のみ抽出し、
E列以降の情報については、必要な列の情報のみ、
A列の通しNo.で紐づけしてVLOOKUP関数で取得したいと考えています。

しかし、Sheet1の表には空欄があり、これを空欄のまま表示させるために
IF関数を使いたいのですが、
Sheet1の参照元のセルの番地をどう書けばいいでしょうか？
とてもわかりにくくて申し訳ありません…

たとえば、Sheet2のA列が18であれば(上記Sheet2のA4)、
元データはSheet1の19行目にあることになります。　
A列の数値+1、が行番号ということです。

そのK列を参照する場合、
=IF(Sheet1!K19="","",VLOOKUP（A4,Sheet1!A:K,COLUMN(K4),FALSE）
となるかと思うのですが、
このSheet1!K19　というセル番地を、自分で計算して手入力するというのはとても大変なので、
関数などで表現する方法はないでしょうか。
たとえば、Sheet1!K(A4+1)　とか書いてみましたが、もちろんこれでよいわけもなく…

不勉強で恐縮ですが、どうしてもわかりません。
どうかご教授ください。
よろしくおねがいいたします。

回答No.1

keithin · ベストアンサー

基本のご質問：
シート１のＫ列の１８＋１＝１９行目は
=INDEX(Sheet1!K:K,A4+1)
で直接参照します。わざわざVLOOKUPする必要「も」ありません。

次のご質問：
＞これを空欄のまま表示させるために

Ｋ列の内容が「文字列」だった場合は
=INDEX(Sheet1!K:K,A4+1)&""
としておきます。

Ｋ列の内容が「数値」で、これを「数値として」計算結果を出しとかなきゃならない場合は
=IF(INDEX(Sheet1!K:K,A4+1)="","",INDEX(Sheet1!K:K,A4+1))
としておきます。

Ｋ列の内容が数値でも、結果は「文字列として表示するだけでＯＫ」な場合は、先の&""の方式を利用できます。

お礼

前回に引き続きご回答いただきありがとうございます！
しかも、とてもスマートな関数に変身させてくださって、
大変勉強になりました。
心からの感謝をこめて、重ね重ね御礼申し上げます。

回答No.7

tom04

No.6です！
たびたびごめんなさい。

投稿後に思ったのですが・・・
Sheet1の1行目は項目が入っているのですよね？

そうであればSheet2の1行目は列番号でなく
表示したい「項目名」を入力した方が良いと思います。

その場合の数式は
E2セルを
=IF(OR(E$1="",$A2=""),"",INDEX(OFFSET(Sheet1!$A:$A,,MATCH(E$1,Sheet1!$1:$1,0)-1,,1),MATCH($A2,Sheet1!$A:$A,0)))

として、列・行方向にオートフィルでコピーしてみてください。
（前回の数式は列方向のオートフィルは考慮していませんでした）

※　空白の「0」の表示の件についてはNo.1さんが回答してくださっているので
その方法を利用させてもらう手もあると思います。

何度も失礼しました。m（_ _）m

回答No.6

tom04

こんにちは！
Sheet2のE1セルは単に表示したい列番号を入力するだけとします。
今回の質問の場合は　K　と入力

E2セルに
=IF(OR(A2="",E$1=""),"",INDEX(INDIRECT("Sheet1!"&E$1&":"&E$1),MATCH(A2,Sheet1!A:A,0)))

という数式を入れオートフィルで下へコピー！

※　Sheet1の表示したいセルが空白の場合は「0」が表示されてしまいますので、
Excelのオプション　→　詳細設定　→　「次のシートで作業するときの・・・」　→　「ゼロ値」のチェックを外しておきます。

尚、Sheet2で他の項目でどうしても「0」を表示しなければならない場合は
IF関数で上記数式が空白の場合の条件を追加してやります。m（_ _）m

お礼

前回に引き続き、貴重なお時間を割いてご回答くださったこと、
心から感謝いたします。また、わざわざオートフィルのことまで
ご考慮いただき追加のご回答をいただきましたことも、
重ねて御礼申し上げます。
こちらで質問させていただくごとに自分の不勉強が身に沁みます。
またお世話になることがあるかもしれませんが、どうぞよろしくお願いいたします。

回答No.5

KURUMITO

次のような式にすることで問題は無いように思いますね。

=IF(VLOOKUP（A4,Sheet1!A:K,COLUMN(K4),FALSE)="","",VLOOKUP（A4,Sheet1!A:K,COLUMN(K4),FALSE))

お礼

貴重なお時間を割いてご回答いただきありがとうございます！
大変勉強になりました。

回答No.4

MSZ006

＃２です。すみません、誤記がありました。
INDIRECTのすぐ後の（　が抜けてました。追加してください。
すみません。

回答No.3

moon00

INDIRECT関数を使えば可能な気がします。

Sheet1!K19と書くべきところを、以下のようにします。

INDIRECT("R"&A4+1&"C11",FALSE)

"R"&A4+1&"C11"で、A4セルの値を使い、R1C1形式でK19を表します。

お礼

貴重なお時間を割いてご回答いただきありがとうございます！
大変勉強になりました。

回答No.2

MSZ006

他にも方法はあると思いますが、例えば、

=IF(INDIRECT"Sheet1!K"&(A4+1))="","",VLOOKUP（A4,Sheet1!A:K,COLUMN(K4),FALSE）

でどうでしょうか。もしくは、

＞たとえば、Sheet2のA列が18であれば(上記Sheet2のA4)、
＞元データはSheet1の19行目にあることになります。　
＞A列の数値+1、が行番号ということです。

なのですから、VLOOKUPで廻さなくても

=IF(INDIRECT"Sheet1!K"&(A4+1))="","",INDIRECT"Sheet1!K"&(A4+1))）
とか。
この他にもOFFSET関数を使ってもできそうです。

お礼

ご親切にご回答いただきありがとうございました！
大変勉強になりました。
心から感謝申し上げます。