डेटा के साथ काम करते समय स्रोत टेक्स्ट में कीवर्ड खोजना सबसे आम कार्यों में से एक है। आइए निम्नलिखित उदाहरण का उपयोग करके इसके समाधान को कई तरह से देखें:
मान लें कि आपके और मेरे पास खोजशब्दों की एक सूची है - कार ब्रांडों के नाम - और सभी प्रकार के स्पेयर पार्ट्स की एक बड़ी तालिका, जहाँ विवरण में कभी-कभी एक या कई ऐसे ब्रांड हो सकते हैं, यदि स्पेयर पार्ट एक से अधिक फिट बैठता है कार का ब्रांड। हमारा कार्य किसी दिए गए विभाजक वर्ण (उदाहरण के लिए, अल्पविराम) के माध्यम से पड़ोसी कोशिकाओं में सभी खोजे गए खोजशब्दों को खोजना और प्रदर्शित करना है।
विधि 1. पावर क्वेरी
बेशक, पहले हम कीबोर्ड शॉर्टकट का उपयोग करके अपनी तालिकाओं को गतिशील ("स्मार्ट") में बदल देते हैं कंट्रोल+T या आदेश होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें), उन्हें नाम दें (उदाहरण के लिए टिकटोंи स्पेयर पार्ट्स) और टैब पर चयन करके एक-एक करके Power Query संपादक में लोड करें डेटा - टेबल/रेंज से (डेटा - टेबल/रेंज से). यदि आपके पास Excel 2010-2013 के पुराने संस्करण हैं, जहाँ Power Query को एक अलग ऐड-इन के रूप में स्थापित किया गया है, तो वांछित बटन टैब पर होगा पावर क्वेरी. यदि आपके पास Excel 365 का बिल्कुल नया संस्करण है, तो बटन टेबल/रेंज से अब वहाँ बुलाया पत्तों के साथ (शीट से).
Power Query में प्रत्येक तालिका को लोड करने के बाद, हम आदेश के साथ Excel पर वापस लौटते हैं होम — बंद करें और लोड करें — बंद करें और इसमें लोड करें… — केवल कनेक्शन बनाएं (होम - बंद करें और लोड करें - बंद करें और लोड करें ... - केवल कनेक्शन बनाएं).
अब एक डुप्लीकेट रिक्वेस्ट क्रिएट करते हैं स्पेयर पार्ट्सउस पर राइट-क्लिक करके और चयन करके डुप्लिकेट अनुरोध (डुप्लिकेट क्वेरी), फिर परिणामी प्रतिलिपि अनुरोध का नाम बदलें परिणाम और हम उसके साथ काम करना जारी रखेंगे।
क्रियाओं का तर्क इस प्रकार है:
- उन्नत टैब पर एक कॉलम जोड़ना एक टीम चुनें कस्टम कॉलम (कॉलम जोड़ें — कस्टम कॉलम) और सूत्र दर्ज करें = ब्रांड्स. पर क्लिक करने के बाद OK हमें एक नया कॉलम मिलेगा, जहां प्रत्येक सेल में हमारे कीवर्ड्स की सूची के साथ एक नेस्टेड टेबल होगी - ऑटोमेकर ब्रांड्स:
- सभी नेस्टेड तालिकाओं का विस्तार करने के लिए जोड़े गए कॉलम के हेडर में डबल एरो वाले बटन का उपयोग करें। उसी समय, स्पेयर पार्ट्स के विवरण वाली लाइनें कई ब्रांडों की संख्या से गुणा हो जाएंगी, और हमें "स्पेयर पार्ट-ब्रांड" के सभी संभावित जोड़े-संयोजन मिलेंगे:
- उन्नत टैब पर एक कॉलम जोड़ना एक टीम चुनें सशर्त स्तंभ (सशर्त स्तंभ) और स्रोत पाठ (आंशिक विवरण) में किसी कीवर्ड (ब्रांड) की घटना की जाँच के लिए एक शर्त निर्धारित करें:
- खोज मामले को असंवेदनशील बनाने के लिए, सूत्र पट्टी में मैन्युअल रूप से तीसरा तर्क जोड़ें तुलना करें। ऑर्डिनल इग्नोरकेस घटना जाँच समारोह के लिए पाठ। शामिल है (यदि सूत्र पट्टी दिखाई नहीं दे रही है, तो इसे टैब पर सक्षम किया जा सकता है समीक्षा):
- हम परिणामी तालिका को फ़िल्टर करते हैं, अंतिम कॉलम में केवल एक को छोड़ते हैं, यानी मिलान करते हैं और अनावश्यक कॉलम को हटा देते हैं घटनाओं.
- कमांड के साथ समान विवरण समूहीकृत करना समूह द्वारा टैब परिवर्तन (रूपांतरण - समूह द्वारा). एकत्रीकरण ऑपरेशन के रूप में, चुनें सभी पंक्तियाँ (सभी पंक्तियाँ). आउटपुट पर, हमें तालिकाओं के साथ एक कॉलम मिलता है, जिसमें प्रत्येक स्पेयर पार्ट के लिए सभी विवरण शामिल होते हैं, जिसमें हमें आवश्यक वाहन निर्माताओं के ब्रांड भी शामिल हैं:
- प्रत्येक भाग के लिए ग्रेड निकालने के लिए, टैब पर एक अन्य परिकलित कॉलम जोड़ें एक कॉलम जोड़ना - कस्टम कॉलम (कॉलम जोड़ें — कस्टम कॉलम) और तालिका वाले सूत्र का उपयोग करें (वे हमारे कॉलम में स्थित हैं विवरण) और निकाले गए कॉलम का नाम:
- हम परिणामी कॉलम के हेडर में डबल एरो वाले बटन पर क्लिक करते हैं और कमांड का चयन करते हैं मूल्य निकालें (मूल्य निकालें)आप चाहते हैं कि किसी भी सीमांकक चरित्र के साथ टिकटों का उत्पादन करने के लिए:
- एक अनावश्यक कॉलम हटाना विवरण.
- परिणामी तालिका में उन हिस्सों को जोड़ने के लिए जो इससे गायब हो गए, जहां विवरण में कोई ब्रांड नहीं मिला, हम क्वेरी के संयोजन के लिए प्रक्रिया करते हैं परिणाम मूल अनुरोध के साथ स्पेयर पार्ट्स बटन मिलाना टैब होम (होम - प्रश्नों को मर्ज करें). रिश्ते का प्रकार - आउटर जॉइन राइट (दायां बाहरी जुड़ाव):
- जो कुछ बचा है वह अतिरिक्त स्तंभों को हटाना है और शेष का नाम बदलना है - और हमारा कार्य हल हो गया है:
विधि 2. सूत्र
यदि आपके पास एक्सेल 2016 या बाद का संस्करण है, तो हमारी समस्या को नए फ़ंक्शन का उपयोग करके बहुत ही कॉम्पैक्ट और सुरुचिपूर्ण तरीके से हल किया जा सकता है जोड़ना (टेक्स्टजॉइन):
इस सूत्र के पीछे का तर्क सरल है:
- समारोह खोजे (पाना) भाग के वर्तमान विवरण में बदले में प्रत्येक ब्रांड की घटना की खोज करता है और या तो प्रतीक की क्रम संख्या देता है, जिससे ब्रांड पाया गया था, या त्रुटि #VALUE! अगर ब्रांड विवरण में नहीं है।
- फिर फ़ंक्शन का उपयोग करना IF (अगर) и ईओशिबका (इसरर) हम त्रुटियों को एक खाली टेक्स्ट स्ट्रिंग "", और वर्णों की क्रमिक संख्याओं को स्वयं ब्रांड नाम से प्रतिस्थापित करते हैं।
- फ़ंक्शन का उपयोग करके दिए गए विभाजक वर्ण के माध्यम से खाली कोशिकाओं और पाए गए ब्रांडों के परिणामी सरणी को एक स्ट्रिंग में इकट्ठा किया जाता है जोड़ना (टेक्स्टजॉइन).
स्पीडअप के लिए प्रदर्शन तुलना और पावर क्वेरी बफ़रिंग
प्रदर्शन परीक्षण के लिए, प्रारंभिक डेटा के रूप में 100 स्पेयर पार्ट्स विवरणों की एक तालिका लेते हैं। इस पर हमें निम्नलिखित परिणाम मिलते हैं:
- सूत्रों द्वारा पुनर्गणना समय (विधि 2) - 9 सेकंड। जब आप पहली बार सूत्र को पूरे कॉलम और 2 सेकंड में कॉपी करते हैं। बार-बार (बफरिंग प्रभावित करता है, शायद)।
- Power Query क्वेरी (विधि 1) का अद्यतन समय बहुत खराब है - 110 सेकंड.
बेशक, बहुत कुछ एक विशेष पीसी के हार्डवेयर और कार्यालय के स्थापित संस्करण और अपडेट पर निर्भर करता है, लेकिन समग्र तस्वीर, मुझे लगता है, स्पष्ट है।
Power Query क्वेरी को गति देने के लिए, आइए लुकअप तालिका को बफ़र करें टिकटों, क्योंकि यह क्वेरी निष्पादन की प्रक्रिया में नहीं बदलता है और इसे लगातार पुनर्गणना करने की आवश्यकता नहीं है (जैसा कि पावर क्वेरी वास्तव में करता है)। इसके लिए हम फंक्शन का इस्तेमाल करते हैं टेबल। बफर बिल्ट-इन Power Query भाषा से M.
ऐसा करने के लिए, एक प्रश्न खोलें परिणाम और टैब पर समीक्षा बटन दबाएँ उन्नत संपादक (देखें - उन्नत संपादक). खुलने वाली विंडो में, एक नए वेरिएबल के साथ एक लाइन जोड़ें मार्की 2, जो हमारी ऑटोमेकर निर्देशिका का एक बफ़र्ड संस्करण होगा, और बाद में निम्न क्वेरी कमांड में इस नए चर का उपयोग करें:
इस तरह के शोधन के बाद, हमारे अनुरोध की अद्यतन गति लगभग 7 गुना - 15 सेकंड तक बढ़ जाती है। काफी अलग बात 🙂
- Power Query में फ़ज़ी टेक्स्ट खोज
- सूत्रों के साथ बल्क टेक्स्ट प्रतिस्थापन
- Power Query में List.Accumulate function के साथ बल्क टेक्स्ट रिप्लेसमेंट