محاسبه واریانس در اکسل — به زبان ساده

۹۵۲۲ بازدید
آخرین به‌روزرسانی: ۳ خرداد ۱۴۰۲
زمان مطالعه: ۵ دقیقه
محاسبه واریانس در اکسل — به زبان ساده

در آمار توصیفی، محاسبه شاخص‌های تمرکز و پراکندگی از اهمیت زیادی برخوردار است. واریانس به عنوان یکی از معیارهای پراکندگی در بسیاری از گزارشات آماری محاسبه شده، برای مقایسه پراکندگی بین دو یا چند گروه به کار می‌رود. ولی برعکس مفهوم ساده‌ای که واریانس دارد، محاسبه آن کمی پیچیده و بخصوص برای حجم زیاد از اطلاعات، زمان‌بر است. بنابراین استفاده از نرم‌افزارهای محاسباتی مانند «اکسل مایکروسافت» (MS-Excel) کمک شایانی در کاهش زمان محاسبه این شاخص دارد. به همین علت در اکسل، تابع محاسبه واریانس با شیوه‌های مختلف ایجاد شده است.

997696

برای آگاهی از مفهوم واریانس بهتر است مطلب واریانس و اندازه‌های پراکندگی — به زبان ساده را بخوانید سپس به کمک این نوشتار محاسبه واریانس در اکسل را بیاموزید.

محاسبه واریانس در اکسل

در اکسل، حدود 400 تابع وجود دارد که محاسبات را برایمان ساده‌تر می‌کنند. از این حدود تابع، شش تابع منحصرا، واریانس را محاسبه می‌کند. تفاوت این شش تابع در دو نکته زیر نهفته است:

  • بعضی از این توابع واریانس نمونه‌ای را محاسبه می‌کنند و بعضی واریانس جامعه آماری را بدست می‌آورند.
  • نحوه محاسبه واریانس برای داده‌‌هایی که بعضی از آن‌ها عددی و بعضی متنی یا عبارت‌های منطقی هستند در اکسل متفاوت است. در نتیجه برای در نظر گرفتن مقدارهای متنی یا منطقی یا صرف نظر کردن از این مقدارها، باید از توابع مختلفی در اکسل کمک گرفت.

با توجه به اینکه شیوه محاسبه واریانس جامعه و نمونه با دو فرمول متفاوت صورت می‌گیرد، در زیر نحوه محاسبه هر یک نشان داده شده است:

واریانس جامعه:i=1n(xix)2n\frac{\sum_{i=1}^n(x_i-\overline{x})^2}{n}

 واریانس نمونه: i=1n(xix)2n1\frac{\sum_{i=1}^n(x_i-\overline{x})^2}{n-1}

نکته: باید توجه داشت در این دو فرمول منظور از n تعداد سلول‌هایی است که به عنوان پارامتر به تابع داده شده و شامل مقدارهای عددی، متنی و منطقی است. پس هنگام محاسبه «واریانس جامعه» (Population Variance) صورت کسر بر تعداد کل‌ مقدارها تقسیم می‌شود، در حالیکه در «واریانس نمونه‌ای» (Sample Variance) یکی از تعداد مقدارها کاسته شده، سپس عمل تقسیم صورت می‌گیرد.

درج تابع واریانس در یک سلول

برای محاسبه واریانس نمونه‌ای با استفاده از تابع VAR که قابل استفاده در همه نسخه‌های اکسل است، مراحل زیر را طی می‌کنیم. فرض کنید در یک کاربرگ داده‌های مربوط به نمرات ده دانشجو قرار گرفته است. پس «محدوده» (Range) مقدارهای نمرات شامل سلول‌های B2 تا B11 است. قرار است مقدار محاسبه شده برای واریانس در سلول B14 قرار بگیرد. پس این سلول را انتخاب می‌کنیم و از «نوار فرمول» (Formula Bar) دکمه fx\large \it f_x را می‌زنیم.

insert function dialog box

در داخل پنجره ظاهر شده که «درج تابع» (Insert Function) نامیده می‌شود، فهرست کشویی Or select a category را باز کنید. این فهرست شامل اسامی گروه‌های مختلف توابع اکسل است. گزینه Compatibility را انتخاب کنید. به این ترتیب در کادر Select a function، لیستی از توابعی اکسل ظاهر می‌شود که به منظور سازگاری با نسخه‌های قدیمی ایجاد شده‌اند. از این لیست VAR را انتخاب کنید و دکمخ Ok را بزنید تا پنجره پارامترهای این تابع (Function Arguments) ظاهر شود.

var parameters in excel

این پنجره در ابتدا دو کادر به عنوان پارامترهای تابع به نام‌های Number1 و Number2 را نشان می‌دهد. با توجه به اسم و پارامترها و عبارت number که در سمت راست کادر مربوط به پارامتر نوشته شده، مشخص است که باید مقدارهای عددی برای آن‌ها وارد یا انتخاب شود. این امکان وجود دارد که تا ۲۵۵ ناحیه مختلف را به عنوان پارامترهای تابع VAR به کار ببرید. کافی است که در پارامتر دوم کلیک کنید تا پارامترهای سوم و ... اضافه شوند.

ناحیه B2 تا B11 را به عنوان پارامتر این تابع در کادر Number1 انتخاب کنید. مقدارهای مربوط به این ناحیه در سمت راست کادر پارامتر دیده می‌شود. مشخص است که مقدارهای غیر عددی مانند مقادیر متنی و منطقی نیز در این میان وجود دارند. در توضیحاتی که در مورد نحوه محاسبه تابع VAR در وسط این پنجره دیده می‌شود، گفته شده، هنگام محاسبه واریانس، مقدارهای متنی و منطقی نادیده گرفته می‌شوند. با توجه به این موضوع، در پایین کادر پارامترها در کنار علامت = حاصل محاسبات این تابع قرار گرفته که برابر با 9.86666667 است. البته این مقدار را در انتهای پنجره در قسمت Formula result نیز می‌توان مشاهده کرد.

اگر می‌خواهید اکسل راهنمای استفاده از این تابع را ظاهر کند، کافی است که روی لینک Help on this Function کلیک کنید.با فشردن دکمه OK (یا کلید Enter از صفحه کلید) محاسبه انجام شده و در سلول مورد نظر قرار می‌گیرد ولی با زدن دکمه Cancel اعلام می‌کنید که از ثبت تابع در سلول صرفنظر کرده‌اید.

result of var function

به نظر می‌رسد که تابع VAR بدون در نظر گرفتن مقدارهای متنی و یا منطقی محاسبات مربوط به واریانس نمونه‌ای را انجام داده است و تعداد دانشجویان را برابر با 6 و در نتیجه در مخرج کسر فرمول مربوط به محاسبه واریانس مقدار ۵=۱-۶ به کار رفته.

توابع مختلف محاسبه واریانس در اکسل

البته در اکسل نسخه 2010 به بعد شیوه محاسبه واریانس به روز شده و با اسمی متفاوت از اسامی قبلی به کار می‌رود. جدول زیر این توابع را معرفی کرده و خصوصیات هر یک را بیان می‌کند. به این ترتیب بنا به نیازتان می‌توانید تابع مناسب را برای محاسبه واریانس در این نرم‌افزار همه کاره و کاربرپسند انتخاب کنید.

باید توجه داشت که برای استفاده از هر تابعی که در لیست زیر قرار دارد، مراحل انتخاب تابع و درج پارامترهای آن درست به مانند قبل خواهد بود به جز اینکه برای توابعی که در نسخه‌های ۲۰۰۷ به بعد معرفی شده‌اند باید از فهرست کشویی Or select a category گروه توابع آماری Statistical را انتخاب کرده و در کادر پایینی، تابع مورد نظر خود را  پیدا کنید.

ردیفنام تابعنسخه اکسلمحاسبه واریانس نمونه‌ای/ جامعهمقدارهای متنی و منطقی
۱VAR.Sنسخه 2010 به بعدنمونه‌ایبدون در نظر گرفتن
2VARتا اکسل۲۰۰۷ (برای حفظ سازگای در نسخه‌های جدید وجود دارد)نمونه‌ایبدون در نظر گرفتن
۳VARAتا اکسل۲۰۰7 (برای حفظ سازگای در نسخه‌های جدید وجود دارد)نمونه‌ایبا در نظر گرفتن
۴VAR.Pاکسل۲۰۱۰ به بعدجامعهبدون در نظر گرفتن
۵VARPتا اکسل۲۰۰۷جامعهبدون در نظر گرفتن
۶VARPAاز اکسل۲۰۰۳ به بعدجامعهبا در نظر گرفتن

در تصویر زیر به کمک یک کاربرگ شیوه‌های مختلف محاسبه واریانس در اکسل بررسی شده است.

VARIANCE FUNCTIONS IN EXCEL

همانطور که دیده می‌شود محاسبه واریانس به کمک توابع VAR.S و VAR برابر بوده و همینطور VAR.P نیز با VARP یکسان است. وجود دو تابع برای چنین حالتی حفظ سازگاری با نسخه‌های قبلی است. اگر خودتان محاسبات را دنبال کنید، خواهید دید که در تابع VARA و VARPA مقدارهای منطقی TRUE و FALSE به ترتیب برابر با ۱ و صفر در نظر گرفته شده‌اند.

تفاوت توابع VAR.S با VARA و VAR.P با VARPA

تفاوت توابع VAR.S با VARA و همچنین VAR.P با VARPA در نحوه به کارگیری سلول‌هایی است که دارای مقدارهای منطقی و متنی هستند و باید در محاسبه واریانس به کار روند. هنگام محاسبه واریانس به کمک دو تابع VARA و VARPA، اگر سلول‌هایی شامل مقدارهای متنی باشند، در محاسبه واریانس مقدارشان صفر در نظر گرفته شده و واریانس طبق فرمول مربوطه، حاصل می‌شود. همچنین هنگام محاسبه واریانس، مقدار منطقی TRUE برابر با ۱ و FALSE برابر با صفر خواهد بود.

جدول زیر به بررسی وضعیت مقدارهای متنی و منطقی در هنگام محاسبه واریانس پرداخته است.

محتوای سلولVAR.S, VAR, VAR.P  و VARPVARA و VARPA
مقدارهای منطقینادیده گرفته می‌شوند.در محاسبات تعداد چنین سلول‌های لحاظ می‌شود و در جمع TRUE  = 1 , FALSE=0 در نظر گرفته می‌شود.
مقدارهای متنینادیده گرفته می‌شوند.در محاسبات شمارش شده ولی در جمع صفر در نظر گرفته می‌شوند.
سلول خالینادیده گرفته می‌شوند.نادیده گرفته می‌شوند.
مقدارهای منطقی و یا متنی که بیانگر عدد هستند و مستقیما در پارامترهای تابع درج شده‌اند.چنین مقدارهایی، شمارش شده و در جمع TRUE  = 1 , FALSE=0 در نظر گرفته می‌شوند.چنین مقدارهایی، شمارش شده و در جمع TRUE = 1 , FALSE=0 در نظر گرفته می‌شوند.
متنی که بیانگر عدد نیست و در پارامترهای تابع به کار رفته است.خطای $$#VALUE!$$خطای $$#VALUE!$$

برای روشن شدن مطلبی که در دو سطر آخر جدول بالا نوشته شده به تصویر زیر دقت کنید.

PARAMETERS WINDOW OF VARIANCE FUNCTION

در سطر دوم پنجره پارامترهای تابع VARP، مقدار متنی "1" مستقیما نوشته شده ولی رفتار تابع VARP با این مقدار، به صورت عددی است. همچنین مقدار TRUE و FALSE نیز مستقیما در پارامترهای این تابع نوشته شده‌اند. به مانند قبل، مقدارهایی که تابع برای هر یک در نظر می‌گیرد به ترتیب برابر با ۱ و ۰ است. در پارامتر آخر، یک مقدار متنی نیز مستقیما در پارامتر تابع قرار دارد. این کار باعث می‌شود که در سلول، با پیغام خطای $$#VALUE!$$

اگر مطلب بالا برایتان مفید بوده است، آموزش‌های زیر نیز به شما پیشنهاد می‌شوند:

^^

بر اساس رای ۱۴ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
۲ دیدگاه برای «محاسبه واریانس در اکسل — به زبان ساده»

در فرمول واریانس در ابتدای مقاله به توان دو رو حاظ نکردید

با سلام و تبریک سال نو خدمت همراه مجله فرادرس
از اینکه فرمول واریانس کامل نوشته نشده بود، عذرخواهی می کنم. فرمول مربوطه اصلاح و بروزرسانی گردید.
با تشکر فراوان و آرزوی شادی و تندرستی و پیروزی برای شما

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *