Excel et la gestion du temps - Editions ENI

Excel et la gestion du temps Concevoir planning, échéanciers, tableaux d'absence...

Introduction A. À qui s'adresse ce livre ?

11

B. Présentation

11

C. Comment lire ce livre ?

12

1. Informations d'ordre général 2. Projets individuels 3. Précisions sur les projets proposés D. Téléchargement

12 12 12 13

L'étape de conception A. Introduction

17

B. En quoi consiste cette étape de conception ?

17

C. Projet exemple

17

1. Énoncé 2. Quel est l'objectif principal à atteindre ? Quelles sont les contraintes ? a. Formulation des objectifs et contraintes b. Analyse des contraintes 3. Quelles données doivent être saisies ? Comment les organiser ?Quelle sera la taille tableau ? a. Définition des données b. Type de données c. Taille du tableau 4. À qui le résultat final est-il destiné ? Sur quel support doit-il être présenté ? a. Destinataire du tableau

www.editions-eni.fr

© Editions ENI

17 18 18 19 du 20 20 21 22 22 22

1/9


b. Le support final 5. Le concepteur est-il l'utilisateur ? Qui devra saisir ou exploiter les données ? 6. Ce tableau servira-t-il une ou plusieurs fois ? Est-ce une matrice ? D. Choix des outils Excel

23 23 23 24

1. Indications - Commentaires - Listes de choix a. Saisir une indication dans une cellule b. Insérer un commentaire c. Afficher une info-bulle d. Liste de choix e. Interdire des valeurs 2. Écrire plusieurs lignes dans la même cellule a. Passage à la ligne forcée b. Renvoyer à la ligne automatiquement 3. Formule de calcul 4. La mise en page a. L’orientation du document b. Les marges et/ou l’ajustement de l’échelle c. Répétition des lignes ou colonnes de titre 5. Reproduire le même tableau a. Copier-coller b. Copier puis collage spécial c. Copier la feuille entière d. Groupe de travail 6. Protéger le tableau a. Protéger le fichier en lecture b. Protéger le fichier en modification c. Protéger le classeur d. Protéger la feuille E. Conclusion

24 24 25 25 25 25 26 26 26 27 27 27 28 28 28 28 28 28 29 29 29 29 29 29 30

Introduction à la notion de temps

www.editions-eni.fr

© Editions ENI

2/9


A. Que signifie gérer le temps sur Excel ?

33

B. Comment saisir une date ?

33

C. Comment saisir un temps ?

35

D. La codification réelle des dates et heures

35

1. Les dates 2. Les heures

35 36

E. Les formats

36

1. Les paramètres régionaux 2. Inconvénient des formats date et heure a. Reconnaissance des formats saisis b. Reconnaissance des formats importés 3. Les formats personnalisés F. Travailler avec les heures

36 38 38 38 44 44

1. Convertir un temps en fraction d'heures, de minutes, ou de secondes 2. Calcul avec des temps a. Additionner des temps b. Soustraire des temps

44 45 45 47

G. Les calculs avec les dates

47

H. Un peu d'histoire

50

1. Astronomie 2. Les calendriers a. Le calendrier romain b. Le calendrier julien c. Le calendrier grégorien d. Les autres calendriers

www.editions-eni.fr

50 51 52 52 52 52

© Editions ENI

3/9


Construire rapidement l'ossature d'un planning annuel A. Énoncé du projet

57

B. Étape 1 : analyse du projet

57

1. Les questions-réponses 2. Le résultat à obtenir

57 58

C. Étape 2 : choix des commandes Excel

59

1. La saisie des dates 2. Afficher le jour en lettres et en chiffres 3. Éviter les samedis et dimanches a. La fonction JOURSEM b. La fonction SI 4. Un planning par salle 5. Une année entière par planning 6. Redémarrer chaque année avec un planning vide et dont les dates sont mises à jour 7. Protéger la feuille D. Étape 3 : création du planning pas à pas

59 60 60 61 62 65 66 67 67 68

Gestion des absences A. Énoncé du projet

83


83


83 84


85

1. Gagner du temps dans la saisie des absences 2. Comment gérer un tableau de grande taille ?

www.editions-eni.fr

© Editions ENI

85 86

4/9


3. Regroupement des données a. Masquer des colonnes : méthode basique b. Masquer/afficher des colonnes méthode avancée : le plan 4. Repérage rapide des données par les couleurs a. Les couleurs de remplissage b. La mise en forme conditionnelle c. Visibilité des en-têtes de lignes et colonnes 5. Alerte en cas de sous-effectif dans une équipe 6. Les calculs statistiques a. La fonction NB.VIDE b. La fonction NB.SI c. La fonction NBVAL D. Étape 3 : gestion des absences pas à pas 1. Les éléments de base : salariés et dates 2. Figer les volets 3. Listes déroulantes 4. Mise en forme conditionnelle 5. Le plan 6. Calcul du nombre de présents par équipe 7. Formule d'alerte en cas de sous-effectif 8. Calcul du nombre d'absences par mois et par salarié 9. Rectifier rapidement les références dans plusieurs formules 10. Regroupement des données par mois 11. Nombre d'absences par type pour chaque équipe 12. Années bissextiles

89 90 90 91 91 92 92 94 95 95 95 96 97 97 99 99 101 102 104 106 108 109 110 111 113

Gestion des échéances A. Énoncé du projet

117


118


www.editions-eni.fr

118 119

© Editions ENI

5/9


a. Liste des factures b. Trésorerie c. TVA d. Chiffre d'affaires par fournisseur e. Liste des factures à payer C. Étape 2 : choix des commandes Excel

122

1. Validation de données 2. Fonction SI a. Automatiser le calcul des dates d'échéance b. Automatiser le calcul du montant des échéances c. Vérification de la saisie manuelle des échéances 3. Formules matricielles a. Qu'est-ce qu'une formule matricielle ? b. Formule matricielle qui renvoie plusieurs valeurs c. Formule matricielle qui renvoie une seule valeur d. Formule matricielle avec condition e. Passer par des calculs intermédiaires f. Contraintes de la formule matricielle g. Avantages et inconvénients d'une formule matricielle 4. Les filtres a. Les filtres simples ou automatiques b. Les filtres avancés ou élaborés 5. Tableau croisé dynamique a. Quelles autres solutions avons-nous ? b. Pourquoi choisir le tableau croisé dynamique ? c. Contraintes des filtres et des tableaux croisés dynamiques 6. Interdire la modification des données 7. Faut-il protéger les formules ? D. Étape 3 : gestion des échéances pas à pas 1. Limiter la saisie à deux valeurs pour la colonne Type Tiers 2. Création des listes déroulantes Taux de TVA et Mode paiement 3. Calcul des dates d'échéances 4. Calcul des montants à payer à chaque échéance

www.editions-eni.fr

119 121 121 121 122

© Editions ENI

122 123 123 124 124 124 124 125 125 126 126 129 129 129 129 129 130 130 131 132 132 132 133 133 136 138 142

6/9


5. Contrôle des échéances saisies manuellement 6. Calcul de la TVA sur chaque échéance 7. Création du tableau de trésorerie 8. Calcul de la TVA due par mois 9. Calcul du chiffre d'affaires par fournisseur a. Création du tableau croisé dynamique b. Actualisation du tableau croisé dynamique c. Modification des références de plage de données 10. Afficher la liste des factures à payer à une date donnée 11. Protection a. Protection du fichier b. Protection partielle de la feuille 12. Conclusion

143 143 144 150 152 152 159 162 164 171 171 172 172

Gestion des heures supplémentaires A. Énoncé du projet

175

1. Énoncé 2. Informations légales

175 175


176

1. Les questions-réponses 2. Le résultat à obtenir a. Suivi journalier des heures supplémentaires b. Synthèse hebdomadaire des heures supplémentaires c. Synthèse mensuelle de tous les salariés C. Étape 2 : choix des commandes Excel

178

1. Feuille de suivi des heures supplémentaires a. Déterminer le numéro de la semaine b. Calcul du mois c. Calcul du mois de paie d. Calcul des heures d'absences e. Calcul des heures travaillées

www.editions-eni.fr

176 177 177 178 178

© Editions ENI

178 179 181 181 182 182

7/9


f. Calcul des heures supplémentaires 2. Tableau de synthèse mensuel a. Lier des classeurs b. Quelle méthode utiliser pour calculer les résultats de synthèse ? D. Étape 3 : gestion des échéances pas à pas

182 183 184 187 188

1. Création du modèle de suivi des heures 188 a. Calcul du numéro de la semaine 188 b. Calcul des heures travaillées 189 c. Calcul des heures supplémentaires 189 d. Calcul des heures d'absence 190 e. Calcul du mois civil 190 f. Calcul du mois de paie des heures supplémentaires 190 g. Calcul des heures supplémentaires par semaine converties en fraction d'heures 192 h. Calcul des heures supplémentaires par taux de majoration 192 i. Report du mois de paie dans la synthèse hebdomadaire 193 j. Dernières retouches avant enregistrement en tant que modèle 194 k. Enregistrer en tant que modèle 195 2. Tableau de synthèse mensuelle 196 a. Préparation des critères 196 b. Mise en place des calculs avec liaison 196

Calculette dates de livraison A. Énoncé du projet

203


203


203 204


204

1. Calcul de la date de livraison a. NB.JOURS.OUVRES et NB.JOURS.OUVRES.INTL

www.editions-eni.fr

© Editions ENI

204 207

8/9


b. SERIE.JOUR.OUVRE et SERIE.JOUR.OUVRE.INTL c. JOURSEM d. SI 2. Gérer des noms de cellule a. Nommer une cellule b. Supprimer un nom c. Modifier la ou les références d'un nom 3. Remise à zéro du tableau 4. La validation des données et la protection des formules D. Étape 3 : notre calculette pas à pas

222

1. Création des identifiants de chaque cellule 2. Nos formules 3. Création de la macro et du bouton de remise à zéro 4. Limiter la saisie à des données précises : validation des données 5. Protection de votre calculette 6. Enregistrement d'un fichier contenant une macro

Index

www.editions-eni.fr

211 211 212 212 212 213 214 214 221

223 224 227 230 234 234

237

© Editions ENI

9/9