|
Outils et
méthodes bureautiques |
|
Site de l’auteur |
Dernière mise
à jour :
1er septembre 2004.
·
Consultez le
corrigé de la grille d’autoévaluation
·
Téléchargez le
corrigé de l’exercice : Ventes.xls
·
Consultez le
corrigé de la grille d’autoévaluation
·
Téléchargez le
corrigé de l’exercice : FicheDeCalculFinancement.xls
·
Consultez le
corrigé de la grille d’autoévaluation
·
Téléchargez le
corrigé de l’exercice : TouristFicheCalcul.zip
·
Consultez le
corrigé de la grille d’autoévaluation
·
Téléchargez le
corrigé de l’exercice : PlanningDesConges.xls
·
Télécharger
le document de départ pour le chapitre 5 : BaseDeDonneesVentes.xls
· Consultez le corrigé de la grille d’autoévaluation
·
Téléchargez le
corrigé de l’exercice : BaseDeDonneesVentesCompletee.xls
·
Télécharger la dernière version de l’AMI, aide-mémoire informatique
universel.
·
Télécharger l’exemple de dossier de l’étudiant, 40 pages (attention : 2,6
Mo).
·
Télécharger une série d’exercices pour rendre dynamique un titre ou un libellé.
Je vérifie que j’ai bien progressé…
Je sais … CORRIGÉ |
Commandes et procédures |
Repérer
sur un brouillon les zones dangereuses d’un tableau (à limiter au max.) |
Limiter au maximum la saisie, source
d’erreurs, en calculant tout ce qui est calculable, en vérifiant toutes les
frappes, autant que possible, en assistant la saisie par des messages clairs,
en protégeant le tableau… |
Annuler
une commande avec le clavier. La
rétablir en cas d’erreur. |
CTRL Z pour annuler, CTRL
Y pour rétablir. |
Montrer
à l’écran la zone Nom, ainsi que |
La zone Nom affiche le plus souvent l’adresse
de la cellule active, A1 par exemple, en haut à gauche de l’écran, sous |
Désactiver
ou réactiver le déplacement auto de la sélection quand on tape Entrée |
Alt O, O, M, Alt V : Outils, Options,
onglet Modification, Déplacer la sélection après validation. |
Créer automatiquement
une suite de mois sans les saisir tous |
Saisir le premier mois, puis glisser sur la poignée
de recopie. |
Valider
une saisie et se déplacer en même temps avec une seule touche |
Alt O, O, M, Alt V : Outils, Options,
onglet Modification… Cocher Déplacer la sélection après validation
et choisir le sens de déplacement. Ensuite, c’est la touche Entrée qui vaut à
la fois Validation et déplacement. |
Copier
un bloc de cellules par une simple glissade de souris |
CTRL + glisser sur le bord
(pas sur le coin !). |
Sélectionner
des cellules avec le clavier |
Déplacement jusqu’à la première cellule, puis MAJ
déplacement jusqu’à la dernière. |
« Entrer »
dans une cellule avec le clavier (pour la corriger) |
F2, puis les flèches
déplacent le pointeur dans la formule. |
Sélectionner
de mot en mot avec le clavier
(importante technique) |
Ctrl Maj Flèches
(Maj pour étendre, et Ctrl pour filer de mot en mot) |
Changer
la largeur d’une colonne, faire
calculer une largeur automatique |
Glisser sur le bord droit
du numéro de la colonne. Largeur auto = double clic sur ce bord droit. |
Créer
un sous-dossier en cours de commande d’enregistrement sur disque |
Cliquer sur l’outil dans la boîte de dialogue Enregistrer sous… |
Contrôler
la saisie : imposer dans une cellule la frappe d’un nombre cohérent |
Alt D, V : Données, Validation,
Autoriser… Nombre entier ou Décimal (pour accepter les virgules). |
Sélectionner
des cellules disjointes (des blocs séparés) |
Cliquer ou glisser sur la première cellule (le
premier bloc), puis Ctrl cliquer sur les autres cellules (Ctrl
glisser sur les autres blocs). |
Lancer
des commandes du menu avec le clavier (sans souris) |
Alt + lettre d’accès rapide
(lettre soulignée dans le nom des commandes et dans le nom des champs de
boîtes de dialogue). |
Prévoir
un message d’aide à la saisie qui apparaît au passage sur une cellule |
Alt D, V : Données, Validation,
onglet Message de saisie… |
Faire
une somme en bas de colonne ou en bout de ligne |
Sélectionner la cellule qui doit calculer la
somme, puis cliquer sur l’outil Somme automatique ,
bien contrôler visuellement la plage proposée (contour clignotant), éventuellement
corriger cette plage en glissant dessus ou en utilisant les flèches avec Maj…
Raccourci Ctrl = (contrôle égale). |
Modifier
la plage proposée par l’outil Somme
automatique |
Glisser sur la nouvelle plage avec la souris, ou bien
utiliser les flèches avec Maj. |
Créer
une addition manuellement (sans utiliser l’outil ) |
Sélectionner la cellule qui doit calculer la
somme, taper le signe = (pour dire « cette cellule égale…), cliquer sur
les différentes cellules à sommer en les séparant avec le signe + (plus). |
Recopier
sur les cellules voisines, recopier des formules à distance |
Copier vers les voisines : glisser
sur la poignée de recopie. |
Changer
le niveau de zoom à l’écran |
Cliquer une fois sur le chiffre dans l’outil |
Adapter
exactement le niveau de zoom écran aux
cellules sélectionnées |
Sélectionner les cellules à cadrer,
|
Centrer
sur plusieurs colonnes, retirer ce format, |
Sélectionner les cellules à fusionner,
puis cliquer sur l’outil |
Changer
la hauteur de plusieurs lignes, centrer verticalement |
Sélectionner les lignes à régler en glissant sur
leurs numéros, puis glisser sur le trait bas du numéro d’une des lignes de la
sélection. |
Mettre
en gras ou en italiques avec le clavier |
Ctrl G et Ctrl I
(raccourci valable pour presque tous les logiciels sous Windows). |
Copier
une mise en forme de cellule |
Cliquer sur le modèle, « tremper le pinceau
dans cette forme » avec un clic sur l’outil ,
puis « badigeonner les cellules destination » avec le pointeur en
forme de pinceau. |
Masquer
tous les zéros, masquer le quadrillage, montrer les sauts de page |
Alt O, O, A… : Outils, Options,
onglet Affichage, décocher Valeurs zéro et Quadrillage,
et cocher Sauts de page. |
Renommer
une feuille, afficher ce nom de feuille dans un pied de page |
Double cliquer sur le nom de la feuille
(ou cliquer droit Renommer sur ce nom de feuille), puis taper le
nouveau nom, et valider avec Entrée. |
Régler
la mise en page : changer les marges, l’orientation, ajuster à une page |
Alt F, P : Fichier, Mise en page… onglets Page, Marges, En-tête/Pied
de page et Feuille… |
Insérer
des colonnes |
Sélection d’au moins une cellule dans la colonne
à repousser par l’insertion, |
Répéter
la dernière commande avec le clavier |
F4 |
Calculer
un pourcentage recopiable |
Diviser la partie par l’ensemble, en figeant l’adresse
de l’ensemble avec un dollar $ (touche F4). |
Copier
avec le presse-papiers et le clavier |
Sélection du modèle, Copier au
presse-papiers avec Ctrl C (ou Ctrl Ins), puis |
Revenir
au début du document avec le clavier |
Ctrl Origine (Ctrl Début). |
Rendre
un calcul conditionnel pour éviter le message #DIV/0! |
Encadrer la formule de calcul de pourcentage dans
une fonction SI pour rendre
la formule conditionnelle : |
Enregistrer
avec le clavier |
Ctrl S (S comme Sauvegarder) |
Figer
les volets à l’écran, fixer les titres sur toutes les pages papier |
Sélectionner la première
cellule placée en dehors des volets (sous le volet haut et à droite du
volet gauche), puis lancer Fenêtre,
Figer les volets. |
Recopier
une formule sans recopier sa forme |
Glisser droit sur la poignée de recopie,
et choisir Recopier le format après avoir relâché la souris. |
Protéger
les libellés et les formules, mais laisser libre un chemin de saisie |
Retirer le format verrouillé à toutes les
cellules du chemin de saisie (Format, Cellule,
onglet Protection), |
Bien
mettre en évidence le chemin de saisie pour aider l’opérateur à anticiper les
mouvements du pointeur avec Tab |
Donner une couleur de fond identique,
plutôt pâle et discrète, toujours la même d’un classeur à l’autre, à toutes
les cellules à saisir. |
Nommer
le chemin de saisie, se déplacer rapidement sur ce chemin |
Sélectionner les cellules du chemin de saisie avec
Ctrl (sélection disjointe), cliquer une fois sur Quand la feuille est protégée,
utiliser Tab pour avancer sur le chemin de saisie, et Maj Tab pour
reculer. |
Sélectionner
le chemin de saisie grâce à son nom |
Pour sélectionner rapidement le chemin de saisie,
développer la liste des noms en cliquant sur la flèche à droite de |
Sélectionner
des blocs disjoints pour en tirer un graphique |
Glisser sur le premier bloc, puis Ctrl glisser
sur les blocs suivants. |
Dimensionner,
déplacer un graphique |
Dimensionner
un objet graphique : glisser sur une poignée (un des 8 petits
carrés placés autour des objets). |
Effectuer
une copie de sauvegarde du disque dur vers un support amovible |
Ne surtout pas effectuer cette
copie à partir d’Excel ! |
Citer
quelques supports amovibles et décrire leur intérêt |
Disquette |
Je vérifie que j’ai bien progressé…
Je sais… CORRIGÉ |
Commandes et procédures |
Créer un brouillon d’objectif avant de créer un tableau |
Déterminer tout ce qui pourra
être calculé
et limiter au maximum ce qui
devra être saisi. |
Changer l’unité monétaire de mon poste en euro € |
Démarrer, Paramètres, Panneau de
configuration : Paramètres régionaux, onglet Symbole monétaire. (Sous
Windows XP : Panneau de config, Options régionales et linguistiques,
Personnaliser, onglet Symbole monétaire...) |
Choisir un format Monétaire
pour des cellules (pas le style Monétaire :
le Format…) |
Alt T, C : Format,
Cellule, onglet Nombre, catégorie Monétaire… |
Saisir une date sans caractère inutile |
Taper le jour, slash
/, le mois et Entrée. Pas besoin de taper
l’année si c’est l’année en cours. |
Personnaliser finement un format de date |
Alt T, C : Format,
Cellule, onglet Nombre, catégorie Personnalisé j
le chiffre du jour sur une position : 1, 2, 3... jj
le chiffre du jour sur deux positions (pour être sûr des alignements dans une
colonne de dates). Exemple : 01, 02, 03... jjj
le nom du jour sur 3 positions (pour être sûr des alignements dans une colonne
de dates). Exemple : lun, mar, mer... jjjj
le nom du jour en entier. Exemple : lundi, mardi, mercredi... m
le chiffre du mois sur une position. Exemple : 1, 2, 3... mm
le chiffre du mois sur deux positions (alignements dans une colonne de
dates). Exemple : 01, 02, 03... mmm
le nom du mois sur 3 positions (alignements dans une colonne de dates).
Exemple : jan, fév, mar... mmmm
le nom du mois en toutes lettres. Exemple : janvier, février, mars.. aa
le millésime de l'année sur 2 chiffres. Exemple : 03 aaaa
l'année entière sur 4 chiffres. Exemple : 2003 |
Programmer des contrôles de saisie (partout où c’est possible)
et des messages d’erreur |
Alt D, V : Données,
Validation, onglets Options, Message de
saisie et Alerte d’erreur… |
Représenter un enchaînement logique (algorithme) de façon
graphique (organigramme) |
Représenter les
questions (toujours binaires, n’acceptant que deux réponses : oui ou
non) dans des losanges, et les actions dans des rectangles. |
Créer un compteur calculé |
Saisir la première
valeur du compteur dans une cellule, et, dans la
cellule suivante, saisir la formule « égale la cellule précédente
plus un » (exemple =B14+1), puis recopier la formule autant
de fois que nécessaire. |
Rendre un compteur conditionnel par rapport à un maximum à ne
pas dépasser, et à un minimum à atteindre pour commencer |
=SI(précédente=0 ;
0 ; SI (précédente = limite ; 0 ; précédente + 1) ) |
Extraire d’une date son année, son mois, son jour |
JOUR(celluledateréf), MOIS(celluledateréf), ANNEE(celluledateréf) (annee sans accent) |
Recomposer une date de toutes pièces, en utilisant des variables
ou des constantes |
Utiliser la fonction
DATE qui a besoin de ces arguments : DATE(année;mois;jour) |
Créer un retrait par rapport au bord gauche |
Utiliser les outils . |
Donner un nom isolé à des cellules |
Sélectionner
la ou les cellules à nommer, puis : |
Relire un nom donné et vérifier qu’il est correct |
Cliquer sur la flèche à
droite de |
Corriger un nom mal orthographié |
Il faut supprimer le nom
Alt I, N, D : Insertion, Nom, Définir, sélection
du nom et Alt S : Supprimer… |
Corriger une plage nommée mal définie |
Alt I, N, D : Insertion,
Nom, Définir, sélection du nom,
modification de la plage nommée dans le champ Fait référence à et Ajouter. |
Donner d’un coup un nom à toute une série de cellules, en se
servant de leur étiquette (texte voisin) |
Sélectionner à la fois
les étiquettes et les cellules à nommer, puis lancer Alt I, N, C : Insertion,
Nom, Créer… |
Appeler la liste des noms avec le clavier (en cours de création
ou de modification de formule) |
F3 |
Arrondir vraiment en profondeur, c’est-à-dire tronquer
réellement les microdécimales |
Prévoir d’utiliser dans
la cellule la fonction ARRONDI( formule à arrondir ; nb de décimales) |
Copier une mise en forme d’une cellule à l’autre |
Cliquer sur le modèle, « tremper le pinceau dans
cette forme » avec un clic sur l’outil ,
puis « badigeonner les cellules destination » avec le pointeur en
forme de pinceau. |
Sélectionner rapidement un mot |
Double cliquer
sur le mot ou Ctrl Maj Flèche |
Faire à la fois un choix dans une boîte de dialogue, et valider
la boîte, d’un seul geste |
Double cliquer
sur le choix. |
Recopier d’un geste une cellule vers la fin du bloc |
Double cliquer sur la
poignée de recopie. Ceci ne fonctionne que si Excel dispose d’un
repère à côté : une série de cellules déjà pleines qui lui permettent
de deviner la longueur voulue pour la recopie, par imitation. |
Figer une référence (adresse) pour qu’elle ne soit pas
transformée lors d’une recopie |
Mettre un dollar
$ devant le caractère à figer. |
Aider l’opérateur à repérer visuellement les cellules à saisir
(pour anticiper les mouvements du pointeur) |
Donner une couleur de fond identique,
plutôt pâle et discrète, toujours la même d’un classeur à l’autre, à toutes
les cellules à saisir. |
Aider l’opérateur à se déplacer d’une saisie à l’autre |
Déverrouiller
(avec Format, Cellule, onglet Protection) les
cellules à saisir et protéger les autres (avec Alt O, E, F : Outils,
Protection, Protéger la feuille (pas de mot de passe) Entrée). Ensuite, l’opérateur peut filer directement
vers la prochaine cellule à saisir : il peut avancer avec Tab, et reculer
avec Maj Tab. |
Aider l’opérateur par des consignes et des conseils |
Rédiger des consignes
claires dans l’onglet Message de saisie de la commande Données,
Validation. |
Aider l’opérateur par des messages clairs en cas d’erreur de
saisie (non respect des règles définies) |
Rédiger des messages
clairs dans l’onglet Alerte d’erreur de la commande Données, Validation. |
Montrer à l’écran les sauts de pages (pointillés) |
Alt O, O, A, Alt
S : Outils, Options, onglet Affichage,
cocher Sauts de page. |
Écarter les chiffres du bord droit (sans les centrer !) |
Donner un format personnalisé
comportant quelques espaces (entre guillemets)
à droite de la valeur de la cellule : Alt T, C, N : Format, Cellule,
onglet Nombre, catégorie Personnalisé… Exemple : # ##0,00" " |
Déplacer le message (jaune) s’il gêne la visibilité |
Glisser simplement sur
le message avec la souris. |
Ajouter un outil à droite du menu général |
Affichage, Barres
d’outils, Personnaliser, onglet Commandes, puis faire glisser l’outil
voulu juste à droite du dernier mot du menu : il faut que le pointeur
prenne une forme de i majuscule noir au moment de relâcher la souris,
sinon l’outil n’est pas ajouté. |
Faciliter la déprotection / reprotection d’une feuille protégée
à mettre au point |
Ajouter l’outil Protéger
la feuille à une Barre d’outils : c’est une
bascule On/Off qui indique en plus de façon assez visuelle dans quel état de
protection on se trouve en ce moment. Et si on change la
lettre rapide de l’outil, (clic droit sur l’outil, pendant que la boîte
Personnaliser est ouverte), son texte ne s’actualise plus, il ne signale
plus si la feuille est protégé ou pas, en ce moment. C’est donc la lettre de
la commande Fichier qu’il faut changer, ce qui bouleverse de nombreuses
habitudes. |
Prévoir une forme conditionnelle (bordure, par exemple) |
Alt T, D : Format,
Mise en forme conditionnelle… |
Masquer tous les zéros d’une feuille |
Alt O, O, A… :
Outils, Options, onglet Affichage, décocher Valeurs
zéro. |
Protéger les formules d’un classeur |
Alt O, E, F :
Outils, Protection, Protéger la feuille (pas de mot de passe) Entrée. |
Masquer le quadrillage gris des cellules à l’écran |
Alt O, O, A… :
Outils, Options, onglet Affichage, décocher Quadrillage. |
Finaliser un classeur avant de le livrer à un utilisateur (check
list) |
·
Vérifier un à un tous les contrôles de
saisie : saisir des valeurs normales, des
valeurs limites, des valeurs interdites, contrôler la lisibilité
des consignes et des messages d’erreur. ·
Vérifier méthodiquement tous les calculs
avec une calculette. Vérifier avec des grandes valeurs que les colonnes ont
une largeur suffisante sans afficher de dièses ###########. ·
Vérifier la mise en page en Aperçu :
pas de page blanche pour rien, blocs bien centrés, sans déséquilibre
disgracieux, réfléchir à toutes les mentions utiles en Pied de page ou en
En-tête, fixer éventuellement les titres courants (lignes ou colonnes
répétées sur toutes les pages). ·
Protéger la feuille et contrôler tout le
chemin de saisie avec Tab jusqu’à revenir au début
et contrôler que l’écran se recadre bien sur la première cellule. Le cas
échéant, déverrouiller la cellule A1 pour forcer ce recadrage du début de
tableau dans l’écran en fin de chemin de saisie. ·
Vider le chemin de saisie,
pour éviter à l’opérateur de le faire. ·
Placer le pointeur sur la première
cellule juste avant le tout dernier
enregistrement avec Ctrl S. |
Faire une copie de sauvegarde du travail sur disquette |
Ne surtout pas effectuer cette
copie à partir d’Excel ! |
Je vérifie que j’ai bien progressé…
Je sais… CORRIGÉ |
Commandes et
procédures |
Créer
un brouillon d’objectif avant de créer un tableau |
Regrouper les infos en ZONES. Repérer les infos à SAISIR.
Concevoir les TABLES de référence… |
Choisir la première
colonne d’une table
|
La
première colonne de la table doit être de même nature que l’info connue
(qui sera cherchée dans cette colonne). C’est une colonne dans laquelle il
faut absolument interdire les doublons. |
Expliquer
l’intérêt d’un cas zéro dans
toutes les tables |
Faute
de cas zéro dans la table de référence, le cas de la cellule à saisir vide
(valant donc zéro) pose problème à la fonction RechercheV qui, ne trouvant pas ce qu’elle cherche (zéro),
affiche un message d’erreur #NA (info non accessible). C’est cette fastidieuse programmation de tests devant toutes
les fonctions recherchev dont nous sommes dispensés avec l’astuce du
cas zéro dans les tables : avec cette ligne zéro, quand la cellule à
saisir est vide, RechercheV cherche
zéro, trouve zéro et affiche ce qu’on a prévu dans la table (un espace ou un
zéro)… sans rien programmer. |
Ordonner les seuils, et
les taux correspondants,
dans une table à utiliser avec Recherchev et VRAI |
La
fonction RechercheV avec Vrai
accepte de ne pas trouver exactement ce qu’elle cherche. Dans ce cas, elle
s’arrête juste avant de dépasser ce qu’elle cherche. Mais cela ne
fonctionne que si la table est en ordre croissant sur la
première colonne, celle où on cherche. |
Nommer une table en anticipant les
futures évolutions |
Donner
un nom à une zone plus vaste que le maximum prévisible. Souvent, on
nomme des colonnes entières pour à la fois aller vite et ne jamais
être limité. Parfois, il est plus
astucieux de donner un nom dynamique, calculé, dont la plage s’adapte
toute seule aux cellules pleines. |
Relire (contrôler) un nom
donné |
Dérouler
la liste de |
Contrôler la saisie : imposer une
saisie conforme à une liste de possibles |
Alt D, V : Données, Validation,
onglet Options, Autoriser Liste… |
Donner
un nom à des cellules d’un classeur
externe |
Ouvrir
le classeur externe
pour qu’il soit présent en arrière-plan (disponible avec Fenêtre ou Ctrl F6). |
Aider l’opérateur par des
consignes et des conseils |
Alt D, V : Données, Validation,
onglet Message de saisie… |
Aider
l’opérateur par des messages clairs en
cas d’erreur de saisie (non respect des règles définies) |
Alt D, V : Données, Validation,
onglet Alerte d’erreur… |
Aider l’opérateur à
repérer visuellement les cellules à saisir (pour anticiper les mouvements du pointeur) |
Donner une couleur de fond identique,
plutôt pâle et discrète, toujours la même d’un classeur à l’autre, à toutes
les cellules à saisir. |
Aider l’opérateur à se
déplacer d’une
cellule de saisie à l’autre |
Déverrouiller
(avec Format, Cellule, onglet Protection) les
cellules à saisir et protéger les autres (avec Alt O, E, F : Outils,
Protection, Protéger la feuille (pas de mot de passe) Entrée). Ensuite, l’opérateur peut filer directement
vers la prochaine cellule à saisir : il peut avancer avec Tab, et reculer
avec Maj Tab. |
Récupérer une info dans
une table à partir d’un identifiant connu (sans accepter les erreurs de saisie) |
Construire
une table dont la première colonne soit de même nature que l’info qui sera
connue
(souvent saisie), colonne d’identifiants sans doublon et possiblement
en désordre,
en prévoyant un cas zéro. Remplir
les colonnes voisines en correspondance avec l’identifiant de la première colonne,
simplement. Nommer
cette table entière,
sans se limiter à l’exemple actuellement saisi dans la table : prendre
des colonnes entières pour anticiper les futurs ajouts à la table. Prévoir
une zone de saisie assistée et contrôlée pour la valeur qui servira à la fonction
rechercheV. Saisir
enfin la formule de recherche en respectant cette syntaxe : |
Récupérer un taux dans
une table en fonction d’un seuil atteint (saisie approximative admise) |
Construire
une table dont la première colonne soit de même nature que l’info qui sera
connue
(souvent saisie), colonne de seuils en ordre croissant, en prévoyant un cas
zéro. Remplir
les valeurs des colonnes voisines en vous disant intérieurement les mots
suivants : Nommer
cette table entière,
sans devoir, cette fois, anticiper les ajouts à la table (improbables). Prévoir
une zone de saisie assistée et contrôlée pour la valeur qui servira à la fonction
rechercheV. Saisir
enfin la formule de recherche en respectant cette syntaxe : |
Étiquette
dynamique : accompagner une valeur avec des textes constants dans une même cellule,
sans altérer son contenu |
Donner un format personnalisé comportant
des textes constants entre guillemets
de part et d’autre de la valeur de la cellule : Alt T, C, N :
Format, Cellule, onglet Nombre, catégorie Personnalisé…
|
Montrer
à l’écran les sauts de pages
(pointillés) |
Alt O, O, A, Alt S :
Outils, Options, onglet Affichage, cocher Sauts
de page. |
Écarter les chiffres du
bord droit des cellules (sans les centrer !) |
Donner un format
personnalisé comportant quelques espaces (entre guillemets)
à droite de la valeur de la cellule : Alt T, C, N : Format, Cellule,
onglet Nombre, catégorie Personnalisé… Exemple : # ##0,00" " |
Déplacer le message de
consignes (jaune)
s’il gêne la visibilité |
Glisser simplement sur
le message avec la souris. |
Dérouler une liste avec
le clavier /
Replier cette liste sur elle-même |
Alt
+ Flèche Bas |
Dans
une formule, figer une adresse de cellule
pour qu’elle ne soit pas transformée pendant une recopie |
Mettre un dollar
$ devant le caractère à figer. |
Prévoir
une forme conditionnelle |
Alt
T, D : Format, Mise en forme conditionnelle… |
Afficher dans un cadre dynamique l’image fidèle d’un lot de cellules éloignées |
Sélectionner les cellules éloignées, faire un clic sur l’outil
appareil photo, aller là où doit figurer l’image fidèle, (même si c’est
un autre classeur) et cliquer pour poser la photo. Ensuite, l’objet graphique qu’est la photo
peut être dimensionné en glissant sur une poignée, ou déplacé
en glissant sur l’objet lui-même. L’image est dynamique, c'est-à-dire
qu’elle représente toujours l’état actuel des cellules photographiées. |
Créer un lien entre deux zones qui permette de se déplacer
directement d’un point à l’autre par simple double clic |
Poser une photo de la destination (voir ci-dessus) à
chaque point où on en a besoin. Un simple double clic sur la photo sélectionne
aussitôt l’original de la photo, même s’il est dans un
classeur fermé au moment du double clic ! Indispensable ! |
Masquer tous les zéros d’une feuille |
Alt O, O, A, Alt Z :
Outils, Options, onglet Affichage, décocher
Valeurs zéro. |
Protéger les formules d’un classeur |
Alt O, E, F :
Outils, Protection, Protéger la feuille (pas de mot de passe) Entrée.
|
Organiser l’écran
pour protéger / déprotéger en un seul clic |
Une
fois pour toutes, ajouter au menu l’outil Protéger la feuille : Affichage,
Barres d’outils, Personnaliser… |
Masquer ou afficher le
quadrillage gris clair
à l’écran |
Alt O, O, A, Alt Q:
Outils, Options, onglet Affichage, décocher Quadrillage. |
Finaliser un tableau avant de le livrer à un
utilisateur |
·
Vérifier un à un tous les contrôles de
saisie : saisir des valeurs normales, des
valeurs limites, des valeurs interdites, contrôler la lisibilité
des consignes et des messages d’erreur. ·
Vérifier méthodiquement tous les calculs
avec une calculette. Vérifier avec des grandes valeurs que les colonnes ont
une largeur suffisante sans afficher de dièses ###########. ·
Vérifier la mise en page en Aperçu :
pas de page blanche pour rien, blocs bien centrés, sans déséquilibre
disgracieux, réfléchir à toutes les mentions utiles en Pied de page ou en
En-tête, fixer éventuellement les titres courants (lignes ou colonnes
répétées sur toutes les pages). ·
Protéger la feuille et contrôler tout le
chemin de saisie avec Tab jusqu’à revenir au début
et contrôler que l’écran se recadre bien sur la première cellule. Le cas
échéant, déverrouiller la cellule A1 pour forcer ce recadrage du début de
tableau dans l’écran en fin de chemin de saisie. ·
Vider le chemin de saisie,
pour éviter à l’opérateur de le faire. ·
Placer le pointeur sur la première
cellule juste avant le tout dernier
enregistrement avec Ctrl S. |
Enregistrer une suite de
commandes simples à exécuter automatiquement à l’ouverture du classeur |
1.
Bien régler le classeur et l’affichage pour que l’enregistrement
se déroule bien (planter le décor qui va permettre de bien jouer). 2.
Lancer l’enregistrement avec Outils, Macros,
Nouvelle macro (nom de macro sans espace) Entrée. 3.
Exécuter naturellement tout ce qui doit être enregistré sous les yeux
de la caméra. 4.
Éventuellement, basculer en cours d’enregistrement d’un adressage relatif
à un adressage absolu, ou l’inverse, en cliquant sur l’outil 5.
Arrêter l’enregistrement avec Outils, Macro,
arrêter l’enregistrement ou cliquer sur l’outil |
Désactiver le message
automatique invitant à rétablir les liaisons, à l’ouverture d’un
classeur lié à un autre |
Alt O, O, M, Alt S :
Outils, Options, onglet Modification, décocher
Confirmation de la mise à jour automatique des liens. |
Je vérifie que
j’ai bien progressé…
Je sais… CORRIGÉ |
Commandes et procédures |
Incliner un texte ou un chiffre |
Format,
Cellule, onglet
Alignement, champ Orientation, glisser sur le petit losange
au bout du trait, ou bien taper directement un angle dans le champ Degrés. |
Régler toutes les largeurs d’un coup |
Sélectionner
toute la feuille avec Ctrl A, puis glisser à droite du numéro de n’importe
quelle colonne. |
Saisir une date |
Taper le jour, slash
/, le mois et Entrée. Pas besoin de taper
l’année si c’est l’année en cours. |
Calculer une date à partir d’une autre |
Seules sont
possibles : La
soustraction de deux dates
qui donne un délai, en jours. L’addition
d’une date et d’un délai
qui donne une autre date. La
soustraction d’un délai d’une date
qui donne une autre date. |
Créer
rapidement un petit compteur en
partant de 1 |
Ctrl
glisser sur la poignée de recopie. |
Transformer un chiffre en
couleur |
Utiliser un format
conditionnel ton sur ton : couleur de police identique à la couleur
du motif (fond). |
Saisir d’un coup de nombreuses cellules identiques |
Sélectionner
toutes les cellules à remplir, taper la valeur, et valider avec Ctrl
Entrée. |
Compter des cellules colorées |
D’abord, il
faut colorer les cellules par saisie de codes (1, 2, 3), et mise en
couleur de ces codes par formats conditionnels. Ensuite, il
suffit de compter les codes avec NB.SI. |
Intégrer une valeur
variable dans un texte constant (pour fabriquer des titres
dynamiques). |
Concaténer (assembler) avec l’esperluette &. |
Assembler à volonté les
caractères composant une date en récupérant la date affichée dans une cellule |
Utiliser les
fonctions JOUR(celluledate) ou MOIS(celluledate) ou ANNEE(celluledate). |
Interdire la saisie de
valeurs autres
que les valeurs prévues |
Alt D, V : Données, Validation,
Autoriser… |
Afficher
des messages d’aide et des messages d’erreurs |
Alt D, V : Données, Validation,
onglets Message de saisie et Alerte d’erreur… |
Figer des lignes et des
colonnes à l’écran
pendant les défilements dans un grand tableau |
Sélectionner la première
cellule placée en dehors des volets (sous le volet haut et à droite du
volet gauche), puis lancer Fenêtre,
Figer les volets. |
Imprimer certaines lignes
et colonnes en tête de toutes les pages papier d’un grand tableau |
Alt F, P,
F : Fichier, Mise en page, onglet Feuille, remplir les champs Lignes à
répéter en haut et Colonne à répéter à gauche. |
Prévoir
une mise en forme variable selon le
contenu |
Alt T, D : Format,
Mise en forme conditionnelle… |
Prévoir
l’affichage d’un texte variable selon
le contenu de la cellule. |
Donner un format
personnalisé comportant des conditions entre crochets. On peut aussi
prévoir des couleurs dans ce format personnalisé avec des couleurs, elles
aussi entre crochets. Exemple : |
Ajouter un outil à une
barre d'outils |
Format,
Cellule, onglet Nombre, catégorie Personnalisé… prévoir des conditions
entre crochets,
des textes constants entre guillemets et éventuellement des couleurs
entre crochets.
Attention, les conditions exprimées entre points-virgules. Exemple : |
Masquer les zéros
indésirables sans
supprimer mes précieuses formules : masquer d'un geste tous les zéros de la feuille en cours |
Alt O, O, A… :
Outils, Options, onglet Affichage, décocher Valeurs
zéro. |
Masquer les zéros
indésirables sans
supprimer mes précieuses formules : masquer seulement certains zéros, et laisser affichés les autres zéros de
la feuille |
Deux
possibilités : ·
Soit on rend la formule conditionnelle et c’est le
calcul lui-même qui rend "" au lieu de 0 quand c’est nécessaire. ·
Soit c’est par le format qu’on masque certains
zéros. En effet, la commande Format, Cellule, onglet Nombre,
catégorie Personnalisé permet de prévoir non pas un format mais quatre
formats, séparés par des points-virgules : |
Nommer une plage de cellules |
Sélectionner
la ou les cellules à nommer, puis (au choix) : ·
Cliquer sur ·
Alt I, N, D (Insertion, Nom Définir)
taper le nom et valider avec Entrée. Dans
les deux cas, il faut absolument relire les noms que l’on vient
d’attribuer : dérouler la liste de |
Sélectionner rapidement
une plage nommée |
Dérouler
la liste de |
Programmer un
"flag" (drapeau visuel qui signale
automatiquement un problème, un cas à surveiller) |
Si
le problème est simple, comme un dépassement de seuil ou de délai dans une
colonne, il suffit de prévoir un format conditionnel sur toute la colonne,
de façon à ce que, lorsque la cellule courante à une valeur problématique,
elle devienne très visible, fond rouge et caractères gras blancs, par
exemple. Si
le problème est plus compliqué, il est astucieux de prévoir une colonne
test qui se charge, avec une ou plusieurs fonctions SI, de détecter le problème.
Cette colonne test peut très bien être masquée pour ne pas gêner
l’affichage et l’impression. Ensuite, un format conditionnel sur cette colonne
test pourra changer l’apparence d’une cellule de colonne normale
en la rendant très visible. |
Lire l’ascenseur des
fenêtres de Windows |
Dans toutes les fenêtres de Windows, la cage de l’ascenseur
représente la longueur totale du document en cours. La taille du rectangle gris de l’ascenseur représente la proportion
que représente la fenêtre en cours dans le document entier : si
l’ascenseur est tout petit, c’est que le document est très long, et
inversement. La position de l’ascenseur, elle, représente la position de la
fenêtre dans le document entier : au début, au milieu, à la fin… |
Je vérifie que
j’ai bien progressé…
Je sais…
CORRIGÉ |
Commandes et procédures |
Organiser les
données en une liste unique, et choisir suffisamment de colonnes discriminantes
|
Il faut éviter d’exploser les données en feuilles
séparées, car cela interdit ensuite tout regroupement sur d’autres critères.
Il faut essayer de garder toutes les données ensemble, et de rendre le
découpage toujours possible grâce à des colonnes discriminantes, c’est-à-dire
des colonnes qui permettent de discriminer certaines lignes dans l’ensemble,
de les traiter à part. |
Expliquer l’intérêt
de placer les totaux en tête de liste |
Il est beaucoup plus commode d’accéder tout de
suite à la synthèse, en haut des documents, avant de consulter
(éventuellement) le détail plus bas. Surtout qu’en plaçant les sommes dans les volets
figés, on garde les calculs sous les yeux pendant qu’on saisit ou
qu’on corrige les données de base, parfois très éloignées. De surcroît, avec les totaux en haut des
colonnes, on évite tous les désagréments lors de chaque ajout de lignes en
bas : pas besoin de repousser les calculs, pas besoin de modifier les
formules… |
Choisir un zoom
écran adapté au tableau en cours |
Trois possibilités : ·
Cliquer une fois sur le chiffre dans
l’outil ·
On peut aussi utiliser la commande Alt
A, Z : Affichage, Zoom… ·
On peut enfin sélectionner les cellules
à cadrer, |
Choisir une bonne
définition d’écran et expliquer pourquoi c’est très utile |
Plus on donne de points à un écran pour dessiner
les caractères, plus il est précis : en haute résolution, les
caractères sont nets et lisibles, on se fatigue moins, même si les caractères
sont petits. Pour changer la résolution de l’écran (on dit
aussi sa définition), utiliser Démarrer, Paramètres, Panneau de
configuration, Affichage, onglet Paramètres.. ou bien clic droit Propriétés, sur le fond
d'écran, onglet Paramètres... (c’est beaucoup plus rapide). |
Fixer l’affichage
des titres à l’écran pendant les déplacements dans les grands tableaux |
Sélectionner la première
cellule placée en dehors des volets (sous le volet haut et à droite du
volet gauche), puis lancer Fenêtre,
Figer les volets. |
Colorer une ligne
sur deux, zébrer les lignes, par calcul et expliquer l’intérêt de
cette technique par rapport à l’outil « pot de peinture »… |
Ctrl A pour sélectionner toute la feuille, puis
Format, Mise en forme conditionnelle, définir ensuite la
formule =MOD(LIGNE();2)=0 et choisir un motif très pâle pour ne
pas gêner la lisibilité, et
valider. Cliquez sur une cellule
quelconque pour bien voir le résultat. Conseil : faire ensuite disparaître le
quadrillage gris clair : Outils, Options, onglet Affichage,
décochez Quadrillage. La formule utilise la fonction modulo, MOD(dividende;diviseur),
qui rend le reste de la division des deux arguments, et la fonction LIGNE()
qui rend le numéro de la ligne en cours. Cette astucieuse formule détecte les lignes paires quand le
reste de la division par 2 donne 0. Et dans ce cas, le
format conditionnel donne une couleur de fond (un motif). Donc, ensuite, même si vous insérez
ou supprimez une ligne, vous conservez l’alternance paire - impaire / couleur
- pas de couleur. |
Donner à Excel
le moyen de reconnaître la première ligne d’une liste, pour éviter que cette
ligne ne soit triée comme les autres |
Donner une forme particulière à cette
ligne : gras, fond jaune… De plus, il faut que la première ligne soit la
ligne 1 ou bien soit surmontée d’une ligne totalement vide. Il est conseillé de créer cette ligne vide en tête, et de la
masquer pour être sûr qu’elle reste vide. |
Supprimer une forme
conditionnelle |
Format, Mise en forme conditionnelle,
bouton Supprimer, cocher les conditions à retirer. |
Isoler les x
caractères à gauche ou à droite d’une cellule |
Utiliser la fonction GAUCHE(cellule ; nb
de car à extraire) |
Extraire x
caractères au beau milieu (à la position y) d’une cellule |
Utiliser la fonction STXT ( texte ; numdépart
; nbcar ) |
Recomposer une date
à partir de ses composants (jour, mois et année) |
=DATE(ANNEE(celluledate) ;
MOIS(celluledate) ; JOUR(celluledate) ) |
Distinguer le
"vide texte" du "vide numérique" au moment de tester le
fait qu’une cellule est vide ou pas |
Le « vide numérique » est le chiffre
0, zéro, alors que le « vide texte » est la chaîne de
caractères vide, qui s’écrit "", deux guillemets qui se
touchent. Si on teste une cellule qui contient un
texte en la comparant à un chiffre, on
obtient une erreur. Il faut donc faire attention à la nature des cellules
testées avec SI. |
Changer de cellule
active au sein d’une grande sélection, sans perdre la sélection |
Entrée et Maj Entrée
pour circuler de haut en bas. Remarque : Tab et Maj Tab permettent
de circuler de gauche à droite. En arrivant à la fin, on revient au début, et
inversement : il faut imaginer que la cellule active suit un circuit fermé, sans fin. |
Aller
instantanément à la fin de la zone sélectionnée dont l’extrémité opposée est
très lointaine |
Taper Maj Entrée pour filer d’un geste à
la fin de la sélection. Taper ensuite Entrée pour revenir d’un
geste au début. |
Coller la seule
valeur d’un groupe de cellules modèles |
Alt E, G, bas bas Entrée :
Edition, Collage spécial, Valeurs. |
Coller le seul
format d’un groupe de cellules modèles |
Alt E, G… :
Edition, Collage spécial, Formats. |
Expliquer le piège
que tend Excel lors de chaque TRI |
Pour nous offrir toujours plus de
fonctionnalités, les programmeurs d’Excel ont prévu qu’une sélection de
plusieurs cellules avant de lancer un tri signifie qu’on veut ne trier que la
sélection. En fait, c’est très rarement le cas et l’opérateur n’a simplement
pas prêté attention à la sélection. Par contre, le plus souvent, trier la seule
sélection (sans trier le reste autour) détruit le tableau, purement et
simplement. Si on s’en rend compte assez tôt et qu’on pense à
annuler le tri, on s’en sort bien. Mais si on réalise trop tard l’étendue des
dégâts, on peut perdre des centaines d’heures de travail ! |
Trier une liste sur
le critère d’une colonne |
Cliquer sur UNE cellule quelconque (une
seule !) dans la colonne critère, |
Éviter que le tri
n’emporte une ligne de calculs placée juste au-dessus de la ligne des titres
de colonnes |
Donner à cette ligne une forme particulière,
gras par exemple, et s’assurer qu’il y a une ligne vide (ou pas de ligne
du tout) au-dessus de la ligne des titres. |
Ajouter les deux précieux outils liés aux filtres à
droite du menu et expliquer leur mode d’emploi |
Les deux outils fabuleux sont et .
Ils s’utilisent avec une déconcertante simplicité, et une efficacité
spectaculaire : un clic sur une cellule modèle et un clic sur
l’entonnoir, c’est filtré… instantanément. On peut ensuite ajouter d’autres
filtres de la même façon. Et pour récupérer une liste complète, Afficher
tout, facile. |
Effectuer des
opérations (somme, moyenne, min, max, nb, nbval) sur les seules lignes affichées
avec un filtre |
Une seule fonction : =SOUS.TOTAL(CodeOpération;Plage
) |
Sélectionner
65 000 cellules (sans les premières cellules de la colonne) en 1 seconde |
Ctrl Maj flèche bas |
Filtrer entre deux
dates avec une saisie ergonomique |
Il faut combiner astucieusement les
critères de filtres en créant une zone de saisie de deux dates, une colonne
test sur ces deux dates, et un filtre sur cette colonne test. La formule de la colonne test
est : Ainsi, la colonne test vaut 1 si
la ligne est comprise entre les dates saisie. Il ne reste donc qu’à filtrer
sur cette colonne pour ne montrer que les 1.
|
Utiliser SOMME.SI
et NB.SI avec plusieurs contraintes alors qu’elles sont prévues pour une
seule |
Utiliser une colonne test (comme
ci-dessus) et exprimer la condition de SOMME.SI ou NB.SI sur cette colonne
test. |
Effectuer des
calculs conditionnels sur une liste, indépendamment des filtres éventuellement
en cours |
Utiliser NB.SI et SOMME.SI. |
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.