hirax.net::Keywords::「ガウスザイデル法」のブログ

■エクセルの計算ワークシートをRuby計算スクリプトに変換してみよう　

　下記で使ったエクセルファイルは、自己参照部分がなく、つまり反復計算が必要ないものを使ってしまいました。反復収束計算が必要なエクセルファイルを「続　エクセルの計算ワークシートをRubyでC言語に変換してみよう」 でサーバ内に置きましたので、下記記事を読んだ後には、上記記事（さらにその後に続く記事など）を引き続きご覧ください。

---

　目的に対して達成方法があまりに過剰、あるいは、あまりに的外れで荒唐無稽なものというのは、それはそれで何だか面白いような気がします。

　今日は、そんなものを、ふと、けれど無性に作ってみたくなりました。

　先日、Microsoft Excelのような「表計算ソフト」スプレッドシートを使った「静電界計算や非定常熱伝導シミュレーション計算」をみっちりしてきました。離散化された物理式を表計算ソフトウェアを用いて計算を行うというのは、「セル」というメッシュと空間が感覚的に近く感じられることから、とても自然にシミュレーション計算を行う空間分割と計算領域との対応を感じることができるのが、とても便利で良いと思っています。

　もちろん、セル間の計算を「反復計算」を行うことで、複雑なことを気にせず、計算が自然に終わってしまう、というのも実に「自然」で「お手軽」で良い、と思っています。

　とはいえ、私はエクセルは苦手です。毎年何回か、エクセルでのシミュレーション計算を（悩んでいる）人に教えるという作業をするのですが、それでもエクセルを使うのは苦手です。そこで、エクセルで作った離散化シミュレーション用.xlsシートをRuby Script（プログラム）に自動的に変換する、Rubyスクリプトを作ってみることにしました。そう、「エクセルで物理シミュレーションを学んだ人が、そのエクセルシートをそのまま多言語に移植することができる」スクリプトを作ってみよう、と考えてみたのです。

　作ったコードxls2rb.rbは次のようになります（近日中にサーバに置いて置きます）。これは、「反復計算を使った」エクセルファイルを読み込み、それをRubyスクリプトに変換するスクリプトです。「表計算ソフト」スプレッドシートを使いった「静電界計算や非定常熱伝導シミュレーション計算」は、四則演算と周囲の空間が持つ値の計算だけで行うことができます。だから、そういう「条件」下では、そんなスクリプトを作るのも比較的簡単です。初期化や反復計算をそれぞれ「関数」化しているとはいえ、変数はすべてグローバル変数ですし、そこら辺の「エクセル感」も再現してみたスクリプトです。

require 'win32ole'

def getAbsolutePath(filename)
  fso=WIN32OLE.new('Scripting.FileSystemObject')
  fso.GetAbsolutePathName(filename)
end

def getAlphabet(n)
  val=n.to_f/25.0 
  mod=n%25
  result=''
  result+=('a'..'z').to_a[(n-val)/25] if val>1
  result+=('a'..'z').to_a[mod]
end

def defFunc(state,book)
  src=''
  book.Worksheets.each do |sheet|
    y=1
    sheet.UsedRange.Rows.each do |row|
      x=0
      record=[]
      row.Columns.each do |cell|
        if state==:init &&cell.Value!=''
          record＜＜'  '+'$'+getAlphabet(x)+y.to_s+
            '='+cell.Value.to_s if cell.Value.to_s!=''
        end
        if state==:calc &&cell.Formula!=''
          t=cell.Formula.sub(/[=$]/,'')
          t=t.downcase.gsub(/([a-zA-Z]+\d+)/,'$\1')
          record＜＜'  '+'$'+getAlphabet(x)+y.to_s+'='+t
        end
        x+=1
      end
      src+=record.join("\n")+"\n" if record.join('').gsub("\n",'')!=''
      y+=1
    end
  end
  return src
end

filename=getAbsolutePath(ARGV[0])
excel=WIN32OLE.new('Excel.Application')
book=excel.Workbooks.Open(filename)
initsrc=defFunc(:init,book)
calcsrc=defFunc(:calc,book)
book.close
excel.quit
GC.start

puts ＜＜INIT
# autocreated ruby script from excel file
# jun hirabayashi jun@irax.net http://www.hirax.net

def init
#{initsrc}end

def calc
#{calcsrc}end

10.times do
  calc
end
INIT

　ちなみに、このスクリプトを

ruby xls2rb.rb ex.xls

という具合にして（引数は変換したいXLSファイルです）、実行すると、次のようなスクリプトができあがります。ちなみに、ここで使ったex.xlsファイルは、ラプラス方程式を3X3のセルに離散化し解く、実に単純なエクセルシートです。（この辺りのファイル一式を週末にでもサーバ上に置いておきます）

# autocreated ruby script from excel file
# jun hirabayashi jun@irax.net http://www.hirax.net

def init
  $a1=1.0
  $b1=2.0
  $c1=3.0
  $a2=1.0
  $b2=2.0
  $c2=5.0
  $a3=1.0
  $b3=2.0
  $c3=3.0
end

def calc
  $a1=1
  $b1=2
  $c1=3
  $a2=1
  $b2=2
  $c2=$b2+$c1
  $a3=1
  $b3=2
  $c3=3
end

10.times do
  calc
end

　つまり、今日私が作ったスクリプトの動作は、エクセルが最初に行う初期化ルーチンを"init"関数として定義（作成）し、次に行う反復計算を"calc"関数として定義（作成）し、それを（適当に決めた）10回繰り返すスクリプトを作り出す、という具合です。ポイントは初期化時には、cell.Valueを用いることで初期数値を設定し、逐次計算時にはcell.Formulaを使うことで数式を使う、という「使い分け」になります。反復計算自体は、ガウスザイデル法によって行われます。

　あとは適当に、知りたい値を出力する（たとえば、"puts $c2"といった）出力処理文でも書き足せば、はい、シミュレーション・rubyプログラムのできあがり、というわけです。

離散化された物理計算をするために作成したエクセル・シートを、Rubyスクリプトに自動で変換し、好きに加工できるプログラム…って便利なような、そもそもそんな用途ってある訳ないような…というのが正直で的確な感想だと思います。つまり、それはかなり無意味なツールです。

　他の言語に置き換えて、高速化指向の変換プログラムを書いてみるのも（一回くらいはやってみようと思いますが、今ひとつ「魅力」に欠けるように思います。

　今日作った、「離散化された物理現象を計算するために作成したエクセル・シートを、Rubyスクリプトに自動で変換するプログラム」なんていうものを、（何だか馬鹿馬鹿しいけれど）意外に少し面白い笑える、そう思える人が一人でもいたら、幸いに思います。

■名前付きセルのあるエクセルのワークシートをC++言語プログラムに変換してみよう　

これまで「続　エクセルの計算ワークシートをRubyでC言語に変換してみよう」や「エクセルの計算ワークシートをRuby計算スクリプトに変換してみよう」で、エクセルの表計算シートを他の言語のプログラムに変換する、なんていうことをしてきました。ここで想定している「エクセルの表計算シート」というのは、「エクセルで作った離散化シミュレーション用.xlsシート」を主眼に置いています。つまり、何らかの方程式を差分化し、エクセルシート上でその差分化された空間を表現し、反復収束計算ことで求めたい結果を得る、そんなためのエクセルシートを主眼に置いています。

　ところで、そんな風にエクセルシートを使うときには、セルに名前をつけたくなります。たとえば、真空の透磁率をA1セルに入れたら、そのセルを"A1"セルでなく、"eps"なんていう名前で参照したくなります。そこで、たとえばこんな風にセルに名前をつけます。すると、そのセルの値を、"A1"というようにも参照できるのに加えて、"eps"なんていう名前でもアクセスすることができるようになります。そうすれば、他のセルでは"=eps*4"なんていう風に数式を入力することができるわけです(参考ビデオ）。

　そこで、今回は名前付きセルを使ったエクセルのワークシート（簡単のために、一枚目のシートだけを使ったエクセルブックを前提にしています）をC++言語プログラムに変換するRubyスクリプトを書いてみました。変換用のRubyスクリプトや、名前付きセルを使った（ラプラス方程式を解く）エクセルシートや、エクセルシートから変換・作成されたC++言語ソースファイルは、ここに置いておきました。このサンプルのエクセルファイルでは、中心のセルの値を変化させるために入れるセルに"centerVal"という名前を付けています。そのため、変換されたC++ソースには、

  C3=centerVal;

といった記述が現われています。エクセルでは、「名前」はセルにではなくブックの直下で管理されているので、Ruby変換スクリプトでは、

names={}
book.names.each do |name|
  cell=$1.gsub('$','') if /!([^!]+)$/=~name.RefersTo
  names[cell]=name.name
end

といったようなコードにしてあります。

　名前付きセルを使ったエクセルシートを使い物理定数や中間変数などをわかりやすく表現した上で、離散化シミュレーションをシート上の「いかにもわかりやすく「差分化された空間で」実感した後に、（その自分自身が作ったスプレッド・シートを元に）変数の名前が（少なくともA1とかD4とかいう名前よりは）わかりやすいシミュレーションプログラムが自動生成されるとしたら、つまり、自分自身がスプレッド・シート上で作ったものが、C++言語の（もちろん他の言語でも）プログラム・ソースとして眺めることができるとしたら、…これって結構面白いと思いません？