Analyse et compilation données quotidiennes

Bonjour,

Je reçois quotidiennement des données de stock de marchandises par lieu de stockage sous forme de fichier excel. Les données se présentent sous cette forme :

Nom Magasin / Qté Marchandise 1/ Qté Marchandise 2/ Qté Marchandise 3/ TOTAL

______________/___________________/___________________/___________________/___________

Magasin A

_____________/____________________/___________________/___________________/___________

Magasin B

_____________/____________________/___________________/___________________/__________

J’aimerai savoir comment constituer un fichier dans lequel je pourrai intégrer ces données au quotidien, et en même temps garder un suivi de l’évolution des stocks (conso moyenne par marchandise pour une période donnée, si les stocks ont bien été renouvelés à une certaine date, etc.).

J’ai tenté de rechercher des solutions par moi-même, mais je suis novice en Excel, et j’ai besoin d’être au moins guidée pour parvenir à ce que je souhaite. J’ai cru comprendre que Power Pivot pourrait être une solution, mais je ne parviens pas à appliquer les différents tutos que je trouve à ma situation et à mes données. Peut-être que mon sujet aurait déjà été traité ?

En espérant avoir été à peu près claire

Par avance merci de votre aide !

Bonjour

Ton fichier ne te donne qu'un état de stock actualisé chaque jour?

Donc si ta marchandise 1 pour magasin A = 10 le jour J et =5 le jour J+1, cela signifie que tu as consommé 5 pcs sur la journée?

As tu une notion du volume qui est réceptionné à côté?

Car si ca trouve dnas mon exemple si tu peux aussi avoir réceptionné 20 et consommé 25 sur la journée

Si tu tu n'as qu'une notion journalière d'un niveau de stock mais que tu n'as pas a côté au moins la notion d'entrée ou de sortie, tu ne pourras pas faire de fichier de suivi de consommation.

Tu pourras juste visualiser l'évolution de ton niveau de stockage par magasin et marchandise

Je reçois chaque jour un fichier excel qui me donne l'état des stocks actualisé (de la forme de l'exemple) , ainsi qu'un autre fichier excel qui récapitule les approvisionnements prévu à une certaine date.

Donc si ta marchandise 1 pour magasin A = 10 le jour J et =5 le jour J+1, cela signifie que tu as consommé 5 pcs sur la journée

Exact ! C'est bien l'idée du calcul de consommation dont j'aurais besoin. Avec, comme tu l'as précisé, la difficulté d'inclure les données d'approvisionnement des stocks...

Pourrais tu fournir tes 2 fichiers : appro et stock.

Si'il y a des données confidnetielles tu renommes des marchidises par un nom commun.

De même tu peux réduire le nb de ref, c'est juste pour voir comment s'organisent tes ficheirs afin de te proposer une compilation.

Voilà en gros le genre de fichier avec lesquels je travaille.

Il y a en tout une cinquantaine de magasins à traiter.

Merci beaucoup de jeter un œil à ma question en tout cas !

Bonjour

On est d'accord que quand un produit est en commande le 24/01 par exemple, cet ajout va se retrouver sur ton état de stock que tu reçois le 25/01?

Ton fichier appro est-il une prévision de commande? La date d'arrivée peut-elle changer ainsi que la quantité réellement réceptionnée?

Bonjour,

On est d'accord que quand un produit est en commande le 24/01 par exemple, cet ajout va se retrouver sur ton état de stock que tu reçois le 25/01?

Oui c'est tout à fait ça ;

Le fichier "appro" représente des commandes passées manuellement en ligne auprès de notre fournisseur, et correspond toujours aux quantité reçues et donc ce qui sera "injecté" dans nos stocks.

En revanche, autre difficulté, il arrive certaines fois qu'il y ait 1 ou 2 jours de décalage entre la date pour laquelle la commande est passée et la date à laquelle nous les incluons dans nos stocks ... (par exemple une commande reçue le 24/01 peut parfois ne pas apparaître dans les stocks avant le 26/01 si manque de main d'oeuvre, etc.)

Bonjour

Voici une ébauche de ce que l'on pourrait faire.

Objectif :- pas de macro afin que tu puisses l'utiliser et le modifier sans avoir de connaissances en programmation.

- Pouvoir utiliser les fichiers bruts que tu reçois chaque jour sans avoir besoin de les modifier

Voici comment il fonctionne:

Sur l'onglet stock tu copies colles chaque jour tes données bruts de stocks à la suite. Pour mon exemple j'ai copier coller plusieurs jours et j'ai mis des valeurs manuelles.

Sur l'onglet Appro, tu copies colles tes données de réception à la suite également. J'ai crée 2 réceptions en exmple.

J'ai ajouté une colonne H pour signaler à quel moment ces réceptions vont apparaitre dans ta photo de stock. Par défaut j'ai mis J+1 mais tu peux le modifier à ta guise si la marchandise n'est réceptionnée qu'à J+2 par exemple.

Dans l'onglet stock, j'ai crée 3 colonnes J K et L qui te donne la consommation journalière d'un produit par magasin. Il déduit automatiquement le volume réceptionné pour avoir la consommmation réelle.

Dans l'onglet consommation, j'ai ajouté une fourchette de date afin que tu puisses visualiser ta consommation sur une période que tu choisis : semaine, mois, année

Pour chaque produit tu as la consommation et le nombre de pcs également réceptionnées.

Pour l'instant je n'ai mis que 3 magasins mais il est tout à fait applicable pour 50. Idem pour le nombre de produit.

A terme on pourrait agrémenter ce fichier en ajoutant des graphs, des seuils d'alerte de niveau de stock, etc.

De même s'il y a beaucoup de ref, il faudra que l'on crée un filtre de choix de produit dans l'onglet consommation afin de réduire la niveau d'affichage.

Mais je voulais déjà savoir si ce type de tableau correspondait en partie à tes attentes avant d'aller plus loin.

Bonjour,

Tout d'abord un grand merci pour ton aide, je suis impressionnée que tu m'aies carrément conçu un fichier

J'ai passé les 2 derniers jours à le décortiquer pour savoir si je saurais l'adapter à ma situation et éventuellement l'expliquer à mes collègues pour que l'on puisse gérer tout ça à plusieurs le plus simplement possible.

Dans l'idée ce fichier est parfait, toutes les infos dont nous avons besoin y sont et il est extrêmement simple à manipuler dans la mesure où il faut juste copier/coller les données quotidiennement et naviguer entre les onglets.

Maintenant mon soucis principal est que les contenus des fichiers que nous recevons sont susceptibles de varier légèrement d'un jour à l'autre; je m'explique : il arrive que pour une raison quelconque un magasin n'y apparaisse plus pendant quelque jours puis revienne par exemple. Dans ce cas (arrête-moi si je me trompe), il me semble que les données de consommations dans l'onglet STOCK s'en trouvent assez chamboulées.

De plus, nous ne recevons pas les fichiers le week-end, et donc les données du vendredi et du samedi ne peuvent pas être prises en compte (mais peut-être n'est-ce pas un soucis, j'ai juste un doute sur le calcul de la conso quotidienne dans un cas comme celui-ci...)

Et enfin, (je me doute qu'il n'y a pas grand chose à faire même je tente quand même) la difficulté réside dans le fait du décalage entre le jour de livraison et l'injection dans les stocks. Regarder magasin par magasin à quel jour on retrouve la commande de tel jour dans les stocks est une gymnastique assez fastidieuse puisqu'il n'y a aucune logique. En règle générale l'injection se fait à J mais les exceptions sont malgré tout nombreuses. Il me semble que ce paramètre complique un peu le suivi via le fichier que tu as réalisé.

Je continue cependant tout de même à tester des trucs avec le fichier pour voir si je peux résoudre tout cela moi-même

Bonjour

Pour le premier point, il n'y aura pas de souci dans l'onglet consommation mais en effet la formule de marchera pas dans l'onglet stock

En effet, dans ta base, il peut manquer un numéro de magasin, ca n'aura aucune incidence car dans l'onglet "consommation", je récupère la notion de numéro de magasin avec ma formule qu je vais expliquer en détail, mais je pense que tu l'as comprise:

=SOMMEPROD((STOCKS!$B$2:$B$34=Consommation!$A9)*(STOCKS!$A$2:$A$34>=Consommation!$B$3)*(STOCKS!$A$2:$A$34<=Consommation!$D$3)*(STOCKS!$J$2:$J$34))

Cette formule est la multiplication de 4 matrices:

la première matrice va regarder toutes les lignes dans la colonne B de ton onglet stock qui correspondent au numéro de magasin de l'onglet consommation. En gros il ne va additionner que les chiffres de la colonne J dont la ligne va être égale à ton numéro de mag dont tu as besoin.

Donc s'il pendant 1 semaine, tu n'as pas de données pour un magasin, ce n'est pas bien grave. Par contre si tes dates de début et de fin ne contiennent pas au moins 1 ligne dans ta base, tu auras une consommation de 0 pour ce magasin.

Les 2 matrices suivantes vont en gros définir un début et une fin pour faire la somme. Il ne fera la somme de la colonne J que si la colonne 1 est comprise entre les 2 dates que tu as définis

La 4ème matrice définit juste la colonne sur laquelle il doit faire une somme.

Cette formule est équivalent à la fonction somme.si.ens mais est très utile pour définir un critère de fourchette de date.

Pour l'onglet stock, je me suis en effet basé sur le fait que tes photos de stocks étaient toujours configurées de la même façon. Il faut juste que je modifie la formule si tes numéros de magasins ne sont pas identiques d'une photo de stock à une autre. Elle risque d'être assez complexe car je pense que je vais êter obligé d'utiliser l'index equiv associé à la fonction décaler. Je cherche et te tiens au jus.

Pour ton seocnd point, j'ai déjà supprimé les samedi et dimanches dans les dates que tu peux définir dans l'onglet consommation (feuil3).

Dans l'onglet stock, sur le we, ce ne sera pas vraiment en effet la conso journalière mais ce sera la conso entre tes 2 photos de stocks donc entre le lundi et le vendredi.

Si ton magasin a travaillé le samedi mais que tu ne reçois pas de photo de stock, ce sera en effet une consommatino du week end total.

Pour le 3ème point, en effet je pense qu'il n'est pas pertinent de s'éplucher les dates réelles de réception par magasin. J'ai envie de dire, laissons par défaut la formule J+1

Si en réalité c'est du J+2 par ex, le risque est que sur ta conso journalière dans l'onglet stock tu arrives à une consommation négative. Mais ce stock négatif sera régularisé le jour suivant.

Donc dans ton onglet consommation, il sera lissé sauf si tu mets la date de fin pile le jour où je considère une réception alors qu'il n'y en a pas.

C'est assez difficile à expliquer et comme ça je n'ai pas de solution. Il faudrait faire vivre le fichier voir si ce type de cas se présente ou non.

Bonjour !

Bon, grâce à tes explications aux petits oignons et les quelques jours de recul, je dirais en effet que le seul souci qu'il me reste vraiment au final c'est le fait que le nombre de lignes varie parfois dans mon fichier "stock" quotidien (j'imagine que c'est ce que tu appelles un changement de photo du stock).

Donc voilà, après si tu penses que ça nécessiterait de partir dans des formules trop complexes, je comprends tout à fait hein !

Avec l'aide des personnes de ce forum, j'ai trouvé la formule adaptée à ton problème.

Voici le fichier mis à jour

J'ai par exemple supprimé des lignes magasins A B et C et mis en couleur des lignes d'exemple où j'ai supprimé des lignes intermédiaires de photo de stock

Par ex sur le 25/01, il n'y a pas de ligne pour le magasin C et il n'apparait que le 28/01

Voici la formule utilisée pour la consommation de ce magasin C sur le produit 1 le 28/01

=INDEX($D$1:D10;MAX(SI($B$2:B10=B11;LIGNE($D$2:D10))))-D11+SOMME.SI.ENS(Appro!D:D;Appro!A:A;STOCKS!$B11;Appro!H:H;">="&MAX(SI($B$2:B10=B11;$A$2:A10));Appro!H:H;"<="&A11)

C'est du matriciel, si tu as #valeur, il te faut aller sur la ligne de formule et taper CTRL SHIFT ENTREE

Explication de la formule:

La formule index va chercher la dernière valeur qu'il trouve au dessus de ta nouvelle ligne pour le magasin C

Je soustrait D11 qui est ton nouveau stock

La formule somme.si.ens, va chercher le volume réceptionné éventuellement entre la dernière date où tu as inséré un stock et la date de ta ligne (28/01 dans cet ex)

Je t'ai mis dans l'onglet un exemple du calcul effectué par la formule pour tenter de donner du sens.

Au global, il y avait peut être plus simple mais par rapport à mes compétences c'est ce que j'ai pu trouver.

Pour utiliser ce fichier, voici donc le process:

• Dans l'onget stock, tu copies colle chaque jour à la suite ta photo de stock à partir de la colonne B à la colonne I
• Pour la colonne A, il faudra que tu mettes la date manuellement. J'avais mis une formule mais elle ne fonctionne que si tu avais le même nombre de ligne chaque jour
• Il faut tirer les formules des colonnes J à L
• Dans l'onglet appro, tu copies colles ton fichier de réception des colonnes A à G. Tu tires la formule de la colonne H
• Tu peux profiter de l'onglet consommation qui normalement doit fonctionner

Si ce fichier te convient, fais le vivre, et reviens vers moi si tu vois des anomalies apparaitre.

Cordialement

Bonjour,

Dernier petit message pour te dire que le fichier est nickel, j'ai réussi à l'adapter à mes besoins sans problème et grâce à ça je vais gagner un temps considérable !

Un GRAND MERCI à toi pour ta disponibilité et ta pédagogie, j'aurais tellement galéré seule ^^

Bonne journée et bonne continuation !

Bonjour

Tant mieux s'il t'es utile. C'es tout l'objectif de ce forum.

Il te reste à travailler l'ergonomie et la mise en page pour le rendre plus joli et plus pratique à utiliser

N'hésite pas à revenir vers moi si tu as un changement de process qui nécessite de revoir les formules ou si un problème non prévu ensemble survient