課題
課題16で作成した表を用いて、毎月の支払額を12万円とするために、必要な自己資金を データ分析機能(ゴールシーク)を用いて求めよ。 課題16の表の「自己資金」以外の赤字は既定値とする。
一変数関数の目標値を与えて、該当する変数値を求める機能である。 数学的には方程式を解くことに対応するが、解析的に解くわけではなく、数値的に求めるため、幾つかの注意が必要である。 幾つかの例題を考える。
周囲の長さが250cm であるような、正方形の1辺の長さを求めよ。
|
右のような表を作成する。 ライトブルーのセルには、式を代入すること。 本例題の場合は、正方形の全周と1辺の長さの関係であるから、 内容は "=B2*4" である。 |
| ||||||||||||||||
標準ツールバーの「ツール(T)」→「ゴールシーク(G)」を起動する。 あらかじめ数式の入っているセルをアクティブにしておくと、入力を減らすことができる。
|
数式入力セルには、目標値を得たい「式」が入力されているセルを、 目標値にはその値を、変化させるセルには「式」から参照されている変数のセル(B2)を 代入する。(マウスで選択すると絶対参照で「$B$2」と代入される。) |
|
|
「OK」を押すと、解の探索が始まり、答が得られると、元のセルの値が変更される。 |
|
面積が144cm2であるような、正方形の1辺の長さを求めよ。
代入される式が変更されるだけで、行うことは例題 1の場合と同様である。
|
ある直角三角形がある。 直角をはさむ2辺の和は 19cm であり、面積は 42cm2である。 直角をはさむ2辺の長さを求めよ。 例題 1), 2) と異なり、少し工夫が必要である。 問題文より、底辺を a, 高さを b, 面積を S とすると、
a+b=19
S=a×b÷2 の二式が成立するので、b を消去して、S=a(19-a)/2 とした上で、例題 1), 2) と同様に解けばよい。 |
|
式の変形等の作業を行わず、エクセルのセル参照機能を有効に用いることを考えた場合は、 次のような手順を取ることになる。
次のような表を作成し、ブルーのセルに適切に式を設定する。
| A | B | C | |
| 9 | 直角三角形 | ||
|---|---|---|---|
| 10 | 底辺 | 1 | cm |
| 11 | 高さ | "=19-B10" | cm |
| 12 | 面積 | "=B10*B11/2" | cm |
ゴールシークを起動し、数式入力セルに「B12」、目標値に「42」、 変化させるセルに「B10」を指定する。
底辺の値として、小さな値(たとえば 1)を指定した場合と、大きな値(たとえば 18)を指定した場合の 結果の違いを観察せよ。
「ゴールシーク」は、数学的には
という関係があるとき、y0 を与えることにより
を求めることに相当するが、幾つかの制約があるので注意する必要がある。
用いることができる独立変数は一つである。
独立変数が複数ある場合は「ソルバー」を用いる。
(例題3の場合は独立変数は 1であることに注意すること)
解析的に解いているわけではなく、「探索」であるので、解は近似値であり、 たとえ「解答が見つかった」としても正しくない場合がある。 例として
1/x = 0
解は実数の範囲でのみ探索され、複素数となる場合は「解答がみつからない」となる。 例として
x2+1 = 0
解が複数ある場合でも一つの解しか求まらない。 複数の解があるかどうかを判断するためには変化させるセルに 探索のための初期値を与えてみて調べる必要がある。 例として
x3- 4x2 - 7x + 10 = 0
を初期値を +10, -10, 2 として、それぞれ試してみよ。
課題 16 で作成した表にソルバーを適用することで、次の値を求めよ。
多変数関数の目標値を与えて、該当する変数値を求める機能である。 課題17で学習した「ゴールシーク」は、 独立変数が一つにしか対応していなかったのに対し、「ソルバー」は 複数の独立変数を変化させることで、最適値を求めることができる。
さらに、最適値を求める際に、さまざまな制約条件を課すことができる点も異なる。
なお、「ゴールシーク」と同様、数値的に求める(内部での実現方法は異なる)ので、 注意が必要である。
「ソルバー」は標準ではインストールされていないので、「ツール」メニューに 「ソルバー」メニューがない場合は、「ツール」→「アドイン」を選択し、 ソルバーアドインを登録する必要がある。
鶴とカメがいて、頭の合計と足の合計からそれぞれ何羽(匹)いるかを求める問題として有名。 原形は今から1800年ほど前の、中国 漢・晋の時代の《孫子算経》の中に見いだせる。 元々の文章である次の問題に答えよ。
「今有雉兎同籠 上有三十五頭 下有九十四足 問雉兎各幾何」
（いま、キジとウサギが同じ籠に入っている。上には35の頭があり、下には94の足がある。
キジとウサギはそれぞれ何羽いるか？）
|
右のような表を作成する。(シート名は solver 1 とせよ) ライトブルーのセルには、式を代入すること。 言わずもがなであるが、ウサギは1羽につき、頭 1, 足 4 であり、 キジは1羽につき頭 1、足 2である。
表に正しく式を設定すれば、この問題は
「 |
|
||||||||||||||||||||||||||||||
ちなみに、本問の場合、「頭」欄は不要かも知れないが、もし問題に出てくる動物が「やまたのおろち」だの、 「ケルベロス」だのに変わった場合は必須である。
「ツール(T)」→「ソルバー(V)」を起動し、目的セルを C5(クリックで選択すると絶対参照となる)、 目標値を「値 35」、変化させるセルに B3, B4 を設定する。
「オプション(O)」をクリックすると、解の探索方法の細かな指定ができる。
|
目的セルを設定しただけでは、まだ問題は解けない。 上の手順では、まだ足の数が設定されていないので、その条件を指定する必要がある。 そのためには「制約条件」の中の「追加(A)」タブをクリックし、 現れる右のウィンドウで条件を指定する。 ここで注意するべきことは条件として、任意の式で書けるわけではなく、 ある「セル」の値についての条件を指定するという点である。 実際には、制約条件側に式を書く事も可能であるが、 あらかじめ条件を表現したセルを準備したほうが判りやすいと思われる。(D5 に相当) |
|
||||||||||||
|
例
真ん中のリストボックスで選択する「演算子」と制約条件に書くことのできる内容は右の表のようになる。 |
|
複数の条件を指定する場合は、「追加(A)」を、終了する場合は「OK」をクリックする。
条件の確認および実行
制約条件などを確認した上で、「実行(S)」をクリックする
|
「実行(S)」を押すと、解の探索が始まり、答が得られると、 セルに答えが表示される。 実際にセルに反映させるには「解を記入する(K)」を選択して「OK」を押す。 この操作は元に戻せないので、注意すること。 「OK」をクリックする前に、「レポート(R)」を選択しておくと、 得られた解についての初期値および最終結果、制約条件の満足度の 解の探索に関するレポートが別シートに作成される。 |
|
ある工場で、生産している製品の作成工程は大きく３つにわけることができ、 それぞれの工程で100個の製品を作成するのにかかる時間は次の表の通りである。
| 工程名 | 社員 | アルバイト |
| 工程1 | 4 | 6 |
| 工程2 | 2 | 6 |
| 工程3 | 8 | 12 |
なお、勤務時間は1日8時間、社員の日給は 12,000円、アルバイトの日給は 6,000円である。
現在、7人の社員と36人のアルバイトを次のように配置して、 1日に1,600個の製品を生産しており、人件費は 300,000円/日 となっている。
| 工程名 | 社員 | アルバイト | 生産能力(個/日) |
| 工程 1 | 2 | 9 | 1,600 |
| 工程 2 | 1 | 9 | 1,600 |
| 工程 3 | 4 | 18 | 1,600 |
| 合計 | 7 | 36 | 1,600 |
不況のため、アルバイトを減らして人件費の削減を行いたい。 ただし、生産能力は落としたくないし、どの工程にも責任者として社員を最低一人は配置したい。 どのように人員配置を変更すれば良いか？また、その時の一日辺りの人件費はいくらか？
なお、解答には solver 2 というシートを用いよ。
|
右の図のような表を作成すると判りやすい(と思う)。 ライトブルーのセルには式が入力されている。 1日の生産能力は
1日の勤務時間÷100個作るのに必要な時間×100×配置される人数
の和(社員およびアルバイト)である。 目的セルは「総人件費」であり、目標値には「最小値」を選択する。 変化させるセルは、各工程に配置される人数を入力。 制約条件には次のものを指定する。
|
|
ある牧場で、馬を27頭放牧すると、6週間で草が食べ尽くされ、 23頭放牧した場合は 9週間で食べ尽くされるという。 21頭放した場合は何週間で食べ尽くされるか？
ただし、放牧する前に草は生えそろっているものとする。
なお、解答には solver 3 というシートを用いよ。
| 6日目 | 表紙 | 8日目 |