Ranplasman tèks esansyèl ak fòmil

Sipoze ou gen yon lis kote, ak diferan degre "simplite," premye done yo ekri - pou egzanp, adrès oswa non konpayi:

Li wè klè ke menm vil la oswa konpayi an prezan isit la nan varyant motley, ki, evidamman, pral kreye yon anpil nan pwoblèm lè w ap travay ak tab sa yo nan tan kap vini an. Men, si ou panse yon ti kras, ou ka jwenn yon anpil nan egzanp travay ki sanble nan lòt zòn.

Koulye a, imajine ke done kwochi sa yo vin jwenn ou regilyèman, sa vle di sa a se pa yon istwa "manyèlman ranje li, bliye li" yon sèl fwa, men yon pwoblèm sou yon baz regilye ak nan yon gwo kantite selil.

Kisa pou fe? Pa manyèlman ranplase tèks kwochi a 100500 fwa ak youn ki kòrèk la atravè kare "Jwenn ak Ranplase" oswa lè w klike. Ctrl+H?

Premye bagay ki vin nan tèt ou nan yon sitiyasyon konsa se fè yon ranplasman mas selon yon liv referans pre-konpile nan matche opsyon kòrèk ak kòrèk - tankou sa a:

Malerezman, ak prévalence evidan nan yon travay konsa, Microsoft Excel pa gen metòd senp entegre pou rezoud li. Pou kòmanse, ann chèche konnen ki jan fè sa ak fòmil, san yo pa enplike "atiri lou" nan fòm makro nan VBA oswa Power Query.

Ka 1. Bulk ranplasman konplè

Ann kòmanse ak yon ka relativman senp - yon sitiyasyon kote ou bezwen ranplase ansyen tèks kwochi a ak yon nouvo. konplètman.

Ann di nou gen de tab:

Nan premye a - orijinal non dyapre konpayi yo. Nan dezyèm lan - yon liv referans nan korespondans. Si nou jwenn nan non konpayi an nan premye tablo a nenpòt mo ki soti nan kolòn nan Jwenn, Lè sa a, ou bezwen konplètman ranplase non sa a kwochi ak youn ki kòrèk la - soti nan kolòn nan Ranplase dezyèm tab rechèch.

Pou konvenyans:

• Tou de tab yo konvèti nan dinamik ("entelijan") lè l sèvi avèk yon chemen kout klavye Ctrl+T oswa ekip Mete - Tablo (Antre — Tablo).
  
• Sou tab la ki parèt Constructor (Design) premye tab ki rele Done, ak dezyèm tablo referans lan - Sibstitisyon.

Pou eksplike lojik fòmil la, ann ale yon ti kras lwen.

Pran premye konpayi an nan selil A2 kòm yon egzanp epi tanporèman bliye sou rès konpayi yo, ann eseye detèmine ki opsyon nan kolòn nan. Jwenn rankontre la. Pou fè sa, chwazi nenpòt selil vid nan pati a gratis nan fèy la epi antre nan fonksyon an la JWENN (JWENN):

Fonksyon sa a detèmine si substring yo bay la enkli (premye agiman an se tout valè ki soti nan kolòn nan Jwenn) nan tèks sous la (premye konpayi ki soti nan tablo done a) epi yo ta dwe bay swa nimewo ordinal karaktè ki te jwenn tèks la, oswa yon erè si yo pa jwenn substring a.

Trick la isit la se ke depi nou espesifye pa youn, men plizyè valè kòm premye agiman an, fonksyon sa a pral retounen tou kòm yon rezilta pa yon valè, men yon etalaj de 3 eleman. Si ou pa gen dènye vèsyon Office 365 ki sipòte etalaj dinamik, apre w fin antre fòmil sa a epi klike sou Antre nan ou pral wè etalaj sa a dwat sou fèy la:

Si ou gen vèsyon anvan yo nan Excel, Lè sa a, apre klike sou Antre nan nou pral sèlman wè premye valè nan etalaj la rezilta, sa vle di erè #VALÈ! (#VALÈ!).

Ou pa ta dwe pè 🙂 An reyalite, fòmil nou an ap travay epi ou ka toujou wè tout seri rezilta yo si w chwazi fonksyon ki antre nan ba fòmil la epi peze kle a. F9(jis pa bliye peze Kompitèpou tounen nan fòmil la):

Etalaj la ki kapab lakòz rezilta vle di ke nan non orijinal la konpayi kwochi (GK Morozko OAO) nan tout valè nan yon kolòn Jwenn jwenn sèlman dezyèm lan (Morozko), epi kòmanse nan 4yèm karaktè nan yon ranje.

Koulye a, kite a ajoute yon fonksyon nan fòmil nou an VIEW(CHACHE):

Fonksyon sa a gen twa agiman:

1. Valè dezirab - ou ka itilize nenpòt nimewo ase gwo (bagay prensipal la se ke li depase longè nenpòt tèks nan done sous yo)
   
2. Viewed_vector – seri a oswa etalaj kote nou ap chèche pou valè a vle. Isit la se fonksyon ki deja prezante a JWENN, ki retounen yon etalaj {#VALUE!:4:#VALUE!}
   
3. vektè_rezilta – seri a soti nan ki nou vle retounen valè a si yo jwenn valè a vle nan selil ki koresponn lan. Men non kòrèk yo nan kolòn nan Ranplase tab referans nou an.
   

Karakteristik prensipal la ak ki pa evidan isit la se ke fonksyon an VIEW si pa gen okenn match egzak, toujou chèche valè ki pi pre ki pi piti (anvan).. Se poutèt sa, lè nou espesifye nenpòt nimewo for (pa egzanp, 9999) kòm valè a vle, nou pral fòse. VIEW jwenn selil ki gen nimewo ki pi pre a (4) nan etalaj {#VALUE!:4:#VALÈ!} epi retounen valè ki koresponn lan nan vektè rezilta a, sa vle di non konpayi kòrèk la nan kolòn nan. Ranplase.

Dezyèm nuans la se ke, teknikman, fòmil nou an se yon fòmil etalaj, paske fonksyon JWENN retounen kòm rezilta pa youn, men yon etalaj twa valè. Men, depi fonksyon an VIEW sipòte etalaj soti nan bwat la, Lè sa a, nou pa bezwen antre fòmil sa a kòm yon fòmil etalaj klasik - lè l sèvi avèk yon chemen kout klavye Ctrl+Ka glise+Antre nan. Yon senp ap sifi Antre nan.

Se tout. Espere ou jwenn lojik la.

Li rete pou transfere fòmil la fini nan premye selil B2 kolòn nan Fiks - epi travay nou an rezoud!

Natirèlman, ak tab òdinè (pa entelijan), fòmil sa a travay byen tou (jis pa bliye sou kle a F4 ak fikse lyen ki enpòtan yo):

Ka 2. En ranplasman pasyèl

Ka sa a se yon ti kras pi difisil. Ankò nou gen de tab "entelijan":

Premye tab la ki gen adrès ki mal ekri ki bezwen korije (mwen rele li Done2). Dezyèm tab la se yon liv referans, dapre sa ou bezwen fè yon ranplasman pasyèl nan yon substring andedan adrès la (mwen rele tab sa a. Sibstitisyon2).

Diferans fondamantal isit la se ke ou bezwen ranplase sèlman yon fragman nan done orijinal yo - pou egzanp, premye adrès la gen yon kòrèk “St. Petersburg" sou bò dwat la “St. Petersburg", kite rès adrès la (kòd postal, lari, kay) jan li ye.

Fòmil fini an ap gade tankou sa a (pou fasilite pèsepsyon, mwen divize li an konbyen liy lè l sèvi avèk Alt+Antre nan):

Travay prensipal la isit la fè pa fonksyon tèks estanda Excel Ranplasan (SUBSTITUTION), ki gen 3 agiman:

1. Tèks Sous - premye adrès kwochi ki soti nan kolòn Adrès la
2. Ki sa nou ap chèche pou - isit la nou itilize jwe fent la ak fonksyon an VIEW (CHACHE)soti nan fason anvan an rale valè a soti nan kolòn nan Jwenn, ki enkli kòm yon fragman nan yon adrès koube.
3. Ki sa ki ranplase ak - nan menm fason an nou jwenn valè ki kòrèk la ki koresponn ak li nan kolòn nan Ranplase.

Antre fòmil sa a avèk Ctrl+Ka glise+Antre nan pa nesesè isit la tou, byenke li se, an reyalite, yon fòmil etalaj.

Epi li wè klèman (gade erè #N/A nan foto anvan an) ke yon fòmil konsa, pou tout distenksyon li, gen yon koup nan dezavantaj:

• fonksyon SUBSTITUTE se yon bagay ki sansib, Se konsa, "Spb" nan penultyèm liy lan pa te jwenn nan tablo ranplasman an. Pou rezoud pwoblèm sa a, ou ka swa itilize fonksyon an ZAMENIT (RANPLASE), oswa preliminèman pote tou de tab yo nan menm rejis la.
• Si tèks la okòmansman kòrèk oswa nan li pa gen okenn fragman pou ranplase (dènye liy), Lè sa a, fòmil nou an voye yon erè. Moman sa a ka netralize pa entèsepte ak ranplase erè lè l sèvi avèk fonksyon an SI ERREUR (SI ERROR):

  

• Si tèks orijinal la gen ladan l plizyè fragman nan anyè a nan yon fwa, Lè sa a, fòmil nou an ranplase sèlman dènye a (nan 8yèm liy lan, Ligovsky «Avenue« chanje an "pr-t", Men "S-Pb" on “St. Petersburg" pa ankò, paske "S-Pb” pi wo nan anyè a). Pwoblèm sa a ka rezoud pa re-kouri fòmil pwòp nou an, men deja sou kolòn nan Fiks:

  
  

Pa pafè ak ankonbran nan kote, men pi bon pase menm ranplasman manyèl la, dwa? 🙂

PS

Nan pwochen atik la, nou pral konnen ki jan yo aplike tankou yon sibstitisyon esansyèl lè l sèvi avèk makro ak Power Query.

• Ki jan fonksyon SUBSTITUTE a travay pou ranplase tèks
  
• Jwenn Korespondans Tèks Egzak Sèvi ak Fonksyon EXACT la
• Rechèch ak ranplasman ki sansib (chèche VLOOKUP ki sansib)