XL 2019 appliquer une formule similaire à si sur un tableau

[uhhh]

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 !

 

[Deadpool_CC]

Bonjour,
plutôt que les données écrites en texte ... t'as pas un fichier exemple ?

 

[uhhh]

voici le fichier
merci d'avance

 

[Deadpool_CC]

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]

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

 

[uhhh]

ci-joint le fichier réactualisé avec les 2 cas énoncés dans le premier message.
exemple concret de mon problème
merci d'avance

 

[Deadpool_CC]

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, ...

 

[uhhh]

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]

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) :

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

 

[soan]

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) :

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

 

[soan]

@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) :

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

 

[uhhh]

@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) :

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]

@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 :​

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