HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2019)
2か所の乱数からの抽選
投稿日時: 23/09/28 15:13:42
投稿者: ラングドシャ

よろしくお願いいたします。
 

	G	　　H	　　　　　　I
11		　 抽選１	　抽選２
12	社員１		　　　　 0.990349133
13	社員２		
14	社員３		　　　　 0.720878271
15	社員４		
16	社員５　  0.63891507	
17	社員６		
18	社員７		　　　　 0.873199814
19	社員８		
20	社員９　  0.162289121	
21	社員１０		 0.148771564

 
このような表があります。
Ｈ，Ｉ列は、抽選対象者のみ乱数が表示されています。
 
そこから26､27行目に抽選として乱数の大きい順に選択し、以下の表を作っています。 
	G	H	I	J	K
26	確定	社員５	社員９	社員３	社員１０
27	確定	#NUM!
    
 
Ｈ列抽選１を優先的に抽選し、対象者がいなくなればＩ列抽選２から選択したいため
H26にはこのような関数を入れています。
=IFERROR(INDEX($G$12:$G$21,MATCH(LARGE($H$12:$H$21,COLUMN(A1)),$H$12:$H$21,0)),INDEX($G$12:$G$21,MATCH(LARGE($I$12:$I$21,COLUMN(A1)),$I$12:$I$21,0)))
これを26，27行目にコピーしています。
抽選1から社員９が選ばれたあと、J26では抽選2から選択されていますが関数をコピーしている
ためJ26ではCOLUMN(C1)となり、3番目の人から選択されて
しまいます。H27では、選択できずエラーになります。
 
抽選１が選択できなくなり、抽選２から選ぶときに1番目に大きい人から選択されるようにする
にはどのようは方法がありますか?
よろしくお願いいたします。
 
回答
投稿日時: 23/09/28 19:44:26
投稿者: 半平太

＞H26にはこのような関数を入れています。
＞=IFERROR(INDEX($G$12:$G$21,MATCH(LARGE($H$12:$H$21,COLUMN(A1)),・・・
　↓
=IFERROR(LOOKUP(1,0/(LARGE(INDEX($H$12:$I$21,0,ROW(A1)),COLUMN(A1))=INDEX($H$12:$I$21,0,ROW(A1))),$G$12:$G$21),"")

投稿日時: 23/09/29 13:50:53
投稿者: ラングドシャ

半平太様
ありがとうございます。
27行目にコピーする際、手動で変更しなくてもきちんと抽出されました。
 
一点、質問の仕方が悪かったのですが、仮に抽選1に対象者が８名いた場合、
H26:K27までをH列から選択したいのですが、そういったことは不可能でしょうか？
 

	G	　　H	　　　　　　I
11		　 抽選１	　抽選２
12	社員１ 　 0.731366542　　
13	社員２　　0.776761239		
14	社員３	　0.720878271
15	社員４	　0.491260815	
16	社員５　  0.63891507	
17	社員６	　0.565566473
18	社員７		　　　　 0.873199814
19	社員８	  0.299470033	
20	社員９　  0.162289121	
21	社員１０　　　　　　 　  0.148771564

 
こういう場合、抽選１を優先したいので
 
	G	H	I	J	K
26	確定	社員２	社員１	社員３	社員５
27	確定	社員６　社員４　社員８　社員９

 
こういう選択をする関数は無理でしょうか？
抽選1の人が選択できなくなった時点で抽選２から選択したく。
色々考えてみますが、大きい順に選択させるところでつまづいてしまい。
もし、無理そうなら別の方法を考えてみます。
よろしくお願いいたします。
        
回答
投稿日時: 23/09/29 14:54:42
投稿者: 半平太

1行最多4人ずつ表示、と言うことなんですか？
 
H26セル =IFERROR(IF(COUNTA($G26:G26)>4,"",INDEX($G$1:$G$21,MOD(AGGREGATE(14,6,RANK($H$12:$I$21,$H$12:$I$21,1)*100+{10000,0}+ROW($H$12:$I$21),COLUMN(A1)+4*(ROW(A1)-1)),100))),"")

投稿日時: 23/09/29 15:20:35
投稿者: ラングドシャ

半平太様
ありがとうございます。
1行最多4人というのを、最初の質問で漏れていました。申し訳ありません。
 
H26セル =IFERROR(IF(COUNTA($G26:G26)>4,"",INDEX($G$1:$G$21,MOD(AGGREGATE(14,6,RANK($H$12:$I$21,$H$12:$I$21,1)*100+{10000,0}+ROW($H$12:$I$21),COLUMN(A1)+4*(ROW(A1)-1)),100))),"")
 
こちらの式でうまく抽出できました。できるんですね･･･すごいです。
想像していたよりずっと複雑な式でした。
本当にありがとうございました。