Excelでシフト表作成‥

質問

Excelで来年の基本シフト表（縦軸：日付・横軸：担当者）を作成しています。

月間労働時間177ｈ、休日8日、1日のシフトパターン（A：7.45ｈ・B：7.20ｈ）の月で、
出勤時刻はA・B共に6：00～9：00の30分単位、
退勤時刻はシフトパターンと出勤時刻によって異なります。

シフトパターン列・出勤時刻列は、ドロップダウンリストからそれぞれ選択できるよう設定してあり、
できるだけ、担当者に入力の手間をかけないよう、
「シフトパターンと出勤時刻を選択すると、
　退勤時刻列にシフトパターンと出勤時刻に応じた退勤時刻が設定される」
というようなことがしたいのですが、関数（数式）の設定で出来ますでしょうか？

Excel特に関数や数式設定が苦手なので、何方かご教示いただけましたら幸いです。
バージョンはExcel2003です。よろしくお願いいたします。。

回答No.6

kagakusuki · ベストアンサー

>C列：シフトパターン、D列：出勤時刻、E列：休憩時間、G列：実働時間
>F列に、例えば9：00出勤の場合は、Aの時「17：45」、Bの時「17：20」
>と表示されるように設定することは可能でしょうか？

　それでしたら、G列のセルには次の数式

=IF(AND(OR(INDEX($C:$C,ROW())="A",INDEX($C:$C,ROW())="B"),ISNUMBER(INDEX($D:$D,ROW()))),LOOKUP(INDEX($C:$C,ROW()),{"A","B"},{"7:45","7:20"})+0,"")

を入力し、定時の退勤時刻を表示するセルには

=IF(AND(ISNUMBER(INDEX($E:$E,ROW())),ISNUMBER(INDEX($G:$G,ROW())+0)),INDEX($D:$D,ROW())+INDEX($G:$G,ROW())+INDEX($E:$E,ROW()),"")

という数式を入力して下さい。

>ただ、実際の退勤時刻より1時間遅く表示されてしまいまして‥
>7時間45分と7時間20分は、元々、休憩時間（1時間）を引いた実働時間なので、

　逆ではないでしょうか？　実働時間の合間に休憩時間が挟まる事で、実働時間が7時間45分、或いは7時間20分に達する時刻が1時間遅くなるのですから、休憩時間を含めずに計算するANo.3の数式では、実際の退勤時刻よりも1時間だけ早い時刻が表示されるはずです。

お礼

Kagakusuki様

お礼が遅くなってしまいましたが・・
教えていただいた数式で、やりたいと思っていたことが完璧にできました！
凄いです。感動しました。
お陰様で、今年中に間に合いそうです。
数式の意味が理解できるよう、勉強頑張ります。
ありがとうございました！

回答No.5

keithin

＞シフトの列には、A・B以外に休日の選択もありまして

別に困っていないなら、わざわざ余計な（考えの足りてない）数式に差し替える必要はありません。
元の数式をそのままご利用ください。

たとえばＧ３：
=IF(C3="","",IF(C3="休日",0,TIME(7,IF(C3="A",45,20),0)))
他にも何かあるなら、適宜応用してください。

お礼

Keithin様

お礼が遅くなってしまいましたが・・
ご回答、ありがとうございます。
説明不足でしたが、何とかできました。
Excelの勉強をもっと頑張ります。
ありがとうございました！

回答No.4

keithin

あまり難しく考えず、

添付図：
Ｆ３には
=IF(OR(D3="",C3=""),"",D3+E3+G3)
Ｇ３には
=IF(C3="","",TIME(7,IF(C3="A",45,20),0))
と記入、下にコピーしておきます。

お礼

Keithin様

ご回答、ありがとうございます。
退勤時刻の表示はバッチリでした！
ただ‥　シフトの列には、A・B以外に休日の選択もありまして、
実動時間の列の数式を変えてしまうと、
表の下の方にある実動時間の集計が上手くいかなくなってしまいました。。
ちなみに、現在実働時間の列に設定されている数式は、
=IF(C5="A",TIME(7,45,0),IF(C5="B",TIME(7,20,0),TIME(0,0,0)))
というものです。←どこかからパクリました‥^^;

説明が中途半端で、上手くできなくてすみません。。
土日はパソコンが使用できないので、
また来週、勉強させていただきたいと思います。
ありがとうございました。

回答No.3

kagakusuki

>（A：7.45ｈ・B：7.20ｈ）

との事ですが、本当に7.45h(=7時間27分)や7.20h(=7時間12分)のような中途半端な時間となっているのでしょうか？
　若しかしますと、（A：7.45ｈ・B：7.20ｈ）ではなく、（A：7時間45分・B：7時間20分）の間違いではないでしょうか？
　もし、（A：7.45ｈ・B：7.20ｈ）が間違いだと仮定しますと、以下の様な方法となります。

　今仮に、B列にシフトパターン、C列に出勤時刻が入力されるものとしますと、退勤時刻の定時を表示するセルの中で、1番上にあるセルに、次の関数を入力して下さい。

=IF(AND(OR(INDEX($B:$B,ROW())="A",INDEX($B:$B,ROW())="B"),ISNUMBER(INDEX($C:$C,ROW()))),INDEX($C:$C,ROW())+LOOKUP(INDEX($B:$B,ROW()),{"A","B"},{"7:45","7:20"}),"")

　そして、上記の関数を入力したセルをコピーして、退勤時刻の定時を表示する列の中の他のセルに貼り付けて下さい。

補足

Kagakusuki様

ご回答、ありがとうございます。
ご指摘の通り、7時間45分と7時間20分の間違いでした。。
説明が不十分でしたが、教えていただいた数式をコピーしてみたところ、
ほぼ、やりたいと思っていたことができました。
数式の意味はまったく理解できていないのですが‥（汗）

ただ、実際の退勤時刻より1時間遅く表示されてしまいまして‥
7時間45分と7時間20分は、元々、休憩時間（1時間）を引いた実働時間なので、
またよく分からなくなってしまいました。。

そこで、今一度教えていただきたいのですが‥
↓C列：シフトパターン、D列：出勤時刻、E列：休憩時間、G列：実働時間
F列に、例えば9：00出勤の場合は、Aの時「17：45」、Bの時「17：20」
と表示されるように設定することは可能でしょうか？
宜しくお願いいたします。。

A　B　 C　　D　　　E　　　　F　　　　G
1　金　A　6:00　　1:00　　　　　　7:45:00
2　土　A　6:30　　1:00　　　　　　7:45:00
3　日　A　7:00　　1:00　　　　　　7:45:00
4　月　A　7:30　　1:00　　　　　　7:45:00
5　火　A　8:00　　1:00　　　　　　7:45:00
6　水　A　8:30　　1:00　　　　　　7:45:00
7　木　A　9:00　　1:00　　　　　　7:45:00
8　金　B　9:00　　1:00　　　　　　7:20:00

回答No.2

KURUMITO

例えばB1セルには出勤時刻、C１セルにはシフトパターン、D１セルには退勤時刻の文字がそれぞれ入力されているとしてB列やC列のデータはドロップダウン方式で選択できるようになっているとのことですから、D２セルには次の式を入力して下方にドラッグコピーしてはどうでしょう。

=IF(OR(B2="",C2=""),"",IF(C2="A",B2+7.45/24,B2+7.2/24))

エクセルでの時間の計算はシリアル値が基本となっており、日単位の数値となっています。そのため7.45時間などを２４で割って使用しています。

お礼

KURUMITO様

教えていただいた数式をコピーして試してみたのですが、
シリアル値の調整が上手くできずに、時間が微妙にズレてしまいまして‥
シリアル値‥　もう少し勉強しなければいけませんね。。
ご回答、ありがとうございました。