داشبورد فروش پویا در اکسل؛ از طراحی تا SUMIFS
داشبورد فروش پویا در اکسل یکی از کارآمدترین ابزارهای گزارش‌دهی است؛ چون مدیرعامل، هیئت‌مدیره و سرمایه‌گذاران همه به عدد فروش علاقه دارند. اگر آن را مرتب و زنده ارائه کنید، اعتبار گزارش بالا می‌رود. در این راهنما چهار مرحله‌ی ساخت را می‌بینید: طراحی، ساختار داده، فرمول‌ها و نمودار.
اول ظاهر، بعد موتور
مهم‌ترین قاعده‌ی ساخت داشبورد این است: ابتدا «جلو» را بسازید، بعد «پشت‌صحنه» را. این اصل در مهندسی نرم‌افزار هم رایج است. یعنی پیش از نوشتن حتی یک فرمول، کل داشبورد را با داده‌ی ساختگی طراحی کنید. چیدمان، رنگ‌ها و قلم‌ها را نهایی کنید و بعد سراغ اتصال داده‌ی واقعی بروید.
برای رنگ‌ها لازم نیست با ابزار انتخاب رنگ وقت بگذارید. می‌توانید از طرح موردنظر تصویری بگیرید و از یک دستیار هوش مصنوعی بخواهید کدهای رنگ و خانواده‌ی قلم را بیرون بکشد. نتیجه معمولاً نزدیک است اما کامل نیست؛ پس خروجی را با چشم بررسی کنید.
جزئیاتی که حرفه‌ای‌ها را از تازه‌کارها جدا می‌کند
- گوشه‌های گرد: کادرهای KPI در اکسل گوشه‌تیز هستند. یک مستطیل گوشه‌گرد بدون پرکننده و فقط با خط رنگی روی هر کادر بگذارید. تفاوت ظاهری بسیار بزرگ است.
- اندازه‌ی قلم‌ها: برای نمونه عنوان 34 پوینت و پررنگ، سرفصل بخش‌ها 18 و اعداد KPI حدود 28 پوینت. این اندازه‌ها را قفل کنید تا داشبورد هدفمند دیده شود.
- اجزا را پیش از فرمول قفل کنید: هر قلم، اندازه و رنگ پیش از نخستین فرمول نهایی شود.
ساختار داده: سه چیزی که لازم دارید
پیش از ظاهر شدن اولین عدد واقعی، سه چیز لازم است: خود مقدارها، تاریخ‌هایی که مقدارها را هدایت می‌کنند، و متن پویایی که با تغییر دوره به‌روز می‌شود.
منوی بازشوی دوره
با تابع UNIQUE همه‌ی سال‌های موجود در داده را خودکار بکشید و با VSTACK گزینه‌ی «دوازده ماه اخیر» (TTM) را به انتهای فهرست اضافه کنید. فهرست دستی نیاز نیست و هیچ‌وقت هم قدیمی نمی‌شود.
با تغییر دوره، همه‌چیز عوض می‌شود: عنوان‌ها، اعداد و نمودار. حتی عنوان دوره هم پویاست؛ با ترکیب IF و TEXT می‌توانید برای سال انتخابی بازه‌ی همان سال و برای TTM بازه‌ی دوازده ماه منتهی به آخرین ماه داده را نشان دهید. نه ماکرو لازم است و نه VBA.
فرمول‌ها: SUMIFS و واریانس
وقتی تاریخ‌ها آماده است، فقط باید مقادیر داخل بازه را جمع کنید. SUMIFS با دو شرط کار را تمام می‌کند:
- تاریخ شروع بزرگ‌تر یا مساوی ابتدای دوره،
- تاریخ پایان کوچک‌تر یا مساوی انتهای دوره.
محدوده‌ی درآمد را با علامت دلار قفل کنید تا هنگام کپی جابه‌جا نشود. سپس عدد را با صورت سود و زیان تطبیق دهید؛ اگر یکی بود، فرمول درست است.
برای واریانس: دوره‌ی جاری منهای دوره‌ی قبل. برای واریانس درصدی، فرمول را در IFERROR بپیچید تا وقتی دوره‌ی قبل خالی است، خطای تقسیم بر صفر نبینید. برای فلش بالا و پایین کنار هر KPI هم می‌توان نمادهای پیکان را وارد کرد و با تابع TEXT درصد و مبلغ واریانس را کنار هم نشان داد؛ نتیجه کارت KPI است که کل داستان را در یک خط می‌گوید.
میانگین را با ابزار درست حساب کنید
برای میانگین فروش از AVERAGEIFS استفاده کنید، نه SUMIFS. این اشتباه ساده را به‌آسانی می‌شود نادیده گرفت، اما عدد را کاملاً عوض می‌کند. مثال فرضی: اگر در سه ماه فروش 90، 120 و 150 میلیون ثبت شده، جمع 360 است و میانگین 120؛ اگر اشتباهاً SUMIFS بزنید، عدد 360 را به‌جای میانگین نشان می‌دهید.
نمودار و پرداخت نهایی
داده را انتخاب کنید، نمودار بسازید، تاریخ‌های محور را قالب‌بندی کنید و رنگ نمودار را با طرح داشبورد یکی کنید. نمودار را بدون پرکننده بگذارید و پشت آن همان مستطیل گوشه‌گرد را قرار دهید تا با بقیه‌ی داشبورد هماهنگ شود. حالا دوره را عوض کنید: دوازده ماه اخیر، سال جاری یا سال بعد؛ همه‌چیز زنده است و دست‌کاری لازم نیست.
اگر دنبال انتخاب درست شاخص‌ها هستید، انتخاب شاخص‌ها در داشبورد مالی را ببینید و برای اشتباهات رایج بایدها و نبایدهای داشبورد پایان فصل مفید است. اگر می‌خواهید برای ساخت کمک هوش مصنوعی بگیرید، ساخت داشبورد مالی با هوش مصنوعی را مرور کنید.
پرسش‌های متداول
چرا باید داشبورد را با داده‌ی ساختگی شروع کرد؟
چون وقتی طراحی با فرمول قاطی شود، اصلاح چیدمان پرهزینه می‌شود. با داده‌ی ساختگی، ظاهر را یک‌بار و درست نهایی می‌کنید.
UNIQUE و VSTACK در همه‌ی نسخه‌های اکسل هست؟
این توابع در نسخه‌های جدید Microsoft 365 موجودند. در نسخه‌های قدیمی‌تر باید فهرست دوره‌ها را دستی یا با جدول محوری بسازید.
چرا IFERROR برای واریانس لازم است؟
چون وقتی دوره‌ی قبل خالی یا صفر است، تقسیم خطا می‌دهد و داشبورد ناخوشایند دیده می‌شود.
عدد داشبورد را چگونه راستی‌آزمایی کنم؟
جمع SUMIFS را با گزارش سود و زیان همان دوره مقایسه کنید؛ اگر برابر بود، منطق تاریخ‌ها هم درست است.
جمع‌بندی
داشبورد فروش خوب از طراحی شروع می‌شود، نه از فرمول. اول ظاهر و اندازه‌ها را قفل کنید، بعد با UNIQUE و VSTACK منوی دوره بسازید، با SUMIFS و IFERROR عددها و واریانس را حساب کنید و در پایان نمودار و کادر گوشه‌گرد را اضافه کنید. نتیجه گزارشی زنده است که با یک انتخاب، همه‌ی دوره‌ها را پوشش می‌دهد.