Somme conditionnelle sur format de cellule

bg56

XLDnaute Nouveau
Bonsoir à tous,
Je n'arrive pas à résoudre un problème de somme conditionnelle.
Dans le fichier joint il y a trois onglet: un pour des avances de trésorerie encours, un pour des avances terminées/soldées et un onglet de synthèse.
Les avances de trésorerie se font à différentes sociétés (A,B,C...) et peuvent se faire soir en euros soit en dollars. Une même société peut avoir au même instant un prêt en euros et un autre en dollars.
Dans l'onglet de synthèse, ma somme conditionnelle recherche le nom de la société mais je voudrais qu'en plus on recherche sur la devise car la synthèse se fait par devise.
Dans la cellule B6 de l'onglet Synthèse je ne devrais pas additionner la cellule A2 car elle est en dollars alors que la synthèse que je fais est en euros.
Le fichier joint éclairera, je l'espère, mes propos.
Merci à tous ceux qui pourront m'aider.
 

Pièces jointes

  • Somme par devise.xlsx
    9.7 KB · Affichages: 94

david84

XLDnaute Barbatruc
Re : Somme conditionnelle sur format de cellule

Bonsoir,
une proposition de fonction personnalisée :
Code:
Function Somme_devise(PlageDevise As Range, devise As String, Optional PlageCritere As Range, Optional critere As String) As Long
Dim c As Range
For Each c In PlageDevise
If InStr(c.Text, devise) > 0 And PlageCritere(PlageCritere.Row, c.Column).Value = critere Then _
Somme_devise = Somme_devise + c
Next c
End Function
Fichier enregistré au format .xls pour permettre à d'autres de t'aider.
A+
 

Pièces jointes

  • Copie de Somme par devise.xls
    38.5 KB · Affichages: 99

david84

XLDnaute Barbatruc
Re : Somme conditionnelle sur format de cellule

Re
les arguments 3 et 4 ne sont pas optionnels, donc :
Code:
Function Somme_devise(PlageDevise As Range, devise As String, PlageCritere As Range, critere As String) As Long
Dim c As Range
For Each c In PlageDevise
If InStr(c.Text, devise) > 0 And PlageCritere(PlageCritere.Row, c.Column).Value = critere Then _
Somme_devise = Somme_devise + c
Next c
End Function
A+
 

david84

XLDnaute Barbatruc
Re : Somme conditionnelle sur format de cellule

Re re,
cette fonction permet, au choix, de calculer la somme par devise en précisant ou non une société en particulier (contrairement à la fonction initiale où la plage des sociétés et le nom de la société choisie devaient obligatoirement être précisés) :
Code:
Function Somme_devise2(PlageDevise As Range, devise As String, Optional PlageCritere As Variant, Optional critere As Variant) As Long
Dim c As Range
For Each c In PlageDevise
    If IsMissing(PlageCritere) And IsMissing(critere) Then
        If InStr(c.Text, devise) > 0 Then Somme_devise2 = Somme_devise2 + c
    Else
        If InStr(c.Text, devise) > 0 And PlageCritere(PlageCritere.Row, c.Column) = critere Then _
        Somme_devise2 = Somme_devise2 + c
    End If
Next c
End Function
A+
 
Dernière édition:

bg56

XLDnaute Nouveau
Re : Somme conditionnelle sur format de cellule

Merci à vous david84 et BOISGONTIER.
Quelle richesse ce forum, c'est incroyable!
Ceci étant dit, je suis un peu submergé et je dois "digérer" vos propositions. En effet, je ne suis pas sûr de bien comprendre la nature de vos propositions. Il me faut un peu de temps pour tester et évaluer, voire percer le mystère des fonctions que vous me proposez.
@BOISGONTIER, j'imagine que pour le bilan en $ il faut écrire une fonction EstDollar().
Merci mille fois à tous les deux de vous être penché sur mon problème.
 

bg56

XLDnaute Nouveau
Re : Somme conditionnelle sur format de cellule

@david84
Bonjour,
J'ai essayé les différentes fonctions et cela résout parfaitement mon problème. Une seule chose m'intrigue. Quand on fait la calcul sur une société on utilise PlageCritere et critere. La fonction ne donne de résultat correct que si PlageCritere démarre une colonne avant PlageDevise ce qui à l'utilisation est un peu curieux et non intuitif car il faut sélectionner une colonne avant la zone utile.
Y a-t-il moyen de faire en sorte que PlageDevise et PlageCritere contiennent les mêmes colonnes?
Dans le fichier joint si PlageCritere démarre en colonne B, les résultats sont faux. Merci de votre aide.
 

Pièces jointes

  • test_somme_devise.xls
    35 KB · Affichages: 87
  • test_somme_devise.xls
    35 KB · Affichages: 82
  • test_somme_devise.xls
    35 KB · Affichages: 97

david84

XLDnaute Barbatruc
Re : Somme conditionnelle sur format de cellule

Re
vite fait, en rajoutant un petit -1 dans le code au niveau de c.column, cela fonctionne (cf.fichier).
Si tes tableaux sont présentés comme dans ton fichier exemple, cela fonctionnera.
Sinon, je regarderai demain dans le train si je trouve une autre solution.
Code:
Function Somme_devise(PlageDevise As Range, devise As String, Optional PlageCritere As Variant, Optional critere As Variant) As Long
Dim c As Range
For Each c In PlageDevise
    If IsMissing(PlageCritere) And IsMissing(critere) Then
        If InStr(c.Text, devise) > 0 Then Somme_devise = Somme_devise + c
    Else
        If InStr(c.Text, devise) > 0 And PlageCritere(PlageCritere.Row, c.Column - 1) = critere Then _
        Somme_devise = Somme_devise + c
    End If
Next c
End Function
A+
 

Pièces jointes

  • test_somme_devise.xls
    35.5 KB · Affichages: 60
  • test_somme_devise.xls
    35.5 KB · Affichages: 67
  • test_somme_devise.xls
    35.5 KB · Affichages: 63
Dernière édition:

david84

XLDnaute Barbatruc
Re : Somme conditionnelle sur format de cellule

Re
finalement, je pense que c'est bon avec ce code mais teste-le de ton côté et dis-moi :
Code:
Function Somme_devise(PlageDevise As Range, devise As String, Optional PlageCritere As Variant, Optional critere As Variant) As Long
Dim c As Range, i&
For Each c In PlageDevise
    If IsMissing(PlageCritere) And IsMissing(critere) Then
        If InStr(c.Text, devise) > 0 Then Somme_devise = Somme_devise + c
    Else
    i = i + 1
        If InStr(c.Text, devise) > 0 And PlageCritere.Columns(i) = critere Then _
        Somme_devise = Somme_devise + c
    End If
Next c
End Function
A+
 

Pièces jointes

  • test_somme_devise.xls
    36 KB · Affichages: 88
  • test_somme_devise.xls
    36 KB · Affichages: 94
  • test_somme_devise.xls
    36 KB · Affichages: 97
Dernière édition:

bg56

XLDnaute Nouveau
Re : Somme conditionnelle sur format de cellule

@david84
Cela marche super bien, merci beaucoup de ton aide. J'aimerais bien avoir ta science du VBA. Je peine à déchiffrer ta fonction et suis fourré dans l'aide en ligne du VBA. Mais bon, j'y arriverai peut être un jour.
J'ai fait une petite modification dans la fonction car tous les résultats qu'elle me renvoyait était arrondis. J'ai fini par comprendre que cela était lié au type de variable Long. Je l'ai changé pour Double.
Me feuille de calcul comporte des dizaines de sociétés, la calcul mouline un peu à chaque saisie de données mais rien de catastrophique.
Cordialement.
P.S. Où as-tu appris la programmation VBA?
 

david84

XLDnaute Barbatruc
Re : Somme conditionnelle sur format de cellule

Re
merci beaucoup de ton aide
c'est le but de ce forum : se faire aider et aider les autres.
J'aimerais bien avoir ta science du VBA
Euh là non à côté de certaines personnes sur ce site, il y a encore du boulot:rolleyes:
P.S. Où as-tu appris la programmation VBA?
Cela fait un an que je me suis mis au VBA et ce que j'ai appris, je le dois principalement à ce site, à celui de
et au site de Jacques Boisgontier
.
Concernant les fonctions personnalisées, c'est tout nouveau pour moi (j'ai d'ailleurs répondu à ta demande pour m'exercer:p).
Donc, si tu veux vraiment, tu n'as plus qu'à t'accrocher et tu y arriveras.
A+
 
Dernière édition:

bg56

XLDnaute Nouveau
Re : Somme conditionnelle sur format de cellule

Bon, j'ai compris. Rien de bien sorcier: du travail, du travail et encore du travail!
Je n'ai pas choisi la vie préconisée par Jacques Boisgontier, que je remercie chaleureusement, car pour être franc je n'ai pas compris sa solution qui fonctionne pourtant parfaitement. Je me sens plus à l'aise en mettant en œuvre des solutions que je comprend un peu!
 

bg56

XLDnaute Nouveau
Re : Somme conditionnelle sur format de cellule

@david84
La fonction Somme_devise que tu m'as concoctée marche vraiment bien. J'ai juste rajouter un Application.Volatile avant la déclaration des variables pour avoir une mise à jour automatique de la fonction. En effet dans ce cas la fonction est recalculée à chaque calcul effectué dans n'importe laquelle des cellules de la feuille. Il me reste cependant encore un petit soucis que j'ai eu du mal à identifier. Dans certains cas, le résultat était faux et je ne comprenait pas pourquoi. après pas mal de manips, je me suis rendu compte que la fonction ne comptabilise pas les cellules qui sont masquées. J'ai tellement de prêts en cours dans les sociétés que je suis quasiment obligé de masquer certaines colonnes pour avoir une certaine visibilité de la feuille. Penses-tu que l'on puisse modifier la fonction pour qu'elle prenne en compte toutes les cellules y compris celles qui sont masquées?
Merci, par avance, de ton aide.
 

david84

XLDnaute Barbatruc
Re : Somme conditionnelle sur format de cellule

Re
la fonction initiale était basée sur l'exploitation de la propriété text. Or celle-ci ne semble pas être exploitable sur des valeurs masquées.
La solution que j'ai trouvée est donc d'exploiter la propriété NumberFormat et de déclarer l'argument "devise" de la fonction en Range plutôt qu'en String.
Il te faudra donc sélectionner la cellule comportant la devise plutôt que de l'inscrire en dur dans les arguments de ta fonction (cf. fichier).

La nouvelle fonction est donc :
Code:
Function Somme_devise(PlageDevise As Range, devise As Range, Optional PlageCritere As Variant, Optional critere As Variant) As Long
Dim c As Range, i&
Application.Volatile
For Each c In PlageDevise
    If IsMissing(PlageCritere) And IsMissing(critere) Then
        If devise.NumberFormat = c.NumberFormat Then Somme_devise = Somme_devise + c
    Else
    i = i + 1
        If devise.NumberFormat = c.NumberFormat And PlageCritere.Columns(i) = critere Then _
        Somme_devise = Somme_devise + c
    End If
Next c
End Function
A+
 

Pièces jointes

  • test_somme_devise (2).xls
    44.5 KB · Affichages: 80

Discussions similaires

Réponses
1
Affichages
383

Statistiques des forums

Discussions
311 725
Messages
2 081 940
Membres
101 845
dernier inscrit
annesof