Traduction de formule en vba

Y compris Power BI, Power Query et toute autre question en lien avec Excel
A
Abracadabra
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 28 février 2016
Version d'Excel : 2007

Message par Abracadabra » 13 mars 2016, 13:46

Bonjour,

Pourriez vous m'aider a mettre cette formule en vba

=SOMMEPROD((Feuil1!$F$2:$F$100000='VISITE '!$A31 )*(Feuil1!$D$2:$D$100000='VISITE '!$B$27)*(Feuil1!$G$2:$G$100000='VISITE '!$B$28)*(Feuil1!$E$2:$E$100000='VISITE '!$A$27);Feuil1!$M$2:$M$100000)

merci
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 448
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 13 mars 2016, 14:05

Bonjour,

Une formule est une formule. C'est une expression interprétable par Excel pour renvoyer un résultat. Pour VBA, ce n'est que du texte !
On lui indique cependant la façon de le faire reconnaître par Excel comme formule au moyen de propriétés de l'objet Range :
.Formula : l'expression est une formule écrite en anglais et en style A1
.FormulaR1C1 : l'expression est une formule écrite en anglais et en style L1C1
.FormulaLocal : l'expression est une formule écrite dans la langue correspondant aux paramètres régionaux du système et en style A1
.FormulaR1C1Local : l'expression est une formule écrite dans la langue correspondant aux paramètres régionaux du système et en styleL1C1

.FormulaArray l'expression est une formule matricielle (à valider matriciellement)
Mais petite carence de VBA pour ce dernier type d'expression représentant une formule : il ne sait la passer en Excel qu'en anglais et en style L1C1.

Cordialement.
A
Abracadabra
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 28 février 2016
Version d'Excel : 2007

Message par Abracadabra » 13 mars 2016, 14:18

Merci pour l'info, c'est noté,

alors je vais reposé ma question différemment, je veux obtenir la somme de m2:m20000 selon plusieurs critère, j essaie de formuler le tout avec SUMIF et Countif mais je ny arrive pas, pourriez vous svp m'aider.

Exemple:

si A1:A20000 est = a 2016
Et si B1 a B20000 est = a Paul
Afficher le resultat de c1 a c20000

Mon problème est que j'ai tellement de formule comme celle-ci que mon fichier est devenu vraiment très lent

Merci de votre aide
Avatar du membre
zarik
Membre fidèle
Membre fidèle
Messages : 268
Appréciation reçue : 1
Inscrit le : 14 mars 2014
Version d'Excel : 2007 FR

Message par zarik » 13 mars 2016, 14:38

si A1:A20000 est = a 2016
Et si B1 a B20000 est = a Paul
Afficher le resultat de c1 a c20000
un truc comme ca?
Cumul2016=0 'cumul depart
CumulPaul=0

for i =1 to 20000 'represente nombre de lignes
If Range("A"&i)="2016"then 'si A1:A20000 est = a 2016
Cumul2016=Cumul2016+1 'augmente cumul
'le resultat de c1 a c20000
range("C"&i)="2016: "&Cumul2016 'resultat de cumul a chaque ligne (2016 rappel du cumul en question)
end if
If Range("B"&i)="Paul"then 'si B1 a B20000 est = a Paul
CumulPaul=CumulPaul+1
range("C"&i)="Paul: "&CumulPaul 'le resultat de c1 a c20000'
end if
next i
Mieux vaut allumer une bougie que maudire les ténèbres.
A
Abracadabra
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 28 février 2016
Version d'Excel : 2007

Message par Abracadabra » 13 mars 2016, 15:16

Merci c'est très gentil mais je ni arrive pas

voici le lien exemple du fichier en feuil2 b6 vous avez l'exemple de ce que je fait http://www.cjoint.com/c/FCnony3iNgt

merci de m'aider

:D
Avatar du membre
zarik
Membre fidèle
Membre fidèle
Messages : 268
Appréciation reçue : 1
Inscrit le : 14 mars 2014
Version d'Excel : 2007 FR

Message par zarik » 13 mars 2016, 15:32

je ni arrive pas
normal tu jongle entre plusieurs feuilles:
If [surligner]sheets("feuil1").[/surligner] Range("A"&i)="2016"then 'si A1:A20000 est = a 2016
Cumul2016=Cumul2016+1 'augmente cumul
'le resultat de c1 a c20000
[surligner]Sheets("feuil2")[/surligner].range("C"&i)="2016: "&Cumul2016 'resultat de cumul a chaque ligne (2016 rappel du cumul en question)
end if
preciser les feuilles fonctionnera mieux

de+, c'est une formule que je n'ai jamais utiliser...j'peux pas t'aider + que ca dsl
Mieux vaut allumer une bougie que maudire les ténèbres.
A
Amadéus
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'620
Appréciations reçues : 79
Inscrit le : 7 mai 2006
Version d'Excel : Office Excel 2003 FR et 2013FR

Message par Amadéus » 13 mars 2016, 17:03

Bonjour
Au vu de ton fichier, ta formule est largement erronée.
Il serait étonnant que tu aies 100000 lignes de données.
Ta formule SOMMEPROD calculant sur 100 000 lignes prendra un temps considérable.
Pour limiter le champ d'action des formules aux seules lignes remplies, tu peux, comme sur ce fichier utiliser des Champs dynamiques (adaptables automatiquement au nombre de lignes)
Pour cela:
La formule:
=DECALER(VISITE!$D$2;;;NBVAL(VISITE!$D:$D)-1)
est Nommée Année
elle représente le champ de la colonne D de la feuille VISITE
Suite de quoi, la formule
=DECALER(VISITE!$G$2;;;NBVAL(Année))
est nommée Motif et représente les données de la colonne G
La formule
=DECALER(VISITE!$F$2;;;NBVAL(Année))
est nommée Vendeur
La formule
=DECALER(VISITE!$M$2;;;NBVAL(Année))
est nommée Visite
et la formule
=DECALER(VISITE!$N$2;;;NBVAL(Année))
est nommée Vente
Ta formule en B6 de Feuil2 devient
=SOMMEPROD((Vendeur=$A6 )*(Année=$B$2)*(Motif=$B$3);Visite)
Les autres formules utilisent les mêmes noms définis
A partir de là, si tu préfères en VBA, il faudra, compte tenu de la présentation de la Feuil2, rédiger une formule pour chaque colonne.
A titre indicatif, la variable pour la fonction SOMMEPROD en VBA et du type = Evaluate("SUMPRODUCT((...
Cordialement
Classeurtest.xls
(61.5 Kio) Téléchargé 20 fois
Amadéus vous informe que, pour cause de saturation, il ne peut plus répondre aux messages privés non sollicités.
A
Abracadabra
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 28 février 2016
Version d'Excel : 2007

Message par Abracadabra » 13 mars 2016, 17:11

Genial, Merci c'est parfais,

autre question j'ai un graphique qui sort les stats des ventes par semaine, j'aimerais avoir un autre graphique qui édite le cumul de la semaine du premier graphique + les 2 semaines précédentes

une idée

ps le graphique est alimenter par un tableau dont je vais chercher les donnée comme ta formule

merci encore Amadéus
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 448
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 13 mars 2016, 17:19

Bonjour,

Pour faire le calcul et l'affecter à ton tableau de vendeur.
J'espère que ton modèle est exactement conforme car les 4 boucles imbriquées pour réduire au mieux la longueur de la macro sont adaptées à la conformation de ton tableau...
J'ai utilisé SOMME.SI.ENS (SumIfs, pas réussi à faire fonctionner SumProduct...)
Sub Calculer()
    Dim pl As Range, nv$, sp$, a%, i%, d%, j%, ja%
    Set pl = Worksheets("Feuil1").Range("A2:A100000")
    With Worksheets("Feuil2")
        For ja = 2 To 14 Step 6
            a = .Cells(2, ja).Value
            For j = 0 To 4 Step 2
                sp = .Cells(3, ja + j).Value
                For d = 0 To 1
                    For i = 6 To 20
                        nv = .Cells(i, 1).Value
                        .Cells(i, ja + j + d).Value = Application.SumIfs(pl.Offset(, 12 + d), _
                         pl.Offset(, 5), nv, pl.Offset(, 3), a, pl.Offset(, 6), sp)
                    Next i
                Next d
            Next j
        Next ja
    End With
End Sub
Cordialement.
A
Abracadabra
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 28 février 2016
Version d'Excel : 2007

Message par Abracadabra » 13 mars 2016, 17:46

Merci MFerrand,

j'essaie ton code
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message