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

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

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

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

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

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

فیلم آموزش محاسبات آماری در اکسل Excel در فرادرس

کلیک کنید

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

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

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

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

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

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

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

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

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

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

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

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

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

فیلم آموزش آنالیز واریانس با اس پی اس اس SPSS در فرادرس

کلیک کنید

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

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

همانطور که دیده می‌شود محاسبه واریانس به کمک توابع 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 برابر با صفر خواهد بود.

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

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

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

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

• مجموعه آموزش‌های نرم‌افزار مایکروسافت اکسل
• آموزش اکسل (Microsoft Office Excel 2016)
• آموزش ترفندهای کاربردی در اکسل
• آموزش‌های مجموعه نرم‌افزارهای آفیس
• آموزش ابزارهای کاربردی اکسل
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• آموزش محاسبات آماری در اکسل

^^