Contare il numero di celle che non contengono molti valori

Di solito, contare le celle che non contengono un valore specifico è semplice grazie alla funzione CONTA.SE. Questo articolo spiega passo dopo passo come contare le celle che non includono più valori all’interno di un intervallo definito in Excel.

Come contare le celle che non contengono determinati valori?

Come illustrato nello screenshot seguente, per contare le celle nell’intervallo B3:B11 che non contengono i valori elencati in D3:D4, procedere nel modo indicato.

Formula generica

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

Argomenti

Nota: Questa formula deve essere inserita come formula di matrice. Dopo averla applicata, se compare racchiusa tra parentesi graffe, significa che la formula di matrice è stata creata correttamente.

Come utilizzare questa formula?

1. Selezionare una cella vuota in cui visualizzare il risultato.

2. Inserisci la seguente formula nella cella e premi contemporaneamente i tasti Ctrl+Maiusc+Invio per ottenere il risultato.

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

Come funzionano queste formule?

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4):La funzione TRASPOSI ruota l'orientamento di D3:D4 e restituisce {«count»,«blank»};
• SEARCH({“count”,”blank”},B3:B11): In questo caso, la funzione TROVA individua la posizione delle sottostringhe «count» e «blank» nell'intervallo B3:B11 e restituisce un array come {#VALORE!;#VALORE!;#VALORE!;#VALORE!;1;#VALORE!;#VALORE!;8;1;#VALORE!;#VALORE!;#VALORE!;1;
  #VALORE!;1,7}.
• In questo caso, ogni cella nell'intervallo B3:B11 verrà cercata due volte, poiché sono presenti due valori da escludere durante il conteggio delle celle, generando così un array di 18 valori. Ciascun numero nell’array indica la posizione del primo carattere di «count» o «blank» all’interno di ogni cella compresa tra B3 e B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}:La funzione VAL.NUMERO restituisce VERO se incontra numeri nell'array e FALSO se incontra errori. In questo caso restituisce il risultato come {FALSO;FALSO;FALSO;FALSO;VERO;FALSO;FALSO;VERO;VERO;FALSO;FALSO;FALSO;VERO;FALSO;
  VERO;VERO}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,)
  FALSE;TRUE,TRUE}):Questi due segni meno convertono «VERO» in 1 e «FALSO» in 0. In questo modo si ottiene un nuovo array come {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0: La funzione RIGA restituisce i numeri di riga del riferimento di cella: {3;4}. Dopodiché, l'operatore di elevamento a potenza (^) eleva i numeri 3 e 4 alla potenza 0, restituendo infine il risultato {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): La funzione MATR.PRODOTTO restituisce il prodotto matriciale di questi due array: {0;0;1;1;1;0;0;1;2}, da confrontare con i dati originali. Qualsiasi valore diverso da zero nell'array indica la presenza di almeno una delle stringhe escluse, mentre lo zero significa che nessuna delle stringhe escluse è stata trovata.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: Qui viene verificato se ogni numero nell'array è maggiore di 0. Se un numero è maggiore di 0, restituisce VERO; altrimenti restituisce FALSO, generando così un nuovo array come {FALSO;FALSO;VERO;VERO;VERO;FALSO;FALSO;VERO;VERO}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Poiché dobbiamo contare solo le celle che non contengono i valori specificati, dobbiamo invertire questi valori nell'array sottraendoli da 1. In questo caso, l'operatore matematico converte automaticamente i valori VERO e FALSO in 1 e 0, restituendo infine il risultato come {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}La funzione SOMMA somma tutti i numeri presenti nell'array e restituisce il risultato finale, pari a 4.

Funzioni correlate

Formule correlate