課題
次のような表を作成し、住宅ローンの返済額を求めよ。 (表中のライトブルーの枠内には式を入力する)
| 物件金額 | 35,000,000 | 円 |
| 自己資金 | 6,500,000 | 円 |
| 借入金額 | 円 | |
| 返済期間 | 35 | 年 |
| 年利 | 4.20% | |
| 毎月支払額 | 円 | |
| 年間支払額 | 円 | |
| 年収 | 6,000,000 | 円 |
| 年収に対する割合 |
住宅ローンに代表される金融計算は少し複雑であるが、 エクセルを適切に用いることによって簡単に行うことができる。
本課題の場合は、「財務」分類に含まれる PMT 関数を用いる。
課題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 として、それぞれ試してみよ。
| 5日目 | 表紙 | 7日目 |