Il n’est pas pratique d’utiliser plusieurs formules SMALL pour mettre en évidence les n plus petites valeurs de chaque ligne dans Google Sheets. Vous vous demandez pourquoi ?
Si vous souhaitez mettre en forme conditionnellement la plus petite ou les deux plus petites valeurs, il est alors possible d’utiliser la formule SMALL deux fois.
Je veux dire que nous pouvons utiliser 2 formules SMALL pour mettre en évidence les deux plus petites valeurs. Mais qu’en est-il de la mise en évidence des dix plus petites valeurs de chaque ligne ?
Pour cela, vous devrez peut-être utiliser 10 règles de mise en forme conditionnelle basées sur dix formules SMALL différentes. Pas pratique, n’est-ce pas ?
Dans cet article, je vais vous présenter ma formule (une seule règle de mise en forme conditionnelle) pour mettre en évidence les n plus petites valeurs de chaque ligne dans Google Sheets.
Je propose deux versions de la même formule. Une pour inclure 0 et une autre pour exclure 0 du calcul. Veuillez noter que dans mon exemple, n est égal à 3.
Sans perdre de temps, commençons le tutoriel.
Mettre en évidence les N plus petits nombres, y compris les zéros, dans Google Sheets
Pour ce test, les nombres à mettre en évidence, y compris les 0, se trouvent dans la plage B2:J8. Sélectionnez donc cette plage et entrez la formule suivante dans le volet de mise en forme conditionnelle, comme indiqué sur la capture d’écran juste en dessous de la formule.
=regexmatch(B2 & "", "^" & textjoin("$|^", 1, iferror(ArrayFormula(small($B2:$J2, sequence(1, 3))))) & "$")Dans la formule ci-dessus, n est égal à 3. Cela signifie que la formule met en forme conditionnellement les cellules contenant les trois plus petites valeurs de chaque ligne.
Pour changer de 3 à 5 plus petites valeurs, modifiez la partie sequence(1, 3) de la formule en sequence(1, 5). J’espère que vous comprenez maintenant comment modifier n dans la formule.
Voyons comment la formule met en évidence les plus petites valeurs de chaque ligne dans Google Sheets. Voici une explication de la formule.
Logique – Correspond aux valeurs Min | N plus petites valeurs à l’aide de Regexmatch
Résultat du tableau Small
Supposons que n soit égal à 3. Sans utiliser plusieurs formules SMALL, nous pouvons obtenir les trois plus petites valeurs dans Google Sheets de la manière suivante :
=IFERROR(ArrayFormula(small($B2:$J2, sequence(1, 3))))En fait, la fonction SEQUENCE alimente les nombres 1, 2 et 3 à SMALL. La formule unique ci-dessus est équivalente aux trois formules suivantes : small($B2:$J2,1), small($B2:$J2,2) et small($B2:$J2,3).
Nous voulons plusieurs nombres, c’est-à-dire les 3 plus petites valeurs, en utilisant une seule formule. Nous avons donc besoin de la fonction ArrayFormula avec SMALL.
Qu’en est-il de l’utilisation de IFERROR avec SMALL ?
Si l’une des lignes contient moins de 3 nombres (moins de n nombres), alors la formule renverrait une erreur #NUM!.
Par exemple, si une ligne ne contient que 2 nombres, alors la formule renverra les deux plus petites valeurs et une erreur #NUM!.
La formule IFERROR rend cette erreur vide. Sinon, la mise en forme conditionnelle omettrait cette ligne lors de la mise en forme.
Textjoin pour joindre les N plus petites valeurs
La fonction TEXTJOIN convertit la sortie du tableau SMALL en une « expression régulière » regex (veuillez vous référer à la syntaxe Regexmatch ci-dessous) pour faire correspondre chaque cellule de la ligne $B2:$J2.
Syntaxe : REGEXMATCH(texte, expression_régulière)
Voici la formule utilisée pour cette « expression régulière ».
"^" & textjoin("$|^", 1, iferror(ArrayFormula(small($B2:$J2, sequence(1, 3))))) & "$"Le « texte » dans Regexmatch correspondant à l’aide de cette « expression régulière » est le nombre dans la cellule B2 (la première cellule de la plage de mise en forme).
Nous ne pouvons pas utiliser B2 comme argument « texte » dans Regexmatch. Au lieu de cela, nous l’utilisons comme texte en ajoutant un caractère nul à l’aide de B2 & « » (voir formule #1).
Les cellules contenant les nombres correspondants sont mises en évidence.
Règles multiples pour mettre en évidence les 3 plus petites valeurs, y compris zéro
Au lieu d’utiliser la formule unique ci-dessus, nous pouvons utiliser les trois formules suivantes en tant que trois règles distinctes (formules personnalisées) dans la mise en forme conditionnelle.
=et(nbcar(B2), B2 = small($B2:$J2, 1))
=et(nbcar(B2), B2 = small($B2:$J2, 2))
=et(nbcar(B2), B2 = small($B2:$J2, 3))Si vous souhaitez mettre en évidence la 4e plus petite valeur, vous devez ajouter une règle supplémentaire. Je ne recommande donc pas cette méthode.
Mettre en évidence les N plus petites valeurs en excluant les zéros dans Google Sheets
Voyons comment mettre en évidence (appliquer une couleur de remplissage) les n plus petites valeurs dans la plage B11:J17 en excluant les zéros dans chaque ligne de Google Sheets.
Voici la formule pour cela.
=regexmatch(B2 & "", "^" & textjoin("$|^", 1, iferror(ArrayFormula(small(filter($B11:$J11, $B11:$J11 > 0), sequence(1, 3))))) & "$")La formule #2 ci-dessus présente une seule différence majeure par rapport à la formule #1. Quelle est cette différence ?
Dans la formule #2, les données de la formule SMALL (veuillez vous référer à la syntaxe ci-dessous) sont filtrées pour exclure les valeurs 0.
Syntaxe : SMALL(data, n)
Voici la partie de la formule SMALL. La référence de plage $B11:$J11 telle que dans la formule #1 (là, c’est $B2:$J2) est remplacée par filter($B11:$J11, $B11:$J11 > 0).
=IFERROR(ArrayFormula(small(filter($B11:$J11, $B11:$J11 > 0), sequence(1, 3))))Comme il n’y a pas d’autres changements, je vais passer la (suite de la) partie d’explication de la formule.
Règles multiples alternatives pour mettre en forme conditionnelle Min 3 en excluant zéro
Pour mettre en évidence les 3 plus petites valeurs en excluant zéro dans Google Sheets, vous pouvez également utiliser les formules suivantes en tant que 3 règles distinctes dans la mise en forme conditionnelle.
=et(nbcar(B2), B2 = small(filter($B2:$J2, $B2:$J2 > 0), 1))
=et(nbcar(B2), B2 = small(filter($B2:$J2, $B2:$J2 > 0), 2))
=et(nbcar(B2), B2 = small(filter($B2:$J2, $B2:$J2 > 0), 3))Ce n’est pas idéal si n est supérieur à trois. Il est donc préférable de rester avec la formule #2, qui est plus adaptée pour traiter n en excluant les 0. C’est tout. Amusez-vous bien !
Ressources :
- Comment mettre en évidence la valeur maximale d’une ligne dans Google Sheets
- Comment mettre en évidence les 3 plus grandes valeurs de chaque ligne dans Google Sheets
- Mettre en forme conditionnellement les deux valeurs maximales en fonction d’un critère dans Google Sheets
- Mettre en évidence le min en excluant les zéros et les cellules vides dans Google Sheets
