Oletame, et teil on loend, kuhu on erineva otsekohesusega kirjutatud algandmed – näiteks aadressid või ettevõtete nimed:
 |  |
Selgelt on näha, et sama linn või firma on siin kirjus variandis, mis ilmselt tekitab tulevikus nende tabelitega töötades palju probleeme. Ja kui veidi järele mõelda, siis võib leida palju näiteid sarnastest ülesannetest ka teistest valdkondadest.
Kujutage nüüd ette, et sellised kõverad andmed jõuavad teieni regulaarselt, st see ei ole ühekordne “paranda käsitsi, unusta ära” looga, vaid probleemiga regulaarselt ja paljudes lahtrites.
Mida teha? Ärge asendage käsitsi kõverat teksti 100500 XNUMX korda õigega kasti "Otsi ja asenda" kaudu või klõpsates Ctrl+H?
Esimene asi, mis sellises olukorras pähe tuleb, on teha massiline asendus vastavalt eelnevalt koostatud valede ja õigete valikute sobitamise teatmikule – näiteks järgmiselt:
Kahjuks pole Microsoft Excelil sellise ülesande ilmselge levimusega lihtsaid sisseehitatud meetodeid selle lahendamiseks. Alustuseks mõtleme välja, kuidas seda teha valemitega, ilma VBA-s või Power Querys makrode kujul „raskekahurväe” kaasamiseta.
Juhtum 1. Hulgi täielik asendamine
Alustame suhteliselt lihtsast juhtumist – olukorrast, kus on vaja vana kõver tekst uuega asendada. täielikult.
Oletame, et meil on kaks tabelit:
Esimeses – firmade algupärased kirjud nimed. Teises – kirjavahetuse teatmeteos. Kui leiame ettevõtte nimest esimesest tabelist suvalise sõna veerust Leidma, siis tuleb see kõver nimi täielikult asendada õigega – veerust Asendaja teine otsingutabel.
Mugavuseks:
- Mõlemad tabelid teisendatakse kiirklahvi abil dünaamilisteks („nutikateks”) Ctrl+T või meeskond Sisesta – tabel (Sisesta – tabel).
- Ilmuval vahekaardil Ehitaja (Kujundus) esimene tabel nimega kuupäevja teine võrdlustabel – Vahetused.
Valemi loogika selgitamiseks läheme veidi eemalt.
Võttes näitena esimese ettevõtte lahtrist A2 ja unustades ajutiselt ülejäänud ettevõtted, proovime veerust kindlaks teha, milline valik Leidma kohtub seal. Selleks valige lehe vabas osas suvaline tühi lahter ja sisestage sinna funktsioon LEIDMA (LEIA):
See funktsioon määrab, kas antud alamstring on kaasatud (esimene argument on kõik veerus olevad väärtused Leidma) lähteteksti (esimene ettevõte andmetabelist) ja peaks väljastama kas märgi järjekorranumbri, millest tekst leiti, või veateate, kui alamstringi ei leitud.
Siin on trikk selles, et kuna me ei määranud esimese argumendina mitte ühe, vaid mitu väärtust, tagastab see funktsioon ka mitte ühe väärtuse, vaid 3 elemendi massiivi. Kui teil pole Office 365 uusimat versiooni, mis toetab dünaamilisi massiive, siis pärast selle valemi sisestamist ja klõpsamist sisene näete seda massiivi otse lehel:
Kui teil on Exceli eelmised versioonid, siis pärast klõpsamist sisene me näeme tulemuste massiivist ainult esimest väärtust ehk viga #VALUE! (#VALUE!).
Te ei tohiks karta 🙂 Tegelikult meie valem töötab ja ikkagi näete kogu tulemuste massiivi, kui valite valemiribal sisestatud funktsiooni ja vajutate klahvi F9(ära unusta vajutada Escvalemi juurde tagasi pöördumiseks):
Saadud tulemuste massiiv tähendab, et ettevõtte algses kõveras nimes (GK Morozko OAO) kõigist veerus olevatest väärtustest Leidma leidis ainult teise (Morozko), ja alustades järjekorras 4. märgist.
Nüüd lisame oma valemile funktsiooni Vaata(VAATA ÜLES):
Sellel funktsioonil on kolm argumenti:
- Soovitud väärtus - võite kasutada mis tahes piisavalt suurt arvu (peaasi, et see ületaks lähteandmetes oleva teksti pikkuse)
- Vaadatud_vektor – vahemik või massiiv, kust otsime soovitud väärtust. Siin on eelnevalt tutvustatud funktsioon LEIDMA, mis tagastab massiivi {#VALUE!:4:#VALUE!}
- Vektor_tulemused – vahemik, kust soovime väärtust tagastada, kui vastavast lahtrist leitakse soovitud väärtus. Siin on õiged nimed veerust Asendaja meie võrdlustabel.
Peamine ja mitteilmne omadus on siin see funktsioon Vaata kui täpset vastet pole, otsib alati lähimat väikseimat (eelmist) väärtust. Seega, määrates soovitud väärtuseks mis tahes kopsaka numbri (näiteks 9999), sunnime Vaata leidke massiivist {#VALUE!:4:#VALUE!} lähima väikseima arvuga (4) lahter ja tagastage tulemusvektorist vastav väärtus ehk õige ettevõtte nimi veerust Asendaja.
Teine nüanss on see, et tehniliselt on meie valem massiivivalem, kuna funktsioon LEIDMA tagastab tulemustena mitte ühe, vaid kolme väärtuse massiivi. Aga kuna funktsioon Vaata toetab massiive juba väljas, siis ei pea me seda valemit klassikalise massiivivalemina sisestama – kasutades kiirklahvi Ctrl+nihe+sisene. Piisab ühest lihtsast sisene.
See on kõik. Loodetavasti saad loogikast aru.
Jääb üle kanda valmis valem veeru esimesse lahtrisse B2 Fikseeritud – ja meie ülesanne on lahendatud!
Muidugi, tavaliste (mitte nutikate) tabelitega töötab see valem ka suurepäraselt (ära unusta võtit). F4 ja asjakohaste linkide parandamine):
Juhtum 2. Massi osaline asendamine
See juhtum on veidi keerulisem. Jällegi on meil kaks "nutikat" tabelit:
Esimene tabel viltu kirjutatud aadressidega, mis vajab parandamist (ma nimetasin seda Andmed2). Teine tabel on teatmeteos, mille järgi peate aadressi sees alamstringi osaliselt asendama (nimetasin selle tabeli Asendused2).
Põhiline erinevus seisneb siin selles, et peate asendama ainult killu algandmetest – näiteks esimene aadress on vale “St. Peterburi” paremal “St. Peterburi”, jättes ülejäänud aadressi (postiindeks, tänav, maja) samaks.
Valmis valem näeb välja selline (taju hõlbustamiseks jagasin selle mitmeks reaks muu+sisene):
Põhitöö teeb siin ära standardne Exceli tekstifunktsioon ASENDA (ASENDAJA), millel on 3 argumenti:
- Lähtetekst – esimene kõveraadress veerust Aadress
- Mida me otsime – siin kasutame funktsiooniga trikki Vaata (VAATA ÜLES)eelmisest viisist väärtuse veerust tõmbamiseks Leidma, mis sisaldub fragmendina kõveras aadressis.
- Millega asendada – samamoodi leiame veerust sellele vastava õige väärtuse Asendaja.
Sisestage see valem koos Ctrl+nihe+sisene ka siin pole vaja, kuigi tegelikult on tegemist massiivivalemiga.
Ja on selgelt näha (vt #N/A vead eelmisel pildil), et sellisel valemil on kogu oma elegantsi juures paar puudust:
- funktsioon SUBSTITUTE on tõstutundlik, seega asendustabelist eelviimasel real olevat “Spb” ei leitud. Selle probleemi lahendamiseks võite kasutada funktsiooni ZAMENIT (ASENDA), või tuua mõlemad tabelid esialgu samasse registrisse.
- Kui tekst on algselt õige või selles pole ühtegi fragmenti, mida asendada (viimane rida), siis annab meie valem vea. Seda hetke saab neutraliseerida funktsiooni abil vigade pealtkuulamise ja asendamisega VIGA (IFERROR):
- Kui originaaltekst sisaldab mitu fragmenti kataloogist korraga, siis meie valem asendab ainult viimast (8. real, Ligovsky «Puiestee« muudetud "pr-t", Aga "S-Pb" on “St. Peterburi” enam mitte, sest "S-Pb” asub kataloogis kõrgemal). Selle probleemi saab lahendada meie enda valemi uuesti käivitamisega, kuid juba mööda veergu Fikseeritud:
Mitte täiuslik ja kohati tülikas, aga palju parem kui sama käsitsi vahetamine, eks? 🙂
PS
Järgmises artiklis selgitame välja, kuidas sellist hulgiasendust makrode ja Power Query abil rakendada.
- Kuidas funktsioon SUBSTITUTE töötab teksti asendamisel
- Täpsete tekstivastete leidmine funktsiooni EXACT abil
- Tõstutundlik otsing ja asendamine (tõstutundlik VLOOKUP)