多くの人は、Microsoft Excelをビジネスでしか役に立たないツールだと考えています。実際には、家庭でも役立つ方法はたくさんあります。日常生活でExcelを使うコツは、問題を解決する適切な数式を選ぶことです。
新しい車のローンを組む場合、どの投資信託が自分に最適かを知りたい場合、あるいは単に銀行口座の明細を理解したい場合でも、Excelは役立つ強力なツールです。
シンプルで強力で、複雑な問題を解決するのに役立つ15の数式を選びました。
財務数式
新しい家を探していて、住宅ローンの専門用語がすべてわからない場合。新しい車を探していて、販売員が説明する車のローンの条件がわからない場合。
ご心配なく。ローンを組む前に、Excelを使ってリサーチしましょう!
1. PMT---支払い
ローンの条件を比較して、条件によって異なる実際の毎月の支払額をすぐに計算したい場合は、強力でシンプルなPMT数式を利用しましょう。
この数式を使用するために必要な情報:
- ローンの金利
- ローンの期間(支払いの回数?)
- ローンの元本
- 将来価値(何らかの理由でローンがゼロになる前に完済されると見なされる場合(オプション))
- ローンの種類---0は毎月末に支払いが発生する場合、1は毎月初めに支払いが発生する場合(オプション)
さまざまなローンを素早く比較して、支払いがどうなるかを確認する方法は以下のとおりです。可能性のあるすべてのローンと、そのローンに関するすべての情報をリストしたExcelシートを作成します。次に、「支払い」列を作成してPMT数式を使用します。
作成したPMTセルの右下隅をドラッグして、シートにリストされているすべてのローンの条件の合計支払額を計算します。Excelのオートフィル機能は、これらのテクニックでよく使用する機能の1つです。
これで、さまざまな種類のローンの毎月の支払いを比較できます。
(PMTとFVの数式を使用する場合は、同じ期間を使用することに注意してください。この場合、毎月の支払いは金利期間を12か月で割る必要があります。Twitterの@redtextureのMark Jonesさんに感謝します。)
読者の皆様は本当に素晴らしいです。この修正に協力してくれてありがとう、マーク!
2. FV---将来価値
次に紹介する数式は、定期預金(CD)などに投資するお金を探していて、その期間の終了時にいくらになるかを知りたい場合に便利です。
FV数式を使用するために必要な情報:
- ローンの金利
- 支払いの回数(または投資期間(月数))
- 各期間の支払い(通常は毎月)
- 現在の開始残高(オプション)
- ローンの種類---0は毎月末に支払いが発生する場合、1は毎月初めに支払いが発生する場合(オプション)
銀行から提供された情報から知っている条件を使用して、いくつかのCDを比較してみましょう。以下の例では、定期預金に投資する20,000ドルの相続財産があるとします。
金利は再び小数点形式で表されます(銀行から提供された金利を100で割ります)。CDは通常、開始値と支払われる将来値に基づいているため、支払いはゼロです。検討しているすべてのCDにFV数式を使用すると、比較は以下のようになります。
間違いなく、より高い金利のCDは、より長い期間にわたってより多くの利益を支払います。唯一の欠点は、3年間はお金に手をつけられないことですが、投資とはそういうものです!
3-4. 論理数式---IFとAND
最近のほとんどの銀行では、ほぼ1年分の銀行取引をCSVなどの形式でダウンロードできます。これはExcelを使用して支出を分析するのに最適な形式ですが、銀行から受け取るデータは非常に整理されていない場合があります。
論理数式を使用すると、支出過多を簡単に特定できます。
理想的には、銀行が支出を自動的に分類するか、アカウントを設定して支出を支出カテゴリに分類します。たとえば、私たちが行ったレストランはすべて外食ラベルでラベル付けされます。
これにより、外食して20ドル以上使った場合をいつでも特定するために論理数式を使用しやすくなります。
これを行うには、新しい列に論理数式を作成し、カテゴリ列が「外食」で、取引列が$-20より大きい値を探します。
注意:以下の比較では「<」つまり「未満」が表示されています。これは、列Cの値はすべて負の値であるためです。
以下はその例です:
IFとANDを1つの数式で一緒に使用すると難しそうに見えますが、実際は非常に簡単です。IFステートメントは、ANDステートメントが真の場合には金額(C2)を出力し、そうでない場合はFALSEを出力します。ANDステートメントは、カテゴリが「外食」で、取引が20ドルを超えているかどうかをチェックします。
これで完了です! すべての取引を手動で精査することなく、特定のカテゴリで支出が超過した時期を正確に把握できるようになりました。
リストを理解する
リストは日常生活の大きな部分を占めています。家庭を管理している場合、常にリストを使用しています。Excelには、チェックリストやその他の種類のリスト形式で生産性を高めるための強力なツールがいくつかあります。
5-6. COUNTとCOUNTIF
Excelは、リスト内の値をすばやく整理して並べ替えるのに役立ちます。PTCの例を見てみましょう。これは、コミュニティメンバーからの寄付のリストです。
リストに名前が何回表示されているかを確認します。これを行うには、COUNT数式とIF数式を組み合わせることができます。まず、その人がミシェルかどうかを確認する列を作成します。この数式では、これが真の場合に「1」でセルを埋めるIFステートメントを使用します。
次に、リストでミシェル・ジョンソンを何回見つけたかをカウントする列を作成します。
これにより、列Eの空白ではなく1があるすべての場所のカウントが得られます。
これが、このようなことを行う最も簡単な方法ですが、2つの手順が必要です。
6-8. SUMIF、COUNTIF、AVERAGEIF
少し高度な数式を使用しても構わない場合は、SUMIF、COUNTIF、AVERAGEIFなどの「IF」数式を組み合わせて使用することを検討してください。これにより、論理条件が真の場合に数式(COUNT、SUM、またはAVERAGE)を実行できます。上記の例を使用して、その仕組みを説明します。
この数式は、すべての寄付者名を含む列Aを見て、範囲内のセルが引用符内の条件と一致する場合、1ずつカウントアップします。これにより、1ステップで寄付者名が「ミシェル・ジョンソン」と一致するすべての回数がカウントされます。
2つの列を使用するよりもはるかに高速ですが、少し複雑です。そのため、状況に最適なアプローチを使用してください。
SUMIFとAVERAGEIF数式はまったく同じように機能しますが、数学的な結果は異なります。この例でSUMIFを使用すると、代わりに使用した場合、ミシェル・ジョンソンの寄付金の合計金額が得られます。
9. LEN
創造的に使用できるもう1つの数式はLEN数式です。この数式は、テキストの文字列に含まれる文字数を示す多くのExcelテキスト数式の1つです。
上記の例でこれを使用する1つの興味深い方法は、寄付列の数字の数をカウントして、1,000ドルを超えて寄付した寄付者を強調表示することです。数値の長さが4以上の場合、少なくとも1,000ドル寄付されています。
これで、見やすくするために追加の書式設定を追加できます。
これを行うには、寄付列のすべてのセルを強調表示し、メニューのホームタブを選択して、ツールバーの条件付き書式をクリックします。次に、数式を使用して書式設定するセルを決定するを選択します。
この数式が真の場合に値の書式を設定する:の下の範囲を、すべてのLEN数式の出力が表示される列/範囲に設定します。
この例では、条件を「>3」にすると、1,000ドルを超えるすべてのものに特別な書式が適用されます。書式...ボタンをクリックして、これらに適用する特別な書式の種類を選択することを忘れないでください。
また、簡単な注意書きです。範囲が「$E$2:$E$11」ではなく「$E2:$E11」と定義されていることに気付くでしょう。範囲を選択すると、デフォルトで前者になりますが、これは機能しません。上の図のように相対参照を使用する必要があります。そうすると、条件付き書式は2番目の範囲の条件に基づいて機能します。
銀行と金融のダウンロードを整理する
銀行や健康保険会社など、企業から情報をダウンロードする場合、受信データの形式が常に必要な形式と一致するとは限りません。
たとえば、銀行からエクスポートされたデータに標準形式の日付が記載されているとします。
独自の新しい列を追加して、年を先頭に付け、受取人情報を追加する場合は(独自の並べ替え目的で)、列から情報を抽出するのは非常に簡単です。
10-14. RIGHT、LEFT、TEXT、およびCONCATENATE
RIGHT数式を使用して、その列のテキストから年を取り出すことができます。
上の数式は、Excelに列Dのテキストを取り、右側から4文字を抽出するように指示しています。CONCATENATE数式は、列Eの受取人テキストを使用して、その4桁をまとめます。
日付からテキストを抽出する場合は、「=TEXT(D2、「mm/dd/yyyy」)」数式を使用して、日付形式(日付ではなく)に変換する必要があることに注意してください。その後、RIGHT数式を使用して年を取り出すことができます。
情報が左にある場合はどうでしょうか。その場合は、LEFT数式を使用すると、左から右にテキストを取り出すことができます。
CONCATENATEは、複数の異なる列にあるテキストを1つの長い文字列にまとめたい場合に非常に便利です。Excelの列を結合する方法に関するガイドで詳細を確認できます。
文字列を完全に操作する方法を学習したい場合は、Excelでテキストを分割する方法もいくつかあります。
くじ引きからランダムに名前を選ぶ
15. RANDBETWEEN
最後に紹介する楽しい数式は、クリスマスパーティーのためにくじ引きをする必要がある場合などに使用できます。その帽子と紙切れを片付けて、代わりにラップトップを取り出してExcelを起動しましょう!
RANDBETWEEN数式を使用すると、指定した数値の範囲内からランダムに数値を選択できます。
使用する必要がある2つの値は、最低値と最高値です。これは、各人の名前に適用した数値の範囲の端にある必要があります。
Enterキーを押すと、数式は範囲内の数値の1つをランダムに選択します。
これは、可能な限りランダムで改ざん防止です。したがって、帽子から数字を選ぶ代わりに、Excelから数字を選びましょう!
日常の問題にExcelを使用する
ご覧のとおり、Excelはデータ分析の専門家やビジネスのプロフェッショナルのためだけのツールではありません。誰もがExcelに隠されている数多くの数式から恩恵を受けることができます。これらの数式を学習すれば、Excelで現実の問題を解決し始めることができます。
Excelの学習を止めてはいけません。使用できるExcelの関数や数式はたくさんありますが、Excelにはできないと思っていたちょっとしたコツが見つかるかもしれません。
画像クレジット: Goodluz via Shutterstock.com
コメントする