सामान्य सूत्र
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
सारांश
आंशिक मिलान के आधार पर सभी मैचों को निकालने के लिए, आप ISNUMBER और SEARCH के समर्थन के साथ INDEX और AGGREGATE फ़ंक्शन के आधार पर एक सरणी सूत्र का उपयोग कर सकते हैं। दिखाए गए उदाहरण में, G5 में सूत्र है:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
निम्नलिखित नामित श्रेणियों के साथ: "खोज" = D5, "ct" = D8, "डेटा" = B5: B55।
नोट: यह एक सरणी सूत्र है, लेकिन इसे नियंत्रण + शिफ्ट + दर्ज करने की आवश्यकता नहीं है, क्योंकि AGGREGATE मूल रूप से सरणियों को संभाल सकता है।
स्पष्टीकरण
इस सूत्र का मूल इंडेक्स फ़ंक्शन है, जिसमें AGGREGATE का उपयोग एक्सट्रैक्ट एरिया में प्रत्येक पंक्ति के लिए "nth मैच" का पता लगाने के लिए किया जाता है:
INDEX(data,nth_match_formula)
लगभग सभी कार्य पता लगाने और रिपोर्ट करने में है कि "डेटा" में कौन सी पंक्तियाँ खोज स्ट्रिंग से मेल खाती हैं, और INDEX के लिए प्रत्येक मिलान मूल्य की स्थिति की रिपोर्ट कर रही है। यह इस तरह कॉन्फ़िगर किए गए AGGREGATE फ़ंक्शन के साथ किया जाता है:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
पहला तर्क, 15, AGGREGATE को SMALL की तरह व्यवहार करने के लिए कहता है, और nth के सबसे छोटे मान लौटाता है। दूसरा तर्क, 6, त्रुटियों को अनदेखा करने का एक विकल्प है। तीसरा तर्क एक अभिव्यक्ति है जो मिलान परिणामों का एक सरणी उत्पन्न करता है (नीचे वर्णित है)। चौथा तर्क, F5, "n" मान को निर्दिष्ट करने के लिए SMALL में "k" की तरह कार्य करता है।
AGGREGATE सरणियों पर काम करता है, और नीचे दी गई अभिव्यक्ति AGGREGATE के अंदर तीसरे तर्क के लिए एक सरणी बनाता है:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
यहां, ROW फ़ंक्शन का उपयोग रिश्तेदार पंक्ति संख्याओं की एक सरणी उत्पन्न करने के लिए किया जाता है, और ISNUMBER और SEARCH का उपयोग डेटा में मानों के विरुद्ध खोज स्ट्रिंग से मिलान करने के लिए किया जाता है, जो TRUE और FALSE मानों की एक सरणी उत्पन्न करता है।
चतुर बिट खोज परिणामों के द्वारा पंक्ति संख्याओं को विभाजित करना है। इस तरह एक गणित ऑपरेशन में, TRUE 1 की तरह व्यवहार करता है, और FALSE शून्य की तरह व्यवहार करता है। परिणाम एक सकारात्मक मैच से जुड़ी पंक्ति संख्याएं 1 से विभाजित होती हैं और ऑपरेशन से बच जाती हैं, जबकि गैर-मिलान मूल्यों से जुड़ी पंक्ति संख्या नष्ट हो जाती है और # DIV / 0 त्रुटियां बन जाती हैं। क्योंकि AGGREGATE त्रुटियों को अनदेखा करने के लिए सेट है, यह # DIV / 0 त्रुटियों को अनदेखा करता है, और "nth" के लिए कॉलम F में संख्या का उपयोग करते हुए, शेष मानों में "nth" सबसे छोटी संख्या देता है।
प्रदर्शन का प्रबंधन
सभी सरणी सूत्रों की तरह, यह सूत्र बड़े डेटा सेट के साथ संसाधनों के संदर्भ में "महंगा" है। प्रदर्शन प्रभावों को कम करने के लिए, पूरे INDEX और MATCH सूत्र को IF में लपेटा गया है:
=IF(F5>ct,"",formula)
जहां नामित सीमा "ct" (D8) इस सूत्र को रखती है:
=COUNTIF(data,"*"&search&"*")
यह जाँच INDEX और AGGREGATE भाग को रोकती है ताकि सभी मिलान मान निकाले जाने से पहले चल सके।
लघु के साथ सरणी सूत्र
यदि आपके Excel के संस्करण में AGGREGATE फ़ंक्शन नहीं है, तो आप SMALL और IF पर आधारित वैकल्पिक सूत्र का उपयोग कर सकते हैं:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
नोट: यह एक सरणी सूत्र है और इसे नियंत्रण + शिफ्ट + दर्ज के साथ दर्ज किया जाना चाहिए।
