Excel関数で利益率を求める方法をお探しですね。
広告
売上管理で使う「利益率・達成率・前年比」の計算方法｜Excelでパーセント表示を使いこなそう
売上管理で「今月は○○万円売れた!」という数字だけを見ていても、実は不十分なんです。
本当に知りたいのは「ちゃんと利益が残っているのか」「目標に対して順調なのか」「去年より伸びているのか」ですよね。
そこで役立つのが、**利益率・達成率・前年比**という3つの指標です。
この記事では、Excelでこれらをどう計算するか、パーセント表示でよくあるミス、エラーを避ける方法まで、初心者の方にもわかりやすく解説していきます。
利益率の計算｜売上のうち何%が利益として残ったか
利益率ってどう計算するの?
利益率は、売上に対してどれくらい利益が残ったかを示す数字です。
いくら売上が大きくても、仕入れや外注費が高ければ手元に残るお金は少なくなります。
だから「いくら売れたか」だけでなく「どれだけ利益を残せたか」を見ることが大切なんです。
Excelでの基本の計算式はこちら:
“`
利益率 = 利益 ÷ 売上
“`
たとえば、売上がB2セル、原価がC2セルに入っているとしたら:
– **利益額**: `=B2-C2`
– **利益率**: `=(B2-C2)/B2`
この計算結果は0.25のような小数で表示されますが、セルの表示形式を「パーセント」に変えると、25%のように見やすくなります。
粗利率と営業利益率の違い
実務では、2種類の利益率がよく使われます。
– **粗利率**: 売上から原価を引いた「粗利」を売上で割ったもの。
商品そのものの儲けを見る指標です。
– **営業利益率**: 粗利から人件費・家賃・広告費などを引いた「営業利益」を売上で割ったもの。
会社全体の稼ぐ力を見る指標です。
たとえば営業利益率なら、売上がB2、営業利益がD2のとき:
“`
=D2/B2
“`
どちらもパーセントで表示できますが、意味は違います。
商品ごとの採算を見たいなら粗利率、事業全体の健全性を見たいなら営業利益率、というように使い分けましょう。
利益率だけ見てもダメ?
ここで注意したいのが、**利益率と利益額はセットで見る**ということ。
– 利益率が高くても、売上が少なければ利益額は小さい
– 利益率が低くても、たくさん売れれば利益額は大きくなる
Excelでは、売上・原価・利益額・利益率を横に並べて表にしておくと、値段を変えるときの判断がしやすくなります。
また、売上が0円の行で利益率を計算すると「#DIV/0!」というエラーが出ます。
これを防ぐには:
“`
=IFERROR((B2-C2)/B2,””)
“`
こうすると、計算できない場合は空白になるので、見た目もスッキリします。
達成率の計算｜目標に対してどこまで進んでいるか
達成率の基本の計算式
達成率は、目標に対して実績がどれくらい到達しているかを示す指標です。
営業や店舗運営、ECサイトの売上管理では、月間目標や年間目標に対する進み具合を確認するためによく使われます。
基本の式はこちら:
“`
達成率 = 実績 ÷ 目標
“`
Excelで、目標がB2、実績がC2なら:
“`
=C2/B2
“`
表示形式をパーセントにすれば、目標100万円に対して実績80万円なら80%、実績120万円なら120%と表示されます。
100%を超えていれば目標達成、100%未満なら未達ということがひと目でわかります。
達成率だけでは判断できないこともある
ただし、達成率だけを見て進捗を判断すると、勘違いすることがあります。
たとえば、月末時点で達成率80%なら未達ですが、**月の10日目で80%なら超順調**ですよね。
途中経過を正しく見るには、期間に対する進み具合も考える必要があります。
たとえば、30日間で300万円の目標に対し、10日目で100万円の実績なら:
– 1日あたりの実績:10万円
– 目標ペース:1日あたり10万円
→ ペースとしては100%です。
Excelでは、こんな式で期間を加味した進捗率を計算できます:
“`
=(実績/経過日数)/(目標/期間)
“`
売上目標の管理では、最終達成率と進捗ペースを分けて見ると、早めに対策を考えやすくなります。
エラー対策も忘れずに
達成率をExcelで管理するときは、目標が空白や0のときのエラー対策も大切です。
目標が入力されていない状態で「=実績/目標」を計算すると、ゼロ除算エラーが出て表が見づらくなります。
実務では、こんな式を使います:
“`
=IF(B2=0,””,C2/B2)
“`
または:
“`
=IFERROR(C2/B2,””)
“`
これで、計算できない場合は空白になります。
また、達成率が100%以上なら緑、80%以上100%未満なら黄色、80%未満なら赤といった**条件付き書式**を設定すると、数字を細かく読まなくても状況がパッとわかります。
会議資料や営業管理表では、達成率を単なる数値ではなく、判断しやすい形で見せることが大切です。
前年比・増減率の計算｜前年より何%増えたか減ったか
前年比と増減率は何が違う?
前年比は、今年の実績が前年と比べてどれくらいの水準かを示す指標です。
よく似た言葉に「増減率」がありますが、両者は意味が少し違います。
– **前年比**: 今年 ÷ 前年(前年を100%としたときの今年の水準)
– **増減率**: (今年 – 前年)÷ 前年(前年に対して何%増えたか減ったか)
たとえば、前年売上が800万円、今年売上が900万円なら:
– **前年比**: `=900/800` → 112.5%(前年の112.5%の売上)
– **増減率**: `=(900-800)/800` → 12.5%(前年より12.5%増えた)
つまり、「前年比112.5%」と「増減率12.5%」は同じ状況を表しているけれど、見方が違うんです。
Excelでの計算式
前年売上がB2、今年売上がC2なら:
– **前年比**: `=C2/B2`
– **増減率**: `=(C2-B2)/B2`
どちらもパーセント表示にできますが、見出し名を明確にしておかないと、読み手が混同しやすくなります。
特に「前年比120%」と「前年比20%増」は同じような意味ですが、「前年比20%」は前年の2割しか売れていないという意味になってしまいます。
資料では「前年比(前年=100%)」や「増減率(前年差÷前年)」のように補足を入れると、誤読を防げます。
売上が減った場合は?
売上が減った場合も、計算の考え方は同じです。
前年売上が1,000万円、今年売上が850万円なら:
– **前年比**: `=850/1000` → 85%
– **増減率**: `=(850-1000)/1000` → -15%
前年比が100%を下回ると前年割れ、増減率がマイナスになると減少を意味します。
Excelではマイナスの増減率を赤字で表示したり、条件付き書式で背景色を変えたりすると、悪化している項目を見つけやすくなります。
また、前年売上が0の場合は増減率を計算できないので:
“`
=IF(B2=0,”前年実績なし”,(C2-B2)/B2)
“`
こうすると、異常値と通常の減少を区別できます。
Excelのパーセント表示と実務で使える設定のコツ
パーセント表示でよくある間違い
Excelで利益率・達成率・前年比を扱うとき、特に間違いやすいのが**パーセント表示**です。
Excelでは、割合の実体は小数で管理されています。
たとえば、0.2という値をパーセント表示にすると20%になります。
だから、利益率を `=利益/売上` で計算した結果が0.25なら、セルの表示形式を「パーセント」にするだけで25%と表示できます。
**ここで注意!** `=利益/売上*100` と入力したうえでパーセント表示にすると、2500%のように過大な表示になってしまいます。
数式で100を掛けるのか、表示形式でパーセントにするのかを混在させないことが大切です。
既存データをパーセント表示に変えるときの注意点
セルに「20」と入力されている状態でパーセント表示を適用すると、Excel上では**2000%**と表示されます。
これは、20という数値を「20倍」と解釈してパーセント表示するためです。
20%として扱いたい場合は:
– 元の値を0.2にする
– 最初から「20%」と入力する
売上管理表では、入力ルールを統一し、計算列は数式で自動算出する形にしておくと安全です。
小数点以下の桁数はどうする?
会議資料や報告書では、利益率や達成率は小数点1桁または2桁で表示されることが多いです。
Excelでは、ホームタブの「%」ボタンでパーセント表示にし、小数点以下の表示桁数を増減できます。
ただし、**表示桁数を変えても、セル内部の値そのものは変わりません**。
計算結果自体を丸めたい場合は:
“`
=ROUND(C2/B2,4)
“`
この例では小数第4位まで丸めるため、パーセント表示では小数点以下2桁程度の精度になります。
– 切り上げなら `ROUNDUP`
– 切り捨てなら `ROUNDDOWN`
社内ルールに合わせた端数処理が可能です。
実務で使いやすい表の作り方
最後に、売上管理表を実務で使いやすくするには、**数式をコピーしやすい構造**にしておくことが大切です。
列の並びは、元データから計算結果へ流れる順番にすると確認しやすくなります:
1. 前年売上
2. 今年売上
3. 売上差額
4. 前年比
5. 増減率
6. 目標
7. 実績
8. 達成率
9. 利益額
10. 利益率
計算式には、売上が0の場合の `IFERROR` や `IF` 関数を入れておくと、空白行や未入力行があっても表が崩れません。
さらに、条件付き書式やグラフを組み合わせれば、数字の羅列ではなく、**判断に使える売上管理表**になります。
Excelの数式は一度正しく作れば、毎月の入力作業を減らし、利益率・達成率・前年比を安定して管理できる強力な仕組みになりますよ。
広告
