Conta il numero di celle che non contengono molti valori

Normalmente, è facile contare le celle che non contengono un valore specifico con la funzione CONTA.SE. Questo articolo ti guiderà attraverso i passaggi per contare il numero di celle che non contengono molti valori in un intervallo specificato in Excel.

Come contare le celle che non contengono molti valori?

Come mostrato nello screenshot sottostante, per contare le celle in B3:B11 che non contengono i valori elencati in D3:D4, puoi procedere come segue.

Formula generica

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

Argomenti

Nota: Questa formula deve essere inserita come una formula matrice. Dopo aver applicato la formula, se ci sono parentesi graffe attorno alla formula, significa che una formula matrice è stata creata correttamente.

Come utilizzare questa formula?

1. Seleziona una cella vuota per visualizzare il risultato.

2. Inserisci la seguente formula al suo interno e poi premi contemporaneamente i tasti Ctrl + Maiusc + Invio per ottenere il risultato.

=SOMMA(1-(MATR.SOMMA.PRODOTTO(--(VAL.NUMERO(RICERCA(TRANSPONI(D3:D4);B3:B11)));RIF.RIGA(D3:D4)^0)>0))

Come funzionano queste formule?

=SOMMA(1-(MATR.SOMMA.PRODOTTO(--(VAL.NUMERO(RICERCA(TRANSPONI(D3:D4);B3:B11)));RIF.RIGA(D3:D4)^0)>0))

1) --(VAL.NUMERO(RICERCA(TRANSPONI(D3:D4);B3:B11))):

• TRANSPONI(D3:D4): La funzione TRANSPONI ruota l'orientamento di D3:D4 e restituisce {“conta”,”vuoto”};
• RICERCA({“conta”,”vuoto”},B3:B11): La funzione RICERCA qui trova la posizione della sottostringa “conta” e “vuoto” dall'intervallo B3:B11 e restituisce un array come {#VALORE!,#VALORE!;#VALORE!,#VALORE!;1,#VALORE!;#VALORE!,8;1,#VALORE!;#VALORE!,#VALORE!;#VALORE!,#VALORE!;1,
  #VALORE!;1,7}.
• In questo caso, ogni cella in B3:B11 verrà cercata due volte poiché ci sono due valori che si desidera escludere durante il conteggio delle celle, quindi otterrai 18 valori nell'array. Ogni numero nell'array indica la posizione del primo carattere di “conta” o “vuoto” in ogni cella di B3:B11.
• VAL.NUMERO{#VALORE!,#VALORE!;#VALORE!,#VALORE!;1,#VALORE!;#VALORE!,8;1,#VALORE!;#VALORE!,#VALORE!;#VALORE!,
  #VALORE!;1,#VALORE!;1,7}: La funzione VAL.NUMERO restituisce VERO se incontra numeri nell'array e restituisce FALSO se incontra errori. Qui restituisce il risultato come {FALSO,FALSO;FALSO,FALSO;VERO,FALSO;FALSO,VERO;VERO,FALSO;FALSO,FALSO;FALSO,FALSO;VERO,FALSO;
  VERO,VERO}.
• --({FALSO,FALSO;FALSO,FALSO;VERO,FALSO;FALSO,VERO;VERO,FALSO;FALSO,FALSO;FALSO,FALSO;VERO,
  FALSO;VERO,VERO}): Questi due segni meno convertono “VERO” in 1 e “FALSO” in 0. Qui otterrai un nuovo array come {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2) RIF.RIGA(D3:D4)^0: La funzione RIF.RIGA restituisce i numeri di riga del riferimento di cella: {3;4}, e poi l'operatore di elevamento a potenza (^) restituisce il risultato dei numeri 3 e 4 elevati alla potenza 0, e infine restituisce il risultato come {1;1}.

3) MATR.SOMMA.PRODOTTO({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): La funzione MATR.SOMMA.PRODOTTO restituisce il prodotto matriciale di questi due array: {0;0;1;1;1;0;0;1;2} per soddisfare i dati originali. Qualsiasi numero diverso da zero nell'array rappresenta un valore in cui almeno una delle stringhe escluse è stata trovata, e lo zero indica che nessuna delle stringhe escluse è stata trovata.

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

• {0;0;1;1;1;0;0;1;2}>0: Qui verifica se ogni numero nell'array è maggiore di 0. Se un numero è maggiore di 0, restituisce VERO, altrimenti restituisce FALSO. Poi otterrai un nuovo array come {FALSO;FALSO;VERO;VERO;VERO;FALSO,FALSO,VERO;VERO}.
• 1-{FALSO;FALSO;VERO;VERO;VERO;FALSO,FALSO,VERO;VERO}: Poiché dobbiamo contare solo le celle che non contengono i valori specificati, dobbiamo invertire questi valori nell'array sottraendoli da 1. Qui l'operatore matematico converte automaticamente i valori VERO e FALSO in 1 e 0, e infine restituisce il risultato come {1;1;0;0;0;1;1;0;0}.
• SOMMA{1;1;0;0;0;1;1;0;0}: La funzione SOMMA somma tutti i numeri nell'array e restituisce il risultato finale come 4.

Funzioni correlate

Formule correlate