- 締切済み
エクセル(関数) ド素人にお助け願います!
エクセルド素人です。質問の意味も的確に伝えたれるかわかりませんが、教えて下さい。 アルバイトの方の出勤簿で、勤務時間のうち早朝・通常・遅出・深夜の時間数を計算できる公式が知りたいです。 早朝(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(深夜)を自動で計算することは可能でしょうか?
- みんなの回答 (5)
- 専門家の回答
みんなの回答
- chie65536(@chie65535)
- ベストアンサー率44% (8755/19867)
追記。 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つ目もあるので注意) 「関係式」は、前の回答にある通りの「大小比較をする式」を書きます。
- chie65536(@chie65535)
- ベストアンサー率44% (8755/19867)
- chie65536(@chie65535)
- ベストアンサー率44% (8755/19867)
- chie65536(@chie65535)
- ベストアンサー率44% (8755/19867)
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関数を書くだけ」です。
お礼
chie65535様 ご丁寧に説明して下さったり、画像を添付して頂き、ありがとうございます。 今は携帯からしか見られず、画像が見られなかったので、明日会社のパソコンで確認させてもらいます。 IF関数の入力の仕方も教えていただけると嬉しいのですが、無理でしょうか?!
- aokii
- ベストアンサー率23% (5210/22062)
一つのセルで色々な条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。 なるべく平易な分かりやすい数式となるように、各時間帯別に、勤務開始時刻、勤務終了時刻、休憩開始時刻、休憩終了時刻を区切り、通常勤務時間、通常残業時間、深夜残業時間、合計勤務時間を計算しています。
お礼
aokii様 早々の回答ありがとうございます。パソコンに不慣れなもので、アドレスにアクセスさせて頂きましたが、ファイルを開くことが出来ず、確認することが出来ませんでした。申し訳ありません。
お礼
chie65535様 昨日からご丁寧な回答ありがとうございます。24:00をまたぐと、とてもややこしくなるので、0:00で一旦区切って考えれば良いというヒントは、大変役に立ちました。基本の式も教えて頂き、ありがとうございました。参考にさせてもらいながら、今から格闘してみたいと思います。