Discussion :

[AimeDija]

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

[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/

[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

[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.



En VBA, tu auras

[jerome.vaussenat]

re,

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/

[AimeDija]

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.



En VBA, tu auras

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.

[Pierre Fauconnier]

[...]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...

[AimeDija]

Bien entendu

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[Pierre Fauconnier]

Tu as un " de trop après sumifs(

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"sumifs(Tableau1

[AimeDija]

Oki Doki, question complémentaire ; comment puis je lancer ma fonction?

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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...

[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.

[boisgontierjacques]

Bonjour,

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

Boisgontier

[AimeDija]

Bonjour PJ,

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

Bien à toi,

[Pierre Fauconnier]

[...]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

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!

[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,