GESTION DES STOCKS sous EXCEL
CREATION sur cette page de la procédure pour la création d'un fichier Excel pour gérer simplement vos stocks !!
Dans un
premier temps nous allons construire la Base de notre fichier Excel.
Créer dans votre fichier, trois onglets que vous allez nommer respectivement :
* ACCUEIL
* STOCK
* ENTREES
* SORTIES
L'onglet ACCUEIL reste vierge pour l'instant, nous y ajouterons des boutons (selon image ci-dessus) pour naviguer dans le fichier ultérieurement via des formulaires.
Dans l'onglet STOCK inscrivez en :
A3 => Code
B3 => Désignation
C3 = > Fournisseur
D3 = > Racle
E3 = > Etagère
F3 = > Rang
G3 = > PA H.T. (Prix Achat Hors Taxe)
H3 = > TAUX de MARGE
I3 => PU H.T. (Prix Unitaire Hors Taxe)
J3 => Début de stock
K3 => Entrées
L3 => Sorties
M3 => Stock actuel
N3 => Valeur stock actuel
O3 => Stock Mini
P3 => Alerte
Q3 => Commande pour atteindre le stock mini
Maintenant insérez les formules suivantes dans l'onglet "STOCK" en :
K4 => =SOMME.SI(ENTREES!A:A;A4;ENTREES!C:C)
Ce qui correspond à la somme de la valeur en cellule A4 (onglet STOCK) retrouvé dans l'onglet "ENTREES"
L4 => =SOMME.SI(SORTIES:A;A4;SORTIES!C:C)
Idem mais pour les valeurs de l'onglet "SORTIES"
M4 => =J4+K4-L4
N4 => =SI(M4="";"";G4*M4)
P4 => =SI(O4="";"";SI(M4<O4;"Commande à effectuer";""))
R4 => =SI(O4=0;"";SI(M4<O4;O4-M4;0))
Dans l'onglet ENTREES inscrivez en:
A1 => Entrées en stock
A2 => Code
B2 = > Désignation
C2 => Quantités
D2 => PU H.T.
E2 => TOTAL H.T.
F2 => Date
Créer dans votre fichier, trois onglets que vous allez nommer respectivement :
* ACCUEIL
* STOCK
* ENTREES
* SORTIES
L'onglet ACCUEIL reste vierge pour l'instant, nous y ajouterons des boutons (selon image ci-dessus) pour naviguer dans le fichier ultérieurement via des formulaires.
Dans l'onglet STOCK inscrivez en :
A3 => Code
B3 => Désignation
C3 = > Fournisseur
D3 = > Racle
E3 = > Etagère
F3 = > Rang
G3 = > PA H.T. (Prix Achat Hors Taxe)
H3 = > TAUX de MARGE
I3 => PU H.T. (Prix Unitaire Hors Taxe)
J3 => Début de stock
K3 => Entrées
L3 => Sorties
M3 => Stock actuel
N3 => Valeur stock actuel
O3 => Stock Mini
P3 => Alerte
Q3 => Commande pour atteindre le stock mini
Maintenant insérez les formules suivantes dans l'onglet "STOCK" en :
K4 => =SOMME.SI(ENTREES!A:A;A4;ENTREES!C:C)
Ce qui correspond à la somme de la valeur en cellule A4 (onglet STOCK) retrouvé dans l'onglet "ENTREES"
L4 => =SOMME.SI(SORTIES:A;A4;SORTIES!C:C)
Idem mais pour les valeurs de l'onglet "SORTIES"
M4 => =J4+K4-L4
N4 => =SI(M4="";"";G4*M4)
P4 => =SI(O4="";"";SI(M4<O4;"Commande à effectuer";""))
R4 => =SI(O4=0;"";SI(M4<O4;O4-M4;0))
Ensuite pour le "fun", nous allons colorier des
lignes "impair" et "pair" pour une meilleure lisibilité du tableau.
Cette manipulation peut être effectué pour les onglet « ENTREES » et « SORTIES ».
Cette manipulation peut être effectué pour les onglet « ENTREES » et « SORTIES ».
Donc procédez de la manière suivante :
a)
Cliquez sur => Mise en forme
conditionnelle
b)
Sélectionnez => Nouvelle Règle
c)
Dans sélectionnez un type de règle
sélectionnez => Utilisez une formule pour déterminer pour quelles
cellules le format sera appliqué
d)
Dans le champ dédier à => Appliquer une
mise en forme aux valeurs pour lesquelles cette formule est vraie
e)
Ajoutez la formule suivante => =NON(MOD(LIGNE();2))
Cette formule
correspond au ligne « Pair »
Pour les ligne « impair »
mentionnez la formule suivante => =NON(MOD(LIGNE();1))
f)
Cliquez sur le bouton « format »
pour choisir la couleur de fond et les bordures selon vos goûts pour la
présentation de votre fichier
g)
Le format conditionnel s’applique pour la
plage suivante =$A$3:$F$1000
Ces paramètres devraient donner
ceci par exemple.
Dans l'onglet ENTREES inscrivez en:
A1 => Entrées en stock
A2 => Code
B2 = > Désignation
C2 => Quantités
D2 => PU H.T.
E2 => TOTAL H.T.
F2 => Date
Nous allons créer une liste déroulante pour les
onglets « ENTREES » et « SORTIES ».
a)
Dans la barre de tache cliquez « formule » puis sur « Gestionnaire de noms »
b)
Cliquez sur « Nouveau » et nommez
en Nom => CODES
c)
Dans « fait
référence à » sélectionnez la plage A4 :A100 de l’onglet STOCK
La formule est celle-ci => =Stock!$A$4:$A$100
d)
Donc maintenant vous avez une liste de
référence pour faire vos recherches.
Sélectionner
la cellule A4 de l’onglet « ENTREES ».
La procédure est la même pour l’onglet « SORTIES »
e)
Ensuite dans la barre de tache cliquez sur « Données » et puis sur « Validation
des données »
f)
Dans « Autoriser » sélectionnez
« liste »
g)
Et dans « Source » ajouter
=> =CODES
h)
Maintenant en A4 vous avez la liste
déroulante des codes enregistrés dans l’onglet « STOCK »
i)
Incrémentez cette cellule vers le bas jusqu’à
la ligne que vous souhaitez.
Maintenant pour que les données s'associe au numéro de code choisi dans la liste déroulante s'affiche dans les cellules adjacentes suivez la procédure suivante :
Donc créer maintenant une liste de données de la manière suivante :
Donc créer maintenant une liste de données de la manière suivante :
a)
Dans la barre de tache cliquez « formule » puis sur « Gestionnaire de noms »
b)
Cliquez sur « Nouveau » et nommez
en Nom => CODES_REFERENCE
c)
Dans « fait
référence à » sélectionnez la plage A3 :I100 de l’onglet STOCK
La formule est celle-ci => =Stock!$A$3:$I$100
Ceci étant fait, positionnez vous sur la cellule B3 et ajouter la formule suivante => =RECHERCHEV(A3;CODE_REFERENCE;2;FAUX)
Donc la formule va chercher par rapport au code mentionné en A3 et renvoyer l'information situé en colonne 2 de la table de données "CODE_REFERENCE".
Pour la colonne D ajouter la formule suivante => =RECHERCHEV(A3;CODE_REFERENCE;9;FAUX)
Procédez de la même manière pour l'onglet "SORTIES"
Dans l'onglet SORTIES inscrivez en :
A1 => Sorties du stock
A2 => Code
B2 = > Désignation
C2 => Quantité
D2 => PU H.T.
E2 => TOTAL H.T.
F2 => Date
Donc maintenant votre fichier peut être renseigner manuellement. "Simplissime" au départ, mais fastidieux si vous avez une liste de produit au-delà de 100 lignes et voire plus.
Comment faire ? Vous ne l'avez pas deviné.... sans blague ?
Mais par le formulaire ou plus, par les formulaires..... Soyez patients cela va suivre le temps que je teste et programme la suite de cette page.
Avant de créer les formulaires nous allons paramétrer la page "ACCUEIL".
Pour gagner du temps vous pouvez la télécharger avec tous les boutons à cette adresse :
PAGE ACCUEIL
Avant toutes choses nous allons empêcher le changement de NOM des feuilles existante. Pour cela accéder au VBAProject (Alt + F11)
Sélectionnez la feuille "ACCUEIL" (Feuil4(ACCUEIL) et double-cliquez sur cette ligne. Sur la droite coller le code suivant :
'Empêche le changement de nom de la feuille
'A chaque changement de sélection, le nom de la feuille sera vérifié et remplacé si nécessaire.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Name <> "ACCUEIL" Then
ActiveSheet.Name = "ACCUEIL"
End If
End Sub
Pour les autres feuilles procédez de la même manière en changeant tout simplement le nom de la feuille (Ex. ACCUEIL par ENTREES).
Sur la page ACCUEIL du fichier excel (que vous avez téléchargé) il existe plusieurs bouton, et entre autres le bouton nommé "Sauvegarde FICHIER sur clé USB".
Nous allons le programmer et ce bouton vous permettra de sauvegarder votre fichier sur une clé USB automatiquement.
Pour ce faire insérer un nouveau Module dans le VBAProject via Insertion + Module.
Dans ce module inserer le code suivant :
Sub SAUVEGARDER()'Créer une copier du fichier GESTION DE STOCK sur la clé USB
USB = lettre_usb
chemin = USB & "\SAUGEGARDE\"
ActiveWorkbook.SaveCopyAs chemin & "Copie - Le NOM de votre FICHIERxlsm"
End Sub
Changer "Le NOM de votre FICHIER" pour le nom de votre fichier et sur votre clé USB vous créez un dossier "SAUVEGARDE" sur le premier plan.
Dans le module1 ajoutez le programme suivant :
Function lettre_usb()
ordi = "."
Set objet_WMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & ordi & "\root\cimv2")
Set liste_pilotes = objet_WMI.ExecQuery _
("Select * from Win32_LogicalDisk")
For Each pilote In liste_pilotes
If pilote.DriveType = 2 Then
lettre_usb = pilote.DeviceID
Exit Function
End If
Next
lettre_usb = "aucune clé connectée"
End Function.
Maintenant nous allons associer cette macro de sauvegarde au bouton.
Sur la page accueil sélectionnez le bouton par un clique-droit et dans liste qui s'affiche à l'écran selectionn la ligne Affecter une macro....et choississez SAUVEGARDER qui apparaît dans le liste.
A très bientôt, vous pouvez effectuer la demande d'attribution du fichier via le lien ci-dessous.
Pour les professionnels, je peux adapter le fichier en fonctions des demandes (ex. insertion de logo, etc.)
Voici des captures d'écran des différents formulaires de gestion des données du fichier à disposition. Le fichier peut encore évoluer.
Formulaire d'accès au fichier via mot de passe dédié :
Formulaire de gestion globale :
Formulaire de gestion des entrées :
Formulaire d'insertion de nouvelles références :
Formulaire gestion des données fournisseurs :
Formulaire gestion des données clients :
Bonjour,
RépondreSupprimerje tiens à vous remercier pour cet excel, d'une qualité irréprochable.
Je cherche à modifier quelques UserForm pour adapter ce fichier mais il m'est demandé un mot de passe est-il possible de l'avoir ?
Bien à vous Jonathan
Ce commentaire a été supprimé par un administrateur du blog.
RépondreSupprimerFélicitations pour tout ce que vous présentez, c'est trés trés intéressant et fructueux à la fois.
RépondreSupprimerBonne continuation.
tout vieillot que je suis, je vous remercie infiniment car vous m'avez aidé dans mes petits travaux.
Merci encore...
Salut merci de votre contribution
RépondreSupprimerNous vous encourageons
Merci
SupprimerMerci beaucoup de votre contribution
RépondreSupprimerNous vous encourageons