関数式を教えて下さい(Excel2000)

質問

よろしくお願い致します。
以下の様なExcelデータがあります。

2/2 月 8.00
2/2 火 10.5
2/3 水 8.00
2/4 木 9.00
2/5 金 8.00
2/6 土 0.00
2/7 日 0.00
2/8 月 8.00
2/9 火 8.00
2/10 水 8.00
・
・
・
アルバイトをした時間を管理しているのですけど、過去直近の5日間の総労働時間を当日労働時間の隣に表示させたいです。

但し以下条件で．．
・土曜、日曜は休みなので、過去直近5日間にはカウントしない。
・でも土曜、日曜でも働いていれば(労働時間が入力されていればカウント対象になる)

例えば、今日が2/9(火)だとしたら、過去直近5日間は、2/2～2/5と2/8になり、その5日間の合計を出したいです。
（但し、もし2/6(土)に働いていたら、2/3～2/6と2/8の5日間がカウントの対象となる）

うまく説明出来ませんが、ご不明な点があれば、補足させていただきます。

何卒よろしくお願いします。

回答No.1

mshr1962 · ベストアンサー

祝日や休業日を考慮しない場合は
=WORKDAY(TODAY(),-5)
で直近の5日の開始日を求めることができます。
合計を出す場合は
「前日までの累計から直近の5日以前の累計を減算する」
=SUMIF(A$1:A$100,"<"&TODAY(),B$1:B$100)-SUMIF(A$1:A$100,"<"WORKDAY(TODAY(),-5),B$1:B$100)
または
「前日以前で且つ直近5日以降の時間を合計する」
=SUMPRODUCT(A$1:A$100<TODAY(),A$1:A$100>=WORKDAY(TODAY(),-5),B$1:B$100)

祝日や休業日を考慮する場合はそのリストとなる表を作成してください。
=WORKDAY(TODAY(),-5,祝日の表の範囲)
に変更すればOKです。

日付と時間の範囲は実際の範囲に合わせて変更してください。

お礼

ありがとうございます。
ただ、私の操作が悪いのか =WORKDAY がうまくいきません（エラーになってしまいます）
もう少し頑張ります。

回答No.3

mshr1962

#1のmshr1962です。
すみません。一つ書き忘れていました。
>私の操作が悪いのか =WORKDAY がうまくいきません
「ツール」「アドイン」で「分析ツール」にチェックしてください。
WORKDAY関数に始まるいくつかの関数は分析ツールをアドインしないと使えない関数です。

お礼

ありがとうございます。
おかげさまで解決しました。

回答No.2

zinchan

Ａ列に日付、Ｂ列に曜日、Ｃ列に当日労働時間のデータが、日付の昇順で入力されていることを前提に回答します。

　Ｄ列に、「労働日カウント」を新たに挿入します。
　例えば日付データがＡ２から始まっているとしたら、Ｄ２のセルに、以下の数式を入力します。

=IF(AND(MAX(D3:D$65536)>0,C2>0),MAX(D3:D$65536)+1,IF(C2>0,1,""))

※　D$65536とあるのは、エクセルの最大行数が65536行であるため

　Ｄ３以降に、Ｄ２の式をコピーしてください。

　次に、Ｅ列に、「労働直近５日の労働時間合計」として、日付データがＡ２から始まっていれば、Ｅ７セルに、以下の数式を入力します。

=IF(C7=0,SUMIF(D$2:D6,"<="&MIN(D$2:D6)+4,C$2:C6)-C7,SUMIF(D$2:D7,"<="&D7+5,C$2:C7)-C7)

※日付データがＡnセルから始まっていれば、Ｅn+5セルに、上記式中のD$2、C$2とある部分を、D$n、C$nとします。なお、日付データがＡ２から始まっていた場合、Ｅ６以前のセルに本式を入力しても、それ以前のデータがないので、意味のない値しか出ないので、式を入力しないで下さい。

　その数式を、Ｅ８以下のセルにコピーします。

　Ｄ列は可視の状態にしても意味がないのですが、この方式ではどうしても必要な列なので、隠したい場合は、事前に日付、曜日、Ｄ列、Ｅ列の式を必要行数分入力しておいて、Ｄ列を表示しないようにして下さい。
　必要行数の見当が付かない場合は、日付、曜日、Ｄ列、Ｅ列の式を、とりあえず１ヶ月分先に作っておき、足りなくなりそうでしたら行のコピーをしておいてください。

　もし、「労働直近５日の労働時間合計」の列で、先の日付の行に、数字が羅列されるのがいやでしたら、

=IF(C7="","",IF(C7=0,SUMIF(D$2:D6,"<="&MIN(D$2:D6)+4,C$2:C6)-C7,SUMIF(D$2:D7,"<="&D7+5,C$2:C7)-C7))

としてください。当日労働時間を入れると、あなたのいう直近５日労働時間合計値が表示されるようになります。

　捕捉
　この方法ですと、月ごとに集計している場合に、式が恐ろしく複雑になるので、一つのシートに連続して入れることをお薦めします。

お礼

ありがとうございます。
ウ～ン．．言われた通りやっているのですけれど、やっぱりうまくカウントしてくれません。
私のやり方が悪いと思いますので、もうすこし頑張ります。