rubyによるExcel操縦あれこれ　〜　シナリオの利用

rubyによるExcel操縦あれこれ　〜　シナリオの利用

最終更新日： 2011/05/05

Excelのシナリオの利用について記します。

snr_test.zip には、関連のサンプルスクリプトなどが含まれています。

　以降で掲げるサンプルスクリプトを私が実行した環境は次のとおり。

• MS-Windows xp | vista
• Excel2002(Office xp版) | Excel2007
• ruby ver 1.8.7 (ver 1.9系でもおそらく大丈夫)
• 使用ライブラリ: 拙作exlap.rb v1.1

　　　　

なお、rubyがインストールされていない環境でサンプルスクリプトを実行したい方は、exl.exeをお試し下さい。詳しくは exl.exeの使い方 を参照して下さい。

---

• ＜はじめに＞
• 1.　単純なシナリオの作成
• (1) シナリオの登録
• (2) シナリオの要約レポートを表示する(CreateSummary)
• (3) シナリオの要約レポートをピボットテーブルで表示する
• 2.　各シナリオの表示結果の保存
• (1) ワークシートのコピーによる保存
• (2) ChangingCellsをずらしてシナリオを総て表示する(ChangeScenario, Merge)
• 3.　２種類のシナリオを組み合わせて使う
• (1) ２種類のシナリオの組合せパターン一つを表示する
• (2) ２種類のシナリオの組合せパターン総てを表示・保存する

---

　Excelのシナリオは、どの欄に何を書き込むかの書式が決まっている場合に、「何を書き込むか」のパターンを予めいくつか登録しておいて、そのうちの注目パターンを必要に応じて表示させる仕組みです。

　例えば、第１行目に科目、収入、支出、利益の４つの見出しがあり、その下の第２行目の４つのセル(A2:D2)にそれぞれの値を書き込むようになっているとします。ただし、D2の「利益」は、「収入 マイナス 支出」の計算式で求めるので実際には書き込み不要。

　この場合、科目、収入、支出の３項目の値をシナリオとして複数セット登録しておきます。登録されたデータは、Excelが内的に記録・保持します。

　そして、そのうちの１つを呼び出すと、A2:C2に該当の値がセットされて、D2の「利益」の値も自動的に計算され、結果がワークシート上に表示されます。

　シナリオを複数セットの内の別のものに切り替えることによって、異なる表示結果を得ることができます。こうした仕組みがExcelのシナリオです。

　なお、シナリオを扱う場合は、書式における「どの欄に」のことをChangingCells(変化させるセル)、「何を書き込むか」のことをValues(値)といいます。

--------

　これから個人経営事業を始めるつもりで、どの事業にするか検討中だとします。

　事業として考えているのは、飲食店事業、通信販売事業、塾経営事業の３つです。それぞれの事業について収支計画書を作成してみたとします。

　計画書に盛り込んだ項目は、基本的に収入と支出の２つだけです。あと、その２つから差額の利益を算出します。

　正確には次の４つの項目を立てます。

1. 　事業(その名前)
2. 　収入(金額)
3. 　支出(金額)
4. 　利益(金額：計算式)

　利益は、「収入 マイナス 支出」の計算式で求めます。セルに計算式を書き込みます。なので、シナリオには登録しません。

　それ以外の(1)〜(3)をシナリオとして登録することにします。

　シナリオは、事業の数だけ用意するので３つ登録します。

　１つのシナリオ(飲食店事業)について表示したイメージは下のようになります。項目が横に並ぶ形にしました。

2011年度　事業別収支計画書
事業	収入	支出	利益
飲食店	20,000,000	11,300,000	8,700,000

　第１行目(A1欄)はタイトルです。

　第２行目のA2:D2が見出し、３行目のA3:D3が該当の値をセットする欄です。

　「利益」のセルD3には計算式が書き込まれています。

　「事業、収入、支出」のA3:C3には、シナリオから読み込まれた値がセットされます。この３つのセルがChangingCells(変化させるセル)です。

　以下、シナリオを扱う方法について記します。

(1) シナリオの登録

　ssがワークシートオブジェクトである場合、１つのシナリオは次の記述で登録できます。

   ss.Scenarios.Add({……})

　このAddメソッドに渡す引数はハッシュですが、とりあえず１つの例を掲げると次のようになります。

   ss.Scenarios.Add({
     'Name'=>"飲食店のシナリオ",
     'ChangingCells'=>ss.Range("A3,B3,C3"),
     'Values'=>["飲食店", 20000000, 11300000],
     'Comment'=>"飲食店事業の見込み",
     'Locked'=>true,
     'Hidden'=>false})

　引数であるハッシュの意味は次のとおり。

• Name: シナリオの名前
  

　後でシナリオを表示したりする時に、この名前を手がかりにする。

• ChangingCells: 変化させるセル
  

　登録されているシナリオの値をどのセルに書き込むかの指定。
　サンブルでは "A3,B3,C3" の３つのセルが指定されている。この３つのセルに、登録されている値が入ることになる。
　サンプルの３つのセルは、たまたま連続している領域なので "A3:C3" と書くことができるが、シナリオを扱う場合、連続しているとは限らないので、カンマで区切るのが一般的。

• Values: 代入する値のセット
  

　代入する値のセットを配列で与える。
　サンプルでは３つの値をセットしている。ここで設定した３つの値は、先の ChangingCells で指定した３つのセルにそれぞれ書き込まれる。

• Comment: シナリオに付ける注釈
  

　適当なものでかまわないが、後で「シナリオの要約レポート」を表示させた時にその中に盛り込まれるので、簡潔かつ参考になるものにする。最長半角255文字まで。
　長めになる時は、改行コードを含めて１行の長さを抑えた方がいいかもしれない。
　省略すると、「作成者:xxx 日付:2011/4/30」などのようになる。作成者は、通常、Excelインストール時の登録ユーザー名。

• Locked: シナリオのロックの有無
  

　これをtrueにすると、シナリオにロックがかかり変更できなくなる。falseならワークシートを保護しているときでも変更可能。既定値は true。

• Hidden: 表示／非表示の指定
  

　これを true にすると、シナリオが非表示になる。既定値は false。

　今は３つの事業についてそれぞれシナリオを登録するので、ss.Scenarios.Addの記述を３回行うことになります。

　サンプルの snr01.rb は、シナリオ登録を３回行って、その後で飲食店事業のシナリオ１つを表示しています。

　ChangingCellsについては、A3とかB3などの番地で扱うのでなく、「名前付き範囲」の名前を付けて扱うようにしています。「名前付き範囲」といっても、１つのセル各々にそれぞれ名前を付けています。なので「範囲」というと違和感がありますが、手法は「名前付き範囲」の名前付けです。

　以下、少々長くなりますがスクリプトを掲げます。

   −−−−　ここから (snr01.rb)
   #! ruby -Ks
     # シナリオ作成とその表示
   require "exlap"
   
   labels = %w(事業 収入 支出 利益)  # 見出しの名前
   cell_names = labels
   
   filename = "test01.xls"
   Exlap.new(filename) {|wb|
     ss = wb.fes  # 空のワークシートを選択
     ss.Name = "事業別収支計画書"
     ss[1,1] = "2011年度　事業別収支計画書"  # A1欄にタイトルを書き込む
     ss.rr(1,1, 1,labels.size).MergeCells = true  # セル結合
         # 第２行目に見出しを書き込む
     ss.rr(2,1, 2,labels.size).Value = [labels]
         # 第３行目の変化させるセルに名前を付ける(名前付き範囲の定義)
     cell_names.each_with_index {|name, i|
       ss.cell(3,i+1).Name = name
     }
         # セルに関するその他の設定
     ss.Range("利益").Value = "=収入-支出"  # 計算式
     %w(収入 支出 利益).each {|name|
       ss.Range(name).NumberFormatLocal = "#,##0"  # 数値の表示形式
     }
   
         # ３つのシナリオを登録
     changing_cells = ss.Range("事業,収入,支出")
     ss.Scenarios.Add({
       'Name'=>"飲食店のシナリオ",
       'ChangingCells'=>changing_cells,
       'Values'=>["飲食店", 20000000, 11300000],
       'Comment'=>"飲食店事業の見込み",
       'Locked'=>true,
       'Hidden'=>false})
   
     ss.Scenarios.Add({
       'Name'=>"通信販売のシナリオ",
       'ChangingCells'=>changing_cells,
       'Values'=>["通信販売", 12000000, 6680000],
       'Comment'=>"通信販売事業の見込み",
       'Locked'=>true,
       'Hidden'=>false})
   
     ss.Scenarios.Add({
       'Name'=>"塾経営のシナリオ",
       'ChangingCells'=>changing_cells,
       'Values'=>["塾経営", 30000000, 20520000],
       'Comment'=>"塾経営事業の見込み",
       'Locked'=>true,
       'Hidden'=>false})
   
     ss.Scenarios(1).Show  # 第１のシナリオを表示
     ss.range_autofit  # セルの幅と高さを自動調整
     wb.save
   }
   −−−−　ここまで

　シナリオの登録方法については、前述したとおりです。

　「名前付き範囲」の定義では、A3に「事業」、B3に「収入」、C3に「支出」、D3に「利益」という名前を付けています。

　このように定義したことにより、計算式を

   ss.Range("利益").Value = "=収入-支出"

と書くことができます。これを番地形式で書くなら

   ss.Range("D3").Value = "=B3-C3"

となります。

　また、ChangingCellsについては

   changing_cells = ss.Range("事業,収入,支出")

としていますが、これを番地形式で書くと

   changing_cells = ss.Range("A3,B3,C3")

と書くことができます。

　ちなみに、この changing_cells の値として、R1C1形式で番地を指定する場合は、次のように文字列を与えることができます。

   changing_cells = "#{ss.Name}!R3C1,R3C2,R3C3"

　今回のケースでは、ワークシート名を省略して

   changing_cells = "R3C1,R3C2,R3C3"

としても大丈夫です。

　シナリオの表示は、

   ss.Scenarios(1).Show

で行っています。シナリオを番号でなく名前で指定することもできます。つまり

   ss.Scenarios("飲食店のシナリオ").Show

と書くことができます。

　これは、登録されているうちの１つのシナリオを表示するものですが、３つのシナリオを総て表示する方法については、事項で述べます。

(2) シナリオの要約レポートを表示する(CreateSummary)

　先の snr01.rb では、３つのシナリオを登録しましたが、表示したのは１つのみでした。

　３つとも表示する場合、

   ss.Scenarios(1).Show
   ss.Scenarios(2).Show
   ss.Scenarios(3).Show

と３行を記述すればいいかというと、そういうわけにはいきません。

　というのは、３つのシナリオとも ChangingCells が同じなので、シナリオを切り替えると、同じ箇所のセルの値が変化するだけで、前のものが消えてしまいます。したがって、上の３行の実行結果は、最終的に

   ss.Scenarios(3).Show

という１行の実行結果と同じです。

　ＧＵＩ操作している最中であれば、シナリオを切り替えて、各々の表示結果を画面上で確認しながら進めることができますが、スクリプトで一括処理する場合は、工夫が必要になります。

　登録されているシナリオを総て表示させる一つの方法は、その要約レポートを表示させることです。

　これは、CreateSummaryメソッドで行いますが、スクリプトの記述の前に、要約レポートのイメージを下に掲げてみます。

シナリオ情報
		現在値:	飲食店のシナリオ	通信販売のシナリオ	塾経営のシナリオ
			飲食店事業の見込み	通信販売事業の見込み	塾経営事業の見込み
変化させるセル:
	事業	飲食店	飲食店	通信販売	塾経営
	収入	20,000,000	20,000,000	12,000,000	30,000,000
	支出	11,300,000	11,300,000	6,680,000	20,520,000
結果出力セル:
	利益	8,700,000	8,700,000	5,320,000	9,480,000

　上の要約レポートをみると、登録されているシナリオの情報が一通り分かります。

　上に出てくる「現在値」は、Showで表示されているシナリオの値を意味します。

　スクリプト中では「飲食店のシナリオ」をShowで表示しているので、それが「現在値」になっています。

　「飲食店」に関する「事業、収入、支出、利益」が横方向に２回繰り返し出てくるのは、「飲食店のシナリオ」が「現在値」になっているからです。

　なお、この要約レポートは、シナリオが登録されているワークシートの前に新たなワークシートが設けられ、そこに書き出されます。ワークシート名は「シナリオ情報」です。

　この要約レポートの表示を実現するためのスクリプト記述は、次のとおりです。シナリオの登録までは snr01.rb と同じです。該当箇所のみ記します。

   −−−−　ここから (snr01_2.rb 抜粋)
     ss.Scenarios.CreateSummary({
       'ReportType'=>XlStandardSummary,
       'ResultCells'=>ss.Range("利益")})
   −−−−　ここまで

　CreateSummaryの引数はハッシュですが、次の２つを指定できます。

• ReportType: 要約レポートの種類
  

　種類には次の２つがある。
　標準タイプ： XlStandardSummary
　ピボットテーブル： XlSummaryPivotTable

• ResultCells: 結果出力セル
  

　要約レポートの種類が標準タイプの場合、特に指定しなくても「変化させるセル」(サンプルではA3:C3)の値は表示される。
　それに加えて、「結果出力セル」(サンプルではD3の「利益」)の値も表示したい時は、それをRange形式で指定する。
　要約レポートの種類がピボットテーブルの場合は、「変化させるセル」と「結果出力セル」の区別なく、その両方から表示したいセルを指定する。やはりRange形式で指定。

　「変化させるセル」と「結果出力セル」に名前を定義していないと、該当のセルが「$A$3」などのように番地で表示されます。

　それだと見ても分かりにくいので、要約レポートの表示を行う時は、サンプルで行っているように、該当のセルに名前を付けておくのが望ましいといえます。

(3) シナリオの要約レポートをピボットテーブルで表示する

　サンプルの snr01_2.rb には、標準タイプの要約レポートの他に、ピボットテーブルの要約レポートを表示するための記述も含まれています。

　その該当箇所を下に掲げます。

   −−−−　ここから (snr01_2.rb 抜粋)
     ss.Scenarios.CreateSummary({
       'ReportType'=>XlSummaryPivotTable,
       'ResultCells'=>ss.Range("収入,支出,利益")})
   −−−−　ここまで

　CreateSummaryメソッドについては既に述べたとおりです。特に付け加えることはありませんが、ResultCellsの指定の仕方が標準タイプの時と違っていることに留意する必要があります。

　標準タイプの場合はデフォルトで「変化させるセル」の値が表示されるのに対し、ピボットテーブル形式の場合は、意図的に指定しないと表示されません。そのため

   'ResultCells'=>ss.Range("収入,支出,利益")

としています。「利益」だけでなく、「収入」と「支出」も指定している点が標準タイプの時と異なります。

　もう１つの項目「事業」を指定していないのは、「飲食店のシナリオ」などの各シナリオの名前がデフォルトで表示されるので、省略しても大丈夫と考えたからです。

　また、実際に試してみると分かりますが、「事業」を指定に加えると、その値である名称ではなく個数(数値１)が表に盛り込まれます。意図したようにはなりません。

　なお、ピボットテーブルが作成される新設のワークシートの名前は、「シナリオ ピボットテーブル」です。

　ピボットテーブルの表示結果は次のとおりです。中核部分を載せます。

行ラベル	収入	支出	利益
飲食店のシナリオ	20,000,000	11,300,000	8,700,000
塾経営のシナリオ	30,000,000	20,520,000	9,480,000
通信販売のシナリオ	12,000,000	6,680,000	5,320,000

　シナリオの表示順がその登録の順番と違っています。登録順であれば、通信販売のシナリオが塾経営のシナリオよりも上になるはずです。

　この辺の調整は、もちろん可能ですが、少々不満の残るところです。

　なお、シナリオ登録順に調整したい時は、CreateSummaryメソッドの記述を終えた直後(「wb.save」の直前)に下の６行を挿入します。この６行に関する説明は省略しますが、必要ならピボットテーブルに関する解説を参照して下さい。

   −−−−　ここから
     ss2 = wb.ss("シナリオ ピボットテーブル")
     ss2.Activate
     ptfr = ss2.PivotTables(1).RowFields(1)
     ss.Scenarios.each {|snr|
       ptfr.PivotItems(snr.Name).Position = snr.Index
     }
   −−−−　ここまで

　もっと簡便な「シナリオ登録順に調整するためのノウハウ」があるのかもしれませんが、私に思い浮かんだのは上の方法くらいです。

--------

　ここでは、各シナリオの表示結果を保存する方法について記します。

　材料とするのは、前項と同じ「飲食店のシナリオ」など３つのシナリオです。その表示結果を保存することを考えます。

(1) ワークシートのコピーによる保存

　まず、各々のシナリオの表示結果を別のワークシートに保存する方法について記しておきます。

　方法といっても、新たなワークシートを設けて、シナリオが表示されているシートをまるごとコピーするだけです。snr02.rb にその方法が書かれています。

　シナリオの表示とシートのコピーに関する部分を下に掲げます。シナリオの登録までは snr01.rb と同じです。

   −−−−　ここから (snr02.rb 抜粋)
     ss.Scenarios.each {|snr|
       snr.Show  # １つのシナリオを表示
       ss.range_autofit  # セルの幅や高さを自動調整
       ss.Copy({'Before'=>ss})  # 現シートの前に複製シートを新設
       ss2 = wb.offset_sheet(ss, -1)  # 新設の複製シートのオブジェクトを得る
       ss2.Name = snr.Name
     }
   −−−−　ここまで

　シートのコピー「ss.Copy(……)」で、'Before' となっているところを 'After' とすれば、現シートの直後に複製シートを新設することになります。

(2) ChangingCellsをずらしてシナリオを総て表示する(ChangeScenario, Merge)

　先に述べたのは、各シナリオの表示結果を別々のワークシートにコピーする方法でした。

　しかし、今回のような簡単なシナリオであれば、わざわざ別シートにするまでもないというのが実感です。３つのシナリオを並べて表示するだけでいい、と思うのが自然です。

　つまり、第２行目に飲食店のシナリオ、３行目に通信販売のシナリオ、４行目に塾経営のシナリオを表示すればいいわけです。

　要約レポート(ピボットテーブル版)がそれに当たるといえますが、ピボットテーブルを使わなくても、各シナリオのChangingCellsをずらすことで、３つを並べて表示できます。

　既に登録してあるシナリオの ChangingCells と Values を変更するには、ChangeScenarioメソッドを使います。

   ss.Scenarios(2).ChangeScenario(changing_cells, values)

のように書きます。

　ChangingCellsがずれていれば、３つのシナリオをそれぞれ表示させた時に、３つとも見える形で表示されます。

　このやり方で３つのシナリオを表示させているのが snr02_2.rb です。

　オリジナルのシナリオに変更を加えるのは避けます。

　ワークシートのコピーで次シートに複製を設けます(タイトル、見出し、計算式がコピーされます)。

　そこに３つのシナリオもコピーします。それにはScenarios.Mergeを用います。

　そして、ChangeScenarioでそれら３つのシナリオを変更しつつ表示させます。

　サンプルスクリプトの該当箇所は次のとおり。

   −−−−　ここから (snr02_2.rb 抜粋)
         # ワークシートをコピー
     ss.Copy({'After'=>ss})  # 現シートの次に複製シートを新設
     ss2 = wb.offset_sheet(ss, 1)  # 新たな複製シートのオブジェクトを得る
     ss2.Name = "複製シート"
     if ss2.Scenarios.Count == 0
       ss2.Scenarios.Merge(ss)  # シナリオを読み込む
     end
   
         # ２番、３番のシナリオのChangingCellsを変更しつつ表示
     ss2.Scenarios.each {|snr|
       i = snr.Index
       if i > 1  # ２番目、３番目のシナリオの変更
         old_changing_cells = snr.ChangingCells
         values = snr.Values
         changing_cells = ss2.rr(i+2,1, i+2,3)
         snr.ChangeScenario(changing_cells, values)
         old_changing_cells.Copy(changing_cells)  # 書式も含めてコピー
         ss2.cell(i+2,4).Value = "=RC[-2]-RC[-1]"  # 「利益」欄の設定
         ss2.cell(i+2,4).NumberFormatLocal = "#,##0"
       end
       snr.Show
     }
     ss2.range_autofit
   −−−−　ここまで

　シナリオが登録されているワークシートオブジェクトは、ssです。それを複製したシートがss2です。

　ssに登録されているシナリオをss2にコピーするための記述が

   ss2.Scenarios.Merge(ss)

です。Mergeの引数にはワークシート名を使うこともできるので

   ss2.Scenarios.Merge(ss.Name)

と書いても同じ結果が得られます。

　Mergeは、単なるコピーではなく併合です。ss2にシナリオが登録されていれば、それとssのシナリオを併合することになります。

　サンプルの場合はss2にシナリオが何も登録されていないので(＊)、結果的に単なるコピーです。

（＊）　ワークシートをコピーした場合、Excel2007ではシナリオがコピーされないのに対し、Excel2002ではシナリオも含めてコピーされます。そこで、サンプルでは、コピー先にシナリオがあるかどうかをCountプロパティで確認して、ない時にMergeを実行するようにしています。

　シナリオの変更部分については、まず、１番目のシナリオを変更する必要はないので

   i = snr.Index
   if i > 1
     …………
   end

として、シナリオのIndex(番号)が1を超える場合に変更を実行するようにしています。

　その後の

     changing_cells = snr.ChangingCells
     values = snr.Values
     changing_cells = ss2.rr(i+2,1, i+2,3)
     snr.ChangeScenario(changing_cells, values)

という４行は、既存シナリオのValuesを変数 values に記録し、次に ChangingCells の新しい値を変数 changing_cells にセットしています。そして、ChangeScenarioメソッドでそれらを変更しています。

　changing_cellsの値を数値で設定しているので分かりにくいかもしれませんが、iが2の時は ss2.Range("A4:C4")、iが3の時は ss2.Range("A5:C5") です。

　ChangeScenarioメソッドの第１引数 changing_cells は省略できませんが、第２引数の values の方は省略できます。

　ただし、省略した時は change_cells で指定されたセルに実際に書き込まれている値がセットされます。オリジナルのシナリオのValuesが引き継がれるわけではありません。

　そのため、サンプルではオリジナルのValuesの値を変数valuesにセットして、それを引数として渡しています。

　以上、各シナリオの表示結果を保存する方法について記しました。

　他にも方法はあると思いますが、このテーマはこの辺にしておきます。

--------

　これまで「飲食店のシナリオ」など３つのシナリオを扱うサンプルを取り上げてきました。

　これに加えて、副業も行うことを考えたとします。副業の候補は、副業Ａ、副業Ｂ、副業Ｃの３つです。

　この３つの副業についてもそれぞれシナリオを登録します。項目は本業と同じく「事業、収入、支出、利益」の４項目ですが、「利益」は計算式で求めるのでシナリオには登録しません。

　本業と副業を合わせて６個のシナリオを登録するわけですが、両者の組合せは３×３＝９通りです。つまり、６個を材料として、９通りの表示結果を得ることができます。

　これでも実務レベルにおけるシナリオ活用に比べると単純ではありますが、シナリオの便利さを多少は実感できるのでは？と思います。

　この項では、少し込み入ったシナリオ書式情報等をどのように読みやすいスクリプトにするかがテーマです。

(1) ２種類のシナリオの組合せパターン一つを表示する

　まず、本業と副業の１つの組みを表示した時の表のイメージを決める必要があります。いわば書式の設定です。

　第１行目はタイトル、２行目が４項目の見出し、３行目が本業のデータ、４行目は副業データ、そして、５行目を合算の結果という形にしてみます。

　合算の結果は、総て計算式で求めることができるのでシナリオには登録しません。

　「飲食店のシナリオ」と「副業Ａのシナリオ」を組み合わせた表示結果を示してみます。

2011年度　事業別収支計画書
事業	収入	支出	利益
飲食店	20,000,000	11,300,000	8,700,000
副業Ａ	1,500,000	100,000	1,400,000
合算	21,500,000	11,400,000	10,100,000

　上の表のうち、「飲食店」と「副業Ａ」という２つの事業の名前は、シナリオから読み込んだものです。

　また、基本的に数値もシナリオから読み込みます(計算式で求める「利益」と「合算」の関係は除く。)。

　それらシナリオから読み込む箇所以外は、予めセルにデータを書き込んでおく必要があります。

　今回は、５行に渡る多くのデータを扱うので、セルに書き込む見出しや計算式を一度CSVで記述し、それを配列に変換する方法を採ります。

　具体的には次のとおりです。

   −−−−　ここから
   cell_values_format = <<EOV
   2011年度　事業別収支計画書
   事業,収入,支出,利益
   ,,,=収入-支出
   ,,,=副業収入-副業支出
   合算,=収入+副業収入,=支出+副業支出,=利益+副業利益
   EOV
   cell_values = CSV.parse(cell_values_format)  # csvを配列に変換
   −−−−　ここまで

　上のスクリプトが実行されると、配列 cell_values に５行分のデータがセットされます。これは配列の配列で、いわば２次元配列です。

　CSV.parse() は、rubyに標準で添付されているcsvライブラリが提供するメソッドです。文字列を配列に変換してくれます。

　この２次元配列を実際にセルに書き込むスクリプトは、次のとおりです。

   −−−−　ここから
   cell_values.each_with_index {|list, r|
     list.each_with_index {|val, c|
       ss.Cells.Item(r+1, c+1).Value = val  if val.to_s != ''
     }
   }
   −−−−　ここまで

　セルに付ける名前(名前付き範囲の定義)についても、同じようにCSVで記述して、それを配列に変換します。

　スクリプトでは下のように記述します。

   −−−−　ここから
   cell_names_format = <<EON
   タイトル
   ラベル
   事業,収入,支出,利益
   副業,副業収入,副業支出,副業利益
   合算,合算収入,合算支出,合算利益
   EON
   cell_names = CSV.parse(cell_names_format)  # csvを配列に変換
   −−−−　ここまで

　このようにして２次元配列を用意しておけば、cell_valuesの時と同じように容易にワークシートにセットできます。

　なお、上に出てくる「タイトル」と「ラベル」は、実は不要です。単に空白行にしたくないと思って載せたものです。これを消去して空白行にしても差し支えありません。

　ついでといっては何ですが、シナリオに登録するデータもCSV形式で記述することにします。

　Comment, Locked, Hidden は、指定せずにデフォルトのままにします。

　ChangingCells は、本業用と副業用の２種類あれば足りるので、CSVには盛り込みません。

　残るのは Name, Values の２つですが、これをCSVの形で記述します。

　１つのシナリオ(CSVの１行)の最初の要素がName、残りがValuesであるとみなすことにします。

　具体的には次のような形です。

   −−−−　ここから
   scenario_data1 = <<EOS1
   飲食店のシナリオ,飲食店,20000000,11300000
   通信販売のシナリオ,通信販売,12000000,6680000
   塾経営のシナリオ,塾経営,30000000,20520000
   EOS1
   −−−−　ここまで

　以上のことを前提として、６個のシナリオを登録し、「飲食店のシナリオ」と「副業Ａのシナリオ」の組みを表示させているのが snr03.rb です。

　CSVによる記述は既にかなり載せたので、Excel起動後の部分を下に掲げます。

　なお、シナリオに登録するデータのうち、本業関連は scenario_group[0], 副業関連は scenario_group[1] にセットするようにしました。いわばグループ分けした形です。こうしておくと、後で総ての組合せを表示するのが楽になります。

   −−−−　ここから (snr03.rb 抜粋)
   filename = "test03.xls"
   Exlap.new(filename) {|wb|
     ss = wb.fes  # 空のワークシートを選択
     ss.Name = "事業別収支計画書"
         # セルに名前を付ける
     cell_names.each_with_index {|list, r|
       list.each_with_index {|name, c|
         ss.Cells.Item(r+1, c+1).Name = name  if name.to_s != ''
       }
     }
         # セルの値をセットする
     cell_values.each_with_index {|list, r|
       list.each_with_index {|val, c|
         ss.Cells.Item(r+1, c+1).Value = val  if val.to_s != ''
       }
     }
     ss.rr(1,1, 1,cell_values[1].size).MergeCells = true  # タイトル部のセル結合
         # セルに関するその他の設定
     (%w(収入 支出 利益) +
      %w(副業収入 副業支出 副業利益) +
      %w(合算収入 合算支出 合算利益)).each {|name|
         ss.Range(name).NumberFormatLocal = "#,##0"  # 数値の表示形式
     }
   
         # シナリオの登録
     changing_cells = [ss.Range("事業,収入,支出"),
         ss.Range("副業,副業収入,副業支出")]
     scenario_group.each_with_index {|info, idx|
       info.each {|ary|
         name = ary[0]
         values = ary[1...ary.size]
         ss.Scenarios.Add({
             'Name'=>name,
             'ChangingCells'=>changing_cells[idx],
             'Values'=>values})
       }
     }
   
     ss.Scenarios("飲食店のシナリオ").Show
     ss.Scenarios("副業Ａのシナリオ").Show
     ss.range_autofit  # セルの幅と高さを自動調整
     wb.save
   }
   −−−−　ここまで

　changing_cells[0] が本業に関するChangingCellsで、番地で書くなら A3:C3 です。

　changing_cells[1] は副業に関するChangingCellsで、番地で書くと A4:C4 です。

　scenario_group は、その中に２次元配列を２つ内包しています。

　scenario_group[0] が本業のシナリオ情報、scenario_group[1] が副業のシナリオ情報です。

　なお、「values = ary[1...ary.size]」は、配列の２番目の要素から最後の要素までを変数valuesに代入するものです。ちなみに、最初の要素はシナリオの名前なので、変数nameに代入しています。

(2) ２種類のシナリオの組合せパターン総てを表示・保存する

　本業と副業の総ての組合せパターン(３×３の９通り)を表示して、各々の表示を別々のワークシートに保存することを考えます。

　前の項で既に、表示結果を別シートにコピーする方法を取り上げましたが、その応用です。snr03_2.rb がそのサンプルです。

　下に表示と保存に関する部分を抜粋しておきます。それより前の箇所は、snr03.rb と同じです。

   −−−−　ここから (snr03_2.rb 抜粋)
   scenario_group[0].each {|info1|
     scenario_name1 = info1[0]
     scenario_group[1].each {|info2|
       scenario_name2 = info2[0]
       ss.Scenarios(scenario_name1).Show
       ss.Scenarios(scenario_name2).Show
       ss.range_autofit  # セルの幅と高さを自動調整
       ss.Copy({'Before'=>ss})  # 現シートの前に複製シートを新設
       ss2 = wb.offset_sheet(ss, -1)  # 新設の複製シートのオブジェクトを得る
       ss2.Name = scenario_name1 + "＆" + scenario_name2
     }
   }
   −−−−　ここまで

　以上、２種類のシナリオを組み合わせて表示するケースを取り上げました。

　今回は、本業と副業が同じ項目からなる並列の関係なので単純な処理で済みましたが、実務では、例えば、「支出」を固定費と変動費に分けて記載し、その組をシナリオとして登録するといったケースがあります。細分化するほど書式が複雑になって、シナリオの扱いが面倒になります。

　といっても、書式を決めて、各セルに名前を付け、Name, Values あるいは ChangingCells のシナリオ情報を設定する、という処理の仕方は snr03.rb のものを応用できると思います。

　CSVでなく、タブ区切りテキストを用いるとか、場合によってはExcelのワークシートを用いるといったこともあると思いますが、基本的な進め方は同じだと思います。

　なお、ChangingCellsもCSVの中に書き込んで、また、グループの種類が３個以上になった時も総ての組合せパターンを表示できるようにした snr03_3.rb を同梱しておきます。

　それから、要約レポートを表示するスクリプト snr03_4.rb も同梱しておきますので、よかったら参考にして下さい。

　その他、シナリオについてまだ述べていない事柄がいくつかあります。例えば、１つのシナリオを削除するDeleteメソッドなどです。

　それらについては、他のwebサイトなどを参照して下さい。

　ここでは私がよく用いる側面についてだけ紹介しました。不十分な説明ですが、参考になるところがいくらかでもあれば幸いです。

−　シナリオの利用　おわり　−

Copyright (C) T. Yoshiizumi, 2011 All rights reserved.