excel 別シートから複数項目を取得

質問

こんばんは。エクセルについて質問があります。

今、エクセルで同じ構造で中身の違うデータベースの様なシートを数枚作り、
そこから参照して、別の入力シートにそのデータを引っ張って来るということをしたいです。

調べてみた感じVLOOKUP関数などを使うのかなと思うのですが、
どうもうまいこといきません。

DBのような扱いをしているデータシートの構造は

【データシート】
A1(クラス名)列｜ B１(番号) 列 ｜ C1(名前：漢字) 列 ｜ D１(名前：かな) 列 ｜
-------------------------------------------------------------------
A2 　　１－１　｜　 B2 １番 | C2 鈴木　太郎 　 | D2 すずき　たろう 　 |

のようなデータの入れ方をしており、クラスは１－７まである感じで、
B１の列はA1列のクラスの人数分が連番になっています。
なので、番号は重複してる部分が多いです。
これが複数枚シートあるといった具合です。

データを反映させたいシートも前４列は同じ様な構造をしています。
これにあと複数列足した感じになってます。

【反映させたいシート】
A1(クラス名)列｜ B１(番号) 列 ｜ C1(名前：漢字) 列 ｜ D１(名前：かな) 列 ｜ E1 別項目　|…
--------------------------------------------------------------------------------
A2 　3-7 　｜　 B2 2 | C2 田中　次郎 　 | D2 たなか　じろう 　 | ×× |…

ここで名前列に名前(漢字、もしくはかな)を入れるか、
もしくはクラス名＋番号ですぐさま該当シートの特定し、
合致するE列以前の残りのデータを全て取得してくる。

反映させたシートのE列などに入れたデータを
今度はデータシートへとリンクさせるという風な
プログラムを組みたいです。

こういった具合のエクセルシートを作成したいのですが、
どのようにしたら良いでしょうか？
プログラムの様にIF文などを使用する感じになるのでしょうか？
それともデータのシートに別でIDなどユニークな値を各々の
データに割り振らないとダメでしょうか？

また参考として知りたいのですが、
１行に引っ張りたくないデータが多数あっても行単位で３列の分だけ取得して、
１行にある３列分のデータを同じ１行３列に反映させることは可能でしょうか？

説明が下手で意味が解り辛い所もあるかもしれませんが、
回答よろしくお願いします。

回答No.4

mu2011 · ベストアンサー

>エクセルで同じ構造で中身の違うデータベースの様なシートを数枚作り、
　⇒シート分散するとデータ処理が複雑になりますので中身が同じならば同一シートをお勧めします。
　　エクセル本来の機能（オートフィルタやフィルタオプションの設定等）を活用し易くなります。

>プログラムの様にIF文などを使用する感じになるのでしょうか？
>それともデータのシートに別でIDなどユニークな値を各々のデータに割り振らないとダメでしょうか？
　⇒プログラムはなるべく介在させないようにデータ主体に構築した方がよく、１データにはユニークな情報（例えば、クラス＋番号）も持たせた方がプログラム（ワークシート関数やマクロ）が必要になった場合でもシンプルになる、３行以上に跨るような数式は改定等の保守性上、お勧めできません

>１行に引っ張りたくないデータが多数あっても行単位で３列の分だけ取得して、
>１行にある３列分のデータを同じ１行３列に反映させることは可能でしょうか？
　⇒可能です。

一度、添付のＵＲＬを覗いてみてください。

参考URL：

http://www.eurus.dti.ne.jp/~yoneyama/

お礼

回答ありがとうございます。

やはり何かしらユニークな値を持っている方がいいんですね。

今の所、No.1さんの回答に書いた通り、IDを振って
簡素にやりたいことを一応は実現させることができました。

でも簡素にするためにデータベースシートも構造や内容も書き換えて
余りよくありませんし…もうちょっと改良の余地があるかなーといった具合です。

すごい参考になりました。ありがとうございました。

回答No.3

imogasi

質問を文章だけで書こうとするから、読みにくい。
エクセル経験が少ないと、こうなるのだろうが、余分な記述も多く、経験を積めば聞きたいところだけ絞って質問できる。
ーー
こう書いたら
データを採ってくるシート（＝関数をいれるシート）Sheet1　ーー＞反映させたいシートといっているシート
条件はA1セルにセットするものとする。
参照して持って来たいシート　Sheet2
列構成は
A-F列
クラス名　番号　名前漢字　名前かな) 英語点数」　国語点数
A　　　　　　12 田中　　　　　たなか　　30 50
・・・・
と書けば判るのだ。こんなに簡単になるのだ。
ーー
エクセル関数での検索は、２列を条件にするのは難しいのだ。
だから両者を結合したセルの列を作っておく
=A2&B2 クラス名と番号を結合した文字列を作る（ワーク列）
例データ
Sheet2　A-G列
クラス名 番号 名前漢字 名前かな) 英語点数」 国語点数 ワーク列
クラス2 5 大田 おおた 50 40 クラス2 5
クラス1 12 田中 たなか 30 50 クラス1 12
Sheet1　A1:B1
クラス1 12
式　A2に　=INDEX(Sheet2!$A$1:$F$100,MATCH($A$1&" "&$B$1,Sheet2!$G$1:$G100,0),COLUMN())
右方向に式を引っ張る。
結果
クラス1 12
クラス1 12 田中 たなか 30 50
ーーーーー
質問と少し違うかもしれないが、応用して。
ー
VLOOKUP関数がMATCH関数に手要るのはワーク列がデータ列より右に来ているため。A列に列挿入してワーク列をおくのは体裁が悪かろう。
ーー
＞また参考として知りたいのですが、
１行に引っ張りたくないデータが多数あっても行単位で３列の分だけ取得して、
１行にある３列分のデータを同じ１行３列に反映させることは可能でしょうか
ー
１質問にあれこれ詰め込まないで。
私の質問の書き方を参考にして、実例を挙げて、完結に別質問にしてください。

お礼

回答ありがとうございます。

なかなか説明が難しくて…。すいません。
簡素に書けたら良かったんですが。

他のやり方も考えていて、１つの質問に対して
複数質問してしまいました。

参考に勉強させていただきます。ありがとうございました。

回答No.2

KURUMITO

例えばシート1が反映させたいシートとします。
A1セルにはクラス名、B1セルには番号、C1セルには氏名（漢字）、D1セルには氏名（ひらがな）の文字があり、2行目か下方にはデータを入力または関数で表示するなどができるようにします。
氏名で入力してデータをデータシートから表示させるのは氏名などがダブっている場合もありますので適当ではありません。A列とB列にデータを入力してC列とD列には関数を使ってデータシートからデータを取り込んで表示させることにします。
A列に3-7、B列に2と入力してC列でD列にデータを表示させるとしたらそれぞれのデータシートの名前はクラス名と同じになっているとします。例えばシート名が3-7のシートがあるとします。各データシートはシート1と同じ項目名で列ができているとします。
そこでシート1のC2セルには次の式を入力してD2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A2="","",IF(INDEX(INDIRECT("'"&$A2&"'!C1:D100"),SUMPRODUCT((INDIRECT("'"&$A2&"'!A1:A100")=$A2)*(INDIRECT("'"&$A2&"'!B1:B100")=$B2)*ROW(INDIRECT("'"&$A2&"'!A1:A100"))),COLUMN(A1))=0,"該当なし",INDEX(INDIRECT("'"&$A2&"'!C1:D100"),SUMPRODUCT((INDIRECT("'"&$A2&"'!A1:A100")=$A2)*(INDIRECT("'"&$A2&"'!B1:B100")=$B2)*ROW(INDIRECT("'"&$A2&"'!A1:A100"))),COLUMN(A1))))

これで氏名がC列およびD列に表示されます。
シート1のE列より右側の列に、例えばL列までに項目が有ってそれらのデータを今度は元のデータシートに表示させるようにするためには各データシートのE2セルに次の式を入力してL2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(INDEX(Sheet1!$E:$L,SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=$B2)*ROW(Sheet1!$B$1:$B$100)),COLUMN(A1))=0,"",INDEX(Sheet1!$E:$L,SUMPRODUCT((Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=$B2)*ROW(Sheet1!$B$1:$B$100)),COLUMN(A1)))

お礼

回答ありがとうございます。

この数式参考にさせていただきます。
ものすごい式ですね。

凄過ぎて何がどう作用してどうなっているのかすら
自分には意味がわからない状態です（笑

やっぱりどうしても長くなるのは必然ですかね。
これをコピーしてちょっと使ってみます。

ほんとにありがとうございました。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

＞反映させたシートのE列などに入れたデータを
＞今度はデータシートへとリンクさせる
ACCESS向きの処理だと思います。
＞プログラムを組みたいです。
とりあえず、Excelでどの程度までプログラムを書かれたのでしょうか？

数式では処理できないので
シート数3で強引にハイパーリンク(^.^;
C2セルに
=IF(OR(A2="",B2=""),"",
HYPERLINK("#"&ADDRESS(MIN(IF(CHOOSE({1,2,3},Sheet1!$A$2:$A$45,Sheet2!$A$2:$A$45,Sheet3!$A$2:$A$45)&"_"&CHOOSE({1,2,3},Sheet1!$B$2:$B$45,Sheet2!$B$2:$B$45,Sheet3!$B$2:$B$45)=A2&"_"&B2,ROW($A$2:$A$45)-1)),5,,,CHOOSE(MIN(IF(CHOOSE({1,2,3},Sheet1!$A$2:$A$45,Sheet2!$A$2:$A$45,Sheet3!$A$2:$A$45)&"_"&CHOOSE({1,2,3},Sheet1!$B$2:$B$45,Sheet2!$B$2:$B$45,Sheet3!$B$2:$B$45)=A2&"_"&B2,{1,2,3})),
"Sheet1","Sheet2","Sheet3"))))

お礼

ご連絡遅くなりました。回答ありがとうございます。
ハイパーリンクですか。

今の所、A列にIDを振ることでユニークなものにして
VLOOKUPで取ってくる形にして一応はやりたいことを
簡易的ではありますが、実現してあります。

できればIDを無くしてやってみたいんですが…。
やっぱりもの凄く長くなるんですね…。