Transformer formule SOMMEPROD en VBA
Résolu
belba.yahya
Messages postés
110
Date d'inscription
Statut
Membre
Dernière intervention
-
belba.yahya Messages postés 110 Date d'inscription Statut Membre Dernière intervention -
belba.yahya Messages postés 110 Date d'inscription Statut Membre Dernière intervention -
Bonjour
peux quelqu'un m'aider pour effectué une fonction SOMMEPROD en vba pour un tableau de statistique mensuel via une date horizontal de ("D3:AH3") et des nationalité vertical de("C4:C39"), j'avais fais cette formule suivante:=SOMMEPROD((Tableau3[Nat]=Feuil5!$C4)*($D$3>=Tableau3[Arrivée])).
mais je veux faire ça en vba pour que mon fichier excel soit bien professionnelle.
sachant que les données sont dans la feuil("Fiche_Client") et le transfert soit dans la feuil("Arrivée").
merci en avance ce fichier pour mon travail d’hôtel.
ci joint mon fichier de test.
https://www.cjoint.com/c/IFisRyLN8gK
peux quelqu'un m'aider pour effectué une fonction SOMMEPROD en vba pour un tableau de statistique mensuel via une date horizontal de ("D3:AH3") et des nationalité vertical de("C4:C39"), j'avais fais cette formule suivante:=SOMMEPROD((Tableau3[Nat]=Feuil5!$C4)*($D$3>=Tableau3[Arrivée])).
mais je veux faire ça en vba pour que mon fichier excel soit bien professionnelle.
sachant que les données sont dans la feuil("Fiche_Client") et le transfert soit dans la feuil("Arrivée").
merci en avance ce fichier pour mon travail d’hôtel.
ci joint mon fichier de test.
https://www.cjoint.com/c/IFisRyLN8gK
A voir également:
- Transformer formule SOMMEPROD en VBA
- Formule si et - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Excel mise en forme conditionnelle formule - Guide
- Formule mathématique - Télécharger - Études & Formations
- Formule somme excel colonne - Guide
12 réponses
Toutes mes excuses, je n'avais pas vu qu'il pouvait y avoir plusieurs fois le même nom. Voici le correctif
Cdlt
Sub countbetween_twodates() ' ceci à rectifier
Application.ScreenUpdating = False
Sheets("Feuil3").Range("D5:AH39").FormulaR1C1 = "=SUMPRODUCT((Feuil1!R2C2:R10000C2=RC3)*(R4C>=Feuil1!R2C3:R10000C3)*(R4C<Feuil1!R2C4:R10000C4))"
Sheets("Feuil3").Range("D5:AH39").Value = Sheets("Feuil3").Range("D5:AH39").Value
Application.ScreenUpdating = True
End Sub
Cdlt
Bonjour,
Pourquoi parlez-vous de SOMMEPROD alors que vous utilisez NB.SI.ENS qui va très bien d'ailleurs?
Cdlt
Pourquoi parlez-vous de SOMMEPROD alors que vous utilisez NB.SI.ENS qui va très bien d'ailleurs?
Range("D4:AH42").FormulaR1C1 = "=COUNTIFS(Fiche_Client!C11,R3C,Fiche_Client!C9,RC3)"
Cdlt
Bonsoir.
Je ne connais pas bien VBA, et je ne peux pas t'aider pour ta macro. Cependant j'en sais suffisamment pour t'assurer que tu ne retirerais aucun avantage à remplacer tes formules classiques pour du VBA, et que tu resterais le seul à penser que ton fichier serait devenu "bien professionnel", ce qui est inexact.
Par contre tu peux optimiser ta formule
=NB.SI.ENS(Tableau3[Arrivée];Arrivées!D$3;Tableau3[Nat];Arrivées!$C4)
en l'écrivant
=NB.SI.ENS(Tableau3[Arrivée];D$3;Tableau3[Nat];$C4)
ou même
=NB.SI.ENS(T[Arrivée];D$3;T[Nat];$C4)
si tu remplaces le nom Tableau3 par T,
vu qu'il y a quand même 1209 fois cette formule dans chacune de tes 3 dernières feuilles !
Tu pourrais en profiter pour supprimer les noms inutiles de cette liste : chambres, Clients, Codes, compta, Crit1, Crit2, Crit3, Extraire, invoices, names, Noms, prix, taxe et tpt
Je ne connais pas bien VBA, et je ne peux pas t'aider pour ta macro. Cependant j'en sais suffisamment pour t'assurer que tu ne retirerais aucun avantage à remplacer tes formules classiques pour du VBA, et que tu resterais le seul à penser que ton fichier serait devenu "bien professionnel", ce qui est inexact.
Par contre tu peux optimiser ta formule
=NB.SI.ENS(Tableau3[Arrivée];Arrivées!D$3;Tableau3[Nat];Arrivées!$C4)
en l'écrivant
=NB.SI.ENS(Tableau3[Arrivée];D$3;Tableau3[Nat];$C4)
ou même
=NB.SI.ENS(T[Arrivée];D$3;T[Nat];$C4)
si tu remplaces le nom Tableau3 par T,
vu qu'il y a quand même 1209 fois cette formule dans chacune de tes 3 dernières feuilles !
Tu pourrais en profiter pour supprimer les noms inutiles de cette liste : chambres, Clients, Codes, compta, Crit1, Crit2, Crit3, Extraire, invoices, names, Noms, prix, taxe et tpt
RE,
votre formule traduite en VBA
ou avec la fonction équivalente VBA
Cdlt
votre formule traduite en VBA
Range("D4:AH42").FormulaR1C1 = "=COUNTIFS(Tableau3[Arrivée],Arrivées!R3C,Tableau3[Nat],Arrivées!RC3)"
ou avec la fonction équivalente VBA
Sub Formule()
Application.ScreenUpdating = False
Set f1 = Sheets("Fiche_Client")
Set f2 = Sheets("Arrivées")
For L = 4 To 42
For c = 4 To 34
f2.Cells(L, c) = Application.WorksheetFunction.CountIfs(f1.Range("K6:K10000"), f2.Cells(3, c), f1.Range("I6:I10000"), f2.Cells(L, 3))
Next c
Next L
Set f1 = Nothing
Set f2 = Nothing
End Sub
Cdlt
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonjour Frenchie
vous vous souvenez,vous m'avez aider de transformé une formule en vba avec succés
est ce possible de me donner un idé pour cela d'une formule pour faire countifs entre deux dates
j'ai crée ce code mais ne m'a rien donné:
Application.ScreenUpdating = False
Set f1 = Sheets("Client")
Set F3 = Sheets("NUITEE")
For L = 5 To 39
For c = 4 To 34
F3.Cells(L, c) = Application.WorksheetFunction.CountIfs(f1.Range("D2:D10000"), "<" & F3.Cells(4, c) - f1.Range("C2:C10000"), "=" & F3.Cells(4, c), f1.Range("B2:B10000"), F3.Cells(L, 3))
Next c
Next L
Set f1 = Nothing
Set F3 = Nothing
vous vous souvenez,vous m'avez aider de transformé une formule en vba avec succés
est ce possible de me donner un idé pour cela d'une formule pour faire countifs entre deux dates
j'ai crée ce code mais ne m'a rien donné:
Application.ScreenUpdating = False
Set f1 = Sheets("Client")
Set F3 = Sheets("NUITEE")
For L = 5 To 39
For c = 4 To 34
F3.Cells(L, c) = Application.WorksheetFunction.CountIfs(f1.Range("D2:D10000"), "<" & F3.Cells(4, c) - f1.Range("C2:C10000"), "=" & F3.Cells(4, c), f1.Range("B2:B10000"), F3.Cells(L, 3))
Next c
Next L
Set f1 = Nothing
Set F3 = Nothing
Bonjour,
Avez-vous créer la formule dans la feuille excel (dans la première cellule et première colonne du tableau)?
Si elle fonctionne bien, allez dans la barre de formule, balayez-là avec la souris puis faire CTRL+ C (copier)
Allez dans la feuille VBA et dans les boucles For next, remplacez
par
Ce qui vous copiera la formule identique à celle écrite dans excel.
Cdlt
Avez-vous créer la formule dans la feuille excel (dans la première cellule et première colonne du tableau)?
Si elle fonctionne bien, allez dans la barre de formule, balayez-là avec la souris puis faire CTRL+ C (copier)
Allez dans la feuille VBA et dans les boucles For next, remplacez
F3.Cells(L, c) = Application.WorksheetFunction.CountIfs(f1.Range("D2:D10000"), "<" & F3.Cells(4, c) - f1.Range("C2:C10000"), "=" & F3.Cells(4, c), f1.Range("B2:B10000"), F3.Cells(L, 3))
par
F3.Cells(L, c).FormulaLocal=suivi d'un CTRL + V
Ce qui vous copiera la formule identique à celle écrite dans excel.
Cdlt
je vous remerie carrément Mr Frenchie pour la réponse favorable,ensuite
j'ai essayé de faire le code comme il est mais je ne sais pas pourquoi n'a pas fonctionné.
je vous joint mon fichier pour regarder si il y a une possibilité de l'activer.
https://www.cjoint.com/c/JAtnj20OZDD
j'ai essayé de faire le code comme il est mais je ne sais pas pourquoi n'a pas fonctionné.
je vous joint mon fichier pour regarder si il y a une possibilité de l'activer.
https://www.cjoint.com/c/JAtnj20OZDD
c'est la formule exact que j'ai crée:
SOMMEPROD((Feuil1!B2:B10000=Feuil3!$C10)*(Feuil3!D$4>=Feuil1!C2:C10000)*(Feuil3!D$4<Feuil1!D2:D10000))
je veux la convertir en VBA si possible
SOMMEPROD((Feuil1!B2:B10000=Feuil3!$C10)*(Feuil3!D$4>=Feuil1!C2:C10000)*(Feuil3!D$4<Feuil1!D2:D10000))
je veux la convertir en VBA si possible
Bonjour,
Vous n'avez pas fait ce que je vous ai indiqué.
Il faut récupérer la formule créée sous excel et l'ajouter à la suite de "F3.Cells(L, c).FormulaLocal="
Ce qui donne:
F3.Cells(L, c).FormulaLocal=SOMMEPROD((Feuil1!B2:B10000=Feuil3!$C10)*(Feuil3!D$4>=Feuil1!C2:C10000)*(Feuil3!D$4<Feuil1!D2:D10000))
Cdlt
Vous n'avez pas fait ce que je vous ai indiqué.
Il faut récupérer la formule créée sous excel et l'ajouter à la suite de "F3.Cells(L, c).FormulaLocal="
Ce qui donne:
F3.Cells(L, c).FormulaLocal=SOMMEPROD((Feuil1!B2:B10000=Feuil3!$C10)*(Feuil3!D$4>=Feuil1!C2:C10000)*(Feuil3!D$4<Feuil1!D2:D10000))
Cdlt
Voici le fichier
https://mon-partage.fr/f/c9JpoG5m/
le code pour la macro "countbetween_twodates"
Cdlt
https://mon-partage.fr/f/c9JpoG5m/
le code pour la macro "countbetween_twodates"
Sub countbetween_twodates() ' ceci à rectifier
Application.ScreenUpdating = False
Sheets("Feuil3").Range("D5:AH39").FormulaR1C1 = "=IFERROR(IF(AND(INDIRECT(""Feuil1!$C""&MATCH(RC3,Feuil1!C2,0))<=R4C,INDIRECT(""Feuil1!$D""&MATCH(RC3,Feuil1!C2,0))>=R4C),1,0),"""")"
Sheets("Feuil3").Range("D5:AH39").Value = Sheets("Feuil3").Range("D5:AH39").Value
Application.ScreenUpdating = True
End Sub
Cdlt