अभी हाल ही में, हमने इंटरनेट से XML डेटा आयात करने के लिए FILTER.XML फ़ंक्शन के उपयोग पर चर्चा की - मुख्य कार्य जिसके लिए यह फ़ंक्शन, वास्तव में, अभिप्रेत है। रास्ते में, हालांकि, इस फ़ंक्शन का एक और अप्रत्याशित और सुंदर उपयोग सामने आया है - चिपचिपा पाठ को कोशिकाओं में जल्दी से विभाजित करने के लिए।
मान लें कि हमारे पास इस तरह का डेटा कॉलम है:
बेशक, सुविधा के लिए, मैं इसे अलग-अलग कॉलम में विभाजित करना चाहूंगा: कंपनी का नाम, शहर, सड़क, घर। आप इसे कई अलग-अलग तरीकों से कर सकते हैं:
- उपयोग कॉलम द्वारा टेक्स्ट टैब से जानकारी (डेटा - कॉलम से टेक्स्ट) और तीन कदम जाओ टेक्स्ट पार्सर. लेकिन अगर कल डेटा बदलता है, तो आपको पूरी प्रक्रिया फिर से दोहरानी होगी।
- इस डेटा को पावर क्वेरी में लोड करें और इसे वहां विभाजित करें, और फिर इसे वापस शीट पर अपलोड करें, और फिर डेटा बदलने पर क्वेरी को अपडेट करें (जो पहले से आसान है)।
- यदि आपको मक्खी पर अद्यतन करने की आवश्यकता है, तो आप अल्पविराम खोजने और उनके बीच के पाठ को निकालने के लिए कुछ बहुत ही जटिल सूत्र लिख सकते हैं।
और आप इसे और अधिक सुंदर ढंग से कर सकते हैं और FILTER.XML फ़ंक्शन का उपयोग कर सकते हैं, लेकिन इसका इससे क्या लेना-देना है?
FILTER.XML फ़ंक्शन अपने प्रारंभिक तर्क के रूप में एक XML कोड प्राप्त करता है - विशेष टैग और विशेषताओं के साथ चिह्नित पाठ, और फिर इसे इसके घटकों में पार्स करता है, हमें आवश्यक डेटा अंशों को निकालता है। एक्सएमएल कोड आमतौर पर ऐसा कुछ दिखता है:
एक्सएमएल में, प्रत्येक डेटा तत्व टैग में संलग्न होना चाहिए। एक टैग कुछ पाठ है (ऊपर के उदाहरण में यह प्रबंधक, नाम, लाभ है) कोण कोष्ठक में संलग्न है। टैग हमेशा जोड़े में आते हैं - खोलना और बंद करना (शुरुआत में जोड़े गए स्लैश के साथ)।
FILTER.XML फ़ंक्शन हमारे लिए आवश्यक सभी टैग की सामग्री को आसानी से निकाल सकता है, उदाहरण के लिए, सभी प्रबंधकों के नाम, और (सबसे महत्वपूर्ण) उन सभी को एक सूची में एक साथ प्रदर्शित करें। तो हमारा काम स्रोत टेक्स्ट में टैग जोड़ना है, इसे FILTER.XML फ़ंक्शन द्वारा बाद के विश्लेषण के लिए उपयुक्त XML कोड में बदलना है।
यदि हम अपनी सूची में से पहला पता एक उदाहरण के रूप में लेते हैं, तो हमें इसे इस निर्माण में बदलना होगा:
मैंने ग्लोबल ओपनिंग और क्लोजिंग ऑल टेक्स्ट टैग को कॉल किया t, और प्रत्येक तत्व को तैयार करने वाले टैग हैं s।, लेकिन आप किसी अन्य पदनाम का उपयोग कर सकते हैं - इससे कोई फर्क नहीं पड़ता।
यदि हम इस कोड से इंडेंट और लाइन ब्रेक हटाते हैं - पूरी तरह से, वैसे, वैकल्पिक और केवल स्पष्टता के लिए जोड़ा जाता है, तो यह सब एक लाइन में बदल जाएगा:
और इसे पहले से ही स्रोत पते से अपेक्षाकृत आसानी से प्राप्त किया जा सकता है, इसमें अल्पविराम को कुछ टैग के साथ बदलकर प्राप्त किया जा सकता है फ़ंक्शन का उपयोग करना विकल्प (स्थानापन्न) और प्रतीक के साथ gluing & उद्घाटन और समापन टैग की शुरुआत और अंत में:
परिणामी सीमा को क्षैतिज रूप से विस्तारित करने के लिए, हम मानक फ़ंक्शन का उपयोग करते हैं ट्रांसपी (ट्रांसपोज़), इसमें हमारे सूत्र को लपेटते हुए:
इस संपूर्ण डिज़ाइन की एक महत्वपूर्ण विशेषता यह है कि गतिशील सरणियों के समर्थन के साथ Office 2021 और Office 365 के नए संस्करण में, इनपुट के लिए किसी विशेष जेस्चर की आवश्यकता नहीं है - बस दर्ज करें और क्लिक करें दर्ज - सूत्र अपने आप में आवश्यक कोशिकाओं की संख्या पर कब्जा कर लेता है और सब कुछ एक धमाके के साथ काम करता है। पिछले संस्करणों में, जहां अभी तक कोई गतिशील सरणी नहीं थी, आपको पहले सूत्र में प्रवेश करने से पहले पर्याप्त संख्या में खाली कक्षों का चयन करना होगा (आप मार्जिन के साथ कर सकते हैं), और सूत्र बनाने के बाद, कीबोर्ड शॉर्टकट दबाएं कंट्रोल+पाली+दर्जइसे एक सरणी सूत्र के रूप में दर्ज करने के लिए।
लाइन ब्रेक के माध्यम से एक सेल में एक साथ फंसे टेक्स्ट को अलग करते समय एक समान चाल का उपयोग किया जा सकता है:
पिछले उदाहरण के साथ एकमात्र अंतर यह है कि अल्पविराम के बजाय, यहां हम अदृश्य Alt + Enter लाइन ब्रेक वर्ण को प्रतिस्थापित करते हैं, जिसे कोड 10 के साथ CHAR फ़ंक्शन का उपयोग करके सूत्र में निर्दिष्ट किया जा सकता है।
- एक्सेल में लाइन ब्रेक (Alt + Enter) के साथ काम करने की सूक्ष्मता
- एक्सेल में टेक्स्ट को कॉलम से विभाजित करें
- टेक्स्ट को SUBSTITUTE से बदलना