सामग्री
डेटासह कार्य करताना स्त्रोत मजकूरात कीवर्ड शोधणे हे सर्वात सामान्य कार्यांपैकी एक आहे. खालील उदाहरणाचा वापर करून त्याचे समाधान अनेक प्रकारे पाहू.
समजा तुमच्याकडे आणि माझ्याकडे कीवर्डची यादी आहे – कारच्या ब्रँडची नावे – आणि सर्व प्रकारच्या स्पेअर पार्ट्सची एक मोठी टेबल आहे, जिथे स्पेअर पार्ट एकापेक्षा जास्त फिट असल्यास, वर्णनात कधी कधी एकाच वेळी एक किंवा अनेक ब्रँड असू शकतात. कारचा ब्रँड. दिलेले विभाजक वर्ण (उदाहरणार्थ, स्वल्पविराम) द्वारे शेजारच्या सेलमधील सर्व शोधलेले कीवर्ड शोधणे आणि प्रदर्शित करणे हे आमचे कार्य आहे.
पद्धत 1. पॉवर क्वेरी
अर्थात, प्रथम आम्ही कीबोर्ड शॉर्टकट वापरून आमच्या टेबल्स डायनॅमिक (“स्मार्ट”) मध्ये बदलतो Ctrl+T किंवा आज्ञा मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित), त्यांना नावे द्या (उदाहरणार्थ शिक्केи सुटे भाग) आणि टॅबवर निवडून पॉवर क्वेरी एडिटरमध्ये एक एक लोड करा डेटा - सारणी/श्रेणीवरून (डेटा — सारणी/श्रेणीवरून). तुमच्याकडे एक्सेल 2010-2013 च्या जुन्या आवृत्त्या असल्यास, जेथे पॉवर क्वेरी स्वतंत्र अॅड-इन म्हणून स्थापित केली आहे, तर इच्छित बटण टॅबवर असेल. उर्जा प्रश्न. तुमच्याकडे Excel 365 ची अगदी नवीन आवृत्ती असल्यास, बटण टेबल/श्रेणीतून आता तिथे बोलावले पाने सह (पत्रकावरून).
पॉवर क्वेरीमध्ये प्रत्येक टेबल लोड केल्यानंतर, आम्ही कमांडसह Excel वर परत येतो मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि त्यावर लोड करा... — फक्त कनेक्शन तयार करा (होम — बंद करा आणि लोड करा — बंद करा आणि त्यावर लोड करा... — फक्त कनेक्शन तयार करा).
आता डुप्लिकेट रिक्वेस्ट तयार करू सुटे भागत्यावर उजवे-क्लिक करून आणि निवडून डुप्लिकेट विनंती (डुप्लिकेट क्वेरी), नंतर परिणामी कॉपी विनंतीचे नाव बदला निकाल आणि आम्ही त्याच्यासोबत काम करत राहू.
क्रियांचे तर्कशास्त्र खालीलप्रमाणे आहे:
- प्रगत टॅबवर एक स्तंभ जोडत आहे एक संघ निवडा सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ) आणि सूत्र प्रविष्ट करा = ब्रँड. वर क्लिक केल्यानंतर OK आम्हाला एक नवीन कॉलम मिळेल, जिथे प्रत्येक सेलमध्ये आमच्या कीवर्डची सूची असलेली एक नेस्टेड टेबल असेल - ऑटोमेकर ब्रँड:
- सर्व नेस्टेड सारण्या विस्तृत करण्यासाठी जोडलेल्या स्तंभाच्या शीर्षलेखातील दुहेरी बाणांसह बटण वापरा. त्याच वेळी, स्पेअर पार्ट्सच्या वर्णनासह रेषा ब्रँडच्या संख्येच्या गुणाकाराने गुणाकार करतील आणि आम्हाला "स्पेअर पार्ट-ब्रँड" च्या सर्व संभाव्य जोड्या मिळतील:
- प्रगत टॅबवर एक स्तंभ जोडत आहे एक संघ निवडा सशर्त स्तंभ (सशर्त स्तंभ) आणि स्त्रोत मजकूर (भाग वर्णन) मध्ये कीवर्ड (ब्रँड) च्या घटना तपासण्यासाठी एक अट सेट करा:
- शोध केस असंवेदनशील करण्यासाठी, सूत्र बारमध्ये तिसरा युक्तिवाद व्यक्तिचलितपणे जोडा तुलना करा.OrdinalIgnoreCase घटना तपासणी कार्यासाठी मजकूर.समाविष्ट आहे (फॉर्म्युला बार दिसत नसल्यास, तो टॅबवर सक्षम केला जाऊ शकतो पुनरावलोकन):
- आम्ही परिणामी सारणी फिल्टर करतो, फक्त शेवटच्या स्तंभात, म्हणजे जुळण्या सोडतो आणि अनावश्यक स्तंभ काढून टाकतो. घटना.
- कमांडसह एकसारखे वर्णन गटबद्ध करणे गट टॅब परिवर्तन (परिवर्तन — गटानुसार). एकत्रीकरण ऑपरेशन म्हणून, निवडा सर्व ओळी (सर्व पंक्ती). आउटपुटवर, आम्हाला टेबलांसह एक स्तंभ मिळतो, ज्यामध्ये आम्हाला आवश्यक असलेल्या ऑटोमेकर्सच्या ब्रँडसह प्रत्येक स्पेअर पार्टसाठी सर्व तपशील असतात:
- प्रत्येक भागासाठी ग्रेड काढण्यासाठी, टॅबवर दुसरा गणना केलेला स्तंभ जोडा एक स्तंभ जोडणे - सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ) आणि टेबलचा समावेश असलेले सूत्र वापरा (ते आमच्या स्तंभात आहेत माहिती) आणि काढलेल्या स्तंभाचे नाव:
- आम्ही परिणामी स्तंभाच्या शीर्षलेखातील दुहेरी बाणांसह बटणावर क्लिक करतो आणि कमांड निवडा मूल्ये काढा (मूल्ये काढा)तुम्हाला पाहिजे असलेल्या कोणत्याही परिसीमक वर्णासह स्टॅम्प आउटपुट करण्यासाठी:
- अनावश्यक स्तंभ काढून टाकत आहे माहिती.
- परिणामी सारणीमध्ये त्यातून गायब झालेले भाग जोडण्यासाठी, जेथे वर्णनांमध्ये कोणतेही ब्रँड आढळले नाहीत, आम्ही क्वेरी एकत्र करण्याची प्रक्रिया करतो. निकाल मूळ विनंतीसह सुटे भाग बटण एकत्र टॅब होम पेज (मुख्यपृष्ठ - क्वेरी एकत्र करा). कनेक्शन प्रकार - बाह्य सामील उजवीकडे (उजवे बाह्य जोडणे):
- बाकीचे फक्त अतिरिक्त कॉलम काढून टाकणे आणि बाकीचे नाव बदलणे-हलवणे हे आहे - आणि आमचे कार्य सोडवले आहे:
पद्धत 2. सूत्रे
तुमच्याकडे Excel 2016 ची किंवा नंतरची आवृत्ती असल्यास, नवीन फंक्शन वापरून आमची समस्या अतिशय संक्षिप्त आणि मोहक पद्धतीने सोडवली जाऊ शकते. एकत्र (TEXTJOIN):
या सूत्रामागील तर्क सोपे आहे:
- कार्य शोध (शोधणे) भागाच्या वर्तमान वर्णनानुसार प्रत्येक ब्रँडच्या घटनेचा शोध घेते आणि एकतर चिन्हाचा अनुक्रमांक, ज्यापासून ब्रँड सापडला होता, किंवा त्रुटी #VALUE! ब्रँड वर्णनात नसल्यास.
- नंतर फंक्शन वापरून IF (तर) и इओशिबका (ISERROR) आम्ही त्रुटी रिकाम्या मजकूर स्ट्रिंगने बदलतो “”, आणि स्वतः ब्रँड नावांसह वर्णांची क्रमिक संख्या.
- रिकाम्या सेल आणि सापडलेल्या ब्रँड्सची परिणामी अॅरे फंक्शन वापरून दिलेल्या विभाजक वर्णाद्वारे एकाच स्ट्रिंगमध्ये एकत्र केली जाते एकत्र (TEXTJOIN).
स्पीडअपसाठी कार्यप्रदर्शन तुलना आणि पॉवर क्वेरी क्वेरी बफरिंग
कार्यप्रदर्शन चाचणीसाठी, प्रारंभिक डेटा म्हणून 100 स्पेअर पार्ट्सच्या वर्णनांची सारणी घेऊ. त्यावर आम्हाला खालील परिणाम मिळतात:
- सूत्रांनुसार पुनर्गणना वेळ (पद्धत 2) – 9 से. जेव्हा तुम्ही प्रथम संपूर्ण स्तंभात सूत्र कॉपी करता आणि 2 से. पुनरावृत्तीवर (बफरिंग प्रभावित करते, कदाचित).
- पॉवर क्वेरी क्वेरीची अपडेट वेळ (पद्धत 1) खूपच वाईट आहे – 110 सेकंद.
अर्थात, एखाद्या विशिष्ट पीसीच्या हार्डवेअरवर आणि ऑफिसची स्थापित आवृत्ती आणि अद्यतनांवर बरेच काही अवलंबून असते, परंतु एकूण चित्र, मला वाटते, स्पष्ट आहे.
पॉवर क्वेरी क्वेरीचा वेग वाढवण्यासाठी, लुकअप टेबल बफर करू या शिक्के, कारण ते क्वेरी अंमलबजावणीच्या प्रक्रियेत बदलत नाही आणि त्याची सतत पुनर्गणना करणे आवश्यक नाही (जसे पॉवर क्वेरी डी फॅक्टो करते). यासाठी आपण फंक्शन वापरतो टेबल.बफर अंगभूत पॉवर क्वेरी भाषेतून एम.
हे करण्यासाठी, एक क्वेरी उघडा निकाल आणि टॅबवर पुनरावलोकन बटण दाबा प्रगत संपादक (पहा — प्रगत संपादक). उघडणाऱ्या विंडोमध्ये, नवीन व्हेरिएबलसह एक ओळ जोडा मार्की २, जी आमच्या ऑटोमेकर निर्देशिकेची बफर केलेली आवृत्ती असेल आणि पुढील क्वेरी कमांडमध्ये हे नवीन व्हेरिएबल नंतर वापरा:
अशा परिष्करणानंतर, आमच्या विनंतीची अद्यतन गती जवळजवळ 7 पटीने वाढते – 15 सेकंदांपर्यंत. अगदी वेगळी गोष्ट 🙂
- Power Query मध्ये अस्पष्ट मजकूर शोध
- सूत्रांसह मोठ्या प्रमाणात मजकूर बदलणे
- List.Acumulate फंक्शनसह पॉवर क्वेरीमध्ये मोठ्या प्रमाणात मजकूर बदलणे