rubyによるExcel操縦あれこれ 〜 データテーブル、ゴールシークの利用
rubyによるExcel操縦あれこれ 〜 データテーブル、ゴールシークの利用
最終更新日： 2012/02/11
ExcelのWhat-If分析機能の一種であるデータテーブル、ゴールシークの利用について記します。
以降で掲げるサンプルスクリプトを私が実行した環境は次のとおり。
- MS-Windows xp | vista
- Excel2002(Office xp版) | Excel2007
- ruby ver 1.8.7
- 使用ライブラリ: 拙作exlap.rb v1.1
what_if.zip には、以下で掲げるサンプルスクリプトのほか、exlap.rbも含まれています。別途ダウンロードする必要はありません。
なお、rubyがインストールされていない環境でサンプルスクリプトを実行したい方は、exl.exeをお試し下さい。詳しくは exl.exeの使い方 を参照して下さい。
＜はじめに＞
Excelには What-If 分析と称する機能があります。「もしも×××ならば△△△の結果になる」をいくつか表示させる機能です。×××に相当する条件を複数セット用意し、各々の条件セットについてどのような結果△△△が得られるかを見てみる機能です。
Excel の What-If 分析には、シナリオ、データテーブル、ゴールシークの三つがあります。シナリオについては既に書いたので、ここではデータテーブルとゴールシークについて記します。
--------
1. データテーブル
体重と身長から体格指数(BMI: Body Mass Index)を求めることができます。このBMIが25以上だと肥満、18.5未満だとやせすぎということのようです。
ＢＭＩ＝「体重(キログラム)」÷「身長(メートル)」÷「身長(メートル)」
という計算式で求めることができます。
例えば、体重65キロ、身長170センチの人の場合、65/1.7/1.7=22.5 となります。
以下では、このＢＭＩを材料にしてデータテーブルを作成してみます。
Webを検索すると、データテーブルの材料として住宅ローン返済がよく取り上げられているようです。
３千万円を借りた場合、年利が3%と仮定すると、20年返済だと返済月額がいくらになるか、30年ならどうか。また、年利が2%ならどうかなどを見てみるケースです。
話題としておもしろいので、このローン返済についてもサンプルを示してみたいと思いますが、まずは計算式が簡単なＢＭＩを取り上げます。
(1) 単入力テーブル
データテーブルには、単入力テーブルと複入力テーブルの２種類があります。
ＢＭＩの例でいうと、体重をx、身長をyとした場合、身長yを170センチに固定し、体重xだけが変化するケースを考えるのが単入力テーブルです。
計算式で書くなら、BMI = x/1.7/1.7 となり、変数が一つです。
一方、体重だけでなく、身長も変化させてみるのが複入力テーブルです。
ここでは、身長は 170センチに固定し、体重が 50, 55, 60, …… 80 と７段階で変化するケースを考えます。
最初に、データテーブルを作成してみた結果のワークシートを示してみます。２行からなる表です。セル番地でいうと A1:H2 の２行×８列です。
| 50 | 55 | 60 | 65 | 70 | 75 | 80 | |
| 0 | 17.3 | 19.03 | 20.76 | 22.49 | 24.22 | 25.95 | 27.68 |
この表は、A1欄が空欄、「0」のA2は、計算式が書かれているセルです。「0」は、体重が０の場合のBMIが出てしまっているものです。
それ以外は、B1:H1に体重の数値、B2:H2にBMIの数値が表示されています。
このデータテーブルを作成するためのrubyスクリプトは、いたって簡単です。ただ、Excelの挙動に分かりにくい面があります。
プログラミング的な発想でいうと、1)50〜80の体重データ、2)計算式、3)計算結果たるBMI表示領域、この三つを設定して「データテーブルを設定しなさい」という命令を実行する、というような手順になると思います。Excelでも基本的にはこの手順をたどりますが、ちょっと分かりにくいところがあります。
Excelをスクリプトで動かすための手順を文章化すると、次のようになります。５ステップです。
- B1:H1のそれぞれにに体重50〜80を書き込む。
- 代入セルを決める。使う予定のないセルをあてる。例えば「A99」
- A2に計算式を書き込む。式は「=A99/1.7/1.7」
「A99」は代入セルで、Excelは、計算式を計算する際、体重の値を一つづつこのセルにセットして計算を行う。計算が終了すると、A99は空欄に戻る。 - データテーブル領域全体(A1:H2)を変数(例えばrng)にセットする。ここでいう領域全体とは、体重データ、計算式、BMI表示領域を合わせたもの。
- 領域全体rngに対して、Tableメソッドを適用する(引数として代入セルを渡す)。体重データが横に広がる形で書かれていることをExcelに知らせるため、引数の与え方を工夫する。もし体重データが縦に広がる形で書かれていれば、引数の与え方が違ってくる。
代入セルというのが少々分かりにくいのと、データテーブル領域全体を設定するというやり方が腑に落ちないところですが、そのような仕様なのでやむを得ません。領域全体のどの部分が素材データなのか、どこに計算式が書かれているのか、その識別は Excelに「お任せ」になります。ほんとは各々きちんと指定したいところです。
計算式を書き込むセル番地ですが、BMI表示領域の左隣であるA2でないと、うまくいかないようです。データテーブル領域にはA1欄もあり、空欄のままになっています。ならば、A2でなくA1に計算式を書き込んでもかまわないのでは、と思って試してみると、うまくいきません。計算結果表示領域の左隣に計算式があるものとExcelが想定するのだろう？と思います。
以下、データテーブルを作成するためのスクリプトを掲げます。それほど長いものではないので全部を掲げておきます。
−−−− dtbl01.rb ここから
#! ruby -Ks
require "exlap"
filename = "dtbl01.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
ss.Range("B1:H1").Value = [[50, 55, 60, 65, 70, 75, 80]] # 体重データ
x_cell = "A99" # 代入セル、使う予定のない適当なセル
ss.Range("A2").Formula = "=#{x_cell}/1.7/1.7" # BMI計算式
rng = ss.Range("A1:H2") # データテーブル領域全体
rng.Table({'RowInput'=>ss.Range(x_cell)}) # データテーブルの設定
wb.save # ワークブック保存
end
−−−− dtbl01.rb ここまで
「rng.Table({'RowInput'=>ss.Range(x_cell)})」というのがデータテーブルを作成するための肝です。
「ss.Range(x_cell)」は、「ss.Range("A99")」と書いても同じです。代入セルを示しています。
今回のサンプルでは体重データを横方向に広げて書きましたが、縦方向に広げた場合は、'RowInput' を 'ColumnInput' に変更します。
また、Tableメソッドに引数を渡す際、Hashを用いるのが面倒なら、RowInput, ColumnInput の順番で引数を渡すことになっているルールを利用します。
RowInputだけ渡すのであれば
rng.Table(ss.Range(x_cell))
と書くことができます。
ColumnInputだけ渡すなら
rng.Table(nil, ss.Range(x_cell))
と書くことができます。
参考まで、体重データを縦方向に広げた場合のスクリプトを dtbl02.rb として圧縮区ファイルに同梱しておきます。
体重データを A2:A8 に書き込み、計算式をB1に記します。データテーブル領域全体は A1:B8 となります。それ以外は dtbl01.rb と同じです。
［補足］ 意味のない「0」の表示を避ける方法
計算式を書き込んだA2欄に、「0」が表示されてしまいます。これを避けるためには、ExcelのIF関数を用います。
例えば、A2欄に次のように記述します。
=IF(A99="","",A99/1.7/1.7)
IF関数の書式は、「IF(条件式,値１,値2）」です。条件式が成り立つ場合は値１が、そうでなければ値2が表示されます。上の記述は、「もしA99欄が空欄なら空白を表示し、そうでなければBMIの計算結果を表示する」という意味です。
rubyスクリプトとして書く場合は次のようになります。
x_cell = "A99"
ss.Range("A2").Formula = "=IF(#{x_cell}=\"\",\"\"," +
"#{x_cell}/1.7/1.7)"
(2) 複入力テーブル
今度は、体重だけでなく身長も変化させることを考えます。
体重は 50〜80、身長は 150〜180 の範囲で、５づつ上がる形で変化させます。体重を横方向に、身長を縦方向に広げる形にします。
データテーブルの出力結果は次のとおり。データテーブルの領域は、A1:H8の８行×８列です。
| 50 | 55 | 60 | 65 | 70 | 75 | 80 | |
| 150 | 22.22 | 24.44 | 26.67 | 28.89 | 31.11 | 33.33 | 35.56 |
| 155 | 20.81 | 22.89 | 24.97 | 27.06 | 29.14 | 31.22 | 33.3 |
| 160 | 19.53 | 21.48 | 23.44 | 25.39 | 27.34 | 29.3 | 31.25 |
| 165 | 18.37 | 20.2 | 22.04 | 23.88 | 25.71 | 27.55 | 29.38 |
| 170 | 17.3 | 19.03 | 20.76 | 22.49 | 24.22 | 25.95 | 27.68 |
| 175 | 16.33 | 17.96 | 19.59 | 21.22 | 22.86 | 24.49 | 26.12 |
| 180 | 15.43 | 16.98 | 18.52 | 20.06 | 21.6 | 23.15 | 24.69 |
スクリプトがたどるステップは、基本的に単入力テーブルの時と同じです。
計算式を書き込むのがA1欄になること、代入セルを一つだけでなく二つ設けること、データテーブルの領域全体が８行×８列と縦・横の両方に広がること、この３点くらいが相違点です。
また、単入力テーブルの時は、Tableメソッドに渡す引数が一つでしたが、今度は二つになります。横方向に関する代入セルと縦方向に関する代入セルの二つを渡します。
以下、スクリプトを掲げます。
−−−− dtbl03.rb ここから
#! ruby -Ks
require "exlap"
filename = "dtbl03.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
ss.Range("B1:H1").Value = [[50, 55, 60, 65, 70, 75, 80]] # 体重
ss.Range("A2:A8").Value = [[150], [155], [160], [165], # 身長
[170], [175], [180]]
x_cell = "A99" # 体重の代入セル
y_cell = "B99" # 身長の代入セル
ss.Range("A1").Formula = "=IF(OR(#{x_cell}=\"\",#{y_cell}=\"\"),\"\"," +
"#{x_cell}/((#{y_cell}/100)^2))"
rng = ss.Range("A1:H8") # データテーブル領域全体
rng.Table(ss.Range(x_cell), ss.Range(y_cell)) # データテーブルの設定
wb.save # ワークブック保存
end
−−−− dtbl03.rb ここまで
［補足］ 代入セルの呼び名
これまで、横方向に関する代入セル、縦方向に関する代入セルというように書いてきましたが、横方向用を「行の代入セル」、縦方向用を「列の代入セル」といいます。
ExcelをGUI操作してデータテーブルを作る場合、Excel2002なら［データ］ → ［テーブル］で、また、Excel2007なら［データ］ → ［What-If分析］ → ［データテーブル］で、代入セル入力場面に進むことができます。このとき、「行の代入セル」と「列の代入セル」を入力できますが、行の代入セルが横方向用、列の代入セルが縦方向用です。
私は、「行」とか「列」といわれても、どちらが横方向用でどちらが縦方向用か、いつも分からなくなります。なので、この解説では「横」と「縦」の表現を用いています。
(3) 他のケースに応用しやすいデータテーブル作成スクリプト
前述の dtbl03.rb を書き換えて、例えば、住宅ローン返済のテーブルを作成しようとすると面倒です。
というより、そもそも、与える素材データの個数を変えるだけでも H8 などのセル番地を書き換えなければならず大変です。
そこで、もう少し汎用性の高いスクリプトにしてみます。与えられる素材データの個数から、データテーブル領域の範囲(セル番地)を自動的に求めます。なので、データテーブルの領域がどこからどこまでになるかを気にする必要がなくなります。
以下に掲げる dtbl04.rb は、処理の基本は dtbl03.rb とほぼ同じですが、様子がかなり違います。
最初にデータテーブル作成に必要な各種の値を設定し、それから Excel を起動してデータテーブルを作成、最後に見出しなどを書き込んでワークブックを保存しています。
最初に設定する値は次の六つです。
- 横方向素材データ(体重): 変数 row_data
- 縦方向素材データ(身長): 変数 col_data
- 横方向データ用の代入セル: 変数 row_cell
- 縦方向データ用の代入セル: 変数 col_cell
- BMI計算式: 変数 formula
- 計算式のセル(データテーブル領域の始点): 変数 formula_cell
dtbl04.rbを書き換えて、ローン返済用にする場合、上の６項目を書き換えればＯｋです。もちろん見出しなどは別途書き換える必要がありますが。
以下、dtbl04.rbを掲げます。
−−−− dtbl04.rb ここから
#! ruby -Ks
# データテーブル: 複入力テーブル。いろいろなケースに応用しやすいよう改定
# BMIの計算。体重50〜80、身長150〜180の変化結果をみる
require "exlap"
row_data = [50, 55, 60, 65, 70, 75, 80] # 横方向データ(体重)
col_data = [150, 155, 160, 165, 170, 175, 180] # 縦方向データ(身長)
row_cell = "H1" # 横方向データ用の代入セル
col_cell = "I1" # 縦方向データ用の代入セル
formula = "#{row_cell}/((#{col_cell}/100)^2)" # BMI計算式
formula_cell = "B3" # 計算式のセル(データテーブル領域の始点)
filename = "dtbl04.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
y,x = Exl::yx(ss.Range(formula_cell)) # 計算式セルの番地を数値で得る
row_data.each_with_index do |val, i| # 横方向のデータ書込み
ss[y, x+i+1] = val
end
col_data.each_with_index do |val, i| # 縦方向のデータ書込み
ss[y+i+1, x] = val
end
ss.Range(formula_cell).Formula = "=IF(OR(#{row_cell}=\"\"," +
"#{col_cell}=\"\"),\"\",#{formula})"
rng = ss.Range(formula_cell).CurrentRegion # データテーブル領域全体
rng.Table(ss.Range(row_cell), ss.Range(col_cell)) # データテーブルを設定
ly,lx = ss.range_last(rng) # データテーブル領域の終点を数値で得る
ss.rr(y+1,x+1, ly,lx).NumberFormatLocal = "#0.0" # 小数点１桁まで表示
# 見出しなどの書込み
ss[1,1] = "BMI算出結果"
ss[y-1, x+1] = "体重" # 横方向の見出し
ss[y-1, x+2] = "(kg)" # 体重の単位(キログラム)
ss[y+1, x-1] = "身長" # 縦方向の見出し
ss[y+2, x-1] = "(cm)" # 身長の単位(センチメートル)
wb.save
end
−−−− dtbl04.rb ここまで
上のスクリプトについて、少し解説してみます。
変数 row_data, col_data には、それぞれ横方向のデータ(体重)、縦方向のデータ(身長)をセットします。
row_cell, col_cell には代入セルの番地をセットします。前者が横方向データ用(体重用)、後者が縦方向データ用(身長用)です。今回は A99 などでなく、H1, I1 を用いています。１行目に「BMI算出結果」というタイトルを表示するので、その少し離れた横っちょを代入セルに使うこととしました。
formula には BMI計算式をセットします。「体重」÷（身長の二乗）」ですが、身長のデータをセンチメートル単位で扱うので、メートル単位に変換するため100で割ってから二乗しています。
formula_cell には、計算式セルの番地(つまりデータテーブルの始点番地) B3 をセットします。１行目にはタイトル、２行目には横方向データの見出し「体重」を、また、左端の１列目には縦方向データの見出し「身長」を書き込むので、データテーブルの始点は、３行目・２列目(B3)としました。
以上がデータテーブルを作成するための基本情報です。
次に、データテーブル作成のための処理手続きです。
「y,x = Exl::yx(ss.Range(formula_cell))」は、始点 B3 の番地を数値で得るものです。y, x にはそれぞれ 3, 2 がセットされます。Exl::yx() は、ライブラリexlapの中で定義されています。
「rng = ss.Range(formula_cell).CurrentRegion」は、データテーブルの領域全体を変数rngにセットするものです。
CurrentRegionは、指定番地を含むアクティブセル領域を返します。アクティブセル領域は、完全な空白行と完全な空白列のどちらも含まない一連の四角い領域のことです。横と縦の素材データを書き込んだ後にアクティブセル領域を求めると、それがデータテーブル領域になります。
「ly,lx = ss.range_last(rng)」は、データテーブル領域の右下端の番地を数値で得るものです。
「ss.rr(y+1,x+1, ly,lx).NumberFormatLocal = "#0.0"」は、データテーブル領域のうち、素材データを除く部分、つまり計算式の結果が表示される部分について、小数点１桁までを表示するとの設定です。これを設定しないと、小数点以下がもっと長くずらずら表示されます。
rr()は、ライブラリexlapで定義されているメソッドです。与えられた四つの数値(左上端の番地、右下端の番地)から、その範囲のRangeオブジェクトを返します。
この dtbl04.rb を書き換えて、ローン返済に関する複入力テーブルを作成するようにしたのが dtbl05.rb です。
返済総額３千万円、返済年数 10〜35年(５年刻み)、年利 1%〜3.5%(0.5%刻み)で作成します。
以下にそのスクリプトを掲げておきます。
−−−− dtbl05.rb ここから
#! ruby -Ks
# データテーブル: 複入力テーブル。dtbl04.rbをローン返済用に変更
# ローン返済。返済年数 10〜35、年利 1%〜3.5%の変化結果をみる
require "exlap"
amount = 30000000 # 返済総額
row_data = [10, 15, 20, 25, 30, 35] # 返済年数
col_data = %w(1% 1.5% 2% 2.5% 3% 3.5%) # 年利
row_cell = "H1" # 横方向データ用の代入セル
col_cell = "I1" # 縦方向データ用の代入セル
formula = "PMT(#{col_cell}/12,#{row_cell}*12,-#{amount})"
formula_cell = "B3" # 計算式のセル(データテーブル領域の始点)
filename = "dtbl05.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
y,x = Exl::yx(ss.Range(formula_cell)) # 計算式セルの番地を数値で得る
row_data.each_with_index do |val, i| # 横方向のデータ書込み
ss[y, x+i+1] = val
end
col_data.each_with_index do |val, i| # 縦方向のデータ書込み
ss[y+i+1, x] = val
end
ss.Range(formula_cell).Formula = "=IF(OR(#{row_cell}=\"\"," +
"#{col_cell}=\"\"),\"\",#{formula})"
rng = ss.Range(formula_cell).CurrentRegion # データテーブル領域全体
rng.Table(ss.Range(row_cell), ss.Range(col_cell)) # データテーブルを設定
ly,lx = ss.range_last(rng) # データテーブル領域の終点
ss.rr(y+1,x+1, ly,lx).NumberFormatLocal = "#,##0" # カンマ付き整数
# 見出しなどの書込み
ss[1,1] = "ローン返済月額算出"
ss[1,2] = "返済額 #{amount}円"
ss[y-1, x+1] = "返済期間(年)" # 横方向の見出し
ss[y+1, x-1] = "年利" # 縦方向の見出し
wb.save
end
−−−− dtbl05.rb ここまで
Excelの関数 PMT() は、PMT(金利,返済期間,返済総額) で単位あたり返済額を算出します。
例えば、「PMT(0.02/12, 30*12, 30000000)」によって、年利2%、返済年数30年、返済総額3千万の返済月額を得ることができます。月額は、マイナス値として出てきます。マイナスをとりたければ、返済総額の指定をマイナス値にすればＯｋです。
ほんとは、PMT() にもっと多くの引数を渡すことができます。ここでは省略しますが、詳しくは別のWebなどを参照して下さい。
［補足］ 設けられているデータテーブルをGUI操作する時の挙動
例えば、先のスクリプトで作成したdtbl05.xlsをExcelで開いたとします。
このとき、返済期間の最後の 35 を 40 に書き換えたとすると、該当の列の返済月額が総て変化します。35年返済でなく、40年返済の場合の月額が表示されます。
年利の方の 3.5% を 4% に書き換えたとすれば、同じように 3.5%でなく4%の場合の数値が該当の行に表示されます。
このように、データテーブルは、いわば動的に変化します。なお、後述のゴールシークは、このような動的な変化を引き起こしません。
(4) データテーブルの存在確認と関連情報の取得
他の人からExcelファイルをもらった場合、その中にデータテーブルが含まれているかどうかを確認する方法について記します。
データテーブルの領域内で、計算式の計算値が入るセルには、単純にその値が書き込まれているわけではなく、「=TABLE(H1,I1)」などのように、TABLE関数の形で式がセットされています。
rubyスクリプトの形で、もう少し正確に記すと次のようになります。dtbl05.xls(ローン返済のテーブル)を見てみた時の例です。
p ss.Range("C4").Formula # => "=TABLE(H1,I1)"
p ss.Range("C4").Value # => 262812.364110492
TABLE関数の引数は二つで、前者が行の代入セル、後者が列の代入セルです。
単入力テーブルの時は、どちらか一方が省略されています。例えば、「=TABLE(H1,)」とか「=TABLE(,I1)」のようになっています。
同じデータテーブルに属する計算式適用セルには、総て同じ「=TABLE(……)」がセットされています。
このことを利用すれば、データテーブルが存在するかどうか、その領域がどこからどこまでか、計算式がどこに書かれているか、計算素材のデータはどうか、といったことを調べて出力することができます。
ほんとは、TABLE関数を手がかりにするのでなく、もっと簡便なやり方があるような気がしますが、私には分かりませんでした。
圧縮ファイルに同梱してある dtbl06.rb は、カレントディレクトリにある *.xls ファイルについて、データテーブルが存在するかどうかをチェックし、存在する時はその関連情報を標準出力に出力します。
単入力テーブル、複入力テーブルのどちらにも対応しています。
計算式がどのセルに書かれているかは、私なりの推測に基づくものですが、概ね大丈夫だろうと思います。
dtbl06.rb は、意外に長くなったのでここには掲げません(70行程度)。必要に応じて参照して下さい。
-------------------------------------------------------------------------
2. ゴールシーク
(1) 単純なゴールシークの例
ゴールシークは、与えられた計算式が特定の値を算出する時の、その式内の変数の値を逆算するものです。
例えば、「y = x*x」という式があって、yが2である場合に、xが何かを求めるのがゴールシークです。このケースでは、当然ながら x は、2の平方根(1.41421……)になります。
逆算するための計算式が明らかな場合は、わざわざゴールシークを用いなくても、その逆算のための式を使えばいいだけですが、とりあえずのサンプルとしては単純なものがいいので、この平方根算出のケースを取り上げます。
Excelのゴールシークは、与えられた式から逆算のための式の見当をつけて、その計算結果を出す、というわけではありません。
とりあえず x に適当な値を代入して計算を行い、その結果と 2 を比較して、もっと2に近い結果になりそうなxについて再度計算してみる。この反復処理を行って答えとする、というやり方のようです。なので、当然ながら、ちょうどの値が得られるとは限りません。多くの場合、近い値で良しとすることになります。
また、ゴールシークを適用したとしても、ExcelをGUI操作している最中に、逆算の結果を動的に変化させることはできません。文章で書いても分かりにくいので、後でサンプルで示します。
まずは、ゴールシークの単純な例、2の平方根を求めるケースを考えます。次の２ステップで行います。
- A1欄に計算式「=B1*B1」を書き込みます。B1欄が、いわば変数xに相当します。ゴールシークの実行が終わると、逆算した値がB1欄に表示されます。
- A1欄に対してゴールシークを適用します。GoalSeek() メソッドで行います。
GoalSeek() に与える引数は２つで、目標値(今回は2)、変数xの値をセットするセル(今回はB1)です。Excelの用語では、前者をGoal、後者をChangingCell(変化させるセル)というようです。
該当部分のみ記述すると、次のようになります。変数ssは、ワークシートオブジェクトを示します。
ss.Range("A1").GoalSeek(2, ss.Range("B1"))
同じことを実現するためのHashを用いた記述は、次のとおりです。
ss.Range("A1").GoalSeek({'Goal'=>2, 'ChangingCell'=>ss.Range("B1")})
ChangingCellの番地を数値(RC形式)で表現する形も下に示してみます。前者は絶対番地形式、後者は相対番地形式です。
ss.cell(1,1).GoalSeek(2, "R1C2") ss.cell(1,1).GoalSeek(2, "RC[1]")
「RC[1]」は、「R[0]C[1]」の省略記述で、注目セルと行のずれが0(つまり同じ行)・列のずれが1(つまり右隣)のセルを示します。
このようにゴールシークを適用したワークシートをExcelで開くと、A1には 2.000031、B1には 1.414224 が表示されます。B1が逆算結果の値、A1がその値を二乗した値です。
以下、ゴールシークに関するサンプルスクリプトを掲げます。
−−−− gs01.rb ここから
#! ruby -Ks
require "exlap"
filename = "gs01.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
ss.Range("A1").Formula = '=B1*B1'
ss.Range("A1").GoalSeek(2, ss.Range("B1"))
wb.save
end
−−−− gs01.rb ここまで
(2) 一連の(複数の)ゴールシークを表示
ゴールシークそのものについて、他に付け加えることはありませんが、これだけだと寂しいので、1〜10の10個の整数の平方根を表示することを考えてみます。
A列に1〜10の整数を縦に並べ、B列には「=C1*C1」のような計算式をセットして、C列には逆算の結果を表示する形を考えます。
B列の１行目には「=C1*C1」、２行目には「=C2*C2」をセットし、10行まで類似の形で書き込むわけですが、このような場合、セルの番地をRC形式の相対番地で扱うと便利です。
また、B列の10行それぞれについて GoalSeek() を適用しますが、目標値を数値で与えるのでなく、左隣のA列の値を与えることにします。といっても、数値で与えるのと何ら違いはありませんが。
具体的には次のようになります。
−−−− gs02.rb ここから
#! ruby -Ks
require "exlap"
filename = "gs02.xls"
Exlap.new(filename) do |wb|
ss = wb.fes # 空のワークシートを選択
y = 0
for val in 1..10
y += 1
cell1 = ss.Cells.Item(y,1) # A列
cell2 = cell1.Offset(0,1) # cell1の右隣、つまりB列
cell1.Value = val
cell2.Formula = "=RC[1]*RC[1]"
cell2.GoalSeek(cell1.Value, "RC[1]")
end
wb.save
end
−−−− gs02.rb ここまで
参考まで、上のスクリプトで作成されるワークシートの表示結果を下に掲げます。小数点以下７桁を四捨五入して、６桁まで表示しています。
| 1 | 1.000082 | 1.000041 |
| 2 | 2.000031 | 1.414224 |
| 3 | 2.999325 | 1.731856 |
| 4 | 4.000092 | 2.000023 |
| 5 | 5.000760 | 2.236238 |
| 6 | 5.999992 | 2.449488 |
| 7 | 6.999998 | 2.645751 |
| 8 | 8.000315 | 2.828483 |
| 9 | 9.000023 | 3.000004 |
| 10 | 9.999017 | 3.162122 |
1, 4, 9 の平方根がちょうどの値になっていませんが、概ね適切な値が算出されているように思います。例えば、小数点以下２桁までしか表示しない場合を考えると、それなりの精度だと思います。
ところで、先にゴールシークが「動的に変化させることができない」と書きましたが、それは次のような意味合いです。
gs02.xlsをExcelで開いて、A列・10行目の 10 を 100 に書き換えたとします。100の平方根は 10 なので、その右側のセルの値がそれに近いものに変化するかというと、何も変化しません。相変わらず 10の平方根の近似値が表示されているだけです。
私が確認したかぎりでは、ゴールシークは、それぞれが単発で実行されて、それっきりのもののようです。ゴールシークの定義がワークシートやワークブックのどこかに記録・保持されていて、必要に応じてそれを呼び出すことができる、というようにはなっていないようです。
そういうことなら、ゴールシークを適用した後で、該当のセルを遠慮なく削除してかまわないということになります。
gs02.rb の場合、素の整数とその平方根だけ分かればいいということであれば、ゴールシークを適用したB列を削除してもいいわけです。
実際、gs02.rbにおいて「cell2.GoalSeek(……)」の次の行に、「cell2.Delete」と書き込むと、素の整数および逆算した平方根の二つの列からなる表ができます。
「計算式の記述とゴールシークの適用は使い捨てでかまわない」と割り切れば、計算式を書くためのセルを一つだけに固定して、一連の逆算処理が終了したらそれを削除する、ということでいいわけです。
長くなるのでここには掲げませんが、圧縮ファイルに同梱してある gs03.rb は、そのようなやり方のサンプルです。計算式を書くセルをH1一つだけにして、それを使い回します。
データテーブルのところで触れたPMT()関数は、利率、返済期間(というより返済回数)、返済総額の三つを引数として与えると、１回当たりの返済額を算出してくれます。gs03.rbでは、これをゴールシークに用います。
返済回数を36回(36ヶ月払いの３年間)に固定して、縦軸に年利 1％〜10％の10段階、横軸に返済月額 5万円〜10万円の６段階を取ります。そして、PMT関数によって返済総額(いわば借用可能限度額)を逆算します。
1％・5万円の時に借りることのできる最高額、2％・5万円の時の額、…… それから、1％・6万円の時の額、2％・6万円の時の額 …… とそれぞれを逆算します。
ゴールシークを60回実行することになりますが、それは、総てセルH1で行います。で、60回の処理が終了したら H1を削除します。
縦と横が入り乱れる感じで、スクリプトをたどるのは少々ややこしいかもしれませんが、やっていることは単純です。興味があったら覗いてみて下さい。
［補足］ 計算式にExcelの関数等を用いる場合の注意
ゴールシークが実行される際、与えられた計算式の変数に何か値を代入して試算するというのを繰り返すわけですが、その過程で、計算式の値として数値でないものが返った時は、エラーになるので注意が必要です。
例えば、0での割り算が発生した時は「#DIV/0!」が返されるので、ゴールシークもうまくいきません。
自分で計算式を書いたのであれば式を修正できますが、中がブラックボックス的な関数を利用する時は、「IF(……)」などを用いて工夫する必要があります。なかなか骨が折れますが。
PMT関数もゴールシークで利用する時は注意が必要です。返済期間(返済回数)を逆算するのは大変です。１回当たりの返済額を算出する際、返済回数が分母にくるため、ゴールシーク実行中に「#DIV/0!」が発生することを覚悟しなければなりません。
ゴールシークは、それなりに便利な機能ですが、本格的に使い込むのはなかなか大変、というのが率直な感想です。
(3) 計算精度の調整
ゴールシークは、与えられた計算式に適当に値を代入して試算し、その結果が目標値と違っていれば別の値を代入してみる…… という手順で処理を進めるようです。
しかし、無制限に処理を反復するようになっては困るので、一定の制約条件があります。
計算の反復回数が「最大反復回数」の値を超えたとき、または、試算結果の変動が「変化の最大値」に設定した数値より小さい変動になったとき、反復計算が停止されるようです。
「最大反復回数」は、VBAふうに書くと Application.MaxIterations で確認したり再設定できます。私のところでは 100 になっていました。
「変化の最大値」は、Application.MaxChange です。こちらは 0.001 でした。
「最大反復回数」をもっと大きな数にし、また、「変化の最大値」をもっと小さな数に設定し直せば、ゴールシークの精度が向上することになります。ただし、計算に要する時間が長くなります。
なお、「最大反復回数」も「変化の最大値」も、ゴールシークに限らず他の計算にも影響を及ぼすので、再設定する時は注意が必要です。
Excel操縦用ライブラリexlapで上の２つの値を参照する時は、wbがワークブックを指している場合、
wb.app.obj.MaxIterations
wb.app.obj.MaxChange
と記述します。
参考まで、この２つの値を表示するスクリプトを下に掲げておきます。
−−−− ここから
#! ruby -Ks
require "exlap"
filename = "test.xls"
Exlap.new(filename) do |wb|
p wb.app.obj.MaxIterations # => 100.0
p wb.app.obj.MaxChange # => 0.001
end
−−−− ここまで
以上、ゴールシークについて簡単な解説を書きました。
ゴールシークの場合は、計算式に盛り込むことのできる変数が一つだけですが、複数の変数を含む計算式において、目標値を達成する最適な変数(複数個)の値を模索する「ソルバー」というのもあります。
ソルバーは、Excelに標準装備されているものではなく、アドインとして提供される機能です。この利用は、なかなか複雑なので今回はパスします。
とりあえず What-If分析について一通りは終了ということで、ここまでにします。
〜 以上 〜
Copyright (C) T. Yoshiizumi, 2012 All rights reserved.
Keyword(s):
References: