|
|
|
下記の式で求まります。
=TEXT(DATE(YEAR(A1), MONTH(A1) - 3, 1), "e年度")
[平成]が必要なら
=TEXT(DATE(YEAR(A1), MONTH(A1) - 3, 1), "ggge年度")
書式をユーザー設定で『 [h]:mm 』という風に、ｈ(時)を角括弧で囲みます。
[7:30]→[7.5] では､時刻に２４を掛けて( =A1*24)､書式を標準にします。
[7.5] →[7:30] では､数値を２４で割って( =A1/24)､書式を時刻にします。
時刻は､シリアル値の『小数部』に格納され､
１時間＝１/２４
という分数で表される値を単位としてカウントされます。
なお、１分＝１／(２４×６０) １秒＝１／(２４×６０×６０) です。
時刻⇒数値変換というのは､[7:30]というシリアル値が「１時間」というシリアル値の『何倍か？』
というのを求めるという風に解釈できます。つまり､
[7:30]÷１時間 ⇒ [7:30]÷(1/24) ⇒ [7:30]×24
ということです。
逆に、数値⇒時刻変換は､『１時間』というシリアル値の『？倍』を求めるということです。つまり、
１時間×[7.5] ⇒ (1/24)×[7.5] ⇒ 7.5÷24
ということです。
２４時間のシリアル値は､『24×(1/24) ⇒ 1.0 』ですから､[1:00]に数値の[1.0]を足せば[25:00]に
なります(書式は"[h]:mm")。
[只の1:00]と上のような[翌日の1:00]を区別するには、開始時刻と比較して『開始時刻≧終了時刻』
なら、その終了時刻は【翌日】を意味すると判断します。
補正終了時刻 ⇒ =IF(開始時刻 >= 終了時刻, 終了時刻+1, 終了時刻)
丸め処理には[CEILING(切り上げ)] [FLOOR(切り捨て)]関数を使います。
出勤時刻なら『切り上げ』､退社時刻なら『切り捨て』になりますから
出勤時刻 ⇒ =CEILING(出勤時刻, TIMEVALUE("0:15"))
退社時刻 ⇒ =FLOOR(退社時刻, TIMEVALUE("0:15"))
という風にします。
ただ、ここで注意事項があります。
手入力された出退勤時刻ならば、何の問題もないのですが、計算結果として得られた勤務時間など
の場合には、そのシリアル値に小数誤差が含まれますので、上記の式が期待通りの結果を返して
くれない場合がありえます。
どういう事かというと、
[1:30]と表示されているデータにCEILING関数で１５分丸めを施す場合、そのままの[1:30]という結果を
期待しますが、プラスの小数誤差が加わっていると『１５分の倍数である[1:30]を僅かに越えている』と
判断されて､ひとつ上の[1:45]が求まってしまうという事です。
FLOOR関数の場合ならばマイナスの小数誤差が加わっていると『１５分の倍数である[1:30]に僅かに
届かない』と判断されて､ひとつ下の[1:15]が求まってしまうという事です。
（注）[1:30]という時刻シリアル値だと必ずこういう結果になってしまうという意味では
ないですから勘違いしないでください。
こういう結果は「丸め」単位の倍数となる時刻で出てきます。
このような誤差を取り除くには､一旦「時刻文字列」に変換して再びシリアル値に戻す事で可能です。
=VALUE(TEXT(時間, "[h]:mm"))
TIMEVALUEでは[0:0:0〜23:59:59]しか変換出来ないので､VALUE関数を使用します。
詳細は『時刻シリアル値と小数誤差』をご覧下さい。
先ずは『カレンダーの作り方』の縦型カレンダーを参考にして【休日フラグ】を設定して下さい。
次に、「休日残業」の時間帯は、平日の「通常勤務」「普通残業」を合わせたものと考えて良いで
しょうか？それならば、下記のように求められます（午前,午後,残業,深夜など時間帯別に勤務時間
が求まった後になります）。
[通常勤務]時間 =IF(休日フラグ="休", 0, SUM(通常午前, 通常午後))
[普通残業]時間 =IF(休日フラグ="休", 0, SUM(早出残業, 夜間残業))
[休日残業]時間 =IF(休日フラグ="休", SUM(通常午前, 通常午後, 早出残業, 夜間残業), 0)
ただし、ここで問題になるのが『日付跨り勤務』です。
平日⇒休日 または 休日⇒平日 と日付を跨って連続勤務した場合、翌日部分をどう扱うかです。
早出や通常勤務帯にまで連続勤務した場合に、その部分の【時給】をどう扱うのかという事です。
出社日の【時給】とは違ってくる訳ですから、単純に全て足し込んでも良いとは言えません。
この辺りは「就業規則」に定まられた条項に則って処理する必要があります。例えば
・０時を過ぎた勤務分は、その「翌日」の時給で計算
・あくまで退社するまでは「出社日」の時給で計算
・通常勤務時刻を過ぎてまで連続勤務する場合は、通常勤務時刻で一旦[退社起票]し、
通常勤務時刻以後は「その翌日」の勤務として改めて[出社起票]する
といった方式などが考えられますが、実際に就業規則を調べて、それに従ってください。
３番目の方式で翌日への分離起票を人手で行ない､翌日跨り分は「前日」扱いとして計算する規則なら､
式は凄く単純になりますけどね‥‥‥
「翌日に跨った分は翌日に集計」とか「時給は､その日の時給で」という条件だと､月初/月末/年初/年末
などで､別シート/別ブックへの参照を必要とするかもしれません（おそらく普通は１月１シート､１年１ブック
といった形態でしょうから）。
個々の勤務帯における勤務時間の計算は『始業〜終業が、その勤務帯に掛かっているかどうか』
によって「ゼロになるか、時間が出て来るか」に分かれます。その条件の見方は
勤務帯の終わりより前から仕事している 且つ 勤務帯の始めより後まで仕事している
場合は【その勤務帯で仕事をしている】。
IF(AND(始業時刻 < 勤務帯_終, 終業時刻 >= 勤務帯_始)
, MIN(終業時刻, 勤務帯_終) - MAX(始業時刻, 勤務帯_始)
, 0 )
もしくは、条件を逆にして
勤務帯の終わりより後から仕事を始めた 又は 勤務帯の始めより前に仕事を終えた
場合は【その勤務帯で仕事はしていない→０】
IF(OR(始業時刻 >= 勤務帯_終, 終業時刻 < 勤務帯_始)
, 0
, MIN(終業時刻, 勤務帯_終) - MAX(始業時刻, 勤務帯_始) )
尚、この判定は『始業時刻＜終業時刻』という前提（入力値チェック済、および翌1:00→25:00
といった２４時補正済）の下に使えるものです。
時間計算部分の【 MIN(終業時刻, 勤務帯_終) - MAX(始業時刻, 勤務帯_始) 】の意味は
下図の各就業パターンにおいて、その勤務帯に掛かっている（灰色と赤線の重なっている）
部分の勤務時間は
「どの時刻と、どの時刻」を使って引き算するべきか
という事を考ええれば自ずと理解できるかと思います。
【前提条件】
・日付跨り勤務は通常勤務開始時刻である翌9:00で締め､9:00以降は翌日分として
別に起票する（分離起票そのものは人が行なう）。
・日付跨り勤務で翌日部分の時給単価は始業側の日付の単価を用いる。
・[5:00〜翌9:00]などの場合は[5:00〜33:00]という風に入力する。それ以外で[9:00〜5:00]と
いう風に入力した場合には､自動的に[9:00〜29:00]と判断する。
・時間割は下記の通りとし､休日では「午前,午後,夜間,早出」が「休日残業」となる。
9:00-12:00 午前 12:00-13:00 (昼休み) 13:00-17:30 午後 17:30-18:00 (休憩) 18:00-22:00 夜間残業 22:00- 5:00 深夜 5:00- 8:45 早出残業 8:45- 9:00 (休憩)
・ここでは、「日勤（昼間から従事して、夜間/深夜まで働く）」の人を対象にします。
夜勤（夜間/深夜から従事して翌朝まで働く等）の人の場合には、上表で「残業・深夜」として
いる部分が「残業となるのか、通常勤務となるのか」といった扱いで就業規則などを確認する
必要があると思いますので、それに合わせた修正が必要になるでしょう。
-- 例 --
・所定労働時間に満たない部分は残業割増なし
・所定労働時間を超えた部分について残業割増 25%
・深夜勤務帯は 深夜割増 25% （所定労働時間に満たない部分では 深夜割増のみ 25% 、
所定労働時間を超えている部分では 残業割増 25% + 深夜割増 25% = 50% ）。
-------------------------------------------------------
上記の時間割は､下記のように見方を変えます。
【以降は翌日の勤務として起票を改める←手作業で】
平 日 休 日 0:00- 5:00 深夜１ 0:00- 5:00 深夜１ 5:00- 8:45 残業１ 5:00- 8:45 休日１ 8:45- 9:00 (休憩) 8:45- 9:00 (休憩) 9:00-12:00 午前 9:00-12:00 休日２ 12:00-13:00 (昼休み) 12:00-13:00 (昼休み) 13:00-17:30 午後 13:00-17:30 休日３ 17:30-18:00 (休憩) 17:30-18:00 (休憩) 18:00-22:00 残業２ 18:00-22:00 休日４ 22:00-24:00 深夜２ 22:00-24:00 深夜２ ----------------------------------------------- 24:00-29:00 深夜1(2) 24:00-29:00 深夜1(2) 29:00-32:45 残業1(2) 29:00-32:45 休日1(2) 32:45-33:00 (休憩) 32:45-33:00 (休憩)
-------------------------------------------
Ａ列：始業時刻 Ｂ列：終業時刻 ←【手入力】
Ｃ列：終業時刻の２４時補正(始業≧終業の時に[終業+24時]）
=IF(OR(A2="",B2=""),"",IF(A2<B2,B2,B2+1))
Ｄ列（深夜１）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<TIMEVALUE("5:00"),C2>=TIMEVALUE("0:00")),
MIN(C2,TIMEVALUE("5:00"))-MAX(A2,TIMEVALUE("0:00")),0))
Ｅ列（残業１）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<TIMEVALUE("8:45"),C2>=TIMEVALUE("5:00")),
MIN(C2,TIMEVALUE("8:45"))-MAX(A2,TIMEVALUE("5:00")),0))
Ｆ列（午前）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<TIMEVALUE("12:00"),C2>=TIMEVALUE("9:00")),
MIN(C2,TIMEVALUE("12:00"))-MAX(A2,TIMEVALUE("9:00")),0))
Ｇ列（午後）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<TIMEVALUE("17:30"),C2>=TIMEVALUE("13:00")),
MIN(C2,TIMEVALUE("17:30"))-MAX(A2,TIMEVALUE("13:00")),0))
Ｈ列（残業２）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<TIMEVALUE("22:00"),C2>=TIMEVALUE("18:00")),
MIN(C2,TIMEVALUE("22:00"))-MAX(A2,TIMEVALUE("18:00")),0))
Ｉ列（深夜２）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<(1+TIMEVALUE("0:00")),C2>=TIMEVALUE("22:00")),
MIN(C2,(1+TIMEVALUE("0:00")))-MAX(A2,TIMEVALUE("22:00")),0))
Ｊ列（深夜１(2) ）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<(1+TIMEVALUE("5:00")),C2>=1+TIMEVALUE("0:00")),
MIN(C2,1+TIMEVALUE("5:00"))-MAX(A2,1+TIMEVALUE("0:00")),0))
Ｋ列（残業１(2) ）
=IF(OR(A2="",C2=""),"",
IF(AND(A2<(1+TIMEVALUE("8:45")),C2>=1+TIMEVALUE("5:00")),
MIN(C2,1+TIMEVALUE("8:45"))-MAX(A2,1+TIMEVALUE("5:00")),0))
↑のＤ〜Ｋ列は作業セルとして非表示にし､↓のＬ〜Ｐ列だけ表に出します。
（下記式での[休日条件]について）
Ｌ列 勤務時間 =IF(OR(A2="",C2=""),"", SUM(D2:K2)) Ｍ列 通常勤務 =IF(OR(A2="",C2=""),"", IF(休日条件,0,SUM(F2,G2))) Ｎ列 普通残業 =IF(OR(A2="",C2=""),"", IF(休日条件,0,SUM(E2,H2,K2))) Ｏ列 休日残業 =IF(OR(A2="",C2=""),"", IF(休日条件,SUM(E2:H2,K2),0)) Ｐ列 深夜残業 =IF(OR(A2="",C2=""),"", SUM(D2,I2,J2))
小数誤差を除いておく事を考慮するなら
Ｌ列 勤務時間 =IF(OR(A2="",C2=""),"",
VALUE(TEXT(SUM(D2:K2),"[h]:mm")))Ｍ列 通常勤務 =IF(OR(A2="",C2=""),"",
IF(休日条件,0,VALUE(TEXT(SUM(F2,G2),"[h]:mm"))))Ｎ列 普通残業 =IF(OR(A2="",C2=""),"",
IF(休日条件,0,VALUE(TEXT(SUM(E2,H2,K2),"[h]:mm"))))Ｏ列 休日残業 =IF(OR(A2="",C2=""),"",
IF(休日条件,VALUE(TEXT(SUM(E2:H2,K2),"[h]:mm")),0))Ｐ列 深夜残業 =IF(OR(A2="",C2=""),"",
VALUE(TEXT(SUM(D2,I2,J2),"[h]:mm")))
あとは、オートフィルしてＣ〜Ｋ列を非表示などにすれば完成です。
ただし「これを１ヶ月分合計する」といった計算をすれば､そこには再び小数誤差が入ってきます。
勤務時間計算の基本はここまでです。ここから下は、σ(^_^)の「kt関数アドイン」が使える場合の解説になります。エクセルの一般関数のみで処理する場合は、ここから下の解説を読む必要はありません。
『ktWorkingTime 』のヘルプ参照のこと。
Sheet2に下記のレイアウトで時間割を定義します（実際に利用するのは終了時間のＣ列です）。
-------------------------------------------------
(A) (B) (C) (1) 深夜１ 0:00 5:00 (2) 残業１ 5:00 8:45 (3) 休 憩 8:45 9:00 (4) 午 前 9:00 12:00 (5) 昼休み 12:00 13:00 (6) 午 後 13:00 17:30 (7) 休 憩 17:30 18:00 (8) 残業２ 18:00 22:00 (9) 深夜２ 22:00 24:00
Ａ列：始業時刻 Ｂ列：終業時刻 ←【手入力】
Ｃ列：終業時刻の２４時補正(始業≧終業の時に[終業+24時]）
=IF(OR(A2="",B2=""),"",IF(A2<B2,B2,B2+1))
Ｄ列（深夜１）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,0,0))
Ｅ列（残業１）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,0,1))
Ｆ列（午前）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,0,3))
Ｇ列（午後）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,0,5))
Ｈ列（残業２）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,0,7))
Ｉ列（深夜２）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,0,8))
Ｊ列（深夜１(2) ）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,1,0))
Ｋ列（残業１(2) ）
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,1,1))
↑のＤ〜Ｋ列は作業セルとして非表示にし､↓のＬ〜Ｐ列だけ表に出します。
（下記式での[休日条件]について）
Ｌ列 勤務時間 =IF(OR(A2="",C2=""),"", SUM(D2:K2)) Ｍ列 通常勤務 =IF(OR(A2="",C2=""),"", IF(休日条件,0,SUM(F2,G2))) Ｎ列 普通残業 =IF(OR(A2="",C2=""),"", IF(休日条件,0,SUM(E2,H2,K2))) Ｏ列 休日残業 =IF(OR(A2="",C2=""),"", IF(休日条件,SUM(E2:H2,K2),0)) Ｐ列 深夜残業 =IF(OR(A2="",C2=""),"", SUM(D2,I2,J2))
小数誤差を除いておく事を考慮するなら
Ｌ列 勤務時間 =IF(OR(A2="",C2=""),"",
VALUE(TEXT(SUM(D2:K2),"[h]:mm")))Ｍ列 通常勤務 =IF(OR(A2="",C2=""),"",
IF(休日条件,0,VALUE(TEXT(SUM(F2,G2),"[h]:mm"))))Ｎ列 普通残業 =IF(OR(A2="",C2=""),"",
IF(休日条件,0,VALUE(TEXT(SUM(E2,H2,K2),"[h]:mm"))))Ｏ列 休日残業 =IF(OR(A2="",C2=""),"",
IF(休日条件,VALUE(TEXT(SUM(E2:H2,K2),"[h]:mm")),0))Ｐ列 深夜残業 =IF(OR(A2="",C2=""),"",
VALUE(TEXT(SUM(D2,I2,J2),"[h]:mm")))
という風にします。ただし「これを１ヶ月分合計する」といった計算をすれば､そこには再び
小数誤差が入ってきます。
Sheet2に下記のレイアウトで時間割を定義します（実際に利用するのは終了時刻のＣ列です）。
(A) (B) (C) (1) 深夜１ 0:00 5:00 (2) 残業１ 5:00 8:45 (3) 休 憩 8:45 9:00 (4) 午 前 9:00 12:00 (5) 昼休み 12:00 13:00 (6) 午 後 13:00 17:30 (7) 休 憩 17:30 18:00 (8) 残業２ 18:00 22:00 (9) 深夜２ 22:00 24:00
同じくSheet2に､集計対象とする時間帯を指定する文字列を定義します。
(A) (B) (11) 総勤務時間 110101011#110000000 (12) 通常勤務 000101000#000000000 (13) 普通残業 010000010#010000000 (14) 休日残業 010101010#010000000 (15) 深夜残業 100000001#100000000
日付跨りで翌日の9:00以降は手作業で翌日分として別起票する前提なので､＃の右側の４文字目
以降は全て"0"(集計しない)にしてます（下記式での[休日条件]について）。
-------------------------------------------------
Ａ列：始業時刻 Ｂ列：終業時刻 ←【手入力】
Ｃ列：終業時刻の２４時補正(始業≧終業の時に[終業+24時]）
=IF(OR(A2="",B2=""),"", IF(A2<B2,B2,B2+1))
Ｄ列 勤務時間
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$11))
Ｅ列 通常勤務
=IF(OR(A2="",C2=""),"",
IF(休日条件,0,ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$12)))
Ｆ列 普通残業
=IF(OR(A2="",C2=""),"",
IF(休日条件,0,ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$13)))
Ｇ列 休日残業
=IF(OR(A2="",C2=""),"",
IF(休日条件,ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$14),0))
Ｈ列 深夜残業
=IF(OR(A2="",C2=""),"", ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$15))
集計オプションを使えば作業セルは必要なくなります。
また､ktWorkingTimeの中では『分』ベースでの整数演算で処理してますので､求まる集計結果には
小数誤差は含まれて来ませんから､そのままで大丈夫です。
ただし「これを１ヶ月分合計する」といった計算をすれば､そこには小数誤差が入ってきます。
(注) Ver2.60からの新機能になります。
Ver2.60 からのktWorkingTime には､引数[Serial]が追加されます。
[True または 省略]の場合は従来通りにシリアル値で結果を返します。
[False]を指定すると『分』に換算した整数値で返します。
整数ですから､いくら集計しようが､そこに小数誤差など存在しません。
ktWorkingTime は作業セルに記述し､表に出す時間セルの所で､『分』から変換します。
７：４５のように時間表示にしたいなら「1440で割って､表示形式を時刻」にします。
７．７５のように数値表示にしたいなら「６０で割って､表示形式は標準/数値」にします。
（下記式での[休日条件]について）
------------------------------------------------------
Ａ列：始業時刻 Ｂ列：終業時刻 ←【手入力】
Ｃ列：終業時刻の２４時補正(始業≧終業の時に[終業+24時]）
=IF(OR(A2="",B2=""),"",IF(A2<B2,B2,B2+1))
Ｄ列 勤務時間
=IF(OR(A2="",C2=""),"",
ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$11,FALSE))
Ｅ列 通常勤務
=IF(OR(A2="",C2=""),"",
IF(休日条件,0,ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$12,FALSE)))
Ｆ列 普通残業
=IF(OR(A2="",C2=""),"",
IF(休日条件,0,ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$13,FALSE)))
Ｇ列 休日残業
=IF(OR(A2="",C2=""),"",
IF(休日条件,ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$14,FALSE),0))
Ｈ列 深夜残業
=IF(OR(A2="",C2=""),"",
ktWorkingTime(A1,C1,Sheet2!$C$1:$C$9,3,Sheet2!$B$15,FALSE))
↑のＤ〜Ｈ列は作業セルとして非表示にし､↓のＩ〜Ｍ列だけ表に出します。
Ｉ列 勤務時間 =IF(OR(A2="",C2=""),"",D1/1440) 数値の場合は D1/60 Ｊ列 通常勤務 =IF(OR(A2="",C2=""),"",E1/1440) 〃 E1/60 Ｋ列 普通残業 =IF(OR(A2="",C2=""),"",F1/1440) 〃 F1/60 Ｌ列 休日残業 =IF(OR(A2="",C2=""),"",G1/1440) 〃 G1/60 Ｍ列 深夜残業 =IF(OR(A2="",C2=""),"",H1/1440) 〃 H1/60
１ヶ月分を集計する時は､
Ｉ〜Ｍ列を集計するのではなく､
Ｄ〜Ｈ列の合計に対して､同じ様に１４４０や６０で割ります。
（注） 紹介している「式」内で、【休日条件】と記している部分について
上記「式」において、【休日条件】と記している部分は、実際には
その部分に、日付に対して平日/休日を判定する『条件式』を記述して下さい
という意味です。
尚、上記「式」では【休日条件】と表現しているように『TRUE:休日, FALSE:平日』という
判定を前提に式を組んであります。したがって、TRUE/FALSEの意味が逆になる条件式
を使う場合は、その後ろの２項（０ と SUM関数/ktWorkingTime関数）の並びも逆にする
必要があります。
(1) 祝日一覧をMATCH関数で検索して、土日祝休みを判定する例
【休日条件】の部分→ IF(WEEKDAY(日付, 2)>5, FALSE, ISERROR(MATCH(日付, 祝日一覧, 0)))
「TRUE:平日, FALSE:休日」という判定になります
(2) [kt営業日判定]関数を使って、土日祝休みを判定する例
【休日条件】の部分→ kt営業日判定("10000011000@", 日付)
「TRUE:平日, FALSE:休日」という判定になります
カレンダーの作り方/縦型カレンダー での休日判定の解説なども合わせて参考にしてください。
[ Home へ戻る ]
 |
 |
|
|
|
||
| 角田 桂一 Mail:addinbox@h4.dion.ne.jp CopyRight(C) 2001 Allrights Reserved. | ||
