محاسبات آماری با اکسل — راهنمای کاربردی

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

اکسل به عنوان یک ابزار محاسبات جدول که قادر به انجام عملیات سطری و ستونی است، نقش مهمی در تحلیل داده‌ها همینطور استخراج آمار توصیفی در بین کاربران رایانه‌های خانگی دارد. خوشبختانه شرکت مایکروسافت، توابع و ابزارهای محاسباتی زیادی برای استخراج شاخص‌های آماری تهیه کرده است که البته در بیشتر نسخه‌های اکسل موجود هستند. در این نوشتار به محاسبات آماری با اکسل پرداخته‌ایم تا با فراگیری افزونه اختصاصی (Add-ins) اکسل برای انجام این گونه محاسبات، بتوانیم از انجام کارهای تحقیقی لذت بیشتری ببریم. البته توجه داشته باشید که این راهنما در اکسل ۲۰۱۹ اجرا شده ولی با اندکی تلاش می‌توانید برای نسخه‌های قدیمی‌تر اکسل نیز افزونه مورد نظر را پیدا کرده و محاسبات آماری را به کمک آن اجرا کنید.

برای آشنایی و نحوه کار با اکسل به نوشتارهای آموزش مقدماتی اکسل (Excel) — به زبان ساده و فرمول نویسی در اکسل – آموزش مقدماتی را مطالعه کنید. همچنین خواندن شاخص های آمار توصیفی در علم داده --- راهنمای کاربردی و  جامعه آماری — انواع داده و مقیاس‌های آن‌ها نیز خالی از لطف نیست.

محاسبات آماری با اکسل

در اکثر نرم‌افزارهای محاسبات آماری، شاخص‌های مرکزی مانند میانگین و نما و همینطور شاخص‌های پراکندگی مانند واریانس و انحراف معیار محاسبه می‌شوند. چولگی و کشیدگی نیز از معیارهای تقارن برای توزیع داده‌ها محسوب می‌شوند. در این نوشتار به بررسی افزونه‌ای از اکسل می‌پردازیم که قادر به محاسبه این گونه شاخص‌های آماری است. برای نصب این افزونه که به نام Data Analysis معروف است، باید گام‌های زیر را مطابق با تصاویر مربوطه طی کنید. البته توجه داشته باشید که بسیاری از آزمون‌های و تحلیل‌های آماری نیز به کمک این افزونه قابل اجرا هستند.

گام اول: از فهرست File دستور Options را انتخاب کنید. پنجره‌ای به شکل زیر ظاهر خواهد شد. برای دسترسی به افزونه‌ها و نصب آن‌ها در اکسل، کافی است از کادر سمت چپ تصویر ۱، بخش Add-ins را انتخاب کنید. به این ترتیب در سمت راست پنجره، لیستی از افزونه‌های در حال اجرا (Active) یا غیرفعال (Inactive) ظاهر می‌شود.

Excel add ins
تصویر ۱: پنجره تنظیمات پیش‌فرض اکسل و انتخاب گزینه مدیریت افزونه‌های آن

اگر می‌خواهید افزونه‌های غیرفعال را به صورت فعال در آورید، در بخش Manage گزینه Excel Add-ins را انتخاب و دکمه ...Go را بزنید. به این ترتیب وارد گام دوم می‌شوید.

گام دوم: با طی کردن گام اول به پنجره Add-ins خواهید رسید که فهرستی از افزونه‌های فعال و غیر فعال را نشان می‌دهد. با انتخاب هر یک از افزونه‌ها در بخش یا فهرست Add-ins available، می‌توانید هر یک از آن‌ها را فعال یا غیرفعال سازید.

Excel Analysis ToolPack
تصویر ۲: پنجره افزونه‌های استاندارد اکسل

در تصویر ۲، برای فعال سازی افزونه محاسبات آماری در اکسل گزینه اول یعنی Analysis ToolPak را انتخاب کرده‌ایم. با فشردن دکمه OK عملیات بارگذاری این افزونه صورت خواهد گرفت.

با اجرای این گام‌ها، در برگه Data بخش جدیدی به نام Data Analysis ظاهر می‌شود. به این ترتیب با انتخاب این دکمه، پنجره‌ای مطابق با تصویر ۳ نمایان شده که می‌توانید نوع محاسبه و تحلیل آماری را از داخل آن انتخاب نمایید.

data analysis dialogbox
تصویر ۳: پنجره انتخاب تحلیل آماری در افزونه Analysis ToolPak

پس از انتخاب محاسبات دلخواه خود از این لیست، دکمه OK را بزنید. البته جدول ۱، گزینه‌های موجود در این پنجره را معرفی کرده است تا با انواع تحلیل‌های قابل اجرا آشنا شوید.

جدول ۱: لیست تحلیل‌های آماری در افزونه محاسبات آماری با اکسل

ردیفنام تحلیلعملکردتوضیحات
۱Anova: Single Factorتحلیل واریانس: تک عاملتحلیل واریانس یک طرفه
۲Anova: Two-Factor with Replicationتحلیل واریانس: دو طرفهتحلیل واریانس دو طرفه با مشاهدات تکراری در هر سطح
۳Anova: Two-Factor without Replicationتحلیل واریانس: دو طرفهتحلیل واریانس دو طرفه بدون مشاهدات تکراری در هر سطح
۴Correlationضریب همبستگیمحاسبه انواع شاخص‌های همبستگی دو متغیره
۵Covarianceکوواریانستشکیل ماتریس کوواریانس
6Descriptive Statisticsآمار توصیفیمحاسبه شاخص‌های آمار توصیفی
7Exponential Smoothingهموارسازی نماییروش پیش‌بینی سری زمانی با هموارسازی نمایی
8F-Test Two Sample for Variancesآزمون F برای نسبت دو واریانسآزمون آماری برابری واریانس دو جامعه آماری با استفاده از توزیع F
9Fourier Analysisتحلیل فوریهحل سیستم‌های خطی با استفاده از تبدیل سریع فوریه
10Histogramهیستوگرامرسم نمودار فراوانی (ستونی)
11Moving Averageمیانگین متحرکهموارسازی و حذف روند از داده‌های سری زمان
12Random Number Generationتولید اعداد تصادفیانتخاب توزیع آماری و استخراج یک نمونه با تعداد مشخص از آن
13Rank and Percentileرتبه و صدک‌هاتحلیل برمبنای رتبه و محاسبه چندک‌های داده‌ها
14Regressionرگرسیون خطمحاسبه ضرایب و فاصله اطمینان برای مدل رگرسیون خطی
15Samplingنمونه‌گیریانتخاب یک نمونه تصادفی از بین مقادیر انتخاب شده
16t-Test: Paired Two Sample For Meansآزمون t برای مقایسه میانگین زوجیمقایسه تغییرات در اثر یک تیمار روی مشاهدات (آزمون قبل و بعد از اجرای تیمار)
17t-Test: Two-Sample Assuming Equal Variancesآزمون t برای مقایسه میانگین دو جامعه مستقلآزمون t برای دو جامعه مستقل با واریانس‌های برابر
18t-Test: Two-Sample Assuming Unequal Variancesآزمون t برای مقایسه میانگین دو جامعه مستقلآزمون t برای دو جامعه مستقل با واریانس‌های نابرابر
19Z-test: Two sample for the Meansآزمون Z برای مقایسه میانگینآزمون Z برای مقایسه میانگین دو جامعه مستقل با توزیع نرمال (معلوم بودن واریانس دو جامعه)

به یاد داشته باشید که در این نوشتار به معرفی گزینه مربوط به محاسبات آمار توصیفی (Descriptive Statistics) پرداخته خواهد شد.

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

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

پنجره‌ جدیدی باز خواهد شد که ناحیه مربوط به مقادیر و همچنین شاخص‌های مورد نظر را از کاربر دریافت می‌کند. برای روشن‌تر شدن موضوع از یک کاربرگ اکسل که نمرات ۱۵ دانشجو در آن ثبت شده کمک می‌گیریم. این اطلاعات را در تصویر ۴ مشاهده می‌کنید.

student grades
تصویر ۴: نمرات دانشجویان در یک درس

با توجه به ناحیه مربوط به اطلاعات این کاربرگ، پارامترهای آمار توصیفی را مطابق با تصویر ۵، تنظیم کرده‌ایم.

descriptive statistics window in excel
تصویر ۵: تنظیمات مربوط به استخراج آمار توصیفی برای کاربرگ نمرات

در قسمت Input Range، ناحیه‌ای از کاربرگ که باید شاخص‌های آمار توصیفی از آن استخراج شود، تعیین شده است. برای مثال ما این قسمت شامل ناحیه $A$1:$A$16 است. از آنجایی که گزینه Columns انتخاب شده است، متغیرها به صورت ستونی از مجموعه داده گرفته می‌شوند. از آنجایی که کاربرگ فقط شامل یک ستون است در نتیجه یک متغیر بیشتر برای تحلیل به کار نخواهد رفت.

نکته: اگر گزینه Rows را انتخاب می‌کردید، به تعداد سطرها، متغیر ایجاد می‌شد.

با فعال‌سازی گزینه Label in first row، اکسل متوجه می‌شود که سطر اول در ستون A، شامل نام متغیر است و نباید از آن در محاسبات استفاده کند. البته این سلول مقدار متنی دارد و در محاسبات نقشی نخواهد داشت.

بخش بعدی مربوط به تعیین محل خروجی محاسبات است. اگر گزینه Output Range را انتخاب کنید، باید ناحیه‌ای از کاربرگ را برای نمایش اطلاعات خروجی مشخص نمایید. با گزینه New Worksheet Ply یک کاربرگ جدید به منظور قرارگیری خروجی تعیین می‌شود. با گزینه New Workbook هم، کارپوشه‌ای جدید (یک فایل جدید اکسلی) برای نمایش اطلاعات جدول‌های آمار توصیفی در نظر گرفته خواهد شد.

اگر گزینه Summary statistics و Confidence Level for Mean را انتخاب کرده باشید، شاخص‌های معرفی شده در جدول ۲، محاسبه و در خروجی ظاهر خواهند شد.

نکته: انتخاب دو گزینه Kth Largest و Kth smallest باعث نمایش kامین مقدار بزرگتر و کوچکتر خواهد بود. مقدار K در کادر جلوی هر یک از این گزینه‌ها، وارد می‌شود.

جدول ۲: شاخص‌های توصیفی در خروجی محاسبات آماری در اکسل

ردیفشاخصعملکردتوضیحات
۱Meanمحاسبه میانگینشاخص مرکزی
۲Standard Errorخطای استانداردانحراف استاندارد نمونه تقسیم بر جذر حجم نمونه
3Medianمیانهمقدار مرکزی داده‌ها
4Modeنمامقدار با بیشترین تکرار
5Standard Deviationانحراف استاندارد نمونه‌ایجذر واریانس نمونه‌ای
6Sample Varianceواریانس نمونه‌ایمیانگین مجموع مربعات انحراف از میانگین
7Kurtosisکشیدگیمعیار تقارن عمودی نسبت به توزیع نرمال
8Skewnessچولگیمعیار تقارن افقی نسبت به توزیع نرمال
9Rangeدامنه تغییراتفاصله بین حداقل و حداقل مقادیر
10Minimumکوچکترین مقدار
11Maximumبزرگترین مقدار
12Sumمجموع مقادیر
13Countتعداد مقادیر
14Confidence Level(95.0%)فاصله اطمینان برای میانگین نمونه‌ایبا سطح اطمینان ۹۵٪ (با توجه به انتخاب کاربر)

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

descriptive statistics in excel output
تصویر ۶: خروجی محاسبات آماری در اکسل

همانطور که مشخص است میانگین نمرات برابر با ۱۴٫۸ و انحراف استاندارد نیز تقریبا برابر با ۴ واحد است. در این صورت خطای برآورد میانگین حداکثر ۱٫۰۶ خواهد بود که نشان دهنده حداکثر یک واحد خطا برای میانگین نمرات کل دانشجویان در درس مورد نظر، توسط این نمونه تصادفی است.

همینطور چولگی و کشیدگی نیز به صفر نزدیک بوده که نشانگر تقارن و مشابهت توزیع این داده‌ها با توزیع نرمال (Normal Distribution) است.

خلاصه و جمع‌بندی

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

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

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

سلام روز بخیر من پایان نامه ام با الگوداده ستانده هست فرمولامو تو اکسل با الگو بلد نیستم ایا کسی هست اموزش بده

آقا خدا از بزرگی کمت نکنه دمت گرم
توی پروژم مونده بودم چطور جدول آمار توصیفی بیارم
مرسی

با سلام. من در حال انجام کار آماری پایان نامه هستم. دیتای من به صورت پنل هست (دیتای شرکتهای بورسی در طول 10 سال) و برای برخی متغیرها باید انحراف استاندارد برای هر شرکت در طول سالهای مطالعه را محاسبه کنم یعنی برای هر سال- شرکت یک عدد بدست بیارم. میخواستم ببینم در اکسل چطور میتونم این کار رو انجام بدم؟

نظر شما چیست؟

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