11/21/2024

Excel。積立期間は何ヵ月?それを算出するには、NPER関数がオススメです。

Excel。積立期間は何ヵ月?それを算出するには、NPER関数がオススメです。

<NPER関数>

お小遣いを毎月貯めているけど、希望する金額に到達するには、何ヵ月かかるのかを求めたい場合に、使うと便利なのが、NPER関数です。


NPER関数(エヌパー)は、Number of PERiodsの略

ナンバー・オブ・ピリオズ


次の表を使って、NPER関数を紹介します。

NPER関数

貯金箱など、金融機関に預けていなければ、年利は関係ありませんが、NPER関数の引数にありますので、B1の年利を用意しております。


月額積立金額のB2は、月々1000円貯金箱に入れる。


B3の積立目標金額は50000円まで、貯めたいという金額です。


B5には、毎月1000円を貯めて、50000円になるのに、何ヵ月かかるのでしょうかというのを求めたいわけです。


では、B5をクリックして、数式を設定していきましょう。


手入力で数式を設定したほうが、わかりやすいかと思いますが、説明の為、NPER関数のダイアログボックスをつかって説明します。


最初の引数の利率は、B1 ただし、年利で、求めたいのが、何ヵ月なので、12で割る必要があります。そのため、B1/12


定期支払額は、毎月1000円ずつなので、B2


現在価値は、貯めていないので、0


将来価値は、目標金額なので、B3


支払期間は、期末払いならば0で、とりあえず、期末払いとしておきます。


実は、これで、OKボタンをクリックすると、-50 と表示されます。


これは、2つ目の引数の「定額支払額」が、手元から資産が積立金で減るので、B2を1000ではなく、「-1000」とする必要があります。


では、B5の数式を修正して、完成します。


 =NPER(B1/12,-B2,0,B3,0)


積立期間は50と算出されましたので、1000円を毎月積み立てると、50ヵ月後には、50000円になるというわけです。

11/20/2024

Excel。ISBLANK関数は、対象が空白セルの場合にTRUEを返す関数です【ISBLANK】

Excel。ISBLANK関数は、対象が空白セルの場合にTRUEを返す関数です 

<関数辞典:ISBLANK関数>

ISBLANK関数

読み方: イズブランク  

分類: 情報 

ISBLANK関数


ISBLANK(テストの対象)

対象が空白セルの場合にTRUEを返す

11/19/2024

Excel。2024/10/13-10/19にYEAR関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/10/13-10/19にYEAR関数など紹介したFacebookページのコメントです。

<Facebookページ>

Facebookページに書いた、Excelの豆知識(Trivia)です。

Facebookページ

10月13日

Excel。

XMATCH関数

読み方: エックスマッチ  

分類: 検索/行列 

XMATCH(検索値,検索範囲,[一致モード],[検索モード])

配列内での項目の相対的な位置を返す 




10月14日

Excel。

XNPV関数

読み方: エックスエヌピーヴイ  

読み方: エクストラ・ネット・プレゼント・バリュー

分類: 財務 

XNPV(割引率,キャッシュフロー,日付)

非定期キャッシュフローに対する正味現在価値を算出します 




10月15日

Excel。

XOR関数

読み方: エックスオア  

読み方: エクスクルーシブ・オア

分類: 論理 

XOR(論理式1,[論理式2],…)

複数の条件で奇数の数を満たすかどうかを調べる 




10月16日

Excel。

YEAR関数

読み方: イヤー  

分類: 日付時刻 

YEAR(シリアル値)

日付から年を算出します 




10月17日

Excel。

YEARFRAC関数

読み方: イヤーフラクション  

分類: 日付時刻 

YEARFRAC(開始日,終了日,[基準])

2つの日付の間の期間を年数で算出します 




10月18日

Excel。

YEN関数

読み方: エン  

分類: 文字列操作 

YEN(数値,[桁数])

数値を四捨五入して、円記号(¥)を付けた文字列に変換する 




10月19日

Excel。

YIELD関数

読み方: イールド  

分類: 財務 

YIELD(受渡日,満期日,利率,現在価値,償還価額,頻度,[基準])

定期利付債の利回りを算出します

11/18/2024

Excel。#DIV/0!エラーの確認には、参照元のトレースをつかってみよう【trace】

Excel。#DIV/0!エラーの確認には、参照元のトレースをつかってみよう

<絶対参照と参照元トレース>

構成比を算出した時に、#DIV/0!というエラーが発生することがあります。


この原因は、絶対参照を、常に使いたいセルに設定していないために発生するわけです。


どのセルが原因なのかを確認するために、参照元のトレースをつかって確認してみましょう。


今回用意した表です。

参照元のトレース

B6には、B2:B5の合計値が求められています。

C2には、構成比。

つまり、鉛筆の合計が、全体の中で何パーセントを占めているのかを求めたいわけです。


C2に数式を設定します。


C2の数式は、=B2/B6として、オートフィルで数式をコピーしてみました。

すると#DIV/0!というエラーが表示されてしまいました。

この#DIV/0!というエラーは、「ゼロで除算している」というエラーです。


ゼロで除算した覚えはないのですが…


このぐらいの数式ならば、目視で確認することもできますが、ここで、数式タブにある「参照元のトレース」をつかうことで、視覚的に原因がわかります。


エラーが表示されている、C2をクリックして、数式タブの「参照元のトレース」をクリックしてみましょう。


すると、どこのセルをつかって、結果を求めているのかが、参照元を矢印で確認することができます。


本来は、B6をつかってくれないと困るのですが、B7をつかった数式になっていることがわかりました。


C3の数式は、

=B3/B7

となっていることでも確認することができますが、参照元トレースをつかうことで、より、視覚的に確認することができます。


確認出来たら、「トレース矢印の削除」をクリックすると矢印を非表示にすることができます。


B6を常に使いたいので、どのようにしたらいいのかというと、絶対参照をつかって、B6を固定した数式に修正します。

C2の数式を

=B2/$B$6

と設定しました。


B6のセルをクリックした後に、F4キーを押すと、絶対参照が設定された、$B$6 に変わります。


あとは、オートフィルで数式をコピーしてみると、#DIV/0!エラーは表示されず、構成比を求めることができました。

11/17/2024

Excel。動画で紹介。列方向(行内)の重複を削除して、一意のデータで並べ替える。【Youtube】

Excel。動画で紹介。列方向(行内)の重複を削除して、一意のデータで並べ替える。

<Youtube>

行方向(列内)の重複は、データタブの「重複の削除」をつかえば、手早く、重複を除いた「一意」のデータだけのリストをつくることができます。


ところが、列方向(行内)の重複は、「重複の削除」で対応することができません。


そこで、手早く処理するには、UNIQUE関数をつかうことで対応することができます。


そして、さらに、SORT関数を組み合わせて数式をつくる方法をご紹介しております。

11/16/2024

Excel。定期キャッシュフローに対する内部利益率を求められるIRR関数【IRR】

Excel。定期キャッシュフローに対する内部利益率を求められるIRR関数

<関数辞典:IRR関数>

IRR関数

読み方: アイアールアール  

読み方: インターナル・レート・オブ・リターン

分類: 財務 

IRR関数

IRR(範囲,[推定値])

定期キャッシュフローに対する内部利益率を算出します

Internal Rate of Return の略

11/15/2024

Excel。1回目から3回目まですべて80以上かを判定したい【AND】

Excel。1回目から3回目まですべて80以上かを判定したい

<IF+AND関数>

条件判断する関数はExcelには多く用意されています。


次のような表があります。

IF+AND関数

B列からD列には、1回目から3回目のポイントが入力されています。


この1回目から3回目までのポイントすべてが80以上ならば、「○」と表示したいのですが、どのようにしたらいいのでしょうか。


IF関数を複数重ねたネストでも判定することはできますが、少し複雑な数式になりますし、面倒です。


そこで、今回は、IF関数と、AND関数をつかった数式で、対応していきます。


E2に数式を設定します。


E2に設定した数式は、

=IF(AND(B2>=80,C2>=80,D2>=80),"○","")

あとは、オートフィルで数式をコピーします。


これで、1回目から3回目まですべてが80以上の場合に、○が表示されていることが確認できました。


AND関数は、「○○かつ○○」というAND条件の関数です。


AND関数の引数に条件を3つ設定する必要がありますが、とてもわかりやすく、数式を作ることができます。


仮に4回目以降に増えたとしても、AND関数の条件を追加するだけで、すみますので、比較的容易に対応することができます。