Gestion des stocks dans Excel : quantité et seuil
Cette feuille de stock est la liste entrepôt. Une ligne est un article : la quantité en stock, le minimum que vous fixez, et la commande, qui est le minimum moins la quantité quand la quantité est sous ce minimum. Un lot d'officine, avec une date de péremption, est une autre feuille. Cette page ne suit pas les lots. Vous saisissez la quantité que vous avez comptée. Elyx peut ajouter la commande sur l'export que vous avez déjà. L'essai dure 7 jours, sans carte.
Le probleme
La quantité en stock et le seuil de commande sont souvent dans deux fichiers. Vous voyez le manque quand la commande est déjà en retard. Gardez les deux nombres sur la ligne de l'article. La commande est l'écart sous le minimum. La feuille ne passe pas le bon de commande.
Les benefices
Une ligne est un article, avec la quantité et un minimum.
La commande est le minimum moins la quantité, quand la quantité est sous le minimum.
Vous saisissez la quantité comptée.
Une quantité vide reste vide.
Le lot et la péremption restent sur la feuille de stock officine.
Tutoriel pas a pas
Créer la structure du tableau principal
Créez un nouveau classeur Excel et définissez les colonnes essentielles pour le suivi des stocks. Les colonnes doivent inclure : Référence Produit, Désignation, Catégorie, Stock Actuel, Stock Minimum, Stock Maximum, Prix Unitaire, Valeur Stock et Statut Alerte.
Utilisez Ctrl+T pour convertir votre plage de données en tableau structuré. Cela facilite les formules et les filtres automatiques.
Organiser les données d'exemple
Remplissez les premières lignes avec des données réalistes : produits de votre entrepôt, leurs références, catégories et niveaux de stock. Par exemple : REF001 - Vis M6 - Quincaillerie - 450 unités - Stock min : 100 - Stock max : 800.
Laissez au moins 50 lignes vides pour accueillir les nouveaux produits. Formatez les colonnes avec les bons types (nombre, devise, texte).
Calculer la valeur totale du stock par produit
Créez une colonne 'Valeur Stock' qui multiplie le Stock Actuel par le Prix Unitaire. Cette colonne permet au responsable logistique de connaître la valeur financière de chaque article en stock.
=D2*G2Formatez cette colonne en devise (€) pour une meilleure lisibilité. Copiez la formule vers le bas avec Ctrl+D.
Ajouter une formule d'alerte de stock
Créez une colonne 'Statut Alerte' qui affiche automatiquement si le stock est critique, normal ou excédentaire. Utilisez la fonction IF pour comparer le Stock Actuel avec les seuils minimum et maximum définis.
=IF(D2<E2;"CRITIQUE";IF(D2>F2;"EXCÉDENTAIRE";"NORMAL"))Utilisez la mise en forme conditionnelle pour colorer les cellules : rouge pour CRITIQUE, orange pour EXCÉDENTAIRE, vert pour NORMAL.
Créer un tableau récapitulatif par catégorie avec SUMIF
Dans une nouvelle section du classeur, créez un résumé qui totalise le Stock Actuel et la Valeur Stock par Catégorie. Utilisez SUMIF pour agréger les données du tableau principal selon la catégorie de chaque produit.
=SOMME.SI($C$2:$C$100;J2;$D$2:$D$100)Placez ce résumé sur la même feuille ou créez un onglet 'Synthèse'. Cela donne une vue d'ensemble au responsable logistique pour les décisions d'approvisionnement.
Ajouter une fonction VLOOKUP pour les prix fournisseur
Créez un onglet 'Tarifs Fournisseurs' avec les références produits et leurs prix. Utilisez VLOOKUP dans le tableau principal pour récupérer automatiquement le prix unitaire depuis cet onglet, évitant les erreurs de saisie.
=VLOOKUP(A2;Tarifs!A:B;2;0)Si le produit n'existe pas dans la table Tarifs, ajoutez IFERROR pour afficher un message : =IFERROR(VLOOKUP(...);"Prix non trouvé")
Calculer les mouvements de stock (entrées/sorties)
Ajoutez deux colonnes 'Entrées du mois' et 'Sorties du mois' pour suivre les mouvements. Créez une colonne 'Variation' qui calcule la différence pour identifier les tendances de consommation et ajuster les commandes.
=H2-I2Mettez à jour mensuellement ces colonnes. Vous pouvez aussi créer une colonne 'Rotation Stock' (=I2/D2*100) pour identifier les produits lents à vendre.
Créer un tableau de bord avec indicateurs clés
Synthétisez les KPIs essentiels en haut du classeur : nombre de produits en alerte critique, valeur totale du stock, rotation moyenne, coût de stockage estimé. Utilisez des formules SUMIF et COUNTIF pour automatiser ces calculs.
=COUNTIF($H$2:$H$100;"CRITIQUE")Formatez ce tableau de bord avec des polices grandes et des couleurs pour qu'il soit visible au premier coup d'œil. Actualisez-le hebdomadairement.
Ajouter des filtres et des segments interactifs
Activez les filtres automatiques sur le tableau principal (Données > Filtre) et créez des segments pour filtrer rapidement par Catégorie ou Statut Alerte. Cela permet au responsable logistique de consulter rapidement les produits critiques ou par zone d'entrepôt.
Utilisez Insertion > Segment pour créer des filtres visuels et interactifs. Cela rend le suivi des stocks plus dynamique et intuitif.
Protéger et finaliser le template
Protégez les formules et la structure en verrouillant les cellules critiques (Révision > Protéger la feuille). Laissez les colonnes de saisie (Stock Actuel, Entrées, Sorties) déverrouillées pour que les utilisateurs puissent les mettre à jour facilement.
Enregistrez le fichier en tant que modèle Excel (.xltx) pour le réutiliser pour chaque nouveau produit ou période. Documentez les instructions de saisie dans un onglet 'Mode d'emploi'.
Fonctionnalites du template
Alerte automatique de stock faible
Les articles dont le stock passe sous le seuil minimum s'affichent en rouge, permettant au responsable logistique de commander à temps et d'éviter les ruptures de stock
=SI(B2<C2,"ALERTE","OK")Calcul automatique de la valeur de stock
La valeur totale du stock est calculée en temps réel (quantité × prix unitaire), essentielle pour le bilan comptable et la gestion budgétaire
=B2*C2Suivi des mouvements de stock (entrées/sorties)
Chaque mouvement est enregistré avec date et raison (achat, vente, casse), offrant une traçabilité complète et facilitant les audits d'inventaire
=D2+(E2-F2)Calcul du taux de rotation des stocks
Identifie les produits lents à vendre et optimise l'espace d'entreposage en calculant la fréquence de renouvellement
=VENTES_ANNUELLES/STOCK_MOYENGénération automatique de commandes fournisseurs
Crée une liste des articles à commander en fonction du seuil minimum et de la quantité économique, économisant du temps administratif
=SI(B2<C2,QUANTITE_COMMANDE,0)Tableau de bord KPI logistique
Affiche en temps réel les indicateurs clés : valeur totale du stock, nombre d'articles en alerte, taux de disponibilité, facilitant la prise de décision
=SOMME(B2:B100) / SOMME(C2:C100)Exemples concrets
Alerte automatique de rupture de stock
Thomas, responsable logistique chez un distributeur de pièces automobiles, doit identifier rapidement les articles en dessous du seuil minimum pour déclencher des commandes urgentes.
Article A (Filtre moteur): Stock actuel 45 unités, Seuil minimum 100 unités. Article B (Plaquettes frein): Stock actuel 280 unités, Seuil minimum 200 unités. Article C (Batterie): Stock actuel 12 unités, Seuil minimum 50 unités.
Resultat : Un tableau avec code couleur (rouge pour rupture imminente, orange pour vigilance, vert pour normal) et une colonne 'Quantité à commander' calculée automatiquement. Thomas voit immédiatement que Filtre moteur et Batterie nécessitent une commande urgente.
Calcul du coût de stockage mensuel par catégorie
Sophie, responsable logistique dans un entrepôt de produits électroniques, doit justifier les coûts de stockage auprès de la direction et optimiser l'espace occupé par catégorie.
Catégorie Téléphones: Stock moyen 450 unités, Coût unitaire mensuel 2,50€. Catégorie Accessoires: Stock moyen 1200 unités, Coût unitaire mensuel 0,80€. Catégorie Pièces détachées: Stock moyen 800 unités, Coût unitaire mensuel 1,20€.
Resultat : Un tableau récapitulatif montrant le coût de stockage par catégorie (Téléphones: 1125€, Accessoires: 960€, Pièces: 960€) avec un total de 3045€. Sophie peut identifier que les téléphones représentent 37% des coûts et justifier une optimisation de ce segment.
Suivi des rotations de stock et identification des articles lents
Marc, responsable logistique dans une PME de distribution, doit identifier les articles qui ne se vendent pas assez vite pour libérer de l'espace et réduire les coûts de financement du stock.
Produit X: Quantité en stock 500 unités, Quantité vendue en 3 mois 45 unités, Délai de rotation 33 mois. Produit Y: Quantité en stock 300 unités, Quantité vendue en 3 mois 280 unités, Délai de rotation 3,2 mois. Produit Z: Quantité en stock 150 unités, Quantité vendue en 3 mois 8 unités, Délai de rotation 56 mois.
Resultat : Un tableau trié par délai de rotation montrant que Produit Z et X sont des articles lents (immobilisant du capital). Marc peut décider de réduire les commandes, proposer des promotions ou arrêter la vente de ces articles pour améliorer la trésorerie.
Astuces de pro
Automatiser les alertes de stock critique avec mise en forme conditionnelle
Configurez une mise en forme conditionnelle pour identifier instantanément les articles en rupture ou sous le seuil minimum. Cela vous permet de repérer les problèmes sans scanner manuellement chaque ligne. Accédez à Accueil > Mise en forme conditionnelle > Nouvelle règle, puis utilisez une formule personnalisée pour colorer les cellules en rouge si le stock est inférieur au seuil défini.
=B2<C2Créer un tableau croisé dynamique pour analyser les mouvements de stock en temps réel
Utilisez un TCD pour visualiser rapidement les sorties/entrées par catégorie, fournisseur ou période. Cela transforme des milliers de lignes en données exploitables en quelques clics. Sélectionnez vos données (Ctrl+A), puis Insertion > Tableau croisé dynamique. Glissez les dimensions (produit, date, magasin) pour identifier les tendances et optimiser vos commandes.
Utiliser INDEX/MATCH pour retrouver automatiquement les infos fournisseur et délai
Remplacez les RECHERCHEV limitées par INDEX/MATCH pour une recherche bidirectionnelle fiable. Cela évite les erreurs lors de réorganisation de colonnes et accélère les calculs. Parfait pour récupérer automatiquement le délai d'approvisionnement ou le prix unitaire en fonction de la référence produit.
=INDEX(Fournisseurs!$B$2:$B$100;MATCH(A2;Fournisseurs!$A$2:$A$100;0))Automatiser les commandes avec une formule de point de commande dynamique
Créez une colonne « À commander » qui calcule automatiquement la quantité optimale selon la consommation moyenne et le délai fournisseur. Cela élimine les décisions manuelles et réduit les ruptures. Utilisez MAX pour garantir une quantité minimum et intégrez un coefficient de sécurité selon la variabilité.
=MAX(ARRONDI(MOYENNE(D2:D30)*E2*1,2;0);Seuil_minimum)