Programmā Excel ir dažas pārsteidzošas teksta funkcijas, kas var jums palīdzēt, strādājot ar teksta datiem.
Dažos gadījumos jums, iespējams, būs jāaprēķina kopējais rakstzīmju skaits šūnā/diapazonā vai tas, cik reizes konkrēta rakstzīme parādās šūnā.
Lai gan ir funkcija LEN, kas šūnā var saskaitīt rakstzīmju skaitu, pārējo varat veikt arī ar formulu kombināciju (kā mēs redzēsim vēlāk piemēros).
Šajā apmācībā es apskatīšu dažādus piemērus, kur programmā Excel šūnā/diapazonā varat saskaitīt kopējās vai konkrētās rakstzīmes.
Šūnā saskaitiet visas rakstzīmes
Ja jūs vienkārši vēlaties iegūt kopējo visu šūnu rakstzīmju skaitu, varat izmantot funkciju LEN.
Funkcijai LEN ir viens arguments, kas varētu būt teksts ar pēdiņām vai šūnas atsauce uz šūnu, kurā ir teksts.
Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties saskaitīt kopējo rakstzīmju skaitu katrā šūnā:
Zemāk ir formula, kas to darīs:
= LEN (A2)
LEN funkcija pati par sevi var neizskatīties daudz, taču, apvienojot to ar citām formulām, tā var paveikt dažas brīnišķīgas lietas (piemēram, iegūt vārdu skaitu šūnā vai sadalīt vārdu un uzvārdu).
Piezīme. Funkcija LEN saskaitīs visas šūnā esošās rakstzīmes, neatkarīgi no tā, vai tās ir īpašas rakstzīmes, cipari, pieturzīmes un atstarpes zīmes (sākuma, beigu un dubultās atstarpes starp vārdiem).
Tā kā funkcija LEN saskaita visas šūnas rakstzīmes, dažkārt var rasties nepareizs rezultāts, ja šūnā ir papildu atstarpes.
Piemēram, tālāk minētajā gadījumā funkcija LEN atgriež 25 šūnā A1 esošajam tekstam, bet tam vajadzēja būt 22. Bet, tā kā tiek skaitītas arī papildu atstarpes rakstzīmes, tiek iegūts nepareizs rezultāts.
Lai izvairītos no papildu atstarpju uzskaitīšanas, vispirms varat izmantot funkciju TRIM, lai noņemtu visus priekšējos, pēdējos un dubultos atstarpes, un pēc tam izmantot LEN funkciju, lai iegūtu īsto vārdu skaitu.
Zemāk esošā formula to darīs:
= LEN (TRIM (A2))
Saskaitiet visas rakstzīmes šūnu diapazonā
Varat arī izmantot funkciju LEN, lai saskaitītu kopējo rakstzīmju skaitu visā diapazonā.
Piemēram, pieņemsim, ka mums ir vienāda datu kopa, un šoreiz tā vietā, lai iegūtu rakstzīmju skaitu katrā šūnā, es vēlos uzzināt, cik to ir visā diapazonā.
To var izdarīt, izmantojot zemāk esošo formulu:
= SUMPRODUCT (LEN (A2: A7)))
Ļaujiet man paskaidrot, kā šī formula darbojas.
Iepriekš minētajā formulā funkcijas LEN daļa aizņem visu šūnu diapazonu un saskaita rakstzīmes katrā šūnā.
Funkcijas LEN rezultāts būtu šāds:
{22;21;23;23;23;31}
Katrs no šiem skaitļiem norāda rakstzīmju skaitu šūnā.
Un, ja kopā ar to izmantojat funkciju SUMPRODUCT, tas vienkārši pievienos visus šos skaitļus.
Tagad, ja jums rodas jautājums, kāpēc jūs nevarat izmantot SUM, nevis SUMPRODUCT, iemesls ir tas, ka tas ir masīvs, un SUMPRODUCT var apstrādāt masīvu, bet SUM funkcija to nevar.
Tomēr, ja jūs joprojām vēlaties izmantot SUM, varat izmantot zemāk esošo formulu (taču atcerieties, ka, lai iegūtu rezultātu, nevis parasto ievadīšanas taustiņu, jums ir jāizmanto Control + Shift + Enter)
= SUMMA (LEN (A2: A7))
Šūnā saskaitiet īpašas rakstzīmes
Kā jau minēju, LEN funkcijas patiesā lietderība ir tad, ja to lieto kombinācijā ar citām formulām.
Un, ja vēlaties šūnā saskaitīt konkrētas rakstzīmes (tas var būt burts, cipars, īpašā rakstzīme vai atstarpes zīme), varat to izdarīt, izmantojot formulu kombināciju.
Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties saskaitīt kopējo vārdu skaitu katrā šūnā.
Lai gan nav iebūvētas formulas vārdu skaita noteikšanai, varat saskaitīt atstarpes rakstzīmes un pēc tam izmantot, lai uzzinātu kopējo vārdu skaitu šūnā.
Zemāk ir formula, kas parādīs kopējo atstarpes rakstzīmju skaitu šūnā:
= LEN (A2) -LEN (AIZSTĀJIS (A2, "", ""))+1
Iepriekš minētā formula saskaita kopējo atstarpju rakstzīmju skaitu un pēc tam šim skaitlim pievieno 1, lai iegūtu vārdu skaitu.
Šī formula darbojas šādi.
- Funkciju SUBSTITUTE izmanto, lai visas atstarpes rakstzīmes aizstātu ar tukšu. Funkcija LEN tiek izmantota, lai saskaitītu kopējo rakstzīmju skaitu, ja nav atstarpes rakstzīmju.
- LEN rezultāts (AIZSTĀJIS (A2, ”,“ ”))) tiek atņemts no LEN (A2). Tas dod mums kopējo šūnu rakstzīmju skaitu šūnā.
- Formulā tiek pievienots 1, un kopējais vārdu skaits būtu par vienu vairāk nekā kopējais atstarpes rakstzīmju skaits (jo divi vārdi ir atdalīti ar vienu rakstzīmi).
Ņemiet vērā, ka gadījumā, ja ir priekšējās, beigu vai dubultās atstarpes, jūs iegūsit nepareizu rezultātu. Šādā gadījumā vislabāk ir izmantot TRIM funkciju kopā ar LEN funkciju.
Varat arī izmantot to pašu loģiku, lai šūnā atrastu noteiktu rakstzīmi vai vārdu vai frāzi.
Piemēram, pieņemsim, ka man ir datu kopa, kā parādīts zemāk, kur man ir dažādas partijas, kur katrai partijai ir alfabēts un cipars, kas to attēlo (piemēram, A1, J2 utt.)
Zemāk ir formula, kas parādīs kopējo reižu skaitu, kad katru mēnesi ir izveidota partija ar alfabētu A.
= LEN (B2) -LEN (AIZSTĀJIS (B2, "A", ""))
Iepriekšminētā formula atkal izmanto to pašu loģiku - atrodiet teksta garumu šūnā ar un bez rakstzīmes, kuru vēlaties saskaitīt, un pēc tam ņemiet vērā šo divu atšķirību.
Iepriekš minētajā formulā es esmu kodējis rakstzīmi, kuru vēlos saskaitīt, taču varat to ievietot šūnā un pēc tam izmantot šūnas atsauci. Tas padara to ērtāku, jo formula tiks atjaunināta, nākamreiz mainot tekstu šūnā.
Skaitiet īpašas rakstzīmes, izmantojot formulu, kas nejūt reģistrjutīgu
Pastāv viena problēma ar formulu, ko izmanto, lai šūnā saskaitītu konkrētas rakstzīmes.
Funkcija SUBSTITUTE ir reģistrjutīga. Tas nozīmē, ka jūs “A” nav vienāds ar “a”. Tāpēc šūnā C5 tiek iegūts nepareizs rezultāts (rezultātam vajadzēja būt 3).
Tātad, kā jūs varat iegūt konkrētas rakstzīmes rakstzīmju skaitu, ja tas varēja būt jebkurā gadījumā (apakšējā vai augšējā).
Jūs to darāt, padarot formulas nejutīgo. Lai gan jūs varat izmantot sarežģītu formulu, es vienkārši pievienoju abu gadījumu rakstzīmju skaitu (mazie un lielie A).
= LEN (B2) -LEN (AIZSTĀJIS (B2, "A", ""))+LEN (B2) -LEN (AIZSTĀJĪTĀJS (B2, "a", ""))
Skaitīt rakstzīmes/ciparus pirms un pēc decimāldaļas
Es nezinu, kāpēc, bet tas ir bieži uzdots jautājums no maniem lasītājiem un esmu redzējis daudzos forumos, piemēram, šajā
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties saskaitīt rakstzīmes pirms komata un aiz komata.
Zemāk ir formulas, kas to darīs.
Skaitīt rakstzīmes/ciparus pirms komata:
= LEN (INT (A2))
Skaitīt rakstzīmes/ciparus aiz komata:
= LEN (A2) -FIND (".", A2)
Ņemiet vērā, ka šīs formulas ir paredzētas tikai nozīmīgiem šūnas cipariem. Ja jums ir sākuma vai beigu nulle vai esat izmantojis pielāgotu skaitļu formatējumu, lai parādītu vairāk/mazāk skaitļu, iepriekš minētās formulas jums dotu nozīmīgus ciparus pirms un aiz komata.
Tātad šie ir daži no scenārijiem, kuros varat izmantot formulas, lai saskaitītu rakstzīmes šūnā vai šūnu diapazonā programmā Excel.
Es ceru, ka apmācība jums šķita noderīga!
