VBA Excel Calculer la somme cumulée pour des noms distincts

Version imprimable

05/07/2018, 10h47
AimeDija

1 pièce(s) jointe(s)

VBA Excel Calculer la somme cumulée pour des noms distincts

Bonjour,

Depuis plusieurs jours j'essaye sans succès de trouver le code VBA approprié pour l'opération suivante, pourriez-vous m'aider?

Dans le ficher joint (Feuil1), j'ai le coût par opération (en colonne H) pour des personnes nommés ; Pierre / Paul/ Luc/ Jean/ Andre (colonne F).

Ne connaissant pas à l'avance le nombre de lignes que le rapport comporte , ni le nombre d'occurrence des personnes, je souhaiterais calculer pour chacune d'elles le coût total par période de temps (Feuil2). je cherche ici à automatiser les opérations, ayant déjà essayer de le faire manuellement.

Résultats souhaiter :

Name Month Total cost
Pierre
Paul
Luc
Andre

Pourriez-vous me suggérer un code sachant que je peux avoir plus de 1000 lignes par rapport ? Merci d'avance
05/07/2018, 11h06
jerome.vaussenat
Salut,

De mon point de vue, pas besoin de code pour faire ce que tu veux.
Tu peux utiliser au choix :
- Somme.si ou sa grande soeur somme.si.ens
- Les TCD
Pour en savoir plus sur les totaux conditionels, vas voir là
https://fauconnier.developpez.com/tu...ion-sommeprod/
Pour en savoir plus sur l'utilisation des TCD, vas voir là
https://silkyroad.developpez.com/excel/tcd/
05/07/2018, 11h34
AimeDija

Bonjour Jérôme,

Merci de ton intérêt, malheureusement pour l'utilisation du NB.Si , je dois obligatoirement avoir des plages nommées tel que H2:H1000. Ne connaissant pas à l'avance le nombre de lignes, l'automatisation de ce calcul par VBA m'aiderais vraiment à pouvoir déléguer certaines taches et ainsi optimiser le reporting.

Est ce que quelqu'un aurais une idée? Merci d'avance
05/07/2018, 11h59
Pierre Fauconnier

2 pièce(s) jointe(s)

Salut.

Sans VBA, on pourrait partir sur un TCD.

Sans VBA, on pourrait utiliser SOMME.SI.ENS qui autorise 127 critères

Si après, on a besoin de VBA, on transpose en VBA ce qu'on a réalisé en Excel (cfr ma signature)

Pour ce qui est du nombre de lignes, travailler avec un tableau structuré permet de ne pas avoir à se préoccuper de la taille du tableau.

Pièce jointe 394133

En VBA, tu auras

Pièce jointe 394147
05/07/2018, 14h03
jerome.vaussenat

re,

Citation:

je dois obligatoirement avoir des plages nommées tel que H2:H1000

pas forcement ...

D'une part, tu peux très bien (même si ce n''est pas optimisé utiliser la colonne entière.

sinon, comme dans illustration de Pierre, tu active la fonction Tableau d'Excel. De cette manière tes fonctions SOMME.SI, NB.SI et leur grandes soeurs .ENS s'adapteront à la taille de ton tableau.

Pour info, cette fonction tableau est très utile aussi en cas de création de TCD ((et pour bien d'autres choses !)
pour en savoir plus vas voir la
https://silkyroad.developpez.com/excel/tableau/
05/07/2018, 16h12
AimeDija

Citation:

Envoyé par Pierre Fauconnier

Salut.

Sans VBA, on pourrait partir sur un TCD.

Sans VBA, on pourrait utiliser SOMME.SI.ENS qui autorise 127 critères

Si après, on a besoin de VBA, on transpose en VBA ce qu'on a réalisé en Excel (cfr ma signature)

Pour ce qui est du nombre de lignes, travailler avec un tableau structuré permet de ne pas avoir à se préoccuper de la taille du tableau.

Pièce jointe 394133

En VBA, tu auras

Pièce jointe 394147

Merci Pierre,

La fonction excel fonctionne parfaitement, merci.

néanmoins, lorsque je test le code VBA, j'ai un msg d'erreur tel que : Erreur de compilation / Attendu fin d'instruction. Serais tu pourquoi je reçois ce msg? merci

Jérôme,

Ton line vers les TCD est vraiment pratique, merci. je cherche maintenant à les développer de manière automatique.
05/07/2018, 16h18
Pierre Fauconnier

Citation:

Envoyé par AimeDija

[...]j'ai un msg d'erreur tel que : Erreur de compilation / Attendu fin d'instruction. Serais tu pourquoi je reçois ce msg?[...]

Ma boule de cristal est en panne. Elle m'aurait aidé à voir le code que tu as utilisé. Je ne sais pas la réparer pour l'instant, mais si tu montres ton code avec un copier-coller, je pourrai me passer d'elle pour essayer de te répondre... ;) ;)

Bien entendu

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
Function getAmountforSellerandPeriod(ByVal Period As String, ByVal Seller As String) As Double
 
Dim Formula As String
 
Seller = """" & Seller & """"
Period = """" & Period & """"
Formula = "sumifs("Tableau1[AmountInTransactionCurrency_E],Tableau1[DocumentDate],[Period],Tableau1[GLAccountName],[seller]))"
Formula = Replace(Formula, "[Period]", Period, , , vbTextCompare)
Formula = Replace(Formula, "[Seller]", Seller, , , vbTextCompare)
 
getAmountforSellerandPeriod = Evaluate(Formula)
 
End Function

Le code bloque au premier Formula.

05/07/2018, 16h33
Pierre Fauconnier

Tu as un " de trop après sumifs(

Code:

"sumifs(Tableau1
05/07/2018, 16h48
AimeDija

Oki Doki, question complémentaire ; comment puis je lancer ma fonction?
05/07/2018, 16h52
Pierre Fauconnier
La fonction renvoie le montant vendu par un vendeur pour une période donnée. elle s'utilise donc a priori dans un code du style
Code:

1 2 3 dim MontantVendu as double montantvendu= getAmountforSellerandPeriod(...)
Pourrais-tu montrer le code que tu utilises?

Question subsidiaire: Pourquoi passes-tu par du VBA? L'utilisation de SOMME.SI.ENS ne te convient pas? Tu dois absolument réaliser un traitement VBA des valeurs calculées? La fonction proposée peut être utilisée dans une feuille Excel, mais je ne vois pas en quoi elle aide grandement par rapport à un SOMME.SI.ENS...
05/07/2018, 17h03
AimeDija

Mon code est exactement le même que celui présenter plus haut.

Pourquoi VBA ? Je cherche à automatiser complétement le processus. L'utilisation de somme.si.ens est vraiment utile dans la mesure où c'est moi qui réalise les opérations, dans le cas où une autre personne souhaite le faire cela serait mieux et plus efficace de manière automatique.
06/07/2018, 07h35
boisgontierjacques

1 pièce(s) jointe(s)

Bonjour,

-Sélectionner N2:O11
=soustotsomme(F2:F1000;H2:H1000)
-Valider avec maj+ctrl+entrée

Boisgontier
06/07/2018, 11h49
AimeDija

Bonjour PJ,

Merci pour ton aide, j'analyse le tout et reviens vers toi dés que possible.

Bien à toi,
06/07/2018, 12h02
Pierre Fauconnier

2 pièce(s) jointe(s)

Citation:

Envoyé par AimeDija

[...]dans le cas où une autre personne souhaite le faire cela serait mieux et plus efficace de manière automatique.

J'ai des doutes que VBA soit plus efficace qu'Excel, surtout sur base d'une fonction matricielle perso qui n'utilise pas les tableaux structurés. C'est... exotique :mrgreen:

Il ne faut normalement utiliser VBA qu'en appui d'Excel. Peux-tu expliquer dans quel cas l'utilisateur aurait à manipuler le code et en quoi cela serait plus simple pour lui d'utiliser ton VBA plutôt qu'une formule?

Voici une illustration de l'utilisation très simple de SOMME.SI.ENS avec deux critères, et un classeur d'exemple. Celui qui arrive à faire plus simple que cela en VBA, je lui paie des chiques!

Pièce jointe 394573
18/07/2018, 13h13
AimeDija

Bonjour Pierre
Bonjour le forum,

Après avoir pris le temps de faire certains tests, je dois en effet admettre qu'une formule Excel dans ce cas précis est tout aussi si pas plus efficace qu'un code VBA. Merci pour ton intervention et ton aide.

Bien à toi,