Etter denne opplæringen vil du kunne telle totalt antall tegn i en celle eller et område, forekomster av spesifikk karakter eller tegnkombinasjon i en celle eller et område med begge store og små bokstaver og ufølsomme alternativer. Kunnskap oppnådd i denne opplæringen er en veldig god base for videre kreativ Excel-problemløsning.

Hvordan telle tegn i Excel

Hvis du vil lære å telle tegn i Excel, du må bruke funksjon LEN, ved å bruke formel = LEN (celle) for å telle totalt antall tegn i en celle, eller en kombinasjon av funksjoner SUMPRODUCT og LEN for å telle totalt antall tegn i et område med formel = SUMPRODUCT (LEN (område)). I tillegg vil tellering av et bestemt tegn i en celle eller et område og en spesifikk kombinasjon av tegn i en celle eller et område bli forklart i detalj.

1. Telle totalt antall tegn i en celle
2. Telle totalt antall tegn i et område
3. Telle spesifikt tegn i en celle
4. Antall Spesifikt tegn i et område
5. Antall Spesifikk kombinasjon av tegn i en celle eller område

Antall totalt antall tegn i en celle

Totalt antall tegn i et cellen kan enkelt bli funnet ved hjelp av Excel-funksjonen LEN. Denne funksjonen har bare ett argument, cellereferanse eller tekst, der det totale tegntallet vil telles:

=LEN(text)

=LEN(B3)

Denne funksjonen teller totalt antall tegn i en celle, inkludert mellomrom, skilletegn, symboler, til tross for hvor mange ganger de forekommer i en streng.

Hvis vi vil telle totalt antall tegn i en celle, unntatt mellomrom, vil kombinasjonen av formler LENN og SUBSTITUT kreves. La oss se på eksemplet nedenfor, formelen som ekskluderer mellomrom ser slik ut:

=LEN(SUBSTITUTE(B3;" ";""))

SUBSTITUTE-funksjonen endres i en definert celle ett tegn / tekst med et annet tegn / tekst. Hvis vi vil ekskludere mellomrom ved å bruke denne funksjonen, vil de bli eliminert, og erstatte alle forekomster av mellomrom med en tom streng. Etter SUBSTITUTE vil funksjonstekst i en celle se slik ut:

= LEN («Sunisshining»)

Etter det er det enkelt å telle totalt antall tegn med LEN-funksjon, med den endelige resultat på 12 tegn.

Antall tegn totalt i et område

For å telle totalt tegnnummer i et definert område, er kombinasjonen av to funksjoner nødvendig, SUMPRODUCT og LENGE. SUMPRODUCT-funksjonsbruk er en elegant løsning når vi har å gjøre med flere celler eller matriser. Ta en titt i eksemplet nedenfor og en kombinasjon av formlene:

=SUMPRODUCT(LEN(range))

=SUMPRODUCT(LEN(B3:B6))

LEN-funksjon er relatert til celle, men for bruk av summerte LEN-funksjoner (= LEN (B3) + LEN (B4) + LEN (B5) + LEN (B6)) er ikke den beste løsningen, siden vi kan håndtere store områder. I stedet for det oppsummerer SUMPRODUCT-funksjonen resultater av LEN-funksjon i det definerte området. Ved formelevaluering vil resultatet se ut som:

Tall fra matrisen er LEN-funksjonsresultater fra hver celle i det definerte området B3: B6, noe som gir sluttresultat på 75.

Den alternative løsningen for å håndtere områder er bruken av kombinasjon av SUM og LEN. Syntaksen er nesten den samme, med den eneste forskjellen i bruk av krøllete parenteser, for å konvertere SUM-funksjon i matriseformel (Snarvei for å lage en matrise er CTRL + SKIFT + ENTER). I formelen nedenfor er den eksakte syntaksen for SUM / LEN-funksjonskombinasjon:

={SUM(LEN(B3:B6))}

Count Specific Character in a Cell

I tillegg til det totale tegnetallet, er det også muligheten i Excel for å telle antall forekomster av spesifikke tegn. La oss gå gjennom eksemplet med å telle antall enkelttegn i en bestemt celle. For dette formålet er det nødvendig med kombinasjon av LEN og SUBSTITUTE-funksjon, som vi gjorde i det lignende eksemplet med å telle antall tegn i cellen uten mellomrom. Formelsyntaks vil se slik ut:

=LEN(cell)-LEN(SUBSTITUTE(cell;character;""))

I et spesifikt eksempel, hvis vi vil telle antall tegn s i en definert celle B3, vil formelen se slik ut:

=LEN(B3)-LEN(SUBSTITUTE(B3;"s";""))

La oss forklare kort logikken til funksjonskombinasjonen. Totalt tegntall i en celle B3 blir trukket med tegnnummer i samme celle, men uten spesifikt tegn som vi vil telle. Som nevnt i opplæringspunkt 1, brukes SUBSTITUTE-funksjonen til å endre en streng i en definert celle i form uten spesifikt tegn, og erstatte det tegnet med en tom streng.

=SUBSTITUTE(cell;"character";"")

Det kan i det endelige resultatet bli lagt merke til at funksjonen ikke teller store bokstaver siden LEN-funksjonen er mellom store og små bokstaver. Løsningen for å telle tegn uten store og små bokstaver er bruken av OPPER / LOWER-funksjonen, der alle tegn blir oversatt til store / små bokstaver, og funksjonen blir ikke store og små bokstaver.

I eksemplet nedenfor, funksjon LOWER er nestet i SUBSTITUTE-funksjonen, og endrer all streng i celle B3 til små bokstaver, siden kriteriene er definert som små bokstaver, «s»:

=LEN(cell)-LEN(SUBSTITUTE(LOWER(cell);"lowercase character";""))

=LEN(B3)-LEN(SUBSTITUTE(LOWER(B3);"s";""))

En annen løsning for å lage ikke-store bokstaver bruken av dobbelt nestet SUBSTITUTE-funksjon i kombinasjon med LEN-funksjon. I det videre eksemplet vil telletegnet være i en bestemt celle, for noen ganger er det ikke praktisk å skrive hver gang for å telle et tegn i formelen, spesielt hvis du har å gjøre med komplekse områder og formler.

Formel med dobbelt nestet SUBSTITUTE-funksjon:

=LEN(cell)-LEN(SUBSTITUTE(SUBSTITUTE(cell;LOWER(character);"");UPPER(character);""))

=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

Formelen ser kanskje kompleks ut, men alt vil være klart etter forklaring. La oss evaluere formelen trinn for trinn.

Først vil vi eliminere telletegnet for små bokstaver fra teksten:

=SUBSTITUTE(B3;LOWER(B6);"")

=SUBSTITUTE("Sun is shining ”;LOWER("S”);"")

Funksjon LOWER settes for å oversette telletegn til små bokstaver, og deretter erstatter + SUBSTITUTE-funksjonen små bokstaver i en tom streng. Etter at dette trinnformelresultatet ser ut: «Sun i hining».

I neste formeltrinn er målet å eliminere det store tellertegnet fra den definerte teksten / cellen. Dette løses av nestet SUBSTITUTE-funksjon :

=SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")

Vi har allerede forklart i SUBSTITUTE funksjonsresultatet «Sun i hining», og vil sette det i funksjonen nedenfor for å gjøre situasjonen klarere. I den første SUBSTITUTE-funksjonen er liten bokstav erstattet med en tom streng, og i den andre SUBSTITUTE-funksjonen blir store tellertegn erstattet med en tom streng, noe som resulterer i teksten uten å telle tegn «s» og «S»: «un i hining» .

=SUBSTITUTE("Sun i hining”;UPPER("S”);"")

LEN-funksjon teller så bare antall tegn fra en modifisert tekst:

=LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

=LEN("un i hining”)

I det siste trinnet, evaluert formelresultat, tekst uten «s» og «S», blir trukket av det totale antallet tegn i en definert celle:

=LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);""))

=LEN("Sun is shining”) - LEN("un i hining”)

Count Specific Character in a Range

Når vi har å gjøre med celleområder og matriser, er SUMPRODUCT-funksjonen nødvendig i kombinasjon med andre funksjoner. For å telle den spesifikke tegn i et område, for store og små bokstaver, sensitiv telling, brukes kombinasjonen av tre funksjoner: SUMPRODUCT, LENGE og SUBSTITUTE. For store og små bokstaver bør det legges til flere formler: UPPER / LOWER.

Først vil vi forklare telle antall forekomst av tegnet «S» i et definert område (versjonsavviksfølsom versjon). En formel er nesten lik formelen som forklarer telletegn. Forskjellen kommer fra LEN-funksjon nestet i SUMPRODUCT-funksjonen og i telleområdet, i stedet for cellen er definert celleområde:

=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;"")))

For store og små bokstaver i et definert område, kan vi bruke den detaljerte forklarte funksjonen for store og små bokstaver -følsom karaktertelling i en bestemt celle i opplæringspunkt 3, med to endringer: nesting av LEN-funksjon i SUMPRODUCT-funksjon, og erstatning av cellen med celleområde:

=SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);"")))

Husk at når du har med celleområder å gjøre, må du bruke SUMPRODUCT-funksjonen. For store og små bokstaver teller i ra Det kreves en kombinasjon av funksjoner: SUMPRODUCT, LENN og SUBSTITUTE, og for store og små bokstaver: SUMPRODUCT, LEN, SUBSTITUTE og UPPER / LOWER-funksjoner.

Count Specific Combination of Characters in a Cell or Range

Det er også muligheten til å telle spesifikk tegnkombinasjon i en definert celle eller et definert område. I tidligere opplæringspunkter dekket vi telling av enkelttegn i en celle eller et område med store og små bokstaver. Formelen for å telle kombinasjonen av tegn er den samme, bare vi må dele den med antall tegn i tegnkombinasjonen.

La oss ta en titt i eksemplet nedenfor, for å telle spesifikk kombinasjon av tegn i en celle (versjonsfølsom versjon):

=(LEN(B3)-LEN(SUBSTITUTE(SUBSTITUTE(B3;LOWER(B6);"");UPPER(B6);"")))/LEN(B6)

Logikken er den samme som for å telle enkelt tegn, bare vi måtte dele den vanlige formelen med antall spesifikke tegn som vi teller, og bruker lett formelen: LEN («in»). Uten å dele formelen med LEN («in»), vil resultatet bli multiplisert med antall tegn i tegnkombinasjon (i vårt eksempel med 2, siden «i ”Har to tegn)

For å telle spesifikk kombinasjon av tegn i en celle (versjon av store og små bokstaver) vil formelen se slik ut:

=(LEN(B3)-LEN(SUBSTITUTE(B3;B6;"")))/LEN(B6)

Tellingskombinasjon av tegn i et område har samme logikk som å telle enkelt tegn, og for tilfellet vil sensitiv versjonsformel se ut som:

=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(SUBSTITUTE(B3:B6;LOWER(B9);"");UPPER(B9);""))))/LEN(B9)

Vanlig formel forklart i tu torials tema 4, er delt med antall spesifikke tegn som vi teller, ved hjelp av funksjon LEN.

Hvis vi vil ha den store og små bokstaver, vil formelsyntaks se ut som:

=(SUMPRODUCT(LEN(B3:B6))-SUMPRODUCT(LEN(SUBSTITUTE(B3:B6;B9;""))))/LEN(B9)

Fortsatt trenger du hjelp med Excel-formatering eller har andre spørsmål om Excel? Kontakt en live Excel-ekspert her for noen 1 til 1 hjelp. Din første økt er alltid gratis.