Editions ENI Livres | Vidéos | e-Formations
Calculette dates de livraison
Énoncé du projet
Ce projet, assez simple de prime abord, va consister à créer un tableau Excel permettant de déterminer une date de livraison selon certains paramètres entrés au départ.
Cette date de livraison devra être calculée à partir de :
-
La date de commande.
-
La durée de fabrication.
-
Le délai de livraison.
D’autre part, elle ne peut être valide que si elle tombe en dehors des samedis, dimanches, mercredis ou jours fériés.
Ce tableau sera utilisé principalement par des personnes qui ne sont pas forcément habituées à l’utilisation d’Excel.
Étape 1 : analyse du projet
Les questions-réponses
Quel est l’objectif principal à atteindre ? Calculer les dates de livraison.
Quelles sont les contraintes ?
-
Automatiser les calculs.
-
Durée de fabrication.
-
Délai de livraison.
-
Éliminer les samedis, les dimanches et les mercredis.
-
Tenir compte des jours fériés.
À qui le résultat final est-il destiné ? Utilisation interne.
Sur quel support doit-il être présenté ? Le tableau sera seulement une sorte de calculette. Il restera sur le fichier Excel.
Le concepteur est-il l’utilisateur ? Il fait partie des utilisateurs potentiels.
Qui devra saisir ou exploiter les données ? Le concepteur et toute personne ayant besoin de calculer une date de livraison.
Ce tableau servira-t-il une ou plusieurs fois ? Est-ce un modèle ? C’est un tableau de calcul, qui aura besoin d’être remis à zéro après chaque utilisation. Ce ne sera pas un modèle.
Quelles sont les données à saisir ? Date de commande, durée de fabrication, délai de livraison.
Quelles sont les données faisant partie de la structure du tableau ? Libellé des lignes ou colonnes du tableau. Liste des jours fériés.
Quelles sont les données à calculer ? Dates de livraison intermédiaires, jour de semaine de la date de livraison...
Étape 2 : choix des commandes Excel
Calcul de la date de livraison
Ce calcul aurait pu se résumer à : date commande + durée fabrication + délai livraison si nous n’avions pas eu besoin de nous soucier des dates qui tomberaient un samedi, un dimanche, un jour férié ou un mercredi.
Notre travail va donc consister globalement à :
-
Calculer une première date.
-
Vérifier si cette date tombe un mercredi, un samedi ou un dimanche.
-
Calculer une nouvelle date et vérifier si elle tombe un jour férié.
-
Calculer la date définitive.
Pour simplifier le contenu de chaque formule nous avons choisi de passer par des étapes intermédiaires.
Quelles sont les fonctions à utiliser pour mener à bien ce calcul ?
Pour répondre à cette question, nous allons cette fois nous reporter à l’aide en ligne de Microsoft Excel.
Cliquez dans la zone Rechercher (ou Dites-nous ce que vous voulez faire) dans la partie supérieure du ruban.
Saisissez fonctions date.
Cliquez sur Obtenir de l’aide sur "fonctions date", puis sur Fonctions de date et d’heure (référence).
Le volet d’aide s’affiche dans la partie droite de votre écran, avec la liste des fonctions date et heure.
À la lecture des descriptions des différentes fonctions, il est clair que les fonctions faisant référence à des jours ouvrés pourraient nous être utiles.
Nous allons les étudier de près pour en cerner les nuances.
de la barre de formule, ou
onglet Formules - groupe Bibliothèque de fonctions -
bouton liste DateHeure.NB.JOURS.OUVRES et NB.JOURS.OUVRES.INTL
Elles déterminent toutes deux le nombre de jours ouvrés entre deux dates avec la possibilité d’affiner le calcul en y intégrant une liste de jours fériés.
La différence se situe dans les jours représentant les fins de semaine. Avec la fonction NB.JOURS.OUVRES, la fin de semaine est forcément composée du samedi et du dimanche, alors qu’avec la fonction NB.JOURS.OUVRES.INTL les jours à considérer comme non ouvrés peuvent être paramétrés...
Étape 3 : notre calculette pas à pas
Ouvrez le fichier Livraison_debut ou reproduisez les tableaux ci-dessous dans les feuilles correspondantes :
Feuille Calculette :
Feuille Jours fériés :
Avant de nous lancer dans les calculs, commençons par nommer les cellules pour simplifier la lecture de nos formules.
Création des identifiants de chaque cellule
Sélectionnez la cellule B1 de la feuille Calculette.
Cliquez dans la zone nom de la barre de formule puis saisissez DateCom.
Validez par Entrée.
Attention ! Si vous ne validez pas aussitôt le nom par Entrée, il ne sera pas mémorisé.
Reproduisez les mêmes manipulations pour nommer les cellules B2, B3, B4 et B6 respectivement DFabr, DLivr, DTotal et DateL1.
Pour vérifier que vous avez bien enregistré tous les noms, cliquez sur la liste déroulante de la Zone Nom.
Le nom de la cellule active est encadré.
En cliquant sur un autre nom de la liste vous déplacez automatiquement le rectangle de sélection sur la cellule correspondante.
Activez la feuille Jours fériés puis sélectionnez les cellules B1 à B11.
Nommez cette plage JFerie.
Sélectionnez DTotal dans la liste déroulante de la Zone Nom de la feuille Jours fériés.
Vous retrouvez aussitôt le rectangle de sélection sur la cellule B4 de la feuille Calculette, où nous allons calculer le délai total.
Nos formules
Tapez = cliquez sur B2 tapez + cliquez sur B3.
Lors de la sélection de la cellule désirée le nom est automatiquement inscrit dans la formule, vous obtenez :
=DFabr+DLivr Validez par Entrée.
Vous obtenez 0 pour le moment.
Sélectionnez la cellule B6 puis entrez la formule suivante :
=SERIE.JOUR.OUVRE(DateCom;DTotal;JFerie) Validez par Entrée.
Vous obtenez 0 également.
Nous allons tester nos formules :
Saisissez 09/01/2020 en B1 ; 10 en B2 ; 15 en B3.
Vérifiez...
