エクセルの最小値判別と範囲内数値の抽出と平均値

質問

分かりにくくてすみません。
エクセルの表から、各グループの最小値を判断して、+1以内の数字を識別、平均値を出したいです。
A～C列にデータが入っています。

列 A　：　B　：　C　：　D　：　E
A-1：　1　：　2.05　：　　　：　1
A-1：　1.5：　1.08　：　　　：　1.5
A-1：　2　： 12.12　：　　　：　
A-1：　3　：　0.35　：　　　：　

A-2：　0.5：　1　　：　　　： 0.5
A-2：　0.3：　2　　：　　　： 0.3
A-2：　1.5：　3　　：　　　：　　
A-2： 0.05： 4　　：　　　：　0.05

とあるとします。
A-1グループのB列の中で最小値（1）を基準に+1以内のもの（1.5）をE列に出す方法はありませんでしょうか？（最小値も含む）
同様にA-2グループの最小値（0.05）を基準に+1以内のもの（0.3、0.5）をE列に出したいです。

次の段階で、E列に出されたものの C列の平均をD列に計算させたいです。
A-1グループの場合、（1、1.5）のC列（2.05+1.08/2）＝1.565をD列4箇所に入れたいです。
同様にA-2の場合は、（0.05、0.5、0.3）のC列（4+1+2/3）＝2.333333をD列4箇所に入れたいです。
（小数点以下 7桁目を四捨五入）

一番やりたいことは 各グループの 最小値から+1以内のものの C列の平均をD列に入れることですのでE列に出さなくても良いです。

どこか一部だけでも、E列に出すだけでも 分かればとても助かります。
マクロとか全く理解できていないので できれば 関数で教えてください。
すみませんが どうぞ宜しくお願いいたします。

回答No.1

KURUMITO · ベストアンサー

E1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",IF(B1<MIN(INDEX(B:B,MATCH(A1,A:A,0)):INDEX(B:B,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1))+1,B1,""))

D1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",SUMIF(INDEX(E:E,MATCH(A1,A:A,0)):INDEX(E:E,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1),">=0",INDEX(C:C,MATCH(A1,A:A,0)):INDEX(C:C,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)+1))/COUNT(INDEX(E:E,MATCH(A1,A:A,0)):INDEX(E:E,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1)))

お礼

早速ありがとうございます。できました！
最小値を認識させる時点でお手上げだったのですが、やりたかった事 全て叶えてくださってありがとうございました。

回答No.4

kagakusuki

ANo.1様とANo.2様の方法は、A列のグループが、グループごとに隙間なく一塊になっている場合に限り、有効な方法ですが、グループが一塊になっていない場合、例えば、

A列
A-1
A-1
A-2
A-1
A-1
A-2
A-2

という様に、グループごとの塊の途中に別のグループが入っていて、グループが分断されている場合や、

A列
A-1
A-1

A-1
A-1
A-2
A-2

という様に、グループごとの塊の途中に途中に空欄が入っていて、グループが分断されている場合には、
ANo.1様とANo.2様の方法では正しい値を求める事が出来ません。
　もし、A列のグループが、グループごとに一塊になってはいない場合には、次の様な方法となります。

　御質問の

>A-1グループのB列の中で最小値（1）を基準に+1以内のもの（1.5）をE列に出す方法

で平均値を出されるのであれば、まず、E1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($B1),$B1-MAX($A:$A)-1+SUMPRODUCT(MAX(($A$1:INDEX($A:$A,MATCH(99^9,$B:$B))=$A1)*(MAX($A:$A)+1-$B$1:INDEX($B:$B,MATCH(99^9,$B:$B)))))<1),$B1,"")

　次に、D1セルに次の数式を入力して下さい。

=IF($A1="","",SUMPRODUCT(($A$1:INDEX($A:$A,MATCH(99^9,$E:$E))=$A1)*ISNUMBER($E$1:INDEX($E:$E,MATCH(99^9,$E:$E)))*$C$1:INDEX($C:$C,MATCH(99^9,$E:$E)))/SUMPRODUCT(($A$1:INDEX($A:$A,MATCH(99^9,$E:$E))=$A1)*ISNUMBER($E$1:INDEX($E:$E,MATCH(99^9,$E:$E)))))

　そして、D1～E1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。
　以上です。

　尚、上記の方法では、あくまでも「御質問内容に沿って、条件に合致している行のE列のセルに、B列の値を参照させている」ため、D列の数式が複雑になっています。
　単に、条件に合致している行のみの、C列の平均値を算出されるのでしたら、条件に合致している行のE列のセルには、B列の値ではなく、グループ名であるA列の値を表示させた方が、D列の数式が遙かに単純化しますので、以下の方法の方を御奨め致します。
　まず、E1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($B1),$B1-MAX($A:$A)-1+SUMPRODUCT(MAX(($A$1:INDEX($A:$A,MATCH(99^9,$B:$B))=$A1)*(MAX($A:$A)+1-$B$1:INDEX($B:$B,MATCH(99^9,$B:$B)))))<1),$A1,"")

　次に、D1セルに次の数式を入力して下さい。

=IF($A1="","",SUMIF($E:$E,$A1,$C:$C)/COUNTIF($E:$E,$A1))

　そして、D1～E1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。
　以上です。

お礼

kagakusuki様
ありがとうございました。
E列の式がうまく計算されないようで？？
#VALUE!になってしまいました。
今回はNo.1の方法で提出できました。
また 教えてください。
どうもありがとうございました。

回答No.3

KURUMITO

No2の式でー1とすべきところが＋1になっていました。訂正してください。E列が無い場合の式です。

=IF(A1="","",SUMIF(INDEX(B:B,MATCH(A1,A:A,0)):INDEX(B:B,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1),"<"&MIN(INDEX(B:B,MATCH(A1,A:A,0)):INDEX(B:B,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1))+1,INDEX(C:C,MATCH(A1,A:A,0)):INDEX(C:C,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1))/COUNT(INDEX(E:E,MATCH(A1,A:A,0)):INDEX(E:E,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1)))

お礼

E列が無い場合で、E列を参照する式になっているようで うまく計算できませんでした。
でも これが出来れば一番早いと思います。
今回は No.1の方法で提出できました。
すぐに回答いただけ 本当に助かりました。
どうもありがとうございました。

回答No.2

KURUMITO

E列が無くてもD1セルに次の式を入力して下方にオートフィルドラッグすることで平均値を表示させることもできます。

=IF(A1="","",SUMIF(INDEX(B:B,MATCH(A1,A:A,0)):INDEX(B:B,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1),"<"&MIN(INDEX(B:B,MATCH(A1,A:A,0)):INDEX(B:B,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1))+1,INDEX(C:C,MATCH(A1,A:A,0)):INDEX(C:C,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)+1))/COUNT(INDEX(E:E,MATCH(A1,A:A,0)):INDEX(E:E,MATCH(A1,A:A,0)+COUNTIF(A:A,A1)-1)))