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

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

برای شروع تحلیل داده‌ها، موارد زیر را باید بدانید:

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

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

جداول محوری (Pivot Tables)

جداول محوری برای همه نوع تحلیل داده‌ها از داده‌های فروش/بازاریابی تا داده‌های منابع انسانی بسیار مهم هستند.

اما جداول محوری دقیقاً چیستند؟ به زبان ساده، اگر سؤالاتی درباره مجموعه داده‌های خود داشته باشید مانند کدام محصولات بیشترین فروش را دارند و چه کسانی وفادارترین مشتریان من هستند، جدول محوری به شما این امکان را می‌دهد که به سرعت به این سؤالات پاسخ دهید.

مثال از جدول محوری:

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

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

چگونه جداول محوری بسازیم:

1. همه داده‌هایی که می‌خواهید تحلیل کنید را انتخاب کنید (CTRL + A برای انتخاب همه).
2. به تب Insert بروید و گزینه Pivot Table را انتخاب کنید.
3. متغیرهای خود را به فیلدهای ردیف‌ها، ستون‌ها و مقادیر بکشید و رها کنید.

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

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

نمودارها/گراف‌ها

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

برای بیشتر تحلیل‌ها، سه نمودار اصلی مهم‌ترین هستند:

1. نمودارهای میله‌ای (Bar Charts)
2. نمودارهای پراکندگی (Scatter Plots)
3. سری‌های زمانی (Time Series)

چگونه سریعاً نمودارها را در اکسل ایجاد کنیم:

1. داده‌هایی که می‌خواهید بصری‌سازی کنید را انتخاب کنید.
2. به تب Insert بروید. اینجا بیشتر ابزارهای تحلیل داده شما از جمله نمودارها و جداول محوری قرار دارند.
3. گزینه Recommended Charts را انتخاب کنید و اگر مناسب نیازهای شما بود، آن را انتخاب کنید. در غیر این صورت، به All Charts بروید و نمودار مناسبی را در آنجا پیدا کنید. گزینه‌های زیادی در آنجا موجود است.

نمودارهای میله‌ای (Bar Charts) :

نمودارهای میله‌ای اساس و پایه تحلیل داده‌ها هستند. شما صدها تا از این نمودارها را خواهید ساخت!

نمودارهای میله‌ای برای مطالعه رابطه بین دو متغیر ایده‌آل هستند:

1. یک متغیر دسته‌ای مانند جنسیت، محصول، نوع پرداخت
2. یک اندازه‌گیری عددی مانند هزینه، سود، تعداد.

نمودار میله‌ای به شما اجازه می‌دهد رابطه بین این موارد را مشاهده کنید، مثلاً: چگونه نوع پرداخت بر سود تأثیر می‌گذارد؟

استراتژی‌های پیشرفته‌تر برای نمودارهای میله‌ای شامل برش زدن نمودار میله‌ای می‌شود.

فرض کنید می‌خواهیم رابطه بین یک متغیر سوم، مثلاً جنسیت، را مطالعه کنیم. می‌خواهیم ببینیم آیا مردان یا زنان بیشتر از ewallet، پول نقد یا کارت اعتباری استفاده می‌کنند.

به‌طور کلی، اگر متغیر سوم دسته‌های زیادی نداشته باشد، برش زدن بهتر کار می‌کند مانند جنسیت.

نمودارهای پراکندگی (Scatter Plots) 

نمودارهای پراکندگی به شما اجازه می‌دهند همبستگی و توزیع بین دو متغیر عددی را ببینید. مثلاً: قدرت خرید مشتری در مقابل درآمد خانوار.

یک خط رگرسیون می‌تواند برای کمک به پیش‌بینی داده‌ها ترسیم شود. برای مثال: قدرت خرید مشتری چقدر خواهد بود اگر درآمد خانوار آن‌ها ۶۰,۰۰۰ دلار باشد؟

چگونه یک خط رگرسیون در نمودار پراکندگی اکسل ایجاد کنیم:

1. نمودار پراکندگی خود را ایجاد کنید.
2. روی Add Chart Element در بالای سمت چپ کلیک کنید.
3. Trendline -> Linear را انتخاب کنید.

سری‌های زمانی (Time Series)

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

این نوع نمودار بسیار شبیه به نمودار خطی است، اما محور x همیشه مرتبط با زمان است در حالی که محور y یک متغیر عددی است.

پایگاه داده SQL Server رو قورت بده! بدون کلاس، سرعت 2 برابر، ماندگاری 3 برابر، پولسازی بلافاصله ... دانلود:

پک کامل SQL Server

پک مبانی برنامه نویسی (اختیاری)

مثالی از سری زمانی که به وضوح نشان می‌دهد ترافیک وب‌سایت در آخر هفته‌ها کاهش می‌یابد:

مشابه نمودارهای پراکندگی، یک خط روند می‌تواند برای پیش‌بینی کسب‌وکار (اغلب برای پیش‌بینی فروش) ترسیم شود.

چگونه یک خط روند در نمودار سری زمانی ایجاد کنیم:

1. سری زمانی خود را ایجاد کنید.
2. روی Add Chart Element کلیک کنید.
3. Trendline -> Linear Forecast را انتخاب کنید.

دستکاری و تمیز کردن داده‌ها

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

اکسل بیش از ۴۷۵ فرمول دارد!

هیچ ابزاری نمی‌تواند با توانایی اکسل برای دستکاری داده‌ها به هر شکلی که می‌خواهید رقابت کند، مگر اینکه شروع به کدنویسی با استفاده از R یا Python کنید که ماه‌ها طول می‌کشد تا یاد بگیرید و برای اکثر افراد غیر ضروری است.

با اینکه ۴۷۵ فرمول ممکن است به نظر زیاد بیاید، لازم نیست همه آن‌ها را یاد بگیرید. در عوض، اکثر حرفه‌ای‌های اکسل زمانی که به فرمولی نیاز دارند، آن را سرچ می‌کنند. گوگل دوست خوب شما در کنارتان است.

پایه‌ترین توابع ریاضی که باید یاد بگیرید عبارتند از:

=SUM(A1

) همه مقادیر از A1 تا A50 را جمع می‌کند. COUNT، AVERAGE و MEDIAN نیز به همین شکل ساختار دارند. یادگیری نحوه نوشتن این توابع، زمان زیادی را به خاطر جستجو در گوگل صرفه‌جویی می‌کند.

مکمل این مطلب: چگونگی تجزیه و تحلیل داده های آماری پرسشنامه ای در اکسل

۱۰ فرمول رایج اکسل

مفیدترین توابع در اکسل توابع CountIfs و SumIfs هستند:

=SUMIF : جمع همه مقادیری که یک معیار را برآورده می‌کنند. مثلاً

 =SUMIF(C2, >2000)

همه مقادیر بین سلول‌های C2که بیشتر از ۲۰۰۰ هستند را جمع می‌کند.

=CountIF : شمارش همه مقادیری که یک معیار مشخص را برآورده می‌کنند. مثلاً

=COUNTIF(C2, ???es)

همه کلمات ۵ حرفی که به es ختم می‌شوند را شمارش می‌کند.

1. =COUNTIFS:  شمارش همه مقادیری که معیارهای متعددی را برآورده می‌کنند.
2. =SUMIFS:  جمع همه مقادیری که معیارهای متعددی را برآورده می‌کنند.
3. =LEFT:  به شما می‌گوید چه چیزی در سمت چپ یک سلول مشخص قرار دارد. برای مثال اگر سلول C2 حاوی کلمه data باشد، =LEFT(C2) نتیجه d را خواهد داد. شما می‌توانید تعداد کاراکترهایی که می‌خواهید بگیرید را نیز مشخص کنید: =LEFT(C2,3) نتیجه dat را خواهد داد.
4. =RIGHT: همان کار =LEFT را انجام می‌دهد، اما کاراکترها را از سمت راست می‌گیرد.
5. =VLOOKUP: نگاه می‌کند که یک سلول به چه مقداری مرتبط است. برای مثال، ممکن است شماره آیتم‌هایی برای محصولاتی که می‌فروشید داشته باشید. VLOOKUP به شما می‌گوید که این محصول به چه شماره آیتمی مربوط است.
6. =IF: بررسی می‌کند که آیا یک شرط برآورده شده است یا خیر. نتیجه TRUE یا FALSE برمی‌گرداند.
7. =NETWORKDAYS: تعداد روزهای کاری بین دو تاریخ مشخص را محاسبه می‌کند.
8. =YEAR: سال یک تاریخ را می‌گیرد. مثلاً ۷-۱۲-۲۰۲۴ به ۲۰۲۴ تبدیل می‌شود.

به‌طور کلی، دستکاری و تمیز کردن داده‌ها از یک کار به کار دیگر متفاوت است. بسته به مجموعه داده‌ها، ممکن است مجبور شوید فرمول‌ها را هر زمانی که به آن‌ها نیاز دارید، جستجو کنید.
مکمل این مطلب: دانلود رایگان آموزش صفر تا صد اکسل (فیلم فارسی+PDF)

چگونه داده‌ها را در اکسل تحلیل کنیم؟

در اینجا نحوه تحلیل داده‌ها در اکسل آورده شده است:

1. ابزار تحلیل داده در اکسل را باز کنید: File -> Options (در پایین سمت چپ) -> Add-ins -> Go -> ابزار Analysis Toolpak را انتخاب کنید و روی OK کلیک کنید.
2. به تب Data بروید و روی Analyze Data کلیک کنید.
3. نوع تحلیلی که می‌خواهید انجام دهید را انتخاب کنید.

شما می‌توانید ANOVAها، آزمون‌های t، آزمون‌های z، همبستگی‌ها، رگرسیون، آمار توصیفی و موارد دیگر را انجام دهید.

ابزار Analysis Toolpak برای افرادی با دانش آماری متوسط طراحی شده است، اما قادر به انجام تحلیل‌های پیچیده است.

ویدیوهای دروس آموزشی آنالیز اطلاعات در اکسل

این آموزشهای ویدیویی بصورت گلچین شده گرداوری شده اند تا با تکنیک های مختلف انالیز دیتا آشنا شوید:

• اگر تاکنون سعی کرده‌اید داده‌های بزرگ را در اکسل تحلیل کنید، متوجه می‌شوید که انجام توابع ابتدایی مانند Vlookups باعث می‌شود برنامه هنگ کرده و سپس کرش کند.
• اکسل برای پردازش داده‌های بزرگ طراحی نشده است. هر صفحه‌گسترده دارای محدودیت یک میلیون ردیف است و حتی اگر صفحه‌گسترده شما این محدودیت را نداشته باشد، ممکن است برنامه در حین محاسبات کرش کند و زمان ارزشمند شما را هدر دهد.
• خوشبختانه پلاگین‌ها و قالب‌های صفحه‌گسترده اکسل برای این وجود دارند. Power Pivot یک پلاگین است که به شما اجازه می‌دهد بیش از یک میلیون ردیف را در اکسل پردازش کنید. (برای دانلود پلاگین کلیک کنید)
• این پلاگین برای تحلیل داده‌های بزرگ تنظیم شده است، زیرا سریع‌تر است و می‌توانید داده‌ها را از منابع مختلف متصل کنید.

نکته تکمیلی

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