Top page
Excel 数値解析入門
ExcelはWordと並んで、オフィスだけでなく個人にも広く使われている表計算ソフトですが、住所録を作るか、月間売り上げ高の合計を出して、内訳をグラフに表示する程度にしか使われていないことが多いようです。
解説本も山のように出ていますが、機能が多過ぎるのが災いしてか、どの本も総花的に成らざるを得ないようです。
ここでは、エクセルを使って初歩的な数値解析をするという、ピンポイントの使用法を考えてみることにします。
代数計算と数値計算
中学や高校で学んだ数学は主に代数を使った数学、つまり実際の数値ではなく、変数の関係をxとかyという記号（記号を数に代えて使うので代数）の関係式を立て、次にこの式を徐々に変形していって、解法が知られている公式の形に当てはめることで答えを見出す方法を使っていました。例えば、
「ある値自身を乗じて２倍した値は、15に、ある値の７倍を加えた値に等しい。この値は？」
というような問題を解くときは、ある値をXという代数にして、
2x2 = 15 + 7x
という関係式を立てたら、これを二次式のお決まりの形、つまりXの次数順に整理します。
2x2 - 7x - 15 = 0 ①
学校で習った、この方程式の解法は２通りあって、まずは、二次式の根を求める公式に当てはめる方法で、各次数の係数を a,b,c として
x = (-b±√(b2-4ac))/2a
を使って解く方法。
そして、もうひとつは、因数分解で解く方法です。日本の教育、特に受験の世界は、この「インスウーブンカイ」を特に好む方向へと進んだようです。
その方法はこうです。係数をにらみ付けて、一気に
(2x+3) (x-5) = 0
をひらめきで思い付くのが極意です。ここで重要なのは過去モノの例題集です。
何れにしても
x = -1.5 または x = 5
と回答するのが正解ということになります。都合の良いことに、試験に出てくる方程式の解は、たいてい切れのいい値と、相場が決まっていて、√17.97
などという値は出てこないことになっているようです。
第３の解法
しかし、学校で習わなかった第３の解法も実際にはあるのです。それはシラミツブシ解法とも呼ばれる原始的な解法です。
①の式をもう一回見て見ましょう。
2x2 - 7x - 15 = 0 ①
この式は、左辺を変数xの関数 f(x) の形に見立てて、その値が0になるxを見出すことを意味していることになります。一気にxの値が判るわけでは無いので、f(x)の値を、先ずは
f(x) = y
と置き、xの値を振っていって、yの値を調べてみるのがシラミツブシ解法の唯一の解法です。
試しにxの値を-3から+1しながら調べてみます。
y = f(-3) = 2×(-3)2 - 7×(-3) - 15 = 24 (1)
y = f(-2) = 2×(-2)2 - 7×(-2) - 15 = 7 (2)
y = f(-1) = 2×(-1)2 - 7×(-1) - 15 = -6 (3)
y = f(0) = 2×(0)2 - 7×(0) - 15 = -15 (4)
y = f(1) = 2×(1)2 - 7×(1) - 15 = -20 (5)
y = f(2) = 2×(2)2 - 7×(2) - 15 = -21 (6)
y = f(3) = 2×(3)2 - 7×(3) - 15 = -25 (7)
y = f(4) = 2×(4)2 - 7×(4) - 15 = -12 (8)
y = f(5) = 2×(5)2 - 7×(5) - 15 = 0 (9)
y = f(6) = 2×(6)2 - 7×(6) - 15 = 15 (10)
y = f(7) = 2×(7)2 - 7×(7) - 15 = 34 (11)
xの値をずらしながらyの値を調べていくと、yの値が0に近づくのは、赤で示した２箇所、つまり、yの符号が変わる場所は２箇所あって、(2)～(3)と、(8)～(10)です。たまたま、(9)では f(5)=0 となって５が解の一つであることがわかります。もう１箇所、f(-2)とf(-1)の何処に正解があるかは、この間でxをもっと細かく分割して調べていけば
y = 0 となる場所の見当が付きそうです。
公式や因数分解を使わない、この第３の解法は数値解析法と呼ばれています。数値解析法は因数分解ほど美しくは無いのですが、二次式でも3次式でも５次式でも、極端に言えば、関係式では無く、一部に観測データに基づくデータが入り込んでも解けるところに特徴があります。言わば万能解法です。
電算機で問題を解く
実際の世の中に出ると、代数を使って解を求めることが困難な現象の方が普通であることが判ります。例えば、人類が戦争で直面した大きな問題は、自分達の生死に係わるものでした。強力な火薬と長距離砲が完成した時、敵より早く、そして正確に弾道計算する必要が生じたのです。しかし、弾道の軌道は、大気圧や風速の影響を微妙に受け、着弾目標の運動を予測しながら、装填する火薬の量、砲の角度、などを正確に求める関係式は、残念ながら代数計算では歯が立たなかったのです。それらしい代数式を立てても、因数分解で手間取っている間に、敵は、先ず当てずっぽうに試し撃ちをして、次に砲射角度を30秒角上げて撃ち、目標位置までのずれを補正して３射目を撃とうとしています。
この弾道計算を目的として開発されたのが最初の電子計算機ENIACです。ENIACは代数方程式は一切立てないで、直接数値だけを使って計算する、真空管のかたまりでした。中でしていることといったら単純で、足し算か引き算だけです。ただ、１回あたりの計算速度は圧倒的に早く、膨大な計算回数をあっという間にこなす能力を持っていました。電子計算機は、代数計算に代えて、単純な数値計算を繰り返し実行することで、近似解を求める方法を使うのです。ENIACは実際に砲弾を試し撃ちすることなく、電子回路の中で砲射角度を補正し、近似解を短時間ではじき出して来たのです。
Excelと数値解析計算
現代では、大砲の弾道計算の必要性は減じましたが、天気予報や高層建築物の揺れ計算だけでなく、広範囲に数値解析をおこなうようになってきました。コンピュータはそのその代表的なツールです。家庭に入り込んでいるパソコンでさえ、ENIACとは桁違いの能力を持っているのです。ただ、コンピュータにはプログラムが付き物で、これが無ければ只の箱とまで言われるように、このままでは役に立ちません。専用プログラムを組んで、数値解析法で解を求める方法もありますが、Excelを使うことで、プログラム無しで、その上、計算結果の様子を簡単にグラフ表示することもできるのです。天気予報や、高層建築の計算は厄介にしても、天体の軌道計算やバネの振動計算は工夫次第でできるはずです。
実は、Excelは四則演算だけでなく、高度な関数機能を持っていて、統計計算、行列計算、、、我々には一生縁がなさそうな関数までが予め用意されているようです。しかし、Excel関数の高度な使い方は、解説本や、Excel解説サイトに任せるとして、このページでは高級な数学を使わないで、自然現象を解析する、初歩の数値解析法を取り上げたいと考えます。
Excelで数値解析
１．Excelの画面
エクセルを開くとたくさんのマス目が現れますが、このマス目がセルです。縦の行番号は1～65536、横の列番号はA～IVまであります。
一つのセルを左クリックすると、クリックされたセルは選択された状態になり、文字や数字を書き込むことができます。
下図で選択されているセルはＢ列3行目なのでこのセルは、B3 のセルと呼びます。
２．セルで範囲を指定する
単一のセルではなく、B3～B10 までのセルを右クリックしながら範囲を選択することもできます。
クリックではなく、Excel自身の中で範囲を表現する場合は、セル番号:セル番号 のようにします。１行や１列だけでなく、複数の行列に渡って範囲を示すことも可能です。
B3:D10 は、左上隅がB3、右下隅がD10セルまでの範囲を表す方法です
３．セルに数式を書き込む
セルの中には文字や数値を書き込むことができますが、数式を書き込むこともできます。数式は、
上のように =数式 の形式で記入します。セルを選択しておいて、数式バーに記入してもOKです。
加減乗除は +, -, *, /
べき算 ^ は、A3^2 や A3^0.5 (＝平方根) のように使います。
SIN( )、COS( )、TAN( ) の単位はラジアン
ABS( ) 絶対値
EXP( ) 底eの指数
LOG( ) 基数10の対数
LN( ) eの自然対数
式の優先順位の ( ) の使いかたは普通の計算式と同じです。
他にも多くの関数がありますが、それらはネットで調べることができます。
ところで、B2セルに "60" を予め入力しておき、次に、 C2 セルに "=B2*3.14" という数式を記入して、Enterキーを押すとどうなるでしょうか？
C2セルには数式に変わって、"188.4"が表示されます。これは、C2に書き込んだ数式が実行された結果の答えです。
エクセルの表に "=数式" を書き込むと、書かれた数式が実行されて、その答えが表示されることがわかります。
そして、Excelの優秀さの一つは、一つのセルに書いた数式を他のセルにも簡単にコピーできるところです。
準備として、先ほど作ったB2の下のセルにも、別なサンプルデータを下図のように作ってみましょう。
そして、数式が書かれているC2セルをクリックして選択状態にしたら、右クリックしてコピーを指定します。
すると、コピー元であるC2セルの周囲は、蟻の行列状態になりますから、続けてコピー先を指定します。ここではマウスでＣ3からC4の範囲を押しながら指定して、最後にリターンキーを押すと、下図のような画面になるはずです。
B2 × 3.14 が C2 に表示されたように、
B3 × 3.14 が C3
B4 × 3.14 が C4
に表示されています。C4に表示されている値は、 Ｂ４の値×3.14 が表示されているようです。試しに、C4セルをクリックしてみると、
やはり、 =B2*3.14 ではなく、 =B4*3.14 に自動的に変更されていることがわかします。
このように、 B2 や C2 のように書いたセルは 相対セル番号 といい、別のセルにコピーすると、自動的にセル番号が書換えられるのです。
コピーした時でも変更されないセル番号は、絶対セル番号といい、 $A$3 のように $ を頭に付けます。
相対セル番号指定と、絶対セル番号指定との違いは、とても重要な違いです。
4．グラフを表示する。
グラフに表示する範囲を選択します。この時、最も上の行を項目名しておくと見易いグラフになります。
棒グラフのアイコンのグラフウィザードをクリック→折れ線の形式を下図のように選択し→完了 するとグラフが画面に表示されます。
ニュートンの運動方程式
このページでは、物体の運動を数値解析する方法を取り上げます。 物体の運動を記述する方程式は、「ニュートンの第２法則」と呼ばれています。
F = m ・α (F：力〔N〕 m：質量〔kg〕 α：加速度〔m/sec2〕 ) ②
これは、物体に加えられた力と、質量×加速度 は等しいとする表現ですが、書き換えると
α = F / m ③
これは、質量 m の物質に力 F を加えると、Ｆの大きさに比例し、m に逆比例する加速度αが発生する、と解釈できる表現です。 数学で A ×B
= C と書いても、「AとBの積はCである」以上の意味はないのですが、ニュートンの第２法則は、よくよく見ると、実に不思議な方程式です。その一つが、α（加速度）という概念です。
加速度というのは、「速度」では無く、「速度が変化する量」です。しかも「わずかの時間の間に」「わずかに変化する速度の量」を対象としているのです。
ここには【わずかの時間】という概念と、【わずかの速度の変化量】という、重要な概念が使われています。
【わずかの時間】 → Δt
【わずかの速度の変化量】 → Δv
と呼ぶことにします。【わずか】という量は、決められた値ではありません。例えば、電車の速度はかなり滑らかに変化する量ですから、100分1秒くらいの間なら、ほとんど同じ速度のままと見なしても構わないはずです。一方、弾丸が衝突する瞬間を調べる場合は、１0万分の１秒以下で調べないと粗すぎるかもしれません。考える事象の変化に対して、十分小さい変化の量をΔとする概念なのです。加速度は次のような量なのです。
α = Δv / Δt ④
例えば、計測器の時刻表示が 23.2秒から 23.5秒に移動する間に、物体の速度vが 120m/s から 130.2 m/s に変化したら、
α= (130.2m/s - 120m/s) / (23.5s - 23.2s)
10.2 m/s
= -----
0.3s
= 34 m/s・s
= 34 m/s2
観測した時刻（23.2～23.5秒）の、加速度は34 m/s2という値だったことになります。
また、物体の質量が例えば 2kg だとしたら、②式で
F = 2kg × 34m/s2
= 68 N
の力が働いていたことになります。
さて、こうして、ある瞬間の加速度αがわかると、次の瞬間の速度がどうなるかを計算できるようになります。
時刻 t + Δt における
速度は v + Δv ＝ v + α・Δt
ある瞬間の位置と速度が分かると、その次の瞬間の位置と速度が分かるということは、その操作を繰り返していけば、ずっと、ずっと先の運動までわかるということになります。各時点で加わる力と質量が分かれば、天体やロケットのような物体の運動も精密に記述できるはずです。
等加速度質量の運動
Excelで等加速度運動する質量を数値解析してみることにします。
下図は、その準備です。2～5行目は注釈です。使用する主要な計算式などのメモです。
A7～A9は、計算式の内訳です。ある行での時間nと次の行の時間n+1の関係式です。
質量の位置はXとしています。
評価で振ってみるパラメーター値は、絶対セル番地の $F$6 に時間増分量、 $F$7 に力、 $F$8 に質量 を記入します。
初期値の速度V,位置X、時間t はB11～D11 に入れることにします。
計算する速度の増分ΔVはA11に記入しますが、その内容はニュートンの運動方程式です。
ΔV = F・Δt/M
次にB12にVの次の値を記入します。
B13には、xの次の値である、 C11+前の速度×時間増分 を指定します。
これで用意は完了です。各列の式を、以降に必要な行数までコピーすると表が完成です。
上はV、x、t のグラフを表示させたものです。積分を使った代数計算は
x = (1/2) α・t2
例えば、t=1.0の時は x=0.5 になるはずですが、数値計算では0.49 になっています。このように、近似計算では、ある程度誤差が出ますが、グラフを見ると、きれいに二次曲線が出ていることがわかります。
バネに付けたおもりの振動
バネにつけたおもりを引いてから離すとおもりは振動します。以下は、バネの振動の数値解析です。
バネは引いた距離X、バネ定数K に対して、力 -K・X を発生し、この力がおもりに加速度を与えることになります。
この場合も、ニュートンの第２方程式が成り立ちますから、以下のようにExcelで表を作ることができます。
減衰率はバネ振動が減衰する率です。
速度変化とおもり位置の変化、時間の変化は次のようになります。
B11 は =(B10+A10)*$E$7
C11 は =C10+B10*$E$4
D11 は =D10+$E$4
以上を記入し、各最上位の行を必要な行数だけ、下にコピーすれば表は完成です。
グラフのように、バネは振動していることがわかります。元の式には何処にも三角関数など含まれていなかったにも係わらずグラフには美しいサイン波形が出ています。
では、バネの運動周期はどうでしょうか？ 同じおもりとバネを使った場合の周期は、公式では
T = 2π√(m/K) = 2×3.14√(10/1000) = 0.628 sec
ですが、表では下図のようにおもりの最高位置での時間tは 0.63秒 と、ほぼ同じ結果が得られていることが分かります。
数値解析法は、ニュートンの第２法則のような、微分モデル式を使い、初期条件を与えることで以降の物理現象を分析するのに適した方法です。こういった意味で、運動方程式だけでなく、たわみや、慣性モーメントを解析する際も、数値解析法を使うことができるはずです。
また、電気回路のインダクタンスL、キャパシタンスC、抵抗R などもまた、ニュートンの第２法則と基本的には同じ性質を持っていますので数値解析法が使えます。別ページのLTspiceは電気回路専用の数値解析プログラムといえます。
多くの物理現象は、短い時間、あるいは、短い距離でみると、比較的簡単な比例関係や反比例関係にあることが多いため、数値解析法とエクセルを効果的に使うことができる場面が多いのです。
ただ、数値解析法はあくまで近似計算ですから、誤差が出て、しかも累積誤差が拡大することもあるということです。どこまでもΔを細かくすればいいかというと、数値の計算精度に限界がありますからそうもいかないので、注意が必要です。演算される相互の変数のレンジが離れないように各単位のレンジを選ぶ必要があります。mかmmか、あるいはgかｋｇかだけでなく、必要であれば通常以外のレンジを使って、多数回の演算でも有効数値が崩れていかないような配慮が必要です。
Excel操作の補足
上で取り上げたような例は、Excelの初歩的な使用方法でできるものですが、もう少し細かい操作が必要になる場合があります。以下は、Excel操作の簡単な補足です。
最大値/最小値 指定範囲内の最大値、あるいは最小値を求める関数です。
=MAX(B3:B100)
=MIN(B3:C100)
指数表記 エクセルで 6.26E-2 のように表す数値は指数表記とよばれ、これは 6.26×10-2 を表します。
3.0E+5 は 3×105 です。
連続データの作成 1,2.3,,のように連続したデータを大量にセルに作るには
範囲を選択し、右クリック→セルの書式設定で書式を選択しておきます（標準的な属性ならこの操作は不要です）。
開始セルをクリックして、最初の値を入力します。
編集→フィル 連続データの作成を選び、必要な項目をいれ、停止値を指定して OK します。
アドイン Excelを標準インストールすると、ソルバーなどの機能を使用できません。追加機能は、ツール→アドイン で機能を追加することができます。この操作には Windows
Office のCDが必要です。
ゴールシーク 関数の値が目的とする値になるよう、変数を振って自動的に捜してくれる機能です。
使用例
B2に数式を入力します。 =8*A2^3-6*A2^2-3*A2+1
ツール→ゴールシーク で
数式入力セル B2
目標値 0
変化させるセル A2
のように指定し、OK すると A2 に回答が入ります。
ソルバー ゴールシークと違い制約条件付きや、連立方程式にも使える機能です。基本的な操作はゴールシークと似ています。
Top page