Calcul de position en fonction du rang, avec des égalités

Résolu

MT -
MT - 15 févr. 2015 à 21:16

Bonjour à tous,

Après plusieurs jours de recherche et tentatives infructueuses, j'en appelle à la connaissance collective :
Je cherche à développer une fonction (on l'appelera CalculRang) qui renvoie le numéro de colonne d'une cellule en fonction du rang de sa valeur parmi d'autres, dans l'ordre croissant.
en entrée : MaPlage = la plage de l'ensemble des valeurs
en entrée : Rang = le rang de la valeur de la cellule dont on recherche le numéro de colonne
Attention, certaines valeurs de la plage peuvent être identiques, et c'est là que je coince notamment.
Pour contextualisation, l'appli globale est un harmonisateur de charge de travail, les colonnes sont des personnes et la valeur dont on cherche le rang une charge jusqu'à la veille. L'ajout de la charge du jour se fait dans l'odre de celui qui avait le moins travaillé vers celui qui a le plus travaillé jusqu'alors.

J'ai essayé pas mal de truc avec les fonctions large, ou search, mais sans grand résultat. Et je suis à court d'idée.

Merci par avance de votre aide

MT

exemple :
MaPlage = A1H1 et contient {5,8,9,6,4,3,5,5}

CalculRang(MaPlage,1) renvoie 6
CalculRang(MaPlage,2) renvoie 5
CalculRang(MaPlage,3) renvoie 1, 3 ou 5, peu importe, mais la valeur renvoyée ne doit pas l'être de nouveau
CalculRang(MaPlage,4) même affaire que la précédente...
etc.

A voir également:

Calcul de position en fonction du rang, avec des égalités
Fonction si et - Guide
Ma position - Guide
Calcul moyenne excel - Guide
Suivi position google - Guide
Calcul km marche à pied gratuit - Télécharger - Sport

4 réponses

Réponse 1 / 4

En fait pour être plus complet : j'ai réussi à programmer la version sans égalité parmi les valeurs : en utilisant la fonction large puis en recherchant la valeur retournée parmi la plage, puis en regardant la colonne de la cellule qui correspond. Mais avec ce principe, en cas d'égalité c'est toujours la même colonne qui est renvoyée quand bien même on souhaite le rang suivant.

MT

Réponse 2 / 4

pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention 2 761

Bonjour,

Que souhaites tu réellement en cas de doublon?
Soit CalculRang doit renvoyer une seule valeur (au hasard parmi les colonnes possibles) soit elle doit retourner toutes les colonnes correspondantes.
Dans le second cas, il faudra un paramètre supplémentaire.

Réponse 3 / 4

Dans les cas d'égalité CalculRang doit renvoyer une seule valeur. Cependant elle ne doit pas renvoyer la même valeur pour les valeurs de Rang (entrée) égales à 3, 4 et 5. Elle doit renvoyer une colonne différente à chaque fois, mais peu m'importe l'ordre.

Ex : CalculRang(MaPlage,3) renvoie 1 ; CalculRang(MaPlage,4) renvoie 7 ; CalculRang(MaPlage,5) renvoie 8.

Et pas : CalculRang(MaPlage,3) renvoie 1 ; CalculRang(MaPlage,4) renvoie 1 ; CalculRang(MaPlage,5) renvoie 1.

pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention 2 761

A titre indicatif, pour ne pas surcharger le recalcul automatique des feuilles de ton classeur, combien (environ) vas tu placer de ces formules dans tes feuilles?

MT > pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention

Compte environ 4000 exécutions de cette fonction par run de l'appli principale. Pour la taille de la plage mon exemple est dans l'ordre de grandeur.

pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention 2 761 > MT

Faut alors que je revoie ma fonction personnalisée. Son temps de calcul est de l'ordre de 8 secondes pour 10 000 formules...

Réponse 4 / 4

pijaku Messages postés 12263 Date d'inscription Statut Modérateur Dernière intervention 2 761

Bon.
Voici le résultat.

Option Explicit

Function CalculRang(maPlage As Range, Rang As Integer)
Dim temp(), tb(), j As Long, k As Long

    For j = 1 To maPlage.Columns.Count
        If k > 0 Then
            If Not Exist(temp(), maPlage.Cells(1, j).Value) Then
                k = k + 1: ReDim Preserve temp(1 To 2, 1 To k)
                temp(1, k) = maPlage.Cells(1, j).Value
                temp(2, k) = maPlage.Cells(1, j).Column
            End If
        Else
            k = k + 1: ReDim Preserve temp(1 To 2, 1 To k)
            temp(1, k) = maPlage.Cells(1, j).Value
            temp(2, k) = maPlage.Cells(1, j).Column
        End If
    Next j
    tb = Application.Transpose(temp)
    Call Quick(tb, LBound(tb), UBound(tb))
    CalculRang = tb(Rang, 2)
End Function

'sources : http://boisgontierjacques.free.fr/
Sub Quick(a, gauc, droi) ' Quick sort
Dim ref, g, d, temp
ref = a((gauc + droi) \ 2, 1)
g = gauc: d = droi
Do
   Do While a(g, 1) < ref: g = g + 1: Loop
   Do While ref < a(d, 1): d = d - 1: Loop
   If g <= d Then
      temp = a(g, 1): a(g, 1) = a(d, 1): a(d, 1) = temp
      temp = a(g, 2): a(g, 2) = a(d, 2): a(d, 2) = temp
      g = g + 1: d = d - 1
   End If
Loop While g <= d
If g < droi Then Call Quick(a, g, droi)
If gauc < d Then Call Quick(a, gauc, d)
End Sub

Function Exist(tb(), maVal) As Boolean
Dim i As Long
For i = LBound(tb, 2) To UBound(tb, 2)
    If tb(1, i) = maVal Then Exist = True: Exit Function
Next
Exist = False
End Function

Dans la cellule, saisir la formule exemple :

=CalculRang(A2:F2;3)

autre exemple :
permet "d'étirer" la formule vers le bas :

=CalculRang(A2:F2;LIGNE()-1)

ou encore :

=CalculRang(I$21:P$21;LIGNE()-20)

🎼 Cordialement,
Franck 🎶

merci !

Discussions similaires

Les meilleurs places bercy / accor hotel arena

Calcul des ventes par mois d'un produit

Votre réponse

Discussions similaires