ساخت داشبورد از صفر تا صد

آموزش ساخت داشبورد از صفر تا صد

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

دیتابیسی داریم از میزان فروش دو فروشگاه زنجیره ای معروف در استرالیا (فروشگاه Fashion Direct و Next Store) که میزان فروش رو در سال مالی و ماه های مختلف و به تفکیک مناطق جغرافیایی (Suburb/ State/ Postcode/Country) و مدیران و دسته بندی محصول (Category) و خریدار ثبت شده است. میخواهیم تحلیل هایی از میزان فروش و چگونگی توزیع در سطح کشور رو بدست بیاریم. در شیت Database داده های خام وجود داره که میخوایم از روی اونها داشبورد رو بسازیم.

پیشنهاد میکنیم که به عنوان اولین مرحله داده ها رو به Table تبدیل کنید. این کار مزیت های متنوعی داره که یکی از مهم ترین هاش اینه که اگر دیتا بهش اضافه بشه خودش آپدیت میشه. برای این کار کافیه Ctrl+A بزنیم و بعد Ctrl+T یا از تب Insert گزینه Table رو انتخاب کنیم.

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

ایجاد نمودارها و گزارش های مورد نیاز برای داشبورد

حالا با توجه به داده ها میخوایم گزارشی تهیه کنیم که مجموع میزان فروش رو به تفکیک زمان و در دو فروشگاه نمایش بده. برای این کار وارد تب insert میشیم و گزینه Pivottable رو میزنیم. در پنجره ای که نمایش داده میشه، چک میکنیم که منبع داده های خام درست انتخاب شده باشه و بعد OK رو میزنیم.

حالا وارد فضای ایجاد Pivotable میشیم و باید گزارش دلخواه رو بسازیم. اگر با منطق کلی پیوت تیبل، نحوه ایجاد و مدل کارکردش آشنا نیستید حتما مقاله مربوط به ۴ گام ایجاد Pivottable رو مطالعه کنید. برای تهیه این گزارش کافیه فیلد زمان (Month) رو در row و Chain رو در Column، و میزان Sale رو هم در Value قرار بدیم. وقتی این کار رو میکنیم با توجه به اینکه این فیلد، داده هایی از جنس تاریخ داره، با اضافه کردن این فیلد به قسمت Row خودش تفکیک میشه به سال و ماه (مطابق شکل ۱)

شکل ۱- گزارش میزان فروش به تفکیک ماه در دو فروشگاه

شکل ۲- گروه بندی داده تاریخ در pivottable

حالا برای اینکه برای این گزارش نمودار رسم کنیم و روند فروش دو فروشگاه در ماه های مختلف رو ببنییم کافیه که از تب Pivottable Analyze روی گزینه Pivotchart کلیک کنیم و از پنجره باز شده نمودار خطی Line رو انتخاب کنیم (شکل ۳)

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

حالا برای اینکه نمودار قشنگ تری داشته باشیم، میتونیم رنگ و ضخامت خطوط رو از قسمت Format تغییر بدیم و همچنین روی دکمه های نمودار کلیک راست میکنیم و مطابق شکل، Hide all field Button رو میزنیم تا نمودار خلوت تر و شیک تری داشته باشیم.

شکل ۴- پنهان کردن دکمه های انتخاب آیتم از روی نمودار

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

نمودار مجموع فروش دو فروشگاه به تفکیک دسته بندی محصول (Category)

شکل ۵- نمایش میزان فروش به تفکیک گروه محصول

نمودار مجموع فروش به تفکیک دو فروشگاه

شکل ۶- نمایش میزان فروش به تفکیک دو فروشگاه

نمودار مجموع فروش دو فروشگاه به تفکیک هر ایالت و مدیریت هر ایالت

شکل ۷- نمایش مجموع فروش دو فروشگاه به تفکیک هر ایالت و مدیریت هر ایالت

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

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

حالا میخواهیم نمودار نقشه رو رسم کنیم. اما این نمودار در Pivotchart وجود نداره. قبل از اینکه نکته راجع به رسم این نمودار رو بگم، اول ساختار گزارش رو هم یک تغییرکوچیک بدیم. برای اینکه نام کشور استرالیا قبل همه ایالت ها نمایش داده بشه. کافیه از تب Design مطابق شکل ۹، گزینه Show in Tabular form و Repeat All Items Labels رو انتخاب کنیم.

شکل ۹- تغییر تنظیمات ساختار گزارش

برای اینکه نمودارهایی که در PivotChart پشتیبانی نمیشن رو یک جوری رسم کنیم که داینامیک باشن و با اسلایسرها تغییر کنن، کافیه وقتی گزارش رو با پیوت تیبل ساختیم، همون گزارش رو در جای دیگه ای تکرار کنیم (با استفاده از فرمول نویسی کپی کنیم). برای اینکه بهتر متوجه بشید، به ادامه آموزش دقت کنید:

حالا که گزارش رو ساختیم کافیه در سلول های مجاور، مساوی مقادیر گزارش قرار بدیم، یعنی در یک سلول = میذاریم و روی اولین سلول گزارش کلیک میکنیم. بعد از Enter زدن، فرمول رو درگ میکنیم به سمت راست و پایین. مطابق شکل ۱۰.

شکل ۱۰- کپی کردن گزارش پیوت با استفاده از فرمول (مساوی قرار دادن)

حالا کافیه داده ها رو انتخاب کنیم و از تب Insert نمودار Map رو انتخاب کنیم.

شکل ۱۱- رسم نمودار فروش به تفکیک ایالت ها

ایجاد داشبورد و داینامیک کردن گزارش

حالا که همه نمودارهای مورد نیاز رو رسم کردیم، باید بتونیم این نمودارها رو داینامیک کنیم که بتونیم داشبورد تعاملی رو بسازیم. مثلا گزینه ای باشه که همه این نمودارها رو بتونه برای یک سال مشخص نمایش بده و … برای این کار از Slicer استفاده میکنیم. یکی از جذاب ترین ابزارها در اکسل Slicer است که میتونه داده ها رو فیلتر کنه و همینطور میتونه وصل بشه به Pivotable و Pivotchart. پس به عنوان گام اول، همه نمودارها رو رو منتقل میکنیم به شیت نهایی نمایش (شکل ۱۲) و Slicer ها رو ایجاد میکنیم.

شکل ۱۲- چیدمان داشبورد نهایی

حالا کافیه Slicer رو ایجاد و به گزارش اضافه کنیم. اول باید ببینیم که میخواهیم گزارش روی چه مواردی فیلتر بشه. مثلا میخواهیم گزارش ها روی سال مالی، ایالت ها و دسته بندی محصول فیلتر بشه. پس روی یکی از نمودارها کلیک کرده و از تب Pivotchart Analyze روی گزینه insert slicer کلیک میکنیم. پنجره ای مطابق شکل ۱۳ باز میشه که میتونیم فیلدهایی که میخواهیم فیلتر بر اساس اونها انجام بشه رو انتخاب کنیم.

شکل ۱۳- اضافه کردن Slicer به داشبورد

Slicer ها رو مطابق شکل ۱۴ در کنار داشبورد میچینیم، سپس انتخاب کرده و از تب Design رنگ مناسبی رو انتخاب میکنیم.

شکل ۱۴- تنظیمات Slicer

حالا Slicer رو تغییر میدیم و انتظار داریم که همه نمودارها با توجه به آیتم انتخابی تغییر کنند. اما این اتفاق نمیفته. در واقع فقط همون نموداری تغییر میکنه که اول انتخاب کردیم و Slicer ها رو اضافه کردیم. پس چاره چیه؟ ما میخوایم اسلایسرها به همه گزارش ها وصل باشه. باری این کار کافیه روی Slicer کلیک راست کنیم و از منوی باز شده ، گزینه Report Connection رو انتخاب کنیم. در پنجره باز شده، کافیه انتخاب کنیم که این Slicer قراره به کدوم گزارش ها وصل باشه و اونو تیک میزنیم. مطابق شکل ۱۵. همین کار رو برای همه Slicer ها انجام میدیم.

شکل ۱۵- اتصال Slicer به گزارش های دلخواه

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

زیر نمودار دایره ای فروش فضایی خالی گذاشتیم که بتونیم روند فروش رو در هر ایالات در سال های مختلف نمایش بدیم. برای این کار ابتدا باید گزارش رو با پیوت تیبل بسازیم (چون میخوایم اسلایسرها بهش وصل باشه). پس سه گزارش ایجاد میکنیم:

1. یکی برای نمایش مجموع فروش فروشگاه Nest Look به تفکیک ماه و سال
2. یکی برای نمایش مجموع فروش فروشگاه Fashion Direct به تفکیک ماه و سال
3. .یکی هم برای هر دو. شکل ۱۶

شکل ۱۶- ایجاد ۳ گزارش برای نمایش روند فروش در فروشگاه ها

حالا سرستون ها رو مطابق خواسته تایپ میکنیم و در قسمت State نام ایالت ها رو از روی گزارش کل فروش برای هر دو فروشگاه لیست میکنیم. نکته خیلی مهم اینه که Slicer مربوط به ایالت ها باید به این ۳ گزارشی که ساختیم وصل بشه (با همون روش که در بالا توضیح دادیم). وقتی این کار رو بکنیم، با تغییر اسلایسر، لیست ایالت ها هم تغییر میکنه. برای این تغییر باید فرمول زیر رو بنویسیم:

=IF(Calculation!A41<>””,Calculation!A41,””)

این فرمول چک میکنه که گزارش فروش هر دو فروشگاه روی کدوم ایالت هاست، همونا رو لیست میکنه و بقیه رو خالی میذاره. (فایل اکسل رو دانلود کنید و ببینید بهتر متوجه میشید)

شکل ۱۷- وصل کردن لیست ایالت ها به گزارش پیوت تیبل فروش هر دو فروشگاه

حالا میخوایم در ستون All Chain میزان فروش هر دو فروشگاه رو به تفکیک ایالت نمایش بدیم. کافیه این کار رو با استفاده از تابع Getpivotdata انجام بدیم. ما مجموع فروش به تفکیک ایالت رو در گزارش مربوط به نمودار نقشه داریم. پس کافیه در سلول E17 کلیک کرده و روی سلول مربوط به میزان فروش ایالت در اون گزارش کلیک کنیم. با این کار تابع Getpivotdata ایجاد میشه و کافیه که مقدار ثابت ایالت در تابع رو با مقدار D17 جایگزین کنیم که بتونیم درگ کنیم. (برای درک بهتر تابع Getpivotdata حتما مقاله مربوط به این موضوع رو مطالعه کنید)

= GETPIVOTDATA(“Sales”,Calculation!$AO$1,”State”,D17,”Country”,”Australia”)

حالا این فرمول رو با Iferror ترکیب میکنیم که وقتی اسلایسر رو تغییر میدیم، بخاطر مواردی که نمیخوایم، خطا نمایش داده نشه.

=IFERROR(GETPIVOTDATA(“Sales”,Calculation!$AO$1,”State”,D17,”Country”,”Australia”),””)

حالا میخوایم برای ۳ ستون بعدی نمودار درون سلولی از میزان فروش در ماه ها و سال های مختلف ایجاد کنیم. برای این کار کافیه روی سلول F17 کلیک کرده و از تب insert رویSparkline/Line  کلیک کنیم. از پنجره باز شده محدوده فروش ایالت مورد نظر رو از گزارش مربوط به فروش هر دو فروشگاه انتخاب کنیم. مطابق شکل ۱۷.

شکل ۱۸-انتخاب ردیف مربوط به فروش ایالت ACT در Sparkline

حالا کافیه Sparkline ایجاد شده رو به سمت پایین درگ کنیم. همین کار رو برای دو ستون بعدی (یعنی دو فروشگاه) انجام میدیم و Sparkline رو رسم میکنیم و رنگ نمودارها رو با توجه به گرافیک مورد نظر تعیین میکنیم. (شکل ۱۹). اگر با Sparkline و چگونگی استفاده ازش آشنا نیستید حتما مقاله مربوط به نمودارهای درون سلولی رو مطالعه کنید.

شکل ۱۹- جدول نمایش روند فروش در ماه های مختلف برای ایالات انتخابی

تبریک! شما تونستید با استفاده از اکسل، یک داشبورد تعاملی جذاب بسازید.

شکل ۲۰- داشبورد تعاملی نهایی

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

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

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

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

مطمئن باشید ایمیل شما فقط در اختیار اکسل پدیا قرار خواهد گرفت و صرفا برای ارسال مطالب مرتبط استفاده خواهد شد.

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

درحال بارگذاری...

توجه: جهت دانلود فایل حتما در حساب کاربری خود وارد شوید.