Merkkien laskeminen Excelissä: solujen / alueiden kokonaismerkit tai vain tietyt merkit

Opetusohjelmassa kerrotaan merkkien laskemisesta Excelissä. Opit kaavat saadaksesi merkkien kokonaislukumäärän alueelle ja laskeaksesi vain tietyt merkit solussa tai useissa soluissa.

Edellisessä opetusohjelmassa esiteltiin Excel LEN -toiminto, jonka avulla voidaan laskea kokonaismäärä merkkiä solussa.

LEN-kaava on itsestään hyödyllinen, mutta yhdessä muiden toimintojen, kuten SUM, SUMPRODUCT ja SUBSTITUT, kanssa se pystyy käsittelemään paljon monimutkaisempia tehtäviä. Tässä opetusohjelmassa tarkastelemme tarkemmin muutamia perus- ja edistyneitä kaavoja merkkien laskemiseksi Excelissä.

  • Laske merkkien kokonaismäärä alueella
  • Laske tietyt merkit soluun
  • Laske tietyt merkit alueella

Kuinka lasketaan kaikki alueen merkit

Kun se tulee laskea merkkien kokonaismäärä useissa soluissa, välitön ratkaisu, joka tulee mieleen, on saada jokaisen solun merkkimäärä ja laskea yhteen nämä numerot:

=LEN(A2)+LEN(A3)+LEN(A4)

Tai

=SUM(LEN(A2),LEN(A3),LEN(A4))

Yllä olevat kaavat voivat toimia hyvin pienellä alueella. Jos haluat laskea kokonaismerkkejä suuremmalle alueelle, keksimme paremmin jotain kompaktimpaa, esimerkiksi SUMPRODUCT-funktion, joka kertoo matriisit ja palauttaa tuotteiden summan.

Tässä on yleinen Excel kaava merkkien laskemiseksi alueella:

= SUMPRODUCT (LEN (alue))

Ja tosielämän kaava voi näyttää samanlaiselta kuin tämä:

=SUMPRODUCT(LEN(A1:A7))

Toinen tapa laskea merkkejä alueella on käyttää LEN-toimintoa yhdessä SUMMA:

=SUM(LEN(A1:A7))

Toisin kuin SUMPRODUCT, SUM-toiminto ei laske taulukoita oletusarvoisesti, ja sinun on painettava Ctrl + Vaihto + Enter-näppäimellä voit muuttaa siitä taulukon kaavan.

Kuten seuraavassa kuvakaappauksessa osoitetaan, SUM-kaava palauttaa saman merkkimäärän:

Kuinka tämä alueiden merkkilaskukaava toimii

Tämä on yksi suoraviivaisimmista kaavoista merkkien laskemiseksi Excelissä. LEN-toiminto laskee merkkijonon pituuden kullekin määritetyn alueen solulle ja palauttaa sen numeroryhmänä. Sitten SUMPRODUCT tai SUM laskee yhteen nämä numerot ja palauttaa merkkien kokonaismäärän.

Yllä olevassa esimerkissä 7 numeron taulukko, joka edustaa solujen A1 – A7 merkkijonojen pituuksia, summataan:

Huomautus. Huomaa, että Excel LEN -toiminto laskee ehdottomasti kaikki merkit kussakin solussa, mukaan lukien kirjaimet, numerot, välimerkit, erikoissymbolit ja kaikki välilyönnit (etupuoli, loppu ja väli sanojen välillä).

Kuinka lasketaan tietyt solun merkit

Joskus sen sijaan, että lasket kaikki solun merkit, joudut ehkä laskemaan vain tietyn kirjaimen, numeron tai erikoismerkin esiintymät.

laskea kuinka monta kertaa tietty merkki näkyy solussa, käytä LEN-funktiota yhdessä SUBSTITUTE-toiminnon kanssa:

= LEN (solu) -LEN (SUBSTITUTE (solu, merkki ””))

Jotta ymmärtäisit kaavan paremmin, harkitse seuraavaa esimerkkiä.

Oletetaan, että ylläpidät toimitettujen tuotteiden tietokantaa, jossa jokaisella tuotetyypillä on oma yksilöllinen tunniste. Ja jokainen solu sisältää useita kohteita pilkuilla, välilyönneillä tai millä tahansa muulla erottimella erotettuna. Tehtävänä on laskea, kuinka monta kertaa tietty yksilöllinen tunniste näkyy kussakin solussa.

Olettaen, että toimitettujen tuotteiden luettelo on sarakkeessa B (alkaa sarakkeesta B2), ja laskemme ”A”: n määrän ”esiintymät, kaava on seuraava:

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

Kuinka tämä Excel-merkkilaskukaava toimii

Ymmärtääksesi kaavan logiikan, jaottakaamme se pienempiin osiin:

Tuloksena saat poistetun määrän. ”merkkiä, joka on sama kuin kyseisessä merkissä esiintyvien merkkien kokonaismäärä.

Sen sijaan, että määrität kaavassa laskettavan merkin, voit kirjoittaa sen johonkin soluun ja viitata sitten kyseiseen soluun. kaavassa. Tällä tavalla käyttäjät voivat laskea minkä tahansa muun soluun kirjoittamasi merkin esiintymät muuttamatta kaavaa:

Huom. . Excelin SUBSTITUTE on kirjainkokoinen funktio, ja siksi myös yllä oleva kaava on kirjainkokoinen. Esimerkiksi yllä olevassa kuvakaappauksessa solu B3 sisältää 3 A-esiintymää – kaksi isoa ja yksi pientä. Kaava on laskenut vain isot kirjaimet, koska toimitimme A-arvon SUBSTITUTE-funktiolle.

Kirjainkoon erottamaton Excel-kaava tiettyjen merkkien laskemiseen solussa

Jos tarvitset kirjainkoon merkitsemättömiä merkkejä, upota UPPER-funktio kohtaan SUBSTITUT muuntaa määritetty merkki suuriksi ennen suoritetaan vaihto. Ja muista kirjoittaa isot kirjaimet kaavaan.

Esimerkiksi laskeaksesi A- ja A-kohteet solussa B2, käytä tätä kaavaa:

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

Toinen tapa on käyttää sisäkkäisiä korvaavia toimintoja:

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

Kuten alla olevasta kuvakaappauksesta näet, molemmat kaavat laskevat virheettömästi määritetyn merkin isot ja pienet kirjaimet:

Joissakin tapauksissa , sinun on ehkä laskettava useita erilaisia merkkejä taulukkoon, mutta et ehkä halua muokata kaavaa joka kerta. Pistä tässä tapauksessa yksi korvaava funktio toiseen, kirjoita laskettava merkki johonkin soluun (tässä esimerkissä D1) ja muunna kyseisen solun arvo isoksi ja pieneksi käyttämällä YLÄ- ja ALA-funktioita:

=LEN(B2)-LEN(SUBSTITUTE(SUBSTITUTE(B2, UPPER($D$1), ""), LOWER($D$1),""))

Vaihtoehtoisesti voit muuntaa sekä lähdesolun että merkin sisältävän solun joko isoksi tai pieneksi. Esimerkki:

=LEN(B2)-LEN(SUBSTITUTE(UPPER(B2), UPPER($C$1),""))

Tämän lähestymistavan etuna on, että riippumatta siitä, syötetäänkö isoihin vai pieniin merkkeihin viitattua solua, kirjainkoko ei-merkitsevä merkkilaskukaava palauttaa oikean määrän:

Laske tietyn tekstin tai alaotsikon esiintymät solussa

Jos haluat laskea kuinka monta kertaa tietty merkkiyhdistelmä (ts. tietty teksti tai alimerkkijono) esiintyy tietyssä solussa, esim. ”A2” tai ”SS”, jaa sitten yllä olevilla kaavoilla palautettujen merkkien lukumäärä alamerkkijonon pituudella .

Kirjainkoon erottava kaava:

=(LEN(B2)-LEN(SUBSTITUTE(B2, $C$1,"")))/LEN($C$1)

Kirjainkoon erottamaton kaava:

=(LEN(B2)-LEN(SUBSTITUTE(LOWER(B2),LOWER($C$1),"")))/LEN($C$1)

Missä B2 on solu, joka sisältää koko tekstimerkkijonon, ja C1 on teksti (alimerkkijono), jonka haluat laskea.

Kaavan yksityiskohtaisen selityksen löydät kohdasta Kuinka tiettyjen tekstiä / sanoja lasketaan solussa.

Kuinka lasketaan tietty merkki ( s) alueella

Nyt kun tiedät Excel-kaavan laskemaan merkkejä solussa, haluat ehkä parantaa sitä edelleen selvittääksesi, kuinka monta kertaa tietty merkki esiintyy alueella. Tätä varten otamme Excel LEN -kaavan laskemaan tietyn merkin solussa, josta keskusteltiin edellisessä esimerkissä, ja laitamme sen SUMPRODUCT-funktioon, joka pystyy käsittelemään taulukoita:

SUMPRODUCT (LEN (alue) – LEN (SUBSTITUTE (alue, merkki, ””))))

Tässä esimerkissä kaava on seuraavan muotoinen:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))

Ja tässä on toinen kaava merkkien laskemiseksi Excelin alueella:

=SUM(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, "A","")))

Ensimmäiseen kaavaan verrattuna ilmeisin ero on SUMPRODUCTin sijaan SUM. Toinen ero on se, että se vaatii Ctrl + Vaihto + Enter, koska toisin kuin SUMPRODUCT, joka on suunniteltu prosessoimaan taulukoita, SUM pystyy käsittelemään matriiseja vain silloin, kun niitä käytetään matriisikaavassa.

Jos et halua kovakoodia kaavassa olevan merkin, voit tietysti kirjoittaa sen johonkin soluun, sanoa D1, ja viitata kyseiseen soluun merkkilaskukaavassa:

=SUMPRODUCT(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8, D1,"")))

Kuinka tämä merkkilaskentakaava toimii

Kuten ehkä muistat, SUBSTITUTE-funktiota käytetään korvaamaan kaikki määritetyn merkin esiintymät (tässä esimerkissä ”A”) tyhjällä tekstimerkkijonolla ( ””).

Sitten toimitamme SUBSTITUTE: n palauttaman tekstimerkkijonon Excel LEN -toimintoon, jotta se laskee merkkijonon pituuden ilman A: ta. Ja sitten vähennämme kyseisen merkkimäärän tekstimerkkijonon kokonaispituudesta. Näiden laskelmien tulos on merkkilaskentaryhmä, jossa on yksi merkkimäärä solua kohden.

Lopuksi SUMPRODUCT laskee yhteen matriisin numerot ja palauttaa määritetyn merkin kokonaismäärän alueella.

Kirjainkoon erottamaton kaava tiettyjen merkkien laskemiseksi alueella

Tiedät jo, että SUBSTITUTE on kirjainkokoinen funktio, mikä tekee Excel-kaavastamme merkkilaskulle myös kirjainkoon.

Jotta kaava ohitettaisiin kirjainkoko, noudata edellisessä esimerkissä esitettyjä lähestymistapoja: Kirjainkoon erottamaton kaava laskeaksesi tiettyjä merkkejä solussa.

Voit käyttää erityisesti yhtä seuraavat kaavat tiettyjen merkkien laskemiseksi alueella välittämättä:

Alla olevassa kuvakaappauksessa näkyy viimeinen kaava toiminnassa:

Vinkki. Voit laskea tietyn tekstin (alimerkkijonon) esiintymät alueella käyttämällä kaavaa, joka on esitetty kohdassa Kuinka tietyn alueen tekstiä / sanoja lasketaan.

Näin voit laskea merkkejä Excelissä LEN: n avulla toiminto.Jos haluat tietää, kuinka sanat lasketaan yksittäisten merkkien sijaan, löydät seuraavasta artikkelistamme muutaman hyödyllisen kaavan, ole hyvä ja pysy kuulolla!

Tällä välin voit ladata esimerkkityökirjoja merkkien määrällä tässä opetusohjelmassa käsitelty kaava ja tutustu luetteloon aiheeseen liittyvistä resursseista sivun lopussa. Kiitos lukemisesta ja toivon tapaavasi pian!

  • Excel LEN -toiminto – kaavat laskea solun pituus eli solun merkkien kokonaismäärä välilyöntein tai ilman.
  • Kuinka lasketaan sanat Excelissä – kirjainkoon mukaan erottamaton ja kirjainkoon erottamaton kaava laskemaan solun tai solualueen sanojen kokonaissummat.
  • COUNT- ja COUNTA-funktiot – laskea solut numeroilla ja ei-tyhjillä soluilla.
  • COUNTIF-funktio – kaavat laskea ehdollisesti solut Excelissä.
  • COUNTIFS-funktio – laskee solut, joissa on useampi kuin yksi ehto.

Write a Comment

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *