数学の道具箱

Excelは元々経営シミュレーションの目的で開発されたソフトウェアですが、現在では様々な数値計算を可能にする汎用の計算道具になっています。せっかく手元にあるのだから使わなければ損。　

数学的な問題で、数値に置き換えて計算することで、問題の状況を明確に把握できるということはしばしば経験することです。そうすれば、数学や統計、確率の理解もすすむことでしょう。


目次
  1. はじめに、関数電卓の代わり
    1. 普通の計算
    2. 集計シートとして利用
  2. データの入力
    1. 数式の入力、矢印キーの活用
    2. セル参照、相対参照、絶対参照、部分絶対参照
    3. ショートカットキー
    4. 連続データの入力
    5. アクティブセルの移動、選択
  3. 関数の種類
    1. 数学関数
    2. 統計関数
  4. 便利な機能
    1. データテーブル
    2. ゴールシーク
    3. ソルバー
    4. 感度分析、スクロールバー
  5. 計算の精度
    1. 表示桁数
  6. 行列計算
    1. 行列の四則演算
    2. 連立方程式
  7. 微分方程式
    1. 数値微分
    2. バスモデル
    3. SIRモデル
    4. 在庫モデル
  8. シミュレーション
    1. 確率実験
    2. 乱数の生成
  9. 統計計算

はじめに、関数電卓の代わり

普通の計算

計算といえば電卓、キー操作には十分慣れているはず、ですが、ちょっとした計算でもExcelを使うことによって、作業効率が100倍、1000倍、∞倍になることは間違いありません。電卓との違い（のホンの一部）をまとめておきましょう。

電卓 Excel
いきなり数式を入力し、最後に「=」キー 最初に「=」を入力し、最後にEnterキーを押す
(関数計算）数値を入力して関数キーを押す 関数文字列を数式として入力する（SQRT(.)のように）
同じ数でも、その都度入力しなければいけない
 セルに入力しておけば、あらためて入力することなく何回でも使える
長い式を間違いなく入力するのはむつかしい
 間違えたら何度でも部分的に修正ができる、分けて入力することもできる
計算結果は次の計算の時消えてしまう
 別のセルを使えば結果を残しておくことができる
したがて、比較計算がむつかしい
 関数表作成が容易、いろいろな計算を比較確認できる
逆行列を計算するのはむつかしい
 行列計算が容易


集計シートとして利用

統計データの集計用として、Excelを使うことには大きなメリットがあります。一旦入力しておけば、何度でも繰り返し使うことができますし、追加修正も簡単にできます。統計用の関数も豊富です。

データの入力

数式の入力

入力は「直接入力モード」が原則。「=」から始めると、数式入力モードになる。

入力は大文字でも小文字でも構わない。入力が確定した後は、関数名もセル番号も大文字で表記される。

数式の中の定数は、直接式に書き込まないでなるべく、セル番号を使うのがExcel流。セル番号を数式に書き込むことをセル参照という。そうすることで、定数の値を変えたい場合に迅速に対応することができる。

四則演算は「+」「-」「*」「/」と書く。べき乗計算は「^」。カッコは「( )」しか使えない。

注意　「-」は「^」に優先する。普通「=-3^2」と書けば「-32」と思うが、Excelでは「(-3)2」の意味であるので注意が必要。

ヒント　長い式はいくつかに分割して複数のセルに入力して、最後にそれらを統合する式を入力するようにすればながーい１行の式を見続けなくてすむ。たとえば、分数式の分母、分子を別々のセルに入力し、最後に「=A3/A4」のような式を入力する。

関数の入力

Excelは豊富な関数によって、計算能力を飛躍的に高めている。関数を入力する方法はいろいろある。

方法１　「関数の挿入」ボタンの利用

「数式バー」の左にある「関数の挿入」ボタンをクリックすると、「関数の挿入」ウィンドウが表示され、関数のリストが表示される。機能によっていくつかのグループに分かれているので（財務、数学、統計、文字列操作、など）、分類が分かっていれば「関数の分類」のプルダウンメニューから適切な分類名を選択する。よく分からない場合は、「関数の検索」の下の空白に、掲載したい内容の検索ワードを入力する。

方法２　インライン入力

関数の名前がある程度分かっている場合は、いちいち「関数の挿入」ボタンをクリックしてリストから選択するのは煩わしい。いきなり「=sq」のように関数名を最初から入力していく。「=s」と入力したとたんに、sから始まる関数名のリストがポップアップされ矢印キーで選べるようになる。さらに続けて「q」と入力すると候補が二つに絞られるので、選択が容易になる。特に長い名前の関数を入力する場合は有効な方法である。

方法３　リボンのコマンドボタン利用

平均、総和にようによく使われるいくつかの関数は「ホーム」→「編集」→「オートSUM」ボタンの右をクリックすると、いくつかの関数名が表示される。

数式の入力の際の矢印キーの活用

数式の中でセルを引用するときは、セル番号をキーボードから直接入力する代わりに矢印キーとCtrlキーを組み合わせて使うと、入力ミスが減り、効率が良くなる場合が多い。

例えば、セルC4に「=SQRT(A4)」と入力する場合、「=SQRT(」と打ってから←矢印キーを１回押すと、セルC4の表示が自動的に「=SQRT(B4」と変わります。もう一回←キーを押して「=SQRT(A4」となってから 「)」キーを入力してEnterキーを押せばセルは「=SQRT(A4)」という式が確定します。

「=SUM(A3:A10)」のようにセル範囲を入力する場合は、上の手順で「=SUM(A3」まで入力したら「:」をキーボードから入力しま す。そうしてからまた、矢印キーを動かすと「=SUM(A3:A4」のように次のセル記号が入力されるので、それがA10になるまで↓キーを移動させ、 最後に「)」と入力してEnterキーを押します。

セル参照、相対参照、絶対参照、部分絶対参照

セル一つだけを使って作業するということはあまりなく、いくつかのセルで同じような計算をしてそれらを比較する、という場合がよくあります。例えば、複利計算：(1+r)nの場合を考えます。n が大きくなるにつれてどのように増えていくか、r の大きさの違いで増え方がどう変わるか、ということを調べるのが目的です。セルB2に r の値、セルB3に n の値、セルB5に「=(1+B2)^B3」と入力して、セルB2,B3の値をいろいろに変えると目的が達成されますが、別の方法を試してみましょう。

セルB5に(1+r)0= 1、セルB6に(1+r)1= 1+r、セルB7に(1+r)2、セルB8に(1+r)3、．．．のように入力しておけば、n の違いをすぐに比較できますし、セルB2を変えたときもセルB5,B6,...の内容が一斉に入れ替わるので、セル一つだけで調べるよりも分かりやすいでしょう。

（漸化式）各セルに別々の数式を入力する必要がありますが、工夫するとその作業を劇的に減らすことができます。セルB6はセルB5の 1+r 倍、セルB7はセルB6の 1+r 倍、セルB8はセルB7の 1+r 倍、ということに注意してください。この関係（漸化式といいます）はどこまでも続きます。そこで、セルB5に1を入力したあと、セルB6に「=B5*(1+B2)」、セルB7に「=B6*(1+B2)」、．．．と入力すれば良いのですが、この作業もサボることができます。

（相対参照）セルB6を入力したあと、セルB6をコピーし、セルB7にペーストします。そうすると、セルB7には「=B6*(1+B3)」という式が入力されます。セルB6に入力された式にあるB5,B2は、セルB6からみて「一つ上のセル」「4つ上のセル」という意味を持ち、その数式をセルB7にペーストすると、B5,B2は、セルB7からみて「一つ上のセルB6」「4つ上のセルB3」に置き換わり、「=B6*(1+B3)」となった、というわけです。このようなセルの参照を相対参照といいます。

（絶対参照）B6はよいのですが、B3では困ります。そういう場合は「セルB6から見ていくつ上」という相対参照ではなく、「どこから見てもセルB2」という絶対参照を使います。絶対参照は $B$2 のように「$」を使って表します。結局、

  1. セルB5に「1」、セルB6に「=B5*(1+&B&2)」という式を入力する。
  2. セルB6をセルB7:B25にコピーペーストする。

これで20年分の計算が一覧表で表示されます。セルB2の数値を変えるとセルB6:B25が一斉に変わることが観察できるでしょう。

ショートカットキー

このように入力の手間を省こうとすると、コピーペーストが盛んに出てきます。いちいちマウスを使って「ホーム／クリップボード」タブのアイコンをク リックしていたのでは非能率です。マウスを使う代わりに、Ctrlキーを押しながらCキーを押すと「コピー」ボタンをクリックしたのと同じ、Ctrlキー を押しながらVキーを押すと「ペースト」をクリックしたのと同じ、という便利な機能がありますので、覚えてください。これは必須です。

このように、マウス操作を同じ動作を実現するキーの組み合わせをショートカットキーと いいます。コピーペーストの次によく使うショートカットキーはCtrlキーとZの組み合わせで、「直前の命令をキャンセルする」という動作をします。何か 間違ったときは、慌てずにCtrlキーとZキーを押してみてください。たいていは元に戻ります。そのほかのショートカットキーは、アイコンにマウスを合わ せるときに表示される小さなウィンドウで「（Ctrl+C）」のような文字列が書かれている場合は、それがショートカットキーですので、自分で調べてくだ さい。

連続データの入力

ある規則に従った数列を入力したい場合、もちろん、１項ごとに入力すれば良いのですが、入力ミスを防ぐために、規則を利用して、計算によって入力させる方法に従う方が効率的です。

方法はいくつかあります。必要に応じて使い分けてください。

方法１　フィルハンドル

規則性が（Excelにとって）明らかな場合は、初期値を入力したセルをアクティブにして、フィルハンドルをドラッグすれば、連続データ（等差数列）が入力されます（例　「項目１」と入力して上の方法を用いると「項目２」「項目３」、...が入力される）。フィルハンドルとは、アクティブセルの右下隅に表示されている小さな四角形のことで、マウスをそこに移動するとアイコンが黒い十字形に変わるので、そこからドラッグを開始すると、アクティブセル範囲が拡大し、「項目２」「項目３」、．．．が表示されるようになります。

普通の数値データの場合は、初項だけではなく、2項目を入力して階差を明示的に与えてから、二つのセルをアクティブにしてからフィルハンドルをドラッグします。

  1. セルA3に1、セルA4に2を入力する
  2. セルA3:A4をアクティブにし、セルA4の右下隅にあるフィルハンドルをセルA102までドラッグする
  3. セルA101:A102をアクティブにして、 セルA102の右下隅にあるフィルハンドルをセルA1002までドラッグする

100セルくらいまでなら何とかなるでしょうが、1000せるとなるとちょっと大変、別の方法を探そう。

方法２　「ホーム／編集／フィル」メニュー利用

「ホーム／編集」タブの「フィル」ボタンをクリックして「連続データの生成」オプションを利用すると、様々な等差数列を大量のセルに入力することが可能。

事例　セルa1:a1000に1から1000までの通し番号を入力し、さらに（別の機会に）a1001:a2000 に2000までの通し番号を追加する方法

  1. セルa1に1を入力
  2. 名前ボックス（シートの左上）にa1000と入力（セルA1000がアクティブセルになる）
  3. 「Ctrlキー」と「Shiftキー」を押しながら「↑キー」を押す（セルA1:A1000がアクティブセルになる）
  4. 「ホーム／編集」タブの「フィル」ボタンから「連続データの生成」オプションを選んでデフォルトのまま「OK」クリック
  5. （追加分）名前ボックスにa2000と入力
  6. 「Ctrlキー」と「Shiftキー」を押しながら「↑キー」を押す
  7. 「ホーム／編集」タブの「フィル」ボタンから「連続データの生成」オプションを選んでデフォルトのまま「OK」クリック

方法３　数式による方法

もっと複雑な規則的数列を入力するには数式入力が必要。複雑でなくても、この方法を覚えておくと柔軟なシート作りができる。

  1. セルa1に1、セルa2に「=a1+1」と入力
  2. 名前ボックスにa1000と入力
  3. 「Ctrlキー」と「Shiftキー」を押しながら「↑キー」を押す
  4. 「ホーム／編集」タブの「フィル」ボタンをクリックして「下方向へコピー」オプションを選ぶ（Ctrlキー＋dというショートカットキーでも同じ）

アクティブセルの移動、選択

連続データの例のようにシートを広く使う場合、マウスのドラッグや、シートの右のスクロールバーを操作するのは煩わしくなるので、一挙に遠いセルに移動できる方法をマスターしておいた方が、作業効率が上がります。

方法1　名前ボックスの利用

シートの左上、数式バーの左隣にセル記号の書かれたところがありますが、それが名前ボックスです。そこにアクティブセルにしたいセル記号を入力すると、そのセルを含むシートの範囲が表示されます。目的セルの番号が分かっていれば、一番手っ取り早い方法でしょう。

方法２　矢印キー＋Ctrlキー

矢印キーは隣のセルにしか移動できませんが、Ctrlキーを押しながら矢印キーを押すと、複数セルをスキップすることができます。↓キーを押した場 合、現在のアクティブセルにデータが入力されていない場合は、下方向に探して、最初にデータの入力されているセルをアクティブセルにします。データが入力 されている場合は、下方向に探して、最初にデータの入力されていないセルのすぐ上のセルをアクティブセルにします。例えばセルA10:A40にデータがあ り、現在のアクティブセルがA4だった場合、Ctrl+↓キーを押すとセルA10がアクティブセルになり、現在のアクティブセルがA13だった場合はセル A40がアクティブセルになります。他の矢印キーを押しても同様です。例えば、現在のアクティブセルがA13で、Ctrl+↑キーを押した場合はセル A10がアクティブセルになります。

Ctrlキーと同時にShiftキーも押しておくと、途中のセルをアクティブにします。

数式を入力している最中でもこの操作は可能です。たとえば、セルA3:A902にデータが入力されていて、セルB3に「=SUM(A3:A902)」と入力したい場合は、

  1. 「=SUM(」と入力してから←キーを押す（「=SUM(A3」と表示される）
  2. 「：」を入力する（「=SUM(A3:A3)」と表示される）
  3. CtrlキーとShiftキーを押しながら↓キーを押す（表示が「=SUM(A3:A902」と変わる）
  4. 「）」と入力する。

方法３　ページダウン、ページアップキーを使う

それほど遠くはないけれど画面上にないセルをアクティブにしたい場合は、ページダウンキー、ページアップキーを使うと、画面スクロールを見続けなくてすむ。意外とストレスレス。

もどる

関数の種類

数学関数

Excelにはたいていの数学関数は揃っています。次のような関数はよく使われるので、覚えておいて下さい。いくつか、特に注意することを特記します。


種類 関数名 用例 備考
対数関数 LN
natural logarithm
べき乗計算 ^ 2^10, A4^3 マイナスより優先度が低い
（「-2^2」は「(-2)^2」と解釈されるので注意）
四捨五入
 ROUND
 ROUND(A2,1)
 絶対値を四捨五入した後に符号を付ける
ROUNDUP, ROUNDDOWN,TRUNCも同様
整数化
 INT
FLOOR
CEILING
TRUNC
その数以下の最大整数（符号は関係なし）
FLOOR(..,1)と同じ
TRUNCは絶対値を切り捨てた後、符号を付ける


主要な関数の一覧表

EXP 指数関数 ネピア数（e = 2.718281828...）のx乗
LN 自然対数関数 ネピア数を底とする対数関数
SIN, COS, TAN, ほか
 三角関数
PI
 円周率
SQRT 平方根 「^0.5」と同じ
ABS 絶対値
MOD, QUOTIENT 整数割り算のあまりと商
SIGN 符号関数 正ならば「1」、ゼロならば「0」、負ならば「-1」とする
ROUND 四捨五入 ROUND(-2.6,0) = -3
INT, TRUNC 切り捨て INT(-2.6) = -3, TRUNC(-2.6) = -2
ROUNDUP, ROUNDDOWN 切り上げ、切り捨て ROUNDUP(-2.6,0) = -3
FLOOR, CEILING 切り上げ、切り捨て FLOOR(-2.6) = -3
FACT 階乗
COMBIN 2項係数
GCD, LCM 最大公約数(gcd)、最小公倍数(lcm)

統計計算用の関数

関数名 内容
関数名 内容
AVERAGE 算術平均 VAR, VARP 分散
AVEDEV 平均絶対偏差 STDEV, STDEVP 標準偏差
GEOMEAN 幾何平均 SKEW 歪度
HARMEAN 調和平均 KURT 尖度
MEDIAN 中央値 QUARTILE 四分位数
MODE 最頻値 CORREL 相関係数
TRIMMEAN 両端外れ値排除後の平均値 COVAR 共分散

関数名 内容
SUM 普通の和
SUMIF 条件に合ったものだけの和
SUMSQ 2乗和（Σi xi^2）
SUMX2MY2 二つの配列のそれぞれの平方和の差（Σi xi^2 - Σi yi^2）
SUMX2PY2 二つの配列のそれぞれの平方和の和（Σi xi^2 + Σi yi^2）
SUMXMY2 二つの配列の要素毎の差の平方和（Σi (xi - yi)^2）

関数名 内容
関数名 内容
COUNT 数値データの個数 MAX, MIN 最大値、最小値
COUNTA 非空白セルの個数 LARGE 降順データの上から何番目
COUNTIF, COUNTIFS 条件抽出 SMALL 昇順データの上から何番目
PERCENTILE 分位点 RANK データの順位
FREQUENCY 度数分布表生成

分布関数

正規分布の密度関数のように、統計で良く出てくる関数もたくさん用意されています。関数名は、分布の名前に続けて「.DIST」「.INV」のよう な語尾を付けて表します。「.DIST」は累積分布関数、密度関数あるいは確率関数、をあらわし、「.INV」は逆関数を表します。

BETA.DIST, BETA.INV ベータ分布関数、その逆関数
CHISQ.DIST, CHISQ.INV カイ2乗分布、その逆関数
EXPON.DIST 指数分布
F.DIST, F.INV F分布、その逆関数
GAMMA.DIST, GAMMA.INV ガンマ分布関数、その逆関数
LOGNORM.DIST, LOGNORM.INV 対数正規分布関数とその逆関数
NORM.DIST, NORM.INV 正規分布関数とその逆関数
NORM.S.DIST, NORM.S.INV 標準正規分布関数とその逆関数
T.DIST, T.INV ｔ分布関数とその逆関数
WEIBULL.DIST ワイブル分布関数
BINOM.DIST, BINOM.INV 2項分布
HYPERGEOM.DIST 超幾何分布関数
NEGBINOM.DIST 負の2項分布関数
POISSON.DIST ポアソン分布関数


例えば、標準正規分布の場合、「NORM.DIST(x, 0, 1, FALSE)」と書くと密度関数のxにおける値、「NORM.DIST(x, 0, 1, TRUE)」と書くと累積分布関数のxにおける値を計算します。

もどる

便利な機能

データテーブル

同じような計算をパラメータの値を変えて計算し、それらを一覧表にまとめる、というような計算は、パラメータを入力する、再計算結果をコピーペーストする、パラメータを入力する、再計算結果をコピーペーストする、の繰り返しですが、それを一つの手順にまとめたのが「データテーブル」機能です。

ゴールシーク

方程式の解を数値的に求めるにはニュートン法のような反復法によるアルゴリズムが使われます。例えば、f(x) = 0 を満たす x の値を数値的に求める場合は、初期値として適当な値x0を決め、|f(x0)| > |F(x1)| となるようなx1を計算し、次いで、|f(x1)| > |F(x2)| となるようなx2を計算し、...ということを繰り返して、f(x*)≒0 として良いような x* を求め、それを解とする方法です。

無限に繰り返せば真の解に収束するというアルゴリズムが使われますが、通常は時間の関係で、ある程度収束したらそこで反復を停止します。また、コンピュータの計算はせいぜい十数桁の精度しかないので、得られる解は真の解ではなく近似解で す。

反復法のプログラムを書くのは結構面倒です。この作業を一つの命令で実行できるようにしたのが「ゴールシーク」命令です。

手順

例えば、cos(x) = x を満たす x を計算したい場合、

  1. セルB3に「=COS(A3) - A3」と入力し、
  2. 「データ／データツール」タブの「What-If分析」ボタンをクリックして「ゴールシーク」オプションを選ぶ
  3. 「数式入力セル」にB3、「目標値に「０」、「変化させるセル」にA3と入力してＯＫボタンをクリック

そうすると、表示されている「ゴールシーク」ウィンドウに「解答が見つかりました」という文字列が表示され、セルA3に0.739062、セルB3に3.79E-05というような文字列が表示されます。表示される数は、コンピュータの設定によって異なります。セルB3の「3.79E-05」はExcel流表記法で、3.79 x 10-6という数を表します。これはcos(x) = x という方程式の解を見つけようとしたのだけれど、左辺−右辺が3.79 x 10-6としたところで計算を止めました、ということを表しています。つまり、求めた方程式の解は 10-6くらいの誤差があるということです。

誤差の設定

ゴールシークで使われる反復法の停止規則は、あらかじめ設定した誤差より改善が見られないことです。誤差を小さくすることによってよりよい解を求めることができます。誤差は次のようにして設定します。

  1. 「ファイル」タブをクリックし、左欄に並んでいる「オプション」メニューをクリックします
  2. 表示される「Excelのオプション」ウィンドウの左欄に並んでいる「数式」メニューをクリックします
  3. 「計算方式の設定」の中にある「変化の最大値」に0.0000000001のような小さい数を入力します。これが誤差の許容限界です（コンピュータ計算には限界がありますから、許容限界を小さくｓれば良いというものでもありません）。

こうしてから、もう一度ゴールシークを実行すると、今度はセルA3の表示は0.739085、セルB3は9.97E-11のような数値に置き換わります。誤差がこれくらい小さければ実用的には十分でしょう。

ゴールシークの計算には誤差がつきものですので、その限界をわきまえて、うまく使いこなすことが必要です。

注意

ゴールが複数ある場合は、スタート地点を人間が決める必要があります。例えば、x3 - x = 0 という方程式の解は、反復法の初期値をどこに取るかで、収束先が異なります。「変化させるセル」を「-2」とした場合の解は「-1」、「2」とした場合の解は「1」となることを確かめて下さい。様子の分からない関数のゼロ点を計算する場合は、あらかじめ、その形状を確認してから、適切な初期値を設定する必要があります。

参考　ニュートン法

ソルバー

いろいろな制約条件の下で目的関数の値を最適化したい、という要求は至る所に出てきます。これを自動的に実行させるのが「ソルバー」と呼ばれる最適化のためのアドインプログラムです。

ソルバーアドインの追加　

ソルバーは「データ」タブの「分析」グループにある「ソルバー」ボタンから起動します。もしそこに「ソルバー」ボタンがない場合は、「アドインを追加する」必要があります。

  1. 「ファイル」タブの左に並ぶメニューから「オプション」を選び
  2. 「Excelのオプション」ウィンドウの左にある「アドイン」メニューを選び
  3. 一番下の「管理」ボックスから「Excelアドイン」を選び、「設定」ボタンをクリックし
  4. 「アドイン」ウィンドウで表示される「ソルバーアドイン」にチェックマークを入れる。

ソルバーを使う

たとえば、x^2 + y^2 - 1 = 0 という条件の下で xy の極値を求める問題を例に説明する。

  1. セルA4に0、セルB4に0を入力する（A4,B4はx,yの値を入力する）
  2. セルC4に条件式「=A4^2+B4^2-1」を入力し、セルD4に関数「=A4*B4」を入力する
  3. 「データ／分析／ソルバー」をクリックして「ソルバー」ウィンドウを表示させ、「目的セル」にはD4、「変化させるセル」にはA4:B4と入 力する（シートのセルをクリックしても良い）
  4. 目標値は「最大値」
  5. 制約条件の「追加」ボタンをクリックして、「制約条件の追加」ウィンドウで「セル参照」にはC4、制約条件には0を入力して、関係演算子は 「=」を選択し、「OK」ボタンをクリックする。
  6. 制約条件の下の枠に「$C$4 = 0」という式が入力されていることを確認して、「実行」ボタンをクリックする
  7. そうすると「ソルバー：探索結果」ウィンドウが表示されて「最適解が見つかりました」と表示されるので、Enterキーを押す。

ソルバーの計算精度

ソルバーも、ゴールシークと同じように反復法によって近似解を求めている。その誤差の上限を小さくすることで、誤差の少ない解を得ることができる。 あまり小さくすると、いつまでたっても収束したと見なされず、計算が終わらないという危険もあるので、ほどほどにする必要がある。

  1. 「ソルバー：パラメータ設定」ウィンドウで「オプション」ボタンをクリックする
  2. 「ソルバー：オプション設定」ウィンドウで、「精度」の枠に適当な小さな数字を入力する

線形計画法

原料 P, Q を使って二種類の製品 A, B を作る。A を一つ作るには P が 6 単位、Q が 2 必要、B を一単位作るには P が 3 単位、Q が 8 単位必要である。製品 A, B はそれぞれ１個当たり 3 単位円、5 単位円の利益がある。原料 P が 120 単位、Q が 96 単位使えるとき、A, B をいくつずつ生産すれば、利益が最大になるか、計算しなさい、という問題のシートの作成手順は以下の通り。

  1. セルC6:6, D6:3, C7:2, D7:8, C9:3, D9:D9, F6:120, F7:96と入力する
  2. セルH6に「=SUMPRODUCT(C6:D6,$C$10:$D$10)」と入力し、それをセルH7,H9にコピーペーストする。
  3. 「データ」タブの「分析」グループから「ソルバー」ボタンをクリックする
  4. 表示された「ソルバー：パラメータ設定」ウィンドウで、「目的セル」を「H9」、「目標値」を「最大値」、「変化させるセル」を「C10: D10」とする。
  5. 「制約条件」の右にある「追加」ボタンをクリックする
  6. 表示される「制約条件の追加」ウィンドウで、「セル参照」を「H6」、「制約条件」を「F6」不等号を「<= 」として「追加」ボタンをクリック
  7. 表示される「制約条件の追加」ウィンドウで、「セル参照」を「H7」、「制約条件」を「F7」不等号を「<= 」として「追加」ボタンをクリック
  8. 「キャンセル」ボタンをクリック
  9. 「ソルバー：パラメータ設定」画面で「実行」ボタンをクリック
  10. 計算が終了して表示される「ソルバー：探索結果」の画面を確認して「OK 」ボタンをクリック

もどる

感度分析、スクロールバー

最適化問題で最適解を見つけたとしても、条件がちょっと変わったときその解がどのように変わるのか、ということを調べることは、最適解の性質を知る上で重要な分析手順です。このように、パラメータの値の変動が目的関数の値に及ぼす影響を調べる分析法を「感度分析」と言います。パラメータの入力されているセルの内容をその都度再入力してEnterキーを押す、ということを繰り返すのは、結構な手間です。その入力を自動化する機能が「スクロールバー」です。

例　ロジスティック写像

f(x) = a x(1-x) という関数に対して、適当な初期値（0より大きく、1より小さい）x0を与えて、xn+1 = f(xn) によって数列を作り、十分大きなところだけ利用して (xn, xn+1)の散布図を描くと、a（3以上4以下）の値によって興味深い。図が描ける。これをスクロールバーの例題としよう。

  1. セルC3に350、セルB3に「=C3/100」、セルB5に「0.1」、セルB6に「=$B$3*B5*(1-B5)」と入力して、セルB6をセルB7:B500にコピーペーストする。
  2. 「開発」タブ「コントロール」グループにある「挿入」ボタンを押して、「フォームコントロール」の中にある「スクロールバー」ボタンをクリックする
  3. そうすると、アイコンが十字形になるので、スクロールバーを配置する場所をマウスドラッグによって指定する。
  4. マウスを右クリックし、「コントロールの書式設定」メニューを選ぶ。
  5. 表示される「コントロールの書式設定」ウィンドで、「コントロール」タブをクリックし、「現在値」を「350」、最小値」を「300」、「最大値」を「400」とし、、「リンクするセル」としてセルC3を指定する。
  6. スクロールバー以外のところでマウスをクリックすると、スクロールバーが動かせるようになり、その動きに応じてセルC3の内容が変化します。それを参照しているシート全体が再計算され、散布図も不思議な結果を表示するようになります。散布図の軸の設定を[0,1]と固定すると比較しやすいでしょう。


もどる

計算の精度

表示桁数

Excelのシートで日本の借金総額（一千兆円）を表示することはできません。あるセル（セルA3とします）に1000兆1000億1000万1234と入力すると、「1.0001E+15」と表示されます。「E+15」は「x 1015」を表すExcelの表記法です。Excelでは桁数が多い数が入力された場合、あるいは計算された場合は、先頭の数字の後に小数点を付けた数と10のn乗の積の形で表すのが標準になっています。

入力した数字をすべて表示させたい場合は、「ホーム」タブ「数値」グループの「小数点以下の表示桁数を増やす」ボタンをクリックします。一回クリックする毎に下の位の数値が表示されるようになります。しかし、「1.00010001000123E+15」までは順調ですが、その次は「1.000100010001230E+15」となって、「４」が表示されません。Excelの数値記憶能力には限界があることが分かります。

計算結果も、15桁を超えると、最後まで正しい計算を実行することができません。たとえば、セルA5に「=A3*10001」と入力すると、「1.000200020002230E+19」と表示され、「小数点以下の表示桁数を増やす」ボタンをクリックしても「０」が追加されるだけです。


もどる


行列計算

行列の四則演算

行列の各要素を同時に計算することができる。

行列の和

たとえば、セルA2:C3とセルA5:C6に行列データが入力されているとき、その和をセルA8:C9に計算するには、

  1. セルA8:C9をアクティブにして、
  2. セルA8に「=A2:C3 + A5:C6」と入力して
  3. ShiftキーとCtrlキーを押しながらEnterキーを押す

そうすると、6個のセルに同時に結果が入力される。

行列の積

行列の積は「MMULT」関数を使う。たとえば、セルA2:C4とセルA5:C7に行列データが入力されているとき、その和をセルA9:C11に計算するには、

  1. セルA9:C11をアクティブにして、
  2. セルA9に「=MMULT(A2:C4, A5:C6)」と入力して
  3. ShiftキーとCtrlキーを押しながらEnterキーを押す

そうすると、 3x3 行列同士の掛け算が計算される。最初の行列の列の数と、2番目の行列の行の数が等しくないとエラーになる。

行列計算用の関数


仕事 関数名 使用例
行列の積 MMULT
逆行列 MINVERSE
内積 SUMPRODUCT
行列式 MDETERM

行列範囲の入力

引数の行列範囲をキーボードから入力するのは間違いの元。矢印キーをうまく利用すると入力ミスを減らすことができます。セルE3に「=MMULT(A3:B4,C3:D4)」を入力する場合を例にとって説明しましょう。

  1. 「=MMULT(」と入力したところで、←キーを押すと表示が「=MMULT(D3」のように変化し、セルD3が点線の四角形で囲まれます。
  2. さらに、矢印キーを操作してセルA3まで移動し、そこで「:」を押してから、→キー、↓キーを押して「,」を押すと、セルの表示が「= MMULT(A3:B4,」と変わります。
  3. 「,」を打つ前にF4キーを押すと「=MMULT($A$3:$B$4」のように絶対参照にすることも出来ます。
  4. 次のC3:D4も同じような手順で、矢印キーを操作することにより、入力することが出来ます。
  5. 最後に「)」と入力して、「Ctrlキー」「Shiftキー」を押しながらEnterキーを押せばおしまい。


連立方程式

連立方程式 Ax = b の解は x = A-1b で求められるので、逆行列をMINVERSE関数で計算し、MMULT関数を使って定数ベクトルとの積を計算すれば良い。

たとえば、3元連立方程式：{3x - y + 3z = 2, 2x + y - z = 1, x + z = 1} の解を求める際のシートの作成手順を示します。

  1. セルA4:C6に係数行列を入力、セルD4:D6に定数ベクトルを入力する
  2. セルF4:F6をアクティブにし、セルF4に「=MMULT(MINVERSE(A4:C6),D4:D6)」と入力し、「Ctrlキー」「Shift キー」を押しながらEnterキーを押す。この結果、セルF4:F6には「0.333333, 1, 0.66667」と表示される
  3. セルG4:G6を選び、セルG4に「=MMULT(A4:C6,F4:F6)」と入力し、「Ctrlキー」「Shiftキー」を押しながら Enterキーを押す。この結果、セルG4:G6にはセルD4:D6と同じものが表示されているはず。これは検算です


もどる

微分方程式

数値微分

システムの状態の時間変化を微分方程式によってモデル化することができます。

バスモデル

冷蔵庫のような耐久消費財の普及過程をモデル化した数学的モデルにバスモデルがあります。市場には、周りの状況に左右されないイノベータと呼ばれる顧客と、所有者の存在に影響を受けて購買を決めるフォロワーという顧客の2種類が存在し、普及率の短期的な増分はこれら2種類の顧客によって定まるパラメータを使った微分方程式モデルで表現できる、としたものです。

dy/dt = (p + q y(t)) (N - y(t))

y(t) が時点 t における購買者の数、N が市場規模、p がイノベータの購買力を表すパラメータ、q がフォロワーの購買力を表すパラメータを表しています。q y(t) はフォロワーがそのときの購買者数が多ければ多いほど購入するという行動を表現し、N - y(t) がかかっていることから、未購入者が減ることによって普及スピードが減少することを表現しています。


SIRモデル

感染症の感染の時間推移モデルとして、SIRモデルがあります。未感染者S(t)、感染中I(t)、治癒者R(t) の時間変化を次の微分方程式で表したモデルです。未感染者は感染中の患者に比例して減少し、治癒者は感染中の患者に比例して増える、という単純なモデルです。a/b を再生産数といい、これが１を超えていると感染は拡がる、という指標になっています。

dS/dt = - a I(t) dt

dI/dt = a I(t) dt - b I(t) dt

dR/dt = b I(t) dt

もどる

シミュレーション

確率実験

乱数の生成

一様乱数

RAND：区間[0,1]の一様乱数

RANDBETWEEN：指定した二つの整数値の間の離散一様乱数

正規乱数

累積分布関数の近似逆関数が与えられているので、それを利用して逆関数法で生成する

指数乱数

逆関数法を使う

一般の分布に従う乱数

確率分布の逆関数が与えられている場合は、逆関数法を利用して、正規分布と同じように変数としてrand()を指定すれば、望みの乱数を得ることが できます。ただし、たいていの場合、逆関数は近似関数なので、乱数も微妙に違う可能性があります。特に、分布の裾の頻度は要注意です。

相関のある2変量正規分布に従う乱数

個別には標準正規分布に従い、その相関が r の二変量正規乱数は次のようにして生成できます。

  1. 標準正規乱数を生成：x = NORM.S.INV(RAND())
  2. 相関のある別の乱数を標準正規乱数から生成：y = x * r + sqrt(1-r^2) * NORM.S.INV(RAND())

もどる