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 post spiegherà come 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 nell'immagine sottostante, per contare le celle in B3: B11 che non contengono 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 immessa come formula di matrice. Dopo aver applicato la formula, se sono presenti parentesi graffe avvolte attorno alla formula, viene creata correttamente una formula di matrice.

Come utilizzare questa formula?

1. .Seleziona una cella vuota per visualizzare il risultato.

2. Immettere la formula seguente in essa e quindi premere il tasto Ctrl + spostamento + Entra tasti contemporaneamente 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 TRANSPOSE ruota l'orientamento di D3:D4 e restituisce {“conta”,”vuoto”};
• SEARCH({“count”,”blank”},B3:B11):La funzione SEARCH qui trova la posizione della sottostringa "count" e "blank" dall'intervallo B3: B11 e restituisce un array come {#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE! ;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 si otterranno 18 valori nell'array. Ciascun numero nell'array indica la posizione del primo carattere di “count” o “blank” in ogni cella di B3: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 i numeri nell'array e restituisce FALSO se incontra gli errori. Qui restituisce il risultato come {FALSO,FALSO;FALSO,FALSO;VERO,FALSO;FALSO,VERO;VERO,FALSO;FALSO,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 "TRUE" in 1 e convertono "FALSE" 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) ROW(D3:D4)^0:La funzione RIGA restituisce i numeri di riga del riferimento di cella: {3;4}, quindi l'operatore di elevazione a potenza (^) restituisce il risultato del numero 3 e 4 elevato a una potenza 0 e infine restituisce il risultato come {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 MMULT restituisce il prodotto matrice di questi due array: {0;0;1;1;1;0;0;1;2} per incontrare i dati originali. Qualsiasi numero diverso da zero nell'array rappresenta un valore in cui è stata trovata almeno una delle stringhe escluse e zero indica che non sono state trovate stringhe escluse.

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

• {0;0;1;1;1;0;0;1;2}>0:Qui controlla se ogni numero nell'array è maggiore di 0. Se un numero è maggiore di 0, restituisce VERO, in caso contrario, restituisce FALSO. Quindi otterrai 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. 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}.
• SUM{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

I migliori strumenti per la produttività in ufficio

Kutools per Excel: ti aiuta a distinguerti dalla folla

Vorresti completare il tuo lavoro quotidiano in modo rapido e perfetto? Kutools per Excel offre 300 potenti funzionalità avanzate (Combina cartelle di lavoro, somma per colore, contenuto di celle divise, converti data e così via ...) e risparmia l'80% di tempo per te.

• Progettato per 1500 scenari di lavoro, ti aiuta a risolvere l'80% dei problemi di Excel.
• Riduci migliaia di clic della tastiera e del mouse ogni giorno, allevia i tuoi occhi e le tue mani stanche.
• Diventa un esperto di Excel in 3 minuti. Non è più necessario ricordare formule dolorose e codici VBA.
• Prova gratuita illimitata di 30 giorni. Garanzia di rimborso di 60 giorni. Aggiornamento gratuito e supporto per 2 anni.

Continua a leggere... Scaricare

---

Scheda Office: abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)

• Un secondo per passare da una dozzina di documenti aperti all'altra!
• Riduci ogni giorno centinaia di clic del mouse, dì addio alla mano del mouse.
• Aumenta la produttività del 50% durante la visualizzazione e la modifica di più documenti.
• Porta schede efficienti in Office (incluso Excel), proprio come Chrome, Firefox e il nuovo Internet Explorer.

Continua a leggere... Scaricare