उन्नत VLOOKUP उदाहरणहरू: बहु-मापदण्ड खोज

प्रकार्य मा हाम्रो ट्यूटोरियल को दोस्रो भाग मा VPR (VLOOKUP) Excel मा, हामी केहि उदाहरणहरू विश्लेषण गर्नेछौं जसले तपाईंलाई सबै शक्ति निर्देशित गर्न मद्दत गर्नेछ VPR सबैभन्दा महत्वाकांक्षी एक्सेल कार्यहरू समाधान गर्न। उदाहरणहरूले यो सुविधाले कसरी काम गर्छ भन्ने बारे तपाईंसँग पहिले नै आधारभूत ज्ञान छ भनी मानिन्छ। यदि होइन भने, तपाइँ यस ट्यूटोरियलको पहिलो भागबाट सुरु गर्न इच्छुक हुन सक्नुहुन्छ, जसले वाक्यविन्यास र आधारभूत उपयोगको व्याख्या गर्दछ। VPR। खैर, सुरु गरौं।

• धेरै मापदण्डहरूद्वारा एक्सेलमा खोज्नुहोस्
• हामी VLOOKUP प्रयोग गरेर 2nd, 3rd, आदि मानहरू निकाल्छौं
• वांछित मानका सबै पुनरावृत्तिहरू प्राप्त गर्नुहोस्
• ज्ञात पङ्क्ति र स्तम्भ द्वारा XNUMXD खोज
• एउटै सूत्रमा धेरै VLOOKUPs प्रयोग गर्दै
• विभिन्न तालिकाहरूबाट डाटाको गतिशील प्रतिस्थापन

धेरै मापदण्डहरूद्वारा एक्सेलमा खोज्नुहोस्

समारोह VPR Excel मा डाटाबेस मा एक विशेष मान को लागी खोज प्रदर्शन को लागी एक साँच्चै शक्तिशाली उपकरण हो। यद्यपि, त्यहाँ एक महत्त्वपूर्ण सीमा छ - यसको सिन्ट्याक्सले तपाईंलाई केवल एक मान खोज्न अनुमति दिन्छ। के तपाई बहु सर्तहरू द्वारा खोजी गर्न चाहनुहुन्छ भने? तपाईंले तल समाधान पाउनुहुनेछ।

उदाहरण १: २ फरक मापदण्डद्वारा खोज्नुहोस्

मानौं हामीसँग अर्डरहरूको सूची छ र हामी फेला पार्न चाहन्छौं सामानको मात्रा (मात्रा), दुई मापदण्डमा आधारित - ग्राहक नाम (ग्राहक) र उत्पाद नाम (उत्पादन)। कुरा यस तथ्यले जटिल छ कि प्रत्येक खरीददारहरूले धेरै प्रकारका सामानहरू अर्डर गरे, जस्तै तलको तालिकाबाट देख्न सकिन्छ:

नियमित कार्य VPR यस परिदृश्यमा काम गर्दैन किनभने यसले दिइएको लुकअप मानसँग मेल खाने पहिलो मान फिर्ता गर्नेछ। उदाहरणका लागि, यदि तपाइँ वस्तुको मात्रा जान्न चाहनुहुन्छ भने मिठाईखरिदकर्ता द्वारा आदेश जेरेमी हिल, निम्न सूत्र लेख्नुहोस्:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- यो सूत्र परिणाम फर्काउनेछ 15उत्पादन अनुरूप स्याउ, किनभने यो मेल खाने पहिलो मान हो।

त्यहाँ एक साधारण समाधान छ - एक अतिरिक्त स्तम्भ सिर्जना गर्नुहोस् जसमा सबै इच्छित मापदण्डहरू संयोजन गर्न। हाम्रो उदाहरणमा, यी स्तम्भहरू हुन् ग्राहक नाम (ग्राहक) र उत्पाद नाम (उत्पादन)। नबिर्सनुहोस् कि मर्ज गरिएको स्तम्भ सधैं खोज दायरामा सबैभन्दा बायाँ स्तम्भ हुनुपर्छ, किनकि यो बायाँ स्तम्भ हो जुन प्रकार्य VPR मूल्य खोज्दा माथि देखिन्छ।

त्यसोभए, तपाइँ तालिकामा सहायक स्तम्भ थप्नुहोस् र यसको सबै कक्षहरूमा निम्न सूत्र प्रतिलिपि गर्नुहोस्: =B2&C2। यदि तपाइँ स्ट्रिङ अधिक पढ्न योग्य हुन चाहनुहुन्छ भने, तपाइँ एक ठाउँ संग संयुक्त मान अलग गर्न सक्नुहुन्छ: =B2&» «&C2। त्यस पछि, तपाइँ निम्न सूत्र प्रयोग गर्न सक्नुहुन्छ:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

सेल कहाँ छ B1 तर्क को एकबद्ध मान समावेश गर्दछ look_value (lookup_value) र 4 - तर्क col_index_num (column_number), अर्थात् डेटा समावेश भएको स्तम्भको संख्या।

उदाहरण २: अर्को पानामा तालिका हेरिएको दुई मापदण्डहरूद्वारा VLOOKUP

यदि तपाइँले अर्को पाना वा अर्को एक्सेल कार्यपुस्तिकामा रहेको दोस्रो तालिका (लुकअप तालिका) बाट डाटा थपेर मुख्य तालिका (मुख्य तालिका) अद्यावधिक गर्न आवश्यक छ भने, तपाइँले घुसाउनु भएको सूत्रमा सीधै इच्छित मान सङ्कलन गर्न सक्नुहुन्छ। मुख्य तालिकामा।

अघिल्लो उदाहरणमा जस्तै, तपाईलाई लुकअप तालिकामा संयुक्त मानहरू सहित सहायक स्तम्भ चाहिन्छ। यो स्तम्भ खोज दायरामा सबैभन्दा बायाँ स्तम्भ हुनुपर्छ।

त्यसैले सूत्र संग VPR यो जस्तै हुन सक्छ:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

यहाँ, स्तम्भहरू B र C मा क्रमशः ग्राहक नाम र उत्पादन नामहरू र लिङ्क समावेश छन् अर्डरहरू!$A&$2:$D$2 अर्को पानामा हेर्नको लागि तालिका परिभाषित गर्दछ।

सूत्रलाई थप पढ्न योग्य बनाउन, तपाईंले दृश्य दायरालाई नाम दिन सक्नुहुन्छ, र त्यसपछि सूत्र धेरै सरल देखिनेछ:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

सूत्रले काम गर्नको लागि, तपाईंले हेरिरहनुभएको तालिकाको बायाँपट्टिको स्तम्भमा भएका मानहरू खोजी मापदण्डमा जस्तै ठ्याक्कै मिलाउनु पर्छ। माथिको चित्रमा, हामीले मानहरू जोड्यौं u2bu2band तिनीहरूको बीचमा खाली ठाउँ राख्छौं, जसरी तपाईंले प्रकार्यको पहिलो तर्कमा गर्न आवश्यक छ (BXNUMX& “” & CXNUMX)।

सम्झनु! समारोह VPR 255 वर्णहरूमा सीमित, यसले 255 वर्णहरू भन्दा लामो मान खोज्न सक्दैन। यो मनमा राख्नुहोस् र निश्चित गर्नुहोस् कि वांछित मानको लम्बाइ यो सीमा भन्दा बढी छैन।

म सहमत छु कि सहायक स्तम्भ थप्नु सबैभन्दा सुन्दर र सधैं स्वीकार्य समाधान होइन। तपाईले सहायक स्तम्भ बिना नै गर्न सक्नुहुन्छ, तर त्यसका लागि कार्यहरूको संयोजनको साथ धेरै जटिल सूत्र चाहिन्छ। INDEX (INDEX) र MATCH (अधिक खुलासा)।

हामी VLOOKUP प्रयोग गरेर 2nd, 3rd, आदि मानहरू निकाल्छौं

तपाईलाई पहिले नै थाहा छ VPR केवल एक मिल्दो मान फिर्ता गर्न सक्छ, अधिक स्पष्ट रूपमा, पहिलो फेला पर्यो। तर के हुन्छ यदि यो मान हेरिएको एरेमा धेरै पटक दोहोर्याइएको छ, र तपाइँ ती मध्ये 2nd वा 3rd निकाल्न चाहनुहुन्छ? के हुन्छ यदि सबै मानहरू? समस्या जटिल देखिन्छ, तर समाधान अवस्थित छ!

मानौं तालिकाको एउटा स्तम्भमा ग्राहकहरूको नाम (ग्राहकको नाम) समावेश छ र अर्को स्तम्भमा उनीहरूले खरिद गरेका उत्पादनहरू (उत्पादन) समावेश छन्। दिइएको ग्राहक द्वारा खरिद गरिएको 2nd, 3rd र 4th वस्तुहरू फेला पार्न प्रयास गरौं।

स्तम्भको अगाडि सहायक स्तम्भ थप्नु सबैभन्दा सजिलो तरिका हो ग्राहकको नाम र प्रत्येक नामको पुनरावृत्ति संख्याको साथ ग्राहक नामहरू भर्नुहोस्, उदाहरणका लागि, जोन डो १, जोन डो १ आदि। हामी फंक्शन प्रयोग गरेर नम्बरिङको साथ ट्रिक गर्नेछौं COUNTIF (COUNTIF), ग्राहक नामहरू स्तम्भ B मा छन् भनेर दिइएको छ:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

त्यस पछि तपाईले सामान्य प्रकार्य प्रयोग गर्न सक्नुहुन्छ VPRआवश्यक अर्डर फेला पार्न। उदाहरणका लागि:

• फेला २ औं ग्राहक द्वारा आदेश गरिएको वस्तु दान भूरा:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• फेला २ औं ग्राहक द्वारा आदेश गरिएको वस्तु दान भूरा:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

वास्तवमा, तपाईंले निम्न चित्रमा देखाइए अनुसार पाठको सट्टा लुकअप मानको रूपमा सेल सन्दर्भ प्रविष्ट गर्न सक्नुहुन्छ:

यदि तपाईं मात्र खोज्दै हुनुहुन्छ भने पहिलो पुनरावृत्ति, तपाईं थप जटिल सूत्र सिर्जना गरेर सहायक स्तम्भ बिना गर्न सक्नुहुन्छ:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

यस सूत्रमा:

• $F$2 - खरिदकर्ताको नाम भएको सेल (यो अपरिवर्तित छ, कृपया ध्यान दिनुहोस् - लिङ्क निरपेक्ष छ);
• $ B $ - स्तम्भ ग्राहकको नाम;
• तालिका 4 - तपाईंको टेबल (यो ठाउँ नियमित दायरा पनि हुन सक्छ);
• $ C16 - तपाईंको तालिका वा दायराको अन्तिम कक्ष।

यो सूत्रले दोस्रो मिल्दो मान मात्र फेला पार्छ। यदि तपाईंलाई बाँकी पुनरावृत्तिहरू निकाल्न आवश्यक छ भने, अघिल्लो समाधान प्रयोग गर्नुहोस्।

यदि तपाईंलाई सबै मिल्दोहरूको सूची चाहिन्छ - प्रकार्य VPR यो एक सहायक होइन, किनकि यसले एक समयमा एक मान मात्र फर्काउँछ - अवधि। तर एक्सेलको एक प्रकार्य छ INDEX (INDEX), जसले सजिलै यो कार्य सामना गर्न सक्छ। यस्तो सूत्र कस्तो देखिन्छ, तपाइँ निम्न उदाहरणमा सिक्नुहुनेछ।

वांछित मानका सबै पुनरावृत्तिहरू प्राप्त गर्नुहोस्

माथि उल्लेखित रूपमा VPR स्क्यान गरिएको दायराबाट सबै डुप्लिकेट मानहरू निकाल्न सक्दैन। यो गर्नको लागि, तपाईलाई थोरै जटिल सूत्र चाहिन्छ, धेरै एक्सेल प्रकार्यहरू मिलेर बनेको, जस्तै INDEX (INDEX), सानो (सानो) र पङ्क्ति (लाइन)

उदाहरणका लागि, तलको सूत्रले B2:B2 दायरामा कक्ष F16 बाट मानका सबै पुनरावृत्तिहरू फेला पार्छ र स्तम्भ C मा उही पङ्क्तिहरूबाट परिणाम फर्काउँछ।

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

यो एरे सूत्र धेरै नजिकका कक्षहरूमा प्रविष्ट गर्नुहोस्, जस्तै कक्षहरू F4: F8तल चित्रमा देखाइएको रूपमा। कक्षहरूको संख्या खोजी गरिएको मानको दोहोरिएको अधिकतम सम्भावित संख्याको बराबर वा बढी हुनुपर्छ। क्लिक गर्न नबिर्सनुहोस् Ctrl+Shift+Enterएरे सूत्र सही रूपमा प्रविष्ट गर्न।

यदि तपाइँ यो कसरी काम गर्दछ भनेर बुझ्नमा रुचि राख्नुहुन्छ भने, सूत्रको विवरणमा थोरै डुबौं:

भाग 1:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - B2:B2 दायराका प्रत्येक मानहरूसँग कक्ष F16 मा मान तुलना गर्नुहोस्। यदि एक मिल्दो फेला पर्यो भने, अभिव्यक्ति STRING(C2:C16)-1 सम्बन्धित रेखाको संख्या फर्काउँछ (मान -1 तपाईंलाई हेडर लाइन समावेश नगर्न अनुमति दिन्छ)। यदि कुनै मिल्दो छैन भने, प्रकार्य IF (IF) खाली स्ट्रिङ फर्काउँछ।

कार्य परिणाम IF (IF) त्यहाँ यस्तो तेर्सो एरे हुनेछ: {1,"",3,"",5,"","","","","","",12,"","",""}

भाग 2:

ROW()-3

СТРОКА()-3

यहाँ समारोह पङ्क्ति (लाइन) अतिरिक्त काउन्टरको रूपमा कार्य गर्दछ। सूत्र F4:F9 कक्षहरूमा प्रतिलिपि गरिएको हुनाले, हामी संख्या घटाउँछौं 3 प्रकार्य परिणामबाट मूल्य प्राप्त गर्न 1 सेल मा F4 (लाइन ४, घटाउ ३) प्राप्त गर्न 2 सेल मा F5 (लाइन ५, घटाउ ३) र यस्तै।

भाग 3:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

समारोह सानो (SMALL) फर्काउँछ n-ओह डाटा एरेमा सबैभन्दा सानो मान। हाम्रो अवस्थामा, कुन स्थिति (सबैभन्दा सानोबाट) फर्काउने कार्यद्वारा निर्धारण गरिन्छ पङ्क्ति (लाइन) (भाग २ हेर्नुहोस्)। त्यसोभए, सेलको लागि F4 समारोह SMALL({array},1) फर्काउँछ २ औं (सबैभन्दा सानो) एरे तत्व, अर्थात् 1। सेल को लागी F5 फर्काउँछ २ औं array मा सबैभन्दा सानो तत्व, त्यो हो 3, आदि

भाग 4:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

समारोह INDEX (INDEX) ले एर्रेमा एउटा विशेष सेलको मान मात्र फर्काउँछ C2:C16। सेल को लागी F4 समारोह INDEX($C$2:$C$16) फर्कने छौ, फर्कने छन् स्याउलागि F5 समारोह INDEX($C$2:$C$16) फर्कने छौ, फर्कने छन् मिठाई र यति मा।

भाग 5:

IFERROR()

ЕСЛИОШИБКА()

अन्तमा, हामी प्रकार्य भित्र सूत्र राख्छौं IFERROR (IFERROR), किनभने तपाईं त्रुटि सन्देशसँग खुसी हुने सम्भावना छैन #AT (#N/A) यदि सूत्र प्रतिलिपि गरिएको कक्षहरूको संख्या हेरिएको दायरामा डुप्लिकेट मानहरूको संख्या भन्दा कम छ।

ज्ञात पङ्क्ति र स्तम्भ द्वारा XNUMXD खोज

एक्सेलमा XNUMXD खोज प्रदर्शन गर्दा ज्ञात पङ्क्ति र स्तम्भ नम्बर द्वारा मान खोज्नु समावेश छ। अन्य शब्दहरूमा, तपाइँ एक विशेष पङ्क्ति र स्तम्भको प्रतिच्छेदनमा सेल मान निकाल्दै हुनुहुन्छ।

त्यसोभए, हाम्रो तालिकामा फर्कौं र प्रकार्यको साथ सूत्र लेखौं VPR, जसले मार्चमा बेचेको कागतीको मूल्यको बारेमा जानकारी पाउनेछ।

त्यहाँ XNUMXD खोज प्रदर्शन गर्न धेरै तरिकाहरू छन्। विकल्पहरू जाँच गर्नुहोस् र आफूलाई उपयुक्त हुने एउटा छान्नुहोस्।

VLOOKUP र MATCH प्रकार्यहरू

तपाईं प्रकार्यहरूको गुच्छा प्रयोग गर्न सक्नुहुन्छ VPR (VLOOKUP) र थप खुलासा (MATCH) क्षेत्रहरूको प्रतिच्छेदनमा मान फेला पार्न उत्पाद नाम (स्ट्रिङ) र महिना (स्तम्भ) प्रश्नमा array को:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

माथिको सूत्र एक नियमित प्रकार्य हो VPR, जसले A2 बाट A9 कक्षहरूमा मान "लेमन" को सही मिलान खोज्छ। तर तपाईलाई थाहा छैन कि मार्चको बिक्री कुन स्तम्भमा छ, तपाईले तेस्रो प्रकार्य तर्कको लागि स्तम्भ नम्बर सेट गर्न सक्षम हुनुहुने छैन। VPR। यसको सट्टा, प्रकार्य प्रयोग गरिन्छ थप खुलासायो स्तम्भ परिभाषित गर्न।

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

मानव भाषामा अनुवाद गरिएको, यो सूत्रको अर्थ:

• हामी क्यारेक्टरहरू खोजिरहेका छौं "मार" - तर्क look_value (lookup_value);
• A1 देखि I1 सम्म कक्षहरूमा हेर्दै - तर्क lookup_array (lookup_array);
• सटीक मिलान फर्काउँदै - तर्क match_type (match_type)।

प्रयोग 0 तेस्रो तर्कमा, तपाइँ कार्यहरू भन्नुहुन्छ थप खुलासा तपाईंले खोजिरहनु भएको मानसँग ठ्याक्कै मिल्ने पहिलो मान खोज्नुहोस्। यो मूल्य बराबर छ गलत (FALSE) चौथो तर्कको लागि VPR.

यसरी तपाईले एक्सेलमा दुई-तर्फी खोज सूत्र सिर्जना गर्न सक्नुहुन्छ, जसलाई द्वि-आयामी खोज वा द्विदिशात्मक खोज पनि भनिन्छ।

SUMPRODUCT प्रकार्य

समारोह SUMPRODUCT (SUMPRODUCT) चयन गरिएका एरेका उत्पादनहरूको योगफल फर्काउँछ:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX र MATCH कार्यहरू

अर्को लेखमा म यी प्रकार्यहरू विस्तृत रूपमा व्याख्या गर्नेछु, त्यसैले अहिलेको लागि तपाईंले यो सूत्र प्रतिलिपि गर्न सक्नुहुन्छ:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

नाम दिइएको दायरा र प्रतिच्छेदन अपरेटर

यदि तपाइँ ती सबै जटिल एक्सेल सूत्रहरूमा हुनुहुन्न भने, तपाइँलाई यो दृश्य र यादगार तरिका मन पर्न सक्छ:

1. तालिका चयन गर्नुहोस्, ट्याब खोल्नुहोस् सूत्रहरू (सूत्रहरू) र क्लिक गर्नुहोस् चयनबाट सिर्जना गर्नुहोस् (चयनबाट सिर्जना गर्नुहोस्)।
2. बक्सहरू जाँच गर्नुहोस् शीर्ष पङ्क्ति (माथिको रेखामा) र बायाँ स्तम्भ (बाँया स्तम्भमा)। Microsoft Excel ले तपाईंको स्प्रिेडसिटको शीर्ष पङ्क्ति र बायाँ स्तम्भमा मानहरूबाट दायराहरूलाई नामहरू तोक्ने छ। अब तपाईं सूत्रहरू सिर्जना नगरी यी नामहरू प्रयोग गरेर खोज्न सक्नुहुन्छ।
3. कुनै पनि खाली कक्षमा लेख्नुहोस् =row_name column_name, उदाहरण को लागी यो जस्तै:

   = लेमन मार

   ... वा उल्टो:

   = मार कागती

   याद गर्नुहोस् कि पङ्क्ति र स्तम्भ नामहरू स्पेसद्वारा छुट्याउनुपर्दछ, जुन यस अवस्थामा प्रतिच्छेदन अपरेटर जस्तै काम गर्दछ।

जब तपाइँ एक नाम प्रविष्ट गर्नुहुन्छ, Microsoft Excel ले मिल्दो नामहरूको सूचीको साथ एक टूलटिप देखाउनेछ, जस्तै तपाइँ एक सूत्र प्रविष्ट गर्दा।

4. प्रेस प्रविष्ट गर्नुहोस् र परिणाम जाँच गर्नुहोस्

सामान्यतया, तपाईंले माथिका जुनसुकै विधिहरू रोज्नुहुन्छ, दुई-आयामी खोजको परिणाम समान हुनेछ:

एउटै सूत्रमा धेरै VLOOKUPs प्रयोग गर्दै

यस्तो हुन्छ कि मुख्य तालिका र लुकअप तालिकामा एउटै स्तम्भ साझा छैन, र यसले तपाईंलाई सामान्य प्रकार्य प्रयोग गर्नबाट रोक्छ। VPR। यद्यपि, त्यहाँ अर्को तालिका छ जसमा हामीले रुचि राखेको जानकारी समावेश गर्दैन, तर मुख्य तालिका र लुकअप तालिकासँग साझा स्तम्भ छ।

निम्न उदाहरणमा हेरौं। हामीसँग स्तम्भ भएको मुख्य तालिका छ SKU (नयाँ), जहाँ तपाइँ अर्को तालिकाबाट सम्बन्धित मूल्यहरूसँग स्तम्भ थप्न चाहनुहुन्छ। थप रूपमा, हामीसँग 2 लुकअप तालिकाहरू छन्। पहिलो (लुकअप तालिका 1) मा अद्यावधिक नम्बरहरू समावेश छन् SKU (नयाँ) र उत्पादन नामहरू, र दोस्रो (लुकअप तालिका 2) - उत्पादन नामहरू र पुराना नम्बरहरू SKU (पुरानो).

दोस्रो लुकअप तालिकाबाट मुख्य तालिकामा मूल्यहरू थप्नको लागि, तपाईंले डबल रूपमा चिनिने कार्य गर्नु पर्छ VPR वा नेस्टेड VPR.

1. एक प्रकार्य लेख्नुहोस् VPR, जसले तालिकामा उत्पादनको नाम फेला पार्छ खोज तालिका 1प्रयोग SKU, वांछित मानको रूपमा:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   यहाँ नयाँ_SKU - नामित दायरा $A:$B तालिकामा खोज तालिका 1, 2 - यो स्तम्भ बी हो, जसमा सामानहरूको नाम समावेश छ (माथिको चित्र हेर्नुहोस्)

2. तालिकाबाट मूल्यहरू सम्मिलित गर्न सूत्र लेख्नुहोस् खोज तालिका 2 प्रसिद्ध उत्पादन नामहरूमा आधारित। यो गर्नको लागि, नयाँ प्रकार्यको लागि लुकअप मानको रूपमा तपाईंले पहिले सिर्जना गर्नुभएको सूत्र टाँस्नुहोस् VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   यहाँ मूल्य - नामित दायरा $A:$C तालिकामा खोज तालिका 2, 3 स्तम्भ C मूल्यहरू समावेश छ।

तलको चित्रले हामीले सिर्जना गरेको सूत्रद्वारा फर्काइएको नतिजा देखाउँछ:

VLOOKUP र INDIRECT प्रयोग गरी विभिन्न तालिकाहरूबाट डाटाको गतिशील प्रतिस्थापन

पहिले, हामीले एकअर्कालाई सही रूपमा बुझेका छौं भनी सुनिश्चित गर्नको लागि "विभिन्न तालिकाहरूबाट डाटाको गतिशील प्रतिस्थापन" अभिव्यक्तिबाट हामीले के बुझाउँछौं भनेर स्पष्ट गरौं।

त्यहाँ परिस्थितिहरू छन् जब त्यहाँ एउटै ढाँचाको डेटाको साथ धेरै पानाहरू छन्, र निश्चित पानाबाट आवश्यक जानकारी निकाल्न आवश्यक छ, दिइएको कक्षमा प्रविष्ट गरिएको मानको आधारमा। मलाई लाग्छ कि यो एक उदाहरण संग व्याख्या गर्न सजिलो छ।

कल्पना गर्नुहोस् कि तपाईंसँग एउटै उत्पादनहरू र एउटै ढाँचामा धेरै क्षेत्रहरूको लागि बिक्री रिपोर्टहरू छन्। तपाइँ एक विशेष क्षेत्र को लागी बिक्री तथ्याङ्क खोज्न चाहनुहुन्छ:

यदि तपाइँसँग केवल दुईवटा त्यस्ता रिपोर्टहरू छन् भने, तपाइँ प्रकार्यहरूको साथ एक अपमानजनक सरल सूत्र प्रयोग गर्न सक्नुहुन्छ VPR и IF (IF) खोजी गर्न वांछित रिपोर्ट चयन गर्न:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

जहाँ:

• $ D $ १ उत्पादनको नाम भएको सेल हो। नोट गर्नुहोस् कि हामी अन्य कक्षहरूमा सूत्र प्रतिलिपि गर्दा लुकअप मान परिवर्तन गर्नबाट बच्न यहाँ निरपेक्ष सन्दर्भहरू प्रयोग गर्छौं।
• $D3 क्षेत्रको नाम भएको सेल हो। हामी एक निरपेक्ष स्तम्भ सन्दर्भ र एक सापेक्ष पङ्क्ति सन्दर्भ प्रयोग गर्दैछौं किनभने हामी समान स्तम्भमा अन्य कक्षहरूमा सूत्र प्रतिलिपि गर्ने योजना बनाउँछौं।
• FL_Sales и CA_बिक्री - तालिकाका नामहरू (वा नाम गरिएका दायराहरू) जसमा सम्बन्धित बिक्री रिपोर्टहरू छन्। तपाईं, अवश्य पनि, सामान्य पाना नामहरू र सेल दायरा सन्दर्भहरू प्रयोग गर्न सक्नुहुन्छ, उदाहरणका लागि 'FL पाना'!$A$3:$B$10, तर नामित दायराहरू धेरै सुविधाजनक छन्।

यद्यपि, जब त्यहाँ धेरै तालिकाहरू छन्, प्रकार्य IF उत्तम समाधान होइन। बरु, तपाईं प्रकार्य प्रयोग गर्न सक्नुहुन्छ अप्रत्यक्ष (अप्रत्यक्ष) इच्छित खोज दायरा फर्काउन।

तपाईलाई थाहा छ, प्रकार्य अप्रत्यक्ष पाठ स्ट्रिङद्वारा दिइएको लिङ्क फिर्ता गर्न प्रयोग गरिन्छ, जुन हामीलाई अहिले चाहिने कुरा हो। त्यसोभए, साहसपूर्वक माथिको सूत्रमा अभिव्यक्तिलाई प्रकार्यसँग बदल्नुहोस् IF प्रकार्य संग लिङ्क गर्न अप्रत्यक्ष। यहाँ एक संयोजन छ VPR и अप्रत्यक्ष संग राम्रो काम गर्दछ:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

जहाँ:

• $ D $ १ - यो उत्पादनको नाम भएको सेल हो, यो निरपेक्ष लिङ्कको कारण अपरिवर्तित छ।
• $D3 क्षेत्र नामको पहिलो भाग भएको कक्ष हो। हाम्रो उदाहरणमा, यो FL.
• _बिक्री - सबै नामित दायरा वा तालिकाहरूको नामको साझा भाग। सेल D3 मा मानसँग जोड्दा, यसले आवश्यक दायराको पूर्ण रूपमा योग्य नाम बनाउँछ। तल समारोहमा नयाँ भएकाहरूका लागि केही विवरणहरू छन् अप्रत्यक्ष.

कसरी INDIRECT र VLOOKUP ले काम गर्छ

पहिले, म तपाईंलाई प्रकार्यको वाक्य रचना सम्झाउन दिनुहोस् अप्रत्यक्ष (अप्रत्यक्ष):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

पहिलो तर्क सेल सन्दर्भ (A1 वा R1C1 शैली), दायरा नाम, वा पाठ स्ट्रिङ हुन सक्छ। दोस्रो तर्कले पहिलो तर्कमा लिङ्कको कुन शैली समावेश छ भनेर निर्धारण गर्दछ:

• A1यदि तर्क हो सही कोड (TRUE) वा निर्दिष्ट गरिएको छैन;
• R1C1, यदि Fएएस ई (FALSE)।

हाम्रो अवस्थामा, लिङ्कको शैली छ A1, त्यसैले तपाईंले दोस्रो तर्क छोडेर पहिलोमा फोकस गर्न सक्नुहुन्छ।

त्यसोभए हाम्रो बिक्री रिपोर्टहरूमा फर्कौं। यदि तपाइँ सम्झनुहुन्छ भने, त्यसपछि प्रत्येक रिपोर्ट एक अलग पाना मा स्थित एक अलग तालिका हो। सूत्र ठीकसँग काम गर्नको लागि, तपाईंले आफ्नो तालिकाहरू (वा दायराहरू) नाम दिनै पर्छ, र सबै नामहरूमा साझा भाग हुनुपर्छ। उदाहरण को लागी, यो जस्तै: CA_बिक्री, FL_Sales, TX_बिक्री र यस्तै। तपाईले देख्न सक्नुहुन्छ, "_सेल्स" सबै नामहरूमा अवस्थित छ।

समारोह अप्रत्यक्ष स्तम्भ D र पाठ स्ट्रिङ "_Sales" मा रहेको मानलाई जोड्छ, यसरी बताउँछ VPR कुन तालिकामा खोज्न। यदि सेल D3 मा "FL" मान समावेश छ भने, सूत्रले तालिका खोज्नेछ FL_Sales, यदि "CA" - तालिकामा CA_बिक्री र यति मा।

कार्यहरूको परिणाम VPR и अप्रत्यक्ष निम्न हुनेछ:

यदि डाटा विभिन्न एक्सेल पुस्तकहरूमा अवस्थित छ भने, तपाईंले नाम गरिएको दायरा अघि पुस्तकको नाम थप्न आवश्यक छ, उदाहरणका लागि:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

यदि समारोह अप्रत्यक्ष अर्को कार्यपुस्तिकालाई बुझाउँछ, त्यो कार्यपुस्तिका खुला हुनुपर्छ। यदि यो बन्द छ भने, प्रकार्यले त्रुटि रिपोर्ट गर्नेछ। #REF! (#SSYL!)