एक्सेल सूत्र: दो सूची से सामान्य मान निकालें -

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

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

सारांश

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

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

जहाँ list1 (B5: B15) और list2 (D5: D13) को श्रेणी कहा जाता है। परिणाम, मान जो दोनों सूचियों में दिखाई देते हैं, सीमा F5: F11 में फैलते हैं।

स्पष्टीकरण

फिल्टर समारोह मूल्यों की एक सरणी को स्वीकार करता है और एक "शामिल" तर्क है जो एक तार्किक अभिव्यक्ति या मूल्य के आधार पर सरणी को फ़िल्टर करता है।

इस स्थिति में, सरणी को नामित सूची "सूची 1" के रूप में प्रदान किया जाता है, जिसमें बी 5: बी 15 में सभी मूल्य शामिल हैं। शामिल तर्क COUNTIF समारोह है, जो नेस्टेड अंदर फ़िल्टर है के द्वारा दिया जाता है:

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

COUNTIF को list2 के साथ श्रेणी , और list1 को मानदंड के रूप में सेट किया गया है । क्योंकि हम COUNTIF को ग्यारह मानदंड देते हैं, COUNTIF ग्यारह परिणाम इस तरह से देता है:

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

सूची 1 में दिखाई देने वाले आइटम 2 में 1 के अनुरूपों पर ध्यान दें।

इस सरणी को सीधे "फ़ंक्शन" तर्क के रूप में फ़िल्टर फ़ंक्शन में वितरित किया जाता है:

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

फ़िल्टर फ़ंक्शन COUNTIF द्वारा प्रदान किए गए मानों का उपयोग करके list1 को फ़िल्टर करता है। शून्य से जुड़े मान हटा दिए जाते हैं; अन्य मान संरक्षित हैं।

अंतिम परिणाम मानों की एक सरणी है जो दोनों सूचियों में मौजूद है, जो कि F5: F11 रेंज में फैलती है।

विस्तारित तर्क

उपरोक्त सूत्र में, हम फ़िल्टर के रूप में COUNTIF से कच्चे परिणामों का उपयोग करते हैं। यह काम करता है क्योंकि एक्सेल TRUE के रूप में किसी भी गैर-शून्य मान का मूल्यांकन करता है, और FALSE के रूप में शून्य। यदि COUNTIF 1 से अधिक गिनती देता है, तो फ़िल्टर अभी भी ठीक से काम करेगा।

TRUE और FALSE परिणामों को स्पष्ट रूप से लागू करने के लिए, आप इस तरह से "> 0" का उपयोग कर सकते हैं:

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

डुप्लिकेट या सॉर्ट निकालें

डुप्लिकेट को निकालने के लिए, UNIQUE फ़ंक्शन के अंदर केवल फॉर्मूला घोंसला बनाएं:

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

परिणामों को सॉर्ट करने के लिए, SORT फ़ंक्शन में घोंसला:

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

सूची 2 से गायब मानों को सूचीबद्ध करें

List2 से सूची 1 से गायब मानों को आउटपुट करने के लिए, आप इस तरह से तर्क को उलट सकते हैं:

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