प्रगत VLOOKUP उदाहरणे: बहु-निकष शोध

फंक्शनवरील ट्युटोरियलच्या दुसऱ्या भागात व्हीपीआर (VLOOKUP) Excel मध्ये, आम्ही काही उदाहरणांचे विश्लेषण करू जे तुम्हाला सर्व शक्ती निर्देशित करण्यात मदत करतील व्हीपीआर सर्वात महत्वाकांक्षी Excel कार्ये सोडवण्यासाठी. उदाहरणे असे गृहीत धरतात की हे वैशिष्ट्य कसे कार्य करते याचे मूलभूत ज्ञान तुम्हाला आधीपासूनच आहे. नसल्यास, तुम्हाला या ट्यूटोरियलच्या पहिल्या भागापासून सुरुवात करण्यात स्वारस्य असेल, जे वाक्यरचना आणि मूलभूत वापराचे स्पष्टीकरण देते. व्हीपीआर. बरं, चला सुरुवात करूया.

• अनेक निकषांनुसार एक्सेलमध्ये शोधा
• आम्ही VLOOKUP वापरून 2रा, 3रा, इ. मूल्ये काढतो
• इच्छित मूल्याच्या सर्व पुनरावृत्ती पुनर्प्राप्त करा
• ज्ञात पंक्ती आणि स्तंभानुसार XNUMXD शोध
• एका सूत्रात अनेक VLOOKUPs वापरणे
• विविध सारण्यांमधून डेटाचे डायनॅमिक प्रतिस्थापन

अनेक निकषांनुसार एक्सेलमध्ये शोधा

कार्य व्हीपीआर डेटाबेसमधील विशिष्ट मूल्यासाठी शोध करण्यासाठी एक्सेलमध्ये खरोखर एक शक्तिशाली साधन आहे. तथापि, एक महत्त्वपूर्ण मर्यादा आहे - त्याची वाक्यरचना आपल्याला फक्त एक मूल्य शोधण्याची परवानगी देते. तुम्हाला अनेक अटींनुसार शोधायचे असल्यास काय? तुम्हाला खालील उपाय सापडतील.

उदाहरण 1: 2 भिन्न निकषांनुसार शोधा

समजा आमच्याकडे ऑर्डरची यादी आहे आणि आम्हाला ते शोधायचे आहे मालाचे प्रमाण (प्रमाण), दोन निकषांवर आधारित – क्लायंटचे नाव (ग्राहक) и उत्पादनाचे नाव (उत्पादन). हे प्रकरण गुंतागुंतीचे आहे की प्रत्येक खरेदीदाराने अनेक प्रकारच्या वस्तूंची ऑर्डर दिली आहे, जसे की खालील तक्त्यावरून पाहिले जाऊ शकते:

नियमित कार्य व्हीपीआर या परिस्थितीत कार्य करणार नाही कारण ते दिलेल्या लुकअप मूल्याशी जुळणारे पहिले मूल्य परत करेल. उदाहरणार्थ, जर तुम्हाला एखाद्या वस्तूचे प्रमाण जाणून घ्यायचे असेल मिठाईखरेदीदाराने आदेश दिलेला जेरेमी हिल, खालील सूत्र लिहा:

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

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

- हे सूत्र परिणाम देईल 15उत्पादनाशी संबंधित सफरचंद, कारण ते जुळणारे पहिले मूल्य आहे.

एक सोपा उपाय आहे - एक अतिरिक्त स्तंभ तयार करा ज्यामध्ये सर्व इच्छित निकष एकत्र करा. आमच्या उदाहरणात, हे स्तंभ आहेत क्लायंटचे नाव (ग्राहक) и उत्पादनाचे नाव (उत्पादन). हे विसरू नका की विलीन केलेला स्तंभ नेहमी शोध श्रेणीतील सर्वात डावीकडील स्तंभ असणे आवश्यक आहे, कारण तो फंक्शनचा डावा स्तंभ आहे व्हीपीआर मूल्य शोधताना दिसते.

तर, तुम्ही टेबलमध्ये एक सहायक स्तंभ जोडा आणि त्याच्या सर्व सेलवर खालील सूत्र कॉपी करा: =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 युक्तिवादाचे एकत्रित मूल्य समाविष्ट आहे लुकअप_मूल्य (lookup_value) आणि 4 - युक्तिवाद col_index_num (स्तंभ_संख्या), म्हणजे पुनर्प्राप्त करायचा डेटा असलेल्या स्तंभाची संख्या.

उदाहरण 2: दोन निकषांनुसार VLOOKUP दुसऱ्या शीटवर सारणी पाहिली जात आहे

दुसर्‍या शीटवर किंवा दुसर्‍या एक्सेल वर्कबुकमध्ये असलेल्या दुसर्‍या टेबल (लूकअप टेबल) मधील डेटा जोडून तुम्हाला मुख्य सारणी (मुख्य सारणी) अद्यतनित करायची असल्यास, तुम्ही समाविष्ट केलेल्या सूत्रामध्ये इच्छित मूल्य थेट गोळा करू शकता. मुख्य टेबल मध्ये.

मागील उदाहरणाप्रमाणे, आपल्याला एकत्रित मूल्यांसह लुकअप टेबलमध्ये सहायक स्तंभाची आवश्यकता असेल. हा स्तंभ शोध श्रेणीतील सर्वात डावीकडे स्तंभ असणे आवश्यक आहे.

त्यामुळे सूत्र सह व्हीपीआर असे असू शकते:

=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) करणे आवश्यक आहे.

लक्षात ठेवा! कार्य व्हीपीआर 255 वर्णांपर्यंत मर्यादित, ते 255 वर्णांपेक्षा जास्त लांबीचे मूल्य शोधू शकत नाही. हे लक्षात ठेवा आणि इच्छित मूल्याची लांबी या मर्यादेपेक्षा जास्त नसेल याची खात्री करा.

मी सहमत आहे की सहाय्यक स्तंभ जोडणे हा सर्वात मोहक आणि नेहमीच स्वीकार्य उपाय नाही. तुम्ही हेल्पर कॉलमशिवाय हे करू शकता, परंतु त्यासाठी फंक्शन्सच्या संयोजनासह अधिक जटिल सूत्र आवश्यक आहे. INDEX (INDEX) आणि मॅच (अधिक उघड).

आम्ही VLOOKUP वापरून 2रा, 3रा, इ. मूल्ये काढतो

तुम्हाला हे आधीच माहित आहे व्हीपीआर फक्त एक जुळणारे मूल्य परत करू शकते, अधिक अचूकपणे, पहिले आढळले. पण हे मूल्य पाहिल्या गेलेल्या अॅरेमध्ये अनेक वेळा पुनरावृत्ती होत असेल आणि तुम्हाला त्यातील 2रा किंवा 3रा काढायचा असेल तर? सर्व मूल्ये असल्यास काय? समस्या क्लिष्ट दिसते, परंतु उपाय अस्तित्वात आहे!

समजा टेबलच्या एका कॉलममध्ये ग्राहकांची नावे आहेत (ग्राहकांचे नाव), आणि दुसऱ्या कॉलममध्ये त्यांनी खरेदी केलेली उत्पादने (उत्पादन) आहेत. दिलेल्या ग्राहकाने खरेदी केलेल्या 2ऱ्या, 3ऱ्या आणि 4व्या वस्तू शोधण्याचा प्रयत्न करूया.

स्तंभापूर्वी सहायक स्तंभ जोडणे हा सर्वात सोपा मार्ग आहे ग्राहकाचे नाव आणि प्रत्येक नावाच्या पुनरावृत्ती क्रमांकासह ग्राहकांच्या नावांसह भरा, उदाहरणार्थ, जॉन डो 1, जॉन डो 2 इ. फंक्शन वापरून नंबरिंगची युक्ती करू COUNTIF (COUNTIF), ग्राहकांची नावे स्तंभ B मध्ये आहेत हे लक्षात घेऊन:

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

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

त्यानंतर आपण सामान्य कार्य वापरू शकता व्हीपीआरआवश्यक ऑर्डर शोधण्यासाठी. उदाहरणार्थ:

• शोधणे 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;ЛОЖЬ)

खरं तर, खालील आकृतीत दाखवल्याप्रमाणे तुम्ही मजकुराऐवजी लुकअप मूल्य म्हणून सेल संदर्भ प्रविष्ट करू शकता:

आपण फक्त शोधत असाल तर एक्सएनयूएमएक्स-ई पुनरावृत्ती, आपण अधिक जटिल सूत्र तयार करून सहायक स्तंभाशिवाय करू शकता:

=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 – खरेदीदाराचे नाव असलेला सेल (तो अपरिवर्तित आहे, कृपया लक्षात ठेवा – लिंक निरपेक्ष आहे);
• $ब$ - स्तंभ ग्राहकाचे नाव;
• Table4 - तुमचे टेबल (हे ठिकाण नियमित श्रेणी देखील असू शकते);
• $C16 - तुमच्या टेबल किंवा रेंजचा शेवटचा सेल.

हे सूत्र फक्त दुसरे जुळणारे मूल्य शोधते. जर तुम्हाला उर्वरित पुनरावृत्ती काढायची असेल तर मागील उपाय वापरा.

तुम्हाला सर्व सामन्यांची यादी हवी असल्यास - फंक्शन व्हीपीआर हे सहाय्यक नाही, कारण ते एका वेळी फक्त एक मूल्य परत करते - कालावधी. परंतु एक्सेलमध्ये एक कार्य आहे INDEX (INDEX), जे सहजपणे या कार्याचा सामना करू शकतात. असे सूत्र कसे दिसेल, ते तुम्ही पुढील उदाहरणातून शिकाल.

इच्छित मूल्याच्या सर्व पुनरावृत्ती पुनर्प्राप्त करा

वर नमूद केल्याप्रमाणे व्हीपीआर स्कॅन केलेल्या श्रेणीतून सर्व डुप्लिकेट मूल्ये काढू शकत नाही. हे करण्यासाठी, तुम्हाला काहीसे अधिक क्लिष्ट सूत्र आवश्यक आहे, जे अनेक एक्सेल फंक्शन्सचे बनलेले आहे, जसे की 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 + एंटर कराअॅरे फॉर्म्युला योग्यरित्या प्रविष्ट करण्यासाठी.

हे कसे कार्य करते हे समजून घेण्यात तुम्हाला स्वारस्य असल्यास, चला सूत्राच्या तपशीलांमध्ये थोडेसे जाऊ या:

भाग 1:

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

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

$F$2=B2:B16 - सेल F2 मधील मूल्याची B2:B16 श्रेणीतील प्रत्येक मूल्याशी तुलना करा. जर एक जुळणी आढळली तर अभिव्यक्ती STRING(C2:C16)-1 संबंधित रेषेची संख्या मिळवते (मूल्य -1 तुम्हाला हेडर लाइन समाविष्ट न करण्याची परवानगी देते). जुळत नसल्यास, फंक्शन IF (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))

कार्य लहान (लहान) परतावा n-ओह डेटा अॅरेमधील सर्वात लहान मूल्य. आमच्या बाबतीत, कोणती स्थिती (सर्वात लहान पासून) परत करायची हे फंक्शनद्वारे निर्धारित केले जाते पंक्तीचा (लाइन) (भाग 2 पहा). तर, सेलसाठी F4 कार्य SMALL({अॅरे},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 (INDEX) अॅरेमधील विशिष्ट सेलचे मूल्य फक्त परत करते सी 2: सी 16. सेल साठी F4 कार्य INDEX($C$2:$C$16) परत येईल सफरचंदकारण F5 कार्य INDEX($C$2:$C$16) परत येईल मिठाई आणि त्यामुळे वर.

भाग 5:

IFERROR()

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

शेवटी, आपण फंक्शनमध्ये सूत्र ठेवतो IFERROR (IFERROR), कारण तुम्ही त्रुटी संदेशावर खूश होण्याची शक्यता नाही #AT (#N/A) ज्या सेलमध्ये सूत्र कॉपी केले आहे त्यांची संख्या पाहिल्या जात असलेल्या श्रेणीतील डुप्लिकेट मूल्यांच्या संख्येपेक्षा कमी असल्यास.

ज्ञात पंक्ती आणि स्तंभानुसार XNUMXD शोध

एक्सेलमध्ये XNUMXD शोध करताना ज्ञात पंक्ती आणि स्तंभ क्रमांकाद्वारे मूल्य शोधणे समाविष्ट आहे. दुसऱ्या शब्दांत, तुम्ही विशिष्ट पंक्ती आणि स्तंभाच्या छेदनबिंदूवर सेल मूल्य काढत आहात.

तर, आपल्या टेबलकडे वळू आणि फंक्शनसह एक सूत्र लिहू व्हीपीआर, ज्यामध्ये मार्चमध्ये विकल्या गेलेल्या लिंबाच्या किमतीची माहिती मिळेल.

XNUMXD शोध करण्याचे अनेक मार्ग आहेत. पर्याय तपासा आणि तुमच्यासाठी सर्वात योग्य पर्याय निवडा.

VLOOKUP आणि MATCH कार्ये

आपण फंक्शन्सचा एक समूह वापरू शकता व्हीपीआर (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);ЛОЖЬ)

वरील सूत्र नियमित कार्य आहे व्हीपीआर, जे सेल A2 ते A9 मधील "लेमन" मूल्याची अचूक जुळणी शोधते. पण मार्च सेल कोणत्या कॉलममध्ये आहे हे तुम्हाला माहीत नसल्यामुळे, तुम्ही तिसऱ्या फंक्शन वितर्कासाठी कॉलम नंबर सेट करू शकणार नाही. व्हीपीआर. त्याऐवजी, फंक्शन वापरले जाते अधिक उघडहा स्तंभ परिभाषित करण्यासाठी.

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

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

मानवी भाषेत अनुवादित, या सूत्राचा अर्थ आहे:

• आम्ही "मार" - तर्क शोधत आहोत लुकअप_मूल्य (lookup_value);
• A1 ते I1 सेलमध्ये पाहणे - युक्तिवाद लुकअप_अॅरे (lookup_array);
• अचूक जुळणी परत करणे - युक्तिवाद जुळणी_प्रकार (match_type).

वापरून 0 तिसऱ्या युक्तिवादात, तुम्ही फंक्शन्स म्हणता अधिक उघड तुम्ही शोधत असलेल्या मूल्याशी तंतोतंत जुळणारे पहिले मूल्य पहा. हे मूल्याच्या बरोबरीचे आहे असत्य (FALSE) चौथ्या युक्तिवादासाठी व्हीपीआर.

अशा प्रकारे तुम्ही Excel मध्ये द्वि-मार्ग शोध सूत्र तयार करू शकता, ज्याला द्वि-आयामी शोध किंवा द्विदिशात्मक शोध देखील म्हणतात.

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. बॉक्स तपासा शीर्ष पंक्ती (वरील ओळीवर) आणि डावा स्तंभ (डावीकडील स्तंभात). मायक्रोसॉफ्ट एक्सेल तुमच्या स्प्रेडशीटच्या वरच्या पंक्ती आणि डाव्या स्तंभातील मूल्यांमधून श्रेणींना नावे नियुक्त करेल. आता तुम्ही सूत्रे तयार न करता थेट ही नावे वापरून शोधू शकता.
3. कोणत्याही रिकाम्या सेलमध्ये लिहा =पंक्ती_नाव स्तंभ_नाव, उदाहरणार्थ यासारखे:

   = लिंबू मार

   … किंवा या उलट:

   = मार लिंबू

   लक्षात ठेवा की पंक्ती आणि स्तंभाची नावे एका जागेद्वारे विभक्त केली जाणे आवश्यक आहे, जे या प्रकरणात छेदनबिंदू ऑपरेटरसारखे कार्य करते.

तुम्ही एखादे नाव एंटर करता तेव्हा, Microsoft Excel जुळणार्‍या नावांच्या सूचीसह टूलटिप दाखवेल, जसे तुम्ही सूत्र एंटर करता.

4. प्रेस प्रविष्ट करा आणि निकाल तपासा

सर्वसाधारणपणे, तुम्ही वरीलपैकी कोणतीही पद्धत निवडाल, द्विमितीय शोधाचा परिणाम सारखाच असेल:

एका सूत्रात अनेक VLOOKUPs वापरणे

असे घडते की मुख्य टेबल आणि लुकअप टेबलमध्ये एकच स्तंभ सामाईक नसतो आणि हे तुम्हाला नेहमीच्या फंक्शन वापरण्यापासून प्रतिबंधित करते व्हीपीआर. तथापि, आणखी एक सारणी आहे ज्यामध्ये आम्हाला स्वारस्य असलेली माहिती नाही, परंतु मुख्य सारणी आणि लुकअप टेबलसह एक सामान्य स्तंभ आहे.

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

दुस-या लुकअप टेबलमधून मुख्य टेबलवर किमती जोडण्यासाठी, तुम्ही दुहेरी म्हणून ओळखली जाणारी क्रिया करणे आवश्यक आहे व्हीपीआर किंवा घरटे व्हीपीआर.

1. फंक्शन लिहा व्हीपीआर, जे टेबलमध्ये उत्पादनाचे नाव शोधते पहा सारणी 1वापरून SKU, इच्छित मूल्य म्हणून:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   येथे नवीन_SKU - नावाची श्रेणी $A:$B टेबल मध्ये पहा सारणी 1, 2 - हा स्तंभ बी आहे, ज्यामध्ये वस्तूंची नावे आहेत (वरील चित्र पहा)

2. टेबलमधून किंमती घालण्यासाठी एक सूत्र लिहा पहा सारणी 2 सुप्रसिद्ध उत्पादन नावांवर आधारित. हे करण्यासाठी, नवीन फंक्शनसाठी लुकअप व्हॅल्यू म्हणून तुम्ही पूर्वी तयार केलेला फॉर्म्युला पेस्ट करा व्हीपीआर:

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

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

   येथे किंमत - नावाची श्रेणी $A:$C टेबल मध्ये पहा सारणी 2, 3 किमती असलेला स्तंभ C आहे.

खालील आकृती आम्ही तयार केलेल्या फॉर्म्युलाद्वारे मिळालेला परिणाम दर्शविते:

VLOOKUP आणि INDIRECT वापरून वेगवेगळ्या सारण्यांमधून डेटाचे डायनॅमिक प्रतिस्थापन

प्रथम, आपण एकमेकांना योग्यरित्या समजत आहोत याची खात्री करण्यासाठी “वेगवेगळ्या सारण्यांवरील डेटाचे डायनॅमिक प्रतिस्थापन” या अभिव्यक्तीचा अर्थ काय ते स्पष्ट करूया.

अशी परिस्थिती असते जेव्हा एकाच स्वरूपाच्या डेटासह अनेक पत्रके असतात आणि दिलेल्या सेलमध्ये प्रविष्ट केलेल्या मूल्यावर अवलंबून, विशिष्ट शीटमधून आवश्यक माहिती काढणे आवश्यक असते. मला वाटते की हे उदाहरणासह स्पष्ट करणे सोपे आहे.

अशी कल्पना करा की तुमच्याकडे समान उत्पादनांसह आणि समान स्वरूपातील अनेक प्रदेशांसाठी विक्री अहवाल आहेत. तुम्हाला विशिष्ट प्रदेशासाठी विक्रीचे आकडे शोधायचे आहेत:

जर तुमच्याकडे असे फक्त दोन अहवाल असतील तर तुम्ही फंक्शन्ससह एक लज्जास्पद सोपा फॉर्म्युला वापरू शकता व्हीपीआर и IF (IF) शोधण्यासाठी इच्छित अहवाल निवडण्यासाठी:

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

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

कोठे:

• $ डी $ 2 उत्पादनाचे नाव असलेला सेल आहे. लक्षात ठेवा की इतर सेलमध्ये सूत्र कॉपी करताना लुकअप मूल्य बदलणे टाळण्यासाठी आम्ही येथे परिपूर्ण संदर्भ वापरतो.
• $D3 प्रदेशाच्या नावाचा सेल आहे. आम्ही निरपेक्ष स्तंभ संदर्भ आणि सापेक्ष पंक्ती संदर्भ वापरत आहोत कारण आम्ही त्याच स्तंभातील इतर सेलमध्ये सूत्र कॉपी करण्याची योजना आखत आहोत.
• FL_Sales и CA_विक्री - सारण्यांची नावे (किंवा नामांकित श्रेणी) ज्यात संबंधित विक्री अहवाल आहेत. तुम्ही अर्थातच, नेहमीच्या शीटची नावे आणि सेल श्रेणी संदर्भ वापरू शकता, उदाहरणार्थ 'FL शीट'!$A$3:$B$10, परंतु नामांकित श्रेणी अधिक सोयीस्कर आहेत.

तथापि, जेव्हा अशा अनेक सारण्या असतात, तेव्हा फंक्शन IF सर्वोत्तम उपाय नाही. त्याऐवजी, तुम्ही फंक्शन वापरू शकता अप्रत्यक्ष (अप्रत्यक्ष) इच्छित शोध श्रेणी परत करण्यासाठी.

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

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

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

कोठे:

• $ डी $ 2 - हा उत्पादनाच्या नावाचा सेल आहे, तो परिपूर्ण दुव्यामुळे अपरिवर्तित आहे.
• $D3 क्षेत्राच्या नावाचा पहिला भाग असलेला सेल आहे. आमच्या उदाहरणात, हे FL.
• _विक्री - सर्व नामांकित श्रेणी किंवा सारण्यांच्या नावाचा सामान्य भाग. सेल D3 मधील मूल्यासह एकत्रित केल्यावर, ते आवश्यक श्रेणीचे पूर्ण पात्र नाव तयार करते. जे फंक्शनसाठी नवीन आहेत त्यांच्यासाठी खाली काही तपशील आहेत अप्रत्यक्ष.

INDIRECT आणि VLOOKUP कसे कार्य करतात

प्रथम, मी तुम्हाला फंक्शनच्या वाक्यरचनाची आठवण करून देतो अप्रत्यक्ष (अप्रत्यक्ष):

INDIRECT(ref_text,[a1])

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

पहिला युक्तिवाद सेल संदर्भ (A1 किंवा R1C1 शैली), श्रेणी नाव किंवा मजकूर स्ट्रिंग असू शकतो. दुसरा युक्तिवाद पहिल्या युक्तिवादात लिंकची कोणती शैली आहे हे निर्धारित करते:

• A1जर युक्तिवाद असेल तर ट्रू कोड (TRUE) किंवा निर्दिष्ट नाही;
• आर 1 सी 1, तर Fएएस ई (असत्य).

आमच्या बाबतीत, दुव्याची शैली आहे A1, त्यामुळे तुम्ही दुसरा युक्तिवाद सोडून पहिल्यावर लक्ष केंद्रित करू शकता.

तर चला आमच्या विक्री अहवालांकडे परत जाऊया. जर तुम्हाला आठवत असेल, तर प्रत्येक अहवाल वेगळ्या शीटवर स्थित एक स्वतंत्र टेबल आहे. सूत्र योग्यरितीने कार्य करण्यासाठी, तुम्ही तुमच्या सारण्यांना (किंवा श्रेणी) नाव दिले पाहिजे आणि सर्व नावांमध्ये समान भाग असणे आवश्यक आहे. उदाहरणार्थ, यासारखे: CA_विक्री, FL_Sales, TX_विक्री आणि असेच. तुम्ही बघू शकता, "_विक्री" सर्व नावांमध्ये आहे.

कार्य अप्रत्यक्ष स्तंभ D मधील मूल्य आणि मजकूर स्ट्रिंग “_Sales” जोडते, त्याद्वारे सांगते व्हीपीआर कोणत्या टेबलमध्ये शोधायचे. सेल D3 मध्ये "FL" मूल्य असल्यास, सूत्र सारणी शोधेल FL_Sales, जर “CA” – टेबलमध्ये CA_विक्री आणि त्यामुळे वर.

फंक्शन्सचा परिणाम व्हीपीआर и अप्रत्यक्ष खालील असेल:

जर डेटा वेगवेगळ्या एक्सेल पुस्तकांमध्ये असेल, तर तुम्हाला नावाच्या श्रेणीपूर्वी पुस्तकाचे नाव जोडावे लागेल, उदाहरणार्थ:

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

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

फंक्शन असल्यास अप्रत्यक्ष दुसर्‍या कार्यपुस्तकाचा संदर्भ देते, ती कार्यपुस्तिका खुली असणे आवश्यक आहे. ते बंद असल्यास, फंक्शन त्रुटीची तक्रार करेल. #REF! (#SSYL!).