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

توضیحات

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

تجزیه و تحلیل داده ها در اکسل

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

نحوه تجزیه و تحلیل داده ها در اکسل

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

ترسیم نمودار با اکسل

یکی از تکنیک های عالی برای ارائه یک روایت با گرافیک، نمودار است. نمودارها داده ها را خلاصه می کنند تا مجموعه داده ها راحت تر قابل درک و تجزیه و تحلیل باشند. اکسل به دلیل توانایی خود در سازماندهی و محاسبه اعداد مشهور است. نمودار یک تصویر گرافیکی از هر مجموعه ای از حقایق است. نمودار تصویری از داده ها است که از نمادهایی مانند میله ها در نمودار میله ای یا خطوط در نمودار خطی برای نشان دادن داده ها استفاده می کند. اکسل انواع مختلفی از نمودارها را ارائه می دهد که می توانید از بین آن ها انتخاب کنید، یا می توانید از گزینه نمودارهای پیشنهادی اکسل برای بررسی نمودارهای متناسب با داده های شما و انتخاب یکی از آن ها استفاده کنید. نمودارهای اکسل برای کمک به تجزیه و تحلیل داده ها با تمرکز بر یک یا چند جزء گزارش عالی هستند. می‌توانیم از نمودارهای اکسل برای فیلتر کردن «noise» غیرضروری که در آن زمان می‌خواهیم منتقل کنیم، استفاده کنیم . با رفتن به تب Insert و انتخاب گروه دستور Charts، می توانید به سرعت نمودارهای پای، خط، ستون یا میله ایجاد کنید.

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

برای این کار کافی است:

1. مرحله 1: یک محدوده داده را انتخاب کنید.
2. مرحله 2: Insert > (نوع نمودار مورد نظر را از نمادها انتخاب کنید) را انتخاب کنید.
3. مرحله 3: در صورت نیاز، نمودار درج شده را اصلاح کنید.

قالب بندی شرطی (Conditional Formatting) با اکسل

قالب بندی شرطی می تواند به برجسته کردن الگوها و روندها در داده های شما کمک کند. قوانینی ایجاد کنید که قالب سلول ها را بر اساس مقادیر آنها برای استفاده از آن تعریف می کند. قالب بندی شرطی ممکن است در محدوده ای از سلول ها (یا یک محدوده انتخابی یا یک محدوده نامگذاری شده)، یک جدول اکسل و حتی یک گزارش PivotTable در Excel برای ویندوز اعمال شود. مراحل ذکر شده در زیر را برای انجام قالب بندی شرطی دنبال کنید. روی گزینه Conditional Formatting در تب Home کلیک کنید. یکی از موارد زیر را انجام دهید:

• اگر می خواهید مقادیر را در سلول های جداگانه تغییر دهید، این کار را انجام دهید. Highlight Cells Rules یا Top/Bottom Rules را انتخاب کنید و سپس گزینه ای را انتخاب کنید که با نیازهای شما مطابقت دارد.
• اگر می‌خواهید تاریخ‌های بعد از این هفته، اعداد بین 50 تا 100 یا پایین‌ترین 10 درصد امتیازات را برجسته کنید، قوانین سلول‌های برجسته را انتخاب کنید.
• مقیاس رنگی که شدت رنگ سلول را نشان می‌دهد مربوط به قرارگیری مقدار در بالا یا پایین محدوده است، بر رابطه بین مقادیر در یک محدوده سلول تأکید می‌کند. توزیع فروش بین مناطق یک مثال است. به Color Scales اشاره کنید و سپس روی مقیاس مورد نظر کلیک کنید.
• برای تأکید بر رابطه مقادیر در یک محدوده سلولی، به نوارهای داده اشاره کنید و سپس روی پر مورد نظر کلیک کنید. این یک نوار رنگی در سراسر سلول ایجاد می کند. مقایسه قیمت یا جمعیت در شهرهای بزرگ دو نمونه است.
• برای برجسته کردن یک محدوده سلولی حاوی سه تا پنج مجموعه از مقادیر، که هر کدام آستانه خاص خود را دارند، به مجموعه نمادها اشاره کنید و سپس روی یک مجموعه کلیک کنید. به عنوان مثال، ممکن است از مجموعه ای از سه نماد برای تأکید بر سلول هایی با فروش کمتر از 80000، 60000 و 40000 دلار استفاده کنید. از طرف دیگر، می توانید یک سیستم رتبه بندی 5 امتیازی را به خودروها اختصاص دهید و از مجموعه ای از پنج نماد استفاده کنید.

روش های تجزیه و تحلیل داده ها در اکسل

روش های متداول را به همراه فرمول های آن ها برای شما قرار می دهم:

ادغام و چسباندن متن با عملگر الحاق اکسل

=CONCATENATE یکی از ساده ترین و در عین حال قدرتمندترین فرمول ها برای تجزیه و تحلیل داده ها است. متن، اعداد، تاریخ‌ها و سایر داده‌های سلول‌های متعدد را می‌توان در یکی ترکیب کرد. فرمول:

=CONCATENATE (سلول هایی را که می خواهید ادغام کنید انتخاب کنید)

آموزش تابع LEN در اکسل

=LEN تعداد کاراکترهای یک سلول معین را به سرعت برمی گرداند. همانطور که در مثال بالا مشاهده می شود، فرمول =LEN ممکن است برای تعیین تعداد کاراکترها در یک سلول استفاده شود تا دو نوع واحد انبارداری محصول (SKUs) را متمایز کند. LEN هنگام تلاش برای تمایز بین شناسه‌های منحصر به فرد متمایز (UID) که گاهی طولانی هستند و توالی درستی ندارند، بسیار مهم است. فرمول:

=LEN(انتخاب سلول)

تابع Trim در اکسل

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

=TRIM (تکه از متن)

تابع COUNTA در اکسل

=COUNTA خالی بودن یا نبودن یک سلول را تعیین می کند. هر روز به عنوان یک تحلیلگر داده، با مجموعه داده های ناقص مواجه خواهید شد. COUNTA به شما این امکان را می دهد که شکاف موجود در مجموعه داده را بدون نیاز به بازسازی آن بررسی کنید. فرمول:

=COUNTA (انتخاب سلول)

تابع AVERAGEIFS در اکسل

AVERAGEIFS مانند SUMIFS به شما امکان می دهد بر اساس یک یا چند پارامتر میانگین بگیرید. فرمول:

=AVERAGEIF (انتخاب سلول، معیارها، محدوده میانگین)

پیدا کردن/جستجو

=FIND/=SEARCH روش های موثری برای مکان یابی متن خاص در یک منبع داده است. هر دو در اینجا ذکر شده‌اند زیرا =FIND یک تطابق حساس به حروف کوچک و بزرگ را برمی‌گرداند، یعنی اگر شما برای “Big” پرس و جو کنید، فقط نتایج Big=true را دریافت خواهید کرد. با این حال، جستجوی “بزرگ” با Big یا Big مطابقت دارد و پرس و جو را گسترش می دهد. هنگامی که به دنبال ناهنجاری ها یا شناسه های منحصر به فرد می گردید، این بسیار مفید است.  فرمول:

=FIND(TEXT,WITHIN TEXT,[START NUMBER]) و یا =SEARCH(TEXT,WITHIN TEXT,[START NUMBER])

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

در این بخش برخی از روش های تحلیل آماری را می آموزید:

• هنگام مرتب‌سازی داده‌ها در یک صفحه‌گسترده، می‌توانید داده‌ها را دوباره مرتب کنید تا به سرعت مقادیر را کشف کنید. مرتب سازی محدوده یا جدولی از داده ها بر روی یک یا چند ستون داده امکان پذیر است. به عنوان مثال می توانید پرسنل را ابتدا بر اساس بخش و سپس بر اساس نام خانوادگی رتبه بندی کنید.
• فیلتر کنید: می توانید از تابع FILTER برای فیلتر کردن مجموعه ای از داده ها بسته به معیارهایی که ارائه می کنید استفاده کنید. لطفاً به خاطر داشته باشید که این ویژگی در حال حاضر فقط برای کاربران Microsoft 365 در دسترس است.
• قالب بندی شرطی: قالب بندی شرطی در اکسل به شما این امکان را می دهد که سلول ها را با رنگ خاصی بر اساس مقدار سلول برجسته کنید.
• نمودار: یک گرافیک ساده اکسل ممکن است اطلاعات بیشتری را نسبت به یک صفحه آمار منتقل کند. همانطور که می بینید، ساخت نمودار بسیار ساده است.

مجموعه داده در اکسل

مجموعه داده مجموعه ای از سلول های پیوسته در یک کاربرگ اکسل است که حاوی داده هایی برای تجزیه و تحلیل است. برای ایجاد عملکرد Analyse-it با داده های خود، هنگام ساختاردهی داده ها در یک کاربرگ اکسل، باید چند دستورالعمل ساده را دنبال کنید:

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

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

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

مرتب سازی در اکسل

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

• اولین قدم این است که روی هر سلولی در ستونی که می خواهید مرتب کنید کلیک کنید.
• در مرحله بعد، برای مرتب سازی به ترتیب صعودی، روی AZ که در تب Data در گروه Sort & Filter قرار دارد، کلیک کنید. توجه: برای مرتب سازی به ترتیب نزولی، روی ZA کلیک کنید.

شما همچنین می توانید بر روی چندین ستون در کاربرگ خود مرتب کنید. مراحل زیر را اجرا کنید. بر روی Sort کلیک کنید که در گروه Sort & Filter در زبانه Data یافت می شود. سطوحی را که می خواهید بر اساس آن ها مرتب کنید اضافه کنید. روی OK کلیک کنید.

فیلتر کردن در اکسل

زمانی که می‌خواهیم داده‌هایی را بدست آوریم که با شرایط خاص مطابقت دارند، از فیلتر کردن استفاده می‌کنیم. روی هر تک سلولی داخل داده‌های خود کلیک کنید. به Tab Data > Sort and Filter > Filter بروید. متوجه خواهید شد که نوک پیکان ها در ستون ها ظاهر شده اند. اکنون می توانید بر اساس نیاز خود فیلتر کنید.

COUNTIF یک تابع بسیار رایج اکسل است که برای شمارش سلول‌ها در محدوده‌ای که یک شرط واحد را برآورده می‌کند استفاده می‌شود. نحو:

=COUNTIF (محدوده، معیارها)

تابع SUMIF در اکسل

تابع Excel SUMIF مجموع سلول هایی را که دارای یک شرط واحد هستند را برمی گرداند. نحو:

=SUMIF (محدوده، معیارها، [جمع_محدوده])

جداول محوری در اکسل

جداول محوری به عنوان هدفمندترین و قدرتمندترین ویژگی در اکسل شناخته می شوند. ما از آنها در خلاصه کردن داده های ذخیره شده در جدول استفاده می کنیم. آنها آمارها را سازماندهی و مرتب می کنند (یا “محور”) را برای جلب توجه حقایق مهم و ارزشمند. این کمک می کند تا مجموعه داده های بسیار بزرگی تهیه کنید و داده های مرتبط مورد نیاز خود را به روشی واضح، آسان و قابل مدیریت ببینید. برای درج جدول محوری در برگه خود، مراحل ذکر شده در زیر را دنبال کنید: روی هر سلول در مجموعه داده کلیک کنید. در تب Insert، در گروه Tables، روی PivotTable کلیک کنید. یک جعبه گفتگو ظاهر خواهد شد. اکسل مجموعه داده شما را به طور خودکار انتخاب می کند. همچنین یک کاربرگ جدید برای جدول محوری شما ایجاد می کند. روی Ok کلیک کنید. سپس، یک کاربرگ جدول محوری ایجاد می کند. فیلدها را بکشید

ابزارهای What if Analysis در اکسل

What-If Analysis فرآیند تغییر مقادیر برای آزمایش مقادیر مختلف (سناریوها) برای فرمول ها است. می توانید از چندین مجموعه مقادیر مختلف در یک یا چند فرمول برای بررسی همه نتایج مختلف استفاده کنید. برای تجزیه و تحلیل what-if، حل کننده یک برنامه افزودنی مایکروسافت اکسل است که در سطوح مختلف مفید است. می توانید از این ویژگی برای یافتن مقدار بهینه (حداکثر یا حداقل) برای فرمول در یک سلول استفاده کنید که به سلول هدف معروف است. این تابع برخی از محدودیت‌ها یا محدودیت‌ها در مقادیر سایر سلول‌های فرمول در یک کاربرگ است. حل‌کننده با گروهی از سلول‌ها به نام متغیرهای تصمیم یا سلول‌های ساده متغیر کار می‌کند که در محاسبه فرمول‌های سلول‌های هدف و محدودیت استفاده می‌شوند. حل کننده همچنین مقادیر سلول های متغیر تصمیم را برای کار بر روی محدودیت های سلول های محدودیت تنظیم می کند. در نتیجه به ایجاد نتیجه مطلوب برای سلول هدف کمک می کند.

ابزار solver در اکسل

در تب File، روی گزینه ها کلیک کنید. به Add-ins بروید، Solver Add-in را انتخاب کنید و روی دکمه Go کلیک کنید. Solver Add-in را بررسی کرده و OK کنید. در سربرگ Data، در گروه Analyze می بینید که گزینه Solver اضافه شده است. در تب Data، در گروه Analysis، روی دکمه Solver کلیک کنید. در هدف تعیین شده، سلول درآمد را انتخاب کنید و ارزش آن را تنظیم کنید. برای تغییر سلول متغیر، سلول ها را انتخاب کنید. روی Solver کلیک کنید.

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

• روی زبانه File کلیک کنید، روی گزینه ها کلیک کنید و سپس روی دسته Add-Ins کلیک کنید.
• Analysis ToolPak را انتخاب کرده و روی دکمه Go کلیک کنید.
• Analysis ToolPak را بررسی کرده و روی OK کلیک کنید.
• در تب Data در گروه Analysis اکنون می توانید بر روی Data Analysis کلیک کنید.

آمار توصیفی با مایکروسافت اکسل

آمار توصیفی یکی از اطلاعات اساسی “باید دانستن” هر مجموعه داده است. این به شما ایده ای در مورد زیر می دهد:

1. میانگین
2. میانه
3. مد
4. محدوده
5. واریانس
6. انحراف معیار

به تب Data > Analysis group > Data analysis بروید. Descriptive Statistics را انتخاب کرده و OK کنید. آمار توصیفی شما آماده است.

تحلیل واریانس در اکسل

ANOVA (تحلیل واریانس) در اکسل یک روش آماری است که برای آزمایش تفاوت بین دو یا چند میانگین استفاده می شود. برای اجرای ANOVA تک عاملی، مراحل را دنبال کنید. به تب Data > Analysis group > Data analysis بروید. Anova: Single Factor را انتخاب کرده و OK کنید. محدوده ورودی و خروجی را انتخاب کرده و OK کنید. تحلیل ANOVA شما آماده است. در اکسل از تحلیل رگرسیون برای تخمین روابط بین دو یا چند متغیر استفاده می کنیم.

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

1. R چندگانه ضریب همبستگی است که قدرت رابطه خطی بین دو متغیر را اندازه گیری می کند.
2. مربع R نشان‌دهنده ضریب تعیین است که به عنوان نشانگر خوب بودن تناسب استفاده می‌شود. با کمک مربع R می توانید تعداد نقاط روی خط رگرسیون را ردیابی کنید.
3. خطای استاندارد یکی دیگر از معیارهای خوبی است که دقت تحلیل رگرسیون شما را نشان می دهد.