エクセル2007曲線の接線と傾きを求めたい（微分）

Question

●質問の主旨
エクセル2007で曲線の接線及び傾きを求めたいと
思っています。どういう操作をすればよいでしょうか？

●質問の補足
・添付画像上の曲線の範囲指定は（A4:B16）です。
・接線はグラフエリア上に描画し、傾きは任意の
　セル上に数値を求めたいと考えています。
・具体的には次の2点の接線とその傾きを求めたいと
　考えています（添付画像参照）
　
　１．日経平均が17,500円でC175が400円のとき
　２．日経平均が17,500円でC175が511円のとき

以上、ご存知の方がいらっしゃいましたら、
ぜひご教示願います。

なお使用機種はWIndows Vistaです。

CoalTar · Accepted Answer

#4です
＞業務の都合である金融工学の入門書に
＞掲載されている２次曲線
そのような学問は知らなかったです。

２次の近似曲線で代用できるという仮定の元
＃１さんの回答を一つにまとめるとC2セルに
=SUMPRODUCT(LINEST($B$2:$B$12,$A$2:$A$12^{1,2})*CHOOSE({1,2,3},2*A2,1,0))
下へオートフィル

現実的には空いているセル1行3列を選択し、
=LINEST($B$2:$B$12,$A$2:$A$12^{1,2})
を入力後、[Ctrl]+[Shft] ＋[Enter] で確定、配列数式({}で囲まれる)
それぞれの係数が算出されますので、それらの値を使ったほうが良いでしょう。

CoalTar · Answer

#4,#7です
＞・=SUMPRODUCT(LINEST($B$2:$B$12,$A$2:$A$12^{1,2})*CHOOSE({1,2,3},2*A2,1,0))
＞相対参照ではなく、絶対参照にしている点がどうしても分かりません。
LINEST($B$2:$B$12,$A$2:$A$12^{1,2})
は定数を出すので、コピーしたとき変化してもらっては困ります。
よって、絶対参照。

＞・=LINEST($B$2:$B$12,$A$2:$A$12^{1,2})
絶対参照に意味はありません。
単純に先の数式から抜き出したから。
また、その点が分かりやすいように。(かえって混乱させてしまったようです)

＞三度の質問で心苦しい限りですが、各関数式における
＞絶対参照の理由を教えてください。
当方の回答に関する返答・質問・疑問なら何度でもどうぞ。
ただし、追加質問は基本的にお断りしたいです。

imogasi · Answer

#２、＃３です。
私がグラフから目視で読み取るとき、見誤ったと思います。すみません。その部分は0でなくて6でしょう。
質問者のグラフに出ている式を信頼していただいて、それが正解です。
式部分のボックスを動かすとき誤って変えてしまったと思います。編集・移動可能部分なので。
 ーー
曲線の種類が、２次曲線と指定してなかったのが、迷いました。
画像やグラフの形から２次曲線としましたが。
グラフに近似曲線が出るので使いましたが、それをデータからの回帰曲線の式の係数を求める話題も、エクセルを使った
初等統計の本（やWEB）に出ています。
スムージングした曲線などになるとまた難しい。

keithin · Answer

グラフに描かせた近似曲線の式を信じて計算に使うと，今回ご相談のようなケースでは絶対に正しい答えにならないので注意してください。

＃得られた２次の式を書いて日経平均の17000から18000までの数字を入れ，値段の近似値を実際に計算させてみると，何がどれぐらい正しいのかすぐに確認できます。必ずご自分の手で動かして検証してください。

また
＞ただ私が操作を行うと1次項が
＞ｙ＝0.0002X2-0.3402x+51389ではなく、
＞ｙ＝0.0002X2-6.3402x+51389という式が
＞算出されます。

それをワタシにご質問いただいても困ります。

回答No.1でご説明した方法で計算すると
　２次の係数は0.000195804
　１次の係数は-6.340237762
　定数項は51389.27273
という結果になりますよ？

面倒がってまだ手を動かして試してない，なんてことは勿論無いと思いますが，必ず実際に手を動かして確認してから追加のご質問をお願いします。

それと数式をご自分で書くことが出来ないのでしたら，各点の接線の傾きは
=2*INDEX(LINEST(B6:B16,C6:D16,TRUE,TRUE),1,2)*A6+INDEX(LINEST(B6:B16,C6:D16,TRUE,TRUE),1,1)
のようになるとお話ししました。
実際には
=2*INDEX(LINEST($B$6:$B$16,$C$6:$D$16,TRUE,TRUE),1,2)*A6+INDEX(LINEST(B$6:B$16,C$6:D$16,TRUE,TRUE),1,1)
のようにします。

CoalTar · Answer

＞エクセル2007で曲線の接線及び傾きを求めたいと
曲線の説明をお願いします。
二次曲線であらわされる近似曲線の接線を求めるなら#1さんの方法で。
スムージングされた曲線の方法は、わかりかねるのでHPのリンク。

目的が分かれば違った角度からの視点もあるかもしれません。
また、ここはExcel掲示板ですから求めたい解も提示しましょう。

参考URL：http://www.efcit.co.jp/navi/navi.cgi?word=%83X%83%80%81%5B%83W%83%93%83O&cond=0&view=10

imogasi · Answer

#1です。
　y'=0.0002x-0.3402
は　y'=0.0002*x*2-0.3402 の誤りで訂正します。

imogasi · Answer

質問がどの程度聞いているのかわからない。はっきりさせること。
例データ
A列　　　　B列
平均値段
17000193
17100226
17200264
17300305
17400351
17500400
17600453
17700511
17800572
17900637
18000705
（お願い）
画像じゃなくて、シートのデータを範囲指定してコピーし、質問文に貼り付けてくれたら
回答者・読者は、コピペ＋区切り位置で、一遍にデータがシートに再現できる。
このほうがはっきり見えるし、テマを省いてほしい。
ーー
操作
並びかえ　A列の小から大（昇順）
範囲指定　A1:B12
挿入ーグラフー　　散布図
軸の書式設定ー目盛間隔ー１００　（質問の画像に似せるために）
近似曲線の書式設定
グラフに数式を表示する
ｙ＝0.0002X2-0.3402x+51389
なんて式が現れる。
こういう方向ではダメなのか。
この数式からで質問者が、２次関数の微計数　y'=0.0002x-0.3402
を知って、x値を代入してyを計算してはダメなのか。
ーー
以上の方向は間違って居るか？知っていて検討済みか？
（１）何処までをエクセルにやらすのか
（２）質問者は何処までのエクセルの技量なのか
(３)何処までつなぎの人がするものを省きたいのか
など、考えてください。
ーー
エクセルにはSLOPE関数、TREND関数なんていうのもある。
Googleででも「エクセル　SLOPE関数」「エクセル　TREND関数」
で照会して記事を見てみたら。

keithin · Answer

簡単な方法：
試しに２次の多項式近似を入れてみるとＲ2＝0.999997692と極めて良い近似が得られたので，２次の近似を採用することとする。
Ｃ６以下に日経平均の２乗を計算
　=A6*A6
Ｄ６以下に日経平均の１乗を計算
　=A6

２次の近似式の２次の係数
=INDEX(LINEST(B6:B16,C6:D16,TRUE,TRUE),1,2)
１次の係数
=INDEX(LINEST(B6:B16,C6:D16,TRUE,TRUE),1,1)
定数項
=INDEX(LINEST(B6:B16,C6:D16,TRUE,TRUE),1,3)
Ｒ２
=INDEX(LINEST(B6:B16,C6:D16,TRUE,TRUE),3,1)

各点における近似曲線の傾きは近似曲線の微分式より
＝２＊２次の係数＊日経平均＋１次の係数
と求まる。

必要に応じて接線の上端下端の範囲を指定してプロットを計算し，グラフに追加すること。

＃必ずExcelの関数のヘルプでLINEST関数の説明をよく目を通しておくこと。

エクセル2007曲線の接線と傾きを求めたい（微分）

#4です

この回答への補足

#4,#7です

#２、＃３です。

グラフに描かせた近似曲線の式を信じて計算に使うと，今回ご相談のようなケースでは絶対に正しい答えにならないので注意してください。

＞エクセル2007で曲線の接線及び傾きを求めたいと

#1です。

質問がどの程度聞いているのかわからない。

この回答への補足

簡単な方法：

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング