स्पिल्ड लुकअप के चारों ओर IFNA का उपयोग करके उसके #N/A परिणामों को दूसरी संदर्भ तालिका से भरें। Mac के लिए Excel का यह उदाहरण आर्ट-एसेट लेबलों का मिलान करता है, प्राथमिक तालिका में पहले से मिली मात्राओं को बनाए रखता है और उन लेबलों को स्पष्ट रूप से चिह्नित करता है जो दोनों तालिकाओं में नहीं हैं।

Mac के लिए Excel के कौन-से संस्करण इस तरीके का समर्थन करते हैं?

Mac के लिए Excel for Microsoft 365, Mac के लिए Excel 2024 या Mac के लिए Excel 2021 का उपयोग करें। Microsoft इन संस्करणों को XLOOKUP और IFNA—दोनों के लिए सूचीबद्ध करता है। इसके XLOOKUP दस्तावेज़ में Excel 2016 और Excel 2019 को स्पष्ट रूप से शामिल नहीं किया गया है, भले ही ये संस्करण पृष्ठ की व्यापक लागूता सूची में दिखाई देते हों। नीचे दिए गए सूत्र समर्थित Mac संस्करणों के लिए हैं। XLOOKUP फ़ंक्शन, IFNA फ़ंक्शन

यह काम विशिष्ट है: आपके पास अनुरोधित एसेट लेबलों का एक कॉलम है और आप हर लेबल के लिए एक मात्रा चाहते हैं। प्राथमिक संदर्भ को प्राथमिकता मिलती है; दूसरा संदर्भ केवल तभी मात्रा देता है जब प्राथमिक लुकअप से #N/A मिलता है। नीचे दिए गए सभी लेबल और मात्राएँ काल्पनिक हैं।

दो संदर्भ तालिकाएँ और अनुरोधित एसेटों की सूची सेट अप करें

एक ही वर्कबुक में खाली वर्कशीट से शुरू करें। नीचे दिए गए सेल स्थानों का उपयोग करें ताकि उनके संदर्भ बदले बिना सूत्र काम करें। ये छोटी संदर्भ तालिकाएँ सामान्य वर्कशीट रेंज हैं; इन्हें Excel Table ऑब्जेक्ट में बदलने की आवश्यकता नहीं है।

प्राथमिक संदर्भ D1:E4 में दर्ज करें:

कॉलम D का सेल एसेट लेबल कॉलम E का सेल मात्रा
D1 एसेट E1 मात्रा
D2 पत्ती की रूपरेखा E2 8
D3 बिंदु टेक्सचर E3 0
D4 टाइल पैटर्न E4 12

दूसरा संदर्भ G1:H5 में दर्ज करें:

कॉलम G का सेल एसेट लेबल कॉलम H का सेल मात्रा
G1 एसेट H1 मात्रा
G2 बिंदु टेक्सचर H2 9
G3 वक्र स्केच H3 5
G4 दानेदार टेक्सचर H4 3
G5 पत्ती की रूपरेखा H5 10

J1 में अनुरोधित एसेट दर्ज करें, फिर इन पाँच लेबलों को J2:J6 में, हर सेल में एक और इसी क्रम में रखें:

  1. पत्ती की रूपरेखा
  2. बिंदु टेक्सचर
  3. वक्र स्केच
  4. दानेदार टेक्सचर
  5. चाप आकृति

मात्राओं को संख्यात्मक रखें और दोनों संदर्भों में मात्रा वाले हर सेल में मान भरें। इस उदाहरण में, शून्य का अर्थ दर्ज की गई मात्रा शून्य है। इसका अर्थ यह नहीं कि एसेट गायब है।

लेबलों की स्पिल्ड सूची बनाएँ

A1, B1 और C1 में एसेट, प्राथमिक मात्रा और पूर्ण मात्रा शीर्षक दर्ज करें। A2:C20 को खाली छोड़ें, फिर यह सूत्र A2 में दर्ज करें:

=FILTER(J2:J20,J2:J20<>"","")

यह अनुरोधित सूची के खाली सेल हटा देता है। दिए गए डेटा के साथ, यह पाँच एसेट लेबलों को A2:A6 में स्पिल करता है। Microsoft ऊपर सूचीबद्ध Mac के तीनों संस्करणों के लिए FILTER का दस्तावेज़ देता है और बताता है कि इसका अंतिम आर्ग्युमेंट उन मामलों के लिए परिणाम देता है जब कोई पंक्ति शर्त पूरी नहीं करती। यहाँ वह परिणाम एक खाली स्ट्रिंग है। FILTER फ़ंक्शन

बाद के सूत्र इस पूरे स्पिल को संदर्भित करने के लिए A2# का उपयोग करते हैं। # ऑपरेटर स्पिल के आकार के अनुसार चलता है, इसलिए यह वर्तमान अंतिम सेल को A6 के रूप में हार्ड-कोड करने से बचाता है। Microsoft इस व्यवहार का वर्णन अपने स्पिल्ड रेंज ऑपरेटर के दस्तावेज़ में करता है।

छूटी हुई मात्राएँ दूसरे संदर्भ से भरें

पहले B2 में प्राथमिक लुकअप बनाएँ:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

यह प्राथमिक एसेट कॉलम में खोजता है और उससे संबंधित मात्राएँ लौटाता है। XLOOKUP डिफ़ॉल्ट रूप से सटीक मिलान करता है और जब कोई मिलान नहीं मिलता तथा कोई वैकल्पिक परिणाम नहीं दिया गया हो, तो #N/A लौटाता है। XLOOKUP फ़ंक्शन

इस नमूने के लिए, B2:B6 में 8, 0, #N/A, #N/A और #N/A होने चाहिए। अगले चरण के लिए मिलान न मिलने वाली इन त्रुटियों को बनाए रखें।

अब पूर्णता वाला सूत्र C2 में दर्ज करें:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA प्राथमिक स्पिल की जाँच करता है। #N/A वाली स्थितियों पर, यह सूत्र दूसरे लुकअप से संबंधित परिणाम देता है। प्राथमिक के अन्य परिणाम बिना बदलाव के आगे भेजे जाते हैं। Microsoft स्पष्ट रूप से दस्तावेज़ देता है कि जब इसका पहला आर्ग्युमेंट कोई ऐरे फ़ॉर्मूला हो, तो IFNA परिणामों का ऐरे लौटाता है। IFNA फ़ंक्शन

अपेक्षित वर्कशीट परिणाम यह है:

कॉलम A में एसेट कॉलम B में प्राथमिक मात्रा कॉलम C में पूर्ण मात्रा
पत्ती की रूपरेखा 8 8
बिंदु टेक्सचर 0 0
वक्र स्केच #N/A 5
दानेदार टेक्सचर #N/A 3
चाप आकृति #N/A सूचीबद्ध नहीं

इन पंक्तियों को प्राथमिकता की जाँच के रूप में देखें। पत्ती की रूपरेखा 8 ही रहती है, जबकि दूसरे संदर्भ में 10 मौजूद है। बिंदु टेक्सचर 0 ही रहता है, जबकि दूसरे संदर्भ में 9 मौजूद है। वक्र स्केच और दानेदार टेक्सचर को उनकी द्वितीयक मात्राएँ मिलती हैं। चाप आकृति का मिलान स्पष्ट रूप से अनसुलझा रहता है, क्योंकि वह किसी भी संदर्भ में मौजूद नहीं है।

दोनों ऐरे में अनुरोधित एसेट एक ही क्रम में होने चाहिए। इस सेटअप में, दोनों A2# से आते हैं। यदि आप किसी मौजूदा वर्कबुक को अनुकूलित करते हैं, तो अलग-अलग क्रम में सॉर्ट किए गए प्राथमिक परिणाम और अलग क्रम वाली लेबल सूची पर आधारित फ़ॉलबैक लुकअप को एक साथ न मिलाएँ।

मध्यवर्ती कॉलम की आवश्यकता न हो तो एक सूत्र का उपयोग करें

प्राथमिक लुकअप और पूर्णता को एक साथ रखने के लिए, C2 के सूत्र को इस पूर्ण विकल्प से बदलें:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

यह वही नमूना डेटा इस्तेमाल करता है और इससे वही पाँच पूर्ण मात्राएँ मिलनी चाहिए। गणना के लिए अब कॉलम B आवश्यक नहीं है। वर्कबुक सेट अप करते समय दो-चरण वाला संस्करण रखने से यह देखना आसान होता है कि हर परिणाम किस संदर्भ से आया है।

तय करें कि कोई परिणाम वास्तव में गायब है या नहीं

सूत्र को अपने अनुसार बदलने से पहले जाँचें कि पहला लुकअप क्या लौटाता है। गायब लेबल, खाली दिखने वाला परिणाम और शून्य मात्रा—इनमें अलग-अलग निर्णय लेने पड़ते हैं।

प्राथमिक परिणाम यह पूर्णता वाला सूत्र क्या करता है क्या जाँचें
संख्यात्मक मात्रा, 0 सहित उसे बनाए रखता है पुष्टि करें कि यह इच्छित प्राथमिक मात्रा है।
#N/A द्वितीयक लुकअप का उपयोग करता है पुष्टि करें कि त्रुटि अनुपस्थित लेबल को दर्शाती है।
खाली स्ट्रिंग, \"\" उसे बनाए रखता है जाँचें कि प्राथमिक सूत्र मिलान न मिलने की स्थिति छिपा तो नहीं रहा।
कोई अन्य त्रुटि उसे दिखाई देता रहने देता है अंतर्निहित सूत्र या संदर्भ ठीक करें।

उदाहरण के लिए, यदि आपका मौजूदा प्राथमिक लुकअप अपने if_not_found आर्ग्युमेंट के रूप में \"\" का उपयोग करता है, तो IFNA उस खाली स्ट्रिंग को #N/A के रूप में पहचान नहीं सकता। प्राथमिक लुकअप से वह आर्ग्युमेंट हटाएँ, जैसा B2 में दिखाया गया है, या ऊपर दिया गया संयुक्त सूत्र इस्तेमाल करें। पूर्णता का तर्क इस बात पर निर्भर करता है कि मिलान न मिलने का पहचाने जाने योग्य परिणाम बना रहे। XLOOKUP फ़ंक्शन, IFNA फ़ंक्शन

संदर्भों में मौजूद मात्राओं की भी जाँच करें। IFNA त्रुटि मान पर प्रतिक्रिया करता है; यह यह जाँच नहीं करता कि मात्रा वाला सेल भरा गया था या नहीं। यदि किसी मिलान किए गए प्राथमिक रिकॉर्ड की मात्रा अधूरी है, तो इस कार्यप्रवाह को लागू करने से पहले वह रिकॉर्ड पूरा करें या अधूरी मात्राओं के लिए अलग नियम तय करें।

एक और अंतर है: प्राथमिक मात्रा डेटा में मौजूद कोई #N/A त्रुटि भी इस फ़ॉलबैक को सक्रिय करेगी। इस उदाहरण के लिए, पूर्वशर्त यह है कि मात्रा वाले कॉलम में साफ़ संख्यात्मक मान हों और #N/A केवल असफल लुकअप से आए। हर प्रतिस्थापन को गायब एसेट मिलान मानने से पहले इस शर्त की जाँच करें।

प्राथमिकता की जाँच करें और स्पिल को चालू रखें

अपने एसेट लेबल बदलने से पहले नमूने की पाँच पंक्तियों को एक छोटी स्वीकृति जाँच के रूप में इस्तेमाल करें। ये मिलकर प्राथमिक मिलान, मान्य शून्य, दो द्वितीयक मिलान और दोनों संदर्भों से अनुपस्थित एक लेबल की जाँच करती हैं।

अतिरिक्त जाँच के लिए, पत्ती की रूपरेखा की द्वितीयक मात्रा को अस्थायी रूप से 10 से बदलकर 14 करें। पूर्ण मात्रा 8 ही रहनी चाहिए। फिर वक्र स्केच की द्वितीयक मात्रा को 5 से बदलकर 6 करें; उसकी पूर्ण मात्रा 6 होनी चाहिए। इसके बाद मूल मान वापस रखें। ये आपकी वर्कबुक के लिए उदाहरणात्मक जाँचें हैं, Excel में किए गए परीक्षण के रिपोर्ट किए गए परिणाम नहीं।

हर संदर्भ में प्रति एसेट लेबल एक ही रिकॉर्ड रखें। XLOOKUP आम तौर पर पहले आइटम से खोजता है और पहला मिलान लौटाता है, इसलिए यह अपेक्षा नहीं करनी चाहिए कि दोहराए गए लेबलों की मात्राएँ जुड़ जाएँगी। इस लुकअप डिज़ाइन पर निर्भर होने से पहले तय करें कि दोहराए गए लेबलों का अर्थ क्या है। XLOOKUP फ़ंक्शन

अवरुद्ध आउटपुट रेंज ठीक करें

हर स्पिलिंग फ़ॉर्मूला केवल उसके शुरुआती सेल में दर्ज करें। Excel पास के सेल अपने आप भरता है और स्पिल का केवल ऊपर-बाएँ सेल संपादित किया जा सकता है। फ़ॉर्मूले Excel Table ऑब्जेक्ट के बाहर रखें; Microsoft स्रोत डेटा के रूप में तालिकाओं का समर्थन करता है, लेकिन उनके भीतर स्पिल्ड फ़ॉर्मूलों का नहीं। डायनेमिक ऐरे फ़ॉर्मूले और स्पिल्ड ऐरे का व्यवहार

यदि आपको #SPILL! दिखे, तो इच्छित आउटपुट सीमा देखने के लिए फ़ॉर्मूला सेल चुनें। जहाँ उपलब्ध हो, त्रुटि अलर्ट का Select Obstructing Cells विकल्प चुनें और परिणाम को रोकने वाली मौजूदा प्रविष्टियों को स्थानांतरित करें। मर्ज किए गए सेल भी स्पिल को रोक सकते हैं; आउटपुट को दूसरी जगह ले जाएँ या प्रभावित सेल अनमर्ज करें। #SPILL! त्रुटि ठीक करने का तरीका

इनपुट सोच-समझकर बढ़ाएँ

अनुरोधित सूची का सूत्र J2:J20 की जाँच करता है। इस रेंज में जोड़े गए लेबल अपने आप शामिल हो जाते हैं; इसके नीचे दर्ज किए गए लेबलों के लिए सूत्र की रेंज बढ़ानी होगी। इसी तरह, संदर्भ लुकअप 4 और 5 पंक्तियों पर समाप्त होने वाली निश्चित रेंजों का उपयोग करते हैं। रिकॉर्ड जोड़ते समय प्रत्येक एसेट रेंज और उससे मेल खाने वाली मात्रा रेंज को साथ-साथ बढ़ाएँ।

बड़ी वर्कशीट में, संरचित संदर्भों वाली Excel Tables पंक्तियाँ जोड़े या हटाए जाने पर स्रोत रेंज को अपने आप समायोजित कर सकती हैं। आउटपुट फ़ॉर्मूले उन तालिकाओं के बाहर वर्कशीट ग्रिड में रखें। डायनेमिक ऐरे फ़ॉर्मूले और स्पिल्ड ऐरे का व्यवहार

इस उदाहरण के इनपुट एक ही वर्कबुक में रखें। Microsoft वर्कबुकों के बीच डायनेमिक ऐरे के सीमित समर्थन का दस्तावेज़ देता है: लिंक किए गए डायनेमिक ऐरे के लिए दोनों वर्कबुक खुली रहनी चाहिए, और स्रोत बंद करने के बाद रीफ़्रेश करने पर #REF! आ सकता है। डायनेमिक ऐरे फ़ॉर्मूले और स्पिल्ड ऐरे का व्यवहार

जब अपेक्षित पंक्तियाँ मेल खाएँ, तो पूर्ण कॉलम को एसेट-मात्रा के परिणाम के रूप में इस्तेमाल करें। प्राथमिकता का नियम बनाए रखें, अर्थपूर्ण शून्य मान सुरक्षित रखें और दोनों संदर्भों से अनुपस्थित लेबलों को सूचीबद्ध नहीं के रूप में चिह्नित छोड़ें, ताकि आप संदर्भ डेटा को सोच-समझकर पूरा कर सकें।

स्रोत