एक्सेल सूत्र: मिलान मूल्यों को निकालने के लिए फ़िल्टर -

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

=FILTER(list1,COUNTIF(list2,list1))

सारांश

दो सूचियों में मिलान मूल्यों को निकालने के लिए डेटा को फ़िल्टर करने के लिए, आप फ़िल्टर फ़ंक्शन और COUNTIF या COUNTIFS फ़ंक्शन का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, F5 में सूत्र है:

=FILTER(list1,COUNTIF(list2,list1))

जहाँ लिस्ट 1 (बी 5: बी 16) और सूची 2 (डी 5: डी 14) का नाम रेंज है। परिणाम फिल्टर द्वारा वापस लौटाए में केवल मान होते हैं List1 कि में प्रदर्शित List2 ।

नोट: फ़िल्टर Excel 365 में एक नया डायनामिक ऐरे फ़ंक्शन है।

स्पष्टीकरण

यह सूत्र COINTIF फ़ंक्शन के साथ निर्मित तार्किक परीक्षण के आधार पर डेटा को पुनः प्राप्त करने के लिए FILTER फ़ंक्शन पर निर्भर करता है:

=FILTER(list1,COUNTIF(list2,list1))

अंदर से बाहर की ओर काम करते हुए, वास्तविक फ़िल्टर बनाने के लिए COUNTIF फ़ंक्शन का उपयोग किया जाता है:

COUNTIF(list2,list1)

ध्यान दें कि हम list2 को श्रेणी तर्क के रूप में उपयोग कर रहे हैं , और list1 को मानदंड तर्क के रूप में। दूसरे शब्दों में, हम COUNTIF को list2 में दिखाई देने वाले list1 के सभी मूल्यों को गिनने के लिए कह रहे हैं । क्योंकि हम मानदंड के लिए कई मान दे रहे हैं, हमें कई परिणामों के साथ एक सरणी वापस मिलती है:

(1;1;0;1;0;1;0;0;1;0;1;1)

नोट सरणी 12 मायने रखता है, में प्रत्येक मान के लिए एक शामिल List1 । एक शून्य मान में एक मूल्य इंगित करता है List1 कि में नहीं पाया जाता List2 । किसी भी अन्य सकारात्मक संख्या में एक मूल्य इंगित करता है List1 कि में पाया जाता है List2 । इस सरणी को सीधे फ़ंक्शन फ़ंक्शन में शामिल किया जाता है जिसमें तर्क शामिल हैं:

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

फ़िल्टर फ़ंक्शन फ़िल्टर के रूप में सरणी का उपयोग करता है। शून्य से जुड़ी सूची 1 में किसी भी मूल्य को हटा दिया जाता है, जबकि सकारात्मक संख्या से जुड़ा कोई भी मूल्य जीवित रहता है।

परिणाम 7 मिलान मूल्यों की एक सरणी है जो F5: F11 रेंज में फैलती है। यदि डेटा में परिवर्तन होता है, तो फ़िल्टर नए डेटा के आधार पर मिलान मूल्यों की एक नई सूची को फिर से तैयार करेगा और लौटाएगा।

गैर-मिलान मूल्य

से मेल नहीं खाने वाले मान एक्सट्रैक्ट करने List1 (में यानी मूल्यों List1 कि में दिखाई नहीं देते List2 आप इस तरह सूत्र के नहीं समारोह में जोड़ सकते हैं):

=FILTER(list1,NOT(COUNTIF(list2,list1)))

NOT फ़ंक्शन प्रभावी रूप से COUNTIF से परिणाम को उलट देता है - कोई भी गैर-शून्य संख्या FALSE हो जाती है, और कोई भी शून्य मान TRUE हो जाता है। परिणाम में मानों की सूची है List1 कि में मौजूद नहीं हैं List2 ।

INDEX के साथ

फिल्टर फ़ंक्शन के बिना मिलान मूल्यों को निकालने के लिए एक सूत्र बनाना संभव है, लेकिन सूत्र अधिक जटिल है। एक विकल्प इस तरह से सूत्र में INDEX फ़ंक्शन का उपयोग करना है:

G5 में सूत्र, नीचे कॉपी किया गया है:

=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")

नोट: यह एक सरणी सूत्र है और इसे Excel 365 को छोड़कर नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।

इस सूत्र के मुख्य सूचकांक समारोह है, जो प्राप्त करता है List1 सरणी तर्क के रूप में। शेष सूत्र का अधिकांश मिलान मूल्यों के लिए उपयोग करने के लिए केवल पंक्ति संख्या की गणना करता है। यह अभिव्यक्ति सापेक्ष पंक्ति संख्याओं की सूची तैयार करती है:

ROW(list1)-ROW(INDEX(list1,1,1))+1

जो 12 में पंक्तियों का प्रतिनिधित्व संख्या की एक सरणी देता है List1 :

(1;2;3;4;5;6;7;8;9;10;11;12)

COFFIF फ़ंक्शन के आधार पर, IF फ़ंक्शन और FILTER में उपर्युक्त तर्क के साथ इन्हें फ़िल्टर किया गया है:

COUNTIF(list2,list1) // find matching values

परिणामी सरणी इस तरह दिखती है:

(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF

इस सरणी को सीधे SMALL फ़ंक्शन पर पहुंचाया जाता है, जिसका उपयोग अगली मिलान पंक्ति संख्या को लाने के लिए किया जाता है क्योंकि सूत्र को कॉलम के नीचे कॉपी किया जाता है। SMALL के लिए k मान (एनटीटी लगता है) की गणना एक विस्तार सीमा के साथ की जाती है:

ROWS($G$5:G5) // incrementing value for k

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