Excel : inventaire de stock avec seuil et réappro
Créez dans Excel un inventaire de stock avec seuil d’alerte, valeur du stock et suggestion de réappro via SOMME.SI.ENS, RECHERCHEX et MFC.
- Difficulté
- Intermédiaire
Un bon inventaire Excel ne se limite pas à une liste d’articles. Il doit montrer le stock disponible, signaler les niveaux critiques et aider à décider quoi recommander.
Dans ce tutoriel, vous allez construire un fichier simple et robuste avec une table d’articles, un journal de mouvements, des calculs de stock via SOMME.SI.ENS, des recherches via RECHERCHEX et une alerte visuelle grâce à la mise en forme conditionnelle.
Prérequis
Préparez un classeur Excel avec au moins deux feuilles. Vérifiez aussi que votre version d’Excel prend en charge RECHERCHEX ; si ce n’est pas le cas, vous pourrez remplacer cette fonction par une combinaison INDEX/EQUIV, sans que cela soit détaillé ici.
Le tutoriel suppose que vous savez convertir une plage en tableau Excel et saisir une formule dans une colonne structurée.
Matériel nécessaire
- Un classeur Excel avec 3 feuilles : Articles, Mouvements et Tableau de bord
- Une liste d’articles avec code, libellé, prix unitaire, seuil d’alerte et stock cible
- Un journal de mouvements avec date, code article, type et quantité
Étapes
Créer la table des articles
Dans la feuille Articles, créez les colonnes suivantes : Code, Désignation, Prix unitaire, Seuil d’alerte et Stock cible. Sélectionnez la plage et convertissez-la en tableau Excel avec Ctrl + T.
Donnez un nom clair à ce tableau, par exemple tArticles. Chaque ligne doit représenter un article unique, identifié par un code sans doublon.
Créer le journal des mouvements
Dans la feuille Mouvements, créez les colonnes Date, Code, Type et Quantité. Convertissez aussi cette plage en tableau Excel et nommez-le par exemple tMouvements.
Dans la colonne Type, utilisez des valeurs cohérentes comme Entrée et Sortie. Saisissez ensuite chaque mouvement sur une ligne séparée, sans corriger directement le stock dans la feuille de synthèse.
Préparer le tableau de suivi
Dans la feuille Tableau de bord, créez une nouvelle table avec les colonnes Code, Désignation, Prix unitaire, Entrées, Sorties, Stock disponible, Seuil d’alerte, Stock cible, Valeur du stock et Réapprovisionnement suggéré.
Copiez dans la colonne Code la liste des codes de la table des articles. Convertissez cette synthèse en tableau Excel, par exemple tStock.
Récupérer les informations d’article avec RECHERCHEX
Dans la colonne Désignation de tStock, saisissez une formule RECHERCHEX qui cherche le code dans tArticles et renvoie la désignation. Le principe est le même pour Prix unitaire, Seuil d’alerte et Stock cible.
Par exemple, pour le prix unitaire, utilisez une formule de type
=RECHERCHEX([@Code];tArticles[Code];tArticles[Prix unitaire];""). Adaptez le nom des colonnes à votre tableau réel.Calculer les entrées avec SOMME.SI.ENS
Dans la colonne Entrées de tStock, additionnez les quantités du journal quand le code correspond à la ligne en cours et que le type vaut Entrée. Utilisez une formule de type
=SOMME.SI.ENS(tMouvements[Quantité];tMouvements[Code];[@Code];tMouvements[Type];"Entrée").Excel calcule alors, pour chaque article, la quantité totale entrée en stock. La formule s’étend automatiquement si la plage est bien sous forme de tableau.
Calculer les sorties avec SOMME.SI.ENS
Dans la colonne Sorties, appliquez le même principe en filtrant cette fois le type Sortie. Une formule possible est
=SOMME.SI.ENS(tMouvements[Quantité];tMouvements[Code];[@Code];tMouvements[Type];"Sortie").Vous obtenez ainsi le cumul des quantités sorties par article. Gardez la même logique de saisie dans le journal pour conserver des résultats fiables.
Calculer le stock disponible
Dans la colonne Stock disponible, soustrayez les sorties des entrées. Saisissez une formule simple comme
=[@Entrées]-[@Sorties].Le résultat donne le niveau de stock courant pour chaque article. Vous pouvez le formater en nombre sans décimales si vos quantités sont entières.
Calculer la valeur du stock
Dans la colonne Valeur du stock, multipliez le Stock disponible par le Prix unitaire. Utilisez une formule comme
=[@Stock disponible]*[@[Prix unitaire]].Appliquez ensuite un format monétaire à la colonne. Vous visualiserez immédiatement quels articles immobilisent le plus de valeur.
Calculer la suggestion de réapprovisionnement
Dans la colonne Réapprovisionnement suggéré, affichez une quantité à commander quand le stock disponible est inférieur au seuil. Une logique simple consiste à remonter jusqu’au Stock cible.
Vous pouvez saisir une formule de type
=SI([@Stock disponible]<[@[Seuil d’alerte]];[@[Stock cible]]-[@Stock disponible];0). Si le stock est suffisant, la cellule renvoie 0 ; sinon, elle propose la quantité manquante pour revenir au niveau cible.Mettre en évidence les articles à risque
Sélectionnez la colonne Stock disponible ou l’ensemble de la table de synthèse. Ouvrez la Mise en forme conditionnelle et créez une règle basée sur une formule.
Utilisez une logique qui compare le stock disponible au seuil d’alerte de la ligne. Par exemple, appliquez un format rouge lorsque le stock disponible est inférieur au seuil, afin de repérer rapidement les articles à traiter.
Ajouter un filtre pour traiter les réapprovisionnements
Activez les filtres sur la table tStock si ce n’est pas déjà fait. Filtrez ensuite la colonne Réapprovisionnement suggéré pour n’afficher que les valeurs supérieures à 0.
Vous obtenez une vue courte, exploitable immédiatement pour préparer une commande. Cette liste peut aussi être triée par quantité suggérée ou par valeur du stock.
Tester le fichier avec des cas simples
Saisissez quelques mouvements de test, par exemple une entrée de 20 unités puis une sortie de 5 unités pour un même article. Vérifiez que les colonnes Entrées, Sorties, Stock disponible, Valeur du stock et Réapprovisionnement suggéré réagissent comme prévu.
Testez aussi un article sans mouvement et un article passé sous le seuil. La mise en forme conditionnelle doit suivre le même raisonnement que la formule de réapprovisionnement.
Erreurs fréquentes & dépannage
L’erreur la plus fréquente consiste à saisir directement un stock courant au lieu d’enregistrer chaque entrée et chaque sortie dans le journal. Vous perdez alors la traçabilité et vos formules ne reflètent plus la réalité.
Autre piège courant : utiliser des libellés incohérents dans la colonne Type, comme entrée, Entrées ou sortie. SOMME.SI.ENS dépend de critères cohérents ; une variation suffit à fausser le résultat.
Enfin, vérifiez toujours les correspondances entre codes d’articles dans les différentes feuilles. Si RECHERCHEX ne trouve pas un code, les prix, seuils et désignations peuvent rester vides, ce qui fausse la valeur du stock et les alertes.
Astuces & pour aller plus loin
Gardez les données de référence dans Articles et les opérations dans Mouvements. Cette séparation rend le fichier plus simple à maintenir et réduit les ressaisies.
Si vous ajoutez souvent de nouveaux articles, commencez par compléter la table tArticles, puis reportez le code dans la synthèse. Les formules fondées sur RECHERCHEX et sur les références structurées suivront plus facilement.
Pour améliorer la lecture, appliquez un format monétaire à la valeur du stock et un format visuel distinct aux lignes sous seuil. Un tableau lisible se contrôle plus vite qu’un tableau seulement juste.
Questions fréquentes
Pourquoi utiliser deux tables au lieu d’un seul tableau de stock ?
La table des articles contient les données stables, tandis que le journal des mouvements enregistre les opérations. Cette séparation facilite les calculs, les corrections et la traçabilité.
À quoi sert RECHERCHEX dans cet inventaire ?
RECHERCHEX récupère automatiquement des informations comme la désignation, le prix unitaire, le seuil d’alerte ou le stock cible à partir du code article. Vous évitez ainsi de dupliquer ces données dans la feuille de synthèse.
Pourquoi calculer les entrées et sorties avec SOMME.SI.ENS ?
SOMME.SI.ENS additionne les quantités selon plusieurs critères, ici le code article et le type de mouvement. C’est une manière simple d’obtenir des totaux séparés pour les entrées et les sorties.
Comment définir la suggestion de réapprovisionnement ?
Dans ce tutoriel, la suggestion s’affiche quand le stock disponible passe sous le seuil d’alerte. La quantité proposée correspond à l’écart entre le stock cible et le stock disponible.
La mise en forme conditionnelle remplace-t-elle la colonne de réapprovisionnement ?
Non. Elle sert surtout à voir rapidement les articles à risque, tandis que la colonne de réapprovisionnement donne une quantité exploitable pour l’action.
Conclusion
Avec cette structure, votre inventaire Excel reste simple à alimenter et utile au quotidien. Vous saisissez les mouvements, Excel calcule le stock disponible, estime sa valeur et vous signale les articles à réapprovisionner.
La base peut ensuite évoluer selon vos besoins, par exemple avec plus de filtres, une vue d’achat dédiée ou des contrôles de saisie plus stricts. L’essentiel est déjà là : une logique fiable, lisible et facile à maintenir.