Software">
Cours Complet Excel
Cours Complet Excel
Cours Complet Excel
ELMSAADI
Table des matières
INTRODUCTION ....................................................................................................................................... 5
Le classeur ................................................................................................................................................... 5
2
EXCEL
EXCEL- INTRODUCTION
FONDAMENTAUX OFFICE 2013
EXCEL
1. L’ECRAN
2. BARRE D’ETAT : CALCULS ET MODES D’AFFICHAGE
3. PARAMETRAGE DU LOGICIEL
Chapitre 1 – GESTION DES FEUILLES ET DES CLASSEURS
1. GESTION DES FEUILLES DE CALCUL
2. AFFICHAGE D’UNE FEUILLE DE CALCUL
3. TRAVAILLER SUR PLUSIEURS CLASSEURS
4. PROTEGER FEUILLES ET CLASSEURS
Chapitre 2 – SAISIE SIMPLE DE DONNEES
1. SELECTION DE CELLULES
2. SAISIE DANS LES CELLULES
3. MODIFICATION DES DONNEES
4. RECHERCHER, REMPLACER
5. VERIFICATION ORTHOGRAPHIQUE
6. INSERTION ET SUPPRESSION
Chapitre 3 – SERIES DE DONNEES
1. SERIE NUMERIQUE
2. SERIE CHRONOLOGIQUE
3. SERIE ALPHANUMERIQUE
4. SERIE CREEE A PARTIR D’UNE LISTE PERSONNALISEE
Chapitre 4 – MISE EN FORME ET DIMENSIONS
1. ELEMENTS DE MISE EN FORME
2. CREATION ET APPLICATION D’UN STYLE
3. MISE EN FORME CONDITIONNELLE
4. REPRODUCTION D’UNE MISE EN FORME
5. MODIFICATION DU THEME
6. LARGEURS DES COLONNES ET HAUTEURS DES LIGNES
Chapitre 5 – DEPLACER, COPIER, COLLER ET TRI RAPIDE
1. VOLET PRESSE-PAPIERS
2. DEPLACER
3. COPIER
4. OPTIONS DE COLLAGE
5. TRI RAPIDE
Chapitre 6 – MISE EN PAGE et IMPRESSION
1. ZONE D’IMPRESSION
2. SAUTS DE PAGE
3. EN-TETE ET PIED DE PAGE
4. AUTRES OPTIONS AVANT IMPRESSION
Chapitre 7 – LES FORMULES
1. OPERATEURS
2. REFERENCES RELATIVES OU ABSOLUES
3. EXEMPLE
4. REFERENCES : AUTRE FEUILLE, AUTRE CLASSEUR
5. ATTRIBUTION D’UN NOM
6. DATE ET HEURE
7. AUDIT DE FORMULES, ERREURS ET REFERENCES CIRCULAIRES
3
EXCEL
4
EXCEL
INTRODUCTION
Excel est un tableur. Il est une application conçue pour créer et utiliser des tableaux, appelés
ici « feuilles de calcul ».
Dans Word, on travaille sur un document.
Dans PowerPoint, on travaille sur une présentation.
Dans Excel, on travaille sur un classeur. Par défaut, le classeur est enregistré dans un fichier
portant l’extension « .xlsx ».
1. L’ECRAN de l’Excel
L’écran comprend le ruban, juste en dessous une barre avec deux zones de saisie, puis la
fenêtre principale qui est le classeur. Le classeur est amovible, par cliqué-glissé sur sa barre de
titre.
En-dessous du classeur, on trouve la barre d’état, puis la barre des tâches.
Le classeur
Un classeur est constitué de feuilles, ayant chacune un onglet. Cliquer sur un onglet permet
d’afficher la feuille correspondante.
L’onglet sur fond blanc désigne la feuille active.
Par défaut, le classeur comporte 3 feuilles, donc 3 onglets. On peut en rajouter, le nombre
maximal étant 255 feuilles.
A gauche des onglets, des boutons fléchés de défilement permettent d’accéder à
l’onglet souhaité, dès lors qu’ils sont nombreux et que certains sont masqués. Pour afficher la liste
de toutes les feuilles du classeur : clic droit sur l’un de ces boutons. Cliquer ensuite sur une feuille
permet de l’afficher.
Suite aux onglets correspondant aux feuilles existantes, un onglet d’info-bulle « Insérer une
feuille de calcul » permet d’insérer une nouvelle feuille.
5
EXCEL
A droite des onglets, une petite barre verticale permet, par cliqué-glissé, d’afficher
plus ou moins d’onglets (le pointeur se transforme en double-flèche).
La feuille de calcul
Colonnes et lignes
Une feuille de calcul comporte :
16 384 colonnes, dont les cases d’en-tête sont nommées de A à Z, puis de AA à AZ, puis de
BA à BZ, de CA à CZ, et ainsi de suite jusqu’à XFD.
1 048 576 lignes, dont les cases d’en-tête sont numérotées de 1 à 1 048 576.
Une feuille de calcul totalise donc plus de 17 milliards de cellules (16 384 *1 048 576). De
quoi faire bien des calculs !
Chaque cellule est référencée par la lettre, ou les deux ou trois lettres, de sa colonne,
suivie du numéro de sa ligne, sans espace entre les deux. Les lettres des colonnes et les chiffres
des lignes sont indiqués dans les cases d’en-tête de la feuille. Excel ne reconnaissant pas la
casse des lettres (majuscules ou minuscules), autant tout écrire en minuscules.
Quand le pointeur est sur une case d’en-tête, il se transforme en flèche noire, désignant la
colonne ou la ligne correspondante. Quand il est sur une cellule, il se transforme en croix blanche.
Cellule active - Plage de cellules - Zone active
Cellule active
La cellule active est la cellule dans laquelle va s’inscrire la saisie. Elle se distingue par un
cadre plus marqué : . La feuille ne peut en afficher qu’une.
A l’ouverture du classeur, la cellule active par défaut est A1.
Le nom de la cellule active apparaît dans la Zone Nom, située à gauche de la barre de formule,
juste au-dessus de la feuille de calcul. Par défaut, le nom d’une cellule est sa référence Colonne
Ligne (exemple : F5). On verra qu’on peut aussi lui attribuer un nom (exemple : Total).
Quand plusieurs cellules sont sélectionnées, le fond de la cellule active reste blanc, tandis que
les autres cellules sélectionnées changent de couleur.
Plage de cellules
Tout rectangle de cellules est appelé « plage de cellules », ou « plage ». Dans une plage, la
cellule active par défaut est la cellule située en haut à gauche.
On désigne une plage par la référence de sa 1ère cellule en haut à gauche, suivie d’un
double-point, puis de la référence de sa dernière cellule en bas à droite (exemple : B7:E12).
Zone active
C’est la plus petite plage de cellules, commençant par la cellule A1, et contenant toutes
les données de la feuille.
Les barres de défilement
6
EXCEL
Pour cela, cliquez-glissez sur l’un des deux (ou sur les deux) curseurs de fractionnement,
étroits rectangles situés juste au-dessus de la barre de défilement verticale, ou juste à droite de
la barre horizontale (le pointeur se transforme en double flèche).
Chaque nouvelle zone d’affichage est dotée de sa propre barre de défilement.
Les zones d’affichage font partie de la même feuille de calcul. Toute modification sur
l’une sera effectuée sur la feuille.
Juste au-dessous de la barre de titre du classeur, s’étend une barre contenant deux zones :
Zone de nom
Barre de formule
La barre de formule est située à droite de la « Zone Nom ». On peut écrire dans la cellule,
ou bien écrire directement dans cette zone. Le résultat est le même.
Un menu déroulant
Permet de choisir une fonction, dès lors que le signe égal = a été saisi dans la cellule active
(le signe = tapé en début de saisie, signifie en effet que la cellule contiendra le résultat d’une
fonction) et que la saisie n’a pas été validée.
Ce menu déroulant permet d’accéder rapidement aux fonctions les plus utilisées.
Le bouton fx affiche la fenêtre « Insérer une fonction », qui permet d’accéder à toutes les
fonctions proposées par Excel 2010.
Des calculs, après sélection d’une plage de cellules : moyenne des valeurs de la plage, nombre de
cellules vides, nombre de cellules contenant des valeurs (et non du texte), la somme des valeurs
de la plage, le minimum et le maximum des valeurs.
Trois modes d’affichage : « Normal » , « Mise en page » et « Aperçu des sauts de page »
. Pour que ces boutons soient présents sur la barre d’état, l’option « Afficher les raccourcis »
doit être cochée.
3. PARAMETRAGE DU LOGICIEL
Certaines caractéristiques du logiciel sont paramétrables. Pour modifier les paramètres, et
personnaliser ainsi le logiciel, affichez la fenêtre « Options Excel » : ouvrez le menu Fichier >
Options.
Le volet gauche de la fenêtre liste dix catégories : « Général », « Formules »,
« Vérification », « Enregistrement »… Le volet principal contient les options relatives à la
-bulle.
7
EXCEL
La gestion des feuilles passe par leurs onglets. Pour nommer, sélectionner, insérer, supprimer,
copier ou déplacer une ou plusieurs feuilles, on opère sur les onglets.
Pour afficher la liste des noms de toutes les feuilles du classeur, faites un clic droit sur l’un
des boutons de défilement des onglets. Cliquer sur un nom affiche la feuille correspondante.
Couleur de l’onglet
Pour choisir la couleur d’un onglet : clic droit sur l’onglet > Couleur d’onglet. Choisissez la
couleur.
Quand la feuille correspondante est sélectionnée, son nom est souligné de cette couleur.
Sinon, l’arrière-plan de l’onglet revêt cette couleur.
Groupe de travail
Plusieurs feuilles sélectionnées constituent ce qui est appelé « un groupe de travail ». Dès
que deux feuilles sont sélectionnées, cette appellation est indiquée dans la barre de titre, juste
après le nom du classeur : [Groupe de travail].
Pour insérer une seule feuille, après les feuilles existantes : activez l’onglet « Insérer une
feuille de calcul » .
Sinon, sélectionnez autant d’onglets que de feuilles à insérer.
Puis sous l’onglet Accueil, dans le groupe « Cellules », ouvrez le menu
déroulant du bouton « Insérer » > « Insérer une feuille ».
Ou bien : clic droit sur la sélection > Insérer ; dans la fenêtre « Insérer »,
cliquez sur « Insérer ».
Les nouvelles feuilles s’insèrent à gauche de la sélection.
Zoom
Par défaut, le zoom est à 100%. Une valeur supérieure agrandit l’affichage. Une valeur
inférieure permet d’avoir une meilleure vision d’ensemble sur une zone étendue.
Par défaut, le zoom s’applique à toute la feuille.
Pour modifier le zoom, on peut utiliser la zone zoom de la barre d’état, ou bien la fenêtre
« Zoom ». Pour afficher cette fenêtre, cliquez sur le bouton « Zoom », sous l’onglet Affichage,
ou bien sur le pourcentage de zoom dans la barre d’état.
Si vous souhaitez « zoomer » sur une plage de cellules, sélectionnez-la d’abord. Puis, sous
l’onglet Affichage, dans le groupe Zoom, cliquez sur le bouton « Zoom sur la sélection », ou bien
choisissez l’option « Ajusté à la sélection » dans la fenêtre « Zoom ».
Pour revenir à un affichage normal, c’est-à dire à un zoom de 100%, cliquez par exemple
sur le bouton « 100% » du groupe Zoom.
Plein écran
Sous l’onglet Affichage, dans le groupe « Affichages classeur », activez le bouton « Plein
écran ». Le ruban, ainsi que la barre de formule et la barre d’état, sont masqués. Cela permet
l’affichage d’une plus grande partie de la feuille de calcul.
Pour désactiver ce mode d’affichage, appuyez sur la touche Echap (Esc).
Une fois les classeurs ouverts, vous pouvez afficher la feuille d’un classeur en cliquant sur
9
EXCEL
Ouvrez le classeur. Sous l’onglet Affichage, dans le groupe Fenêtre, activez le bouton
« Nouvelle fenêtre ». Le nom de la 2ème fenêtre est celui du classeur suivi de « :2 ».
Toute modification dans une fenêtre est effectuée sur le classeur.
Comme tout fichier, on peut protéger un classeur lors de son premier enregistrement, en
utilisant la fenêtre « Enregistrer sous ». Dans cette fenêtre, activez le bouton « Outils » >
Options générales.
Renseignez la fenêtre « Options générales ». Vous pouvez définir un mot de passe pour la
lecture, ainsi qu’un mot de passe pour sa modification.
Un classeur en « lecture seule » est modifiable, mais les modifications ne pourront pas être
enregistrées. Il est en revanche proposé d’enregistrer une copie du fichier, contenant les
modifications apportées.
Un classeur marqué comme final, ne peut plus être modifié, sinon il faut désactiver le
marquage.
Pour marquer le classeur en cours, ouvrez le menu Fichier > Protéger le classeur (volet
central) > Marquer comme final.
L’icône d’info-bulle « Marqué comme final » apparaît dans la barre d’état quand le
Classeur actif est ainsi protégé.
Pour désacti ver l e marquage, effectuez l’opération inverse pour désactiver l’option
10
EXCEL
Masquer un classeur
Pour que l’ouverture d’un classeur nécessite la saisie d’un mot de passe : ouvrez le menu
Fichier > « Protéger le classeur » > « Chiffrer avec mot de passe ».
S’il est perdu, le mot de passe n’est pas récupérable.
Pour masquer la feuille active : sous l’onglet Accueil, dans le groupe Cellules, cliquez sur
le bouton « Format » > Masquer & afficher > Masquer la feuille.
Masquer n’est pas supprimer. Pour afficher une feuille masquée, choisissez en dernière
option > Afficher la feuille. Dans la fenêtre « Afficher », cliquez sur le nom de la feuille à
afficher.
1. Sélectionner 1es cellules à protéger (cf. chapitre 2 § 1 « Sélection des cellules »).
Masquer : après protection de la feuille (étape 3), les contenus des cellules masquées ne
s’afficheront pas dans la barre de formule. Pour masquer les cellules, faites un clic droit sur la
sélection > Format de cellule. Ouvrez la fenêtre « Format de cellule » à l’onglet « Protection », et
cochez la case « Masquée ». Validez.
Verrouiller : toutes les cellules sont verrouillées par défaut (cette option n’est effective
que si la feuille est protégée, voir étape 3 ci-dessous).
Pour verrouiller ou déverrouiller des cellules, on peut utiliser la fenêtre « Format de
cellule », ou bien le menu déroulant du bouton « Format » du groupe « Cellules ».
3. Protéger la feuille
Pour rendre effective la protection des cellules (masquage ou verrouillage), il est nécessaire
de protéger la feuille.
Par défaut, toutes les cellules de la feuille sont verrouillées. Ainsi, en protégeant la feuille
(en effectuant cette étape 3, sans effectuer les étapes précédentes), on protège toutes les cellules
de la feuille. Il est plus courant de protéger toute une feuille, plutôt que des plages de cellules.
Pour protéger la feuille, la première case « Protéger la feuille et le contenu des cellules
verrouillées » doit nécessairement être cochée. Validez.
Dans la mesure où toutes les cellules de la feuille sont verrouillées par défaut, vous
pouvez, à l’étape précédente, déverrouiller les cellules à protéger, puis choisir dans la
fenêtre « Protéger la feuille » l’option
« Sélectionner les cellules déverrouillées ».
Pour supprimer la protection d’une feuille, activez le bouton « Format » > Oter la
protection de la feuille.
Attribution d’un mot de passe à une sélection de cellules
L’attribution d’un mot de passe à une sélection de cellules s’effectue en plusieurs étapes :
- Otez la protection de la feuille, si elle est protégée.
- Sélectionnez les cellules à protéger.
- Sous l’onglet Révision, dans le groupe Modifications, activez le bouton « Permettre la
modification des plages ». Dans la fenêtre du même nom, cliquez sur
« Nouvelle ».
12
EXCEL
1. SELECTION DE CELLULES
La sélection peut être effectuée avec le clavier et la souris, ou bien avec la fenêtre
« Atteindre ». Pour désélectionner, cliquez n’importe où (s’il s’agit d’une seule cellule, cliquez
en dehors de la cellule).
Si on fait précéder un nombre d’une apostrophe (Exemple : ’16), Excel le considère comme
un texte et l’aligne à gauche dans la cellule.
Après validation, un triangle vert apparaît en haut à gauche de la cellule
signalant que ce nombre est de type texte (il ne pourra donc pas faire partie de formules
mathématiques).
Quand la cellule est sélectionnée, une balise s’affiche à côté. Elle indique qu’il s’agit d’un
nombre stocké sous forme de texte et elle propose plusieurs options, en particulier la
conversion en type nombre.
Après un clic droit sur la cellule contenant le commentaire, un menu contextuel propose les
options : Modifier, Effacer, Afficher / Masquer le commentaire.
commentaire » : faites un clic droit dans le cadre du commentaire > Format de commentaire.
Date
Heure
Dès qu’Excel reconnaît des heures, des minutes ou des secondes, il les convertit en
fractions. Pour le vérifier, il convient de mettre la cellule au format Standard (onglet Accueil,
groupe "Nombre", ouvrez le menu déroulant et cliquez sur Standard).
Seul le caractère : est admis pour séparer les heures et les minutes (exemple : 9h28 s’écrit
9:28 et est affiché 09:28), ainsi que pour séparer les minutes et les secondes.
Si on écrit 9h28, au lieu de 9:28, on ne pourra pas se servir de cette donnée en tant
qu’heure, ni dans les calculs ni dans les formules.
Dates et heures, reconnues en tant que telles par Excel, sont des données numériques.
Elles sont donc alignées à droite dans les cellules.
Saisie restreinte
Commencez par sélectionner les cellules concernées par l’application d’une saisie
restreinte.
Puis, affichez la fenêtre « Validation des données » : sous l’onglet « Données », dans le
groupe « Outils de données », activez le bouton « Validation des données ».
Après validation
Soit vous modifiez le contenu de la cellule dans la zone située juste au-dessus de la
feuille de calcul.
Soit vous double-cliquez dans la cellule, puis vous rectifiez.
Soit, la cellule étant active, vous tapez F2, puis vous modifiez.
Suppression de données
4. RECHERCHER, REMPLACER
Pour afficher la fenêtre « Rechercher et remplacer » : sous l’onglet Accueil, dans le groupe
« Edition », activez le bouton « Rechercher ». Cette fenêtre peut rester ouverte lors de la saisie
dans les cellules.
Rechercher
Le bouton « Suivant » sélectionne les cellules contenant la valeur recherchée, une par une.
Remplacer
5. VERIFICATION ORTHOGRAPHIQUE
Si la vérification orthographique est à effectuer dans une plage de cellules de la feuille active,
sélectionnez-la. Sinon la recherche s’effectue sur la zone active (cliquez sur une cellule
quelconque de la feuille).
Pour lancer la vérification orthographique : sous l’onglet « Révision », dans le groupe
« Vérification », cliquez sur le bouton « Orthographe ». Ou bien appuyez sur la touche F7.
Si Excel détecte un terme absent du dictionnaire, la fenêtre « Orthographe » est affichée.
Elle contient des suggestions de correction, ainsi que diverses options.
15
EXCEL
Pour créer une série numérique (§ 1), une série chronologique (§ 2) ou une série
alphanumérique (§ 3), on se servira :
Du cliqué-glissé sur la poignée d’une sélection de cellules, le pointeur revêt
l’aspect d’une croix noire .
Du menu de la balise active qui apparaît en fin de cliqué-glissé.
De la fenêtre « Série de données ». Pour l’afficher : sous l’onglet Accueil, dans le
groupe « Edition », activez le bouton « Remplissage » > Série.
1. SERIE NUMERIQUE
Série linéaire
Une série linéaire est une suite de nombres, chacun (à partir du 2ème) étant obtenu en
ajoutant un nombre fixe à l’élément précédent.
Le nombre ajouté est appelé « incrément » ou « pas ».
Exemple : 5, 8, 11, 14, 17, 20. Le pas de cette série linéaire est 3.
Saisissez les 2 premiers éléments de la série, sur deux cellules adjacentes, en ligne ou en
colonne. Exemple : saisissez 5 et 8.
Excel en déduit le pas de la série. Dans l’exemple, le pas est 3.
Pour créer une suite de nombres de valeur constante (exemple : 12, 12, 12) : saisissez ce
nombre. Après validation, sélectionnez la cellule et effectuez un cliqué-glissé sur la poignée.
Une seule cellule étant sélectionnée, Excel applique un pas nul.
Exemple : saisissez 12. Validez (en appuyant sur la touche Entrée ou en cliquant dans une
autre cellule). Sélectionnez la cellule. Puis cliquez-glissez sur sa poignée dans le sens souhaité.
16
EXCEL
Série géométrique
Une série géométrique est une suite de nombres, chacun (à partir du 2ème) étant obtenu en
multipliant par un nombre fixe l’élémentprécédent.
Exemple : 4, 8, 16, 32, 64. Le pas de cette série géométrique est 2.
2. SERIE CHRONOLOGIQUE
Une série chronologique est une suite de dates, chacune (à partir de la 2ème) étant obtenue
en ajoutant la même durée à la date précédente. La durée ajoutée est le « pas » de la série.
Exemple : 10/05/2011, 17/05/2011, 24/05/2011. Le pas de cette série chronologique est 7.
Comme pour la création d’une série linéaire, on peut utiliser pour créer une série
chronologique : cliqué-glissé sur la poignée de la sélection, balise active et fenêtre « Série de
données ».
Pour créer une série chronologique, procédez ainsi :
- Cliquez-glissez sur les cellules à remplir. Excel applique par défaut une
incrémentation de pas un. Exemple : 10/05/2011, 11/05/2011, 12/05/2011…
L’utilisation de la balise active est plus rapide. La fenêtre « Série de données » offre
17
EXCEL
davantage de possibilités.
3. SERIE ALPHANUMERIQUE
Les valeurs d’une série alphanumérique sont constituées d’un texte fixe et d’un nombre
qui peut être incrémenté.
La fenêtre « Série de données » n’est pas utilisable pour une série alphanumérique.
Dans l’incrémentation, Excel ne prend en compte que le nombre situé le plus à droite
(c’est-à-dire la dernière décimale).
Exemple : saisissez hauteur 1,84. Validez. Cliquez-glissez sur la poignée de la cellule.
Vous obtenez hauteur 1,85 puis hauteur 1,86…
18
EXCEL avancé
Les cinq premiers éléments cités ci-dessus font chacun l’objet d’un onglet de la fenêtre
« Format de cellule ».
Il existe plusieurs méthodes pour afficher cette fenêtre, en particulier les deux suivantes :
- Cliquez sur le lanceur du groupe Nombre.
- Ou bien : clic droit sur la sélection à mettre en forme > Format de cellule.
La fenêtre présente des options supplémentaires par rapport aux commandes du ruban.
Des boutons du ruban, dont certains sont présents sur la mini-barre d’outils (clic droit sur la
cellule pour l’afficher), peuvent être utilisés pour accéder directement aux mises en forme. Par
exemple, sont communément utilisés les boutons d’alignement ou les boutons de police (type,
taille, styles gras, italique et souligné).
Onglet : excepté pour l’application d’un thème (onglet Mise en page), on utilisera
toujours l’onglet Accueil.
Groupes : concernant le nombre et l’alignement, on utilisera les groupes du même nom.
Concernant la police, la bordure et le remplissage, on utilisera le groupe Police. Concernant le
format, on utilisera le groupe Cellules.
19
EXCEL avancé
Plutôt que de procéder à la mise en forme cellule par cellule, il est plus rapide de sélectionner,
avant ou après saisie, le maximum de cellules, avant de leur appliquer une mise en forme.
- Sélectionnez les cellules à traiter. Quand une seule cellule est sélectionnée, sélectionnez
bien la cellule (elle a un contour plus épais), et non son contenu (dans ce cas, vous
n’aurez accès qu’à l’onglet Police de la fenêtre « Format de cellule ») ;
- Cliquez sur les commandes de mises en forme souhaitées, en utilisant le ruban, la mini-
barre d’outils ou la fenêtre « Format de cellule ».
Formats d’affichage
Format Standard
Format Nombre
C’est le format communément utilisé pour l’affichage d’un nombre. Exemple : 3528,10
Par défaut, Excel affiche un nombre de ce format avec deux décimales et sans séparateur de
milliers. Vous pouvez modifier le nombre de décimales et ajouter un séparateur de milliers.
Dans le ruban ou sur la mini-barre d’outils, vous pouvez utiliser les boutons « Réduire les
Format Monétaire
Par défaut, Excel affiche un nombre de ce format avec deux décimales, séparateur de milliers
et devise. Le symbole de la devise est aligné contre le bord droit de la cellule.
Si la saisie précise la devise par défaut (par exemple si on saisit 219,50 € et que l’euro a été
20
EXCEL avancé
défini comme la devise par défaut), le format monétaire est automatiquement appliqué à la cellule.
Dans la fenêtre « Format de cellule », divers types d’affichage sont proposés en fonction
du paramètre régional choisi.
Dans le groupe Nombre, le menu déroulant de la zone de saisie propose les deux types de date : « Date
courte » et « Date longue ». Le format « Date longue » affiche également le jour de la date.
Format Pourcentage
Format Fraction
Le dénominateur de la fraction peut être précisé, soit en nombre de chiffres (1,2 ou 3), soit
par une valeur exacte (par exemple demis ou quarts).
Exemple : 2,581 peut être affiché avec un dénominateur
d’un chiffre : 2 4/7
ou de deux chiffres : 2 43/74
ou de trois chiffres : 2 104/179.
A chaque fois, Excel affiche la fraction la plus proche de la valeur saisie. Le
format prédéfini d’Excel affiche un dénominateur d’un seul chiffre.
Format Scientifique
En notation scientifique, un nombre positif s’affiche comme produit d’un nombre décimal
compris entre 1 et 10 exclus, et une puissance de 10.
Dans le format Scientifique, la puissance de 10 est affichée sous la forme de la lettre E suivie
de l’exposant.
Exemples :
On a 123456 = 1,23456 * 105. Le nombre 123456 sera affiché 1,235E05 (s’il a été paramétré
trois décimales).
On a 0,057 = 5,7 * 10-2. Le nombre 0,057 sera affiché 5,700E-02 (toujours avec l’option
trois décimales).
21
EXCEL avancé
Format Texte
Format Spécial
Format personnalisé
Un format personnalisé comprend une à quatre parties, qui sont séparées par des points-
virgules.
Si le format comprend :
Alignement et orientation
L’alignement concerne la disposition du texte dans la cellule. Le mot « texte » est pris dans
son sens le plus large, désignant une suite de caractères quelconques.
22
EXCEL avancé
Seront utilisés :
- Sur le ruban, le groupe Alignement de l’onglet Accueil ;
- La fenêtre « Format de cellule », à l’onglet Alignement.
L’alignement par défaut est l’alignement Standard : un nombre ou une date est aligné à
droite (contre le bord droit, sans retrait) ; un texte est aligné à gauche (contre le bord gauche, sans
retrait).
Exemples :
250310 25/03/10
Texte sans retrait Texte avec retrait gauche
Orientation du texte
Par défaut, le texte est aligné horizontalement, donc avec un angle nul par rapport à
l’horizontal.
Seront utilisés :
- Le bouton Orientation du groupe Alignement ;
- La zone « degrés » de la fenêtre « Format de cellule ».
« Ajuster »
En cochant cette case dans la fenêtre « Format de cellule », la taille des caractères est
réduite, s’il y a besoin, au fur et à mesure de la saisie. La cellule garde les mêmes
dimensions.
23
EXCEL avancé
Ces deux options visent à afficher entièrement un long texte dans la cellule. En
choisissant la 1ère option, on augmente la hauteur de ligne. En choisissant la 2ème option, on
réduit la taille des caractères.
Dans la fenêtre « Format de cellule », cette option est souvent combinée avec un
alignement Centré pour disposer un titre. Si plusieurs cellules contiennent une valeur avant la
fusion, seule la valeur de la cellule supérieure gauche est conservée.
Sur le ruban, dans le groupe Alignement, le bouton « Fusionner et centrer » aboutit
directement au même résultat. Ce bouton est également présent sur la mini-barre d’outils.
Police
Dans le groupe Police, on peut directement choisir le type, la taille et la couleur des
caractères (A), ainsi que le style (gras G, italique I, souligné S). Dans la liste de choix, les deux
premiers types de police correspondent à ceux utilisés par défaut, dans le classeur en cours,
pour les en-têtes et le corps (caractères saisis dans les cellules).
Les boutons A et A permettent d’augmenter ou de réduire, à chaque clic, d’un point la
taille des caractères.
Dans la fenêtre, sont également proposés des types de soulignement, des couleurs
personnalisées, ainsi que les attributs Barré, Exposant et Indice.
Bordure
Dans le groupe Police, le bouton de bordures (son icône et son info-bulle dépendent
de la dernière commande appliquée) dispose d’un menu déroulant.
Tandis que le bouton de la mini-barre d’outils propose des bordures prédéfinies, le bouton
du ruban propose en plus diverses commandes dans la rubrique « Traçage des bordures », qui
permettent de tracer des bordures, le pointeur prenant l’aspect d’un crayon.
24
EXCEL avancé
Définissez :
- Le style et la couleur de la ligne, dans le cadre gauche ;
- Puis les bordures souhaitées : cliquez sur un ou plusieurs boutons. Vous pouvez
également définir des bordures en cliquant dans le cadre de l’aperçu.
Remplissage
Styles prédéfinis
Au lieu d’utiliser plusieurs fois les mêmes éléments de mise en forme, en les appliquant
successivement, on peut les définir dans un « style », qu’il suffira ensuite d’appliquer d’un seul
clic.
On peut commencer par appliquer le style souhaité à une cellule, que l’on sélectionne. On
retrouvera ainsi ses paramètres de style dans la fenêtre « Format de cellule ».
25
EXCEL avancé
Pour afficher la fenêtre « Style » : activez le bouton « Styles de cellules » > Nouveau
style de cellule.
- Attribuez un nom au style ;
- Affichez la fenêtre « Format de cellule » en cliquant sur le bouton « Format ».
Définissez les paramètres du style : Nombre, Alignement, Bordure, Police...
Validez.
Le nouveau style apparaît dans la galerie des styles, dans la rubrique « Personnalisé ».
Excel 2010 permet de définir des critères portant sur des valeurs appartenant à d’autres
feuilles du classeur actif.
26
EXCEL avancé
Barres de données
Quand on applique ce type de règle, chaque cellule sélectionnée comporte une barre de
couleur, de longueur proportionnelle à sa valeur.
Par rapport à Excel 2007, Excel 2010 offre davantage de possibilités de mise en forme des barres de
données.
Nuances de couleurs
Quand on applique ce type de règle, les cellules sélectionnées acquièrent des remplissages
de couleurs différentes en fonction de leur contenu.
Jeuxd’icônes
Quand on applique ce type de règle, chaque cellule sélectionnée contient une icône,
fonction de son contenu. Les jeux d’icônes proposés contiennent 3, 4 ou 5 icônes.
Par rapport à Excel 2007, Excel 2010 dispose de plus de jeux d’icônes, facilement combinables.
Barres de données, Nuances de couleurs et Jeux d’icônes :
Modifier une règle de mise en forme :
Pour modifier le paramétrage de la représentation utilisant le type « Base de données »,
« Nuances de couleurs » ou « Jeux d’icônes », utilisez la fenêtre « Modifier la règle de mise en
forme ».
Procédez ainsi :
- Sélectionnez une cellule à laquelle est appliquée la mise en forme conditionnelle
à appliquer.
- Sous l’onglet Accueil, dans le groupe Style, activez le bouton « Mise en forme
conditionnelle » > « Gérer les règles ».
- Sélectionnez la règle souhaitée, puis cliquez sur « Modifier la règle ».
Renseignez la fenêtre « Modifier la règle de mise en forme ».
27
EXCEL avancé
5. MODIFICATION DU THEME
Couleurs
Polices
Cliquez sur le bouton « Polices ». Chaque jeu de polices comporte deux polices : l’une
pour les cases d’en-tête, l’autre pour le corps (c’est-à-dire les cellules).
28
EXCEL avancé
Effets
Cliquez sur le bouton « Effets ». Les effets s’appliquent sur des objets graphiques. On ne
peut pas créer de jeux d’effets personnalisés.
Il est également possible de créer ses propres thèmes, utilisables sur d’autres classeurs.
On choisit un thème, on effectue les modifications souhaitées (couleurs, polices, effets),
puis onl’enregistre : activez le bouton « Thèmes » > Enregistrer le thème actif.
Dans la fenêtre « Enregistrer le thème actif », attribuez un nom au nouveau thème.
Le nouveau thème apparait dans la liste des thèmes, sous le titre « Personnalisé ».
Procédure
Réglage de la largeur
Placez le pointeur sur la bordure droite de la case d’en-tête de la colonne à modifier. Concernant
la ligne, le réglage par cliqué-glissé s’effectue sur la bordure inférieure de la case d’en-tête.
Quand le pointeur a la forme d’une double-flèche, cliquez-glissez jusqu’à obtenir la
largeur désirée. Une info-bulle indique la largeur.
Ajustement automatique
Pour que la largeur de la colonne s’ajuste au contenu, double-cliquez sur la bordure droite de la
case d’en-tête. Si vous supprimez ensuite les textes qui « dépassent », la colonne
conservera la largeur acquise.
1. VOLET PRESSE-PAPIERS
Par défaut, pour que les éléments soient stockés dans le Presse-papiers, il est nécessaire que
celui-ci soit affiché. Sinon seul est stocké le dernier élément prêt à être collé (il est entouré d’une
bordure clignotante) et cet élément disparaît du volet dès qu’il ne peut plus être collé (disparition
de la bordure clignotante).
Si vous souhaitez que les éléments soient stockés dans le Presse-papiers quand ce dernier
n’est pas affiché : affichez le Presse-papiers (voir ci-dessous), ouvrez le menu Options et cliquez
sur « Copier sans afficher le Presse-papiers Office ».
2. DEPLACER
Avant de cliquer-glisser pour déplacer ou copier des cellules, le pointeur doit avoir la
forme d’une croix fléchée.
Avec la souris
Après sélection, placez le pointeur sur le contour (pointeur en croix fléchée). Cliquez-
glissez jusqu’à l’emplacement désiré.
Le déplacement peut également être réalisé avec la commande Couper (§ ci-après).
- Faites un clic droit sur la plage > Couper (ou Ctrl + X, ou encore dans le groupe
« Presse-papiers », activez le bouton Couper).
La sélection affiche une bordure clignotante. Cette bordure disparaîtra après collage,
ou en double-cliquant dans une cellule, ou encore en tapant sur la touche Echap (Esc).
-Sélectionnez la 1ère cellule (en haut à gauche) de la plage de destination.
- Clic droit > Coller (ou Ctrl + V ; ou dans le groupe « Presse-papiers », activez le bouton
Coller).
30
EXCEL avancé
- Sur une autre feuille du même classeur : avec la souris, faites Alt + cliqué-glissé sur le
contour de la sélection, jusqu’à l’onglet de la feuille de destination, puis continuez
jusqu’à l’emplacement souhaité sur la nouvelle feuille.
- Ou encore sur une feuille d’un autre classeur : affichez les deux feuilles en taille réduite
(ouvrez les classeurs, puis cliquez-glissez sur la barre de titre pour les disposer à convenance, ou
bien : sous l’onglet Affichage, dans le groupe Fenêtre, cliquez sur « Réorganiser tout » et
choisissez une option d’affichage).
Pour déplacer les valeurs, utilisez l’une des méthodes vues précédemment.
3. COPIER
Copier, puis coller la copie conforme
4. OPTIONS DE COLLAGE
Il existe diverses autres options de collage, proposées juste avant ou juste après le collage.
31
EXCEL avancé
Après avoir sélectionné une plage de cellules, puis avoir effectué un « Couper » ou un
« Copier », cliquez sur la première cellule (en haut à gauche) de la plage de destination.
- Dans le menu contextuel qui apparaît après un clic droit sur la sélection de la 1 ère
cellule de la plage de destination.
En pointant sur « Collage spécial », on obtient les mêmes options que celles
proposées dans le bouton Coller du Presse-papiers.
5. TRI RAPIDE
Pour trier une colonne de cellules (le tri d’une ligne nécessite l’ouverture de la fenêtre
« Tri », voir ci-après), indiquez d’abord cette colonne :
-Cliquez sur une cellule quelconque de la colonne : avec cette méthode, le tri
s’arrêtera à la première cellule vide trouvée.
-Ou bien sélectionnez la colonne de valeurs à trier. Dans un ordre croissant, les cellules
vides seront placées en dernier.
Sous l’onglet Accueil, dans le groupe « Edition », activez le bouton « Trier et filtrer »,
puis choisissez l’option souhaitée. Pour afficher la fenêtre « Tri », cliquez sur l’option « Tri
personnalisé ».
Ou bien : sous l’onglet Données, dans le groupe « Trier et filtrer », activez le bouton
« Trier de A à Z » ou le bouton « Trier de Z à A » . Pour afficher la fenêtre « Tri
», cliquez sur le bouton « Trier ».
En ordre croissant, les chiffres sont placés avant les lettres. Pour les autres caractères dont
vous auriez besoin de connaître l’ordre : saisissez-les sur une colonne, sélectionnez-les, puis
triez-les.
Fenêtre « Tri »
Elle est principalement utilisée pour un tableau de données (cf. chapitre 12, § 1 Tableaux
de données). Elle permet de classer les données sur plusieurs niveaux de critères.
Exemple d’un tableau comportant deux colonnes : noms et prénoms.
Un premier critère de classement peut être l’ordre alphabétique des noms.
On peut ajouter un second critère (ou « niveau »), l’ordre alphabétique des prénoms
(intéressant quand il y a des homonymes).
32
EXCEL avancé
Pour modifier la mise en page, on utilise les commandes du groupe « Mise en page » de
l’onglet « Mise en page ».
En cliquant sur le lanceur de ce groupe, ou bien à partir du mode « Aperçu avant
impression » (en cliquant sur « Mise en page »), on affiche la fenêtre « Mise en page ». Elle
comporte les quatre onglets : Page, Marges, En-tête/Pied de page et Feuille.
1. ZONE D’IMPRESSION
Par défaut, la zone d’impression d’une feuille est sa zone active, c’est-à-dire la plus petite
plage de cellules, commençant par la cellule A1, et contenant toutes les données de la feuille.
Pour visualiser et si besoin redéfinir la zone d’impression, il existe diverses méthodes :
Passez en mode d’affichage « Aperçu des sauts de page ». La zone d’impression de la feuille
apparaît sur fond blanc et délimitée en bleu. Pour la modifier, cliquez-glissez sur son contour.
Pour annuler les modifications, afin que la zone d’impression redevienne la zone active : clic droit
> Rétablir la zone d’impression.
Dans la fenêtre « Mise en page », sous l’onglet Feuille, cliquez dans la zone « Zone
d’impression », puis sélectionnez sur la feuille la plage de cellules à imprimer. Les références de
la plage de cellules sélectionnée apparaissent alors dans la "Zone d'impression".
Le bouton avec la flèche rouge, en fin de zone de saisie, permet d’agrandir ou de
réduire la fenêtre à convenance.
Validez. Sur la feuille, la plage qui a été sélectionnée (devenue nouvelle zone
d’impression) est entourée de traits pointillés noirs.
33
EXCEL avancé
2. SAUTS DE PAGE
Un saut de page peut être automatique, ou bien paramétré (on l’insère à l’endroit
souhaité). Il est en effet possible d’insérer un saut de page, horizontal ou vertical.
Vous pouvez visualiser les sauts de page en mode d’affichage « Aperçu des sauts de
page » . Des traits bleus les matérialisent. Ils sont déplaçables par cliqué-glissé.
Quand ils sont en pointillé, il s’agit de sauts de page automatiques. Quand ils sont en
continu, il s’agit de sauts de page paramétrés.
Pour insérer :
Un saut de page horizontal : sélectionnez la ligne au-dessus de laquelle on souhaite
l’insérer. Dans le groupe « Mise en page », cliquez sur le bouton « Saut de page » >
« Insérer un saut de page ».
Un saut de page vertical : la méthode est similaire. Sélectionnez la colonne à
gauche de laquelle on insérera le saut de page.
Deux sauts de page horizontal et vertical, simultanément : la méthode est similaire.
Sélectionnez cette fois la cellule au-dessus de laquelle sera inséré le saut horizontal,
et à gauche de laquelle sera inséré le saut vertical.
Un en-tête ou un pied de page peuvent par exemple comprendre un nom, une adresse, la
date, le numéro de page, également un logo, une image, une photo.
Par défaut, le même en-tête et le même pied de page apparaissent sur toutes les pages de
la feuille.
Pour afficher l’onglet Création des « Outils des en-têtes et pieds de page », activez le
bouton « En-tête et pied de page » du groupe Texte, sous l’onglet Insertion. On utilisera ici les
commandes de cet onglet Création. La feuille s’affiche en mode « Mise en page » .
Vous pouvez également créer un en-tête ou un pied de page personnalisé. En-tête et pied de
page comportent chacun trois zones de saisie.
34
EXCEL avancé
Vous pouvez insérer des éléments à partir des commandes du groupe « Eléments en-tête
et pied de page ».
L’insertion d’un élément se traduit par l’affichage d’un code de commande de la forme :
&[élément].
Exemple
Si on saisit : Le (espace) (bouton « Date actuelle ») (espace) à (espace) (bouton « Heure
actuelle »), le code est : Le &[Date] à &[Heure].
L’en-tête de chaque page pourra être : Le 08/06/2010 à 20:35.
Pour modifier la mise en forme des textes, utilisez les commandes du groupe « Police » de
l’onglet Accueil.
Pour valider l’en-tête ou le pied de page, il suffit de cliquer sur la feuille de calcul.
D’autres options sont disponibles à l’onglet «Mise en page » du ruban, ainsi que dans la
fenêtre « Mise en page ». Par exemple :
Impressions des lignes ou colonnes de titres (fenêtre « Mise en page », onglet « Tailles »)
La procédure est similaire pour les lignes ou pour les colonnes.
Prenons l’exemple de lignes à répéter. Cliquez dans la zone « Lignes à répéter en haut »,
puis sélectionnez sur la feuille la ou les lignes à répéter en haut de chaque page.
Marges
Il y a 6 marges à définir : 4 marges pour le texte de la feuille de calcul, une marge d’en-
tête et une marge de pied de page. On peut utiliser les marges prédéfinies proposées (bouton
« Marges » du groupe « Mise en page »), ou bien définir des marges personnalisées (fenêtre
« Mise en page », onglet « Feuille »).
Centrage
Le texte peut être centré horizontalement et verticalement (fenêtre « Mise en page »,
onglet « Marges).
Orientation
Elle peut être horizontale (Paysage) ou verticale (Portrait) (bouton « Orientation » du
groupe « Mise en page » ou fenêtre « Mise en page » à l’onglet « Page »).
Format de papier
Il existe des formats prédéfinis (bouton « Taille » du groupe « Mise en page »). On peut
en définir d’autres (fenêtre « Mise en page », onglet « Page »).
Quadrillage, en-têtes des lignes et des colonnes
Cochez si nécessaire les cases Afficher et/ou Imprimer, présentes dans le groupe
« Options de la feuille de calcul » (également dans la fenêtre « Mise en page », à l’onglet
« Feuille »).
Nombre de pages, ou bien changementd’échelle (groupe « Mise à l’échelle » ou fenêtre
« Mise en page » à l’onglet « Page »)
Soit vous choisissez le nombre de pages sur la largeur, ainsi que le nombre de
pages sur la hauteur, que contiendra chaque page imprimée,
Soit vous laissez l’option « Automatique » sur les deux zones précédentes, et
vous définissez un pourcentage de réduction (< 100%) ou d’agrandissement
(> 100%) dans la zone « Mettre à l’échelle ».
35
EXCEL avancé
Le signe égal indique qu’un résultat doit être donné, contrairement à une saisie simple, sans
résultat attendu.
Il n’est pas faux d’écrire dans une cellule = 3, mais cela n’a aucun intérêt. Autant écrire
directement 3.
En revanche, on doit écrire = 5 + 2, si on veut obtenir le résultat de la somme après validation
de la saisie. Si on écrit seulement 5 + 2, on aura toujours l’expression 5 + 2 après validation.
Le pavé numérique d’un ordinateur ne contient pas de signe égal. Remplacez-le par le
signe +, ou par le signe – si la formule débute par un nombre négatif.
Pour être plus rapide, vous pouvez donc écrire une référence de colonne, un nom de fonction
ou un nom attribué en minuscules.
Affichage
Après validation (tapez Entrée ou cliquez dans une autre cellule), le résultat d’une
formule apparaît dans la cellule.
Cette cellule étant sélectionnée, la formule saisie apparaît dans la barre de formule.
Pour afficher toutes les formules de la feuille de calcul :
- Dans le groupe « Audit de formules », activez le bouton « Afficher les formules ».
- Ou bien, appuyez sur : Ctrl + touche guillemets (utilisez à nouveau ces touches pour
masquer les formules).
Si une formule est longue, pour que son affichage soit plus lisible, insérez un ou plusieurs
sauts de ligne : Alt + Entrée.
Indépendance ou dépendance
Une formule est indépendante lorsqu’elle n’utilise pas la valeur d’une autre cellule
(exemple = 8 * 5 - 6).
Dans le cas contraire, on dit qu’elle est dépendante (exemple = A3 + B1). On intègre
communément des contenus de cellules dans les formules.
Les fonctions
Une formule peut utiliser une ou plusieurs fonctions, relatives à divers domaines.
36
EXCEL avancé
1. OPERATEURS
Opérateurs de calcul
Opérateur alphanumérique
L’esperluette & est l’opérateur permettant de concaténer (c’est-à-dire lier) des chaînes de
caractères.
Exemple : saisissez Julie dans A1, Quiberon dans B1, puis dans la cellule C1 la formule
suivante : =A1&« habite à »&B1. La cellule C1 affiche : Julie habite à Quiberon. Ne pas
oublier le signe égal, il s’agit d’une formule.
37
EXCEL avancé
Référence relative
Comme son nom l’indique, elle est relative. Une formule faisant appel à une cellule avec sa
référence relative, mémorise la position de cette cellule par rapport à celle dans laquelle la formule
est saisie.
Exemple : dans la cellule C5, si on saisit =A4, Excel mémorise que A4 désigne la valeur
de la cellule située 2 colonnes avant (colonne C à colonne A) et une ligne au-dessus (ligne 5 à
ligne 4).
Ainsi, si on copie la cellule C5 en D8, la valeur indiquée en D8 sera celle de la cellule située
2 colonnes avant (colonne B) et une ligne au-dessus (ligne 7), ce qui correspond à la cellule B7
(et non la valeur de C5).
Recopie verticale
Sélectionnez la première cellule. Puis cliquez-glissez sur la poignée (le petit carré noir
en bas à droite de son contour). Pendant le cliqué-glissé, le pointeur prend la forme d’une croix
noire .
La formule de la première cellule est recopiée, on dit aussi « étendue », jusqu’à la
dernière cellule du cliqué-glissé.
Exemple
Saisissez des nombres sur chaque cellule de la plage
A1:B4.
Dans la cellule C1, saisissez =A1*B1 et validez.
Excel mémorise que A1*B1 est le produit des deux cellules
précédentes à gauche.
Sélectionnez C1. Par cliqué-glissé sur sa poignée, étendez la formule jusqu’en C4.
Comme la cellule C1, les cellules C2 à C4 contiendront le produit des deux cellules
précédentes. On obtient, avec les chiffres pris dans l’exemple : 8, 10, 12.
Pour étendre plus rapidement une formule jusqu’à la cellule « d’en bas », double-cliquez
sur la poignée de la cellule à recopier.
Dans l’exemple précédent, supprimez les valeurs des cellules C2 à C4 (sélectionnez la
plage C2:C4, puis appuyez sur la touche Suppr).
Sélectionnez la cellule C1, puis double-cliquez sur la poignée. Vous obtenez les mêmes
résultats que précédemment dans les cellules C2 à C4.
38
EXCEL avancé
Recopie horizontale
Référence absolue
Quand la référence à une cellule est absolue, il convient de le préciser sous la forme :
$colonne$ligne (procédé mémo : la référence au dollar est absolue…). Dans ce cas, même si la
cellule est recopiée, la valeur reste celle de la cellule d’origine. Le symbole $ fige la colonne ou
la ligne qui le suit.
Exemple
E F G
1 5 2
2 =E1
3 =$E$1
Référence mixte
Il est possible d’indiquer une colonne fixe ($ColonneLigne) et une ligne relative, ou vice
versa (Colonne$Ligne). Exemples : $A8 et A$8.
Pour passer de l’écriture d’un type de référence à un autre, il est pratique d’utiliser la
touche F4.
Exemple
Saisissez dans une cellule =A4.
Appuyez sur F4. La référence devient $A$4.
Appuyez à nouveau sur F4. On obtient A$4. Puis $A4. Puis A4.
39
EXCEL avancé
Exemple
Prenons le cas d’un classeur à trois feuilles : Feuil1, Feuil2 et Feuil3.
Etant sur Feuil2 ou sur Feuil3, pour faire appel à la cellule T9 de la Feuil1, on écrit :
=Feuil1!T9
Utilisation de plages de cellules ayant les mêmes références, situées sur des
feuilles différentes du même classeur
On indique le nom des feuilles, suivi d’un point d’exclamation, puis de la référence des
cellules.
Exemple
Si on saisit dans une cellule la formule =somme(Feuil1:Feuil3!G2:H8), on obtient la
somme des valeurs de toutes les cellules des plages G2:H8 affichées sur les trois feuilles.
Un nom peut être attribué à une cellule, à une plage de cellules, à un ensemble de plages
de cellules, également (plus rarement) à une formule ou à une constante.
Il constitue une référence absolue (par opposition à une référence relative, voir § 2).
Un nom est utilisable dans tout le classeur.
La liste de tous les noms attribués est affichée dans la fenêtre « Gestionnaire de noms ».
Pour l’afficher, activez le bouton du même nom, dans le groupe « Noms définis », ou bien, plus
rapidement, faites : Ctrl + F3.
Chaque nom qui a été attribué apparaît avec sa valeur, les références des cellules concernées,
son étendue (c’est-à-dire sa zone d’utilisation, classeur ou feuille), éventuellement son
commentaire. Le bouton « Filtrer », avec menu déroulant, permet de filtrer les noms
souhaités.
Excepté ceux faisant référence à des constantes ou à des formules, les noms apparaissent
40
EXCEL avancé
également dans la liste déroulante de la zone Nom, par ordre alphabétique. Cliquez sur l’un d’eux
permet de sélectionner l’ensemble des cellules qu’il désigne.
Cette méthode est rapide. Sélectionnez les cellules dont vous souhaitez nommer l’ensemble.
Puis saisissez un nom dans la zone Nom (zone gauche au-dessus de la grille). Validez par Entrée.
Attributiond’unnomàuneconstante
Dans la fenêtre « Nouveau nom » (utilisée ci-dessus), dans la zone « Fait référence à »,
saisissez la constante, sans le signe égal, s’il s’agit d’une constante autrequ’un texte.
Si la constante est un texte, saisissez le texte précédé du signe égal et entouré de guillemets.
Exemple : =«Le total est ».
Saisie directe
Un nom peut être saisi directement dans une formule. Il a l’avantage d’être plus explicite
41
EXCEL avancé
qu’une référence colonne ligne. Exemple : attribution du nom TauxTVA, puis saisie de la
formule =D6*(1+TauxTVA).
Insertion
On peut également insérer le nom dans une formule en utilisant le menu déroulant du bouton
« Utiliser dans la formule », dans le groupe « Noms définis » (groupe Formules).
Reprenons l’exempleprécédent. Saisissez =D6*(1+
Activez le bouton « UtiliserDsFormule » (utiliser dans la formule). Dans le menu
déroulant, choisissez TauxTVA. Le nom est alors inséré dans la formule. Fermez la parenthèse.
Autre exemple
Attribuez le nom Totalité à une plage de cellules. Saisissez dans une cellule la
formule =somme(Totalité). Le nom Totalité peut être intégré dans la formule par saisie directe
ou par insertion. On obtient la somme de toutes les valeurs des cellules de Totalité.
Collage de noms
Remplacer les références colonne/ligne par un nom, dans les formules déjà saisies
Après définition d’un nom, on peut vouloir l’appliquer aux formules déjà saisies :
Ouvrez le menu déroulant du bouton « Définir un nom » (groupe « Noms définis », onglet
Formules) > Appliquer les noms.
Dans la fenêtre « Affecter un nom », sélectionnez les noms à appliquer (Ctrl + clic).
6. DATE ET HEURE
Date
Excel enregistre une date sous la forme d’un nombre, appelé « numéro de série », calculé
à partir du 1er janvier 1900. Par exemple, le 12/09/2008 correspond au numéro de série 39703,
ce qui signifie que 39703 jours se sont écoulés depuis le 1er janvier 1900.
Heure
La transformation par Excel d’une date ou d’une heure en nombre, permet d’effectuer des
calculs avec des données de ce format.
Par exemple, la formule =DATEDIF(C2;C3;"d") renvoie le nombre de jours (d initiale
de « days ») entre deux dates, dont la première est saisie dans C2 et la deuxième (plus tardive)
dans C3.
Il n’est pas possible de mettre directement des dates à la place des deux premiers arguments,
car en tant qu’arguments elles ne seraient pas converties en numéros de série. Or la fonction
DATEDIF requiert en arguments des numéros de série.
Audit de formules
Liaisons
Il peut être utile de visualiser les liaisons existantes avec d’autres cellules.
Ces liaisons sont matérialisées par des flèches. Pour supprimer l’affichage des flèches,
activez le bouton « Supprimer les flèches ».
Antécédents
Sélectionnez la cellule contenant la formule dont on recherche les antécédents. Activez le
bouton « Repérer les antécédents ». Des flèches pointent sur la cellule contenant la formule, en
provenance des cellules utilisées dans la formule.
Pour supprimer l’affichage de ces flèches, ouvrez le menu déroulant du bouton
« Supprimer les flèches » > Supprimer les flèches des antécédents.
Dépendants
Sélectionnez la cellule dont on recherche les dépendants. Activez le bouton « Repérer les
dépendants ». Des flèches partent de la cellule sélectionnée et pointent sur les formules qui
l’utilisent.
Pour supprimer l’affichage de ces flèches, ouvrez le menu déroulant du bouton
« Supprimer les flèches » > Supprimer les flèches des dépendants.
Exemple
Saisissez une valeur numérique dans A10, puis saisissez dans B8 la formule =A10+9 et dans C12 la
43
EXCEL avancé
formule =7*A10.
B8 et C12 sont dépendants de A10. Leur valeur dépend de celle de A10.
Sélectionnez A10 et activez le bouton « Repérer les dépendants ». Deux flèches partent de A10, elles
pointent l’une vers B8, l’autre vers C12.
Evaluation d’uneformule
Suividelavaleurd’unecellule
Erreurs
Messagesd’erreurs
Après validation d’une saisie, lorsqu’Excel repère une erreur, la cellule affiche un bref
message commençant par un dièse # et indiquant sa nature.
Exemples de valeurs d’erreurs : #VALEUR !, #NOM ?, #REF !, #N/A (valeur non
disponible, Not Available).
Pour afficher la fenêtre « Vérification des erreurs », activez le bouton du même nom.
Cette fenêtre indique les cellules contenant une erreur (activez les boutons « Précédent »
et « Suivant »), et elle peut ainsi aider à la résoudre.
« Aide sur cette erreur » : permet d’accéder à l’aide en ligne.
« Afficher les étapes du calcul » : affiche la fenêtre « Evaluation de formule », étudiée ci-
dessus au paragraphe Evaluation d’une formule.
« Ignorerl’erreur » : l’erreur ne sera plus indiquée dans cette fenêtre, à moins que ne soit
activée l’option « Rétablir les erreurs ignorées ». Pour activer cette option, cliquez sur
« Options » ; dans la fenêtre « Options Excel », choisissez la catégorie « Formules », puis à la
rubrique « Vérification des erreurs », activez « Rétablir les erreurs ignorées ».
« Modifier dans la barre de formule » : place le curseur dans la barre de formule pour
modifier la formule.
« Options » : affiche la fenêtre « Options Excel ».
Marque et balise
44
EXCEL avancé
On peut paramétrer Excel pour qu’une cellule contenant une erreur affiche une marque (il
s’agit d’un petit triangle à l’angle supérieur gauche), ainsi qu’une balise quand elle est
sélectionnée. Le menu déroulant de la balise propose des options similaires à celles de la
fenêtre « Vérification des erreurs ».
Dans la fenêtre « Options Excel », dans la catégorie « Formules », cochez la case
« Activerlavérificationdeserreursd’arrière-plan ».
Référence circulaire
Lorsqu’une cellule fait référence à son propre résultat, on dit qu’il y a référence circulaire.
Pour afficher la liste des cellules contenant une référence circulaire, ouvrez le menu déroulant
du bouton « Vérification des erreurs » > Références circulaires.
Il y a arrêt des calculs, dès que le nombre d’itérations est atteint ou dès que l’écart entre deux
valeurs est inférieur ou égal à l’écart fixé. Si l’expression « Calculer » s’affiche alors sur la barre
d’état, cela signifie que le nombre d’itérations a été atteint, sans que l’écart entre deux valeurs soit
inférieur ou égal à l’écart fixé.
45
EXCEL avancé
Excel 2010 propose plus de 300 fonctions, utilisables dans les formules.
L’onglet« Formules » contient le groupe « Bibliothèque de fonctions ».
Si la fonction est en début de formule, elle doit être précédée du signe égal.
Exemple : La formule =MAINTENANT() renvoie la date et l’heure courantes.
Si des signes dièses # s’affichent, élargissez la colonne (cliquez-glissez sur le bord droit de la case d’en-tête
de la colonne).
Exemple
Sélectionnez la plage F1:G3. Saisissez un nombre dans
chaque cellule, validez après chaque saisie en appuyant sur la
touche Tab .
Dans la cellule H1, saisissez la formule =«La somme est »&SOMME(F1;G1). Validez.
H1 affiche : La somme est 3.
Les références étant relatives dans cet exemple, on peut recopier la formule par cliqué-
glissé sur la poignée de la cellule H1 sélectionnée, jusqu’en H3 (ou plus rapidement en double-
cliquant sur la poignée de H1).
H2 affiche : La somme est 7, et H3 affiche : La somme est 11.
1. ARGUMENTS
Sauf exceptions, une fonction requièrent des données, appelées arguments, pour retourner
un résultat.
Les arguments sont mis entre parenthèses. On place des parenthèses même en l’absence
d’argument, afin d’indiquer qu’il s’agit d’une fonction (et non d’un nom de cellule).
Quand il y a plusieurs arguments, ils doivent être séparés par un point-virgule.
46
EXCEL avancé
Pour utiliser une fonction dans une formule, trois cas se présentent :
- Soit, bien que sachant ce que vous voulez faire, vous ignorez le nom de la fonction
à utiliser. Il vous faut alors passer par la fenêtre « Insérer une fonction » (voir ci-
après « Pour rechercher, puis insérer une fonction »).
- Soit vous avez récemment utilisé la fonction, ou bien vous connaissez son thème et
vous saurez la retrouver dans la liste des fonctions de ce thème, activez alors le thème
correspondant de la Bibliothèque de fonctions.
- Soit vous connaissez le nom de la fonction. Vous pouvez alors directement le saisir,
une info-bulle vous aidera si nécessaire pour l’écriture des arguments (voir § 3
« Saisie directe » de ce chapitre).
47
EXCEL avancé
La fonction dotée de ses arguments est insérée dans la formule, et le résultat est affiché
dans la cellule.
3. SAISIE DIRECTE
Si vous connaissez le nom exact de la fonction que vous souhaitez insérer dans une
formule, vous pouvez le saisir directement.
Juste après la saisie de la première parenthèse ouvrante, une info-bulle affiche le nom de la
fonction et les arguments requis. Les arguments cités entre crochets sont facultatifs.
En cliquant sur le nom de la fonction, l’aide sur cette fonction apparaît.
En cliquant sur un argument, l’argument correspondant dans la cellule est mis en
évidence.
L’insertion des arguments peut être effectuée soit par saisie, soit par sélection de cellules.
Fermez la parenthèse, puis validez (tapez Entrée ou cliquez dans une autre cellule).
Le résultat de la fonction s’affiche dans la cellule.
4. EXEMPLES
Bien que ce bouton soit nommé « Somme automatique », son menu déroulant propose
également quatre autres fonctions, souvent utilisées : Moyenne, NB, Max et Min.
48
EXCEL avancé
La fonction SI
Définition
Une formule contenant la fonction SI est appelée une formule conditionnelle. Cette
fonction est très utilisée.
La fonction SI renvoie un résultat qui diffère selon qu’une expression, appelée « Test »,
est vraie ou fausse (expression dite logique) :
SI (Test; alors Résultat_si_Test_vrai; sinon Résultat_si_Test_faux)
Autres exemples
EXACT
EXACT(texte1;texte2)
La fonction renvoie la valeur VRAI si les deux textes spécifiés sont identiques, sinon elle
renvoie FAUX. La casse est considérée, la mise en forme est ignorée.
MAJUSCULE et MINUSCULE
MAJUSCULE(texte) et MINUSCULE(texte)
Chaque fonction convertit le texte passé en argument dans la casse spécifiée.
SUPPRESPACE
SUPPRESPACE(texte)
La fonction supprime dans le texte spécifié les espaces « de trop ».
NB.VIDE
NB.VIDE(plage)
La fonction compte le nombre de cellules vides dans la plage spécifiée.
NB.SI
NB.SI(plage;critère)
La fonction compte le nombre de cellules de la plage donnée, qui répondent au critère
spécifié.
Exemples
La formule =NB.SI(B2:C5;«=24 ») renvoie le nombre de cellules dont la valeur égale
24. Mettez bien les guillemets.
La formule =NB.SI(B2:C5;«>=»&D1) renvoie en résultat le nombre de cellules de la
plage B2:C5 dont la valeur est supérieure à celle de la cellule D1. Positionnez bien les
guillemets.
La formule =NB.SI(A1:B3 ; « =paris ») renvoie le nombre de cellules de la plage
indiquée en argument, qui contiennent la valeur Paris (quelle que soit la casse des lettres,
Excel ne distingue pas les majuscules des minuscules ).
50
EXCEL avancé
Une formule matricielle est une formule contenant une matrice, c’est-à-dire une plage de
cellules, utilisée dans un calcul.
La formule apparaît alors entre accolades dans la barre de formule, montrant ainsi qu’il
s’agit d’une formule matricielle.
2. EXEMPLES
Exemple simple
1. Sélectionnez la plage des cellules qui contiendront les résultats, soit H1:H3.
2. Dans la barre de formule, saisissez : =f1:f3+g1:g3
3. Validez en tapant les touches Ctrl + Maj + Entrée.
En affichant toutes les formules (tapez Ctrl + guillemets), la formule s’affiche dans
chaque cellule de la plage H1:H3.
Tapez de nouveau les touches Ctrl + guillemets pour masquer les formules.
3. CONTRAINTES PARTICULIERES
51
EXCEL avancé
Si par erreur, on a voulu modifier la valeur d’une cellule faisant partie d’une matrice, le
message « Impossible de modifier une partie de matrice » apparaît.
Appuyez sur le bouton Annuler situé dans la barre de formule.
Exemple
52
EXCEL avancé
Création et outils
Un graphique est efficace pour représenter, « faire parler » des données chiffrées.
Pour créer un graphique, procédez ainsi :
53
EXCEL avancé
Par défaut, la zone de graphique est placée sur la feuille où sont situées les données.
Elle peut être déplacée sur une autre feuille du classeur. Il s’agit d’un déplacement, non
d’une copie.
Cliquez sur la zone de graphique afin de la sélectionner.
Puis affichez la fenêtre « Déplacer le graphique » : sous l’onglet Création, dans le groupe
« Emplacement », activez le bouton « Déplacer le graphique ». Indiquez la feuille souhaitée.
Quel que soit son emplacement, le graphique reste lié aux données sources. Il est mis à
jour lors de la modification de ces données.
Si les valeurs d’une série de données sont d’un ordre de grandeur très différent de celui des
autres données (par exemple des valeurs exprimées en dizaines, quand les autres sont exprimées
en milliers), il est nécessaire que cette série ait son propre axe vertical.
Un second axe est qualifié d’axe « secondaire ».
Pour créer un axe secondaire : clic droit sur cette série (éventuellement, pour qu’elle soit
visible,
créez un autre graphique de type « Ligne ») > « Mettre en forme une série de données ».
Dans la fenêtre « Mise en forme des séries de données », dans la catégorie « Options des
séries », cochez la case « Axe secondaire ». Validez.
S’affichent la nouvelle courbe, ainsi que l’axe secondaire sur le côté droit.
Données sources
54
EXCEL avancé
Par défaut, les étiquettes des colonnes sont indiquées sur l’axe horizontal du graphique.
Pour que les étiquettes des lignes les remplacent : sélectionnez la zone de graphique. Puis
sous l’onglet Création, dans le groupe Données, activez le bouton « Intervertir
les lignes/colonnes ».
Exemple : sur l’axe horizontal, les mois sont remplacés par les moyens de transport.
Quel que soit son emplacement, sur la feuille contenant les données déjà représentées, ou
sur une autre feuille du classeur, une série de données peut être ajoutée aux données sources.
Après sélection de la zone de graphique, il existe deux méthodes pour ajouter une série :
« Copier/Coller »
Copiez les cellules de la série, puis collez-les dans la zone de graphique sélectionnée.
On peut copier une zone de graphique en image. Ses éléments ne seront plus modifiables.
Sélectionnez la zone de graphique. Faites un clic droit dessus > Copier.
Cliquez où vous souhaitez la coller. Faites un clic droit et cliquez sur l’option de collage
« Image » .
Dispositions prédéfinies
55
EXCEL avancé
Mise en forme
Styles prédéfinis
Sous l’onglet Création, dans le groupe « Styles du graphique », Excel propose des
styles en fonction du graphique.
Cliquez sur la flèche « Autres » permet d’accéder à la galerie de tous les styles proposés.
Une zone de graphique est constituée de divers éléments : axe, quadrillage, titre,
légende, série, zone de graphique, zone de traçage (incluse dans la précédente), etc.
Ces éléments sont modifiables séparément (exemple : cadre de la zone de
graphique).
Quand on pointe sur l’un d’eux, une info-bulle indique son nom.
Un clic droit sur un élément fait apparaître le menu propre à cet élément.
On peut notamment afficher la fenêtre concernant sa mise en forme, ou format.
Courbes de tendance
Comme son nom l’indique, une courbe de tendance sert à révéler la tendance des données.
Excel permet le traçage automatique d’une courbe de tendance.
Après sélection de la série, activez le bouton « Courbe de tendance » du groupe Analyse.
56
EXCEL avancé
En cliquant sur « Autres options de la courbe de tendance », vous pouvez renseigner la fenêtre
« Format de courbe de tendance ».
Diverses courbes de tendance sont proposées. Plusieurs peuvent être tracées pour une
même série.
Sur certains types de graphiques, il est possible d’ajouter des lignes de projection ou des
barres, afin de faciliter la lecture des données.
Après sélection de la série, activez le bouton « Lignes » ou le bouton « Barres haut/bas »
du groupe « Analyse ».
Barres d’erreur
Sur certains types de graphiques, il est possible d’ajouter des barres d’erreur.
Après sélection de la série, activez le bouton « Barresd’erreur » du groupe Analyse.
4. GRAPHIQUES SPARKLINE
Nouveauté d’Excel 2010, un sparkline (« spark » signifie étincelle) est un petit graphique
inséré dans une cellule, en arrière-plan (on peut donc écrire dessus).
Il permet de visualiser diverses caractéristiquesd’unesérie.
57
EXCEL avancé
Les graphiques étudiés au chapitre précédent font partie des objets graphiques.
Il est possible de placer d’autres types d’objets graphiques sur une feuille de calcul :
formes (cf. § 1), images (cf. § 2), diagrammes SmartArt (cf. § 3), WordArt (cf. § 4).
Pour placer un objet sur une feuille, on se sert sous l’onglet « Insertion », du groupe
« Illustrations » pour les trois premiers types d’objets, et du groupe « Texte » pour un
WordArt.
Faire un clic droit sur un objet ouvre un menu de commandes propres à ce type d’objet.
Double-cliquer sur un objet permet d’afficher l’onglet « Format » de ce type d’objet. Il
existe de nombreuses possibilités de mises en forme. Elles sont explicites et agréables à
tester ; nous n’en détaillerons qu’une partie.
Il y a de nombreux points communs avec les objets graphiques de Word (cf. chapitre 4 –
OBJETS GRAPHIQUES), également des différences importantes.
Il n’y a plus de notion d’habillage, ni d’ancrage à un paragraphe.
Sous Excel, un objet graphique est toujours un objet « flottant », posé sur la feuille.
Pour déplacer ou traiter divers objets simultanément, il est pratique de les grouper :
sélectionnez les objets (voir ci-après). Puis sous l’onglet « Format », dans le groupe
« Organiser », activez le bouton « Grouper ». Ce bouton permet ensuite de les dissocier.
Suite à une dissociation du groupe, pour regrouper à nouveau les objets : sélectionnez l’un
d’eux ayant appartenu au groupe, puis activez le bouton « Regrouper ».
Pour sélectionner un objet, cliquez dessus. S’il s’agit d’un objet faisant partie d’un
groupe, sélectionnez le groupe, puis cliquez sur l’objet.
Pour sélectionner plusieurs objets un par un : sélectionnez le premier, puis
Maj (Shift) + clic pour sélectionner chacun des objets suivants.
Pour sélectionner plusieurs objets voisins à la fois : sous l’onglet « Accueil », dans le
groupe « Edition », activez le bouton « Rechercher et sélectionner » > Sélectionner les objets.
Cliquez-glissez sur les objets à sélectionner. Pour terminer, désactivez le bouton « Sélectionner
les objets ».
Pour visualiser la liste des noms des objets présents dans la feuille de calcul, affichez le volet
« Sélection et visibilité » : sous l’onglet « Mise en page » ou sous l’onglet « Format » d’un
objet sélectionné, dans le groupe « Organiser », activez le bouton « Volet de sélection ».
Sélectionner des noms permet de sélectionner les objets correspondants. A droite du nom,
cliquer sur l’icône de l’œil (élargissez le volet si nécessaire en cliquant-glissant sur son
bord gauche) permet de masquerl’objet. Cliquez de nouveau dans la case pour afficher l’objet.
Les boutons fléchés et permettent de changer l’ordredesnoms.
Sur la feuille, on peut passer d’un objet à l’autre avec les touches Tab ou
Maj (Shift) + Tab, enfonctiondel’ordredesnoms affichés dans le volet.
58
EXCEL avancé
Déplacement
Pour déplacer un ou plusieurs objets : pointez sur la sélection (pointez sur son contour, s'il
s'agit d'une zone de texte). Quand le pointeur a l’aspect d’une croix fléchée, cliquez-glissez
jusqu’à l’emplacement souhaité.
Utilisez les flèches du clavier pour de petits déplacements de la sélection d’objet(s).
Redimensionner
Pour redimensionner un objet, sélectionnez-le, puis pointez sur l’une des poignées du
pourtour (rond, carré ou pointillé). Quand le pointeur a l’aspect d’une double flèche, cliquez-
glissez. En cliquant-glissant sur la poignée d’unangle, l’objet garde ses proportions.
Pour définir une taille précise, ouvrez l’onglet Format, et utilisez le groupe « Taille ».
Vous pouvez cliquer sur le lanceur du groupe pour afficher la fenêtre « Taille ».
Rotation
Alignement et distribution
Pour aligner ou distribuer des objets : sélectionnez-les, puis activez le bouton « Aligner »,
situé dans le groupe « Organiser » sous l’onglet « Format ».
Il s’agit d’aligner des objets l’un par rapport à l’autre, d’où la nécessité de sélectionner au
minimum deux objets.
Pour distribuer des objets, c’est-à-dire les disposer à égale distance les uns des autres, il
faut en sélectionner au minimum trois.
1. FORMES
Insertion de la forme
Cliquez sur la forme choisie. Sur la feuille, le pointeur prend la forme d’une croix
noire Cliquez pour insérer la forme standard.
Ou bien cliquez-glissez sur la feuille pour donner à la forme l’allure souhaitée.
59
EXCEL avancé
Si vous cliquez-glissez en appuyant sur la touche Maj (Shift), la forme restera régulière,
elle ne sera pas déformée. Exemple : une forme circulaire restera circulaire.
Si vous cliquez-glissez en appuyant sur la touche Ctrl, vous dessinerez la forme à partir
de son centre.
Poignée jaune
Après sélection, certaines formes affichent un losange jaune. Cette poignée est appelée
« poignéed’ajustement ». En cliquant-glissant dessus, on modifiel’apparencedel’objet.
Exemple
Voici une nouvelle forme obtenue après cliqué-glissé sur la poignée jaune du
cube de l’exemple précédent :
Ce sont les noms (lire les info-bulles sur passage du pointeur) de trois formes de la
catégorie « Lignes ».
Elles se distinguent des autres par leur tracé particulier.
Pour terminer le tracé avec les formes « Courbe » et « Forme libre », on double-clique.
Ou bien, pour obtenir une forme fermée, on clique sur le point de départ.
à en couper (supprimer) une partie. Pour rogner une image :
- Sélectionnez l’image.
- Sous l’onglet Format, dans le groupe Taille, activez le bouton
« Rogner ».
Des « poignées de rognage » (en noir) apparaissent sur l’image.
- Cliquez-glissez sur une poignée pour rogner l’image.
- Pour terminer, cliquez sur le bouton Echap (Esc).
« Rogner à la forme »
3. SMARTART
Vous pouvez saisir un texte soit directement dans le graphique, soit dans le volet qui
s’affiche en cliquant sur les flèches à gauche du graphique.
Quand le SmartArt ou l’une des formes qu’il contient, est sélectionné, les Outils SmartArt
sont accessibles, répartis sur deux onglets : Création et Format.
En utilisant l’onglet Création, on peut en particulier :
- Changer de disposition SmartArt : après sélection du SmartArt, cliquez sur une autre
disposition de la galerie.
- Ajouter une forme au SmartArt : sélectionnez la forme à côté de laquelle vous souhaitez
60
EXCEL avancé
ajouter une autre forme, puis cliquez sur le bouton « Ajouter une forme » dans le groupe
« Créer un graphique ».
Pour transformer une image en SmartArt : sélectionnez l’image. Puis dans le groupe
« Stylesd’images » (onglet « Format »), activez le bouton « Dispositiond’image ».
Choisissez la disposition SmartArt souhaitée. En passant le pointeur sur une disposition,
on obtient un aperçu de son effet sur l’image sélectionnée.
4. WORDART
WordArt est traduisible par « l’art du mot », c’est-à-dire un texte mis sous forme
artistique. Pour placer un WordArt sur la feuille, cliquez sur le bouton « WordArt » du groupe
« Texte », sous l’onglet « Insertion ». Sélectionnez l’effet souhaité, et saisissez le texte.
On peut appliquer au texte du WordArt les commandes du groupe « Police », sous l’onglet
« Accueil ».
Par défaut, un WordArt est inséré dans une forme rectangle.
Comme pour toute forme, on dispose de l’onglet « Format » des « Outils de Dessin » pour le
personnaliser.
61
EXCEL avancé
1. TABLEAUX DE DONNEES
Exemple
On peut sélectionner une colonne de la liste en pointant sur la bordure supérieure de sa case
d’en-tête, puis quand le pointeur se transforme en flèche noire, cliquez. La méthode est similaire
pour sélectionner une ligne (pointez sur la bordure gauche de sa case d’en-tête).
Le tableau reçoit une mise en forme prédéfinie.
L’intitulé de chaque colonne est doté d’un menu déroulant , permettant de trier et de
filtrer les données.
La dernière ligne est une ligne supplémentaire qui permet d’ajouter les données d’un nouvel
enregistrement. A sa droite, une poignée de dimensionnement permet d’agrandir ou de réduire le
tableau par cliqué-glissé.
Dès qu’on ajoute une donnée dans une cellule adjacente à la plage, après validation, sa
ligne ou sa colonne est intégrée dans le tableau (sauf si la donnée est saisie juste au-dessous d’une
ligne de totaux).
Le tableau est actif dès qu’une cellule est sélectionnée.
Plusieurs tableaux peuvent être créés sur une même feuille.
Création
62
EXCEL avancé
Après validation d’une donnée juste sous cette ligne, ou bien juste à droite du tableau, le
tableau s’agrandit automatiquement et une balise s’affiche. Elle propose les options :
« Annuler le développement automatique du tableau » : la nouvelle ligne (ou colonne) ne
sera pas intégrée au tableau.
« Arrêter le développement automatique du tableau » : toute nouvelle donnée adjacente au
tableau n’en fera pas partie. Pour rétablir le développement automatique du tableau, affichez la
fenêtre « Correction automatique » : ouvrez le menu Fichier > Options > Vérification > Options
de correction automatique ; cliquez sur le premier onglet de la fenêtre, et cochez la case « Inclure
de nouvelles lignes et colonnes dans le tableau ».
La dernière option affiche la fenêtre « Correction automatique ». C’est plus rapide…
Commandes relatives au tableau
Le tableau étant sélectionné, l’onglet « Création » des « Outils de tableau » propose des
commandes spécifiques au tableau.
Certaines sont accessibles après clic droit sur une cellule du tableau.
Précisions sur certaines fonctionnalités proposées:
Groupe « Propriétés »
- « Redimensionner le tableau » : pour indiquer la nouvelle plage de cellules, il est
rapide de la sélectionner.
On peut également cliquer-glisser sur la poignée du tableau.
Groupe « Outils »
- « Supprimer les doublons » : les lignes en double n’apparaissent qu’une fois. La
fenêtre « Supprimer les doublons » permet de spécifier les doublons ne portant que
sur certaines colonnes. Dans l’exemple, deux personnes habitent Caen. Si, dans
cette fenêtre, on ne laisse cochée que la colonne Ville, la deuxième personne habitant
Caen sera masquée.
- « Convertir en plage » : la liste est transformée en plage « normale ». Si elle est
présente, la ligne « Total » demeure.
- « Synthétiser avec un tableau croisé dynamique » : le tableau croisé dynamique fait
l’objet du chapitre 14 - LES TABLEAUX CROISES DYNAMIQUES.
63
EXCEL avancé
En cliquant sur les flèches à droite de la galerie, vous faites défiler les nombreux styles
proposés. Pour modifier la taille de la galerie, pointez sur la bordure inférieure ; quand le
pointeur a la forme d’une double-flèche, cliquez-glissez.
Quand le pointeur passe sur un style, on peut visualiser son effet sur le tableau, et une
info-bulle indique ses caractéristiques (trame, accent).
L’option « Nouveau style de tableau » proposée en bas de la galerie, permet de créer un
nouveau style. L’option « Effacer » supprime la mise en forme du tableau actif.
On peut trier les données en utilisant un ou plusieurs critères (appelés aussi « clés »),
chacun correspondant à un en-tête de colonne. Après application d’un tri, une flèche apparait dans
la case du menu déroulant de la colonne concernée. Dirigée vers le haut, elle indique un tri
croissant ; vers le bas, un tri décroissant.
On peut effectuer un tri en fonction des valeurs, également en fonction de la couleur de
cellule ou de police, ou de l’icône de la cellule.
En fonction des valeurs, un tri croissant peut être : de A à Z (textes), du plus petit au plus
grand (nombres), du plus ancien au plus récent (dates).
Quel que soit l’ordre (croissant ou décroissant), les cellules vides sont placées en dernier.
En fonction des couleurs ou des icônes, on peut placer les cellules en haut ou en bas.
Une seule clé de tri
Filtrer des données permet de ne laisser affichées que celles qui répondent à des critères
définis (les autres données demeurent, elles sont juste masquées).
Les données affichées peuvent être triées avant ou après filtrage.
Certaines combinaisons de critères ne peuvent pas être appliquées en mode « Filtre
automatique ». Elles requièrent un filtre avancé (voir plus loin).
64
EXCEL avancé
Filtre automatique
Le filtre automatique est par défaut activé, d’où la présence d’un menu déroulant dans la case
d’en-tête de chaque colonne.
Sinon, pour passer en mode « Filtre automatique » : activez le tableau en cliquant dans l’une
de ses cellules, puis sous l’onglet « Données », cliquez sur le bouton « Filtrer » (désactivez
ce bouton pour sortir du mode « Filtre automatique »).
-têtes de
colonnes. En fonction des données de la colonne, il est proposé un type de filtre : textuel, numérique
ou chronologique. Si une cellule au moins se distingue par sa couleur, il est également proposé un
filtre par couleur. Chaque type de filtre propose un menu.
Décocher des cases permet de masquer les enregistrements correspondants. Après avoir
cliqué dans le cadre où sont listées les données de la colonne, taper l’initiale d’une donnée permet
de la mettre en évidence, ce qui peut être utile lorsque la liste est longue.
Plusieurs filtres peuvent être appliqués sur une même colonne.
L’application d’un filtre est indiquée par un symbole filtre dans la case d’en-tête de la
colonne concernée.
Pour supprimer tous les filtres du tableau, et afficher ainsi toutes les données : sous
l’onglet « Données », dans le groupe « Trier et filtrer », activez le bouton « Effacer ».
Filtre avancé
Un filtre avancé combine des critères sur plusieurs données, permettant d’obtenir des filtres
qu’il serait impossible d’obtenir en mode « Filtre automatique ».
Dans l’exemple précédent, un filtre avancé doit être mis en place si on souhaite obtenir les
habitants de Caen, nés après 1983, ainsi que les habitants de Limoges, nés après 1970.
L’application d’un filtre automatique ne pouvant combiner tous ces critères, il est
nécessaire d’appliquer un filtre avancé.
On commence par définir la zone de critères, avant d'appliquer le filtre.
Définition du filtre
précisés sur une même ligne doivent être respectés simultanément (cela correspond à
l’opérateur « et »).
Placez la zone de critères de sorte qu’elle ne risque pas d’interférer avec le tableau de
données, éventuellement sur une autre feuille du classeur, ou bien en haut et à droite du tableau
(afin que le tableau, en s’étendant vers le bas, ne puisse atteindre la plage de définition de critères).
65
EXCEL avancé
Application du filtre
En cochant la case « Extraction sans doublon », on n’affichera que les lignes distinctes.
Un trait vertical relie alors ces données (marquées chacune d’un point), avec le symbole –
à une extrémité.
Si vous cliquez sur ce signe moins, les données du groupe sont
masquées.
Le symbole + remplace alors le – . Cliquez sur le signe plus pour
afficher à nouveau les données du groupe.
Pour créer un niveau supplémentaire, sélectionnez des lignes dont l’une au moins est déjà
ligne de synthèse, puis groupez-les.
Cliquer sur un bouton contenant un numéro de niveau,
permet d’afficher les lignes correspondant à ce niveau, ainsi
que les lignes des niveaux de numéros inférieurs.
Plan automatique
Un plan automatique peut être créé si des données ont été synthétisées, par exemple avec
une formule contenant la fonction Somme.
Exemple :
13 Produits Prix
14 ProduitA 1000,00
15 ProduitB 500,00
16 ProduitC 2000,00
17 Total 3500,00
Les principales fonctions de synthèse sont : Somme, Nombre (le sous-total correspondant est
le nombre de données), Moyenne, Max, Min, Produit, Chiffres (le sous-total correspondant est le
nombre de données numériques uniquement), Ecartype et Var.
67
EXCEL avancé
Quand un sous-total est appliqué, il y a constitution automatique d’un plan : les cellules
concernées par ce sous-total sont automatiquement groupées.
Cela explique la présence de la commande « Sous-total » dans le groupe Plan.
Chaque sous-total est affiché, ainsi que le total correspondant.
68
COURS EXCEL
Chapitre 13 – SIMULATIONS
Les simulations présentées, fonction « Valeur cible », tables et scénarios, consistent à faire
varier des paramètres, puis à examiner les résultats obtenus en fonction des différentes valeurs
testées.
Elle permet de connaître quelle doit être la valeur contenue dans une cellule pour atteindre
une valeur définie dans une autre cellule.
Exemple : quel doit être le prix de vente d’un produit (contenu dans la cellule à modifier)
pour obtenir un bénéfice donné (valeur cible, contenue dans la cellule à définir).
Autrement dit, en utilisant les expressions de la fenêtre « Valeur cible », la fonction
« Valeur cible » indique, en fonction de la « valeur cible » contenue dans la « cellule à
définir », quelle doit être la valeur de la « cellule à modifier ».
La cellule à modifier ne doit pas contenir de formule, juste une valeur.
La cellule à définir contient une formule dépendant, directement ou indirectement, de la
valeur de la cellule à modifier.
Exemple
Vous êtes artiste peintre. Vous vendez vos toiles à un prix moyen de 400 €.
Vous aimeriez connaître le nombre minimum de toiles que vous devez vendre par mois pour
gagner 2000 € / mois.
Vos frais s’élèvent à 80 € en moyenne par tableau, et vous louez un atelier 150 € / mois,
charges comprises.
Modélisez ces données sur une feuille de calcul :
A B
1 Prix moyen d’une toile 400,00 €
2 Nombre de toiles
3 Recette de la vente - €
4 Frais moyens d’une toile 80,00 €
5 Total frais moyens 0
6 Location de l’atelier 150,00 €
7 Bénéfice 150,00 €
69
COURS EXCEL
Affichez la fenêtre « Valeur cible » : sous l’onglet « Données », dans le groupe « Outils
de données », activez le bouton « Analyse de scénarios » > Valeur cible.
- « Cellule à définir » : cliquez dans la zone de saisie, puis sélectionnez la cellule à
définir (dans l’exemple B7).
- « Valeur à atteindre » : saisissez la valeur souhaitée (dans l’exemple 2000).
- « Cellule à modifier » : cliquez dans la zone de saisie, puis sélectionnez la cellule à
modifier (dans l’exemple B2).
Validez.
La fenêtre « Etat de la recherche » s’affiche. Si la recherche a abouti, le tableau de
simulation est rempli.
A B
1 Prix moyen d’une toile 400,00 €
2 Nombre de toiles 6,72
3 Recette de la vente 2687,50 €
4 Frais moyens d’une toile 80,00 €
5 Total frais moyens 537,50 €
6 Location de l’atelier 150,00 €
7 Bénéfice 2000,00 €
Pour obtenir un bénéfice d’au moins 2000 €, il convient donc de vendre 7 toiles.
Autres simulations :
Si on applique la fonction « Valeur cible » pour une Valeur à atteindre de 1000 €, on
obtient un minimum de 4 toiles à vendre.
Si le prix moyen d’une toile est augmenté à 500 € : pour viser un bénéfice d’au moins
3000 €, on devra vendre 8 toiles.
Une table de données affiche des résultats de formules, résultats dépendant d’un ou de
deux paramètres.
On peut ainsi créer des tables à une ou à deux entrées.
70
COURS EXCEL
Exemple
A B C
1 =10*A1 =A1+7
2 20
3 50
4 60
5 100
- Validez.
A B C
1 =10*A1 =A1+7
2 20 200 27
3 50 500 57
4 60 600 67
5 100 1000 107
On fait varier les valeurs de deux paramètres sur une seule formule.
Exemple
A B C D E F
1 =10*E1+F1 4 9 5 3
2 20
3 50
4 60
71
COURS EXCEL
On souhaite connaître les résultats de la formule selon les valeurs de ses deux
paramètres.
Le premier paramètre a ses valeurs en ligne (la cellule d’entrée en ligne est E1) : 4 et 9.
Le second paramètre a ses valeurs en colonne (la cellule d’entrée en colonne est F1) : 20,
50 et 60.
On aurait pu faire l’inverse.
- Sélectionnez la plage de cellules contenant les valeurs des deux paramètres (dans
l’exemple, sélectionnez la plage A1:C4).
- Validez.
A B C D E F
1 =10*E1+F1 4 9 5 3
2 20 60 110
3 50 90 140
4 60 100 150
3. LES SCENARIOS
72
COURS EXCEL
Modifications, activez le bouton « Protéger la feuille »).
Des scénarios relatifs à une même modélisation peuvent se trouver sur plusieurs feuilles,
voire sur plusieurs classeurs, si différentes personnes ont travaillé sur le projet.
Afin que tous les scénarios soient affichables sur une même feuille, il est possible de les
fusionner :
- Ouvrez tous les classeurs contenantdes scénarios à fusionner.
- Sélectionnez la feuille dans laquelle seront fusionnés les scénarios.
- Dans la fenêtre « Gestionnaire de scénarios », cliquez sur « Fusionner ».
Fusionnez feuille par feuille.
Synthèse de scénarios
Un rapport de synthèse affiche pour chaque scénario les valeurs testées dans les cellules
variables et les résultats obtenus dans les cellules résultantes. Il est créé automatiquement, sur une
nouvelle feuille de calcul. Il peut comporter jusqu’à251scénarios.
Dans le classeur actif, Excel crée une nouvelle feuille de calcul, nommée « Synthèse de
scénarios ». Pour chaque scénario, sont affichées les valeurs des cellules variables et celles des
cellules résultantes.
Le rapport est nettement plus lisible si les cellules variables et résultantes ont reçu des noms
explicites, au lieu d’être désignées par leurs références.
73
EXCEL avancé
Un tableau croisé dynamique permet de combiner et de comparer des données, pour mieux
les analyser.
Croisé : toute donnée dépend des étiquettes de sa ligne et de sa colonne.
Dynamique : un tableau croisé dynamique est évolutif, facilement modifiable. Il permet
d’examiner les données sous des angles différents.
Il peut être complété par un graphique croisé dynamique représentant les données du
tableau. Principes et procédures qui lui sont applicables, sont similaires à ceux du tableau.
Source de données
Création du tableau
Pour créer un tableau croisé dynamique, sélectionnez d’abord une cellule quelconque de la
plage des colonnes de données.
Puis affichez la fenêtre « Créer un tableau croisé dynamique » : sous l’onglet
« Insertion », dans le groupe « Tableaux », activez le bouton « TablCroiséDynamique »
Sur la feuille, apparaissent un espace réservé au tableau croisé dynamique, ainsi que le
volet « Liste de champs de tableau croisé dynamique » sur le côté droit.
Le menu déroulant situé sur la barre de titre du volet propose : « Déplacer », « Taille » et
« Fermer ». Après avoir déplacé ou redimensionné le volet, terminez en appuyant sur la touche
Echap (Esc) pour que le curseur reprenne sa forme normale.
Ce volet « Liste de champs » s’affiche dès que le tableau est sélectionné.
74
EXCEL avancé
La constitution du tableau croisé dynamique dépend des champs qui ont été déposés dans
ces trois dernières zones, situées en bas du volet.
Pour déposer un champ, cliquez-glissez sur son nom présent (et case cochée) dans la
« Liste de champs » jusque dans la zone souhaitée, dans la partie inférieure du volet.
Les valeurs concernant ce champ apparaissent dans le tableau croisé dynamique.
Fonctions de synthèse
Une fois les champs déposés, il y a ajout automatique d’une ligne «Total général » et
d’unecolonne«Total général », correspondant à la fonction de synthèse « Somme ».
« Somme » peut être remplacée par une autre fonction, en utilisant la fenêtre « Paramètres
des champs de valeurs ».
Pour afficher cette fenêtre : dans la zone « Valeurs » du volet, cliquez sur le champ
« Somme de… » (ou bien : clic droit sur le nom ou sur une valeur du champ) > « Paramètres
des champs de valeurs ».
Choisissez une nouvelle fonction de synthèse, et attribuez-lui le nom souhaité.
Exemple
75
EXCEL avancé
- La plage A3:D12 des données sources a été sélectionnée par Excel (avec
précision de la feuille de la plage, et références absolues).
Indiquez l’emplacement du tableau croisé dynamique (nouvelle feuille ou feuille
existante) : cliquez dans la zone de saisie, et sélectionnez la première cellule de
l’emplacement prévu pour le tableau.
La dernière ligne contient les sous-totaux Somme du nombre de reçus par centre sur 3
ans, et elle est terminée par le total final relatif à chaque centre.
La dernière colonne totalise le nombre de reçus par année, et elle est terminée par le total
final pour les trois centres.
Remplacez les totaux de Somme par des totaux de Moyenne :
- Affichez la fenêtre « Paramètres des champs de valeurs » : dans la zone « Valeurs »
en bas du volet « Liste de champs », cliquez sur le champ « Somme de Nbre reçus »
> « Paramètres des champs de valeurs ».
- Sélectionnez la fonction « Moyenne » et attribuez le nom « Nbre moyen de reçus ».
Validez.
Remarque : bien que s’agissant cette fois de moyennes, Excel indique encore « Total » général.
Le terme « total » est générique.
76
EXCEL avancé
Exemple
Dans le dernier tableau croisé dynamique, double-cliquez sur la cellule contenant la
moyenne sur les trois années des nombres de candidats reçus à Amiens (valeur 13).
Une nouvelle feuille est créée sur lequel est affiché le tableau suivant :
Nouveauté d’Excel 2010, des segments ou « slicers » peuvent être ajoutés au tableau croisé
dynamique. Simples à créer (et à supprimer), ils facilitent le filtrage des données.
Pour insérer des segments sur le tableau :
-Sélectionnez une cellule quelconque du tableau croisé dynamique.
-Affichez la fenêtre « Insérer des segments » : sous l’onglet « Options », dans le groupe «
Trier et filtrer », activez le bouton « Insérer un segment ».
-Indiquez les champs sur lesquels vous souhaitez filtrer des données. Validez.
Pour sélectionner plusieurs valeurs d’un segment, appuyez sur la touche Ctrl en cliquant sur
les valeurs.
Pour modifier l’apparence d’un segment (style, nombre de colonnes, titre…),
sélectionnez-le, puis utilisez les commandes de l’onglet « Options » des « Outils Segment » (qui
ne comprennent que cet onglet).
Un segment peut agir sur plusieurs tableaux croisés dynamiques. Pour associer un
segment à plusieurs tableaux : après sélection, sous l’onglet « Options », dans le groupe
« Segment », activez le bouton « Connexion de tableau croisé dynamique ». Dans la fenêtre de
même nom, sélectionnez les tableaux à associer au segment. Validez.
77
EXCEL avancé
Cliquez-glissez sur le champ à ajouter de la zone « Liste de champs » jusque dans la zone
souhaitée (« Valeurs », « Etiquettes de lignes » ou « Etiquettes de colonnes »).
L’ajout d’un champ de données par exemple entraîne l’affichage de ce champ à côté du
champ de lignes. Par défaut, la fonction de synthèse est Somme. Une autre fonction peut être
choisie (cf. § 1 « Fonctions de synthèse »).
Pour créer le graphique croisé dynamique, on utilise le même volet « Liste de champs » que
précédemment.
On procède également par cliqué-glissé des champs de la zone supérieure du volet jusque
dans l’une des zones de dépôt : « Champs Axe », « Champs Légende » ou « Valeurs ».
78