rubyによるExcel操縦あれこれ 〜 ソルバーの利用
rubyによるExcel操縦あれこれ 〜 ソルバーの利用
最終更新日： 2012/03/25
Excelのソルバーの利用について記します。計算式の最適解を求める(逆算する)ものです。いわばゴールシークの高機能版です。
ソルバーは、Excelに標準装備されている機能ではなく、アドインとして提供されています。なので、ソルバーの利用は、アドインの利用方法の一例になっています。
solver.zip には、以下で掲げるサンプルスクリプトのほか、exlap.rbも含まれています。別途ダウンロードする必要はありません。
以降で掲げるサンプルスクリプトを私が実行した環境は次のとおり。
- MS-Windows xp | vista
- Excel2002(Office xp版) | Excel2007 (Excel2010は少しだけ試す)
- ruby ver 1.8.7
- 使用ライブラリ: 拙作exlap.rb v1.1
なお、rubyがインストールされていない環境でサンプルスクリプトを実行したい方は、exl.exeをお試し下さい。詳しくは exl.exeの使い方 を参照して下さい。
--------
＜はじめに＞
Excelのソルバー機能は、複数の変数を含む計算式が、ある値を取る場合について、各変数の値を分析・推測するものです。
ゴールシークが単一の変数しか扱わないのに対し、ソルバーは複数の変数を扱うことができます。もちろん単一の変数にも適用できるので、ゴールシークと同じことも実現できます。
例えば、「x*200+y*300=2600」 「x+y=11」という連立方程式の解を求めることができます。ちなみに解は x=7, y=4。
ソルバーが本格的に利用されるのは、科学技術計算の分野だと思います。また、ソルバーの背後で動いているアルゴリズムを理解してないと、ソルバーの細かなオプション設定ができません。
ここでは、そうした本格的な利用には踏み込みません。ごく基本的な利用方法を取り上げるだけですのでご了承ください。
--------
1. とりあえずソルバーを実行してみる
(1) ソルバーを利用するための準備
a. アドインとして組込む： Excelでソルバーを利用するためには、まず、ExcelをGUI操作してアドインの中のソルバーを「有効」にする必要があります。そのやり方は、Excelのバージョンによって違います。申し訳ありませんが、ここでは説明しません。他のWebなどを参考にして行って下さい。
当然ながら、既にソルバーが使える状態になっているのであれば、改めてアドインに組み込む作業は必要ありません。
b. VBEでの参照設定： Excelのマクロを自作したりする時に起動するエディタVBEを立ち上げて、メニューの［ツール］−［参照設定］で solver.xla または solver.xlam(以下「xla|xlam」と標記)を設定します。
Excel2003までなら solver.xla、Excel2007以降なら solver.xlam です。
この作業は、以降で掲げるスクリプトを実行する上で必要ないかもしれません。私のパソコンは、２台とも参照設定してしまっているので、設定してない時にrubyスクリプトがちゃんと動作するかどうか確認していません。
上のa.とb.の準備ができたら、xla|xlamのフルパス名を確認しておきます。
私のところでは次のようなフルパス名です。
- Excel2007: C:\Program Files\Microsoft Office\Office12\Library\SOLVER\SOLVER.XLAM
- Excel2002: C:\Program Files\Microsoft Office\Office10\Library\Solver\SOLVER.XLA
なお、圧縮ファイルに同梱してある addin.rb を実行すると、フルパス名が標準出力に出力されます。
ソルバー以外のアドインについても表示されます。
VBA風に書くと Application.AddIns を用いるのが addin.rb の要点です。
AddInsの詳細には触れませんが、アドインを扱うためのメソッドがいくつかあります。興味がありましたら調べてみて下さい。
xla|xlamのフルパス名は、サンプルの slv01.rb で使うので、メモしておくといいかもしれません。
(2) ソルバー利用の簡単なサンプル
変数が一つだけのケース、平方根を求める場合を取り上げます。
「y=x*x」の式で、yが2のとき、xがどんな値になるかを調べます。
A1欄に計算式 "=B1*B1" をセットして、それが2になるとの条件を設定し、ソルバーを実行します。すると、B1欄に2の平方根(1.4142……)がセットされます。
アドインとして提供される関数は、Application.Run(……) で呼び出すことができます。
ソルバーでよく用いる関数は次のものです。
- SolverOk: 目標条件を設定する。
- SolverSolve: ソルバーを実行する。
- SolverFinish: 終了処理。求めた解を該当セル(B1欄)に書き込む。
細かなことはともかくとして、とりあえずスクリプトを掲げます。
下の slv01.rb は、Excel2007 以外では正しく動作しません。他のExcelの場合は、変数 solver_file に代入する値を変更する必要があるので注意して下さい。
−−−− slv01.rb ここから
#! ruby -Ks
require "exlap"
solver_file = 'C:\Program Files\Microsoft Office' +
'\Office12\Library\SOLVER\SOLVER.XLAM' # for Excel2007(VISTA)
unless test(?e, solver_file)
STDERR.puts "変数solver_fileの値が不適当です."
exit
end
prefix = "'#{solver_file}'!"
filename = "slv01.xls"
Exlap.new(filename) do |wb|
app = wb.app.obj # Excel Application
app.Run "#{prefix}Auto_Open" if app.Version.to_f < 12.0
ss = wb.fes # 空のワークシートを選択
ss.Activate
ss.Range("A1").Formula = "=B1*B1"
val = 2 # 2の平方根を求める
app.Run("#{prefix}SolverOk", "A1", 3, val, "B1")
app.Run("#{prefix}SolverSolve", true)
app.Run("#{prefix}SolverFinish", 1)
wb.save
end
−−−− slv01.rb ここまで
変数 solver_file には xla|xlam のフルパス名を代入します。
面倒なことに、Application.Run を実行する時は常に、このフルパス名を指定します。
毎回これを記述するのは大変なので、このフルパス名を含む「前置きの文字列」を変数 prefix にセットしておきます。
「app = wb.app.obj」で、VBAでいうところの Application を変数 app にセットします。
「app.Run "#{prefix}Auto_Open"」は、Excel2007では不要ですが、Excel2002では、これがないとソルバーがうまく動きません。ちなみに、Excel2010では Auto_Open があるとエラーになるようです。
「app.Run("#{prefix}SolverOk", "A1", 3, val, "B1")」は、ソルバーの目標条件を設定するSolverOk()を呼び出すものです。
Runメソッドでは、その第１引数として、関数名 SolverOk をフルパス名付きの文字列で渡します。
このSolverOkの設定は、A1欄の計算式が、val(つまり2)になるような、B1欄の値を探索しなさい、という命令です。
３番目の引数の 3 は、２番目の引数 "A1" と４番目の引数 val とが等しくなるように、との指定です。いわば統合またはニアリーイコールの記号に相当します。この３番目の引数として、1:最大、2:最小、3:指定値、のどれかを指定できます。今回は「3:指定値」にしたわけです。
最後の引数 "B1" は、Excelの用語でいう「変化させるセル」です。計算式の中に出てくる変数に該当します。
セルの指定は、"A1" などでなく ss.Range("A1") のようにセルオブジェクトそのものを渡すこともできます。ただし、"R1C1" のようなRC形式を書くと、うまくいかないようです。
なお、SolverOk() をVBAで呼び出す時は、次のように書きます。２通りを示します。
SolverOk(SetCell:="A1", MaxMinVal:=3, ValueOf:=2, ByChange:="B1")
SolverOk("A1", 3, 2, "B1")
前者は、本来ならrubyにおいてHashを用いて書くことのできるパターンです。Excelに標準装備されている GoalSeek() などでは引数にHashを使うことができます。
しかし、アドインを実行するための Runメソッドの場合、Hash記法を使えないようです。もしかすると何か方法があるのかもしれませんが、私には分かりませんでした。
というわけで、第２の記法、順番に従って引数を並べる方法を採ります。
引数の個数が少ない時はこれで問題ありませんが、SolverOptions() では引数の個数が10個以上になります。これだと大変です。
先に書いたように、逐一 prefix を置かなければならないのも大変だし、他にもいろいろ不便な点があるので、後で、ソルバーを扱うためのrubyクラスを設けて、それを経由してソルバーを実行するようにします。
SolverOkの後は、SolverSolve(ソルバーの実行)、SolverFinish(終了処理、求めた解のセルへの書き込み)を実行しています。それぞれの引数に関する説明は省略しますが、ExcelをGUI操作するのでなく rubyスクリプトで一括処理する時は、このパターンで使うことが多いと思います。余計ななダイアログボックスを出さずに終了させるためのパターンです。
(3) ソルバー利用の簡単なサンプルその２
念のため、簡単なサンプルをもう一つ掲げておきます。
2〜10の平方根を出力するものです。ソルバーを９回実行します。
slv01.rbでは、xla|xlamのフルパス名を予めスクリプト内に書き込んでおく必要がありましたが、今回は Application.AddIns を用いて探索するようにしてあります。なので、フルパス名を意識する必要はありません。
−−−− slv02.rb ここから
#! ruby -Ks
require "exlap"
filename = "slv02.xls"
Exlap.new(filename) do |wb|
app = wb.app.obj # Excel Application
# SOLVER.XLA | SOLVER.XLAM のフルパスを探索
solver_file = nil
app.AddIns.each do |target|
if target.Name =~ /^solver\.x/i
solver_file = target.FullName
break
end
end
##
prefix = "'#{solver_file}'!"
app.Run "#{prefix}Auto_Open" if app.Version.to_f < 12.0
ss = wb.fes # 空のワークシートを選択
ss.Activate
# 2〜10の平方根を求める作業
y = 0
for val in 2..10
y += 1 # 行番号
ss.cell(y,1).Formula = "=RC[1]*RC[1]" # 「RC[1]」は右隣のセル
app.Run("#{prefix}SolverOk", ss.cell(y,1), 3, val, ss.cell(y,2))
app.Run("#{prefix}SolverSolve", true)
app.Run("#{prefix}SolverFinish", 1)
end
wb.save
end
−−−− slv02.rb ここまで
上のスクリプトについて、特に説明を加える必要はないと思いますが、SolverOk()に渡す引数で、セルの指定を "A1" などの文字列でなく、ss.cell(y,1) のようにセルオブジェクトそのものを渡しています。
それ以外は、slv01.rb と基本的に同じです。
--------
2. ソルバーを利用するためのrubyクラス
ソルバーで複数の変数を扱う前に、ソルバー用のrubyクラスを作ってみたので紹介します。
複数の変数を扱う場合は、一般に、SolverOkで目標条件を設定するだけでは不十分です。
xとyの二つを扱う場合、少なくとも計算式が二つないと、解を求めることができません。あるいは、式とまではいわないまでも、x>=4, y<=10 などのいろいろな条件を加える必要が出てきたりします。
こうした付随条件の設定は、SolverAdd で行います。実用的にソルバーを利用する場合、この SolverAdd をかなり書かなければならなくなることがあります。
更に、ソルバーのオプションの設定は、Application.Runメソッドだけでは困難です。
そこで、ソルバーを扱うためのrubyクラスを作ってみました。SolverOk, SolverAdd, SolverOptions といった関数を呼び出しやすくして、後始末的な SolverSolve, SolverFinish のよく用いるパターンを settle() で簡単に実行できるようにしました。
クラスの使い方については以降でおいおい触れますが、細かな仕様については、ソースコード本体の exlap.rb を参照して下さい(手抜きですみません)。
xla|xlamのフルパスは、特に指定がなければ探索するようにしてあります。
それから、Auto_Openの実行は、背後でこっそり行うようにしたので、意図して行う必要あありません(Excel2007以降では実行しないようにしてあります)。
このクラスは、同梱の exlap.rb の中で定義してあります。これをrequireすればクラスを使うことができます。
例えば、次のような記述が可能です。
slv = ExlSolver.new(……)
slv.ok("A1", 3, 2, "B1")
slv.settle()
とりあえず slv02.rb をクラス利用の形に書き換えたものを下に掲げます。
−−−− slv03.rb ここから
#! ruby -Ks
require "exlap"
filename = "slv03.xls"
Exlap.new(filename) do |wb|
slv = ExlSolver.new(wb.app.obj)
ss = wb.fes # 空のワークシートを選択
ss.Activate
# 2〜10の平方根を求める作業
y = 0
for val in 2..10
y += 1 # 行番号
ss.cell(y,1).Formula = "=RC[1]*RC[1]" # 「RC[1]」は右隣のセル
slv.ok(ss.cell(y,1), 'val', val, ss.cell(y,2))
slv.settle
end
wb.save
end
−−−− slv03.rb ここまで
「slv = ExlSolver.new(wb.app.obj)」がクラスExlSolverのオブジェクトを生成するための命令です。以後、変数slvを用いてソルバーを処理します。
この初期化で与える引数は、VBAでいうところのApplicationオブジェクトです。
第２引数として xla|xlamのフルパス名を与えることができますが、省略したり nil を与えると、フルパス名を探索して取得します。
「slv.ok(ss.cell(y,1), 'val', val, ss.cell(y,2))」の２番目の引数 'val' は、数値の 3 でも大丈夫です。ただ、数値が何を意味するのか覚えておくのは大変なので、文字列でも指定できるようにしました。
'max':1, 'min':2, 'val':3 という対応になっています。
後述のSolverAddを呼び出す場合、大小関係等を数値で指定しますが、これも分かりにくいので、次のような対応を設けて、文字列を用いることができるようにしてあります。
'<=':1, '=':2, '>=':3, 'int':4, 'bin':5, 'dif':6
これらの意味については後で触れます。
slv.ok(……) の引数は、次のようにHash形式にすることもできます。
slv.ok({'SetCell'=>ss.cell(y,1), 'MaxMinVal'=>'val',
'ValueOf'=>val, 'ByChange'=>ss.cell(y,2)})
--------
3. 複数の変数を扱うソルバーの例
(1) 連立１次方程式の解
複数の変数を扱う例として、連立１次方程式を解くことを試みます。
実は、連立１次方程式を解くことについては、行列処理で行う方が確実です。ソルバーの実用例として取り上げることには疑問を感じる方もいると思いますが、イメージしやすいので取り上げます。
お菓子ｘが１個200円、お菓子ｙが300円だとします。合計の購入金額が2600円程度になるようにしたいのですが、購入個数を２種類合わせて11個に決めたとします。
これを計算式の形で書くと、次のようになります。お菓子ｘの購入個数を変数x、お菓子ｙの個数を変数yとします。
x*200 + y*300 = 2600 x + y = 11
解は、x:7, y:4 です。
これをソルバーで扱ってみることにします。
A1欄を変数x、B1欄を変数yに見立てます。
「x*200+y*300」をC1欄にセットし、「x+y」をD1欄にセットします。
あとは、2600になるようにとか、11になるようにという条件を設定します。
以下にスクリプトを掲げます。
−−−− slv04.rb ここから
#! ruby -Ks
require "exlap"
filename = "slv04.xls"
Exlap.new(filename) do |wb|
slv = ExlSolver.new(wb.app.obj)
ss = wb.fes
ss.Activate
ss.Range("A1:B1").Value = [[1, 1]] # 初期値の設定(ダミー)
ss.Range("C1").Formula = "=A1*200+B1*300"
ss.Range("D1").Formula = "=A1+B1"
slv.ok("C1", 'val', 2600, "A1:B1") # x*200+y*300=2600の目標条件
slv.add("A1", 'int') # A1が整数であるとの制約条件
slv.add("B1", 'int') # B1が整数であるとの制約条件
slv.add("D1", '=', 11) # x+y=11の制約条件
slv.settle # ソルバーの実行と結果のセルへの書き込み
wb.save
end
−−−− slv04.rb ここまで
目標条件の設定「slv.ok("C1", 'val', 2600, "A1:B1")」で、最後の引数は、変化させるセルです。
これまでのサンプルでは、セル一つだけを指定していましたが、今回は A1, B1 の二つを変化させるセルにするので "A1:B1" としてあります。"A1,B1" と書いても大丈夫です。
これまでのサンプルに出てこなかったのは slv.add() です。これは、制約条件を追加するものです。
「slv.add("A1", 'int')」は、A1欄が整数であるとの制約条件を設定します。
「slv.add("D1", '=', 11)」は、D1欄が11であるとの制約条件です。D1欄には、x+yに該当する "=A1+B1" の数式がセットされています。
第２引数の '=' は、第１引数のセルと、第３引数の数値との関係を示すものです。上の例では等号であるとの条件を示しています。
第２引数が 'int' の場合は、「整数である」との条件なので、第３引数がありません。
'int' でなく 'bin' とすれば、バイナリー、つまり0(ゼロ)または1のいずれかの値を取るとの条件になります。
Excel2010では 'dif' も指定できるようです。これは、第１引数にRange(つまり複数のセル)を指定した場合に、各々のセルの値が異なる(同一ではない)こと、各々が整数であること、という条件に当たるようです。
第２引数として指定できるものを再掲しておきます。
'<=':1, '=':2, '>=':3, 'int':4, 'bin':5, 'dif':6
ちなみに、slv.add() をクラスを用いずに書くと、次のようになります。
app.Run("#{prefix}SolverAdd", "A1", 4)
app.Run("#{prefix}SolverAdd", "D1", 2, 11)
(2) 目標条件における「最大」と「最小」の指定
先のサンプルでは、合計購入金額を2600円程度としました。
そうではなく、きまえよく、なるべく多い金額になるようにしたいとします。ただし、お菓子ｘもｙも、どちらも１個以上は買うものとします。
あるいは、けちって、可能な限り少ない金額にしたいとします。どちらも１個以上買うという条件は同じです。
このような場合、目標条件の設定は次のようにします。slv.ok() の記述だけを示します。
slv.ok("C1", 'max', nil, "A1:B1") # 最大値を求めたい
slv.ok("C1", 'min', nil, "A1:B1") # 最小値を求めたい
ソルバーで分析するまでもなく、最大値は、300円のものを10個、200円のものを１個、合計3200円です。
一方、最小値は、200円のものを10個、300円のものを１個、合計2300円です。
最大値を求めるslv04max.rb、最小値を求めるslv04min.rbを圧縮ファイルに同梱してあります。
A1欄が1以上、B1欄が1以上という二つの制約条件を追加してあります。
ここには掲げませんが、よかったら覗いてみて下さい。
(3) 放物線の分析
もう一つの例として、放物線の分析を取り上げます。
数学のx,y座標の原点に立って、初速度28メートル毎秒でボールを投げる場面を想定します。x軸が地面、y軸が空に向かう方向です。秒速28メートルは、おおよそ時速100キロです。
どの角度でボールを投げると、最も遠くまで飛ばすことができるかを考えます。
ボールは、いわゆる放物線を描いて飛びます。時間の経過に伴って右側に進むと同時に高く上がり、やがて高さが下がりはじめて、いずれ地面に落ちます。
角度をa(度)、時間をt(秒)とすると、横の距離xおよび高さyは、それぞれ次の式で表現できます。Excelの関数を利用する形で書きます。
x: "=(28*COS(RADIANS(#{a})))*#{t}"
y: "=(28*SIN(RADIANS(#{a})))*#{t}-#9.8*#{t}^2/2"
28は初速度、9.8は重力加速度です。
RADIANS(a) は、角度a(度)をラジアンに変換するための関数です。三角関数 COS, SIN に渡す引数は、45度などの度数でなくラジアンでなければならないので変換します。
ソルバーを実行した結果は次のような表になります。
| Ａ列 | Ｂ列 | Ｃ列 | Ｄ列 | Ｅ列 | |
| １行 | 放物線の分析 | （初速度 | 28 | m/s） | |
| ２行 | 角度(度) | 時間(秒) | 横の距離x | 高さy | |
| ３行 | x最大値 | 44.999976 | 4.040609 | 80 | -0.000001 |
| ４行 | ↑y最大値 | 44.999976 | 2.020304 | 40 | 19.999983 |
以下にサンブルスクリプトを掲げます。
初速度は、予め C1欄に書き込んでおきます。
角度は10度以上、時間は0.1秒以上という制約条件を設定します。また、数学的にはyがマイナスになるケースもあるので、yが0以上であるとの制約条件も設定します。
それから、単にxの最大値を求めるだけでは何なので、次の段階で、xが最大値になる時のyの最大値(どこまで高く上がるか)も求めてみることにします。
xの最大値を求めるためのソルバーの実行と、yの最大値を求めるためのソルバーの実行とで、ソルバーを２回実行します。
−−−− slv05.rb ここから
#! ruby -Ks
require "exlap"
filename = "slv05.xls"
Exlap.new(filename) do |wb|
slv = ExlSolver.new(wb.app.obj)
ss = wb.fes # 空のワークシートを選択
ss.Activate
vel = "C1" # 初速度が書かれているセル番地
g = 9.8 # 重力加速度 9.8メートル毎秒毎秒
ss.Range("A1:D1").Value = [["放物線の分析", "（初速度", 28, "m/s）"]]
ss.Range("B2:E2").Value = [%w(角度(度) 時間(秒) 横の距離x 高さy)] # 見出し
ss.Range("A3:A4").Value = [["x最大値"], ["↑y最大値"]] # 縦の見出し
for rn in 3..4 # rnは行番号
a = "B#{rn}" # 角度の番地
t = "C#{rn}" # 時間の番地
x = "D#{rn}" # 横の距離xの番地
y = "E#{rn}" # 高さyの番地
ss.Range(a).Value = (rn==3) ? 10 : "=R[-1]C"
ss.Range(t).Value = 0.1 # 時間・秒数の初期値(ダミー)
ss.Range(x).Formula = "=(#{vel}*COS(RADIANS(#{a})))*#{t}"
ss.Range(y).Formula = "=(#{vel}*SIN(RADIANS(#{a})))*#{t}-#{g}*#{t}^2/2"
slv.reset # 念のためソルバー諸設定のリセット
case rn
when 3 # xの最大値を求める
slv.ok(x, 'max', nil, "#{a}:#{t}")
slv.add(a, '>=', 10)
slv.add(t, '>=', 0.1)
slv.add(y, '>=', 0.0)
when 4 # xが最大値の時のyの最大値を求める
slv.ok(y, 'max', nil, t)
slv.add(t, '>=', 0.1)
end
slv.settle
end
wb.save
end
−−−− slv05.rb ここまで
xの最大値を求める場合は、角度aと時間tの二つが変数です。この二つが「変化させるセル」に該当します。
それに対し、yの最大値を求める時は、既に角度が決まっているので(xが最大になる時の角度を採用)、「変化させるセル」は時間tのみです。
このスクリプトを実行すると、xが最大になる場合(最も遠くまで飛ぶ場合)の角度は、おおよそ45度であることが分かります。ソルバーの実行結果では45度ちょうどにはなりませんが、ほぼ45度であることが確認できます。
この場合のxの値は 80(メートル)、飛ぶのに要した時間は約4秒であることも分かります。
次に、角度が45度の時にyが最大になる場合をみると、xが40メートル、yが約20メートル、その時点の経過秒数が約2秒であることが分かります。
以上、連立１次方程式と放物線をソルバーで扱う例を掲げました。
ソルバーの効力を実感できるほどの事例ではありませんが、ソルバーの基本的な使い方の例にはなっていると思います。
--------
4. ソルバーの各種メソッド
これまで SolverOk(目標条件の設定), SolverAdd(制約条件の設定) それから SolverSolve(ソルバーの実行), SolverFinish(逆算結果の書込み)に触れました。
これ以外にもソルバーに関するメソッドがいろいろあります。それらを総て取り上げることはしませんが、以下でいくつかサンプルを示します。
なお、私が ExlSolverクラスを作る際に参考にしたwebは次のところです。
Using the Solver VBA Functions
上のサイトは、Excel2010のソルバーについて説明したものです。
クラス ExlSolver は、一応、Excel2010で使えるように作ったつもりですが、2010での試験は少ししかやっていません。あしからず。
(1) ソルバー情報の save, load
あるワークシート上でソルバーを設定し、実行してからExcelを終了したとします。すると、そのソルバーの設定情報は消えてしまいます。次にExcelを立ち上げ直して、同じ条件のソルバーを実行しようとしても行えません。
ただ、Excelを終了する前に、ソルバーの save を実行してからワークシートを保存すれば、再びそのソルバーをloadして実行できます。
例えば、slv05.rb では、初速度28メートル毎秒の放物線の分析を行いました。このワークブック slv05.xls を再び開いて、第２ワークシートに初速度42メートル毎秒(約時速150キロ)の分析結果を表示させたいと思ったとします。
この場合、第１ワークシート上にソルバー情報をsaveしておいて、第２ワークシート上でそれをloadすると処理が簡単になります。
saveは、シート上の番地を指定して行います。
「slv.save("A10")」とすれば、A10欄を先頭にして、縦１列にソルバー情報が書き込まれます。何個のセルが使われるかは一定でありません。制約条件が多ければ多くのセルが使われます。
slv05.rbの場合、２種類のソルバーを設定・実行しました。xの最大値を求めるもの、yの最大値を求めるものの２種類です。
なので、その各々をsaveする必要があります。slv06.rb では、A10とC10を始点とする領域にそれぞれをsaveすることにしました。
やってみると、A10:A15, C10:C13 にsaveされます。
以下、slv06.rb を掲げます。前半は slv05.rb と共通する部分が多いので、［中略］を挿入して掲げます。
−−−− slv06.rb ここから
#! ruby -Ks
require "exlap"
filename = "slv06.xls"
Exlap.new(filename) do |wb|
slv = ExlSolver.new(wb.app.obj)
ss = wb.ss(1) # 第１ワークシートを選択
ss.Name = "velocity28" # ワークシート名を設定
ss.UsedRange.Clear # 念のためシート全体の内容を消去
ss.Activate
vel = "C1" # 初速度が書かれているセル番地
［中略］
for rn in 3..4 # rnは行番号
a = "B#{rn}" # 角度の番地
t = "C#{rn}" # 時間の番地
［中略］
case rn
when 3 # xの最大値を求める
slv.ok(x, 'max', nil, "#{a}:#{t}")
［中略］
when 4 # xが最大値の時のyの最大値を求める
［中略］
end
slv.settle
save_cell = (rn==3) ? "A10" : "C10"
slv.save(save_cell)
end
# saveした結果を標準出力に出力
puts "SolverSaveされた情報"
for cn in [1,3] # A列、C列
rn = 10
while true
val = ss.cell(rn,cn).Value.to_s
fml = ss.cell(rn,cn).Formula.to_s
break if val == '' and fml == ''
printf("%s\t%s\t%s\n", a1(ss.cell(rn,cn)), val, fml)
rn += 1
end
printf("\n")
end
wb.save
end
# ２回目のExcel起動
Exlap.new(filename) do |wb|
slv = ExlSolver.new(wb.app.obj)
ss = wb.ss(1) # 先ほど作成した第１ワークシート
sname = ss.Name # => "velocity28"
rng = ss.Range("A1").CurrentRegion # 放物線分析結果領域
addr1 = Exl::ab(ss.Range("A10").CurrentRegion) # => "$A$10:$A$15"
addr2 = Exl::ab(ss.Range("C10").CurrentRegion) # => "$C$10:$C$13"
ss = wb.fes # 空のシートを選択
rng.Copy(ss.Range("A1")) # 第１シートの分析結果領域をA1以降にコピー
ss.Range("C1").Value = 42 # 初速度を42m/sにする
ss.Range("B3").Value = 10 # 角度の初期値
ss.Range("C3:C4").Value = [[0.1], [0.1]] # 時間の初期値
ss.Activate
slv.load("#{sname}!#{addr1}") # "velocity28!$A$10:$A$15"
slv.settle
slv.reset
slv.load("#{sname}!#{addr2}") # "velocity28!$C$10:$C$13"
slv.settle
wb.save
end
−−−− slv06.rb ここまで
saveについては前述しました。
loadを実行する時は、引数に "velocity28!$A$10:$A$15" などを与えます。
感嘆符 '!' の前はワークシート名です。ソルバーがセーブされているシート名を記します。同一のシートにあるものをloadするなら、"velocity28!" は不要です。"$A$10:$A$15" だけで大丈夫です。
感嘆符の後はセル番地を書きます。セル番地は絶対番地でないと駄目のようです。少なくとも slv06.rb では相対番地だと うまくいきませんでした。
余談ですが、saveする時にA列の次C列を使い、B列を空けました。こうしておくと、アクティブセル領域の CurrentRegion を使って各々のsave領域を簡単に取得できます。アクティブセル領域は、指定のセルを含む［隔たり(空白行｜空白列)のない一連の領域］のことです。
一つのソルバー情報をA列にsaveし、すぐ隣のB列に次のソルバー情報をsaveすると、CurrentRegionで一つのsave情報を取得する、というわけにはいかなくなります。
ところで、slv06.rb では saveした情報を標準出力に出力しています。A10:A15, C10:C13 の各セルについて、そのValue, Formula を出力しています。
A10:A15の出力結果は次のとおりです。
| 番地 | Value | Formula |
| A10 | 80.0000009927595 | =MAX($D$3) |
| A11 | 2.0 | =COUNT($B$3:$C$3) |
| A12 | true | =$B$3>=10 |
| A13 | true | =$C$3>=0.1 |
| A14 | false | =$E$3>=0 |
| A15 | 100.0 | ={100,100,0.000001,0.05,FALSE,……} |
上記が何を意味しているかの詳細は、前述のwebを参照していただくとして、おおよそ次のとおりです。
- 目標条件設定(SolverOk)での第１・第２引数の情報: SetCell, MaxMinVal。SetCellの値がValueに入る。
- 変化させるセル(ByChange): その個数がValueに入る。
- 制約条件(SolverAdd): 条件が満たされているか否かがValueに入る。個数は様々。
- 最後は各種オプションの値(オプションについては後述)
なお、save(), load() は、クラス内部で Application.Run() を呼び出しているだけなので、引数をHashで渡すことはできません。
クラスExlSolverにおいて、Hashで引数を渡すことができるのは、ok(), add(), options() の三つだけです。
(2) オプションの確認と設定
ソルバー関係では多くのオプションが用意されています。Excel2007までなら 12個のオプション、Excel2010だと 21個のオプションがあります。これだけ多いと、Hashで扱わないと訳が分からなくなります。
クラスExlSolverでは、次のメソッドを設けました。
- get_options: オプションの現在値をHashで返す。
- options(hs): 引数hs(Hash形式)に従ってオプションの値を変更する。Hashでなく通常の列記形式も可能。
- options_key: オプションの名前(MaxTime, Iterationsなど)を順不同で返す。Excel2007までなら12個、Excel2010なら21個を文字列の配列で返す。
個々のオプションの意味については、ここでは触れませんが、例えば次のようなものがあります。
- MaxTime: 問題を解決するのに使う時間の制限(秒)。デフォルト 100 (最大 32767 まで指定可能)
- Iterations: 問題を解決するのに行う試行の最大反復回数、100 (最大 32767 まで指定可能)
- Precision: 問題を解決するときの精度に使用できる範囲、0.000001
- IntTolerance: 問題のいずれかの要素に整数の制限が設定されているときの最適解の誤差の許容範囲、0.05(Excel2010では0.01) (つまり5% | 1%の意味)
- Convergence: 非線型ソルバーの収束の許容限度の度合い、0.0001
(目的セル(SetCell)の値の相対的な変化量が、直前の5回の反復計算でこの収束値よりも小さいとき、目的達成と判断されてソルバーが停止。)
オプションの現在値の確認と設定に関するサンプルを掲げます。
−−−− slv07.rb ここから
#! ruby -Ks
require "exlap"
filename = "dummy.xls"
Exlap.new(filename) do |wb|
slv = ExlSolver.new(wb.app.obj)
hs = slv.get_options
puts "現在のオプションの値の一覧"
slv.options_key.each do |key|
printf("%s\t", key)
p hs[key]
end
printf("\n")
puts "MaxTime, IntToleranceの値を変更."
slv.options({'MaxTime'=>1000, 'IntTolerance'=>2.5})
hs = slv.get_options
printf("現在のMaxTimeの値:")
p hs['MaxTime']
printf("現在のIntToleranceの値:")
p hs['IntTolerance']
end
−−−− slv07.rb ここまで
IntToleranceには注意が必要です。値を取り出すと 0.05 とか 0.01 なのですが、値を設定する時は 5 とか 1 などのようにパーセントの数値で与えます。そうすると、それが100で割り算されて 0.05 | 0.01 の値になります。
サンプルでは 2.5 を設定していますが、値として取り出すと 0.025 になります。
それから、サンプルでは dummy.xls というワークブックを開く形にしてありますが、単にオプションを扱うだけなら、ワークブックを開かなくてもいいのでは？と思われるかもしれません。
xl = Exlap.new slv = ExlSolver.new(xl.obj) hs = slv.get_options ………… xl.quit
上のように、ワークブックを開くことなく処理してもいいのでは？という疑問です。
しかし、試してみると、ワークブックなしではソルバーの処理がうまくいかないようです。何かしらワークブックを開いていないと、ソルバーに関する処理はできないようです。
(3) 終了処理を手動で行う FinishDialog
ソルバーをExcelのGUI操作で行っている場合、終了処理のところでいくつか選択できるようになっています。例えば、分析結果をシナリオとして保存するといったことが可能です。
終了処理だけ手動で行いたい場合、FinishDialog() を用います。
圧縮ファイルに同梱してある slv08.rb がそのサンプルです。これは、slv05.rb(放物線の分析)を少し書き換えただけのものです。
「slv.settle」の１行を次の２行にしただけです。
slv.solve(true) slv.FinishDialog
よかったらお試し下さい。
なお、終了処理のダイアログが表示された時に、注目タスクがExcelになっていない場合は、タスクを切り替えてExcelに焦点を当ててからダイアログボックスに応答して下さい。slv08.rbは、タスクの自動切替は行いません。
(4) 各種の情報を取得する get
ソルバーには SolverGet というメソッドがあります。
目標条件に関する情報、制約条件に関する情報、オプションに関する情報を取得できます。
例えば、次のように番号1を指定すると、ok()のSetCellの情報を得られます。
p slv.get(1) # => "Sheet1!$A$1"
2番を指定すると MaxMinValの情報、3番なら ValueOf、4番だと ByChange(変化させるセル)という具合です。
9番以降はオプションに関する情報です。先述した get_options は、このgetメソッドを何度か実行して一連のオプション情報を取得しています。
5〜8番は、制約条件に関する情報です。5:制約条件の個数、6:制約条件の左側を配列で、7:制約条件の左と右の関係値を配列で、8:制約条件の右側を配列で、という具合です。
オリジナルの SolverGet では引数を数値で与えなければなりませんが、クラスExlSolverでは、該当の文字列で与えることもできます。例えば次のとおりです。
slv.get('SetCell')
slv.get('MaxMinVal')
slv.get('MaxTime')
slv.get('IntTolerance')
制約条件に関しては該当の文字列がないと思いますが、独自に次のものを使えるようにしてあります。
AddCount:5、AddLeft:6、AddRelation:7、AddRight:8
ソルバーについては他にもいくつかメソッドがあります。SolverChange, SolverDelete, SolverOkDialog などです。
これらをクラスExlSolverで呼び出す時は、先頭の "Solver" を除いて、「slv.change(), slv.delete(), slv.OkDialog()」などの形で記述します。いずれも引数にHashを使うことはできません。
これらのメソッド名は、アルファベットの大文字｜小文字を区別しません。Change, Delete などとしても大丈夫です。
ただし、add, get, get_options, ok, options, options_key, settle は、必ず小文字で書く必要があります。
--------
5. メソッドの戻り値
(1) ソルバー実行の solve の戻り値
これまでのサンプルでは、ソルバーの実行と逆算結果の書込みを一括して行う settle() を使ってきました。これは、クラスExlSolverで設けてあるメソッドで、本来のソルバーにはないメソッドです。
本来のメソッドでいうと、SolverSolve, SolverFinish の二つを呼び出します。
ソルバーを試す時に特に重要になるのは、「実行」の SolverSolve の戻り値です。これが0(ゼロ)なら問題なく分析が行われたとの意味になります。しかし、それ以外の値の時は注意が必要です。
ソルバーの実行結果がどうも納得いかないという時は、この戻り値を確認して、設定をやり直して再び試す、そんな手順になると思います。
res = slv.settle p res # => [0.0, 0]
上のように、settle() の戻り値は、二つの数値からなる配列です。
一つ目がSolverSolveの戻り値、二つ目がSolverFinishの戻り値です。
それぞれを別々に行うのであれば、次のように書くこともできます。
p slv.solver(true) # => 0.0 p slv.finish(1) # => 0
solve, finish の引数は、個数は少ないながら、なかなか ややこしいので ここでは触れません。
ただ、finish() の第２引数には [1] とか [1,3] などの配列を引き渡せることだけ付記しておきます。配列の要素の数値は、1:回答レポート、2:感度レポート、3:条件レポートを意味します。
solveの戻り値は多岐に渡りますが、前述のサイトの英語を私なりに翻訳したものを掲げてみます。適切でないところもあると思いますが参考まで。
- 0: 解を発見。総ての制約条件と最適条件が満たされている。
[Solver found a solution. All constraints and optimality conditions are satisfied.] - 1: 現在の解に収斂。総ての制約条件が満たされている。
[Solver has converged to the current solution. All constraints are satisfied.] - 2: 現在値より適切な解をみつけられなかった。総ての制約条件が満たされている。
[Solver cannot improve the current solution. All constraints are satisfied.] - 3: 指定の反復回数の制限に達したため中断。
[Stop chosen when the maximum iteration limit was reached.] - 4: 対象セルの値が収斂していない。
[The Objective Cell values do not converge.] - 5: それらしい解を発見できなかった。
[Solver could not find a feasible solution.] - 6: ユーザーにより中断。
[Solver stopped at user’s request.] - 7: 当該LP(線形計画法)に求められる線形性の条件が満たされていない。
[The linearity conditions required by this LP Solver are not satisfied.] - 8: 問題が大規模過ぎて処理できない。
[The problem is too large for Solver to handle.] - 9: 目的セルまたは制約条件に関するセルでエラー値が発生(目的セルは、ok()のSetCell)。
[Solver encountered an error value in a target or constraint cell.] - 10: 指定の処理時間の制限に達したため中断。
[Stop chosen when the maximum time limit was reached.] - 11: 問題解決のためにはメモリが足りない。
[There is not enough memory available to solve the problem.] - 13: モデルにおけるエラー。総てのセルと制約条件が妥当か確認して下さい。
[Error in model. Please verify that all cells and constraints are valid.] - 14: 許容誤差の範囲内で整数解を発見。全制約条件が満たされている。
[Solver found an integer solution within tolerance. All constraints are satisfied.] - 15: 許容解(整数)の最大生成数に達したため中断。
[Stop chosen when the maximum number of feasible [integer] solutions was reached.] - 16: 許容される(整数の)子問題最大生成数に達したため中断。
[Stop chosen when the maximum number of feasible [integer] subproblems was reached.] - 17: ソルバーは、概ね全体的解決に収束した。
[Solver converged in probability to a global solution.] - 18: 全変数が上限と下限の両方を持たなければならない。
[All variables must have both upper and lower bounds.] - 19: 変数の範囲が、バイナリ(0|1)または全相違の制約条件において矛盾する。
[Variable bounds conflict in binary or alldifferent constraint.] - 20: 変数の上限・下限の範囲内では、それらしい解をみつけられない。
[Lower and upper bounds on variables allow no feasible solution.]
(2) 各種メソッドの戻り値を確認する方法(error_out)
solveメソッドに限らず、ソルバーの各種メソッドは、数値の戻り値を返すようです。
それが0(ゼロ)であれば正常に実行されたという意味だと思います。
マイナスの値(私の経験では -2146826273)だと、何らかの理由で実行されなかったという意味のようです。いわば致命的なエラーです。
プラスの数については、それぞれのメソッドによって意味が違うので、webやExcelのヘルプを参照して下さい。
クラスExlSolverでは、Application.Run() の戻り値を逐一 標準エラー出力に出力できるようにしてあります。次のようにします。
slv = ExlSolver.new(……) slv.error_out = 1 # デフォルトは0
こうすると、何らかのメソッドを呼び出した時に、内部で Application.Run() が実行される度に、その戻り値と実行されたコマンドや引数を標準エラー出力に出力します。
slv.error_out = -1
とすれば、戻り値がマイナスの時だけ出力します。
ソルバーの結果がどうも奇妙だという場合は、このようにして戻り値を確認すると、解決の糸口が得られるかもしれません。
ちなみに、戻り値がマイナスの場合、-2146826273 に 2146828288 を加算すると 2015 になります。
セルに関するエラー XlCVError Enumeration では、2015は、「Cell error value: #VALUE!」に当たります。
ソルバーに関するエラーとセルのエラーを関連づけて考えていいのかどうか分かりませんが、一応、上記の加算を行うと 2015 のようなエラーコードらしきものになります。
--------
雑駁な説明ですが、これでおわりにします。
rubyでExcelのソルバーを利用する方法の基本的なところは、一応、触れることができたのでは？と思っています。
〜 以上 〜
Copyright (C) T. Yoshiizumi, 2012 All rights reserved.
Keyword(s):
References: