# GoalSeek03.rb.txt: ruby script (encoding: Windows-31J) # コールシークの操作 z_goal03.xls を出力 # 「PMT(利率, 返済月数, 返済総額)=返済月額」で《返済総額》を逆算 # 返済期間(36ヶ月)は固定。年利1%〜10%、返済月額5万円〜10万円のとき # 借用可能限度額(総額)がいくらになるかをみる require "exlap" # 逆算のための各種情報を設定 row_data = [50000, 60000, 70000, 80000, 90000, 100000] # 返済月額 col_data = %w(1% 2% 3% 4% 5% 6% 7% 8% 9% 10%) # 年利 calc_cell = "H1" # 数式&ゴールシーク用セル start_cell = "B3" # 表の領域の始点 pay_count = 3*12 # 返済期間・回数:3年=36ヶ月、つまり36回払い yen_format = "\\#,##0;[赤]\\-#,##0" # Excelでの「円」の表示フォーマット filename = "z_goal03.xls" if test(?e, filename) # z_goal03.xlsが存在するなら削除 File.unlink(filename) end xl = Exlap.new # Excelの起動 xl.opens(filename) do |wb| # ワークブックを開く ss = wb.fes # 空のワークシートを選択 ccell = ss.Range(calc_cell) # 数式&ゴールシーク用セルを変数に代入 sy,sx = Exl::yx(ss.Range(start_cell)) # 表の領域の始点(番地)を数値で得る ss.from_a(sy, sx+1, row_data) # 返済月額を横方向に列記 col_data2 = Exl::table_turn(col_data) # 配列の縦・横逆転 ss.from_a(sy+1, sx, col_data2) # 年利を縦方向に列記 rng = ss.Range(start_cell).CurrentRegion # 表の領域全体 ly,lx = ss.range_last(rng) # 表の領域の最終番地 for y in (sy+1)..ly for x in (sx+1)..lx rate = Exl::a1(ss.cell(y,sx)) # 年利のセル番地をA1形式で得る unit_pay = Exl::a1(ss.cell(sy,x)) # 返済月額のセル番地 amount = Exl::a1(ss.cell(y,x)) # 借用可能限度額(総額)格納セル番地 ccell.Formula = "=PMT(#{rate}/12,#{pay_count},-#{amount})" ccell.GoalSeek('Goal'=>ss.Range(unit_pay).Value, 'ChangingCell'=>ss.Range(amount)) end end ccell.Formula = ccell.Value = "" #計算式用のセルの内容消去 ss.rr(sy,sx+1, sy,lx).NumberFormatLocal = yen_format ss.rr(sy+1,sx+1, ly,lx).NumberFormatLocal = yen_format # 見出しの書き込み ss[1,1] = "借用可能限度額算出" ss[1,2] = "返済期間:" ss[1,3] = "#{pay_count}ヶ月" ss[sy+1,sx-1] = "年利" ss[sy-1,sx+1] = "返済月額" ss.range_autofit wb.save # ワークブックの保存 end xl.quit