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

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

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

पहली चीजें पहले, आइए उस डेटासेट के बारे में जानें जो हमारे उदाहरणों का आधार है।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

यहां हमारे पास एक टेबल है जिसमें फिल्मों की सूची है जिसमें शैली (genre) और मुख्य अभिनेता (lead actor) के साथ रिलीज़ वर्ष (release year) है। इस डेटासेट का उपयोग करके, हम एकाधिक मानदंडों के आधार पर डेटा पुनर्प्राप्त करेंगे।

ध्यान दें कि चीजों को सरल रखने के लिए यह एक बुनियादी टेबल है। एक व्यावहारिक परिदृश्य में, आप बहुत बड़े और जटिल डेटासेट का सामना कर सकते हैं।

अभ्यास वर्कबुक

आप निम्न लिंक से अभ्यास वर्कबुक डाउनलोड करने के लिए स्वागत है।

एकाधिक मानदंडों के आधार पर टेबल से डेटा निकालें

यहां उदाहरण के लिए हम मानदंड के रूप में शैली और अभिनेता का नाम प्रदान करेंगे और इन मानदंडों के आधार पर हम फिल्म का नाम निकालेंगे।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

1. एकल मान वापस करें

इस खंड में, हम एकल मान वापस करेंगे। मानदंडों के आधार पर केवल एक मान प्राप्त होगा। आइए पता लगाएं।

I. INDEX-MATCH ऐरे फॉर्मूला

हम INDEX और MATCH फंक्शन के संयोजन का उपयोग कर सकते हैं। INDEX एक रेंज में दिए गए स्थान पर मान लौटाता है। MATCH एक रेंज में लुकअप मान की स्थिति का पता लगाता है।

इन फंक्शन को जानने के लिए, इन लेखों की जांच करें: INDEX, MATCH।

आइए मानदंड मान सेट करें। फिलहाल शैली (Genre) के रूप में थ्रिलर (Thriller) और अभिनेता (Actor) फ़ील्ड में ह्यू जैकमैन (Hugh Jackman)।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

फॉर्मूला निम्न होगा

=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0))

B4:B19 वह ऐरे है जहां से हम मान निकालेंगे। और MATCH फंक्शन वह पंक्ति संख्या सेट करता है जिसे प्राप्त किया जाना है।

आप देख सकते हैं हमने MATCH के भीतर 1 को लुकअप_वैल्यू (lookup_value) के रूप में प्रदान किया है। और लुकअप_ऐरे (lookup_array) मानदंड मिलान तर्क के गुणन के माध्यम से उत्पन्न होता है।

$H$4=$C$4:$C$19 के माध्यम से हमने शैली की जांच की है और $H$5=$D$4:$D$19 अभिनेता नाम के लिए।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

यह गुणन परिणाम की ऐरे में 1 ढूंढता है। और पंक्ति संख्या लौटाता है और INDEX फंक्शन फिल्म का नाम लौटाता है।

यह एक ऐरे फॉर्मूला है इसलिए इसे निष्पादित करने के लिए CTRL + SHIFT + ENTER का उपयोग करना होगा।

मानदंड मान बदलें, और आपको अपडेटेड मान मिलेंगे।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

और पढ़ें: एक्सेल में सेल से विशिष्ट डेटा कैसे निकालें (3 उदाहरण)

II. INDEX-MATCH नॉन-ऐरे फॉर्मूला

हम INDEX और MATCH को संयोजित करके एक नॉन-ऐरे फॉर्मूला बना सकते हैं।

आइए पहले फॉर्मूला देखें

=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 फंक्शन का उपयोग करके गुणा छोड़ सकते हैं।

IF एक तार्किक परीक्षण चलाता है और परिणाम के रूप में बूलियन मान (TRUE या FALSE) लौटाता है। फंक्शन के बारे में जानने के लिए इस IF लेख पर जाएं।

हमारा फॉर्मूला निम्न होगा

=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 के लिए if_true_value है।

फॉर्मूला निष्पादित करने के लिए CTRL+SHIFT+ENTER का उपयोग करना होगा।

मानदंड मान बदलें, और आपको अपडेटेड मान मिलेगा।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

और पढ़ें: एक्सेल शीट से डेटा कैसे निकालें (6 प्रभावी तरीके)

IV. LOOKUP फंक्शन

हम मानदंडों के आधार पर डेटा निकालने के अपने कार्य को करने के लिए LOOKUP फंक्शन का उपयोग कर सकते हैं।

LOOKUP फंक्शन एक रेंज में मिलान लुकअप करता है और संबंधित मान लौटाता है। अधिक जानकारी के लिए, माइक्रोसॉफ्ट सपोर्ट (Microsoft Support) साइट पर जाएं।

अब आइए फॉर्मूला देखें

=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 दबाने की आवश्यकता नहीं।

मानदंड मान बदलें ताकि यह देखा जा सके कि फॉर्मूला अन्य मानों के लिए पूरी तरह से काम कर रहा है या नहीं।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

ध्यान दें कि हमने लुकअप_वैल्यू (lookup_value) के रूप में 2 का उपयोग किया है। यह 1 से शुरू होने वाली कोई भी संख्या हो सकती है।

और पढ़ें: छवि से एक्सेल में डेटा कैसे निकालें (त्वरित चरणों के साथ)

समान पठन

  • टेक्स्ट फाइल को एक्सेल में बदलने के लिए VBA कोड (7 तरीके)
  • एक्सेल VBA: वेबसाइट से डेटा स्वचालित रूप से खींचें (2 तरीके)
  • सुरक्षित वेबसाइट से एक्सेल में डेटा कैसे आयात करें (त्वरित चरणों के साथ)
  • VLOOKUP के साथ एक एक्सेल वर्कशीट से दूसरी में डेटा स्वचालित रूप से स्थानांतरित करें
  • पाइप डिलीमीटर के साथ एक्सेल को टेक्स्ट फाइल में कैसे बदलें (2 तरीके)

2. एकाधिक मान वापस करें

I. INDEX-SMALL संयोजन

मानदंडों के आधार पर एकाधिक डेटा निकालने के लिए विभिन्न फंक्शन संयोजनों का उपयोग किया जा सकता है। संयोजनों में से एक INDEX – SMALL संयोजन है।

SMALL फंक्शन मान के आधार पर रैंक की गई सूची में उनकी स्थिति के आधार पर मान लौटाता है। अधिक जानने के लिए इस SMALL लेख को देखें।

इन दो के साथ, हमें कुछ हेल्पर फंक्शन की आवश्यकता होगी, IF, ROW और 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),"")

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

यहां हर फंक्शन का अपना उद्देश्य है। INDEX फंक्शन ऐरे B2:B17 से मान लौटाता है और बड़ा SMALL भाग पंक्ति संख्या प्रदान करता है, जिसे प्राप्त किया जाना है।

IF, SMALL के भीतर, जांचता है कि मानदंड मेल खाते हैं या नहीं। हमारे पास मिलान करने के लिए दो शर्तें हैं, इसलिए हमने दोनों तार्किक ऑपरेशनों को गुणा करके मानदंड जांचे। और ROW फंक्शन कॉलम के सेल पर पुनरावृति करता है।

फिर बाहरी ROW SMALL फंक्शन के लिए k-वां मान दर्शाता है। साथ में ये फंक्शन पंक्ति संख्या लौटाते हैं और INDEX परिणाम लौटाता है।

IFERROR फॉर्मूला से उत्पन्न होने वाली किसी भी त्रुटि से निपटने के लिए। हमने इसे इस तरह सेट किया है कि त्रुटि के लिए यह खाली सेल प्रदान करेगा।

इसे नीचे खींचें, आपको मानदंडों से मेल खाने वाले सभी मान मिल जाएंगे।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

और पढ़ें: एक्सेल फॉर्मूला का उपयोग करके सूची से डेटा कैसे निकालें (5 तरीके)

II. INDEX-AGGREGATE संयोजन

एक्सेल में AGGREGATE फंक्शन हमें विभिन्न कार्य करने की अनुमति देता है। कई ऑपरेशन के लिए एक फंक्शन। हम इस फंक्शन का उपयोग एकाधिक मानदंडों के आधार पर एकाधिक मान वापस करने के लिए कर सकते हैं।

आइए फंक्शन के बारे में थोड़ा जानें, AGGREGATE फंक्शन AVERAGE, COUNT, MAX, आदि जैसे समग्र गणना लौटाता है।

AGGREGATE फंक्शन के लिए सिंटैक्स निम्न है:

AGGREGATE(function_number,behavior_options, range)

function_number: यह संख्या निर्दिष्ट करती है कि कौन सी गणना की जानी चाहिए।

behavior_options: संख्या का उपयोग करके इसे सेट करें। यह संख्या दर्शाती है कि फंक्शन कैसे व्यवहार करेगा।

range: वह रेंज जिसे आप समग्र करना चाहते हैं।

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

फंक्शन फंक्शन_नंबर
AVERAGE 1
COUNT 2
COUNTA 3
MAX 4
MIN 5
PRODUCT 6
SUM 9
LARGE 14
SMALL 15

फंक्शन के बारे में अधिक जानने के लिए, माइक्रोसॉफ्ट सपोर्ट (Microsoft Support) साइट पर जाएं।

अब आइए फॉर्मूला देखें, यह INDEX और AGGREGATE का संयोजन होगा।

=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),"")

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

यहां हमने AGGREGATE में function_number के रूप में 15 का उपयोग किया है। उपरोक्त तालिका से, आप देख सकते हैं 15 SMALL फंक्शन ऑपरेशन के लिए कॉल करता है। आप देख सकते हैं AGGREGATE (और फंक्शन नंबर और व्यवहार विकल्प नंबर) का उपयोग करने के अलावा फॉर्मूला पहले INDEX–SMALL फॉर्मूला के समान ही है।

तंत्र समान है, INDEX वह ऐरे रखता है जो AGGREGATE भाग में मिलान पाए जाने पर मान लौटाता है।

6 व्यवहार विकल्प के लिए, जो त्रुटि मानों को अनदेखा करें (ignore error values) को दर्शाता है।

इसे नीचे खींचें, आपको मानदंडों से मेल खाने वाले सभी मान मिल जाएंगे।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

फॉर्मूला निष्पादित करने के लिए CTRL+SHIFT+ENTER का उपयोग करना याद रखें।

और पढ़ें: एकल मानदंड के आधार पर एक्सेल में एकाधिक मान लौटाएं (3 विकल्प)

III. INDEX-MATCH-COUNTIF संयोजन

एकाधिक मानदंडों के आधार पर एकाधिक मान वापस करने के लिए, हम INDEX, MATCH, और 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 फंक्शन के भीतर, हमने 0 को लुकअप_ऐरे (lookup_array) के रूप में प्रदान किया, और लुकअप_रेंज (lookup_range) के लिए हमने COUNTIF वाले IF भाग का उपयोग किया।

यहां, COUNTIF फंक्शन किसी भी मान को बाहर करता है जो पहले ही प्राप्त हो चुका है। और दो IF फंक्शन दो शर्तों की जांच करते हैं। हमने इन फंक्शन को जोड़ा है ताकि वे मिलकर लुकअप_रेंज (lookup_range) बनाएं।

MATCH भाग तब तक मान लौटाता है जब तक 0 मिलता है। मान यहां INDEX के लिए पंक्ति संख्या के रूप में काम करता है।

इसे नीचे खींचें, आपको मानदंडों से मेल खाने वाले सभी मान मिल जाएंगे।

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

और पढ़ें: एक्सेल में सेल से डेटा कैसे निकालें (5 तरीके)

IV. FILTER फंक्शन

यदि आप एक्सेल 365 का उपयोग कर रहे हैं, तो आप एक ही अंतर्निहित फंक्शन FILTER के साथ कार्य कर सकते हैं।

FILTER फंक्शन दिए गए मानदंडों के आधार पर डेटा की एक रेंज को फ़िल्टर करता है और मिलान करने वाले रिकॉर्ड निकालता है। फंक्शन के बारे में जानने के लिए इस लेख पर जाएं: FILTER।

आइए फॉर्मूला देखें

=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))

एक्सेल मास्टरी: एकाधिक मानदंडों का उपयोग करके टेबल डेटा कुशलतापूर्वक निकालें

B4:B19 वह ऐरे है जिसे फ़िल्टर किया जाना है। फिर हमने शर्त प्रदान की, जिसके आधार पर हम मान निकालेंगे। चूंकि हमें दो मानदंड जांचने हैं, हमने उन्हें गुणा किया है।

यहां आपको फॉर्मूला नीचे खींचने की आवश्यकता नहीं होगी, एक बार में यह सभी मान प्रदान करेगा और सूची पूरी करेगा।

और पढ़ें: एक्सेल में फ़िल्टर्ड डेटा को दूसरी शीट में निकालें (4 तरीके)

निष्कर्ष

आज के लिए बस इतना ही। हमने एकाधिक मानदंडों के आधार पर टेबल से डेटा निकालने के कई तरीके सूचीबद्ध किए हैं। आशा है आपको यह मददगार लगेगा। अगर कुछ समझने में मुश्किल लगता है तो बेझिझक कमेंट करें। हमें बताएं कि हमने यहां कौन से अन्य दृष्टिकोण छोड़ दिए हैं।

आगे पढ़ें

  • एक्सेल से वर्ड में डेटा कैसे निकालें (4 तरीके)
  • VBA का उपयोग करके एक शीट से दूसरी में डेटा निकालें (3 तरीके)
  • VBA में एकाधिक वर्कशीट से डेटा कैसे खींचें
  • एक एक्सेल वर्कशीट से दूसरी में डेटा स्वचालित रूप से स्थानांतरित करें
  • मानदंडों के आधार पर एक्सेल में दूसरी शीट से डेटा कैसे खींचें
  • एक्सेल मैक्रो: एकाधिक एक्सेल फाइलों से डेटा निकालें (4 तरीके)
  • एक्सेल में तारीख से वर्ष कैसे निकालें (3 तरीके)

  1. एक्सेल डेटा मॉडल में महारत हासिल करें: डेटा व्यवस्थित और विश्लेषण करने के 3 व्यावहारिक उदाहरण एक्सेल डेटा मॉडल में महारत हासिल करें: डेटा व्यवस्थित और विश्लेषण करने के 3 व्यावहारिक उदाहरण

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

  1. एक्सेल डेटा मॉडल से टेबल त्वरित रूप से हटाएं: 2 सीधे तरीके एक्सेल डेटा मॉडल से टेबल त्वरित रूप से हटाएं: 2 सीधे तरीके

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

  1. त्वरित गाइड: XML से एक्सेल में डेटा खींचें 2 सरल चरणों में त्वरित गाइड: XML से एक्सेल में डेटा खींचें 2 सरल चरणों में

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