Excelil on hämmastavaid tekstifunktsioone, mis aitavad teid tekstiandmetega töötamisel.
Mõnel juhul peate võib -olla arvutama lahtri/vahemiku tähemärkide koguarvu või konkreetse märgi lahtris esinemiste arvu.
Kuigi on olemas funktsioon LEN, mis suudab loendada lahtris olevate märkide arvu, saate ka ülejäänud teha valemite kombinatsiooniga (nagu näeme hiljem näidetes).
Selles õpetuses käsitlen erinevaid näiteid, kus saate Exceli lahtris/vahemikus kokku või kindlaid märke kokku lugeda.
Loendage lahtrisse kõik märgid
Kui soovite lihtsalt saada kõigi lahtri märkide koguarvu, saate kasutada funktsiooni LEN.
Funktsioon LEN võtab ühe argumendi, milleks võib olla topelt jutumärkides olev tekst või lahtri viide lahtrile, millel on tekst.
Oletame näiteks, et teil on andmestik, nagu allpool näidatud, ja soovite lugeda igas lahtris olevate märkide koguarvu:
Allpool on valem, mis seda teeb:
= LEN (A2)
Funktsioon LEN iseenesest ei pruugi tunduda palju, kuid kui ühendate selle teiste valemitega, võib see teha mõningaid imelisi asju (näiteks saada sõnade arv lahtrisse või jagada ees- ja perekonnanimi).
Märkus. Funktsioon LEN loeb lahtrisse kõik märgid, olgu need siis erimärgid, numbrid, kirjavahemärgid ja tühikud (sõnade ees-, lõpp- ja topeltvahed).
Kuna funktsioon LEN loeb lahtrisse iga märgi, võite mõnikord saada vale tulemuse, kui teil on lahtris täiendavaid tühikuid.
Näiteks alloleval juhul tagastab funktsioon LEN lahtris A1 oleva teksti 25, samas kui see oleks pidanud olema 22. Kuid kuna see loeb ka lisatühikuid, saate vale tulemuse.
Täiendavate tühikute loendamise vältimiseks võite esmalt kasutada funktsiooni TRIM, et eemaldada kõik esi-, lõpp- ja topeltruumid ning seejärel kasutada sellel funktsiooni LEN, et saada tõeline sõnade arv.
Allolev valem teeb seda:
= LEN (TRIM (A2))
Loendage kõik rakumärgid
Funktsiooni LEN abil saate lugeda ka kogu vahemiku tähemärkide koguarvu.
Oletame näiteks, et meil on sama andmekogum ja seekord tahan selle asemel, et saada igas lahtris tähemärkide arvu, teada, kui palju neid on kogu vahemikus.
Seda saate teha järgmise valemi abil:
= SUMPRODUCT (LEN (A2: A7)))
Las ma selgitan, kuidas see valem töötab.
Ülaltoodud valemis võtab funktsiooni LEN osa terve hulga lahtreid ja loeb igas lahtris olevad märgid.
Funktsiooni LEN tulemus oleks järgmine:
{22;21;23;23;23;31}
Kõik need numbrid tähistavad lahtris olevate märkide arvu.
Ja kui kasutate sellega funktsiooni SUMPRODUCT, lisab see lihtsalt kõik need numbrid.
Kui te ei tea, miks te ei võiks SUMPRODUCTi asemel kasutada SUM -i, on põhjus selles, et see on massiiv ja SUMPRODUCT saab massiiviga hakkama, kuid SUM -funktsioon ei saa.
Kui soovite siiski kasutada summat, võite kasutada järgmist valemit (kuid pidage meeles, et tulemuse saamiseks peate tavalise sisestusklahvi asemel kasutama klahvikombinatsiooni Control + tõstuklahv + sisestusklahv).
= SUMMA (LEN (A2: A7))
Loendage lahtris konkreetseid märke
Nagu mainisin, on LEN -funktsiooni tõeline kasulikkus see, kui seda kasutatakse koos teiste valemitega.
Ja kui soovite lahtrisse lugeda konkreetseid märke (see võib olla täht, number, erimärk või tühik), saate seda teha valemikombinatsiooniga.
Oletame näiteks, et teil on allpool näidatud andmekogum ja soovite loendada igas lahtris olevate sõnade koguarvu.
Ehkki sõnade arvu saamiseks pole sisseehitatud valemit, saate tühikutähed kokku lugeda ja seejärel selle abil teada saada lahtris olevate sõnade koguarvu.
Allpool on valem, mis annab teile lahtris tühikute koguarvu:
= LEN (A2) -LEN (ASENDUS (A2, "", ""))+1
Ülaltoodud valem loeb tühikute koguarvu ja lisab sellele numbrile 1, et saada sõnade arv.
See valem töötab järgmiselt.
- Funktsiooni SUBSTITUTE kasutatakse kõigi tühikute asendamiseks tühikuga. Funktsiooni LEN kasutatakse siis tähemärkide koguarvu loendamiseks, kui tühikuid pole.
- Seejärel lahutatakse LEN -ist (A2) LEN -i (asendaja (A2, ”“, ””)) tulemus. See annab meile lahtris olevate tühimärkide koguarvu.
- Valemile lisatakse 1 ja sõnade koguarv oleks üks rohkem kui tühimärkide koguarv (kuna kaks sõna on eraldatud ühe märgiga).
Pange tähele, et kui on ees-, lõpp- või topeltruume, saate vale tulemuse. Sellisel juhul on parem kasutada funktsiooni TRIM koos LEN -funktsiooniga.
Sama loogikat saate kasutada ka lahtrist konkreetse märgi või sõna või fraasi leidmiseks.
Oletame näiteks, et mul on allpool näidatud andmekogum, kus mul on erinevad partiid, kus igal partiil on selle tähistamiseks tähestik ja number (näiteks A1, J2 jne).
Allpool on valem, mis näitab teile, mitu korda on A -tähega partii iga kuu loodud:
= LEN (B2) -LEN (ASENDUS (B2, "A", ""))
Ülaltoodud valem kasutab taas sama loogikat - leidke lahtris oleva teksti pikkus koos selle tähemärgiga ja ilma selleta, mida soovite lugeda, ja seejärel võtke nende kahe erinevus.
Ülaltoodud valemis olen märgi, mida tahan loendada, kõvasti kodeerida, kuid võite selle ka lahtrisse panna ja seejärel kasutada lahtri viidet. See muudab selle mugavamaks, kuna valemit värskendatakse järgmisel korral, kui muudate lahtris olevat teksti.
Loendage tähemärke, kasutades tõstutundlikku valemit
Lahtris konkreetsete märkide loendamiseks kasutatava valemiga on üks probleem.
Funktsioon SUBSTITUTE on tõstutundlik. See tähendab, et teie “A” ei võrdu “a” -ga. Sellepärast saate lahtris C5 vale tulemuse (tulemus oleks pidanud olema 3).
Niisiis, kuidas saada konkreetse tegelase tähemärkide arv, kui see oleks võinud igal juhul olla (alumine või ülemine).
Teete seda, muutes oma valemit tõstutundetuks. Kuigi võite valida keerulise valemi, lisasin lihtsalt mõlema juhtumi (väiketähed ja suured tähed A) tähemärkide arvu.
= LEN (B2) -LEN (ASENDUS (B2, "A", ""))+LEN (B2) -LEN (ASENDUS (B2, "a", ""))
Loe tähemärke/numbreid enne ja pärast kümnendkohta
Ma ei tea miks, aga see on tavaline päring, mille saan oma lugejatelt ja mida olen näinud paljudel foorumitel - näiteks sellel
Oletame, et teil on allpool näidatud andmekogum ja soovite lugeda tähemärke enne koma ja pärast koma.
Allpool on valemid, mis seda teevad.
Lugege tähemärke/numbreid enne koma:
= LEN (INT (A2))
Lugege tähemärke/numbreid pärast koma:
= LEN (A2) -FIND (".", A2)
Pange tähele, et need valemid on mõeldud ainult lahtri oluliste numbrite jaoks. Kui teil on ees- või lõppnullid või olete kasutanud kohandatud numbrivormingut, et kuvada rohkem/vähem numbreid, annaksid ülaltoodud valemid teile olulisi numbreid enne ja pärast koma.
Nii et need on mõned stsenaariumid, mille abil saate Exceli lahtris või lahtrivahemikus olevate märkide lugemiseks valemite abil.
Loodan, et õpetus oli teile kasulik!
