सूत्रांसह मोठ्या प्रमाणात मजकूर बदलणे

समजा तुमच्याकडे एक सूची आहे ज्यामध्ये "सरळपणा" च्या वेगवेगळ्या प्रमाणात प्रारंभिक डेटा लिहिलेला आहे - उदाहरणार्थ, पत्ते किंवा कंपनीची नावे:

हे स्पष्टपणे दिसून येते की तेच शहर किंवा कंपनी मोटली प्रकारांमध्ये येथे उपस्थित आहे, जे साहजिकच, भविष्यात या टेबलांसह काम करताना खूप समस्या निर्माण करेल. आणि जर तुम्ही थोडासा विचार केला तर तुम्हाला इतर क्षेत्रांतील समान कार्यांची बरीच उदाहरणे सापडतील.

आता कल्पना करा की असा कुटिल डेटा तुमच्याकडे नियमितपणे येतो, म्हणजे ही “मॅन्युअली फिक्स इट, विसरा” ही एक वेळची कथा नाही, तर नियमितपणे आणि मोठ्या संख्येने सेलमध्ये समस्या आहे.

काय करायचं? “शोधा आणि बदला” बॉक्सद्वारे किंवा क्लिक करून कुटिल मजकूर 100500 वेळा मॅन्युअली बदलू नका. Ctrl+H?

अशा परिस्थितीत लक्षात येणारी पहिली गोष्ट म्हणजे चुकीच्या आणि योग्य पर्यायांशी जुळणाऱ्या पूर्व-संकलित संदर्भ पुस्तकानुसार वस्तुमान बदलणे – याप्रमाणे:

दुर्दैवाने, अशा कार्याच्या स्पष्ट व्याप्तीसह, मायक्रोसॉफ्ट एक्सेलमध्ये ते सोडवण्यासाठी साध्या अंगभूत पद्धती नाहीत. सुरूवातीस, VBA किंवा Power Query मधील मॅक्रोच्या स्वरूपात “हेवी आर्टिलरी” समाविष्ट न करता, सूत्रांसह हे कसे करायचे ते शोधू या.

केस 1. मोठ्या प्रमाणात पूर्ण बदली

चला तुलनेने सोप्या केसपासून सुरुवात करूया - अशी परिस्थिती जिथे तुम्हाला जुना कुटिल मजकूर नवीनसह बदलण्याची आवश्यकता आहे. पूर्णपणे.

समजा आमच्याकडे दोन टेबल्स आहेत:

प्रथम - कंपन्यांची मूळ विविधरंगी नावे. दुसऱ्यामध्ये - पत्रव्यवहाराचे संदर्भ पुस्तक. पहिल्या टेबलमध्ये कंपनीच्या नावात कॉलममधील कोणताही शब्द आढळल्यास शोधण्यासाठी, नंतर तुम्हाला हे कुटिल नाव पूर्णपणे योग्य नावाने पुनर्स्थित करणे आवश्यक आहे – स्तंभातून पर्याय दुसरे लुकअप टेबल.

सोयीसाठी:

• कीबोर्ड शॉर्टकट वापरून दोन्ही टेबल डायनॅमिक (“स्मार्ट”) मध्ये रूपांतरित केले जातात Ctrl+T किंवा संघ घाला - टेबल (घाला - टेबल).
  
• दिसणार्‍या टॅबवर रचनाकार (डिझाइन) नावाचे पहिले टेबल डेटा, आणि दुसरा संदर्भ सारणी - पर्याय.

सूत्राचे तर्कशास्त्र समजावून सांगण्यासाठी, चला थोडे दूर जाऊया.

सेल A2 मधील पहिली कंपनी उदाहरण म्हणून घेऊन आणि उर्वरित कंपन्यांबद्दल तात्पुरते विसरून, कॉलममधून कोणता पर्याय आहे हे ठरवण्याचा प्रयत्न करूया. शोधण्यासाठी तिथे भेटतो. हे करण्यासाठी, शीटच्या मुक्त भागामध्ये कोणताही रिक्त सेल निवडा आणि तेथे फंक्शन प्रविष्ट करा शोधण्यासाठी (शोधणे):

हे फंक्शन दिलेले सबस्ट्रिंग समाविष्ट केले आहे की नाही हे निर्धारित करते (पहिला युक्तिवाद स्तंभातील सर्व मूल्ये आहेत शोधण्यासाठी) स्त्रोत मजकूरात (डेटा सारणीतील पहिली कंपनी) आणि एकतर ज्या वर्णातून मजकूर सापडला होता त्या वर्णाची क्रमिक संख्या किंवा सबस्ट्रिंग आढळली नसल्यास त्रुटी आउटपुट केली पाहिजे.

येथे युक्ती अशी आहे की आम्ही प्रथम युक्तिवाद म्हणून एक नाही तर अनेक मूल्ये निर्दिष्ट केल्यामुळे, हे फंक्शन देखील परिणामी एक मूल्य नाही तर 3 घटकांचे अॅरे देईल. तुमच्याकडे Office 365 ची नवीनतम आवृत्ती नसेल जी डायनॅमिक अॅरेला सपोर्ट करते, तर हे सूत्र एंटर केल्यानंतर आणि वर क्लिक केल्यानंतर प्रविष्ट करा तुम्हाला ही अॅरे थेट शीटवर दिसेल:

जर तुमच्याकडे एक्सेलच्या मागील आवृत्त्या असतील तर त्यावर क्लिक केल्यानंतर प्रविष्ट करा आम्हाला रिझल्ट अॅरेमधून फक्त पहिले व्हॅल्यू दिसेल, म्हणजे एरर #VALUE! (#मूल्य!).

तुम्ही घाबरू नका 🙂 खरं तर, आमचा फॉर्म्युला काम करतो आणि तुम्ही फॉर्म्युला बारमधील एंटर केलेले फंक्शन निवडल्यास आणि की दाबल्यास तुम्ही परिणामांची संपूर्ण श्रेणी पाहू शकता. F9(फक्त दाबायला विसरू नका Escसूत्राकडे परत जाण्यासाठी):

परिणामांच्या परिणामी अॅरेचा अर्थ असा आहे की मूळ कुटिल कंपनीच्या नावात (जीके मोरोझको ओएओ) स्तंभातील सर्व मूल्यांची शोधण्यासाठी फक्त दुसरा सापडला (मोरोझको), आणि सलग चौथ्या वर्णापासून सुरू होत आहे.

आता आपल्या सूत्रात फंक्शन जोडू पहा(वर बघ):

या फंक्शनमध्ये तीन वितर्क आहेत:

1. इच्छित मूल्य - आपण कोणतीही पुरेशी मोठी संख्या वापरू शकता (मुख्य गोष्ट म्हणजे ती स्त्रोत डेटामधील कोणत्याही मजकुराच्या लांबीपेक्षा जास्त आहे)
   
2. पाहिले_वेक्टर - श्रेणी किंवा अॅरे जिथे आम्ही इच्छित मूल्य शोधत आहोत. येथे पूर्वी सादर केलेले कार्य आहे शोधण्यासाठी, जे एक अॅरे मिळवते {#VALUE!:4:#VALUE!}
   
3. वेक्टर_परिणाम - संबंधित सेलमध्ये इच्छित मूल्य आढळल्यास ज्या श्रेणीतून आम्हाला मूल्य परत करायचे आहे. स्तंभातील योग्य नावे येथे आहेत पर्याय आमचे संदर्भ सारणी.
   

येथे मुख्य आणि स्पष्ट नसलेले वैशिष्ट्य म्हणजे कार्य पहा कोणतीही अचूक जुळणी नसल्यास, नेहमी सर्वात जवळचे सर्वात लहान (मागील) मूल्य शोधते. म्हणून, इच्छित मूल्य म्हणून कोणतीही भारी संख्या (उदाहरणार्थ, 9999) निर्दिष्ट करून, आम्ही सक्ती करू पहा {#VALUE!:4:#VALUE!} अॅरेमधील सर्वात जवळची सर्वात लहान संख्या (4) असलेला सेल शोधा आणि परिणाम वेक्टरमधून संबंधित मूल्य परत करा, म्हणजे कॉलममधून योग्य कंपनीचे नाव पर्याय.

दुसरी महत्त्वाची गोष्ट म्हणजे, तांत्रिकदृष्ट्या, आमचे सूत्र अॅरे सूत्र आहे, कारण फंक्शन शोधण्यासाठी एक नाही तर तीन व्हॅल्यूजचा अ‍ॅरे म्हणून परिणाम मिळतो. पण फंक्शन पासून पहा बॉक्सच्या बाहेर अॅरेला सपोर्ट करते, मग आम्हाला हे सूत्र क्लासिक अॅरे फॉर्म्युला म्हणून एंटर करण्याची गरज नाही – कीबोर्ड शॉर्टकट वापरून Ctrl+शिफ्ट+प्रविष्ट करा. एक साधा पुरेसा असेल प्रविष्ट करा.

इतकंच. तुम्हाला तर्क समजेल अशी आशा आहे.

तयार सूत्र स्तंभाच्या पहिल्या सेल B2 मध्ये हस्तांतरित करणे बाकी आहे मुदत - आणि आमचे कार्य सोडवले आहे!

अर्थात, सामान्य (स्मार्ट नाही) सारण्यांसह, हे सूत्र देखील चांगले कार्य करते (फक्त की बद्दल विसरू नका F4 आणि संबंधित दुवे निश्चित करणे):

केस 2. मोठ्या प्रमाणात आंशिक बदली

हे प्रकरण थोडे अवघड आहे. पुन्हा आमच्याकडे दोन "स्मार्ट" टेबल आहेत:

कुटिलपणे लिहिलेले पत्ते असलेले पहिले टेबल जे दुरुस्त करणे आवश्यक आहे (मी त्याला कॉल केला डेटा 2). दुसरा सारणी एक संदर्भ पुस्तक आहे, त्यानुसार तुम्हाला पत्त्याच्या आत सबस्ट्रिंगची आंशिक बदली करणे आवश्यक आहे (मी या टेबलला म्हटले आहे बदली २).

येथे मूलभूत फरक असा आहे की तुम्हाला मूळ डेटाचा फक्त एक तुकडा बदलण्याची आवश्यकता आहे - उदाहरणार्थ, पहिल्या पत्त्यामध्ये चुकीचा आहे "सेंट. पीटर्सबर्ग" उजवीकडे "सेंट. पीटर्सबर्ग", बाकीचा पत्ता (पिन कोड, रस्ता, घर) आहे तसा सोडून.

तयार झालेले सूत्र असे दिसेल (समज सुलभतेसाठी, मी ते वापरून किती ओळींमध्ये विभागले आहे alt+प्रविष्ट करा):

येथे मुख्य काम मानक एक्सेल मजकूर फंक्शनद्वारे केले जाते सबस्टिट्यूट (बदली), ज्यामध्ये 3 वितर्क आहेत:

1. स्त्रोत मजकूर - पत्ता स्तंभातील पहिला कुटिल पत्ता
2. आपण काय शोधत आहोत - येथे आपण फंक्शनसह युक्ती वापरतो पहा (वर बघ)स्तंभातून मूल्य खेचण्यासाठी मागील मार्गावरून शोधण्यासाठी, जे वक्र पत्त्यामध्ये एक तुकडा म्हणून समाविष्ट केले आहे.
3. कशासह बदलायचे - त्याच प्रकारे आपल्याला स्तंभातून त्याच्याशी संबंधित योग्य मूल्य सापडते पर्याय.

सह हे सूत्र प्रविष्ट करा Ctrl+शिफ्ट+प्रविष्ट करा येथे देखील आवश्यक नाही, जरी ते खरं तर अॅरे फॉर्म्युला आहे.

आणि हे स्पष्टपणे दिसून येते (मागील चित्रात #N/A त्रुटी पहा) की अशा सूत्रात, त्याच्या सर्व अभिजाततेसाठी, दोन तोटे आहेत:

• कार्य SUBSTITUTE केस संवेदनशील आहे, म्हणून बदली सारणीमध्ये उपांत्य रेषेतील “Spb” आढळले नाही. या समस्येचे निराकरण करण्यासाठी, आपण एकतर फंक्शन वापरू शकता ZAMENIT (बदला), किंवा सुरुवातीला दोन्ही टेबल्स एकाच रजिस्टरवर आणा.
• जर मजकूर सुरुवातीला बरोबर असेल किंवा त्यात बदलण्यासाठी कोणताही तुकडा नाही (शेवटची ओळ), नंतर आमचे सूत्र एक त्रुटी टाकते. फंक्शन वापरून त्रुटी रोखून आणि पुनर्स्थित करून हा क्षण तटस्थ केला जाऊ शकतो IFERROR (IFERROR):

  

• मूळ मजकुरात असल्यास एकाच वेळी निर्देशिकेतून अनेक तुकडे, नंतर आमचे सूत्र फक्त शेवटचे बदलते (8 व्या ओळीत, लिगोव्स्की «अव्हेन्यू« मध्ये बदलले "प्र-टी", परंतु "एस-पीबी" on "सेंट. पीटर्सबर्ग" यापुढे, कारण "एस-पीबीनिर्देशिकेत जास्त आहे). ही समस्या आमचे स्वतःचे सूत्र पुन्हा चालवून सोडविली जाऊ शकते, परंतु आधीच स्तंभासह मुदत:

  
  

ठिकाणी परिपूर्ण आणि अवजड नाही, परंतु त्याच मॅन्युअल बदलण्यापेक्षा बरेच चांगले, बरोबर? 🙂

PS

पुढील लेखात, आम्ही मॅक्रो आणि पॉवर क्वेरी वापरून अशा मोठ्या प्रमाणात प्रतिस्थापन कसे कार्यान्वित करायचे ते शोधू.

• मजकूर बदलण्यासाठी SUBSTITUTE फंक्शन कसे कार्य करते
  
• अचूक कार्य वापरून अचूक मजकूर जुळणे शोधणे
• केस संवेदनशील शोध आणि प्रतिस्थापन (केस संवेदनशील VLOOKUP)