[[20060124145553]]　『出勤簿の作成』（ぼう）　ページの最後に飛ぶ

『出勤簿の作成』（ぼう）

Excel2000、Windows2000を使っています

今度、新規で出勤簿を作成することになりました

日付　　出勤　　　退勤　　　残業　　　深夜　　　休日　　　備考
　

1/27　　9：0　　19：30　　　1.25　　　0　　　　0　　　　

1/28

1/29　

・

と、いうような表で作ろうとしています

１、休憩時間は\x{fffd}@12：00～13：00と18：00～18：15の二つ

２、休日出勤は残業扱い（深夜も同じ）
　
３、24：00を超える時間帯の勤務もあり

４、残業時間は１５分単位のカウント

５、個人ごとの出勤簿管理

上記の条件で作ることになるんですが、

\x{fffd}@残業時間を小数で算出する方法がわからない

\x{fffd}A24：00以降の時間の表示方法がわからない

\x{fffd}B拘束時間から、休憩時間の引き方がわからない

結局、勤務表というよりは「残業時間表」なんですが、
わからないづくしで苦労しています

何卒、ご教授をお願い致します　　

 \x{fffd}@\x{fffd}Aのご参考まで（あきお）
 エクセルの学校：時間の計算(e2h) 
https://www.excel.studio-kazu.jp/lib/e2h/e2h.html

ありがとうございます
早速、読んでみます（ぼう）

読んでみました
時間の表記はなんとなくわかった気になりました

私の作りたい表は、通常勤務時間の欄の無い物だったのですが、
この欄を作らないでも、残業時間は表示できますでしょうか？

条件の補足としまして、

\x{fffd}@拘束時間　9：00～18：00

\x{fffd}A深夜時間　22：00～05：00

\x{fffd}B残業の際は18：00～18：15を休憩として差し引く
　

お忙しいところ本当にすみませんが、お願い致します

↑（ぼう）でした

 とっかかりだけでスミマセン（あきお）
 未入力とか休憩に掛かってるとか、まだまだ色々配慮が必要です。

        B                 C                 D
  1  出勤              退勤              残業
  2  2006/1/26 9:00    2006/1/27 2:30    =(C2-B2-TIME(8,0,0))*24
     ↓書式を変えて
  2  9:00              26:30  訂正       9.5

 私、なんか間違ってますね（あきお）
        B                 C                 D
  2  9:00              26:30             後で、考えます（どなたかに助けてもらうのは、大歓迎）

私の質問の仕方（＝何をどういう風にやりたいのか？）が、
うまく伝えられなくて、かえってご迷惑をおかけしているようです（ぼう）

あきおさんの書いてくださった表にE＝深夜残業・F＝休日出勤・G=備考を
加えたものが、私の作りたい残業時間簿です。

定義は

\x{fffd}@拘束時間9：00～18：00

\x{fffd}A昼休み　一時間を引くので定時の実働8時間

\x{fffd}B残業が発生した場合、18：00～18：15を休憩として差し引く

\x{fffd}C22：00～翌5：00を深夜残業時間に表示する

\x{fffd}D残業・深夜・休日の稼働時間を0.25刻みの小数で表す

\x{fffd}E休日は残業時間扱い

\x{fffd}F夜勤で17：00～翌2：00が定時の者もおり、深夜の扱いは昼勤と同じ

ここで出た残業時間を、同じシートの枠内の空いたスペースに単価を
かけて、月々の残業代を計算する予定です

この他に、私はどういったことをお伝えすれば宜しいのでしょうか？
お手間をおかけして申し訳ありません

[[20050807164943]]

 参考になりませんか？
 全部質問するのではなく、絞って（もっと調べて）から質問しましょう！
 「単語検索」このＨＰの右上で調べてみましょう
 （・・）

はい、できるだけやってみます（ぼう）
今の状態だと、全部の作り方を聞いてるのと同じですものね。
わからないことが絞り込めたら、このスレで質問させてください
ありがとうございました　多謝

 細かい判定は、ユーザー関数を作ってやるのも一考ですね（あきお）

やはり、残業時間を深夜残業を求める関数？が、難しいです。（ぼう）

過去ログやエクセルの本も見ているのですが､深夜時間を超えて働く人間も
いまして、何をどうしたら？になってます。

何を？が、つかめましたらまた、質問させてください。

 ＜参考＞勤務表の作り方
http://www.h3.dion.ne.jp/~sakatsu/WorkingTimeCalc.htm

悪戦苦闘中の（ぼう）です。
以下のような表を作りました。

日付　　出勤　　　退勤　　　昼休　　　休憩　　　実働　　　残業　　深夜　

1/27　　9：00　　19：30　　　1：00　　　0：15　　9.25　　1.25　　　　　　　　　

1/28

1/29　

・
出勤＝C、退勤＝D･･･、残業＝H、深夜＝Iの作業列（？）にあります。

出勤・退勤時間は入力者が15分単位で判断して入力するのでOK
　　
昼休みは、「出社すれば必ず9時間以上いるので、1：00発生する」という、恐ろしい社風の前提条件の上で　　　=IF(D7="","","1:00")という式を入れています。

同様に、休憩は　　=IF((D7*24)-9>0,"0:15","")にしました。

ここで、実働時間を求めるのに　　=IF((D7-C7-E7-F7)*24>0,(D7-C7-E7-F7)*24,"")としましたら、勤務時間の記入の無い日に＃VALUE!が表示されてしまいます。多分この式は、D7～F7間に数値が入らないと、エラーになってしまう式なんだと思いますが、どういったものを付け加え（？）たらいいんでしょうか？

教えていただけますでしょうか？

 count() をみなさん、よく利用していらっしゃいますね。
 入力された項目数が条件を判定して。 =if(count( ・・）>4, ・・・　のように
 まちがっていたら、ごめんです（あきお）

 　 A　　　B　　　C　　　D　　　E
 1 日付　出勤　 退勤　 残業　 深夜　休日　備考
 2 1/27　9:00　22:50　 3.00　 0.75
 3 1/28
 4 1/29

 D2 =IF(C2="","",IF(C2<=--"13:00",MROUND(SUM(C2,---"18:00")/--"1:00",0.25),
      IF(E2="",MROUND(SUM(C2,---"19:00")/--"1:00",0.25),MROUND(SUM(C2,---"19:00")/--"1:00",0.25)-E2)))

 E2 =IF(C2<=--"22:00","",MROUND(SUM(C2,---"22:00")/--"1:00",0.25))

 で一応はご希望通りの形になると思うのですが、休日の扱いの場合はどうしても
 休日の判定基準（土、日、祝、社休など）が必要になります。

 また、22:00～5:00もそれなりの判定（日勤＝１、夜勤＝２など）の判定要素が必要です。

 もうひとつ、遅刻の場合も通常通りのカウントでいいのかがわかりません。（Alica）

あきおさん、以下のようにしましたら、クリアーできました（ぼう）
=IF(COUNT(D7)>0,IF((D7-C7-E7-F7)*24>0,(D7-C7-E7-F7)*24,""),"")

Alicaさん、レスありがとうございました。

自分の表に合わせて、残業　　=IF(D7="","",IF(D7<=--"13:00",MROUND(SUM(D7,---"18:00")/--"1:00",0.25),

      IF(I7="",MROUND(SUM(D7,---"19:00")/--"1:00",0.25),MROUND(SUM(D7,---"19:00")/--"1:00",0.25)-I7)))

深夜　　=IF(D7<=--"22:00","",MROUND(SUM(D7,---"22:00")/--"1:00",0.25))　と、変更してみましたら、共に　＃NAME?　に、なってしまいました。

休日の判定基準なんですが、日曜・祝祭日は基本的に休みでして、土曜日に関しては、月に二回他の社員と調整を取りながら、不定期に休みをとる形です。また、年末年始などの特別休業日に関しては、毎年適当（泣）に本社からカレンダーが届く状況です。尚、全ての休日出社は、通常の時間帯が平日残業、深夜は平日深夜と同じ扱いで、残業時間としてカウントします。

これに関しては、日付・曜日の横に枠を設けて、「１を入れたら休日扱い、何も入れなければ通常扱い」といった枠にしてこれを判定基準にする、といった方法なのかな？と考えています。
また、遅刻・早退は無視（笑）です。

＞22:00～5:00もそれなりの判定（日勤＝１、夜勤＝２など）の判定要素が必要です

会社としては、全て日勤者の扱いなんですが、ここはどうするべきなんでしょうか？

(ぼう）です。　

MROUNDを検索してみたら、”「ツール」→「アドイン」→「分析ツール」を有効にする必要がある”という文章がありましたので実行したんですが、エクセルのCD-Rが無いとインストできないようなんですが（会社のPCなもんで、今手元に無いんです）、この所為でAlicaさんに教えていただいた関数が利用できないんでしょうか？

 MROUNDは分析ツールとは関係なかったと思います。
 残業と深夜のセルの書式が時間表示用の　[h]:mm　になっていませんか？
 残業と深夜のセルを　セルの書式　→　ユーザー定義　→　0.00　と設定し直してみてください。（Alica）

 追記：ごめんなさい！
 今確認してみたら、やっぱ分析ツールが要るようです。
 会社のPC管理者の方に相談してみては？

はい、聞いてみます。Alicaさん、ありがとうございます。（ぼう）

 ＞会社としては、全て日勤者の扱いなんですが、ここはどうするべきなんでしょうか？

 私の提案した式に、22:00からの設定を加えると、果てしなく長い式になる（というか、IF関数の限度を超えるｗ）ので
 もし、分析ツールが使えるようならこうしてみてください。

 　  A　　　B　　　C　　　D　　　E　　　F　　　G　　　H　　　I　　　J
 1 日付　 　　　 勤務　 出勤　 退勤　 出勤2　退勤2　 残業　 深夜　 休日　 備考
 2 1/27　　 　　　1　　 9:00　 22:50　 9:00　22:50　 3.00　 0.75
 3 1/28　　　 　　2　　17:00　 27:30　 9:00　19:50　 0.75
 4 1/29　　休

 Ｂ列：休日判定　休日：休　平日：空白　として

 Ｃ列：勤務シフト　日勤：1　夜勤：2　として

 F2 =IF(C2=1,D2,SUM(D2,---"8:00"))

 G2 =IF(B2="休","",IF(C2=1,E2,SUM(E2,---"8:00")))

 H2 =IF(G2="","",IF(G2<=--"13:00",MROUND(SUM(G2,---"18:00")/--"1:00",0.25),
      IF(I2="",MROUND(SUM(G2,---"19:00")/--"1:00",0.25),MROUND(SUM(G2,---"19:00")/--"1:00",0.25)-I2)))

 I2 =IF(G2="","",IF(G2<--"22:00","",MROUND(SUM(G2,---"22:00")/--"1:00",0.25)))

 J2 =IF(B2="","",MROUND(SUM(E2,-D2,---"1:00")/--"1:00",0.25))

 で、Ｆ列とＧ列を非表示にします。

 これでどうですか？（Alica）

 注意：E列の退勤時間で翌日になる場合は 2:00 ではなく26:00 というふうに入力してください。

Alicaさん　本当にありがとうございます！！（ぼう）

まだ委託業者さんに連絡がつかないんですけど、早く試したいです！
はぅぅ･･･

 ごめんなさい　↑Ｊ列（J2）の式をチョコッと修正ｗ（Alica）

 重ねてごめんなさい！
 上記のH2の式では定時の退勤に対応していません

 H2 =IF(G2="","",IF(G2<=--"13:00",MROUND(SUM(G2,-F2)/--"1:00",0.25)-8,
      IF(I2="",MROUND(SUM(G2,-F2,---"1:00")/--"1:00",0.25)-8,MROUND(SUM(G2,-F2,---"1:00")/--"1:00",0.25)-I2-8)))

 としてください　ｍ（_　_;）ｍ　（Alica）

 後でやるといっておいて、やってないので申し訳ありませんが
 情報だけ（あきお）ヘルプの「勤務」でこんなんありました
 Microsoft Office テンプレート スケジュール
http://office.microsoft.com/ja-jp/templates/CT010753091041.aspx 現在参照不可

Alicaさん、ありがとうございます（ぼう）

あきおさん、情報ありがとうございますm(_ _)m

ロムが届いたと思ったら、プロフェッショナルじゃなくて、プレミアムが届きました　｡ﾟ(ﾟ´Д｀ﾟ)ﾟ。

 アドインのインストールならOfficeのどのグレードでも可能．．．だったと思います　←頼りないｗ
 ってか、プレミアムってグレードがあるんですか？（Alica）

はい、プレミアムというグレードがあるようです（ぼう）

んでもって、インストールできませんって･･･

今日は、残業時間以外の部分のデータを考えます。

明日、土曜休日出勤で頑張ります。
また、教えを請わせてくださいm(_ _;)m（ぼう）

Alicaさんへ

　日付休日判定シフト出勤退勤出勤２退勤２残業深夜休日

	       1	   9:00	30:00 9:00  30:00	   #NUM!	 8	
	休     1	   09:00	25:30	1		      15.5

やっとロムが届きましたので、分析ツールがインストールできました。
お教えいただいた数式を入れてみましたら、上記のようになりました。
折角お教えいただいたのに、結果が出なくて申し訳ありませんm(_ _;)m（ぼう）

 #NUMは計算式に適合しない値を用いたときに出るエラーです。
 私の方では再現できないので、確実ではありませんが、各セルの書式設定がおかしいのかもしれません。
 各列の「セルの書式設定」の「ユーザー定義」を

 日付　　　　　：　m/d
 休日判定　　　：　標準
 シフト　　　　：　標準　または　0
 出勤～出勤２　：　[h]:mm　←重要（ h:mm ではありません）
 残業～休日　　：　0.00　または　#,##0.00　←重要（小数点第2位まで）

 というふうにしてみてください（Alica）

Alicaさんへ

おっしゃる通りの設定を確認しましたら、無事に求められるようになりました！
長いことお時間を割いていただき、根気よくお付き合いいただきまして感謝です。
事務所の人間も大変喜んでおります。
本当にありがとうございました！

そして、レスを下さった皆様、重ねて御礼申し上げます　*･゜ﾟ･*:.｡..｡.:*･゜（ﾟ∀ﾟ）ﾟ･*:.｡. .｡.:*･゜ﾟ･*!!!!!*･゜ﾟ･*:.｡..｡.

 ぼう様
 私、大変な勘違いをしていました。
 0.25刻みって0.25で丸める意味だと思っていたんですが、0.25、0.50、0.75、1.00未満は切捨てってことなんですよね？
 だとしたら、私の式は使えません。
 正しい式を至急考えます。

 ぼうさん、これ見てくれるかな？

Alaca様へ

0.25刻みの部分なんですが、「15分単位での残業発生」という意味です。
ただ、本来の勤務表ですと、正確なタイムカードの時間の記入になるんでしょうが、
入力する時点で、入力担当が15分刻みに「丸めて」（？）入力するので、頂いた数式で検証させて頂いて、希望の答えが返ってきました。

通常残業の部分で、シフト１の控除時間が昼+休憩で1：15になるので、

H2　=IF(G2="","",IF(G2<=--"13:00",MROUND(SUM(G2,-F2)/--"1:00",0.25)-8,IF(I2="",MROUND(SUM(G2,-F2,---"1:00")/--"1:00",0.25)-8,MROUND(SUM(G2,-F2,---"1:15")/--"1:00",0.25)-I2-8)))

最後のMROUND(SUM(G2,-F2,---"1:00")/--"1:00",0.25)-I2-8)))を

MROUND(SUM(G2,-F2,---"1:15")/--"1:00",0.25)-I2-8)))に意味も理解しきらないで変更して使ってますが、これは結果オーライなんでしょうか？

今日は、勉強のつもりでこの掲示板を見に来まして、このスレが予想より高い位置にあったのでつついてみたんですが、Alicaさん、本当にありがとうございます｡｡｡

 あぁ、見ていてくださってよかった！！！

 いやはや、浅慮のまま回答してしまい、滝汗状態ですｗ
 ほんと、ごめんなさい！

 ＞入力担当が15分刻みに「丸めて」（？）入力

 よかった．．．
 でももし、ＴＣ通りに入力するなら、以下のように入れてみてください

  　  A　　　B　　　C　　　D　　　E　　　F　　　G　　　H　　　I　　　J
 1 日付　 　　　 勤務　 出勤　 退勤　 出勤2　退勤2　 残業　 深夜　 休日　 備考
 2 1/27　　 　　　1　　 9:00　 22:50　 9:00　22:50　 3.00　 0.75
 3 1/28　　　 　　2　　17:00　 27:30　 9:00　19:50　 0.75
 4 1/29　　休

 は、以前のままとして

 F2 =IF(D2="","",IF(C2=1,D2,SUM(D2,---"8:00")))
 G2 =IF(E2="","",IF(C2=1,E2,SUM(E2,---"8:00")))
 H2 =IF(B2="",IF(F2="","",IF(G2<=--"12:00",FLOOR((G2-F2)/--"1:00",0.25)-8,IF(G2>=--"22:00",4,FLOOR((G2-F2---"1:15")/--"1:00",0.25)-8))),"")
 I2 =IF(B2="",IF(F2="","",IF(G2<=--"22:00","",FLOOR((G2---"22:00")/--"1:00",0.25))),"")
 J2 =IF(B2="","",IF(G2<--"12:00",FLOOR((G2-F2)/--"1:00",0.25),FLOOR((G2-F2---"1:15")/--"1:00",0.25)))

 セルの書式設定は以前書いた通りで．．．

 これが最も簡素な式じゃないかと思います。（Alica）

 ＞これは結果オーライなんでしょうか？

 オーライですｗ！

 と、言いたかったのですが、シフト１の休憩時間が1:15ということは、
 通常、一日の基準労働時間は7:75ということに？
 で、シフト２の方は、1:00の休憩？
 だとしたら、もう一工夫いるかも？（Alica）

alicaさん　おはようございます
シフト1の基準労働時間なんですが、9:00～18:00で休憩1:00　拘束9:00の休憩1:00です。
しかし、18:00～22:00には0:25（15分）休憩を差し引きされます。そして、「残業有りき」の悲しい会社なもんで、「退勤-出勤-8:00-1:25=残業」　で考えているんですけど、直近に教えて頂いた式で定時であがった場合、基準時間が7:75になりますね（w
あ、それと22:00退勤のとき、残業が4.00で計算されますが、ここが3.75になれば･･･です。

そしてシフト2はどんだけ働いても休憩は1:00なんです。

今、頂いた式を読み取ろうとエクセルの本と↑の検索を行ったり来たりしてるんですが、Alicaさんが式に使っている、「---」「--」（半角のマイナス？）の意味が見つからないんです。
この意味も教えていただけますか？m(_ _;)m

 あ、やっぱりシフト１と２で少し違うんですねｗ
 今、式を考えてます。

 ＞「---」「--」（半角のマイナス？）の意味

 「---」の頭の - は減算の意味のマイナスですが、うしろの -- は違います。
 普通時間は 1:15 などと表記しますが、直接計算式に 1:15 と書き込んでは使えません。
 そこで 1:15 を "1:15" とクォーテーションではさんで一旦文字列にし、その前に -- を付け --"1:15" とすることで
 数値化（シリアル値化）し、計算式に用いることができるようにしています。
 （Alica）

 過去ログから
[[20040530061813]] 『「--」と「！」の意味?』(masabou5)

 シフト１と２で変えるとしたらこうかな？

 H2 =IF(B2="",IF(F2="","",IF(G2<=--"12:00",FLOOR((G2-F2)/--"1:00",0.25)-8,
      IF(C2=1,IF(G2>=--"22:00",3.75,FLOOR((G2-F2---"1:15")/--"1:00",0.25)-8),
       IF(C2=2,IF(G2>=--"22:00",4,FLOOR((G2-F2---"1:00")/--"1:00",0.25)-8),"")))))

 段々頭の中がこんがらがってきて、わからなくなってきましたｗ

 もう少し簡単な式にできそうな気もしますが、思いつかない．．．
 もう一列、作業列を増やせば可能なんですが、作業列多いのも鬱陶しいしｗ

 シフトを、日勤：１　夜勤：２　休日：３　としてスリム化も可能なんですが、
 もしカレンダー機能を利用して「休」の字を入れるんでしたら使えないのでやめました。
 （Alica）

Alicaさん、masabou5さん、ありがとうございました。「---」や「--」を関数とまではいかなくとも、何か特別な記号かエクセル独特の数式の略だと思っていた自分って･･･orz

Alicaさん、本当にありがとうございます♪

コメント返信：

[ 一覧(最新更新順) ]

[[20060124145553]] 『出勤簿の作成』（ぼう） ページの最後に飛ぶ

[[20060124145553]]　『出勤簿の作成』（ぼう）　ページの最後に飛ぶ