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

–          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

Share This