Menu haut new

Suivi Stock et fiche de stock avec Excel

 BonjourAujourd’hui, j’aimerai m’attaquer au suivi du stock dans Excel : fiche de stock, stock de minimum, stock de sécurité… L’objectif de ce tutoriel est de mettre en place un outil qui permet :

–          D’entrer les produits en stock avec leurs caractéristiques : nom, stock initial, stock de sécurité, stock minimum

–          De suivre toutes les entrées et sorties de stock pour tous les produits;

–          D’éditer les fiches de stocks

On verra que c’est plutôt simple d’utiliser Excel pour résoudre une gestion simple des stocks dans Excel. Avec un minimum de formule (une seule) et un tableau croisé dynamique on aura la solution dans Excel. He oui, Excel est assez intéressant !

1.       Liste des produits :

Dans le nouveau classeur que vous avez créé, dans une des feuilles, il faut juste créer un tableau simple avec des entêtes correspondant:

Nommer la liste de produits :

–          Sélectionner B4:N14 ;

–          Dans la barre des noms écrire : Liste_produit

 

2.       Suivi des Entrées et Sorties de stock:

Pour suivre les entrées et sortie de stock, j’opte pour la mise en place d’un tableau avec les entêtes :

–          Date : la date de l’entrée en stock ;

–          Réf : la référence (le numéro du Bon par exemple) ;

–          Type : Entrée ou Sortie;

–          Produit: le produit qui est mouvementé;

–          Qté: la quantité mouvementée

2.       Suivi des Entrées et Sorties de stock:

Pour suivre les entrées et sortie de stock, j’opte pour la mise en place d’un tableau avec les entêtes :

–          Date : la date de l’entrée en stock ;

–          Réf : la référence (le numéro du Bon par exemple) ;

–          Type : Entrée ou Sortie;

–          Produit: le produit qui est mouvementé;

–          Qté: la quantité mouvementée

Insertion de quelques listes déroulantes:

Pour insérer une liste déroulante dans la colonne de type :

–          Sélectionner la colonne de type

–          Dans le ruban, Données/Outils de données/Validation de données

–          Dans l’onglet Option choisir Liste dans autoriser et dans Source écrire: « Entrée »; »Sortie »

 

Pour insérer une liste déroulante dans la colonne Produit :

–          Sélectionner la colonne de type

–          Dans le ruban, Données/Outils de données/Validation de données

–          Dans l’onglet Option choisir Liste dans autoriser et dans Source écrire: =Liste_produit

3- Créer la fiche de stock:

Pour créer la fiche de stock, j’ai choisi d’associé un tableau croisé dynamique et une formule

–          Se mettre sur une cellule du tableau de suivi de stock ;

–          Dans le ruban, Insertion/Tableaux/Tableau croisé dynamique

–          Valider dans une nouvelle feuille de calcul

Pour les champs du tableau croisé dynamique, choisir:

–          Date en Etiquette de ligne ;

–          Type en Etiquette de colonne ;

–          Produit en Filtre du Rapport ;

–          Qte en somme des valeurs

Supprimer la colonne du total général:

Dans le nouveau menu Outils de tableau croisé dynamique, Création/Disposition/Totaux généraux : Désactivé pour les lignes et colonnes

Pour compléter notre fiche de stock, on doit rajouter par une colonne solde et choisir les produits dans le filtre :

–          Choisir dans filtre de produit un produit, par exemple produit1

–          Retrouver le solde initial du produit choisi : se mettre sur D4 et écrire =RECHERCHEV(C3;Feuil1!$B$4:$E$14;2;0) pour des précisions sur la fonction RECHERCHEV, clique ici  ou ici

–          Mettre une formule pour calculer le solde à partir du tableau croisé dynamique:

·         En E6, écrire Solde

·         En E7 écrire la formule en vous aidant de la souris :

o   cliquer sur la cellule D3, ajouter un plus ;

o   cliquer sur la cellule C7, ajouter un moins ;

o   cliquer sur la cellule D7

La formule en E7 est :

=$D$3+LIREDONNEESTABCROISDYNAMIQUE(« Qte »;$B$5; »Date »;DATE(2011;1;5); »Type »; »Entrée »)-LIREDONNEESTABCROISDYNAMIQUE(« Qte »;$B$5; »Date »;DATE(2011;1;5); »Type »; »Sortie »)

–          En E8 on va mettre une formule un peu différente:

·         Se mettre sur E8

=SI(B8= » »; » »;E7+LIREDONNEESTABCROISDYNAMIQUE(« Qte »;$B$5; »Date »;B8; »Type »; »Entrée »)-LIREDONNEESTABCROISDYNAMIQUE(« Qte »;$B$5; »Date »;B8; »Type »; »Sortie »))

Pour l’écrire utiliser la même technique de la souris, mais surtout n’oublier pas de remplacer DATE(2011;1;7) par B8. Cette formule doit être incrémentée jusqu’à la ligne 1000 par exemple.

La fonction SI() est là juste pour s’assurer qu’il y a bel et bien des entrées.

Dans le prochain article on verra comment aller plus loin avec le choix de la période de la fiche de stock dans le tableau croisé dynamique, et les mises en forme conditionnelles pour améliorer notre outil.

Le téléchargement du fichier se fait  dans Ressources/Téléchargements/Suivi stock et fiche stock sous la categorie Gestion Stock avec Excel

, ,

11 Responses to Suivi Stock et fiche de stock avec Excel

  1. k 22 décembre 2011 at 0 h 00 min #

    Stephane,
    On dirait que ce n’est pas un fichier Excel. Priere de revoir.
    Cdlts

  2. TSS 22 décembre 2011 at 0 h 00 min #

    K, c’est un fichier Excel 2007.

  3. Pierre67 27 mars 2012 at 0 h 00 min #

    Bonjour

    j’ai excel 2007 mais comment ouvrir ce tableau ?
    Cordialement

  4. Pierre67 28 mars 2012 at 0 h 00 min #

    bonjour

    j’ai eu un mail m’indiquant que j’avais une réponse en suivant un lien mais je n’arrive pas à trouver cette réponse
    Cordialement

  5. Abderhamane Ben Mohamed Abdelkrim 17 octobre 2012 at 0 h 00 min #

    Super mais comment faire pour télécharger le fichier

  6. Ciahell 11 janvier 2013 at 0 h 00 min #

    Bonjour, juste pour vous dire j’ai applique les recommandations données mais je ne réussir pas comment au niveau du TCD, pour que les données sortent comme dans l’exemple à savoir: « date »; « sortie »; entrée »; etc… et comment appliquer la formule du solde?
    Cordialemment;
    Ciahell.

  7. Sarious 15 février 2013 at 0 h 00 min #

    Bonjour,
    J’ai suivi les étapes décrites ci-dessus et j’ai un souci. Pour passer à l’étape « Pour compléter notre fiche de stock… », la case produit est vide.
    Je tiens à préciser que je suis novice et assez fier d’avoir réussi les premières étapes grâce aux explications très claires.
    Je peux transmettre le fichier pour que quelqu’un me vienne en aide et si il y a des réponses, pouvez-vous les formulées sans trop de dialectes technico-excel!!! :-)
    D’avance merci

  8. spam 6 novembre 2013 at 17 h 55 min #

    cool

  9. emilie 28 novembre 2013 at 18 h 16 min #

    bonjour, j’essaie de terminer mon tableau de stock afin de savoir le stock qui reste à une même date mais impossible de faire la dernière formule car je n’arrive pas à comprendre à quoi correspond vos cellules et de voir si elles correspondent aux miennes. Est ce que cela vous serait possible de faire la formule en la mettant en image afin que je puisse voir les cellules concernées. je vous remercie par avance.
    Cordialement

  10. amel 2 décembre 2013 at 11 h 08 min #

    Svp je veux plus d’informations (explication) sur la deuxième formule E7 ET E8

  11. KEITA MAMOUDOU 18 octobre 2014 at 20 h 31 min #

    Bonjour Madame/Monsieur,

    Prière de bien vouloir m’apporter votre aide en me proposant sur Excel un modèle de tableau de suivi de gestion des stocks matériels et outillages,un tableau de suivi de gestion des stocks carburant véhicules et autres matériels pour une entreprise de bâtiment.

    cordialement

Laisser un commentaire

Stephane TSS General Manager
Cher visiteur, laissez votre message (question-commentaire...) ci-dessous! NB: Précisez l'outil relatif à votre message.
Envoyer