शर्त के अनुसार पाठ को जोड़ना

मैंने पहले ही इस बारे में लिखा है कि आप कई सेल्स से टेक्स्ट को जल्दी से एक में कैसे गोंद कर सकते हैं और इसके विपरीत, घटकों में एक लंबी टेक्स्ट स्ट्रिंग को पार्स कर सकते हैं। अब आइए एक करीबी, लेकिन थोड़ा अधिक जटिल कार्य देखें - एक निश्चित निर्दिष्ट शर्त पूरी होने पर कई कोशिकाओं से टेक्स्ट को कैसे गोंद किया जाए। 

मान लें कि हमारे पास ग्राहकों का एक डेटाबेस है, जहां एक कंपनी का नाम उसके कर्मचारियों के कई अलग-अलग ईमेल के अनुरूप हो सकता है। हमारा काम कंपनी के नामों से सभी पते एकत्र करना है और उन्हें (अल्पविराम या अर्धविराम से अलग) जोड़ना है, उदाहरण के लिए, ग्राहकों के लिए एक मेलिंग सूची, यानी आउटपुट कुछ इस तरह प्राप्त करें:

दूसरे शब्दों में, हमें एक ऐसे टूल की आवश्यकता है जो टेक्स्ट को कंडीशन के अनुसार ग्लू (लिंक) करे - फंक्शन का एक एनालॉग सारांश (SUMIF), लेकिन पाठ के लिए।

विधि 0. सूत्र

बहुत सुंदर नहीं, लेकिन सबसे आसान तरीका। आप एक सरल सूत्र लिख सकते हैं जो यह जांच करेगा कि अगली पंक्ति में कंपनी पिछले एक से अलग है या नहीं। यदि यह भिन्न नहीं है, तो अल्पविराम द्वारा अलग किए गए अगले पते को गोंद दें। यदि यह भिन्न होता है, तो हम संचित को "रीसेट" करते हैं, फिर से शुरू करते हैं:

इस दृष्टिकोण के नुकसान स्पष्ट हैं: प्राप्त अतिरिक्त कॉलम की सभी कोशिकाओं से, हमें प्रत्येक कंपनी (पीला) के लिए केवल अंतिम की आवश्यकता होती है। यदि सूची बड़ी है, तो उन्हें जल्दी से चुनने के लिए, आपको फ़ंक्शन का उपयोग करके एक और कॉलम जोड़ना होगा डीएलएसटीआर (एलईएन), संचित तारों की लंबाई की जाँच करना:

अब आप उन्हें फ़िल्टर कर सकते हैं और आगे के उपयोग के लिए आवश्यक ग्लूइंग पते की प्रतिलिपि बना सकते हैं।

विधि 1. एक शर्त द्वारा ग्लूइंग का मैक्रोफंक्शन

यदि मूल सूची कंपनी द्वारा क्रमबद्ध नहीं है, तो उपरोक्त सरल सूत्र काम नहीं करता है, लेकिन आप आसानी से वीबीए में एक छोटे से कस्टम फ़ंक्शन के साथ मिल सकते हैं। कीबोर्ड शॉर्टकट दबाकर विजुअल बेसिक एडिटर खोलें ऑल्ट + F11 या बटन का उपयोग कर Visual Basic के टैब विकासक (डेवलपर). खुलने वाली विंडो में, मेनू के माध्यम से एक नया खाली मॉड्यूल डालें सम्मिलित करें - मॉड्यूल और वहां हमारे फंक्शन के टेक्स्ट को कॉपी करें:

फ़ंक्शन MergeIf(टेक्स्टरेंज एज़ रेंज, सर्चरेंज ऐज़ रेंज, कंडीशन एज़ स्ट्रिंग) डिम डेलीमीटर अस स्ट्रिंग, आई एज़ लॉन्ग डेलीमीटर = "," ग्लूइंग्स एक दूसरे के बराबर नहीं हैं - हम एक त्रुटि के साथ बाहर निकलते हैं यदि SearchRange.Count <> TextRange.Count फिर MergeIf = CVERr(xlErrRef) Exit Function End If 'सभी सेल से गुजरें, स्थिति की जांच करें और i = 1 To SearchRange के लिए वेरिएबल आउटटेक्स्ट में टेक्स्ट को कलेक्ट करें। Cells.Count यदि SearchRange.Cells(i) स्थिति की तरह है तो OutText = OutText & TextRange.Cells(i) & Delimiter Next i 'अंतिम सीमांकक के बिना परिणाम प्रदर्शित करता है MergeIf = बायां(आउटटेक्स्ट, लेन(आउटटेक्स्ट) - लेन(डेलीमीटर)) अंत समारोह  

यदि आप अब Microsoft Excel में वापस आते हैं, तो कार्यों की सूची में (बटन fx सूत्र पट्टी या टैब में सूत्र - फ़ंक्शन सम्मिलित करें) हमारे कार्य को खोजना संभव होगा मर्जइफ श्रेणी में उपयोगकर्ता परिभाषित (उपयोगकर्ता परिभाषित). फ़ंक्शन के तर्क इस प्रकार हैं:

विधि 2. सटीक स्थिति के अनुसार पाठ को संयोजित करें

यदि हम अपने मैक्रो की 13वीं पंक्ति में पहले वर्ण को प्रतिस्थापित करते हैं = अनुमानित मैच ऑपरेटर के लिए पसंद, तो चयन मानदंड के साथ प्रारंभिक डेटा के एक सटीक मिलान द्वारा ग्लूइंग करना संभव होगा। उदाहरण के लिए, यदि कंपनी का नाम अलग-अलग रूपों में लिखा जा सकता है, तो हम उन सभी को एक फ़ंक्शन के साथ जांच और एकत्र कर सकते हैं:

मानक वाइल्डकार्ड समर्थित हैं:

• तारांकन (*) - किसी भी वर्ण की किसी भी संख्या को दर्शाता है (उनकी अनुपस्थिति सहित)
• प्रश्नवाचक चिह्न (?) - किसी एकल वर्ण के लिए खड़ा है
• पौंड चिह्न (#) - किसी एक अंक (0-9) के लिए खड़ा है

डिफ़ॉल्ट रूप से, लाइक ऑपरेटर केस सेंसिटिव होता है, उदाहरण के लिए, "ओरियन" और "ओरियन" को अलग-अलग कंपनियों के रूप में समझता है। मामले को नज़रअंदाज़ करने के लिए, आप Visual Basic संपादक में मॉड्यूल की शुरुआत में ही लाइन जोड़ सकते हैं विकल्प टेक्स्ट की तुलना करें, जो केस असंवेदनशील होने की तरह स्विच करेगा।

इस तरह, आप स्थितियों की जाँच के लिए बहुत जटिल मास्क बना सकते हैं, उदाहरण के लिए:

• ?1##??777RUS - 777 क्षेत्र की सभी लाइसेंस प्लेटों का चयन, 1 . से शुरू
• एलएलसी* - सभी कंपनियां जिनका नाम एलएलसी से शुरू होता है
• ##7## - पांच अंकों के डिजिटल कोड वाले सभी उत्पाद, जहां तीसरा अंक 7 . है
• ????? - पांच अक्षरों के सभी नाम, आदि।
  

विधि 3. दो शर्तों के तहत ग्लूइंग टेक्स्ट के लिए मैक्रो फ़ंक्शन

काम में समस्या हो सकती है जब आपको टेक्स्ट को एक से अधिक शर्तों से लिंक करने की आवश्यकता होती है। उदाहरण के लिए, आइए कल्पना करें कि हमारी पिछली तालिका में, शहर के साथ एक और कॉलम जोड़ा गया था, और न केवल किसी कंपनी के लिए, बल्कि किसी दिए गए शहर के लिए भी ग्लूइंग किया जाना चाहिए। इस मामले में, हमारे फ़ंक्शन को इसमें एक और रेंज चेक जोड़कर थोड़ा आधुनिक बनाना होगा:

फ़ंक्शन MergeIfs(टेक्स्टरेंज एज रेंज, सर्चरेंज1 ऐज रेंज, कंडीशन1 ऐज स्ट्रिंग, सर्चरेंज2 ऐज रेंज, कंडीशन2 ऐज स्ट्रिंग) डिम डिलीमीटर ऐज लॉन्ग डेलीमीटर = "," 'डिलिमिटर कैरेक्टर्स (स्पेस या ; आदि से बदले जा सकते हैं) ई।) 'यदि सत्यापन और ग्लूइंग रेंज एक दूसरे के बराबर नहीं हैं, तो एक त्रुटि के साथ बाहर निकलें यदि SearchRange1.Count <> TextRange.Count या SearchRange2.Count <> TextRange.Count तो MergeIfs = CVERr(xlErrRef) Exit Function End If 'सभी कक्षों के माध्यम से जाएं, सभी स्थितियों की जांच करें और i = 1 के लिए SearchRange1.Cells.Count यदि SearchRange1.Cells(i) = कंडीशन1 और SearchRange2.Cells(i) = कंडीशन2 के लिए वेरिएबल आउटटेक्स्ट में टेक्स्ट एकत्र करें तो आउटटेक्स्ट = आउटटेक्स्ट और TextRange.Cells(i) और डेलीमीटर एंड इफ नेक्स्ट i 'डिस्प्ले रिजल्ट्स विदाउट लास्ट डेलीमीटर MergeIfs = लेफ्ट(आउटटेक्स्ट, लेन(आउटटेक्स्ट) - लेन(डिलीमीटर)) एंड फंक्शन  

इसे ठीक उसी तरह लागू किया जाएगा - केवल तर्कों को अब और निर्दिष्ट करने की आवश्यकता है:

विधि 4. पावर क्वेरी में ग्रुपिंग और ग्लूइंग

यदि आप मुफ्त पावर क्वेरी ऐड-इन का उपयोग करते हैं, तो आप वीबीए में प्रोग्रामिंग के बिना समस्या का समाधान कर सकते हैं। एक्सेल 2010-2013 के लिए इसे यहां डाउनलोड किया जा सकता है, और एक्सेल 2016 में यह पहले से ही डिफ़ॉल्ट रूप से बनाया गया है। क्रियाओं का क्रम इस प्रकार होगा:

Power Query नियमित तालिकाओं के साथ काम करना नहीं जानता है, इसलिए पहला कदम हमारी तालिका को "स्मार्ट" तालिका में बदलना है। ऐसा करने के लिए, इसे चुनें और संयोजन दबाएं कंट्रोल+T या टैब से चुनें होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें). फिर दिखाई देने वाले टैब पर निर्माता (डिज़ाइन) आप तालिका का नाम सेट कर सकते हैं (मैंने मानक छोड़ दिया टेबल 1):

अब अपनी तालिका को Power Query ऐड-इन में लोड करते हैं। ऐसा करने के लिए, टैब पर जानकारी (यदि आपके पास एक्सेल 2016 है) या पावर क्वेरी टैब पर (यदि आपके पास एक्सेल 2010-2013 है) क्लिक करें मेज से (डेटा - तालिका से):

खुलने वाली क्वेरी संपादक विंडो में, हेडर पर क्लिक करके कॉलम का चयन करें कंपनी और ऊपर का बटन दबाएं समूह (समूह द्वारा). ग्रुपिंग में नए कॉलम का नाम और ऑपरेशन का प्रकार दर्ज करें - सभी पंक्तियाँ (सभी पंक्तियाँ):

ओके पर क्लिक करें और हमें प्रत्येक कंपनी के लिए समूहीकृत मूल्यों की एक मिनी-टेबल मिलती है। तालिकाओं की सामग्री स्पष्ट रूप से दिखाई देती है यदि आप परिणामी कॉलम में कोशिकाओं की सफेद पृष्ठभूमि (पाठ पर नहीं!) पर बायाँ-क्लिक करते हैं:

अब एक और कॉलम जोड़ते हैं, जहां, फ़ंक्शन का उपयोग करके, हम प्रत्येक मिनी-टेबल में एड्रेस कॉलम की सामग्री को कॉमा से अलग करते हैं। ऐसा करने के लिए, टैब पर कॉलम जोड़ें हम दबाते हैं कस्टम कॉलम (कॉलम जोड़ें — कस्टम कॉलम) और दिखाई देने वाली विंडो में, नए कॉलम का नाम और पावर क्वेरी में निर्मित M भाषा में युग्मन सूत्र दर्ज करें:

ध्यान दें कि सभी एम-फ़ंक्शन केस संवेदनशील होते हैं (एक्सेल के विपरीत)। पर क्लिक करने के बाद OK हमें चिपके पतों के साथ एक नया कॉलम मिलता है:

यह पहले से ही अनावश्यक कॉलम को हटाने के लिए बनी हुई है टेबलपते (शीर्षक पर राइट क्लिक करें) कॉलम हटाएं) और टैब पर क्लिक करके परिणाम शीट पर अपलोड करें होम - बंद करें और डाउनलोड करें (होम - बंद करें और लोड करें):

महत्वपूर्ण बारीकियां: पिछली विधियों (फ़ंक्शंस) के विपरीत, Power Query की तालिकाएँ स्वचालित रूप से अपडेट नहीं होती हैं। यदि भविष्य में स्रोत डेटा में कोई परिवर्तन होगा, तो आपको परिणाम तालिका में कहीं भी राइट-क्लिक करना होगा और कमांड का चयन करना होगा अपडेट करें और सहेजें (ताज़ा करें).

• एक लंबी टेक्स्ट स्ट्रिंग को भागों में कैसे विभाजित करें
• विभिन्न सेल से टेक्स्ट को एक में ग्लू करने के कई तरीके
• मास्क के विरुद्ध टेक्स्ट का परीक्षण करने के लिए लाइक ऑपरेटर का उपयोग करना