[[20060821141106]]　『個人別作業工数表』（ねね）　ページの最後に飛ぶ

『個人別作業工数表』（ねね）

工数表を作っています。
　　　

       A   B  C  D  E  F  G  H          
 1　　2006 年　7 月
 2
 3　          1  2　3　4　5　6 .........
 4　          土 日 月 火 水 木	 	 
 5営業       　　　　1
 6開発       　　　3.5
 7ﾌﾟﾛｼﾞｪｸﾄA  　　　　2
 8ﾌﾟﾛｼﾞｪｸﾄB  　　　　4
 9合計　　　　　　　10.5
10
11出社時間　　　　　　8:30
12帰社時間          20:00
13実労働時間        10.5

質問１）
月を変えると自動的に曜日がでて土日祝祭日の列に網掛けが入るようにするには
C4にどのような式を入れるとよいですか？
=IF(C3=" "," ",IF(WEEKDAY(DATE($A$1,$C$1,C$3))=7," 土",IF(WEEKDAY(DATE($A$1,$C$1,C$3))=1," 日"," ")))
だと土日しか表示されません。
祝祭日は別のシートに書き出し表を作ってあります。

質問２）E13(実労働時間）には昼休み11:45-12:40(55分休)と17:15-17:30(15分休）を除く計算式（マクロ？）がありますか？

 回答1）
 C3=TEXT(DATE($A$1,$C$1,0)+COLUMN(A$1),"d")
 C4=TEXT(DATE($A$1,$C$1,0)+COLUMN(A$1),"aaa")
 右にフィルドラッグ
 (ROUGE)

(ROUGE)さん、ご回答ありがとうございます。すごいです。望みどおりですo(^-^)o。で、「右にフィルドラッグ」ってどうするのですか(ﾟ0ﾟ;。関数はまったくわからなくてすみません。

 回答1)_2
 土日祝祭日に網掛を設定する方法
 ツール-->アドイン-->分析ツールにチェックを入れてOK。
 2行目を作業行として使います。
 祝祭日の表はSheet2のA1:A100にあるものとして作成しています。
 C2=WORKDAY(DATE($A$1,$C$1,C$3-1),1,Sheet2!$A1:$A100)<>DATE($A$1,$C$1,C$3)
 作業行が邪魔であれば、表示形式を「;;;」とする。
 C3:C4を選択した状態で条件付書式を設定。
 数式が =C$2 としてお望みの書式を設定してOK。
 C2:C4を選択して、右にフィルドラッグ。
 【フィルドラッグの方法】
http://www.excel.studio-kazu.jp/lib/e2aw/e2aw.html#Fill
 こちらをご覧下さい。
 (ROUGE)

 回答1)_2_HANA案

 祝祭日の表は、それが入力されているセルを選んで
 メニュー・挿入→名前→定義　で
 「祝祭日」としておいて下さい。

 回答1）の(ROUGE)さんの式をC3・C4に入力。(しっかり使わせて頂きます)

 C3・C4を選択し、メニュー・書式→条件付き書式

 条件１
　　数式が　▼　=MONTH(DATE($A$1,$C$1,0)+COLUMN(A$1))<>$C$1
　　　　　書式(F)で、文字色を白に
 条件２
　　数式が　▼　=(WEEKDAY(DATE($A$1,$C$1,0)+COLUMN(A$1),2)>5)+COUNTIF(祝祭日,DATE($A$1,$C$1,0)+COLUMN(A$1))
　　　　　書式(F)で、お好みの網掛けに

 C3・C4をAG列までフィルドラッグ。

 ・・・・でうまくいきますか？

 (HANA)

(ROUGE)さん、「フィルドラッグ」って使用していました。呼び方がわからなかった(;^_^Aだけでした。(HANA)さんありがとうございます。条件２の方法で祝祭日に好みの網掛けと赤字に設定してできました。２月や４月などの31日でない月は30日の後、1日と表示されてしまうのですが、表示しないようにするにはどうすればよいですか？

 横から（？）失礼します。
 > ２月や４月などの31日でない月は30日の後、1日と表示されてしまうのですが、
 > 表示しないようにするにはどうすればよいですか？
 HANAさんの条件１がご希望の処理のように思いますが、いかがでしょうか？
 (ROUGE)

 回答2)
 E13=(E12-E11-MIN("12:40",MAX("11:45",E12))+MAX("11:45",MIN("12:40",E11))
     -MIN("17:30",MAX("17:15",E12))+MAX("17:15",MIN("17:30",E11)))*24
 ただし、期待値10.5とはなりません。
 （休憩時間が1:10あるため）
 (ROUGE)

(ROUGE)さん、HANAさんの条件１に変更すると、1日の表示は消えますが網掛けになってしまいます。網掛けも白に設定すればそれぞれの月の日数分の表示はされますが、希望の網掛けと赤字も消えてしまいます。それぞれの月の日数表示を正しくかつ祝祭日の網掛けと赤字ができる方法って無理なのでしょうか？
回答2)ありがとうございます。休憩時間は昼休みと夕方以外に夜21:30-21:45、深夜0:45-1:00にもあります。休憩時間の一覧リストを利用して計算する事はできますか？でもどのようにリストを書けばいいんだろう？

 > HANAさんの条件１に変更すると、1日の表示は消えますが網掛けになってしまいます。
 > 網掛けも白に設定すればそれぞれの月の日数分の表示はされますが、希望の網掛けと赤字も消えてしまいます。
 > それぞれの月の日数表示を正しくかつ祝祭日の網掛けと赤字ができる方法って無理なのでしょうか？
 条件１と条件２の順番を守っていますか？
 条件１が優先されますが、条件１に合致しない場合には条件２となり、網掛されると思いますが？

 > 休憩時間は昼休みと夕方以外に夜21:30-21:45、深夜0:45-1:00にもあります。
 やぼったい式ですが、、、
 E13=(E$12-E$11+(E$11>E$12)-(MIN("12:40",MAX("11:45",E$12+(E$11>E$12)))-MAX("11:45",MIN("12:40",E$11)))
    -(MIN("17:30",MAX("17:15",E$12+(E$11>E$12)))-MAX("17:15",MIN("17:30",E$11)))
    -(MIN("21:45",MAX("21:30",E$12+(E$11>E$12)))-MAX("21:30",MIN("21:45",E$11)))
    -(MIN("25:",MAX("24:45",E$12+(E$11>E$12)))-MAX("24:45",MIN("25:",E$11))))*24
 (ROUGE)

(ROUGE)さん、条件１と条件２を同時に指定するのですね(;^_^A。「条件付き書式」に追加があったのに気が付き、今度はちゃんと指定通りに表示されましたm(-_-)m。時間の表示は、申告作業時間と実労働時間を統一すれば同じになりますか？申告作業時間を「3.5」としないで「3：30」という感じで。実労働時間「10.3」は１０時間３０分の事で、申告作業時間合計「10.5」と同じ意味ですよね？

すいません。時間の表示でこんがらがっていますo(;-_-;)o

 申告作業時間が何かは分かりませんが、、、
 h:mm形式で表示させたいのであれば、最後の*24をとって下さい。
 表示形式は[h]:mmとします。
 (ROUGE)

(ROUGE)さん、時間の表示は最初の表示形式にします。だんだんできてきました。もう一つ合計の下(C14)に基本勤務時間（8:15-17:15の7h50m）をカレンダーに連動し、土日祝祭日はブランクになるように表示したいのですが、どうすればよいですか？

 C14=IF(OR(MONTH(DATE($A$1,$C$1,0)+COLUMN(A$1))<>$C$1,(WEEKDAY(DATE($A$1,$C$1,0)+COLUMN(A$1),2)>5)
    +COUNTIF(祝祭日,DATE($A$1,$C$1,0)+COLUMN(A$1))),"",MIN("17:15",C$12+(C$11>C$12))
    -MAX("8:15",C$11)-(MIN("12:40",MAX("11:45",C$12+(C$11>C$12)))-MAX("11:45",MIN("12:40",C$11))))
 これでどうでしょうか？
 (ROUGE)

(ROUGE)さん、C14に上記の式を入れたら「0.0」と表示されます。「7.8」と表示したいのですができません。あと、２月から６月とか月を変更すると土日祝祭日は表示しないのですが、平日にも表示できない所がでてきます。式は入っているのに「0.0」を表示しなく、表示しないカーソルで表示されているセルのカットアンドペーストすると「0.0」が表示されます。

 これでどうですか？ファイナルアンサー？
 (ROUGE)
 C13=IF(COUNT(C$11:C$12)<>2,"",(C12-C11-MIN("12:40",MAX("11:45",C12))+MAX("11:45",MIN("12:40",C11))
    -MIN("17:30",MAX("17:15",C12))+MAX("17:15",MIN("17:30",C11)))*24)
 C14=IF(OR(COUNT(C$11:C$12)<>2,MONTH(DATE($A$1,$C$1,0)+COLUMN(A$1))<>$C$1,(WEEKDAY(DATE($A$1,$C$1,0)+COLUMN(A$1),2)>5)
    +COUNTIF(祝祭日,DATE($A$1,$C$1,0)+COLUMN(A$1))),"",(MIN("17:15",C$12+(C$11>C$12))
    -MAX("8:15",C$11)-(MIN("12:40",MAX("11:45",C$12+(C$11>C$12)))-MAX("11:45",MIN("12:40",C$11))))*24)

(ROUGE)さん、お手間を取らせて申訳ございません。C14の表示はC13が「0」でも、平日であれば、「7.83」を表示したいのですが、可能でしょうか？

 ざんね～ん。みのさん、、、いや、ねねさん、やられた～

 それでは仕切りなおして、、、
 それならば、14行目にはあらかじめ7.83とにゅうりょくしておいて、条件付書式を設定すればよいのでは？
 HANAさんの条件付書式の数式をつなげて、
 =OR(条件１の数式,条件２の数式)
 文字色を白にする。
 ところで、8:15～17:15（昼休憩55分）ということは、8:05-->8.083となるように思いますが？
 (ROUGE)

(ROUGE)さん、ごめんなさぁーいm(T_T)m。C14に「7.83」と入力して条件付書式で条件１にHANAさんの２つの数式を「,」でつなげるのですか？なかなかうまくいかないですo(;-_-;)oその場合、表示されない所は1ｹ月の基本勤務時間にはカウントされないんですか？勤務時間は8:30-17:15で昼休憩55分です。

 別につなげなくてもいけます。
 同じように条件１、２を入力して、それぞれ文字色を白にしてしまえばOKですｗ
 (ROUGE)

(ROUGE)さん、なかなか終わらなくてすみません。表示はうまくいきました。でも、月合計がブランクになっているセルの数字もカウントしてしまうのですが、表示されたい所は「０」になるという事はできますか？ほんとに申し訳ございません。

 計算されているのですね。
 それでは、条件付書式案は取り下げます。
 C14=IF(OR(COUNT(C$11:C$12)<>2,MONTH(DATE($A$1,$C$1,0)+COLUMN(A$1))<>$C$1,(WEEKDAY(DATE($A$1,$C$1,0)+COLUMN(A$1),2)>5)
    +COUNTIF(祝祭日,DATE($A$1,$C$1,0)+COLUMN(A$1))),"","7:50"*24)
 (ROUGE)

(ROUGE)さん、条件付書式は削除しました。今回頂いた数式を当てはめましたが、月を変更して平日の作業時間ががブランクだとC14が表示されません。カレンダー通りの平日には｢7.83｣を表示したいのですが。。。だんだん申し訳なくなってきました(T_T)。でも、がんばらせてくださいm(-_-)m。

 ORの中のCOUNT(C$11:C$12)<>2を削除して下さい。
 (ROUGE)

 (ROUGE)さん、フォローありがとうございます。

 現在の流れとは全く関係ないのですが
 ←この部分に半角スペースを入れると
 改行できますよ。

 (HANA)

(ROUGE)さん、おはようございます。ファイナルアンサーとなりましたo(^-^)o。本当に辛抱強くお付き合い頂きありがとうございました。言われるままに数式を入れていって工数表が出来上がっていくのに、感動の連発です。教えていただいた数式をゆっくり紐解いて、エクセルでの表作成に役立てたいと思います。本当にありがとうございましたm(n_n)m。

再び、でてきてしまいました。休暇の行に12:40に出社したら「AM半休」、12:45で帰社したら「PM半休」、出社時間が「0」の場合は「全休」と表示するには、B3にはどのような数式を入れればよいでしょうか？IF関数？AND？OR？

 　   A       B      C     D
 1 出社時間 12:40　 8:15　0:00 
 2 帰社時間 17:15  12:45
 3 休　　暇 AM半休 PM半休  全休

 B3=IF(B1="","",IF(TEXT(B1,"[h]:mm")="12:40","AM半休",
                IF(TEXT(B2,"[h]:mm")="12:45","PM半休","全体")))

 このくらいしか思いつかないんですが。
 ちなみに、時間は必ず12:40出社 と 12:45退社ですか？

 (HANA)

(HANA)さん、ありがとうございます。時間を間違えました。出社時間が12:40だと「AM半休」で帰社時間が11:45だと「PM半休」でした。教えていただいた数式の時間を変更したらできました。
すみません。もうひとつお願いします。(ROUGE)さんに回答頂いたのですが、出社時間と帰社時間を入力すると昼休み(55分)、夕方(15分）、夜(15分）、深夜(15分）の休憩時間を差引いて実労働時間を計算する数式ですが、土日祝祭日にも実労働時間が休憩分(1.7)？が残ってしまいます。実労働時間を計算しつつ、カレンダーの土日祝祭日はブランクにする事は可能ですか？

実労働時間（休憩時間を除く）の数式↓にカレンダーの土日祝祭日がブランクになる数式です。

 E13=(E$12-E$11+(E$11>E$12)-(MIN("12:40",MAX("11:45",E$12+(E$11>E$12)))
　　　-MAX("11:45",MIN("12:40",E$11)))-(MIN("17:30",MAX("17:15",E$12
　　　+(E$11>E$12)))-MAX("17:15",MIN("17:30",E$11)))
　　　-(MIN("21:45",MAX("21:30",E$12+(E$11>E$12)))
　　　-MAX("21:30",MIN("21:45",E$11)))
    　-(MIN("25:",MAX("24:45",E$12+(E$11>E$12)))
　　　-MAX("24:45",MIN("25:",E$11))))*24

 E13=IF(E14="","",現在E13に入っている数式)

 でどうですか？
 E14は土日祝祭日ブランクになっているんですよね？

 (HANA)

(HANA)さん、すごいです！！できました。エクセルの学校ってすばらしいです！！ほんとに、ほんとにありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20060821141106]] 『個人別作業工数表』（ねね） ページの最後に飛ぶ

[[20060821141106]]　『個人別作業工数表』（ねね）　ページの最後に飛ぶ