Suivi Stock Et Fiche De Stock Avec Excel 2t4o4s

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 Suivi Stock et fiche de stock avec Excel partie 2 BonjourDans l‟article précédent nous apprenions comme suivre les stocks avec Excel, et introduisions l‟utilisation du tableau croisé dynamique comme fiche de stock. Nous continuons aujourd‟hui avec la mise en place de cette fiche de stock .1Comment mettre en forme notre tcd avec la colonne formule pour qu‟ils restent uniformes ?

Pour arriver à donner l‟impression que nous avons un seul tableau croisé dynamique, il faut faire une même mise en forme conditionnelle sur le tableau croisé dynamique et sur la colonne solde: -

Mise en forme conditionnelle sur le tableau croisé dynamique :

·

Se mettre sur le TCD,

·

Dans le ruban, Accueil/Style/Mise en forme conditionnelle

· Dans la boite de dialogue, choisir : toutes les cellules affichant les valeurs « »pour « Date » et « Type » Pour la règle, choisir Utiliser une formule pour déterminer pour quelles cellules le format Pour la formule : =$B9<> »" Choisir le format qui vous plait

Avec ceci, les données du tableau croisé dynamique sont mises en forme conditionnelle. -

Mettre en forme les étiquettes et la colonne solde :

·

Se mettre sur B9

·

Dans le ruban, Accueil/Style/Mise en forme conditionnelle/Gérer les règles

·

Choisir nouvelle règle

· Dans la boite de dialogue, choisir Utiliser une formule pour quelles cellules le format sera appliqué Dans formule, écrire: =$B9<> »"

Choisir exactement le même format que celui du tableau croisé dynamique Valider Dans la boite de dialogue du gestionnaire des règles de mise en forme conditionnelle, dans s‟applique à: sélectionner avec la souris =$B$9:$B$21000;$E$9:$E$21000. Pour sélectionner 2 colonnes contiguës il faut utiliser la touche ctrl du clavier.

Maintenant votre tableau croisé dynamique et votre colonne semblent être un seul et même tableau. 2-

Choisir le produit et la période pour la fiche de stock

Pour choisir le produit de la fiche de stock, il faut juste cliquer sur la flèche de la cellule C5 et choisir dans la liste déroulante le produit.

Pour choisir la période à analyser dans notre fiche de stock, il faut : -

Se mettre sur Date et cliquer sur la petite flèche

Choisir filtre chronologique/Entre et dans la boite de dialogue spécifier la période de votre choix

Ok, je pense que nous avons vu entièrement comment jongler avec notre Tableau croisé dynamique et notre colonne solde pour avoir notre fiche de stock.Bien sûr vous vous doutez bien que j‟aurai certainement rajouté un graphique pour rendre plus intéressant cette fiche de stock. Des questions, des commentaires n‟hésitez pas à utiliser l‟espace commentaire pour le faire. Soyez sympa et partagez l'article avec vos proches!                  

    

inShare0

La toute puissante fonction RECHERCHEV() La fonction RECHERCHEV()Hello, nous allons voir une fonction de recherche très puissante: RECHERCHERV() Lorsque vous recherchez dans un tableau un élément la fonction RECHERCHEV() est bien souvent très indiquée: Excel dit : « RECHERCHEV() cherche une valeur dans la 1ère colonne à gauche d‟un tableau, puis renvoie une valeur dans la même ligne d‟une colonne spécifiée. Par défaut le tableau doit être trié par ordre croissant» RECHERCHEV(valeur_cherchée, table_matrice, no_index_col, [valeur_proche]) J‟avoue moi-même que comprendre cela n‟est pas évident. Exemple: Nous avons ce tableau :

Si je vous demande quel est le salaire du Matricule Mat03 ?Qu‟est-ce que vous faites ? Vous regardez votre tableau, vous regardez la colonne Matricule (la 1ère colonne), dès que vous avez retrouvé Mat03 (l‟élément de recherche), vous “glissez” pour vous retrouver sur la colonne salaire (la 3ème colonne) du tableau. C‟est exactement de cette manière que la fonction RECHERCHEV() fonctionne. La formule sera : =RECHERCHEV(« Mat03″;A1:D6;3;0) La fonction RECHERCHEV() recherche dans la 1ère colonne du tableau en question ($A$1:$D$6), l‟élément recherché (Mat03) et renvoie la valeur présente dans la 3ème colonne. Mat03 : la valeur de recherche A1:D6 : tableau de recherche

3 : le numéro de colonne dans laquelle on doit renvoyer la valeur 0 : pour préciser de recherche la valeur exacte Mat03 J‟espère que cet exemple vous a un peu clarifié cette fonction. Une autre technique pour l‟utilisation de cette fonction :

Exemple2: Retrouver le taux d’impôt avec RECHERCHEV(): Supposons ce tableau

Nous voulons retrouver le taux d‟impôt selon le montant de la facture. Nous avons nommé la plage E3 :F7=Impots En C2, on écrira:=RECHERCHEV(B2;impots;2;VRAI) L‟astuce ici est de mettre pour la valeur proche la valeur VRAI (ou 1) . Dans ce cas, la fonction recherchera la valeur et si elle ne la trouve pas, il prendra la valeur la plus proche .

Comment Gérer Les Erreurs Dans Recherchev()Lorsque la fonction recherchev ne trouve pas la valeur, elle peut renvoyer #N/A cette erreur, pour contourner cela on peut utiliser la fonction SIERREUR(). Cette dernière est uniquement présente à partir d‟Excel 2007. =SIERREUR(RECHERCHEV(« Mat03″;$A$1:$D$6;3;0); »Absent ») Si vous avez dans une version inferieure à Excel 2007, vous pouvez plutôt utiliser cette formule: =SI(ESTERREUR(RECHERCHEV(« Mat03″;$A$1:$D$6;3;0)); »Absent »;RECHERCHEV( « Mat03″;$A$1:$D$6;3;0)) Un peu plus long, mais donne exactement le même résultat.

RECHERCHEV MATRICIEL Supposons ce tableau :

Nous voulons retrouver à partir de ce tableau avec une seule formule la boite postale, la ville et le pays de Raphael. Nous avons nommé Donnees=A2:D5 1- Sélectionner G2:G4 2- Ecrire =RECHERCHEV(G1;Donnees;{2;3;4};0) 3- Valider avec Ctrl+Maj+Entrer Et automatiquement les { s‟ajoutent dans la formule. C‟est une formule matricielle. Bien sûr il y a encore de nombreuses utilisations de cette fonction qu‟on couvrira plus tard. Des questions ? N „hésitez pas. Soyez sympa et partagez l'article avec vos proches!                    

  

inShare0