Ipagpalagay na mayroon kang isang listahan kung saan, na may iba't ibang antas ng "pagkatuwiran," nakasulat ang paunang data - halimbawa, mga address o pangalan ng kumpanya:
 |  |
Malinaw na nakikita na ang parehong lungsod o kumpanya ay naroroon dito sa mga variant ng motley, na, malinaw naman, ay lilikha ng maraming problema kapag nagtatrabaho sa mga talahanayang ito sa hinaharap. At kung mag-isip ka ng kaunti, makakahanap ka ng maraming halimbawa ng mga katulad na gawain mula sa ibang mga lugar.
Ngayon isipin na ang ganitong baluktot na data ay dumarating sa iyo nang regular, ibig sabihin, ito ay hindi isang beses na "manu-manong ayusin ito, kalimutan ito" na kuwento, ngunit isang problema sa isang regular na batayan at sa isang malaking bilang ng mga cell.
Anong gagawin? Huwag manu-manong palitan ang baluktot na teksto nang 100500 beses ng tama sa pamamagitan ng kahon na "Hanapin at Palitan" o sa pamamagitan ng pag-click Ctrl+H?
Ang unang bagay na pumapasok sa isip sa ganoong sitwasyon ay gumawa ng mass replacement ayon sa isang pre-compiled reference book ng pagtutugma ng mali at tamang mga opsyon – tulad nito:
Sa kasamaang palad, sa halatang pagkalat ng naturang gawain, ang Microsoft Excel ay walang mga simpleng built-in na pamamaraan para sa paglutas nito. Upang magsimula, alamin natin kung paano ito gagawin gamit ang mga formula, nang hindi kinasasangkutan ng "mabigat na artilerya" sa anyo ng mga macro sa VBA o Power Query.
Kaso 1. Bultuhang buong kapalit
Magsimula tayo sa isang medyo simpleng kaso – isang sitwasyon kung saan kailangan mong palitan ng bago ang lumang baluktot na teksto. ganap.
Sabihin nating mayroon tayong dalawang talahanayan:
Sa una - ang orihinal na sari-saring pangalan ng mga kumpanya. Sa pangalawa - isang sangguniang libro ng sulat. Kung makikita natin sa pangalan ng kumpanya sa unang talahanayan ang anumang salita mula sa column Hanapin, pagkatapos ay kailangan mong ganap na palitan ang baluktot na pangalan na ito ng tama – mula sa column Kapalit pangalawang lookup table.
Para sa kaginhawaan:
- Ang parehong mga talahanayan ay na-convert sa dynamic ("smart") gamit ang isang keyboard shortcut Ctrl+T o pangkat Ipasok – Talahanayan (Ipasok - Talahanayan).
- Sa tab na lalabas Tagagawa (Disenyo) unang talahanayan na pinangalanan data, at ang pangalawang reference table – Pamalit.
Upang ipaliwanag ang lohika ng formula, pumunta tayo ng kaunti mula sa malayo.
Ang pagkuha ng unang kumpanya mula sa cell A2 bilang isang halimbawa at pansamantalang nakalimutan ang tungkol sa iba pang mga kumpanya, subukan nating matukoy kung aling opsyon mula sa column Hanapin nagkikita doon. Upang gawin ito, pumili ng anumang walang laman na cell sa libreng bahagi ng sheet at ipasok ang function doon HANAPIN (HANAPIN):
Tinutukoy ng function na ito kung ang ibinigay na substring ay kasama (ang unang argumento ay ang lahat ng mga halaga mula sa column Hanapin) sa pinagmulang teksto (ang unang kumpanya mula sa talahanayan ng data) at dapat na i-output ang alinman sa ordinal na numero ng character kung saan natagpuan ang teksto, o isang error kung hindi natagpuan ang substring.
Ang trick dito ay dahil hindi namin tinukoy ang isa, ngunit ilang mga halaga bilang unang argumento, ang function na ito ay babalik din bilang isang resulta hindi isang halaga, ngunit isang hanay ng 3 elemento. Kung wala kang pinakabagong bersyon ng Office 365 na sumusuporta sa mga dynamic na array, pagkatapos ipasok ang formula na ito at i-click ang Magpasok makikita mo ang array na ito mismo sa sheet:
Kung mayroon kang mga nakaraang bersyon ng Excel, pagkatapos ay pagkatapos ng pag-click sa Magpasok makikita lang natin ang unang halaga mula sa hanay ng resulta, ibig sabihin, error #VALUE! (#VALUE!).
Hindi ka dapat matakot 🙂 Sa katunayan, gumagana ang aming formula at makikita mo pa rin ang buong hanay ng mga resulta kung pipiliin mo ang ipinasok na function sa formula bar at pinindot ang key F9(huwag kalimutang pindutin Escpara bumalik sa formula):
Ang resultang hanay ng mga resulta ay nangangahulugan na sa orihinal na baluktot na pangalan ng kumpanya (GK Morozko OAO) ng lahat ng halaga sa isang column Hanapin natagpuan lamang ang pangalawa (Morozko), at simula sa ika-4 na magkakasunod na character.
Ngayon, magdagdag tayo ng function sa ating formula VIEW(TINGNAN):
Ang function na ito ay may tatlong argumento:
- Ninanais na halaga – maaari kang gumamit ng anumang sapat na malaking numero (ang pangunahing bagay ay lumampas ito sa haba ng anumang teksto sa source data)
- Viewed_vector – ang hanay o hanay kung saan hinahanap namin ang nais na halaga. Narito ang dating ipinakilala na function HANAPIN, na nagbabalik ng array {#VALUE!:4:#VALUE!}
- Vector_resulta – ang hanay kung saan nais naming ibalik ang halaga kung ang nais na halaga ay matatagpuan sa kaukulang cell. Narito ang mga tamang pangalan mula sa column Kapalit aming reference table.
Ang pangunahing at hindi halatang tampok dito ay ang function VIEW kung walang eksaktong tugma, laging hanapin ang pinakamalapit na pinakamaliit (nakaraang) halaga. Samakatuwid, sa pamamagitan ng pagtukoy ng anumang mabigat na numero (halimbawa, 9999) bilang ang nais na halaga, pipilitin naming VIEW hanapin ang cell na may pinakamalapit na pinakamaliit na numero (4) sa array {#VALUE!:4:#VALUE!} at ibalik ang katumbas na halaga mula sa vector ng resulta, ibig sabihin, tamang pangalan ng kumpanya mula sa column Kapalit.
Ang pangalawang nuance ay na, technically, ang aming formula ay isang array formula, dahil function HANAPIN nagbabalik bilang mga resulta hindi isa, ngunit isang hanay ng tatlong mga halaga. Ngunit dahil ang pag-andar VIEW sumusuporta sa mga array out of the box, pagkatapos ay hindi namin kailangang ilagay ang formula na ito bilang isang klasikong array formula - gamit ang isang keyboard shortcut Ctrl+Ilipat+Magpasok. Ang isang simple ay sapat na Magpasok.
Iyon lang. Sana makuha mo ang logic.
Nananatili itong ilipat ang natapos na formula sa unang cell B2 ng column Nakapirmi - at ang aming gawain ay nalutas na!
Siyempre, sa mga ordinaryong (hindi matalino) na mga talahanayan, mahusay din ang formula na ito (huwag kalimutan ang tungkol sa susi F4 at pag-aayos ng mga nauugnay na link):
Kaso 2. Maramihang bahagyang pagpapalit
Ang kasong ito ay medyo nakakalito. Muli, mayroon kaming dalawang "matalinong" talahanayan:
Ang unang talahanayan na may baluktot na nakasulat na mga address na kailangang itama (tinawag ko ito Data2). Ang pangalawang talahanayan ay isang reference na libro, ayon sa kung saan kailangan mong gumawa ng isang bahagyang kapalit ng isang substring sa loob ng address (tinawag ko ang talahanayang ito Pagpapalit2).
Ang pangunahing pagkakaiba dito ay kailangan mong palitan lamang ang isang fragment ng orihinal na data - halimbawa, ang unang address ay may mali “St. Petersburg” sa kanan “St. Petersburg”, na iniiwan ang natitirang address (zip code, kalye, bahay) nang ganoon.
Ang natapos na formula ay magiging ganito (para sa kadalian ng pang-unawa, hinati ko ito sa kung gaano karaming mga linya ang gumagamit Alt+Magpasok):
Ang pangunahing gawain dito ay ginagawa ng karaniwang Excel text function SUBSTITUTE (PALIT), na mayroong 3 argumento:
- Source text – ang unang baluktot na address mula sa column ng Address
- Ano ang hinahanap namin - dito ginagamit namin ang trick na may function VIEW (TINGNAN)mula sa nakaraang paraan upang hilahin ang halaga mula sa column Hanapin, na kasama bilang isang fragment sa isang curved address.
- Ano ang papalitan - sa parehong paraan makikita namin ang tamang halaga na naaayon dito mula sa column Kapalit.
Ilagay ang formula na ito gamit ang Ctrl+Ilipat+Magpasok ay hindi rin kailangan dito, kahit na ito ay, sa katunayan, isang array formula.
At malinaw na nakikita (tingnan ang #N/A error sa nakaraang larawan) na ang gayong pormula, para sa lahat ng kagandahan nito, ay may ilang mga kakulangan:
- tungkulin Ang SUBSTITUTE ay case sensitive, kaya ang "Spb" sa penultimate na linya ay hindi nakita sa kapalit na talahanayan. Upang malutas ang problemang ito, maaari mong gamitin ang function ZAMENIT (PALITAN), o preliminarily dalhin ang parehong mga talahanayan sa parehong rehistro.
- Kung ang teksto sa simula ay tama o nasa loob nito walang fragment na papalitan (huling linya), pagkatapos ay naghagis ng error ang aming formula. Ang sandaling ito ay maaaring neutralisahin sa pamamagitan ng pagharang at pagpapalit ng mga error gamit ang function IFERROR (IFERROR):
- Kung naglalaman ang orihinal na teksto ilang mga fragment mula sa direktoryo nang sabay-sabay, pagkatapos ay pinapalitan lamang ng aming formula ang huli (sa ika-8 linya, Ligovsky «Abenida« binago sa "pr-t", Ngunit "S-Pb" on “St. Petersburg” hindi na, kasi “S-Pb” ay mas mataas sa direktoryo). Ang problemang ito ay maaaring malutas sa pamamagitan ng muling pagpapatakbo ng aming sariling formula, ngunit nasa hanay na Nakapirmi:
Hindi perpekto at mahirap sa mga lugar, ngunit mas mahusay kaysa sa parehong manu-manong pagpapalit, tama ba? 🙂
PS
Sa susunod na artikulo, malalaman natin kung paano ipatupad ang naturang bulk substitution gamit ang mga macro at Power Query.
- Paano gumagana ang SUBSTITUTE function upang palitan ang text
- Paghahanap ng Eksaktong Mga Tugma sa Teksto Gamit ang EXACT Function
- Case sensitive na paghahanap at pagpapalit (case sensitive VLOOKUP)