మీరు "సూటిగా" యొక్క వివిధ స్థాయిలలో ప్రారంభ డేటా వ్రాయబడిన జాబితాను కలిగి ఉన్నారని అనుకుందాం - ఉదాహరణకు, చిరునామాలు లేదా కంపెనీ పేర్లు:
 |  |
అదే నగరం లేదా కంపెనీ ఇక్కడ మోట్లీ వేరియంట్‌లలో ఉన్నట్లు స్పష్టంగా కనిపిస్తుంది, ఇది భవిష్యత్తులో ఈ పట్టికలతో పనిచేసేటప్పుడు చాలా సమస్యలను సృష్టిస్తుంది. మరియు మీరు కొంచెం ఆలోచిస్తే, మీరు ఇతర ప్రాంతాల నుండి ఇలాంటి పనులకు చాలా ఉదాహరణలు కనుగొనవచ్చు.
ఇప్పుడు అలాంటి వంకర డేటా మీకు క్రమం తప్పకుండా వస్తుందని ఊహించండి, అంటే ఇది ఒక సారి "మాన్యువల్‌గా పరిష్కరించండి, మర్చిపోండి" కథ కాదు, కానీ క్రమ పద్ధతిలో మరియు పెద్ద సంఖ్యలో సెల్‌లలో సమస్య.
ఏం చేయాలి? "కనుగొను మరియు భర్తీ చేయి" పెట్టె ద్వారా లేదా క్లిక్ చేయడం ద్వారా 100500 సార్లు వంకరగా ఉన్న వచనాన్ని సరైన దానితో మాన్యువల్‌గా భర్తీ చేయవద్దు Ctrl+H?
అటువంటి పరిస్థితిలో గుర్తుకు వచ్చే మొదటి విషయం ఏమిటంటే, సరికాని మరియు సరైన ఎంపికలను సరిపోల్చడానికి ముందుగా సంకలనం చేయబడిన రిఫరెన్స్ బుక్ ప్రకారం మాస్ రీప్లేస్‌మెంట్ చేయడం – ఇలా:
దురదృష్టవశాత్తు, అటువంటి పని యొక్క స్పష్టమైన ప్రాబల్యంతో, మైక్రోసాఫ్ట్ ఎక్సెల్ దానిని పరిష్కరించడానికి సాధారణ అంతర్నిర్మిత పద్ధతులను కలిగి లేదు. ప్రారంభించడానికి, VBA లేదా పవర్ క్వెరీలో మాక్రోల రూపంలో “భారీ ఆర్టిలరీ” ప్రమేయం లేకుండా సూత్రాలతో దీన్ని ఎలా చేయాలో తెలుసుకుందాం.
కేస్ 1. బల్క్ ఫుల్ రీప్లేస్‌మెంట్
సాపేక్షంగా సరళమైన కేసుతో ప్రారంభిద్దాం - మీరు పాత వంకర వచనాన్ని కొత్త దానితో భర్తీ చేయాల్సిన పరిస్థితి. పూర్తిగా.
మనకు రెండు పట్టికలు ఉన్నాయని అనుకుందాం:
మొదటిది - కంపెనీల అసలు రంగురంగుల పేర్లు. రెండవది - కరస్పాండెన్స్ యొక్క సూచన పుస్తకం. మేము కంపెనీ పేరులో మొదటి పట్టికలో కాలమ్ నుండి ఏదైనా పదాన్ని కనుగొంటే కనుగొనేందుకు, అప్పుడు మీరు ఈ వంకర పేరును సరైన దానితో పూర్తిగా భర్తీ చేయాలి - నిలువు వరుస నుండి సబ్స్టిట్యూట్ రెండవ శోధన పట్టిక.
సౌలభ్యం కోసం:
- రెండు పట్టికలు కీబోర్డ్ సత్వరమార్గాన్ని ఉపయోగించి డైనమిక్ ("స్మార్ట్")కి మార్చబడతాయి Ctrl+T లేదా జట్టు ఇన్సర్ట్ - టేబుల్ (చొప్పించు - టేబుల్).
- కనిపించే ట్యాబ్‌లో నమూనా రచయిత (రూపకల్పన) పేరు పెట్టబడిన మొదటి పట్టిక సమాచారం, మరియు రెండవ సూచన పట్టిక - ప్రత్యామ్నాయాలను.
సూత్రం యొక్క తర్కాన్ని వివరించడానికి, దూరం నుండి కొంచెం వెళ్దాం.
సెల్ A2 నుండి మొదటి కంపెనీని ఉదాహరణగా తీసుకొని, మిగిలిన కంపెనీల గురించి తాత్కాలికంగా మరచిపోయి, కాలమ్ నుండి ఏ ఎంపికను నిర్ణయించడానికి ప్రయత్నిద్దాం కనుగొనేందుకు అక్కడ కలుస్తుంది. దీన్ని చేయడానికి, షీట్ యొక్క ఉచిత భాగంలో ఏదైనా ఖాళీ సెల్‌ను ఎంచుకుని, అక్కడ ఫంక్షన్‌ను నమోదు చేయండి కనుగొనేందుకు (కనుగొనండి):
ఇచ్చిన సబ్‌స్ట్రింగ్ చేర్చబడిందో లేదో ఈ ఫంక్షన్ నిర్ణయిస్తుంది (మొదటి ఆర్గ్యుమెంట్ కాలమ్ నుండి అన్ని విలువలు కనుగొనేందుకు) సోర్స్ టెక్స్ట్‌లోకి (డేటా టేబుల్ నుండి మొదటి కంపెనీ) మరియు టెక్స్ట్ కనుగొనబడిన అక్షరం యొక్క ఆర్డినల్ నంబర్‌ను లేదా సబ్‌స్ట్రింగ్ కనుగొనబడకపోతే ఎర్రర్‌ను అవుట్‌పుట్ చేయాలి.
ఇక్కడ ఉపాయం ఏమిటంటే, మేము మొదటి వాదనగా ఒకటి కాదు, అనేక విలువలను పేర్కొన్నందున, ఈ ఫంక్షన్ కూడా ఫలితంగా ఒక విలువ కాదు, 3 మూలకాల శ్రేణిని అందిస్తుంది. మీరు డైనమిక్ శ్రేణులకు మద్దతిచ్చే Office 365 యొక్క తాజా సంస్కరణను కలిగి లేకుంటే, ఈ ఫార్ములాను నమోదు చేసి, క్లిక్ చేసిన తర్వాత ఎంటర్ మీరు షీట్‌లో ఈ శ్రేణిని చూస్తారు:
మీరు Excel యొక్క మునుపటి సంస్కరణలను కలిగి ఉంటే, క్లిక్ చేసిన తర్వాత ఎంటర్ మేము ఫలిత శ్రేణి నుండి మొదటి విలువను మాత్రమే చూస్తాము, అనగా లోపం #VALUE! (#విలువ!).
మీరు భయపడాల్సిన అవసరం లేదు 🙂 నిజానికి, మా ఫార్ములా పని చేస్తుంది మరియు మీరు ఫార్ములా బార్‌లో నమోదు చేసిన ఫంక్షన్‌ని ఎంచుకుని, కీని నొక్కితే, మీరు ఇప్పటికీ ఫలితాల మొత్తం శ్రేణిని చూడవచ్చు F9(నొక్కడం మర్చిపోవద్దు Escఫార్ములాకు తిరిగి వెళ్ళడానికి):
ఫలితంగా ఏర్పడే ఫలితాల శ్రేణి అసలు వంకర కంపెనీ పేరులో ఉందని అర్థం (GK మొరోజ్కో OAO) నిలువు వరుసలోని అన్ని విలువలు కనుగొనేందుకు రెండవది మాత్రమే కనుగొనబడింది (మొరోజ్కో), మరియు వరుసగా 4వ అక్షరం నుండి ప్రారంభమవుతుంది.
ఇప్పుడు మన ఫార్ములాకు ఒక ఫంక్షన్‌ని యాడ్ చేద్దాం VIEW(పైకి చూడు):
ఈ ఫంక్షన్ మూడు వాదనలను కలిగి ఉంది:
- కావలసిన విలువ - మీరు ఏదైనా తగినంత పెద్ద సంఖ్యను ఉపయోగించవచ్చు (ప్రధాన విషయం ఏమిటంటే ఇది మూల డేటాలోని ఏదైనా టెక్స్ట్ యొక్క పొడవును మించిపోయింది)
- వీక్షించబడిన_వెక్టర్ – మేము కోరుకున్న విలువ కోసం వెతుకుతున్న పరిధి లేదా శ్రేణి. ఇంతకుముందు ప్రవేశపెట్టిన ఫంక్షన్ ఇక్కడ ఉంది కనుగొనేందుకు, ఇది శ్రేణిని అందిస్తుంది {#VALUE!:4:#VALUE!}
- వెక్టర్_ఫలితాలు – కావలసిన విలువ సంబంధిత సెల్‌లో కనుగొనబడితే మనం విలువను తిరిగి ఇవ్వాలనుకుంటున్న పరిధి. నిలువు వరుస నుండి సరైన పేర్లు ఇక్కడ ఉన్నాయి సబ్స్టిట్యూట్ మా సూచన పట్టిక.
ఇక్కడ ప్రధాన మరియు స్పష్టమైన లక్షణం ఏమిటంటే ఫంక్షన్ VIEW ఖచ్చితమైన సరిపోలిక లేనట్లయితే, ఎల్లప్పుడూ సమీప చిన్న (మునుపటి) విలువ కోసం చూస్తుంది. అందువల్ల, ఏదైనా భారీ సంఖ్యను (ఉదాహరణకు, 9999) కావలసిన విలువగా పేర్కొనడం ద్వారా, మేము బలవంతం చేస్తాము VIEW {#VALUE!:4:#VALUE!} శ్రేణిలో సమీప చిన్న సంఖ్య (4)తో సెల్‌ను కనుగొని, ఫలిత వెక్టార్ నుండి సంబంధిత విలువను తిరిగి ఇవ్వండి, అంటే నిలువు వరుస నుండి సరైన కంపెనీ పేరు సబ్స్టిట్యూట్.
రెండవ స్వల్పభేదం ఏమిటంటే, సాంకేతికంగా, మా ఫార్ములా శ్రేణి సూత్రం, ఎందుకంటే ఫంక్షన్ కనుగొనేందుకు ఫలితాలు ఒకటి కాదు, మూడు విలువల శ్రేణిని అందిస్తుంది. కానీ ఫంక్షన్ నుండి VIEW పెట్టె వెలుపల శ్రేణులకు మద్దతు ఇస్తుంది, అప్పుడు మేము ఈ ఫార్ములాను క్లాసిక్ అర్రే ఫార్ములాగా నమోదు చేయనవసరం లేదు – కీబోర్డ్ సత్వరమార్గాన్ని ఉపయోగించి Ctrl+మార్పు+ఎంటర్. ఒక సాధారణ సరిపోతుంది ఎంటర్.
అంతే. మీకు లాజిక్ లభిస్తుందని ఆశిస్తున్నాను.
పూర్తయిన సూత్రాన్ని కాలమ్ యొక్క మొదటి సెల్ B2కి బదిలీ చేయడానికి ఇది మిగిలి ఉంది స్థిర - మరియు మా పని పరిష్కరించబడింది!
వాస్తవానికి, సాధారణ (స్మార్ట్ కాదు) పట్టికలతో, ఈ ఫార్ములా కూడా అద్భుతంగా పనిచేస్తుంది (కీ గురించి మర్చిపోవద్దు F4 మరియు సంబంధిత లింక్‌లను పరిష్కరించడం):
కేస్ 2. బల్క్ పాక్షిక భర్తీ
ఈ కేసు కొంచెం గమ్మత్తైనది. మళ్ళీ మనకు రెండు "స్మార్ట్" పట్టికలు ఉన్నాయి:
సరిదిద్దవలసిన వంకరగా వ్రాసిన చిరునామాలతో మొదటి పట్టిక (నేను దానిని పిలిచాను డేటా 2) రెండవ పట్టిక ఒక సూచన పుస్తకం, దీని ప్రకారం మీరు చిరునామా లోపల సబ్‌స్ట్రింగ్‌ను పాక్షికంగా భర్తీ చేయాలి (నేను ఈ పట్టిక అని పిలుస్తాను. ప్రత్యామ్నాయాలు2).
ఇక్కడ ఉన్న ప్రాథమిక వ్యత్యాసం ఏమిటంటే, మీరు అసలు డేటాలోని ఒక భాగాన్ని మాత్రమే భర్తీ చేయాలి - ఉదాహరణకు, మొదటి చిరునామా తప్పుగా ఉంది “సెయింట్. పీటర్స్‌బర్గ్" కుడి వైపు “సెయింట్. పీటర్స్‌బర్గ్", మిగిలిన చిరునామాను (జిప్ కోడ్, వీధి, ఇల్లు) అలాగే వదిలివేయండి.
పూర్తయిన ఫార్ములా ఇలా కనిపిస్తుంది (గ్రహణ సౌలభ్యం కోసం, నేను దానిని ఎన్ని పంక్తులు ఉపయోగిస్తున్నానో విభజించాను alt+ఎంటర్):
ఇక్కడ ప్రధాన పని ప్రామాణిక Excel టెక్స్ట్ ఫంక్షన్ ద్వారా చేయబడుతుంది సబ్స్టిట్యూట్ (సబ్స్టిట్యూట్), ఇందులో 3 వాదనలు ఉన్నాయి:
- మూల వచనం – చిరునామా కాలమ్ నుండి మొదటి వంకర చిరునామా
- మనం వెతుకుతున్నది - ఇక్కడ మేము ఫంక్షన్‌తో ట్రిక్‌ని ఉపయోగిస్తాము VIEW (పైకి చూడు)నిలువు వరుస నుండి విలువను లాగడానికి మునుపటి మార్గం నుండి కనుగొనేందుకు, ఇది వక్ర చిరునామాలో ఒక భాగం వలె చేర్చబడింది.
- దేనితో భర్తీ చేయాలి - అదే విధంగా కాలమ్ నుండి దానికి సంబంధించిన సరైన విలువను మేము కనుగొంటాము సబ్స్టిట్యూట్.
దీనితో ఈ సూత్రాన్ని నమోదు చేయండి Ctrl+మార్పు+ఎంటర్ ఇక్కడ కూడా అవసరం లేదు, అయితే ఇది నిజానికి శ్రేణి ఫార్ములా.
మరియు ఇది స్పష్టంగా కనిపిస్తుంది (మునుపటి చిత్రంలో #N/A ఎర్రర్‌లను చూడండి) అటువంటి సూత్రం, దాని చక్కదనం కోసం, రెండు లోపాలను కలిగి ఉంది:
- ఫంక్షన్ SUBSTITUTE అనేది కేస్ సెన్సిటివ్, కాబట్టి చివరి వరుసలోని “Spb” భర్తీ పట్టికలో కనుగొనబడలేదు. ఈ సమస్యను పరిష్కరించడానికి, మీరు ఫంక్షన్‌ను ఉపయోగించవచ్చు ZAMENIT (భర్తీ చేయి), లేదా ప్రాథమికంగా రెండు పట్టికలను ఒకే రిజిస్టర్‌కి తీసుకురావాలి.
- టెక్స్ట్ ప్రారంభంలో సరైనది లేదా దానిలో ఉంటే భర్తీ చేయడానికి శకలం లేదు (చివరి పంక్తి), అప్పుడు మా ఫార్ములా లోపాన్ని విసురుతుంది. ఫంక్షన్‌ని ఉపయోగించి లోపాలను అడ్డగించడం మరియు భర్తీ చేయడం ద్వారా ఈ క్షణం తటస్థీకరించబడుతుంది IFERROR (ముందు):
- అసలు వచనం కలిగి ఉంటే ఒకేసారి డైరెక్టరీ నుండి అనేక శకలాలు, అప్పుడు మా ఫార్ములా చివరిదాన్ని మాత్రమే భర్తీ చేస్తుంది (8వ పంక్తిలో, లిగోవ్స్కీ «అవెన్యూ« కు మార్చబడింది "pr-t", కానీ "S-Pb" on “సెయింట్. పీటర్స్‌బర్గ్" ఇకపై, ఎందుకంటే “S-Pb” డైరెక్టరీలో ఎక్కువగా ఉంది). ఈ సమస్య మా స్వంత సూత్రాన్ని మళ్లీ అమలు చేయడం ద్వారా పరిష్కరించబడుతుంది, కానీ ఇప్పటికే కాలమ్‌తో పాటు స్థిర:
ప్రదేశాలలో ఖచ్చితమైన మరియు గజిబిజిగా లేదు, కానీ అదే మాన్యువల్ రీప్లేస్‌మెంట్ కంటే మెరుగ్గా ఉంది, సరియైనదా? 🙂
PS
తదుపరి కథనంలో, మాక్రోలు మరియు పవర్ క్వెరీని ఉపయోగించి అటువంటి బల్క్ ప్రత్యామ్నాయాన్ని ఎలా అమలు చేయాలో మేము కనుగొంటాము.
- వచనాన్ని భర్తీ చేయడానికి SUBSTITUTE ఫంక్షన్ ఎలా పని చేస్తుంది
- ఖచ్చితమైన ఫంక్షన్‌ని ఉపయోగించి ఖచ్చితమైన టెక్స్ట్ సరిపోలికలను కనుగొనడం
- కేస్ సెన్సిటివ్ శోధన మరియు ప్రత్యామ్నాయం (కేస్ సెన్సిటివ్ VLOOKUP)