एक्सेल स्प्रेडशीट संपादक के निश्चित रूप से कई सक्रिय उपयोगकर्ताओं को कभी-कभी उन स्थितियों से निपटना पड़ता था जिनमें मूल्यों को एक तालिका से दूसरे तालिका में स्थानापन्न करना आवश्यक था। जरा सोचिए, एक निश्चित वस्तु आपके गोदाम में आई। हमारे पास हमारे निपटान में दो फाइलें हैं: एक प्राप्त माल के नामों की सूची के साथ, दूसरा - इस उत्पाद की मूल्य सूची। मूल्य सूची को खोलने के बाद, हम पाते हैं कि इसमें और भी स्थितियाँ हैं और वे उस क्रम में स्थित नहीं हैं जो नामों की सूची के साथ फाइल में है। यह संभावना नहीं है कि हम में से कोई भी दोनों फाइलों की जांच करने और एक दस्तावेज़ से दूसरे मैन्युअल रूप से कीमतों को स्थानांतरित करने के विचार को पसंद करेगा। बेशक, इस मामले में जब यह 5-10 पदों पर आता है, तो डेटा का यांत्रिक इनपुट काफी संभव है, लेकिन क्या होगा यदि नामों की संख्या 1000 से अधिक हो? इस मामले में, एक्सेल और इसका जादू सीडीएफ फ़ंक्शन (या vlookup, अगर हम कार्यक्रम के अंग्रेजी संस्करण के बारे में बात कर रहे हैं) हमें नीरस काम से निपटने में मदद करेगा।
वीपीआर आपको बड़ी तालिकाओं के साथ काम करने की अनुमति देता है, एक से दूसरे में डेटा स्थानांतरित करता है
सीडीएफ क्या है और इसका उपयोग कैसे करें?
इसलिए, एक तालिका से दूसरे तालिका में डेटा परिवर्तित करने पर हमारे काम की शुरुआत में, सीडीएफ फ़ंक्शन का एक छोटा अवलोकन करना उचित होगा। जैसा कि आप शायद पहले से ही समझ चुके हैं, vlookup आपको एक टेबल से दूसरे टेबल पर डेटा ट्रांसफर करने की अनुमति देता है, जिससे हमारे द्वारा आवश्यक कोशिकाओं को भरना पड़ता है। सीडीएफ फ़ंक्शन को सही ढंग से काम करने के लिए, अपनी तालिका के शीर्षलेखों में विलय की गई कोशिकाओं की उपस्थिति पर ध्यान दें। यदि कोई हो, तो आपको उन्हें तोड़ने की आवश्यकता होगी।
मान लें कि हमें "मूल्य सूची" से डेटा के साथ "ऑर्डर टेबल" भरने की आवश्यकता है
इसलिए, हम कार्य का सामना करते हैं - मौजूदा उत्पादों की कीमतों को उनके नाम के साथ एक तालिका में स्थानांतरित करना और प्रत्येक उत्पाद की कुल लागत की गणना करना। ऐसा करने के लिए, हमें निम्नलिखित एल्गोरिथ्म को पूरा करना होगा:
- सबसे पहले, एक्सेल स्प्रेडशीट को अपने इच्छित रूप में लाएँ। "मूल्य" और "लागत" नामों के साथ तैयार डेटा मैट्रिक्स दो कॉलम में जोड़ें। नवगठित कॉलम की सीमा में कोशिकाओं के लिए चुनें, मौद्रिक प्रारूप।
- अब "मूल्य" ब्लॉक में पहले सेल को सक्रिय करें और "फ़ंक्शन विजार्ड" को कॉल करें। यह सूत्र पंक्ति के सामने स्थित "fx" बटन को दबाकर या "Shift + F3" कुंजी संयोजन को दबाकर किया जा सकता है। खुलने वाले संवाद में, श्रेणी "लिंक और सरणियाँ" ढूंढें। यहां हमें सीडीएफ के कार्य को छोड़कर किसी भी चीज में दिलचस्पी नहीं है। इसे चुनें और "ओके" पर क्लिक करें। वैसे, यह कहा जाना चाहिए कि VLOOKUP फ़ंक्शन को "सूत्र" टैब के माध्यम से ड्रॉप-डाउन सूची में बुलाया जा सकता है, जिसमें "लिंक और सरणियाँ" श्रेणी भी स्थित है।
- सीडीएफ को सक्रिय करने के बाद, आप अपने चुने हुए फ़ंक्शन के तर्कों की एक सूची के साथ एक विंडो देखेंगे। "आवश्यक मान" फ़ील्ड में आपको प्राप्त सामानों की सूची और उनकी मात्रा के साथ तालिका के पहले कॉलम में निहित डेटा रेंज दर्ज करनी होगी। यही है, आपको एक्सेल को यह बताने की जरूरत है कि दूसरी तालिका में वास्तव में क्या मिलना चाहिए और पहले एक को स्थानांतरित करना चाहिए।
- पहला तर्क चिह्नित होने के बाद, आप दूसरे पर जा सकते हैं। हमारे मामले में, दूसरा तर्क मूल्य सूची के साथ एक तालिका है। माउस कर्सर को तर्क क्षेत्र में रखें और कीमतों की सूची में स्थानांतरित करें। मैन्युअल रूप से वाणिज्यिक उत्पादों के नाम और उनकी कीमत के साथ स्तंभों के क्षेत्र में स्थित कोशिकाओं के साथ सीमा का चयन करें। एक्सेल निर्दिष्ट करें, कि आप कौन से मान VLOOKUP फ़ंक्शन को मैप करना चाहते हैं।
- एक्सेल को भ्रमित न करने और आपके द्वारा आवश्यक डेटा को संदर्भित करने के लिए, इसके लिए दिए गए लिंक को ठीक करना महत्वपूर्ण है। ऐसा करने के लिए, तालिका फ़ील्ड में आवश्यक मानों को हाइलाइट करें और F4 कुंजी दबाएं। यदि सब कुछ सही ढंग से किया जाता है, तो स्क्रीन पर $ साइन दिखाई देना चाहिए।
- अब हम पृष्ठ संख्या तर्क के क्षेत्र में जाते हैं और इसे "2" मान पर सेट करते हैं। इस ब्लॉक में वह सभी डेटा होता है जिसे आप हमारी कार्य तालिका में भेजना चाहते हैं, और इसलिए इंटरवल व्यू के लिए एक गलत मान निर्दिष्ट करना महत्वपूर्ण है (FALSE की स्थिति निर्धारित करें)। वीएलआर फ़ंक्शन को केवल सटीक मानों के साथ काम करने के लिए और उन्हें गोल नहीं करने के लिए यह आवश्यक है।
पहले कॉलम के लिए फॉर्मूला भरें।
अब जबकि सभी आवश्यक कार्य पूरे हो चुके हैं, हम केवल "ओके" बटन दबाकर उनकी पुष्टि कर सकते हैं। जैसे ही पहली सेल में डेटा बदलता है, हमें पूरे Excel दस्तावेज़ में CDF फ़ंक्शन को लागू करना होगा। ऐसा करने के लिए, पूरे "मूल्य" कॉलम पर VLOOKUP का प्रचार करना पर्याप्त है। यह स्तंभ के बहुत नीचे तक संशोधित मूल्य के साथ सेल के निचले दाएं कोने को खींचकर किया जा सकता है। अगर सब कुछ काम कर गया, और आवश्यकतानुसार डेटा बदल गया, तो हम अपने माल के कुल मूल्य की गणना के लिए आगे बढ़ सकते हैं। इस क्रिया को करने के लिए, हमें दो कॉलम - "क्वांटिटीज़" और "मूल्य" के उत्पाद को खोजने की आवश्यकता है। चूंकि एक्सेल में सभी गणितीय सूत्र हैं, इसलिए गणना "फॉर्मूला बार" में परिचित "एफएक्स" आइकन का उपयोग करके प्रदान की जा सकती है।
महत्वपूर्ण बिंदु
ऐसा लगता है कि सब कुछ तैयार है और VLOOKUP हमारे कार्य के साथ मुकाबला कर रहा है, लेकिन यह वहां नहीं था। तथ्य यह है कि "मूल्य" कॉलम में सीडीएफ फ़ंक्शन अभी भी सक्रिय है, इस तथ्य का प्रमाण फॉर्मूला बार में बाद का प्रदर्शन है। अर्थात्, हमारी दोनों तालिकाएँ एक दूसरे से संबंधित हैं। इस तरह के एक अग्रानुक्रम इस तथ्य को जन्म दे सकता है कि जब मूल्य के साथ तालिका में डेटा बदलता है, तो माल की सूची के साथ हमारी कार्यशील फ़ाइल में निहित जानकारी भी बदल जाती है।
दो तालिकाओं को विभाजित करके इस स्थिति से सबसे अच्छा बचा जाता है। ऐसा करने के लिए, हमें उन कक्षों का चयन करना होगा जो मूल्य स्तंभ की सीमा में हैं, और उस पर राइट-क्लिक करें। खुली हुई खिड़की में, "कॉपी" विकल्प चुनें और सक्रिय करें। उसके बाद, चयनित सेल क्षेत्र से चयन को हटाने के बिना, सही माउस बटन को फिर से दबाएं और "पेस्ट स्पेशल" विकल्प चुनें।
इस विकल्प को सक्षम करने से आपकी स्क्रीन पर एक डायलॉग बॉक्स खुलेगा जिसमें आपको "मान" श्रेणी के आगे वाले बॉक्स को चेक करना होगा। "ओके" बटन पर क्लिक करके अपने कार्यों की पुष्टि करें।
हम अपने सूत्र पट्टी पर लौटते हैं और मूल्य कॉलम में सक्रिय फ़ंक्शन VLOOKUP की उपस्थिति की जांच करते हैं। यदि सूत्र का स्थान आप केवल संख्यात्मक मान देखते हैं, तो सब कुछ बदल गया है, और सीडीएफ फ़ंक्शन अक्षम है। यही है, दो एक्सेल फ़ाइलों के बीच कनेक्शन टूट गया है, और मूल्य सूची के साथ तालिका में संलग्न डेटा के अनियोजित परिवर्तन या हटाने का कोई खतरा नहीं है। अब आप सुरक्षित रूप से सारणीबद्ध दस्तावेज़ का उपयोग कर सकते हैं और चिंता न करें कि मूल्य सूची बंद होने या किसी अन्य स्थान पर ले जाने पर क्या होगा।
पूरे तालिका में सूत्र को लागू करने के लिए सेल के निचले दाएं कोने में मार्कर को खींचें।
एक्सेल में दो तालिकाओं की तुलना कैसे करें?
सीडीएफ फ़ंक्शन की सहायता से, आप क्रम में सेकंड के एक मामले में कई अलग-अलग मूल्यों की तुलना कर सकते हैं, उदाहरण के लिए, किसी मौजूदा उत्पाद की कीमतें कैसे बदल गई हैं, इसकी तुलना करने के लिए। ऐसा करने के लिए, आपको खाली कॉलम में VLOOKUP को पंजीकृत करना होगा और फ़ंक्शन को उन परिवर्तित मानों से जोड़ना होगा जो किसी अन्य तालिका में हैं। सबसे अच्छा, अगर स्तंभ "नई कीमत" कॉलम "मूल्य" के तुरंत बाद स्थित होगा। इस तरह का निर्णय आपको तुलनात्मक रूप से मूल्य परिवर्तनों को अधिक दृश्यमान बनाने की अनुमति देगा।
कई स्थितियों के साथ काम करने की क्षमता
VLOOKUP फ़ंक्शन का एक और निस्संदेह लाभ यह है कि यह आपके उत्पाद में निहित कई मापदंडों के साथ काम करने की क्षमता है। दो या अधिक विशेषताओं द्वारा उत्पाद खोजने के लिए, आपको निम्न करना होगा:
- दो (या यदि आवश्यक हो, तो अधिक) खोज शब्द बनाएं।
- एक नया कॉलम जोड़ें, जिसमें फ़ंक्शन के काम के दौरान, अन्य सभी कॉलम जोड़े जाएंगे, जिसके द्वारा उत्पाद खोजा जाता है।
- परिणामस्वरूप कॉलम में, ऊपर वर्णित एल्गोरिथ्म के अनुसार, हम वीएलबुकअप सूत्र का परिचय देते हैं, जो पहले से ही हमसे परिचित है।
निष्कर्ष में, यह कहा जाना चाहिए कि सीडीएफ के रूप में इस तरह के एक फ़ंक्शन के एक्सेल को बनाए रखना सारणीबद्ध जानकारी के साथ काम करना बहुत सरल करता है। भारी मात्रा में डेटा के साथ काम करने में VLOOKUP का उपयोग करने से डरो मत, क्योंकि वे कैसे निष्पादित किए जाते हैं, फ़ंक्शन का सिद्धांत हमेशा समान होता है। आपको बस इसके तर्कों को सही ढंग से परिभाषित करने की आवश्यकता है।