Aujourd’hui, je vais vous présenter l’utilisation de la fonction « Large » pour mettre en évidence les trois plus grandes valeurs de chaque ligne dans Google Sheets. Il y a quelque temps, je vous ai expliqué comment utiliser la fonction « Max » pour formater conditionnellement la valeur maximale de chaque ligne. Si vous ne l’avez pas vu, voici le lien : Comment mettre en évidence la valeur maximale d’une ligne dans Google Sheets. Avec la fonction « Large », nous pouvons mettre en évidence les ‘n’ plus grandes valeurs de chaque ligne. J’ai simplement mis 3 dans le titre pour expliquer la règle de mise en évidence. Voici un exemple d’utilisation de la fonction « Large » pour retourner les valeurs les plus grandes de 1, 2, 3… d’une ligne.
| A | B | C | D | |
|---|---|---|---|---|
| 1 |
Ici, la formule =large(A1:D1,1) renverra 50, et =large(A1:D1,2) renverra 45. Maintenant, voyons la syntaxe.
=large(données,n)Pour obtenir la troisième plus grande valeur de la ligne n ° 1 ci-dessus, vous devez savoir quelles modifications apporter à la formule. Sinon, voici la formule « Large » ci-dessous.
=large(A1:D1,3)Comme indiqué dans le titre, ce que nous voulons réaliser, c’est colorer les cellules contenant les trois plus grands chiffres de chaque ligne, n’est-ce pas ? Laissez-moi vous expliquer cela étape par étape.
Formule personnalisée pour mettre en évidence les valeurs maximales | plus grandes de chaque ligne – Google Sheets
Voyez les nombres mis en évidence dans chaque ligne pour comprendre de quoi je parle. Dans ce type de mise en forme conditionnelle, vous rencontrerez trois problèmes. Alors, abordons ces problèmes dans les instructions pas à pas ci-dessous.
Normalement, pour mettre en évidence les trois plus grandes valeurs de chaque ligne, vous pouvez utiliser la formule suivante dans les feuilles.
=B2:B>=large($B2:$K2,3)Pour l’appliquer, sélectionnez la plage B2:K, puis allez dans « Format » > « Mise en forme conditionnelle » et appliquez la formule ci-dessus dans le champ de formule personnalisée. Voyez les paramètres de format conditionnel mentionnés ci-dessus et l’un des problèmes que cette formule a ci-dessous.
Problème n° 1 : Aucune mise en évidence lorsque moins de trois valeurs maximales sont présentes
Veuillez vous référer à cette image pour les paramètres de format.
La formule a un problème qui se reflète dans la dernière ligne B5:K5. Quel est ce problème ? La plage B5:K5 n’est pas affectée par la mise en évidence ! Étant donné qu’il n’y a que deux valeurs dans cette ligne, la fonction « Large » ne renvoie pas la troisième plus grande valeur. Vous pouvez le vérifier par vous-même.
=large(B5:K5,3)Résultat: il renverra uniquement une erreur NUM!. Il est donc inutile de s’attendre à ce que le format conditionnel fonctionne correctement dans cette ligne. Nous pouvons résoudre ce problème de la manière suivante.
Problème n° 2 : Erreur NUM de la fonction « Large »
Remplissez toutes les cellules vides de la plage B2:K5 avec 0. Ainsi, la formule ci-dessus renverra 0 en tant que troisième plus grande valeur au lieu d’une erreur NUM!. Cela a du sens dans la mise en forme conditionnelle, mais ce n’est pas pratique dans une plage en expansion. La feuille aura un aspect désagréable si vous remplissez toutes les cellules vides d’une plage comme B2:K avec des 0. Laissez-moi vous expliquer comment résoudre cela en modifiant la formule utilisée ci-dessus pour mettre en évidence les trois plus grandes valeurs de chaque ligne.
Pour résoudre l’erreur NUM! de la fonction « Large », nous pouvons envelopper les données dans la fonction N comme ci-dessous.
=B2:B>=large(arrayformula(n($B2:$K2)),3)J’ai modifié $B2:$K2 en arrayformula(n($B2:$K2)). La fonction N est utilisée non pas avec une seule cellule, mais avec une plage. Nous ne devons donc pas oublier d’utiliser ArrayFormula ensemble.
Voyons maintenant le résultat et préparons-nous à résoudre un nouveau problème !
Problème n° 3 : Les lignes vides sont également mises en évidence lors de la mise en évidence des trois plus grandes valeurs de chaque ligne
Lorsque nous résolvons un problème de format conditionnel, un autre problème apparaît. Heureusement, c’est le dernier. Quelqu’un peut-il deviner pourquoi toutes les lignes vides sont mises en évidence ? C’est parce que la fonction N utilisée dans l’exemple précédent remplit les cellules vides avec des 0. Si toutes les valeurs d’une ligne sont égales à 0, cela signifie que les trois plus grandes valeurs sont égales à 0. Il est donc logique que l’ensemble de la ligne soit mis en évidence. Comment résoudre cette énigme de mise en évidence ? Nous pouvons utiliser l’opérateur logique « AND ».
Syntaxe:
AND(expression_logique1, [expression_logique2, ...])Dans ce cas, utilisez la formule ci-dessus comme « expression_logique2 ». Utilisez len($B2:$K2) comme « expression_logique1 ». La formule finale pour mettre en forme conditionnellement/mettre en évidence les trois plus grandes valeurs de chaque ligne dans Google Sheets sera la suivante :
=and(len($B2:$K2),B2:B>=large(arrayformula(n($B2:$K2)),3))Ressources :
- Conditional Format Max Two Values Based on Criterion in Google Sheets
- AND, OR, or NOT in Conditional Formatting in Google Sheets
- Highlight an Entire Row in Conditional Formatting in Google Sheets
- Role of Indirect Function in Conditional Formatting in Google Sheets
- How to Highlight Every Nth Row or Column in Google Sheets
- Highlight Matches or Differences in Two Lists in Google Sheets
- Highlight Intersecting Value in Google Sheets in a Two Way Lookup
- Google Sheets – Highlight the Max Value in Each Group
- Highlight Min Excluding Zero and Blank Cell in Google Sheets
