Microsoft 365 Répartir des quantités restantes en fonction des dates d'expiration

[Magcar]

Bonjour,
Je suis à la recherche d'une formule depuis un moment mais je n'y arrive pas.
Je m'explique brièvement et j'espère pouvoir joindre un fichier excel mais je ne vois pas la comment... (Je suis nouvelle sur ce forum).
J'ai un inventaire d'item. Chaque items est divisé en un nombre variable des lots et date d'expiration.
Par exemple:
Item 154995 100 unités, Lot 1 = 50 unités expirant 2022-05-31, Lot 2 = 35 unités expirant 2023-05-31, Lot 3 = 15 unités expirant 2024-05-31.
Je vend 75 unités de cet item, il me reste donc 25 unités.
On considère que les items vendus en premier sont ceux qui expire en premier. Donc si je vend 75 unités de cet item, les 25 unités restant doivent être réparti sur les lots expirant en dernier, donc Lot 2 = 10 unités et lot 3 = 15 unités.
Je suis à la recherche d'une formule capable de faire cette répartition.
Bref, dans le fichier excel joint, je cherche à calculer les valeurs en bleu, pas exactement les mêmes données que dans mon exemple cependant, mais même principe. Est-ce faisable ?
Je vous remercie pour votre aide !!

 

[JHA]

Bonjour à tous,

A essayer

=SI(SOMME.SI($B$2:$B2;$B2;$E$2:$E2)-RECHERCHEV($B2;$K$2:$M$3;3)<0;0;SI(SOMME.SI($B$2:$B2;$B2;$E$2:$E2)-RECHERCHEV($B2;$K$2:$M$3;3)>$E2;$E2;SOMME.SI($B$2:$B2;$B2;$E$2:$E2)-RECHERCHEV($B2;$K$2:$M$3;3)))

JHA

 

[Magcar]

Bonjour JHA ! Merci pour ton aide ! Ça fonctionne !
Cependant, il faut que les dates soient toujours triées en ordre croissant. Peut-on ajouter un degré de difficulté en tenant compte des dates ? Supposons que les dates ne sont pas en ordre, une formule pour respecter cette contrainte est-elle possible ? Ce tableau est très manipulé, des lignes sont régulièrement ajoutées et il se pourrait que les dates ne soient plus en ordre croissant un moment donné. Merci encore pour ton aide !

 

[JHA]

Bonjour à tous,

Si tu as mis tes données sous mode tableau, il est facile de faire un tri sur les dates pour avoir des résultats cohérents.
Tu peux également mettre un bouton pour le filtre.

JHA

 

[mapomme]

Bonjour à tous ,

Une solution par VBA. L'ordre des lignes dans le tableau n'a pas d'importance.
Cliquer sur le bouton "Les soldes " de la feuille "Feuil1".

Le code est dans le module associé à la feuille "Feuil1".

Sub LesSoldes()
Dim t, tdep, i&, k&, ref, qte

If Me.FilterMode Then Me.ShowAllData
With Intersect(Columns("a:e"), Range("a1").CurrentRegion)
   .Sort key1:=Range("a1"), order1:=xlAscending, Header:=xlYes, _
         key2:=Range("b1"), order2:=xlAscending, _
         key3:=Range("d1"), order2:=xlAscending
   t = Intersect(Columns("a:e"), Range("a1").CurrentRegion)
   ReDim Preserve t(1 To UBound(t), 1 To UBound(t, 2) + 1)
End With
tdep = Intersect(Columns("i:m"), Range("i1").CurrentRegion)
t(1, UBound(t, 2)) = "Solde par lot"
For i = 2 To UBound(t)
   t(i, 6) = t(i, 5)
   If Join(Array(t(i, 1), t(i, 2)), "\") <> ref Then
      ref = Join(Array(t(i, 1), t(i, 2)), "\"): qte = 0
      For k = 2 To UBound(tdep)
         If Join(Array(tdep(k, 1), tdep(k, 2)), "\") = ref Then
            qte = tdep(k, 4)
            Exit For
         End If
      Next k
   End If
   If k <= UBound(tdep) And qte <> 0 Then
      If qte <= t(i, 5) Then
         t(i, 6) = t(i, 5) - qte
         qte = 0
      Else
         qte = qte - t(i, 5)
         t(i, 6) = 0
      End If
   End If
Next i
Range("a1").Resize(UBound(t), UBound(t, 2)) = t
End Sub

 

[Magcar]

Bonjour

Bonjour à tous ,

Une solution par VBA. L'ordre des lignes dans le tableau n'a pas d'importance.
Cliquer sur le bouton "Les soldes " de la feuille "Feuil1".

Le code est dans le module associé à la feuille "Feuil1".

Sub LesSoldes()
Dim t, tdep, i&, k&, ref, qte

If Me.FilterMode Then Me.ShowAllData
With Intersect(Columns("a:e"), Range("a1").CurrentRegion)
   .Sort key1:=Range("a1"), order1:=xlAscending, Header:=xlYes, _
         key2:=Range("b1"), order2:=xlAscending, _
         key3:=Range("d1"), order2:=xlAscending
   t = Intersect(Columns("a:e"), Range("a1").CurrentRegion)
   ReDim Preserve t(1 To UBound(t), 1 To UBound(t, 2) + 1)
End With
tdep = Intersect(Columns("i:m"), Range("i1").CurrentRegion)
t(1, UBound(t, 2)) = "Solde par lot"
For i = 2 To UBound(t)
   t(i, 6) = t(i, 5)
   If Join(Array(t(i, 1), t(i, 2)), "\") <> ref Then
      ref = Join(Array(t(i, 1), t(i, 2)), "\"): qte = 0
      For k = 2 To UBound(tdep)
         If Join(Array(tdep(k, 1), tdep(k, 2)), "\") = ref Then
            qte = tdep(k, 4)
            Exit For
         End If
      Next k
   End If
   If k <= UBound(tdep) And qte <> 0 Then
      If qte <= t(i, 5) Then
         t(i, 6) = t(i, 5) - qte
         qte = 0
      Else
         qte = qte - t(i, 5)
         t(i, 6) = 0
      End If
   End If
Next i
Range("a1").Resize(UBound(t), UBound(t, 2)) = t
End Sub

 

[Magcar]

Bonjour ma pomme Mes connaissances en VBA sont malheureusement très limitées, j'ai donc de la difficulté à reproduire votre solution dans mon vrai tableau . Mais merci quand même pour votre réponse !

 

[Magcar]

J'ai opté pour la solution de JHA finalement. Merci