Calculs selon différents critères Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
L
Leo10Messi
Membre habitué
Membre habitué
Messages : 55
Appréciation reçue : 1
Inscrit le : 9 avril 2019
Version d'Excel : 2016

Message par Leo10Messi » 15 avril 2019, 11:53

Sample calcus.xlsx
(8.53 Kio) Téléchargé 7 fois
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
Avatar du membre
i20100
Passionné d'Excel
Passionné d'Excel
Messages : 4'840
Appréciations reçues : 235
Inscrit le : 16 mars 2017
Version d'Excel : 2010 sur PC

Message par i20100 » 15 avril 2019, 13:25

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 Sub
Une pensée partagée
Lorsque tu omets de marquer la question initiale comme étant résolu, tu prives les participants du forum d’une solution possible pour eux.
Merci! de faire un clic sur le bouton résolu pour nous aider à t'aider.
isabelle
L
Leo10Messi
Membre habitué
Membre habitué
Messages : 55
Appréciation reçue : 1
Inscrit le : 9 avril 2019
Version d'Excel : 2016

Message par Leo10Messi » 15 avril 2019, 14:54

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 :) :)
Avatar du membre
Pedro22
Membre impliqué
Membre impliqué
Messages : 2'404
Appréciations reçues : 244
Inscrit le : 26 janvier 2017
Version d'Excel : 2010

Message par Pedro22 » 15 avril 2019, 14:56

Leo10Messi a écrit :
15 avril 2019, 14:54
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 : 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 = xlCalculationAutomatic
Le problème n'est pas de ne pas savoir, le problème est de ne pas chercher...
Ton problème est résolu ? N'oublie pas de l'indiquer :
Avatar du membre
Jean-Eric
Fanatique d'Excel
Fanatique d'Excel
Messages : 15'124
Appréciations reçues : 494
Inscrit le : 27 août 2012
Version d'Excel : O365 32 bits

Message par Jean-Eric » 15 avril 2019, 15:26

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
(21.32 Kio) Téléchargé 8 fois
1 membre du forum aime ce message.
Jean-Eric

Je ne réponds pas aux M.P. non sollicités.
L
Leo10Messi
Membre habitué
Membre habitué
Messages : 55
Appréciation reçue : 1
Inscrit le : 9 avril 2019
Version d'Excel : 2016

Message par Leo10Messi » 15 avril 2019, 16:15

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.
Avatar du membre
Pedro22
Membre impliqué
Membre impliqué
Messages : 2'404
Appréciations reçues : 244
Inscrit le : 26 janvier 2017
Version d'Excel : 2010

Message par Pedro22 » 16 avril 2019, 08:35

2 remarques :

- Tu n'as besoin que d'une seule instruction finale
Application.Calculation = xlCalculationAutomatic
Celle-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
:btres:
1 membre du forum aime ce message.
Le problème n'est pas de ne pas savoir, le problème est de ne pas chercher...
Ton problème est résolu ? N'oublie pas de l'indiquer :
L
Leo10Messi
Membre habitué
Membre habitué
Messages : 55
Appréciation reçue : 1
Inscrit le : 9 avril 2019
Version d'Excel : 2016

Message par Leo10Messi » 16 avril 2019, 10:01

Bonjour

Merci encore une fois

C'est fait :) :)
1 membre du forum aime ce message.
L
Leo10Messi
Membre habitué
Membre habitué
Messages : 55
Appréciation reçue : 1
Inscrit le : 9 avril 2019
Version d'Excel : 2016

Message par Leo10Messi » 16 avril 2019, 11:12

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
L
Leo10Messi
Membre habitué
Membre habitué
Messages : 55
Appréciation reçue : 1
Inscrit le : 9 avril 2019
Version d'Excel : 2016

Message par Leo10Messi » 16 avril 2019, 14:26

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
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message