Réassortir les produits 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 :


Réassortiment des produits en stock

Connaître la population des articles en vente situés dans une tranche de stocks établie, est un enjeu important pour les gestionnaires désireux d'anticiper les décisions logistiques.



Dans l'exemple illustré par la capture ci-dessus, l'utilisateur émet des contraintes sur le stock minimum et le stock maximum. Aussitôt, des informations statistiques sont délivrées en fonction des enregistrements concordants dans la base de données. Dans le même temps, une extraction de ces articles est produite dans une zone placée juste en dessous du bilan.

Source et présentation du concept
Une base de données est nécessaire pour établir ces travaux. Nous proposons tout d'abord de la réceptionner.

• Télécharger le classeur reassortiment-stocks.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Le tableau de l'unique feuille réceptionnée référence donc les articles de l'entreprise. Cette base de données s'étend de la colonne B à la colonne H. La colonne B énumère les identifiants de chaque produit tandis que les stocks correspondants sont énumérés en colonne G.

Un tableau de bord est proposé sur la droite de cette base de données, entre les colonnes J et K. Des contraintes bornées doivent être émises sur les stocks en cellules K5 et K6. Selon ces conditions, un bilan statistique des articles concordants doit être dressé à partir de la cellule K9. De plus, l'extraction de ces derniers, sur un niveau de détail allégé, doit être entreprise à partir de la cellule J15.

Nous le verrons, nous parachèverons la solution avec une fonctionnalité intéressante. Au clic sur l'une des références extraites, nous pointerons directement sur l'enregistrement concordant dans la base de données. Pour cela, nous emprunterons des techniques que nous avons abouties lors de formations précédentes.

Calculer les seuils de stocks
Pour commencer, nous souhaitons nous soucier des deux premiers calculs proposés en cellules respectives K9 et K10. Il s'agit de révéler quels sont les stocks effectivement les plus petits et les plus grands, dans la tranche imposée par l'utilisateur. Et pour cela, nous proposons d'émettre ces contraintes.

• En cellule K5, saisir et valider la valeur 2,
• En cellule K6, saisir et valider la valeur 12,

Le calcul du premier résultat attendu consiste à dénombrer toutes les plus petites valeurs de stocks au moins supérieures ou égales à la contrainte plancher fournie. Le calcul du second résultat consiste à dénombrer toutes les plus grandes valeurs de stocks forcément inférieures ou égales à la condition plafond mentionnée. Dans les deux cas, la fonction Excel conditionnelle Si est nécessaire pour vérifier le critère. Et puis, pour cibler les valeurs seuilles, les fonctions Excel Min et Max doivent être employées. Mais comme c'est l'intégralité de la base de données qui doit être analysée, nous devons raisonner sur des matrices. Et pour simplifier la syntaxe, il est préférable de pouvoir les identifier avec des noms.

• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Comme vous pouvez le voir, toutes les colonnes du tableau ont hérité de leur titre. Si vous cliquez sur l'un d'entre eux comme produit_stock, l'intégralité de ses données est immédiatement sélectionnée. C'est par ces noms que nous ferons références aux matrices concernées dans les formules.

• Sélectionner le premier résultat à trouver soit la cellule K9,
• Taper le symbole égal (=) pour enclencher l'opération matricielle,
• Saisir la fonction pour la plus petite valeur, suivie d'une parenthèse, soit : Min(,
• Taper la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la matrice des stocks par son nom, soit : produit_stock,
• Taper le symbole supérieur suivi du symbole égal, soit : >=, pour le critère à poser,
• Sélectionner la contrainte du stock minimum en cliquant sur sa cellule K5,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Désigner de nouveau la matrice des stocks par son nom, soit : produit_stock,
• Taper un nouveau point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets pour conserver la cellule vide, soit : '',
• Fermer la parenthèse de la fonction Si,
• Puis, fermer la parenthèse de la fonction Min,
• Enfin, valider nécessairement le calcul par le raccourci clavier CTRL + MAJ + Entrée,

En effet, nous raisonnons sur des matrices tout en exploitant des fonctions de calcul classiques. Pour que le critère sur le stock soit analysé sur chacune des lignes de la colonne, nous devons donc transformer le calcul formule matricielle. Et à ce titre, dans la barre de formule de la cellule, vous notez l'apparition des accolades en préfixe et suffixe de la syntaxe :

{=MIN(SI(produit_stock>=K5; produit_stock; ''))}

Ce sont elles qui intiment l'ordre à Excel de raisonner sur des matrices. A chaque fois que le critère est satisfait, nous ne retenons que la plus petite valeur, grâce à la fonction Min, englobant le calcul. Le résultat obtenu est d'ailleurs supérieur au plancher défini. Aucun article ne possède un stock de deux unités. La valeur directement supérieure est donc extraite. Voilà encore une prouesse permise par les raisonnements matriciels.

De la même façon, nous souhaitons connaître le stock maximum effectif, selon le plafond défini. Le raisonnement est identique. Il s'agit de remplacer la fonction Min par la fonction Max et d'inverser l'inégalité du critère.

• En cellule K10, saisir la formule suivante : =MAX(SI(produit_stock<=K6; produit_stock; '')),
• Puis, la valider nécessairement par le raccourci clavier CTRL + MAJ + Entrée,

{=MAX(SI(produit_stock<=K6; produit_stock; ''))}



Là encore, le résultat diffère par rapport au plafond indiqué. Il n'existe pas d'articles à 12 unités en stock. La valeur directement inférieure recensée s'élève à 11 unités.

En définissant une valeur plancher supérieure à zéro, nous ignorons les stocks nuls considérant que ces produits ne se font plus. Avec les travaux à suivre, nous allons pouvoir comptabiliser et extraire les stocks faibles, compris dans cet intervalle dynamique, afin de traiter en urgence ces produits. Nous permettrons de fait un réassortiment des gammes dans les meilleures conditions.

Bien sûr, si vous modifiez ces contraintes en cellules K5 et K6, vous constatez que ces résultats statistiques s'actualisent immédiatement pour honorer les nouveaux seuils. De tels résultats auraient été bien plus difficiles à obtenir avec des formules classiques.

Dénombrements bornés des stocks
Les résultats attendus en cellules K11 et K12 sont plus classiques à obtenir, selon les techniques matricielles sur lesquelles nous nous sommes entraînés lors des formations précédentes. Tout d'abord, nous souhaitons connaître le nombre d'articles possédant le stock minimum effectif. Il suffit de poser un critère dans la fonction matricielle SommeProd. A chaque fois qu'il est vérifié, il est repéré par une valeur booléenne transcrite sous forme de chiffre (1). A l'issue, la fonction SommeProd réalise l'addition de ces unités repérées. De fait, elle offre le dénombrement escompté.

• Sélectionner la cellule K11 et taper le symbole égal (=) pour initier la formule matricielle,
• Saisir le nom de la fonction matricielle, suivi d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la matrice conditionnelle,
• Désigner la matrice des stocks par son nom, soit : produit_stock,
• Taper le symbole égal (=) pour la condition à respecter,
• Sélectionner le stock minimum effectif en cliquant sur sa cellule K9,
• Fermer la parenthèse de la matrice conditionnelle,
• Multiplier le tout par un (*1) pour transformer les valeurs booléennes en chiffres,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider la formule matricielle à l'aide de la touche Entrée du clavier,

La fonction SommeProd raisonne en effet naturellement sur des matrices. Chaque ligne est inspectée. En conséquence, le raccourci clavier qui était nécessaire précédemment n'est pas utile ici.

En retour, nous obtenons le nombre d'articles dont le stock correspond à la valeur minimum effective et calculée. La formule que nous avons construite est la suivante :

=SOMMEPROD((produit_stock=K9)*1)

L'idée est similaire pour le calcul suivant sauf que nous devons recouper deux critères. Il s'agit donc de spécifier deux matrices conditionnelles en respectant les contraintes du tableau de bord. Nous souhaitons connaître le nombre d'articles dont le stock est compris dans la fourchette située entre le stock minimum et le stock maximum renseignés.

• En cellule K12, saisir la formule suivante :

=SOMMEPROD((produit_stock >= K9)*(produit_stock <= K10))

Sur la première matrice, celle des stocks, nous recherchons la correspondance avec les articles dont le stock est supérieur ou égal au stock minimum effectif. Dans le même temps, sur la seconde matrice, toujours celle des stocks, nous recherchons la correspondance pour les quantités inférieures ou égales au stock maximum effectif. Du fait de ce croisement, la multiplication par 1 n'est plus nécessaire. Les indicateurs booléens sont naturellement transcrits en chiffres. De fait, chaque article appartenant à cette tranche est repéré par le chiffre 1 et tous les autres par le chiffre 0. L'addition finale réalisée par la fonction SommeProd produit ce dénombrement statistique affiné.

En modifiant les contraintes du tableau de bord, vous notez que tous les résultats s'actualisent naturellement.



Extraction bornée des produits en stock
Désormais, avant de réaliser l'extraction des produits appartenant à cette tranche, pour laquelle nous venons de définir le dénombrement, nous devons réaliser un calcul intermédiaire de repérage. Il s'agit de techniques que nous avons déjà largement exploitées, notamment au travers de la formation Excel pour extraire les données d'une référence.

Chaque article dont le stock est compris dans les bornes définies, doit être repéré par un numéro incrémenté. Pour vérifier les contraintes de stocks, nous devons croiser les conditions grâce à la fonction ET. Pour créer des numéros incrémentés, nous devons utiliser la fonction Max sur une plage de cellules qui grandit en même temps que le calcul se déplace. Ce sont ensuite ces numéros de repérage qui permettront l'extraction de tous les produits concordants.

• Sélectionner la cellule vide en regard du premier article, soit I4,
• Taper le symbole égal (=) pour initier la syntaxe du calcul,
• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Taper la fonction pour recouper les critères, suivie d'une parenthèse, soit : Et(,
• Désigner le premier stock à comparer en cliquant sur sa cellule G4,
• Taper le symbole supérieur suivi du symbole égal, soit : >=, pour le critère à suivre,
• Désigner alors le stock minimum effectif en cliquant sur sa cellule K9,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $K$9,

En effet, ce calcul de repérage est destiné à être reproduit sur les lignes du dessous. Ainsi, tous les stocks pourront être comparés. Mais cette comparaison doit immuablement se réaliser par rapport à cette valeur de référence. Sa cellule ne doit pas suivre le déplacement. Donc nous la figeons.

• Taper un point-virgule (;) pour poursuivre l'énumération des critères,
• Sélectionner de nouveau le premier stock, soit la cellule G4,
• Taper le symbole inférieur suivi du symbole égal, soit : <=, pour annoncer la condition,
• Désigner le stock maximum effectif en cliquant sur sa cellule K10,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $K$10,
• Fermer la parenthèse de la fonction Et,
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Saisir la fonction donnant la plus grande valeur suivie d'une parenthèse, soit : Max(,
• Cliquer sur la cellule située juste au-dessus du calcul, soit I3,
• Taper le symbole deux points (:) pour générer la plage I3:I3,

Au départ, cette plage ne désigne donc qu'une seule cellule. Nous devons la configurer pour qu'elle grandisse en même temps que le calcul est répliqué sur les lignes du dessous.

• Cliquer entre le I et le 3 de la première référence,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $I$3:I3,
• Cliquer ensuite à la fin de la syntaxe pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction Max,
• Ajouter une unité à ce résultat calculé, soit : +1,

C'est ainsi que l'incrémentation est rendue possible. Chaque valeur maximale trouvée sur une plage extensible est incrémentée d'une unité. Elle devient à son tour la donnée la plus grande pour le prochain repérage, et ainsi de suite.

• Taper un point-virgule (;) pour passer dans la branche Sinon de la fonction Si,
• Saisir deux guillemets pour garder la cellule vide en cas de non correspondance,
• Fermer la parenthèse de la fonction Si,
• Puis, valider la formule avec le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette technique permet de garder active la cellule du résultat pour l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée de la cellule du résultat,

De cette façon, nous reproduisons la logique du calcul sur la hauteur du tableau.



Comme vous le remarquez, des numéros effectivement incrémentés apparaissent, mais pas sur toutes les lignes. Selon la double condition que nous avons engagée, ils repèrent uniquement les produits dont le stock est compris dans la fourchette définie par le tableau de bord. Il conviendrait de les formater avec une couleur de police claire. Ils servent en effet d'intermédiaires seulement.

La formule que nous avons créée est la suivante :

=SI(ET(G4>=$K$9; G4<=$K$10); MAX($I$3:I3)+1; '')

Désormais, pour produire l'extraction, nous devons imbriquer les fonctions Excel Index et Equiv. La fonction Index permet d'extraire la donnée située au croisement d'une colonne et d'une ligne dans une base de données :

=Index(Base_de_données; ligne; colonne)

La colonne dépend de l'information que nous souhaitons extraire. La ligne dépend de la position de ces numéros incrémentés. Elle doit donc être calculée dynamiquement grâce à la fonction Equiv :

=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_correspondance)

Pour gérer les anomalies en cas de recherche infructueuse, nous allons englober la syntaxe dans la fonction SiErreur. Les premières données à extraire concernent les références des produits.

• Sélectionner donc la cellule J15,
• Taper le symbole égal (=) pour débuter le calcul,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit : SiErreur(,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des références en guise de base de données, soit : produit_ref,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,
• Saisir la fonction donnant la ligne d'une recherche, suivie d'une parenthèse, soit : Equiv(,
• Saisir la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,
• Désigner une cellule de la première ligne, par exemple A1,
• Fermer la parenthèse de la fonction Ligne,

Grâce à elle, au fil de la réplication du calcul vers le bas, nous chercherons bien la position du chiffre 1, puis du 2, du 3 etc...

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Cliquer sur l'étiquette de colonne I des numéros incrémentés, ce qui donne : I:I,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Retrancher trois unités à ce résultat, soit : -3,

En effet, l'énumération des références débute en ligne 4 tandis que nous effectuons la recherche des numéros à partir de la première ligne. Pour que l'extraction pointe sur le bon élément, nous devons corriger ce décalage.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Saisir le chiffre 1 et fermer la parenthèse de la fonction Index,

La recherche est effectuée uniquement dans la colonne des références. La donnée à extraire ne peut donc être située que dans cette colonne, reconnue numériquement comme la première.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Saisir deux guillemets pour garder la cellule vide en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule par le raccourci clavier CTRL + Entrée,
• Tirer la poignée du résultat sur quelques dizaines de lignes vers le bas,

Toutes les références dont le stock est compris dans la fourchette définie sont parfaitement extraites. Si vous diminuez l'écart entre le plafond min et le plafond max, vous constatez rapidement que le nombre d'extractions est fidèle au dénombrement produit par le calcul matriciel.

Pour les informations à extraire sur le prix et le stock, il suffit simplement d'adapter la colonne de recherche dans la fonction Index.



Grâce à ces trois calculs répliqués :

=SIERREUR(INDEX(produit_ref; EQUIV(LIGNE(A1); I:I; 0)-3; 1); '')
=SIERREUR(INDEX(produit_prix; EQUIV(LIGNE(A1); I:I;0)-3; 1); '')
=SIERREUR(INDEX(produit_stock; EQUIV(LIGNE(A1); I:I; 0)-3; 1); '')

Nous obtenons l'extraction des produits correspondants sur le niveau de détail demandé.

Pour plus d'ergonomie, nous avons aussi la possibilité de créer des liens hypertextes dynamiques. L'idée est de pointer sur l'enregistrement dans la base de données au clic sur l'une des références extraites :

=Lien_Hypertexte(Adresse_à_atteindre; Texte_à_afficher)

Le précédent calcul doit intervenir dans l'argument du texte à afficher pour cette fonction. Pour reconstruire l'adresse de la cellule correspondante à atteindre, il suffit d'exploiter la fonction Excel Adresse :

=Adresse(Ligne; Colonne).

En cellule J15, il convient donc d'adapter la formule comme suit :

=SIERREUR(LIEN_HYPERTEXTE('#' & ADRESSE(EQUIV(LIGNE(A1); I:I; 0); 2); INDEX(produit_ref; EQUIV(LIGNE(A1); I:I; 0)-3; 1) ); '')

Le symbole dièse concaténé en préfixe de l'adresse est une astuce. C'est lui qui permet de pointer en interne, soit sur une cellule de la feuille, selon les coordonnées transmises. Ces coordonnées sont recalculées dynamiquement grâce à la fonction Adresse. Pour cela, nous repérons de nouveau la position des numéros incrémentés dans la colonne I. Nous lui fournissons donc l'indice dynamique de ligne. L'indice de colonne est connu quant à lui. Il est fixe. Il s'agit de la colonne B, soit de la deuxième.

Identifier les articles à réassortir
Comme souvent, pour renforcer l'impact offert par les calculs de synthèse et les extractions, nous proposons d'identifier visuellement les articles ciblés. Ils doivent surgir dynamiquement sur un fond de couleur explicitement différent, au gré des contraintes modifiées depuis le tableau de bord.

Nous devons créer une règle de mise en forme conditionnelle à appliquer sur l'ensemble des données de la base. Cette règle doit s'assurer que le stock de l'article est compris entre les bornes des stocks effectifs calculés.

• Sélectionner l'intégralité des données de la base, soit la plage de cellules B4:H247,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie juste en dessous pour l'activer,
• Dès lors, taper la syntaxe suivante :

=ET($G4>=$K$9; $G4<=$K$10)

Une mise en forme conditionnelle raisonne chronologiquement. Ainsi, nous posons les critères sur le stock du premier article, soit la cellule G4. Comme nous libérons cette dernière en ligne, tous seront passés en revue. La colonne reste figée quant à elle. En effet, pour chaque article, soit chaque ligne, le stock est placé en colonne G. La comparaison s'effectue par rapport aux stocks effectifs issus du tableau de bord. Ces cellules (K9 et K10) sont nécessairement figées pour ne pas suivre le déplacement de l'analyse chronologique. Comme ces deux conditions sont énumérées dans la fonction Et, elles doivent être recoupées. Les enregistrements concordants doivent surgir dans des attributs de format spécifiques.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu pâle,
• Dans l'onglet Police, choisir un rouge foncé pour le texte,
• Valider ces attributs de format et valider la création de la règle de mise en forme,