Automatiser les prévisions logistiques 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 :


Logistique - Gestion des flux

Une grande entreprise, grossiste en semences a besoin d'anticiper les volumes des commandes pour mieux gérer ses flux. De nombreux collaborateurs interviennent sur une même base de données, actualisée régulièrement via l'intranet de l'entreprise. Cette base de données recense tous les articles en commande. Certains ont déjà été traités par de tierces intervenants. D'autres doivent être montés sur des palettes pour charger le camion réalisant le transport. Ce sont les poids des articles qui déterminent le nombre de palettes nécessaires.



Vous l'avez compris, l'objectif est de faire gagner du temps et de la précision à l'entreprise, en déterminant automatiquement le nombre de palettes à commander pour affréter les semi-remorques. Le tableau de bord de l'application finalisée, illustré par la capture ci-dessus, montre le résultat à atteindre. Un clic sur le bouton traite les milliers de lignes de la base de données pour fournir instantanément, le résultat attendu soit ici, 280 palettes à prévoir. Aucun code VBA ne doit intervenir, seuls les calculs Excel permettront d'apporter la solution.

Source et présentation du concept

Nous proposons de débuter les travaux à partir d'un classeur Excel fournissant la base de données à l'instant t ainsi que la structure du tableau de bord.

• Télécharger le classeur logistique-gestion-des-flux.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Deux feuilles sont proposées. La feuille Suivi héberge la base de données, celle qui est actualisée régulièrement. Elle recense tous les produits en commande et en cours de traitement. Le détail a volontairement été simplifié en nombre de champs pour faciliter les manipulations. Trois colonnes nous intéressent particulièrement. La colonne Qté En stock énumère tous les poids des articles. Ce sont ces valeurs cumulées qui permettront de déterminer la quantité de palettes nécessaires pour l'acheminement. Les colonnes Description et Description 2 fournissent des renseignements dans lesquels des indicateurs textuels permettent de savoir si l'enregistrement a déjà été traité par une autre équipe. Une extraction sera alors envisagée selon des critères posés sur ces indicateurs. De fait, des calculs Excel permettront de réaliser la différence des quantités pour fournir le nombre exact de palettes à prévoir. Bien sûr, tous nos calculs devront être dynamiques en prévoyant des plages évolutives.

La feuille synthese quant à elle propose la structure du tableau de bord dans lequel les calculs conditionnels devront être bâtis. Entre B14 et C17 vous notez la présence d'un petit tableau de références, indiquant les équivalences entre les désignations d'articles et le nombre de palettes associées. Un Big Bag correspond à un colis d'au moins 60 Kg. Il requière à lui tout seul une palette. Les piscines correspondent à des sachets dont le poids est nécessairement inférieur ou égal à 5 Kg. Il en faut trente pour justifier une palette. Enfin les sacs correspondent à des produits dont le poids est compris entre 5 et 60 Kg exclus. Il en faut 10 pour justifier la commande d'une palette.

Sur la droite de la feuille, entre les cellules H14 et I15, vous notez la présence d'un petit tableau recensant des indicateurs textuels encadrés du symbole *. Ces morceaux de chaîne, s'ils sont trouvés dans les champs Description ou Description 2 de la base de données, indiquent que le lot a déjà été traité. En conséquence, ils ne doivent pas être comptabilisés dans le calcul des palettes à prévoir. Ce tableau servira donc de zone de critères pour une extraction automatisée et enclenchée par le clic sur le bouton de la feuille. Cette zone de critères n'étant pas exhaustive, nous devrons prévoir des bornes mouvantes pour que l'extraction dynamique puisse être évolutive. L'extraction aura lieu sur la feuille Suivi. Et c'est sur la base de ce nouveau tableau ainsi généré que devront s'enclencher les calculs déterminant les palettes, en haut de la feuille, entre les colonnes C à F.

Dénombrement conditionnel
La formation Excel sur l'analyse multicritère nous avait appris à comptabiliser des lignes en fonction d'une ou de plusieurs conditions. En colonne C du premier tableau de la feuille synthese, nous devons compter le nombre total de palettes, traitées ou non traitées, selon la contrainte de poids correspondant au lot. La fonction Excel NB.SI permet de dénombrer toutes les cellules d'une plage correspondant à un critère transmis, selon la syntaxe suivante :

=Nb.si('Plage_de_cellules' ; 'Critère')

Parfois néanmoins, les conditions doivent être recoupées et un seul critère ne suffit plus. C'est le cas des sacs pour lesquels le poids est à la fois supérieur à 5 Kg et à la fois inférieur à 60 Kg. La fonction Excel Nb.Si.Ens permet ce dénombrement multicritère en énumérant les plages et les conditions, selon la syntaxe suivante :

=Nb.Si.Ens('Plage_de_cellules_1' ; 'Critère_1' ;... ; 'Plage_de_cellules_n' ; 'Critère_n')

Pour que les résultats proposés par ces fonctions puissent s'adapter en toutes circonstances, nous devons les exploiter sur des plages de cellules potentiellement évolutives. La fonction Excel Decaler permet de désigner des plages dont les bornes évoluent en fonction de leur contenu.

=Decaler(1ere_cellule_colonne ; decalage_ligne ; decalage_colonne ; [hauteur] ; [largeur])

• Cliquer sur l'onglet synthese en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule C5 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de dénombrement suivi d'une parenthèse, soit Nb.Si(,
• Saisir ensuite le nom de la fonction Decaler suivi d'une parenthèse, soit Decaler(,
• Cliquer ensuite sur l'onglet suivi en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur la première cellule de la colonne Qté, ce qui donne : Suivi!C1,
• Enfoncer la touche F4 du clavier pour la figer,
• Taper ensuite : ;0;0; pour indiquer qu'aucun décalage ne doit se produire ni en ligne et ni en colonne,
• Pour déterminer la hauteur de cette plage de recherche, taper le nom de la fonction comptant les cellules non vides suivi d'une parenthèse, soit nbval(,
• Cliquer sur l'étiquette de la colonne C, ce qui donne : Suivi!C:C,
• Fermer la parenthèse de la fonction NbVal puis fermer la parenthèse de la fonction Decaler,
• Taper un point-virgule (;) pour passer à l'argument du critère de la fonction Nb.Si,
• Saisir le critère : >=60 entre guillemets, soit '>=60',
• Fermer la parenthèse de la fonction et valider le calcul par CTRL + Entrée pour conserver la cellule active.

Nous obtenons le résultat de 339, soit le même nombre total en palettes spécifiquement pour ce lot. La formule que nous avons bâtie est la suivante :

=NB.SI(DECALER(Suivi!$C$1; 0; 0; NBVAL(Suivi!C:C)); '>=60')

Grâce à la fonction Decaler, nous avons pu spécifier une plage de cellules dont la hauteur variera en fonction des données saisies dans le tableau. Si les enregistrements augmentent, les nouvelles lignes seront considérées. C'est la fonction NbVal qui permet de faire varier la hauteur de cette plage de cellules en fonction des cases non vides dénombrées. Le dernier paramètre de la fonction Decaler est facultatif, nous ne l'avons pas renseigné. Il permet en effet de spécifier la largeur de la plage. Elle est fixe dans notre cas puisqu'elle ne concerne qu'une seule colonne.



Le calcul du lot de la cellule du dessous est le même. Seul le critère change. Il s'agit de considérer tous les lots dont le poids est inférieur ou égal à 5 Kg. Comme nous avons judicieusement figé la cellule de référence sur la plage du dénombrement, nous pouvons tirer la poignée vers le bas. Et nous n'aurons plus que le critère à adapter.

• Tirer la poignée de la cellule C5 sur une ligne, soit jusqu'en C6,
• Puis, sélectionner la cellule C6 et enfoncer la touche F2 du clavier pour activer la modification,
• Entre les guillemets, remplacer le critère >=60 par <=5,
• Puis, valider à l'aide de la touche Entrée du clavier,

En réponse nous obtenons la valeur 623 correspondant au nombre de lots répondant à ce critère. Contrairement au lot précédent en revanche, un calcul intermédiaire est nécessaire pour déterminer le besoin en palettes.

Mais avant cela nous devons régler le calcul du dessous pour savoir combien de sacs sont représentés dans cette source de données. Il s'agit de recouper ensemble deux critères (>5 et <60), grâce à la fonction Nb.Si.Ens.

• Sélectionner la cellule C7 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de dénombrement multicritère suivi d'une parenthèse, soit Nb.Si.Ens(,
• Désigner la même plage dynamique de cellules que précédemment en saisissant la fonction Decaler comme suit : decaler(Suivi!$C$1; 0; 0; nbval(Suivi!C:C)), sans oublier de figer la référence C1,
• Taper un point-virgule (;) pour passer à l'argument du premier critère,
• Saisir le critère suivant sans oublier les guillemets : '>5',
• Taper un nouveau point-virgule pour passer à l'argument de la nouvelle plage,
• Saisir de nouveau la fonction Decaler : decaler(Suivi!$C$1; 0; 0; nbval(Suivi!C:C)) car bien que la plage soit identique, le critère est nouveau,
• Taper un point-virgule pour passer à l'argument du deuxième critère,
• Saisir le second critère sans oublier les guillemets, soit : '<60',
• Fermer la parenthèse de la fonction Nb.Si.Ens et valider le calcul par Entrée,

Nous obtenons le résultat de 398 lots correspondant à ce double critère recoupé. Là encore, un calcul intermédiaire est nécessaire pour déterminer les palettes à prévoir, selon les indications du petit tableau de référence entre B14 et C17. La formule que nous avons bâtie est la suivante :

=NB.SI.ENS(DECALER(Suivi!$C$1; 0; 0; NBVAL(Suivi!C:C));'>5'; DECALER(Suivi!$C$1; 0; 0; NBVAL(Suivi!C:C));'<60')

Comme les deux précédentes, elle s'adaptera à l'évolution des lignes de la base de données grâce à l'emploi conjoint des fonctions Excel Decaler et NbVal.



Calculs de conversions par extraction des coefficients
Il aurait pu être judicieux d'inscrire ces critères de poids dans un petit tableau de référence, afin de les désigner en tant que variables dans les formules. C'est d'ailleurs ce que nous allons faire pour la correspondance des nombres de lots en nombres de palettes. Pour chaque ligne, il s'agit de diviser le résultat du calcul du nombre de lots par le coefficient de conversion, situé dans le petit tableau du dessous. Pour assurer un calcul dynamique et facile à répliquer nous proposons d'extraire le coefficient du lot par une Recherche Verticale. La fonction RechercheV d'Excel permet de retourner une valeur inscrite dans une base de données et correspondant à une information recherchée dans une autre colonne. Sa syntaxe est la suivante :

=RechercheV(valeur_cherchée ; tableau_de_recherche ; num_colonne_retour ; Faux)

La valeur cherchée est la désignation pour le calcul de la ligne en cours (NB BIG BAG, NB PISCINE ou NB PALETTE). Le tableau de recherche est la petite plage de cellules B14:C17. La colonne contenant l'information de conversion à retourner est la deuxième, soit le numéro 2. Enfin le booléen Faux permet de demander à la fonction RechercheV de réaliser une recherche selon une correspondance exacte.

• Sélectionner la cellule D5 et taper le symbole = pour débuter le calcul,
• Désigner le calcul précédent soit la cellule C5 et taper le symbole slash (/) pour la division,
• Saisir le nom de la fonction de recherche suivi d'une parenthèse, soit RechercheV(,
• Sélectionner la désignation de la cellule correspondante, soit la cellule B5,
• Taper un point-virgule (;) pour passer à l'argument du tableau de recherche,
• Sélectionner alors la plage de cellules B14:C17 et enfoncer la touche F4 pour la figer,
• Taper un nouveau point-virgule pour passer à l'argument du numéro de colonne,
• Saisir le chiffre 2 et taper un point-virgule,
• Puis taper le booléen Faux, fermer la parenthèse et valider le calcul par Entrée,

Pour le premier résultat, pas de surprise, aucune conversion n'est entreprise puisque par défaut un Big Bag équivaut à une palette. Nous obtenons donc une quantité de palettes identique au nombre de lots trouvés précédemment pour cette catégorie. La formule que nous avons bâtie est la suivante :

=C5/RECHERCHEV(B5; $B$14:$C$17; 2; FAUX)

C5 et B5 n'ont pas été figées pour qu'en répliquant le calcul sur les lignes du dessous, la formule considère bien les quantités et désignations suivantes. En revanche, la plage de cellules a judicieusement été figée pour que la recherche se réalise entre des bornes fixes, puisque le tableau de recherche lui, ne bouge pas.

• Tirer la poignée du résultat jusqu'en cellule D7,

Comme il s'agit de divisions, nous obtenons des résultats avec des décimales, parfois nombreuses. Les palettes s'estiment en quantités, donc en nombres entiers. Et comme il vaut mieux en prévoir plus que pas suffisamment, nous proposons d'arrondir ces résultats à l'entier supérieur. Comme nous l'avait appris la formation pour reproduire des calculs en toutes circonstances, il existe plusieurs fonctions Excel d'arrondis. Celle qui nous intéresse est la fonction Arrondi.Sup dont la syntaxe très simple est la suivante :

=Arrondi.Sup(nombre_a_arrondir ; nombre_de_decimales)

Dans notre cas, le nombre à arrondir est le résultat du calcul. Comme nous souhaitons obtenir l'entier directement supérieur, nous devons saisir le chiffre 0 en deuxième argument, pour n'obtenir aucune décimale.

• Sélectionner le premier résultat, soit la cellule D5,
• Enfoncer la touche F2 du clavier pour accéder à la modification de la formule,
• Après le symbole =, intercaler le nom de la fonction suivi d'une parenthèse, soit Arrondi.Sup(,
• Puis, cliquer à la toute fin du calcul et taper un point-virgule pour passer à l'argument du nombre de décimales à conserver,
• Saisir le chiffre 0, fermer la parenthèse et valider le calcul par la touche Entrée,
• Enfin, tirer de nouveau la poignée du calcul jusqu'en cellule D7,

Comme attendu, nous obtenons bien les résultats arrondis correspondant au nombre total de palettes. La formule finale que nous avons bâtie est la suivante :

=ARRONDI.SUP(C5/RECHERCHEV(B5; $B$14:$C$17; 2; FAUX); 0)

Sources de données évolutives et dynamiques
Les opérations suivantes consistent à connaître le nombre de palettes traitées et non traitées afin de pouvoir gérer et anticiper les flux. Mais ces calculs logistiques nécessitent une extraction préalable des données correspondant aux critères énoncés dans le petit tableau situé en colonnes H et I. Ces critères doivent permettre d'isoler les lots ayant déjà été traités. Nous pourrons en déduire les palettes traitées et en cascade les palettes non traitées, soit le résultat final qui nous intéresse. Mais comme cette liste de critères n'est pas exhaustive, elle est susceptible d'évoluer. Il en va de même pour la base de données de la feuille suivi qui est en constante évolution. Nous proposons donc de référencer ces deux zones avec des noms de plages de cellules. Puis, comme précédemment, nous rendrons ces plages évolutives grâce à l'utilisation conjointe des fonctions Decaler et NbVal.

• Cliquer sur l'onglet Suivi en bas de la fenêtre Excel pour activer sa feuille,
• Réaliser le raccourci CTRL + Home (Flèche orientée Nord-Ouest) pour activer la cellule A1,
• Puis, réaliser le raccourci clavier CTRL + MAJ + Fin pour étendre la sélection jusqu'à la dernière cellule active de la base de données,
• Dans la zone Nom, tout à fait en haut à gauche, saisir bd et valider par la touche Entrée,

La plage de la base de données est désormais reconnue sous le nom bd que nous venons de lui attribuer. C'est ce qu'illustre l'extrait de la capture ci-dessous. Mais pour l'instant, elle fait toujours référence à une plage de cellules dont les bornes sont strictement définies, donc fixes.

Nous allons devoir manipuler ce nom de manière à ce que la borne inférieure de la plage de cellules ainsi définie se déplace en même temps que le contenu évolue. Mais avant cela, nous proposons de définir de la même manière la plage de cellules de la zone de critères pour l'extraction.

• Cliquer sur l'onglet synthese en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la plage de cellules de la zone de critères, soit H4:I15,
• Dans la zone Nom en haut à gauche, saisir le texte zoneC et valider par la touche Entrée,

Les deux plages étant nommées, nous devons désormais faire en sorte qu'elles soient en mesure de s'adapter au contexte, soit au contenu potentiellement évolutif. Pour ce faire, nous devons transformer ces noms par des calculs.

• Cliquer sur l'onglet Formules en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue qui suit, sélectionner tout d'abord la ligne bd,
• Dans la zone Fait référence à, en bas de la boîte de dialogue, remplacer l'expression par la formule suivante :

=DECALER(Suivi!$A$1:$F$1; 0; 0; NBVAL(Suivi!$A:$A))

Grâce à la fonction Decaler, nous définissons la ligne de départ figée de la base de données, soit la plage A1:F1. Grâce à la fonction NbVal nous définissons sa hauteur variable, selon le nombre d'éléments contenus dans la colonne A, celle des références.

• Sélectionner maintenant le nom zoneC,
• Dans la zone Fait référence à, remplacer l'expression par la formule suivante :

=DECALER(synthese!$H$4:$I$4; 0; 0; NBVAL(synthese!$H:$I)-2)

Nous indiquons bien la première ligne du tableau comme point de départ de référence de la zone de critères, avec la plage de cellules H4:I4. Puis, pour que la hauteur évolue en fonction du contenu, nous comptons les cellules non vides sur les deux colonnes H et I, grâce à la fonction NbVal. Mais comme nous comptons deux fois le titre (Description et Description 2) pour une même ligne et que nous comptons aussi le texte fusionné en cellule H2, nous retranchons deux unités au décompte. La plage de cellules de la zone de critères doit être dynamique certes, mais ses bornes doivent être définies au plus juste. En cas de mauvais calcul, nous serions susceptibles d'intégrer une ligne vide. Or une ligne vide dans une zone de critères pour une extraction par filtre, annulerait toutes les conditions énoncées en amont.



Extraction dynamique des lots traités
Maintenant que les plages sont définies et dynamiques, nous allons pouvoir les utiliser dans les zones du filtre avancé pour extraire uniquement les enregistrements correspondant aux multicritères. Ainsi lorsque les calculs manquants seront bâtis sur ces plages, ils se mettront à jour automatiquement à chaque nouvelle extraction. Et pour que le processus soit complètement automatisé, il faut que cette extraction se réalise au clic sur le bouton présent sur la feuille. Donc nous proposons d'enregistrer une macro capable de reproduire à chaque clic, l'extraction sur la base de données évolutive, selon la zone de critères elle-même dynamique. Mais avant cela, nous devons préparer la zone d'extraction des données sur la feuille Suivi.

• Cliquer sur l'onglet Suivi en bas de la fenêtre Excel pour activer sa feuille,
• A l'aide de la touche CTRL, sélectionner ensemble les cellules C1, E1 et F1,
• Les copier (CTRL + C) puis sélectionner la cellule I1,
• Et les coller à l'aide du raccourci CTRL + V,

Nous venons de reproduire à l'identique trois étiquettes de champs de la source de données. Ainsi, nous indiquons à Excel les informations strictes que nous souhaitons extraire. Il s'agit maintenant de réaliser la première extraction correspondant aux critères proposés par défaut. Et pour ne pas devoir reproduire ces actions complexes à chaque fois, nous devons enregistrer ces tâches dans une macro. Pour cela, nous avons besoin du ruban Développeur. S'il n'est pas visible sur votre interface, la formation pour débuter la programmation en VBA Excel rappelle comment l'afficher.

• Cliquer sur l'onglet synthese en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code tout à fait à gauche, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, la nommer extraire et cliquer sur le bouton Ok,

A partir de ce point, Excel enregistre la moindre de nos actions afin de les reproduire à l'identique. Nous devons donc simuler les enchaînements strictement nécessaires à l'extraction dynamique des données, selon la zone de critères nommée.

• Cliquer sur l'onglet Suivi en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer dans l'une des cellules de la source de données, par exemple B4,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Trier et filtrer du ruban, cliquer sur le bouton Avancé,
• Dans la zone Plages de la boîte de dialogue qui suit, remplacer les références par le nom bd,
• Dans la zone de Critères, saisir le nom zoneC,
• Cocher la case Copier vers un autre emplacement,
• Cliquer dans la zone Copier dans pour l'activer,
• Sélectionner alors les étiquettes précédemment collées, soit la plage de cellules I1:K1,
• Puis, cliquer sur le bouton Ok pour produire l'extraction,

Comme vous le constatez, les données correspondant vraisemblablement aux critères de la plage nommée sont extraites du tableau d'origine, avec les informations spécifiques aux trois champs demandés. Ces données sont susceptibles de varier selon les nouvelles conditions introduites dans la plage de critères. De fait, les calculs que nous bâtirons sur cette extraction évolueront dynamiquement pour fournir au final le décompte exact de palettes nécessaires. Nous devons finaliser l'enregistrement de la macro.

• Cliquer sur l'onglet synthese en bas de la fenêtre Excel pour activer sa feuille,
• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code, à droite dans le ruban, cliquer sur le bouton Arrêter l'enregistrement,

La macro existe désormais. A chaque appel, elle est susceptible de reproduire l'extraction dynamique des données. Elle est en effet bâtie sur des plages de cellules évolutives qui considèrent les potentielles nouvelles informations, qu'il s'agisse de la source de données ou de la zone de critères. Nous devons donc la matérialiser par un bouton pour automatiser les actions de l'utilisateur.

• Dans la section Contrôles du ruban Développeur, cliquer sur le bouton Insérer,
• Dans la liste qui s'affiche, choisir le premier contrôle, soit le bouton,
• Le tracer sous forme de rectangle dans l'encadré prévu à cet effet entre D10 et E12,
• Dans la boîte de dialogue qui surgit, sélectionner la macro extraire,
• Puis, cliquer sur Ok pour valider cette liaison,
• Modifier le texte du bouton avec l'information Extraire,
• Enfin, cliquer sur la feuille pour désactiver la conception du bouton,

Logistique dynamique et adaptative
A ce stade, si nous cliquons de nouveau sur le bouton Extraire, les données produites ne changent pas. Mais si entre temps la source de données est mise à jour et/ou que les critères sont modifiés, les données extraites évoluent. En conséquence, les calculs que nous allons produire à partir de la colonne E doivent eux aussi évoluer. Ils consistent à connaître le nombre de palettes traitées par lot. Ils doivent donc être bâtis sur le tableau résultant de l'extraction. Et bien sûr, ils doivent considérer des plages de cellules mouvantes car les lignes produites par l'extraction peuvent varier. Les fonctions Decaler et NbVal doivent de nouveau entrer en jeu.

• Sélectionner la cellule E5 de la feuille synthese et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction pour gérer les erreurs suivi d'une parenthèse, soit SiErreur(,
• Saisir le nom de la fonction de dénombrement suivi d'une parenthèse, soit Nb.Si(,
• Cliquer sur l'onglet suivi en bas de la fenêtre Excel pour activer sa feuille,
• Supprimer le suffixe suivi! ajouté automatiquement par Excel dans la formule,
• Taper le nom de la fonction pour faire varier les bornes suivi d'une parenthèse, soit Decaler(,
• Sélectionner la première cellule de la colonne du calcul, soit I1,
• Enfoncer la touche F4 du clavier pour la figer,
• Poursuivre l'expression en fixant les deux arguments suivants à Zéro, soit ;0;0;,
• Taper le nom de la fonction pour compter les cellules non vides suivi d'une parenthèse, soit NbVal(,
• Cliquer sur l'étiquette de colonne I et fermer la parenthèse de la fonction NbVal,
• Puis, fermer la parenthèse de la fonction Decaler,
• De retour dans la fonction Nb.Si, taper un point-virgule (;) pour passer à l'argument du critère,
• Taper le critère correspondant au lot sans oublier les guillemets, soit '>=60',
• Fermer la parenthèse de la fonction Nb.si,
• Taper le symbole slash (/) pour la division,
• Cliquer sur l'onglet synthese en bas de la fenêtre Excel pour activer sa feuille,
• Supprimer le suffixe synthese inscrit automatiquement par Excel dans la formule,
• Saisir le nom de la fonction de recherche suivi d'une parenthèse, soit RechercheV(,
• Désigner ou écrire la cellule du lot sans oublier en préfixe le nom de la feuille, soit synthese!B5,
• Taper un point-virgule (;) pour passer à l'argument du tableau de recherche,
• Sélectionner alors la plage de cellules B14:C17 et enfoncer la touche F4 pour la figer,
• Taper un point-virgule suivi du chiffre 2 pour indiquer la colonne de retour,
• Puis, terminer l'expression par : ;Faux,
• Fermer la parenthèse de la fonction RechercheV pour revenir dans la fonction SiErreur,
• Taper un point-virgule pour passer dans la branche de la gestion d'erreur,
• Taper deux guillemets pour conserver la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur et valider le calcul par la touche Entrée,

Nous obtenons le résultat dynamique de 53 palettes traitées pour ce lot, sur cette source de données avec cette zone de critères. Comme précédemment nous avions calculé le nombre total de palettes, nous pourrons en déduire en colonne F, le nombre de palettes à prévoir pour ce lot. Mais avant cela, nous devons produire les calculs manquants pour les autres lots. La formule complète que nous avons bâtie est la suivante :

=SIERREUR(NB.SI(DECALER(Suivi!$I$1; 0; 0; NBVAL(Suivi!I:I)); '>=60')/RECHERCHEV(synthese!B5; synthese!$B$14:$C$17; 2; FAUX); '')

Le suffixe synthese de la recherche n'est d'ailleurs pas nécessaire puisque le calcul s'exécute dans la feuille où il est écrit. Nous pourrions donc simplifier la syntaxe de la manière suivante :

=SIERREUR(NB.SI(DECALER(Suivi!$I$1; 0; 0;NBVAL(Suivi!I:I)); '>=60')/RECHERCHEV(B5; $B$14:$C$17; 2; FAUX); '')

Comme nous avons judicieusement figé les références dans le calcul, nous pouvons le répliquer pour le lot suivant. Il s'agira simplement de modifier le critère '>=60' par le critère '<=5'. Mais comme le résultat de la division fournira un grand nombre de décimales, nous devons par anticipation intégrer le calcul précédent dans la fonction Arrondi.Sup, afin de nettoyer le résultat.

• En E5, adapter le calcul comme suit :

=SIERREUR(ARRONDI.SUP(NB.SI(DECALER(Suivi!$I$1; 0; 0; NBVAL(Suivi!I:I)); '>=60')/RECHERCHEV(B5; $B$14:$C$17; 2; FAUX);0);'')

La fonction Arrondi.Sup intervient juste avant la fonction NB.SI. Comme elle doit arrondir le résultat de l'opération, nous ajoutons le point-virgule suivi du chiffre 0 après la parenthèse fermante de la fonction RechercheV. Il ne faut pas oublier de fermer la parenthèse de la fonction Arrondi.Sup avant de passer au second argument de la fonction SiErreur.

• Tirer la poignée du calcul sur la cellule du dessous, soit jusqu'en E6,
• Modifier le critère '>=60' par le critère '<=5',
• Valider le calcul par la touche Entrée du clavier,

Nous obtenons le résultat dynamique de 6 palettes traitées pour ce lot. Le calcul pour le dernier lot doit recouper deux critères comme précédemment. La fonction Nb.Si doit donc être remplacée par la fonction Nb.Si.Ens. La colonne I doit être désignée à deux reprises dans la fonction Decaler afin de croiser ces deux critères.

• En cellule E7, adapter le calcul comme suit :

=SIERREUR( ARRONDI.SUP( NB.SI.ENS( DECALER(Suivi!$I$1; 0; 0; NBVAL(Suivi!I:I)); '>5'; DECALER(Suivi!$I$1; 0; 0; NBVAL(Suivi!I:I)); '<60')/RECHERCHEV(B7; $B$14:$C$17; 2; FAUX); 0);'')



Pour ce dernier lot, avec les critères ainsi définis, 16 palettes sont traitées. Tous ces calculs intermédiaires permettent désormais de conduire au résultat final en toute simplicité. Le nombre de palettes non traitées se déduit par soustraction. Par précaution, comme précédemment, nous choisissons d'intégrer le calcul dans une fonction SiErreur. Ce triple calcul peut être réalisé en une fois par présélection de la plage concernée.

• Sélectionner la plage de cellules F5:F7,
• Taper le symbole = pour initier le calcul dans la première cellule de la plage,
• Saisir le nom de la fonction de gestion d'erreur suivi d'une parenthèse, soit SiErreur(,
• Sélectionner la première cellule du calcul du total des palettes, soit D5,
• Taper le symbole Moins (-) pour la soustraction,
• Sélectionner la première cellule du calcul des palettes traitées, soit E5,
• Taper un point-virgule pour passer dans l'argument de la gestion d'erreur,
• Taper deux guillemets ('') pour conserver la cellule vide en cas de problème,
• Fermer la parenthèse de la fonction SiErreur,
• Valider le calcul par le raccourci clavier CTRL + Entrée afin de le répliquer sur la plage,

Nous pouvons désormais calculer les totaux pour chaque type de palette.

• Sélectionner la plage de cellules D5:F8,
• Cliquer sur le bouton Somme automatique du ruban Accueil (Ou Alt + =),

Comme nous avions indiqué les cellules à sommer ainsi que les cellules où placer les résultats dans la sélection, toutes les sommes apparaissent instantanément.



Le résultat final apparaît automatiquement en cellule F8. Il s'agit de la quantité de palettes non traitées et donc du nombre à prévoir pour gérer les flux et les transports. Ce résultat dépend des précédents qui eux-mêmes dépendent de l'extraction s'adaptant aux évolutions de la source de données et de la zone de critères. En conclusion, un clic sur le bouton Extraire permet d'obtenir instantanément le résultat permettant de prendre les décisions les plus justes.

Avant de tester l'aspect dynamique et évolutif de notre application, il convient de consolider ce résultat sans équivoque, dans la cellule E14 fusionnée prévue à cet effet. Il s'agit de récupérer le résultat final et de le concaténer à un texte explicatif.

• Sélectionner la cellule E14 et taper le symbole = pour débuter le calcul,
• Cliquer sur la cellule du résultat pour la désigner, soit la cellule F8,
• Taper le symbole de concaténation (&), touche 1 en haut à gauche du clavier,
• Enfoncer la touche 3 du clavier pour ouvrir les guillemets,
• Taper un espace pour séparer le texte de l'information numérique,
• Puis saisir le texte : palettes à commander et fermer les guillemets,
• Valider enfin le calcul par la touche Entrée du clavier,

Vérification du dynamisme des résultats
Pour tester le bon fonctionnement de l'application, nous souhaitons compléter les critères et influer sur la source de données. L'objectif est de constater que tous les calculs se mettent instantanément à jour, au clic sur le bouton. Ainsi nous pourrons considérer que l'application permet d'automatiser la logistique de l'entreprise pour gérer et optimiser les flux.

Dans la zone de critères et plus précisément pour le champ Description 2, les fragments de texte encadrés d'étoiles (*) correspondent aux séquences remarquables indiquant qu'un lot a déjà été traité. Mais ces séquences peuvent très bien être inscrites dans le champ Description de la source de données d'origine. Pour une logistique précise, nous devons donc les inclure. Cependant, ils peuvent être inscrits dans l'un des deux champs, mais pas dans les deux à la fois. Pour énumérer ces critères, nous devons les inscrire à la suite dans la zone prévue à cet effet.

• Sur la feuille synthese, sélectionner la plage de cellules I5:I9,
• Copier la sélection à l'aide du raccourci clavier CTRL + C par exemple,
• Cliquer sur la cellule H10 pour la sélectionner,
• Coller la sélection précédente à l'aide du raccourci clavier CTRL + V par exemple,
• Utiliser éventuellement la balise active en bas de la sélection pour indiquer une copie sans la mise en forme afin de ne pas dégrader les bordures,
• Puis cliquer sur le bouton de macro Extraire,

Comme vous le constatez, avec ces critères plus fins, le nombre de lots reconnus comme ayant été traités, augmente. De fait, la prévision logistique sur le nombre de palettes se révise automatiquement à la baisse. Il est important désormais de vérifier que ces calculs s'adaptent bien aux mouvements perpétuels sur la source de données. Nous proposons de dupliquer les lignes de cette dernière afin de réaliser une simulation.

• Cliquer sur l'onglet Suivi en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la première cellule du premier enregistrement, soit A2,
• Atteindre la fin de la feuille à l'aide de l'ascenseur vertical,
• Tout en maintenant la touche MAJ enfoncée (Shift), cliquer sur la dernière cellule du dernier enregistrement, soit la cellule F1361,
• Copier (CTRL + C) la sélection intégrale ainsi réalisée,
• Sélectionner la cellule A1362 et coller (CTRL + V) cette copie,
• Cliquer sur l'onglet synthese en bas de la fenêtre Excel pour activer sa feuille,

Comme vous le remarquez, les calculs se sont mis à jour automatiquement. Mais ils ne sont pas justes puisque l'extraction sur la base de cette nouvelle source n'a pas encore été réalisée.

• Cliquer sur le bouton Extraire pour réajuster les prévisions,

Les prévisions se réajustent automatiquement. En un clin d'oeil, le responsable d'équipe peut exploiter la donnée et ainsi gérer ses approvisionnements au plus précis. Il s'agit d'un gain de temps important mais aussi d'une sécurité non négligeable permettant dene pas cumuler les erreurs d'appéciation.