３個の山になっている連続数値データから各最大値を

エクセルで３個の山になっている連続数値データがあります。
０．３
２．５
５．８
７．６
３．５
０．２
３．０
６．２
７．９
４．５
０．１
２．７
５．２
７．３
３．３
０．３
といった具合です。
このデータはたとえのデータで、実際の連続データの行数は３０００行くらいです。
今は、まずこのデータのグラフを描いて、そのグラフの山をみてから、該当するおよその行を選択して、MAX関数で最大値を３つ別々に求めています。
この方法以外にそれぞれの３つの山の最大値を得る便利な方法がありましたら教えてください。
よろしくお願いします。

No.5 回答者： MarcoRossiItaly 回答日時： 2012/08/13 02:36

SD比がいくつかとか、ブロードなピークなのかバックグラウンドなのか判然としない場合とか、どんなメソッドで処理するのか方針を先に持たないと、Excelでの操作についても、何とも言えません。

いろいろこだわると、どんどんExcelから離れていって、専用ソフトが必要な世界に入っていくように思います。質問者さんのように目で見てから個別に判断するというのも、それはそれで一法だと思います。

この回答へのお礼

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

お礼日時：2012/08/13 15:35

No.4 回答者： kagakusuki 回答日時： 2012/08/12 17:47

　もしも、各ピークの値に大きな差があって、最も低いピークの値が平均値よりも低くなる事もあり得る場合には、次の様な方法は如何でしょうか？

　各行の上下に適当なセル範囲を決めて、そのセル範囲内の最大値とその行に入力されている値が等しければ、その行の値がピーク値だと判定する方法です。

　具体的には以下の様な方法となります。
　今仮に、元データがA２以下に並んでいるものとして、F2セルに各行毎にプラスマイナス何行分の範囲内のデータの中から最大値を求めるのかを指定する整数値を入力し、H列とI列を作業列として使用して、D1～D3セルにピーク値を表示させるものとします。
　まず、F2セルの書式設定の表示形式を[ユーザー定義]の

±0

として下さい。
　次に、F2セルに対して、次の様なデータの入力規則を設定して下さい。
　　　入力値の種類・・・整数
　　　データ・・・・・・・・・次の値より大きい
　　　最小値・・・・・・・・・0

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

=IF(AND(ISNUMBER(INDEX($A:$A,ROW())),MAX(INDEX($A:$A,MAX(ROW($A$1)+1,ROW()-$F$2)):INDEX($A:$A,ROW()+$F$2))=INDEX($A:$A,ROW())),MAX(INDEX($A:$A,MAX(ROW($A$1)+1,ROW()-$F$2)):INDEX($A:$A,ROW()+$F$2)),"")

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

=IF(OR($M2="",COUNTIF($M:$M,">="&$M2)>3),"",ROW())

　次に、H2～I2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、D1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($H:$H),"",VLOOKUP(SMALL($H:$H,ROWS($1:1)),$H:$I,2))

　次に、D1セルコピーして、D2セルとD3セルに貼り付けて下さい。

　後は、F2セルに「各行に入力されている値が、ピーク値であるか否かを判定する際に、上下方向、±何行の範囲内の最大値を求めて比較する事で判定するのか」を指定する整数値を入力しますと、（その指定した範囲内に他の山のピークが存在する場合を除いて）3つのピーク値が自動的に表示されます。

　尚、ピークが4つ以上存在する場合には、ピーク値の高さが４番目以下のピークは無視されます。
　又、添付画像の例では、データのピーク同士が近接しているため、F2セルで指定する行数を３行と少ない行数としていますが、ノイズによる変動がある事を考慮する必要がある場合には、F2セルに入力する値はもっと大きな数とする必要があります。
　但し、複数の山が近接していて、F2セルで指定した行数よりも近い範囲内に、低い方のピーク値よりも高い値が入力されている行が存在している場合には、正しいピーク値を抽出する事が出来ませんので、F2セルで指定する範囲は広過ぎても狭過ぎても不都合が生じる原因となります。実際のピークの感覚やノイズの影響を考慮された上で、F2セルで指定する行数を調節して下さい。　

この回答へのお礼

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

お礼日時：2012/08/13 16:17

No.3 回答者： mike_g 回答日時： 2012/08/12 14:10

[No.2]の添付図訂正

セル G1 の表示「5.8」は「7.3」の間違いでした。m(__)m

この回答へのお礼

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

お礼日時：2012/08/13 16:09

No.2 回答者： mike_g 回答日時： 2012/08/12 14:05

添付図参照

セル C1 に式 =LARGE(A:A,ROW(A1)) を入力して、此れを下方にドラッグ＆ペースト
または
セル E1 に式 =LARGE($A:$A,COLUMN(A1)) を入力して、此れを右方にドラッグ＆ペースト

この回答へのお礼

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

お礼日時：2012/08/13 16:08

No.1 回答者： keithin 回答日時： 2012/08/12 13:02

一つのアプローチとしては、例えば「平均より上半分」を切り出してカタマリになっている範囲を目視で捕まえ、それぞれの区間の最大を取るような方法も可能です。

Ｂ１：
=IF(A1>=AVERAGE(A:A),A1,"")
以下コピー
仮に平均値を閾値としましたが、あなたの目で見て適当な値を与えてももちろん構いません。たとえば「７で切る」とか。



またご質問に書かれたそのデータだけを見れば、数字が「上昇から下降に転じた値」を計算させれば出てきます。
Ｃ２：
=IF(AND(A1<A2,A2>=A3),A2,"")
以下コピー

しかし現実のデータはジグザグしながら上がったり下りたりするモノですから、通常こんな単純なやり方じゃ、うまくいきません。
ピークtoピークの計算がどうしても必要なら、レインフロー処理をしてからピークを拾います。計測器の付属ソフトが使えるときは使ってください。
あるいは「ジグザグ」がノイズであると判断できるなら、移動平均によってノイズをなまらせ、あなたがご質問で書かれたような単純増減するデータにしてから簡単にやっつける事も出来ます。「ピーク」の意味をよく考えてデータ処理を選んでください。

この回答へのお礼

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

お礼日時：2012/08/13 15:36