Excelによる科学技術計算入門

大江　修造

はじめに

Excelは現在最も広く利用されている「表計算ソフト」といえよう．ところで，Excelとは「優れている」と言う意味であるが，これはExcel以前に使われた表計算ソフトMultiplanを意識したネーミングと思われる．しかし，表計算ソフトが何故それほど良く使われるのであろうか？それは「表計算ソフト」と言うけれど，事実上の「計算ソフト」であることによる．短的に言えば「計算」はすべて「表計算ソフト」によっていると言っても過言ではない．さらに，計算のみならず，グラフ作成の機能を有するので統合ソフトとも言われている．

このことは何も事務計算分野に限ったことでは無い．たしかに，Excelの解説書の殆どは成績処理や在庫管理を例としている．しかし，Excelには行列演算や方程式の根を求めるなど技術計算用の機能が一通り用意されている．これによって，Excelにより

方程式の根を求める

連立方程式の解を求める

最小自乗法により実験式を決定する

非線形計画法の解を求める

などが可能である．

本稿ではExcelの技術計算への応用に際し基本となることを中心に解説した．分かり易く読めるように配慮したが，Excelを１度位は触った事があることを前提としている．

１．Excelの使用法

　Excelは256列×65536行からなる大きな表でできている．表中の一マスをセルと言い１行１列目はＡ１と表す．列はA,B,C...で行は1,2,3...で示す．セルには数値，文字列および計算式を書くことができる．セルの内容は

図１ Excel画面の概要　

「数式バー」に表示され，編集も可能である．カーソルやマウスにより入力対象のセルを変えることができるが，セルの「名前」が「名前ボックス」に表示される(図1)．

Excelの使い方を簡単な例で見てみよう．

1500×3＝4500

この計算はA1セルに1500をB1セルに3を入力しC1セルに=A1*B1と入力すれば良い．

この計算の実行の様子を図２に示した．これ

だけなら電卓と変わりないが，Excelの良さは1500や3を種々変える事ができるし，この３つのセルを下方向にコピーしてA,B列の

図２　Excelにおける計算

値を連続的に変える事に一種シミュレーションを実行できる点にある．

２．方程式の解法

　２次方程式の根の公式は誰でも知っているが３次方程式以上の根の公式に詳しい人は少ない．コンピュータでは数値解法として種々の解法が用意されている．従来はプログラムを作成して方程式を解くのが一般であった．Excelには種々の方程式を解くためのプログラムが「ソルバー」として準備されている．

　ソルバーは最初からExcelにインストールされていないので初めて使う場合は購入時のCD-ROMよりセットアップする．

　３次方程式

を例にしてソルバーにより解を求める．ソルバーの利用に先立ち，この方程式の根がどこにあるのか検討をつけるためにグラフを書いてみよう．

xをＡ列に方程式をＢ列に書き込む．挿入からグラフを選び散布図によりグラフを得る．図３より，この３次方程式は３実根を有する

図３　方程式の値をグラフ表示

ことと大凡の値を知ることが出来る．

図４　ソルバーにおけるパラメータ設定

次にツールからソルバーを呼び出す（図４）．目的セルとして方程式を書いてあるB1セルを選択し変化させるセルとしてｘの書き込んであるA1セルを選択する．A1およびB1セルが$A$1および$B$1となっているのは絶対番地セルとして書き込まれたことを示している．方程式の値が０となることを要求しているので目標値として０を選ぶ．「実行」により画面が変わり，解の１つがＡ１に瞬時に書き込まれ

図５　ソルバーによる方程式の解

る（図５）．ソルバーの画面としては最適解が探索結果がとして見つかったと言う表現となっている．解が得られたとしたときの方程式の値は０ではなく5.2E-0.7とＢ１に書き込まれている．これは5.2×10―７を表しているが数値計算による収束計算の結果のためである．

　ソルバーのパラメータ設定画面には目標値として最大値や最小値が設定できるようになっているがこれは，ソルバーが最適値探索の機能を有しているためである．

３．連立方程式の解法

　２つの関数　MINVERSEおよびMMULTにより連立方程式の解を求めることができる．例により説明する．連立方程式は

　

行列の形式によれば

[係数行列][未知数行列]=[定数行列]

と表せるから，[係数行列] -1が存在するならば

[未知数行列]= [係数行列] -1[定数行列]

により未知数行列を求めれば良い。その方法は�@係数および定数をシートに入力する（図６）．�A係数行列の逆行列を作る（図７）．逆行列の出力されるセル領域（A5：C7）を選択し，A5セルをカレントセルとした上で関数MINVERSEを数式バーに書き係数領域を選択する（[Ctrl][Shift][Enter]キーを同時に押す）．�B関数MMULTにより逆行列と定数行列との積を求める（図８）．数式バーにMMULTを書き逆行列領域と定数行列領域を選択する．解の出力領域を選定する．その領域(D1:D3)に解（x1=１，x２=２，x３=３）が得られる（領域(D1:D3) をカレント領域として[Ctrl][Shift][Enter]キーを同時に押す）．

�@係数および定数をシートに入力する．

図６　連立方程式の係数と定数

�A係数行列の逆行列を作る

図７　係数行列の逆行列の計算

�B連立方程式の解を得る

図８　連立方程式の解を得る

４．実験式の作成法

　実験などで得た結果を実験式にまとめることは研究では極めて多い．実験式は最小自乗法により決定されるが，１次式の場合の切片と勾配を求める関数INTERCEPTおよびSLOPEが用意されている．蒸気圧データからクラペイロン・クラウジウス式

の係数を決定する．処理すべきデータは

温度（℃） 58.6 62.4 65.8 68.8

蒸気圧（mmHg） 60 70 80 90

である．�@ｙ=ln P, x=1/Tとおいて最小自乗法を適用するためにデータを変換する（C,D列，図9）．�A関数INTERCEPTを定義して(C7ｾﾙ)切片を決定する（図10）．�B関数SLOPEを定義して(C8ｾﾙ)勾配を決定する（図11）．

�@データを変換する（C,D列）

図９

�A関数INTERCEPTにより切片を決定する．

図10

�B関数SLOPEにより勾配を決定する．

図11

以上の操作により，実験式

を得る．処理の意味を理解あるいは確認するために ln P と1/T の関係をグラフとして図12に示した．得られた実験式による計算結果（図13）とそのグラフ（図14）も示した．

関数の利用のみにより実験式が作成できることを示した．Excelには他に分析ツールがあって回帰分析が準備されている．この回帰分析によっても同じ結果が得られるが，直線性を検証するための様々の計算値が出力される．直線性が明確に決まっている実験式の決定には不要である．

図12　データ変換結果

図13　決定した実験式による計算

図14　決定した実験式による計算結果

５．検索システムの作成法

多数のデータを含む一覧表の中から特定のデータを検索するシステムは様々な局面で必要となる。そのシステムをプログラムの作成により構築しようとすればかなりの労力と能力が必要である．しかし，Excelではコンボボックスおよび関数INDEXを用いて簡単にシステムを構築できる．

例えば化学物質の基本的な性質（融点，沸点，臨界定数など）を示す表１から特定の物質の基本物性を検索する場合を考えて検索システムを作成してみよう．

表1　化学物質の基本物性表

コンボボックスは図15中に示すように表１中の目的の項を選択するためのツールである．これをシートに設定するにはメニューから

図15　検索システムの構築

表示 → ｺﾝﾎﾞﾎﾞｯｸｽ → ﾌｫｰﾑ → ｺﾝﾎﾞﾎﾞｯｸｽ

を選択しシート上の所定の場所でマウスをドラッグする．次にコンボボックス上でマウスを右クリックしオブジェクト

書式設定メニューを開く．そのメニューのコントロールから入力範囲とリンクするセルを決定する．入力範囲としては一覧表の物質名の日本語名の最初から最後までのセルを選択する．

一覧表とコンボボックスとを関係づけるものとして一覧表の番号を使う．この番号をリンクするセルをシート上に指定する．コンボボックスの近くのセルを指定するのが便利である．

　検索した表中の項目を示すためにＩＮＤＥＸ関数を使う．図15中の数式バーに示す

INDEX(物性!C7: C1079,$A$1,1)

は「物性シート」のＣ列７行目から１０７９行目までのデータからセルＡ１で指定する番号のデータを指定すると言う意味である．

指定されたデータは「名前ボックス」に表示されているセルＥ１に表示される．つまり，関数ＩＮＤＥＸを定義してセルＥ１に書き込んでおけばよい．

６．ＶＢＡにおけるプログラムの作成法　

　かってパソコンと言えばBASICでプログラムを作って使うものであった。しかし，ソフトウェアがそろってきて大体のことがプログラムを組まずに処理出来るようになった．とは言うもののプログラムを組まなければ処理できない問題もある．

Excelでは処理の自動化を「マクロの記録」と言う形で進めるが，そのマクロの中身はVBA(Visual Basic Application)と言うBASICでできている．ところが，このVBA は従来のBASICとはかなり異なっている．

一例を挙げれば，入出力におけるINPUTおよびPRINT文が全く姿を消し，これに変わってセルのデータの代入と言う形式になっている．つまり，シート上のセルとのデータのやりとりに変わってしまった．

しかし乍らこれまでのソフトウェアの資産を生かすことを考えた場合，出来るだけこれまでの資産を継承できる形でのプログラム作成が好ましい．このように考えて，プログラム作成の雛形を作った．方針および方法以下に示す．

(1) サブルーチンを含め１本のプログラムとして作成する

(2) メインの終わりに Exit Sub と書く

(3) 「subnamae:」と言うサブルーチンをExit Subの後に宣言する

(4) サブルーチンの最後にReturnと書く

(5) メイン中からsubnamae:を呼ぶにはGoSub subnamae とする

(6) 引数は要らない

　それでは具体的にプログラム作成法（蒸気圧計算）を見ていこう．�@プログラムを定義する．これを簡単に行うにはメニューからツール → マクロ → 新しいマクロの記録 と選択し，マクロの名前なわちプログラムの名前（蒸気圧計算）を決めた後，何も記録せずに記録終了とする．その後にメニューから ツール → マクロ → Visual Basic Editor と選択した後，プロジェクト欄の標準モジュールのModule1をクリックする．すると図16に示すようにプログラム名が定義されているので，Sub と End Subに囲まれた範囲にプログラムリストを直接書き込めば良い．

図16　プログラムの定義

�Aプログラムリストを直接書き込む(図17)．既に作成済みのBASICプログラムのリストがある場合コピー・ペーストで貼り付ければ良い．この場合，BASICとVBAとの文法の相違点が赤字で表示されるので，その部分を書き換える．例えば入出力の部分などである．

入力「INPUT A」は　「A=Cells(i,j)」に

出力「PRINT A」は　「Cells(i,j)=A」に

変える．Cellsはセルを指定するための予約語であり，カッコ内ｉおよびjはそれぞれ行と列を指定するための要素である。ただし，たとえ文法の異なる部分でも変数の宣言などのように赤字で表示されない部分もある．

�Bサブルーチンの宣言はExit Subでプログラムを終了させた後にラベルの形式で書き，Subなどの予約語は使わない．

図17

ただし，サブルーチンの呼び出しにはGoSubを使う．

�Cプログラムの実行は，プログラムの開発中はVisual Basic Editor(VBE)の中の実行ボタンからの実行がデバッグのために便利である．

ﾌﾟﾛｸﾞﾗﾑ完成後はｼｰﾄ上に実行用ボタンを作成しておけば，いちいちﾒﾆｭｰからマクロを呼び出す必要はない．実行結果を図18に示す．蒸気圧を計算するための温度は予めシート上に書き込んでおく．こうすることによって任意の温度における蒸気圧が求めやすい．

　個人レベルでプログラムを作成して使う場合を念頭においてVBAの使い方を述べた．大規模なプログラムを作成する場合にはプログラムを「プロジェクト」別に作成すべきであろう

図18　VBAプログラムの実行結果

おわりに

　ExcelのBASICプログラム(VBA)を作成すれば殆どの技術計算は可能である．しかし，敢えてプログラムを作成せずに技術計算を達成する方策を主に述べた．それはプログラム無しに問題を解決できるのがこれからのコンピュータの進むべき道と考えたためである．　しかし，プログラム作成そのものは高度に知的な作業であり，達成感も大きい．

本稿の例のどれか一つでも試して頂いてExcelって結構いけるじゃないかと言う実感を味わって頂けることを願って終わりとします．

参考文献

森口繁一，「Excel/Basic基礎指南」，日本規格協会，2000

大江修造，「実例パソコン実験データ処理」，講談社, 1986

おおえ・しゅうぞう

東京理科大学工業化学専攻教授，東京理科大学図書館長，1962年，東京理科大学理学部応用化学科卒．1971年，東京都立大学大学院工学博士号取得．　1962-1980年，石川島播磨重工業（株），1980-1991年，東海大学，1973年米国Fractionation Research, Inc.にて蒸留装置開発に従事，著書，"Vapor-Liquid Equilibrium Data"他, Elsevier, 1989，1991年４月〜現在，現職