Athrú mórchóir téacs le foirmlí

Cuir i gcás go bhfuil liosta agat ina scríobhtar na sonraí tosaigh, ag céimeanna éagsúla “simplí” – mar shampla, seoltaí nó ainmneacha cuideachta:

Feictear go soiléir go bhfuil an chathair nó an chuideachta chéanna i láthair anseo i leaganacha motley, a chruthóidh go leor fadhbanna, ar ndóigh, agus na táblaí seo ag obair amach anseo. Agus má cheapann tú beagán, is féidir leat teacht ar a lán samplaí de thascanna comhchosúla ó réimsí eile.

Anois samhlaigh go dtagann sonraí cam den sórt sin chugat go rialta, .i. ní scéal aonuaire é seo “Déan é a dheisiú de láimh, déan dearmad air”, ach fadhb ar bhonn rialta agus i líon mór cealla.

Cad atá le déanamh? Ná cuir an téacs ceart in áit an téacs cam 100500 uair de láimh tríd an mbosca "Faigh agus Ionadaigh" nó trí chliceáil Ctrl+H?

Is é an chéad rud a thagann chun cuimhne ina leithéid de chás ná athsholáthar mais a dhéanamh de réir leabhar tagartha réamh-thiomsaithe de roghanna míchearta agus cearta a mheaitseáil - mar seo:

Ar an drochuair, le leitheadúlacht soiléir a leithéid de thasc, níl modhanna simplí ionsuite ag Microsoft Excel chun é a réiteach. Ar dtús, déanaimis amach conas é seo a dhéanamh le foirmlí, gan “airtléire trom” i bhfoirm macraí i VBA nó Power Query i gceist.

Cás 1. Athsholáthar iomlán mórchóir

Cuirimis tús le cás sách simplí – cás ina gcaithfidh tú téacs nua a chur in ionad an tseantéacs cam. go hiomlán.

Ligean le rá go bhfuil dhá tábla againn:

Sa chéad - na hainmneacha variegated bunaidh na gcuideachtaí. Sa dara - leabhar tagartha comhfhreagrais. Má fhaighimid in ainm na cuideachta sa chéad tábla aon fhocal ón gcolún A fháil, ansin caithfidh tú an t-ainm ceart a chur in ionad an ainm cam seo go hiomlán – ón gcolún Comhalta ionadaíoch dara tábla cuardaigh.

Ar mhaithe le caoithiúlacht:

• Tiontaítear an dá tábla go dinimiciúil (“cliste”) ag baint úsáide as aicearra méarchláir Ctrl+T nó foireann Ionsáigh – Tábla (Ionsáigh – Tábla).
  
• Ar an táb atá le feiceáil Cruthaitheoir (Dearadh) an chéad tábla ainmnithe Dáta, agus an dara tábla tagartha – Substitutions.

Chun loighic na foirmle a mhíniú, a ligean ar dul beagán ó i bhfad.

Ag tabhairt an chéad chuideachta ó chill A2 mar shampla agus ag déanamh dearmad go sealadach faoin gcuid eile de na cuideachtaí, déanaimis iarracht a chinneadh cén rogha ón gcolún A fháil le chéile ann. Chun seo a dhéanamh, roghnaigh aon chill folamh sa chuid saor in aisce den bhileog agus cuir isteach an fheidhm ann A FHÁIL (Aimsigh):

Cinneann an fheidhm seo an bhfuil an tsubstring tugtha san áireamh (is é an chéad argóint gach luach ón gcolún A fháil) isteach sa bhuntéacs (an chéad chuideachta ón tábla sonraí) agus ba cheart di orduimhir an charachtair óna bhfuarthas an téacs a aschur, nó earráid mura bhfuarthas an fhotheaghrán.

Is é an cleas anseo ná ós rud é nár shonraigh muid luachanna amháin, ach roinnt luachanna mar an chéad argóint, go dtiocfaidh an fheidhm seo ar ais freisin mar thoradh air sin ní luach amháin, ach sraith 3 eilimint. Mura bhfuil an leagan is déanaí de Office 365 agat a thacaíonn le eagair dhinimiciúla, tar éis duit an fhoirmle seo a chur isteach agus cliceáil ar Iontráil feicfidh tú an t-eagar seo ar dheis ar an mbileog:

Má tá leaganacha roimhe seo de Excel agat, ansin tar éis cliceáil ar Iontráil ní fheicfimid ach an chéad luach ón eagar torthaí, ie earráid #VALUE! (#LUACH!).

Níor cheart go mbeadh eagla ort 🙂 Go deimhin, oibríonn ár bhfoirmle agus is féidir leat an raon iomlán torthaí a fheiceáil fós má roghnaíonn tú an fheidhm iontráilte sa bharra foirmle agus brúigh an eochair F9(Ná déan dearmad brúigh Escdul ar ais go dtí an fhoirmle):

Ciallaíonn an raon torthaí a bhíonn mar thoradh air sin gur san ainm cuideachta cam bunaidh (GK Morozko OAO) de gach luach i gcolún A fháil fuair ach an dara (Morozko), agus ag tosú ón 4ú carachtar as a chéile.

Anois cuirimis feidhm lenár bhfoirmle FÉACH(FÉACH SUAS):

Tá trí argóint ag an bhfeidhm seo:

1. Luach inmhianaithe – is féidir leat aon uimhir atá sách mór a úsáid (is é an rud is mó ná go dtéann sé thar fhad aon téacs sna sonraí foinseacha)
   
2. Féachadh_veicteoir – an raon nó an t-eagar ina bhfuil an luach inmhianaithe á lorg againn. Seo é an fheidhm a tugadh isteach roimhe seo A FHÁIL, a sheolann eagar ar ais {#VALUE!:4:#VALUE!}
   
3. veicteoir_torthaí – an raon óna dteastaíonn uainn an luach a thabhairt ar ais má tá an luach inmhianaithe le fáil sa chill chomhfhreagrach. Seo iad na hainmneacha cearta ón gcolún Comhalta ionadaíoch ár tábla tagartha.
   

Is é an ghné is mó agus neamh-soiléir anseo go bhfuil an fheidhm FÉACH mura bhfuil meaitseáil chruinn ann, féachtar i gcónaí don luach is lú (roimhe seo).. Dá bhrí sin, trí aon uimhir hefty a shonrú (mar shampla, 9999) mar an luach inmhianaithe, cuirfimid iallach FÉACH faigh an chill leis an uimhir is lú is gaire (4) san eagar {#VALUE!:4:#VALUE!} agus seol an luach comhfhreagrach ar ais ón veicteoir toraidh, .i. ainm na cuideachta ceart ón gcolún Comhalta ionadaíoch.

Is é an dara nuance go bhfuil, go teicniúil, ár bhfoirmle foirmle eagar, mar gheall ar fheidhm A FHÁIL tuairisceáin mar thorthaí ní amháin, ach sraith de thrí luach. Ach ós rud é an fheidhm FÉACH tacaítear le eagair as an mbosca, mar sin ní gá dúinn an fhoirmle seo a chur isteach mar fhoirmle clasaiceach eagar - ag baint úsáide as aicearra méarchláir Ctrl+Shift+Iontráil. Is leor ceann simplí Iontráil.

Sin é an méid. Tá súil agam go bhfaighidh tú an loighic.

Tá sé fós chun an fhoirmle chríochnaithe a aistriú go dtí an chéad chill B2 den cholún seasta – agus tá ár dtasc réitithe!

Ar ndóigh, le táblaí gnáth (ní cliste), oibríonn an fhoirmle seo go hiontach freisin (ní hamháin ná déan dearmad faoin eochair F4 agus na naisc ábhartha a shocrú):

Cás 2. Athsholáthar páirteach mórchóir

Tá an cás seo beagán níos casta. Arís tá dhá tábla “cliste” againn:

An chéad tábla le seoltaí crookedly scríofa nach mór a cheartú (iarr mé é Sonraí2). Is leabhar tagartha é an dara tábla, dá réir is gá duit fotheaghrán a athsholáthar go páirteach taobh istigh den seoladh (d'iarr mé an tábla seo Ionadaithe2).

Is é an difríocht bhunúsach anseo nach gá duit ach blúire de na sonraí bunaidh a athsholáthar - mar shampla, tá seoladh mícheart ag an gcéad seoladh “St. St Petersburg" ar dheis “St. St Petersburg", ag fágáil an chuid eile den seoladh (cód zip, sráid, teach) mar atá.

Breathnóidh an fhoirmle críochnaithe mar seo (ar mhaithe le héascaíocht aireachtála, roinn mé é i cé mhéad línte a úsáideann Eile+Iontráil):

Is é feidhm chaighdeánach téacs Excel a dhéanann an phríomhobair anseo SUBSTAITIÚIL (SUBSTITUTE), a bhfuil 3 argóint ann:

1. Téacs foinse – an chéad seoladh cam ón gcolún Seoladh
2. Cad atá á lorg againn - anseo úsáidimid an cleas leis an bhfeidhm FÉACH (FÉACH SUAS)ón mbealach roimhe seo chun an luach a tharraingt ón gcolún A fháil, atá san áireamh mar bhlúire i seoladh cuartha.
3. Cad atá le cur ina ionad – ar an mbealach céanna aimsímid an luach ceart a fhreagraíonn dó ón gcolún Comhalta ionadaíoch.

Cuir isteach an fhoirmle seo le Ctrl+Shift+Iontráil níl sé ag teastáil anseo ach an oiread, cé gur foirmle eagair atá ann, i ndáiríre.

Agus tá sé le feiceáil go soiléir (féach earráidí #N/A sa phictiúr roimhe seo) go bhfuil cúpla míbhuntáistí ag baint le foirmle den sórt sin, i gcás a galántachta go léir:

• Feidhm Tá SUBSTITUTE cásíogair, mar sin níor aimsíodh “Spb” sa líne leathdhéanach sa tábla athsholáthair. Chun an fhadhb seo a réiteach, is féidir leat an fheidhm a úsáid ZAMENIT (ÁIT), nó an dá tábla a thabhairt ar an gclár céanna roimh ré.
• Má tá an téacs ceart ar dtús nó ann níl aon blúire le hathsholáthar (líne dheireanach), ansin caitheann ár bhfoirmle earráid. Is féidir an nóiméad seo a neodrú trí earráidí a idircheapadh agus a athsholáthar ag baint úsáide as an bhfeidhm IFERROR (IFERROR):

  

• Má tá sa bhuntéacs roinnt blúirí as an eolaire ag an am céanna, ansin ní chuirtear ár bhfoirmle in ionad ach an ceann deireanach (sa 8ú líne, Ligovsky «Ascaill« athraíodh go “pr-t”, Ach “S-Pb” on “St. St Petersburg" a thuilleadh, mar gheall ar “S-Pb” níos airde san eolaire). Is féidir an fhadhb seo a réiteach trínár bhfoirmle féin a athreáchtáil, ach ar feadh an cholúin cheana féin seasta:

  
  

Gan foirfe agus cumbersome in áiteanna, ach i bhfad níos fearr ná an athsholáthair láimhe céanna, ceart? 🙂

PS

Sa chéad alt eile, déanfaimid amach conas a leithéid de ionadú mórchóir a chur i bhfeidhm ag baint úsáide as macraí agus Power Query.

• Conas a oibríonn an fheidhm SUBSTITUTE chun téacs a athsholáthar
  
• Meaitseanna Beachta Téacs a Fháil Ag Úsáid Feidhm EXACT
• Cuardach agus ionadú cás-íogair (VLOOKUP cás-íogair)