vbaで極大値を抽出する方法

質問

数千の値を持つcsvファイルを所持しております。（A1-A5000位までに値が入っている）
値をグラフ化するとサインカーブの様な曲線が描かれる数値群なのですが
その中から

・極大となる値を始めから５つ
・その極大値に対する行の数

の二点を取り出すものを作りたいのですが、うまく行きません。
基本的にはおよそ１００ごとに極大をとるのですが
ノイズが入っており極大から極小に向けて常に減少を続ける訳でなく
たまに増加してる点が現れていたりします。

流れとしては

A1から１００個を探して、その中の極大を探す
A１０１から１００個を探して極大を・・・

みたいな風にやりたいのですが、もしよろしければご教授頂けないでしょうか。

回答No.1

hallo-2007 · ベストアンサー

VBAではなくて、関数案ですが
B列に
=MAX(A1:A100)　と入れて下までコピーします。
C列に
=INDEX(B:B,(ROW(A1)-1)*100+1)
と入れて下までコピーすれば、１０行ごとの値がでます。

＞ノイズが入っており極大から極小に向けて常に減少を続ける訳でなく
＞たまに増加してる点が現れていたりします。
たぶん、やりたいことは、実験値の中からノイズと思われる異常値を除去したいと
いう事ではないでしょうか。
エクセル　異常値　除去　或いは　ノイズ　除去
などで検索すると、いくつかの方ほが紹介されています。

考え方も色々ですが、例えば
A1:A100　の平均値と分散（或いは標準偏差値）を求めて
実験値が分散の範囲外であれば、空白にするとかであれば
B列に
=IF(A2>AVERAGE(A2:A101)+STDEV(A2:A101),"",A2)
といったような関数を下までコピーすれば、想定した異常値を空白にできます。

お礼

皆さんありがとうございました！
物理の実験で使いたかったのですが、皆さんの解答で非常に勉強になりました。
どの解答も良かったのですが一番早かった方のものにベストアンサーをつけさせて頂きます。
ありがとうございました！

回答No.3

Siegrune

## う～ん、懐かしい。卒業研究で波形解析のプログラムつくったんで(主テーマではないけど)。

>A1から１００個を探して、その中の極大を探す
>A１０１から１００個を探して極大を・・・
という発想は結構しんどいかも。

ン十年前に卒論に書いた内容から、書いておきます。
（当時は特許とか調べる環境がなかったので他の人の特許に抵触しているかどうか不明。
　また、私よりあとで特許が出願されていれば私は卒論集とか出版されているのを知っているので
　公開技術ということで対抗できますが、他の人は探して対抗するのは大変なので、
　ご自身で確認してくださいね。・・・と言うほど大した方法でもないですけど。
　でも私自身はタブン二度と使うことはないんだろうなんて思っていますが。）
⇒「近似」の勉強したらもっといい方法みつかるかもしれませんよ。と一応、言っておきます。

まず、A1～A5000を全部配列に入れてください。X1()とします。
また、1～5000を配列に入れます。Y1()とします。
X1(1)～X1(5000)までについて、差を求めて、d1()へ格納します。
d1(1)=X1(1)-X1(2)
d1(2)=X1(2)-X1(3)
・・・
x2()を用意します。
d1()を順に調べます。
d1(1)とd1(2)を比較して符号が反転した場合、
X1(2)をX2(1)へ入れます。また、Y2(1)にY1(2)を入れます。
d1(2)とd1(3)をを比較して符号が反転した場合、
X1(3)をX2(2)へ入れます。また、Y2(2)にY1(3)を入れます。
・・・
これで極大値と極小値が交互にでてくる配列のできあがりです。
X2()をX1()、Y2()をY1()に代入して再度処理すると
⇒複数の異なる正弦波からなる合成波のうち周期の短い正弦波を１つずつ除去した結果が出せる
というのが目的だったのですが（周期の長い正弦波の波長と振幅が分かれば、
最初の値から、周期の長い正弦波の値を除去して周期の短い正弦波の周期と波長もわかるという寸法。
これを繰り返せばいくつでも対応できたりするんですが、２つでも振幅が違うだけの正弦波は
誰が見ても分離できないとか制限もあります。）

さて、実際にはこのままではノイズが含まれているので期待した結果にはあまりなってくれません。
想定したノイズは２種類。
・振幅の小さい短かい時間で正負が微妙に逆転したノイズ。
・振幅がきわめて大きいきわめて短かい時間に発生した孤立波のようなノイズ。

まず、
X2()とY2()からd2()とt2()を求めます。
d2(1)=X2(1)-X2(2)
d2(2)=x2(2)-X2(3)
・・・
（t2()もY2()を使って同様に求めます。）

・振幅の小さい発生時間が短い時間で正負が逆転してしまったノイズ。
については、d2()の値が小さい、あるいは、t2()の値が小さいものを求めます。
仮に、d2(17)、あるいはt2(17) （どちらも同じ番号になるはずですが。）が対象だったとします。
d2(17)=X2(17)-X2(18)です。したがって、X2(17)とX2(18)、Y2(17)とY2(18)を廃棄します。
※廃棄の仕方は、配列をつめてもいいですし、別の配列に出力する形でもいいです。

・振幅がきわめて大きいきわめて短かい時間に発生した孤立波のようなノイズ。
についても、d2()の値が大きい、あるいは、t2()の値が小さいものを求めることのどちらでも
できそうですが、極大値のすぐ後に発生したときに、極大値の直後のノイズのほうを極大値
として認識してしまいそうです。
ですので、d2()の値が極めて大きいで判断したほうが安全ということになります。
やり方は上といっしょ。
⇒当時、こちらの理由からt2()の方で判定はしないことにしました。

d2()やt2()から、平均を求めて、平均の200%以上とか10%未満のデータを廃棄とか、
いろいろ試してみてましたけど、結局、このノイズ除去方法では、
極一部のノイズしかとれません。
測定値の一部分だけ正弦波のノイズがのっていたり、一部分だけ定量のノイズがのっていたりすると
もう正しく計算できません。
最後は、グラフにして目でみるしかないです。
そこは留意しておいてください。

## とりあえず、考え方だけ。
## いきなりサンプルかいてみてもなんのことやらわからないとなるでしょうし。
## といいつつ、サンプルは・・・どっかに8inchFPDにいれておいてあるはずだけど、
## ドライブないし、MS-DOSフォーマットですらないのであっても読めないでしょうしね。。。
## 作ってテストするのも大変だし。

回答No.2

imogasi

エクセルVBAの質問とはいえ、エクセルの問題ではなかろう。その主な課題は、処理するアルゴリズム的なことにあるだろう。エクセルの関数などで一発で出るとは思えない。
ニーズも考えると
（１）理系の実験データ
（２）株式などの相場のデータ
などが考えられるが、該当する、それぞれの専門カテゴリに、目的を示して、質問してはどうか。
又内容をもう少し詳しく書かないと回答しにくいのではないか。目的ももう少し詳しく。
長年質問回答を読んでいるが、ここはビジネス的な、人・モノ・カネの数量や価格や文章・語句情報などに関することを扱う（経験した）回答者が多いように感じるので、回答が極く少ないのではないかと思う。。
極大が区間の最大値の事であれば（置き換えられるのなら）関数の問題だろうが、区間は等間隔でよいのか。質問者に任せるのか、１律幅（１００データなど）か、データの状態で決るのか。