Formule générique
=MAX(0,MIN(A1,1))
Sommaire
Pour limiter une valeur de pourcentage afin qu'elle soit comprise entre 0% et 100%, vous pouvez utiliser une formule basée sur les fonctions MIN et MAX. Dans l'exemple illustré, la formule en C5, copiée vers le bas, est:
=MAX(0,MIN(B5,1))
Le résultat est que les valeurs négatives sont forcées à zéro, les valeurs supérieures à 1 sont plafonnées à 1 et les valeurs comprises entre 0 et 1 ne sont pas affectées.
Remarque: toutes les valeurs sont formatées au format de nombre de pourcentage
Explication
Afin de comprendre ce problème, assurez-vous de comprendre comment fonctionne le formatage des nombres en pourcentage. En un mot, les pourcentages sont des valeurs décimales: 0,1 est 10%, 0,2 est 20%, et ainsi de suite. Le nombre 1, lorsqu'il est formaté en pourcentage, est 100%. Plus d'informations sur les formats de nombres ici.
Le but de cet exemple est de limiter les valeurs de pourcentage entrantes afin qu'elles tombent entre un seuil supérieur et inférieur. Les valeurs négatives et les valeurs supérieures à 100% ne sont pas autorisées, le résultat final doit donc être un nombre compris entre zéro et 1 (0-100%), inclus.
Bien que la fonction IF puisse être utilisée pour résoudre ce problème (voir ci-dessous), le résultat sera un peu plus long et redondant. Au lieu de cela, l'exemple illustré utilise une combinaison des fonctions MIN et MAX dans une formule très compacte:
=MAX(0,MIN(B5,1))
Voici un exemple d'imbrication - la fonction MIN est imbriquée dans la fonction MAX. L'imbrication est un élément clé pour des formules plus avancées.
En travaillant de l'intérieur vers l'extérieur, la fonction MIN est utilisée pour limiter les valeurs entrantes à 1 comme ceci:
MIN(B5,1) // get smaller value
Traduction: renvoie le plus petit de B5 et 1. Pour toute valeur supérieure à 1, la valeur de B5 est renvoyée. Dans l'exemple, B5 contient -5% (-0,05), donc MIN renvoie -0,05. Ce résultat est renvoyé directement à la fonction MAX:
=MAX(0,-0.05) // get larger value
Ici, on voit la formule faire son travail. Étant donné que zéro est supérieur (supérieur) à -0,05, MAX renvoie zéro comme résultat final. La valeur d'origine est ignorée.
Fonction IF
Comme mentionné ci-dessus, la fonction IF peut également être utilisée pour résoudre ce problème. Pour ce faire, nous avons besoin de deux fonctions IF distinctes. Un IF force les valeurs négatives à zéro:
IF(B5<0,0,B5) // cap at zero
Le deuxième IF plafonne les valeurs plus grandes à 1:
=IF(B5>1,1,B5) // cap at 1
Lorsque nous imbriquons le premier IF dans le second, nous avons la formule finale:
=IF(B5>1,1,IF(B5<0,0,B5))
Ceci est un exemple de IF imbriqué. Il renvoie exactement le même résultat que les formules MIN et MAX ci-dessus, mais est légèrement plus complexe et redondant. Remarquez, par exemple, que la référence à B5 se produit trois fois.
L'essentiel - lorsque vous devez faire un choix basé sur des valeurs plus petites ou plus grandes, les fonctions MIN et MAX peuvent être un moyen intelligent et élégant de garder une formule simple.
Fonction MEDIAN
OK, maintenant que nous avons parlé d'imbrication, et parlé de l'élégance de MIN avec MAX, je dois mentionner qu'il est possible de résoudre ce problème sans aucune imbrication du tout avec la fonction MEDIAN. La version générique de la formule ressemble à ceci:
=MEDIAN(0,1,A1)
Cela fonctionne car la fonction MEDIAN renvoie la médiane (nombre du milieu) dans un groupe de nombres. Lorsqu'une valeur est négative, zéro devient le nombre du milieu. Lorsqu'un nombre est supérieur à 1, 1 devient le nombre du milieu. Intelligent!
Cependant, notez que MEDIAN ne renvoie le nombre du milieu que lorsque le nombre total de valeurs est impair. Si le nombre de valeurs est pair, MEDIAN renvoie la moyenne des deux nombres du milieu. Par conséquent, si la cellule cible (A1) est vide, MEDIAN renverra la moyenne de 1 et zéro, qui est de 0,5 ou 50% lorsqu'elle est formatée en pourcentage.
