Lukijakysymys: Tekstin haku solun sisältä

Tänään käydään läpi seuraava lukijan lähettämä excel-ongelma. Jos sinulla on excel-kysymyksiä, joihin haluaisit vastauksen, ota yhteyttä lomakkeella.

Minulla on aika visainen ja monimutkainen kaava johon en ole löytänyt minkäänlaista toimivaa vastausta.

Oletetaan, että minulla on esim. Solussa B100 tuotekoodi, joka on esim 4CK2VK2421-PR-01 ja 42421-PR-01-B. Mainittakoon tässä kohtaa, että tuotekoodit ovat eri pituisia, mutta -PR- ja -B arvot esiintyvät useasti, mutta niiden paikka voi vaihdella. -B on toki aina viimeisinä merkkeinä, mutta -PR- paikka voi vaihtua. 

Tarkoitukseni olisi saada etsittyä B100 solun tuotekoodista arvo -PR- ja/tai -B, jonka jälkeen kaava palauttaisi C100 soluun sanan ”Ristikytkentä” tai ”Musta”, riippuen onko solussa B100 mainittu -PR- tai -B.

Hommaa helpottamaan, kaava voidaan tehdä siten, että -PR- = ristikytkentä  sana tuodaan soluun C100 ja -B = musta tuodaan soluun D100, eli ei tarvitse molempien tietojen tulla samaan soluun, eli kaksi erilaista kaavaa.

Eli tiivistettynä haasteena on löytää solusta tietty merkkijono (-PR- tai -B) ja merkitä sen perusteella viereiseen soluun tietty teksti.

Tämä vaatii useamman kaavan soveltamista soluun. Ensinnäkin tarvitaan ETSI -funktiota, joka kertoo mistä kohdasta etsittävä merkkijono löytyy. Jos merkkijonoa ei löydy, palauttaa funktio arvon #PUUTTUU!

Koska puuttuvaa arvoa ei voi käyttää vertailussa, tarvitsemme JOSVIRHE-funktiota tunnistamaan puuttuvat arvot.

Lisäksi käytämme JOS -funktiota päättelemään mitä tehdään jos solussa on etsittävä merkkijono ja lopuksi KETJUTA -funktiota yhdistämään molempien etsittävien tekstien sisältö samaan soluun. Tämä viimeinen vaihe on valinnainen, alkuperäisen kysymyksen mukaan voisimme tehdä kaksi erillistä hakua ja kirjoittaa vastaukset eri soluihin.

Käydään ratkaisu vaihe vaiheelta läpi.

VAIHE 1 : Haetaan merkkijono ETSI -funktiolla

ETSI -funktiolle annetaan ensimmäiseksi arvoksi etsittävä teksti lainausmerkeissä, eli tässä tapauksessa ”-PR-”. Toiseksi arvoksi annetaan solu, mistä merkkijonoa etsitään. Kaava on kokonaisuudessaan =ETSI(”-PR-”; A1).

Tulokseksi tulee 11, eli haettava merkkijono löytyy yhdennentoista merkin kohdalta solusta A1.

VAIHE 2 : Puuttuvan arvon tarkastus JOSVIRHE -funktion avulla

JOSVIRHE -funktiolle annetaan sen solun arvo, missä virhe voi tapahtua (tässä tapauksessa edellinen kaava) ja millä tiedolla virheteksti korvataan. Käytetään korvaajana numeroa 0, mikä vastaa arvoa EPÄTOSI.

Tässä voitaisiin käyttää myös puuttuvien arvojen tarkastamiseen käytettävää JOSPUUTTUU -funktiota, mikä toimii täsmälleen samalla tavalla kuin JOSVIRHE, mutta ei reagoi muihin virheellisiin arvoihin.

Kaava on nyt muodossa =JOSVIRHE(ETSI(”-PR-”; A1);0). Esimerkin tapauksessa JOSVIRHE -funktion lisääminen ei muuta lopputulosta mitenkään, sillä solussa A1 on etsittävä arvo.

VAIHE 3 : Uuden merkkijonon lisääminen JOS -funktion avulla

Viimeisessä vaiheessa lisäämme JOS -funktion avulla merkkijonon ”Ristikytkentä” mikäli aiempi kaava sisältää merkkijonon ”-PR-” ja muussa tapauksessa tyhjä merkkijono.

Nyt kaava tulostaa oikein soluun B1 tekstin ”Ristikytkentä”, kun viereisestä solusta löytyy ”-PR-”. Lopullinen kaava on seuraava: =JOS(JOSVIRHE(ETSI(”-PR-”; A1);0);”Ristikytkentä”;””). Testataan vielä kaavan toimintaa usealla solulla.

Kuten nähdään, kaava toimii suunnitellusti. Mikäli viereisestä solusta puuttuu ”-PR-”, jätetään solu tyhjäksi.

Voisimme toistaa tämän saman arvolle ”-B” ja lisäämällä vastaavan kaavan tekstillä ”Musta” C-sarakkeen soluihin:

Lisätään molemmat kaavat kuitenkin sarakkeeseen B käyttäen KETJUTA -funktiota.

Kaava näyttää monimutkaiselta, mutta siinä on ainoastaan edelliset kaksi kaavaa KETJUTA -funktion arvoina. Lopullinen kaava tulee siis muotoon:

=KETJUTA(JOS(JOSVIRHE(ETSI(”-PR-”;A1);0);”Ristikytkentä ”;””);JOS(JOSVIRHE(ETSI(”-B”;A1);0);”Musta”;””)).

Mikäli sinulla on excel-ongelmia, joihin haluaisit saada vastauksen, ota yhteyttä tällä lomakkeella.

Ei kommentteja, oletko sinä ensimmäinen?

Kommentoi

Sähköpostiosoitettasi ei julkaista.


*