Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2019 appliquer une formule similaire à si sur un tableau

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

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

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

Dernière édition:
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

@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

Bonjour Soan !
Je viens de faire tourner le programme, il marche très bien !
Merci infiniment ! 😁😁
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…