こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

このうち、BP列が1のもの約900件のみがSheet2に抽出されています。
(このためのマクロについて前回質問させていただきました)
Sheet2:
  A  B  C   D   E  F  G  …
1 No. ID 品目 月日            
2 2  a87  B  5/11             
3 7  b3  K  2/5
4 18 c44  F  12/1
5 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) とか書いてみましたが、もちろんこれでよいわけもなく…

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

投稿日時 - 2013-07-11 15:52:49

QNo.8171931

困ってます

質問者が選んだベストアンサー

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



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

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


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

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

投稿日時 - 2013-07-11 16:14:19

お礼

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

投稿日時 - 2013-07-12 14:32:39

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.7

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

投稿日時 - 2013-07-11 17:05:35

ANo.6

こんにちは!
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

投稿日時 - 2013-07-11 16:32:04

お礼

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

投稿日時 - 2013-07-12 14:37:50

ANo.5

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

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

投稿日時 - 2013-07-11 16:23:00

お礼

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

投稿日時 - 2013-07-12 14:42:35

ANo.4

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

投稿日時 - 2013-07-11 16:22:42

ANo.3

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

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


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

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

投稿日時 - 2013-07-11 16:20:20

お礼

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

投稿日時 - 2013-07-12 14:42:05

ANo.2

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

=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関数を使ってもできそうです。

投稿日時 - 2013-07-11 16:16:33

お礼

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

投稿日時 - 2013-07-12 14:40:29

あなたにオススメの質問