Computer >> कंप्यूटर ट्यूटोरियल >  >> सॉफ्टवेयर >> Office

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

एक ही वर्कशीट के भीतर सभी आवश्यक जानकारी न होना काफी आम है। लेकिन, एक पूर्ण डेटाबेस बनाने के लिए, हमें एक्सेल में अन्य शीट में आवश्यक डेटा पुनर्प्राप्ति के लिए खोज करने की आवश्यकता होती है। इस प्रकार की परेशानियों को संभालने के लिए, माइक्रोसॉफ्ट एक्सेल एक बहुमुखी फंक्शन VLOOKUP के साथ आया है; जो उपयोगकर्ताओं को एक्सेल में कई शीट में खोज करने में सक्षम बनाता है। इसलिए VLOOKUP फंक्शन का उपयोग करके, हम आपको 3 तरीके सिखाएंगे जिनका उपयोग आप एक्सेल में कई शीट में आसानी से खोज करने के लिए कर सकते हैं।

अभ्यास वर्कबुक डाउनलोड करें

आपको सलाह दी जाती है कि एक्सेल फ़ाइल डाउनलोड करें और इसके साथ अभ्यास करें।

एक्सेल में कई शीट में खोज करने के 3 तरीके

मान लीजिए, एक किताब की दुकान ऑनलाइन और स्टोर दोनों में किताबें बेचती है। उनके पास ऑनलाइन बिक्री के लिए उपलब्ध किताबों और स्टोर में बिक्री के लिए उपलब्ध किताबों की दो किताब सूचियाँ हैं।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

 

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

इस ट्यूटोरियल में, हम आपको सिखाएंगे कि आप इन दो किताब सूचियों को कैसे जोड़ सकते हैं और 3 अलग-अलग तरीकों का उपयोग करके किताबों की एक नई पूर्ण सूची बना सकते हैं।

1. IFERROR के साथ एक्सेल में कई शीट में खोज करें

एक पूर्ण किताब सूची बनाने के लिए जिसमें ऑनलाइन और स्टोर दोनों में बिक्री के लिए उपलब्ध किताबें हों, हमें "स्टोर" और "ऑनलाइन" दोनों वर्कशीट से जानकारी को संयोजित करने की आवश्यकता है।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके अब खोज करना सीखने के लिए नीचे दिए गए चरणों का पालन करें:

🔗 चरण:

❶ सबसे पहले, फॉर्मूला परिणाम स्टोर करने के लिए सेल C5 चुनें ▶।

❷ उसके बाद, निम्नलिखित फॉर्मूला टाइप करें:

=IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found"))
सेल के अंदर।

❸ फिर ENTER बटन दबाएं।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

❹ अब फिल हैंडल आइकन को पुस्तक नाम कॉलम के अंत तक खींचें।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

बस यही है।

💡 लेखक कॉलम को पूरा करने के लिए, सेल D5 के अंदर फॉर्मूला

=IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,3, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 3, FALSE), "Not found"))
उपयोग करें और चरण 1 से 4 दोहराएं।

␥ फॉर्मूला ब्रेकडाउन

📌 सिंटैक्स: IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, "Not found"))

  • B5 ▶ एक आईडी चुनता है, जो खोज कुंजी के रूप में काम करेगा।
  • Store!$B$5:$D$9 ▶ खोज ऑपरेशन स्टोर वर्कशीट में B5 से D9 की रेंज में आयोजित किया जाता है।
  • Online!$B$5:$D$9 ▶ खोज ऑपरेशन ऑनलाइन वर्कशीट में B5 से D9 की रेंज में आयोजित किया जाता है।
  • 2 ▶ पुस्तक नाम प्राप्त करने के लिए पुस्तक नाम कॉलम को इंगित करता है।
  • FALSE ▶ खोज करते समय सटीक मिलान के लिए इस तर्क का उपयोग किया जाता है।
  • =IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found")) ▶ आईडी नंबर 96 के साथ पुस्तक नाम लौटाता है।

और पढ़ें: खोजें और एकाधिक मान एक सेल में जोड़कर लौटाएं एक्सेल में

2. INDIRECT के साथ एक्सेल VLOOKUP कई शीट

हम IFERROR फंक्शन के बजाय INDIRECT फंक्शन का उपयोग करके कई शीट में खोज कर सकते हैं। लेकिन हमें ध्यान रखना चाहिए कि हालांकि INDIRECT फंक्शन कई शीट में डेटा पुनर्प्राप्त करते समय बहुत अधिक लचीलापन प्रदान करता है, INDIRECT फंक्शन का सिंटैक्स अधिक जटिल है। इसलिए, VLOOKUP फंक्शन के साथ INDIRECT फंक्शन का उपयोग करते समय सावधान रहें। तो, बिना किसी और चर्चा के सीधे चरणों में चलते हैं:

🔗 चरण:

❶ सबसे पहले, फॉर्मूला परिणाम स्टोर करने के लिए सेल C5 चुनें ▶।

❷ उसके बाद, फॉर्मूला टाइप करें

=VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$F$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$F$6&"'!$B5:$B9"),$B5)>0,0))&"'!$B$5:$D$9"),2,0)
सेल के अंदर।

❸ फिर ENTER बटन दबाएं।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

❹ अब फिल हैंडल आइकन को पुस्तक नाम कॉलम के अंत तक खींचें।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

बस यही है।

💡 लेखक कॉलम को पूरा करने के लिए, सेल D5 के अंदर फॉर्मूला

=VLOOKUP($B5,INDIRECT("'"&INDEX($F$5:$F$6,MATCH(TRUE,COUNTIF(INDIRECT("'"&$F$5:$F$6&"'!$B5:$B9"),$B5)>0,0))&"'!$B$5:$D$9"),3,0)
उपयोग करें और चरण 1 से 4 दोहराएं।

␥ फॉर्मूला ब्रेकडाउन

📌 सिंटैक्स: VLOOKUP(lookup_value, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(TRUE, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!lookup_range"), lookup_value)>0), 0)) & "'!table_array"), col_index_num, FALSE)

  • Lookup_value ▶ $B5 ▶ खोज कीवर्ड जिसके आधार पर हम खोज करते हैं।
  • Lookup_sheets ▶ $F$5:$F$6 ▶ उन शीटों की सूची का सेल पता जिनमें हम आमतौर पर डेटा खोजते हैं।
  • Lookup_range ▶ $B5:$B9 ▶ वह रेंज जिसमें हमारा लुकअप मान रहता है।
  • Table_array ▶ $B$5:$D$9 ▶ संपूर्ण डेटा टेबल की रेंज।
  • कॉलम_इंडेक्स_नंबर ▶ 2 ▶ वह कॉलम संख्या जहां से हम अपना वांछित डेटा खींचते हैं।

और पढ़ें: एक्सेल में कई मानदंडों के आधार पर VBA INDEX MATCH (3 तरीके)

3. नेस्टेड IF फंक्शन का उपयोग करके एक्सेल में कई शीट में खोज करें

एक्सेल में कई शीट में खोज करने का एक और तरीका है। जो नेस्टेड IF फंक्शन के साथ ISNA और VLOOKUP फंक्शन का उपयोग है।

यदि आपके पास डेटा खींचने के लिए कुछ डेटाशीट हैं तो आप इस विधि को आसानी से उपयोग कर सकते हैं अन्यथा, आपको इसका उपयोग करने की सलाह नहीं दी जाती है। क्योंकि शीट की संख्या बढ़ने पर यह बहुत जटिल हो जाता है।

वैसे भी, नीचे दिए गए चरणों का पालन करें कि फॉर्मूला कैसे काम करता है। यहां जाएं:

🔗 चरण:

❶ सबसे पहले, फॉर्मूला परिणाम स्टोर करने के लिए सेल C5 चुनें ▶।

❷ उसके बाद, फॉर्मूला टाइप करें

=IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,2,0)),VLOOKUP($B5,Online!$B$5:$D$9,2,0),IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,2,0)),VLOOKUP($B5,Store!$B$5:$D$9,2,0)))
सेल के अंदर।

❸ फिर ENTER बटन दबाएं।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

❹ अब फिल हैंडल आइकन को पुस्तक नाम कॉलम के अंत तक खींचें।

एक्सेल मास्टर करें: कई शीट में डेटा खोजने के 3 सिद्ध तरीके

बस यही है।

💡 लेखक कॉलम को पूरा करने के लिए, सेल D5 के अंदर फॉर्मूला

=IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0),IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)))
उपयोग करें और चरण 1 से 4 दोहराएं।

␥ फॉर्मूला ब्रेकडाउन

📌 सिंटैक्स: IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_number,0)),value_if_true,value_if_false)

  • Lookup_value ▶ $B5 ▶ खोज कीवर्ड जिसके आधार पर हम खोज करते हैं।
  • Table_array ▶ $B$5:$D$9 ▶ संपूर्ण डेटा टेबल की रेंज।
  • कॉलम_इंडेक्स_नंबर ▶ 2 ▶ वह कॉलम संख्या जहां से हम अपना वांछित डेटा खींचते हैं।
  • ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) ▶ आईडी ( $B5 द्वारा संदर्भित) के क्रॉस-मिलान को रेंज $B$5:$D$9. के भीतर खोजता है।
  • IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) ▶ यदि ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) सत्य है तो VLOOKUP($B5,Online!$B$5:$D$9,3,0) का उपयोग करके संबंधित पुस्तक नाम खींचा जाता है।
  • यदि भाग IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) असत्य हो जाता है तो हम IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)) में प्रवेश करते हैं।
  • IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)) ▶ यदि ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)) सत्य हो जाता है तो हम VLOOKUP($B5,Store!$B$5:$D$9,3,0)) का उपयोग करके पुस्तक नाम खींचते हैं।

और पढ़ें: दिनांक सीमा सहित एक्सेल में VLOOKUP कई मानदंड (2 तरीके)

याद रखने योग्य बातें

📌 लुकअप मान हमेशा टेबल ऐरे के भीतर पहले कॉलम में होना चाहिए।

📌 सरणी फॉर्मूला पूरा करने के लिए एक साथ Ctrl + Shift + Enter का उपयोग करें।

📌 फंक्शन के सिंटैक्स के बारे में सावधान रहें।

📌 फॉर्मूला में डेटा रेंज सावधानी से डालें।

निष्कर्ष

समाप्त करने के लिए, हमने एक्सेल में कई शीट में खोज करने के 3 अलग-अलग तरीकों पर चर्चा की है। आपको सलाह दी जाती है कि इस लेख के साथ संलग्न अभ्यास वर्कबुक डाउनलोड करें और इसके साथ सभी विधियों का अभ्यास करें। और नीचे टिप्पणी अनुभाग में कोई भी प्रश्न पूछने में संकोच न करें। हम यथाशीघ्र सभी प्रासंगिक प्रश्नों का उत्तर देने का प्रयास करेंगे।

संबंधित पठन

  • हेल्पर कॉलम के बिना एक्सेल में कई मानदंडों के साथ VLOOKUP (5 तरीके)
  • दिनांक सीमा के लिए कई मानदंडों के साथ INDEX MATCH कैसे उपयोग करें
  • आंशिक टेक्स्ट के लिए कई मानदंडों के साथ INDEX-MATCH एक्सेल में (2 तरीके)
  • एक्सेल में कई मानदंडों के साथ XLOOKUP (4 आसान तरीके)

  1. एक्सेल से सीधे एकाधिक ईमेल भेजें: दो सरल तरीके एक्सेल से सीधे एकाधिक ईमेल भेजें: दो सरल तरीके

    हम अपने एक्सेल वर्कशीट में विभिन्न प्रकार की जानकारी संग्रहीत करते हैं। उदाहरण के लिए, इसमें महत्वपूर्ण लोगों या अन्य कंपनियों के ईमेल पते हो सकते हैं। और जब आवश्यक हो तब हमें उन पतों पर ईमेल भेजने की आवश्यकता हो सकती है। लेकिन, उनमें से प्रत्येक के लिए बार-बार पता कॉपी करना काफी थकाऊ और समय लेने वा

  1. एक्सेल में वर्कशीट्स के बीच सेल डिपेंडेंसीज़ आसानी से ट्रेस करें: 2 सरल तरीके एक्सेल में वर्कशीट्स के बीच सेल डिपेंडेंसीज़ आसानी से ट्रेस करें: 2 सरल तरीके

    एक्सेल में कई बार यूजर्स फॉर्मूला का उपयोग करके वांछित मान दिखाते हैं। इस फॉर्मूला का परिणाम उस विशेष शीट पर अन्य सेल मानों पर या उसी वर्कबुक में दूसरी शीट पर निर्भर करता है। हमारा मुख्य लक्ष्य किसी अन्य वर्कशीट में अन्य सेल्स पर एक सेल के मान की डिपेंडेंसी दिखाना है। इस आर्टिकल में हम आपको दिखाएंगे

  1. एक्सेल गाइड: दो सरल तरीकों से एकाधिक रंगों द्वारा डेटा फ़िल्टर करें एक्सेल गाइड: दो सरल तरीकों से एकाधिक रंगों द्वारा डेटा फ़िल्टर करें

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