Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : Excel 2007)
抽出結果が複数ヒットした場合に1つのセルに全て表示させる
投稿日時: 17/11/26 16:43:09
投稿者: まさ77

お世話になっております。
 
抽出関数、vlookup等では検索結果に対し1つしか表示されませんが
ヒットした検索結果を全て、セルに表示させたく思っています。
どんな関数を使用すれば、または、関数の組み合わせ等をお教え頂きたく。
 
 
表は下記2つあり

				繰越保有	1H				1Y			
	B	C	D	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
3					10月2日				10月2日			
4					昼				夜			
5					2	2	2	2	2	2	2	2
6					2	2	2	2	2	2	2	2
7					H	H	H	H	Y	Y	Y	Y
8				繰越保有	生産数	使用数	順	保有	生産数	使用数	順	保有
9					2H生産数	2H使用数	2H順	2H保有	2Y生産数	2Y使用数	2Y順	2Y保有
10	機種	部品1	部品2									
11	1111	a1111	222aa	100	500	100	3					
12	2222	b1111	222aa	1	300	1	2					
13	3333	a1111	aaa11		100		1					
14	22AABB		222aa	50		50						
15	11AAA			3								

 
上記「部品1」を使用する順を取得したく
 
表2
		B	C	D	E	F	G
2		1H	1Y	2H	2Y	3H	3Y
3		1H生産数	1Y生産数	2H生産数	2Y生産数	3H生産数	3Y生産数
4	部品	1H順	1Y順	2H順	2Y順	3H順	3Y順
5	a1111	600					
6	b1111	300					
7	aaa11						
8	222aa						
9	111ee						
10	aacc						
11	vvv11						
12							
13							
14		B	C	D	E	F	G
15	部品	1H順	1Y順	2H順	2Y順	3H順	3Y順
16	a1111	1,3					
17	b1111	2					
18	aaa11						
19	222aa						
20	111ee						
21	aacc						
22	vvv11						

表2の5〜13は使用数
16〜22は生産順を取得したく。
使用数については「sumproduct」関数にて取得しています。
 

回答
投稿日時: 17/11/26 19:48:08
投稿者: Mike

「表2」のB列(1H順)のデータは、「表1」ではAJ列(2H生産数)やAl列(2H順)にあるけど、「2H」→「1H」に移動したのですか?
 
「あっ、間違えたぁ〜」なんて軽々しく言わないでぇ〜!

回答
投稿日時: 17/11/26 22:25:30
投稿者: んなっと

順の数字は1〜6まで限定。
A15に部品1と入力して
 
     A   B   C   D
15 部品1  1H順  1Y順  2H順
16  a1111 1,3,5 3,4,5 2,3,4
17  b1111   2   1   5
18  aaa11           
19  222aa   4   2   1
 
B16
=IFERROR(SUBSTITUTE(SUM(IFERROR(AGGREGATE(15,6,INDEX(Sheet1!$11:$20,,MATCH(B$15,Sheet1!$9:$9,0))/(INDEX(Sheet1!$11:$20,,MATCH($A$15,Sheet1!$10:$10,0))=$A16),{1,2,3,4,5,6}),)*10^{5,4,3,2,1,0}),0,)*1,"")
Ctrl+Shift+Enter同時押し
書式 ユーザー定義
[>=100]#,#!,#!,#;[>=10]#!,#;#
条件付書式 セルの値が10000以上のとき 表示形式 ユーザー定義
[>=100000]#!,#!,#,#!,#!,#;#!,#,#!,#!,#

回答
投稿日時: 17/11/27 00:38:05
投稿者: んなっと

今気づきました。
前のスレッドではExcel2010だったのに
http://www.moug.net/faq/viewtopic.php?t=76350
今回はExcel2007での質問に変わりましたね。
AGGREGATEは使えないので、式が変わります。
 
B16
=IFERROR(SUBSTITUTE(SUM(IFERROR(SMALL(IF(INDEX(Sheet1!$11:$20,,MATCH($A$15,Sheet1!$10:$10,0))=$A16,INDEX(Sheet1!$11:$20,,MATCH(B$15,Sheet1!$9:$9,0)),""),{1,2,3,4,5,6}),)*10^{5,4,3,2,1,0}),0,)*1,"")
Ctrl+Shift+Enter同時押し
 
それから、1Hは「1日の昼」という意味だったようです。
前回の質問、今回の質問ともに表1の10月2日はすべて10月1日の間違いですね?
今後は気を付けて質問してください。

回答
投稿日時: 17/11/27 09:42:24
投稿者: bi

回答ではありませんが、未解決の質問がありますよ。
 
REPT関数で分かりやすい簡易グラフ作成について
http://www.moug.net/faq/viewtopic.php?t=75853
 
部品一覧表から使用する部品数を抽出する
http://www.moug.net/faq/viewtopic.php?t=76362

回答
投稿日時: 17/11/27 09:58:15
投稿者: 細雪

回答ではありませんが、
家は2010、会社は2007ってことですかね?
> (前の質問)家ではできた、会社ではできなかった
2007では使えない関数もあるのだから、
そこはハッキリさせた方が良さそうですよ。
 
 

回答
投稿日時: 17/11/28 16:33:16
投稿者: んなっと

表示形式を使わないで数式だけだと、ますます長くなって
 
B16
=IFERROR(SUBSTITUTE(TRIM(TEXT(SUBSTITUTE(SUM(IFERROR(SMALL(IF(INDEX(Sheet1!$11:$20,,MATCH($A$15,Sheet1!$10:$10,0))=$A16,INDEX(Sheet1!$11:$20,,MATCH(B$15,Sheet1!$9:$9,0)),""),{1,2,3,4,5,6}),)*10^{5,4,3,2,1,0}),0,)*1,"# # # # # #"))," ",","),"")
Ctrl+Shift+Enter同時押し

トピックに返信