एक्सेल सूत्र: मानदंड के साथ शीर्ष एन मूल्यों पर फिल्टर -

सामान्य सूत्र

=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))

सारांश

विशिष्ट मानदंडों को पूरा करने वाले शीर्ष n मानों को दिखाने के लिए डेटा को फ़िल्टर करने के लिए, आप LARGE और IF फ़ंक्शन के साथ FILTER फ़ंक्शन का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, F5 में सूत्र है:

=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))

जहाँ डेटा (B5: D16), समूह (C5: C16) और स्कोर (D5: D16) को श्रेणी कहा जाता है।

स्पष्टीकरण

यह सूत्र LARGE और IF फ़ंक्शन के साथ निर्मित तार्किक परीक्षण के आधार पर डेटा को पुनः प्राप्त करने के लिए FILTER फ़ंक्शन का उपयोग करता है। परिणाम समूह बी में शीर्ष 3 स्कोर है।

फ़िल्टर फ़ंक्शन में शामिल तर्क के साथ मानदंड लागू होते हैं। इस उदाहरण में, बूलियन तर्क के साथ मानदंड बनाए जाते हैं:

(score>=LARGE(IF(group="b",score),3))*(group="b")

अभिव्यक्ति का बायाँ भाग समूह B के 3 उच्चतम स्कोर के बराबर या उससे अधिक स्कोर को लक्षित करता है:

score>=LARGE(IF(group="b",score),3)

IF फ़ंक्शन का उपयोग यह सुनिश्चित करने के लिए किया जाता है कि LARGE केवल समूह B स्कोर के साथ काम कर रहा है। क्योंकि हमारे पास कुल 12 अंक हैं, IF इस तरह 12 परिणामों के साथ एक सरणी देता है:

(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)

केवल वही स्कोर नोटिस करें जो ऑपरेशन से बचे हैं, ग्रुप बी से हैं। अन्य सभी स्कोर FALSE हैं। यह सरणी सरणी तर्क के रूप में सीधे LARGE को लौटा दी जाती है:

LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)

LARGE FALSE मूल्यों की अनदेखी करता है और तीसरा उच्चतम स्कोर, 83 लौटाता है।

अब हम सूत्र को सरल बना सकते हैं:

=FILTER(data,(score>=83)*(group="b"))

जो हल करता है:

=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))

अंत में, मेस्टर, एनी और कैसिडी के लिए फिल्टर रिकॉर्ड्स लौटाता है, जो F5: H7 रेंज में फैलता है।

स्कोर के आधार पर क्रमबद्ध करें

डिफ़ॉल्ट रूप से, फ़िल्टर उसी क्रम में मिलान रिकॉर्ड लौटाएगा जो वे स्रोत डेटा में दिखाई देते हैं। स्कोर द्वारा अवरोही क्रम में परिणामों को क्रमबद्ध करने के लिए, आप इस तरह SORT फ़ंक्शन के अंदर मूल फ़िल्टर सूत्र को घोंसला कर सकते हैं:

=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)

यहाँ, सरणी तर्क के रूप में फ़िल्टर सीधे SORT फ़ंक्शन पर परिणाम देता है। सॉर्ट इंडेक्स को 3 (स्कोर) पर सेट किया गया है और क्रम को अवरोही क्रम के लिए -1 पर सेट किया गया है।