Excel : rech valeur max dans colonne

Résolu
ann3soph -  
Bonjour,

je souhaiterais trouver la valeur max dans une colonne L sur toutes les lignes qui ont la même valeur dans la colonne B.

Quelqu'un peut-il m'aider à trouver une formule pour faire ça ?

d'avance merci !

11 réponses

Résumé de la discussion

Problème : trouver dans Excel la valeur maximale de la colonne L pour toutes les lignes qui partagent la même valeur dans la colonne B, afin d’identifier les valeurs maximales associées à chaque groupe. Des éléments de réponse indiquent d’utiliser une formule matricielle, par exemple =MAX(SI(B:B=B1;L:L)) à saisir avec Ctrl+Shift+Entrée, afin de renvoyer le maximum des valeurs L correspondant à B1. Un inconvénient évoqué est que si les valeurs des colonnes B et L évoluent, la formule peut nécessiter une revalidation ou une macro qui s’exécute à chaque modification, pouvant alourdir la feuille. D'autres échanges suggèrent des ajustements et des variantes, tout en soulignant la nécessité d'adapter la solution à la structure du fichier et à l'évolution des données.

Bobot (l’IA à votre service)
  1. Mazzaropi Contributeur
    ann3soph, Bonjour.

    Vous voulez connaître la valeur MAX de la colonne L ou voulez savoir le nombre de fois une valeur de la colonne L apparaît dans la colonne B?

    S'il vous plaît, expliquer un peu mieux à vos besoins.
    0
    1. via55
      Re bonjour ;)

      Utilises la fonction sous-total avec le n° de fonction 4 pour MAX
      Ensuite tu appliques un filtre sur ta colonne B que sur les valeurs qui t'intéressent
      tu auras le max que des valeurs filtrées

      Cdlmnt
      0
      1. ann3soph
        Re-bonjour ;) (oui je me fais une journée casse tête excel)

        le problème c'est que j'ai 40 000 lignes (littéralement) et que donc je ne peux pas filtrer 1 par 1 les 4 000 valeurs qui sont prises dans ma colonne B... Il me faudrait une fonction que je puisse extendre sans qu'elle ne nécessite de filtre... :/ C'est possible ?
        0
    2. Mike-31
      Bonjour,

      veux tu la valeur max de la colonne L par rapport a une valeur connue de la colonne B ou souhaites avoir la valeur max par groupe de valeur colonne B
      0
      1. ann3soph
        bonjour, il me faudrait le max de la colonne L par groupe de valeurs dans la colonne B... :)
        0
    3. ann3soph
      Bonjour,

      je veux la valeur max de la colonne L mais pas de la colonne L tout entière, de la colonne L concernant les lignes pour lesquelles toutes les valeurs dans B sont égales.

      Je m'explique :

      si de B2 à B 23 toutes mes cases avaient pour valeur 10, je voudrais la valeur max en L entre L2 et L23.
      Et que cette formule puisse se répéter de manière à ce que je puisse ensuite avoir le max en L entre les lignes 24 et 56; si toutes les valeurs de B entre 24 et 56 sont les mêmes.

      Est-ce clair ? :)

      D'avance merci beaucoup !
      0
      3. Raymond PENTIER Contributeur
        Non ; ce n'est toujours pas très clair ...
        Les lignes ayant la même valeur sont-elles regroupées ou dispersées ?
        Et à quel endroit tes résultats doivent-ils apparaître ?
        Envoie donc ton fichier ; on travaillera directement dessus.
        Pour cela, utilise https://www.cjoint.com/ et reviens ici coller le lien obtenu.
        0
        1. ann3soph
          Malheureusement elles sont dispersées ! Mes résultats devraient apparaître dans la colonne N, idéalement sur la première ligne sur laquelle apparaît une valeur prise dans la colonne B.

          Merci beaucoup pour votre intérêt, n'hésitez pas si vous avez besoin d'autres infos !
          0
        2. ann3soph
          pas d'accès à cjoint, pour des raisons de confidentialité j'imagine... Désolée je me doute que ça vous aiderait bien à visualiser !!
          0
        3. Raymond PENTIER Contributeur
          ccm81 t'a fourni la bonne solution.
          0
        4. ccm81
          as tu jeté un oeil à ma proposition (post #5) et à celle de Vaucluse (post #10) (salut à toi et à tous par la même occasion)
          0
        5. Vaucluse Contributeur
          Et que donne ma proposition ci dessous
          a+
          0
      4. Vaucluse Contributeur
        Re
        essayez cette formule dans la, colonne résultat, toujours depuis ligne 1

        =MAX(SI(B:B=B1;L:L))

        Attention
        cette formule est matricielle, entrez là avec la touche enter en maintenant ctrl et shift enfoncées.
        Elle doit s'afficher entre accolades dans la barre de formule

        crdlmnt
        0
        1. ann3soph
          pour l'instant elle ne donne rien car excel tourne... le fichier est extrêmement lourd, je n'y connais rien mais j'ai l'impression que c'est à cause de ça que ça pose souvent problème avec les formules matricielles... :/
          0
        2. ann3soph
          Ah ça y est il a fini, mais malheureusement les résultats affichés ne collent pas avec ce que je devrais obtenir quand je regarde quelques lignes en particulier :( Merci beaucoup en tous cas !!
          0
        3. Vaucluse Contributeur
          Exact, si vous avez beaucoup de données, les formules matricielles alourdissent sérieusement.(les codes SOMMEPROD aussi)
          Mais il n'y a guère moyen de faire autrement vu ce que vous demandez, n'y aurait il pas moyen d'éditer une liste des valeurs de B dans un autre champ, sans doublon, pour réduire le nombre de formules définissant les maxi.?
          A+
          0
        4. ann3soph
          je vais essayer ça ! :)
          0
      5. Bertog
        Bonjour
        Excuses moi mais c'est juste pour être sur de comprendre. Tu veux identifier dans la colonne B toutes les valeurs qui sont égales au max des valeurs de la colonne L, c'est bien ça ?
        Personnellement, je créerais une nouvelle colonne à coté de B, donc une colonne C ou je mettrais la formule en première cellule et si c'est la ligne 1, =SI(B1=max(L:L);1;0) puis tu copie dans toutes les lignes de la colonne C. En filtrant les 1 ensuite, tu as toutes les lignes que tu cherches
        0
        1. ann3soph
          Bonjour,

          Non je souhaite trouver toutes les lignes qui ont la même valeur dans B, regarder les valeurs qu'elles prennent en L et dans une colonne N que j'ai crée à côté noter la valeur maximum des valeurs concernées en L :)

          Merci !
          0
        2. Bertog
          Désolé, mon navigateur indiquait 0 réponse quand j'ai vu la question, et ce n'est pas le cas !!
          En plus de toutes les propositions déjà évoquées, tu peux faire via un tableau croisé dynamique. Mais il faut ramener les 2 colonnes cote à cote si possible puis les identifier pour faire le tableau
          Dans le TCD tu mets la colonne B à gauche en première position et la colonne L collée juste à coté (pas dans le champs titres (ligne du haut) ou résultats (zone centrale)..
          Les valeurs de L s'afficheront pour chaque valeur de B et tu peux ensuite faire clique droit dans la colonne L du TCD, sélectionner "Paramètres de champs", puis "avancé" et cocher "Décroissant"
          0
      6. ccm81
        Comme dit notre ami Vaucluse, les formules matricielles sont longues à s'exécuter sur de gros fichiers
        Une autre proposition via une macro qui devrait aller plus vite
        https://www.cjoint.com/?3CeqVY2YIFk

        Cdlmnt
        0
        1. ann3soph
          Merci je regarderai ça aussi ce soir ! :)
          0
      7. ccm81
        Un inconvénient toutefois
        Si les valeurs changent en colonnes B et L, il faudra re-valider la formule

        On peut soigner ça, mais l'inconvénient sera que la macro sera exécutée à chaque changement dans la feuille, et du coup, ça risque d'alourdir
        https://www.cjoint.com/?3Ceq4qNYx0Y
        à toi de voir (il n'y a qu'une ligne qui diffère)
        0
        1. michel_m Contributeur
          Bonjour,

          En reprenant la maquette de CCM81 qu>e je salue , un autre macro itilisant l'objet dictionary 

          Sub par_dico()
          Dim Derlig As Long, T_colB(), T_colL()
          Dim Dico As Object, Cptr As Long, Cle As Long, MaxL As Long
          Dim T_max(), T_cle()
          Dim start As Single

          start = Timer
          Application.ScreenUpdating = False
          Derlig = Columns("B").Find("*", , , , , xlPrevious).Row
          T_colB = Application.Transpose(Range("B2:B" & Derlig).Value)
          T_colL = Application.Transpose(Range("L2:L" & Derlig).Value)

          Set Dico = CreateObject("scripting.dictionary")
               For Cptr = 1 To UBound(T_colB)
                    Cle = T_colB(Cptr)
                    MaxL = T_colL(Cptr)
                    If Not Dico.exists(Cle) Then
                         Dico.Add Cle, MaxL
                    Else
                         If T_colL(Cptr) > Dico.Item(Cle) Then Dico.Item(Cle) = T_colL(Cptr)
                    End If
               Next
               T_cle = Dico.keys
               T_max = Dico.items
               
               Cells(10, "N").Resize(Dico.Count, 1) = Application.Transpose(T_cle)
               Cells(10, "O").Resize(Dico.Count, 1) = Application.Transpose(T_max)
               
               Application.ScreenUpdating = True
               MsgBox "durée: " & Timer - start & " .seC;"

          End Sub


          La maquette de CCM81 avec mes élucubrations
          https://www.cjoint.com/?3CesdHEUUn8
          0