Lorsque vous mettez en évidence la valeur maximale d’une ligne en utilisant les fonctions MAX ou LARGE, elle mettra également en évidence les doublons, le cas échéant. Dans ce tutoriel, je vais vous expliquer comment mettre en évidence la valeur maximale en ignorant les doublons dans chaque ligne sur Google Sheets.
Exemples pour comprendre le scénario (Mise en évidence)
Prenons les valeurs dans la plage B2:H2 du tableau 1.
Tableau 1:
B | C | D | E | F | G | H | 2
Dans cet exemple, la valeur maximale ou la plus grande valeur est 319. Il n’y a donc aucun problème à la mettre en évidence.
Sélectionnez la plage B2:H2 et appliquez la formule LARGE suivante pour mettre en évidence la valeur maximale ou la plus grande valeur.
=B2=LARGE($B2:$H2,1)
Ceci est une autre façon de représenter la valeur maximale.
=B2=MAX($B2:$H2)
Mais la deuxième plus grande valeur (Large 2) est 318, qui se répète dans les cellules E2 et G2.
Donc normalement, la formule suivante mettrait en évidence ces deux cellules si vous mettez en évidence la valeur Large 2 (Max 2).
=B2=LARGE($B2:$H2,2)
Mais je veux simplement mettre en évidence la cellule E2, qui est la première apparition de la deuxième plus grande valeur dans cette ligne.
De même, si la plus grande troisième valeur a un doublon, je ne veux simplement pas mettre en évidence cette valeur en double. Voyez la ligne 3 du tableau ci-dessus.
Tableau 2:
B | C | D | E | F | G | H | 3
La formule suivante (utilisation normale ou standard) mettrait en évidence les cellules D3 et E3. Je veux simplement mettre en évidence uniquement la cellule D3 (première apparition).
=B2=LARGE($B2:$H2,3)
Dans ce tutoriel Google Sheets, vous allez obtenir 3 règles (formules de mise en forme conditionnelle) pour mettre en évidence les valeurs Max 1, Max 2 et Max 3 en ignorant les doublons ligne par ligne.
Si vous utilisez les 3 règles, j’ai quelque chose de plus à clarifier. Voici comment faire.
Mise en évidence de la valeur maximale sans doublons – Points à savoir
Si je veux mettre en évidence les valeurs Max 1, Max 2 et Max 3, selon la ligne 3 (deuxième tableau ci-dessus), les cellules à mettre en évidence sont B3, C3 et D3. Dans la ligne 2 (premier tableau), ce serait B2, E2, G2.
Cela signifie que :
Lorsque je dis « mettre en évidence la valeur maximale en ignorant les doublons », cela signifie « en ignorant les doublons de Max 1, Max 2 et Max 3 individuellement ».
Si vous voulez simplement mettre en évidence Max 2, si Max 1 et Max 2 ont la même valeur, la deuxième cellule contenant la valeur sera mise en évidence et une troisième valeur identique sera ignorée.
Cela signifie que si vous voulez utiliser trois règles pour mettre en évidence Max 1, Max 2 et Max 3 en ignorant les doublons, alors la mise en évidence sera limitée à 3 cellules dans cette ligne. Les valeurs peuvent être des valeurs en double ou non.
Veuillez consulter l’image ci-dessous pour plus de clarté.
Étapes pour mettre en évidence la valeur maximale en ignorant les doublons sur Google Sheets
Pour mettre en évidence la valeur maximale en ignorant les doublons comme décrit ci-dessus, nous pouvons adopter la méthode de correspondance des adresses de cellules. Nous allons faire correspondre les adresses des cellules de la plage B2:H2 avec l’adresse de la cellule contenant la valeur maximale/large requise.
Voici comment procéder en 5 étapes. À l’étape 5, vous obtiendrez les formules pour mettre en évidence la valeur maximale en ignorant les doublons sur Google Sheets.
Trouver la valeur maximale/large (étape 1)
Note : Pour les données d’exemple, veuillez vous référer à l’image ci-dessus. La plage est B2:H. Ne conservez que les lignes dont vous avez besoin pour votre projet afin d’améliorer les performances.
Étant donné que nous allons utiliser la formule dans la mise en forme conditionnelle, nous ne voulons écrire la formule que pour la première ligne (B2:H2).
Lors de l’application de la formule, sélectionnez simplement la plage B2:H dans « Appliquer à la plage » dans le panneau de mise en forme conditionnelle pour appliquer la même règle aux lignes suivantes.
Formule de l’étape 1:
=LARGE($B2:$H2,1)
Cela retournerait 319, qui est la valeur Large 1 (Max) dans la plage B2:H2.
Extraire les adresses des cellules de la ligne (étape 2)
Comme je l’ai expliqué, pour mettre en évidence la valeur maximale en ignorant les doublons, nous allons adopter la correspondance des adresses des cellules.
Nous pouvons utiliser la fonction ADDRESS comme suit (en tant que formule de tableau) pour cela.
Formule de l’étape 2:
=ArrayFormula(ADDRESS(ROW(),COLUMN($B$2:$H$2)))
Dans la formule ci-dessus, je n’ai spécifié aucune adresse de cellule dans la fonction ROW car je veux que la formule retourne l’adresse de la cellule de la ligne dans laquelle elle est insérée.
Si vous insérez la formule ci-dessus dans la cellule K2, elle renverrait les identifiants de cellules suivants dans K2:Q2.
Si elle est dans K4, cela donnerait :
La formule pour la plage B2:H2 serait donc suffisante pour une plage ouverte comme B2:H pour mettre en évidence les valeurs maximales en ignorant les doublons sur Google Sheets.
Filtrer l’ID de cellule de la cellule maximale ou des cellules (étape 3)
Pour filtrer l’adresse de la cellule contenant la valeur Large 1 ou Max 1 dans une ligne, nous pouvons utiliser la formule générique de filtrage suivante (selon mon exemple).
=FILTER(Step_2_Formula, $B2:$H2=Step_1_Formula, LEN($B2:$H2))
Donc la formule serait :
Formule de l’étape 3:
=FILTER(ADDRESS(ROW(),COLUMN($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,1), LEN($B2:$H2))
Note : La fonction ArrayFormula a été supprimée de la formule de l’étape_2 ici car elle n’est pas nécessaire avec FILTER.
La formule retournerait $B$2 dans la ligne 2, $B$3 (si vous faites glisser la formule de la ligne 2 à la ligne 3) dans la ligne 3… $B$7 | $C$7 | $D$7 | $E$7 | $F$7 dans la ligne 7…
L’image ci-dessous est simplement à titre d’explication. Vous n’avez pas besoin de saisir la formule telle quelle dans la cellule J2 et de la copier vers le bas.
Pour mettre en évidence la valeur maximale en ignorant les doublons ligne par ligne, nous devons utiliser notre formule de l’étape_5 dans la mise en forme conditionnelle. Vous l’obtiendrez après une étape supplémentaire.
Index pour extraire l’ID de cellule correct en fonction de la valeur maximale N à mettre en évidence (étape 4)
Sur l’image ci-dessus, vous pouvez voir qu’il y a plusieurs valeurs Max 1 (doublons) dans la ligne 7. Donc, plusieurs adresses de cellules dans le résultat du filtre.
Nous devons nous assurer de n’utiliser qu’une seule ID de cellule dans la mise en forme conditionnelle. L’index est le meilleur pour cela.
Relatif : Sélectionnez uniquement la colonne requise à partir d’un résultat de tableau dans Google Sheets.
Index pour Max 1 (Large 1) :
En utilisant l’INDEX, nous pouvons extraire le premier ID de cellule du résultat de l’étape_3.
Formule pour Large 1 :
=INDEX(FILTER(ADDRESS(ROW(),COLUMN($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,1), LEN($B2:$H2)), 0, 1)
Le 0 dans la dernière partie signifie toutes les lignes et 1 signifie 1 colonne.
Index pour Max 2 (Large 2) :
Si vous voulez mettre en évidence la valeur Large 2 (Max 2) en ignorant les doublons, utilisez la formule LARGE suivante dans la formule ci-dessus.
LARGE($B2:$H2,2)
De plus, vous devez modifier l’argument de colonne dans Index par la formule logique IF suivante.
=IF(LARGE($B2:$H2,2)=LARGE($B2:$H2,1),2,1)
Cela signifie que si Max 2 est égal à Max 1, renvoie la deuxième colonne des ID de cellule, sinon la première colonne des ID de cellule.
Formule pour Large 2 :
=INDEX(FILTER(ADDRESS(ROW(),COLUMN($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,2), LEN($B2:$H2)), 0, IF(LARGE($B2:$H2,2)=LARGE($B2:$H2,1),2,1))
Index pour Max 3 (Large 3) :
De même, pour mettre en évidence la valeur Large 3 en ignorant les doublons, utilisez la formule LARGE suivante et la formule logique IF suivante.
LARGE($B2:$H2,3)
IF logique (argument de colonne Index) :
=IF(LARGE($B2:$H2,3)=LARGE($B2:$H2,1),3, IF(LARGE($B2:$H2,3)=LARGE($B2:$H2,2),2,1))
Si Max 3 est égal à Max 1, cette formule renverra 3. Si Max 3 est égal à Max 2, elle renverra 2, sinon 1.
Formule pour Large 3 :
=INDEX(FILTER(ADDRESS(ROW(),COLUMN($B$2:$H$2)), $B2:$H2=LARGE($B2:$H2,3), LEN($B2:$H2)), 0, IF(LARGE($B2:$H2,3)=LARGE($B2:$H2,1),3, IF(LARGE($B2:$H2,3)=LARGE($B2:$H2,2),2,1)))
Règle de mise en forme pour mettre en évidence la valeur maximale | large en ignorant les doublons (étape 5)
Nous sommes prêts à appliquer la règle de mise en évidence pour mettre en évidence les valeurs Max 1, Max 2 et Max 3 en ignorant les doublons sur Google Sheets.
Nous avons 3 formules. Il s’agit de Large_1_Formula, Large_2_Formula et Large_3_Formula. Pour appliquer les règles, allez dans Format (menu) > Mise en forme conditionnelle.
Dans le panneau « Règles de mise en forme conditionnelle » à droite, entrez B2:H dans « Appliquer à la plage ». Les « Règles de format » doivent être « La formule personnalisée est ».
Maintenant, dans le champ fourni (qui sera vide), copiez-collez la formule Large_1_Formula selon la formule générique ci-dessous.
=CELLULE("adresse",B2)=Large_1_Formula
Définissez la couleur de remplissage de l’arrière-plan en vert et enregistrez. Ajoutez ensuite deux autres règles.
Couleur de remplissage : Bleu.
=CELLULE("adresse",B2)=Large_2_Formula
Couleur de remplissage : Rouge.
=CELLULE("adresse",B2)=Large_3_Formula
Note : Remplacez Large_1_Formula, Large_2_Formula et Large_3_Formula par les formules correspondantes.
J’espère que vous avez pu comprendre comment mettre en évidence la valeur maximale en ignorant les doublons ligne par ligne sur Google Sheets. Si ce n’est pas le cas, faites une copie de ma feuille d’exemple à partir du lien ci-dessous et examinez les formules.
Merci pour votre attention. Profitez-en !
