श्रेणी सूचकांक में आंशिक पाठ के मिलान के आधार पर Google पत्रक में पाठ को वर्गीकृत करें

Nov 25 2020

मैं मैच के लिए देख रहा हूँ, VLOOKUP के साथ, मुख्य शीट में एक कॉलम में शब्दों का हिस्सा कीवर्ड / श्रेणियों के एक इंडेक्स में है जहाँ आंशिक शब्दों को दूसरी शीट में आसानी के लिए संग्रहीत किया जाता है।

यहाँ एक उदाहरण कार्यपत्रक है: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

  1. व्यय पत्रक में डेटा है जैसा कि हम इसे प्राप्त करते हैं, कॉलम ए से सी में।
  2. कॉलम D में, हम इन खर्चों के लिए एक श्रेणी को ऑटो-असाइन करना चाहते हैं। यदि हम उदाहरण के लिए, कॉलम B में सटीक पाठ के साथ ऐसा करते हैं BANK VISA PREAUTH PYMT, तो यह आसान है। कॉलम D में VLOOKUP फॉर्मूला 'श्रेणियाँ' टैब से यह सटीक संदर्भ प्राप्त करके काम करता है (सेल A1 को इस तरह से शब्द दिया गया है)। यह सूत्र काम करता है:=IFERROR(VLOOKUP(B2,Categories!A:B,2,FALSE),"")
  3. चुनौती यह है कि एक समान प्रकार के हमारे खर्च हमेशा सटीक पाठ का पालन नहीं करेंगे। उदाहरण के लिए: SEND E-TFR US***ke5और SEND E-TFR US***xja। जैसे, हम सिर्फ पाठ से मेल खाना चाहेंगे SEND E-TFR

इसका मतलब है कि VLOOKUP को 'श्रेणियाँ' टैब के पहले कॉलम में भाग से मिलान करना होगा। इसे प्राप्त करने के लिए VLOOKUP फॉर्मूला कैसे समायोजित करता है?

पुनश्च। मेरा सवाल इस से काफी मिलता-जुलता है, जो कि मैचिंग सबस्ट्रिंग के लिए महत्वपूर्ण अंतर है।
Google स्प्रेडशीट में कोशिकाओं को वर्गीकृत करें

संपादित करें: उत्तर दिया।

जवाब

2 Rubén Nov 25 2020 at 11:31

इस मामले में VLOOKUPउपयोग नहीं किया जा सकता है क्योंकि कुछ श्रेणियां kyes मिलान किए जाने वाले डेटा के भीतर समाहित हैं, दूसरी ओर, प्रश्न के शरीर में शामिल विवरण की तुलना में मामला अधिक जटिल है क्योंकि कुछ श्रेणियां हैं उनके (यानी BANK VISA PREAUTH PYMTऔर Bank Visa) के बीच एक आंशिक मैच और क्योंकि मैच असंवेदनशील BANK MUTUAL FUNDSहोना चाहिए (के रूप में वर्गीकृत किया जाना चाहिए Mutual Funds)

यहाँ प्रस्तावित सूत्र है

=ArrayFormula(ARRAY_CONSTRAIN(
SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE({
IF(
TRIM(B2:B9)=TRIM(TRANSPOSE(Categories!A1:A3)),Categories!B1:B3&"|",),
TRANSPOSE(IF(IFERROR(SEARCH(TRIM(Categories!A4:A8),TRANSPOSE(TRIM(B2:B9))),),Categories!B4:B8&"|",))
}),,1000000))),"|"),
COUNTA(B2:B9),1))
  • B2:B9 वे कोशिकाएं हैं जिनके मूल्य श्रेणियों की कुंजियों के साथ होने चाहिए।

    नोट: यह मान लिया गया है कि इस श्रेणी में रिक्त कक्ष नहीं होंगे।

  • Categories!A1:A3वे कोशिकाएँ हैं जिनमें कुंजियाँ होती हैं जिन्हें एक पूर्ण मिलान करना चाहिए और Categories!B1:B3इसी श्रेणी के होते हैं

  • Categories!A4:A8क्या कोशिकाएं उन कुंजियों को परिभाषित कर रही हैं जो एक आंशिक मिलान करना चाहिए और Categories!B4:B8संबंधित श्रेणियां हैं

2 PKHunter Nov 26 2020 at 01:35

उत्तर के लिए @ रुबेन को धन्यवाद। आखिरकार हमने एक सरल तरीका अपनाने का फैसला किया। इसके बजाय VLOOKUP में खोज स्ट्रिंग के regexp की जाँच करें, और श्रेणियाँ टैब में प्रमुख अनुक्रमित में उन सबस्ट्रिंग हैं।

तो अगर एक खर्च सेल B1 में यह पाठ है:

SEND E-TFR US***ke5
SEND E-TFR US***abc
SEND E-TFR US***xyz
...

जो सटीक मिलान के साथ समस्या पैदा कर रहा था, हम स्ट्रिंग को दूसरे स्थान पर आने वाले सबस्ट्रिंग में विभाजित करते हैं:

REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")

हमें यह देने के लिए:

SEND E-TFR

और हमारे नए VLOOKUP फ़ंक्शन:

=IFERROR(VLOOKUP(TRIM(REGEXEXTRACT(B1, "[^\ ]*\ [^\ ]*")),Categories!A:B,2,FALSE), "")

श्रेणियां कुंजी इंडेक्स तब लुकअप के लिए उस काटे गए पाठ को संदर्भित करता है:

इसलिए सही श्रेणी से मेल खाता है और पॉपुलेट करता है:

इस कार्य को देखने के लिए एक परीक्षण फ़ाइल है: https://docs.google.com/spreadsheets/d/13QrS1nDjTN6oxIoXhvdyoiqAa4lf8x3eSY3566_eTJ8/edit?usp=sharing

धन्यवाद!