किसी सटीक कला-संपत्ति श्रेणी को अलग करने के लिए FILTER का उपयोग करें, फिर उस सबसे बड़ी मात्रा-सीमा का स्तर लौटाने के लिए XLOOKUP का उपयोग करें जो आपकी संख्या से अधिक न हो। इस गाइड में कृत्रिम लेबल और सीमा-परीक्षणों के साथ Mac के लिए Excel का एक पूरा उदाहरण बनाया गया है।

Mac के लिए Excel के किन संस्करणों में यह फ़ॉर्मूला काम करता है?

नीचे दिए गए फ़ॉर्मूले में LET, FILTER, और XLOOKUP का उपयोग किया गया है। Microsoft के दस्तावेज़ों के अनुसार, ये तीनों Mac के लिए Excel for Microsoft 365, Mac के लिए Excel 2024 और Mac के लिए Excel 2021 में उपलब्ध हैं। Microsoft अपने Mac के लिए Excel 2021 में नया क्या है अवलोकन में भी इन फ़ंक्शन की पहचान करता है।

Excel 2016 और Excel 2019 XLOOKUP का मूल्यांकन नहीं कर सकते; Microsoft इस सीमा को स्पष्ट रूप से दर्ज करता है। इस सटीक फ़ॉर्मूले के लिए ऊपर सूचीबद्ध Mac संस्करणों में से किसी एक का उपयोग करें। पुराने संस्करण में इस फ़ंक्शन वाली कार्यपुस्तिका खोलने से उस संस्करण में फ़ंक्शन का समर्थन नहीं जुड़ता। Microsoft का XLOOKUP दस्तावेज़ देखें।

अगर आपको पता नहीं है कि कौन-सा संस्करण इंस्टॉल है, तो एप्लिकेशन के About डायलॉग में उसका संस्करण और लाइसेंस प्रकार देखें। Microsoft बताता है कि Office के बारे में: मैं Office का कौन-सा संस्करण इस्तेमाल कर रहा हूँ? में ये विवरण कैसे पहचानें।

श्रेणी-विशिष्ट न्यूनतम मात्राएँ सेट करें

मान लें कि आप पुनः उपयोग किए जा सकने वाले ग्राफ़िक एसेट की गिनती रखते हैं और हर श्रेणी के लिए एक वर्णनात्मक इन्वेंटरी स्तर चाहते हैं। इस काम के दो नियम हैं: श्रेणी का सटीक मिलान होना चाहिए और मात्रा किसी स्तर की न्यूनतम सीमा तक पहुँचनी चाहिए। नीचे दिए गए सभी लेबल और मात्राएँ केवल उदाहरण हैं।

खाली वर्कशीट में यह तालिका A1:C7 में दर्ज करें। शीर्षक पंक्ति 1 में हैं और छह डेटा पंक्तियाँ 2–7 में हैं। न्यूनतम मात्राएँ संख्याओं के रूप में दर्ज करें और हर श्रेणी तथा स्तर वाले सेल भरें।

श्रेणी न्यूनतम मात्रा स्तर का लेबल
टेक्सचर टाइल 5 कॉम्पैक्ट
टेक्सचर टाइल 20 विस्तृत
टेक्सचर टाइल 50 व्यापक
आकार स्टैम्प 5 कॉम्पैक्ट
आकार स्टैम्प 30 विस्तृत
आकार स्टैम्प 80 व्यापक

इसके बाद, E1 में जाँचने की श्रेणी, F1 में मात्रा, और G1 में इन्वेंटरी स्तर दर्ज करें। E2 में टेक्सचर टाइल और F2 में संख्या 34 दर्ज करें। संदर्भ डेटा और इनपुट को अलग रखने के लिए कॉलम D खाली छोड़ें।

न्यूनतम मात्राओं को शामिल निचली सीमाएँ मानें। टेक्सचर टाइल के लिए कॉम्पैक्ट स्तर 5 से, विस्तृत 20 से, और व्यापक 50 से शुरू होता है। इसलिए, 34 की गिनती विस्तृत स्तर में आती है। आकार स्टैम्प के लिए यही लेबल अलग मात्राओं से शुरू होते हैं: 20 की गिनती अभी भी कॉम्पैक्ट स्तर में आती है।

फ़ॉर्मूला लिखने से पहले तय करें कि आपके अपने सीमा मान न्यूनतम हैं या अधिकतम। इस उदाहरण में हर जगह न्यूनतम का उपयोग किया गया है। इसमें 5 से कम मात्राएँ किसी भी स्तर में नहीं आतीं, जबकि अंतिम स्तर अपनी शुरुआती मात्रा से ऊपर भी जारी रहता है। ये इस नमूने के स्पष्ट डिज़ाइन विकल्प हैं; अगर आपके नियम अलग हैं, तो संदर्भ डेटा को उसी अनुसार बदलें।

पूरा श्रेणी और मात्रा वाला फ़ॉर्मूला दर्ज करें

G2 चुनें, नीचे दिया गया पूरा फ़ॉर्मूला फ़ॉर्मूला बार में चिपकाएँ और Return दबाएँ। यह ऊपर दिए गए सटीक वर्कशीट लेआउट का उपयोग करता है; नामित रेंज या सहायक कॉलम की ज़रूरत नहीं है।

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

टेक्सचर टाइल और 34 के लिए अपेक्षित परिणाम विस्तृत है। बीच की गणनाओं में ऐरे का उपयोग होने के बावजूद फ़ॉर्मूला एक लेबल लौटाता है। आधुनिक डायनेमिक ऐरे फ़ॉर्मूलों को सामान्य रूप से दर्ज किया जाता है; Microsoft डायनेमिक ऐरे फ़ॉर्मूले और स्पिल होने वाले ऐरे का व्यवहार में Enter दबाकर पुष्टि करने का तरीका बताता है।

श्रेणी फ़िल्टर ऐरे को एक साथ कैसे रखता है

अभिव्यक्ति categories=category समावेशन ऐरे बनाती है, जिसका नाम keep है। इस नमूना इनपुट के लिए, यह टेक्सचर टाइल वाली तीन पंक्तियाँ चुनती है। FILTER के दोनों कॉल उसी ऐरे का उपयोग करते हैं, जिससे न्यूनतम मात्राएँ 5, 20 और 50, उनसे संबंधित कॉम्पैक्ट, विस्तृत और व्यापक लेबलों के साथ मिलती हैं।

Microsoft के अनुसार, FILTER एक बूलियन समावेशन ऐरे चुनता है, जिसके आयाम स्रोत रेंज से मेल खाते हैं। दोनों कॉलम के लिए समान समावेशन ऐरे का उपयोग करने से उनका आपसी मेल बना रहता है। FILTER फ़ंक्शन देखें।

बाहरी LET स्रोत रेंज, इनपुट और समावेशन ऐरे को पढ़ने योग्य नाम देता है। ये नाम केवल इसी फ़ॉर्मूले में मौजूद होते हैं; इन्हें कार्यपुस्तिका में अलग से बनाने की ज़रूरत नहीं है। Microsoft अपने LET फ़ंक्शन के दस्तावेज़ में यह व्यवहार समझाता है।

मात्रा स्तर कैसे चुनती है

XLOOKUP का पाँचवाँ तर्क, -1, मात्रा का सटीक मिलान या अगला छोटा मान चुनता है। फ़िल्टर की गई टेक्सचर टाइल पंक्तियों में, 34 के लिए 20 की न्यूनतम मात्रा चुनी जाती है और विस्तृत स्तर लौटता है। 50 की सटीक मात्रा के लिए व्यापक स्तर चुना जाता है। Microsoft XLOOKUP फ़ंक्शन के दस्तावेज़ में इन मोड को परिभाषित करता है।

फ़ॉर्मूला अज्ञात श्रेणी और पहले स्तर से कम गिनती में भी अंतर करता है। SUM(--keep) मेल खाने वाली श्रेणी की पंक्तियाँ गिनता है। अगर ऐसी कोई पंक्ति नहीं है, तो फ़ॉर्मूला फ़िल्टर की गई खोज करने से पहले श्रेणी नहीं मिली लौटाता है। अगर श्रेणी मौजूद है, लेकिन उसकी सभी न्यूनतम मात्राएँ गिनती से अधिक हैं, तो खोज पहले स्तर से कम लौटाती है।

सीमाओं और श्रेणी के अलगाव की जाँच करें

E2 और F2 में नीचे दिए गए युग्म एक-एक करके दर्ज करें। ये जाँच केवल बीच के किसी मान को जाँचने से ज़्यादा उपयोगी हैं: इनमें पहली सीमा, सटीक सीमाएँ, श्रेणियों के अंतर और अनुपलब्ध श्रेणियाँ शामिल हैं।

E2 में श्रेणी F2 में मात्रा अपेक्षित परिणाम
टेक्सचर टाइल 0 पहले स्तर से कम
टेक्सचर टाइल 5 कॉम्पैक्ट
टेक्सचर टाइल 19 कॉम्पैक्ट
टेक्सचर टाइल 20 विस्तृत
टेक्सचर टाइल 34 विस्तृत
टेक्सचर टाइल 50 व्यापक
आकार स्टैम्प 20 कॉम्पैक्ट
आकार स्टैम्प 34 विस्तृत
आकार स्टैम्प 100 व्यापक
कलर स्वैच 34 श्रेणी नहीं मिली

20 की गिनती वाले दोनों परीक्षण विशेष रूप से उपयोगी हैं। टेक्सचर टाइल के लिए विस्तृत, जबकि आकार स्टैम्प के लिए कॉम्पैक्ट लौटना चाहिए। अगर दोनों में विस्तृत लौटता है, तो देखें कि मात्रा की खोज फ़िल्टर की गई श्रेणी की पंक्तियों का उपयोग कर रही है या नहीं।

इन दस अपेक्षित परिणामों को बताए गए न्यूनतम-सीमा नियम को लागू करने वाली एक छोटी Python गणना से जाँचा गया। Excel फ़ॉर्मूले को Microsoft के दस्तावेज़ों में दिए गए सिंटैक्स के अनुसार देखा गया; इसे Excel में चलाया नहीं गया।

इनपुट संदेश भी जाँचें: खाली श्रेणी या टेक्स्ट के रूप में दर्ज मात्रा से श्रेणी और संख्यात्मक मात्रा दर्ज करें लौटना चाहिए; ऋणात्मक संख्यात्मक मात्रा से शून्य या अधिक दर्ज करें लौटना चाहिए। एसेट की गिनती के लिए पूर्णांक इनपुट का उपयोग करें। फ़ॉर्मूला जाँचता है कि मात्राएँ संख्यात्मक और गैर-ऋणात्मक हैं, लेकिन पूर्णांक होने की शर्त लागू नहीं करता।

अनपेक्षित परिणामों की जाँच करें और वर्कशीट का विस्तार करें

इन जाँचों को इसी क्रम में करें, ताकि फ़ॉर्मैटिंग की समस्या के कारण स्तर के नियम न बदल जाएँ।

  1. श्रेणी का टेक्स्ट जाँचें। इनपुट की तुलना संदर्भ लेबलों से करें और स्पेस भी जाँचें। Microsoft अपनी लुकअप समस्या-निवारण मार्गदर्शिका में अनपेक्षित वर्णों और छिपे हुए स्पेस को लुकअप की आम समस्याएँ बताता है।
  2. संख्यात्मक सेल जाँचें। मात्रा वाले इनपुट और न्यूनतम मात्रा में संख्याएँ होनी चाहिए, संख्या जैसे दिखने वाला टेक्स्ट नहीं। Microsoft की वही मार्गदर्शिका डेटा प्रकारों के असंगत होने को लुकअप की समस्या बताती है।
  3. रेंज का संरेखण जाँचें। श्रेणी, न्यूनतम मात्रा और लेबल की रेंज में डेटा की समान पंक्तियाँ शामिल होनी चाहिए। इस उदाहरण में हर रेंज पंक्ति 2 से शुरू होकर पंक्ति 7 पर समाप्त होती है।
  4. डुप्लिकेट नियम जाँचें। हर श्रेणी और न्यूनतम मात्रा के युग्म के लिए एक ही स्तर का लेबल रखें। अगर दो पंक्तियाँ एक ही सीमा के लिए अलग-अलग लेबल देती हैं, तो संदर्भ डेटा में वह अस्पष्टता दूर करें।
  5. सीमा का तरीका जाँचें। अगर परिणाम एक स्तर ऊपर या नीचे दिखता है, तो हो सकता है कि आपके संदर्भ मान अधिकतम हों, जबकि यह फ़ॉर्मूला न्यूनतम मान मानकर चलता है।

दृश्य निरीक्षण के लिए, I2 जैसे किसी खाली सेल में यह डायग्नोस्टिक फ़ॉर्मूला दर्ज करें। E2 में टेक्सचर टाइल होने पर इसे टेक्सचर टाइल की तीनों पूरी पंक्तियाँ दिखानी चाहिए। यह एक अलग डायग्नोस्टिक फ़ॉर्मूला है, G2 का विकल्प नहीं।

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

डायग्नोस्टिक परिणाम को तीन कॉलम में और तीन पंक्तियों तक फैलने की जगह दें। अलग से दर्ज किया गया ऐरे परिणाम पड़ोसी सेल में स्पिल होता है, और भरे हुए सेल के कारण #SPILL! आ सकता है। Microsoft डायनेमिक ऐरे फ़ॉर्मूले और स्पिल होने वाले ऐरे का व्यवहार में यह समझाता है।

संदर्भ पंक्तियाँ जोड़ते समय, मुख्य फ़ॉर्मूले में तीनों स्रोत रेंज को एक साथ बढ़ाएँ। उदाहरण के लिए, अगर अंतिम डेटा पंक्ति 10 हो जाती है, तो परिभाषाएँ बदलकर categories,$A$2:$A$10, minimums,$B$2:$B$10, और labels,$C$2:$C$10 करें। फिर जोड़ी गई श्रेणी के लिए सीमा-परीक्षण दोहराएँ।

कई इनपुट पंक्तियों को प्रोसेस करने के लिए, E2:F2 के नीचे श्रेणी और मात्रा के और युग्म दर्ज करें और G2 को नीचे कॉपी करें। स्रोत रेंज स्थिर रहती हैं, क्योंकि उनके संदर्भों में डॉलर के चिह्न हैं; इनपुट की पंक्ति संख्याएँ आगे बढ़ती हैं। श्रेणी-विशिष्ट सीमाएँ स्पष्ट रखें और जोड़ी वाले श्रेणी परीक्षणों से पुष्टि करें कि लौटाया गया हर लेबल अपेक्षित इन्वेंटरी नियम के अनुसार है।

स्रोत