Formula generica
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Sommario
Per contare le celle non uguali a nessuna delle tante cose, puoi usare una formula basata sulle funzioni MATCH, ISNA e SUMPRODUCT. Nell'esempio mostrato, la formula nella cella F5 è:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
dove "dati" è l'intervallo denominato B5: B16 e "escludi" è l'intervallo denominato D5: D7.
Spiegazione
Innanzitutto, un piccolo contesto. Normalmente, se hai solo un paio di cose che non vuoi contare, puoi usare COUNTIFS in questo modo:
=COUNTIFS(range,"apple",range,"orange")
Ma questo non scala molto bene se hai un elenco di molte cose, perché dovrai aggiungere un'ulteriore coppia intervallo / criteri per ogni cosa che non vuoi contare. Sarebbe molto più semplice creare un elenco e passare un riferimento a questo elenco come parte dei criteri. Questo è esattamente ciò che fa la formula in questa pagina.
In sostanza, questa formula utilizza la funzione CONFRONTA per trovare celle diverse da "a", "b" o "c" con questa espressione:
MATCH(data,exclude,0)
Nota che il valore di ricerca e l'array di ricerca sono "invertiti" rispetto alla configurazione normale: forniamo tutti i valori dell'intervallo denominato "dati" come valori di ricerca e forniamo tutti i valori che vogliamo escludere nell'intervallo denominato "escludi". Poiché diamo a MATCH più di un valore di ricerca, otteniamo più di un risultato in un array come questo:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
In sostanza, CONFRONTA ci fornisce la posizione dei valori corrispondenti come numero e restituisce # N / A per tutti gli altri valori.
I risultati # N / A sono quelli che ci interessano, poiché rappresentano valori diversi da "a", "b" o "c". Di conseguenza, utilizziamo ISNA per forzare questi valori a TRUE e i numeri a FALSE:
ISNA(MATCH(data,exclude,0)
Quindi usiamo un doppio negativo per forzare TRUE a 1 e FALSE a zero. L'array risultante, all'interno di SUMPRODUCT, ha questo aspetto:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Con un solo array da elaborare, SUMPRODUCT somma e restituisce un risultato finale, 4.
Nota: l'utilizzo di SUMPRODUCT al posto di SUM evita la necessità di utilizzare control + shift + enter.
Conta meno corrispondenza
Un altro modo per contare le celle che non sono uguali a molte cose è contare tutti i valori e sottrarre le corrispondenze. Puoi farlo con una formula come questa:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Qui, COUNTA restituisce un conteggio di tutte le celle non vuote. La funzione CONTA.SE, dato l'intervallo denominato "escludi", restituirà tre conteggi, uno per ogni elemento nell'elenco. SUMPRODUCT somma il totale e questo numero viene sottratto dal conteggio di tutte le celle non vuote. Il risultato finale è il numero di celle che non corrispondono ai valori in "escludi".
Il valore letterale contiene la logica del tipo
La formula in questa pagina conta con la logica "uguale a". Se devi contare le celle che non contengono molte stringhe, dove contiene significa che una stringa potrebbe apparire ovunque in una cella, avrai bisogno di una formula più complessa.
