excel 2010 - freepagelus.free.fr/antoine/excel 2010.pdf · 2014. 9. 5. · excel 2010 – i....
TRANSCRIPT
1
EXCEL 2010 –
I. FONDAMENTAUX OFFICE 2010 Pour étudier des méthodes et éléments essentiels, communs aux logiciels Microsoft Office
2010, donc applicables à Excel 2010,
Vous devrez maitriser en particulier :
- Le mode Backstage
- La constitution et l’affichage du ruban
- L’intérêt de la barre d’outils Accès rapide
Disponible dans la présentation de la suite office
II. 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 ».
Démarrez Excel 2010.
Le ruban contient les deux onglets spécifiques supplémentaires « Formules » et « Données ».
Le titre du fichier par défaut est « Classeur1 ». La fenêtre principale affiche une partie de la feuille
de calcul active. Par défaut, le nom de la feuille, écrit sur le premier onglet, est « Feuil1 ».
III. L’ECRAN 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.
2
A. 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.
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).
B. DIMENSION DES FENETRES – LES DEUX FENETRES DE L’ECRAN
1. POUR DI MENSIO NN ER UNE FEN ETR E , I L Y A DEUX MET HODES :
a) O N P E U T U T I L I S E R L E S B O U T O N S D E D I M E N S I O N D E S F E N E T R E S ,
S I T U E S E N H A U T A D R O I T E :
▬ : Ce bouton permet de réduire la fenêtre à un titre dans la barre des tâches (en bas de l’écran).
: Ce bouton permet d’afficher la fenêtre en taille réduite. : Ce bouton permet d’afficher la fenêtre en plein écran. : Bouton de suppression, la croix ferme la fenêtre.
b) O N P E U T D I M E N S I O N N E R L E S F E N E T R E S P A R C L I Q U E -G L I S S E S U R
L E U R B O R D U R E .
Si on effectue un cliqué-glissé sur un angle (le pointeur se transforme en double-flèche oblique),
on modifie à la fois la largeur et la hauteur de la fenêtre.
2. L’ECRAN CO MP R EN D DEUX FEN ET RES :
La fenêtre du programme, constituée du ruban, du bouton Microsoft Office et des différentes
barres (barre de titre, barre d’outils Accès rapide, barre des deux zones de saisie, barre d’état et
barre des tâches).
3
La fenêtre du classeur, constituée de sa barre de titre, de la feuille de calcul, des onglets et des
outils de défilement. Par défaut, elle a une taille plein écran.
Elles ont chacune deux boutons de dimension, ainsi qu’un bouton de suppression.
Par défaut, les boutons de dimension du classeur sont situés à côté du bouton d’aide, d’icône le
point d’interrogation. Afin de bien visualiser la fenêtre du classeur, appuyez sur le bouton du
classeur , d’info-bulle « Restaurer la fenêtre ».
Pour déplacer la fenêtre, cliquez-glissez sur sa barre de titre. Pour la redimensionner, vous pouvez
cliquer-glisser sur ses bordures, également utiliser les boutons de dimension, situés dans sa barre de titre, à droite.
3. AFFI CHAGE DE FEUI LLES APP ART ENAN T A DES CLAS S EURS
DI FFER ENT S
Quand on travaille sur des feuilles appartenant à plusieurs classeurs (donc à plusieurs fichiers), on ne peut toutes les afficher en plein écran. On est obligé de réduire plus ou moins leur affichage.
C. LA FEUILLE DE CALCUL
1. COLONN ES ET LI GN ES
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.
4
2. CELLULE ACTIV E - PLAGE DE CELLULES - ZON E ACTI VE
a) C E L L U L E A C T I V E
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 : B2). 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.
b) PL A G E D E C E L L U L E S
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
c) ZO N E D E C E L L U L E S
Une zone de cellules est une plage de cellules, dont les cellules voisines sont soit des cases d’en-tête de colonnes ou de lignes, soit des cellules vides.
La zone active peut être constituée de plusieurs zones de cellules.
d) L E S B A R R E S D E D E F I L E M E N T
En bordure de feuille de calcul, il y a deux barres de défilement, l’une verticale et l’autre
horizontale. Elles permettent de se déplacer dans la feuille de calcul.
On utilise :
Les curseurs de défilement vertical et horizontal, par cliqué-glissé.
les boutons fléchés d’extrémités, pour un déplacement progressif, colonne par colonne ou
ligne par ligne, par clics successifs.
Les barres de défilement hors ces boutons et le curseur (c’est-à-dire les portions non
colorées) pour un déplacement maximal immédiat, par un seul clic.
5
e) F R A C T I O N N E M E N T D E L ’A F F I C H A G E D E L A F E U I L L E
Il est pratique de diviser en 2, ou en 4, l’affichage de la feuille afin de visualiser, éventuellement
de modifier divers endroits de la feuille.
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.
f) ZO N E D E N O M E T BA R R E D E F O R M U L E
Juste au-dessous de la barre de titre du classeur, s’étend une barre contenant deux zones :
Z O N E D E N O M
La « Zone Nom » contient le nom de la cellule active.
B A R R E D E F O R M U L E
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.
Quand la saisie est longue, il est plus pratique d’écrire dans la barre de formule. La saisie ne
risque pas de recouvrir d’autres données de la feuille. Cette barre est par ailleurs extensible en
largeur et en hauteur :
Pour modifier sa largeur : pointez sur le point situé à droite de la Zone Nom. Quand le
pointeur a l’aspect d’une double-flèche horizontale, cliquez-glissez.
Pour modifier sa hauteur : pointez sur la bordure inférieure, puis cliquez-glissez, ou
bien cliquez sur la flèche située à son extrémité.
Pour ajuster la hauteur de la barre au contenu, pointez sur la bordure inférieure, puis double-
cliquez.
Pour afficher de nouveau la barre sur une seule ligne, cliquez sur la double-flèche.
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.
6
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.
Les deux boutons suivants apparaissent dès qu’il y a saisie dans une cellule :
- Le bouton permet d’annuler la saisie en cours. L’équivalent par le clavier est la touche Echap
(Esc). - Le bouton permet de valider la saisie. L’équivalent par le clavier est la touche Entrée. Cette
validation est automatique après déplacement sur une autre cellule.
IV. BARRE D’ETAT : CALCULS ET MODES D’AFFICHAGE La barre d’état permet d’afficher en particulier (faire un clic droit sur la barre, puis, dans la liste « Personnaliser la barre d’état », cocher les options souhaitées) :
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.
V. 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 catégorie sélectionnée. Le bouton indique la présence d’une info-bulle.
Exemples ▪ Par défaut, un classeur contient 3 feuilles.
Pour modifier le nombre de feuilles attribué à chaque nouveau classeur, dans la fenêtre « Options
Excel », choisissez la catégorie « Général ».
Puis, à la rubrique « Lors de la création de classeurs », dans la zone « Inclure ces feuilles »,
saisissez le nombre de feuilles souhaitées. ▪ Après validation de la saisie dans une cellule, en tapant Entrée,
une cellule adjacente est sélectionnée automatiquement afin de
permettre la saisie suivante. On peut choisir quelle cellule sera
sélectionnée : dans la fenêtre « Options Excel », sélectionnez la
catégorie « Options avancées ». Puis, à la rubrique « Options d’édition », la case « Déplacer la
sélection après validation » étant cochée, choisissez le sens souhaité
: Bas, Droite, Haut ou Gauche.
7
GESTION DES FEUILLES ET DES
CLASSEURS
I. GESTION DES FEUILLES DE CALCUL 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.
A. NOMMER UNE FEUILLE
Double-cliquez sur l’onglet. Le nom de l’onglet s’affiche en surbrillance.
Ou bien faites un clic droit sur l’onglet > « Renommer ».
Saisissez le nouveau nom de la feuille.
B. 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.
C. SELECTIONNER DES FEUILLES
Pour sélectionner une ou plusieurs feuilles, on procède ainsi :
- Une feuille : clic sur l’onglet. L’onglet de la feuille sélectionnée devient blanc.
- Des feuilles adjacentes : clic sur l’onglet de la 1ère ; Maj (Shift) + clic sur l’onglet de la dernière.
- Des feuilles non adjacentes : clic sur l’onglet de la 1ère ; Ctrl + clic sur chaque autre onglet de feuille.
- Toutes les feuilles du classeur : clic droit sur un onglet > Sélectionner toutes les feuilles.
Pour annuler la sélection de feuilles, cliquez sur un onglet non sélectionné. A défaut d’onglet
visible non sélectionné, faites un clic droit sur l’onglet souhaité > Dissocier les feuilles. Tous les
autres onglets seront désélectionnés.
D. 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].
Quand il y a constitution d’un groupe de travail, les saisies sur la feuille active peuvent être répercutées sur les autres feuilles du groupe.
8
E. INSERER DES FEUILLES
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.
F. SUPPRIMER UNE FEUILLE
Clic droit sur l’onglet > Supprimer.
On peut sélectionner plusieurs feuilles, puis les supprimer d’un bloc.
On ne peut pas récupérer les feuilles supprimées en activant le bouton « Annuler Frappe », situé sur la barre d’outils Accès rapide.
G. DEPLACER OU COPIER UNE FEUILLE
1. AV EC LA SO URI S
Déplacer : sélectionnez l’onglet à déplacer, puis cliquez-glissez dessus
jusqu’à l’emplacement souhaité. Le curseur prend la forme d’une feuille.
Copier : cliquez sur l’onglet de la feuille. Puis Ctrl + cliquez-glissez jusqu’à l’emplacement
souhaité. Le curseur prend la forme d’une feuille avec le signe +. La nouvelle feuille prend le nom
de la feuille d’origine suivie du n° d’exemplaire placé entre parenthèses : par exemple Feuil1 (2),
pour la 1ère copie de la feuille Feuil1.
On peut déplacer ou copier plusieurs feuilles à la fois en sélectionnant les onglets correspondants
avant de cliquer-glisser.
2. AV EC LA FEN ETR E « DEPLACER OU COPI ER »
Sélectionnez les feuilles à déplacer ou à copier. Pour afficher la fenêtre « Déplacer ou
copier », faites un clic droit sur un onglet sélectionné > Déplacer ou copier.
Renseignez la fenêtre, puis validez. En revanche, pour passer à la bonne page écran, il
est souvent plus pratique de passer par les touches de déplacement du clavier.
9
II. DÉPLACEMENTS DANS UN CLASSEUR
A. AVEC LE CLAVIER :
Déplace le curseur d'une cellule vers le haut.
Déplace le curseur d'une cellule vers le bas.
Déplace le curseur d'une cellule vers la droite (ou d'un caractère vers la droite en
modification).
Déplace le curseur d'une cellule vers la gauche (ou d'un caractère vers la gauche en
modification).
Ctrl + Déplace le curseur sur la dernière cellule travaillée vers la droite, ou dans la
colonne IV, s'il n'y rien sur la ligne.
Ctrl + Déplace le curseur sur la première cellule travaillée vers la gauche, ou dans la
colonne A, s'il n'y rien sur la ligne.
Ctrl + Déplace le curseur sur la dernière cellule travaillée vers le bas, ou sur la ligne
65 536, s'il n'y rien dans la colonne.
Ctrl + Déplace le curseur sur la première cellule travaillée vers le haut, ou sur la ligne l,
s'il n'y rien dans la colonne.
Déplace le curseur au début de la ligne en cours.
Ctrl + Déplace le curseur en Al.
Ctrl + Fin Déplace le curseur à la fin des cellules travaillées.
Pg up Déplace sur le curseur d'un écran vers le haut.
Pg down Déplace le curseur d'un écran vers le bas.
Ctrl + Pg
down
Déplace le curseur sur la feuille précédente du classeur (changement de l'onglet
actif).
Ctrl + Pg up Déplace le curseur sur la feuille suivante du classeur (changement de l'onglet
actif).
Notez bien ces différentes façons de se déplacer car cela va vous servir sans cesse.
Vous pouvez également utiliser l'ascenseur vertical et l'ascenseur horizontal.
Si on connaît la référence de la cellule où on désire aller, on peut utiliser la touche F5 suivie de
l'adresse de cellule, permet de placer le curseur à l'endroit souhaité.
Ex : F5, C20 permet de placer immédiatement le curseur en C20.
Vous pouvez également taper la référence de la cellule à gauche de la barre de formule (dans la
zone de nom), puis validez avec la touche Entrée.
Vous pouvez également accéder à une feuille en cliquant avec la souris sur l'onglet correspondant
à la feuille. Ces onglets se trouvent en bas de la feuille de calcul.
B. AVEC LA SOURIS :
Il faut cliquer dans la cellule désirée. Attention il ne suffit pas tic mettre la souris sur la
cellule, il faut cliquer sinon votre curseur reste n l'endroit précédent.
10
III. AFFICHAGE D’UNE FEUILLE DE CALCUL
A. 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.
B. PLEIN ECRAN
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).
1. CONS ERV ER L ’AFFI CHAGE DES INTIT U LES DES LI GN ES ET DE S
CO LONN ES
Quand la zone active de la feuille de calcul est très large, il n’est plus possible de voir ses
colonnes de titres quand on affiche des colonnes trop à droite.
De même, quand la zone active de la feuille s’étale sur une grande hauteur, on ne peut plus
voir ses lignes de titres quand on affiche les lignes du bas.
Les lignes et colonnes de titres sont appelées des « volets ».
Pour conserver l’affichage des volets :
- Sélectionnez la cellule à gauche de laquelle les colonnes resteront
affichées, et au-dessus de laquelle les lignes resteront affichées ;
- Sous l’onglet Affichage, dans le groupe Fenêtre, activez le bouton « Figer
les volets ». Deux traits noirs continus apparaissent, pour indiquer la présence des volets.
Pour supprimer les volets : cliquez sur le bouton « Figer les volets » > Libérer les volets.
11
IV. TRAVAILLER SUR PLUSIEURS CLASSEURS
A. OUVRIR PLUSIEURS CLAS SEURS
Si on veut travailler sur plusieurs classeurs, il faut commencer par les ouvrir.
Affichez la fenêtre « Ouvrir » : ouvrez le menu Fichier > Ouvrir. Si les fichiers sont dans le
même dossier, faites Ctrl + clic sur chaque nom de classeur à ouvrir. Puis ouvrez les classeurs
en activant le bouton « Ouvrir » de la fenêtre.
L’un affiche une feuille sur l’écran, les autres ont leur nom affiché dans la barre des tâches.
B. BASCULER D ’UN CLASSEUR A L ’AUTRE
Une fois les classeurs ouverts, vous pouvez afficher la feuille d’un classeur en cliquant sur le
nom de ce classeur dans la barre des tâches.
Ou bien : sous l’onglet Affichage, dans le groupe Fenêtre, activez le bouton « Changement de
fenêtre », puis affichez le classeur souhaité.
C. AFFICHER LE MEME CLAS SEUR DANS PLUSIEURS FENETRES
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.
D. DISPOSER LES FENETRES DES CLASSEURS OUVERT S
Affichez la fenêtre « Réorganiser » : dans le groupe Fenêtre (onglet Affichage), cliquez sur le
bouton « Réorganiser tout ».
La fenêtre propose plusieurs options : Mosaïque, Horizontal, Vertical ou Cascade.
Tous les classeurs ouverts, qu’ils soient réduits à leur nom dans la barre des tâches,
ou qu’une de leurs feuilles soit affichée, également les fenêtres d’un même classeur,
seront disposés selon l’option choisie.
En cochant la case « Fenêtres du classeur actif », il ne sera affiché que les fenêtres du même
classeur (voir paragraphe précédent « Afficher le même classeur dans plusieurs fenêtres »).
Pour retrouver l’affichage initial, cliquez sur le bouton Agrandir d’une fenêtre.
E. COMPARAISON DE FEUILLES DE DEUX CLASSEURS COTE A COTE
Si deux classeurs ont une structure similaire, il est intéressant de les mettre côte à côte, et de
les faire défiler simultanément afin de pouvoir comparer facilement leurs données.
Procédez ainsi :
- Ouvrez les deux classeurs.
- Activez le premier classeur.
- Dans le groupe Fenêtre (onglet Affichage), activez le bouton « Afficher
côte à côte ».
12
Si d’autres classeurs sont ouverts, en plus des deux classeurs concernés, la fenêtre « Comparer
en côte à côte » permet d’indiquer le classeur avec lequel on souhaite comparer les données.
Les deux classeurs apparaissent l’un en dessous de l’autre.
Dans le groupe Fenêtre, le bouton d’info-bulle « Défilement synchrone » est activé. Le
déplacement dans l’un des deux classeurs induit le déplacement dans l’autre.
Si on a redimensionné la fenêtre d’un classeur (par cliqué-glissé sur sa bordure inférieure), le
bouton « Rétablir la position de la fenêtre » (groupe Fenêtre), permet de diviser à nouveau
l’écran en deux parties égales pour chacun des classeurs.
Pour revenir à l’affichage normal, désactivez le bouton « Afficher côte à côte ».
13
SAISIE SIMPLE DE DONNEES
I. 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).
A. AVEC LE CLAVIER ET LA SOURIS
Pour sélectionner :
1. UNE CELLULE
Pointez sur la cellule. Le pointeur ayant l’aspect d’une croix blanche, cliquez.
Ou bien, atteignez la cellule avec les touches fléchées du clavier.
Si vous êtes en cours de saisie, validez (par exemple, en tapant Entrée), avant de sélectionner
cette cellule en cliquant dessus.
Pour atteindre :
La cellule A1 : Ctrl + (Home)
La dernière cellule de la zone active (la plage commençant par A1 et qui contient
toutes les données) : Ctrl + Fin (End)
La première cellule de la ligne active : (Home)
Une cellule en bordure de la zone de cellules (zone entourée de cases d’en-tête de
lignes ou de colonnes, ou de cellules vides) : pointez sur la bordure de la cellule
active, en direction de la cellule à atteindre. Quand le pointeur se transforme en
croix fléchée, double-cliquez.
Exemple : si vous souhaitez atteindre la dernière cellule de la zone, à droite de la cellule
active, pointez sur la bordure droite de celle-ci, puis double-cliquez.
2. DES CELLULES FAI SAN T P AR TI E DE LA LI GN E O U DE LA CO LON NE
DE LA CELLULE ACTIV E
Pour sélectionner toutes les cellules situées entre la cellule active et la cellule en bordure de
zone, appuyez sur la touche Maj en double-cliquant sur la bordure de la cellule active en
direction des cellules à sélectionner.
3. UNE P LAGE DE CELLULES
Cliquez-glissez de la première à la dernière cellule.
Ou bien sélectionnez la première cellule, puis appuyez sur la touche Maj et
sélectionnez la dernière cellule de la plage
14
4. DES CELLULES NON ADJA CENT ES ( I SO LEES , EN P LAGES , EN
CO LONN ES OU EN LIGNE S)
Sélectionnez le 1er élément. Puis appuyez sur la touche Ctrl et sélectionnez les
autres en cliquant, ou en cliquant-glissant, dessus.
5. LA ZO NE DE LA CELLULE ACTI VE
Tapez : Ctrl + *
Remarque : la sélection s’arrête à la première lignes/colonnes vide
6. UNE CO LONN E O U UN E LI GN E
Pointez sur la bordure externe de la case de l’en-tête. Le pointeur ayant l’aspect d’une flèche,
cliquez.
Pour sélectionner :
La colonne de la cellule active : Ctrl + Espace.
La ligne de la cellule active : Maj (Shift) + Espace.
Toute la colonne ou toute la ligne de la feuille est sélectionnée.
7. DES CO LONN ES O U DES L I GN ES ADJACENT ES
Cliquez-glissez sur les cases d’en-tête des colonnes ou des lignes à sélectionner.
Ou bien : sélectionnez la 1ère, puis faites Maj (Shift) + clic sur la dernière.
8. DES CO LONN ES O U DES LI GN ES non ADJACENT ES
Sélectionnez la première. Puis Ctrl + clic pour sélectionner chacune des autres.
9. TOUTES LES CELLULES D E LA FEUI LLE
Avec la souris : pointez la case de l’intersection entre les deux lignes d’en-têtes
(horizontale et verticale). Le pointeur se transforme en croix blanche ; cliquez. Toutes les
cellules de la feuille sont sélectionnées.
Avec le clavier : Ctrl + A.
Avec la fenêtre « Atteindre »
Pour l’afficher : sous l’onglet Accueil, dans le groupe « Edition », activez le
bouton « Rechercher et sélectionner » > Atteindre. Ou bien, plus rapidement,
appuyez sur la touche F5.
Renseignez la zone « Référence ».
Exemple de sélection de plages non adjacentes, situées sur la feuille active :
B2 : D5 ; E1 : F8 .
Exemple de sélection de plages situées sur une autre feuille, par exemple la feuille
« Feuil3 » :
feuil3! A4 : E8 ; feuil3! B1 : B5.
15
L’appartenance d’une plage à une feuille se traduit par l’écriture : nomfeuille ! plage
Il n’est pas nécessaire de mettre un espace après un signe de ponctuation ( : ; ou !). 18
La fenêtre « Atteindre » permet également :
De mémoriser les 4 dernières sélections (affichées dans la zone « Atteindre
»).
D’offrir diverses possibilités de sélection : après activation du bouton
« Cellules », la fenêtre « Sélectionner les cellules » permet de sélectionner des cellules
particulières à l’intérieur de la zone active.
Exemple : cocher l’option « Cellules vides » sélectionne toutes les cellules vides de la zone
active (une cellule vide ne contient aucun caractère, pas même un espace).
II. SAISIE DANS LES CELLULES
1. SAISIE DES DONNÉES
Une cellule peut contenir différents types de données :
des libellés (alphabétiques ou alphanumériques),
des nombres,
des dates,
des heures,
des formules,
des libellés répétitifs ....
Si vous n'avez pas de classeur à l'écran, créez-en un nouveau.
o Cliquez dans la cellule Al pour y positionner votre curseur.
2. SAISI E D 'UN LI BELLE
Le libellé est une suite de caractères alignés par défaut à gauche.
o Tapez votre prénom ;
o appuyez sur la touche Entrée pour valider votre saisie.
On remarque que le texte est aligné sur la gauche, mais si vous souhaitez un alignement
différent, vous pouvez utiliser les icônes d'alignement de la barre d'outils Mise en forme, mais
nous y reviendrons plus tard.
o Dans la cellule A2, tapez : BP 25;
o appuyez sur la touche Entrée pour valider votre saisie.
16
De nouveau, ce que vous avez saisi est aligné sur la gauche.
Votre saisie est un mélange d'alphabétique et de numérique, ce que les
informaticiens ont appelé alphanumérique. Comme on peut le
remarquer, c'est l’alpha qui l'emporte pour l'alignement.
3. SAISI E D 'UN N OMBR E
Dans la cellule A3, tapez le nombre 123 en utilisant le pavé numérique ; appuyez sur la
touche Entrée.
On note cette fois-ci que par défaut votre saisie est alignée sur la droite.
En effet tout ce qui est numérique sera par défaut aligné sur la droite.
C'est un moyen de repère.
Dans la cellule A4 tapez le nombre 123,25; validez avec Entrée.
Pour faciliter votre saisie de chiffres avec des décimales, vous pouvez utiliser la touche point
du pavé numérique. En effet, si dans les options d'Excel, on a précisé que l'on voulait une
virgule comme symbole des décimales, le point du pavé numérique, dans Excel uniquement,
vous donne alors une virgule.
À la base les options d'Excel dépendent de la configuration indiquée dans Windows au niveau
des options régionales.
Ne tapez pas, par exemple, la lettre O à la place du zéro (0), car, dans ce cas, il ne s'agit plus
d'un chiffre. Mais de toute façon, si vous utilisez le pavé numérique, vous n'aurez pas ce
problème.
4. SAISI E D 'UN E DAT E
Les valeurs date sont entrées sous la forme JJ/MM/AA :
o Tapez : 25/11/09 dans la cellule A5, puis validez avec la touche Entrée.
Vous obtenez 25/11/2009. L'écran affiche ce nombre dans le format date courant (prévu dans
les paramètres de Windows) mais vous avez la possibilité de demander, par la suite, un autre
format dans Excel.
Lorsque vous tapez une date dans Excel, en ne précisant que 2 chiffres pour l'année, Excel
l'interprète de la façon suivante :
• de 00 à 29, cela concerne les années 2000
• de 30 à 99, cela concerne les années 1900
Exemple: 11/5/61 donnera 11/05/1961 et 16/8/20 donnera 16/08/2020.
17
5. SAISI E D ’UN E HEUR E
Les valeurs heure sont entrées sous la forme HH:MM.
o tapez : 10:30 dans la cellule A6 ;
o validez, avec la touche Entrée.
6. SAISI E D 'UN E FOR MULE
Excel affiche à l'écran le résultat et non la formule, celle-ci apparaît dans la partie supérieure
de l’écran, dans la barre de formule, quand la cellule est active.
Pour rentrer une formule, on commence par taper le symbole =
Pour écrire une formule, vous aurez besoin des opérateurs mathématiques ci-dessous :
+ Addition
- Soustraction
* Multiplication (on n'utilise pas le "x" qui est une lettre) /
Division ^
Élévation à la puissance (AltGr + 9 sur le pavé alphabétique).
7. SAISI E D 'UN LI BELLE R EP ETI TIF
o Dans la cellule A7, tapez Janvier;
o validez en cliquant sur la coche verte de la barre de formule :
Excel vous offre la possibilité de générer automatiquement la suite de la liste. Pour cela, il
suffit de taper le premier libellé, puis d'utiliser la poignée de recopie dans le sens désiré.
o Positionnez votre souris en bas à droite de la cellule.
o Lorsque le signe + apparaît, cliquez et glissez vers la droite sur 2 cellules.
Excel génère automatiquement les autres libellés de la liste.
Dans la cellule A8, tapez : Produit 1, validez avec la coche verte ; prenez votre poignée de
recopie et tirez vers la droite ; vous obtenez Produit 2, Produit 3.... ; dans la cellule A9, tapez :
Lundi, puis faites la même chose ; dans la cellule A10, tapez : 1er Trimestre, et recopiez ...
Pour une suite de chiffres, il faut saisir les deux premières valeurs ; Exemple : tapez 12,
validez puis tapez 14.
Sélectionnez les 2 cellules modèles et tirez avec la poignée de recopie : Excel affiche une
suite de nombres qui vont de 2 en 2.
La poignée de recopie peut être utilisée aussi bien vers la droite que vers un autre sens.
18
Il est également possible de créer ses propres listes personnalisées, grâce à l’onglet Fichier,
Options, option avancé, bouton modifier les listes Personnelles.
o Cliquez avec votre souris dans la cellule Dl ;
o tapez : Pim, puis validez ;
o dans la cellule D2, tapez : Pam et validez;
o dans la cellule D3, tapez ; Poum et validez ;
o cliquez sur la cellule D1 et glissez jusque D3 afin de sélectionner
la liste.
o Appelez le menu fichier, Options, Options avancées, bouton Liste
Pers ;
o cliquez sur le bouton Importer, puis Ok.
Désormais, dans n'importe quel classeur, dès que vous
taperez un des mots de la liste, il vous suffira de prendre
votre poignée de recopie
Pour obtenir les autres mots ou expressions.
o Cliquez dans la cellule F1, tapez ; Pam et validez avec la coche verte ;
o prenez la poignée de recopie et tirez vers le bas.
Excel affiche alors les autres noms de la liste.
8. SAISI E S EMI -AUTO MATI QUE
Nous constaterons parfois lors d'une saisie qu'Excel nous propose des libellés. 11 s'agit en
effet de la saisie semi-automatique qui s'active lorsque nous tapons une seconde fois, le début
d'un mot précédemment utilisé dans la même colonne.
Si cette suggestion vous intéresse, il suffit de valider, sinon continuez votre saisie.
o Cliquez dans ta cellule G1, tapez: Total ht et validez;
o dans la cellule G2, tapez : Total.
o Lorsqu’on effectue des saisies de type texte dans une colonne, Excel les
mémorise.
o Après avoir tapé une ou plusieurs lettres dans une nouvelle cellule de la colonne, dès
qu’Excel reconnaît une saisie précédente, celle-ci est automatiquement proposée.
o
o Il ne doit pas y avoir de cellule vide entre la cellule active et la cellule reconnue.
o Tapez sur Entrée pour valider la saisie proposée et passer à la cellule suivante.
19
o On peut également afficher la « Liste déroulante de choix » : faites un clic
droit dans la cellule juste au-dessus ou juste au-dessous des valeurs déjà saisies > «
Liste déroulante de choix ».
o Apparaissent dans cette liste tous les contenus de type texte de la colonne. La liste
s’arrête dès qu’il y a une cellule vide (en dessus ou en dessous).
o Cliquez sur un élément de la liste affiche son contenu dans la cellule.
9. VALI DATION ET P AS S AGE A UN E AUT R E CELLULE
Pour valider la saisie, et passer :
A la cellule d’en dessous : Entrée ou touche
A la cellule de droite : Tab ou touche
A la cellule du haut ou à celle du bas : touches fléchées du clavier ou
A une cellule quelconque : cliquez dessus.
Par défaut, taper Entrée sélectionne la cellule d’en dessous (option « Bas »).
On peut paramétrer différemment cette option Excel : cf. fin du chapitre INTRODUCTION.
10. SAISI E R API DE DAN S UN E P LAGE SELECTIO NN EE :
Colonne après colonne (saisie « à la verticale ») : après chaque saisie, y compris en
fin de colonne, il suffit d’appuyer sur la touche Entrée (à condition de ne pas avoir
modifié l’option « Bas » de la touche Entrée - cf. fin du chapitre
INTRODUCTION).
Ligne après ligne (saisie « à l’horizontal ») : après chaque saisie, y compris en fin
de ligne, il suffit d’appuyer sur la touche Tab .
11. SAISI E D ’UN E MEME V ALEUR DAN S TOUTES LES CELLULES D ’UN E
PLAGE
Sélectionnez la plage. Saisissez la valeur. Validez avec Ctrl + Entrée.
a) DE B O R D E M E N T
Quand un texte déborde sur la cellule suivante, une saisie dans celle-ci
masque le texte de la cellule précédente.
Quand une date déborde, la validation transforme l’affichage de la date en une suite de
dièses (#).
Testez : réduisez la largeur d’une colonne (par cliqué-glissé sur la bordure droite de sa case
d’en-tête). Dans une cellule de cette colonne, saisissez une date, puis validez.
Quand un nombre déborde, il est affiché en notation scientifique, c’est-à-dire sous la forme du
produit d’un nombre décimal compris entre 1 inclus et 10 exclus par une puissance de 10.
20
Exemple : le nombre 123456789 qui est environ égal à 1,2 * 108, s’affiche dans la cellule
1,2E+08.
S’il y a débordement en notation scientifique, l’affichage du nombre dans la cellule est
remplacé, comme une date, par une série de dièses.
Que ce soit un texte, une date ou un nombre qui déborde, le contenu exact de la cellule
demeure, même si l’affichage est différent. En sélectionnant la cellule (cliquez dessus), son
contenu exact apparaît dans la zone de contenu de cellule (barre de formule).
12. SAISI E D ’UN NO MBR E S OUS FOR ME DE TEXT E
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.
13. SAISI E D ’UN COMMEN T AIR E
L’onglet « Révision » contient un groupe de commandes « Commentaires ».
On peut rattacher un commentaire à une cellule :
- Sélectionnez la cellule.
- Activez le bouton « Nouveau commentaire » du ruban, ou bien clic droit > Insérer un
commentaire.
Le commentaire commence par défaut par le nom de l’utilisateur. Ce nom
peut être modifié ou supprimé.
Après un clic droit sur la cellule contenant le commentaire, un menu contextuel propose les
options : Modifier, Effacer, Afficher / Masquer le commentaire.
Quand le commentaire est masqué, il subsiste dans la cellule un triangle rouge :
Le commentaire apparaît dès que le pointeur est sur la cellule.
Pour modifier l’apparence du commentaire, affichez la fenêtre « Format de
commentaire » : faites un clic droit dans le cadre du commentaire > Format de
commentaire.
14. SAISI E R EST R EINT E
La fenêtre « Validation des données » permet de restreindre une saisie.
Il est par exemple possible de restreindre la saisie à un nombre entier supérieur à 30.
21
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 ».
Le premier onglet « Options » permet de définir la restriction à appliquer.
Le deuxième « Message de saisie » permet de saisir le message à afficher lors de la sélection
de la cellule.
Le troisième « Alerte d’erreur » permet de saisir le message à afficher en cas d’erreur de
saisie.
Enregistrez votre classeur sous le nom « manipulations » :
o Fichier, Enregistrez sous ...
o assurez-vous que vous êtes bien dans le dossier portant votre nom ;
o dans la zone Nom de fichier, tapez le nom du classeur, puis cliquez sur Enregistrer.
III. MODIFICATION DES DONNEES
A. AVANT VALIDATION , SI ON SOUHAITE RECOMMENCER LA SAISIE
DANS LA CELLULE
Le bouton de la barre de formule permet d’annuler la saisie en cours. L’équivalent par le
clavier est la touche Echap (Esc).
B. APRES 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.
C. SUPPRESSION DE DONNEES
Sélectionnez la ou les cellules, puis appuyez sur la touche Suppr.
Après sélection, si vous souhaitez bénéficier d’options d’effacement : sous
l’onglet Accueil, dans le groupe « Edition », activez le bouton « Effacer ».
22
IV. 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.
A. RECHERCHER
Ouvrez l’onglet « Rechercher ».
Si la recherche est à effectuer dans une plage de cellules de la feuille active, sélectionnez-cette
plage de cellules.
Sinon la recherche s’effectuera sur la zone active (cliquez sur une cellule quelconque de la
feuille), ou dans le classeur si vous le précisez.
Vous pouvez utiliser : l’astérisque * pour remplacer 0, 1 ou plusieurs caractères, ainsi que le
point d’interrogation ? pour remplacer un seul caractère.
Vous pouvez préciser le format des expressions recherchées.
Le bouton « Suivant » sélectionne les cellules contenant la valeur recherchée, une par une.
Le bouton « Rechercher tout » liste les occurrences correspondant à la recherche.
B. REMPLACER
Ouvrez l’onglet « Remplacer ».
Le bouton Remplacer permet de remplacer les valeurs une par une autre.
V. 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.
23
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.
A. INSERTION ET SUPPRESSION
1. D ISTI NGUER CON TENANT ET CON TENU
Il faut bien distinguer la suppression du contenant et celle du contenu.
Exemples :
Quand on supprime la donnée d’une cellule, on ne supprime pas la case qu’est cette
cellule.
Quand on supprime les données d’une colonne, la colonne devient vierge, mais elle
reste.
La suppression du contenant entraîne celle du contenu, mais on n’a pas l’inverse.
Cette partie concerne les différents types de contenants : colonnes, lignes et cellules.
Une insertion ou une suppression de contenant entraîne un décalage
Dès qu’il y a insertion (ou suppression) du contenant, il y a décalage de cellules pour
insérer (ou supprimer) les nouvelles cellules.
Les cellules qui ont été décalées, changent de références (sauf si leurs références sont liées à
des références d’autres cellules.
Pour insérer ou supprimer des cellules, on peut utiliser :
Les options du menu contextuel qui s’affiche après clic droit sur la sélection.
Ou bien le ruban : boutons « Insérer » ou « Supprimer » du groupe « Cellules » de
l’onglet Accueil.
B. INSERTION OU SUPPRESS ION DE COLONNES OU D E LIGNES
Le principe est le même pour les colonnes ou pour les lignes.
1. POUR IN S ER ER DES CO LO NNES :
- Sélectionnez le nombre de colonnes à gauche desquelles vont être insérées les
nouvelles colonnes. Le nombre de colonnes insérées correspondra au nombre de
colonnes sélectionnées.
- Clic droit sur la sélection > « Insertion ».
Les colonnes sont aussitôt insérées.
Les références des cellules décalées ont changé.
Exemple
Saisissez le nombre 5 dans la cellule référencée A1.
24
Puis insérez deux colonnes avant la colonne A : sélectionnez les colonnes A et B, en cliquant-
glissant sur leurs cases d’en-tête. Faites un clic droit sur la sélection > Insertion.
Après insertion des colonnes, le nombre 5 est dans la cellule référencée C1.
2. POUR IN S ER ER DES LI GN ES :
- Sélectionnez le nombre de lignes au-dessus desquelles vont être insérées les nouvelles
lignes. Le nombre de lignes insérées correspond au nombre de lignes sélectionnées.
- Clic droit sur la sélection > « Insertion ».
Les lignes sont aussitôt insérées.
Comme expliqué précédemment, les références des cellules décalées ont changé.
3. POUR SUPPRI MER DES LI GN ES O U DES COLO NN ES :
Sélectionnez-les. Puis faites un clic droit sur la sélection > « Supprimer ».
Comme pour l’insertion, les références des cellules décalées changent.
4. IN SERTIO N O U S UP PR ES S ION D ’UN E PLAGE DE CELLULE S
- Sélectionnez la plage de cellules à côté de laquelle sera effectuée l’insertion ou la
suppression de cellules.
- Clic droit sur la sélection > « Insérer » ou « Supprimer ».
- Choisissez l’option souhaitée dans la boîte de dialogue qui s’affiche.
5. IN SERTIO N DE CELLULES
a) O P T I O N « DE C A L A G E V E R S L A D R O I T E » :
Sont insérées le même nombre de cellules vierges que celui des cellules
sélectionnées. Les cellules sélectionnées sont décalées à droite, des cellules vierges
prennent leur place.
b) O P T I O N « DE C A L A G E V E R S L E B A S » :
Les cellules sélectionnées sont décalées vers le bas, des cellules vierges prennent leur place
c) O P T I O N « L I G N E E N T I E R E » :
Des lignes entières sont insérées au-dessus des cellules sélectionnées, en nombre égal au
nombre de lignes de ces dernières (le résultat est le même qu’une insertion de lignes, vue au
paragraphe précédent « Pour insérer des lignes »).
d) O P T I O N « C O L O N N E E N T I E R E » :
Des colonnes entières sont insérées à gauche des cellules sélectionnées, en nombre égal au
nombre de colonnes de ces dernières (le résultat est le même qu’une insertion de colonnes,
vue au paragraphe précédent « Pour insérer des colonnes »).
25
6. SUPP R ES SION DE CELLUL ES
Option « Décalage vers la gauche » : les cellules sélectionnées sont supprimées
et toutes les cellules de droite qui occupent les lignes de ces cellules, sont
décalées à gauche.
Option « Colonne entière » : cette fois-ci, toutes les cellules des colonnes de droite sont
décalées à gauche (le résultat est le même qu’une suppression de colonnes).
Option « Décalage vers le haut » : c’est le même principe que précédemment. Les cellules
sélectionnées sont supprimées et toutes les cellules d’en-dessous qui occupent les colonnes de
ces cellules, sont décalées vers le haut.
Option « Ligne entière » : cette fois-ci, ce sont tous les cellules en dessous qui sont décalées
vers le haut (et non plus seulement celles qui occupent les colonnes des cellules sélectionnées)
(le résultat est le même qu’une suppression de lignes).
VI. ANNULER OU RÉTABLIR UNE ACTION Si vous vous apercevez que vous venez de commettre une erreur de manipulation (effacer une
cellule par mégarde), vous avez la possibilité de faire marche arrière en annulant votre
commande.
Vous pouvez annuler les actions une par une, ou annuler plusieurs opérations en une
seule fois en cliquant sur la flèche à côté.
Si finalement vous changez d'avis, vous pouvez toujours rétablir les
opérations annulées, en cliquant sur l'icône :
VII. CRÉATION D'UNE FORMULE DE CALCUL Pour créer une formule, placez le curseur sur la cellule qui doit recevoir le résultat, et donc la
formule.
o Cliquez sur l'onglet Feuil2 ;
o saisissez le petit tableau ci-dessous (sauf la
cellule B4). On désire obtenir le résultat dans la cellule
B4.
o Placez le curseur en B4, en cliquant dans la
cellule B4 ;
o tapez = ;
o cliquez sur la cellule B2 ;
o tapez * (signe de la multiplication) ;
o cliquez sur la cellule B3 ; On retrouve la formule : =B2*B3
26
Lorsque vous avez terminé la formule, validez en appuyant sur la touche Entrée ou cliquez sur
l'icône qui apparaît en haut de l'écran à côté de la formule.
Le signe = signifie saisie d'une formule (vous pouvez également taper +, dans ce cas, Excel
mettra automatiquement le signe = devant)
Une fois la formule saisie et validée, Excel affiche le résultat.
Dans la cellule B4, on retrouve la formule B2*B3 dans la barre de formule.
Il est également possible d'entrer une formule en tapant directement les coordonnées, mais
cette solution est moins commode et peut conduire quelquefois à des erreurs de coordonnées
de cellules.
Nous venons de voir comment construire une formule simple, mais en réalité, les possibilités
sont beaucoup plus nombreuses.
Une formule peut se composer de plusieurs références de cellules ou constantes reliées par
des opérateurs.
Il est alors possible d'utiliser des parenthèses () pour construire des formules plus complexes
ou lorsque l'on souhaite modifier les règles de priorité.
A. PRIORITE DES OPERATEU RS
100+3 Se réalise sans problème.
100+3* 0,1 Pose un problème.
Si vous souhaitez réaliser 103*0,1, vous devez utiliser des () et donc • .faire (100+3)*0,1.
Si vous souhaitez réaliser 100+0,3 vous pouvez laisser la formule telle quelle.
B. LES REGLES DE PRIORIT E CLASSIQUES SONT :
( ) Les priorités ont une priorité maximum, dans le respect de leur ordre
d’apparition
^ Elévation à la puissance
* Multiplication
/ Division
+ Addition
- Soustraction
Par exemple, dans une formule de calcul, * sera exécuté avant +
27
Exemple de formules valides :
= A5/A3-AI0
=A5/(A3-A7)
=AI0*200
=B2--10 équivaut à B2 - (-10) le premier - signifie soustraction, le deuxième - veut dire
négatif.
C. MODIFIER UNE FORMULE
A tout moment, il est possible de modifier une formule :
o cliquez dans la barre de formule
o corrigez la formule et validez. Ou
o double-cliquez dans la cellule contenant la formule ou appuyez sur la touche F2
o apportez directement les modifications et validez.
Pour le moment, ne modifiez pas votre formule.
Fermez le classeur en enregistrant les modifications.
28
EXERCICE 2
OBJECTIF : Création d'un tableau simple. ?
Créez un nouveau classeur :
o dans la cellule Al, saisissez : Articles, puis faites Entrée ;
o en A2, saisissez : Rosiers, puis Entrée ;
o continuez la saisie comme dans le modèle ci-dessous :
La saisie terminée, enregistrez le classeur sous le nom « PÉPINIÈRE ».
o Appelez le menu Fichier, Enregistrer sous ;
o mettez-vous dans le dossier portant votre nom, si vous n'y êtes pas ;
o le nom du fichier et cliquez sur Enregistrer.
Création des formules :
Vous allez tout d'abord calculer le hors taxe (HT) pour les rosiers, qui est égal à la quantité
que multiplie le prix unitaire.
o Cliquez dans la cellule D2 et tapez = ;
o cliquez ensuite dans la cellule B2 et tapez * ;
o cliquez dans la cellule C2 ;
o appuyez sur la touche Entrée, pour validez votre formule.
o Dans la cellule D3, tapez = ;
o cliquez dans la cellule B3, tapez * ;
o cliquez dans la cellule C3, puis Entrée, pour validez votre formule.
o Effectuez les mêmes manipulations pour les cellules D4, D5 et D6.
Pour le calcul de la TVA (HT* Taux TVA), vous allez, pour le moment, mettre le taux clans
la formule.
29
o Cliquez dans la cellule E2 et tapez = ;
o cliquez dans la cellule D2 et tapez * ;
o tapez ensuite le taux de T.V.A : 19,6% ;
o appuyez sur Entrée, pour validez votre formule.
o Dans la cellule E3, tapez = ;
o cliquez dans la cellule D3, tapez * ;
o tapez ensuite le taux : 19,6%, puis Entrée, pour validez votre formule.
o Effectuez les mêmes manipulations pour les cellules E4, E5, et E6.
Calcul du TTC : HT+TVA
o Cliquez dans la cellule F2 et tapez = ;
o cliquez dans la cellule D2, tapez + ;
o cliquez dans la cellule E2 ;
o appuyez sur Entrée, pour validez votre formule.
o Dans la cellule F3, tapez = ;
o cliquez dans la cellule D3, tapez + ;
o cliquez dans !a cellule E3, puis Entrée, pour validez votre formule.
o Effectuez les mêmes manipulations pour les cellules F4, F5, et F6.
Réenregistrez votre classeur ("cliquez sur la 3ème
icône de la barre d'outils standard).
Voici les résultats que vous obtenez après vos calculs :
Il est bien évident que si vous aviez eu 5 000 articles en stock, vous n'auriez pas pu effectuer
tous les calculs un par un.
Fermez le classeur en enregistrant les modifications.
30
PRESENTATION DES DONNEES, MISE EN FORME ET DIMENSIONS
I. ELEMENTS DE MISE EN FORME La mise en forme d’une cellule concerne plusieurs éléments :
- Le « nombre », c’est-à-dire le format d’affichage de la valeur (exemples : format monétaire,
format pourcentage ou format texte) ;
- L’alignement de la valeur dans la cellule ;
- La police des caractères ;
- La bordure de la case ;
- Son remplissage ;
- Son format.
II. OUTILS DE MISE EN FORME
A. FENETRE « FORMAT DE CELLULE »
Les cinq premiers éléments cités ci-dessus font chacun l’objet d’un onglet
de la fenêtre « Format de cellule » facilement accessible par le clic droit.
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.
B. LES COMMANDES DU RUBAN OU DE LA MINI-BARRE D ’OUTILS
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.
31
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.
C. PRINCIPE D ’APPLICATION D ’UNE MISE EN FORME
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 ».
1. ELEMENTS DE MISE EN FORME
a) F O R M A T S D ’A F F I C H A G E
Sélectionnez les cellules à traiter.
Dans le groupe Nombre, activez le menu déroulant de la zone de saisie.
Onze formats prédéfinis par Excel sont proposés, avec un aperçu de la cellule active (en
dessous du nom du format). La dernière option « Autres formats numériques » affiche la
fenêtre « Format de cellule ».
b) F O R M A T S T A N D A R D
C’est le format qu’Excel applique par défaut.
Il consiste en l’alignement à droite d’un nombre ou d’une date, et en l’alignement
à gauche d’un texte.
c) F O R M A T NO M B R E
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.
d) F O R M A T MO N E T A I R E
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.
32
Si la saisie précise la devise par défaut (par exemple si on saisit 219,50 € et que l’euro a été
défini comme la devise par défaut), le format monétaire est automatiquement appliqué à la
cellule.
Changement du symbole de la devise : ouvrez la fenêtre « Format de cellules » à l’onglet
Nombre, et choisissez le symbole souhaité.
e) F O R M A T C O M P T A B I L I T E
Comme un nombre de format Monétaire, un nombre de ce format affiche par défaut deux
décimales, le séparateur de milliers et la devise.
Des éléments le différencient cependant du format monétaire :
Le symbole de la devise est aligné avec un retrait d’un caractère du bord droit de la cellule ;
Une valeur nulle est remplacée par un tiret, placé au centre ;
Montant négatif : le signe moins est aligné, seul, à gauche de la cellule.
f) F O R M A T DA T E E T HE U R E
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.
g) F O R M A T PO U R C E N T A G E
Exemple : si on applique le format Pourcentage à la cellule contenant la valeur 0,055, la
valeur affichée devient 5,5% (avec l’option choisie d’une décimale).
Le bouton d’icône % du groupe Nombre ou de la mini-barre d’outils permet d’appliquer
directement ce format.
h) F O R M A T F R A C T I O N
Le format Fraction permet de représenter un nombre décimal sous la forme de sa partie
entière suivie d’une fraction. Exemple : 3,5 peut être affiché 3 ½ en format Fraction.
i) F O R M A T S C I E N T I F I Q U E
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).
33
j) F O R M A T TE X T E
En appliquant à une cellule le format Texte, même un nombre acquiert ce format. Il ne pourra
donc pas être utilisé dans des formules mathématiques.
Une formule saisie restera sous sa forme de texte (le résultat ne sera pas affiché).
k) F O R M A T S P E C I A L
La fenêtre « Format de cellule » propose la catégorie « Spécial ».
Le format Spécial regroupe divers formats particuliers, type Code postal, Sécurité Sociale ou
Téléphone. Ils dépendent du paramètre régional choisi.
l) F O R M A T P E R S O N N A L I S E
Dans la fenêtre « Format de cellule », choisissez la catégorie « Personnalisée ».
Si vous souhaitez définir un format Personnalisé, vous pouvez l’écrire directement, ou plus
simplement, vous baser sur un type prédéfini que vous modifierez.
Il existe en effet plusieurs types prédéfinis de format Personnalisé. Pour en choisir un, cliquez
dessus. Il apparaît dans la zone de texte, sous « Type ». Validez.
2. ALI GN EMEN T ET O RI EN TA TION
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.
Seront utilisés :
- Sur le ruban, le groupe Alignement de l’onglet Accueil ;
- La fenêtre « Format de cellule », à l’onglet Alignement.
a) AL I G N E M E N T H O R I Z O N T A L O U V E R T I C A L
A L I G N E M E N T H O R I Z O N TA L ( A L I G N E M E N T P A R R A P P O RT A L A L A R G E U R D E
L A C E L L U L E )
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).
La fenêtre « Format de cellule » contient d’autres possibilités d’alignement.
L’option Recopié répète le contenu dans la cellule, autant de fois que
possible.
L’option Distribué répartit le texte dans la cellule, avec autant de retrait
à gauche qu’à droite.
Alignement vertical (alignement par rapport à la hauteur de la cellule)
34
On retrouve les principales options appliquées précédemment, verticalement cette fois.
OR I E N T A T I O N D U T E X T E
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 ».
TE X T E E T D I M E N S I O N S D E L A C E L L U L E
« Renvoyer à la ligne automatiquement »
Le texte ne déborde plus sur les cellules voisines. Il est affiché entièrement dans la cellule, qui
s’agrandit en hauteur, s’il y a besoin, au fur et à mesure de la saisie.
Activez le bouton « Renvoyer à la ligne automatiquement » du groupe Alignement, ou bien
cochez la case correspondante 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.
« Fusionner les cellules »
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.
3. POLI CE , BO RDURE , REMPLI SS AGE
a) PO L I C E
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 Aet Apermettent d’augmenter ou de réduire, à chaque clic, d’un
point la taille des caractères.
Fenêtre « Format de cellule », onglet Police :
35
Dans la fenêtre, sont également proposés des types de soulignement, des couleurs
personnalisées, ainsi que les attributs Barré, Exposant et Indice.
b) BO R D U R E
Commandes du ruban (ou de la mini-barre d’outils) :
Dans le groupe Police, le bouton de bordures 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.
TR A Ç A G E D E S B O R D U R E S :
Choisissez d’abord une « Couleur de ligne » et un « Style de trait ».
Quand le pointeur devient crayon, ou gomme si vous cliquez sur l’option « Effacer les
bordures », procédez par clics ou par cliqués-glissés. Pour appliquer un contour (option «
Tracer les bordures ») ou un quadrillage (option « Tracer les bordures de grille ») à toute une
plage de cellules, cliquez-glissez en diagonale.
Pour terminer, appuyez sur la touche Echap (Esc).
OU
F E N E T RE « F O R M A T D E C E L L U L E », O N G L E T B O R D U R E :
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.
c) RE M P L I S S A G E
Commande du ruban (ou de la mini-barre d’outils) :
Le bouton « Couleur de remplissage » (d’icône pot de peinture) permet
d’appliquer une couleur de fond. En pointant sur une couleur de la galerie, on a un
aperçu instantané sur les cellules sélectionnées.
Fenêtre « Format de cellule », onglet Remplissage :
A cet onglet, on peut choisir :
- Soit une couleur d’arrière-plan, agrémentée d’un motif, dont on définit la couleur et le type,
36
- Soit un dégradé, après affichage de la fenêtre « Motifs et textures » sur activation du bouton
du même nom. On définit sa couleur et son type.
37
EXERCICE 3
OBJECTIF : Mise en forme du tableau « PÉPINIÈRE».
Ouvrez le document « PÉPINIÈRE ».
o Sélectionnez les cellules de Al à F1, et mettez les en gras, alignement centré ;
o modifiez la hauteur de ligne de la ligne 1 (doublez-la) ;
o sélectionnez les cellules A2 à A6, et mettez-les en italique ;
o sélectionnez les cellules B2 à F6 ; I-
o cliquez sur l'icône Séparateur de milliers
o sélectionnez les cellules B2 à B6, et cliquez 2 fois sur l'icône Supprimer les
décimales ;
o sélectionnez les cellules de Al à FI, et demandez avec l'icône une trame jaune ;
o sélectionnez tout le tableau (cliquez dans une cellule et faites CTRL + *) ;
o demandez un contour double et des lignes verticales simples ;
o sélectionnez les cellules de Al à F1, et demandez avec l'icône Bordure, un trait bas
double ;
o conservez votre sélection (Al à F1), et demandez un alignement vertical centré.
Enregistrez le document sous le nom « STOCK » et fermez-le.
38
III. CREATION ET APPLICATION D’UN STYLE Un style est un ensemble d’éléments de mise en forme (format d’affichage, alignement,
police…) auquel on donne un nom afin de pouvoir ensuite le réutiliser. Il est applicable sur
toutes les feuilles du classeur.
Excel 2010 dispose de nombreux styles prédéfinis. On peut créer des styles personnalisés.
Pour appliquer un style : sélectionnez les cellules à traiter, puis cliquez sur le nom du style
souhaité.
Dans le groupe « Style », on utilise le bouton « Styles de cellules ».
A. STYLES PREDEFINIS
Pour accéder à la galerie des styles, activez le bouton « Styles de cellules ».
En pointant sur un style, on a un aperçu instantané sur les cellules sélectionnées de
la feuille de calcul.
Cliquer sur le nom d’un style permet de l’appliquer à la sélection de cellules.
B. CREATION D ’UN STYLE
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 ».
IV. MISE EN FORME CONDITIONNELLE « Mise en forme conditionnelle » : la mise en forme est conditionnée par le contenu de la
cellule.
Par exemple, si la valeur d’une cellule est comprise entre 18 et 24, ses caractères sont verts et
le style est italique.
Des mises en forme conditionnelles prédéfinies d’Excel sont
disponibles.
On peut également créer des mises en forme conditionnelles
personnalisées.
Excel 2010 permet de définir des critères portant sur des
valeurs appartenant à d’autres feuilles du classeur actif.
Principe de procédure pour définir et appliquer une mise en forme conditionnelle :
- Sélectionnez la plage de cellules à traiter.
39
- Dans le groupe Style, cliquez sur le bouton « Mise en forme conditionnelle ».
- Pointez sur le type choisi, puis cliquez sur l’option souhaitée.
Ou bien, cliquez sur « Nouvelle règle » pour créer une règle personnalisée.
- Renseignez la ou les fenêtre(s).
- Validez.
V. REPRODUCTION D’UNE MISE EN FORME On utilise sur le ruban, dans le groupe Presse-papiers, le bouton d’info-bulle «
Reproduire la mise en forme », d’icône pinceau. Ce bouton est également présent
dans la mini-barre d’outils.
- Sélectionnez la cellule dont vous allez reproduire la mise en forme.
- Cliquez sur le bouton « Reproduire la mise en forme » . Le format de la cellule sélectionnée
est alors mémorisé. Le pointeur se transforme en pinceau.
- « Peignez » la plage de cellules sur lesquelles vous souhaitez appliquer la mise en forme.
Pour reproduire une mise en forme sur plusieurs plages de cellules, procédez de même, mais
en double-cliquant sur le bouton « Reproduire la mise en forme ».
Pour retrouver le curseur normal, désactivez le bouton « Reproduire la mise en forme » ou
bien appuyez sur la touche Echap (Esc).
VI. MODIFICATION DU THEME Un thème est un ensemble d’éléments de mise en forme : couleurs, polices et effets
de remplissage. L’application d’un thème permet de modifier et d’harmoniser les
styles appliqués dans le document.
Les thèmes prédéfinis disponibles dans Excel 2010 sont également proposés dans les autres
applications d’Office 2010, ce qui favorise une cohérence visuelle de documents issus de ces
applications.
Seront utilisés les boutons du groupe « Thèmes », sous l’onglet Mise en page.
Pour accéder à la galerie des thèmes, activez le bouton « Thèmes ». En pointant sur un thème,
on a un aperçu instantané sur la feuille en cours (modification de la police des cases d’en-tête
et de celle des caractères des cellules, modification des couleurs des styles appliqués).
Le thème appliqué par défaut est « Office » (ou « Bureau »).
40
A. JEU DE COULEURS , JEU DE POLICES ET JE U D ’EFFETS
Chaque thème a un jeu de couleurs, un jeu de polices et un jeu d’effets.
Les couleurs (remplissage, caractères, bordures…) et les polices (liste de choix du bouton «
Police ») qui sont proposées, commencent toujours par celles du thème actif.
Il est possible de modifier séparément les couleurs, les polices ou les effets d’un thème.
Cliquer sur un jeu l’applique au thème actif, remplaçant le jeu précédent.
Jeux de couleurs et jeux de polices s’appliquent respectivement à des couleurs et à des polices
prédéfinies par Excel.
1. COULEUR S
Cliquez sur le bouton « Couleurs ». Chaque thème comporte 12 couleurs : 2 de texte, 2
d’arrière-plan, 6 d’accentuation et 2 de liens hypertexte (lien non encore activé et lien qui a
été activé).
Sous le titre « Prédéfini », la liste des thèmes prédéfinis s’affiche, chaque nom de thème
comportant à sa gauche un jeu de 8 couleurs. Les 4 autres couleurs (2 de texte et 2 d’arrière-
plan) du thème actif sont représentées dans le bouton « Couleurs ».
2. POLI CES
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).
VII. LARGEURS DES COLONNES ET HAUTEURS DES
LIGNES On se sert de la souris, ou bien on utilise le bouton Format du groupe Cellules.
Les procédures de paramétrage sont similaires pour une colonne ou pour une ligne.
Prenons l’exemple de la colonne.
Modifier la taille des colonnes
Ouvrez le document « manipulations », afin de vous entraîner...
Excel vous permet de modifier la largeur des colonnes. Celles-ci prennent par défaut une
largeur de 10 caractères. Cette largeur peut varier entre 0 et 255. Vous disposez de plusieurs
possibilités :
o modifier une seule colonne ;
o modifier la largeur de plusieurs colonnes ;
o ajuster automatiquement la largeur d'une colonne ;
o modifier la largeur standard.
41
A. MODIFIER LA LARGEUR D ’UNE COLONNE
o Placez le pointeur de la souris entre les références de colonnes ;
o cliquez et glissez afin d'agrandir ou de rétrécir.
Vous pouvez changer la taille de plusieurs colonnes de la même manière. Il vous suffit de les
sélectionner préalablement.
B. AJUSTEMENT AUTOMATIQU E DE LA LARGEUR D 'UNE COLONNE
o Positionnez votre souris entre les références de colonnes ;
o double-cliquez sur la limite droite.
Excel ajuste automatiquement par rapport au contenu le plus long de la colonne.
Vous avez la possibilité de lancer un ajustement automatique sur plusieurs colonnes. Pour
cela, i1 vous suffit de sélectionner les colonnes concernées et de double-cliquer sur l'une des
limites.
La forme du pointeur change
Toutes les commandes précédemment énoncées peuvent également
être effectuées par le bouton format du groupe cellule contenu dans
l’onglet accueil
C. MODIFIER LA LARGEUR STANDARD
Si vous souhaitez modifier la largeur standard (égale à 10,71).
o Avec le bouton Format, largeur par défaut
o tapez une nouvelle dimension.
Toutes les colonnes ayant une largeur standard seront modifiées. En revanche, les
colonnes ayant eu une largeur modifiée précédemment, conserveront leur largeur.
Vous pouvez réduire la taille des colonnes à une largeur de zéro. Dans ce cas, vous masquez
la colonne qui n'apparaîtra pas lors de l'aperçu ou de l'impression.
Cette manipulation peut être faite manuellement ou par le bouton format, Masquer &
afficher.
Le masquage des cellules peut être annulé par la commande Afficher du bouton Format
Masquer & afficher.
42
D. MODIFIER LA HAUTEUR DES LIGNES
Le principe est le même que pour les colonnes, mis à part qu'Excel ajuste toujours
automatiquement la hauteur de la ligne par rapport à la taille des caractères. La hauteur
standard correspond à 120 % du corps du caractère.
2 possibilités :
Avec la souris
o Placez le pointeur entre les références de lignes ;
o cliquez et glissez vers le bas, afin d'agrandir.
Avec le bouton Format,....
Les commandes sont les mêmes que pour les colonnes.
Il est préférable de jouer sur les hauteurs de ligne ou sur les largeurs de colonnes pour
embellir votre tableau, plutôt que d'insérer de nouvelles lignes ou de nouvelles colonnes.
43
DEPLACER, COPIER, COLLER ET TRI
RAPIDE
I. VOLET PRESSE-PAPIERS Ce volet permet de stocker 24 éléments coupés ou copiés.
A volonté, un élément du Presse-papiers peut être collé par simple clic.
Pour afficher le volet Presse-papiers : sous l’onglet Accueil, cliquez sur le lanceur du
groupe Presse-papiers.
Le bouton « Coller tout » insère d’un bloc tous les éléments, les uns en dessous des
autres, à partir de la cellule sélectionnée. L’ordre des valeurs insérées est inverse de
celui affiché dans le Presse-papiers.
o Sélectionnez ce que vous désirez recopier ;
o cliquez sur l’icône Copier ;
o Affichez le presse-papiers
Si vous choisissez Coller tout, Excel colle alors tous les éléments dans une seule colonne.
II. DEPLACER Avant de cliquer-glisser pour déplacer ou copier des cellules, le pointeur doit avoir la forme d’une
croix fléchée.
A. DEPLACER LES VALEURS D ’UNE PLAGE DE CELLULE S
1. AV EC LA S OURIS
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
2. AV EC LA CO MMAN DE COUP ER
Procédez ainsi pour déplacer les valeurs d’une plage de cellules :
- 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).
44
III. COPIER
A. COPIER , PUIS COLLER LA COPIE CONFORME
La méthode Copier/Coller est similaire à celle du Couper/Coller :
- Clic droit sur la plage > Copier (ou Ctrl + X ; ou dans le groupe Presse-papiers, activez le bouton
Copier). La sélection affiche une bordure clignotante.
- Sélectionnez la première cellule de la plage de destination.
- Clic droit > Coller (ou Ctrl + V, ou bouton Coller du groupe Presse-papiers).
Il est rapide d’effectuer un Copier/Coller à la souris : Ctrl + cliqué-glissé sur le contour de la
sélection (signe + près du pointeur).
La bordure clignotante qui entoure la sélection indique que la sélection peut être collée à nouveau.
La plage peut être collée à volonté tant qu’elle est entourée de cette bordure.
Pour la supprimer : double-cliquez dans une cellule, ou appuyez sur la touche Echap (Esc).
B. COPIER UNE PLAGE ET L ’INSERER SUR LA MEME FEUILLE DU
CLASSEUR
Cette fois-ci, il y a également insertion, donc décalage de cellules.
- Faites un clic droit sur la sélection > Copier ;
- Clic droit sur la 1ère cellule de la plage de destination > Insérer les cellules copiées.
Dans la boîte de dialogue « Insérer et coller », choisissez une option de décalage.
C. OPTIONS DE COLLAGE
On vient de voir le « Coller » simple, copie conforme de la sélection.
Il existe diverses autres options de collage, proposées juste avant ou juste après le collage.
Diverses options de collage sont proposées :
- Dans le menu déroulant du bouton Coller, situé dans le groupe Presse-papiers (onglet Accueil).
- 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.
- Juste après le collage : dans le menu déroulant du bouton « Options de collage » qui s’affiche à
l’angle inférieur droit des cellules qui ont été collées.
45
Ouvrez le classeur « PÉPINIÈRE ».
o Cliquez et glissez sur les cellules allant de A1 à F1 pour les sélectionner
o cliquez sur l'icône Copier ;
o cliquez ensuite dans la cellule A10 ;
o cliquez sur l'icône Coller et votre ligne de titre a été recopiée.
o Sélectionnez les cellules Al à A6 ;
o cliquez sur l'icône Copier ;
o cliquez ensuite en bas de l'écran sur l'onglet Feuil2 ;
o cliquez sur l'icône Coller et votre ligne de titre a été recopiée cette fois-ci dans une
autre feuille.
Vous avez sans doute remarqué que l'icône option de Collage à proximité de la recopie. Il
possède une liste déroulante : en effet, à côté de celle-ci apparaît une petite flèche.
Si vous cliquez dessus, vous pouvez alors choisir de coller les formules ou les
valeurs et surtout vous pouvez coller sans les bordures. N'oubliez cette possibilité qui
vous sera très utile par la suite dans votre travail.
o Entraînez-vous à faire quelques copier - coller sur la même feuille, puis d'une
feuille à une autre.
Fermez ensuite le classeur sans enregistrer les modifications.
IV. RECOPIE INCRÉMENTÉE (POIGNÉE DE RECOPIE) Nous avons précédemment évoqué la saisie incrémentée. La recopie incrémentée fonctionne
de la même manière et est aujourd'hui la méthode la plus rapide et la plus simple pour
recopier vos formules.
Positionnez votre curseur sur la cellule contenant la formule à recopier ;
o attrapez la poignée de recopie, en bas à droite ;
o appuyez sur le clic gauche de la souris et maintenez-le ;
o tirez vers le bas ou vers la droite, jusqu'à l'endroit où vous désirez
o recopier.
Poignée de recopie
Lorsque vous faites des recopies,
Excel recopie la formule et l'applique aux nouvelles cellules en reproduisant en fait le même
chemin.
Ce qui parfois posera des problèmes que nous aborderons plus loin.
46
Ouvrez le classeur « PÉPINIÈRE ».
Comme nous l'avons vu précédemment, lors de la création des formules, il est bien évident
que si vous aviez eu 5 000 articles en stock, vous n'auriez pas pu saisir tous les calculs un par
un.
o Cliquez dans la cellule D2, puis glissez (en maintenant le clic appuyé) jusqu'à la
cellule F6 afin de sélectionner toutes les cellules de D2 à F6 ;
o appuyez sur la touche Suppr afin d'effacer vos formules.
o En D2, refaites la formule du montant HT pour les rosiers.
o Revenez sur la cellule D2 et positionnez votre souris sur le bord en bas à droite de
manière à obtenir la poignée de recopie ;
o lorsque vous avez la poignée de recopie, cliquez en maintenant le clic appuyé et
glissez jusqu'à la cellule D6.
C'est magique ! Excel a recopié votre formule en appliquant la même opération
(multiplication) et en suivant le même chemin (Excel a pris le chiffre qui est dans la colonne
Quantité et l'a multiplié par le chiffre de la colonne P. Unitaire).
o En E2, refaites la formule de la TVA pour les rosiers ;
o revenez sur la cellule E2 et positionnez votre souris sur le bord en bas à droite de
manière à obtenir la poignée de recopie, puis cliquez et glissez jusqu'à la cellule E6.
o En F2, refaites la formule du TTC pour les rosiers ;
o revenez sur la cellule F2 et positionnez votre souris sur le bord en bas à droite de
manière à obtenir la poignée de recopie, puis faites un double-clique. C'est encore plus
rapide !
o Le double-clique sur la poignée de recopie ne fonctionne que verticalement
(Vers le bas). Excel recopie jusqu'à la fin de la colonne précédente.
Evitez de laisser des lignes ou des colonnes vierges dans votre tableau.
47
ETUDE DE CAS №1
OBJECTIF : Création de formules simples et utilisation de la poignée de recopie.
Ouvrez le classeur « Compte de Résultat ».
o Agrandissez la colonne A ;
o faites les formules pour la marge brute, le total des charges et le résultat pour le mois
de janvier :
o Marge brute - chiffre d'affaires - achats.
o Total charges = frais personnel + frais transports +... +...jusqu'à amortissement.
o Résultat = marge brute - total charges.
Enregistrez le document sous le nom « ÉTUDE l ».
o Appliquez un format à zéro décimale, avec un séparateur de milliers et faites en sorte
que les chiffres négatifs soient en rouge avec un moins devant, pour qu'ils soient plus
visibles.
o Faites la mise en forme de votre tableau afin d'obtenir le résultat donné en page
suivante (largeur colonne, hauteur de lignes, encadrements et traits intérieurs, trame,
alignement...).
o Demandez un zoom à 50 %,, afin de faciliter votre recopie.
o Sélectionnez les cellules B1 à B12, prenez la poignée de recopie et glissez vers la
droite jusqu'à la colonne M (pour recopier jusque décembre), puis demandez un zoom
à 100%.
o Modifiez le chiffre d'affaires et les achats pour les mois de juillet et août : Juillet : CA
: 12 000 ; Achats : 3 500 Août : CA 10 000 ; Achats : 3 500
Réenregistrez le document, puis fermez-le
48
MISE EN PAGE ET IMPRESSION L'affichage par défaut d'une feuille est le mode d'Affichage "Normal".
Pour la mise en page et l’affichage avant impression, on utilise les autres modes d’affichage, dont
les noms respectifs sont signifiants :
- « Mise en page » et « Aperçu des sauts de page » , accessibles sous l’onglet Affichage, dans le
groupe « Affichages classeur »,
également dans la barre d’état.
- « Aperçu avant impression » : ouvrez le menu Fichier > Imprimer.
A droite, chaque page apparaît comme elle sera imprimée.
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.
I. 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 :
A. EN MODE D ’AFFICHAGE « APERÇU DES SAUTS DE PAGE »
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.
49
B. AVEC LE BOUTON « ZONE D ’IMPRESSION » DU GROUPE « MISE EN
PAGE »
Sélectionnez la plage de cellules à imprimer. Dans le groupe « Mise en page », activez le bouton « Zone d’impression » > Définir.
1. AV EC LA FEN ETR E « M I SE EN P AGE »
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.
Sur la fenêtre mise en page, ce premier onglet permet de choisir l'orientation de vos pages :
- Portrait (ou à la française) 21 x 29,7cm
- Paysage (ou à l'italienne) 29,7 x 21cm
Vous pouvez également modifier l'échelle pour agrandir ou rétrécir vos
tableaux.
o Cliquez sur le bouton compteur et mettez l'échelle à 125 % ; cliquez sur Ok pour
validez ; visualisez le résultat, puis cliquez de nouveau sur le bouton Page.
Si vous désirez réduire un tableau vous pouvez également modifier l'échelle, mais dans le
sens de la réduction, l'option Ajuster est beaucoup plus rapide.
a) L'O N G L E T MARG E
Permet de modifier les marges haute, basse, gauche et droite pour
toute la feuille de calcul.
Vous pouvez également modifier la position des en-têtes et pieds de
page que nous détaillerons tout à l'heure.
Utilisez l'option Centrage vertical et/ou horizontal qui évite bien
souvent de modifier les marges.
o Activez les options de centrage horizontal et vertical, puis
Ok. Votre tableau est maintenant au milieu de la page.
o Clique/, de nouveau sur le bouton Page afin de poursuivre la
mise en page.
50
b) L'O N G L E T EN-TÊTE E T PIED DE PAGE
Cet onglet vous permet d'avoir en haut (en-tête) et/ou en bas (pied de page) du texte, la date,
l'heure, le numéro des pages.... sur toutes les pages de la feuille travaillée.
o Excel vous propose des en-têtes ou pieds de page
prédéfinis, mais vous pouvez aussi en créer.
o Choisissez en en-tête prédéfini le nom du fichier
(STOCK) ;
o choisissez ensuite en pied de page Page 1/1;
o cliquez sur Ok pour validez et visualiser le résultat.
Voire tableau prend forme, mais avant de l'imprimer vous allez rajouter votre nom et quelques
autres renseignements.
o cliquez de nouveau sur l’onglet en-tête et pied de page.
o Pour créer vos propres en-têtes ou pied de pages :
o Cliquez sur En-tête Personnalisé ou Pied de Page Personnalisé.
o Tapez votre texte à gauche, au centre ou à droite.
o Combinez votre texte avec les icônes proposées. Notez bien la signification des
symboles (&Page,... &Onglet).
Détail des boutons
1. Mise en forme des en-têtes et pied de
pages, après sélection.
2. Numérotation automatique des pages
(&Page).
3. Nombre total de pages (&Pages).
4. Date de l'impression (&Date).
5. Heure de l'impression (&Heure).
6. Indique le chemin où se trouve le classeur
ainsi que son nom (&Chemin d'accès & Fichier)
7. Nom du classeur (&Fichier).
8. Nom de la feuille (&Onglet).
9. Insertion d'une image et possibilité de la mettre en forme.
Pour faire la mise en forme de vos en-têtes ou pied de pages, il faut sélectionner avant de
cliquer sur le bouton A.,
c) EN -T E T E P E R S O N N A L I S E :
o Cliquez dans section de gauche : tapez Le (suivi d'un espace), puis cliquez sur l'icône
vous permettant d'obtenir la date de l'impression.
o Dans la section centrale : cliquez et glissez sur & Fichier, afin de sélectionner cette
expression, puis cliquez sur le bouton A et demandez de la police Comic sans Ms,
taille 20, gras et double souligné et Ok.
o Cliquez dans la section de droite, puis cliquez sur l'icône vous permettant d'obtenir
l'heure de l'impression.
o Cliquez sur Ok pour valider votre en-tête personnalisé.
51
d) P I E D D E P A G E P E R S O N N A L I S E :
Le procédé est absolument identique.
o Cliquez dans section de gauche : tapez
votre prénom et votre nom.
o Dans la section centrale : cliquez juste
après Page&[Page], tapez une barre de fraction
(/) puis cliquez sur l'icône vous permettant
d'obtenir le nombre total de pages (voir ci-
dessous).
o Cliquez dans la section de droite, tapez : Excel 2010.
o Cliquez sur Ok pour valider votre pied de page personnalisé, puis de nouveau Ok pour
valider et visualiser vos en-tête et pied de page.
Celle fois votre: tableau a vraiment meilleure allure et vous pouvez lancer l'impression en
cliquant sur le bouton Imprimer.
Excel ouvre la boîte de dialogue concernant l'impression. Cliquez sur Ok afin de lancer votre
impression.
Réenregistrez votre classeur.
Cliquez de nouveau sur la flèche du groupe mise en page, onglet Feuille, afin de continuer les
explications sur la mise en page.
e) L'O N G L E T FEUIL LE
Grâce à l'option Titres à imprimer de l'onglet Feuille, vous avez la possibilité de récupérer
automatiquement certaines lignes et/ou colonnes sur chaque page si le document fait plusieurs
pages ; en général il s'agit très souvent de la première ligne et/ou de la première colonne.
Cette option n'est accessible que par le menu Fichier, Mise en Page...
Ressortez de l'aperçu (Échap) et appelez le menu Fichier, Mise en Page.
Dans ce menu, vous avez également la possibilité de demander le
quadrillage du tableau, si aucun encadrement n'a été fait.
Impression des formules d'un tableau :
Si vous désirez imprimer les formules d'un tableau, activez l'option En-
têtes de lignes et de colonnes, afin que vos formules soient plus faciles à lire.
Pour obtenir les formules, il faut appeler le menu Outils, Options, onglet Affichage et activer
l'option Formules. Ne le faites pas pour le moment.
Fermez le classeur « STOCK » en enregistrant les modifications.
52
II. IMPRESSION D'UN GRAND TABLEAU Ouvrez le classeur « ÉTUDE 1 ».
o Faites un aperçu.
Votre tableau se présente sur 2 pages.
o Cliquez sur le bouton Suiv pour voir la seconde page. Si vous imprimez ce tableau
sans modifier la mise en page, la seconde page ne sera pas facile à lire car la colonne
des titres n'est pas apparente.
C'est pourquoi vous allez apprendre à récupérer les titres sur toutes les pages.
o Sortez de l'aperçu.
A. LES TITRES
Pour récupérer automatiquement la première ligne et/ou la première colonne sur toutes les
pages
o Activez l’onglet Mise en page, flèche du groupe de travail mise en page, onglet
Feuille.
o Cliquez dans l'option Lignes à répéter en haut, ou Colonnes à répéter à gauche.
Dans cet exemple, vous avez besoin de récupérer la première colonne sur toutes les pages :
cliquez dans l'option Colonnes à répéter à gauche.
Cliquez ensuite dans la feuille de calcul, sur la référence de la ligne ou de la colonne à
répéter : cliquez sur la lettre A et Excel affiche « $A:$A » (cf. image écran en page suivante).
Validez en cliquant sur Ok, puis faites un aperçu pour vérifier.
À l'aperçu, on remarque (c'est inscrit en bas à gauche de l'écran) que vous avez désormais 3
pages. En effet, vous avez rajouté une colonne sur toutes les pages, ce qui fait que la mise en
page a été modifiée.
De plus, les 3 pages ne sont pas équilibrées ... la dernière est beaucoup plus petite que les
autres, nous allons donc modifier les sauts de pages en essayant de couper ce tableau sur 4
pages (une par trimestre).
Auparavant, enregistrez le classeur sous le nom « MISE EN PAGE ».
B. 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.
53
1. POUR IN S ER ER UN SAUT DE PAGE :
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.
2. POUR SUPPRI MER UN S AU T DE P AGE
Seuls les sauts de page qui ont été paramétrés (insérés par nous-mêmes), peuvent être supprimés :
sélectionnez une cellule juste en dessous (pour supprimer un saut de page horizontal), ou juste à
droite (pour supprimer un saut de page vertical) du saut de page paramétré, puis au choix :
- En mode « Aperçu des sauts de page » : faites un clic droit > Supprimer le saut de page.
- Ou bien en mode d’affichage « Normal » : activez le bouton « Sauts de page » > Supprimer le
saut de page.
3. L’AP ER ÇU DES SAUT S DE P AGE
Il est également possible avec Excel 2010 de demander à partir de l’aperçu ou par bouton
Affichage du ruban mise en page, un aperçu des sauts de page, et de modifier alors les sauts
de page en les faisant glisser avec la souris (cf. ci-dessous).
Lorsque la souris prend la forme d'une double flèche, cliquez et glissez pour bouger la ligne
(saut de page).
o Appelez le menu Affichage, et cliquez sur la commande Aperçu des sauts de page.
o Excel affiche un message, lisez-le et cliquez sur Ok.
o Positionnez votre souris sur les lignes bleues. La souris change de forme et devient
une double flèche horizontale ;
o cliquez sur la ligne bleue de pointillés qui se trouve après le mois de mai et lorsque
vous avez la double flèche, glissez pour la mettre juste après le mois de mars ;
o faites la même chose pour la ligne juste après le mois d'août et déplacez-la après le
mois de juin. Idem pour la ligne qui est après novembre que vous devez déplacer après
septembre ;
o appelez ensuite le menu Affichage et demandez Normal, afin de récupérer une
visibilité normale de votre tableau ;
Faites un Aperçu pour vérifier que vous avez de nouveau 4 pages ; Avant de lancer
l'impression, vous allez améliorer la mise en page :
o Cliquez sur le bouton Page :
o onglet Page, mettez l'échelle à 135 % ;
o onglet Marges, demandez un centrage vertical et horizontal du tableau ;
54
o onglet en-tête et pied de page : créez des en-têtes et des pieds de page personnalisés
afin d'avoir en haut à gauche Le suivi de la date, et au centre le nom du classeur en
police Times new roman, gras, taille 20. En bas, au centre demandez le numéro de
page sur le nombre total de pages et à droite tapez vos initiales ;
o validez votre mise page et, si tout vous semble correct, lancez l'impression.
4. LA ZO NE D ' I MP R ES SION
Il est possible de n'imprimer qu'une partie de tableau.
Ouvrez le classeur « ÉTUDE 1 ».
o Sélectionnez la partie à imprimer (ici de Al à E12) ;
o appelez le menu Fichier, Zone d'impression, Définir ;
o faites ensuite un Aperçu, afin de vérifier que vous
avez uniquement la partie sélectionnée ;
o en en-tête à gauche, tapez vos initiales, et au
centre tapez Zone d'impression ; lancez ensuite l'impression.
L'intérêt d'une zone d'impression est de pouvoir conserver la partie que vous imprimez
régulièrement.
Pour supprimer une zone d'impression, appelez le menu Fichier, Zone d'impression, Annuler.
Fermez le classeur « ÉTUDE 1 » sans enregistrer.
55
LES FORMULES DE CALCUL Sur le ruban, l’onglet « Formules » regroupe les commandes spécifiques aux formules.
I. TOUTE FORMULE DOIT COMMENCER PAR LE SIGNE EGAL = 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.
Excel ne distingue pas la casse (majuscule ou minuscule)
A. 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).
B. LES FONCTIONS
Une formule peut utiliser une ou plusieurs fonctions, relatives à divers domaines.
Exemples de domaines (mathématiques, lettres…) et exemples de fonctions (SOMME, NBCAR…) :
C. LES FORMULES DE CALCUL ET LES FONCTIONS
Pour réaliser des traitements plus complexes, Excel propose un certain nombre de fonctions.
Toutes ces fonctions sont reconnaissables par le signe =, leur nom suivi de ().
1. CR EATION D 'UNE FO RMULE AV EC SOMME
o Créez un nouveau classeur :
o Saisissez le tableau qui suit
o En B1, tapez 1er Trimestre et validez ;
56
o sur la cellule B1, prenez la poignée de recopie ; cliquez et glissez avec la poignée
jusqu'à la cellule El (les 4 trimestres sont saisis).
o Saisissez le texte des cellules A2 à A7,
o Pour les chiffres : afin de gagner du temps dans la saisie, créez une zone de saisie :
o sélectionnez toutes les cellules où vous désirez saisir des chiffres (B2 à E6).
o Tapez, 150, puis Entrée, tapez 120 et Entrée ...
o continuez la saisir un chiffre et à faire Entrée.
De cette manière, dès que vous serez en bas d'une colonne, Excel passera automatiquement à
la colonne suivante.
o faites la mise en forme (bordures, trames, alignements...).
o Enregistrez le classeur sous le nom « Région ».
o Placez le curseur en B7 ;
o Tapez =SOMME (
o Placez le curseur sur B2 ;
o Maintenez le bouton gauche de la souris et glissez jusque B6 ;
o Fermez la parenthèse ;
o Validez.
On notera un clignotement autour des cellules sélectionnées pour le calcul. Excel construit la
formule =SOMME(B2:B6).
Le résultat 1 040 apparaît alors en B7, et en haut de l'écran vous voyez =SOMME(B2:B6) ce
qui signifie "Somme de B2 jusqu'à B6".
La somme étant une formule que vous allez utiliser très souvent dans un tableur Excel vous
propose une icône vous permettant encore une fois de gagner du temps :
2. SOMME AUTO MATIQ UE
o Placez le curseur sur la cellule B7 ;
o appuyez sur Suppr pour effacer votre calcul.
o cliquez dans la barre d'outils standard sur l'icône :
Une zone est prédéfinie (entourée) et vous pouvez confirmer en
appuyant sur la touche Entrée.
Si la zone proposée est invalide, vous pouvez la corriger, après
l'avoir sélectionnée, directement avec la souris sur la feuille de
calcul, en cliquant et en glissant pour définir une nouvelle
plage de cellules. Vous pouvez également utiliser la touche Ctrl
pour définir des cellules non continues.
o Positionnez votre curseur dans la cellule C7;
o cliquez sur l'icône Somme automatique et validez.
57
o Faites en suite la même chose pour les cellules D7 et E7.
o Cliquez ensuite dans la cellule F2 ;
o cliquez sur l'icône Somme automatique, Excel vous propose de faire la somme des
cellules B2 à E2 ;
o validez en cliquant sur la coche verte.
o Prenez la poignée de recopie (en F2) ;
o effectuez un double clic et Excel recopie la formule jusqu'en bas.
Ne refaites pas pour le moment vos traits... car vous allez de nouveau modifier les formules.
Vous pouvez en effet lancer une somme automatique en agissant sur un tableau complet et
non plus sur une seule cellule résultat.
Sélectionnez les cellules de B7 à F7 ; appuyez sur Ctrl (maintenez la touche enfoncée) ;
sélectionnez ensuite les cellules de F2 à F6 ; appuyez sur la touche Suppr pour tout effacer ;
faites Ctrl + * pour sélectionner tout le tableau ; cliquez sur l'icône :
Tous les calculs se font automatiquement.
N'oubliez pas que le but est de gagner du temps. Alors notez bien tout ce que vous venez de
faire.
Terminez la mise en forme du tableau et réenregistrez-le.
II. LES FONCTIONS SIMPLES D'EXCEL Ouvrez le classeur « Région » s'il n'est plus à l'écran.
o Tapez le mot Moyenne dans la cellule A8 ;
o positionnez votre curseur dans la cellule B8.
Excel possède plus de 300 fonctions qui permettront de réaliser toutes les formes de calcul. Si
vous avez du mal à vous rappeler le nom, l'orthographe ou la syntaxe d'une fonction, vous
pouvez demander de l'aide en cliquant sur dans la barre de formule.
Dans ce cas, Excel affiche directement la liste des fonctions (catégorie et détail).
Pour obtenir les explications sur une fonction, cliquez avec la souris sur son nom et Excel
affiche la définition correspondante.
o Cliquez dans la catégorie des fonctions Statistiques ;
o avec l'ascenseur cherchez dans la liste, la fonction
Moyenne.
o cliquez sur Ok pour continuer.
Comme on le remarque, l'assistant fonction nous guide pas à pas
dans la définition tics arguments obligatoires et facultatifs.
Excel affiche le détail :
Attention Excel vous propose de faire la moyenne des cellules B2 à H7. Cela ne convient pas,
car dans une moyenne, il ne faut pas prendre le total.
Vous allez donc redéfinir la sélection.
o Cliquez sur le bouton rouge pour masquer lu boîte de
dialogue :
o Cliquez et glissez afin de sélectionner les cellules de B2
à B6.
o Cliquez de nouveau sur le bouton rouge afin de
réafficher la boîte de dialogue
58
o cliquez ensuite sur Ok pour valider la formule. Excel affiche le résultat dans la cellule
B8.
Cette méthode semble complexe et longue, mais lorsque l'on ne connaît pas la syntaxe d'une
fonction, c'est la seule solution. Il existe pour les fonctions courantes (moyenne, min, max...)
d’autres solutions dans cette version Excel 2010.
En dessous de l'icône Somme automatique, il y aune petite flèche indiquant une liste
déroulante.
o Cliquez dans la cellule B8 et effacez la formule ;
o cliquez sur la flèche en dessous de l'icône Somme automatique ;
o cliquez sur Moyenne, (attention car Excel propose de prendre le
o total dans la moyenne) ;
o cliquez sur la cellule B2 et glissez jusque B6 ;
o validez.
o Vous obtenez le même résultat que précédemment.
o Cliquez dans la cellule B8 ;
o prenez la poignée de recopie et tirez jusque F8 pour recopier la formule jusqu'au bout.
Réenregistrez le classeur.
Les fonctions peuvent être tapées en majuscules ou minuscules, cela n'a aucune importance du
moment que vous respectez la syntaxe.
Vous allez utiliser maintenant 2 autres fonctions statistiques permettant de trouver le plus
grand nombre d'une liste, puis le plus petit.
= MAX( : ) et = MIN ( : )
Les deux point : signifient "jusqu'à" (liste continue).
On peu éventuellement utiliser le point-virgule (;) pour dire "et"... (liste non continue).
o Dans la cellule A9, tapez le mot Maxi et Mini en A10 ;
o positionnez votre souris dans la cellule B9 ;
o cliquez sur la flèche à côté de l'icône Somme automatique ;
o choisissez la fonction Max, puis cliquez en B2 et glissez jusque B6 ;
o validez.
o Dans la cellule B10, faites les mêmes manipulations en choisissant cette fois la
fonction Min.
o Sélectionnez les cellules B9 et B10 ;
o prenez la poignée de recopie sur la cellule B10 ;
o cliquez et glissez jusqu'à la colonne F, puis lâchez la souris.
Vos 2 formules ont été recopiées en une seule fois.
o Sélectionnez les cellules A7 jusque F7 ;
o Cliquez sur l’icône de reproduction de la mise en forme :
o Cliquez, sur la cellule A8 et glissez jusque F10.
o Vous avez reproduit la mise en forme de la ligne Total.
o Faites un aperçu, viellez votre classeur en page, tapez vos initiales en en-tête.
o Lancez l'impression.
o Réenregistrez le classeur et fermez-le.
59
EXERCICE
OBJECTIF : Utilisation des fonctions statistiques de base.
Ouvrez le classeur « Trimestre ».
o Dans la cellule F3, faites la somme automatique ;
o recopiez la formule jusqu'à F11 ;
o dans la cellule G3, faites la moyenne ;
o recopiez la formule jusqu'à G11;
o dans la cellule B12, faites la somme automatique ;
o dans la cellule B13, faites la moyenne ;
o dans la cellule B14, utilisez la fonction Max ;
o dans la cellule B15, utilisez la fonction Min.
o Sélectionnez les cellules allant de B12 à B15 ;
o prenez la poignée de recopie en bas à droite ;
o glissez jusqu'à la colonne E.
Enregistrez le document en changeant son nom, et appelez-le « Trimestre 2 ». Mise en forme
du tableau :
o sélectionnez tous les chiffres et appliquez un format milliers sans décimale ;
o Mettez du gras sur vos calculs et sur les titres ;
o Mettez de l'italique sur les noms des personnes ;
o Augmentez la hauteur des lignes 1, 12, 13, 14 et 15 ;
o Appliquez une trame sur les titres ;
o Faites un contour épais autour du tableau ;
o Faites les traits verticaux et horizontaux ;
o Faites un aperçu. Mise en page du tableau :
o Mettez le tableau en paysage et centrez-le verticalement et horizontalement ;
o Demandez le nom du classeur en en-tête dans la section centrale ;
o Tapez, votre nom, dans la section droite du pied de page ;
o Lancez l'impression
o Ré-enregistrez votre classeur
60
REFERENCES RELATIVES OU
ABSOLUES Dans une formule, on peut désigner une cellule soit par sa référence Colonne/Ligne soit par son
nom (cf. « Attribution d’un nom »). Exemple : =6*quantité, si la cellule J43 a été nommée quantité.
Quand on saisit sa référence, le contour de la cellule change de couleur et s’entoure de quatre poignées, ce qui permet de bien la distinguer.
I. NOTIONS D'ADRESSES ABSOLUES/RELATIVES Une adresse relative (ou référence relative) indique un chemin à suivre, alors qu'une adresse
absolue (ou référence absolue) indique un emplacement précis.
o Ouvrez le classeur « STOCK ».
Dans ce tableau, le calcul de la TVA n'est pas satisfaisant. En effet si le taux de TVA change,
il faut modifier toutes les cellules qui le contiennent.
Or, l’intérêt d'Excel, lorsque les formules sont bien conçues, est précisément d'éviter les
modifications et les ressaisies fastidieuses.
Vous allez donc saisir le taux de TVA dans une cellule du tableau et non plus dans chaque
formule de calcul.
o Cliquez en G1 et tapez : Taux TVA ;
o en G2 tapez 19, 60 %.
o Sélectionnez les cellules allant de E2 à E6 ;
o Cliquez-les avec la touche Suppr.
o Cliquez, dans la cellule E2 où vous allez refaire la formule (HT * Taux Tva), en tenant
compte du taux qui se trouve en G2.
o Tapez = (pour démarrer la formule) ;
o cliquez dans la cellule D2 (pour prendre le montant HT) ;
o tapez *, puis cliquez sur la cellule G2 ;
o validez.
Le résultat donne toujours 196, ce qui tout à fait correct.
o Cliquez sur la cellule E2 que vous allez recopier vers le bas ;
o Prenez la poignée de recopie et tirez vers le bas (ou double-clic).
Mais là, apparait un problème ! Le calcul est 0, que se passe-t-il ?
Regardez bien dans la cellule E3, les cellules qu'Excel a pris pour son calcul : D3*G3
La cellule D3 est bonne (HT des cerisiers) mais en G3, il n'y a rien. Descendez dans la cellule
E4, Excel a pris D4 * G4, encore une fois D4 est correct, mais G4 non.
Jusqu'à présent les adresses se décalent dynamiquement en fonction de la recopie.
Lors de la recopie, Excel recopie la formule et le même chemin. Or, dans cet exemple, le taux
de TVA est toujours dans la cellule G2.
61
Pour éviter ce problème, il faudra fixer la cellule G2, c'est-à-dire « bloquer » l'adresse et
transformer une adresse relative en adresse ' absolue.
Au cours de la construction de la formule qui se trouve en E2, il faudra après avoir cliqué sur
la cellule à fixer (dans notre exemple G2), appuyer sur la touche de fonction F4 pour
transformer cette adresse en adresse absolue.
Excel insère alors des $ devant les références, et la formule contiendra : D2*$G$2
Adresse G2 bloquée par des $.
Adresse absolue invariable au cours de la recopie
À partir de ce moment, vous pourrez recopier cette formule vers le bas.
Sélectionnez les cellules de E2 à E6 ; appuyez sur la touche Suppr pour les effacer.
o Cliquez dans la cellule E2 ;
o Tapez « (pour démarrer la formule) ;
o Cliquez dans la cellule D2 (pour prendre le montant HT) ;
o Tapez *, puis cliquez sur la cellule G2 ;
o Appuyez maintenant sur la touche F4 (une seule fois, pour le moment) ; validez.
o Sur la cellule E2, prenez la poignée de recopie et tirez vers le bas ou faites un double-
clique. Le résultat semble correct.
o Cliquez dans la cellule E3 afin de vérifier les cellules qu'Excel a pris pour faire sa
formule ; D3*$G$2.
o En E4, Excel a pris D4*$G$2.
Grâce aux « $ » et à la touche F4, Excel a compris que la référence au taux de TVA m- devait
pas bouger, et vous avez résolu un problème important. En effet, vous allez le voir par la
suite, tout tableau comporte en général des données variables. Dans ce cas, il faut les mettre
en dehors des formules et à chaque fois se posera le problème de la recopie dynamique. Il
faudra donc très souvent utiliser cette touche F4.
II. LES DIFFERENTS TYPES DE BLOCAGE ;
G2 Référence relative : pas de blocage.
F4 $G$2 Référence absolue : la colonne G et la ligne 2 sont
bloquées.
2 fois F4 GS2 Référence semi-Absolue : seule la ligne 2 est bloquée.
3 fois F4 SG2 Référence semi-Absolue : seule la colonne G est
bloquée.
N'appuyez pas trop longtemps sur la touche F4, car elle continue à faire le tour.
Notez bien ces différentes façons de bloquer une cellule, car cela va vous servir par la suite.
Terminez la mise en forme du tableau : chiffres, bordures...
Dans la cellule G2, tapez 19,60 % afin de vérifier que votre tableau se recalcule avec le
nouveau taux de TVA.
62
Remodifiez ensuite la cellule G2, et tapez 19,60 %. Faites un aperçu.
Pour la mise en page : mettez votre tableau en paysage, centré verticalement et
horizontalement, tapez vos initiales en en-tête à droite.
Imprimez le classeur.
Enregistrez-le en changeant son nom : appelez-le « Touche F4 » Fermez ensuite ce classeur.
Exercice : travail sur plusieurs feuilles de calcul références semi-
relatives
Ouvrez le classeur « Service commercial »
Cliquez sur l’onglet feuil3 nommez-le « Salaires »
Effectuez un copier-coller entre les feuilles de calcul afin de récupérer le nom des salariés ;
Insérez des lignes afin de pouvoir ajouter le nom des services, les lignes de total par service,
et le nouveau salarié « Lucien » ;
Complétez ensuite la ligne des titres puis celle des taux de cotisation en ne modifiant pas la
largeur des colonnes (retour à la ligne automatique).
Enfin ajustez la largeur des colonnes afin de correspondre au tableau suivant :
Réenregistrez votre travail.
Calculez le total des salaires bruts par service ;
Calculez les charges sociales en utilisant une seule formule (utiliser la touche F4 plusieurs
fois) ;
Effectuez la mise en page de votre document, centrage, zoom, en-tête, pied de page.
63
III. ATTRIBUTION D’UN NOM A UNE (PLAGE) DE CELLULE
A. REGLES CONCERNANT LE NOM :
- Il doit être différent d’une référence ligne colonne.
- Le premier caractère doit être une lettre ou le caractère de soulignement.
- Ne pas utiliser d’espace. Le point et le caractère de soulignement peuvent le remplacer.
- Se rappeler qu’Excel ne fait pas la différence entre majuscules et minuscules, afin d’éviter
d’appeler deux cellules par le même nom.
B. ATTRIBUTION D ’UN NOM
1. DEUX METHO DES D ’ATTRI BUTION D ’UN NO M :
a) S A I S I E D A N S L A Z O N E N O M
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.
b) F E N E T R E « G E S T I O N N A I R E D E N O M S »
Elle permet de définir un nom, également de le modifier, de le
supprimer, ou de modifier les références des cellules qu’il désigne.
Pour l’afficher, activez le bouton du même nom, dans le groupe «
Noms définis » (onglet Formules), ou bien, plus rapidement, faites : Ctrl + F3.
Pour attribuer un nouveau nom, cliquez sur le bouton « Nouveau » de la fenêtre. La fenêtre «
Nouveau nom » s’affiche.
Les noms, utilisables dans toutes les feuilles d'un classeur, évitent d'avoir à mémoriser des
plages ou des références de cellules. De plus, les noms: figent les cellules, c'est-à-dire qu'ils
permettent de représenter des valeurs constantes.
Ouvrez le classeur « BUDGET ».
Cliquez sur la cellule B9 afin de visualiser la formule.
Comme vous pouvez le remarquer, la cellule B2 a été bloquée. Il en est de même pour toutes
les formules faisant référence aux cellules allant de B1 à B5.
64
IV. NOMMER UN BLOC DE CELLULES Donner à B1 à B6, les noms figurant dans la colonne A.
Sélectionner A1 :B6, sur le ruban formules dans le groupe nom cliquer
sur le bouton créer à partir de la sélection, une fenêtre s’affiche vous
demandant de préciser à partir de quelle sélection le nommage va se faire.
Cliquez sur OK à partir de la colonne de gauche ?
Vous pouvez vérifier l’attribution des noms en cliquant sur chaque cellule et en observant la
zone le nom
V. UTILISER LES NOMS
A. LORS DE LA CREATION D 'UNE FORMULE :
Refaire la formule en B10, en utilisant le nom Frais.
Recopiez votre formule vers la droite
Recopier les valeurs sans la mise en forme afin de ne pas perdre votre bordure à droite.
F3 : Coller un nom.
Vous allez ressaisir la formule de la ligne 12 en utilisant la touche F3
Effacer B12 à F12
Ressaisir les formules avec F3, recopiez
Ignorer l'erreur.
B. CRÉER UN NOM
Fermez le classeur « BUDGET » sans enregistrer les modifications. Ouvrez le classeur le
classeur « COMMISSIONS ».
De B2 à D2 Tapez Taux dans la zone de nommage puis entrée;
Affecter le nom aux formules de (B8 à D8) par le bouton Définir un nom, Appliquer un
nom…,
Fermez le classeur en enregistrant les modifications.
65
Exercice :
Insertion d’une zone de listes déroulante simple dans une cellule
Dans votre tableau, vous souhaitez insérer un texte ou une valeur choisi dans une liste
(déroulante).
vous créez votre liste, ligne après ligne, sur votre feuille en cours ou sur une autre
feuille du classeur en cours.
Sélectionner la zone choisi (A1:A6) la nommez « liste1 »:
sur l’onglet formule, cliquer sur la zone de liste déroulante définir un
nom, saisir le nom de la liste, vérifier la zone de référence
(=feuille1!$A$1 :$A$6) et validez.
(ou utiliser la zone de nom à gauche de la barre de formule).
Après validation la sélection de la zone permet de vérifier le nom
vous revenez sur votre tableau, à l'emplacement ou vous souhaitez afficher votre
choix : ex C3.
vous cliquez sur C3, puis sur l’onglet DONNEES choisir le bouton VALIDATION
des données
Sur l’onglet option, dans la lise Autoriser : choisir liste
Dans la zone Source : tapez « = liste1 » et validez.
La liste est insérée en C3 prête à être utilisée
66
PROTEGER FEUILLES ET CLASSEURS Protection de cellules protection - contre l'ouverture d'un document confidentiel
I. PROTEGER DES CELLULES Ouvrez le classeur « BUDGET »
Comme tout fichier, on peut protéger un classeur lors de son premier
enregistrement, en utilisant la fenêtre « Enregistrer sous » du ruban fichier. 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.
Il existe divers autres moyens pour protéger classeurs et feuilles de calcul.
II. PROTECTION D ’UN CLASSEUR
A. MARQUER COMME FINAL
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ésactiver le marquage, effectuez l’opération inverse pour désactiver l’option « Marquer
comme final ».
B. « PROTEGER LE CLASSEUR » - STRUCTURE ET FENETRES
Pour afficher la fenêtre « Protéger le classeur », activez le bouton « Protéger le classeur » du
groupe « Modifications », sous l’onglet Révision.
Ou bien : ouvrez le menu Fichier > « Protéger le classeur » > « Protéger la structure du
classeur ».
67
Suite à la protection de la feuille, le bouton « Protéger le classeur » devient le bouton « Oter la
protection de la feuille ».
C. PROTECTION D ’UNE FEUILLE - PROTECTION D ’UNE SELECTION DE
CELLULES
1. MASQ UER UN E FEUI LLE
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.
III. SUPPRESSION DU MOT DE PASSE Fichier, Enregistrer sous, Outils, Options générales ; (touche Suppr),
Fermez le classeur « BUDGET ».
68
GESTION MULTI-FICHIERS
I. LA CONSOLIDATION Données, Consolider, permet de consolider de plus gros tableaux.
Ouvrez le classeur « PRODUCTION ».
Cliquez sur la feuille Trimestre 1 ; positionnez le curseur en B2 ; appelez l’onglet
Données, bouton Consolider.
Vérifiez que votre curseur soit positionné dans la zone des références.
Cliquez sur la feuille Janvier, dans la cellule B2 et glissez jusqu’en E8, Ajouter ; feuille
Février ; Ajouter ; Mars.
Cliquez sur OK
Avantage de cette méthode :
Vous n'avez écrit aucune formule.
Vous avez la possibilité de changer la fonction à tout moment
Onglet Données, bouton Consolider ; liste des fonctions Moyenne ;
II est possible de conserver les différents résultats ; il vous suffit alors de vous positionner
dans une autre cellule avant d'appeler l’onglet Données, bouton Consolider.
En cas de modification de l'une des feuilles, il faut relancer la commande Données,
Consolider afin de réactualiser la consolidation (en étant placé dans la première cellule à
partir de laquelle vous désirez faire la consolidation).
69
La consolidation peut être automatique si l'on active l'option Lier aux données source, mais
dans ce cas, Excel crée un plan du tableau. Le mode Plan peut être très intéressant pour
certaines applications et vous l'étudierez plus loin...
D'autre part, sachez qu'il est tout à fait possible de réaliser une consolidation entre différents
classeurs, comme vous le verrez dans le premier exercice
Dans la feuille Trimestre 1, en B2 ; Données, Consolider ; fonction Somme
Enregistrez le classeur sous le nom « PRODUCTION 2001 ». Fermez le classeur.
70
Exercice
OBJECTIF : Réaliser une consolidation avec 2 classeurs.
Ouvrez les classeurs « Région. Région 1 et Région 2 » en une seule fois :
Réorganiser votre écran afin des les voir tous les trois en même temps.
Effectuer la consolidation dans le classeur Région des tableaux région 1 et région 2 (de Al à
F5) en utilisant l fonction somme. Copiez votre résultat en cellule A8 afin d’obtenir le tableau
de synthèse et d’appliquer la fonction moyenne.
Effectuez la mise en forme des tableaux, hauteur des lignes (21,75), trame, bordures.
Enregistrez le classeur en changeant son nom : appelez-le « TOTAL RÉGION ».
Faites la mise en page en précisant vos nom et prénom dans le pied de page.
Fermez le classeur en enregistrant les modifications
Fermez les autres classeurs en une seule fois sans enregistrer les modifications.
71
II. LES GROUPES DE TRAVAIL Excel vous offre la possibilité de créer ou de travailler sur plusieurs feuilles en même temps.
Il suffit pour cela de se mettre en groupe de travail.
Créez un nouveau classeur.
Pour mettre en groupe plusieurs feuilles qui se suivent :
• cliquez sur la première feuille à mettre dans le groupe ;
• appuyez sur la touche Maj, et maintenez-la ;
• cliquez sur la dernière feuille à mettre dans le groupe.
Si les feuilles ne se suivent pas, utilisez alors la touche Ctrl. C'est dans la barre de titre que
vous verrez si vous êtes en groupe.
A partir du moment, où vous êtes en groupe de travail tout ce qui se fait dans une feuille se
répercute automatiquement dans toutes les cellules correspondantes de toutes les feuilles
mises en groupe.
La mise en page de plusieurs feuilles peut se faire également grâce au groupe, mais attention
dans ce cas, il faut utiliser le menu Fichier, Mise en Page (et non Aperçu, Page) pour que
toutes les feuilles soient bien mises en page en une seule fois.
Pour sortir du groupe, cliquez sur une feuille en dehors du groupe. Si toutes les feuilles du
classeur ont été mises en groupe, le fait de cliquer sur une autre feuille vous sort du mode
groupe de travail
La méthode du groupe de travail permet de gagner beaucoup de temps en création,
impression...
À tout moment, vous pouvez l'activer ou en sortir.
Fermez le classeur sans enregistrer.
72
Exercice : groupe de travail et consolidation
Créez un nouveau classeur ;
Insérez un quatrième feuille de calcul ;
Mettez-vous n groupe de travail sur les 4 feuilles ;
Effectuez une saisie conforme au tableau ci-dessous.
Effectuez la mise en page en conservant le groupe de travail ;
Passez l’échelle à 130 %, Centrage horizontal et vertical ;
L’en-tête comporte vos initiales à gauche, au centre, le nom de l’onglet ;
Le pied de page comporte au centre le numéro de la page sur le nombre total de page.
Sortez du groupe de travail et nommez les feuilles de calcul : feuil1 : Valence, feuil2 : Lille,
feuil3 : Lyon, feuil4 : Récap.
Créez une zone de saisie et tapez les valeurs suivantes pour chaque tableau
Enregistrez votre travail sou le nom de « Filiales françaises »
Effectuez la somme des trois tableaux sur la feuille de calcul Récap (Deux solutions
possibles).
73
LES AUTRES LES FONCTIONS
COURANTES
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).
I. 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.
A. FONCTION SANS ARGUMEN T
La formule =AUJOURDHUI() renvoie la date courante.
La formule =ALEA() renvoie une valeur aléatoire, qui n’est jamais la même, entre 0 et 1.
La formule =MAINTENANT() renvoie la date et l’heure courante.
B. FONCTIONS AVEC UN ARGUMENT
La formule =MOYENNE(3:3) renvoie la moyenne des valeurs de la ligne 3. Les valeurs qui
ne sont pas numériques sont ignorées.
La formule =ENT(SOMME(E6:G8)) renvoie la partie entière de la somme des valeurs de la
plage E6:G8.
C. FONCTION AVEC DEUX ARGUMENTS
La formule =DROITE($H$6;4) ou GAUCHE()renvoie les 4 derniers (ou premiers) caractères
du texte contenu dans la cellule H6.
La référence étant absolue, le texte de référence sera toujours celui de la cellule H6, quelle
que soit la cellule d’écriture de la formule.
74
II. QUELQUES EXEMPLES La formule =ARRONDI(B3 ;2) renvoie un résultat arrondi au plus proche à 2 décimales de
B3
La formule =ARRONDI.SUP(B3 ;2) renvoie un résultat arrondi supérieur à 2 décimales de
B3
La formule =ARRONDI.INF(B3 ;2) renvoie un résultat arrondi inférieur à 2 décimales de B3
La formule =TRONQUE(valeur;nombre de décimales) affiche le chiffre avec le nombre de
décimales voulu. Est l'équivalent de la fonction =arrondi.inf() .
Fonctions s'appliquant à du texte
Excel offre aussi des fonctions pour chercher de l'information sur du texte. En voici quelques-
unes.
La formule =DROITE(texte;nombre de caractères) Cette fonction va afficher les derniers
caractères d'une cellule ayant du texte. ex.: =DROITE("Ceci est un exemple";2) = le
La formule =GAUCHE(texte;nombre de caractères) Affiche le nombre de caractères que vous
avez choisi du début du texte. Ex.: =GAUCHE("Ceci est un exemple";2) = Ce
La formule =CONCATENER(première cellule;seconde cellule) Cette fonction vous permet
de regrouper le contenu de plusieurs cellules ensemble même si le contenu des cellules est
différent comme du texte, des chiffres, le résultat d'une formule ou des dates. Ex.:
=CONCATENER("Ce";45) = Ce45
La formule =NBCAR(cellule ou texte) Affiche le nombre de caractères contenu dans une
cellule.
La formule =ROMAIN(chiffre) Convertis un chiffre sous forme arabe en chiffre romain. Ex.:
=ROMAIN(45) = XLV
La formule =TEXTE(chiffre;format texte) Convertis un chiffre en format texte. Il prend la
présentation comme le reste des cellules ayant du texte. Il est encore possible de l'utiliser pour
les opérations mathématiques. Ex.: =TEXTE(45;0) = 45 mais collé à la bordure gauche de la
cellule.
Fonctions s'appliquant sur des dates
Excel offre aussi des fonctions permettant de retirer de l'information à partir de cellules ayant
des dates. Ces cellules contiennent beaucoup d'informations. Voici les fonctions les plus
utilisées.
La formule =MAINTENANT() Affiche dans la cellule choisie le moment exact ou vous avez
confirmé la formule.
75
La formule =DATE() Affiche seulement dans la cellule la date de l'ordinateur.
La formule =ANNEE(cellule ayant une date) Affiche l'année contenue dans la date.
La formule =MOIS(cellule ayant une date) Affiche le mois contenu dans une date. Le résultat
est toujours entre 1 et 12.
La formule =JOUR(cellule ayant une date) Affiche le chiffre du jour contenu dans une date).
Le résultat est toujours entre 1 et 31.
La formule =JOURSEM(cellule ayant une date) Affiche un chiffre qui représente le jour de la
semaine: 1 = dimanche, 2 = lundi ... 7 = samedi. On peut le combiner à la fonction =si() ou
=rechercheh() pour affiche en texte le jour de la semaine.
La formule =HEURE() Affiche l'heure d'une cellule ayant une date. Le résultat est toujours
entre 0 et 23.
La formule =MINUTE() Affiche les minutes d'une cellule ayant une date. Le résultat est
toujours entre 0 et 59.
La formule =SECONDE() Affiche les secondes d'une cellule ayant une date. Le résultat est
toujours entre 0 et 59.
76
LA FONCTION SI
I. DEFINITION 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)
L’expression Test contient nécessairement un opérateur logique : égal = , supérieur > ,
supérieur ou égal >= , inférieur < , inférieur ou égal <= .
Les deux résultats suivants ( Résultat_si_Test_vrai et Résultat_si_Test_faux ) peuvent
contenir des fonctions, des calculs, différents types d’expression (Date, Texte, Pourcentage…)
etc.
La fonction SI peut n’être dotée que de deux arguments.
Exemple :
Sur le tableau suivant, =SI() le montant de la
commande du client est supérieur ou égal à 1525 €
alors ( ;) le texte Remise 5% s’affiche (les guillemets
sont indispensables pour insérer du texte) sinon ( ;)
rien ne s’affiche (« »)
Placez-vous sur la cellule qui contiendra le résultat, tapez =SI(B2>=1525 ; «remise 5%» ; « »)
Ou sur l’onglet formule cliquez sur le bouton Logique, option SI et complétez la
fenêtre d’assistance de fonction comme suit
Validez et recopiez vers le bas
77
Exercice :
Ouvrez le classeur « achats »
Sélectionnez les onglets feuil1 et feuil2pour vous mettre en groupe de travail ;
Terminez la saisie comme ci-dessous des colonnes C, D et E ;
Faites la mise en forme des tableaux (en restant en groupe de travail) ;
Orientation paysage, centrage vertical et horizontal, En en-tête de page tapez votre prénom, au
centre le nom de l’onglet (taille 20, gras, double souligné). En pied de page, le nom du fichier
à gauche, au centre le numéro de page sur le nombre de page.
Sortez du groupe de travail ;
Renommez les onglets : Feuil1 : Semestre 1, Feuil2 : Semestre 2, Feuil3 : Année ;
Réenregistrez votre classeur.
Calculez la remise de telle sorte que le contenu de la cellule C2 s’affiche si le montant du 1er
semestre est supérieur à 1 525 €, sinon rien ne doit s’afficher (utiliser les guillemets), recopiez
la formule vers le bas ;
Calculez le montant de la remise si le pourcentage s’affiche dans la colonne précédente, sinon
rien ne doit s’afficher (utiliser la même base de formule avec CA du 1er
semestre. Supérieur à
1 525 €).
Calculez le total de la même façon que les deux formules précédentes, vous obtiendrez le
résultat suivant.
78
Il est possible d'imbriquer jusqu'à sept fonctions SI comme arguments valeur_si_vrai et
valeur_si_faux pour élaborer des tests plus complexes :
Microsoft Feuille Semestre 2
Saisissez les chiffres de la colonne B, ainsi que le petit tableau ci-dessous :
Pour calculer le montant de la remise, Vous devez utiliser le petit tableau de la cellule A20
Remarque :
Commencez par tester la condition la plus importante à savoir B3> 760 €, dans ce cas le
contenu de la cellule B22 sinon si B3>=450 vous prendrez le contenu de la cellule B21, sinon
rien.
Dans ce genre de situation, vous devrez utiliser 2 fonctions SI() imbriquées ainsi que la
touche F4.
Pour le calcul du montant de la remise et du total, une seule condition suffit, puisqu’a partir
du moment où le chiffre d’affaires est supérieur à 450 € le montant doit être calculé, sinon
c’est le chiffre d’affaires brut qui s’affichera.
Les résultats devront être les même que l’exemple suivant :
Réenregistrez votre classeur, puis cliquez sur l’onglet Année
Saisissez les libellés conformément au tableau suivant
79
Calculez avec la somme automatique le total des ventes et des remises du 1er
semestre puis du
2nd
;
Pour trouver le pourcentage de remise sur ventes, demandez le calcul remise/ventes puis
appliquez un format pourcentage à 2 décimales.
Pour trouver le nombre de remises, utilisez la fonction =NB().
Préparez ensuite la mise en forme de votre classeur (revenez en groupe de travail et confirmez
la mise en page déjà faite.
LA FONCTION NB.SI
La fonction NB.SI compte le nombre de cellules à l'intérieur d'une plage qui répondent
à un critère donné.
=NB.SI(plage;critère)
Plage représente la plage de cellules dans laquelle
vous voulez compter les cellules.
Critère représente le critère, exprimé sous forme de
nombre, d'expression ou de texte, qui
détermine les cellules à compter :
Le mot Mardi apparaît trois fois dans la plage de cellules (B3 :B8)
II. LA FONCTION SOMME.SI La fonction SOMME.SI Additionne des cellules spécifiées si elles répondent à un critère
donné.
=SOMME.SI(plage;critère;somme_plage)
Plage représente la plage de cellules sur lesquelles baser la fonction.
Critère représente le critère, sous forme de nombre, d'expression ou de
80
texte, définissant les cellules à additionner.
Somme_plage représente les cellules à additionner.
• Les cellules comprises dans l'argument somme_plage sont
additionnées si et seulement si les cellules correspondantes situées
dans l'argument plage répondent au critère.
• Si l'argument somme_plage est omis, ce sont les cellules de l'argument plage qui sont
additionnées.
81
FONCTIONS DE RECHERCHE Excel dispose de fonctions de recherche qui permettent de rechercher une valeur et d'en
renvoyer une autre. Pour illustrer ce chapitre, vous allez utiliser la fonction RECHERCHEV
(recherche verticale).
Cette fonction permet de chercher une valeur dans la première colonne d'un tableau, puis de
renvoyer une valeur se trouvant dans la même ligne, à partir d'une colonne spécifiée.
Si la première colonne du tableau n'est pas triée par ordre croissant, l'argument "Valeur
proche" devient obligatoire. Utilisez l'icône AZ dans la barre d'outils Standard, si vous désirez
trier la colonne.
La syntaxe de la fonction RECHERCHEV est : =RECHERCHEV(vaIeur cherchée;table
matrice;n°index colonne ...)
Suivant la valeur recherchée, Excel va prendre dans le tableau le contenu de la colonne que
vous allez indiquer.
Créez un nouveau classeur. Faire la saisie comme ci-dessous :
De B4 à D6 dans la zone Nom tapez PRODUIT, en Cl, saisissez une valeur (1, 2 ou 3) ;
En C2, écrivez la formule de recherche :
=RechercheV(Cl; PRODUIT; 3)
Excel cherche alors dans la table PRODUIT, la valeur saisie dans la cellule C1, et prend le
contenu de la 3eme
colonne.
Dans la cellule Cl, saisissez une autre valeur
Si vous ne saisissez rien dans la cellule C1, Excel va afficher un message d'erreur #N/A.
Dans le point suivant, vous allez étudier d'autres fonctions d'Excel qui permettent d'éviter des
messages d'erreurs.
82
III. LES FONCTIONS D'INFORMATION Pour supprimer le message #N/A, il existe plusieurs solutions c'est-à-dire fonctions
=ESTVIDE(réf cellule) : renvoie vrai si l'argument valeur fait référence à une cellule vide.
=ESTTEXTE(réf cellule) : renvoie vrai si l'argument valeur fait référence à du texte.
=ESTNUM(réf cellule) : renvoie vrai si l' argument valeur fait référence à un nombre.
=ESTERREUR(réf cellule) : renvoie vrai si l'argument valeur fait référence à une valeur
d'erreur (#N/A, #VALEUR!, #REF!...).
Cliquez dans la cellule C2 et vous allez modifier la formule afin de ne plus avoir le message
d'erreur. Il faut donc, dans ce cas, utiliser la fonction conditionnelle SI() afin de dire à Excel
que si la cellule Cl est vide, alors il faut ne faut rien afficher dans la cellule C2, sinon la
recherche peut s'effectuer.
Tapez en C2 la formule suivante :
=SI(ESTVIDE(Cl);"";RECHERCHEV(cl;produit;3))
Enregistrez le classeur dans votre dossier sous le nom « RECHERCHE ».
Modifier votre tableau afin d'y ajouter des calculs.
Effacez maintenant la cellule C1 et Excel affiche un nouveau message d'erreur pour les
calculs de TVA et TTC (#Valeur), qui signifie que vous effectuez une opération avec des
caractères au lieu des valeurs.
Tester la fonction ESTTEXTE.
=SI(ESTTEXTE(C2) ;"";C2*19,60%)
Pour la formule du TTC, utiliser ESTNUM afin de tester cette nouvelle fonction
En G2 =SI(ESTNUM(G1);G1+C2;"")
Il reste cependant un problème, en effet, si vous tapez un code supérieur à 3, Excel calcule
alors avec le code le plus proche.
Pour résoudre ce nouveau problème, il faut ajouter à la recherche un 4ème
argument qui sera 0
ou Faux.
Dans la cellule C2, modifiez la formule :
=SI(ESTVIDE(C1);"";RECHERCHEV (C1 ;produit;3;0))
83
Excel ne prend plus la valeur la plus proche, mais en revanche affiche de nouveau le message
#N/A....
Que faire ?
Ajouter à la formule, la fonction ESTERREUR sur la recherche afin d'indiquer que si le
résultat de la recherche est faux, elle affiche "Code erroné", en revanche, si la recherche est
correcte, elle continue sa recherche et affiche la valeur recherchée.
Dans la cellule C2, tapez la formule suivante :
=SI(ESTVIDE(C1);"";SI(ESTERREUR(RECHERCHEV(c1;produit;3;0));"Code
erroné";RECHERCHEV(C1;produit;3;0)))
Réenregistrez le classeur, puis fermez-le.
Remarque : en cas d’erreur vous pouvez obtenir de l’aide avec Outils, Audit de formule
Si besoin illustration par une erreur de calcul simple (texte/chiffre)
84
EXERCICE
OBJECTIF : Création d'une facture en utilisant les fonctions de recherche et d'information.
Ouvrez le classeur « VINS »
Celui-ci contient pour le moment une feuille nommé Facture, et une feuille contenant la Table
des vins. Sélectionnez la table des vins et nommez-la TABLE. Dans la feuille FACTURE, il
faut créer toutes les formules.
Saisissez une référence dans la cellule A2 (un chiffre entre 1 et 10, pour le moment).
Formule de la désignation :
Dans la cellule B2, créez une formule permettant de rechercher la désignation à partir de là
référence saisie.
Attention, vous devez tester si la colonne A est vide et si la référence saisie n'est pas erronée,
dans ce dernier cas, demandez l'affichage du message « Réf inconnue ».
Votre formule doit donc contenir les fonctions : Si, Estvide, Esterreur et RechercheV.
Avant de continuer l'exercice, testez votre formule : effacez la référence dans la colonne A
afin de vérifier qu'Excel n'affiche rien.
Saisissez ensuite une fausse référence et voyez si Excel affiche « Réf inconnue ».
Pour gagner du temps et surtout éviter de refaire pratiquement la même formule pour le prix
unitaire, bloquez avec la touche F4, la colonne A.
Solution :
=SI(ESTVIDE($A2);"";SI(ESTERREUR(RECHERCHEV($A2;table;2;0));"Réf
inconnue";RECHERCHEV($A2;table;2;0)))
Formule du prix unitaire :
85
Copiez la formule de la désignation, puis modifiez-la afin d'indiquer à Excel, que les prix
unitaires se trouvent dans la colonne 3 de la table et que si la référence est inconnue, on ne
veut rien dans la colonne.
Saisissez un chiffre pour la quantité.
Formule du montant :
II s'agit de faire une multiplication entre le prix unitaire et la quantité, mais attention si aucune
référence n'est saisie ou si la référence est inconnue, Excel va afficher le message #Valeur, car
il ne trouvera rien dans la colonne Prix Unitaire. Il faut donc de nouveau utiliser la fonction
conditionnelle Si et tester si la colonne A est vide ou si la référence est inconnue.
Vous pouvez copier la formule du prix unitaire et la modifier.
Testez ensuite que tous les cas fonctionnent bien, recopiez vos formules vers le bas jusqu'à la
ligne 14.
Créez les formules pour le montant HT et la TVA sachant que le taux a été saisi dans la
cellule D16, et enfin pour le total TTC.
Faites la mise en forme du tableau comme ci-dessous.
Enregistrez votre classeur en changeant le nom : appelez-le « FACTURE VINS ».
B2 : =SI(ESTVIDE($A2);"";SI(ESTERREUR(RECHERCHEV($A2;table;1;0));"Réf inconnue";RECHERCHEV($A2;table;2;0)))
C2 : =SI(ESTVIDE($A2);"";SI(ESTERREUR(RECHERCHEV($A2;table;3;0));"";RECHERCHEV($A2;table;3;0)))
D2 : =SI(ESTVIDE($A2);"";SI(ESTERREUR(RECHERCHEV($A2;table;1;0));"";C2*D2))
86
LES CALCUL MATRICIELS ET LES
COMPLEMENTS
I. TABLE À SIMPLE ENTRÉE Un des intérêts principaux des tableurs est de pouvoir faire des simulations.
Lorsque vous faites un emprunt, vous souhaitez connaître le montant de la mensualité en
fonction de la durée de l'emprunt.
Pour cela, Excel possède une fonction financière VPM, qui permet de calculer une annuité ou
une mensualité.
La syntaxe de la formule VPM est : =VPM(Taux;Npm;Va;Vc;Type)
Taux : le taux d'intérêt du prêt par période.
Npm : le nombre total de versements pour rembourser le prêt.
Va : la valeur actuelle (d'origine).
Vc : la valeur future, c'est-à-dire la valeur finale (0 pour un emprunt).
Type : valeur logique : 1 pour un paiement en début de mois, ou 0 en fin de mois.
Dans ce type de table, nous ne travaillerons qu'avec une seule valeur variable : la durée.
Créez un nouveau classeur.
Entrez, tout d'abord, les éléments de votre emprunt :
Sur l’onglet Formule, dans la catégorie financier, choisissez fonction VPM, puis OK.
Indiquer les paramètres suivants
Le taux (B2) doit être divisé par 12 pour travailler en mensualités.
87
La durée (B3) doit être multipliée par 12 pour l'obtenir en mois.
Le montant (B1) est négatif car il s'agit de calculer un emprunt.
La valeur finale est 0.
Les mensualités sont prélevées en début de mois, donc il faut taper 1.
Vous obtenez 870,86.
Afin de connaître le montant à rembourser suivant une durée variable, modifiez votre tableau
comme ci dessous :
Sélectionnez la plage des cellules contenant les valeurs à substituer, (A5 à B13), Onglet
Données, bouton analyse et scénario, Table de données, cellule d'entrée en colonne (car les
valeurs à substituer sont présentées en colonne), cellule B3 qui est la référence de la cellule
dont on veut faire varier le contenu.
Calcul de l'intérêt total payé : en C5 faire la formule suivante : Mensualité * 12 * durée de
l'emprunt diminué du montant emprunté.
= B5*12*B3-B1
Enregistrez votre classeur sous le nom « EMPRUNT ».
Sélectionnez de nouveau la plage des cellules contenant les valeurs à substituer, en y incluant
la colonne C : (A5 à Cl3), appelez l’onglet Données, bouton analyse et scénario, Table de
données, cellule d'entrée en colonne B3 qui est la référence de la cellule dont on veut faire
varier le contenu
Effectuez une mise en forme rapide.
Préparez l'impression en ajoutant un en-tête et un pied de page dans
lequel vous taperez votre nom.
Réenregistrez le classeur et fermez-le.
88
II. TABLE À DOUBLE ENTRÉE Après avoir fait varier une valeur, vous allez maintenant en faire varier deux.
Ouvrez le classeur « EMPRUNT ».
Nommez la feuille 1 : Table simple copiez cette feuille nommez cette feuille : Table double,
effacez les cellules allant de B5 à C13 ;
Modifier le tableau :
Enregistrez le classeur en changeant son nom, appelez-le « TABLE ».
Vous allez maintenant faire varier la durée et le taux.
Sélectionnez les cellules de A6 à F14, Données, Table, cellule d'entrée en ligne concerne le
taux : B2, la cellule d'entrée en colonne concerne la durée : B3
Les calculs sont faits et, de cette manière, vous obtenez les mensualités calculées suivant
différents cas (taux, nombre d'années).
Faites une mise en forme rapide, créez un format personnalisé Ans sur les cellules allant de
A7 à A14;
Réenregistrez le classeur puis fermez-le.
89
III. LE GESTIONNAIRE DE SCENARIO Cette fonction d'Excel vous permet de « stocker » différentes hypothèses dans une même
cellule (C.A. optimiste et C.A. pessimiste par ex.) et de pouvoir activer rapidement l'une des
hypothèses.
A. CRÉER UN SCENARIO
Pour illustrer cette possibilité : créez le tableau ci-dessous :
Saisir les cellules de B2 à D3, calculez les ventes prévisionnelles (Résultat année précédente
+ Résultat année précédente *% Augmentation), saisissez les prix de vente, calculez le
C.Affaires (chiffre d'affaires) prévisionnel (Ventes prév * P.Vente).
Enregistrez le classeur sous le nom « SCENARIO ».
Vous allez maintenant utiliser ce tableau pour faire varier le pourcentage d'augmentation des
ventes afin de vérifier les différents résultats pour l'année suivante.
Sélectionnez les cellules pour lesquelles vous voulez faire des essais avec des
valeurs différentes (ici les pourcentages d'augmentation : B3 à D3) ; Données
Analyse et scénarios, scénarios. Ajouter pour créer un premier scénario ;
Tapez un nom pour le scénario : Bon, puis OK, ne modifiez pas les valeurs de ce premier
scénario, Ajouter, tapez un nom : Mauvais, OK, modifiez les valeurs,
Ajouter afin de créer un troisième scénario tapez un nom : Moyen, OK modifiez les valeurs,
puis OK.
Cliquez sur Mauvais et Excel affiche dans votre tableau les résultats avec les pourcentages
définis dans ce scénario.
Pour voir les résultats, cliquez et glissez dans la barre de titre afin de bouger votre fenêtre
Gestionnaire de scénarios.
90
Cliquez ensuite sur Moyen, puis fermez la fenêtre. Votre tableau se présente avec les valeurs
du scénario Moyen, appelez de nouveau le menu Outils, Gestionnaire de scénarios ;
Cliquez ensuite sur l'option Synthèse ;
Vérifiez dans la zone Cellules résultantes, qu'Excel a bien détecté les 3 cellules concernant le
résultat, puis validez avec OK.
Faites un aperçu de cette nouvelle feuille ; créez un en-tête et un pied de page où vous
préciserez le nom de la feuille (& onglet), votre nom et la date de l'impression.
Réenregistrez le classeur puis fermez-le.
91
IV. LES PLANS Vous avez très souvent à travailler sur de grands tableaux. Pour simplifier leurs présentations,
vous pouvez les diviser en plan.
Ouvrez le classeur « RÉSULTAT ».
Vous allez, avant de créer le plan, modifier ce tableau afin d'y ajouter des colonnes
intermédiaires de calculs pour obtenir un total par trimestre, par semestre et le total général.
Sélectionnez les colonnes E, H, I, Insertion, Colonnes, en E1, tapez Trim , en E2, faites la
Somme automatique et validez, recopiez votre formule jusqu'en bas,
En I1, tapez Trim 2, faites la somme automatique en I2 puis recopiez ;
En J1, tapez Semestre 1, et en J2, faites le Somme automatique, puis validez.
Continuez sur le même principe pour Trim 3, Trim 4, Semestre 2 et Total Général ;
Sélectionnez ensuite les cellules de D1 à D12, Reproduire la mise en forme, cliquez et glissez
sur les cellules allant de El à T12, afin d'appliquer la même mise en forme à toutes les
colonnes.
Enregistrez ce classeur en changeant son nom, appelez-le « PLAN ».
Votre tableau est maintenant très large et il est difficile de voir les principaux totaux ; c'est
pourquoi, nous allons créer un plan.
A. CRÉER UN PLAN
Sélectionnez les colonnes B, C, et D, onglet Données, Grouper et créer un plan,
choisissez ensuite l'option Grouper.
Excel génère un premier regroupement, et vous remarquez la présence d'une barre au-dessus
de la feuille de calcul.
B. EFFACER UN PLAN
Appelez le menu Données, Grouper et créer un plan, choisissez ensuite l'option
Effacer le plan.
C. CREER AUTOMATIQUEMENT UN PLAN
Dans votre tableau, les colonnes de calculs sont en cascade : chaque trimestre est la somme
des trois mois, chaque semestre est la somme des deux trimestres précédents, l'année la
somme des deux semestres. Cette condition de calcul en cascade est impérative pour utiliser
cette méthode de réalisation de plans. Les lignes de calculs sont organisées de la même façon.
Appelez l’onglet Données, Grouper : choisissez ensuite l'option Plan automatique.
Excel a automatiquement généré un plan à la fois sur les lignes et sur les colonnes.
92
D. UTILISER UN PLAN
La création d'un plan génère l'apparition de barres, de symboles mais également de numéros.
-Affiche les niveaux 1 et 2 du plan
- Affiche les niveaux 1, 2 et 3 du plan
-Affiche les niveaux 1, 2, 3 et 4 du plan
Pour obtenir l'exemple ci-dessous, il faut cliquer sur le niveau 3 pour les colonnes et sur le
niveau 2 pour les lignes.
Fermez le document « PLAN » sans enregistrer les modifications.
93
EXERCICE
OBJECTIF : Création d'un plan et utilisation des vues.
Ouvrez le classeur « PLAN ».
Créez de manière rapide un plan horizontal et vertical.
Créez ensuite grâce au bouton personnalisé situé sur l’onglet affichage 3 vues que
vous nommerez Vuel, Vue2 et Vue3 et qui représenteront respectivement :
Vue 1 : tous les niveaux (affichage de tout le
tableau).
Vue 2 : les niveaux 1 en ligne et en colonne.
Vue 3 : les niveaux 1, 2, et 3 en colonne et
les niveaux 1 et 2 en ligne.
Demandez l'impression de la vue 3, sans
oublier de faire une mise en page rapide et surtout n'oubliez pas de mettre votre nom en en-
tête ou pied de page.
Enregistrez ce classeur sous le nom « VUES et PLAN ».
94
LES BASES DE DONNEES
I. CONCEPTS DE BASE Une base de données est ce que nous appelons en langage courant un fichier. Une base de
données est un ensemble d'enregistrements de même nature et de même structure. Les
enregistrements sont eux-mêmes composés de champs. Chaque enregistrement a exactement
le même nombre de champs. Chacun de ces champs a un nom, l'ensemble de ces noms
formant la structure. Excel permet théoriquement de disposer de 1 048 576 enregistrements et
de 16 384 champs, le formulaire de saisie (ou masque) se limite à 32 champs !
A. CREER LA BASE DE DONNEES
1. UTILI SATION DU FOR MUL AI R E DE S AI SIE
Créez un nouveau classeur :
Saisissez comme sur le modèle, le nom des champs sur la ligne 1.
Les noms de champs doivent être impérativement en ligne.
Le bouton Formulaire (version Excel 2003) n’a pas été ajouté au ruban, mais vous pouvez
toujours l’utiliser dans des versions de Microsoft Office Excel ultérieures à Excel 2007 en
ajoutant le bouton Formulaire à la barre d’outils Accès rapide.
Ajouter le bouton Formulaire à la barre d’outils Accès rapide
1. Cliquez sur la flèche en regard de la barre d’outils Accès rapide, puis cliquez sur
Autres commandes.
2. Dans la zone Choisir les commandes dans les catégories suivantes, cliquez sur Toutes
les commandes.
3. Dans la zone de liste, sélectionnez le bouton Formulaire , puis cliquez sur Ajouter.
Excel affiche alors le formulaire de saisie.
95
1. Bouton de passage à un nouvel enregistrement (nouvelle saisie).
2. Suppression d'un enregistrement.
3. Annule les modifications apportées à un enregistrement, mais ne
restaure pas un enregistrement supprimé.
4. Revient à l'enregistrement précédent.
5. Passe à l'enregistrement suivant.
6. Recherche les enregistrements correspondant à un critère.
7. Ferme le formulaire de saisie
La saisie se fait dans le formulaire avec la touche Tabulation qui vous permet d'accéder au
champ suivant.
Maj + TAB permet de revenir au champ précédent.
Lorsque la saisie d'un enregistrement est terminée, cliquez sur Nouvelle ou Entrée.
Pour arrêter la saisie, cliquez sur Fermer et le formulaire de saisie se ferme.
L'ensemble des informations que vous aurez saisi sera désormais inclus dans la base de
données.
B. LA SAISIE DIRECTE DANS LA FEUILLE
Vous pouvez également saisir les enregistrements directement dans les cellules. Mais les
manipulations en saisie ou modifications peuvent se révéler beaucoup plus hasardeuses.
Si vous préférez saisir sans utiliser le formulaire de saisie, vous pouvez créer une zone de
saisie : sélectionnez toutes les cellules où vous voulez saisir ; utilisez la touche Tabulation
pour une saisie en ligne ; ou la touche Entrée pour une saisie en colonne ; Effectuez la saisie,
soit par le formulaire, soit en direct, de la petite base de données présentée en annexe.
Enregistrez ensuite le classeur sous le nom « BASE ».
II. LE TRI Bien que cette fonctionnalité ne soit pas propre aux bases de données, nous l'abordons; ici car
c'est à ce niveau qu'elle est la plus significative.
Cliquez dans le champ à trier (positionnez-vous dans la colonne A, afin de trier les noms).
2 possibilités s'offrent à vous pour effectuer un tri : Cliquez dans l’onglet accueil sur le
bouton : AZ ; pour un tri croissant ou sur ZA pour un tri décroissant.
Les noms ont été triés par ordre croissant ou décroissant suivant l'icône sur laquelle vous
avez cliqué. Vous remarquerez qu'Excel a trié l'ensemble de l'enregistrement et pas
seulement les noms, et heureusement !
96
La 2nd
possibilité permet de trier plusieurs champs en une seule fois : Cliquez dans la
base de données ; appelez l’onglet Données, bouton Trier.
Excel affiche une boîte de dialogue : Choisissez de trier en priorité sur les villes, par ordre
croissant ; en second, demandez un tri sur les noms par ordre croissant ; validez avec OK.
Excel a trié les villes par ordre alphabétique croissant,
et suivant les villes, les noms ont été également triés
par ordre croissant. Vous pouvez vérifiez avec la ville
de Lille.
Triez toujours en priorité les champs où il y a le plus
de renseignements identiques afin que votre résultat soit judicieux et compréhensible.
Effectuez ensuite un tri sur le champ Somme de manière décroissante.
Faites ensuite un tri judicieux sur les Sommes et sur les Villes dans un ordre croissant.
Fermez le classeur sans enregistrer.
97
EXERCICE
1. OBJECTIF : Création et tri d'une base de données.
Créez un nouveau classeur :
Saisissez la base de données présentée ci-dessous.
Vous pouvez saisir le formulaire de saisie ou normalement. L'ancienneté n'est pas à saisir
mais à calculer
=Ent((Aujourdhui()-dated'entrée)/365,25)
Effectuez ensuite une mise en forme rapide de la base. Tapez votre nom en pied de page.
Enregistrez ce classeur sous le nom « FICHIER COMMERCIAUX ».
Faites un tri simple par ordre croissant sur les régions ;
Faites ensuite un second tri judicieux sur 2 champs : Nom et Produit par ordre croissant ;
préparez l'impression en précisant en saisissant en-tête Tri sur les noms et sur les produits.
Enregistrez le classeur en changeant son nom : appelez-le « TRI » puis fermez-le.
98
III. RECHERCHER DES ENREGISTREMENTS Excel dispose d'un système de filtre automatique qui vous permet de rechercher les
enregistrements qui correspondent à un critère.
A. LES FILTRES AUTOMATIQUES
Ouvrez le classeur « BASE ».
Onglet Données, bouton Filtrer.
Excel affiche alors une ligne d'en-tête modifiée où chaque champ dispose d'une liste
déroulante permettant de sélectionner les enregistrements :
Listes déroulantes permettant de choisir un critère
Pour faire apparaître les listes déroulantes vous pouvez aussi utiliser le
bouton Mettre sous forme de tableau et de choisir dans la palette de
couleur un modèle qui vous convient
Cliquez sur la liste déroulante des villes ; choisissez LILLE.
Excel affiche maintenant uniquement les enregistrements correspondant au critère choisi, en
l'occurrence les personnes habitant à LILLE.
Vous noterez également que qu’un filtre est apparu à coté de la flèche de filtre. C'est un
repère vous permettant de savoir s’il y a un filtre appliqué ou non sur un champ.
Cliquez sur la liste déroulante du champ Ville ; cochez sélectionner tout
afin de récupérer tous les enregistrements.
Autre possibilité de critère sur les champs numériques : cliquez sur la
liste déroulante du champ Somme ; choisissez filtre numériques, 10
premiers.
Une boîte de dialogue s'affiche : demandez les 3 éléments les plus hauts
Excel affiche alors les 3 enregistrements dont la somme est la plus élevée.
99
Cliquez sur la liste déroulante du champ Somme ; cliquez sur Tous afin
de récupérer tous les enregistrements.
Lors de l’application d’un filtre sur un champ texte le sous menu s’adapte
au type de champs choisit
II est également possible de créer des critères personnalisés. Si vous désirez par exemple
obtenir toutes les personnes dont le nom commence par un B :
Cliquez sur la liste déroulante du champ Nom ; choisissez
Personnalisé ; une boîte de dialogue s'affiche : tapez B* et validez.
Excel affiche alors les 2 enregistrements dont le nom commence par
un B.
Il existe 2 symboles qui vous permettent de tester : * remplace un ensemble de caractères et,
dans ce cas, peu importe le nombre. ? Remplace un seul caractère.
Cliquez sur la liste déroulante du champ Nom ; cliquez sur Tous afin de récupérer tous les
enregistrements ; cliquez sur la liste déroulante du champ Nom ; choisissez Personnalisé ;
tapez ?i* et validez.
Vous obtenez tous les noms dont la 2e1
"" lettre est un "i".
Vous avez sans doute remarqué qu'Excel transforme votre critère Égal à, en Commence par
qui est également une seconde solution pour vous.
Il est également possible de créer des critères sur des champs différents : conservez votre
critère sur le champ Nom ; cliquez sur la liste déroulante du champ Ville ; choisissez
Personnalisé ; tapez 1* et validez.
Excel affiche alors 2 enregistrements correspondant aux personnes dont la 2nd
lettre est un
« i » et qui habitent une ville dont le nom commence par un « i ».
Comme vous le voyez, il est très simple avec cette méthode de Filtres automatiques
d'obtenir des enregistrements correspondants à vos critères.
Entraînez-vous quelques minutes. Refaites tous les critères que vous venez de tester afin de
bien assimiler toutes les manipulations.
Vous pouvez, lorsque vous avez fait des filtres sur différents champs, demandez
le réaffichage de tous vos enregistrements en cliquant sur le bouton effacer
Ce qui vous évite de cliquer sur les différentes listes déroulantes et de choisir.
Pour sortir du mode Filtres appuyez de nouveau sur le bouton Filtre
Fermez le classeur sans enregistrer les modifications.
100
IV. LES FILTRES ELABORES Pour effectuer des recherches encore plus complexes, vous pouvez passer par
l’onglet Données, Filtre avancé
Dans ce cas, vous devrez créer, au préalable, un champ critère sur la feuille de calcul et entrer
les critères dans différentes cellules.
Un critère est composé d'au moins deux lignes dont la première qui reprend la ligne d'en-tête.
Chaque critère se trouve dans une cellule.
Ouvrez le classeur « BASE ».
Sélectionnez la ligne des champs (Al à El) et copiez-la en ligne 12 ; dans la cellule A13, tapez
B* ; en D13, tapez L* et en dessous en D14, tapez B*.
ligne 1 signifie ET ligne 2 signifient OU
Ces critères vont permettre de trouver les personnes dont le nom commence par un B ET qui
habitent une ville dont la première lettre et un L OU un B.
Pour faire un critère avec un « ET », il faut que les données recherchées soient sur la même
ligne et éventuellement dans plusieurs colonnes. Pour faire un critère avec un « OU », il faut
que les données recherchées soient sur des lignes différentes (au minimum 2).
Cliquez dans une cellule de la base de données ; cliquez sur le bouton Filtre avancé.
Cliquez et glissez sur l'emplacement de la zone de critères (A12 à E14) ; cliquez sur OK pour
valider.
Excel a trouvé 2 enregistrements correspondants à votre critère.
Vous allez maintenant créer un critère sur le champ Somme, afin d'extraire les
enregistrements dont la somme est supérieure à 1 300 et inférieure à 2 000.
Supprimez le contenu des cellules allant de A13 jusqu'à E14. Copiez le champ Somme dans la
cellule F12. En E13, tapez >1300, et en F13, tapez <2000.
Cliquez dans la base et appliquez un filtre élaboré ; cliquez dans la Zone de critères, puis
cliquez et glissez dans la feuille de calcul afin d'indiquer le nouvel emplacement de la zone de
critères (E12 à F13) ; validez.
Excel a trouvé 3 enregistrements correspondants à votre critère.
Créez un autre filtre élaboré afin de trouver les enregistrements correspondants au: critères
suivants :
Les personnes dont la 3ème
lettre du nom est un R ou bien les personnes habitant Lyon.
Vous devez obtenir 3 enregistrements (Riri, Barbie + Picsou qui habitent à Lyon),
101
Il est bien évident, que lorsque vous le pourrez, vous uti1iserez de préférence les filtres
automatiques. Mais, il y a quelques situations où vous ne pourrez; pas le faire.
Imaginez, par exemple, que vous voulez avoir la base entière d'un côté e le résultat ailleurs. Il
faut, dans ce cas, faire une extraction de données e pour cela utiliser obligatoirement les filtres
élaborés et une zone de destination.
Fermez le classeur sans enregistrer les modifications.
EXERCICE
OBJECTIF : Utilisation de filtres dans une base de données.
Ouvrez le classeur « FICHIER COMMERCIAUX ».
Demandez le mode Filtres automatiques ;
Recherchez les commerciaux travaillant dans la région NO et imprimez le résultat ;
Recherchez les 7 premiers commerciaux ayant réalisé les ventes les plus importantes, et
imprimez le résultat ;
Recherchez les 4 commerciaux ayant réalisé les ventes les plus basses, et imprimez le résultat
Recherchez ensuite les commerciaux entrés entre le 1 Janvier 1994 et le 31 décembre 1999 (il
y en a 7), imprimez le résultat ;
Trouvez enfin les commerciaux qui vendent de la formation dans la région SE et dont le nom
commence par un D ou un S (il y en a 2), imprimez le résultat.
Désactivez le mode filtre automatique.
Vous allez maintenant utiliser les filtres élaborés.
Préparez la zone de critères ;
Effectuez un premier filtre élaboré afin de trouver les commerciaux qui vendent du matériel ;
Modifiez ensuite votre critère et éventuellement la zone de critères afin de trouver les
commerciaux qui travaillent dans la région SE, qui ont une ancienneté supérieure ou égale à 5
ans et inférieure ou égale à 15, ou qui vendent du matériel.
102
Enregistrez le classeur en changeant le nom : appelez-le « Filtre élaboré 1 ».
Lancez l'impression (en paysage et sur une page) du résultat obtenu ainsi que de la zone de
critères.
Créez de nouveau une zone de critères et tapez un critère permettant de trouver le personnes
qui s'appellent DURANT.
Vous obtenez 3 enregistrements dont un qui est faux ; en effet on veut obtenir DURANT mais
pas DURANTON.
Pour cela, vous devez créer un filtre tout à fait spécial.
Tapez dans la zone de critère, en dessous du champ NOM, la formule suivante :
="=DURANT"
Relancez le filtre et cette fois vous obtenez que les DURANT et non tous les noms :
commençant par DURANT.
Notez bien cet exemple qui pourra vous servir dans l'élaboration des filtres élaborés.
Fermez le classeur « FICHIER COMMERCIAUX » sans enregistrer les modifications.
V. EXTRAIRE DES DONNEES Vous avez la possibilité d'extraire les résultats dans un champ particulier (et non plus avoir
une "vue" de votre champ de données). Pour obtenir une extraction, vous devez passer par un
filtre élaboré et créer un champ de sortie.
Vous pouvez, pour le champ de sortie, créer un en-tête qui reprend certaines zones ou laisser à
Excel la possibilité d'extraire toutes les zones. Si vous ne souhaitez que certaines zones, vous
pouvez créer un en-tête quelques lignes en dessous des critères et reprendre les zones qui vous
intéressent (préférez le copier-coller à la frappe pour éviter les erreurs dans la reprise des
noms de zones).
103
Ouvrez le classeur « BASE ».
Sélectionnez la ligne des champs (A1 à E1) et copiez-la en ligne 12 ; dans la cellule A13,
tapez B* ; en D13, tapez F* et en dessous en D14, tapez B* ; préparez le champ de sortie
(zone d'extraction) ; sélectionnez la ligne des champs (Al à El) et copiez-la en ligne 20;
Positionnez votre curseur dans une cellule de la base cliquez sur Filtre avancé ; la zone
Plages est déjà définie puisque vous aviez positionné votre curseur dedans ; cliquez dans
l'option Zones de critères, puis cliquez et glissez dans votre feuille de calcul afin de
sélectionnez les cellules de A12 à E13 ;
Activez ensuite l'option Copier vers un autre emplacement afin de pouvoir définir la zone
de destination (A20 à E20) ; validez.
Le résultat de votre extraction se trouve maintenant en ligne 21 et suivantes.
Vous noterez que la définition du champ de sortie correspond à une seule ligne (le nom des
champs dont vous avez besoin).
Il est possible également de créer la zone de critères et la zone d'extraction sur des feuilles
différentes. Sélectionnez toutes les cellules de A12 à E23 ; Effacer tout ;
Sélectionnez les cellules Al à El et cliquez sur l'icône Copier ; cliquez sur l'onglet Feuil2 et
Coller ; cliquez ensuite sur l'onglet Feuil3 et Coller ; dans la feuille 2, tapez votre critère
dans la cellule E2 pour obtenir les personnes qui ont une somme supérieure ou égale à 1 525
Venez ensuite sur la feuille 3 (feuille ou vous désirez extraire les données) puis cliquez dans
une cellule de la ligne 3.
Si vous positionnez votre curseur sur la ligne 1 ou sur la ligne 2, Excel va penser qu'il s'agit de
l'emplacement de votre base de données et va alors vous indiquer qu'une base nécessite au
moins 2 lignes ; c'est pourquoi vous devez positionnez votre curseur sur une autre ligne que
les lignes 1 et 2.
Appelez le menu Données, Filtrer, Filtre élaboré ; cliquez dans la zone Plages, cliquez sur
l'onglet Feuil1 et glissez afin de sélectionner les cellules allant de Al à E8 ; cliquez dans
l'option Zones de critères, puis cliquez sur l'onglet Feuil2 et glissez afin de sélectionnez les
cellules de Al à E2 ;
Activez ensuite l'option Copier vers un autre emplacement afin de pouvoir définir la zone de
destination : onglet Feuil3 (Al à El) ; validez.
104
VI. LES FONCTIONS LIEES AUX BASES DE DONNEES Les fonctions de calcul liées aux bases de données commencent par BD et leurs arguments
sont toujours : Base de données;champ;critère.
Pour faciliter la sélection des emplacements de la base et du critère, vous allez, dans un
premier temps, nommer la base de données ainsi que le critère.
Ouvrez le classeur « FICHIER COMMERCIAUX ».
Sélectionnez les cellules allant de Al à G19 ; cliquez dans la zone nom, à gauche de la barre
de formule et tapez le mot BASE ;
Préparez votre zone de critères, afin d'effectuer des calculs sur les produits matériels ;
Nommez ensuite votre zone : CRIT.
Les emplacements de la base et du critère étant maintenant nommés, vous allez saisir la
formule suivante en I4.
BDSOMME(BASE ;"Ventes";CRIT) validez votre formule ;
Dans les cellules en dessous, faites les formules afin d'obtenir la moyenne (BDMOYENNE),
le maximum (BDMAX) et le minimum (BDMIN) des ventes sur les produits « Matériel ».
Vous devez obtenir les résultats ci-dessous :
Modifiez ensuite votre critère pour travailler sur les produits « Formation » et les calculs se
refont automatiquement.
Enregistrez le classeur en changeant son nom et appelez-le « Fonctions BDD ». Fermez
ensuite le classeur.
105
VII. LES SOUS-TOTAUX Excel vous permet d'introduire des totalisations intermédiaires dans votre base de données.
Cette génération d'opération de totalisation se fait à chaque rupture. Afin d'obtenir des
résultats intéressants, vous devez trier au préalable votre base de données sur la zone
correspondant à votre souhait de totalisations intermédiaires.
Après avoir trié vos données, placez le curseur à l'intérieur de la base de données
(peu importe où) et affichez l’onglet Données, bouton Sous-totaux.
Ouvrez le classeur « FICHIER COMMERCIAUX ».
Faites un tri par ordre croissant sur les produits. Affichez l’onglet Données, bouton Sous-
totaux.
Dans la zone A chaque changement de, choisissez Produit ; dans la zone Utiliser la fonction,
choisissez Somme ; Dans la zone Ajouter un sous-total, désactivez Ancienneté et activez
Ventes ; Cliquez sur OK pour valider votre opération.
Excel a généré automatiquement un mode Plan qui vous permet de choisir l'affichage qui
vous convient le mieux. Cliquez sur le chiffre 2 afin d'obtenir un résultat comme ci-dessous.
A. TOTALISATION GLOBALE
Pour récupérer votre base sans les sous-totaux, affichez l’onglet Données, bouton Sous-
totaux, et cliquez sur la commande Supprimer tout.
Vous avez, d'autre part, la possibilité de faire différentes opérations de sous-totaux (par
exemple, demander la somme et la moyenne). Dans ce cas, vous devrez désactiver la
commande Remplacer les sous-totaux existants.
Si vous avez supprimé les sous-totaux, refaites un sous-total (somme) sur les produits ;
affichez l’onglet Données, bouton Sous-totaux; dans la zone Utiliser la fonction, choisissez
Moyenne ; désactivez l'option Remplacer les sous-totaux existants ; validez.
Les sous-totaux peuvent alors être imprimés. Si vous souhaitez revenir en saisie avec le
formulaire ou directement sur la feuille, vous avez intérêt à retirer cette option de sous-
totalisation. Ne le faites pas pour le moment.
Enregistrez le classeur en changeant son nom, appelez-le « SOUS-TOTAUX ». Fermez le
classeur.
106
Les tableaux croise s dynamiques 2010
I. INTRODUCTION Un tableau croisé dynamique est un outil qui permet de ressortir rapidement une synthèse
à partir d’une masse de données. Il est possible de comparer une variable, une donnée, par
rapport à un autre (aspect croisé). Mais la partie la plus intéressante est l’aspect dynamique.
Le tableau croisé se recalcule rapidement après que vous ayez apporté des changements. Vous
pouvez ainsi faire des analyses plus approfondies ou essayer plusieurs combinaisons très
rapidement. Le prochain exercice consiste à créer un tableau croisé dynamique et de voir les
options principales.
II. AVANT DE COMMENCER Pour suivre cet exercice, vous pouvez aller
chercher le classeur demos-excel2007-
3.xlsx
Il faut des données pour être capable de
générer votre premier tableau croisé
dynamique. Mais ces données doivent être
organisées d’une certaine façon.
A. AVANT DE CREER UN TAB LEAU
Avant de commencer, il faut une base de données. Il est possible de créer et de gérer des bases
de données simples à partir d'Excel. Il y a certains termes que vous devez connaître.
Champ Caractéristique sur une personne, une chose ou un événement qui doit être
conservé dans une base de données.
Enregistrement Série de champs qui décrivent une personne, une chose ou un événement.
Dans une base de données Excel, chaque colonne représente un champ.
Le nom du champ doit être sur la première ligne.
Chaque ligne suivante représente un enregistrement.
Afin qu'Excel soit capable de reconnaître tous les enregistrements qui composent la base
de données, il est important de ne pas laisser des lignes vides.
Toutes les lignes après le nom des champs doivent avoir des enregistrements.
La base de données suivante conserve des données sur les employés d'une entrepris
Dans une liste de données, le nom des variables doit être sur la première ligne. Aucune
colonne ne doit être laissée sans noms.
Toutes les lignes suivantes doivent contenir des données.
Vous ne pouvez pas laisser des lignes vides; tout doit se suivre.
107
Placez le pointeur sur l’une des cellules de la liste de données.
Sélectionnez l’onglet Insertion.
Appuyez sur le bouton Tableau croisé dynamique.
Assurez-vous d’avoir la bonne sélection de cellules.
Il est préférable de placer le tableau dans une nouvelle feuille
de calcul.
Appuyez sur le bouton OK.
Voici votre premier aperçu de votre tableau croisé dynamique vide.
Vous devez le remplir avec les champs qui sont dans le volet à la droite
de l’écran ou créer vos propres champs calculés.
Note :
La liste des champs de tableau croisé dynamique va disparaître si vous
cliquez à l'extérieur du tableau. Sélectionnez une cellule du tableau
croisé dynamique pour ravoir accès à ses options.
Le tableau croisé dynamique est composé de quatre zones. Chacune à une fonction
particulière.
B. LES QUATRE ZONES
Filtre du rapport Permets de filtrer les données du tableau sur un ou
plusieurs champs.
Étiquettes de
colonnes
Regroupe les valeurs communes ensemble et affiche le
résultat de chaque valeur dans une colonne.
Étiquettes de lignes Affiche les résultats de la synthèse de chaque valeur sur
une ligne différente.
Valeurs Affiche les résultats des fonctions demandées (somme,
nombre, moyenne …)
Selon les valeurs affichées sur les lignes et les
colonnes.
C. REMPLIR LE TABLEAU
Cochez la case à côté du champ Salaire.
Le contenu sera automatiquement placé dans la zone Valeur puisqu’il
s’agit de chiffres. Le tableau va aussi choisir par défaut l’option Somme
108
des valeurs regroupées. Vous verrez comment changer l’opération un peu plus loin dans le
texte.
Cochez la case à côté du champ Titre.
La première description est automatiquement placée dans la zone
des étiquettes de lignes. Chaque ligne représente l’une des valeurs
du champ (Administrateur, Ouvrier …)
Sélectionnez la variable Sexe.
Cette variable s’ajoute aussi dans la zone des étiquettes de lignes. Cela
fait un tableau qui est plus difficile à lire. Il serait préférable de déplacer
ce champ dans la zone des étiquettes de colonnes.
D. DEPLACER UN CHAMP
De la fenêtre de la liste des champs à la droite de l’écran, sélectionnez le
champ Sexe de la zone des étiquettes de lignes.
Sélectionnez l’option Déplacer dans la zone Étiquettes de colonnes.
OU
Placez le pointeur par-dessus le bouton Sexe de la zone des étiquettes de
lignes.
En gardant un doigt sur le bouton gauche de la souris, déplacez le champ dans la zone des
étiquettes de colonnes.
Voici un tableau qui est maintenant plus facile à lire parce qu’un champ a été déplacé de zone.
E. FILTRER LES CHAMPS
Il y a des moments ou vous ne voulez pas avoir une vision globale des données mais plutôt
filtrer sur un groupe qui pourrait mieux vous éclairer votre analyse. Le tableau croisé offre
plusieurs façons de filtrer
1. F ILT RER UN CHAMP SIT U E DANS LA LI ST E DES DONN EE S
De la liste des champs, appuyez sur le triangle pointant vers le bas
à la fin de la barre du champ Titre.
Vous verrez la liste des options pour trier et filtrer le champ que vous avez
choisi. Parmi les filtres, vous allez voir une liste des valeurs contenu dans le
champ. Vous pouvez décider de filtrer et de retirer certaines de ces valeurs.
109
De la liste des valeurs du champ, décochez les cases devants Ouvrier et Vendeur.
Cela va vous laisser seulement les employés qui travaillent au bureau et non ceux qui sont sur
la route.
On constate que le total des employés est moindre qu'auparavant puisqu'il y a seulement les
administrateurs et les secrétaires. Remarquez aussi qu'il y a l'icone d'un filtre à coté de
Étiquettes de lignes.
2. F ILT RER UN CHAMP SIT U E DANS LE T ABLEAU CROIS E DY N AMIQ UE
En plus de filtrer un champ actif du tableau, vous pouvez aussi filtrer directement du tableau
croisé dynamique.
Appuyez sur le à la fin de l'étiquette de lignes.
Cette même option est disponible pour l'étiquette de colonnes. La même
liste d'options va apparaître.
De la liste des options, sélectionnez Effacer le filtre de Titre.
Filtrer le tableau sur un champ qui n’est pas dans le tableau (filtre du rapport)
La zone Filtre du rapport peut filtrer les données des champs sans que ceux-ci apparaissent
dans le tableau croisé dynamique.
Appuyez sur le bouton à la fin du champ Catégorie.
De la liste des valeurs de champs, décochez la case pour les valeurs 1 et 2.
Le filtre est activé pour le champ. Mais celui-ci n'est pas encore placé dans l'une des zones du
tableau croisé dynamique.
De la liste des champs, placez le pointeur par dessus le champ Catégorie.
Appuyez sur le bouton droit de la souris.
110
De la liste des options, sélectionnez Ajouter au filtre de rapport.
Un tableau apparaît avec le champ Catégorie dans la zone des filtres de rapport. Il filtre le
tableau puisque les soldes de plusieurs valeurs, ainsi que le grand total, sont moindres que
dans le tableau initial. La prochaine étape consiste à retirer le champ Catégorie de la zone de
filtre du rapport.
De la liste des champs, décochez le champ que vous ne désirez plus voir dans le tableau croisé
dynamique.
Ajouter un champ
Vous n’êtes pas limité à utiliser un seul champ la zone des valeurs. Le prochain exercice
consiste à ajouter un champ pour savoir combien de personnes sont dans le groupe.
De la liste de champs, sélectionnez encore une fois le champ Salaire.
En gardant un doigt sur le bouton gauche de la souris, déplacez le champ Salaire encore une
fois dans la zone Valeurs.
Renommer les champs et changer ses propriétés
Vous avez maintenant deux fois le champ Salaire dans la zone valeur. Mais vous en avez
besoin d’un de ces champs pour déterminer la masse salariale et l’autre pour connaître le
nombre de personnes qui sont dans ce groupe.
Vous avez deux façons de sélectionner un champ.
111
Dans le tableau croisé dynamique, cliquez dans la case Somme de salaires.
De l’onglet des options de tableau croisé dynamique, vous pouvez changer le nom du champ
ou sélectionner l’option Paramètres des champs de valeur.
OU
Faire un double-clic dans la cellule Somme de salaires.
OU
Dans la zone des valeurs, placez le pointeur par-dessus la case Somme de salaire.
Appuyez sur le bouton droit de la souris.
Sélectionnez l’option Paramètres des champs de valeurs.
OU
Faire un double-clic dans la cellule Somme de Salaire.
À partir de cette fenêtre, vous pouvez changer le nom du
champ ainsi que certaines propriétés.
Faire une Synthèse par Nombre.
Changez le nom du champ à Nombre d’employés.
Appuyez sur le bouton OK.
Sélectionnez le champ Somme de salaire2.
Changer le nom à Salaires.
Appuyez sur le bouton OK.
Le tableau croisé dynamique montre maintenant le nombre de personnes des regroupements
ainsi que leur masse salariale. Le nom des champs a aussi été changé pour mieux représenter
le contenu.
112
Retirer un champ
Il y a deux façons de retirer un champ du tableau.
De la liste des champs, décochez le champ que vous ne désirez plus voir dans le tableau croisé
dynamique.
OU
De la liste des zones, sélectionnez le champ à retirer.
Appuyez sur le triangle à la fin du champ.
De la liste des options, sélectionnez Supprimer le champ.
Retirez le champ Sexe de la zone Étiquettes de colonnes.
Ajouter un champ calculé
Il vous est possible d’ajouter des champs calculés dans le tableau croisé dynamique pour
mieux comprendre les données. Le prochain exercice consiste à ajouter un champ Calculé
Bénéfices qui représente 50% du salaire des employés.
De l'onglet des Options du tableau croisé dynamique, appuyez sur le bouton Formules.
Sélectionnez l’option Champ calculé.
Dans la case Nom, entrez le texte Bénéfices.Sélectionnez la case Formule.
Comme pour les formules dans les cellules, les champs calculés commencent avec un « = ».
De la liste des champs, sélectionnez le champ Salaire. Appuyez sur le bouton Insérer un
113
champ. Retournez à la case Formule. Ajoutez * 0,5 à la formule. Appuyez sur le bouton
Ajouter.
Appuyez sur le bouton OK.
Il y a maintenant un nouveau champ disponible. Vous pouvez l'utiliser comme les autres.
Appuyez sur le bouton OK.
Déplacez le nouveau champ Bénéfices dans la zone des valeurs.
Regrouper les champs
La prochaine étape consiste à la création de deux groupes : Bureau et Extérieur. Le premier
groupe inclura les administrateurs et les secrétaires. Le second inclura les ouvriers et les
vendeurs.
Dans le tableau croisé dynamique, sélectionnez la cellule ayant le texte Administrateur.
En gardant un doigt sur la touche CTRL, sélectionnez la cellule ayant le texte Secrétaire.
De l’onglet des options du tableau croisé dynamique, sélectionnez l’option Grouper la
sélection.
OU
Appuyez sur le bouton droit de la souris.
De la liste des options, sélectionnez Grouper.
114
Il y a maintenant un regroupement pour les valeurs Administrateur et Secrétaire. Le bouton
sert à masquer les valeurs. Vous pouvez toujours les réafficher en appuyant sur le bouton .
Il faut ensuite créer un regroupement pour les valeurs Ouvrier et Vendeur.
Dans le tableau croisé dynamique, sélectionnez la cellule ayant le texte Ouvrier (celui
sans la boite ).
En gardant un doigt sur la touche CTRL, sélectionnez la cellule ayant le texte
Vendeur (celui sans la boite ).
De l’onglet des options du tableau croisé dynamique, sélectionnez l’option Grouper
la sélection.
Changer les noms des groupes
Placez le pointeur dans la cellule ayant le texte Groupe1.
Appuyez sur la touche F2 et modifiez le titre à Bureau.
OU
Placez le pointeur dans la cellule ayant le texte Groupe1.
Placez le pointeur dans la barre formule et modifiez le titre à Bureau.
Changez le nom du regroupement Groupe2 à Extérieur.
115
Actualiser un tableau croisé dynamique
Contrairement aux formules que vous utilisez tous les jours, le tableau croisé dynamique ne se
met pas à jour lorsque vous modifiez une donnée. Il faut le forcer à s’actualiser.
Retournez à la feuille de calcul Données.
Allez à la cellule F12.
Changez le salaire d’Aline St-Pierre de 22 500 $ à 27 000 $.
Retournez à la feuille de calcul ayant le tableau croisé dynamique.
Vous remarquerez que le total des salaires est toujours le même soit 394 400 $.
De l’onglet complémentaire Options de tableau croisé dynamique, appuyez sur le bouton
Actualiser.
OU
Appuyez sur le touches Alt et F5.
Le grand total des salaires va passer de 394 400 $ à 398 900 $.
Afficher les options d'un tableau croisé dynamique
Toutes les commandes reliées au tableau croisé dynamique se retrouvent sur deux onglets
contextuels : Options et Création.
116
À partir de l’onglet Création, vous pouvez décider d’afficher les totaux et sous-totaux, les
titres des lignes et des colonnes ainsi que de changer la présentation du tableau.
Voir un graphique croisé dynamique
Excel 2007 vous permet aussi de représenter les données sous forme d’un graphique croisé
dynamique. Vous pouvez changer les champs de la même façon que pour un tableau.
De l’onglet des Options du tableau croisé dynamique, appuyez sur le bouton Graphique
croisé dynamique.
De la liste des types de graphiques disponibles, sélectionnez un histogramme empilé en 3D.
Appuyez sur le bouton OK.
117
Placez le pointeur par dessus le titre du groupe Bureau.
Appuyez sur le bouton droit de la souris.
Du menu contextuel, sélectionnez les options Développer/Réduire et Réduire.
Répétez la même opération pour le groupe Extérieur.
Le graphique offre moins de détails, mais regroupe les détails des groupes en une seule barre.
Les graphiques croisés dynamiques offrent plusieurs options pour représenter ce que vous
désirez. Il y a premièrement le volet pour les filtres du graphique. Il y a aussi quatre onglets
contextuels pour gérer tout ce que vous pouvez réaliser avec un graphique.
118
LES FORMULAIRES Excel dispose d'une barre d'outils Formulaire permettant de créer des formulaires à l'aide de
différents objets, tels des boutons, des cases à cocher qui vous permettent d'afficher ou de
saisir des données.
I. LA BARRE D'OUTILS FORMULAIRE • Activez l’onglet développeur, groupe contrôle, bouton insérer.
Toutes les icônes de cette nouvelle barre d'outils ne seront pas utilisées
dans ce chapitre, mais détaillons celles indiquées :
SY N T H E S E D E S C O N T R O L E S D E F O R M U L A I R E
Nom du
bouton Exemple Description
Étiquette
Identifie l’objectif d’une cellule ou d’une zone de
texte, ou affiche un texte descriptif, tel que des
titres, des légendes ou des images, ou des
instructions concises.
Zone de
groupe
Regroupe les contrôles liés en une seule unité
visuelle dans un rectangle éventuellement assorti
d’une étiquette. En général, les cases d’option, les
cases à cocher ou les contenus étroitement liés sont
regroupés.
Bouton
Exécute une macro qui effectue une action quand
un utilisateur clique dessus. Un bouton est
également appelé un bouton de commande.
119
Case à
cocher
Active ou désactive une valeur qui indique un choix
contraire sans ambiguïté. Vous pouvez sélectionner
plusieurs cases à cocher sur une feuille de calcul ou
dans une zone de groupe. Une case à cocher peut
être dans l’un des états suivants : sélectionné
(activé), vide (désactivé) et mixte (combinaison
d’états activé et désactivé comme dans les
sélections multiples).
Case
d’option
Autorise un choix unique dans un ensemble limité
de choix mutuellement exclusifs ; une case d’option
est généralement contenue dans une zone de groupe
ou un cadre. Une case d’option peut être dans l’un
des états suivants : sélectionné (activé), vide
(désactivé) et mixte (combinaison d’états activé et
désactivé comme dans les sélections multiples).
Une case d’option est également appelée bouton
radio.
Zone de
liste
Affiche une liste comportant un ou plusieurs
éléments de texte parmi lesquels un utilisateur peut
faire son choix. Utilisez une zone de liste pour
afficher une grande quantité de choix dont le
nombre ou le contenu peut varier.
Zone de
liste
déroulante
Associe une zone de texte à une zone de liste pour
créer une zone de liste déroulante. Une zone de liste
déroulante est plus compacte qu’une zone de liste,
mais nécessite que l’utilisateur clique sur la flèche
vers le bas pour afficher la liste des éléments.
Utilisez une zone de liste déroulante pour permettre
à l’utilisateur de taper une entrée ou de choisir un
seul élément de la liste. Le contrôle affiche la
valeur actuelle dans la zone de texte, quelle que soit
la valeur entrée.
Barre de
défilement
Permet de faire défiler une plage de valeurs lorsque
vous cliquez sur les flèches de défilement ou que
vous faites glisser la case de défilement. De plus,
vous pouvez faire défiler une page (intervalle
prédéfini) de valeurs en cliquant dans la zone située
entre la case de défilement et l’une ou l’autre des
flèches de défilement. L’utilisateur peut
généralement taper une valeur de texte directement
dans la cellule ou dans la zone de texte
correspondante.
120
Toupie
Permet d’augmenter ou de réduire une valeur,
comme un incrément de numéro, d’heure ou de
date. Pour augmenter la valeur, cliquez sur la flèche
vers le haut ; pour la réduire, cliquez sur la flèche
vers le bas. L’utilisateur peut généralement taper
une valeur de texte directement dans la cellule ou
dans la zone de texte correspondante.
II. CRÉER UN FORMULAIRE Créez un nouveau classeur :
• saisissez les données présentées ci-dessous.
Dans cet exemple, il s'agit de créer un bouton compteur pour choisir le prix du bureau dont la
valeur minimale est 150 et maximale 1 600 avec une incrémentation de 10.
Une zone de groupe transports doit être créée avec 2 cases à cocher (on cochera 152 € si le
HT est supérieur à 380 et on cochera 76 € si on désire opter pour un transport fragile).
Une seconde zone de groupe doit être créée pour le montant de la remise avec des boutons
d'option car la remise sera de 10 ou 20 % : 10 % si le HT est inférieur à 762 6, sinon 20 %.
Le total à payer sera donc égal au HT plus éventuellement le transport, avec une remise de 10
ou 20 % sans oublier d'ajouter le montant de la TVA.
Le tableau « Résultat » est présenté page suivante.
• Cliquez sur l'icône Compteur et glissez afin de dessiner un petit
bouton compteur à côté du prix HT Bureau (cellule B4) ;
• cliquez sur l'icône Propriétés du contrôle ou faites ensuite un clic
droit de la souris sur la forme dessinée et choisissez la commande
Format de contrôle.
-Effectuez un clic droit afin d'obtenir le menu contextuel ou le bouton
Cliquez sur l'onglet Contrôle ; tapez la valeur minimale, la valeur maximale,
le pas ; indiquez dans l'option Cellule liée, la cellule dans laquelle vous
désirez faire apparaître le montant HT (B4) ; cliquez sur OK pour valider.
121
• Dans la cellule B5 : faites le calcul de la TVA : B4* 19, 6 %
• En B7, calculez le montant TTC.
Sur le côté, créez une zone de groupe :
• cliquez sur l'icône Zone de groupe et glissez ;
• cliquez ensuite sur le nom Zone de groupe 1 et tapez Transports.
Tapez Transports à cet endroit
• Dans la zone de groupe Transports, créez 2 cases à cocher ;
• cliquez, puis cliquez-glissez sur l'icône Case à cocher ;
• sélectionnez l'expression Case à cocher 1 et tapez en remplacement 152 € si HT >= 380
€.
• Cliquez, puis cliquez-glissez de nouveau sur l'icône Case à cocher ;
• sélectionnez l'expression Case à cocher 2 et tapez en remplacement
76 € Fragile (choix client).
Vous obtenez :
II n'est pas toujours facile de sélectionner les cases à cocher ou les boutons d'option. Pour
faciliter ces tâches, faites un clic droit sur les objets, puis appuyez ensuite sur la touche Échap
du clavier.
En dessous de la zone de groupe Transports, créez une autre zone de groupe :
• cliquez sur l'icône Zone de groupe et glissez ;
• cliquez ensuite sur le nom Zone de groupe 2 et tapez Remise.
Dans la zone de groupe Remise, créez 2 boutons d'option :
• cliquez et glissez sur l'icône Bouton d'option ;
• sélectionnez l'expression Case d'option 1 et tapez en remplacement :
10% si HT<762 ;
• cliquez et glissez de nouveau sur l'icône Bouton d'option ;
• sélectionnez l'expression Case d'option 2 et tapez en remplacement : 20% si HT >=762.
Réenregistrez votre formulaire.
122
Il s'agit maintenant de définir les contrôles sur les cases à cocher et les boutons d'option.
• Faites un clic droit sur la première case à cocher ;
• choisissez dans le menu contextuel, la commande Format de contrôle ;
• cliquez ensuite sur l'onglet Contrôle ;
• cochez l'option Valeur activée ;
• indiquez dans Cellule liée la référence de la cellule qui sera en liaison avec cette case (B9) ;
• cliquez sur OK pour valider.
• Effectuez la même opération pour la 2ème
case :
• cochez l'option Valeur activée ;
• indiquez dans Cellule liée la référence de la cellule qui sera en liaison avec cette case (B10);
• cliquez sur OK pour valider.
Vous noterez qu'Excel affiche FAUX dans la cellule B9, car la case n'est pas cochée et VRAI
dans la cellule B10, car la case est cochée.
N'oubliez pas de réenregistrer votre classeur de temps en temps.
Vous allez maintenant définir les formats de contrôle sur les boutons d'option.
• Faites un clic droit sur le bouton d'option ;
• choisissez dans le menu contextuel, la commande Format de
contrôle ;
• cliquez ensuite sur l'onglet Contrôle ;
• indiquez dans Cellule liée la référence de la cellule qui sera en
liaison avec cette case (B11);
• cliquez sur OK pour valider.
• Effectuez la même opération pour la 2ème
case, la Cellule liée est obligatoirement la même
(B11);
• Cliquez sur OK pour valider.
Vous noterez qu'Excel affiche cette fois 1 ou 2 dans la cellule B11, suivant que vous
choisissez la 1ére
ou la seconde remise.
II ne vous reste plus qu'à calculer le montant à payer en utilisant la fonction conditionnelle
123
SI :
Si la remise est égale à 1, on prend le HT * 90 %, sinon HT * 80 %, auquel on ajoute 152€ si
la cellule liée au transport est égale à Vrai, et 76 € si la taxe fragilité est égale à Vrai, sans
oubliez d'ajouter le montant de la TVA.
Réenregistrez le classeur.
• Testez votre formulaire en utilisant l'exemple ci-dessous :
• Effectuez la mise en page et préparez l'impression, sans oublier de préciser votre nom ;
• lancez l'impression.
Fermez le classeur en enregistrant les modifications.
Un formulaire peut être enregistré sous forme de modèle.
124
EXERCICE
OBJECTIF : Création d'un formulaire "Fiche annuelle Adhérent' d'une famille à un club de
tennis.
• Le nom de la famille sera indiqué grâce à une liste déroulante modifiable.
• La cotisation pour un ancien membre est de 16 € et de 27 € pour un nouveau membre.
• Le code pour un ancien membre sera l, et le code pour un nouveau membre sera 2.
• La cotisation de base sera calculée en fonction du code saisi.
• Par des compteurs, vous indiquerez le nombre d'adultes et d'enfants par famille.
• Vous pourrez ensuite calculer la cotisation membre(s).
• À cette cotisation s'ajouteront des charges de 7 % s'il s'agit uniquement d'enfants, de 9 % si
uniquement des adultes ou de 10 % s'il s'agit d'enfants et d'adultes.
• Une participation supplémentaire peut éventuellement s'ajouter si l'adhérent décide de
s'abonner aux revues du club (6 €/an) ou de suivre des stages « Extra » (3l €/an).
• Le total à payer sera donc égal à la cotisation membre auquel s'ajouteront éventuellement les
participations supplémentaires, plus les charges.
Créez un nouveau classeur.
• Saisissez les données des colonnes A, F, H.
• Créez avec l'icône zone de liste, une liste déroulante modifiable pour choisir le nom de
l'adhérent. Cette liste sera en liaison avec les cellules H11 à H19 (voir page suivante).
• Saisissez ensuite les cellules B3 à C4 et nommez-les Code.
• Calculez la cotisation de base en B7, en utilisant la fonction de recherche verticale, sans
oublier de tester si le code existe et si la cellule de saisie (B6) n'est pas vide.
Enregistrez le classeur sous le nom « Fiche Annuelle Adhérent ».
• Créez ensuite les 2 boutons Compteur qui seront liés aux cellules B8 et B9.
• Calculez la cotisation membre(s) qui est égale à la cotisation de base multipliée par le
nombre de personnes.
• Créez une zone de groupe Participation Sup contenant 2 cases à cocher liées aux cellules
B11 et B12.
125
• La cotisation avant charges est égale à la cotisation membres
+ cellule F11 (6 €) si B11 est égale à Vrai
+ cellule F12 (31 €) si B121 est égale à Vrai.
• Créez une zone de groupe Charges contenant 3 boutons d'options liés à la cellule B15.
• Le total à payer sera égal à la cotisation avant charges (B13)
+ si B15= 1 , B13*F16,
Si B15 = 2, B13 *F17,
sinon B13 * F18.
• Effectuez la mise en page et préparez l'impression, sans oublier de préciser votre nom.
• Lancez l'impression.
Fermez le classeur en enregistrant les modifications.
2.
126
LES GRAPHIQUES
I. EN SAVOIR PLUS SUR LES GRAPHIQUES
Les graphiques sont utilisés pour afficher des séries de données numériques sous forme
graphique afin d’appréhender plus facilement d’importantes quantités de données et les
relations entre différentes séries de données.
Pour créer un graphique dans Excel, vous commencez par entrer les données numériques pour
le graphique dans une feuille de calcul. Vous pouvez ensuite tracer ces données dans un
graphique en sélectionnant le type de graphique que vous voulez utiliser sur le ruban Office
Fluent (onglet Insérer, groupe Graphiques).
Données de feuille de calcul
Graphique créé à partir des données d’une feuille de calcul
Excel prend en charge de nombreux types de graphiques pour vous permettre d’afficher des
données de manière à ce qu’elles soient facilement lisibles. Lorsque vous créez un graphique
ou en modifiez un existant, vous pouvez choisir parmi une grande variété de types de
graphiques (un histogramme ou un graphique à secteurs, par exemple) et leurs sous-types (un
histogramme empilé ou un graphique à secteurs 3D, par exemple). Vous pouvez également
créer un graphique combiné en créant un graphique qui fait appel à plusieurs types de
graphiques.
Exemple de graphique combiné dans lequel un histogramme et un
graphique en courbes sont utilisés.
II. APPRENDRE A CONNAITRE LES ELEMENTS D ’UN GRAPHIQUE
Un graphique comporte de nombreux éléments. Certains d’entre eux sont affichés par défaut,
d’autres peuvent être ajoutés en fonction des besoins. Vous pouvez modifier l’affichage des
éléments d’un graphique en les déplaçant sur d’autres emplacements dans le graphique, en les
redimensionnant ou en modifiant la mise en forme. Vous pouvez également supprimer les
éléments de graphique que vous ne souhaitez pas afficher.
1. L’aire graphique du graphique.
2. L’aire de traçage du graphique.
3. Les points de données des séries de données tracées dans le
graphique.
4. Les axes horizontaux (catégorie) et verticaux (valeur) le long
127
desquels les données sont tracées dans le graphique.
5. La légende du graphique.
6. Un titre de graphique et d’axes que vous pouvez utiliser dans le graphique.
7. Une étiquette de données que vous pouvez utiliser pour identifier les détails d’un point
de données dans une série de données.
III. MODIFICATION D ’UN GRAPHIQUE DE BASE POUR REPONDRE A
VOS BESOINS
Une fois que vous avez créé un graphique, vous pouvez modifier n’importe lequel de ses
éléments. Par exemple, vous pourriez changer la façon dont les axes sont affichés, ajouter un
titre, afficher ou masquer la légende ou bien encore afficher davantage d’éléments de
graphique.
Pour modifier un graphique, vous pouvez :
Modifier l’affichage des axes du graphique Vous pouvez spécifier l’échelle des
axes et ajuster l’intervalle entre les valeurs ou les catégories affichées. Pour rendre
votre graphique plus lisible, vous pouvez également ajouter des graduations à un axe
et spécifier l’intervalle auquel elles doivent apparaître.
Ajouter des titres et des étiquettes de données à un graphique Pour clarifier les
informations présentées dans votre graphique, vous pouvez ajouter un titre, des titres
d’axe et des étiquettes de données.
Ajouter une légende ou une table de données Vous pouvez afficher ou masquer
une légende, la déplacer ou modifier les entrées de la légende. Dans certains
graphiques, vous pouvez également afficher une table de données qui affiche les
symboles de légende et les valeurs présentés dans le graphique.
Appliquer des options spéciales à chaque type de graphique Des lignes spéciales
(telles que des lignes haut-bas et des courbes de tendance), des barres (telles que des
barres haut-bas et des barres d’erreur), des indicateurs de données ainsi que d’autres
options sont disponibles pour les différents types de graphiques.
IV. LES STYLES DE GRAPHIQUES ET DISPOSITIONS DE
GRAPHIQUES PREDEFINIS
Au lieu d’ajouter ou de modifier manuellement des éléments du graphique ou de le mettre en
forme, vous pouvez lui appliquer rapidement une disposition et un style de graphique
prédéfinis. Excel propose une gamme de dispositions et de styles prédéfinis très utiles. Vous
pouvez également adapter une disposition ou un style à vos besoins en modifiant
manuellement des éléments de graphique, tels que la zone du graphique, la zone de traçage,
les séries de données ou la légende.
Lorsque vous appliquez une disposition de graphique prédéfinie, un ensemble spécifique
d’éléments de graphique (tels que des titres, une légende, une table de données ou des
étiquettes de données) est affiché d’une manière spécifique dans votre graphique. Chaque type
de graphique propose plusieurs dispositions parmi lesquelles vous pouvez faire votre
sélection.
128
Lorsque vous appliquez un style de graphique prédéfini, le graphique est mis en forme dans le
thème de document que vous avez appliqué de sorte que votre graphique soit conforme aux
couleurs du thème (un ensemble de couleurs), aux polices du thème (un ensemble de polices
d’en-tête et de corps de texte) et aux effets du thème (un ensemble de ligne et d’effets de
remplissage) que vous ou votre société utilisez.
Vous ne pouvez pas créer vos propres dispositions ou styles de graphiques mais vous pouvez
créer des modèles de graphiques qui incluent la disposition et la mise en forme que vous
voulez.
V. AJOUT D ’UNE MISE EN FORME VISUELLEMENT ATTRAYANTE A
UN GRAPHIQUE
Outre l’application d’un style de graphique prédéfini, vous pouvez facilement appliquer une
mise en forme à des éléments individuels d’un graphique, par exemple des indicateurs de
données, la zone de graphique, la zone de traçage ainsi que les nombres et le texte des titres et
des étiquettes pour donner à votre graphique un aspect à la fois attrayant et personnalisé. Vous
pouvez appliquer des styles de forme ainsi que des styles WordArt et mettre en forme
manuellement les formes et le texte des éléments du graphique.
Pour ajouter une mise en forme, vous pouvez :
Remplir les éléments du graphique Vous pouvez utiliser des couleurs, des
textures, des images et des dégradés pour attirer l’attention sur des éléments
spécifiques du graphique.
Modifier le contour des éléments du graphique Vous pouvez utiliser des couleurs,
des styles de lignes et des épaisseurs de lignes pour mettre en évidence des éléments
du graphique.
Ajouter des effets spéciaux aux éléments du graphique Vous pouvez appliquer
des effets spéciaux, comme une ombre, une lumière, un reflet, des bordures arrondies,
un biseau ou une rotation 3D à des éléments de graphique, pour donner à votre
graphique un aspect fini.
Mettre en forme les nombres et le texte Vous pouvez mettre en forme le texte et
les nombres des titres, des étiquettes et des zones de texte de votre graphique tout
comme vous le faites dans une feuille de calcul. Pour faire ressortir le texte et les
nombres, vous pouvez appliquer des styles WordArt.
VI. ÉTAPE 1 : CREER UN GRAPHIQUE DE BASE
Avec la plupart des graphiques, comme ceux à colonnes et à barres, vous pouvez tracer les
données et les organiser en lignes et en colonnes dans une feuille de calcul dans un graphique.
Cependant, certains types de graphiques, tels que les graphiques à secteurs et à bulles,
nécessitent une organisation des données spécifique.
1. Sélectionnez les cellules contenant les données que vous voulez utiliser pour votre
graphique.
129
Les données peuvent être organisées en lignes ou colonnes — Excel détermine
automatiquement le meilleur moyen de tracer les données dans le graphique. Certains types de
graphiques, tels que les graphiques à secteurs et à bulles, nécessitent une organisation des
données spécifique, comme celle décrite dans le tableau suivant.
Type de graphique Organisation des données
Graphique en
histogrammes, en
barres, en courbes,
en aires, en surface
ou en radar
En colonnes ou lignes, comme :
Lorem Ipsum
1 2
3 4
Ou :
Lorem 1 3
Ipsum 2 4
Graphique en
secteurs ou en
anneaux
Pour une série de données, dans une colonne ou ligne de données et une
colonne ou ligne d’étiquettes de données, comme :
A 1
B 2
C 3
Ou :
A B C
1 2 3
Pour plusieurs séries de données, dans plusieurs colonnes ou lignes de
données et une colonne ou ligne d’étiquettes de données, comme :
A 1 2
B 3 4
C 5 6
Ou :
A B C
1 2 3
4 5 6
Graphique xy
(nuages de points)
ou à bulles
En colonnes, avec les valeurs X dans la première colonne, et les
valeurs Y et les valeurs de la taille des bulles correspondantes dans les
colonnes adjacentes, comme :
X Y Taille des bulles
1 2 3
4 5 6
Graphique boursier
En colonnes ou lignes dans l’ordre suivant, avec les noms ou les dates
comme étiquettes :
valeurs maximales, valeurs minimales et valeurs de clôture
Comme :
Date Max Min Clôture
1/1/2002 46,125 42 44,063
Ou :
130
Date 1/1/2002
Max 46,125
Min 42
Fermer 44,063
2. Sélectionnez les cellules contenant les données que vous voulez utiliser dans le
graphique.
Conseil Si vous ne sélectionnez qu’une seule cellule, Excel trace automatiquement toutes
les cellules qui contiennent des données adjacentes à cette cellule dans le graphique. Si les
cellules que vous voulez tracer dans un graphique ne sont pas adjacentes, vous pouvez
sélectionner des cellules ou des plages de cellules non adjacentes en veillant à former un
rectangle avec la sélection. Vous pouvez également cacher les lignes ou les colonnes que vous
ne voulez pas tracer dans le graphique.
A. COMMENT SELECTIONNER CELLULES , PLAGES , LIGNES ET
COLONNES
Pour sélectionner Procédez comme suit
Une seule cellule Cliquez sur la cellule ou appuyez sur les touches de direction pour
vous placer sur la cellule.
Une plage de cellules
Cliquez sur la première cellule de la plage, puis faites glisser le
pointeur jusqu'à la dernière cellule, ou maintenez la touche Maj
enfoncée et appuyez sur les touches de direction pour étendre la
sélection.
Vous pouvez aussi sélectionner la première cellule de la plage et
appuyer sur F8 pour étendre la sélection à l'aide des touches de
direction. Pour arrêter la sélection, appuyez de nouveau sur F8.
Une grande plage de
cellules
Cliquez sur la première cellule de la plage, maintenez la touche
Maj enfoncée et cliquez sur la dernière cellule de la plage. Vous
pouvez faire défiler la fenêtre pour afficher la dernière cellule.
Toutes les cellules d'une
feuille de calcul
Cliquez sur le bouton Sélectionner tout.
Pour sélectionner toute la feuille de calcul, vous pouvez aussi
appuyer sur Ctrl+A.
Remarque Si la feuille de calcul contient des données, Ctrl+A
sélectionne la zone active. Si vous appuyez une deuxième fois sur
Ctrl+A, toute la feuille de calcul est sélectionnée.
Des cellules ou plages de
cellules non adjacentes
Sélectionnez la première cellule ou plage de cellules, puis
maintenez la touche Ctrl enfoncée et sélectionnez les autres
131
cellules ou plages.
Vous pouvez aussi sélectionner la première cellule ou plage de
cellules, puis appuyer sur Maj+F8 pour ajouter une autre cellule
ou plage non adjacente à la sélection. Pour arrêter d'ajouter des
cellules ou des plages à la sélection, appuyez de nouveau sur
Maj+F8.
Remarque Vous ne pouvez pas annuler la sélection d'une
cellule ou d'une plage de cellules dans une sélection non adjacente
sans annuler toute la sélection.
Toute une ligne ou toute
une colonne
Cliquez sur l'en-tête de ligne ou de colonne.
1. En-tête de ligne
2. En-tête de colonne
Vous pouvez aussi sélectionner des cellules
dans une ligne ou une colonne en sélectionnant la première
cellule puis en appuyant sur Ctrl+Maj+Touche de direction
(Droite ou Gauche pour les lignes, Haut ou Bas pour les
colonnes).
Remarque Si la ligne ou la colonne contient des données,
Ctrl+Maj+Touche de direction sélectionne la ligne ou la colonne
jusqu'à la dernière cellule utilisée. Si vous appuyez une deuxième
fois sur Ctrl+Maj+Touche de direction, toute la ligne ou colonne
est sélectionnée.
Des lignes ou colonnes
adjacentes
Faites glisser le pointeur sur les en-têtes des lignes ou des
colonnes. Ou sélectionnez la première ligne ou colonne, puis
maintenez la touche Maj enfoncée et sélectionnez la dernière ligne
ou colonne.
Des lignes ou colonnes
non adjacentes
Cliquez sur l'en-tête de colonne ou de ligne de la première ligne
ou colonne dans votre sélection ; puis maintenez la touche Ctrl
enfoncée et cliquez sur les en-têtes de colonne ou de ligne des
autres lignes ou colonnes à ajouter à la sélection.
La première ou la dernière
cellule d'une ligne ou
d'une colonne
Sélectionnez une cellule dans la ligne ou la colonne et appuyez sur
Ctrl+Touche de direction (Droite ou Gauche pour les lignes, Haut
ou Bas pour les colonnes).
La première ou la dernière
cellule d'une feuille de
calcul ou d'un tableau
Microsoft Office Excel
Appuyez sur Ctrl+Début pour sélectionner la première cellule
d'une feuille de calcul ou d'une liste Excel.
Appuyez sur Ctrl+Fin pour sélectionner la dernière cellule d'une
feuille de calcul ou d'une liste Excel qui contient des données ou
une mise en forme.
Des cellules jusqu'à la
dernière cellule utilisée de
la feuille de calcul (coin
inférieur droit)
Sélectionnez la première cellule et appuyez sur Ctrl+Maj+Fin
pour étendre la sélection des cellules jusqu'à la dernière cellule
utilisée de la feuille de calcul (coin inférieur droit).
132
Des cellules jusqu'au
début de la feuille de
calcul
Sélectionnez la première cellule et appuyez sur Ctrl+Maj+Début
pour étendre la sélection des cellules au début de la feuille de
calcul.
La sélection active avec
des cellules en plus ou en
moins
Maintenez la touche Maj enfoncée et cliquez sur la dernière
cellule à inclure dans la nouvelle sélection. La plage rectangulaire
entre la cellule active et la cellule sur laquelle vous cliquez devient
la nouvelle sélection.
Conseil Pour annuler une sélection, cliquez sur n'importe quelle cellule de la feuille de
calcul.
3. Dans le groupe Graphiquede l’onglet Insertion, effectuez l’une des opérations
suivantes :
Cliquez sur un type de graphique, puis sur le sous-type de graphique que vous
souhaitez utiliser.
Pour afficher tous les types de graphiques disponibles, cliquez sur un type de
graphique, puis sur Tous types de graphiques pour afficher la boîte de
dialogue Insérer un graphique, cliquez ensuite sur les flèches pour parcourir
les types et sous-types de graphiques disponibles, puis sur celui de votre choix.
Conseil Une info-bulle contenant le nom du type de graphique s’affiche lorsque vous placez
le pointeur de la souris sur le type ou le sous-type. Pour plus d’informations sur les types de
graphiques que vous pouvez utilisez, voir Types de graphiques disponibles(En anglais).
4. Par défaut, le graphique est placé sur la feuille de calcul en tant que graphique
incorporé. Si vous désirez le placer dans une feuille de graphique distincte, vous
pouvez changer son emplacement en procédant comme suit :
1. Cliquez sur le graphique incorporé pour le sélectionner.
Les Outils de graphique s’affichent et les onglets Création, Disposition et Mise
en forme sont ajoutés.
2. Dans l’onglet Créer, groupe Emplacement, cliquez sur Déplacer le graphique.
3. Sous Emplacement, exécutez l’une des actions suivantes :
Pour afficher le graphique dans une feuille de graphique, cliquez sur Nouvelle
feuille.
Conseil Si vous voulez changer le nom du graphique proposé, tapez un nouveau nom dans
la zone Nouvelle feuille.
Pour afficher le graphique en tant que graphique incorporé dans une feuille de calcul,
cliquez sur Objet dans, puis cliquez sur une feuille de calcul dans la zone Objet dans.
133
Excel attribue automatiquement un nom au graphique, tel que Graphique1 s’il s’agit
du premier graphique que vous créez sur la feuille de calcul. Pour modifier le nom du
graphique, procédez comme suit :
Cliquez sur le graphique.
Sous l’onglet Disposition, dans le groupe Propriétés, cliquez sur la zone de texte
Nom du graphique.
Conseil Au besoin, cliquez sur l’icône Propriétés du groupe Propriétés pour développer le
groupe.
Entrez un nouveau nom.
Appuyez sur Entrée.
VII. ÉTAPE 2 : MODIFIER LA DISPOSITION OU LE STYLE D ’UN
GRAPHIQUE
Vous pouvez modifier l’aspect d’un graphique immédiatement après l’avoir créé. Au lieu
d’ajouter ou de modifier manuellement des éléments du graphique ou de le mettre en forme,
vous pouvez lui appliquer rapidement une disposition et un style prédéfinis. Excel met à votre
disposition toute une série de dispositions et de styles prédéfinis (ou dispositions rapides et
styles rapides), mais vous pouvez personnaliser au besoin une disposition ou un style en
modifiant manuellement la disposition et la mise en forme de chaque élément du graphique.
A. APPLIQUER UNE DISPOSITION DE GRAPHIQUE PREDEFINIE
1. Cliquez sur le graphique à mettre en forme en utilisant une disposition de graphique
prédéfinie.
Conseil Les Outils de graphique s’affichent et les onglets Création, Disposition et Mise
en forme sont ajoutés.
2. Dans le groupe Dispositions du graphique de l’onglet Création, cliquez sur la
disposition que vous voulez utiliser.
Remarque Lorsque la taille de la fenêtre Excel est réduite, les dispositions de graphique
seront disponibles dans la galerie Disposition rapide du groupe Dispositions du graphique.
Conseil Pour voir toutes les dispositions disponibles, cliquez sur Autres.
B. APPLIQUER UN STYLE DE GRAPHIQUE PREDEFINI
1. Cliquez sur le graphique à mettre en forme en utilisant un style de graphique prédéfini.
134
Conseil Les Outils de graphique s’affichent et les onglets Création, Disposition et Mise
en forme sont ajoutés.
2. Dans le groupe Styles du graphique de l’onglet Création, cliquez sur le style que
vous voulez utiliser.
Remarque Lorsque la taille de la fenêtre Excel est réduite, les styles de graphique seront
disponibles dans la galerie Style rapide du groupe Styles du graphique.
Conseil Pour voir tous les styles prédéfinis, cliquez sur Autres.
C. MODIFIER MANUELLEMENT LA DISPOSITION DES ELEMENTS DU
GRAPHIQUE
Cliquez sur le graphique ou l’élément de graphique dont vous voulez modifier la
disposition, ou procédez comme suit pour sélectionner un élément de graphique dans
une liste d’éléments de graphiques.
Cliquez n’importe où dans le graphique pour afficher les Outils de
graphique.
Dans le groupe Sélection actuelle de l’onglet Mise en forme,
cliquez sur la flèche située en regard de la zone Éléments de
graphique, puis sur l’élément de graphique désiré.
Dans le groupe Étiquettes, Axes ou Arrière-plan de l’onglet Disposition, cliquez sur
l’élément de graphique à modifier, puis cliquez sur l’option de disposition souhaitée.
D. MODIFIER MANUELLEMENT LA MISE EN FORME DES ELEMENTS DU
GRAPHIQUE
Cliquez sur le graphique ou l’élément de graphique dont vous voulez modifier le style,
ou procédez comme suit pour sélectionner un élément de graphique dans une liste
d’éléments de graphiques.
1. Cliquez sur un graphique pour afficher les Outils de graphique.
2. Dans le groupe Sélection actuelle de l’onglet Mise en forme, cliquez
sur la flèche située en regard de la zone Éléments de graphique, puis sur
l’élément de graphique désiré.
135
VIII. ETAPE 3 : AJOUTER OU MODIFIER DES TITRES OU DES
ETIQUETTES DE DONNEES
Pour simplifier la lecture d’un graphique, vous pouvez ajouter des titres, soit au graphique lui-
même, soit aux axes du graphique. Les titres des axes sont généralement disponibles pour tous
les axes affichables dans un graphique, y compris les axes de profondeur (axes des séries)
dans les graphiques 3D. Certains types de graphiques, tels que les graphiques en radar, sont
munis d’axes, mais ne peuvent afficher les titres des axes. C’est également le cas des
graphiques sans axe, tels que les graphiques en anneaux ou en secteurs.
Vous pouvez également relier les titres de graphique et des axes au texte correspondant aux
cellules d’une feuille de calcul, en créant une référence à ces cellules. Les titres liés sont
automatiquement mis à jour dans le graphique lors de la modification du texte correspondant
dans la feuille de calcul.
Pour identifier rapidement une série de données dans un graphique, vous pouvez ajouter des
étiquettes de données aux points de données du graphique. Par défaut, les étiquettes de
données sont liées aux valeurs de la feuille de calcul, et elles s’actualisent automatiquement
lorsque ces valeurs sont modifiées.
A. AJOUTER UN TITRE DE GRAPHIQUE
1. Cliquez sur le graphique auquel vous souhaitez ajouter un titre.
Les Outils de graphique s’affichent et les onglets Création, Disposition et Mise en forme
sont ajoutés.
2. Sous l’onglet Disposition, cliquez sur Titre du graphique dans le groupe Éléments
de graphique.
3. Cliquez sur Titre de graphique superposé centré ou Au-dessus du graphique.
4. Dans la zone de texte Titre du graphique qui s’affiche dans le graphique, tapez le
texte souhaité.
5. Pour procéder à la mise en forme du texte, sélectionnez-le, puis cliquez sur les options
de mise en forme souhaitées dans la mini barre d’outils.
Conseil Vous pouvez également utiliser les boutons de mise en forme du ruban (onglet
Accueil, groupe Police). Pour mettre en forme l’ensemble du titre, cliquez dessus avec le
bouton droit, cliquez sur Mise en forme du titre du graphique, puis sélectionnez les options
de mise en forme souhaitées.
136
B. AJOUTER DES TITRES D ’AXE
1. Cliquez sur le graphique dans lequel vous souhaitez ajouter des titres aux axes.
Les Outils de graphique s’affichent et les onglets Création, Disposition et Mise en forme
sont ajoutés.
2. Sous l’onglet Disposition, cliquez sur Titres des axes dans le groupe Éléments de
graphique.
3. Effectuez l’une des opérations suivantes :
Pour ajouter un titre à l’axe horizontal (axe des catégories) principal, cliquez sur
Titre de l’axe horizontal principal et sélectionnez l’option de votre choix.
Pour ajouter un titre à l’axe vertical principal (valeur), cliquez sur Titre de l’axe
vertical principal, puis sur l’option de votre choix.
Pour ajouter un titre à un axe de profondeur (axe des séries), cliquez sur Titre de
l’axe de profondeur, puis sélectionnez l’option de votre choix.
4. Dans la zone de texte Axe de profondeur qui s’affiche dans le graphique, tapez le
texte souhaité.
5. Pour procéder à la mise en forme du texte, sélectionnez-le, puis cliquez sur les options
de mise en forme souhaitées dans la mini barre d’outils.
Conseil Vous pouvez également utiliser les boutons de mise en forme du ruban (onglet
Accueil, groupe Police). Pour mettre en forme l’ensemble du titre, cliquez dessus avec le
bouton droit, cliquez sur Mise en forme du titre de l’axe, puis sélectionnez les options de
mise en forme souhaitées.
C. LIER UN TITRE A UNE C ELLULE DE FEUILLE DE CALCUL
1. Cliquez sur le titre du graphique ou de l’axe que vous souhaitez lier à une cellule de
feuille de calcul.
2. Dans la feuille de calcul, cliquez dans la barre de formule et tapez un égal signe (=).
3. Sélectionnez la cellule de feuille de calcul contenant les données ou le texte à afficher
dans le graphique.
4. Appuyez sur Entrée.
D. AJOUTER DES ETIQUETTES DE DONNEES
1. Dans un graphique, effectuez l’une des opérations suivantes :
137
Pour ajouter une étiquette de données à tous les points de données de toutes les
séries de données, cliquez sur la zone de graphique .
Pour ajouter une étiquette de données à tous les points de données d’une série
de données, cliquez n’importe où dans la série de données que vous souhaitez
étiqueter.
Pour ajouter une étiquette de données à un point précis d’une série de données
dans une série de données, cliquez sur la série contenant le point de données à
étiqueter, puis cliquez sur le point de données à étiqueter.
Les Outils de graphique s’affichent et les onglets Création, Disposition et Mise en forme
sont ajoutés.
2. Sous l’onglet Disposition , dans le groupe Éléments de graphique, cliquez sur
Étiquettes de données, puis cliquez sur l’option de votre choix.
E. SUPPRIMER LES TITRES OU LES ETIQUETTES DE DONNEES D ’UN
GRAPHIQUE
1. Cliquez sur le graphique.
Les Outils de graphique s’affichent et les onglets Création, Disposition et Mise en forme
sont ajoutés.
2. Dans le groupe Étiquettes de l’onglet Disposition, effectuez l’une des opérations
suivantes :
Pour supprimer un titre de graphique, cliquez sur Titre du graphique, puis sur
Aucun.
Pour supprimer un titre d’axe, cliquez sur Titre de l’axe, cliquez sur le type de
titre d’axe à supprimer, puis cliquez sur Aucun.
Pour supprimer des étiquettes de données, cliquez sur Étiquettes de données,
puis sur Aucune.
Conseil Pour supprimer rapidement un titre ou une étiquette de données, cliquez dessus puis
appuyez sur Suppr.
138
IX. ÉTAPE 4 : AFFICHER OU MASQUER UNE LEGENDE
Lorsque vous créez un graphique, la légende apparaît, mais vous pouvez la masquer ou
modifier son emplacement une fois le graphique créé.
1. Sélectionnez le graphique dans lequel vous souhaitez afficher ou masquer une
légende.
2. Sous l’onglet Disposition, cliquez sur Légende dans le groupe Étiquettes.
3. Effectuez l’une des actions suivantes :
Pour masquer la légende, cliquez sur Aucune.
Pour afficher une légende, cliquez sur l’option d’affichage de votre choix.
X. ÉTAPE 5 : AFFICHER OU MASQUER LES AXES OU
QUADRILLAGES D ’UN GRAPHIQUE
Lors de la création d’un graphique, les axes principaux sont affichés pour la plupart des types
de graphiques. Vous pouvez les activer ou les désactiver en fonction des besoins.
Pour faciliter la lecture d’un graphique, vous pouvez afficher ou masquer les quadrillages
horizontaux et verticaux qui s’étendent le long des deux axes, dans la zone de traçage du
graphique.
A. AFFICHER OU MASQUER LES AXES PRINCIPAUX
1. Cliquez sur le graphique dont vous souhaitez afficher ou masquer les axes.
2. Dans le groupe Axes de l’onglet Disposition, cliquez sur Axes, puis effectuez l’une
des opérations suivantes :
Pour afficher un axe, cliquez sur Axe horizontal principal, Axe vertical
principal ou Axe de profondeur (sur un graphique 3D), puis cliquez sur
l’option d’affichage de l’axe voulue.
Pour masquer un axe, cliquez sur Axe horizontal principal, Axe vertical
principal ou Axe de profondeur (sur un graphique 3D), puis cliquez sur
Aucun.
Pour spécifier des options détaillées d’affichage d’axe et d’échelle, cliquez sur
Axe horizontal principal, Axe vertical principal ou Axe de profondeur (sur
un graphique 3D), puis cliquez sur Plus d’options d’axe horizontal principal,
Plus d’options d’axe vertical principal ou Plus d’options d’axe de
profondeur.
139
B. AFFICHER OU MASQUER LES AXES SECONDAIRES
1. Dans un graphique, cliquez sur la série de données que vous voulez tracer sur l’axe
vertical secondaire ou sélectionnez la série de données voulue dans une liste
d’éléments. Pour cela, procédez comme suit :
Cliquez sur le graphique.
Sous l’onglet Mise en forme, dans le groupe Sélection active,
cliquez sur la flèche située à côté de la zone Éléments de graphique, puis
cliquez sur la série de données que vous voulez tracer sur l’axe vertical
secondaire.
2. Sous l’onglet Mise en forme, dans le groupe Sélection active, cliquez
sur Mise en forme de la sélection.
3. Cliquez sur Options des séries si cette option n’est pas déjà
sélectionnée, puis sous Tracer la série avec, cliquez sur Axe secondaire, puis
sur Fermer.
4. Sous l’onglet Disposition, dans le groupe Axes, cliquez sur Axes.
5. Effectuez l’une des actions suivantes :
Pour afficher un axe vertical secondaire, cliquez sur Axe vertical secondaire, puis
cliquez sur l’option d’affichage voulue.
Pour afficher un axe horizontal secondaire, cliquez sur Axe horizontal secondaire,
puis cliquez sur l’option d’affichage voulue.
6. Pour masquer un axe secondaire, cliquez sur Axe vertical secondaire ou Axe
horizontal secondaire, puis cliquez sur Aucun.
C. AFFICHER OU MASQUER LES QUADRILLAGES
1. Cliquez sur le graphique auquel vous souhaitez ajouter un quadrillage.
2. Sous l’onglet Disposition, cliquez sur Quadrillage dans le groupe Axes.
3. Procédez comme suit :
Pour ajouter un quadrillage horizontal au graphique, pointez sur
Quadrillage horizontal principal, puis cliquez sur l’option souhaitée.
Si le graphique est doté d’un axe horizontal secondaire, vous pouvez
également cliquer sur Quadrillage horizontal secondaire.
Pour ajouter un quadrillage vertical au graphique, pointez sur
Quadrillage vertical principal, puis cliquez sur l’option souhaitée. Si le
graphique est doté d’un axe vertical secondaire, vous pouvez également cliquer
sur Quadrillage vertical secondaire.
140
Pour ajouter un quadrillage de profondeur dans un graphique 3D, pointez sur
Quadrillage de profondeur, puis cliquez sur l’option désirée. Cette option
n’est disponible que lorsque le graphique sélectionné est un véritable
graphique 3D, tel qu’un histogramme 3D.
Pour masquer le quadrillage d’un graphique, pointez sur Autres options de
quadrillage horizontal principal, Autres options de quadrillage vertical
principal ou Quadrillage de profondeur (dans le cas d’un graphique 3D),
puis cliquez sur Aucun. Si le graphique possède des axes secondaires, vous
pouvez également cliquer sur Quadrillage horizontal secondaire ou
Quadrillage vertical secondaire, puis sur Aucun.
Pour supprimer rapidement le quadrillage d’un graphique, sélectionnez le
quadrillage et appuyez sur Suppr.
XI. ÉTAPE 6 : DEPLACER OU REDIMENS IONNER UN GRAPHIQUE
Vous pouvez déplacer un graphique sur n’importe où dans une feuille de calcul ou vers une
feuille de calcul nouvelle ou existante. Vous pouvez également modifier la taille du graphique
afin de l’ajuster au document dans lequel il apparaît.
A. DEPLACER UN GRAPHIQUE
Pour déplacer un graphique, faites-le glisser jusqu’à l’emplacement de votre choix.
B. REDIMENSIONNER UN GRA PHIQUE
Pour redimensionner un graphique, effectuez l’une des opérations suivantes :
Cliquez sur le graphique, puis faites glisser les poignées de redimensionnement en
fonction de la taille voulue.
Sous l’onglet Format, dans le groupe Taille, entrez les tailles dans Hauteur de la
forme et Largeur de la forme.
141
XII. 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éristiques d’une série.
A. INSERTION D ’UN SPARKLINE
Pour insérer un sparkline :
- Prévoyez la cellule où sera inséré le sparkline. Insérez si nécessaire une nouvelle colonne.
Pour insérer une nouvelle colonne : sélectionnez la colonne à gauche de laquelle sera insérée
la nouvelle colonne, en cliquant sur sa case d’en-tête. Faites un clic droit sur la sélection >
Insertion.
- Sous l’onglet Insertion, dans le groupe « Graphiques sparkline », choisissez le type de
sparkline souhaité : Courbe, Colonne ou Conclusions/Pertes.
La fenêtre « Créer des graphiques sparkline » s’affiche.
- Indiquez la plage de données ainsi que l’emplacement du sparkline.
Pour l’une comme pour l’autre, il suffit de cliquer dans la zone de saisie, puis de sélectionner
la ou les cellules concernées. Validez.
La plage de données affiche des références relatives, tandis que la plage d’emplacements
affiche des références absolues (cf. chapitre 7 § 2 « Références relatives ou absolues).
Créer un nouveau classeur
- saisissez le tableau suivant
- Sous l’onglet « Insertion », dans le groupe « Graphiques sparkline », choisissez le type «
Courbe ». La fenêtre « Créer des graphiques sparkline » s’affiche.
- Cliquez dans la zone « Plage de données » et sélectionnez la plage de la série « Par avion »
E2:J2.
- Cliquez dans la zone « Plage d’emplacements » et sélectionnez la cellule D2. Validez.
142
B. NSERTION DE PLUSIEURS SPARKLINES
Pour insérer plusieurs sparklines, on peut :
- Soit utiliser la même méthode que précédemment.
Dans la fenêtre « Créer des graphiques sparkline », prévoyez le nombre
d’emplacements nécessaires à tous les sparklines à insérer (autant que de séries
sélectionnées).
- Soit utiliser la poignée de recopie : un sparkline ayant été créé, sélectionnez la cellule, et
cliquez-glissez sur sa poignée pour créer d’autres graphiques sparklines.
Voir exemple ci-dessus.
Par défaut, les sparklines ainsi créés forment un groupe.
Si on modifie la mise en forme de l’un d’eux, celle des autres sera pareillement modifiée.
C. MODIFICATION D ’UN GRAPHIQUE SPARKLI NE
Quand on clique sur une cellule contenant un sparkline, l’onglet « Création » des « Outils
sparkline » est disponible. Il contient les commandes spécifiques au sparkline permettant de :
modifier les données, le type du sparkline, son apparence, distinguer des points particuliers,
appelés « marqueurs ».
Dans le groupe « Groupe », l’option « Dissocier » permet de dissocier les sparklines
constitutifs d’un groupe. Dans ce même groupe, on peut paramétrer un axe au sparkline… et
effacer le sparkline.
143
OBJETS GRAPHIQUES
Il est possible de placer d’autres types d’objets graphiques sur une feuille de calcul : formes,
images, diagrammes SmartArt , WordArt.
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.
I. GROUPER DES OBJETS
Pour déplacer ou traiter divers objets simultanément, il est pratique de les grouper :
sélectionnez les objets. 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 ».
II. DEPLACEMENT 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).
III. 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’un angle, 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 ».
144
IV. ROTATION Pour faire pivoter ou pour retourner un objet, sélectionnez-le, puis cliquez-glissez sur sa
poignée de rotation (rond vert).
Ou bien utilisez le bouton « Rotation » du groupe « Organiser » (onglet Format).
V. 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.
VI. « AVANCER » OU « RECULER » DES OBJETS QUI SE
SUPERPOSENT Sélectionnez l’objet. Pour « l’avancer » ou le « reculer » : sous l’onglet « Format », dans le
groupe « Organiser », utilisez les boutons « Mettre au premier plan » ou « Mettre à l’arrière-
plan », boutons dotés d’un menu déroulant.