Conta valori mancanti
Supponendo di avere due elenchi, per contare il numero totale di valori in un elenco che non esistono in un altro elenco, è possibile utilizzare una formula SUMPRODUCT con l'aiuto delle funzioni MATCH e ISNA, oppure la funzione COUNTIF .
Conta valori mancanti con SUMPRODUCT, MATCH e ISNA
Conta valori mancanti con SUMPRODUCT e COUNTIF
Conta valori mancanti con SUMPRODUCT, MATCH e ISNA
Per contare il numero totale di valori nell'elenco B che sono assenti dall'elenco A come mostrato sopra, è possibile prima utilizzare la funzione MATCH per restituire una matrice delle posizioni relative dei valori dell'elenco B nell'elenco A. Se un valore non esiste nell'elenco A, verrà restituito un errore #N/A. La funzione ISNA identificherà quindi gli errori #N/A e SUMPRODUCT conterà il numero totale di tali errori.
Sintassi generica
=SUMPRODUCT(--ISNA(MATCH(intervallo_da_contare,intervallo_di_ricerca,0)))
- intervallo_da_contare: L'intervallo da cui contare i valori mancanti. Qui si riferisce all'elenco B.
- intervallo_di_ricerca: L'intervallo da confrontare con intervallo_da_contare. Qui si riferisce all'elenco A.
- 0: Il tipo_di_corrispondenza 0 forza MATCH a eseguire una corrispondenza esatta.
Per contare il numero totale di valori nell'elenco B che sono assenti dall'elenco A, copiare o inserire la formula seguente nella cella H6 e premere Invio per ottenere il risultato:
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
Spiegazione della formula
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- MATCH(F6:F8,B6:B10,0): Il tipo_di_corrispondenza 0 forza la funzione MATCH a restituire valori numerici che indicano le posizioni relative dei valori dalle celle F6 a F8 nell'intervallo B6:B10. Se un valore non esiste nell'elenco A, verrà restituito un errore #N/A. Quindi, i risultati saranno in una matrice come questa: {2;3;#N/A}.
- ISNA(ISNA(MATCH(F6:F8,B6:B10,0))) = ISNA(ISNA({2;3;#N/A})): ISNA verifica se un valore è un errore “#N/A” o meno. In caso affermativo, la funzione restituirà VERO; altrimenti, restituirà FALSO. Quindi, la formula ISNA restituirà {FALSO;FALSO;VERO}.
- SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0))) = SUMPRODUCT(--{FALSO;FALSO;VERO}): Il doppio segno negativo converte i VERO in 1 e i FALSO in 0: {0;1;0}. Quindi la funzione SUMPRODUCT restituisce la somma: 1.
Conta valori mancanti con SUMPRODUCT e COUNTIF
Per contare il numero totale di valori nell'elenco B che sono assenti dall'elenco A, è possibile utilizzare anche la funzione COUNTIF per determinare se un valore esiste nell'elenco A o meno con la condizione “=0”, poiché uno 0 verrà generato se un valore è mancante. Quindi SUMPRODUCT conterà il numero totale di valori mancanti.
Sintassi generica
=SUMPRODUCT(--(COUNTIF(intervallo_di_ricerca,intervallo_da_contare)=0))
- intervallo_di_ricerca: L'intervallo da confrontare con intervallo_da_contare. Qui si riferisce all'elenco A.
- intervallo_da_contare: L'intervallo da cui contare i valori mancanti. Qui si riferisce all'elenco B.
- 0: Il tipo_di_corrispondenza 0 forza MATCH a eseguire una corrispondenza esatta.
Per contare il numero totale di valori nell'elenco B che sono assenti dall'elenco A, copiare o inserire la formula seguente nella cella H6 e premere Invio per ottenere il risultato:
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
Spiegazione della formula
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10,F6:F8): La funzione COUNTIF conta il numero di occorrenze delle celle da F6 a F8 nell'intervallo B6:B10. Il risultato sarà in una matrice come questa: {1;1;0}.
- --(--(COUNTIF(B6:B10,F6:F8)=0)=0) = --(--({1;1;0}=0)=0): Il frammento {1;1;0}=0 produce una matrice VERO e FALSO {FALSO;FALSO;VERO}. Il doppio segno negativo converte quindi i VERO in 1 e i FALSO in 0. La matrice finale è come questa: {0;0;1}.
- SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0)) = SUMPRODUCT({0;0;1}): SUMPRODUCT restituisce quindi la somma: 1.
Funzioni correlate
In Excel, la funzione SUMPRODUCT può essere utilizzata per moltiplicare due o più colonne o array insieme e quindi ottenere la somma dei prodotti. In realtà, SUMPRODUCT è una funzione utile che può aiutare a contare o sommare i valori delle celle con criteri multipli come le funzioni COUNTIFS o SUMIFS. Questo articolo introdurrà la sintassi della funzione e alcuni esempi per questa funzione SUMPRODUCT.
La funzione MATCH di Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa del valore.
La funzione COUNTIF è una funzione statistica in Excel utilizzata per contare il numero di celle che soddisfano un criterio. Supporta operatori logici (<>, =, >, e <), e i caratteri jolly (? e *) per la corrispondenza parziale.
Formule correlate
Ci sono casi in cui è necessario confrontare due elenchi per verificare se un valore dell'elenco A esiste nell'elenco B in Excel. Ad esempio, si dispone di un elenco di prodotti e si desidera verificare se i prodotti presenti nell'elenco esistono nell'elenco dei prodotti fornito dal proprio fornitore. Per svolgere questo compito, qui abbiamo elencato tre modi diversi, scegli quello che preferisci.
Questo articolo si concentrerà sulle formule di Excel per contare le celle che sono esattamente uguali a una stringa di testo specificata o parzialmente uguali alla stringa di testo data come mostrato negli screenshot sottostanti. In primo luogo, spiegherà la sintassi della formula e l'argomento, quindi fornirà esempi per una migliore comprensione.
Conta numero di celle non comprese tra due numeri dati
Contare il numero di celle comprese tra due numeri è un compito comune per noi in Excel, ma, in alcuni casi, potresti voler contare le celle non comprese tra due numeri dati. Ad esempio, ho un elenco di prodotti con le vendite da lunedì a domenica, ora devo ottenere il numero di celle che non sono comprese tra numeri bassi e alti specifici come mostrato nello screenshot sottostante. In questo articolo, introdurrò alcune formule per affrontare questo compito in Excel.
I migliori strumenti per la produttività di Office
Kutools per Excel - Ti aiuta a distinguerti dalla massa
Kutools per Excel vanta oltre 300 funzionalità, garantendo che ciò di cui hai bisogno sia a portata di clic...
Office Tab - Abilita la lettura e la modifica a schede in Microsoft Office (include Excel)
- Un secondo per passare tra decine di documenti aperti!
- Riduci centinaia di clic del mouse ogni giorno, dì addio al dolore al polso.
- Aumenta la tua produttività del 50% durante la visualizzazione e la modifica di più documenti.
- Porta le schede efficienti in Office (include Excel), proprio come Chrome, Edge e Firefox.