समझिए
उपलब्धता: Microsoft 365, Excel 2021/2024, Excel for the web। (Google Sheets में मिलते-जुलते फ़ंक्शन हैं।) पुराना Excel इन फ़ॉर्मूलों को {=...} array फ़ॉर्मूले या #NAME? दिखाता है।
1. एक फ़ॉर्मूला, कई नतीजे
अभ्यास डेटा: tblSales (16 रो)। H2 में टाइप करें:
=UNIQUE(tblSales[Region])
Enter दबाएँ — H2:H4 में North, South, West दिखते हैं। फ़ॉर्मूला सिर्फ़ H2 में है; H3:H4 spill range हैं (चुनने पर नीला बॉर्डर, फ़ॉर्मूला बार में फ़ॉर्मूला धुंधला)।
सादा गणित भी spill होता है: =tblSales[Qty]*2 16 नतीजे देता है।
2. # operator — पूरे spill को रेफ़र करें
I2 में:
=SUMIFS(tblSales[Amount], tblSales[Region], H2#)
H2# का मतलब "H2 ने जो भी spill किया", अभी 3 सेल। नतीजा: 5,09,000 · 2,59,000 · 3,02,000। डेटा में नया रीजन आए तो H2# बढ़ता है और I2 भी साथ बढ़ता है — फ़ॉर्मूले नीचे कॉपी करने की ज़रूरत नहीं।
H2# को चार्ट (named range के ज़रिए), data validation लिस्ट (=$H$2#), conditional formatting और दूसरे फ़ॉर्मूलों में इस्तेमाल करें।
3. #SPILL! — नतीजे को जगह नहीं मिली
अगर spill एरिया का कोई सेल खाली नहीं, तो #SPILL! आता है। चेतावनी आइकन पर क्लिक → "Select Obstructing Cells", उन्हें साफ़ करें, और spill हो जाएगा।
दूसरे कारण:
- Excel Table के अंदर spill करना (Tables spill arrays नहीं रख सकतीं — dynamic फ़ॉर्मूले Table के बाहर रखें)।
- नतीजा शीट के किनारे से बड़ा।
- रास्ते में merged cells।
4. Implicit intersection और @
पुराना Excel कुछ फ़ॉर्मूलों में रेंज को चुपचाप एक वैल्यू बना देता था। नया Excel वह व्यवहार @ से दिखाता है:
=@tblSales[Amount]
मतलब "उसी रो की एक वैल्यू"। अगर पुरानी फ़ाइल खोलने पर @ जुड़ा दिखे, तो वह पुराना व्यवहार बचा रहा है — आमतौर पर रहने दें।
5. गणना में arrays
=SUM((tblSales[Amount] >= 100000) * tblSales[Amount])
tblSales[Amount] >= 100000 16 TRUE/FALSE वैल्यू बनाता है; × Amount सिर्फ़ बड़े ऑर्डर रखता है → 4,93,000 (4 ऑर्डर)। पूरे कॉलम पर तुलना और गणित फ़ॉर्मूले के अंदर arrays लौटाते हैं — अब Ctrl + Shift + Enter की ज़रूरत नहीं।
6. अच्छी आदतें
- Dynamic फ़ॉर्मूलों के नीचे/दाएँ खाली जगह छोड़ें।
- हर spill के ऊपर हेडर रखें और उसे
#से रेफ़र करें। - Spill एरिया के अंदर वैल्यू टाइप न करें।
आम गलतियाँ
Spill एरिया में कुछ टाइप होना (#SPILL!)। Table के अंदर dynamic फ़ॉर्मूला लिखना। H2# की जगह H2:H4 रेफ़र करना (लिस्ट बढ़ने पर टूटता है)।