सामान्य सूत्र
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
सारांश
मानदंड के साथ nth के सबसे बड़े मूल्य का नाम पाने के लिए, आप INDEX और MATCH, LARGE फ़ंक्शन और IF फ़ंक्शन के साथ बनाया गया फ़िल्टर का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, सेल G5 में सूत्र, नीचे कॉपी किया गया है:
=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))
जहाँ नाम (B5: B16), समूह (C5: C16), और स्कोर (D5: D16) को श्रेणी कहा जाता है। यह फॉर्मूला ग्रुप ए में पहली, दूसरी और तीसरी सबसे बड़ी वैल्यू से जुड़ा नाम देता है।
नोट: यह एक सरणी सूत्र है जिसे एक्सेल 365 को छोड़कर नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।
स्पष्टीकरण
LARGE फ़ंक्शन एक सीमा में nth का सबसे बड़ा मूल्य प्राप्त करने का एक आसान तरीका है:
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
इस उदाहरण में, हम उच्चतम स्कोर प्राप्त करने के लिए LARGE फ़ंक्शन का उपयोग कर सकते हैं, फिर INDEX और MATCH के साथ संबंधित नाम को पुनः प्राप्त करने के लिए "कुंजी" जैसे स्कोर का उपयोग कर सकते हैं। ध्यान दें कि हम F, N7, F3: और 3 उच्चतम स्कोर प्राप्त करने के लिए F5: F7 रेंज से n के लिए मान उठा रहे हैं ।
हालांकि इस मामले में ट्विस्ट यह है कि हमें समूह ए और समूह बी में स्कोर के बीच अंतर करना होगा। दूसरे शब्दों में, हमें मापदंड लागू करने की आवश्यकता है। हम IF फ़ंक्शन के साथ ऐसा करते हैं, जिसका उपयोग LARGE के साथ मूल्यांकन करने से पहले मूल्यों को "फ़िल्टर" करने के लिए किया जाता है। एक सामान्य उदाहरण के रूप में, रेंज 2 में सबसे बड़ा मूल्य (यानी 1 मान) प्राप्त करने के लिए जहां रेंज 1 = "ए" है, आप इस तरह एक सूत्र का उपयोग कर सकते हैं:
LARGE(IF(range="A",range2),1)
नोट: इस तरह से IF का उपयोग करने से यह एक सरणी सूत्र बन जाता है।
अंदर से बाहर काम करना, पहला कदम LARGE फ़ंक्शन के साथ समूह ए के साथ जुड़े डेटा में "1" सबसे बड़ा मूल्य प्राप्त करना है:
LARGE(IF(group="A",score),F5)
इस स्थिति में, F5 में मान 1 है, इसलिए हम ग्रुप ए में शीर्ष स्कोर के लिए पूछ रहे हैं। जब IF फ़ंक्शन का मूल्यांकन किया जाता है, तो यह नामित रेंज समूह में प्रत्येक मान का परीक्षण करता है । नामित रेंज स्कोर value_if_true के लिए प्रदान किया गया है। यह एक नया सरणी उत्पन्न करता है, जिसे सीधे LARGE फ़ंक्शन में लौटाया जाता है:
LARGE((79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),1)
फ़िल्टर से बचे एकमात्र स्कोर पर ध्यान दें, ग्रुप ए LARGE से हैं, फिर उच्चतम शेष स्कोर, 93, को सीधे देखने के मूल्य के रूप में MATCH फ़ंक्शन पर लौटाता है। अब हम सूत्र को सरल बना सकते हैं:
=INDEX(name,MATCH(93,IF(group="A",score),0))
अब हम देख सकते हैं कि MATCH फ़ंक्शन उसी फ़िल्टर किए गए सरणी का उपयोग करके कॉन्फ़िगर किया गया है जिसे हमने ऊपर देखा था। IF फ़ंक्शन फिर से अवांछित मानों को फ़िल्टर करता है, और सूत्र का MATCH भाग निम्नानुसार हल करता है:
MATCH(93,(79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE),0)
चूँकि 93 तीसरी स्थिति में दिखाई देते हैं, MATCH 3 सीधे INDEX फ़ंक्शन पर लौटता है:
=INDEX(name,3) // Hannah
अंत में, INDEX फ़ंक्शन तीसरी पंक्ति, "हन्ना" में नाम लौटाता है।
XLOOKUP के साथ
इस समस्या को हल करने के लिए XLOOKUP फ़ंक्शन का उपयोग भी किया जा सकता है, ऊपर बताए गए समान दृष्टिकोण का उपयोग करके:
=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)
ऊपर के रूप में, LARGE को IF द्वारा फ़िल्टर की गई सरणी के साथ काम करने के लिए कॉन्फ़िगर किया गया है, और लुकअप वैल्यू के रूप में 93 से XLOOKUP का परिणाम देता है:
=XLOOKUP(93,IF(group="A",score),name) // Hannah
लुकअप एरे को ग्रुप ए के अंकों पर फिल्टर के रूप में IF के उपयोग से भी बनाया गया है। नाम (B5: B16) के रूप में दिए गए रिटर्न एरे के साथ । अंतिम परिणाम के रूप में XLOOKUP "हन्नाह" लौटाता है।
टिप्पणियाँ
- मानदंडों के साथ nth मान का नाम प्राप्त करने के लिए, (समूह A या B के लिए परिणाम सीमित करें) आपको अतिरिक्त तर्क का उपयोग करने के लिए सूत्र का विस्तार करने की आवश्यकता होगी।
- Excel 365 में, FILTER फ़ंक्शन गतिशील रूप से शीर्ष या निचले परिणामों को सूचीबद्ध करने का एक बेहतर तरीका है। यह दृष्टिकोण स्वचालित रूप से संबंधों को संभाल लेगा।
