Colorer un suivi de stock automatiquement sur Excel
Un exercice corrigé pour maîtriser la mise en forme conditionnelle sur un suivi de stock, alertes rouges, barres de données et jeux d'icônes, avec le fichier Excel à télécharger.
Repérer une rupture de stock dans un tableau de chiffres bruts, c'est fastidieux, on parcourt les lignes une à une et on finit par rater la référence critique. La mise en forme conditionnelle fait justement ce travail à ta place, en colorant les cellules selon des règles dès qu'une valeur franchit un seuil. On va la dompter pas à pas !
L'objectif est d'apprendre à transformer un tableau en alerte visuelle et à hiérarchiser plusieurs règles qui se superposent sur la même plage. C'est le réflexe qui rend un suivi lisible en un coup d'œil, on ne lit plus une grille de nombres, on voit immédiatement ce qui cloche.
Ce que tu vas construire
- Créer une règle de mise en forme conditionnelle basée sur une formule pour comparer deux colonnes ligne par ligne.
- Utiliser une référence mixte ($B2) pour que la règle s'applique correctement sur toute une plage.
- Ajouter des barres de données pour comparer visuellement les stocks entre les références.
- Appliquer un jeu d'icônes feu tricolore et comprendre sur quoi reposent ses seuils.
- Gérer l'ordre des règles et comprendre l'option « Interrompre si Vrai ».
À connaître avant de commencer
- Savoir sélectionner une plage de cellules.
- Avoir déjà ouvert le menu Accueil puis Mise en forme conditionnelle au moins une fois.
Voici les données de départ de cet exercice. Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | |
|---|---|---|---|
| 1 | Référence | Stock actuel | Stock mini |
| 2 | REF-001 | 4 | 10 |
| 3 | REF-002 | 25 | 10 |
| 4 | REF-003 | 8 | 15 |
| 5 | REF-004 | 40 | 20 |
| 6 | REF-005 | 12 | 12 |
| 7 | REF-006 | 18 | 5 |
| 8 | REF-007 | 30 | 35 |
| 9 | REF-008 | 6 | 5 |
| 10 | REF-009 | 36 | 12 |
| 11 | REF-010 | 9 | 20 |
| 12 | REF-011 | 22 | 25 |
| 13 | REF-012 | 20 | 8 |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
Colore toute la ligne, pas seulement la cellule
Pour que la règle =$B2<$C2 s'applique à toute la ligne (pas seulement à la colonne B), sélectionne la plage complète du tableau (par exemple A2:C13) avant de créer la règle. La colonne B est figée ($B), la ligne est relative (2), ce qui permet de comparer chaque ligne à son propre seuil.
Combine mise en forme conditionnelle et fonction SI
Pour afficher en plus un statut texte dans une colonne, utilise =SI($B2<$C2;"À commander";"OK"). La mise en forme conditionnelle colore visuellement, la formule SI fournit un libellé exploitable dans des filtres ou des comptages avec NB.SI.
Colore les statuts texte avec la règle Texte qui contient
La mise en forme conditionnelle s'applique aussi à du texte, sans formule. Si tu as ajouté la colonne de statut de l'astuce précédente, sélectionne-la de D2 à D13, va dans Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules > Texte qui contient, tape À commander et choisis un remplissage rouge. Les cinq références sous leur minimum, REF-001, REF-003, REF-007, REF-010 et REF-011, ressortent dès que la formule SI écrit leur statut, et la règle marche tout autant sur un statut tapé à la main, puisqu'elle lit le texte affiché dans la cellule, où qu'il se trouve, et non la formule.
S'entraîner sur 3 exercices similaires à colorer un suivi de stock automatiquement
Planning de production
Créer un planning de production qui calcule automatiquement les dates de livraison en tenant compte des jours ouvrés, des contraintes de capacité et des priorités.
Voir l'exercice
Gestion des stocks et réappro
Construire un tableau de gestion des stocks qui calcule le stock disponible, déclenche les alertes de réappro et identifie les surstocks. Gérer 200 références sans stress.
Voir l'exercice
Traiter des données avec une condition If
Faire réagir ta macro selon la valeur des cellules : une condition If dans une boucle qui marque et colore les références sous le seuil de stock.
Voir l'exercice
Tu as peut-être encore une question
Va dans Accueil > Mise en forme conditionnelle > Nouvelle règle. Choisis le type de règle (valeur supérieure à, texte contenant, formule...), définis la condition et le format à appliquer (couleur de fond, couleur de texte, bordure). Excel colore automatiquement les cellules qui satisfont la condition.
Sélectionne la plage, puis Accueil > Mise en forme conditionnelle > Règles de mise en surbrillance des cellules. Choisis la comparaison (inférieur à, supérieur à, égal à...) et saisis la valeur seuil. Pour des conditions plus complexes, passe par « Nouvelle règle » et l'option qui utilise une formule.
Sélectionne toute la plage du tableau (toutes les colonnes), pas seulement la colonne testée. Dans la règle, utilise une formule avec une référence mixte comme =$B2<$C2, qui fige la colonne B ($B) mais laisse la ligne relative (2). La règle s'applique à toute la ligne en comparant toujours les colonnes B et C.
Pour supprimer une règle précise, va dans Accueil > Mise en forme conditionnelle > Gérer les règles, sélectionne la règle et clique sur Supprimer. Pour effacer toutes les règles d'un coup, passe par Accueil > Mise en forme conditionnelle > Effacer les règles > Effacer les règles des cellules sélectionnées (ou de la feuille entière).
Une mise en forme conditionnelle par rapport à une autre cellule passe par une règle de type « Utiliser une formule », où le test compare la cellule à colorer à la cellule de référence, comme =$B2<$C2 pour comparer le stock au minimum de la même ligne, ou =$B2<$F$1 pour le comparer à un seuil unique écrit en F1. Les dollars décident de ce qui bouge quand Excel applique la règle à toute la plage, $C2 suit la ligne et $F$1 reste fixe. Si la couleur tombe sur les mauvaises lignes, c'est presque toujours que la première cellule sélectionnée ne correspond pas à la ligne écrite dans la formule.
Plusieurs conditions dans une mise en forme conditionnelle s'écrivent soit dans une seule règle à formule avec ET ou OU, comme =ET($B2<$C2;$D2="Oui") si une colonne D dit quelles références sont encore commandées, soit dans plusieurs règles empilées, chacune avec sa couleur. Dans le second cas l'ordre compte, la règle du haut l'emporte quand deux règles changent le même format, et la case « Interrompre si Vrai » arrête les règles suivantes sur les cellules déjà traitées. Une règle avec ET reste plus facile à relire qu'une pile de règles, tant qu'elle tient sur une ligne.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de gestion de stock
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule