Excel (一般機能)

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

 
(指定なし : 指定なし)
ローテーション表作成について
投稿日時: 17/05/30 01:10:14
投稿者: まさ77

お世話になっております。
表題の件ですが
A〜D班まであり、班員は8名。
班毎に仕事内容は全く違います。
班内では、2名作業で4つの仕事があります。
下記2点について関数を用いできないでしょうか?
@班内の仕事を1日毎に1名スライドさせる。
A一定の期間(1週間、1か月)で班の仕事をスライドさせる。
B人員や班の増減にもできれば対応したい。
 
イメージとしては下記のような月刊表みたいな感じかなと思っていますが
関数が思いつきません。
下記のような表でなくても簡単にローテーション表を作成できれば問題ありませんので
お教え頂きたく。

		5	月					
班名	氏名	1	2	3	4	5	6	7
A	田中さん							
	斉藤さん							
	佐藤さん							
	鈴木さん							
	山田さん							
	井上さん							
	安藤さん							
	伊藤さん							
B								
								
								
								
								
								
								
								

自力で作成しようとネット検索し、簡単な自動ローテーション表をみつけコピペしたのが
下記のような物です。
A           B         C                   
日にち	曜日	A班 音読係@	       開始	1
2017/5/1	月	AAAAA		
2017/5/2	火	田中さん	            音読@開始qホ応表	
2017/5/3	水	斉藤さん	                        1      田中さん
2017/5/4	木	佐藤さん	                        2      斉藤さん
2017/5/5	金	鈴木さん	                        3      佐藤さん
2017/5/6	土	山田さん	                        4      鈴木さん
2017/5/7	日	井上さん	                        5    山田さん
2017/5/8	月	安藤さん	                        6      井上さん
2017/5/9	火	AAAAA	                        7     安藤さん
2017/5/10	水	田中さん	                        8     AAAAA

数式は=IF(COUNTIF($B$2:$B3,"*日*")>1,VLOOKUP(MOD($E$1+ROW($A6)+COUNTIF($B$2:$B3,"*日*"),7)+1,$D$4:$E$11,2,0),VLOOKUP(MOD($E$1+(ROW($A7)),8)+1,$D$4:$E$11,2,0))
となっており、ただ単にスライドさせるような物ではないようです。
上記内容ついても理解できないので詳しく教えて頂けると助かります。
参考にしたサイトを下記に載せて置きます。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13129149728?__ysp=44Ot44O844OG44O844K344On44Oz6KGoIOOCqOOCr%2BOCu%2BODqw%3D%3D
 
エクセルのバージョン、等分かりましたら追記致します。
説明不足等あるかと思いますがよろしくお願い致します。

回答
投稿日時: 17/05/30 09:57:08
投稿者: んなっと

参考にした数式は今回は使えません。
今後は、ある程度完成した具体例を添えて質問してください。
 
1〜12の仕事番号を並べ替える例です。
 
●最初の2人組の分、D3:D4に1と入力
 D1に○を入力
 その右、班の仕事入れ替え日にも○を入力
 
  A   B   C  D E F G H I J  K L  M  N  O  P  Q  R
 1      5月 ○          ○             ○
 2   班名 氏名  1 2 3 4 5 6 7  8 9 10 11 12 13 14 15
 3    A  あ  1                         
 4       い  1                         
 5       う                           
 6       え                           
 7       お                           
 8       か                           
 9       き                           
10       く                           
11    B  け                           
12       こ                           
13       さ                           
14       し                           
15       す                           
16       せ                           
17       そ                           
18       た                           
19    C  ち                           
20       つ                           
21       て                           
22       と                           
23       な                           
24       に                           
25       ぬ                           
26       ね                           
 

 
    A   B   C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R
 1      3  5月 ○             ○             ○
 2    班名 氏名  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15
 3 1+1i   A  あ  1  4  4  3  3  2  2  9 12 12 11 11 10 10  5
 4 1+2i     い  1  1  4  4  3  3  2  9  9 12 12 11 11 10  5
 5 1+3i     う  2  1  1  4  4  3  3 10  9  9 12 12 11 11  6
 6 1+4i     え  2  2  1  1  4  4  3 10 10  9  9 12 12 11  6
 7 1+5i     お  3  2  2  1  1  4  4 11 10 10  9  9 12 12  7
 8 1+6i     か  3  3  2  2  1  1  4 11 11 10 10  9  9 12  7
 9 1+7i     き  4  3  3  2  2  1  1 12 11 11 10 10  9  9  8
10 1+8i     く  4  4  3  3  2  2  1 12 12 11 11 10 10  9  8
11 2+1i   B  け  5  8  8  7  7  6  6  1  4  4  3  3  2  2  9
12 2+2i     こ  5  5  8  8  7  7  6  1  1  4  4  3  3  2  9
13 2+3i     さ  6  5  5  8  8  7  7  2  1  1  4  4  3  3 10
14 2+4i     し  6  6  5  5  8  8  7  2  2  1  1  4  4  3 10
15 2+5i     す  7  6  6  5  5  8  8  3  2  2  1  1  4  4 11
16 2+6i     せ  7  7  6  6  5  5  8  3  3  2  2  1  1  4 11
17 2+7i     そ  8  7  7  6  6  5  5  4  3  3  2  2  1  1 12
18 2+8i     た  8  8  7  7  6  6  5  4  4  3  3  2  2  1 12
19 3+1i   C  ち  9 12 12 11 11 10 10  5  8  8  7  7  6  6  1
20 3+2i     つ  9  9 12 12 11 11 10  5  5  8  8  7  7  6  1
21 3+3i     て 10  9  9 12 12 11 11  6  5  5  8  8  7  7  2
22 3+4i     と 10 10  9  9 12 12 11  6  6  5  5  8  8  7  2
23 3+5i     な 11 10 10  9  9 12 12  7  6  6  5  5  8  8  3
24 3+6i     に 11 11 10 10  9  9 12  7  7  6  6  5  5  8  3
25 3+7i     ぬ 12 11 11 10 10  9  9  8  7  7  6  6  5  5  4
26 3+8i     ね 12 12 11 11 10 10  9  8  8  7  7  6  6  5  4
27 4+1i                                    
28 4+2i                                    
29 4+3i                                    
30 4+4i                                    
31 4+5i                                    
32 4+6i                                    
 
A3
=IF(OR(B3<>"",AND(C2<>"",C3="")),IMREAL(A2)+1&"+1i",IMREAL(A2)&"+"&IMAGINARY(A2)+1&"i")
下方向・↓[少し多めに]
B1
=COUNTA(B3:B200)
D5
=IF(C5="","",IF(B5="",D3,D4)+1)
下方向・↓
E3
=IF($C3="","",IF(E$1="○",INDEX($D:$D,MATCH(MOD(IMREAL($A3)-COUNTIF($C$1:E$1,"○"),$B$1)+1&"+"&IMAGINARY($A3)&"i",$A:$A,0)),IF($B3="",D2,INDEX(D:D,MATCH(IMREAL($A3)+1&"+*",$A:$A,0)-1))))
右方向・→下方向・↓

投稿日時: 17/05/30 23:28:46
投稿者: まさ77

お世話になっております。
ご回答ありがとうございます。
ご指南頂いた通りできました。
ご指南頂いた表に下記表を添えて仕事を割り振る感じに致しました。

仕事u\	
	仕事内容
1	テキスト音読
2	リスニング
3	書き取り
4	感想
5	野球
6	サッカー
7	バスケ
8	水泳
9	ギター
10	ピアノ
11	ベース
12	ドラム
13	洗濯
14	掃除機
15	トイレ掃除
16	窓ふき

んなっとさんが参考にした数式は今回は使用できません。
とおっしゃられていますが
下記数式の意味を勉強のためにお教え頂ければと思っております。
参考にしたサイトには日曜日に当たった人は次週土曜日に当たるようにすると記載がありますが
どこで指定しているのかとか等お教え頂ければ勉強になります。
 
数式は=IF(COUNTIF($B$2:$B3,"*日*")>1,VLOOKUP(MOD($E$1+ROW($A6)+COUNTIF($B$2:$B3,"*日*"),7)+1,$D$4:$E$11,2,0),VLOOKUP(MOD($E$1+(ROW($A7)),8)+1,$D$4:$E$11,2,0))
 
お忙しいとは思いますがよろしくお願い致します。

回答
投稿日時: 17/05/31 09:27:01
投稿者: んなっと

まず、ずらさないときの基本式
D2
=VLOOKUP(MOD($F$1-2+ROW(1:1),7)+1,$E$4:$F$10,2,0)
はいいですね。
 
    A  B    C       D E F
 1  日 曜 ずらす ずらさない    1
 2  5/1 金    A       A   
 3  5/2 土    B       B   
 4  5/3 日    C←1個目    C 1 A
 5  5/4 月    D       D 2 B
 6  5/5 火    E       E 3 C
 7  5/6 水    F       F 4 D
 8  5/7 木    G       G 5 E
 9  5/8 金    A       A 6 F
10  5/9 土    B       B 7 G
11 5/10 日    D←2個目★  C   
12 5/11 月    E       D   
13 5/12 火    F       E   
14 5/13 水    G       F   
15 5/14 木    A       G   
16 5/15 金    B       A   
17 5/16 土    C       B   
18 5/17 日    E←3個目★★ C   
19 5/18 月    F       D   
20 5/19 火    G       E   
21 5/20 水    A       F   
22 5/21 木    B       G   
23 5/22 金    C       A   
24 5/23 土    D       B   
25 5/24 日    F←4個目★★★C   
 
次に、ずらすときのC列を見てください。
B列の"日"の個数が0〜1個まではずらす必要はないので、D列と同じ式でいいことがわかります。
=VLOOKUP(MOD($F$1-2+ROW(1:1),7)+1,$E$4:$F$10,2,0)
 
ところが、B列の"日"の個数が2個になった瞬間、一つ次に飛ばしていますね。
=VLOOKUP(MOD($F$1-2+ROW(1:1)+1(★),7)+1,$E$4:$F$10,2,0)
 
B列の"日"の個数が3個になった瞬間、また一つ次に飛ばします。
=VLOOKUP(MOD($F$1-2+ROW(1:1)+1(★)+1(★),7)+1,$E$4:$F$10,2,0)
 
B列の"日"の個数が4個になった瞬間、さらにまた一つ次に飛ばします。
=VLOOKUP(MOD($F$1-2+ROW(1:1)+1(★)+1(★)+1(★),7)+1,$E$4:$F$10,2,0)
 
つまり、B列の"日"の個数が2個以上の時は
=VLOOKUP(MOD($F$1-2+ROW(1:1)+【B列の"日"の個数-1】(★★★の数),7)+1,$E$4:$F$10,2,0)
=VLOOKUP(MOD($F$1-2+ROW(1:1)+COUNTIF($B$2:B2,"日")-1,7)+1,$E$4:$F$10,2,0)
 
そんなわけで、2つの場合分けが必要だとわかりました。
・B列の"日"の個数が0〜1個まで
=VLOOKUP(MOD($F$1-2+ROW(1:1),7)+1,$E$4:$F$10,2,0)
・B列の"日"の個数が2個以上
=VLOOKUP(MOD($F$1-2+ROW(1:1)+COUNTIF($B$2:B2,"日")-1,7)+1,$E$4:$F$10,2,0)
 
まとめると
C2
=IF(COUNTIF($B$2:B2,"日")>1,VLOOKUP(MOD($F$1-2+ROW(1:1)+COUNTIF($B$2:B2,"日")-1,7)+1,$E$4:$F$10,2,0),VLOOKUP(MOD($F$1-2+ROW(1:1),7)+1,$E$4:$F$10,2,0))
 
さらにまとめると
C2
=VLOOKUP(MOD($F$1-2+ROW(1:1)+MAX(COUNTIF($B$2:B2,"日")-1,0),7)+1,$E$4:$F$10,2,0)

投稿日時: 17/05/31 23:07:11
投稿者: まさ77

こんばんは
 
ご丁寧にありがとうございます。
問題も解決、勉強にもなりました。