Conteggio con logica OR per criteri multipli in Excel
Normalmente, puoi utilizzare la funzione CONTA.PIÙ.SE per contare le celle basandoti su una o più condizioni con logica AND in Excel. Ti è mai capitato di trovarti in una situazione in cui devi contare più di un valore da una singola colonna o da un intervallo di celle? Questo significa contare con criteri multipli e logica OR. In questo caso, puoi applicare insieme le funzioni SOMMA e CONTA.PIÙ.SE oppure utilizzare la funzione MATR.SOMMA.PRODOTTO.
Conta celle con condizioni OR in Excel
Ad esempio, ho un intervallo di dati come mostrato nello screenshot sottostante, ora voglio contare il numero di prodotti che sono "Matita" o "Righello", qui discuterò due formule per risolvere questo compito in Excel.
Conta celle con condizioni OR utilizzando le funzioni SOMMA e CONTA.PIÙ.SE
In Excel, per contare con più condizioni OR, puoi utilizzare le funzioni SOMMA e CONTA.PIÙ.SE con una costante di matrice; la sintassi generica è:
- intervallo: L'intervallo di dati contiene i criteri da cui vuoi contare le celle;
- criterio1, criterio2, criterio3…: Le condizioni in base alle quali vuoi contare le celle.
Per contare il numero di prodotti che sono "Matita" o "Righello", copia o inserisci la seguente formula in una cella vuota, quindi premi il tasto Invio per ottenere il risultato:
Spiegazione della formula:
=SOMMA(CONTA.SE(B2:B13;{"Matita";"Righello"}))
- {"Matita";"Righello"}: Prima di tutto, devi inserire tutte le condizioni in una costante di matrice come questa: {"Matita";"Righello"}, separando gli elementi con punti e virgola.
- CONTA.SE(B2:B13;{"Matita";"Righello"}): Questa funzione CONTA.SE otterrà conteggi individuali per "Matita" e "Righello", e otterrai il risultato come questo: {2;3}.
- SOMMA(CONTA.SE(B2:B13;{"Matita";"Righello"}))=SOMMA({2;3}): Infine, questa funzione SOMMA somma tutti gli elementi nell'array e restituisce il risultato: 5.
Suggerimenti: Puoi anche utilizzare riferimenti di cella per i criteri, applica la seguente formula di matrice, quindi premi contemporaneamente i tasti Ctrl + Maiusc + Invio per ottenere il risultato corretto:
Conta celle con condizioni OR utilizzando la funzione MATR.SOMMA.PRODOTTO
Ecco un'altra formula creata dalla funzione MATR.SOMMA.PRODOTTO che può aiutare a contare le celle con logica OR. La sintassi generica è:
- intervallo: L'intervallo di dati contiene i criteri da cui vuoi contare le celle;
- criterio1, criterio2, criterio3…: Le condizioni in base alle quali vuoi contare le celle.
Copia o inserisci la seguente formula in una cella vuota e premi il tasto Invio per ottenere il risultato:
Spiegazione della formula:
=MATR.SOMMA.PRODOTTO(1*(B2:B13={"Matita";"Righello"}))
- B2:B13={"Matita";"Righello"}: Questa espressione confronta ogni criterio "Matita", "Righello" con l'intervallo delle celle B2:B13. Se il criterio è soddisfatto, restituisce VERO, altrimenti FALSO, otterrai il risultato come questo: {VERO;FALSO;FALSO;FALSO;FALSO;VERO;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 otterrai il risultato come questo: {1;0;0;0;0;1;0;0;0;1;0;0}.
- MATR.SOMMA.PRODOTTO(1*(B2:B13={"Matita";"Righello"}))= MATR.SOMMA.PRODOTTO({1;0;0;0;0;1;0;0;0;1;0;0}): Infine, la funzione MATR.SOMMA.PRODOTTO somma tutti i numeri nell'array per ottenere il risultato: 5.
Conta celle con insiemi multipli di condizioni OR in Excel
A volte, potresti aver bisogno di contare le celle con due o più insiemi di condizioni OR, in questo caso, puoi utilizzare sia SOMMA e CONTA.PIÙ.SE con una costante di matrice che MATR.SOMMA.PRODOTTO con le funzioni ISNUMBER MATCH.
Conta celle con due insiemi di condizioni OR utilizzando le funzioni SOMMA e CONTA.PIÙ.SE
Per gestire solo due insiemi di criteri OR, basta aggiungere un'altra costante di matrice alla formula CONTA.PIÙ.SE.
Ad esempio, ho l'intervallo di dati come mostrato nello screenshot 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 il tasto Invio per ottenere il risultato, vedi screenshot:
NotaNella formula, dovresti usare un punto e virgola per la seconda costante di matrice, che crea un array verticale.
Conta celle con insiemi multipli di condizioni OR utilizzando la funzione MATR.SOMMA.PRODOTTO
La formula precedente funziona solo per due insiemi di criteri OR, se hai bisogno di contare con più criteri, una formula MATR.SOMMA.PRODOTTO complessa insieme alle funzioni ISNUMBER MATCH può aiutarti.
Prendiamo i seguenti dati come esempio, 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.
Copia o inserisci la seguente formula in una cella vuota, quindi premi il tasto Invio per ottenere il calcolo, vedi screenshot:
Spiegazione della formula:
=MATR.SOMMA.PRODOTTO(NUMERO(ISNUMERO(CERCA.VERT(B2:B13;{"Matita";"Righello"};0)))*NUMERO(ISNUMERO(CERCA.VERT(C2:C13;{"Consegnato";"In transito"};0)))*NUMERO(ISNUMERO(CERCA.VERT(D2:D13;{"Bob";"Eko"};0))))
ISNUMBER(MATCH(B2:B13;{"Matita";"Righello"};0)):
- MATCH(B2:B13;{"Matita";"Righello"};0): Questa funzione MATCH viene utilizzata per confrontare ogni cella nell'intervallo B2:B13 con la corrispondente costante di matrice. Se viene trovata una corrispondenza, restituisce la posizione relativa del valore nell'array, altrimenti viene visualizzato un valore di errore. Quindi, otterrai l'elenco dell'array come questo: {1;#N/D;#N/D;2;#N/D;1;#N/D;2;1;#N/D;2;#N/D}.
- ISNUMBER(MATCH(B2:B13;{"Matita";"Righello"};0))= ISNUMBER({1;#N/D;#N/D;2;#N/D;1;#N/D;2;1;#N/D;2;#N/D}): La funzione ISNUMBER converte i numeri in VERO e i valori di errore in FALSO come questo: {VERO;FALSO;FALSO;VERO;FALSO;VERO;FALSO;VERO;VERO;FALSO;VERO;FALSO}.
La stessa logica può essere applicata anche alla seconda e terza espressione ISNUMBER.
MATR.SOMMA.PRODOTTO(ISNUMBER(MATCH(B2:B13;{"Matita";"Righello"};0))*ISNUMBER(MATCH(C2:C13;{"Consegnato";"In transito"};0))*ISNUMBER(MATCH(D2:D13;{"Bob";"Eko"};0))):
- Quindi, queste tre matrici vengono moltiplicate insieme all'interno di MATR.SOMMA.PRODOTTO, che converte automaticamente i valori VERO e FALSO in 1 e 0 come parte dell'operazione matematica in questo modo: MATR.SOMMA.PRODOTTO({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})=MATR.SOMMA.PRODOTTO({1;0;0;0;0;0;0;1;0;0;1;0}).
- Infine, la funzione MATR.SOMMA.PRODOTTO somma tutti i numeri nell'array per ottenere il risultato: 3.
Funzioni correlate utilizzate:
- SOMMA:
- La funzione SOMMA di Excel restituisce la somma dei valori forniti.
- CONTA.SE:
- La funzione CONTA.SE è una funzione statistica in Excel utilizzata per contare il numero di celle che soddisfano un criterio.
- MATR.SOMMA.PRODOTTO:
- La funzione MATR.SOMMA.PRODOTTO può essere utilizzata per moltiplicare due o più colonne o array insieme e poi ottenere la somma dei prodotti.
- VAL.NUMERO:
- La funzione VAL.NUMERO di Excel restituisce VERO quando una cella contiene un numero e FALSO se non lo contiene.
- CERCA.VERT:
- La funzione CERCA.VERT 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 Unici Basati Su Criteri
- In un foglio di lavoro Excel, potresti affrontare il problema di contare il numero di valori numerici unici basati su una condizione specifica. Ad esempio, come posso contare i valori unici di quantità del prodotto "Maglietta" dal report come mostrato nello screenshot sottostante? In questo articolo, mostrerò alcune formule per raggiungere questo compito in Excel.
- Conta Celle Che Corrispondono A Due O Più Criteri
- In Excel, per contare il numero di celle basandosi su due o più criteri, la funzione CONTA.PIÙ.SE può aiutarti a risolvere rapidamente e facilmente questo compito.
- Conta Numero Di Righe Con Criteri OR Multipli
- Per contare il numero di righe con criteri multipli in diverse colonne, con logica OR, la funzione MATR.SOMMA.PRODOTTO può aiutarti. Ad esempio, ho un report di prodotti come mostrato nello screenshot sottostante, ora voglio contare le righe dove il prodotto è "Maglietta" o il colore è "Nero". Come 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.