एक्सेल में, कभी-कभी आपको मापदंड के आधार पर एक सूची तैयार करने की आवश्यकता हो सकती है। आज हम आपको दिखाने जा रहे हैं कि मापदंड के आधार पर सूची कैसे तैयार की जाती है। इस सत्र के लिए, हम एक्सेल 365 का उपयोग कर रहे हैं, हालांकि इस संस्करण का उपयोग करने की अनुशंसा की गई है, बेझिझक अपने संस्करण का उपयोग करें।
सबसे पहले सबसे पहले, आइए उस डेटासेट के बारे में जानें जो हमारे उदाहरणों का आधार है।
यहां हमारे पास विभिन्न स्थानों के कई लोगों का उनके वाहनों के साथ एक डेटासेट है। इस डेटा का उपयोग करके, हम मापदंड के आधार पर एक सूची तैयार करेंगे।
ध्यान दें कि चीजों को सरल रखने के लिए यह डमी डेटा वाली एक मूल तालिका है। व्यावहारिक परिदृश्य में, आप एक बहुत बड़े और अधिक जटिल डेटासेट का सामना कर सकते हैं।
कार्यपुस्तिका का अभ्यास करें
निम्नलिखित लिंक से अभ्यास कार्यपुस्तिका डाउनलोड करने के लिए आपका स्वागत है।
मानदंड के आधार पर सूची तैयार करें
उदाहरण के लिए, हम लोगों की उनके क्षेत्र के आधार पर एक सूची बनाएंगे।
चूंकि यह एक छोटा डेटासेट है, इसलिए हम जानते हैं कि 4 क्षेत्र हैं। हमने क्षेत्रों के नाम संग्रहीत किए हैं और क्षेत्र के आधार पर सूची प्राप्त करेंगे।
<एच3>1. सूची बनाने के लिए INDEX-SMALL संयोजन का उपयोग करनायहां हमें एक सूची की आवश्यकता है, इसलिए हमारा सूत्र ऐसा होना चाहिए जो तालिका से कई मानों को पुनः प्राप्त करे। उस कार्य के लिए, हम INDEX . के संयोजन का उपयोग कर सकते हैं और छोटा कार्य।
इन कार्यों को जानने के लिए, इन लेखों को देखें:INDEX, SMALL.
इन दोनों के साथ, हमें कुछ सहायक कार्यों की आवश्यकता होगी, IF , पंक्ति और 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),"")
यहां हर समारोह का अपना उद्देश्य होता है। इंडेक्स फ़ंक्शन सरणी से मान लौटाता है B2:B12 (नाम कॉलम) और बड़ा छोटा भाग पंक्ति संख्या प्रदान करता है, जिसे प्राप्त किया जाना है।
अगर, छोटा, . के भीतर जांचता है कि मानदंड मेल खाते हैं या नहीं, और ROW फ़ंक्शन कॉलम की कोशिकाओं पर पुनरावृत्त होता है।
फिर बाहरी ROW छोटा . के लिए k-वें मान को दर्शाता है समारोह। ये फ़ंक्शन एक साथ पंक्ति संख्या लौटाते हैं और INDEX परिणाम देता है।
IFERROR सूत्र से उत्पन्न होने वाली किसी भी त्रुटि से निपटने के लिए।
नीचे खींचें आप दिए गए क्षेत्र के सभी लोगों को प्राप्त करेंगे।
इसी तरह, अन्य क्षेत्रों के लिए सूत्र लिखें (सूत्र समान है, केवल सेल को शिफ्ट करें)।
एक वैकल्पिक अनुक्रमणिका-छोटा संयोजन
हम सूत्र को वैकल्पिक तरीके से लिख सकते हैं। सूत्र के लिए उपयोग किए जाने वाले कार्य पिछले के समान ही होने जा रहे हैं। केवल प्रस्तुतिकरण अलग होगा।
आइए देखें सूत्र
=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 press दबाना होगा सूत्र को क्रियान्वित करने के लिए।
इन दोनों सूत्रों में थोड़ा सा अंतर है, क्या आप इनमें अंतर कर सकते हैं?
हां, हमारे पहले के फॉर्मूले में, हमने SMALL . के बिल्कुल अंत में 1 घटाया है भाग, लेकिन यहाँ हमने IF . में 1 घटाया है भाग।
1 घटाने का उद्देश्य उचित पंक्ति संख्या को चैनलाइज़ करना है। इससे पहले हमने वह किया है, अंत में, यहां वह पहले किया और आगे के ऑपरेशन के लिए आगे बढ़ें।
सूची को पूरा करने के लिए अन्य मानदंड के लिए सूत्र लिखें।
और पढ़ें:Excel में किसी कक्ष में सूची कैसे बनाएं (3 त्वरित तरीके)
<एच3>2. सूची बनाने के लिए AGGREGATE फ़ंक्शन का उपयोग करनाएक्सेल आपको AGGREGATE . नामक एक फ़ंक्शन प्रदान करता है जिसका उपयोग आप विभिन्न कार्यों को करने के लिए कर सकते हैं। यहां हम मानदंड के आधार पर सूची बनाने के लिए फ़ंक्शन का उपयोग कर सकते हैं।
कुल फ़ंक्शन AVERAGE, COUNT, MAX, आदि जैसी समग्र गणना देता है।
AGGREGATE . के लिए सिंटैक्स फ़ंक्शन इस प्रकार है:
AGGREGATE(function_number,behavior_options, range) function_number: यह संख्या निर्दिष्ट करती है कि कौन सी गणना की जानी चाहिए।
व्यवहार_विकल्प: इसे नंबर का उपयोग करके सेट करें। यह संख्या दर्शाती है कि फ़ंक्शन कैसे व्यवहार करेगा।
श्रेणी: वह श्रेणी जिसे आप समेकित करना चाहते हैं।
कुल फ़ंक्शन कई कार्य करता है इसलिए इसके भीतर कार्यों की संख्या पूर्वनिर्धारित होती है। हम अक्सर उपयोग किए जाने वाले कुछ फ़ंक्शन नंबर सूचीबद्ध कर रहे हैं
| फ़ंक्शन | <वें शैली ="चौड़ाई:40.8303%;">Function_number|
|---|---|
| औसत | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| छोटा | 15 |
फ़ंक्शन के बारे में और जानने के लिए, माइक्रोसॉफ्ट सपोर्ट . पर जाएं साइट।
अब देखते हैं सूत्र,
=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),"")
यहां AGGREGATE . के साथ फ़ंक्शन, हमने INDEX . का उपयोग किया है . इंडेक्स वह सरणी रखता है जो सूत्र के बाद के भाग में मिले मिलानों के आधार पर मान लौटाता है।
आप देख सकते हैं, कि हमने 15 . का उपयोग किया है function_number . के रूप में एग्रीगेट . में . उपरोक्त तालिका से, आप 15 . देख सकते हैं छोटा . के लिए कॉल करता है समारोह संचालन। अब क्या आप संबंधित कर सकते हैं?
हां, हमने INDEX-SMALL . को क्रियान्वित किया है AGGREGATE . के तरीके से सूत्र समारोह।
6 व्यवहार विकल्प के लिए, जो दर्शाता है त्रुटि मानों को अनदेखा करें ।
शेष मानों के लिए सूत्र लिखें।
समान रीडिंग
- एक्सेल में टू डू लिस्ट कैसे बनाएं (3 आसान तरीके)
- एक्सेल में मेलिंग सूची बनाना (2 तरीके)
- Excel में क्रमांकित सूची कैसे बनाएं (8 तरीके)
हम मापदंड के आधार पर एक अनूठी सूची बना सकते हैं। उसके लिए, हम INDEX . के संयोजन का उपयोग कर सकते हैं , मिलान , और COUNTIF ।
COUNTIF एक श्रेणी में कक्षों की गणना करता है जो एक ही शर्त को पूरा करता है। और मिलान किसी श्रेणी में लुकअप मान की स्थिति का पता लगाता है। इन कार्यों के बारे में अधिक जानकारी के लिए इन लेखों पर जाएँ: मैच, 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)),"")
इस सूत्र में:B2:B12 वह स्तंभ श्रेणी है जिसमें वे अद्वितीय मान होते हैं जिनसे आप निकालना चाहते हैं, C2:C12 वह कॉलम है जिसमें वह मानदंड है जिस पर आप G2 . पर आधारित हैं मानदंड को इंगित करता है।
MATCH . के भीतर फ़ंक्शन, हमने 0 को lookup_array, . के रूप में प्रदान किया है और lookup_range . के लिए हमने IF . का उपयोग किया है COUNTIF . युक्त भाग . इसलिए, जब तक 0 पाया जाता है, यह भाग मान लौटाता है। यहां मान INDEX . के लिए पंक्ति संख्या के रूप में कार्य करता है ।
इसे नीचे खींचें और आपको सभी अद्वितीय मान मिलेंगे।
CTRL+SHIFT + ENTER . का उपयोग करना न भूलें सूत्र निष्पादित करने के लिए।
यह एक अनूठी सूची तैयार करने के तरीकों का एक सम्मानजनक उल्लेख था। मानदंडों के आधार पर अद्वितीय सूची generating बनाने के बारे में जानने के लिए इस लेख का अनुसरण करें ।
<एच3>4. मानदंड के आधार पर सूची बनाने के लिए फ़िल्टर फ़ंक्शन का उपयोग करनायदि आप Excel 365 का उपयोग कर रहे हैं, तो आप FILTER नामक एकल अंतर्निहित फ़ंक्शन के साथ कार्य कर सकते हैं ।
फ़िल्टर फ़ंक्शन दिए गए मानदंड के आधार पर डेटा की एक श्रेणी को फ़िल्टर करता है और मिलान रिकॉर्ड निकालता है। समारोह के बारे में जानने के लिए, इस लेख पर जाएँ:फ़िल्टर ।
अब, हमारा सूत्र निम्नलिखित होगा,
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
B2:B12 वह सरणी है जिसे फ़िल्टर किया जाना है। फिर हमने सूची तैयार करने के आधार पर शर्त प्रदान की है।
यहां आपको सूत्र को नीचे खींचने की आवश्यकता नहीं होगी, यह एक बार में सभी मान प्रदान करेगा और सूची को पूरा करेगा।
और पढ़ें: एक्सेल में वर्णमाला सूची कैसे बनाएं (3 तरीके)
निष्कर्ष
आज के लिए इतना ही। हमने मापदंड के आधार पर सूची तैयार करने के कई तरीके सूचीबद्ध किए हैं। आशा है कि आपको यह मददगार लगेगा। अगर कुछ भी समझना मुश्किल लगता है तो बेझिझक टिप्पणी करें। आइए जानते हैं कोई और तरीका जो हमने यहां नहीं छोड़ा है।
आगे की रीडिंग
- एक्सेल में बुलेटेड लिस्ट कैसे बनाएं (9 तरीके)
- एक्सेल में अल्पविराम से अलग की गई सूची बनाएं (5 तरीके)
- एक्सेल में मूल्य सूची कैसे बनाएं (चरण दर चरण दिशानिर्देश)