Excel में सरल डेटाबेस क्वेरीज़ के लिए INDEX और MATCH का उपयोग करें

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

VLOOKUP (लंबवत) और HLOOKUP (क्षैतिज) विशिष्ट जानकारी के लिए डेटाबेस से पूछने के लिए उपलब्ध एकमात्र कार्य थे। उदाहरण के लिए, आप उन सभी रिकॉर्ड्स को ढूंढने और निकालने के लिए क्वेरी कर सकते हैं जिनमें बिक्री $ 1000 से अधिक है लेकिन $ 5000 से कम है- लेकिन केवल फ्लैट फाइलों (केवल एक डेटाबेस मैट्रिक्स) पर।

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

[आगे पढ़ने: आपके नए पीसी को इन 15 मुफ़्त, उत्कृष्ट कार्यक्रमों की आवश्यकता है]

इंडेक्स, मैच, और इंडेक्स मैच

एक्सेल में, INDEX फ़ंक्शन किसी आइटम को किसी विशिष्ट स्थिति (सूची, तालिका, डेटाबेस में) देता है।

MATCH फ़ंक्शन किसी मान की स्थिति देता है (सूची में , टेबल, डेटाबेस)। और, INDEX-MATCH फ़ंक्शंस एक साथ एक टेबल से डेटा निकालने के लिए उपयोग करते हैं।

INDEX फ़ंक्शन के लिए सिंटैक्स है: INDEX (सरणी, row_num, [column_num])। सरणी उन कक्षों की श्रृंखला है जिनके साथ आप काम कर रहे हैं। Row_num, स्पष्ट रूप से, उस श्रेणी में पंक्ति संख्या है जिसमें वह डेटा शामिल है जिसे आप ढूंढ रहे हैं। Column_num उस श्रेणी में कॉलम नंबर है जिसमें वह डेटा शामिल है जिसे आप ढूंढ रहे हैं। INDEX सूत्र कॉलम अक्षरों को नहीं पहचानता है, इसलिए आपको संख्याओं का उपयोग करना चाहिए (बाएं से गिनती)।

MATCH फ़ंक्शन के लिए वाक्यविन्यास है: MATCH (lookup_value, lookup_array, [match_type])। Lookup_value वह संख्या या टेक्स्ट है जिसे आप ढूंढ रहे हैं, जो एक मान, लॉजिकल वैल्यू या सेल संदर्भ हो सकता है। Lookup_array उन कक्षों की श्रृंखला है जिनके साथ आप काम कर रहे हैं। Match_type MATCH फ़ंक्शन को निर्धारित करता है-वह एक सटीक मिलान या निकटतम मिलान है।

ए। INDEX फ़ंक्शन

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

1। अपने प्रश्नों (यानी, फ़ंक्शंस और परिणाम) के लिए एक स्थान (सेल या कक्षों की श्रेणी) का चयन करें, फिर अपने कर्सर को उस सेल पर ले जाएं। उदाहरण के लिए: पंक्ति 18 पर कोई भी सेल

2। INDEX फ़ंक्शन (बराबर चिह्न से पहले), साथ ही एक उद्घाटन कोष्ठक दर्ज करें, फिर डेटाबेस / तालिका श्रेणी को हाइलाइट करें (या टाइप करें): = INDEX (ए 2: I16

नोट: यदि आप एक पूर्ण संदर्भ चाहते हैं (जो , इस मामले में, सूत्र का हार्ड-कोडिंग का मतलब है, जब इसकी प्रतिलिपि बनाई जाती है, तो सीमा बदल नहीं जाती है), प्रत्येक सेल संदर्भ के बाद एक बार F4 दबाएं। आप सीमा को हाइलाइट भी कर सकते हैं: पूर्ण श्रेणी का चयन करने के बाद बस F4 दबाएं , और पूर्ण निरपेक्ष संदर्भ प्रतीक जोड़े गए हैं।

3। अगला, तर्कों को अलग करने के लिए एक अल्पविराम दर्ज करें (यानी, अलग सूत्र टुकड़े); फिर पंक्ति संख्या और अल्पविराम दर्ज करें, उसके बाद स्तंभ संख्या (हां , यह एक संख्या होना चाहिए, न कि सामान्य कॉलम अक्षर) और दाएं कोष्ठक (या केवल एंटर दबाएं और एक्सेल को आपके लिए अंतिम संश्लेषण जोड़ें)। पूरा सूत्र इस तरह दिखता है: = INDEX ($ A $ 2: $ I $ 16 , 15,2)।

नोट: पंक्ति संख्या पहले श्रेणी के साथ शुरू होती है, न कि स्प्रेडशीट पर पहला नंबर। उदाहरण के लिए, पूर्व संध्या n हालांकि कैवेलियारिया समुद्री डाकू जहाज एक्सेल पंक्ति 16 पर है, यह वास्तव में हमारे सूत्र में पंक्ति 15 है क्योंकि हमारी सीमा ए 2 पर शुरू होती है और I16 के माध्यम से जाती है। यदि ए 2 पंक्ति 1 है, तो ए 16 पंक्ति 15 है)।

4. ध्यान दें कि जहाज नॉरिंगटन की तलाश एक युद्ध स्लूप है।

जेडी सार्टैन

अपने डेटाबेस में विशिष्ट जानकारी का पता लगाने के लिए इंडेक्स फ़ंक्शन का उपयोग करें।

बी। INDEX श्रेणी

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

1। ए 2 पर जाएं और श्रेणी ए 2 से I16 तक हाइलाइट करें।

2। सूत्र टैब से, परिभाषित नाम समूह से नाम परिभाषित करें का चयन करें।

3। पॉपअप संवाद बॉक्स में, नाम फ़ील्ड बॉक्स में अपनी सीमा के लिए एक नाम दर्ज करें।

4। इसके बाद, स्कोप दर्ज करें (जहां सीमा स्थित है), जो वर्कबुक में वर्कशीट या वर्कशीट्स में से एक है।

5। यदि आवश्यक हो, तो एक टिप्पणी दर्ज करें।

6। और आखिरकार, सत्यापित करें कि क्षेत्र को संदर्भित करने के लिए सही नाम और सीमा प्रदर्शित होती है, फिर ठीक ।

7 क्लिक करें। यदि आप यह सत्यापित करना चाहते हैं कि आपकी श्रेणी वास्तव में एक्सेल में सहेजी गई है, तो इस छोटे से परीक्षण को आजमाएं: Ctrl + G (GoTo कमांड) दबाएं। GoTo संवाद में जहाजों का चयन करें, फिर ठीक क्लिक करें, और एक्सेल श्रेणी A2: I16 को फिर से हाइलाइट करता है।

एक श्रेणी को परिभाषित और सहेजने के लिए

सी। इंडेक्स एसयूएम और औसत सूत्रों के साथ

नॉरिंगटन बेड़े की युद्ध क्षमताओं का आकलन कर रहा है। सबसे पहले वह जानना चाहता है कि समुद्री डाकू कितने तोपों, प्रति जहाज तोपों की औसत संख्या, और उन सभी समुद्री डाकू जहाजों का प्रबंधन करने वाले दल की कुल संख्या। वह निम्नलिखित सूत्रों में प्रवेश करता है:

1। = एसयूएम (इंडेक्स (जहाज, 8)) 334 के बराबर है, तोपों की कुल संख्या, और

2। = औसत (INDEX (जहाज, 8)) 22.27, या प्रति जहाज लगभग 22.27 तोपों के बराबर है।

3। = एसयूएम (इंडेक्स (जहाज, 7)) 2350 के बराबर है, सभी जहाजों पर सभी चालक दल की कुल संख्या।

जहाजों और संख्या 8 के बीच एक अल्पविराम, अंतरिक्ष, अल्पविराम क्यों है, और इन संख्याओं का क्या अर्थ है? जहाज रेंज (एक अल्पविराम के बाद) है, पंक्ति तर्क खाली (या एक स्थान) है क्योंकि नॉरिंगटन सभी पंक्तियों को चाहता है, और 8 8 वें कॉलम (जो स्तंभ एच, तोप हैं) का प्रतिनिधित्व करता है।

कुछ पूछ सकते हैं , क्यों न केवल उन स्तंभों के नीचे SUM और / या औसत सूत्र दर्ज करें? इस छोटी स्प्रेडशीट में, हाँ, यह उतना ही आसान होगा। लेकिन यदि स्प्रेडशीट में 5000 पंक्तियां और 300 कॉलम हैं, तो आप इंडेक्स

जेडी सार्टैन

03 INDEX सूत्रों का उपयोग एसयूएम और औसत का उपयोग करना चाहेंगे।

एक बार रेंज का नाम देने के बाद, नॉरिंगटन एक खाली स्प्रेडशीट खोल सकता है यह वही कार्यपुस्तिका और स्तंभ बी में उन प्रश्नों को परिभाषित करने वाले विवरण के साथ कॉलम बी (जो सूत्रों के बजाय परिणाम दिखाती है) में उनके प्रश्न (सूत्र) लिखें (नोट: कॉलम सी कॉलम बी में मौजूद वास्तविक सूत्र दिखाता है)।

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

जेडी सार्टैन

04 कमोडोर जेम्स नॉरिंगटन की समुद्री डाकू जहाज की जानकारी / क्वेरी शीट।

डी। MAXEX MAXCH MAX

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

1। = MAX (INDEX (जहाज, 7)) 300 के बराबर है, जहाजों में से एक पर समुद्री डाकू की सबसे अधिक संख्या

2। = INDEX ($ ए $ 2: $ ए $ 16, MATCH (MAX (जहाज), $ जी $ 2: जी $ 16, 0)) रॉयल जेम्स, जहाज

3 पर सबसे समुद्री डाकू के साथ जहाज के बराबर है। = INDEX ($ एफ $ 2: $ एफ $ 16, MATCH (MAX (जहाज), $ जी $ 2: जी $ 16, 0)) रॉड जेम्स के कैप्टन 300 के एक समुद्री डाकू दल के साथ स्टेनलेस बोनेट के बराबर है

जेडी सार्टैन

अपने डेटाबेस से विशिष्ट जानकारी पुनर्प्राप्त करने के लिए INDEX-MATCH और MAX का उपयोग करें।