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

質問

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

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

　１．日経平均が17,500円でC175が400円のとき
　２．日経平均が17,500円でC175が511円のとき

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

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

回答No.7

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

#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様たびたびのご回答ありがとうございます。
求めたかった接線の傾き、2次曲線の各項をそれぞれ
求めることができました。ただその関数式の内容について
質問がございます。

補足質問についてもご確認いただければ幸いです。

補足

ご指導いただいた

・=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})

を用いると各接線の傾きと2次曲線の各項を算出することが
できました。ただそれぞれの関数式において範囲指定を
相対参照ではなく、絶対参照にしている点がどうしても
分かりません。

三度の質問で心苦しい限りですが、各関数式における
絶対参照の理由を教えてください。

お忙しいところ誠に恐れ入りますが、よろしくお願いします。

回答No.8

某HN クロメート(Chromate)（@CoalTar）

#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})
絶対参照に意味はありません。
単純に先の数式から抜き出したから。
また、その点が分かりやすいように。(かえって混乱させてしまったようです)

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

お礼

三度にわたる回答を下さいまして、
本当にありがとうございました。

なるべくエクセルの本やら数学の参考書なども
活用して、質問をしぼれるよう精進します。

回答No.6

imogasi

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

お礼

imogasi様、たびたびのご回答ありがとうございます。

曲線の種類を2次曲線であることを、私が予め
明示しておけば良かったです。今後質問する時の
参考にします。

またエクセルについても、数学についても初級者
なので、時間を見てそれらの参考書を読んで
専門家の方にスムーズな質問ができるように
心がけたいと思います。

回答No.5

keithin

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

＃得られた２次の式を書いて日経平均の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)
のようにします。

お礼

たびたびのご回答ありがとうございます。
keithin様のご忠告も今後の参考といたします。

各点の接線の傾きについて、ご指導の通り

=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)

を入力したら期待する答えが出すことをできました。
大変助かりました。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＞エクセル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

お礼

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

業務の都合である金融工学の入門書に
掲載されている２次曲線とその接線の
傾きをエクセルで表示できるように
しなければならなくなりました。

命じられた私は、

・エクセルも初級者
（初歩の関数を組み込んだ表作成や申込書などが作成できる程度）
・微分は数学苦手な人向けに書かれた本を読みかじった程度

なので、目的が具体的に申し上げられず大変恐縮です。

もう少しエクセルそのものや微分について
時間の合間をみて勉強するようにします。

CoalTar様の参照URLも今後の参考にしたいと
思います。

回答No.3

imogasi

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

お礼

微分係数までお知らせくださいまして
ありがとうございます。

補足質問についてもご確認いただければ
幸いです。

回答No.2

imogasi

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

お礼

早速のご回答ありがとうございます。

ご指摘の通り、データが画像でかつ、
質問本文にデータを掲載していなかったこと、
大変恐縮です。エクセルの技量もインターネットに
関する知識も初級者程度であったことも
前もってお知らせすべきでした

ご回答について補足質問がございますのでそちらの
方もご確認いただければ幸いです。

補足

「操作」についてですが、imogasi様の
ご指導の通り操作すると、
グラフエリア上に２次関数が現れました
ありがとうございました。

ここまでわかれば、２次関数の微分係数y'を
算出することができます。

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

これは私の式の入力について、
これは何か私の入力もしくは「近似曲線の設定」などについて
誤りがあったのでしょうか？

たびたびで大変恐縮ですが、ご確認いただければ
幸いです。なお元データをコピペいたしましたので
ご参照ください。

　 A列　　　B列
１　平均　　　値段
２　17000　　193
３　17100　　226
４　17200　　264
５　17300　　305
６　17400　　351
７　17500　　400
８　17600　　453
９　17700　　511
10 17800　　572
11 17900　　637
12 18000　　705

回答No.1

keithin

簡単な方法：
試しに２次の多項式近似を入れてみるとＲ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関数の説明をよく目を通しておくこと。

お礼

早速のご回答ありがとうございます。
上記の関数式で2次関数式を出すことが
できました。

ただ、１次項について確認したいことが
ございますので、ANo.3の補足質問についても
ご確認いただければ幸いです。

よろしくお願いします。

補足

申し訳ありません。「お礼」のところで「ANo.3の補足質問」について
とありますが、「ANo.1のご回答」と書くべきでした。申し訳ありません。

LINEST関数を確認の上、keithin様のご指導の通り、
関数操作を行いますと2次関数の式が出来上がりました。

ただ私が操作を行うと1次項が

ｙ＝0.0002X2-0.3402x+51389ではなく、
ｙ＝0.0002X2-6.3402x+51389という式が

算出されます。

また他の会員様に教わったやり方で

グラフエリアの近似曲線を右クリック
↓
近似曲線の書式設定
↓
グラフに数式を入力するにチェック

というやり方でも1次項は-6.3402xとなります。
何か私のエクセルの設定等に誤り等が
あったのでしょうか？ご確認くだされば幸いです。

なお元データをコピペいたしましたので
ご参照ください。

　 A列　　　B列
１　平均　　　値段
２　17000　　193
３　17100　　226
４　17200　　264
５　17300　　305
６　17400　　351
７　17500　　400
８　17600　　453
９　17700　　511
10 17800　　572
11 17900　　637
12 18000　　705

以上よろしくお願い申し上げます。