Ibutang ta nga ikaw adunay usa ka lista diin, nga adunay lain-laing ang-ang sa "pagkaprangka," gisulat ang inisyal nga datos - pananglitan, mga adres o mga ngalan sa kompanya:
 |  |
Klaro nga nakita nga ang parehas nga lungsod o kompanya naa dinhi sa mga variant sa motley, nga, klaro, maghimo daghang mga problema kung magtrabaho kauban kini nga mga lamesa sa umaabot. Ug kung maghunahuna ka gamay, makit-an nimo ang daghang mga pananglitan sa parehas nga mga buluhaton gikan sa ubang mga lugar.
Karon hunahunaa nga ang ingon nga hiwi nga datos moabut kanimo kanunay, ie dili kini usa ka higayon nga "manual nga pag-ayo niini, kalimti kini" nga istorya, apan usa ka problema sa kanunay ug sa daghang mga selyula.
Unsay buhaton? Ayaw usba ang hiwi nga teksto nga 100500 ka beses sa husto pinaagi sa kahon nga "Pangita ug Ilisan" o pinaagi sa pag-klik Ctrl+H?
Ang unang butang nga naa sa hunahuna sa ingon nga sitwasyon mao ang paghimo sa usa ka mass replacement sumala sa usa ka pre-compiled reference book sa pagpares sa sayop ug husto nga mga kapilian - sama niini:
Ikasubo, sa dayag nga pagkaylap sa ingon nga buluhaton, ang Microsoft Excel wala’y yano nga built-in nga mga pamaagi alang sa pagsulbad niini. Sa pagsugod, atong mahibal-an kung giunsa kini buhaton gamit ang mga pormula, nga wala mag-apil sa "bug-at nga artilerya" sa porma sa mga macro sa VBA o Power Query.
Kaso 1. Bulk bug-os nga puli
Magsugod ta sa medyo yano nga kaso – usa ka sitwasyon diin kinahanglan nimong ilisan ang daan nga hiwi nga teksto og bag-o. bug-os nga.
Ingnon ta nga adunay duha ka lamesa:
Sa una - ang orihinal nga lainlaing mga ngalan sa mga kompanya. Sa ikaduha - usa ka pakisayran nga libro sa mga sulat. Kung makit-an namon sa ngalan sa kompanya sa una nga lamesa ang bisan unsang pulong gikan sa kolum Pangita-on, nan kinahanglan nimo nga hingpit nga ilisan kining hiwi nga ngalan sa husto - gikan sa kolum Kapuli ikaduhang lookup table.
Para sa kasayon:
- Ang duha ka mga lamesa gi-convert ngadto sa dinamikong (“smart”) gamit ang keyboard shortcut Ctrl+T o team Isulod – Talaan (Sal-ot — Talaan).
- Sa tab nga makita Magtutukod (Laraw) unang lamesa nga ginganlan Data, ug ang ikaduhang reference table - Mga Substitutions.
Para ipatin-aw ang lohika sa pormula, adto ta gamay gikan sa layo.
Pagkuha sa una nga kompanya gikan sa cell A2 ingon usa ka pananglitan ug temporaryo nga nakalimtan ang bahin sa nahabilin nga mga kompanya, sulayan naton mahibal-an kung unsang kapilian gikan sa kolum. Pangita-on nagkita didto. Aron mahimo kini, pilia ang bisan unsang walay sulod nga cell sa libre nga bahin sa sheet ug isulod ang function didto PANGITA-ON (PANGITA):
Kini nga function nagtino kung ang gihatag nga substring gilakip (ang una nga argumento mao ang tanan nga mga kantidad gikan sa kolum Pangita-on) ngadto sa tinubdan nga teksto (ang unang kompanya gikan sa talaan sa datos) ug kinahanglang magpagawas sa ordinal nga numero sa karakter diin ang teksto nakit-an, o usa ka sayop kon ang substring wala makit-an.
Ang lansis dinhi mao nga tungod kay wala namo gipiho ang usa, apan daghang mga kantidad isip unang argumento, kini nga function mobalik usab isip resulta dili usa ka bili, apan usa ka han-ay sa 3 nga mga elemento. Kung wala kay pinakabag-o nga bersyon sa Office 365 nga nagsuporta sa dinamikong mga arrays, unya human sa pagsulod niini nga pormula ug pag-klik sa Pagsulod imong makita kini nga array diha mismo sa sheet:
Kung naa kay mga naunang bersyon sa Excel, unya pagkahuman sa pag-klik Pagsulod makita ra nato ang unang bili gikan sa laray sa resulta, ie error #VALUE! (#BILI!).
Dili ka angay mahadlok 🙂 Sa tinuud, ang among pormula molihok ug makita nimo ang tibuuk nga han-ay sa mga resulta kung imong pilion ang gisulod nga function sa formula bar ug ipadayon ang yawe F9(ayaw lang kalimot sa pagpindot Escbalik sa pormula):
Ang resulta nga han-ay sa mga resulta nagpasabot nga sa orihinal nga hiwi nga ngalan sa kompanya (GK Morozko OAO) sa tanan nga mga kantidad sa usa ka kolum Pangita-on nakit-an ra ang ikaduha (Morozko), ug sugod sa ika-4 nga karakter sa usa ka laray.
Karon magdugang ta ug function sa atong pormula PANGUTANA(TAN-AW SA TAAS):
Kini nga function adunay tulo ka mga argumento:
- Gitinguha nga bili - mahimo nimong gamiton ang bisan unsang igo nga daghang numero (ang panguna nga butang mao nga kini molapas sa gitas-on sa bisan unsang teksto sa gigikanan nga datos)
- Gitan-aw_vector – ang range o array diin kita nangita sa gitinguha nga bili. Ania ang kaniadto gipaila nga function PANGITA-ON, nga nagbalik ug array {#VALUE!:4:#VALUE!}
- Vector_resulta – ang han-ay diin gusto namong ibalik ang kantidad kung ang gusto nga kantidad makit-an sa katugbang nga cell. Ania ang husto nga mga ngalan gikan sa kolum Kapuli among reference table.
Ang panguna ug dili klaro nga bahin dinhi mao nga ang function PANGUTANA kung walay eksaktong tugma, pangitaa kanunay ang labing duol nga pinakagamay (kaniadto) nga kantidad. Busa, pinaagi sa pagtino sa bisan unsa nga dako nga numero (pananglitan, 9999) ingon nga ang gitinguha nga kantidad, atong pugson PANGUTANA pangitaa ang cell nga adunay labing duol nga pinakagamay nga numero (4) sa array {#VALUE!:4:#VALUE!} ug ibalik ang katugbang nga bili gikan sa resulta vector, ie husto nga ngalan sa kompanya gikan sa column Kapuli.
Ang ikaduha nga nuance mao nga, sa teknikal, ang among pormula usa ka array formula, tungod kay function PANGITA-ON mibalik ingon nga mga resulta dili usa, apan usa ka han-ay sa tulo ka mga kantidad. Apan sukad sa function PANGUTANA nagsuporta sa mga arrays gikan sa kahon, nan dili na nato kinahanglan nga mosulod niini nga pormula isip usa ka classic array formula - gamit ang keyboard shortcut Ctrl+pagbalhin+Pagsulod. Ang usa ka yano igo na Pagsulod.
Mao ra. Hinaot makuha nimo ang logic.
Nagpabilin nga ibalhin ang nahuman nga pormula sa una nga cell B2 sa kolum fixed – ug ang atong buluhaton nasulbad na!
Siyempre, uban sa ordinaryo (dili intelihente) nga mga lamesa, kini nga pormula nagtrabaho usab nga maayo (ayaw lang kalimti ang yawe F4 ug pag-ayo sa may kalabutan nga mga link):
Kaso 2. Bulk partial replacement
Kini nga kaso usa ka gamay nga trickier. Sa makausa pa kami adunay duha ka "smart" nga mga lamesa:
Ang una nga lamesa nga adunay hiwi nga sinulat nga mga adres nga kinahanglan nga tul-iron (gitawag ko kini Datos2). Ang ikaduha nga lamesa usa ka reference nga libro, diin kinahanglan nimo nga maghimo usa ka partial nga pagpuli sa usa ka substring sa sulod sa adres (gitawag nako kini nga lamesa Pagpuli2).
Ang sukaranan nga kalainan dinhi mao nga kinahanglan nimo nga ilisan lamang ang usa ka tipik sa orihinal nga datos - pananglitan, ang una nga adres adunay sayup "St. Petersburg” sa tuo "St. Petersburg”, gibiyaan ang nahabilin nga adres (zip code, dalan, balay) nga ingon.
Ang nahuman nga pormula mahimong ingon niini (alang sa kadali sa pagsabut, gibahin ko kini sa pila ka linya nga gigamit alt+Pagsulod):
Ang nag-unang buluhaton dinhi gihimo pinaagi sa standard Excel text function SUBSTITUT ( SUBSTITUT ), nga adunay 3 ka argumento:
- Tinubdan nga teksto – ang unang hiwi nga adres gikan sa Address column
- Unsa ang among gipangita - dinhi among gigamit ang lansis sa function PANGUTANA (TAN-AW SA TAAS)gikan sa miaging paagi sa pagbitad sa bili gikan sa kolum Pangita-on, nga gilakip ingon usa ka tipik sa usa ka kurbadong adres.
- Unsa ang ilisan - sa parehas nga paagi makit-an namon ang husto nga kantidad nga katumbas niini gikan sa kolum Kapuli.
Isulod kini nga pormula gamit ang Ctrl+pagbalhin+Pagsulod Dili usab kinahanglan dinhi, bisan kung kini, sa tinuud, usa ka pormula sa array.
Ug kini klaro nga nakita (tan-awa ang #N/A nga mga sayup sa miaging litrato) nga ang ingon nga pormula, alang sa tanan nga kaanyag niini, adunay pipila ka mga kakulangan:
- function SUBSTITUTE kay case sensitive, mao nga ang "Spb" sa penultimate nga linya wala makita sa puli nga lamesa. Aron masulbad kini nga problema, mahimo nimong gamiton ang function ZAMENIT (PILI), o preliminarily dad-on ang duha ka mga lamesa sa samang rehistro.
- Kung ang teksto sa sinugdan husto o naa niini walay tipik nga ilisan (katapusan nga linya), unya ang among pormula adunay sayup. Kini nga higayon mahimong ma-neutralize pinaagi sa pag-intercept ug pag-ilis sa mga sayup gamit ang function IFERROR (IFERROR):
- Kung ang orihinal nga teksto adunay daghang mga tipik gikan sa direktoryo sa usa ka higayon, unya ang among pormula mopuli lamang sa kataposan (sa ika-8 nga linya, Ligovsky «Avenue« nabag-o sa "pr-t", Apan "S-Pb" on "St. Petersburg” dili na, kay “S-Pb” mas taas sa direktoryo). Kini nga problema masulbad pinaagi sa pagpadagan pag-usab sa atong kaugalingong pormula, apan naa na sa kolum fixed:
Dili perpekto ug hasol sa mga lugar, apan labi ka maayo kaysa parehas nga pagpuli sa manual, dili ba? 🙂
PS
Sa sunod nga artikulo, atong mahibal-an kung giunsa ang pagpatuman sa ingon nga daghang pagpuli gamit ang mga macro ug Power Query.
- Giunsa paglihok ang SUBSTITUTE function aron ilisan ang teksto
- Pagpangita sa Eksaktong Text Match Gamit ang EXACT Function
- Pagpangita ug pagpuli sa case sensitive (case sensitive nga VLOOKUP)