आवश्यकता के समय डेटा निकालना किसी भी स्प्रेडशीट का उपयोग करने का प्रमुख कारक है, एक्सेल कोई अपवाद नहीं है। आप डेटा को निकालने के लिए एक्सेल में सारणीबद्ध रूप में संग्रहीत करते हैं। आज हम आपको दिखाने जा रहे हैं कि कई मानदंडों के आधार पर तालिका से डेटा कैसे निकाला जाता है।
सबसे पहले सबसे पहले, आइए डेटासेट के बारे में जानें जो हमारे उदाहरणों का आधार है।
यहां हमारे पास एक टेबल है जिसमें रिलीज वर्ष के साथ शैली और मुख्य अभिनेता के साथ फिल्मों की एक सूची है। इस डेटासेट का उपयोग करके, हम कई मानदंडों के आधार पर डेटा पुनर्प्राप्त करेंगे।
ध्यान दें कि चीजों को सरल रखने के लिए यह एक मूल तालिका है। व्यावहारिक परिदृश्य में, आपका सामना बहुत बड़े और जटिल डेटासेट से हो सकता है।
कार्यपुस्तिका का अभ्यास करें
निम्नलिखित लिंक से अभ्यास कार्यपुस्तिका डाउनलोड करने के लिए आपका स्वागत है।
एकाधिक मानदंडों के आधार पर तालिका से डेटा निकालें
यहां उदाहरण के लिए हम मानदंड के रूप में शैली और अभिनेता का नाम प्रदान करेंगे और इन मानदंडों के आधार पर हम फिल्म का नाम निकालेंगे।
इस खंड में, हम एकल मान लौटाएंगे। मानदंड के आधार पर केवल एक मूल्य प्राप्त किया जाएगा। आइए एक्सप्लोर करें।
मैं. इंडेक्स-मैच ऐरे फॉर्मूला
हम INDEX . के संयोजन का उपयोग कर सकते हैं और मिलान कार्य। इंडेक्स किसी श्रेणी में दिए गए स्थान पर मान लौटाता है। मिलान किसी श्रेणी में लुकअप मान की स्थिति का पता लगाता है।
इन कार्यों को जानने के लिए, इन लेखों को देखें: INDEX, MATCH।
आइए मानदंड मान सेट करें। कुछ समय के लिए थ्रिलर के रूप में शैली और ह्यूग जैकमैन अभिनेता . में फ़ील्ड.
सूत्र निम्नलिखित होगा
=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0)) B4:B19 वह सरणी है जहाँ से हम मान निकालेंगे। और MATCH फ़ंक्शन उस पंक्ति संख्या को सेट करता है जिसे लाया जाना है।
आप देख सकते हैं कि हमने 1 को lookup_value . के रूप में प्रदान किया है MATCH . के भीतर . और lookup_array मापदंड मिलान तर्क के गुणन के माध्यम से उत्पन्न होता है।
$H$4=$C$4:$C$19 . के माध्यम से हमने शैली और $H$5=$D$4:$D$19 की जांच की है अभिनेता के नाम के लिए।
यह गुणन परिणाम की सरणी के बीच 1 पाता है। और पंक्ति संख्या और INDEX . लौटाएं फ़ंक्शन मूवी का नाम लौटाता है।
यह एक सरणी सूत्र है इसलिए हमें CTRL + SHIFT + ENTER . का उपयोग करने की आवश्यकता है इसे निष्पादित करने के लिए।
मानदंड मान बदलें, और आपको अपडेट किए गए मान मिलेंगे।
और पढ़ें: एक्सेल में किसी सेल से विशिष्ट डेटा कैसे निकालें (3 उदाहरण)
द्वितीय। इंडेक्स-मैच नॉन-एरे फॉर्मूला
हम INDEX . को मिलाकर एक गैर-सरणी सूत्र बना सकते हैं और मिलान ।
आइए पहले सूत्र देखें
=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0)) यहां आप देख सकते हैं, हमने कुछ INDEX . का उपयोग किया है वहाँ कार्य करता है। बाहरी INDEX फ़ंक्शन निष्कर्षण कार्य करता है जहां अंदरूनी सूत्र पंक्ति संख्या का पता लगाने में मदद करता है।
आंतरिक INDEX, . के भीतर हमने मानदंड मान की जाँच की है। यहां दो तार्किक संक्रियाओं को INDEX . के भीतर गुणा किया गया है और सरणी संदर्भ के रूप में कार्य किया।
मात्र ENTER सूत्र निष्पादित करेगा।
मानदंड मूल्यों को संशोधित करने के लिए स्वतंत्र महसूस करें, आपको अद्यतन मूल्य मिलेंगे।
और पढ़ें: मानदंडों के आधार पर एक्सेल से डेटा कैसे निकालें (5 तरीके)
III. INDEX-MATCH-IF संयोजन
पहले के खंडों में, हमने शर्तों की जाँच की है और उन्हें एक साथ कार्य करने के लिए मजबूर करने के लिए उन्हें गुणा किया है। हम IF . का उपयोग करके गुणा को छोड़ सकते हैं समारोह।
अगर तार्किक परीक्षण चलाता है और बूलियन मान लौटाता है (TRUE या गलत ) परिणाम के रूप में। समारोह के बारे में जानने के लिए इस आईएफ लेख पर जाएं।
हमारा सूत्र निम्नलिखित होगा
=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))
यहां हमारे पास मिलान करने के लिए दो शर्तें हैं, इसलिए दो IF . वे नेस्टेड के रूप में काम कर रहे हैं IF (एक दूसरे के अंदर)। बाहरी IF फ़ंक्शन पहले एक शर्त की जांच करता है (अनुक्रम के बावजूद) और दूसरी स्थिति (आंतरिक IF) if_true_value . है पहले IF . के लिए ।
आपको CTRL+SHIFT+ENTER . का उपयोग करना होगा सूत्र निष्पादित करने के लिए।
मानदंड मान बदलें, और आप एक अद्यतन मान पाएंगे।
और पढ़ें: एक्सेल शीट से डेटा कैसे निकालें (6 प्रभावी तरीके)
<एच4>चतुर्थ। लुकअप फंक्शनहम लुकअप . का उपयोग कर सकते हैं मानदंड के आधार पर डेटा निकालने के हमारे कार्य को करने के लिए कार्य करता है।
लुकअप फ़ंक्शन किसी श्रेणी में मिलान लुकअप करता है और संबंधित मान लौटाता है। अधिक जानकारी के लिए, माइक्रोसॉफ्ट सपोर्ट पर जाएं साइट।
आइए अब फ़ॉर्मूला देखें
=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))
यहां हमने 2 को lookup_value . के रूप में सेट किया है . और उनके द्वारा विभाजित 1 के रूप में दो तार्किक संक्रियाएं हैं lookup_vector ।
यहां, यह 1 को TRUE/FALSE . की एक सरणी से विभाजित करता है मान ($C$4:$C$19=$H$4 ), और फिर TRUE/FALSE . की दूसरी सरणी द्वारा मान ($D$4:$D$19=$H$5 ) यह 1 या एक #DIV/0! . लौटाएगा त्रुटि।
lookup_value श्रेणी में संख्यात्मक मान के साथ इसका मिलान करने के लिए सूत्र बताता है, एक बार मिलान मिलने पर मान सरणी B4:B19 से प्राप्त किया जाएगा ।
CTRL + SHIFT + ENTER press दबाने की आवश्यकता नहीं है निष्पादित करने के लिए।
यह देखने के लिए मानदंड मान बदलें कि सूत्र अन्य मानों के लिए पूरी तरह से काम कर रहा है या नहीं।
ध्यान दें कि हमने 2 का उपयोग lookup_value . के रूप में किया है . यह 1 से शुरू होने वाली कोई भी संख्या हो सकती है।
और पढ़ें: छवि से एक्सेल में डेटा कैसे निकालें (त्वरित चरणों के साथ)
समान रीडिंग
- टेक्स्ट फ़ाइल को एक्सेल में बदलने के लिए VBA कोड (7 तरीके)
- Excel VBA:किसी वेबसाइट से डेटा स्वचालित रूप से प्राप्त करें (2 तरीके)
- सुरक्षित वेबसाइट से एक्सेल में डेटा कैसे आयात करें (त्वरित चरणों के साथ)
- VLOOKUP के साथ स्वचालित रूप से एक एक्सेल वर्कशीट से दूसरे में डेटा ट्रांसफर करें
- पाइप सीमांकक के साथ एक्सेल को टेक्स्ट फ़ाइल में कैसे बदलें (2 तरीके)
मैं. इंडेक्स-छोटा संयोजन
मानदंड के आधार पर एकाधिक डेटा निकालने के लिए विभिन्न फ़ंक्शन संयोजनों का उपयोग किया जा सकता है। संयोजनों में से एक है INDEX - छोटा संयोजन।
छोटा फ़ंक्शन मान के आधार पर रैंक की गई सूची में उनकी स्थिति के आधार पर मान लौटाता है। अधिक जानने के लिए इसे देखें छोटा लेख।
इन दोनों के साथ, हमें कुछ सहायक कार्यों की आवश्यकता होगी, IF , पंक्ति और IFERROR . अधिक जानकारी के लिए लेख देखें:IF, ROW, IFERROR।
हमारा सूत्र निम्नलिखित होगा
=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
यहां हर समारोह का अपना उद्देश्य होता है। इंडेक्स फ़ंक्शन सरणी से मान लौटाता है B2:B17 और बड़ा छोटा भाग पंक्ति संख्या प्रदान करता है, जिसे प्राप्त किया जाना है।
अगर, छोटा, . के भीतर जांचता है कि मानदंड मेल खाते हैं या नहीं। हमारे पास मिलान करने के लिए दो शर्तें हैं, इसलिए हमने मापदंड की जांच करने के लिए दोनों तार्किक संक्रियाओं को गुणा किया है। और पंक्ति फ़ंक्शन कॉलम की कोशिकाओं पर पुनरावृत्त होता है।
फिर बाहरी ROW छोटा . के लिए k-वें मान को दर्शाता है समारोह। ये फ़ंक्शन एक साथ पंक्ति संख्या लौटाते हैं और INDEX परिणाम देता है।
IFERROR सूत्र से उत्पन्न होने वाली किसी भी त्रुटि से निपटने के लिए। हमने इसे इस तरह से सेट किया है कि त्रुटि के लिए यह एक खाली सेल प्रदान करेगा।
इसे नीचे खींचें, आपको मानदंड से मेल खाने वाले सभी मान मिलेंगे।
और पढ़ें: एक्सेल फॉर्मूला (5 तरीके) का उपयोग करके किसी सूची से डेटा कैसे निकालें
द्वितीय। इंडेक्स-एग्रीगेट संयोजन
कुल एक्सेल में फ़ंक्शन हमें विभिन्न कार्य करने की अनुमति देता है। कई कार्यों के लिए एक समारोह। हम इस फ़ंक्शन का उपयोग कई मानदंडों के आधार पर कई मान वापस करने के लिए कर सकते हैं।
आइए फ़ंक्शन के बारे में थोड़ा जान लें, AGGREGATE फ़ंक्शन AVERAGE, COUNT, MAX, आदि जैसी समग्र गणना देता है।
AGGREGATE . के लिए सिंटैक्स फ़ंक्शन इस प्रकार है:
AGGREGATE(function_number,behavior_options, range) function_number: यह संख्या निर्दिष्ट करती है कि कौन सी गणना की जानी चाहिए।
व्यवहार_विकल्प: इसे नंबर का उपयोग करके सेट करें। यह संख्या दर्शाती है कि फ़ंक्शन कैसे व्यवहार करेगा।
श्रेणी: वह श्रेणी जिसे आप समेकित करना चाहते हैं।
कुल फ़ंक्शन कई कार्य करता है इसलिए इसके भीतर कार्यों की संख्या पूर्वनिर्धारित होती है। हम अक्सर उपयोग किए जाने वाले कुछ फ़ंक्शन नंबर सूचीबद्ध कर रहे हैं
| Function | Function_number |
|---|---|
| औसत | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| छोटा | 15 |
फ़ंक्शन के बारे में और जानने के लिए, माइक्रोसॉफ्ट सपोर्ट . पर जाएं साइट।
अब देखते हैं फॉर्मूला, यह INDEX . का कॉम्बिनेशन होगा और एग्रीगेट ।
=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")
यहां हमने 15 . का उपयोग किया है function_number . के रूप में एग्रीगेट . में . उपरोक्त तालिका से, आप 15 . देख सकते हैं छोटा . के लिए कॉल करता है समारोह संचालन। आप AGGREGATE . का उपयोग करने के अलावा देख सकते हैं (और फ़ंक्शन संख्या और व्यवहार विकल्प संख्या) सूत्र बिल्कुल पहले INDEX . जैसा ही है –छोटा सूत्र।
तंत्र वही है, INDEX सरणी रखता है जो AGGREGATE . पर मिले मिलानों के आधार पर मान लौटाता है सूत्र का भाग।
6 व्यवहार विकल्प के लिए, जो दर्शाता है त्रुटि मानों को अनदेखा करें ।
इसे नीचे खींचें, आपको मानदंड से मेल खाने वाले सभी मान मिलेंगे।
CTRL+SHIFT+ENTER का उपयोग करना याद रखें सूत्र निष्पादित करने के लिए।
और पढ़ें: एकल मानदंड (3 विकल्प) के आधार पर एक्सेल में एकाधिक मान लौटाएं
III. INDEX-MATCH-COUNTIF संयोजन
अनेक मानदंडों के आधार पर अनेक मान लौटाने के लिए, हम INDEX . के संयोजन का उपयोग कर सकते हैं , मिलान , और COUNTIF ।
COUNTIF एक श्रेणी में कक्षों की गणना करता है जो एक ही शर्त को पूरा करता है। इस फ़ंक्शन के बारे में अधिक जानने के लिए, इस लेख पर जाएँ:COUNTIF ।
हमारा सूत्र निम्नलिखित होगा
=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")
MATCH . के भीतर function, we provided 0 as the lookup_array, and for lookup_range we have used the IF portion containing COUNTIF ।
Here, the COUNTIF function excludes any value that has already been fetched. And two IF functions check two conditions. We have added these functions so that they together form the lookup_range ।
The MATCH portion returns the value as long as 0 is found. The value here works as the row number for INDEX ।
Drag it down, you will get all the values that match the criteria.
Read More: How to Extract Data from Cell in Excel (5 Methods)
IV. FILTER Function
If you are using Excel 365, then you can perform the task with a single built-in function called FILTER ।
The FILTER function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article:FILTER.
Let’s explore the formula
=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))
B4:B19 is the array that is to be filtered. Then we have provided the condition, based on what we will extract values. Since we need to check two criteria, we have multiplied them.
Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.
और पढ़ें: Extract Filtered Data in Excel to Another Sheet (4 Methods)
निष्कर्ष
That’s all for today. We have listed several methods to extract data from table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.
Further Readings
- How to Extract Data from Excel to Word (4 Ways)
- Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
- How to Pull Data from Multiple Worksheets in Excel VBA
- Transfer Data from One Excel Worksheet to Another Automatically
- How to Pull Data From Another Sheet Based on Criteria in Excel
- Excel Macro:Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Year from Date in Excel (3 Ways)