Un problema che emerge molto in Excel è il conteggio o la somma in base a più condizioni OR. Ad esempio, forse è necessario analizzare i dati e contare gli ordini a Seattle o Denver, per gli articoli che sono rossi, blu o verdi? Questo può essere sorprendentemente complicato, quindi naturalmente è una bella sfida!
La sfida
I dati seguenti rappresentano gli ordini, un ordine per riga. Ci sono tre sfide separate.
Quali formule in F9, G9 e H9 conteggeranno correttamente gli ordini con le seguenti condizioni:
- F9 - Maglietta o felpa con cappuccio
- G9 - (maglietta o felpa con cappuccio) e (rosso, blu o verde)
- H9 - (Maglietta o felpa con cappuccio) e (Rosso, Blu o Verde) e (Denver o Seattle)
L'ombreggiatura verde viene applicata con la formattazione condizionale e indica i valori corrispondenti per ogni set di criteri OR in ogni colonna.
Per comodità, sono disponibili i seguenti intervalli denominati:
item = B3: B16
color = C3: C16
city = D3: D16
Il foglio di lavoro è allegato. Lascia le tue risposte qui sotto come commenti!
Rispondi (fai clic per espandere)La mia soluzione utilizza SUMPRODUCT con ISNUMBER e MATCH in questo modo:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Che conterà gli ordini in cui …
- L'articolo è (maglietta o felpa con cappuccio) e
- Il colore è (rosso, blu o verde) e
- La città è (Denver o Seattle)
Diverse persone hanno anche suggerito lo stesso approccio. Mi piace questa struttura perché si ridimensiona facilmente per gestire più criteri e funziona anche con i riferimenti di cella (invece dei valori hard-coded). Con i riferimenti di cella, la formula in H9 è:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
La chiave di questa formula è la costruzione ISNUMBER + MATCH. MATCH è impostato "all'indietro": i valori di ricerca provengono dai dati e i criteri vengono utilizzati per l'array. Il risultato è un array di colonne singole ogni volta che viene utilizzato MATCH. Questo array contiene # N / D errori (nessuna corrispondenza) o numeri (corrispondenza), quindi ISNUMBER viene utilizzato per convertire i valori booleani VERO e FALSO. L'operazione di moltiplicazione delle matrici insieme forza i valori VERO FALSO a 1 e 0 e la matrice finale all'interno di SUMPRODUCT contiene 1 in cui le righe soddisfano i criteri. SUMPRODUCT quindi somma la matrice e restituisce il risultato.
