Skipt um magn texta með formúlum

Segjum sem svo að þú sért með lista þar sem, með mismiklum „einfaldleika“, eru upphafsgögn skrifuð - til dæmis heimilisföng eða fyrirtækjanöfn:

Það sést greinilega að hér er sama borg eða fyrirtæki til staðar í brosóttum afbrigðum, sem augljóslega mun skapa mikil vandamál þegar unnið er með þessar töflur í framtíðinni. Og ef þú hugsar þig aðeins um geturðu fundið fullt af dæmum um svipuð verkefni frá öðrum sviðum.

Ímyndaðu þér nú að svona skakkt gögn berist til þín reglulega, þ.e. þetta er ekki einskiptis saga „lagaðu það handvirkt, gleymdu því“ heldur vandamál reglulega og í miklum fjölda frumna.

Hvað skal gera? Ekki skipta út skakka textanum handvirkt 100500 sinnum fyrir réttan í gegnum reitinn „Finna og skipta út“ eða með því að smella á Ctrl+H?

Það fyrsta sem mér dettur í hug í slíkum aðstæðum er að skipta um fjöldann í samræmi við fyrirfram samsetta uppflettibók sem samsvarar röngum og réttum valkostum – svona:

Því miður, með augljósu algengi slíks verkefnis, hefur Microsoft Excel ekki einfaldar innbyggðar aðferðir til að leysa það. Til að byrja með skulum við reikna út hvernig á að gera þetta með formúlum, án þess að taka þátt í „þungum stórskotalið“ í formi fjölva í VBA eða Power Query.

Tilfelli 1. Full skipti

Við skulum byrja á tiltölulega einföldu máli - aðstæður þar sem þú þarft að skipta út gamla skakka textanum fyrir nýjan. að fullu.

Segjum að við höfum tvær töflur:

Í fyrsta lagi - upprunalegu fjölbreyttu nöfn fyrirtækja. Í öðru - uppflettiriti bréfaskrifta. Ef við finnum í nafni fyrirtækisins í fyrstu töflunni hvaða orð sem er úr dálknum Að finna, þá þarftu alveg að skipta út þessu skakka nafni fyrir rétta nafnið – úr dálknum Staðgengill önnur uppflettitöflu.

Til þæginda:

• Báðum töflunum er breytt í kraftmikið („snjall“) með því að nota flýtilykla Ctrl+T eða lið Sett inn - Tafla (Setja inn - Tafla).
  
• Á flipanum sem birtist Framkvæmdaaðili (Hönnun) fyrsta borðið nefnt Gögn, og önnur viðmiðunartafla – Punktbreytingar.

Til að útskýra rökfræði formúlunnar skulum við fara aðeins úr fjarlægð.

Með því að taka fyrsta fyrirtækið úr reit A2 sem dæmi og gleyma tímabundið restinni af fyrirtækjunum, skulum við reyna að ákvarða hvaða valmöguleika úr dálknum Að finna mætir þar. Til að gera þetta skaltu velja hvaða tóma reit sem er í lausa hluta blaðsins og slá inn aðgerðina þar AÐ FINNA (FINNA):

Þessi aðgerð ákvarðar hvort tiltekinn undirstrengur sé innifalinn (fyrstu rökin eru öll gildi úr dálknum Að finna) inn í frumtextann (fyrsta fyrirtækið úr gagnatöflunni) og ætti að gefa út annað hvort raðtölu stafsins sem textinn fannst úr eða villu ef undirstrengurinn fannst ekki.

The bragð hér er að þar sem við tilgreindum ekki eitt, heldur nokkur gildi sem fyrstu rök, mun þessi aðgerð einnig skila ekki einu gildi, heldur fylki af 3 þáttum. Ef þú ert ekki með nýjustu útgáfuna af Office 365 sem styður kraftmikla fylki, þá eftir að hafa slegið inn þessa formúlu og smellt á Sláðu inn þú munt sjá þetta fylki beint á blaðinu:

Ef þú ert með fyrri útgáfur af Excel, þá eftir að hafa smellt á Sláðu inn við munum aðeins sjá fyrsta gildið úr niðurstöðufylkinu, þ.e villa #VALUE! (#VALUE!).

Þú ættir ekki að vera hræddur 🙂 Reyndar virkar formúlan okkar og þú getur ennþá séð alla niðurstöðurnar ef þú velur innslátta aðgerðina í formúlustikunni og ýtir á takkann F9(bara ekki gleyma að ýta á Esctil að fara aftur í formúluna):

Niðurstöðurnar sem myndast þýðir að í upprunalega skakka fyrirtækjanafninu (GK Morozko OAO) af öllum gildum í dálki Að finna fann aðeins annað (Morozko), og byrja á 4. stafnum í röðinni.

Nú skulum við bæta falli við formúluna okkar SKOÐA(HORFÐU UPP):

Þessi aðgerð hefur þrjú rök:

1. Æskilegt gildi - þú getur notað hvaða nægilega stóra tölu sem er (aðalatriðið er að það fer yfir lengd hvaða texta sem er í upprunagögnunum)
   
2. Skoðaður_vektor – svið eða fylki þar sem við erum að leita að æskilegu gildi. Hér er aðgerðin sem áður var kynnt AÐ FINNA, sem skilar fylki {#VALUE!:4:#VALUE!}
   
3. Vector_Niðurstöður – bilið sem við viljum skila gildinu frá ef æskilegt gildi er að finna í samsvarandi reit. Hér eru rétt nöfn úr dálknum Staðgengill viðmiðunartöflu okkar.
   

Helsti og óljósa eiginleiki hér er að aðgerðin SKOÐA ef það er engin nákvæm samsvörun, leitar alltaf að næsta minnsta (fyrra) gildi. Þess vegna, með því að tilgreina hvaða stóra tölu sem er (til dæmis 9999) sem æskilegt gildi, þvingum við SKOÐA finndu reitinn með næstu minnstu tölu (4) í fylkinu {#VALUE!:4:#VALUE!} og skilaðu samsvarandi gildi úr niðurstöðuvektornum, þ.e. rétt fyrirtækisnafn úr dálknum Staðgengill.

Annað litbrigðið er að tæknilega séð er formúlan okkar fylkisformúla, vegna þess að virka AÐ FINNA skilar sem niðurstöðum ekki einu, heldur fylki með þremur gildum. En þar sem virka SKOÐA styður fylki úr kassanum, þá þurfum við ekki að slá inn þessa formúlu sem klassíska fylkisformúlu - með því að nota flýtilykla Ctrl+Shift+Sláðu inn. Einfaldur mun nægja Sláðu inn.

Það er allt og sumt. Vona að þú skiljir rökfræðina.

Það er eftir að flytja fullunna formúluna í fyrsta reit B2 í dálknum Fast – og verkefni okkar er leyst!

Auðvitað, með venjulegum (ekki snjöllum) töflum, virkar þessi formúla líka frábærlega (bara ekki gleyma lyklinum F4 og laga viðeigandi tengla):

Tilfelli 2. Skipting í magni að hluta

Þetta mál er aðeins erfiðara. Aftur höfum við tvö „snjöll“ töflur:

Fyrsta taflan með skakkt skrifað heimilisföng sem þarf að leiðrétta (ég kallaði það Gögn2). Önnur taflan er uppflettirit, samkvæmt henni þarftu að skipta út að hluta undirstreng inni í heimilisfanginu (ég kallaði þessa töflu Skipting 2).

Grundvallarmunurinn hér er sá að þú þarft aðeins að skipta út broti af upprunalegu gögnunum - til dæmis er fyrsta heimilisfangið rangt „St. Pétursborg“ á hægri „St. Pétursborg“, og skilur restina af heimilisfanginu (póstnúmeri, götu, hús) eftir eins og það er.

Fullbúna formúlan mun líta svona út (til að auðvelda skynjun skipti ég henni í hversu margar línur nota Alt+Sláðu inn):

Aðalvinnan hér er unnin með venjulegu Excel textaaðgerðinni Varamaður (VARAMAÐUR), sem hefur 3 rök:

1. Upprunatexti – fyrsta skakka heimilisfangið úr dálknum Heimilisfang
2. Það sem við erum að leita að - hér notum við bragðið með aðgerðinni SKOÐA (HORFÐU UPP)frá fyrri leið til að draga gildið úr dálknum Að finna, sem fylgir sem brot í bogadregnu heimilisfangi.
3. Hvað á að skipta út fyrir - á sama hátt finnum við rétt gildi sem samsvarar því úr dálknum Staðgengill.

Sláðu inn þessa formúlu með Ctrl+Shift+Sláðu inn er ekki þörf hér heldur, þó að það sé í raun fylkisformúla.

Og það sést greinilega (sjá #N/A villur á fyrri mynd) að slík formúla, þrátt fyrir allan glæsileikann, hefur nokkra galla:

• virka STÖÐURINN er hástafaviðkvæmur, þannig að „Spb“ í næstsíðustu línunni fannst ekki í töflunni sem skiptist. Til að leysa þetta vandamál geturðu annað hvort notað aðgerðina ZAMENIT (SKIPTA), eða færa báðar töflurnar til bráðabirgða á sama skrá.
• Ef textinn er upphaflega réttur eða í honum það er ekkert brot sem kemur í staðinn (síðasta línan), þá kastar formúlan okkar villu. Þetta augnablik er hægt að hlutleysa með því að stöðva og skipta um villur með því að nota aðgerðina IFERROR (FJÚR):

  

• Ef frumtextinn inniheldur nokkur brot úr möppunni í einu, þá kemur formúlan okkar í stað þeirrar síðustu (í 8. línu, Ligovsky «Avenue« breytt í "pr-t", En "S-Pb" on „St. Pétursborg“ ekki lengur, því „S-Pb” er ofar í skránni). Þetta vandamál er hægt að leysa með því að keyra okkar eigin formúlu aftur, en nú þegar eftir dálknum Fast:

  
  

Ekki fullkomið og fyrirferðarmikið á stöðum, en miklu betra en sama handvirka skiptingin, ekki satt? 🙂

PS

Í næstu grein munum við finna út hvernig á að útfæra slíka magnskipti með því að nota fjölva og Power Query.

• Hvernig SUBSTITUTE aðgerðin virkar til að skipta út texta
  
• Að finna nákvæma textasamsvörun með því að nota EXACT aðgerðina
• Látahá og hástafaviðkvæm leit og skipting (hástafanæmi VLOOKUP)