Formula generica
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Sommario
Per cercare un valore in più fogli di lavoro in una cartella di lavoro e restituire un conteggio, è possibile utilizzare una formula basata sulle funzioni CONTA.SE e INDIRETTO. Con alcune impostazioni preliminari, è possibile utilizzare questo approccio per cercare un valore specifico in un'intera cartella di lavoro. Nell'esempio mostrato, la formula in C5 è:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Contesto: dati di esempio
La cartella di lavoro contiene 4 fogli di lavoro in totale. Sheet1 , Sheet2 e Sheet3 contengono ciascuno 1000 nomi casuali simili a questo:
Spiegazione
L'intervallo B7: B9 contiene i nomi dei fogli che vogliamo includere nella ricerca. Queste sono solo stringhe di testo e dobbiamo lavorare per farle riconoscere come riferimenti di fogli validi.
Lavorando dall'interno verso l'esterno, questa espressione viene utilizzata per creare un riferimento completo del foglio:
"'"&B7&"'!"&"1:1048576"
Le virgolette singole vengono aggiunte per consentire nomi di fogli con spazi e il punto esclamativo è una sintassi standard per gli intervalli che includono un nome di foglio. Il testo "1: 1048576" è un intervallo che include ogni riga del foglio di lavoro.
Dopo che B7 è stato valutato e i valori sono stati concatenati, l'espressione sopra restituisce:
"'Sheet1'!1:1048576"
che va nella funzione INDIRETTO come argomento 'ref_text'. INDIRETTO valuta questo testo e restituisce un riferimento standard a ogni cella in Sheet1 . Questo entra nella funzione CONTA.SE come intervallo. Il criterio è fornito come riferimento assoluto a C4 (bloccato in modo che la formula possa essere copiata nella colonna C).
CONTA.SE quindi restituisce un conteggio di tutte le celle con un valore uguale a "maria", 25 in questo caso.
Nota: COUNTIF non fa distinzione tra maiuscole e minuscole.
Contiene vs. uguale
Se vuoi contare tutte le celle che contengono il valore in C4, invece di tutte le celle uguali a C4, puoi aggiungere caratteri jolly ai criteri in questo modo:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Ora CONTA.SE conterà le celle con la sottostringa "John" ovunque nella cella.
Prestazione
In generale, non è una buona pratica specificare un intervallo che includa tutte le celle del foglio di lavoro. Ciò può causare problemi di prestazioni, poiché l'intervallo include milioni e milioni di celle. In questo esempio, il problema è aggravato, poiché la formula utilizza la funzione INDIRETTO, che è una funzione volatile. Le funzioni volatili vengono ricalcolate a ogni modifica del foglio di lavoro, quindi l'impatto sulle prestazioni può essere enorme.
Quando possibile, limitare gli intervalli a una dimensione ragionevole. Ad esempio, se sai che i dati non appariranno oltre la riga 1000, puoi cercare solo le prime 1000 righe in questo modo:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
