いろいろな関数　11

主な内容
１．参照元のトレース、参照先のトレース
２．PMT 関数について
３．ゴールシーク

ワ−クシート分析

参照元のトレース、参照先のトレース

大きな表で、関数を使い「セル参照」を行っている場合、どこを参照しているのか分らなくなってしまうことがあります。
[Ctrl＋Shift＋＠]キーで数式を表示しても、絶対参照の[$]付きなどでは、参照元を判断するのに一苦労ありそうです。特に何十列にもなっていると行番号より探すのが大変です。

そんなとき、「参照元のトレース」や「参照先のトレース」（矢印表示）をしてくれる機能がエクセルにはあるのです。

参照元のトレースの図

参照先のトレースの図

PMT 関数とゴールシーク

PMT 関数について

定額の支払いを定期的に行い、利率が一定であると仮定して、貸付に必要な定期支払額を算出します。

書式　PMT(利率, 期間, 現在価値, 将来価値, 支払期日)
利率　　　：　貸付期間を通じて一定の利率を指定します。
期間　　　：　貸付期間全体での支払回数の合計を指定します。
現在価値　：
現在の貸付額、つまり将来行われる一連の支払いを現時点で一括払いした場合の合計金額、または元金を指定します
将来価値　：
貸付の将来価値、つまり最後の支払いを行った後に残る現金の収支を指定します。将来価値 を省略すると、0 (ゼロ) を指定したと見なされ、貸付額の将来価値は 0 になります。
支払期日　：
支払いがいつ行われるかを、数値の 0 または 1 で指定します。支払期日を省略すると、0 を指定したと見なされます。
支払期日支払いが行われる時期　：
0 各期の期末
1 各期の期首
解説
使用例
（１）100 万円を年利 8% の 10 か月ローンで借り入れた場合、毎月の返済額は、 次のようになります。(手順は)下記（２）同様です。
PMT(8%/12,10,1000000) = \-103,703 
借入額	1,000,000
期間(月)	10
利率	8%
支払額	\-103,703
（２）同じ設定のローンで支払いが月初 (期首) に発生する場合、毎月の返済額は、 次のようになります。
PMT(8%/12,10,1000000,0,1) = \-103,016 
借入額	1,000,000
期間(月)	10
利率	8%
支払額	\-103,016

PMT関数　数式パレットの図 「パレットを上から埋めていくだけです。」

（３）人から借金を申し込まれ、年利 12% の 5 か月払いで 50万円を貸した場合、
毎月の受取額は、次のようになります。
PMT(12%/12,5,-500000) = \103,020 
貸金額	500,000
期間(月)	5
利率	12%
月受取額	\103,020
（４）18 年間で 500 万円をためることを目標に、毎月一定の金額を預金することにしました。毎年 6% の金利が期待できる場合、
毎月の預金額は、次のようになります。
PMT(6%/12,18*12,0,5000000) = \-12,908
（年利 6% の口座に毎月 12,908 円を預金すると、18 年後には 500 万円ためることができたのです。 ー日本の良き時代の話でした。） 
目標額	5,000,000
期間(月)	12
利率	6%
月預金額	\-12,908

PMT 関数の引数および財務関数の詳細については 下記PV 関数（借入可能額を出す関数）を参照してください。

参考：PV 関数

投資の現在価値を返します。現在価値とは、将来行われる一連の支払いを、現時点で一括払いした場合の合計金額をいいます。たとえば、借金をした場合、借入金額の合計が、貸方に対する現在価値となります。

書式　PV(利率, 期間, 定期支払額, 将来価値, 支払期日)
ゴールシークとソルバー
ゴール シーク使用の場合

目標の値を数式から得るために数式に代入すべき値を求めるには、ゴール シークを使います。ゴール シークを使うと、数式の値が目標の値になるように、ある「特定のセルの値」が求められます。

ソルバー使用の場合

ソルバーを使うと、複数の制約条件に基づいて「複数のセルの値」を変化させ、特定のセルの値を決定できます。ソルバーを使うと、数式の結果が目的の値になるように、「指定したセルの値」（複数のセル値が）が調整されます。

ゴールシーク

PMT 関数で処理された表の場合

数式の計算結果が目的の結果（値）になるように、数式に代入する値の最適値を求める方法。
ゴールシークを実行すると、指定したセルを参照する数式が、目的値を返すまで、その参照元のセルの値が変化し、解が求められます。

特定セルの値を指定して他の１つのセルの値を変化させ、指定した値に収束する解を求める場合は[ツール]メニューの[ゴールシーク]コマンドを使います。

例題 1-0 下記契約モデルが有ったとします
借入額	10,000,000
期間(月)	180
利率	7.02%	
支払額	\-90,000	=PMT(B3/12,B2,B1)
例題 1-1　（借入額を増やします。）

若し、月々の返済額が100,000円まで可能なら、幾らまで借金できるか？。 これを、ゴールシ−クを用いて、「特定セルの値を指定」（支払額を 100,000に）し、「他の１つのセルの値」（借入額）の値を変化させます。

  1. [ツール]メニューの[ゴールシーク]をクリックします。
    ゴールシークダイアログボックスが表示されます。（下図参照）
  2. [数式入力セル] ボックスに、解を探索する数式が入力されているセルの参照を入力します。
    これは勿論[B4]セルになります。
  3. [目標値] ボックスに、目的の計算結果を入力します。月々１０万円返済可能としました。 ここで大事なことは数値に[-]負号を付けることです。特に、下記利率を変化させるとき、[-]を付けないと上手く行きません。
    （このセルは、[数式入力セル] ボックスで指定したセルの数式によって参照される必要があります。）
  4. 「変化させるセル」は「幾らまで借金が可能か」になりますので、借入額の[B1]セルになります。
  5. 回答は「11,111768円」になりました。

ゴールシーク ダイアログ１

ゴールシーク ダイアログ２

この例では、セル B4 の支払額を 100,000 にしたとき、ゴールシークによって、セル B1 の借入額が変化します。また、PMT 関数が使用されています。 

借入額	11,111,768
期間(月)	180
利率	7.02%
支払額	\-100,000
例題 1-2　(利率を変化させます。)

この例では、10000000円を借り入れ、180ヶ月間、月々80000円の返済で完了するとした場合の利率を計算します。 

借入額	10,000,000				
期間(月)	180				
利率	5.18%				
支払額	\-80,000	[B1][B2]の条件そのままで、\-89995を左に
		　　　　変更したとき、利率は[7.02%]から[5.18%]に。

ゴールシーク ダイアログ２-２

（PMT 関数には無関係）の一般売上表の場合

メロン販売現状は５万円の赤字です。１０万円の利益を出すためには？
　　（数値が不揃いですみません。）

（１）[○]中央列のように、人件費削減ー一番分り易い解決策
（２）[○]右列のように、仕入価を値切る方法
（３）[×]売る上げ個数を増やす方法も算出できますが、それに伴う「仕入価格も増やさなければなりません」ので、ゴールシークでは、正確な解答にはなりません。」） 

メロン販売現状		労務費削減		仕入単価を安くする
単価	　2,700		単価	　2,700		単価	　2,700
売上個数　　800		売上個数　　800		売上個数　　800
売上高	2,160,000	売上高	2,160,000	売上高	2,160,000
							
仕入単価　2,000		仕入単価　2,000		仕入単価　1,813　△
仕入個数　800		仕入個数　800		仕入個数　800
仕入額	1,600,000	仕入額	1,600,000	仕入額	1,450,000
粗利益	560,000		粗利益	560,000		粗利益	710,000
							
労務費	450,000		労務費	300,000	△	労務費	450,000
土地借料　50,000		土地借料　50,000		土地借料　50,000
広告料	 30,000		広告料	 30,000		広告料	 30,000
梱包材料　60,000		梱包材料　60,000		梱包材料　60,000
諸雑費	 20,000		諸雑費	 20,000		諸雑費	 20,000
経費	610,000		経費	460,000		経費	610,000
							
純利益	\-50,000	純利益	\100,000	純利益	\100,000

◇注意点◇
「純利益＝粗利益ー経費」でセル参照で計算する必要があります。
経費のなかに労務費が、セル参照で含まれる必要があります。
仕入額の算出には「仕入単価」がセル参照で含まれ、その仕入額を要因として「粗利益」が算出されていなければなりません。
早い話が、数値直接記入ではなく、できる限り「セル参照」で計算するということです。


トップへ戻る     いろいろな関数目次へ戻る
いろいろな関数10へ戻る     EXCEL関数の勉強１へ進む

inserted by FC2 system