अॅडव्हान्स एक्सेल : भाग आठवा ( VLookup )

आजकाल आपल्या कानावर अॅडव्हान्स एक्सेल हा शब्द खूप मोठ्या प्रमाणावर पडू लागला आहे. एक्सेलच्या विषयावर चर्चा चालू आहे आणि कुणी अॅडव्हान्स एक्सेल हा शब्द वापरला नाही असे सहसा होत नाही. आणि एकदा का हा शब्द आला की मग पर्यायाने अजून एक शब्द आपल्या कानावर पडतो. व्हीलुकअप नावाचा. ( VLOOKUP ). बरे जवळपास ९०% लोकांना फक्त हा शब्दच तेवढा माहित असतो. त्याचा वापर किंवा उपयोग कुठे आणि कसा करायचा हे मात्र समजत नाही. काही जण खास पुस्तके आणून याचा आभ्यास करू लागतात. पण पहिल्या अर्ध्या तासातच पुस्तक बाजूला ठेवले जाते आणि ‘यार... ते vlookup function खूपच अवघड आहे रे... काही समजत नाही.’ हे वाक्य बोलायला मोकळे होतात. बहुतांशी लोकं फक्त ते एक खूप अवघड function आहे इतकेच धरून चालतात. पण खरंच का ते इतके अवघड आहे? मला विचाराल तर बिलकुल नाही. एकदम सोपे function आहे. मग प्रश्न हा पडतो की बहुतांशी लोकांना ते अवघड का वाटत असावे? याचे मला फक्त एकच कारण वाटते, ते म्हणजे कुणीतरी सांगितले म्हणून लोकं हे function वापरण्याचा अट्टाहास करतात. कित्येक वेळेस तर त्यांना त्याची गरजही नसते. आणि दुसऱ्यासाठी तुम्ही एखादी गोष्ट कराल तर अवघड वाटणारच.

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

एक्सेल मध्ये function चे ग्रुप बनवले गेले आहेत. सगळ्यांना सोपे जावे यासाठी. त्यातील एक ग्रुप म्हणजे lookup. या ग्रुपमध्ये आपणास अनेक function दिसतात. LOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH इत्यादी. यातील जास्त वापरले जाणारे function म्हणजे VLOOKUP. थोडक्यात साठवलेल्या माहितीच्या टेबल मधून उभ्या रकान्याच्या माध्यमातून एखादी माहिती शोधणे. HLOOPUP म्हणजे साठवलेल्या माहितीच्या टेबल मधून ओळीच्या माध्यमातून एखादी माहिती शोधणे. LOOKUP मात्र याहून थोडे वेगळे आहे.

आता हे VLOOKUP कधी वापरावे? समजा एखाद्या ठिकाणी आपण एखादी माहिती साठवून ठेवतो. जी सहसा बदलत नाही. पण तीची गरज नेहमीच लागते अशावेळेस हे vlookup खूप उपयोगी पडते. आपण एक उदाहरण पाहू. समजा मी एखाद्या कंपनीत मानव संसाधन व्यवस्थापनाचे काम करतोय... ( ओह... जास्तच बोजड शब्द वापरलेत का? म्हणजे Human Resource Management - HR Manager हो. ) मग माझ्याकडे सगळ्यात मोठे काम कोणते असेल तर माझ्या कंपनीत असलेल्या कामगारांचे पगारपत्रक काढणे. अनेकांना हे काम खूप किचकट वाटते. कारण वेगवेगळ्या लोकांचा वेगवेगळा Basic Pay, अधिक वेगवेगळे भत्ते, त्यातही ते भत्ते एकाच दराने असतील असे नाही. काही भत्ते तर त्या कर्मचाऱ्याच्या हुद्द्यानुसार कमी जास्त असतात. बरे त्याला देण्यात येणारा पगारही तो किती दिवस हजर होता त्यानुसार बदलणारा असू शकतो. बरोबर ना? अशा वेळेस आपल्याला VLookup खूप फायदेशीर पडते. याच गोष्टींचे आपण प्रात्यक्षिक करून पाहणार आहोत.

सगळ्यात आधी आपण त्याचा syntax पाहू.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

या फंक्शन मध्ये काही parameter आपल्याला द्यावे लागतात. त्याबद्दल थोडेसे.

१. Lookup Value : याआधी सांगितल्याप्रमाणे VLookup Function हे आपल्याला त्याच्याकडील उपलब्ध माहितीमधून विशिष्ट माहिती देणार असते. पण आपल्याला काय पाहिजे ते त्याला नको का समजायला? त्यासाठी ही माहिती आधी आपल्याला त्याला द्यावी लागते.

२. Table Array : आपल्याला हवी असणारी माहिती त्याने कुठे शोधावी याचा पत्ता या भागात दिला जातो.

३. Column Index Number : आपण दिलेल्या टेबल मधून कोणत्या रकान्यातील माहिती त्याने आपल्याला द्यावी हे आपण या भागात सांगतो.

४. Range Lookup : हे parameter चौकोनी कंसात दिले आहे. याचा अर्थ हे ऐच्छिक ( optional ) आहे. या भागात आपल्याला False (0) किंवा True (1) यापैकी एक गोष्ट द्यायची असते. आपण काहीच दिले नाही तर त्याचा अर्थ True असा समजला जातो. काय फरक आहे या दोन्हीमध्ये? यातील False हा पर्याय निवडल्यास आपल्याला Exact Match दिले जाते. म्हणजे जर आपण शोधात असलेला शब्द / संख्या उपलब्ध माहितीच्या तक्त्यात ( टेबल हो ) दिलेल्या माहितीशी तंतोतंत जुळणारी असेल तरच आपल्याला दाखवली जाते. आणि Approximate Match या पर्याय निवडल्यानंतर आपल्याला दिली जाणारी माहिती ही आपण शोधत असलेल्या माहितीशी साधर्म्य असणारी किंवा त्याच्या जवळ जाणारी असते.

टीप : यात एक गोष्ट आपल्याला लक्षात ठेवावी लागते. ती म्हणजे आपण जो टेबल माहिती साठविण्यासाठी वापरणार आहोत आणि ज्या रकान्याच्या आधारे ही माहिती शोधली जाणार आहे, ती sort असली पाहिजे. नाहीतर आपल्याला मिळणारा रिझल्ट चुकीचा येण्याची जास्त शक्यता असते. अनेक जण आपल्याला VLookup अवघड जाते हे याच एका कारणाने सांगतात. त्यांनी फंक्शन बरोबर वापरलेले असते. टेबल मध्ये माहितीही व्यवस्थित भरलेली असते आणि तरीही त्यांना पाहिजे असलेला रिझल्ट येत नसतो. त्याचे कारण चूक फंक्शन लिहिण्यात नाही तर माहिती साठविण्यात असते.

Picture 1.1

सोबतच्या चित्रात ( Picture 1.1 ) एक टेबल तयार केले आहे. ज्यात Employee No., Name, Designation, Basic Pay, D.A. %, H.R.A. %, T. A. Amount आणि Medical Allownace Amount देण्यात आले आहेत. या गोष्टी वर्षानंतरच बदलतात. पण त्या प्रत्येकाच्या वेगवेगळ्या असू शकतात. त्यामुळे त्या एका टेबलमध्ये साठवल्या आहेत. आणि त्या टेबलाला नाव दिले आहे EmployeeDetails. हे टेबल तुम्ही त्याच Sheet मध्ये किंवा वेगळ्या Sheet मध्येही तयार करू शकता.

Picture 1.2

दुसऱ्या चित्रात हे फंक्शन कुठे आणि कसे वापरायचे हे दाखवले आहे. यातही एक टेबल बनवले आहे. टेबल मधील प्रत्येक रकान्याला ( column ) नाव देण्यात आले आहे. पहिला आहे. Emp. ID ( EmployeeDetail या टेबलमध्ये याचे नाव Employee No. असे होते. ) ही माहिती आपण द्यायची आहे. आणि हाच तो कॉलम आहे ज्याचा वापर EmployeeDetails या टेबल मधील माहिती मिळविण्यासाठी करण्यात येणार आहे. त्यापुढील कॉलमचे नाव आहे Employee Name. आपण या आधीच आपल्याकडील employees ची नावे EmployeeDetails या टेबल मध्ये भरून ठेवलेली आहेत. आता आपल्याला फक्त ती तिथून मिळवायची आहेत. आणि म्हणून सगळ्यात पहिले VLookup Function आपण वापरणार आहोत. त्यासाठी सर्वात प्रथम आपल्याला आपला कर्सर B2 या cell मध्ये न्यायचा आहे. त्यानंतर = चे चिन्ह देऊन त्यापुढे VLookup म्हणायचे आहे. त्यानंतरच्या कंसात आपल्याला A2 हा cell Address द्यायचा आहे. ( काही जण $A$2 असेही देतात. याचा अर्थ ते कॉलम आणि रो यांना फिक्स करतात. म्हणजेच जर कुणी हा address कॉपी केल्यानंतर पेस्ट करताना बदलत नाही. ) यानंतर कॉमा देऊन Table Array द्यायचा आहे. म्हणजे जिथे आपण employeeची माहिती साठवली आहे तो पत्ता. त्याला आपण EmployeeDetails हे नाव दिलेले असल्यामुळे तेच नाव आपण वापरणार आहोत. आणि त्यानंतरच्या आपण Column Index No. देणार आहोत. हा कॉलम नंबर EmployeeDetails या टेबल मधील आहे. यानुसार B2 या cell मध्ये पुढील फंक्शन असेल.

=VLOOKUP(A2,EmployeeDetails,2)

या फंक्शन मध्ये Column Index No. हा 2 देण्यात आला आहे. EmployeeDetails या टेबलमधील कॉलम नंबर 2 मध्ये Employee चे नाव देण्यात आले आहे. म्हणजेच आपण ज्यावेळेस A2 या cell मध्ये Employee No. देऊ त्यावेळेस vlookup function आपल्याला त्या employee नाव देईल.

त्यापुढील रकान्यात हजर दिवस देण्यात आले आहेत. आणि त्यापुढील रकान्यात vlookupचाच वापर केला आहे.

=C2*VLOOKUP(A2,EmployeeDetails,4)/30

वरील फंक्शनचा विचार केल्यास C2 या सेल मध्ये हजर दिवस घेण्यात आले आहेत. गुणिले त्या employeeचा Basic Pay भागिले ३०. Employee चा बेसिक पे vlookupचा वापर करून आपण मिळवतो आहोत. ( यात संपूर्ण फंक्शन सारखेच आहे. फरक आहे तो फक्त कॉलम इंडेक्स नंबरचा. ) आणि भागिले ३० केले आहेत म्हणजे आपल्याला एका दिवसाचा पगार मिळू शकेल. अशा पद्धतीने बेसिक पे आपल्याला मिळतो. अगदी त्याच प्रमाणे इतर गोष्टीत देखील VLookup वापरण्यात आले आहे.

अजून एक गोष्ट... आपण =VLOOKUP(A2,EmployeeDetails,2) असे फंक्शन वापरले आहे. पण अनेक जण Name Define केलेले नसल्यामुळे त्याची रेंज देतात. त्यावेळेस ते फंक्शन पुढील प्रमाणे असेल. =VLOOKUP(A2,Sheet2!$A$2:$H$7,2). याचा विचार केला तर पहिले फंक्शन हे सुटसुटीत वाटते तर दुसरे काहीसे किचकट...

थोडक्यात काय तर मागील लेखात सांगितल्या प्रमाणे Define Name हे खूप फायद्याचे असते आणि VLookup मध्ये तर त्याचा फायदा जास्तच होतो.