Dans cet article, nous allons apprendre à créer un tableau de gestion de stock sur Excel permettant de calculer automatiquement les quantités disponibles à partir des entrées et des sorties de produits.
Grâce à la fonction SOMME.SI, Excel additionne les quantités entrées et sorties pour chaque produit, puis calcule automatiquement le stock restant.
Ce guide vous permettra de créer un tableau simple, pratique et facile à utiliser pour suivre les mouvements de vos produits.
1. Créer le tableau des entrées et des sorties voir le modèle ci-dessous :
2. Créer une liste déroulante pour les produits
Pour faciliter la saisie des mouvements, vous pouvez créer une liste déroulante dans la colonne « Désignation ».
Étapes à suivre
- Préparez la liste des produits dans une zone de votre feuille Excel.
- Sélectionnez les cellules dans lesquelles vous souhaitez choisir les produits.
- Cliquez sur l’onglet Données.
- Sélectionnez Validation des données.
- Dans « Autoriser », choisissez Liste.
- Dans « Source », sélectionnez la plage contenant les désignations.
- Cliquez sur « OK ».
Une liste déroulante apparaît dans les cellules sélectionnées. Vous pouvez alors choisir directement un produit sans devoir saisir son nom à chaque fois.
Prévoyez suffisamment de lignes dans la plage de destination afin de pouvoir enregistrer de nouveaux mouvements( notre exemple contient 18 lignes)
Vous allez compléter remplir le tableau avec des données à titre d’exemple( exemple ci-dessous):
3. Calculer automatiquement le stock disponible avec SOMME.SI
La fonction SOMME.SI permet d’additionner des quantités selon un critère précis. Dans notre exemple, le critère est la désignation du produit.
Sa syntaxe est la suivante :
=SOMME.SI(plage;critère;somme_plage)
- Plage : la colonne dans laquelle Excel recherche le produit.
- Critère : le nom du produit à rechercher.
- Somme_plage : la colonne contenant les quantités à additionner.
Calculer le total des entrées
total des entrées =SOMME.SI($B$3:$B$20;G3;$C$3:$C$20)
Excel recherche le produit indiqué en G3 dans la colonne B et additionne les quantités correspondantes dans la colonne C.
Les signes $ permettent de conserver les mêmes plages lorsque vous recopiez la formule vers le bas.
Calculer le total des sorties
Pour additionner les quantités sorties du même produit, utilisez :
=SOMME.SI($E$3:$E$20;G3;$F$3:$F$20)
Cette formule recherche le produit dans la colonne E, puis additionne les quantités correspondantes dans la colonne F.
Ainsi, même si un produit apparaît plusieurs fois dans le tableau des mouvements, Excel calcule le total de ses entrées et de ses sorties.
Calculer le stock disponible
Une fois les totaux calculés, le stock disponible = entrées de stocks- sorties de stocks
Valeur du stock disponible =SOMME.SI($B$3:$B$20;G3;$C$3:$C$20)-SOMME.SI($E$3:$E$20;G3;$F$3:$F$20)
Le résultat correspond au stock disponible pour le produit concerné.
Par exemple Clavier :
- Entrées : 100
- Sorties : 10
- Stock disponible : 90
Pour les autres produits, recopiez les formules vers le bas ( incrémenter la cellule). Le résultat se mettra à jour lorsque vous ajouterez ou modifierez les mouvements enregistrés dans les plages couvertes par les formules.
Lorsqu’un produit possède des entrées mais aucune sortie, le stock disponible correspond au total des entrées enregistrées.
Si aucune entrée ni sortie n’est enregistrée pour un produit, le résultat du calcul est zéro.
Cette méthode permet de regrouper les mouvements par désignation et d’obtenir rapidement les quantités restantes.
4. Conseils pour utiliser correctement le tableau
Pour limiter les erreurs et faciliter le suivi des stocks :
- Utilisez la liste déroulante pour conserver des désignations cohérentes.
- Vérifiez que les quantités entrées sont enregistrées dans la bonne colonne.
- Vérifiez que les quantités sorties sont correctement saisies.
- Assurez-vous que les plages utilisées dans SOMME.SI couvrent toutes les lignes nécessaires.
- Recopiez les formules pour les différents produits du tableau récapitulatif.
Si vous ajoutez des mouvements au-delà de la ligne 100, adaptez les plages des formules ou utilisez un tableau Excel qui s’étend automatiquement.
Conclusion
Avec Excel, il est possible de créer un tableau de gestion de stock simple qui calcule automatiquement les quantités disponibles à partir des entrées et des sorties.
La liste déroulante facilite la saisie des produits, tandis que la fonction SOMME.SI permet de calculer les totaux pour chaque désignation. Enfin, une simple soustraction donne le stock disponible.
Pour voir les manipulations directement dans Excel, regardez la vidéo intégrée à cet article et reproduisez les étapes dans votre propre fichier.
📥 Téléchargez le fichier Excel utilisé dans ce tutoriel pour reproduire les étapes et tester le calcul automatique des entrées, des sorties et du stock disponible.
Découvrez également notre guide complet sur la fonction INDEX EQUIV dans Excel pour apprendre à rechercher des données facilement.