सामग्रीहरू
स्रोत पाठमा कुञ्जी शब्दहरू खोज्नु भनेको डाटासँग काम गर्दा सबैभन्दा सामान्य कार्यहरू मध्ये एक हो। निम्न उदाहरण प्रयोग गरेर यसको समाधानलाई धेरै तरिकामा हेरौं:
मानौं कि तपाईं र मसँग कीवर्डहरूको सूची छ - कार ब्रान्डहरूको नामहरू - र सबै प्रकारका स्पेयर पार्ट्सहरूको ठूलो तालिका, जहाँ विवरणमा कहिलेकाहीँ एक वा धेरै ब्रान्डहरू एकै पटकमा समावेश हुन सक्छन्, यदि स्पेयर पार्ट एक भन्दा बढी फिट हुन्छ। कार को ब्रान्ड। हाम्रो कार्य छिमेकी कक्षहरूमा सबै पत्ता लगाइएका कुञ्जी शब्दहरूलाई दिइएको विभाजक क्यारेक्टर (उदाहरणका लागि, अल्पविराम) मार्फत फेला पार्नु हो।
विधि ३. पावर क्वेरी
निस्सन्देह, पहिले हामी किबोर्ड सर्टकट प्रयोग गरेर हाम्रो तालिकालाई गतिशील ("स्मार्ट") मा परिणत गर्छौं। Ctrl+T वा आदेशहरू गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा), तिनीहरूलाई नाम दिनुहोस् (उदाहरणका लागि टिकटहरुи स्पेयर पार्ट्स) र ट्याबमा चयन गरेर Power Query सम्पादकमा एक-एक गरी लोड गर्नुहोस् डाटा - तालिका/दायराबाट (डेटा - तालिका/दायराबाट)। यदि तपाइँसँग एक्सेल 2010-2013 को पुरानो संस्करणहरू छन्, जहाँ पावर क्वेरी छुट्टै एड-इनको रूपमा स्थापना गरिएको छ, त्यसपछि इच्छित बटन ट्याबमा हुनेछ। पावर क्वेरी। यदि तपाइँसँग एक्सेल 365 को ब्रान्ड नयाँ संस्करण छ भने, त्यसपछि बटन तालिका/दायराबाट अहिले त्यहाँ बोलाए पातहरु संग (पानाबाट).
पावर क्वेरीमा प्रत्येक तालिका लोड गरेपछि, हामी आदेशको साथ एक्सेलमा फर्कन्छौं गृह - बन्द र लोड - बन्द र लोड गर्न ... - जडान मात्र सिर्जना गर्नुहोस् (घर - बन्द र लोड - बन्द र लोड गर्न ... - जडान मात्र सिर्जना गर्नुहोस्).
अब डुप्लिकेट अनुरोध सिर्जना गरौं स्पेयर पार्ट्सयसमा दायाँ क्लिक गरेर र चयन गरेर डुप्लिकेट अनुरोध (डुप्लिकेट क्वेरी), त्यसपछि नतिजा प्रतिलिपि अनुरोधलाई पुन: नामाकरण गर्नुहोस् परिणामहरू र हामी उहाँसँग काम गर्न जारी राख्नेछौं।
कार्यहरूको तर्क निम्न छ:
- उन्नत ट्याबमा स्तम्भ थप्दै टोली छान्नुहोस् अनुकूलन स्तम्भ (स्तम्भ थप्नुहोस् — अनुकूलन स्तम्भ) र सूत्र प्रविष्ट गर्नुहोस् = ब्रान्डहरू। क्लिक गरेपछि OK हामी नयाँ स्तम्भ प्राप्त गर्नेछौं, जहाँ प्रत्येक कक्षमा हाम्रा किवर्डहरूको सूचीको साथ एउटा नेस्टेड तालिका हुनेछ - automaker ब्रान्डहरू:
- सबै नेस्टेड तालिकाहरू विस्तार गर्न थपिएको स्तम्भको हेडरमा दुईवटा तीरहरूसँग बटन प्रयोग गर्नुहोस्। एकै समयमा, स्पेयर पार्ट्सको विवरणका साथ लाइनहरू ब्रान्डहरूको संख्याको गुणाले गुणन हुनेछ, र हामीले "स्पेयर पार्ट-ब्रान्ड" को सबै सम्भावित जोडीहरू प्राप्त गर्नेछौं:
- उन्नत ट्याबमा स्तम्भ थप्दै टोली छान्नुहोस् सशर्त स्तम्भ (सशर्त स्तम्भ) र स्रोत पाठ (अंश विवरण) मा कुञ्जी शब्द (ब्रान्ड) को उपस्थिति जाँच गर्न एक शर्त सेट गर्नुहोस्:
- खोज केस असंवेदनशील बनाउन, म्यानुअल रूपमा सूत्र पट्टीमा तेस्रो तर्क थप्नुहोस् तुलना गर्नुहोस्। OrdinalIgnoreCase घटना जाँच समारोहमा पाठ। समावेश छ (यदि सूत्र पट्टी देखिएन भने, तब यसलाई ट्याबमा सक्षम गर्न सकिन्छ समीक्षा):
- हामी परिणाम तालिका फिल्टर गर्छौं, अन्तिम स्तम्भमा मात्र छोड्छौं, अर्थात् मिल्छ र अनावश्यक स्तम्भ हटाउँछौं। घटनाहरू.
- आदेशको साथ समान विवरणहरू समूहबद्ध गर्दै द्वारा समूह ट्याब परिवर्तन (रूपान्तरण - समूह द्वारा)। एकीकरण कार्यको रूपमा, छनौट गर्नुहोस् सबै रेखाहरू (सबै पङ्क्तिहरू)। आउटपुटमा, हामीले टेबलसहितको स्तम्भ पाउँछौं, जसमा हामीलाई चाहिने अटोमेकरहरूको ब्रान्डहरू सहित प्रत्येक स्पेयर पार्टका लागि सबै विवरणहरू समावेश छन्:
- प्रत्येक भागको लागि ग्रेडहरू निकाल्न, ट्याबमा अर्को गणना गरिएको स्तम्भ थप्नुहोस् स्तम्भ थप्दै - अनुकूलन स्तम्भ (स्तम्भ थप्नुहोस् — अनुकूलन स्तम्भ) र तालिका समावेश भएको सूत्र प्रयोग गर्नुहोस् (तिनीहरू हाम्रो स्तम्भमा अवस्थित छन् विवरण) र निकालिएको स्तम्भको नाम:
- हामी नतिजा स्तम्भको हेडरमा डबल एरोको साथ बटनमा क्लिक गर्छौं र आदेश चयन गर्छौं मानहरू निकाल्नुहोस् (मानहरू निकाल्नुहोस्)तपाईले चाहानु भएको कुनै पनि डेलिमिटर क्यारेक्टरको साथ स्ट्याम्पहरू आउटपुट गर्न:
- अनावश्यक स्तम्भ हटाउँदै विवरण.
- नतिजा तालिकामा यसबाट गायब भएका भागहरू थप्नको लागि, जहाँ वर्णनहरूमा कुनै ब्रान्डहरू फेला परेनन्, हामी क्वेरी संयोजन गर्ने प्रक्रिया पूरा गर्छौं। परिणाम मूल अनुरोध संग स्पेयर पार्ट्स टांक जोड्नुहोस् ट्याब गृहपृष्ठ (गृह - प्रश्नहरू मर्ज गर्नुहोस्)। जडान प्रकार - बाहिरी जोइन दायाँ (दायाँ बाहिरी जोड):
- बाँकी सबै अतिरिक्त स्तम्भहरू हटाउन र बाँकीहरूलाई पुन: नामाकरण-सार्न हो - र हाम्रो कार्य हल भयो:
विधि 2. सूत्रहरू
यदि तपाइँसँग एक्सेल 2016 वा पछिको संस्करण छ भने, हाम्रो समस्या नयाँ प्रकार्य प्रयोग गरेर धेरै कम्प्याक्ट र सुरुचिपूर्ण तरिकामा समाधान गर्न सकिन्छ। COMBINE (TEXTJOIN):
यो सूत्र पछाडि तर्क सरल छ:
- समारोह खोज्नुहोस (फेला) भागको हालको विवरणमा पालैपालो प्रत्येक ब्रान्डको घटनाको लागि खोजी गर्दछ र या त प्रतीकको क्रम संख्या फर्काउँछ, जहाँबाट ब्रान्ड फेला परेको थियो, वा त्रुटि #VALUE! यदि ब्रान्ड विवरणमा छैन।
- त्यसपछि प्रकार्य प्रयोग गर्दै IF (यदि) и EOSHIBKA (ISERROR) हामी त्रुटिहरूलाई खाली पाठ स्ट्रिङ "", र क्यारेक्टरहरूको क्रम संख्याहरू ब्रान्ड नामहरू आफैंले बदल्छौं।
- खाली कक्षहरू र फेला परेका ब्रान्डहरूको परिणामस्वरूप एरेलाई प्रकार्य प्रयोग गरेर दिइएको विभाजक क्यारेक्टर मार्फत एकल स्ट्रिङमा भेला गरिन्छ। COMBINE (TEXTJOIN).
स्पीडअपको लागि प्रदर्शन तुलना र पावर क्वेरी क्वेरी बफरिङ
प्रदर्शन परीक्षणको लागि, प्रारम्भिक डेटाको रूपमा 100 स्पेयर पार्ट्स विवरणहरूको तालिका लिनुहोस्। यसमा हामीले निम्न परिणामहरू पाउँछौं:
- सूत्रहरू द्वारा पुन: गणना समय (विधि 2) - 9 सेकेन्ड। जब तपाइँ पहिलो पटक सम्पूर्ण स्तम्भमा सूत्र प्रतिलिपि गर्नुहुन्छ र 2 सेकेन्ड। बारम्बार (बफरिङले असर गर्छ, सायद)।
- पावर क्वेरी क्वेरीको अद्यावधिक समय (विधि 1) धेरै खराब छ - 110 सेकेन्ड।
निस्सन्देह, धेरै एक विशेष पीसी को हार्डवेयर र Office र अपडेट को स्थापित संस्करण मा निर्भर गर्दछ, तर समग्र तस्वीर, मलाई लाग्छ, स्पष्ट छ।
पावर क्वेरी क्वेरीको गति बढाउनको लागि, लुकअप तालिका बफर गरौं टिकटहरु, किनभने यसले क्वेरी कार्यान्वयनको प्रक्रियामा परिवर्तन गर्दैन र यसलाई निरन्तर रूपमा पुन: गणना गर्न आवश्यक छैन (जस्तै Power Query de facto)। यसका लागि हामी प्रकार्य प्रयोग गर्दछौं तालिका। बफर निर्मित पावर क्वेरी भाषा M बाट।
यो गर्नका लागि, एउटा प्रश्न खोल्नुहोस् परिणामहरू र ट्याबमा समीक्षा बटन थिच्नुहोस् उन्नत सम्पादक (हेर्नुहोस् - उन्नत सम्पादक)। खुल्ने विन्डोमा, नयाँ चरको साथ लाइन थप्नुहोस् मार्की २, जुन हाम्रो automaker डाइरेक्टरीको बफर गरिएको संस्करण हुनेछ, र यो नयाँ भेरिएबल पछि निम्न क्वेरी आदेशमा प्रयोग गर्नुहोस्:
यस्तो परिष्करण पछि, हाम्रो अनुरोधको अपडेट गति लगभग 7 गुणाले बढ्छ - 15 सेकेन्ड सम्म। एकदम फरक कुरा 🙂
- Power Query मा अस्पष्ट पाठ खोज
- सूत्रहरूसँग बल्क पाठ प्रतिस्थापन
- List.Acumulate प्रकार्यको साथ पावर क्वेरीमा बल्क पाठ प्रतिस्थापन