Excelソルバー

Support Vector Regressionをエクセルのソルバーで作ってみた

Excelソルバー

[PR]当サイトはアフィリエイト広告を利用しています。

以前作物の高さを数量化1類で回帰分析してみましたが、今回はSupport Vector Regression(サポートベクトル回帰)で回帰分析してみようというお話です。

Support Vector Regressionはscikit-learnで簡単に実装できるのですが、中身はどうなっているのか調べてみたところ、ある種の最適化問題を解いて回帰しているみたいなので、これ、ソルバーで解けるんじゃ?と思って作ってみました。

初心者がフィーリングで作っているので間違いもあるかもしれません。

当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

まず対象とする表は以下。

液肥2週に一回液肥週1日照長い作物高さ
00051
00052
10050
10051
01051
01049
00160
00161
10161
10162
01160
01161

作物の高さとその条件が書かれている表です。

これを回帰します。

参考にしたのは以下。

(1)データ化学工学研究室(金子研究室)@明治大学 理工学部 応用化学科. “サポートベクター回帰(Support Vector Regression, SVR)~サンプル数10000以下ならこれを使うべし!~”. 2019/4/20. (参照2021/08/23)

(2)竹内一郎 烏山昌幸(2015)『機械学習プロフェッショナルシリーズ サポートベクトルマシン』講談社.

とにかく色々すっ飛ばして結論から。

最終的な回帰したf(x)は

このaとa*とcを求めるのが問題となります。

またカーネルにRBFカーネルを用いて

で計算されます。

aとa*の求め方は

の条件のもとaiとai*について最大化すると求まります。

またεとCはとσは最初に与えるパラメータ、nはデータ数、yiはデータの関数値となっています。

これをエクセルで求めるのですが、ところどころユーザー定義関数を使って省力化します。

まずカーネルを計算する必要があるのでその関数。

argがxi、arg2がxj、sigがσ。

Function Func1(arg As Range, arg2 As Range, sig As Double)
    Dim re As Double
    Dim xn As Integer
    Dim i, j As Long
    Dim v As Variant
    Dim v2 As Variant
    Dim n1 As Long
    Dim sa(3) As Double
    v = arg.Value
    v2 = arg2.Value
    xn = 3
    re = 0
    For i = 1 To xn
        sa(i) = v(i, 1) - v2(i, 1)
    Next i
    For i = 1 To xn
        re = re + sa(i) * sa(i)
    Next i
    re = Exp(-(re) / (2 * sig * sig))
    Func1 = re
End Function

カーネルをエクセルで計算していきます。

B14=Func1($B$9:$B$11,B$9:B$11,$O$8)

C14=Func1($B$9:$B$11,C$9:C$11,$O$8)

横だけはオートフィルでなんとかなりますが、縦は地道にB列だけ打ち込む必要があります。O8は次の画像を見てください。O8が映っています。

B14がi=1、j=1、C14がi=1、j=2となっています。

aの値を入力するフィールドを作ります。

B2がa1、C2がa1*です。

B4がa1-a1*です。

この図は最適化後の値なので、最初はとりあえず0を入力しておきます。

7列目から11列目は作物の表を横にしたものです。

次は最適化する目的関数の計算ですがこれもユーザー定義関数を使用します。

‘vはa、v2はK、v3はy。

Function Func2(arg As Range, arg2 As Range, arg3 As Range, e As Double)
    Dim re As Double
    Dim n As Integer
    Dim i, j As Long
    Dim v As Variant
    Dim v2 As Variant
    Dim v3 As Variant
    Dim n1 As Long
    v = arg.Value
    v2 = arg2.Value
    v3 = arg3.Value
    n = 12
    re = 0
   
    For i = 1 To n
        For j = 1 To n
            re = re + (v(1, i * 2 - 1) - v(1, i * 2)) * (v(1, j * 2 - 1) - v(1, j * 2)) * v2(i, j)
        Next j
    Next i
    re = -re / 2
    For i = 1 To n
        re = re + (v(1, i * 2 - 1) - v(1, i * 2)) * v3(1, i) - (v(1, i * 2 - 1) + v(1, i * 2)) * e
    Next i
    Func2 = re
End Function

O13=Func2(B2:Y2,B14:M25,B8:M8,O10)

sigがσ、eがε、CはそのままCです。

またO5はB4からM4までの和です。

ここでソルバーを使います。

今回は不連続ではないのでGRG非線形を選択します。

解決ボタンを押すとaとa*が求まります。

ここでeとCとsigの値次第で結果が変わります。

色々試した結果

sigは2、eは0.1、Cは40となりました。

Cではなく、回帰式のcを求めます。

これまで出てきた図でbとなっているところです。

c=bと読み替えてください。

cはaまたはa*が0<a,a*<Cのとき存在して、

aについては

a*については

で求めます。

今回は1,4,5,9,11のサンプル点が該当します。

それがQ5からQ9までの値となっています。

これも省力化のためユーザー定義関数を作ります。

‘argはa、arg2はK、arg3はy。

Function Funcb(arg As Range, arg2 As Range, y As Double, e As Double)
   
    Dim re As Double
    Dim n As Integer
    Dim i, j As Long
    Dim v As Variant
    Dim v2 As Variant
    Dim v3 As Variant
    Dim n1 As Long
    v = arg.Value
    v2 = arg2.Value
    re = 0
    n = 12
    For i = 1 To n
        re = re + (v(1, i * 2 - 1) - v(1, i * 2)) * v2(i, 1)
    Next i
    Funcb = re + y - e
End Function
Function Funcb2m(arg As Range, arg2 As Range, y As Double, e As Double)
   
    Dim re As Double
    Dim n As Integer
    Dim i, j As Long
    Dim v As Variant
    Dim v2 As Variant
    Dim v3 As Variant
    Dim n1 As Long
    v = arg.Value
    v2 = arg2.Value
    re = 0
    n = 12
    For i = 1 To n
        re = re + (v(1, i * 2 - 1) - v(1, i * 2)) * v2(i, 1)
    Next i
    Funcb2m = re + y + e
End Function

Q5 =Funcb2m(B2:Y2,B14:B25,B8,O10)

そしてこの平均を取ります。

Q11 =AVERAGE(Q5:Q9)

これがcとなります。ここは平均でいいのか少し理解していないのですがとりあえず平均にしたら結果がうまくいったのでたぶん合ってると思います。

最後に回帰した値を求めるユーザー定義関数。

‘argはa、arg2はK。

Function Funcf(arg As Range, arg2 As Range, b As Double)
    
    Dim re As Double
    Dim n As Integer
    Dim i, j As Long
    Dim v As Variant
    Dim v2 As Variant
    v = arg.Value
    v2 = arg2.Value
    re = 0
    n = 12
    For i = 1 To n
        re = re + (v(1, i * 2 - 1) - v(1, i * 2)) * v2(i, 1)
    Next i
    Funcf = re + b
End Function

S13 =Funcf($B$2:$Y$2,B$14:B$25,$Q$11)

まあまあよく回帰できていると思います。

今回はSupport Vector Regressionをソルバーで求めるということをやってみました。

scikit-learnがあるのでわざわざ自分で作る意味はあまりないかもしれませんが、Support Vector Regressionにもいろいろなバリエーションがあるのでそれを手作業で実装するなんてときはこういう処理が必要なんでしょうね。

とりあえずそれなりのものが作れたのでよかったです。

当ブログ(シルルスのコードおきば)ではエクセルソルバー関係の記事を他にも執筆しています。参考になりましたら幸いです。

●エクセルソルバーで複数解を求める方法【初期値を変えるしかないです】

●エクセルソルバーと組み合わせ最適化問題【解法例と基礎知識】

●エクセルソルバーとは【使い方と数理最適化の基礎】

●ソルバーで高校の化学平衡の問題を解いてみる

●輪作作物をソルバーで決定

●Calcのソルバーでどこまでできるか実験

●巡回セールスマン問題をソルバーで解く

●移動販売をどこに出店するかソルバーで求める

●ソルバーで正規分布の90%範囲を求める

●ソルバーとユーザー定義関数の連携