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
Escursione (obbligatorio): l'intervallo da cui si desidera contare le celle che non contengono molti valori.
Criteri_intervallo (obbligatorio): l'intervallo contiene i valori che si desidera escludere durante il conteggio delle celle.
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 + Shift + entrare 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
Funzione SOMMA di Excel
La funzione SOMMA di Excel aggiunge valori.
Funzione MMULT di Excel
La funzione MMULT di Excel restituisce il prodotto di matrice di due matrici.
Funzione ISNUMBER di Excel
La funzione VAL.NUMERO di Excel restituisce VERO quando una cella contiene un numero e FALSO in caso contrario.
Funzione TRASPOSI di Excel
La funzione TRANSPOSE di Excel ruota l'orientamento di un intervallo o di una matrice.
Funzione RIGA di Excel
La funzione RIGA di Excel restituisce il numero di riga di un riferimento.
Formule correlate
Conta le celle che non contengono errori
Questo tutorial fornisce passaggi dettagliati per aiutarti a contare il numero di celle che non contengono errori in un intervallo specificato in Excel.
Conta le celle che non contengono testo specifico
Puoi utilizzare la funzione CONTA.SE con un carattere jolly per contare il numero di celle che contengono testo specifico in un intervallo. Al contrario, è anche facile utilizzare la funzione CONTA.SE per contare le celle che non contengono testo specifico. Questo tutorial fornisce passaggi dettagliati per aiutare a risolvere il problema.
Conta il numero di celle della data in un intervallo per giorno della settimana
Excel fornisce la combinazione delle funzioni SUMPRODUCT e WEEKDAY per aiutarci a contare facilmente il numero di giorni della settimana specificati all'interno di un intervallo. Questo tutorial fornisce una guida passo passo per aiutarti a superarlo.
Conta il numero di celle di testo
Per contare il numero di celle che contengono testo in un intervallo specificato, la funzione CONTA.SE può aiutarti a farlo facilmente. Qui questo articolo introdurrà la formula in dettaglio per aiutarti a risolvere il problema.
I migliori strumenti per la produttività in ufficio
Kutools per Excel: ti aiuta a distinguerti dalla folla
Kutools per Excel vanta oltre 300 funzionalità, Garantirti che ciò di cui hai bisogno sia a portata di clic...
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, Edge e Firefox.
