XL 2019 appliquer une formule similaire à si sur un tableau

uhhh

XLDnaute Nouveau
bonjour,

j'ai un exercice avec 3 colonnes qui référence les tailles et quantités d'un même produit :


tailles : quantités :

ratio quantité stock / stock total :
total quantité stock / stock total arrondi
160​
120​
53,33​
53​
140​
10​
4,44​
4​
130​
40​
17,78​
18​
120​
55​
24,44​
24​
stock total : 225​
100%​
99%​


et il me faut trouver une formule pour :

si le total arrondi est a 99% ajouter 1 au produit ayant la taille la plus haute, ici 160
si le ratio arrondi est à 101% retirer 1 au "ratio quantité stock / stock total" le plus bas, ici 4,44

cette formule doit marcher sur une liste contenant plusieurs produits avec chacun plusieurs tailles.
( le tableau présenté ne montre que les 4 tailles d'un même produit )

si vous arrivez a me répondre vous etes incroyable sachez le !
 
Solution
@uhhh

à partir du fichier .xlsx de ton post #6 :

* je l'ai converti en .xlsm pour pouvoir y mettre du code VBA.

* compte tenu de l'organisation de ton tableau structuré initial avec des cellules vides en colonne A et 4 lignes "total :" les avantages d'un tableau structuré deviennent inutiles ➯ j'ai préféré convertir ton tableau structuré en plage normale, ce qui a beaucoup facilité le travail demandé.

* regarde ton nouveau tableau ; y'a plein d'cellules vides et y'a plus aucune formule ! mais t'inquiètes pas, c'est normal : y'a qu'les données de base ; fais Ctrl e ➯ travail effectué !

je te laisse vérifier tous les résultats ; à te lire pour avoir ton avis. ;)
...

Deadpool_CC

XLDnaute Impliqué
je vais manqué de temps (de suite) mais un autre contributeur va surement s'y pencher et dans le cas contraire je regarde tout à l'heure.
par contre en gestion de stock ... faut vraiment écraser tes quantités ? ou il faut ajouter une colonne avec les Qte Corrigées ?
 

uhhh

XLDnaute Nouveau
je vais manqué de temps (de suite) mais un autre contributeur va surement s'y pencher et dans le cas contraire je regarde tout à l'heure.
par contre en gestion de stock ... faut vraiment écraser tes quantités ? ou il faut ajouter une colonne avec les Qte Corrigées ?
merci c'est super gentil, et pour les stocks les 2 peuvent fonctionner mais on parle de milliers de produits donc impossible de faire ça de manière individuel... il faut absolument une formule qui pourrait soit corriger une nouvelle colonne de quantité soit écraser les cellules déjà existantes.
bonne journée à vous
 

Deadpool_CC

XLDnaute Impliqué
Dans le doute j'ai fait cela dans une autre colonne pour que tu ne perde pas tes stock "réels".
Après j'ai garder ta logique ... donc fichier joins

mais si milliers de produit va falloir faire les formule autrement
ton exemple reflète réellement tes données réelles ? suis étonné que les produits (référence) soit pas présent sur chaque ligne de chaque taille.
En fonction de tes vraies données, il y aurait surement moyen de faire des formules ou tu ne serait pas obligés de recaler les plages de sommes, ...
 

Pièces jointes

  • FICHIER 2XEMPLE.xlsx
    14.2 KB · Affichages: 5

uhhh

XLDnaute Nouveau
Dans le doute j'ai fait cela dans une autre colonne pour que tu ne perde pas tes stock "réels".
Après j'ai garder ta logique ... donc fichier joins

mais si milliers de produit va falloir faire les formule autrement
ton exemple reflète réellement tes données réelles ? suis étonné que les produits (référence) soit pas présent sur chaque ligne de chaque taille.
En fonction de tes vraies données, il y aurait surement moyen de faire des formules ou tu ne serait pas obligés de recaler les plages de sommes, ...
je viens de regarder c'est déjà un gros déblocage, merci beaucoup de ton aide !
C'est un exemple le "produit 1" représente une référence produit en effet.
c'est fou comme le problèmes Excel expliqué à l'oral paraissent faciles mais s'applique difficilement avec ce type de langage numérique...😂
 

soan

XLDnaute Barbatruc
Inactif
Bonsoir uhhh, Deadpool,

bienvenue sur le site XLD ! :)

je te propose le fichier joint ci-dessous.

* note que les cellules D2 à D5 sont vides.

* fais Ctrl e et vérifie les résultats.



code VBA (21 lignes) :

VB:
Option Explicit

Sub Essai()
  Dim n&: n = Cells(Rows.Count, 1).End(3).Row: If n = 1 Then Exit Sub
  Dim tp As Byte, tm As Byte, ra#, rm#, i&, j&, k&: rm = 100
  Application.ScreenUpdating = 0: Range("D2:D" & n) = Empty
  For i = 2 To n
    With Cells(i, 1)
      tp = .Value 'taille produit
      If tp > tm Then tm = tp: j = i 'taille maxi
      ra = Round(.Offset(, 2), 2): .Offset(, 3) = ra
      If ra < rm Then rm = ra: k = i 'ratio mini
    End With
  Next i
  ra = Cells(n + 1, 4)
  If ra < 1 Then
    Cells(j, 4) = Cells(j, 4) + 0.01
  ElseIf ra > 1 Then
    Cells(k, 4) = Cells(k, 4) - 0.01
  End If
End Sub

soan
 

Pièces jointes

  • Exo uhhh - v1.xlsm
    18.3 KB · Affichages: 3
Dernière édition:

soan

XLDnaute Barbatruc
Inactif
Bonjour uhhh,

Lis d'abord mon post #9. :)

dans ton post #5 tu as écrit : « on parle de milliers de produits »,
donc y'a des milliers de lignes ➯ autre version plus rapide.

même manip : fais Ctrl e et vérifie les résultats.



code VBA (22 lignes) :

VB:
Option Explicit

Sub Essai()
  Dim n&: n = Cells(Rows.Count, 1).End(3).Row: If n = 1 Then Exit Sub
  Dim T, tp As Byte, tm As Byte, ra#, rm#, i&, j&, k&: rm = 100
  Application.ScreenUpdating = 0: Range("D2:D" & n) = Empty
  n = n - 1: T = [A2].Resize(n, 4)
  For i = 1 To n
    tp = T(i, 1) 'taille produit
    If tp > tm Then tm = tp: j = i 'taille maxi
    ra = Round(T(i, 3), 2): T(i, 4) = ra
    If ra < rm Then rm = ra: k = i 'ratio mini
  Next i
  ra = Cells(n + 2, 4)
  If ra < 1 Then
    T(j, 4) = T(j, 4) + 0.01
  ElseIf ra > 1 Then
    T(k, 4) = T(k, 4) - 0.01
  End If
  [D2].Resize(n) = Application.Index(T, _
    Evaluate("Row(" & "1:" & n & ")"), 4)
End Sub

soan
 

Pièces jointes

  • Exo uhhh - v2.xlsm
    18.6 KB · Affichages: 1

soan

XLDnaute Barbatruc
Inactif
@uhhh

à partir du fichier .xlsx de ton post #6 :

* je l'ai converti en .xlsm pour pouvoir y mettre du code VBA.

* compte tenu de l'organisation de ton tableau structuré initial avec des cellules vides en colonne A et 4 lignes "total :" les avantages d'un tableau structuré deviennent inutiles ➯ j'ai préféré convertir ton tableau structuré en plage normale, ce qui a beaucoup facilité le travail demandé.

* regarde ton nouveau tableau ; y'a plein d'cellules vides et y'a plus aucune formule ! mais t'inquiètes pas, c'est normal : y'a qu'les données de base ; fais Ctrl e ➯ travail effectué !

je te laisse vérifier tous les résultats ; à te lire pour avoir ton avis. ;)



code VBA (34 lignes) :

VB:
Option Explicit

Sub Essai()
  Dim n1&
  n1 = Cells(Rows.Count, 2).End(3).Row: If n1 < 3 Then Exit Sub
  Dim T, T1&, T2#, T3&, L0&, L1&, L2&, L3&, L4&
  Dim n2&, tp As Byte, tm As Byte, ra#, rm&
  Application.ScreenUpdating = 0: Range("D2:E" & n1) = Empty
  n2 = n1 - 1: T = [A2].Resize(n2, 5): L1 = 1
  Do While L1 < n1
    T1 = 0: T2 = 0: T3 = 0: tm = 0: rm = 100: L2 = L1
    Do While Left$(T(L2, 2), 5) <> "total"
      T1 = T1 + T(L2, 3): tp = T(L2, 2)
      If tp > tm Then tm = tp: L3 = L2 'taille maxi
      L2 = L2 + 1
    Loop
    For L0 = L1 To L2 - 1
      If T1 <> 0 Then
        ra = T(L0, 3) / T1 * 100: T(L0, 4) = ra: T2 = T2 + ra
        ra = Round(ra, 0): T(L0, 5) = ra: T3 = T3 + ra
        If ra < rm Then rm = ra: L4 = L0 'ratio mini
      End If
    Next L0
    Cells(L2 + 1, 3) = T1: T(L2, 4) = T2: T(L2, 5) = T3
    If T3 < 100 Then
      T(L3, 5) = T(L3, 5) + 1: T(L2, 5) = T3 + 1
    ElseIf T3 > 100 Then
      T(L4, 5) = T(L4, 5) - 1: T(L2, 5) = T3 - 1
    End If
    L1 = L2 + 1
  Loop
  [D2].Resize(19, 2) = Application.Index(T, _
    Evaluate("Row(" & "1:" & L2 & ")"), [Column(D:E)])
End Sub

soan
 

Pièces jointes

  • FICHIER 2XEMPLE.xlsm
    21 KB · Affichages: 11

uhhh

XLDnaute Nouveau
@uhhh

à partir du fichier .xlsx de ton post #6 :

* je l'ai converti en .xlsm pour pouvoir y mettre du code VBA.

* compte tenu de l'organisation de ton tableau structuré initial avec des cellules vides en colonne A et 4 lignes "total :" les avantages d'un tableau structuré deviennent inutiles ➯ j'ai préféré convertir ton tableau structuré en plage normale, ce qui a beaucoup facilité le travail demandé.

* regarde ton nouveau tableau ; y'a plein d'cellules vides et y'a plus aucune formule ! mais t'inquiètes pas, c'est normal : y'a qu'les données de base ; fais Ctrl e ➯ travail effectué !

je te laisse vérifier tous les résultats ; à te lire pour avoir ton avis. ;)



code VBA (34 lignes) :

VB:
Option Explicit

Sub Essai()
  Dim n1&
  n1 = Cells(Rows.Count, 2).End(3).Row: If n1 < 3 Then Exit Sub
  Dim T, T1&, T2#, T3&, L0&, L1&, L2&, L3&, L4&
  Dim n2&, tp As Byte, tm As Byte, ra#, rm&
  Application.ScreenUpdating = 0: Range("D2:E" & n1) = Empty
  n2 = n1 - 1: T = [A2].Resize(n2, 5): L1 = 1
  Do While L1 < n1
    T1 = 0: T2 = 0: T3 = 0: tm = 0: rm = 100: L2 = L1
    Do While Left$(T(L2, 2), 5) <> "total"
      T1 = T1 + T(L2, 3): tp = T(L2, 2)
      If tp > tm Then tm = tp: L3 = L2 'taille maxi
      L2 = L2 + 1
    Loop
    For L0 = L1 To L2 - 1
      If T1 <> 0 Then
        ra = T(L0, 3) / T1 * 100: T(L0, 4) = ra: T2 = T2 + ra
        ra = Round(ra, 0): T(L0, 5) = ra: T3 = T3 + ra
        If ra < rm Then rm = ra: L4 = L0 'ratio mini
      End If
    Next L0
    Cells(L2 + 1, 3) = T1: T(L2, 4) = T2: T(L2, 5) = T3
    If T3 < 100 Then
      T(L3, 5) = T(L3, 5) + 1: T(L2, 5) = T3 + 1
    ElseIf T3 > 100 Then
      T(L4, 5) = T(L4, 5) - 1: T(L2, 5) = T3 - 1
    End If
    L1 = L2 + 1
  Loop
  [D2].Resize(19, 2) = Application.Index(T, _
    Evaluate("Row(" & "1:" & L2 & ")"), [Column(D:E)])
End Sub

soan
Bonjour Soan !
Je viens de faire tourner le programme, il marche très bien !
Merci infiniment ! 😁😁
 

soan

XLDnaute Barbatruc
Inactif
@uhhh

Je viens de faire tourner le programme, il marche très bien !

merci pour ton retour ! 😊 puisque c'est ok, peux-tu cocher ainsi mon post #11 :​

Coche.jpg


le bord vertical droit passera en vert ➯ les lecteurs peuvent aller dessus.

(rappel : le numéro d'un post est dans le coin haut droit, à droite du caractère « # »)

soan
 

Statistiques des forums

Discussions
299 930
Messages
1 980 158
Membres
207 010
dernier inscrit
Setry