Amnewid testun swmp gyda fformiwlâu

Tybiwch fod gennych restr lle mae data cychwynnol, gyda graddau amrywiol o “symlrwydd”, yn cael ei ysgrifennu - er enghraifft, cyfeiriadau neu enwau cwmni:

Mae’n amlwg bod yr un ddinas neu gwmni yn bresennol yma mewn amrywiadau brith, a fydd, yn amlwg, yn creu llawer o broblemau wrth weithio gyda’r tablau hyn yn y dyfodol. Ac os ydych chi'n meddwl ychydig, gallwch chi ddod o hyd i lawer o enghreifftiau o dasgau tebyg o feysydd eraill.

Nawr dychmygwch fod data cam o'r fath yn dod atoch yn rheolaidd, hy nid stori un-amser "ei thrwsio â llaw, ei anghofio" yw hon, ond problem yn rheolaidd ac mewn nifer fawr o gelloedd.

Beth i'w wneud? Peidiwch â disodli'r testun cam â llaw 100500 o weithiau gyda'r un cywir trwy'r blwch "Canfod ac Amnewid" neu trwy glicio Ctrl+H?

Y peth cyntaf sy'n dod i'r meddwl mewn sefyllfa o'r fath yw gwneud amnewidiad torfol yn ôl cyfeirlyfr a luniwyd ymlaen llaw o gyfateb opsiynau anghywir a chywir - fel hyn:

Yn anffodus, gyda chyffredinrwydd amlwg tasg o'r fath, nid oes gan Microsoft Excel ddulliau integredig syml i'w datrys. I ddechrau, gadewch i ni ddarganfod sut i wneud hyn gyda fformiwlâu, heb gynnwys “magnelau trwm” ar ffurf macros yn VBA neu Power Query.

Achos 1. Amnewidiad llawn swmp

Gadewch i ni ddechrau gydag achos cymharol syml - sefyllfa lle mae angen i chi ddisodli'r hen destun cam am un newydd. llawn.

Gadewch i ni ddweud bod gennym ddau dabl:

Yn y cyntaf - enwau amrywiol gwreiddiol cwmnïau. Yn yr ail – cyfeirlyfr o ohebiaeth. Os canfyddwn yn enw'r cwmni yn y tabl cyntaf unrhyw air o'r golofn i ddod o hyd, yna mae angen i chi ddisodli'r enw cam hwn yn llwyr gyda'r un cywir - o'r golofn Dirprwy ail dabl chwilio.

Er hwylustod:

• Mae'r ddau dabl yn cael eu trosi i ddeinamig (“smart”) gan ddefnyddio llwybr byr bysellfwrdd Ctrl+T neu dîm Mewnosod – Tabl (Mewnosod — Tabl).
  
• Ar y tab sy'n ymddangos Constructor (Dylunio) tabl cyntaf a enwyd Dyddiad, a'r ail dabl cyfeirio - Dirprwyon.

I egluro rhesymeg y fformiwla, gadewch i ni fynd ychydig o bell.

Gan gymryd y cwmni cyntaf o gell A2 fel enghraifft ac anghofio dros dro am weddill y cwmnïau, gadewch i ni geisio penderfynu pa opsiwn o'r golofn i ddod o hyd yn cyfarfod yno. I wneud hyn, dewiswch unrhyw gell wag yn rhan rydd y ddalen a nodwch y swyddogaeth yno I DARGANFOD (DARGANFOD):

Mae'r swyddogaeth hon yn penderfynu a yw'r is-linyn a roddir wedi'i gynnwys (y ddadl gyntaf yw'r holl werthoedd o'r golofn i ddod o hyd) i'r testun ffynhonnell (y cwmni cyntaf o'r tabl data) a dylai allbynnu naill ai rhif trefnol y nod y canfuwyd y testun ohono, neu wall os na chanfuwyd yr is-linyn.

Y tric yma yw, ers i ni nodi nid un, ond nifer o werthoedd fel y ddadl gyntaf, bydd y swyddogaeth hon hefyd yn dychwelyd o ganlyniad nid un gwerth, ond amrywiaeth o 3 elfen. Os nad oes gennych y fersiwn diweddaraf o Office 365 sy'n cefnogi araeau deinamig, yna ar ôl mynd i mewn i'r fformiwla hon a chlicio ar Rhowch fe welwch yr arae hon yn union ar y ddalen:

Os oes gennych fersiynau blaenorol o Excel, yna ar ôl clicio ar Rhowch dim ond y gwerth cyntaf o'r arae canlyniadau y byddwn yn ei weld, hy gwall #VALUE! (#VALUE!).

Ni ddylech ofni 🙂 Mewn gwirionedd, mae ein fformiwla'n gweithio a gallwch weld yr holl amrywiaeth o ganlyniadau o hyd os dewiswch y swyddogaeth a gofnodwyd yn y bar fformiwla a phwyso'r allwedd F9(Peidiwch ag anghofio pwyso Esci fynd yn ôl at y fformiwla):

Mae'r amrywiaeth canlyniadol o ganlyniadau yn golygu hynny yn enw cam gwreiddiol y cwmni (GK Morozko OAO) o bob gwerth mewn colofn i ddod o hyd dod o hyd dim ond yr ail (Morozko), ac yn cychwyn o'r 4ydd cymeriad yn olynol.

Nawr, gadewch i ni ychwanegu swyddogaeth at ein fformiwla GWELD(EDRYCH):

Mae gan y swyddogaeth hon dair dadl:

1. Gwerth dymunol – gallwch ddefnyddio unrhyw rif digon mawr (y prif beth yw ei fod yn fwy na hyd unrhyw destun yn y data ffynhonnell)
   
2. Edrychwyd_fector – yr ystod neu'r arae lle rydym yn chwilio am y gwerth dymunol. Dyma'r swyddogaeth a gyflwynwyd yn flaenorol I DARGANFOD, sy'n dychwelyd arae {#VALUE!:4:#VALUE!}
   
3. Fector_canlyniadau – yr ystod yr ydym am ddychwelyd y gwerth ohoni os canfyddir y gwerth dymunol yn y gell gyfatebol. Dyma'r enwau cywir o'r golofn Dirprwy ein tabl cyfeirio.
   

Y brif nodwedd ac nad yw'n amlwg yma yw bod y swyddogaeth GWELD os nad oes cyfatebiaeth union, edrychwch bob amser am y gwerth lleiaf (blaenorol) agosaf. Felly, trwy nodi unrhyw rif helaeth (er enghraifft, 9999) fel y gwerth a ddymunir, byddwn yn gorfodi GWELD darganfyddwch y gell gyda'r rhif lleiaf agosaf (4) yn yr arae {#VALUE!:4:#VALUE!} a dychwelwch y gwerth cyfatebol o'r fector canlyniad, hy enw cywir y cwmni o'r golofn Dirprwy.

Yr ail naws yw bod, yn dechnegol, mae ein fformiwla yn fformiwla amrywiaeth, oherwydd swyddogaeth I DARGANFOD yn dychwelyd fel canlyniadau nid un, ond amrywiaeth o dri gwerth. Ond ers y swyddogaeth GWELD yn cefnogi araeau allan o'r blwch, yna nid oes yn rhaid i ni nodi'r fformiwla hon fel fformiwla arae glasurol - gan ddefnyddio llwybr byr bysellfwrdd Ctrl+Symud+Rhowch. Bydd un syml yn ddigon Rhowch.

Dyna i gyd. Gobeithio y cewch chi'r rhesymeg.

Mae'n aros i drosglwyddo'r fformiwla orffenedig i gell gyntaf B2 y golofn Sefydlog - ac mae ein tasg wedi'i datrys!

Wrth gwrs, gyda thablau cyffredin (nid smart), mae'r fformiwla hon hefyd yn gweithio'n wych (peidiwch ag anghofio am yr allwedd F4 a thrwsio'r dolenni perthnasol):

Achos 2. Amnewidiad rhannol swmp

Mae'r achos hwn ychydig yn anoddach. Unwaith eto mae gennym ddau dabl “smart”:

Y tabl cyntaf gyda chyfeiriadau wedi'u hysgrifennu'n gam y mae angen eu cywiro (fe wnes i ei alw Data2). Cyfeirlyfr yw'r ail dabl, ac yn unol â hynny mae angen i chi wneud amnewidiad rhannol o is-linyn y tu mewn i'r cyfeiriad (galwais y tabl hwn Eilyddion2).

Y gwahaniaeth sylfaenol yma yw bod angen i chi ddisodli darn o'r data gwreiddiol yn unig - er enghraifft, mae gan y cyfeiriad cyntaf gyfeiriad anghywir “ St. Petersburg" ar y dde “ St. Petersburg", gan adael gweddill y cyfeiriad (cod zip, stryd, tŷ) fel y mae.

Bydd y fformiwla orffenedig yn edrych fel hyn (er mwyn rhwyddineb canfyddiad, fe'i rhannais i faint o linellau sy'n defnyddio Alt+Rhowch):

Mae'r prif waith yma yn cael ei wneud gan y swyddogaeth testun Excel safonol TANYSGRIFIAD (SUBSTITUTE), sydd â 3 dadl:

1. Testun ffynhonnell – y cyfeiriad cam cyntaf o'r golofn Cyfeiriad
2. Yr hyn yr ydym yn chwilio amdano - yma rydym yn defnyddio'r tric gyda'r swyddogaeth GWELD (EDRYCH)o'r ffordd flaenorol i dynnu'r gwerth o'r golofn i ddod o hyd, a gynnwysir fel dernyn mewn cyfeiriad crwm.
3. Beth i'w ddisodli - yn yr un modd rydym yn dod o hyd i'r gwerth cywir sy'n cyfateb iddo o'r golofn Dirprwy.

Rhowch y fformiwla hon gyda Ctrl+Symud+Rhowch nid oes ei angen yma ychwaith, er mai fformiwla arae ydyw, mewn gwirionedd.

Ac mae'n amlwg (gweler gwallau # N/A yn y llun blaenorol) bod gan fformiwla o'r fath, er ei holl geinder, ychydig o anfanteision:

• swyddogaeth Mae SUBSTITUTE yn sensitif i achosion, felly ni chanfuwyd “Spb” yn y llinell olaf ond un yn y tabl amnewid. I ddatrys y broblem hon, gallwch naill ai ddefnyddio'r swyddogaeth ZAMENIT (NEWID), neu ddod â'r ddau dabl i'r un gofrestr yn gyntaf.
• Os yw'r testun yn gywir i ddechrau neu ynddo nid oes darn i'w ddisodli (llinell olaf), yna mae ein fformiwla yn taflu gwall. Gellir niwtraleiddio'r foment hon trwy ryng-gipio a disodli gwallau gan ddefnyddio'r ffwythiant IFERROR (IFERROR):

  

• Os yw'r testun gwreiddiol yn cynnwys sawl darn o'r cyfeiriadur ar unwaith, yna mae ein fformiwla yn disodli'r un olaf yn unig (yn yr 8fed llinell, Ligovsky «Rhodfa« wedi newid i “pr-t”, Ond “S-Pb” on “ St. Petersburg" mwyach, oherwydd “S-Pb” yn uwch yn y cyfeiriadur). Gellir datrys y broblem hon trwy ail-redeg ein fformiwla ein hunain, ond eisoes ar hyd y golofn Sefydlog:

  
  

Ddim yn berffaith ac yn feichus mewn mannau, ond yn llawer gwell na'r un ailosod â llaw, iawn? 🙂

PS

Yn yr erthygl nesaf, byddwn yn darganfod sut i weithredu amnewidiad swmp o'r fath gan ddefnyddio macros a Power Query.

• Sut mae swyddogaeth SUBSTITUTE yn gweithio i ddisodli testun
  
• Dod o Hyd i Gyfatebion Testun Union Gan Ddefnyddio'r Swyddogaeth EXACT
• Chwilio ac amnewid sy'n sensitif i achosion (VLOOKUP sy'n sensitif i achosion)