واریانس یکی از ابزارهای بسیار مفید در نظریه احتمالات و آمار است. در علم، واریانس توصیف می‌کند که هر عدد در مجموعه داده‌ها چقدر از میانگین فاصله دارد. در عمل، واریانس اغلب نشان می‌دهد که چیزی چقدر تغییر می‌کند. به‌عنوان‌مثال، دما در نزدیکی خط استوا نسبت به سایر مناطق آب‌وهوایی واریانس کمتری دارد. محاسبه واریانس در اکسل همانند تبدیل عدد به حروف در اکسل به‌راحتی امکان‌پذیر است که در این مقاله روش‌های مختلف آن را تحلیل خواهیم کرد.
در این مطلب می‌خوانید
تابع واریانس در اکسل چیست و چه کاربردی دارد؟
واریانس معیاری برای اندازه‌گیری پراکندگی یک مجموعه داده است که نشان می‌دهد چقدر مقادیر مختلف از میانگین فاصله دارند. در ریاضی، واریانس به‌عنوان میانگین مجذور اختلاف‌ها از میانگین تعریف می‌شود.
برای درک بهتر محاسبه واریانس به مثال ساده زیر توجه کنید:
فرض کنید ۵ ببر در باغ‌وحش محلی شما وجود دارند که سن آن‌ها به‌ترتیب ۱۴، ۱۰، ۸، ۶ و ۲ سال است.
برای یافتن واریانس، مراحل زیر را دنبال کنید:
- میانگین (میانگین ساده) این پنج عدد را محاسبه کنید.
- از هر عدد، میانگین را کم کنید تا اختلاف‌ها به‌دست آید. برای بصری‌کردن این امر، اختلاف‌ها در چارت زیر نشان داده شده است:
- هر اختلاف را مجذور کنید.
- میانگین مجذور اختلاف‌ها را محاسبه کنید.
بنابراین، واریانس برابر با ۱۶ است؛ اما این عدد واقعاً چه معنایی دارد؟
در واقع، واریانس تنها یک ایده کلی از پراکندگی داده‌ها به شما می‌دهد. مقدار صفر به معنای نبودن پراکندگی است، یعنی همه اعداد در مجموعه داده یکسان هستند. هرچه عدد بزرگ‌تر باشد، داده‌ها بیشتر پراکنده‌اند.
این مثال برای واریانس جمعیت (یعنی ۵ ببر به‌عنوان کل گروهی که به آن علاقه دارید) است. اگر داده‌های شما گزیده‌ای از جمعیت بزرگ‌تری باشد، باید واریانس نمونه را با استفاده از فرمول کمی متفاوت محاسبه کنید.
پیش از ادامه مطلب پیشنهاد می شود که از مطلب آموزش راس گیری چک در اکسل نیز دیدن فرمائید.
نحوه محاسبه واریانس در اکسل
تابع محاسبه واریانس در اکسل شامل ۶ تابع مختلف است: VAR، VAR.S، VARP، VAR.P، VARA و VARPA.
انتخاب فرمول واریانس به عوامل زیر بستگی دارد:
- نسخه اکسل مورد استفاده شما؛
- آیا شما واریانس نمونه یا واریانس جمعیت را محاسبه می‌کنید؛
- آیا می‌خواهید مقادیر متنی و منطقی را ارزیابی یا نادیده بگیرید.
جدول زیر مروری بر توابع واریانس موجود در اکسل را ارائه می‌دهد تا به شما در انتخاب فرمول مناسب برای نیازهایتان کمک کند.
| نام | نسخه اکسل | نوع داده | متون و منطقی‌ها |
| VAR | ۲۰۰۰-۲۰۱۹ | نمونه | نادیده گرفته‌شده |
| VAR.S | ۲۰۱۰-۲۰۱۹ | نمونه | نادیده گرفته‌شده |
| VARA | ۲۰۰۰-۲۰۱۹ | نمونه | ارزیابی‌شده |
| VARP | ۲۰۰۰-۲۰۱۹ | جمعیت | نادیده گرفته‌شده |
| VAR.P | ۲۰۱۰-۲۰۱۹ | جمعیت | نادیده گرفته‌شده |
| VARPA | ۲۰۰۰-۲۰۱۹ | جمعیت | ارزیابی‌شده |
محاسبه واریانس نمونه در اکسل
یک نمونه مجموعه‌ای از داده‌ها است که از کل جمعیت استخراج شده‌اند و واریانس محاسبه‌شده از یک نمونه، واریانس نمونه نامیده می‌شود.
برای مثال، اگر می‌خواهید بدانید قد افراد چگونه متغیر است، از نظر فنی غیرممکن است که قد هر فرد روی زمین را اندازه‌گیری کنید. راه‌حل این است که یک نمونه از جمعیت بگیرید، مثلاً ۱۰۰۰ نفر و براساس آن نمونه، قد کل جمعیت را تخمین بزنید.
واریانس نمونه با این فرمول محاسبه می‌شود:
که در آن:
- x̄ میانگین (میانگین ساده) مقادیر نمونه است؛
- n اندازه نمونه است؛ یعنی تعداد مقادیر در نمونه؛
برای یافتن واریانس نمونه در اکسل، ۳ تابع وجود دارد: VAR, VAR.S و VARA.
توابع اکسل همانطور که در وبسایت ۲۴ وب لرن آورده شده است،
چیزی جز فرمول‌های ازپیش‌تعریف‌شده که محاسبات موردنیاز را در پس‌زمینه انجام می‌دهند، نیستند. پس فرمول محاسبه واریانس در اکسل هم در واقع همین توابع از پیش تعریف‌شده هستند.
تابع VAR در اکسل
این تابع، قدیمی‌ترین تابع اکسل برای تخمین واریانس براساس یک نمونه است. تابع VAR در تمام نسخه‌های اکسل از ۲۰۰۰ تا ۲۰۱۹ موجود است.
VAR(number1, [number2], …)
طبق آنچه که در وبسایت رسمی مایکروسافت منتشر شده است،
تابع VAR در اکسل ۲۰۱۰ با VAR.S جایگزین شد که دقت بهبودیافته‌ای را فراهم می‌کند. هر چند VAR همچنان برای سازگاری با نسخه‌های قدیمی‌تر موجود است، توصیه می‌شود در نسخه‌های جدید اکسل از VAR.S استفاده کنید.
تابع VAR.S در اکسل
این تابع، نسخه مدرن تابع VAR اکسل است. از تابع VAR.S برای یافتن واریانس نمونه در اکسل ۲۰۱۰ و بعد از آن استفاده کنید.
VAR.S(number1, [number2], …)
تابع VARA در اکسل
تابع VARA اکسل واریانس نمونه را براساس مجموعه‌ای از اعداد، متن و مقادیر منطقی برمی‌گرداند.
VARA(value1, [value2], …)
فرمول واریانس نمونه در اکسل
هنگام کار با مجموعه‌ای عددی از داده‌ها می‌توانید از هر یک از توابع بالا برای محاسبه واریانس در اکسل استفاده کنید.
به‌عنوان‌مثال بیایید واریانس یک نمونه متشکل از ۶ آیتم (B2:B7) را پیدا کنیم. برای این کار می‌توانید از یکی از فرمول‌های زیر استفاده کنید:
- =VAR(B2:B7)
- =VAR.S(B2:B7)
- =VARA(B2:B7)
همانطور که در شکل زیر نشان داده شده است، همه فرمول‌ها همان نتیجه را برمی‌گردانند (گردشده به ۲ رقم اعشار):
برای بررسی نتیجه، بیایید محاسبه واریانس در اکسل را به‌صورت دستی انجام دهیم:
- میانگین را با استفاده از تابع AVERAGE محاسبه کنید:
=AVERAGE(B2:B7)
- میانگین را از هر عدد در نمونه کم کنید:
=B2-$B$8
- هر تفاوت را به توان دو برسانید و نتایج را به ستون D، شروع از D2 بگذارید:
=C2^2
- جمع تفاوت‌های مربعی را اضافه و نتیجه را بر تعداد آیتم‌ها در نمونه منهای ۱ تقسیم کنید:
=SUM(D2:D7)/(6-1)
همانطور که می‌بینید، نتیجه محاسبه دستی همان عددی است که توسط توابع داخلی اکسل برگردانده شده است:
اگر مجموعه داده‌های شما شامل مقادیر بولین و/یا متنی باشد، تابع VARA نتیجه متفاوتی برمی‌گرداند. دلیل آن این است که VAR و VAR.S هر گونه مقادیر به جز اعداد را در مراجع نادیده می‌گیرند، در حالی که VARA مقادیر متنی را به‌عنوان صفر، TRUE را به‌عنوان ۱ و FALSE را به‌عنوان ۰ ارزیابی می‌کند. پس بسته به اینکه آیا می‌خواهید متون و منطقی‌ها را پردازش کنید یا نادیده بگیرید، تابع واریانس را برای محاسبات خود بادقت انتخاب کنید.
محاسبه واریانس جمعیت در اکسل
جمعیت شامل تمام اعضای یک گروه داده‌شده، یعنی تمام مشاهدات در حوزه مطالعه است. واریانس جمعیت توصیف می‌کند که چگونه نقاط داده در کل جمعیت پراکنده هستند.
واریانس جمعیت را می‌توان با این فرمول یافت:
که در آن:
- x̄ میانگین جمعیت است؛
- n اندازه جمعیت است؛ یعنی تعداد کل مقادیر در جمعیت.
برای محاسبه واریانس جمعیت در اکسل، ۳ تابع وجود دارد: VARP، VAR.P و VARPA. این توابع به همراه فرمول‌های واریانس نمونه، فرمول محاسبه واریانس در اکسل هستند.
تابع VARP در اکسل
تابع VARP اکسل واریانس یک جمعیت را براساس مجموعه کامل اعداد برمی‌گرداند. این تابع در تمام نسخه‌های اکسل از ۲۰۰۰ تا ۲۰۱۹ موجود است.
VARP(number1, [number2], …)
در اکسل ۲۰۱۰، VARP با VAR.P جایگزین شد؛ اما همچنان برای سازگاری با نسخه‌های قدیمی‌تر نگه داشته شده است. توصیه می‌شود در نسخه‌های جدید اکسل از VAR.P استفاده کنید؛ زیرا تضمینی وجود ندارد که تابع VARP در نسخه‌های آینده اکسل موجود باشد.
تابع VAR.P در اکسل
این نسخه بهبودیافته تابع VARP است که در اکسل ۲۰۱۰ و بعد از آن موجود است.
VAR.P(number1, [number2], …)
تابع VARPA در اکسل
تابع VARPA واریانس یک جمعیت را براساس مجموعه کامل اعداد، متن و مقادیر منطقی محاسبه می‌کند. این تابع در تمام نسخه‌های اکسل از ۲۰۰۰ تا ۲۰۱۹ موجود است.
VARPA(value1, [value2], …)
فرمول واریانس جمعیت در اکسل
در مثال محاسبه واریانس نمونه، واریانس ۵ نمره امتحان را با فرض اینکه این نمرات انتخابی از یک گروه بزرگ‌تر از دانش‌آموزان بودند، پیدا کردیم. اگر داده‌ها را در مورد تمام دانش‌آموزان در گروه جمع‌آوری کنید، این داده‌ها کل جمعیت را نمایندگی می‌کنند و شما واریانس جمعیت را با استفاده از توابع بالا محاسبه خواهید کرد.
فرض کنید نمرات امتحان یک گروه از ۱۰ دانش‌آموز (B2:B11) را داریم. نمرات، کل جمعیت را تشکیل می‌دهند؛ پس واریانس را با این فرمول‌ها محاسبه خواهیم کرد:
- =VARP(B2:B11)
- =VAR.P(B2:B11)
- =VARPA(B2:B11)
و همه فرمول‌ها نتیجه یکسانی را برمی‌گردانند
برای اطمینان از صحت محاسبه واریانس در اکسل می‌توانید آن را با فرمول محاسبه دستی که در تصویر زیر نشان داده شده است، بررسی کنید:
اگر برخی از دانش‌آموزان امتحان نداده و به جای نمره، N/A دارند، تابع VARPA نتیجه متفاوتی برمی‌گرداند. دلیل این است که VARPA مقادیر متنی را به‌عنوان صفر ارزیابی می‌کند؛ درحالی‌که VARP و VAR.P مقادیر متنی و منطقی را در مراجع نادیده می‌گیرند.
پیش از ادامه مطلب پیشنهاد می شود که به مقاله آموزش درصد در اکسل نیز مراجعه فرمائید.
محاسبه واریانس و انحراف معیار در اکسل
واریانس بدون شک مفهومی مفید در علم است؛ اما اطلاعات عملی کمی دارد. برای مثال، ما سن ببرهای موجود در یک باغ‌وحش محلی را یافتیم و واریانس آن‌ها را که برابر با ۱۶ است، محاسبه کردیم. سوال این است که چگونه می‌توانیم واقعاً از این عدد استفاده کنیم؟
می‌توانید از واریانس برای محاسبه انحراف معیار استفاده کنید که معیار بهتری برای میزان تغییر در یک مجموعه داده است.
انحراف معیار به‌عنوان جذر واریانس محاسبه می‌شود. بنابراین، جذر ۱۶ را می‌گیریم و انحراف معیار ۴ را به‌دست می‌آوریم.
ترکیب انحراف معیار با میانگین می‌تواند به شما بگوید که اکثر ببرها چه سنی دارند. برای مثال، اگر میانگین ۸ و انحراف معیار ۴ باشد، اکثر ببرهای موجود در باغ‌وحش بین ۴ سال (۸ – ۴) و ۱۲ سال (۸ + ۴) هستند.
چگونه می‌توانید در کوتاه‌ترین زمان به یک متخصص اکسل تبدیل شوید؟
آیا می‌خواهید در محل کار خود با ارائه گزارش‌ها و تحلیل‌های حرفه‌ای، نظر همه را به خود جلب کنید و پله‌های موفقیت را هرچه سریع‌تر طی کنید؟ دوره آموزش اکسل مقدماتی تا پیشرفته ۲۴ وب لرن، همان چیزی است که شما به دنبال آن هستید!
این دوره آموزشی:
- از پایه‌های اکسل شروع می‌کند و تا تکنیک‌های پیشرفته مانند ماکروها و VBA پیش می‌رود؛
- تمرین‌ها و پروژه‌های کاربردی دارد تا مهارت‌های خود را در دنیای واقعی به کار ببندید؛
- دسترسی به تمام ویدیوها، تمرین‌ها و منابع دوره را به‌صورت نامحدود در اختیار شما قرار می‌دهد تا هر زمان که خواستید، مرور کنید.
در ضمن، تیم پشتیبانی ۲۴ وب لرن همواره آماده پاسخگویی به سوالات شماست. پس همین حالا از صفحه محصول آموزش جامع اکسل ۲۴ وب لرن دیدن کنید و با تبدیل‌شدن به یک متخصص اکسل موقعیت‌های شغلی بی‌نظیری را برای خود به وجود بیاورید.
سوالات متداول
- چرا اکسل چند تابع برای محاسبه واریانس دارد؟
اکسل چند تابع برای محاسبه واریانس دارد تا نیازهای مختلف کاربران را از جمله تفاوت بین واریانس نمونه و جمعیت و همچنین نحوه برخورد با داده‌های متنی و منطقی برآورده کند.
- چگونه می‌توانم بفهمم که باید از کدام تابع واریانس در اکسل استفاده کنم؟
انتخاب تابع واریانس بستگی به نوع داده‌های شما (نمونه یا جمعیت) و اینکه آیا نیاز دارید مقادیر متنی و منطقی را در محاسبات خود لحاظ کنید یا خیر، دارد.
- تفاوت بین توابع S و VARA در اکسل چیست؟
تفاوت اصلی بین توابع VAR.S و VARA در اکسل این است که چگونه با مقادیر غیر عددی برخورد می‌کنند. تابع VAR.S فقط مقادیر عددی را در محاسبات خود لحاظ می‌کند و مقادیر متنی و منطقی را نادیده می‌گیرد. در مقابل، تابع VARA مقادیر منطقی را به اعداد تبدیل می‌کند (TRUE به‌عنوان ۱ و FALSE به‌عنوان ۰) و مقادیر متنی را به‌عنوان صفر ارزیابی می‌کند. اگر می‌خواهید مقادیر غیر عددی را در محاسبات خود لحاظ کنید، از VARA استفاده کنید. در غیر این صورت، VAR.S گزینه مناسبی است.
سخن پایانی
محاسبه واریانس در اکسل یکی از ابزارهای ضروری برای تحلیل داده‌ها است که به شما امکان می‌دهد تا از میزان پراکندگی داده‌ها و روندهای موجود در آن‌ها آگاهی پیدا کنید. با استفاده از توابع مختلف اکسل می‌توانید به‌راحتی واریانس نمونه یا جمعیت را محاسبه کنید و به تحلیل دقیق‌تری از اطلاعات خود برسید.
سلام مشکلی که من برای محاسبه واریانس در اکسل دارم اینه که زمانی که تصمیم میگیرم محاسبه انجام بده و انجام میگیره خانه های اکسل به شکل #### در میاد میشکل از کجا میتونه باشه. ممنون میشم راهنماییم کنید.
درود فراوان بر شما. دوست عزیز این یک ارور به دلیل کوچک بودن عرض و یا ارتفاع سلول شما هست و ربطی به فرمول استفاده شده نداره. با بزرگ تر کردن اندازه سلول مشکل شما برطرف خواهد شد.