[[20150627143039]]　『複数シートのデーター検索』（SUK）　ページの最後に飛ぶ

『複数シートのデーター検索』（SUK）

	Sheet1（検索シート）							
	A	B	C	D	E	F	G	H
1	sheet名	A列検索値	１行目検索値	結果		
2		↑						
3	 ↑	シート２～１０までの"A"列の検索値		
4	プルダウンでシート名選択				
5

	Sheet2							
	A	B	C	D	E	F	G	H
1		AB	BC	CD	DE	EF	FG	.
2	1111				130	140	150	
3	2222				230	240	250	
4	3333				290	300	310	
5	4444	100	110	120				
6 	3333	200	210	220				
7 	5555	260	270	280				
8 	2222	500	600	700				
9 	8888	900	910	920				
10 	9999	800	810	820				
11 	.							
12 	.							
	Sheet3							
	A	B	C	D	E	F	G	H
1		AB	BC	CD	DE	EF	FG	
2	1112				130	140	150	
3	2223				230	240	250	
4	3334				290	300	310	
5	4445	100	110	120				
6 	3334	200	210	220				
7 	5556	260	270	280				
8 	2223	500	600	700				
9 	8889	900	910	920				
10 	9990	800	810	820				
11

上記のシートがあります。Sheet2からデーターシートが１０枚あります。
Sheet１のＡ１セルにプルダウンでSheet名を選択、Ｂ１セルにＡ列の番号を入力、Ｃ３セルに１行目の記号を入力してＤ１セルに該当データーを表示させたいのですが、Ａ列は重複する番号が存在します。１行目の記号は重複しません。Sheet１のＤ１セルにINDEX、MATCH関数で検索しましたが、Ａ列の番号が重複しているとＡ列上部の番号を検索してしまいます。それとMatch関数はSheetの切り替えに対応していないようです。
勉強不足なので私の力量では、Sheet１のＤ１セルに”=INDEX(Sheet2!A1:G10,MATCH(B1,Sheet2!A1:A10,0),MATCH(C1,Sheet2!A1:G2,0))”程度しかできません。
ご教示宜しくお願いいたします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 例題を見た感じでは SUMIF でできそうですね。
 シートの切り替えは INDIRECT 関数で。

 D1 =IF(COUNTA(A1:C1)<3,"",SUMIF(INDIRECT("'"&A1&"'!A2:A100"),B1,INDEX(INDIRECT("'"&A1&"'!B2:G100"),0,MATCH(C1,INDIRECT("'"&A1&"'!B1:G1"),0))))

 こんな感じかな、ちょっと冗長だけど。
（笑） 2015/06/27(土) 20:24

笑様　早速コメント有難うございます。希望のものができそうです。これからも勉強させていただきます
本当にありがとうございました。
（ＳＵＫ） 2015/06/28(日) 09:15

再質問です。以前の表は大変便利に使用させていただいております。

今回は上記のデータファイルとは別ファイルで下記のようにA列にシート名、B列にデータシートa列の値、C列にデータシート１行目の値の一覧がありD列に検索結果を表示させたいのですが、
なかなか上手くいきません。ご教示宜しくお願いいたします。

      A        B        C        D        　　E　　        F    
1    Sheet2   1112      DE     検索結果表示
2    Sheet3   2223      EF     検索結果表示
3    Sheet1   5555      AB     検索結果表示
4    Sheet3   9990      AB     検索結果表示
5    Sheet4   9911      CD     検索結果表示
6    Sheet5

（SUK） 2015/07/14(火) 15:16

 誰かくるまでのつなぎですー

 D1  =INDEX(INDIRECT(A1&"!A1:G200"),MATCH(B1,INDIRECT(A1&"!A1:A200"),0),MATCH(C1,INDIRECT(A1&"!A1:G1"),0))

（さいき） 2015/07/14(火) 16:57

すみません私の説明が上手くできなかったみたいですみません。
検索結果を表示させるシートがあるのはデーターファイルとは別ファイルなので、
シート名の前にファイル名が入ると思います。
仮にデーターがあるファイル名を”データ.xlsx ”と
検索するシートがあるファイルを”検索.xlsx”として
もう一度教えて頂けませんでしょうか。よろしくお願いいたします。
（SUK） 2015/07/14(火) 17:41

 あ、書いてありましたね…すみません(_ _)
 んーと、INDIRECT関数は調べましたか？？
 その変更であれば、INDIRECT関数を調べて、何例か試してみるとすぐにわかると思います
 複雑な数式を作ろうとせず、別ファイルのA1に入力されているシート名のA1を表示させてみるとか…

 仮に、検索ファイルのSheet1のD1に、データファイルのSheet1のA1の値を表示させたいとすれば、
 D1  =[データ.xlsx]Sheet1!A1  となりますよね？
 　　　~~~~~~~~~~~ ~~~~~~
 　　　↑ファイル名　↑シート名

 INDIRECTを使うと
 D1  =INDIRECT("[データ.xlsx]Sheet1!A1")
 　　　　　　　　~~~~~~~~~~~ ~~~~~~
 　　　　　　　　↑ファイル名　↑シート名

 次に、検索ファイルのA1にシート名のSheet1が入力されているとして、D1に表示させたいのであれば、シート名のところにA1が入るので、
 D1  =INDIRECT("[データ.xlsx]"&A1&"!A1")
 　　　　　　　　~~~~~~~~~~~ 　~~
 　　　　　　　　↑ファイル名　↑シート名

 という風にシート名の前に必ずファイル名が入ります

 そして、文字は " " で囲む必要があり、文字の連結は & を使いますので、A1の前に"[データ.xlsx]"& を入力してやればいいことになります

 INDIRECT() の中には、単純に = で結んだ時に表示されるルールに従って入力すればいいのです

（さいき） 2015/07/15(水) 10:13

 その「データ.xlsx」のシート構成は冒頭のサンプルと同じと考えていいのかな？

 それと INDIRECT や SUMIF は、参照する別ファイル（データ.xlsx）が開かれてないと
 エラーになるんだけど、その点は大丈夫かな？
（笑） 2015/07/15(水) 12:45

さいき様、笑様いつもありがとうございます。
さいき様、詳しい説明ありがとうございます。自分なりに校内全文検索などで確認をしましたが、希望のものが自力ではできませんでした。さいき様の例をもとに勉強させていただきます。
笑様、データ.xlsxは最初のサンプルと同じものです。２つのファイルは同じＰＣで読み込みできますので問題ありません
これからお二人から教えていただいた事をもとに式を考えますが、行き詰まったら次回もよろしくお願いいたします
（SUK） 2015/07/15(水) 15:06

 冒頭の表と同じなら、既出回答の INDEX～MATCH ではできないってことは
 理解できてるんじゃなかったの？

 > ２つのファイルは同じＰＣで読み込みできますので問題ありません

 つまり「データ.xlsx」も同時に開いてるってこと？

 だったら

 D1 =IF(COUNTA(A1:C1)<3,"",SUMIF(INDIRECT("'[データ.xlsx]"&A1&"'!A2:A100"),B1,INDEX(INDIRECT("'[データ.xlsx]"&A1&"'!B2:G100"),0,MATCH(C1,INDIRECT("'[データ.xlsx]"&A1&"'!B1:G1"),0))))

 下にコピー

 これでいいのかな？
（笑） 2015/07/15(水) 16:40

笑様　ありがとうございます。おかげでできました
私の勉強不足で関数を１００％理解できていませんでした。もっと勉強していきます
本当にありがとうございました

（SUK） 2015/07/16(木) 22:19

コメント返信：

[ 一覧(最新更新順) ]

[[20150627143039]] 『複数シートのデーター検索』（SUK） ページの最後に飛ぶ

[[20150627143039]]　『複数シートのデーター検索』（SUK）　ページの最後に飛ぶ