सूत्रहरूसँग बल्क पाठ प्रतिस्थापन

मानौं तपाईंसँग एउटा सूची छ जसमा, "सीधा स्पष्टता" को विभिन्न डिग्रीहरू सहित, प्रारम्भिक डेटा लेखिएको छ - उदाहरणका लागि, ठेगानाहरू वा कम्पनी नामहरू:

यो स्पष्ट रूपमा देखिएको छ कि एउटै शहर वा कम्पनी यहाँ मोटली भेरियन्टहरूमा अवस्थित छ, जसले, स्पष्ट रूपमा, भविष्यमा यी तालिकाहरूसँग काम गर्दा धेरै समस्याहरू सिर्जना गर्नेछ। र यदि तपाइँ थोरै सोच्नुहुन्छ भने, तपाइँ अन्य क्षेत्रहरूबाट समान कार्यहरूको धेरै उदाहरणहरू पाउन सक्नुहुन्छ।

अब कल्पना गर्नुहोस् कि यस्ता कुटिल डाटा तपाईमा नियमित रूपमा आउँछन्, अर्थात् यो एक पटकको "मैन्युअल रूपमा ठीक गर्नुहोस्, बिर्सनुहोस्" कथा होइन, तर नियमित आधारमा र ठूलो संख्यामा सेलहरूमा समस्या छ।

के गर्ने? "फेला र बदल्नुहोस्" बाकस मार्फत वा क्लिक गरेर 100500 पटक टेढो पाठलाई म्यानुअल रूपमा सहीसँग बदल्नुहोस्। Ctrl+H?

यस्तो अवस्थामा दिमागमा आउने पहिलो कुरा गलत र सही विकल्पहरू मिलाउने पूर्व-कम्पाइल गरिएको सन्दर्भ पुस्तक अनुसार सामूहिक प्रतिस्थापन गर्नु हो - जस्तै:

दुर्भाग्यवश, यस्तो कार्यको स्पष्ट प्रचलनको साथ, Microsoft Excel सँग यसलाई समाधान गर्नको लागि सरल निर्मित विधिहरू छैनन्। सुरु गर्नका लागि, VBA वा Power Query मा म्याक्रोको रूपमा "हेभी आर्टिलरी" समावेश नगरिकन, सूत्रहरूसँग यो कसरी गर्ने भनेर पत्ता लगाउनुहोस्।

केस १. बल्क पूर्ण प्रतिस्थापन

एक अपेक्षाकृत सरल केसबाट सुरु गरौं - एउटा अवस्था जहाँ तपाईंले पुरानो टेढो पाठलाई नयाँसँग बदल्न आवश्यक छ। पूर्णतया.

मानौं हामीसँग दुईवटा तालिकाहरू छन्:

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

सुविधाको लागि:

• किबोर्ड सर्टकट प्रयोग गरेर दुवै तालिकाहरू गतिशील ("स्मार्ट") मा रूपान्तरण गरिएका छन् Ctrl+T वा टोली घुसाउनुहोस् - तालिका (घुसाउनुहोस् - तालिका).
  
• देखिने ट्याबमा कन्स्ट्रक्टर (डिजाईन) नामको पहिलो तालिका तथ्याङ्क, र दोस्रो सन्दर्भ तालिका - प्रतिस्थापन.

सूत्रको तर्क व्याख्या गर्न, टाढाबाट अलि जाऔं।

सेल A2 बाट पहिलो कम्पनीलाई उदाहरणको रूपमा लिँदै र बाँकी कम्पनीहरूको बारेमा अस्थायी रूपमा बिर्सेर, स्तम्भबाट कुन विकल्प निर्धारण गर्ने प्रयास गरौं। खोज्न को लागि त्यहाँ भेटिन्छ। यो गर्नका लागि, पानाको खाली भागमा कुनै पनि खाली कक्ष चयन गर्नुहोस् र त्यहाँ प्रकार्य प्रविष्ट गर्नुहोस् पत्ता लाउनु (फेला):

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

यहाँ चाल यो हो कि हामीले पहिलो तर्कको रूपमा एक होइन, तर धेरै मानहरू निर्दिष्ट गरेका छौं, यस प्रकार्यले पनि परिणामको रूपमा एक मान होइन, तर 3 तत्वहरूको एरेको रूपमा फर्काउँछ। यदि तपाईंसँग Office 365 को नवीनतम संस्करण छैन जुन गतिशील arrays लाई समर्थन गर्दछ, त्यसपछि यो सूत्र प्रविष्ट गरेपछि र क्लिक गर्नुहोस् प्रविष्ट गर्नुहोस् तपाईंले पानामा यो एरे देख्नुहुनेछ:

यदि तपाइँसँग एक्सेलको अघिल्लो संस्करणहरू छन् भने, त्यसपछि क्लिक गर्नुहोस् प्रविष्ट गर्नुहोस् हामीले परिणाम एरेबाट पहिलो मान मात्र देख्नेछौं, अर्थात् त्रुटि #VALUE! (#VALUE!).

तपाईं डराउनु पर्दैन 🙂 वास्तवमा, हाम्रो सूत्रले काम गर्छ र यदि तपाईंले सूत्र पट्टीमा प्रविष्ट गरिएको प्रकार्य चयन गर्नुभयो र कुञ्जी थिच्नु भयो भने तपाईंले परिणामहरूको सम्पूर्ण एरे हेर्न सक्नुहुन्छ। F9(केवल थिच्न नबिर्सनुहोस् Escसूत्रमा फर्कन:

नतिजाहरूको नतिजा एर्रे भनेको मूल कुटिल कम्पनीको नाममा हो (GK Morozko OAO) स्तम्भमा सबै मानहरूको खोज्न को लागि दोस्रो मात्र भेटियो (मोरोज्को), र पङ्क्तिमा 4 औं वर्णबाट सुरु गर्दै।

अब हाम्रो सूत्रमा एउटा प्रकार्य थपौं हेर्नुहोस्(माथि हेर):

यस प्रकार्यमा तीन तर्कहरू छन्:

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

यहाँ मुख्य र गैर-स्पष्ट विशेषता भनेको कार्य हो हेर्नुहोस् यदि त्यहाँ कुनै सटीक मेल छैन भने, सधैं सबैभन्दा नजिकको सबैभन्दा सानो (अघिल्लो) मान खोज्छ। त्यसैले, कुनै पनि भारी संख्या (उदाहरणका लागि, 9999) वांछित मानको रूपमा निर्दिष्ट गरेर, हामी बल गर्नेछौं हेर्नुहोस् एरे {#VALUE!:4:#VALUE!} मा सबैभन्दा नजिकको सानो संख्या (4) भएको सेल फेला पार्नुहोस् र परिणाम भेक्टरबाट सम्बन्धित मान फर्काउनुहोस्, अर्थात् स्तम्भबाट कम्पनीको नाम सही गर्नुहोस्। उपन्यास.

दोस्रो nuance यो छ कि, प्राविधिक रूपमा, हाम्रो सूत्र एरे सूत्र हो, किनभने प्रकार्य पत्ता लाउनु परिणाम एक होइन, तर तीन मानहरूको एरेको रूपमा फर्काउँछ। तर समारोह देखि हेर्नुहोस् बक्स बाहिर arrays लाई समर्थन गर्दछ, त्यसपछि हामीले यो सूत्रलाई क्लासिक एरे सूत्रको रूपमा प्रविष्ट गर्नुपर्दैन - किबोर्ड सर्टकट प्रयोग गरेर Ctrl+सिफ्ट+प्रविष्ट गर्नुहोस्। एक साधारण पर्याप्त हुनेछ प्रविष्ट गर्नुहोस्.

यति नै। आशा छ तपाईंले तर्क पाउनुहुनेछ।

यो स्तम्भको पहिलो कक्ष B2 मा समाप्त सूत्र स्थानान्तरण गर्न बाँकी छ स्थिर - र हाम्रो कार्य हल भयो!

निस्सन्देह, साधारण (स्मार्ट होइन) तालिकाहरूसँग, यो सूत्र पनि राम्रो काम गर्दछ (केवल कुञ्जी बारे नबिर्सनुहोस्। F4 र सान्दर्भिक लिङ्कहरू फिक्स गर्दै):

केस २. थोक आंशिक प्रतिस्थापन

यो मामला अलि जटिल छ। फेरि हामीसँग दुई "स्मार्ट" तालिकाहरू छन्:

कुटिल रूपमा लिखित ठेगानाहरू भएको पहिलो तालिका जुन सुधार गर्न आवश्यक छ (मैले यसलाई बोलाएँ डाटा 2)। दोस्रो तालिका एउटा सन्दर्भ पुस्तक हो, जसको अनुसार तपाईंले ठेगाना भित्र सबस्ट्रिङको आंशिक प्रतिस्थापन गर्न आवश्यक छ (मैले यो तालिकालाई बोलाएँ। प्रतिस्थापन २).

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

समाप्त सूत्र यस्तो देखिनेछ (अनुभूतिको सहजताको लागि, मैले यसलाई कति लाइनहरू प्रयोग गरेर विभाजित गरें। alt+प्रविष्ट गर्नुहोस्):

यहाँ मुख्य काम मानक एक्सेल पाठ प्रकार्य द्वारा गरिन्छ सबस्टिट्यूट (प्रतिस्थापन), जसमा 3 तर्कहरू छन्:

1. स्रोत पाठ - ठेगाना स्तम्भबाट पहिलो बाङ्गो ठेगाना
2. हामीले के खोजिरहेका छौं - यहाँ हामी प्रकार्यको साथ चाल प्रयोग गर्छौं हेर्नुहोस् (माथि हेर)स्तम्भबाट मान तान्न अघिल्लो तरिकाबाट खोज्न को लागि, जुन घुमाउरो ठेगानामा टुक्राको रूपमा समावेश गरिएको छ।
3. केसँग प्रतिस्थापन गर्ने - त्यसै गरी हामीले स्तम्भबाट यसको अनुरूप सही मान फेला पार्छौं उपन्यास.

संग यो सूत्र प्रविष्ट गर्नुहोस् Ctrl+सिफ्ट+प्रविष्ट गर्नुहोस् यहाँ पनि आवश्यक छैन, यद्यपि यो, वास्तवमा, एरे सूत्र हो।

र यो स्पष्ट रूपमा देखिएको छ (पहिलेको चित्रमा #N/A त्रुटिहरू हेर्नुहोस्) कि यस्तो सूत्र, यसको सबै सुन्दरताको लागि, केही कमिहरू छन्:

• समारोह SUBSTITUTE केस सेन्सेटिभ छ, त्यसैले प्रतिस्थापन तालिकामा उपान्त्य रेखामा "Spb" फेला परेन। यो समस्या समाधान गर्न, तपाईं या त प्रकार्य प्रयोग गर्न सक्नुहुन्छ ZAMENIT (बदल्नुहोस्), वा प्रारम्भिक रूपमा एउटै दर्तामा दुवै तालिकाहरू ल्याउनुहोस्।
• यदि पाठ सुरुमा सही छ वा यसमा प्रतिस्थापन गर्न कुनै टुक्रा छैन (अन्तिम रेखा), त्यसपछि हाम्रो सूत्रले त्रुटि फ्याँक्छ। यस क्षणलाई फंक्शन प्रयोग गरेर त्रुटिहरूलाई अवरोध र प्रतिस्थापन गरेर तटस्थ गर्न सकिन्छ IFERROR (IFERROR):

  

• यदि मूल पाठ समावेश छ एकै पटक डाइरेक्टरीबाट धेरै टुक्राहरू, त्यसपछि हाम्रो सूत्रले अन्तिम एकलाई मात्र प्रतिस्थापन गर्दछ (8 औं लाइनमा, Ligovsky «एवेन्यू« लाई परिवर्तन गरियो "pr-t", तर "S-Pb" on "सेन्ट। पिटर्सबर्ग" अब छैन, किनभने "S-Pb"निर्देशिकामा उच्च छ)। यो समस्या हाम्रो आफ्नै सूत्र पुन: चलाएर हल गर्न सकिन्छ, तर पहिले नै स्तम्भ संग स्थिर:

  
  

ठाउँहरूमा सही र बोझिल छैन, तर उही म्यानुअल प्रतिस्थापन भन्दा धेरै राम्रो, हैन? 🙂

PS

अर्को लेखमा, हामी म्याक्रो र पावर क्वेरी प्रयोग गरेर यस्तो बल्क प्रतिस्थापन कसरी लागू गर्ने भनेर पत्ता लगाउनेछौं।

• पाठ प्रतिस्थापन गर्न SUBSTITUTE प्रकार्यले कसरी काम गर्छ
  
• सटीक प्रकार्य प्रयोग गरेर सटीक पाठ मेलहरू खोज्दै
• केस सेन्सेटिभ खोज र प्रतिस्थापन (केस सेन्सेटिभ VLOOKUP)