धुरी तालिका शीर्ष 10 फ़िल्टर दिखाई पंक्तियों की कुल देता है
पिवट टेबल एक शीर्ष 10 फिल्टर प्रदान करते हैं। यह अच्छा है। यह लचीला है। लेकिन मैं इससे नफरत करता हूं, और मैं आपको बताऊंगा कि क्यों।
यहां ग्राहक द्वारा राजस्व दर्शाने वाली एक पिवट टेबल है। राजस्व कुल $ 6.7 मिलियन है।
क्या होगा अगर मेरे प्रबंधक के पास एक सुनहरी मछली का ध्यान है और केवल शीर्ष पांच ग्राहकों को देखना चाहता है?
शुरू करने के लिए, ए 3 में ड्रॉपडाउन खोलें और मूल्य फिल्टर, शीर्ष 10 का चयन करें।
सुपर-फ्लेक्सिबल टॉप 10 फ़िल्टर डायलॉग टॉप / बॉटम की अनुमति देता है। यह 10, 5, या कोई अन्य संख्या कर सकता है। आप शीर्ष पाँच वस्तुओं, शीर्ष 80%, या पर्याप्त ग्राहकों से $ 5 मिलियन प्राप्त करने के लिए कह सकते हैं।
लेकिन यहाँ समस्या यह है: परिणामी रिपोर्ट में पाँच ग्राहकों और कुल ग्राहकों से कुल के बजाय सभी से ग्राहक दिखाए गए हैं।
लेकिन पहले, ऑटोफ़िल्टर के बारे में कुछ महत्वपूर्ण शब्द
मुझे लगता है कि यह एक दीवार के सवाल की तरह लगता है। यदि आप एक नियमित डेटा सेट पर फ़िल्टर ड्रॉपडाउन चालू करना चाहते हैं, तो आप इसे कैसे करते हैं? यहाँ तीन सामान्य तरीके हैं:
- अपने डेटा में एक सेल चुनें और डेटा टैब पर फ़िल्टर आइकन पर क्लिक करें।
- अपने सभी डेटा को Ctrl + * के साथ चुनें और डेटा टैब पर फ़िल्टर आइकन पर क्लिक करें।
- तालिका के रूप में डेटा को प्रारूपित करने के लिए Ctrl + T दबाएँ।
ये तीन बहुत अच्छे तरीके हैं। जब तक आप उनमें से किसी को भी जानते हैं, तब तक दूसरा तरीका जानने की कोई आवश्यकता नहीं है। लेकिन यहाँ फिल्टर पर बारी करने के लिए एक अविश्वसनीय अस्पष्ट लेकिन जादुई तरीका है:
- हेडर की अपनी पंक्ति पर जाएं, सबसे दाईं ओर स्थित सेल पर जाएं। एक सेल को दाईं ओर ले जाएं। किसी अज्ञात कारण से, जब आप इस सेल में होते हैं और फ़िल्टर आइकन पर क्लिक करते हैं, तो एक्सेल आपके बाईं ओर स्थित डेटा को फ़िल्टर करता है। मुझे नहीं पता कि यह क्यों काम करता है। यह वास्तव में बात करने लायक नहीं है क्योंकि फ़िल्टर ड्रॉपडाउन को चालू करने के लिए पहले से ही तीन बहुत अच्छे तरीके हैं। मैं इस सेल को मैजिक सेल कहता हूं।
और अब, वापस धुरी टेबल्स में…
इसलिए, एक नियम है जो कहता है कि जब आप एक धुरी तालिका में होते हैं तो आप ऑटोफ़िल्टर का उपयोग नहीं कर सकते हैं। नीचे देखें? फ़िल्टर आइकन धूसर हो गया है क्योंकि मैंने धुरी तालिका में एक सेल का चयन किया है।
मैंने वास्तव में कभी नहीं सोचा कि Microsoft इसे क्यों समाप्त करता है। यह कुछ आंतरिक होना चाहिए जो कहता है कि AutoFilter और एक पिवट तालिका सह-अस्तित्व नहीं कर सकती है। तो, एक्सेल टीम पर कोई है जो फ़िल्टर आइकन को धूसर करने के आरोप में है। उस व्यक्ति ने मैजिक सेल के बारे में कभी नहीं सुना। धुरी तालिका में एक सेल का चयन करें, और फ़िल्टर धूसर हो जाता है। धुरी तालिका के बाहर क्लिक करें, और फ़िल्टर फिर से सक्षम है।
पर रुको। मैजिक सेल के बारे में मैंने अभी आपको किस बारे में बताया? यदि आप अंतिम शीर्ष के दाईं ओर सेल में क्लिक करते हैं, तो Excel फ़िल्टर आइकन को धूसर करना भूल जाता है!
निश्चित रूप से पर्याप्त है, एक्सेल आपके पिवट टेबल की शीर्ष पंक्ति में ऑटिफ़िल्टर ड्रॉपडाउन जोड़ता है। और AutoFilter धुरी टेबल फिल्टर की तुलना में अलग तरीके से संचालित होता है। राजस्व ड्रॉपडाउन पर जाएँ और नंबर फ़िल्टर चुनें, शीर्ष 10…
शीर्ष 10 ऑटोफ़िल्टर संवाद में, शीर्ष 6 आइटम चुनें। यह एक टाइपो नहीं है … यदि आप पांच ग्राहक चाहते हैं, तो 6. चुनें यदि आप 10 ग्राहक चाहते हैं, तो 11 चुनें।
ऑटिफ़िल्टर के लिए, भव्य कुल पंक्ति डेटा में सबसे बड़ी वस्तु है। शीर्ष पांच ग्राहक डेटा में 6 के माध्यम से 2 पदों पर कब्जा कर रहे हैं।
सावधान
स्पष्ट रूप से, आप इस चाल के साथ एक्सेल के कपड़े में एक छेद फाड़ रहे हैं। यदि आप बाद में अंतर्निहित डेटा को बदलते हैं और अपनी धुरी तालिका को ताज़ा करते हैं, तो Excel फ़िल्टर को ताज़ा नहीं करेगा, क्योंकि जहाँ तक Microsoft को पता है, वहाँ एक धुरी तालिका पर फ़िल्टर लागू करने का कोई तरीका नहीं है!
ध्यान दें
हमारा लक्ष्य इसे Microsoft से गुप्त रखना है, क्योंकि यह एक बहुत अच्छी सुविधा है। यह काफी समय से "टूटा हुआ" है, इसलिए बहुत सारे लोग हैं जो अब तक इस पर भरोसा कर रहे हैं।
एक्सेल 2013+ में पूरी तरह से कानूनी समाधानयदि आप एक पिवट टेबल चाहते हैं जो आपको शीर्ष पांच ग्राहक दिखाती है लेकिन सभी ग्राहकों से कुल मिलाकर, आपको अपना डेटा एक्सेल से बाहर ले जाना होगा। यदि आपके पास Excel 2013 या 2016 है, तो ऐसा करने का एक बहुत ही सुविधाजनक तरीका है। आपको यह दिखाने के लिए, मैंने मूल धुरी तालिका को हटा दिया है। इंसर्ट, पिवट टेबल चुनें। OK पर क्लिक करने से पहले, बॉक्स को चुनें जो डेटा मॉडल में इस डेटा को जोड़ें।
अपनी पिवट टेबल को सामान्य बनाएं। मान फ़िल्टर, शीर्ष 10 का चयन करने के लिए A3 में ड्रॉपडाउन का उपयोग करें, और शीर्ष पांच ग्राहकों के लिए पूछें। चयनित धुरी तालिका में एक सेल के साथ, रिबन में डिज़ाइन टैब पर जाएं और सबटोटल्स ड्रॉपडाउन खोलें। ड्रॉपडाउन में अंतिम पसंद टोटल में फिल्टर्ड आइटम शामिल हैं। आम तौर पर, यह विकल्प धूसर हो जाता है। लेकिन क्योंकि डेटा एक सामान्य धुरी कैश के बजाय डेटा मॉडल में संग्रहीत है, यह विकल्प अब उपलब्ध है।
कुल विकल्प में शामिल फ़िल्टर किए गए आइटम चुनें, और आपके ग्रैंड टोटल में अब एक तारांकन और कुल डेटा शामिल है।
यह चाल मूल रूप से फिलाडेल्फिया में मेरे सेमिनार में डैन से आई थी। इस सुविधा का सुझाव देने के लिए मिगुएल कैबलेरो को धन्यवाद।
वीडियो देखेंा
- धुरी तालिका शीर्ष 10 फ़िल्टर दिखाई पंक्तियों की कुल देता है
- टोटल्स में फिल्टर्ड आइटम शामिल करें ग्रेयड आउट
- जादू सेल से डेटा फ़िल्टर को लागू करने का अजीब तरीका
- डेटा फ़िल्टर को पिवट तालिकाओं में अनुमति नहीं है
- एक्सेल मैजिक सेल से डेटा फ़िल्टर को ग्रे करने में विफल रहता है
- शीर्ष 6 के लिए पूछें शीर्ष 5 प्लस ग्रैंड कुल प्राप्त करें
- एक विशिष्ट धुरी आइटम द्वारा फ़िल्टरिंग के लिए उपयोगी
- एक्सेल 2013 या नया: ट्रू टोटल पाने का अलग तरीका
- डेटा मॉडल के माध्यम से अपना डेटा भेजें
- टोटल में फिल्टर्ड आइटम शामिल करें
- तारांकन के साथ कुल प्राप्त करें
- मैंने यह ट्रिक 10+ साल पहले Dan से Philadelphia में सीखी थी
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट के लिए एक्सेल सीखें, एपिसोड 1999 - पिवट टेबल ट्रू टॉप फाइव
मैं इस पूरी किताब को पॉडकास्ट कर रहा हूं। एक प्लेलिस्ट है, उस प्लेलिस्ट का अनुसरण करने के लिए मैं दाएं हाथ के शीर्ष कोने पर क्लिक करता हूं। नेटकास्ट में आपका स्वागत है। मैं बिल जेलन हूं।
ठीक है, इसलिए हम एक पिवट टेबल बनाने जा रहे हैं और हम सभी ग्राहकों को नहीं, बल्कि सिर्फ शीर्ष पांच ग्राहकों को दिखाना चाहते हैं। INSERT, धुरी तालिका। ठीक है, मैं ग्राहक को बाईं ओर नीचे और राजस्व डाल दूंगा। ठीक है तो यहाँ हमारे ग्राहकों की पूरी सूची 6.7 मिलियन डॉलर के रूप में उल्लेखित है। एक्सेल, एक शीर्ष पांच करना आसान बनाता है। पंक्ति लेबल, मूल्य फ़िल्टर में जाएं, शीर्ष 10. शीर्ष होना आवश्यक नहीं है। यह ऊपर या नीचे हो सकता है। पाँच नहीं होना चाहिए यह बीस, चालीस हो सकता है, जो कुछ भी हो सकता है। अस्सी प्रतिशत, मुझे तीन मिलियन डॉलर या चार मिलियन डॉलर पाने के लिए पर्याप्त रिकॉर्ड दें, लेकिन यहां हम जाते हैं। शीर्ष पांच आइटम। अब 6.7 मिलियन डॉलर याद रखें, ओके पर क्लिक करें और यहाँ मेरी बड़ी समस्या यह है कि, यह कुल मिलाकर 6.7 मिलियन नहीं है। जब मैं इसे बिक्री के वीपी को देता हूं, तो वह कहता है कि एक दूसरे का इंतजार करो।मुझे पता है कि मैंने 3.3 मिलियन डॉलर से अधिक किया। ठीक है, इसलिए हम पूर्ववत करने जा रहे हैं, इसे पूर्ववत करें और मूल डेटा पर वापस जाएं।
अब यह अगली चाल मैंने फिलाडेल्फिया में अपने एक पावर एक्सेल सेमिनार के दौरान सीखी। पंक्ति दो में डैन नाम के एक व्यक्ति ने मुझे यह दिखाया। दस साल से अधिक समय पहले उसने मुझे यह चाल दिखाई थी, और पहले हमें फिल्टर्स के बारे में बात करनी थी। आम तौर पर, यदि आप नियमित रूप से फ़िल्टर का उपयोग करने जा रहे हैं, तो यह फ़िल्टर यहाँ, आप अपने डेटा सेट में किसी एक सेल को चुनते हैं और फ़िल्टर आइकन पर क्लिक करते हैं, या कुछ लोग संपूर्ण डेटा सेट, नियंत्रण * का चयन करते हैं और फ़िल्टर आइकन पर क्लिक करते हैं। लेकिन तीसरा रास्ता है। एक ऐसा तरीका जिसकी किसी को परवाह नहीं है। यदि आप मेरे मामले में बहुत अंतिम हेडिंग सेल में जाते हैं, तो इसकी लागत L1 में है, और एक सेल दाईं ओर जाएं। मैं इसे जादू की सेल कहता हूं, मुझे पता नहीं क्यों लेकिन किसी अज्ञात कारण से, इस सेल से, मैं आसन्न डेटा सेट को फ़िल्टर कर सकता हूं। ठीक है, यह एक अजीब तरीका है और कोई भी इस बारे में परवाह नहीं करता है।
ठीक है, क्योंकि फ़िल्टर को लागू करने के दो अन्य बहुत अच्छे तरीके हैं, किसी को भी जादू सेल के बारे में जानने की ज़रूरत नहीं है, लेकिन यहाँ एक बात है, एक पिवट टेबल के अंदर देखें, यह धूसर हो गया है। आपको उन फ़िल्टर का उपयोग करने की अनुमति नहीं है। यह नियमों के खिलाफ है। अब, अगर मैं यहाँ आता हूँ, तो मैं फ़िल्टर का उपयोग करने के लिए स्वागत से अधिक हूँ लेकिन अंदर वे बाहर ग्रेड करते हैं। मुझे नहीं पता कि वह व्यक्ति कौन है जो इसे बाहर निकालता है, लेकिन उन्होंने जादू की सेल के बारे में मेरी छोटी सी बात कभी नहीं सुनी है, क्योंकि अगर मैं बहुत आखिरी हेडिंग सेल में जाता हूं और एक सेल को दाईं ओर जाता हूं, तो उसे देखें, वे फ़िल्टर को धूसर करना भूल जाते हैं और अब मैंने पुराने ऑटो फिल्टर को पिवट टेबल में जोड़ दिया है। इसलिए मैं यहां आया, नंबर फिल्टर्स पर गया, जो वैल्यू फिल्टर्स से अलग है। इसे अभी भी टॉप टेन कहा जाता है। थोड़ा अलग, मैं शीर्ष पांच के लिए पूछने जा रहा हूं, कोई शीर्ष छह नहीं।शीर्ष छह के कारण इस फ़िल्टर के लिए ग्रैंड टोटल एक और पंक्ति है, और ग्रैंड टोटल सबसे बड़ा आइटम है और फिर आइटम 2 के लिए 6 के माध्यम से पूछे जाने पर, मुझे शीर्ष पांच आइटम मिलते हैं।
ठीक है, इसलिए हम वहां हैं। एक अच्छा फ़िल्टर हैक, जो हमें शीर्ष पांच आइटम और हर किसी का सही कुल देता है। अब ठीक है, कुछ बातें। जादू सेल के बारे में मत भूलना। ठीक है, इस फ़िल्टर को बंद करने का कोई तरीका नहीं है, जब तक कि आप जादू सेल में वापस नहीं जाते हैं। ठीक है, इसलिए आपको जादू सेल को याद रखने की आवश्यकता है। इसके अलावा, यदि आप अंतर्निहित डेटा को बदलते हैं और आप पिवट टेबल को रिफ्रेश करते हैं, तो वे फ़िल्टर को रीफ़्रेश नहीं करने वाले हैं क्योंकि जहां तक Microsoft को पता है, आपको फ़िल्टर करने की अनुमति नहीं है।
यह अन्य चीजों के लिए उपयोगी है। कभी-कभी हमारे पास शीर्ष पर जाने वाले उत्पाद होते हैं। आइए यहां एक सारणीबद्ध रूप में जाएं। आवश्यक नहीं, मैं सिर्फ वास्तविक शीर्षकों को प्राप्त करना पसंद करता हूं। Gizmo, विजेट, गैजेट्स, Doodads। ठीक है और हो सकता है कि आप Doodads के प्रबंधक हों और आपको केवल उन ग्राहकों को देखना होगा जिनके पास एक विशेष मूल्य और Doodads था। इसलिए मैं मैजिक सेल में जाता हूं, फ़िल्टर चालू करता हूं और फिर डूडाड्स के तहत मैं उन वस्तुओं के लिए पूछ सकता हूं जो शून्य से अधिक हैं। ओके पर क्लिक करें। ठीक है, उस प्रकार की फ़िल्टरिंग एक नियमित पिवट टेबल पर संभव नहीं होगी, लेकिन मैजिक सेल का उपयोग करना संभव है।
ठीक है अब सूची को पूर्ववत करें। चलिए इस फ़िल्टर को बंद करते हैं और पिवट टेबल को हटाते हैं, और यदि आप एक्सेल 2013 या नए हैं, तो मैं आपको नीचे दिए गए सही कुल प्राप्त करने के लिए पूरी तरह से कानूनी रास्ता दिखाने जा रहा हूं। पिवट टेबल डालें, नीचे नीचे यहाँ, Excel 2013 में शुरू होने वाला यह बहुत ही सहज बॉक्स, बहुत रोमांचक नहीं लगता है, इस डेटा को डेटा मॉडल में जोड़ें। वह डेटा को, पावर पिवट इंजन को पर्दे के पीछे भेजता है। ठीक उसी रिपोर्ट का निर्माण करें। ग्राहक बाईं ओर नीचे की ओर। पिवट टेबल के दिल में राजस्व। फिर, रेगुलर फिल्टर्स पर जाएं, वैल्यू फिल्टर्स टॉप 10. टॉप फाइव के लिए पूछें। सूचना फिर से हमारे पास 6.7 मिलियन डॉलर है जब मैं ऐसा करता हूं, 3.3 मिलियन डॉलर लेकिन यहां अंतर है। जब मैं सबटोटल्स के तहत डिज़ाइन टैब पर जाता हूं, तो यह फीचर टोटल में फ़िल्टर किए गए आइटम को सम्मिलित करता है,अब धूसर नहीं है। एक नियमित पिवट टेबल पर उपलब्ध नहीं है। हमें वहां थोड़ा सा तार मिलता है और यह सब कुछ है। ठीक है, अब निश्चित रूप से केवल एक्सेल 2013 या नए में काम करता है।
ठीक है कि मुझे इस पूरी पुस्तक को YouTube पर यहां लाने में छह सप्ताह लगने वाले हैं। यहाँ बहुत सारे अच्छे सुझाव हैं। युक्तियाँ जो आपको समय बचाने के लिए शुरू कर सकती हैं, तुरंत। अभी पूरी किताब खरीदें और आप सभी 40 तक पहुंच पाएंगे, यह वास्तव में 40 से अधिक युक्तियां हैं। एक्सेल शॉर्टकट की। इस पुस्तक में सभी प्रकार के महान सामान।
ठीक है, पुनर्कथन। इसलिए जब हम एक पिवट टेबल टॉप 10 फ़िल्टर करते हैं, तो यह हमें कुल लेकिन केवल दृश्य पंक्तियाँ देता है, न कि वह सामग्री जो इसे फ़िल्टर करती है। हाँ, अगर हम दूसरे टैब पर जाते हैं और सबटोटल्स, फ़िल्टर्ड आइटम और टोटल्स की तलाश करते हैं, तो यह धूसर हो जाता है, लेकिन मैजिक सेल से ओल्ड डेटा फ़िल्टर को इनवॉइस करने का एक अजीब तरीका है। बहुत अंतिम शीर्षक सेल, दाईं ओर एक सेल पर जाएँ, आप फ़िल्टर्स और पिवट टेबल्स का उपयोग नहीं कर सकते हैं, लेकिन यदि आप मैजिक सेल में जाते हैं तो वे इसे ग्रे करना भूल जाते हैं। अब नंबर फ़िल्टर में, आप शीर्ष छह के लिए शीर्ष पांच पाने के लिए कहते हैं, साथ ही कुल भव्य। किसी विशिष्ट पिवट आइटम को फ़िल्टर करने के लिए भी उपयोगी है: डूडैड, कुछ भी जो डूडैड में 0 से अधिक था या शीर्ष 5 डूडैड। Excel 2013 या नया, ट्रू टोटल प्राप्त करने का एक अलग तरीका है।डेटा मॉडल के लिए उस बॉक्स की जाँच करें और फिर फ़िल्टर में फ़िल्टर किए गए आइटम शामिल होंगे। आपको तारांकन के साथ कुल मिलता है। और फिलाडेल्फिया में डैन को धन्यवाद जिन्होंने दस साल से अधिक समय पहले मुझे अपने पावर एक्सेल सेमिनार में से एक में दिखाया, और मुझे यह महान छोटी चाल दी। क्लब पिवट टेबल वॉल के माध्यम से फ़िल्टर करने का एक तरीका है। वे आम तौर पर उस ऑटो फ़िल्टर की अनुमति नहीं देते हैं।
अरे, मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार, MRExcel के एक और नेटकास्ट के लिए देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast1999.xlsx
