Nous pouvons facilement mettre en évidence les N valeurs uniques les plus élevées dans les colonnes ou les lignes de Google Sheets. Une combinaison des fonctions UNIQUE et GRANDE le fera.
Mais j’utiliserai également les fonctions ArrayFormula(), ET(), N() et LONG() pour prendre en compte les cellules vides, ainsi que COUNTIF() éventuellement pour appliquer une couleur de remplissage blanche aux doublons.
Laissez-moi vous donner un exemple. Ensuite, nous passerons aux formules.
J’ai les nombres {1;2;3;4;5;6;7;8;9;10;1;2;3;4;5;6;7;8;9;10} dans une colonne (plage B2:B21) sur Google Sheets.
Si n=5, les N nombres uniques les plus élevés à mettre en évidence dans B2:B21 seront 10, 9, 8, 7 et 6, et non pas 10, 10, 9, 9 et 8.
En plus de cela, vous pouvez choisir ou non d’appliquer la couleur de remplissage aux occurrences multiples des N nombres les plus élevés (dans ce cas, nous pouvons utiliser facultativement une règle COUNTIF).
Voici des exemples (captures d’écran) d’approches à couleur unique.
Valeurs uniques les plus élevées (Toutes les occurrences) :
Valeurs uniques les plus élevées (Première occurrence) :
Mettez en évidence les N valeurs uniques les plus élevées dans les colonnes de Google Sheets
Voici les formules et les étapes à suivre.
Sélectionnez le menu Format > Mise en forme conditionnelle. Sous « Couleur unique » (onglet), suivez les paramètres ci-dessous.
- « Appliquer à la plage » – B2:B21 (ou B2:B).
- « Règles de formatage > Mise en forme des cellules si… > La formule personnalisée est » – Insérez la formule donnée après le point 4 ci-dessous.
- « Règles de formatage > Style de mise en forme » – Choisissez une couleur de remplissage (ici, vert).
- Sélectionnez « Terminé ».
=ET(LONG(B$2:B),B2>=GRANDE(UNIQUE(ARRAYFORMULA(N(B$2:B)),FALSE),$F$1))
Entrez 5 dans la cellule F1 pour contrôler dynamiquement le N. Si vous souhaitez mettre en évidence les 10 nombres les plus élevés et uniques, remplacez 5 par 10.
Cela mettra en évidence toutes les occurrences des N valeurs uniques les plus élevées.
Si vous souhaitez mettre en évidence uniquement les premières occurrences des N valeurs uniques les plus élevées, en plus des éléments ci-dessus, vous devrez peut-être ajouter une autre règle.
=COUNTIF(B$2:B2,B2)>1
« Appliquer à la plage » est le même qu’auparavant, c’est-à-dire B2:B21. La couleur de remplissage doit être blanche ici.
Une fois ajoutée, vous devez faire glisser et placer cette règle au-dessus de la première règle, et cela est très important.
Et si nous voulons mettre en évidence les N nombres uniques les plus élevés dans chaque colonne de B2:E21 ?
Modifiez simplement « Appliquer à la plage » en B2:E21 au lieu de B2:B21.
Explication de la formule
Permettez-moi de diviser la première formule (couleur de remplissage verte) en quatre parties. Cela semble être la manière la plus simple de l’apprendre.
Partie_1:
arrayformula(n(B$2:B)) – N convertit les cellules vides (ou les cellules de texte, le cas échéant) en 0 (zéro) et renvoie les autres nombres tels quels.
Partie_2:
unique(partie_1,false) – UNIQUE ne renvoie que les nombres uniques dans la plage de colonnes sélectionnée.
Partie_3:
grande(partie_2,$F$1) – GRANDE trouve la nième valeur la plus élevée en fonction de F1.
Partie_4 (formule finale):
C’est un test logique ET comme suit.
ET(expression_logique1, expression_logique2)
C’est équivalent à ET(LONG(B$2:B),B2>=partie_4).
Ici, LEN dans l’expression logique 1 renvoie VRAI (n’importe quel nombre) si la cellule a une valeur ou FAUX (0) si elle est vide.
La deuxième formule (couleur de remplissage blanche) est explicite, car il s’agit d’un COUNTIF pour compter les occurrences.
Mettez en évidence les N valeurs uniques les plus élevées dans les lignes de Google Sheets
Dans l’exemple ci-dessus, nous avons appris comment mettre en évidence les N valeurs uniques les plus élevées dans une colonne ou chaque colonne de Google Sheets.
Mais qu’en est-il d’une ligne ou de chaque ligne ?
Ici, dans l’exemple suivant, l' »Appliquer à la plage » est B2:U2 (ou B2:U).
Couleur verte :
=ET(LONG($B2:2),B2>=GRANDE(UNIQUE(ARRAYFORMULA(N($B2:2)),TRUE),$B$1))
Couleur blanche (facultatif) :
=COUNTIF($B2:B2,B2)>1
Ici, B1 contrôle la valeur de N.
Conseils supplémentaires (Couleurs multiples)
Parfois, vous pouvez vouloir différencier les N valeurs uniques les plus élevées avec des couleurs différentes. Que ferez-vous alors ?
Bien sûr, nous pouvons utiliser le même COUNTIF pour remplir la couleur blanche et ignorer la mise en évidence dans les cellules de valeurs en double.
Mais pour les premiers 1, 2, 3…, nous devons utiliser la règle 1, la règle 2, la règle 3…
Nous devons choisir différentes couleurs de remplissage pour chaque règle.
Voici comment mettre en évidence les N nombres uniques les plus élevés avec des couleurs multiples dans Google Sheets.
Si l’ensemble de nombres se trouve dans les colonnes, utilisez notre formule précédente. Il y a cependant deux changements.
Règle 1 :
=ET(LONG(B$2:B),B2=GRANDE(UNIQUE(ARRAYFORMULA(N(B$2:B)),FALSE),1))
Auparavant, c’était B2>= , ici c’est B2=. De plus, nous avons spécifié directement la valeur de N ici, qui est 1.
Règle 2 :
=ET(LONG(B$2:B),B2=GRANDE(UNIQUE(ARRAYFORMULA(N(B$2:B)),FALSE),2))
De même, ajoutez davantage de règles. Changez uniquement la valeur de N et utilisez des couleurs de remplissage différentes.
Le COUNTIF doit être au-dessus de ces règles.
Exemple avec cinq règles de couleurs multiples appliquées pour les cinq valeurs les plus élevées et uniques dans Google Sheets.
Pour les données (ensemble de nombres) dans les lignes, vous pouvez utiliser nos règles précédemment mentionnées avec les deux modifications ci-dessus.
C’est tout. Merci de votre attention. Amusez-vous bien !
Visitez Crawlan.com pour plus d’informations.
