واریانس در اکسل
در این آموزش خواهیم دید که چگونه می‌توان تجزیه و تحلیل واریانس در اکسل را انجام داد و از چه فرمول‌هایی برای یافتن واریانس نمونه و جمعیت استفاده کرد.
واریانس یکی از مفیدترین ابزارها در نظریه و آمار احتمال است. در علم توضیح می‌دهد که هر عدد در مجموعه داده‌ها چقدر از میانگین دور است. در عمل، غالباً نشان می‌دهد که چقدر چیزی تغییر می‌کند. به عنوان مثال، دمای نزدیک استوا نسبت به سایر مناطق آب و هوایی واریانس کمتری دارد. در این مقاله، روش‌های مختلف محاسبه واریانس در اکسل را تجزیه و تحلیل خواهیم کرد.
واریانس چیست؟
واریانس اندازه‌گیری تغییرپذیری یک مجموعه داده‌است که نشان می‌دهد تا چه حد مقادیر مختلف از هم پخش شده‌اند. از نظر ریاضی به صورت میانگین اختلاف مربعات از میانگین تعریف می‌شود.
برای درک بهتر آن‌چه که شما با واریانس محاسبه می‌کنید، لطفاً این مثال ساده را در نظر بگیرید.
فرض کنید 5 ببر در باغ وحش محلی شما وجود داشته باشد که 14، 10، 8 ، 6 و 2 سال سن دارند.
برای یافتن واریانس، این مراحل ساده را دنبال کنید:
- میانگین (میانگین ساده) پنج عدد را محاسبه کنید:
- از هر عدد، میانگین را برای یافتن اختلاف، کم کنید. برای تجسم این، بگذارید اختلاف‌ها را در نمودار ترسیم کنیم:
- مجذور هر اختلاف را حساب کنید.
- میانگین مربع اختلاف را حساب کنید.
بنابراین ، واریانس 16 است. اما این عدد در واقع به چه معنی است؟
در حقیقت، واریانس ایده‌ی بسیار کلی در مورد پراکندگی مجموعه داده‌ها به شما می‌دهد. مقدار 0 به معنای عدم تغییر است، یعنی تمام اعداد موجود در مجموعه داده‌ها یکسان هستند. هرچه این عدد بزرگ‌تر باشد، داده‌ها بیشتر پخش شده‌اند.
این مثال برای واریانس جمعیت است است (یعنی 5 ببر تمام گروه مورد نظر شما هستند). اگر داده‌های شما انتخابی از جمعیت بزرگ‌تر است، باید واریانس نمونه را با استفاده از فرمول کمی متفاوت محاسبه کنید.
نحوه‌ی محاسبه‌ی واریانس در اکسل
6 تابع داخلی برای انجام واریانس در اکسل وجود دارد: VAR، VAR.S، VARP، VAR.P، VARA و VARPA.
انتخاب فرمول واریانس شما توسط عوامل زیر تعیین می شود:
- نسخه‌ی اکسل مورد استفاده شما.
- بخواهید واریانس نمونه را محاسبه کنید یا جمعیت را.
- بخواهید متن یا مقادیر منطقی را ارزیابی کنید یا نادیده بگیرید.
توابع واریانس اکسل
در جدول زیر یک نمای کلی از توابع واریانس موجود در اکسل ارائه شده است تا در انتخاب فرمول مناسب برای نیازهای شما کمک کند.
| نام | ورژن اکسل | نوع داده | مقادیر متنی و منطقی |
| VAR | 2000 – 2019 | نمونه | نادیده می‌گیرد |
| VAR.S | 2010 – 2019 | نمونه | نادیده می‌گیرد |
| VARA | 2000 – 2019 | نمونه | ارزیابی می‌کند |
| VARP | 2000 – 2019 | جمعیت | نادیده می‌گیرد |
| VAR.P | 2010 – 2019 | جمعیت | نادیده می‌گیرد |
| VARPA | 2000 – 2019 | جمعیت | ارزیابی می‌کند |
VAR.S در برابر VARA و VAR.P در برابر VARPA
VARA و VARPA فقط با رفتاری که با مقادیر منطقی و متنی را در ارجاع‌ها دارند، با دیگر توابع واریانس تفاوت دارند. در جدول زیر خلاصه‌ای از نحوه‌ی ارزیابی اعداد به صورت متنی و مقادیر منطقی ارائه شده‌است.
| نوع آرگومان | VAR, VAR.S, VARP, VAR.P | VARA و VARPA |
| مقادیر منطقی در آرایه‌ها و ارجاع‌ها | نادیده می‌گیرد | ارزیابی می‌کند (TRUE=1, FALSE=0) |
| اعداد به صورت متنی در آرایه‌ها و ارجاع‌ها | نادیده می‌گیرد | به صورت صفر ارزیابی می‌کند |
| مقادیر منطقی و اعداد به صورت متنی که مستقیماً در آرگومان‌ها تایپ می‌شوند | ارزیابی می‌کند (TRUE=1, FALSE=0) |
|
| سلول‌های خالی | نادیده می‌گیرد | |
نحوه‌ی محاسبه‌ی واریانس نمونه در اکسل
نمونه مجموعه‌ای از داده‌های استخراج‌شده از کل جمعیت است. واریانس محاسبه‌شده از یک نمونه، واریانس نمونه نامیده می‌شود.
به عنوان مثال، اگر می‌خواهید بدانید که چقدر قد افراد متغیر است، اندازه‌گیری هر شخص روی زمین برای شما غیرممکن است. راه حل این است که یک نمونه از جمعیت، مثلاً 1000 نفر را بگیرید و ارتفاع کل جمعیت را بر اساس آن نمونه تخمین بزنید.
واریانس نمونه با این فرمول محاسبه می‌شود:
جایی که:
- x̄ میانگین (میانگین ساده) مقادیر نمونه است.
- n اندازه‌ی نمونه است ، یعنی تعداد مقادیر موجود در نمونه.
3 تابع برای یافتن واریانس نمونه در اکسل وجود دارد: VAR، VAR.S و VARA.
تابع VAR در اکسل
این قدیمی‌ترین تابع اکسل است که واریانس را بر اساس نمونه تخمین می‌زند. تابع VAR در کلیه نسخه‌های اکسل 2000 تا 2019 موجود است.
VAR(number1, [number2], …)
توجه: در اکسل 2010، تابع VAR با VAR.S جایگزین شده‌است که باعث بهبود دقت می‌شود. اگرچه VAR هنوز هم برای سازگاری با عقب در دسترس است، اما توصیه می‌شود از نسخه‌ی VAR.S در نسخه‌های فعلی اکسل استفاده کنید.
تابع VAR.S در اکسل
این همتای مدرن تابع VAR است. برای پیدا کردن واریانس نمونه در Excel 2010 و بعد از آن، از تابع VAR.S استفاده کنید.
VAR.S(number1, [number2], …)
تابع VARA در اکسل
تابع VARA یک واریانس نمونه را بر اساس مجموعه‌ای از اعداد، متن و مقادیر منطقی به صورتی که در این جدول نشان داده‌شده، برمی‌گرداند.
VARA(value1, [value2], …)
فرمول واریانس نمونه در اکسل
هنگام کار با یک مجموعه عددی از داده‌ها، می‌توانید از هر یک از توابع فوق برای محاسبه‌ی واریانس نمونه در اکسل استفاده کنید.
به عنوان نمونه، بیایید واریانس یک نمونه متشکل از 6 مورد (B2:B7) را بیابیم. برای این کار می‌توانید یکی از فرمول‌های زیر را استفاده کنید:
=VAR(B2:B7)
=VAR.S(B2:B7)
=VARA(B2:B7)
همان‌طور که در تصویر نشان داده شده‌است، تمام فرمول‌ها نتیجهی مشابهی را به دست می‌آورند (به 2 مکان اعشاری گرد شده‌اند):
برای بررسی نتیجه، بیایید محاسبه‌ی var را به صورت دستی انجام دهیم:
- با استفاده از تابع AVERAGE میانگین را پیدا کنید:
=AVERAGE(B2:B7)میانگین را در یک سلول خالی مثلاً B8 بگذارید. - میانگین را از هر عدد موجود در نمونه کم کنید:
=B2-$B$8این اختلافات را در ستون C قرار می‌دهیم که از C2 شروع می‌شود - هر اختلاف را مربع کنید و نتایج را در ستون D قرار دهید، که از D2 شروع می‌شود:
=C2^2 - اختلافات مربعی را جمع کنید و نتیجه را به تعداد موارد موجود در نمونه منهای 1 تقسیم کنید:
=SUM(D2:D7)/(6-1)
همان‌طور که مشاهده می‌کنید، نتیجهی محاسبه واریانس دستی ما دقیقاً برابر با عدد برگشتی توسط توابع داخلی اکسل است:
اگر مجموعه داده‌های شما حاوی مقادیر منطقی و/یا متنی باشد، تابع VARA نتیجهی متفاوتی را برمی‌گرداند. دلیل این امر این است که VAR و VAR.S هیچ مقداری را به غیر از اعداد در ارجاع‌ها در نظر نمی‌گیرند، در حالی که VARA مقادیر متنی را به صورت صفر، TRUE به صورت 1 و FALSE را به صورت 0 ارزیابی می‌کند؛ بنابراین، لطفاً بسته به این‌که آیا می‌خواهید مقادیر متنی و منطقی را نادیده بگیرید یا پردازش کنید، تابع واریانس را برای محاسبات خود با دقت انتخاب کنید.
نحوه‌ی محاسبه‌ی واریانس جمعیت در اکسل
جمعیت همه‌ی اعضای یک گروه معین هستند، یعنی تمام مشاهدات در زمینهی مطالعه. واریانس جمعیت نحوه‌ی توزیع نقاط داده در کل جمعیت را توصیف می‌کند.
واریانس جمعیت را می‌توان با این فرمول یافت:
جایی که:
- x̄ میانگین جمعیت است.
- n اندازه‌ی جمعیت است، یعنی تعداد کل مقادیر جمعیت.
3 تابع برای محاسبه‌ی واریانس جمعیت در اکسل وجود دارد: VARP، VAR.P و VARPA.
تابع VARP در اکسل
تابع VARP واریانس یک جمعیت را بر اساس کل مجموعه‌ی اعداد برمی‌گرداند. در کلیه‌ی نسخه‌های اکسل 2000 تا 2019 موجود است.
VARP(number1, [number2], …)
توجه: در اکسل 2010، VARP با VAR.P جایگزین شده اما هنوز هم برای سازگاری با عقب نگه داشته می‌شود. استفاده از VAR.P در نسخه‌های فعلی اکسل توصیه می‌شود زیرا هیچ تضمینی برای در دسترس بودن تابع VARP در نسخه‌های بعدی اکسل نیست.
تابع VAR.P در اکسل
این یک نسخه‌ی بهبودیافته از تابع VARP است که در Excel 2010 و بعد از آن موجود است.
VAR.P(number1, [number2], …)
تابع VARPA در اکسل
تابع VARPA واریانس یک جمعیت را بر اساس کل مجموعه‌ی اعداد، مقادیر متنی و منطقی محاسبه می‌کند. در کلیه‌ی نسخه‌های اکسل 2000 تا 2019 وجود دارد.
VARA(value1, [value2], …)
فرمول واریانس جمعیت در اکسل
در مثال محاسبه‌ی var نمونه، ما واریانس 5 نمره‌ی امتحان را پیدا کردیم که فرض کردیم این نمرات از گروه بزرگ‌تر دانش‌آموزان انتخاب شده‌است. اگر داده‌هایی را برای همه‌ی دانش‌آموزان گروه جمع آوری کنید، آن داده‌ها کل جمعیت را نمایندگی می‌کند، و با استفاده از توابع فوق واریانس جمعیت را محاسبه می‌کنید.
در محاسبه‌ی var نمونه، ما واریانس 5 نمره‌ی امتحان را پیدا کردیم که فرض کردیم این نمرات از گروه بزرگ‌تر دانش‌آموزان انتخاب شده‌است. اگر داده‌هایی را برای همه‌ی دانش‌آموزان گروه جمع آوری کنید، آن داده‌ها کل جمعیت را نمایندگی می‌کند، و با استفاده از توابع فوق واریانس جمعیت را محاسبه می‌کنید.
بیایید فرض کنیم که نمرات امتحانات یک گروه 10 نفره‌ی دانش‌آموزان را داریم (B2:B11). نمرات، کل جمعیت را تشکیل می‌دهد، بنابراین با این فرمول‌ها واریانس را خواهیم یافت:
=VARP(B2:B11)
=VAR.P(B2:B11)
=VARPA(B2:B11)
و همه‌ی فرمول‌ها نتیجهی یکسان را به دست می‌آورند:
برای این‌که مطمئن شوید اکسل واریانس را به درستی انجام داده‌است، می‌توانید آن را با فرمول محاسبه‌ی واریانس دستی نشان‌داده‌شده در تصویر زیر بررسی کنید:
اگر برخی از دانش آموزان امتحان را نداده باشند و به جای عدد نمره N/A داشته باشند، تابع VARPA نتیجهی متفاوتی را برمی‌گرداند. دلیل این امر این است که VARPA مقادیر متنی را به صورت صفر ارزیابی می‌کند در حالی که VARP و VAR.P از متن و مقادیر منطقی در ارجاع‌ها چشم پوشی می‌کنند. لطفاً VAR.P در برابر VARPA را برای اطلاعات بیشتر مشاهده کنید.
فرمول واریانس در اکسل – نکات استفاده
برای انجام درست تجزیه و تحلیل واریانس در اکسل، این قوانین ساده را دنبال کنید:
- آرگومان‌ها را به صورت مقادیر، آرایه‌ها یا ارجاع به سلول ارائه دهید.
- در اکسل 2007 و بعد می‌توانید 255 آرگومان متناسب با یک نمونه یا جمعیت ارائه دهید. در اکسل 2003 و پایین‌تر – حداکثر 30 آرگومان.
- برای ارزیابی فقط اعداد در ارجاع‌ها، نادیده گرفتن سلول‌های خالی، متن و مقادیر منطقی، از تابع VAR یا VAR.S برای محاسبه‌ی واریانس نمونه و VARP یا VAR.P برای یافتن واریانس جمعیت استفاده کنید.
- برای ارزیابی مقادیر منطقی و متنی در ارجاع‌ها، از تابع VARA یا VARPA استفاده کنید.
- حداقل دو مقدار عددی را به یک فرمول واریانس نمونه و حداقل یک مقدار عددی به فرمول واریانس جمعیت در اکسل ارائه دهید، در غیر این صورت خطای #DIV/0! رخ می‌دهد.
- آرگومان‌های حاوی متن که به صورت عددقابل تفسیر نیست باعث خطای #VALUE! می‌شود.
واریانس در برابر انحراف معیار در اکسل
بدون شک واریانس یک مفهوم مفید در علم است، اما اطلاعات کاربردی بسیار کمی دارد. به عنوان مثال، ما سن‌های جمعیت ببرها را در یک باغ وحش محلی پیدا کردیم و واریانس را محاسبه کردیم، که برابر است با 16. سؤال این است – چگونه می‌توانیم از این عدد استفاده کنیم؟
می‌توانید از واریانس استفاده کنید تا انحراف معیار را محاسبه کنید، این یک سنجه‌ی بسیار بهتر از میزان تغییر در یک مجموعه داده‌است.
انحراف معیار به صورت ریشه‌ی دوم واریانس محاسبه می‌شود؛ بنابراین، ما ریشه‌ی دوم 16 را می‌گیریم و انحراف معیار 4 را به دست می‌آوریم.
در ترکیب با میانگین، انحراف معیار می‌تواند به شما بگوید که اکثر ببرها چند ساله هستند. به عنوان مثال، اگر میانگین 8 و انحراف معیار 4 باشد، اکثر ببرهای موجود در باغ وحش بین 4 سال (8 – 4) و 12 سال (4 + 8) هستند.
مایکروسافت اکسل توابع ویژه ای برای محاسبه‌ی انحراف معیار یک نمونه و جمعیت دارد. توضیحات مفصل در مورد تمام این توابع را می‌توان در این آموزش یافت: نحوه‌ی محاسبه انحراف معیار در اکسل.
این‌گونه است که واریانس را در اکسل محاسبه می‌کنیم. برای مشاهده‌ی دقیق‌تر فرمول‌های مورد بحث در این آموزش، می‌توانید نمونه ورک‌بوک ما محاسبه‌ی واریانس در اکسل را دانلود کنید. از شما متشکریم که این مطلب را خواندید و امیدوارم دوباره شما را در وب‌سایت‌مان ببینیم!
سلام خیلی ممنون از سایت خوبتون.مختصر و مفید ومیشه گفت اینجا خیلی راحت تر از کلاس های درس واریانس و انحراف معیار رو یاد گرفتم و داخل اکسل به کار میگیرم