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

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

• एक्सेल में कई मानदंडों द्वारा खोजें
• हम VLOOKUP . का उपयोग करके दूसरा, तीसरा, आदि मान निकालते हैं
• वांछित मूल्य के सभी पुनरावृत्तियों को पुनः प्राप्त करें
• ज्ञात पंक्ति और स्तंभ द्वारा XNUMXD खोज
• एक सूत्र में अनेक VLOOKUP का उपयोग करना
• विभिन्न तालिकाओं से डेटा का गतिशील प्रतिस्थापन

एक्सेल में कई मानदंडों द्वारा खोजें

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

उदाहरण 1: 2 अलग-अलग मानदंडों के आधार पर खोजें

मान लीजिए हमारे पास आदेशों की एक सूची है और हम खोजना चाहते हैं माल की मात्रा (मात्रा), दो मानदंडों के आधार पर - ग्राहक नाम (ग्राहक) और उत्पाद का नाम (उत्पाद)। मामला इस तथ्य से जटिल है कि प्रत्येक खरीदार ने कई प्रकार के सामानों का ऑर्डर दिया, जैसा कि नीचे दी गई तालिका से देखा जा सकता है:

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

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

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

- यह सूत्र परिणाम लौटाएगा 15उत्पाद के अनुरूप सेब, क्योंकि यह पहला मान है जो मेल खाता है।

एक सरल समाधान है - एक अतिरिक्त कॉलम बनाएं जिसमें सभी वांछित मानदंडों को संयोजित किया जाए। हमारे उदाहरण में, ये कॉलम हैं ग्राहक नाम (ग्राहक) और उत्पाद का नाम (उत्पाद)। यह न भूलें कि मर्ज किए गए कॉलम को हमेशा सर्च रेंज में सबसे बाएं कॉलम होना चाहिए, क्योंकि यह बाएं कॉलम है जो फ़ंक्शन VPR मूल्य की तलाश करते समय दिखता है।

तो, आप तालिका में एक सहायक कॉलम जोड़ते हैं और इसके सभी कक्षों पर निम्न सूत्र की प्रतिलिपि बनाते हैं: =बी2 और सी2. यदि आप चाहते हैं कि स्ट्रिंग अधिक पठनीय हो, तो आप संयुक्त मानों को एक स्थान से अलग कर सकते हैं: =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 तर्क का संक्षिप्त मूल्य शामिल है पता लगाने का मूल्य (लुकअप_वैल्यू) और 4 - बहस col_index_num (कॉलम_नंबर), यानी उस कॉलम की संख्या जिसमें डेटा को पुनर्प्राप्त किया जाना है।

उदाहरण 2: दो मानदंडों के अनुसार VLOOKUP तालिका को दूसरी शीट पर देखा जा रहा है

यदि आपको दूसरी तालिका (लुकअप तालिका) से डेटा जोड़कर मुख्य तालिका (मुख्य तालिका) को अपडेट करने की आवश्यकता है, जो किसी अन्य शीट पर या किसी अन्य एक्सेल कार्यपुस्तिका में स्थित है, तो आप सीधे उस सूत्र में वांछित मान एकत्र कर सकते हैं जिसे आप सम्मिलित करते हैं मुख्य तालिका में।

पिछले उदाहरण की तरह, आपको लुकअप तालिका में संयुक्त मानों के साथ एक सहायक कॉलम की आवश्यकता होगी। यह स्तंभ खोज श्रेणी में सबसे बाईं ओर का स्तंभ होना चाहिए।

तो सूत्र के साथ VPR इस तरह हो सकता है:

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

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

यहां, कॉलम बी और सी में क्रमशः ग्राहक नाम और उत्पाद के नाम और लिंक शामिल हैं आदेश!$A&$2:$D$2 किसी अन्य शीट में देखने के लिए तालिका को परिभाषित करता है।

सूत्र को अधिक पठनीय बनाने के लिए, आप दृश्य श्रेणी को एक नाम दे सकते हैं, और तब सूत्र बहुत सरल दिखाई देगा:

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

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

सूत्र के काम करने के लिए, आप जिस तालिका को देख रहे हैं उसके सबसे बाएं कॉलम में मानों को ठीक उसी तरह से जोड़ा जाना चाहिए जैसे खोज मानदंड में। ऊपर दिए गए चित्र में, हमने u2bu2band के मानों को उनके बीच एक स्थान रखा है, उसी तरह आपको फ़ंक्शन के पहले तर्क (BXNUMX& “” & CXNUMX) में करने की आवश्यकता है।

याद है! समारोह VPR 255 वर्णों तक सीमित, यह 255 वर्णों से अधिक लंबे मान की खोज नहीं कर सकता है। इसे ध्यान में रखें और सुनिश्चित करें कि वांछित मान की लंबाई इस सीमा से अधिक नहीं है।

मैं सहमत हूं कि एक सहायक कॉलम जोड़ना सबसे सुरुचिपूर्ण और हमेशा स्वीकार्य समाधान नहीं है। आप हेल्पर कॉलम के बिना भी ऐसा कर सकते हैं, लेकिन इसके लिए कार्यों के संयोजन के साथ अधिक जटिल सूत्र की आवश्यकता होगी सूचकांक (इंडेक्स) और मैच (अधिक उजागर)।

हम VLOOKUP . का उपयोग करके दूसरा, तीसरा, आदि मान निकालते हैं

आप पहले से ही जानते हैं VPR केवल एक मिलान मान लौटा सकता है, अधिक सटीक रूप से, पहला पाया गया। लेकिन क्या होगा यदि यह मान देखे गए सरणी में कई बार दोहराया जाता है, और आप उनमें से दूसरा या तीसरा निकालना चाहते हैं? क्या होगा यदि सभी मूल्य? समस्या जटिल लगती है, लेकिन समाधान मौजूद है!

मान लीजिए तालिका के एक कॉलम में ग्राहकों (ग्राहक का नाम) के नाम हैं, और दूसरे कॉलम में उनके द्वारा खरीदे गए उत्पाद (उत्पाद) हैं। आइए किसी दिए गए ग्राहक द्वारा खरीदे गए दूसरे, तीसरे और चौथे आइटम को खोजने का प्रयास करें।

सबसे आसान तरीका है कि कॉलम से पहले एक सहायक कॉलम जोड़ा जाए ग्राहक का नाम और इसे प्रत्येक नाम की पुनरावृत्ति संख्या के साथ ग्राहक नामों से भरें, उदाहरण के लिए, जॉन डो1, जॉन डो2 आदि। हम फ़ंक्शन का उपयोग करके नंबरिंग के साथ चाल करेंगे COUNTIF (COUNTIF), यह देखते हुए कि ग्राहक के नाम कॉलम B में हैं:

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

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

उसके बाद आप सामान्य फ़ंक्शन का उपयोग कर सकते हैं VPRआवश्यक आदेश खोजने के लिए। उदाहरण के लिए:

• खोज 2-वें ग्राहक द्वारा ऑर्डर की गई वस्तु डैन ब्राउन:

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

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

• खोज 3-वें ग्राहक द्वारा ऑर्डर की गई वस्तु डैन ब्राउन:

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

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

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

यदि आप केवल ढूंढ रहे हैं 2-ए पुनरावृत्ति, आप इसे अधिक जटिल सूत्र बनाकर सहायक कॉलम के बिना कर सकते हैं:

=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;ИСТИНА);"")

इस सूत्र में:

• $एफ$2 - खरीदार का नाम वाला एक सेल (यह अपरिवर्तित है, कृपया ध्यान दें - लिंक पूर्ण है);
• $ बी $ - कॉलम ग्राहक का नाम;
• Table4 - आपकी टेबल (यह जगह एक नियमित रेंज भी हो सकती है);
• $ सी 16 - आपकी टेबल या रेंज का अंतिम सेल।

यह सूत्र केवल दूसरा मिलान मान ढूँढता है। यदि आपको शेष दोहराव निकालने की आवश्यकता है, तो पिछले समाधान का उपयोग करें।

यदि आपको सभी मैचों की सूची की आवश्यकता है - फ़ंक्शन VPR यह सहायक नहीं है, क्योंकि यह एक समय-अवधि में केवल एक मान लौटाता है। लेकिन एक्सेल का एक फंक्शन है सूचकांक (INDEX), जो आसानी से इस कार्य का सामना कर सकता है। ऐसा सूत्र कैसा दिखेगा, यह आप निम्न उदाहरण में जानेंगे।

वांछित मूल्य के सभी पुनरावृत्तियों को पुनः प्राप्त करें

जैसा कि ऊपर उल्लेख किया गया है VPR स्कैन की गई सीमा से सभी डुप्लिकेट मान नहीं निकाल सकते। ऐसा करने के लिए, आपको कुछ अधिक जटिल सूत्र की आवश्यकता है, जो कई एक्सेल फ़ंक्शंस से बना है, जैसे कि सूचकांक (अनुक्रमणिका), छोटे (छोटा) और आरओडब्ल्यू (रेखा)

उदाहरण के लिए, नीचे दिया गया सूत्र 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));"")}

इस सरणी सूत्र को अनेक आसन्न कक्षों, जैसे कक्षों में दर्ज करें एफ4: एफ8जैसा कि नीचे चित्र में दिखाया गया है। कक्षों की संख्या खोजे गए मान की पुनरावृत्तियों की अधिकतम संभव संख्या के बराबर या उससे अधिक होनी चाहिए। क्लिक करना न भूलें Ctrl + Shift + Enterसरणी सूत्र को सही ढंग से दर्ज करने के लिए।

यदि आप यह समझने में रुचि रखते हैं कि यह कैसे काम करता है, तो आइए सूत्र के विवरण में थोड़ा गोता लगाएँ:

भाग 1:

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

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

$F$2=B2:B16 - सेल F2 में मान की तुलना B2:B16 श्रेणी के प्रत्येक मान से करें। यदि एक मिलान पाया जाता है, तो व्यंजक स्ट्रिंग(सी2:सी16)-1 संबंधित पंक्ति की संख्या लौटाता है (मान -1 आपको हेडर लाइन शामिल नहीं करने की अनुमति देता है)। यदि कोई मिलान नहीं है, तो फ़ंक्शन IF (आईएफ) एक खाली स्ट्रिंग देता है।

समारोह परिणाम IF (आईएफ) ऐसी क्षैतिज सरणी होगी: {1,"",3,"",5,"","","","","","",12,"","",""}

भाग 2:

ROW()-3

СТРОКА()-3

यहाँ समारोह आरओडब्ल्यू (लाइन) एक अतिरिक्त काउंटर के रूप में कार्य करता है। चूंकि सूत्र F4:F9 कक्षों में कॉपी किया गया है, हम संख्या घटाते हैं 3 फ़ंक्शन परिणाम से मूल्य प्राप्त करने के लिए 1 सेल में F4 (पंक्ति 4, घटाना 3) प्राप्त करने के लिए 2 सेल में F5 (पंक्ति 5, घटाना 3) इत्यादि।

भाग 3:

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

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

समारोह छोटे (छोटा) रिटर्न एन-ओह डेटा सरणी में सबसे छोटा मान। हमारे मामले में, कौन सी स्थिति (सबसे छोटी से) वापसी के लिए फ़ंक्शन द्वारा निर्धारित की जाती है आरओडब्ल्यू (लाइन) (भाग 2 देखें)। तो, एक सेल के लिए F4 समारोह छोटा({सरणी},1) रिटर्न 1-वें (सबसे छोटा) सरणी तत्व, अर्थात 1. सेल के लिए F5 रिटर्न 2-वें सरणी में सबसे छोटा तत्व, वह है 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) किसी सरणी में किसी विशिष्ट सेल का मान देता है सी 2: सी 16. सेल के लिए F4 समारोह सूचकांक ($C$2:$C$16) वापस होगा सेबके लिए F5 समारोह सूचकांक ($C$2:$C$16) वापस होगा मीठा' और इतना पर.

भाग 5:

IFERROR()

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

अंत में, हम फंक्शन के अंदर फॉर्मूला डालते हैं IFERROR (IFERROR), क्योंकि आपको त्रुटि संदेश से प्रसन्न होने की संभावना नहीं है #एटी (#N/A) यदि उन कक्षों की संख्या जिनमें सूत्र की प्रतिलिपि बनाई गई है, देखी जा रही श्रेणी में डुप्लिकेट मानों की संख्या से कम है।

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

Excel में XNUMXD खोज करने में किसी ज्ञात पंक्ति और स्तंभ संख्या द्वारा मान की खोज करना शामिल है। दूसरे शब्दों में, आप किसी विशेष पंक्ति और स्तंभ के प्रतिच्छेदन पर सेल मान निकाल रहे हैं।

तो, आइए अपनी तालिका की ओर मुड़ें और एक फ़ंक्शन के साथ एक सूत्र लिखें VPRजिसमें मार्च में बिकने वाले नींबू की कीमत की जानकारी मिलेगी।

XNUMXD खोज करने के कई तरीके हैं। विकल्पों की जाँच करें और वह चुनें जो आपको सबसे अच्छा लगे।

VLOOKUP और MATCH फ़ंक्शन

आप कार्यों के एक समूह का उपयोग कर सकते हैं VPR (VLOOKUP) और अधिक उजागर (MATCH) फ़ील्ड के चौराहे पर मान ज्ञात करने के लिए उत्पाद का नाम (स्ट्रिंग) और महीना (स्तंभ) प्रश्न में सरणी का:

=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)

मानव भाषा में अनुवादित, इस सूत्र का अर्थ है:

• हम "मार्च" के पात्रों की तलाश कर रहे हैं - तर्क पता लगाने का मूल्य (पता लगाने का मूल्य);
• A1 से I1 तक की कोशिकाओं में देख रहे हैं - तर्क देखें_श्रेणी (लुकअप_एरे);
• सटीक मिलान लौटाना - तर्क मिलान के प्रकार (मिलान के प्रकार)।

का प्रयोग 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. किसी भी खाली सेल में लिखें = पंक्ति_नाम स्तंभ_नाम, उदाहरण के लिए इस तरह:

   =नींबू मार

   … या ठीक इसके विपरीत:

   = मार लेमन्स

   याद रखें कि पंक्ति और स्तंभ नामों को एक स्थान से अलग किया जाना चाहिए, जो इस मामले में प्रतिच्छेदन ऑपरेटर की तरह काम करता है।

जब आप कोई नाम दर्ज करते हैं, तो Microsoft Excel मेल खाने वाले नामों की सूची के साथ एक टूलटिप दिखाएगा, ठीक वैसे ही जब आप कोई सूत्र दर्ज करते हैं।

4. दबाएँ दर्ज और परिणाम की जांच करें

सामान्य तौर पर, आप उपरोक्त विधियों में से जो भी चुनते हैं, द्वि-आयामी खोज का परिणाम वही होगा:

एक सूत्र में अनेक VLOOKUP का उपयोग करना

ऐसा होता है कि मुख्य तालिका और लुकअप तालिका में एक भी स्तंभ समान नहीं होता है, और यह आपको सामान्य फ़ंक्शन का उपयोग करने से रोकता है VPR. हालाँकि, एक और तालिका है जिसमें वह जानकारी नहीं है जिसमें हम रुचि रखते हैं, लेकिन मुख्य तालिका और लुकअप तालिका के साथ एक सामान्य कॉलम है।

आइए निम्नलिखित उदाहरण पर एक नज़र डालें। हमारे पास एक कॉलम के साथ एक मुख्य तालिका है एसकेयू (नया), जहां आप किसी अन्य तालिका से संबंधित मूल्यों के साथ एक कॉलम जोड़ना चाहते हैं। इसके अलावा, हमारे पास 2 लुकअप टेबल हैं। पहले वाले (लुकअप टेबल 1) में अपडेटेड नंबर होते हैं एसकेयू (नया) और उत्पाद के नाम, और दूसरा (लुकअप तालिका 2) - उत्पाद के नाम और पुराने नंबर एसकेयू (पुराना).

दूसरी लुकअप तालिका से मुख्य तालिका में मूल्य जोड़ने के लिए, आपको एक क्रिया करनी होगी जिसे डबल के रूप में जाना जाता है VPR या नेस्टेड VPR.

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

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   यहाँ नया_एसकेयू - नामित श्रेणी $ए:$बी तालिका में लुकअप टेबल 1, 2 - यह कॉलम बी है, जिसमें सामान के नाम हैं (ऊपर चित्र देखें)

2. तालिका से मूल्य सम्मिलित करने का सूत्र लिखिए लुकअप टेबल 2 प्रसिद्ध उत्पाद नामों के आधार पर। ऐसा करने के लिए, आपके द्वारा पहले बनाए गए सूत्र को नए फ़ंक्शन के लुकअप मान के रूप में पेस्ट करें VPR:

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

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

   यहाँ मूल्य - नामित श्रेणी $ए:$सी तालिका में लुकअप टेबल 2, 3 कॉलम सी है जिसमें कीमतें हैं।

नीचे दिया गया आंकड़ा हमारे द्वारा बनाए गए सूत्र द्वारा लौटाए गए परिणाम को दर्शाता है:

VLOOKUP और INDIRECT का उपयोग करके विभिन्न तालिकाओं से डेटा का गतिशील प्रतिस्थापन

सबसे पहले, आइए स्पष्ट करें कि "विभिन्न तालिकाओं से डेटा का गतिशील प्रतिस्थापन" अभिव्यक्ति से हमारा क्या मतलब है, यह सुनिश्चित करने के लिए कि हम एक दूसरे को सही ढंग से समझते हैं।

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

कल्पना करें कि आपके पास समान उत्पादों और समान प्रारूप में कई क्षेत्रों की बिक्री रिपोर्टें हैं। आप किसी विशिष्ट क्षेत्र के लिए बिक्री के आंकड़े खोजना चाहते हैं:

यदि आपके पास ऐसी केवल दो रिपोर्टें हैं, तो आप फ़ंक्शन के साथ एक शर्मनाक सरल सूत्र का उपयोग कर सकते हैं VPR и IF (आईएफ) खोज के लिए वांछित रिपोर्ट का चयन करने के लिए:

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

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

कहा पे:

• $ डी $ 2 एक सेल है जिसमें उत्पाद का नाम होता है। ध्यान दें कि सूत्र को अन्य कक्षों में कॉपी करते समय लुकअप मान को बदलने से बचने के लिए हम यहां निरपेक्ष संदर्भों का उपयोग करते हैं।
• $डी3 क्षेत्र के नाम के साथ एक सेल है। हम एक एब्सोल्यूट कॉलम रेफरेंस और एक रिलेटिव रो रेफरेंस का उपयोग कर रहे हैं क्योंकि हम फॉर्मूला को उसी कॉलम में अन्य सेल में कॉपी करने की योजना बना रहे हैं।
• FL_सालes и सीए_बिक्री - तालिकाओं के नाम (या नामित श्रेणियां) जिनमें संबंधित बिक्री रिपोर्ट शामिल हैं। उदाहरण के लिए, आप निश्चित रूप से सामान्य शीट नामों और सेल श्रेणी संदर्भों का उपयोग कर सकते हैं 'FL शीट'!$A$3:$B$10, लेकिन नामित श्रेणियां कहीं अधिक सुविधाजनक हैं।

हालाँकि, जब ऐसी कई तालिकाएँ होती हैं, तो function IF सबसे अच्छा समाधान नहीं है। इसके बजाय, आप फ़ंक्शन का उपयोग कर सकते हैं अप्रत्यक्ष (अप्रत्यक्ष) वांछित खोज श्रेणी वापस करने के लिए।

जैसा कि आप शायद जानते हैं, समारोह अप्रत्यक्ष टेक्स्ट स्ट्रिंग द्वारा दिए गए लिंक को वापस करने के लिए उपयोग किया जाता है, जो कि हमें अभी चाहिए। तो, उपरोक्त सूत्र में फ़ंक्शन के साथ अभिव्यक्ति को साहसपूर्वक बदलें IF समारोह के साथ जोड़ने के लिए अप्रत्यक्ष. यहाँ एक संयोजन है VPR и अप्रत्यक्ष के साथ महान काम करता है:

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

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

कहा पे:

• $ डी $ 2 - यह उत्पाद के नाम के साथ एक सेल है, यह पूर्ण लिंक के कारण अपरिवर्तित है।
• $डी3 वह सेल है जिसमें क्षेत्र के नाम का पहला भाग होता है। हमारे उदाहरण में, यह FL.
• _बिक्री - सभी नामित श्रेणियों या तालिकाओं के नाम का सामान्य भाग। सेल D3 में मान के साथ संयुक्त होने पर, यह आवश्यक श्रेणी का पूर्णतः योग्य नाम बनाता है। नीचे उन लोगों के लिए कुछ विवरण दिए गए हैं जो समारोह में नए हैं अप्रत्यक्ष.

अप्रत्यक्ष और VLOOKUP कैसे काम करते हैं

सबसे पहले, मैं आपको फ़ंक्शन का सिंटैक्स याद दिलाता हूं अप्रत्यक्ष (अप्रत्यक्ष):

INDIRECT(ref_text,[a1])

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

पहला तर्क सेल संदर्भ (A1 या R1C1 शैली), श्रेणी नाम या टेक्स्ट स्ट्रिंग हो सकता है। दूसरा तर्क यह निर्धारित करता है कि पहले तर्क में लिंक की कौन सी शैली निहित है:

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

हमारे मामले में, लिंक की शैली है A1, तो आप दूसरे तर्क को छोड़ सकते हैं और पहले तर्क पर ध्यान केंद्रित कर सकते हैं।

तो चलिए अपनी बिक्री रिपोर्ट पर वापस आते हैं। अगर आपको याद हो तो प्रत्येक रिपोर्ट एक अलग शीट पर स्थित एक अलग टेबल है। सूत्र के सही ढंग से काम करने के लिए, आपको अपनी तालिकाओं (या श्रेणियों) को नाम देना चाहिए, और सभी नामों में एक सामान्य भाग होना चाहिए। उदाहरण के लिए, इस तरह: सीए_बिक्री, FL_बिक्री, TX_बिक्री और इसी तरह। जैसा कि आप देख सकते हैं, "_Sales" सभी नामों में मौजूद है।

समारोह अप्रत्यक्ष कॉलम डी में मान और टेक्स्ट स्ट्रिंग "_Sales" को जोड़ता है, जिससे बता रहा है VPR किस तालिका में खोजना है। यदि सेल D3 में "FL" मान है, तो सूत्र तालिका में खोज करेगा FL_बिक्री, यदि "सीए" - तालिका में सीए_बिक्री और इतना पर.

कार्यों का परिणाम VPR и अप्रत्यक्ष निम्नलिखित होगा:

यदि डेटा विभिन्न एक्सेल पुस्तकों में स्थित है, तो आपको नामित श्रेणी से पहले पुस्तक का नाम जोड़ना होगा, उदाहरण के लिए:

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

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

यदि कार्य अप्रत्यक्ष किसी अन्य कार्यपुस्तिका को संदर्भित करता है, कि कार्यपुस्तिका खुली होनी चाहिए। यदि यह बंद है, तो फ़ंक्शन एक त्रुटि की रिपोर्ट करेगा। #REF! (#एसएसवाईएल!)