職場から様々な課題を与えられて、どのように解決したらよいか分からず悩んでいませんか？
- 営業でノルマを達成といけない
- お客様に満足して頂けるようなサービスを提供しないといけない
- クレームや不良品を無くさないといけない
- 課題を自分で作らないといけない
そんな課題や悩みを抱えている方に、是非エクセルを使った統計を知って頂きたいと思います。
もくじ
この記事を読むことで、
- 統計を使った計算をエクセルで簡単に行えるということを知る
- 業務の効率アップや課題に対しての成果を出しやすくなる
といった効果を期待できます。
Excel で統計関数を使う目的
統計と聞くと、何だか難しくてとっつきにくいようなイメージがありますよね。
統計は専門的な人のみが扱うもので、一般市民には関係ない分野のように感じられますが、全然そんなことはありません。
日常の業務で何か問題が起こった時に、統計を少し理解していて、分析してみることで問題の傾向や対処方法が見えてきます。
しかも、エクセルの統計関数を使えば一発で計算の結果を導くことが出来るので、時短に間違いなくつながります。
統計の結果を根拠に対策を打てば、無鉄砲に対策を打つより成功する確率も高まります。
つまり、業務上でもプライベートでも、何かの問題を数値で可視化し、状況の改善やトラブル回避のためにエクセルの統計関数は役に立つことが出来ます。
エクセルの統計関数には様々な種類がありますが、高度な統計を知る必要はありません。
高校数学程度の知識で計算できるもので、十分現状を理解して方向性を見出すことができます。
オートSUMボタンで行える統計
エクセルで関数を計算する時に最もよく使われる「オートSUM」ボタンでも、合計・平均・個数・最大値・最小値など、統計に必要な計算を行うことが出来ます。
オートSUMボタンを使い、５つの計算を行うことが出来ました。
オートSUMボタンで計算できる関数は以下のとおりで、引数は対象の範囲を選択します。
| 計算方法 | 関数名 |
|---|---|
| 合計 | SUM |
| 平均 | AVERAGE |
| 個数 | COUNT |
| 最大値 | MAX |
| 最小値 | MIN |
その他の合計・個数・平均値を求める関数については、過去に記事を書いているので、そちらを参考にして頂けたら良いかと思います。
平均値と似て非なるメジアンやモード
統計の計算値に、メジアン(中央値)と、モード(最頻値)というのがあります。
メジアンはデータを並び替えた時の中央の値、モードは最も件数が多い値です。
どちらも平均値と似ていますが、データの分布によっては結果が大きく異なる場合があります。
例として、11個のデータを小さい順に並べてみます。
平均値は「42.27」、メジアンは「40」、モードは「60」となります。
メジアンは中央である6番目の値を示します。
モードは60が3件となっており一番件数が多くなっています。
メジアンとモードを計算する関数は以下のとおりで、引数の設定はオートSUMボタンで計算できる関数と同じ様に、対象の範囲を選択します。
| 計算方法 | 関数名 |
|---|---|
| メジアン(中央値) | MEDIAN |
| モード(最頻値) | MODE |
データの分布が正規分布の場合は、平均値・メジアン・モード共に近い値を示しますが、偏りがあると3つの値の差が大きくなります。
男性100万人の身長分布などのように「自然界で発生する現象」は、正規分布を描きます。
一方、「年収の人口分布」などは、正規分布に当てはまらないものとしてよく挙げられます。
バラツキを見る分散、標準偏差
統計値として様々場面で使われるのが標準偏差です。
集団の中のばらつきが大きいと標準偏差が大きくなります。
母集団が安定しているかどうかの判断に使われます。
標準偏差が大きくなれば、何か集団に問題があると判断されることもあります。
例えば、生まれたばかりの赤ちゃん100人分の身長の標準偏差は小さいが、大人100人の標準偏差は大きくなります。
赤ちゃんは身長のバラツキは少なく、成長して大人になるとバラツキが大きくなるというのが、イメージしやすいかと思います。
標準偏差と分散の計算方法は以下の通りです。
| 計算方法 | 関数 | 数式 |
|---|---|---|
| 標準偏差(母集団) | STDEVP | |
| 標準偏差(標本) | STDEV | |
| 分散(母集団) | VARP | |
| 分散(標本) | VAR |
分散は、標準偏差を2乗した数値です。
また、標準偏差と分散は2種類(母集団・標本)の計算方法が存在しますが、サンプル数が少ない時は、標本で計算するとよいでしょう。
注意すべきことは、グループ間を比較する時に、標本と母集団とを混同してしまうと分母が変わるので、正しい評価ができなくなります。
手計算で標準偏差を計算する場合はかなり時間がかかりますが、Excelを使えば範囲を指定するだけで一瞬で結果を出すことができます。
2つの項目の関係を見る相関係数
- 気温とアイスクリームの売上の関係を調べたい
- 一定期間運動時間と体脂肪率の増減を調べたい
こんな時によく使われるのが、散布図と相関係数です。
散布図は視覚的に、相関係数は数値で2項目の相関を表せます。
相関係数はExcelのCORREL関数を使って簡単に算出することが出来ます。
| 計算方法 | 関数 | 数式 |
|---|---|---|
| 相関係数 | CORREL(配列1,配列2) |
CORREL関数の引数は、比較したい2項目の範囲を選択します。
ここで、店舗ごとに「ビールと紙おむつの販売数」を比較してみましょう。
まずは表を作成し、データを入力します。
ここで、2項目の並びとデータの数が正しく入力されていることを確認しましょう。
オートSUMでの計算やメジアン、モード、標準偏差については、データの並びを無視しても問題ありません。
相関係数は、ソートで片方のみを並び替えると間違った結果になります。データも崩れますので、並び替えるのは避けましょう。
CORREL関数を数式バーに入力し、引数に相関を確認したい範囲を選択します。
相関係数が算出されました。「0.855(85.5%)」となりました。
ここでは、ビールと紙おむつの販売数について強い相関関係にあることが分かります。
関係を散布図で表現してみました。
相関係数の範囲は「-1(-100%)から1(100%)」ですが、0に近づくほど関係が薄くなります。
数値と評価の方法は以下の表の通りです。
| 範囲 | 評価 | グラフ |
|---|---|---|
| 0.7～1.0 | 強い正の相関がある | |
| 0.4～0.7 | やや正の相関がある | |
| 0.2～0.4 | 弱い正の相関がある | |
| 0.2～-0.2 | 相関が無い | |
| -0.2～-0.4 | 弱い正の相関がある | |
| -0.4～-0.7 | やや正の相関がある | |
| -0.7～-1.0 | 強い負の相関がある |
エクセルのバージョンで使える関数が異なる
ほとんどのソフトウェアはバージョンアップすると、機能が増えたり、使い勝手が良くなったりします。
Excel もバージョンアップすると、機能や関数が増えます。統計関数については、条件を設ける「IFS」の付いた関数が増えます。
例えば、COUNTIFSやSUMIFSが当てはまります。最新のExcelについては、最大値や最小値を条件で絞ることが出来るMAXIFSやMINIFSを使うことが出来ます。
永続版のエクセル(2013,2016,2019など)の購入もできますが、Microsoft365サブスクリプションを購入すると、永続版に無い関数も使えます。
コスパは永続版の方がいいですが、ExcelなどのOfficeの機能を便利に使おうと思えば、Microsoft365はコスト以上のパフォーマンスを発揮できます。
特にこれからテレワークなどで個人のITスキルが求められる中、Microsoft365バージョンで使える機能を使いこなせるようになることが、会社員・自営業・フリーランス問わず必要になってくることが予想されます。
まとめ
統計関数は、様々なものがあります。
日常の業務を行っていくと、何かしらの課題や問題が発生します。
統計を知っていることは、抱えている問題を正確に捉えることに大きく役立ちます。
更に、エクセルでは、統計の計算をセルの範囲を選択するだけで、一瞬で計算することが出来ます。
統計を習得するのは多少難しいイメージがありますが、自分のスキルとして身に付けられたら、役に立ちますし様々な方から重宝されます。
本日もありがとうございました。