[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『完全不規則な就業時間の時間計算』（独学Excel）
完全不規則な就業時間帯の時間計算をしたく様々なサイトやヘルプを調べましたが、
まったくもって上手く進まないのでご教授頂けると幸いです。
※時間の反映については24時制での反映です。
手動入力箇所
・就業時間…………………………A4
・終了時間…………………………B4
・休憩取得時間（通常時間帯）…C4
・休憩取得時間（深夜時間帯）…D4
――――――――――――――――――
以下関数
・通常時間…………………………E4
・深夜時間…………………………F4
・深夜残業…………………………G4
・通常残業…………………………H4
・実働時間…………………………I4
深夜時間のみの算出式であればなんとか分かるようになったのですが、
その時間が深夜残業に絡んでいる場合や、
その他の通常時間、通常残業の出し方で立ち往生しています。
・深夜時間
=IF(COUNT(A4:B4)<2,"",
MAX(0,MIN("5:00",(B4<A4)+B4)-A4)+
MAX(0,MIN((B4<A4)+B4,"29:00")-MAX(A4,"22:00")))
また、手動入力の休憩時間に誤りがある場合（以下1〜3）は
実働時間に取得休憩時間が正しくない旨を反映させたいと思っています。
1、実働6時間以下の場合（休憩不要）
2、実働6時間を超え8時間以下の場合（45分）
3、実働8時間を超える場合（1時間以上）
以上となります。
面倒だとは思いますが、ご存知もしくはお分かりであれば
お力を貸して頂けると幸甚です。
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
深夜時間計算ができたならば、通常時間はそれより簡単に思いますよ？ B列（A列より小さければ24時間加算）と22:00を比較し、小さい方とA列の差を取るだけでしょう。 ただし、5:00から始業時間の間に開始と終了が含まれてしまう場合を考えると少し複雑になりますが、大小比較すれば判定できますよね？
（???） 2017/09/25(月) 11:07
就業時間については各雇用形態上全てばらばらになります。
例）
9:00〜18:00（実働8時間）
14:00〜0:00（実働10時間）
21:00〜7:00（実働9時間）など
休憩時間については就業時間がそれぞれ異なるため、
通常時間帯での勤務で9時間拘束、8時間実働の場合の休憩（通常時間で1時間取得）や
深夜が絡む時間帯での勤務の場合に深夜帯で休憩を取得する場合があるので
セルを2つに分けています。
大小の差分を引くだけであればできるのですが、
差分を引いた際にそれが実働8時間を超えていた場合や
その時間が通常時間帯の残業なのか深夜帯の残業なのかがうまくいかずに質問させていただきました。
（独学Excel） 2017/09/25(月) 13:13
例えば、通常が8時間労働ならば、コアタイムの考え方がよく使われます。 コアがなくとも、昼の勤務時間から8時間までは通常で、それ以外は残業。深夜分は別計算とかにすれば良いでしょう。 この考えならば、深夜とそれ以外に分けてから、8時間までは通常勤務。それを超えた分を残業と計算します。
式の作り方は、既に深夜残業の計算で使われていますが、開始と終了の大小が逆転していたら24時間（=1）を加算です。ここさえ判っていれば、後はルールに合わせて式に条件追加していくだけです。休息時間は、何回休んでも合計分をそれぞれ1セルずつで記述のようですから、深夜とそれ以外から引けば良いですよね？ あとは8時間と比べて小さいほうが通常勤務で、余った分が残業です。
（深夜を含んでいても、8時間以下ならば通常勤務とするのかどうか、これもルール次第です）
（???） 2017/09/25(月) 13:37
勤怠管理に使うツールでは無いので、
開始時間より前に就業した場合のことは考えていません。
単純に指定した就業時間の内訳が分かれば良いです。
頭の中では「こうしてこうすれば」はわかるのですが、
いざ式にしようとするとIFだらけになったり、
MIN、MAXがごちゃごちゃになってしまいなかなか進まないのです。。。
（独学Excel） 2017/09/25(月) 20:09
＞いざ式にしようとするとIFだらけになったり、 ＞MIN、MAXがごちゃごちゃになってしまいなかなか進まないのです。。。
まぁ、旨く説明できない人は少なくないです。
でも、具体的なケースでは、今までなんとか計算して来たんですよね？(仕事なんですから）
その実例を出来るだけ沢山上げて貰えませんか？
特に「このケースは苦労したなぁ」と思うものを中心に10件くらい上げることは出来ませんか？ 今、新たに考える訳でもなく、過去のデータから拾ってくればいいので、比較的楽じゃないですか？
それを手掛かりにこっちでどんなルールなのか考えてみますよ。
こんなレイアウトで示して頂くと分かり易くていいのですけど・・ ↓ <具体例 & 正解> 行 ____A____ ____B____ _____C_____ _____D_____ ____E____ ____F____ ____G____ ____H____ ____I____ 2 通常時間帯 深夜時間帯 3 就業時間 終了時間 休憩取得 休憩取得 通常時間 深夜時間 深夜残業 通常残業 実働時間 4 8:00 17:00 0:00 9:00 5 14:00 0:00 2:00 10:00 6 21:00 7:00 7:00 10:00 ： ： （半平太） 2017/09/25(月) 22:27
返信が遅くなり申し訳ございません。
現状は手作業による反映の為、関数を使用して運用はしておりません。
入力者以外の者による後確は行っていますが、
後確が遅くなってしまった場合や
入力者の入力ミス・後確も誤っていた事に気付かずに
そのままの状態になってしまっていることがあったため、
関数を使用してどうにかできないかと思っていた次第です。
使用する関数については単純な四則演算や
文字の操作・検索等にしか使用していない為、
あまり苦労したというものは。。。
レイアウトについては失礼致しました。
記載頂いた内容で恐縮でございますが、レイアウトについては示して頂いた内容と相違はありません。
補足として、
?@始めに質問した際に誤っておりましたが、
A3の就業時間は正しくは開始時間になります。
?AA4〜D4までが手動での入力となり、E4以降に関数を入れる予定です。
?B休憩時間については、取得する合計の時間をC4もしくはD4へ反映します。
（独学Excel） 2017/09/27(水) 12:04
＞あまり苦労したというものは。。。
はれ？ 苦労ないんですかぁ。
私は、ちょっと力が抜けてしまったので、パスします。 済みませーん m(__)m
（半平太） 2017/09/27(水) 12:57
もちろん関数の使い方を覚える等の苦労はありますが、
関数自体は好きなのでこれと言って苦と感じたことがなかっただけです。
先にも述べた通り、使う関数はせいぜい30個も無いと思いますし。
もう少し自分でも調べて頑張ってみたいと思います。
ありがとうございました。
（独学Excel） 2017/09/27(水) 14:30
独学Excelさんは説明も読解力も少し、足りないのかもしれませんね。 半平太さんは 今までの経験から、 実例を10個ほど拾って 表を埋めてください って言ったのです。 レイアウトがあってますか？ なんて聞いてませんよ。
「苦労」に関しても 関数について苦労してるか なんて誰も聞いてないです。 間違いを探すのに苦労した事例 を聞いているのです。
がんばる方向を少しかえてみませんか？ まずは事例を10個出してみてください。
（稲葉） 2017/09/27(水) 16:44
指摘を受けて気づきました。
自分の話が明後日の方向でお恥ずかしい限りです。
半平太さん
遅いかもしれませんが、頂いたご質問に答えさせていただきます。
行 ＿＿A＿＿ ＿＿B＿＿ ＿＿C＿＿ ＿＿D＿＿ ＿＿E＿＿ ＿＿F＿＿ ＿＿G＿＿ ＿＿H＿＿ ＿＿I＿＿ 2 休憩時間 3 開始時間 終了時間 通常時間 │ 深夜時間 通常時間 深夜時間 深夜残業 通常残業 実働時間 4 9:00 12:00 0:00 0:00 3:00 0:00 0:00 0:00 4:00 5 23:00 4:00 0:00 0:00 0:00 5:00 0:00 0:00 5:00 6 18:00 1:00 0:45 0:00 3:25 3:00 0:00 0:00 6:25 7 22:00 5:00 0:00 0:45 0:00 6:25 0:00 0:00 6:25 8 3:00 10:00 0:45 0:00 4:25 2:00 0:00 0:00 6:25 9 16:00 1:00 1:00 0:00 5:00 3:00 0:00 0:00 8:00 10 0:30 9:30 0:30 0:30 4:00 4:00 0:00 0:00 8:00 11 7:00 17:00 1:00 0:00 8:00 0:00 0:00 1:00 9:00 12 11:00 23:00 1:00 0:00 8:00 0:00 1:00 2:00 11:00 13 19:00 6:00 0:00 1:00 3:00 5:00 1:00 1:00 10:00
※補足
4行目、5行目→休憩を含まない短時間就業
6行目〜8行目→45分の休憩を含む実働8時間以下の就業
9行目〜13行目→1時間の休憩を含む実働8時間以上の就業
開始〜終了が実働8時間、且つ1日（24時）の中で納まるのであればそれほど手間ではありませんが、
実働6超え〜8時間以下の休憩が45分必要な場合の休憩の入力ミスや
日付を跨ぐ場合（深夜残業＋通常残業など）はどうしても間違えやすくなってしまいます。
（独学Excel） 2017/09/28(木) 12:28
稲葉さん
軌道修正いただき、有難うございます。
私はお手上げだったので、逃げちゃいました。
独学Excelさん
ほとんど分かりました。以下確認です。
１． 4行目の実働時間が、3:00でなく、4:00なのはどうしてですか？（単なるミス？）
２. (1) 6行目の通常時間が、3:15でなく、3:25なのはどうしてですか？（単なるミス？） (2) 6行目の実働時間が、6:15でなく、6:25なのはどうしてですか？（単なるミス？）
３. (1) 7行目の深夜時間が、6:15でなく、6:25なのはどうしてですか？（単なるミス？） (2) 7行目の実働時間が、6:15でなく、6:25なのはどうしてですか？（単なるミス？）
４. 8行目の通常時間が、4:15でなく、4:25なのはどうしてですか？（単なるミス？）
適正な休憩時間をチェックする件ですが、要件が少し不明瞭です。
下記の勤務時間だと、休憩取得はそれぞれ何分が適正になりますか？（労基法はこの際、考慮外としますけど・・）
通常時間帯
開始時間 終了時間 休憩取得 実働時間(休憩時間考慮前)
9:00 14:00 ？ 5:00
9:00 14:59 ？ 5:59
9:00 15:00 ？ 6:00
9:00 15:01 ？ 6:01
9:00 15:59 ？ 6:59
9:00 16:00 ？ 7:00
9:00 16:01 ？ 7:01
9:00 16:59 ？ 7:59
9:00 17:00 ？ 8:00
9:00 17:01 ？ 8:01
9:00 17:59 ？ 8:59
9:00 18:00 ？ 9:00
9:00 18:01 ？ 9:01
（半平太） 2017/09/28(木) 18:28
大変失礼いたしました。
1〜4の件についてはこちらのミスになります。
ご指摘頂いた通りですが、念のため正しいものを再記載致します。
※併せて、補足内容にも訂正、及び追加補足がございます。
行 ＿＿A＿＿ ＿＿B＿＿ ＿＿C＿＿ ＿＿D＿＿ ＿＿E＿＿ ＿＿F＿＿ ＿＿G＿＿ ＿＿H＿＿ ＿＿I＿＿ 2 休憩時間 3 開始時間 終了時間 通常時間 │ 深夜時間 通常時間 深夜時間 深夜残業 通常残業 実働時間 4 9:00 12:00 0:00 0:00 3:00 0:00 0:00 0:00 3:00 5 23:00 4:00 0:00 0:00 0:00 5:00 0:00 0:00 5:00 6 18:00 1:00 0:45 0:00 3:15 3:00 0:00 0:00 6:15 7 22:00 5:00 0:00 0:45 0:00 6:15 0:00 0:00 6:15 8 3:00 10:00 0:45 0:00 4:15 2:00 0:00 0:00 6:15 9 16:00 1:00 1:00 0:00 5:00 3:00 0:00 0:00 8:00 10 0:30 9:30 0:30 0:30 4:00 4:00 0:00 0:00 8:00 11 7:00 17:00 1:00 0:00 8:00 0:00 0:00 1:00 9:00 12 11:00 23:00 1:00 0:00 8:00 0:00 1:00 2:00 11:00 13 19:00 6:00 0:00 1:00 3:00 5:00 1:00 1:00 10:00 [訂正] ※補足 誤）9行目〜13行目→1時間の休憩を含む実働8時間以上の就業 正）9行目〜13行目→1時間の休憩を含む実働8時間を超える就業 [追加補足] 実働8時間以下の場合、厳密には45分の休憩で事が足りますが、 社内上1時間の休憩で計算することが多いです。 45分の休憩で処理することもありますが、極稀なケースです。 ※実際の運用は労基法で定められている必要休憩時間（以上でも可）が取れていれば問題ありません。 →9:00〜20:00（11時間拘束9時間実働/2時間休憩）など
休憩時間について確認がございます。
労基法を考慮しない場合の考え方がいまいちピンとこないのですが、
開始時間から指定の実働時間を働きたい場合の休憩時間ということでしょうか。
■具体的に 開始時間 終了時間 休憩取得 実働時間(休憩時間考慮前) 9:00 17:01 ？ 8:01 9時から開始して8時間1分働きたい場合は 9:00 18:01 1:00 8:01 となりますが、このような考え方で良いのでしょうか。
相違があるようであれば別途ご指示願います。
（独学Excel） 2017/09/28(木) 20:19
＞労基法を考慮しない場合の考え方がいまいちピンとこないのですが、
済みません。m(__)m 労基法に則ったルールになっているのであれば、忘れてください。
・・が、この記述でまた不明瞭になりました。(実働8時間以内は、45分じゃないと「正しくない」旨を反映させるんじゃないですか？） ↓ ＞ 実働8時間以下の場合、厳密には45分の休憩で事が足りますが、 ＞ 社内上1時間の休憩で計算することが多いです。 ＞ 45分の休憩で処理することもありますが、極稀なケースです。
＞ ■具体的に ＞ 開始時間 終了時間 休憩取得 実働時間(休憩時間考慮前) ＞ 9:00 17:01 ？ 8:01 ＞ 9時から開始して8時間1分働きたい場合は ＞ 9:00 18:01 1:00 8:01 ＞ となりますが、このような考え方で良いのでしょうか。
実働8時間以下と言うことは、休憩は労働時間の途中で与えることになっていますから、 休憩込みの拘束時間は、MAX「8:45」ですよね？
こちらとしては、境目になる拘束時間が何であるか、 具体的に示していただくだけでいいんですけども・・・
<私の考える境目> 行 ____A____ ____B____ ______C______ __________K__________ _________ L _________ 4 開始時間 終了時間 休憩取得(通) 拘束時間(休憩考慮前) 実働時間(休憩考慮後) 5 9:00 15:00 0:00 6:00 6:00 6 9:00 15:01 0:45 6:01 5:16 7 9:00 17:45 0:45 8:45 8:00 8 9:00 17:46 1:00 8:46 7:46
上の通りとして、6行目、7行目の休憩が0:45を超えていたり(例えば1:00）、 過少だったり(例えば0:40）したら、 「正しくない」にするんですか？
（半平太） 2017/09/28(木) 22:37
分かりづらくてすみません。
＞・・が、この記述でまた不明瞭になりました。(実働8時間以内は、45分じゃないと「正しくない」旨を反映させるんじゃないですか？） 45分ぴったりではなくとも、45分以上の休憩が取れているのであれば「正しい」で構いません。
＞実働8時間以下と言うことは、休憩は労働時間の途中で与えることになっていますから、 ＞休憩込みの拘束時間は、MAX「8:45」ですよね？ おっしゃる通りです。
実働時間が8:00:00ピッタリに終わる、
もしくはそれ以前に業務が終了することが明確な場合に於いてのみ、
45分休憩で処理する場合があります。（終了時刻から10分前には必ず業務が終了するなど）
ですが、契約を巻いた後、実態が8:00:00を超えていた場合（極端ですが8:00:01など）は
後々面倒なことになるので、余裕をもって1時間休憩で処理をすることが多いです。
また、休憩については拘束時間に対して何時間働くのかによって決めています。
例で出して頂いた内容に沿ってお答えすると、
行 ____A____ ____B____ ______C______ __________K__________ _________ L _________
4 開始時間 終了時間 休憩取得(通) 拘束時間(休憩考慮前) 実働時間(休憩考慮後)
5 9:00 15:00 0:00 6:00 6:00
6 9:00 15:01 0:45 6:01 5:16
7 9:00 17:45 0:45 8:45 8:00
8 9:00 17:46 1:00 8:46 7:46
※5行目については説明は不要かと思いますので割愛します。
・6行目（9:00〜15:01）
→あくまでも6:01「拘束」なだけであり、実働時間が6:00なのであれば休憩は0:01となります。
そうではなく、本当に6:01「実働」をするのであれば45分の休憩が必要になるので、
開始時間を45分前倒しにするのか、（8:15〜15:01）
終了時間を45分後ろ倒しにするなどの調整を行っています。（9:00〜15:46）
・7行目（9:00〜17:45）
先に述べた通り、8:00:00ピッタリに終了、もしくはそれ以前に終了することが明確なのであれば、
45分休憩で問題ありません。
・8行目
→6行目と同様です。
あくまでも8:46「拘束」なだけであり、実働時間が8:00なのかそうでないのかで休憩が異なります。
＞上の通りとして、6行目、7行目の休憩が0:45を超えていたり(例えば1:00）、 過少だったり(例えば0:40）したら、
「正しくない」にするんですか？
労基法上、取らせる必要のある休憩時間は決まっていますが、
逆に、それ以上の休憩を取らせても何ら問題はないので、
6行目が契約上9:00〜15:01/5:16実働なのであれば、休憩を取らせる必要はありませんが0:45の休憩を取得しても「正しい」となり、
8行目が契約上9:00〜17:46/7:46実働なのであれば、0:45以上の休憩が取れているので1:00の休憩であっても「正しい」となります。
これが、実働時間に対して必要な休憩が取れていない場合に於いてのみ、「正しくない」と表記をしたいです。
行 ＿＿A＿＿ ＿＿B＿＿ ＿＿C＿＿ ＿＿D＿＿ ＿＿E＿＿ ＿＿F＿＿ ＿＿G＿＿ ＿＿H＿＿ ＿＿I＿＿
2 休憩時間
3 開始時間 終了時間 通常時間 │ 深夜時間 通常時間 深夜時間 深夜残業 通常残業 実働時間
4 9:00 15:01 0:45 0:00 5:16 0:00 0:00 0:00 5:16
5 5:00 23:00 10:00 0:00 7:00 1:00 0:00 0:00 8:00
6 9:00 15:01 0:00 0:00 6:01 0:00 0:00 0:00 ※休憩時間が正しくありません。
↑
6:01拘束6:01実働になってしまっているため。
7 5:00 23:00 0:45 0:00 8:00 0:00 1:00 8:15 ※休憩時間が正しくありません。
↑
18:00拘束17:15実働になってしまっているため。
※36上こんな時間はあり得ませんが。
（独学Excel） 2017/09/29(金) 01:59
＞実働時間に対して必要な休憩が取れていない場合に於いてのみ、「正しくない」と表記をしたいです。
分かりました。
次のいずれかが出力されます → ｢休憩時間帯相違｣ ｢休憩過少｣ ｢不整合あり｣
＞ 7 5:00 23:00 0:45 0:00 8:00 0:00 1:00 8:15 ※休憩時間が正しくありません。
5:00 23:00 0:45 0:00 8:15 0:00 1:00 8:00 休憩過少
↑
そこのところは、私の作りでは、となります。残業開始時刻は「就業時刻+9:00」で決め打ちしたため(※)。
(※)ここを余り厳密に考えると、 休憩時間を早いうちに取得したのか、終業間際に取得したのか、 一括で取得したのか、分割で取得したのか と言う詳細が分からないデータでは、処理が非常に面倒になるためです。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 沢山の数式および所要データをこの掲示板から転記するのは面倒と思いますので、 後記マクロでそれらを自動的に埋めちゃってください｡
<自動埋込み手順>
↓
新規シートの「シート見出し」を右クリックして、「コードの表示(V)」を選ぶと
画面中央に白いエリアが表れます。(VBE画面です）
その白いエリアに後記マクロをコピぺし、F5キーを押下してください (すると、マクロ「onlyOnce」が実行され、自動的に所要データと数式が入力されます）
※実行は１回だけですので、終わったら「Ctrl+Z」でコードを消去し、Alt+F11 でエクセルに戻って下さい
’<貼り付けるマクロコード>
Private Sub onlyOnce()
Rem 結合状態を処理
Range("M2:P2").Merge
Range("S2:V2").Merge
Range("X2:Z2").Merge
Range("AB2:AD2").Merge
Rem 生データのセルをまとめて処理
Range("M1,C4:D5,D6,C7,D8:D9,D11:D12,C13:C14").Value = 0
Range("R1").Value = 2
Range("C2").Value = "通常時間帯"
Range("D2").Value = "深夜時間帯"
Range("M2").Value = "時間帯毎拘束時間"
Range("S2").Value = "残業時間帯毎拘束時間"
Range("X2").Value = "昼間休憩"
Range("AB2").Value = "深夜休憩"
Range("A3").Value = "就業時間"
Range("B3").Value = "終了時間"
Range("C3:D3").Value = "休憩取得"
Range("E3").Value = "通常時間"
Range("F3").Value = "深夜時間"
Range("G3,T3").Value = "深夜残業"
Range("H3").Value = "通常残業"
Range("I3,K3").Value = "実働時間"
Range("M3").Value = "早朝"
Range("N3,P3").Value = "昼間"
Range("O3").Value = "深夜"
Range("Q3").Value = "深夜翌"
Range("S3").Value = "昼間残業"
Range("U3").Value = "昼間残業(翌日)"
Range("V3").Value = "深夜残翌"
Range("X3").Value = "昼間普通"
Range("Y3").Value = "昼普休憩"
Range("Z3").Value = "昼残休憩"
Range("AB3").Value = "深夜普通"
Range("AC3").Value = "深普休憩"
Range("AD3").Value = "深残休憩"
Range("A4,A19").Value = 0.375
Range("B4").Value = 0.5
Range("A5,B12,B18,B20").Value = 0.958333333333333
Range("B5").Value = 0.166666666666667
Range("A6").Value = 0.75
Range("B6,B9:C9,C11:C12,D13,B14,D17:D18").Value = 4.16666666666667E-02
Range("C6,D7,C8,D15,C16,C20").Value = 0.03125
Range("A7").Value = 0.916666666666667
Range("B7,A20").Value = 0.208333333333333
Range("A8").Value = 0.125
Range("B8").Value = 0.416666666666667
Range("A9").Value = 0.666666666666667
Range("A10,C10:D10").Value = 2.08333333333333E-02
Range("B10").Value = 0.395833333333333
Range("A11").Value = 0.291666666666667
Range("B11").Value = 0.708333333333333
Range("A12").Value = 0.458333333333333
Range("A13").Value = 0.791666666666667
Range("B13").Value = 0.25
Range("A14:A15").Value = 0.583333333333333
Range("D14").Value = 5.55555555555556E-02
Range("B15").Value = 0.96875
Range("A16").Value = 0.569444444444444
Range("B16").Value = 0.979166666666667
Range("D16").Value = 1.04166666666667E-02
Range("A17").Value = 0.541666666666667
Range("B17").Value = 0.9375
Range("A18").Value = 0.993055555555555
Range("B19").Value = 0.625694444444444
Rem 数式セルをまとめて処理
Range("N1").FormulaR1C1Local = "=TEXT(5/24,""[h]:m"")*1"
Range("O1").FormulaR1C1Local = "=TEXT(22/24,""[h]:m"")*1"
Range("P1").FormulaR1C1Local = "=TEXT(29/24,""[h]:m"")*1"
Range("Q1").FormulaR1C1Local = "=TEXT(46/24,""[h]:m"")*1"
Range("N1:Q1").Value = Range("N1:Q1").Value
Range("E4:E20").FormulaR1C1Local = "=TEXT(RC[19]-RC[20],""[h]:m"")*1"
Range("F4:F20").FormulaR1C1Local = "=TEXT(RC[22]-RC[23],""[h]:m"")*1"
Range("G4:G20").FormulaR1C1Local = "=TEXT(RC[13]+RC[15]-RC[23],""[h]:m"")*1"
Range("H4:H20").FormulaR1C1Local = "=TEXT(RC[11]+RC[13]-RC[18],""[h]:m"")*1"
Range("I4:I20").FormulaR1C1Local = "=IF(COUNTIF(RC[-4]:RC[-1],#VALUE!),""休憩時間帯相違"",IF(TEXT(SUM(RC[-6]:RC[-5]),""[h]:m"")*1<LOOKUP(RC[2]-0.0001,{0,""6:00"",""8:00"";0,""0:45"",""1:00""}*1),""休憩過少"",IF(ABS(SUM(RC[-4]:RC[-1])-RC[2])>0.0001,""不整合あり"",RC[2])))"
Range("K4:K20").FormulaR1C1Local = "=TEXT(MAX(0,RC[-9]+(RC[-9]<RC[-10])-RC[-10]-SUM(RC[-8]:RC[-7])),""[h]:m"")*1"
Range("M4:Q20").FormulaR1C1Local = "=MAX(0,MIN(R1C[1],RC2+(RC2<RC1))-MAX(R1C,RC1))"
Range("S4:V20").FormulaR1C1Local = "=MAX(0,MIN(R1C[-4],RC2+(RC2<RC1))-MAX(R1C[-5],RC1+""9:00""))"
Range("X4:X20").FormulaR1C1Local = "=TEXT(RC[-10]+RC[-8]-RC[-5]-RC[-3]+0.0001,""[h]:m"")*1"
Range("Y4:Y20").FormulaR1C1Local = "=MIN(RC[-1],RC[-22]*1)"
Range("Z4:Z20").FormulaR1C1Local = "=TEXT(RC[-23]-RC[-1]+0.0001,""[h]:m"")*1"
Range("AB4:AB20").FormulaR1C1Local = "=TEXT(RC[-15]+RC[-13]+RC[-11]-RC[-8]-RC[-6]+0.0001,""[h]:m"")*1"
Range("AC4:AC20").FormulaR1C1Local = "=MIN(RC[-1],RC[-25]*1)"
Range("AD4:AD20").FormulaR1C1Local = "=TEXT(RC[-26]-RC[-1]+0.0001,""[h]:m"")*1"
Rem 標準外書式セルをまとめて処理
Range("F1,M1:R1,T1:V1,S2:V2,L4:L18").NumberFormatLocal = "[h]:mm;@"
Range("A4:I18,K4:K20,M4:AD18,E19:I20,M19:Q20,S19:V20,X19:Z20,AB19:AD20").NumberFormatLocal = "[<=0.0001]"""";[>0.0001][h]:mm;"
Range("A19:B19,A20:D20").NumberFormatLocal = "h:mm"
Rem 塗りつぶしセルをまとめて処理
Range("M1:R1,A4:B20,I4:I20").Interior.ColorIndex = 6
Range("M2:P2,S2:U2,X2:Z2,AB2:AD2").Interior.ColorIndex = 24
End Sub
’注「0:00」は「空白で表示」の書式設定になっています。「0:00」が見たい場合は、セルの書式を変更してください。
（半平太） 2017/09/29(金) 10:11
MS クエリ使用例
新規シートを2枚追加して、それぞれ 新規追加分、削除分 等にする(シート名は何でもよい) 1) Alt + d d n 2) [データソースの選択] - [Excel Files*] - OK 3) [ブックの選択] - 該当ブックを選択 - OK 4) [ウィザード - 列の選択] - キャンセル 5) Microsoft Query を使ってこのクエリの編集を続けますか? - はい 6) [テーブルの追加] - 該当シートを選択 - 追加 - 閉じる 7) メニューバーの SQLアイコン をクリック 8) 下記をペースト
select * from `sheet2$` left join `sheet1$` on `sheet2$`.電話番号 = `sheet1$`.電話番号 where `sheet1$`.電話番号 is null
9) SQLアイコンの左にある矢印の付いた扉アイコンをクリック 10) 抽出先を指定して OK
上記で新規追加分
同じことを 8)のコードを下記に変えれば削除分
select * from `sheet1$` left join `sheet2$` on `sheet1$`.電話番号 = `sheet2$`.電話番号 where `sheet2$`.電話番号 is null
Sheet1, Sheet2 のデータが変更された場合は [データ] - [全て更新]
あくまでも電話番号を基準にデータを照合。 （seiya） 2017/09/29(金) 10:16
す、す、す、すげー！！！！
式の作成、さらにはコードまでご用意いただき、
至れり尽くせりのご説明ありがとうございます！！
そのまま作って頂いたものを使用したいところですが、
手順や式の作り方などを理解したうえで運用したいと思います。
1点確認があります。
・I4 IF(ABS(SUM(E4:H4)-K4)>0.0001,"不整合あり",K4)
不整合が出るパターンは、M列以降の式が変更されなければ基本出ない認識であってますでしょうか。
（独学Excel） 2017/09/29(金) 14:17
＞ ・I4 ＞ IF(ABS(SUM(E4:H4)-K4)>0.0001,"不整合あり",K4) ＞不整合が出るパターンは、M列以降の式が変更されなければ基本出ない認識であってますでしょうか。
このチェックは念の為に入れたものです。
今回、かなり複雑な構成であり、ロジックバグが入る可能性は否定できないです。
この為、以下の二つの合致を確認し、安全弁とするものです。
(1)「時間帯別に算出した労働時間の合計」の総労働時間
(2)「総拘束時間から休憩合計を差し引いた」総労働時間
※「0.0001」以下の差は許容する。(10秒未満は許容) 小数演算誤差で0.1秒程度の不一致はあり得るので、それをスルーさせるもの。
（半平太） 2017/09/29(金) 15:58
了解しました。
ご丁寧に色々とありがとうございました。
まだまだ勉強することが多いので、習熟したら次は自分でも1から考えて作ってみたいと思います。
本当にありがとうございました。
（独学Excel） 2017/09/29(金) 17:25
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.