関数で平均を出して判定したいのですが

質問

エクセルで給与の実績日数と実績時間で平均値を出して判定をしなければいけません
　　　　Ａ　　　Ｂ　　　　　　Ｃ　　　　　　　Ｄ
１　月　　　　　４月　　　　　5月　　　　　　判定　
２実績日数　　　16日　　　　　20日　　　　　　
３実績時間　　１２８Ｈ　　　　１５０Ｈ
４　月　　　　　4月　　　　　5月　　　　　　　　　
５実績日数　　　空白　　　　25日　
６実績時間　　　空白　　　　１３０Ｈ

時間126時間以上　日数15日以上を満たしている場合”該当”満たしてない場合”非該当”と、判定するのですが、
一つの列例えばＢ列に対してだとＩＦ関数ですることができましたが、これを2ヶ月、6月が増えると3ヶ月というふうに平均値に対しての判定を組むことができません。
5月からの入社の人は平均を割り出す分母が入社日でそれぞれで違ってきます。
この場合、一人ひとり分母を変えることでしか平均を出して判定することしかできないのでしょうか？
人数が250人以上いるのでコピーしてできるような関数を作りたいのですが・・・
わかりづらい文章ですがよろしくおねがいします。

回答No.2

noname#79209 · ベストアンサー

まず、Ｄ列に「判定」をおくのはマズイのでは？
今後６月、７月と増えていくので、Ｎ列に固定するか、Ａ列とＢ列の間に移動するかです。
ここでは、Ａ列とＢ列の間に移動する方法をとります。
> 時間126時間以上　日数15日以上
これは当然月平均ですよね？
長期休暇の人を気にしないなら、
B2に
=IF(SUM(C$2:$N2)/COUNTIF(C$2:$N2,">0")>=15,"","非")&"該当"
B3に
=IF(SUM(C$3:$N3)/COUNTIF(C$3:$N3,">0")>=128,"","非")&"該当"

もし、両方を満たすものを探したいなら、
B2に
=IF(AND(SUM(C$2:$N2)/COUNTIF(C$2:$N2,">0")>=15,SUM(C$3:$N3)/COUNTIF(C$3:$N3,">0")>=128),"","非")&"該当"

もし、どちらかを満たすものを探したいなら、
B2に
=IF(OR(SUM(C$2:$N2)/COUNTIF(C$2:$N2,">0")>=15,SUM(C$3:$N3)/COUNTIF(C$3:$N3,">0")>=128),"","非")&"該当"

でいかがでしょう。

お礼

ありがとうございます。
関数だけを見て、意味がわからなかったですが、実際に試してみてわかりました。
もう一つ問題があるのですが・・・
4月～3月の12ヶ月間はいいとして、年度が替わると次の4月が入ってくるんです（＞＜）その時に年度の間に合計が入るのですが・・・
そのセルを対象外なんてできますか？

回答No.5

noname#79209

#2です。
> 括弧が一つ足りない気がするのですが・・・
ゴメンナサイ。
誤：
=IF(AND(SUM($C$2:$N2)/$B3>=$A$2,SUM($C$3:$N3)/$B3>=$A$3,"","非")&"該当"
正：
=IF(AND(SUM($C$2:$N2)/$B3>=$A$2,SUM($C$3:$N3)/$B3>=$A$3),"","非")&"該当"
ANDに対する右括弧が抜けていました。「/$B3>=$A$3),"","非"」のところです。

> 年度が替わると次の4月が入ってくるんです（＞＜）
> その時に年度の間に合計が入るのですが・・・
うーん、長期で使っていくわけですか。
まあ、横へ広げていくと20年は使える計算ですが．．．あまりお勧めしません。
第一、見にくいでしょう。
一つのブックは一つの年度で使うか、あるいは年度ごとに1シート使っていくことをお勧めします．
年度ごとに1シートにしておけば、各シートすべて同じフォーマットですから、
表紙用のシートを作っておけば、各年度のサマリを表示させることもできます。
個人的には、年度ごとに一ブックを採用します。万が一ファイルが壊れても一年度分だけの被害ですみますから．．．

回答No.4

noname#79209

#2です。
#3での回答で、
B3の表示書式を「:::」とすれば、ワークセルの計算結果は表示されません。

回答No.3

noname#79209

#2です。
式自体は同じですが、こんな方法ももあります。
A2に「15」と入力し、表示書式に「実績日数」
A3に「126」と入力し、表示書式に「実績時間」
こうすると、それぞれのセルの内容に関わらず、表示は「実績日数」「実績時間」となります。
B3に
=COUNTIF($C$3:$N3,">0")
として、
B2に
=IF(AND(SUM($C$2:$N2)/$B3>=$A$2,SUM($C$3:$N3)/$B3>=$A$3,"","非")&"該当"
でもいけます。

A2とA3にチェックする数値を入れておけば、これらの数値が変更になった場合でも、
個々の数式をいちいち変更せずに、A2とA3の2カ所の数値を変更するだけですみます。

補足

＞B2に
=IF(AND(SUM($C$2:$N2)/$B3>=$A$2,SUM($C$3:$N3)/$B3>=$A$3,"","非")&"該当"
は、括弧が一つ足りない気がするのですが・・・
どこにいれればいいのでしょうか？どこに入れてもエラーになります。

回答No.1

NaoDorry

B列に対してというのがわかりませんが・・・
判定はD1セルに
=IF((OR(AVERAGE(B2:C2)>15,AVERAGE(B3:C3)>126)),"該当","非該当")
でいけるんじゃないでしょうか

お礼

ありがとうございます！
これから列に6月7月・・・と増えていくので、
ＮＯ２さんの関数を利用したいと思います。
ＡＶＥＲＡＧＥというのもはじめてみました。
どんどん勉強して自分で組めるようになりたいです！