Vous vous demandez comment mettre automatiquement en évidence la valeur maximale de chaque groupe dans Google Sheets ? Ne vous inquiétez pas ! Cet article a la réponse et la solution. J’ai la formule à utiliser dans la mise en forme conditionnelle de Google Sheets pour mettre en surbrillance la valeur maximale de chaque groupe (par groupe).
Avec cette règle de formule, vous pouvez changer la couleur de la cellule, la couleur de la police ou barrer la valeur maximale par groupe.
Où pouvons-nous utiliser ce type de mise en forme de cellule ?
Voici quelques scénarios.
Supposons que vous receviez plusieurs entrées de formulaire chaque jour. Avec cette règle, vous pouvez mettre en évidence la dernière soumission de formulaire de chaque jour en utilisant la colonne de date et d’heure.
Un autre scénario concerne le rapport de ventes. Dans un rapport de ventes combinées, nous pouvons trouver le vendeur ayant le volume de ventes maximal à son nom pour chaque date.
Ici, nous regrouperons les données en fonction de la colonne de date et mettrons en évidence la colonne du volume de ventes (colonne de valeur maximale).
Dans un ensemble de données liées à des événements sportifs, par exemple une course de voitures, nous pouvons mettre en évidence le leader de chaque tour de la course.
Il est donc intéressant d’apprendre comment mettre en évidence la valeur maximale de chaque groupe.
Pour votre information, j’utilise mes tests avec un ensemble de données très basique. Mais vous pouvez l’adapter à votre utilisation réelle. Ces astuces sont également incluses.
Mettre automatiquement en évidence la valeur maximale de chaque groupe dans Google Sheets
J’ai utilisé mes règles de mise en forme conditionnelle dans la plage B2:B. Observez les règles (il y en a deux) qui mettent en évidence les valeurs maximales en fonction des éléments (la colonne de groupe est la colonne A).
J’ai utilisé deux règles de formule personnalisée pour cela. Le premier est pour conserver les cellules vides dans la colonne B sans mise en forme par la deuxième règle, qui est la règle clé.
Règle n°1 :
=isblank($B2)=true
Règle n°2 :
La règle clé qui met automatiquement en évidence le maximum de chaque groupe dans votre fichier Google Sheets.
=isna(vlookup(A2&B2,ArrayFormula(index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,1)&index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,2)),1,0))=false
Je sais que vous voulez vraiment savoir comment utiliser cette formule dans votre fichier Google Sheets pour mettre en forme conditionnelle les valeurs maximales par groupe.
De plus, certains d’entre vous voudront peut-être également connaître l’explication de la formule ainsi que la logique. Alors, allons-y !
Comment appliquer des formules en tant que règles de mise en forme conditionnelle dans Google Sheets
Parlons de l’application des deux règles ci-dessus. Cela vous aidera également à apprendre comment appliquer des formules en tant que règles personnalisées dans la mise en forme conditionnelle.
La première chose importante est l’ordre des règles personnalisées. Si vous utilisez des règles personnalisées (plus d’une formule), respectez leur ordre « prévu » !
Étapes :
Sélectionnez la plage B2:B. C’est-à-dire de B2 jusqu’à la dernière cellule de cette colonne. Sur ma feuille, c’est B2:B11.
Ensuite, ouvrez le volet latéral de la mise en forme conditionnelle depuis le menu Format > Mise en forme conditionnelle.
Comme vous avez sélectionné B2:B11 comme plage, les paramètres par défaut seront quelque chose comme ci-dessous.
Cela soulève une question. Ne pouvons-nous pas utiliser une plage infinie dans la mise en forme conditionnelle de Google Sheets ?
Oui, bien sûr. Pour sélectionner une plage infinie, utilisez uniquement les lignes existantes, comme je l’ai fait (B2:B11).
N’utilisez pas B2:B (plage ouverte) car ce n’est pas nécessaire/accepté. Google Sheets étendra automatiquement la plage B2:B11 en fonction des nouvelles lignes ajoutées.
Cliquez sur le menu déroulant « N’est pas vide » et sélectionnez « La formule personnalisée est ». Vous verrez un champ vide. Copiez-collez la règle n°1 ci-dessus dans le champ vide.
Sous le champ, vous verrez « Style de mise en forme ». Cliquez sur l’icône similaire à un seau de peinture (couleur de remplissage) et sélectionnez « Aucun ».
Une fois terminé, cliquez sur « Ajouter une autre règle » et saisissez la règle n°2 dans le champ de formule personnalisée.
Cette fois, choisissez votre couleur préférée (autre que blanc/aucun) pour mettre en évidence la valeur maximale et cliquez sur « Terminé ».
Modifier les règles de formule en cas de colonne de groupe différente et de colonne de valeur différente – Comment modifier les règles de formule ?
Supposons que la colonne de groupe soit B au lieu de la colonne A et que la colonne de valeur maximale soit la colonne E au lieu de la colonne B. Voici les modifications à apporter à la formule.
Règle modifiée n°1 :
=isblank($E2)=true
Règle modifiée n°2 :
=isna(vlookup(B2&E2,ArrayFormula(index(sortn(sort({$B$2:$B,$E$2:$E},1,0,2,0),9^9,2,1,0),0,1)&index(sortn(sort({$B$2:$B,$E$2:$E},1,0,2,0),9^9,2,1,0),0,2)),1,0))=false
Dans les règles modifiées 1 et 2, « Appliquer à la plage » dans les paramètres de mise en forme conditionnelle changera en E2:E11 au lieu de B2:B11.
Comment la formule de la règle n°2 met-elle en évidence la valeur maximale de chaque groupe ?
Ci-dessous, je vais expliquer pas à pas la formule clé, qui est la règle n°2.
La formule SORT trie les éléments et les valeurs par ordre décroissant. Ainsi, la valeur maximale de chaque groupe sera à la première ligne de chaque groupe.
=sort({$A$2:$A,$B$2:$B},1,0,2,0)
La fonction SORTN supprime les doublons du groupe et conserve la première ligne de chaque groupe.
=sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0)
Pour comprendre cette fonctionnalité SORTN (supprimer les doublons de chaque groupe), vous pouvez lire cet article : Les quatre casseurs d’égalité de la fonction SORTN dans Google Sheets.
Maintenant, nous avons les valeurs maximales de chaque groupe et les noms des éléments correspondants.
À l’aide de deux formules INDEX, nous pouvons séparer les colonnes (noms des éléments et colonnes des valeurs maximales).
Extraire le nom de l’élément (première colonne) :
=index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,1)
Extraire les valeurs maximales (deuxième colonne) :
=index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,2)
Ensuite, à l’aide de l’opérateur &, nous avons combiné les deux colonnes (deux formules INDEX) comme suit.
De plus, j’ai enveloppé ces formules INDEX avec ArrayFormula car l’opérateur & est utilisé dans un tableau.
=ArrayFormula(index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,1)&index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,2))
Les valeurs ci-dessus sont la ‘plage’ dans une recherche verticale (Vlookup), qui est notre formule finale qui met en évidence la valeur maximale de chaque groupe dans notre fichier de feuille de calcul Google.
Syntaxe :
VLOOKUP(clé_de_recherche, plage, index, [triée])
Maintenant, examinons nos données dans A2:B11 et la ‘clé de recherche’ Vlookup ci-dessous.
=A2&B2
Cela renvoie « Pomme100″ comme clé de recherche. L' »index » (numéro de colonne de sortie dans Vlookup) est 1 car il y a seulement 1 colonne dans la ‘plage’.
Vlookup ne trouvera pas la clé de recherche ci-dessus dans la ‘plage’ et renverra donc FAUX.
Ensuite, Vlookup utilisera la clé de recherche « Pomme255 » (A3&B3) dans la ‘plage’ et renverra VRAI.
Comme la valeur est VRAIE, la cellule correspondante dans la plage B2:B11 sera mise en évidence. Cela continue dans les lignes suivantes.
Voilà tout sur la mise en valeur de la valeur maximale de chaque groupe dans Google Sheets.
Plus d’articles sur la mise en forme conditionnelle
- Multiple OU dans la mise en forme conditionnelle avec Regex dans Google Sheets.
- Règles de mise en forme conditionnelle liées à la date dans Google Sheets.
- Comment mettre en évidence les doublons entre les onglets de feuille dans Google Sheets.
- Mettre en surbrillance une ligne entière dans la mise en forme conditionnelle de Google Sheets.
- Comment mettre en évidence toutes les lignes d’erreur dans Google Sheets.
- Comment mettre en évidence les trois plus grandes valeurs de chaque ligne dans Google Sheets.
- Trouver toutes les cellules comportant une mise en forme conditionnelle dans Google Sheets.
- Comment mettre en évidence les cellules contenant une fonction spécifique dans Google Sheets.
- Mettre en évidence les groupes lorsque le total du groupe dépasse l’objectif dans Google Sheets.
- Comment mettre en évidence la valeur de résultat de Vlookup dans Google Sheets.
- Mettre en évidence les doublons correspondant partiellement dans Google Sheets.
