Sommario
In alcuni casi, puoi utilizzare SUMIFS come una formula di ricerca per recuperare un valore numerico. Nell'esempio mostrato, la formula in G6 è:
=SUMIFS(sales,region,G4,quarter,G5)
dove regione (B5: B20), trimestre (C5: C20) e vendite (D5: D20) sono denominati intervalli.
Il risultato sono le vendite del terzo trimestre per la regione centrale, 127.250.
Spiegazione
Se sei nuovo nella funzione SUMIFS, puoi trovare una panoramica di base con molti esempi qui.
La funzione SUMIFS è progettata per sommare i valori numerici in base a uno o più criteri. In casi specifici, tuttavia, potresti essere in grado di utilizzare SUMIFS per "cercare" un valore numerico che soddisfi i criteri richiesti. Le ragioni principali per farlo sono semplicità e velocità.
Nell'esempio mostrato, abbiamo dati sulle vendite trimestrali per quattro regioni. Iniziamo dando a SUMIFS un intervallo di somma e la prima condizione, che verifica la regione per il valore in G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- La gamma della somma indica le vendite (G5: G20)
- L'intervallo di criteri 1 è la regione (B5: B20)
- Il criterio 1 è G4 ("Centrale")
Aggiungiamo quindi la seconda coppia intervallo / criteri, che controlla il trimestre:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- L'intervallo di criteri 2 è un quarto (C5: C20)
- Il criterio 2 è G5 ("Q3")
Con questi criteri, SUMIFS restituisce 127.250, il numero di vendite del terzo trimestre centrale.
Il comportamento di SUMIFS consiste nel sommare tutti i valori corrispondenti. Tuttavia, poiché esiste un solo valore corrispondente, il risultato è lo stesso del valore stesso.
Di seguito, esaminiamo diverse opzioni di formula di ricerca.
Opzioni della formula di ricerca
Questa sezione esamina brevemente altre opzioni di formula che producono lo stesso risultato. Ad eccezione di SUMPRODUCT (in basso), si tratta di formule di ricerca più tradizionali che individuano la posizione del valore di destinazione e restituiscono il valore in quella posizione.
Con CERCA.VERT
Sfortunatamente, CERCA.VERT non è una buona soluzione a questo problema. Con una colonna helper, è possibile creare una formula CERCA.VERT da abbinare a più criteri (esempio qui), ma è un processo scomodo che richiede di armeggiare con i dati di origine.
Con INDEX e MATCH
INDICE e CONFRONTA è una combinazione di ricerca molto flessibile che può essere utilizzata per tutti i tipi di problemi di ricerca e questo esempio non fa eccezione. Con INDEX e MATCH, possiamo cercare le vendite per regione e trimestre con una formula di matrice come questa:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Nota: questa è una formula di matrice e deve essere immessa con control + shift + invio.
Il trucco con questo approccio consiste nell'usare la logica booleana con operazioni di array all'interno della funzione MATCH per costruire un array di 1 e 0 come array di ricerca. Quindi possiamo chiedere alla funzione MATCH di trovare il numero 1. Una volta creato l'array di ricerca, la formula si risolve in:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Con solo 1 rimanente nella matrice di ricerca, CONFRONTA restituisce una posizione di 11 alla funzione INDICE e INDICE restituisce il numero di vendita in quella posizione, 127.250.
Per maggiori dettagli, vedere: INDICE e CONFRONTA con più criteri
Con XLOOKUP
XLOOKUP è una nuova funzione flessibile in Excel in grado di gestire gli array in modo nativo. Con XLOOKUP, possiamo utilizzare esattamente lo stesso approccio di INDEX e MATCH, utilizzando la logica booleana e le operazioni sugli array per creare un array di ricerca:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Una volta eseguite le operazioni sull'array, la formula si risolve in:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
E XLOOKUP restituisce lo stesso risultato di cui sopra, 127,250.
Altro: XLOOKUP con più criteri
Con LOOKUP
La funzione CERCA è una funzione precedente in Excel che molte persone non conoscono nemmeno. Uno dei principali punti di forza di LOOKUP è che può gestire gli array in modo nativo. Tuttavia, LOOKUP presenta alcuni punti deboli distinti:
- Non può essere bloccato in "modalità corrispondenza esatta"
- Presuppone sempre che i dati di ricerca siano ordinati, AZ
- Restituisce sempre una corrispondenza approssimativa (se non è possibile trovare una corrispondenza esatta)
Tuttavia, LOOKUP può essere utilizzato per risolvere questo problema in questo modo:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
che semplifica a:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Se la situazione non soddisfa entrambi i requisiti, SUMIFS non è una buona scelta.
