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

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	社員１　　　5	　5		3	 0.225560151	
6	社員２	　　5	　5		3	 0.3882304	
7	社員３						
8	社員４　　　5	　5		2	 0.493205119	
9	社員５	　　5			1	 0.962810836	
10	社員６　　　5	　5		2	 0.643787536	
11	社員７	　　5	　5		3	 0.223476178	
12	社員８	　　5	　5		2	 0.892161323	
13	社員９	　　5	　5		2	 0.283325128	
14	社員１０　　5	　5		2	 0.523452283	
15	社員１１　　5			3	 0.964078535	
16	社員１２　　5　　 5		3	 0.148343103	
17	社員１３　　5     5		1	 0.771740558	
18	社員１４　　5	  5		2	 0.437697384	
19	社員１５　　5	  5		3	 0.829057765	
20	社員１６　　5			1	 0.936339612

 
＜割り当てシート＞ 
	B	C	　D	　E
2				
3		入力	　確認	　調査
4	A	社員１１		
5	B	社員５		
6	C	社員１６

 
抽選シートと割り当てシートがあります。
抽選シートのB2に日付を入れると、その日に出勤できる人(その業務ができる人)の時間が
表示されます。（別のシートから関数で抽出しています）
上記で言うと5という数字の入っている人がその日に出勤できる人です。
（曜日によっては6,7などの数字が入ることもあります）
G列はＲＡＮＤ関数です。
 
この抽選シートを元に、割り当てシートに担当作業をグループ分けをしたいと思っています。
割り当てシートの「入力」はレベルなど関係なく全員にまんべんなく割り当てたいので、
割り当てシートのC列に
=INDEX(B:B,MATCH(LARGE(G:G,1),G:G,0))
とし、上位3位がＡ・Ｂ・Ｃに割り当てられています。
次に「確認」と「調査」を割り当てたいのですが、抽選シートのレベルを参考にグループの
レベルが合計6になるようにしたいのです。
Ａグループなら、社員１１のレベルが3なので、確認と調査は1と2から選出。
Ｂグループなら、社員５のレベルが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　　社員１　　 5　　 5　　 5　　　 3　0.225560151　　　　3
 6　　社員２　　 5　　 5　　 5　　　 3　　0.3882304　　　　2
 7　　社員３　　　　　　　　　　　　　　　　　　　　　　　　
 8　　社員４　　 5　　 5　　 5　　　 2　0.493205119　　 4　　
 9　　社員５　　 5　　　　　　　　　 1　0.962810836　1　　　
10　　社員６　　 5　　 5　　 5　　　 2　0.643787536　　 2　　
11　　社員７　　 5　　 5　　 5　　　 3　0.223476178　　　　4
12　　社員８　　 5　　 5　　 5　　　 2　0.892161323　　 1　　
13　　社員９　　 5　　 5　　 5　　　 2　0.283325128　　 6　　
14　社員１０　　 5　　 5　　 5　　　 2　0.523452283　　 3　　
15　社員１１　　 5　　　　　　　　　 3　0.964078535　　　　　
16　社員１２　　 5　　 5　　 5　　　 3　0.148343103　　　　5
17　社員１３　　 5　　 5　　 5　　　 1　0.771740558　3　　　
18　社員１４　　 5　　 5　　 5　　　 2　0.437697384　　 5　　
19　社員１５　　 5　　 5　　 5　　　 3　0.829057765　　　　1
20　社員１６　　 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　社員１１　社員１６　社員１０　3　1　2
5　B　　社員５　　社員６　　社員１　1　2　3
6　C　社員１４　　社員２　社員１３　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　　社員１　　 5　　 5　　 5　　　 3　0.225560151　　　　3
 6　　社員２　　 5　　 5　　 5　　　 3　　0.3882304　　　　2
 7　　社員３　　　　　　　　　　　　　　　　　　　　　　　　
 8　　社員４　　 5　　 5　　 5　　　 2　0.493205119　　 4　　
 9　　社員５　　 5　　　　　　　　　 1　0.962810836　1　　　
10　　社員６　　 5　　 5　　 5　　　 2　0.643787536　　 2　　
11　　社員７　　 5　　 5　　 5　　　 3　0.223476178　　　　4
12　　社員８　　 5　　 5　　 5　　　 2　0.892161323　　 1　　
13　　社員９　　 5　　 5　　 5　　　 2　0.283325128　　 6　　
14　社員１０　　 5　　 5　　 5　　　 2　0.523452283　　 3　　
15　社員１１　　 5　　　　　　　　　 3　0.964078535　　　　　
16　社員１２　　 5　　 5　　 5　　　 3　0.148343103　　　　5
17　社員１３　　 5　　 5　　 5　　　 1　0.771740558　3　　　
18　社員１４　　 5　　 5　　 5　　　 2　0.437697384　　 5　　
19　社員１５　　 5　　 5　　 5　　　 3　0.829057765　　　　1
20　社員１６　　 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
投稿者: ラングドシャ

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