みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excel VBA TREND関数について

はじめまして、以前に教えて!gooに同じ質問が掲載されており、参考にさせて頂いたのですが
演算結果に違いが出てしまい原因がわかりません。どなたかアドバイスいただけませんか?

私の環境
winXP sp3
office2000

----------------------------------------------------------------------------------
以前の回答(1)

Sub Trend_Test()
  Dim y(10) As Double '既知のy
  Dim x(10) As Double '既知のx
  Dim newX As Double '新しいx

  '配列y()、x()に値を代入
  y(1) = 100: x(1) = 1
  y(2) = 200: x(2) = 2
  y(3) = 300: x(3) = 3
  y(4) = 400: x(4) = 4
  y(5) = 500: x(5) = 5
  y(6) = 600: x(6) = 6
  y(7) = 700: x(7) = 7
  y(8) = 800: x(8) = 8
  y(9) = 900.1: x(9) = 9

  '新しいx(例)
  newX = 5.5

  '試しに計算結果を出力
  Range("A1") = Application.Trend(y, x, newX, True)
  Range("A2") = Application.Trend(y, x, newX + 1, True)
  Range("A3") = Application.Trend(y, x, newX + 2, True)

End Sub

----------------------------------------------------------------------------------
以前の回答(2)

Sub TEST_Trend()
Dim x As Variant
Dim y As Variant
Dim NEWx As Double
x = Array(0.005479452, 0.019178082, 0.038356164, 0.082191781, 0.167123288 _
, 0.252054795, 0.328767123, 0.41369863, 0.495890411, 0.580821918, 0.663013699 _
, 0.747945205, 0.832876712, 0.915068493, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 12, 15, 20)
y = Array(0.055, 0.055, 0.057, 0.064, 0.086, 0.086, 0.087, 0.096 _
, 0.099, 0.1, 0.1, 0.1, 0.01, 0.103, 0.106, 0.15, 0.233 _
, 0.347, 0.483, 0.64, 0.817, 0.995, 1.163, 1.31, 1.545, 1.797, 2.07)
NEWx = 0.05

Dim i As Integer
For i = 1 To 20
Cells(i, 1) = Application.Trend(y, x, NEWx + i - 1, True)
Next i

End Sub

----------------------------------------------------------------------------------
今回の検証

Dim y(6) As Double
Dim x(6) As Double
Dim newX As Double
Dim YY As Variant
Dim XX As Variant

newX=7

y(1) = 92.87: x(1) = 1
y(2) = 92.55: x(2) = 2
y(3) = 91.64: x(3) = 3
y(4) = 92.3: x(4) = 4
y(5) = 93.29: x(5) = 5
y(6) = 92.59: x(6) = 6
Range("A1") = Application.trend(y, x, newX, True)
Range("A1") には"119.085714285714"が出力されます

XX = Array(1, 2, 3, 4, 5, 6)
YY = Array(92.87, 92.55, 91.64, 92.3, 93.29, 92.59)
または
XX = Array(x(1), x(2), x(3), x(4), x(5), x(6))
YY = Array(y(1), y(2), y(3), y(4), y(5), y(6))
Range("B1") = Application.trend(YY, XX, newX, True)
Range("B1") には"92.688"が出力されます

ワークシート上でtrend関数を実行すると
"92.688"を返しますので
Arrayを使用した値と同じです

ここで問題がありまして
Do...Loop等でx(1)~x(6), y(1)~y(6)の値を取り出しています

取り出した配列(値は固定ではないのです)をどうのように
Array(1, 2, 3, 4, 5, 6)
または
Array(x(1), x(2), x(3), x(4), x(5), x(6))
等の形にしたら良いか分かりません

たとえば
Dim a as String
Dim b as String
a="1, 2, 3, 4, 5, 6"
b = "92.87, 92.55, 91.64, 92.3, 93.29, 92.59"
XX = Array(a)
YY = Array(b)
また、
Dim a As Variant
Dim b As Variant
a="1, 2, 3, 4, 5, 6"
b = "92.87, 92.55, 91.64, 92.3, 93.29, 92.59"
XX = Array(a)
YY = Array(b)

Range("D1") = Application.trend(YY, XX, newX, True)
演算結果は"#VALUE!"を返します

どうのようにしたらよいのでしょうか?

投稿日時 - 2011-05-26 16:21:20

QNo.6764989

困ってます

質問者が選んだベストアンサー

>Range("A1") には"119.085714285714"が出力されます

しょっぱなの
Dim y(6) As Double
Dim x(6) As Double
により,配列がゼロから宣言されているため,
x:{0,1,2,3,4,5,6}
y:{0, 92.87, 92.55, …, 92.59}
の7要素の配列からTREND関数を計算しています。

Option Base 1
を宣言して配列を1から開始するようにするか,若しくは
Dim y(1 To 6) As Double
Dim x(1 To 6) As Double
に直して実行します。


#参考
Excel2002以前のバージョンのエクセルでは,TREND関数の内部的な計算の元になっているLINEST関数にバグがあるため,可能な限りExcel2003以降をご利用ください。

投稿日時 - 2011-05-26 16:51:02

お礼

soul_surferです
早速のアドバイス、ありがとうございます
教えて頂いた通りに変更し、実行しましたら希望の演算結果が得られました。
また、Excel2003以降に変更したいと思います
ありがとうございました

投稿日時 - 2011-05-26 17:05:29

ANo.1

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

この質問は長いが、質問の要点を箇条書きにでもして表現を工夫してほしい。
すでにわかる人がいて、回答も出ているが、それに甘えないで。
2つろんてんが書いてあるようだ
(1)2つのプログラムの結果が合わない
(2)その他
>取り出した配列(値は固定ではないのです)をどうのようにArray(1, 2, 3, 4, 5, 6)またはArray(x(1), x(2), x(3), x(4), x(5), x(6))等の形にしたら良いか分かりません
プログラムの実行中の結果はあえてArray(・・・)
にしなくてもよいのではないかな。普通の配列にためて行けばよい。
そういう方法(Arrayにする形)があれば、私も教わりたい。
エクセルの場合には
Sub test01()
Range("a1:D1") = Array(1, 2, 3, 4)
End Sub
のようなのができて、1行ですむ。
しかし
Sub test02()
Dim h(4)
h(0) = 0: h(1) = 1: h(2) = 2: h(3) = 3
Range("a1:D1") = h()
End Sub
のようなのもできるようだ。
ーー
Sub test03()
a = "0,1,2,3"
Range("a1:D1") = Array(a)
End Sub
は結果が違う。
Sub test04()
Range("a1:D1") = 1
End Sub
と同じタイプになる。
ーー
これらを認識してコードを考えてみたら。
また配列の加減が0であることを注意すべにです。
Option Base 1を宣言しないと0どぇす。
ーー
Application.Trendはうまくいきましたか。
Application.Worksheetfunction.Trend
.Worksheetfunction.Trend
式でやってきたが。

投稿日時 - 2011-05-26 18:24:09

お礼

soul surferです
ご回答ありがとうございます
ご連絡が遅くなりました

>この質問は長いが、質問の要点を箇条書きにでもして表現を工夫してほしい。
>すでにわかる人がいて、回答も出ているが、それに甘えないで。

たしかにそうでした、どこまで記述すれば、アドバイスして頂く方に理解してもらえるのか
分からず長いコードを記入してしまいました。
すみません。以後、気を付けます。

>Range("a1:D1") = Array(1, 2, 3, 4)

上記のArray等にこだわったのは一度エクセルで演算結果を分析してみて、その後、
サーブレット+SQL等に考え方を反映させたかったのでRange("a1:D1")等のエクセル固有のコード
はなるべく避けたかったのです。とは言え、そもそもtrend関数なるものが現時点のSQLに用意せれているかどうかは、まだ不明ですが。。。

今回の演算結果の差異は私の配列の宣言方法のミスであることが、
皆様のご指摘により判明しましたのでArrayにこだわらずにすみました。ありがとうございます

また、ご指摘のtest01()からtest04()の記述は今後の考え方に大変参考になりました。
ありがとうございます

今後とも、ご指導の程、宜しくお願いします

投稿日時 - 2011-05-27 11:05:44

あなたにオススメの質問