Discussion :

[monfiston]

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.

[Thautheme]

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

[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

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

[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 :

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]))}

[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?

[Philippe Tulliez]

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

[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?

[Thautheme]

Re,

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) ?!...

[monfiston]

Si, si, mais je pensais qu il y avait moyen de faire plus simple, mais visiblement non

[Pierre Fauconnier]

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.

[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

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

[Pierre Fauconnier]

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

[Qwazerty]

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

[monfiston]

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?

[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 :

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