सामग्री
हे छोटे ट्यूटोरियल फंक्शन कसे बनवायचे ते स्पष्ट करते व्हीपीआर (VLOOKUP) केस-सेन्सिटिव्ह, इतर अनेक सूत्रे दाखवते जी एक्सेल केस-सेन्सिटिव्ह पद्धतीने शोधू शकते आणि प्रत्येक फंक्शनची ताकद आणि कमकुवतपणा दर्शवते.
माझा अंदाज आहे की प्रत्येक एक्सेल वापरकर्त्याला कोणते कार्य अनुलंब शोध करते हे माहित आहे. ते बरोबर आहे, हे एक कार्य आहे व्हीपीआर. मात्र, हे फार कमी लोकांना माहीत आहे व्हीपीआर केस सेन्सेटिव्ह नाही, म्हणजे त्याच्यासाठी लोअर आणि अप्पर केस कॅरेक्टर एकसारखे आहेत.
येथे अक्षमता दर्शविणारे एक द्रुत उदाहरण आहे व्हीपीआर नोंदणी ओळखा. समजा सेलमध्ये A1 मूल्य "बिल" आणि सेल समाविष्टीत आहे A2 - "बिल", सूत्र:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… “बिल” वर त्याचा शोध थांबवेल कारण ते मूल्य सूचीमध्ये प्रथम येते आणि सेलमधून मूल्य काढेल B1.
या लेखात नंतर, मी तुम्हाला कसे करायचे ते दर्शवू व्हीपीआर केस संवेदनशील. याव्यतिरिक्त, आम्ही एक्सेलमध्ये केस-सेन्सिटिव्ह शोध करू शकणारी आणखी काही फंक्शन्स शिकू.
आम्ही सर्वात सोप्यासह प्रारंभ करू - पहा (LOOKUP) आणि संक्षेप (SUMPRODUCT), ज्याला, दुर्दैवाने, अनेक महत्त्वपूर्ण मर्यादा आहेत. पुढे, आपण थोडे अधिक क्लिष्ट सूत्र जवळून पाहू INDEX+MATCH (INDEX+MATCH), जे कोणत्याही परिस्थितीत आणि कोणत्याही डेटासेटसह निर्दोषपणे कार्य करते.
VLOOKUP फंक्शन केस सेन्सेटिव्ह आहे
आपल्याला आधीच माहित आहे की, नेहमीचे कार्य व्हीपीआर केस असंवेदनशील आहे. तथापि, केस संवेदनशील बनवण्याचा एक मार्ग आहे. हे करण्यासाठी, खालील उदाहरणात दर्शविल्याप्रमाणे, आपल्याला टेबलमध्ये एक सहायक स्तंभ जोडण्याची आवश्यकता आहे.
समजा एका स्तंभात B तेथे उत्पादन अभिज्ञापक (आयटम) आहेत आणि तुम्हाला उत्पादनाची किंमत आणि स्तंभांमधून संबंधित टिप्पणी काढायची आहे C и D. समस्या अशी आहे की अभिज्ञापकांमध्ये लोअरकेस आणि अपरकेस दोन्ही वर्ण असतात. उदाहरणार्थ, सेल मूल्ये B4 (001Tvci3u) आणि B5 (001Tvci3U) फक्त शेवटच्या वर्णाच्या बाबतीत भिन्न आहे, u и U अनुक्रमे.
आपण कल्पना करू शकता, नेहमीच्या शोध सूत्र
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
परत येईल $ 90, मूल्य पासून 001Tvci3u पेक्षा पूर्वीच्या शोध श्रेणीत आहे 001Tvci3U. पण तेच आपल्याला हवे आहे ना?
फंक्शनसह शोधण्यासाठी व्हीपीआर एक्सेल केस सेन्सिटिव्हमध्ये, तुम्हाला एक हेल्पर कॉलम जोडावा लागेल आणि त्याचे सेल खालील सूत्राने भरावे लागतील (जेथे 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) आणि नंतर हे कोड एका अनन्य क्रमांकाच्या स्ट्रिंगमध्ये एकत्र करतात.
त्यानंतर, आम्ही एक साधे फंक्शन वापरतो व्हीपीआर केस संवेदनशील शोधासाठी:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
फंक्शनचे योग्य ऑपरेशन व्हीपीआर केस-संवेदनशीलता दोन घटकांवर अवलंबून असते:
- सहाय्यक स्तंभ दृश्यमान श्रेणीतील सर्वात डावीकडे स्तंभ असणे आवश्यक आहे.
- तुम्ही शोधत असलेल्या मूल्यामध्ये वास्तविक मूल्याऐवजी वर्ण कोड असणे आवश्यक आहे.
CODE फंक्शन योग्यरित्या कसे वापरावे
सहाय्यक स्तंभाच्या सेलमध्ये समाविष्ट केलेले सूत्र असे गृहीत धरते की आपल्या सर्व शोध मूल्यांमध्ये समान वर्ण आहेत. नसल्यास, तुम्हाला सर्वात लहान आणि सर्वात मोठी संख्या माहित असणे आवश्यक आहे आणि जास्तीत जास्त वैशिष्ट्ये जोडणे आवश्यक आहे 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) तुम्ही खालील युक्तिवाद प्रदान करता:
- पहिला युक्तिवाद - मजकूर (मजकूर) हा मजकूर किंवा सेल संदर्भ आहे ज्यामध्ये काढले जाणारे वर्ण आहेत (आमच्या बाबतीत ते B2 आहे)
- पहिला युक्तिवाद - start_num (start_position) काढल्या जाणार्‍या त्या वर्णांपैकी पहिल्या वर्णांची स्थिती आहे. तुम्ही प्रविष्ट करा 1 पहिल्या फंक्शनमध्ये पीएसटीआर, 2 - दुसऱ्या फंक्शनमध्ये पीएसटीआर इ
- पहिला युक्तिवाद - num_chars (number_of_characters) - मजकूरातून काढण्यासाठी वर्णांची संख्या निर्दिष्ट करते. आपल्याला नेहमी फक्त 1 अक्षराची आवश्यकता असल्याने, सर्व फंक्शन्समध्ये आपण लिहितो 1.
मर्यादा: कार्य व्हीपीआर Excel मधील केस-सेन्सिटिव्ह शोधांसाठी सर्वोत्तम उपाय नाही. प्रथम, सहायक स्तंभ जोडणे आवश्यक आहे. दुसरे म्हणजे, जर डेटा एकसंध असेल किंवा शोधलेल्या मूल्यांमधील अक्षरांची अचूक संख्या माहित असेल तरच सूत्र चांगले कार्य करते. हे तुमचे केस नसल्यास, आम्ही खाली दर्शविलेल्या उपायांपैकी एक वापरणे चांगले आहे.
केस संवेदनशील शोधासाठी लुकअप फंक्शन
कार्य पहा (LOOKUP) संबंधित व्हीपीआर, तथापि त्याची वाक्यरचना सहायक स्तंभ न जोडता केस-संवेदनशील शोधांना अनुमती देते. हे करण्यासाठी, वापरा पहा फंक्शनसह एकत्रित अचूक (अचूक).
जर आपण मागील उदाहरणावरून डेटा घेतला (सहायक स्तंभाशिवाय), तर खालील सूत्र कार्यास सामोरे जाईल:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
सूत्र श्रेणीमध्ये शोधते ए 2: ए 7 सेल मूल्याशी अचूक जुळणी F2 केस संवेदनशील आणि त्याच पंक्तीच्या स्तंभ B मधून मूल्य मिळवते.
सारखे व्हीपीआरकार्य पहा मजकूर आणि अंकीय मूल्यांसह समानपणे कार्य करते, जसे आपण खालील स्क्रीनशॉटमध्ये पाहू शकता:
महत्त्वाचे! कार्यासाठी क्रमाने पहा योग्यरित्या कार्य केले, लुकअप स्तंभातील मूल्ये चढत्या क्रमाने क्रमवारी लावली पाहिजेत, म्हणजे सर्वात लहान ते सर्वात मोठी.
फंक्शन कसे कार्य करते ते मी थोडक्यात सांगू अचूक वर दर्शविलेल्या सूत्रामध्ये, कारण हा मुख्य मुद्दा आहे.
कार्य अचूक 1ल्या आणि 2ऱ्या वितर्कांमधील दोन मजकूर मूल्यांची तुलना करते आणि ते अगदी सारखे असल्यास TRUE किंवा ते नसल्यास FALSE मिळवते. आमच्यासाठी हे कार्य महत्वाचे आहे अचूक केस संवेदनशील.
चला पाहुया आपले सूत्र कसे कार्य करते पहा + अचूक:
=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 (A2:A7). अचूक जुळणी आढळल्यास TRUE मिळवते, अन्यथा FALSE.
- तुम्ही प्रथम फंक्शन आर्ग्युमेंट देत असल्याने पहा मूल्य TRUE, ते निर्दिष्ट स्तंभातून संबंधित मूल्य काढते (आमच्या बाबतीत, स्तंभ B) जर अचूक जुळणी आढळली, केस संवेदनशील.
मला आशा आहे की हे स्पष्टीकरण स्पष्ट होते आणि आता तुम्हाला मुख्य कल्पना समजली आहे. तसे असल्यास, आपल्याला इतर फंक्शन्समध्ये कोणतीही अडचण येणार नाही ज्याचे आम्ही पुढील विश्लेषण करू, कारण. ते सर्व समान तत्त्वावर कार्य करतात.
मर्यादा: लुकअप कॉलममधील डेटा चढत्या क्रमाने लावणे आवश्यक आहे.
SUMPRODUCT – मजकूर मूल्ये शोधते, केस संवेदनशील, परंतु फक्त संख्या मिळवते
शीर्षकावरून तुम्हाला आधीच समजले आहे, संक्षेप (SUMPRODUCT) हे दुसरे एक्सेल फंक्शन आहे जे तुम्हाला केस-सेन्सिटिव्ह शोध करण्यात मदत करेल, परंतु केवळ अंकीय मूल्ये देईल. जर हा पर्याय आपल्यास अनुरूप नसेल तर आपण त्वरित बंडलवर जाऊ शकता INDEX+MATCH, जे कोणत्याही केससाठी आणि कोणत्याही डेटा प्रकारांसाठी उपाय देते.
प्रथम, मी या फंक्शनचे वाक्यरचना थोडक्यात स्पष्ट करू, हे तुम्हाला पुढील केस-सेन्सिटिव्ह सूत्र अधिक चांगल्या प्रकारे समजून घेण्यास मदत करेल.
कार्य संक्षेप दिलेल्या अॅरेच्या घटकांचा गुणाकार करतो आणि परिणामांची बेरीज मिळवतो. वाक्यरचना असे दिसते:
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 साठी 0पुढील संक्षेप या संख्यांचा गुणाकार करतो आणि निकालांची बेरीज करतो.
शून्य मोजले जात नाहीत कारण गुणाकार केल्यावर ते नेहमी देतात 0. स्तंभात तंतोतंत जुळल्यावर काय होते ते जवळून पाहू A सापडले आणि परत केले 1… कार्य संक्षेप स्तंभातील संख्येचा गुणाकार करतो B on 1 आणि परिणाम परत करतो - अगदी समान संख्या! याचे कारण असे की इतर उत्पादनांचे परिणाम शून्य आहेत, आणि ते परिणामी बेरीजवर परिणाम करत नाहीत.
दुर्दैवाने फंक्शन संक्षेप मजकूर मूल्ये आणि तारखांसह कार्य करू शकत नाही कारण ते गुणाकार केले जाऊ शकत नाहीत. या प्रकरणात, आपल्याला एक त्रुटी संदेश प्राप्त होईल #मूल्य! (#VALUE!) सेलप्रमाणे F4 खालील चित्रात:
मर्यादा: फक्त संख्यात्मक मूल्ये मिळवते.
INDEX + MATCH – कोणत्याही डेटा प्रकारासाठी केस-संवेदनशील शोध
शेवटी, आम्ही अमर्यादित आणि केस-संवेदनशील शोध सूत्राच्या जवळ आहोत जे कोणत्याही डेटा सेटसह कार्य करते.
हे उदाहरण शेवटचे आहे, मिठाईसाठी सर्वोत्तम शिल्लक आहे म्हणून नाही, परंतु मागील उदाहरणांवरून मिळालेले ज्ञान तुम्हाला केस-सेन्सिटिव्ह फॉर्म्युला अधिक चांगले आणि जलद समजण्यात मदत करेल. INDEX+MATCH (INDEX+MATCH).
जसे आपण अंदाज लावला असेल, फंक्शन्सचे संयोजन अधिक उघड и INDEX साठी अधिक लवचिक आणि शक्तिशाली पर्याय म्हणून Excel मध्ये वापरले जाते व्हीपीआर. VLOOKUP ऐवजी INDEX आणि MATCH वापरणे हा लेख या फंक्शन्स एकत्र कसे कार्य करतात हे पूर्णपणे स्पष्ट करेल.
मी फक्त मुख्य मुद्दे पुन्हा सांगेन:
- कार्य अधिक उघड (MATCH) दिलेल्या श्रेणीमध्ये मूल्य शोधते आणि त्याचे सापेक्ष स्थान, म्हणजे, पंक्ती आणि/किंवा स्तंभ क्रमांक मिळवते;
- पुढे, फंक्शन INDEX (INDEX) निर्दिष्ट स्तंभ आणि/किंवा पंक्तीमधून मूल्य मिळवते.
सूत्राला INDEX+MATCH केस-संवेदनशीलपणे शोधू शकतो, तुम्हाला त्यात फक्त एक फंक्शन जोडण्याची आवश्यकता आहे. ते पुन्हा काय आहे याचा अंदाज लावणे कठीण नाही अचूक (अचूक):
=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))
या सूत्रात अचूक फंक्शनच्या संयोगाप्रमाणेच कार्य करते पहा, आणि समान परिणाम देते:
हे सूत्र लक्षात घ्या INDEX+MATCH कुरळे ब्रेसेसमध्ये बंद केलेले अॅरे फॉर्म्युला आहे आणि तुम्ही ते दाबून पूर्ण केले पाहिजे Ctrl + Shift + एंटर करा.
केस-सेन्सिटिव्ह शोधासाठी INDEX+MATCH हा सर्वोत्तम उपाय का आहे?
बंडलचे मुख्य फायदे INDEX и अधिक उघड:
- सहाय्यक स्तंभ जोडणे आवश्यक नाही, विपरीत व्हीपीआर.
- शोध स्तंभाची क्रमवारी लावण्याची आवश्यकता नाही, उलट पहा.
- सर्व प्रकारच्या डेटासह कार्य करते – संख्या, मजकूर आणि तारखा.
हे सूत्र परिपूर्ण वाटते, नाही का? वास्तविक, ते नाही. आणि म्हणूनच.
लुकअप मूल्याशी संबंधित रिटर्न व्हॅल्यू कॉलममधील सेल रिक्त आहे असे गृहीत धरा. सूत्र काय परिणाम देईल? नाही? चला फॉर्म्युला प्रत्यक्षात काय परत येतो ते पाहूया:
अरेरे, सूत्र शून्य परत करतो! आपण शुद्ध मजकूर मूल्यांसह कार्य करत असल्यास ही मोठी समस्या असू शकत नाही. तथापि, जर सारणीमध्ये "वास्तविक" शून्यांसह संख्या असतील, तर ही समस्या बनते.
खरं तर, इतर सर्व लुकअप फॉर्म्युले (VLOOKUP, LOOKUP, आणि SUMPRODUCT) ज्यांची आपण आधी चर्चा केली होती ते त्याच प्रकारे वागतात. पण तुम्हाला परिपूर्ण सूत्र हवे आहे, बरोबर?
सूत्र केस संवेदनशील करण्यासाठी INDEX+MATCH परिपूर्ण, फंक्शनमध्ये ठेवा IF (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+ ही संख्या आहे जी फंक्शनद्वारे परत केलेल्या सेलची सापेक्ष स्थिती बदलते अधिक उघड, सेलच्या वास्तविक पत्त्यावर. उदाहरणार्थ, आमच्या फंक्शनमध्ये अधिक उघड शोध अॅरे दिलेला आहे ए 2: ए 7, म्हणजे, सेलची सापेक्ष स्थिती A2 होईल 1, कारण ते अ‍ॅरेमधील पहिले आहे. परंतु सेलची वास्तविक स्थिती A2 स्तंभात आहे 2, म्हणून आम्ही जोडतो 1फरक तयार करण्यासाठी आणि कार्य करण्यासाठी अप्रत्यक्ष (अप्रत्यक्ष) इच्छित सेलमधून मूल्य पुनर्प्राप्त केले.
खालील चित्रे सुधारित केस-संवेदी सूत्र दर्शवितात INDEX+MATCH कृतीत. परत केलेला सेल रिकामा असल्यास तो रिकामा परिणाम देतो.
मी स्तंभांमध्ये सूत्र पुन्हा लिहिले B:Dस्क्रीनशॉटवर फॉर्म्युला बार बसवण्यासाठी.
फॉर्म्युला परत येतो 0परत केलेल्या सेलमध्ये शून्य असल्यास.
लिंक हवी असल्यास INDEX и अधिक उघड जेव्हा रिटर्न व्हॅल्यू रिकामे असेल तेव्हा काही संदेश प्रदर्शित करा, तुम्ही ते सूत्राच्या शेवटच्या कोट (“”) मध्ये लिहू शकता, उदाहरणार्थ, याप्रमाणे:
=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.")
