Abbiamo già imparato a contare le celle che contengono un testo specifico usando la funzione CONTA.SE. In questo articolo impareremo a contare quante volte una parola appare nell'intervallo excel. In altre parole, conteremo quante volte una parola si è verificata in un intervallo excel.
Formula generica
=SUMPRODUCT(LEN(intervallo)-LEN(SOSTITUTO(intervallo,parola,"")))/LEN(parola)
Gamma: L'intervallo in cui stai cercando di contare la parola specifica.
Parola: La parola che vuoi contare.
Facciamo un esempio e capiamo come funziona.
Esempio: conta la parola "felice" nell'intervallo excel.
Qui abbiamo alcune frasi in celle diverse. Dobbiamo contare le occorrenze della parola "felice" in quell'intervallo.
Scrivi questa formula nella cella D2.
=SOMMA.PRODOTTO(LUNGHEZZA(A2:A4)-LUNGHEZZA(SOSTITUTO(A2:A4,D1,"")))/LUNGHEZZA(D1)
Usando questa funzione excel conta quante volte la parola specifica "felice" appare nell'intervallo A2: A4. Questa formula excel restituisce il conteggio come 4.
Come funziona?
L'idea è di ottenere la somma del conteggio dei caratteri di una data parola nell'intervallo e quindi dividere per la lunghezza della parola. Ad esempio, se Happy si verifica 4 volte in un intervallo, è una lunghezza totale di 20 (4*5) nell'intervallo. Se dividiamo 20 per 5 otteniamo 4. Che è il conteggio delle parole nell'intervallo.
Abbattiamolo dall'interno.
LEN(A2:A4): restituisce il conteggio dei caratteri in ogni cella come un array {49;27;34}.
Next LEN(SUBSTITUTE(A2:A4,D1,"")): La funzione sostitutiva sostituisce la parola in D1 con "" in ogni cella dell'intervallo A2:A4. Quindi la funzione Len restituisce il conteggio dei caratteri da queste frasi sostituite in un array {39;22;29}.
LEN(D1): restituisce la lunghezza della parola in D1 quale 5 (felice).
Ora la formula è semplificata in SOMMA.PRODOTTO({49;27;34} - {39;22;29})/5. Dopo la sottrazione degli array, SUMPRODUCT ha SOMMAPRODOTTO({10;5;5})/5. La funzione aggiunge l'array e otteniamo 20/5. Che ci dà il nostro risultato 4.
Conteggio senza distinzione tra maiuscole e minuscole
Poiché SOSTITUTO fa distinzione tra maiuscole e minuscole, la formula sopra ignorerà qualsiasi parola che non abbia lo stesso caso, ad es. "Felice". Per fare in modo che la formula sopra ignori le maiuscole, dobbiamo cambiare le maiuscole di ogni parola in maiuscole e minuscole della parola che stiamo cercando. Ecco come è fatto.
=SOMMA.PRODOTTO(LUNGHEZZA(A2:A4)-LUNGHEZZA(SOSTITUTO(SUPERIORE(A2:A4),SUPERIORE(D1),"")))/LIM(D1)
Ora il caso non ha più importanza. Questa funzione conterà ogni parola in D1 indipendentemente dal caso.
Possibili errori:
Verrà contata una parte della parola di un'altra parola: in questo esempio, se avessimo avuto la parola "felicità" (solo per esempio, so che non esiste una parola come questa) sarebbe stata conteggiata anche quella. Per evitare ciò avresti potuto circondare le parole con specifiche, “ ” &D1& “ ”. Ma quando la parola appare per prima o per ultima nella frase, anche questo fallirà.
Articoli popolari
50 scorciatoie Excel per aumentare la produttività: Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare tla funzione CERCA.VERT in Excel: questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel: Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel: Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.