[[20071210134052]]　『平日のみの時間集計』（やなせ）　ページの最後に飛ぶ

『平日のみの時間集計』（やなせ）

 下記のように勤怠表があります。
 残業分の平日のみ(土日祝日を除く)の合計を出したいのですが、SUMPRODUCT関数を使用してみましたが、
 エラー(#VALUE!)になってしまいます。
 どこがいけないのか、ご指摘頂けると幸いです。

 A	B	C	D	E	F	G		H

 日	曜日	出社時刻	退社時刻	休 暇	自宅作業	就業時間数	残業

 21	水	9:00	19:00			9.00		1.00	
 22	木	9:00	17:15			7.25		-0.75	
 23	金				2.00			2.00	
 24	土				3.00			3.00	
 合計								0.25	←平日のみの合計を出したい。

 日
1日=DATEVALUE(A2&"/"&E2&"/1")
1日以降=A6+1
末日=IF(MONTH(DATE($A$2,$E$2,ROWS($A$6:A36)))=$E$2,DATE($A$2,$E$2,ROWS($A$6:A36)),"")
曜日=A6
出勤・退社時間・休暇・自宅作業は手入力
就業時間数=IF(C6="代",8,IF(COUNT(D6:E6)<2,"",IF(C6="出",0,IF(D6>=TIMEVALUE("12:00"),IF(D6>=TIMEVALUE("13:00"),IF(D6>=TIMEVALUE("16:00"),E6-D6,E6-D6),E6-D6),IF(E6<TIMEVALUE("13:00"),E6-D6,E6-"1:00"-D6))*24)+G6))
残業=IF(COUNT(D6:E6)<2,"",IF(C6="出",0,IF(OR(WEEKDAY($A6,2)>5,COUNTIF($C$47:$D$82,B6)>0),IF(COUNT(D6:E6)<2,"",IF(D6>=TIMEVALUE("12:00"),IF(D6>=TIMEVALUE("13:00"),IF(D6>=TIMEVALUE("16:00"),E6-D6,E6-D6),E6-D6),IF(E6<TIMEVALUE("13:00"),E6-D6,E6-"1:00"-D6))*24),IF(C6="欠",IF(H6>0,H6,""),SUM(F6,H6)-TIMEVALUE("8:00")*24))))

 平日のみの残業を求めようと作成した式=SUMPRODUCT((WEEKDAY(A6:A36,2)<6)*J6:J36)
 よろしくお願いいたします。

 > 残業分の平日のみ(土日祝日を除く)の合計を出したいのですが、
 >SUMPRODUCT関数を使用してみましたが、エラー(#VALUE!)になってしまいます。
 >どこがいけないのか

 掛け算するデータの中に空白文字("")があるためと思います。

 文字を無視させるよう、SUMPRODUCT「本来」の引数の書き方をする必要があります。

 =SUMPRODUCT((WEEKDAY(A6:A36,2)<6)*1 , J6:J36)
  　　　　　　　　　　　　　　　　↑↑

 (半平太)　J列はサンプルには見当たりませんが、その列に空白文字がありそう、、、

 理由は空白文字("")をWEEKDAYのシリアル値として扱っているため、エラー
 SUMPRODUCT「本来」の引数の書き方でもエラーは無視してくれないので

 とりあえず、たたき台として
 =SUMPRODUCT((TEXT(A6:A36,"aaa")={"月","火","水","木","金"})*1,J6:J36)

 By しげちゃん

 半平太さん、しげちゃんさんご指摘ありがとうございます。
日・曜日欄にも空白セルはありますが、J列にも空白セルがあります。
J列にも空白が存在したら、SUMPRODUCT関数は使用できないでしょうか？
しげちゃんさんにご提示頂いた式でもエラーになってしまったので。
(やなせ)

 大変失礼しました、配列の範囲に問題がありましたね

 =SUMPRODUCT(ISNUMBER(MATCH(TEXT(A6:A36,"aaa"),{"月","火","水","木","金"},0))*1,J6:J36)

 By しげちゃん

 おはようございます。しげちゃんさんありがとうございました。
エラー表示しなくなりました。追加質問になってしまい、申し訳ないのですがもう一つご教授ください。
祝日の場合も集計から除きたいのですがどんな関数を使えばよいですか？
祝日リストは別に作り、条件付書式で塗りつぶしをしています。

 よろしくお願いいたします。
（やなせ）

 こんな感じでも良かったかな
 =SUMPRODUCT(ISNUMBER(0/(WEEKDAY(A6:A36,2)<6))*1,J6:J36)

 >祝日リストは別に作り
 =SUMPRODUCT(ISNUMBER(0/((WEEKDAY(A6:A36,2)<6)*(COUNTIF(祝日リスト,A6:A36)=0)))*1,B1:B10)

 こんな感じとは思いますが、お薦めしません

 By しげちゃん

 しげちゃんさん、ありがとうございました。
 思うような結果が出ました。因みに、
 >こんな感じとは思いますが、お薦めしません
 との事ですが、(たぶん聞いても分からないと思うのですが)なぜですか？
（やなせ）

 >末日=IF(MONTH(DATE($A$2,$E$2,ROWS($A$6:A36)))=$E$2,DATE($A$2,$E$2,ROWS($A$6:A36)),"")
 末日=IF(MONTH(DATE($A$2,$E$2,ROWS($A$6:A36)))=$E$2,DATE($A$2,$E$2,ROWS($A$6:A36)),0)
 として、０値にチェックまたは、A列の書式を　D;;; にすれば
 何も問題がなかったのでは?

 但し「0」は土曜日扱いになりますが今回の場合は問題ないのでは

 B列の曜日を
 B6=IF(COUNTIF(祝日リスト,A6),"祝",TEXT(A6,"AAA"))

 =SUM(SUMIF(A6:A36,{"月","火","水","木","金"},J6:J36))

 工夫すればもっと簡単になると思いますよ（軽くなりますよ）

 一回切りであればいいのですが、沢山使用すると重くなります

 By しげちゃん

コメント返信：

[ 一覧(最新更新順) ]

[[20071210134052]] 『平日のみの時間集計』（やなせ） ページの最後に飛ぶ

[[20071210134052]]　『平日のみの時間集計』（やなせ）　ページの最後に飛ぶ