|
|
Outils et
méthodes bureautiques |
|
|
Site de l’auteur |
|
Avant-propos........................................................................................................................... I à VIII
1. UN TABLEAU DES VENTES............................................. 1
ANALYSER le problème au brouillon................................................... 2
Organiser le cadre général du tableau............................................... 3
Les intitulés de colonnes............................................................ 3
Les intitulés de lignes............................................................... 3
Repérer les cellules devant contenir des informations saisies........................ 3
Repérer les cellules pouvant contenir des formules de calcul........................ 3
Créer le tableau : premières observations............................................. 4
L’écran d’Excel....................................................................... 5
Désactiver le déplacement automatique et le trombone.
Utiliser le clavier pour lancer les commandes avec Alt.............................. 5
Créer le tableau : préparation du cadre général....................................... 6
Créer le cadre du tableau : saisir les libellés des
colonnes,
puis les libellés de lignes............................................................. 7
Modifier des cellules sans la souris.................................................. 7
Créer le tableau : SAISIR DES VALEURS............................................. 8
Saisir une longue série de nombres.................................................. 9
Sauvegarder sur disque le classeur sans attendre d’avoir fini....................... 9
Contrôler et assister la saisie........................................................ 11
SAISIR UNE FORMULE et
Saisir une formule avec la fonction SOMME......................................... 13
Recopier une formule................................................................ 13
SAISIR LES FORMULES et les COPIER............................................... 14
Saisir une formule de toutes pièces................................................. 15
Recopier les dernières formules...................................................... 15
Ajouter des titres, éventuellement avec un saut de ligne manuel................... 15
Donner des FORMES aux cellules...................................................... 16
Centrer une cellule sur plusieurs colonnes............................................ 17
Centrer les titres des colonnes...................................................... 17
Tous les nombres aérés : espaces entre les milliers et retrait droit................ 17
Changer la hauteur d’un groupe de lignes, et la largeur des colonnes............... 17
Centrer verticalement toutes les cellules du tableau................................. 17
Régler les couleurs de fond et les polices........................................... 17
Mise en forme sur une sélection disjointe........................................... 17
Copie de mise en forme.............................................................. 17
Bordure manuelle avec Format....................................................... 17
Finitions.............................................................................. 17
Contrôler
Utiliser le mode Aperçu avant impression pour la mise en page..................... 19
Imprimer un texte sur toutes les pages : En-tête et Pied de page................. 19
Imaginer des ÉVOLUTIONS............................................................ 20
Saisir de nouvelles données.......................................................... 21
INSÉRER des cellules.................................................................. 22
Insérer une série de colonnes avec le clavier........................................ 23
Calculer des POURCENTAGES.......................................................... 24
Insérer le signe % dans le titre des colonnes de pourcentage....................... 25
Calculer un pourcentage.............................................................. 25
Calculer des POURCENTAGES RECOPIABLES.......................................... 26
Recopier une formule non recopiable : erreurs imprévues et analyse................ 27
Rendre recopiable une formule avec adresse bloquée................................ 27
Reprendre la séquence pour le bloc janvier d’Avignon................................ 27
COPIER DES COLONNES ENTIÈRES À RÉPÉTITION................................. 28
Figer les volets dans les grands tableaux............................................ 29
Copier plusieurs fois un bloc de cellules avec le clavier.............................. 29
Analyser un message #DIV/0! et rendre une formule conditionnelle................ 29
Mettre en page des GRANDS TABLEAUX.............................................. 30
Imprimer les titres sur toutes les pages............................................. 31
Changer l’orientation : Portrait ou Paysage.......................................... 31
Forcer l’impression sur une seule page............................................... 31
CONTRÔLER les formules.............................................................. 32
Contrôler les formules : l’erreur née de l’insertion dans une plage.................. 33
Recopier une formule sans recopier son format..................................... 33
PROTÉGER les formules................................................................ 34
Comprendre les objectifs et principes de la protection des cellules................. 35
Déverrouiller le chemin de saisie et protéger la feuille.............................. 35
Insérer des GRAPHIQUES simples..................................................... 36
Soigner la sélection préalable au dessin du graphique............................... 37
Sauvegarder le travail sur support amovible : disquette, clef USB.................. 37
AUTOÉVALUATION sur les BASES D’EXCEL.......................................... 38
2. UN PLAN DE REMBOURSEMENT........................................ 39
ANALYSER les contraintes de réalisation du document................................ 40
Déterminer ce qui devra être saisi et ce qui pourra être calculé.................... 41
Contrôler les saisies : interdire de taper des données absurdes..................... 41
Assister la saisie : afficher des messages (consignes et alertes.................... 41
Prévoir les calculs relatifs aux remboursements..................................... 41
RÉALISER le document, première partie :
ORGANISER
Saisir les textes simples et régler leurs bordures et alignements................... 43
Contrôler la saisie et assister l’opérateur de saisie par des messages............... 45
Deuxième partie : LES CALCULS....................................................... 46
Créer un compteur à longueur variable (selon le nombre de mensualités saisi)...... 47
Calculer des dates décalées d’un mois et toujours au 16 du mois................... 49
Calculer les mensualités : premières valeurs de base................................ 51
1. Capital restant dû, au début du mois, avant les calculs du mois (col. D)....... 51
2. Remboursement du capital (col. E)............................................... 51
Calculer le cumul remboursé, les intérêts et les mensualités........................ 53
1. Cumul capital
remboursé, en fin du mois,
après le calcul de la partie fixe (col. F)......................................... 53
2. Intérêts (col. G)................................................................. 53
3. Mensualité (col. H)............................................................... 53
Calculer la synthèse de l’emprunt.................................................... 53
1. Total remboursé (H6) et Total des intérêts (H8)................................ 53
2. Taux effectif (H10)............................................................. 53
Troisième partie :
rédiger des calculs LISIBLES grâce aux NOMS de cellules............................ 54
Créer automatiquement des noms grâce aux étiquettes proches..................... 55
1) Commencer par nommer les quatre cellules à saisir (bleues)..................... 55
2) Relire immédiatement tous vos noms............................................. 55
3) Nommer ensuite toutes les cellules du tableau des mensualités.................. 55
4) Supprimer tous les noms (pour vous exercer).................................... 55
5) Et recommencer plusieurs fois cette page (pour vous exercer).................. 55
Remplacer dans les formules les références en noms lisibles........................ 57
Quatrième partie :
un tableau dont
Prévoir des bordures variables avec des formats conditionnels...................... 59
1. Analyse du problème (ne faites rien pour l’instant,
il faut d’abord comprendre)...................................................... 59
2. Réalisation (c’est facile et rapide quand le problème est bien analysé)......... 59
Faire disparaître tous les zéros de la feuille........................................ 59
FINALISATION : TESTS et PROTECTION........................................... 60
Vérifier tous les contrôles de saisie................................................. 61
Vérifier méthodiquement tous les calculs............................................ 61
Vérifier la mise en page, en Aperçu................................................. 61
Protéger les formules, sans bloquer les cellules à saisir bien sûr.................... 61
Tester le chemin de saisie, avec Tab et Maj+Tab................................... 63
Indiquer dans les consignes (au moins dans les premiers
messages)
ce que l’utilisateur peut faire........................................................ 63
Avant le dernier enregistrement, il faut vider le chemin de saisie.................. 63
Avant le dernier enregistrement, il faut aussi placer le
pointeur
sur la première cellule à saisir....................................................... 63
Derniers contrôles : quittez Excel, relancez-le, rouvrez le classeur................ 63
Quittez Excel, et faites une copie du classeur sur deux disquettes................ 63
AUTOÉVALUATION sur les CALCULS................................................. 64
3. UNE FICHE DE CALCUL DE PRIX....................................... 65
ANALYSER les contraintes de réalisation du document : organiser la saisie.......... 66
Déterminer ce qui devra être saisi et ce qui pourra être calculé.................... 66
Comprendre les deux grands cas de recherche dans des tables..................... 67
Étape 1 - ANALYSER la première zone : "LE VOYAGE............................... 68
Récupérer automatiquement les infos signalétiques sur les
voyages
à partir d’un identifiant saisi par l’opérateur (choisi dans une liste................. 69
Une liste déroulante pour, à la fois, contrôler et assister la saisie................. 69
Vérifier avant de programmer une recherche sur un
identifiant :
JAMAIS DE DOUBLONS dans la première colonne
de la table..................... 69
Isoler les tables et infos stables dans un classeur séparé........................... 69
Préliminaires : préparer
Créer un classeur séparé pour centraliser les tables de
référence.
Il faut ne mémoriser chaque information stable qu’une seule fois
sur le poste de travail : simplicité, rapidité, fiabilité............................... 71
Prévoir dans chaque table une première colonne qui servira
d’indice de recherche, et un cas zéro.............................................. 71
Donner un nom fiable aux cellules qui seront utiles dans les formules.............. 73
Étape 1 - le voyage : faciliter et contrôler
Créer un nouveau classeur........................................................... 75
Prévoir une saisie assistée et contrôlée du nom du voyage.......................... 75
1 Donner d’abord un nom à une liste externe...................................... 75
2 Limiter ensuite la saisie de la cellule B9 aux valeurs de cette liste externe..... 75
3 Programmer des messages de conseils et d’erreur (en cas de saisie invalide).... 75
4 Tester tout ça................................................................... 75
Le problème de la mise à jour des tables Excel..................................... 77
Problème : il ne faut pas figer la table nommée en taille réduite.................. 77
• Une solution simple
mais qui pose un autre problème :
en nommant ListeVoyages toute la colonne $A$3:$A$65536,
on s’assure de pouvoir ajouter des voyages quasiment sans limite................ 77
• Une solution plus
complexe, mais réglant bien le problème : on peut
calculer la zone qui portera un nom : fonctions DECALER et NBVAL............ 77
Étape 1 - le voyage (fin) : RÉCUPÉRER UNE INFO
DANS UNE TABLE
À PARTIR D'UN IDENTIFIANT SAISI............................................... 78
B11, B12, B13 : récupérer une info dans une
table-fichier, grâce à une donnée connue,
avec RECHERCHEV et FAUX......................................................... 79
Étape 2 - ANALYSER la seconde zone : "LES VOYAGEURS.......................... 80
Appliquer un taux en fonction d’un montant et d’une table des seuils............... 81
PARAMÉTRER le calcul de
Certains libellés (étiquettes) sont variables et seront donc calculés................. 81
Étape 2 - les voyageurs : Mettre en place LES TABLES de référence............... 82
Prévoir dans la table de recherche une première colonne
TRIÉE,
qui servira d’indice de recherche, avec un cas "zéro"............................... 83
Donner un nom à toutes les cellules qui seront utiles dans les formules............ 83
Ne fermez pas ce classeur : zappez, sinon les listes déroulantes sont HS......... 83
Étape 2 - les voyageurs : Préparer le cadre
des calculs, contrôler la saisie,
nommer les cellules utiles, UTILISER LES NOMS dans les formules.................. 84
Créer le cade des voyageurs (titres, bordures, largeurs)........................... 85
Nommer les cellules utiles aux prochains calculs : B11 à B13 et F10 à F15........ 85
Contrôler la saisie de F10........................................................... 85
Utiliser les noms de cellules dans les calculs : calculer F11......................... 85
Étape 2 - les voyageurs : RENDRE VARIABLE UNE
ÉTIQUETTE,
et RÉCUPÉRER UN TAUX EN FONCTION D’UN SEUIL.............................. 86
Calculer un texte variable (combinaison de formule et de format).................. 87
1 Récupérer d’abord le
taux dans la table des remises,
en fonction du nombre de voyageurs saisi en F10............................... 87
2 Puis, ajouter les
textes constants à l’affichage de la cellule E12,
avant et après la valeur numérique de la cellule,
grâce à la commande Format, Cellule, Personnalisé.............................. 87
Étape 2 - les voyageurs : Derniers CALCULS......................................... 88
Récupérer le taux de TVA à l’extérieur et l’accompagner d’un texte................ 89
Calculer la remise en utilisant l’étiquette calculée affichant le taux................ 89
Copier une forme avec l’outil 
.................................................... 89
Calculer les dernières formules et contrôler les résultats à la calculette........... 89
FINALISATION : OPTIMISATION, TESTS, PROTECTION et derniers RÉGLAGES. 90
Faciliter les allers-retours du tableau principal vers les
tables avec 
........... 91
Vérifier
FINALISER le tableau............................................................... 91
1) Masquez les zéros (d’un coup sur toute la feuille)............................... 91
2) Protégez les formules (pas les cellules à saisir)................................. 91
3) Testez soigneusement le chemin de saisie....................................... 91
4) Videz enfin le chemin de saisie (les données variables) : Suppr................. 91
5) Positionnez le pointeur sur la 1ère cellule à
remplir
avant d’enregistrer une dernière fois : Ctrl S................................... 91
FINALISATION (fin) : DERNIER TEST, grandeur nature............................ 92
Testez le classeur depuis le départ : depuis le lancement d’Excel................... 93
Enregistrer une macro-commande autoexécutable................................... 93
Dernier test et sauvegardes sur disquettes ou clef USB............................ 93
AUTOÉVALUATION sur les RECHERCHES............................................. 94
4. UN PLANNING DES CONGÉS........................................... 95
ANALYSER les contraintes de réalisation du document................................ 96
RÉALISER le document : préparer la structure et CALCULER UN CALENDRIER..... 98
Incliner le texte de toutes les cellules à -90....................................... 99
Régler la largeur des colonnes....................................................... 99
Créer rapidement un compteur....................................................... 99
Personnaliser une date à l'aide d'un texte dans une seule et même cellule......... 99
Construire le calendrier.............................................................. 99
Calculer les titres recopiés sur toutes les pages..................................... 101
RÉALISER le document :
organiser les SAISIES et les CALCULS, puis les PROTECTIONS...................... 102
Préparer la zone des bandeaux de couleur, zone de saisie du planning.............. 103
Organiser la zone des décomptes : calculs et formats.............................. 103
Finaliser le tableau................................................................... 103
AUTOÉVALUATION sur les PLANNINGS.............................................. 104
5. UN OUTIL D’ANALYSE DES VENTES.................................. 105
ANALYSER les objectifs............................................................... 106
Comprendre l’importance des colonnes discriminantes dans une grande table unique 107
Placer en tête les données de synthèse, les calculs, plutôt qu’en bas.............. 107
Convertir les données brutes en provenance d’un AS 400........................... 107
Connaître le danger des tris sous Excel et soigner la sélection préalable........... 107
RÉGLER L’ÉCRAN pour travailler dans les GRANDS TABLEAUX....................... 108
Adapter le zoom et choisir une résolution d’écran élevée pour la lisibilité........... 109
Colorer une ligne sur deux, par calcul............................................... 109
Prévoir de la place pour mettre les totaux en tête,
dans un volet figé pour garder la synthèse sous les yeux........................... 109
RECONSTRUIRE DES DATES à partir d’extraits calculés : FONCTIONS TEXTE..... 110
Convertir des données : fonction DATE et fonctions texte......................... 111
Remplacer les originaux : collage spécial, en valeur puis en format............... 111
ORDONNER une liste et NE MONTRER QUE CERTAINES LIGNES................... 112
Trier en sécurité..................................................................... 113
Filtrer une liste et effectuer des calculs sur les seules lignes affichées............ 113
FILTRER ENTRE DEUX DATES et prévoir des CALCULS CONDITIONNELS......... 114
Enrichir la zone d’en-tête qui peut devenir une synthèse complète.................. 115
Filtrer entre deux dates............................................................. 115
Prévoir des calculs conditionnels dans d’autres feuilles.............................. 115
AUTOÉVALUATION sur les LISTES................................................... 116
Sommaire
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.