విషయ సూచిక
మీరు అనేక సెల్‌ల నుండి టెక్స్ట్‌ను ఒకదానిలో త్వరగా ఎలా అతికించవచ్చో మరియు దానికి విరుద్ధంగా, పొడవైన టెక్స్ట్ స్ట్రింగ్‌ను భాగాలుగా ఎలా అన్వయించవచ్చో నేను ఇప్పటికే వ్రాసాను. ఇప్పుడు ఒక దగ్గరి, కానీ కొంచెం సంక్లిష్టమైన పనిని చూద్దాం - నిర్దిష్ట నిర్దిష్ట షరతు నెరవేరినప్పుడు అనేక కణాల నుండి వచనాన్ని ఎలా జిగురు చేయాలి.
మాకు కస్టమర్ల డేటాబేస్ ఉందని చెప్పండి, ఇక్కడ ఒక కంపెనీ పేరు దాని ఉద్యోగుల యొక్క అనేక విభిన్న ఇమెయిల్‌లకు అనుగుణంగా ఉంటుంది. కస్టమర్ల కోసం మెయిలింగ్ జాబితాను రూపొందించడం కోసం కంపెనీ పేర్లతో అన్ని చిరునామాలను సేకరించి, వాటిని (కామాలు లేదా సెమికోలన్‌లతో వేరు చేయడం) కలపడం మా పని, అంటే ఇలాంటి అవుట్‌పుట్‌ను పొందడం:
మరో మాటలో చెప్పాలంటే, షరతు ప్రకారం వచనాన్ని జిగురు (లింక్) చేసే సాధనం మనకు అవసరం - ఫంక్షన్ యొక్క అనలాగ్ SUMMESLI (SUMIF), కానీ టెక్స్ట్ కోసం.
పద్ధతి 0. ఫార్ములా
చాలా సొగసైనది కాదు, కానీ సులభమైన మార్గం. మీరు ఒక సాధారణ సూత్రాన్ని వ్రాయవచ్చు, అది తదుపరి వరుసలోని కంపెనీ మునుపటి నుండి భిన్నంగా ఉందో లేదో తనిఖీ చేస్తుంది. ఇది భిన్నంగా లేకుంటే, కామాతో వేరు చేయబడిన తదుపరి చిరునామాను అతికించండి. ఇది భిన్నంగా ఉంటే, మేము సేకరించిన వాటిని “రీసెట్” చేస్తాము, మళ్లీ ప్రారంభిస్తాము:
ఈ విధానం యొక్క ప్రతికూలతలు స్పష్టంగా ఉన్నాయి: పొందిన అదనపు కాలమ్ యొక్క అన్ని కణాల నుండి, మనకు ప్రతి కంపెనీకి (పసుపు) చివరిది మాత్రమే అవసరం. జాబితా పెద్దగా ఉంటే, వాటిని త్వరగా ఎంచుకోవడానికి, మీరు ఫంక్షన్‌ని ఉపయోగించి మరొక నిలువు వరుసను జోడించాలి DLSTR (LEN), సేకరించిన తీగల పొడవును తనిఖీ చేస్తోంది:
ఇప్పుడు మీరు వాటిని ఫిల్టర్ చేయవచ్చు మరియు తదుపరి ఉపయోగం కోసం అవసరమైన చిరునామాను కాపీ చేయవచ్చు.
పద్ధతి 1. ఒక షరతు ద్వారా gluing యొక్క మాక్రోఫంక్షన్
అసలు జాబితా కంపెనీ ద్వారా క్రమబద్ధీకరించబడకపోతే, పైన పేర్కొన్న సాధారణ ఫార్ములా పని చేయదు, కానీ మీరు VBAలో చిన్న కస్టమ్ ఫంక్షన్‌తో సులభంగా పొందవచ్చు. కీబోర్డ్ సత్వరమార్గాన్ని నొక్కడం ద్వారా విజువల్ బేసిక్ ఎడిటర్‌ను తెరవండి Alt + F11 లేదా బటన్‌ని ఉపయోగించడం విజువల్ బేసిక్ టాబ్ డెవలపర్ (డెవలపర్). తెరుచుకునే విండోలో, మెను ద్వారా కొత్త ఖాళీ మాడ్యూల్‌ను చొప్పించండి చొప్పించు - మాడ్యూల్ మరియు మా ఫంక్షన్ యొక్క వచనాన్ని అక్కడ కాపీ చేయండి:
Function MergeIf(TextRange as Range, SearchRange as Range, Condition as Range) Dim Delimeter as Long Delimeter = ", " gluings ఒకదానికొకటి సమానంగా ఉండవు - SearchRange.Count <> TextRange.Count అయితే మేము లోపంతో నిష్క్రమిస్తాము. అప్పుడు MergeIf = CVErr(xlErrRef) ఎగ్జిట్ ఫంక్షన్ ఎండ్ 'అన్ని సెల్‌ల గుండా వెళితే, పరిస్థితిని తనిఖీ చేయండి మరియు శోధన రేంజ్ కోసం i = 1 కోసం OutText వేరియబుల్‌లో వచనాన్ని సేకరించండి. Cells.Count if SearchRange.Cells(i) కండిషన్ ఇలా ఉంటే OutText = OutText & TextRange.Cells(i) & Delimeter తదుపరి i 'చివరి డీలిమిటర్ లేకుండా ఫలితాలను ప్రదర్శించు MergeIf = ఎడమ(ఔట్‌టెక్స్ట్, లెన్(అవుట్‌టెక్స్ట్) - లెన్(డిలిమీటర్)) ఫంక్షన్
మీరు ఇప్పుడు మైక్రోసాఫ్ట్ ఎక్సెల్‌కి తిరిగి వస్తే, ఫంక్షన్ల జాబితాలో (బటన్ fx ఫార్ములా బార్ లేదా ట్యాబ్లో సూత్రాలు - ఇన్సర్ట్ ఫంక్షన్) మా ఫంక్షన్‌ను కనుగొనడం సాధ్యమవుతుంది విలీనం చేస్తే వర్గంలో వినియోగాదారునిచే నిర్వచించబడినది (వినియోగాదారునిచే నిర్వచించబడినది). ఫంక్షన్ యొక్క వాదనలు క్రింది విధంగా ఉన్నాయి:
విధానం 2. సరికాని స్థితి ద్వారా వచనాన్ని సంగ్రహించండి
మన స్థూల 13వ లైన్‌లోని మొదటి అక్షరాన్ని భర్తీ చేస్తే = సుమారు మ్యాచ్ ఆపరేటర్‌కు వంటి, అప్పుడు ఎంపిక ప్రమాణంతో ప్రారంభ డేటా యొక్క ఖచ్చితమైన సరిపోలిక ద్వారా గ్లూయింగ్ చేయడం సాధ్యమవుతుంది. ఉదాహరణకు, కంపెనీ పేరును వేర్వేరు వేరియంట్‌లలో వ్రాయగలిగితే, మేము వాటన్నింటినీ ఒకే ఫంక్షన్‌తో తనిఖీ చేయవచ్చు మరియు సేకరించవచ్చు:
ప్రామాణిక వైల్డ్‌కార్డ్‌లకు మద్దతు ఉంది:
- ఆస్టరిస్క్ (*) - ఎన్ని అక్షరాలు ఉన్నాయో (వాటి లేకపోవడంతో సహా) సూచిస్తుంది
- ప్రశ్న గుర్తు (?) - ఏదైనా ఒక అక్షరాన్ని సూచిస్తుంది
- పౌండ్ గుర్తు (#) - ఏదైనా ఒక అంకె (0-9)
డిఫాల్ట్‌గా, లైక్ ఆపరేటర్ కేస్ సెన్సిటివ్, అంటే "ఓరియన్" మరియు "ఓరియన్"లను వేర్వేరు కంపెనీలుగా అర్థం చేసుకుంటుంది. కేసును విస్మరించడానికి, మీరు విజువల్ బేసిక్ ఎడిటర్‌లో మాడ్యూల్ ప్రారంభంలోనే పంక్తిని జోడించవచ్చు ఎంపిక వచనాన్ని సరిపోల్చండి, ఇది లైక్‌ని కేస్ ఇన్‌సెన్సిటివ్‌గా మారుస్తుంది.
ఈ విధంగా, మీరు పరిస్థితులను తనిఖీ చేయడానికి చాలా క్లిష్టమైన ముసుగులను కంపోజ్ చేయవచ్చు, ఉదాహరణకు:
- ?1##??777RUS – 777 ప్రాంతంలోని అన్ని లైసెన్స్ ప్లేట్‌ల ఎంపిక, 1తో ప్రారంభమవుతుంది
- LLC* - LLCతో ప్రారంభమయ్యే అన్ని కంపెనీలు
- ##7## - ఐదు అంకెల డిజిటల్ కోడ్‌తో అన్ని ఉత్పత్తులు, ఇక్కడ మూడవ అంకె 7
- ????? - ఐదు అక్షరాల యొక్క అన్ని పేర్లు మొదలైనవి.
విధానం 3. రెండు షరతులలో వచనాన్ని అతుక్కోవడానికి మాక్రో ఫంక్షన్
మీరు వచనాన్ని ఒకటి కంటే ఎక్కువ షరతులను లింక్ చేయవలసి వచ్చినప్పుడు పనిలో సమస్య ఉండవచ్చు. ఉదాహరణకు, మా మునుపటి పట్టికలో, నగరంతో మరో కాలమ్ జోడించబడిందని ఊహించుదాం మరియు ఇచ్చిన కంపెనీకి మాత్రమే కాకుండా, ఇచ్చిన నగరానికి కూడా గ్లూయింగ్ చేయాలి. ఈ సందర్భంలో, దానికి మరొక శ్రేణి తనిఖీని జోడించడం ద్వారా మా ఫంక్షన్ కొద్దిగా ఆధునికీకరించబడాలి:
Function MergeIfs(TextRange as Range, SearchRange1 as Range, Condition1 as String, SearchRange2 as Range, Condition2 as String) డిమ్ డీలిమీటర్ స్ట్రింగ్‌గా, i As Long Delimeter = "," 'delimiter అక్షరాలు (స్పేస్‌తో భర్తీ చేయవచ్చు లేదా ; etc.) ఇ.) 'ధృవీకరణ మరియు అంటుకునే పరిధులు ఒకదానికొకటి సమానంగా లేకుంటే, SearchRange1.Count <> TextRange.Count లేదా SearchRange2.Count <> TextRange.Count అప్పుడు MergeIfs = CVErr(xlErrRef) నిష్క్రమిస్తే ఫంక్షన్ ముగింపు 'అన్ని సెల్‌ల ద్వారా వెళ్లి, అన్ని షరతులను తనిఖీ చేయండి మరియు టెక్స్ట్‌ని వేరియబుల్ అవుట్‌టెక్స్ట్‌లో సేకరించండి i = 1 కోసం SearchRange1.Cells. సెర్చ్‌రేంజ్ 1.సెల్స్(i) = షరతు1 మరియు SearchRange2.Cells(i) = Condition2 తర్వాత అవుట్‌టెక్స్ట్ = అవుట్‌టెక్స్ట్ & TextRange.Cells(i) & Delimeter End తర్వాత ఐ 'చివరి డీలిమిటర్ లేకుండా ఫలితాలను ప్రదర్శిస్తే MergeIfs = ఎడమ(ఔట్‌టెక్స్ట్, లెన్(ఔట్‌టెక్స్ట్) - లెన్(డిలిమీటర్)) ఎండ్ ఫంక్షన్
ఇది సరిగ్గా అదే విధంగా వర్తించబడుతుంది - ఇప్పుడు ఆర్గ్యుమెంట్‌లను మాత్రమే మరింత పేర్కొనాలి:
విధానం 4. పవర్ క్వెరీలో గ్రూపింగ్ మరియు గ్లూయింగ్
మీరు ఉచిత పవర్ క్వెరీ యాడ్-ఇన్‌ని ఉపయోగిస్తే, మీరు VBAలో ప్రోగ్రామింగ్ లేకుండానే సమస్యను పరిష్కరించవచ్చు. Excel 2010-2013 కోసం దీన్ని ఇక్కడ డౌన్‌లోడ్ చేసుకోవచ్చు మరియు Excel 2016లో ఇది ఇప్పటికే డిఫాల్ట్‌గా నిర్మించబడింది. చర్యల క్రమం క్రింది విధంగా ఉంటుంది:
పవర్ క్వెరీకి సాధారణ పట్టికలతో ఎలా పని చేయాలో తెలియదు, కాబట్టి మొదటి దశ మన పట్టికను "స్మార్ట్"గా మార్చడం. దీన్ని చేయడానికి, దాన్ని ఎంచుకుని, కలయికను నొక్కండి Ctrl+T లేదా ట్యాబ్ నుండి ఎంచుకోండి హోమ్ - టేబుల్‌గా ఫార్మాట్ చేయండి (హోమ్ — టేబుల్ లాగా ఫార్మాట్ చేయండి). అప్పుడు కనిపించే ట్యాబ్లో నమూనా రచయిత (రూపకల్పన) మీరు పట్టిక పేరును సెట్ చేయవచ్చు (నేను ప్రమాణాన్ని విడిచిపెట్టాను పట్టిక 11):
ఇప్పుడు మన టేబుల్‌ని పవర్ క్వెరీ యాడ్-ఇన్‌లోకి లోడ్ చేద్దాం. దీన్ని చేయడానికి, ట్యాబ్లో సమాచారం (మీకు ఎక్సెల్ 2016 ఉంటే) లేదా పవర్ క్వెరీ ట్యాబ్‌పై (మీకు ఎక్సెల్ 2010-2013 ఉంటే) క్లిక్ చేయండి టేబుల్ నుండి (డేటా - టేబుల్ నుండి):
తెరుచుకునే ప్రశ్న ఎడిటర్ విండోలో, హెడర్‌పై క్లిక్ చేయడం ద్వారా నిలువు వరుసను ఎంచుకోండి కంపెనీ మరియు పైన ఉన్న బటన్‌ను నొక్కండి గ్రూప్ (సమూహం ద్వారా). సమూహంలో కొత్త నిలువు వరుస పేరు మరియు ఆపరేషన్ రకాన్ని నమోదు చేయండి - అన్ని పంక్తులు (అన్ని వరుసలు):
సరే క్లిక్ చేయండి మరియు మేము ప్రతి కంపెనీకి సమూహ విలువల యొక్క చిన్న-పట్టికను పొందుతాము. ఫలిత కాలమ్‌లోని సెల్‌ల తెల్లని నేపథ్యంపై (టెక్స్ట్‌పై కాదు!) ఎడమ-క్లిక్ చేస్తే పట్టికలోని విషయాలు స్పష్టంగా కనిపిస్తాయి:
ఇప్పుడు మరొక నిలువు వరుసను జోడిద్దాం, ఇక్కడ, ఫంక్షన్‌ని ఉపయోగించి, కామాలతో వేరు చేయబడిన ప్రతి మినీ-టేబుల్‌లోని చిరునామా నిలువు వరుసల కంటెంట్‌లను జిగురు చేస్తాము. దీన్ని చేయడానికి, ట్యాబ్లో కాలమ్ జోడించండి మేము నొక్కండి అనుకూల కాలమ్ (నిలువు వరుసను జోడించండి - అనుకూల కాలమ్) మరియు కనిపించే విండోలో, పవర్ క్వెరీలో నిర్మించిన M భాషలో కొత్త నిలువు వరుస పేరు మరియు కలపడం సూత్రాన్ని నమోదు చేయండి:
అన్ని M-ఫంక్షన్‌లు కేస్ సెన్సిటివ్ (Excel కాకుండా) అని గమనించండి. క్లిక్ చేసిన తర్వాత OK మేము అతుక్కొని ఉన్న చిరునామాలతో కొత్త నిలువు వరుసను పొందుతాము:
ఇది ఇప్పటికే అనవసరమైన కాలమ్‌ను తీసివేయడానికి మిగిలి ఉంది పట్టిక చిరునామాలు (శీర్షికపై కుడి క్లిక్ చేయండి) నిలువు వరుసను తొలగించండి) మరియు ట్యాబ్‌పై క్లిక్ చేయడం ద్వారా ఫలితాలను షీట్‌కు అప్‌లోడ్ చేయండి హోమ్ — మూసివేయండి మరియు డౌన్‌లోడ్ చేయండి (ఇల్లు — మూసివేసి లోడ్ చేయండి):
ముఖ్యమైన స్వల్పభేదం: మునుపటి పద్ధతుల (ఫంక్షన్‌లు) వలె కాకుండా, పవర్ క్వెరీ నుండి పట్టికలు స్వయంచాలకంగా నవీకరించబడవు. భవిష్యత్తులో సోర్స్ డేటాలో ఏవైనా మార్పులు ఉంటే, మీరు ఫలితాల పట్టికలో ఎక్కడైనా కుడి-క్లిక్ చేసి, ఆదేశాన్ని ఎంచుకోవాలి. నవీకరించండి & సేవ్ చేయండి (రిఫ్రెష్).
- పొడవైన టెక్స్ట్ స్ట్రింగ్‌ను భాగాలుగా ఎలా విభజించాలి
- వివిధ సెల్‌ల నుండి వచనాన్ని ఒకదానిలో ఒకటిగా అతికించడానికి అనేక మార్గాలు
- మాస్క్‌కి వ్యతిరేకంగా వచనాన్ని పరీక్షించడానికి లైక్ ఆపరేటర్‌ని ఉపయోగించడం