XL 2019 appliquer une formule similaire à si sur un tableau

uhhh · 28 Juin 2022

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 !

soan · 29 Juin 2022

@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 · 28 Juin 2022

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

uhhh · 28 Juin 2022

voici le fichier
merci d'avance

Deadpool_CC · 28 Juin 2022

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 · 28 Juin 2022

Deadpool_CC à dit:
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 · 28 Juin 2022

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 · 28 Juin 2022

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 · 28 Juin 2022

Deadpool_CC à dit:
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 · 28 Juin 2022

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

soan · 29 Juin 2022

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

soan · 29 Juin 2022

@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

uhhh · 29 Juin 2022

soan à dit:
@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 · 29 Juin 2022

@uhhh

uhhh à dit:
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

XL 2019 appliquer une formule similaire à si sur un tableau

uhhh

XLDnaute Nouveau

soan

Deadpool_CC

XLDnaute Accro

uhhh

XLDnaute Nouveau

Pièces jointes

Deadpool_CC

XLDnaute Accro

uhhh

XLDnaute Nouveau

uhhh

XLDnaute Nouveau

Pièces jointes

Deadpool_CC

XLDnaute Accro

Pièces jointes

uhhh

XLDnaute Nouveau

soan

XLDnaute Barbatruc

Pièces jointes

soan

XLDnaute Barbatruc

Pièces jointes

soan

XLDnaute Barbatruc

Pièces jointes

uhhh

XLDnaute Nouveau

soan

XLDnaute Barbatruc