Excelで来年の基本シフト表(縦軸:日付・横軸:担当者)を作成しています。
月間労働時間177h、休日8日、1日のシフトパターン(A:7.45h・B:7.20h)の月で、
出勤時刻はA・B共に6:00~9:00の30分単位、
退勤時刻はシフトパターンと出勤時刻によって異なります。
シフトパターン列・出勤時刻列は、ドロップダウンリストからそれぞれ選択できるよう設定してあり、
できるだけ、担当者に入力の手間をかけないよう、
「シフトパターンと出勤時刻を選択すると、
退勤時刻列にシフトパターンと出勤時刻に応じた退勤時刻が設定される」
というようなことがしたいのですが、関数(数式)の設定で出来ますでしょうか?
Excel特に関数や数式設定が苦手なので、何方かご教示いただけましたら幸いです。
バージョンはExcel2003です。よろしくお願いいたします。。
No.6ベストアンサー
- 回答日時:
>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
- 回答日時:
>シフトの列には、A・B以外に休日の選択もありまして
別に困っていないなら、わざわざ余計な(考えの足りてない)数式に差し替える必要はありません。
元の数式をそのままご利用ください。
たとえばG3:
=IF(C3="","",IF(C3="休日",0,TIME(7,IF(C3="A",45,20),0)))
他にも何かあるなら、適宜応用してください。
Keithin様
お礼が遅くなってしまいましたが・・
ご回答、ありがとうございます。
説明不足でしたが、何とかできました。
Excelの勉強をもっと頑張ります。
ありがとうございました!
No.4
- 回答日時:
あまり難しく考えず、
添付図:
F3には
=IF(OR(D3="",C3=""),"",D3+E3+G3)
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
- 回答日時:
>(A:7.45h・B:7.20h)
との事ですが、本当に7.45h(=7時間27分)や7.20h(=7時間12分)のような中途半端な時間となっているのでしょうか?
若しかしますと、(A:7.45h・B:7.20h)ではなく、(A:7時間45分・B:7時間20分)の間違いではないでしょうか?
もし、(A:7.45h・B:7.20h)が間違いだと仮定しますと、以下の様な方法となります。
今仮に、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
- 回答日時:
例えばB1セルには出勤時刻、C1セルにはシフトパターン、D1セルには退勤時刻の文字がそれぞれ入力されているとしてB列やC列のデータはドロップダウン方式で選択できるようになっているとのことですから、D2セルには次の式を入力して下方にドラッグコピーしてはどうでしょう。
=IF(OR(B2="",C2=""),"",IF(C2="A",B2+7.45/24,B2+7.2/24))
エクセルでの時間の計算はシリアル値が基本となっており、日単位の数値となっています。そのため7.45時間などを24で割って使用しています。
KURUMITO様
教えていただいた数式をコピーして試してみたのですが、
シリアル値の調整が上手くできずに、時間が微妙にズレてしまいまして‥
シリアル値‥ もう少し勉強しなければいけませんね。。
ご回答、ありがとうございました。
aokii様
教えていただいたところを覗いてみたのですが
ざっと見ただけではよく分からなかったので‥(汗)
時間のある時にじっくり勉強してみようと思います。
ご回答、ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル2019の関数を教えてください。 8 2022/12/16 12:45
- Excel(エクセル) 出退勤管理の早退時間について 4 2023/08/21 15:22
- 求人情報・採用情報 シフト勤務について 2 2023/08/05 16:53
- Excel(エクセル) Excel関数で日またぎの勤務時間にしるしを立てる 2 2022/04/20 17:22
- 仕事術・業務効率化 IF関数で時間指定をして、数値を切り上げたいです 1 2022/05/01 23:37
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- Excel(エクセル) 【Excel関数、count系】どなたか教示下さると助かります。 Excelでシフトを管理しており、 2 2022/07/09 06:34
- アルバイト・パート 【至急回答募集!】バイトを無断欠勤してしまいました…… 1 2022/06/27 22:04
- その他(Microsoft Office) 勤務表のエクセル作成で数式を教えてください。 1 2023/01/17 03:27
- 医療・介護・福祉 タイムカードの不正打刻に当たるのかどうか教えてほしいです。 1 2022/05/25 17:11
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの入力規則で2列表示したい
-
複数の文字列のいずれかが含ま...
-
別のセルに値が入力されたら、...
-
エクセルでセルの値分の個数の...
-
エクセルのsumifでかけ算してか...
-
エクセルで1列全部10倍したい
-
列の数字に100をかけたい
-
Excel上でのデータ数字が連番で...
-
【EXCEL】指定したセルの値を他...
-
エクセル セル内の文字数を超え...
-
数式が入ったセルを含めて、数...
-
Excelにて、セルをダブルクリッ...
-
Excelでセル同士のうちで、一部...
-
OpenOfficeでウインドウの固定...
-
エクセル:横長の表を改行して...
-
エクセルで2行まとめて並び替...
-
エクセル 関数(カウント?)...
-
エクセルで特定の文字を含むセ...
-
VBA 同一シート内での転記の仕方
-
「Exel」で、指定した列が...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
複数の文字列のいずれかが含ま...
-
Excelの入力規則で2列表示したい
-
SUMに含まれる範囲から特定のセ...
-
数式が入ったセルを含めて、数...
-
Excel上でのデータ数字が連番で...
-
列の数字に100をかけたい
-
スペースとスペースの間の文字...
-
別のセルに値が入力されたら、...
-
エクセルで表示されている数字...
-
エクセルでセルの値分の個数の...
-
エクセル:横長の表を改行して...
-
[関数について]わかる方教えて...
-
エクセル関数に詳しい方教えて...
-
エクセルで、毎日の走行距離(...
-
エクセルのsumifでかけ算してか...
-
HYPERLINKとADDRESSとMATCHの組...
-
エクセルで曜日に応じた文字を...
-
入退社日より各月末の在籍者数...
-
エクセルで1列全部10倍したい
-
エクセルで小数を含む数値の抽出
おすすめ情報