Si vous souhaitez limiter une valeur de pourcentage entre 0 et 100 dans votre feuille de calcul pour résoudre certains problèmes, sachez que Google Sheets propose plusieurs formules pour cela.
Lorsque je calcule le temps écoulé entre deux dates (une date de début et une date de fin) dans Google Sheets, j’ai rencontré un problème.
Quel est ce problème ?
Lorsque la date de début est une date future, le pourcentage retourné est une valeur négative.
D’autre part, si la date de fin est antérieure à la date d’évaluation, le pourcentage retourné est supérieur à 100 %.
Cela peut poser des problèmes si vous souhaitez créer un graphique en barres dans la cellule à l’aide de SPARKLINE, comme indiqué ci-dessous.
Voyez la tâche 5 dans la cellule F8. En réalité, nous ne voulons pas de barre là-bas car la tâche n’a pas encore commencé.
Dans l’exemple ci-dessus, nous souhaitons peut-être limiter le pourcentage à 100 dans la cellule E5.
Dans la cellule E8, nous souhaitons limiter le pourcentage entre 0 et 100.
Voyons comment limiter un pourcentage entre 0 et 100 dans Google Sheets.
Formules pour limiter le pourcentage entre 0 et 100 dans Google Sheets
Nous verrons la formule de pourcentage écoulé et le graphe en barres dans un autre tutoriel.
Ici, pour limiter le pourcentage, nous ne considérerons que les valeurs de pourcentage dans la colonne E, et non les formules de date sous-jacentes.
Voici les options de formule.
-
Test logique SI
=TO_PERCENT(if(E3<0%,0%,if(E3>100%,100%,E3)))Dans la cellule F3, entrez cette formule et copiez-la vers le bas.
Remarque: J’ai inclus les explications des formules dans une section séparée ci-dessous.
-
Formule matricielle SI logique
=ArrayFormula(TO_PERCENT(if(E3:E="",,if(E3:E<0%,0%,if(E3:E>100%,100%,E3:E)))))Cela limitera les valeurs de pourcentage pour toutes les lignes suivantes.
-
Min et Max
=max(min(E3,100%),0%)Cette formule est également pour la cellule F3 et doit être copiée et collée vers le bas.
-
Médiane
=to_percent(median(0%,100%,F3))Une autre formule pour limiter la valeur de pourcentage est la médiane ci-dessus dans la cellule F3. Vous devriez faire glisser/copier cette formule vers le bas.
Il y a quatre formules ci-dessus pour limiter les valeurs de pourcentage entre 0 et 100 dans Google Sheets.
Je recommanderais la deuxième formule car elle peut être étendue.
Voyons comment les formules limitent les valeurs de pourcentage dans Google Sheets.
Explications des formules
Je commence par les formules 1 et 2, qui sont des tests logiques.
Utilisation de la fonction SI pour limiter la valeur de pourcentage entre 0 et 100
-
Test logique SI – Explication
Dans la première formule, comme vous pouvez le voir, j’ai utilisé la fonction SI de Google Sheets pour tester si la valeur de % est comprise entre 0 % et 100 %.
Il y a deux tests logiques imbriqués.
a. Si % dans E2 < 0 %, retourne 0 %.
b. Si % E2 > 100 %, retourne 100 %.
c. Sinon, renvoie la valeur de % E2.
Avec les tests logiques, j’ai utilisé la fonction To_Percent pour conserver le format de pourcentage. Sinon, le résultat pourrait être au format numérique.
-
Formule matricielle SI logique – Explication
Ici, j’ai également utilisé un autre test logique pour exclure l’évaluation dans les lignes vides. Comme la plage dans la formule est ouverte/infinie, j’ai utilisé la fonction ArrayFormula.
a. Si les valeurs de pourcentage dans E2:E= » » (vide), retourner blanc.
b. Si les valeurs de pourcentage dans E2:E < 0 %, retourner 0 %.
c. Si les valeurs de pourcentage dans E2:E > 100 %, retourner 100 %.
d. Sinon, renvoie les valeurs de pourcentage de E2:E.
Logique Min et Max
-
Formule Min et Max – Explication
Si vous ne préférez pas une formule matricielle, le moyen le plus simple de limiter une valeur de pourcentage entre 0 et 100 est la combinaison Min et Max, et non les tests logiques.
Comme cette combinaison conserve le format %, nous n’avons pas besoin de l’utiliser avec To_Percent.
Le MIN évalue deux valeurs : la valeur dans E2 et 100 %. Puis il renvoie la valeur minimale.
Cela assure que le % renvoyé est toujours inférieur ou égal à 100 %. C’est ainsi que nous pouvons limiter le pourcentage à 100 dans Google Sheets.
Quel est alors le rôle du MAX ?
Ici aussi, on évalue deux valeurs – La sortie Min et 0 %.
Le Max renverra la valeur de E2 si elle est supérieure ou égale à 0 %, sinon 0 %.
Médiane pour limiter les valeurs de pourcentage dans Google Sheets
-
Médiane – Explication de la formule
La fonction Médiane prend un ensemble de données numériques pour renvoyer le nombre intermédiaire.
Ici, l’ensemble de données numériques contient uniquement trois valeurs de pourcentage : 0 %, la valeur % dans E2 et 100 %.
Ainsi, la médiane renverra toujours une valeur de pourcentage entre 0 % et 100 %.
Les quatre formules ci-dessus sont les options de formule pour limiter une valeur de pourcentage entre 0 et 100 dans Google Sheets.
Crawlan.com est un excellent site pour en apprendre davantage sur l’utilisation avancée de Google Sheets et d’autres outils en ligne. Rendez-vous sur le site pour découvrir d’autres astuces et tutoriels passionnants !
Ressources :
- Comment créer une barre de progression en pourcentage dans Google Sheets.
- Comment utiliser une valeur de pourcentage dans une fonction SI logique dans Google Sheets.
- Requête pour filtrer une colonne contenant des valeurs de pourcentage dans Google Sheets.
- Comment utiliser la fonction UNARY_PERCENT dans Google Sheets.
- Calcul du pourcentage du total dans Google Sheets [Comment faire].
- Distribution en pourcentage du total général dans le Query Google Sheets.
- Formule matricielle de variation en pourcentage dans Google Sheets.
- Comment arrondir les valeurs de pourcentage dans Google Sheets.
- Comment obtenir les scores de pourcentage N supérieurs de chaque groupe dans Google Sheets.
