सामान्य सूत्र
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
सारांश
श्रेणी मिलान मानदंडों में शीर्ष n मानों को जोड़ने के लिए, आप SUMPRODUCT फ़ंक्शन के अंदर लिपटे LARGE फ़ंक्शन के आधार पर एक सूत्र का उपयोग कर सकते हैं। सूत्र के सामान्य रूप में (ऊपर), श्रेणी मानों की तुलना में कोशिकाओं की एक श्रृंखला का प्रतिनिधित्व करती है , मान उन संख्यात्मक मानों का प्रतिनिधित्व करते हैं जिनसे शीर्ष मान प्राप्त होते हैं, और N , Nth मान के विचार का प्रतिनिधित्व करता है।
उदाहरण में, सक्रिय सेल में यह सूत्र है:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
रंग का नाम B5: B12 है और मान का नाम C5: C12 है।
स्पष्टीकरण
अपने सरलतम रूप में, LARGE इस निर्माण के साथ एक सीमा में "Nth सबसे बड़ा" मान लौटाता है:
=LARGE (range,N)
इसलिए, उदाहरण के लिए:
=LARGE (C5:C12,2)
श्रेणी C5: C12 में दूसरा सबसे बड़ा मान लौटाएगा, जो दिखाए गए उदाहरण में 12 है।
हालाँकि, यदि आप एक "सरणी स्थिरांक" (उदाहरण के लिए (1,2,3) में एक स्थिरांक) को दूसरे तर्क के रूप में LARGE को आपूर्ति करते हैं, तो LARGE एक परिणाम के बजाय परिणाम की एक सरणी लौटाएगा। तो, सूत्र:
=LARGE (C5:C12, (1,2,3))
1, 2 और 3 सबसे बड़े मान C5: C12 को इस तरह से एक सरणी में लौटाएगा: (12,12,10)
तो, चाल यहाँ LARGE चलने से पहले रंग के आधार पर मान फ़िल्टर करने के लिए है। हम इसे अभिव्यक्ति के साथ करते हैं:
(color=E5)
जिसके परिणामस्वरूप TRUE / FALSE मानों की एक सरणी होती है। गुणन ऑपरेशन के दौरान, इन मूल्यों को लोगों और शून्य में समाहित किया जाता है:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
तो अंतिम परिणाम यह है कि केवल "लाल" रंग से जुड़े मान ही ऑपरेशन से बचते हैं:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
और अन्य मान शून्य करने के लिए मजबूर हैं।
नोट: यह सूत्र मान श्रेणी में पाठ को हैंडल नहीं करेगा। नीचे देखें।
मूल्यों में पाठ को संभालना
यदि आपके पास मूल्य श्रेणियों में कहीं भी पाठ है, तो LARGE फ़ंक्शन एक # त्रुटि त्रुटि को फेंक देगा और सूत्र को काम करने से रोक देगा।
मान श्रेणी में पाठ को संभालने के लिए, आप IFERROR फ़ंक्शन को इस तरह जोड़ सकते हैं:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
यहाँ, हम पाठ मानों के कारण LARGE से त्रुटियों को फँसाते हैं और शून्य से प्रतिस्थापित करते हैं। LARGE के अंदर IF का उपयोग करने के लिए यह आवश्यक है कि सूत्र को कंट्रोल + शिफ्ट + एंटर के साथ दर्ज किया जाए, इसलिए हम SUMPRODUCT के बजाय SUM पर जाते हैं।
नोट: मैं स्टैकओवरफ़्लो पर अद्भुत बैरी हौडिनी द्वारा पोस्ट किए गए इस सूत्र में भाग गया।
