Excel इन्वेंटरी लुकअप से सबसे विशिष्ट मेल खाने वाला नियम चुनवाने के लिए, पहले योग्य पंक्तियों की पहचान करें, फिर उनमें निर्दिष्ट फ़ील्ड गिनें। सबसे अधिक स्कोर वाली अकेली पंक्ति से मात्रा लौटाएँ और बराबरी होने पर उसे चिह्नित करें। यह चरण-दर-चरण मार्गदर्शिका Excel for Mac में काल्पनिक कला परिसंपत्तियों के लिए यह परिणाम तैयार करती है।

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

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

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

आपकी इन्वेंटरी में “सबसे विशिष्ट” का क्या अर्थ होना चाहिए?

इस उदाहरण में, आप तीन विशेषताओं—परिसंपत्ति लेबल, फ़िनिश और आकार—के आधार पर किसी कला परिसंपत्ति को दी गई मात्रा खोज रहे हैं। नियम के किसी फ़ील्ड का खाली होना बताता है कि नियम उस विशेषता का कोई भी मान स्वीकार करता है। भरे हुए फ़ील्ड का मान संबंधित इनपुट से मेल खाना चाहिए। योग्य नियमों में से सबसे अधिक भरे हुए फ़ील्ड वाला नियम चुना जाता है।

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

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

काल्पनिक कला परिसंपत्ति नियम सेट अप करें

खाली वर्कशीट में, A1:D6 में ये शीर्षक और मान दर्ज करें। नीचे दी गई तालिका के खाली सेल Excel में भी खाली रहने चाहिए। सभी लेबल और मात्राएँ केवल उदाहरण के लिए हैं।

वर्कशीट पंक्ति A: परिसंपत्ति B: फ़िनिश C: आकार D: मात्रा
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

पंक्ति 2 का नियम जानबूझकर डिफ़ॉल्ट रखा गया है: इसमें कोई विशेषता निर्दिष्ट नहीं है और इसकी विशिष्टता शून्य है। पंक्ति 3 में एक विशेषता, पंक्ति 4 में दो और पंक्ति 5 में तीन विशेषताएँ निर्दिष्ट हैं। ये मात्राएँ एक-दूसरे के विकल्प हैं, इसलिए लुकअप एक मान लौटाता है। यह डिफ़ॉल्ट और अपवाद की मात्राओं को जोड़ता नहीं है।

H1:K1 में Asset, Finish, Size और Result शीर्षक दर्ज करें। फिर H2 में Tile, I2 में Matte और J2 में Small दर्ज करें। तीनों इनपुट विशेषताओं को भरा हुआ रखें। इस डिज़ाइन में खाली सेल केवल नियम तालिका में अप्रतिबंधित शर्तों के रूप में काम करते हैं; अधूरे इनपुट पर संदेश दिखता है।

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

विजेता नियम चुनने वाला फ़ॉर्मूला दर्ज करें

K2 चुनें, यह पूरा फ़ॉर्मूला दर्ज करें और Return दबाएँ। इसमें ऊपर बताई गई नियम तालिका और इनपुट सेल का उपयोग होता है। डायनामिक ऐरे गणनाएँ फ़ॉर्मूले के भीतर होती हैं; अंतिम परिणाम एक सेल में आता है। Microsoft के दस्तावेज़ के अनुसार, डायनामिक ऐरे फ़ॉर्मूले सामान्य तरीके से दर्ज किए जाते हैं, पुराने ऐरे-एंट्री कीबोर्ड शॉर्टकट के बिना। डायनामिक ऐरे फ़ॉर्मूले और स्पिल्ड ऐरे का व्यवहार।

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

दिए गए इनपुट के साथ, K2 को 7 लौटाना चाहिए। चार नियम योग्य हैं, लेकिन केवल पंक्ति 5 का नियम तीनों विशेषताएँ निर्दिष्ट करता है।

योग्यता और विशिष्टता कैसे काम करती हैं

LET रेंज और मध्यवर्ती परिणामों को स्थानीय नाम देता है, जिससे चयन प्रक्रिया को समझना आसान होता है। Microsoft के दस्तावेज़ के अनुसार, ये नाम फ़ॉर्मूले के दायरे में ही उपलब्ध होते हैं। LET फ़ंक्शन।

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

specificity नियम के भरे हुए फ़ील्ड गिनता है। डबल यूनरी ऑपरेटर हर गैर-खाली जाँच को संख्या में बदलते हैं, जिससे शून्य से तीन तक स्कोर मिलते हैं। IF रैंकिंग गणना के लिए अयोग्य पंक्तियों को माइनस एक स्कोर देता है; फिर MAX सबसे अधिक योग्य स्कोर ढूँढ़ता है। माइनस एक से अयोग्य पंक्ति, अप्रतिबंधित डिफ़ॉल्ट के शून्य स्कोर से भी नीचे रहती है। IF फ़ंक्शन, MAX फ़ंक्शन।

winner केवल सबसे अधिक स्कोर वाली योग्य पंक्तियों को चिह्नित करता है। मात्रा लौटाने से पहले फ़ॉर्मूला जाँचता है कि कोई मिलान नहीं है या कई विजेता हैं। इसकी अंतिम संख्यात्मक जाँच जानबूझकर खाली मात्रा या टेक्स्ट के रूप में संग्रहीत संख्या को अस्वीकार करती है। Microsoft के दस्तावेज़ के अनुसार, ISNUMBER टेक्स्ट संख्याओं को संख्यात्मक मानों में नहीं बदलता। संख्यात्मक शून्य मान्य परिणाम रहता है। IS फ़ंक्शन।

वर्कशीट का विस्तार करने से पहले परिणाम जाँचें

H2:J2 को नीचे दिए गए प्रत्येक संयोजन में बदलें। ये नमूना नियमों से निकाले गए अपेक्षित परिणाम हैं, इन्वेंटरी में देखे गए वास्तविक परिणाम नहीं।

परिसंपत्ति फ़िनिश आकार अपेक्षित परिणाम जीतने की शर्त
Tile Matte Small 7 तीन निर्दिष्ट फ़ील्ड
Tile Matte Large 12 परिसंपत्ति और फ़िनिश
Tile Gloss Small 18 केवल परिसंपत्ति
Disc Gloss Large 10 परिसंपत्ति और आकार
Arc Matte Small 30 अप्रतिबंधित डिफ़ॉल्ट

इसके बाद, अस्थायी रूप से पंक्ति 6 को परिसंपत्ति Tile, फ़िनिश Matte, आकार Small और मात्रा 9 में बदलें। मूल इनपुट वापस रखने पर परिणाम Tie: review rules होना चाहिए: अब दो नियम तीनों मिलती हुई विशेषताएँ निर्दिष्ट करते हैं। मात्राएँ एक जैसी होने पर भी बराबरी बनी रहेगी, क्योंकि नियम का चयन अस्पष्ट है। इसके बाद पंक्ति 6 को मूल रूप में लौटाएँ।

कोई मिलान न मिलने वाली शाखा जाँचने के लिए, अस्थायी रूप से फ़ॉर्मूले की चारों नियम रेंज को पंक्ति 3 से शुरू करें, ताकि डिफ़ॉल्ट शामिल न हो। तब इनपुट Arc, Matte, Small से No matching rule लौटना चाहिए। जाँच पूरी होने पर रेंज को पंक्ति 2 से शुरू होने वाली स्थिति में लौटाएँ। मात्रा की जाँच करने के लिए, मूल इनपुट रखते हुए D5 साफ़ करें; Check rule quantity अपेक्षित है, फिर 7 को वापस रखें।

एक-दूसरे पर लागू नियम देखें और बराबरी सुलझाएँ

मौजूदा इनपुट के लिए सभी योग्य नियम देखने हेतु, यह अलग फ़ॉर्मूला M2 में दर्ज करें। पहले M2:P6 खाली रखें। आउटपुट मेल खाने वाली नियम पंक्तियों को उनके मूल क्रम में दिखाता है; यह उनकी रैंकिंग नहीं करता।

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER अपने Boolean inclusion array से चुनी गई पंक्तियाँ लौटाता है, और इसका अंतिम आर्ग्युमेंट तब संदेश देता है जब कोई पंक्ति योग्य न हो। इस निरीक्षण आउटपुट में स्रोत के खाली सेल शून्य के रूप में दिख सकते हैं; मूल नियम तालिका के खाली सेल को अप्रतिबंधित शर्तें ही मानें। FILTER फ़ंक्शन।

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

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

अर्थ बदले बिना लुकअप का विस्तार करें

किसी अन्य परिसंपत्ति के लिए, उसकी तीन विशेषताएँ H3:J3 में दर्ज करें और K2 को K3 में कॉपी करें। इनपुट संदर्भ फ़ॉर्मूले के साथ बदलते हैं, जबकि डॉलर चिह्न नियम रेंज को स्थिर रखते हैं। पंक्ति 6 के आगे नए नियम जोड़ते समय, मुख्य फ़ॉर्मूले की चारों रेंज और निरीक्षण फ़ॉर्मूले के दोनों आयामों को एकसमान ढंग से बढ़ाएँ।

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

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

स्रोत