सामग्रीहरू
यो सानो ट्यूटोरियलले फंक्शन कसरी बनाउने भनेर बताउँछ VPR (VLOOKUP) केस-संवेदनशील, एक्सेलले केस-संवेदनशील तरिकामा खोजी गर्न सक्ने धेरै अन्य सूत्रहरू देखाउँछ, र प्रत्येक प्रकार्यको बल र कमजोरीहरूलाई औंल्याउँछ।
मलाई लाग्छ कि प्रत्येक एक्सेल प्रयोगकर्ताले कुन प्रकार्यले ठाडो खोज प्रदर्शन गर्छ थाहा छ। त्यो सही छ, यो एक प्रकार्य हो VPR। तर, यो कुरा थोरैलाई थाहा छ VPR केस सेन्सेटिभ छैन, अर्थात् तल्लो र ठूला अक्षरहरू यसको लागि समान छन्।
यहाँ असक्षमता प्रदर्शन गर्ने एक द्रुत उदाहरण हो VPR दर्ता पहिचान। मानौं एक कक्षमा A1 मान "बिल" र सेल समावेश गर्दछ A2 - "बिल", सूत्र:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... ले "बिल" मा यसको खोज रोक्नेछ किनकि त्यो मान सूचीमा पहिलो आउँछ, र सेलबाट मान निकाल्छ। B1.
यस लेखमा पछि, म तपाईंलाई कसरी गर्ने भनेर देखाउनेछु VPR केस संवेदनशील। थप रूपमा, हामी एक्सेलमा केस-संवेदनशील खोजहरू गर्न सक्ने केही थप प्रकार्यहरू सिक्नेछौं।
हामी सरल संग सुरु गर्नेछौं - हेर्नुहोस् (लुकअप) र SUMPRODUCT (SUMPRODUCT), जसमा दुर्भाग्यवश, धेरै महत्त्वपूर्ण सीमाहरू छन्। अर्को, हामी थोरै जटिल सूत्रमा नजिकबाट हेर्नेछौं INDEX+MATCH (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, मूल्य देखि 001Tvci3u भन्दा पहिले खोजी दायरामा छ 001Tvci3U। तर त्यो हामीलाई चाहिने कुरा होइन, हो र?
एक प्रकार्य संग खोज गर्न 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 केस-संवेदनशीलता दुई कारकहरूमा निर्भर गर्दछ:
- हेल्पर स्तम्भ हेर्न मिल्ने दायरामा सबैभन्दा बायाँ स्तम्भ हुनुपर्छ।
- तपाईले खोज्नु भएको मानमा वास्तविक मानको सट्टा क्यारेक्टर कोड हुनुपर्छ।
कसरी 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));"")
कार्यको लागि PSTR (MID) तपाईंले निम्न तर्कहरू प्रदान गर्नुहुन्छ:
- पहिलो तर्क - पाठ (पाठ) निकालिने क्यारेक्टरहरू समावेश गरिएको पाठ वा सेल सन्दर्भ हो (हाम्रो अवस्थामा यो B2 हो)
- पहिलो तर्क - start_num (start_position) निकालिने ती क्यारेक्टरहरू मध्ये पहिलोको स्थिति हो। तपाईं प्रविष्ट गर्नुहोस् 1 पहिलो समारोह मा PSTR, 2 - दोस्रो प्रकार्य मा PSTR आदि
- पहिलो तर्क - num_chars (number_of_characters) - पाठबाट निकाल्न क्यारेक्टरहरूको संख्या निर्दिष्ट गर्दछ। किनकि हामीलाई सबै समय केवल 1 वर्ण चाहिन्छ, सबै प्रकार्यहरूमा हामी लेख्छौं 1.
सीमाहरू: समारोह VPR एक्सेलमा केस-संवेदनशील खोजहरूको लागि उत्तम समाधान होइन। पहिलो, सहायक स्तम्भ थप्न आवश्यक छ। दोस्रो, सूत्रले मात्र राम्रो काम गर्छ यदि डाटा एकरूप छ, वा खोजी मानहरूमा क्यारेक्टरहरूको सही संख्या थाहा छ। यदि यो तपाईंको मामला होइन भने, हामीले तल देखाउने समाधानहरू मध्ये एउटा प्रयोग गर्नु राम्रो हुन्छ।
केस संवेदनशील खोजको लागि लुकअप प्रकार्य
समारोह हेर्नुहोस् (LOOKUP) सम्बन्धित 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 केस सेन्सेटिभ र उही पङ्क्तिको स्तम्भ B बाट मान फर्काउँछ।
जस्तै VPRसमारोह हेर्नुहोस् पाठ र संख्यात्मक मानहरूसँग समान रूपमा काम गर्दछ, जसरी तपाइँ तलको स्क्रिनसटमा देख्न सक्नुहुन्छ:
महत्वपूर्ण! समारोह को लागी हेर्नुहोस् सही तरिकाले काम गरेको छ, लुकअप स्तम्भमा मानहरू बढ्दो क्रममा क्रमबद्ध हुनुपर्छ, अर्थात् सबैभन्दा सानो देखि ठूलो सम्म।
मलाई छोटकरीमा वर्णन गरौं कि प्रकार्य कसरी काम गर्दछ अचूक माथि देखाइएको सूत्रमा, यो मुख्य बिन्दु हो।
समारोह अचूक 1st र 2nd arguments मा दुई पाठ मानहरू तुलना गर्छ र यदि तिनीहरू ठ्याक्कै उस्तै छन् भने 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 (SUMPRODUCT) अर्को एक्सेल प्रकार्य हो जसले तपाईंलाई केस-संवेदनशील खोजी गर्न मद्दत गर्दछ, तर संख्यात्मक मानहरू मात्र फर्काउँछ। यदि यो विकल्पले तपाईंलाई उपयुक्त गर्दैन भने, तपाइँ तुरुन्तै बन्डलमा अगाडि बढ्न सक्नुहुन्छ INDEX+MATCH, जसले कुनै पनि केस र कुनै पनि डेटा प्रकारहरूको लागि समाधान दिन्छ।
पहिले, मलाई यस प्रकार्यको वाक्य रचना संक्षिप्त रूपमा व्याख्या गरौं, यसले तपाईंलाई निम्न केस-संवेदनशील सूत्रलाई अझ राम्रोसँग बुझ्न मद्दत गर्नेछ।
समारोह SUMPRODUCT दिइएको arrays को तत्वहरूलाई गुणन गर्छ र परिणामहरूको योगफल फर्काउँछ। वाक्य रचना यस्तो देखिन्छ:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
हामीलाई केस-संवेदनशील खोज चाहिन्छ, हामी प्रकार्य प्रयोग गर्छौं अचूक गुणकहरू मध्ये एकको रूपमा अघिल्लो उदाहरणबाट (EXACT):
=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थप SUMPRODUCT यी संख्याहरूलाई गुणन गर्छ र परिणामहरूको योगफल दिन्छ।
शून्यलाई गणना गर्दैन किनभने गुणा गर्दा तिनीहरू सधैं दिन्छन् 0। स्तम्भमा ठ्याक्कै मिल्दा के हुन्छ भनेर नजिकबाट हेरौं A भेटियो र फिर्ता गरियो 1… समारोह SUMPRODUCT स्तम्भमा रहेको संख्यालाई गुणन गर्दछ B on 1 र परिणाम फर्काउँछ - ठ्याक्कै उही संख्या! यो किनभने अन्य उत्पादनहरूको नतिजा शून्य छ, र तिनीहरूले परिणाम योगलाई असर गर्दैनन्।
दुर्भाग्यवश समारोह SUMPRODUCT पाठ मान र मितिहरूसँग काम गर्न सक्दैन किनभने तिनीहरू गुणन गर्न सकिँदैन। यस अवस्थामा, तपाईंले त्रुटि सन्देश प्राप्त गर्नुहुनेछ #VALUE! (#VALUE!) सेलमा जस्तै F4 तलको चित्रमा:
सीमाहरू: संख्यात्मक मानहरू मात्र फर्काउँछ।
INDEX + MATCH - कुनै पनि डेटा प्रकारको लागि केस-संवेदनशील खोज
अन्तमा, हामी कुनै पनि डेटा सेटसँग काम गर्ने असीमित र केस-संवेदनशील खोज सूत्रको नजिक छौं।
यो उदाहरण अन्तिममा आउँछ, मिठाईको लागि उत्तम छोडिएको कारण होइन, तर अघिल्लो उदाहरणहरूबाट प्राप्त ज्ञानले तपाईंलाई केस-संवेदनशील सूत्र अझ राम्रो र छिटो बुझ्न मद्दत गर्नेछ। INDEX+MATCH (INDEX+MATCH)।
तपाईंले सायद अनुमान गर्नुभएको रूपमा, प्रकार्यहरूको संयोजन थप खुलासा и INDEX को लागी अधिक लचिलो र शक्तिशाली विकल्प को रूप मा Excel मा प्रयोग गरियो VPR। 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+Enter.
किन INDEX+MATCH केस-संवेदनशील खोजको लागि उत्तम समाधान हो?
बन्डल को मुख्य लाभ INDEX и थप खुलासा:
- एक सहायक स्तम्भ थप्न आवश्यक छैन, विपरीत VPR.
- खोज स्तम्भ क्रमबद्ध गर्न आवश्यक छैन, विपरीत हेर्नुहोस्.
- सबै प्रकारका डाटा - नम्बर, पाठ र मितिहरूसँग काम गर्दछ।
यो सूत्र सिद्ध देखिन्छ, हैन? वास्तवमा, यो होइन। र त्यही कारणले।
लुकअप मानसँग सम्बन्धित रिटर्न मान स्तम्भमा सेल खाली छ भनी मान्नुहोस्। सूत्रले कस्तो परिणाम दिन्छ? छैन? सूत्रले वास्तवमा के फर्काउँछ हेरौं:
उफ्, सूत्रले शून्य फर्काउँछ! यदि तपाइँ शुद्ध पाठ मानहरूसँग काम गर्दै हुनुहुन्छ भने यो ठूलो समस्या नहुन सक्छ। यद्यपि, यदि तालिकामा संख्याहरू छन्, "वास्तविक" शून्यहरू सहित, यो समस्या हुन्छ।
वास्तवमा, हामीले पहिले छलफल गरेका अन्य लुकअप सूत्रहरू (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+ एउटा संख्या हो जसले प्रकार्यद्वारा फर्काइएको कक्षको सापेक्षिक स्थितिलाई बदल्छ थप खुलासा, सेलको वास्तविक ठेगानामा। उदाहरण को लागी, हाम्रो समारोह मा थप खुलासा खोज एरे दिइएको छ A2:A7, अर्थात्, सेल को सापेक्ष स्थिति A2 हुनेछ 1, किनभने यो array मा पहिलो हो। तर सेल को वास्तविक स्थिति A2 स्तम्भमा छ 2, त्यसैले हामी थप्छौं 1फरक बनाउन र कार्य गर्न अप्रत्यक्ष (INDIRECT) ले इच्छित कक्षबाट मान प्राप्त गर्यो।
तलका चित्रहरूले सही केस-संवेदनशील सूत्र देखाउँछन् 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.")
