Calculs selon différents critères
Bonjour
J'ai besoin d'aide au sujet d'un traitement de données que j'essaye de faire. J'ai pensé à réaliser un tableau croisé dynamique, cependant je voudrais savoir si une solution VBA est envisageable.
J'ai mis le fichier en pièce-jointe. (c'est un fichier qui sert d'exemple, je souhaiterai appliquer le code VBA à une base de données beaucoup plus importante, sachant que les colonnes sont dans le même ordre)
Je cherche à savoir le nombre de fois qu'une valeur de la colonne A est répétée, son établissement correspondant (chaque valeur a un établissement unique), et la somme des montants en fonction de chaque devise (par exemple, la somme des montant qui sont en EUR pour la valeur B, et la somme des montants en GBP)
Un grand merci d'avance pour votre aide
Cordialement
Bonjour,
à tester,
Sub test()
rw = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & rw).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True
For i = 2 To Cells(Rows.Count, "F").End(xlUp).Row
Cells(i, "G") = Application.CountIf(Range("A:A"), Cells(i, "F"))
Cells(i, "H") = Application.Index(Range("B:B"), Application.Match(Cells(i, "F"), Range("A:A"), 0))
dev = Array("USD", "EUR", "NOK", "GBP")
For j = 0 To 3
t = "=SUMPRODUCT((A2:A" & rw & "=F" & i & ")*(D2:D" & rw & "=""" & dev(j) & """)*(C2:C" & rw & "))"
Cells(i, j + 9) = Evaluate(t)
Next j
Next i
End SubBonjour
un grand Merci pour ton retour rapide
J'ai essayé le code et il marche parfaitement.
Je l'ai adapté pour travailler sur une base de plus de 40 000 lignes, et du coup il met pas mal de temps. Est-ce que ce code est adapté pour travailler sur une base de plus de 100 000 lignes? ne va t-il pas planter à un moment?
Merci encore une fois
Bonjour
un grand Merci pour ton retour rapide
J'ai essayé le code et il marche parfaitement.
Je l'ai adapté pour travailler sur une base de plus de 40 000 lignes, et du coup il met pas mal de temps. Est-ce que ce code est adapté pour travailler sur une base de plus de 100 000 lignes? ne va t-il pas planter à un moment?
Merci encore une fois
Bonjour,
Je t'invite à apporter les mêmes modification que dans ce post : https://forum.excel-pratique.com/viewtopic.php?f=2&t=123189&p=753646#p753646
A savoir :
'Ajouter en début de macro :
Application.ScreenUpdating = False 'Désactive l'affichage
Application.Calculation = xlCalculationManual 'Désactive le recalcul auto des formules à chaque modification
'Rétablir le paramétrage initiale à la fin :
Application.Calculation = xlCalculationAutomaticBonjour,
Une alternative au TCD et à VBA.
Cela nécessite l'installation de Power Query (complément gratuit à télécharger et installer, si possibilté ?).
A tester avec tes vraies données.
Donc copier les données dans T_Données (en lieu et place), puis Ruban, Données et Actualiser tout.
Cdlt.
Bonjour
Merci Pedro22, la macro mets beaucoup moins de temps
Sub test()
Application.ScreenUpdating = False 'Désactive l'affichage
Application.Calculation = xlCalculationManual 'Désactive le recalcul auto des formules à chaque
rw = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & rw).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True
For i = 2 To Cells(Rows.Count, "F").End(xlUp).Row
Cells(i, "G") = Application.CountIf(Range("A:A"), Cells(i, "F"))
Cells(i, "H") = Application.Index(Range("B:B"), Application.Match(Cells(i, "F"), Range("A:A"), 0))
dev = Array("USD", "EUR", "NOK", "GBP", "CHF", "JPY", "CNH", "SEK", "AUD", "CAD", "HKD")
For j = 0 To 10
t = "=SUMPRODUCT((A2:A" & rw & "=F" & i & ")*(D2:D" & rw & "=""" & dev(j) & """)*(C2:C" & rw & "))"
Cells(i, j + 9) = Evaluate(t)
Next j
Application.Calculation = xlCalculationAutomatic
Next i
Application.Calculation = xlCalculationAutomatic
End Sub
Jean-Eric, merci pour la solution que tu m'as apporté, je vais faire des recherches afin de comprendre à quoi correspond le PowerQuery et essayer ta solution.
Merci pour vos retours rapides
Bonne journée.
2 remarques :
- Tu n'as besoin que d'une seule instruction finale
Application.Calculation = xlCalculationAutomaticCelle-ci est donc à supprimer :
...
Next j
Application.Calculation = xlCalculationAutomatic
Next i
...- 2nde remarque : si ton problème est résolu, merci de valider ton sujet
Bonjour
Merci encore une fois
C'est fait
Bonjour
je me permets de vous relancer encore une fois au sujet de cette demande
Quand j'ai appliqué le code à une base de données de 40 000 lignes jai gagné du temps.
Par contre, quand je l'ai appliqué à une base de plus de 350 000 lignes, cela fait 1H10 que la macro tourne, toujours sans résultats.
J'aurai bien utilisé la solution de Jean-Eric, sauf qu'au travail nous n'avons pas le droit de télécharger des logiciels externes (ou compléments).
Quelqu'un aurait une autre alternative SVP ? ou une autre amélioration afin de gagner du temps?
Désolé de vous relancer, et je vous remercie tous d'avance
Cordialement
Re bonjour
J'ai réussi à intégrer Power Query après autorisation
J'ai utilisé la solution de Jean-Eric, elle fonctionne parfaitement
un grand Merci à vous tous
Cordialement
Bonjour,
Une alternative au TCD et à VBA.
Cela nécessite l'installation de Power Query (complément gratuit à télécharger et installer, si possibilté ?).
A tester avec tes vraies données.
Donc copier les données dans T_Données (en lieu et place), puis Ruban, Données et Actualiser tout.
Cdlt.
Sample calcus.xlsx
Un grand Merci pour ta solution
Bonjour,
Une alternative au TCD et à VBA.
Cela nécessite l'installation de Power Query (complément gratuit à télécharger et installer, si possibilté ?).
A tester avec tes vraies données.
Donc copier les données dans T_Données (en lieu et place), puis Ruban, Données et Actualiser tout.
Cdlt.
Sample calcus.xlsx
Bonjour Jean-Eric
Je me permets de te relancer au sujet de cette demande
Pourrais-tu s'il te plaît, me rajouter des commentaires dans le fichier Excel, afin que je comprenne la mise en place de cette solution
J'aimerais comprendre et être capable de refaire le fichier
Je te remercie d'avance
Bonjour,
Okay pour des explications, mais par quoi commencer ?
Sais-tu ouvrir l'éditeur Power Query ?
A te relire.
Cdlt.
Bonjour,
Okay pour des explications, mais par quoi commencer ?
Sais-tu ouvrir l'éditeur Power Query ?
A te relire.
Cdlt.
Merci pour ton retour
J’ai vraiment un niveau débutant, ma première expérience avec PQ était suite à un fichier que 78chris m’avait envoyé avec des explications.
Donc oui peux ouvrir l’éditeur.
Je voudrais savoir comment tu as fait la requête, et la partie calculs aussi s’il te plaît.
Merci