1. Produire une feuille de calcul sous Excel

a. Récupérez le travail précédent sous Excel.

Proposition de réponseLe plus simple est de procéder à l’aide d’un « copier/coller » de Word vers Excel.

Mode opératoire– Sélectionner la totalité du document sous Word :

– Coller le contenu du presse-papier dans une feuille Excel, préalablement ouverte) :

Remarque :  il   faut   procéder   à   certaines   corrections  mineures   (largeur   des   colonnes   et hauteur des lignes, centrage du titre, etc.).

b. Après avoir déterminé et saisi les formules de calcul nécessaires, sauvegardez la feuille de calcul en lui attribuant un nom temporaire de votre choix.

Proposition de réponse Mode opératoire

– Saisir la formule de multiplication de la quantité par le prix unitaire, en évitant d’afficher une liste de zéros lorsque la feuille est vide.– Recopier cette formule vers le bas.

– Saisir la formule de somme dans la cellule du total.

– En   exécutant   la   commande  Format/Cellule,   formater   les   colonnes   « Prix   unitaire »   et « Montant » en nombre à deux décimales.– Formater la colonne « Quantité » en nombre sans décimale.– Formater les colonnes « Départ le », « Retour le » et « Date » en date.– Sauvegarder ce classeur dans l’espace de travail, sous le nom de « Note_de_frais.xls ».

2. Produire un formulaire sous Excel

Proposition de réponse Mode opératoire

– Faire apparaître la barre d’outils Formulaires.– Pour les différentes zones de saisie, utiliser le contrôle Liste déroulante.– Préparer   les  plages  d’entrée  nécessaires  au  bas  de  la   feuille,   tel  que précisé  à   la  page suivante.– Amener le curseur sur la cellule où le contrôle doit être inséré.– Faire Données/Validation et, dans Autoriser, choisir l’option « Liste ».

– Spécifier la  Source, c’est-à-dire la plage de cellules contentant les choix possibles. Noter qu’Excel impose que cette plage de cellules soit localisée dans la feuille courante.

Exemple : A46:A57 pour le mois

– Ajouter puis recopier vers le bas chaque liste déroulante autant de fois que nécessaire.

– Sauvegarder la feuille dans l’espace de travail, sous le nom de « Form_note_de_frais.xls ».

Remarque : ce fichier est disponible sur le site compagnon, sous le nom de :« Chapitre2_Miseenoeuvre_Form_note_de_frais.xls »

3. Produire un modèle sous Excel et créer une famille de fichiers

a. La vocation de ce formulaire étant de devenir un document générique (un modèle), créez le modèle correspondant sous Excel.

Proposition de réponse Mode opératoire

– Ouvrir la feuille « Form_note_de_frais.xls ».– Sélectionner  Fichier/Enregistrer   sous  et   choisir   le   type   de   fichier   « Modèle »   dont l’extension est «.xlt ».

– Donner le nom : « Model_note_de_frais.xlt »

– Remarquer que, par défaut, le modèle s’enregistre dans un répertoire « Modèles », propre à Office.

Remarque : ce fichier est disponible sur le site compagnon sous le nom de :« Chapitre2_Miseenoeuvre_Model_note_de_frais.xlt »

b. À partir de ce modèle, préparez les fichiers nécessaires pour gérer le mois à venir (avril), sachant que les représentants ont respectivement pour nom : Brisse, Regian, Tanni et Rolle et qu’ils devront compléter les fichiers en question.c. Testez votre travail en saisissant les données fournies sur le site compagnon. Vérifiez que les totalisations s’effectuent correctement.

Proposition de réponseLes fichiers destinés aux représentants seront issus du modèle « Model_note_de_frais.xlt », mis à disposition par l’assistant(e) sur le réseau. Les représentants les compléteront et les sauvegarderont dans leur répertoire de travail, lorsque ce système sera mis en œuvre.Pour l’instant, les étudiant(e)s établissent ces fichiers à partir du modèle en question et les complètent avec les données contenues dans le fichier Word :« Chapitre2_Miseensituation_Notesdefrais.doc »

Au   cours  de   ce   travail,   il   y   a   lieu  de   vérifier  que   les   saisies   et   les   calculs   s’effectuent correctement   (au   besoin,   vérifier   à   l’aide   d’une   calculatrice).   Des   corrections   et   des modifications du modèle Excel initial peuvent s’avérer nécessaires.À  l’issue de cette phase, chaque étudiant dispose, dans son espace de travail,  des quatre fichiers Excel requis. Se pose alors le problème du nommage de ces fichiers, qui doit être systématique, comme on le verra à la question e.

Exemple :NDF_040N_BRISSE.xls NDF_040N_ROLLE.xlsNDF_040N_REGIAN.xls NDF_040N_TANNI.xls

Remarque : ces fichiers sont disponibles sur le site compagnon sous les noms suivants :– « Chapitre2_Miseenoeuvre_Ndf_040n_Brisse.xls »– « Chapitre2_Miseenoeuvre_Ndf_040n_Regian.xls »– « Chapitre2_Miseenoeuvre_Ndf_040n_Rolle.xls »– « Chapitre2_Miseenoeuvre_Ndf_040n_Tanni.xls »

d. Préparez la feuille récapitulative qui vous permettra de consolider les données en fin de mois et assurez-vous que la consolidation s’effectue correctement.

Proposition de réponseLa feuille récapitulative mensuelle peut être de structure assez simple :

Mode opératoire• Obtenir le total général des frais,  sachant que  les notes de frais totalisent  les frais en cellule E36 :– amener le curseur en cellule B10 ;– sélectionner Données/Consolider ;– cliquer sur Parcourir et naviguer jusqu’à la première feuille de frais (Brisse) ;– compléter la référence obtenue en tapant E36 (à droite du point d’exclamation) ;– cliquer sur Ajouter ;– réitérer cette manipulation pour les trois autres représentants ;– le total s’affiche en cellule B10.On obtient alors l’écran suivant :

• Afficher les montants de chaque représentant :– ouvrir, sous Excel, les quatre notes de frais ;– activer la feuille récapitulative ;– positionner le curseur en cellule B6 (total de Brisse) ;– saisir = (le signe « égal ») ;– ouvrir la note de frais de Brisse et amener le curseur en cellule E36 ;– valider en tapant sur la touche Entrée. Le montant de Brisse apparaît (724,68) ;– réitérer la manipulation pour les trois autres représentants.

• Afficher le mois en cours :– dans la feuille récapitulative, amener le curseur en cellule B3 ;– taper = (le signe « égal ») ;

– activer n’importe quelle note de frais ;– positionner le curseur en cellule C4 ;– taper sur la touche Entrée pour valider ;– formater   la  cellule  B3 par  Format/Cellule/Date  en  choisissant   le   format  du mois  et  de l’année (mars-01) :

– le mois considéré s’affiche ;– sauvegarder la feuille récapitulative sous le nom de « NDF_200N_RECAP ».

Remarque : ce fichier est disponible sur le site compagnon sous le nom de :« Chapitre2_Miseenoeuvre_Ndf_200n_Recap.xls »

e. Choisissez un système de dénomination des fichiers utilisés qui permette de les gérer de façon optimale.

Proposition de réponseOn peut facilement deviner que M. Saligny demandera un second niveau de consolidation : le niveau annuel. Cette anticipation conduit à décider que le classeur récapitulatif sera de périodicité   annuelle.   Les   récapitulations   mensuelles   s’effectueront   dans   12 feuilles   de   ce classeur, prévues à cet effet. Une treizième feuille sera affectée à la récapitulation annuelle des   frais   de   déplacement.

Voici une représentation possible de ce classeur récapitulatif :

L’assistant(e)  doit   impérativement  attribuer  des  noms de  fichiers  qui  permettent  de   les distinguer sans ambiguïté et dont le format est uniforme. En effet, pour chaque mois, le système ainsi conçu génère 4 fichiers. Pour un an, on obtiendra donc 48 fichiers. L’assistant(e) aura donc, en définitive, à gérer 49 fichiers Excel pour suivre les frais de déplacement.

SpécificationsVoici les spécifications suggérées pour la cohorte des 4 fichiers du mois d’avril N et de la feuille du mois d’avril (R04) du classeur récapitulatif :

NDF_040N_BRISSE.xls NDF_040N_ROLLE.xlsNDF_040N_REGIAN.xls NDF_040N_TANNI.xls

NDF_200N_RECAP.xls (feuille R04)

Nommage des fichiers

Identification Mois/année Nom propre/RECAP Extension

Fixe (3 car.) Fixe (4 car.) Variable/Fixe Fixe (3 car.) automatique

NDF 040N200N

BRISSERECAP

.xls