समझिए
नीचे दी गई वर्कबुक डाउनलोड करें। इस अभ्यास में references, text cleanup, dates, Tables और conditional aggregation साथ इस्तेमाल होंगे। डेटा काल्पनिक है। XLOOKUP या dynamic arrays की ज़रूरत नहीं है। 30 मिनट रखें और शुरू करने से पहले एक working copy सेव करें।
1. डेटा देखें और नियम तय करें · 3 मिनट
मैनेजर को जनवरी–मार्च 2026 की region और month के हिसाब से net sales चाहिए। इस export में हर order की सिर्फ़ एक line item है, इसलिए Order ID इस अभ्यास की unique key है। कई items वाले वास्तविक order में order-plus-line key चाहिए होगी।
पहले Read Me, फिर Raw Export देखें। हेडर के अलावा 2,000 data rows हैं। Clean Work में इसकी कॉपी और H:Q में खाली helper columns हैं। Raw Export को audit trail के लिए अपरिवर्तित रखें।
| समस्या | फैसला |
|---|---|
| एक order दोबारा import हुआ | ID साफ़ करके एक row रखें; यहाँ दोहराए गए order का transaction डेटा समान है। |
| बड़े/छोटे अक्षर, spaces और non-breaking spaces | तुलना और grouping से पहले text एक जैसा करें। |
YYYY-MM-DD text dates |
year, month, day से date बनाएँ; पहले से numeric Excel date हो तो वही रखें। |
| संख्या text के रूप में | पहचाना हुआ formatting हटाकर number बनाएँ। |
| Quantity गायब | order को Review रखें और sales KPIs से बाहर रखें; अनुमान से quantity न भरें। |
| Negative quantity | वास्तविक return है; इसे रखें और net sales में घटाएँ। |
| Zero unit price | वैध promotional order है; order और उसकी zero revenue दोनों रखें। |
बदलने से पहले कुछ उदाहरण filter करके देखें। यदि एक ID की दो rows में अलग transaction डेटा मिले तो पहले जाँच करें। इस फ़ाइल में सभी prices मौजूद हैं और dates वैध हैं; नीचे का Status formula सिर्फ़ इसकी missing-quantity समस्या के लिए है।
2. Helper columns से सफ़ाई करें · 9 मिनट
Clean Work की row 2 में ये फ़ॉर्मूले लिखें। H2:Q2 को row 2001 तक भरें। पहली row के बाद H2:Q2001 चुनकर Ctrl+D भी कर सकते हैं।
| कॉलम | हेडर | Row 2 का फ़ॉर्मूला |
|---|---|---|
| H | Clean ID | =UPPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))) |
| I | Clean Date | =IF(ISNUMBER(B2),B2,DATE(VALUE(LEFT(B2,4)),VALUE(MID(B2,6,2)),VALUE(RIGHT(B2,2)))) |
| J | Clean Region | =PROPER(TRIM(CLEAN(SUBSTITUTE(C2,CHAR(160)," ")))) |
| K | Clean Code | =UPPER(TRIM(CLEAN(SUBSTITUTE(D2,CHAR(160)," ")))) |
| L | Clean Qty | =IF(TRIM(E2&"")="","",VALUE(TRIM(E2&""))) |
| M | Clean Price | =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2&"","₹",""),",","")," ","")) |
| N | Clean Rep | =PROPER(TRIM(CLEAN(SUBSTITUTE(G2,CHAR(160)," ")))) |
| O | Status | =IF(L2="","Review","Ready") |
| P | Duplicate | =IF(COUNTIF($H$2:H2,H2)>1,"Duplicate","Keep") |
| Q | Net Sales | =IF(O2="Ready",L2*M2,"") |
TRIM सामान्य spaces हटाता है; पहले CHAR(160) बदलने से इस फ़ाइल के non-breaking spaces भी सँभलते हैं। CLEAN सामान्य nonprinting characters हटाता है। Date formula ISO text को क्षेत्रीय date setting के भरोसे नहीं पढ़ता। Number formula इस export के comma thousands separator और पूर्णांक prices के लिए है।
I को yyyy-mm-dd, L को number और M/Q को currency या दो दशमलव वाले number का format दें। सिर्फ़ format बदलना text को number नहीं बनाता। ISNUMBER(I2) और किसी nonblank L/M सेल पर जाँच करें: TRUE आना चाहिए। Negative quantities का minus न हटाएँ।
3. Values रखें, duplicates हटाएँ और Table बनाएँ · 5 मिनट
- हटाने से पहले P में Duplicate filter करें: 100 repeated rows मिलनी चाहिए। Filter साफ़ करें।
- H2:Q2001 copy करके उसी range में Paste Special → Values करें, ताकि rows हटाने पर नतीजे न बदलें।
- पूरी A1:Q2001 चुनें। Data → Remove Duplicates → headers की पुष्टि → सिर्फ़ Clean ID चुनें। 100 duplicates हटेंगे और 1,900 orders बचेंगे। सिर्फ़ ID column पर अलग से कार्रवाई न करें।
- A1:Q1901 चुनकर Ctrl+T करें, headers की पुष्टि करें और Table Design में नाम Sales रखें। इससे नीचे की खाली rows table में नहीं आएँगी।
- Status = Review filter करें: 20 orders मिलने चाहिए। इन्हें audit के लिए रखें; गायब quantity को zero या average से न भरें। फिर filter साफ़ करें।
1,880 orders Ready हैं। Missing quantity वाली row भी duplicate हो सकती है, इसलिए review rows की गिनती deduplication के बाद करें। अलग-अलग समस्याओं की गिनतियाँ जोड़कर हटाई गई rows न निकालें।
4. Summary block बनाएँ · 8 मिनट
Summary में labels पहले से हैं। ये फ़ॉर्मूले भरें:
| सेल | माप | फ़ॉर्मूला |
|---|---|---|
| B2 | Raw rows | =COUNTA('Raw Export'!A2:A2001) |
| B3 | Unique orders | =COUNTA(Sales[Clean ID]) |
| B4 | Duplicates removed | =B2-B3 |
| B5 | Review orders | =COUNTIF(Sales[Status],"Review") |
| B6 | Ready orders | =COUNTIF(Sales[Status],"Ready") |
| B7 | Net units | =SUMIFS(Sales[Clean Qty],Sales[Status],"Ready") |
| B8 | Net revenue | =SUMIFS(Sales[Net Sales],Sales[Status],"Ready") |
| B9 | Average Ready order | =IFERROR(B8/B6,0) |
| B10 | Return orders | =COUNTIFS(Sales[Status],"Ready",Sales[Clean Qty],"<0") |
| B11 | Zero-price orders | =COUNTIFS(Sales[Status],"Ready",Sales[Clean Price],0) |
E2:E5 में regions हैं। F2 में =COUNTIFS(Sales[Clean Region],E2,Sales[Status],"Ready") और G2 में =SUMIFS(Sales[Net Sales],Sales[Clean Region],E2,Sales[Status],"Ready") लिखकर row 5 तक भरें।
E9:E11 में महीने की शुरुआत की असली dates हैं। F9 में:
=COUNTIFS(Sales[Clean Date],">="&E9,Sales[Clean Date],"<"&DATE(YEAR(E9),MONTH(E9)+1,1),Sales[Status],"Ready")
G9 में:
=SUMIFS(Sales[Net Sales],Sales[Clean Date],">="&E9,Sales[Clean Date],"<"&DATE(YEAR(E9),MONTH(E9)+1,1),Sales[Status],"Ready")
दोनों को row 11 तक भरें। महीने की पहली तारीख शामिल और अगले महीने की पहली तारीख बाहर रखने से time वाली dates भी सही group होती हैं। हर summary में Ready की शर्त है; सिर्फ़ worksheet filter लगाने से SUMIFS के नतीजे नहीं बदलते।
5. हिसाब मिलाएँ और रिपोर्ट दें · 5 मिनट
Clean Answer और Answer Key देखने से पहले ये जाँचें:
- Raw = unique + duplicates: 2,000 = 1,900 + 100।
- Unique = Ready + Review: 1,900 = 1,880 + 20।
- Net units 8,904; net revenue ₹8,783,340; average Ready order ₹4,671.99। सिर्फ़ display round करें, मूल गणना की precision रखें।
- 46 return orders और 19 zero-price orders Ready group में बने रहते हैं।
- चारों regions और तीनों महीनों की revenue अलग-अलग जोड़ने पर दोनों का योग B8 हो; उनकी order counts का योग B6 हो।
| Region | Ready orders | Net revenue |
|---|---|---|
| North | 470 | 2,224,370 |
| South | 470 | 2,134,940 |
| East | 470 | 2,240,950 |
| West | 470 | 2,183,080 |
Summary A15:A16 में दो observations और A18 में unresolved-data note लिखें। उदाहरण: इस sample में East की regional net revenue सबसे अधिक है; 20 orders quantity की पुष्टि तक रिपोर्ट से बाहर हैं। डेटा क्या दिखाता है वह लिखें, बिना सबूत कारण न बनाएँ। Raw Export, साफ़ Table और formula-based summary के साथ workbook सेव करें।
पूरा होने की कसौटी: row counts मिलें, Review rows बची हों, returns और zero prices मौजूद हों, dates/amounts numeric हों, regional/monthly totals मिलें और handover note साफ़ हो। जाँच गलत हो तो rows दोबारा देखें; हर error को IFERROR से न छिपाएँ और expected total सीधे टाइप न करें।