4 एक्सेल लुकअप कार्य कुशलता से खोज करने के लिए स्प्रेडशीट

• उत्पादकता

विज्ञापन

अधिकांश समय, Microsoft Excel स्प्रेडशीट की खोज करना बहुत आसान है। यदि आप इसके लिए केवल पंक्तियों और स्तंभों के माध्यम से स्कैन नहीं कर सकते हैं, तो आप इसे खोजने के लिए Ctrl + F का उपयोग कर सकते हैं। यदि आप वास्तव में बड़ी स्प्रेडशीट के साथ काम कर रहे हैं, तो इन चार लुकअप कार्यों में से एक का उपयोग करने के लिए बहुत समय बचा सकता है।

अब "आवश्यक एक्सेल फॉर्मूला" धोखा पत्र अनलॉक करें!

यह आपको हमारे समाचार पत्र पर हस्ताक्षर करेगा

एक बार जब आप जानते हैं कि लुकअप का उपयोग करके एक्सेल में कैसे खोज करें, तो इससे कोई फर्क नहीं पड़ता कि आपकी स्प्रैडशीट कितनी बड़ी हो जाती है, आप हमेशा एक्सेल में कुछ खोज पाएंगे!

1. VLOOKUP फ़ंक्शन

यह फ़ंक्शन आपको एक कॉलम और एक मूल्य निर्दिष्ट करने की अनुमति देता है, और एक अलग कॉलम की संबंधित पंक्ति से एक मान लौटाएगा (यदि यह समझ में नहीं आता है, तो यह एक पल में स्पष्ट हो जाएगा)। दो उदाहरण जहां आप ऐसा कर सकते हैं वे किसी कर्मचारी के अंतिम नाम को उनके कर्मचारी नंबर से देख रहे हैं या अंतिम नाम निर्दिष्ट करके फोन नंबर ढूंढ रहे हैं।

यहाँ फ़ंक्शन का सिंटैक्स है:

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

• [लुकअप_वेल्यू] आपके पास पहले से मौजूद जानकारी का एक टुकड़ा है। उदाहरण के लिए, यदि आपको यह जानना है कि शहर किस राज्य में है, तो यह शहर का नाम होगा।
• [table_array] आपको उन कोशिकाओं को निर्दिष्ट करने देता है जिसमें फ़ंक्शन लुकअप और रिटर्न मानों के लिए दिखेगा। अपनी सीमा का चयन करते समय, सुनिश्चित करें कि आपके सरणी में शामिल पहला कॉलम वह है जो आपके लुकअप मान को शामिल करेगा!
• [col_index_num] उस कॉलम की संख्या है जिसमें रिटर्न वैल्यू होती है।
• [range_lookup] एक वैकल्पिक तर्क है, और 1 या 0. लेता है यदि आप 1 दर्ज करते हैं या इस तर्क को छोड़ देते हैं, तो फ़ंक्शन आपके द्वारा दर्ज किए गए मान या अगले-निम्नतम संख्या के लिए दिखता है। इसलिए नीचे की छवि में, 652 के SAT स्कोर की तलाश में एक VLOOKUP 646 लौटाएगा, क्योंकि यह उस सूची में सबसे निकटतम संख्या है जो 652 से कम है, और [range_lookup] 1 के लिए डिफ़ॉल्ट है।

आइए एक नज़र डालते हैं कि आप इसका उपयोग कैसे कर सकते हैं। इस स्प्रेडशीट में आईडी नंबर, पहला और अंतिम नाम, शहर, राज्य और SAT स्कोर शामिल हैं। मान लें कि आप अंतिम नाम "सर्दियां" वाले व्यक्ति का SAT स्कोर ढूंढना चाहते हैं, VLOOKUP इसे आसान बनाता है। यहां वह सूत्र है जिसका आप उपयोग करेंगे:

 =VLOOKUP("Winters", C2:F101, 4, 0) 

क्योंकि एसएटी स्कोर अंतिम नाम कॉलम से चौथा कॉलम है, 4 कॉलम इंडेक्स तर्क है। ध्यान दें कि जब आप टेक्स्ट खोज रहे हों, तो [range_lookup] को 0 पर सेट करना एक अच्छा विचार है। इसके बिना, आप खराब परिणाम प्राप्त कर सकते हैं।

यहाँ परिणाम है:

यह 651 पर लौटा, कैनेडी विंटर्स नाम के छात्र से संबंधित सैट स्कोर, जो 92 की पंक्ति में है (ऊपर इनसेट में प्रदर्शित)। यह नाम की तलाश में स्क्रॉल करने में बहुत अधिक समय लेगा, क्योंकि यह सिंटैक्स को जल्दी से टाइप करने के लिए किया था!

VLOOKUP पर नोट्स

जब आप VLOOKUP का उपयोग कर रहे हैं तो कुछ चीजें याद रखना अच्छा है। सुनिश्चित करें कि आपकी सीमा में पहला कॉलम वह है जिसमें आपका लुकअप मान शामिल है। यदि यह पहले कॉलम में नहीं है, तो फ़ंक्शन गलत परिणाम देगा। यदि आपके कॉलम अच्छी तरह से व्यवस्थित हैं, तो यह समस्या नहीं होनी चाहिए।

इसके अलावा, ध्यान रखें कि VLOOKUP केवल एक मूल्य लौटाएगा। यदि आपने "जॉर्जिया" को लुकअप वैल्यू के रूप में उपयोग किया है, तो यह जॉर्जिया से पहले छात्र का स्कोर लौटा देगा, और इस बात का कोई संकेत नहीं दिया होगा कि वास्तव में जॉर्जिया के दो छात्र हैं।

2. HLOOKUP फ़ंक्शन

जहां VLOOKUP दूसरे कॉलम में संबंधित मान पाता है, वहीं HLOOKUP एक ​​अलग पंक्ति में संबंधित मान पाता है। क्योंकि कॉलम हेडिंग के माध्यम से स्कैन करना आम तौर पर तब तक आसान होता है जब तक आप सही नहीं पाते हैं और जो आप खोज रहे हैं उसे खोजने के लिए एक फिल्टर का उपयोग करें, HLOOKUP का उपयोग तब किया जाता है जब आपके पास वास्तव में बड़े स्प्रेडशीट हों या आप उन मानों के साथ काम कर रहे हों जो समय के अनुसार व्यवस्थित होते हैं। ।

यहाँ फ़ंक्शन का सिंटैक्स है:

 =HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup]) 

• [लुकअप_वेल्यू] वह मूल्य है जिसे आप जानते हैं और इसके लिए संबंधित मूल्य खोजना चाहते हैं।
• [table_array] वह कोशिकाएं हैं जिनमें आप खोजना चाहते हैं।
• [row_index_num] उस पंक्ति को निर्दिष्ट करता है जो रिटर्न मान से आएगा।
• [range_lookup] VLOOKUP की तरह ही है, संभव होने पर निकटतम मान प्राप्त करने के लिए इसे खाली छोड़ दें, या केवल सटीक मैचों के लिए 0 दर्ज करें।

इस स्प्रेडशीट में प्रत्येक राज्य के लिए एक पंक्ति है, साथ ही वर्ष 2000-2014 में SAT स्कोर। आप 2013 में मिनेसोटा में औसत स्कोर खोजने के लिए HLOOKUP का उपयोग कर सकते हैं। यहां बताया गया है कि हम इसे कैसे करेंगे:

 =HLOOKUP(2013, A1:P51, 24) 

जैसा कि आप नीचे की छवि में देख सकते हैं, स्कोर वापस आ गया है:

मिनेसोटन्स ने 2013 में 1014 का स्कोर औसत किया। ध्यान दें कि 2013 उद्धरणों में नहीं है क्योंकि यह एक संख्या है, और एक स्ट्रिंग नहीं है। इसके अलावा, 24 मिनेसोटा से 24 वीं पंक्ति में आता है।

HLOOKUP पर नोट्स

VLOOKUP के साथ, लुकअप मान आपके तालिका सरणी की पहली पंक्ति में होना चाहिए। यह शायद ही कभी HLOOKUP के साथ कोई समस्या है, क्योंकि आप आमतौर पर लुकअप मान के लिए एक कॉलम शीर्षक का उपयोग कर रहे होंगे। HLOOKUP भी केवल एक ही मूल्य लौटाता है।

3-4। INDEX और MATCH फ़ंक्शंस

INDEX और MATCH दो अलग-अलग कार्य हैं, लेकिन जब वे एक साथ उपयोग किए जाते हैं तो वे एक बड़ी स्प्रेडशीट को बहुत तेजी से खोज सकते हैं। दोनों कार्यों में कमियां हैं, लेकिन उन्हें जोड़कर हम दोनों की ताकत का निर्माण करेंगे।

पहले, हालांकि, दोनों कार्यों का वाक्यविन्यास:

 =INDEX([array], [row_number], [column_number]) 

• [array] वह सरणी है जिसमें आप खोज करेंगे।
• [row_number] और [column_number] का उपयोग आपकी खोज को कम करने के लिए किया जा सकता है (हम एक क्षण में उस पर एक नज़र डालेंगे।)

 =MATCH([lookup_value], [lookup_array], [match_type]) 

• [लुकअप_वेल्यू] एक खोज शब्द है जो एक स्ट्रिंग या एक संख्या हो सकती है।
• [लुकअप_अरे] वह सरणी है जिसमें Microsoft Excel खोज शब्द के लिए दिखेगा।
• [match_type] एक वैकल्पिक तर्क है जो 1, 0, या -1 हो सकता है। 1 आपके खोज शब्द से सबसे बड़ा या उससे छोटा मान लौटाएगा। 0 केवल आपका सटीक शब्द लौटाएगा, और -1 आपके खोज शब्द से अधिक या उसके बराबर छोटा मूल्य लौटाएगा।

यह स्पष्ट नहीं हो सकता है कि हम इन दो कार्यों को एक साथ कैसे उपयोग करने जा रहे हैं, इसलिए मैं इसे यहां रखूंगा। MATCH एक खोज शब्द लेता है और एक सेल संदर्भ देता है। नीचे दी गई छवि में, आप देख सकते हैं कि स्तंभ एफ में मूल्य 646 की खोज में, MATCH 4 रिटर्न करता है।

दूसरी ओर, INDEX इसके विपरीत होता है: यह एक सेल संदर्भ लेता है और इसमें मान लौटाता है। आप यहां देख सकते हैं कि, जब सिटी कॉलम की छठी सेल को वापस करने के लिए कहा गया था, तो INDEX पंक्ति 6 ​​के लिए "एंकरेज" लौटाता है।

हम जो करने जा रहे हैं, वह दोनों को मिलाता है ताकि MATCH एक सेल संदर्भ देता है और INDEX उस संदर्भ का उपयोग सेल में मान देखने के लिए करता है। मान लीजिए कि आपको याद है कि एक छात्र था जिसका अंतिम नाम वाटर्स था, और आप देखना चाहते हैं कि इस छात्र का स्कोर क्या था। यहां वह सूत्र है जिसका हम उपयोग करेंगे:

 =INDEX(F:F, MATCH("Waters", C:C, 0)) 

आप देखेंगे कि यहाँ मिलान प्रकार 0 पर सेट है। जब आप एक स्ट्रिंग की तलाश कर रहे हैं, तो यही आप उपयोग करना चाहते हैं। जब हम उस फ़ंक्शन को चलाते हैं तो हमें यह मिलता है:

जैसा कि आप इनसेट से देख सकते हैं, ओवेन वाटर्स ने 1720 स्कोर किया, वह संख्या जो फ़ंक्शन को चलाने पर दिखाई देती है। यह सब तब उपयोगी नहीं लग सकता है जब आप बस कुछ कॉलम देख सकते हैं, लेकिन कल्पना करें कि यदि आप इसे किसी बड़े डेटाबेस स्प्रेडशीट एक्सेल बनाम पर 50 बार करना चाहते हैं तो कितना समय बचा सकते हैं। एक्सेस - क्या स्प्रेडशीट डाटाबेस को रिप्लेस कर सकती है? एक्सेल बनाम। एक्सेस - क्या स्प्रेडशीट डाटाबेस को रिप्लेस कर सकती है? डेटा प्रबंधित करने के लिए आपको किस टूल का उपयोग करना चाहिए? एक्सेस और एक्सेल दोनों में डेटा फ़िल्टरिंग, कॉलेशन और क्वेरी की सुविधा है। हम आपको दिखाएंगे कि आपकी आवश्यकताओं के लिए सबसे उपयुक्त कौन सा है। और पढ़ें जिसमें कई सौ कॉलम शामिल हैं!

एक्सेल खोज शुरू करते हैं

Microsoft Excel में डेटा में हेरफेर करने के लिए बहुत शक्तिशाली कार्य हैं, और ऊपर सूचीबद्ध चार सतह को खरोंचते हैं। उनका उपयोग करना सीखना आपके जीवन को बहुत आसान बना देगा।

यदि आप वास्तव में Microsoft Excel में महारत हासिल करना चाहते हैं, तो आप वास्तव में आवश्यक Excel Cheat Sheet को रखने से लाभ उठा सकते हैं। आवश्यक Microsoft Excel फ़ार्मुले और फ़ंक्शंस Cheat Sheet आवश्यक Microsoft Excel फ़ार्मुले और फ़ंक्शंस Cheat Sheet एक शॉर्टकट लेने के लिए इस Excel फ़ार्मुलों को धोखा देने वाली शीट डाउनलोड करें दुनिया का पसंदीदा स्प्रेडशीट कार्यक्रम। और पढ़ें हाथ के करीब!

चित्र साभार: सीको / शटरस्टॉक

इसके बारे में अधिक जानें: Microsoft Excel, खोज ट्रिक्स, स्प्रेडशीट।