エクセル散布図の最大値の横軸値

質問

エクセルで(x,y)2系列のデータがあるとして散布図にすると凸2次関数のようになったとします。
その時、最大値（極大値）のX軸の値を返す方法はありますでしょうか。

Y最大値の行のXの値を返す

という感じの関数になるのでしょうか。

よろしくお願いします。

回答No.5

MarcoRossiItaly · ベストアンサー

＞この計算は「2次関数に近似できる曲線に、最小二乗法を適用した近似曲線極値のX座標」の求め方という認識でよろしいでしょうか。

そうです。No.1さんの書かれている「X=-b/2a」という式を単にExcelのワークシート関数で書き換えた数式です。

＞さらに低次関数で近似できない場合（理想と離れている場合）もあるためもう少し一般的な求め方はないでしょうか。
　エクセルの6次多項式近似では全く合わないので、使えません。
　たとえば、Y切片・X切片でもいいので求められないでしょうか。

まず、LINEST関数は、2変数以上の1次式の各係数を求める関数です。使用する配列に2次、3次、…の値を含めることで、結果としてべき乗の多項式の係数を求めたことになる、という仕組みです。ですから、べき乗を用いているという意味においては、グラフで表示させるのと基本的に変わりません。

お手元に得られている数値に合う合わないではなく、元データの意味を考えて、本当にべき乗ではふさわしくないかの確認は必要かもしれませんが、やめにするんだとすると、他にどんな近似を考えるかです。それが先にないと、切片も求める方法はないというのが道理かと。

べき乗に代わる他の式が用意され、近似式が和の形をしている場合であれば、べき乗の係数を求めたのと同じ要領で、LINEST関数で求められそうではあります。また、対数、指数など他の初等関数を使って全体が和の形でない場合などであっても、グラフでも選択できるべき乗以外の近似式で近似できるかもしれません。

また、初等関数はテイラー展開できるはずなのだから、xの区間を区切れば、基本的に多項式で近似しても差し支えないのではないかとも思います。

いずれにしても、モデルなり、方針が先かと。

お礼

確かにモデル等が先に立てられれば良いと思います・・・

というより、最初の例で2次関数を立てたのが悪かったかもしれません。単純に最大のｙの時のｘを読みに行く、絶対値Yが最小の時のXを読む、という関数がないかと思った次第です。

具体的説明ができない状態での質問に丁寧にご回答いただきましてありがとうございました。

本件はご指摘があったように一旦締めさせていただきます。上記のアドバイス通りまず、モデルなりを立てることを考えてみます。その後再度トピ立てした時はまたよろしくお願いします。

回答No.4

keithin

あなたのご相談は

＞凸2次関数のようになったとします。
＞その時、最大値（極大値）のX軸の値を返す方法はありますでしょうか。

です。
何か違うことをしたいのでしたら、このご相談はキチンと解決で閉じてから、改めて具体的に「何をどうしたい」のかキチンとご相談を書いて投稿してください。

＃一応ご参考まで
Ｙ切片：
=INDEX(LINEST(B1:B10,A1:A10^COLUMN(A1:B1),TRUE,FALSE),3)
で計算できます。

ｘ切片：
ふつーに二次方程式の解の公式で計算してください。
-b±√(b^2-4ac)/2a
ってヤツです。
定数項、１次および２次の係数の求め方は回答済みです。

＞近似できない場合

近似曲線が引けるのに近似できないって、具体的にどういう事かさっぱりイミフメイです。
ヤリタイ事に見合った数式を組んで計算できるかもしれませんし、ゴールシークやソルバーで解を探索させてしまった方が手っ取り早いかも？しれません。
こちらも今回のご相談とはまた別のお話のようなので、やはり具体的な状況を添えて別途ご相談を投稿してください。

お礼

失礼いたしました。

同内容のトピ乱立はご法度だと思い、重ねて質問させていただきましたが、ポイント制度がある以上よくないことですね。キャッチの手法みたいになってしまい申し訳ありませんでした。

また、具体的事例が出せず説明が悪いためにお手数をおかけしていることは承知しています。

しかしながら、最後までお応えいただきましてありがとうございました。
本件は上記ご指摘のとおり一旦締めさせていただきます。

回答No.3

MarcoRossiItaly

なるほど、そうですね。詳しくないですが、No.1さんのおっしゃるとおり、LINEST関数により、多項式の係数は配列として求められるようです。失礼しました。

No.1さんの式中、「COLUMN(A1:B1)」という部分は、代わりに「{1,2}」と書いても同じです。

グラフに表示させた近似式から係数を読み取る方法については、15桁とか、桁数を多く表示させることができます。ですから、これでも正確な計算ができるかと…。表示の方法は、No.2をご覧ください。

なお目視とは言っても、近似式をダブルクリックすると、係数をCtrl+Cでコピーできます。ですから写し取るのに、数字を一つひとつタイプする必要はありません。

お礼

ありがとうございます。

No.1さんへも書きましたが、求めたいものはあまり単純な系でない場合多いです。
エクセルの6次多項式近似では全く合わないので、使えません。

たとえば、Y切片・X切片でもいいので求められないでしょうか。

回答No.2

MarcoRossiItaly

「近似曲線」の式を表示させ、その式を算数で習うとおり平方完成すればいいでしょう。

2次関数の係数を数式でセルに算出させるのは、グラフ機能が行っているのと同等なアルゴリズムを自分で用意してやる必要があるので、たいへんです…。近似式を目で見て転記、がラクかと。

以下は、近似曲線とその式の表示のさせ方です。

グラフ中のプロットを右クリックし、「近似曲線の追加」をクリック。表示されるダイアログで、「多項式近似」を選択。「次数」を「2」に設定。「前方補外」と「後方補外」に適当な値を入力。「グラフに数式を表示する」にチェックを入れ、「閉じる」ボタンを押します。

こうして表示させた近似式の係数の桁数を多く表示させたい場合は、近似式を右クリックして「近似曲線ラベルの書式設定」をクリック。「表示形式」で「数値」を選択し、桁数を入力してください。

曲線の最大値ではなく、単にyデータが最大になるときのxを求めるだけでいいなら、MAX関数でいいですね。

=index(a:b,match(max(b:b),b:b,),1)

回答No.1

keithin

必ずエクセル２００３以降を使います。

Ａ１：Ａ１０にＸの値
Ｂ１：Ｂ１０にＹの値
として

=-INDEX(LINEST(B1:B10,A1:A10^COLUMN(A1:B1),TRUE,FALSE),2)/(2*INDEX(LINEST(B1:B10,A1:A10^COLUMN(A1:B1),TRUE,FALSE),1))

のように計算できます。

＃参考：
二次曲線
y=aX^2+bX+c
の極値のＸ座標は
X=-b/2a
で求まります。

グラフで二次曲線の近似を挿入して、グラフから係数を読み取って計算してはいけません。
多くの場合に有効桁数不足で間違った計算結果になります。

お礼

ありがとうございます。
この計算は
「2次関数に近似できる曲線に、最小二乗法を適用した近似曲線極値のX座標」
の求め方という認識でよろしいでしょうか。

実は質問は簡単な系でさせていただいていますが、実際に求める数値は
極値だけでなく、Y切片・X切片もあります。
さらに低次関数で近似できない場合（理想と離れている場合）も
あるためもう少し一般的な求め方はないでしょうか。