मान लीजिए कि आपके पास एक सूची है, जिसमें "सीधापन" की अलग-अलग डिग्री के साथ प्रारंभिक डेटा लिखा गया है - उदाहरण के लिए, पते या कंपनी के नाम:
 |  |
यह स्पष्ट रूप से देखा गया है कि वही शहर या कंपनी यहां मोटल वेरिएंट में मौजूद है, जो जाहिर है, भविष्य में इन तालिकाओं के साथ काम करते समय बहुत सारी समस्याएं पैदा करेगा। और अगर आप थोड़ा सोचते हैं, तो आप अन्य क्षेत्रों से समान कार्यों के बहुत सारे उदाहरण पा सकते हैं।
अब कल्पना कीजिए कि इस तरह के टेढ़े-मेढ़े डेटा आपके पास नियमित रूप से आते हैं, यानी यह एक बार की "मैन्युअल रूप से इसे ठीक करें, इसे भूल जाएं" कहानी नहीं है, बल्कि नियमित रूप से और बड़ी संख्या में कोशिकाओं में एक समस्या है।
क्या करें? "ढूंढें और बदलें" बॉक्स के माध्यम से या क्लिक करके कुटिल पाठ को 100500 बार सही के साथ मैन्युअल रूप से प्रतिस्थापित न करें कंट्रोल+H?
ऐसी स्थिति में पहली बात जो दिमाग में आती है वह यह है कि गलत और सही विकल्पों के मिलान की पूर्व-संकलित संदर्भ पुस्तक के अनुसार बड़े पैमाने पर प्रतिस्थापन किया जाए - जैसे:
दुर्भाग्य से, ऐसे कार्य के स्पष्ट प्रसार के साथ, Microsoft Excel में इसे हल करने के लिए सरल अंतर्निहित तरीके नहीं हैं। शुरू करने के लिए, आइए जानें कि VBA या Power Query में मैक्रोज़ के रूप में "भारी तोपखाने" को शामिल किए बिना, फ़ार्मुलों के साथ इसे कैसे किया जाए।
केस 1. थोक पूर्ण प्रतिस्थापन
आइए एक अपेक्षाकृत सरल मामले से शुरू करते हैं - एक ऐसी स्थिति जहां आपको पुराने टेढ़े-मेढ़े पाठ को एक नए से बदलने की आवश्यकता होती है। पूरी तरह से.
मान लें कि हमारे पास दो टेबल हैं:
पहले में - कंपनियों के मूल भिन्न नाम। दूसरे में - पत्राचार की एक संदर्भ पुस्तक। यदि हम पहली तालिका में कंपनी के नाम में कॉलम से कोई शब्द पाते हैं ढूँढ़ने के लिए, तो आपको इस टेढ़े-मेढ़े नाम को पूरी तरह से सही नाम से बदलना होगा - कॉलम से विकल्प दूसरा लुकअप टेबल।
सुविधा के लिए:
- कीबोर्ड शॉर्टकट का उपयोग करके दोनों तालिकाओं को गतिशील ("स्मार्ट") में बदल दिया जाता है कंट्रोल+T या टीम टेबल इंसर्ट करें (टेबल इंसर्ट करें).
- दिखाई देने वाले टैब पर निर्माता (डिज़ाइन) नाम की पहली तालिका जानकारी, और दूसरी संदर्भ तालिका - प्रतिस्थापन.
सूत्र का तर्क समझाने के लिए थोड़ा दूर से चलते हैं।
सेल A2 से पहली कंपनी को एक उदाहरण के रूप में लेते हुए और बाकी कंपनियों के बारे में अस्थायी रूप से भूलकर, आइए यह निर्धारित करने का प्रयास करें कि कॉलम से कौन सा विकल्प है ढूँढ़ने के लिए वहाँ मिलता है। ऐसा करने के लिए, शीट के खाली हिस्से में किसी भी खाली सेल का चयन करें और वहां फ़ंक्शन दर्ज करें ढूँढ़ने के लिए (पाना):
यह फ़ंक्शन निर्धारित करता है कि क्या दिया गया सबस्ट्रिंग शामिल है (पहला तर्क कॉलम से सभी मान है ढूँढ़ने के लिए) स्रोत पाठ में (डेटा तालिका से पहली कंपनी) और या तो उस वर्ण की क्रमिक संख्या को आउटपुट करना चाहिए जिससे पाठ मिला था, या यदि विकल्प नहीं मिला था तो एक त्रुटि।
यहां चाल यह है कि चूंकि हमने पहले तर्क के रूप में एक नहीं, बल्कि कई मान निर्दिष्ट किए हैं, इसलिए यह फ़ंक्शन एक मान नहीं, बल्कि 3 तत्वों की एक सरणी के परिणामस्वरूप भी वापस आ जाएगा। यदि आपके पास Office 365 का नवीनतम संस्करण नहीं है जो गतिशील सरणियों का समर्थन करता है, तो इस सूत्र को दर्ज करने और क्लिक करने के बाद दर्ज आप इस सरणी को सीधे शीट पर देखेंगे:
यदि आपके पास एक्सेल के पिछले संस्करण हैं, तो पर क्लिक करने के बाद दर्ज हम परिणाम सरणी से केवल पहला मान देखेंगे, यानी त्रुटि #VALUE! (#मूल्य!).
आपको डरना नहीं चाहिए वास्तव में, हमारा फॉर्मूला काम करता है और आप अभी भी परिणामों की पूरी सरणी देख सकते हैं यदि आप फॉर्मूला बार में दर्ज किए गए फ़ंक्शन का चयन करते हैं और कुंजी दबाते हैं F9(बस दबाना ना भूलें ईएससीसूत्र पर वापस जाने के लिए):
परिणामों के परिणामी सरणी का अर्थ है कि मूल कुटिल कंपनी के नाम में (जीके मोरोज़्को ओएओ) एक कॉलम में सभी मानों का ढूँढ़ने के लिए केवल दूसरा पाया (मोरोज़्को), और लगातार चौथे वर्ण से शुरू होता है।
अब हमारे सूत्र में एक फ़ंक्शन जोड़ते हैं देखें(खोजें):
इस फ़ंक्शन के तीन तर्क हैं:
- वांछित मूल्य - आप किसी भी पर्याप्त बड़ी संख्या का उपयोग कर सकते हैं (मुख्य बात यह है कि यह स्रोत डेटा में किसी भी पाठ की लंबाई से अधिक है)
- देखे गए_वेक्टर - वह श्रेणी या सरणी जहाँ हम वांछित मान की तलाश कर रहे हैं। यहाँ पहले से पेश किया गया कार्य है ढूँढ़ने के लिए, जो एक सरणी देता है {#VALUE!:4:#VALUE!}
- वेक्टर_परिणाम - वह श्रेणी जिससे हम मान वापस करना चाहते हैं यदि वांछित मान संबंधित सेल में पाया जाता है। यहां कॉलम से सही नाम दिए गए हैं विकल्प हमारी संदर्भ तालिका।
यहां मुख्य और गैर-स्पष्ट विशेषता यह है कि फ़ंक्शन देखें यदि कोई सटीक मिलान नहीं है, तो हमेशा निकटतम सबसे छोटा (पिछला) मान खोजें. इसलिए, किसी भी भारी संख्या (उदाहरण के लिए, 9999) को वांछित मान के रूप में निर्दिष्ट करके, हम मजबूर करेंगे देखें सरणी {#VALUE!:4:#VALUE!} में निकटतम सबसे छोटी संख्या (4) वाला सेल ढूंढें और परिणाम वेक्टर से संबंधित मान लौटाएं, यानी कॉलम से कंपनी का सही नाम विकल्प.
दूसरी बारीकियां यह है कि, तकनीकी रूप से, हमारा सूत्र एक सरणी सूत्र है, क्योंकि फ़ंक्शन ढूँढ़ने के लिए परिणाम एक के रूप में नहीं, बल्कि तीन मानों की एक सरणी के रूप में लौटाता है। लेकिन समारोह के बाद से देखें बॉक्स से बाहर सरणियों का समर्थन करता है, तो हमें इस सूत्र को क्लासिक सरणी सूत्र के रूप में दर्ज करने की आवश्यकता नहीं है - एक कीबोर्ड शॉर्टकट का उपयोग करके कंट्रोल+पाली+दर्ज. एक साधारण पर्याप्त होगा दर्ज.
बस इतना ही। आशा है कि आपको तर्क मिल गया होगा।
यह तैयार सूत्र को कॉलम के पहले सेल B2 में स्थानांतरित करना बाकी है फिक्स्ड - और हमारा काम हल हो गया है!
बेशक, साधारण (स्मार्ट नहीं) तालिकाओं के साथ, यह सूत्र भी बहुत अच्छा काम करता है (बस कुंजी के बारे में मत भूलना F4 और प्रासंगिक लिंक को ठीक करना):
केस 2. थोक आंशिक प्रतिस्थापन
यह मामला थोड़ा पेचीदा है। फिर से हमारे पास दो "स्मार्ट" टेबल हैं:
टेढ़े-मेढ़े पतों वाली पहली तालिका जिसे ठीक करने की आवश्यकता है (मैंने इसे कॉल किया Data2) दूसरी तालिका एक संदर्भ पुस्तक है, जिसके अनुसार आपको पते के अंदर एक सबस्ट्रिंग का आंशिक प्रतिस्थापन करने की आवश्यकता है (मैंने इस तालिका को बुलाया है) प्रतिस्थापन2).
यहां मूलभूत अंतर यह है कि आपको मूल डेटा के केवल एक टुकड़े को बदलने की जरूरत है - उदाहरण के लिए, पहले पते में गलत है "अनुसूचित जनजाति। पीटर्सबर्ग" सही पर "अनुसूचित जनजाति। पीटर्सबर्ग", शेष पता (ज़िप कोड, गली, घर) को यथावत छोड़कर।
तैयार सूत्र इस तरह दिखेगा (धारणा में आसानी के लिए, मैंने इसे कितनी पंक्तियों का उपयोग करके विभाजित किया है ऑल्ट+दर्ज):
यहां मुख्य कार्य मानक एक्सेल टेक्स्ट फ़ंक्शन द्वारा किया जाता है विकल्प (स्थानापन्न), जिसमें 3 तर्क हैं:
- स्रोत पाठ - पता कॉलम से पहला कुटिल पता
- हम जो खोज रहे हैं - यहां हम फ़ंक्शन के साथ ट्रिक का उपयोग करते हैं देखें (खोजें)कॉलम से मान खींचने के पिछले तरीके से ढूँढ़ने के लिए, जो एक घुमावदार पते में एक टुकड़े के रूप में शामिल है।
- किसके साथ प्रतिस्थापित करना है - उसी तरह हम कॉलम से इसके अनुरूप सही मान पाते हैं विकल्प.
इस सूत्र को के साथ दर्ज करें कंट्रोल+पाली+दर्ज यहां भी इसकी आवश्यकता नहीं है, हालांकि यह वास्तव में एक सरणी सूत्र है।
और यह स्पष्ट रूप से देखा गया है (पिछली तस्वीर में #N/A त्रुटियां देखें) कि इस तरह के एक सूत्र, इसकी सभी भव्यता के लिए, कुछ कमियां हैं:
- समारोह SUBSTITUTE केस संवेदी है, इसलिए अंतिम पंक्ति में "Spb" प्रतिस्थापन तालिका में नहीं मिला। इस समस्या को हल करने के लिए, आप या तो फ़ंक्शन का उपयोग कर सकते हैं जमनीत (बदलने के), या प्रारंभिक रूप से दोनों तालिकाओं को एक ही रजिस्टर में लाएं।
- यदि पाठ प्रारंभ में सही है या उसमें है बदलने के लिए कोई टुकड़ा नहीं है (अंतिम पंक्ति), तो हमारा सूत्र एक त्रुटि फेंकता है। फ़ंक्शन का उपयोग करके त्रुटियों को रोककर और प्रतिस्थापित करके इस क्षण को बेअसर किया जा सकता है IFERROR (इफरर):
- यदि मूल पाठ में शामिल है निर्देशिका से एक साथ कई टुकड़े, तो हमारा सूत्र केवल अंतिम (8 वीं पंक्ति में, लिगोव्स्की) को बदल देता है «मार्ग« में परिवर्तित किया गया "पीआर-टी", परंतु "एस-पीबी" on "अनुसूचित जनजाति। पीटर्सबर्ग" अब नहीं, क्योंकि "एस-पीबी"निर्देशिका में अधिक है)। इस समस्या को हमारे अपने सूत्र को फिर से चलाकर हल किया जा सकता है, लेकिन पहले से ही कॉलम के साथ फिक्स्ड:
स्थानों में सही और बोझिल नहीं है, लेकिन उसी मैनुअल प्रतिस्थापन से बहुत बेहतर है, है ना? मैं
PS
अगले लेख में, हम यह पता लगाएंगे कि मैक्रोज़ और पावर क्वेरी का उपयोग करके इस तरह के थोक प्रतिस्थापन को कैसे लागू किया जाए।
- टेक्स्ट को बदलने के लिए SUBSTITUTE फ़ंक्शन कैसे काम करता है
- सटीक फ़ंक्शन का उपयोग करके सटीक टेक्स्ट मिलान ढूँढना
- केस संवेदनशील खोज और प्रतिस्थापन (केस संवेदनशील VLOOKUP)