Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Trouver la valeur max avec conditions


Sujet :

Macros et VBA Excel

  1. #1
    Membre du Club
    Trouver la valeur max avec conditions
    Bonjour
    Pour vous quelle serait la meilleure solution pour trouver la valeur max d'une colonne(2) par rapport à une donnée texte d'une autre colonne (1) ?

    Exemple :


    [TH]Col 1[/TH]
    [TH]col 2[/TH]
    S14 3
    S14 5
    S15 3
    S15 7
    S16 5

    Je ne souhaite pas utiliser de fonction recherche, ni de tableau croisé dynamique, peu importe en VBA ou en Excel, mais le point de départ sera en VBA.

  2. #2
    Membre émérite
    Bonjour Monfiston, bonjour le forum,

    Si j'; ai bien compris, ce qui est rare, essaie comme ça :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    Sub Macro1()
    Dim O As Worksheet 'déclare la variable O (Onglet)
    Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
    Dim D As Object 'déclare la variable D (Dictionnaire)
    Dim I As Integer 'déclare la variable I (Incrément)
    Dim J As Integer 'déclare la variable J (incrément)
    Dim TMP 'déclare la variable TMP (tableau TeMPoraire)
    Dim TM() 'déclare la variable TM (Tableau des Max)
     
    Set O = Worksheets("Feuil1") 'définit l'onglet O (à adapter à ton cas)
    TV = O.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
    Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire D
    For I = 1 To UBound(TV, 1) 'boucle sur toutes les lignes I du tableau des valeurs TV
        D(TV(I, 1)) = "" 'alimente le dictionnaire D avec les données de la colonne 1 de TV
    Next I 'prochaine ligne de la boucle
    TMP = D.Keys 'récupère dans le tableau temporaire TMP la liste des éléments du dictionnaire D sans doublons
    ReDim TM(1 To 2, 1 To D.Count) 'redimensionne le tableau des max TM
    For J = 0 To UBound(TMP) 'boucle 1 : sur tous les éléments J du tableau temporaire TMP
        TM(1, J + 1) = TMP(J) 'définit la valeur de la ligne 1 de TM
        TM(2, J + 1) = 0 'définit la valeur de la ligne 2 de TM
        For I = 1 To UBound(TV, 1) 'boucle 2 : sur toutes les lignes I du tableau des valeurs TV
            If TV(I, 1) = TMP(J) Then 'condition : si la donnée en colonne 1 du tableau des valeurs TV est égele à l'élément J de TMP
                'si la donnée en colonne 2 de TV est supérieure à la donnée en ligne 2 de TM,
                'la donnée en ligne 2 de TM devient la valeur en colonne 2 de TV
                If TV(I, 2) > TM(2, J + 1) Then TM(2, J + 1) = TV(I, 2)
            End If 'fin de la condition
        Next I 'prochaine ligne de la boucle 2
    Next J 'prochain élément de la boucle 1
    O.Range("D1").Value = "Donnée" 'écrit en D1
    O.Range("E1").Value = "Max" 'écrit en E1
    'renvoie en D2 redimensionnée le tableau TM transposé
    O.Range("D2").Resize(UBound(TM, 2), UBound(TM, 1)).Value = Application.Transpose(TM)
    End Sub
    À plus,

    Thauthème

    Je suis Charlie

  3. #3
    Membre du Club
    ha oui, quand même
    c'est quand même assez compliqué
    je pensait qu'il existait une fonction/formule qui pouvait solutionner mon pb en quelque lignes

    merci quand même, je vais tester ta méthode

  4. #4
    Membre émérite
    Re,

    Le problème c'est que je suis nul en formules mais je pense qu'un balèze d'ici pourra te résoudre ça en moins de deux...
    À plus,

    Thauthème

    Je suis Charlie

  5. #5
    Rédacteur

    Bonjour,
    Il existe depuis la version 2016, la fonction MAX.SI mais pour les précédentes, il y a une solution entre autres avec une fonction matricielle
    $D$2 contient la valeur du texte se trouvant en colonne A dont on cherche la valeur maximum

    Formule à valider à l'aide de la combinaison des touches Ctrl+Shift+Entrée
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    {=MAX(SI($A$2:$A$6=$D$2;B2:$B$6))}


    La même formule faisant référence à un tableau structuré
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    {=MAX(SI(Tableau2[Col 1]=D2;Tableau2[col 2]))}
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Quelques contributions : USERFORM - Créer, Consulter, Modifier et Supprimer des enregistrements à l'aide d'un formulaire - Géolocalisation d'une adresse avec Excel et Google sans VBA

  6. #6
    Membre du Club
    merci
    actuellement, je n'ai pas le pc sous 2013, je ne pourrais pas tester avant mardi

    mais je ne comprend pas ta dernières phrares
    on est obliger de valider la formule matriciel avec touches Ctrl+Shift+Entrée?


    du coup, il n'est pas possible de générer la formule et de trouver le résultat via vba automatiquement, d'une manière invisible pour les utilisateurs?

  7. #7
    Rédacteur

    Bonjour
    on est obliger de valider la formule matriciel avec touches Ctrl+Shift+Entrée?
    Oui
    du coup, il n'est pas possible de générer la formule et de trouver le résultat via vba automatiquement, d'une manière invisible pour les utilisateurs?
    Je n'avais pas vu que tu cherchais une solution par VBA distrait par ta réaction à la réponse de Thauthème que je salue au passage
    ha oui, quand même
    c'est quand même assez compliqué
    je pensait qu'il existait une fonction/formule qui pouvait solutionner mon pb en quelque lignes
    donc il est difficile de savoir ce que tu souhaites vraiment. Une formule native d'excel ou une solution par VBA ?

    Mais évidement, il est possible de traduire la solution que j'ai publiée en fonction personnelle en VBA mais franchement, je ne vois pas l'intérêt. On peut également effectuer la somme d'une plage par VBA si tu le souhaites
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Quelques contributions : USERFORM - Créer, Consulter, Modifier et Supprimer des enregistrements à l'aide d'un formulaire - Géolocalisation d'une adresse avec Excel et Google sans VBA

  8. #8
    Membre du Club
    quelque chose de simple et de rapide, car les colonnes on plusieurs milliers de lignes

    si c'est par formule, j'écrirais la formule en vba

    ma solution, c'était de mettre les données de la feuille excel dans une variable tableau
    et de tester chaque ligne, par rapport à une autre, à l'aide de 2 boucles

    je pensais qu'il y avait plus simple?

  9. #9
    Membre émérite
    Re,

    Citation Envoyé par monfiston Voir le message
    quelque chose de simple et de rapide, car les colonnes on plusieurs milliers de lignes

    si c'est par formule, j'écrirais la formule en vba

    ma solution, c'était de mettre les données de la feuille excel dans une variable tableau
    et de tester chaque ligne, par rapport à une autre, à l'aide de 2 boucles

    je pensais qu'il y avait plus simple?

    Ha bon... Et ce n'est pas ce que je t'ai proposé (à peu de choses près) ?!...
    À plus,

    Thauthème

    Je suis Charlie

  10. #10
    Membre du Club
    Si, si, mais je pensais qu il y avait moyen de faire plus simple, mais visiblement non

  11. #11
    Responsable
    Office & Excel

    Salut.

    En VBA, EVALUATE "comprend" que tu travailles en matricielle... Dès lors, tu peux avoir ceci, sur base d'une table de données nommée Tableau1, par exemple





    En 2013, tu peux aussi te tourner vers Power Query, qui permettrait de créer un tableau résultat sans doublons sur la colonne 1 et avec la valeur max en colonne 2. L'avantage est qu'un simple clic droit sur le tableau résultat permet d'actualiser ce dernier suite à des modifications du tableau source

    1. Créer la solution Power Query sur base du tableau source




    2. Choisir l'outil de regorupement




    3. Préciser les options de regroupement




    4. Restituer le résultat dans Excel




    5. Un simple clic permet d'actualiser la source




    Toute cette démarche Power Query peut bien entendu s'intégrer dans un traitement VBA, si on part du principe que je préconise de planter le décor (tableaux, graphiques, TCD, Power QUery, ...) puis de faire jouer le vba (forcément plus léger) pour faire prendre le tout.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  12. #12
    Membre du Club
    Merci,
    J aime bien ta formule EVALUATE..... sous vba
    Tu me confirmes bien que qu'elle fonctionne sous 2013, car je n ai pas accès à ces ordinateurs avant mardi
    Et je vais coder mon projet dès demain matin sous office 365 Pour le moment

  13. #13
    Responsable
    Office & Excel

    Sans soucis depuis 2007 (à cause des tableaux structurés, car EVALUATE fonctionne depuis que le VBA existe)

    Il faut juste noter que EVALUATE, tel que rédigé ici, fonctionne avec le classeur actif, quel que soit le classeur qui contient le code. Ne sont donc concernés par cette remarque que les xlam et le classeur de macros personnelles, à mon avis.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  14. #14
    Responsable
    Office & Excel

    Si tu devais récupérer les valeurs de la colonne1 dédoublonnées et placer la formule sur chaque ligne du tableau dédoublonné, tu peux aussi remplacer Power Query par quelques lignes de vba, sur base d'un tableau source Tableau1 et d'un tableau t_Résulats créé lors de la conception.

    Je pense que les lignes de code parlent d'elles-mêmes et qu'il n'est pas nécessaire de les commenter.





    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    Sub Test1()
      If Not Range("t_Résultats").ListObject.DataBodyRange Is Nothing Then Range("t_résultats").ListObject.DataBodyRange.Delete
      Range("tableau1[col1]").Copy
      Range("t_Résultats[col1]").PasteSpecial xlPasteValues
      Range("t_Résultats").RemoveDuplicates Columns:=1
      Range("t_Résultats[Col2]")(1).FormulaArray = "=MAX(Tableau1[Col2]*(Tableau1[Col1]=[@Col1]))"
    End Sub


    Cela dit, si c'est pour réaliser cela, un TCD classique Excel est plus simple à mettre en place que du VBA ou du Power Query...


    Je rappelle ma signature à toutes fins utiles: VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  15. #15
    Expert éminent
    Salut

    Sinon en VBA

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    Option Explicit
     
    Public Function Maxi(Plage As Range, Valeur As String) As Long
    Dim vMax As Long, bFind As Boolean
    Dim aCell As Range
        'Fait en sorte de recalculer la formule en cas de modification des cellules
        Application.Volatile True
     
        'init
        bFind = True
     
        'On boucle sur la plage
        For Each aCell In Plage.Columns(1).Cells
            If (aCell = Valeur) Then
                bFind = bFind Or vMax < aCell.Offset(0, 1).Value
                If bFind Then
                    vMax = aCell.Offset(0, 1).Value
                    bFind = False
                End If
            End If
        Next
        Maxi = vMax
    End Function


    La fonction est utilisable directement dans du code VBA mais également dans une cellule
    =maxi(Tableau1;E2) avec en E2 la valeur texte recherchée

    ++
    Qwaz

    MagicQwaz := Harry Potter la baguette en moins
    Le monde dans lequel on vit
    Ma page perso DVP
    Dernier et Seul Tutoriel : VBA & Internet Explorer
    Dernière contribution : Lien Tableau Structuré et UserForm
    L'utilisation de l’éditeur de message

  16. #16
    Membre du Club
    je viens de tester cette méthode

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    max = Evaluate("max(Tableau1[col2]*(Tableau1[col1]=""S15""))")


    ça fonctionne impeccablement, c'est nickel, ultra rapide, simple, rien à dire


    par contre dans ma colonne 2, j'ai quelque valeurs texte, malheureusement, la formule me génère une erreur

    vous auriez une piste?

  17. #17
    Responsable
    Office & Excel

    Normalement, ça ne devrait pas arriver. Une colonne ne devrait contenir que des valeurs d'un seul type

    Cela étant dit, voici la formule adaptée à faire évaluer par EVALUATE.
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    evaluate("MAX(IF(ISNUMBER(Tableau1[Col2]),Tableau1[Col2],0)*(Tableau1[Col1]=""S15""))")


    C'est pour cela que j'aime penser Excel lorsque j'écris du VBA pour Excel. Il suffit d'adapter la formule. Avec un développement "de programmeur", il faut modifier plus de code

    Si tu dois adapter la formule par la suite, tu peux rédiger la formule dans Excel puis, en laissant la cellule contenant la formule active, tu passes en VBA et dans la fenêtre d'exécution (Ctrl+G), tu saisis ? activecell.formula puis ENTER. Tu auras alors la formule en anglais que tu pourras insérer dans la chaîne à évaluer, en l'adaptant au besoin (doublement des " si la formule Excel contient des guillemets, pas exemple, remplacement d'une valeur "en dur" par l'insertion d'une variable, ...)
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Une fois pour toutes, je donne mon avis. Je ne vais pas le répéter à chaque message...
    Si je propose une solution générique sur votre solution spécifique, c'est parce que, fainéant de nature, je privilégie le réutilisable...
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------