Sommario
Per contare le righe con la logica OR, puoi utilizzare una formula basata sulla funzione SUMPRODUCT. Nell'esempio mostrato, la formula in G6 è:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
dove group (B5: B15), color1 (C5: C15) e color2 (D5: D15) sono intervalli denominati.
Spiegazione
Uno dei problemi più complicati in Excel è contare le righe in un insieme di dati con "logica OR". Esistono due scenari di base: (1) si desidera contare le righe in cui un valore in una colonna è "x" O "y" (2) si desidera contare le righe in cui esiste un valore, "x", in una colonna O in un'altra .
In questo esempio, l'obiettivo è contare le righe in cui group = "a" AND Color1 OR Color2 sono "rosse". Ciò significa che stiamo lavorando con lo scenario 2 sopra.
Con COUNTIFS
All'inizio potresti raggiungere la funzione CONTA.SE, che gestisce più criteri in modo nativo. Tuttavia, la funzione COUNTIFS unisce le condizioni con la logica AND, quindi tutti i criteri devono essere TRUE per essere inclusi nel conteggio:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Ciò rende COUNTIFS impraticabile, a meno che non utilizziamo più istanze di COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Traduzione: conta le righe dove il gruppo è "a" e color1 è "rosso" + conta le righe dove il gruppo è "a" e color2 è "rosso" - conta le righe dove il gruppo è "a" e color1 è "rosso" e color2 è " rosso "(per evitare il doppio conteggio).
Funziona, ma puoi vedere che questa è una formula un po 'complicata e ridondante.
Con logica booleana
Una soluzione migliore consiste nell'usare la logica booleana ed elaborare il risultato con la funzione SUMPRODUCT. (Se hai bisogno di un manuale sull'algebra booleana, questo video fornisce un'introduzione.) Nell'esempio mostrato, la formula in G6 è:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
dove group (B5: B15), color1 (C5: C15) e color2 (D5: D15) sono intervalli denominati.
La prima parte del problema è testare per group = "a" che ci piace in questo modo:
(group="a")
Poiché l'intervallo B5: B15 contiene 11 celle, questa espressione restituisce una matrice di 11 valori VERO e FALSO come questo:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Ogni TRUE rappresenta una riga in cui il gruppo è "A".
Successivamente, dobbiamo verificare il valore "rosso" in colonna1 o colonna2. Lo facciamo con due espressioni unite da addizione (+), poiché l'addizione corrisponde alla logica OR in algebra booleana:
(color1="red")+(color2="red")
Excel valuta automaticamente i valori VERO e FALSO come 1 e 0 durante qualsiasi operazione matematica, quindi il risultato dell'espressione sopra è un array come questo:
(2;0;0;1;1;0;1;0;0;0;1)
Il primo numero nella matrice è 2, perché sia Color1 che Color2 sono "rossi" nella prima riga. Per i motivi spiegati di seguito, dobbiamo proteggerci da questa situazione verificando valori maggiori di zero:
((2;0;0;1;1;0;1;0;0;0;1))>0
Ora abbiamo di nuovo un array di valori TRUE e FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
La tabella seguente riassume come Excel valuta la logica del colore spiegata sopra:
A questo punto, abbiamo i risultati del test Group = "a" in un array:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
E risultati dal test "rosso" in Color1 o Color2 in un altro array:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Il passaggio successivo consiste nell'unire questi due array con la "logica AND". Per fare ciò, usiamo la moltiplicazione (*), poiché la moltiplicazione corrisponde alla logica AND in algebra booleana.
Dopo aver moltiplicato i due array insieme, abbiamo un singolo array di 1 e 0, che viene consegnato direttamente alla funzione SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
La funzione SUMPRODUCT restituisce la somma dei numeri, 2, come risultato finale. Questo è il conteggio delle righe in cui group = "a" AND Color1 OR Color2 sono "red".
Per evitare il doppio conteggio
Non vogliamo conteggiare due volte le righe in cui sia Color1 che Color2 sono "rossi". Questo è il motivo per cui controlliamo i risultati di (color1 = "red") + (color2 = "red") per i valori maggiori di zero nel codice seguente:
((color1="red")+(color2="red"))>0
Senza questo controllo, il 2 della prima riga nei dati verrebbe visualizzato nell'array finale e la formula restituirà erroneamente 3 come conteggio finale.
Opzione FILTRO
Una cosa bella della logica booleana è che funziona perfettamente con le nuove funzioni di Excel, come XLOOKUP e FILTER. Ad esempio, la funzione FILTER può utilizzare esattamente la stessa logica spiegata sopra per estrarre le righe corrispondenti:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Il risultato di FILTER sono le due righe che soddisfano i criteri, come mostrato di seguito:
Se desideri saperne di più su queste nuove funzioni, abbiamo una panoramica e video di formazione.
