Am învățat deja cum să numărăm celulele care conțin un anumit text folosind funcția COUNTIF. În acest articol, vom învăța cum să numărăm de câte ori apare un cuvânt în gama excel. Cu alte cuvinte, vom număra de câte ori a apărut un cuvânt într-un interval excel.
Formula generică
= SUMPRODUCT (LEN (interval) -LEN (SUBSTITUT (interval, cuvânt, ""))) / LEN (cuvânt)
Gamă: Intervalul în care încercați să numărați cuvântul specific.
Cuvânt: Cuvântul pe care vrei să-l numeri.
Să luăm un exemplu și să înțelegem cum funcționează.
Exemplu: numărați cuvântul „fericit” din gama excel.
Aici avem câteva propoziții în diferite celule. Trebuie să numărăm aparițiile cuvântului „fericit” în acest interval.
Scrieți această formulă în celula D2.
= SUMPRODUCT (LEN (A2: A4) -LEN (SUBSTITUT (A2: A4, D1, ""))) / LEN (D1)
Folosirea acestei funcții excel contează de câte ori apare cuvântul specific „fericit” A2: A4. Această formulă excel returnează numărul ca 4.
Cum functioneaza?
Ideea este să obțineți suma numărului de caractere al cuvântului dat în interval și apoi să împărțiți la lungimea cuvântului. De exemplu, dacă Happy are de 4 ori într-un interval, lungimea totală este de 20 (4 * 5) în interval. Dacă împărțim 20 la 5 obținem 4. Care este numărul de cuvinte din interval.
Să-l dărâmăm din interior.
LEN (A2: A4): acesta returnează numărul de caractere din fiecare celulă ca matrice {49; 27; 34}.
Următorul LEN (SUBSTITUT (A2: A4, D1, "")): Funcția de substituire înlocuiește cuvântul din D1 cu „” în fiecare celulă din intervalul A2: A4. Apoi, funcția Len returnează numărul de caractere din aceste propoziții substituite într-o matrice {39; 22; 29}.
LEN (D1): aceasta returnează lungimea cuvântului în D1 care 5 (fericit).
Acum formula este simplificată la SUMPRODUCT ({49; 27; 34} - {39; 22; 29}) / 5. După scăderea matricilor, SUMPRODUCT are SUMPRODUCT ({10; 5; 5}) / 5. Funcția adaugă matricea și obținem 20/5. Ceea ce ne dă rezultatul nostru 4.
Numărarea nu face sensibilitate la majuscule
Deoarece SUBSTITUTE este sensibil la majuscule și minuscule, formula de mai sus va ignora orice cuvânt care nu are același caz, adică „Fericit”. Pentru a face ca formula de mai sus să ignore majuscule, trebuie să schimbăm majusculele și minusculele fiecărui cuvânt în caz de cuvânt pe care îl căutăm. Așa se face.
= SUMPRODUCT (LEN (A2: A4) -LEN (SUBSTITUT (UPPER (A2: A4), UPPER (D1), "")))) / LEN (D1)
Acum, cazul nu mai contează. Această funcție va conta fiecare cuvânt în D1, indiferent de caz.
Posibile erori:
Va fi numărată o parte a cuvântului din alt cuvânt: în acest exemplu, dacă am avea cuvântul „fericire” (doar de exemplu, știu că nu există niciun cuvânt ca acesta), ar fi fost numărat și el. Pentru a evita acest lucru, ați fi putut înconjura cuvintele cu specificații, „” & D1 & „”. Dar când cuvântul apare primul sau ultimul în propoziție, acest lucru va eșua și el.
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea: Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel: Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel: Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel: Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.