XL 2019 Calculer un rang avec condition et sans ex aequo

thomas.bladier · 26 Octobre 2022

Bonjour à tous,

Je ne m'en sors pas du tout avec une formule, j'ai eu beau tourner le problème dans tous les sens je n'ai pas trouvé de solution.

J'ai une liste de 9 magasins, classés dans trois groupes différents : A, B et C. Je cherche à établir un classement dans chacun des groupes, le magasin "1" étant celui avec le CA le plus élevé.

Dans mon groupe A j'aurai donc un classement de 1 à 3, et pareil dans mes autres groupes.

La difficulté est ici : certains magasins ont un Chiffre d'Affaires identiques, avec les formules que j'ai essayé certains magasins avaient le même rang et il manquait alors un rang (Deux magasins rang 1 et un magasin rang 3, sans qu'aucun ne soit rang 2).

Mon but est que ma formule, même si deux magasins affichent le même CA, m'indique toujours tous les rangs, sans avoir de "ex aequo".

Voici un fichier avec le résultat souhaité, merci d'avance !

mapomme · 27 Octobre 2022

Re,

Une autre manière de faire est de passer par une fonction personnalisée:

VB:

=RangUniqueSi(Valeur As Range, PlageValeurs As Range, ValeurCondition As Range, PlageConditions As Range)

Voir formule en D2 à copier vers la bas:

VB:

=RangUniqueSi(C2;C$2:C$10;B2;B$2:B$10)

Contrairement à la formule du message précédent, il n'y aucune restriction sur les valeurs.

La formule renvoie #REF! si les plages sont incohérentes.

Le code de la fonction est dans module1 :

VB:

Function RangUniqueSi(Valeur As Range, PlageValeurs As Range, ValeurCondition As Range, PlageConditions As Range)
Dim tval, tcond, i&, n&, ival&, ech As Boolean, aux

   ' Vérification des arguments de la fonction
   If PlageValeurs.Count <> PlageConditions.Count...

sylvanu · 26 Octobre 2022

Bonjour Thomas,
Pas tout compris.
Pourquoi Paris et Le Havre sont ex aequo ? Et comment évite t-on les ex aequo lorsqu'il y en a ?

Une solution qui en vaut une autre :
1- Vous sélectionnez le tableau puis faites Données/Trier
2-
Tri sur Groupe en alpha puis sur CA en décroissant.
3- Vous avez le résultat directement.

thomas.bladier · 26 Octobre 2022

Bonjour Sylvanu,

Dans cette pièce jointe ce sera plus explicite, j'ai mis en colonne D la formule que j'avais utilisé à la base dans mon fichier. On peut voir que les magasins à Lille et Rouen ont le même CA (40k€) et sont donc tout deux rang 1. Le magasin Bordeaux (30k€) est indiqué rang 3. J'aimerai grâce à une formule que Lille soit "1", Rouen "2" (ou l'inverse peut importe) et Bordeaux "3".

Voici la formule utilisé :

Code:

=NB(SI($B$2:$B$10=B2;$C$2:$C$10;""))+1-SOMME(--(C2>=SI($B$2:$B$10=B2;$C$2:$C$10;"")))

Votre solution serait suffisante sur le fichier que j'ai posté, mais le fichier sur lequel je veux appliquer cette formule est bien plus complexe que celui que j'ai inséré et j'ai besoin du rang pour alimenter différents dashboards.

sylvanu · 26 Octobre 2022

Re,
En une formule, je sèche.
Alors un essai de "bourrin", en créant une colonne D masquée avec :

VB:

=C2-LIGNE()/10000

Ce qui élimine les doublons.

Phil69970 · 27 Octobre 2022

Bonjour à tous

@thomas.bladier

Perso je trouve un manque de logique dans ta demande car si il y a 2 seconds c'est pas gênant pour toi

mais si il y a 2 premiers alors c'est gênant

En fait c'est comme au foot en cas d'égalité il faut donner d'autres critères de départage comme les buts marqués, les buts encaissés et/ou le nombre de victoires voir d'autre critère (tirage au sort)....

Et bien pour ton cas il faut préciser un ou des critères pour départager les exæquos mais on les départage dans tous les cas pas seulement pour la 1ere place.

Dans ton cas cela peut être par exemple :
==> 1) Le CA au centime près (Peu de chance qu'il soit identique)
==> 2) le CA de l'année d'avant
==> 3) le nombre de personnel (le plus grand ou le plus petit à toi de choisir)
==> 4) le hasard (tirage au sort)
==> 5) l'ordre alphabétique pas très juste car c'est donné un avantage à la ville A par rapport à la ville B
==> 6) l'age du directeur ou n'importe quel critère discriminant permettant d'obtenir un classement.

Sans connaitre les critères que tu as choisi cela me parait compliqué de donner une réponse.

@Phil69970

thomas.bladier · 27 Octobre 2022

En effet @Phil69970 une coquille s'est glissée dans mes résultats souhaités, 2 seconds sont bien gênants pour moi, j'ai modifié cela dans mes fichiers

Je vois ce que tu veux dire, pour mon cas le plus pertinent est de départager avec le nombre de personnel.
Le magasin ayant le plus de personnel et à CA égal avec un autre sera donc classé rang 1.

Voici mon fichier avec un ajout de nombre d'employés.

JHA · 27 Octobre 2022

Bonjour à tous,

A essayer

JHA

mapomme · 27 Octobre 2022

Bonjour thomas.bladier, à tous ,

Une formule sans colonne auxiliaire même principe que @sylvanu . La formule matricielle en D2 à recopier vers le bas est une formule matricielle.

VB:

=EQUIV(C2-LIGNE()/(10^12);SIERREUR(GRANDE.VALEUR((SI(B$2:B$10=B2;C$2:C$10-LIGNE(C$2:C$10)/(10^12);""));LIGNE(C$2:C$10)-LIGNE($C$2)+1);"");0)

Une petite restriction cependant :

Les valeurs de CA doivent être des nombres décimaux d'au maximum 5 chiffres après la virgule. Ce qui est le cas ici. On parle bien de la valeur réelle du CA et non pas de son affichage. Si le CA est le résultat d'une formule de calcul (directement ou indirectement), on englobera la formule du CA dans un ARRONDI() de type: =ARRONDI( Formule(du CA) ; 5 ) . Pour un CA de quelques dizaines de milliers d'€, un arrondi à 2 voire à 0 est largement significatif.
cette restriction se justifie par le fait que la petite quantité qu'on rajoute au CA pour le classement ne doit pas modifier l'ordre relatif des CA et ceci même si on va jusqu'à la dernière ligne de la feuille.

nota : je ne comprends pas pourquoi différencier des magasins ayant le même CA. Il y a un comme un parfum d'arbitraire et d'injustice .

mapomme · 27 Octobre 2022

Re,

Une autre manière de faire est de passer par une fonction personnalisée:

VB:

=RangUniqueSi(Valeur As Range, PlageValeurs As Range, ValeurCondition As Range, PlageConditions As Range)

Voir formule en D2 à copier vers la bas:

VB:

=RangUniqueSi(C2;C$2:C$10;B2;B$2:B$10)

Contrairement à la formule du message précédent, il n'y aucune restriction sur les valeurs.

La formule renvoie #REF! si les plages sont incohérentes.

Le code de la fonction est dans module1 :

VB:

Function RangUniqueSi(Valeur As Range, PlageValeurs As Range, ValeurCondition As Range, PlageConditions As Range)
Dim tval, tcond, i&, n&, ival&, ech As Boolean, aux

   ' Vérification des arguments de la fonction
   If PlageValeurs.Count <> PlageConditions.Count Then RangUniqueSi = CVErr(xlErrRef): Exit Function
   If PlageValeurs.Columns.Count <> 1 Or PlageConditions.Columns.Count <> 1 Then RangUniqueSi = CVErr(xlErrRef): Exit Function
   If PlageValeurs.Row <> PlageConditions.Row Then RangUniqueSi = CVErr(xlErrRef): Exit Function
   If Valeur.Count <> 1 Then RangUniqueSi = CVErr(xlErrRef): Exit Function
   If Intersect(Valeur, PlageValeurs) Is Nothing Then RangUniqueSi = CVErr(xlErrRef): Exit Function
 
   ' Lecture des tableaux des valeurs et des conditions
   tval = PlageValeurs: tcond = PlageConditions
 
   ' Tableau t des valeurs (colonne2) avec leur rang d'apparition (colonne 1) pour la condition vérifiée
   ReDim t(1 To UBound(tval), 1 To 2): n = 0
   For i = 1 To UBound(tval)
      If tcond(i, 1) = ValeurCondition Then: n = n + 1: t(n, 1) = n: t(n, 2) = tval(i, 1)
   Next i
 
   ' Tri du tableau suivant la clef CA (colonne 2)
   Do
      ech = False
      For i = 1 To n - 1
         If t(i, 2) < t(i + 1, 2) Then
            ech = True
            aux = t(i, 1): t(i, 1) = t(i + 1, 1): t(i + 1, 1) = aux
            aux = t(i, 2): t(i, 2) = t(i + 1, 2): t(i + 1, 2) = aux
         End If
      Next i
   Loop Until Not ech
 
   ' Calcul du rang d'apparition de Valeur pour la région en paramètre
   ival = Application.CountIf(PlageConditions.Resize(Valeur.Row - PlageValeurs.Row + 1), ValeurCondition)
 
   ' On recherche le rang d'apparition ival dans le tableau trié t
   ' quand on l'a trouvé, son rang est la valeur i à retourner
   For i = 1 To n
      If t(i, 1) = ival Then RangUniqueSi = i: Exit Function
   Next i
End Function

sylvanu · 27 Octobre 2022

Bonjour tout le monde,
Ou encore tout en VBA, comme en PJ.
Un appui sur le bouton, et "Hop!" c'est trié.

NB : J'ai considéré qu'en cas d'ex aequo c'est la magasin qui a le moins d'employé qui gagne, car c'est mieux de faire 30k€ à 3 plutôt qu'à 6.

mapomme · 27 Octobre 2022

thomas.bladier à dit:
pour mon cas le plus pertinent est de départager avec le nombre de personnel.
Le magasin ayant le plus de personnel et à CA égal avec un autre sera donc classé rang 1.

Je n'avions point vu cette histoire de Nombre de personnel donc j'ai proposé sans. J'étais resté avec la première version du fichier.

mapomme · 27 Octobre 2022

sylvanu à dit:
NB : J'ai considéré qu'en cas d'ex aequo c'est la magasin qui a le moins d'employé qui gagne, car c'est mieux de faire 30k€ à 3 plutôt qu'à 6.

C'est ce qui m'aurait semblé aussi.

thomas.bladier · 27 Octobre 2022

J'ai utilisé le code de la fonction de @mapomme , ça marche très bien pour mon fichier, merci beaucoup !

Merci à tous les trois, vous m'avez beaucoup aidé, ça va bien me servir pour mon projet.

Passez une bonne fin de journée.

mapomme · 27 Octobre 2022

thomas.bladier à dit:
le code de la fonction de @mapomme

Re,
Attention, la formule et la fonction personnalisée que j'ai proposées, respectent la question de départ et donc attribuent un rang différent à tous les magasins ex-æquo d'une région. Il faut le savoir.

XL 2019 Calculer un rang avec condition et sans ex aequo

thomas.bladier

XLDnaute Nouveau

Pièces jointes

mapomme

sylvanu

XLDnaute Barbatruc

Pièces jointes

thomas.bladier

XLDnaute Nouveau

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

Phil69970

XLDnaute Barbatruc

thomas.bladier

XLDnaute Nouveau

Pièces jointes

JHA

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

sylvanu

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

thomas.bladier

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

Discussions similaires