XL 2016 les 4 meilleurs/pires sans trier ?

[F22Raptor]

Bonjour à tous,
Ci-joint, un tableau avec en colonne des dates, et en ligne des items, et une valeur dans chaque cellule.
Il faut imaginer une centaine de dates/colonnes, et jusqu'à quelques dizaines d'items ...

Je souhaite obtenir pour chaque date la médiane des N meilleurs items, et des N pires items. Disons que N = 4 (mais ça pourrait être changé).
En manuel, il faut que je trie sur chaque date par ordre décroissant, je fais MEDIANE(B2:B5) pour les meilleurs, MEDIANE(B12:B15) pour les pires, je copie/colle les deux résultats, et je recommence en triant la date suivante.

Un peu fastidieux avec 100 dates !
Je pourrais faire un fonction en VBA, mais s'il y a une manière plus simple, en formule (matricielle ?), je suis preneur !

 

[chris]

Bonjour

Une solution PowerQuery (intégré à 2016) : je ne sais pas s'il fallait lister les items mais c'est possible.

Actualiser par Données, Actualiser Tout

Edit : fichier modifié car déplacement à tort des données à droite de la source ce qui ne permet pas d'actualiser

 

[F22Raptor]

merci Chris !
J'avoue que PowerQuery est bien au-delà de mes compétences XL !

 

[tbft]

Bonjour

avec les fonctions equation.rang ou rang ??

 

[tbft]

voici un exemple qui utilise les fonctions rang équation.rang equiv et décaler

 

[F22Raptor]

Merci Tbft.
Dans ton tableau, où calcules-tu la médiane ?
Je vois que ça retourne les 3 pires et 3 meilleurs, mais pas directement leur médiane ?

Et au cas où ce serait trop complexe/lourd à faire directement en formule, j'ai écrit ce petit bout de fonction VBA

Function MaMediane(MaZone As Range, MonNbre As Integer, Ordre As Byte)
    ' MonNbre : N meilleurs ou N pires de la zone // Ordre : 0 pour ordre décroissant (médiane des meilleurs), sinon 1

    Dim i As Integer, j As Integer
    
    Dim ListeValeurs() As Double
    ReDim ListeValeurs(MonNbre - 1)
    
    j = 0
    
    For i = 1 To MaZone.Rows.Count
    
        If Application.Rank_Eq(MaZone.Cells(i, 1).Value, MaZone, Ordre) <= MonNbre Then ListeValeurs(j) = MaZone.Cells(i, 1).Value: j = j + 1
    
    Next i

    MaMediane = Application.Median(ListeValeurs)

End Function

 

[chris]

RE

10 manips en tout via les menus avec quelques formules c'est pas plus , voire moins, compliqué que VBA...

 

[tbft]

Bonjour

Je n'avais rien fait pour le calcul de la médiane