【EXCEL VBA】COUNTIF関数について

質問

A列・B列：氏名コード、C列：日付、D列：出社時刻、E列：退社時刻、F列：勤務時間といった具合に1ヶ月分のデータが入力されています。

各々氏名コード毎にG列の月末日の箇所に1ヶ月分の勤務時間の合計を入れているのですが、出社時刻と退社時刻がブランク(休日等)の場合、F列の勤務時間が0時間でなく24:00:00で換算されてしまう為、実質1ヶ月分の勤務時間の合計がその分加算されてしまいます。(画像参照)

そこで、各々氏名コード毎にH列の月末日の箇所に24:00:00で換算されてオーバーしてしまった時間を自動的にカウント出来るようにしたいのです。

手動で勤務時間の1ヶ月の合計を算出する場合は、
[=IF(A32<>A33,SUMIF(A:A,A32,F:F),"")]で、

また手動で24:00:00で換算されてオーバーしてしまった時間をカウントする場合は、
[=COUNTIF(F2:F32,"24:00:00")]

と関数対応できるところまではこじつけたたのですが、マクロで自動的に反映させる方法が分かりません。

何かいい手順がございましたら、ご指導の程宜しくお願い致します。
尚、作業環境はWindows7、office2010です。

回答No.3

mu2011 · ベストアンサー

>F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。
　⇒この数式を以下のようにすれば調整が不要ではないでしょうか。
　　[=IF(E2-D2>=0,E2-D2,E2+1-D2)]

>マクロで自動的に反映させる方法が分かりません
　⇒何をマクロで自動化したいのか判りませんので具体的に補足して下さい。

お礼

主題の件、ご提示頂いた数式で問題が一発で解決しました。！
お陰でH列を使用する無駄が省けました。
本当に有難うございました。

回答No.4

KURUMITO

手動で24:00:00で換算されてオーバーしてしまった時間をカウントするとのことですが、勤務時間の計算においてそのようなことになることは通常の方法ではありえませんね。避けることが必要でしょう。
どうやら24時間の分を差し引くことになっている原因はF列に入力している式に問題がありますね。

実際の計算ではシリアル値から時刻を出すため、F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。
出社時刻と退社時刻がブランクの場合、F列の勤務時間が0:00:00=24:00:00と認識されてるかと思います。

F列の式が上のようではE列やD列のデータが空の場合には1の値で、それがシリアル値となって時間では24:00と同じことになります。上のような式ではE2-D2の値がE2に比べてD2の値が大きい場合にはエラー表示になってしまうのでそのようにしているのでしょうか？　シリアル値から時刻を出すためにそうしているとのことですが、例えば時刻で12:00と入力すればエクセルではそれが自動的にシリアル値では0.5となって時間の計算に使われているのです。逆にセルに0.5と入力してからセルの表示形式を時刻にすることで12:00のように表示させることができます。すなわちセルに入力した時刻はシリアル値としてエクセルでは理解され、セルに入力した数値もまたセルの表示形式を操作することで時刻の表示にもできるのです。
シリアル値から時刻を出すために1などの数値を加算するなどのことは全く必要ありません。シリアル値と時刻とは一体ものと考えればよいのです。

通常ではE列の時刻(シリアル値）がD列の時刻(シリアル値)よりも大きくなっていますのでE2-D2の値は負になることはありませんが負になる場合には時間の計算ではエラー表示になります。例えば退勤時間が夜中の午前1時の場合にE2セルに1：00のように入力した場合にはエラー表示になってしまいますのでその場合にはE2セルに25：00と入力するか、あるいはE2+1からD2セルの値を引くことになります。この式で1は24:00と同じ意味を持っています。
E列で午前1時を25：00のように入力する場合にF2セルに入力する式は次のようにすればよいでしょう。

=IF(COUNT(D2:E2)<>2,"",E2-D2)

午前1時を1:00のように入力する場合には

=IF(COUNT(D2:E2)<>2,"",IF(E2>=D2,E2-D2,E2+1-D2))

F2セルに入力した式を下方にオートフィルドラッグすればよいでしょう。
G2セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。

=IF(A2<>A3,SUMIF(A:A,A2,F:F),"")

お礼

主題の件、仰るとおりF列の数式に問題がありました。
下記コメントを頂いた方の数式で無事解決しました。
コメント有難うございました。

回答No.2

keithin

とりあえずご質問の直接の回答としては,Ｈ列の数式を
=IF(A32<>A33,COUNTIFS(A:A,A32,F:F,1),"")
とでもしておけばＯＫです（勿論書式は[h]:mm:ssのように）が，

そもそもで言えば
＞F列の勤務時間が0時間でなく24:00:00で換算されてしまう

こちらを解消するのが先でしょう。

一体全体いまＦ列にどんな式を入れていて(若しくはＤ列Ｅ列にいったい何を入れていて)そんなけったいな事になってしまうのかというのはありますが，ごくシンプルに
=IF(COUNT(D32:E32)=2,E32-D32,0)
といった具合にしておくか，もしかしてもしかすると
=IF(COUNT(D32:E32)=2,MOD(E32-D32,1),0)
としておくとよいかも？しれません。

お礼

主題の件、ご提示頂いた数式で表示出来ました。
コメント有難うございました。

補足

実際の計算ではシリアル値から時刻を出すため、F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。

出社時刻と退社時刻がブランクの場合、F列の勤務時間が0:00:00=24:00:00と認識されてるかと思います。

まずは教えて頂いた数式を入れてみますね。

回答No.1

bin-chan

列Fの式でその行の列D,列Eを評価して勤務時間を算出させるほうが先なのでは？

補足してください。
１）２５時など、２４時を過ぎる退社時刻がありますか？
２）そもそも列Fに何か式を設定してるのでは？何と設定してますか？

お礼

主題の件、無事解決しました。
コメント有難うございました。

補足

・24時を過ぎる退社時刻があります。
・F列には[=IF(E2-D2>0,E2-D2,E2+1-D2)]が入っています。