यदि आप काफी नौसिखिए उपयोगकर्ता नहीं हैं, तो आपने पहले ही देखा होगा कि एक्सेल में 99% सब कुछ ऊर्ध्वाधर तालिकाओं के साथ काम करने के लिए डिज़ाइन किया गया है, जहां पैरामीटर या विशेषताएँ (फ़ील्ड) कॉलम के माध्यम से जाती हैं, और वस्तुओं या घटनाओं के बारे में जानकारी स्थित होती है। पंक्तियों में। पिवट टेबल, सबटोटल, डबल क्लिक के साथ फ़ार्मुलों की प्रतिलिपि बनाना - सब कुछ इस डेटा प्रारूप के लिए विशेष रूप से तैयार किया गया है।
हालांकि, अपवादों के बिना कोई नियम नहीं हैं और काफी नियमित आवृत्ति के साथ मुझसे पूछा जाता है कि क्या करना है यदि एक क्षैतिज अर्थ अभिविन्यास वाली तालिका, या एक तालिका जहां पंक्तियों और स्तंभों का अर्थ समान वजन है, काम में आया:
और अगर एक्सेल अभी भी क्षैतिज रूप से सॉर्ट करना जानता है (कमांड के साथ डेटा - सॉर्ट करें - विकल्प - कॉलम सॉर्ट करें), तो फ़िल्टरिंग के साथ स्थिति बदतर है - स्तंभों को फ़िल्टर करने के लिए कोई अंतर्निहित उपकरण नहीं हैं, एक्सेल में पंक्तियाँ नहीं हैं। इसलिए, यदि आप इस तरह के कार्य का सामना कर रहे हैं, तो आपको जटिलता की अलग-अलग डिग्री के वर्कअराउंड के साथ आना होगा।
विधि 1. नया फ़िल्टर फ़ंक्शन
यदि आप Excel 2021 के नए संस्करण या Excel 365 सदस्यता पर हैं, तो आप नई पेश की गई सुविधा का लाभ उठा सकते हैं फ़िल्टर (फ़िल्टर), जो स्रोत डेटा को न केवल पंक्तियों द्वारा, बल्कि स्तंभों द्वारा भी फ़िल्टर कर सकता है। काम करने के लिए, इस फ़ंक्शन को एक सहायक क्षैतिज एक-आयामी सरणी-पंक्ति की आवश्यकता होती है, जहां प्रत्येक मान (TRUE या FALSE) निर्धारित करता है कि हम तालिका में अगला कॉलम दिखाते हैं या नहीं।
आइए हमारी तालिका के ऊपर निम्न पंक्ति जोड़ें और उसमें प्रत्येक कॉलम की स्थिति लिखें:
- मान लीजिए कि हम हमेशा पहले और आखिरी कॉलम (हेडर और टोटल) प्रदर्शित करना चाहते हैं, इसलिए उनके लिए एरे के पहले और आखिरी सेल में हम मान = TRUE सेट करते हैं।
- शेष स्तंभों के लिए, संबंधित कोशिकाओं की सामग्री एक सूत्र होगी जो उस स्थिति की जांच करती है जिसकी हमें फ़ंक्शन का उपयोग करने की आवश्यकता होती है И (तथा) or OR (या). उदाहरण के लिए, कि कुल 300 से 500 की सीमा में है।
उसके बाद, यह केवल फ़ंक्शन का उपयोग करने के लिए रहता है फ़िल्टर उन स्तंभों का चयन करने के लिए जिनके ऊपर हमारे सहायक सरणी का TRUE मान है:
इसी तरह, आप किसी दी गई सूची के अनुसार कॉलम फ़िल्टर कर सकते हैं। इस मामले में, फ़ंक्शन मदद करेगा COUNTIF (काउंटिफ), जो अनुमत सूची में तालिका शीर्षलेख से अगले कॉलम नाम की घटनाओं की संख्या की जांच करता है:
विधि 2. सामान्य के बजाय पिवट टेबल
वर्तमान में, एक्सेल में केवल पिवट टेबल में कॉलम द्वारा बिल्ट-इन हॉरिजॉन्टल फ़िल्टरिंग है, इसलिए यदि हम अपनी मूल तालिका को पिवट टेबल में बदलने का प्रबंधन करते हैं, तो हम इस अंतर्निहित कार्यक्षमता का उपयोग कर सकते हैं। ऐसा करने के लिए, हमारी स्रोत तालिका को निम्नलिखित शर्तों को पूरा करना होगा:
- खाली और मर्ज किए गए सेल के बिना "सही" एक-पंक्ति हेडर लाइन है - अन्यथा यह पिवट टेबल बनाने के लिए काम नहीं करेगा;
- पंक्तियों और स्तंभों के लेबल में डुप्लीकेट शामिल न हों - वे सारांश में केवल अद्वितीय मानों की सूची में "संक्षिप्त" हो जाएंगे;
- मानों की श्रेणी (पंक्तियों और स्तंभों के चौराहे पर) में केवल संख्याएँ होती हैं, क्योंकि पिवट तालिका निश्चित रूप से उन पर किसी प्रकार का एकत्रीकरण कार्य लागू करेगी (योग, औसत, आदि) और यह पाठ के साथ काम नहीं करेगा
यदि इन सभी शर्तों को पूरा किया जाता है, तो हमारी मूल तालिका की तरह दिखने वाली पिवट टेबल बनाने के लिए, इसे (मूल एक) क्रॉसस्टैब से एक फ्लैट (सामान्यीकृत) में विस्तारित करने की आवश्यकता होगी। और ऐसा करने का सबसे आसान तरीका Power Query ऐड-इन है, जो 2016 से एक्सेल में निर्मित एक शक्तिशाली डेटा परिवर्तन उपकरण है।
ये हैं:
- आइए तालिका को "स्मार्ट" डायनेमिक कमांड में बदलें होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें).
- आदेश के साथ पावर क्वेरी में लोड हो रहा है डेटा - टेबल / रेंज से (डेटा - टेबल / रेंज से).
- हम योग के साथ रेखा को फ़िल्टर करते हैं (सारांश का अपना योग होगा)।
- पहले कॉलम हेडिंग पर राइट-क्लिक करें और चुनें अन्य स्तंभों को छोटा करें (अन्य कॉलम अनपिवट करें). सभी गैर-चयनित कॉलम दो में परिवर्तित हो जाते हैं - कर्मचारी का नाम और उसके संकेतक का मूल्य।
- कॉलम में गए कुल योग के साथ कॉलम को फ़िल्टर करना विशेषता.
- हम कमांड के साथ परिणामी फ्लैट (सामान्यीकृत) तालिका के अनुसार एक पिवट टेबल बनाते हैं होम - बंद करें और लोड करें - बंद करें और लोड करें ... (होम - बंद करें और लोड करें - बंद करें और लोड करें ...).
अब आप पिवट टेबल में उपलब्ध कॉलम को फ़िल्टर करने की क्षमता का उपयोग कर सकते हैं - नामों और वस्तुओं के सामने सामान्य चेकमार्क हस्ताक्षर फ़िल्टर (लेबल फ़िल्टर) or मान के अनुसार फ़िल्टर करें (मान फ़िल्टर):
और निश्चित रूप से, डेटा बदलते समय, आपको हमारी क्वेरी और सारांश को कीबोर्ड शॉर्टकट से अपडेट करना होगा कंट्रोल+ऑल्ट+F5 या टीम डेटा - सभी को ताज़ा करें (डेटा - सभी को ताज़ा करें).
विधि 3. वीबीए में मैक्रो
पिछली सभी विधियां, जैसा कि आप आसानी से देख सकते हैं, बिल्कुल फ़िल्टरिंग नहीं हैं - हम मूल सूची में कॉलम छुपाते नहीं हैं, लेकिन मूल से कॉलम के दिए गए सेट के साथ एक नई तालिका बनाते हैं। यदि स्रोत डेटा में स्तंभों को फ़िल्टर करना (छिपाना) आवश्यक है, तो एक मौलिक रूप से भिन्न दृष्टिकोण की आवश्यकता है, अर्थात् मैक्रो।
मान लीजिए कि हम फ्लाई पर कॉलम फ़िल्टर करना चाहते हैं जहां टेबल हेडर में मैनेजर का नाम पीले सेल ए 4 में निर्दिष्ट मास्क को संतुष्ट करता है, उदाहरण के लिए, "ए" अक्षर से शुरू होता है (यानी, "अन्ना" और "आर्थर प्राप्त करें" " नतीजतन)।
पहली विधि की तरह, हम पहले एक सहायक श्रेणी-पंक्ति को लागू करते हैं, जहाँ प्रत्येक सेल में हमारे मानदंड को एक सूत्र द्वारा जाँचा जाएगा और तार्किक मान TRUE या FALSE क्रमशः दृश्यमान और छिपे हुए कॉलम के लिए प्रदर्शित किए जाएंगे:
तो चलिए एक साधारण मैक्रो जोड़ते हैं। शीट टैब पर राइट-क्लिक करें और कमांड चुनें स्रोत (सोर्स कोड). खुलने वाली विंडो में निम्न VBA कोड को कॉपी और पेस्ट करें:
निजी उप वर्कशीट_चेंज (रेंज के रूप में लक्ष्य लक्ष्य) यदि लक्ष्य। पता = "$ ए $ 4" तो रेंज में प्रत्येक सेल के लिए ("डी 2: ओ 2") यदि सेल = सही है तो सेल। संपूर्ण कॉलम। छुपा = झूठा अन्य सेल। संपूर्ण कॉलम। छुपा हुआ = ट्रू एंड अगर अगला सेल खत्म होता है तो एंड सब इसका तर्क इस प्रकार है:
- सामान्य तौर पर, यह एक ईवेंट हैंडलर है वर्कशीट_बदलें, यानी यह मैक्रो वर्तमान शीट पर किसी भी सेल में किसी भी बदलाव पर स्वचालित रूप से चलेगा।
- बदले गए सेल का संदर्भ हमेशा वेरिएबल में रहेगा लक्ष्य.
- सबसे पहले, हम जांचते हैं कि उपयोगकर्ता ने मानदंड (ए 4) के साथ सेल को बिल्कुल बदल दिया है - यह ऑपरेटर द्वारा किया जाता है if.
- फिर शुरू होता है सिलसिला प्रत्येक के लिए… प्रत्येक कॉलम के लिए TRUE / FALSE संकेतक मानों के साथ ग्रे सेल (D2: O2) पर पुनरावृति करने के लिए।
- यदि अगले ग्रे सेल का मान TRUE (सत्य) है, तो कॉलम छिपा नहीं है, अन्यथा हम इसे छिपाते हैं (संपत्ति छिपा हुआ).
- Office 365 से गतिशील सरणी कार्य: फ़िल्टर, सॉर्ट, और UNIC
- Power Query का उपयोग करके मल्टीलाइन हेडर वाली पिवट टेबल
- मैक्रोज़ क्या हैं, कैसे बनाएं और उनका उपयोग कैसे करें