Excel VBA、fomulaを使って範囲が変わる近似曲線の係数を求める!

管理人の日記

VBAでセルに式を入力するにはfomulaプロパティを使います。

セルに式を入れるなら、

「普通にExcel上で式を書けば良いのでは?」
「fomulaって誰得?」

というのが、正直な感想でした。

じゃあなぜ使うのかと言ったら、参照するセルがその時々で違うときに活躍してくれるからです。

でも、この子、変数を入れるのめちゃくちゃ、めんどくさい。

というより、分かりづらい。

同じ悩みを持つ方のため、一肌脱いでみる。(自分のメモにもなるし)

今回やりたい事

実験で取得した値から近似曲線を取得する(データ取るだけのため割愛)

範囲が変わる、近似曲線の係数を求める。(指数関数)

これをfomulaプロパティを使って実現する。

VBAで指数関数の係数を求める

y=a ebの係数aとbを求めます。

最初は計算式で係数を求めようとしましたが、無理じゃーとなって挫折。

エクセルの関数を使えば、簡単に係数が求まるというベストアンサーを見かけたので、まるっとコピペしたら本当に係数が算出できた。

その式がこちら。

=EXP(INTERCEPT(LN($B$38:$B$43), $A$38:$A$43))
=SLOPE(LN($B$38:$B$43), $A$38:$A$43)

$$マークのところは自分の求めたいセルの値で良いですよ。

じゃあ、セルにこの式書けば良いんですけど、
私が求めているのは、セルの参照範囲が変わっても対応できるモノ。
参照範囲を可変にする、すなわち、変数が必要です。
では、VBAで書きましょう、そうしましょう。

で、書いたのがこちら。

最初は、セルの値が入ればいいんだろと、RangeとCellsで指定していますが、勿論エラーではじかれます。

そりゃ、シート関数にこんな関数はありませんからね。

ましてや、セルの値を求めようとしてますね。

それに気づかず、なんでできないのかと悩んで三日。

'入力された値から行数を取得
    i = Range("N4").Value
    p = Range(Cells(i + 25, 2), Cells(i + 30, 2))
    q = Range(Cells(i + 25, 1), Cells(i + 30, 1))

    Range("Z11").Formula = "=EXP(INTERCEPT(LN("p"), "q"))"  '係数a
    Range("AA11").Formula = "=SLOPE(LN("p"), "q")"          '係数b

ふとお昼寝から目が覚めて、セルのアドレスが欲しいということに気が付きました。

ということで、セルのアドレスを求めて、変数に代入、確認のために書き出してみると無事、アドレスがゲットで来ている。

よっしゃーい!

で、あとはこいつをformulaの中に入れれば完成。

なんですが、変数が文字列としてそのまま、セルに書き出されています。

なんてこったい、Googleせんせーい!

と助けを求めて、formulaでは変数を&で囲むということが分かりました。

完成したプログラムがこちら。

'入力された値から行数を取得
    i = Range("N4").Value
    p = Range(Cells(i + 25, 2), Cells(i + 30, 2)).Address
    q = Range(Cells(i + 25, 1), Cells(i + 30, 1)).Address

    Range("Z11").Formula = "=EXP(INTERCEPT(LN(" & p & "), " & q & "))"  '係数a
    Range("AA11").Formula = "=SLOPE(LN(" & p & "), " & q & ")"          '係数b

+25とかは私の作った表に対応させているだけですので、あまり意味はありません。

おそらく、もっと簡単にできるのでしょうが、初心者にはこれが限界。
答えは出てるし、いいよね。

指数近似の係数を求める(範囲が可変)でマクロを組みたい方の助けになりましたかね。
めっちゃ、ピンポイントなマクロ作成でした。

間違っていたらごめんね!

コメント

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