சூத்திரங்களுடன் மொத்த உரை மாற்றீடு

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

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

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

என்ன செய்ய? வளைந்த உரையை 100500 முறை கைமுறையாக "கண்டுபிடித்து மாற்றவும்" பெட்டியின் மூலம் அல்லது கிளிக் செய்வதன் மூலம் சரியானதை மாற்ற வேண்டாம் ctrl+H?

அத்தகைய சூழ்நிலையில் முதலில் நினைவுக்கு வருவது, தவறான மற்றும் சரியான விருப்பங்களைப் பொருத்துவதற்கு முன்பே தொகுக்கப்பட்ட குறிப்பு புத்தகத்தின் படி வெகுஜன மாற்றீடு செய்வது - இது போன்றது:

துரதிர்ஷ்டவசமாக, அத்தகைய பணியின் வெளிப்படையான பரவலுடன், மைக்ரோசாஃப்ட் எக்செல் அதைத் தீர்ப்பதற்கான எளிய உள்ளமைக்கப்பட்ட முறைகளைக் கொண்டிருக்கவில்லை. தொடங்குவதற்கு, VBA அல்லது பவர் வினவலில் மேக்ரோக்கள் வடிவில் "கனரக பீரங்கிகளை" ஈடுபடுத்தாமல், சூத்திரங்கள் மூலம் இதை எப்படி செய்வது என்று கண்டுபிடிப்போம்.

வழக்கு 1. மொத்தமாக முழு மாற்றீடு

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

எங்களிடம் இரண்டு அட்டவணைகள் உள்ளன என்று வைத்துக்கொள்வோம்:

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

வசதிக்காக:

• இரண்டு அட்டவணைகளும் விசைப்பலகை குறுக்குவழியைப் பயன்படுத்தி டைனமிக் ("ஸ்மார்ட்") ஆக மாற்றப்படுகின்றன ctrl+T அல்லது குழு செருகு - அட்டவணை (செருகு - அட்டவணை).
  
• தோன்றும் தாவலில் கன்ஸ்ட்ரக்டர் (வடிவமைப்பு) பெயரிடப்பட்ட முதல் அட்டவணை தேதி, மற்றும் இரண்டாவது குறிப்பு அட்டவணை - பதிலீடுகள்.

சூத்திரத்தின் தர்க்கத்தை விளக்க, சற்று தூரத்திலிருந்து செல்லலாம்.

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

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

இங்குள்ள தந்திரம் என்னவென்றால், நாங்கள் ஒன்றை அல்ல, பல மதிப்புகளை முதல் வாதமாக குறிப்பிட்டுள்ளதால், இந்த செயல்பாடும் ஒரு மதிப்பாக அல்ல, ஆனால் 3 கூறுகளின் வரிசையாக திரும்பும். டைனமிக் வரிசைகளை ஆதரிக்கும் Office 365 இன் சமீபத்திய பதிப்பு உங்களிடம் இல்லையென்றால், இந்த சூத்திரத்தை உள்ளிட்டு கிளிக் செய்யவும் உள்ளிடவும் தாளில் இந்த வரிசையை நீங்கள் காண்பீர்கள்:

உங்களிடம் எக்செல் முந்தைய பதிப்புகள் இருந்தால், கிளிக் செய்த பிறகு உள்ளிடவும் முடிவு வரிசையிலிருந்து முதல் மதிப்பை மட்டுமே காண்போம், அதாவது பிழை #VALUE! (#மதிப்பு!).

நீங்கள் பயப்பட வேண்டாம் 🙂 உண்மையில், எங்கள் ஃபார்முலா வேலை செய்கிறது மற்றும் நீங்கள் ஃபார்முலா பட்டியில் உள்ளிடப்பட்ட செயல்பாட்டைத் தேர்ந்தெடுத்து விசையை அழுத்தினால் முடிவுகளின் முழு வரிசையையும் பார்க்கலாம் F9(அழுத்த மறக்க வேண்டாம் escசூத்திரத்திற்குச் செல்ல:

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

இப்போது நமது சூத்திரத்தில் ஒரு செயல்பாட்டைச் சேர்ப்போம் பார்வை(தேடுதல்):

இந்த செயல்பாடு மூன்று வாதங்களைக் கொண்டுள்ளது:

1. விரும்பிய மதிப்பு - நீங்கள் போதுமான பெரிய எண்ணைப் பயன்படுத்தலாம் (முக்கிய விஷயம் என்னவென்றால், மூலத் தரவில் உள்ள எந்த உரையின் நீளத்தையும் மீறுகிறது)
   
2. பார்க்கப்பட்ட_வெக்டர் - நாம் விரும்பிய மதிப்பைத் தேடும் வரம்பு அல்லது வரிசை. முன்பு அறிமுகப்படுத்தப்பட்ட செயல்பாடு இங்கே கண்டுபிடிக்க, இது ஒரு வரிசையை வழங்கும் {#VALUE!:4:#VALUE!}
   
3. திசையன்_முடிவு - தேவையான மதிப்பு தொடர்புடைய கலத்தில் காணப்பட்டால், மதிப்பை திரும்பப் பெற விரும்பும் வரம்பு. நெடுவரிசையிலிருந்து சரியான பெயர்கள் இங்கே பதிலாக எங்கள் குறிப்பு அட்டவணை.
   

இங்கே முக்கிய மற்றும் வெளிப்படையான அம்சம் என்னவென்றால், செயல்பாடு பார்வை சரியான பொருத்தம் இல்லை என்றால், எப்போதும் அருகிலுள்ள சிறிய (முந்தைய) மதிப்பைத் தேடுகிறது. எனவே, எந்த ஒரு பெரிய எண்ணையும் (உதாரணமாக, 9999) விரும்பிய மதிப்பாகக் குறிப்பிடுவதன் மூலம், நாங்கள் கட்டாயப்படுத்துவோம் பார்வை {#VALUE!:4:#VALUE!} வரிசையில் உள்ள மிகச்சிறிய எண்ணைக் கொண்ட (4) கலத்தைக் கண்டறிந்து, முடிவு திசையனிலிருந்து தொடர்புடைய மதிப்பை, அதாவது நெடுவரிசையில் இருந்து சரியான நிறுவனத்தின் பெயரைத் தரவும் பதிலாக.

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

அவ்வளவுதான். உங்களுக்கு லாஜிக் கிடைக்கும் என்று நம்புகிறேன்.

முடிக்கப்பட்ட சூத்திரத்தை நெடுவரிசையின் முதல் செல் B2 க்கு மாற்ற இது உள்ளது நிலையானது - மற்றும் எங்கள் பணி தீர்க்கப்பட்டது!

நிச்சயமாக, சாதாரண (ஸ்மார்ட் அல்ல) அட்டவணைகளுடன், இந்த சூத்திரமும் சிறப்பாக செயல்படுகிறது (விசையை மறந்துவிடாதீர்கள் F4 மற்றும் தொடர்புடைய இணைப்புகளை சரிசெய்தல்):

வழக்கு 2. மொத்த பகுதி மாற்று

இந்த வழக்கு கொஞ்சம் தந்திரமானது. மீண்டும் எங்களிடம் இரண்டு "ஸ்மார்ட்" அட்டவணைகள் உள்ளன:

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

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

முடிக்கப்பட்ட சூத்திரம் இப்படி இருக்கும் (கருதலின் எளிமைக்காக, நான் அதை எத்தனை வரிகளைப் பயன்படுத்துகிறேன் என்று பிரித்தேன் alt+உள்ளிடவும்):

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

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

இந்த சூத்திரத்தை உள்ளிடவும் ctrl+ஷிப்ட்+உள்ளிடவும் உண்மையில், இது ஒரு வரிசை சூத்திரம் என்றாலும், இங்கேயும் தேவையில்லை.

மேலும் இது தெளிவாகக் காணப்படுகிறது (முந்தைய படத்தில் #N/A பிழைகளைப் பார்க்கவும்) அத்தகைய சூத்திரம், அதன் அனைத்து நேர்த்தியுடன், இரண்டு குறைபாடுகளைக் கொண்டுள்ளது:

• விழா SUBSTITUTE என்பது கேஸ் சென்சிட்டிவ், எனவே இறுதி வரியில் "Spb" மாற்று அட்டவணையில் காணப்படவில்லை. இந்த சிக்கலை தீர்க்க, நீங்கள் செயல்பாட்டைப் பயன்படுத்தலாம் ZAMENIT (மாற்று), அல்லது பூர்வாங்கமாக இரண்டு அட்டவணைகளையும் ஒரே பதிவேட்டில் கொண்டு வரவும்.
• உரை ஆரம்பத்தில் சரியாக இருந்தால் அல்லது அதில் மாற்றுவதற்கு ஒரு துண்டு இல்லை (கடைசி வரி), பின்னர் எங்கள் சூத்திரம் ஒரு பிழையை வீசுகிறது. செயல்பாட்டைப் பயன்படுத்தி பிழைகளை இடைமறித்து மாற்றுவதன் மூலம் இந்த தருணத்தை நடுநிலையாக்க முடியும் IFERROR (முன்):

  

• அசல் உரை இருந்தால் ஒரே நேரத்தில் கோப்பகத்திலிருந்து பல துண்டுகள், பின்னர் எங்கள் சூத்திரம் கடைசி ஒன்றை மட்டுமே மாற்றுகிறது (8வது வரியில், லிகோவ்ஸ்கி «அவென்யூ« க்கு மாற்றப்பட்டது "pr-t", ஆனால் "எஸ்-பிபி" on “செயின்ட். பீட்டர்ஸ்பர்க்" இனி, ஏனெனில் “எஸ்-பிபி” கோப்பகத்தில் அதிகமாக உள்ளது). எங்கள் சொந்த சூத்திரத்தை மீண்டும் இயக்குவதன் மூலம் இந்த சிக்கலை தீர்க்க முடியும், ஆனால் ஏற்கனவே நெடுவரிசையில் நிலையானது:

  
  

சரியான மற்றும் சிக்கலான இடங்களில் இல்லை, ஆனால் அதே கையேடு மாற்றீட்டை விட சிறந்தது, இல்லையா? 🙂

PS

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

• உரையை மாற்றுவதற்கு SUBSTITUTE செயல்பாடு எவ்வாறு செயல்படுகிறது
  
• சரியான செயல்பாட்டைப் பயன்படுத்தி சரியான உரைப் பொருத்தங்களைக் கண்டறிதல்
• கேஸ் சென்சிட்டிவ் தேடல் மற்றும் மாற்று (வழக்கு உணர்திறன் VLOOKUP)