سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

ساخت داشبورد از صفر تا صد
۲.۳/۵ - (۳ امتیاز)

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

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

دیتابیسی داریم از میزان فروش دو فروشگاه زنجیره ای معروف در استرالیا (فروشگاه 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 Analyze روی گزینه Group Field کلیک کنیم و مطابق شکل ۲ ،نحوه دسته بندی و نمایش تاریخ رو مشخص کنیم. در اینجا ما سال و ماه رو انتخاب کردیم.

گروه بندی داده تاریخ در pivottable

شکل ۲- گروه بندی داده تاریخ در 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 به داشبورد

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

تنظیمات Slicer در ساخت داشبورد

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

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

اتصال Slicer به گزارش های دلخواه

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

نکته:
نمودار نقشه Map نباید به Slicer مربوط به State وصل بشه، پس در ارتباط این Slicer، تیک Pivottable5 رو نمیزنیم که اسلایسر به این نمودار وصل نشه.

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

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

  1. یکی برای نمایش مجموع فروش فروشگاه Nest Look به تفکیک ماه و سال
  2. یکی برای نمایش مجموع فروش فروشگاه Fashion Direct به تفکیک ماه و سال
  3. .یکی هم برای هر دو. شکل ۱۶
نکته:
برای اینکه هر گزارش فقط برای یک فروشگاه ایجاد بشه، Chain رو در فیلتر قرار میدیم و برای هر گزارش فروشگاه مورد نظر رو از قسمت فیلتر انتخاب میکنیم.

ایجاد 3 گزارش برای نمایش روند فروش در فروشگاه ها در هنگام ساخت داشبورد

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

نکته:
گزینه Show Item with no data رو از قسمت Field Setting/ Layout & Print برای این ۳ گزارش فعال میکنیم که همه state ها رو همیشه نشون بده حتی اگه داده براشون وجود نداشته باشه. (دقت کنید که روی یکی از State ها کلیک میکنیم بعد Filed Setting رو میزنیم.

حالا سرستون ها رو مطابق خواسته تایپ میکنیم و در قسمت 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

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

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

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

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

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

داشبورد تعاملی نهایی

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

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

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

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

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

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

آواتار
144

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • رضا ۱ مهر ۱۴۰۲ / ۶:۰۰ ب٫ظ

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

  • شادفر ۲۷ شهریور ۱۴۰۲ / ۵:۳۴ ب٫ظ

    سلام
    درود
    احتراما” آیا امکانش هست که یک جدول اکسل رو بشه در قالب یک تکست باکس بصورت چند ستونی نمایش داد در قالب vba نه در همون صفحه اکسل
    ممنون میشم پاسخ بدید
    تشکر

    • آواتار
      حسنا خاکزاد ۲۸ شهریور ۱۴۰۲ / ۶:۰۵ ب٫ظ

      درود بر شما
      متوجه منطورتون نشدم!

  • محمدرضا مهدی پور9104769864 ۲۳ تیر ۱۴۰۲ / ۶:۱۱ ب٫ظ

    سام . سرکار خانم مهندس .
    ممنون از آموزشهای خوبتون .
    آیا امکانش هست آموزش مدیریت شارژ و مخارج ساختمان هم داشته باشید . به این صورت که هر واحد مبلغی \رداخت کرد در شیت یا جدول خاص خود قرار بگیرد و همچنین مدیریت خرج ساختمان هم داشته باشه ؟
    ممنون

    • آواتار
      حسنا خاکزاد ۲۴ تیر ۱۴۰۲ / ۹:۵۸ ق٫ظ

      درود بر شما
      واقعیتش شخصا تجربشو نداشتم و نمیدونم چه جزئیاتی داره
      ایمیل بزنید به اکسل پدیا و چندتا کارهایی که انجام میشه رو ذکر کنید که بتونیم تبدیلش کنیم به آموزش
      info@excelpedia.net

  • Amin ۳۰ اردیبهشت ۱۴۰۲ / ۱۰:۱۹ ق٫ظ

    Based on your impressive Excel background, I recommend that you start exploring and teaching yourself Power BI. This Microsoft software is incredibly powerful and can effectively aid those who are interested in analyzing data and presenting it in a dashboard format.
    Good luck

  • آواتار
    محمد حسن ۱۰ اردیبهشت ۱۴۰۲ / ۸:۵۴ ق٫ظ

    باسلام
    سرکار خانم خاکزاد چ تفاهمی بین ما هست
    وقتی متن تون رو خوندم شدیدا تعجب کردم چون منم دقیقا سوابق تحصیلیم مث شماست!
    منم ورود ۸۷ بودم تو دانشگاه، ورودی بهمن ماه
    سال ۸۸ منم دقیقا ترم ۲ مهندسی صنایع بودم و ارشدم هم مدیریت صنعتی خوندم!
    البته بنده تو کارشناسی گرایشم تکنولوژی صنعتی و تو ارشد گرایشم OR هست!
    میتونم گرایش شما رو بپرسم؟

    • آواتار
      حسنا خاکزاد ۱۰ اردیبهشت ۱۴۰۲ / ۴:۴۶ ب٫ظ

      درود بر شما هم رشته ای عزیز 🙂
      گرایشم تولید بود، یاد باد آن روزگاران 🙂
      انشاله که سراغ اکسل هم رفتید
      موفق و سلامت باشید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان