अॅडव्हान्स एक्सेल : भाग आठवा ( 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 मध्ये तर त्याचा फायदा जास्तच होतो.

अॅडव्हान्स एक्सेल : भाग सातवा ( Define Name )

मध्यंतरी माझ्या एका मित्राला विचारले... काय रे, तुझ्या व्यवसायासाठी एक्सेल इतके उपयोगी असतानाही तू त्यापासून लांब का पळतोस? आणि त्यावर त्याचे उत्तर होते... ‘यार... खरं सांगू का... त्यातील ते फॉर्म्युले डोक्याचा भुगा करतात. ते Columnचे नाव लक्षात ठेवा, Rowचा नंबर लक्षात ठेवा. आणि परत ते कॉपी पेस्ट करायचे तर मध्येच ते नावं आणि नंबर बदलून जातात. बेरीज वजाबाकी पर्यंत ठीक आहे. पण ते लांबलचक फॉर्म्युले डोकं पिकवतात.’

एका दृष्टीने त्याचे नक्कीच बरोबर आहे. मला त्याची माहिती आहे म्हणून मला ते अवघड वाटत नाही. पण ज्यांना त्याची सवय नाही त्यांना ते किचकट वाटतच असणार. पण म्हणून काय पारंपारिक पद्धत वापरत बसायचे? बिलकुल नाही. क्लिष्ट फॉर्म्युले आपण सोपे करून घ्यायचे. कसे? जास्त काही नाही त्या रेंजला किंवा सेलला योग्य असे नाव देऊन.

आपण उदाहरणच पाहू... समजा तुम्ही एक्सेलच्या एका Sheet वर माहिती साठवीत आहात, आणि दुसर्या Sheet वर त्याचे विश्लेषण करीत आहात. Consilidate च्या भागात आपण हे पाहिले होते. त्यावेळेस आपण रेंज कशी दिली होती? ‘Consolidate2!$B$2:$E$24’ – अशीच ना? यात Consolidate2 हे Sheet चे नाव होते, $B$2:$E$24 ही रेंज होती. आणि “!” ( Exclamation mark ) या चिन्हाने आपण त्यातील वेगळेपण दाखवले होते. बरे प्रत्येक Column Name आणि Row Number च्या आधी $ ( dollar sign ) लावण्यात आले आहे. ते पाहिले की टेंशन येते. ( हे मी माझ्या अनुभवावरून सांगतो आहे. ) समजा त्या ऐवजी आपण त्या रेंजला नाव दिले तर आपले काम अगदी सोपे होईल. समजा आपण त्या रेंजला “Expenses” हे नाव दिल्यानंतर ज्या ठिकाणी ‘Consolidate2!$B$2:$E$24’ अशी रेंज दिली आहे तिथे ‘Expenses’ लिहायचे आहे. आणि आपल्याला तोच रिझल्ट मिळेल जो पहिल्या वेळी मिळाला होता. आहे की नाही सोपे? आणि आपण याला कॉपी पेस्ट करताना Column Name तसेच Row Number बदलण्याचीही काळजी करण्याचे कारण राहणार नाही. 

Picture 1.1

Name Define करणे खूपच सोपे आहे. ज्या सेल किंवा रेंजला आपल्याला नाव द्यायचे आहे. ते सिलेक्ट करायचे. त्यानंतर नेम बॉक्स ( Address Box ) मध्ये त्याचा Address आपल्याला दिसू लागले. त्यावर क्लिक करून आपल्याला हवे ते नाव आपण देऊ शकतो. आहे की नाही सोप्पे.

सोबतच्या चित्रात आपल्याला नाव कुठे द्यायचे आहे ते दिसू शकेल.

दुसऱ्या चित्रात Name Define करण्याची दुसरी पद्धतही दिलेली आहे. यात आपण त्याची व्याप्ती ( Scope ) देखील देऊ शकतो. 

Picture 1.2

एक्सेलमधील Formulas Tab वर क्लिक केल्यानंतर आपल्याला Define Names नावाचा एक विभाग दिसू लागेल. त्यात डाव्या बाजूला Name Manager चा icon असेल. आणि उजव्या बाजूला Define Name चा पर्याय दिसेल. त्यावर क्लिक केल्यानंतर दुसऱ्या चित्रात दाखविलेला Box दिसेल. यात तुम्ही Name च्या जागी नाव द्यायचे आहे. त्याखाली Scope असेल. जिथे workbook असे लिहिलेले असेल. याचा अर्थ संपूर्ण वर्कबुक मध्ये तुम्ही ती रेंज किंवा तो सेल त्याच नावाने वापरू शकाल. ( संपूर्ण वर्कबुक म्हणजे ती एक्सेल फाईल. त्यामध्ये जितके sheet असतील त्या सगळ्या sheet मध्ये तुम्ही ही रेंज वापरू शकता. ) तुम्ही हा scope एखाद्या sheet पुरताही मर्यादित करू शकता. त्या sheet चे नाव select करून. Comment मध्ये तुम्ही या बद्दलची अतिरिक्त माहिती देऊ शकता. याचा उपयोग तुम्हाला Name Manager च्या window मध्ये होऊ शकेल. जर एकापेक्षा जास्त नावे तुम्ही तयार केलेली असतील तर Name Manager च्या विंडोत तुम्हाला नाव, त्याचा Address ( रेंज ), त्याची व्याप्ती ( scope ) आणि Comment दिसेल. ज्यामुळे समजा एखादे नाव व्यवस्थित दिले गेले नसेल तर कमेंटमध्ये तुम्हाला त्याबद्दलची अतिरिक्त माहिती मिळू शकेल. आणि सगळ्यात शेवटी Refers to : या भागात तुम्ही निवडलेल्या सेलचा Address किंवा selected range Sheet च्या नावासहित तुम्हाला दिसू शकेल. त्या box मध्ये तुमचा कर्सर ठेवून तुम्ही दुसरी एखादी रेंज देखील select करू शकता.

आता रेंजला किंवा सेलला नाव देताना काही गोष्टी लक्षात ठेवायच्या आहेत. 

१. नाव जस्तीत जास्त २५६ अक्षरांपर्यंत मोठे असावे.

२. नाव देताना त्यात space असू नये. म्हणजे ‘Monthly Sales’ असे नाव आपल्याला देता येणार नाही. कारण दोन शब्दात मोकळी जागा आली आहे. या ऐवजी आपण ‘MonthlySales’ किंवा ‘Monthly_Sales’ असे देऊ शकतो.

३. नाव हे कोणत्याही सेलचा address असू नये. म्हणजे आपल्याला ‘X365’ किंवा ‘XL16’ असे नाव देता येणार नाही. कारण हा cell address आहे.

४. नावाची सुरुवात अक्षरानेच करावी. ( A – Z )

५. सगळ्यात महत्वाचे. इथे दिलेली नावे Case sensitive नसतात. म्हणजेच इथे “Sales”, “sales”, “SALES” हे एकच समजले जाते. याचा वापर यापुढे खूप होणार आहे. खासकरून VLookup मध्ये जास्तच... त्यामुळे याची सवय करून घेणे कधीही चांगलेच.

अॅडव्हान्स एक्सेल : भाग सहावा ( Nested IF )

मागील भागात आपण IF Function आणि AND Function यांचा वापर करून एखादा विद्यार्थी त्याला मिळालेल्या मार्कांवरून पास की नापास हे कसे ठरवायचे ते पाहिले. पण त्याच वेळेस मी Nested IF याबद्दलही दोन शब्द सांगितले होते. आणि असेही म्हटले होते की Nested IF वापरताना तुमची कन्सेप्ट क्लिअर असली पाहिजे. तेच आज आपण इथे पाहणार आहोत. नाही नाही... घाबरू नका... इतकेही अवघड नाहीये हे. फक्त एका IF Function मध्ये परत IF Function येत असल्यामुळे ते काहीसे क्लिष्ट वाटू शकते. अर्थात वाटणे आणि असणे यात खूप फरक असतो. हे फक्त क्लिष्ट वाटते... असत नाही. चला तर मग...

Nested IF वापरण्यासाठी आपण मागील उदाहरणाचाच पुढील भाग पाहू. मागील उदाहरणात आपण काही विद्यार्थ्यांच्या तीन विषयांचे मार्क घेऊन त्यानुसार त्यांचा रिझल्ट काढला होता. म्हणजे जर ते सगळ्या विषयात पास असतील तर “पास” आणि तीन पैकी कोणत्याही एका विषयात नापास असतील तर “नापास” ठरवत होतो. पण यात दोनच गोष्टी येतात... एकतर पास किंवा नापास. पण प्रत्यक्ष निकालपत्रकात मात्र रिझल्ट बरोबरच त्यांच्या मार्कांची टक्केवारी आणि त्यावर आधारलेली ग्रेड देखील दिलेली असते. मार्कांची टक्केवारी ३५% ते ४४% असल्यास ‘Pass Class’, ४५% ते ५९% असल्यास ‘Second Class’, ६०% ते ७४% असल्यास ‘First Class’ आणि त्यापेक्षाही जास्त असल्यास ‘Dinstintion’. यासाठी आज आपण IF Functionचाच वापर करणार आहोत. पण एका पेक्षा जास्त वेळेस... आणि तेही एकात एक असलेले.

अनेकांनी महाभारत सीरिअल पाहिले असेल. त्यात द्रोणाचार्य अभिमन्यूला चक्रव्यूह रचून पराभूत करतात. इथेही हे एक प्रकारचे चक्रव्यूहच आहे. पण याची रचना माहित झाल्यावर विद्यार्थ्यांचा अभिमन्यू नाही तर अर्जुन बनतो. चक्रव्युहात जसे मध्यभागी मुख्य योद्धा असतो आणि त्याच्या भोवताली वेगवेगळे कडे बनवले जाते. आधी पहिले कडे फोडले की दुसरे, दुसरे फोडले की तिसरे... याप्रमाणे. आणि समजा पहिले कडे तुम्हाला फोडताच आले नाही तर तुम्हाला अगदी मध्यभागी जाण्याची वेळच येत नाही. तसेच हे Nested IF Function असते.

Picture 1.1

सोबतच्या चित्रात एक टेबल दाखवले आहे. ज्यात काही विद्यार्थ्यांची नावे, त्यांना तीन विषयात मिळालेले मार्क, त्या मार्कांची बेरीज, त्यांचा रिझल्ट, त्यांची टक्केवारी आणि त्यांची श्रेणी ( ग्रेड हो ) देण्यात आली आहे. यापैकी नाव, आणि त्यांच्या विषयांचे मार्क आपणच भरणार आहोत. एकूण मार्कांसाठी SUM हा फोर्मुला वापरला आहे. रिझल्ट साठी मागील भागात पाहिलेले IF AND Function वापरले आहे. त्यांच्या मार्कांची टक्केवारी काढण्यासाठी एकूण मिळविलेले मार्क भागिले एकूण असलेले मार्क गुणिले १०० हा फोर्मुला वापरण्यात आला आहे.

=(F4/300)*100 – [ 131/300*100 = 43.67 ]

इथपर्यंत काहीच अवघड नाही. त्यापुढे ग्रेडच्या रकान्यातील सेलमध्ये आपण पुढील फोर्मुला वापरणार आहोत.

=IF(G4="Fail","Fail",IF(H4<45,"Pass Class",IF(H4<60,"Second Class",IF(H4<75,"First Class","Distinction"))))

काहीसा डेंजर दिसतोय ना फोर्मुला? पण तो फक्त लांबच लांब असल्यामुळे तसा वाटतोय. आपण त्याचे विभाजन केल्यास तो तितका अवघड वाटणार नाही. मी तर त्यासाठी कॉपी पेस्टचाच जास्त वापर करतो. बाकी काही नाही हो... टाईप करण्याचे कष्ट वाचतात.

सुरुवात करू पहिल्या IF पासून. यातील logical test या भागात G4=”Fail” ही condition दिली आहे. म्हणजे G4 या सेलमधील रिझल्ट “Fail” असा असेल तर त्याच्या True Value या भागात “Fail” जसेच्या तसे लिहिले आहे. कारण जर मुलगा नापास असेल तर त्याची ग्रेड काढली जात नाही. पण समजा G4 या सेल मधील रिझल्ट “Pass” असा असेल तर IF Functionच्या False Value भागातील माहिती दाखविण्यात येईल. पण वरील फंक्शन मध्ये आपण त्या भागात कोणताही शब्द किंवा शब्दसमूह न देता तिथे अजून एक IF Function दिले आहे. ( IF Function No. 2 - चित्रात त्याची फोड केलेली तुम्हाला दिसेल. )

दुसऱ्या नंबरचे जे IF Function आहे त्यातील logical test मध्ये H4<45 ही कंडीशन दिली आहे. H4 का? कारण H4 या सेलमध्येच त्या मुलाची टक्केवारी काढण्यात आली आहे म्हणून. एक गोष्ट इथे लक्षात घ्या. पहिल्या IF मध्ये कंडीशन देताना आपण value ला अवतरण चिन्ह ( “ “ - डबल कोटेशन मार्क ) वापरले होते. दुसऱ्या IF मध्ये मात्र कंडीशन देताना value अवतरण चिन्हात दिलेली नाही. का? कारण पहिल्या value चा DataType हा Text ( शब्द ) आहे तर दुसऱ्या value चा DataType हा Numeric ( संख्या ) आहे. जर दुसऱ्या value ला देखील आपण अवतरण चिन्हात दाखवले असते तर एक्सेलने त्याला संख्या न मानता शब्द मानले असते आणि मग त्यामुळे त्याच्या सोबत कोणत्याही प्रकारचे Mathematical Calculation करणे शक्य होऊ शकले नसते. हे इथे सांगण्याचे कारण म्हणजे ही गोष्ट दिसताना अगदी लहान दिसली तरीही त्यामुळे मिळणारा रिझल्ट चुकीचा असू शकतो. असो... ज्या वेळेस एक्सेल H4<45 ही कंडीशन चेक करेल त्यावेळेस त्याला जर H4 cell मधील value 45 पेक्षा लहान दिसली तर “Pass Class” हे शब्द आपल्याला दिसू लागतील. ( इथे मी फक्त ‘<’ हेच Operator वापरले आहे. कारण या कंडीशन मध्ये 45 ही संख्या घेतली आहे. जर ती संख्या 44 असती तर ‘<’ operator बरोबरच ‘=’ हे operator देखील वापरावे लागले असते. [ H4<=44 ] ) जर H4 या सेल मधील संख्या 45 किवा त्यापेक्षा मोठी असेल तर दुसऱ्या IF Function च्या false value या सेक्शन मधील माहिती आपल्याला मिळेल. हीच गोष्ट बाकी दोन IF Function मध्येही लागू होते. आहे की नाही सोप्पं? आता अजून एक गोष्ट... या आधीच्या भागात मी असेही म्हटले होते की यासाठी सगळ्यात महत्वाचे म्हणजे कन्सेप्ट क्लिअर असावी लागते म्हणून. बरोबर ना? त्याबद्दल थोडेसे. इथे पहिल्या फंक्शन मध्ये रिझल्ट चेक केला आहे, दुसऱ्या मध्ये Pass Class, तिसऱ्या मध्ये Second Class आणि चौथ्या मध्ये First Class तसेच Distinction असे चढत्या क्रमाने logical test देण्यात आली आहे. समजा ती जर उतरत्या क्रमाने दिली असती तर? Function ने योग्य रिझल्ट दिला असता? पहा बरे करून... जमतंय का?

अॅडव्हान्स एक्सेल : भाग पाचवा ( Conditional Functions )

आजच्या भागात आपण एक्सेलमधील एक खूप सोपे पण खूप उपयोगी असे If Function पाहणार आहोत. धक्का बसला ना? कारण आपण एक्सेल शिकतो त्यावेळेस जवळपास प्रत्येकालाच हे फंक्शन कसे वापरायचे हे शिकवले जाते. आता मग मी त्यात आणखी काय नवीन सांगणार? आणि तेही अॅडव्हान्स एक्सेल या विभागात? पण नक्कीच काही जणांना आजचा भाग उपयुक्त ठरू शकेल.

आपण एरवी if function वापरतो ते फक्त एखादा प्रश्न कंडीशनल असेल तर. जसे एखाद्या विद्यार्थ्याला एखाद्या विषयात ३५ मार्क असतील तर तो पास आणि नसतील तर नापास... इतके सोपे. पण ज्यावेळेस आपण प्रत्यक्ष व्यवहारात याचा वापर करतो त्यावेळेस हे इतकेच सोपे असेल का? नक्कीच नाही. कारण विद्यार्थी पास की नापास हे फक्त एका विषयावर ठरत नाही. एकूण सहा सात विषय असतात. त्यातील सगळ्या विषयात तो पास झाला तर त्याला पास समजले जाते पण समजा त्यातील कोणत्याही एका जरी विषयात नापास झाला तरी तो नापास ठरतो. म्हणजेच इथे कंडीशन आहेत, पण एकापेक्षा जास्त. अशा वेळेस आपल्याला नुसते if function वापरून भागणार नाही. आणि इथेच सुरुवात होते अॅडव्हान्स एक्सेलची. या केस मध्ये रिझल्ट आपल्याला दोन वेगवेगळ्या प्रकारे काढता येवू शकतो. त्यातील पहिला प्रकार म्हणजे nested if ( नेस्टेड इफ ) आणि दुसरा प्रकार म्हणजे if च्या जोडीला वापरले जाणारे AND Function. सोप्या भाषेत सांगायचे तर एकाच फोर्मुल्यात एकापेक्षा जास्त इफ फंक्शन वापरणे म्हणजे नेस्टेड इफ. ज्यांना कोडींगची माहिती असते ते जास्तकरून याचा वापर करतात. कारण हे वापरताना आपली कन्सेप्ट क्लीअर असणे खूप गरजेचे असते. नाहीतर यात चुका होण्याचा जास्त संभव असतो. आणि त्यामुळेच आपण यातील दुसरा प्रकार आधी पाहणार आहोत.

आधी इफ फंक्शनचा syntax पाहू.

= if ( Condition, True Value, False Value )

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

आता इफच्या बरोबरीने जे दुसरे फंक्शन पाहणार आहोत त्याबद्दल. AND Function syntax.

= AND(Logical1, Logical2,...)

या ठिकाणी बरोबरच्या चिन्हानंतर आपल्याला AND लिहिलेले दिसते. त्यानंतरच्या कंसात Logical1, Logical2, ... या गोष्टी दिसतात. यात लॉजिकल याचा अर्थ होतो कंडीशन. या फंक्शन मध्ये आपण कमीत कमी दोन आणि जास्तीत जास्त २५६ कंडीशन देऊ शकतो. जर यातील सगळ्या कंडीशन बरोबर असतील तर हे फंक्शन आपल्याला True रिझल्ट देते आणि जर यातील एक जरी कंडीशन चूक असेल तर हे फंक्शन आपल्याला False रिझल्ट देते.

या दोन्ही फंक्शनचा आपल्याला कसा वापर करता येतो हे आपल्याला सोबतच्या चित्रावरून चांगले समजू शकते. 

Table 1.1

टेबल १.१ मध्ये चार मुलांचे रिझल्ट दाखवण्यात आले आहेत. प्रत्येकाला तीन विषय दिले गेले आहेत. रिझल्ट लावताना मात्र जर तो विद्यार्थी तिन्ही विषयात पास असेल तरच पास समजला जाईल अन्यथा नापास समजला जाईल. पहिला विद्यार्थी तिन्ही विषयात पास आहे त्यामुळे त्याचा रिझल्ट पास आलेला आहे पण इतर बाकी तीन विद्यार्थी मात्र एकेका विषयात नापास असल्यामुळे नापास दाखवले गेले आहेत. आणि यासाठी खालील प्रमाणे फोर्मुला वापरण्यात आला आहे.

=IF(AND(C27>=35,D27>=35,E27>=35),"PASS","FAIL")

वरील फोर्मुलामध्ये IFच्या कंडीशन पार्ट मध्ये AND फंक्शनचा वापर केलेला दिसतो.

[ AND(C27>=35,D27>=35,E27>=35) ].
इथे AND फंक्शन मध्ये तीन कंडीशन दिल्या गेल्या आहेत. पहिली कंडीशन ‘C27>=35’ ही आहे. याचा अर्थ C27 या सेलमधील नंबर जर ३५ किंवा त्यापेक्षा मोठा असेल तर True असा रिझल्ट मिळावा आणि नसेल तर False असा रिझल्ट मिळावा. असेच पुढच्या दोन कंडीशनच्या बाबतीत सुद्धा आहे. आता AND या फंक्शन नुसार जर तिन्ही रिझल्ट True असतील तरच ते फंक्शन आपल्याला True असा रिझल्ट देईल. अन्यथा आपल्याला False असा रिझल्ट मिळेल. तसेच आपण दिलेल्या इफ फंक्शन मध्ये जर कंडीशन True असेल तर आपल्याला ते फंक्शन पास असा रिझल्ट देईल आणि नाहीतर फेल असा रिझल्ट मिळेल. म्हणजेच जर C27, D27 आणि E27 या तिन्ही सेल मधील नंबर ३५ पेक्षा मोठे असतील तरच आपल्याला “पास” असा रिझल्ट मिळेल आणि त्यापैकी कोणत्याही एका सेल मधील नंबर ३५ पेक्षा लहान असेल तर आपल्याला “फेल” असा रिझल्ट मिळेल. IF हे फंक्शन एक्सेल मध्ये सगळ्यात जास्त उपयोगी आणि वापरले जाणारे फंक्शन आहे त्यामुळे जर तुम्ही त्याचा प्रभावीपणे वापर करू शकलात तर तुमचे अनेक रिपोर्ट तुम्ही अगदी काही मिनिटात बनवू शकतात. त्यामुळे उफ न करता इफ शिकलात तर नक्कीच ते तुम्हाला खूप फायद्याचे ठरेल.

अॅडव्हान्स एक्सेल : भाग चौथा ( Paste Special )

बेसिक एक्सेल ज्यांना येते त्यांना एक गोष्ट १००% माहित असते. CTRL+C हे key combination एक किंवा अनेक cell मधील मजकूर कॉपी करण्यासाठी वापरले जाते, आणि पेस्ट करताना CTRL+V हे key combination वापरले जाते. कित्ती सोपे ना? आता काही जण म्हणतील... ज्या गोष्टी माहित आहेत त्या परत का सांगत बसलाय हा माणूस? आहे... कारण आहेच. आपण इथे फक्त बेसिक एक्सेल शिकत नाही आहोत, आपल्याला शिकायचे आहे त्यापेक्षा थोडे जास्त. त्यामुळे आज आपण पाहणार आहोत Pest Special.

ही गोष्ट खरे तर जे लोक Data Analyst आहेत त्यांच्यासाठी जास्त गरजेची आहे. किंवा मग ज्यांच्याकडे रिपोर्ट्स बनविण्याचे काम असते त्यांनाही या गोष्टीची गरज पडतेच. पण त्यांच्या बरोबरच आपल्यालाही या गोष्टी माहिती झाल्या तर त्यात काय बिघडले? उलट त्याचा आपल्याला इतरांवर प्रभाव टाकण्यासाठी फायदाच होऊ शकेल. बरोबर ना? चला तर मग... इतरांवर प्रभाव टाकण्याची सुरुवात करू. 

ज्या वेळेस आपण कोणत्याही सेलला कॉपी करतो त्यावेळेस त्या सेलशी संबंधित सगळ्या गोष्टी कॉपी होत असतात.

Picture 01

उदा. सोबतच्या चित्रात B2 आणि C2 या सेलमध्ये अनुक्रमे 45 आणि 50 अशा संख्या आल्या आहेत. त्यानंतरच्या सेल मध्ये ( D2 ) मध्ये B2 आणि C2 ची बेरीज करणारा फोर्मुला देण्यात आला आहे. ( =B2+C2 ) त्यामुळे D2 मध्ये आपणास दोन 45 आणि 50 या संख्यांची बेरीज 95 ही संख्या दिसते आहे. ती संख्या लाल रंगात दिसत असून त्या सेलच्या सभोवती बोर्डरही देण्यात आली आहे. म्हणजेच ज्यावेळेस आपण D2 या सेलला कॉपी करू त्यावेळेस त्या सेल मधील फोर्मुला, संख्या, त्याचे Formatting अशा सगळ्याच गोष्टी कॉपी होतील. आणि जेंव्हा D3 या सेलवर जाऊन आपण त्याला पेस्ट करू त्यावेळेस तिथे आपणास लाल रंगात (0) ही संख्या दिसेल. का? कारण पेस्ट होताना इथे formatting बरोबरच त्यातील फोर्मुला पेस्ट झाला. पण त्यात थोडा बदल करून. 

Picture 02

दुसऱ्या चित्रात आपला कर्सर D3 या सेलवर आहे. आणि फोर्मुला बार मध्ये त्याचा फोर्मुला =B3+C3 असा दिसतो आहे. म्हणजेच फोर्मुलामधील कॉलमचे नाव तसेच असले तरी ओळीचा नंबर मात्र बदलला आहे. आणि B3 तसेच C3 या सेलमध्ये कोणतीची संख्या नसल्याने D3 सेलमध्ये आपणास झीरो (0) दिसत आहे.

पण ज्यावेळेस आपण एखादा रिपोर्ट बनवत असतो त्यावेळेस यातील फक्त काही गोष्टीच आपल्याला पेस्ट करायच्या असतात. उदा. एखाद्या वेळेस cell मध्ये फोर्मुला (=B2+C2) असला तरी आपल्याला फक्त त्यातील value च (95) गरजेची असते. ही गोष्ट शक्य होऊ शकते पेस्ट स्पेशल सुविधेने. 

Picture 3

Picture 3 चित्रात Right Click केल्यानंतर येणारी विंडो दिसते आहे. यात Paste Options मध्ये सहा आयकॉन दिसत आहेत. त्यातील पहिला आयकॉन आहे paste, दुसरा आहे value, तिसरा आहे formula, चौथा आहे Transpose, पाचवा Formatting आणि सहावा link. सामान्यतः या सहा प्रकारात आपल्या गरजा पूर्ण होऊ शकतात. पण समजा आपली गरज याहून जास्तीची असेल तर त्या आयकॉनच्या खाली Paste Special नावाचा जास्तीचा पर्याय देण्यात आला आहे. त्यावर आपला कर्सर गेल्यानंतर त्याच्या बाजूला असलेली विंडो दिसू लागते. यात अजून काही जास्तीच्या सुविधा देण्यात आल्या आहेत.

आता एकेक करून आधी पहिल्या सहा आयकॉन बद्दल पाहू.

१. Paste (P) : यावर क्लिक करून आपण कॉपी केलेल्या सगळ्याच गोष्टी सामान्यतः पेस्ट करत असतो.

२. Value (V) : यावर क्लिक केले तर आपण कॉपी केलेल्या सेल मधील फक्त value ( Result ) तेवढीच पेस्ट करतो.

३. Formula (F) : यावर क्लिक केल्याने कॉपी केलेल्या सेल मधील फोर्मुला फक्त पेस्ट होईल. त्या सेलचे formatting पेस्ट होणार नाही. 

Picture 4

४. Transpose (T) : यावर क्लिक केल्याने ओळीत विभागलेला मजकूर कॉलममध्ये पेस्ट केला जातो. चौथ्या चित्रात दाखविल्या प्रमाणे आधी चार महिन्यांची नावे A कॉलम मध्ये १ ते ४ ओळीत भरली गेली होती. त्यानंतर ती रेंज कॉपी केली. आणि C1 या सेलवर Right Click केल्यानंतर आपल्या समोर जे पेस्टचे पर्याय आले त्यातील चौथा पर्याय निवडल्यानंतर ती चारही महिन्यांची नावे अनुक्रमे C1, D1, E1 आणि F1 या सेलमध्ये पेस्ट केली गेली. 

५. Formatting (R) : यावर क्लिक केल्याने त्या सेलचा फक्त format पेस्ट होतो. त्यातील मजकूर मात्र पेस्ट होत नाही.

६. Paste Link (N) : यावर क्लिक केल्याने त्या सेलचा Address पेस्ट केला जातो.

Paste Special वर कर्सर नेल्यानंतर उजव्या बाजूला जे पर्याय दिसू लागतात त्यातील बरेच पर्याय आपणास वर दिसले आहेत. पण त्यातील दोन पर्याय असे आहेत जे एक्सेल २००७ व्हर्जन मध्ये आपल्याला आढळून येणार नाहीत.

७. Picture (U) : यावर क्लिक केल्यानंतर कॉपी केलेली माहिती picture format मध्ये paste होते.

८. Linked Picture (I) : यावर क्लिक केल्यानंतर कॉपी केलेली माहिती picture format मध्ये paste होते पण हे Live Picture असते. म्हणजे समजा आपण कॉपी केलेल्या सेल मध्ये काही बदल झाला तर पेस्ट केलेल्या picture मध्येही आपोआप बदल होतो.

आहे की नाही मजेदार गोष्ट? याप्रमाणे CTRL+ALT+V हे key combination वापरले तर तुमच्या समोर Paste Special Dialog Box दिसू लागेल. यातही अनेक वेगवेगळ्या सुविधा दिल्या गेल्या आहेत. त्यातील बाकी सुविधा आपल्याला सहसा उपयोगी पडतातच असे नाही. अर्थात नवीन गोष्टी शिकण्याच्या दृष्टीने त्या करून पाहिल्या तर चांगलेच आहे.

अॅडव्हान्स एक्सेल : भाग तिसरा ( Consolidate 2 )

 

आज आपल्याला consolidate याच सुविधेतील अजून काही गोष्टी पहायच्या आहेत. मागील भागात याचा वापर दैनंदिन जमाखर्च ठेवताना कसा होऊ शकतो हे पाहिले, या भागात मार्केटिंगच्या दृष्टीकोनातून काढण्यात येणारे रिपोर्ट कसे बनवावेत हे पाहणार आहोत. सोबतच्या चित्रातून आपल्याला या गोष्टी समजून घेता येतील. टेबल १.१ मध्ये तारखेनुसार कोणत्या सेल्समनने कोणत्या प्रोडक्टची किती विक्री केली याची माहिती नोंदवण्यात आली आहे. पण ही माहिती एकत्रित नाहीये. तारखेनुसार रेकॉर्ड असल्यामुळे सगळी माहिती विखुरलेली आहे. या टेबल मध्ये खूप कमी ओळी असल्यामुळे त्याचे एकत्रिकरण करणे त्या मानाने खूप सोपे आहे पण समजा जर याच ओळी शेकड्यांमध्ये असतील तर? मग मात्र त्याचे एकत्रिकरण त्रासदायक ठरू शकते. कारण आधी फक्त एकच गोष्ट पाहिली जात होती. ती म्हणजे खर्चाचा तपशील. पण इथे सेल्समन सुद्धा अनेक आहेत आणि त्यांनी विकलेले प्रोडक्ट्स देखील अनेक आहेत. बरे या सगळ्या गोष्टी विखुरलेल्या आहेत. अशा वेळेस एक्सेल मधील consolidate ही सुविधा खूप उपयोगी ठरते. सोबतच्या चित्रातही जी consolidate window आहे त्यात सगळी माहिती आधीप्रमाणेच आहे. अर्थात पहिल्या वेळेस रेंज घेताना फक्त दोन कॉलम घेतले गेले होते पण कॉलमचे नाव मात्र वगळण्यात आले होते. इथे मात्र रेंज घेताना एकूण चार कॉलम घेण्यात आलेले आहेत आणि ते सुद्धा त्या कॉलमच्या नावासह. ( 'Consolidate 2'!$B$2:$E$24 ) तसेच यात अजून एक गोष्ट जी वेगळी आहे ती म्हणजे Use labels in मधील Top Row हा चेकबॉक्सही चेक केला गेला आहे. ज्यावेळेस आपण याप्रमाणे माहिती consolidate च्या window मध्ये भरून ओकेच्या बटन वर क्लिक करू आपल्याला त्याचा रिझल्ट टेबल १.२ या प्रमाणे दाखवण्यात येईल. या टेबल मध्ये ( टेबल १.२ ) आपल्याला आपल्याकडे काम करीत असलेले सेल्समन ओळीनुसार दिसत आहेत तसेच त्यांच्यापुढे आपल्याकडील प्रोडक्ट्स कॉलमनुसार दिसत आहेत. आणि इतर सेल मध्ये आपल्याला कोणत्या सेल्समनने कोणत्या प्रोडक्टची किती विक्री केली हे दाखवले जात आहे. याचा वापर आपल्याला सेल्समनचा Performance Analysis करण्यासाठीही करता येवू शकतो.

आता या अनुषंगाने अजून काही गोष्टी. जर तुम्ही ज्या एक्सेल शीट वरून माहिती घेत आहात तिथेच ती consolidate करत असाल तर त्यात एक प्रॉब्लेम आहे. म्हणजे consolidate window मधील Use labels in भागातील तिसरा चेकबॉक्स ( Create Link To Source Data ) तुम्हाला चेक करता येणार नाही. तो चेक केल्यास एक्सेल तुम्हाला ( Cannot create links to consolidation sheet ) एरर मेसेज देते. याचे कारण म्हणजे तुम्ही ज्या ठिकाणावरून माहिती गोळा करीत आहात त्याच ठिकाणची लिंक एक्सेल घेत नाही. याचा एक तोटा असा की जर ओरिजिनल माहितीमध्ये तुम्ही काही बदल केल्यास त्याचा कोणताही परिणाम रिझल्टच्या टेबलमध्ये तुम्हाला दिसून येणार नाही. असा परिणाम होण्यासाठी तुम्हाला परत एकदा consolidate प्रोसिजर फॉलो करावी लागेल. उदा. टेबल १.२ हे रिझल्ट टेबल बनवताना consolidate सुविधेने टेबल १.१ मधून माहिती घेतलेली आहे. उदा. सेल्समन अभिषेक याने प्रोडक्ट एक याची विक्री एकूण ६ वेळेस केलेली आहे. ( ५०+५०+४५+४८+४५+५६=२९४) समजा आता जर आपण टेबल १.१ च्या अभिषेक समोरील प्रोडक्ट १ मधील माहिती ५० ऐवजी ६० केली तरीही टेबल १.२ मधील अभिषेकने प्रोडक्ट १ ची केलेली एकूण विक्री ही पहिल्या इतकीच म्हणजे २९४ इतकीच दाखवली जाईल जी बदल केल्यानंतर ३०४ इतकी होणे अपेक्षित आहे. आता यात फक्त थोडाबदल केला आणि ओरीजनल माहिती वेगळ्या एक्सेल शीट वरून घेतली आणि रिझल्ट वेगळ्या शीट वर घेतला तर मात्र consolidate window मधील Create Link To Source Data हा चेकबॉक्स आपण चेक करू शकतो. त्याने फायदा असा होतो की जे काही रिझल्ट टेबल असेल त्याच्या प्रत्येक सेलमध्ये एक्सेल फार्मुलाचा वापर करते. अशा वेळेस जर आपण ओरीजनल माहितीत काहीही बदल केला तर त्याचा परिणाम तत्काळ रिझल्ट टेबलमध्ये आपल्याला दिसून येतो. म्हणजेच जर अशा वेळी सेल्समन अभिषेकने केलेली प्रोडक्ट १ ची विक्री आपण ५० ऐवजी ६० केली तर रिझल्ट शीट मध्ये आपल्याला सेल्समन अभिषेकची प्रोडक्ट १ ची एकूण विक्री ही आपल्याला लगेचच ३०४ अशी बदललेली दिसेल.

माहितीचे एकत्रिकरण करताना आपल्याला ही माहिती एकापेक्षा जास्त एल्सेल शीट मधूनही घेता येते. त्यासाठीआपल्याला रेफरन्स बॉक्स मध्ये माहितीची रेंज दिल्यानंतर Add बटनवर क्लिक करावे लागते जेणेकरून ती रेंज All References या लिस्टमध्ये add केलीजाते. समजा जर आपल्याला एक्सेलच्या दुसऱ्या फाईल मधून एखादी रेंज घ्यायची असेल तर त्यासाठी Browse या बटणावर क्लिक करा. आपल्या समोर ब्राउज विंडो उघडेल ज्यात आपल्याला एक दुसरी एखादी एक्सेल फाईल उघडता येईल आणि त्यातून माहिती घेता येवू शकेल. थोडक्यात consolidate ही सुविधा आपल्याला अनेक ठिकाणी उपयोगी पडू शकते.

अॅडव्हान्स एक्सेल : भाग दुसरा ( Consolidate )

पहिल्या भागात आपण सेलच्या कॅटेगरी पहिल्या. आज आपल्याला जी गोष्ट पहायची आहे ती सगळ्यांनाच उपयोगी पडू शकते. अकौंटंट असो, छोटा व्यावसायिक असो वा गृहिणी असो, याचा वापर ते त्यांच्या दैनंदिन जीवनात नक्कीच करू शकतात. बरे ही गोष्ट इतकी सोपी आहे की ज्याला एक्सेल मध्ये फक्त डाटा भरता येतो तो सुद्धा याचा वापर प्रभावीपणे करू शकतो. कोणती ही गोष्ट? ही आहे एक्सेल मधील Consolidate सुविधा. एक्सेल २००७ मध्ये ही सुविधा आपल्याला Data Tab मध्ये सापडते. Data Tools या कॅटेगरीमध्ये Data Validation च्या शेजारी आपल्याला Consolidate चा आयकॉन दिसतो. सर्वात आधी ही काय भानगड आहे हे पाहू.

Consolidate म्हणजे आपल्याकडील विखुरलेली माहिती एका ठिकाणी गोळा करणे इतकेच. बरे या गोष्टी आपण एरवी करत नाही असे नाही. पण ते करताना आपण एक्सेलचा वापर सहसा करत नाही. उदा. काही लोकं आपला रोजचा जमाखर्च एका डायरीत लिहून ठेवत असतात. कोणत्या तारखेला किती पैसे मिळाले आणि त्यातील किती खर्च झाले हे बरेच जण करतात. छोटे व्यावसायिक तर करतातच करतात. रोज फक्त हा हिशोब डायरीत मांडला जातो आणि महिन्याच्या शेवटी कोणता खर्च किती झाला याचे तपशील काढले जाते. बरोबर ना? पण हे तपशील काढताना किती वेळ जातो? जर रोजची उलाढाल जास्त असेल तर महिन्याच्या शेवटी किमान एक दीड तास तर लागतोच लागतो. परत त्यातही होणाऱ्या मानवी चुका. कधी त्या लक्षात येतात तर कधी येतही नाहीत. आणि समजा त्यावर आपण आपले नियोजन करणार असलो तर तेही चुकणारच. हीच गोष्ट जर आपल्याला काही मिनिटात आणि तीही बिनचूक करता आली तर? आहे ना उपयोगी? हेच काम एक्सेल मधील Consolidate ही सुविधा आपल्यासाठी करत असते... अगदी काही मिनिटात.

आता आपण वेगवेगळ्या व्यक्तींना ही सुविधा कशी फायदेशीर ठरू शकते हे पाहणार आहोत. तसेच ती कशी वापरायची हे देखील आकृतीच्या सहाय्याने पाहणार आहोत. सुरुवात करू गृहिणींपासून.

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

खालील फोटोत ही सुविधा कशी वापरावी हे दाखविले गेले आहे. यात आपल्याला एक टेबल दिसते ज्यात तीन कॉलम आहेत. पहिल्यात दिनांक, दुसऱ्यात तपशील आणि तिसऱ्यात रक्कम भरली गेली आहे. या टेबल मध्ये तारखेनुसार झालेल्या खर्चाचा हिशोब लिहिला गेला आहे. पण यातील कित्येक खर्च हे दोन तीन दिवसाआड रिपीट होत आहेत. उदा. पेट्रोल किंवा भाजीपाला यांचा खर्च इ. Consolidate करताना आपल्या कर्सरचा Address आहे F10. या ठिकाणी आपल्याला Petrol Expenses असे शब्द दिसत आहेत. पण हे सगळे आपले काम पूर्ण झाल्यावर येते. ते कसे दिसेल याची माहिती व्हावी म्हणून तिथे रिझल्ट दाखवण्यात आला आहे. Consolidate सुविधा वापरताना आपला कर्सर हा ज्या ठिकाणी आपल्याला रिझल्ट दाखवायचा आहे तिथे सेट करावा लागतो. रिबन वरील Consolidate या icon वर क्लिक केल्यानंतर Consolidate window ओपन होते. यात सर्वात वर Function म्हटले गेले आहे. जिथे सध्या Sum हा शब्द दिसतो. याचा अर्थ माहितीचे एकत्रीकरण केल्यानंतर जो खर्च एकापेक्षा जास्त वेळेस लिस्टमध्ये आला असेल त्याच्या किमतीची बेरीज करण्यात येते. त्याखाली Reference चा बॉक्स दिसेल. इथे मी टेबलमधील फक्त दोन कॉलमचा रेफरन्स देतो आहे. Details आणि Amount हे ते दोन कॉलम. ( 'Consolidate 1'!$C$9:$D$29) शेजारील रेफरन्स मधील 'Consolidate 1' हा शब्द त्या एक्सेल शीटचे नाव आहे ( तुमच्या इथे ते नाव Sheet1 असू शकेल ) आणि त्यापुढील !$C$9:$D$29 ही सिलेक्ट केलेली रेंज आहे. त्यानंतर Add च्या बटणावर क्लिक करून ती रेंज All References या लिस्टमध्ये add करण्यात आली आहे. त्याखाली Use labels in असे लिहिलेले असून त्याखाली दोन चेकबॉक्स देण्यात आले आहेत. त्यातील एक म्हणजे Top row आणि दुसरा Left column असे. त्यातील दुसऱ्या म्हणजे Left column या चेकबॉक्सला चेक करण्यात आले आहे. आणि त्यानंतर ओकेच्या बटणावर क्लिक केल्यानंतर दिलेल्या रेंज मधील माहितीचे एकत्रीकरण केले जाते.

consolidate रिझल्ट शीटमध्ये पेट्रोलचा खर्च ६००/- रुपये दाखवण्यात आला आहे. ( १२०/- (०१ मे) + १२०/- (०४ मे) + १२०/- (०८ मे) + १२०/- (१० मे) + १२०/- (१४ मे) = ६००/- )

पहा... आहे ना सोपे आणि उपयुक्त? या पद्धतीने अकौंटंट किंवा छोटे व्यावसायिक आपल्या व्यवसायाचा डेली हिशोब नक्कीच ठेवू शकतात. यातील अजून काही गोष्टी मात्र आपण पुढील भागात पाहणार आहोत.

अॅडव्हान्स एक्सेल : भाग पहिला ( Cell Formatting )

खरे तर मला खूप दिवसांपासून अशा प्रकारच्या पोस्ट टाकायला सुरुवात करावी असे वाटत होते पण काही ना काही कारणाने राहून जात होते. आजपासून मात्र जितके शक्य होईल तितके या उपक्रमास सुरुवात करत आहे. काही चुकल्यास सांभाळून घ्यावे आणि आपणही आपल्याकडे असलेल्या गोष्टी शेअर कराव्यात जेणेकरून सगळ्यांना त्याचा उपयोग होऊ शकेल.

काही वर्षांपूर्वी तुम्हाला MS Office येते हे Extra Qualification म्हणून गृहीत धरले जात होते पण नंतर काळ बदलत गेला. अनेक नवीन नवीन software येत गेले. संगणकाचे दैनंदिन कामकाजात असलेले योगदान वाढत गेले आणि Extra Qualification हे Minimum Qualification बनले. आता तर तुम्हाला MS Office येतेच असेच गृहीत धरले जाते. पण आपण किती वापरतो ते? त्या मानाने खूपच कमी. एक्सेलचा वापर माहिती साठवण्यासाठी. वर्डचा वापर कागदपत्रे ड्राफ्ट करण्यासाठी आणि आउटलुकचा वापर इमेल पाहण्यासाठी... बस... इतकेच... इतर टूल्सची आपल्याला गरजच पडत नाही. पण खरंच त्याची गरज नसते का? असते... पण आपल्याला त्याचा वापर कसा प्रभावीपणे करता येऊ शकतो हेच माहिती नसते. तेच आता या लेखमालेतून आपल्याला जाणून घ्यायचे आहे. सुरवात करणार आहे ती एक्सेल पासून. कारण फक्त एकच. हेच टूल्स सगळ्यात जास्त सगळीकडे वापरले जाते आणि वापरले जाऊ शकते.

आज आपण सगळ्यात सोपा टॉपिक पाहणार आहोत. खरे तर ज्यांना ज्यांना एक्सेल येते त्यांना या गोष्टी माहिती असणारच आहेत. पण काही वेळेस जी गोष्ट सगळ्यात सोपी वाटते तीच आपल्याला वेळेवर आठवत नाही. एक्सेलमध्ये जशी माहिती साठवता येते तसेच त्याचा वापर हा Quick Reports बनवण्यासाठी सुद्धा केला जातो. या रिपोर्ट मध्ये माहिती जशी आपण भरतो तशीच दिसणे गरजेचे असते आणि यासाठी गरजेची असते ती Cell Format Category.तीच आज आपण पाहणार आहोत.

Cell Format Category

Cell Format Category यालाच काहीजण डाटाबेसच्या भाषेत DataType असेही म्हणतात. सेलमध्ये भरला जाणारा डाटा कोणत्या प्रकारचा असेल ते Cell Format Category ठरवते. एक्सेल २००७ मध्ये आपल्याला एकूण १२ कॅटेगरी दिसतात. ज्यातील फक्त चार पाचच आपण दैनंदिन जीवनात जास्त करून वापरतो. ज्यावेळेस आपण नवीन एक्सेल शीट उघडतो त्यावेळेस त्यातील प्रत्येक सेलची कॅटेगरी ही General असते. म्हणजेच आपण जो डाटा त्यात भरू त्याला एक्सेल स्वतःच्या Format नुसार साठवते. फोटोमधील उदाहरणावरून हे जास्त स्पष्ट होऊ शकेल. ( टेबल १.१ ) पहा... मी डाटा भरताना पहिल्या ओळीतील सगळ्या सेल मध्ये एकच माहिती भरतो आहे... १११००० हा नंबर. पण सेलच्या कॅटेगरी नुसार तो मला वेगवेगळा दिसतो. नुसता दिसतच नाही तर तो वेगवेगळा असतो सुद्धा. General कॅटेगरी असताना तो फक्त एक नंबर म्हणून घेतला गेला. तेच Number कॅटेगरी असताना त्याच्या पुढे दोन डेसिमल जोडले गेले. कारण या कॅटेगरीच्या सब कॅटेगरीमध्ये जो Format आपण सिलेक्ट केला असेल त्याप्रमाणे आपल्याला तो नंबर दिसतो. Date ही कॅटेगरी असताना आपल्याला २७ नोव्हेंबर २००३ ही तारीख दिसते. कारण एक्सेलमध्ये प्रत्येक तारीख ही एक नंबर मात्र असते. त्यामुळेच दोन तारखेतील दिवस आपण मोजू शकतो. Accounting आणि Currency या कॅटेगरी असताना त्याचा फक्त display format बदलला गेला आहे. Text कॅटेगरी मध्ये हा नंबर जसा आहे तसा साठवला गेला आहे. पण जर आपण बारकाईने पाहिले तर त्या सेलच्या Top Left Position ला आपल्याला एक हिरव्या रंगाचा छोटा indicator दिसेल. हा indicator आपल्याला त्या सेलची कॅटेगरी Text असल्याचे सूचित करतो. तसेच त्यावर क्लिक केल्यास त्याबद्दलची इतर माहिती सुद्धा पुरवतो. Percentage कॅटेगरी असताना जो नंबर आपण देऊ त्याला तो % हे चिन्ह लावतो. इतकेच नाही तर त्याचा दुसरा अर्थ होतो, ती संख्या भागिले १००. ( टेबल १.२ ) बाकीच्या कॅटेगरी ह्या सहसा आपल्याला वापराव्या लागत नाहीत.

दुसऱ्या ओळीत मी ५/३ ही माहिती भरतो आहे पण परत इथेही काही ठिकाणी ती ३ मे अशी दिसते, काही ठिकाणी १.६७ अशी तर काही ठिकाणी १ २/३ दिसते. ज्या डाटाची सुरुवात अक्षराने होते तो मात्र कोणतीही कॅटेगरी असेल तरी सारखाच दिसेल. मग प्रश्न असा पडतो की आपल्याला प्रत्येक वेळेस त्या सेलची कॅटेगरी बदलायची का? उत्तर आहे हो... आणि नाही सुद्धा... म्हणजे कॅटेगरी बदलणे त्या मानाने खूप सोपे असते. एक्सेल रिबन मधील होम या tab वर आपल्याला ती लगेच सापडते. पण त्याला दुसरा सुद्धा एक मार्ग म्हणजे कोणत्याची डाटाच्या आधी जर ( ‘ ) ( single quotation mark ) दिले तर सेल कॅटेगरी कोणतीही असो डाटा हा टेक्स्ट स्वरूपाचा समजला जातो. उदा. टेबल १.१ मधील पाचवी आणि सहावी ओळ... पाचव्या ओळीत =45/0 असा फोर्मुला दिला आहे. Text ही कॅटेगरी सोडली तर इतर सगळ्या ठिकाणी #Div/0! ही एरर तुम्हाला दिसून येईल कारण =45/0 याला इतर सगळ्या कॅटेगरी फोर्मुला म्हणून स्विकारतात पण Text ही कॅटेगरी त्याला फक्त शब्दसमूह म्हणून स्विकारते. सहाव्या ओळीत मात्र प्रत्येक कॅटेगरीच्या सेल मध्ये ( ‘=45/0 ) फोर्मुलाच्या आधी single quotation mark दिले आहे त्यामुळे सगळीकडे त्याला फक्त शब्दसमूह म्हणूनच स्विकारले गेले आहे. सेल कॅटेगरी फक्त डाटावरच नाही तर आपण देत असलेल्या फोर्मुलावर सुद्धा इफेक्ट करते.

टेबल १.३ मध्ये प्रत्येक कॅटेगरीमध्ये AUTOSUM function कशा पद्धतीचा रिझल्ट देईल हे दाखवण्यात आले आहे.

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