(Resolu) Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

[Lillisa]

Bonjour à tous!

Déjà : mille merci à toute la communauté car si c'est la première fois que je poste en revanche j'ai très souvent parcouru le forum pour trouver des solutions à mes petits soucis d'excel et jusqu'à maintenant j'avais toujours trouvé mon bonheur

Voici mon problème aujourd'hui :

Pour commencer, je suis une débutante d'excel. Je sais utiliser des formules simples (nbval, somme.si, recherche...) et des petites mises en forme simples (listes, validations de données, mise en forme conditionnelle...)
Par contre je n'ai jamais fait de formulaire, de tableaux croisés dynamiques etc

Je travaille dans une TPE qui fait de la location de matériel. Il n'y a pas une activité énorme donc nous n'avons pas de logiciel de gestion (les devis et facturation sont gérés avec excel) et nous n'avons pas non plus, donc, d'outil pour gérer les stocks. Quand on nous demande si on a un produit X dispo à la location de telle date à telle date et bien c'est le système D : on connait notre stock et on regarde les locations à venir pour répondre... Pas très pratique et source d'erreur!

J'ai donc décidé d'essayer de mettre en place un outil "simple" (d'utilisation) pour connaitre l'état de notre stock à une date donnée.

En gros j'ai :
un onglet "produit" : ref, nom, stock
un onglet "locations" : ref, nom, stock, client, quantité louée, du (date), au (date)
et j'aimerais avoir :
un onglet "stock à date" : où j'aurais : ref (du produit), date (date à laquelle je souhaiterais savoir si j'ai la ref en question en stock), et stock disponible (à cette date).

J'ai un tableau "exemple" ci-joint pour vous faire voir ce que j'essaie de faire. Je sais, je n'ai pas de piste de solution à mon problème... mais ce n'est pas faute d'avoir longuement cherché. Il y a plusieurs sujets qui abordent le sujet sous différents angles mais : 1. ce n'est pas exactement ce que je souhaite faire et 2. comme je n'arrive pas à comprendre les solutions données aux diverses situations, je n'arrive pas à les appliquer à mon cas...

si quelqu'un peut me donner des pistes je vous serais mille fois reconnaissante!! Mon but est aussi d'apprendre en essayant.

bonne soirée à tous,

 

[Modeste]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Bonsoir Lillisa et bienvenue!

Pour autant que j'aie bien compris, que donnerait, en C2 de la feuille "DISPO":

=RECHERCHEV($B2;'INFO LOCATION'!$A$2:$C$15;3;FAUX)-SOMMEPROD(('INFO LOCATION'!$A$2:$A$15=$B2)*('INFO LOCATION'!$F$2:$F$15<=$A2)*('INFO LOCATION'!$G$2:$G$15>=$A2)*('INFO LOCATION'!$E$2:$E$15))

Peut-être est-ce dans tes projets, mais il me semble que j'essaierais de masquer les messages d'erreurs en feuille "INFO LOCATION". De même, je m'intéresserais de près au 4e argument de la fonction RechercheV, dans cette même feuille. Enfin, les plages nommées "dynamiques" pourraient être intéressantes pour ta plage REF.

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Bonsoir Lillisa et bienvenue!

Pour autant que j'aie bien compris, que donnerait, en C2 de la feuille "DISPO":

=RECHERCHEV($B2;'INFO LOCATION'!$A$2:$C$15;3;FAUX)-SOMMEPROD(('INFO LOCATION'!$A$2:$A$15=$B2)*('INFO LOCATION'!$F$2:$F$15<=$A2)*('INFO LOCATION'!$G$2:$G$15>=$A2)*('INFO LOCATION'!$E$2:$E$15))

Peut-être est-ce dans tes projets, mais il me semble que j'essaierais de masquer les messages d'erreurs en feuille "INFO LOCATION". De même, je m'intéresserais de près au 4e argument de la fonction RechercheV, dans cette même feuille. Enfin, les plages nommées "dynamiques" pourraient être intéressantes pour ta plage REF.

Merci!! Cela fonctionne parfaitement à priori.
Par contre je ne comprend pas la formule. Est-ce que vous pourriez me l'expliquer? ça me permettrait de pouvoir l'appliquer à d'autres situation et surtout de comprendre mon tableau si je dois le faire évoluer
Vous parlez de masquez les messages d'erreur : effectivement! j'ai glissé la formule pour les futures "entrées" mais je ne sais pas comment cacher cela en attendant d'entrer une nouvelle location...
Vous dites aussi "je m'intéresserais de près au 4e argument de la fonction RechercheV, dans cette même feuille." Le 4e argument, càd la valeur proche? Je ne sais pas à quoi cela sert vraiment.
Enfin : "Enfin, les plages nommées "dynamiques" pourraient être intéressantes pour ta plage REF." Alors ça je ne connais pas. Je vais faire quelques recherches pour mettre ça en place!
Encore mille merci! C'est vraiment génial de pouvoir trouver de l'aide!

 

[mapomme]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Bonjour Lillisa,
Un petit essai avec une autre présentation.
Le tableau des produits restant en stock est sur une année glissante (choisir lr produit en cellule A1)
Une réservation peut se faire à partir du tableau précédent (Feuille 'Dispo')
Les noms sont dynamiques.
Des MFC sont utilisées pour visualiser les produits devant être rendus à la date du jour et le nombre de jours de retard.

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Whahou tout ça est impressionnant. Malheureusement ça ne marche pas : "impossible de trouver la macro" Peut-être qu'il faut que je fasse une manip particulière? ou peut-être un problème de compatibilité (je suis sur mac, avec excel 2008).
Sinon j'aimerais vraiment être capable de faire ce genre de tableau mais pour le moment j'ai un niveau limité, je ne maitrise absolument pas les macros.
En tout cas merci beaucoup! Je suis toujours impressionnée par la maitrise de tous les membres de ce forum!

 

[mapomme]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Bonjour Lillisa,

Je suis confus. Je n'avais pas fait attention au fait que tu sois sur Mac avec Excel 2008. Il m'a semblé lire qu'Excel 2008 ne comportait pas de macros. Quelqu'un d'autre confirmera peut-être. Mis à par cela, le reste fonctionne-t il ?

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Oui ça marche!
Il va falloir que je transpose cela à mon tableau complet (env 250 references) ce qui n'est pas gagné vu que je ne comprend pas forcément tout dans ce tableau top-niveau.
Une chose que je ne comprend pas : pourquoi cette colonne Ref+prod ? est-ce que cela ne fonctionnerait pas en entrant dans le premier onglet "dispo" en A1 juste la référence du produit? J'aimerais juste comprendre le "lien" qui existe entre mon produit, cette "Ref+Prod" et la cellule A1...
Merci d'avance!!

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Ah et dans mon excel il y a bien dans "Outils" -> Macro et Macro complémentaire...

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Du coup j'ai essayé d'enlever les macros (message d'excel : les macros ne peuvent pas être lus avec excel 2008, cliquez sur ouvrir et supprimer pour les supprimer....) et le tableau marche bien! J'ai essayé de rentrer une nouvelle ref pour voir si ça la prenait en compte : c'est magique, ça marche. j'ai ajouté des "listes" pour ref+produit car sinon c'est un peu compliqué de l'écrire sans erreur (surtout qu'après, avec mes vrais produits, je vais avoir des noms de produits bien plus longs et compliqués). C'est absolument génial on voit tout de suite quand le produit est loué et jusqu'à quand; la quantité dispo à toute date, s'il y a du retard pour le retour d'une loc, bref c'est absolument parfait ! Il ne me reste plus qu'à analyser les formules pour bien les comprendre et pouvoir refaire cela toute seule... Encore merci!

Sinon : comment est-il possible qu'une version d'excel ne lisent pas les macros? C'est une fonctionnalité très utilisé apparemment?

 

[mapomme]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

(re)Bonjour,

En recherchant sur le NET, il semble bien qu'Office Mac 2008 ne soit pas compatible avec le VBA. Ne connaissant pas Office pour Mac, j'ai adapté le fichier à Excel 2003 PC sans la macro en vba (pas de possibilité de réserver à partir de la feuille 'Dispo'). Je pense qu'Excel 2008 devrait savoir convertir un fichier Excel 2003 PC en Excel Mac 2008. J'ai aussi redéfini certains noms dynamiques. J'ai ziper le fichier car en version 2003, il a un peu gonflé et dépasse la taille admise par le forum.


EDIT : je n'ai vu ton message qu'après le mien.

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Merci beaucoup!
c'est très bien comme ça, ça va vraiment nous faciliter la vie! Je vais tenter de rentrer ma vraie base de données produits pour voir et je vous tiens au courant.
Bonne journée!

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Re-Bonjour,
Pouvez-vous m'expliquer comment fonctionne cette formule (ce qu'elle signifie en fait) j'essaie de la comprendre mais je n'y arrive pas ... (la formule qui calcule la quantité en stock à une date donnée dans le calendrier) :

=SI(C3="";"";$A$9-SOMMEPROD( (loc_REF=$A$6) * ( loc_DU<=C3) *( (loc_AU="")+ (loc_AU>=C3)) * (loc_Qte)))

c'est à partir de SOMMEPROD que je ne suis plus

merci beaucoup d'avance!!

 

[mapomme]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

Bonsoir,

=SI(C3="";"";$A$9-SOMMEPROD( (loc_REF=$A$6) * ( loc_DU<=C3) *( (loc_AU="")+ (loc_AU>=C3)) * (loc_Qte)))

Tout d'abord, on a défini des noms de zones dynamiques pour faciliter la lisibilité des formules.

Prenons le nom loc_Ref:
Sa définition est: =DECALER('INFO LOCATION'!$B$2;0;0;NBVAL('INFO LOCATION'!$A$1:$A$1000)-1;1)
(que vous pouvez voir dans la boite de dialogue de gestion des noms)

NBVAL('INFO LOCATION'!$A$1:$A$1000) compte le nombre de valeurs dans la zone $A$1:$A$1000 de la feuille 'INFO_LOCATION). J'ai supposé que vous n'aviez pas plus de 999 lignes de location.

On compte en fait le nombre de lignes de location référencées, en-tête compris. Ce nombre (diminué de 1 car la ligne de l'en-tête ne nous intéresse pas) représente le nombre de lignes du tableau des locations.

On va partir de la cellule $B$2 pour définir la zone des références. Cette zone est décalée de 0 ligne par rapport à $B$2, de 0 colonne par rapport à $B$2 (pour retrouver la cellule $B$2 !)

La taille de cette zone en hauteur est égale au nombre de lignes de location et sa largeur est d'1 colonne.

On utilise la fonction d'Excel DECALER pour définir cette zone:
DECALER(cellule de base ; n1 ligne vers le bas ; n2 colonne vers la droite ; hauteur ; largeur)
d'où
DECALER($B$2 ; 0 ; 0 ; hauteur, 1)
d'où
DECALER($B$2 ; 0 ; 0 ; NBVAL('INFO LOCATION'!$A$1:$A$1000)-1 ; 1)
en rajoutant le nom de la feuille où se trouve $B$2, on trouve:
DECALER('INFO LOCATION'!$B$2$B$2 ; 0 ; 0 ; NBVAL('INFO LOCATION'!$A$1:$A$1000)-1 ; 1)

* les références sont des références absolues d'où la présence des $

Les formules sont analogues pour les autres noms; on ne modifie que la cellule de base.
loc_REF ==> liste des références des produits loués.
loc_DU ==> liste des dates de début de la location.
loc_AU ==> liste des dates de fin (théorique) de la location.
loc_QTE ==> liste des quantités de produits loués


La formule sommeprod:

1) une multiplication dans un sommeprod correspond à l'opérateur ET
2) une addition dans un sommeprod correspond à l'opérateur OU

Le principe est le suivant: soit un jour J0 (ici c'est la valeur de C3)

Un produit est sorti du stock s'il est sorti en location et s'il n'a pas encore été rendu au jour J0.

Pour chaque ligne de INFO_LOCATION, il faut vérifier la condition:
( date de sortie est avant J0 ET Pas encore rendu à J0 )
ce qui se traduit par:
( loc_DU<= J0 ET loc_AU n'est pas renseigné )
d'où
( loc_DU<= J0 ET loc_AU ="" )
d'où
( loc_DU<= C3 ET loc_AU ="" )

Il y a un autre cas pour lequel un produit est sorti au jour J0:
Un produit est sorti du stock s'il est parti en location avant le jour J0 et si sa date prévue de retour est supérieur à J0
ce qui se traduit par:
( date de sortie est avant J0 ET date prévue de retour est après J0 )
d'où
( loc_DU<= J0 ET loc_AU >= J0 )
d'où
( loc_DU<= C3 ET loc_AU >= C3 )

Ces deux conditions peuvent s'énoncer:
un produit est sorti si: sa date de début de location est avant J0 et (s'il n'est pas encore rendu ou bien si sa date de retour prévu est après J0)

ce qui peut s'écrire avec sommeprod et les règles 1) et 2)
sommeprod( (début location <= J0) et (pas rendu ou date fin location >= J0) )
sommeprod( (loc_DU<= J0) et ( (loc_AU ="") ou (loc_AU >= J0) ) )
ou bien encore:
sommeprod( (loc_DU<= C3) * ( (loc_AU ="") + (loc_AU >= C3) ) )

A ce stade, il manque une condition: nous voulons vérifier ces conditions que pour un type de produit donné repéré par sa référence (ici en cellule $A$6) .
Cette condition s'exprime par:
( les références des produits doivent être égales à la référence du produit considéré )
d'où
( loc_REF doivent être égales à $A$6) d'où (loc_REF=$A$6)

Il faut donc ajouter cette condition sous la forme d'un produit car cette condition doit être vérifiée en plus des deux précédentes d'où:
SOMMEPROD( (loc_REF=$A$6) * ( loc_DU<=C3) *( (loc_AU="")+ (loc_AU>=C3)) )
A ce stade sommeprod est égal à une succession de VRAI et de FAUX (autant que de lignes de location)
SOMMEPROD(VRAI;FAUX;FAUX;VRAI;...)

Mais ce qui nous intéresse n'est pas de savoir si cette condition est Vrai ou Fausse pour chacune des lignes des produits en location mais c'est bien de connaître la quantité du produit qui est hors stock.

Lorsqu'on multiplie une valeur égale à VRAI par un nombre, on obtient ce nombre (comme si VRAI était équivalent à 1)
Lorsqu'on multiplie une valeur égale à FAUX par un nombre, on obtient 0 (comme si FAUX était équivalent à 0)

Il faut donc rajouter pour chaque condition la quantité louée du produit. Si le produit est sorti (VRAI), on additionnera la quantité louée, si le produit n'est pas sorti (FAUX) , on retrouvera 0. Ce qui donne:
SOMMEPROD( (loc_REF=$A$6) * ( loc_DU<=C3) *( (loc_AU="")+ (loc_AU>=C3)) * (loc_Qte))
A ce stade le sommeprod est égal à une succession de quantités sorties et de 0 (autant que de lignes de location):SOMMEPROD(5;0;0;3;...).
La somme nous donne la quantité du produit considéré qui est en location.

On soustrait cette somme du nombre de ce produit que le magasin possède ($A$9) et on a le stock disponible:
$A$9-SOMMEPROD( (loc_REF=$A$6) * ( loc_DU<=C3) *( (loc_AU="")+ (loc_AU>=C3)) * (loc_Qte))

NB: suivant les règles de location (on rend un produit le jour prévu avant 12h et on loue un produit après 12h, il faut remplacer les opérateurs <= ou >= par < ou >.
Dans le cas présent, on considère qu'un produit n'est plus disponible le jour prévu de location et n'est pas encore disponible le jour prévu de retour)

On pourrait améliorer la chose en considérant non pas les dates prévues de retour mais en rajoutant dans les sorties les produits qui devraient être rentrés à J0 mais qui ne le sont pas encore à J0 (retard de retour) mais je n'en ai pas le courage...

 

[Lillisa]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

merci merci merci!
Je vais étudier tout ça attentivement! Je comprend la logique de la fonction si, et de la fonction sommeprod, il me reste cependant à appréhender la fonction "DECALER" que je ne connaissais pas du tout, et à trouver cette boite de dialogue de gestion des noms que je ne trouve pas... (je me doutais bien que "loc_DU" par exemple représentait la plage "loué du" mais impossible de trouver le nom de la plage ni la formule où cette plage est définie (où et comment peut-on modifier cette plage par exemple?).
encore merci et bonne soirée!


EDIT : j'ai trouvé la boite de dialogue : Insertion -> Nom -> Definir (pour excel 2008 sur mac)

 

[mapomme]

Re : Créer un outil pour connaitre l'état du stock d'un produit à une date donnée

(rere)Bonsoir,

Je ne peux guère vous aider à trouver cette boite de dialogue car je ne ne connais pas Excel pour MAC