Formula generica
=MAX(0,MIN(A1,1))
Sommario
Per limitare un valore percentuale in modo che sia compreso tra 0% e 100% è possibile utilizzare una formula basata sulle funzioni MIN e MAX. Nell'esempio mostrato, la formula in C5, copiata verso il basso, è:
=MAX(0,MIN(B5,1))
Il risultato è che i valori negativi vengono forzati a zero, i valori superiori a 1 vengono limitati a 1 e i valori compresi tra 0 e 1 non vengono modificati.
Nota: tutti i valori sono formattati con il formato numerico percentuale.
Spiegazione
Per comprendere questo problema, assicurati di capire come funziona la formattazione del numero percentuale. In poche parole, le percentuali sono valori decimali: 0,1 è il 10%, 0,2 è il 20% e così via. Il numero 1, se formattato come percentuale, è 100%. Maggiori informazioni sui formati numerici qui.
L'obiettivo di questo esempio è limitare i valori percentuali in entrata in modo che rientrino in una soglia superiore e inferiore. Valori negativi e valori superiori al 100% non sono consentiti, quindi il risultato finale deve essere un numero compreso tra zero e 1 (0-100%), inclusi.
Sebbene la funzione IF possa essere utilizzata per risolvere questo problema (vedi sotto), il risultato sarà leggermente più lungo e ridondante. Invece, l'esempio mostrato utilizza una combinazione delle funzioni MIN e MAX in una formula molto compatta:
=MAX(0,MIN(B5,1))
Questo è un esempio di nidificazione: la funzione MIN è nidificata all'interno della funzione MAX. La nidificazione è un elemento fondamentale per le formule più avanzate.
Lavorando dall'interno verso l'esterno, la funzione MIN viene utilizzata per limitare i valori in entrata a 1 in questo modo:
MIN(B5,1) // get smaller value
Traduzione: restituisce il minore tra B5 e 1. Per qualsiasi valore superiore a 1, viene restituito il valore in B5. Nell'esempio, B5 contiene -5% (-0,05), quindi MIN restituisce -0,05. Questo risultato viene restituito direttamente alla funzione MAX:
=MAX(0,-0.05) // get larger value
Qui vediamo la formula fare il suo lavoro. Poiché zero è maggiore (maggiore) di -0,05, MAX restituisce zero come risultato finale. Il valore originale viene scartato.
Funzione SE
Come accennato in precedenza, la funzione IF può essere utilizzata anche per risolvere questo problema. Per fare ciò, abbiamo bisogno di due funzioni IF separate. Un IF forza i valori negativi a zero:
IF(B5<0,0,B5) // cap at zero
Il secondo IF limita i valori maggiori a 1:
=IF(B5>1,1,B5) // cap at 1
Quando annidiamo il primo IF all'interno del secondo, abbiamo la formula finale:
=IF(B5>1,1,IF(B5<0,0,B5))
Questo è un esempio di IF nidificato. Restituisce esattamente lo stesso risultato della formula MIN e MAX sopra, ma è leggermente più complesso e ridondante. Si noti, ad esempio, che il riferimento a B5 si verifica tre volte separate.
Conclusione: quando è necessario effettuare una scelta basata su valori più piccoli o più grandi, le funzioni MIN e MAX possono essere un modo intelligente ed elegante per mantenere una formula semplice.
Funzione MEDIAN
OK, ora che abbiamo parlato di nesting e abbiamo parlato dell'eleganza di MIN con MAX, devo dire che è possibile risolvere questo problema senza alcun nesting con la funzione MEDIAN. La versione generica della formula è simile a questa:
=MEDIAN(0,1,A1)
Questo funziona perché la funzione MEDIANA restituisce la mediana (numero medio) in un gruppo di numeri. Quando un valore è negativo, lo zero diventa il numero centrale. Quando un numero è maggiore di 1, 1 diventa il numero centrale. Intelligente!
Tuttavia, la nota MEDIANA restituisce solo il numero centrale quando il numero totale di valori è dispari. Se il numero di valori è pari, MEDIANTE restituisce la media dei due numeri al centro. Di conseguenza, se la cella di destinazione (A1) è vuota, MEDIANTE restituirà la media di 1 e zero, che è 0,5 o 50% se formattata come percentuale.
