プロが教える店舗&オフィスのセキュリティ対策術

エクセルド素人です。質問の意味も的確に伝えたれるかわかりませんが、教えて下さい。

アルバイトの方の出勤簿で、勤務時間のうち早朝・通常・遅出・深夜の時間数を計算できる公式が知りたいです。

早朝(5:00~8:00) 通常(8:00~18:00) 遅出(18:00~22:00) 深夜(22:00~翌日5:00)に設定したいです。

例(1) 7:00~9:30の場合は、早朝[1時間] 通常〔1時間半]
例(2) 16:00~20:00の場合は、通常[2時間] 遅出[2時間]
例(3) 19:00~25:30の場合は、遅出[3時間] 深夜[3時間半]
例(4) 21:00~翌日9:00の場合は、遅出[1時間] 深夜[7時間] 早朝[3時間] 通常[1時間]

必ず同じ時間に出勤するということもなく、日をまたいでしまうこともあるので、とてもややこしく、いろんな式を試してみましたが上手く行きません。ド素人の質問でも、私のやりたいことがわかって下さる方、どうかお助けお願いします。

  A(日付) B(曜日) C(出勤時間) D(終了時間) E(早朝) F(通常) G(遅出) H(深夜)
1  6日     水     7:00       9:30     1:00   1:30
2  7日     木     16:00      20:00           2:00    2:00
3  8日     金     19:00      25:30                  3:00    3:30
4  9日     土     21:00       9:00     3:00   1:00    1:00     7:00

C(出勤時間)とD(終了時間)を入力すると、E(早朝)・F(通常)・G(遅出)・H(深夜)を自動で計算することは可能でしょうか?

A 回答 (5件)

追記。



8日(金)の例のように、深夜00:00を跨いでの勤務があるので、こういう場合は工夫が必要です。

・方法1:勤務時間を2つに別ける。

勤務時間を19:00~24:00、00:00~01:30(25:00)の2つの範囲に分割して計算します。

すると、時間帯区分は、0:00~24:00の区間だけ考えれば良いので

早朝(5:00~8:00)
通常(8:00~18:00)
遅出(18:00~22:00)
深夜1(22:00~24:00)
深夜2(0:00~5:00)

の5つだけ考えれば良い事になります。

早朝=早朝(5:00~8:00)と19:00~24:00が重複する時間数+早朝(5:00~8:00)と00:00~01:30が重複する時間数

通常=通常(8:00~18:00)と19:00~24:00が重複する時間数+通常(8:00~18:00)と00:00~01:30が重複する時間数

遅出=遅出(18:00~22:00)と19:00~24:00が重複する時間数+遅出(18:00~22:00)と00:00~01:30が重複する時間数

深夜=深夜1(22:00~24:00)と19:00~24:00が重複する時間数+深夜1(22:00~24:00)と00:00~01:30が重複する時間数+深夜2(0:00~5:00)と19:00~24:00が重複する時間数+深夜2(0:00~5:00)と00:00~01:30が重複する時間数

・方法2:時間帯を2種類用意する。

勤務終了時間が24:00を超えるので、時間帯区分を「24時を超えてないもの」と「24時を超えたもの」と2種類用意します。

すると、時間帯区分は

深夜1(0:00~5:00)
早朝1(5:00~8:00)
通常1(8:00~18:00)
遅出1(18:00~22:00)
深夜2(22:00~29:00)
早朝2(29:00~36:00)
通常2(36:00~42:00)
遅出2(42:00~46:00)
深夜3(46:00~48:00)

の9種類になります。

早朝=早朝1(5:00~8:00)と19:00~25:30が重複する時間数+早朝2(29:00~36:00)と19:00~25:30が重複する時間数

通常=通常1(8:00~18:00)と19:00~25:30が重複する時間数+通常2(36:00~42:00)と19:00~25:30が重複する時間数

遅出=遅出1(18:00~22:00)と19:00~25:30が重複する時間数+遅出2(42:00~46:00)と19:00~25:30が重複する時間数

深夜=深夜1(0:00~5:00)と19:00~25:30が重複する時間数+深夜2(22:00~29:00)と19:00~25:30が重複する時間数+深夜3(46:00~48:00)と19:00~25:30が重複する時間数

---

方法1だと退勤時刻が24:00より大きい場合、勤務時間を2つに分割する処理が入るので、式が複雑になります。

方法2だと、退勤時刻が「25:30」とかになっていても、そのまま計算できるので、式が簡単になります。

なので、方法2の方が楽に書ける筈です。

>IF関数の入力の仕方も教えていただけると嬉しいのですが、無理でしょうか?!

基本は

=IF(OR(AとDの関係式,BとCの関係式),0,IF(BとDの関係式,B,D)-IF(AとCの関係式,A,C))+IF(OR(AとD’の関係式,BとC’の関係式),0,IF(BとD’の関係式,B,D’)-IF(AとC’の関係式,A,C’))

です。

A、Bには「出勤時刻、退勤時刻」が入ったセルを書きます。

C、Dには、深夜なら「0:00、5:00」など、1つ目の時間帯区分を書きます。

C'、D'には、深夜なら「22:00、29:00」など、2つ目の時間帯区分を書きます(深夜は3つ目もあるので注意)

「関係式」は、前の回答にある通りの「大小比較をする式」を書きます。
    • good
    • 0
この回答へのお礼

chie65535様
 昨日からご丁寧な回答ありがとうございます。24:00をまたぐと、とてもややこしくなるので、0:00で一旦区切って考えれば良いというヒントは、大変役に立ちました。基本の式も教えて頂き、ありがとうございました。参考にさせてもらいながら、今から格闘してみたいと思います。

お礼日時:2014/11/07 14:44

その2です。

「エクセル(関数) ド素人にお助け願います」の回答画像4
    • good
    • 0

添付図が縮小されて字が読みづらいので2つに分割しました。



その1です。
「エクセル(関数) ド素人にお助け願います」の回答画像3
    • good
    • 0

A~B間、C~D間の2つの「区間」があった時、その2つの区間の「重なり具合」は、添付図のようになります。



この時、必ず「A<B」「C<D」とします。

AとC、AとD、BとC、BとDのそれぞれの関係と、重なり具合を色分けすると「どの関係を調べれば、どう重なっているかが判る」ようになります。

A~B間を「出勤から退勤まで」、C~D間を「早朝、通常、遅出、深夜1、深夜2」とした時、AとC、AとD、BとC、BとDのそれぞれの関係を調べると「それぞれの重なり具合」が判ります。

なお「深夜」が0時(24時)を跨ぐと面倒なので、

深夜1=22:00~24:00
深夜2=00:00~05:00

として別々に考え、最後に「足し算」します。

添付図で「より後」と「等しい」が同じ色になっているので、その部分は「前でない」と考えます。

「より前」と「等しい」が同じ色になっている部分も「後でない」と考えます。

重なりが「AからD」になっている部分の「重なってる時間数」は「D-A」の引き算で計算できます。

その他の重なりも、同様の引き算で計算できます。

こう見ると「上のなし」「AからDまで」「AからBまで」「CからDまで」「CからBまで」「下のなし」の6種類あると判ります。

6種類ですが「なし」「から」「まで」を別々に考えれば

「なし」または「なし以外」
「Aから」または「Cから」
「Dまで」または「Bまで」

の3つを判断すれば良いと判ります。

『「なし」または「なし以外」』は「AとDの関係」と「BとCの関係」で判断できます。

『「Aから」または「Cから」』は「AとCの関係」で判断できます。

『「Dまで」または「Bまで」』は「BとDの関係」で判断できます。

あとは「上記の通りにIF関数を書くだけ」です。
「エクセル(関数) ド素人にお助け願います」の回答画像2
    • good
    • 0
この回答へのお礼

chie65535様 ご丁寧に説明して下さったり、画像を添付して頂き、ありがとうございます。 今は携帯からしか見られず、画像が見られなかったので、明日会社のパソコンで確認させてもらいます。 IF関数の入力の仕方も教えていただけると嬉しいのですが、無理でしょうか?!

お礼日時:2014/11/06 20:24

一つのセルで色々な条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。


なるべく平易な分かりやすい数式となるように、各時間帯別に、勤務開始時刻、勤務終了時刻、休憩開始時刻、休憩終了時刻を区切り、通常勤務時間、通常残業時間、深夜残業時間、合計勤務時間を計算しています。

https://sites.google.com/site/kinnmujikannnokeis …
    • good
    • 0
この回答へのお礼

aokii様 早々の回答ありがとうございます。パソコンに不慣れなもので、アドレスにアクセスさせて頂きましたが、ファイルを開くことが出来ず、確認することが出来ませんでした。申し訳ありません。

お礼日時:2014/11/06 16:49

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!