एक फ़ॉर्मूला, कई नतीजे: ऐसी लिस्ट जो नीचे की सेल में स्पिल हो जाती है।
आधुनिक Excel और स्पीड · लाइव स्प्रेडशीट में 5 छोटे अभ्यास: हर फ़ॉर्मूला खुद टाइप करें और ऐसे सवालों के उत्तर दें जिनमें नतीजों का अनुमान लगाना या बग पहचानना हो। हर उत्तर तुरंत जाँचा जाता है।
डायनामिक ऐरे के साथ एक फ़ॉर्मूला कई मान लौटा सकता है, और वे अपने-आप उसके नीचे या बगल की सेलों में भर जाते हैं। इसे स्पिल होना कहते हैं। यह कई पुराने, चरणबद्ध काम, जैसे अलग-अलग मानों की क्रमबद्ध सूची बनाना, एक ही फ़ॉर्मूला में बदल देता है।
एक फ़ॉर्मूला, कई सेल
कल्पना कीजिए एक पशु-आश्रय की सूची: A2:A7 में पशु का प्रकार है (Dog, Cat, Dog, Rabbit, Cat, Dog) और B2:B7 में उम्र (3, 5, 2, 1, 7, 4)। D2 में =UNIQUE(A2:A7) टाइप करें, और Excel D2:D4 को Dog, Cat और Rabbit से भर देता है। आपने केवल एक फ़ॉर्मूला टाइप किया। D2:D4 के चारों ओर का रंगीन बॉर्डर spill range है।
# चिह्न से पूरे स्पिल का हवाला दें: =COUNTA(D2#) अभी जितने भी आइटम हैं उन्हें गिनता है, भले ही डेटा बाद में बदल जाए।
उम्र सबसे बड़ी से सबसे छोटी तक (7, 5, 4, 3, 2, 1)। 3 से बड़ी उम्र वाले पशु (Cat, Cat, Dog)। अलग-अलग पशु प्रकार वर्णमाला क्रम में (Cat, Dog, Rabbit)।
SORT रेंज लेता है, कौन-से कॉलम से सॉर्ट करना है, और बढ़ते क्रम के लिए 1 या घटते क्रम के लिए -1। पूरी टेबल को उम्र से सॉर्ट करने के लिए उसे दोनों कॉलम दें: =SORT(A2:B7,2,-1)। UNIQUE हर मान एक बार लौटाता है। FILTER उन रो को रखता है जहाँ शर्त TRUE है।
दो शर्तों के लिए टेस्ट को गुणा करें, जिसका मतलब है AND: =FILTER(B2:B7,(A2:A7="Dog")*(B2:B7>2)) उम्र 3 और 4 लौटाता है। अगर कुछ भी मेल नहीं खाता, तो FILTER #CALC! देता है। अपना संदेश चुनने के लिए तीसरा आर्ग्युमेंट जोड़ें: =FILTER(A2:A7,B2:B7>10,"none")।
फ़ंक्शनों को जोड़ना
क्योंकि हर फ़ंक्शन एक सूची लौटाता है, आप एक का नतीजा अगले में डाल सकते हैं। फिर से पशु-आश्रय की सूची से शुरू करें (A2:A7 पशु, B2:B7 उम्र)। सिर्फ़ 2 से बड़ी उम्र वाले पशु पाने के लिए, सबसे बड़ी उम्र पहले, दोनों कॉलम दिखाते हुए, फ़िल्टर को सॉर्ट के भीतर रखें:
=SORT(FILTER(A2:B7,B2:B7>2),2,-1)
फ़िल्टर चार रो रखता है (Dog 3, Cat 5, Cat 7, Dog 4)। फिर सॉर्ट उन्हें दूसरे कॉलम से क्रम देता है, सबसे बड़ा पहले: Cat 7, Cat 5, Dog 4, Dog 3।
नेस्टेड फ़ॉर्मूला अंदर से बाहर की ओर पढ़ें: पहले फ़िल्टर, फिर सॉर्ट। अगर लंबी शृंखला समझने में कठिन हो, तो उसे अलग-अलग सेल में चरणों में बनाएँ, और हर चरण सही होने पर उन्हें एक साथ जोड़ें।
#SPILL! एरर
#SPILL! का मतलब है कि जिन सेलों में नतीजा जाना चाहता है, वे खाली नहीं हैं। रुकावट बनने वाली सेल खाली करें, या फ़ॉर्मूला कहीं और ले जाएँ। अगर आप स्पिल होने वाला फ़ॉर्मूला Excel टेबल के भीतर रखते हैं, तो वही एरर आता है, क्योंकि टेबलें स्पिल नहीं रख सकतीं। ऐसे फ़ॉर्मूला टेबल के बगल में रखें।
पुरानी फ़ाइलें और @ चिह्न
पुराने Excel में बनी वर्कबुक में आपको फ़ॉर्मूला के भीतर @ चिह्न दिख सकता है। यह पुराने एक-मान वाले व्यवहार को चिह्नित करता है, जिसे implicit intersection कहते हैं। Excel इसे इसलिए जोड़ता है ताकि फ़ाइल हमेशा की तरह बिल्कुल वही नतीजे देती रहे। नए फ़ॉर्मूला में इसकी आम तौर पर ज़रूरत नहीं होती: फ़ॉर्मूला @ के बिना लिखें और उसे स्पिल होने दें।
सुझाव
डायनामिक ऐरे Excel 2021, Excel 2024 और Microsoft 365 में मौजूद हैं। पुराने संस्करण #NAME? दिखाते हैं।
स्पिल रेंज में टाइप न करें। आप केवल उसके ऊपर-बाएँ सेल का फ़ॉर्मूला संपादित कर सकते हैं। इसके बजाय स्थिर वैल्यू रखने के लिए स्पिल को कॉपी करें और वैल्यू के रूप में पेस्ट करें।
उन्हें मिलाएँ: =COUNTA(UNIQUE(A2:A7)) 3 लौटाता है, यानी अलग-अलग पशु प्रकारों की संख्या।