मैंने हाल ही में एक लेख लिखा था कि कैसे बड़ी मात्रा में डेटा को आसानी से सारांशित करने के लिए एक्सेल में सारांश फ़ंक्शंस का उपयोग करने के लिए, लेकिन उस आलेख ने वर्कशीट के सभी डेटा को ध्यान में रखा। क्या होगा यदि आप केवल डेटा के सबसेट को देखना चाहते हैं और डेटा के सबसेट को सारांशित करना चाहते हैं?
Excel में, आप कॉलम पर फ़िल्टर बना सकते हैं जो उन पंक्तियों को छिपा देगा जो आपके फ़िल्टर से मेल नहीं खाती हैं। इसके अलावा, आप केवल फ़िल्टर किए गए डेटा का उपयोग करके डेटा को सारांशित करने के लिए एक्सेल में विशेष कार्यों का भी उपयोग कर सकते हैं।
इस लेख में, मैं आपको Excel में फ़िल्टर बनाने और फ़िल्टर किए गए डेटा को सारांशित करने के लिए अंतर्निहित फ़ंक्शंस का उपयोग करने के चरणों के बारे में बताऊंगा।
Excel में सरल फ़िल्टर बनाएं
एक्सेल में, आप साधारण फिल्टर और जटिल फिल्टर बना सकते हैं। आइए सरल फिल्टर के साथ शुरुआत करें। फ़िल्टर के साथ काम करते समय, आपके पास हमेशा शीर्ष पर एक पंक्ति होनी चाहिए जिसका उपयोग लेबल के लिए किया जाता है। यह पंक्ति होना आवश्यक नहीं है, लेकिन यह फ़िल्टर के साथ काम करना थोड़ा आसान बना देता है।
ऊपर, मेरे पास कुछ नकली डेटा है और मैं शहर . पर एक फ़िल्टर बनाना चाहता हूं कॉलम। एक्सेल में, यह करना वास्तव में आसान है। आगे बढ़ें और डेटा . पर क्लिक करें रिबन में टैब करें और फिर फ़िल्टर . पर क्लिक करें बटन। आपको शीट पर डेटा का चयन करने या पहली पंक्ति में क्लिक करने की आवश्यकता नहीं है।
जब आप फ़िल्टर पर क्लिक करते हैं, तो पहली पंक्ति के प्रत्येक कॉलम में स्वतः ही दाईं ओर एक छोटा ड्रॉपडाउन बटन जुड़ जाएगा।
अब आगे बढ़ें और सिटी कॉलम में ड्रॉपडाउन एरो पर क्लिक करें। आपको कुछ अलग-अलग विकल्प दिखाई देंगे, जिनके बारे में मैं नीचे बताऊंगा।
सबसे ऊपर, आप सिटी कॉलम में मानों के आधार पर सभी पंक्तियों को जल्दी से सॉर्ट कर सकते हैं। ध्यान दें कि जब आप डेटा को सॉर्ट करते हैं, तो यह पूरी पंक्ति को स्थानांतरित कर देगा, न कि केवल सिटी कॉलम के मान। यह सुनिश्चित करेगा कि आपका डेटा पहले जैसा ही बना रहे।
साथ ही, आईडी नामक सबसे आगे एक कॉलम जोड़ना और इसे अपनी वर्कशीट में जितनी भी पंक्तियाँ हैं, एक से संख्या देना एक अच्छा विचार है। इस तरह, आप हमेशा आईडी कॉलम के आधार पर छाँट सकते हैं और अपने डेटा को उसी क्रम में वापस पा सकते हैं, जिस क्रम में वह मूल रूप से था, यदि यह आपके लिए महत्वपूर्ण है।
जैसा कि आप देख सकते हैं, स्प्रैडशीट के सभी डेटा को अब सिटी कॉलम के मानों के आधार पर क्रमबद्ध किया गया है। अब तक, कोई पंक्तियाँ छिपी नहीं हैं। अब फ़िल्टर डायलॉग के निचले भाग में चेकबॉक्स पर एक नज़र डालते हैं। मेरे उदाहरण में, सिटी कॉलम में मेरे पास केवल तीन अद्वितीय मान हैं और वे तीन सूची में दिखाई देते हैं।
मैंने आगे बढ़कर दो शहरों को अनचेक किया और एक को चेक छोड़ दिया। अब मेरे पास डेटा दिखाने की केवल 8 पंक्तियाँ हैं और बाकी छिपी हुई हैं। यदि आप सबसे बाईं ओर पंक्ति संख्याओं की जाँच करते हैं, तो आप आसानी से बता सकते हैं कि आप फ़िल्टर किए गए डेटा को देख रहे हैं। कितनी पंक्तियाँ छिपी हैं, इस पर निर्भर करते हुए, आपको कुछ अतिरिक्त क्षैतिज रेखाएँ दिखाई देंगी और संख्याओं का रंग नीला होगा।
अब मान लें कि मैं परिणामों की संख्या को और कम करने के लिए दूसरे कॉलम पर फ़िल्टर करना चाहता हूं। कॉलम सी में, मेरे पास प्रत्येक परिवार में कुल सदस्यों की संख्या है और मैं केवल दो से अधिक सदस्यों वाले परिवारों के परिणाम देखना चाहता हूं।
आगे बढ़ो और कॉलम सी में ड्रॉपडाउन तीर पर क्लिक करें और आपको कॉलम में प्रत्येक अद्वितीय मान के लिए समान चेकबॉक्स दिखाई देंगे। हालांकि, इस मामले में, हम नंबर फ़िल्टर . पर क्लिक करना चाहते हैं और फिर इससे भी बड़ा . पर क्लिक करें . जैसा कि आप देख सकते हैं, कई अन्य विकल्प भी हैं।
एक नया संवाद पॉप अप होगा और यहां आप फ़िल्टर के लिए मान टाइप कर सकते हैं। आप AND या OR फ़ंक्शन के साथ एक से अधिक मानदंड भी जोड़ सकते हैं। उदाहरण के लिए, आप कह सकते हैं कि आपको ऐसी पंक्तियाँ चाहिए जहाँ मान 2 से अधिक हो और 5 के बराबर न हो।
अब मैं डेटा की केवल 5 पंक्तियों तक सीमित हूं:केवल न्यू ऑरलियन्स के परिवार और 3 या अधिक सदस्यों वाले परिवार। काफी आसान? ध्यान दें कि आप ड्रॉपडाउन पर क्लिक करके और फिर "कॉलम नाम" से फ़िल्टर साफ़ करें पर क्लिक करके किसी कॉलम पर फ़िल्टर को आसानी से साफ़ कर सकते हैं। लिंक।
तो यह इसके बारे में एक्सेल में सरल फिल्टर के लिए है। वे उपयोग करने में बहुत आसान हैं और परिणाम बहुत सीधे हैं। आइए अब उन्नत . का उपयोग करके जटिल फ़िल्टर पर एक नज़र डालें फ़िल्टर संवाद.
Excel में उन्नत फ़िल्टर बनाएं
यदि आप अधिक उन्नत फ़िल्टर बनाना चाहते हैं, तो आपको उन्नत . का उपयोग करना होगा फ़िल्टर संवाद। उदाहरण के लिए, मान लें कि मैं न्यू ऑरलियन्स में रहने वाले उन सभी परिवारों को देखना चाहता हूं जिनके परिवार में 2 से अधिक सदस्य हैं या क्लार्क्सविले के सभी परिवार जिनके परिवार में 3 से अधिक सदस्य हैं और केवल वही जिनके पास .EDU . है ईमेल पता समाप्त करना। अब आप एक साधारण फ़िल्टर से ऐसा नहीं कर सकते।
ऐसा करने के लिए, हमें एक्सेल शीट को थोड़ा अलग तरीके से सेट करना होगा। आगे बढ़ें और अपने डेटा के सेट के ऊपर कुछ पंक्तियाँ डालें और शीर्षक लेबल को ठीक पहली पंक्ति में कॉपी करें जैसा कि नीचे दिखाया गया है।
अब यहां बताया गया है कि उन्नत फ़िल्टर कैसे काम करते हैं। आपको सबसे पहले शीर्ष पर स्थित कॉलम में अपना मानदंड टाइप करना होगा और फिर उन्नत . पर क्लिक करना होगा क्रमबद्ध करें और फ़िल्टर करें . के अंतर्गत बटन डेटा . पर टैब।
तो हम वास्तव में उन कोशिकाओं में क्या टाइप कर सकते हैं? ठीक है, तो चलिए अपने उदाहरण से शुरू करते हैं। हम केवल न्यू ऑरलियन्स या क्लार्क्सविले का डेटा देखना चाहते हैं, तो चलिए उन्हें सेल E2 और E3 में टाइप करते हैं।
जब आप विभिन्न पंक्तियों पर मान टाइप करते हैं, तो इसका अर्थ है OR। अब हम दो से अधिक सदस्यों वाले न्यू ऑरलियन्स परिवार और 3 से अधिक सदस्यों वाले क्लार्क्सविले परिवार चाहते हैं। ऐसा करने के लिए, टाइप करें>2 C2 और>3 . में C3 में।
चूँकि>2 और न्यू ऑरलियन्स एक ही पंक्ति में हैं, यह एक AND ऑपरेटर होगा। उपरोक्त पंक्ति 3 के लिए भी यही सच है। अंत में, हम केवल .EDU वाले ईमेल पते वाले परिवार चाहते हैं। ऐसा करने के लिए, बस *.edu . टाइप करें D2 और D3 दोनों में। * चिन्ह का अर्थ है कितने भी वर्ण।
ऐसा करने के बाद, अपने डेटासेट में कहीं भी क्लिक करें और फिर उन्नत . पर क्लिक करें बटन। सूची रंग e फ़ील्ड स्वचालित रूप से आपके डेटासेट का पता लगा लेगी क्योंकि आपने उन्नत बटन पर क्लिक करने से पहले उसमें क्लिक किया था। अब मानदंड श्रेणी . के दाईं ओर छोटे छोटे बटन पर क्लिक करें बटन।
A1 से E3 तक सब कुछ चुनें और फिर उन्नत फ़िल्टर संवाद पर वापस जाने के लिए उसी बटन पर फिर से क्लिक करें। ठीक क्लिक करें और आपका डेटा अब फ़िल्टर किया जाना चाहिए!
जैसा कि आप देख सकते हैं, अब मेरे पास केवल 3 परिणाम हैं जो उन सभी मानदंडों से मेल खाते हैं। ध्यान दें कि मानदंड श्रेणी के लेबल को काम करने के लिए डेटासेट के लेबल के साथ बिल्कुल मेल खाना चाहिए।
आप स्पष्ट रूप से इस पद्धति का उपयोग करके बहुत अधिक जटिल प्रश्न बना सकते हैं, इसलिए अपने वांछित परिणाम प्राप्त करने के लिए इसके साथ खेलें। अंत में, फ़िल्टर किए गए डेटा के लिए योग फ़ंक्शन लागू करने के बारे में बात करते हैं।
फ़िल्टर किए गए डेटा को सारांशित करना
अब मान लें कि मैं अपने फ़िल्टर किए गए डेटा पर परिवार के सदस्यों की संख्या का योग करना चाहता हूं, मुझे ऐसा करने के बारे में कैसे पता चलेगा? ठीक है, आइए साफ़ करें . पर क्लिक करके अपना फ़िल्टर साफ़ करें रिबन में बटन। चिंता न करें, केवल उन्नत बटन पर क्लिक करके और फिर से ठीक क्लिक करके उन्नत फ़िल्टर को फिर से लागू करना बहुत आसान है।
हमारे डेटासेट के निचले भाग में, कुल . नामक एक सेल जोड़ें और फिर परिवार के कुल सदस्यों का योग करने के लिए एक योग फ़ंक्शन जोड़ें। मेरे उदाहरण में, मैंने अभी-अभी =SUM(C7:C31) . टाइप किया है ।
तो अगर मैं सभी परिवारों को देखता हूं, तो मेरे पास कुल 78 सदस्य हैं। अब आगे बढ़ते हैं और हमारे उन्नत फ़िल्टर को फिर से लागू करते हैं और देखते हैं कि क्या होता है।
ओह! 11 सही संख्या दिखाने के बजाय, मुझे अभी भी कुल 78 दिखाई दे रहा है! ऐसा क्यों? ठीक है, SUM फ़ंक्शन छिपी हुई पंक्तियों को अनदेखा नहीं करता है, इसलिए यह अभी भी सभी पंक्तियों का उपयोग करके गणना कर रहा है। सौभाग्य से, कुछ फ़ंक्शन हैं जिनका उपयोग आप छिपी हुई पंक्तियों को अनदेखा करने के लिए कर सकते हैं।
पहला है SUBTOTAL . इससे पहले कि हम इनमें से किसी विशेष फ़ंक्शन का उपयोग करें, आप अपना फ़िल्टर साफ़ करना चाहेंगे और फिर फ़ंक्शन में टाइप करना चाहेंगे।
एक बार फ़िल्टर साफ़ हो जाने के बाद, आगे बढ़ें और =SUBTOTAL( . टाइप करें और आपको विकल्पों के एक समूह के साथ एक ड्रॉपडाउन बॉक्स दिखाई देना चाहिए। इस फ़ंक्शन का उपयोग करते हुए, आप सबसे पहले किसी संख्या का उपयोग करके उस प्रकार के योग फ़ंक्शन का चयन करते हैं जिसका आप उपयोग करना चाहते हैं।
हमारे उदाहरण में, मैं SUM . का उपयोग करना चाहता हूं , इसलिए मैं 9 नंबर टाइप करूंगा या ड्रॉपडाउन से उस पर क्लिक करूंगा। फिर अल्पविराम टाइप करें और सेल की श्रेणी चुनें।
जब आप एंटर दबाते हैं, तो आप देखेंगे कि 78 का मान पहले जैसा ही है। हालांकि, अगर आप अब फिर से फ़िल्टर लागू करते हैं, तो हम 11 देखेंगे!
उत्कृष्ट! ठीक यही हम चाहते हैं। अब आप अपने फ़िल्टर समायोजित कर सकते हैं और मान हमेशा केवल उन पंक्तियों को प्रदर्शित करेगा जो वर्तमान में दिखाई दे रही हैं।
दूसरा फ़ंक्शन जो काफी हद तक SUBTOTAL फ़ंक्शन के समान ही काम करता है, वह है AGGREGATE . अंतर केवल इतना है कि AGGREGATE फ़ंक्शन में एक और पैरामीटर है जहाँ आपको यह निर्दिष्ट करना होगा कि आप छिपी हुई पंक्तियों को अनदेखा करना चाहते हैं।
पहला पैरामीटर वह योग फ़ंक्शन है जिसका आप उपयोग करना चाहते हैं और SUBTOTAL के साथ, 9 SUM फ़ंक्शन का प्रतिनिधित्व करता है। दूसरा विकल्प वह है जहां आपको छिपी हुई पंक्तियों को अनदेखा करने के लिए 5 टाइप करना होगा। अंतिम पैरामीटर वही है और कोशिकाओं की श्रेणी है।
AGGREGATE फ़ंक्शन और अन्य फ़ंक्शन जैसे MODE, MEDIAN, AVERAGE, आदि का अधिक विस्तार से उपयोग करने का तरीका जानने के लिए आप सारांश फ़ंक्शंस पर मेरा लेख भी पढ़ सकते हैं।
उम्मीद है, यह लेख आपको एक्सेल में फिल्टर बनाने और उपयोग करने के लिए एक अच्छा प्रारंभिक बिंदु प्रदान करता है। यदि आपके कोई प्रश्न हैं, तो बेझिझक एक टिप्पणी पोस्ट करें। आनंद लें!