Excelで「モデル化とシミュレーション」前夜

モデル化とシミュレーション

こんにちは。今回は、以前書いた「モデル化とシミュレーション」前夜の続きです。「モデル化とシミュレーション」前夜では数式モデルを作りました。そのシミュレーションを今回はExcelでやってみたいと思います。

シミュレーションでどれくらい試すか?

シミュレーションで試してみるのは、前提として決めた条件によるものだけで大丈夫ですか?それとも、いろいろ条件を変えて試してみたいですか?

それによって作り方が変わってきます。太郎くんの歩く速さは分速55mとしていましたが、太郎くんは遅刻できない理由があってもっと早く忘れ物をもらわなければなりません。そのため、もっと早く歩いたらどうなるか、さらに早く忘れ物をもらうために走ったらどうなるか、など条件が変わる可能性があります。次郎くんの方も、お兄さんのために自転車でかっ飛ばして忘れ物を届けるかもしれません。

ここまで2人の分速についての話をしましたが、もしかしたら太郎くんは駅に着く前に気が付いていたらどうか?家と駅がもう少し離れていたらどうだったか?など位置についての条件を変えて試してみたくなるかもしれません。

2人が歩く速さや最初の位置といった条件に関する値は数式に直接埋め込むのではなく、パラメータとしてわかりやすく値を別のところに示しておく方が変更しやすくなります。今回は、2人の歩く速さと最初の位置をパラメータとしてシミュレーションしてみます。

いざ、シミュレーション!

それでは、実際にExcelを使ってシミュレーションしてみましょう。

絶対参照が含まれているので、少しわかりにくくなってしまってしまいました。簡単に解説します。

太郎くんの位置は次の式でした。

太郎くんの位置 = 1000 - 55t

ここで、1000は太郎くんのはじめの位置、55は太郎くんが歩く速さでした。また、tは経過時間でした。これらがどのセルに含まれているか確認してみましょう。

太郎くんのはじめの位置  1000   B1のセル

太郎くんが歩く速さ      55   B3のセル

経過時間         変化させる  A7~A25のセル

パラメータになっている太郎くんのはじめの位置(B1)と太郎くんが歩く速さ(B3)は別の表になっていて、式を複製しても参照する位置は変わりません。そのため、それぞれを$B$1、$B$3と絶対参照にしています。それに対して、経過時間は同じ行のA列に値があるので、行が変われば参照する位置も変わるので相対参照となり、$は付けていません。次郎くんについても、同様です。

2人の位置が同じになった時に忘れ物を渡すことができるので、10分経過した時になります。表ではピンク色を付けてありますが、数値を見てもあまりピンときません。

視覚的に表現しよう

Excelでシミュレーションする利点の2点目は、簡単にグラフを描けることです。かなり昔、グラフを描く操作について授業で説明したら結構な時間の説明が必要でした。しかし、今ではグラフの表示にこだわらなければ、アイコンを数クリックするだけで済んでしまいます。そんなに簡単に描けるならば、使わない手はありません。

A6からC25を範囲指定して、散布図のアイコンをクリックしてみましょう。時間経過により太郎くんの位置と次郎くんの位置がどこにあり、2人がいつ出会うかがわかるグラフが描けたのではないでしょうか。今回は散布図を使いました。折れ線グラフでも似たようなグラフが描けますが、試してみると経過時間も2人の位置とは別の系列として折れ線が描かれてしまいます。経過時間の折れ線は不要です。そのことから、散布図でグラフを描いた方が今回の問題を考える上ではわかりやすいと思います。

グラフの書式に少し手を加えてわかりやすくしたものを掲載します。

グラフを見ると、10分経過したときに2人は出会うことが視覚的にわかります。なんか、鉄道のダイヤグラムみたいな感じですね。鉄道ネタが多いように思いますが、鉄ちゃんではないですよ。何となく鉄分が気になる程度です。

出会う位置について書式に手を加えたので、グラフでは450mの位置で出会うことが表示されています。この図のように数値が表示されていなくても、元の表に戻って数値を確認すれば値を知ることができます。グラフの操作について書き始めると、要点がわからなくなりそうなので今回はパスさせてください。(鉄ちゃんのくだりは必要だったのか・・・?)

答えが小数ってこともあるよ…

最後に時間が整数にならない場合について少し触れます。今、経過時間を1分刻みでシミュレーションしています。1分刻みにしてしまったのは、整数に長く慣れ親しんでいて、1ずつ増えていくのがわかりやすいからです。1ずつ経過時間を増やす必然性は何もありません。0.5ずつ増やしても構わないし、0.01ずつ増やしても構いません。ただ、0.01分は0.6秒です。正確な値が出ることにどこまで意味があるか、実用面でどの程度の精度が必要かを考えて、経過時間の刻み幅を検討する必要があると考えています。

それでは、今回は終わりです。経過時間の刻み幅を変えることを書いていたら、表の作り方について気が付いたことがあるのですが、またそのうち書きたいと思います。それでは、また次回。

Posted by 春日井 優