Laske merkit solussa (tai solualueella) Excelin kaavojen avulla

Excelissä on upeita tekstitoimintoja, jotka voivat auttaa sinua työskennellessäsi tekstidatan kanssa.

joissakin tapauksissa joudut ehkä laskemaan solun / alueen merkkien kokonaismäärän tai kuinka monta kertaa tietty merkki esiintyy solussa.

Vaikka LEN-toiminto pystyy laskemaan solujen määrän merkkejä solussa, voit tehdä loput myös kaavojen yhdistelmällä (kuten näemme myöhemmin esimerkeissä).

Tässä opetusohjelmassa käsittelen erilaisia esimerkkejä, joissa voit laskea yhteensä tai tietyt merkit solussa / alueella Excelissä.

Laske kaikki solun merkit

Jos haluat yksinkertaisesti laskea kaikkien solun merkkien kokonaismäärän, voit käyttää LEN-toimintoa.

LEN-funktiossa on yksi argumentti, joka voi olla teksti kaksinkertaisena -lainaukset tai soluviittaus soluun, jossa on teksti.

Oletetaan, että sinulla on esimerkiksi alla oleva kuvan mukainen tietojoukko ja haluat laskea kunkin solun merkkien kokonaismäärän:

Alla on kaava, joka tekee tämän:

=LEN(A2)

Itse LEN-toiminto ei ehkä näytä kovin suurelta, mutta kun yhdistät sen muihin kaavoihin, se voi tehdä hienoja asioita (esimerkiksi saada sanamäärä soluun tai jakaa etu- ja sukunimi).

Huomaa: LEN-toiminto laskee kaikki solun merkit, olipa kyseessä sitten erikoismerkki, numerot, välimerkit ja välilyönnit (etu-, loppu- ja kaksoisvälit sanojen välillä).

Koska LEN funktio laskee kaikki solun merkit, joskus saatat saada väärän tuloksen, jos solussa on ylimääräisiä välilyöntejä.

Esimerkiksi alla olevassa tapauksessa LEN-funktio palauttaa solun tekstille 25 A1, vaikka sen olisi pitänyt olla 22. Mutta koska se laskee myös ylimääräisiä välilyöntejä, sinä saat väärän tuloksen.

Välttääksesi ylimääräisten välilyöntien laskemisen, voit ensin käyttää TRIM-toimintoa poistamaan kaikki johtavat, jäljessä olevat ja kaksinkertaiset välilyönnit ja käytä sitten sen LEN-toimintoa saadaksesi todellisen sanamäärän.

Alla oleva kaava tekee tämän:

=LEN(TRIM(A2))

Laske kaikki solualueen merkit

LEN-funktiota voidaan käyttää myös laskemaan merkkien kokonaismäärä koko alueen.

Oletetaan esimerkiksi, että meillä on sama tietojoukko ja tällä kertaa haluan tietää, kuinka monta merkkiä koko alueella on sen sijaan, että saisimme kussakin solussa merkkien määrän.

Voit tehdä sen käyttämällä seuraavaa kaavaa:

=SUMPRODUCT(LEN(A2:A7)))

Anna minun selittää, miten tämä kaava toimii.

Yllä olevassa kaavassa funktion LEN-osa vie kokonaisen solualueen ja laskee merkit kussakin solussa.

LEN fu: n tulos Nction olisi:

{22; 21; 23; 23; 23; 31}

Jokainen näistä numeroista edustaa solujen merkkimäärä. Ja kun käytät SUMPRODUCT-toimintoa sen kanssa, se yksinkertaisesti lisää kaikki nämä numerot.

Jos ihmettelet, miksi et voi käyttää SUMPRODUCTin sijaan, syynä on, että tämä on matriisi, ja SUMPRODUCT pystyy käsittelemään matriisia, mutta SUM-toiminto ei.

Jos haluat silti käyttää SUM, voit käyttää seuraavaa kaavaa (mutta muista, että sinun on käytettävä Control + Vaihto + Enter saadaksesi tuloksen tavallisen Enterin sijaan)

=SUM(LEN(A2:A7))

Laske solun erityismerkit

Kuten mainitsin, että todellinen apuohjelma LEN-funktion arvo on silloin, kun sitä käytetään yhdessä muiden kaavojen kanssa.

Ja jos haluat laskea solussa tiettyjä merkkejä (se voi olla kirjain, numero, erikoismerkki tai välilyönti), voit tehdä sen kaavayhdistelmällä.

Oletetaan esimerkiksi, että sinulla on tietojoukko kuvan osoittamalla tavalla alla ja haluat laskea sanojen kokonaismäärän kussakin solussa.

Vaikka sanojen määrän saamiseksi ei ole sisäänrakennettua kaavaa, voit laskea välilyönnit ja käyttää sitä sen jälkeen tuntemaan kokonaissumman. solujen sanamäärä.

Alla on kaava, joka antaa sinulle solun välilyöntien kokonaismäärän:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

Yllä oleva kaava laskee välilyöntien kokonaismäärän ja lisää sitten 1 tähän numeroon saadakseen sanamäärän.

Näin tämä kaava toimii:

• SUBSTITUTE-funktiota käytetään korvaamaan kaikki välilyönnit tyhjillä. LEN-toimintoa käytetään sitten laskemaan merkkien kokonaismäärä, kun välilyöntejä ei ole.
• LEN (SUBSTITUTE (A2, ””, ””)) tulos vähennetään sitten LEN: stä ( Tämä antaa meille solussa olevien välilyöntien kokonaismäärän.
• 1 lisätään kaavaan ja sanojen kokonaismäärä olisi yksi enemmän kuin välilyöntien kokonaismäärä (koska kaksi sanaa erotetaan yhdellä merkillä).

Huomaa, että jos on etu-, taka- tai kaksinkertaisia välilyöntejä, saat väärän tuloksen. Tällöin on parasta käyttää TRIM-toimintoa yhdessä LEN-toiminnon kanssa.

Samalla logiikalla voit myös etsiä tietyn merkin, sanan tai lauseen solusta.

Oletetaan, että minulla on esimerkiksi alla oleva tietojoukko, jossa minulla on erilaisia eriä, joissa jokaisella erällä on aakkoset ja numero edustamaan sitä (kuten A1, J2 jne.)

Alla on kaava, joka kertoo, kuinka monta kertaa aakkosella A varustettu erä on luotu kuukaudessa:

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

Yllä oleva kaava käyttää jälleen samaa logiikkaa – etsi tekstin pituus solusta sekä ilman että merkki, jonka haluat laskea ja ottaa sitten näiden kahden ero.

Yllä olevassa kaavassa olen koodannut merkin, jonka haluan laskea, mutta voit myös laittaa sen soluun ja sitten käytä soluviittausta. Tämä tekee siitä kätevämmän, koska kaava päivittyy seuraavan kerran, kun muutat solun tekstiä.

Laske tietyt merkit kirjainkoon merkitsemättömän kaavan avulla

Kaavassa on yksi ongelma käytetään tiettyjen merkkien laskemiseen solussa.

SUBSTITUTE-funktio erottaa kirjainkoon. 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 miten voit Hanki tietyn merkin merkkimäärä, kun se olisi voinut olla joka tapauksessa (alempi tai ylempi).

Teet sen tekemällä kaavasta isot ja pienet kirjaimet. Vaikka voit käyttää monimutkaista kaavaa, lisäsin yksinkertaisesti molempien kirjainten (pienet ja isot A) merkkien määrän.

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))+LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))

Laske merkit / numerot ennen ja jälkeen desimaalin

En tiedä miksi, mutta tämä on yleinen kysely, jonka saan lukijoiltani ja jonka olen nähnyt monilla foorumeilla – kuten tässä.

Oletetaan, että sinulla on alla oleva 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 etu- tai loppunollat tai jos olet käyttänyt mukautettua numeroiden muotoilua näyttämään enemmän / vähemmän numeroita, yllä olevat kaavat antavat silti merkittäviä numeroita ennen desimaalia ja sen jälkeen.

Joten nämä ovat joitain skenaariot, joissa voit laskea kaavoja merkkien merkitsemiseen solussa tai solualueella Excelissä.

Toivottavasti pidit opetusohjelman hyödyllisenä!

Muut ehkä pitämäsi Excel-oppaat:

• Laske yksilölliset arvot Excelissä käyttämällä COUNTIF-funktiota
• kuinka lasketaan soluja, jotka sisältävät tekstimerkkijonoja
• kuinka lasketaan värillisiä soluja Excelissä
• Laske yksilölliset arvot Excelissä COUNTIF-funktiolla
• tekstin poistaminen ennen tiettyä merkkiä tai sen jälkeen Excelissä