Excel pour gérer simplement vos stocks

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))

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 ».

Donc procédez de la manière suivante :

1. Cliquez sur => Mise en forme conditionnelle

1. Sélectionnez => Nouvelle Règle

1. Dans sélectionnez un type de règle sélectionnez => Utilisez une formule pour déterminer pour quelles cellules le format sera appliqué

1. Dans le champ dédier à => Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie

1. 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))

1. 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

1. 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 ».

1. Dans la barre de tache cliquez « formule » puis sur « Gestionnaire de noms »

1. Cliquez sur « Nouveau » et nommez en Nom => CODES

1. 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

1. 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 »

1. Ensuite dans la barre de tache cliquez sur « Données » et puis sur « Validation des données »

1. Dans « Autoriser » sélectionnez « liste »

1. Et dans « Source » ajouter => =CODES

1. Maintenant en A4 vous avez la liste déroulante des codes enregistrés dans l’onglet « STOCK »

1. 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 :

1. Dans la barre de tache cliquez « formule » puis sur « Gestionnaire de noms »

1. Cliquez sur « Nouveau » et nommez en Nom => CODES_REFERENCE

1. 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 sélectionner la ligne Affecter une macro….et choisissez SAUVEGARDER qui apparaît dans le liste.

A très bientôt, mais vous pouvez déjà obtenir une fichier source avec les formulaires