जर तुम्ही अगदी नवशिक्या वापरकर्ते नसाल तर तुमच्या लक्षात आले असेल की एक्सेलमधील 99% प्रत्येक गोष्ट उभ्या सारण्यांसह कार्य करण्यासाठी डिझाइन केलेली आहे, जिथे पॅरामीटर्स किंवा विशेषता (फील्ड) स्तंभांमधून जातात आणि वस्तू किंवा घटनांबद्दल माहिती असते. ओळींमध्ये पिव्होट टेबल्स, सबटोटल, डबल क्लिकने फॉर्म्युले कॉपी करणे – सर्वकाही या डेटा फॉरमॅटसाठी खास तयार केले आहे.
तथापि, अपवादांशिवाय कोणतेही नियम नाहीत आणि बर्‍याच नियमित वारंवारतेसह मला विचारले जाते की क्षैतिज शब्दार्थ अभिमुखता असलेली सारणी किंवा पंक्ती आणि स्तंभांचा अर्थ समान वजन असलेले सारणी कामात आढळल्यास काय करावे:
आणि जर एक्सेलला अजूनही क्षैतिज क्रमवारी कशी लावायची हे माहित असेल (कमांडसह डेटा – क्रमवारी – पर्याय – क्रमवारी स्तंभ), तर फिल्टरिंगची परिस्थिती आणखी वाईट आहे – कॉलम फिल्टर करण्यासाठी कोणतीही अंगभूत साधने नाहीत, Excel मध्ये पंक्ती नाहीत. म्हणून, जर तुम्हाला अशा कार्याचा सामना करावा लागला असेल, तर तुम्हाला वेगवेगळ्या प्रकारच्या जटिलतेच्या उपायांसह यावे लागेल.
पद्धत 1. नवीन FILTER कार्य
तुम्ही Excel 2021 च्या नवीन आवृत्तीवर किंवा Excel 365 सदस्यत्वावर असल्यास, तुम्ही नव्याने सादर केलेल्या वैशिष्ट्याचा लाभ घेऊ शकता FILTER (फिल्टर), जे स्त्रोत डेटा केवळ पंक्तीद्वारेच नव्हे तर स्तंभांद्वारे देखील फिल्टर करू शकते. कार्य करण्यासाठी, या फंक्शनला सहाय्यक क्षैतिज एक-आयामी अॅरे-पंक्ती आवश्यक आहे, जिथे प्रत्येक मूल्य (TRUE किंवा FALSE) निर्धारित करते की आम्ही टेबलमध्ये पुढील स्तंभ दर्शवू किंवा लपवू.
आपल्या सारणीच्या वर खालील ओळ जोडू आणि त्यामध्ये प्रत्येक स्तंभाची स्थिती लिहू:
- समजा आम्हाला नेहमी पहिले आणि शेवटचे कॉलम (हेडर आणि बेरीज) दाखवायचे आहेत, म्हणून अॅरेच्या पहिल्या आणि शेवटच्या सेलमध्ये आम्ही मूल्य = TRUE सेट करतो.
- उर्वरित स्तंभांसाठी, संबंधित पेशींची सामग्री एक सूत्र असेल जी आपल्याला फंक्शन्स वापरून आवश्यक स्थिती तपासते. И (आणि) or OR (किंवा). उदाहरणार्थ, एकूण 300 ते 500 च्या श्रेणीत आहे.
त्यानंतर, ते फंक्शन वापरण्यासाठीच राहते FILTER आमच्या सहाय्यक अॅरेचे खरे मूल्य असलेले स्तंभ निवडण्यासाठी:
त्याचप्रमाणे, तुम्ही दिलेल्या सूचीनुसार स्तंभ फिल्टर करू शकता. या प्रकरणात, फंक्शन मदत करेल COUNTIF (COUNTIF), जे अनुमत सूचीमधील सारणी शीर्षलेखावरून पुढील स्तंभ नावाच्या घटनांची संख्या तपासते:
पद्धत 2. नेहमीच्या ऐवजी मुख्य सारणी
सध्या, एक्सेलमध्ये फक्त पिव्होट टेबल्समध्ये कॉलम्सद्वारे बिल्ट-इन क्षैतिज फिल्टरिंग आहे, म्हणून जर आम्ही आमच्या मूळ टेबलला पिव्होट टेबलमध्ये रूपांतरित करू शकलो, तर आम्ही ही अंगभूत कार्यक्षमता वापरू शकतो. हे करण्यासाठी, आमच्या स्त्रोत सारणीने खालील अटी पूर्ण केल्या पाहिजेत:
- रिक्त आणि विलीन केलेल्या सेलशिवाय "योग्य" एक-लाइन शीर्षलेख ओळ आहे – अन्यथा ते मुख्य सारणी तयार करण्यासाठी कार्य करणार नाही;
- पंक्ती आणि स्तंभांच्या लेबलमध्ये डुप्लिकेट असू नका - ते केवळ अद्वितीय मूल्यांच्या सूचीमध्ये सारांशात "संकुचित" होतील;
- मूल्यांच्या श्रेणीमध्ये (पंक्ती आणि स्तंभांच्या छेदनबिंदूवर) फक्त संख्या समाविष्ट आहेत, कारण मुख्य सारणी निश्चितपणे त्यांना काही प्रकारचे एकत्रित कार्य लागू करेल (बेरजे, सरासरी, इ.) आणि हे मजकूरासह कार्य करणार नाही.
या सर्व अटी पूर्ण झाल्यास, आमच्या मूळ सारणीसारखे दिसणारे मुख्य सारणी तयार करण्यासाठी, ते (मूळ) क्रॉसटॅबमधून सपाट (सामान्यीकृत) मध्ये विस्तारित करणे आवश्यक आहे. आणि हे करण्याचा सर्वात सोपा मार्ग म्हणजे Power Query अॅड-इन, 2016 पासून Excel मध्ये तयार केलेले शक्तिशाली डेटा ट्रान्सफॉर्मेशन टूल.
हे आहेत:
- चला टेबलचे रूपांतर “स्मार्ट” डायनॅमिक कमांडमध्ये करू मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित).
- कमांडसह पॉवर क्वेरीमध्ये लोड करत आहे डेटा – टेबल / रेंज वरून (डेटा – टेबल / रेंज वरून).
- आम्ही बेरीजसह ओळ फिल्टर करतो (सारांशाची स्वतःची बेरीज असेल).
- पहिल्या स्तंभाच्या शीर्षकावर उजवे-क्लिक करा आणि निवडा इतर स्तंभ अनकोलॅप करा (अन्य स्तंभ अनपिव्हट करा). सर्व न निवडलेले स्तंभ दोनमध्ये रूपांतरित केले जातात - कर्मचाऱ्याचे नाव आणि त्याच्या निर्देशकाचे मूल्य.
- कॉलममध्ये गेलेल्या बेरीजसह कॉलम फिल्टर करणे विशेषता.
- कमांडसह परिणामी सपाट (सामान्यीकृत) सारणीनुसार आम्ही पिव्होट टेबल तयार करतो मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि लोड करा… (घर — बंद करा आणि लोड करा — बंद करा आणि लोड करा...).
आता तुम्ही पिव्होट टेबलमध्ये उपलब्ध कॉलम फिल्टर करण्याची क्षमता वापरू शकता – नावे आणि आयटमच्या समोर नेहमीचे चेकमार्क स्वाक्षरी फिल्टर (लेबल फिल्टर) or मूल्यानुसार फिल्टर करा (मूल्य फिल्टर):
आणि अर्थातच, डेटा बदलताना, तुम्हाला आमची क्वेरी आणि सारांश कीबोर्ड शॉर्टकटसह अपडेट करावा लागेल. Ctrl+alt+F5 किंवा संघ डेटा - सर्व रिफ्रेश करा (डेटा — सर्व रिफ्रेश करा).
पद्धत 3. VBA मध्ये मॅक्रो
मागील सर्व पद्धती, जसे आपण सहजपणे पाहू शकता, अचूकपणे फिल्टर करत नाही – आम्ही मूळ सूचीमधील स्तंभ लपवत नाही, परंतु मूळ सूचीमधील स्तंभांच्या दिलेल्या संचासह एक नवीन सारणी तयार करतो. स्त्रोत डेटामधील स्तंभ फिल्टर (लपविणे) आवश्यक असल्यास, मूलभूतपणे भिन्न दृष्टीकोन आवश्यक आहे, म्हणजे, मॅक्रो.
समजा आपल्याला फ्लायवर कॉलम फिल्टर करायचे आहेत जेथे टेबल हेडरमधील व्यवस्थापकाचे नाव पिवळ्या सेल A4 मध्ये निर्दिष्ट केलेल्या मुखवटाचे समाधान करते, उदाहरणार्थ, "A" अक्षराने सुरू होते (म्हणजे "अण्णा" आणि "आर्थर" मिळवा "परिणामी).
पहिल्या पद्धतीप्रमाणे, आम्ही प्रथम सहायक श्रेणी-पंक्ती लागू करतो, जिथे प्रत्येक सेलमध्ये आमचा निकष सूत्राद्वारे तपासला जाईल आणि दृश्यमान आणि लपविलेल्या स्तंभांसाठी अनुक्रमे TRUE किंवा FALSE ही तार्किक मूल्ये प्रदर्शित केली जातील:
मग एक साधा मॅक्रो टाकू. शीट टॅबवर उजवे-क्लिक करा आणि कमांड निवडा स्रोत (मूळ सांकेतिक शब्दकोश). उघडणाऱ्या विंडोमध्ये खालील VBA कोड कॉपी आणि पेस्ट करा:
खाजगी सब वर्कशीट_बदला(श्रेणीनुसार टार्गेट) जर लक्ष्य.पत्ता = "$A$4" तर रेंजमधील प्रत्येक सेलसाठी("D2:O2") सेल = खरे असल्यास सेल.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub त्याचे तर्कशास्त्र खालीलप्रमाणे आहे.
- सर्वसाधारणपणे, हा इव्हेंट हँडलर आहे वर्कशीट_बदला, म्हणजे वर्तमान शीटवरील कोणत्याही सेलमधील कोणत्याही बदलावर हा मॅक्रो आपोआप चालेल.
- बदललेल्या सेलचा संदर्भ नेहमी व्हेरिएबलमध्ये असेल लक्ष्य.
- प्रथम, आम्ही तपासतो की वापरकर्त्याने निकष (A4) सह सेल अचूक बदलला आहे - हे ऑपरेटरद्वारे केले जाते if.
- मग चक्र सुरू होते प्रत्येकासाठी… प्रत्येक स्तंभासाठी TRUE/FALSE निर्देशक मूल्यांसह राखाडी पेशींवर (D2:O2) पुनरावृत्ती करणे.
- पुढील ग्रे सेलचे मूल्य TRUE (सत्य) असल्यास, स्तंभ लपविला जात नाही, अन्यथा आपण ते लपवू (गुणधर्म लपलेली).
- Office 365 मधील डायनॅमिक अॅरे फंक्शन्स: FILTER, SORT आणि UNIC
- पॉवर क्वेरी वापरून मल्टीलाइन हेडरसह मुख्य सारणी
- मॅक्रो काय आहेत, ते कसे तयार करावे आणि कसे वापरावे