【Excel】分散や標準偏差の求め方/エクセルで関数を利用した処理

Excelを使えば表やグラフが作成できるだけでなく、様々なデータ分析を行うことができます。この分析を行うためにはいくつかの基本的な数式を用いて計算をすることになります。例えば「平均」などは非常にシンプルな分析方法です。計算方法も単純で、その計算後の数値が持つ意味も理解しやすいです。Excelは計算をすることも得意としています。Excel上のセルに直接数式を入力して計算することもできますし、「平均」のようによく使う計算方法についてはExcel関数として初めから用意されているからです。
ここでは「平均」から一歩先に進んで「分散」についてExcelでの求め方や、その意味について説明していきます。

分散とは

まずはデータ分析における「分散」の意味について紹介していきます。一般的に使われる「分散」という言葉とイメージは似ています。多くの数値データについて分散の値では、バラけ具合を示す指標となります。後に説明する「標準偏差」にも関係してきますが、単位がないという特徴があります。つまり身長であれば「m」や「㎝」という単位を使って表しますが、分散の値はただの数値となります。

分散を使った分析の利点は、平均だけでは見えてこない異常値を示すことができるということにあります。平均では数値の全体像が見えているようで実はごまかしの効く数値でもあります。
すべての値が「5」であれば当然平均値も「5」です。しかしただひとつの値だけが「100」であれば、たったひとつのデータによって平均値が大きく引っ張られることになります。分母数が小さいほどひとつのデータの影響力は多きくなってきます。そのような場合に、同時に分散値も求めておけば大きくずれた値が混じっているということも知ることができます。

上の2つのグラフはどちらもあるデータについての平均をExcelにてグラフで表示したものです。グラフ内にある赤い線が平均の値を示しています。上のグラフについてはおよそ中央値を示していますが、下のグラフについては1つのデータに平均値がかなり影響を受けています。

分散の求め方

Excelで分散を求めるには「偏差」と「平均」についての求め方を知っておく必要があります。平均はすべての値を合計し、そのデータ数で割れば答えが得られます。偏差は、それぞれの値から平均値を引いた値になります。つまり各データにおける平均からのズレを示します。
分散は偏差を2乗した値の平均を取れば求められます。
値が小さいほどバラつきが小さいと考えます。

Excel関数を使った分散の求め方

Excelではわざわざ数式を毎回打ち込むことなく分散の計算ができます。しかしExcel関数については覚えておくとスピーディーに分析を行えるでしょう。分散を行えるExcel関数は

VAR.P

です。計算させたいセルを選択し

=VAR.P(A1:A5)

などと打ち込めば瞬時に答えが得られます。またExcel関数の名前を直接入力しんなくても関数を使うことは可能です。この場合「関数の挿入」を選択し、関数の一覧から「統計」を選びましょう。するとその中に「VAR.P」が出てくるのでこれを選択し「OK」をクリックしましょう。

上のグラフは、平均値をグラフで表した時に使ったデータについてExcelにて分散を求めたものです。Bについては分散の値が非常に大きくなっています。平均値だけを見ていれば全体的にAよりも大きなデータ群であるように見えますが、分散と合わせて見ることで怪しいデータが含まれているかもしれないと考えることができます。

Excelで分散を使った応用例

Excelで分散を使った実際の分析例を見てみましょう。例えばある商店を営んでおり、過去の売り上げデータから今後の仕入れに関して、計画を立てたいと考えているとします。ここで現状を知るための方法のひとつとして分散が使えます。
しかし分散だけで傾向を読み取るにはひと月分では不十分です。このことには分散に単位がないことも関係しています。実際に計算をしてみて値が「10」や「50」などになったとします。この数値だけを見ても何の判断もできません。しかしこれを毎月、毎年算出していると何か傾向が読み取れるかもしれません。例えば毎年1月には分散値が大きく、8月には小さいといったように規則性が出てきたりすれば分散を取った価値が出てきます。
しかしこの場合においてもう少し分かりやすい指標を使うには「標準偏差」を利用すると良いでしょう。

MacのExcelで分散を求めるには

エクセルを利用しているユーザーは多いですが、多くの場合はOSにWindowsを使用しています。しかしMacでも利用は可能です。MacとWindowsでは操作方法に若干の違いがありますが、分散に関しては特に心配をする必要はありません。Macのユーザーも同じように計算式をセルに打ち込むか、分散関数を記入すれば問題なく計算が可能です。

標準偏差の求め方

分散の説明をしている段階で何度か「標準偏差」の名前が出てきたように、分散と標準偏差の関連性は大きいです。どちらも似た性質を持っており、計算方法も似ています。エクセル上でも状況に応じて使い分けることになるでしょう。

標準偏差の求め方

標準偏差を求めるにも、途中までは分散と同じ過程を歩みます。というより、分散をさらに計算することで標準偏差が得られます。
標準偏差は、偏差を2乗し平均を取ったのち正の平方根を取った値となります。つまり、分散の正の平方根が標準偏差となります。標準偏差の2乗が分散という逆の表現の方が理解しやすいかもしれません。
計算式を知っただけではどのような値か理解しにくいかもしれませんが、エクセルで実際に使ってみると分散よりもイメージのしやすい値であると気が付きます。

Excel関数を使った標準偏差の求め方

Excel関数の場合、標準偏差は

STDEV.P

となります。標準偏差を求めたいセルを選択し、

=STDEV.P(A1:A5)

のように直接打ち込むか関数の一覧から選択しましょう。こちらもExcel関数によって瞬時に算出可能です。

Excelで標準偏差を使った応用例

分散の応用例で使った商店の売り上げ分析について、標準偏差を取ってみましょう。
分散では単位のないただの数値だったのが「円」という単位で表すことのできる数値になります。標準偏差でも表すのはデータのバラつきなので意味としては分散と似ています。しかしここで算出された数値はそのまま平均からのズレになります。例えばある月の日々の売り上げ平均が10万円だったとします。標準偏差が2万だとすれば、プラスマイナス2万円のバラつきで売り上げを出していることになります。
ここで例えば単位のない「30」や「50」などという数値が出てくるのとは具体性が異なります。ひと月分のデータだけを見たとしてもバラつきを把握することが容易になります。

Macで標準偏差を求める方法

分散で説明した時と同様、Macで使うエクセルであっても標準偏差の求め方に違いはありません。直接計算式を打ち込む、関数名を入力する、関数の一覧から選択するなどの方法によってMacでも簡単に標準偏差を求められます。

分散・標準偏差にもいくつかのExcel関数がある

実は分散や標準偏差には上で紹介した以外のExcel関数を使って求めることもできます。しかしやや使い方が異なるので簡単に説明していきます。

VAR.PとVAR.Sで求める分散

すでに説明した「VAR.P」は、選択したデータ自体の分散を求めます。一方「VAR.S」では選択したデータから母集団の分散を推定することになります。どのように違うのかと言えば、データ分析を行う際、すべてのデータを入手できているとは限らないため、現在あるデータから全体を予測する必要がでてきます。そこで、「VAR.S」の関数を使うことによって推定を行うことが可能になります。一部のデータのみを使い分散を計算した場合、本来の分散値よりも小さな値が算出される傾向にあります。そのため「VAR.S」ではこの過少推定を修正するために計算式がやや加工され、結果として得られる応えは「VAR.P」よりも大きなものとなります。

STDEV.PとSTDEV.Sで求める標準偏差

標準偏差についても同様の考え方のもと2つの関数が用意されています。「STDEV.P」では選択したデータ自体の分散、「STDEV.S」では選択したデータから母集団の分散を推定します。分散の時と同じく関数名の最後が「P」と「S」で分けられているので使っていくうちにすぐに覚えることができるでしょう。
また、STDEV.P関数とSTDEV.S関数の他にも似た意味を持つ関数はありますが、基本的な計算は「STDEV.P」でまかなえるためここでは割愛します。「VAR.P」についても同様です。

Excelで分散・標準偏差と求める方法まとめ

ここまでで分散や標準偏差の意味とその使い方、Macでエクセルを扱っている場合でも同様に求められるということを紹介してきました。
エクセルを仕事やプライベートで日常的に使っている人でもあまり関数を使っていないという人は多いのではないでしょうか。分散や標準偏差を使えば色んなデータの傾向を読み取ることができるようになります。家計簿をエクセルで管理しているなど、データを多く抱えている人なら一度これらの関数を使って分析してみてはいかがでしょうか。

88 件のコメント

  • I and also my guys appeared to be looking through the excellent tactics from your site while immediately developed a horrible feeling I had not thanked the website owner for those secrets. Those boys had been for this reason stimulated to study all of them and now have simply been tapping into these things. Thanks for simply being really thoughtful and also for considering such nice ideas most people are really desirous to understand about. Our own honest regret for not expressing gratitude to earlier.

  • Wow that was odd. I just wrote an extremely long comment but after I clicked submit my comment didn’t show up. Grrrr… well I’m not writing all that over again. Regardless, just wanted to say wonderful blog!

  • Terrific work! This is the type of information that should be shared around the net. Shame on the search engines for not positioning this post higher! Come on over and visit my web site . Thanks =)

  • I’m truly enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a developer to create your theme? Fantastic work!

  • Excellent post. I was checking constantly this weblog and I’m impressed! Very helpful info particularly the final phase 🙂 I handle such info a lot. I was looking for this particular information for a very long time. Thank you and good luck.

  • Excellent read, I just passed this onto a colleague who was doing a little research on that. And he just bought me lunch because I found it for him smile Therefore let me rephrase that: Thanks for lunch!

  • I cherished as much as you’ll obtain carried out proper here. The cartoon is attractive, your authored material stylish. however, you command get bought an nervousness over that you would like be turning in the following. unwell indubitably come more beforehand again as precisely the similar just about very steadily inside of case you shield this increase.

  • I simply had to thank you so much again. I do not know what I would’ve created in the absence of the entire ways shown by you about such a theme. It previously was the hard situation in my opinion, nevertheless finding out the very specialised technique you treated it made me to cry over fulfillment. I will be grateful for the guidance and even believe you know what a powerful job that you’re putting in teaching many others using a web site. More than likely you have never encountered any of us.

  • Wow! This can be one particular of the most useful blogs We have ever arrive across on this subject. Basically Magnificent. I’m also an expert in this topic therefore I can understand your effort.

  • The subsequent time I read a weblog, I hope that it doesnt disappoint me as much as this one. I imply, I know it was my option to learn, however I actually thought youd have something fascinating to say. All I hear is a bunch of whining about one thing that you can repair if you happen to werent too busy looking for attention.

  • This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years. Great stuff, just great!

  • I’ve been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content as you did, the internet will be much more useful than ever before.

  • I appreciated perusing your articles on 3 week diet. This is really an incredible read for me to 3 week diet. Really Your website is very attractive and neat. I am also passionate writer. I have been works at more than five years. Here we specially provide academic 3 week diet solution for our student. You may visit our site. Thanks!

  • Magnificent goods from you, man. I’ve understand your stuff previous to and you are just too great. I actually like what you’ve acquired here, really like what you are saying and the way in which you say it. You make it enjoyable and you still care for to keep it sensible. I can not wait to read far more from you. This is actually a wonderful website.

  • I have been absent for some time, but now I remember why I used to love this blog. Thanks , I will try and check back more frequently. How frequently you update your website?

  • I am so happy to read this. This is the type of manual that needs to be given and not the random misinformation that is at the other blogs. Appreciate your sharing this best doc.

  • Howdy very cool site!! Man .. Excellent .. Wonderful .. I’ll bookmark your site and take the feeds also…I am satisfied to find a lot of helpful info here in the post, we want develop extra techniques in this regard, thanks for sharing. . . . . .

  • I’m impressed, I have to say. Really not often do I encounter a blog that’s each educative and entertaining, and let me tell you, you might have hit the nail on the head. Your thought is excellent; the issue is one thing that not enough people are speaking intelligently about. I’m very completely happy that I stumbled across this in my search for something referring to this.

  • fantastic issues altogether, you just gained a new reader. What would you recommend about your submit that you made a few days in the past? Any positive?

  • Exceptional post however , I was wondering if you could write a litte more on this subject? I’d be very grateful if you could elaborate a little bit further. Thank you!

  • Please let me know if you’re looking for a article author for your weblog. You have some really good posts and I believe I would be a good asset. If you ever want to take some of the load off, I’d absolutely love to write some articles for your blog in exchange for a link back to mine. Please send me an email if interested. Regards!

  • Hi there just wanted to give you a quick heads up. The words in your article seem to be running off the screen in Safari. I’m not sure if this is a format issue or something to do with internet browser compatibility but I thought I’d post to let you know. The layout look great though! Hope you get the issue fixed soon. Many thanks

  • Hello! I just would like to give a huge thumbs up for the nice information you’ve got right here on this post. I can be coming back to your blog for more soon.

  • This is very interesting, You’re a very skilled blogger. I have joined your rss feed and look forward to seeking more of your fantastic post. Also, I’ve shared your website in my social networks!

  • Virtually all of the things you point out is supprisingly precise and that makes me ponder why I hadn’t looked at this in this light before. Your piece truly did switch the light on for me personally as far as this subject matter goes. Nevertheless at this time there is actually one particular point I am not necessarily too comfy with and whilst I try to reconcile that with the core theme of your point, let me observe exactly what the rest of your visitors have to point out.Nicely done.

  • I am curious to find out what blog system you happen to be using? I’m having some minor security problems with my latest site and I’d like to find something more secure. Do you have any solutions?

  • I know this if off topic but I’m looking into starting my own weblog and was wondering what all is required to get setup? I’m assuming having a blog like yours would cost a pretty penny? I’m not very web savvy so I’m not 100 sure. Any tips or advice would be greatly appreciated. Cheers

  • Someone essentially help to make seriously posts I would state. This is the very first time I frequented your website page and thus far? I surprised with the research you made to make this particular publish extraordinary. Fantastic job!

  • Superb site you have here but I was wanting to know if you knew of any message boards that cover the same topics talked about here? I’d really like to be a part of group where I can get advice from other experienced people that share the same interest. If you have any suggestions, please let me know. Many thanks!

  • Terrific paintings! This is the kind of info that are supposed to be shared around the net. Shame on Google for no longer positioning this put up upper! Come on over and visit my site . Thanks =)

  • jnpkyhz,A fascinating discussion is definitely worth comment. I do think that you ought to publish more on this topic, it may not be a taboo ulvnqedz,subject but generally folks don’t talk about such subjects. To the next! All the best!!

  • ctphrpfsqk,Thanks for ones marvelous posting! I actually enjoyed reading it, you will be a great author.I will always bookmark your blog and will zfehcavrh,come back from now on. I want to encourage that you continue your great writing, have a nice afternoon!

  • rmurmozer,This website truly has alll of the information and facts I wanted about this subject and didn?t know who to ask.

  • xlcnaiyq Yeezy Boost 350,A very good informative article. I’ve bookmarked your website and will be checking back in future!

  • surpxw,Thanks a lot for providing us with this recipe of Cranberry Brisket. I’ve been wanting to make this for a long time but I couldn’t find the right recipe. Thanks to your help here, I can now make this dish easily.

  • ratuiyzd,Definitely believe that which you said. Your favourite justification appeared to be on the net the simplest thing to remember of.

  • Prescription For Propecia Tabletas Propecia Generico Cheap Drugs Canada online cialis Cialis Brand Name Pills To Buy How To Buy Levaquin Overseas Aacifemine Clomid Il Parait Que

  • I was very happy to find this web-site.I needed to thanks to your time for this wonderful read!! I positively having fun with every little little bit of it and I have you bookmarked to check out new stuff you blog post.

  • It’s a shame you don’t have a donate button! I’d definitely donate to this outstanding blog! I suppose for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to new updates and will talk about this website with my Facebook group. Talk soon!

  • Yesterday, while I was at work, my cousin stole my iPad and tested to see if it can survive a twenty five foot drop, just so she can be a youtube sensation. My iPad is now broken and she has 83 views. I know this is completely off topic but I had to share it with someone!

  • I do not even know how I ended up here, but I thought this post was great. I do not know who you are but definitely you are going to a famous blogger if you are not already 😉 Cheers!

  • Hey There. I found your blog using msn. This is a really well written article. I will make sure to bookmark it and return to read more of your useful info. Thanks for the post. I will certainly comeback.

  • I’ve been browsing on-line more than three hours these days, but I by no means found any interesting article like yours. It’s lovely value sufficient for me. In my opinion, if all website owners and bloggers made excellent content as you probably did, the web will likely be a lot more helpful than ever before.

  • Unquestionably consider that which you stated. Your favourite justification appeared to be at the internet the simplest thing to consider of. I say to you, I certainly get annoyed while folks consider worries that they plainly do not recognise about. You controlled to hit the nail upon the top as neatly as defined out the entire thing without having side-effects , other people can take a signal. Will likely be back to get more. Thank you

  • You could certainly see your skills in the work you write. The world hopes for more passionate writers like you who aren’t afraid to say how they believe. Always follow your heart.

  • Great info and straight to the point. I don’t know if this is truly the best place to ask but do you people have any thoughts on where to employ some professional writers? Thank you 🙂

  • Hi there would you mind sharing which blog platform you’re working with? I’m looking to start my own blog soon but I’m having a tough time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your layout seems different then most blogs and I’m looking for something unique. P.S Apologies for getting off-topic but I had to ask!

  • Can I just say what a aid to seek out someone who actually knows what theyre speaking about on the internet. You definitely know learn how to convey an issue to gentle and make it important. Extra folks need to learn this and perceive this facet of the story. I cant consider youre not more widespread because you positively have the gift.

  • Good site! I truly love how it is simple on my eyes and the data are well written. I’m wondering how I could be notified when a new post has been made. I’ve subscribed to your RSS feed which must do the trick! Have a great day!

  • I not to mention my pals were actually viewing the nice pointers found on your web site while before long came up with an awful suspicion I never expressed respect to you for those techniques. The ladies are actually as a consequence stimulated to study all of them and already have quite simply been taking advantage of those things. Appreciate your genuinely really kind and also for utilizing variety of nice themes most people are really eager to know about. Our honest regret for not expressing gratitude to you sooner.

  • Hi there! Quick question that’s entirely off topic.
    Do you know how to make your site mobile friendly?
    My web site looks weird when viewing from my iphone4. I’m trying to find a theme or plugin that might be able to resolve this problem.
    If you have any recommendations, please share.
    With thanks!

  • Hi! This is my first comment here so I just wanted to give
    a quick shout out and tell you I truly enjoy reading your posts.
    Can you suggest any other blogs/websites/forums that go over the same subjects?

    Thanks! adreamoftrains best web hosting

  • My spouse and i ended up being absolutely cheerful Edward could conclude his reports out of the ideas he received out of the web pages. It is now and again perplexing just to happen to be making a gift of points which usually people today might have been making money from. We grasp we need you to thank for this. All of the illustrations you made, the easy blog menu, the friendships your site give support to engender – it is everything amazing, and it is leading our son and the family recognize that this concept is thrilling, and that’s extraordinarily pressing. Thanks for all the pieces!

  • Unquestionably believe that which you said. Your favourite justification appeared to be at the web the easiest thing to be aware of. I say to you, I definitely get annoyed at the same time as other folks consider issues that they just don’t realize about. You managed to hit the nail upon the top and outlined out the whole thing with no need side effect , people could take a signal. Will likely be again to get more. Thank you

  • Aw, this was a very nice post. In thought I want to put in writing like this additionally – taking time and actual effort to make a very good article… but what can I say… I procrastinate alot and on no account appear to get one thing done.

  • The other day, while I was at work, my sister stole my apple ipad and tested to see if it can survive a 30 foot drop, just so she can be a youtube sensation. My apple ipad is now broken and she has 83 views. I know this is entirely off topic but I had to share it with someone!

  • Hey there just wanted to give you a quick heads up. The words in your article seem to be running off the screen in Internet explorer. I’m not sure if this is a formatting issue or something to do with web browser compatibility but I figured I’d post to let you know. The design and style look great though! Hope you get the problem solved soon. Thanks

  • Just want to say your article is as amazing. The clarity to your publish is simply spectacular and that i can assume you’re a professional on this subject. Fine together with your permission allow me to snatch your feed to keep updated with coming near near post. Thanks 1,000,000 and please carry on the gratifying work.

  • Hello, I think your website might be having browser compatibility issues. When I look at your website in Chrome, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, wonderful blog!

  • コメントを残す

    メールアドレスが公開されることはありません。