सामान्य सूत्र
=INDEX(names,MATCH(LARGE(values,F5),values,0))
सारांश
Nth के सबसे बड़े मूल्य का नाम पाने के लिए, आप लार्स फ़ंक्शन के साथ INDEX और MATCH का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, सेल H5 का सूत्र है:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
जहां नाम (B5: B16), और स्कोर (D5: D16) का नाम रेंज है।
स्पष्टीकरण
संक्षेप में, यह सूत्र डेटा के एक सेट में nth के सबसे बड़े मूल्य को खोजने के लिए LARGE फ़ंक्शन का उपयोग करता है। एक बार जब हमारे पास वह मूल्य होता है, तो हम संबंधित नाम को पुनः प्राप्त करने के लिए इसे मानक INDEX और MATCH फॉर्मूला में प्लग करते हैं। दूसरे शब्दों में, हम संबंधित जानकारी को पुनः प्राप्त करने के लिए "कुंजी" की तरह nth सबसे बड़े मूल्य का उपयोग करते हैं।
LARGE फ़ंक्शन एक सीमा में nth का सबसे बड़ा मूल्य प्राप्त करने का एक सीधा तरीका है। पहले तर्क के लिए एक सीमा प्रदान करें (सरणी), और दूसरे तर्क के रूप में n के लिए मान (के):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
अंदर से बाहर काम करना, पहला कदम LARGE फ़ंक्शन के साथ डेटा में "1" सबसे बड़ा मूल्य प्राप्त करना है:
LARGE(score,F5) // returns 93
इस स्थिति में, F5 का मान 1 है, इसलिए हम 1 सबसे बड़ा स्कोर (यानी शीर्ष स्कोर) मांग रहे हैं, जो कि 93 है। अब हम सूत्र को सरल बना सकते हैं:
=INDEX(name,MATCH(93,score,0))
INDEX फ़ंक्शन के अंदर, MATCH फ़ंक्शन को नामांकित श्रेणी स्कोर (D5: D16) में 93 की स्थिति का पता लगाने के लिए सेट किया गया है :
MATCH(93,score,0) // returns 3
चूँकि 93 तीसरी पंक्ति में दिखाई देते हैं, MATCH 3 को INDEX को पंक्ति संख्या के रूप में वापस लौटाता है, नाम के साथ सरणी:
=INDEX(name,3) // Hannah
अंत में, INDEX फ़ंक्शन तीसरी पंक्ति, "हन्ना" में नाम लौटाता है।
ध्यान दें कि हम 1, 2, और 3 के उच्चतम अंक प्राप्त करने के लिए F5: F7: F7 श्रेणी से n के लिए मानों को उठा रहे हैं क्योंकि सूत्र की प्रतिलिपि बनाई गई है।
पुनः प्राप्त समूह
वही मूल सूत्र किसी भी संबंधित जानकारी को पुनः प्राप्त करने के लिए काम करेगा। सबसे बड़े मूल्यों के लिए समूह प्राप्त करने के लिए, आप केवल नामांकित श्रेणी समूह के साथ INDEX को दिए गए सरणी को बदल सकते हैं :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
F5 में मान 1 के साथ, LARGE को उच्चतम स्कोर मिलेगा, और सूत्र "ए" लौटाएगा।
नोट: Excel 365 के साथ, आप गतिशील रूप से शीर्ष या निचले परिणामों को सूचीबद्ध करने के लिए FILTER फ़ंक्शन का उपयोग कर सकते हैं।
XLOOKUP के साथ
XLOOKUP फ़ंक्शन का उपयोग इस तरह nth के सबसे बड़े मूल्य के नाम को वापस करने के लिए भी किया जा सकता है:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE लुकअप वैल्यू के रूप में XLOOKUP से सीधे सबसे बड़ा मूल्य, 93, लौटाता है:
=XLOOKUP(93,score,name) // Hannah
लुकअप ऐरे के रूप में नामित रेंज स्कोर (D5: D16) और रिटर्न ऐरे के रूप में नाम (B5: B16) के साथ, XLOOKUP पहले की तरह "हन्ना" देता है।
संबंधों को संभालना
संख्यात्मक डेटा में डुप्लिकेट मान एक "टाई" बनाएंगे। यदि मानों को रैंक किए जा रहे मूल्यों में एक टाई होती है, उदाहरण के लिए, यदि पहला और दूसरा सबसे बड़ा मान समान हैं, तो LARGE प्रत्येक के लिए समान मान लौटाएगा। जब यह मान MATCH फ़ंक्शन में पास हो जाता है, तो MATCH पहले मैच की स्थिति वापस कर देगा, इसलिए आपको वही (पहला नाम) नाम दिखाई देगा।
यदि संबंधों की संभावना है, तो आप कुछ प्रकार की टाई-ब्रेकिंग रणनीति को लागू करना चाह सकते हैं। एक दृष्टिकोण उन मूल्यों का एक नया सहायक स्तंभ बनाना है जो संबंधों को तोड़ने के लिए समायोजित किए गए हैं। फिर जानकारी को रैंक और पुनः प्राप्त करने के लिए सहायक स्तंभ मानों का उपयोग करें। यह संबंधों को स्पष्ट और स्पष्ट तोड़ने के लिए प्रयुक्त तर्क बनाता है।
एक अन्य दृष्टिकोण केवल स्थिति के आधार पर संबंधों को तोड़ना है (यानी पहला टाई "जीत")। यहाँ एक सूत्र है जो उस दृष्टिकोण को लेता है:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
नोट: यह एक सरणी सूत्र है और इसे Excel 365 को छोड़कर नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।
यहां, हम नंबर 1 को खोजने के लिए MATCH का उपयोग करते हैं, और हम बूलियन लॉजिक का उपयोग करके लुकअप सरणी का निर्माण करते हैं जो (1) LARGE द्वारा लौटाए गए मान के सभी स्कोर की तुलना करता है:
score=LARGE(score,F5)
और (2) यदि नाम पहले से ही सूचीबद्ध सूची में है, तो एक विस्तार श्रेणी की जांच का उपयोग करता है:
COUNTIF(H$4:H4,name)=0
जब कोई नाम पहले से ही सूची में है, तो यह तर्क द्वारा "रद्द" किया जाता है, और अगले (डुप्लिकेट) मान का मिलान किया जाता है। एक विस्तृत संदर्भ से बचने के लिए, विस्तार की सीमा पूर्व पंक्ति पर शुरू होती है।
यह दृष्टिकोण इस उदाहरण में काम करता है क्योंकि नाम कॉलम में कोई डुप्लिकेट नाम नहीं हैं। हालांकि, यदि डुप्लिकेट नाम रैंक किए गए मानों में होते हैं, तो दृष्टिकोण को समायोजित करने की आवश्यकता है। सबसे आसान समाधान यह सुनिश्चित करना है कि नाम अद्वितीय हैं।
टिप्पणियाँ
- मानदंडों के साथ nth मान का नाम प्राप्त करने के लिए, (समूह A या B के लिए परिणाम सीमित करें) आपको अतिरिक्त तर्क का उपयोग करने के लिए सूत्र का विस्तार करने की आवश्यकता होगी।
- Excel 365 में, FILTER फ़ंक्शन गतिशील रूप से शीर्ष या निचले परिणामों को सूचीबद्ध करने का एक बेहतर तरीका है। यह दृष्टिकोण स्वचालित रूप से संबंधों को संभाल लेगा।
