ソルバー：計算式の最適解探索

以下に掲げるrubyスクリプトのサンプルは、Excel操縦用ライブラリ exlap.rb 最新版: exlap_latest.zipに同梱のものを利用するとの前提で書かれています。

addin.rb: アドイン・ファイルのリストを表示
slv01.rb: 2の平方根を求める
slv02.rb: 2～10の平方根を求める
slv03.rb: ExlSolverクラス利用による平方根の計算
slv04.rb: 連立方程式「x*200+y*300=2600」　「x+y=11」を解く
slv04max.rb: x+y=11, x>=1, y>=1 の時に x*200+y*300 の最大値を求める
slv04min.rb: x+y=11, x>=1, y>=1 の時に x*200+y*300 の最小値を求める
slv05.rb: 放物線の分析: なるべく遠くまで投げるための角度、その時の最高位置を求める
slv06.rb: save, loadのサンプル
slv07.rb: ソルバーの各オプションの値を確認・設定する
slv08.rb: FinishDialog利用の例

addin.rb

#! ruby -Ks
# display the Excel addin list, and report especially on the Solver file.
require "win32ole"
app = WIN32OLE.new("Excel.Application")
ary = []
solver_file = nil
app.AddIns.each do |target|
  ary << [target.Name, target.FullName]
  if target.Name =~ /^solver\.x/i
    solver_file = target.FullName
  end
end
app.Quit
unless solver_file
  puts 'can not find the solver addin file'
else
  puts "the solver file: #{solver_file}"
end
printf("\n")
puts 'Excel addin list'
ary.each do |row|
  puts row.join("\t")
end

download addin.rb

slv01.rb

#! ruby -Ks
# ソルバーを使って2の平方根を求める
require "exlap"
solver_file = 'C:\Program Files\Microsoft Office' +
    '\Office12\Library\SOLVER\SOLVER.XLAM'  # for Excel2007
unless test(?e, solver_file)  # SOLVER.XLAMが存在しない
  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

download slv01.rb

slv02.rb

#! ruby -Ks
# ソルバーを使って2～10の平方根を求める
# solver.xla | solver.xlam の所在を検索する仕組みを内包
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

download slv02.rb

slv03.rb

#! ruby -Ks
# ExlSolverクラス利用による平方根の計算
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

download slv03.rb

slv04.rb

#! ruby -Ks
# 連立方程式「x*200+y*300=2600」　「x+y=11」を解く
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

download slv04.rb

slv04max.rb

#! ruby -Ks
# x+y=11, x>=1, y>=1 の時に x*200+y*300 の最大値を求める
require "exlap"
filename = "slv04max.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", 'max', nil, "A1:B1")  # 最大値を求めたい
  slv.add("A1", 'int')  # A1が整数であるとの制約条件
  slv.add("B1", 'int')  # B1が整数であるとの制約条件
  slv.add("A1", '>=', 1)
  slv.add("B1", '>=', 1)
  slv.add("D1", '=', 11)  # x+y=11の制約条件
  slv.settle  # ソルバーの実行と結果のセルへの書き込み
  wb.save
end

download slv04max.rb

slv04min.rb

#! ruby -Ks
# x+y=11, x>=1, y>=1 の時に x*200+y*300 の最小値を求める
require "exlap"
filename = "slv04min.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", 'min', nil, "A1:B1")  # 最小値を求めたい
  slv.add("A1", 'int')  # A1が整数であるとの制約条件
  slv.add("B1", 'int')  # B1が整数であるとの制約条件
  slv.add("A1", '>=', 1)
  slv.add("B1", '>=', 1)
  slv.add("D1", '=', 11)  # x+y=11の制約条件
  slv.settle  # ソルバーの実行と結果のセルへの書き込み
  wb.save
end

download slv04min.rb

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

download slv05.rb

slv06.rb

#! ruby -Ks
# 放物線の分析: なるべく遠くまで投げるための角度、その時の最高位置を求める
# 第１ワークシートでsave、第２ワークシートでloadを行うサンプル
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"  # 初速度が書かれているセル番地
  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
    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", Exl::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

download slv06.rb

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

download slv07.rb

slv08.rb

#! ruby -Ks
# 放物線の分析: なるべく遠くまで投げるための角度、その時の最高位置を求める
# FinishDialog利用の例
require "exlap"
filename = "slv08.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.solve(true)
    slv.FinishDialog
  end
  wb.save
end

download slv08.rb

exlapのページへ戻る