この記事では、「エクセルにてパーセントの合計値がおかしい(100にならない:計算合わない)時の対策」について解説していきます。
やり方としては
2.ROUND関数を使用する方法
3.割合(%)が最も大きい要素で調整する方法
の3つの方法が一般的です。
それでは、具体例を用いて解説していきます。
エクセルにてパーセントの計算合わない(100にならない:表示がおかしい)時の対策【合計】
エクセルにてパーセント(%)の表示がおかしくなる原因は、四捨五入にあります。
合計の値から割合(パーセンテージ)を計算した際、解が割り切れない場合は四捨五入された値が表示されます。
したがって、表示された値を合計しても100%にならない場合があるのです。
以上のことを踏まえた上で、合計を100%にする(100%に近づける)方法について、具体例を示しながら解説していきます。
上に示した例において、各年代の割合(%)は
で計算しています。
割合(%)は小数第一位で四捨五入し、整数で表示しています。
割合(%)の合計のセル(D11)には
の関数が入力されています。
SUM関数で計算すると合計は100%になっています。
しかし、実際に整数の割合の値を足していくと
のように、合計が101%となるため計算が合いません。
合計を100%にする(100%に近づける)ために、小数点以下の桁まで表示してみましょう。
各年代の割合(%)のセル(ここではD4:D10)を選択し、右クリックします。
「セルの書式設定」を選択します。
「セルの書式設定」ダイアログボックスが開きます。
「表示形式」タブの「分類」から「数値」を選択します。
小数点以下の桁数を「1」に設定し、「OK」をクリックします。
小数第二位で四捨五入され、小数第一位まで表示されました。
実際に割合(%)の7つの値を足すと、合計で100%となります。
別のやり方として、各年代の割合(%)のセル(ここではD4:D10)を選択した状態で、「ホーム」タブの「数値」→「小数点以下の表示桁数を増やす」を1回クリックしても同じ結果が得られます。
今回の例では小数第一位まで表示することで100%となりましたが、数値によっては100%にならないこともあります。
そういった場合には、表示される小数点以下の桁数を増やすことでより100%に近づけることができます。
エクセルにてパーセントの計算が合わない(表示がおかしい)原因と対策2
別の方法として、ROUND関数を使用する方法を紹介します。
この例では、各年代の割合(%)を足していくと合計が101%になってしまいます。
これを合計100%にするためには、いずれかの年代の割合を1%小さくする必要があります。
ここで、割合(%)を小数第一位まで表示させてみると、20代、50代、60代、70代以上の割合(%)が小数第一位で四捨五入されることによって切り上げられていることが分かります。
今回は、切り上げられている割合の中で最も小数部分の値が小さい20代の割合(%)「19.5」の小数部分を切り捨てて「19」とする方法を解説します。
ROUNDDOWN関数を使用して、指定の位で切り捨てます。
(指定の位で四捨五入するROUND関数、指定の位で切り上げるROUNDUP関数もあります。)
ROUNDDOWN関数は、
のように使用します。
二つ目の引数である「桁数」には、切り捨てた後に表示される最小の位を指定します。
位は以下のように割り当てられています。
小数第一位:1
一の位:0
十の位:–1
例えば小数第二位で切り捨てたい場合、表示される最小の位は小数第一位であるため、桁数の指定は小数第一位を表す「1」になります。
四捨五入のROUND関数、切り上げのROUNDUP関数も使い方は同じです。
今回の例では小数第一位で切り捨てたいため、桁数には「0」を指定します。
20代の割合(%)のセル(D5)に
と入力します。
20代の割合(%)が「19」%となりました。
この方法で合計を100%にすることができましたが、ROUNDDOWN関数を入力したセルの左上に緑色の三角形が表示されています。
この三角形はセルにエラーが起きていることを示すものであり、このセルを選択すると「矛盾した数式」というエラー警告が表示されます。
この警告は、周囲(この場合は上下)のセルと異なる計算方法を用いているため表示されています。
つまり、この方法で見かけ上100%にすることはできましたが、あくまで見た目を整えただけであって実際には正確な値ではないということです。
その証拠に、割合(%)の合計をSUM関数で求めると99%になってしまいます。
値の整合性が重視される場合は、見かけ上の合計が100%にならなくても全て同じ計算方法を用いる方が適切であると言えます。
エクセルにてパーセントの表示がおかしい(計算合わない)原因と対策3【合計が100にならない】
最後に、割合(%)が最も大きい要素で調整する方法を紹介します。
上でも述べたように、この例では、合計100%にするためにいずれかの年代の割合を1%小さくする必要があります。
どの年代の割合を小さくするかを選ぶ際、全体に占める割合が最も大きい要素を選択する方法があります。
今回の例では、10代以下が最も割合(%)が大きいため、10代以下の割合(%)「26」を1%小さくします。
要素が多い場合はRANK.EQ関数などを使って、割合(%)が最も大きい要素を調べるとよいでしょう。
10代以下の割合(%)のセル(D4)の数式の最後に「-1」と入力します。
10代以下の割合(%)が「25」%となり、合計が100%になりました。
しかし、この方法も二番目に紹介した方法と同じく、見かけ上の合計を100%に合わせる方法です。
そのため、値の整合性が重視される場面では用いない方がよいでしょう。
まとめ エクセルにてパーセントの合計が100にならない(計算が合わない)原因と対策
この記事では、「エクセルにてパーセントの表示がおかしい(計算合わない)原因と対策」について解説しました。
割合(%)の合計を100%にする方法として、
1.小数点以下の桁まで表示する方法
2.ROUND関数を使用する方法
3.割合(%)が最も大きい要素で調整する方法
の3つの方法を紹介しました。
パーセント表示は、必ずしも常に合計が100%になるとは限りません。
目的に応じて、適切な表示形式を選択できるようにしましょう。
コメント