Conteggi con logica OR per più criteri in Excel
Normalmente, puoi utilizzare la funzione CONTA.SE per contare le celle in base a una o più condizioni con la logica AND in Excel. Ti è mai capitato di dover contare più di un valore da una singola colonna o da un intervallo di celle? Ciò significa contare con più condizioni e logica OR. In questo caso, puoi applicare insieme le funzioni SOMMA e CONTA.PI.SE oppure utilizzare la funzione SUMPRODUCT.
Contare le celle con condizioni OR in Excel
Ad esempio, ho una serie di dati come mostrato nell'immagine sottostante, ora voglio contare il numero del prodotto che è "Matita" o "Righello", qui parlerò di due formule per risolvere questa attività in Excel.
Contare le celle con condizioni OR utilizzando le funzioni SOMMA e CONTA.PI.SE
In Excel, per contare con più condizioni OR, è possibile utilizzare le funzioni SOMMA e CONTA.SE con una costante di matrice, la sintassi generica è:
- range: l'intervallo di dati contiene i criteri da cui si contano le celle;
- criterion1, criterion2, criterion3…: le condizioni in base alle quali vuoi contare le celle.
Per contare il numero di prodotti che sono "Matita" o "Righello", copiare o inserire la formula seguente in una cella vuota, quindi premere Entra tasto per ottenere il risultato:
Spiegazione della formula:
=SOMMA(CONTA.SE(B2:B13,{"Matita","Righello"}))
- {"Matita","Righello"}: Innanzitutto, dovresti impacchettare tutte le condizioni in una costante di array come questa: {"Pencil","Ruler"}, separare gli elementi con virgole.
- CONTA.SE(B2:B13,{"Matita","Righello"}): Questa funzione COUNTIFS otterrà i conteggi individuali per "Pencil" e "Ruler", e otterrai il risultato come questo: {2,3}.
- SOMMA(CONTA.SE(B2:B13,{"Matita","Righello"}))=SOMMA({2,3}): Infine, questa funzione SUM somma tutti gli elementi nell'array e restituisce il risultato: 5.
Tip: Puoi anche utilizzare i riferimenti di cella per i criteri, applica la formula di matrice seguente, quindi premi CTRL + MAIUSC + INVIO chiavi insieme per ottenere il risultato corretto:
Contare le celle con condizioni OR utilizzando la funzione SUMPRODUCT
Ecco un'altra formula creata dalla funzione SUMPRODUCT che può aiutare a contare anche le celle con logica OR. La sintassi generica è:
- range: l'intervallo di dati contiene i criteri da cui si contano le celle;
- criterion1, criterion2, criterion3…: le condizioni in base alle quali vuoi contare le celle.
Si prega di copiare o inserire la seguente formula in una cella vuota e premere Entra chiave per restituire il risultato:
Spiegazione della formula:
=SOMMA.PRODOTTO(1*(B2:B13={"Matita","Righello"}))
- B2:B13={"Matita","Righello"}: Questa espressione confronta ogni criterio "Matita", "Righello" con la cella dell'intervallo B2:B13. Se il criterio è soddisfatto, restituisce VERO, altrimenti verrà visualizzato FALSO, il risultato sarà il seguente: {VERO,FALSO;FALSO,FALSO;FALSO,FALSO;FALSO,VERO;FALSO,FALSO;VERO,FALSO ;FALSO,FALSO;FALSO,VERO;FALSO,FALSO;FALSO,FALSO;FALSO,VERO;FALSO,FALSO}.
- 1*(B2:B13={"Matita","Righello"}): La moltiplicazione converte i valori logici – VERO e FALSO in 1 e 0, quindi otterrà il risultato in questo modo: {1,0;0,0;0,0;0,1;0,0;1,0;0,0 ,0,1;0,0;0,0;0,1;0,0;XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Alla fine, la funzione SUMPRODUCT somma tutti i numeri nell'array per ottenere il risultato: 5.
Contare le celle con più set di condizioni OR in Excel
A volte, potrebbe essere necessario contare le celle con due o più set di condizioni OR, in questo caso è possibile utilizzare SUM e COUNTIFS con una costante di matrice o SUMPRODUCT con le funzioni ISNUMBER MATCH.
Contare le celle con due serie di condizioni OR utilizzando le funzioni SOMMA e CONTA.PI.SE
Per gestire solo due serie di criteri OR, è sufficiente aggiungere un'altra costante di matrice alla formula CONTA.PI.SE.
Ad esempio, ho l'intervallo di dati come mostrato nell'immagine sottostante, ora voglio contare le persone che hanno ordinato "Matita" o "Righello" e l'importo è <100 o >200.
Inserisci o copia la seguente formula in una cella, quindi premi Entra tasto per ottenere il risultato, vedi screenshot:
Note:: nella formula, dovresti usare un punto e virgola per la seconda costante di matrice, che crea una matrice verticale.
Contare le celle con più set di condizioni OR utilizzando la funzione SUMPRODUCT
La formula sopra funziona solo per due set di criteri OR, se è necessario contare con più criteri, una formula SUMPRODUCT complessa insieme alle funzioni ISNUMBER MATCH può aiutarti.
Prendi ad esempio i dati seguenti, per contare le persone che hanno ordinato "Matita" o "Righello" e lo stato è "Consegnato" o "In transito" e firmato da "Bob" o "Eko", dovresti applicare una formula complessa .
Si prega di copiare o inserire la formula seguente in una cella vuota, quindi premere Entra chiave per ottenere il calcolo, vedi screenshot:
Spiegazione della formula:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
VAL.NUMERO(CONFRONTA(B2:B13,{"Matita","Righello"},0)):
- CONFRONTA(B2:B13,{"Matita","Righello"},0): Questa funzione CONFRONTA viene utilizzata per confrontare ogni cella nell'intervallo B2: B13 con la corrispondente costante di matrice. Se viene trovata la corrispondenza, restituisce una posizione relativa del valore nell'array, altrimenti viene visualizzato un valore di errore. Quindi, otterrai l'elenco degli array come questo: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# N / A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}):La funzione VAL.NUMERO converte i numeri in VERO e i valori di errore in FALSO come segue: {VERO;FALSO;FALSO;VERO;FALSO;VERO;FALSO;VERO;VERO;FALSO;VERO;FALSO}.
La logica di cui sopra può essere applicata anche alla seconda e alla terza espressione ISNUMBER.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- Quindi, questi tre array vengono moltiplicati insieme all'interno di SUMPRODUCT, che converte automaticamente i valori VERO e FALSO in 1 e 0 come parte dell'operazione matematica in questo modo: SUMPRODUCT({1;0;0;1;0;1;0;1; 1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0; 1;0;0;1;0})=SOMMAPRODOTTO({1;0;0;0;0;0;0;1;0;0;1;0}).
- Infine, la funzione SUMPRODUCT somma tutti i numeri nell'array per ottenere il risultato: 3.
Funzione relativa utilizzata:
- SOMMA:
- La funzione SOMMA di Excel restituisce la somma dei valori forniti.
- CONTA.SE:
- La funzione CONTA.SE è una funzione statistica in Excel che viene utilizzata per contare il numero di celle che soddisfano un criterio.
- SUMPRODOTTO:
- La funzione SUMPRODUCT può essere utilizzata per moltiplicare due o più colonne o matrici insieme e quindi ottenere la somma dei prodotti.
- NUMERO IS:
- La funzione VAL.NUMERO di Excel restituisce VERO quando una cella contiene un numero e FALSO in caso contrario.
- INCONTRO:
- La funzione CONFRONTA di Microsoft Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa di questo valore.
Altri articoli:
- Conta valori numerici univoci in base a criteri
- Nel foglio di lavoro di Excel, potresti soffrire di un problema che conta il numero di valori numerici univoci in base a una condizione specifica. Ad esempio, come posso contare i valori Qty univoci del prodotto "T-shirt" dal report come mostrato nell'immagine sottostante? In questo articolo, mostrerò alcune formule per svolgere questo compito in Excel.
- Contare le celle che corrispondono a due o più criteri
- In Excel, per contare il numero di celle in base a due o più criteri, la funzione CONTA.SE può aiutarti a risolvere questo compito in modo rapido e semplice.
- Contare il numero di righe con più criteri OR
- Per contare il numero di righe con più criteri in colonne diverse, con logica OR, la funzione SUMPRODUCT può aiutarti. Ad esempio, ho un rapporto sul prodotto come mostrato nell'immagine sottostante, ora voglio contare le righe in cui il prodotto è "T-shirt" o il colore è "Nero". Come gestire questa attività in Excel?
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.
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.
