एक्सेल सूत्र: अद्वितीय मानों को सॉर्ट करें और निकालें -

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

=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)

सारांश

डेटा की एक सूची से अनूठे मानों को गतिशील रूप से सॉर्ट करने और निकालने के लिए, आप एक सहायक स्तंभ में रैंक स्थापित करने के लिए एक सरणी सूत्र का उपयोग कर सकते हैं, फिर अनन्य मान निकालने के लिए विशेष रूप से निर्मित INDEX और MATCH सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, C5: C13 में रैंक स्थापित करने का सूत्र है:

=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))

जहां "डेटा" नाम B5: B13 है।

नोट: यह एक मल्टी-सेल ऐरे फॉर्मूला है, जिसे कंट्रोल + शिफ्ट + एंटर के साथ दर्ज किया गया है।

स्पष्टीकरण

नोट: इस सूत्र का मूल विचार माइक गिरविन की उत्कृष्ट पुस्तक कंट्रोल + शिफ्ट + एंटर में एक उदाहरण से अनुकूलित है।

दिखाया गया उदाहरण कई सूत्रों का उपयोग करता है, जो नीचे वर्णित हैं। उच्च स्तर पर, MMULT फ़ंक्शन का उपयोग एक हेल्पर कॉलम (कॉलम C) में एक संख्यात्मक रैंक की गणना करने के लिए किया जाता है, और इस रैंक का उपयोग स्तंभ मान में INDEX और MATCH फॉर्मूला द्वारा अद्वितीय मान निकालने के लिए किया जाता है।

डेटा मूल्यों की रैंकिंग

MMULT फ़ंक्शन मैट्रिक्स गुणा करता है और प्रत्येक मूल्य के लिए एक संख्यात्मक रैंक प्रदान करने के लिए उपयोग किया जाता है। पहला सरणी निम्न अभिव्यक्ति के साथ बनाया गया है:

--(data>TRANSPOSE(data))

यहां, हम डेटा का एक क्षैतिज सरणी बनाने के लिए ट्रांसपोज़ फ़ंक्शन का उपयोग करते हैं , और सभी मूल्यों की एक दूसरे से तुलना की जाती है। संक्षेप में, प्रश्न का उत्तर देने के लिए प्रत्येक मूल्य की तुलना हर दूसरे मूल्य के साथ की जाती है "क्या यह मूल्य हर दूसरे मूल्य से अधिक है"। इसका परिणाम दो आयामी सरणी, 9 कॉलम x 9 पंक्तियों, TRUE और FALSE मानों से भरा है। दोहरे नकारात्मक (-) का प्रयोग TRUE FALSE मानों को 1s और शून्य करने के लिए किया जाता है। आप इस तरह परिणामी सरणी की कल्पना कर सकते हैं:

1s और शून्य से ऊपर की मैट्रिक्स MMULT फ़ंक्शन के अंदर array1 बन जाती है। Array2 इस अभिव्यक्ति के साथ बनाया गया है:

ROW(data)^0

यहां, "डेटा" में प्रत्येक पंक्ति संख्या एक-आयामी सरणी, 1 कॉलम x 9 पंक्तियों को बनाने के लिए शून्य की शक्ति तक बढ़ाई गई है, नंबर 1 से भरा हुआ है। एमएमआईएलटी दो सरणियों के मैट्रिक्स उत्पाद को लौटाता है, जो बन जाते हैं रैंक कॉलम में देखे गए मान।

हमें एक ही समय में सभी 9 रैंकिंग वापस मिल जाती हैं, इसलिए हमें परिणामों को एक ही बार में अलग-अलग कक्षों में रखना होगा। अन्यथा, प्रत्येक सेल केवल उस सरणी में पहला रैंकिंग मान दिखाएगा जो वापस आ गया है।

नोट: यह एक मल्टी-सेल ऐरे फॉर्मूला है, जिसे C5: C13 श्रेणी में कंट्रोल + शिफ्ट + एंटर के साथ दर्ज किया गया है।

रिक्त कोशिकाओं को संभालना

रैंकिंग सूत्र के इस भाग के साथ खाली कोशिकाओं को संभाला जाता है:

=IF(data="",ROWS(data)

यहां, MMULT चलाने से पहले, हम जांचते हैं कि "डेटा" में वर्तमान सेल रिक्त है या नहीं। यदि ऐसा है, तो हम एक रैंक मान प्रदान करते हैं जो डेटा में पंक्ति गणना के बराबर है। यह रिक्त कोशिकाओं को सूची के नीचे करने के लिए मजबूर करने के लिए किया जाता है, जहां उन्हें बाद में आसानी से बाहर रखा जा सकता है क्योंकि अद्वितीय मान निकाले जाते हैं (नीचे समझाया गया है)।

अनूठे मूल्यों की गिनती

डेटा में अद्वितीय मानों की गणना करने के लिए, E5 में सूत्र है:

=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)

चूंकि ऊपर दिया गया रैंकिंग सूत्र प्रत्येक मान के लिए एक संख्यात्मक रैंक प्रदान करता है, इसलिए हम अनूठे मूल्यों को गिनने के लिए SUM के साथ FREQUENCY फ़ंक्शन का उपयोग कर सकते हैं। इस सूत्र को यहां विस्तार से बताया गया है। यदि डेटा में कोई खाली सेल हैं तो हम परिणाम से 1 घटाते हैं:

-(blank>0)

जहां "रिक्त" नामांकित श्रेणी E8 है, और इसमें यह सूत्र है:

=COUNTBLANK(data)

अनिवार्य रूप से, हम डेटा में रिक्त कोशिकाओं के होने पर अद्वितीय गणना को कम करते हैं, क्योंकि हम इन परिणामों को शामिल नहीं करते हैं। सेल E5 में अद्वितीय गणना को "यूनिक" (अद्वितीय गणना के लिए) नाम दिया गया है, और INDEX और MATCH सूत्र द्वारा रिक्त कोशिकाओं को फ़िल्टर करने के लिए उपयोग किया जाता है (नीचे वर्णित है)।

अद्वितीय मान निकाल रहा है

अद्वितीय मान निकालने के लिए, G5 में निम्न सूत्र शामिल हैं, जिन्हें कॉपी किया गया है:

=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))

INDEX और MATCH फॉर्मूला चलाने से पहले, हम पहले यह जांचते हैं कि क्या निष्कर्षण क्षेत्र में वर्तमान पंक्ति की गिनती नामांकित श्रेणी "यूनिक" (E5) की अद्वितीय गणना से अधिक है:

=IF(ROWS($G$5:G5)>unique,"",

यदि, तो, हम अनूठे मूल्यों को निकालने के लिए किए जाते हैं और हम एक खाली स्ट्रिंग ("") लौटाते हैं। यदि नहीं, तो हम निष्कर्षण फॉर्मूला चलाते हैं:

INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))

ध्यान दें कि यहाँ दो MATCH फ़ंक्शंस हैं, एक दूसरे के अंदर। आंतरिक MATCH लुकअप मान के लिए एक सरणी और नामांकित श्रेणी "डेटा" के लिए एक विस्तार श्रेणी का उपयोग करता है:

MATCH(data,$G$4:G4,0)

ध्यान दें कि विस्तार सीमा "पंक्ति के ऊपर", उदाहरण में पंक्ति 4 से शुरू होती है। आंतरिक MATCH से परिणाम एक सरणी है, जिसमें डेटा के प्रत्येक मूल्य के लिए, या तो एक संख्यात्मक स्थिति होती है (मान पहले ही निकाला जा चुका है) या # एन / ए त्रुटि (मान अभी तक नहीं निकाला गया है)। हम इन परिणामों को फ़िल्टर करने के लिए IF और ISNA का उपयोग करते हैं, और "डेटा" में सभी मानों के लिए रैंक मान नहीं लौटाते हैं:

IF(ISNA(results),rank))

यह ऑपरेशन एक सरणी में परिणाम करता है, जिसे अभी तक नहीं निकाले गए डेटा मानों के लिए "न्यूनतम रैंक मान" प्राप्त करने के लिए MIN फ़ंक्शन में खिलाया जाता है। MIN फ़ंक्शन इस मान को बाहरी MATCH को लुकअप मान और सरणी के रूप में नामित श्रेणी "रैंक" के रूप में लौटाता है:

MATCH(min_not_extracted,rank)),rank,0)

अंत में, MATCH, INDEX को एक पंक्ति संख्या के रूप में निम्नतम रैंक मान की स्थिति में लौटाता है, और INDEX निष्कर्षण रेंज की वर्तमान पंक्ति में डेटा मान लौटाता है।