Gestion de stock Excel : inventaire automatisé et alertes de rupture

Excel vs logiciel de stock : quand Excel suffit (et quand il ne suffit plus)

La première question à se poser est honnête : avez-vous vraiment besoin d'un logiciel de gestion de stock dédié ? Pour beaucoup de TPE, artisans, associations ou petites équipes logistiques, la réponse est non — pas tout de suite.

Excel convient parfaitement dès lors que vous gérez moins de 500 références produit, que les mouvements de stock sont saisis manuellement ou semi-automatiquement, et que vous n'avez pas besoin de synchronisation multi-sites en temps réel. Dans ce périmètre, un fichier bien construit vous donnera exactement le même niveau d'information qu'un logiciel à 200 €/mois.

En revanche, Excel atteint ses limites quand :

• Plusieurs utilisateurs modifient le fichier simultanément (même avec SharePoint, les conflits de version arrivent)
• Vous gérez des numéros de série ou des lots avec traçabilité réglementaire (HACCP, ISO…)
• Le volume de transactions dépasse quelques centaines par jour
• Vous avez besoin d'une connexion directe à votre ERP, caisse ou e-commerce

Structure d'un fichier de gestion de stock Excel

Un fichier de stock professionnel repose sur une architecture à trois feuilles minimum, clairement séparées pour éviter les erreurs de saisie et faciliter la maintenance :

1. Feuille "Références" : le catalogue produit avec les seuils de stock minimum et de réapprovisionnement
2. Feuille "Mouvements" : le journal de toutes les entrées et sorties, horodaté et tracé
3. Feuille "Tableau de bord" : la vue consolidée du stock réel, des alertes et des commandes à déclencher

La feuille Références doit au minimum contenir : la référence produit (clé unique), le libellé, l'unité, le stock minimum (seuil d'alerte), la quantité de réapprovisionnement idéale, et le fournisseur principal. Chaque ligne correspond à une référence, sans doublon.

La feuille Mouvements fonctionne comme un journal comptable : on n'y corrige jamais une ligne, on ajoute une ligne corrective. Chaque mouvement comporte : la date, la référence produit, le type de mouvement (entrée / sortie / inventaire), la quantité, et éventuellement le bon de livraison ou le numéro de commande.

Pour que RECHERCHEX et SOMME.SI.ENS fonctionnent correctement, chaque feuille doit être mise en forme en Tableau structuré (Insertion → Tableau, ou Ctrl+T). Nommez chaque tableau explicitement : tRef, tMvt. Cela rend vos formules lisibles et vos plages dynamiques.

Formules pour les entrées/sorties et calcul du stock réel

Le stock réel d'une référence se calcule simplement : stock initial + total des entrées − total des sorties. Avec des tableaux structurés, SOMME.SI.ENS est la formule de référence pour agréger les mouvements par type.

=[@StockInitial]
 + SOMME.SI.ENS(tMvt[Quantité]; tMvt[Référence]; [@Référence]; tMvt[Type]; "Entrée")
 - SOMME.SI.ENS(tMvt[Quantité]; tMvt[Référence]; [@Référence]; tMvt[Type]; "Sortie")
 + SOMME.SI.ENS(tMvt[Quantité]; tMvt[Référence]; [@Référence]; tMvt[Type]; "Inventaire")

Le type "Inventaire" permet de recaler le stock sans modifier les mouvements historiques — indispensable lors d'un comptage physique. Si le comptage donne 45 unités alors que le calcul affiche 48, on saisit un mouvement "Inventaire" de −3.

Pour retrouver le libellé, le fournisseur ou le seuil minimum d'une référence à partir du tableau de bord, RECHERCHEX est bien plus robuste que l'ancien VLOOKUP — elle gère nativement les valeurs non trouvées et ne dépend pas de la position des colonnes :

=RECHERCHEX(
  [@Référence];
  tRef[Référence];
  tRef[StockMinimum];
  "Référence introuvable";
  0
)

Cette formule cherche la référence de la ligne courante dans le tableau tRef, et renvoie la valeur de la colonne StockMinimum correspondante. Le quatrième argument "Référence introuvable" remplace l'erreur #N/A par un message lisible — ce qui évite de casser les formules d'alerte situées plus loin dans le tableau de bord.

Alertes automatiques de rupture et de réapprovisionnement

Un fichier de stock sans alerte est un fichier qu'on consulte trop tard. L'objectif est de détecter automatiquement deux situations critiques : le stock en dessous du seuil minimum (risque de rupture imminente) et le stock nul ou négatif (rupture déjà effective).

La formule d'alerte combine SI et ET pour évaluer plusieurs conditions simultanément et renvoyer un statut lisible dans une colonne dédiée :

=SI([@StockRéel]<=0;
  "🔴 RUPTURE";
  SI(ET([@StockRéel]>0; [@StockRéel]<=[@StockMinimum]);
    "🟠 ALERTE RÉAPPRO";
    SI([@StockRéel]<=[@StockMinimum]*1.2;
      "🟡 STOCK FAIBLE";
      "🟢 OK"
    )
  )
)

Cette formule à trois niveaux couvre l'ensemble du spectre : rupture confirmée, seuil minimum atteint, zone tampon (stock entre le seuil minimum et 120 % de ce seuil), et situation normale. Les emojis ne sont pas obligatoires, mais ils permettent de scanner la colonne visuellement sans lire chaque cellule — très utile sur un tableau de 200+ lignes.

Pour le calcul du stock minimum lui-même, la bonne pratique est de ne pas le saisir arbitrairement mais de le calculer à partir de la consommation moyenne et du délai fournisseur :

=ARRONDI.SUP(
  ([@ConsommationMoyenneJour] * [@DélaiFournisseurJours])
  + ([@ConsommationMoyenneJour] * [@StockSécuritéJours]);
  0
)

' Exemple :
' Consommation moyenne = 12 unités/jour
' Délai fournisseur    = 5 jours
' Stock de sécurité    = 2 jours
' => Stock minimum = ARRONDI.SUP((12*5)+(12*2);0) = 84 unités

Avec ce calcul dynamique, le seuil d'alerte s'adapte automatiquement si vous mettez à jour votre consommation mensuelle ou que votre fournisseur change ses délais. Couplé à une mise en forme conditionnelle sur la colonne Statut (rouge pour RUPTURE, orange pour ALERTE RÉAPPRO), le tableau de bord devient un outil de pilotage visuel immédiat.

Aller plus loin : automatisation des commandes fournisseurs avec VBA

Une fois le fichier de stock structuré et les alertes en place, l'étape suivante est d'automatiser la génération des commandes fournisseurs. Plutôt que de parcourir manuellement la liste des alertes et de remplir un bon de commande, une macro VBA peut le faire en quelques secondes.

Le principe est simple : la macro parcourt le tableau de bord, identifie toutes les lignes en statut "RUPTURE" ou "ALERTE RÉAPPRO", et génère une feuille de commande pré-remplie par fournisseur avec les quantités à commander (stock optimal − stock réel).

Voici la logique de base :

• Boucle sur chaque ligne du tableau de bord
• Si Statut contient "ALERTE" ou "RUPTURE" → copier la référence, le libellé, le fournisseur et la quantité à commander dans une feuille "Commandes"
• Grouper les lignes par fournisseur
• Optionnel : générer un email Outlook pré-rédigé par fournisseur via CreateObject("Outlook.Application")

Cette automatisation fait économiser entre 30 minutes et 2 heures par semaine selon le volume de références gérées. Pour les équipes qui envoient des bons de commande par email, l'intégration Outlook via VBA est particulièrement impactante : un clic suffit pour envoyer les demandes à l'ensemble des fournisseurs concernés.

Si vous n'êtes pas à l'aise avec VBA, une alternative sans code consiste à utiliser Power Query pour filtrer automatiquement les lignes en alerte et les exporter vers une feuille dédiée — sans macros, sans risque de sécurité lié aux fichiers .xlsm.

Pour aller encore plus loin, il est possible de connecter Excel à une base de données Access ou SQL Server pour externaliser le stockage des mouvements tout en conservant Excel comme interface de saisie et de reporting. Cette architecture hybride offre le meilleur des deux mondes : la flexibilité d'Excel et la robustesse d'une base relationnelle.