Excel : classer des Behaviour Points par statut sans sauter de numéros (rang dense)

Besoin de classer vos « Behaviour Points » par « Application Status » (Complete, Started, Not Started) sans trous dans la numérotation ? Voici des formules prêtes à l’emploi pour Excel 365/2021 et 2010–2019, plus des variantes, un exemple complet et des astuces de performance.

Objectif et contexte

Vous souhaitez attribuer un rang dense (1,2,2,3,…) aux valeurs de Behaviour Points (colonne B) au sein de chaque Application Status (colonne A). Deux contraintes clés :

• les doublons partagent le même rang ;
• aucun numéro n’est sauté dans la séquence (on ne veut pas 1,2,2,4 mais 1,2,2,3).

Le piège classique consiste à utiliser un classement « standard » (équivalent de RANK.EQ) qui crée des « trous » après les ex aequo. Nous allons donc calculer un rang dense : le nombre de valeurs distinctes ≤ (ou ≥) la valeur considérée, à l’intérieur du même statut.

Vue d’ensemble de la solution

Nous proposons deux approches :

1. Excel 365 / 2021 (fonctions dynamiques) — FILTER, UNIQUE, SORT, MATCH.
2. Excel 2010–2019 (sans fonctions dynamiques) — SUMPRODUCT/SOMMEPROD + COUNTIFS/NB.SI.ENS.

Les deux renvoient exactement le rang dense attendu par statut, en ordre croissant par défaut, avec variantes pour l’ordre décroissant.

Formules prêtes à coller

Option A — Excel 365 / 2021 (fonctions dynamiques)

Version internationale (fonctions en anglais, virgule comme séparateur) :

=IF(B2="","", COUNT( UNIQUE( FILTER($B$2:$B$1000, ($A$2:$A$1000=A2)*($B$2:$B$1000<=B2) ) ) ))

Version Excel en français (point-virgule comme séparateur) :

=SI(B2="";""; NB( UNIQUE( FILTRE($B$2:$B$1000; ($A$2:$A$1000=A2)*($B$2:$B$1000<=B2) ) ) ))

Principe : on filtre les points du même statut ≤ B2, on conserve les valeurs distinctes (UNIQUE) puis on les compte. Le résultat = rang dense croissant.

Alternative 365, tout aussi valide (et souvent un peu plus rapide) :

EN :

=IF(B2="","", MATCH(B2, SORT(UNIQUE(FILTER($B$2:$B$1000, $A$2:$A$1000=A2))), 1))

FR :

=SI(B2="";""; EQUIV(B2; TRIER(UNIQUE(FILTRE($B$2:$B$1000; $A$2:$A$1000=A2))); 1))

Ici on fabrique la liste triée des valeurs uniques du statut A2 ; MATCH/EQUIV renvoie la position (1‑basée) de B2 dans cette liste : c’est précisément le rang dense.

Option B — Excel 2010–2019 (sans FILTER/UNIQUE)

Version compacte (si vos données ne posent pas de cas limite) :

EN :

=IF(B2="","", ROUND( SUMPRODUCT( ((($A$2:$A$1000=A2)*($B$2:$B$1000<=B2)))/COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,$B$2:$B$1000) ), 0))

FR :

=SI(B2="";""; ARRONDI( SOMMEPROD( ((($A$2:$A$1000=A2)*($B$2:$B$1000<=B2)))/NB.SI.ENS($A$2:$A$1000;A2;$B$2:$B$1000;$B$2:$B$1000) ); 0))

Principe : on additionne, pour chaque ligne candidate (même statut et ≤ B2), 1 / (nombre d’occurrences de cette valeur dans ce statut). La somme donne le nombre de valeurs distinctes ≤ B2, donc le rang dense. ROUND/ARRONDI évite des artefacts de virgule flottante (ex. 2,999999).

Version robuste (recommandée en 2010–2019) : évite tout risque de #DIV/0! en limitant explicitement la division aux lignes retenues par le filtre logique.

EN :

=IF(B2="","", ROUND( SUMPRODUCT( IF(($A$2:$A$1000=A2)*($B$2:$B$1000<=B2), 1/COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,$B$2:$B$1000), 0) ), 0))

FR :

=SI(B2="";""; ARRONDI( SOMMEPROD( SI( ($A$2:$A$1000=A2)*($B$2:$B$1000<=B2); 1/NB.SI.ENS($A$2:$A$1000;A2;$B$2:$B$1000;$B$2:$B$1000); 0 ) ); 0))

Dans cette version, le SI (IF) place 0 pour toutes les lignes hors périmètre, de sorte que la division n’est effectuée que là où il existe bien des occurrences correspondantes.

---

Exemple concret à reproduire

Supposons la plage A1:B17 remplie ainsi (les en-têtes en ligne 1) :

#	Application Status (A)	Behaviour Points (B)	Rang dense attendu (↑)	Rang dense attendu (↓)
2	Complete	-60	1	4
3	Complete	-30	2	3
4	Complete	-30	2	3
5	Complete	-15	3	2
6	Complete	0	4	1
7	Started	-45	1	4
8	Started	-45	1	4
9	Started	-10	2	3
10	Started	20	3	2
11	Started	20	3	2
12	Started	50	4	1
13	Not Started	-30	1	4
14	Not Started	-25	2	3
15	Not Started	-25	2	3
16	Not Started	-5	3	2
17	Not Started	0	4	1

Recopiez l’une des formules ci-dessus en C2 puis tirez vers le bas. Vous obtenez le rang dense « ↑ » (ordre croissant). Pour l’ordre décroissant « ↓ », voir la section « Variantes utiles ».

Pas à pas (recommandé pour Excel 365/2021)

1. Placez la formule (version FR ou EN selon votre Excel) en C2 :
   • Soit la formule COUNT(UNIQUE(FILTER(...))) ;
   • Soit la formule MATCH(EQUIV) + SORT(TRIER) + UNIQUE.
2. Sécurisez les références : notez l’utilisation des $ (ex. $B$2:$B$1000) pour verrouiller les plages lors de la recopie.
3. Gérez les vides : la condition IF/​SI(B2="","",…) laisse la cellule de rang vide si aucun point.
4. Recopiez la formule jusqu’à la dernière ligne de vos données.

Explication détaillée (formule 365 : COUNT(UNIQUE(FILTER(...))))

• FILTER($B$2:$B$1000, ($A$2:$A$1000=A2)*($B$2:$B$1000<=B2)) retient uniquement les points du même statut et inférieurs ou égaux à B2.
• UNIQUE(...) supprime les doublons ; il ne reste que les valeurs distinctes.
• COUNT/NB(...) compte ces valeurs distinctes ; c’est votre rang dense.

Explication détaillée (alternative 365 : MATCH(EQUIV))

• FILTER(...) retient tous les points du même statut.
• UNIQUE(...) enlève les doublons.
• SORT/TRIER(...) classe la liste (par défaut croissant).
• MATCH(EQUIV)(B2; liste triée; 1) renvoie l’index de la plus grande valeur ≤ B2 dans cette liste, soit le rang dense.

Variante : ordre décroissant

Pour mettre le plus grand en rang 1 :

• Option A (365) : remplacez <=B2 par >=B2 dans la version FILTER. Dans la version MATCH, triez en décroissant : SORT(...,,-1).
• Option B (2010–2019) : remplacez <=B2 par >=B2.

Conseils pro : Table Excel & références structurées

Convertissez vos données en Table (Ctrl+T). Nommons la table tData avec les colonnes [Application Status] et [Behaviour Points]. Les références structurées rendent les formules plus lisibles et s’auto-copient.

Exemple 365 (EN) :

=IF([@[Behaviour Points]]="","", MATCH([@[Behaviour Points]], SORT(UNIQUE(FILTER(tData[Behaviour Points], tData[Application Status]=[@[Application Status]]))), 1))

Exemple 365 (FR) :

=SI([@[Behaviour Points]]="";""; EQUIV([@[Behaviour Points]]; TRIER(UNIQUE(FILTRE(tData[Behaviour Points]; tData[Application Status]=[@[Application Status]]))); 1))

Atouts : noms parlants, recopie automatique vers les nouvelles lignes, moins d’erreurs de plage.

Performance & bonnes pratiques

• Limitez les plages : préférez $2:$1000 à une colonne entière ($2:$1048576). Vous évitez des recalculs inutiles.
• Préférez l’alternative MATCH à COUNT(UNIQUE(FILTER(...))) si vous recalculer souvent : une seule liste unique/triée par statut, puis une recherche par position.
• Évitez les conversions implicites : assurez-vous que tous les « Behaviour Points » sont bien numériques (pas de texte « -30 »). Utilisez Valeur ou Textes en colonnes au besoin.
• Placez une mise en forme conditionnelle pour surligner les ex aequo (doublons) si utile : cela facilite l’audit visuel.

Dépannage

• #SPILL! (#DÉVERSEMENT!) : apparaît rarement ici (la formule ne renvoie qu’un scalaire). Si vous voyez l’erreur, c’est qu’un calcul intermédiaire a déversé (ex. vous avez copié seulement une sous-partie). Vérifiez les cellules voisines.
• #N/A (#N/A) dans la version MATCH/EQUIV : peut arriver si la liste unique filtrée est vide (ex. cellules vides) et que vous avez supprimé le test IF/​SI(B2="","",…). Conservez ce test anti‑vide.
• #DIV/0! en 2010–2019 : utilisez la version robuste de l’Option B ; elle encapsule la division dans un IF/​SI et remplace les cas hors périmètre par 0.
• Valeurs négatives : aucun problème. Les comparaisons <= et >= fonctionnent indistinctement pour valeurs négatives et positives.
• Locale FR vs EN : en français, séparez les arguments par des points‑virgules (;) et utilisez les noms de fonctions localisés (SI, NB, FILTRE, etc.).

FAQ rapide

Quelle différence entre « rang dense » et RANK.EQ / RANG.EQ ?

RANK.EQ classe en attribuant le même rang aux ex aequo, mais saute les numéros suivants (ex. 1,2,2,4…). Le rang dense ne saute pas de numéro (1,2,2,3…).

Peut‑on classer sur plusieurs critères (ex. Statut + Site) ?

Oui. Ajoutez simplement le critère supplémentaire dans le filtre :

EN (365) :

=IF(B2="","", MATCH(B2, SORT(UNIQUE(FILTER($B$2:$B$1000, ($A$2:$A$1000=A2)*($D$2:$D$1000=D2)))), 1))

FR (365) :

=SI(B2="";""; EQUIV(B2; TRIER(UNIQUE(FILTRE($B$2:$B$1000; ($A$2:$A$1000=A2)*($D$2:$D$1000=D2)))); 1))

où D contient, par exemple, le Site.

Comment afficher le top N (ex. Top 3 par statut) ?

Créez une colonne « Rang dense » puis filtrez <=3. En 365, vous pouvez aussi extraire directement :

EN :

=LET(stat;A2; points; FILTER($B$2:$B$1000,$A$2:$A$1000=stat); TAKE(SORT(UNIQUE(points)),3))

FR :

=LET(stat;A2; points; FILTRE($B$2:$B$1000;$A$2:$A$1000=stat); PRENDRE(TRIER(UNIQUE(points));3))

(LET/PRENDRE disponibles dans les versions récentes de 365.)

Peut‑on « casser » les ex aequo tout en conservant un rang dense ?

Si vous souhaitez ordonner les ex aequo (par date ou ID), mais afficher un rang dense, gardez la colonne « Rang dense » telle quelle et créez une 2^e colonne « Ordre d’affichage » : SORTBY/TRIERPAR sur [Behaviour Points] puis sur la clé secondaire (ID/Date).

Audit et validation du résultat

Pour vérifier que la série est « dense » :

1. Filtrez sur un seul Application Status.
2. Triez la colonne « Rang dense » : vous devez voir une suite sans trou (1,2,2,3,… jusqu’au nombre de valeurs distinctes).
3. Comptez les valeurs distinctes de Behaviour Points dans ce statut (ex. en 365 : =ROWS(UNIQUE(FILTER(B:B,A:A="Complete"))) / =NB.LIGNES(UNIQUE(FILTRE(B:B;A:A="Complete")))). Le total doit égaler le max du rang dense pour ce statut.

Pourquoi ces formules fonctionnent-elles ?

Le rang dense croissant d’une valeur B2 dans un groupe donné n’est rien d’autre que :

le nombre de valeurs distinctes ≤ B2 dans ce groupe.

Les solutions 365 appliquent exactement cette définition avec FILTER → UNIQUE → COUNT, ou, de façon équivalente, en repérant la position de B2 dans la liste triée des valeurs distinctes (MATCH sur SORT(UNIQUE(...))). En 2010–2019, l’astuce consiste à pondérer chaque ligne par 1 / (effectif de sa valeur dans le groupe) et à sommer ces poids uniquement pour les lignes ≤ B2 : vous obtenez le nombre de valeurs distinctes.

Adaptations courantes

• Ordre décroissant : voir section dédiée. Règle générale : remplacez <= par >= ou triez en décroissant.
• Ignorer des valeurs (ex. 0) : ajoutez un critère supplémentaire au filtre (ex. *($B$2:$B$1000<>0)), ou en 365, utilisez FILTER avec plusieurs conditions.
• Rangs indépendants par sous‑groupe (ex. Statut + Mois) : filtrez simultanément sur les deux colonnes. La logique reste identique.
• Affichage : combinez avec TEXT/TEXTE pour préfixer le rang (ex. « #1 », « N°2 »), sans toucher au calcul.

Étapes de mise en œuvre recommandées

1. Nettoyez la colonne B (numérique, pas de texte ni d’espace insécable).
2. Convertissez votre plage en Table (Ctrl+T).
3. Choisissez la formule selon votre version d’Excel (365 / 2021 ou 2010–2019).
4. Créez deux colonnes si besoin : Rang (↑) et Rang (↓) — vous pourrez filtrer selon l’une ou l’autre.
5. Vérifiez sur 2–3 statuts au hasard : le max du rang = nombre de valeurs distinctes.

Récapitulatif des formules

365 / 2021 — rang dense croissant

• EN : =IF(B2="","", COUNT( UNIQUE( FILTER($B$2:$B$1000, ($A$2:$A$1000=A2)*($B$2:$B$1000<=B2) ) ) ))
• FR : =SI(B2="";""; NB( UNIQUE( FILTRE($B$2:$B$1000; ($A$2:$A$1000=A2)*($B$2:$B$1000<=B2) ) ) ))

365 / 2021 — alternative MATCH (souvent plus rapide)

• EN : =IF(B2="","", MATCH(B2, SORT(UNIQUE(FILTER($B$2:$B$1000, $A$2:$A$1000=A2))), 1))
• FR : =SI(B2="";""; EQUIV(B2; TRIER(UNIQUE(FILTRE($B$2:$B$1000; $A$2:$A$1000=A2))); 1))

2010–2019 — rang dense croissant (robuste)

• EN : =IF(B2="","", ROUND( SUMPRODUCT( IF(($A$2:$A$1000=A2)*($B$2:$B$1000<=B2), 1/COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,$B$2:$B$1000), 0) ), 0))
• FR : =SI(B2="";""; ARRONDI( SOMMEPROD( SI( ($A$2:$A$1000=A2)*($B$2:$B$1000<=B2); 1/NB.SI.ENS($A$2:$A$1000;A2;$B$2:$B$1000;$B$2:$B$1000); 0 ) ); 0))

Ordre décroissant

• 365 : remplacez <= par >=, ou triez SORT(...,,-1) dans la version MATCH/EQUIV.
• 2010–2019 : remplacez <=B2 par >=B2.

Conclusion

Que vous disposiez d’Excel 365/2021 ou d’une version 2010–2019, vous pouvez attribuer un rang dense par statut à vos « Behaviour Points » en quelques secondes. Les doublons reçoivent exactement le même rang et la série est continue (1,2,2,3,…) : fini les trous et les surprises au tri. Adaptez ensuite l’ordre (croissant/décroissant), ajoutez des critères supplémentaires (statut+site), ou combinez le rang dense avec un tri secondaire pour piloter vos tableaux de bord. Les formules proposées sont performantes, auditables et compatibles avec les contraintes réelles d’un classeur en production (plages limitées, valeurs négatives, cellules vides). Bon classement !