Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office
Computers

VLOOKUP, MATCH और INDEX का उपयोग करते हुए स्प्रेडशीट(Spreadsheet) से डेटा कैसे निकालें

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Hindi (हिंदी) translation by Shubham Sharma (you can also view the original English article)

जब आपको एक तालिका से डेटा के एक स्तंभ (Column) को खोजने और निकालने की आवश्यकता होती है, तो इसे दूसरे में रखें, VLOOKUP फ़ंक्शन का उपयोग करें। यह फ़ंक्शन विंडोज और मैक में एक्सेल के किसी भी संस्करण में काम करता है, और गूगल शीट्स में भी। यह आपको एक तालिका में डेटा को कुछ पहचानकर्ता का उपयोग करने की अनुमति देता है जिसमें किसी अन्य तालिका के साथ समानता है। दो टेबल अलग-अलग चादरें या अलग-अलग कार्यपुस्तिकाओं पर भी हो सकते हैं। एक HLOOKUP फ़ंक्शन भी है, जो एक ही काम करता है, लेकिन डेटा के साथ पंक्तियों में क्षैतिज रूप से व्यवस्था की जाती है।

MATCH और INDEX फ़ंक्शंस का उपयोग करना अच्छा है जब आप विशिष्ट डेटा के स्थान, जैसे कॉलम या पंक्ति, जिसमें किसी व्यक्ति का नाम होता है, से संबंधित हो।

प्रीमियम विकल्प

एक्सेल फ़ंक्शंस में आने से पहले, क्या आप जानते हैं कि एनोवाटो मार्केट में एक्सेल लिपियों और प्लगइन्स की एक श्रेणी होती है जो आपको उन्नत फ़ंक्शन करते हैं?

उदाहरण के लिए, आप निम्न कर सकते हैं:

Excel scripts and plugins on Envato Market
एन्वाटो बाजार पर एक्सेल स्क्रिप्ट और प्लगइन्स

स्क्रीनकास्ट

यदि आप इस ट्यूटोरियल के साथ अपनी खुद की एक्सेल फ़ाइल का उपयोग करना चाहते हैं, तो आप ऐसा कर सकते हैं। या यदि आप चाहें, तो इस ट्यूटोरियल के लिए शामिल ज़िप फ़ाइल डाउनलोड करें, जिसमें एक नमूना वर्कबुक है जिसे vlookup example.xlsx कहा जाता है।

VLOOKUP का उपयोग करना

जब VLOOKUP पहचानकर्ता को खोजता है जो आप स्रोत डेटा में निर्दिष्ट करते हैं, तो उसे उस पंक्ति में कोई भी सेल मिल सकता है और आपको जानकारी वापस कर सकती है। ध्यान दें कि स्रोत डेटा में, पहचानकर्ता (identifier) तालिका के पहले स्तंभ में होना चाहिए।

unique identifiers for vlookup
एक अद्वितीय पहचानकर्ता एक सीरियल नंबर की तरह होना चाहिए, जहां समान तालिका में कोई दो समान नहीं है।

सिंटेक्स (Syntax)

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

=VLOOKUP(lookup value, table range, column number, [true/false])

यहां ये तर्क का मतलब क्या हैं:

  • लूकप वैल्यू (Lookup Value) एक विशिष्ट पहचानकर्ता वाला सेल।
  • तालिका श्रेणी (Table range) पहले कॉलम में पहचानकर्ता वाले कोशिकाओं की श्रेणी, दूसरे कॉलम में शेष डेटा के बाद।
  • कॉलम संख्या (Column number) उस कॉलम की संख्या जिसके पास डेटा है जिसे आप ढूंढ रहे हैं। उस स्तंभ पत्र के साथ भ्रमित न करें। उपरोक्त उदाहरण में, राज्यों में स्तंभ 4 में हैं।
  • सही/गलत (True/False) यह तर्क वैकल्पिक है।  सही मतलब है कि अनुमानित मैच स्वीकार्य है, और गलत का अर्थ है कि केवल एक सटीक मिलान स्वीकार्य है।

हम उपर्युक्त चित्र में तालिका से बिक्री की मात्रा खोजना चाहते हैं, इसलिए हम इन तर्कों का उपयोग करते हैं:

syntax of vlookup
VLOOKUP फ़ंक्शन का सिंटैक्स

एक पूर्ण संदर्भ बनाने के लिए एक रेंज नाम को परिभाषित करें

Vlookup example.xlsx में, विक्रय राशि कार्यपत्रक को देखें। हम बी 5 में सूत्र डालेंगे, फिर शीट के नीचे फार्मूले को कॉपी करने के लिए ऑटोफिल सुविधा का उपयोग करें। इसका अर्थ है कि सूत्र में तालिका श्रेणी का एक पूर्ण संदर्भ होना चाहिए। ऐसा करने का एक अच्छा तरीका तालिका रेंज के लिए एक नाम को परिभाषित करना है।

एक्सेल(Excel) में एक रेंज नाम को परिभाषित करना

  1. सूत्र में प्रवेश करने से पहले, स्रोत डेटा वर्कशीट पर जाएं।
  2. H203 के माध्यम से सभी कक्षों को ए4 (ऑर्डर # कॉलम के लिए हेडर) से चुनें। ऐसा करने का एक त्वरित तरीका A4 पर क्लिक करना है, फिर Ctrl-Shift-End (Mac पर Command-Shift-End) दबाएं।
  3. कॉलम के ऊपर नाम बॉक्स के अंदर क्लिक करें (नाम बॉक्स अब A4 प्रदर्शित करता है)।
  4. डेटा टाइप करें, फिर Enter दबाएं।
  5. अब आप नाम $A$4:$H$203. के बजाय सूत्र में नाम डेटा का उपयोग कर सकते हैं
Name box displaying a range name
नाम बॉक्स आमतौर पर वर्तमान सेल पता प्रदर्शित करता है। इसके अंदर क्लिक करें और किसी श्रेणी को परिभाषित करने के लिए एक नाम लिखें।

गूगल शीट्स(Google Sheets) में एक रेंज नाम को परिभाषित करना।

गूगल शीट्स में, नाम परिभाषित करना थोड़ा अलग है।

  1. अपने स्रोत डेटा के पहले कॉलम हेडर पर क्लिक करें, फिर Ctrl-Shift-Right Arrow (मैक पर Command-Shift-Right Arrow) दबाएं। वह कॉलम हैडर की पंक्ति का चयन करता है।
  2. Ctrl-Shift-Down Arrow दबाएं (मैक पर Command-Shift-Down Arrow)। वह वास्तविक डेटा का चयन करता है।
  3. डेटा मेनू पर क्लिक करें, फिर नामांकित और सुरक्षित श्रेणियों का चयन करें।
  4. नाम और संरक्षित श्रेणियां बॉक्स में, डेटा टाइप करें, फिर पूर्ण हो गया क्लिक करें।
Data menu in Google Sheets
गूगल शीट्स में श्रेणी नाम को परिभाषित करना

फ़ॉर्मूला दर्ज करना

सूत्र में प्रवेश करने के लिए, बिक्री राशि कार्यपत्रक पर जाएं और B5 पर क्लिक करें।

सूत्र दर्ज करें:

=VLOOKUP(A5,data,8,FALSE)

एंटर(Enter) दबाए।

entering the vlookup function
VLOOKUP समारोह में प्रवेश करना

परिणाम 40 होना चाहिए। कॉलम के नीचे मानों को भरने के लिए, यदि आवश्यक हो तो B5 पर वापस जाएं। माउस के निचले दाएं कोने में स्वत: भरण (AutoFill) डॉट पर माउस पॉइंटर रखें, ताकि माउस पॉइंटर एक क्रॉस बालों बन जाए।

mouse pointer on the autofill dot
जब आप किसी सेल के निचले दाएं कोने में डॉट पर माउस पॉइंटर डालते हैं, तो यह एक ऑटोफिल क्रॉस हेयर बन जाता है।

कॉलम नीचे मूल्यों को भरने के लिए डबल-क्लिक करें।

worksheet with data after using autofill
सूत्र के नीचे कॉलम को कॉपी करने के लिए ऑटोफिल क्रॉस हेयर पर डबल-क्लिक करें

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

मैच (MATCH) का उपयोग करना

MATCH फ़ंक्शन आपको डेटा के मान वापस नहीं करता है; आप वह मान प्रदान करते हैं जिसे आप ढूंढ रहे हैं, और फ़ंक्शन उस मान की स्थिति देता है। यह पूछने की तरह है कि # 135 मुख्य सड़क कहां है, और यह जवाब दे रहा है कि सड़क के नीचे 4th इमारत है।

सिंटेक्स

मैच(MATCH) फ़ंक्शन का सिंटैक्स है:

=MATCH(lookup value, table range, [match type])

तर्क हैं:

  • लूकप वैल्यू (Lookup Value) -  एक विशिष्ट पहचानकर्ता वाला सेल(Cell)।
  • तालिका श्रेणी (Table range) आपके द्वारा खोज रहे सेल की श्रेणी।
  • मैच के प्रकार (Match type)- वैकल्पिक ऐसा कैसे होता है जिसे आप चाहते हैं कि आप कितने मैच चाहते हैं, इस प्रकार है:

अगला उच्चतम मूल्य

-1

मान अवरोही क्रम में होना चाहिए।

लक्ष्य मूल्य

0

मान(Value) किसी भी क्रम में हो सकते हैं।

अगले न्यूनतम मूल्य

1

डिफ़ॉल्ट प्रकार मान आरोही क्रम में होना चाहिए।

VLOOKUP फ़ंक्शन के साथ, यदि आप किसी श्रेणी नाम को लागू करते हैं, तो संभवतः MATCH फ़ंक्शन का उपयोग करना आसान होगा। स्रोत डेटा शीट पर जाएं, नीचे B4 (ऑर्डर # के लिए कॉलम हैडर) का चयन करें, स्तंभ ए के ऊपर स्थित नाम बॉक्स पर क्लिक करें, और इसे order_number कॉल करें। ध्यान दें कि मूल्य आरोही क्रम में हैं।

name box with one column defined
एक नामांकित श्रेणी केवल एक स्तंभ(Column), सिर्फ एक पंक्ति या सिर्फ एक कक्ष भी हो सकती है

वर्कशीट के मैच(MATCH) टैब पर जाएं। बी5 में, MATCH फ़ंक्शन दर्ज करें:

=MATCH(A5,order_number,1)

entering the match function
मैच फ़ंक्शन में प्रवेश कर रहे है

यदि आप किसी श्रेणी नाम को परिभाषित नहीं करते हैं, तो आप फ़ंक्शन को इस रूप में लिखते हैं:

=MATCH(A5,'Source Data'!A5:A203,0)

किसी भी तरह, आप देख सकते हैं कि यह 14वें स्थान पर है (इसे 13वां क्रम बनाकर)।

completed match function
मैच(MATCH) फ़ंक्शन के परिणाम

INDEX का उपयोग करना

INDEX फ़ंक्शन MATCH फंक्शन के विपरीत है और यह VLOOKUP के समान है। आप फ़ंक्शन को बताते हैं कि डेटा की पंक्ति और स्तंभ क्या आप चाहते हैं, और यह आपको बताता है कि सेल(Cell) में क्या है।

सिंटेक्स

इंडेक्स फ़ंक्शन का सिंटैक्स है:

=INDEX(data range, row number, [column number])

तर्क हैं:

  • डेटा रेंज़(Data range) - दूसरे दो फ़ंक्शन की तरह, यह डेटा की तालिका है।
  • पंक्ति नंबर(Row number) -  डेटा की पंक्ति संख्या, जो कार्यपत्रक की पंक्ति जरूरी नहीं है। अगर तालिका सीमा पत्र की पंक्ति 10 पर शुरू होती है, तो वह पंक्ति # 1 है।
  • कॉलम संख्या - डेटा श्रेणी का स्तंभ संख्या । यदि श्रेणी स्तंभ ई पर शुरू होती है, तो वह कॉलम # 1 है।

एक्सेल(Excel) के प्रलेखन(Documentation) आपको बताएंगे कि कॉलम संख्या(Column number) तर्क वैकल्पिक है, लेकिन पंक्ति संख्या वैकल्पिक की तरह है, बहुत। यदि तालिका सीमा में केवल एक पंक्ति या एक स्तंभ है, तो आपको अन्य तर्क का उपयोग करने की आवश्यकता नहीं है।

कार्यपुस्तिका के इंडेक्स शीट पर जाएं और सी6(c6) में क्लिक करें। हम पहले यह देखना चाहते हैं कि तालिका 9 की पंक्ति 3 में क्या शामिल है। सूत्र में, हम उस श्रेणी नाम का उपयोग करेंगे जो हमने पहले बनाया था।

सूत्र दर्ज करें:

=INDEX(data,A6,B6)

entering the index function
इंडेक्स फ़ंक्शन(index function) में प्रवेश

यह एक ग्राहक का अंतिम नाम देता है: स्ट्रेवेल A6 और B6 के मानों को बदलें, और C6 में परिणाम अलग-अलग परिणाम दिखाएंगे (ध्यान दें कि कई पंक्तियों में एक ही राज्य और उत्पाद नाम हैं)।

निष्कर्ष

कार्यपत्रक(worksheet) की एक अन्य कार्यपत्रक(worksheet) को देखने और डेटा को निकालने की क्षमता एक महान साधन है। इस तरह, आपके पास एक पत्रक हो सकता है जिसमें आपको कई उद्देश्यों के लिए आवश्यक सभी डेटा शामिल हैं, फिर विशिष्ट उदाहरणों के लिए आपको क्या आवश्यक है।

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.