EXCEL 2010

EXCEL 2010
Notions de base et avancées d'Excel
1213 1GTb
Avertissements :
Ces notes n'ont pas la prétention de constituer un guide complet sur le logiciel d'Excel version 2010, ni de
reprendre toutes les notions vues aux cours. En effet, certaines fonctionnalités et instructions de base ne sont pas
reprises dans ces notes bien qu'elles seront étudiées durant les séances. D'autres notions ne sont que
sommairement décrites avec le souci d'indiquer une matière. Par contre, vous trouverez certaines fonctionnalités
qui ne figurent généralement pas dans des guides de base.
Par l'abondance des illustrations, ces notes sont supposées rencontrer les demandes d'un praticien débutant
cherchant à trouver ses marques. Elles devraient, également, soulager tous les étudiants dans leurs prises de
notes.
Parmi les notions non développées, pourtant importantes, les instructions conditionnelles font l'objet d'autres
plus spécifiques.
Ne vous privez pas des très nombreuses ressources qui abondent sur le WEB, ou du support proposé dans ce
logiciel par Microsoft sous forme d'aide et de mise en route.
Enfin, ne cherchez pas ici les exercices, ils seront développés en votre présence tout en sollicitant votre analyse.
Les solutions seront à votre disposition progressivement dans le dossier de votre formation.
Je ne saurai donc trop vous recommander une participation plus qu'active et régulière aux cours.
Bonne lecture et bonne étude.
Notions de base et avancées d'Excel
Le logiciel tableur
L'usage de base d'un tableur est de créer des feuilles dans lesquelles les résultats des calculs évoluent
instantanément en fonction des valeurs d'entrée considérées comme des variables.
Un tableur reprend également d'autres foncions complémentaires parmi lesquelles le grapheur, le solveur, les
scénarios…
Quelques noms de tableurs : Lotus, OpenOffice Calc, Iworks
Interface 2010
L’interface 2010 est basée sur une barre d’onglets qui permettent de sélectionner un des rubans d’outils.
Chaque ruban d’outils présente des fonctionnalités propres regroupées dans des zones distinctes. Il faut
également remarquer qu’un bouton situé au bas de certaines zones appelle une fenêtre de paramétrage.
L’utilisation de ces rubans d’outils est le principal changement par rapport aux versions antérieures de
l’ergonomie du logiciel depuis la version 2007. Bien que cette architecture abandonne l’utilisation des menus
dans lesquels étaient répertoriés par famille les différents outils, elle reprend les mêmes raccourcis clavier et les
fenêtres de paramétrage restent quasi identiques à celles des versions précédentes. De la sorte, le passage d’une
version à l’autre ne doit pas poser trop de difficultés à l’utilisateur.
Compatibilité entre les versions
Quant aux formats des fichiers qui découlent de ces différentes versions, ils sont bien sûr différents car
renfermant des fonctionnalités inexistantes dans les versions précédentes. Cependant, il reste toujours possible
d’utiliser les classeurs d’une version spécifique à l’aide du logiciel d’une version différente à la condition
d’accepter quelques petites différences mineures quand le fichier rétrograde de version. La compatibilité est donc
assurée.
Comment fonctionne un classeur
Les documents d'un tableur sont appelés classeurs. Le classeur se compose de plusieurs feuilles (cf. figure cidessous), elles-mêmes composées de nombreuses cellules intersections de lignes et de colonnes. Chaque cellule
est identifiée par une référence (adresse) et peut recevoir toutes sortes d'informations (données numériques,
textes, formules…)
La version XL 2010 offre 1.048.576 lignes, 16.384 colonnes soit 17.179.869.184 (234) cellules par feuilles !
Un classeur est géré informatiquement sous la forme d'un fichier.
Les feuilles
Le nom du classeur se
trouve dans la barre de titre
de la fenêtre.
Boutons de défilement d’onglets.
EXCEL V2010 1213b 1GT.docx
Onglets de feuille
Barre de fractionnement d’onglets
3 de 46
Ascenseur
12/02/2014
Notions de base et avancées d'Excel
Saisie du contenu d'une cellule
Pour encoder le contenu d'une cellule,
sélectionnez la cellule et ensuite encodez la
donnée, le texte ou l’expression. Ruban affichage
Coche barre de formule
Onglet Affichage
A condition d’avoir activé la coche de la barre
de formule dans le ruban de l’onglet affichage,
les données apparaissent dans celle-ci au fur et
à mesure de leur encodage.
Il est également possible d’encoder ou de
corriger une donnée par la barre de formule.
Zone nom
Barre de formule
La zone nom donne l’adresse de la cellule
concernée.
Edition d'une cellule
La modification du contenu d'une cellule s'effectue soit par la barre de formule, soit pour la touche de fonction
d'édition F2, soit par un double clic sur celle-ci.
La barre de formule permet de
modifier le contenu d'une
cellule
Après une action sur la touche
F2, le mode édition pour la
cellule sélectionnée est enclenché
Joindre une cellule
Une cellule dont la référence est mentionnée dans la « zone nom », sera activée et, réciproquement, la zone nom
indiquera la référence d’une cellule sélectionnée.
Pour référencier une cellule, la zone accepte les noms donnés aux cellules si cette méthode d’adressage est
utilisée.
Notons que la « zone Nom », ainsi que « la barre de formule » ne seront affichées que si la coche « barre de
formule » est activée.
EXCEL V2010 1213b 1GT.docx
4 de 46
12/02/2014
Notions de base et avancées d'Excel
Sélection de cellules - Portée des commandes
Afin de spécifier la portée collective d'une commande, il faut préalablement sélectionner les cellules afin de
préciser la plage de cellules sur laquelle la commande s'appliquera.
Les sélections possibles sont :
 une cellule unique,
 un ensemble de cellules sur une ligne ou une
colonne,
 un ensemble de plusieurs cellules réparties sur
plusieurs lignes et plusieurs colonnes
 Plusieurs plages (avec la touche CTRL).
 Une feuille entière…
Rappelons également la méthode utilisant les noms
pour la référence des plages de cellules.
Les menus contextuels
Comme dans d'autres logiciels, l'utilisation du bouton droit de la souris appelle le menu contextuel qui reprend
les commandes les plus usitées pour le contexte. Ce menu est parfois accompagné des outils de mise en forme
L'aide
Comme la plupart des logiciels actuels, une documentation très complète est fournie avec le logiciel. Vous
l'obtiendrez par la touche F1 ou par le bouton en forme de point d'interrogation.
Une aide contextuelle très utile est également accessible lors de l'utilisation de fonctions.
EXCEL V2010 1213b 1GT.docx
5 de 46
12/02/2014
Notions de base et avancées d'Excel
Écriture des expressions et de liaisons entre cellules
Les expressions permettent des opérations arithmétiques (addition, multiplication…) et des opérations logiques
(comparaisons, conditions …) sur les valeurs d'une feuille de calcul.
Les expressions utilisent les adresses des cellules, des valeurs constantes et des opérateurs. Ces derniers suivent
les mêmes règles de priorité que les opérations mathématiques. Elles seront revues aux cours et vous pouvez
également trouver dans l'aide d'Excel.
Exemples :
5+2*3 = 11 (les parenthèses sont inutiles) ; (5+2)*3 = 21 et -5²-5² = 0
Une expression (formule) commence toujours par
un de ces trois opérateurs :
 égal =
 plus +
 moins Les exemples ci-contre montrent des expressions
utilisant les adresses des cellules. De la sorte, les
valeurs contenues dans les cellules adressées
pourront changer (variables) et influenceront les
résultats des expressions.
Remarquez également l'usage de valeurs
constantes et le rôle des parenthèses.
EXCEL V2010 1213b 1GT.docx
6 de 46
12/02/2014
Notions de base et avancées d'Excel
Les références
Les expressions utilisent les adresses des cellules contenant les valeurs des données. Les adresses référencient
des cellules pouvant être situées sur la même feuille, sur une autre feuille du même classeur ou d’un classeur
différent enregistré. De la sorte les données peuvent provenir d'endroits différents.
Liaisons dynamiques entre classeurs
Les formules faisant références
à des données se situant dans
d'autres classeurs utilisent un
lien dit dynamique.
Ainsi, lors du changement de la
valeur dans le fichier source, la
formule recalcule
immédiatement le résultat de la
valeur liée.
Lors de l'ouverture du fichier
utilisant une liaison, Excel
demandera, en fonction du
paramétrage de votre
application, une mise à jour ou
non de vos données sources.
Au risque de rompre les
liaisons, un fichier source ne
peut être déplacé. Dans ce
cas, Excel ne peut retrouver
les données et vous propose
un processus pour rétablir
les liaisons.
EXCEL V2010 1213b 1GT.docx
7 de 46
12/02/2014
Notions de base et avancées d'Excel
La copie incrémentée et les listes personnalisées
Cette méthode de copie permet de
remplir des plages de cellules
adjacentes à partir d'une donnée
se terminant par un chiffre ou de
deux nombres ou d'une date
La copie doit utiliser le coin
inférieur droit de la cellule initiale
appelée "poignée de recopie". Le
curseur prenant la forme d'une
croix noire.
Les listes personnalisées sont
gérées à partir d'une fenêtre
accessible par le bouton "Modifier
les listes personnalisées.." de
l'onglet "Options avancées" du
Back stage (Fichier).
La copie de cellules contenant des expressions
Les tableaux dans les feuilles présentent souvent des cellules adjacentes contenant des formules répondant à la
même sémantique (construction logique).
Lors d'une copie, les expressions peuvent s'adapter dans des plages de cellules adjacentes.
Lors de sa copie dans les cellules situées en-dessous d’elle, l’expression contenue dans la cellule D2, effectuant
la division des points obtenus dans une branche par le maximum accordé à celle-ci, s’adaptera à chaque ligne en
modifiant son indice de ligne. De la sorte, l'expression copiée utilisera à chaque ligne les données contenues dans
les cellules voisines de droite de la même ligne.
L'adressage en mode relatif
Lorsque les références des adresses évoluent durant une
copie, elles sont dites être en mode relatif.
L'adressage en mode absolu
Le mode absolu permet de figer une référence
d’adresse (ligne, colonne ou les deux) de telle
sorte que l’expression d’une formule copiée
puisse continuer à référencer une cellule ou
une plage bien précise.
Le mode absolu s’utilise pour la copie d'une
expression au sein d’une plage quand un
opérande de l'expression est situé en dehors
de la plage.
Afin de figer l'adresse de la cellule et
empêcher ainsi qu'elle évolue pendant la
copie, le mode absolu sera signalé en plaçant
comme préfixe le signe $ soit devant l'indice
de la ligne ou de l'indice de la colonne.
EXCEL V2010 1213b 1GT.docx
8 de 46
12/02/2014
Notions de base et avancées d'Excel
Le nommage des cellules
Un nom peut remplacer avantageusement l'adresse d'une cellule ou d'une plage de cellules. L'utilisation de noms
en lieu et place des références facilite la lecture des expressions.
Les noms attribués à des plages de cellules peuvent se combiner pour désigner une plage ou une cellule
d’intersection. Ainsi l'adresse "ventes janvier" désigne l'intersection des plages nommées "Ventes" et "Janvier"
soit la cellule B2.
Les deux types d’intersection
Intersection Implicite
Intersection Explicite
Sachant que les lignes 2 et 3 portent respectivement les noms de ventes et de dépenses et que les colonnes B, C,
D sont identifiées par les noms janvier, février et mars, alors il est possible de référencier des cellules de façons
différentes selon leur proximité avec les zones.
Dans les cellules B4, C4 et D4, les formules "=ventes – dépenses" travaillent en identifiant chaque cellule selon
la proximité. On parle d'indentification par intersection implicite.
Au contraire, la cellule A6 qui donnera comme réponse la valeur de 10, l'obtient par une identification basée sur
l'intersection explicite.
Les outils de gestion des
noms de cellules sont dans
l'onglet Insertion et dans le
menu contextuel des cellules
Remarques :
 Il est évident que les noms de cellules forment un adressage absolu.
 Le nom porté par une cellule ou une zone s'affiche dans la zone nom de la feuille.
 Contrairement à la version XP, la zone Nom ne permet plus d'attribuer des noms à des cellules.
EXCEL V2010 1213b 1GT.docx
9 de 46
12/02/2014
Notions de base et avancées d'Excel
Modification d'une feuille de calcul
Insertion et suppression de lignes et de colonnes
Les commandes d''insertion et de suppression des lignes et colonnes se trouvent dans le menu contextuel des
titres de lignes ou de colonnes.
Une ligne sélectionnée, …
Menu contextuel
…une ligne insérée.
Déplacement et copie de cellules de données par glissement
Les données sont déplacées ou copiées en faisant glisser les cellules sélectionnées.
La même méthode avec la touche CTRL permet la copie.
Les commandes couper, copier et coller et leur raccourci ^X, ^C et ^V fonctionnent également en utilisant le
presse-papier permettant l'importation ou l'exportation vers d'autres application.
Effacement et Suppression de cellules
Voici deux opérations différentes à ne pas confondre.


La commande "effacer le contenu" ou l'appui sur la touche Delete efface le contenu de la cellule.
La commande 'Supprimer…' retire la cellule de la feuille et la remplace par une voisine.
Commande 'Effacer le contenu'
EXCEL V2010 1213b 1GT.docx
Commande 'Supprimer…'
10 de 46
12/02/2014
Notions de base et avancées d'Excel
Fractionnement d'une feuille au sein d’une fenêtre
La commande permettant le fractionnement se
trouve dans la zone Fenêtre de l’onglet Affichage.
Il est également possible d’utiliser une des poignées
se trouvant à proximité des ascenseurs
Table ASCII des caractères
Pour supprimer un fractionnement, utilisez un double clic sur la ligne de séparation ou repoussez cette ligne vers
l’extérieur de la fenêtre.
Le défilement des données au sein d’une feuille
Vu l'étendue des feuilles de calcul, il peut être nécessaire de visualiser plusieurs zones d’une feuille qui ne
peuvent apparaître ensemble lors de l'affichage classique d'une fenêtre.
Avec des performances diverses, trois méthodes sont possibles : figer des lignes ou colonnes, fractionner une
fenêtre ou encore ouvrir des fenêtres montrant chacune des vues différentes d'une même feuille de calcul.
Figer des lignes, des colonnes ou des volets
Figer les lignes supérieures ou seulement la première ligne,
figer les colonnes de gauche ou seulement la première, ou
encore figer une combinaison de ces zones, permet de
conserver affichées les données contenue dans ces zones et de
permettre le défilement du reste de la feuille.
Pour figer, après avoir sélectionné une cellule, prenez la
commande "Figer les volets" dans la zone Fenêtre de l’onglet
Affichage. Choisissez ensuite une des trois options proposées
dans la liste déroulante.
Dans le cas de l’option « figer la
ligne supérieure », quelle que soit la
cellule sélectionnée avant la
commande, seule la première ligne
sera figée comme le montre la figure
ci-contre.
EXCEL V2010 1213b 1GT.docx
11 de 46
12/02/2014
Notions de base et avancées d'Excel
Dans cet exemple, c'est la cellule B3 qui était
sélectionnée avant de figer avec l’option « figer les
volets ».
De ce fait, la colonne A est figée par rapport à un
défilement horizontal et les lignes 1 et 2 le sont par
rapport à un défilement vertical.
Afin de libérer les lignes ou les colonnes figées et
ainsi revenir au mode de fonctionnement habituel
pour le défilement des feuilles, il faut utiliser
l’option « libérer les volets » de la même
commande.
Affichage des fenêtres
Il est possible d’afficher simultanément plusieurs fenêtres. Ceci permet de gérer aussi
bien des feuilles de plusieurs classeurs différents (Fichier/ Ouvrir), que des feuilles
différentes au sein d’un classeur (Affichage / Fenêtre / Nouvelle fenêtre), ou encore des
vues différentes d’une même feuille (Affichage / Fenêtre / Nouvelle fenêtre). Dans ces
deux derniers cas, les fenêtres d'un même classeur seront pointées par un indice
numérique
Deux vues différentes de la même feuille
Deux fichiers différents
Deux feuilles d’un même fichier
EXCEL V2010 1213b 1GT.docx
12 de 46
12/02/2014
Notions de base et avancées d'Excel
Insertion, suppression et renommage de feuilles
En cliquant avec le bouton droit de la souris sur un des onglets de feuille, le menu contextuel proposera plusieurs
commandes relatives à la feuille.
 La commande Insérer permet d'ajouter une feuille, un graphique, …
 La commande Supprimer supprime la feuille,
 La commande Renommer permet de changer le nom de l'onglet de la feuille sélectionnée.
 La commande Déplacer ou Copier permet de déplacer ou de copier dans le même classeur ou dans un
autre classeur ouvert…
Un double-clic permet directement de renommer l’onglet d’une feuille.
Déplacement et copie de feuilles dans un classeur
Pour déplacer une feuille, il suffit de la glisser à l'aide de la souris en la prenant par son onglet.
La même action avec la touche CTRL permet la copie intégrale de la feuille.
EXCEL V2010 1213b 1GT.docx
13 de 46
12/02/2014
Notions de base et avancées d'Excel
Protection des cellules
La protection des cellules doit préserver les contenus des cellules contenant des formules ou des textes
d'illustration. De la sorte, quelles que soient les actions menées par l'utilisateur de la feuille, ces cellules rendues
inaccessibles continueront à calculer selon leur formule ou à afficher les textes introduits. Par contre, la
protection des cellules ne doit pas s'appliquer aux cellules, dites d'entrées, contenant les valeurs influant les
résultats. Celles-ci doivent rester accessibles à l'utilisateur afin que la feuille de calcul puisse continuer à remplir
son rôle en acceptant de nouvelles valeurs.

Méthode :
Une feuille protégée doit être déprotégée pour
être corrigée.
La protection d'une cellule s'effectue en deux
manipulations distinctes qui sont le verrouillage et
l'activation de la protection de la feuille.
Seules les cellules à protéger doivent être
verrouillées. Comme les concepteurs d'Excel ont
fait le choix de verrouiller par avance toutes les
cellules de la feuille, l'opération consiste dès lors à
ôter le verrou des cellules ne devant pas être
protégées. En d'autres mots, il faut ôter le verrou de
toutes les cellules d'entrée.
La seconde opération consiste alors à activer la
protection. Celle-ci agit sur toutes les cellules de la
feuille dont le verrou est activé.
Le verrouillage se trouve dans l'onglet "Protection"
de la fenêtre "Format de cellule" du menu
contextuel (clic droit)
Vous trouverez l'activation de la protection dans le
ruban "Révision".
Remarques :
 N'utilisez pas de mot de passe pour vos
travaux scolaires
Masquage
Le masquage d'une cellule permet de masquer son contenu de la barre de formule.
Comme pour la protection des cellules, ce processus se fait en deux étapes; le masquage puis l'activation de la
protection. Le masquage est géré par la case à cocher "Masquer" se trouvant dans l'onglet protection de la fenêtre
format de cellule.
EXCEL V2010 1213b 1GT.docx
14 de 46
12/02/2014
Notions de base et avancées d'Excel
Manipulation sur un groupe de feuille
Excel permet de travailler simultanément sur plusieurs feuilles en les sélectionnant par leur onglet.
L'exemple ci-dessous montre deux feuilles 'feuil1' et 'feuil2' dans lesquelles les données de la ligne 45 ont été
introduites en une seule saisie.
Pour ce faire, sélectionnez les deux onglets des feuilles concernées et encodez les données dans une feuille,
l'autre recevant automatiquement les mêmes données. Le nom de l’onglet apparaissant en gras désigne la seule
feuille qui située à l'avant plan.
Pour revenir au travail individuel dans une seule feuille, il faut d'abord activer une feuille indépendante.
EXCEL V2010 1213b 1GT.docx
15 de 46
12/02/2014
Notions de base et avancées d'Excel
Mise en forme d'une feuille de calcul
Modification de la largeur de colonne ou de la hauteur de ligne
Les bordures séparant les colonnes ou les lignes permettent d'en ajuster les largeurs ou les hauteurs
respectivement.
Faire glisser la bordure pour redimensionner
la colonne à une largeur désirée.
Double clic pour ajuster automatiquement à
la largeur du contenu le plus long de la
colonne.
Faire glisser la bordure pour
redimensionner la ligne à une
hauteur désirée.
Double clic pour ajuster
automatiquement à la hauteur du
contenu le plus haut de la ligne.
Masquage de lignes ou de colonnes
La commande Masquer du menu contextuel des en-têtes de lignes ou de colonne permet de faire disparaître
provisoirement les colonnes ou les lignes sélectionnées. Les données ne sont pas affectées.
La commande Afficher permet de faire réapparaître les colonnes ou les lignes masquées.
EXCEL V2010 1213b 1GT.docx
16 de 46
12/02/2014
Notions de base et avancées d'Excel
Alignement des données dans les cellules
Ces réglages d’alignement sont contrôlés par l'onglet
Alignement de la boîte de dialogue Format de Cellule
du menu Contextuel.
Ils sont aussi disponibles par le ruban de l’onglet
Accueil dans la zone alignement.
Le bouton situé en bas à droite dans la zone
alignement ouvre également l’onglet alignement de la
boite de dialogue format de cellule.
Orientation
Fusionner et centrer
Aligner en bas.
Centrer le texte
Aligner au centre
Centrer le texte
Aligner le texte
à gauche.
Centrer le texte
Aligner le texte
à droite.
Remarque : La commande clavier "Alt + Enter", ou
la coche "Renvoyer à la ligne automatiquement" de
la zone "alignement" de l'onglet "accueil" provoque
au sein d'une cellule le passage à la ligne suivante.
EXCEL V2010 1213b 1GT.docx
17 de 46
12/02/2014
Notions de base et avancées d'Excel
Formats d'affichage (Nombre, Date, Monétaire,….)
Excel propose une panoplie de formats d'affichage prédéfinis dans l'onglet Nombre de la boîte de dialogue
Format de Cellule qui s’ouvre en utilisant le bouton du menu contextuel ou le bouton lanceur de la zone nombre.
.
Plus directement Xl propose les formats prédéfinis les plus courants (pour-cent, monétaire, milliers) activables à
l’aide des boutons ad-hoc de la zone nombre du ruban Accueil ou encore par la boîte d’outils accompagnant le
menu contextuel.
Remarquez la liste déroulante proposant des formats plus particuliers.
Format Nombre sans décimale
Format pourcentage %
Format monétaire € France
Format Date jj/mm/aa
Formats d'affichage personnalisés
En complément des formats prédéfinis, Xl vous
offre la possibilité de définir vos propres formats
d'affichage. Ils sont définis à l’aide de codes à
respecter et mémorisés dans la zone Type de
l'onglet Nombre de la boîte de dialogue Format de
Cellule.
De la sorte, vous pourrez définir des unités comme
des km, cm³, l, m, kWh, m/s accompagnant des
valeurs numériques et vos feuilles de calcul y
gagneront en clarté.
Les possibilités offertes par les formats d'affichage
personnalisés sont multiples et ne se limitent pas à
la création d'unité accompagnant des valeurs.
EXCEL V2010 1213b 1GT.docx
18 de 46
12/02/2014
Notions de base et avancées d'Excel
Vous pouvez spécifier jusqu'à quatre sections de codes de format. Ces codes de format, séparés par des pointsvirgules, définissent, dans l'ordre, les formats des nombres positifs, des nombres négatifs, des valeurs nulles et
du texte. Si vous ne spécifiez qu'une seule section, elle est utilisée pour tous les nombres.
Pour afficher dans une cellule du texte qui accompagnera la donnée numérique introduite, mettez le texte entre
guillemets " " sauf s'il s'agit d'un des caractères suivants qui sont affichés sans qu'il soit nécessaire d'utiliser les
guillemets : $ - + / ( ) : ! ^ & ' ~ { } = < > et le caractère d'espacement.
Pour reproduire un caractère dans toute la largeur de la cellule non vide de valeur, faîtes précéder ce caractère du
couple @*
Par exemple, tapez @*- pour faire suivre un nombre, d'un nombre suffisant de tirets pour remplir la cellule.
Pour mettre en forme des nombres comportant des décimales, insérez les espaces réservés numériques suivants :
 # n'affiche que les chiffres significatifs et non les zéros non significatifs.
Exemple : avec le format ##0,00 la valeur 012,29 s'affiche 12,29
 0 (zéro) affiche les zéros non significatifs
Exemple : avec le format 000,000 la valeur 12,29 s'affiche arrondie à 012,290
Remarques :
 Si le nombre contient plus de décimales qu'il n'y a d'indicateurs de position dans le format, il est arrondi
pour avoir autant de décimales que d'indicateurs de position.
Exemple : avec le format ##0,00 la valeur 12,129 s'affiche arrondie à 12,13
 Si ce nombre est composé de plus de chiffres à gauche du séparateur décimal qu'il n'y a d'indicateurs de
position, les chiffres supplémentaires sont affichés.
Exemple : avec le format ##0,00 la donnée 1234,56 s'affiche 1234,56
 Si le format ne contient que des symboles numériques (#) à gauche du séparateur décimal, les nombres
inférieurs à 1 commencent par un séparateur décimal.
Exemple : avec le format ##,000 la donnée 0,129 s'affiche ,129
Exemples :
1.
2.
3.
4.
Sous le format #.##0 "m³", le montant 100 encodé tel quel pour exprimer le volume s'affichera
100 m³.
Sous le format #.##0 "€", ou le format #.##0 € car ce caractère particulier n'exige pas les ", le
montant 200000 encodé tel quel pour exprimer le montant s'affichera 200.000 €. Cet affichage
sera obtenu plus rapidement par le bouton
ou
demandant le format monétaire en
cours.
Sous le format #.##0,00 "€/personne", le montant 5500 encodé tel quel pour exprimer le coût
par personne d'une activité, s'affichera 5.500,00 e/personne.
Sous le format 0,00 "€/km.m³", le montant 1 encodé tel quel pour exprimer le coût par
kilomètre et par mètre cube d'un transport s'affichera 1,00 €/km.m³.
EXCEL V2010 1213b 1GT.docx
19 de 46
12/02/2014
Notions de base et avancées d'Excel
Mise en forme des cellules
Excel permet de garnir les cellules et les plages de cellules avec des bordures, des couleurs, des motifs ou encore
des ombrages de couleurs. Les possibilités sont presque infinies tant les combinaisons sont multiples.
Ces mises en formes sont contrôlées par les onglets 'Bordures' et 'Remplissage' de la boîte de dialogue Format de
Cellule ou pour certains paramètres par des boutons situés en de nombreux endroits du ruban accueil.
Vous remarquez sur ces illustrations la possibilité de choisir différents types de traits ainsi que différentes
couleurs pour les quatre traits de périphérie formant la bordure, les deux traits internes à la zone et les deux
diagonales.
Il est également important de savoir que ces
propriétés sont gérées par la boîte de dialogue
'Format de cellule'.
EXCEL V2010 1213b 1GT.docx
20 de 46
12/02/2014
Notions de base et avancées d'Excel
Utilisation des formats prédéfinis ou des styles
Pour gagner du temps, à l'aide des boutons de commande de la zone styles du même ruban de commandes, vous
pouvez également utiliser les mises en forme automatiques proposées, ou encore, pratiquer les styles dont les
notions sont identiques à celles étudiées en Word.
Pinceau – Outil 'Reproduire la mise en forme'
Le bouton ''Pinceau-Reproduire la mise en forme' permet de copier rapidement les formats d'une cellule ou d'une
plage de cellules et de les appliquer à d'autres cellules ou plages
La première étape consiste à
sélectionner la cellule (ou la plage)
modèle dont on souhaite copier la
mise en forme. (ici la seule cellule sur
fond jaune de la première série)
Ensuite le bouton Pinceau doit être
activé. Un clic l'active en mode "one
shoot", tandis qu'un double clic le
positionne en mode permanent. Le
pointeur de la souris se transforme
alors en Pinceau.
La troisième étape consiste à balayer
la zone qui doit adopter la mise en
forme copiée.
En mode permanent, le pinceau peut
être utilisé à volonté et ainsi servir à
balayer plusieurs zones distinctes. La
touche "Esc" ou un clic sur l'outil
"pinceau" permet de désactiver le
mode permanent.
EXCEL V2010 1213b 1GT.docx
21 de 46
12/02/2014
Notions de base et avancées d'Excel
Les impressions
L'aperçu
Un premier aperçu est rapidement
accessible par le bouton "Mise en
page" se trouvant dans le coin
inférieur droit de la ligne de
statut.
Basculé dans ce mode "mise en
page", l'index de grossissement
vous permettra de choisir le
nombre de pages que vous
souhaitez afficher.
Le premier bouton de ce groupe
permet le retour au mode normal.
La gestion de l'impression
Il est nécessaire de visualiser l'aspect que prendra la feuille de calcul imprimée. Ceci permet d'économiser du
papier, de gagner du temps, ou tout simplement de visualiser le document avec les éventuels sauts de pages .
Le menu "Imprimer" offre de nombreux réglages
dont voici les principaux :
 Le choix de l'imprimante,
 Le nombre de copies du travail
d'impression,
 La sélection des feuilles et des pages à
imprimer,
 L'assemblage si plusieurs exemplaires
sont demandés
 L'orientation du papier,
 Le format du papier,
 Le choix de l'échelle d'impression qui
permet d'ajuster l'impression à un
nombre de feuilles désirés.
Le menu "imprimer" permet aussi de visualiser
une simulation de l'impression de chacune des
pages grâce à un compteur de pages imprimées
situé en bas d'écran.
Par ce menu, il est également possible d'appeler
la boîte de dialogue "Mise en page" qui se
compose de plusieurs onglets gérant plusieurs
aspects de la disposition.
EXCEL V2010 1213b 1GT.docx
22 de 46
12/02/2014
Notions de base et avancées d'Excel
La mise en page
La boîte de dialogue 'Mise en page' du menu
'Fichier' permet de gérer de nombreux
paramètres de la mise en page.
Parmi les plus classiques, rappelons :
 la gestion des en-têtes et pieds de page
avec les insertions automatiques de la
date d'impression, du nom du fichier et
de l'onglet.
 L'orientation des pages (portrait ou
paysage)
 La gestion des marges
Parmi les moins connues, bien qu'utiles, il y a :
 l'ajustement des sauts de pages et des
largeurs des colonnes,
 la répétition en haut de chaque page de
certaines lignes ou à gauche de
certaines colonnes
 la gestion de l'échelle d'impression.
 Les centrages horizontal et vertical sur
chaque page
 Le quadrillage
 Les en-têtes de lignes et de colonnes
Remarquez la répétition des trois premières
lignes sur chaque page obtenu par une action
dans le champ "ligne à répéter" et ci-dessous le
centrage parfait des données par rapport à la
feuille dû aux coches centrer sur la page.
EXCEL V2010 1213b 1GT.docx
23 de 46
12/02/2014
Notions de base et avancées d'Excel
Gestion d'objets d'illustration
Créations d'objets d'illustration
A l'aide de l'onglet Insertion, il est possible d'utiliser des illustrations telles que des images, clipparts, formes
graphiques, smartArts, ou des graphiques appropriés pour illustrer les données numériques, ou encore des textes
avec l'outil zone de texte ou l'outil WordArt afin de commenter les feuilles à l'aide de textes embellis.
Le menu contextuel de chaque objet propose une commande format par laquelle toutes les propriétés de l'objet
sont modifiables. Je vous laisse découvrir les nombreuses possibilités qu'elles proposent.
La superposition des objets
Dans le menu contextuel des objets graphiques, les commandes "mettre au premier plan " et "mettre à l'arrièreplan" permettent de gérer l'ordre des objets qui se superposent.
EXCEL V2010 1213b 1GT.docx
24 de 46
12/02/2014
Notions de base et avancées d'Excel
Les Graphiques
Un graphique est la représentation visuelle des données, il facilite l'interprétation des données.
Chaque type de graphique convient à un domaine d'application mais un domaine d'applications peut parfois
utiliser plusieurs types de graphiques.
Les domaines d'application de chaque type sont vus au cours, notamment pour les types principaux : Courbe,
Nuage de points, Secteur, Histogramme, Radar,…
Utilisation de l'assistant graphique
L'Assistant Graphique vous guide tout au long de la création d'un graphique, aussi il semble inutile de décrire
chaque étape. Cependant, il est absolument nécessaire de connaître le vocabulaire et de bien analyser les données
faisant l'objet de l'étude.
Avant même de lancer l'assistant, il est
préférable de sélectionner les données
faisant l'objet du graphique.
Il se peut que vous ne vouliez représenter,
sous forme graphique, qu'une partie des
données d'une feuille de calcul. Aussi, dans
ce cas, vous pouvez :
 masquer des lignes ou des colonnes
de la feuille de calcul,
 créer un graphique à partir de
sélections de plages non adjacentes
de la feuille de calcul en définissant
les séries à l'aide des touches CTRL
et Maj
EXCEL V2010 1213b 1GT.docx
25 de 46
12/02/2014
Notions de base et avancées d'Excel
Quelques exemples de types
de graphiques
Surface
Nuage de points
1,00
0,80-1,00
0,80
2000
1800
1600
1400
1200
1000
800
600
400
200
0
0,60-0,80
x²
0,60
x³
0,40
0,40-0,60
0,20-0,40
0
5
6
7
8
9 10 11 12 13
Ligne - Courbe
-1,4
1
4
0,4
3
-0,2
2
-0,8
1
-1,4
0,00
0
0,00-0,20
1,4
0,20
Cartographie – Bulles (3 dimensions)
Consommations journalières
durant 10 jours de trois ménages
2000 L
10
9
1500 L
8
1000 L
7
500 L
6
0 L
1
2
3
4
5
6
7
8
9
10
5
4
Colonne Histogramme 2D
3
Consommations moyennes journalières des trois
ménages
2
2000 L
1
1500 L
0
1000 L
0
500 L
1
2
3
4
5
6
7
8
9
10
0 L
A
B
C
Secteurs 2D
39
16
chasses d'eau
usage alimentaire
arrosages
nettoyage
vaisselle
lessives
hygiène
43
5
8 5
4
EXCEL V2010 1213b 1GT.docx
26 de 46
12/02/2014
Notions de base et avancées d'Excel
Bulles
140
Index vs prix mazout 2000- 2010
120
100
80
60
40
20
0
1998
2000
2002
2004
2006
2008
2010
2012
Boursier (Max , Min, Ouverture, Clôture)
70
60
50
40
30
20
10
0
ATOS ORIGIN ACCOR
ALSTOM
APRR
ADP
Radar
qualité
optique
rapidité
10
8
6
4
2
0
rendu
couleurs
autofocus
sensibilité
flash
EXCEL V2010 1213b 1GT.docx
27 de 46
12/02/2014
Notions de base et avancées d'Excel
Classement ou Tris d'une liste
Classement selon un seul critère
Les outils
permettent de classer les données d'un tableau selon l'ordre croissant ou l'ordre
décroissant d'un un seul critère. Le fond jaune du bouton indiquant l'option choisie (ici croissant)
Ils se trouvent dans le ruban l'onglet Données et dans le ruban Accueil. Les outils sont cependant différents.
Préférez l'outil du ruban Accueil car il est plus complet avec l'option "Tri personnalisé". De plus, contrairement à
l'outil du ruban Données, il considère la première donnée d'une liste comme devant faire partie du classement.
Ci-contre, deux classements qui
montrent qu'une liste peut être triée
selon des critères différents.
La première selon l'ordre croissant
des localités, la seconde selon l'ordre
croissant des codes postaux
Si le tableau que vous ordonnez
possède des colonnes non
sélectionnées, le gestionnaire vous
propose d'étendre votre sélection
afin de garder la cohésion entre les
données
Réflexions sur les classements
Travail :
Sur base de ces deux exemples de listes classées,
analysez l'algorithme du classement utilisé dans
Xl.
Interrogez-vous sur les règles concernant les
caractères spéciaux comme l'espace, le ç les
accentués, le trait d'union et les autres.
Quel est le comportement face aux majuscules et
minuscules?
Notez que ces règles ne sont pas universelles,
elles dépendent des options prises par les
différents logiciels.
EXCEL V2010 1213b 1GT.docx
28 de 46
12/02/2014
Notions de base et avancées d'Excel
Classement ou tri d'une liste (pl. critères)
Pour classer une liste selon plusieurs critères tout en définissant un ordre de priorité entre ceux-ci, complétez les
boîtes de dialogue liées à la commande 'Tri personnalisé' de la liste déroulante de la commande du ruban
Accueil.
En respectant l'ordre d'importance des critères, remplissez les zones 'trier par ' et 'puis par' de la boîte de dialogue
"tri"
Dans l'exemple ci-dessous, les produits sont classés en fonction du prix croissant, puis en cas de prix identiques
en fonction du nom et ensuite pour un même nom par en fonction de la référence et ce chaque fois selon un ordre
croissant.
Remarquez également la coche activée "Mes données ont des en-têtes" destinée à la gestion d'une éventuelle
ligne de titre. En l'absence de celle-ci, la boîte de dialogue mentionnera les repères des colonnes de votre feuille
de calcul.
EXCEL V2010 1213b 1GT.docx
29 de 46
12/02/2014
Notions de base et avancées d'Excel
Le Filtrage
Le filtrage consiste à ne conserver affiché que les lignes d'un tableau correspondant à un critère ou une
combinaison de critères. Dès que la demande de filtrage est désactivée (
), ou que les critères de
sélection sont retirés, la feuille retrouve tous ses éléments.
Le filtrage requiert une organisation en tableau où
les données de chaque objet sont placées dans des
colonnes d'une même ligne.
Après l'activation de la commande Filter de la liste
du bouton de même nom, des listes déroulantes de
sélection de critère apparaissent dans les colonnes
de la ligne supérieure du tableau préalablement
sélectionné
Il est évident que la multiplication des critères de sélection rend le filtre toujours plus sélectif puisque les lignes
doivent répondre à tous les critères précisés. Il n'est pas rare de pécher par excès et de n'avoir aucune ligne du
tableau (fiche) sélectionnée.
Options de
sélection
Dans ce cas ci-dessous, les modèles
filtrés correspondent au Nitendo et au
Train
Le filtrage peut également se faire selon des plages de sélection de natures différentes en adéquation avec le type
des données contenues dans les colonnes.
Type Date
Type numérique
Type texte
EXCEL V2010 1213b 1GT.docx
30 de 46
12/02/2014
Notions de base et avancées d'Excel
Ajout de sous totaux dans une liste
Vous pouvez utiliser les commandes Grouper et
Sous-total du ruban Données pour insérer dans un
tableau des sous-totaux et des totaux ou pour créer
un plan automatique.
Le bouton Dissocier permet de supprimer le plan
établi.
Les listes de données doivent être classées selon un
critère placé en première colonne (ligne) permettant
de former les groupes.
Le tableau ci-dessous calcule le nombre de consommations et le coût résultant par numéro de chambre.
Les fonctions proposées sont :Somme, Nombre,
Produit, Moyenne, Ecart type, Ecart type p, Var et
Var p, Max, Min.
Le bouton "Supprimer tout" permet de réinitialiser
le formulaire
Totaux et Sous- totaux
A l'aide des boutons 'signe –' qui apparaissent dans
la marge de sélection, il est possible de réduire
(masquer) les données afin de ne voir que les
différents niveaux de regroupement.
Les boutons 'signe +' permettent de réafficher les
données masquées.
Les deux zones de lignes (3,6) et (8,11) sont masquables permettant de réduire la feuille aux lignes de synthèses.
EXCEL V2010 1213b 1GT.docx
31 de 46
12/02/2014
Notions de base et avancées d'Excel
Les tableaux croisés dynamiques
Un tableau croisé dynamique est un tableau interactif qui facilite la synthèse des données qu'il contient. Après
avoir créé un tableau croisé dynamique, vous pouvez le manipuler pour présenter les données de plusieurs façons
d'où son appellation de dynamique.
La création
Suite à la demande de création de ce type de tableau par la commande se trouvant dans le ruban Insertion, une
boîte de dalogue, vous demande de préciser le tableau (avec les titres de colonnes) reprenant les données à
analyser, ainsi que la cellule de supérieure de gauche à partir de laquelle le tableau croisé dynamique prendra
forme. A la fin de cette création, vous pourrez déplacer les champs de synthèse.
Dès ce moment, Excel vous propose un lot de commandes spécifiques placées dans le ruban "Outils de tableau
croisé dynamique", ainsi qu'à droite le volet "liste des champs", représenté ci-dessous en mode fenêtré, reprenant
la liste des champs pouvant être sélectionnés pour constituer les lignes et colonnes du tableau croisé dynamique.
EXCEL V2010 1213b 1GT.docx
32 de 46
12/02/2014
Notions de base et avancées d'Excel
Manipulations dans un tableau dynamique
Il est possible de :
 créer un tableau croisé dynamique pour combiner et analyser les données venant de plusieurs zones
différentes
 modifier la présentation de votre tableau croisé dynamique pour faciliter la comparaison et l'analyse des
données.
 modifier l'emplacement d'un champ de ligne, colonne ou page en faisant glisser ce Champ à l'intérieur
d'une même zone dynamique ou vers une autre zone dynamique.
 déplacer les éléments à l'intérieur d'une ligne ou d'une colonne.
 ajouter ou supprimer des champs pour visualiser les données dont vous avez besoin.
 De changer de fonction par un double clic sur le champ pivot pour utiliser une des fonctions de synthèse
parmi Somme, Nombre, Moyenne, Max, Min…
EXCEL V2010 1213b 1GT.docx
33 de 46
12/02/2014
Notions de base et avancées d'Excel
Utilisation des simulations
Recherche d'une solution spécifique à une formule- valeur cible
Excel permet de déterminer la valeur d'une donnée qui donnera le résultat escompté à une expression. L'outil
valeur cible recherche donc la valeur d'une variable qui provoquera pour une fonction un résultat déterminé et
non pas de calculer le résultat de la fonction selon les valeurs des variables.
Les exemples sont nombreux. Parmi tous, prenons le cas classique d'un placement dont on veut déterminer le
montant du capital de départ à placer pour obtenir un montant déterminé pour une durée et un taux d'intérêt
connus et invariables.
La formule connue du capital placé à intérêts simples permet de connaître le montant du capital ayant fructifié
pendant une année. (Le fait que la durée choisie est d'un an, permet de simplifier l'expression en omettant cette
durée)
Capital fructifié = Capital de départ + Capital de départ * Taux d'intérêt
En traitant ce problème en tant que valeur cible, Excel nous épargne une transformation de l'expression
mathématique qui exprimerait le montant du capital de départ, qui est le montant recherché, en fonction du taux
d'intérêt et du capital fructifié.
Pour découvrir le montant à placer, Excel, suivant un algorithme, proposera, sans en faire mention, en une série
de valeurs pour la variable désignée (ici le montant à placer) jusqu'à ce que la valeur de la fonction soit obtenue
(le montant du capital fructifié.
Pour commencer, créez une feuille de
calcul permettant le calcul du montant du
capital fructifié.
Ainsi, la fonction calculant le capital
fructifié est entrée dans la cellule B4 avec
les cellules B1 et B2 comme variables.
Ensuite, à l'aide du bouton Analyse
scénarios qui se trouve dans la partie
"Outils de données" du ruban "Données"
activez l'option "Valeur cible" qui ouvre
la boîte de dialogue "Valeur cible".
Complétez cette boîte de dialogue qui
demande l'adresse (B4) de la fonction, la
valeur cible à atteindre (10000) ainsi que
l'adresse de la cellule (B1) contenant la
valeur du capital de départ qui joue ici le
rôle de variable.
A la fin du processus, Excel indiquera la
valeur trouvée ou l'impossibilité de
trouver une solution. En cas de valeur
trouvée, celle-ci prend place dans la
feuille de calcul.
Remarques :
Les systèmes peuvent apporter aucune, une ou plusieurs solutions. Les systèmes linéaires présentent
généralement toujours et une seule solution.
Pour les systèmes présentant plusieurs solutions, le tableur donnera toujours la même réponse si la recherche part
toujours des mêmes valeurs. Ainsi la découverte de plusieurs solutions demande de relancer plusieurs fois le
processus après avoir changé avec une certaine réflexion les valeurs initiales.
EXCEL V2010 1213b 1GT.docx
34 de 46
12/02/2014
Notions de base et avancées d'Excel
Réponses : 1 et 4
Exercice :
Grâce à nos connaissances de base, nous savons
qu'une fonction du deuxième degré passe deux fois
par une multitude de valeurs pour des valeurs
différentes de son unique variable.
12
x²-5x+6
7
Dans le cas précis de la fonction x²-5x+6, toutes les
valeurs supérieures à –0,25 peuvent être obtenues
par deux valeurs différentes de la fonction.
2
0
A l'aide de l'outil "valeur-cible" d'Excel , cherchez
les deux valeurs de la variable qui donnent deux à
la valeur de la fonction?
EXCEL V2010 1213b 1GT.docx
1
2
3
4
5
6
7
-3
35 de 46
12/02/2014
Notions de base et avancées d'Excel
Création et affichage de scenarii (scénarios)
L'intérêt d'une feuille de calcul est évidemment de générer un résultat pour un calcul qui dépend d'une multitude
de variables différentes dont les valeurs sont connues.
Par contre, dès que les valeurs de ces différentes variables reposent sur des estimations, celles-ci doivent pouvoir
être modifiées pour évaluer l'influence de ces nouvelles propositions sur le résultat. Malheureusement, sans la
pratique des scénarios, il est frustrant de voir une analyse avec ses résultats et ses hypothèses être remplacée par
une nouvelle analyse basée sur d'autres hypothèses.
Ces différentes analyses sont des scénarii qu'Excel vous permet de mémoriser dans un classeur.
L'exemple que nous prenons, est celui du budget dont on prépare au moins une version optimiste et une autre
dite pessimiste. Chacune des versions sera basée sur un certain nombre d'hypothèses concernant les recettes et
les dépenses.
Disposez d'abord normalement votre feuille de calcul en
utilisant de préférence des zones et cellules nommées.
L'activation du gestionnaire de scénarios se trouve dans
la zone "Outils de données" du ruban "Données".
Il permet d'ajouter, de supprimer, de modifier, de
fusionner, d'afficher et de créer la synthèse des
scénarios.
Activez le gestionnaire de scénarios", et cliquez sur
Ajouter.
Dans le champ Nom du scénario de la boîte de dialogue
"Ajouter un scénario" qui s'ensuit, tapez Optimiste
comme nom de votre scénario et dans le champ Cellules
variables de cette même boîte, ajoutez les adresses
séparées d'un point-virgule des données pouvant varier
(C3; C4; C6).
En refermant cette fenêtre en appuyant sur OK., la
fenêtre "Valeurs de scénarios" s'ouvre et encodez-y les
valeurs pour chaque champ pouvant varier.
Créez ensuite de la même façon d'autres scénarios dont
un que vous appellerez pessimiste avec de nouvelles
valeurs pour chacun.
Par la suite, le gestionnaire de scénarios, vous permet de
sélectionner un des scénarios existants pour en afficher
les données.
EXCEL V2010 1213b 1GT.docx
36 de 46
12/02/2014
Notions de base et avancées d'Excel
La synthèse des scénarios.
Dans la fenêtre " Gestionnaire de scénarios", pour créer une synthèse, appuyez sur le bouton portant ce nom.
Après avoir fourni l'adresse de la cellule résultante (ici C7), la synthèse, sous forme de plan, s'affiche sur une
nouvelle feuille. Vous constatez ainsi que les scénarios comparent les résultats obtenus dans les cellules
résultantes tout en rappelant les données introduites dans les cellules variables
EXCEL V2010 1213b 1GT.docx
37 de 46
12/02/2014
Notions de base et avancées d'Excel
Les tables
Les tables sont à une ou à deux variables. Elles permettent de calculer les multiples résultats issus d'une série de
variations de valeurs de deux variables au maximum.
L'outil "Table" se trouve dans le ruban "Données" à la rubrique "Outils de données" en troisième option de la
liste "Analyse scénarios"
Table à une seule variable
Dans un premier tableau, réalisez le calcul du temps
nécessaire pour la réalisation d'un certain nombre de
pièces en connaissant le temps de fabrication par pièce
et le temps de préparation nécessaire à la production.
Passez à la réalisation de la table (second tableau), en
indiquant dans une première colonne (ou ligne) les
différentes valeurs de variation de la variable. Dans la
seconde colonne (ligne), en en-tête de colonne,
référencez-y la cellule du premier tableau donnant le
temps global.
Sélectionnez ensuite la zone de la future table et
appelez la commande "table".
Dans la zone de texte "cellule d'entrée en colonne"
(ligne) de la boîte de dialogue "table", précisez la
cellule du premier tableau qui contient la valeur
pouvant varier (nombre de pièces).
La confirmation, en activant bouton OK, crée la table
qui montre la variation du temps nécessaire en
fonction du nombre de pièces.
Table à deux variables
La table à deux variables s'obtient selon la
même procédure qu'une table à une
variable.
Il faut commencer par la création du
premier tableau calculant la valeur de la
fonction dépendant de deux variables.
Spécifiez les différentes valeurs des deux
variables en en-tête de ligne dans la
première colonne de la table pour l'une et
en-tête de colonne sur la première ligne de
la table pour l'autre.
Placez la référence de la cellule de la
fonction dépendante des deux variables
dans la cellule située à l'intersection de la
première ligne et de la première colonne.
Après avoir sélectionné la zone de la table,
faites apparaître les résultats en utilisant
l'assistant par l'activation du bouton "table".
EXCEL V2010 1213b 1GT.docx
38 de 46
12/02/2014
Notions de base et avancées d'Excel
Le solveur
Utilisez le solveur lorsque vous avez besoin de trouver la valeur optimale d'une cellule donnée en modifiant les
valeurs de plusieurs cellules d'antécédents. En effet, le solveur maximalise ou minimalise le résultat d'une
relation mathématique, que l'on appelle fonction économique ou objectif, entre plusieurs variables.
Quant aux valeurs multiples sur lesquelles le solveur travaille pour trouver une solution, elles sont appelées
variables de décision ou variables de contraintes pour évoquer le fait qu'elles ne peuvent varier sans limites.
A la différence de l'outil valeur-cible, le solveur travaille sur plusieurs variables dans un contexte d'optimisation.
Les solveurs permettent de travailler sur des problèmes extrêmement complexes mais voici un problème des plus
simples pour illustrer les possibilités de cet outil.
Les boissons proposées ont des marges bénéficiaires différentes et sont disponibles en quantités différentes. Le
stock étant limité, le problème est de déterminer la quantité à vendre de chaque boisson de façon à optimiser les
bénéfices. Pour la composition des deux boisons, le mélange se fera dans les proportions 20 cl de coca pour 5 cl
d'alcool. Dans ces conditions, le mélange à base de whisky rapporte 10 € contre 8 € pour celui à base de blanc.
Comme à l'habitude, la feuille de calcul doit être conçue en écrivant les relations qui décrivent les résultats
cherchés en fonction des variables. Dans ces formules, les valeurs cherchées qui sont appelées à varier seront
provisoirement remplacées par des valeurs inconnues. Il est possible qu'un choix malheureux de ces valeurs
initiales empêche le solveur de trouver une solution !
Activez la commande "Solveur" de la zone "Analyse" du ruban "Données"
Dans la boîte de dialogue "Paramètres du solveur",
 Définissez la cellule cible et sa contrainte ou sa valeur objectif à réaliser.
 Indiquez ensuite les adresses des cellules contenant les variables autorisées à varier de valeur.
 Introduisez les contraintes auxquelles doivent satisfaire le problème.

L'objectif est de maximaliser la valeur du gain total (F12).
Les contraintes expriment que les quantités bues sont
limitées par les stocks disponibles et qu'elles s'expriment
par des nombres positifs.
EXCEL V2010 1213b 1GT.docx
39 de 46
12/02/2014
Notions de base et avancées d'Excel
L'activation du bouton Résoudre de la boîte de dialogue "paramètres solveur" lancera le processus permettant de
connaître les quantités à vendre pour réaliser l'objectif de plus gros bénéfice possible.
Le solveur propose la vente de 15 blanc-coca et 60 whisky-coca pour réaliser un profit maximum. Il montre aussi
que le coca et le whisky seront dans ces conditions entièrement consommés.
Installation du complément Solveur
Le solveur fait partie des outils complémentaires d'Excel à installer par les compléments se trouvant dans les
options d'Excel.
EXCEL V2010 1213b 1GT.docx
40 de 46
12/02/2014
Notions de base et avancées d'Excel
Utilisation d'un modèle de classeur
Un modèle de feuille de calcul est une
feuille qui peut être utilisée à volonté
pour faciliter la réalisation d'autres
classeurs du même type.
Dans Excel, Microsoft propose de
nombreux modèles prédéfinis
(Budgets, Calendriers, Etats, Factures,
feuilles de présence,…) mais vous
pouvez aussi utiliser vos propres
créations, vos modèles personnalisés.
La Commande fichier/nouveau vous
permet de choisir entre un nouveau
classeur ou un modèle.
Les modèles office.com seront
téléchargés grâce à votre connexion
internet.
Création d'un modèle personnalisé
Pour créer un modèle de feuille personnalisé, procédez de la manière suivante:
1. Créez un classeur ne contenant qu'une seule feuille.
2. Etablissez votre modèle avec la mise en forme, les styles, le texte et les autres informations.
3. Enregistrez le fichier en précisant Modèle dans la zone Type de fichier. Ainsi il sera enregistré au format .xlt.
Pour la version 2010, les fichiers sont conservés dans le dossier faisant partie de votre profil:
C:\Users\NomUtilisateur\AppData\Roaming\Microsoft\Templates
Versions précédentes
C:\Documents and Settings\USERNAME\Application Data\Microsoft\Modèles
Utilisation d'un modèle personnalisé
Pour insérer une nouvelle feuille basée sur un
modèle donné, il faut sélectionner un onglet de
feuille et choisir l'option "Insérer" dans le menu
contextuel obtenu avec le bouton droit de la
souris.
La boîte de dialogue "Insérer" vous permettra
alors de choisir le modèle désiré qui s'insèrera
dans le classeur actif.
EXCEL V2010 1213b 1GT.docx
41 de 46
12/02/2014
Notions de base et avancées d'Excel
Audit des cellules
L'audit des cellules montre les liaisons de dépendances qui existent entre les cellules. Ceci permet de faciliter la
recherche des erreurs et la compréhension des relations entre les cellules.
Vous trouverez dans la zone "Audit de formules" du ruban "Formules" les outils de l'audit dont ceux permettant
de repérer les antécédents et les dépendants.
Le bouton "Afficher les formules" ou le raccourci CTRL + " commute cycliquement l'affichage normal d'une
feuille de calcul en affichage des formules contenues dans toutes les cellules.
Les annotations
Un point rouge, non imprimable, indique la présence d'un commentaire. L'affichage des commentaires est régi
par la commande affichage/commentaire et par les propriétés individuelles de chaque cellule.
EXCEL V2010 1213b 1GT.docx
42 de 46
12/02/2014
Notions de base et avancées d'Excel
Insertion d'objets et Liaisons
Un environnement informatique se doit de permettre de communiquer et de partager l’information.
Le problème de la communication n’est pas seulement d’importer les données au sein d’autres applications, mais
c’est aussi de permettre leurs mises à jour.
Ici, par objet, entendez une information créée par un programme source. Ce peut être un dessin de Paint, un
tableau de chiffres créé sous Excel ou simplement une donnée, un son , un texte, etc.
Les outils d’échange de données
La méthode OLE (OBJECT LINKING and EMBEDDING objet lié et incorporé) est une copie d’un objet au sein
d’une application, l’objet copié gardant en mémoire le souvenir de son application génératrice. Tant que
l’application native se trouve à portée de l’objet incorporé, au travers de l’application accueillante, l’objet pourra
être retravaillé.
La méthode DDE (DYNAMIC DATA EXCHANGE échange dynamique de données) est une copie d’un objet au
sein d’une application, l’objet copié gardant en mémoire le souvenir de l’application génératrice ainsi que sa
liaison avec son fichiersource.
Grâce à la mémorisation de ce fichier source, toutes les modifications apportées à l’objet, le seront à la fois sur la
source et sur la copie.
Le lien dynamique assure donc en permanence une copie de la dernière version de l’objet source.
Il va de soi que l’application génératrice et l’objet source doivent rester disponibles selon un chemin connu, le
fichier doit donc être enregistré.
Cette méthode est particulièrement utile quand l’objet est appelé à subir de fréquentes mises à jour. Une mise à
jour des données sera demandée à chaque réouverture du fichier contenant la donnée copiée si la donnée source a
changé.
La méthode de coller-copier (sans liaison) permet une copie d’un objet au sein d’une application sans aucune
mémorisation, ni de l’application génératrice, ni de l’objet source. Cette intégration d’objets ne permet donc pas
de corrections, ni de mises à jour. C'est le simple coller-copier que vous utilisez souvent qui ne gère pas de
liaison.
Client Serveur
A ces concepts, il faut encore ajouter les notions de serveur et de client.
 Le client est l’application qui reçoit l’objet.
 Le serveur est l’application qui donne l’objet.
En effet, ces notions sont importantes dans la mesure où certaines applications exclusivement serveur ne peuvent
que donner des informations et d’autres, exclusivement client, ne peuvent que recevoir leurs informations.
Utilisez la commande collage
spéciale pour réaliser un lien
dynamique ou la commande
Insertion – objet en spécifiant
coller avec liaison.
Ci-contre la liaison entre une
valeur calculée dans Excel et
passée dynamiquement dans
Word.
Si la valeur devait être
recalculée par Excel, le
document Word sera
immédiatement mis à jour, ou
au plus tard le sera lors de sa
réouverture s'il était fermé au
moment du changement de la
donnée.
EXCEL V2010 1213b 1GT.docx
43 de 46
12/02/2014
Notions de base et avancées d'Excel
Options du Logiciel
Gestion du ruban. Possibilité d'ajouter l'environnement du développeur dont la programmation VBA.
EXCEL V2010 1213b 1GT.docx
44 de 46
12/02/2014
Notions de base et avancées d'Excel
Table de matière
LE LOGICIEL TABLEUR .................................................................................................................................. 3
COMPATIBILITÉ ENTRE LES VERSIONS .................................................................................................................. 3
COMMENT FONCTIONNE UN CLASSEUR ................................................................................................................ 3
LES FEUILLES .................................................................................................................................................... 3
SAISIE DE DONNÉES ............................................................................................................................................. 4
JOINDRE UNE CELLULE ......................................................................................................................................... 4
SÉLECTION DE CELLULES DANS UNE FEUILLE DE CALCUL .................................................................................... 5
UTILISATION DES MENUS CONTEXTUELS .............................................................................................................. 5
L'AIDE ................................................................................................................................................................... 5
ECRITURE DES EXPRESSIONS ET DE LIAISONS ENTRE CELLULES................................................. 6
LES RÉFÉRENCES.................................................................................................................................................. 7
LIAISONS DYNAMIQUES ENTRE CLASSEURS.......................................................................................................... 7
LA COPIE INCRÉMENTÉE POUR DES LISTES ........................................................................................................... 8
LA COPIE INCRÉMENTÉE DE CELLULES ................................................................................................................. 8
ADRESSAGE RELATIF ET ADRESSAGE ABSOLU .................................................................................................... 8
UTILISATION DES NOMS ....................................................................................................................................... 9
Les deux types d’intersection .......................................................................................................................... 9
MODIFICATION D'UNE FEUILLE DE CALCUL ....................................................................................... 10
EDITION D'UNE CELLULE ...................................................................................................................................... 4
INSERTION ET SUPPRESSION DE LIGNES ET DE COLONNES................................................................................... 10
DÉPLACEMENT ET COPIE DE CELLULES DE DONNÉES PAR GLISSEMENT .............................................................. 10
SUPPRESSION OU EFFACEMENT DE CELLULES .................................................................................................... 10
LE DÉFILEMENT DES DONNÉES AU SEIN D’UNE FEUILLE ..................................................................................... 11
FIGER DES LIGNES, DES COLONNES OU DES VOLETS ........................................................................................... 11
FRACTIONNEMENT D'UNE FEUILLE AU SEIN D’UNE FENÊTRE ................................ ERREUR ! SIGNET NON DEFINI.
INSERTION, SUPPRESSION ET RENOMMAGE DE FEUILLES .................................................................................... 13
DÉPLACEMENT ET COPIE DE FEUILLES DANS UN CLASSEUR ................................................................................ 13
GESTION DES FENÊTRES ..................................................................................................................................... 12
PROTECTION DES CELLULES ............................................................................................................................... 14
MASQUAGE ........................................................................................................................................................ 14
MODIFICATION DES FEUILLES DE CALCUL TRAITÉES EN GROUPE ....................................................................... 15
MISE EN FORME D'UNE FEUILLE DE CALCUL ...................................................................................... 16
MODIFICATION DE LA LARGEUR DE COLONNE OU DE LA HAUTEUR DE LIGNE ..................................................... 16
MASQUAGE ET AFFICHAGE D'UNE LIGNE ET D'UNE COLONNE ............................................................................. 16
ALIGNEMENT DES DONNÉES DANS UNE FEUILLE DE CALCUL .............................................................................. 17
FORMATS D'AFFICHAGE (NOMBRE, DATE, MONÉTAIRE,….) .............................................................................. 18
FORMATS NUMÉRIQUES PERSONNALISÉS ........................................................................................................... 18
MISE EN FORME À L'AIDE DE BORDURES, DE MOTIFS ET DE COULEURS ............................................................... 20
MISE EN FORME À L'AIDE DES FORMATS PRÉDÉFINIS OU DES STYLES ................................................................. 21
COPIE DES FORMATS À L'AIDE DE 'REPRODUIRE LA MISE EN FORME' .................................................................. 21
LES IMPRESSIONS........................................................................................................................................... 22
L'APERÇU ........................................................................................................................................................... 22
LA GESTION DE L'IMPRESSION ............................................................................................................................ 22
GESTION D'OBJETS GRAPHIQUES ....................................................... ERREUR ! SIGNET NON DEFINI.
CRÉATIONS D'OBJETS GRAPHIQUES .................................................................................................................... 24
RÉORGANISATION D'OBJETS GRAPHIQUES SUPERPOSÉS ...................................................................................... 24
LES GRAPHIQUES ........................................................................................................................................... 25
UTILISATION DE L'ASSISTANT GRAPHIQUE ......................................................................................................... 25
QUELQUES TYPES DE GRAPHIQUES ..................................................................................................................... 26
EXCEL V2010 1213b 1GT.docx
45 de 46
12/02/2014
Notions de base et avancées d'Excel
CLASSEMENT OU TRIS D'UNE LISTE ........................................................................................................ 24
Classement selon un seul critère ................................................................................................................... 28
RÉFLEXIONS SUR LES CLASSEMENTS .................................................................................................................. 28
CLASSEMENT OU TRI D'UNE LISTE (PL. CRITÈRES) .............................................................................................. 29
LE FILTRAGE ................................................................................................................................................... 30
AJOUT DE SOUS TOTAUX DANS UNE LISTE .......................................................................................................... 31
LES TABLEAUX CROISÉS DYNAMIQUES ................................................................................................. 32
LA CRÉATION ..................................................................................................................................................... 32
MANIPULATIONS DANS UN TABLEAU DYNAMIQUE ............................................................................................. 32
UTILISATION DES SIMULATIONS .............................................................................................................. 34
RECHERCHE D'UNE SOLUTION SPÉCIFIQUE À UNE FORMULE- VALEUR CIBLE ...................................................... 34
CRÉATION ET AFFICHAGE DE SCENARII (SCÉNARIOS) ......................................................................................... 36
La synthèse des scénarios. ............................................................................................................................ 37
LES TABLES........................................................................................................................................................ 38
Table à une seule variable ............................................................................................................................ 38
Table à deux variables .................................................................................................................................. 38
LE SOLVEUR ..................................................................................................................................................... 39
UTILISATION D'UN MODÈLE DE CLASSEUR .......................................................................................... 41
CRÉATION D'UN MODÈLE PERSONNALISÉ ........................................................................................................... 41
UTILISATION D'UN MODÈLE PERSONNALISÉ ....................................................................................................... 41
AUDIT DES CELLULES ................................................................................................................................... 42
LES ANNOTATIONS ............................................................................................................................................. 42
INSERTION D'OBJETS ET LIAISONS .......................................................................................................... 43
LES OUTILS D’ÉCHANGE DE DONNÉES ................................................................................................................ 43
CLIENT SERVEUR ............................................................................................................................................... 43
OPTIONS DU LOGICIEL ................................................................................................................................. 44
EXCEL V2010 1213b 1GT.docx
46 de 46
12/02/2014

Download Report