エクセルにあるデータの傾向を数値に表示して知りたい場合には、平均値というものを使用します。
エクセルでは、入力されたデータを範囲指定することで平均値を表すことができますが、データ内に大きすぎる数値や小さすぎる数値があることで大小どちらかに偏り、平均値とはいえど、ある値に影響されてしまい、ちょうどよい数値を表すことができなくなってしまいます。
このように、平均値だけではデータの中央にある数値が不明確となってしまう場合には「中央値」を知り、中央値を求める関数を活用するのがおすすめです。
この記事では、大小の数字によって上振れや下振れしてしまった平均値を、中央値を求める関数を用いて現実の平均値に近い中央の数値を出す方法を解説します。
プレゼン資料としてだけでなく、ライターとして世帯年収などをテーマにしたデータを用いた記事をライティングする際にも大変役立ちますから、この機会にしっかりとマスターしておきましょう。
目次
エクセルの中央値とは
エクセルを使ううちに使うことの多くなる中央値。平均ではなく、そのデータ内の中央となる値を指します。
そもそも中央値とは、世帯年収や地域別での年収を調査する際に使われるもので、平均値とは異なる数値を表します。
まずはこちらの表を見ていきましょう。
AさんからIさんまでの月収を調査した表があります。
AさんからHさんまでのおおよその平均月収は目視で確認することができますが、Iさんの150万円が入ったことによって、エクセルの右下に表示された平均値が「503,333」になっているように、平均月収がぐっと上がっていることが確認できます。
平均値はすべての値を合計して、その人数分に等しく分けた時の値になるため、平均値としては正しいですが、多くが30万～40万の月収しか貰えていないこのグループの平均月収が約50万かといわれると、正しい平均値とはいえません。
しかしこのような平均値ではなく、ある関数を用いることで、Iさんの150万円の年収が組み込まれても、CさんやGさんの年収である39万円がこのグループが実際に貰っている月収に近い値であることがわかります。
ここでは中央値を求める関数を使っています。中央値は個々の数値を見るのではなく、大きい数字から数えて真ん中に位置する月収額がいくらなのかを求めるため、ボーダーラインがよりリアルな平均的な年収額に近づく、という仕組みになっています。
そのため世帯ごとの年収や、地域ごとの年収を調査するときは、個々の金額の大きさに影響されることなく、どの値がそのグループの真ん中になっているかを求めるために、中央値が必要であることをここで覚えておきましょう。
【平均値】データを全て足した合計値をデータ数で割った値のこと
【中央値】データを昇順もしくは降順に並べた上で真ん中の順位に位置する値のこと
【最頻値】データの中で最も多く出現する値のこと
平均値は本当にデータの真ん中、普通を示しているとは限らない。データの真ん中を知りたい場合には、中央値も求める方が正確。また、データ上の多数決を優先したいような場合は、最頻値を求めるのがいい。
中央値を求める方法
データの平均を求める場合は、データをマウスで範囲指定することで、エクセルの表の右下に自動的に平均値が出るようになっています。
関数を使わなくても簡単に確認できますが、これでは平均であるため、1つのデータによってぐっと平均値が高くなってしまっています。今回はIさんの150万円の月収が全体の平均値を高くしています。リアルに近い月収の平均的な値、中央にくる値が不明確です。
そのような場合は【MEDIAN関数】を使用し、中央値を求めます。
MEDIANとは中央値の意味です。
MEDIAN関数の入力方法は次の通りです。
＝MEDIAN(数値1,[数値2],[数値3]……)
ちなみに数値は255個まで指定ができます。
もしくは
＝MEDIAN(
と入力してからマウスで中央値を求めたい値の範囲を指定し【)】で閉じるというやり方があります。
上記のような表の場合は、
平均セルを求めたいセル内に
=MEDIAN（
と入力してから中央値を求めたい値の範囲を指定し【)】で式を閉じて【Enter】を押します。この際の「：」は「～」の意味があります。
これでAさんからIさんの月収における中央値を求めることができました。
なお、少ないデータ量であれば目視で中央値を出すこともできます。
数値を並びかえて、真ん中の値を確認するという方法です。
まず、並びかえたいセルをすべて選択します。そしてエクセル上部にある【ホーム】タブのなかにある【並び替えとフィルター】をクリックし、メニューを開き【昇降】を選ぶと、それぞれの数値が上から少ない順に並べ替えられます。
データの総数が偶数であれば、真ん中の数値が中央値になります。奇数の場合は2つの値が中央値となってしまいますが、大体の値がわかるので、目視での確認もおすすめです。
【平均値】【最頻値】を合わせて覚えてよう
中央値と同じくらい使うことが多いのが
- 平均値
- 最頻値
の2種類です。また、平均値、最頻値、中央値などを総合して「代表値」と呼ぶことがあります。
それぞれアンケートやデータの傾向を知るためには必要な値であるため、あわせて覚えておくようにしましょう。
平均値の意味や求める方法とは
平均値とはあらゆるデータの平均を求める関数を指します。主な関数としては【AVERAGE関数】【TRIMMEAN関数】を使用します。
それぞれの関数は以下のようになります。
| AVERAGE関数 | =AVERAGE(数値1,数値2,数値3,……) |
| TRIMMEAN関数 | =TRIMMEAN（数値1,数値2,0.2) |
AVERAGE関数
平均値は前述したように、エクセルでは平均値を出したい値の範囲指定をおこなうと、自動的に表の右下に平均値が出てくる仕様になっていますが、AVERAGE関数を用いることで、範囲指定をせずに平均値を求めることができます。データ量が膨大な場合、また数値の変動が激しい場合は関数を使って求めたほうが便利です。
AVERAGE関数の式の形は【＝AVERAGE(数値)】ですが、数値を1つずつ入れていくのは手間がかかるため、範囲指定で数値を入れるのが簡単です。エクセルで使われる関数では「～」が「：」の記号になります。そのため、今回AVERAGE関数を使って平均値を出すと、
＝AVERAGE(B3:B11)
となります。
AVERAGE関数は、文字列の入ったセル、論理式が入ったセル、空白のセルは無視されて計算されます。そのため間に数値が入っていないセルがあっても、例えば「欠席」という文字が入ったセルがあっても問題なく平均値が出せます。
平均値を求める関数は他にもAVERAGEA関数というのがあります。
式の構造はAVERAGE関数と同じですが、AVERAGEA関数の場合は文字列の入ったセルは「０」、論理式が入ったセルはTRUEが「１」、FALSEが「０」となり、それぞれが1つの値としてカウントされます。そのため無視してほしいセルまで計算式に入ってしまい、正しい平均値で無くなってしまう可能性が高いです。もしすべてのセルも含めて計算したい場合のみにAVERAGEA関数を使うといいでしょう。
では実際にAVERAGE関数とエクセルが自動的に出す平均値があっているか見てみましょう。
関数での平均値は503,333となっていることが確認できるかと思います。
つぎに、セルで数値セルを範囲指定して平均値を求めると、右下に平均：503,333と表示されていることが確認できます。
このように、関数でもマウスでの範囲指定した平均を求めることが可能になります。
データの数が膨大であり、またそれぞれの代表値を求めたい場合は、関数を用いて求めるようにしましょう。
TRIMMEAN関数
TRIMMEAN関数を用いることで、大幅に離れた値を除いて平均値を求められるようになります。先述した、大小大幅に異なる世帯収入が1世帯あることで、上振れ下振れしてしまうデータもTRIMMEAN関数を使えば防げるのです。
TRIMMEAN関数の式の構造は【＝TRIMMEAN(数値の配列,割合)】です。
数値の配列は平均値を求めたい値がセルのどこからどこまでかということなので、今回は「B3:B11」となります。そして割合とは、上位から何％、下位から何％の値を除外して平均値を求めたいかということになります。例えば、割合を0.1にすると上下合わせて10%の値が除外されるため、そのグループの上位5%と下位5%の値は平均値には影響されません。
今回はまず上下合わせた値が20%で求めるため、割合の部分に【0.2】を入力します。
平均を求めたいB12セルにTRIMMEAN関数を入力し【Enter】を押しました。
すると、平均値と同じ503,333が表示されているのが確認できます。
これではまだ、Iさんの150万の月収が平均値に影響しています。そのため、上下合わせて40%の割合で平均値を出してみましょう。
割合を40%にすることで中央値の390,000と同じ数値が求められました。
中央値ではなく平均値を求めたい、ただし極端に小さい値や大きい値があって困っているときには、このTRIMMEAN関数が便利です。
最頻値の意味や求める方法とは
最頻値とは最も頻繁に現れる数値のことを指し、代表値として使うことの多い値です。
例えば、ある施設への利用回数の状況を判断するとき、行ったことのない人が一番多く、ある人が10回、ある人が4回、ある人が3回利用していた場合、平均値や中央値を求めることも大切ですが「利用していない人が一番多い」という事実を数値で表すのが、ここでの最頻値の役割です。
また年収などの統計では、世代別に最頻値を出すことで、各世代でどのくらいの年収を貰っているのかがわかります。
最頻値のみでは、その統計のすべての特徴を明らかにはできませんが、統計の特徴を割り出す1つの方法になります。
最頻値を求める場合は【MODE関数】を使用します。
| MODE関数 | =MODE(数値1,数値2..数値3……) |
代表値を求めたいデータが膨大な数である場合はMODE関数を、データが少ない場合は中央値(AVERAGE関数など)を使うのが一般的です。
MODE関数は古いバージョンであり、現在は最頻値を求める関数として、MODE.SNGL関数があります。どちらも問題なく使えます。
今回の場合にあわせて式を作ると【＝MODE(B3:B11)】となります。
350,000と390,000、400,000がどちらも2回出現していますが、MODE関数では最初に出てきた数値を優先して最頻値として表示されます。
ちなみに最頻値が複数個出てきた場合に、すべての最頻値を出してくれる関数もあります。
それがMODE.MULT関数です。式の構造は次の通りです。
{＝MODE.MULT(数値)}
数値のところには、今までと同様にどこのセルからどこのセルまでの最頻値を求めるかということを表します。今回の場合はB3からB11です。そしてこの関数を使って値を出すと、最頻値として「350,000」と「390,000」「400,000」が出てくるでしょう。
平均値・中央値・最頻値の使い分け
平均値、中央値、最頻値には、それぞれ得意不得意があります。それぞれのメリットデメリットを解説します。
【平均値】全ての数値を足して、数値の個数で割ったもの
メリット：全ての数値を考慮した値になる
デメリット：極端な数値があった場合に大きい影響を受ける
【中央値】数値を小さい方から並べた時に真ん中にくるもの
メリット：大きすぎる値や小さすぎる値がいくつか含まれたとしても、影響を受けない
デメリット：全体としての値がわかりにくい
【最頻値】個数が多いもの
メリット：極端な値があっても影響を受けづらい
デメリット：最頻値はデータが多い場合でしか使えない。区間をどう分けるかによって値が変わる
中央値が求められないときの対処法
中央値が求められないときに多いエラーを解説します。なぜ求められないのか理由を合わせて覚えておくことで、仕事での突発的なエラーも困難なく対処できるようになりますから、しっかりチェックしておきましょう。
関数の入力間違いがある
エクセルを使い慣れていない方に多く見られるのが関数の入力間違いです。たとえば、関数を入力する際は基本的に【=】を用いることが多いのですが、関数を入力する前に【=】を入力していなければ、関数としての役を果たすことができません。
また、【,】や【.】、【”】も間違えやすいため、エラーが起きた際はまず入力間違いがないかを確認するようにしましょう。
数値を選べていない
マウスでも範囲指定をすることで数値を選ぶことができますが、どこかちがうセルを選択しているためにうまく数値が選ばれてなく、エラーになっている可能性もあります。
範囲指定した場合はデータ内の数値を選択できているか、手入力で【Ctrl】を押しながらクリックで選択した場合は、どこか誤ってマウスをクリックしたことによって、複数選択が解除されていないかを確認してみましょう。
まとめ
エクセルを使い始めると、さまざまな関数があることに気づき、覚えるのも一苦労するかもしれません。しかし、使っていくことで徐々に覚えていき、どの関数がなにを求めるのかを理解することで確実に覚えられるようになります。
覚えることよりも先に、なんの関数はどのような状況の場合に使えるのか、どんなデータに対してマッチするのかをよく理解し、少しずつ覚えていけば問題ありません。
読んでいるだけではエクセルの使い方や使い勝手はあまり理解できませんから、ぜひこの記事を通して、一緒に実践し、幅広い分野で活用できるようにマスターしましょう。
