मैंने हाल ही में एक लेख लिखा था कि एक्सेल में सारांश फ़ंक्शंस का उपयोग करके बड़ी मात्रा में डेटा को आसानी से कैसे सारांशित किया जाए, लेकिन उस लेख में वर्कशीट पर सभी डेटा को ध्यान में रखा गया था। क्या होगा यदि आप केवल डेटा के एक सबसेट को देखना चाहते हैं और उस सबसेट को सारांशित करना चाहते हैं?
एक्सेल में, आप कॉलम पर फ़िल्टर बना सकते हैं जो उन पंक्तियों को छिपा देंगे जो आपके फ़िल्टर से मेल नहीं खाते। इसके अलावा, आप एक्सेल में विशेष फ़ंक्शंस का उपयोग करके केवल फ़िल्टर किए गए डेटा का उपयोग करके डेटा को सारांशित कर सकते हैं।
इस लेख में, मैं आपको एक्सेल में फ़िल्टर बनाने के चरणों और बिल्ट-इन फ़ंक्शंस का उपयोग करके उस फ़िल्टर किए गए डेटा को सारांशित करने के बारे में बताऊंगा।
एक्सेल में सरल फ़िल्टर बनाएं
एक्सेल में, आप सरल फ़िल्टर और जटिल फ़िल्टर बना सकते हैं। आइए सरल फ़िल्टर से शुरू करते हैं। फ़िल्टर के साथ काम करते समय, आपके पास शीर्ष पर एक पंक्ति होनी चाहिए जिसका उपयोग लेबल के लिए किया जाता है। इस पंक्ति का होना आवश्यक नहीं है, लेकिन यह फ़िल्टर के साथ काम करना थोड़ा आसान बना देता है।
ऊपर, मेरे पास कुछ नकली डेटा है और मैं City (शहर) कॉलम पर एक फ़िल्टर बनाना चाहता हूं। एक्सेल में, यह करना वास्तव में आसान है। आगे बढ़ें और रिबन में Data (डेटा) टैब पर क्लिक करें और फिर Filter (फ़िल्टर) बटन पर क्लिक करें। आपको शीट पर डेटा चुनने या पहली पंक्ति में क्लिक करने की आवश्यकता नहीं है।
जब आप फ़िल्टर पर क्लिक करते हैं, तो पहली पंक्ति में प्रत्येक कॉलम में सबसे दाईं ओर एक छोटा ड्रॉपडाउन बटन स्वचालित रूप से जुड़ जाएगा।
अब आगे बढ़ें और City कॉलम में ड्रॉपडाउन तीर पर क्लिक करें। आपको कुछ अलग विकल्प दिखाई देंगे, जिनकी मैं नीचे व्याख्या करूंगा।
शीर्ष पर, आप City कॉलम में मानों के आधार पर सभी पंक्तियों को तुरंत सॉर्ट कर सकते हैं। ध्यान दें कि जब आप डेटा को सॉर्ट करते हैं, तो यह पूरी पंक्ति को स्थानांतरित करेगा, न कि केवल City कॉलम में मानों को। यह सुनिश्चित करेगा कि आपका डेटा पहले की तरह ही बरकरार रहे।
इसके अलावा, सबसे सामने ID नामक एक कॉलम जोड़ना और इसे एक से लेकर आपकी वर्कशीट में जितनी पंक्तियां हैं उतनी तक नंबर देना एक अच्छा विचार है। इस तरह, यदि यह आपके लिए महत्वपूर्ण है तो आप हमेशा ID कॉलम द्वारा सॉर्ट कर सकते हैं और अपने डेटा को उसी क्रम में वापस प्राप्त कर सकते हैं जिसमें यह मूल रूप से था।
जैसा कि आप देख सकते हैं, स्प्रेडशीट में सभी डेटा अब City कॉलम में मानों के आधार पर सॉर्ट किया गया है। अभी तक, कोई पंक्ति छिपी नहीं है। अब आइए फ़िल्टर डायलॉग के नीचे चेकबॉक्स पर एक नज़र डालते हैं। मेरे उदाहरण में, मेरे पास City कॉलम में केवल तीन अद्वितीय मान हैं और वे तीन सूची में दिखाई देते हैं।
मैंने आगे बढ़कर दो शहरों को अनचेक किया और एक को चेक किया हुआ छोड़ दिया। अब मेरे पास केवल 8 पंक्तियों का डेटा दिख रहा है और बाकी छिपे हुए हैं। आप आसानी से बता सकते हैं कि आप फ़िल्टर किए गए डेटा को देख रहे हैं यदि आप far left पर पंक्ति संख्याओं की जांच करते हैं। कितनी पंक्तियां छिपी हैं, इस पर निर्भर करते हुए, आपको कुछ अतिरिक्त क्षैतिज रेखाएं दिखाई देंगी और संख्याओं का रंग नीला होगा।
अब मान लीजिए मैं परिणामों को और कम करने के लिए दूसरे कॉलम पर फ़िल्टर करना चाहता हूं। कॉलम C में, मेरे पास प्रत्येक परिवार में सदस्यों की कुल संख्या है और मैं केवल दो से अधिक सदस्यों वाले परिवारों के परिणाम देखना चाहता हूं।
आगे बढ़ें और कॉलम C में ड्रॉपडाउन तीर पर क्लिक करें और आपको कॉलम में प्रत्येक अद्वितीय मान के लिए वही चेकबॉक्स दिखाई देंगे। हालांकि, इस मामले में, हम Number Filters (संख्या फ़िल्टर) पर क्लिक करना चाहते हैं और फिर Greater Than (से अधिक) पर क्लिक करें। जैसा कि आप देख सकते हैं, कई अन्य विकल्प भी हैं।
एक नया डायलॉग पॉप अप होगा और यहां आप फ़िल्टर के लिए मान टाइप कर सकते हैं। आप AND या OR फ़ंक्शन के साथ एक से अधिक मानदंड भी जोड़ सकते हैं। आप कह सकते हैं कि आप उन पंक्तियों को चाहते हैं जहां मान 2 से अधिक है और 5 के बराबर नहीं है, उदाहरण के लिए।
अब मैं न्यू ऑरलियन्स के केवल 5 पंक्तियों के डेटा तक नीचे आ गया हूं और 3 या अधिक सदस्यों के साथ। काफी आसान? ध्यान दें कि आप ड्रॉपडाउन पर क्लिक करके और फिर Clear Filter From "Column Name" लिंक पर क्लिक करके किसी कॉलम पर फ़िल्टर को आसानी से साफ़ कर सकते हैं।
तो यह एक्सेल में सरल फ़िल्टर के लिए लगभग सब कुछ है। वे उपयोग करने में बहुत आसान हैं और परिणाम बहुत सीधे हैं। अब आइए Advanced (उन्नत) फ़िल्टर डायलॉग का उपयोग करके जटिल फ़िल्टर पर एक नज़र डालते हैं।
एक्सेल में उन्नत फ़िल्टर बनाएं
यदि आप अधिक उन्नत फ़िल्टर बनाना चाहते हैं, तो आपको Advanced फ़िल्टर डायलॉग का उपयोग करना होगा। उदाहरण के लिए, मान लीजिए मैं न्यू ऑरलियन्स में रहने वाले 2 से अधिक सदस्यों वाले सभी परिवारों OR क्लार्क्सविले में 3 से अधिक सदस्यों वाले सभी परिवारों AND केवल .EDU अंत वाले ईमेल पते वाले को देखना चाहता था। अब आप साधारण फ़िल्टर के साथ ऐसा नहीं कर सकते।
इसे करने के लिए, हमें एक्सेल शीट को थोड़ा अलग तरीके से सेटअप करने की आवश्यकता है। आगे बढ़ें और अपने डेटा सेट के ऊपर कुछ पंक्तियां डालें और शीर्षक लेबल को पहली पंक्ति में बिल्कुल वैसे ही कॉपी करें जैसे नीचे दिखाया गया है।
अब यहां बताया गया है कि उन्नत फ़िल्टर कैसे काम करते हैं। आपको पहले शीर्ष पर कॉलम में अपने मानदंड टाइप करने होंगे और फिर Data टैब पर Sort & Filter के तहत Advanced बटन पर क्लिक करना होगा।
तो वास्तव में हम उन सेल्स में क्या टाइप कर सकते हैं? ठीक है, आइए हमारे उदाहरण से शुरू करते हैं। हम केवल न्यू ऑरलियन्स या क्लार्क्सविले से डेटा देखना चाहते हैं, तो आइए उन्हें सेल्स E2 और E3 में टाइप करें।
जब आप मानों को अलग-अलग पंक्तियों पर टाइप करते हैं, तो इसका मतलब OR होता है। अब हम न्यू ऑरलियन्स के 2 से अधिक सदस्यों वाले परिवार और क्लार्क्सविले के 3 से अधिक सदस्यों वाले परिवार चाहते हैं। ऐसा करने के लिए, C2 में >2 टाइप करें और C3 में >3 टाइप करें।
चूंकि >2 और न्यू ऑरलियन्स एक ही पंक्ति पर हैं, यह एक AND ऑपरेटर होगा। ऊपर पंक्ति 3 के लिए भी यही सच है। अंत में, हम केवल .EDU अंत वाले ईमेल पते वाले परिवारों को चाहते हैं। ऐसा करने के लिए, बस D2 और D3 दोनों में *.edu टाइप करें। * प्रतीक का अर्थ है किसी भी संख्या में वर्ण।
एक बार जब आप ऐसा कर लेते हैं, तो अपने डेटासेट में कहीं भी क्लिक करें और फिर Advanced बटन पर क्लिक करें। List Range फ़ील्ड स्वचालित रूप से आपके डेटासेट का पता लगा लेगा क्योंकि आपने Advanced बटन पर क्लिक करने से पहले उसमें क्लिक किया था। अब Criteria range बटन के दाईं ओर छोटे बटन पर क्लिक करें।
A1 से E3 तक सब कुछ चुनें और फिर Advanced Filter डायलॉग पर वापस जाने के लिए उसी बटन पर फिर से क्लिक करें। OK पर क्लिक करें और आपका डेटा अब फ़िल्टर हो जाना चाहिए!
जैसा कि आप देख सकते हैं, अब मेरे पास केवल 3 परिणाम हैं जो उन सभी मानदंडों से मेल खाते हैं। ध्यान दें कि मानदंड रेंज के लेबल को डेटासेट के लेबल से बिल्कुल मेल खाना चाहिए ताकि यह काम कर सके।
आप स्पष्ट रूप से इस विधि का उपयोग करके बहुत अधिक जटिल क्वेरी बना सकते हैं, इसलिए अपने इच्छित परिणाम प्राप्त करने के लिए इसके साथ प्रयोग करें। अंत में, आइए फ़िल्टर किए गए डेटा पर योग फ़ंक्शंस लागू करने के बारे में बात करते हैं।
फ़िल्टर किए गए डेटा को सारांशित करना
अब मान लीजिए मैं अपने फ़िल्टर किए गए डेटा पर परिवार के सदस्यों की संख्या को जोड़ना चाहता हूं, मैं ऐसा कैसे करूंगा? खैर, आइए रिबन में Clear (साफ़ करें) बटन पर क्लिक करके अपना फ़िल्टर साफ़ करें। चिंता न करें, Advanced बटन पर क्लिक करके और फिर से OK पर क्लिक करके उन्नत फ़िल्टर को फिर से लागू करना बहुत आसान है।
हमारे डेटासेट के नीचे, आइए Total (कुल) नामक एक सेल जोड़ें और फिर परिवार के कुल सदस्यों को जोड़ने के लिए एक सम फ़ंक्शन जोड़ें। मेरे उदाहरण में, मैंने बस =SUM(C7:C31) टाइप किया।
तो अगर मैं सभी परिवारों को देखता हूं, तो मेरे पास कुल 78 सदस्य हैं। अब आगे बढ़ें और अपना उन्नत फ़िल्टर फिर से लागू करें और देखें क्या होता है।
अरे! सही संख्या 11 दिखाने के बजाय, मुझे अभी भी कुल 78 दिखाई दे रहा है! ऐसा क्यों है? खैर, SUM फ़ंक्शन छिपी हुई पंक्तियों को अनदेखा नहीं करता है, इसलिए यह अभी भी सभी पंक्तियों का उपयोग करके गणना कर रहा है। सौभाग्य से, कुछ फ़ंक्शंस हैं जिनका उपयोग आप छिपी हुई पंक्तियों को अनदेखा करने के लिए कर सकते हैं।
पहला है SUBTOTAL। इन विशेष फ़ंक्शंस में से किसी का उपयोग करने से पहले, आप अपना फ़िल्टर साफ़ करना चाहेंगे और फिर फ़ंक्शन टाइप करेंगे।
एक बार फ़िल्टर साफ़ हो जाने पर, आगे बढ़ें और =SUBTOTAL( टाइप करें और आपको विकल्पों का एक ड्रॉपडाउन बॉक्स दिखाई देगा। इस फ़ंक्शन का उपयोग करके, आप पहले एक संख्या का उपयोग करके जिस प्रकार का योग फ़ंक्शन चाहते हैं उसे चुनते हैं।
हमारे उदाहरण में, मैं SUM का उपयोग करना चाहता हूं, इसलिए मैं नंबर 9 टाइप करूंगा या ड्रॉपडाउन से बस उस पर क्लिक करूंगा। फिर एक कॉमा टाइप करें और सेल्स की रेंज चुनें।
जब आप एंटर दबाते हैं, तो आपको 78 का मान दिखाई देना चाहिए जैसा कि पहले था। हालांकि, यदि आप अब फ़िल्टर को फिर से लागू करते हैं, तो हमें 11 दिखाई देगा!
उत्कृष्ट! यह वही है जो हम चाहते हैं। अब आप अपने फ़िल्टर को समायोजित कर सकते हैं और मान हमेशा केवल उन पंक्तियों को प्रतिबिंबित करेगा जो वर्तमान में दिख रही हैं।
दूसरा फ़ंक्शन जो SUBTOTAL फ़ंक्शन के समान ही काम करता है वह है AGGREGATE। एकमात्र अंतर यह है कि AGGREGATE फ़ंक्शन में एक और पैरामीटर होता है जहां आपको यह निर्दिष्ट करना होता है कि आप छिपी हुई पंक्तियों को अनदेखा करना चाहते हैं।
पहला पैरामीटर वह योग फ़ंक्शन है जिसका आप उपयोग करना चाहते हैं और SUBTOTAL के साथ, 9 SUM फ़ंक्शन को दर्शाता है। दूसरा विकल्प वह है जहां आपको छिपी हुई पंक्तियों को अनदेखा करने के लिए 5 टाइप करना होता है। अंतिम पैरामीटर वही है और यह सेल्स की रेंज है।
आप AGGREGATE फ़ंक्शन और MODE, MEDIAN, AVERAGE आदि जैसे अन्य फ़ंक्शंस का उपयोग करने के तरीके के बारे में अधिक विस्तार से जानने के लिए सारांश फ़ंक्शंस पर मेरा लेख पढ़ सकते हैं।
उम्मीद है, यह लेख आपको एक्सेल में फ़िल्टर बनाने और उपयोग करने के लिए एक अच्छा प्रारंभिक बिंदु देता है। यदि आपके कोई प्रश्न हैं, तो बेझिझक टिप्पणी पोस्ट करें। आनंद लें!
