[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤務時間の集計表』（KJS） Excei2003 WindowsXP
勤務時間の集計表を作成中です。
タイムカードの印字データーを換算表を見て集計していますが、今後はエクセル関数を使っての方式にと考えています。
下記の様に表示したい(D,E,F,G,W,AB列にタイムカードのデーターを入力する)がうまくできません。
教えて下さい。 なお(H〜V)は非表示で使用と考えています。
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB 2 日付 曜日 休日 3 出社 退社 途中出 途中入 出社 退社 外出 再入 午前 休憩1 午後1 休憩2 午後2 休憩3 残業1 休憩4 残業2 休憩5 残業3 加算 事故 定時間 残業 休日 深夜 4 1 金 7:48 20:09 11:56 18:35 8:50 20:00 11:50 18:40 3:00 0:10 0:10 1:00 4:40 3:00 1:10 5 2 土 ◎ 10:18 20:58 10:20 20:50 1:40 0:50 2:10 0:10 2:20 0:20 1:00 0:10 1:40 0:10 0:10 2:50 6:10 6 3 日 ◎ 7 4 月 11:13 14:28 11:20 14:20 0:40 0:50 1:30 5:30 2:10 8 5 火 7:40 9 6 水 8:36 23:36 8:50 23:30 3:10 0:50 2:10 0:10 2:20 0:20 1:00 0:10 1:40 0:20 1:00 7:40 3:40 1:10 35 計 8:30 5:50 4:40 2:10 4:20 1:00 0:10 17:50 12:50 7:40 6:10 1:10
「条件は下記です」
1. 通常勤務の時間帯 8:50〜17:30 2. 残業時間帯 17:50〜22:00 3. 深夜残業時間帯 22:00以降 (AB列に手入力とする） 4. 休憩時間は （休憩1 12:00〜12:50）（休憩2 15:00〜15:10）（休憩3 17:30〜17:50）（休憩4 18:50〜19:00）（休憩5 20:40〜21:00) 5. 出社時刻・途中入時刻は10分単位で切り上げとする (但し8:50以前の出社は全て8:50とする) 6. 退社時刻・途中出時刻は10分単位で切り捨てとする 7. 勤務時間の計算は10分単位で切り捨てとする 8. 未入力及び (0:00) は非表示とする 9. A列,B列,C列,D列,E列,F列,G列,W列,AB列は、手入力とする 10. H列に計算用出社時刻を表示する 11. I列に計算用退社時刻を表示する 12. J列に計算用外出時刻を表示する 13 .K列に計算用再入時刻を表示する 14. L列に午前 ( 8:50〜12:00)間の勤務時間を表示する 15. N列に午後1(12:50〜15:00)間の勤務時間を表示する 16. P列に午後2(15:10〜17:30)間の勤務時間を表示する 17 .R列に残業1(17:50〜18:50)間の勤務時間を表示する 18. T列に残業2(19:00〜20:40)間の勤務時間を表示する 19. V列に残業3(21:00〜22:00)間の勤務時間を表示する 20. X列に通常勤務時間(7:40)に満たない時間（事故時間）を表示する (但しC列に◎印の有る休日は表示しない） 21. Y列に通常勤務の時間帯 (8:50〜17:30) の勤務時間を表示する (但しC列に◎印の有る休日の場合は、Y列に表示せずにAA列に表示する) 22. Z列に残業時間帯 (17:50〜22:00) の勤務時間を表示する (W列の時間も加算する） 23. 35行に合計を表示する
H4セル =IF(COUNT(D4)=1,IF(D4<=TIME(8,50,0),TIME(8,50,0),CEILING(D4,"0:10")),"")
I4セル =IF(COUNT(E4)=1,FLOOR(E4,"0:10"),"")
J4セル =FLOOR(F4,"0:10")
K4セル =CEILING(G4,"0:10")
見よう見まねで意味も解らずここまで作りましたが挫折しました。 もっと良い方法が有ればそれも教えて下さい。
==== (KJS) I4セル=・・・を改行していませんでした。
<下準備>
(1) 空白となるべきところには、0（又は限りなく0に近い値）を出しますので、
それを空白に見せる為に、以下の書式を設定してください。
セルの書式(表示形式)をユーザー定義で設定 ↓ [>0.003][h]:mm;[>-0.003]"";0.000;@
(2) 数式簡略化の為、L2:W2 のセル範囲に計算の基準となる時刻を <結果図>に倣って手入力してください。
<数式> (1) H4セル =IF(D4="",0,CEILING(MAX(D4,"8:50"),"0:10")) (2) I4セル =FLOOR(E4,"0:10") (3) J4セル =FLOOR(F4,"0:10") (4) K4セル =CEILING(G4,"0:10")
(5) L4セル =MAX(0,MIN($I4,M$2)-MAX($H4,L$2))-MAX(0,MIN($K4,M$2)-MAX($J4,L$2)) V4セルまでフィルコピー
(6) X4セル =MAX(0,"7:40"-(L4+N4+P4))*(C4<>"◎") (7) Y4セル =(C4<>"◎")*(L4+N4+P4) (8) Z4セル =R4+T4+V4+W4 (9) AA4セル =(C4="◎")*(L4+N4+P4)
<結果図> 行 __H__ _ I _ __J__ __K__ _ L _ __M__ __N__ __O__ __P__ __Q__ __R__ __S__ __T__ __U__ __V__ __W__ __X__ ___Y___ _ Z _ _AA_ _AB_ 1 2 8:50 12:00 12:50 15:00 15:10 17:30 17:50 18:50 19:00 20:40 21:00 22:00 3 出社 退社 外出 再入 午前 休憩1 午後1 休憩2 午後2 休憩3 残業1 休憩4 残業2 休憩5 残業3 加算 事故 定時間 残業 休日 深夜 4 8:50 20:00 11:50 18:40 3:00 0:10 0:10 1:00 4:40 3:00 1:10 5 10:20 20:50 1:40 0:50 2:10 0:10 2:20 0:20 1:00 0:10 1:40 0:10 0:10 2:50 6:10
(半平太) 2011/04/27 10:52
いかん、非表示にできるのはH:V列でしたね。
上記方法で処理したあと、L2:W2 のセル範囲を選択して 一括で一つ左の列へドラッグ移動してください。 （数式はそれに応じて変化します）
(半平太) 2011/04/27 11:14
早速ためしましたが質問が有ります。
D列(出社)に入力が無い日(休んだ日)にはH,L〜V,X〜AAのセルに [#NAME?] の表示になります。
私の入力が悪いのか、どうしたらよいでしょうか？
(KJS) 2011/04/27 22:40
>私の入力が悪いのか、どうしたらよいでしょうか？
H4セルの数式を正しくいれてください。(掲示板の数式をコピペすると間違いがないです）
多分これが、0(ゼロ)ではなく、O(オー)で入力されたのだと思います。 ↓ H4セル =IF(D4="",0,CEILING(MAX(D4,"8:50"),"0:10"))
(半平太) 2011/04/27 23:52
出来ました 感謝の一言しかありません 早速使います。
(KJS) 2011/04/28 6:53
通年で使用したいのですが月末が31日とは限りません。
A列に日付が無い場合には、X列の事故時間の表示が出ない様にするにはどうしたら良いでしょうか。
(KJS) 2011/4/28 22:28
X4セル =IF(A4,MAX(0,"7:40"-(L4+N4+P4))*(C4<>"◎"),0)
としてください。
(半平太) 2011/04/28 23:59
早速のご教授を頂きましてありがとう御座いました。
今後勉強して少しでも自分で作りたいと思います。
(KJS) 2011/04/29 9:45
年、月の入力で日付・曜日が入る様に作ったのですが日付が空白の場合はX列（事故）のセルの表示が
「#VALUE!」 になります。
A2セルに 2011
D2セルに 4 を入力
A6セル =DATE($A$2,$D$2,1)
A7セル =IF(MONTH($A$6)=MONTH($A$6+ROW()-ROW($A$6)),$A$6+ROW()-ROW($A$6),"")
A7セルをA36セルまでオートフィルしました。
A6〜A36までセルに書式を「ユーザー設定で d 」
B6セル =A6
B6〜B36までオートフィル
書式を「ユーザー設定で aaa 」にしました。
A36セルに関数が有るためにこのようになるのでしょうか？
日付は、手入力しなければダメなのでしょうか？
(KJS) 2011/4/30 10:15
>日付は、手入力しなければダメなのでしょうか？
そんなことはありません。
そう云う数式を入れているとのご説明がなかったので 数値を手入力しているのであろうと理解した結果です。
>X6セル =IF(A6,MAX(0,"7:40"-(L4+N4+P4))*(C6<>"◎"),0) ↓ 変更 X6セル =IF(A6="",0,MAX(0,"7:40"-(L6+N6+P6))*(C6<>"◎"))
(半平太) 2011/04/30 23:00
あとだし条件でお手数をおかけして申し訳ありません。
(KJS) 2011/4/30 23:43
使って気が付いたのですが、35行に時間としての合計を出していますが 36行に数字として表示する様にしました。 X36セル =(SUM(X4:X34))*24 と入力 同じように Y36セル Z36セル AA36セル AB36セル にも入力しました。 関数及びセルの書式は同じにしたのですが、X36セル のみ空白にならず 0.00 と表示します。 但し、C列に休日の表示 ◎ を全て入れると空白になります。 良い方法が有れば教えて頂きたいと思います。 (KJS) 2011/5/11 22:00
＞=(SUM(X4:X34))*24 ↓ =ROUND(SUM(X4:X34)*1440,0)/60
としてみてください。
(半平太) 2011/05/11 22:29
ありがとうございました。 出来ました。大変お世話になりました。
(KJS) 2011/5/11 22:57
１ヶ月間使用しました。
D列とE列の両方にデーターの入力が無いと誤表示するとして使用してきましたが D列に入力が無く、E列に入力が有る場合には D列に「8:50」の入力が有るようにL列〜V列に表示されます。 D列に入力が無い場合には、L列〜V列の表示を無くせますか？ 前回から期間が開きましたが宜しくお願い致します。 (KJS) 2011/6/7 22:15
KJSさんとこの就業規則を知っている訳ではないので、ちょっと分かりません。
> D列とE列の両方にデーターの入力が無いと誤表示するとして使用してきましたが (1)何がどう誤表示されているのですか？
> D列に入力が無く、E列に入力が有る場合には D列に「8:50」の入力が有るようにL列〜V列に表示されます。 > D列に入力が無い場合には、L列〜V列の表示を無くせますか？ (2)出社時刻がないのに、退社時刻があるという事情が飲み込めませんが、 技術的には、この様に、=(D4<>"")*(今までの数式) と云う形にすればいい様な気がします。 ↓ L4セル =(D4<>"")*(MAX(0,MIN($I4,L$2)-MAX($H4,K$2))-MAX(0,MIN($K4,L$2)-MAX($J4,K$2)))
(半平太) 2011/06/07 23:33
出来ました。大変お世話になりました。
=(D4<>"")*(今までの数式) の意味を教えていただけないでしょうか。 (D4<>"")は、 D4が空白で無い場合は との事ですよね * は AND でなくて 掛け算をするのはどうしてですか？ 初心者ですので 宜しくお願い致します。 (KJS) 2011/06/08 6:50
IF関数を使って処理する時は → =IF(D4<>"",今までの数式,0) と云う形にしてください。
条件は「D列に入力が無い場合」と一つだけですので「AND」は必要ないと思います。
数式全体を見直すなら、ANDも必要になるかも知れませんが、作り直しは大変なので、 増築方式で済ます事にしました。
> =(D4<>"")*(今までの数式) の意味 今までの数式は、空白に見えるべきセルには「0 又は 0に非常に近い値」が 出る様に作ってあります。 従って、D列が空白の場合は、今までの数式に0を掛けてやればいい事になります。
(D4<>"")の部分は真偽値になります。つまり、TRUE か FALSE ですが、 真偽値は、四則演算に組み込まれるとそれぞれ 1 か 0 として扱われます。
(半平太) 2011/06/08 08:30
(半平太)さん ありがとうございました。 少しずつでも覚えたいと思います。 今後もいろいろ質問させていただきたいと思いますが、 「数式の意味も教えて下さい」 でも失礼になりませんよね。 (KJS) 2011/06/08 12:30
> 「数式の意味も教えて下さい」 でも失礼になりませんよね。
ここはエクセルのQ&A掲示板ですから、至極あたり前のことと思います。
(私が回答するかどうかは別ですけどね・・・私は解説が苦手なので)
(半平太) 2011/06/08 19:11
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.