मूल्ये बदलण्यासाठी VLOOKUP फंक्शन वापरणे

कोण खूप आळशी आहे किंवा वाचण्यासाठी वेळ नाही - व्हिडिओ पहा. तपशील आणि बारकावे खालील मजकूरात आहेत.

समस्येचे सूत्रीकरण

तर, आमच्याकडे दोन टेबल्स आहेत - ऑर्डर टेबल и किंमत सूची:

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

उपाय

एक्सेल फंक्शन सेटमध्ये, श्रेणी अंतर्गत संदर्भ आणि अॅरे (शोध आणि संदर्भ) एक कार्य आहे व्हीपीआर (VLOOKUP).हे फंक्शन दिलेले मूल्य शोधते (आमच्या उदाहरणात, हा शब्द "सफरचंद") निर्दिष्ट टेबलच्या (किंमत सूची) सर्वात डावीकडील स्तंभात वरपासून खालपर्यंत हलतो आणि ते सापडल्यानंतर, समीप सेलची सामग्री प्रदर्शित करते. (२३ रूबल) .योजनेनुसार, या फंक्शनचे ऑपरेशन असे दर्शविले जाऊ शकते:

फंक्शनच्या पुढील वापरासाठी, एकाच वेळी एक गोष्ट करा – किंमत सूचीमधील सेलची श्रेणी तुमचे स्वतःचे नाव द्या. हे करण्यासाठी, "हेडर" (G3: H19) वगळता किंमत सूचीतील सर्व सेल निवडा, मेनूमधून निवडा घाला - नाव - नियुक्त करा (घाला — नाव — परिभाषित करा) किंवा दाबा सीटीआरएल + एफ 3 आणि कोणतेही नाव (स्पेस नसलेले) एंटर करा किंमत… आता, भविष्यात, तुम्ही किंमत सूचीशी लिंक करण्यासाठी हे नाव वापरू शकता.

आता आपण फंक्शन वापरतो व्हीपीआर… जिथे तो प्रविष्ट केला जाईल तो सेल निवडा (D3) आणि टॅब उघडा सूत्रे - कार्य समाविष्ट करणे (सूत्र - फंक्शन घाला)… वर्गात संदर्भ आणि अॅरे (लुकअप आणि संदर्भ) फंक्शन शोधा व्हीपीआर (VLOOKUP) आणि दाबा OK… फंक्शनसाठी वितर्क प्रविष्ट करण्यासाठी एक विंडो दिसेल:

आम्ही त्या बदल्यात भरतो:

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

सर्व काही! ते दाबणे बाकी आहे OK आणि एंटर केलेले फंक्शन संपूर्ण कॉलममध्ये कॉपी करा.

# N/A त्रुटी आणि त्यांचे दडपशाही

कार्य व्हीपीआर (VLOOKUP) #N/A त्रुटी मिळवते (#N/A) जर एक:

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

  =VLOOKUP(TEXT(B3),किंमत,0)

  आपण याबद्दल अधिक येथे वाचू शकता.
  

• फंक्शन आवश्यक मूल्य शोधू शकत नाही कारण कोडमध्ये स्पेसेस किंवा अदृश्य नॉन-प्रिंट करण्यायोग्य वर्ण आहेत (लाइन ब्रेक इ.). या प्रकरणात, आपण मजकूर कार्ये वापरू शकता टीआरआयएम (TRIM) и प्रिंट(स्वच्छ) त्यांना काढून टाकण्यासाठी:

  =VLOOKUP(ट्रिमस्पेसेस(क्लीन(बी3)),किंमत,0)

  =VLOOKUP(TRIM(CLEAN(B3));किंमत;0)

त्रुटी संदेश दाबण्यासाठी # एन / ए (#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 कार्ये