マンガでよめる痔のこと・薬のこと

皆さんよろしくお願いいたします。
標題の計算をエクセルで行ってみました。
ところが、計算結果が係数によって合うこともあるのですが、
3つの異なる複素解が出てしまうことが有り、ありえない数値が出ることで困っています。
具体的には、3次方程式をax^3+bx^2+cx+d=0としたとき係数に次の数値を入れたときに起こります。
a=1/120、b=1/10,c=1/2,d=1
同じようなご経験のある方、ご教示いただければ幸いです。
計算方法は記号など以下のサイトを引用しました。
http://www2.biglobe.ne.jp/~ytajima/cardanos_form …
計算内容は以下のとおりです。
A1,B1,C1,D1セルにそれぞれの係数a,b,c,dを代入します。
A2=B1/A1---p=b/aの計算
A3=C1/A1---q=c/aの計算
A4=D1/A1---r=d/aの計算
A5=-(A2^2)/3+A3---m=-p^2/3+qの計算
A6=(2*(A2^3)/27)-(A2*A3/3)+A4---n=2p^3/27-pq/3+rの計算
A7=IMPOWER(IMSUM((-A6/2),IMPOWER((A6^2/4+A5^3/27),1/2)),1/3)---u=(-n/2+(n^2/4+m^3/27)^0.5)^(1/3)の計算
A8=IMPOWER(IMSUB((-A6/2),IMPOWER((A6^2/4+A5^3/27),1/2)),1/3)---v=(-n/2-(n^2/4+m^3/27)^0.5)^(1/3)の計算
A9=COMPLEX(A10,A11)---ω=(-1+√3i)/2の計算
A10=-1/2---Re(ω)
A11=SQRT(3)/2---Im(ω)
A12=IMPRODUCT(A9,A9)---ω^2の計算
A13=IMSUB(IMSUM(A7,A8),(A2/3))---解1=u+v-p/3の結果
A14=IMSUB(IMSUM(IMPRODUCT(A9,A7),IMPRODUCT(A8,A12)),(A2/3))---解2=ωu+vω^2-p/3の結果
A15=IMSUB(IMSUM(IMPRODUCT(A9,A8),IMPRODUCT(A7,A12)),(A2/3))---解3=uω^2+vω-p/3の結果

このQ&Aに関連する最新のQ&A

A 回答 (7件)

あれ? 手元の Excel 2007 SP1 (#2 と同じ環境です) で試したんですが, 期待通りの結果が出てますよ.


例えば
A1=-1/120
B1=1/10
C1=-1/2
D1=1
A2=B1/A1
A3=C1/A1
A4=D1/A1
A5=-(A2^2)/3+A3
A6=(2*(A2^3)/27)-(A2*A3/3)+A4
A7=IMPOWER(IMSUM((-A6/2),IMPOWER((A6^2/4+A5^3/27),1/2)),1/3)+A7:A69
A8=IMPRODUCT(-1/3,IMDIV(A5,A7))
A9=COMPLEX(A10,A11)
A10=-1/2
A11=SQRT(3)/2
A12=IMPRODUCT(A9,A9)
A13=IMSUB(IMSUM(A7,A8),(A2/3))
A14=IMSUB(IMSUM(IMPRODUCT(A9,A7),IMPRODUCT(A8,A12)),(A2/3))
A15=IMSUB(IMSUM(IMPRODUCT(A9,A8),IMPRODUCT(A7,A12)),(A2/3))
とすると (つまり 4 ですね)
A13=4.64437070925218
A14=3.67781464537391+3.50876191956744i
A15=3.67781464537391-3.50876191956744i
が得られ, それぞれの残差は
2.88657986402541E-15
-6.21724893790088E-15-3.10862446895044E-15i
-6.21724893790088E-15+3.10862446895044E-15i
です.
    • good
    • 0
この回答へのお礼

ご回答頂きありがとうございます。
どうやら、小生のエクセルに計算違いや入力ミスがあったようです。
おかげさまで間違いに気づくことが出来ました。
ありがとうございます。

お礼日時:2008/04/15 00:34

>u=(-n/2+(n^2/4+m^3/27)^0.5)^(1/3)


>v=(-n/2-(n^2/4+m^3/27)^0.5)^(1/3)

この {u, v} のペアは
  ・いずれも実数
  ・互いに共役な(非実)複素数
のいずれか。

どちらにせよ、このペアが実零点(x = u+v-p/3)の一つを与えるわけです。
 
    • good
    • 0

今は手元に計算できる環境がないので, 回答ではなく指摘だけ:


IMPOWER で 3乗根を計算するというのは, 数学的には
z = r e^(iθ) から z^(1/3) = r^(1/3) e^(iθ/3) を計算する
ことと等しいはずです... というか結果から見る限りこの計算をしているようです.
で, z として負の実数, 例えば -1 を入れると
-1 = 1 ・ e^(iπ) だから z^(1/3) = (-1)^(1/3) として
1^(1/3) ・ e^(iπ/3) = 1/2 + (√3/2)i
が得られます. これは最初の例でも本質的に同じで,
1.17398499670533+2.03340166161721i
の実部と虚部の比は 1:√3 です.
これに対し正の実数を入れると 3乗根として (正の) 実数となり, ここで uv = -m/3 という関係を崩すことになります. だから v = -m/(3u) と計算する, と書きました.
で今はどのように計算しているのでしょうか. 全体を改めて書いていただいて, その上で例えば (2) に対して期待される結果と実際に得られた値を書いていただけるとありがたいです.
    • good
    • 0
この回答へのお礼

ご回答頂きありがとうございます。
数学的な計算方法など懇切丁寧にご指導頂きありがとうございます。
今どのように計算しているかというと
v=(-n/2-(n^2/4+m^3/27)^0.5)^(1/3)の計算
A8=IMPOWER(IMSUB((-A6/2),IMPOWER((A6^2/4+A5^3/27),1/2)),1/3)

A8=IMPRODUCT(-1/3,IMDIV(A5,A7))
としてみました。これはuv = -m/3を満たすようにvを求めるようにしたものです。しかしながら結果は求めたい値に対し以下のようになりました。
1)a=1/120,b=1/10,c=1/2,d=1(解決済み)
 自分の計算結果と得たい結果は精度の問題はありますが一致しました。
 x1=-4.6443707092516、X2=-3.67781464537391+3.50876191956745i
 x3=-3.67781464537391-3.50876191956745i
2)a=8/120,b=4/10,c=1,d=1
 自分の計算x1=0,x2=-3+2.44948974278319i,x3=-3-2.44948974278319i
 得たい結果x1=-2.3221853357179962,
      x2=-1.8389073171410022+1.754380971381805i
      x3=-1.8389073171410022-1.754380971381805i
3)a=125/120,b=25/10,c=5/2,d=1
 自分の計算x1=5.107025913275752E-015,
      x2=-1.2+0.979795897113268i
      x3=-1.2-0.979795897113268i
 得たい結果x1=-0.9288741413663972,
      x2=-0.7355629289328063+0.7017523842103995i,
      x3=-0.7355629289328063-0.7017523842103995i
4)a=-1/120,b=1/10,c=-1/2,d=1
 自分の計算x1=0,x2=6+4.89897948556636i,x3=6-4.89897948556636i
 得たい結果x1=4.644371011781658
      x2=3.677814734101803+3.508761733998084i
      x3=3.677814734101803-3.508761733998084i
5)a=-8/120,b=4/10,c=-1,d=1
 自分の計算x1=0,x2=3+2.44948974278319i,x3=3-2.44948974278319i
 得たい結果x1=2.3221853357179962,
      x2=1.8389073171410022+1.754380971381805i
      x3=1.8389073171410022-1.754380971381805i
6)a=-125/120,b=25/10,c=-5/2,d=1
 自分の計算x1=-5.10702591327872E-015
      x2=1.2+0.979795897113268i
      x3=1.2-0.979795897113268i
 得たい結果x1=0.9288741413663872
      x2=0.7355629289328063+0.7017523842103995i
      x3=0.7355629289328063-0.7017523842103995i

お礼日時:2008/04/14 14:16

タイプミス。


  v^3 = 4*{-1 + SQRT(5)}
  w^3 = 4*{-1 - SQRT(5)}
    • good
    • 0

こちらの EXCEL は複素関数無しですけど.... 。

下記の問題なら実数演算で用を足せます。

-------------------------
原方程式: x^3 + 12x^2 + 60x +120 = 0
x = y-4 として: y^3 + 12y + 8 = 0
y = v + w として:
  v^2 + w^2 + 8 =0
  v*w = -12
この二式から:
  v^6 + 8v^3 - 64 = 0
したがって:
  v^3 = 4*{-1 + SQRT(5)}
  v^3 = 4*{-1 - SQRT(5)}
あとは逆行。
  y = -0.644...
  x = -4.644...
…でした。
    • good
    • 0

あれ? 手元の Excel 2007 で計算した限りでは


A8=IMDIV(A5,IMPRODUCT(-3,A7))
としたら
A13 = -4.64437070925216
となり, 元の方程式に代入したときの残差は2.22044604925031E-15-2.21695285066186E-16i
になりました. これなら精度も十分でしょう.
ちなみにこの問題の場合 m = 12 で
u^3 = 4.94427190999916,
v^3 = -12.9442719099992
となっています. これらに対して IMPOWER で 3乗根を計算すると u の方は 1.70359928415849 と実数が得られますが, v は 1.17398499670533+2.03340166161721i
と複素数になり, uv = -m/3 を満たしません.

この回答への補足

お気づきの点『ちなみにこの問題の場合 m = 12 で~
u の方は 1.70359928415849 と実数が得られますが, v は 1.17398499670533+2.03340166161721i
と複素数になり, uv = -m/3 を満たしません.』
おっしゃるとおりですね、解は合っているのに制約条件を満たさないとは
不可思議に思います。自分も検索したり、エクセルの式を色々
いじくってますが、うまくいきません。

補足日時:2008/04/14 12:29
    • good
    • 0
この回答へのお礼

ご回答頂きありがとうございます。
質問の係数a=1/120,b=1/10,c=1/2,d=1については、求めようとした解が出てきました。
ありがとうございます。
しかし、まだ以下の2)~5)の係数では、おかしな解が計算されてしまいます。
求めたい係数の種類は解決済みの1)の係数も含め、以下のとおりです。
1)a=1/120,b=1/10,c=1/2,d=1(解決済み)
2)a=8/120,b=4/10,c=1,d=1
3)a=125/120,b=25/10,c=5/2,d=1
4)a=-1/120,b=1/10,c=-1/2,d=1
5)a=-8/120,b=4/10,c=-1,d=1
6)a=-125/120,b=25/10,c=-5/2,d=1
結果を以下のHPで確認してみました。
http://homepage3.nifty.com/Architect/TE/ThreeEqu …
ちなみに上の1)~5)は制御工学の教科書に解が載っており、
上のサイトでの計算結果と一致します。
実用的に使用するために、どのような係数に対しても正しい結果を
エクセルで出力したいのですが、うまくいきません。
度々申し訳ありませんが、お気づきになられたことなど有りましたら
ご教示いただきたくお願いいたします。

お礼日時:2008/04/14 12:25

Cardano の公式で 3次方程式を解く途中, u^3 と v^3 を求めてから u, v を計算するんですが, この u と v には


uv = -m/3
という制約が付いています (というかもともとこの式を満たさなければならない).
つまり, u と v を u^3, v^3 からそれぞれ独立に求めてはダメで, 一方を求めたあともう一方は uv = -m/3 から求めなきゃならないような気がします.
    • good
    • 0
この回答へのお礼

ご回答頂きありがとうございます。
おっしゃるとおりuv = -m/3の制約があります。ご指摘ありがとうございます。
しかしなが、制約については、以下の計算手順を踏んで、解を計算しています。
u,vについては次式になります。
u=(-n/2+(n^2・4+m^3/27)^0.5)^(1/3)=Aとおく
v=(-n/2-(n^2・4+m^3/27)^0.5)^(1/3)=Bとおく
するとu,vの根はそれぞれ3つの解がでます。
ω=-1/2+(√3)i/2とすると
u=A,Aω,Aω^2
v=B,Bω,Bω^2
これらを組み合わせると9通りの解(u+v-p/3)ができますが、
ここでuv = -m/3を満たす解は次の3通りしか有りません。
u+v=A+B,Aω+Bω^2,Aω^2+Bω
これは計算に反映されているので、ここは問題ないのではないかと
思われます。
ためしにuのみ求めてv=-m/(3u)として計算してみましたが、
解は合いませんでした。
せっかくアドバイスを頂いたのに申し訳有りませんが、
他にお気づきの点などありましたら、なんでも結構ですのでご教示
いただきたくお願いいたします。

お礼日時:2008/04/13 11:50

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q三次方程式の解法について

三次方程式を解くプログラムを作成したいです。
三次方程式a*x3+b*x2+c*x+d=0を解くのに,
カルダノの公式がありますが,解が全て実数解であるとしても,
計算過程で虚数が存在することを考えなければなりません。

しかし,a=1で解が実数解の場合,
計算過程で虚数を考えなくても,簡単に実数解を求める
ことができる(エクセルで簡単に)と聞きました。
ニュートン法等の収束計算を用いる方法ではないようです。

このような方法をご存知でしたら,詳しく教えてください。
よろしくお願いいたします。

Aベストアンサー

ビエタの解
http://ja.wikipedia.org/wiki/%E4%B8%89%E6%AC%A1%E6%96%B9%E7%A8%8B%E5%BC%8F#.E9.82.84.E5.85.83.E4.B8.8D.E8.83.BD.E3.81.AE.E5.A0.B4.E5.90.88
なら、計算過程で虚数を使わずに実数解が出てきます。

QExcelで3次方程式を解く方法

3次方程式には解の公式が存在していて、この公式をExcelのセルに入力して、係数を入れれば答えが出るようにしたいのですが、どうも複素数の3乗根が取り扱えないようでエラーとなってしまいます。 実数解を持つことは分かっている場合でも、計算途中で複素数の3乗根が出てくるので答えが出ません。 これを回避する方法をご存知でしたら教えてください。

Aベストアンサー

複素数 x + iy の 3乗根を求めるなら, 極座標で r e^(iθ) としてから r^(1/3) e^(iθ/3) = r^(1/3) cos (θ/3) + i r^(1/3) sin (θ/3) とするのが簡単かな.
r = (x^2 + y^2)^(1/2), θ = atan2(x, y) です.
x = y = 0 だと困りますが, まあなんとかしてください.

Qエクセルで計算すると2.43E-19などと表示される。Eとは何ですか?

よろしくお願いします。
エクセルの回帰分析をすると有意水準で2.43E-19などと表示されますが
Eとは何でしょうか?

また、回帰分析の数字の意味が良く分からないのですが、
皆さんは独学されましたか?それとも講座などをうけたのでしょうか?

回帰分析でR2(決定係数)しかみていないのですが
どうすれば回帰分析が分かるようになるのでしょうか?
本を読んだのですがいまいち難しくて分かりません。
教えてください。
よろしくお願いします。

Aベストアンサー

★回答
・最初に『回帰分析』をここで説明するのは少し大変なので『E』のみ説明します。
・回答者 No.1 ~ No.3 さんと同じく『指数表記』の『Exponent』ですよ。
・『指数』って分かりますか?
・10→1.0E+1(1.0×10の1乗)→×10倍
・100→1.0E+2(1.0×10の2乗)→×100倍
・1000→1.0E+3(1.0×10の3乗)→×1000倍
・0.1→1.0E-1(1.0×1/10の1乗)→×1/10倍→÷10
・0.01→1.0E-2(1.0×1/10の2乗)→×1/100倍→÷100
・0.001→1.0E-3(1.0×1/10の3乗)→×1/1000倍→÷1000
・になります。ようするに 10 を n 乗すると元の数字になるための指数表記のことですよ。
・よって、『2.43E-19』とは?
 2.43×1/(10の19乗)で、
 2.43×1/10000000000000000000となり、
 2.43×0.0000000000000000001だから、
 0.000000000000000000243という数値を意味します。

補足:
・E+数値は 10、100、1000 という大きい数を表します。
・E-数値は 0.1、0.01、0.001 という小さい数を表します。
・数学では『2.43×10』の次に、小さい数字で上に『19』と表示します。→http://ja.wikipedia.org/wiki/%E6%8C%87%E6%95%B0%E8%A1%A8%E8%A8%98
・最後に『回帰分析』とは何?下の『参考URL』をどうぞ。→『数学』カテゴリで質問してみては?

参考URL:http://ja.wikipedia.org/wiki/%E5%9B%9E%E5%B8%B0%E5%88%86%E6%9E%90

★回答
・最初に『回帰分析』をここで説明するのは少し大変なので『E』のみ説明します。
・回答者 No.1 ~ No.3 さんと同じく『指数表記』の『Exponent』ですよ。
・『指数』って分かりますか?
・10→1.0E+1(1.0×10の1乗)→×10倍
・100→1.0E+2(1.0×10の2乗)→×100倍
・1000→1.0E+3(1.0×10の3乗)→×1000倍
・0.1→1.0E-1(1.0×1/10の1乗)→×1/10倍→÷10
・0.01→1.0E-2(1.0×1/10の2乗)→×1/100倍→÷100
・0.001→1.0E-3(1.0×1/10の3乗)→×1/1000倍→÷1000
・になります。ようするに 10 を n 乗すると元の数字になるた...続きを読む

Qエクセルで3次式の検量線のyからxの求め方

吸光度測定で検量線を引いて、そこからサンプル濃度を求めたいのですが、
エクセルで作成した近似曲線は3次式が一番フィットしました。
直線回帰の場合はy切片を引いて、傾きで割ればよいですが、
3次式の検量線の場合は、どのようにしてyの値(吸光度)から
xの値(濃度)を求めればよいか教えてください。
具体的には、
y = 2.11x3 - 4.81x2 + 2.93x + 0.0277の式で
y = 0.150の時に、x値を求めたいです。
xyが逆の検量線を書いて、計算もしてみましたが、
近似曲線では数値が微妙に違ってくる気がしています。
よろしくお願いします。

Aベストアンサー

アグリゲーションを起こす場合、1次で近似できないことはあります。しかし、3次近似というのはどうでしょう?

なぜ3次近似になるか、メカニズムを考えなければなりません。

計算は、その後のことと思います。

(近似できればよい、というものではありません)

Q3次方程式の解 (エクセルのフリーのアドイン)

お世話になります。

以前どこかで、3次方程式の解を求めるのにすごく便利な
フリーのアドインソフトがありました。
(アドインソフトそのものは持っています)

しかし、どこのホームページからダウンロードしたか分からず、
検索しても見つかりません。
どなたかご存知でしたら、どうぞ教えて下さい。m(_ _)m

Aベストアンサー

>検索でたくさん出てきたとは...(どのようにされました?)

キーワードは、
 excel 方程式 アドイン
(Google-jp) トップ 50 件にありました。
ほかにも、いろいろあります。

Qエクセルによる方程式の解

エクセル初心者です。
エクセルで方程式の解を求めるにはどの用にすればいいのでしょうか?
たとえば
X^2=100
10^(1/X)=1200
という方程式の解をだすにはどうすればいいか
教えてください。

Aベストアンサー

>X^2=100
だった10
>10^(1/X)=1200
だったら0.324761663786812
を求めると言う事でよいですか?

この場合であれば、ゴールシークで求められます。
 ・A1にとりあえず値を何か埋める。(変化値)
 ・A2に計算式を埋める。この場合は”=a1^2"(目標値)
 ・メニューよりツールゴールシークを選択し
  数式セルにA2を
  目標値に10を
  変化させるセルにA1を指定
以上で求められます。

Qエクセルで三次関数をつくりたいのですが

検量線を三次関数で書きたいというヒトから質問を受けたのですが、
y=4E-11x^3-3E-7x^2+0.0007x
というような式をエクセルで作成する場合、こんな式の作り方で合っているのでしょうか?
y=a(exp(1)+b)x^3+c(exp(1)+d)x^2+ex
a=4
b=-11
c=-3
d=-7
e=0.0007
また、基本的なことですが「E」とは自然対数の底(≒2.71828)で合っていますか?

Aベストアンサー

う~ん。まだ、やりたいことが見えません。

>定数項にべき乗がはいっているので
でも、定数は定数です。特に問題あるとは思えないです。
 
ax^3+bx^2+c=y -> ax^3+bx^2+(c-y)=0 と変形して
解の公式をつかうんでしょうが・・

それより、「エクセルで」ってとこがポイントです。
エクセルでは数式を直接グラフ化してはくれないですよね?
あくまでも数式を満たす(x,y)の組を沢山並べて散布図を書くだけです。

それなら、逆関数など求めなくとも、x,yを入れ替えてモノとして元の
y=xの3次式 のグラフを書けばよいのではないでしょうか?

目的が「何か」によって、しなくていいアプローチなど避けるべきだと考えますが・・

Qエクセルの関数による近似式の求め方

(1)1,2,3,4 (2)5,6,7,8
(1)と(2)の二つの数字があります。
(1)をx軸、(2)をy軸としてエクセルの関数の式のみでy=a*x^2+b*x+cを求める事は可能ですか?
現状は(1)(x軸)と(2)(y軸)からグラフを書き近似曲線を描かせ、2次の多項式を表示させているんですが、大量にデータがあり、この作業に大変時間を要しています。調べに調べた結果、y=a*x+bの形は関数で表示可能なことは確認取れているんですが、2次の式は未だ発見できません。
非常に困っています。回答の方よろしくお願いします。また何か不明な点があったら何でも言って下さい。

Aベストアンサー

y の範囲 A2:A10
x の範囲 B2:B10
として、
C2=B2^2
C10までコピー。

と、しておけば
係数 a =INDEX(LINEST(A2:A10,B2:C10),1)
係数 b =INDEX(LINEST(A2:A10,B2:C10),2)
定数 c =INDEX(LINEST(A2:A10,B2:C10),3)

QEXCEL ゴールシークの関数?

EXCEL ゴールシークの関数?
ゴールシークを画面操作をしなくて ゴールシーク機能を持つ 関数はありませんか?
無ければ 複合関数は? VBを使わないでEXCEL関数だけで 求める方法を探しています。

Aベストアンサー

こんにちは。

例えば、財務関数のNPVなどがありますが、ゴールシークの代わりに、関数だけで、オプションの循環参照計算があります。そうでなければ、基本的な話ですが、微分を使うか、もしくは、ミスが出やすいのですが、オートフィルで、目的の値を探すか、いずれかの方法があると思います。

ただ、VBというか、マクロのことだと思いますが、そういう区分けはあまり意味のないことだと思います。それは、アドインもピボットも、コマンドの3分の1程度は、動的なプログラムを使っているからです。また、分析ツールは完全にExcelの古いマクロです。

私は、マクロを中心とする回答者のひとりです。何かを使わないという条件付き質問は、学校のテストやクイズやお遊びとしてはありえるとしても、ゴールシークというものは、論理的な計算ではなく、反復する時系列な計算です。それは、基本的にマクロにほかなりません。ただし、私たちが考えるよりも、遥かに速い処理です。それを論理的な計算をするというなら、その求める具体的な質問がなければ、話は進みません。解答を求めたいなら、方法論は何でもアリではないかと思います。そうでないなら、純粋に数式で計算させるMathematica のようなもののほうが良いと思います。

こんにちは。

例えば、財務関数のNPVなどがありますが、ゴールシークの代わりに、関数だけで、オプションの循環参照計算があります。そうでなければ、基本的な話ですが、微分を使うか、もしくは、ミスが出やすいのですが、オートフィルで、目的の値を探すか、いずれかの方法があると思います。

ただ、VBというか、マクロのことだと思いますが、そういう区分けはあまり意味のないことだと思います。それは、アドインもピボットも、コマンドの3分の1程度は、動的なプログラムを使っているからです。また、分...続きを読む

Qエクセルで2次関数の計算

y=aX^2+bX+cという2次関数でYの値を入れてXを出す計算は可能なのでしょうか?
また、1次関数y=aX+bでaを求める関数がslope(範囲A,範囲B)のように、2次関数のa,bを求める関数があるのでしょうか?大変困っているので宜しくお願いします。

Aベストアンサー

=(-C9-SQRT(C9*C9-4*C8*(C10-C11)))/2/C8
=(-C9+SQRT(C9*C9-4*C8*(C10-C11)))/2/C8

C8にa
C9にb
C10にc
C11にY
の値を入れる


人気Q&Aランキング