Excel。ビジネスや経理で使われる、主な計算式をまとめてみました。

Excel。
ビジネスや経理で使われる、主な計算式をまとめてみました。

計算式

ビジネス実践スキルの講座や、ビジネスマンのExcel実践講座で、
よくExcelの四則演算はわかるけど、式がイマイチとか、学生の頃数学が苦手で…
という声をよく聞きます。
先日もまとめて、説明をしたら好評でしたので、
今回は、ビジネスシーン。経営分析などで使用する、
主な計算式をまとめてみましょう。

その前に、

【率】【比】という文字が付いたら、割り算です。

売上金額

単価　×　数量
意外かもしれませんが、結構聞かれることがあるんですよ。ナメたらアカン。

割引後金額

元の金額　×（1-割合）
20%OFFならば、元の金額×(1-0.2)ですね。1は100%という意味ですね。

本体価格（税引後価格：消費税5%として）

税込価格　÷（1+5％）

税込金額（消費税5%として）

元の金額　×（1+5%）

平均

合計　÷　個数ですが、これはオートΣボタンで算出できますね。

構成比

その項目の合計　÷　全体
絶対参照は必須ですね。算出後、％ボタンをクリックしましょう。

前年度比

今年度合計　÷　前年度合計
算出後、％ボタンをクリックしましょう。

達成率

実際の数値　÷　目標の数値
算出後、％ボタンをクリックしましょう。
どっちから、どっちを割るのか、分からない場合は、まず割っちゃいましょう。
で100%を越えるか、越えないかを目で確認すれば、
おのずと、判断出来ますね。
頭で考えるよりも、実行しちゃうのがポイントですね。

伸び率

（比較する数値-元の数値）÷元の数値
算出後、％ボタンをクリックしましょう。

売上高

売上の合計ですね。これはオートΣボタンで算出できますね。

粗利率

粗利益　÷　売上高　
または、
売上総利益　÷　売上高　

営業利益率

営業利益　÷　売上高

当期純利益率

当期純利益　÷　売上高

総資産利益率

当期純利益　÷　総資産
投資効率を表す指標で高いほど効率よく利益をあげていることになりますね。

流動比率

流動資産　÷　流動負債
短期的な財務安全性を示します。

自己資本比率

自己資本　÷　総資本
財務安全性を示す数字ですね。

ギアリング（負債比率）

負債合計　÷　総資本
ギアリングは、財務の安全性を示します。

固定資産長期適合比率

固定資産　÷　自己資本
財務安全性を示し、固定資産のうち、自己資本がどれだけまかなっているかがわかります。

債務償還年数

返済財源（営業利益＋減価償却費）　÷　借入金
借入金など債務を何年で返却できるのか？

インタレスト・カバレッジ・レシオ

（営業利益＋受取利息＋受取配当金）　÷　支払利息
利息の支払能力を示します。

キャッシュ・フロー額

営業利益　＋　減価償却

平均従業員

従業員の延べ人数（各月の従業員数の合計）　÷　12

人件費

役員報酬　＋　給与手当　＋法定福利費

売上高人件費率

人件費　÷　売上高

労働分配率

人件費　÷　粗利益
粗利をどれだけ人件費に振り分けているのか

まだまだありますが、一応この辺で。

Excel。一行空きのデータを合計するテクニック SUM関数とISODD+ROW関数

Excel。一行空きのデータを合計するテクニック

SUM関数とISODD+ROW関数

仕事場で一行空きのデータを合計するのが、
面倒でどうにかならないかな？と、
お話がありまして、
確かにSUM関数で、飛び地をCTRLキーでチマチマ、クリックするのも確かに面倒ですね。

ということで、今回は、一列作業列を追加しますが、簡単に合計する方法をご紹介。
下記のようなデータをご覧ください。

金額を合計したいわけですね。本来ならば、こういう表の作り方はよくないわけです。
個数と金額を別々の列にすれば、何も困らないはずですね。
ただ、愚痴をいってもしかたありませんね。
現場レベルではよくあることです。

アイディアとして、
奇数行なのか偶数行なのか判断して、計算対象でないようにすればいいわけです。
なお、この行はROW関数を使うので、行番号です。
また、計算対象外にするということは、ゼロにすればいいわけですね。
で、ここで、IF関数を考えちゃうのですが、
長くなってしまうので、
今回は

ISODD関数

を使用します。
このISODD関数は、イズオッド関数と読みます。
この関数は、奇数の時はTRUEを偶数のときはFALSEを返す関数です。
そして、ここでポイントとなるのが、
Excelは、TRUEは0。FALSEは1を返します。
すなわち、奇数行か偶数行かを判別して、
その結果に数字をかければ、偶数行はゼロになるわけです。

それでは、さっそく作ってみましょう。
まず、D列に計算行を作っていきます。
D4をクリックして、ISODD関数のダイアログボックスを表示します。

数値には、手入力で、ROW(C4)と入力して、OKボタンをクリックします。
数式バーには、

=ISODD(ROW(C4))

が完成していますが、数式的には未完成ですね。これに、C4を掛ける必要があります。
すなわち、数式バーは

=ISODD(ROW(C4))*C4

この式をオートフィルで8月5日までCOPYします。

ここまで、算出できれば、あとは、オートSUMボタンで、簡単に合計を算出することができますね。
結果は、

Excel。Excel2010のピボットテーブルをExcel2003タイプに変える技

Excel。
Excel2010のピボットテーブルをExcel2003タイプに変える技

ピボットテーブル

先日ご質問がありまして、
Excel2010を導入したら、ピボットテーブルが変わってやりにくいので、
どうにかなりませんかね？とのこと。
確かに、Excel2003で使っていたのをExcel2010に変更すると、
すぐには慣れないものですよね。

では、慣れるまで我慢してください。というのもなんなので、
実は、Excel2003のピボットテーブルに変更することが、簡単にできるのです。
ということで、ご紹介しましょう。

まず、
挿入タブ→ピボットテーブルで新規シートにピボットテーブルを作成しましょう。

あとは、右側のピボットテーブルのフィールドリストを使って、
列ラベルや行ラベルなどに移動するわけですね。

Excel2003は、フィールド名を直接、シート上にドラッグ アンド ドロップしたのですが、ここがExcel2010では、変わったわけですね。
ピボットテーブルツールのオプションタブ→ピボットテーブルオプションをクリックします。
ピボットテーブル上で右クリックでもOKですので、
ピボットテーブルオプションのダイアログボックスを表示しましょう。タブは、表示に移動します。

従来のピボットテーブルレイアウトを使用するにチェックマークを付けてOKをクリックしましょう。

Excel2003のピボットテーブルレイアウトに変更されましたので、今までと同じように処理することが可能になりましたね。

Excel。罫線の斜め線をクイックアクセスツールバーで簡単に書く方法 クイックアクセスツールバーをカスタム

Excel。
罫線の斜め線をクイックアクセスツールバーで簡単に書く方法

クイックアクセスツールバーをカスタム

先日、罫線の斜め線を引くのは、どうしたらいいの？
とご質問がありましたので、
どうやって描いたらいいのかをご紹介。

下記のような表があって、色がついている所に斜め線を書きたい訳です。
まずは、一般的な方法。

B3をクリックして、Ctrl+1か、ホームタブの罫線ボタンのその他の罫線をクリックします。

そうすると、セルの書式設定ダイアログボックスが表示されてきます。罫線タブに移動します。

線の種類・色を選択後、
罫線ブロックの右斜め下にある、
斜め線のボタンをクリックします。
このボタンは、ONとOFFができます。
これで、斜め線が描くことができました。

ただ、頻繁に斜め線を書くようでしたら、
その都度に、セルの書式設定ダイアログボックスを表示しないといけないわけで、
効率が悪いですね。

罫線ボタンをクリックして、斜め線があればいいのですが、残念ながらない。

そこで、知っておくと便利なのが、
リボンに表示されていないもので、
意外と用意されているボタンがあるのです。
それを、

クイックアクセスツールバーに登録したら便利になる。

という訳です。

では、やり方をご紹介。

クイックアクセスツールバーの▼をクリックして、その他のコマンドをクリックします。
※ファイルタブ→Excelのオプションと同じです。
 
クイックアクセスツールバーが選択されている状態で、左側がコマンドの選択が、基本的なコマンドになっています。ここの▼をクリックして、リボンにないコマンドを選択します。

これをクリックすると、リボンにない操作が表示されてきます。この中から、【斜め罫線（右下さがり）】を選択して、追加ボタンをクリックすると、右側のクイックアクセスツールバーのユーザー設定に追加されます。

OKボタンをクリックして確定します。

クイックアクセスツールバーに、ボタンが追加されています。では早速クリックしてみましょう。

これで、ボタン一発で、斜め罫線を描くことができましたね。
クイックアクセスツールバー。ちょっとカスタマイズすると便利ですね。

Excel。Excel2010でExcel2003の印刷プレビューを表示する方法

Excel。
Excel2010でExcel2003の印刷プレビューを表示する方法

クイックアクセスツールバーをカスタム

最近、企業研修で、Excel2003を使用していたけどExcel2010になったので…
あれは、これは、どこにいったという質問が多いですね。
そこで、今回は、その中でも多い、

Excel2003の印刷プレビューを使う方法

をご紹介。

Excel2010では、ファイルタブ→印刷をクリックすると、

バックステージビュー

に変わります。

これだと、右側のプレビューは拡大して確認することはできません。
また、表示タブのページレイアウトや改ページプレビューとも、ちょっと違う。

そこで、知っておくと便利なのが、
リボンに表示されていないもので、
便利なもの旧バージョンでお馴染みのものが結構あるのです。
それを、クイックアクセスツールバーに登録しちゃおうという訳です。
では、やり方をご紹介。

クイックアクセスツールバーの▼をクリックして、その他のコマンドをクリックします。
※ファイルタブ→Excelのオプションと同じです。

 クイックアクセスツールバーが選択されている状態で、
左側がコマンドの選択が、基本的なコマンドになっています。
ここの▼をクリックして、

リボンにないコマンド

を選択します。

これをクリックすると、リボンにない操作が表示されてきます。
この中から、【印刷プレビュー（全画面表示）】を選択して、
追加ボタンをクリックすると、
右側のクイックアクセスツールバーのユーザー設定に追加されます。

OKボタンをクリックして確定します。

クイックアクセスツールバーに、ボタンが追加されています。
では早速クリックしてみましょう。

ありゃま、懐かしい。

印刷プレビュー

ですね。
これならば、ズームで確認も出来ますね。
どうしても、バックステージビューになじめない方は、
この方法はいかがでしょうか？

Excel。空白の場合エラーを返さない方法 IF+VLOOKUP関数

Excel。空白の場合エラーを返さない方法

IF+VLOOKUP関数

今回は、空白の場合エラーが出てしまうのを回避する【公式】のような定番関数。

IF関数によるエラー対処方法

をご紹介。

セミナーでも、講義でも、定番中の定番のネタですが、早速やってみましょう。
下記のようなデータがあったとして。

シート名：納品書

シート名：商品一覧

納品書のC13（グレーのところ）にVLOOKUP関数を使って、
商品一覧シートの商品一覧から抽出するとして、
B13の商品IDを空白にしたままVLOOKUP関数を作ると、
下記のような#N/Aというエラーが表示されてしまいます。

数式は、

=VLOOKUP(B13,商品一覧!$A$4:$C$14,2,FALSE)

これは、商品IDが空白なので、エラーになってしまう訳ですね。
そこで、商品IDが空白でもエラーが出ないようにIF関数を使って、
修正してみましょう。
今回は、わかりやすいように、C13の数式を削除して、
何もない状態から作ってみます。
それでは、まず、IF関数のダイアログボックスを表示します。

やりたいことは、商品ID（B13）は空白ですか？
空白だったら、空白。そうでなかったらVLOOKUP。
というのをやりたい訳です。これをボックスに当てはめていきます。

論理式は、B13=””
真の場合は、””
偽の場合は、Vlookup関数を挿入していきます。

これで、エラー表示を回避することが出来ます。
数式は、

=IF(B13="","",VLOOKUP(B13,商品一覧!$A$4:$C$14,2,FALSE))

【IF関数によるエラー対処方法の公式】

Excel。氏名から名前を抽出する方法 RIGHT+LEN+FIND関数

Excel。氏名から名前を抽出する方法

RIGHT+LEN+FIND関数

氏名の欄から、今度は名前を抽出する方法をそれをご紹介します。
下記のような表があったとします。

氏名から名前を抽出する場合、
前提条件として苗字と名前がわかるようにしておかなければいけません。

今回も、半角スペースで苗字と名前を分けております。
この半角スペースを見つけて、その後ろを抽出すればいいわけですね。

では、D2をクリックして、まず、右側の文字を抽出する関数である、
RIGHT関数のダイアログボックスを表示しましょう。

文字列はB2をクリックします。
文字数はというと、
全体の文字数から半角スペースの文字数を引いた数を算出しなければいけません。
そこで、まずは、文字数を数えるLEN関数を使用します。

それでは、文字数のボックスをクリックして、
LEN関数のダイアログボックスを表示しましょう。

文字列は氏名のB2をクリックします。
そうしたら、RIGHT関数に戻ります。
数式バーのRIGHTをクリックするとダイアログボックスがRIGHT関数に変わりますね。

今は、文字数がわかっただけにすぎません。
これから半角スペースまでの文字数を引く計算式を作っていきますので、
LEN(B2)の後ろにマイナスを入力して、
FIND関数のダイアログボックスを表示します。

このFIND関数もわかりにくい関数の1つですね。
検索文字列には、氏名が入っているB2ではなく、
検索したい文字を入力します。

半角スペースですので、” “と入力します。
対象には、氏名が入っているB2をクリックします。
開始位置は、省略もできますが、1を入力します。

これで、OKボタンをクリックして完成です。
数式は、
=RIGHT(B2,LEN(B2)-FIND(" ",B2,1))

苗字のように左側をピックアップするのは、
比較的簡単なのですが、どうしても右側は、
文字列の途中からなので、ちょっと面倒ですね。

Excel。氏名から名前を抽出する方法 LEFT+FIND関数

Excel。氏名から苗字を抽出する方法

LEFT+FIND関数

氏名の欄から、今度は名前を抽出する方法をそれをご紹介します。
下記のような表があったとします。

氏名から苗字を抽出する場合、
前提条件として苗字と名前がわかるようにしておかなければいけません。

今回は、半角スペースで苗字と名前を分けております。
この半角スペースを見つけて、その前を抽出すればいいわけですね。

では、C2をクリックして、
まず、左側の文字を抽出する関数である、
LEFT関数のダイアログボックスを表示しましょう。

文字列はB2をクリックします。
文字数はというと、
半角スペースの前までの文字数を算出しなければいけません。

そこで、FIND関数を使用します。

LEFT+FIND関数

のネストとなります。
それでは、文字数のボックスをクリックして、FIND関数のダイアログボックスを表示しましょう。

このFIND関数もわかりにくい関数の1つですね。
検索文字列には、
氏名が入っているB2ではなく、
検索したい文字を入力します。

半角スペースですので、” “と入力します。
対象には、
氏名が入っているB2をクリックします。

開始位置は、省略もできますが、1を入力します。

これで、OKボタンをクリックでもいいのですが、

これだと、半角スペースまで含めて抽出されてしますので、
LEFT関数に戻ります。

数式バーのLEFTをクリックするとダイアログボックスがLEFT関数に変わりますね。

文字数のボックスには、
FIND(" ",B2,1)と表示されておりますので、半角スペース分を引きます。

つまり、ボックスには、FIND(" ",B2,1)-1とします。
これで、完成です。

Excel。住所から都道府県を抜き出す方法 IF+MID+LEFT関数

Excel。住所から都道府県を抜き出す方法。

IF+MID+LEFT関数

住所から、都道府県を抽出する方法を、ご紹介しましょう。
実は、なかなか厄介なんですねコレが、
問題児は、

神奈川県・和歌山県・鹿児島県。

なんで、問題児なのかというと、
この３県だけ4文字なんですね。
北海道も東京都も京都府や大阪府も他の県も、3文字ですね。

つまり、この問題児がなければ、左から3文字を抽出すれば、
何の問題もないのですが、問題児によって、ひと工夫が必要になるわけです。

4文字目が県かどうか確認して、
4文字目が県ならば左から4文字目を、
そうでなければ左から3文字目を抽出させるようにすればいいわけですね。

それでは、早速やってみましょう。
下記のような表があったとして、都道府県を抽出します。

E2をクリックして、IF関数のダイアログボックスを表示します。

論理式を作っていきます。
4文字目は県かどうかという質問文ですね。
4文字目を確認するためには、MID関数を使用します。

MID関数のダイアログボックスを表示して、
文字列は、住所が入っている、D2をクリック。

開始位置は、4文字目なので、4。
文字数は、一文字を検出したいので、1。

ここで、OKボタンをクリックしてしまうと、
IF関数の作成途中なので、エラーになってしまいますので、
数式バーのIFをクリックして、IF関数のダイアログボックスに戻りましょう。
 
もどったら、下記のように論理式が出来上がっていますが、
これに=”県”という文字を追加します。

論理式は、MID(D2,4,1)="県"　で完成です。

今度は、真の場合と、偽の場合を作成していきます。
論理式で、
県だったら、4文字をそうでなかったら、3文字を抽出するという
数式を作成していきます。

真の場合は、左から4文字を抽出するということですので、
左からというのは、LEFT関数を使用します。

それでは、LEFT関数のダイアログボックスを表示して、作成していきましょう。

文字列は、D2。
文字数は、4文字なので、4。
これと同じように、偽の場合も作成してきます。
文字列は、D2。
文字数は、3文字なので、3。
で、これで、完成。

数式バーは、

=IF(MID(D2,4,1)="県",LEFT(D2,4),LEFT(D2,3))

このようにすると、都道府県を抽出することが出来ます。

もうちょっと、数式をコンパクトにする技もありますので、

別で書きたいと思います。