Automatiser la conversion des montants en fonction des devises

Bonjour Hugo,

tu devrais d'abord retourner sur ton sujet précédent : dans mon post du 11 octobre
à 03:23, je t'ai proposé une solution ; télécharge mon fichier et regarde-le, puis
donne-moi ton avis en dessous (sur ton 1er sujet, pas ici).

juste pour info, je n'aide plus un demandeur s'il ne m'a pas répondu sur un sujet
précédent ; car c'est la moindre des choses qu'il réponde suite à une solution
apportée : il doit donner son avis, et dire si la solution lui convient ou non.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour ce présent sujet aussi, il y a crossposting :

* le 11 octobre à 23:49 sur Excel-Pratique ; voir ce lien.

* le 11 octobre à 23:54 sur Excel Downloads ; voir ce lien.

* le 12 octobre à 00:02 sur ce site.

donc tu postes ta demande sur 3 sites à la fois, à environ 5 mn d'intervalle,
sans même attendre une demi-heure ou une heure pour voir si tu auras ou
non une réponse ! (c'est évident que t'as fait un copier-coller de l'énoncé)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pire : tu n'indique pas sur un site quand on t'a fourni une réponse sur un autre
site ; c'est pas étonnant qu'un contributeur n'a pas de réponse d'Hugo !  :(

même si tu as déjà eu une réponse ailleurs, regarde quand même ma solution
du 1er sujet : tu pourrais au moins l'essayer, ne serait-ce que pour comparer les
2 solutions ; peut-être que ma solution te plaira mieux ? qui sait ? ce que j'ai fait
est bien selon 2 critères : les boutiques ET les catégories.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

même si tu me réponds sur ton 1er sujet, je ne vais pas chercher pour rien une
solution pour ta 2ème demande si un contributeur va te trouver une solution sur
un autre site ; surtout que si ça arrive, il n'y aura plus de réponse sur ce site.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

j'ai bien remarqué ta phrase habituelle :

« Si un crack d'Excel pouvait m'aider, ça m'arrangerait beaucoup ! »

tu vois que malgré cette phrase, tu n'auras pas forcément une réponse ;
alors c'est peut-être inutile que tu la ressorte dans tous tes sujets.  ;)

rhodo

@Hugo,

je suis un simple membre : je suis ni Administrateur, ni Modérateur ;
je ne peux donc pas supprimer des posts.  ;)

pour ta présente demande, tu as déjà eu des réponses ailleurs :

* sur Excel Downloads : tu as déjà vu la 1ère réponse de sylvanu
(post #3) ; regarde la 2ème réponse de sylvanu (post #5).

* sur Excel-Pratique : tu as déjà vu les 2 réponses de BAROUTE78.

pour ta 2ème demande, je les laisse continuer à t'aider.  :)

rhodo

Re @rhodo,

Je comprends ! @sylvanu m'a beaucoup aidé pour l'automatisation du tableau récap, mais il ne souhaite pas poursuivre sur le TCD. 

Aurais-tu une piste en faisant l'usage d'un TCD à la place du tableau récap s'il te plaît ?

Merci !

@Hugo

je connais les TCD seulement de nom, donc je ne peux pas t'aider pour ça ;
par contre, même si d'autres t'ont déjà aidé sur ta 2ème demande, je vais
quand même faire ton exo « à ma sauce ».  ;)  tu pourras comparer les
solutions apportées et choisir celle qui te convient le mieux ; je prends
donc le risque que tout ce que je vais élaborer ne te servira pas ; mais
comme on est sur un forum, ça intéressera peut-être d'autres lecteurs ?

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

j'ai déjà commencé ; comme je ne m'occupe pas des TCD, j'ai purement et
simplement supprimé les 2 feuilles "Test TCD 1" et "Test TCD 2" ; il reste
donc les 3 feuilles "FX rates" ; "Output" ; "db".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

le plus simple à faire est sur la feuille "FX rates" ; ma formule en C2 :
=DECALER(H$1;LIGNE()-1;C$1-2020)

comme ensuite j'ai supprimé la colonne D, formule en C2 :
=DECALER(G$1;LIGNE()-1;C$1-2020)

bien sûr, j'ai étendu la formule de C2 vers le bas jusqu'en C7.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

pour "Output", ma solution sera en VBA.  :)  ça va me prendre beaucoup
de temps, alors je pense que ça sera prêt demain seulement.

remarque : sur la feuille "Output", il n'y aura plus aucune formule.  ;)

rhodo

@Rhodo

Ok merci beaucoup !! Si je comprends bien, tu comptes utiliser du VBA pour remplir le tableau récap dans "Output" ?

A vrai dire, je ne pense pas en avoir à ce stade, c'est surtout le TCD qui pourrait être utile !

Merci encore !

@Hugo

oui, tu as bien compris : c'est par VBA que je vais faire le tableau de la feuille
"Output".  :)  (à partir de la ligne 5, car les lignes 1 à 4 seront inchangées)

as-tu remarqué que ma formule avec DECALER(...) est plus courte qu'une
formule avec INDEX(EQUIV(...)) ?  ;)

rhodo

Bonjour @rhodo

Je crois que tu ne m'as encore transmis ton fichier, donc non je n'ai pas encore pu voir haha ^^

Si ça ne te dérange pas, je voudrais bien que tu regardes mon premier topic ici , avec une petite demande supplémentaire concernant le fichier que tu m'as transmis hier :)

Je t'avoue que ça me sera davantage utile que l'Output en VBA, donc si possible pourrais-tu m'aider sur l'autre demande en priorité ?

Merci encore !!

Bonjour Hugo,

ton fichier en retour : https://www.cjoint.com/c/MJnhDWENzG0

à l'ouverture du fichier, tu es sur la feuille "FX rates" ; note bien que la plage colorée
est vide ; sélectionne C1 ; dans la liste, choisis 2020 ➯ 0,357 à 1,162 ; puis choisis
2022 ➯ 0,348 à 1,143 ; enfin, choisis 2021 ➯ 0,371 à 1,210 ; formule en C2 :
=SI(C$1="";"";DECALER(G$1;LIGNE()-1;C$1-2020))

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

va sur la feuille "Output" ; note bien que sous la ligne 4, c'est entièrement vide ;
note que la plage colorée aussi est vide ; fais Ctrl a ; ça a choisi Local Currency,
et ça a affiché le tableau entier en monnaie locale, SAUF pour la dernière ligne ;
c'est pourquoi j'ai mis « (€) » à droite de « Total Zone » ➯ texte entier de B27 :
"Total Zone (€)" ; vérifie bien attentivement tous les montants.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

fais Ctrl d ➯ la devise est devenue "Euro" au lieu de "Local Currency" ;
là aussi, vérifie bien attentivement tous les montants.

fais Ctrl d ➯ ça retourne en monnaie locale.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

bien sûr, tu peux aller en C2 puis utiliser la liste déroulante : ça fera la même chose
que ce qu'on vient de voir ; mais perso, je trouve que c'est plus rapide et plus facile
d'utiliser un raccourci clavier.  ;)

ne cherche pas de formules : y'en a aucune ; tout est fait par le code VBA.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

va sur "FX rates" ; en C1, choisis 2022 ➯ 0,348 à 1,143 ; va sur "Output" ;
le tableau est inchangé, ET les données sont FAUSSES car c'est toujours
les données précédentes, qui étaient selon les taux de 2021 ! fais Ctrl a
pour actualiser les données.  :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

fais Alt F11 pour voir le code VBA ; puis quand tu auras tout lu,
fais de nouveau Alt F11 pour revenir sur Excel.

rhodo

@Hugo

Lis d'abord mon post précédent de 10:11.

ce post est pour montrer tout le code VBA du classeur Excel.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

code VBA de Module1 (46 lignes) :

Option Explicit: Option Base 1
Public Const LC As String * 14 = "Local Currency": Dim T2, m&, n&

Private Sub Init()
  Dim T1, k As Byte, d&, i&: m = Rows.Count
  T1 = Application.Transpose(Worksheets("FX rates").[C2:C7])
  With Worksheets("db")
    Application.ScreenUpdating = 0: .AutoFilterMode = 0
    n = .Cells(m, 2).End(3).Row: If n = 1 Then Exit Sub
    T2 = .Range("A2:E" & n): n = n - 1
  End With
  For i = 1 To n
    Select Case T2(i, 1)
      Case "KWD": k = 1: Case "AED": k = 2: Case "BHD": k = 3: Case "QAR": k = 4
      Case "SAR": k = 5: Case "USD": k = 6: Case Else: k = 0
    End Select
    If k > 0 Then If T1(k) > 0 Then T2(i, 5) = T2(i, 4) / T1(k)
  Next i
  Worksheets("Output").Select: d = Cells(m, 2).End(3).Row
  If d > 4 Then Rows("5:" & d).Delete
End Sub

Sub DoTbl()
  Dim TH@(3, 13), F@, G@, v@, x@, a$, b$, d$, r&, c As Byte, p As Byte, k As Byte, i&: Init
  If [C2] = "" Then Application.EnableEvents = 0: [C2] = LC: Application.EnableEvents = -1
  a = T2(1, 2): p = InStr(a, "_"): a = Left$(a, p - 1): m = 1: r = 5: c = 3: k = 5 + (Asc([C2]) = 76)
  For i = 1 To n
    d = T2(i, 2): b = d: p = InStr(b, "_"): b = Left$(b, p - 1)
    If b <> a Then
      Cells(r, 2) = "Total Market " & m: Cells(r, 2).Resize(, 14).Font.Bold = -1
      For p = 1 To 13
        Cells(r, p + 2) = TH(1, p): TH(3, p) = TH(3, p) + TH(2, p): TH(1, p) = 0: TH(2, p) = 0
      Next p
      r = r + 1: a = b: m = m + 1
    End If
    Cells(r, 2) = d: v = T2(i, k): x = T2(i, 5): Cells(r, c) = v: p = c - 2
    TH(1, p) = TH(1, p) + v: TH(2, p) = TH(2, p) + x: F = F + v: G = G + x: c = c + 1
    If c = 15 Then
      TH(1, 13) = TH(1, 13) + F: TH(2, 13) = TH(2, 13) + G: Cells(r, c) = F: F = 0: G = 0: r = r + 1: c = 3
    End If
  Next i
  Cells(r, 2) = "Total Market " & m: Cells(r, 2).Resize(2, 14).Font.Bold = -1: i = r + 1
  For p = 1 To 13: Cells(r, p + 2) = TH(1, p): TH(3, p) = TH(3, p) + TH(2, p): Cells(i, p + 2) = TH(3, p): Next p
  Cells(i, 2) = "Total Zone (€)": Range("C5:O" & i).NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

code VBA de Module2 (7 lignes) :

Option Explicit

Sub Devise()
  If ActiveSheet.Name <> "Output" Then Exit Sub
  If [C2] = "" Then [C2] = LC Else [C2] = IIf([C2] = LC, "Euro", LC)
End Sub

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

code VBA du module de la feuille "Output" (7 lignes) :

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .CountLarge > 1 Then Exit Sub
    If .Address = "$C$2" Then DoTbl
  End With
End Sub

rhodo

@Rhodo,

Woooooowwwwww !!!!! Je n'ai pas les mots !

Un grand merci ! J'espère savoir faire ça un jour dans ma vie ^^

La seule amélioration serait de ne pas avoir besoin d'actualiser le tableau lorsqu'on change les taux dans l'onglet FX rates, mais c'est déjà très très bien !

Encore un grand merci et bon week-end !

@Hugo

merci pour ton retour, et je suis ravi que tu aies apprécié ma solution VBA.  :)

à propos de l'amélioration que tu as proposé : j'aurais pu le faire, mais c'est
tout à fait volontairement que je ne l'ai pas fait ; pourquoi ? car quand tu
choisis une année, c'est inutile de construire tout le tableau de "Output"
si l'utilisateur s'est trompé d'année, ou s'il veut changer cette année
juste pour voir les changements que ça fait sur "FX rates".

en conséquence, après un changement d'année sur "FX rates", il ne faut
jamais oublier, quand on va sur "Output", de faire Ctrl a pour actualiser
le tableau ; c'est la seule petite « contrainte », et finalement, c'est quand
même un avantage de ne pas avoir fait d'actualisation automatique
si on change d'année en "FX rates".

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

comme y'a aucune formule : a) ça allège de toutes les formules qu'on
aurait dû mettre si on avait essayé une solution par formule ; mais peut-
être que ça n'aurait même pas été possible du seul fait que le nombre
de lignes est variable (aussi bien pour un magasin donné que pour
l'ensemble du tableau : y compris lignes de totaux d'un magasin et
totaux de la zone) ; b) y'a aucun risque que l'utilisateur écrase une
formule vu qu'il n'y en n'a pas.  ;)

vu la facilité d'utilisation de ma solution VBA, dont les raccourcis ^a
et ^d, tu n'as peut-être plus besoin d'une solution via TCD.  ;)

bon week-end à toi aussi.  :)

rhodo

Bonjour Hugo,

Lis d'abord mon post précédent, du 13 octobre à 17:48.

j'ai regardé un peu les TCD ➯ autre fichier Excel :

https://www.cjoint.com/c/MJpxv7PAD00

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

* sur la feuille "LC" : vérifie tous les montants.

* sur la feuille "EU" : vérifie tous les montants (c'est selon 2021).

* va sur "FX rates" ; en C1, sélectionne 2022.

* va sur "EU" ; fais Alt F5 : ça actualise le TCD ➯ selon 2022.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

à te lire pour avoir ton avis.  :)

rhodo