COUNTIFS関数について

質問

EXCELのCOUNTIFS関数について質問させてください。

I列に部署名（7～8種類）
BC列に残業時間（10：00や30：00など[h]:mm表記に設定）
が入力されたデータが950件弱あります。

社内全体の残業時間を出すことは出来たのですが
部署別で尚且つ、40時間越え〇人・50時間越え〇人
というように区切って集計する事が出来ませんでした…。
使ってみた関数は下記の通りです。
=COUNTIFS(I3:I706,対象部署,BC3:BC706,">=40:00")
結果は何度やってもエラーか【0】になってしまいます。

どなたか力を貸してください。
具体的な関数とアドバイスを頂けると幸いです。
宜しくお願い致します。

回答No.3

mshr1962 · ベストアンサー

対象部署が文字列なら""で囲む必要があるのでは？

部署別で尚且つ、40時間越え〇人（40時間以上50時間未満なら）
=COUNTIFS(I3:I706,"対象部署",BC3:BC706,">=40:00",BC3:BC706,"<50:00")

部署別で尚且つ、50時間越え〇人（50時間以上なら）
=COUNTIFS(I3:I706,"対象部署",BC3:BC706,">=50:00")

まあ部署（CA列）と時間（2行目CB列以降）をセル参照にするなら
=COUNTIFS($I$3:$I$706,"="&$CA3,$BC$3:$BC$706,">="&CB$2,$BC$3:$BC$706,"<"&CC$2)
のようにすればよいかと。。。

お礼

ご回答ありがとうございます。
頂いた関数で無事に集計が出来ました。
また何かあれば宜しくお願い致します。

回答No.6

bunjii

>結果は何度やってもエラーか【0】になってしまいます。
数式に誤りは見受けられません。
但し、質問の文言と検索条件に論理上の不合理があります。
40時間超えは ">40:00" でなければなりません。
40時間以上は ">=40:00" であり、40時間丁度の扱いを誤らないようにしてください。
結果が 0 になるのはBC列の値が数値（時間のシリアル値）ではなく、文字列になっているのではないでしょうか？

お礼

ご回答ありがとうございます。
他の方からもご指摘頂きました。
＞＝と＞の違いには今後気をつけたいと思います。
本当にありがとうございました。

回答No.5

msMike

》 =COUNTIFS(I3:I706,対象部署,BC3:BC706,">=40:00")
質問文全体を読まずに上の部分だけを見た上での感想だけど、「対象部署」の箇処には文字列である部署名を入れていますか？
それが、例えば、「総務部」と仮定した場合、
単に、総務部 でなく、"総務部" としていますか？（ダブルクオーテーションで囲む
「総務部」が入力されたセルを参照するなら、セル番地でＯＫだけど、具体的な部署名を式中に入力するばあいは 、"総務部" としなけりゃアカンのです。

お礼

ご回答ありがとうございます。
””が抜けていて何度かエラーに…
必ず入力するように心掛けます！
無事に解決しました。
ありがとうございました。

回答No.4

kagakusuki

御質問のCOUNTIFS関数の中の2つ目の条件である

">=40:00"

の方は、そのままで40:00以上の時間をカウントする条件になっております。
　それで確認したいのですが、BC3:BC706のセル範囲に入力されている時間データは、本当に時間データになっているのでしょうか?
　例えばTEXT関数などの関数を使用して

"40:00"

の様な文字列データが表示される様になっているなどという事は御座いませんでしょうか?
　念のために同じシート上の適当なセルに

=COUNT(BC3:BC706)

という関数を入力してみて下さい。
　それで表示される結果がBC列に入力されている時間データの数に一致する様なら良いのですが、もし結果が0になる様でしたら、それはBC列に入力されている値が時間データではなく、文字列データであるという事ですので、BC3:BC706に入力する関数を工夫して時間のデータが出力される様にして下さい。

　又、BC列に入力されているデータが本当に時間のデータである事が確認出来た場合には、
　念のために同じシート上の適当なセルに

=COUNTIF(BC3:BC706,">=40:00")

という関数を入力してみて、40時間以上の人数が正しくカウントされるかどうかを確認して下さい。

　それから、御質問文にあるCOUNTIFS関数

=COUNTIFS(I3:I706,対象部署,BC3:BC706,">=40:00")

において、1つ目の条件である

対象部署

が" "で括られていない様ですが、実際の関数では

=COUNTIFS(I3:I706,"対象部署",BC3:BC706,">=40:00")

の様に" "で括られた形式となっているのでしょうか?(もし" "で括られていない文字列データが入力されていた場合にはエラーになりますので直して下さい)
　或いは、対象部署の名称がどこかのセルに入力されていて、そのセルを参照する形になっているのでしょうか?
　何れにしましても1つ目の条件として設定されている部署名と、I3:I706に入力されている部署名が、本当に同じ文字列になっているかどうかを確認して下さい。
　もしかしますと余分なスペースや表示には現れない文字・記号等が入っていますと、それが原因で違う文字列(＝条件とは異なるデータ)として扱われて、カウントされなくなってしまう恐れがありますので、双方のデータ中の部署名が一致するようにデータを修正して下さい。

お礼

ご回答ありがとうございました。
文字列データがあるのは学生時代の記憶が何となく・・・
無事に解決でした。
本当にありがとうございます。

回答No.2

dogs_cats

記載のセル範囲と数式で間違っていはいないと思いますが。
その数式だと以上なので超えるでありません。
超える>のみ
以上>=

又40、と50のカウントが重複カウントするので40以上のカウントから50以上のカウントを引いた値とすべきではありませんか？

残業時間の表示形式は[h]:mmで24時間以上を表記しますが、そうなっているのですよね。

サンプル数式を添付します。
F、G列にカウントします。
F列　４０超え~５０時間以下
F2式
=COUNTIFS($A$2:$A$16,$E2,$C$2:$C$16,">40:00")-G2

G列50時間超える
G2式
=COUNTIFS($A$2:$A$16,$E2,$C$2:$C$16,">50:00")

お礼

ご回答ありがとうございます。
わかりやすい表までつけてくださって
本当に感謝です。
おかげさまで無事に解決しました。

回答No.1

sigesigeo1919

エクセルの日付や時間（00:00）はシリアル値が書式変換されたものなのでそのままでは計算がうまく行かない場合があります。

◾︎シリアル値とは
http://exinfo.biz/lec/lec_serial.shtml

今エクセルがさわれないので、具体的なアドバイスは出来なくて申し訳ないですが、参考になりそうなURL貼ります。

http://m.chiebukuro.yahoo.co.jp/detail/q1124994076

お礼

アドバイス頂きありがとうございます。
無事に解決しました。
シリアル値というものが存在するのですね・・・
1つ勉強になりました。