समझिए
ज़रूरत: Microsoft 365 / Excel 2021+ (TAKE/HSTACK: 365/2024)। डेटा: FY कॉलम वाली tblSales।
1. पिवट की जगह फ़ॉर्मूले क्यों?
| Pivots + slicers | Formulas + controls |
|---|---|
| बनाने में तेज़, खोजबीन के लिए बढ़िया | लेआउट और लॉजिक पर पूरा कंट्रोल |
| refresh चाहिए | लाइव, तुरंत |
| pivot chart की सीमाएँ (scatter, waterfall… नहीं) | कोई भी चार्ट टाइप |
| slicer का रूप तय | ड्रॉपडाउन, spin buttons, कोई भी control |
कई प्रोफ़ेशनल डैशबोर्ड दोनों मिलाते हैं।
2. फ़िल्टर के लिए "All" तरकीब
inp_Region = "All" या रीजन का नाम हो तो:
=SUMIFS(tblSales[Revenue], tblSales[FY], inp_FY, tblSales[Region], IF(inp_Region="All", "*", inp_Region))
SUMIFS/COUNTIFS में "*" कोई भी टेक्स्ट मिलाता है। FILTER में ऐसी शर्त इस्तेमाल करें जो All के लिए हमेशा TRUE हो:
((tblSales[Region]=inp_Region) + (inp_Region="All")) > 0
3. Top-N प्रोडक्ट चार्ट — एक फ़ॉर्मूला
CALC!H5 में:
=LET(
p, UNIQUE(tblSales[Product]),
revenues, SUMIFS(tblSales[Revenue], tblSales[Product], p, tblSales[FY], inp_FY,
tblSales[Region], IF(inp_Region="All", "*", inp_Region)),
TAKE(SORTBY(HSTACK(p, ROUND(revenues/10^5, 1)), revenues, -1), inp_TopN)
)
FY2025-26, सारे रीजन, Top 5 (₹ लाख): Laptop 991.0 · Smartphone 754.8 · Smartwatch 210.8 · Earbuds 178.6 · Study Table 160.4।
4. चार्ट के लिए नाम वाली रेंज
Formulas → Name Manager → New:
ch_TopNames = CHOOSECOLS(CALC!$H$5#, 1)
ch_TopValues = CHOOSECOLS(CALC!$H$5#, 2)
खाली bar चार्ट डालें → Select Data → series जोड़ें: Series values ='Sales Dashboard.xlsx'!ch_TopValues; Horizontal (category) labels ='Sales Dashboard.xlsx'!ch_TopNames।
Spin button 5 से 8 करें → spill 8 रो का हो जाता है → चार्ट 8 बार दिखाता है। रेंज एडिट करने की कभी ज़रूरत नहीं।
(Series डायलॉग में नामों से पहले Excel को वर्कबुक का नाम चाहिए; OK दबाने के बाद वह खुद भर देता है।)
5. चुने रीजन का मासिक trend
चुने FY के महीनों की शुरुआत CALC!B20 में (LEFT(RIGHT("FY2025-26",7),4) → 2025, इसलिए Apr-2025 … Mar-2026):
=EDATE(DATE(LEFT(RIGHT(inp_FY,7),4),4,1), SEQUENCE(12,1,0))
मौजूदा साल का revenue C20 में:
=SUMIFS(tblSales[Revenue], tblSales[OrderDate], ">="&B20#, tblSales[OrderDate], "<"&EDATE(B20#,1), tblSales[Region], IF(inp_Region="All","*",inp_Region))
पिछला साल D20 में, लेसन 2 के checkbox से छिपने वाला:
=IF(inp_ShowLY, SUMIFS(tblSales[Revenue], tblSales[OrderDate], ">="&EDATE(B20#,-12), tblSales[OrderDate], "<"&EDATE(B20#,-11), tblSales[Region], IF(inp_Region="All","*",inp_Region)), NA())
C20# और D20# पर (ऊपर की तरह नामों से) line चार्ट मौजूदा साल बनाम पिछला साल दिखाता है; checkbox हटाने पर LY लाइन हट जाती है।
6. फ़िल्टर की हुई विस्तार टेबल
=TAKE(SORTBY(FILTER(tblSales[[OrderDate]:[Revenue]],
(tblSales[FY]=inp_FY) * (((tblSales[Region]=inp_Region)+(inp_Region="All"))>0)),
FILTER(tblSales[Revenue], (tblSales[FY]=inp_FY) * (((tblSales[Region]=inp_Region)+(inp_Region="All"))>0)), -1), 10)
चुनाव के टॉप 10 ऑर्डर — चार्ट के बगल में लाइव "सबसे बड़े सौदे" टेबल। (शर्त दो बार लिखने से बचने के लिए LET इस्तेमाल करें।)
7. स्पीड
50,000 रो पर dynamic arrays जल्दी गणना होते हैं, पर हर बदलाव पर पूरे कॉलम के दर्जनों SUMIFS जुड़ते जाते हैं। हर गणना CALC में एक बार रखें, # रेफ़रेंस से दोबारा इस्तेमाल करें, और volatile functions से बचें (मॉड्यूल 8)।
आम गलतियाँ
चार्ट series को H5:H9 जैसी फ़िक्स रेंज पर रखना (चार्ट नहीं बढ़ता)। "All" संभालना भूलना। नाम और वैल्यू को (& से) एक टेक्स्ट कॉलम में जोड़ देना जिससे चार्ट के पास प्लॉट करने को कोई नंबर न रहे। LET की जगह एक ही फ़िल्टर शर्त कई बार लिखना।