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

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

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

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

یک داشبورد مدیریتی خوب باید چگونه باشد؟

قانون شماره ۱: داشبورد مدیریتی را از لحاظ ظاهری جذاب کنید.  مدیران و تصمیم گیران می بایست در یک لحظه پیام ها و و نکات کلیدی را دریافت کنند.

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

قانون شماره ۲: داشبورد مدیریتی را فوق العاده پویا کنید. این کار به مدیران کمک میکند تا به سادگی با داشبورد کار کنند و اطلاعات مورد نیاز خود را از آن کشف کنند. قاعدتا شما نمی خواهید در پاسخ به درخواست مدیرتان مبنی بر ارائه اطلاعات بیشتر در مورد محصول، کشور و …. بگویید: «من نمیدانم…»

قانون شماره ۳: به روز رسانی داشبورد ساده باشد.  شما نمی خواهید که برای به روز رسانی داشبورد، هر هفته یا ماه ، ساعت ها و روزها زمان صرف کنید. قاعدتا شما دوست دارید این کار در چشم به هم زدنی انجام شود.درست است؟

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

گام های اولیه: شاخص های کلید عملکرد داشبورد و منابع داده خود را تعریف کنید.

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

برای آشنایی با شاخص های کلیدی عملکرد مطالعه این مطلب را توصیه می کنیم: داشبورد KPI کسب و کار و تعیین شاخص های کلیدی عملکرد

ساختار: داشبورد مدیریتی خود را حول ۴ نوع کاربرگ طراحی کنید.

۴ نوع کاربرگ وجود دارد که در طراحی داشبورد به آن نیاز خواهید داشت.

  1. کاربرگ های منبع داده:

شامل ردیف داده و ستون های محاسبه شده است و فرمت پایگاه داده دارد.

  1. کاربرگ جداول مرجع (REFERENCE)

قانون تبدیل داده های موجود در ردیف ها را به ستون های محاسبه شده به شما ارائه می دهد. (برای مثال جدول تبدیل یورو به دلار ، جدول تبدیل کدپستی به نام شهرها، جدول تبدیل تاریخ شمسی به میلادی و برعکس)

  1. کاربرگ های پیوت تیبل (pivot table)

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

  1. کاربرگ داشبورد

تنها کاربرگی است که نیاز به مصورسازی دارد و همان جایی است که نمودار های شما قرار دارد. (این همان جایی است که افراد می بینند و می گویند «واااااااااااای عجب داشبورد خفنی». در واقع تمام زحماتی که انجام می دهید در این کاربرگ دیده می شود. پس تمام تلاش خود را بکنید که به زیباترین شکل با استفاده از ترکیب رنگ ها و اندازه های مناسب آن را بسازید.

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

داشبورد Pivot table منبع داده جدول مرجع

 

حال به نحوه ساخت هرکدام از این کاربرگ ها برای تولید یک داشبورد مدیریتی فوق العاده می پردازیم.

کاربرگ منبع داده، شامل داده هایی می شود که داشبورد مدیریتی در اکسل را می سازد. عموما، داده های موجود در آن از سیستم های مدیریت ارتباط با مشتری شرکت یا دیگر ابزارهای کسب و کار بدست می آید. البته داشبورد مدیریتی شما نیازمند استفاده از چندین منبع است، میتوانید از چندین کاربرگ منبع داده استفاده کنید.

منابع داده می بایست بعنوان پایگاه داده برای داشبورد مدیریتی ساختاربندی شود

به این معنی که هر ردیف می بایست نشان دهنده یک عنصر خاص باشد (برای مثال یک تراکنش فروش)، و هر ستون نشان دهنده توصیفی از این عنصر باشد (برای مثال مقدار تراکنش، تاریخ انجام تراکنش، سن و یا جنش مشتری و …) اگر داده ردیف شما برگرفته از یک سیستم شرکتی باشد، می بایست قبلا به صورت پایگاه داده ساختار بندی شده باشد.

برای اینکه به قدرت اکسل در ساخت گزارش های پویا پی ببرید و اهمیت ساختار بندی درست داده ها را بدانید این فیلم آموزشی را ببینید: داشبورد حرفه ای در اکسل | نمایش خروجی یک گزارش با استفاده از مدل داده

داشبورد مدیریتی

منبع داده می بایست هم شامل داده های خام و هم شامل ستون های محاسباتی باشد. داده های خام،داده هایی هستند که برای ساخت داشبورد مدیریتی خود از آن استفاده میکنید. ستون های محاسباتی فیلدی اضافی برای محاسبه یک مقدار جدید است. برای مثال، در صورتی که در پایگاه داده خود یک فیلد جنسیت دارید، میتواند مقادیر ۰ و ۱ را برای مرد و زن بگیرد. در این مورد، شما به یک ستون محاسبه نیاز دارید که به ازای ۰، مرد و به ازای ۱، زن را قرار دهد. زیرا مدیران عموما با “زن و مرد” بهتر ارتباط برقرار میکنند تا “۰ و ۱”

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

در کاربرگ منبع داده شما، می بایست پایگاه داده خام خود را در سمت چپ PASTE کنید و در سمت چپ ترین ستون یک ID منحصر به فرد را مورد استفاده قرار دهید. این کار استفاده از VLOOKUP یا INDEX/MATCH را در داشبورد شما آسان تر خواهد کرد.

ستون های محاسباتی همگی می بایست در سمت چپ قرا گیرند.

این ستون ها را در میانه داده های خام خود قرار ندهید. ممکن است وسوسه شوید و ستون “جنسیت” خود را نزدیک ستون داده خام که ۰ و ۱ می گیرد قرار دهید، ولی این ایده خوبی نیست. زمانی که داشبورد خود را به روز رسانی میکنید، ممکن است نیاز باشد که فرمول های مربوط به ستون های محاسبه شده خود را تا آخرین ردیف پایگاه داده خود گسترش دهید (در صورتی که جدول ایجاد نکرده باشید). اگر ستون های محاسبه شده در تمام پایگاه داده پخش شده باشد، زمان بسیار زیادی میگیرد و ممکن است بعضی از ستون ها فراموش شوند و باعث به وجود آمدن آمارهای اشتباهی در داشبورد شود! و اگر در آینده اطلاعات شما دارای ستون های بیشتری باشد، بی نظمی پیش خواهد آمد. بنابراین ستون های محاسبه خود را در گوشه قرار دهید.

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

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

پایگاه داده خود را به فرمت جدول اکسل درآورید

پایگاه داده خود را انتخاب کنید به تب INSERT بروید و بر روی TABLE کلیک کنید. جدول خود را برای مثال به “MY DATA” تغییر نام دهید. چرا از جدول استفاده میکنیم؟

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

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

داشبورد مدیریتی

 از محدوده نام گذاری شده برای مرجع منبع داده خود استفاده کنید.

در اکسل، به پنجره فرمول بروید و بر روی DEFINE NAME کلیک کنید. در فیلد نام،”MyData” را قرار دهید و در فیلد “REFER TO” تایپ کنید

=OFFSET(‘Data Source’!A1;;;COUNTA(‘Data Source’!A:A);COUNTA(‘Data Source’!1:1))

حال شما محدوده ای با نام “MY DATA” دارید که به شما اجازه میدهد PIVOT TABLE  ایجاد کنید بدون اینکه با هر بار اضافه کردن یک ردیف یا ستون نیاز به به روز رسانی داشته باشد.

داشبورد مدیریتی

سایر مطالبی که خواندن آنها را به شما توصیه میکنیم:

اصول طراحی داشبورد مدیریتی اکسل

اصول طراحی ظاهری داشبورد مدیریتی در اکسل

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

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

داشبورد منابع انسانی متریک

جداول مرجع در داشبورد مدیریتی

این کاربرگ به منظور ارائه مرجعی برای داده های خام شما ایجاد می شود.

در جداول مرجع برای داده های خود، جدول های واقعی ایجاد کنید:

مثل منبع داده ،زمانی که یک مرجع را در کاربرگ مرجع خودایجاد میکنید،آن را انتخاب کنید و به INSER/TABLE مراجعه کنید. با این کار، فرمول های LOOK UP در ستونهای محاسبه شده کاربرگ منبع داده، در صورتی که عنصری جدید را به مرجع خود اضافه کنید به صورت اتوماتیک اصلاح خواهد شد.

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

ممکن است واضح به نظربرسد ولی اضافه کردن یا تغییر دادن یک مقدار جدول مرجع بسیار آسان تر از به روز رسانی فرمولهای مختلف در ستون های محاسبه شده است. بنابراین اگر داده های خام شما به صورت EUR می باشد و نیاز به ستونهای محاسبه مختلف برای تبدیل آن به USD داشته باشید،مراجعه به جدول مرجع ارز به شما این امکان را میدهد که بدون تغییر تمام فرمول های خود ، فقط نرخ مبادله را تغییر دهید.

پیوت تیبل (pivot table) در داشبورد مدیریتی

کاربرگ PIVOT TABLE جایی است که داده های شما سازماندهی می شود.این کاربرگ بعنوان یک رابط بین منبع داده و نمودارهای مصور داده عمل میکنند.

یک PIVOT TABLE را از جدول منبع داده خود ایجاد کنید

مطمئن شوید که پیوت تیبل به نام جدولی اشاره میکند که برای منبع داده شما ایجاد شده است ، با این کار انعطاف پذیری بالاتری در مقابل به روز رسانی های آینده خواهید داشت. در زمان انتخاب منبع داده برای پیوت تیبل (pivot table)، بنویسید” MyData” تا به جدولی که قبلا برای منبع داده خود ایجاد کرده اید ارجاع داده شوید.

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

برای ایجاد هر پیوت تیبل (pivot table) دیگری که نیاز داشتید، میتوانید با کپی کردن همین پیوت تیبل (pivot table) آن را ایجاد کنید.

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

به تعداد نمودارهای داشبورد، از پیوت تیبل (pivot table) استفاده کنید (هر نمودار یک پیوت تیبل)

پیوت تیبل (pivot table) ابزاری فوق العاده است. این ابزار میتواند به صورت کلی و در یک لحظه به روز رسانی شود. و به همین دلیل شما برای به روز رسانی سریعتر داشبورد مدیریتی خود به آن نیاز دارید. pivot table موتور محرک داشبورد مدیریتی اکسل شما هستند. مدیر شما آن را نمبیند ولی آنها قسمت ضروری هستند که کل داشبورد را اتوماتیک میکنند.

چند ردیف خالی بین هر پیوت تیبل (pivot table) ایجاد کنید.

در زمان آپدیت شدن، پیوت تیبل (pivot table) به مقداری فضا نیاز دارد و در صورتی که این فضای خالی وجود نداشته باشد، اکسل هشدار خطا خواهد داد. همچنین ما اسلایسر ها را اضافه خواهیم کرد (در آینده در مورد آن توضیح میدهیم)، و اسلایسرها نیز مثل فیلترها به میزانی فضا احتیاج دارند.

داشبورد مدیریتی

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

فقط از پیوت چارت، استفاده کنید.

تمام مزیت استفاده از پیوت تیبل (pivot table) در قسمت قبل این است که در این مرحله از پیوت چارت استفاده کنیم. به کاربرگ پیوت تیبل (pivot table) خود بروید، بر روی PIVOT TABLE کلیک کرده و به پنجره ANALYZE بروید و بر روی PIVOTCHART کلیک کنید. PIVOTCHART نیز رفتاری شبیه به pivot table دارد. آنها به صورت اتوماتیک آپدیت می شوند. بنابراین زمانی که ماه بعد به روز رسانی کنید، نیازی نیست که منبع داده تمام نمودارها را گسترش دهید تا شامل ماه جدید نیز بشود و این کار به صورت اتوماتیک انجام خواهد شد.

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

نمودار خود را مثل هر نمودار کلاسیک دیگری فرمت کنید

به صورت پیش فرض PIVOTCHART با دکمه ها ایجاد می شوند. بر روی آن راست کلیک کنید و ” Hide all field buttons on chart”. را انتخاب کنید. سپس هرگونه که میخواهید به نظر برسد آن را تنظیم کنید. مثل هر نمودار دیگری.

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

 

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

نمودار ها را بعنوان template ذخیره کنید تا نیازی نباشد برای هر نمودار جدید فرمت آن را تنظیم کنید

هر نمودار جدیدی که ایجاد می کنید را بعنوان template ذخیره کنید تا در نمونه های بعدی از آن استفاده کنید. بر روی نمودار راست کلیک کرده و گزینه “Save as Template”را انتخاب کنید. با این کار دیگر نیازی به طراحی دوباره هر نمودار جدید نیست.

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

حال برای هر نموداری که بعدا تولید می کنید، میتوانید به سادگی از template استفاده کنید. برای استفاده مجدد از template به پنجره design بروید. بر روی “Change chart type” کلیک کنید و بر روی دکمه template در سمت راست کلیک کنید.

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

داشبورد خود را بسازید

حال میتوانید به هر تعداد که نیاز دارید نمودار ایجاد کنید. با استفاده از template ذخیره شده آنها را فرمت کنید . قدم به قدم داشبورد خود را ایجاد کنید.  داشبورد خود را جذاب و بدون نیاز به توضیح کنید.

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

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

نمودار خود را انتخاب کنید، به پنجره analyze بروید و بر روی slicer کلیک کنید. فیلدهای مربوطه را انتخاب کنید برای مثال محصول یا منطقه جغرافیایی. حال افراد میتوانند بر روی مقادیر روی Slicer کلیک کرده و نمودار مربوط به آن را بدست آورند. همچنین میتوانید بر روی Slicer راست کلیک کرده و همه آنها را به صورت اصلاح شده توسط کاربر مشاهده کنید.

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

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

در صورتی که با vba آشنا هستید، دکمه هایی را ایجاد کنید که Custom Views را فعال میکند.

شما میتوانید پویایی داشبورد خود را با استفاده از Custom Views فوق العاده بالا ببرید. این مورد در صورتی جالب خواهد بود که تعداد زیادی نمودار داشته باشید و بخواهید که کاربران شما دغدغه جستجو در میان نمودارها را نداشته باشند.

در ابتدا یک view را همراه با تعدادی نمودار وابسته به آن به نام “my view1” انتخاب کنید. سپس برای هر view، ماکرویی با کد زیر ایجاد کنید.

حال میتوانید این ماکرو را به یک شکل اضافه کنید. زمانی که کاربر بر روی شکل کلیک میکند، این ماکرو آن را به view تعریف شده تغییر میدهد.

میتوانید نوعی منو را به همراه شکل هایی که به view های تعریف شده مختلف لینک شده اند، در سمت چپ ایجاد کنید. قاب خود را فریز کنید تا menu شما همیشه قابل مشاهده باشد. حال افراد میتوانند view را با کلیک کردن بر روی منو تغییر دهند.

چارت های خود را به شکلی تنظیم کنید که به آسانی قابل تبدیل به پاورپوینت باشد.

اغلب، داشبورد شما می بایست تبدیل به پاورپوینت شود تا با مدیریت سازمان ، مشتریها و … به اشتراک گذاشته شود. احتمالا با کپی نمودار در پاور پوینت و نیاز به تغییر سایز آن و اتلاف زمان بسیار زیاد، به صورت مکرر مواجه شده اید.

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

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

نمودارها را با استفاده از لینک در پاورپوینت paste کنید.

در صورتی که ارائه ای با استفاده از پاورپوینت دارید و نیاز به به روز رسانی با هر بار refresh کردن داشبورد دارید، میتوانید با استفاده از لینک نمودارهای خود را به پاورپوینت منتقل کنید. یک چارت را در اکسل کپی کنید، به پاورپوینت بروید و paste/paste special را بزنید و لینک را در سمت چپ پنجره انتخاب کنید.

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

حال میتوان با استفاده از یک کلیک راست ساده، از پاورپوینت نمودارها را refresh کرد.

به روز رسانی داشبورد مدیریتی

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

  1. داده های جدید را در جای داده های قدیمی در منبع داده paste کنید
  2. در صورتی که نیاز است جدول مرجع را به روز رسانی کنید
  3. یک pivot table را انتخاب کنید، راست کلیک کرده و refresh را انتخاب کنید
  4. بر روی یک نمودار در پاورپوینت راست کلیک کرده و تمام نمودارهای متصل به آن را به روز رسانی کنید
  5. همین. کل داشبورد و ارائه پاورپوینت در کمتر از یک دقیقه به روز رسانی شد.

حال شما یک داشبورد مدیریتی جالب دارید. افراد میتوانند به سادگی با آن کار کنند و مقادیر مختلف را با استفاده از slicer مشاهده کنند. میتوانید از مزایای داشتن یک داشبورد عالی و منعطف استفاده کنید و آن را در چند ثانیه به روز رسانی کنید.

برگرفته از سایت: Skillpro.ir

نوشتن دیدگاه