ｴｸｾﾙでﾀｲﾑｶｰﾄﾞの集計がしたいのですが

先任の退職につき､ﾀｲﾑｶｰﾄﾞの集計業務をすることになりました｡
私の会社は勤怠状況が複雑な零細企業ですが､従業員は26名ほどいます｡
遅刻･早退･中間外出､残業･深夜･休日出勤 等が頻繁にあります｡
ﾀｲﾑｶｰﾄﾞの計算程度に考えていましたが､複雑な出勤状況もあり､計算のミスが多く､何より集計時間に時間が掛りすぎてしまい､他の業務にも影響が出始めています｡ｴｸｾﾙにて集計表を作成しようとしましたが､1ヶ月経ってもうまく表示できません｡どうかお助けください｡

条件
始業8:00 終業17:00 休憩10:00~10:10 12:00~13:00 15:00~15:10 深夜22:00~29:00
打刻時間　出勤　8:00前 30分単位　所定時間内 5分単位　12:00~13:00 30分単位
　　　　　　　退勤　所定時間内 5分単位　12:00~13:00 30分単位　17:00以降 5分単位
所定時間 7時間40分　残業は30分から発生(17:29退社は残業0)　1日の区切り 5:00から24時間｡
法定内労働時間
　平日： 8:00~19:00勤務は 法定内 0:20・法定外 1:40
　半給：13:00~21:00勤務は 法定内 3:50・法定外 0:10
　遅刻：13:00~21:00勤務は 法定内 3:50・法定外 0:10・不就労 3:50）早退･外出も同じ考えです｡

A1 日付（事前入力）
B1 曜日（事前入力）
C1 当日の勤務種別（空白=平日・1＝所定休日・2＝法定休日・3＝半日有給・4＝1日有給）（入力）
D1 始業時刻（入力）
E1 途中出時刻（入力）
F1 途中入時刻（入力）
G1 終業時刻（入力）
H1 法定内労働時間（平日 所定労働時間を除く､実労働時間が8時間を超えない労働時間）
I 1 法定外労働時間（平日 5時から22時の間で所定時間を省いた労働）+（所定休日労働）
J1 深夜労働時間(22:00~29:00の間の労働)
K1 法定休日労働時間(法定休日は日曜なので､日曜日の労働時間)
L1 不就労時間（半日有給は含まない｡所定時間内での不就労時間）

まだ私も完全に把握していないほど複雑で､無理なのかもしれませんが､どうかよろしくお願いします｡

質問者からの補足コメント

• 

  ご教授にあたり製作における表示､情報､希望打込作業等を補足します｡
  最終表示項目
  ①従業員　氏　　名　横方向に25名ほど
  ②法定内　残業時間　実働の1日の法定労働時間時間(8時間)を超えない所定労働時間外労働
  ③法定外　残業時間　実働の1日の法定労働時間時間を超える労働時間 (昼休憩･深夜時間内作業 含)
  ④深　夜　残業時間　22:00~29:00(翌5:00)の間の労働時間
  ⑤法定休日労働時間　日曜日の労働時間(振替があるので日曜日とは限らない）
  ⑥不　就　労　時間　所定労働時間内の不就労（有給休暇は含まない）
  ⑦有給休暇取得日数　有給休暇 1日=1 半日=0.5
  ※ 所定労働時間(8:00~10:00 10:10~12:00 13:00~15:00 15:10~17:00 の間の労働)
  ※ 中間外出あり
  ※ ①②は実働なので半日有給･不就労は含まない｡平日残業発生時は常に20分

    補足日時：2017/02/24 16:52

No.4 回答者： yokomaya 回答日時： 2017/02/23 20:53

では引き続き続ける前に

実際にはタイムカードの入力作業というのは
締め日あるいは、前日位に行うのではないですか？

この時にタイムカードの形のままに入力するのが
視認性、入力のしやすさ等から必須条件と思われます。

となると、今までa1からで説明してましたが
実際にはa5位から始めるのが妥当でd2やd3など
私が勝手に使いましたが、１日分の処理はz以降まで使う可能性が
あっても１行で処理しなければなりません。
それによって行コピーで最初から最後まで同じ数式で
成立することになります。

あえて５行目としたのは名前であるとか、あるいは職位、職種に
よる差異とか考慮すべきことが、将来あるかも知れないので
多少の余白を上に設けるのが見やすいかと思って。

本来の数式の話からそれてすみません。
ただ設計の肝心な部分になるので先に固めておいた
方が後からの変更より楽なんです。

でa列　日付、Ｂ列　曜日はそのままですが
c列以降は実物のタイムカードの列をそのまま並べます。
4列が多い気がしますが、それ以上かも知れません。

ここでA6には＝A5+1を入力して
そのまま下方向に３０個オートフィルします。
Ｂ５は＝A5として表示形式をaaa
下方向に31個オートフィルです。

小の月は条件書式で一番下のほうを隠すことが出来ますが
それは締め日を聞いてからにします。

で、仮に４列で説明しますとcからF列が入力列
g列以降に計算式となるわけです。

A5に月の最初の日付を入力すると後ろが全部変わって壮観ですよ。

そこで、実際に試していただきたいのはＣ５：Ｆ３５を選択して
セルの書式設定の保護でロックを外します。
次にファイル→オプション→詳細設定で
Enterキーを押した後移動する方向を右にします。
そして、校閲→シートの保護で
ロックされていないセル範囲の選択だけをチェックして
保護します。
実際にダミーデータを入れてみてください。

そうそう
http://www.fujicom.co.jp/salon/it_info/51/51.html
これも時刻入力のツボですので参照してくださいね。

これでタイムカードの入力の、作業時にセル移動に
マウスを使う必要が殆どなくなります。

開発中は当然保護を解除して、完成したら保護して
使うことで誤って数式を消してしまう恐れがなくなる
効用もあります。

ここまでで不明な部分ないでしょうか？

この回答へのお礼

yokomaya様　ご教授有難うございます｡
シートの方は修正しました。動作確認済みです｡
今後の流れまでご配慮頂き､有難うございます｡
ここで気分を害されては悲しいのですが､丸め条件に漏れが有りました｡
さらに条件が増えたので計算式が長くなり試行錯誤のうえ､教えて頂いた計算式から
変更させて頂いています｡漏れは5:00~7:30は0:15単位です｡変更は『VLOOKUP』を
使用してみました｡ 身勝手な変更ですが､今後の作業には影響が無いでしょうか? すみません｡

現在のシート状況
A4＝日付　　B4=曜日　　C4=有給　　D4=出勤　　E4=外出　　F4=再入　　G4=退勤
H4=始業　　I4=途出　　 J4=途入　　 K4=終業 L4=法内　　M4=法外　 N4=深夜　　O=不就
A5=12/6（A6=A5+1) B5=A5（条件 aaa） C5=空白　　D5～G5=ﾀｲﾑｶｰﾄﾞ時刻打込
H5=IFERROR(CEILING(D5,VLOOKUP(D5,AA5:AB15,2))," ")
I5=IFERROR(FLOOR(E5,VLOOKUP(E5,AA5:AB15,2))," ")
J5=IFERROR(CEILING(F5,VLOOKUP(F5,AA5:AB15,2))," ")
K5=IFERROR(FLOOR(G5,VLOOKUP(G5,AA5:AB15,2))," ")
L5~O5=空白
AA5~AA15 順番に 5:00　7:30　8:00　10:00　10:10　12:00　13:00　15:00　15:10　17:00　17:30
AB5~AB15 順番に 0:15　0:30　0:05　0:10　0:05　0:30　0:05　0:10　0:05　0:30　0:05

どうでしょうか?
VLOOKUP(D5,AA5:AB15,2) の AA5:AB15 は､まだ位置が確定していないため絶対値にはしていません。
C列ですが 有給 半日有給 所定休日 法定休日 を番号で区別しようとしてますが､必要有りますか?
今は L4=法内(法定内残業) を思案中です｡
わがままを言って申し訳ありませんが､お付合い下さい｡よろしくお願いします｡

お礼日時：2017/02/24 13:24

No.3 回答者： yokomaya 回答日時： 2017/02/22 17:33

物事には順がありますからひとつづつ。

D1 始業時刻（入力）とありますが
タイムカードをそのまま打ったものが変換されないと
検証のしようもないですから入力されたものと
計算に用いる始業時間はセルを別にしなければ不自由です。
で仮にＤ3をその変換したものとして
D2には
=IF(OR(D1<TIMEVALUE("8:00"),AND(TIMEVALUE("12:00")<D1,D1<TIMEVALUE("13:00"))),TIMEVALUE("0:30"),TIMEVALUE("0:05"))
とします。
これは丸めの単位が30分なのか5分なのかをD1の値で決めています。
Ｄ3には
=INT(D1/D2)*D2+D2
あと表示形式はすべて h:mmでそろえてください。
とすれば10分休憩を除けば解が出るのでは？
10分休憩も同じやり方でできるのですが、ちょっと長すぎて読めなくなりそうですね。

この回答へのお礼

yokomaya様　ご回答有難うございます｡
やはりそうでしたか。　従業員がそこそこ多いためなるべく項目を減らそうとしたのですが、実際やってみて作業は捗りました。とりあえず丸め表示まではクリアです｡有難うございました。次は法定内労働時間に挑戦してみます｡何かアドバイスがあればよろしくお願いします。

お礼日時：2017/02/23 14:16

No.2 回答者： yokomaya 回答日時： 2017/02/22 16:47

ちょっと回答の前によく理解できないので

平日： 8:00~19:00勤務は 法定内 0:20・法定外 1:40
法定内というのが8時間を超えるかどうかという意味ですよね、多分。

半給：13:00~21:00勤務は 法定内 3:50・法定外 0:10
半給が知らない制度で判らないんです。
記述からすると半日有給でもないのかな？
計算理由が見えてこないです。
そもそも8:00~19:00と13:00~21:00の勤務時間が違う事を指すのでしょうか？
もしかして午前を半日有給で4時間残業？？？まさかね？

遅刻：13:00~21:00勤務は 法定内 3:50・法定外 0:10・不就労 3:50）早退･外出も同じ考えです｡
ここも判らないです。同様に。

あと日曜が法定休日だそうですが、祝日はどうでしょう？

打刻時間が5分単位とか、タイムカードマシンの仕様じゃないですよね？
計算をそう丸めるという意味かと。
残念ながら、そういった会社がまだ多いのは実情でしょうが
法的には、毎日丸めることは許されない様です。
一ヶ月集計の時に30分単位でまるめはアリみたいですが。
（参）
http://www.hori-law.jp/category/1429069.html

ひとつだけ簡単な事を答えておきます。
B1に曜日とありますが＝a1として、セルの書式設定の表示形式を
ユーザー定義でaaaに変更すれば入力不要です。
日曜の労働時間についてもWEEKDAY(a1)が1なら日曜なのでifでそれを計算すればよいかと。
A1には月日を例えば 2/22のような形式で入力します。
年も自動的に補完されるので1月に前年分を処理するときだけ注意が必要ですが。

この回答へのお礼

yokomaya様　すみません。お礼の順番が逆になりました｡
おそらく労基法に沿った計算や処理では無いかもしれません。給与計算に限らず労働条件も違反は多々見受けられます。しかし社長からもその旨の説明はあり､徐々に改定しています｡私も今回の作業で現在の集計方法を理解し､まずは集計表を作ってから労基法等も踏まえて提案しようと考えています｡ちなみに､社長をかばう訳ではありませんが俗に言うワンマン社長ではありません｡従業員の事は大切に考えてくれていると思います｡ただ､昔気質と法に弱い｡　あと､この丸めは従業員からの提案らしいです｡
法定内労働時間の考え
所定労働時間(8:00~17:00の間の7時間40分)の実働時間が8時間に満たない時間（残業発生時）
半給 : 有給休暇の半日分（3:50）　上記の実働時間には含まれないが､不就労にはならない。
＜もしかして午前を半日有給で4時間残業？？？まさかね？
勤務状態としてはその通りです。
祝日は出勤です。
私も理解するのに時間がかかったのですが､こんな感じです。

お礼日時：2017/02/23 15:20

No.1 回答者： 銀鱗 回答日時： 2017/02/22 05:10

5分刻みの表を作って、

出勤時間と退社時間を入力したら、
その間のセルに色が付くようにしてはどうか。

で、あとはそこから自力で読み取る。
（これ、ヒントになってるんだけどね）

・・・本題・・・

出勤の打刻時間が
8:00よりも前なら30分単位で切り上げ（？）
8:00から12:00までの間なら5分単位で切り上げ（？）
12:00から13:00までの間なら30分単位で切り上げ（？）
た時間を「X1セル」に計算に使う出勤時間として設定。
…17:00以降はどうなるんだろう(´・ω・`)
退勤の打刻時間も同様。「Y1」セルに設定。

出勤時間から退勤時間までの間が、
10:00までの黄色い色の間に何時間何分あるかを計算。
　=MIN("10:00",Y1)-MAX("8:00",X1)

これと同じことを他の色の時間帯でも行い、最終的に合計すればいい。
そうすれば、
　定時：〇時間〇分
　残業：〇時間〇分
　深夜業：〇時間〇分
のように求められる。
遅刻や早退、半休や休日出勤にもそのまま応用できる。

あとは合計された時間を判定するだけだ。

・・・余談・・・

時間は日付を含んだ形にすることで日にちを跨いでもそのまま時間計算ができる。
　＝"2017/2/23 3:15"-"2017/2/22 8:00"
とすることでエラー無く計算ができます。
午前3時15分を、27:15と入力しないときに使える方法です。


X1セルとY1セルにも日付を加え、
AA1セルに日付と"8:00"の時間を組み合わせ、
AB1セルに日付と"10:00"の時間を組み合わせると、
上で示した計算式は、
　＝MIN(AB1,Y1)-MAX(AA1,X1)
と簡単になります。
　※時間は数値になっている必要があります。文字列ではエラーになります。

この回答へのお礼

銀鱗さん 早速のご教授ありがとうございます｡
文面だけでは理解できる知識を持合わせていませんので､実際に1つ1つ確認したいと思います｡
問題･疑問･要望 等が見つかった場合には､都度質問させて頂いてもよろしいでしょうか?
ｴｸｾﾙは仕事で必要に応じて覚えた程度なので､関数等はほぼ無知です。
よろしくお願いします｡
補足
＞出勤の打刻時間が…（？）
　 7:29→7:30　7:31→8:00　8:01→8:05　11:59→12:00　12:29→12:30　12:31→13:00
　 10:00~10:10→10:10　15:00~15:10→15:10　17:00以降の出社→16:00以降の出社は禁止です｡
　退勤
　 9:04→9:00　10:00~10:10→10:00　15:00~15:10→15:00　17:00~17:29→17:00
　 17:30以降→5分単位切上(19:04→19:00)
10分休憩の時間帯の条件が記入されていませんでした｡

お礼日時：2017/02/22 11:33