Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


3523 / 13644 ツリー ←次へ | 前へ→

【61647】近似曲線のテキストデータが読み取れない yuji 09/5/24(日) 15:10 質問[未読]
【61650】Re:近似曲線のテキストデータが読み取れない yoshi 09/5/24(日) 16:33 回答[未読]
【61653】Re:近似曲線のテキストデータが読み取れない kanabun 09/5/24(日) 21:32 発言[未読]
【61655】Re:近似曲線のテキストデータが読み取れない yuji 09/5/24(日) 23:28 お礼[未読]
【61654】Re:近似曲線のテキストデータが読み取れない kanabun 09/5/24(日) 23:01 発言[未読]

【61647】近似曲線のテキストデータが読み取れない
質問  yuji  - 09/5/24(日) 15:10 -

引用なし
パスワード
   質問させてください。

Sub Macro1()
  Range("A1:B7").Select
  Charts.Add
  ActiveChart.ChartType = xlXYScatterLinesNoMarkers
  ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B3:C7" _
    ), PlotBy:=xlColumns
  ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
  ActiveChart.HasLegend = False
  ActiveChart.SeriesCollection(1).Select
  ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPolynomial, Order:=4 _
    , Forward:=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:= _
    False).Select
  ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Select
  Selection.NumberFormatLocal = "0.0000000000_ "
Range("D10").Value=ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
End Sub
といった、プログラムをEXCEL2003で使用しておりました。

A1:B7に入っているデータを使用して、近似式を作成し、その近似式を読み取るという動作をさせてたいという意図でしようしておりました。

しかしながら、このたびEXCEL2007をインストールして使用していたところ。
上記に記載の
ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
という方法では、読み取った近似曲線の数式の桁数が5桁となってしまうようです。
(尚、グラフ上の数式表示はちゃんと10桁で表示されます)

本件、どのように対応していいのか、まったく見当がつかず、途方にくれております。どなたかお教えいただけましたら幸いです。

【61650】Re:近似曲線のテキストデータが読み取れ...
回答  yoshi  - 09/5/24(日) 16:33 -

引用なし
パスワード
   >本件、どのように対応していいのか、まったく見当がつかず、途方にくれております。どなたかお教えいただけましたら幸いです。

多項式近似を行うのにブラフの近似曲線に付いているデータラベルから求めるなんて根本的によろしくないと思います。
データラベルでは真の値は得られず桁落ちの可能性もあります。
計算して求めるのがいいと思います。
シートを用いるなら、
ht tp://support.microsoft.com/kb/402428/ja
vbaで計算するなら
回帰分析関数 kLstsq関数(多項式近似の回帰分析)
ht tp://homepage2.nifty.com/kmado/kvba3.htm
等が参考になるでしょう。

【61653】Re:近似曲線のテキストデータが読み取れ...
発言  kanabun  - 09/5/24(日) 21:32 -

引用なし
パスワード
   ▼yuji さん:

>ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
>という方法では、読み取った近似曲線の数式の桁数が5桁となってしまうようです。
>(尚、グラフ上の数式表示はちゃんと10桁で表示されます)

なるほど、そのようですね。

>  Selection.NumberFormatLocal = "0.0000000000_ "

書式を「指数表示」にしておけば桁落ちすることはないと思います。

以下は(むかし作ったものですが)対象トレンドラインをマウスで選択しておいて
実行すると、どこに計数を書き込むか聞いてきますので、
書き出し先セルを指定してください。
(近似式を得るというより、係数を得るほうがあとの補間計算に使いやすいと
思いましたので、計数だけを複数セルに分けて出力しています。)

Sub 選択TrendLineの係数を得る()
 Dim Coef    '係数を格納
 Dim c As Range

 '★グラフの系列を選択して実行すること
 If TypeName(Selection) <> "Trendline" Then
   MsgBox "近似曲線を選択して実行してください"
   Exit Sub
 End If
 
 Coef = GetCoef(Selection)

 On Error Resume Next
 Set c = Application.InputBox("書き出し先先頭セルを指定してください", Type:=8)
 On Error GoTo 0
 If Not c Is Nothing Then
   c.Resize(, UBound(Coef) + 1).Value = Coef '横方向に書き出すばあい
   'c.Resize(UBound(Coef) + 1).Value = Application.Transpose(Coef)
   Set c = Nothing
 End If
End Sub

'--- TrendLineの各係数を返す
Private Function GetCoef(TL As Trendline) As Variant
 Dim v, u
 Dim j As Long, k As Long
 Dim ss As String
 Dim disp As Boolean
 
 With TL
  disp = .DisplayEquation
  .DisplayEquation = True
  .DataLabel.NumberFormatLocal = "0.0000000E+00"
  ss = .DataLabel.Text
  .DisplayEquation = disp
 End With
 j = InStr(ss, "R2")
 If j Then ss = Left$(ss, j - 1)
 j = InStr(ss, "=")
 ss = Mid$(ss, j + 2)
 ss = Replace(ss, "+ ", "+")
 ss = Replace(ss, "- ", "-")
 Debug.Print ss
 
 Select Case TL.Type
  Case xlLinear, xlPolynomial
    v = Split(ss)
    If TL.Type = xlLinear Then k = 1 Else k = TL.Order
    ReDim u(k) As Double
    For j = 0 To k
      u(j) = Val(v(j))
    Next
    
  Case xlExponential
    ReDim u(1) As Double
    v = Split(ss, "e")
    u(0) = Val(v(0))
    u(1) = Val(v(1))
  
  Case xlPower
    ReDim u(1) As Double
    v = Split(ss, "x")
    u(0) = Val(v(0))
    u(1) = Val(v(1))
  
  Case Else
    MsgBox "Can't get Coeff from Series because of " & TL.Type
    GetCoef = False
    Exit Function
 End Select
 
 GetCoef = u
End Function

【61654】Re:近似曲線のテキストデータが読み取れ...
発言  kanabun  - 09/5/24(日) 23:01 -

引用なし
パスワード
   補足です。

>多項式近似を行うのにブラフの近似曲線に付いているデータラベルから求めるなんて根本的によろしくないと思います。
>データラベルでは真の値は得られず桁落ちの可能性もあります。
>計算して求めるのがいいと思います。
>シートを用いるなら、
>ht tp://support.microsoft.com/kb/402428/ja

そんなことはないと思いますよ?

>ht tp://support.microsoft.com/kb/402428/ja
には、サンプルデータがのってますが、
途中にあるLINEST関数で求めた計算結果↓
>LINEST(B2:B8,C2:H8)
>  = 5.750001, -130.75, 1193.75, -5442.92,12500.5, -11716.3, 7790.001
は、まったく根拠のないもので、
実際はその下に再度書いてあるように、
> =LINEST(B2:B8,C2:H8)
で求めた結果は、
5.902777778  -133.9583333  1220.486111  -5555.208333  12748.61111
-11985.83333 7900
で、グラフのトレンドラインから求めたもの(指数表示)と有意な差はありません。

5.9027778   -133.95833     1220.4861   -5555.2083  12748.611
-11985.833  7900

グラフのトレンドラインでも 内部では LINESTを使って同様の計算をしている
と考えらます。

【61655】Re:近似曲線のテキストデータが読み取れ...
お礼  yuji  - 09/5/24(日) 23:28 -

引用なし
パスワード
   yoshiさん
kanabunさん

このたびはお教えいただきましてありがとうございます。
おふた方にお教えいただいた方法は両方とも理解することができたと思います。

実装はまだ行っておりませんが、おそらく問題なく対応できると思います。
まずはお礼を述べさせていただきます。ありがとうございました。

3523 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free