VLOOKUPの検索範囲を入力に応じて変えたい

質問

Excel2007を使用しています。
個人別で経費データシートを入力し、全員の当月分データを別シート１枚にまとめようとしています。
当月分データシートJ2番地に指定番号を入力すると、個人シートからデータを参照してくる形を取りました。

=IF($J2="","",VLOOKUP($J2,社員名!$A$5:$I$2004,3,0))

この数式内検索範囲を、指定番号に応じてその該当する社員名に自動で変えることは可能でしょうか？
・J2　山田氏用指定番号
　=IF($J2="","",VLOOKUP($J2,山田!$A$5:$I$2004,3,0))
・J2　佐藤氏用指定番号
　=IF($J2="","",VLOOKUP($J2,佐藤!$A$5:$I$2004,3,0))

指定番号に応じて検索範囲の個人シートも変わり、それぞれのデータを拾ってくるようにしたいです。

もしくはもっと別に良い方法がありましたら教えていただきたいです。
質問がわかりづらかったら申し訳ありません。
上記関数もネット検索を繰り返しながらといったレベルです。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.2

kybo · ベストアンサー

J2に個人固有の番号、
L2:L4に個人固有番号、M2:M10に固有番号に対応する個人名

L　　　M
1　　　山田
2　　　佐藤

の様に入力あるとして、

山田!$A$5:$I$2004

の部分を以下の様にしてみてください。

INDIRECT(VLOOKUP($J2,$L$2:$M$10,2,FALSE)&"!$A$5:$I$2004")

お礼

おかげさまでINDIRECTを使いシート参照できました。
ありがとうございました。

補足

質問としての情報が足りていない中でご回答いただきありがとうございました。
どうやらINDIRECT関数を使うということが皆さんの回答からわかり、いただいた数式から自分でもやってみているところです。
本日画像をアップしてみました。
もしお時間ありましたら、また教えていただけると幸いです。

回答No.6

bunjii

>本日画像をアップしてみました。
解像度が低いので拡大しても読めません。

>もしお時間ありましたら、また教えていただけると幸いです。
時間を割いて検証することはできますが、元になるデータ（実データに似せたダミーで良い）を提示して頂けないと目的に合ったものが見つかりません。
回答No.3ではJ2へ文字列として"山田氏用指定番号"や"佐藤氏用指定番号"のように入力されることを条件にしています。
質問で例示されたJ2セルの「山田氏用指定番号」や「佐藤氏用指定番号」と言うのは特定の番号（数値）でその値が"山田"や"佐藤"と言う文字列と何らかの関連があるのでしょうか？

お礼

おかげさまでINDIRECTを使いシート参照できました。
色々お手間取らせすみません。
ありがとうございました。

補足

画像解像度を維持したままサイズ縮小する方法がわからず、削除しました。
説明もスキルも足らず、ご迷惑をおかけしてすみません。

＞質問で例示されたJ2セルの「山田氏用指定番号」や「佐藤氏用指定番号」と言うのは特定の番号（数値）でその値が"山田"や"佐藤"と言う文字列と何らかの関連があるのでしょうか？

J2にいれるのは文字列でなくK60020140601といった数値です。この12桁を「山田氏用指定番号」と呼んでいます。すみません。

　例　K60020140601
　　　↑社員番号K600の人の、2014年6月度、1件めのデータという意味。一番右がデータ数に応じ1,2,3…増えます。

この12桁が個人シート（山田シート）A列に入っており、当月まとめシートのVLOOKUP式で該当データを拾ってきます。

当月まとめシートJ2番地以下に
　　J
2　K60020140601
3　K60020140602
4　K61020140601（←佐藤さんデータ）
と入力していきます。
ちなみに1行目は表項目ラベルです。

VLOOKUP式はF～I列に入っています。
例　I
2　=IF($J2="","",VLOOKUP($J2,山田!$A$5:$I$2000,3,0))
3　=IF($J3="","",VLOOKUP($J3,山田!$A$5:$I$2000,3,0))
4　=IF($J4="","",VLOOKUP($J4,山田!$A$5:$I$2000,3,0))

ここでI4はエラーが出ます。
J4にはK61020140601（K610＝佐藤さんデータ）が入力されており、I4の式では「山田シート」を指定しているためです。（山田シートA列にはK600…しか入っていないので）
これを、J列の「山田氏用指定番号」の入力で、別シートである「佐藤シート」に自動で変えることは可能でしょうか？

回答No.5

nishi6

何がしたいのかよく分かりませんでした。合わせて、今何ができてるのか分かりませんでした。

質問を整理すると、
１．個人別に入力する経費データシートがある。
２．全員の当月分データを別シート１枚にまとめたい。
３．当月分データシートJ2番地に指定番号を入力する。
４．個人シートからデータを参照してくる形を取った。
５．その算式は、=IF($J2="","",VLOOKUP($J2,社員名!$A$5:$I$2004,3,0))
６．J2番地に指定番号で社員名を自動的に変えたい。

それぞれへの（私の思った）不明点を書いてみます。

＞１．個人別に入力する経費データシートがある。
　・個人別とのことですが、対象者は何名くらいでしょうか。
　・入力する経費データは複数ですね。
　・入力データ数は個人別に違いますか。
　・次に当月分データという説明がありますが、入力データ（経費データシート）に年月を示す項目がありますか？
　・VLOOKUP関数の第一引数がJ2ですが、個人別の経費データシートのA列にはJ2と同じ値が入力されていると考えていいですか？個人別のシートなので全て同じ値ですか？
　・2000行の範囲がありますが、これは最大行数ですか？

＞２．全員の当月分データを別シート１枚にまとめたい。
　・個人単位の当月分データとは個人別の経費データシートの中の当月分の複数データですか？または当月分が合計されていますか？
　・まとめるとは個人別データを別シートに持ってくることですね。

＞３．当月分データシートJ2番地に指定番号を入力する。
　・「当月分データシート」とは個人データをまとめる別シートの別名ですか？
　・「J2番地」は例だと思いますが、全員分をまとめるためにはどのように増やしていきますか？VLOOKUP関数が表示できるデータは一つです。（セル一つ）

＞４．個人シートからデータを参照してくる形を取った。
　・次の算式で参照できるのは最大1件です。全員分のデータをどのように集めればいいですか。

＞５．その算式は、=IF($J2="","",VLOOKUP($J2,社員名!$A$5:$I$2004,3,0))
　・J2に入力があると「J2から何らかの方法」で導き出した「社員名シート」のA5:I2004からA列でJ2と一致した行のC列の値を持ってきます。
　・そこで疑問は、A5:I2004は各列、どのような内容なのだろうかということです。
　・VLOOKUP関数で参照される値を特定したいとすれば、A列にはJ2と同じ値が1つしかないことが条件です。残りの1999件には何が入っている？前に書いた「1.」で書いたように、A列には集めた時のためにもすべてに個人を特定する値が入っているべきでしょう。そうすると、このVLOOKUP関数はいつもシートの5行目のC列を持ってきてしまいます。
　・そもそも、個人別のシートのA列を個人名と同じ内容の値で検索していることがよく分かりません。
　・シート名で個人が特定できているはずなので、「当月」を検索する手立ての方が重要でしょう。

＞６．J2番地に指定番号で社員名を自動的に変えたい。
　・方法としては、J2の値と社員名の関係（表）とINDIRECT関数でしょう。

長々とすいません。質問を読む限り、VLOOKUP関数の使い方に誤解があるようにも思えます。

目的のデータを集めるということについて書いてみます。「マクロを使って」というのが一番手っ取り早いように思えますが、内容に不明点が多いこともあり、まず手作業を考えてみます。

Office2007なのでOfficeクリップボードを使います。
１．[ホーム] タブの [クリップボード]グループでクリップボードを開きます。右下隅の小さなマークです。すべてクリアをすれば、最大24回コピーできます。社員数を気にした理由です。

２．最初の社員のシートを開いて当月分を選択しコピー（CTRL+C）します。年月日順に並んでなかったら面倒なので全部コピーします。CTRL+SHIFT+*で全データ選択できます。表題もコピーして構いません。表題や当月分以外は後で削除します。

３．これを人数分（最大24名分）行います。

４．まとめのシートに行って、クリップボードのすべて貼り付けを実行します。

５．人数が残っていれば、１～４を繰り返します。

６．全員分を集め終わったら並べ替えを行い、複数の表題行があれば削除します。また当月分以外のデータを削除します。（当月分だけをフィルターで絞り込むこともできます）

マクロでの対応は、手作業で十分行った後、マクロ記録の手順を踏んで作っていくべきでしょう。まぁ、誰かに頼んでもいいでしょうが、自分で直せないと後が大変です。

頑張ってください。

お礼

おかげさまでINDIRECTを使いシート参照できました。
色々お手間取らせすみません。
ありがとうございました。

補足

お答えいただきありがとうございます。
おそらくご指摘通り、関数の使い方を誤解している面も多々あるかと思います。

本日画像をアップしてみました。
もしまたお答えいただければ幸いです。
以下いただいた疑問点の回答です。

＞１．個人別に入力する経費データシートがある。
　・対象者現在23名。
　・入力する経費データは複数。
　・入力データ数は個人別に違います。
　・入力データ（経費データシート）に年月を示す項目があります。
　・個人別の経費データシートのA列にはJ2と同じ値が入力されている。
　例　K60020140601
　　　↑個人番号K600の人の、2014年6月度、1件めのデータ。一番右がデータ数に応じ増えます。これがデータシートA列、当月まとめシートJ2に入ります。
　・2000行の範囲は、データ範囲を可変にする設定がわからなかったので、そこまでは無いだろう数値を入れました。

＞２．全員の当月分データを別シート１枚にまとめたい。
　・個人単位の当月分データとは、個人別の経費データシートの中の当月分の複数データです。
　・まとめるとは個人別データを別シートに持ってくることです。

＞３．当月分データシートJ2番地に指定番号を入力する。
　・「当月分データシート」とは個人データをまとめる別シートの別名です。
　・「J2番地」は例です。全員分をまとめるためには、
　　J
１　K60020140601
２　K60020140602
３　K61020140601
と入力していきます。

＞４．個人シートからデータを参照してくる形を取った。
　・次の算式で参照できるのは最大1件です。全員分のデータをどのように集めればいいですか。
　　３．４．同回答になるでしょうか。理解も説明も足らずすみません。

＞５．その算式は、=IF($J2="","",VLOOKUP($J2,社員名!$A$5:$I$2004,3,0))
　・J2に入力があると「J2から何らかの方法」で導き出した「社員名シート」のA5:I2004からA列でJ2と一致した行のC列の値を持ってきます。
　・そこで疑問は、A5:I2004は各列、どのような内容なのだろうかということです。

補足に画像アップ致しました。

マクロはごく単純なものしか作ったことがないのでやめておきます。

質問での情報が少なすぎて申し訳ありませんでした。

回答No.4

keithin

その行が山田さんなのか佐藤さんなのか，「どこのセルを見たら」どのように判るのですか？
そこが曖昧なので，皆さん困ってしまってます。

たとえばＡ列に「山田」や「佐藤」と記入してあって，それぞれ「山田シート」「佐藤シート」のように「シート名とＡ列の記載内容が完全に合わせてある」という具合に作り込んでやれば，
=IF(J2="","",VLOOKUP(J2,INDIRECT(A2&"!A5:I2004"),3,FALSE))
のように作成できます。

あるいは
＞指定番号に応じてその該当する社員名に自動で変える

いま指定番号をVLOOKUPしてくる数式が書かれていますが，Ｊ２の「指定番号を見れば，該当する社員が（どのシートから取ってくればいいのか）判る」のでしたら，そのキマリを数式の中に正しく組み込んでやれば良いという事になります。あるいは１クッション置いて，「指定番号を見て誰シートなのかＡ列に計算で出す」みたいにすれば，前述の数式のままでもできます。
具体的な内容がナイショのままなので具体的な数式は回答できませんが，必要なら具体的に状況を添えて別途新しいご相談として投稿し直してみて下さい。

補足

質問としての情報が足りていない中でご回答いただきありがとうございました。
どうやらINDIRECT関数を使うということが皆さんの回答からわかり、いただいた数式から自分でもやってみているところです。
本日画像をアップしてみました。
もしお時間ありましたら、また教えていただけると幸いです。

回答No.3

bunjii

・J2　山田氏用指定番号
　=IF($J2="","",VLOOKUP($J2,山田!$A$5:$I$2004,3,0))
上記の数式でVLOOKUP関数の第2引数をINDIRECT関数に置き換えれば良いでしょう。
山田!$A$5:$I$2004
　　　　　↓
INDIRECT(LEFT($J2,FIND("氏",$J2)-1)&"!$A$5:$I$2004")

変更後の数式は次のようになります。
=IF($J2="","",VLOOKUP($J2,INDIRECT(LEFT($J2,FIND("氏",$J2)-1)&"!$A$5:$I$2004"),3,0))

お礼

おかげさまでINDIRECTを使いシート参照できました。
色々お手間取らせすみません。
２度もご説明いただき、ありがとうございました。

補足

質問としての情報が足りていない中でご回答いただきありがとうございました。
どうやらINDIRECT関数を使うということが皆さんの回答からわかり、いただいた数式から自分でもやってみているところです。
本日画像をアップしてみました。
もしお時間ありましたら、また教えていただけると幸いです。

回答No.1

shintaro-2

こちらが参考になるのでは？
http://pc.nikkeibp.co.jp/pc21/tech/excel36/27/

お礼

質問として情報量が足りないものに、迅速にお答えいただきありがとうございました。
どうやらINDIRECTを使うことは分かりました。
サイトを参考に試行錯誤中です。
ありがとうございます。