எக்செல் இல் கிடைமட்ட நெடுவரிசை வடிகட்டுதல்

நீங்கள் ஒரு புதிய பயனராக இல்லாவிட்டால், எக்செல் இல் உள்ள எல்லாவற்றிலும் 99% செங்குத்து அட்டவணைகளுடன் வேலை செய்ய வடிவமைக்கப்பட்டுள்ளது என்பதை நீங்கள் ஏற்கனவே கவனித்திருக்க வேண்டும், அங்கு அளவுருக்கள் அல்லது பண்புக்கூறுகள் (புலங்கள்) நெடுவரிசைகள் வழியாக செல்கின்றன, மேலும் பொருள்கள் அல்லது நிகழ்வுகள் பற்றிய தகவல்கள் அமைந்துள்ளன. வரிகளில். பிவோட் அட்டவணைகள், துணைத்தொகைகள், இரட்டைக் கிளிக் மூலம் சூத்திரங்களை நகலெடுப்பது - அனைத்தும் இந்த தரவு வடிவமைப்பிற்காக பிரத்யேகமாக வடிவமைக்கப்பட்டுள்ளன.

எவ்வாறாயினும், விதிவிலக்குகள் இல்லாமல் விதிகள் எதுவும் இல்லை மற்றும் ஒரு கிடைமட்ட சொற்பொருள் நோக்குநிலை கொண்ட அட்டவணை அல்லது வரிசைகள் மற்றும் நெடுவரிசைகள் ஒரே எடையைக் கொண்ட அட்டவணை வேலையில் வந்தால் என்ன செய்வது என்று என்னிடம் கேட்கப்பட்டது.

எக்செல் இன்னும் கிடைமட்டமாக வரிசைப்படுத்துவது எப்படி என்று தெரிந்தால் (கட்டளையுடன் தரவு - வரிசைப்படுத்துதல் - விருப்பங்கள் - நெடுவரிசைகளை வரிசைப்படுத்தவும்), பின்னர் வடிகட்டுதலின் நிலைமை மோசமாக உள்ளது - எக்செல் இல் வரிசைகள் அல்ல, நெடுவரிசைகளை வடிகட்டுவதற்கு உள்ளமைக்கப்பட்ட கருவிகள் எதுவும் இல்லை. எனவே, நீங்கள் அத்தகைய பணியை எதிர்கொண்டால், பல்வேறு அளவிலான சிக்கலான தீர்வுகளை நீங்கள் கொண்டு வர வேண்டும்.

முறை 1. புதிய FILTER செயல்பாடு

நீங்கள் எக்செல் 2021 இன் புதிய பதிப்பு அல்லது எக்செல் 365 சந்தாவில் இருந்தால், புதிதாக அறிமுகப்படுத்தப்பட்ட அம்சத்தைப் பயன்படுத்திக் கொள்ளலாம் வடிகட்டி (வடிகட்டி), இது மூலத் தரவை வரிசைகள் மூலம் மட்டுமல்ல, நெடுவரிசைகள் மூலமாகவும் வடிகட்ட முடியும். வேலை செய்ய, இந்தச் செயல்பாட்டிற்கு ஒரு துணை கிடைமட்ட ஒரு பரிமாண வரிசை-வரிசை தேவைப்படுகிறது, இதில் ஒவ்வொரு மதிப்பும் (சரி அல்லது தவறு) அட்டவணையில் அடுத்த நெடுவரிசையைக் காட்டலாமா அல்லது மறைக்கிறதா என்பதை தீர்மானிக்கிறது.

எங்கள் அட்டவணையின் மேலே பின்வரும் வரியைச் சேர்த்து, அதில் ஒவ்வொரு நெடுவரிசையின் நிலையை எழுதுவோம்:

• நாம் எப்போதும் முதல் மற்றும் கடைசி நெடுவரிசைகளை (தலைப்புகள் மற்றும் மொத்தங்கள்) காட்ட விரும்புகிறோம், எனவே வரிசையின் முதல் மற்றும் கடைசி கலங்களில் மதிப்பு = TRUE ஐ அமைப்போம்.
• மீதமுள்ள நெடுவரிசைகளுக்கு, தொடர்புடைய கலங்களின் உள்ளடக்கங்கள் செயல்பாடுகளைப் பயன்படுத்தி நமக்குத் தேவையான நிலையைச் சரிபார்க்கும் சூத்திரமாக இருக்கும். И (மற்றும்) or OR (அல்லது). எடுத்துக்காட்டாக, மொத்தம் 300 முதல் 500 வரையிலான வரம்பில் உள்ளது.

அதன் பிறகு, செயல்பாட்டைப் பயன்படுத்த மட்டுமே உள்ளது வடிகட்டி எங்கள் துணை அணிக்கு உண்மையான மதிப்பு உள்ள நெடுவரிசைகளைத் தேர்ந்தெடுக்க:

இதேபோல், கொடுக்கப்பட்ட பட்டியலின் மூலம் நெடுவரிசைகளை வடிகட்டலாம். இந்த வழக்கில், செயல்பாடு உதவும் COUNTIF (COUNTIF), அனுமதிக்கப்பட்ட பட்டியலில் உள்ள அட்டவணையின் தலைப்பிலிருந்து அடுத்த நெடுவரிசைப் பெயரின் நிகழ்வுகளின் எண்ணிக்கையை இது சரிபார்க்கிறது:

முறை 2. வழக்கமான அட்டவணைக்கு பதிலாக பிவோட் அட்டவணை

தற்போது, ​​எக்செல் பைவட் டேபிள்களில் மட்டுமே நெடுவரிசைகள் மூலம் கிடைமட்ட வடிகட்டலை உள்ளமைத்துள்ளது, எனவே எங்கள் அசல் அட்டவணையை பைவட் டேபிளாக மாற்ற முடிந்தால், இந்த உள்ளமைக்கப்பட்ட செயல்பாட்டைப் பயன்படுத்தலாம். இதைச் செய்ய, எங்கள் மூல அட்டவணை பின்வரும் நிபந்தனைகளை பூர்த்தி செய்ய வேண்டும்:

• வெற்று மற்றும் ஒன்றிணைக்கப்பட்ட கலங்கள் இல்லாமல் "சரியான" ஒரு-வரி தலைப்புக் கோட்டை வைத்திருங்கள் - இல்லையெனில் அது ஒரு பைவட் அட்டவணையை உருவாக்க வேலை செய்யாது;
• வரிசைகள் மற்றும் நெடுவரிசைகளின் லேபிள்களில் நகல்களைக் கொண்டிருக்க வேண்டாம் - அவை சுருக்கத்தில் தனித்துவமான மதிப்புகளின் பட்டியலில் "சரிந்துவிடும்";
• மதிப்புகளின் வரம்பில் (வரிசைகள் மற்றும் நெடுவரிசைகளின் குறுக்குவெட்டில்) எண்களை மட்டுமே கொண்டுள்ளது, ஏனெனில் பிவோட் அட்டவணை நிச்சயமாக ஒருவிதமான ஒருங்கிணைப்பு செயல்பாட்டைப் பயன்படுத்துகிறது (தொகை, சராசரி, முதலியன) மேலும் இது உரையுடன் வேலை செய்யாது.

இந்த நிபந்தனைகள் அனைத்தும் பூர்த்தி செய்யப்பட்டால், எங்கள் அசல் அட்டவணையைப் போல தோற்றமளிக்கும் ஒரு பைவட் அட்டவணையை உருவாக்க, அது (அசல்) கிராஸ்டாப்பில் இருந்து ஒரு தட்டையானதாக (இயல்பாக்கப்பட்டது) விரிவாக்கப்பட வேண்டும். இதைச் செய்வதற்கான எளிதான வழி, 2016 முதல் எக்செல் இல் கட்டமைக்கப்பட்ட சக்திவாய்ந்த தரவு மாற்றக் கருவியான பவர் வினவல் ஆட்-இன் ஆகும். 

இவை:

1. அட்டவணையை “ஸ்மார்ட்” டைனமிக் கட்டளையாக மாற்றுவோம் முகப்பு - அட்டவணையாக வடிவமைக்கவும் (முகப்பு - அட்டவணையாக வடிவமைக்கவும்).
2. கட்டளையுடன் பவர் வினவலில் ஏற்றப்படுகிறது தரவு - அட்டவணை / வரம்பிலிருந்து (தரவு - அட்டவணை / வரம்பிலிருந்து).
3. வரியை மொத்தத்துடன் வடிகட்டுகிறோம் (சுருக்கமானது அதன் சொந்த மொத்தங்களைக் கொண்டிருக்கும்).
4. முதல் நெடுவரிசை தலைப்பில் வலது கிளிக் செய்து தேர்ந்தெடுக்கவும் மற்ற நெடுவரிசைகளை சுருக்கவும் (பிற நெடுவரிசைகளை விலக்கு). தேர்ந்தெடுக்கப்படாத அனைத்து நெடுவரிசைகளும் இரண்டாக மாற்றப்படுகின்றன - பணியாளரின் பெயர் மற்றும் அவரது குறிகாட்டியின் மதிப்பு.
5. நெடுவரிசையில் சென்ற மொத்த எண்ணிக்கையுடன் நெடுவரிசையை வடிகட்டுதல் கற்பிதம்.
6. கட்டளையுடன் விளைந்த பிளாட் (இயல்பான) அட்டவணையின் படி ஒரு பிவோட் அட்டவணையை உருவாக்குகிறோம் முகப்பு — மூடு மற்றும் ஏற்று — மூடு மற்றும் ஏற்று… (வீடு - மூடவும் & ஏற்றவும் - மூடு & ஏற்றவும்...).

இப்போது நீங்கள் பைவட் அட்டவணைகளில் கிடைக்கும் நெடுவரிசைகளை வடிகட்டுவதற்கான திறனைப் பயன்படுத்தலாம் - பெயர்கள் மற்றும் உருப்படிகளுக்கு முன்னால் உள்ள வழக்கமான சரிபார்ப்புக்குறிகள் கையொப்ப வடிப்பான்கள் (லேபிள் வடிகட்டிகள்) or மதிப்பின்படி வடிகட்டுகிறது (மதிப்பு வடிகட்டிகள்):

நிச்சயமாக, தரவை மாற்றும்போது, ​​எங்கள் வினவலையும் சுருக்கத்தையும் கீபோர்டு ஷார்ட்கட் மூலம் புதுப்பிக்க வேண்டும். ctrl+alt+F5 அல்லது குழு தரவு - அனைத்தையும் புதுப்பிக்கவும் (தரவு - அனைத்தையும் புதுப்பிக்கவும்).

முறை 3. VBA இல் மேக்ரோ

முந்தைய முறைகள் அனைத்தும், நீங்கள் எளிதாகப் பார்க்க முடியும், சரியாக வடிகட்டவில்லை - அசல் பட்டியலில் உள்ள நெடுவரிசைகளை நாங்கள் மறைக்கவில்லை, ஆனால் அசல் ஒன்றிலிருந்து கொடுக்கப்பட்ட நெடுவரிசைகளுடன் புதிய அட்டவணையை உருவாக்குகிறோம். மூலத் தரவில் உள்ள நெடுவரிசைகளை வடிகட்ட (மறைக்க) தேவைப்பட்டால், அடிப்படையில் வேறுபட்ட அணுகுமுறை தேவை, அதாவது மேக்ரோ.

மஞ்சள் செல் A4 இல் குறிப்பிடப்பட்டுள்ள முகமூடியை அட்டவணைத் தலைப்பில் உள்ள மேலாளரின் பெயர் திருப்திப்படுத்தும் இடத்தில் நெடுவரிசைகளை வடிகட்ட விரும்புகிறோம் என்று வைத்துக்கொள்வோம், எடுத்துக்காட்டாக, "A" என்ற எழுத்தில் தொடங்குகிறது (அதாவது, "Anna" மற்றும் "Arthur ஐப் பெறுங்கள். " அதன் விளைவாக). 

முதல் முறையைப் போலவே, நாங்கள் முதலில் ஒரு துணை வரம்பு-வரிசையை செயல்படுத்துகிறோம், அங்கு ஒவ்வொரு கலத்திலும் எங்கள் அளவுகோல் ஒரு சூத்திரத்தால் சரிபார்க்கப்படும் மற்றும் முறையே புலப்படும் மற்றும் மறைக்கப்பட்ட நெடுவரிசைகளுக்கு தர்க்க மதிப்புகள் உண்மை அல்லது தவறு காட்டப்படும்:

பின்னர் ஒரு எளிய மேக்ரோவைச் சேர்ப்போம். தாள் தாவலில் வலது கிளிக் செய்து கட்டளையைத் தேர்ந்தெடுக்கவும் மூல (மூல குறியீடு). திறக்கும் சாளரத்தில் பின்வரும் VBA குறியீட்டை நகலெடுத்து ஒட்டவும்:

தனிப்பட்ட துணைப் பணித்தாள்_மாற்றம்(வரம்பிற்குள் இலக்கு) என்றால் Target.Address = "$A$4" பின்னர் வரம்பில் உள்ள ஒவ்வொரு கலத்திற்கும்("D2:O2") செல் = உண்மை எனில் செல்.EntireColumn.Hidden = False மற்ற செல் = True End என்றால் அடுத்த செல் End என்றால் End Sub  

அதன் தர்க்கம் பின்வருமாறு:

• பொதுவாக, இது ஒரு நிகழ்வு நடத்துபவர் பணித்தாள்_மாற்றம், அதாவது இந்த மேக்ரோ, தற்போதைய தாளில் உள்ள எந்த கலத்திலும் ஏதேனும் மாற்றம் ஏற்பட்டால் தானாகவே இயங்கும்.
• மாற்றப்பட்ட கலத்தின் குறிப்பு எப்போதும் மாறியில் இருக்கும் இலக்கு.
• முதலில், பயனர் கலத்தை (A4) அளவுகோலுடன் சரியாக மாற்றிவிட்டாரா என்பதை நாங்கள் சரிபார்க்கிறோம் - இது ஆபரேட்டரால் செய்யப்படுகிறது. if.
• பின்னர் சுழற்சி தொடங்குகிறது ஒவ்வொரு… ஒவ்வொரு நெடுவரிசைக்கும் TRUE / FALSE காட்டி மதிப்புகளுடன் சாம்பல் கலங்களை (D2:O2) மீண்டும் மீண்டும் செய்ய.
• அடுத்த சாம்பல் கலத்தின் மதிப்பு TRUE (உண்மை) எனில், நெடுவரிசை மறைக்கப்படவில்லை, இல்லையெனில் அதை மறைக்கிறோம் (சொத்து மறைக்கப்பட்ட).

•  Office 365 இலிருந்து மாறும் வரிசை செயல்பாடுகள்: FILTER, SORT மற்றும் UNIC
• பவர் வினவலைப் பயன்படுத்தி மல்டிலைன் ஹெடருடன் பிவோட் டேபிள்
• மேக்ரோக்கள் என்றால் என்ன, அவற்றை எவ்வாறு உருவாக்குவது மற்றும் பயன்படுத்துவது