अटीनुसार मजकूर बंधनकारक

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

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

दुस-या शब्दात सांगायचे तर, आम्हाला एका साधनाची आवश्यकता आहे जे मजकूरला स्थितीनुसार चिकटवते (लिंक) - फंक्शनचे अॅनालॉग सुमेस्ली (SUMIF), परंतु मजकूरासाठी.

पद्धत 0. सूत्र

अतिशय मोहक नाही, परंतु सर्वात सोपा मार्ग. तुम्ही एक सोपा फॉर्म्युला लिहू शकता जे पुढील पंक्तीतील कंपनी मागीलपेक्षा वेगळी आहे की नाही हे तपासेल. जर ते वेगळे नसेल, तर स्वल्पविरामाने विभक्त केलेला पुढील पत्ता चिकटवा. जर ते वेगळे असेल, तर आम्ही जमा केलेले "रीसेट" करतो, पुन्हा सुरू करतो:

या दृष्टिकोनाचे तोटे स्पष्ट आहेत: प्राप्त केलेल्या अतिरिक्त स्तंभाच्या सर्व सेलमधून, आम्हाला प्रत्येक कंपनीसाठी (पिवळा) फक्त शेवटची आवश्यकता आहे. जर सूची मोठी असेल, तर ती त्वरीत निवडण्यासाठी, तुम्हाला फंक्शन वापरून दुसरा कॉलम जोडावा लागेल DLSTR (LEN), जमा झालेल्या तारांची लांबी तपासत आहे:

आता तुम्ही ते फिल्टर करू शकता आणि पुढील वापरासाठी आवश्यक अॅड्रेस ग्लूइंग कॉपी करू शकता.

पद्धत 1. एका स्थितीनुसार ग्लूइंगचे मॅक्रोफंक्शन

जर मूळ यादी कंपनीनुसार क्रमवारी लावली नसेल, तर वरील साधे सूत्र कार्य करत नाही, परंतु तुम्ही VBA मध्ये एका छोट्या सानुकूल फंक्शनसह सहजपणे फिरू शकता. कीबोर्ड शॉर्टकट दाबून व्हिज्युअल बेसिक एडिटर उघडा Alt + F11 किंवा बटण वापरून व्हिज्युअल बेसिक टॅब विकसक (विकासक). उघडणाऱ्या विंडोमध्ये, मेनूमधून एक नवीन रिक्त मॉड्यूल घाला घाला - मॉड्यूल आणि तिथे आमच्या फंक्शनचा मजकूर कॉपी करा:

फंक्शन MergeIf(श्रेणी म्हणून मजकूर रेंज, श्रेणी म्हणून शोधश्रेणी, स्ट्रिंग म्हणून स्थिती) मंद परिसीमक स्ट्रिंग म्हणून, i लाँग डेलिमीटर म्हणून = ", " ग्लूइंग एकमेकांशी समान नसतात - आम्ही त्रुटीसह बाहेर पडतो जर SearchRange.Count <> TextRange.Count नंतर MergeIf = CVErr(xlErrRef) Exit Function End If 'सर्व सेलमधून जा, स्थिती तपासा आणि आउटटेक्स्ट फॉर i = 1 ते SearchRange व्हेरिएबलमधील मजकूर गोळा करा. Cells.Count जर SearchRange.Cells(i) कंडिशन प्रमाणे असेल तर OutText = OutText & TextRange.Cells(i) आणि डेलिमीटर पुढे i 'अंतिम परिसीमाकाशिवाय परिणाम प्रदर्शित करा MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End कार्य  

आपण आता मायक्रोसॉफ्ट एक्सेलवर परत आल्यास, फंक्शन्सच्या सूचीमध्ये (बटण fx फॉर्म्युला बार किंवा टॅबमध्ये सूत्रे - फंक्शन घाला) आमचे कार्य शोधणे शक्य होईल जर विलीन करा श्रेणी मध्ये वापरकर्ता परिभाषित (वापरकर्ता परिभाषित). फंक्शनचे वितर्क खालीलप्रमाणे आहेत:

पद्धत 2. अयोग्य स्थितीनुसार मजकूर एकत्र करा

जर आपण आपल्या मॅक्रोच्या 13व्या ओळीतील पहिले वर्ण बदलले = अंदाजे जुळणी ऑपरेटरला सारखे, नंतर निवड निकषासह प्रारंभिक डेटाच्या अयोग्य जुळणीद्वारे ग्लूइंग करणे शक्य होईल. उदाहरणार्थ, जर कंपनीचे नाव वेगवेगळ्या प्रकारांमध्ये लिहिले जाऊ शकते, तर आम्ही ते सर्व एका फंक्शनसह तपासू आणि गोळा करू शकतो:

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

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

डीफॉल्टनुसार, लाईक ऑपरेटर केस सेन्सेटिव्ह असतो, म्हणजे समजतो, उदाहरणार्थ, “ओरियन” आणि “ओरियन” वेगवेगळ्या कंपन्या. केसकडे दुर्लक्ष करण्यासाठी, तुम्ही व्हिज्युअल बेसिक एडिटरमधील मॉड्यूलच्या अगदी सुरुवातीला ओळ जोडू शकता. पर्याय मजकूर तुलना, जे केस असंवेदनशील होण्यासाठी लाइक स्विच करेल.

अशा प्रकारे, आपण परिस्थिती तपासण्यासाठी अतिशय जटिल मुखवटे तयार करू शकता, उदाहरणार्थ:

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

पद्धत 3. दोन परिस्थितींमध्ये मजकूर ग्लूइंग करण्यासाठी मॅक्रो फंक्शन

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

फंक्शन MergeIfs(श्रेणी म्हणून मजकूर रेंज, श्रेणी म्हणून शोध श्रेणी1, स्ट्रिंग म्हणून स्थिती1, श्रेणी म्हणून शोध श्रेणी2, स्ट्रिंग म्हणून स्थिती 2) स्ट्रिंग म्हणून मंद डेलिमीटर, i लाँग डेलिमीटर = "," 'डिलिमिटर वर्ण (जागा किंवा ; इ.) e.) 'जर व्हॅलिडेशन आणि ग्लूइंग रेंज एकमेकांशी समान नसतील तर, त्रुटीसह बाहेर पडा जर SearchRange1.Count <> TextRange.Count किंवा SearchRange2.Count <> TextRange.Count नंतर MergeIfs = CVErr(xlErrRef) फंक्शनमधून बाहेर पडा तर 'सर्व सेलमधून जा, सर्व अटी तपासा आणि मजकूर आउटटेक्स्ट फॉर i = 1 ते SearchRange1.Cells.Count जर SearchRange1.Cells(i) = Condition1 आणि SearchRange2.Cells(i) = कंडिशन2 मध्ये गोळा करा, त्यानंतर OutText = OutText & मजकूर रेंज  

हे अगदी तशाच प्रकारे लागू केले जाईल - आता फक्त युक्तिवाद अधिक निर्दिष्ट करणे आवश्यक आहे:

पद्धत 4. ​​पॉवर क्वेरीमध्ये ग्रुपिंग आणि ग्लूइंग

तुम्ही मोफत पॉवर क्वेरी अॅड-इन वापरत असल्यास, तुम्ही VBA मध्ये प्रोग्रामिंगशिवाय समस्या सोडवू शकता. एक्सेल 2010-2013 साठी ते येथे डाउनलोड केले जाऊ शकते आणि एक्सेल 2016 मध्ये ते आधीच डीफॉल्टनुसार तयार केले आहे. क्रियांचा क्रम खालीलप्रमाणे असेल:

पॉवर क्वेरीला नियमित टेबलसह कसे कार्य करावे हे माहित नाही, म्हणून पहिली पायरी म्हणजे आमच्या टेबलला "स्मार्ट" मध्ये बदलणे. हे करण्यासाठी, ते निवडा आणि संयोजन दाबा Ctrl+T किंवा टॅबमधून निवडा मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित). त्यानंतर दिसणार्‍या टॅबवर रचनाकार (डिझाइन) तुम्ही टेबलचे नाव सेट करू शकता (मी मानक सोडले आहे टेबल 1):

आता पॉवर क्वेरी ऍड-इनमध्ये आमचे टेबल लोड करू. हे करण्यासाठी, टॅबवर डेटा (जर तुमच्याकडे एक्सेल 2016 असेल) किंवा पॉवर क्वेरी टॅबवर (तुमच्याकडे एक्सेल 2010-2013 असल्यास) क्लिक करा टेबलवरून (डेटा — टेबलवरून):

उघडणाऱ्या क्वेरी एडिटर विंडोमध्ये, हेडरवर क्लिक करून कॉलम निवडा कंपनी आणि वरील बटण दाबा गट (यानुसार गट). ग्रुपिंगमध्ये नवीन कॉलमचे नाव आणि ऑपरेशनचा प्रकार एंटर करा – सर्व ओळी (सर्व पंक्ती):

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

आता आणखी एक कॉलम जोडू या, जेथे फंक्शन वापरून, आम्ही प्रत्येक मिनी-टेबलमधील अॅड्रेस कॉलम्सची सामग्री स्वल्पविरामाने विभक्त करून चिकटवतो. हे करण्यासाठी, टॅबवर स्तंभ जोडा आम्ही दाबतो सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ) आणि दिसणार्‍या विंडोमध्ये, नवीन स्तंभाचे नाव आणि पॉवर क्वेरीमध्ये तयार केलेल्या M भाषेतील कपलिंग फॉर्म्युला प्रविष्ट करा:

लक्षात घ्या की सर्व एम-फंक्शन केस सेन्सेटिव्ह आहेत (एक्सेलच्या विपरीत). वर क्लिक केल्यानंतर OK आम्हाला चिकटलेल्या पत्त्यांसह एक नवीन स्तंभ मिळतो:

आधीच अनावश्यक स्तंभ काढणे बाकी आहे टेबल पत्ते (शीर्षकावर उजवे क्लिक करा) स्तंभ हटवा) आणि टॅबवर क्लिक करून परिणाम शीटवर अपलोड करा मुख्यपृष्ठ - बंद करा आणि डाउनलोड करा (घर - बंद करा आणि लोड करा):

महत्वाची सूक्ष्मता: मागील पद्धतींप्रमाणे (कार्ये), Power Query मधील टेबल आपोआप अपडेट होत नाहीत. भविष्यात स्त्रोत डेटामध्ये काही बदल झाल्यास, तुम्हाला परिणाम सारणीमध्ये कुठेही उजवे-क्लिक करावे लागेल आणि कमांड निवडावी लागेल. अपडेट आणि सेव्ह करा (रिफ्रेश).

• लांब मजकूर स्ट्रिंगला भागांमध्ये कसे विभाजित करावे
• वेगवेगळ्या सेलमधील मजकूर एकामध्ये चिकटवण्याचे अनेक मार्ग
• मास्क विरुद्ध मजकूर तपासण्यासाठी लाइक ऑपरेटर वापरणे