エクセルで売上の構成比、目標に対する達成率、前年比、アンケートの回答割合などを計算したい場面は多くあります。
割合は基本の計算式を理解すれば難しくありませんが、分母と分子を反対にしたり、表示形式だけをパーセントにして数値が想定と異なったりすることもあります。
この記事では、比率・達成率・パーセンテージをエクセルで正確に出す方法を、サンプルデータと数式を交えながら解説します。
割合の基本は、比べたい数値を基準となる数値で割る計算です。
比率 ＝ 分子 ÷ 分母
達成率 ＝ 実績 ÷ 目標
構成比 ＝ 各項目 ÷ 合計
計算結果を見やすいパーセンテージ表示に整える方法や、合計がゼロの場合のエラー対策も確認していきましょう。
エクセルで割合を出す基本計算式
| 商品 | 売上 | 全体に占める割合 |
|---|---|---|
| A商品 | 120,000 | 40.0% |
| B商品 | 180,000 | 60.0% |
| 合計 | 300,000 | 100.0% |
それではまず、割合を求めるもっとも基本的な考え方について解説していきます。
分子と分母の決め方
割合を求める式では、知りたい対象を分子、比較の土台となる全体を分母に置きます。
たとえばA商品の売上が120,000円で、全商品の売上合計が300,000円なら、A商品の構成比は120,000を300,000で割って求めます。
C2セルに割合を表示する場合の数式です。
=B2/B4
計算結果は0.4となり、パーセント表示に変更すると40.0%と表示されます。
分母に入れる数字は、対象範囲全体の合計であることが重要です。
120,000÷300,000ではなく300,000÷120,000としてしまうと、250%となって意味が変わります。
割合の計算前には、何を全体として扱うのかを文章で確認しておくとミスを防げます。
パーセント表示への変更
エクセルでは、数式で求めた0.4をそのまま入力しても、標準表示では0.4と表示されます。
0.4は小数として正しい計算結果であり、40%として見せるには表示形式の変更が必要です。
対象セルを選択し、ホームタブの数値グループにあるパーセントスタイルをクリックしましょう。
パーセントスタイルを設定すると0.4は40%になり、0.125は13%のように表示されます。
小数点以下も表示したい場合は、小数点表示桁数を増やすボタンを利用します。
金額の構成比では40.0%、分析値では40.00%など、用途に応じて桁数をそろえると表が読みやすくなります。
数式を下方向へコピーする方法
複数の商品や部署の割合を連続して計算するときは、最初のセルに式を入力してからオートフィルを使います。
ただし、合計セルへの参照はコピーしても動かないように、絶対参照のドル記号を付けることが必要です。
1行目がヘッダーで、B2からB4に売上、B5に合計がある場合の数式です。
=B2/$B$5
$B$5は行と列を固定するため、C2の式をC3やC4へコピーしても分母はB5のままです。
セル右下に表示される小さな四角を下へドラッグすると、行ごとの分子だけがB3、B4へと自動で変化します。
【操作のポイント】
割合の式は、分子を相対参照、合計の分母を絶対参照にすることが基本です。
比率と構成比の計算方法
| 部門 | 人数 | 構成比 |
|---|---|---|
| 営業部 | 18 | 45.0% |
| 開発部 | 14 | 35.0% |
| 管理部 | 8 | 20.0% |
| 合計 | 40 | 100.0% |
続いては、全体の中で各項目がどの程度を占めるかを示す構成比の計算を確認していきます。
構成比を求める数式
構成比は、売上・人数・費用・件数などの各項目を合計値で割って算出します。
営業部の人数が18人、全体が40人なら、18÷40で0.45となります。
C2セルに入力する構成比の数式です。
=B2/$B$5
セルの表示形式をパーセントにすると45.0%と確認できます。
構成比の各行を合計すると、原則として100%になるため、計算結果を検算する目安にもなります。
表示桁数による四捨五入の影響で99.9%または100.1%になることがありますが、元データの小数点以下まで確認すれば問題ないケースもあります。
合計をSUM関数で求める手順
分母となる合計が未作成なら、SUM関数で先に合計セルを作成します。
たとえばB2からB4までが部門別人数であれば、B5にSUM関数を入力します。
合計セルB5の数式です。
=SUM(B2:B4)
範囲内の数値を合計し、構成比の分母として利用できます。
手入力で合計を入れることもできますが、元の数値が変更されたときに合計と比率が更新されません。
SUM関数を使えば、人数や売上の入力を修正しても割合が連動して変わります。
集計表は合計セルも数式にしておくことが、更新漏れを防ぐ近道です。
円グラフと棒グラフへの活用
構成比を計算した表は、円グラフやドーナツグラフにすると割合の大きさを視覚的に伝えられます。
項目名と数値、または項目名と構成比のセル範囲を選択し、挿入タブから円グラフを選びます。
細かな差を比べたい場合は、棒グラフのデータラベルにパーセントを表示する方法も便利です。
項目数が多すぎると円グラフは見づらくなるため、その他にまとめる、上位項目だけを示すなどの工夫が必要です。
【操作のポイント】
構成比は100%になるかを確認し、表とグラフのどちらが伝わりやすいかを目的別に選びましょう。
達成率と進捗率の求め方
| 担当者 | 目標 | 実績 | 達成率 |
|---|---|---|---|
| 田中 | 500,000 | 425,000 | 85.0% |
| 佐藤 | 500,000 | 540,000 | 108.0% |
続いては、目標値に対する実績値から達成率や進捗率を求める方法を確認していきます。
実績を目標で割る達成率
達成率は、実績を目標で割ることで算出します。
田中さんの実績が425,000円、目標が500,000円なら、425,000÷500,000で0.85です。
D2セルに入力する達成率の数式です。
=C2/B2
パーセント表示に設定すると85.0%となります。
達成率では実績が分子、目標が分母という関係を覚えておくと、式を迷わず作成できます。
目標を上回った場合は100%を超え、佐藤さんのように108.0%と表示されます。
100%超過は数式の異常ではなく、目標達成後も実績が積み上がった結果です。
残り必要量の計算
達成率とあわせて、目標までに残っている金額や件数を出すと行動計画を立てやすくなります。
残り必要量は目標から実績を引く計算です。
E2セルに残り必要量を表示する数式です。
=MAX(B2-C2,0)
MAX関数を使うと、目標を超えたときもマイナスではなく0と表示できます。
未達分だけを管理したい表では、マイナス値を残さないこの方法が役立ちます。
一方で目標超過の金額も把握したい場合は、単純に=B2-C2と入力すると差額をそのまま表示できます。
期間途中の進捗率
月間や年間の途中で進み具合を確認するときにも、実績÷目標で進捗率を求められます。
ただし、月の前半に50%という進捗率であっても、日数に対して順調かどうかは別に判断する必要があります。
経過日数の割合と実績の進捗率を並べると、予定より早いか遅いかを比較できます。
進捗率は実績の割合、経過率は時間の割合であり、同じ意味ではありません。
【操作のポイント】
達成率は実績÷目標で計算し、100%を超えた値も正しく評価できるようにしましょう。
パーセント表示とオートフィルの操作
| 月 | 実績 | 目標 | 達成率 |
|---|---|---|---|
| 4月 | 82 | 100 | 82.0% |
| 5月 | 96 | 100 | 96.0% |
| 6月 | 105 | 100 | 105.0% |
続いては、割合をパーセントで見やすく表示し、数式をまとめてコピーする操作を確認していきます。
先頭セルへの数式入力
サンプルデータでは1行目にヘッダーがあり、4月の実績がB2、目標がC2に入力されているものとします。
達成率を表示するD2セルを選択し、数式バーまたはセル内に=C2/B2ではなく、実績÷目標となる=B2/C2を入力します。
D2セルの入力例です。
=B2/C2
Enterキーで確定した直後の値は0.82です。
この段階では計算は完了していますが、表示形式が標準のためパーセントにはなっていません。
数式と表示形式は別々に設定するという点が、エクセルで割合を扱う際の大切な考え方です。
パーセントスタイルの適用
D2を選択したまま、ホームタブの数値グループからパーセントスタイルをクリックします。
すると0.82が82%に変わり、セル内の値そのものは変えずに見た目だけがパーセント表示になります。
小数第1位まで必要なら、ホームタブの小数点以下の表示桁数を増やすボタンを1回クリックします。
82%が82.0%と表示され、月別や担当者別の比較がしやすくなります。
オートフィルによる一括コピー
D2の右下にあるフィルハンドルをダブルクリック、またはD4までドラッグすると、数式が下の行へコピーされます。
コピー後のD3には=B3/C3、D4には=B4/C4が自動で入力されます。
同じ行内の実績と目標を比較する式は、通常の相対参照のままでコピー可能です。
分母が毎行同じ合計セルである構成比とは、参照の固定方法が異なります。
【操作のポイント】
先頭セルで式と表示形式を完成させてからオートフィルを行うと、作業を短縮できます。
割合計算のエラーと注意点
| 項目 | 実績 | 目標 | 結果 |
|---|---|---|---|
| 通常 | 80 | 100 | 80.0% |
| 目標未入力 | 30 | 0 | 表示なし |
続いては、割合の計算で起こりやすいエラーと、正確な集計につなげる注意点を確認していきます。
ゼロ除算エラーへの対処
分母が0または空白のセルを割ると、エクセルではDIV/0!エラーが表示されます。
目標値や合計がまだ入力されていない表では、エラーを非表示にしたいこともあるでしょう。
分母が0のときに空白を返す数式です。
=IF(C2=0,””,B2/C2)
C2が0なら空白、それ以外なら実績÷目標を計算します。
エラーを隠す前に、分母が0になった理由を確認することが大切です。
未入力なのか、本当に0件なのかで、表の意味や必要な対応は変わります。
百分率と小数の入力ミス
エクセルでは10%と入力すると0.1として扱われますが、10と入力してパーセント表示にすると1000%になります。
割合を直接入力する場合は、10%のようにパーセント記号を付けるか、0.1と小数で入力します。
10%は0.1、1%は0.01として計算されることを押さえましょう。
逆に、計算式の中で100を掛けたうえでパーセント表示を設定すると、数値が100倍になります。
表示形式をパーセントにするなら、通常は式に100を掛ける必要はありません。
表示桁数と丸めの扱い
表で33.3%と表示されていても、実際のセルには33.333333%のような細かな値が保存されていることがあります。
見た目だけを丸めるには表示桁数を減らし、計算そのものも丸めるにはROUND関数を使用します。
小数第1位のパーセント値として計算結果を丸める数式です。
=ROUND(B2/C2,3)
割合を小数で扱うため、小数第1位のパーセントは小数第3位まで丸めます。
請求額や配分額のように端数処理が重要な表では、表示と実計算の違いを意識する必要があります。
【操作のポイント】
分母が0のケース、入力単位、丸める位置を先に決めると、割合表の信頼性が高まります。
割合の計算に役立つ関数と応用
| 区分 | 件数 | 全件数 | 割合 |
|---|---|---|---|
| 完了 | 76 | 100 | 76.0% |
| 未完了 | 24 | 100 | 24.0% |
続いては、条件付きの集計や前年比など、実務で役立つ割合計算の応用を確認していきます。
COUNTIF関数による件数割合
アンケートやタスク管理表では、特定の条件に当てはまる件数の割合を求めることがあります。
たとえばB2からB101に完了または未完了の文字が入力されている場合、完了率はCOUNTIF関数で求められます。
完了率を求める数式です。
=COUNTIF(B2:B101,”完了”)/COUNTA(B2:B101)
完了という文字の件数を、空白ではない回答数で割ります。
条件に合う件数を全件数で割る方法は、回答率や出席率にも応用可能です。
空白の回答を分母に含めるかどうかは、集計ルールに合わせて判断しましょう。
前年比と増減率
前年の数値に対して今年の数値がどの程度増減したかを調べるには、増減額を前年値で割ります。
今年の売上が120万円、前年の売上が100万円なら、増減率は20%です。
B2が前年、C2が今年の場合の前年比増減率の数式です。
=(C2-B2)/B2
結果にパーセント表示を設定すると、前年からの増加率または減少率を確認できます。
前年比そのものと、前年比の増減率は意味が異なる点に注意が必要です。
今年÷前年は120%で、そこから前年を基準とした増減率は20%となります。
条件付き書式による見える化
達成率のセルに条件付き書式を設定すると、数値を読む前に進捗状況を把握できます。
たとえば100%以上を緑、80%未満を赤で表示するルールを作れば、対応が必要な行を見つけやすくなります。
データバーを使うと、セル内に横棒が表示され、割合の差を直感的に比較できます。
ただし色だけに頼らず、パーセントの数値も表示しておくと、印刷時や白黒表示でも情報が伝わります。
【操作のポイント】
関数で集計条件を明確にし、条件付き書式で重要な割合を目立たせると、分析表を活用しやすくなります。
まとめ エクセルで割合を出す方法（パーセンテージ・達成率・比率）
エクセルで割合を出す基本は、比較したい数値を全体または基準となる数値で割ることです。
構成比では各項目÷合計、達成率では実績÷目標、増減率では増減額÷基準値というように、目的に応じて分母を正しく選びましょう。
計算結果が0.4なら、パーセント表示で40%に整えることで、表を読む人に数値の意味が伝わりやすくなります。
合計セルを固定して数式をコピーする構成比では絶対参照を使い、行ごとに実績と目標を比べる達成率では相対参照を使うのが基本です。
分母が0になる場合はIF関数で表示を調整できますが、まずはデータの未入力や集計条件に問題がないかを確認しましょう。
割合、比率、達成率、パーセンテージを使い分ければ、売上分析、進捗管理、アンケート集計、予算管理などの表をより正確で見やすいものにできます。
