[[20110919071607]]　『エクセルで日報を作りたい』（TOM）　ページの最後に飛ぶ

『エクセルで日報を作りたい』（TOM）

エクセルで日報を作りたいとおもっています。

  日にち　  　9/1   9/2    9/3    9/4　　　9/5    9/6    9/7
　 
始業時間　　8:30  8:30   12:45  12:45   8:30   8:30   12:45
　
終業時間　 17:00 18:15   17:00  18:15   21:15  21:30  21:45
　 
就業時間　　7.75  8.75    4.25   5.25   11.75  11.75   8.25
　
残業時間　  0     1       0      1       4      4      4

始業時間と終業時間を入力すると就業時間と残業時間が計算されて入力されるような数式はできないでしょうか。
時間は、15分刻みです。
休憩時間は12:00～12:45 の45分間
　　　　　17:00～17:15 の15分間
　　　　　21:15～21:45 の30分間

休憩時間を求めて、就業時間と残業時間を求めてみようと、
IF(OR(B2="",B3=""),"",(IF(B2<0.5,IF(B3<0.71,IF(B3<=0.5,"0:00","0:45"),"1:00"),IF(B3<0.71,"0:00","0:30"))))と、途中までは挑戦してみたのですが、ここまでが限界でした。どうか、良い方法があれば教えてください。
宜しくお願いします。
OSのバージョンは、Windows Vista エクセルのバージョンは
Excel2007です。

 「日にち」のセルがA1セルから始まっているとして。。。
 B4　就業時間
=TEXT(MAX(0,MIN("17:00",B3)-B2)-MAX(0,MIN("12:45",B3)-MAX("12:00",B2)),"h:mm")*24+B5
 B5　残業時間
=TEXT(MAX(0,B3-MAX("17:15",B2))-MAX(0,MIN("21:45",B3)-MAX("21:15",B2)),"h:mm")*24

 (HANA)

HANA 様

ありがとうございました。

 できました!!!!
この数式で日報を作成してみます。
また、わからないときは、教えてください。
宜しくお願いします。

　　　　　　　　　　　　　TOM

すみません…。
今、日報作成中です。
今朝は、うまくいったのですが、
(今朝は普通に表を作成し、教えていただいた数式を入力しました)
正式な日報を作成していたら、うまくいきません。
エラーがでたり、うまく時間が計算されなかったり…。
変えたところというと、条件付き書式を使ったりくらいです。
(別シートに休日を入力し、休日は、日にちと曜日にに色がつくようにしています)
何が問題なのか分からず苦戦してします。
どうぞ教えてください。(TOM)

 条件付き書式は関係ないと思いますが。。。
 ご提示のデータでしか確認していないですし
 時間のまるめも行っていないので、そのあたりの問題かもしれません。

 具体的に、何処にどの様な時間が入っていて
 どの様な数式を入れている時に
 どうなるべき所が、どの様な結果になるのか。
 　また、単純に　就業時間＝終業時間－始業時間－休憩時間　で求められない様な場合は
 　どの様に計算してその様に成るべきなのかのご説明をつけて貰えると良いのですが。。。
 最初にサンプルデータを載せて下さった様に教えてください。

 表組みは、エクセルで作って貼り付けて貰うと良いと思います。
 また、文頭に半角スペースを入れると、改行を入れた所が改行されて表示されます。
_←この半角アンダーバーの所に　半角スペースを入れてください。

 (HANA)

返信が遅くなってすみません…。
昨日、急用ができて出かけていました。
下記がエラーになった表です。
10/1の始業時間はM6セルから始まっています。
それぞれの数式を下記に示します。

 月日	前月調整	10/1	10/2	10/3	10/4	10/5	10/6	10/7
		 （土）	（日）	（月）	（火）	（水）	（木）	（金）
始業時刻		8:30	12:45	8:30	8:30	8:30	8:30	8:30
就業時刻		17:00	17:00	19:15	18:45	17:00	18:15	20:30
実働時間		#VALUE!	#VALUE!	9.50 	9.00 	#VALUE!	8.50 	10.75 
平日残業		#VALUE!	#VALUE!	1.75 	1.25 	#VALUE!	0.75 	3.00 
休日出勤
(載せ方が下手ですみません。曜日がずれてます)

日にち→ +MONTH($X$1)&"/"&DAY($X$1)

 曜日→+IF(M11=2,"（月）",IF(M11=3,"（火）",IF(M11=4,"（水）",IF(M11=5,"（木）",IF(M11=6,"（金）",IF(M11=7,"（土）",IF(M11=1,"（日）"," ")))))))	
 実働時間→IF(OR(M6="",M7=""),"",TEXT(MAX(0,MIN("17:00",M7)-M6)-MAX(0,MIN("12:45",M7)-MAX("12:00",M6)),"h:mm")*24+M9)

 平日残業→IF(OR(M6="",M7=""),"",TEXT(MAX(0,M7-MAX("17:15",M6))-MAX(0,MIN("20:00",M7)-MAX("19:45",M6))-MAX(0,MIN("21:30",M7)-MAX("22:00",M6)),"h:mm")*24)

と入力をしたら、実働時間と平日残業で上記のエラーになりました。
どこが間違っているかを教えてください。

後、また高度なことを質問するのですが、土曜・日曜などの指定した休日に出勤した場合、実働時間と休日出勤に時間が入るようできるでしょうか。休日の場合「実働時間＝休日出勤」としたいです。

月日 10/1 10/2 10/3

		（土）	（日）	(月）
始業時刻	8:30	8:30	8:30	
就業時刻	17:00　　19:00    19:00
実働時間	7.75	9.5	 9.5	 
平日残業			1.75		 	 
休日出勤	7.75     9.5
こういう風なこともできるでしょうか。
いろいろと質問して申し訳ありません。
どうぞよろしくお願いいたします。
(TOM)

　上記、エラーの件、わかりました!!!
時間が文字列になっていました。
それを変えたら、うまく計算してくれました。

 あと、休日出勤した場合
(土曜・日曜と別シートに祝日等の指定をして条件付き書式を設定しています)
実働時間と休日出勤に時間が入る方法があれば、教えてください。
色々すみません。
お手数をおかけします。
(TOM)

 原因が分かりましたか、良かったです。
 なかなか返信が出来なくて済みません。

 追加のご質問の件ですが
 ＞条件付き書式を設定しています
 具体的にはどの様にしていますか？

 数式で設定している（条件1のみ使用）なら
 =if(条件付き書式の条件,実働時間のセル,"")
 の様にしてみて下さい。

 ちなみに、曜日の表示は　
 日付のセルを = で参照して、表示形式で (aaa) を設定。
 或いは、TEXT関数を使って =TEXT(日付のセル,"(aaa)") の様にも出来ると思います。
 条件付き書式の条件として、曜日のセルを利用しているのなら、TEXT関数を使う方が
 現在の状態に近い状態が保てて良いのかもしれません。

 (HANA)

　ありがとうございます。
曜日表示は、TEXT関数にしました。

 (ちなみにTEXT(WEEKDAY(D4),"aaa")としました。)

あと、条件付き書式の件ですが、3つ条件があります。

 ①WEEKDAY(B4)=7
 ②WEEKDAY(B4)=1
 ③MATCH(B4,祝日,0)

ORで挑戦してみました。(8/1がB4セルです)

	8月1日	8月2日	8月3日	8月4日	8月5日	8月6日	8月7日
	月	火	水	木	金	土	日
始業時間	12:45	12:45	8:30	12:45	8:30	8:30	8:30
終業時間	17:00	17:00	12:00	19:00	19:45	20:00	21:00
就業時間	4.25	4.25	3.5	6	10.25	10.25	11.25
残業時間	0	0	0	1.75	2.5	2.5	3.5
休日	4.25	#N/A	3.5	#N/A	#N/A	#N/A	#N/A
8/1と3日を祝日とし、6日が土曜日と7日が日曜日と設定し、条件付き書式で色をつけています。
休日にIF(OR(WEEKDAY(B4)=1,WEEKDAY(B4)=7,MATCH(B4,祝日,0)),B8,"")として、コピーしたら、
エラーがでてしまいます。

できるなら下記のようにできたらいいとおもっています。

	8月1日	8月2日	8月3日	8月4日	8月5日	8月6日	8月7日
	月	火	水	木	金	土	日
始業時間	12:45	12:45	8:30	12:45	8:30	8:30	8:30
終業時間	17:00	17:00	12:00	19:00	19:45	20:00	21:00
就業時間	4.25	4.25	3.5	6	10.25	10.25	11.25
残業時間	0	0	0	1.75	2.5		
休日	4.25		3.5			10.25	11.25

土曜・日曜・祝日は、就業時間と休日のみ時間が記載され、残業時間分は記載されない。

 土曜・日曜・祝日以外は、就業時間と残業時間が記載され、休日は記載されない。
 最後には、時間の合計をしたいのでセルの空白や色を白に変えるのでは無理なのかと・・・・。

いろいろ質問して申し訳ありません。
挑戦はしてみるのですが、なかなかいい方法が見つかりません。

 どうか、教えてください。
 よろしくお願いします。
(TOM)

 祝日と名前をつけた範囲は、同じブック内に有りますね？

 関数がエラーを返す時は、エラーを返す理由が有ります。

 前回は #VALUE! エラーでした。
 これは、値でない物を値として扱おうとした時に
 エクセル君が「値(VALUE)じゃないんだけど！！」と不満を表現しています。

 今回は #N/A エラーです。
 これは、たとえば MATCH関数を完全一致で使った時に　検索値が見つからない時表示されます。

 ISNA関数等と組み合わせても良いですが、
 　－－－NOT(ISNA(MATCH(・・・)))で、MATCH関数がエラー値で無い時
 　　　　（検索した日付が、祝日範囲に有った場合）TRUEに成ります。－－－
 COUNTIF関数を使ってみてください。

 IF(OR(WEEKDAY(B4)=1,WEEKDAY(B4)=7,COUNTIF(祝日,B4)),B8,"")

 ちなみに↓に関してですが
 ＞土曜・日曜・祝日は、就業時間と休日のみ時間が記載され、残業時間分は記載されない。 
 土日祝日は、残業せずに帰れよ！！残業してもその分はカウントしないから。
 って事ですか？

 ＞曜日表示は、TEXT関数にしました。 
 そしたら、曜日の確認は　B5="土",B5="日"　と文字で指定すると後で分かりやすいかもしれません。

 (HANA)

　ありがとうございます!!!
COUNTIFでできました!!!!
全然思いつかない方法だったので、びっくりです…。

 ちなみに↓に関してですが
 ＞土曜・日曜・祝日は、就業時間と休日のみ時間が記載され、残業時間分は記載されない。 
 土日祝日は、残業せずに帰れよ！！残業してもその分はカウントしないから。
 って事ですか？

　↑これは、ここの「残業時間」は、「平日残業」という意味で

 「休日」は、「休日出勤」という意味です。
 (略してしまって申し訳ありません)
 休日に残業をしても残業はカウントします。

(8/1　8/2　祝日　8/6　土曜　8/7 日曜としています)

	8月1日	8月2日	8月3日	8月4日	8月5日	8月6日	8月7日
	 月	 火	水	木	金	土	日
始業時間	12:45	12:45	8:30	12:45	8:30	8:30	8:30
終業時間	17:00	17:00	12:00	19:00	19:45	20:00	21:00
実働時間	4.25	4.25	3.5	6	10.25	10.25	11.25
平日残業	0	0	0	1.75	2.5		
休日出勤	4.25		3.5			10.25	11.25

 ↑平日の残業は「平日残業」にのり、休日は、「平日残業」は空白で「休日出勤」に
 実働時間(定時間+残業)をのせたいのです。
 (休日出勤=実働時間となると思います)

なかなかうまく説明ができなくてすみません。

 色々省略してしまったので、申し訳ないです。
 上記の休日出勤はうまくいく方法があるでしょうか?
 (TOM)

 あ、すみません。分かりました。

 残念ながら、現在の式の作りは
 17:00以前の時間（通常業務時間）と
 17:15以降の時間（残業時間）を求め
 就業時間は、その二つの時間を足しています。

 簡単なのは、
 　17:15以降の時間をどこかのセルで計算し
 　就業時間は今まで同様、17:00以前の時間に足して表示。
 　平日は、その時間を残業時間に参照。
 だと思いますが、どうでしょう。

 (HANA)

　HANA 様

　ありがとうございます。

 別のセルに残業分を計算し、参照するようにしました。
 できました!!!!
 色々と、ご迷惑をおかけしてすみませんでした。
 また、わからないことがあった時は、教えてください。
 よろしくお願いします。

(TOM)

コメント返信：

[ 一覧(最新更新順) ]

[[20110919071607]] 『エクセルで日報を作りたい』（TOM） ページの最後に飛ぶ

[[20110919071607]]　『エクセルで日報を作りたい』（TOM）　ページの最後に飛ぶ