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

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

एकाधिक श्रेणींमध्ये एकाचवेळी शोध

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

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

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

सरासरी एक्सेल वापरकर्ता फॉर्म्युला-आधारित फंक्शन्स जसे की VLOOKUP वापरून उपाय शोधतो. आणि ते 3 टप्प्यांमध्ये शोध करेल (प्रत्येक टेबलसाठी स्वतंत्रपणे). असे दिसून आले की आपण विशेष सूत्र वापरून केवळ 1 टप्प्यात शोध करून त्वरित तयार परिणाम मिळवू शकता. यासाठी:

1. सेल E6 मध्ये, मूल्य 20 प्रविष्ट करा, जी शोध क्वेरीसाठी अट आहे.
2. सेल E7 मध्ये, खालील सूत्र प्रविष्ट करा:

20 पीसीसाठी उत्पादन खर्च. एक विशिष्ट उत्पादन.

VLOOKUP सह सूत्र अनेक सारण्यांमध्ये कसे कार्य करते:

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

तिसरे फंक्शन शोधण्याच्या परिणामी, आम्हाला 125 मूल्य मिळते, जे दुसऱ्या फंक्शनसाठी पहिले वितर्क आहे. सर्व पॅरामीटर्स प्राप्त झाल्यानंतर, दुसरे कार्य उत्पादनासाठी आवश्यक कामगारांच्या संख्येसाठी दुसऱ्या टेबलमध्ये दिसते. परिणामी, मूल्य 5 परत केले जाते, जे नंतर मुख्य कार्याद्वारे वापरले जाईल. प्राप्त झालेल्या सर्व डेटाच्या आधारे, सूत्र गणनाचा अंतिम परिणाम देते. अर्थात, एका विशिष्ट उत्पादनाच्या 20 तुकड्या तयार करण्यासाठी $1,750 ची रक्कम आवश्यक आहे.

या तत्त्वाचा वापर करून, तुम्ही अनेक शीटमधून VLOOKUP फंक्शनसाठी सूत्रे वापरू शकता.

मायक्रोसॉफ्ट एक्सेल अनेकदा मोठ्या प्रमाणात माहितीसह कार्य करते. हे हजारो पंक्ती, स्तंभ आणि स्थानांसह विशाल तक्ते तयार करते. अशा ॲरेमध्ये कोणताही विशिष्ट डेटा शोधणे कठीण होऊ शकते. आणि कधीकधी ते पूर्णपणे अशक्य आहे. हे कार्य सोपे केले जाऊ शकते. एक्सेलमध्ये योग्य शब्द कसा शोधायचा ते शोधा. हे तुमच्यासाठी दस्तऐवज नेव्हिगेट करणे सोपे करेल. आणि आपण शोधत असलेल्या माहितीवर आपण पटकन जाऊ शकता.

तुम्ही जे शोधत आहात त्या सर्व सेलचे पत्ते प्रदर्शित करण्यासाठी, पुढील गोष्टी करा:

1. तुमच्याकडे Office 2010 असल्यास, मेनूवर जा - संपादित करा - शोधा.
2. इनपुट फील्ड असलेली विंडो उघडेल. त्यात एक शोध वाक्यांश लिहा.
3. एक्सेल 2007 मध्ये, हे बटण संपादन पॅनेलमधील होम मेनूवर आहे. ते उजवीकडे आहे.
4. सर्व आवृत्त्यांमध्ये समान परिणाम Ctrl+F दाबून प्राप्त केला जाऊ शकतो.
5. फील्डमध्ये, तुम्हाला शोधायचा असलेला शब्द, वाक्यांश किंवा संख्या टाइप करा.
6. संपूर्ण दस्तऐवज शोधण्यासाठी सर्व शोधा क्लिक करा. तुम्ही "पुढील" वर क्लिक केल्यास, प्रोग्राम एक्सेल सेल कर्सरच्या खाली असलेले सेल एक-एक करून निवडेल.
7. प्रक्रिया पूर्ण होण्याची प्रतीक्षा करा. दस्तऐवज जितका मोठा असेल तितका वेळ सिस्टम शोधेल.
8. परिणामांसह एक सूची दिसेल: ज्या सेलमध्ये निर्दिष्ट वाक्यांशाशी जुळणारे आहेत त्यांची नावे आणि पत्ते आणि त्यामध्ये लिहिलेला मजकूर.
9. जेव्हा तुम्ही प्रत्येक ओळीवर क्लिक कराल, तेव्हा संबंधित सेल हायलाइट होईल.
10. सोयीसाठी, आपण विंडो "ताणणे" शकता. अशा प्रकारे अधिक रेषा दृश्यमान होतील.
11. डेटा क्रमवारी लावण्यासाठी, शोध परिणामांवरील स्तंभाच्या नावांवर क्लिक करा. तुम्ही “शीट” वर क्लिक केल्यास, पत्रकाच्या नावावर आधारित रेषा वर्णक्रमानुसार लावल्या जातील; तुम्ही “मूल्य” निवडल्यास, त्या मूल्यानुसार व्यवस्थित केल्या जातील.
12. हे स्तंभ देखील "ताणणे" आहेत.

तुम्ही तुमच्या स्वतःच्या अटी सेट करू शकता. उदाहरणार्थ, अनेक वर्ण वापरून शोध चालवा. तुम्हाला पूर्णपणे आठवत नसलेला शब्द Excel मध्ये कसा शोधायचा ते येथे आहे:

1. मथळ्याचा फक्त एक भाग प्रविष्ट करा. तुम्ही किमान एक अक्षर लिहू शकता - ते अस्तित्त्वात असलेली सर्व ठिकाणे हायलाइट केली जातील.
2. चिन्हे वापरा * (तारका) आणि ? (प्रश्न चिन्ह). ते हरवलेल्या वर्णांची जागा घेतात.
3. प्रश्न एक गहाळ आयटम सूचित करतो. तुम्ही लिहिल्यास, उदाहरणार्थ, "P???," सेल दिसतील ज्यामध्ये "P" ने सुरू होणारा चार-अक्षरांचा शब्द असेल: "नांगर," "फील्ड," "कपल" आणि असेच.
4. तारा (*) कितीही वर्ण बदलू शकतो. मूळ "rast" असलेली सर्व मूल्ये शोधण्यासाठी, "*rast*" की वापरून शोध सुरू करा.

आपण सेटिंग्जवर देखील जाऊ शकता:

1. शोधा विंडोमध्ये, पर्याय क्लिक करा.
2. "ब्राउझ करा" आणि "शोध क्षेत्र" विभागात, कुठे आणि कोणत्या निकषांनुसार सामने पहायचे ते निर्दिष्ट करा. तुम्ही सूत्रे, नोट्स किंवा मूल्ये निवडू शकता.
3. सिस्टीमला लोअरकेस आणि अपरकेस अक्षरांमध्ये फरक करण्यास अनुमती देण्यासाठी, "केस जुळवा" चेकबॉक्स तपासा.
4. तुम्ही "संपूर्ण सेल" चेकबॉक्स चेक केल्यास, परिणाम केवळ निर्दिष्ट शोध वाक्यांश असलेल्या सेल दर्शवतील आणि दुसरे काहीही नाही.

सेल फॉरमॅट पर्याय

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

1. शोध विंडोमध्ये, "पर्याय" वर क्लिक करा आणि "स्वरूप" बटणावर क्लिक करा. अनेक टॅबसह एक मेनू उघडेल.
2. तुम्ही विशिष्ट फॉन्ट, फ्रेम प्रकार, पार्श्वभूमी रंग, डेटा स्वरूप निर्दिष्ट करू शकता. प्रणाली निर्दिष्ट निकषांशी जुळणारी ठिकाणे पाहेल.
3. वर्तमान सेलमधून माहिती घेण्यासाठी (सध्या हायलाइट केलेले), "या सेलचे स्वरूप वापरा" वर क्लिक करा. मग प्रोग्रामला समान आकार आणि चिन्हांचे प्रकार, समान रंग, समान सीमा आणि यासारख्या सर्व मूल्ये सापडतील.

अनेक शब्द शोधा

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

एक्सेलमध्ये फक्त एकच शब्द नाही तर एकाच वेळी अनेक शब्द शोधण्यासाठी, पुढील गोष्टी करा:

1. त्यांना शोध बारमध्ये लिहा.
2. त्यांच्या दरम्यान तारे ठेवा. परिणाम "*Text* *Text2* *Text3*" असेल. हे निर्दिष्ट शिलालेख असलेली सर्व मूल्ये शोधेल. त्यांच्यामध्ये कोणतीही पात्रे आहेत की नाही याची पर्वा न करता.
3. अशा प्रकारे तुम्ही वैयक्तिक अक्षरांसह एक की निर्दिष्ट करू शकता.

फिल्टर करा

फिल्टर वापरून एक्सेलमध्ये कसे शोधायचे ते येथे आहे:

1. कोणताही भरलेला सेल निवडा.
2. होम - सॉर्ट - फिल्टर क्लिक करा.
3. वरच्या ओळीतील सेलच्या पुढे बाण दिसतील. हा एक ड्रॉप डाउन मेनू आहे. ते उघडा.
4. मजकूर फील्डमध्ये तुमची विनंती प्रविष्ट करा आणि ओके क्लिक करा.
5. स्तंभ केवळ शोध वाक्यांश असलेल्या सेल प्रदर्शित करेल.
6. परिणाम रीसेट करण्यासाठी, ड्रॉप-डाउन सूचीमध्ये "सर्व निवडा" निवडा.
7. फिल्टर अक्षम करण्यासाठी, क्रमवारीत पुन्हा त्यावर क्लिक करा.

आपल्याला आवश्यक असलेले मूल्य कोणत्या पंक्तीमध्ये आहे हे आपल्याला माहित नसल्यास ही पद्धत कार्य करणार नाही.

Excel मध्ये वाक्यांश किंवा संख्या शोधण्यासाठी, अंगभूत इंटरफेस क्षमता वापरा. तुम्ही अतिरिक्त शोध पर्याय निवडू शकता आणि फिल्टर चालू करू शकता.

हे ट्यूटोरियल फंक्शन्सचे मुख्य फायदे स्पष्ट करते INDEXआणि शोधा Excel मध्ये, जे त्यांना तुलनेत अधिक आकर्षक बनवते VLOOKUP. तुम्हाला सूत्रांची अनेक उदाहरणे दिसतील जी तुम्हाला फंक्शनला सामोरे जाणाऱ्या अनेक जटिल कार्यांना सहजपणे सामोरे जाण्यास मदत करतील. VLOOKUPशक्तीहीन

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

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

INDEX आणि MATCH बद्दल मूलभूत माहिती

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

येथे आम्ही सार समजून घेण्यासाठी आवश्यक किमान सादर करतो आणि नंतर आम्ही वापरण्याचे फायदे दर्शविणारी सूत्रांची तपशीलवार उदाहरणे तपासू. INDEXआणि शोधाऐवजी VLOOKUP.

INDEX - फंक्शन सिंटॅक्स आणि वापर

कार्य INDEXएक्सेलमधील (INDEX) दिलेल्या पंक्ती आणि स्तंभ क्रमांकावरील ॲरेमधून मूल्य मिळवते. फंक्शनमध्ये हे वाक्यरचना आहे:

प्रत्येक युक्तिवादाचे एक अतिशय सोपे स्पष्टीकरण आहे:

• रचना(ॲरे) ही सेलची श्रेणी आहे ज्यामधून तुम्हाला मूल्य काढायचे आहे.
• पंक्ती_संख्या(line_number) ही ॲरेमधील रेषेची संख्या आहे ज्यामधून तुम्हाला मूल्य काढायचे आहे. निर्दिष्ट न केल्यास, वितर्क आवश्यक आहे स्तंभ_संख्या(स्तंभ_संख्या).
• स्तंभ_संख्या(column_number) ही ॲरेमधील स्तंभाची संख्या आहे ज्यामधून तुम्हाला मूल्य काढायचे आहे. निर्दिष्ट न केल्यास, वितर्क आवश्यक आहे पंक्ती_संख्या(रेखा_क्रमांक)

दोन्ही वितर्क निर्दिष्ट केले असल्यास, फंक्शन INDEXनिर्दिष्ट पंक्ती आणि स्तंभाच्या छेदनबिंदूवर सेलमधून मूल्य मिळवते.

येथे फंक्शनचे एक साधे उदाहरण आहे INDEX(INDEX):

INDEX(A1:C10,2,3)
=INDEX(A1:C10,2,3)

सूत्र श्रेणी शोधते A1:C10आणि मध्ये सेल मूल्य परत करते 2राओळ आणि 3 मीस्तंभ, म्हणजे सेलमधून C2.

खूप सोपे, बरोबर? तथापि, सराव मध्ये, आपल्याला नेहमी माहित नसते की आपल्याला कोणत्या पंक्ती आणि स्तंभाची आवश्यकता आहे आणि म्हणून आपल्याला फंक्शनची मदत आवश्यक आहे. शोधा.

MATCH - फंक्शन सिंटॅक्स आणि वापर

कार्य मॅचएक्सेलमधील (MATCH) सेलच्या श्रेणीमध्ये निर्दिष्ट मूल्य शोधते आणि श्रेणीमध्ये त्या मूल्याचे संबंधित स्थान परत करते.

उदाहरणार्थ, श्रेणीमध्ये असल्यास B1:B3न्यू-यॉर्क, पॅरिस, लंडन या मूल्यांचा समावेश आहे, नंतर खालील सूत्र संख्या परत करेल 3 , कारण "लंडन" हा यादीतील तिसरा घटक आहे.

सामना("लंडन",B1:B3,0)
=MATCH("लंडन";B1:B3;0)

कार्य मॅच(MATCH) मध्ये खालील वाक्यरचना आहे:

MATCH(lookup_value,lookup_array,)
MATCH(lookup_value, lookup_array, [match_type])

• lookup_value(search_value) ही संख्या किंवा मजकूर तुम्ही शोधत आहात. वितर्क हे बुलियन किंवा सेल संदर्भासह मूल्य असू शकते.
• लुकअप_ॲरे(viewed_array) – सेलची श्रेणी ज्यामध्ये शोध होतो.
• match_type(match_type) - हा युक्तिवाद फंक्शन सांगतो शोधा, तुम्हाला अचूक किंवा अंदाजे जुळणी शोधायची आहे का:
  • 1 किंवा निर्दिष्ट नाही- इच्छित मूल्यापेक्षा कमी किंवा समान कमाल मूल्य शोधते. पाहिल्या जाणाऱ्या ॲरेला चढत्या क्रमाने, म्हणजेच सर्वात लहान ते सर्वात मोठ्या क्रमाने क्रमबद्ध करणे आवश्यक आहे.
  • 0 - इच्छित एक समान प्रथम मूल्य शोधते. संयोजनासाठी INDEX/शोधातुम्हाला नेहमी अचूक जुळणीची आवश्यकता असते, म्हणून फंक्शनचा तिसरा युक्तिवाद शोधासमान असणे आवश्यक आहे 0 .
  • -1 - शोध मूल्यापेक्षा मोठे किंवा समान सर्वात लहान मूल्य शोधते. पाहिल्या जाणाऱ्या ॲरेची उतरत्या क्रमाने क्रमवारी लावणे आवश्यक आहे, म्हणजेच सर्वात मोठ्या ते सर्वात लहान.

पहिल्या दृष्टीक्षेपात, फंक्शनचा फायदा शोधासंशयास्पद आहे. श्रेणीतील घटकाची स्थिती कोणाला माहित असणे आवश्यक आहे? आम्हाला या घटकाचा अर्थ जाणून घ्यायचा आहे!

आम्ही तुम्हाला स्मरण करून देऊ या की आम्ही शोधत असलेल्या मूल्याची सापेक्ष स्थिती (म्हणजे पंक्ती आणि/किंवा स्तंभ क्रमांक) हीच आम्हाला वितर्कांसाठी निर्दिष्ट करण्याची आवश्यकता आहे. पंक्ती_संख्या(लाइन_क्रमांक) आणि/किंवा स्तंभ_संख्या(स्तंभ_संख्या) कार्ये INDEX(INDEX). जसे तुम्हाला आठवते, फंक्शन INDEXदिलेल्या पंक्ती आणि स्तंभाच्या छेदनबिंदूवर मूल्य परत करू शकते, परंतु आम्हाला कोणत्या पंक्ती आणि स्तंभामध्ये स्वारस्य आहे हे निर्धारित करू शकत नाही.

Excel मध्ये INDEX आणि MATCH कसे वापरावे

आता तुम्हाला या दोन फंक्शन्सबद्दल मूलभूत माहिती माहित आहे, मला विश्वास आहे की फंक्शन्स कसे आहेत हे आधीच स्पष्ट होत आहे शोधाआणि INDEXएकत्र काम करू शकतात. शोधासेलच्या दिलेल्या श्रेणीतील शोध मूल्याची सापेक्ष स्थिती निर्धारित करते आणि INDEXती संख्या (किंवा संख्या) वापरते आणि संबंधित सेलमधून निकाल देते.

अद्याप पूर्णपणे स्पष्ट नाही? कार्ये सादर करा INDEXआणि शोधाया फॉर्ममध्ये:

INDEX(, (सामना ( शोध मूल्य,ज्या स्तंभात आपण पहात आहोत,0))
=INDEX( स्तंभ ज्यामधून आपण काढतो;(मॅच( शोध मूल्य;ज्या स्तंभात आपण पहात आहोत;0))

मला वाटते की एका उदाहरणाने समजून घेणे आणखी सोपे होईल. समजा तुमच्याकडे राज्यांच्या राजधानींची खालील यादी आहे:

चला खालील सूत्र वापरून एका राजधानीची लोकसंख्या शोधू, उदाहरणार्थ, जपान:

INDEX($D$2:$D$10,MATCH("जपान",$B$2:$B$10,0))
=INDEX($D$2:$D$10,MATCH("जपान",$B$2:$B$10,0))

आता या सूत्रातील प्रत्येक घटक काय करतो ते पाहू:

• कार्य मॅच(MATCH) स्तंभातील “जपान” मूल्य शोधते बी, आणि विशेषतः - पेशींमध्ये B2:B10, आणि एक संख्या मिळवते 3 , कारण "जपान" यादीत तिसऱ्या स्थानावर आहे.
• कार्य INDEX(INDEX) वापरतो 3 वादासाठी पंक्ती_संख्या(row_number), जे कोणत्या पंक्तीमधून मूल्य परत केले जावे हे निर्दिष्ट करते. त्या. आम्हाला एक साधे सूत्र मिळते:

  इंडेक्स($D$2:$D$10,3)
  =INDEX($D$2:$D$10,3)

  सूत्र असे काहीतरी सांगते: सेलमधून पहा D2आधी D10आणि तिसऱ्या रांगेतून, म्हणजेच सेलमधून मूल्य काढा D4, दुसऱ्या ओळीपासून मोजणी सुरू होते.

तुम्हाला Excel मध्ये मिळणारा हा परिणाम आहे:

महत्वाचे! फंक्शन वापरत असलेल्या ॲरेमधील पंक्ती आणि स्तंभांची संख्या INDEX(INDEX), वितर्क मूल्यांशी जुळले पाहिजे पंक्ती_संख्या(लाइन_क्रमांक) आणि स्तंभ_संख्या(स्तंभ_संख्या) कार्ये मॅच(मॅच). अन्यथा, सूत्राचा परिणाम चुकीचा असेल.

थांबा, थांबा... आपण फक्त फंक्शन का वापरू शकत नाही VLOOKUP(VPR)? चक्रव्यूह शोधण्यात वेळ वाया घालवण्यात काही अर्थ आहे का? शोधाआणि INDEX?

VLOOKUP("जपान",$B$2:$D$2,3)
=VLOOKUP("जपान",$B$2:$D$2,3)

या प्रकरणात, काही अर्थ नाही! या उदाहरणाचा उद्देश निव्वळ प्रात्यक्षिक हेतूंसाठी आहे जेणेकरून तुम्हाला समजेल की कार्य कसे होते शोधाआणि INDEXजोडी काम. खालील उदाहरणे तुम्हाला बंडलची खरी ताकद दाखवतील. INDEXआणि शोधा, जे अनेक कठीण प्रसंगांना सहजतेने सामोरे जाते VLOOKUPस्वत: ला मृतावस्थेत सापडतो.

VLOOKUP पेक्षा INDEX/MATCH का चांगले आहे?

उभ्या शोधासाठी कोणते सूत्र वापरायचे हे ठरवताना, बहुतेक एक्सेल गुरु असे मानतात INDEX/शोधापेक्षा बरेच चांगले VLOOKUP. तथापि, अनेक एक्सेल वापरकर्ते अजूनही वापरण्याचा अवलंब करतात VLOOKUP, कारण हे कार्य अधिक सोपे आहे. असे घडते कारण फार कमी लोकांना वरून स्विच करण्याचे सर्व फायदे पूर्णपणे समजतात VLOOKUPप्रति घड INDEXआणि शोधा, आणि कोणीही अधिक जटिल सूत्राचा अभ्यास करण्यात वेळ वाया घालवू इच्छित नाही.

एक्सेलमध्ये MATCH/INDEX वापरण्याचे 4 मुख्य फायदे:

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

2. स्तंभ सुरक्षितपणे जोडा किंवा काढा.फंक्शनसह सूत्रे VLOOKUPआपण लुकअप टेबलमध्ये स्तंभ काढल्यास किंवा जोडल्यास कार्य करणे थांबवा किंवा चुकीची मूल्ये परत करा. कार्यासाठी VLOOKUPकोणताही घातलेला किंवा काढलेला स्तंभ सूत्राचा परिणाम बदलेल कारण वाक्यरचना VLOOKUPतुम्हाला संपूर्ण श्रेणी आणि विशिष्ट स्तंभ क्रमांक निर्दिष्ट करणे आवश्यक आहे ज्यामधून तुम्ही डेटा काढू इच्छिता.

उदाहरणार्थ, आपल्याकडे टेबल असल्यास A1:C10, आणि तुम्हाला स्तंभातून डेटा पुनर्प्राप्त करायचा आहे बी, नंतर तुम्हाला मूल्य सेट करणे आवश्यक आहे 2 वादासाठी col_index_num(स्तंभ_संख्या) कार्ये VLOOKUP, याप्रमाणे:

VLOOKUP("लुकअप मूल्य",A1:C10,2)
=VLOOKUP("लुकअप मूल्य";A1:C10;2)

जर तुम्ही नंतर स्तंभांमध्ये नवीन स्तंभ घातला एआणि बी, नंतर वितर्काचे मूल्य वरून बदलावे लागेल 2 वर 3 , अन्यथा सूत्र नव्याने घातलेल्या स्तंभातून परिणाम देईल.

वापरत आहे शोधा/INDEXइच्छित मूल्य असलेला स्तंभ थेट परिभाषित केल्यामुळे, परिणाम विकृत न करता तुम्ही तपासल्या जात असलेल्या श्रेणीमध्ये स्तंभ काढू किंवा जोडू शकता. खरंच, हा एक मोठा फायदा आहे, विशेषत: जेव्हा आपल्याला मोठ्या प्रमाणात डेटासह कार्य करावे लागते. तुम्ही वापरत असलेल्या प्रत्येक फंक्शनचे निराकरण करण्याची काळजी न करता तुम्ही स्तंभ जोडू आणि काढू शकता VLOOKUP.

3. शोधलेल्या मूल्याच्या आकारावर मर्यादा नाही.वापरत आहे VLOOKUP, लक्षात ठेवा की शोधलेल्या मूल्याची लांबी 255 वर्णांपर्यंत मर्यादित आहे, अन्यथा तुम्हाला त्रुटी मिळण्याचा धोका आहे #मूल्य!(#मूल्य!). म्हणून, जर टेबलमध्ये लांब पंक्ती असतील तर, वापरणे हा एकमेव व्यवहार्य उपाय आहे INDEX/शोधा.

समजा तुम्ही हे सूत्र वापरा VLOOKUP, जे पासून सेलमध्ये शोधते B5आधी D10सेलमध्ये निर्दिष्ट केलेले मूल्य A2:

VLOOKUP(A2,B5:D10,3,FALSE)
=VLOOKUP(A2,B5:D10,3,FALSE)

सेलमधील मूल्य असल्यास सूत्र कार्य करणार नाही A2 255 वर्णांपेक्षा मोठे. त्याऐवजी, तुम्हाला एक समान सूत्र वापरण्याची आवश्यकता आहे INDEX/शोधा:

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))

4. उच्च ऑपरेटिंग गती.जर तुम्ही लहान सारण्यांसह काम करत असाल, तर एक्सेल कार्यप्रदर्शनातील फरक बहुधा लक्षात येणार नाही, विशेषत: नवीनतम आवृत्त्यांमध्ये. तुम्ही हजारो पंक्ती आणि शेकडो शोध सूत्रे असलेल्या मोठ्या सारण्यांसह काम केल्यास, तुम्ही वापरल्यास एक्सेल अधिक जलद कार्य करेल शोधाआणि INDEXऐवजी VLOOKUP. सर्वसाधारणपणे, ही बदली एक्सेलची गती वाढवते 13% .

प्रभाव VLOOKUPवर्कबुकमध्ये शेकडो क्लिष्ट ॲरे फॉर्म्युले असल्यास एक्सेल कामगिरी विशेषतः लक्षात येण्याजोगी असते, जसे की VLOOKUP+SUM. वस्तुस्थिती अशी आहे की ॲरेमधील प्रत्येक मूल्य तपासण्यासाठी स्वतंत्र फंक्शन कॉल आवश्यक आहे VLOOKUP. त्यामुळे, ॲरेमध्ये जितकी जास्त व्हॅल्यू असतील आणि तुमच्या टेबलमध्ये जितके ॲरे फॉर्म्युले असतील तितके एक्सेल हळू काम करेल.

दुसरीकडे, फंक्शन्ससह एक सूत्र शोधाआणि INDEXहे फक्त एक शोध करते आणि परिणाम परत करते, समान कार्य लक्षणीयरीत्या वेगाने करते.

इंडेक्स आणि मॅच - सूत्रांची उदाहरणे

आता तुम्ही फंक्शन्स का शिकले पाहिजेत याची कारणे तुम्हाला समजली आहेत शोधाआणि INDEX, चला मजेदार भागाकडे जाऊया आणि आपण सैद्धांतिक ज्ञान व्यवहारात कसे लागू करू शकता ते पाहू या.

MATCH आणि INDEX वापरून डावीकडून कसे शोधायचे

कोणतेही पाठ्यपुस्तक चालू VLOOKUPम्हणते की हे फंक्शन डावीकडे पाहू शकत नाही. त्या. जर तुम्ही पहात असलेला स्तंभ शोध श्रेणीतील सर्वात डावीकडे नसेल, तर येथून येण्याची शक्यता नाही VLOOKUPइच्छित परिणाम.

कार्ये शोधाआणि INDEXएक्सेल अधिक लवचिक आहे आणि आपण पुनर्प्राप्त करू इच्छित मूल्य असलेला स्तंभ कोठे स्थित आहे याची काळजी घेत नाही. उदाहरणार्थ, राज्यांच्या राजधानी आणि लोकसंख्येसह टेबलवर परत येऊ. यावेळी आपण सूत्र लिहू शोधा/INDEX, जे लोकसंख्येच्या दृष्टीने रशियाची राजधानी (मॉस्को) कोणते स्थान व्यापते हे दर्शवेल.

तुम्ही खालील आकृतीत पाहू शकता, सूत्र हे काम उत्तम प्रकारे करते:

INDEX($A$2:$A$10,MATCH("रशिया",$B$2:$B$10,0))

आता हे सूत्र कसे कार्य करते हे समजून घेण्यात तुम्हाला कोणतीही अडचण नसावी:

• प्रथम, फंक्शन वापरू मॅच(MATCH), जे सूचीमध्ये "रशिया" चे स्थान शोधते:

  सामना("रशिया",$B$2:$B$10,0))
  =MATCH("रशिया",$B$2:$B$10,0))

• पुढे, फंक्शनसाठी श्रेणी सेट करा INDEX(INDEX) ज्यामधून मूल्य काढायचे आहे. आमच्या बाबतीत ते आहे A2:A10.
• मग आम्ही दोन्ही भाग एकत्र करतो आणि सूत्र मिळवतो:

  INDEX($A$2:$A$10;MATCH("रशिया";$B$2:$B$10,0))
  =INDEX($A$2:$A$10,MATCH("रशिया",$B$2:$B$10,0))

सुगावा:योग्य उपाय म्हणजे नेहमी साठी निरपेक्ष संदर्भ वापरणे INDEXआणि शोधा, जेणेकरुन इतर सेलमध्ये सूत्र कॉपी करताना शोध श्रेणी गमावल्या जाणार नाहीत.

Excel मध्ये INDEX आणि MATCH वापरून गणना (सरासरी, MAX, MIN)

तुम्ही इतर एक्सेल फंक्शन्स आत नेस्ट करू शकता INDEXआणि शोधा, उदाहरणार्थ, किमान, कमाल किंवा सरासरी मूल्याच्या सर्वात जवळ शोधण्यासाठी. वरील सारणीशी संबंधित सूत्रांसाठी येथे अनेक पर्याय आहेत:

1. MAX(MAX). सूत्र एका स्तंभात कमाल शोधते डी सीसमान ओळ:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))

निकाल: बीजिंग

2. मि(MIN). सूत्र एका स्तंभात किमान शोधते डीआणि स्तंभातून मूल्य परत करते सीसमान ओळ:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))

निकाल: लिमा

3. सरासरी(सरासरी). सूत्र श्रेणीची सरासरी काढते D2:D10, नंतर त्याच्या सर्वात जवळचे शोधते आणि स्तंभातून मूल्य परत करते सीसमान ओळ:

INDEX($C$2:$C$10,MATCH(Average($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,MATCH(Average($D$2:D$10),$D$2:D$10,1))

निकाल: मॉस्को

INDEX आणि MATCH सह AVERAGE फंक्शन वापरताना लक्षात ठेवण्यासारख्या गोष्टी

फंक्शन वापरणे सरासरीसह संयोजनात INDEXआणि शोधा, फंक्शनचा तिसरा वितर्क म्हणून शोधाबर्याचदा आपल्याला सूचित करावे लागेल 1 किंवा -1 जर तुम्हाला खात्री नसेल की तुम्ही पहात असलेल्या श्रेणीमध्ये सरासरीच्या बरोबरीचे मूल्य आहे. जर तुम्हाला खात्री असेल की असे मूल्य अस्तित्वात आहे, तर ठेवा 0 अचूक जुळणी शोधण्यासाठी.

• आपण सूचित केल्यास 1 , लुकअप स्तंभातील मूल्ये चढत्या क्रमाने क्रमाने लावली पाहिजेत आणि सूत्र सरासरीपेक्षा कमी किंवा समान कमाल मूल्य परत करेल.
• आपण सूचित केल्यास -1 , लुकअप स्तंभातील मूल्ये उतरत्या क्रमाने क्रमाने लावली पाहिजेत आणि सरासरीपेक्षा मोठे किंवा समान किमान मूल्य परत केले जाईल.

आमच्या उदाहरणात, स्तंभातील मूल्ये डीचढत्या क्रमाने क्रम दिले आहेत, म्हणून आम्ही कोलेशन प्रकार वापरतो 1 . सुत्र INDEX/शोधपोझेड"मॉस्को" परत करते, कारण मॉस्को शहराची लोकसंख्या सरासरी मूल्याच्या (12,269,006) सर्वात जवळ आहे.

ज्ञात पंक्ती आणि स्तंभ शोधण्यासाठी INDEX आणि MATCH कसे वापरावे

हे सूत्र द्विमितीय शोधाच्या समतुल्य आहे VLOOKUPआणि तुम्हाला विशिष्ट पंक्ती आणि स्तंभाच्या छेदनबिंदूवर मूल्य शोधण्याची परवानगी देते.

या उदाहरणात सूत्र INDEX/शोधाया धड्यात आपण आधीच चर्चा केलेल्या सूत्रांशी अगदी समानता असेल, फक्त एका फरकासह. कोणते अंदाज लावा?

जसे तुम्हाला आठवते, फंक्शन सिंटॅक्स INDEX(INDEX) तीन वितर्कांना अनुमती देते:

INDEX(अरे,पंक्ती_संख्या,)
INDEX(ॲरे, पंक्ती_संख्या, [स्तंभ_संख्या])

आणि मी तुमच्यापैकी ज्यांनी याचा अंदाज लावला त्यांचे अभिनंदन!

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

INDEX(तुमचा टेबल ,(MATCH(, शोधण्यासाठी स्तंभ,0)),(मॅच(, शोधण्यासाठी ओळ,0))
=INDEX(तुमचा टेबल ,(MATCH( अनुलंब शोधासाठी मूल्य,शोधण्यासाठी स्तंभ,0)),(मॅच( क्षैतिज शोधासाठी मूल्य,शोधण्यासाठी ओळ,0))

लक्षात घ्या की द्विमितीय शोधासाठी तुम्हाला युक्तिवादात संपूर्ण सारणी निर्दिष्ट करणे आवश्यक आहे रचना(ॲरे) फंक्शन्स INDEX(INDEX).

आता हा नमुना सरावात वापरून पाहू. खाली तुम्हाला जगातील सर्वाधिक लोकसंख्या असलेल्या देशांची यादी दिसेल. समजा 2015 मध्ये युनायटेड स्टेट्सची लोकसंख्या शोधणे हे आमचे कार्य आहे.

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

तर दोन फंक्शन्सने सुरुवात करूया शोधा, जे फंक्शनसाठी पंक्ती आणि स्तंभ क्रमांक परत करेल INDEX:

• स्तंभासाठी जुळणी- आम्ही स्तंभात पाहत आहोत बी, किंवा त्याऐवजी श्रेणीत B2:B11, सेलमध्ये निर्दिष्ट केलेले मूल्य H2(संयुक्त राज्य). फंक्शन असे दिसेल:

  जुळणी($H$2,$B$1:$B$11,0)
  =MATCH($H$2,$B$1:$B$11,0)

  4 , कारण "USA" हा स्तंभातील 4 था सूची घटक आहे बी(शीर्षकासह).

• स्ट्रिंगसाठी जुळणी- आम्ही सेल मूल्य शोधत आहोत H3(2015) ओळीत 1 , म्हणजे पेशींमध्ये A1:E1:

  जुळणी($H$3,$A$1:$E$1,0)
  =MATCH($H$3,$A$1:$E$1,0)

  या सूत्राचा परिणाम होईल 5 , कारण "2015" 5व्या स्तंभात आहे.

आता आपण फंक्शनमध्ये ही सूत्रे समाविष्ट करू INDEXआणि व्होइला:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))

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

इंडेक्स($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))

हे सूत्र छेदनबिंदूवरील मूल्य परत करते 4 थाओळी आणि 5 वाश्रेणीतील स्तंभ A1:E11, म्हणजे, सेल मूल्य E4. फक्त? होय!

INDEX आणि MATCH सह बहु-निकष शोध

वरील ट्यूटोरियलमध्ये VLOOKUPआम्ही फंक्शनसह सूत्राचे उदाहरण दाखवले VLOOKUPएकाधिक निकष वापरून शोधण्यासाठी. तथापि, या सोल्यूशनची एक महत्त्वपूर्ण मर्यादा म्हणजे सहायक स्तंभ जोडण्याची आवश्यकता होती. चांगली बातमी: सूत्र INDEX/शोधाहेल्पर कॉलम तयार न करता, दोन कॉलममध्ये व्हॅल्यूज शोधू शकतात!

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

हे सूत्र आहे INDEX/शोधासमस्या सोडवते:

(=INDEX("लूकअप टेबल"!$A$2:$C$13,MATCH(1,(A2="लूकअप टेबल"!$A$2:$A$13)*
(B2="लूकअप टेबल"!$B$2:$B$13),0),3))
(=INDEX("लूकअप टेबल"!$A$2:$C$13;MATCH(1,(A2="लूकअप टेबल"!$A$2:$A$13)*
(B2="लूकअप टेबल"!$B$2:$B$13);0);3))

हे सूत्र आपण आधी चर्चा केलेल्या इतरांपेक्षा अधिक जटिल आहे, परंतु कार्यांच्या ज्ञानाने सज्ज आहे INDEXआणि शोधातू तिचा पराभव करशील. सर्वात कठीण भाग म्हणजे कार्य शोधा, मला वाटते ते आधी स्पष्ट करणे आवश्यक आहे.

MATCH(1,(A2="लूकअप टेबल"!$A$2:$A$13),0)*(B2="लूकअप टेबल"!$B$2:$B$13)
MATCH(1;(A2="लूकअप टेबल"!$A$2:$A$13);0)*(B2="लूकअप टेबल"!$B$2:$B$13)

वर दर्शविलेल्या सूत्रामध्ये, आम्ही शोधत असलेले मूल्य आहे 1 , आणि शोध ॲरे हा गुणाकाराचा परिणाम आहे. ठीक आहे, आपण काय गुणाकार केले पाहिजे आणि का? चला क्रमाने सर्वकाही पाहू:

• स्तंभातील पहिले मूल्य घ्या ए(ग्राहक) शीटवर मुख्य टेबलआणि शीटवरील टेबलमधील सर्व ग्राहकांच्या नावांशी तुलना करा टेबल पहा(A2:A13).
• जुळणी आढळल्यास, समीकरण परत येते 1 (सत्य), आणि नसल्यास - 0 (खोटे).
• पुढे, आपण स्तंभ मूल्यांसाठी तेच करतो बी(उत्पादन).
• मग आम्ही प्राप्त परिणाम गुणाकार (1 आणि 0). दोन्ही स्तंभांमध्ये जुळण्या आढळल्या तरच (म्हणजे दोन्ही निकष खरे आहेत), तुम्हाला प्राप्त होईल 1 . दोन्ही निकष खोटे असल्यास, किंवा त्यापैकी फक्त एक समाधानी असल्यास, तुम्हाला प्राप्त होईल 0 .

आम्ही का विचारले ते आता तुम्हाला समजले आहे 1 , इच्छित मूल्य काय आहे? हे कार्य योग्य आहे शोधादोन्ही निकष पूर्ण झाल्यावरच पद परत केले.

टीप:या प्रकरणात, तुम्ही फंक्शनचा तिसरा पर्यायी युक्तिवाद वापरला पाहिजे INDEX. ते आवश्यक आहे कारण पहिल्या युक्तिवादात आपण संपूर्ण सारणी निर्दिष्ट करतो आणि कोणत्या स्तंभातून मूल्य पुनर्प्राप्त करायचे हे कार्य सांगणे आवश्यक आहे. आमच्या बाबतीत हा स्तंभ आहे सी(सम), आणि म्हणून आम्ही प्रवेश केला 3 .

आणि शेवटी, कारण आपल्याला ॲरेमधील प्रत्येक सेल तपासण्याची आवश्यकता आहे, हे सूत्र ॲरे सूत्र असणे आवश्यक आहे. ते बंद केलेल्या कुरळे ब्रेसेसद्वारे तुम्ही हे पाहू शकता. त्यामुळे तुम्ही सूत्र प्रविष्ट केल्यानंतर, क्लिक करण्यास विसरू नका Ctrl+Shift+Enter.

सर्वकाही योग्यरित्या केले असल्यास, आपल्याला खालील चित्राप्रमाणे परिणाम मिळेल:

INDEX आणि MATCH Excel मध्ये IFERROR सह एकत्रित

तुम्ही कदाचित आधीच लक्षात घेतले असेल (एकापेक्षा जास्त वेळा), जर तुम्ही चुकीचे मूल्य प्रविष्ट केले असेल, उदाहरणार्थ, पाहिले जात असलेल्या ॲरेमध्ये नसलेले, सूत्र INDEX/शोधात्रुटी नोंदवते #N/A(#N/A) किंवा #मूल्य!(#मूल्य!). जर तुम्हाला अशा संदेशाला अधिक समजण्याजोगे काहीतरी बदलायचे असेल, तर तुम्ही यासह एक सूत्र समाविष्ट करू शकता INDEXआणि शोधाफंक्शन मध्ये IFERROR.

फंक्शन सिंटॅक्स IFERRORखूप सोपे:

IFERROR(मूल्य,मूल्य_जर_त्रुटी)
IFERROR(मूल्य,मूल्य_जर_त्रुटी)

कोठें वाद मूल्य(मूल्य) त्रुटीसाठी तपासले जाणारे मूल्य आहे (आमच्या बाबतीत, सूत्राचा परिणाम INDEX/शोधा); आणि युक्तिवाद value_if_error(value_if_error) हे फॉर्म्युला एरर टाकल्यास परत केले जाणारे मूल्य आहे.

उदाहरणार्थ, तुम्ही फंक्शनमध्ये घालू शकता IFERRORयाप्रमाणे:

IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"कोणत्याही जुळण्या आढळल्या नाहीत. पुन्हा प्रयत्न करा!") =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 : $E$1;0));
"कोणत्याही जुळण्या आढळल्या नाहीत. पुन्हा प्रयत्न करा!")

आणि आता, जर कोणी चुकीचे मूल्य प्रविष्ट केले तर, सूत्र हा परिणाम देईल:

एरर आल्यास तुम्ही सेल रिकामा ठेवण्यास प्राधान्य दिल्यास, फंक्शनच्या दुसऱ्या वितर्काचे मूल्य म्हणून तुम्ही कोट्स (“”) वापरू शकता. IFERROR. याप्रमाणे:

IFERROR(INDEX(ॲरे,MATCH(lookup_value,lookup_array,0),"")
IFERROR(INDEX(ॲरे,MATCH(lookup_value,looked_array,0),"")

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

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

या लेखात

अचूक जुळणी करून अनुलंब सूचीमध्ये मूल्ये शोधा

हे कार्य करण्यासाठी तुम्ही VLOOKUP फंक्शन किंवा INDEX आणि MATCH फंक्शन्सचे संयोजन वापरू शकता.

VLOOKUP फंक्शन वापरण्याची उदाहरणे

VLOOKUP कार्य.

INDEX आणि जुळण्यांची उदाहरणे

याचा अर्थ काय आहे:

=INDEX(तुम्हाला C2:C10 वरून मूल्य परत करणे आवश्यक आहे, जे MATCH (ॲरे B2:B10 मधील पहिले मूल्य "कोबी") शी जुळेल)

सूत्र सेल C2:C10 मधील पहिले मूल्य शोधते कोबी(B7 मध्ये), आणि C7 मधील मूल्य मिळवते ( 100 ) - संबंधित प्रथम मूल्य कोबी.

अधिक माहितीसाठी, INDEX फंक्शन आणि मॅच फंक्शन पहा.

अंदाजे जुळणीनुसार अनुलंब सूचीमध्ये मूल्ये शोधा

हे करण्यासाठी, VLOOKUP फंक्शन वापरा.

महत्त्वाचे:पहिल्या रांगेतील मूल्ये चढत्या क्रमाने लावलेली असल्याची खात्री करा.

वरील उदाहरणामध्ये, VLOOKUP फंक्शन A2:B7 या श्रेणीमध्ये 6 टर्डी असलेल्या विद्यार्थ्याचे नाव शोधते. साठी टेबलमध्ये कोणतीही नोंद नाही 6 उशिरा, म्हणून VLOOKUP फंक्शन 6 च्या खाली पुढील सर्वोच्च जुळणी शोधते आणि पहिल्या नावाशी संबंधित मूल्य 5 शोधते डेव्ह, आणि म्हणून परत येतो डेव्ह.

अधिक माहितीसाठी, VLOOKUP कार्य पहा.

अचूक जुळणीसह अज्ञात आकाराच्या सूचीमध्ये अनुलंब मूल्ये शोधणे

हे कार्य करण्यासाठी, OFFSET आणि MATCH कार्ये वापरा.

टीप:तुम्ही दररोज अपडेट करत असलेल्या बाह्य डेटा श्रेणीमध्ये डेटा असल्यास हा दृष्टिकोन वापरला जातो. तुम्हाला माहीत आहे की कॉलम B ची किंमत आहे, परंतु सर्व्हर डेटाच्या किती पंक्ती परत करतो हे तुम्हाला माहीत नाही आणि पहिला कॉलम वर्णक्रमानुसार लावलेला नाही.

C1श्रेणीचा वरचा डावा सेल आहे (याला प्रारंभिक सेल देखील म्हणतात).

जुळणी("संत्रा"; C2: C7; 0) C2:C7 श्रेणीमध्ये केशरी रंग शोधतो. तुम्ही सुरुवातीचा सेल श्रेणीमध्ये समाविष्ट करू नये.

1 - सुरुवातीच्या सेलच्या उजवीकडील स्तंभांची संख्या ज्यासाठी रिटर्न व्हॅल्यू परत केले जावे. आमच्या उदाहरणात, रिटर्न व्हॅल्यू स्तंभ D मध्ये आहे, विक्री.

अचूक जुळणी करून क्षैतिजरित्या सूचीमध्ये मूल्ये शोधा

हे कार्य करण्यासाठी, GLOOKUP फंक्शन वापरले जाते. खाली एक उदाहरण आहे.

LOOKUP फंक्शन कॉलम शोधते विक्रीआणि निर्दिष्ट श्रेणीतील ओळ 5 वरून मूल्य परत करते.

अधिक माहितीसाठी, LOOKUP कार्ये पहा.

लुकअप विझार्ड वापरून लुकअप फॉर्म्युला तयार करा (फक्त एक्सेल 2007)

टीप:लुकअप विझार्ड ऍड-इन एक्सेल 2010 मध्ये बंद करण्यात आले आहे. ही कार्यक्षमता फंक्शन विझार्ड आणि उपलब्ध शोध आणि संदर्भ (संदर्भ) कार्यक्षमतेने बदलली आहे.

एक्सेल 2007 मध्ये, लुकअप विझार्ड वर्कशीट डेटावर आधारित एक लुकअप फॉर्म्युला तयार करतो ज्यामध्ये पंक्ती आणि स्तंभ हेडिंग असतात. लुकअप विझार्ड तुम्हाला एका स्तंभातील मूल्य माहित असताना आणि त्याउलट सलग इतर मूल्ये शोधण्यात मदत करतो. लुकअप विझार्ड तयार केलेल्या सूत्रांमध्ये अनुक्रमणिका आणि जुळणी वापरतो.

एक्सेल ऑफिस प्रोग्रामचा मुख्य उद्देश गणना करणे हा आहे. या प्रोग्रामच्या दस्तऐवजात (पुस्तक) संख्या, मजकूर किंवा सूत्रांनी भरलेल्या लांब सारण्यांसह अनेक पत्रके असू शकतात. स्वयंचलित द्रुत शोध आपल्याला त्यांच्यामध्ये आवश्यक सेल शोधण्याची परवानगी देतो.

साधा शोध

एक्सेल टेबलमध्ये मूल्य शोधण्यासाठी, तुम्हाला "होम" टॅबवर शोधा आणि बदला टूलची ड्रॉप-डाउन सूची उघडणे आवश्यक आहे आणि "शोधा" वर क्लिक करणे आवश्यक आहे. हाच प्रभाव कीबोर्ड शॉर्टकट Ctrl + F वापरून मिळवता येतो.

सर्वात सोप्या प्रकरणात, दिसत असलेल्या "शोधा आणि बदला" विंडोमध्ये, तुम्हाला इच्छित मूल्य प्रविष्ट करणे आणि "सर्व शोधा" क्लिक करणे आवश्यक आहे.

तुम्ही बघू शकता, शोध परिणाम डायलॉग बॉक्सच्या तळाशी दिसू लागले आहेत. सापडलेली मूल्ये टेबलमध्ये लाल रंगात अधोरेखित केली आहेत. “सर्व शोधा” ऐवजी तुम्ही “पुढील शोधा” वर क्लिक केल्यास, या मूल्यासह प्रथम सेल शोधला जाईल आणि जेव्हा तुम्ही पुन्हा क्लिक करता तेव्हा दुसरा शोधला जाईल.

मजकूर शोध त्याच प्रकारे केला जातो. या प्रकरणात, शोधलेला मजकूर शोध बारमध्ये टाइप केला जातो.

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

प्रगत शोध

समजा तुम्हाला 3000 ते 3999 या श्रेणीतील सर्व मूल्ये शोधायची आहेत. या प्रकरणात, तुम्ही शोध बारमध्ये 3??? टाइप कराल. वाइल्डकार्ड "?" इतर कोणत्याही बदलते.

शोध परिणामांचे विश्लेषण करताना, हे लक्षात घेतले जाऊ शकते की, योग्य 9 परिणामांसह, प्रोग्रामने लाल रंगात हायलाइट केलेले अनपेक्षित देखील तयार केले. ते सेल किंवा सूत्रामध्ये क्रमांक 3 च्या उपस्थितीशी संबंधित आहेत.

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

"पर्याय" वर क्लिक करून, वापरकर्त्याकडे प्रगत शोध करण्याची क्षमता आहे. सर्व प्रथम, "शोध क्षेत्र" आयटमकडे लक्ष द्या, जे डीफॉल्टनुसार "सूत्र" वर सेट केले आहे.

याचा अर्थ असा आहे की ज्या सेलमध्ये मूल्य नाही, परंतु एक सूत्र आहे अशा सेलसह शोध घेण्यात आला. त्यापैकी 3 क्रमांकाच्या उपस्थितीने तीन चुकीचे निकाल दिले. तुम्ही शोध स्कोप म्हणून "मूल्ये" निवडल्यास, तुम्ही फक्त डेटा शोधाल आणि फॉर्म्युला सेलशी संबंधित चुकीचे परिणाम अदृश्य होतील.

पहिल्या ओळीवर फक्त उर्वरित चुकीच्या निकालापासून मुक्त होण्यासाठी, प्रगत शोध विंडोमध्ये तुम्हाला "संपूर्ण सेल" आयटम निवडण्याची आवश्यकता आहे. यानंतर, शोध परिणाम 100% अचूक होतो.

हा परिणाम ताबडतोब “संपूर्ण सेल” आयटम निवडून प्राप्त केला जाऊ शकतो (“शोध क्षेत्र” मध्ये “फॉर्म्युला” मूल्य सोडून देखील).

आता "शोध" आयटमकडे वळू.

डीफॉल्ट “ऑन शीट” ऐवजी तुम्ही “वर्कबुकमध्ये” निवडल्यास, तुम्ही शोधत असलेल्या सेलच्या शीटवर असण्याची गरज नाही. स्क्रीनशॉट दर्शवितो की वापरकर्त्याने रिक्त शीट 2 वर असताना शोध सुरू केला.

प्रगत शोध विंडोमधील पुढील आयटम "दृश्य" आहे, ज्याचे दोन अर्थ आहेत. डीफॉल्ट "पंक्तीनुसार" आहे, ज्याचा अर्थ असा आहे की सेल एका ओळीने स्कॅन केले जातात. वेगळे मूल्य निवडणे – “स्तंभांनुसार” – केवळ शोध दिशा आणि परिणामांचा क्रम बदलेल.

Microsoft Excel दस्तऐवजांमध्ये शोधताना, तुम्ही दुसरे वाइल्डकार्ड वर्ण वापरू शकता – “*”. मानले तर "?" कोणत्याही वर्णाचा अर्थ, नंतर “*” एक नव्हे तर कितीही वर्ण बदलतो. खाली लुईझियाना शोधाचा स्क्रीनशॉट आहे.

कधीकधी शोध घेताना वर्णांची स्थिती विचारात घेणे आवश्यक असते. लुझियाना हा शब्द कॅपिटल केलेला असल्यास, शोध परिणाम बदलणार नाहीत. परंतु आपण प्रगत शोध विंडोमध्ये "केस जुळवा" निवडल्यास, शोध अयशस्वी होईल. कार्यक्रम लुईझियाना आणि लुईझियाना या दोन शब्दांचा विचार करेल आणि स्वाभाविकच, त्यापैकी पहिला शब्द सापडणार नाही.

शोधाचे प्रकार

जुळण्या शोधा

कधीकधी टेबलमधील डुप्लिकेट मूल्ये शोधणे आवश्यक असते. जुळण्या शोधण्यासाठी, तुम्हाला प्रथम शोध श्रेणी निवडणे आवश्यक आहे. त्यानंतर, त्याच “होम” टॅबवर, “शैली” गटामध्ये, “कंडिशनल फॉरमॅटिंग” टूल उघडा. पुढे, "सेल्स हायलाइट करण्याचे नियम" आणि "रिपीटिंग व्हॅल्यूज" या बाबी क्रमशः निवडा.

परिणाम खालील स्क्रीनशॉटमध्ये दर्शविला आहे.

आवश्यक असल्यास, वापरकर्ता जुळलेल्या सेलच्या व्हिज्युअल डिस्प्लेचा रंग बदलू शकतो.

गाळणे

शोधाचा दुसरा प्रकार म्हणजे फिल्टरिंग. चला असे गृहीत धरू की वापरकर्त्याला स्तंभ B मध्ये 3000 ते 4000 या श्रेणीतील संख्यात्मक मूल्ये शोधायची आहेत.

जसे आपण पाहू शकता, प्रविष्ट केलेल्या स्थितीचे समाधान करणार्या फक्त पंक्ती प्रदर्शित केल्या जातात. बाकी सर्व तात्पुरते लपलेले होते. प्रारंभिक स्थितीकडे परत येण्यासाठी, चरण 2 पुन्हा करा.

उदाहरण म्हणून एक्सेल 2010 वापरून विविध शोध पर्यायांवर चर्चा करण्यात आली. इतर आवृत्त्यांच्या एक्सेलमध्ये कसे शोधायचे? आवृत्ती 2003 मध्ये फिल्टरिंगच्या संक्रमणामध्ये फरक आहे. “डेटा” मेनूमध्ये, तुम्ही क्रमशः “फिल्टर”, “ऑटो फिल्टर”, “कंडिशन” आणि “कस्टम ऑटो फिल्टर” या कमांड्स निवडल्या पाहिजेत.

व्हिडिओ: एक्सेल टेबलमध्ये शोधा