Gestion Stock pou Master Excel
Bonjour à toute la communauté,
J'espère que tout le monde se porte bien !
Je suis devant un pb de gestion de stock assez balaise et j'aurai vraiment besoin d'un coup de main histoire de ne pas devenir complètement barjot sous peu...
J'ai une référence de produit qui peut apparaître jusqu'à 25 fois max ds un listing.
Chaque produit est associé à un lot.
La fusion des deux donne un code unique appelé "ID" qui peut être associé à une date d'entrée en stock et une date de sortie de stock.
Un ou pls lots peut être défectueux (surligné en jaune ds le tableau)
J'aimerai identifier le chemin critique, c'est à dire la période la plus longue (en jour) sur des lots en minimisant mes lots défectueux mais à part un travail manuel je n'arrive pas à trouver d'autres solutions...
NB 1 : J'ai 19013 produits et 98285 lignes ds le fichier d'origine
D'où mon pb... qui est bien entendu un pb de temps et de fiabilité ds le résultat.
Merci d'avance pour l'aide apportée.
Sincères salutations,
Djil
Salut,
concrètement, tu veux identifier les périodes les plus longues de quelle manière, avec une formule en F ?
Et la période la plus longue entre quoi et quoi ?
J'ai du mal à comprendre tes exemples : en F17 tu as mis =DATEDIF(E16;D20;"d"), mais quel est le rapport entre les lignes 16 et 20 puisqu'elles concernent des lots différents ?
Hello,
Ds cet exemple, pour le produit 105528, j'ai 9 lots.
Pour expliquer le calcul en F17 : Les stockages des 3 lots 480 , 492 , 498 (lot défectueux) sont couverts en partie ou partiellement pas les lots précédents ou postérieurs.
E16 = date de fin de stock du lot OK 437 = 2023-12-06 07:30. Ca couvre le début du lot 480
et
D20 = Date du début du stock OK du 506 = 2023-12-16 18:43, ça couvre la fin du lot 498
Pour mon calcul de jour de stock de mes lots 480 , 492 , 498, je fais donc basiquement la différence (DateDif) entre ses 2 dates.
Pour automatiser tout ça, je cherche encore mais c'est chaud... mon dernier neurone fume à fond ^^
Ok, je t'avoue que je ne comprends pas encore tout : comment savoir quel lot est défectueux à part qu'ils sont en jaune ? C'est toujours la suite 480, 492, 498 ?
D'ailleurs il n'y a pas de MFC, donc tu as mis en jaune manuellement ?
Il me semble qu'il manque une colonne qui indiquerait quels lots sont défectueux pour simplifier la formule, ainsi qu'une MFC.
Oui, les lots défectueux ont été surlignés en jaune manuellement. C'est tjs les mêmes.
On avance mais ce n'est pas encore très clair ; la formule doit donc donner des résultats uniquement sur les lignes du lot 480 ?
Le but est donc de trouver au-dessus de chaque lot 480 la dernière date de sortie postérieure à la date d'entrée de ce lot ?
Et pour l'autre date à trouver je ne comprends pas : en F8 on voit E10, alors qu'en F17 et F26 c'est une date de la colonne D
"Le but est donc de trouver au-dessus de chaque lot 480 la dernière date de sortie postérieure à la date d'entrée de ce lot ?"
Oui, on peut résumer comme ça. Bien vu ^^
Et ds le même genre il faut pouvoir identifier la premier date hors des lot avec une date légèrement inférieur à la dernière date du dernier lot NOK.
Avec cette nouvelle approche, pour F8, ça devrait être plutôt : =DATEDIF(E7;D11;"d")
Voici un premier jet en matriciel mais je dois affiner la formule qui renvoie bien 13 en F8 mais renvoie 11 et 13 au lieu de 10 et 12 en F17 et F26 ;
=DATEDIF(PETITE.VALEUR(SI(E2:E7>D8;E2:E7);1);GRANDE.VALEUR(SI(D11:D16<E10;D11:D16);1);"d")C'est à mettre en F8 et incrémenter.
Et je dois encore rajouter une condition pour ne rien afficher si la colonne n'affiche pas 480.
Bonjour Doux Réveur et Akam,
Merci pour votre support. Ca m'aide super bien aussi ds la réflexion de la problématique.
Par contre un élément que je n'ai pas précisé :
La liste des lots défaillant peut évoluer et il ne se répète pas tjs ds le même ordre :
Liste complète de ce jour est :
480 ; 486 ; 492 ; 498 ; 504 mais peut devoir en ajouter plein d'autres...
Ds beaucoup de cas on a bien les 480 ; 492 ; 498 mais ds d'autres cas un mix de tout ça allant de 1 lot à la totalité...
Ca fausse un peu la donne... sorry :/
Bonjour à vous !
Là, je ne vois pas. Navré
DjillijD, pourrais-tu nous donner un fichier qui ressemble au cas que tu décris ?
Quand c'est dans un autre ordre, la formule doit quand-même apparaître à côté du premier lot, même si ce n'est pas 480 ?
Si oui ça me semble compliqué, ou alors on devrait mettre la même formule pour les 3 lots concernés (480, 492 et 498) au lieu de juste 480.
Tel quel ça me semble très difficile, mais si tu pouvais rajouter une colonne avec par exemple la lettre D (pour défectueux) à chaque ligne d'un lot défectueux (480, 486, 492, 498 ou 504), alors on pourrait aller chercher chaque lettre D qui n'est pas précédée par un autre D, comme ça on sait que c'est la date de cette ligne-là qu'on doit utiliser pour la formule.
Il ne faut même pas le faire manuellement, il suffit d'avoir une liste des lots défectueux que tu peux toujours mettre à jour et mettre une formule dans la nouvelle colonne (à insérer entre les colonnes B et C par exemple) qui met un D si la cellule en B contient bien une de ces références (et sinon rien).
Du genre =SI(B2=480;"D";""), sauf que ce sera par rapport à la liste de lots et pas juste 480.
Bonjour à vous,
Doux Rêveur, j'y ai pensé mais je me retrouve dans une impasse....
La logique de sélection des dates de la seconde partie du tableau change. Retour à la case départ pour moi.
Je ne laisse pas tomber, j'espère avoir le temps aujourd'hui ou demain.
J'ai besoin d'une précision : concernant la date de sortie postérieure à la date d'entrée du lot, ça veut dire une décalage d'au moins un jour, on ne tient pas compte des heures ?
Parce que je vois qu'en F8 tu as choisi E7 qui contient le 06/12 alors que les cellules au-dessus sont aussi postérieures à D8 mais uniquement en heures, pas en jours (c'est le même jour).
Et pour l'autre date, c'est aussi en jours ou ça peut être quelques minutes avant le même jour ? Et c'est toujours par rapport à la date de sortie de la dernière ligne des lots défectueux, donc par rapport à E10 pour la première série de lots défectueux ?
L'heure n'est pas primordiale pour le résultat. Car la finalité devra se présenter sous la forme d'une moyenne par nb de jours sur le slots défectueux.
Merci encore pour votre support
Ok, mais est-ce que ça peut être le même jour ou pas ? C'est important parce que ça modifie la formule.
Et j'ai remarqué autre chose : dans la cellule F34 de ton 2ème fichier, tu te réfères à D34, donc sur la même ligne alors que tu disais plus haut qu'il fallait chercher une date dans les lignes au-dessus, entre les lots défectueux.
Mais si on se réfère aux lignes du dessus (en-dessous du lot défectueux précédent), il n'y a qu'une date postérieure au 09/12 (de D34), c'est celle de E29, le 27/12, donc après la date de sortie du lot 504 en ligne 38, ce qui renvoie une erreur quand on fait la différence entre les 2 dates.
Il faut donc faire une exception pour ce cas et choisir alors la date de la même ligne, comme tu l'as fait ? Sinon on cherche toujours entre les lots défectueux, que ce soit pour la date de E ou celle de D ?
Et dernière question : je demandais au début s'il fallait trouver "la dernière date de sortie postérieure à la date d'entrée de ce lot" et tu m'avais dit oui. Mais on peut interpréter dernière de 2 manières : la dernière dans l'ordre où c'est écrit dans la colonne ou la dernière dans l'ordre chronologique ? Parce que dans certains cas ça ne renvoie pas la même date. Quand je dis dans la colonne, je veux dire dans la portion de colonne entre les lots défectueux.
J'ai trouvé une solution ce w-e mais j'ai besoin de tes précisions pour adapter la formule.
Ok, mais est-ce que ça peut être le même jour ou pas ?
Oui, ça peut.
dans la cellule F34 de ton 2ème fichier, tu te réfères à D34, donc sur la même ligne alors que tu disais plus haut qu'il fallait chercher une date dans les lignes au-dessus, entre les lots défectueux.
Oui, il y a des cas différent : Il faut la date du premier lot défectueux non couvert par un lot précédent. Si couvert par le lot précédent, on prend la date de fin du lot.
Hello,
alors voilà ce que je te propose ;
La colonne J remplace la colonne F et fait la différence entre les dates que tu cherches. Pour ça on a besoin de 3 autres colonnes :
-en G on numérote les lots défectueux en recommençant à 1 à chaque fois, seul le 1sera gardé pour la suite
-en H on va chercher la date en colonne E
-en I on va chercher la date en colonne D
Formule matricielle en G2 ;
=SI(SOMMEPROD(--(B2=LotsDef)*1)<>0;1+SI(G1="";0;G1);"")matricielle en H2 ;
=SI(G2=1;SI(PETITE.VALEUR(SI(ENT(INDIRECT("E"&GRANDE.VALEUR(SI(ESTNUM($G$1:$G1);LIGNE($G$1:$G1));1)+1):E1)>ENT(D2);INDIRECT("E"&GRANDE.VALEUR(SI(ESTNUM($G$1:$G1);LIGNE($G$1:$G1));1)+1):E1);1)>I2;D2;PETITE.VALEUR(SI(ENT(INDIRECT("E"&GRANDE.VALEUR(SI(ESTNUM($G$1:$G1);LIGNE($G$1:$G1));1)+1):E1)>ENT(D2);INDIRECT("E"&GRANDE.VALEUR(SI(ESTNUM($G$1:$G1);LIGNE($G$1:$G1));1)+1):E1);1));"")matricielle en I2 ;
=SI(G2=1;GRANDE.VALEUR(SI(INDIRECT("D"&PETITE.VALEUR(SI(ESTNUM(G3:G$46)=FAUX;LIGNE(G3:G$46));1)):INDIRECT("D"&PETITE.VALEUR(SI(G3:G$46=1;LIGNE(G3:G$46));1)-1)<INDIRECT("E"&PETITE.VALEUR(SI(ESTNUM(G3:G$46)=FAUX;LIGNE(G3:G$46));1)-1);INDIRECT("D"&PETITE.VALEUR(SI(ESTNUM(G3:G$46)=FAUX;LIGNE(G3:G$46));1)):INDIRECT("D"&PETITE.VALEUR(SI(G3:G$46=1;LIGNE(G3:G$46));1)-1));1);"")Le G$46 c'est pour déterminer la dernière cellule où chercher puisqu'il y a 46 lignes dans ton fichier mais il faudra bien sûr changer ça pour ton vrai fichier.
non matricielle en J2 ;
=SI(ESTNUM(I2);DATEDIF(H2;I2;"d");"")Il y a juste pour le dernier lot que ça ne marche pas parce qu'il n'est suivi d'aucun lot non défectueux donc forcément il n'y a aucune ligne où aller chercher les dates, mais il suffit de rajouter une ligne en-dessous.
Le fichier ;