Formula generica
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Sommario
Per ottenere il valore massimo in un set di dati in base a più di un criterio, è possibile utilizzare una formula di matrice basata sulle funzioni MAX e IF. Nell'esempio mostrato, la formula in I6 è:
(=MAX(IF(color=G6,IF(item=H6,price))))
Con un colore "rosso" e l'elemento "cappello" il risultato è $ 11,00
Nota: questa è una formula di matrice e deve essere inserita utilizzando Ctrl + Maiusc + inserito
Spiegazione
In questo esempio vengono utilizzati i seguenti intervalli denominati: "color" = B6: B14, "item" = C6: C14 e "price" = E6: E14. L'obiettivo è trovare il prezzo massimo per un determinato colore e oggetto.
Questa formula utilizza due funzioni IF annidate, racchiuse in MAX per restituire il prezzo massimo con due criteri. A partire da un test logico della prima istruzione IF, color = G6, i valori nell'intervallo denominato "color" (B6: B14) vengono confrontati con il valore nella cella G6, "red". Il risultato è un array come questo:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Nel test logico per la seconda istruzione IF, item = H6, i valori nell'elemento di intervallo denominato (C6: C14) vengono confrontati con il valore nella cella H6, "hat". Il risultato è un array come questo:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
Il "valore se vero" per la seconda istruzione IF è l'intervallo denominato "prezzi" (E6: E14), che è un array come questo:
(11;8;9;12;9;10;9;8;7)
Viene restituito un prezzo per ogni articolo in questo intervallo solo quando il risultato dei primi due array sopra è VERO per gli articoli nelle posizioni corrispondenti. Nell'esempio mostrato, l'array finale all'interno di MAX ha questo aspetto:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Nota che gli unici prezzi che "sopravvivono" sono quelli in una posizione in cui il colore è "rosso" e l'articolo è "cappello".
La funzione MAX restituisce quindi il prezzo più alto, ignorando automaticamente i valori FALSE.
Sintassi alternativa che utilizza la logica booleana
È inoltre possibile utilizzare la seguente formula di matrice, che utilizza solo una funzione IF insieme alla logica booleana:
(=MAX(IF((color=G6)*(item=H6),price)))
Il vantaggio di questa sintassi è che è più semplice aggiungere criteri aggiuntivi senza aggiungere ulteriori funzioni IF nidificate. Se hai bisogno della logica OR, usa l'addizione invece della moltiplicazione tra le condizioni.
Con MAXIFS
La funzione MAXIFS, introdotta in Excel 2016, è progettata per calcolare i massimi in base a uno o più criteri senza la necessità di una formula di matrice. Con MAXIFS, la formula in I6 è:
=MAXIFS(price,color,G6,item,H6)
Nota: MAXIFS ignorerà automaticamente le celle vuote che soddisfano i criteri. In altre parole, MAXIFS non tratterà le celle vuote che soddisfano i criteri come zero. D'altra parte, MAXIFS restituirà zero (0) se nessuna cella corrisponde ai criteri.
