Discussion :

[nouveauvba]

Bonjour à tous,

j'ai un listing de données dans une variable appelée zone définie ainsi:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

zone = Workbooks("Copie").Sheets("Feuil1").Range("A1").CurrentRegion

Dans cette variable, je filtre la colonne 4 pour trouver les valeurs des composantes de AA, BB, CC, DD, EE, FF, GG
j'ai besoin pour AA de connaitre la valeur min, la valeur max, la moyenne, la quantité inférieure à 1000, la quantité entre 1000 et 2000 la quantité entre 2000 et 3000 et enfin la quantité supérieure à 3000. Même chose pour BB, CC, DD, EE, FF, GG .

Pour cela j’utilise une variable tableau AA(c) et j'alimente mes résultats dans une autre variable tableau (tablo). Les données issue de l'analyse de AA sont stockée dans la ligne 1 et dans les colonnes suivantes de la variable tablo. Puis je traite BB de la m^me façon une variable tableau BB(d) résultats stockés dans tablo, puis CC(e) ect ect. Cela génére beaucoup de variables.

Pour info, les données issues de BB sont stockées dans la ligne 2 et colonne suivantes de la variable tablo et ainsi de suite.

J'effectue une seconde fois ce travail mais pour la colonne 5.

Mon bout de code pour déterminer AA (dans la colonne 4) est le suivant et il fonctionne, il est donc pratiquement identique pour BB (aux variables pres):

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
34
zone = Workbooks("Copie").Sheets("Feuil1").Range("A1").CurrentRegion
'récupère la ligne de données 
 
For i = LBound(zone, 1) To UBound(zone, 1)
        'regarde la ligne i et la colonne 4 si la valeur correspond à AA
        If zone(i, 4) = "AA" Then
        c = c + 1
        ReDim Preserve AA(c)
 
        AA(c) = Application.Index(zone, i)
        tablo(1, 1) = c 'écrit la quantité de AA dans le tableau de synthese
        'trouve la valeur min et l'écrit dans le tableau de synthese
        tablo(1, 2) = Application.Min(Application.Index(AA, , 3))
        'trouve la valeur max et l'écrit dans le tableau de synthese
        tablo(1, 3) = Application.Max(Application.Index(AA, , 3))
        'quantité inf 1000 et l'écrit dans le tableau de synthese
            If Application.Min(Application.Index(AA(c), , 3)) < 1000 Then
            tablo(1, 4) = tablo(1, 4) + 1
            End If
        'quantité 1000/2000 et l'écrit dans le tableau de synthese
            If Application.Min(Application.Index(AA(c), , 3)) >= 1000 And Application.Min(Application.Index(AA(c), , 3)) < 2000 Then
            tablo(1, 5) = tablo(1, 5) + 1
            End If
        'quantité 2000/3000 et l'écrit dans le tableau de synthese
            If Application.Min(Application.Index(AA(c), , 3)) >= 2000 And Application.Min(Application.Index(AA(c), , 3)) < 3000 Then
            tablo(1, 6) = tablo(1, 6) + 1
            End If
        'quantité sup 3000 et l'écrit dans le tableau de synthese
            If Application.Min(Application.Index(AA(c), , 3)) >= 3000 Then
            tablo(1, 7) = tablo(1, 7) + 1
            End If
        'calculs la moyenne l'écrit dans le tableau de synthese
        tablo(1, 8) = Application.Average(Application.Index(AA, , 3))
        End If

Première question dans cette ligne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If Application.Min(Application.Index(AA(c), , 3)) >= 1000 And Application.Min(Application.Index(AA(c), , 3)) < 2000 Then

Quelle est la raison pour laquelle il faut que je garde Application.Min si je l'enleve j'ai erreur d’exécution 13 incompatibilité de type?

Seconde question est il possible de faire AA,BB,CC.... colonne 4 et 5 en une fois plutôt que de traiter AA puis BB puis CC puis de nouveau AA colonne 5? Bref traiter AA BB en même temps...

Merci par avance.

[Al__22]

Pour ce qui est de Application.Min Microsoft réponds de lui même :
https://support.microsoft.com/fr-fr/help/291309
https://support.microsoft.com/en-us/help/213660

Pour le reste je dois comprendre

[nouveauvba]

Bonjour AL__22 merci pour le retour.

Je viens de regarder les liens.

Pour ce qui est de Application.Min Microsoft réponds de lui même :
https://support.microsoft.com/fr-fr/help/291309
https://support.microsoft.com/en-us/help/213660

mais ça reste encore très obscure pour moi.
Ce dont j'ai compris c'est qu' "application.min" cherche la valeur minimale dans mon filtrage.

Je ne comprends pas pourquoi, si je l’enlève dans une ligne où je me contrefiche de la valeur minimale j'ai l'erreur 13 (désolé) .

Il n'a pas d’intérêt (mais il doit bien en avoir un qui m’échappe car le code fonctionne...).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If Application.Min(Application.Index(AA(c), , 3)) < 1000 Then

[joe.levrai]

Bonjour,

l'incompatibilité de type s'explique par pure logique

ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Application.Index(AA(c), , 3)

Retourne l'intégralité de la colonne 3 ou 4 (si base 0 ou base 1) de ton tableau
C'est donc un Array qui est retourné


Application.Min, lui, retourne une valeur


Or, si tu peux parfaitement comparer une valeur avec une autre valeur (cas avec application.min) tu ne peux pas comparer un Array et une valeur !

Ensuite, pour traiter plusieurs colonnes en une fois, le plus simple serait de boucler sur chaque colonne pour alimenter un unique tableau de résultat AA(c)

Au final, je n'ai pas vraiment compris ce que tu cherchais à faire avec tes données, sur ton tableau final tu veux extraire uniquement les éléments dont la valeur est comprise entre deux bornes ? Si tel est le cas, autant réaliser cette condition en même temps que la création de ton tableau AA(c)

[nouveauvba]

Bonjour à tous,
Merci beaucoup Joe.levrai pour l'explication très claire.

Au final, je n'ai pas vraiment compris ce que tu cherchais à faire avec tes données, sur ton tableau final tu veux extraire uniquement les éléments dont la valeur est comprise entre deux bornes ? Si tel est le cas, autant réaliser cette condition en même temps que la création de ton tableau AA(c)

Pour plus de précisions :

je cherche à obtenir la quantité de AA (de la colonne 4 de mon listing), sa valeur min et max, la quantité de données (d’occurrences)< 1000, la quantité de données entre 1000 et 2000, la quantité de données entre 2000 et 3000 et la quantité de données> 3000 et la moyenne des AA dans cette colonne (code présenté dans le premier poste pour le traitement de AA de la colonne 4).

Même chose pour les données BB, CC, DD ,EE ,FF

J'effectue les mêmes extractions dans la colonne 5.

Cette analyse est effectuée en vue de la création d'un tableau de synthèse (la variable tablo) de 12 lignes 8 colonnes.



Je précise que mon code fonctionne mais j'essaie de voir si on peut le diminuer ou l'améliorer...

Merci encore

[joe.levrai]

Bonjour,

ce que tu cherches à obtenir se calcule aisément par formule excel, autant utiliser ceci plutôt que des tableaux non ?

On peut utiliser les formules en VBA, que ce soit pour les écrire sur une feuille de calcul, mais également (et ça va t'intéresser) pour calculer/évaluer directement dans la procédure les résultats !

va voir dans l'aide ces deux entrées :

- Application.Worksheetfunction : on utilise les formules Excel anglaises pour calculer un résultat
https://msdn.microsoft.com/fr-fr/lib.../ff834434.aspx

- Application.Evaluate : ça évalue un objet/un élément/une formule pour en retourner le resultat/la plage etc..
https://msdn.microsoft.com/fr-fr/lib.../ff193019.aspx


Par exemple pour te mettre sur la voie.
Soit une plage allant de A1 à A100
Je cherche dans cette plage à connaître :

- la valeur max
- la valeur min
- la moyenne
- le nombre d'éléments supérieurs à 50
- le nombre d'éléments inférieurs à 100

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Sub toto()
Dim MonMax As Long, MonMin As Long, MaMoyenne As Double, Nb_Sup_50 As Long, Nb_Inf_100 As Long
Dim MaPlage As Range
Set MaPlage = Range("A1:A100")
    With Application.WorksheetFunction
        MonMax = .Max(MaPlage)
        MonMin = .Min(MaPlage)
        MaMoyenne = .Average(MaPlage)
        Nb_Sup_50 = .CountIf(MaPlage, ">50")
        Nb_Inf_100 = .CountIf(MaPlage, "<100")
    End With
End Sub

cet exemple est bien sûr simplifié, on peut aussi faire un NB.SI.ENS (CountIfs) pour les cas où tu as plusieurs conditions (tes valeurs comprises entre deux bornes ... ou pour isoler les AA , BB)

[nouveauvba]

Merci pour ton retour, une fois de plus très clair.
Je regarde tout cela avec attention. Intéressant car il n'y a pas de boucle.
D'un point de vue vitesse de traitement selon toi laquelle est plus efficace? (je vais tester de ce pas, enfin à la vitesse de mes capacités de codage ).

[joe.levrai]

indéniablement, mais encore faudrait-il chronométrer ... je pense que c'est plus rapide de s'appuyer sur les formules excel que de collecter des éléments dans divers tableaux, boucler dessus à plusieurs reprises, pour construire de nouveaux tableaux de résultats

mais je peux me tromper
et quand même ce serait un chouilla plus long, ta procédure ici est parfaitement limpide, courte, compréhensible, facile à maintenir
On s'y perd moins qu'avec tes variables tableaux

j'utilise fréquemment du Worksheetfunction ou du Evaluate sur des grosses formules (parfois matricielles) basées sur des plages de plus de 100 000 lignes ... et je n'ai jamais eu à souffrir de traitements rédhibitoirement longs

[nouveauvba]

et quand même ce serait un chouilla plus long, ta procédure ici est parfaitement limpide, courte, compréhensible, facile à maintenir
On s'y perd moins qu'avec tes variables tableaux

C'est aussi mon objectif donc j'ai tout à y gagner.

j'utilise fréquemment du Worksheetfunction ou du Evaluate sur des grosses formules (parfois matricielles) basées sur des plages de plus de 100 000 lignes ... et je n'ai jamais eu à souffrir de traitements rédhibitoirement longs

Ca m'arrive aussi dans d'autres macro que je vais faire évoluer par la suite

Je test et regarde après comment je peux interagir avec la suite du code.

[nouveauvba]

Ah zut!
AA est en colonne 4. Les données numériques sont en colonne 3 pour le min max moyenne tranche...
On peut spécifier 2 plages?
Exemple si AA en colonne 4 je détermine le max de AA de la colonne 3 ect ect.
C'est jouable?

[patricktoulon]

Bonjour
j'en rajoute une couche a celle de joe que je salut au passage

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
Sub toto()
    Dim MonMax As Long, MonMin As Long, MaMoyenne As Double, Nb_Sup_50 As Long, Nb_Inf_100 As Long
    Dim MaPlage As Range
    Set MaPlage = Range("A1:D10")
    With Application.WorksheetFunction
        MonMax = .Max(MaPlage)    'max de la plage complete
        MonMaxcol2 = .Max(WorksheetFunction.Index(MaPlage, 0, 2))    'max de la colonne 2("B") de la plage
        MonMaxligne8 = .Max(WorksheetFunction.Index(MaPlage, 8, 0))  'max de la ligne 8 de la plage
        MonMin = .Min(MaPlage)
        MaMoyenne = .Average(MaPlage)    'moyenne de la plage
        MaMoyennecol3 = .Average(WorksheetFunction.Index(MaPlage, 0, 3))    'moyenne de la colonne 3 de la plage
        Nb_Sup_50 = .CountIf(MaPlage, ">50")    'nombre de chiffre plus grand que .. dans la plage complete
        Nb_Sup_50col2 = .CountIf(WorksheetFunction.Index(MaPlage, 0, 2), ">50")    'nombre de chiffre plus grand que 50  .. dans la colonne 2
        Nb_Sup_50ligne5 = .CountIf(WorksheetFunction.Index(MaPlage, 5, 0), ">50")    'nombre de chiffre plus grand que50  .. dans la ligne 5
        Nb_Inf_100 = .CountIf(MaPlage, "<100")
        'etc..etc....
    End With
    Debug.Print MonMaxligne8
' pour info
'macol2 = WorksheetFunction.Index(MaPlage, 0, 2).Value 'donne la colonne 2 dans une variable tableau a 2 dim (10 ligne sur 1 colonne )
'macol2 = Application.Transpose(WorksheetFunction.Index(MaPlage, 0, 2)) 'donne la colonne 2 dans une variable tableau a 1 dim (10 items)
'etc..etc...
End Sub

[nouveauvba]

Merci Patricktoulon pour ta contribution qui m'ouvre une porte supplémentaire.

bon j'essaie cela:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MonMax = .CountIfs(WorksheetFunction.Index(MaPlage, 0, 4), "AA", .Max(WorksheetFunction.Index(MaPlage, 0, 3)))

je vous le donne en mille j'ai l' erreur 1004 impossible de lire la propriété de Countifs de la classe worksheetfonction.

[patricktoulon]

dis moi en Français ce que tu essaie de faire avec cette ligne de code ?

[nouveauvba]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

MonMax = .CountIfs(WorksheetFunction.Index(MaPlage, 0, 4), "AA", .Max(WorksheetFunction.Index(MaPlage, 0, 3)))

Si dans ma colonne 4 j'ai l'identifiant AA,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.CountIfs(WorksheetFunction.Index(MaPlage, 0, 4), "AA",

et bien je récupère la valeur numérique maximale correspondant aux AA située en colonne 3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 .Max(WorksheetFunction.Index(MaPlage, 0, 3)

Je ne souhaite pas obtenir la valeur maximale de tout le listing mais bien celle correspondante à l'entée AA de la colonne 4.


Edit :
En fait Joelevrai m'a ouvert la voie. Dans Excel ça correspond à Max.si.ens.
L'éditeur de macro me propose cela

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ActiveCell.FormulaR1C1 = "=MAXIFS(C[-7],C[-6],""AA"")"

reste à le transcrire correctement.....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 MonMax = .MaxIfs(MaPlage, MaPlage2, "AA")

[nouveauvba]

J'ai trouvé.

Merci Joelevrai pour ton aide précieuse et l'aiguillage et Patricktoulon pour le complément très intéressant.