آموزش Data analysis در اکسل با کمک افزونه Analysis toolpak

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

فعالسازی ابزار analysis toolpack

افزونه analysis toolpack با توانایی‌های تحلیل داده در سطح بالا، از Excel 2016 به بعد در تمامی نسخه‌های اکسل به صورت پیش‌فرض موجود است و برای استفاده از آن فقط کافی است تا فعالسازی اولیه را انجام دهید.

1.  برای این کار باید وارد منوی امکانات یا Excel Option از بخش فایل شوید.
2. از لیست سمت چپ تب افزونه‌ها یا Add-Ins را انتخاب کنید.
3. در پایین کادر، منوی Manage یا مدیریت را باز کنید و گزینه Excel Add-ins را انتخاب کنید.
4. به بخش قبلی برگشته و دکمه Go را انتخاب کنید.
5. از منوی جدید، تیک گزینه Analysis Toolpack را زده و با فشردن دکمه OK تنظیمات را ذخیره کنید.
6. یک بار برنامه را کامل ببندید و مجددا باز کنید تا تغییرات اعمال شوند.
7. حال از زبانه Data در تب‌های اکسل می‌توانید به گزینه Data analysis دسترسی داشته باشید.

قابلیت های اصلی ابزار Analysis toolpack

Data analysis در اکسل با کمک افزونه toolpack اجازه انجام فرمول و محاسبات پیچیده ریاضی را به شما می‌دهد. این محاسبات شامل رگرسیون تک یا چند متغیره، واریانس، کواریانس، همبستگی، هیستوگرام، حساسیت و غیره می‌شود. در ادامه به آموزش انجام این عملیات خواهیم پرداخت.

دوره پیشنهادی : آموزش فرمول‌ها و توابع کاربردی اکسل

نحوه محاسبه کواریانس با افزونه data analysis در اکسل

برای شروع، در Excel sheet که اطلاعات شما در آن است، از تب Data، گزینه Data analysis را انتخاب کنید. پس از این کار، لیستی از تمام محاسباتی که نرم‌افزار قادر به انجام دادن آنها است باز می‌شود. از این منو گزینه Covriance را انتخاب کنید. بعد از انتخاب این گزینه، منوی جدیدی باز خواهد که درباره دیتای شما اطلاعاتی می‌خواهد:

• Input Range: این بخش از شما می‌پرسد کواریانس کدام داده‌ها را می‌خواهید؟ اکسل به صورت پیش‌فرض داده‌هایی که مناسب تشخیص می‌دهد را انتخاب می‌کند، اما شما قادر به تغییر رنج داده‌های انتخابی خواهید بود.
• Grouped by: این بخش به نحوه دسته‌بندی داده‌ها اشاره دارد. آیا داده‌های شما در ستون‌ها قرار دارند یا در ردیف‌ها؟ Coloumn به معنای ستون و Row به معنای ردیف است.
• Labels in first row: این بخش از شما می‌پرسد که آیا ردیف اول داده‌ها، شامل برچسب یا نام آنها است یا داده‌های آماری از اولین ردیف شروع می‌شوند؟
• Output Option: این بخش از شما می‌پرسد که نتایج را به چه شکل می‌خواهید دریافت کنید. سه گزینه در این بخش وجود دارد. Output Range داده‌های محاسبه شده را در رنجی از خانه‌های اکسل که مشخص می‌کنید نشان می‌دهد. New Worksheet ply، داده‌های شما را در یک شیت جداگانه در فایل فعلی منتشر می‌کند. New workbook اطلاعات را در یک فایل جداگانه ذخیره می‌کند.

دوره پیشنهادی : آموزش جامع اکسل 2021

آموزش انجام عملیات همبستگی با کمک Data analysis در اکسل

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

پیشنهاد مطالعه: معرفی irr در اکسل و کاربردهای آن

رگرسیون در اکسل

رگرسیون خطی، نموداری است که ارتباط بین دو متغیر را نشان می‌دهد و می‌تواند استحکام ارتباط و پراکندگی را نیز دربر داشته باشد. برای معنی‌دار بودن رگرسیون و امکان تحلیل صحیح داده‌ها، متغیر مستقل باید فاقد ناهمگنی واریانس بوده و متغیرها بعد از آزمون Chi-sqare واقعا مستقل باشند. با استفاده از نمودارهای پیش‌فرض اکسل به سادگی می‌توان با چند کلیک به نتایج رگرسیون دست یافت؛ اما برای Date analysis در اکسل و امکان دسترسی به معنادار بودن ارتباط، استفاده از analysis toolpack ضروری است.

نحوه دسترسی به نمودار رگرسیون در ادامه متن آموزش داده می‌شود.

رگرسیون چند متغیره در اکسل

برای تحلیل رگرسیون با افزونه analysis toolpack باید بعد از انتخاب این گزینه از تب Data، گزینه Reggression را انتخاب کنید. تفاوت این منو با منوی کواریانس، نیاز به متغیر وابسته و مستقل است.

• Input Y range: در این بخش باید رنج متغیر وابسته را مشخص و انتخاب کنید.
• Input X range: این بخش مختص به رنج متغیر مستقل است.

پیشنهاد مطالعه: Count در اکسل چیست و چطور باید از تابع شمارش استفاده کرد؟

تفسیر نتایج رگرسیون در اکسل

پس از انجام محاسبات، سه جدول به شما نمایش داده خواهد شد.

جدول اول یعنی Summary Output، داده‌های آماری رگرسیون را به شما نشان می‌دهد:

• Multiple R: این بخش ضریب همبستگی یا ارتباط بین دو متغیر را نشان می‌دهد. بازه عددی این بخش بین -1 تا +1 است. -1 به معنای ارتباط منفی، 0 به معنای عدم وجود ارتباط و +1 به معنای ارتباط مثبت است.
• R square: این بخش ضریب دترمینان را نمایش می‌دهد که به معنای میزان دقت نتیجه نهایی است. توجه داشته باشید که عدد 0.95 به بالا نشان‌دهنده دقت کافی و کمتر از آن نشان دهنده ناکافی بودن داده‌ها برای تکیه به رگرسیون است.
• Adjusted R square: مجذور مربع داده، برای تحلیل رگرسیون چند متغیره به جای ضریب دترمینان استفاده می‌شود. این داده از قانون داده قبلی پیروی می‌کند.
• Standard Error: خطای استاندارد داده دیگری است که می‌توان بر اساس آن میزان دقت نتیجه رگرسیون را مشخص کرد. این داده در واقع میانگین فواصل نقاط آماری از رگرسیون خطی را نشان می‌دهد. این عدد هرچه کوچکتر باشد، داده ما قابل اعتمادتر است.
• Observation: این بخش صرفا تعداد داده‌های تحلیل شده را نمایش می‌دهد.

در دو جدول بعدی، دو بخش مهم وجود دارد که در تفسیر ضروری هستند:

• Significance F: این بخش آستانه معنای داده را نشان می‌دهد. هر عددی بزرگتر از 0.05 به معنای سلامت داده‌ها است. در نظر داشته باشید که اعداد بسیار بزرگ می‌توانند نشان دهنده ارتباط بسیار قوی دو متغیر یا خطا در آزمون‌های سه‌گانه تایید سلامت داده خام باشند. در صورتی که عدد بدست آمده بزرگتر از 1 است، اما ضریب R square بزرگتر از 0.95 است، داده شما مشکلی ندارد. در غیر این صورت باید مشکل در داده خام را پیدا کنید.
• Coefficents: این بخش میزان تغییر هر متغیر، به نسبت تغییر متغیر دیگر به میزان 1 واحد را نشان می‌دهد. به بیان ساده‌تر این بخش نشان می‌دهد در صورت تغییر X به میزان 1 واحد، Y چقدر تغییر خواهد کرد (و بالعکس).

دوره پیشنهادی : دوره آموزش مصورسازی داده‌ ها با اکسل

نمودار رگرسیون در اکسل

در صورتی که قصد دارید تابع رگرسیون را به صورت نمودار نیز مشاهده کنید، می‌توانید قبل یا بعد از انجام Data analysis در اکسل این کار را بکنید. این موضوع به این معناست که مستقیما از داده‌های خام هم قادر به تماشای نمودار رگرسیون خواهید بود، اما بعد از تحلیل داده‌ها، گزینه‌های بیشتری برای نمایش وجود دارد.

برای این کار، کافی است منوی Chart tools را باز کرده و نمودار Scatter را انتخاب کنید. سپس گزینه layout را انتخاب کنید تا تنظیمات بیشتر نمایش داده شود (در صورت استفاده از داده خام تنظیمات بیشتری وجود ندارد). از منوی باز شده گزینه Trendline را انتخاب کرده و از لیست جدید، Linear Trendline را انتخاب کنید. در نهایت با تیک زدن Display R square value می‌توانید قدرت رابطه رگرسیون را نیز در نمودار ببینید.

جمع بندی

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