सभी Excel-समारोह CDF (VLOOKUP) के रहस्यों को किसी तालिका में डेटा के लिए खोज और उन्हें किसी अन्य को पुनः प्राप्त करने के
टिप्स प्रौद्योगिकी के / / December 19, 2019
Batyanov डेनिस अतिथि लेखक के अधिकारों पर पता चलता है ऊर्ध्वाधर देखने समारोह के सभी रहस्यों को एक एक्सेल स्प्रेडशीट में डेटा को खोजने और उन्हें किसी अन्य को निकालने के लिए कैसे करें के बारे इस पोस्ट में बताते हैं, और।
जब में काम कर एक्सेल बहुत बार एक तालिका में डेटा को खोजने और उन्हें किसी अन्य को दूर करने के लिए की जरूरत है। आप इसे कैसे करना नहीं जानते हैं, तो मैं लेख को पढ़ने के लिए, आप केवल यह नहीं सीख जाएगा, लेकिन यह भी क्या स्थिति आप इस प्रणाली के अधिकतम प्रदर्शन बाहर निचोड़ कर सकेंगे के तहत पता लगाना। सबसे अत्यधिक प्रभावी तकनीक है कि CDF के समारोह के साथ संयोजन के रूप में इस्तेमाल किया जाना चाहिए माना जाता है।
आप उपयोग की साल भले ही CDF समारोह, उच्च संभावना के साथ यह लेख आपके लिए उपयोगी हो सकता है और उदासीन नहीं छोड़ देंगे। मैं, उदाहरण के लिए, आईटी विशेषज्ञ, और उसके बाद आईटी में नेता, इस्तेमाल किया VLOOKUP 15 साल, के रूप में लेकिन सभी बारीकियों के साथ सौदा करने के लिए अब बस हो गया, जब मैं एक पेशेवर आधार मैं सिखाने के लिए शुरू किया पर हूँ एक्सेल लोग।
CDF - का एक संक्षिप्त नाम मेंकार्यक्षेत्र,
आदिनिरीक्षण। इसी तरह, VLOOKUP - कार्यक्षेत्र LOOKUP। समारोह का नाम हमें पता चलता है कि यह खोज तालिका की पंक्तियां (ऊर्ध्वाधर - लाइन छंटाई और अक्ष फिक्सिंग), और नहीं कॉलम में (क्षैतिज - कॉलम क्रमित और फिक्सिंग लाइन)। यह ध्यान देने योग्य है कि CDF एक बहन है - बदसूरत बतख का बच्चा है कि एक हंस बन कभी नहीं होगा - PGR (HLOOKUP) के एक समारोह है। PGR, के रूप में CDF करने का विरोध किया, एक क्षैतिज खोज, लेकिन एक्सेल अवधारणा (और वास्तव में अवधारणा का उत्पादन डेटा) साधन का आयोजन कि आपके टेबल कॉलम और कई और अधिक की एक छोटी संख्या है लाइनों। यही कारण है कि तार के लिए खोज, हम कई बार स्तंभों से अधिक की आवश्यकता है। आप तो एक्सेल भी अक्सर HLOOKUP उपयोग करते हैं, यह काफी संभावना है कि आप इस जीवन में समझ में नहीं आता है कि वहाँ है।वाक्य-विन्यास
VLOOKUP चार पैरामीटर:
पी पी एस = ( ; ;
- वांछित मान (दुर्लभ) या सेल वांछित मान (अधिकांश मामलों) से युक्त के लिए एक संदर्भ;
(!) - कोशिकाओं (दो आयामी सरणी) की एक सीमा के लिए एक संदर्भ है, जो पहले कॉलम में है पैरामीटर मान खोज की जाएगी ;
- यह एक बहुत महत्वपूर्ण पैरामीटर है, जो सवाल का जवाब है, जैसे कि सीमा के पहले कॉलम आरोही क्रमबद्ध हो जाता है है । गलत (FALSE) या 0 - सरणी सॉर्ट हो जाता है, तो हम सही (TRUE) या 1, अन्यथा संकेत मिलता है। यदि आप इस मानदंड को छोड़ देते हैं, तो डिफ़ॉल्ट रूप से, यह 1 के बराबर है।
मुझे यकीन है कि जो लोग एक छीलने के रूप में CDF समारोह पता के कई, असुविधा हो सकती है चौथे पैरामीटर का विवरण पढ़ क्योंकि वे एक से थोड़ा अलग तरीके से यह देखने के लिए प्रयोग किया है: आम तौर पर वहाँ खोज (FALSE या 0), या एक ही देखने रेंज के बारे में के साथ सटीक अनुसार के बारे में एक भाषण है (सही या 1).
अब यह कसने और अगले अनुच्छेद कई बार पढ़ा जब तक आप क्या अंत तक कहा गया है के अर्थ के लिए एक महसूस पाने के लिए आवश्यक है। वहाँ हर शब्द के लिए महत्वपूर्ण है। उदाहरण समझने में मदद मिलेगी।
एक ही सूत्र के रूप में विशेष रूप से काम करता है CDF
- सूत्र प्रथम के प्रकार पिछले पैरामीटर छोड़े गए या निर्दिष्ट है, तो 1 के रूप में, CDF को उम्मीद है कि पहले कॉलम, आरोही क्रम में सॉर्ट किया जाता है ताकि खोज लाइन है कि कम से बंद हो जाएगा तुरंत जिसमें अपेक्षा से अधिक से अधिक मूल्य पंक्ति के पहले आता है. यदि यह रेखा नहीं पाया जाता है, यह सीमा के अंतिम पंक्ति देता है।
- सूत्र द्वितीय के लिए लिखें। दूसरा विकल्प 0 के रूप में निर्दिष्ट किया जाता है, तो CDF क्रमिक रूप से सरणी के पहले कॉलम को स्कैन करता है और तुरंत बंद हो जाता है खोज जब पहली पैरामीटर के साथ एक सटीक मिलान मिला , अन्यथा त्रुटि कोड है # N / A (# N / A)।
इस योजना के फार्मूले
CDF प्रकार मैं
CDF प्रकार द्वितीय
सूत्र मैं की प्रजातियों के लिए निहितार्थ
- सूत्रों मानों की श्रेणी के वितरण के लिए इस्तेमाल किया जा सकता।
- पहले कॉलम डुप्लिकेट होता है और ठीक से हल कर रहे हैं, तो यह डुप्लिकेट मानों वाली पंक्तियों की पिछले लौटा दी जाएगी।
- अगर हम स्पष्ट रूप से और अधिक से अधिक आप पहले कॉलम शामिल कर सकते हैं मूल्य के लिए लग रही है, आप आसानी से मेज, जो काफी मूल्यवान हो सकती की अंतिम पंक्ति पा सकते हैं।
- केवल जब यह एक मूल्य कम पाता है की तुलना में या वांछित के बराबर यह दृश्य एक त्रुटि # N / A वापस आ जाएगी।
- समझे सूत्र देता है कि गलत मान, यदि आपका सरणी, पृथक नहीं किया जा रहा है काफी मुश्किल।
सूत्र द्वितीय की प्रजातियों के लिए निहितार्थ
मान सरणी में कई बार के पहले कॉलम में पाया जाता है, सूत्र बाद में पुनः प्राप्ति के लिए पहली पंक्ति का चयन करेंगे।
उत्पादकता VLOOKUP
आप लेख के स्थान के चरमोत्कर्ष पर पहुंच गया है। यह प्रतीत होता है, अगर मैं पिछले पैरामीटर शून्य या एक के रूप में अच्छी तरह से बताते हैं, क्या अंतर है? मूल रूप से, सभी बिंदु, ज़ाहिर है, शून्य की, के रूप में यह काफी व्यावहारिक है: सरणी के पहले कॉलम छँटाई के बारे में चिंता करने की कोई जरूरत है, एक देख सकते हैं, मूल्य पाया है या नहीं। लेकिन अगर आप कई हजार सूत्रों CDF (VLOOKUP) की एक सूची है, तो आप उस CDF प्रकार द्वितीय रन धीरे धीरे देखेंगे। यह आमतौर पर सभी में सोचना शुरू किया गया है:
- मैं एक अधिक शक्तिशाली कंप्यूटर की जरूरत है;
- मैं एक त्वरित सूत्र की जरूरत है, उदाहरण के लिए, कई लोगों सूचकांक + मैच (INDEX + मैच) है, जो माना जाता है तेजी से दुखी 5-10% के बारे में पता है।
और कुछ लोगों को लगता है कि जैसे ही का उपयोग शुरू CDF मैं टाइप करें और CDF आपरेशन की गति 57 बार में वृद्धि होगी के रूप में प्रथम स्तंभ सॉर्ट करने के लिए किसी भी तरह से प्रदान करते हैं। में पचास सात बार - मैं शब्द लिखने! 57% और 5700% में। इस तथ्य को मैं काफी मज़बूती से जाँच की।
वास्तव में इस तरह के त्वरित काम झूठ करने के लिए रहस्य नहीं है कि आप क्रमबद्ध सरणी के लिए आवेदन कर सकते हैं अत्यंत है कुशल खोज एल्गोरिथ्म, जो एक द्विआधारी खोज (द्विभाजन विधि के रूप में जाना जाता है, विरोधाभास)। मैं तो CDF की तरह इसे इस्तेमाल करता है, और CDF प्रकार द्वितीय सामान्य रूप में किसी भी अनुकूलन के बिना करना चाहता है। एक ही कार्य करने के लिए मैच (मैच), जो एक ही पैरामीटर शामिल हैं पर लागू होता है, और यह भी देख (LOOKUP) है, जो लोटस के साथ संगतता के लिए केवल क्रमबद्ध सरणियों पर काम करता है और Excel में सक्रिय 1-2-3.
नुकसान सूत्र
CDF का नुकसान स्पष्ट हैं: पहला, यह इस सरणी का केवल पहला स्तंभ चाहता है, और दूसरा, सिर्फ इस स्तंभ के दाईं ओर। और जैसा कि आप जानते हैं, यह अच्छी तरह से हो सकता है कि स्तंभ स्तंभ जिसमें हम तलाश के बाईं ओर आवश्यक जानकारी शामिल है। इस कमी से रहित पहले से ही फार्मूले का एक समूह का उल्लेख सूचकांक + मैच (INDEX + मैच) है, जो यह सबसे लचीला CDF (VLOOKUP) के साथ तुलना में तालिकाओं से डेटा निकालने का हल करता है।
वास्तविक जीवन में सूत्र के आवेदन के कुछ पहलू
रेंज खोज
खोज रेंज के क्लासिक उदाहरण - आदेश छूट के आकार का निर्धारण करने की समस्या।
पाठ स्ट्रिंग्स के लिए खोज
बेशक, CDF चाहता है न केवल संख्या लेकिन यह भी पाठ। यह ध्यान रखें कि केस-संवेदी सूत्र भेद नहीं करता है में वहन किया जाना चाहिए। आप वाइल्डकार्ड वर्णों का उपयोग करते हैं, यह एक फजी खोज को व्यवस्थित करने के लिए संभव है। दो वाइल्डकार्ड रहे हैं "?" - पाठ स्ट्रिंग "*" में किसी भी एक वर्ण की जगह - किसी भी वर्णों की संख्या बदल देता है।
रिक्तियों के साथ लड़ो
अक्सर यह सवाल उठाया जाता है, कैसे खोज में अतिरिक्त रिक्त स्थान की समस्या को हल करने के लिए। लुक-अप तालिका अभी भी उन्हें साफ करने के लिए संभव है, तो सूत्र के पहले पैरामीटर CDF हमेशा आप पर निर्भर नहीं करता। इसलिए, यदि कोशिकाओं अतिरिक्त जगह अवरोध का खतरा मौजूद है, यह संभव सफाई के लिए TRIM (ट्रिम) समारोह का उपयोग करने के लिए है।
एक अलग डेटा स्वरूप
पहले पैरामीटर समारोह CDF एक सेल है कि एक नंबर शामिल करने के लिए संदर्भित करता है, लेकिन जो में जमा हो जाती है, तो पाठ के रूप में सेल और सरणी के पहले कॉलम सही प्रारूप में नंबर शामिल हैं, खोज हो जाएगा असफल। रिवर्स स्थिति। समस्या को आसानी से हस्तांतरण पैरामीटर 1 से हल किया जाता है के लिए आवश्यक प्रारूप में:
पी पी एस = (- D7; उत्पाद $ A $ 2: $ सी $ 5;! 3; 0) - अगर D7 पाठ और तालिका में शामिल है - संख्या;
= CDF (D7 और «»); उत्पाद $ A $ 2: $ सी $ 5;! 3; 0) - और इसके विपरीत।
वैसे, संख्या में पाठ का अनुवाद करने के कई तरीके हो सकते हैं, चुनें:
- डबल नकारात्मक -D7।
- गुणा इकाई D7 * 1।
- शून्य D7 + 0 के साथ इसके अलावा।
- पहली डिग्री D7 ^ 1 का निर्माण।
पाठ का अनुवाद एक खाली स्ट्रिंग, का कारण बनता है जो एक्सेल डेटा प्रकार परिवर्तित करने के लिए के साथ युग्मन द्वारा उत्पादन किया।
कैसे को दबाने के लिए जारी करने के # N / A
यह बहुत IFERROR फ़ंक्शन (IFERROR) के साथ क्या करना सुविधाजनक है।
उदाहरण के लिए: = IFERROR (CDF (D7; उत्पाद $ A $ 2: $ सी $ 5;! 3; 0); «»).
CDF एक त्रुटि कोड # N / A वापस आ जाएगी, तो यह IFERROR स्थानापन्न और पैरामीटर 2 (इस मामले में, एक खाली स्ट्रिंग) रोकना होगा, और यदि कोई त्रुटि हुई है, इस समारोह का नाटक है कि यह बिल्कुल नहीं है, लेकिन केवल CDF, सामान्य करने के लिए बहाल परिणाम है।
सरणी
अक्सर अनदेखी एक सरणी संदर्भ पूर्ण किया जाता है, और जब "तैरता" की एक सरणी खींच रहा है। याद रखें ए 2 के बजाय कि: सी 5 इस्तेमाल किया जाना चाहिए $ A $ 2: $ सी $ 5।
एक अच्छा विचार कार्यपुस्तिका में एक अलग शीट पर एक संदर्भ सरणी जगह है। यह पैरों के नीचे नहीं मिलता है, और संग्रहीत की जाएगी।
नामांकित श्रेणी के रूप में देता है की घोषणा करने के लिए इससे भी अधिक अच्छा विचार।
कई प्रयोक्ताओं उपयोग किया जाता है जब सरणी ग्रुप ए की संरचना को निर्दिष्ट: सी, पूरी तरह से कॉलम का संकेत है। यह दृष्टिकोण अस्तित्व के लिए एक सही जब से तुम इस तथ्य है कि अपने सरणी सभी स्ट्रिंग्स शामिल का ट्रैक रखने के होने से बच जाता है। आप मूल सरणी के एक टुकड़े पर एक लाइन जोड़ दें, तो रेंज एक के रूप में निर्दिष्ट: सी, को समायोजित करने के लिए नहीं होगा। बेशक, इस वाक्य Excel श्रेणी का सही संकेत के साथ तुलना में कुछ अधिक परिश्रम खर्च करने का कारण बनता है, लेकिन डेटा भूमि के ऊपर नगण्य है। हम एक दूसरे के सैकड़े के बारे में बात कर रहे हैं।
ठीक है, प्रतिभा के कगार पर - एक सरणी के रूप में इस मुद्दे को स्मार्ट तालिका.
निष्कर्षण स्तंभ निर्दिष्ट करने के लिए स्तंभ समारोह का उपयोग करना
तालिका जिसमें आप CDF का उपयोग कर डेटा निकालने हैं, तो लुक-अप तालिका के रूप में एक ही संरचना है, लेकिन सिर्फ WRT डोमेन पंक्तियों की एक न्यूनतम संख्या स्वचालित रूप से वसूली की संख्या की गणना के लिए स्तंभ () फंक्शन इस्तेमाल किया जा सकता शामिल हैं कॉलम। इस मामले में सभी CDF-सूत्र एक ही (पहले पैरामीटर के लिए समायोजित, जो स्वत: बदल जाता है) हो जाएगा! ध्यान दें कि पहले पैरामीटर पूर्ण स्तंभ के समन्वय है।
»और | द्वारा और» एक समग्र कुंजी बनाना
तो एक समय में एक से अधिक स्तंभ को देखने के लिए की जरूरत है, यह खोज के लिए एक समग्र कुंजी बनाने के लिए आवश्यक है। यदि दिए गए मान (क्षेत्र "कोड" के मामले में यहां के रूप में) पाठ, और संख्यात्मक, के लिए नहीं है यह आवश्यकता नहीं होगी एक सुविधाजनक सूत्र SUMIFS (SUMIFS) और एक अभिन्न कुंजी स्तंभ के लिए आया था सब पर।
यह Layfhakera के लिए मेरा पहला लेख है। आपको यह पसंद आया, तो मैं तुम्हें यात्रा करने के लिए आमंत्रित करते हैं मेरी साइटऔर ख़ुशी से VLOOKUP और तरह का उपयोग करने के रहस्य के बारे में अपनी टिप्पणी पढ़ें। धन्यवाद। :)