Excel (一般機能)

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

 
(Windows 10 Pro : Excel 2019)
レベルを均等にグループわけ
投稿日時: 22/05/10 11:09:00
投稿者: ラングドシャ

よろしくお願いいたします。
 
<抽選シート>

	B	  C   D	 E	F	    G	       H
2	5月8日						
3							
4	氏名	 入力  確認	調査	レベル		
5	社員1   5	 5		3	 0.225560151	
6	社員2	  5	 5		3	 0.3882304	
7	社員3						
8	社員4   5	 5		2	 0.493205119	
9	社員5	  5			1	 0.962810836	
10	社員6   5	 5		2	 0.643787536	
11	社員7	  5	 5		3	 0.223476178	
12	社員8	  5	 5		2	 0.892161323	
13	社員9	  5	 5		2	 0.283325128	
14	社員10  5	 5		2	 0.523452283	
15	社員11  5			3	 0.964078535	
16	社員12  5   5		3	 0.148343103	
17	社員13  5     5		1	 0.771740558	
18	社員14  5	  5		2	 0.437697384	
19	社員15  5	  5		3	 0.829057765	
20	社員16  5			1	 0.936339612	

 
<割り当てシート>
	B	C	 D	 E
2				
3		入力	 確認	 調査
4	A	社員11		
5	B	社員5		
6	C	社員16		

 
抽選シートと割り当てシートがあります。
抽選シートのB2に日付を入れると、その日に出勤できる人(その業務ができる人)の時間が
表示されます。(別のシートから関数で抽出しています)
上記で言うと5という数字の入っている人がその日に出勤できる人です。
(曜日によっては6,7などの数字が入ることもあります)
G列はRAND関数です。
 
この抽選シートを元に、割り当てシートに担当作業をグループ分けをしたいと思っています。
割り当てシートの「入力」はレベルなど関係なく全員にまんべんなく割り当てたいので、
割り当てシートのC列に
=INDEX(B:B,MATCH(LARGE(G:G,1),G:G,0))
とし、上位3位がA・B・Cに割り当てられています。
次に「確認」と「調査」を割り当てたいのですが、抽選シートのレベルを参考にグループの
レベルが合計6になるようにしたいのです。
Aグループなら、社員11のレベルが3なので、確認と調査は1と2から選出。
Bグループなら、社員5のレベルが1なので、確認と調査は2と3から選出。
というようにしたいのですが、関数で可能でしょうか?
「入力」に割り当てられた人と同じレベルの人を非表示にしてみたりしたのですが、3つの
グループに対応しないといけないのでうまくできませんでした。
※上記サンプルデータの<抽選シート>の「調査」に5が入っていないのは、確認の担当が
決まった時点で確認の人を対象から外すためあえて入れていませんが実際には5が入っています。
 
ご教示よろしくお願い致します。

投稿日時: 22/05/10 11:46:31
投稿者: ラングドシャ

訂正
 
サンプルを同じシート内で作成したので
=INDEX(B:B,MATCH(LARGE(G:G,1),G:G,0))
に誤りがありました。
 
実際には
=INDEX(抽選!B:B,MATCH(LARGE(抽選!G:G,1),抽選!G:G,0))

投稿日時: 22/05/10 12:09:21
投稿者: ラングドシャ

さらに追記です。すみません。
確認と調査もそのレベルからランダムに選出したいです。
よろしくお願いいたします。

回答
投稿日時: 22/05/10 12:47:01
投稿者: んなっと

完全に希望通りとはいきませんが、一つの方法です。
  
●抽選
  
      B   C   D   E    F      G H I J
 4   氏名 入力 確認 調査 レベル        1 2 3 ←入力
 5  社員1   5   5   5    3 0.225560151    3
 6  社員2   5   5   5    3  0.3882304    2
 7  社員3                        
 8  社員4   5   5   5    2 0.493205119   4  
 9  社員5   5          1 0.962810836 1   
10  社員6   5   5   5    2 0.643787536   2  
11  社員7   5   5   5    3 0.223476178    4
12  社員8   5   5   5    2 0.892161323   1  
13  社員9   5   5   5    2 0.283325128   6  
14 社員10   5   5   5    2 0.523452283   3  
15 社員11   5          3 0.964078535     
16 社員12   5   5   5    3 0.148343103    5
17 社員13   5   5   5    1 0.771740558 3   
18 社員14   5   5   5    2 0.437697384   5  
19 社員15   5   5   5    3 0.829057765    1
20 社員16   5          1 0.936339612 2   
  
H5
=IF(C5="","",IF($F5=H$4,COUNTIFS(C$5:C$200,">0",$F$5:$F$200,H$4,$G$5:$G$200,">"&$G5)+1,""))
右方向・→下方向・↓
  
●割り当て
  
  B     C     D     E F G H
3     入力   確認   調査     
4 A 社員11 社員16 社員10 3 1 2
5 B  社員5  社員6  社員1 1 2 3
6 C 社員14  社員2 社員13 2 3 1 ←本当は2 ,2 ,2のレベルの
                       組み合わせもあるが、面倒なので無視
  
D4
=INDEX(抽選!$B$5:$B$200,MATCH(COUNTIF($F$4:F$6,G4)+COUNTIF(G$3:G3,G4)+1,INDEX(抽選!$H$5:$J$200,,G4),0))
右一つ・→下方向・↓
F4
=VLOOKUP(C4,抽選!$B:$F,5,FALSE)
下方向・↓
G4
=MOD($F4,3)+1
下方向・↓
H4
=MOD($F4+1,3)+1
下方向・↓

投稿日時: 22/05/10 14:24:29
投稿者: ラングドシャ

んなっと様
 
ありがとうございます。
教えていただいた方法でほぼ完ぺきにできました。
 
一つ気になる箇所があるので教えてください。
 
●抽選シート

      B   C   D   E    F      G H I J
 4   氏名 入力 確認 調査 レベル        1 2 3 ←入力
 5  社員1   5   5   5    3 0.225560151    3
 6  社員2   5   5   5    3  0.3882304    2
 7  社員3                        
 8  社員4   5   5   5    2 0.493205119   4  
 9  社員5   5          1 0.962810836 1   
10  社員6   5   5   5    2 0.643787536   2  
11  社員7   5   5   5    3 0.223476178    4
12  社員8   5   5   5    2 0.892161323   1  
13  社員9   5   5   5    2 0.283325128   6  
14 社員10   5   5   5    2 0.523452283   3  
15 社員11   5          3 0.964078535     
16 社員12   5   5   5    3 0.148343103    5
17 社員13   5   5   5    1 0.771740558 3   
18 社員14   5   5   5    2 0.437697384   5  
19 社員15   5   5   5    3 0.829057765    1
20 社員16   5          1 0.936339612 2   

 
上記のように、確認のD列とE列は数字が同じように入っています。
調査のE列で、確認作業で割り当たった人を除外しておかなくても重複しないでしょうか?
H〜J列ではIFでそれぞれの空白を検査しているので、除外が必要ですよね?
 
実は調査E列に、確認で割り当たった人を除外しようと関数を入れると循環になってしまいました。
=IF(OR(割り当て!$C$4=$B5,割り当て!$C$5=$B5,割り当て!$C$6=$B5,割り当て!$D$4=$B5,割り当て!$D$5=$B5,割り当て!$D$6=$B5,$C5=""),"",$C5)
としました。E列とJ列、割り当てシートのD列で循環となります。
E列で確認作業者の除外が不要であればよいのですが、除外した方がよい場合、E列の関数で
循環にならない方法を教えていただけないでしょうか?
後だしになってしまい申し訳ありません。

回答
投稿日時: 22/05/10 15:13:39
投稿者: んなっと

別の方法で除外していますので、調査シートE列は下のような単純なものでOKです。
 
E5
=IF(D5="","",D5)
下方向・↓

投稿日時: 22/05/10 15:30:25
投稿者: ラングドシャ

んなっと様
 
承知しました。
別のグループ作成にも利用させていただきます。
本当にありがとうございました。