विषय-सूची
यह छोटा ट्यूटोरियल बताता है कि फ़ंक्शन कैसे बनाया जाता है VPR (वीलुकअप) केस-संवेदी, कई अन्य फ़ार्मुलों को दिखाता है जिन्हें एक्सेल केस-संवेदी तरीके से खोज सकता है, और प्रत्येक फ़ंक्शन की ताकत और कमजोरियों को इंगित करता है।
मुझे लगता है कि प्रत्येक एक्सेल उपयोगकर्ता जानता है कि कौन सा फ़ंक्शन लंबवत खोज करता है। यह सही है, यह एक कार्य है VPR. हालांकि, कम ही लोग जानते हैं कि VPR केस सेंसिटिव नहीं है, यानी लोअर और अपर केस कैरेक्टर इसके लिए एक जैसे हैं।
अक्षमता का प्रदर्शन करने वाला एक त्वरित उदाहरण यहां दिया गया है VPR पहचान रजिस्टर। मान लीजिए एक सेल में A1 मूल्य "बिल" और सेल शामिल है A2 - "बिल", सूत्र:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... "बिल" पर अपनी खोज बंद कर देगा क्योंकि वह मान सूची में पहले आता है, और सेल से मूल्य निकालेगा B1.
बाद में इस लेख में, मैं आपको दिखाऊंगा कि कैसे करना है VPR अक्षर संवेदनशील। इसके अलावा, हम कुछ और फंक्शन सीखेंगे जो एक्सेल में केस-सेंसिटिव सर्च कर सकते हैं।
हम सबसे सरल से शुरू करेंगे - देखें (लुकअप) और SUMPRODUCT (SUMPPRODUCT), जिसकी दुर्भाग्य से, कई महत्वपूर्ण सीमाएँ हैं। इसके बाद, हम थोड़ा अधिक जटिल सूत्र पर करीब से नज़र डालेंगे इंडेक्स+मैच (INDEX+MATCH), जो किसी भी स्थिति में और किसी भी डेटासेट के साथ त्रुटिपूर्ण ढंग से काम करता है।
VLOOKUP फ़ंक्शन केस संवेदी है
जैसा कि आप पहले से ही जानते हैं, सामान्य कार्य VPR केस असंवेदनशील है। हालाँकि, इसे केस सेंसिटिव बनाने का एक तरीका है। ऐसा करने के लिए, आपको तालिका में एक सहायक कॉलम जोड़ना होगा, जैसा कि निम्नलिखित उदाहरण में दिखाया गया है।
मान लीजिए एक कॉलम में B उत्पाद पहचानकर्ता (आइटम) हैं और आप उत्पाद की कीमत और कॉलम से संबंधित टिप्पणी निकालना चाहते हैं C и D. समस्या यह है कि पहचानकर्ताओं में लोअरकेस और अपरकेस दोनों वर्ण होते हैं। उदाहरण के लिए, सेल मान B4 (001Tvci3u) और B5 (001Tvci3U) केवल अंतिम वर्ण के मामले में भिन्न है, u и U क्रमशः.
जैसा कि आप कल्पना कर सकते हैं, सामान्य खोज सूत्र
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
वापस होगा $ 90, मान के बाद से 001टीवीसीआई3यू से पहले खोज श्रेणी में है 001टीवीसीआई3यू. लेकिन वह नहीं है जो हमें चाहिए, है ना?
फ़ंक्शन के साथ खोजने के लिए VPR एक्सेल केस सेंसिटिव में, आपको एक हेल्पर कॉलम जोड़ना होगा और इसके सेल को निम्नलिखित फॉर्मूले से भरना होगा (जहाँ B लुकअप कॉलम है):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
यह सूत्र वांछित मान को अलग-अलग वर्णों में विभाजित करता है, प्रत्येक वर्ण को उसके कोड से बदल देता है (उदाहरण के लिए, के बजाय A 65 पर, इसके बजाय a कोड 97) और फिर इन कोडों को संख्याओं की एक अनूठी स्ट्रिंग में संयोजित करता है।
उसके बाद, हम एक साधारण फ़ंक्शन का उपयोग करते हैं VPR केस संवेदनशील खोज के लिए:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
समारोह का उचित संचालन VPR केस-संवेदी दो कारकों पर निर्भर करता है:
- देखने योग्य श्रेणी में सहायक स्तंभ सबसे बाईं ओर का स्तंभ होना चाहिए।
- आप जिस मान की तलाश कर रहे हैं उसमें वास्तविक मान के बजाय एक वर्ण कोड होना चाहिए।
कोड फ़ंक्शन का सही उपयोग कैसे करें
सहायक कॉलम की कोशिकाओं में डाला गया सूत्र मानता है कि आपके सभी खोज मानों में वर्णों की संख्या समान है। यदि नहीं, तो आपको सबसे छोटी और सबसे बड़ी संख्या जानने और अधिक से अधिक सुविधाओं को जोड़ने की आवश्यकता है IFERROR (IFERROR) सबसे छोटे और सबसे लंबे खोजे गए मान के बीच कितने वर्ण का अंतर है।
उदाहरण के लिए, यदि सबसे छोटा खोज मान 3 वर्ण का है और सबसे लंबा 5 वर्ण है, तो इस सूत्र का उपयोग करें:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
समारोह के लिए पीएसटीआर (MID) आप निम्नलिखित तर्क देते हैं:
- पहला तर्क - टेक्स्ट (पाठ) पाठ या सेल संदर्भ है जिसमें निकाले जाने वाले वर्ण हैं (हमारे मामले में यह बी 2 है)
- पहला तर्क - प्रारंभ संख्या (start_position) निकाले जाने वाले उन पात्रों में से पहले की स्थिति है। तुम प्रवेश करो 1 पहले समारोह में पीएसटीआर, 2 - दूसरे समारोह में पीएसटीआर इत्यादि
- पहला तर्क - num_chars (number_of_characters) - पाठ से निकालने के लिए वर्णों की संख्या निर्दिष्ट करता है। चूँकि हमें हर समय केवल 1 वर्ण की आवश्यकता होती है, सभी कार्यों में हम लिखते हैं 1.
सीमाएं: समारोह VPR एक्सेल में केस-संवेदी खोजों के लिए सबसे अच्छा समाधान नहीं है। सबसे पहले, एक सहायक कॉलम को जोड़ने की आवश्यकता है। दूसरे, सूत्र एक अच्छा काम तभी करता है जब डेटा सजातीय हो, या खोजे गए मानों में वर्णों की सटीक संख्या ज्ञात हो। यदि यह आपका मामला नहीं है, तो हम नीचे दिखाए गए समाधानों में से किसी एक का उपयोग करना बेहतर समझते हैं।
केस संवेदनशील खोज के लिए लुकअप फ़ंक्शन
समारोह देखें (लुकअप) संबंधित VPR, हालांकि इसका सिंटैक्स सहायक कॉलम जोड़े बिना केस-संवेदी खोजों की अनुमति देता है। ऐसा करने के लिए, उपयोग करें देखें समारोह के साथ संयुक्त सटीक (सटीक)।
यदि हम पिछले उदाहरण (एक सहायक कॉलम के बिना) से डेटा लेते हैं, तो निम्न सूत्र कार्य का सामना करेगा:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
सूत्र श्रेणी में खोज करता है A2: A7 सेल मान के साथ सटीक मिलान F2 केस संवेदनशील और उसी पंक्ति के कॉलम बी से मान लौटाता है।
पसंद VPRसमारोह देखें टेक्स्ट और संख्यात्मक मानों के साथ समान रूप से काम करता है, जैसा कि आप नीचे स्क्रीनशॉट में देख सकते हैं:
महत्वपूर्ण! समारोह के लिए देखें सही ढंग से काम किया, लुकअप कॉलम में मानों को आरोही क्रम में क्रमबद्ध किया जाना चाहिए, यानी सबसे छोटे से सबसे बड़े तक।
मुझे संक्षेप में बताएं कि फ़ंक्शन कैसे काम करता है सटीक ऊपर दिखाए गए सूत्र में, क्योंकि यह मुख्य बिंदु है।
समारोह सटीक 1 और 2 तर्कों में दो पाठ मानों की तुलना करता है और यदि वे बिल्कुल समान हैं, या FALSE यदि वे नहीं हैं तो TRUE लौटाते हैं। हमारे लिए यह महत्वपूर्ण है कि समारोह सटीक अक्षर संवेदनशील।
आइए देखें कि हमारा फॉर्मूला कैसे काम करता है देखें+सटीक:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- समारोह सटीक सेल वैल्यू की तुलना करता है F2 एक कॉलम में सभी तत्वों के साथ A (ए2:ए7) । सटीक मिलान मिलने पर TRUE लौटाता है, अन्यथा FALSE.
- चूंकि आप पहला फ़ंक्शन तर्क देते हैं देखें मान TRUE, यह निर्दिष्ट कॉलम (हमारे मामले में, कॉलम B) से संबंधित मान तभी निकालता है, जब कोई सटीक मिलान, केस सेंसिटिव पाया जाता है।
मुझे आशा है कि यह स्पष्टीकरण स्पष्ट था और अब आप मुख्य विचार को समझ गए हैं। यदि ऐसा है, तो आपको अन्य कार्यों में कोई कठिनाई नहीं होगी जिसका हम आगे विश्लेषण करेंगे, क्योंकि। वे सभी एक ही सिद्धांत पर काम करते हैं।
सीमाएं: लुकअप कॉलम में डेटा को आरोही क्रम में क्रमबद्ध किया जाना चाहिए।
SUMPRODUCT - टेक्स्ट वैल्यू, केस सेंसिटिव पाता है, लेकिन केवल नंबर लौटाता है
जैसा कि आप शीर्षक से समझ चुके हैं, SUMPRODUCT (SUMPRODUCT) एक अन्य एक्सेल फ़ंक्शन है जो आपको केस-संवेदी खोज करने में मदद करेगा, लेकिन केवल संख्यात्मक मान लौटाएगा। यदि यह विकल्प आपको शोभा नहीं देता है, तो आप तुरंत बंडल पर आगे बढ़ सकते हैं इंडेक्स+मैच, जो किसी भी मामले और किसी भी डेटा प्रकार के लिए समाधान देता है।
सबसे पहले, मैं संक्षेप में इस फ़ंक्शन के सिंटैक्स की व्याख्या करता हूं, इससे आपको केस-संवेदी सूत्र को बेहतर ढंग से समझने में मदद मिलेगी जो इस प्रकार है।
समारोह SUMPRODUCT दिए गए सरणियों के तत्वों को गुणा करता है और परिणामों का योग देता है। वाक्यविन्यास इस तरह दिखता है:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
चूंकि हमें केस-संवेदी खोज की आवश्यकता है, इसलिए हम फ़ंक्शन का उपयोग करते हैं सटीक (सटीक) पिछले उदाहरण से गुणकों में से एक के रूप में:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
जैसा कि आपको याद है, सटीक सेल वैल्यू की तुलना करता है F2 एक कॉलम में सभी तत्वों के साथ A. सटीक मिलान मिलने पर TRUE लौटाता है, अन्यथा FALSE. गणितीय संक्रियाओं में, एक्सेल TRUE को इस रूप में लेता है 1, और FALSE for 0आगे SUMPRODUCT इन संख्याओं को गुणा करता है और परिणामों का योग करता है।
शून्यों की गणना नहीं की जाती है क्योंकि जब गुणा किया जाता है तो वे हमेशा देते हैं 0. आइए देखें कि क्या होता है जब किसी कॉलम में सटीक मिलान होता है A पाया और लौटा 1… समारोह SUMPRODUCT कॉलम में संख्या को गुणा करता है B on 1 और परिणाम देता है - बिल्कुल वही संख्या! ऐसा इसलिए है क्योंकि अन्य उत्पादों के परिणाम शून्य हैं, और वे परिणामी योग को प्रभावित नहीं करते हैं।
दुर्भाग्य से समारोह SUMPRODUCT टेक्स्ट मानों और तिथियों के साथ काम नहीं कर सकता क्योंकि उन्हें गुणा नहीं किया जा सकता है। इस मामले में, आपको एक त्रुटि संदेश प्राप्त होगा #VALUE! (#VALUE!) जैसे सेल में होता है F4 नीचे दी गई तस्वीर में:
सीमाएं: केवल संख्यात्मक मान लौटाता है।
INDEX + MATCH - किसी भी डेटा प्रकार के लिए केस-संवेदी खोज
अंत में, हम असीमित और केस-संवेदी खोज फ़ॉर्मूले के करीब हैं जो किसी भी डेटा सेट के साथ काम करता है।
यह उदाहरण अंतिम आता है, इसलिए नहीं कि मिठाई के लिए सबसे अच्छा बचा है, बल्कि इसलिए कि पिछले उदाहरणों से प्राप्त ज्ञान आपको केस-संवेदी सूत्र को बेहतर और तेज़ी से समझने में मदद करेगा। इंडेक्स+मैच (इंडेक्स+मैच)।
जैसा कि आपने शायद अनुमान लगाया, कार्यों का संयोजन अधिक उजागर и सूचकांक एक्सेल में अधिक लचीले और शक्तिशाली विकल्प के रूप में उपयोग किया जाता है VPR. VLOOKUP के बजाय INDEX और MATCH का उपयोग करने वाला लेख पूरी तरह से समझाएगा कि ये फ़ंक्शन एक साथ कैसे काम करते हैं।
मैं सिर्फ मुख्य बिंदुओं का पुनर्कथन करूंगा:
- समारोह अधिक उजागर (MATCH) किसी दी गई श्रेणी में किसी मान की खोज करता है और उसकी सापेक्ष स्थिति, यानी पंक्ति और/या स्तंभ संख्या लौटाता है;
- अगला, फ़ंक्शन सूचकांक (INDEX) एक निर्दिष्ट कॉलम और/या पंक्ति से एक मान देता है।
सूत्र के लिए इंडेक्स+मैच केस-संवेदी रूप से खोज सकता है, आपको इसमें केवल एक फ़ंक्शन जोड़ने की आवश्यकता है। यह अनुमान लगाना मुश्किल नहीं है कि यह फिर से क्या है सटीक (सटीक):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
इस सूत्र में सटीक उसी तरह काम करता है जैसे फ़ंक्शन के साथ संयोजन में देखें, और एक ही परिणाम देता है:
ध्यान दें कि सूत्र इंडेक्स+मैच घुंघराले ब्रेसिज़ में संलग्न एक सरणी सूत्र है और आपको इसे दबाकर पूरा करना होगा Ctrl + Shift + Enter.
केस-संवेदी खोज के लिए INDEX+MATCH सबसे अच्छा समाधान क्यों है?
बंडल के मुख्य लाभ सूचकांक и अधिक उजागर:
- इसके विपरीत, एक सहायक कॉलम जोड़ने की आवश्यकता नहीं है VPR.
- इसके विपरीत, खोज कॉलम को क्रमबद्ध करने की आवश्यकता नहीं है देखें.
- सभी प्रकार के डेटा के साथ काम करता है - नंबर, टेक्स्ट और तारीखें।
यह सूत्र एकदम सही लगता है, है ना? दरअसल, ऐसा नहीं है। और यही कारण है।
मान लें कि लुकअप मान से संबद्ध रिटर्न वैल्यू कॉलम में सेल खाली है। सूत्र क्या परिणाम देगा? नहीं? आइए देखें कि सूत्र वास्तव में क्या लौटाता है:
ओह, फ़ॉर्मूला शून्य लौटाता है! यदि आप शुद्ध टेक्स्ट मानों के साथ काम कर रहे हैं तो यह एक बड़ी समस्या नहीं हो सकती है। हालाँकि, यदि तालिका में "वास्तविक" शून्य सहित संख्याएँ हैं, तो यह एक समस्या बन जाती है।
वास्तव में, अन्य सभी लुकअप फ़ार्मुलों (VLOOKUP, LOOKUP, और SUMPRODUCT) जिनकी हमने पहले चर्चा की थी, उसी तरह व्यवहार करते हैं। लेकिन आप सही फॉर्मूला चाहते हैं, है ना?
फ़ॉर्मूला केस को संवेदनशील बनाने के लिए इंडेक्स+मैच बिल्कुल सही, इसे एक समारोह में रखें IF (आईएफ) जो एक वापसी मूल्य के साथ एक सेल का परीक्षण करेगा और खाली होने पर एक खाली परिणाम लौटाएगा:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
इस सूत्र में:
- B रिटर्न वैल्यू वाला एक कॉलम है
- 1+ एक संख्या है जो फ़ंक्शन द्वारा लौटाए गए सेल की सापेक्ष स्थिति को बदल देती है अधिक उजागर, सेल के वास्तविक पते पर। उदाहरण के लिए, हमारे फ़ंक्शन में अधिक उजागर खोज सरणी दी गई A2: A7, अर्थात्, कोशिका की सापेक्ष स्थिति A2 मर्जी 1, क्योंकि यह सरणी में पहला है। लेकिन सेल की वास्तविक स्थिति A2 कॉलम में है 2, तो हम जोड़ते हैं 1फर्क करने के लिए और कार्य करने के लिए अप्रत्यक्ष (अप्रत्यक्ष) ने वांछित सेल से मान प्राप्त किया।
नीचे दिए गए चित्र सही केस-संवेदी सूत्र दिखाते हैं इंडेक्स+मैच कार्रवाई में। यदि लौटा हुआ सेल खाली है तो यह एक खाली परिणाम देता है।
मैंने सूत्र को कॉलम में फिर से लिखा बी: डीस्क्रीनशॉट पर फॉर्मूला बार फिट करने के लिए।
फॉर्मूला रिटर्न 0यदि लौटे सेल में शून्य है।
अगर आप लिंक चाहते हैं सूचकांक и अधिक उजागर कुछ संदेश प्रदर्शित करता है जब वापसी मूल्य खाली होता है, तो आप इसे सूत्र के अंतिम उद्धरण ("") में लिख सकते हैं, उदाहरण के लिए, इस तरह:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
