GUIDE - TRAITEMENTS BUREAUTIQUES MANUELS

Traitements bureautiques manuels sous Windows et Office - rapidité, trucs et astuces

Ce guide s'adresse aux personnes utilisant déjà les outils bureautiques Office standards et qui souhaitent savoir comment réaliser certains traitements bureautiques le plus efficacement possible.

Il est centré surtout sur les commandes de base, leurs raccourcis clavier en version française, la manipulation des données ainsi que les problèmes classiquement rencontrés et leurs solutions.

Les autres fonctionnalités ne sont pas abordées, notamment les notions Office pour débutant, les graphiques, les styles (mises en forme conditionnelles, styles de tableau, styles de cellules), les formules, les révisions (vérifications, commentaires, modifications), les macros VBA, le publipostage, etc.

La première partie du document (cf. chapitre I «Utilisation du clavier plutôt que de la sourisUtilisation du clavier plutôt que de la souris») est consacrée au clavier et à la position des doigts. Cela correspond aux fondamentaux qui vous permettront d'acquérir une rapidité d'exécution.

La seconde partie (chapitre II « Raccourcis clavierRaccourcis clavier ») traite des raccourcis clavier Windows, Word et Excel.

La troisième partie (chapitres IIIWord - Actions, astuces, problèmes et solutions et IVExcel - problèmes, solutions et manipulation de données ) est dédiée aux actions, astuces, problèmes et solutions sous Word et Excel, mais aussi aux différents moyens de manipuler des tableaux de données, en les croisant, triant, synthétisant…

Enfin, une synthèse des raccourcis est présentée au chapitre VAnnexe - Résumé des principaux raccourcis clavier .

Pour toute remarque ou suggestion, vous pouvez réagir sur l'espace de discussion créé sur le forum. 3 commentaires Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Utilisation du clavier plutôt que de la souris

I-A. Introduction

L'utilisation du clavier permet d'accroître grandement la rapidité d'exécution des tâches

Cette utilisation du clavier en remplacement de la souris est possible dans 99 % des cas.

I-B. Automatisation de la frappe au clavier

Pour que cette automatisation puisse se faire, le plus important est que chaque doigt s'habitue à taper les mêmes touches. Ainsi, le cerveau mémorise automatiquement la frappe au clavier et celle-ci devient quasiment automatique, tout comme l'est, par exemple, le laçage d'une chaussure.

Dans ce but, chaque caractère doit toujours être tapé avec le/les même(s) doigt(s). Pour cela, il est important que les mains reviennent régulièrement à la même position de départ. Pour cette raison, les touches F et J du clavier possèdent un léger relief. Elles sont des repères pour positionner les index des deux mains :

  • index de la main gauche sur le F ;
  • index de la main droite sur le J.

Dans la pratique, plusieurs associations entre touches du clavier et doigts utilisés sont possibles. Le plus important est de toujours utiliser la même correspondance, c'est-à-dire les mêmes doigts par touche, pour que l'automatisation s'effectue. Et celle-ci s'effectue très vite ; rassurez-vous.

La méthode la plus simple pour arriver à cette automatisation est la suivante :

  • en replaçant ses doigts : avant de taper chaque touche du clavier, ne regardez pas cette touche. Placez plutôt vos index aux emplacements adéquats, puis appuyez sur la touche souhaitée grâce au doigt le plus proche. Il faut donc replacer ses doigts à chaque fois à la position initiale. Cela ralentit la frappe dans les premiers temps, mais uniquement durant la période d'automatisation. Ensuite, le gain est très net ;
  • sans replacer ses doigts :  si vous n'avez qu'une touche à taper, vous pouvez aussi ne pas replacer vos doigts. Si vous tapez la touche avec le bon doigt, cela est suffisant. Par exemple, si l'on veut juste taper « L » au clavier, il est très important de toujours le taper avec l'annulaire.

Enfin, sachez qu'il y a plusieurs positions de base des doigts :

  • la position générale pour les touches du clavier central ;
  • une position décalée pour les touches du clavier numérique ;
  • une position éventuelle pour les quatre flèches Image non disponible

I-C. Mon état des lieux de l'utilisation du clavier

Voici mon état des lieux personnel de l'utilisation du clavier :


Pour les touches du clavier central (sur un clavier AZERTY), la position générale des doigts est celle-ci :

Doigt

Auriculaire

Annulaire

Majeur

Index

Pouce

Main gauche :

Q

Z

D

F

espace

Main droite :

J

K

L

M

espace

Ensuite, pour frapper chaque caractère, la correspondance ci-dessous est utilisée.

Doigt de la main gauche et touches correspondantes :

1 Auriculaire

2 Annulaire

3 Majeur

4 Index

5 Pouce

Aa Qq Ww
Esc F1 1& Tab
caps-lock Maj <>
Ctrl

Zz Ss Xx
F2 2 é ~

Ee Dd Xx
F3 3" «»

Rr Tt Ff Gg Cc VvBb
F4 4' { 5([ 6-|

Windows Alt

Doigt de la main droite et touches correspondantes :

6 Pouce D

7 Index D

8 Majeur D

9 Annulaire D

10 Auriculaire D

Espace Alt Gr
clic droit = bouton droit (de la souris)
=Image non disponible ou Image non disponible

Yy Uu Hh Jj Nn
7è 8_ ?,
@ (avec Alt Gr)

Ii Kk
9ç ;.
] (avec Alt Gr)

Oo Ll
0à /:
} (avec Alt Gr)

Pp Mm °) § !
+=¨^%ù Maj
Delete £$ µ*
Enter

Remarques :

  • Un 2e doigt de l'autre main est parfois utilisé pour appuyer sur 'Maj' ou 'Alt Gr'
  • Cas particulier du Maj+Ctrl+…: le pouce gauche est utilisé pour appuyer sur ctrl.

Pour les touches du clavier numérique, la position initiale des doigts est la suivante :

Doigt

Index

Majeur

Annulaire

Main droite

4

5

6

En terme de clavier, cela donne ceci :

Image non disponible

Avec la légende ci-dessous :

Image non disponible

I-D. Votre état des lieux de l'utilisation du clavier

Image non disponible

Exercice 1 : à vous maintenant de trouver la voie qui vous convient le mieux en fonction de vos anciennes habitudes.


Vous pouvez remplir votre propre tableau correspondant à votre utilisation actuelle des touches puis voir éventuellement comment vous pouvez l'améliorer petit à petit par la suite.

Position de départ des différents doigts pour les touches du clavier central :

Doigt

Auriculaire

Annulaire

Majeur

Index

Pouce

Main gauche :

         

Main droite :

         

Doigt de la main gauche et touches correspondantes :

1 Auriculaire

2 Annulaire

3 Majeur

4 Index

5 Pouce

         

Doigt de la main droite et touches correspondantes :

6 Pouce D

7 Index D

8 Majeur D

9 Annulaire D

10 Auriculaire D

         

Position initiale des différents doigts pour les touches du clavier numérique

6 Pouce D

7 Index D

8 Majeur D

9 Annulaire D

10 Auriculaire D

         

En termes de clavier, à vous d'identifier les doigts que vous utilisez pour chaque touche :

Image non disponible

II. Raccourcis clavier

Tous les raccourcis présentés ici concernent la version française d'Office.

Si vous ne connaissez que très peu de raccourcis, vous pouvez apprendre en premier les raccourcis en surbrillance bleue. Ce sont des quick wins.

Les raccourcis en gras sont incontournables et à utiliser sans modération.

Les raccourcis en gris sont beaucoup moins utiles.


Il y a donc dans ce document quatre niveaux de raccourcis :

Niveau

Symbolisation

Signification

1

xxx

Les raccourcis essentiels à connaitre. Peu nombreux (donc faciles à apprendre) et très utiles en gain de productivité. (d'où le terme de quick win)

2

xxx

Les raccourcis quasi incontournables et à utiliser sans modération

3

xxx

Les raccourcis à connaitre, et à maitriser si on est amené à utiliser fréquemment l'action correspondante

4

xxx

Les raccourcis supplémentaires. Rarement utilisés. À connaitre si on est amené à utiliser fréquemment l'action correspondante

II-A. Raccourcis généraux

Les raccourcis ci-dessous fonctionnent pour des applications Microsoft et ce sont plus ou moins répandus pour d'autres applications.

Raccourcis

Thème - Action

Ctrl+Flèche

  • Édition de texte
  • Sous Excel

Déplacement - rapidité : aller à la limite d'une zone (permet d'éviter la répétition)
Ctrl+Image non disponibleImage non disponible(limite de mot)      Ctrl+Image non disponibleImage non disponible(limite de paragraphe)
Ctrl+Image non disponibleImage non disponibleImage non disponibleImage non disponible(limite de zone Excel)

Maj+Flèche

Déplacement/Sélection - sélection : sélection multiple de l'objet de départ à l'objet d'arrivée

Maj+Ctrl+Flèche

Déplacement/Sélection - rapidité+sélection : sélectionner en se déplaçant jusqu'à la limite d'une zone
Remarque : pour cette combinaison, j'utilise l'auriculaire gauche pour Maj et le pouce gauche pour Ctrl

Touche Début / Fin

Déplacement - Début/Fin de ligne

Ctrl+Touche Début / Fin

Déplacement - Début/Fin de document

Ctrl + clic souris

Sélection - Sélection d'objets (fichiers, textes, cellules…) non adjacents

F1

Divers - Aide applicative

F2

Divers - Entrer en mode édition dans zone de texte (Ex. : Excel)

Escape, F4, Ctrl+z, Ctrl+y

Divers - Gestion des actions (sortir de l'action, la répéter, l'annuler, la dé-annuler)

Touche clic droitImage non disponible ou Image non disponible

Divers - Touche menu contextuel

Ctrl+x, Ctrl+c, Ctrl+v

Édition - Coupé, Copié, Collé

Ctrl+g, Ctrl+i, Ctrl+u

Édition - Gras, Italique, Souligné (de la sélection)

Ctrl+a, Ctrl+f, Ctrl+h

Édition - Sélection totale (avec un comportement spécifique sous Excel), Recherche, Remplace

Ctrl+s, Ctrl+p

Fichier - Sauvegarde, Impression

Ctrl+w (ou Ctrl+F4)

Fichier - Ferme le document en cours

Drag & Drop

Édition - Déplace un objet (fichier, répertoire, texte…) (Remarque : comportement spécifique pour Windows)

Ctrl + Drag & Drop

Édition - Déplace un objet (fichier, répertoire, texte…) en le copiant

Alt + Print Screen

Fenêtre -Ne copie que la fenêtre courante dans le presse-papier

Ctrl+n, Ctrl+o

Fichier - Nouveau document, Ouvre un document

Ctrl+molette souris

Affichage - Zoom avant / arrière

Alt + Espace

Fenêtre -Affiche le menu système de la fenêtre

Image non disponible

Exercice : appliquez chacun des raccourcis dans plusieurs applications (Explorateur, Word, Excel)

II-B. Raccourcis généraux sous office

Raccourcis

Thème - Action

Alt (Office 2007…)

Divers - Affiche les raccourcis accessibles

F12 = Alt+F2

Fichier - Affiche la boite de dialogue « Enregistrer (sous) »

F7

Divers - Affiche la boite de dialogue (Grammaire et) Orthographe

Tab

Déplacement - Passe d'un objet au suivant après sélection champ ou un objet graphique

Ctrl+F1 (Office 2007,…)

Affichage - Passe alternativement de« Affiche le ruban » à « Masque le ruban »

Ctrl+F10

Affichage - Passe alternativement de réduit à agrandit la fenêtre

Alt+Espace

Divers - Affiche le menu Contrôle pour la fenêtre Microsoft Office Excel.

Alt+F8

Divers - Exécuter une macro VBA

Alt+F11

Divers - Accès aux fenêtres VBA

Image non disponible

Exercice : appliquez chacun des raccourcis dans plusieurs Word et Excel.

II-C. Raccourcis dans une fenêtre pop-up

Une fenêtre pop-up est une fenêtre qui s'ouvre au sein d'une application pour l'exécution d'une tâche particulière. Exemple fenêtre « remplacer » (Ctrl+h).

Raccourcis

Thème - Action

Tab

Pop-up - Passe d'un champ au champ suivant dans une fenêtre pop-up

Maj+Tab

Pop-up - Passe d'un champ au champ précédent dans une fenêtre pop-up

Alt + Lettre soulignée

Pop-up - Accès direct à un item de menu ou de fenêtre pop-up

Enter

Pop-up - Remplace un clic de souris sur le bouton par défaut

barre d'espace

Pop-up - Remplace le clic de souris sur l'objet sélectionné

Ctrl+Page Up

Pop-up - Passer à onglet précédent

Ctrl+Page Down  

Pop-up - Passer à onglet suivant

Image non disponible

Exercice : appliquez chacun des raccourcis dans plusieurs fenêtres pop-up et sans utiliser la souris.

II-D. Raccourcis pour fenêtres et applications

Les raccourcis ci-dessous concernent les applications ouvertes par l'utilisateur.

Raccourcis

Thème - Action

(Maj+) Alt+Tab

Fenêtre - Application suivante (précédente)

Windows + d

Fenêtre - Réduit les fenêtres et affiche le bureau, puis revient à la fenêtre initiale

Windows + r 

Divers - Permet d'exécuter un programme

Image non disponible

Exercice : appliquez Alt+Tab de nombreuses fois.

II-E. Raccourcis de l'explorateur

Les raccourcis ci-dessous sont utilisables dans l'explorateur Windows.

Raccourcis

Thème - Action

Windows + e

Explorateur - Lance l'explorateur

Tab

Explorateur - Passe de la partie gauche à la partie droite de l'explorateur

Maj+Tab

Explorateur - Passe de la partie droite à la partie gauche de l'explorateur

F2

Explorateur - Permet de renommer un élément (dossier ou fichier)

Drag & Drop

Déplace un fichier ou un répertoire. Remarque : lorsque les répertoires source et cible sont sur des volumes différents, une copie a lieu (plutôt qu'un déplacement)

Ceux-ci sont utilisés dans la partie gauche de l'explorateur :

Raccourcis

Thème - Action

Image non disponible

Explorateur / partie gauche - Replie l'arborescence si elle est ouverte, sinon passe au dossier parent

Image non disponible

Explorateur / partie gauche - Déplie l'arborescence si elle est fermée, sinon aucune action

Image non disponible

Explorateur / partie gauche - Passe à la ligne supérieure

Image non disponible

Explorateur / partie gauche - Passe à la ligne inférieure

Entrée

Explorateur / partie gauche - Affiche le contenu du dossier

Ceux-ci sont utilisés dans la partie droite de l'explorateur :

Raccourcis

Thème - Action

Image non disponible

Explorateur / partie droite - Passe à la ligne supérieure

Image non disponible

Explorateur / partie droite - Passe à la ligne inférieure

Image non disponible

Exercice : appliquez chacun des raccourcis plusieurs fois dans l'Explorateur.

II-F. Raccourcis Word

Les raccourcis ci-dessous concernent tous l'application Word.

Certains raccourcis sont visibles dans le bandeau de Word.

D'autres, comme dans le chapitre suivant, ne sont pas visible dans ce bandeau.

II-F-1. Word - raccourcis identiques en Word 2003 et 2007

Raccourcis

Thème - Action

Touche clic droit Image non disponible ou Image non disponible

Caractère - Sur un mot souligné en rouge : corriger l'orthographe ou la grammaire

Maj+Enter

Paragraphe - Insérer un retour à la ligne (tout en restant dans le même paragraphe)

F9 sur un sommaire

Affichage - Pour mettre à jour le sommaire (Exemple : F9, t, Enter)

Maj+F3

Caractère - Minuscule 1re lettre majuscule Majuscule (du mot courant ou de la sélection)

Ctrl+Maj+k

Caractère - Minuscule Petite Majuscule (du mot courant ou de la sélection)

Ctrl+Maj+F8

Caractère - Permet une sélection verticale de texte. Ce raccourci est utile dans le cas de données qui devraient être sous forme de tableau.

Alt+F7

Caractère - Corrige l'orthographe ou la grammaire du mot suivant.

Alt+e, l

Affichage - Affiche/Masque le code de tous les champs. Exemple, affiche {PAGE \* MERGEFORMAT} à la place du numéro de page

Alt+F9

Affichage - Affiche la liste des liaisons

Image non disponible

Exercice : appliquez chacun de ces raccourcis sous Word, notamment pour les raccourcis en gras. Pour les autres raccourcis, essayez ceux qui vous paraissent intéressants.

II-F-2. Word - Raccourcis 2003 à préférer

Tous les raccourcis ci-dessous issus de Word 2003 fonctionnent toujours sous Word 2007.

Ils ont une correspondance via le bandeau Word (Alt+…). Cependant, la version 2003 peut être préférée, car elle est plus simple à utiliser, ou plus cohérente avec les autres raccourcis, notamment si vous avez déjà l'habitude de les utiliser.

Word 2003 

Word 2007,…

Thème - Action

Alt+e,g, Image non disponible

Alt+Ctrl+v

Caractère - Recopie du texte sans mise en forme

Ctrl+d
= Ctrl+Maj+e

Alt+l,p,l

Caractère - Ouvrir la boite de dialogue de police

Ctrl+Enter

Alt+s, e

Paragraphe - Insérer un saut de page

Ctrl+r

Alt+l,a,i

Paragraphe - Augmente le retrait

Delete en début de ligne

Alt+l,a,u

Paragraphe - Diminue le retrait

Ctrl+j

Alt+l,j Alt+l,a,e

Paragraphe - Change de mode entre paragraphe justifié et aligné à gauche

Ctrl+e

Alt+l,a,t
Alt+l,a,e

Paragraphe - Change de mode entre paragraphe centré et non centré

Maj+Ctrl+Enter

Alt+j,d,f,t

Tableau - Split un tableau.

Image non disponible

Exercice 1 : quelle que soit la version, utilisez Ctrl+d puis tab, Maj tab et ctrl+lettre soulignée pour effectuer diverses actions.

Image non disponible

Exercice 2 : quelle que soit la version, utilisez le raccourci 2003 (ou 2007 pour les puristes adeptes de 2007) notamment pour les raccourcis en noir. Pour les raccourcis supplémentaires (en gris), essayez ceux qui vous paraissent intéressants.

II-F-3. Word - Raccourcis 2007 à préférer

Pour les raccourcis ci-dessous et en version 2007, l'ancien raccourci 2003 est à oublier. Soit parce qu'il ne fonctionne plus en 2007, soit parce qu'il a une correspondance plus simple ou plus « standard » en version 2007.

Word 2003 

Word 2007,…

Thème - Action

Alt+o, o

Alt+f, i

Options - Ouvre la fenêtre des options Word
Remarque : le raccourci de Word 2003 fonctionne toujours sous Word 2007

Alt+Ctrl+s, entrée

Alt+n,n, entrée

Affichage - Fractionne (split) ou défractionne (désplit) le document
Remarque : le raccourci de Word 2003 fonctionne toujours sous Word 2007

Alt+*

Alt+l, 8

Affichage - Afficher/Masquer ¶ et autres symboles de mise en forme masqués
Remarque : le raccourci de Word 2003 ne fonctionne plus sous Word 2007

Ctrl+Maj+ Image non disponible

Alt+l,p,a

Caractère - Augmente la taille des caractères sélectionnés
Remarque : le raccourci de Word 2003 était vraiment malaisé à utiliser pour la position des doigts

Ctrl+Alt+ Image non disponible

Alt+l,p,r

Caractère - Diminue la taille des caractères sélectionnés
Remarque : le raccourci de Word 2003 était vraiment malaisé à utiliser pour la position des doigts

Image non disponible

Exercice : quelle que soit la version, utilisez le raccourci 2007 pour les raccourcis supplémentaires (en gris), essayez ceux qui vous paraissent intéressants.

II-F-4. Word - Cas du copier/coller une mise en forme

Word 2003 

Word 2007,…

Thème - Action

Ctrl+Maj+c
Puis
Ctrl+Maj+v

Alt+l,r,m puis souris

Caractère - Copier/coller de la mise en forme sur une partie de texte
on peut préférer la version 2003 pour sa simplicité, la non-utilisation de la souris et son homogénéité avec le fameux Ctrl+c, Ctrl+v

Ctrl+Maj+c
Puis
Ctrl+Maj+v

Alt+l,r,m puis souris

Paragraphe - Copier/coller de la mise en forme sur des paragraphes entiers :
attention, un bogue Word 2000 à 2007 (et au-delà ?) a été constaté (parfois) dans la version Ctrl+Maj+C/V. Cette anomalie peut entraîner la MAJ, non annulable par Ctrl+z, des styles.
Donc, soit utiliser la version 2007 du raccourci (ou directement le bouton Image non disponible), soit utiliser la version 2003, mais avec précaution et en sauvegardant le document avant de l'utiliser.

II-F-5. Word - Cas de la fonctionnalité « atteindre »

Word 2003 

Word 2007,…

Thème - Action

F5 (ou Ctrl+b)

Alt+l, r, s, t

Déplacement - Ouvre la boite de dialogue « atteindre ». Cette fonctionnalité permet de sélectionner un large panel d'ensembles d'objets. Cf. les options de la « pop-up ». On peut par exemple atteindre une page particulière à partir de son n°, parcourir tous les signets, commentaires, notes, champs/images, tableaux…
Personnellement, je préfère la version 2003.

Alt+F1

 

Déplacement - Sélectionne le champ ou l'image suivante

Maj+Alt+F1

 

Déplacement - Sélectionne le champ ou l'image précédente

Voici quelques exemples d'utilisation :

  • Ctrl+b, Alt+a, Image non disponible ou Image non disponible plusieurs fois jusqu'à la ligne « tableaux » puis :

Alt+v : Sélectionne le tableau suivant

Alt+d : Sélectionne le tableau précédent

  • Ctrl+b, Alt+a, Image non disponible ou Image non disponible plusieurs fois jusqu'à la ligne « titre » puis :

Alt+v : Sélectionne le titre suivant

Alt+d : Sélectionne le titre précédent

Image non disponible

Exercice : essayez les raccourcis qui vous paraissent intéressants.

II-F-6. Nouveaux raccourcis à partir de Word 2007

Il existe pléthore de nouveaux raccourcis à partir de Word 2007. N'hésitez pas à constituer votre propre liste.

Raccourcis

Thème - Action

Alt+…

Divers - Accès à toutes les options depuis le bandeau

Alt+l

Divers - Accès à l'onglet « Accueil » du bandeau

Alt+s

Divers - Accès à l'onglet « Insertion » du bandeau

Alt+s, y

Édition - Insère un symbole

Alt+s, q, c

Édition - Insère un champ (exemple : n° de page…). Très utile pour les entêtes et pieds de page

Alt+r

Révisions - Accès à l'onglet « Révision » du bandeau

Alt+r, u, espace

Révisions - Entre ou sort du mode « Suivi des modifications »

Alt+n

Affichage - Accès à l'onglet « Affichage » du bandeau

Alt+n, r

Affichage - Affiche ou masque la règle

Image non disponible

Exercice 1 : appliquez chacun de ces raccourcis sur un fichier Word.

Image non disponible

Exercice 2 : complétez votre propre liste de raccourcis en remplissant par exemple la zone ci-dessous :

Raccourcis

Thème - Action

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

II-G. Raccourcis Excel

Les raccourcis ci-dessous concernent tous l'application Excel.

Certains raccourcis sont visibles dans le bandeau d'Excel.

D'autres, comme dans le chapitre suivant, ne sont pas visible dans ce bandeau.

II-G-1. Excel - raccourcis identiques en Excel 2003 et 2007

Raccourcis

Thème - Action

Ctrl+Page Up

Affichage - Passe à onglet précédent

Ctrl+Page Down

Affichage - Passe à onglet suivant

Ctrl+Tab

Affichage - Change de classeur
Remarque : fonctionne aussi avec d'autres logiciels (Ex. : Word)

Maj+Espace

Sélection - Sélectionne la ligne courante (ou étend la sélection courante sur l'ensemble des lignes)

Ctrl+Espace

Sélection - Sélectionne la colonne courante (ou étend la sélection courante sur l'ensemble des colonnes)

F9

Formule - Force le calcul sur tous les onglets de tous les fichiers Excel ouverts

Maj+F9

Formule - Force le calcul sur l'onglet courant uniquement

Sélection de cellules, saisie d'une valeur, puis Ctrl+Entrée

Édition - Copie la valeur saisie sur l'ensemble des cellules sélectionnées

Sélection de cellules, F2, puis Ctrl+Entrée

Edition - Copie la valeur de la cellule active sur l'ensemble des cellules sélectionnées

Alt+Entrée

Édition - Permet un retour à la ligne à l'intérieur d'une cellule

Alt+Image non disponiblesur une cellule faisant l'objet d'une validation

Édition - Sélectionne les valeurs autorisées

Alt+Image non disponiblesur une autre cellule.

Edition - Sélectionne parmi les valeurs situées au-dessous de la cellule

Alt+e, m

Suppression onglet

Maj + F11

Ajout nouvel onglet

Ctrl+F2

Fichier - Affiche la fenêtre « Aperçu avant impression »

Alt+Page up

Affichage - déplace la feuille de calcul d'un écran (= d'un cran) vers la gauche

Alt+Page down

Affichage - déplace la feuille de calcul d'un écran (= d'un cran) vers la droite

Ctrl+l

Affichage - Affiche ou masque les symboles d'un plan
                Affiche ou masque les marques de lignes ou de colonnes groupées

Ctrl+"

Formule - Affiche les formules à la place des résultats de calcul (et vice versa)

F9 sur une partie de formule sélectionnée

Formule - Évalue la partie sélectionnée (puis transforme cette partie en valeur si l'on appuie sur Enter ou ne fait rien si l'on appuie sur Escape)

F4 sur une partie de formule sélectionnée

Formule - Passe alternativement d'un mode à l'autre parmi les quatre modes suivants :

o  fixe/fixe

Exemple « N5 »

o  variable/variable

Exemple « $N$5 »

o  fixe/variable

Exemple « N$5 »

o  variable/fixe

Exemple « $N5 »

Maj+F2

Divers - Ajoute ou modifie un commentaire de cellule

Alt+F1

Divers - Crée un graphique dans l'onglet courant avec les données dans la plage active

F11

Divers - Crée un graphique dans un nouvel onglet avec les données dans la plage active

Ctrl+k

Divers - Insère un lien hypertexte

Ctrl+e sur un nombre

Format - Change le format de la cellule en nombre scientifique (en exposant)

Ctrl+j sur une date ou un nombre

Format - Change le format de la cellule en date au format « 01-Juil-16 »

Ctrl+m sur un nombre

Format - Change le format de la cellule en nombre au format montant en euros

Ctrl+r sur un nombre

Format - Change le format de la cellule en nombre au format standard

Image non disponible

Exercice : appliquez chacun de ces raccourcis sous Excel, notamment pour les raccourcis en gras. Pour les autres raccourcis, essayez ceux qui vous paraissent intéressants.

II-G-2. Excel - Raccourcis 2003 à préférer

Tous les raccourcis ci-dessous, issus d'Excel 2003, fonctionnent toujours sous Excel 2007.

Ils ont une correspondance via le bandeau Excel (Alt+…). Cependant, la version 2003 peut être préférée, car elle est plus simple à utiliser ou plus cohérente avec les autres raccourcis, notamment si vous en avez déjà l'habitude.

Pour les raccourcis de collage spécial ci-dessous, la version 2003 est préférée, car elle est plus homogène et plus rapide dans bien des cas.

Excel 2003 

Excel 2007,…

Thème - Action

Alt+d, p, g

Alt+é, g

Affichage - Groupe la ligne ou la colonne (*)

Alt+d, p, s

Alt+é, i, s

Affichage - Dissocie la ligne ou la colonne (*)

Alt+o, o

Alt+f, i

Options - Ouvre la fenêtre des options Excel

Ctrl+c puis Alt+e, g, f

Ctrl+c puis Alt+l,v, g, f

Édition - ne recopie que le format

Ctrl+c puis Alt+e, g, v

Ctrl+c puis Alt+l,v, v

Édition - ne recopie que le contenu (=ou le résultat en cas formule)

Ctrl+c puis Alt+e, g, u

Ctrl+c puis Alt+l,v, u

Édition - ne recopie que les formules

Ctrl+c puis Alt+e, g, g

Ctrl+c puis Alt+l,v, g, g

Édition - recopie en une seule fois la largeur de plusieurs colonnes

Ctrl+c puis Alt+e, g, r

Ctrl+c puis Alt+e, g, p

Edition - recopie la transposée d'un tableau
Effectuer une transposée revient à passer les colonnes en lignes et les lignes en colonnes

Ctrl+c sur une valeur puis Alt+e, g, m sur une plage

Ctrl+c sur une valeur puis Alt+l,v, g, m sur une plage

Édition - multiplie par la valeur copiée la totalité de la plage sélectionnée

F5, Alt+c
Ou
Ctrl+t, Alt+c

Alt+l, r, s, t puis Alt+c

Déplacement - Ouvre la boite de dialogue «atteindre ». Cette fonctionnalité permet de sélectionner un large panel d'ensemble d'objets. Cf. les options de la « pop-up ». On peut par exemple atteindre toutes les formules, antécédents, dépendants…
Deux exemples d'utilisation :

  • Connaitre le nombre de formules de la page : voir «Nb (non vides) » dans le bas de page ;
  • Mettre en fond (remplissage) jaune, toutes les formules.

(*) : Pour ces fonctions, il y a deux utilisations possibles :

  • soit sélectionner d'abord la ligne ou la colonne, puis appliquer le raccourci ;
  • soit appliquer le raccourci sans être entièrement sur une ligne ou colonne et choisir ensuite 'ligne' ou 'colonne' lors de l'apparition de la fenêtre pop-up correspondante.

Image non disponible

Exercice : quelle que soit la version, utilisez le raccourci équivalent 2003 (ou 2007 pour les puristes adeptes de 2007) pour les autres lignes du tableau ci-dessus, notamment pour les raccourcis en noir. Pour les raccourcis supplémentaires (en gris), essayez ceux qui vous paraissent intéressants.

II-G-3. Excel- Raccourcis 2007 à préférer

Pour les raccourcis ci-dessous et en version 2007, l'ancien raccourci 2003 est plutôt à oublier. Soit parce qu'il ne fonctionne plus en 2007, soit parce qu'il a une correspondance plus simple ou plus « standard » en version 2007.

Excel 2003 

Excel 2007,…

Thème - Action

Alt+i, f
ou Maj+F3

Alt+u, f
ou Maj+F3

Formule - Insère une fonction
Remarque : le raccourci de 2003 fonctionne toujours sous 2007

Ctrl+*

Ctrl+a

Sélection
Ctrl+*: Sélectionne les cellules du tableau courant
Ctrl+a: Sélectionne les cellules du tableau courant, puis toute la page
Remarque : le raccourci de 2003 ne fonctionne plus sous 2007

Alt+d, n

Alt+é, e

Édition - Ouvre la fenêtre d'assistant conversion
Remarque : le raccourci de 2003 fonctionne toujours sous 2007

Alt+o, o, a, Alt+q

Alt+m, y ou Alt+n, y

Affichage - Ajoute/supprime le quadrillage
Remarque : le raccourci de 2003 ne fonctionne plus sous 2007

Image non disponible

Exercice : quelle que soit la version, essayez (plutôt) le raccourci 2007.

II-G-4. Nouveaux raccourcis à partir d'Excel 2007

Il existe pléthore de nouveaux raccourcis à partir d'Excel 2007. Voici ceux que j'utilise le plus souvent. N'hésitez pas à constituer votre propre liste.

Raccourcis

Thème - Action

Alt+…

Divers - Accès à toutes les options depuis le bandeau

Alt+l…

Divers - Accès à l'onglet « Accueil » du bandeau

Alt+l, f, n

Edition - Ouvre la fenêtre « format cellule » - onglet «  Nombres »

Alt+l, b, a

Édition - Ouvre la fenêtre « format cellule » - onglet « Bordures »

Alt+l, d

Édition - Ouvre la fenêtre de changement de couleur de remplissage

Alt+l, p, c

Édition - Ouvre la fenêtre de changement de couleur de police

Alt+u…

Formules - Accès à l'onglet « Formules » du bandeau

Alt+u, n

Formules - Ouvre la fenêtre de gestionnaire des noms

Alt+é…

Données - Accès à l'onglet « Données » du bandeau

Alt+é, m

Données - Ouvre la fenêtre de suppression des doublons

Alt+é, f

Données - Ajoute/supprime les filtres

Image non disponible

Exercice 1 : appliquez chacun de ces raccourcis sur un fichier Excel.

Image non disponible

Exercice 2 : complétez votre propre liste de raccourcis en remplissant par exemple la zone ci-dessous :

Raccourcis

Thème - Action

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

Alt+

 

II-G-5. Excel - Cas des raccourcis d'insertion ou de suppression

Version quelconque

Excel 2007…

Thème - Action

Ctrl+ +

Image non disponible + i

Édition - Insère une cellule, ligne ou colonne

Ctrl+ +

Image non disponible + e

Édition - Insère une cellule, ligne ou colonne après un copié

Ctrl+ +

Image non disponible + e

Édition - Insère une cellule, ligne ou colonne après un coupé

Ctrl+ -

Image non disponible + s

Édition - Supprime une cellule, ligne ou colonne

La méthode utilisant Ctrl semble plus avantageuse, car plus homogène. Néanmoins, celle utilisant le bouton Image non disponible est plus aisée au niveau du placement des doigts.

Image non disponible

Exercice : choisissez la méthode que vous voulez (Ctrl ou via Image non disponible ) et appliquez chacun de ces raccourcis sur un fichier Excel, pour une cellule (ou une plage), une ligne entière ou une colonne.

II-G-6. Excel - Quelques exemples d'utilisation des raccourcis de base

  • Sélection de plusieurs lignes

Maj+Espace, puis Maj+Image non disponible ou Maj+Image non disponible
ou l'inverse : Maj + Image non disponible/Image non disponible, puis Maj+Espace

  • Sélection de plusieurs colonnes

Ctrl+Espace, puis Maj+Image non disponible ou Maj+Image non disponible
ou l'inverse : Maj +Image non disponible ou Image non disponible puis Ctrl+Espace

  • Les supprimer

Ctrl+x

  • Copier par insertion une ou plusieurs lignes (principe identique pour des colonnes)
    • Sélectionner la/les ligne(s) :

    cf. infra.

    • Copier :

    Ctrl+c

    • Se déplacer sous la/les ligne(s) à copier :

    Image non disponible ou Image non disponible

    • Sélectionner la/les ligne(s) :

    Maj+Espace

    • Insérer la/les ligne(s) copiée(s) :

    Ctrl+ +

  • Déplacer une ou plusieurs lignes (principe identique pour des colonnes)
    • Sélectionner la/les ligne(s) :

    cf. infra.

    • Couper :

    Ctrl+x

    • Se déplacer sous la/les ligne(s) à déplacer :

    Image non disponible ou Image non disponible

    • Sélectionner la/les ligne(s) :

    Maj+Espace

    • Insérer la/les ligne(s) coupée(s) :

    Ctrl+ +

  • Changer en une seule action toutes les valeurs d'une plage verticale
    • Aller sur cette plage :

    Ctrl+Image non disponible (par exemple, si l'on se trouve à gauche)

    • Aller en haut de cette plage :

    Ctrl+Image non disponible

    • Sélectionner la plage :

    Maj+Ctrl+Image non disponible

    • Taper le texte souhaité :

    texte souhaité

    • Mise à jour de la plage :

    Ctrl+Entrée

  • Recopier la hauteur d'une ligne
    • Sélectionner la ligne modèle=source :

    Maj+Espace

    • Mémoriser la hauteur de ligne :

    Image non disponible + r, r, Enter, Enter

    • Se déplacer sur la 1re ou dernière ligne cible :

    Image non disponible ou Image non disponible

    • Sélectionner la/les ligne(s) cibles : Maj+Espace, puis Maj+ :

    Image non disponible ou Maj+Image non disponible(ou l'inverse)

    • Recopier la hauteur :

    F4

  • Recopier la largeur d'une colonne
    • Se positionner sur la colonne modèle (sans pour autant être obligé de la sélectionner entièrement) :

    Image non disponible ou Image non disponible

    • Copier la cellule :

    Ctrl+c

    • Se déplacer sur la 1re ou dernière colonne cible :

    Image non disponible ou Image non disponible

    • Sélectionner la/les colonne (s) cibles :

    Maj+Image non disponible ou Maj+Image non disponible

    • Recopier la largeur :

    Alt+e, g, g, enter

Image non disponible

Exercice : appliquez chacun de ces exemples sur un fichier Excel.

III. Word - Actions, astuces, problèmes et solutions

III-A. Explorer Word

Image non disponible

Exercice : voici un exercice simple et très formateur : créez un fichier Word de brouillon et explorez chacune des fonctionnalités accessibles à partir du bandeau.

III-B. Word - Astuces

  • Manipulation de lignes : pour manipuler une ou plusieurs lignes (supprimer, recopier…), il est préférable de sélectionner l'ensemble de la (des) ligne(s) et non une partie seulement.

    Pour cela, il y a deux méthodes :

    • via la souris : placez suffisamment le curseur à gauche du texte pour que le curseur soit représenté par un Image non disponible puis sélectionnez les lignes voulues ;
    • par le clavier : allez en début de ligne (touche 'début' ou Ctrl+FlècheImage non disponible), puis enfoncez Maj Image non disponible ou MajImage non disponible . Cette méthode est également préconisée pour tout éditeur de texte qui le permet.

    Image non disponible

    Exercice : recopiez, supprimez, déplacez des lignes par chacune des deux méthodes dans un fichier Word.

  • Visualiser le nom du style courant :

    • Word 2007 : menu Accueil, Style (ou Alt+L+Y), puis bouton « inspecteur de style » Image non disponible

    Image non disponible

    Exercice : essayez !

  • Modification de Style - méthode par menu :

    • Word 2000 : Menu Format, Style, Modifier, Format…
    • Word 2003 : Menu Format, Styles et mise en forme, Clic droit : Modifier le style, Format…
    • Word 2007 : Menu Accueil, clic sur Style (ou faire Alt+L+Y), Clic droit sur un style particulier: clic sur Modifier le style, Format…

    Image non disponible

    Exercice : dans un fichier Word, choisissez un style utilisé et modifiez-le en bleu souligné. Constatez que toutes les lignes correspondant à ce style ont été changées.

  • Modification de Style - méthode par l'exemple

    Modifier directement un style à partir d'un paragraphe exemple est beaucoup plus simple que par menu :

    • Word 2007 : Image non disponible + t, e
    • Attention : cette opération n'est pas annulable par Ctrl+z. Il est donc fortement conseillé de sauvegarder le document avant de l'utiliser.

    Image non disponible

    Exercice : dans un fichier Word, choisissez une ligne au hasard et mettez-la en rouge Calibri 1. Puis effectuez la modification du style de cette ligne. Constatez que toutes les lignes correspondant à ce style ont été changées.

  • Ajout d'un lien interne = Renvoi

    Image non disponible

    Exercice : saisissez un renvoi de type «cf. chapitre … … page … » en insérant successivement le numéro du paragraphe, son titre et son numéro de page.

  • Go back après clic sur un lien (ou un titre de sommaire) : si l'on appuie sur un lien interne dans Word (par Ctrl+Clic) et que l'on veut revenir en arrière = à la page du lien, la solution (à partir de Word 2007) est d'ajouter dans la barre d'outils d'accès rapide le bouton « précédent » = Image non disponible.

    Pour ajouter ce bouton, cliquer sur Image non disponible, cliquez sur « Autres commandes », choisissez « toutes les commandes » et ajoutez le bouton « précédent » = Image non disponible.

    Par exemple, s'il y a dans le document le texte « cf. chapitre II 'Raccourcis clavier' page 6» et si j'appuie sur ce lien, j'arrive alors en page 6. L'intérêt du bouton Image non disponible est alors de me permettre de revenir au texte initial : « cf. chapitre  … ».

    Image non disponible

    Exercice : ajoutez ce bouton et faites l'essai sur un lien quelconque du sommaire ou à l'intérieur du document.

IV. Excel - problèmes, solutions et manipulation de données

IV-A. Explorer Excel

Image non disponible

Exercice : voilà un exercice un peu long, mais simple et très formateur : créer un fichier Excel de brouillon et explorer chacune des fonctionnalités accessibles à partir du bandeau.

IV-B. Diverses astuces Excel

  • Il vaut mieux rester (en général = sauf si l'on veut connaitre la position d'une colonne) en option « style de référence » = LC et non « style de référence » = L1C1. Il est ainsi plus aisé de modifier les formules pour transformer une référence relative (sans $) à une référence absolue (avec $). Pour ce faire allez dans les options Excel (raccourcis Alt+o, o), partie « Formules » et décocher la case « Style de référence L1C1 » : Image non disponible.

  • Pour un retour à la ligne dans une cellule : enfoncez « Alt + Entrée ».

  • Pour saisir un texte sur plusieurs cellules en même temps: sélectionnez la plage concernée, saisissez le texte, et appuyez sur « Ctrl + Entrée ».

  • Si l'on ne veut pas afficher des #N/A dans un tableau :

    imaginons que la formule « formule » renvoie parfois #N/A et que je souhaite remplacer ces #N/A par des ""

    • sous 2003, il fallait utiliser la formulation =SI(ESTERREUR(formule) ;""; formule) pour masquer les #N/A. Ce qui imposait d'écrire deux fois la même formule ;
    • à partir de 2007, la fonction SIERREUR() permet d'éviter cette répétition de la formule puisque la syntaxe devient : =SIERREUR(formule ;"").
  • Pour forcer le calcul sur tout ou partie des formules (en calcul sur ordre) : appuyez sur Ctrl+h, puis remplacer «=» par «=». Cela ne change pas le contenu des cellules mais force le calcul sur les zones sélectionnées.
  • Pour tous les autres problèmes de calcul non effectués ou de format, voir le chapitre suivant.
  • Si les macros ne s'exécutent pas, il se peut que cela soit dû au niveau de sécurité trop élevé

    Sous Excel 2000/2003 : il faut aller dans Outils>Macros>Sécurité>, puis autoriser les macros

    Sous 2007, il faut aller dans le menu Développeur puis « Sécurité des macros » et faire de même.

  • Recopie d'une cellule vers le bas :

    faire un double-clic sur la poignée de recopie Image non disponiblepour recopier vers le bas une cellule (tant qu'il y a une valeur dans les cellules de la colonne de gauche et/ou de droite) ;

    s'il y a des trous dans les colonnes de gauche ou de droite on peut utiliser la méthode suivante :

    • pour une copie simple :

      • Ctrl+c,
      • Aller tout en bas : Ex. : (Ctrl+) Image non disponible ou Image non disponible pour aller sur cette colonne puis Ctrl+ Image non disponible,
      • Se mettre une colonne non vide (Ctrl +) Image non disponible ou Image non disponible et Ctrl+ Image non disponible pour remonter à la dernière ligne,
      • Puis par (Ctrl +) Image non disponible ou Image non disponible pour revenir à la colonne initiale, et à la ligne souhaitée,
      • Maj+Ctrl + Image non disponible pour revenir à la cellule à recopier,
      • Ctrl+v pour effectuer la copie ;
    • pour une copie incrémentale :

      • Sélectionner la plage cible : étapes 2 à 4 précédentes,
      • Maj+Ctrl + Image non disponible pour revenir à la cellule à recopier,
      • Maj+ Image non disponible pour descendre d'un cran,
      • x et Ctrl+Enter pour remplir la plage cible,
      • Ctrl+ pour revenir à la cellule à recopier,
      • Éventuellement Maj+ Image non disponible pour sélectionner deux cellules (si données en format nombre),
      • Double-clic sur la poignée de recopie.
  • Faire une recherche approximative : il est possible de remplacer un caractère cherché par «?» et un ou plusieurs caractères par «*».


Image non disponible
Exercice : testez toutes ces astuces.

IV-C. Excel - Problèmes liés aux formats et aux calculs

Un des grands problèmes d'Excel est le fait que les nombres qui apparaissent au format nombre (nombres, montants, dates…) peuvent être interprétés par Excel de deux manières :

  • soit comme des nombres (nombres, montants, dates) ;
  • soit comme du texte s'ils sont au format texte.

Ainsi, ce qui apparaît comme un nombre (nombre, montant ou date) peut en réalité être un texte pour Excel. Les conséquences sont nombreuses au niveau des formules :

  • les tests d'égalité ne fonctionnent plus (par exemple dans des fonctions de recherche) ;
  • les fonctions de somme, moyenne, etc. ne tiennent pas non plus compte de ces nombres.

Il arrive parfois qu'une formule ne se calcule pas correctement ou pas du tout.

  • Si le calcul est « sur ordre » (= 'manuel'), essayez de le forcer par F9 ou de le remettre en automatique (2000-2003 : Alt+o, o, c. 2007 : Alt+o, o, f, Alt+a).
  • Sinon, les deux cellules comparées n'ont peut-être pas le même format (exemple nombre au format texte et nombre au format nombre). Voir alors la remarque ci-dessous.
  • Pour convertir au format nombre, des nombres qui sont au format texte :

    • sélectionner les cellules concernées ;
    • puis les passer au format nombre : Alt+t, c (ou Ctrl+Maj+1) , Alt+c, nombre, Entrée ;
    • puis :
      • Méthode 1 : faire F2 et Entrée sur chaque cellule (si peu de cellules),
      • Méthode 2 : sélectionner la plage, puis menu données, convertir, terminer (si une seule colonne),
      • Méthode 3 : faire un Ctrl+h et remplacer / par / (s'il s'agit de dates),
      • Méthode 4 : faire un Ctrl+h et remplacer 0 par 0 (si l'on est certain que toutes les cellules contiennent un 0),
      • Méthode 5 : faire Crtl+c sur une cellule contenant juste '1' , puis Alt+e, g, m sur l'ensemble des cellules à transformer ;
    • Remarque : on constate alors que les nombres sont bien considérés comme des nombres, car ils sont alors alignés à droite au lieu de gauche en tant que texte (à condition d'être en alignement horizontal standard).
  • Pour conserver des 0 non significatifs pour des nombres (exemple pour garder '00124' dans le fichier), il existe plusieurs méthodes :

    • d'une manière générale : mettre la cellule en format texte,
    • lors d'une conversion : utiliser le format texte sur la colonne concernée,
    • autre possibilité : ajouter un caractère textuel en début, notamment une simple quote (exemple '00012'),
    • lors d'un remplace : ne pas utiliser le Ctrl+h (qui transforme en format numérique), mais plutôt la fonction SUBSTITUE (qui conserve les 0 non significatifs).
  • Pour faire apparaitre des 0 non significatifs à l'affichage (exemple '124' dans le fichier qui apparait comme '00124' à l'affichage), il faut modifier le format nombre, choisir la catégorie « Personnalisée » et taper dans la zone «Type» '00000' avec autant de 0 que l'on souhaite.

Image non disponible
Exercices :

  • placez Excel en Mode de calcul « Manuel », et constatez que les calculs ne s'effectuent qu'après appui sur F9 ;
  • remettez Excel en Mode de calcul « Automatique », et constatez que les calculs s'effectuent immédiatement après modification des données sources ;
  • placez quatre nombres en format texte afin d'y ajouter des 0 non significatifs à gauche ;
  • remettez-les en format nombre par chacune des quatre méthodes proposées ;
  • testez la méthode SUBSTITUE ;
  • tapez 512 dans une cellule et affichez ce nombre en 00512 grâce à un format personnalisé.

IV-D. Tableaux structurés

Auparavant, un tableau était construit de manière très libre avec un ensemble de lignes et de colonnes et éventuellement une ou plusieurs lignes d'entête. Ce type de tableau libre, appelé 'plage' ou 'plage normale' sous Excel peut désormais être évité (cf. "avantages des tableaux structurés" ci-dessous). En effet, une des grandes nouveautés d'Excel 2007 est la mise en œuvre des tableaux structurés. Pour plus d'information sur ce sujet, voir le tutoriel correspondant sur développez.com : http://silkyroad.developpez.com/excel/tableau/

Ces tableaux sont des tableaux estampillés 'tableaux structurés' à partir de la fonctionnalité « Insertion / Tableau ».

Ils sont repérables par les caractéristiques suivantes :

  • un symbole Image non disponiblese situe sur la dernière cellule en bas à droite du tableau ;
  • il existe une ligne d'entête ou les noms dans cette ligne sont en gras, tous présents et tous différents ;
  • l'option « Outil de tableau/Création » apparait lorsque l'on est sur un de ces tableaux ;
  • ils ont un nom unique (exemple : Tableau3) qui apparait dans cette option (à gauche du bandeau).

Ils peuvent avoir été créés de plusieurs manières :

  • transformation d'un tableau libre en tableau structuré ;
  • création d'un tableau structuré vide à n lignes et n colonnes puis remplissage.

Ils peuvent ensuite être étendus en ajoutant des lignes ou des colonnes de plusieurs manières :

  • soit en se positionnant sur le coin en bas à droite et en l'étendant via la souris Ajout de lignes ou de colonnes ;
  • soit en étant sur la dernière cellule et en appuyant sur tab Ajout d'une nouvelle ligne ;
  • soit en saisissant une valeur juste sous dernière ligne Ajout d'une nouvelle ligne ;
  • soit en saisissant une valeur juste à droite de la dernière colonne Ajout d'une nouvelle colonne.

Les avantages indéniables des tableaux structurés sont nombreux :

  • ils réduisent les risques d'erreur ;
  • ils fournissent des références structurées. Exemple : « Tableau1[[#Totaux];[NbJours]] » ;
  • leur utilisation au sein de fonctionnalités est plus structurée et peut être effectuée dans des formules, TCD, graphiques…
  • il peut y avoir des filtres sur chacun des tableaux d'une même feuille Excel. Alors que pour des tableaux libres d'une même feuille, les filtres ne peuvent être que sur un seul tableau à la fois ;
  • en cas de modification de formule sur une cellule, cette modification est reportée sur toute la colonne.

Image non disponible

Exercice :

  • construisez des tableaux structurés de différentes manières ;
  • étendez-les de différentes manières ;
  • utilisez-les de différentes manières.

IV-E. Les tris et les filtres

Il existe plusieurs manières naturelles d'effectuer des tris.

Voici ces différentes manières, présentées de la meilleure à la moins bonne.

IV-E-1. Tris sur un tableau structuré (via les boutons de filtre ou via les boutons de tris standards )

Cette méthode est la plus plus sûre et la plus rapide.

Ces tris peuvent s'effectuer via les boutons de filtre ( Image non disponible ), ou plus rapidement encore via les boutons de tris standards ( Image non disponible ) directement sur une des cellules du tableau.

IV-E-2. Tri par fenêtre de tri

Ceci s'effectue grâce au raccourci Alt+t, n.

Elle peut aussi s'effectuer via les menus, par exemple sous Excel 2007, dans l'onglet « Données » via le bouton « Trier » : Image non disponible

C'est la méthode conseillée si l'on souhaite maîtriser totalement l'opération de tri, notamment pour trier une partie (=un sous-ensemble de lignes et/ou de colonnes) d'un tableau.

Remarque : ce n'est réalisable que sur un tableau non structuré.

IV-E-3. Tri par boutons de filtre sur un tableau libre (non structuré)

Cette méthode, qui utilise les boutons de filtre (Image non disponible), présente le risque de désynchronisation des tableaux. (Cf. chapitre IV-F-3. Le risque de désynchronisation de tableauxLe risque de désynchronisation de tableaux )

IV-E-4. Tri par boutons standards sur un tableau libre (non structuré)

Il s'agit des deux boutons de tris standards suivants : Image non disponible.

Ils effectuent automatiquement les deux tâches suivantes :

  • sélection automatique et quasi intuitive du tableau courant ;
  • puis tri sur la colonne sélectionnée selon l'ordre du bouton choisi (Asc. ou Desc.).

Ils sont simples et rapides à utiliser pour des tableaux basiques, mais :

  • chaque tri annule parfois complètement le tri précédent. On ne peut donc pas toujours trier sur une colonne X puis sur une colonne Y ;
  • la (/les) ligne(s) de titre n'est pas toujours conservée.

Image non disponible
Exercice : explorez chacune de ces méthodes.

IV-F. Lorsque l'on ne peut pas utiliser les tableaux structurés

Normalement, si vous utilisez des tableaux structurés, vous n'avez pas besoin des conseils ci-dessous.

Néanmoins, si vous ne pouvez faire autrement et utilisez des tableaux non structurés, voici quelques conseils.

IV-F-1. À quel moment utiliser le $ ?

  • Cas d'une référence à une plage limitée : si dans une formule, une référence à une plage limitée verticalement se trouve dans une formule que l'on prévoit de recopier verticalement, il est nécessaire d'utiliser le $ pour indiquer les coordonnées verticales de cette référence.

Exemple : plage limitée verticalement = Tableau1 = B10:B20 (limité en ligne 10 et 20)

Image non disponible

Voir le tutoriel http://fauconnier.developpez.com/excel/bases/references/ concernant les références absolues et relatives pour une description détaillée.

De même, si dans une formule, une référence à une plage limitée horizontalement se trouve dans une formule que l'on prévoit de recopier horizontalement, il est nécessaire d'utiliser le $ pour indiquer les coordonnées horizontales de cette référence.

  • Cas d'une référence à des colonnes entières (resp. lignes entières) :

    Si l'on envisage d'effectuer une copie verticale (resp. horizontale) d'une formule, pointant vers un tableau (ex. ici Tableau1) et s'il n'y a pas d'autres tableaux sous celui-ci (resp. à côté de celui-ci).

    Alors, plutôt que d'utiliser le $, il vaut mieux prendre comme référence l'ensemble de la/des colonne(s) (resp. lignes) du tableau plutôt que de se limiter au tableau seul.

    L'avantage est que la formule est plus simple et que le code est plus robuste en cas d'ajout/suppression de lignes (resp. colonnes) dans le tableau.

    Exemple, dans la copie d'écran ci-dessous : Plage = colonne B:B

    Image non disponible

IV-F-2. Le risque de référence incorrecte

Reprenons le chapitre précédent.

Si l'on souhaite rester dans le 1er exemple (=cas d'une référence à une plage limitée), et que l'on ajoute une ligne, on risque d'obtenir la situation fausse suivante :

Image non disponible

Pour éviter ce risque, il y a trois solutions à priori possibles :

  • la meilleure solution serait d'utiliser un tableau structuré pour tableau1. Ainsi en cas d'ajout d'une donnée à la ligne suivante, le tableau structuré est automatiquement étendu et la référence pointant vers ce tableau (ici tab1) est inchangée.

    Image non disponible
  • sinon, pour les irréductibles, on peut toujours ajouter une ligne horizontale de délimitation verticale et faire englober cette ligne dans la référence. Cela permet alors de sécuriser le tableau puisque toute ligne ajoutée sera au-dessus de cette ligne de délimitation.

    Image non disponible
  • enfin la solution d'adapter toutes les références en cas d'extension du tableau est une solution quasi impossible, trop compliquée et risquée.

On le voit donc, dans cet exemple, les tableaux structurés s'imposent.

IV-F-3. Le risque de désynchronisation de tableaux

La désynchronisation d'un tableau correspond au fait qu'une partie (gauche) du tableau ne correspond plus à l'autre partie (droite)

Exemple :

Avant désynchronisation

Après désynchronisation

Image non disponible

Image non disponible

Cette désynchronisation peut avoir lieu dans deux cas :

  • désynchronisation par filtre incomplet : c'est le cas le plus fréquent ; on ajoute des colonnes (à gauche ou à droite du tableau original) et on oublie d'étendre le filtre. Ainsi, dès que l'on va trier via les boutons de filtre, les colonnes ajoutées ne seront pas triées.
    L'utilisation de tableaux structurés résout ce problème.
    Sinon, une autre solution consisterait à ajouter un encadrement vertical de chaque côté du tableau et les inclure dans le filtre. Ces délimitations permettent alors d'alerter l'utilisateur. Ainsi si une colonne est ajoutée « entre » ces deux encadrements verticaux, elle sera automatiquement incluse dans le filtre.
    Avec ces lignes verticales, les tableaux ont ainsi cet aspect :
    Image non disponible

    Dans tous les cas, pensez à faire des copies régulières de votre fichier de travail ;

  • désynchronisation par colonne vide. Cas plus rare, cette désynchronisation a lieu (plutôt sur des grands tableaux) à cause d'une ou plusieurs colonnes vides que l'on ne voit pas. Le filtre couvre bien l'ensemble du tableau, mais la désynchronisation s'effectue quand même au niveau de la colonne vide. Elle peut se faire par exemple si on clique sur les boutons standards de tris.
    Solution : ici, l'utilisation de tableaux structurés résout entièrement ce problème puis-qu'aucun titre ne peut être vide.

Image non disponible
Exercice : lisez cette page entièrement (c'est déjà suffisant). Éventuellement, testez chacune des deux méthodes de désynchronisation.

IV-G. Croisements de données (=appareillages = jointures)

Le but est ici de rapatrier les données X à côté du tableau Y en en faisant coïncider clé X des deux tableaux. Pour ce faire, les données X ne doivent contenir aucun doublon sur la clé X, i.e. chaque clé doit être unique dans le tableau X. Par contre, il peut y avoir des doublons de clé X dans le tableau Y comme le montre l'exemple ci-dessous.

Voici trois méthodes pour atteindre ce but.

IV-G-1. Méthode par RechercheV

Exemple 1 :

Image non disponible

Exemple 2, Cas où la clé (clé X) n'est pas en début de tableau :

Image non disponible

Personnellement, j'utilise toujours 0 comme dernier paramètre de RerchercheV ce qui permet à cette fonction d'être efficace quels que soient les tris effectués.

Avantages / Inconvénients du RechercheV 

  • Avantage : on rapatrie les données voulues avec une seule formule.
  • Inconvénient : utilisées de cette manière, les formules sont moins robustes que Equiv + Index (les valeurs renvoyées deviennent différentes si l'on insère des colonnes dans le tableau des données X).
  • Inconvénient : S'il y a beaucoup de colonnes de RechercheV: les calculs sont plus longs qu'avec Equiv + Index
  • Inconvénient : la colonne clé doit être la première colonne de la plage, donc obligatoirement à gauche de la (des) colonne(s) rapatriée(s).
  • Inconvénient : lors de la recopie horizontale de la formule, il faut adapter manuellement le n° de la colonne renvoyée. -> Donc plus long à adapter si beaucoup de colonnes.
  • Remarque : depuis la version 2007, les tableaux structurés permettent de circonscrire les plages de traitement. Il n'est donc plus utile (avec les tableaux structurés) d'utiliser RechercheV sur une colonne entière. Par contre pour pouvoir utiliser les tableaux structurés, la colonne clé doit être la première colonne du tableau.

Pour plus d'information sur ce sujet, voir le tutoriel correspondant sur Developpez.com : http://fauconnier.developpez.com/tutoriels/excel/recherchev

IV-G-2. Sélection des colonnes en entier Méthode par Equiv + Index

Image non disponible

Avantages / Inconvénients du Equiv + Index 

  • Inconvénient : nécessité d'une colonne supplémentaire, sauf si l'on combine les deux formules dans chaque cellule.
  • Avantage : possibilité de recopie horizontale et verticale (= sur tout le tableau) de la formule.
  • Avantage : si l'on utilise Equiv dans une seule colonne et Index dans de nombreuses autres colonnes, alors les calculs sont plus rapides qu'avec RechercheV.
  • Avantage : les formules sont robustes et indépendantes des insertions de colonnes.
  • Remarque : depuis la version 2007, les tableaux structurés permettent de circonscrire les plages de traitement. Il n'est donc plus utile (avec les tableaux structurés) d'utiliser Index/Equiv sur une colonne entière. Par contre, il faut alors adapter les formules sur chacune des colonnes rapatriées.

Exemple :

Image non disponible

Image non disponible
Exercice : testez les deux méthodes.

IV-G-3. Méthode par tri et copie

Mode opératoire :

  • trier les données X selon qu'il y ait ou non une correspondance dans le tableau Y (en utilisant EQUIV, cf. page suivante) ;
  • puis, parmi les données X ayant une correspondance, trier selon la clé X ;
  • trier les données Y selon qu'il y ait ou non une correspondance dans le tableau X (en utilisant EQUIV) ;
  • puis, parmi les données Y ayant une correspondance, trier selon la clé X.

Image non disponible

Avantage / Inconvénients 

  • Avantage : la méthode par copie est nécessaire si l'on désire conserver les mises en forme des cellules rapatriées.
  • Inconvénient : ne fonctionne que s'il n'y a pas de doublons de part et d'autre. Cf. chapitre H 'Les doublons', pour éliminer les doublons (= éliminer les lignes en double) avant d'appliquer cette méthode.
  • Inconvénient : risque d'erreur plus important. Vérifier alors à nouveau l'égalité de clé une fois les données X rapatriées. Dans notre exemple, il faut ensuite vérifier l'égalité entre les colonnes E et H de l'onglet Joint3b.
  • Note : ne pas hésiter à faire régulièrement des copies de sécurité de ses fichiers Excel.


Image non disponible
Exercice : testez cette méthode.

IV-H. Les doublons

Il y a plusieurs traitements possibles autour des doublons :

  • soit les éliminer, c'est-à-dire éliminer les données identiques (sur une ou plusieurs colonnes) = les doublons en trop ;
  • soit faire apparaître les clés qui ne sont pas uniques = tous les doublons ;
  • soit, parmi les clés qui ne sont pas uniques, faire apparaître uniquement les doublons en trop c'est-à-dire ceux à partir de chaque 2e ligne.

Voici ces trois traitements.

IV-H-1. Éliminer les doublons en trop

  • Sous Excel 2007, la méthode est ultra simple : sélectionnez le tableau à dédoublonner, puis aller dans le menu « Données », puis « Supprimer les doublons ». Remarque : cet outil de suppression conserve alors chaque première ligne de doublon et supprime lignes en double suivantes. Image non disponible
    Exercice : si vous utilisez office 2007 ou suivant, testez cette méthode.
  • Sous Excel 2003, il existait deux méthodes. La première méthode est d'utiliser les filtres élaborés :
    Image non disponible

    • Avantage : méthode rapide.
    • Avantage : pas besoin de trier les données.
    • Inconvénient: méthode archaïque lorsque l'on possède Excel version 2007 ou plus.
  • Sous Excel 2003, la seconde méthode est par tableau croisé dynamique :

    • Sélectionner la colonne clé avec l'entête ;
    • Alt+d, r, Suivant, Suivant, puis « Nouvelle feuille » ou « Feuille existante » ;
    • Terminer, puis drag & drop du champ clé dans la zone des champs de ligne (colonne à gauche) ;
    • Puis drag & drop du champ clé dans la zone des données (cœur du tableau) ;
    • Éventuellement, double-cliquer sur le champ pivot de table pour sélectionner NbVal ;
    • Vous obtenez alors la liste des données dédoublonnée ainsi que les quantités associées.

    Image non disponible Image non disponible

    • Avantage : permet de connaitre le nombre de lignes par données clés.
    • Avantage : pas besoin de trier les données.
    • Inconvénient: Ne permet de sélectionner/filtrer/recopier les données que d'une seule colonne (la colonne clé).
      Astuce : on peut ajouter une colonne concaténant plusieurs autres colonnes que l'on souhaite étudier.

    Image non disponible
    Exercice : si vous utilisez (encore) office 2003, testez cette méthode.

IV-H-2. Faire apparaître tous les doublons

Le but est ici l'identification des données ayant des doublons, c'est-à-dire d'identifier toutes les lignes dont la clé est présente en plus d'un exemplaire.

À partir d'Excel 2007 : le plus simple est d'utiliser une mise en forme conditionnelle sur les doublons (ou sur les valeurs uniques). 

Sous Excel 2003 : la méthode était plus laborieuse : vous deviez répéter la formule suivante : « =NB.SI(colonne ; cellule)>1 » sur toutes les lignes. Apparait alors VRAI en face de tous les doublons :

Image non disponible

Image non disponible
Exercice : testez la méthode qui correspond à votre version Excel.

IV-H-3. Faire apparaître tous les doublons en trop

Le but est de marquer les lignes contenant les doublons en trop, c'est-à-dire d'identifier uniquement les lignes en double parmi celles dont la clé est présente en plus d'un exemplaire.

La méthode est la suivante :

  • trier d'abord les lignes sur la colonne étudiée (la colonne clé) ;
  • puis répéter une des deux formules ci-dessous sur une colonne libre :

Formule 1 :
* les « x » représentent les lignes en trop.
* Variante : la formule peut être plus simple : =B3=B2
( => les VRAI représentent les lignes en trop)

Formule 2 :
* les nombres > 1 représentent les lignes en trop

Image non disponible

Image non disponible

  • puis, éventuellement :

    • transformer les formules en résultat (Sélection, Ctrl+c, Alt+e, g, v)
    • trier toutes les données sur la colonne D.

Avantages :

  • méthode pour identifier les lignes en trop ;
  • permet de sélectionner/filtrer/recopier les données de plusieurs colonnes puisque c'est la toute la ligne qui est identifiée (contrairement à la page suivante).

Image non disponible
Exercice : testez cette méthode via les deux formules.

IV-I. Les regroupements de données

IV-I-1. Définition

Faire un groupement de données cela signifie :

  • définir certains champs comme identifiants de groupes ;
  • et définir si l'on souhaite grouper les données sur chaque champ horizontalement ou verticalement (cf. exemple suivant) ;
  • puis appliquer des fonctions de groupe (min, max, premier= first, dernier=last, nombre=count, moyenne…) sur tout ou partie des autres champs (données groupées).

Nous allons illustrer les regroupements au travers de trois exemples différents.

IV-I-2. Exemple n° 1

Dans cet exemple, nous partons du tableau initial ci-dessous et souhaitons obtenir le groupement n° 1 suivant. Les champs « Année » et « Lieu » sont ici tous les deux groupés horizontalement, avec les fonctions : nombre, moyenne (tarif), min (tarif) et max (tarif) :

Image non disponible

Comme nous allons le voir avec quatre méthodes différentes, il n'existe pas encore de solution simple, souple et répondant entièrement au besoin de cet exemple.

  • Méthode 1, avec un TCD (Tableau croisé dynamique)
    Image non disponible
    La méthode par TCD offre l'avantage de la souplesse et de la rapidité d'exécution.
    Cependant il peut présenter deux inconvénients :

    • le résultat est non normé. Il présente des lignes de totaux que l'on ne souhaite pas forcément. Ainsi dans notre exemple, le TCD montre cinq lignes au lieu des trois souhaitées ;
    • il n'existe pas de fonctions min et max portant sur des chaines de caractères.
  • Méthode 2, TCD avec concaténation
    Cette méthode consiste à concaténer les étiquettes de lignes avant d'effectuer le TCD. Le tableau résultant est ainsi normé :
    Image non disponible

    • Avantages : souplesse, tableau normé.
    • Inconvénients : étape supplémentaire de concaténation, pas de fonction de min/max sur chaines de caractères.
  • Méthode 3, tableau figé, avec formules
    Cette 3e méthode utilise les fonctions xxx.SI.ENS d'Excel. Exemple SOMME.SI.ENS, NB.SI.ENS, MOYENNE.SI.ENS
    Exemple :
    Image non disponible

    • Avantages :

      • rapidité ;
      • tableau normé.
    • Inconvénients :

      • les fonctions NB.SI.ENS, MOYENNE.SI.ENS et SOMME. SI.ENS existent. Par contre il n'y a pas, en version 2007, de fonction MIN.SI.ENS ni MAX.SI.ENS ;
      • la structure du tableau doit être adaptée manuellement en cas d'ajout de nouvelles étiquettes de lignes (dans notre exemple, en cas de nouvelle année ou de nouveau lieu).
  • Méthode 4, via SQL
    En utilisant une base de données (ex. Access, Oracle, SQL server…) il est possible en une seule requête d'obtenir exactement le résultat souhaité.

    • Avantages : souplesse, tableau normé, résultat souhaité.
    • Inconvénients : nécessite de passer par un SGBD (Access, Oracle…)

    Voici une méthode concernant Access :

    • Créer une table virtuelle pointant vers le tableau (<=> lier une table) : Alt + F, D, L…
    • Remarque : Access peut pointer :

      • vers Excel : vers un onglet entier ou vers un tableau défini par un nom,
      • vers un fichier texte, une table Access…
    • Réaliser la requête,

      • avec l'assistant de création,
      • en mode création de requête

        • opération = groupement pour les champs de groupement,
        • ou opération = fonction de groupe (min, max…) pour les autres champs,
      • ou en mode SQL : syntaxe classique du group by.

Image non disponible
Exercice : testez au minimum la méthode 1 et la méthode 2.

IV-I-3. Exemple n° 2

Dans cet exemple, nous partons du tableau initial ci-dessous et souhaitons obtenir le groupement n° 2 suivant. Les champs « Année » et « Lieu » sont utilisés comme groupement (horizontal et vertical) avec les fonctions : nombre et moyenne (tarif)

Image non disponible

  • Méthode 1, avec un TCD (Tableau croisé dynamique)
    Image non disponible
    La méthode par TCD offre l'avantage de la souplesse et de la rapidité d'exécution.
    Le seul inconvénient est qu'il n'existe pas de fonctions min et max portant sur des chaines de caractères.
  • Méthode 2, tableau figé, avec formules
    Cette méthode n'offre ici aucun avantage par rapport au TCD.
  • Méthode 3, avec Access
    La solution consiste à effectuer une requête analyse croisée, que l'on peut générer :

    • avec l'assistant de création,
    • en mode création de requête;

      • opération = groupement pour les champs de groupement (analyse = entête de ligne ou de colonne),
      • ou opération = fonction de groupe (min, max…) pour les autres champs (analyse = valeur)
    • ou en mode SQL : syntaxe propre à Access avec group by + transform + pivot.

Image non disponible
Exercice : testez au minium la méthode 1.

IV-I-4. Exemple n° 3

  Dans cet exemple, nous partons du tableau initial ci-dessous et souhaitons obtenir le groupement n° 3 suivant.

Les champs « Année » et « Lieu » sont utilisés comme groupement (horizontal et vertical).

La particularité est qu'il n'y a qu'une seule valeur par couple année-lieu.

La conséquence est que fonctionnellement, une fonction min ou max correspond à la même chose ;c'est-à-dire à une fonction d'égalité (puisque le min = le max = la donnée de LA ligne initiale).

Il s'agit donc d'un groupement sans véritable calcul.

Image non disponible

La solution sous Excel n'est pas triviale (si vous en avez une meilleure contactez-moi).

En effet, elle nécessite une colonne et une ligne en plus (de préférence masquées toutes les deux).

Image non disponible

Voici les différentes actions à effectuer :

  • tout d'abord ajoutez la colonne D (que nous masquerons ensuite) et qui concatène l'année et le lieu. formule en D6 : « =B6&C6 ». À recopier verticalement ;
  • ensuite, ajoutez la ligne 4 (que nous masquerons ensuite) et dont le rôle est de faire en sorte que le lieu soit renseigné sur toutes les colonnes. Ce qui n'est pas le cas de la ligne 3 où le lieu n'est pas renseigné en J4, L4 et N4. Formule en I4 : « =SI(I3="";H4;I3) » . À recopier horizontalement ;
  • indiquez la formule suivante en I6 : « =SIERREUR(RECHERCHEV($H6&I$4;$D:$F;2;0) ; "" ) » ;
  • indiquez la formule suivante en J6 : « = SIERREUR (RECHERCHEV($H6&I$4;$D:$F;3;0) ; "")» ;
  • recopiez verticalement la plage I6:J6 pour remplir le tableau sous « Londres » ;
  • recopiez la plage I6:J8 sur les deux autres lieux.

Remarque pour des raisons de visibilité, je n'ai volontairement pas utilisé de noms dans les formules utilisées.

IV-J. Normalisation de données issues du Web

IV-J-1. Introduction

Nous avons parfois besoin, pour un sujet précis, d'avoir un tableau simple contenant une ligne unique par item, et un certain nombre de colonnes bien définies. C'est ce qu'on pourrait appeler (en simplifiant) « données normalisées ».

Or il est fréquent de trouver des sources de données ayant un format bien éloigné de celui-ci.

Typiquement, lorsque l'on récupère des données d'une page web, il est très rare d'avoir les données souhaitées directement normalisées. En effet, la plupart du temps ces données :

  • sont dispersées sur plusieurs pages ;
  • sont au milieu d'autres informations ou images inutiles ;
  • et ont des formats bien éloignés d'un simple tableau ;

Voici donc deux chapitres méthodologiques concernant cette normalisation :

  • le chapitre 2 suivant est un exemple très concret de normalisation de données issues du Web (ici des pages jaunes) ;
  • le chapitre 3 présente une généralisation de l'exemple concret, c'est-à-dire une méthode générale.

IV-J-2. Exemple : récupération de coordonnées

Dans cet exemple, nous allons récupérer et normaliser les données du site pagesjaune.fr et concernant les Coiffeurs à Conflans Ste Honorine (en 2012). Nous étudierons cet exemple, sachant que le mode opératoire pour la mise en forme de données provenant d'autres sources est souvent très similaire, d'où le chapitre 3 suivant.

Mode opératoire

  • Étape 1 : Récupération des données : copier-coller les données des deux pages web dans le même onglet Excel (placer le collage des pages web les uns en dessous des autres) ;

    Image non disponible
  • Étape 2 : Élimination des images : copier - collage spécial valeur de la totalité de l'onglet dans un autre onglet.
    En effet, cette méthode en deux étapes permet de conserver les mises en tableau des données web tout en supprimant la totalité des images, des liens hypertexte…

    Image non disponible
  • Étape 3 : Numérotation des lignes : insertion d'une colonne à gauche (colonne 1) et numérotation de toutes les lignes (cf. « Recopie d'une cellule vers le bas », chapitre 4.B). (Ceci va permettre de trier à loisir les différentes lignes tout en étant certain de pouvoir revenir à l'ordre initial).

    Image non disponible
  • Étape 4 : tri sur colonnes « données » et élimination des données répétitives inutiles
    Dans l'image ci-dessous, et pour une meilleure visibilité, les données supprimées sont colorées en rouge, les données conservées sont en vert :

    Image non disponible
  • Étape 5 : retri sur colonne 1 pour vérifier que l'ensemble est correct.
    Dans l'image ci-dessous, et pour une meilleure visibilité, la première ligne pour chaque coiffeur est en gras :

    Image non disponible
  • Étape 6 : Marquage des nièmes lignes de chaque groupe.
    Le but de cette étape est d'identifier rapidement la première ligne pour chaque coiffeur (celle qui a été positionnée manuellement en gras dans l'image ci-dessus).
    Il y a deux méthodes possibles. Si nous sommes certains qu'il y a le même nombre de lignes pour chaque groupe (ici chaque coiffeur), nous utilisons la méthode 1. Sinon il faut utiliser la méthode 2.

    • La méthode 1 est à utiliser s'il y a quasiment toujours le même nombre de lignes de chaque groupe.
      Les étapes sont :

      • ajout d'une colonne de marquage (ici col 2) ;
      • marquage de la première ligne du premier groupe (ici un « x ») ;
      • sélection des lignes du premier groupe dans cette colonne (cf. fig. 1) ;
      • double-clic sur la poignée de recopie (cf. fig. 2) ;
      • puis remplacement des x par des 1.

      On voit que cette méthode ne peut pas fonctionner dans notre exemple puisque les « 1 » ne correspondent pas aux premières lignes de chaque groupe (celles en gras) (cf. fig. 2).
      Remarque : si nous n'avions conservé que les noms, adresses et numéro de téléphone (trois lignes par groupe), cette méthode aurait fonctionné.
      Image non disponible

    • Méthode 2 :
      Le but est d'abord de trouver un moyen d'identifier les premières (2e, 3e, .. ou dernières) lignes de chaque groupe par une formule.
      Ici, on voit que chaque 2e ligne contient le mot « 0 conflans », nous allons donc nous servir de cette caractéristique par une formule ajoutée en colonne 3 : ex. =SI(ESTERREUR(CHERCHE(« 0 CONFLANS S »;LC(1))); ""; 1). Remarque pour que cette formule fonctionne, veiller à passer en style de référence L1C1 (Alt+o, o, formules…
      Image non disponible
  • Étape 7 : Numérotation de toutes les lignes de chaque groupe.
    Maintenant que l'on sait séparer les différents groupes, le but est ici de numéroter les lignes à l'intérieur de chaque groupe. Remarque pour les connaisseurs d'Oracle : cela correspondrait à un rank dans un select.

    • La première étape est de copier les 1 et de les coller en collage spécial valeur.
    • On peut supprimer la colonne 2 inutile.
    • Ensuite, on remonte les 1 d'une cellule pour qu'ils correspondent à la première ligne de chaque groupe.
    • On insère ensuite une colonne à droite des 1 dans laquelle on va numéroter les lignes à l'intérieur de chaque groupe. Par exemple une formule du type =SI(LC(-1)=1;1;L(-1)C+1) en colonne 3 basée sur la col. 2.
    • Enfin, on transforme les formules en valeur (=copier, collage spécial valeur).
      Image non disponible
  • Étape 8 : Tri par n° de ligne dans le groupe puis décalage.
    Le but de cette étape est de décaler vers la droite certaines cellules.

    • En premier on passe en valeur les n° de ligne.
    • Ensuite on trie sur les n° de lignes.
    • Puis on décale les cellules :

      • les lignes n°1 ne sont pas décalées ;
      • les lignes n°2 sont décalées d'une colonne vers la droite ;
      • les lignes n°N sont décalées de N -1 colonnes vers la droite (utiliser la touche F4).
    • N'oubliez pas d'étendre le filtre sur les nouvelles colonnes (si vous n'utilisez pas de tableaux structurés) :
      Image non disponible
    • Cela donne finalement ceci :
      Image non disponible
  • Étape 9 : réalignement des données sur les premières lignes.

    • Nous allons tout d'abord retrier les lignes par n° de ligne (ici sur colonne 1).
    • Ensuite, nous allons réaligner les données :

      • se mettre cellule L2C5 (correspondant lignes n° 2, c'est-à-dire ici aux adresses des coiffeurs) et décaler les cellules vers le haut ;
      • puis se mettre sur la cellule juste à droite (les lignes n° 3 = n° de téléphone) et décaler les cellules deux fois vers le haut (utiliser F4) ;
      • etc. (décaler vers la droite et augmenter le nombre de F4)
    Image non disponible
  • Étape 10 : suppression des lignes et colonnes inutiles et finalisation.

    • Pour cette étape, nous retrions sur les n° de ligne dans le groupe (colonne 3).
    • Puis nous supprimons les lignes inutiles (les lignes vides).
    • Nous supprimons les colonnes inutiles (colonnes 1 à 3).
    • Nous concaténons les colonnes 4 et 5.
    • Et nous améliorons un peu la présentation.

    Ce qui donne :

    Image non disponible

IV-J-3. Généralisation

Si l'on généralise l'exemple précédent, on s'aperçoit que la transformation de données répétitives nécessite toujours quatre phases :

  • Phase préliminaire (correspond aux étapes 1 à 5 précédentes) :

    • récupération des données ;
    • éventuellement : élimination des images ;
    • numérotation des lignes ;
    • élimination des données inutiles.
  • Identification (et éventuel regroupement) des nièmes lignes de groupe : (étapes 6 et 7 précédentes) :
    Plusieurs outils et méthodes peuvent être utilisés :

    • recopie répétitive grâce à la poignée de répétition (cf.étape 5 - méthode 1 dans l'exemple précédent) ;
    • utilisation de la fonction CHERCHE (cf. étape 5 - méthode 2 dans l'exemple précédent) ;
    • utilisation de la fonction INSTR ;
    • utilisation d'une formule basée sur une ou plusieurs cellules de la ligne + la cellule juste au-dessus (ou juste en dessous) Exemple =SI(LC(-1)=1;1;L(-1)C+1 (cf. étape 6 dans l'exemple précédent) ;
    • découpage de données (avec GAUCHE ou DROITE).
  • Modifications en masse des nièmes lignes de groupe (étapes 8 et 9 précédentes).
    Là aussi, plusieurs outils et méthodes peuvent être utilisés :

    • décalage (cf. étape 7 dans l'exemple précédent) ;
    • découpage de données (avec GAUCHE ou DROITE) ;
    • remplacement avec Ctrl+h ou SUBSTITUTE ;
    • concaténation (avec &) ;
  • Mise en forme finale (étape 10 précédente).

V. Annexe - Résumé des principaux raccourcis clavier

V-A. Raccourcis généraux

Légende de la colonne de gauche : Raccourcis essentiels, à connaitre, moins utiles, supplémentaires.

Raccourcis

Thème - Action

Ctrl+Flèche

  • Édition de texte
  • Sous Excel

Déplacement - rapidité : aller à la limite d'une zone (permet d'éviter la répétition)
Ctrl+Image non disponibleImage non disponible(limite de mot)      Ctrl+Image non disponibleImage non disponible(limite de paragraphe)
Ctrl+Image non disponibleImage non disponibleImage non disponibleImage non disponible(limite de zone Excel)

Maj+Flèche 

Déplacement/Sélection - sélection : sélection multiple de l'objet de départ à l'objet d'arrivée

Maj+Ctrl+Flèche 

Déplacement/Sélection - rapidité+sélection : sélectionner en se déplaçant jusqu'à la limite d'une zone.

Touche Début / Fin

Déplacement - Début/Fin de ligne

Ctrl+Touche Début / Fin

Déplacement - Début/Fin de document

Ctrl + clic souris

Sélection - Sélection d'objets (fichiers, textes, cellules…) non adjacents

F1

Divers - Aide applicative

F2

Divers - Entrer en mode édition dans zone de texte (Ex. : Excel)

Escape, F4, Ctrl+z, Ctrl+y

Divers - Gestion des actions (sortir de l'action, la répéter, l'annuler, la dé-annuler)

Touche clic droit Image non disponible ou Image non disponible

Divers - Touche menu contextuel

Ctrl+x, Ctrl+c, Ctrl+v

Édition - Coupé, Copié, Collé

Ctrl+g, Ctrl+i, Ctrl+u

Édition - Gras, Italique, Souligné (de la sélection)

Ctrl+a, Ctrl+f, Ctrl+h

Édition - Sélection totale (avec un comportement spécifique sous Excel), Recherche, Remplace

Ctrl+s, Ctrl+p

Fichier - Sauvegarde, Impression

Ctrl+w (ou Ctrl+F4)

Fichier - Ferme le document en cours

Drag & Drop

Édition - Déplace un objet (fichier, répertoire, texte…) (Remarque : comportement spécifique pour Windows)

Ctrl + Drag & Drop

Édition - Déplace un objet (fichier, répertoire, texte…) en le copiant

Alt + Print Screen

Fenêtre -Ne copie que la fenêtre courante dans le presse-papier

V-B. Raccourcis Word

Légende de la colonne de gauche : Raccourcis essentiels, à connaître, moins utiles, supplémentaires.

Raccourcis

Thème - Action

F9 sur un sommaire

Affichage - Pour mettre à jour le sommaire (Exemple : F9, t, Enter)

Alt+n,n, entrée

Affichage - Fractionne (split) ou défractionne (désplit) le document

Alt+l, 8

Affichage - Afficher/Masquer ¶ et autres symboles de mise en forme masqués

Alt+n (2007 et +)

Affichage - Accès à l'onglet « Affichage » du bandeau

Alt+n, r (2007 et +)

Affichage - Affiche ou masque la règle

Alt+o, o ou Alt+f, i

Options - Ouvre la fenêtre des options Word

Alt+… (2007 et +)

Divers - Accès à toutes les options depuis le bandeau

Touche clic droit Image non disponible ou Image non disponible

Caractère - Sur un mot souligné en rouge : corriger l'orthographe ou la grammaire

Alt+e,g, Image non disponible

Caractère - Recopie du texte sans mise en forme

Ctrl+d

Caractère - Ouvrir la boite de dialogue de police

Ctrl+Maj+c puis Ctrl+Maj+v

Caractère - Copier/coller de la mise en forme sur une partie de texte

Maj+Enter

Paragraphe - Insérer un retour à la ligne (tout en restant dans le même paragraphe)

Ctrl+Enter

Paragraphe - Insérer un saut de page

Alt+r (2007 et +)

Révisions - Accès à l'onglet « Révision » du bandeau

Alt+r, u, espace (2007 et +)

Révisions - Entre ou sort du mode « Suivi des modifications »

V-C. Raccourcis Excel

Légende de la colonne de gauche : Raccourcis essentiels, à connaitre, moins utiles, supplémentaires.

Raccourcis

Thème - Action

Ctrl+Page Up

Affichage - Passer à onglet précédent

Ctrl+Page Down  

Affichage - Passer à onglet suivant

Ctrl+Tab

Affichage - Change de classeur

Alt+d, p, g

Affichage - Groupe la ligne ou la colonne

Alt+d, p, s

Affichage - Dissocie la ligne ou la colonne

Alt+m, y ou Alt+n, y

Affichage - Ajoute/supprime le quadrillage

Maj+Espace

Sélection - Sélectionne la ligne courante (ou étend la sélection courante sur l'ensemble des lignes)

Ctrl+Espace

Sélection - Sélectionne la colonne courante (ou étend la sélection courante sur l'ensemble des colonnes)

Ctrl+a

Sélection - Sélectionne les cellules du tableau courant, puis toute la page

Alt+d, n ou Alt+é, e

Édition - Ouvre la fenêtre d'assistant conversion

Ctrl+c puis Alt+e, g, f

Édition - ne recopie que le format

Ctrl+c puis Alt+e, g, v

Édition - ne recopie que le contenu (=ou le résultat en cas formule)

Ctrl+c puis Alt+e, g, u

Édition - ne recopie que les formules

Ctrl+c puis Alt+e, g, g

Édition - recopie en une seule fois la largeur de plusieurs colonnes

Ctrl+Entrée

Édition - Copie la valeur saisie ou actuelle sur l'ensemble des cellules sélectionnées

Alt+Image non disponiblesur une cellule.

Edition - Sélectionne parmi les valeurs autorisées ou parmi celles situées au-dessous de la cellule

Ctrl+ +   ou   Image non disponible + i

Édition - Insère une cellule, ligne ou colonne

Ctrl+ +   ou   Image non disponible + e

Édition - Insère une cellule, ligne ou colonne après un copié ou après un coupé

Ctrl+ -    ou   Image non disponible + s

Édition - Supprime une cellule, ligne ou colonne

Alt+l, f, n

Edition - Ouvre la fenêtre « format cellule » - onglet «  Nombres »

Alt+l, b, a

Édition - Ouvre la fenêtre « format cellule » - onglet « Bordures »

Alt+i, f ou Alt+u, f

Formule - Insère une fonction

F9

Formule - Force le calcul sur tous les onglets de tous les fichiers Excel ouverts

Alt+…

Divers - Accès à toutes les options depuis le bandeau

Alt+l

Divers - Accès à l'onglet « Accueil » du bandeau

Alt+o, o

Options - Ouvre la fenêtre des options Excel

Alt+é…

Données - Accès à l'onglet « Données » du bandeau

Alt+é, m

Données - Ouvre la fenêtre de suppression des doublons

VI. Remerciements

Un grand merci à Francis Walter qui m'a invité, à Pierre Fauconnier pour sa relecture technique, à Claude Leloup pour sa relecture orthographique, et à Siguillaume pour son aide permettant la publication finale de cette page.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2017 Frédéric Morosoff. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.