एक्सेलमा तेर्सो स्तम्भ फिल्टरिङ

यदि तपाईं एकदम नौसिखिया प्रयोगकर्ता हुनुहुन्न भने, तपाईंले पहिले नै याद गर्नु भएको छ कि एक्सेलमा सबै चीजको 99% ठाडो तालिकाहरूसँग काम गर्न डिजाइन गरिएको छ, जहाँ प्यारामिटरहरू वा विशेषताहरू (फिल्डहरू) स्तम्भहरू मार्फत जान्छन्, र वस्तुहरू वा घटनाहरूको बारेमा जानकारी अवस्थित छ। लाइनहरूमा। पिभोट तालिकाहरू, उप-योगहरू, एक डबल क्लिकको साथ सूत्रहरू प्रतिलिपि गर्ने - सबै कुरा विशेष रूपमा यो डेटा ढाँचाको लागि अनुकूल छ।

जे होस्, त्यहाँ अपवाद बिना कुनै नियमहरू छैनन् र पर्याप्त नियमित फ्रिक्वेन्सीको साथ मलाई के गर्ने भनेर सोधिएको छ यदि तेर्सो सिमेन्टिक अभिमुखीकरण भएको तालिका, वा पङ्क्ति र स्तम्भहरूको अर्थमा समान तौल भएको तालिका, कार्यमा आए:

र यदि एक्सेलले अझै पनि तेर्सो रूपमा क्रमबद्ध गर्ने तरिका थाहा छ (कमाण्डको साथ डेटा - क्रमबद्ध गर्नुहोस् - विकल्पहरू - स्तम्भहरू क्रमबद्ध गर्नुहोस्), तब फिल्टरिङको अवस्था झन् खराब छ - त्यहाँ केवल स्तम्भहरू फिल्टर गर्नका लागि निर्मित उपकरणहरू छैनन्, एक्सेलमा पङ्क्तिहरू छैनन्। त्यसोभए, यदि तपाइँ यस्तो कार्यको सामना गर्दै हुनुहुन्छ भने, तपाइँ जटिलता को विभिन्न डिग्री को workarounds संग आउन हुनेछ।

विधि 1. नयाँ FILTER प्रकार्य

यदि तपाइँ Excel 2021 को नयाँ संस्करण वा Excel 365 को सदस्यतामा हुनुहुन्छ भने, तपाइँ भर्खरै प्रस्तुत गरिएको सुविधाको फाइदा लिन सक्नुहुन्छ FILTER (फिल्टर), जसले स्रोत डेटालाई पङ्क्तिहरूद्वारा मात्र होइन, स्तम्भहरूद्वारा पनि फिल्टर गर्न सक्छ। काम गर्नको लागि, यो प्रकार्यलाई सहायक तेर्सो एक-आयामी एरे-पङ्क्ति चाहिन्छ, जहाँ प्रत्येक मान (TRUE वा FALSE) ले निर्धारण गर्छ कि हामीले तालिकामा अर्को स्तम्भ देखाउने वा लुकाउने छौं।

हाम्रो तालिका माथि निम्न रेखा थप्नुहोस् र यसमा प्रत्येक स्तम्भको स्थिति लेख्नुहोस्:

• मानौं हामी सधैं पहिलो र अन्तिम स्तम्भहरू (हेडर र योगफल) प्रदर्शन गर्न चाहन्छौं, त्यसैले तिनीहरूका लागि एरेको पहिलो र अन्तिम कक्षहरूमा हामीले मान = TRUE सेट गर्छौं।
• बाँकी स्तम्भहरूका लागि, सम्बन्धित कक्षहरूको सामग्रीहरू एक सूत्र हुनेछ जसले हामीलाई प्रकार्यहरू प्रयोग गरेर आवश्यक अवस्था जाँच गर्दछ। И (र) or OR (OR)। उदाहरण को लागी, कुल 300 देखि 500 ​​को दायरामा छ।

त्यस पछि, यो केवल प्रकार्य प्रयोग गर्न बाँकी छ FILTER हाम्रो सहायक एरेको TRUE मान माथि रहेको स्तम्भहरू चयन गर्न:

त्यसै गरी, तपाइँ दिइएको सूची द्वारा स्तम्भहरू फिल्टर गर्न सक्नुहुन्छ। यस अवस्थामा, प्रकार्यले मद्दत गर्नेछ COUNTIF (COUNTIF), जसले अनुमति दिइएको सूचीमा तालिका हेडरबाट अर्को स्तम्भ नामको घटनाहरूको संख्या जाँच गर्दछ:

विधि २. सामान्यको सट्टा पिभोट तालिका

हाल, एक्सेलले पिभोट तालिकाहरूमा मात्र स्तम्भहरूद्वारा निर्मित तेर्सो फिल्टरिङ छ, त्यसैले यदि हामीले हाम्रो मूल तालिकालाई पिभोट तालिकामा रूपान्तरण गर्न व्यवस्थापन गर्छौं भने, हामी यो अन्तर्निहित कार्यक्षमता प्रयोग गर्न सक्छौं। यो गर्नको लागि, हाम्रो स्रोत तालिकाले निम्न सर्तहरू पूरा गर्नुपर्छ:

• खाली र मर्ज गरिएका कक्षहरू बिना "सही" एक-लाइन हेडर लाइन छ - अन्यथा यसले पिभोट तालिका बनाउन काम गर्दैन;
• पङ्क्तिहरू र स्तम्भहरूको लेबलहरूमा नक्कलहरू समावेश नगर्नुहोस् - तिनीहरू केवल अद्वितीय मानहरूको सूचीमा सारांशमा "संकुचित" हुनेछन्;
• मानहरूको दायरामा मात्र संख्याहरू समावेश गर्दछ (पङ्क्ति र स्तम्भहरूको प्रतिच्छेदनमा), किनभने पिभोट तालिकाले निश्चित रूपमा तिनीहरूमा कुनै प्रकारको समग्र प्रकार्य लागू गर्नेछ (योग, औसत, आदि) र यसले पाठसँग काम गर्दैन।

यदि यी सबै सर्तहरू पूरा भएमा, हाम्रो मूल तालिका जस्तो देखिने पिभोट तालिका बनाउनको लागि, यसलाई (मूल एउटा) लाई क्रसट्याबबाट समतल (सामान्यीकृत) मा विस्तार गर्न आवश्यक छ। र यो गर्ने सबैभन्दा सजिलो तरिका Power Query add-in को साथ हो, एक्सेलमा 2016 देखि निर्मित शक्तिशाली डेटा रूपान्तरण उपकरण। 

यी हुन्:

1. तालिकालाई "स्मार्ट" डायनामिक आदेशमा रूपान्तरण गरौं गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा).
2. आदेशको साथ पावर क्वेरीमा लोड गर्दै डेटा - तालिका / दायराबाट (डेटा - तालिका / दायराबाट).
3. हामी योगको साथ रेखा फिल्टर गर्छौं (सारांशको आफ्नै योग हुनेछ)।
4. पहिलो स्तम्भ शीर्षकमा दायाँ क्लिक गर्नुहोस् र चयन गर्नुहोस् अन्य स्तम्भहरू संकुचित गर्नुहोस् (अन्य स्तम्भहरू अनपिभोट गर्नुहोस्)। सबै गैर-चयनित स्तम्भहरू दुईमा रूपान्तरण हुन्छन् - कर्मचारीको नाम र उसको सूचकको मूल्य।
5. स्तम्भमा गएका योगहरूको साथ स्तम्भ फिल्टर गर्दै विशेषताहरु.
6. हामी आदेशको साथ नतिजा समतल (सामान्यीकृत) तालिका अनुसार पिभोट तालिका बनाउँछौं घर - बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ... (घर - बन्द र लोड - बन्द र लोड गर्न ...).

अब तपाइँ पिभोट तालिकाहरूमा उपलब्ध स्तम्भहरू फिल्टर गर्ने क्षमता प्रयोग गर्न सक्नुहुन्छ - नाम र वस्तुहरूको अगाडि सामान्य चेकमार्कहरू हस्ताक्षर फिल्टरहरू (लेबल फिल्टरहरू) or मूल्य अनुसार फिल्टर (मान फिल्टरहरू):

र निस्सन्देह, डाटा परिवर्तन गर्दा, तपाईंले हाम्रो क्वेरी र किबोर्ड सर्टकटको साथ सारांश अद्यावधिक गर्न आवश्यक हुनेछ। Ctrl+alt+F5 वा टोली डाटा - सबै ताजा गर्नुहोस् (डेटा - सबै ताजा गर्नुहोस्).

विधि 3. VBA मा म्याक्रो

तपाईंले सजिलै देख्न सक्नुहुने सबै अघिल्ला विधिहरू ठ्याक्कै फिल्टरिङ छैनन् - हामी मूल सूचीमा स्तम्भहरू लुकाउँदैनौं, तर मूलबाट स्तम्भहरूको दिइएको सेटको साथ नयाँ तालिका बनाउँछौं। यदि यो स्रोत डेटामा स्तम्भहरू फिल्टर (लुकाउन) आवश्यक छ भने, एक मौलिक रूपमा फरक दृष्टिकोण आवश्यक छ, अर्थात्, एक म्याक्रो।

मानौं हामी फ्लाईमा स्तम्भहरू फिल्टर गर्न चाहन्छौं जहाँ तालिका हेडरमा प्रबन्धकको नामले पहेंलो सेल A4 मा निर्दिष्ट मास्कलाई सन्तुष्ट गर्दछ, उदाहरणका लागि, अक्षर "A" (अर्थात, "Ana" र "आर्थर" प्राप्त गर्नुहोस्। " फलस्वरूप)। 

पहिलो विधिमा जस्तै, हामीले पहिलो पटक सहायक दायरा-पङ्क्ति लागू गर्छौं, जहाँ प्रत्येक कक्षमा हाम्रो मापदण्डलाई सूत्रद्वारा जाँच गरिनेछ र तार्किक मानहरू TRUE वा FALSE क्रमशः देखिने र लुकेका स्तम्भहरूका लागि प्रदर्शित हुनेछन्:

त्यसपछि एउटा साधारण म्याक्रो थपौं। पाना ट्याबमा दायाँ क्लिक गर्नुहोस् र आदेश चयन गर्नुहोस् मुहान (स्रोत कोड)। खुल्ने विन्डोमा निम्न VBA कोड प्रतिलिपि गरेर टाँस्नुहोस्:

निजी उप कार्यपत्र_परिवर्तन(दायित्वको रूपमा लक्ष्य) यदि लक्ष्य। ठेगाना = "$A$4" त्यसपछि दायराको प्रत्येक कक्षको लागि ("D2:O2") यदि कक्ष = True त्यसपछि cell.EntireColumn.Hidden = False अन्य cell।EntireColumn.Hidden = True End यदि अर्को सेल End यदि End Sub  

यसको तर्क निम्नानुसार छ:

• सामान्यतया, यो एक घटना ह्यान्डलर हो Worksheet_Change, अर्थात् यो म्याक्रो हालको पानाको कुनै पनि सेलमा कुनै पनि परिवर्तनमा स्वचालित रूपमा चल्नेछ।
• परिवर्तन गरिएको कक्षको सन्दर्भ सधैं चरमा हुनेछ लक्ष्य.
• पहिले, हामी जाँच गर्छौं कि प्रयोगकर्ताले मापदण्ड (A4) को साथ सेललाई ठीक परिवर्तन गरेको छ - यो अपरेटर द्वारा गरिन्छ। if.
• त्यसपछि चक्र सुरु हुन्छ प्रत्येकको लागी… प्रत्येक स्तम्भको लागि TRUE / FALSE सूचक मानहरूसँग खैरो कक्षहरू (D2:O2) मा दोहोर्याउन।
• यदि अर्को ग्रे सेलको मान TRUE (true) हो भने, तब स्तम्भ लुकाइएको छैन, अन्यथा हामी यसलाई लुकाउँछौं (गुण लुकाइएको).

•  Office 365 बाट डायनामिक एरे प्रकार्यहरू: FILTER, SORT, र UNIC
• पावर क्वेरी प्रयोग गरी बहुलाइन हेडर भएको पिभोट तालिका
• म्याक्रोहरू के हुन्, तिनीहरूलाई कसरी सिर्जना गर्ने र प्रयोग गर्ने