सामान्य सूत्र
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
सारांश
किसी सूची या स्तंभ से केवल अनन्य मान निकालने के लिए, आप INDEX, MATCH और COUNTIF के आधार पर एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, D5 का फॉर्मूला, नीचे कॉपी किया गया है:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
जहाँ "सूची" का नाम B5: B11 है।
नोट: यह एक सरणी सूत्र है और इसे कंट्रोल + शिफ्ट + एंटर का उपयोग करके दर्ज किया जाना चाहिए।
स्पष्टीकरण
इस सूत्र का मूल INDEX के साथ एक बुनियादी खोज है:
=INDEX(list,row)
दूसरे शब्दों में, INDEX को सूची और एक पंक्ति संख्या दें, और INDEX अद्वितीय सूची में जोड़ने के लिए एक मान प्राप्त करेगा।
कड़ी मेहनत INDEX देने के लिए ROW नंबर का पता लगा रही है, जिससे हमें केवल विशिष्ट मूल्य मिलते हैं। यह MATCH और COUNTIF के साथ किया जाता है, और मुख्य चाल यहाँ है:
COUNTIF($D$4:D4,list)
यहाँ, COUNTIF गिना जाता है कि पहले से ही अद्वितीय सूची में कितनी बार आइटम मास्टर सूची में दिखाई देते हैं, सीमा के लिए एक विस्तृत संदर्भ का उपयोग करके, $ D $ 4: D4।
एक तरफ एक विस्तार संदर्भ निरपेक्ष है, दूसरे पर सापेक्ष। इस मामले में, जैसा कि सूत्र नीचे कॉपी किया गया है, अद्वितीय सूची में अधिक पंक्तियों को शामिल करने के लिए संदर्भ का विस्तार होगा।
ध्यान दें कि D4, अद्वितीय सूची में पहली अनूठी प्रविष्टि के ऊपर एक पंक्ति में शुरू होता है। यह जानबूझकर है - हम अद्वितीय सूची में आइटम * पहले से ही * गिनना चाहते हैं, और हम एक परिपत्र संदर्भ बनाए बिना वर्तमान सेल को शामिल नहीं कर सकते। तो, हम ऊपर की पंक्ति पर शुरू करते हैं।
महत्वपूर्ण: सुनिश्चित करें कि अद्वितीय सूची के लिए शीर्षक मास्टर सूची में प्रकट नहीं होता है।
COUNTIF में मानदंड के लिए, हम मास्टर सूची का ही उपयोग कर रहे हैं। जब कई मापदंड दिए जाते हैं, तो COUNTIF एक परिणाम में कई परिणाम देगा। प्रत्येक नई पंक्ति में, हमारे पास एक अलग सरणी होती है:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
नोट: COUNTIF एक "OR" रिलेशनशिप (यानी COUNTIF (रेंज, ("रेड", "ब्लू", "ग्रीन")) के साथ कई मापदंड को हैंडल करता है, लाल, नीले या हरे रंग को गिनता है।
अब हमारे पास एरे हैं जिन्हें हमें पदों (पंक्ति संख्या) को खोजने की आवश्यकता है। इसके लिए, हम शून्य मान ज्ञात करने के लिए, सटीक मिलान के लिए सेट MATCH का उपयोग करते हैं। यदि हम COUNTIF द्वारा बनाई गई सरणियों को MATCH में डालते हैं, तो यहां हमें यह मिलता है:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH, शून्य की गिनती (यानी ऐसी वस्तुओं की तलाश में है जो अभी तक अद्वितीय सूची में दिखाई नहीं देती हैं) द्वारा आइटम ढूँढती है। यह काम करता है, क्योंकि डुप्लिकेट होने पर MATCH हमेशा पहला मैच लौटाता है।
अंत में, पदों को पंक्ति संख्या के रूप में INDEX में खिलाया जाता है, और INDEX उस स्थिति में नाम देता है।
गैर सरणी संस्करण LOOKUP के साथ
आप लचीले LOOKUP फ़ंक्शन का उपयोग करके अद्वितीय आइटम निकालने के लिए एक गैर-सरणी सूत्र बना सकते हैं:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
सूत्र निर्माण INDEX MATCH सूत्र के समान है, लेकिन LOOKUP सरणी संचालन को मूल रूप से संभाल सकता है।
- COUNTIF, विस्तार सूची $ D $ 4: D4 में "सूची" से प्रत्येक मान की गणना करता है
- शून्य की तुलना में TRUE और FALSE मूल्यों की एक सरणी बनती है
- संख्या 1 को सरणी से विभाजित किया गया है, 1s और # DIV / 0 त्रुटियों की एक सरणी बना रहा है
- यह सरणी LOOKUP के अंदर लुकअप_वेक्टर बन जाता है
- 2 का लुकअप मान लुकअप_वेक्टर में किसी भी मान से बड़ा है
- LOOKUP लुकअप सरणी में अंतिम गैर-त्रुटि मान से मेल खाएगा
- LOOKUP result_vector में संबंधित मान लौटाता है, नामांकित श्रेणी "सूची"
केवल एक बार दिखने वाले आइटम निकालें
ऊपर LOOKUP सूत्र बूलियन तर्क के साथ विस्तार करना आसान है। स्रोत डेटा में केवल एक बार दिखाई देने वाली अद्वितीय वस्तुओं की सूची निकालने के लिए, आप इस तरह एक सूत्र का उपयोग कर सकते हैं:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
एकमात्र अतिरिक्त दूसरा COUNTIF अभिव्यक्ति है:
COUNTIF(list,list)=1
यहाँ, COUNTIF इस तरह से आइटम की एक श्रेणी देता है:
(2;2;2;2;2;1;2)
जिसकी तुलना 1 से की गई है, जिसके परिणामस्वरूप TRUE / FALSE मूल्यों की एक सरणी है:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
जो स्रोत डेटा में सिर्फ एक बार होने वाली वस्तुओं के उत्पादन को प्रतिबंधित करने के लिए एक "फिल्टर 'के रूप में कार्य करता है।
