VBA - SOMME SI ENS Ref circulaire
Bonjour à tous,
Je possède un fichier sur lequel j'ai peu de droits de modification. Mais j'aimerais mettre en place des formules dans la colonne J, pour que des calculs soit réalisés. Je vous explique comment mon fichier fonctionne:
Chaque ligne du fichier correspond à un compte donné, en colonne B est inscrit le nom du compte et en colonne J, la valeur chiffrée qui lui est associée. Lorsque la cellule de la colonne J est en jaune claire, c'est qu'elle est destinée à accueillir des données que l'utilisateur va rentrer à la main en dur. Lorsque la cellule est verte, c'est qu'elle correspond à un totalisateur. En fait, les lignes de compte ayant une cellule verte en colonne J sont des comptes "parent". C'est à dire que la valeur chiffrée qui va leur être associée en colonne J, va correspondre à la somme de leurs comptes "fils". Comment savoir quels sont les lignes qui sont leur fils? Grâce à la colonne Z qui répertorie le parent de chaque compte.
Pour donner un exemple, le compte de la ligne 6 est "PL101010". Sa valeur associée est 12 et son "parent" est "PL10100T". On retrouve en ligne 11 ce parent justement, et sa cellule J11 est verte. Ici il doit donc exister une formule que j'imagine de type SOMME.SI.ENS, qui permettrait de sommer l'ensemble des valeurs chiffrées de la colonne J, ayant comme "parent" PL10100T".
Afin de ne pas ralentir mon fichier (l'exemple que je vous ai transmis a seulement 57 ligne, mais l'original en a 12000), j'aimerais renseigner à l'aide d'une macro VBA, les résultats de ces formules SOMME.SI.ENS dans les cellules adéquates. J'ai essayé de renseigner l'ensemble des SOMME.SI.ENS, mon fichier était très ralenti.
Dans cette optique là, j'ai donc élaboré une macro qui ne fonctionne qu'à moitié. Il y a deux soucis sur lesquels j'aurais besoin de votre aide:
- Le premier c'est que dans chaque cellule concernée, c'est "#VALEUR" qui m'est retournée. Lorsque je rentre la formule dans la cellule, cela fonctionne correctement pourtant (voir cellule J10). Cela est dû à une référence circulaire (j'en suis sûr à 99%° car je prends effectivement l'ensemble de la colonne J en compte, et du coup la cellule contenant la formule en fait partie puisqu'elle se trouve en colonne J. En revanche, si je renseigne la formule en colonne J, directement avec Excel, ça fonctionne, je n'ai pas ce problème.. Mais mon fichier est très lent. Par ailleurs, cette référence circulaire n'aura en réalité jamais lieu car pour qu'une cellule se somme elle même, il faudrait que le compte de ligne concerné ait comme "parent".. lui même. Ce qui n'arrive pas.
- La seconde chose est que la macro doit tourner pendant un certain temps puisque je lui fait parcourir l'intégralité de lignes (dans mon exemple on a que 57 mais dans mon fichier original 12000 je le rappelle). Et j'aimerais donc l'optimiser du point de vue temps de calcul, mais je n'ai pas d'idée.
Pourriez-vous me donner un coup de main ?
SkillzZ
Bonjour AuSecour,
Un très grand merci pour ta proposition.. Que je ne comprends pas malheureusement en termes de code. Pourrais-tu la commenter en détails ? Elle semble fonctionner mais je m'attendais à quelque chose utilisant des SOMME.SI.ENS. Car j'ai spécifié dans mon texte ci-dessus un critère, celui du parent, mais en réalité il y en a d'autre comme celui du C2 Code qui doit être égal entre la ligne totalisatrice (avec la formule donc) et les lignes "enfants". De plus, la colonne ICP Code, doit contenir le mot "ICP"..
Ce sont des critères supplémentaires que je n'ai pas voulu évoquer pour ne pas alourdir ma demande déjà longue.
Mais le truc est que je ne comprends pas ce que tu as écrit comme je te le disais, je suis vraiment novice en VBA . Par exemple: "New collection", "ReDim", les tableaux, le GoSub somme:, toutes ces choses là, je n'y connais vraiment pas grand chose.
Ta solution a l'air géniale autrement, et semble optimisée contrairement à ce que j'avais proposé. Si j'arrivais à la comprendre entièrement alors je pourrais l'utiliser et ce serait top.
Je te remercie beaucoup pour le temps consacré AuSecour, c'est vraiment cool, d'autant que ce que tu proposes semble être ce que je recherche!
Bonnz journée !
SkillzZ
Bonjour,
Bien sûr, je te passe une version du code commentée, je te laisse copier ça dans ta fenêtre VBA:
Sub test()
Dim ColecLig As New Collection 'on déclare un objet ColecLig qui va être un objet de type collection
'une collection est un objet particulier, l'avantage par rapport à un tableau, c'est qu'il est redimensionnable
'très facilement, il suffit d'utiliser additem ou removeitem pour ajouter ou supprimer un élément,
'en revanche, elle ne contient qu'une colonne de données, l'avantage aussi c'est de pouvoir appeler un élément
'via une clé, ici j'utilise le code de compte comme clé, la collection enregistre la ligne correspondante de ce code de
'compte dans le tableau qui s'appelle tableau. La collection sert donc à faire un lien rapide entre un code et la
'ligne du tableau où se trouve ce code, sans avoir à faire une boucle sur le tableau pour trouver le code
'je fais souvent ça car je pense que ça va plus vite
Dim TabEnfants As Variant, TabSplit As Variant
der_lig = Range("b" & Rows.Count).End(xlUp).Row
tableau = Range("a6", "z" & der_lig)
ReDim TabEnfants(LBound(tableau, 1) To UBound(tableau, 1), 1 To 2) 'codes enfants + somme faite ou non
'redim sert à redimensionner un tableau, ensuite on lui précise les dimensions que l'on va vouloir,
'cette instruction est très pratique quand on ne connaît pas à l'avance la taille du tableau et que l'on veut
'y mettre des variables
'création de la collection de lignes
On Error Resume Next 'permet de passer à la ligne suivante en cas d'erreur, j'ai dû mettre ça car tu avais
'des doublons au niveau de tes codes de compte, mais je ne pouvais pas ajouter une clé identique pour deux valeurs
'si tu as besoin de traiter les doublons, j'ajouterai un numéro de ligne à la clé pour pouvoir avoir des valeurs
'uniques, mais comme je ne comprenais pas pourquoi tu avais des doublons, je me suis abstenu
For i = LBound(tableau, 1) To UBound(tableau, 1)
'lbound permet de renvoyer la borne inférieure de la dimension d'un tableau,
'dit comme ça ça ressemble à du chinois, un tableau a en général deux dimensions,
'les lignes, et les colonnes, ligne = 1, colonnes = 2
'les tableaux ont un numéro d'index, en fonction de comment tu les déclares, le premier élément d'un tableau
'peut se retrouver à l'index 1 voir 0, j'utilise lbound pour ne pas me prendre la tête
'lbound(tableau,1) = borne inférieure de la première dimension du tableau qui s'appelle "tableau"
ColecLig.Add i, tableau(i, 2) 'ajoute à la collection "ColecLig" la valeur i, avec la clé tableau(i,2) (ligne i, colonne 2)
Next i
'Enregistrement des enfants de chaque compte
For i = LBound(tableau, 1) To UBound(tableau, 1)
'ColecLig(tableau(i, 26)) peut se traduire par "ligne où se trouve ce code dans le tableau enfant"
'iif = un SI, tu fais un test, puis tu mets valeur si vrai, et valeur si faux
'ici ça sert à ajouter une virgule ou non pour séparer les codes enfant si il y a en a déjà un de stocké,
'comme ça: "enfant1,enfant2"
TabEnfants(ColecLig(tableau(i, 26)), 1) = TabEnfants(ColecLig(tableau(i, 26)), 1) & _
IIf(TabEnfants(ColecLig(tableau(i, 26)), 1) <> "", ",", "") & tableau(i, 2)
Next i
On Error GoTo 0 'permet de remettre le paramètre de base en cas d'erreur, c'est à dire, afficher un message d'erreur
'et faire planter le code
'Calcul des sommes
'pour toutes les lignes du tableau
For i = LBound(tableau, 1) To UBound(tableau, 1)
'je mets les enfants du code dans une variable pour pouvoir l'utiliser dans le sous programme "somme:"
'j'ai dû faire ça car dans le sous programme j'utilise aussi les enfants, mais d'un enfant,
'c'est l'enfant de l'enfant qui peut aussi avoir des enfants qui ont des enfants...
'sauf que dans un cas j'utilise la variable i pour avoir la ligne, et dans l'autre, h, je devais avoir
'une variable pour stocker l'une des deux valeurs, puis ensuite me permettre d'avoir exactement le même code
'sinon j'aurais dû avoir deux fois le même code avec juste une variable qui change...
enfants = TabEnfants(i, 1)
If enfants <> "" Then 'je teste si il a des enfants avant de vouloir faire la somme de ses enfants
tableau(i, 10) = 0 'je réinitialise la valeur du total
GoSub somme 'je file dans le sous programme
TabEnfants(i, 2) = "fait" 'une fois la somme de ses enfants faite, je passe le statut de cette somme
'en fait, pour ne pas recalculer cette somme si je retombe sur ce code de compte
End If
Next i
'ici c'est la dernière ligne lue, elle permet d'exporter le tableau où on a tout modifié dans le fichier
Range("a6", "z" & der_lig) = tableau
Exit Sub 'permet de terminer le programme
somme: 'c'est la façon dont on écrit une ligne accessible via goto ou gosub, la différence c'est que quand on appelle
'cette ligne avec un gosub, on a un return plus tard pour revenir à la ligne qui a appelé le sous programme
'enregistrement des codes comptes des enfants dans un tableau
TabSplit = Split(enfants, ",") 'split est une fonction qui retourne un tableau,
'ce tableau est le résultat de la séparation de plusieurs valeurs via un délimiteur,
'ici on stockait les différents codes enfants séparés par une virgule, du coup j'utilise "," pour séparer
'les valeurs
For h = LBound(TabSplit, 1) To UBound(TabSplit, 1) 'on boucle sur tous les enfants du code
If TabEnfants(ColecLig(TabSplit(h)), 2) = "" Then 'si la somme des enfants de ce code n'a pas été faite
enfants = TabEnfants(ColecLig(TabSplit(h)), 1) 'on enregistre dans la variable enfant les enfants...
If enfants <> "" Then 'on regarde si le code compte a des enfants
GoSub somme 'si c'est le cas on appelle à nouveau le sous programme somme
End If
End If
tableau(i, 10) = tableau(i, 10) + tableau(ColecLig(TabSplit(h)), 10) 'c'est ici qu'on ajoute progressivement
'les valeurs pour faire la somme
Next h
Return 'on revient à la ligne qui a appelé le sous programme
End SubSi tu as toujours du mal avec le code, tu peux toujours regarder en mode pas à pas, avec des espions etc le programme, pour voir comment il fonctionne, c'est comme ça que j'arriver à voir si il fonctionne comme prévu ou non.
Mais le truc est que je ne comprends pas ce que tu as écrit comme je te le disais, je suis vraiment novice en VBA . Par exemple: "New collection", "ReDim", les tableaux, le GoSub somme:, toutes ces choses là, je n'y connais vraiment pas grand chose.
Ta solution a l'air géniale autrement, et semble optimisée contrairement à ce que j'avais proposé. Si j'arrivais à la comprendre entièrement alors je pourrais l'utiliser et ce serait top.
Tu as des sections de cours sur le site (et il en existe des milliers d'autres !) :
Rebonjour,
Merci beaucoup pour les précisions je vais étudier ça ! Je ne connais pas les espions, seulement l'exécution pas à pas. Mais je vais me renseigner!
Merci beaucoup pour l'effort fourni, c'est vraiment top !
SkillzZ