Somme Si avec deux devises

[crashtwo]

Bonjour à tous.

J'ai besoin d'aider car je suis vraiment plus à jour sur Excel.

Pour situer, je réalise un document qui regroupe des commandes.
Je souhaite trouver 2 formules quasi identiques pour automatiser le calcul des coûts qui sont en 2 monnaies : € et $ dans une même colonne.

Plus dans le detail. :
1 colonne, de I2 à I32, avec des valeurs chiffrées en € et en $ (pour les commandes) et une seconde colonne, de N2 à N32, avec le même type de valeurs (en € et $) pour le coût des déplacements.

2 cellules en dessous de cette colonne :
- La premiere est O32.
C'est la cellule que je dédie à la somme des commandes + coût déplacement uniquement en €
- La seconde est P32.
C'est la cellule que je dédie à la somme de toutes les commandes + coût déplacement uniquement en $

Mon souhait est donc de réaliser dans la cellule O32 une somme qui exclu les valeurs en $
Et faire la meme chose en P32 mais excluant les valeurs en €
Pour au final connaitre les deux totaux en € et $.

J'ai essayé avec une somme SI, mais je n'ai pas réussi à intégrer la condition : "si la cellule est en € ou en $"

D'avance, merci.

 

[mapomme]

Bonsoir @crashtwo,

mais il faut ajouter le calcul quand ce n'est pas une journée complète mais des heures par un taux horaire. Et aussi qu'au niveau de la démobilisation, si c'est en $ ou €.

J'ai codé une autre fonction : Est_Devise (plage , pepete)

Cette fonction prend deux arguments en entrée :

1. plage qui doit être une plage d'une seule ligne ou d'une seule colonne
2. pepete qui est le symbole monétaire qu'on recherche (cet argument est facultatif - s'il est absent, on considère toutes les devises)

Cette fonction renvoie un tableau de même dimension que la plage d'entrée. Chaque élément de ce tableau vaut TRUE (VRAI) si la cellule correspondante de la plage est au format de la devise recherchée. Dans le cas contraire, l'élément du tableau en retour contiendra FALSE (FAUX).
Cette fonction peut donc être très utile au sein de la fonction SOMMEPROD pour déterminer si chaque cellule d'une plage est au format de la devise recherchée.

Pour l'exemple, voir les formules des cellules N35, N38, N41 et O35, O38, O41.

Le code de la fonction Est_Devise est dans module1:

Function Est_Devise(plage As Range, Optional pepete As String)
Dim t, pattern, j&, i&, n&, xcell
  If IsMissing(pepete) Then pattern = "*" Else pattern = "*" & pepete & "*"
  If plage.Count = 1 Then
    Est_Devise = (plage.Text Like pattern)
  ElseIf plage.Rows.Count = 1 Then
    t = plage.Value
    For Each xcell In plage
      n = n + 1
      t(1, n) = xcell.Text Like pattern
    Next xcell
    Est_Devise = t
  ElseIf plage.Columns.Count = 1 Then
    t = plage.Value
    For Each xcell In plage
      n = n + 1
      t(n, 1) = xcell.Text Like pattern
    Next xcell
    Est_Devise = t
  Else
    Est_Devise = CVErr(xlErrRef)
  End If
End Function

 

[Dranreb]

Bonsoir.
Information: Si le format d'une cellule est monétaire, la propriété Value du Range qui la représente est servie en Currency, bien que sa vraie valeur demeure stockée en Double dans Excel. Il serait donc possible de faire simplement Est_Devise = VarType(Plage.Value) = vbCurrency.
Il reste cependant à distinguer le $ du € dans le format de nombre.

 

[crashtwo]

Merci pour l'aide. Dommage qu'on ne sache pas faire cela en seulement deux cases. Mais je vais utiliser ce tableau en cachant le petit tableau.

 

[mapomme]

Re @crashtwo

Dommage qu'on ne sache pas faire cela en seulement deux cases. Mais je vais utiliser ce tableau en cachant le petit tableau.

On peut tout à fait le faire en deux formules. J'avais décomposé pour aider à la compréhension.

 

[crashtwo]

Top merci, par contre, je fais la même chose que ta formule et ça me met: "NOM!", tu saurais m'expliquer pourquoi?

 

[zebanx]

Bonjour à tous (mapomme 😉)

Je profite de ta présence @mapomme pour te demander stp si le code que tu as indiqué en #13 peut-être amendé pour utiliser deux colonnes sur un range.
J'ai essayé de repartir de ce code en essayant de décomposer la plage mais ça me renvoie une erreur (essai avec SD2).

La formule sommeprod fonctionne parfaitement mais c'est le principe de décomposition du range choisi dans l'UDF qui m'intéresserait ici.

Exemple toujours plus explicite avec un petit tableau.

Si tu as un peu de temps pour regarder, je t'en remercie par avance.
Et bravo pour cet énième utile proposition. 😎

Bonne journée à tous
zebanx

 

[Dranreb]

Mon histoire de Currency ne marche pas dans tous les cas.
En fait ça ne marche qu'avec les cellules auxquelles on a soit tapé un montant avec '€' soit attribué une expression Currency à leurs Value.
Je propose ces deux fonctions qui peuvent être utilisées directement comme paramètre indépendant d'un SOMMEPROD vu qu'elle rendent du numérique et non du logique.

Function EstEuro(ByVal Rng As Range)
   Dim TNF() As String, TVAL(), L As Long, Cel As Range
   If Rng.Rows.Count = 1 Then
      EstEuro = -(VarType(Rng.Value) = vbCurrency Or Rng.NumberFormat Like "*[[]$€*]*")
   Else
      TVAL = Rng.Value
      ReDim TNF(1 To UBound(TVAL, 1))
      For Each Cel In Rng: L = L + 1: TNF(L) = Cel.NumberFormat: Next Cel
      For L = 1 To UBound(TVAL, 1): TVAL(L, 1) = -(VarType(TVAL(L, 1)) = vbCurrency Or TNF(L) Like "*[[]$€*]*"): Next L
      EstEuro = TVAL: End If
   End Function
Function EstDollar(ByVal Rng As Range)
   Dim TNF() As String, TVAL(), L As Long, Cel As Range
   If Rng.Rows.Count = 1 Then
      EstDollar = -(Rng.NumberFormat Like "*[[]$$*]*")
   Else
      ReDim TVAL(1 To Rng.Rows.Count, 1 To 1)
      For Each Cel In Rng: L = L + 1: TVAL(L, 1) = -(Cel.NumberFormat Like "*[[]$$*]*"): Next Cel
      EstDollar = TVAL: End If
   End Function

Édition: Enlevé une instruction devenue inutile.

 

[mapomme]

Bonsoir @zebanx 🙂,

J'aurais écrit un truc du genre:

Function SomDev(plage As Range, Optional pepete As String) As Currency
Dim cumul As Currency, pattern, t, i As Long
  If IsMissing(pepete) Then pattern = "*" Else pattern = "*" & pepete & "*"
  t = plage.Value
  For i = 1 To UBound(t)
    If IsNumeric(t(i, 1)) Then If t(i, 2) Like pattern Then cumul = cumul + t(i, 1)
  Next i
  SomDev = cumul
End Function

edit : bonsoir @Dranreb 😉

 

[zebanx]

Rebonjour Mapomme

Je te remercie beaucoup car j'ai bien tourné pour un résultat médiocre.
Et ce n'est pas très long en plus, vraiment super😉.
Très content de voir autant de bons codes UDF qui sortent de plus en plus sur le site.

Bonne soirée à toi
zebanx

Edit : bonsoir à Dranreb😎

 

[crashtwo]

Re @crashtwo


On peut tout à fait le faire en deux formules. J'avais décomposé pour aider à la compréhension.

Top merci, par contre, je fais la même chose que ta formule et ça me met: "NOM!", tu saurais m'expliquer pourquoi?* (le message est passé inaperçu haha)

 

[mapomme]

Bonjour @crashtwo 🙂

Avec ton pseudo, c'est normal que ça bogue au moins deux fois !

J'avais effectivement raté ton message 😕. Peux-tu fournir un court extrait de tond fichier (anonymisé) qui merdoie ?

A+

 

[ellimac]

Bonjour à tous.

J'ai besoin d'aider car je suis vraiment plus à jour sur Excel.

Pour situer, je réalise un document qui regroupe des commandes.
Je souhaite trouver 2 formules quasi identiques pour automatiser le calcul des coûts qui sont en 2 monnaies : € et $ dans une même colonne.

Plus dans le detail. :
1 colonne, de I2 à I32, avec des valeurs chiffrées en € et en $ (pour les commandes) et une seconde colonne, de N2 à N32, avec le même type de valeurs (en € et $) pour le coût des déplacements.

2 cellules en dessous de cette colonne :
- La premiere est O32.
C'est la cellule que je dédie à la somme des commandes + coût déplacement uniquement en €
- La seconde est P32.
C'est la cellule que je dédie à la somme de toutes les commandes + coût déplacement uniquement en $

Mon souhait est donc de réaliser dans la cellule O32 une somme qui exclu les valeurs en $
Et faire la meme chose en P32 mais excluant les valeurs en €
Pour au final connaitre les deux totaux en € et $.

J'ai essayé avec une somme SI, mais je n'ai pas réussi à intégrer la condition : "si la cellule est en € ou en $"

D'avance, merci.

Bonjour,
Dans une colonne entrer la formule :
=CELLULE("format";I2)
Renvoie M2 si format en euro ou P2 si format en dollar

ensuite faire un Sommesi prenant en compte la colonne renvoyant les M2 et P2 et additionnat les valeurs en colonne I puis en colonne N.

Cordialement

Ellimac

 

[crashtwo]

Bonjour @crashtwo 🙂

Avec ton pseudo, c'est normal que ça bogue au moins deux fois !

J'avais effectivement raté ton message 😕. Peux-tu fournir un court extrait de tond fichier (anonymisé) qui merdoie ?

A+

Désolé, j'étais en vadrouille. Voici le fichier

 

[mapomme]

Bonjour @crashtwo,

Même pas besoin d'ouvrir ton fichier. Il est de type *.xlsx. Il ne contient donc pas de macro!

 

[Staple1600]

Bonsoir le fil,

Y-a-t-il une faille dans la solution par formule proposée par ellimac?
(à qui je souhaite la bienvenue sur le forum)
Moi, je la trouve pas mal cette astuce, non ?