ここから本文です

今回、初めてこちらの掲示板を利用させてもらいます。 皆様のお知恵を拝借できた...

sat********さん

2020/5/3015:41:54

今回、初めてこちらの掲示板を利用させてもらいます。
皆様のお知恵を拝借できたら考えております。

今、私は福祉施設で勤務している者です。

小さな法人ですので、これからExcelで勤務時間の入力をできないものかと検討しております。
私は、Excelはそれなりに使うのでですが、関数がSUMぐらいしか分かりません・・・。
もちろん、こちらを利用する前に色々と本やネット検索してみたり、したのですが、私が求めている関数式が見つかりません。

【表の見方】

6/1は、1回目勤務が6:30~9:30、2回目勤務が15:30~22:30、宿直が22:30~
翌朝6:00までという勤務があります(9:30~15:30は休憩時間となります)。
なので、最高で1日3行の記入が必要になります。
6/2は、朝6:00~18:00までの続行勤務となります。

G欄はそれぞれの勤務時間を表示しております。
H欄の黒字はG欄の1日の勤務時間合計(3:00+7:00+7:30)で「17:30」。
H欄の赤字は残業時間(H5-G3)で「9:30」と表示されるようにしております。

【挿入している計算式】

6/1の場合
■G欄・・・G5:F5+1-E5、G6:F6+1-E5、G7:F7+1-E7
→数式に1を入力しているのは、日付をまたいでしまうためです。1を入れないとエラーで計算してくれません。
■H欄黒字・・・=SUM(G5:G7)
■H欄赤字・・・=H5-G3


【下記が私の疑問点です】

①、6/3は14:30~翌日(6/4)朝6:00まで勤務していることを意味しています。
G欄は正確に時間計算ができています(14:30-24:00=9:30勤務、24:00-6:00=6:00勤務)が、H欄の黒字(H11)が上手く表示できません。本来ですと、9:30+6:00でH11には「15:30」と表示されないといけないのですが、「63:30」と表示されます。ここにはどのような計算式を入れたらいいのでしょうか?
→今、H11には「=SUM(G11:G13)」という式をいれています。

②、6/4は休みですが、残業時間が16:00と表示されます。ここを0:00に表示したいのですが、どのような計算式を入れたらよろしいでしょうか?

③、また、6/4は休みなのにもかかわらず、H14には「72:00」と表示されます。ここも勤務してないため「0:00」と表示したいです。
表示には、「[h]:mm」を利用してます。


以上になります。
皆様のお力を貸して下さい。

H5-G3,H11,G7,G100 ROUNDUP,関数,黒字,H8 H11 H14

閲覧数:
35
回答数:
2
お礼:
100枚

違反報告

ベストアンサーに選ばれた回答

set********さん

2020/5/3020:35:56

下の図のように表示したい、ということでしょうか?

G5セルには、
① =F5+(F5<E5)-E5
の式を入れ、
②「セルの書式設定」の「ユーザー定義」で h:mm;;; と設定し、
下にオートフィル(コピー)しています。

H5セルには、
③ =SUM(G5:G7)
の式を入れ、
④「セルの書式設定」の「ユーザー定義」で h:mm;;; と設定します。

H6セルには、
⑤ =MAX(0,H5-G$3)
または、
⑥ =MAX(0,H5-"8:00")
の式を入れ、
⑦「セルの書式設定」の「ユーザー定義」で h:mm;;; と設定します。

最後に、
⑧H5~H7の範囲を「コピー」し、H8,H11,H14,…へと「貼り付け」していきます。

下の図のように表示したい、ということでしょうか?

G5セルには、
① =F5+(F5&lt;E5)-E5...

  • set********さん

    2020/5/3115:28:41

    mit********さんのように、H列に入れる式を一つにしてしまうなら、

    H5セルには、
    ⑨ =IF(MOD(ROW(1:1),3)=1,SUM(G5:G7),IF(MOD(ROW(1:1),3)=2,MAX(0,H4-G$3),0))
    の式を入れ、
    ⑩「セルの書式設定」の「ユーザー定義」で h:mm;;; と設定してみて下さい。

    但し、残業時間の各セルは、一つ一つフォントを赤色に変える必要があります。

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

質問した人からのコメント

2020/6/5 22:06:19

御二方、沢山のご回答をありがとうございました‼︎
沢山返信をして下さった方にお礼を致します。
本当にありがとうございました‼︎

ベストアンサー以外の回答

1〜1件/1件中

mit********さん

2020/5/3108:40:20

G列に0を表示させていますがG5セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(E5:F5)<>2,"",IF(F5<E5,F5+1,F5)-E5)

H5セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(INDEX(E$5:E100,ROUNDUP(ROW(A1)/3,0)*3-2):INDEX(F$5:F100,ROUNDUP(ROW(A1)/3,0)*3))=0,"",IF(MOD(ROW(A1),3)=1,SUM(INDEX(G$5:G100,ROUNDUP(ROW(A1)/3,0)*3-2):INDEX(G$5:G100,ROUNDUP(ROW(A1)/3,0)*3)),IF(MOD(ROW(A1),3)=2,MAX(0,H4-G$3),"")))

セルの表示形式は時刻から好みの表示にします。

この質問につけられたタグ

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる