hirax.net::Keywords::「Excel」のブログ

■続　エクセルの計算ワークシートをRubyでC言語に変換してみよう　

　前回、「エクセルの計算ワークシートをRubyでC言語に変換してみよう」でサンプルに作ったエクセルのシートが反復収束計算をする必要がない（自己参照型のシートになっていなかったこと）に気づいたので、今日はエクセル・シートを作り直し、サーバ内に置いておきましたここに置いておきます。置いたエクセル・ファイル"ex2.xls"は、5×5のセルの周囲と中央のセルが固定条件で、それ以外の（5×5内の）セルが自分の上下左右のセルの平均の値をとるというラプラス方程式を差分化したエクセルシートです。そして、xls2c.rbがエクセル・ファイルをC言語ソースに変換するRubyスクリプトで、ex2.xlsをC言語に変換した結果ファイルが、ex2.cになります。

　元々の記事は、「とても無意味なこと・けれど技術発想的には何だか（的外れ具合が）面白いこと」をしよう、という考えのもとにやってみたのですが、非定常熱伝導計算の復習をしているうちに、非定常問題を解くシートに対しては、意外に使えるかもしれない、という気がしてきました。

　プログラムというものをまったく知らない人が、熱伝導方程式の物理的な意味を理解上で、表計算でシミュレーションをしてみた（習ってみた）あとに、（エクセルのセルと直感的に対応した）自動出力したＣ言語ソースをもとに「printfの使い方だけを習い」色んな計算をしてみれば、そして注目するセル＝箇所の時間変化・経過を出力させてみたりすれば、案外面白く感じるかもしれない・・・という風にも思ったのです。

　もちろん、こんな単純自動変換Cソースでは一瞬のうちに物足りなくなるはずなので、上のような時間を経ることで、プログラム言語を学ぼうとするまでの小さな「足がかり」「キッカケ」になるのではないか、という間違っていそうなそんなことをふと思ったのです。いわば、エクセルを入口に差分化シミュレーション・プログラムの世界に"Hello World"する、というわけです。ゼロから学ぶのと、何か叩き台があって、それをほんの少しづついじりながら学ぶのとでは、気楽さが全然違うのではないか、といったことを「後付けで」考えてみたりしたのです。

　ちなみに、エクセルシート→C言語　変換などで、配列を使わずにグローバル変数を使ったのは、まずは何より簡単な変換スクリプトにしたいということ、そして、変換後のC言語ソースをコンパイルしたときに、少しは早く動作すると良いなと考えて、そんな作りにしてみました。

■エクセルでシミュレーション　Vol.10　二次元非定常熱伝導問題シミュレーション＋P(ID)制御エクセルシートを作ってみよう　

　「エクセルでシミュレーション　Vol.9　二次元非定常熱伝導問題の温度変化グラフも作ってみよう」で、二次元非定常熱伝導問題を解くシートを使いながら（実用的範囲では三次元問題と大差がない）、ヒーターで回転するベルトを高温にした場合の、その回転ベルトの温度分布がどのように変化していくかを計算し、グラフ化し、さらにベルト中の一部分の温度を時系列的にモニターする機能を付けた、シートで遊んでみました。

　そこで、今回は、センシングしたベルトの温度を用いて、ヒーターの温度を簡単なPID制御を使ってフィードバック制御するエクセル・シートを作ってみることにしましょう。

PID制御とは、調整量を（現在の）出力値と目標値との差に比例＝Proportionalした量、その（過去からの）積分=Integralに応じた量、および（その瞬間に次にどのように変化するかという）微分=Differentialに応じた量にしたがって変える制御です。言いかえれば、現在（Proportional）・過去（Integral）・未来（Differential）の挙動に応じて、制御調整量を変えてやろうという制御です。比較的、古典的な制御手法ですが、現在でも、もっとも一般的な制御手法です。

　まずは、前回のエクセル・シートでヒーター部分を単純に（計算をさせ始めてから＝回転ベルトに対するヒーターを動かし始めてから）100℃にし続けた場合の計算過程を示してみます。つまり、何の制御もしない場合です。その計算結果が、下の動画です。上の動画で上に示したグラフが、回転ベルトの温度分布を示したグラフです（回転ベルトを切り開いたように温度分布を示しています）。動画中の下の折れ線グラフは（上のコンター図で灰色丸部の温度の時間変化を示したグラフです）。ちなみに、縦軸は0℃から150℃までで、横軸が時間軸です。

　下の折れ線グラフを見れば、上のコンター図で灰色丸で示した部分の、温度が時間に応じて上昇し、やがて100℃近くになっていることがわかります。

　さて、次にPID制御を行ってみることにしましょう。…とはいえ、最初は簡単のために、「灰色丸で示した部分の温度と目標調整温度である100℃との差をヒーターに足す（ネガティブ・日―ドばっく）」という「P=比例成分=現在の違い」だけを利用したP(ID)制御を行ってみます。つまり、現在の出力と目標出力の差異のみをヒーター出力に（適当な比率で）足し合わせてみるのです。言いかえれば、ベルトの温度が0℃なら、ヒーターを200℃くらいにすることで、急激にベルトを温め温度を調整し、目標温度100度と現在のベルト温度の差が小さくなってきたら、ヒーターの温度を110℃くらいに変えてやる・・・というような制御をしてみます。そんなシミュレーション計算を行ってみた結果が、下の計算結果になります。ちなみに、ヒーターの制御温度はエクセルのシートでB32セルで計算されています。また、灰色丸のセンサ取り付け部分（を示した）の温度を示す下の折れ線グラフは、先ほどと同じく、縦軸は0℃から150℃までの温度を示し、横軸が時間軸となっています。

　この結果動画を見るとわかるように、「P=比例成分=現在の違い」だけを利用したP(ID)制御（=積分成分と微分成分を使わない）制御では、ベルトの温度は早く立ち上がりますが、ベルトの温度は振動して、なかなかすぐに安定してくれません。しかも、早く温度を立ち上げようとすると、温度振動は大きくなってしまい、温度振動を防ごうとすると、なかなか温度が早く立ち上がってくれない、という相反する関係になっています。いわば、（ある程度、減衰がある）強いバネと弱いバネの振動と同じような現象が起きてしまいます。

　さて、それでは、一体どうしたら良い制御ができるのでしょうか。・・・せっかく、簡易にエクセルのような（表計算＝スプレッドシート・ソフトウェアを使った）熱伝導方程式を使ったシミュレーション計算を行うことができる環境があるわけですから、今回扱ったPID制御のような古典的な制御をもう少し復習してみようと思います。

　そして、せっかく熱伝導方程式を計算してみたりしているわけですから、そういった微分方程式（つまり現在から未来を示す式）を使うことで実現できる、最適化制御についても考えてみることにします。

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

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

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

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

  C3=centerVal;

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

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

■エクセルでのセルの「名前」管理　

「名前付きセルのあるエクセルのワークシートをC++言語プログラムに変換してみよう」でこう書きました。

　エクセルでは、「名前」はセルにではなくブックの直下で管理されている

　エクセルのワークシートをC++言語プログラムに変換するプログラムを書くとき、最初は、Cellオブジェクトが名前を保持している、たとえばNameなんていうプロパティがあるのではないかと思いこんでいました。けれど、プログラムを書いている途中で、「Cellオブジェクトは自分に付けられている名前情報を保持していない」ということに気づき、どこに名前情報が保持されているかを調べてみたら、エクセルのワークブック直下に、つまり"book.names"というような形で、Nameの情報を示す配列が格納されていたのでした。（セルはちなみに、”ブック”の下の”ワークシート”の下の”行”の下の”列”の下にいます）

　そのことに気付いた瞬間は、（エクセルのワークシートをC++言語プログラムに変換するプログラム）の「コードの量が増えそうだな」と思い暗い気分になりました。しかし、少し考え、エクセルを作る側から考えてみれば、「名前はワークブックが保持する作りにするよなぁ」と思ったのです。

　もしも、セルが名前を保持する作りにしていたら、エクセルの動き上、ちょっと面倒になってしまうからです。それは、たとえば、どこかのセルに"velocity"という名前を付けていたとして、（それと異なる）またどこかのセルで"=0.1*velocity"なんていう式を使っていたとします。すると、その"=0.1*velocity"という計算をするときに、（セルが名前を保持する作りにしていたら）その名前を保持するセルが見つかるまでセル検索を行わなければならないからです。

　もちろん、その検索自体は、「エクセルにおける循環参照時の計算順序」で書いたような「エクセルの計算プロセスの詳細」を考えてみれば、エクセルで「（複数ブック・複数シート内のすべての）計算」を一回するのにつき一回だけですむはずだと思いますが、それでも検索作業はかなり面倒でしょう。

　大体、セルが「自分がどういう名前で他のセルから呼ばれているか」なんて知る必要がないわけです。他のセルの名前を知りたいことはあっても、自分の名前なんかどうでも良いのです（今回、私が書いたようなプログラムを書くのでなければ、ですね）。だから、エクセルでのセルの「名前」オブジェクトをブック直下に置くようにするようにするのは、当然の作りです。

　と、エクセルの作りに納得しつつ、（もちろん、自分のプログラムを書くのには、少し面倒だなぁと思いながらも）前回書いたような

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

　ちなみに、蛇足ですが、上記コードはbookがnameオブジェクトの配列を保持していて、nameオブジェクトはnameという自分の名前と、どこのセルを意味するかを指し示すRefersToというプロパティを持っているので、名前で参照することができるHashを作っているという具合になります。

■RubyでExcelを操作するときのTips　

　RubyでExcelを操作するときのTipsをメモる

　昨年「RubyでExcelのデータを読み込む」というエントリを立てた。ExcelのデータをRubyで処理すると、オフィスアプリを使うレイヤとシステム開発をするレイヤの橋渡しが簡単になりますよ、という記事だった。
　今回はその続編。Rubyの方からExcelに値や書式を設定していく際のTipsをメモっておく。