Macro pour faire une somme conditionnelle en recherchant dans toutes les feuilles

[fbeaupre]

Bonjour à tous !

Premièrement, ça fait plusieurs fois que je fais des recherches sur ce forum et je suis très impressionné par la qualité des répondants alors félicitations à tous.

J'ai un fichier Excel qui a présentement 5 onglets mais qui grandira au cours des prochains mois (se rendra possiblement à plus de 10 onglets). J'aimerais faire une macro qui me permettra de faire une somme selon les conditions :

1) Rechercher les montants correspondants à la condition décrite en 2) dans toutes les feuilles (et que ce soit dynamique donc si j'ajoute une feuille, pas besoin de modifier la macro)

2) La condition est que si j'ai une valeur (disons "FB") dans la colonne A, le montant à ajouter à la somme est celui dans la colonne C de la ligne équivalente.

Dans l'exemple fournit, je m'attends à ce que la macro me donner 294 comme valeur.

Merci d'avance pour votre aide.

François

 

[Compte Supprimé 979]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Bonsoir FBeaupre et bienvenue sur ce forum

Voilà une petite fonction qui fera ce que tu demandes

Public Function SomSiCrit(Crit As String)
Dim Sht As Worksheet, Exclues As String
Dim Mt As Single, Total As Single
' Liste des feuilles à exclure du total
Exclues = "Total - Macro, Toto"
' Initialisation du TOTAL
Total = 0
' Pour chaque feuille
For Each Sht In ThisWorkbook.Worksheets
Mt = 0 ' Initialiser le montant à 0
' Si le nom de la feuille n'est pas dans la liste
If InStr(1, Exclues, Sht.Name) = 0 Then
' On trouve le montant selon condition
Mt = Application.Evaluate("SUMIF('" & Sht.Name & "'!A:A" & "," & Chr(34) & Crit & Chr(34) & ",'" & Sht.Name & "'!C:C)")
' On fait le total
Total = Total + Mt
End If
Next Sht
' Retourner la valeur totale pour la fonction
SomSiCrit = Total
End Function

Il suffit d'appeler la fonction en saisissant dans une cellule

=somsicrit("FB")

Voilà A+

 

[Gorfael]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Salut fbeaupre et le forum

Sub test()
'Déclarations ----------------------
Dim Cel As Range, F As Worksheet
'MEI -------------------------------
Sheets("Total - Macro").Activate
'Traitement ------------------------
For Each F In ThisWorkbook.Sheets
    If F.Name <> ActiveSheet.Name Then
        For Each Cel In F.Range(F.[A3], F.Cells(Rows.Count, "A"))
            If Cel = "FB" Then [B5] = [B5] + Cel.Offset(0, 2)
        Next Cel
    End If
Next F
End Sub

Un exemple. on parcourt les feuilles, une à une
dans chaque feuille, on lit la cellule A et si elle correspond à FB, on additionne, en B5, la valeur de la cellule située à 2 colonnes à droite.

Petites remarques :
- Ce n'est qu'un exemple pour comprendre => on pourrait faire dans chaque feuille une recherche de FB (avec Find), ce qui serait un peu moins lent. Ou mettre un filtre Automatique et comptabiliser les cellules visibles.
- Mettre "Total pour FB:" ne me semble pas une bonne idée : Si tu avais mis simplement FB, on pouvait faire référence à la cellule A5, au lieu de mettre la valeur en "dur" dans le code. Et si tu as plusieurs valeurs à sommer, il suffit de parcourir la colonne A de "Total - Macro" et de faire la somme de toutes les valeurs de C des autres feuilles dont A est égal au A de la feuille "Total - Macro".
-Il est préférable de s'obliger à déclarer les variables ; c'est chiant... mais ça évite bien des problèmes.
A+

 

[BOISGONTIER]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Bonjour,

Somme 3D conditionnelle avec noms de feuilles génériques & nombre de feuilles variable.

Nom de champ
NbFeuilles =LIRE.CLASSEUR(4)&INDIRECT("iv65000")

=SOMMEPROD(SOMME.SI(INDIRECT("'Feuille"&LIGNE(INDIRECT("1:"&NbFeuilles-1))&"'!A2:A100");A5;INDIRECT("'Feuille"&LIGNE(INDIRECT("1:"&NbFeuilles-1))&"'!c2:c100")))

JB
http://boisgontierjacques.free.fr/

 

[fbeaupre]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Bonjour à tous et merci pour vos idées.

L'idée de Boisgontier est excellente mais le problème est que les feuilles auront des noms variés alors c'est moins applicable.

Les idées de BrunoM45 et Gorfael me semblent bien mais je ne suis pas capable de les appliquer. Ca me donne l'erreur #NOM? . Seriez-vous en mesure d'incorporer votre code dans le fichier que j'ai posté en exemple afin que je puisse voir ce que je fais de mal.

Merci d'avance pour votre aide, c'est très apprécié !

François

 

[Gorfael]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Salut fbeaupre et le forum
Fichier de test
A+
Edit : Du mal à envoyer le fichier, j'ai l'habitude de coder en rar ^^

 

[BOISGONTIER]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

>L'idée de Boisgontier est excellente mais le problème est que les feuilles auront des noms variés alors c'est moins applicable.

C'est la réponse au problème posé!

Avec les noms d'onglets différents:

Somme conditionnelle de champ 3D

=SOMMEPROD(SOMME.SI(INDIRECT("'"&Nf&"'!A2:A100");A5;INDIRECT("'"&Nf&"'!c2:c100")))


Noms de champ
Nf =DECALER(Total!$K$3;;;NB.SI(Total!$K$3:$K$14;"><"&""))
NomsFeuilles =STXT(LIRE.CLASSEUR(1);TROUVE("]";LIRE.CLASSEUR(1))+1;99)&INDIRECT("iv65000")




JB

 

[fbeaupre]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

>

C'est la réponse au problème posé!

JB

Oui t'a effectivement raison. Mon exemple n'était pas assez détaillé. Le lien que tu fournis semble expliquer le tout mais je ne suis pas sur de comprendre. Pourrais-tu me fournir la ligne exacte à utiliser si les noms des feuilles sont variés.

Merci !

 

[BOISGONTIER]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

http://boisgontierjacques.free.fr/fichiers/Matriciel/MatSomme3DNonGenerique.xls

JB

 

[Compte Supprimé 979]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Salut FBeaupre,

J'attire ton attention sur le problème des formules matricielles
qui deviennent très, très, longuent à exécuter avec le temps ... de plus en plus de données

Quant à mon code, voir dans le fichier joint

A+

 

[BOISGONTIER]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

>J'attire ton attention sur le problème des formules matricielles
qui deviennent très, très, longuent à exécuter avec le temps ... de plus en plus de données

Les fonctions VBA ne sont pas nécessairement + rapides:

-La fonction perso utilise la fonction somme.si() (comme la formule matricielle)
-Une boucle sur les feuilles calcule le total (somme pour la formule matricielle)

Où sont les tests de comparaison de rapidité?

JB

 

[fbeaupre]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Bonjour à tous !

La solution de Bruno fonctionne à merveille ! Merci beaucoup !

 

[fbeaupre]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Rebonjour !

Admettons que je veux pousser la chose encore plus loin.

Cette fois-ci, j'ai ajouté des colonnes de données dans chacune des feuilles et j'aimerais que dans la feuille "Total - Macro", dans la cellule C5, je trouve l'addition, de toutes les feuilles, de la colonne dont le titre est la valeur inscrite en B5, au même condition que dans ma première requête (pour FB), en prenant pour acquis que le contenu de B5 variera et que les colonnes ne seront pas toujours placé au même endroits dans les autres feuilles.

Il faut donc que Excel recherche les colonnes, dans toutes les feuilles, qui portent le titre inscrit B5 et qu'il fasse l'addition si la valeur dans la colonne A est "FB".


Merci d'avance pour votre aide.

François

 

[BOISGONTIER]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

S3D avec 2 critères

1-Si les feuilles sont structurées de la même façon

=SOMMEPROD(SOMME.SI(INDIRECT("'"&Nf&"'!A2:A100");A5;DECALER(INDIRECT("'"&Nf&"'!c2:c100");;EQUIV(B5;aa!$C$1:$K$1;0)-1)))

2-Si les feuilles ne sont pas structurées de la même façon (les colonnes des champs à sommer ne sont pas au même endroit)

Pas de solution avec SommeProd()

En VBA (-rapide)

=s3D2(2;NbFeuilles;"A2:A100";A5;"C2:C100";B5;"C1:Z1")

Function S3D2(début, fin, champCritère, critère, champSomme, critère2, ChampCritère2)
  tot = 0
  For s = début To fin
   p = Application.Match(critère2, Sheets(s).Range(ChampCritère2), 0)
   If Not IsError(p) Then tot = tot + Application.SumIf(Sheets(s).Range(champCritère), critère, Sheets(s).Range(champSomme).Offset(, p - 1))
  Next s
  S3D2 = tot
End Function

http://boisgontierjacques.free.fr/pages_site/sommeprod3D.htm#S3D2crit


JB

 

[fbeaupre]

Re : Macro pour faire une somme conditionnelle en recherchant dans toutes les feuille

Merci, ça marche #1