2010年12月アーカイブ


今回の計算は、住宅や自動車をローンで購入する場合の資金繰りの見積りに役立てることができます。
利率(定利)、返済期間、借入額から必要な毎月の均等返済額を求めていきます。

PMT関数

=PMT(利率,期間,現在価値[,将来価値][,支払期日])
 ※[ ]内は省略可能

利   率:期間を通じ一定の利率(通常年利)を指定
       月返済額を求める場合は年利を12で割って月利を指定しましょう

期   間:期間全体の支払回数(通常年数)を指定
       月返済額を求める場合は、年数に12を掛けた支払回数を指定しましょう

現在価値:現在の貸付金額を指定
       ローンの計算では借入額を指定

将来価値:貸付の将来額を指定
       ローンの計算では返済完了時の借入残額(ゼロなら省略可)

支払期日:期末支払は0(ゼロ)を指定または省略、期首支払は1(イチ)を指定



1000万円を年利3.5%で20年ローンで借入をすると、毎月の返済額はいくらになるでしょう。
支払期日は毎月末とします。

e0143.jpg

1.計算結果を表示するセルF3をクリックし数式を入力していきましょう
  
  =PMT(C2/12,C3*12,−C4)

2.セルF3に計算結果57,996円が表示されます

e0144.jpg


同じ利率、期間、金額で支払い期日が月初の場合

 =PMT(C2/12,C3*12,−C4,,1)

 計算結果は57,827円になります

※引数1(支払期日)の前にカンマが2つある事に注意しましょう。
  引数(将来価値)の0(ゼロ)が省略されているためにカンマが2つあります。



開始年月日から終了年月日までの日数から、土曜日、日曜日および指定された祭日を除く日数を稼働日数として求めてみましょう。

=NETWORKDAYS(開始日,終了日,祭日)

開始日から終了日までの期間に含まれる稼働日数を返します。
稼働日とは、土曜日、日曜日、指定された祭日や休日を除く日のことです。

開始日:対象期間の初日を日付で指定
終了日:対象期間の最終日を日付で指定
祭  日:祭日や夏季休暇など稼働日数の計算から除外したい日を日付で指定

 ※NETWORKDAYS関数はアドイン機能により追加れる関数です。
  あらかじめアドインより、「分析ツール」をオンに設定して組み込んでおく必要があります。

完成図
e0142.jpg

1.稼働日数を算出するセルD4をクリックしましょう

2.「=NETWORKDAYS(B4,C4、E4:H4)」と数式を入力しましょう。
  稼働日数「18」と計算されました。

3.D4の数式をD15までコピーしましょう。

「1/1」と入力をすると、入力した年の日付が表示されますので、
翌年の稼働日数表を作成する場合は注意しましょう。

売上伝票から、売上伝票明細表を作成すると、伝票番号を指定し
求める明細データを検索することができます。

=VLOOKUP(検索値,データ範囲,列番号,検索の型)
 ※検索値、データ範囲のセル範囲は、数式を他のセルにコピーするために
   絶対参照にしましょう。
 ※データ範囲を選択する際、表の見出し(項目名)は含めません。
 ※列番号の数値の意味
   選択したテーブル範囲の左端列から、検索したい列を1、2、3と
   数えていきます。

 検索値:表の左端列で検索したい値を指定します
      左端列のデータは必ず昇順に並べることが必要です
 範  囲:検索対象となるデータの範囲を指定
 列番号:検索値のあるレコードで検索したいデータの列番号を
      左端列から1,2,3と数えた数で指定
 検索型:TRUEまたはFALSEを指定
      省略するとTRUEが指定されます
      TRUE−検索値が内場合は検索値未満で最大の値を選択
      FALSE−検索値と完全に一致する値だけが検索される
            (見つからない場合はエラー値「#N/A」が返される)


完成例
e0140.jpg

1.検索するための検索表を準備しましょう
  7行目の項目名称を2行目にコピーしましょう

2.関数を入力していきましょう
  セルC3をクリックし以下の数式を入力しましょう
  =VLOOKUP($B$3,$B$8:$I$277,2,FALSE)
  ※画像では、見えませんがデータは277行目まで入力されています。

3.数式をI列までコピーし、列番号を修正しましょう
  (セル表示が日付に変わってしまいますので、通貨スタイル等に変更しましょう)

伝票番号がない場合
e0141.jpg

伝票番号(検索値)がない場合は、上記のようにエラー表示されます。

伝票番号がない場合にエラー表示をせずに、「欠番」と表示する場合には、
IF関数とISERROR関数を組み合わせます。

=IF(ISERROR(VLOOKUP($B$3,$B$8:$I$277,2,FALSE)),"欠番",VLOOKUP($B$3,$B$8:$I$277,2,FALSE))

IS関数
 ISが付いているIS関数と呼ばれる関数がExcelには9つあります。
 IS関数はセルの内容をチェックし、TRUEまたはFALSEの戻り値を返します。
 IF関数と併用し活用できます。

ISBLANK(テストの対象)
 テストの対象が空白の時TRUEを返します

ISERR(テストの対象)
 テストの対象が#N/Aを除くエラー値の時TRUEを返します

ISERROR(テストの対象)
 テストの対象が任意のエラー値の時TRUEを返します

ISLOGOCAL(テストの対象)
 テストの対象が論理値の時TRUEを返します

ISNA(テストの対象)
 テストの対象がエラー値#N/Aの時TRUEを返します

ISNONTEXT(テストの対象)
 テストの対象が文字列でない項目の時TRUEを返します

ISNUMBER(テストの対象)
 テストの対象が数値の時TUREを返します

ISREF(テストの対象)
 テストの対象がセル範囲の時TRUEを返します

ISTEXT(テストの対象)
 テストの対象が文字列の時TRUEを返します

各関数は、上記解説以外の時はFALSEを返します


VLOOKUP関数の利用方法
 伝票番号あるいは製品コード等の項目を使うと、問い合わせに役立ちます。
 同じ番号が発行されないように番号発行管理上の注意も必要になります。
 

IF関数
 =IF(論理式,真の場合,偽の場合)
  条件により異なる内容を出力します。
  論理式が成り立つ場合は、真の場合の値を返し、成り立たない場合は、
  偽の場合の値を返します。

法人部門の売り上げが150以上であれば「優」150未満であれば「良」と
E列に表示されるようにしましょう。

e0138.jpg

1.セルE5に数式を入力していきましょう。
  =IF(B5>150,"優","良")
 
  e0139.jpg

  ※「真(TRUE)の場合」=論理式が成り立つ場合
    「偽(FALSE)の場合」=論理式が成り立たない場合

  ※文字列や記号を入力し、別のテキストボックスをクリックすると自動的に
    ダブルコーテーションで
    囲まれ「"優"」のように表示されます。

  ※式の意味
   セルE5に入力した数式「=IF(B5>150,"優","良")」は
   セルB5が150以上ならば優を表示し、150以上が成り立たない場合は
   良を表示するという意味です

  ※「""」と入力した場合は空(NULL)を表示、または空のセルという意味

端数処理


10円単位や100円単位など切りのよい数値に揃えたい場合に使用する関数を学習します。

CEILING関数
 =CEILING(数値,基準値)
   数値をはさむ基準値の倍数のうち、0より遠い値を返します
   (絶対値で比べて大きいほうの値)
   例) =CEILING(125,10)
      =130
       この場合、120をはさむ10の倍数は120か130です
       このうち0より遠い値は130です

FLOOR関数
 =FLOOR(数値,基準値)
   数値をはさむ基準値の倍数のうち、0に近い値を返します
   (絶対値で比べて小さいほうの値)
   例) =FLOOR(125,10)
      =120
       この場合、120をはさむ10の倍数は120か130です
       このうち0に近い値は120です

MROUND関数
 =MROUND(数値,基準値)
  基準値の倍数になるように四捨五入を行います
  ※この関数を使うには、分析ツールを組み込まなくてはいけません
  例) =MROUND(125,10)
     =130
       この場合、数値の125が10の倍数になる四捨五入の値は130です


e0137.jpg

1.セルE7の合計金額を100円単位で四捨五入してみましょう。
  セルE9に数式を入力してみましょう。
  =CEILING(E7,100)

2.セルE7の合計金額を100円単位で切り捨てしてみましょう。
  セルE10に数式を入力してみましょう。
  =FLOOR(E7,100)

3.セルE7の合計金額を100円単位で切り上げてみましょう。
  セルE11に数式を入力してみましょう。
  =MROUND(E7,100)

※EVEN関数
  数値を切り上げて最も近い偶数にします
  =EVEN(数値)

※ODD関数
  数値を切り上げて最も近い奇数にします
  =ODD(数値)



見積計算書において、合計金額の小数点未満を四捨五入、切り捨て、切り上げた値を求めてみましょう。

小数点の特定の桁数以下を四捨五入したり、切り捨てしたり、切り上げをしたりしますが、今回はこれらを行う関数をご紹介します。

四捨五入の場合
 =ROUND(数値,桁数)
  数値を四捨五入し、指定された桁数にします

切り捨ての場合
 =ROUNDDOWN(数値,桁数)
  数値を指定された桁数にして、残りを切り捨てます

切り上げの場合
 =ROUNDUP(数値,桁数)
  数値を指定された桁数にして、残りを切り上げます

※数値=四捨五入、切り捨て、切り上げをしたい数値
  桁数=数値の結果の桁数を指定
      桁数に0を指定すると数値は最も近い整数として計算されます
      桁数に負の数を指定すると、数値は小数点の左側(整数部分)で、
      桁数に正の数を指定すると、数値は小数点の右側(小数点以下)で計算されます。

e0136.jpg

1.セルE9の合計を四捨五入した値をセルE9に算出してみましょう。
  セルE9に数式を入力します。
  =ROUND(E7,2)

2.セルE9の合計を切り捨てした値をセルE10に算出してみましょう。
  セルE10に数式を入力します。
  =ROUNDDOWN(E7,2)

3.セルE9の合計を切り上げした値をセルE11に算出してみましょう。
  セルE11に数式を入力します。
  =ROUNDUP(E7,2)


※整数にしたい場合に使用するINT関数
  =INT(数値)
  数値を超えない最大の整数を算出します。
  

SUBTOTAL関数を用い、引数を変えるだけで、
「件数」「平均値」「最大値」「最小値」「標準偏差」「分散」などの集計値を
算出することができます。

数式は
 =SUBTOTAL(集計方法,範囲)

集計方法の指定番号と算出される集計値
 指定 
 番号 
算出される集計値  同じ機能の関数 
1  平均値  AVERAGE
2  数値データの個数   COUNT
3  空白でないセルの個数   COUNTA
4  最大値  MAX
5  最小値  MIN
6  すべての引数の積   PRODUCT
7  引数を標本とみなした標準偏差   STDEV
8  引数を母集団とみなした標準偏差   STDEVP
9  合計  SUM
10  引数を標本とみなした分散  VAR
11  引数を母集団とみなした分散  VARP


下図のように、右の成績表をもとに、左表の集計値一覧表を作成してみましょう。

e0135.jpg

1.セルC5に数式を入れて国語の件数を求めてみましょう。
  「=SUBTOTAL(2,$H5:$J12)」

2.下方向にセルC5の数式をコピーしてみましょう。
  すべて、件数が表示されていますので、集計方法を修正してみましょう。
  平均=1 最大値=4 最小値=5 標準偏差=7 分散=10
  数式を修正したら、右へ全て数式コピーしましょう。
  上図の左側の「集計値一覧表」のように表示されましたね。


※調査データ等の場合、オートフィルタを設定すると、リストを抽出して非表示になった行は
  無視されるため、抽出リストだけの集計値を表示することができます。


2011年10月

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31