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

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

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

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

فیلم آموزش داشبورد مدیریتی اکسل و گزارش گری حرفه ای در Excel در فرادرس

کلیک کنید

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

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

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

• چرا به داشبورد نیاز دارید؟
• به چه هدفی می‌خواهید از داشبورد استفاده کنید؟
• داده‌ها از چه منابعی در دسترس شما قرار می‌گیرند؟
• چه امکاناتی را احتیاج دارید و چه امکاناتی مورد نیاز شما نیستند؟

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

چرا این داشبورد را ایجاد می‌کنید؟

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

آیا می‌خواهید KPIهای خاصی را ردیابی کنید؟

داشبورد شما فقط باید داده‌هایی را که ارزش افزوده دارند برجسته کند. حتماً موثر‌ترین و مهمترین شاخص‌های کلیدی عملکرد (KPIها) را شناسایی کرده و داشبورد را برمبنای آن‌ها ایجاد کنید. هر چیزی خارج از KPIهای اصلی باشد، احتیاجی به قرارگیری در داشبورد ندارد.

چه کسی باید داشبورد را ببیند؟

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

داده‌ها از کجا استخراج خواهند شد؟

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

داشبورد چگونه به روز رسانی می‌شود؟

آیا لازم است اطلاعات داشبورد خود را هفتگی یا ماهانه به روز کنید یا همیشه باید اطلاعات جاری را نمایش دهید؟ پاسخ به این سوال می‌تواند روش ساخت داشبورد را تغییر دهد.

در چه قالبی باید داشبورد را طراحی کنید؟

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

نحوه طراحی داشبورد

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

فیلم آموزش ساخت داشبورد مدیریتی در گوگل شیت در فرادرس

کلیک کنید

گزینه‌ها یا مولفه‌های داشبورد

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

رنگ‌ها در طراحی داشبورد

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

تقویت رابط کاربری داشبورد

سهولت استفاده از داشبورد برایتان چقدر مهم است؟ آیا می‌خواهید برای ارتقاء رابط کاربری (User Interface) داشبورد وقت بگذارید؟ برای مرور آسان‌تر اطلاعات در داشبورد بهتر است اطلاعات را به صورت سلسله مراتبی (Hierarchical) ارائه کنید. به کارگیری لیست‌های کشویی این امر را تسهیل می‌بخشند. با استفاده از اشیاء در فرم‌های اکسل، برچسب‌های نمودار را به صورت خودکار اضافه کنید یا به کمک ثابت کردن سطر یا ستون (Freeze Pane)، سطرهایی از کاربرگ اکسل، گزینه‌های ثابت مانند عناوین ستون‌ها را همیشه در قسمت بالایی داشبورد صفحه نمایش دهید.

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

در ادامه با طی کردن گام‌هایی یک داشبورد مدیریتی ایجاد خواهیم کرد. مراحل توضیح داده شده را با دقت و اطمینان جلو ببرید تا یک داشبورد موثر و زیبا ایجاد کنید.

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

کلیک کنید

۱- ورود داده‌ها به اکسل

قبل از ایجاد داشبورد، باید داده‌ها را در اکسل وارد کنید. ممکن است این کار توسط عملیات کپی و چسباندن (Copy and Paste) صورت گیرد. ولی اگر داده‌ها در یک بانک اطلاعاتی هستند، بهترین راه استفاده از ODBC‌ یا (Open Database Connectivity) است. رابط ODBC یک رابط برنامه‌نویسی کاربردی (API) است که امکان اتصال و دریافت و ثبت اطلاعات را روی بانک‌های اطلاعاتی فراهم می‌سازد. به این ترتیب ODBC می‌تواند برنامه‌ها و بانک‌های اطلاعاتی شما را به اکسل متصل و داده‌های جاری را به آن منتقل کند. به این ترتیب به روزرسانی داده‌ها در بانک‌های اطلاعاتی، خروجی‌ داشبوردهای مدیریتی در اکسل را به روز می‌کند و شما نیز آخرین اطلاعات و تحلیل‌ها را در داشبورد مشاهده می‌کنید.

اگر داده‌ها را در مکانی دیگر دریافت یا ذخیره می‌کنید، باز هم اکسل گزینه مناسبی است. داده‌ها را می‌توان به دو شیوه مختلف وارد اکسل کرد: توسط یک پرونده یا یک جدول محوری (Pivot Table). اکسل ابزارهای متعددی برای دریافت داده‌ها از منابع خارجی دارد. حتی افزونه‌های متعددی برای اکسل به این منظور ایجاد شده است.

۲- ایجاد پرونده داشبورد اکسل

پس از افزودن داده، باید کارپوشه یا کتاب‌کار (Workbook) خود را تنظیم کنید. یک کارپوشه جدید در اکسل بسازید و دو یا سه کاربرگ (Worksheet) ایجاد کنید. می‌توانید یک کاربرگ برای داشبورد (Dashboard) و یک کاربرگ برای داده‌های خام (Raw Data) در نظر بگیرید.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

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

۳- ایجاد یک کاربرگ برای داده‌های خام

در کاربرگ داده‌های خام، اطلاعات خود را وارد یا کپی کنید. اطمینان حاصل کنید که اطلاعات در قالب جدول‌ در اکسل وارد شده‌اند. این بدان معنی است که هر مورد (Case- Record) در یک سطر و اقلام اطلاعاتی (FIeld) آن به صورت در یک سلول و به صورت ستونی قرار گیرند.

در مثالی که ارائه خواهیم داد، ستون‌هایی را برای «نام پروژه» (Project Name)، «جدول زمانی» (Start Date, End Date, Duration)، «تعداد اعضای تیم» (of Team Members)، بودجه (Project Budget)، ریسک‌ها (Risk High, Medium, Low) وظایف (Issues) و اقدامات انجام نشده (Pending Actions) در نظر گرفته‌ایم و داده‌های نیز به صورت نمونه ثبت کرده‌ایم. همانطور که در تصویر زیر می‌بینید، جمع هر یک از ستون‌های عددی نیز به صورت پررنگ (Bold) محاسبه و نمایش داده شده است.

در صورت نیاز می‌توانید از فرمولی برای محاسبات خودکار جمع کمک بگیرد. ما این کار را برای ستون‌های بودجه، ریسک‌ها، و ... انجام داده‌ایم. برای انجام این کار، روی یک سلول خالی در انتهای ستون مورد نظر کلیک کرده و )SUM= را تایپ کنید. بعد از علامت پرانتز، روی اولین سلول موجود در ستون کلیک کنید و موس را به سمت آخرین سلول بکشید و سپس پرانتز را ببندید. آدرس نواحی مورد نظر در تابع SUM نوشته خواهد شد. با زدن کلید Enter، فرمول ثبت و نتیجه درون سلول دیده خواهد شد. در صورت لزوم این کار را برای ستون‌های دیگر نیز تکرار کنید.

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

۴- تحلیل داده‌ها

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

• توابع اکسل مانند SUMIFS ،OFFSET ،COUNT ،VLOOKUP ،GETPIVOTDATA و ...
• جدول محوری (PIVOT TABLES)
• جدول‌های اکسل (EXCEL TABLES)
• اعتبار سنجی داده‌ها (DATA VALIDATION)
• شکل‌ها و ترسیم‌ها (AUTO-SHAPES)
• نواحی نام‌گذاری شده (NAMED RANGES)
• قالب‌بندی شرطی (CONDITIONAL FORMATTING)
• نمودارها (CHARTS)
• ماکرو (MACROS)
• ...

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

۵- ساخت داشبورد

برای ارائه و نمایش اطلاعات محاسبه شده، نمودارها ابزار بسیار مناسبی در اکسل هستند. در ادامه با توجه به تاریخ اجرای هر پروژه (Start- End- Duration) یک نمودار گانت (Gantt Chart) ایجاد خواهیم کرد. مراحل زیر را برای ایجاد چنین نموداری طی خواهیم کرد.

رسم نمودار گانت

1. به کاربرگ Dashboard وارد شوید از قاب Insert گزینه Chart و سپس نمودار دوم از بخش 2D-Bar را انتخاب کنید.
2. حال نام پروژه (Project Name)، تاریخ شروع (Start Date) و طول پروژه (Duration) را برای ترسیم نمودار به عنوان داده‌های خام مشخص کنید.

با انتخاب این نواحی برای ترسیم نمودار ستونی، نموداری مطابق با تصویر زیر ترسیم خواهد شد. محور افقی، زمان و محور عمودی نیز اسامی پروژه‌ها را نشان می‌دهد. طول هر یک از میله‌ها براساس زمان اتمام پروژه (Duration) مشخص شده است.

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

نکته: توجه داشته باشید که داده‌های اصلی و خام برای ترسیم چنین نموداری در کاربرگ Raw Data قرار گرفته است. به همین علت در قسمت Chart data range نام این کاربرگ به چشم می‌خورد.

برای قالب‌بندی نمودار و تغییر رنگ‌ها بهتر است از برگه Design مربوط به نمودار مطابق با تصویر زیر تغییراتی بوجود آورید. با این کار جذابیت بیشتری به داشبورد مدیریتی در اکسل می‌بخشید.

با این کار عنوان نمودار در بالای صفحه قرار خواهد گرفت. حال عنوان را با کلیک روی قاب متنی، وارد کنید. این کار را می‌توانید برای قسمت‌های دیگر نمودار که قابلیت نمایش عنوان (Title) را دارد انجام دهید.

ایجاد جدول محوری (PivotTables)

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

مراحل انجام این کار را طبق فهرست زیر ارائه می‌کنیم.

1. کاربرگ Dashboard را فعال کنید و از برگه Insert دکمه PivotTable را کلیک کنید.
2. پنجره‌ای با نام Create PivotTable ظاهر می‌شود که باید در کادر Table/Range ناحیه‌ای که مربوط به جدول اطلاعاتی است، مشخص شود. همانطور که در تصویر می‌بینید، ناحیه A2 تا N16 مشخص شده است.
3. فیلدها یا متغیرهایی که باید در سطر یا ستون جدول محوری قرار گیرند را تنظیم کنید.

نتیجه اجرای این دستور به صورت زیر خواهد بود.

برای مثال ما می‌توانیم در این مرحله از ساخت داشبورد مدیریتی در اکسل نام پروژه را در کادر Rows و Risk High تا Risk Low را در کادر Values قرار دهیم. به این ترتیب جدولی به مانند تصویر زیر ایجاد خواهد شد که جمع‌بندی را برای مقادیر ریسک نمایش می‌دهد.

رنگ‌آمیزی و قالب‌بندی

با استفاده از قالب‌بندی شرطی (Conditional Formatting) یا الگوهای جدول (Styles) در اکسل به داشبورد خود زیبایی و صد البته، خوانایی ببخشید. همچنین با تبدیل اطلاعات در قالب جدول (Format as Table) امکاناتی مانند فیلتر کردن نیز به مجموعه ابزارهای کاربردی داشبوردتان اضافه می‌شود. هر چه بر سادگی و خوانایی نمودارها و جدول‌ها اضافه کنید، کاربر را در پیدا کردن اقلام کلیدی (Key Performance) یاری داده‌اید. این امر را همیشه در نظر بگیرید که از نمایش گزینه‌های زیاد و غیر ضروری بپرهیزید و نتایجی که باید توجه کاربر را به خود جلب کنند، جذاب طراحی کنید.

خلاصه و جمع‌بندی

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

فیلم آموزش داشبوردهای مدیریتی اکسل (Excel)

اگر می‌خواهید در زمینه ایجاد داشبورد مدیریتی در اکسل اطلاعات بیشتری کسب کنید فیلم آموزش داشبوردهای مدیریتی اکسل (Excel) را ملاحظه کنید. عناوین اصلی در این آموزش در ادامه فهرست شده‌اند.

• درس اول، فرمول نویسی پیشرفته در اکسل: که شامل نحوه فرمول نویسی و آشنایی با توابع اکسل است. یکی از نکات مهم در این بخش، فرمول نویسی برداری است که کمتر در آموزش‌های اکسل مطرح شده است.
• درس دوم، کاندیشنال فرمتینگ (Conditional Formatting): این بخش به قالب‌بندی شرطی می‌پردازد. این موضوع بخصوص زمانی اهمیت دارد که بتوانیم به کمک فرمول‌نویسی و گزاره‌های شرطی، قالب رنگ، زمینه یک یا چندین سلول را تعیین کنیم.
• درس سوم، آموزش نمودارها و تکنیک‌های رسم آن‌ها: آشنایی با انواع نمودارها و حتی سفارشی‌سازی نمودارها در این بخش مورد بحث قرار گرفته است.
• درس چهارم، داشبوردها: این بخش ویژگی‌های یک داشبورد مدیریتی در اکسل را بازگو می‌کند و به چند داشبورد مهم از جمله داشبورد فروش می‌پردازد.
• درس پنجم، آموزش Pivot Tables: همانطور که در متن نیز اشاره شد، جدول محوری یا Pivot Table ابزاری برای جمع‌بندی و محاسبات روی گروهی از داده‌ها است. به این ترتیب گزارشاتی که نیاز به محاسبه شاخص‌های آماری نظیر میانگین و واریانس دارند به راحتی تولید می‌شوند.
• درس ششم، شاخص کلیدی عملکرد ‌(KPI): در این بخش برای نمایش شاخص کلیدی عملکرد، از نمودارهایی مانند نمودار درجه‌ای (Gauge Chart) که توسط مدرس طراحی شده است، آموزش داده می‌شود.
• درس هفتم، پیاده سازی سه داشبورد به صورت کامل

فیلم آموزش داشبوردهای مدیریتی اکسل Excel در فرادرس

کلیک کنید

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

• مجموعه آموزش‌های نرم‌افزار مایکروسافت اکسل
• آموزش داشبوردهای مدیریتی اکسل (Excel)
• مجموعه آموزش‌های مدیریت
• ایجاد و رسم نمودار در اکسل – راهنمای کاربردی
• شاخص کلیدی عملکرد در داشبوردهای مدیریتی — به زبان ساده

^^