Tableaux, ubound, moyenne sous contraintes
Bonjour cher forum!
J'ai un problème de compréhension de code. C'est un code qui doit calculer la moyenne des valeurs présentes dans une colonne pour chaque groupe ayant la même référence (valeur associée dans une autre colonne). J'aimerais le comprendre pour pouvoir:
1- progresser (et pouvoir aider par la suite sur le forum
2- modifier le code pour l'utiliser dans différentes situations. Il y a deux colonnes de résultats, et j'aimerais par exemple en utiliser qu'une. J'ai essayé de modifier les indices mais vu ke je ne comprends tout le code!
Donc si quelqu'un a la gentillesse d'ajouter quelques lignes de commentaire dessus (en PJ), je la remercie beaucoup!!
En particulier cette partie:
For i = 2 To UBound(b)
If Not IsEmpty(b(i, 1)) And Not IsEmpty(s(i, 1)) And IsNumeric(s(i, 1)) Then
If ip.Exists(b(i, 1)) Then 'les 6 lignes suivantes sont très floues pour moi
v = ip(b(i, 1))
v(0) = v(0) + s(i, 1): v(1) = v(1) + 1
ip(b(i, 1)) = v
Else
ip.Add b(i, 1), Array(s(i, 1), 1)
End If
End If
Next
b = ip.Keys 'ici on attribue les clés et items aux variables a et b
s = ip.Items
ReDim v(UBound(b) - (UBound(b) < 0), 1) ' Ici c'est la 1ere fois que je vois "Ubound" utilisé comme ça ::~
For i = 0 To UBound(b) 'on calcul la moyenne ici? Mais c'est pas clair du tout!
v(i, 0) = b(i)
v(i, 1) = s(i)(0) / s(i)(1)
Next
With Worksheets("feuil1")
Set plage = Worksheets("feuil1").Range("A1").End(xlToRight)(2, 1)
plage.Resize(i - (i = 0), 2).Value = v
End With
End With
End SubSalut ! Drôle de titre... !
avec les déclarations de variables (dans la procédure, et module si utilisées) la lecture pourrait éviter des conjectures...
Bonne soirée.
A+
Hé!! bonjour MFerrand!!
Je progresse petit à petit!!
D'accord y'a pas de problème! merci quand même d'avoir répondu! Et pour le code je le remets en entier mais je l'ai aussi mis en PJ, dans le message c'était pour insister sur une partie mais effectivement sans les déclarations de variables c'est encore moins claire!!
Sub test()
Dim dat1 As Range, dat2 As Range, plage As Range
Dim i&, b(), s(), v()
Dim ip As Object
With Worksheets("feuil1")
Set ip = CreateObject("scripting.dictionary")
Set dat1 = Worksheets("feuil1").Range(.Range("B1"), .Range("B14").End(xlDown))
Set dat2 = Worksheets("feuil1").Range(.Range("L1"), .Range("L14").End(xlDown))
b = dat1.Value
s = dat2.Value
For i = 2 To UBound(b)
If Not IsEmpty(b(i, 1)) And Not IsEmpty(s(i, 1)) And IsNumeric(s(i, 1)) Then
If ip.Exists(b(i, 1)) Then 'les 6 lignes suivantes sont très floues pour moi
v = ip(b(i, 1))
v(0) = v(0) + s(i, 1): v(1) = v(1) + 1
ip(b(i, 1)) = v
Else
ip.Add b(i, 1), Array(s(i, 1), 1)
End If
End If
Next
b = ip.Keys 'ici on attribue les clés et items aux variables a et b
s = ip.Items
ReDim v(UBound(b) - (UBound(b) < 0), 1) ' Ici c'est la 1ere fois que je vois "Ubound" utilisé comme ça ::~
For i = 0 To UBound(b) 'on calcul la moyenne ici? Mais c'est pas clair du tout!
v(i, 0) = b(i)
v(i, 1) = s(i)(0) / s(i)(1)
Next
Set plage = Worksheets("feuil1").Range("A1").End(xlToRight)(2, 2)
plage.Resize(i - (i = 0), 2).Value = v
End With
End SubMerci et Bonne soirée!!!
J'ai lu dans l'intermède entre les films...
Il y a plusieurs détails discutables, en particulier le premier qui fait affecter toute la colonne (B d'une part, L d'autre part) aux variables, et qui doit sûrement résulter d'une erreur d'écriture... mais les différents points que je réserve ne nuisent pas au résultat, tu obtiens la moyenne pour chaque I_Pacage.
Que veux-tu savoir ?
Et surtout que veux-tu en faire ?
(Je ne me lance pas dans l'explication détaillée du code, c'est toujours plus long que l'écrire !
D'accord super!!
En fait je veux pouvoir utiliser que la colonne des moyennes générées. Or pour le moment le code génère deux colonnes: (I_PACAGE) + la colonne des moyennes correspondantes (je crois que c'est la remarque que tu as faite?).
Si ce n'est pas un code "faux" , alors j'aimerais déjà savoir si j'ai bien compris ce que je crois avoir compris
If Not IsEmpty(b(i, 1)) And Not IsEmpty(s(i, 1)) And IsNumeric(s(i, 1)) Thenici on vérifie que les cellules des 2 colonnes ne sont pas vides et que la 2eme " s(i, 1)" contient une valeur numérique.
If ip.Exists(b(i, 1)) Thenici, si j'ai bien retenu ma leçon, on vérifie si la valeur contenue dans b(i, 1) est contenue dans le dico.
v = ip(b(i, 1))ici ce n'est pas clair... si b(i, 1) existe alors on attribue quoi (la clè ou l'item?) au tableau v??
Et je m'arrête sur ces 2 lignes pour le moment
v(0) = v(0) + s(i, 1): v(1) = v(1) + 1
ip(b(i, 1)) = vici "V(0)" correspond à la première ligne du tableau v?? et on ajoute à ça valeur actuelle la valeur de la ligne s(i, 1) correspondant à b(i, 1) (qui existait déjà dans le dico: condition vérifiée)
Merci d'avance pour ta réponse!!
Quand je dis "utiliser" la colonne des moyennes c'est déjà savoir qu'est-ce qui la représente exactement dans le code
Bonsoir,
Là on ne fait pas une moyenne générale, mais une moyenne par élément recensé en B. Si tu as 2 colonnes à l'arrivée, une indique de quoi on fait la moyenne, et la seconde la moyenne elle-même.
Ce qu'on teste avec : If ip.Exists(b(i, 1)) c'est que l'élément de dico existe.
b(i, 1) est la clé, soit un élément de B dont on fait la moyenne. Exemple : 971001269.
Si l'élément n'existe pas, on le crée en lui affectant une valeur. La valeur qu'on lui affecte est un tableau de 2 éléments : la valeur dont on fait les moyennes (en L) et 1 puisqu'à la création on n'a incorporé qu'une valeur.
Exemple : ip(971001269) = Array(8, 1) [en remplaçant les variables par leur valeur à la première ligne]
Si l'élément existe déjà (prenons le même) : on extrait sa valeur dans v (qui sera donc un tableau) et on ajoute la valeur en L au premier élément et 1 au second, on fait donc : v= (8+14, 1+1) = (22, 2) qu'on réaffecte à l'élément dico ip(971001269).
Tu vois facilement que la clé indique l'élément dont on va sortir la moyenne, et la valeur fournira la moyenne par : total/nb soit : 22/2=11.
Bonjour! Réponse tardive!
Merci pour cette réponse! Si ce n'est pas trop demandé il y a deux choses qui me pose toujours problème!
ici, si j'ai bien compris on attribue le tableau (à 2 éléments) "v" à la clé b(i, 1):
v = ip (b( i , 1) )mais ensuite que signifie la ligne
ip (b( i , 1) ) = v?
La deuxième chose c'est la ligne
ip.Add b(i, 1), Array(s(i, 1), 1)ici, une nouvelle clé est crée? Avec un nouveau tableau "array" c'est bien ça? Car il y a un tableau à 2 éléments crée pour chaque clé mais je ne voix qu'un seul tableau c'est "v". En fait quel est le nom du nouveau tableau à 2 éléments pour la nouvelle clé? J'espère ne pas être trop insistant! en tout cas merci et bonne soirée!!
Je reprends l'exemple :
on est au stade où on a : ip (b( i , 1) ) = (8, 1)
En faisant : v = ip (b( i , 1) ), on a donc : v = (8, 1)
On traite ensuite v pour ajouter à chaque élément, et on obtient : v = (22, 2)
On réaffecte alors v (qui a changé de valeur à l'élément du dico.
En faisant : ip (b( i , 1) ) = v, on aura maintenant : ip (b( i , 1) ) = (22, 2)
Pour : ip.Add b(i, 1), Array(s(i, 1), 1), ce n'est qu'une question de syntaxe.
On a utilisé ici une syntaxe : dico.Add clé, item
Le résultat est toutefois le même qu'avec la syntaxe : dico(clé) = item
ip( b(i, 1) ) = Array(s(i, 1), 1)
Cordialement.
Bonjour,
J'ai compris le code mais je n'ai pas réussi à afficher que la colonne des moyennes! Je reviendrai dessus après!
Je pensais que le dictionnaire permettait de mettre en mémoire vive et donc que j'aurais pu le faire!
Mais tu peux le faire ! Ma question portait sur le pourquoi le faire car tu supprimes une information qui va faire défaut pour savoir de quoi il s'agit ?
Re,
En fait c'est parce j'ai des calculs à faire sur d'autres variables pour cette même liste d'individus "I_PACAGE" alors je ne voulais pas qu'elle réapparaisse à chaque fois que je veux afficher les résultats d'une nouvelle variable...
Je vais plutôt essayer de rajouter des colonnes au tableau "v" pour effectuer les autres calculs!:idea: Je pense que c'est possible
Bé ! Tu as 2 colonnes en résultats parce que v est constitué avec 2 colonnes, si tu n'en veux qu'une, il faut supprimer la première...
On voit dans le détail plus tard, tu vas comprendre... mais c'est l'heure de m'occuper du repas
D'accord merci et bon appétit
En fait jusqu'à maintenant j'essayais d'afficher celle que je voulais plutôt que de supprimer l'autre...
Me revoilà, il me semble que l'on avait lors des explications du début shunté la fin de la procédure au profit du dico.
Reprenons-la au passage des valeurs du dico dans les tableaux...
b = ip.Keys 'ici on attribue les clés et items aux variables a et b
s = ip.Items
ReDim v(UBound(b) - (UBound(b) < 0), 1) ' Ici c'est la 1ere fois que je vois "Ubound"On commence par les 3 premières lignes (pas aussi simples qu'il y paraît) :
b, s et v sont des tableaux dynamiques (voir déclarations).
A b et s on affecte respectivement les clés et les items du dico. Ces deux tableaux vont avoir le même nombre de lignes, mais n'oublie pas que les items sont constitués de tableaux donc s aura aussi 2 colonnes.
v fait l'objet d'un redimensionnement avec ReDim, on le dimensionne en lignes sur b, donc également sur s, (malgré la façon qui t'a paru curieuse qu'on va voir ensuite) et 2 en colonnes (0 et 1) qui correspondent aux 2 colonnes qui seront servies dans la feuille.
Le dimensionnement de la première dimension de v mérite un détour :
UBound(b) l'aligne sur b, mais c'est assorti de - (UBound(b) < 0).... !
L'expression est entre parenthèses, ce qui signifie que si UBound(b) n'est pas inférieur à 0, l'expression renvoie 0, et on est dans le cas général...
Mais pourquoi UBound(b) serait-il inférieur à 0 et que va-t-il se passer dans ce cas ?
J'ignore qui a conçu ce code mais cette ligne c'est du haut-vol ! Elle repose sur 2 subtilités qu'il fallait vraiment penser à amalgamer.
Pour comprendre si tu écris dans une feuille une formule genre : =A1+(A1<0) : si A1 est inférieur à 0, +(A1<0) va devenir +1 ; une telle expression booléenne dans Excel (qui renvoie toute seule VRAI ou FAUX) lorsqu'elle est incorporée à un calcul renvoie donc 1 ou 0.
Mais en VBA (UBound(b)<0) va renvoyer 0 si elle est fausse mais -1 si elle est vraie. D'où le fait qu'elle est retranchée et non additionnée, ce qui fera en fait ajouter 1 à UBound(b) si sa valeur est inférieure à 0.
Mais un tableau a par défaut un indice minimum de 0 donc UBound(b) devrait être au minimum égal à 0..., sauf si b n'a pas été initialisé (c'est à dire si pour une raison quelconque on n'a pas d'élément de dico, dans ce cas le tableau ne contient aucun élément).
Et dans ce cas UBound renvoie la valeur -1 !!
Donc si on n'a rien à afficher à la fin, le dimensionnement de v se traduira par : v(0, 1) [une ligne deux colonnes], le tableau sera défini, rien ne lui sera affecté, mais la procédure se déroulera jusqu'au bout sans erreur.
Après ça pause café !
Mais j'espère que tu vois bien toute la subtilité de la manoeuvre sur cette seule ligne de code. Ne t'étonne pas de n'avoir jamais vu ça avant. On ne peut pas dire que ce soit du code courant...
Re,
D'accord... je ne vois pas comment j'aurais pu comprendre ça tout seul!
Et si c'est possible de savoir pourquoi je ne peux pas choisir la colonne du tableau des items que je veux afficher, ça serait super! Si la clé et les items sont en mémoire vive je ne vois pas pourquoi je ne peux pas!?
Merci encore pour votre aide!!
For i = 0 To UBound(b) 'on calcul la moyenne ici? Mais c'est pas clair du tout!
v(i, 0) = b(i)
v(i, 1) = s(i)(0) / s(i)(1)
Next2e volet à examiner l'affectation à v des valeurs de b et s. Pas de difficulté particulière au moins pour l'affectation des valeurs "clés" (la partie que tu veux supprimer).
Pour la 2e affectation, on fait en même temps le calcul de la moyenne. Le fait que 0 et 1 soient dans des parenthèses séparées résulte du fait que s n'a pas fait l'objet d'un dimensionnement spécifique : c'est un tableau des items du dictionnaire, mais chacun étant en fait un tableau, cela revient à avoir affecté à chaque élément (de type Variant) du tableau s (tableau déclaré), un tableau non déclaré. Chaque s(i), en tant que variable de type Variant accueille un tableau. VBA ne s'y retrouverait pas si on écrivait s(i, 0) et s(i, 1).
Il faut aussi noter que si UBound(b) est inférieur à 0, il n'y aura aucune exécution du code à l'intérieur de la boucle, et en sortie de boucle (dans laquelle on n'est pas rentré !!) i sera égal à 0.
Alors que si la boucle s'est déroulée, i sera égal à UBound(b)+1
Je le note car ce i est réutilisé dans la suite.
Set plage = Worksheets("feuil1").Range("A1").End(xlToRight)(2, 2)
plage.Resize(i - (i = 0), 2).Value = vCes deux dernières lignes affectent le tableau v constitué dans la boucle à la feuille.
On définit plage par le premier couple de cellules immédiatement à droite du tableau existant en ligne 1.
On redimensionne cette plage de deux cellules sur i en nombre de lignes (souvenons que les éléments du tableau v sont d'indice 0 à UBound(b) et que i a pris la valeur UBound(b)+1, ce qui permet de redimensionner exactement sur le nombre d'éléments du tableau (encore une subtilité...!!!)
le i - (i = 0) repose sur la même astuce que celle vue précédemment.
On a vu que s'il n'y avait rien à affecter, v serait un tableau de dimension v(0, 1) dont les 2 éléments seront vides.
Que dans ce cas i sera égal à 0 en sortie de boucle (ci-dessus).
Donc i-(i=0) sera égal à 0-(True) [True devenant -1], sera donc 0+1 = 1, ce qui donnera : Resize(1, 2) et qui ne changera pas la dimension de plage occupant 2 cellules, et correspondra aussi à la dimension de v.
Fin du 2er épisode (reste à voir ta modification du code pour ne conserver que les moyennes)
En avant première, tu as la méthode simple :
Tu mets une apostrophe devant cette ligne (dans la boucle vue) :
v(i, 0) = b(i)pour l'invalider, et les clés ne seront pas affectées.
Inconvénient : tu auras toujours deux colonnes à l'arrivée mais la première sera vide.
Pour améliorer, tu peux modifier aussi la suivante :
v(i, 1) = s(i)(0) / s(i)(1)en :
v(i, 0) = s(i)(0) / s(i)(1)Tu transfères ainsi le contenu de la 2e colonne dans la première, la seconde restant vide.
Ce qui pourrait convenir et te permettrait de conserver ton code en l'état.
[Car je persiste à penser que tu auras des moyennes dont tu ne sauras plus à quoi elles peuvent correspondre...)
La modification pour ne traiter qu'une colonne exige de revoir le code à partir du dimensionnement de v.
Super merci!
plage.Resize(i - (i = 0), 2).Value = vpar
plage.Resize(i - (i = 0), 2).Value = v(i, 2)mais ça ne marche pas
Sub test()
Dim dat1 As Range, dat2 As Range, plage As Range
Dim i&, b(), s(), v(), u()
Dim ip As Object
With Worksheets("feuil1")
Set ip = CreateObject("scripting.dictionary")
Set dat1 = Worksheets("feuil1").Range(.Range("B1"), .Range("B14").End(xlDown))
Set dat2 = Worksheets("feuil1").Range(.Range("L1"), .Range("L14").End(xlDown))
b = dat1.Value
s = dat2.Value
For i = 2 To UBound(b)
If Not IsEmpty(b(i, 1)) And Not IsEmpty(s(i, 1)) And IsNumeric(s(i, 1)) Then
If ip.Exists(b(i, 1)) Then
v = ip(b(i, 1))
u = ip(b(i, 1))
v(1) = v(1) + s(i, 1)
u(1) = u(1) + 1
ip(b(i, 1)) = v
Else
ip.Add b(i, 1), Array(s(i, 1), 1)
End If
End If
Next
b = ip.Keys
s = ip.Items
ReDim v(UBound(b) - (UBound(b) < 0), 1)
ReDim u(UBound(s) - (UBound(s) < 0), 1)
For i = 1 To UBound(b)
v(i) = b(i)
u(i) = s(i)(0) / s(i)(1)
Next
Set plage = Worksheets("feuil1").Range("o2:o20")
plage.Resize(i - (i = 0), 1).Value = u(i)
End With
End SubD'accord, je relis les explications et je vais essayer...
Bonjour,
Pas le temps de regarder maintenant (réveil tardif de ma part...!
Je te ferai une modification pour ne fournir qu'une colonne, à titre didactique, dès que je trouve un moment.
A+