Excel_条件に合う範囲の平均値の求め方

質問

Excel関数についての質問です。

以下のようなデータが合った場合、条件に合う範囲のデータのみ
抜き出して平均値を求める方法を教えてください。

■やりたいこと
マイナスが続いている範囲を見つけ出し、マイナスが続いている
範囲のみの平均を算出する。
以下で言うとB4～B7の平均を自動で計算してくれる
関数を考えています。

A列：時間が入っていますが、これ自体も他の値を時間に換算したもの。
（式が入っています。）
B列：データが入っており、プラスの領域とマイナスの領域を交互に繰り返す。

　　 A　　B
1　Time　DATA
2　　0　　　0.05
3　　6　　　0.04
4　　8　　　-0.02
5　　11　　-0.01
6　　15　　-0.03
7　　17　　-0.05
8　　21　　0.05
9　　24　　0.06
10　 27　　-0.03
・・・（以下プラスとマイナスが交互に続きます。）

DABERAGEなどの様に、条件に合うセルの平均ではなく、
ある範囲のセルの平均を出したいと思いますが
中々良い案がありません。

よろしくお願いします。

回答No.3

KURUMITO · ベストアンサー

SUMPRODUCTなどの関数は計算に負担がかかります。分かり易く簡単な方法は作業列を設ける方法です。作業列が目障りでしたらその列を選択して右クリックして「非表示」を選択すればよいでしょう。
作業列をC列としてC2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(AND(B1>=0,B2<0,B3<0),AND(B1<0,B2<0,B3>=0)),MAX(C$1:C1)+1,"")

そこでマイナスを含むグループの平均ですがD列に表示させることにして、グループの最下行にそれぞれ表示させるのでしたら次の式をD2セルに入力して下方にオートフィルドラッグします。

=IF(C2="","",IF(MOD(C2,2)=1,"",AVERAGE(INDEX(B:B,MATCH(C2-1,C:C,0)):B2)))

お礼

ご回答ありがとうございました。

試してみたところ、意図通りに計算できました。

プラスの範囲にマイナスが出た場合の応用が容易だった事と
SUMPRODUCTは極力使用しない方がよさそうでしたので
ベストアンサーとさせていただきました。

回答No.2

kagakusuki

回答番号ANo.1です。
　それとも、添付画像の様に、マイナスが続いている範囲毎に、平均値を出すという事なのでしょうか？

　因みに、添付画像のC列は、マイナスが続いている範囲の上端の行に平均値を表示しているのに対して、D列では、マイナスが続いている範囲の下端の行に平均値を表示しています。
　尚、C2セルに入力している関数は、次の様なものです。

=IF(AND(INDEX($B:$B,ROW())<0,INDEX($B:$B,ROW()-1)>=0),SUMPRODUCT((COUNTIF(OFFSET($B$1,ROW()-ROW($B$1),,ROW(C2:INDEX(C:C,MATCH(9^99,$B:$B)))-ROW()+1),">=0")=0)*(INDEX($B:$B,ROW()):INDEX($B:$B,MATCH(9^99,$B:$B))))/SUMPRODUCT((COUNTIF(OFFSET($B$1,ROW()-ROW($B$1),,ROW(C2:INDEX(C:C,MATCH(9^99,$B:$B)))-ROW()+1),">=0")=0)*1),"")

　又、D2セルに入力している関数は、次の様なものです。

=IF(AND(INDEX($B:$B,ROW())<0,INDEX($B:$B,ROW()+1)>=0),SUMPRODUCT((COUNTIF(OFFSET($B$1,ROW(D$2:D2)-ROW($B$1),,ROW()-ROW(D$2:D2)+1),">=0")=0)*(INDEX($B:$B,ROW($B$1)+1):INDEX($B:$B,ROW())))/SUMPRODUCT((COUNTIF(OFFSET($B$1,ROW(D$2:D2)-ROW($B$1),,ROW()-ROW(D$2:D2)+1),">=0")=0)*1),"")

　本当は、SUMPRODUCT関数は使用せずに、AVERAGE関数で平均値を求める対象範囲を決定する際に、MATCH関数を使用して、マイナスが続いている範囲を算出したかったのですが、何故かMATCH関数ではTime欄が27～28となっている範囲の行数を、正しく求める事が出来ませんでした。
　もし、他の回答者様で、MATCH関数を使用して、マイナスの範囲を求め様とされる方が居られましたら、マイナスの行とプラスの行が連続してるパターンを様々に変えてみて、全てにおいて正しく計算されているのかを確認される事を御勧め致します。

お礼

ご回答ありがとうございました。

試してみたところ、仰る通りに計算できました。

SUMPRODUCT関数の使い方など、勉強になりました。
マイナスが一つだけあった場合も平均値を算出してしまいましたので
計算プロセスの参考にさせていただきます。
（私の説明不足でした。）

回答No.1

kagakusuki

B4,B5,B6,B7,B10の平均という事であれば、

=SUMIF($B:$B,"<0")/COUNTIF($B:$B,"<0")

で求める事が出来ます。
　又、使用するExcelのバージョンがExcel2007以降のバージョンである場合には、

=AVERAGEIF($B:$B,"<0")

で、求める事が出来ます。
　しかしながら、

>以下で言うとB4～B7の平均

と言われるのでしたら、どの様な条件に従った結果、平均値を求める範囲を、B4～B7としているのかが不明なため、回答する事が出来ません。
　何故なら、

>プラスとマイナスが交互に続きます。

と言う条件なのですから、もしかしますと、B11以下にもマイナスの値が続いているかもしれず、

>マイナスが続いている範囲

が複数存在している事もあり得るため、マイナス値が連続している範囲が複数存在している場合には、
一番最初に現れている連続した範囲のみの平均を求めるのか、
それとも、連続している範囲が最も長い部分のみの平均を求めるのか（これも、最も長く続いている行数が同数１位となっている箇所が複数ある場合にはどうするのか）、
それとも、マイナスの値が１行だけ単独で存在している行は除いて、それ以外の全てのマイナス値の合計を、その行数で割った値を求めれば良いのか、
という点が、御質問文中には記述されていないからです。
　ですから、どの様な条件の下で範囲を決めているのかという事と、複数存在する条件に合う範囲が複数存在している場合には、どの範囲の平均値を求めれば良いのかという事を、補足欄等を使用して御教え願います。

お礼

連続して回答頂いていましたね。。。
非常に参考になりました。ありがとうございました。

補足

ご回答ありがとうございます。
説明不足で申し訳ありません。

「マイナスが続く範囲」は複数存在します。

ここで平均を求めるのは、
「マイナスが連続して続く場合に、連続している範囲のみ」
かつ
「B列中にマイナスが続く範囲が複数存在するため、その全ての範囲に対して」
という条件をつけたいと思っています。

よろしくお願いします。