関数(Excel)

エクセルの統計関数を使って問題を解決する

スポンサーリンク

こんにちは。システム担当の父親、KATSUです。

エクセル歴20年、アクセス歴10年ですが、ブログは30記事投稿した程度の初心者ですので、引き続き量産に努めていきます。


  • 営業でノルマを達成といけない
  • お客様に満足して頂けるようなサービスを提供しないといけない
  • クレームや不良品を無くさないといけない
  • 課題を自分で作らないといけない

このように職場から様々な課題を与えられて、どのように解決したらよいか分からず悩んでいませんか?

そんな課題や悩みを抱えている方に、是非エクセルを使った統計を知って頂きたいと思います。

今回は、高校数学や品質管理検定3級程度の知識で使える統計関数に的を絞って紹介します。

この記事を読むことで、

  • 統計を使った計算をエクセルで簡単に行えるということを知る
  • 業務の効率アップや課題に対しての成果を出しやすくなる

といった効果を期待できます。

Excel で統計関数を使う目的

統計と聞くと、何だか難しくてとっつきにくいようなイメージがありますよね。

統計は専門的な人のみが扱うもので、一般市民には関係ない分野のように感じられますが、全然そんなことはありません。

日常の業務で何か問題が起こった時に、統計を少し理解していて、分析してみることで問題の傾向や対処方法が見えてきます。

しかも、エクセルの統計関数を使えば一発で計算の結果を導くことが出来るので、時短に間違いなくつながります。

統計の結果を根拠に対策を打てば、無鉄砲に対策を打つより成功する確率も高まります。

つまり、業務上でもプライベートでも、何かの問題を数値で可視化し、状況の改善やトラブル回避のためにエクセルの統計関数は役に立つことが出来ます。

エクセルの統計関数には様々な種類がありますが、高度な統計を知る必要はありません。

高校数学程度の知識で計算できるもので、十分現状を理解して方向性を見出すことができます。

オートSUMボタンで行える統計

エクセルで関数を計算する時に最もよく使われる「オートSUM」ボタンでも、合計・平均・個数・最大値・最小値など、統計に必要な計算を行うことが出来ます。

オートSUM

オートSUMボタンを使い、5つの計算を行うことが出来ました。

オート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バージョンで使える機能を使いこなせるようになることが、会社員・自営業・フリーランス問わず必要になってくることが予想されます。

まとめ

統計関数は、様々なものがあります。

日常の業務を行っていくと、何かしらの課題や問題が発生します。

統計を知っていることは、抱えている問題を正確に捉えることに大きく役立ちます。

更に、エクセルでは、統計の計算をセルの範囲を選択するだけで、一瞬で計算することが出来ます。

統計を習得するのは多少難しいイメージがありますが、自分のスキルとして身に付けられたら、役に立ちますし様々な方から重宝されます。


本日もありがとうございました。

スポンサーリンク

-関数(Excel)