تحلیل داده در اکسل
در دنیای امروز، تحلیل داده یکی از مهارت‌های ضروری برای هر کسی است که با داده‌ها سروکار دارد. از مدیران گرفته تا تحلیل‌گران، همه نیاز به توانایی تحلیل داده دارند تا تصمیم‌گیری‌های هوشمندانه‌تری انجام دهند. یکی از ابزارهای پرکاربرد در این زمینه، نرم‌افزار اکسل (Excel) است. یادگیری تحلیل داده در اکسل ممکن است کار پیچیده‌ای به نظر برسد. به‌هرحال، رابط کاربری اکسل ضعیف است و سیستم آن به‌قدری بزرگ است که به‌راحتی می‌توان دچار سردرگمی شد. خبر خوب این است که اگر این فرآیند را گام‌به‌گام پیش ببرید، بسیار ساده‌تر از آن چیزی است که فکر می‌کنید. در این مقاله به آموزش data analysis در اکسل می‌پردازیم و به شما یاد می‌دهیم که چگونه دیتا آنالیز در اکسل انجام دهید.
آموزش data analysis در اکسل
بیشتر قابلیت‌های اکسل را هرگز استفاده نخواهید کرد. این قابلیت‌ها فقط برای موارد خاصی وجود دارند و این همان نکته عالی در مورد اکسل است. شما فقط باید موارد اصلی را یاد بگیرید و در صورت نیاز به راهنماها مراجعه کنید. برای شروع تحلیل داده در اکسل لازم است تکنیک‌های زیر را بدانید:
تکنیک‌های تحلیل داده در اکسل برای مبتدیان
باوجوداینکه یادگیری تکنیک‌های دیتا آنالیز در اکسل آسان است؛ اما برای تحلیل‌گران داده در همه سطوح بسیار مفید هستند.
جداول محوری (Pivot Tables)
جداول محوری برای انواع تحلیل داده‌ها از داده‌های فروش/بازاریابی گرفته تا داده‌های منابع انسانی بسیار مهم هستند؛ اما جداول محوری دقیقاً چه هستند؟
در وبسایت رسمی مایکروسافت در مورد جداول محوری چنین نوشته شده است:
PivotTable یک ابزار قدرتمند برای محاسبه، خلاصه‌کردن و تجزیه‌وتحلیل داده‌ها است که به شما امکان می‌دهد مقایسه‌ها، الگوها و روندها را در داده‌های خود مشاهده کنید. PivotTableها بسته به پلتفرمی که برای اجرای اکسل استفاده می‌کنید، کمی متفاوت عمل می‌کنند.
به‌طور خلاصه، اگر سؤالاتی در مورد مجموعه داده خود دارید مانند «کدام محصولات بیشترین فروش را دارند؟» و «وفادارترین مشتریان من چه کسانی هستند؟»، یک جدول محوری به شما امکان می‌دهد به‌سرعت به این سؤالات پاسخ دهید.
فرض کنید یک مجموعه داده شامل ۴۱ رکورد با ۵ فیلد اطلاعات در مورد اطلاعات خریدار داریم. این داده‌ها برای درک جدول محوری عالی هستند.
نحوه ایجاد جداول محوری به‌صورت زیر است:
- تمام داده‌هایی را که می‌خواهید تحلیل کنید، انتخاب کنید (CTRL + A برای انتخاب همه چیز).
- به تب Insert بروید و Pivot Table را انتخاب کنید.
یک کادر گفتگو ظاهر خواهد شد. اکسل به‌طور خودکار مجموعه داده شما را انتخاب می‌کند. همچنین یک کاربرگ جدید برای جدول محوری شما ایجاد خواهد کرد.
۳. روی Ok کلیک کنید. یک کاربرگ جدول محوری ایجاد خواهد کرد.
۴. برای به‌دست‌آوردن مجموع اقلام خریداری‌شده توسط هر خریدار، فیلدهای زیر را به مناطق زیر بکشید:
- فیلد Buyer را به ناحیه Rows بکشید.
- فیلد Items را به ناحیه Values بکشید.
نمودارها/گراف‌ها
بصری‌سازی داده‌ها فقط برای ارائه داده‌ها نیست، بلکه برای مشاهده روندها، همبستگی‌ها و الگوها در داده‌ها حین دیتا آنالیز در اکسل هم مفید است.
برای اکثر تحلیل‌ها، سه نمودار مهم عبارت‌اند از:
- نمودارهای میله‌ای (Bar Charts)؛
- پلات‌های پراکندگی (Scatter plots)؛
- سری‌های زمانی (Time series).
نحوه ایجاد نمودار در اکسل به‌صورت زیر است:
- داده‌هایی را که می‌خواهید بصری‌سازی کنید، انتخاب کنید.
- به تب “Insert” بروید.
- “Recommended Charts” را انتخاب کنید و اگر یکی از آن‌ها نیاز شما را برآورده می‌کند، آن را انتخاب کنید. درغیراین‌صورت، به “All Charts” بروید و گزینه مناسب را پیدا کنید.
نمودارهای میله‌ای:
نمودارهای میله‌ای، اساس تحلیل داده در اکسل هستند و برای مطالعه رابطه بین دو متغیر زیر ایدئال محسوب می‌شوند:
- یک متغیر دسته‌بندی مانند جنسیت، محصول، نوع پرداخت؛
- یک اندازه‌گیری عددی مانند هزینه، سود، مقدار.
یک نمودار میله‌ای به شما امکان می‌دهد رابطه بین این موارد را ببینید؛ به‌عنوان‌مثال: چگونه نوع پرداخت بر سود تأثیر می‌گذارد؟
استراتژی‌های پیشرفته‌تر برای نمودارهای میله‌ای شامل برش نمودار میله‌ای است:
فرض کنید می‌خواهیم رابطه بین یک متغیر سوم مانند جنسیت را مطالعه کنیم. می‌خواهیم ببینیم آیا مردان یا زنان تمایل بیشتری به استفاده از کیف پول الکترونیکی، پول نقد یا کارت اعتباری دارند:
به‌طورکلی، برش نمودار هنگامی بهتر عمل می‌کند که متغیر سوم شامل دسته‌های زیادی نباشد؛ مانند جنسیت.
نمودارهای پراکندگی:
نمودارهای پراکندگی به شما امکان می‌دهند همبستگی و توزیع بین دو متغیر عددی را ببینید. به‌عنوان‌مثال، قدرت خرید مشتری در مقابل درآمد خانوار.
خط بهترین برازش (line of best fit) می‌تواند برای کمک به پیش‌بینی داده‌ها ترسیم شود. به‌عنوان‌مثال، قدرت خرید یک مشتری در صورتی که درآمد خانوار او ۶۰,۰۰۰ دلار باشد، چقدر خواهد بود؟
خط بهترین برازش در نمودارهای پراکندگی اکسل به‌صورت زیر ترسیم می‌شود:
- نمودار پراکندگی خود را ایجاد کنید.
- روی “Add Chart Element” در بالا سمت چپ کلیک کنید.
- Trendline -> Linear را انتخاب کنید.
سری‌های زمانی:
سری‌های زمانی به شما امکان می‌دهند روندها را در طول زمان تحلیل و آینده را پیش‌بینی کنید.
این نمودار بسیار شبیه به یک نمودار خطی است؛ اما محور x همیشه مرتبط با زمان است؛ درحالی‌که محور y یک متغیر عددی است.
مثال زیر یک سری زمانی را که به‌وضوح نشان می‌دهد ترافیک وبسایت در آخر هفته کاهش می‌یابد، نشان می‌دهد.
مشابه نمودارهای پراکندگی، یک خط روند می‌تواند برای پیش‌بینی‌های تجاری (اغلب برای پیش‌بینی فروش) ترسیم شود.
نحوه ترسیم یک خط روند در نمودار سری زمانی به‌صورت زیر است:
- سری زمانی خود را ایجاد کنید.
- روی “Add Chart Element” کلیک کنید.
- Trendline -> Linear Forecast را انتخاب کنید.
دستکاری و پاکسازی داده‌ها در اکسل
یکی از بزرگ‌ترین نقاط قوت اکسل، توانایی آن در دستکاری داده‌ها، به هر شکلی است که بخواهید. بیش از ۴۷۵ فرمول در اکسل وجود دارد! هیچ ابزار دیگری نمی‌تواند با توانایی اکسل در دستکاری داده‌ها به هر شکلی که بخواهید، مقایسه شود؛ مگر اینکه کدنویسی با R یا Python کنید که ممکن است ماه‌ها طول بکشد و برای اکثر افراد غیرضروری است.
با اینکه ۴۷۵ فرمول ممکن است زیاد به نظر برسد، نیازی به یادگیری همه آن‌ها ندارید. در عوض، اکثر حرفه‌ای‌های اکسل فقط فرمول‌ها را در صورت نیاز جستجو می‌کنند. گوگل در اینجا بهترین دوست شماست.
پایه‌ای‌ترین توابع حسابی که باید برای تحلیل داده در اکسل یاد بگیرید، عبارت‌اند از:
SUM، COUNT، AVERAGE و MEDIAN.
۱۰ فرمول رایج اکسل برای تحلیل داده ها
مفیدترین توابع برای تحلیل داده در اکسل عبارت‌اند از:
- `=SUMIF`: جمع کردن تمام مقادیری که با یک معیار خاص مطابقت دارند.
در مقاله آموزش SUMIF در اکسل که در وبسایت ۲۴ وب لرن منتشر شده است، در توصیف این تابع گفته شده است که SUMIF به شما اجازه می‌دهد از یک عبارت منطقی (در این مورد، یک عبارت IF) استفاده کنید تا به اکسل کمک کند قبل‌از محاسبه مجموع تصمیم بگیرد کدام سلول‌ها را درنظر بگیرد. مثال: `=SUMIF(C2:C20, “>2000”)` تمام مقادیر بین سلول‌های C2 تا C20 که بیش از ۲۰۰۰ هستند را جمع می‌کند.
- `=COUNTIF`: شمارش تمام مقادیری که با یک معیار خاص مطابقت دارند.
مثال: `=COUNTIF(C2:C200, “???es”)` تمام کلمات پنج‌حرفی را که به “es” ختم می‌شوند، می‌شمارد.
- `=COUNTIFS`: شمارش تمام مقادیری که با چند معیار مطابقت دارند.
مثال: `=COUNTIFS(A2:A100, “Male”, B2:B100, “>30”)` تعداد مردان بالای ۳۰ سال را می‌شمارد.
- `=SUMIFS`: جمع‌کردن تمام مقادیری که با چند معیار مطابقت دارند.
مثال: `=SUMIFS(D2:D100, A2:A100, “Product A”, B2:B100, “Region 1”)` مجموع فروش Product A در Region 1 را محاسبه می‌کند.
- `=LEFT`: برگرداندن کاراکترهای سمت چپ یک سلول.
مثال: اگر سلول C2 حاوی کلمه “data” باشد، `=LEFT(C2)` نتیجه “d” و `=LEFT(C2, 3)` نتیجه “dat” را برمی‌گرداند.
- `=RIGHT`: مشابه `=LEFT`، اما کاراکترهای سمت راست را برمی‌گرداند.
مثال: اگر سلول C2 حاوی کلمه “data” باشد، `=RIGHT(C2)` نتیجه “a” و `=RIGHT(C2, 2)` نتیجه “ta” را برمی‌گرداند.
- `=VLOOKUP`: جستجوی مقداری که یک سلول به آن مربوط است.
مثال: اگر شماره‌های شناسه محصولات را دارید، `=VLOOKUP(ProductID, A2:B100, 2, FALSE)` به شما می‌گوید که شماره شناسه به کدام محصول مربوط است.
- `=IF`: بررسی اینکه آیا یک شرط برآورده شده است یا خیر.
مثال: `=IF(A2 > 1000, “High”, “Low”)` بررسی می‌کند که آیا مقدار A2 بیش از ۱۰۰۰ است یا خیر و در صورت درست‌بودن “High” و درغیراین‌صورت “Low” را برمی‌گرداند.
- `=NETWORKDAYS`: محاسبه تعداد روزهای کاری بین دو تاریخ مشخص.
مثال: `=NETWORKDAYS(“2024-01-01”, “2024-01-31”)` تعداد روزهای کاری بین ۱ ژانویه ۲۰۲۴ و ۳۱ ژانویه ۲۰۲۴ را محاسبه می‌کند.
- `=YEAR`: استخراج سال از یک تاریخ.
مثال: `=YEAR(“2024-07-12”)` نتیجه ۲۰۲۴ را برمی‌گرداند.
به‌طورکلی، دستکاری و پاکسازی داده‌ها از یک وظیفه به وظیفه دیگر متفاوت است. بسته به مجموعه داده ممکن است مجبور شوید فرمول‌ها را هر زمان که نیاز دارید، جستجو کنید.
چگونه تحلیل داده در اکسل انجام دهیم؟
نحوه تحلیل داده در اکسل به‌صورت زیر است:
- افزونه Analysis Toolpak را در اکسل بارگذاری کنید:
File -> Options (at the bottom left) -> Add-ins -> Go -> Check the Analysis Toolpak and press OK.
- به تب “Data” بروید و “Analyze Data” را انتخاب دهید.
- نوع تحلیلی را که می‌خواهید انجام دهید، انتخاب کنید.
شما می‌توانید ANOVA، آزمون‌های t، آزمون‌های z، همبستگی، رگرسیون، آمار توصیفی و موارد دیگر را انجام دهید. توجه داشته باشید که Analysis Toolpak برای افرادی با دانش آماری متوسط طراحی شده است؛ اما قادر به انجام تحلیل‌های پیچیده است.
آموزش data analysis در اکسل برای داده‌های بزرگ
با استفاده از Analysis Toolpak می‌توانید تحلیل فاکتور (factor analysis) را در اکسل انجام دهید.
تحلیل فاکتور راهی برای تجزیه مجموعه داده‌های بزرگ با ترکیب چند متغیر با یکدیگر است. کلید تحلیل فاکتور یافتن کوواریانس است.
کوواریانس به این معناست که شما به‌دنبال متغیرهایی هستید که به‌شدت با یکدیگر همبستگی دارند. به‌عنوان‌مثال، درآمد خانوار و تمایل به خرج‌کردن ممکن است به‌شدت با هم همبستگی داشته باشند. وقتی این متغیرها را پیدا کردید، می‌توانید آن‌ها را در یک فاکتور واحد به‌نام «قدرت خرید» گروه‌بندی کنید.
اگر تابه‌حال تلاش کرده‌اید داده‌های بزرگ را در اکسل تحلیل کنید، متوجه می‌شوید که انجام عملکردهای پایه‌ای مانند Vlookup باعث می‌شود برنامه فریز و سپس کرش کند.
اکسل برای مدیریت داده‌های بزرگ طراحی نشده است. هر صفحه‌گسترده محدود به یک میلیون ردیف است و حتی اگر صفحه‌گسترده شما از این حد فراتر نرود، احتمال دارد برنامه در حین محاسبات کرش کند و زمان ارزشمندی را هدر دهد.
خوشبختانه افزونه‌ها و قالب‌های صفحه‌گسترده اکسل برای این منظور وجود دارند. Power Pivot یک افزونه است که به شما امکان می‌دهد بیش از یک میلیون ردیف را در اکسل پردازش کنید.
این افزونه برای تحلیل داده‌های بزرگ طراحی شده است؛ زیرا سریع‌تر است و می‌توانید داده‌ها را از منابع مختلف به هم متصل کنید.
چگونه با یادگیری اکسل فرصت‌های شغلی جدید ایجاد کنید؟
اگر به‌دنبال یادگیری جامع و حرفه‌ای اکسل هستید و می‌خواهید در شرکت و اداره خود به‌عنوان متخصص اکسل مزیت رقابتی داشته باشید، دوره آموزش اکسل از مقدماتی تا پیشرفته ۲۴ وب لرن انتخابی بی‌نظیر برای شماست! این دوره با بیش از ۹ ساعت آموزش و ۱۸ ویدیو آموزشی، تمام مباحث مورد نیاز را برای تسلط بر اکسل پوشش می‌دهد و با قیمت ویژه ۱۹۹.۰۰۰ تومان (به جای ۴۹۹.۰۰۰ تومان)، فرصت بی‌نظیری را برای یادگیری تمام مهارت‌های اکسل فراهم می‌کند.
با خرید دوره آموزش جامع اکسل ۲۴ وب لرن، شما به تمام فایل‌ها و بروزرسانی‌ها دسترسی مادام‌العمر خواهید داشت و از پشتیبانی دائمی بهره‌مند خواهید شد. فرصت را از دست ندهید! همین حالا از صفحه محصول دوره اکسل مقدماتی تا پیشرفته ۲۴ وب لرن دیدن کنید، به یک متخصص اکسل تبدیل شوید و در تصمیم‌گیری‌های خود، یک قدم جلوتر باشید.
سوالات متداول
- آیا اکسل برای تحلیل داده‌های بزرگ مناسب است؟
اکسل می‌تواند برای تحلیل داده‌های بزرگ هم مورد استفاده قرار گیرد؛ اما محدودیت‌هایی دارد. برای داده‌های بسیار بزرگ (بیش از چند میلیون ردیف)، ممکن است اکسل کند عمل کند و نیاز به استفاده از ابزارهای پیشرفته‌تری مانند Power BI یا نرم‌افزارهای تخصصی تحلیل داده باشد.
- چگونه می‌توانم داده‌های خود را در اکسل به‌صورت خودکار تحلیل کنم؟
اکسل امکان خودکارسازی تحلیل‌ها را با استفاده از ماکروها و VBA (Visual Basic for Applications) فراهم می‌کند. با استفاده از این ابزارها می‌توانید فرآیندهای تکراری را خودکار کنید و تحلیل‌های خود را به‌صورت خودکار انجام دهید.
- آیا می‌توانم از اکسل برای پیش‌بینی داده‌ها استفاده کنم؟
بله، اکسل ابزارهای پیش‌بینی مختلفی مانند توابع رگرسیون و تحلیل روند دارد که می‌توانید از آن‌ها برای پیش‌بینی داده‌ها استفاده کنید. همچنین، ابزارهای پیشرفته‌تری مانند تحلیل What-if و توابع مالی هم می‌توانند برای پیش‌بینی داده‌ها مفید باشند.
سخن پایانی
تحلیل داده در اکسل یکی از مهارت‌های ضروری برای هر کسی است که با داده‌ها سروکار دارد. با استفاده از ابزارها و قابلیت‌های مختلف اکسل می‌توانید داده‌های خود را به‌صورت مؤثر تحلیل و اطلاعات ارزشمندی را استخراج کنید. این مهارت نه‌تنها به شما در تصمیم‌گیری‌های بهتر کمک می‌کند، بلکه می‌تواند به بهبود عملکرد سازمان یا کسب‌وکار شما هم منجر شود.
منابع
https://www.polymersearch.com/blog/how-to-analyze-data-in-excel
https://www.simplilearn.com/tutorials/excel-tutorial/data-analysis-excel
