सामग्री
आम्ही अलीकडेच एका सर्वात उपयुक्त एक्सेल फंक्शनला एक लेख समर्पित केला आहे व्हीपीआर आणि डेटाबेसमधून आवश्यक माहिती वर्कशीट सेलमध्ये काढण्यासाठी कशी वापरली जाऊ शकते हे दाखवले. आम्ही हे देखील नमूद केले आहे की फंक्शनसाठी दोन वापर प्रकरणे आहेत व्हीपीआर आणि त्यापैकी फक्त एक डेटाबेस प्रश्नांशी संबंधित आहे. या लेखात, आपण फंक्शन वापरण्याचा आणखी एक कमी ज्ञात मार्ग शिकाल व्हीपीआर एक्सेलमध्ये
जर तुम्ही अजून हे केले नसेल, तर फंक्शनबद्दलचा शेवटचा लेख नक्की वाचा व्हीपीआर, कारण खालील सर्व माहिती असे गृहीत धरते की तुम्ही पहिल्या लेखात वर्णन केलेल्या तत्त्वांशी आधीच परिचित आहात.
डेटाबेससह कार्य करताना, कार्ये व्हीपीआर एक युनिक आयडेंटिफायर पास केला जातो, जो आम्हाला शोधू इच्छित असलेली माहिती ओळखण्यासाठी वापरला जातो (उदाहरणार्थ, उत्पादन कोड किंवा ग्राहक ओळख क्रमांक). हा अद्वितीय कोड डेटाबेसमध्ये उपस्थित असणे आवश्यक आहे, अन्यथा व्हीपीआर त्रुटीची तक्रार करेल. या लेखात, आपण फंक्शन वापरण्याचा हा मार्ग पाहू व्हीपीआरजेव्हा डेटाबेसमध्ये आयडी अजिबात अस्तित्वात नाही. जणू कार्य व्हीपीआर अंदाजे मोडवर स्विच केले, आणि जेव्हा आम्हाला काहीतरी शोधायचे असेल तेव्हा आम्हाला कोणता डेटा प्रदान करायचा ते निवडतो. विशिष्ट परिस्थितींमध्ये, हेच आवश्यक आहे.
जीवनातील एक उदाहरण. आम्ही कार्य सेट केले
चला हा लेख वास्तविक जीवनातील उदाहरणासह स्पष्ट करूया - विक्री मेट्रिक्सच्या विस्तृत श्रेणीवर आधारित कमिशनची गणना करणे. आम्ही अगदी सोप्या पर्यायाने सुरुवात करू, आणि नंतर फंक्शन वापरणे हा समस्येचे एकमेव तर्कसंगत उपाय होईपर्यंत आम्ही हळूहळू ते गुंतागुंतीत करू. व्हीपीआर. आमच्या काल्पनिक कार्यासाठी प्रारंभिक परिस्थिती खालीलप्रमाणे आहे: जर एखाद्या विक्रेत्याने एका वर्षात $30000 पेक्षा जास्त विक्री केली, तर त्याचे कमिशन 30% आहे. अन्यथा, कमिशन फक्त 20% आहे. चला ते टेबलच्या स्वरूपात ठेवूया:
विक्रेता सेल B1 मध्ये त्यांचा विक्री डेटा प्रविष्ट करतो आणि सेल B2 मधील सूत्र विक्रेत्याला अपेक्षित असलेला योग्य कमिशन दर निर्धारित करतो. या बदल्यात, परिणामी दर सेल B3 मध्ये विक्रेत्याला मिळणाऱ्या एकूण कमिशनची गणना करण्यासाठी वापरला जातो (फक्त सेल B1 आणि B2 गुणाकार करणे).
टेबलचा सर्वात मनोरंजक भाग सेल B2 मध्ये समाविष्ट आहे - हे कमिशन दर निश्चित करण्यासाठी सूत्र आहे. या फॉर्म्युलामध्ये एक्सेल फंक्शन म्हणतात IF (तर). जे वाचक या कार्याशी परिचित नाहीत त्यांच्यासाठी मी ते कसे कार्य करते ते स्पष्ट करेन:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
अट एक फंक्शन वितर्क आहे जे एकतर मूल्य घेते ट्रू कोड (TRUE), किंवा असत्य (असत्य). वरील उदाहरणात, अभिव्यक्ती B1
B1 हे B5 पेक्षा कमी आहे हे खरे आहे का?
किंवा तुम्ही ते वेगळ्या पद्धतीने म्हणू शकता:
हे खरे आहे की वर्षासाठी एकूण विक्रीची रक्कम थ्रेशोल्ड मूल्यापेक्षा कमी आहे?
जर आपण या प्रश्नाचे उत्तर दिले होय (TRUE), नंतर फंक्शन परत येते खरे असल्यास मूल्य (सत्य असल्यास मूल्य). आमच्या बाबतीत, हे सेल B6 चे मूल्य असेल, म्हणजे एकूण विक्री थ्रेशोल्डच्या खाली असताना कमिशन दर. जर आम्ही प्रश्नाचे उत्तर दिले नाही (FALSE) नंतर परत येतो असत्य असल्यास मूल्य (असत्य असल्यास मूल्य). आमच्या बाबतीत, हे सेल B7 चे मूल्य आहे, म्हणजे एकूण विक्री थ्रेशोल्डच्या वर असताना कमिशन दर.
तुम्ही बघू शकता, जर आम्ही $20000 ची एकूण विक्री घेतली, तर आम्हाला सेल B2 मध्ये 20% कमिशन दर मिळेल. आम्ही $40000 चे मूल्य प्रविष्ट केल्यास, कमिशन दर 30% ने बदलेल:
आमचे टेबल अशा प्रकारे कार्य करते.
आम्ही कार्य गुंतागुंती करतो
चला गोष्टी जरा कठीण करूया. चला आणखी एक थ्रेशोल्ड सेट करू: जर विक्रेत्याने $40000 पेक्षा जास्त कमाई केली, तर कमिशनचा दर 40% पर्यंत वाढेल:
सर्व काही सोपे आणि स्पष्ट दिसते, परंतु सेल B2 मधील आमचे सूत्र लक्षणीयपणे अधिक क्लिष्ट होते. जर तुम्ही सूत्राकडे बारकाईने पाहिले तर तुम्हाला फंक्शनचा तिसरा युक्तिवाद दिसेल IF (IF) दुसर्‍या पूर्ण वाढलेल्या कार्यात बदलले IF (तर). या बांधकामाला फंक्शन्सचे एकमेकांमध्ये नेस्टिंग म्हणतात. Excel आनंदाने या रचनांना अनुमती देते, आणि ते कार्य देखील करतात, परंतु ते वाचणे आणि समजणे खूप कठीण आहे.
आम्ही तांत्रिक तपशीलांचा शोध घेणार नाही - ते का आणि कसे कार्य करते आणि आम्ही नेस्टेड फंक्शन्स लिहिण्याच्या बारीकसारीक गोष्टींमध्ये जाणार नाही. शेवटी, हा फंक्शनला समर्पित लेख आहे व्हीपीआर, Excel साठी संपूर्ण मार्गदर्शक नाही.
काहीही असो, सूत्र अधिक क्लिष्ट होते! जे विक्रेते $50 पेक्षा जास्त विक्री करतात त्यांच्यासाठी 50000% कमिशन दरासाठी आम्ही दुसरा पर्याय सादर केल्यास काय होईल. आणि जर कोणी $60000 पेक्षा जास्त विकले असेल तर ते 60% कमिशन देतील का?
आता सेल B2 मधील सूत्र, जरी ते त्रुटींशिवाय लिहिलेले असले तरी ते पूर्णपणे वाचनीय झाले आहे. मला असे वाटते की असे काही लोक आहेत ज्यांना त्यांच्या प्रकल्पांमध्ये 4 स्तरांची घरटी असलेली सूत्रे वापरायची आहेत. एक सोपा मार्ग असावा?!
आणि असा एक मार्ग आहे! फंक्शन आम्हाला मदत करेल व्हीपीआर.
समस्येचे निराकरण करण्यासाठी आम्ही VLOOKUP फंक्शन लागू करतो
चला आपल्या टेबलची रचना थोडी बदलूया. आम्ही सर्व समान फील्ड आणि डेटा ठेवू, परंतु त्यांना नवीन, अधिक संक्षिप्त पद्धतीने व्यवस्था करू:
थोडा वेळ घ्या आणि नवीन टेबलची खात्री करा रेट टेबल मागील थ्रेशोल्ड सारणी प्रमाणेच डेटा समाविष्ट करते.
फंक्शन वापरणे ही मुख्य कल्पना आहे व्हीपीआर टेबलनुसार इच्छित दर निश्चित करण्यासाठी रेट टेबल विक्रीच्या प्रमाणात अवलंबून. कृपया लक्षात ठेवा की विक्रेता टेबलमधील पाच थ्रेशोल्डपैकी एकाच्या समान नसलेल्या रकमेसाठी वस्तू विकू शकतो. उदाहरणार्थ, तो $34988 मध्ये विकू शकतो, परंतु अशी कोणतीही रक्कम नाही. फंक्शन कसे ते पाहू व्हीपीआर अशा परिस्थितीचा सामना करू शकतो.
VLOOKUP फंक्शन टाकत आहे
सेल B2 निवडा (जेथे आम्हाला आमचे सूत्र घालायचे आहे) आणि शोधा VLOOKUP (VLOOKUP) एक्सेल फंक्शन्स लायब्ररीमध्ये: सूत्रे (सूत्र) > फंक्शन लायब्ररी (फंक्शन लायब्ररी) > शोध आणि संदर्भ (संदर्भ आणि अॅरे).
एक डायलॉग बॉक्स दिसेल कार्य तर्क (फंक्शन वितर्क). आम्ही वितर्कांची मूल्ये एकामागून एक भरतो, यापासून सुरुवात करतो Lookup_value (lookup_value). या उदाहरणात, सेल B1 मधील विक्रीची ही एकूण रक्कम आहे. शेतात कर्सर ठेवा Lookup_value (lookup_value) आणि सेल B1 निवडा.
पुढे, आपल्याला कार्ये निर्दिष्ट करण्याची आवश्यकता आहे व्हीपीआरडेटा कुठे शोधायचा. आमच्या उदाहरणात, हे एक टेबल आहे रेट टेबल. शेतात कर्सर ठेवा सारणी_अॅरे (टेबल) आणि संपूर्ण टेबल निवडा रेट टेबलशीर्षलेख वगळता.
पुढे, आमचा फॉर्म्युला वापरून कोणता कॉलम डेटा काढायचा हे निर्दिष्ट करणे आवश्यक आहे. आम्हाला कमिशन दरामध्ये स्वारस्य आहे, जे टेबलच्या दुसऱ्या स्तंभात आहे. म्हणून, युक्तिवादासाठी Col_index_num (स्तंभ_संख्या) मूल्य 2 प्रविष्ट करा.
आणि शेवटी, आम्ही शेवटचा युक्तिवाद सादर करतो - श्रेणी_लुकअप (interval_lookup).
महत्वाचे: या युक्तिवादाचा उपयोग फंक्शन लागू करण्याच्या दोन पद्धतींमध्ये फरक करतो व्हीपीआर. डेटाबेससह काम करताना, युक्तिवाद श्रेणी_लुकअप (range_lookup) नेहमी मूल्य असणे आवश्यक आहे असत्य (FALSE) अचूक जुळणी शोधण्यासाठी. फंक्शनच्या आमच्या वापरामध्ये व्हीपीआर, आपण हे फील्ड रिक्त सोडले पाहिजे किंवा मूल्य प्रविष्ट केले पाहिजे ट्रू कोड (खरे). हा पर्याय योग्यरित्या निवडणे अत्यंत महत्वाचे आहे.
हे स्पष्ट करण्यासाठी, आम्ही परिचय देऊ ट्रू कोड (TRUE) शेतात श्रेणी_लुकअप (interval_lookup). जरी, आपण फील्ड रिक्त सोडल्यास, ही त्रुटी होणार नाही, पासून ट्रू कोड त्याचे डीफॉल्ट मूल्य आहे:
आम्ही सर्व पॅरामीटर्स भरले आहेत. आता आम्ही दाबतो OK, आणि Excel आमच्यासाठी फंक्शनसह एक सूत्र तयार करतो व्हीपीआर.
आम्ही एकूण विक्री रकमेसाठी अनेक भिन्न मूल्यांसह प्रयोग केल्यास, आम्ही हे सुनिश्चित करू की सूत्र योग्यरित्या कार्य करते.
निष्कर्ष
जेव्हा कार्य व्हीपीआर डेटाबेस, युक्तिवाद सह कार्य करते श्रेणी_लुकअप (range_lookup) स्वीकारणे आवश्यक आहे असत्य (असत्य). आणि मूल्य म्हणून प्रविष्ट केले Lookup_value डेटाबेसमध्ये (Lookup_value) अस्तित्वात असणे आवश्यक आहे. दुसऱ्या शब्दांत, तो अचूक जुळणी शोधत आहे.
आम्ही या लेखात पाहिलेल्या उदाहरणामध्ये, अचूक जुळणी मिळण्याची गरज नाही. जेव्हा फंक्शन असते तेव्हा ही परिस्थिती असते व्हीपीआर इच्छित परिणाम परत करण्यासाठी अंदाजे मोडवर स्विच करणे आवश्यक आहे.
उदाहरणार्थ: $34988 च्या विक्री परिमाण असलेल्या विक्रेत्यासाठी कमिशन गणनेमध्ये कोणता दर वापरायचा हे आम्हाला ठरवायचे आहे. कार्य व्हीपीआर आम्हाला 30% चे मूल्य परत करते, जे पूर्णपणे बरोबर आहे. परंतु सूत्राने 30% किंवा 20% नसून अगदी 40% असलेली पंक्ती का निवडली? अंदाजे शोध म्हणजे काय? चला स्पष्ट होऊ द्या.
जेव्हा वाद श्रेणी_लुकअप (interval_lookup) चे मूल्य आहे ट्रू कोड (TRUE) किंवा वगळलेले, कार्य व्हीपीआर पहिल्या स्तंभाद्वारे पुनरावृत्ती होते आणि सर्वात मोठे मूल्य निवडते जे लुकअप मूल्यापेक्षा जास्त नाही.
महत्वाचा मुद्दाः ही योजना कार्य करण्यासाठी, सारणीचा पहिला स्तंभ चढत्या क्रमाने क्रमवारी लावणे आवश्यक आहे.