Excelissä on hämmästyttäviä tekstitoimintoja, jotka voivat auttaa sinua käsitellessäsi tekstitietoja.
Joissakin tapauksissa sinun on ehkä laskettava solun/alueen merkkien kokonaismäärä tai kuinka monta kertaa tietty merkki esiintyy solussa.
Vaikka on olemassa LEN -toiminto, joka voi laskea solujen merkkien määrän, voit tehdä loput myös kaavojen yhdistelmällä (kuten näemme myöhemmin esimerkeissä).
Tässä opetusohjelmassa käsitän erilaisia esimerkkejä, joissa voit laskea Excelin solun/alueen kokonaismerkit tai tietyt merkit.
Laske kaikki solun merkit
Jos haluat yksinkertaisesti saada kaikkien solun merkkien kokonaismäärän, voit käyttää LEN -toimintoa.
LEN-funktio sisältää yhden argumentin, joka voi olla teksti lainausmerkeissä tai soluviittaus soluun, jossa on teksti.
Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat laskea merkkien kokonaismäärän kussakin solussa:
Alla on kaava, jolla tämä tehdään:
= LEN (A2)
Itse LEN -toiminto ei ehkä näytä paljon, mutta kun yhdistät sen muihin kaavoihin, se voi tehdä hienoja asioita (kuten saada sanamäärän soluun tai jakaa etu- ja sukunimen).
Huomautus: LEN -toiminto laskee kaikki solun merkit, olivatpa ne erikoismerkkejä, numeroita, välimerkkejä ja välilyöntejä (alku-, loppu- ja kaksoisvälit sanojen välillä).
Koska LEN -toiminto laskee solun kaikki merkit, saatat joskus saada väärän tuloksen, jos solussa on ylimääräisiä välilyöntejä.
Esimerkiksi alla olevassa tapauksessa LEN -funktio palauttaa 25 solun A1 tekstille, kun sen olisi pitänyt olla 22. Mutta koska se laskee myös ylimääräisiä välilyöntejä, saat väärän tuloksen.
Voit välttää ylimääräisten välilyönnien laskemisen poistamalla TRIM -toiminnolla ensin kaikki edessä, takana ja kaksinkertaiset välilyönnit ja käyttämällä sitten LEN -toimintoa todellisen sanamäärän saamiseksi.
Alla oleva kaava tekee tämän:
= LEN (TRIM (A2))
Laske kaikki hahmot solualueella
Voit myös käyttää LEN -toimintoa kokonaisalueen merkkien kokonaismäärän laskemiseen.
Oletetaan esimerkiksi, että meillä on sama tietojoukko, ja tällä kertaa haluan tietää, kuinka monta merkkiä on koko alueella, sen sijaan, että saisimme merkkien määrän kussakin solussa.
Voit tehdä sen käyttämällä alla olevaa kaavaa:
= SUMPRODUCT (LEN (A2: A7)))
Selitän kuinka tämä kaava toimii.
Yllä olevassa kaavassa funktion LEN -osa ottaa koko solualueen ja laskee kunkin solun merkit.
LEN -toiminnon tulos olisi:
{22;21;23;23;23;31}
Jokainen näistä numeroista edustaa solujen merkkien määrää.
Ja kun käytät SUMPRODUCT -toimintoa sen kanssa, se yksinkertaisesti lisää kaikki nämä numerot.
Jos mietit, miksi et voi käyttää SUM: ta SUMPRODUCTin sijaan, syy on se, että tämä on taulukko ja SUMPRODUCT pystyy käsittelemään matriisia, mutta SUM -toiminto ei.
Jos kuitenkin haluat edelleen käyttää SUM, voit käyttää alla olevaa kaavaa (mutta muista, että sinun on käytettävä Control + Vaihto + Enter saadaksesi tuloksen tavallisen Enterin sijaan)
= SUMMA (LEN (A2: A7))
Laske soluun tietyt merkit
Kuten mainitsin, LEN -toiminnon todellinen hyödyllisyys on, kun sitä käytetään yhdessä muiden kaavojen kanssa.
Ja jos haluat laskea tietyt merkit solussa (se voi olla kirjain, numero, erikoismerkki tai välilyönti), voit tehdä sen kaavayhdistelmällä.
Oletetaan esimerkiksi, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat laskea sanojen kokonaismäärän kussakin solussa.
Vaikka sanamäärän saamiseen ei ole sisäänrakennettua kaavaa, voit laskea välilyönnit ja sen avulla tietää solun sanojen kokonaismäärän.
Alla on kaava, joka antaa sinulle solun välilyönnien kokonaismäärän:
= LEN (A2) -LEN (VAIHTOEHTO (A2, "", ""))+1
Yllä oleva kaava laskee välilyönnien kokonaismäärän ja lisää sitten 1 numeroon saadakseen sanamäärän.
Näin tämä kaava toimii:
- SUBSTITUTE -toimintoa käytetään korvaamaan kaikki välilyönnit tyhjillä. LEN -toimintoa käytetään sitten merkkien kokonaismäärän laskemiseen, kun välilyöntejä ei ole.
- Linssin tulos (VAIHTOEHTO (A2, ””, ””))) vähennetään sitten linssistä (A2). Tämä antaa meille solussa olevien välilyönnien kokonaismäärän.
- Kaavaan lisätään 1 ja sanojen kokonaismäärä olisi yksi enemmän kuin välilyönnien kokonaismäärä (kaksi sanaa erotetaan yhdellä merkillä).
Huomaa, että jos edessä, takana tai kaksinkertaisia välilyöntejä on, saat väärän tuloksen. Tällaisessa tapauksessa on parasta käyttää TRIM -toimintoa yhdessä LEN -toiminnon kanssa.
Voit myös käyttää samaa logiikkaa löytääksesi tietyn merkin tai sanan tai lauseen solusta.
Oletetaan esimerkiksi, että minulla on alla esitetyn kaltainen tietojoukko, jossa minulla on eri erät, ja jokaisessa erässä on aakkoset ja numerot, jotka edustavat sitä (kuten A1, J2 jne.)
Alla on kaava, joka kertoo, kuinka monta kertaa erä, jossa on aakkoset A, on luotu kuukausittain:
= LEN (B2) -LEN (KORVAUS (B2, "A", ""))
Yllä oleva kaava käyttää jälleen samaa logiikkaa - etsi tekstin pituus solusta ja ilman merkkiä, jonka haluat laskea, ja ota sitten näiden kahden ero.
Yllä olevassa kaavassa olen kovakoodannut merkin, jonka haluan laskea, mutta voit myös laittaa sen soluun ja käyttää sitten soluviittausta. Tämä tekee siitä helpompaa, koska kaava päivittyy seuraavan kerran, kun muutat solun tekstiä.
Laske erikoismerkit kirjainkokoherkän kaavan avulla
Solun tiettyjen merkkien laskemiseen käytetyssä kaavassa on yksi ongelma.
SUBSTITUTE -toiminto erottaa isot ja pienet kirjaimet. Tämä tarkoittaa, että sinä "A" ei ole sama kuin "a". Siksi saat väärän tuloksen solussa C5 (tuloksen olisi pitänyt olla 3).
Joten kuinka voit saada tietyn merkin merkkimäärä, kun se olisi voinut olla joka tapauksessa (alempi tai ylempi).
Voit tehdä sen tekemällä kaavan kirjainkokosta eron. Vaikka voit käyttää monimutkaista kaavaa, lisäsin yksinkertaisesti molempien tapausten merkkien määrän (pienet ja isot kirjaimet A).
= LEN (B2) -LEN (KORVA (B2, "A", ""))+LEN (B2) -LEN (SISÄLTÖ (B2, "a", ""))
Laske merkit/numerot ennen desimaalia ja sen jälkeen
En tiedä miksi, mutta tämä on yleinen kysely, jonka saan lukijoiltani ja olen nähnyt monilla foorumeilla - kuten tällä
Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat laskea merkit ennen desimaalia ja desimaalin jälkeen.
Alla on kaavat, jotka tekevät tämän.
Laske merkit/numerot ennen desimaalia:
= LEN (INT (A2))
Laske merkit/numerot desimaalin jälkeen:
= LEN (A2) -FIND (".", A2)
Huomaa, että nämä kaavat on tarkoitettu vain solun merkittäville numeroille. Jos sinulla on nolla edessä tai lopussa tai olet käyttänyt mukautettua lukumuotoa näyttääksesi enemmän/vähemmän numeroita, yllä olevat kaavat antaisivat sinulle silti merkittäviä numeroita ennen ja jälkeen desimaalin.
Nämä ovat siis joitakin skenaarioita, joissa voit laskea kaavoja solun tai solualueiden merkkien laskemiseksi Excelissä.
Toivottavasti pidit opetusohjelmaa hyödyllisenä!
