Oletetaan, että sinulla on luettelo, johon kirjoitetaan vaihtelevalla "suorallisuusasteella" alkutiedot - esimerkiksi osoitteet tai yritysten nimet:
 |  |
On selvästi nähtävissä, että sama kaupunki tai yritys on läsnä täällä kirjavina muunnelmina, mikä luonnollisesti aiheuttaa paljon ongelmia näiden taulukoiden kanssa työskentelyssä tulevaisuudessa. Ja jos vähän ajattelet, voit löytää paljon esimerkkejä vastaavista tehtävistä muilta alueilta.
Kuvittele nyt, että tällaista kieroa dataa tulee sinulle säännöllisesti, eli tämä ei ole kertaluonteinen "korjaa se manuaalisesti, unohda se" -tarina, vaan ongelma säännöllisesti ja suuressa määrässä soluja.
Mitä tehdä? Älä vaihda vinoa tekstiä manuaalisesti 100500 kertaa oikealla "Etsi ja korvaa" -ruudun kautta tai napsauttamalla Ctrl+H?
Ensimmäinen asia, joka tulee mieleen tällaisessa tilanteessa, on tehdä massakorvaus ennalta laaditun hakukirjan mukaan, joka sisältää virheellisiä ja oikeita vaihtoehtoja - kuten tämä:
Valitettavasti tällaisen tehtävän ilmeisen yleisyyden vuoksi Microsoft Excelissä ei ole yksinkertaisia sisäänrakennettuja menetelmiä sen ratkaisemiseksi. Aluksi selvitetään, kuinka tämä tehdään kaavoilla käyttämättä "raskasta tykistöä" makrojen muodossa VBA:ssa tai Power Queryssä.
Tapaus 1. Kokonainen vaihto
Aloitetaan suhteellisen yksinkertaisesta tapauksesta – tilanteesta, jossa vanha kiero teksti on korvattava uudella. täysin.
Oletetaan, että meillä on kaksi pöytää:
Ensimmäisessä – yritysten alkuperäiset kirjavat nimet. Toisessa - kirjeenvaihdon hakuteos. Jos löydämme ensimmäisen taulukon yrityksen nimestä minkä tahansa sanan sarakkeesta Löytää, sinun on korvattava tämä vino nimi kokonaan oikealla - sarakkeesta korvike toinen hakutaulukko.
Mukavuuden vuoksi:
- Molemmat taulukot muunnetaan dynaamiksi ("älykkääksi") pikanäppäimen avulla Ctrl+T tai joukkue Lisää – Taulukko (Lisää - taulukko).
- Näyttöön tulevalla välilehdellä Rakentaja (Design) ensimmäinen pöytä nimeltä Päiväysja toinen viitetaulukko – Pelaajavaihdot.
Selvitetään kaavan logiikkaa, mennään vähän kaukaa.
Otetaan esimerkkinä ensimmäinen yritys solusta A2 ja unohdetaan väliaikaisesti muut yritykset, yritetään määrittää sarakkeesta mikä vaihtoehto Löytää tapaa siellä. Voit tehdä tämän valitsemalla minkä tahansa tyhjän solun arkin vapaasta osasta ja kirjoittamalla funktion sinne LÖYTÄÄ (LÖYTÖ):
Tämä funktio määrittää, sisältyykö annettu osamerkkijono (ensimmäinen argumentti on kaikki sarakkeen arvot Löytää) lähdetekstiin (ensimmäinen yritys tietotaulukosta), ja sen tulee tulostaa joko sen merkin järjestysnumero, josta teksti löydettiin, tai virhe, jos osamerkkijonoa ei löydy.
Temppu tässä on se, että koska emme määritelleet yhtä, vaan useita arvoja ensimmäiseksi argumentiksi, tämä funktio palauttaa tuloksena ei yhden arvon, vaan 3 elementin joukon. Jos sinulla ei ole Office 365:n uusinta versiota, joka tukee dynaamisia taulukoita, syötä tämä kaava ja napsauta enter näet tämän taulukon suoraan arkilla:
Jos sinulla on aiemmat Excel-versiot, napsauta sen jälkeen enter näemme vain ensimmäisen arvon tulostaulukosta, eli virheen #ARVO! (#ARVO!).
Sinun ei pitäisi pelätä 🙂 Itse asiassa kaavamme toimii ja näet silti koko joukon tuloksia, jos valitset syötetyn funktion kaavapalkista ja painat näppäintä F9(älä vain unohda painaa escpalataksesi kaavaan):
Tuloksena oleva tulosjoukko tarkoittaa, että alkuperäisessä vinossa yrityksen nimessä (GK Morozko OAO) kaikista sarakkeen arvoista Löytää löytyi vasta toinen (Morozko), ja alkaen 4. merkistä peräkkäin.
Lisätään nyt funktio kaavaamme KATSO(KATSO YLÖS):
Tällä funktiolla on kolme argumenttia:
- Haluttu arvo - voit käyttää mitä tahansa riittävän suurta numeroa (pääasia, että se ylittää minkä tahansa lähdetiedon tekstin pituuden)
- Katsottu_vektori – alue tai taulukko, josta etsimme haluttua arvoa. Tässä on aiemmin esitelty toiminto LÖYTÄÄ, joka palauttaa taulukon {#VALUE!:4:#VALUE!}
- Vektori_Tulokset – alue, josta haluamme palauttaa arvon, jos haluttu arvo löytyy vastaavasta solusta. Tässä ovat oikeat nimet sarakkeesta korvike referenssitaulukkomme.
Tärkein ja ei-ilmeinen ominaisuus tässä on toiminto KATSO jos tarkkaa vastaavuutta ei ole, etsii aina lähintä pienintä (edellistä) arvoa. Siksi pakotamme määrittämällä minkä tahansa ison luvun (esimerkiksi 9999) halutuksi arvoksi KATSO etsi solu, jolla on lähin pienin luku (4) taulukosta {#ARVO!:4:#ARVO!} ja palauta vastaava arvo tulosvektorista, eli oikea yrityksen nimi sarakkeesta korvike.
Toinen vivahde on, että teknisesti kaavamme on taulukkokaava, koska funktio LÖYTÄÄ palauttaa tuloksina yhden, vaan kolmen arvon joukon. Mutta koska toiminto KATSO tukee taulukoita valmiina, meidän ei tarvitse syöttää tätä kaavaa perinteisenä taulukkokaavana - käyttämällä pikanäppäintä Ctrl+siirtyminen+enter. Yksinkertainen riittää enter.
Siinä kaikki. Toivottavasti ymmärrät logiikan.
On vielä siirrettävä valmis kaava sarakkeen ensimmäiseen soluun B2 kiinteä – ja tehtävämme on ratkaistu!
Tietysti myös tavallisilla (ei älykkäillä) taulukoilla tämä kaava toimii hyvin (älä vain unohda avainta). F4 ja asiaankuuluvien linkkien korjaaminen):
Tapaus 2. Osallinen vaihto
Tämä tapaus on hieman hankalampi. Meillä on jälleen kaksi "älykästä" pöytää:
Ensimmäinen taulukko, jossa on väärin kirjoitetut osoitteet, joka on korjattava (nimesin sen Data2). Toinen taulukko on hakuteos, jonka mukaan osoitteen sisällä oleva osamerkkijono on korvattava osittain (nimesin tätä taulukkoa Vaihtoehdot 2).
Olennainen ero tässä on, että sinun täytyy korvata vain osa alkuperäisestä tiedosta – esimerkiksi ensimmäisessä osoitteessa on virheellinen "St. Pietari” oikealla "St. Pietari”, jättäen loput osoitteesta (postinumero, katu, talo) ennalleen.
Valmis kaava näyttää tältä (havainnoinnin helpottamiseksi jaoin sen kuinka monta riviä käyttäen muut+enter):
Päätyön täällä tekee standardi Excel-tekstitoiminto SUBSTITUTE (KORVAA), jolla on 3 argumenttia:
- Lähdeteksti – ensimmäinen vino osoite Osoite-sarakkeesta
- Mitä etsimme – tässä käytämme temppua toiminnon kanssa KATSO (KATSO YLÖS)edellisestä tavasta noutaa arvo sarakkeesta Löytää, joka sisältyy kaarevan osoitteen fragmenttina.
- Millä korvataan – samalla tavalla löydämme sarakkeesta sitä vastaavan oikean arvon korvike.
Syötä tämä kaava käyttämällä Ctrl+siirtyminen+enter ei tarvita myöskään tässä, vaikka se on itse asiassa taulukkokaava.
Ja on selvästi nähtävissä (katso #N/A-virheet edellisessä kuvassa), että tällaisella kaavalla on kaikesta tyylikkyydestään huolimatta pari haittapuolta:
- Toiminto SUBSTITUTE on kirjainkoon erottelukyky, joten "Spb" toiseksi viimeisellä rivillä ei löytynyt korvaustaulukosta. Voit ratkaista tämän ongelman joko käyttämällä toimintoa ZAMENIT (KORVATA), tai tuo molemmat taulukot alustavasti samaan rekisteriin.
- Jos teksti on alunperin oikein tai siinä korvattavaa fragmenttia ei ole (viimeinen rivi), kaavamme antaa virheen. Tämä hetki voidaan neutraloida sieppaamalla ja korvaamalla virheet toiminnolla IFERROR (IFERROR):
- Jos alkuperäinen teksti sisältää useita fragmentteja hakemistosta kerralla, silloin kaavamme korvaa vain viimeisen (8. rivillä, Ligovsky «Katu« muutettiin "pr-t", Mutta "S-Pb" on "St. Pietari” ei enää, koska "S-Pb” on hakemiston yläpuolella). Tämä ongelma voidaan ratkaista ajamalla oma kaavamme uudelleen, mutta jo saraketta pitkin kiinteä:
Ei täydellinen ja paikoin hankala, mutta paljon parempi kuin sama manuaalinen vaihto, eikö niin? 🙂
PS
Seuraavassa artikkelissa selvitetään, kuinka tällainen joukkokorvaus toteutetaan makrojen ja Power Queryn avulla.
- Miten SUBSTITUTE-toiminto korvaa tekstiä
- Tarkkojen tekstiosumien etsiminen EXACT-funktiolla
- Kirjainkoolla erottuva haku ja korvaaminen (VLOOKUP)