PMT 関数で返済額を計算する 最初に、 PMT 関数で第 1 期目分の返済額を求めます。 PMT 関数は、「 =PMT 」の後に続く「 ( ) 」内に指定した 利率、返済期間、借入金額の情報を使って、元利均等払い での返済額を求めます。入力方法は、 1 つ前の項目で説明 した方法と同じですが、 こでは最後に数式をコピーする ため、セルを絶対参照形式にします。 ①入力するセルの選択 まず、セル B8 を選択します。 ②関数名の入力 「 =PMT( 」を入力します。 ③利率の指定 カーソルが「 ( 」の直後にあることを確認して、利率が 入力されているセル C3 を選択します。 =PMT(C3 SLIM 1 、ローン返済シミュレーシゴン ( 元利均等 ) 2 3 ー 1 4 1 2 5 1 000000 6 7 9 ①セル B8 を選択する一②「 =PMT( 」 を入力する ¯PMT(03 っ 4 0 望 4 3 3 セル C3 を 選択する ④参照するセルを絶対参照へ切り替え あとでコピーした際に参照先がすれないように、キー を 1 回押して絶対参照に変更します。「 C3 」だった表示が 「 $ C $ 3 」となります。 244
6 章日付と時刻の処理に役立つ関数 ・〉 1 カ月後の日付を求める 購入日の 1 カ月後の日付を求める 顧客に商品の購入日から翌月の同じ日まで有効なサービ スを提供するとします。商品の購入日によって異なる有効 期限を求める必要がありますが、これを簡単に調べること はできないでしようか。こういうときは EDATE 関数を 使います。この関数を使うと、指定日を基に指定した月数 EDATE 後の日付を求められます ( 図 A) 。 04 販促サービス有効期限一 1 2 3 4 5 6 7 8 9 10 1 1 1 2 = EDAT 日日 4.1 ) た月数後の日付を求められる EDATE 関数を使うと、指定し ID 01 2351 0 01 2351 1 01 2351 2 01 2351 3 CI 2351 4 CI 2351 5 CI 2351 6 0123517 201 0 / 1 / 1 5 201 0 / 1 / 30 201 0 / 2 / 5 201 0 / 2 / 1 0 201 0 / 3 / 5 201 0 / 3 / 1 2 201 0 / 3 / 1 日 201 0 / 3 / 21 1 / / 1 5 201 0 / 2 / 2 日 201 0 / 3 / 5 201 0 / 3 / 1 0 201 0 / 4 / 5 201 0 / 4 / 1 2 201 0 / 4 / 1 8 201 0 / 4 / 21 こでは、サービスの有効期限が 1 カ月と仮定して、そ ExceI 2003 ではアドインの「分析ツール」が必要となります ( 付録 5 ) こで使う関 れぞれの購入日から有効期限の日付を求めます。 ~ 0 = 第物第第を =EDATE( 開始日 , 月 ) 0 0 ィーテート EDATE 開始日を基に指定した月数後の同 じ日を求める 0 ・・開始日が入力されたセルを指定する 3 ・開始日から何カ月後を求めるか指定する 187
M ATC H INDEX 22 一覧表にあるデータを取り出す 配達料金表から料金を求める ここに配達料金表があります。重量と配達する地域に応 じて、料金が変わるというよくある料金表です。この表を 使って重量と配達先に応じた料金を調べます。このとき、 自分の目で調べると、表の重量と配達先の見出しを確認 し、その先で交差する位置にあるセルを見るはずです。調 べる件数が少なければそれほど問題ではないのですが、ま とめてたくさんの件数を調べるときなど、 1 つ 1 つ目で確 認するのは手間になります。 このようなとき、自分の目でセルの位置を確認する代わ りに、 MATCH 関数でセルの位置を調べることができま す。調べたセルの内容は、 DEX 関数を使って取り出す ことができます ( 図 A) 。 。配達料金表 図 A =INDÜ<()5 ・ ll 0. MATCH()I . A5 : AI 0.0 ). MATCH ( F2. 日 4 : 14.0 2 3 4 5 2k 6 5k 8 20 9 50k 10 100k 12 13 1 4 15 1 6 17 18 19 20 22 23 170 重量 20kg 配達先関西・中国 北海道 2000 2500 3000 3500 4000 5000 東北 158 2000 2500 3000 3500 4000 関東 1000 1500 2000 2500 3000 3500 中部 - 北 1000 1500 2000 2500 3000 3500 関西・ 1500 2000 3500 4000 4500 四国 2000 2500 3000 3500 4000 5000 事斗金 九州 2000 2500 3000 3500 4000 508 3500 沖縄 2500 3000 3500 4000 4500 5000 MATCH 関数と INDEX 関数を使うと、 一覧表からデータを取り出せる
指定した数値の平均を求める 次に取り上げるのは AVERAGE 関数です。これも 1 章 で簡単に触れましたが、 AVERAGE 関数は指定した数値 の平均を求める関数です。 AVERAGE c.- こで使う関ー アベレージ AVERAGE 指定したセル範囲の平均値を求め る =AVERAGE( 数値 1 , [ 数値 2 レ・・ ) 0 0 0 ・・数値やセル番地、セル範囲を指定する あるお店の来店者を対象に、購入金額のアンケートを取 ったとします。このアンケート結果から、商品の購入金額 の平均を求めてみます ( 次ページ、図 A)O ①入力するセルの選択 ます、セル DI 6 を選択します。 ②関数名の入力 「 =AVERAGE( 」を入力します。 ③引数とする ( 平均を求める ) セル範囲の指定 カーソルが「 ( 」の直後にあることを確認して、セル D3 ~ D14 をドラッグします。このとき「 ( 」に続けて「 D3 : D14 」と表示されます。これで平均を求めたい範囲が指定 されました。 ④ D 」の入力 引数の指定が終了したので「 ) 」を入力します。 36
スモール こで使う関 SMALL =SMALL( 範囲 , 順位 ) 0 0 セル範囲の中で、下から数えて指定 した順位となる数値を表示する 0 ・・調べたい数値が入力されているセル範囲を指定する 0 ・順位を指定する LARGE 関数は「 =LARGE 」の後に続く「 ( ) 」内に指 定したセル範囲内で、大きいほうから数えた指定順位とな るデータを求めます。 SMALL 関数は「 =SMALL 」の後 に続く「 ( ) 」内に指定したセル範囲内で、小さいほうか ら数えた指定順位となるデータを求めます。 ベスト 3 とワースト 3 を一度に求めることはできませ ん。 1 位と最下位の売上額を求めてから、数式をコピーし て 2 位 ( ワースト 2 位 ) 、 3 位 ( ワースト 3 位 ) を求めま す。 1 位の売上額を求める ①入力するセルの選択 まず、セル C10 を選択します。 ②関数名の入力 「 =LARGE( 」を入力します。 ③順位を調べたいセルの指定 カーソルが「 ( 」の直後にあることを確認して、順位を 調べたい数値のあるセル範囲を指定します。ここでは、セ ル B4 ~ D7 をドラッグします。このとき「 ( 」に続けて 「 B4 : D7 」と表示されます。 124
条件に合う数値を平均する 男女別の平均購入金額を求める 来店者の購入金額を記録した表から、男女別に平均購入 金額を求めるとします。「男」または「女」という条件に 合う数値を平均するときは、 AVERAGEIF 関数を使いま す ( 図 A) 。 1 来店者記 2 4 6 7 9 1 0 13 14 AVERAGEIF ・均購 購 金額 5 000 4 300 1 5 000 1 2 1 00 25 000 1 5 200 日 000 2 500 3 300 9 日 00 1 0 600 1 9 日 00 入力方法は、 この章ですでに紹介した SUMIF 関数とほ とんど同じです。 ここで使ら藺数 Excel 2003 では使えません 条件が満たされるデータと同じ行 にある数値の平均値を求める = AVERAG 日 F ( 範囲 , 検索条件 , [ 平均対象範囲 l) 0 0 0 ・・条件が満たされているか調べるセル範囲を指定する 0 ・・条件にする文字列や数値、論理式などを指定する ・・・平均を求める数値が入力されているセル範囲を指定する ( 入 力を省略すると 0 内の数値の平均値が求められる ) 1042 日 57 っ凵 4 ・つ凵っこっ凵ーのこ 4 ーっ凵 4 ーの′一つ」 男の平均購入金額 を求められる アベレージイフ AVERAGEIF 100
複数の条件に合う 数値を平均する 男女別に 20 代の平均購入金額を求める AVERAGEIFS 先ほどは、「男」か「女」かを条件にして AVERAGEIF 関数を使って平均購入金額を求めました。今度は、「 20 代 の男の平均購入金額」という具合に、年齢層を条件に加え る場合を説明します。 2 つ以上の条件で平均を求めるとき は、 AVERAGEIFS 関数を使います ( 図 A)O 図 A 1 来店記録 7 ミ女 切第女 10 12 13 1 5 1 6 18 20 21 22 23 24 22 51 3 日 24 33 1 6 1 8 41 55 20 43 23 3 500 4 300 7 500 6 000 4 300 1 500 3 00 2 500 3 500 1 00 9 日 00 1 日 00 平物購入 . 額 1 0 は、 20 イ、 30 イ、 40 代 50 代以 20 代男性の平均購入金額 を求める ここでは、 20 代男性の平均購入金額を求めます。入力 106 歳未満」の 2 つの条件が必要になります。 つです。「 20 代」を条件とする場合、「 20 歳以上」と「 30 設定する条件は、「男」と「 20 歳以上」「 30 歳未満」の 3 条件に合う数値を合計する ) とほとんど同じです。 方法は、この章ですでに紹介した SUMIFS 関数 ( 複数の
こでは、月々の返済額と元金分、金利分の割合を確認 しやすいようにします。はじめに 1 つ前の項目で説明した PMT 関数で、返済額を求めます。その後、 PPMT 関数で 元金分、 IPMT 関数で金利分を求めます。 で使関 ピーエムティー ( ペイメントト PMT ぐ元利均等払いでの返済額を求める = PMT ( 利率 , 期間 , 現在価値 , [ 将来価値 ] , [ 支払期日 ]) 0 0 ・・・返済期間が入力されたセルを指定する 0 ・・・利率が入力されたセルを指定する か省略する。期首の場合は「 1 」を指定する 0 ・・・支払期日を指定する。各期の期末の場合は「 0 」を指定する 期額を指定する ) を指定するか省略する。定期積立額を求めるときは、目標満 ・・返済後の借入金の額を指定する ( 借入金返済の場合は「 0 」 ・・・・返済を始める前の借入金の額が入力されたセルを指定する c.- で使う関 ビービーエムティー PPMT 元利均等払いでの返済額の元金分 を求める = PPMT ( 利率 , 期 , 期間 , 現在価値護将来価値 ] , [ 支払期日 ]) 0 ・・・・返済期間が入力されたセルを指定する 0 ・・・何期目かが入力されたセルを指定する 0 ・・・利率が入力されたセルを指定する 0 ・・返済を始める前の借入金の額が入力されたセルを指定する 0 ・・・返済後の借入金の額を指定する ( 借入金返済の場合は「 0 」 を指定するか省略する。定期積立額を求めるときは、目標満 期額を指定する ) 0 ・・支払期日を指定する。各期の期末の場合は「 0 」を指定する か省略する。期首の場合は「 1 」を指定する 242
月々の返済額を調べる PMT 使って計算できます ( 図 A) 。 の金額を返済していく場合、月々の返済額は PMT 関数を ローンの返済シミュレーションをしてみます。毎月一定 07 図 A =PMT(C3/12P4.05 , 0.0 ) 1 ~ 三ローン返済シミュレーション ( 元利均等 ) 2 3 4 5 6 8 年利 返冫期間 ( 返済 2.15 % 1 2 1 000000 ¥ ~ も 30 ? PMT 関数を使うと、 毎月の返済額を求め られる 毎月一定の金額を支払う返済方法を「元利均等払い」と いいます。ここでは、 100 万円を年利 2.15 % で借り入れ、 1 年かけて返済していく場合、毎月の返済額がいくらにな るかを求めてみます。 う関 ビエムティ”メペイメント ) PMT 元利均等払いでの返済額を求める = PMT ( 利率 , 期間 , 現在価値 , [ 将来価値 ] , [ 支払期日 ]) 0 0 ・・・返済期間が入力されたセルを指定する 0 ・・利率が入力されたセルを指定する か省略する。期首の場合は「 1 」を指定する 0 ・支払期日を指定する。各期の期末の場合は「 0 」を指定する 期額を指定する ) を指定するか省略する。定期積立額を求めるときは、目標満 ・・・・返済後の借入金の額を指定する ( 借入金返済の場合は「 0 」 ・・・・返済を始める前の借入金の額が入力されたセルを指定する 236
数値を比較して順位を付ける 売上額に順位を付ける 各営業所の売上額に順位を付けて成績を評価することが あります。自分で金額を見比べて順位を付けることもでき ますが、件数が多いと手間になります。こんなときは、 RANK 関数を使うと、簡単に間違いなく順位付けできま す ( 図 A) 。 営業所別売上げデータ 2 営業所名 4 月 3 5 月 6 月 キ A 営業所 1 2 日 000 1 05 000 1 08 000 341 000 5 日営業所 1 0 日 000 1 1 6 000 1 20000 344 000 C 営業所 9 日 500 92 000 1 1 0 0 〔旧 300 500 7 D 営業所 110000 日 3 000 1 06 000 299 000 RANK 関数を使うと、 9 金額の順位を表示できる こでは、セル E4 ~ E7 に各営業所の 4 ~ 6 月の売上 額の合計を求めています。この合計値の順位をセル F4 ~ に求めます。 こで使う関 RANK = RANK ( E4 , $ 印 4 ・ $ 7 , 0 ) ランク 特定の数値の、指定したセル範囲 内での順位を表示する RANK = RANK ( 数値 , 範囲 , [ 順序 ]) 0 ・・・順位を調べたい数値が入力されているセルを指定する 0 0 の「数値」を指定したセルを含むセル範囲を指定する ・・・・順位を大きい順で求める場合「 0 」を指定 ( または省略 ) する。 小さい順で求める場合「 1 」を指定する