application.index problème et optimisation?

Version imprimable

Bonjour à tous,

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

Code:

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:

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:

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.

27/04/2017, 12h30
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 :mouarf:
27/04/2017, 13h55
nouveauvba

Bonjour AL__22 merci pour le retour.

Je viens de regarder les liens.

Citation:

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é) :calim2:.

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

Code:

If Application.Min(Application.Index(AA(c), , 3)) < 1000 Then
27/04/2017, 20h52
joe.levrai

Bonjour,

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

ceci :

Code:

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)
28/04/2017, 10h43
nouveauvba

1 pièce(s) jointe(s)

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

Citation:

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.

Pièce jointe 269203

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

Merci encore

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:

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)

28/04/2017, 11h20
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 :lol:).
28/04/2017, 11h30
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 :P

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
28/04/2017, 11h36
nouveauvba

Citation:

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.

Citation:

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. :mouarf:
28/04/2017, 11h59
nouveauvba

Ah zut!
AA est en colonne 4. Les données numériques sont en colonne 3 pour le min max moyenne tranche...:calim2:
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?

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

Code:

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

28/04/2017, 12h20
nouveauvba

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

bon j'essaie cela:

Code:

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. :oops:
28/04/2017, 12h27
patricktoulon

re

dis moi en Français ce que tu essaie de faire avec cette ligne de code ?
28/04/2017, 13h12
nouveauvba

Code:

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:

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

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

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

Code:

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

reste à le transcrire correctement.....

Code:

MonMax = .MaxIfs(MaPlage, MaPlage2, "AA")
28/04/2017, 15h24
nouveauvba

J'ai trouvé.

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

:ave: