मानहरू प्रतिस्थापन गर्न VLOOKUP प्रकार्य प्रयोग गर्दै

को धेरै अल्छी छ वा पढ्ने समय छैन - भिडियो हेर्नुहोस्। विवरण र सूक्ष्मताहरू तलको पाठमा छन्।

समस्याको सूत्रीकरण

त्यसोभए, हामीसँग दुईवटा टेबलहरू छन् - अर्डर तालिका и मूल्य सूचि:

कार्य भनेको मूल्य सूचीबाट स्वचालित रूपमा अर्डरहरूको तालिकामा मूल्यहरू प्रतिस्थापन गर्नु हो, उत्पादनको नाममा फोकस गर्दै ताकि पछि तपाईंले लागत गणना गर्न सक्नुहुनेछ।

समाधान

एक्सेल प्रकार्य सेटमा, श्रेणी अन्तर्गत सन्दर्भ र arrays (हेर्नुहोस् र सन्दर्भ) त्यहाँ एक समारोह छ VPR (VLOOKUP).यो प्रकार्यले दिइएको मान खोज्छ (हाम्रो उदाहरणमा, यो "एप्पल" शब्द हो) निर्दिष्ट तालिकाको बायाँ स्तम्भमा (मूल्य सूची) माथिबाट तल सर्दै र, फेला पारेपछि, छेउछाउको कक्षको सामग्रीहरू प्रदर्शन गर्दछ। (23 rubles) .Schematically, यो प्रकार्य को सञ्चालन यसरी प्रतिनिधित्व गर्न सकिन्छ:

प्रकार्यको थप प्रयोगको सजिलोको लागि, एकैचोटि एउटा कुरा गर्नुहोस् - मूल्य सूचीमा तपाईंको आफ्नै नामको कक्षहरूको दायरा दिनुहोस्। यो गर्नका लागि, "हेडर" (G3: H19) बाहेक मूल्य सूचीका सबै कक्षहरू चयन गर्नुहोस्, मेनुबाट चयन गर्नुहोस्। घुसाउनुहोस् - नाम - असाइन गर्नुहोस् (सम्मिलित गर्नुहोस् - नाम - परिभाषित गर्नुहोस्) वा थिच्नुहोस् CTRL + F3 र कुनै पनि नाम प्रविष्ट गर्नुहोस् (कुनै खाली ठाउँ छैन) जस्तै मूल्य… अब, भविष्यमा, तपाईंले मूल्य सूचीमा लिङ्क गर्न यो नाम प्रयोग गर्न सक्नुहुन्छ।

अब हामी प्रकार्य प्रयोग गर्छौं VPR... सेल चयन गर्नुहोस् जहाँ यो प्रविष्ट गरिनेछ (D3) र ट्याब खोल्नुहोस् सूत्रहरू - प्रकार्य सम्मिलन (सूत्रहरू - प्रकार्य घुसाउनुहोस्)… वर्गमा सन्दर्भ र arrays (हेर्नुहोस् र सन्दर्भ) प्रकार्य फेला पार्नुहोस् VPR (VLOOKUP) र थिच्नुहोस् OK… प्रकार्यको लागि आर्गुमेन्टहरू प्रविष्ट गर्नको लागि एउटा सञ्झ्याल देखा पर्नेछ:

मानहरू प्रतिस्थापन गर्न VLOOKUP प्रकार्य प्रयोग गर्दै

हामी तिनीहरूलाई बारीमा भर्छौं:

  • वांछित मूल्य (लुकअप मान) - उत्पादनको नाम जुन प्रकार्यले मूल्य सूचीको सबैभन्दा बायाँ स्तम्भमा फेला पार्नुपर्दछ। हाम्रो अवस्थामा, सेल B3 बाट "सेब" शब्द।
  • तालिका (तालिका एरे) - एउटा तालिका जसबाट वांछित मानहरू uXNUMXbuXNUMX ले लिएको छ, त्यो हो, हाम्रो मूल्य सूची। सन्दर्भको लागि, हामी पहिले दिइएको हाम्रो आफ्नै नाम "मूल्य" प्रयोग गर्दछौं। यदि तपाईंले नाम दिनुभएन भने, तपाईं केवल तालिका चयन गर्न सक्नुहुन्छ, तर बटन थिच्न नबिर्सनुहोस् F4डलर चिन्हहरूसँग लिङ्क पिन गर्न, किनभने अन्यथा, स्तम्भ D3:D30 को बाँकी कक्षहरूमा हाम्रो सूत्र प्रतिलिपि गर्दा यो तल स्लाइड हुनेछ।
  • स्तम्भ_संख्या (स्तम्भ अनुक्रमणिका नम्बर) - मूल्य सूचीको स्तम्भको क्रम संख्या (अक्षर होइन!) जसबाट हामी मूल्य मानहरू लिनेछौं। नामहरूको साथ मूल्य सूचीको पहिलो स्तम्भ नम्बर 1 छ, त्यसैले हामीलाई स्तम्भ नम्बर 2 बाट मूल्य चाहिन्छ।
  • interval_lookup (दायरा लुकअप) - यो क्षेत्रमा केवल दुई मानहरू प्रविष्ट गर्न सकिन्छ: FALSE वा TRUE:
      • यदि मान प्रविष्ट गरिएको छ 0 or झुटो (FALSE), त्यसोभए वास्तवमा यसको मतलब खोजी मात्र अनुमति छ सही मिलान, अर्थात् यदि प्रकार्यले मूल्य सूचीमा अर्डर तालिकामा निर्दिष्ट गरिएको गैर-मानक वस्तु फेला पारेन (उदाहरणका लागि "कोकोनट" प्रविष्ट गरिएको छ भने), यसले #N/A (डेटा छैन) त्रुटि उत्पन्न गर्नेछ।
      • यदि मान प्रविष्ट गरिएको छ 1 or साँचो (सत्य), त्यसोभए यसको मतलब यो हो कि तपाईले खोजीलाई सटीकको लागि अनुमति दिनुहुन्छ, तर अनुमानित मिलान, अर्थात् "कोकोनट" को मामलामा, प्रकार्यले "नारियल" को सकेसम्म नजिकको नाम भएको उत्पादन फेला पार्ने प्रयास गर्नेछ र यो नामको मूल्य फिर्ता गर्नेछ। धेरै जसो केसहरूमा, यस्तो अनुमानित प्रतिस्थापनले वास्तवमा त्यहाँ भएको गलत उत्पादनको मूल्य प्रतिस्थापन गरेर प्रयोगकर्तामा चाल खेल्न सक्छ! त्यसैले धेरैजसो वास्तविक व्यापार समस्याहरूको लागि, अनुमानित खोजलाई अनुमति नदिनु उत्तम हुन्छ। अपवाद भनेको हामीले नम्बरहरू खोज्दा पाठ होइन - उदाहरणका लागि, चरण छूटहरू गणना गर्दा।

सबै कुरा! दबाउन बाँकी छ OK र प्रविष्ट गरिएको प्रकार्यलाई सम्पूर्ण स्तम्भमा प्रतिलिपि गर्नुहोस्।

# N / A त्रुटिहरू र तिनीहरूको दमन

समारोह VPR (VLOOKUP) #N/A त्रुटि फर्काउँछ (#N/A) यदि एक:

  • सटीक खोज सक्षम (तर्क अन्तराल दृश्य = ०) र इच्छित नाम छैन तालिका.
  • मोटे खोज समावेश (अन्तराल दृश्य = ०), तर तालिका, जसमा खोजी भइरहेको छ नामहरूको बढ्दो क्रममा क्रमबद्ध गरिएको छैन।
  • कक्षको ढाँचा जहाँ नामको आवश्यक मान आउँछ (उदाहरणका लागि, हाम्रो मामलामा B3) र तालिकाको पहिलो स्तम्भ (F3: F19) को कक्षहरूको ढाँचा फरक छन् (उदाहरणका लागि, संख्यात्मक र पाठ )। पाठ नामहरूको सट्टा संख्यात्मक कोडहरू (खाता नम्बरहरू, पहिचानकर्ताहरू, मितिहरू, आदि) प्रयोग गर्दा यो केस विशेष गरी सामान्य हुन्छ। यस अवस्थामा, तपाईं प्रकार्यहरू प्रयोग गर्न सक्नुहुन्छ Ч и पाठ डाटा ढाँचाहरू रूपान्तरण गर्न। यो केहि यस्तो देखिनेछ:

    =VLOOKUP(पाठ(B3),मूल्य,0)

    तपाईं यहाँ यस बारे थप पढ्न सक्नुहुन्छ।

  • प्रकार्यले आवश्यक मान फेला पार्न सक्दैन किनभने कोडले खाली ठाउँहरू वा अदृश्य गैर-मुद्रणयोग्य क्यारेक्टरहरू (लाइन ब्रेकहरू, आदि) समावेश गर्दछ। यस अवस्थामा, तपाइँ पाठ प्रकार्यहरू प्रयोग गर्न सक्नुहुन्छ TRIM (ट्रिम) и प्रिंट(स्वच्छ) तिनीहरूलाई हटाउन:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),मूल्य,0)

    =VLOOKUP(TRIM(CLEAN(B3));मूल्य;0)

त्रुटि सन्देश दबाउन # N / A (#N/A) घटनाहरूमा जहाँ प्रकार्यले सही मेल फेला पार्न सक्दैन, तपाइँ प्रकार्य प्रयोग गर्न सक्नुहुन्छ IFERROR (IFERROR)... त्यसोभए, उदाहरणका लागि, यो निर्माणले VLOOKUP द्वारा उत्पन्न भएका कुनै पनि त्रुटिहरूलाई रोक्छ र तिनीहरूलाई शून्यसँग बदल्छ:

= IFERROR (VLOOKUP (B3, मूल्य, 2, 0), 0)

= IFERROR (VLOOKUP (B3; मूल्य; 2; 0); 0)

PS

यदि तपाइँलाई एक मान होइन, तर सम्पूर्ण सेट एकैचोटि निकाल्न आवश्यक छ (यदि त्यहाँ धेरै फरकहरू छन्), त्यसपछि तपाइँले एरे सूत्रको साथ शमनाइज गर्नुपर्नेछ। वा Office 365 बाट नयाँ XLOOKUP सुविधा प्रयोग गर्नुहोस्।

 

  • VLOOKUP प्रकार्य (VLOOKUP 2) को सुधारिएको संस्करण।
  • VLOOKUP प्रकार्य प्रयोग गरेर चरण (दायरा) छुटहरूको द्रुत गणना।
  • INDEX र MATCH प्रकार्यहरू प्रयोग गरेर "बायाँ VLOOKUP" कसरी बनाउने
  • VLOOKUP प्रकार्य कसरी प्रयोग गर्ने सूचीबाट डेटाको साथ फारमहरू भर्न
  • पहिलो होइन, तर सबै मानहरू एकैचोटि तालिकाबाट कसरी निकाल्ने
  • PLEX एड-अनबाट VLOOKUP2 र VLOOKUP3 प्रकार्यहरू

 

जवाफ छाड्नुस्