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

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

एक्सेल में, कभी-कभी आपको मानदंडों के आधार पर एक सूची उत्पन्न करने की आवश्यकता हो सकती है। आज हम आपको दिखाने जा रहे हैं कि मानदंडों के आधार पर सूची कैसे उत्पन्न करें। इस सत्र के लिए, हम एक्सेल 365 का उपयोग कर रहे हैं, हालांकि इस संस्करण का उपयोग करने की सिफारिश की जाती है, अपने संस्करण का उपयोग करने के लिए स्वतंत्र महसूस करें।

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

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

यहां हमारे पास विभिन्न स्थानों के कई लोगों का डेटासेट है साथ में उनके वाहन। इस डेटा का उपयोग करके, हम मानदंडों के आधार पर एक सूची बनाएंगे।

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

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

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

मानदंडों के आधार पर सूची उत्पन्न करें

उदाहरण के लिए, हम उनके क्षेत्र के आधार पर लोगों की एक सूची बनाएंगे।

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

चूंकि यह एक छोटा डेटासेट है हम जानते हैं कि 4 क्षेत्र हैं। हमने क्षेत्रों के नाम संग्रहीत किए हैं और क्षेत्र के आधार पर सूची ढूंढेंगे।

1. सूची उत्पन्न करने के लिए INDEX-SMALL संयोजन का उपयोग करना

यहां हमें एक सूची की आवश्यकता है, इसलिए हमारा फॉर्मूला ऐसा होना चाहिए जो टेबल से एकाधिक मान प्राप्त करे। उस कार्य के लिए, हम INDEX और SMALL फंक्शन के संयोजन का उपयोग कर सकते हैं।

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

इन दो के साथ, हमें कुछ हेल्पर फंक्शन की आवश्यकता होगी, IF, ROW और IFERROR। अधिक जानकारी के लिए लेख देखें: IF, ROW, IFERROR।

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

=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

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

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

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

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

इसे नीचे खींचें आपको दिए गए क्षेत्र के सभी लोग मिल जाएंगे।

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

इसी तरह, अन्य क्षेत्रों के लिए फॉर्मूला लिखें (फॉर्मूला समान है, केवल सेल शिफ्ट करें)।

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

एक वैकल्पिक INDEX-SMALL संयोजन

हम फॉर्मूला को एक वैकल्पिक तरीके से लिख सकते हैं। फॉर्मूला के लिए उपयोग किए जाने वाले फंक्शन पिछले वाले के समान ही होंगे। केवल प्रस्तुति अलग होगी।

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

=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")

फिर से, फॉर्मूला निष्पादित करने के लिए CTRL + SHIFT + ENTER दबाने की आवश्यकता है।

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

इन दो फॉर्मूला के बीच थोड़ा अंतर है, क्या आप उन्हें अलग कर सकते हैं?

हां, हमारे पहले फॉर्मूला में, हमने SMALL भाग के अंत में 1 घटाया है, लेकिन यहां हमने IF भाग के भीतर 1 घटाया है।

1 घटाने का उद्देश्य उचित पंक्ति संख्या की ओर चैनलाइज़ करना है। पहले हमने इसे अंत में किया, यहां हमने इसे पहले किया और आगे की ऑपरेशन के लिए आगे बढ़े।

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

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

और पढ़ें: एक्सेल में सेल के भीतर सूची कैसे बनाएं (3 त्वरित तरीके)

2. सूची उत्पन्न करने के लिए 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) साइट पर जाएं।

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

=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

यहां AGGREGATE फंक्शन के साथ, हमने INDEX का उपयोग किया है। INDEX वह ऐरे रखता है जो फॉर्मूला के बाद के भाग में मिलान पाए जाने पर मान लौटाता है।

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

हां, हमने AGGREGATE फंक्शन के तरीके से INDEX-SMALL फॉर्मूला निष्पादित किया है।

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

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

बाकी मानों के लिए फॉर्मूला लिखें।

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

समान पठन

  • एक्सेल में टू डू सूची कैसे बनाएं (3 आसान तरीके)
  • एक्सेल में मेलिंग सूची बनाना (2 तरीके)
  • एक्सेल में नंबर वाली सूची कैसे बनाएं (8 तरीके)

3. INDEX-MATCH-COUNTIF का उपयोग करके अद्वितीय सूची उत्पन्न करें

हम मानदंडों के आधार पर एक अद्वितीय सूची बना सकते हैं। उसके लिए, हम INDEX, MATCH, और COUNTIF के संयोजन का उपयोग कर सकते हैं।

COUNTIF एक रेंज में उन सेल को गिनता है जो एक शर्त पूरी करते हैं। और MATCH एक रेंज में लुकअप मान की स्थिति का पता लगाता है। इन फंक्शन के बारे में अधिक जानने के लिए इन लेखों पर जाएं: MATCH, COUNTIF।

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

=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"")

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

इस फॉर्मूला में: B2:B12 वह कॉलम रेंज है जिसमें वे अद्वितीय मान हैं जिन्हें आप निकालना चाहते हैं, C2:C12 वह कॉलम है जिसमें वह मानदंड है जिस पर आप आधारित हैं G2 मानदंड को इंगित करता है।

MATCH फंक्शन के भीतर, हमने 0 को लुकअप_ऐरे (lookup_array) के रूप में प्रदान किया, और लुकअप_रेंज (lookup_range) के लिए हमने COUNTIF वाले IF भाग का उपयोग किया। तो, यह भाग तब तक मान लौटाता है जब तक 0 मिलता है। मान यहां INDEX के लिए पंक्ति संख्या के रूप में काम करता है।

इसे नीचे खींचें और आपको सभी अद्वितीय मान मिल जाएंगे।

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

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

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

यह मानदंडों के आधार पर अद्वितीय सूची उत्पन्न करने के दृष्टिकोण का एक सम्मानजनक उल्लेख था। मानदंडों के आधार पर अद्वितीय सूची उत्पन्न करने के बारे में जानने के लिए इस लेख का पालन करें।

4. मानदंडों के आधार पर सूची उत्पन्न करने के लिए FILTER फंक्शन का उपयोग करना

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

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

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

=FILTER($B$2:$B$12,$C$2:$C$12=G$2)

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

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

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

मानदंडों के आधार पर एक्सेल सूचियां उत्पन्न करें: 4 सिद्ध विधियां

और पढ़ें: एक्सेल में वर्णमाला क्रम में सूची कैसे बनाएं (3 तरीके)

निष्कर्ष

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

आगे पढ़ें

  • एक्सेल में बुलेटेड सूची कैसे बनाएं (9 तरीके)
  • एक्सेल में कॉमा सेपरेटेड सूची बनाएं (5 तरीके)
  • एक्सेल में प्राइस सूची कैसे बनाएं (चरण-दर-चरण दिशानिर्देश)

  1. मानदंड के आधार पर Excel पंक्तियों को कॉलम में ट्रांसपोज़ करें: 2 सिद्ध तरीके मानदंड के आधार पर Excel पंक्तियों को कॉलम में ट्रांसपोज़ करें: 2 सिद्ध तरीके

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

  1. Excel रिपोर्ट तेजी से बनाएं: सटीक डेटा अंतर्दृष्टि के लिए 2 सिद्ध विधियां Excel रिपोर्ट तेजी से बनाएं: सटीक डेटा अंतर्दृष्टि के लिए 2 सिद्ध विधियां

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

  1. एक्सेल गाइड: स्पष्ट डेटा विज़ुअलाइज़ेशन के लिए ग्रुप द्वारा पंक्ति रंग वैकल्पिक करने के 6 सिद्ध तरीके एक्सेल गाइड: स्पष्ट डेटा विज़ुअलाइज़ेशन के लिए ग्रुप द्वारा पंक्ति रंग वैकल्पिक करने के 6 सिद्ध तरीके

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