【エクセルVBA】データから2次の多項近似式を取得する方法!

こんにちは、ヒガシです。

 

このページにたどり着いたということは、エクセル上でデータから2次の近似式を導出する際に、

①データから散布図グラフを作成

②近似曲線の追加

③多項式近似を選択

④グラフに数式を表示

といった面倒な作業を効率化したいと思っているのだと思います。

 

というわけでこのページでは、以下の画像に示しているように、指定したデータから2次の多項近似式を取得する方法をご紹介していきます。

2次の多項近似式を自動算出するという作業概要図

単純に式を取り出すだけでなく、その式内の3つの係数もそれぞれ取得する方法をご紹介していきます。

 

それではさっそくやっていきましょう!

 

※3次曲線の近似式を出す方法は以下から!

【エクセルVBA】データから3次の多項近似式を導出する方法!

 

スポンサーリンク

2次曲線の近似式を取得する流れ

実際の作業に入る前にまずはどのような作業で式を取り出すかを解説しておきます。

 

①データが存在する領域を指定する

②①の領域を参照した散布図を作成する

③②のグラフに2次の近似式を追加する

④③で作った数式を文字列として取り出す

⇒これで数式全体としては取得完了

⑤④の文字列を分解して各係数を取り出す

 

まさに冒頭で紹介した人間が実施している作業そのものですね。笑

 

今回紹介する手法では、①の領域指定さえしてしまえば残りの作業は全部自動でやってくれるようになっています。

 

スポンサーリンク

2次の多項近似式を取得するサンプルコード

それでは先ほど紹介した内容を実施してくれるサンプルコードをご紹介していきます。

Sub secondary_formula()
    '変数の型を宣言
    Dim x_ax As String, y_ax As String
    Dim grf_formula As String
    Dim a As Single, b As Single, c As Single
    'データ範囲を指定する(やるのはここだけ)
    x_ax = "A2:A11"
    y_ax = "B2:B11"
    'グラフ(散布図)を挿入する
    ActiveSheet.Shapes.AddChart2(-1, -4169).Select
    With ActiveChart
        '指定したデータでグラフ作成
        .Parent.Name = "sample_grf"
        .SeriesCollection.NewSeries
        With .FullSeriesCollection(1)
            .XValues = ActiveSheet.Name & "!" & x_ax
            .Values = ActiveSheet.Name & "!" & y_ax
        End With
    End With
    'グラフに近似曲線を追加する
    With ActiveChart.SeriesCollection(1)
        .Trendlines.Add
        .Trendlines(1).Select
    End With
    '近似曲線の種類を指定する
    With Selection
        .Type = xlPolynomial
        .Order = 2
    End With
    Selection.DisplayEquation = True
    '近似曲線の式の桁数を指定する
    ActiveSheet.ChartObjects("sample_grf").Activate
    With ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel
        .NumberFormat = "#,##0.000000_ "
    End With
    'グラフの近似式をリフレッシュ
    ActiveSheet.ChartObjects("sample_grf").Activate
    ActiveChart.Refresh
    'グラフの近似式を取得する
    With ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel
        grf_formula = .Text
    End With
    ActiveSheet.ChartObjects("sample_grf").Delete
    split_formula = Split(grf_formula, " ")
    a = split_formula(2)
    b = split_formula(4) & split_formula(5)
    c = split_formula(7) & split_formula(8)
    '数式&係数をセルに書き出す
    Cells(1, 4) = grf_formula
    Cells(2, 4) = "a="
    Cells(2, 5) = a
    Cells(3, 4) = "b="
    Cells(3, 5) = b
    Cells(4, 4) = "c="
    Cells(4, 5) = c
End Sub

 

あなたが指定するべきところは7,8行目のグラフ作成の際のX, Yの領域だけです。

あとは全自動で多項近似式がセル上に出力されます。

※出力する場所はあなたの状況にあわせて適宜変更してください。

 

スポンサーリンク

サンプルコードの実行結果確認

それでは先ほどのコードを実行してみましょう。

 

実行する前はこのような状態です。

ただデータが存在しているだけですね。

サンプルコード実行前のシート状態

 

それでは実行してみます。

サンプルコード実行後のシート状態

問題なく結果が出力されていますね。

 

念のためグラフに近似式を追加して確認してみましょう。

サンプルコード実行結果とグラフ上に表示した結果を比較した結果

完璧に数値が一致していますね!

 

スポンサーリンク

おわりに

というわけで今回は、エクセルVBAをつかって2次の多項近似式の式、係数を一発算出する方法をご紹介しました。

データ分析の際にぜひご活用ください。

 

このように、私のブログではエクセルスキルはもちろん、様々なプログラミングスキルを紹介しています。

 

今は仕事中で時間がないかもしれませんが、ぜひ通勤時間中などに他の記事も読んでいただけると嬉しいです。

⇒興味をもった方は【ヒガサラ】で検索してみてください。

確実にスキルアップできるはずです。

 

最後に、この記事が役に立ったという方は、ぜひ応援よろしくお願いします。

↓ 応援ボタン

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

それではまた!

コメント

タイトルとURLをコピーしました