Formula generică
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
rezumat
Pentru a număra de câte ori apare un anumit cuvânt (sau orice șir secundar) într-o gamă de celule, puteți utiliza o formulă bazată pe funcțiile SUBSTITUTE, LEN și SUMPRODUCT. În exemplul prezentat, formula din C11 este:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Notă: Formula de pe această pagină contează instanțele unui cuvânt dintr-un interval. De exemplu, dacă o celulă conține două instanțe ale unui cuvânt, va contribui cu 2 la numărul total. Dacă doriți doar să numărați celule care conțin un anumit cuvânt, consultați această formulă simplă bazată pe funcția COUNTIF.
Explicaţie
În versiunea generică a formulei, rng reprezintă intervalul de verificat, iar txt este cuvântul sau sub șirul de numărat.
În exemplul prezentat, B5: B8 este intervalul de verificat, iar C2 conține textul (cuvânt sau șir de caractere) de numărat.
Pentru fiecare celulă din interval, SUBSTITUTE elimină șirul din textul original și LEN calculează lungimea textului fără sub șir. Acest număr este apoi scăzut din lungimea textului original. Rezultatul este numărul de caractere care au fost eliminate de către SUBSTITUTE.
Apoi, numărul de caractere eliminate este împărțit la lungimea șirului secundar. Deci, dacă un șir sau un cuvânt are o lungime de 5 caractere și lipsesc 10 caractere după ce a fost eliminat din textul original, știm că șirul / cuvântul a apărut de două ori în textul original.
Deoarece calculul de mai sus este înfășurat în funcția SUMPRODUCT, rezultatul este un tablou care conține un număr pentru fiecare celulă din interval. Aceste numere reprezintă numărul de apariții ale șirului secundar din fiecare celulă. Pentru acest exemplu, matricea arată astfel: (1; 1; 0; 1)
În cele din urmă, SUMPRODUCT însumează toate elementele din matrice pentru a obține aparițiile totale ale șirului de caractere în intervalul de celule.
Ignorând cazul
SUBSTITUTE este o funcție sensibilă la majuscule, deci se va potrivi cu majuscule și minuscule atunci când se execută o înlocuire. Dacă trebuie să numărați atât aparițiile majuscule, cât și minusculele unui cuvânt sau un șir de caractere, utilizați funcția UPPER din interiorul SUBSTITUTE pentru a converti textul cu majuscule înainte de a rula substituirea:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
