Calcul rupture de stock

[Shaak33]

Bonjour à tous,

Je me permets de solliciter l'aide du forum après plusieurs heures de recherches infructueuses.
Je pense que la tâche n'est pas compliqué mais je ne vois pas comment faire...

Je travaille actuellement sur un fichier de stock qui se présente comme dans l'exemple ci-joint.
Pour plusieurs produits (ici PRODUIT1 et PRODUIT 2) nous avons le stock par taille de la semaine 01 à 25.
Pour chaque ligne de taille il faudrait que dans la colonne "Semaine rupture" s'affiche la première semaine où le produit est en rupture en ne prenant pas en compte les stocks égaux à 0 en début de pèriode (le produit n'étant pas encore arrivé).
Le critère de rupture est un stock infèrieur ou égal à 10 PCE.

J'ai détaillé en exemple ce qui devrait apparaitre pour le PRODUIT 1 avec une bonne formule.

La difficulté pour moi vient du fait que les produits n'étant pas toujours en stock dès la semaines 1, mes formules m'affichent "S01".

J'espère avoir donné tous les détails, toute aide serait la bienvenue
D'avance merci beaucoup,

Shaak33

 

[Grand Chaman Excel]

Re : Calcul rupture de stock

Bonjour Shaakk33 et bienvenue sur le forum,

Voici une proposition avec une formule personnalisée (macro).
Voir dans la colonne AC du fichier joint pour un exemple.
Tu spécifies :la plage des semaine, la plage des quantité, la qté de rupture.

Function SemaineRupture(PlageSemaine As Range, PlageStock As Range, QtéRupture As Integer) As String
    Dim c As Range
    Dim i As Integer
    Dim bValid As Boolean

    If PlageSemaine.Rows.Count <> 1 Then Exit Function
    If PlageStock.Rows.Count <> 1 Then Exit Function
    If PlageSemaine.Columns.Count <> PlageStock.Columns.Count Then Exit Function
    
    bValid = False
    i = 1
    For Each c In PlageStock
        If c <> 0 Then bValid = True
        If c <= QtéRupture And bValid Then
            SemaineRupture = WorksheetFunction.Index(PlageSemaine, i)
            Exit Function
        End If
        i = i + 1
    Next c
    SemaineRupture = "Pas de rupture"

End Function

A+

 

[Shaak33]

Re : Calcul rupture de stock

Bonsoir Grand Chaman Excel,

C'est tout simplement parfait. Ce n'était pas si simple que ça finalement, je vais essayer de comprendre le code
Un grand merci !

Bonne soirée

Shaak33

 

[mapomme]

Re : Calcul rupture de stock

Bonsoir Shaak33,

Un essai avec des formules:

Formule matricielle en AB12 à copier vers le bas:

=SIERREUR(EQUIV(VRAI;SI(COLONNE(C12:AA12)>=PETITE.VALEUR((SI(C12:AA12<>0;COLONNE(C12:AA12)));1);C12:AA12<=10);0);"pas de rupture")

et pour la MFC la formule:

=ET(NB.SI($C12:C12;"<>0")>0;C12<=10)

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

 

[Shaak33]

Re : Calcul rupture de stock

Bonsoir mapomme !

Merci beaucoup pour ton aide, la formule donne également le résultat que je cherche Je ne connaissais pas la formule Petite.valeur.
Pour aller plus loin, y a-t-il un moyen de modifier la formule ou le code VBA de Grand Chaman Excel pour rajouter une exception: ne pas prendre en compte la première semaine de rupture si celle-ci est isolée ?
Dans ton exemple mapomme: pour le PRODUIT 2 taille 44 par exemple, le résultat serait S19 et non S13 ? (Car la rupture semaine 7 est isolée et donc pas significative)

Bon week end à tous et encore merci !

 

[mapomme]

Re : Calcul rupture de stock

Bonsoir Shaak33,

[...] Pour aller plus loin, y a-t-il un moyen de modifier la formule ou le code VBA de Grand Chaman Excel pour rajouter une exception: ne pas prendre en compte la première semaine de rupture si celle-ci est isolée ? [...]

Une tentative avec la formule matricielle suivante en AB12 à copier vers le bas:

=SIERREUR(EQUIV(1;(C12:Z12<=10)*(D12:AA12<=10)*(COLONNE(C12:Z12)>=PETITE.VALEUR( (SI(C12:AA12<>0;COLONNE(C12:AA12)));1));0);"Pas de rupture de plus d'une semaine")

nb: formule à utiliser pour Excel 2007, 2010

 

[mapomme]

Re : Calcul rupture de stock

(re)Bonsoir Shaak33,

La formule matricielle précédente recherchait le premier couple de semaines consécutives avec un stock inférieur ou égal à 10 (en excluant les semaines à stock 0 au début de la période).

La formule matricielle suivante recherche la première semaine à stock inférieur ou égal à 10. Si cette semaine est isolée, alors la formule recherche la première semaine suivante à stock <= 10 (toujours en excluant les semaines à stock 0 au début de la période).

=SIERREUR(SI(INDEX(C12:AA12;-1+PETITE.VALEUR(SI((C12:AA12<=10)*(COLONNE(C12:AA12)>=PETITE.VALEUR((SI(C12:AA12<>0;COLONNE(C12:AA12)));1))=0;"";COLONNE(C12:AA12));1))>10;-2+PETITE.VALEUR(SI((C12:AA12<=10)*(COLONNE(C12:AA12)>=PETITE.VALEUR((SI(C12:AA12<>0; COLONNE(C12:AA12)));1)) =0;"";COLONNE(C12:AA12));2);-2+PETITE.VALEUR(SI((C12:AA12<=10)* (COLONNE(C12:AA12)>= PETITE.VALEUR((SI( C12:AA12<>0;COLONNE(C12:AA12)));1))=0;"";COLONNE(C12:AA12));1));"Pas de rupture sauf peut-être une seule semaine")

 

[Shaak33]

Re : Calcul rupture de stock

Bonjour mapomme,

Encore une fois merci, la formule de ton exemple (la deuxième) est nikel seulement j'essaye de l'adapter à mon vrai fichier qui a une colonne de plus en réalité avant la désignation produit (la ref produit). J'ai essayé en bidouillant les -1/+1 de ta formule mais ça ne donne rien.
Saurais-tu me dire quoi modifier pour l'adapter avec cette colonne en plus ? (Si ce n'est pas trop compliqué)

Merci pour tout,
Bonne fin d'aprem,

Shaak33

 

[mapomme]

Re : Calcul rupture de stock

Bonsoir Shaak33,

[...] l'adapter à mon vrai fichier qui a une colonne de plus en réalité avant la désignation produit (la ref produit). J'ai essayé en bidouillant les -1/+1 de ta formule mais ça ne donne rien.
Saurais-tu me dire quoi modifier pour l'adapter avec cette colonne en plus ? [...]

Voilà la formule matricielle après avoir inséré une colonne avant la colonne produit:

=SIERREUR(SI(INDEX(D12:AB12;-2+PETITE.VALEUR(SI((D12:AB12<=10)*(COLONNE(D12:AB12)>=PETITE.VALEUR((SI(D12:AB12<>0;COLONNE(D12:AB12)));1))=0;"";COLONNE(D12:AB12));1))>10;-3+PETITE.VALEUR(SI((D12:AB12<=10)*(COLONNE(D12:AB12)>=PETITE.VALEUR((SI(D12:AB12<>0;COLONNE(D12:AB12)));1))=0;"";COLONNE(D12:AB12));2);-3+PETITE.VALEUR(SI((D12:AB12<=10)*(COLONNE(D12:AB12)>=PETITE.VALEUR((SI(D12:AB12<>0;COLONNE(D12:AB12)));1))=0;"";COLONNE(D12:AB12));1));"Pas de rupture sauf peut-être une seule semaine")

les -2+ sont devenus -3+ et les -1+ sont devenus -2+.