Synthèse des 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 :


Statistiques sur produits en stock

Lorsque les entreprises sont amenées à gérer une grande quantité de produits, il est particulièrement intéressant de pouvoir livrer des statistiques efficaces sur les articles effectivement en stock.



Dans l'exemple illustré par la capture ci-dessus, nous réalisons l'extraction des produits sur une base de données assez volumineuse. Et cetteextraction isole uniquement les articles dont la quantité en stock n'est pas nulle. Pour cela, nous n'exploitons aucun calcul de repérage intermédiaire. C'est encore une fois une unique formule matricielle qui offre cette prouesse. En entête du tableau de synthèse, des dénombrements permettent de visualiser rapidement le différentiel entre les produits référencés et ceux effectivement disponibles.

Source et présentation de la problématique
Ces informations de base de données sont nécessaires pour déployer les solutions.

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

Les produits de la société sont référencés dans un tableau constitué de plus de 200 lignes. Il s'étend de la colonne B à la colonne H. On y trouve le détail notamment sur le prix et le stock. Un petit tableau de synthèse est présent sur sa droite entre les colonnes J et L. Nous devons réaliser l'extraction des articles effectivement en stock, à partir de la ligne 8.

Pour des interprétations simples et rapides, des dénombrements pertinents sont donc attendus en haut du tableau de synthèse, dans les cellules K5 et M5. Le premier résultat doit renseigner sur le nombre total d'articles archivés en base de données. Le second calcul doit lui confronter le nombre de produits réellement disponibles.

En déployant la zon Nom en haut à gauche de la feuille Excel, vous notez que chaque colonne est identifiée par son titre.



Nous exploiterons ces noms dans la construction des formules.

Dénombrer les articles
Compter les produits de la base de données est un jeu d'enfant. La fonction NbVal dénombre toutes les lignes non vide d'une plage de cellules. Il suffit de lui passer l'une des colonnes de la base de données. Il peut par exemple s'agir de la colonne des identifiants reconnue par son nom : ref.

Concernant le décompte des produits effectivement en stock, le dénombrement doit intervenir sur le tableau d'extraction. La première colonne est reconnue par le nom refe. Il s'agit d'y compter les cellules non vides, tout en excluant celles ne renvoyant aucun résultat mais portant un calcul. Nous l'avons appris, c'est la fonction Excel Nb.Si, avec un critère tout à fait particulier, qui permet de livrer le résultat.

• En cellule K5, taper et valider la formule suivante : =NBVAL(ref),

En réponse, nous obtenons un résultat de 244 articles référencés dans la base de données.

• En cellule M5, construire et valider la formule suivante : =NB.SI(refe; '>*<'),

Nous obtenons fort logiquement un résultat vierge pour l'instant. Aucune extraction n'a encore été produite. Mais la formule existe désormais et actualisera son calcul au gré des importations. C'est ce critère particulier ('>*<'), inscrit en second paramètre de la fonction Nb.Si, qui permet de compter les cellules vraiment vides et pas seulement en apparence.

Extraire les articles en stock
Les fonctions d'extraction, nous les connaissons bien. Il s'agit des fonctions Index et Equiv à imbriquer. Repérer les enregistrements pour lesquels le stock n'est pas nul est une chose. L'extraction doit réunir tous ces articles en excluant les autres. Pour cela, la fonction Petite.Valeur est précieuse. En comparant les rangs concordants avec les positions repérées, elle est capable de réunir les valeurs à importer. Et cette importation est sujette à condition puisqu'il s'agit d'exclure tous les stocks à zéro. Donc, nous limiterons son champ d'action grâce à la fonction conditionnelle Si.

• Sélectionner les cellules des références à extraire, soit la plage J8:J247,
• Taper le symbole égale (=) pour démarrer la construction de la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

Nous souhaitons en effet gérer et neutraliser les messages d'erreur retournés par les fonctions d'extraction en cas de recherche infructueuse.

• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des valeurs à extraire par son nom, soit : ref,
• Taper un point-virgule (;) pour passer dans l'argument des indices de ligne,
• Inscrire la fonction des petites valeurs suivie d'une parenthèse, soit : Petite.Valeur(,

C'est elle qui doit permettre de regrouper les données concordantes en fonction des rangs analysés et correspondant avec les positions repérées. Ces positions, nous allons les identifier avec la fonction Equiv. Mais souvenez-vous, la plage d'étude est soumise à condition. Elle doit vérifier que les stocks ne sont pas nuls, donc positifs. C'est la raison pour laquelle, nous devons d'abord énoncer la contrainte.

• Saisir la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la colonne des quantités par son nom, soit : stock,
• Taper le symbole supérieur suivi du chiffre zéro, soit : >0, pour l'inégalité du critère,

Ainsi, dans ce raisonnement matriciel, pour chaque ligne passée en revue, nous souhaitons restreindre le champ d'action des fonctions d'extraction aux données possédant un stock positif.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction repérant les positions des données suivie d'une parenthèse, soit : Equiv(,
• En guise de valeur cherchée, mentionner la plage des références par son nom, soit : ref,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner de nouveau la colonne des références par son nom, soit : ref,

Dans cette analyse matricielle, chaque référence sera trouvée dans sa propre colonne. Mais souvenez-vous, ce repérage est contraint par la fonction Si. Ces positions ne seront dévoilées que dans la mesure où le stock n'est pas nul.

• Saisir un point-virgule suivi du chiffre zéro, soit : ;0, pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Si,

En effet, la branche Sinon de cette fonction ne nous intéresse pas. Lorsque le critère n'est pas vérifié, les données ne doivent pas être repérées. La fonction SiErreur se chargera de neutraliser ces exceptions.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,

Tous les rangs potentiels sont à observer. Toutes les références peuvent être concernées. Pour les considérer toutes, grâce au raisonnement matriciel, nous allons construire une matrice virtuelle de même hauteur que la colonne d'extraction.

• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,

Bien évidemment, pour considérer tous les rangs, nous n'allons pas lui indiquer une seule cellule, mais l'ensemble de celles qui constitueraient la plage d'extraction. Cette matrice doit être construite de toutes pièces. Elle doit donc être interprétée.

• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Indiquer la première ligne comme point de départ entre guillemets, soit : '1:',

Les deux points (:) annoncent la borne inférieure à suivre pour la matrice virtuelle. Elle doit être de même hauteur que la base de données. Cette valeur n'est pas connue et est potentiellement variable. Cette donnée doit donc être calculée et concaténée.

• Inscrire le caractère de concaténation, soit : &,
• Saisir la fonction calculant les lignes d'un tableau, suivie d'une parenthèse, soit : Lignes(,

Attention, il s'agit du pluriel de son homologue précédemment utilisé.

• Désigner la colonne des références par son nom, soit : ref,

C'est ainsi que nous fabriquons une matrice de même hauteur que la base de données. Elle débute à partir de la première ligne et s'étend sur autant de rangées que la base de données compte d'enregistrements. Comme cette matrice est exploitée dans l'argument du rang de la fonction Petite.Valeur, ce sont tous les rangs potentiels qui seront passés en revue. Ces rangs permettront de coïncider avec les positions repérées par la fonction Equiv, afin de produire une extraction groupée de tous les produits en stock.

• Fermer la parenthèse de la fonction Lignes,
• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Fermer la parenthèse de la fonction Index,

Le troisième argument de cette dernière n'est effectivement pas nécessaire. Il s'agit de l'indice de colonne pour produire l'extraction des données situées au croisement avec les positions repérées en ligne. Mais comme nous avons désigné une matrice d'une seule colonne en premier argument (ref), la fonction Index, en l'absence d'indication, pointera naturellement dessus.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour ignorer les résultats en cas d'anomalie,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Les références sont parfaitement extraites. Et si vous scrutez les premières lignes de la base de données, vous notez que les codes associés à des stocks nuls sont exclus. De plus, cette extraction a réalisé la prouesse de regrouper les données concernées et ce, à l'aide d'une seule formule matricielle, dont la syntaxe est la suivante :

{=SIERREUR(INDEX(ref; PETITE.VALEUR(SI(stock>0; EQUIV(ref; ref; 0)); LIGNE(INDIRECT('1:' & LIGNES(ref))))); '')}

Pour importer le détail sur le prix et le stock, il suffit d'adapter le nom de la matrice d'extraction en premier paramètre de la fonction Index :

{=SIERREUR(INDEX(prix; PETITE.VALEUR(SI(stock>0; EQUIV(ref; ref; 0)); LIGNE(INDIRECT('1:' & LIGNES(ref))))); '')}

{=SIERREUR(INDEX(stock; PETITE.VALEUR(SI(stock>0; EQUIV(ref; ref; 0)); LIGNE(INDIRECT('1:' & LIGNES(ref))))); '')}

Bien sûr, les plages d'extraction doivent préalablement être intégralement sélectionnées. De plus, les formules matricielles doivent nécessairement être validées par le raccourci CTRL + MAJ + Entrée.

Le dernier calcul concerne celui du total, soit la valeur marchande par référence stockée. Il consiste à multiplier la quantité en stock par le prix unitaire du produit. Mais comme des articles sont exclus de la restitution, des lignes vides existent. Elles ne peuvent intervenir dans la multiplication. Un test est donc nécessaire.

• En cellule M8, construire et valider la formule suivante : =SI(L8<>'';K8*L8;''),
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Le critère est donc posé sur le stock. Il consiste à savoir si l'information a bien été extraite. Dans ce cas, la multiplication est entreprise pour le calcul du total. Le cas échéant, la cellule est conservée vide.



Vous notez de même l'actualisation du calcul sur le dénombrement des valeurs extraites. Confronté au premier résultat, il indique clairement que 21 références sont archivées avec un stock nul.