Suivi de stock Excel : la structure et les formules d'un fichier qui reste juste

Un suivi de stock Excel qui tient dans la durée repose sur trois feuilles et une règle.

Les trois feuilles : Articles (une ligne par référence), Mouvements (une ligne par entrée ou sortie) et Tableau de bord (stock théorique et alertes, entièrement calculés). La règle : on ne corrige jamais une quantité à la main dans la feuille Articles. Toute variation de stock passe par une ligne de mouvement, datée et signée. C'est ce principe de journal que Microsoft décrit aussi dans sa page de modèles : enregistrer chaque variation dans un journal des transactions lié, qui met à jour la liste d'inventaire principale (Microsoft Excel — Suivi des stocks).

Sans ce principe, un fichier partagé devient difficile à auditer : plus personne ne sait si le 42 affiché vient d'un calcul ou d'une saisie manuelle de la semaine dernière.

Feuille Articles : les colonnes utiles, et celles qui alourdissent

La feuille Articles est un référentiel, pas un compteur. Elle décrit ce qu'est une référence, pas combien il y en a aujourd'hui.

Colonne	À quoi elle sert
Référence (SKU)	Clé unique, jamais réutilisée, jamais modifiée
Désignation	Libellé lisible par l'atelier
Fournisseur principal	Permet de regrouper les commandes
Unité	Pièce, mètre, kg — évite les quantités ininterprétables
Prix d'achat	Valorisation du stock
Stock initial	Quantité du dernier inventaire physique, avec sa date
Délai de réappro (jours)	Base du calcul du seuil d'alerte
Consommation moyenne	Calculée depuis les mouvements, pas saisie

Microsoft cite les mêmes briques dans ses modèles personnalisables : SKU, codes-barres, noms d'articles, fournisseurs, coût unitaire, niveau de réapprovisionnement (source).

Ce qui alourdit souvent pour rien dans une PME : une colonne « quantité en stock » saisie à la main (elle entre en conflit avec le calcul), les emplacements détaillés si vous n'avez qu'un local, et les colonnes TVA/prix de vente qui transforment le fichier de stock en devisier bancal.

Feuille Mouvements : une ligne = un mouvement

Six colonnes suffisent : Date, Référence, Sens (Entrée / Sortie), Quantité (toujours positive), Motif (réception, vente, casse, retour, régularisation d'inventaire), Saisi par.

Deux garde-fous à poser tout de suite, tous deux disponibles dans Excel : la validation des données, qui limite les saisies à des valeurs précises — par exemple n'accepter que des références SKU valides — et déclenche une alerte en cas d'entrée invalide (Microsoft) ; et la protection de la feuille, qui permet de limiter l'accès à certaines plages ou cellules (Microsoft).

Un journal ne se corrige pas : une erreur ne s'efface pas, elle se compense par un mouvement inverse avec le motif « correction ». C'est ce qui rend le fichier auditable six mois plus tard.

Calculer le stock théorique par référence

Dans la feuille Articles, le stock se calcule : stock initial + entrées − sorties, référence par référence. La fonction adaptée est SOMME.SI.ENS, qui additionne tous ses arguments répondant à plusieurs critères (documentation Microsoft).

En nommant les plages via un tableau structuré (Insertion > Tableau), pour que les formules suivent l'ajout de lignes :

= [@[Stock initial]] + SOMME.SI.ENS(Mouvements[Quantité];Mouvements[Référence];[@Référence];Mouvements[Sens];"Entrée") - SOMME.SI.ENS(Mouvements[Quantité];Mouvements[Référence];[@Référence];Mouvements[Sens];"Sortie")

Quatre points documentés qui expliquent la plupart des formules cassées :

• La plage à additionner est le premier argument de SOMME.SI.ENS, alors qu'elle est le troisième de SOMME.SI : copier l'une pour en faire l'autre est une source classique d'erreur (Microsoft).
• Les critères texte se mettent entre guillemets ; sinon le résultat affiché est souvent 0 au lieu du total attendu (Microsoft).
• Les plages de critères doivent contenir le même nombre de lignes et de colonnes que la plage à additionner (Microsoft).
• La fonction accepte jusqu'à 127 paires plage/critères, largement au-delà de ce qu'un suivi de stock demande (Microsoft).

Pour vous entraîner sur un cas simple avant de l'appliquer à vos références, Microsoft propose un exemple pas à pas d'addition sous plusieurs conditions (guide Microsoft).

Un seuil d'alerte calculé, pas choisi au doigt mouillé

Beaucoup de fichiers portent un « stock mini » saisi une fois, jamais revu. Une base plus solide : consommation moyenne sur la période × délai fournisseur + marge de sécurité.

Exemple entièrement hypothétique, donné pour illustrer le calcul : une référence de joints consommée en moyenne 40 unités par semaine, avec un fournisseur qui livre en 2 semaines et une marge de sécurité de 25 %, donnerait un seuil de 40 × 2 × 1,25 = 100 unités. En dessous, on commande. La consommation moyenne se tire directement du journal (somme des sorties sur les 12 dernières semaines ÷ 12), donc elle se met à jour toute seule au lieu de vieillir.

Côté affichage, les modèles Excel s'appuient sur la mise en forme conditionnelle pour surligner les articles à réapprovisionner et sur la fonction SI pour marquer les points de commande (Microsoft). Ajoutez une colonne « Quantité à commander » (= seuil − stock théorique, si positif) et un tri par fournisseur : votre tableau de bord devient directement une liste de commandes à passer, regroupées par fournisseur — une organisation que Microsoft suggère aussi (« organisez votre inventaire par fournisseur ou emplacement ») (Microsoft).

Partir d'un modèle Microsoft : ce que vous gagnez, ce qu'il faut ajouter

Microsoft met à disposition des modèles gratuits de suivi des stocks dans Excel en ligne — Liste d'inventaires en stock, Liste d'inventaire moderne, Liste d'inventaire bleue, Liste d'inventaire avec mise en surbrillance, Liste des stocks d'équipement — avec mise en forme conditionnelle et formules de calcul personnalisées (page modèles Microsoft). C'est un point de départ honnête : structure propre, colonnes standard, journal de transactions lié possible.

Avant de le mettre en service, vérifiez si le modèle retenu comporte les éléments suivants ; sinon, ajoutez-les :

• vos délais fournisseurs réels, référence par référence ;
• un seuil calculé plutôt que saisi une fois pour toutes ;
• la colonne « Saisi par », sans laquelle aucun écart ne se remonte à sa source ;
• la validation des données sur la colonne Référence et sur la colonne Sens.

On trouve aussi des classeurs de gestion de stock partagés sur des forums d'entraide, souvent équipés de macros. Ils dépannent, mais personne ne s'engage à les maintenir : si une macro casse dans deux ans, il faudra quelqu'un pour rouvrir le code. Quel que soit le fichier retenu — le vôtre, un modèle Microsoft ou un classeur récupéré — faites-lui passer le test ci-dessous avant de le confier à l'équipe.

Le test en six étapes avant de confier le fichier à l'équipe

1. Saisir 10 mouvements connus sur 3 références et vérifier le stock théorique à la calculatrice.
2. Supprimer une ligne de mouvement : le stock doit bouger, les formules ne doivent pas afficher #REF!.
3. Ajouter une référence en bas de la feuille Articles : la formule de stock doit se propager seule.
4. Ajouter 50 lignes de mouvements d'un coup : les plages des formules doivent les inclure.
5. Saisir une référence inexistante : la validation des données doit la refuser.
6. Passer une référence sous son seuil : la ligne doit s'allumer et apparaître dans les articles à commander.

Si une étape échoue, corrigez-la avant la mise en service : c'est exactement par là que les écarts s'installeront.

Les cinq causes classiques d'écart avec le stock réel

• Des mouvements non saisis (sorties d'atelier, casse, échantillons).
• Des doublons de référence : « JNT-50 » et « JNT50 » comptent séparément.
• Une double saisie du même mouvement par deux personnes.
• Un copier-coller qui écrase une formule par une valeur figée.
• Un inventaire physique jamais rapproché, donc un stock initial de plus en plus périmé.

Les trois premières se limitent par de la validation des données et un rappel de saisie ; les deux dernières par un rapprochement régulier et une régularisation passée en mouvement, jamais en écrasant une cellule.

Plusieurs personnes dans le même fichier : attention au mode de partage

C'est là que beaucoup de fichiers se dégradent. Deux mécanismes existent dans Excel, et ils n'ont pas les mêmes conséquences.

La co-édition permet à plusieurs personnes d'ouvrir et de modifier le même classeur, les modifications des autres étant visibles en quelques secondes. Elle suppose un abonnement Microsoft 365, une version récente d'Excel, un fichier au format .xlsx, .xlsm ou .xlsb, stocké sur OneDrive, OneDrive Entreprise ou une bibliothèque SharePoint Online — les sites SharePoint locaux ne la prennent pas en charge — et l'enregistrement automatique activé (documentation Microsoft). Elle donne aussi accès à l'historique des versions, précieux le jour où le stock devient incohérent (Microsoft).

Le classeur partagé est l'ancienne fonctionnalité, que Microsoft présente comme présentant de nombreuses limitations et remplacée par la co-édition. Son problème pour un suivi de stock est net : elle ne prend pas en charge la création ou l'insertion de tableaux, l'ajout ou la modification de formats conditionnels, l'ajout ou la modification de la validation des données, ni l'écriture, l'enregistrement ou la modification de macros (documentation Microsoft). Autrement dit : exactement les mécanismes sur lesquels repose un fichier de stock fiable. Si votre classeur est encore dans ce mode sur un lecteur réseau, c'est la première chose à changer.

Quand Excel suffit, et les signaux qui montrent qu'il ne suffit plus

Excel suffit largement dans beaucoup de situations : Microsoft indique qu'on peut y suivre stocks, commandes et ventes dans des feuilles de calcul liées qui se mettent à jour à partir des données de transactions (Microsoft). En pratique, un fichier bien construit tient sans difficulté quand une seule personne saisit, que les références se comptent en dizaines et que la journée n'est pas rythmée par des confirmations de disponibilité au client. Dans ce cas, ne cherchez pas plus loin : le coût et le temps d'un outil dédié ne se justifient pas.

Les signaux observables qui indiquent que le fichier arrive au bout, à vérifier chez vous plutôt qu'à croire sur parole :

• Deux personnes ou plus saisissent dans la même journée, et l'une attend que l'autre ferme le fichier.
• Vous devez confirmer une disponibilité à un client dans l'heure, à partir d'un fichier mis à jour le soir.
• Les écarts d'inventaire reviennent au même endroit à chaque comptage.
• Quelqu'un passe un temps significatif chaque semaine à « remettre le fichier d'aplomb ».
• Le réappro dépend de la mémoire d'une personne, et les ruptures se découvrent au moment de la commande client.

À noter : avant d'envisager un autre outil, vérifiez d'abord si le problème ne vient pas du mode de partage (classeur partagé au lieu de co-édition) ou de l'absence de validation des données. Ces deux corrections règlent une bonne partie des symptômes ci-dessus sans changer d'outil.

Si vous voulez aller plus loin que le fichier

Transparence : MistralJS édite ce blog et vend des outils sur mesure. Ce qui suit décrit notre propre offre ; prenez-le comme tel.

Si ces signaux sont présents chez vous et qu'un ERP n'est pas d'actualité, c'est le type de chantier sur lequel nous intervenons. Selon notre page d'offre, nous nous branchons sur ce qui fait foi chez vous — logiciel de gestion, caisse ou l'Excel de l'atelier —, l'outil apprend vos seuils et vos délais fournisseurs puis surveille en continu : quand une commande client arrive, il vérifie la disponibilité, génère le bon de préparation et confirme le délai ; quand un seuil est atteint, le bon de commande fournisseur est préparé et vous le validez (Stock & commandes — MistralJS). Toujours selon cette page, un fichier tenu à la main n'est pas bloquant : il sert de point de départ et l'occasion de nettoyer les références progressivement. Ces affirmations sont les nôtres, pas des mesures indépendantes.

La checklist des 8 points

1. Trois feuilles séparées : Articles, Mouvements, Tableau de bord.
2. Aucune quantité saisie en dur dans la feuille Articles.
3. Une ligne = un mouvement, avec date, sens, motif et auteur.
4. Validation des données sur la référence et sur le sens du mouvement.
5. Stock théorique en SOMME.SI.ENS, plages de même taille, critères texte entre guillemets.
6. Seuil d'alerte calculé (consommation moyenne × délai + marge), pas saisi une fois pour toutes.
7. Feuilles protégées sauf les colonnes de saisie ; partage en co-édition, pas en classeur partagé.
8. Inventaire physique rapproché à date fixe, écarts passés en mouvements de régularisation.

FAQ

Comment faire un suivi de stock sur Excel ? Microsoft décrit la démarche ainsi : partir d'un modèle de suivi d'inventaire ou créer le sien avec identifiants d'articles, descriptions, catégories et coûts ; utiliser la validation des données pour contrôler les types d'entrée ; marquer les articles au stock faible ou nul par mise en forme conditionnelle ; configurer un journal des transactions lié à la feuille d'inventaire ; organiser l'inventaire par fournisseur ou emplacement (source). Le calcul du stock se fait ensuite par somme conditionnelle des entrées moins les sorties.

Quel est le meilleur modèle gratuit de gestion de stock Excel ? Nous ne classons pas les modèles entre eux. Microsoft propose gratuitement plusieurs modèles de suivi des stocks dans Excel en ligne (Liste d'inventaires en stock, Liste d'inventaire moderne, Liste d'inventaire bleue, Liste des stocks d'équipement, entre autres), personnalisables et déjà équipés de mise en forme conditionnelle et de formules de calcul (source). Quel que soit le modèle choisi, faites-lui passer le test en six étapes ci-dessus.

Excel peut-il vraiment servir d'outil de gestion de stock ? Oui pour beaucoup de situations : Microsoft indique qu'Excel permet de suivre stocks, commandes et ventes dans des feuilles liées qui se mettent à jour à partir des données de transactions (source). La limite n'est généralement pas la formule, c'est l'organisation autour du fichier : nombre de saisissants, simultanéité, fréquence des rapprochements.

Comment déclencher le réapprovisionnement dans le fichier ? Deux logiques simples à mettre en place dans Excel. Soit un point de commande : une colonne seuil, comparée au stock théorique, avec une mise en forme conditionnelle qui allume la ligne dès que le stock passe dessous. Soit un rendez-vous calendaire : une revue à date fixe (chaque lundi, par exemple) où l'on complète chaque référence jusqu'à un niveau cible. Dans les deux cas, le fichier ne change pas de structure ; seule change la façon de déclencher l'alerte.

Plusieurs personnes peuvent-elles remplir le même fichier de stock en même temps ? Oui, via la co-édition, sous conditions : abonnement Microsoft 365, version récente d'Excel, format .xlsx/.xlsm/.xlsb et fichier hébergé sur OneDrive, OneDrive Entreprise ou SharePoint Online (documentation Microsoft). Évitez l'ancien mode « classeur partagé », qui ne prend en charge ni la création de tableaux, ni l'ajout de formats conditionnels, ni la validation des données (Microsoft).