Trouver la valeur max avec conditions

Version imprimable

09/02/2020, 10h07
monfiston

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 :

Col 1 col 2

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.

Col 1	col 2
S14	3
S14	5
S15	3
S15	7
S16	5

Bonjour Monfiston, bonjour le forum,

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

Code:

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

09/02/2020, 11h14
monfiston

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
09/02/2020, 11h44
Thautheme

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...
09/02/2020, 12h20
Philippe Tulliez

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:

{=MAX(SI($A$2:$A$6=$D$2;B2:$B$6))}

La même formule faisant référence à un tableau structuré

Code:

{=MAX(SI(Tableau2[Col 1]=D2;Tableau2[col 2]))}
09/02/2020, 12h26
monfiston

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?
09/02/2020, 12h38
Philippe Tulliez

Bonjour

Citation:

on est obliger de valider la formule matriciel avec touches Ctrl+Shift+Entrée?

Oui

Citation:

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

Citation:

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
09/02/2020, 13h17
monfiston

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?
09/02/2020, 13h25
Thautheme

Re,

Citation:

Envoyé par monfiston

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) ?!...
09/02/2020, 13h29
monfiston

Si, si, mais je pensais qu il y avait moyen de faire plus simple, mais visiblement non
09/02/2020, 14h27
Pierre Fauconnier

6 pièce(s) jointe(s)

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

Pièce jointe 537101

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

Pièce jointe 537102

2. Choisir l'outil de regorupement

Pièce jointe 537106

3. Préciser les options de regroupement

Pièce jointe 537114

4. Restituer le résultat dans Excel

Pièce jointe 537118

5. Un simple clic permet d'actualiser la source

Pièce jointe 537119

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.
09/02/2020, 14h45
monfiston

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
09/02/2020, 14h52
Pierre Fauconnier

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.

1 pièce(s) jointe(s)

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.

Pièce jointe 537123
Code:

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...

Salut

Sinon en VBA

Code:

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

09/02/2020, 17h12
monfiston

je viens de tester cette méthode

Code:

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?
09/02/2020, 18h13
Pierre Fauconnier

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:

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, ...)