Alerte automatique sur les quantités en stock avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Alerte automatique sur les quantités en stock

Nous proposons ici d'améliorer l'application de facturation Excel sans code VBA. Il s'agit d'ajouter une fonctionnalité permettant d'alerter automatiquement et visuellement l'utilisateur, lorsque la quantité d'article commandée dépasse sa quantité disponible en stock. Nous avions bâti l'application VBA Excel permettant de monter la facturation avec gestion des stocks et édition de la facture. Mais ici, il n'est pas question de code VBA.



Comme l'illustre la capture ci-dessus de l'application finalisée, un format dynamique se déclenche automatiquement, lorsque la capacité en stock est dépassée. Il consiste à remplir toute la ligne de l'article, d'une couleur de fond sans équivoque.

Source et présentation du concept
Nous proposons de débuter les travaux à partir de l'application de facturation que nous avions déjà construite.

• Télécharger le fichier format-dynamique-stocks.xlsm en cliquant sur son lien,
• Puis, l'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est constitué de deux feuilles. La feuille articles propose un petit catalogue des produits disponibles à la vente. Cette base dedonnées est volontairement restreinte pour simplifier les manipulations. On y retrouve la référence de l'article, sa désignation, son prix unitaire hors taxe ainsi que sa quantité disponible en stock.

La feuille facturation se nourrit de ses informations pour bâtir la facture. Ce sont des fonctions d'extraction de base de données, qui permettent de restituer les éléments de l'article choisi par l'utilisateur en colonne C.

• Cliquer sur l'onglet facturation en bas de la fenêtre Excel pour activer sa feuille,
• Dans la facture, cliquer sur la première cellule du code article, soit C5,

Comme vous le constatez, une liste déroulante apparaît et permet de choisir l'un des articles proposés dans le catalogue. Nous simulons ainsi l'action du code à barre qui consiste à scanner une référence.

• Cliquer sur la flèche de la liste déroulante pour déployer son affichage,
• Dans la liste, choisir la première référence, soit b001,

Instantanément, la désignation et le prix correspondants sont rapatriés sur la même ligne, respectivement en colonne D et F. Si vous cliquez par exemple en cellule D5 pour la désignation, la barre de formule livre le calcul d'extraction en vigueur.



Comme l'illustre la capture ci-dessus, c'est la fonction Excel RechercheV qui permet de retourner la désignation correspondant au code article, depuis le catalogue. Le principe est le même en colonne F pour récupérer le prix. C'est seulement la colonne de retour, issue de la base de données qui change dans la formule. Néanmoins ces calculs considèrent un catalogue figé. Nous nous basons sur le cas le plus simple. Mais nous savons considérer une base de données évolutive pour intégrer les nouvelles données saisies, grâce à l'imbrication des fonctions Decaler et NbVal, pour déplacer les bornes des plages de cellules.

• En cellule E5, saisir la quantité 1,

Comme vous le constatez, le montant hors taxe se calcule automatiquement en G5 en fonction du prix unitaire et de la quantité commandée.

• En C6, choisir désormais la référence b003 à l'aide de la liste déroulante,
• En E6, taper la quantité 2,
• En C7, ajouter la référence b007,
• En E7, saisir la quantité 8,

La facture se construit parfaitement. Tous les montants hors taxes sont automatiquement calculés pour chaque article en fonction de sa quantité. De même, le montant total est parfaitement consolidé, en bas de la facturation, avec prise en compte de la TVA.



Mais un problème persiste. Dans le cas d'une commande en ligne par exemple, rien n'empêche de dépasser la quantité disponible en stock.

• En E7 pour la référence b007, saisir la quantité 12 par exemple,

La nouvelle quantité est bien considérée. Le montant HT pour l'article est bien calculé et le total TTC de la facture est consolidé. Pourtant, comme l'indique le catalogue, la quantité disponible en stock pour cette référence n'est que de 11 unités.

Alerte dynamique sur les stocks
A défaut de bloquer la facturation pour empêcher la commande, comme nous l'avions fait grâce au code VBA Excel, nous proposons de déclencher des alertes visuelles. L'objectif consiste à alerter l'utilisateur pour qu'il puisse réajuster les quantités commandées en fonction des quantités disponibles en stock.

En d'autres termes, des indicateurs de couleur doivent surgir sous certaines conditions. Il s'agit de bâtir une mise en forme conditionnelle contrôlée par une formule, comme nous l'avions fait dans la formation pour comparer les résultats des chiffres d'affaires. Le raisonnement est le suivant : Si la quantité demandée pour la ligne de l'article sur la facture, est supérieure à sa quantité en stock, repérée par la référence du produit, alors la couleur de remplissage doit dynamiquement basculer sur un rouge vif. Le critère de la mise en forme conditionnelle doit être vérifié grâce à la fonction Excel RechercheV. Cette dernière permet en effet de récupérer la quantité dans le catalogue en fonction du code article. Cette quantité peut donc être comparée à celle inscrite sur la facture. La syntaxe de la fonction RechercheV est la suivante :

RechercheV(Element_cherché ; Tableau_de_recherche ; numero_de_colonne ; Faux)

L'élément à chercher pour trouver la quantité est le code article du produit facturé. Le tableau de recherche est celui du catalogue situé dans la feuille articles. Le numéro de colonne est la quatrième (4) pour récupérer la quantité correspondante. Le dernier argument booléen Faux, permet de réaliser une recherche selon une correspondance exacte.

• Sélectionner l'ensemble des cellules de la facture, soit la plage C5:G25,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le dernier type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie située juste en dessous pour définir le critère,
• Taper le symbole = pour débuter la formule,
• Cliquer sur la première cellule de la quantité, soit E5,
• Enfoncer deux fois la touche F4 du clavier pour ne la figer qu'en colonne, soit : $E5,

En effet, le critère d'une mise en forme conditionnelle réagit relativement à la sélection des cellules. Il les parcourt dans le sens où elles ont été désignées. Pour chaque ligne, il s'agit de comparer la quantité demandée avec celle définie en stock. Donc chaque quantité doit être analysée. Donc la cellule doit pouvoir se déplacer de ligne en ligne au fur et à mesure de l'analyse par le format dynamique. En revanche, pour une même ligne, la quantité est indiquée seulement en colonne E. Donc la cellule du critère ne doit pas changer de colonne pour affecter l'ensemble de la ligne.

• Taper le symbole supérieur (>) pour la comparaison,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit : RechercheV(,
• Sélectionner la référence à trouver pour importer la quantité, soit C5,
• Enfoncer deux fois de suite la touche F4 du clavier pour ne la figer qu'en colonne, pour les mêmes raisons que précédemment, ce qui donne : $C5,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Cliquer sur l'onglet articles en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner tout le catalogue, soit la plage de cellules C6:F15 et la figer, ce qui donne : articles!$C$6:$F$15,

Nous conservons la plage figée. Les bornes du tableau de recherche ne doivent pas se déplacer en même temps que l'analyse du critère change de ligne et de colonne.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de colonne,
• Saisir le chiffre 4 pour indiquer que la quantité se situe dans la quatrième colonne de la sélection,
• Taper un nouveau point-virgule (;) suivi du texte Faux, soit ;Faux,
• Enfin, fermer la parenthèse de la fonction RechercheV.

Nous venons de bâtir le critère de la mise en forme conditionnelle. Il consiste effectivement à vérifier que la quantité demandée pour l'article en cours est supérieure à la quantité en stock pour la référence de cet article dans le catalogue. S'il est vérifié, la quantité ne peut être honorée. Donc un remplissage rouge dynamique doit se déclencher pour l'ensemble de la ligne concernée. Il s'agit d'associer ce format à ce critère.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la nouvelle boîte de dialogue qui suit, activer l'onglet Remplissage,
• Choisir un rouge foncé dans la palette de couleurs,
• Puis, activer l'onglet Police,
• Définir un texte gras dans un gris très clair,
• Valider ces réglages pour revenir sur la première boîte de dialogue,

La synthèse fournie par la règle de mise en forme conditionnelle est explicite. Si la quantité commandée est supérieure à la quantité disponible en stock, un remplissage rouge se déclenche automatiquement sur toute la ligne concernée.

• Valider cette règle de format dynamique en cliquant de nouveau sur Ok,

De retour sur la feuille, vous constatez que la ligne entière pour la référence b007 est mise en valeur. Cette alerte ne souffre aucune ambiguïté. Elle indique à l'utilisateur que la quantité demandée n'est pas disponible. En effet comme nous l'avions remarqué précédemment, seulement 11 unités sont proposées pour cette référence.

La formule que nous avons bâtie pour déclencher ce format dynamiquement, est la suivante :

=$E5>RECHERCHEV($C5; articles!$C$6:$F$15; 4; FAUX)



Si vous continuez de renseigner la facture, comme le propose la capture ci-dessus, à chaque fois que la capacité d'un article est dépassée, une alerte dynamique est déclenchée.

Renseigner sur la quantité en stock
Pour parfaire l'application et même si les zones et la mise en forme ne sont pas prévues, il peut s'avérer judicieux de fournir l'indication sur la quantité disponible, lorsque la quantité demandée dépasse le plafond. Ainsi l'utilisateur pourra conseiller le client et ajuster les commandes en fonction des stocks. Nous proposons de bâtir ce calcul d'extraction en colonne H, en regard de chaque ligne potentiellement incriminée.

Il s'agit de poser le raisonnement suivant : Si la quantité demandée est supérieure à la quantité en stock pour l'article reconnu par sa référence, alors il faut afficher la quantité disponible. Le même calcul que celui qui a été utilisé pour le format dynamique, doit être exploité dans la zone de critère d'une fonction Excel conditionnelle Si.

Si(Critère_à_vérifier ; Quoi_faire_Alors ; Quoi_faire_Sinon)

Si ce critère est vérifié, alors la quantité en stock pour l'article en cours doit être affichée. Donc la même extraction par la fonction RechercheV doit être réalisée. Dans le cas contraire, la cellule doit rester muette pour indiquer que la commande est conforme. Enfin, l'intégralité du calcul doit être englobée dans une fonction SiErreur. En effet, lorsque l'élément cherché n'est pas trouvé par les fonctions d'extractions, ces dernières retournent des erreurs dans les cellules. Or, le calcul doit être répliqué sur la hauteur de la facture qui sera rarement renseignée jusqu'à la dernière ligne.

• En cellule H5, saisir la formule suivante :

=SIERREUR(SI(E5>RECHERCHEV(C5; articles!$C$6:$F$15; 4; FAUX); RECHERCHEV(C5; articles!$C$6:$F$15; 4; FAUX);'');'')

Nous exploitons exactement la même recherche que précédemment pour vérifier le critère, en basant le calcul sur la première quantité à vérifier :
E5>RECHERCHEV(C5; articles!$C$6:$F$15; 4; FAUX)

Il n'est pas nécessaire cette fois de figer la référence de la cellule cherchée (C5) puisque le calcul est destiné à être répliqué en lignes et non en colonnes. La remarque est identique en ce qui concerne la quantité (E5). Si ce critère est vérifié, la RechercheV est de nouveau exploitée, strictement à l'identique, mais cette fois pour produire l'extraction de la quantité en stock, dans la cellule du calcul. Dans le cas où le critère n'est pas validé, la quantité en stock n'étant pas dépassée, nous conservons la cellule vide grâce à l'inscription de deux guillemets (''). De la même façon, dans la branche de la gestion d'erreur de la fonction SiErreur, si la recherche est infructueuse, nous conservons la cellule vide ('').

• Valider ce calcul par le raccourci clavier CTRL + Entrée,
• Puis, double cliquer sur la poignée de la cellule pour le répliquer sur la hauteur de la facture,

Comme l'illustre la capture ci-dessus, les améliorations que nous avons apportées à l'applicationde facturation Excel, fonctionnent parfaitement et sont précieuses. D'une part, lorsque la quantité en stock n'est pas suffisante pour honorer la commande, un indicateur visuel sans ambiguïté cible la ligne entière de l'article à corriger. Et d'autre part, lorsque cette capacité est dépassée, l'indication sur la quantité disponible vient se greffer en regard de la ligne, pour que l'utilisateur puisse conseiller le client sur les ajustements à envisager.

D'ailleurs si vous corrigez les deux lignes incriminées dans la facture présentée ci-dessus, la couleur rouge de la mise en forme conditionnelle disparaît, et l'indication sur la quantité disponible s'efface.