Vai al contenuto principale

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 è:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • 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 Enter tasto per ottenere il risultato:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))


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.

Suggerimenti:: 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:

=SUM(COUNTIF(B2:B13,D2:D3))


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 è:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • 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 Enter chiave per restituire il risultato:

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))


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 Enter tasto per ottenere il risultato, vedi screenshot:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

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 Enter chiave per ottenere il calcolo, vedi screenshot:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))


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 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 for Excel - Ti aiuta a distinguerti dalla massa

Funzioni popolari: Trova, evidenzia o identifica i duplicati  |  Elimina righe vuote  |  Combina colonne o celle senza perdere dati  |  Round senza formula ...
Super VLookup: Criteri multipli  |  Valore multiplo  |  Su più fogli  |  Ricerca fuzzy...
Avv. Menu `A tendina: Elenco a discesa facile  |  Elenco a discesa dipendente  |  Elenco a discesa a selezione multipla...
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  Confronta colonne con Seleziona Stesse celle e diverse ...
Funzionalità in primo piano: Messa a fuoco della griglia  |  Vista di progettazione  |  Grande barra delle formule  |  Gestore di cartelle di lavoro e fogli | Resource Library (Testo automatico)  |  Date picker  |  Combina fogli di lavoro  |  Crittografa/decrittografa le celle  |  Invia e-mail per elenco  |  Super filtro  |  Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi ...)  |  Più di 50 Grafico Tipi (Diagramma di Gantt ...)  |  40+ Pratico Formule (Calcola l'età in base al compleanno ...)  |  19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso ...)  |  12 Conversione Strumenti (Numeri in parole, Conversione di valuta ...)  |  7 Unisci e dividi Strumenti (Combina righe avanzate, Dividi celle di Excel ...)  |  ... e altro ancora

Kutools for Excel Vanta oltre 300 funzionalità, Garantirti che ciò di cui hai bisogno sia a portata di clic...


Office Tab - 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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations