Excelで売上構成比、達成率、男女比、部門別の人数比などを求めたい場面では、数値を単純に割るだけでなく、分母を固定する方法やパーセント表示の設定を理解することが重要です。
比率の計算式を正しく入力できれば、データ量が増えてもオートフィルで効率よく計算できます。
この記事では、Excelで比率を計算する基本式から、関数を使う場面、百分率表示、合計が100パーセントにならないときの確認方法までを解説します。
比率は対象の数値を全体の数値で割ることで求めます。
パーセントで表示したいときは、計算結果に100を掛けるのではなく、通常は表示形式をパーセンテージに変更します。
複数行へ式をコピーするときは、全体を示す分母のセルを絶対参照にすることがポイントです。
表の1行目には見出しが入力されているものとして、実務で使いやすい例を確認していきましょう。
比率を求める基本計算式【対象数値を合計で割る】
| 商品 | 売上 | 構成比 |
|---|---|---|
| 商品A | 120000 | 40% |
| 商品B | 90000 | 30% |
| 商品C | 90000 | 30% |
| 合計 | 300000 | 100% |
それではまず、比率を計算するもっとも基本的な考え方について解説していきます。
比率の意味と分子・分母の関係
比率とは、全体の中で特定の項目がどれほどの割合を占めるかを表す数値です。
商品Aの売上が120000円で、全商品の売上合計が300000円なら、商品Aの構成比は120000を300000で割って求めます。
比率の基本式
対象となる数値 ÷ 全体の数値
商品Aの構成比
120000 ÷ 300000 ＝ 0.4
計算結果の0.4は小数で表した割合です。
パーセントに直すと40パーセントとなり、売上全体のうち商品Aが4割を占めることが分かります。
分子には知りたい項目の数値、分母には比較の基準となる全体の数値を入力することが、計算ミスを防ぐ基本です。
全体が何を指すかは、集計の目的によって変わります。
月ごとの売上比率なら月内の合計を分母にし、年間売上に占める月別売上の割合なら年間合計を分母にします。
同じ数値でも分母を変えると意味が変化するため、表を作る前に比較の対象を決めておきましょう。
セル参照による最初の計算式
商品Aの売上がB2セル、売上合計がB5セルに入っている場合、C2セルには次の式を入力します。
=B2/B5
Enterキーを押すと、C2セルには0.4と表示されます。
これは計算が失敗したわけではなく、Excelが割合を小数で表示している状態です。
セルに数値を直接入力する代わりにセル番地を使うため、B2またはB5の値を変更すると、C2の計算結果も自動で更新されます。
これがExcelで比率を計算する大きな利点です。
電卓で一つずつ計算した値を入力すると、元データが変わるたびに再計算が必要になります。
一方で数式を残しておけば、売上や人数などの元データだけを更新する運用が可能です。
比率のセルには値ではなく数式を入れるという考え方を定着させると、表の保守が楽になります。
パーセント表示への変更操作
小数で表示された比率を見やすい百分率にするには、比率を計算したC2セルを選択します。
次に、ホームタブの数値グループにあるパーセントスタイルのボタンをクリックします。
0.4は40パーセント、0.075は7.5パーセントのように、表示だけが変わります。
セル内部の実際の値は0.4のままであり、Excelが表示時に100倍してパーセント記号を付けています。
そのため、数式の末尾に100を掛けてからパーセント表示にすると、4000パーセントのような意図しない結果になります。
【操作のポイント】比率をパーセント表示にするときは、まず割り算だけの数式を入力し、その後で表示形式を変更します。
合計セルを固定する比率計算【絶対参照の利用】
| 部門 | 人数 | 構成比の式 |
|---|---|---|
| 営業部 | 18 | =B2/$B$5 |
| 開発部 | 15 | =B3/$B$5 |
| 管理部 | 7 | =B4/$B$5 |
| 合計 | 40 |
続いては、複数の項目に比率の式をコピーするときに欠かせない絶対参照を確認していきます。
相対参照だけでコピーした場合のずれ
C2セルに=B2/B5と入力し、フィルハンドルを下へドラッグすると、C3セルの式は=B3/B6に変わります。
Excelは通常、数式をコピーした距離に合わせてセル番地を自動調整します。
この仕組みは相対参照と呼ばれます。
しかし、B6セルが空欄ならC3セルではエラーが出たり、意図しない比率になったりします。
構成比では、分子である部門ごとの人数は行に応じて変わる必要があります。
一方で、分母となる合計人数は常にB5セルを参照しなければなりません。
コピーしても動かしたくないセル番地にはドル記号を付けることが必要になります。
絶対参照を使った正しい数式
C2セルには、次の数式を入力します。
=B2/$B$5
$B$5は列Bと行5の両方を固定する絶対参照です。
この数式をC3セルへコピーすると、分子だけがB3に変わり、分母は$B$5のまま維持されます。
続くC4セルでも、式は=B4/$B$5になります。
式を入力中にB5の参照部分を選択し、F4キーを押すと、B5、$B$5、B$5、$B5の順に参照形式を切り替えられます。
Windows版Excelでは、F4キーで絶対参照を付ける方法が特に便利です。
ノートパソコンではFnキーとの同時押しが必要になる場合もあります。
セル番地を手入力する場合でも、ドル記号の位置を確認してからEnterキーを押しましょう。
オートフィルで式を一括コピーする操作
正しい式が入ったC2セルを選択すると、右下に小さな四角形のフィルハンドルが表示されます。
この四角形を下方向へドラッグすれば、連続した行へ数式をコピーできます。
隣接するB列にデータが連続している場合は、フィルハンドルをダブルクリックする方法も便利です。
Excelがデータの最終行を判断し、その位置まで式を自動コピーします。
コピー後は、数式バーでC3やC4を選び、分母がすべて$B$5になっているか確認してください。
合計欄の行まで式をコピーすると、合計値を合計値で割った100パーセントが表示されることがあります。
構成比の表では合計行を除外してコピー範囲を選ぶと、見やすい仕上がりになります。
【操作のポイント】比率を下方向へコピーする前に、分母のセル番地だけは絶対参照になっているか確認します。
パーセント表示と小数点の調整【表示形式の設定】
| 計算結果 | 表示形式 | 表示例 |
|---|---|---|
| 0.4 | 標準 | 0.4 |
| 0.4 | パーセンテージ | 40% |
| 0.07625 | パーセンテージ 小数第2位 | 7.63% |
続いては、比率を読みやすく伝えるためのパーセント表示と小数点の設定を確認していきます。
ホームタブでパーセントスタイルを適用する方法
比率を入力したセルまたはセル範囲を選択して、ホームタブのパーセントスタイルをクリックします。
この操作は、表示形式をパーセンテージに変更する最短の方法です。
たとえば0.333333という結果なら、初期設定では33パーセントと表示されます。
整数の割合で十分なアンケート結果や、部門別構成比の一覧では、これだけでも実用的でしょう。
ただし、細かな差を比較したいときは整数表示だけでは情報が不足することがあります。
販売手数料率、進捗率、原価率などでは、小数点以下の桁数を表示するほうが判断しやすい場合があります。
小数点以下の桁数を増減する方法
ホームタブの数値グループには、小数点以下の表示桁数を増やすボタンと減らすボタンがあります。
パーセント表示にしたセルを選び、このボタンをクリックすると、40パーセントを40.0パーセント、40.00パーセントのように変更できます。
表示桁数を変えても、セルに保存されている計算結果そのものは変わりません。
0.07625を小数第1位で表示すると7.6パーセントになり、小数第2位で表示すると7.63パーセントになります。
この差は四捨五入による表示上の差です。
取引先へ提出する資料では、社内規定や帳票の単位に合わせて桁数をそろえるとよいでしょう。
表の中で7パーセントと7.63パーセントが混在すると読みにくいため、同じ列では表示形式を統一することが大切です。
Excel画面での表示形式変更イメージ
パーセントスタイルと小数点以下の桁数を設定するときのExcel画面は、次のような構成です。
罫線 ▦
％ パーセントスタイル
➤
小数点以下の表示桁数を増やす
| A | B | C | |
|---|---|---|---|
| 1 | 商品 | 売上 | 構成比 |
| 2 | 商品A | 120000 | 40.00% |
| 3 | 商品B | 90000 | 30.00% |
| 4 | 商品C | 90000 | 30.00% |
選択中のC2セルでは、数式バーに=B2/$B$5と表示され、シート上では40.00パーセントと表示されています。
数式と表示形式は別々に管理されるため、同じ数式でも資料の目的に応じた見せ方が可能です。
【操作のポイント】比率の意味を損なわない範囲で、小数点以下の桁数を統一し、表全体の読みやすさを整えます。
関数を使った比率計算【SUM関数とSUMIF関数】
| 担当者 | 受注額 | 受注構成比 |
|---|---|---|
| 田中 | 450000 | =B2/SUM($B$2:$B$4) |
| 佐藤 | 380000 | =B3/SUM($B$2:$B$4) |
| 鈴木 | 170000 | =B4/SUM($B$2:$B$4) |
続いては、合計セルを別途作成しない場合や、条件付きで集計する場合に使える関数による比率計算を確認していきます。
SUM関数を分母に入れる方法
データの最終行が変わる表では、分母にSUM関数を直接入れる方法が役立ちます。
=B2/SUM($B$2:$B$4)
この式では、B2セルの受注額をB2からB4までの合計で割っています。
SUM関数は指定したセル範囲の数値を合計する関数です。
分母の範囲を絶対参照にしておけば、数式を下の行へコピーしても合計範囲がずれません。
合計セルを表に表示したくない場合でも、SUM関数なら数式の中で全体値を計算できます。
ただし、データを追加してB5セルに新しい担当者を入力したとき、SUMの範囲がB2からB4のままでは新しい受注額が合計に含まれません。
データをテーブルとして管理するか、あらかじめ余裕を持った範囲を設定する方法を検討しましょう。
SUMIF関数で条件ごとの構成比を求める方法
カテゴリ別、支店別、担当別などの比率を集計する場合は、SUMIF関数が便利です。
たとえばA列に支店名、B列に売上があり、E2セルに東京支店という条件が入力されているとします。
=SUMIF($A$2:$A$20,E2,$B$2:$B$20)/SUM($B$2:$B$20)
SUMIF関数は、A2からA20の範囲でE2と一致する支店名を探し、その行のB列の売上を合計します。
この合計が分子です。
分母には全支店の売上合計を置くため、東京支店が全社売上に占める割合を求められます。
複数条件で集計する必要があるときはSUMIFS関数を使います。
たとえば支店に加えて月や商品分類まで指定したい場合に活用できます。
条件付きの比率では、分子と分母が同じ集計対象になっているかを必ず確認してください。
ピボットテーブルで構成比を表示する方法
元データの件数が多く、カテゴリごとの構成比を繰り返し確認するなら、ピボットテーブルも選択肢になります。
元データ内のセルを選択して、挿入タブからピボットテーブルを作成します。
行に分類項目、値に売上などの数値項目を配置すると、分類別の合計を集計できます。
値フィールドの設定から値の表示方法を選び、総計に対する比率を指定すると、構成比を自動表示できます。
数式を1行ずつ作る方法に比べて、集計軸を変更しやすい点が特徴です。
月別、支店別、商品別といった角度を切り替えながら比率を確認したい場合に向いています。
【操作のポイント】固定的な表にはSUM関数、条件別の集計にはSUMIF関数、集計軸を変える分析にはピボットテーブルを使い分けます。
割合と比率の応用計算【達成率と前年比】
| 項目 | 実績 | 目標または前年 | 計算式 |
|---|---|---|---|
| 達成率 | 850000 | 1000000 | =B2/C2 |
| 前年比 | 850000 | 800000 | =B3/C3 |
| 増減率 | 850000 | 800000 | =(B3-C3)/C3 |
続いては、日常的な集計で使われる達成率、前年比、増減率へ比率計算を応用する方法を確認していきます。
目標に対する達成率の計算
達成率は、実績が目標に対してどこまで進んでいるかを示す割合です。
=実績/目標
例として実績が850000、目標が1000000の場合
=850000/1000000
結果は85パーセント
実績を分子、目標を分母にする順番を反対にしないことが大切です。
目標を実績で割ると、必要な目標額が実績の何倍かという別の意味になります。
達成率が100パーセントなら目標達成、100パーセントを超えれば目標超過です。
進捗管理表では、条件付き書式を組み合わせると、80パーセント未満を目立たせるなどの運用もできます。
前年比と増減率の違い
前年比は、今年の実績が前年の何パーセントに当たるかを示します。
=今年の数値/前年の数値
今年が850000、前年が800000なら、前年比は106.25パーセントです。
前年比が100パーセントを超えると前年より増加し、100パーセント未満なら前年より減少しています。
一方、増減率は前年からどの程度増減したかを示す指標です。
=(今年の数値-前年の数値)/前年の数値
同じ例の増減率は6.25パーセントです。
前年比106.25パーセントと増減率6.25パーセントは似ていますが、意味は異なります。
前年比は基準年を100とする指数、増減率は基準年からの変化量として使い分けましょう。
比率の逆算と全体数の求め方
比率と一部の数値が分かっている場合、全体数を逆算することもできます。
たとえば全体の25パーセントが150人であるなら、全体人数は150を25パーセントで割って求めます。
=150/25%
結果は600
Excelでは25パーセントを0.25として扱うため、=150/25%と入力すれば600が返されます。
また、全体数と割合が分かっていて一部の値を求める場合は、全体数に割合を掛けます。
全体600人の25パーセントを求める式は=600*25%です。
比率の問題では、求める値が部分なのか全体なのかを見極めることで、掛け算と割り算を選べます。
【操作のポイント】達成率、前年比、増減率は分母が異なります。計算前に比較の基準を言葉で確認すると、式の取り違えを防げます。
比率計算のエラー対策【ゼロ除算と合計確認】
| 状況 | 原因 | 対処例 |
|---|---|---|
| #DIV/0! | 分母が0または空欄 | =IFERROR(B2/$B$5,””) |
| 合計が100%にならない | 丸め表示や集計漏れ | 元の数式と範囲を確認 |
| 極端な値になる | 分母参照のずれ | 絶対参照を確認 |
続いては、比率を計算したときに起こりやすいエラーや表示上の疑問について確認していきます。
ゼロ除算エラーの防止
分母が0または空欄のセルを割り算に使うと、Excelには#DIV/0!というエラーが表示されます。
売上が未入力の月、対象者がいない部署、集計前の空欄行などで起こりやすい現象です。
エラー表示を空欄にしたい場合は、IFERROR関数を使えます。
=IFERROR(B2/$B$5,””)
この式は通常の割り算を行い、エラーになった場合だけ空欄を表示します。
ただし、エラーを隠すことで元データの入力漏れに気づきにくくなる場合もあります。
空欄表示が適切か、エラーを残して確認を促すべきかは、表の用途に応じて決めましょう。
分母が0のときに0パーセントを表示するなら、=IFERROR(B2/$B$5,0)という式も使えます。
構成比の合計が100パーセントにならない原因
構成比を合計したとき、99パーセントや101パーセントになることがあります。
もっとも多い理由は、小数点以下を丸めて表示していることです。
たとえば33.33パーセントの項目が3つある場合、表示上の合計は99.99パーセントです。
表示桁数を減らすと、それぞれ33パーセントとなって合計99パーセントになることもあります。
元の計算値に問題がないなら、これは丸め誤差による見え方です。
一方で、合計範囲から一部の行が漏れている、分母が異なるセルを参照している、フィルターで非表示の行を意図せず含めているといった原因も考えられます。
合計が100パーセントから大きく離れるときは、表示形式より先に数式の分母と集計範囲を見直してください。
入力値と表示形式を確認する手順
比率が予想と違うときは、結果のセルを選択し、数式バーで実際の計算式を確認します。
次に、分子のセルと分母のセルをそれぞれ選択して、数値が文字列として保存されていないか確認しましょう。
数値が左寄せで表示されていたり、セル左上に緑色の三角形が表示されたりする場合は、文字列扱いの可能性があります。
また、表示形式がパーセンテージなのに数式で100を掛けていないかも確認が必要です。
計算式、参照先、表示形式の三つを順番に確認すると、ほとんどの問題を切り分けられます。
【操作のポイント】比率の異常値は、分母が0か、参照セルがずれているか、表示形式が重複しているかの順に確認します。
まとめ エクセルでパーセント表示する比率計算の方法
Excelで比率を計算する基本は、対象となる数値を全体の数値で割ることです。
売上構成比や人数比では、分子に個別の値、分母に合計値を指定します。
複数行へ式をコピーする場合は、合計セルを$B$5のように絶対参照で固定することが重要です。
計算結果は小数で返されますが、パーセントスタイルを適用すれば見やすい百分率に変更できます。
数式内で100を掛ける必要はなく、表示形式だけを変更するのが基本です。
合計セルを使わない表ではSUM関数を分母に入れ、条件別の割合にはSUMIF関数、集計軸を切り替える分析にはピボットテーブルが役立ちます。
達成率は実績を目標で割り、前年比は今年の数値を前年の数値で割り、増減率は前年との差を前年で割る計算です。
分母の意味を明確にしてから数式を作れば、割合、構成比、進捗率を正確に扱えるようになります。
ゼロ除算や構成比の合計にも注意しながら、Excelの比率計算を日々の集計や資料作成に活用していきましょう。
