[[20130914224505]]　『曜日で労働時間が変わる交番表の作成』（素人）　ページの最後に飛ぶ

『曜日で労働時間が変わる交番表の作成』（素人）

 以前、お世話になったのですが分からないので宜しくお願いします。
 http://www.excel.studio-kazu.jp/kw/20081114152829.html

 現在は、平日1（月）・平日2（火～木）・　平日3（金）・土（土）・休日（日、祭日）
 の5パターンで下のように入れてます。

 現在

 10月のシート

 C2  2013年  D2  10月

 F6 日にち（16）=C86
 F7 曜日　（土）=F6

 C8 個人番号　D8 名前　E8   F8 コース番号(1)

       16       17
 F84 =C86 G84 =F84+1

       月　　　火
 F85 =F84 G85 =G84

           2013/9/16         9/15                 
 C86 =DATE(C2,D2-1,16)   D86 =F84-1

                  祝日判定
 E86 =VLOOKUP(E84,ﾏｽﾀ!$B$6:$C$41,2,FALSE)

                  祝日判定
 F86 =VLOOKUP(F84,ﾏｽﾀ!$B$6:$C$41,2,FALSE)

       日　　　　　　　　　　　　祝日名　　　　　　　　　祝日名
 D87 =D86     E87 =IF(ISNA(E86),"",E86)       F87 =IF(ISNA(F86),"",F86)

 コース番号  
 F92 =IF(INDEX($C$8:$AJ$37,$B92,F$82)=0,"",INDEX($C$8:$AJ$37,$B92,F$82))

 労働時間
 F93  =IF(F92="","",VLOOKUP(F92,INDIRECT(LOOKUP(WEEKDAY(F$85,2)+10*(F$87<>""),{1;2;5;6;7},{"平日1";"平日2";"平日3";"土";"休日"})),2,0)/24)

 上のように入れてあります。

 そこで、今回は

  勤務パターンが5つあります。

                         (例）

 	平日1（月）

 交番	計画Ｈ	始業Ｈ	休憩H	終業Ｈ
 1	10	2  0	1  0	13:00
 2	10	2  0	1  0	13:00
 3	10	2  0	1  0	13:00
 4	10	2  0	1  0	13:00

 	平日2（火～金）

 交番	計画Ｈ	始業Ｈ	休憩H	終業Ｈ
 1	9	5  0　　　1　0　　15:00
 2	9	5  0      1  0    15:00
 3	9	5  0      1  0    15:00
 4	9	5  0      1  0    15:00

 	　(土)

 交番	計画Ｈ	始業Ｈ	休憩H	終業Ｈ
 1	8	5  0	1  0	14:00
 2	8	5  0	1  0	14:00
 3	8	5  0	1  0	14:00
 4	8	5  0	1  0	14:00

         (祝日)

 交番	計画Ｈ	始業Ｈ	休憩H	終業Ｈ
 1	7	5  0	1  0	13:00
 2	7	5  0	1  0	13:00
 3	7	5  0	1  0	13:00
 4	7	5  0	1  0	13:00

          (前祝)　　前の日が祝日

 交番	計画Ｈ	始業Ｈ	休憩H	終業Ｈ
 1	6	5  0	1  0	12:00
 2	6	5  0	1  0	12:00
 3	6	5  0	1  0	12:00
 4	6	5  0	1  0	12:00

 対象の曜日に計画時間が反映されるようにしたいのですがこちらの式に
 F93  =IF(F92="","",VLOOKUP(F92,INDIRECT(LOOKUP(WEEKDAY(F$85,2)+10*(F$87<>""),{1;2;5;6;7},{"平日1";"平日2";"平日3";"土";"休日"})),2,0)/24)

 前祝（祝日の後日）を追加したいのですがどのようにしてよいかわかりません。

 どうぞ、宜しくお願いします。（素人）

 ＞                  祝日判定
 ＞ E86 =VLOOKUP(E84,ﾏｽﾀ!$B$6:$C$41,2,FALSE)
 　　　　　　　　↑
 　　　　このE84セルには何が入っているんですか？

 (半平太) 2013/09/17(Tue) 19:24

 半平太さん
 お世話になります。

 すみません、E84ではなくD86でした。
 D86には、=F84-1で　○月15日です。

 （素人）

 土曜日の前日が祝日だったら、その土曜日はどっちの扱いになるのですか？

 「土」それとも「前祝」？

 (半平太) 2013/09/17(Tue) 20:29

 それも、前祝になります。

 =WEEKDAY(F85,2)+10*(F87<>"")+(20*(E87<>""))で＋20が前祝と判定します。

 (素人)

 ＞ 対象の曜日に計画時間が反映されるようにしたいのですがこちらの式に
 ＞ F93  =IF(F92="","",VLOOKUP(F92,INDIRECT(LOOKUP(WEEKDAY(F$85,2)+10*(F$87<>""),{1;2;5;6;7},{"平日1";"平日2";"平日3";"土";"休日"})),2,0)/24)

 「形」から考えると(・・って言うか、「形」以外、取っ掛かかれるものが何もない)、こんなものかと思います。

 F93セル =IF(F92="","",VLOOKUP(F92,INDIRECT(LOOKUP(LOOKUP(WEEKDAY(F$85,2)+10*(F$87<>""),{1;2;6;7})+(E$87<>"")*0.5,{1;2;2.5;6;6.5;7},{"平日1";"平日2";"前祝";"土";"前祝";"休日"})),2,0)/24)

 (半平太) 2013/09/17(Tue) 21:55

 半平太さん
 有難うございます。

 前祝の時間が検索出来るので大丈夫だと思います。

 もう一つできないことがあるので宜しくお願いします。

 F96に就業時間を入れたいのですが、前は、

   A       B      C  D       E  F       G

 交番	計画Ｈ  始業Ｈ    休憩H     終業Ｈ
　 1	　10	2  0	1  0	13:00

 F96 =IF(F92="","",VLOOKUP(F92,INDIRECT(LOOKUP(WEEKDAY(F$85,2),{1;2;5;6;7},{"平日1";"平日2";"平日3";"土";"休日"})),7,0))

 と入れてましたが、今回の式で7番目を検索したいのですが入れても出来ないので
 宜しくお願いします。（素人）

 ＞ F96 =IF(F92="","",VLOOKUP(F92,INDIRECT(LOOKUP(WEEKDAY(F$85,2),{1;2;5;6;7},{"平日1";"平日2";"平日3";"土";"休日"})),7,0))
 ＞ と入れてましたが、今回の式で7番目を検索したいのですが入れても出来ないので

 (当方、全貌は理解していないことをご承知おきください。)

 今回と同じようにやればいいんじゃないかと思うのですけど、つまり、
 これですけど、それだと上手くいかないのですか？
 　↓
  F96セル =IF(F92="","",VLOOKUP(F92,INDIRECT(LOOKUP(LOOKUP(WEEKDAY(F$85,2)+10*(F$87<>""),{1;2;6;7})+(E$87<>"")*0.5,{1;2;2.5;6;6.5;7},{"平日1";"平日2";"前祝";"土";"前祝";"休日"})),7,0))

 ちょっと気になるのは、なぜ現状の数式は、当日が祝日かどうかチェックしないのですか？　つまり【+10*(F$87<>"")】が入っていない。
 もし、チェックしないのが正しければ、上の推測で作った式もその部分を消去する必要が生じますけど。

 (半平太) 2013/09/18(Wed) 20:44

 何度もすみません。
 +10*(F$87<>"")抜けてました。半平太さんの式で出来ました。

 教えて欲しいのですが、前祝を検索する、(E$87<>"")*0.5とは、どんな意味ですか？
 あと、もし月曜から日曜、祝前の7パターンあったらどのように組み立てればよいのですか？
 (素人）

 ＞前祝を検索する、(E$87<>"")*0.5とは、どんな意味ですか？

 Lookup関数は、検索範囲の中に検索値が見つからない場合、内輪の最大値をヒットすることになっています。
 ※これが分からないと、以下に書いてあることは理解できません。

 Weekday関数は、1,2,3,4,5,6,7 のどれかを返してきますが、それらを検索値にして、{1;2;6;7}　の中を捜すと
 ヒットするのは 1,2,2,2,2,6,7 と云うことになります。　もし「前祝」だったら、それに0.5を足すことになり
 その７つの値は 1.5,　2.5,　2.5,　2.5,　2.5, 6.5, 7.5 と云うことになります。

 こんどはそれらを検索値にして、,{1;2;2.5;6;6.5;7}　の中を捜すと(Lookupが二つ続いていることに留意)、
 ７つの値は、1, 2.5, 2.5, 2.5, 2.5, 6.5, 7 をヒットします。

 つまり、2と6は、0.5が加わると素直に2.5と6.5になるが、1と7は、0.5を加えても、同じ1と7にしかなりません。

 これによって、「平日2」と「土」だけ、前祝とそうでないケースに区別でき、その他は変化ないことになります。

 なので、もし日曜日(または休日)も前祝になると云うことであれば、7と7.5が区別できるようにする必要がありますので、

 　{1;2;2.5;6;6.5;7;7.5},{"平日1";"平日2";"前祝";"土";"前祝";"休日";"前祝"}

 と云う配列定数をつくることになります。

 さすがに月曜に前祝があるとは思えませんが、考え方はおなじです。
 　{1;1.5;2;2.5;6;6.5;7;7.5},{"平日1";"前祝";"平日2";"前祝";"土";"前祝";"休日";"前祝"}

 でも、全部「前祝」になるなら、別のアプローチを考えることになるでしょう。
 始めっからそのケースを切り離して処理すれば、簡単になるはずですから。

 (半平太) 2013/09/18(Wed) 22:51

 半平太さん
 詳しい説明有難うございます。

 情報がない中、有難うございました。（素人）

コメント返信：

[ 一覧(最新更新順) ]

[[20130914224505]] 『曜日で労働時間が変わる交番表の作成』（素人） ページの最後に飛ぶ

[[20130914224505]]　『曜日で労働時間が変わる交番表の作成』（素人）　ページの最後に飛ぶ