Formation / tutorial / cours EXCEL

première partie
1.1. L'écran Microsoft Excel

EXCEL est un tableur. Sa principale utilisation est le calcul automatique de formules.

Excel peut être vu comme une feuille de calcul. Les colonnes sont référencées par des
lettres, les lignes par des chiffres. La référence d'une cellule est reprise avec la combinaison
de la colonne suivie de la ligne. La référence de la cellule en haut à droite (à l'intersection de
la colonne A et de la ligne 1) est donc A1. Celle à l'intersection de la ligne 8 et de la colonne
D se nomme D8. Les majuscules - minuscules n'ont aucune importance.

Pour sélectionner une cellule, il suffit de se trouver dans la cellule. Pour sélectionner
une colonne, cliquez sur la référence de la colonne (A par exemple). Pour sélectionner
plusieurs colonnes, cliquez sur une référence colonne et poursuivez en maintenant la touche
gauche de la souris enfoncée. C'est de même pour les lignes. Pour sélectionner un groupe de
cellule, maintenez la touche gauche de la souris enfoncée et bougez la souris (quelque soit le
sens).

A retenir: les cellules Excel sont référencées par leur numéro de colonne suivi de leur
numéro de ligne

Un petit truc, pour sélectionner des ensembles de cellules non adjacentes, maintenez la
touche <CTRL> du clavier enfoncée lorsque vous sélectionnez la zone suivante.

Pour élargir (ou rétrécir) un colonne ou une ligne, pointez entre les références de
colonnes dont vous désirez modifier la taille. Il n'est pas possible comme dans Word de
modifier la taille d'une seule cellule.

1.2. Entrée des données, formules de base

Commençons tout de suite par un petit calcul.

Positionnez votre curseur sur la cellule A1 et tapez un

nombre (ici le chiffre 45). Faites de même dans la cellule
A2. Remarquez que le chiffre se met à deux place: dans
la cellule et dans la barre de formule d'Excel.
Nous souhaitons faire la somme de ces cellules et
obtenir le résultat dans la cellule A3. Dans un cas
général, nous utiliserions le calcul mental pour taper dans
la cellule le chiffre 99. Microsoft Excel va nous faciliter
la vie puisque non seulement, il va faire le calcul mais va
tenir également compte des modifications dans les 2
cellules pour afficher le résultat correct.

Pour signaler à EXCEL que le contenu de la cellule est un calcul à exécuter, le

contenu de la cellule doit commencer par = (+ est également accepté). Tapons dans A3
=A1+A2 (les références des cellules que nous souhaitons additionner). Lorsque nous
acceptons la formule (par la touche <Entrée>), le résultat de l'addition apparaît
automatiquement dans la cellule A3

En repassant sur cette cellule, le résultat apparaît dans

la cellule, tandis que la formule est affichée dans la
barre de formule. Pour modifier la formule, il vous faut
donc repasser dans la barre de formule. Un petit truc, si
vous appuyez sur la touche <F2>, vous pouvez
directement modifier le contenu de la formule dans la
cellule.

Si vous modifiez le contenu de la cellule A1 ou A2, le

résultat est directement affiché dans la cellule A3.
A retenir: les calculs commencent toujours par =, il ne faut pas d'espaces entre les
chiffres, références de cellules. La référence de cellule peut être tapée en minuscule ou
majuscule
 Si nous souhaitons le montant TVA comprise dans la cellule A4 (ou ailleurs), tapons la
formule =A3*1,21 et Excel reprend le résultat, même si nous changeons la valeur en A1 ou
en A2. Pour rappel, le taux de TVA en Belgique est de 21 % sur la majorité des produits.

Comme vous le voyez, vous pouvez mélanger des références de cellules et des chiffres
sans problèmes. Ceci est valable pour les 4 opérations

• + pour l'addition
• - pour la soustraction
• * pour la multiplication (la lettre X n'est pas reconnue)
• / pour la division.

Ce sont les 4 sigles du pavé numérique. Attention néanmoins aux parenthèses.

Le calcul 5+6*5 nous donnerait comme résultat 5+6=11 * 5= 55. Malheureusement, les règles
de l'école primaire sont toujours d'application. Pour l'ordinateur, la multiplication et la
division sont prioritaires. Le calcul ci-dessus donnera 6*5=30 + 5 = 35. En utilisant les
parenthèses =(5+6)*5 le résultat sera effectivement 55.

A retenir: EXCEL ne fait des calculs que sur des cellules contenant uniquement des
chiffres. Le calcul 5 * "5 PC" donnera un message d'erreur, le contenu 5 PC est vu par
EXCEL comme du texte.

1.3. Recopier des cellules dans EXCEL.

1. Dans la cellule B2, tapons le chiffre 45 et positionnons notre souris juste dans le coin droit
de la cellule, le curseur devient un +. En maintenant la souris enfoncée, déplaçons le curseur
vers la droite. Le chiffre est automatiquement recopié dans les cellules adjacentes, de même
pour un texte (YBET en C2 et déplacement vers la gauche). Le sens de déplacement n'a pas
d'importances.
3. Dans la cellule B8, tapons LUN, en recopiant les cellules, EXCEL recopie
automatiquement les abréviations des jours de la semaine, de même pour le jour en toute lettre,
abréviation du mois et mois complet.

7. En I5 (par exemple), tapons maintenant une date et recopions la cellule, les dates se suivent
automatiquement, tenant compte des mois et même des années.

Nous souhaiterions faire une suite de nombre (1 2 3, ...). Par le point 1, nous savons
que sélectionner un nombre et le recopier ne fait que remplir les cellules avec le même
nombre. Pour faire cette suite, nous devons d'abord signaler au tableur que nous voulons
effectivement cette progression arithmétique. Sélectionnons la cellule contenant le chiffre 1 et
la cellule adjacente contenant le chiffre 2 et seulement ensuite, recopions les cellules. Excel
va automatiquement créer la suite de nombre.

Ceci marche également avec les formules de calculs (avec des modifications) comme
nous le verrons dans l'exercice ci-dessous et au chapitre suivant

A retenir: le coin droit en bas d'une cellule permet de recopier son contenu vers d'autres
cellules.

1.4. Exercice: un début de facture.

Nous allons utiliser ces notions pour faire un petit tableau de type facture. Tous le
cours Excel premier niveau se basera sur ce petit exercice. Commençons par effacer le
contenu des cellules que nous avons tapé. Pour cela, sélectionnons l'ensemble des cellules où
se trouvent des nombres ou du texte et appuyons sur la touche <DELETE> (clavier belge) ou
<Suppr> (clavier français).

Tapons maintenant les textes ci-

dessous dans les bonnes cellules.

Le but est de taper la quantité, la

description, le prix et de retrouver
automatiquement (par calcul) le
montant total soit le prix multiplié par
la quantité. Ceci est la base de tous les
exercices de ce cours Excel puisque
nous l'utiliserons jusqu'à la dernière
formation, chaque fois en l'améliorant.

Rentrons les données suivantes dans la

ligne 2 (par exemple)

Le but est de retrouver automatiquement le prix dans la cellule A4. Nous allons donc
faire une formule dans la cellule D2 en multipliant la quantité et le prix unitaire dans la ligne
associée (ici la ligne 2). La formule sera: =A2*c2. Si la formule est tapée correctement, le
résultat est effectivement 800. Un changement dans la quantité ou dans le prix donnera
automatiquement le résultat.
= pour signaler un calcul

A2 pour signaler que nous souhaitons la quantité (colonne A) associée à la ligne 2

C2 pour signaler que nous souhaitons le prix unitaire (colonne A) associé à la ligne 2

Une facture, un devis, ... utilise généralement plusieurs lignes. Nous allons donc
compléter notre tableau en conséquence.

Commençons par positionner la

souris dans le coin droit de la
cellule D2 et recopions le
contenu vers le bas jusqu'à la
ligne 20. Les autres cellules
affichent 0. En effet, si nous
positionnons le curseur sur la
cellule D20, le contenu est 0
mais la formule est devenue
=A20*C20.

Excel a automatiquement mis

les formules à jour en fonction
du numéro de ligne. Même si
ceci n'est pas toujours
intéressant, cette mise à jour
permet de faire de grands
tableaux comprenant une
multitudes de formules sans trop
se fatiguer.

Nous allons compléter notre facture - devis Excel par une colonne Réduction. La
première opération est d'insérer une colonne entre la colonne C (prix unitaire) et D (Total).
Sélectionnons la colonne D et utilisons la touche droite de la souris pour afficher le menu
contextuel. Sélectionnons la commande "Insérer" et une colonne est insérée entre C et D. La
colonne D devient donc la colonne E.

Remplissons les 2 cellules comme ci-dessous. A ce stade, n'utiliser le sigle des

pourcentages %, tapez uniquement le montant de la réduction (ici 10). Notre montant total est
faux puisqu'il ne tient pas compte de la réduction. Nous allons donc modifier la formule en E2
pour inclure cette réduction.
 La formule en E2 devient: = montant de base - réduction, en sachant que la
réduction vaut le montant de base multiplié par la réduction divisée par 100 (puisque nous
n'utilisons pas de pourcentages).

<E2>=A2*C2 - A2*C2*D2/100 ou <E2>=A2*C2*(1-D2/100). Les 2 formules

donnent le même résultat. Il ne reste plus qu'à recopier la formule vers le bas jusqu'à la cellule
D20 comme précédemment. Excel met les formules par ligne à jour automatiquement

Avec cette première partie du cours Excel, nous manipulons déjà les cellules et leur
contenu et les 4 opérations de calcul. Ceci donne déjà un petit aperçu de notre facture - devis.
Néanmoins, Microsoft Excel est nettement plus complet puisque des fonctions sont
directement intégrées dans le logiciel. Le chapitre suivant de ce tutorial va nous permettre de
les manipuler.

2. Les fonctions Excel

1. La fonction Somme()
Reprenons l'exercice facture sous Excel du chapitre précédant. La première
modification serait d'ajouter un total de nos lignes, en dessous. Nous pourrions
utiliser en D21 une formule de type =E2+E3+ .... un peu long (en autre). Nous
allons utiliser notre première fonction Excel, la SOMME. Positionnons le
curseur sur la cellule E21 et cliquons dans la barre d'outils sur le symbole
La fonction =SOMME(E2:E20) s'inscrit dans la barre de formule, dans la cellule E21, E2:E20
est en sur brillance et la zone E2 à 22 est entourée. Si nous sélectionnons une autre zone avec
la souris, la zone entre parenthèse dans la formule est automatiquement modifiée en
conséquence mais ce n'est pas notre but. Pour accepter la plage de cellule, tapez <Entrée>.
Remarquez le ":" entre la référence E2 et la référence E20. Ce sera important pour la suite du
cours EXCEL.

L'intérêt d'utiliser cette formule à la place de =E+E3+E4+... est déjà explicite mais il y
en a une autre. Sélectionnez une ligne dans cet ensemble et utilisez le menu contextuel pour
insérer une ligne (touche droite de la souris)
 Reprenons notre résultat (maintenant en E22) et vérifions dans la
barre de formule la fonction. Elle a été modifiée automatiquement
par EXCEL pour tenir compte de cette insertion. Il faudra
néanmoins recopier la formule de prix de la ligne.

Il nous reste deux petites formules à rajouter: le montant de TVA et le montant à payer.
Si nous utilisons une TVA à 21 % (Belgique), la formule en E23 devient =E22*0,21 et la
formule en E24 (montant à payer) devient =E22+E23

A ce stade, il reste deux petits problèmes: les chiffres derrière la virgule et la

présentation. Nous les verrons plus tard.

A retenir: L'insertion d'une ligne ou d'une colonne modifie automatiquement la plage des
cellules d'une fonction.
2.2. Introduction à l'utilisation des fonctions

Le tableur EXCEL est utilisé par différentes professions: des mathématiciens,

logisticiens, banquiers, ingénieurs, ... La liste des formules disponibles est donc importante.
Mon but n'est pas de les voire toutes mais de vous permettre d'utiliser les fonctions qui vous
intéressent.

Sélectionnons une cellule vide. Pour appeler la liste des fonctions, il suffit de
cliquer dans la barre d'outils sur
ou d'utiliser la commande fonction dans le menu Insertion.

Après cette commande, la liste des fonctions possibles classées par catégorie apparaît
comme ci-dessous. Les catégories de fonctions sont à gauche et les fonctions incluses dans
chaque catégories à droite. Cette structure est très courante dans l'informatique. La
présentation est un peu changée en EXCEL XP et 2003 (notamment une aide pour la
recherche) mais le résultat est le même.

La catégorie "Dernières utilisées" reprend la dizaine des dernières commandes que

vous avez faites. La catégorie "toutes" reprend l'ensemble des fonctions d'Excel, les utilisées,
les moins utilisées et les … jamais utilisées. Sélectionnons la fonction somme que nous avons
utilisée plus

haut.

Je lis! Nombre1: nombre1;nombre2 représentent de 1 à 3à arguments .... Chaque

fonction est livrée avec une aide sur les différentes rubriques à remplir.

Pour désigner la plage de cellule dont je veux la somme, je vais utiliser le

bouton à coté de la plage "Nombre 1" demandée (cerclé en rouge). Une petite
barre d'outils blanche apparaît. En sélectionnant une plage de cellule avec la
souris, les références s'inscrivent automatiquement. Pour revenir à notre
fenêtre précédente, il suffit de cliquer de nouveau sur le petit icône ou de
 taper <Entrée>
Remarquez que les 2 références extrêmes de cette zone sont notées A2:A15.
Le : signale à Excel que vous souhaitez toute la plage. Faisons de même mais
en regard de Nombre2. La formule (affichée au-dessus) devient
=SOMME(A2:A15;C2:C10)

A retenir: Les " : " signalent à Excel que la plage reprend toutes les cellules entre les 2
références désignées. Par contre, le signe " ; " découpe des zone distinctes.

Le résultat ci-dessus reprendra donc la somme des cellules comprises entre A2 et A15
+ la somme des cellules reprises entres les cellules C2 et C10.

Les fonctions Max, Min, Moyenne, Mode s'utilisent de la même méthode.

2.3. Une fonction Excel simple, ENTIER: ENT().

Dans Insertion Fonction, sélectionnons la fonction ENT qui affiche la valeur entière
d'un nombre (nombre sans les chiffres derrière la virgule).

La fenêtre qui apparaît est identique à celle ci-dessus. Tout au-dessus, dans la barre des
formules, =ent(). En effet, une fonction nécessite un calcul et tous calculs commencent par =.
Il est suivi par le nom de la fonction, sans espaces.
 Nombre est le nombre que nous voulons arrondir à la décimale inférieure. Cliquons
sur la flèche rouge et la fenêtre disparaît pour laisser la place à notre grille habituelle.

A l'aide de la souris, cliquons sur la cellule pour laquelle nous souhaitons avoir le
nombre entier (exemple A3) et appuyons sur la touche <Entrée> ou sur la flèche en rouge en
haut. La fenêtre de fonction réapparaît comme ci-dessous.

La formule dans la barre des formules devient =ENT(A1). Nous pouvons maintenant appuyer
sur OK pour récupérer le résultat. Intéressons-nous à la manière dont Excell a écrit cette
fonction:

= puisque c'est un calcul

ENT, la fonction

(référence de cellule), la partie à calculer est entre parenthèses.

Remarquez qu'il n'y a pas d'espaces.

Voyons cette formule de manière plus théorique: =ent(nombre à calculer). Ce nombre

à calculer peut en fait être n'importe quoi, un chiffre, une référence de cellule ou .. le résultat
d'un calcul. Dans la barre de formules, nous pouvons par exemple taper =ent(A1+A2). Ceci
fera d'abord l'addition du contenu de A1 et du contenu de A2, la formule retirera la valeur
entière de cette formule. Voyons cette formule avec les aides de Microsoft.

Revenons à notre fonction ENT() de départ et utilisons l'icône pour faire apparaître la petite
barre.

Sélectionnons la cellule A2 et tapons immédiatement sur le sigle +. Si nous

sélectionnons la cellule A3 avec la souris, ceci devient dans la barre blanche A2+A3, et ainsi
de suite. Nous aurions également pu prendre une cellule contenant une autre fonction mais
cette façon de travailler pose quelques problèmes de présentation. Nous verrons les fonctions
imbriquées à la fin de ce chapitre.
 A retenir: Lorsque vous utilisez une fonction, vous pouvez utiliser les 4 opérations à
l'intérieur de l'opérante de la formule.

Une remarque, cette fonction ne permet pas vraiment d'arrondir les chiffres, elle se
contente de supprimer les chiffres derrière la virgule.

2.4. La fonction ARRONDI

La fonction arrondi permet d'arrondir à un nombre de chiffres souhaités un nombre

réel. Même si nous verrons qu'il y a moyen de demander de n'afficher qu'un certain nombre de
chiffres derrière la virgule, cette formule est primordiale. En effet, si vous demandez de
n'afficher que les 2 derniers chiffres derrière la virgule, EXCEL utilise tous les chiffres
derrière la virgule. La somme des arrondis n'égale pas forcément l'arrondi de la somme.

Nombres arrondi
20,3 20
20,3 20
20,3 20
Prenons un
petit exemple 20,3 20
somme des
Somme=81.2
arrondis = 80
arrondi de la
somme = 81

Ceci peut poser quelques petits problèmes de calcul. Par contre, dans le cas de la
fonction ARRONDI() (idem pour la fonction ENT() ci-dessus), les calculs à base de ces
cellules n'utiliseront que les nombres résultats de ces fonctions.

Remplissons Nombre par la cellule E22 (le résultat est l'arrondi de la valeur reprise
dans cette cellule - même si notre cellule E22 reprend un résultat de fonction) et le nombre de
chiffres derrière la virgule, utilisons 2 pour l'€. La formule devient =arrondi(E22;2).
 L'étape suivante de notre cours excel va être de mettre des calculs (fonctions) inclus
dans d'autres calculs (fonctions), par exemple l'arrondi d'une somme.

3.1. Imbrications de fonctions.

L'utilisation de fonctions Excel permet de calculer des fonctions complexes.

Néanmoins, souvent l'utilisation d'une fonction est insuffisante, il faut utiliser une fonction sur
un résultat d'une formule ou d'un ensemble de fonctions. La version Excel 95 permettait ceci
très facilement. Depuis la version 97 (et suivante), l'utilisation est plus ardue.

L'imbrication de fonctions consiste à remplacer des opérantes par d'autres fonctions.

Reprenons notre exercice de facturation des chapitres précédents.

J'ai déjà mentionné le problème des arrondis dans les résultats. Nous avons également
déjà vu au chapitre précédent la fonction arrondi.

Prenons la cellule E2=A2*C2*(1-D2/100). Pour ne pas avoir d'erreurs, cette fonction

devrait inclure l'arrondi de ce résultat. Nous pourrions utiliser la méthode du chapitre
précédent mais voyons cette possibilité d'une manière plus manuelle. Ceci nous préparera à la
suite de la formation EXCEL.

La fonction arrondi est de type: =ARRONDI(nombre à arrondir;nombre de chiffres)

Le calcul qui donne le prix total (réduction incluse) est =A2*C2*(1-D2/100)

Il va nous suffire de remplacer dans la fonction arrondi le nombre à arrondir par la formule de
prix de vente

Le résultat donne comme formule =ARRONDI(A2*C2*(1-D2/100);2). Il ne nous reste plus

qu'à recopier cette formule (coin en bas à droite) sur les 20 lignes de la facture.

Nous pourrions le faire sur la somme des lignes. Ceci ne sert pas à grand chose puisque la
somme de chiffres avec 2 chiffres derrière la virgule maximum donnera toujours au plus 2
chiffres derrière la virgule. Faisons-le pour le plaisir.

La cellule E22 contient =SOMME(E2:E21)

et la fonction arrondi est de type =ARRONDI(nombre à arrondir;nombre de chiffres)

Dans notre cas, nous souhaitons arrondir la somme. La fonction extérieure est dont la fonction
Arrondi qui reprendra comme argument "nombre à arrondir" le résultat de la somme. La
formule devient:

=ARRONDI(SOMME(E2:E21);2)

Nous pourrions faire de même avec la TVA et le montant total (à payer) mais nous pouvons
nous contenter de n'afficher que les 2 derniers chiffres si nous n'utilisons pas ces résultats
pour d'autres calculs.

3.2. La fonction conditionnelle SI

Même si je ne vois pas beaucoup de fonctions, quelques unes s'utilisent régulièrement.

La fonction SI permet d'exécuter une opération si la condition est remplie et une autre si cette
condition n'est pas remplie.

=SI(Condition;opération à effectuer si VRAI; Opération à effectuer si FAUX)

Les conditions possibles sont

Supérieur >
Supérieur ou égal >=
Inférieur <
Inférieur ou égale <=
égal =
différent <>

Les opérantes peuvent être de tous types. Voici quelques exemples: a1<>A2 ;
A12>=20 ; G15 <= 0 mais peuvent également être de type

A2<>"": le contenu de A2 différent de rien. Attention, si le chiffre 0 est contenu dans cette
cellule, le résultat est vrai. Nous allons utiliser cette possibilité dans notre facture dans
quelques secondes.
L'opération Fausse peut être omise, mais le mot FAUX sera affiché. VRAI ou FAUX peuvent
être de tous type mais opèrent sur la cellule où se trouve la fonction. Par exemples:

• 10 affichera le chiffre 10 dans la cellule

• "Facture": affichera le mot facture dans la cellule (remarquez les " ")
• A2+A3

Prenons quelques exemples de cette fonction:

<A3> =SI(A1<>A2;a1+a2;a1*2) si le contenu de A1 est différent de celui d'A2, additionner

les 2 nombres, sinon, multiplier le contenu par 2 (j'en convient, le résultat est le même)

<A1> =si(E32>=0;"Facture";"Note de Crédit") Si le résultat en E32, est supérieur ou égal à 0,

afficher le mot Facture, sinon, affiche Note de crédit

En imbriquant:

=Si(D10<2;"Recommence son année";si(d10<5;"Deuxième

session";si(d10<7;"Réussite";"Félicitations")))

Si la moyenne de l'étudiant est inférieure à 2, l'élève recommence son année, si

l'étudiant à <5 (mais supérieur ou égal à 2 avec la première condition), en deuxième session,
si l'élève est compris entre 5 et 7 non inclus, réussit et si ses points sont supérieurs ou égal à 7,
réussite avec félicitations.

3.3. Fonctions ET et OU

Ces 2 fonctions donne les valeurs VRAI ou FAUX. C'est justement ce que nous avons
utilisé dans la fonction SI.

ET(condition1;condition2;...) donne VRAI si toutes les conditions sont rempliées

OU(condition1;condition2;...) donne VRAI si au moins une condition est remplie

3.4. La fonction maintenant(), aujourd'hui()

La fonction permet d'afficher la date du jour (celle de l'ordinateur) avec le jour, mois,
année, heure, minute. Elle nécessite néanmoins de modifier le format de la cellule en format
date, ce que nous verrons au chapitre suivant. Par contre, =aujourd'hui() affiche directement la
date (sans les heures).

3.5. Modification de notre facture EXCELL

Reprenons notre facture modifiée avec les fonctions arrondi() ci-dessus. Le premier
problème est l'affichage des 0 dans la dernière colonne même si la ligne est vide. Nous allons
corriger ce problème. Commençons par le plus simple. Nous souhaitons afficher les 0 dans la
dernière colonne uniquement si une condition est renseignée. La formule actuelle en E2 est
=ARRONDI(A2*C2*(1-D2/100);2). La condition pour effectuer ce résultat serais que la
colonne 1 (quantités de la ligne correspondante) soit vide. La condition devient:
=SI(A2<>"";calcul;"")

soit en imbriquant les 2 formules: =SI(A2<>"";ARRONDI(A2*C2*(1-D2/100);2);"")

Remplaçons le contenu de la cellule E2 par cette formule et recopions jusqu'à la ligne

20 incluse et les lignes où il n'y a pas de quantités n'affichent plus de zéro.

Nous pourrions encore modifier la fonction si pour que le calcul ne se fasse que si la
colonne quantité et la colonne prix soient différents de rien

=SI(ET(A2<>"";C2<>"");ARRONDI(A2*C2*(1-D2/100);2);"")

A retenir: dans la fonction SI, "" permet de faire une condition sur une case vide ou de
n'afficher aucun caractère comme résultat.

Notre facture EXCEL est déjà bien en route, il nous faut maintenant la personnalisée
pour notre propre entreprise. Ce sera le chapitre suivant.

La facture que nous utilisons ne peut pas encore être utilisée, la mise en page est plutôt
sommaire. Il nous manque les en-têtes, les cases pour délimiter les cellules, les dates, ... Ce
chapitre va nous permettre de mettre en forme. Le suivant sera l'impression.
4.1 Format des cellules

Même si nous le verrons par la suite avec la barre des tâches, l'ensemble des mises en
forme de cellule se trouve dans la commande CELLULE du menu FORMAT

Nombre
reprend le
format du
contenu des
cellules
sélectionnées
Alignement
reprend le
format
"paragraphe"
des cellules
Police
reprend la
police,
couleurs, ...
du contenu
de la cellule
Bordure
reprend les
encadrements
des cellules
sélectionnées
Motifs
reprend les
fond des
cellules
Protection
permet
d'empêcher
la
modification
de cellules

4.2. Le format Nombre

Plusieurs possibilités existent pour la manière qu'affiche le contenu d'une cellule.

STANDARD est le format standard des cellules d'EXCEL. Le texte est aligné à gauche, les
nombres alignés à droite/

NOMBRE permet de modifier la présentation des nombres (texte est néanmoins accepté).
Vous pouvez "formater" par ici les nombres négatifs et insérer un espace automatiquement
entre les centaines et les milliers.
MONETAIRE permet d'insérer le sigle € automatiquement après les chiffres. Pour que le
symbole € soit utilisé par défaut, vous devez modifier le format monétaire dans les paramètres
régionaux du panneau de configuration de Windows.

COMPTABILITE est identique au format monétaire, seul le sigle - éventuel est mis à
gauche.

DATE permet des formats de jour.

A retenir: Lorsque vous tapez une date dans une cellule, EXCEL intègre
automatiquement le format date par défaut dans la cellule. Pour utiliser ensuite cette cellule
avec un autre format, il faut remodifier le format

HEURE permet des mises en formes d'heures, minutes et secondes.

POURCENTAGE insère un format de pourcentage (/100) pour des nombres.

A retenir: Lorsque vous utiliser un format de pourcentage, les calculs à partir de cette
cellule utilisent également le pourcentage. Il faut donc modifier les formules en
conséquence

TEXTE modifie le contenu de la cellule pour que le contenu soit vu uniquement comme du
texte, même si c'est un nombre.

4.3. Alignement

L'alignement permet de modifier le sens du texte dans une cellule. Revers de la

médaille, toute la hauteur de ligne est modifiée en conséquence.
 La fonction "Fusionner des cellules"
permet de rassembler plusieurs cellules
en une seule. Cette fonction est
accessible par la barre d'outils. Par
contre, pour défusionner, il faut
repasser dans le menu (sauf Excel XP
et 2003).

L'option Ajuster va, dans le cas de textes, va diminuer la taille des caractères pour rentrer
toute la phrase dans la cellule. Elle n'a aucune influence sur les chiffres.

4.4. Police
Rien de bien
spécial pour
cette
commande, à
part des
soulignements
doubles, ... La
majorité des
commandes
est accessible
via la barre
d'outils.

Remarque,
vous ne
pouvez pas
utiliser 2
polices
différentes
dans la même
cellule.

4.5. Bordures
1 permet de
créer les
bordures
internes à la
plage de
cellules
sélectionnées
ou externe.
2 permet de
sélectionner
le type de
bordure (y
compris les
 épaisseurs)
3. permet
d'insérer des
bordures de
différentes
manières

Vous devez à chaque fois sélectionner en premier la taille, suivie de la couleur et seulement
taper le contour ou intérieur ou directement un des bord.

4.6. Motifs
Sélection de
la couleur de
fond de la
plage de
cellules
sélectionnées
ou même
motifs
suivant les
couleurs en
dessous.
Pour une
impression
lisible,
seules
quelques
couleurs
claires
peuvent être
utilisées si la
cellule
contient du
texte.

4.7. Protection

La protection des cellules est un peu spéciale. Elle s'utilise avec la fonction protection
du menu OUTILS. Lorsque vous protégez la feuille, toutes les cellules dont la case verrouillée
est cochée ne pourront être modifiées. Par contre, si une cellule verrouillée contient une
formule, le résultat sera quand même affiché si vous modifiez les cellules de départ de cette
formule. Nous utiliserons cette fonction pour protéger les formules dans l'exercice ci-dessous
4.8. Insertion d'images, logos, ...

Dans Word, l'insertion d'images pose de multiples problèmes de mise en page. Par
contre, dans EXCEL, l'image se place au-dessus des cellules. La commande se trouve dans le
menu INSERTION.

A retenir: l'insertion d'une image sous EXCEL peut également se faire par le presse-
papier de Windows: fonctions copier - coller. Le résultat est le même qu'en utilisant la
fonction INSERTION > Images du menu

4.9. Mise en page de notre facture.

Reprenons notre facture. Commençons par mettre une bordure autour et à l'intérieur en
sélectionnant la zone de lignes. Nous ferons ensuite une zone pour les totaux.
Pour pouvoir insérer le logo de l'entreprise, son adresse, les coordonnées du client, numéro et
dates de factures, nous devons insérer des lignes au-dessus. Sélectionnons la ligne 1 et avec la
touche droite de la souris (menu contextuel), utilisons la commande INSERER. Cette
commande doit être répétée 7 - 8 fois ou plus si nécessaire.

Insérons le logo comme précédemment et dans les cellules en dessous les coordonnées
de notre entreprise.

Pour le numéro de facture, nous utiliserons 2 cellules, 1 pour le texte (N° facture) et
une pour le numéro à insérer manuellement (à ce stade). D'autres indications peuvent être
rajoutées.

Pour la date de la facture, nous utiliserons la fonction =maintenant() ou la fonction

=Aujourdhui(). Ces 2 fonctions dates sont équivalentes, sauf que maintenant() donne le
format en jour/mois/année/date/heure, tandis que aujourdhui() ne donne que jour/mois/date.
L'utilisation de cette fonction passe par le format > Nombre > Date de la cellule.

Il ne reste plus qu'à délimiter l'emplacement pour l'adresse du client.

 La dernière opération consiste à protéger les formules. Nous allons donc décocher la
case Verrouillée" pour toutes les cellules que nous pourrons modifier avant de protéger la
feuille.

Il ne nous restera plus qu'à utiliser la commande Protection -> Protéger la feuille du
menu outils.

Remarque: les cellules protégées contenant des formules sont mises à jour mais vous ne
pouvez pas modifier le contenu.

V. Tutorial: Imprimer sous EXCEL

 Dans un logiciel bureautique courant, les impressions ne posent pas de problèmes
particuliers. Par contre, dans Excel, l'impression doit être préprogrammée: logique puisqu'une
feuille EXCEL peut prendre plusieurs pages. Ceci implique également qu'au stade de notre
facture, l'impression ne donne pas une mise en page correcte: décalage vers la gauche, tableau
trop haut, ...

Le plus souvent, l'utilisateur augmente la hauteur des lignes et la largeur des colonnes
ou même d'insérer plus de lignes dans la facture. Les possibilités d'impressions d'Excel vont
nous faciliter la vie même si nous utiliserons également ces possibilités.

Les commandes d'impressions sont toutes reprises dans le menu FICHIER (à part une
qui ne sera pas vue dans ce tutorial EXCEL).
5.1. La commande Mise en page

Mode portrait ou paysage est connu. Par contre, première commande spécifique,
l'échelle. Cette commande permet de réduire (ou d'agrandir) l'impression sur 1 (ou plus)
page(s) en largeur et en hauteur. Une petite remarque: si votre tableau fait au départ 5 pages
en hauteur, le réduire à une page en hauteur réduite également la hauteur des textes par 5. Le
texte devient donc illisible.

L'utilisation des marges est identique à n'importe quel programme sous Windows.
Pour rappel, les marges ne peuvent pas être inférieures à une dimension spécifique à
l'imprimante (typiquement 1 cm) sous peine de voir une partie du texte non imprimée.

A retenir: La commande de mise en page est valable pour toutes les feuilles jusqu'à la
version 2000 incluse. Dans EXCEL XP et 2003, seulement pour la feuille en cours.

5.2. Zone d'impression.

Sélectionnez une
zone de cellule et
dans le menu
Fichier, utilisez la
commande "Zone
d'impression" ->
Définir.

A partir de
maintenant, seule
la zone
sélectionnée sera
imprimée (sur cette
 feuille). Vous
pouvez annuler
cette zone
d'impression. Ceci
permet par
exemple de ne pas
imprimer des
calculs annexes à
coté de votre
facture.
A retenir: si vous
définissez une
zone d'impression,
lors d'une
impression de la
feuille, seule cette
zone sera
imprimée. Les
zones d'impression
sont définie (ou
non) pour chaque
feuille du classeur.

5.3 Aperçu avant impression

Cette commande permet d'afficher sur votre écran la (les) feuille(s) comme elle sera
effectivement imprimée.

5.4. Imprimer
 Cette commande est presque identique à la commande d'impression d'autres logiciels
bureautiques. Sauf que dans la partie impression, vous pouvez soit:

• si vous avez préalablement sélectionner une zone, n'imprimer que la zone sélectionnée.
• Imprimer les feuilles sélectionnées (à notre niveau la feuille en cours) suivant la zone
d'impression si elle est définie - sinon toute la feuille.

5.5. Impression de notre facture Excel

Nous allons terminer ce petit tutorial Excel par la mise en page pour impression de
notre facture. Pour rappel, si vous augmentez la taille d'une ligne (ou d'une colonne), toutes
les cellules de la ligne (ou de la colonne) sont modifiées.

Commençons par augmenter la hauteur de quelques lignes au-dessus du tableau de

calcul. Ceci va nous permettre de descendre le tableau. Pour cela nous devons supprimer la
commande de protection de la feuille dans le menu Outils, nous la remettrons ensuite.

En insérant une ligne entre le tableau de calcul et l'en-tête de facture (+ refaire les
bordures) et en sélectionnant la mise en page 1 page en hauteur et une page en largeur,
modifiant le bord gauche à 3 cm, nous obtenons notre facture définitive.
 D'autres commandes (qui seront vues plus tard dans ce tutorial) permettent de
rechercher automatiquement les coordonnées du clients dans une liste, descriptions, prix et
taux de TVA pour chaque produit et même d'obtenir les base de TVA suivant les taux de
TVA de chaque produits. Pour une utilisation de ce type de fonctionnalité, l'utilisation d'une
gestion commerciale est préférable.

Vous pouvez charger notre facture terminée ici, prête à imprimer, même si nous
améliorerons le calcul TVA plus tard. N'oubliez pas d'ôter la protection de la feuille Excel
pour la modifier à votre convenance (logo et coordonnées), conditions générales de vente en
bas de la facture, ...

6. Formation: Graphique
Excel
La création d'un graphique Excel permet une
visualisation de l'évolution de chiffre (vous vous en
doutiez) pour statistiques. Les nombre de graphiques
possibles est important, bien que seulement quelques
uns soient réellement utilisables en pratique.
Commencez par remplir sous Excel un tableau avec
l'exemple ci-dessous:

Pour rappel, la somme sur la dernière ligne se fait à l'aide d'une addition par la
fonction dans les barres d'outils. Avant de commencer, remarquez que la case (cellule) en
haut à gauche est restée vide. Ceci est obligatoire si vous entrez des chiffres (et non des textes)
comme en-tête de lignes et de colonnes.

Sélectionnez l'ensemble du tableau avec la souris et sélectionnez la commande

graphique dans le menu Insertion. La fenêtre suivante apparaît.

Cette fenêtre reprend les types de graphiques possibles: à gauche les catégories et chaque fois
à droite les possibilités. Dans notre cas d'histogrammes, chaque ligne (sauf la dernière)
reprend 3 possibilités: chaque ligne à côté l'une de l'autre, chaque lignes une au-dessus de
l'autre et en dernier, par pourcentages du total. Cliquez sur suivant pour continuer

Excel propose alors d'organiser les données suivant les lignes ou les colonnes. Dans
le cas ci-dessus, l'organisation se fait par colonnes. Chaque groupe est repris ensemble avec
les mois déterminés par les couleurs des barres. En sélectionnant ligne, les couleurs des barres
seraient "consommables, cartes, …". Si vous souhaitez sélectionner une autre zone de
données, vous pouvez également la changer ici. Par l'onglet "Série", vous pouvez également
donner d'autres noms aux séries (à condition quelles soient sur la feuille de calcul. Cliquez sur
Suivant.
 La fenêtre suivante offre de multiples possibilités:

• titre permet d'insérer des titres sur le graphique et les axes. Par exemple: type de vente
en "axe des X".
• Axe est peu utilisé
• Quadrillage permet d'afficher plus de quadrillage.
• Légende est lié aux carré reprenant Janvier, …
• Etiquettes de données permet d'afficher les montants au-dessus de chaque barre du
graphique. Cette option n'est généralement pas à utiliser.
• Table de données permet d'afficher les données comme sur notre feuille de donnée.

Bref, ces options ne sont pas souvent utilisées parce qu'elles encombrent les
graphiques Excel. Elle réduisent la taille disponible dans la fenêtre réservée au graphique. En
cliquant sur suivant, Excel propose de l'insérer sur cette feuille ou sur une nouvelle feuille.

Notre graphique terminé (sur la même feuille), intéressons-nous

maintenant aux diverses possibilités de ces graphiques.

Commençons par modifier des valeurs dans notre table de données.

En appuyant sur la touche <Entrée>, le graphique est automatiquement mis
à jour.

Cliquez sur le graphique avec la touche droite de la souris et vous

obtenez le menu contextuel ci-dessus. Sinon, cliquez en dehors de la zone
(pour le désélectionner) et recliquez avec la touche droite de la souris.

Format de la zone graphique permet de modifier la couleur de fond, les couleurs et types de
bordures, …

Type de graphique permet de revenir à un autre type de graphique Excel. Prenons par
exemple ci-dessus.

Ce type de graphique est le plus couramment utilisé. Dans ce cas, vous pouvez
même modifier les valeurs de la table en modifiant des données du graphique (sur les points
représentant les valeurs. En cliquant 2 fois sur une des lignes, vous pouvez modifier la couleur
d'une série. C'était également possible avec les barre graphe ci-dessus.

Données sources permet de resélectionner les cellules.

Options du graphique reprend les titres, axes, quadrillage, …

Emplacement permet d'insérer le graphique sur une nouvelle feuille.

Les autres commandes sont soit inutiles (ou presque), soient déjà utilisées.

Si vous utilisez un graphique, vous verrez que chaque fois que vous sélectionnez
quelque chose et cliquez 2 fois dessus, vous pouvez modifier la présentation de chaque parties.
Ceci dépasse le cadre de cette info, essayez.
 Une dernière chose. Nous avons vus que si vous modifiez les données de la table, le
graphique est modifié. Ceci est parfois gênant pour garder un graphique à un moment donné.
Une solution est de créer une copie de la feuille. Cliquez sur feuille 1 avec la touche droite de
la souris et sélectionnez la commande "Déplacer ou copier". Cochez la case "Créer une copie".
Votre feuille ne bougera plus tant que vous ne modifierez pas les données sur la copie.

Si vous désirez insérer votre graphique sur Word. Sélectionnez le graphique et

utilisez la fonction "copier" sous Excel et "Coller" sous Word. Les graphiques se comportent
comme des objets. Cliquez 1 fois pour le déplacer et 2 fois pour le modifier. En supprimant la
liaison (menu Edition, aucune modifications des chiffres dans Excel ne modifiera votre graph.
sous Word).

7. EXCEL: la fonction somme si

Reprenons notre modèle de facture. Quoique intéressante pour la majorité des
entreprises, certains utilisent des taux de TVA différents d'un produits à l'autre. Cette partie va
nous permettre de dissocier les taux de TVA par ligne et de recalculer les bases et TVA
suivant les codes repris sur chaque ligne. La fonction Excel utilisée est la fonction
conditionnelle [Link]()

7.1. Création des codes TVA

Sur notre facture, supprimons la protection (Menu outils -> Protection) et insérons une
colonne entre "réduction" et "total"
 Ceci va nous obliger à reprendre la mise en forme des colonnes supérieures, et même
de modifier la mise en page de notre facture éventuellement. Comme en-tête de colonne dans
la partie insérée, tapons "Taux TVA". Les lignes en-dessous reprendrons les taux de TVA de
chaque produits.

Dans la partie en-dessous (pied de facture), tapons: base 0%, base 6%, base 12% et
base 21%. Les utilisateurs adapteront pour les taux en France et au Luxembourg. La colonne
adjacente nous permettra de mettre la formule Excel adéquate.

7.2. La fonction [Link]()

La fonction Excel [Link] effectue une somme en vérifiant un critère sur la ligne
équivalente.

=[Link](plage de tests;test à effectuer;éléments à additionner).

En plus claire, si la cellule dans la plage de tests est égale au test à effectuer, l'élément
en regard dans la plage à additionner sera évaluée.
 Après avoir positionner le curseur en regard de la case "Base 0%", insérons la fonction
[Link] par la commande Menu Insertion ->Fonctions

Comme plage, sélectionnons la zone E14 à E23 (la zone où nous rentrons les taux de
TVA), comme critère 0 (puisque nous gérons dans cette case la base 0 %) et comme somme
plage la zone F14;F33 (la zone de total par ligne). Reprenons cette fonction chaque fois en
regard des cellules bases de TVA en changeant uniquement le taux de TVA comme critère.
Par exemple, pour le taux à 6 %, la formule en B36 devient:
=[Link](E14:E33;6;F14:F33); Et voici le tour est joué.
 Il ne nous reste plus qu'à modifier la formule de
TVA:=B35*0+B36*0,06+b37*0,12+B38*0,21 Et voilà, la facture tient maintenant compte
des différents taux de TVA. Il ne nous reste plus qu'à supprimer les chiffres d'essais et de
nouveau à protéger notre facture pour les modifications. Voici notre nouvelle facture.

8. EXCEL: Feuille et classeur

Lorsque vous démarrez un nouveau classeur, EXCEL crée automatiquement un certain
nombre de feuilles variables suivant les options du menu outils. Chaque feuille Excel est
individuelle. Néanmoins, vous pouvez transférer des données d'une feuille à l'autre. Comme
applications, citons un devis reprenant plusieurs corps de métier et une page récapitulative
pour le devis, ou même une feuille récapitulative de nos différentes factures.

De même, Excel offre la possibilité de récupérer des données d'un classeur à l'autre.
Pour rappel, un classeur Excel est la dénomination du fichier Excel en lui-même. Chaque
classeur peut inclure plusieurs feuilles.

8.2. Feuille Excel

La gestions des feuilles Excel est reprises dans différents menus. Pour la facilité, nous
allons utiliser le menu contextuel. Je reprend 2 versions de la gestion des feuilles puisque de
grosses modifications sont intervenues entre Excel 2000 et versions inférieures et Excel XP et
2003 d'autre part.

Pour passer d'une feuille à l'autre, cliquez simplement sur la feuille.

Menu contextuel des feuilles EXCEL 2000 et Menu contextuel des feuilles Excel version
inférieurs XP, 2003 et 2007

Cliquez avec la touche droite de la souris sur une des feuilles en bas de l'écran. Un
menu contextuel spécifique est affiché.

8.2.1. Insérer une feuille

Cette commande permet d'insérer une nouvelle feuille, à gauche de la feuille

sélectionnée. D'autres possibilités sont également proposées, mais dans ce cours, nous nous
limiterons aux feuilles. Excel incrémente automatiquement le nom de la feuille en feuil4, ...

8.2.2. Supprimer une feuille

Cette commande ne pose pas de problème particulier, elle supprime la feuille en cours:
son contenu est définitivement supprimé.
8.2.3. Renommer.

Pour la gestion de votre classeur, un nom distinct

pour chaque feuille est préférable. Cette fonction
permet de donner un nouveau nom pour la feuille
sélectionnée. Le nom doit être unique pour le classeur.

Une autre possibilité est de double cliquer sur

le nom de la feuille.

8.2.4. Déplacer ou copier une feuille

Même si la commande du menu contextuel peut être utilisée, il est plus facile de
sélectionner la feuille et de la déplacer de à gauche ou à droite en maintenant la souris
enfoncée. Un petit triangle permet de déterminer l'emplacement futur de la feuille.

L'autre possibilité (copier) est nettement plus intéressante puisque non seulement elle
copie la feuille sélectionnée (cochez la petite croix), mais elle garde les données, formules et
formats de celle de départ. Bref, la feuille copiée est identique à celle de départ. Il ne reste
plus qu'à renommer et déplacer la nouvelle feuille avant de supprimer les données pour
reprendre uniquement les formules par exemple.

Cette fonction Excel permet également de transférer ou copier une feuille Excel d'un
classeur à l'autre.

8.2.5. Sélectionnez les feuilles.

Excel offre la possibilité de sélectionner toutes les feuilles, commandes du menu

contextuel, ou même certaines feuilles. Cette possibilité est utilisée dans 2 cas distincts.

La première est liée aux impressions. Vous pouvez de cette manière n'imprimer que
certaines feuilles
 La deuxième possibilité permet de remplir une cellule (données, formules, format de
cellule) sur une feuille tout en modifiant toutes les feuilles sélectionnées. Cette fonctionnalité
est à utiliser avec précaution, puisque aucun message ne signale si des données sur les autres
feuilles sélectionnées sont écrasées.

Pour sélectionner toutes les feuilles, utilisez la commande du menu contextuel. Par
contre, pour ne sélectionner que quelques feuilles, sélectionnez les feuilles souhaitées en
maintenant la touche <CTRL> enfoncée.

Une large différence entre les classeurs 2000 et versions inférieures et les classeurs
Excell XP et 2003. En Excel 2000, le nom du classeur actif est en gras, les autres feuilles
sélectionnées en fond blanc, pas en version XP et 2003.

Pour désélectionner les feuilles, vous pouvez utiliser le menu contextuel, ou

simplement cliquer sur une autre feuille.

8.2.6. Déplacement entre les feuilles

Si vous n'avez que quelques feuilles, vous pouvez directement cliquer

sur la feuille en bas. Par contre, avec plus de feuilles Excel, vous devez
d'abord vous déplacer pour visualiser la feuille souhaitée, ensuite cliquer
sur la feuille.
8.2.7 Couleur d'onglet.

Cette fonction existe depuis la version XP.

Elle n'est pas présente dans les versions
inférieures d'Excel. Elle permet de sélectionner
une couleur de fond pour l'onglet de chaque
feuille.

La nom de la feuille utilisée est soulignée

dans le couleur choisie (dans l'exemple ci
contre, en rouge), les onglets des feuilles non
sélectionnées sont remplis par la couleur
choisie.

Si vous sélectionnez plusieurs cellules en

même temps (sélection avec la touche control
enfoncée), c'est la seule méthode de visualiser
les feuilles sélectionnées en Excel XP et 2003.
Dans les versions inférieures, les noms des
feuilles sélectionnées sont en gras.

8.3. Formules entre feuilles

La méthode la plus simple pour reprendre des valeurs d'une feuille à l'autre est
d'utiliser la simple formule =ref-cellule.

Pour assurer la liaison, positionnons le curseur sur la feuille et la cellule où nous

souhaitons insérer le résultat et tapons =. Cliquons ensuite en bas sur la feuille ou se trouve la
donnée de départ et directement sur la cellule où se trouve ce chiffre. Excel modifie la
référence de la cellule par feuil1!A3 dans notre cas. Il nous suffit de terminer notre fonction
en appuyant su la touche <entrée>

Si vous renommer la feuille, les formules sont automatiquement modifiées.

Pour une formule reprenant des données d'une autre feuille, la

technique est identique. Reprenons notre feuille 2 et utilisons par
exemple la formule =somme(). Passons à la feuille 1 par la méthode
précédente et sélectionnons les chiffres souhaités.

Excel modifie automatiquement la r

cellules par Feuil1!A1:A2
 8.4. Données entre
classeurs
Excel permet également de reprendre des données d'un classeur (fichier Excel) à
l'autre. Créons un nouveau fichier Excel. Dans une cellule de ce nouveau classeur, tapons = et
dans la barre d'outils Windows, sélectionnons le classeur précédant

Dans le classeur, sélectionnons la feuille, suivi de la cellule souhaitée.

Excel reprend automatiquement le nom du classeur, suivi du nom de la feuille et de la

référence de la cellule. L'utilisation de formules est identique. Si nous enregistrons notre
classeur1, la référence de la cellule est automatiquement modifiée. Si nous fermons ce
classeur, le nom du classeur est remplacé par l'emplacement sur le disque dur et le nom du
classeur.

Attention à ne pas déplacer le fichier sur le disque dur.

8.5. Mise à jour de la liaison à l'ouverture

Si vous fermez les 2 classeurs et ré ouvrez le classeur contenant la formule, la fenêtre

suivante apparaît permettant de mettre à jour la liaison. En effet, le fichier contenant les
données de base à pu être modifié directement.

1. Mettre à jour la liaison: permet de vérifier si les données du classeur de départ n'ont
pas été modifiées et, le cas échéant, de récupérer les nouvelles valeurs de la liaison.
2. Ne pas mettre à jour: garde les anciennes valeurs. Ceci ne coupe néanmoins pas la
liaison entre les classeurs.

Cette fonction n'oblige pas à ouvrir le classeur qui contient les données de départ.
Excel va automatiquement les récupérer sur le disque dur.
 8.6. La commande Liaison.

Cette commande n'est accessible dans le menu édition que si vous avez effectivement
créer une liaison vers un autre classeur dans le classeur Excel en cours.

La commande liaison du menu

Edition.

La liaison va permettre différentes manipulations sur la liaison en cours.

1. Mettre à jour les valeurs permet de recréer une liaison, notamment si vous n'avez pas
mis à jour lors de l'ouverture du document. De toute façon, si les 2 classeurs sont
ouverts sur le même ordinateur, la modification est automatiquement répertoriée.
2. Modifier la source permet de sélectionner un autre classeur pour la source de donnée.
Ceci est utile en cas de déplacement des classeurs. Cette option ne permet pas de
sélectionner la feuille. Si le nom de la feuille est faux, vous devez modifier
manuellement la formule dans la feuille de départ.
3. Ouvrir la source permet d'ouvrir le classeur qui contient les données de départ.
4. Rompre la liaison supprime le lien entre les 2 classeurs. Les formules sont
automatiquement remplacées par les valeurs des cellules du classeur de départ.
5. Vérifier l'état: vérifie si la liaison est toujours présente.
6. Invite de démarrage: permet de spécifier quelques options sur l'ouverture des
liaisons:

1. Laisser les utilisateurs choisir d'afficher ou non l'alerte permet d'afficher la fenêtre au
démarrage du classeur en cas de liaisons.
 2. Ne pas afficher l'alerte et ne pas mettre à jour les liens
automatiques ne pose aucune question, ni ne met à
jour les données. La touche F9 permet de recréer la
mise à jour.
3. Ne pas afficher l'alerte et mettre à jour les liens ne pose pas de questions, met
automatiquement la liaison à jour.

9. Cours EXCEL: Tri et filtre

Même si Excel n'a pas les mêmes possibilités que la base de donnée Access pour trier
et filtrer les enregistrements d'une liste, les fonctions implantées suffiront à la majorité des
utilisateurs. Cette fonctionnalité va nous permettre plus tard de reprendre nos clients et
produits directement dans notre facture par un simple code.

9.1. Création d'une nouvelle feuille

Lorsque vous créez un classeur Excel, 3 feuilles sont automatiquement créées (ceci est
modifiable dans les options). Pour passer d'une feuille à l'autre, cliquez simplement sur la
feuille en bas de votre écran.

Si votre classeur Excel ne comporte qu'une feuille, cliquez avec la touche droite de votre
souris sur la feuille et sélectionnez la commande insérer. Une nouvelle feuille est ainsi créée.
Elle est indépendant de la feuille précédente, même si nous pouvons faire passer des données
d'une à l'autre.

Le transfert des données entre les feuilles et classeurs, manipulations, ... a été vu
complètement dans le chapitre: gestion des feuilles et classeurs

9.2. Création d'une liste

Commençons par créer notre liste de clients sous Excel. Cette liste d'adresse doit
reprendre les coordonnées complètes du client. Un code client nous permettra de le relier à
notre facture excel dans un exercice futur. Comme nous utiliserons également cette partie en
combinaison avec Word pour un publipostage, Outlook pour récupérer comme carnet
d'adresse et même Access, quelques champs (colonnes) seront également rajoutés à titre
indicatif

La structure de notre liste va reprendre:

• Code client: nécessaire pour lier la liste avec la facture excel

• Entreprise
• Type: Monsieur, Madame, ... nécessaire en cas de publipostage
• Nom
• Prénom
• Adresse: rue, numéro de maison
• Code Postal: pour publipostage, mais va également délimiter les informations
• Ville
• Pays
• Code TVA
 • Téléphone: donné à titre indicatif
• Fax: donné à titre indicatif
• Adresse mail

Nous allons rentrer quelques adresses de clients en

respectant à chaque fois les bonnes colonnes. Le code doit être
unique pour chaque client. Toutes les données ne doivent pas
forcément être rentrées. Par exemple, une société ne comportera pas le titre, nom, prénom de
la personne et inversement, une personne n'aura pas de nom d'entreprise.

Une liste similaire pourrait également servir pour un calcul d'inventaire.

9.3. Tri des clients

Dans le menu Données, sélectionnons la commande TRIER.

Le tri peut se faire suivant 3 critères, laissés au libre choix. La première question est
Ligne de Titre (oui ou non). Cette notion est importante puisque q'en cas de ligne de titre, la
première ligne ne sera pas trier. Généralement, Excel le propose par défaut, mais mieux vaut
vérifier le cas échéant. Au contraire, si vous n'avez pas créé d'en-tête, vous devez cocher la
case Non.

Le premier tri va servir servir de tri de départ. Les 2 suivants sont facultatifs. Dans notre
exemple, le tri se fera par le code de l'entreprise. Dans le cas où 2 sociétés utiliseraient le
même code, suivant le nom de l'entreprise, suivi du nom, ....

Le bouton option permet dans certains cas de trier suivant les jours ou les mois. Dans ce
cas, une colonne n'utilisant pas ces listes n'en tiendra pas compte.

Il ne nous reste plus qu'à accepter le tri.

9.4. Problèmes de tris.

Dans certains cas, les tris en excel peuvent poser quelques problèmes. Insérions une
colonne vide au milieu de notre liste et exécutons un tri. Vous remarquez qu'Excel ne tient pas
compte des colonnes après la colonne vide pour effectuer le tri. Il faudra sélectionner
manuellement l'ensemble des colonnes pour que le tri tienne compte des colonnes
supplémentaires.

Supprimer cette colonne vide pour que tout rentre dans l'ordre.

9.5. Etendre la sélection.

Si vous sélectionnez une colonne, Excel propose d'étendre la sélection. Cette possibilité
permet de sélectionner toutes les colonnes pour le tri. Dans le cas contraire, seule la colonne
sélectionnée sera utilisée. Les ;données par lignes ne seront donc plus en rapport. Sauf par la
commande annuler, cette opération sera irréversible.
9.6. Filtre automatique.

Le filtrage des enregistrements va nous permettre de n'afficher que quelques lignes de

notre liste de client. Dans le menu Données, sélectionnons la commande "Filtre", suivi de
l'option "Filtre automatique".

Cette commande va modifier la présentation de notre feuille excel

Pour chaque colonne de notre liste, nous pouvons maintenant sélectionner tous, soit les
10 premiers, soit 1 valeur.

Dans le cas d'un filtre Excel personnalisé, nous allons pouvoir utiliser des critères plus
complexes sur la valeur de la cellule. Vous pouvez même utiliser 2 critères différents en
utilisant les commandes ET (toutes les conditions doivent être remplies) ou OU (au moins 1
des critère doit être rempli).
 Si un filtrage est effectué sur une colonne, la flèche est mise en bleu. Les filtres sont
cumulatifs. Pour supprimer le filtrage, utilisez de nouveau la commande filtre automatique du
menu Données. La commande Filtre: Afficher tout du menu permet de supprimer les filtres
effectués, mais garde la possibilité de filtre automatique. Par cette fonctionnalité, vous pouvez
également trier les enregistrements. Le filtre Excel n'affiche que les enregistrements filtrés
mais trie l'ensemble de la plage de donnée.

9.7. Astuce: conserver les données triées.

Est-il possible de conserver des données triées pour un usage ultérieur? Oui, le truc est de
filtrer selon les critères souhaités, de sélectionner la plage et de coller le résultat sur une autre
feuille.

10. Références relatives et absolues, copie

et déplacement
10.1. Introduction

Ce chapitre du tutorial sur le logiciel Excel va reprendre différentes notions difficiles à

caser dans des chapitres distincts. Il est néanmoins important dans toute formation Excel.
Nous allons commencer par les références absolues et relatives des cellules pour continuer
avec les différences fonctions copier - coller spécifiques à Excel.

10.2. Référence absolue - relative

Nous avons recopié des contenus de cellules dès le premier chapitre de notre tutorial
Excel. Automatiquement, Excel met les références des cellules dans les formules à jour. Cette
méthode de travail est appelé "référence relative". Malheureusement, cette méthode de
travail pose quelques fois différents problèmes.

Prenons un exemple, nous plaçons une somme d'argent sur un livret dans la ferme
intention de ne retirer l'argent que dans quelques années et souhaitons connaître le montant
que nous pourrons retirer au bout de ce laps de temps (capital et intérêts cumulés).

Tapons les valeurs suivantes dans notre feuille Excel.

 Pour connaître le montant disponible à la fin de la deuxième année, nous devons rentrer
la formule suivante en B3: = capital + intérêt, soit =B2+B2*C2. Le résultat nous donne
effectivement 102 €. Pour la deuxième année, nous allons recopier la formule vers le bas.

Le résultat est faux, tout simplement parce que Excel a adapté la formule de calcul
d'intérêt vers le bas. Par exemple, la formule en B3 (soit la deuxième année) est devenue
=B3+B3*C3. La formule reprend bien le capital de l'année précédente, mais le taux d'intérêt
est maintenant repris en C3, soit 0. Même si une solution serait de recopier le taux d'intérêt
vers le bas, nous allons utiliser une référence absolue dans notre fonction Excel en B3.
L'utilisation de référence absolue ne modifie pas la référence de la cellule lorsque nous
recopions la fonction.

Pour signaler au tableur Excel que nous souhaitons des valeurs absolues au lieu de
référence relatives, nous allons utiliser le caractère $ devant la référence de la cellule dans la
fonction

$ devant la lettre de la cellule (colonne) ne modifie pas la référence de la colonne si vous

recopiez la fonction d'une colonne à l'autre.

$ devant le numéro de la ligne ne modifie pas le numéro de la ligne lorsque vous recopiez la
fonction vers le haut ou vers le bas.

$ devant la lettre de la colonne et le numéro de la ligne ne modifie jamais la référence de la

cellule quel que soit le sens pour recopier la fonction.

Dans notre cas, nous descendons chaque fois la référence de la cellule contenant le taux
d'intérêt d'une ligne. La solution est donc de modifier la fonction en B3 qui devient
=B2+B2*C$2
 Nous verrons de nombreux exemples de cette fonctionnalité par la suite.

10.3. Modification de fonctions existantes en absolu.

Dans de nombreux cas, nous devons modifier une fonction existante pour la faire passer
de relatif à absolu. La solution est d'utiliser la barre de formule d'Excel pour modifier
directement la fonction.

Positionnez votre curseur directement dans la barre de formule sur la référence de

cellule à modifier et appuyez sur la touche F4. Excel modifie automatiquement la référence
de la cellule de relatif en absolu.

10.4. Copier - coller

Vous connaissez tous les fonctionnalités du presse papier. Cette fonction implantée
directement dans Windows permet de copier une sélection vers le presse papier et de le copier
à un autre endroit (y compris entre 2 logiciels différents). Simple dans la majorité des cas, le
tableur de Microsoft gère le copier-coller d'une manière différente. La version Excel 2003 a
encore compliqué son utilisation par un presse papier office qui accepte jusque 24 niveaux
contre 1 pour le presse papier standard. Il fait partie du volet office (CTRL + F1 pour rappel).

Commençons justement par ce presse papier office. Si vous copier une cellule, le presse
papier office d'Excel 2003 insère automatiquement le contenu. Petite nuance néanmoins, si la
cellule de départ contient une formule, Excl ne copie que le résultat (pas la formule).

Par contre, le presse papier standard copie lui la formule. Copions une des cellules
contenant une formule ci-dessus:

Plaçons le curseur dans une autre cellule et utilisons la fonction coller. De nouveau,
Excel travaille en référence relative et adapte les références des cellules contenues dans la
fonction. Vous pouvez coller autant de fois que vous voulez dans le classeur excel tant que
vous ne modifiez pas le contenu d'une cellule (quelle qu'elle soit). Pour vous signaler que le
presse papier est actif, la cellule contenant la formule de départ est entourée d'un cadre
pointillé clignotant.
 Néanmoins, nous souhaitons
travailler en référence absolue lors
du copier-coller. Nous pourrions
modifier la formule comme ci-
dessus (en utilisant la touche F4 par
exemple). Une autre méthode existe,
quoiqu'un peu plus compliquée.
Positionnez le curseur dans la cellule de départ et sélectionnez le contenu dans la barre de
formule:

Vous venez de copier non pas le contenu de la cellule, mais la formule contenue dans la
cellule (nuance). Appuyez sur la touche entrée ou <Echap> pour quitter le mode modification.
Positionnez le curseur à l'endroit désiré et utilisez la fonction coller. Le tour est joué, Excel
n'adapte plus la fonction dans la nouvelle cellule.

10.5 Déplacement du contenu d'une cellule.

Pour déplacer le contenu d'une cellule vers une autre, 2 manières sont possibles. La
première va utiliser le presse papier et la fonction COUPER - COLLER. Cette fonction ne
marche qu'une fois. Le simple fait de coller et le presse papier Excel est vidé. Par contre, la
référence des cellules n'est pas modifié cette fois.

L'autre méthode va utilisé le glisser-déplacé (à partir d'Excel 2000). Sélectionnez une

cellule ou un groupe de cellule. Positionnez le curseur en bas de la zone sélectionnée. Une
flèche apparaît, il suffit maintenant de déplacer le curseur de la souris en maintenant le bouton
gauche de la souris enfoncée pour déplacer le contenu sans modifier les références des
cellules déplacées. Cette fonctionnalité est identique au couper - coller.

11. Formation EXCEL: les tables

Les tables permettent d'automatiser certains calculs, elles permettent de comparer par
exemple des prix de revient en fonction d'une ou 2 variables. Elles sont souvent utilisées dans
les fonctions financières ou pour des calculs de rentabilité, notamment en utilisation avec des
graphiques.

11.1. Création d'un tableau

Supposons la fabrication d'un ordinateur. Nous souhaitons calculer le prix de vente. Pour
cela, nous allons créer un tableau reprenant les composants, prix et quantités (les prix sont
fictifs).

La formule dans les cellules D2 à D10 reprend le prix multiplié par la quantité
(ex: D2=B2*C2 ). La formule en D11 reprend la somme des cellules au-dessus soit
=somme(D2:D10). La cellule D12 reprend le prix TVAc. Jusqu'ici, rien de particulier. Nous
souhaitons vérifier les modifications du prix de vente en fonction d'un prix d'un composant
(par exemple du disque dur). Nous pourrions créer plusieurs tableaux identiques en modifiant
chaque fois le prix du disque dur mais les tables excel vont nous permettre d'automatiser cette
fonction.

11.2. Table en colonne

Commençons par rentrer les différents prix en fonction de la quantité

Pour vérifier le prix de vente hTVA de l'ordinateur, recréons la formule de D11 en B14
(=somme(d2:d10). Nous ne pouvons pas la copier par le presse papier puisque qu'elle
s'adapterait. Par contre, nous pourrions la déplacer (en utilisant la double flèche sous la
cellule).

A retenir: Dans une table rentrée en colonne, la formule se place toujours en haut à droite de
la colonne de chiffres à analyser.

En sélectionnant la plage de cellules A14 à B19(où se trouve notre formule et les

différentes valeurs possibles), sélectionnons la commande table du menu données. Excel fait
apparaître la fenêtre suivante:

Comme nos données sont rentrées en colonne, nous devons saisir la valeur qui va être
modifiée (dans notre cas le prix du disque dur) dans la cellule d'entrée en colonne, soit D7.
Excel va automatiquement remplir les cellules C15 à C19 avec le résultat en fonction du prix
effectif du composant.
 Si nous modifions le prix d'un autre composant, la table sera également modifiée.

Nous pouvons également créer plusieurs formules, par exemple TVA comprise. Il suffit de
rentrer chaque fois les formules en haut à droite à la suite les unes des autres et de reprendre la
zone avant de créer notre table.

11.3. Table Excel en ligne

Les tables peuvent également être rentrées en ligne. Dans ce cas, la (les) formule(s) doivent
être rentrées en dessous à gauche comme dans l'exemple ci-dessous.
A retenir: Dans une table rentrée en ligne, la formule se place toujours en bas à gauche de la
ligne de chiffres à analyser.

11.4. Table Excel en ligne et colonne

Excel permet également de créer des tables avec une double entrée (2 valeur modifiées),
mais avec une seule formule. La fonction doit être insérée à l'intersection des données en ligne
et colonne. Prenons comme exemple le tableau reprenant le prix de revient TVA comprise en
fonction du prix des disques durs et de la quantité de main d'oeuvre:

A retenir: Dans une table rentrée en ligne colonne, la formule se place toujours à gauche de
la ligne de chiffres à analyser et en haut de la colonne à analyser.
 Vous pouvez télécharger l'exemple de table Excel ici

11.5. Modification, suppression de table

Pour supprimer ou modifier une table Excel existante, vous devez supprimer l'entièreté
du contenu des cellules créer par la table.

12. Formation EXCEL: Les fonctions

financières
Excel regroupe des fonctions de plusieurs types. Ce chapitre reprend une partie des
fonctions financières: emprunts, taux d'intérêt, amortissement. Sans les voire toutes,
intéressons-nous aux principales.

12.1. Introduction

Avant d'attaquer la création d'une fonction financière sous Excel, une petite introduction
sur les arguments. Les arguments des fonctions sont souvent notées comme suit:

VA: Valeur actuelle, montant de départ

• Elle est égale à 0 pour des économies

• Positive pour un placement
• Négative pour un prêt

Taux: taux d'intérêt.

NPM: nombre total de périodes.

• Si le remboursement est mensuel, le nombre de périodes correspond au nombre de

mois
• Si le remboursement est trimestriel: nombre de trimestre
• Si le remboursement (amortissement) est annuel, nombre d'années

VPM: montant périodique

• du remboursement (prêt)
• du dépôt (économie, livret)
• du retrait (placement)

VC: valeur future. Elle est égale à 0 sur un prêt, valeur résiduelle pour un leasing

Type: date d'échéance des payements (début ou fin de mois). Les emprunts sont
généralement remboursable fin de mois. Certains crédits hypothécaires sont remboursables
début de mois.

Remarque: TAUX, NPM et VPM doivent être calculés sur la même période (mensuel,
trimestriel, annuel).
12.2. Les amortissements

Les types d'amortissements peuvent être:

• amortissement linéaire: le montant de l'amortissement est égale pendant

chaque année de la durée
• amortissement dégressif: le montant varie suivant l'année d'amortissement.
L'amortissement de départ étant plus important que l'amortissement final (fin de vie du
bien d'investissement).
• Amortissement dégressif à taux double: le montant varie suivant l'année
d'amortissement. L'amortissement de départ étant plus important que l'amortissement
final (fin de vie du bien d'investissement). C'est une variante de l'amortissement
dégressif.

12.2.1. Amortissement linéaire

La formule de calcul des amortissement linéaire est: =amorlin(valeur achat;valeur
résiduelle;durée de vie)

Dans quelques cas particuliers, la valeur résiduelle n'est pas égale à ZERO (cas du leasing
par exemple même si le taux est plus souvent calculé en amortissement dégressif dans ce cas).

12.2.2. Amortissement dégressif à taux fixe

Dans les amortissements dégressif, le montant de l'amortissement n'est pas fixe. Le montant
de l'amortissement doit donc se calculer année par année. Attention, la formule Excel
n'accepte pas une valeur résiduelle à ZERO.

Amortissement dégressif à taux fixe: =DB(Valeur achat;valeur résiduelle;durée en

année;année du calcul;mois de la période)

Cette fonction financière appelle plusieurs remarques.

1. Le coût est la valeur d'achat du bien

2. Valeur résiduelle, la valeur en fin d'amortissement du bien. Pour la fonction DB, elle
ne peut être nulle, sinon, le résultat est faux.
3. La durée est exprimée en année
4. La période est l'année de calcul de l'amortissement
5. Le mois est facultatif (sinon, fin d'année). Ceci est nécessaire si l'amortissement de la
première année ne démarre pas le premier janvier.

Pour le calcul avec un mois, il suffit de modifier la dernière variable.

 Par exemple, nous pouvons modifier notre formule pour calculer chaque mois comme
ci-dessus. Pour rappel, les $ devant une référence de colonne ou de ligne permette que lors de
la recopie de cellule, la référence ne soit pas modifié.

Attention, les chiffres mensuels d'amortissent sont faux dès la deuxième années.

12.2.3. Amortissements dégressifs à taux double

Ce type d'amortissement est une variante de l'amortissement dégressif à taux fixe. Le

taux peut-être double ou supérieur. Ce type d'amortissement suivant la formule d'Excel est
automatiquement annuel.

La valeur de l'amortissement de la première année est légèrement supérieure à celle d'un

amortissement dégressif à taux fixe.

Attention, selon la valeur résiduelle, les résultats d'amortissements dégressifs

doubles sont corrects ou faux pour la fonction financière DDB. Les résultats cohérents
sont obtenus avec une valeur résiduelle d'environ 10% de la valeur d'achat.

L'exercice

12.3. Epargne

Prenons maintenant les calculs financiers d'épargne (livret par exemple).

11.3.1. Montant déposé et laissé sur un livret.

Supposons que je dépose 1000 € sur un livret pendant 3 ans. Le calcul du montant
épargné (montant déposé au départ + intérêt) se calcule par les règles de référence absolue des
formules excel.
 Dans le cas, où vous n'utilisez pas la référence absolue pour désigner le taux d'intérêt, le
calcul sera faux dès la deuxième année (le taux d'intérêt sera 0).

12.3.2. Montant déposé mensuellement.

Supposons que nous déposons en banque un même montant chaque début de mois. La
formule suivante va nous permettre de déterminer le montant sur le compte après une période
donnée.

Remarque, les taux des livrets sont donnés annuellement. Comme nous déposons de
l'argent tous les mois, la formule doit tenir compte des mois. De plus, la formule VC est
également utilisée en remboursement d'emprunt, le VPM (montant périodique doit être mis en
négatif

Calcul d'épargne: =VC(Taux d'épargne;nombre d'épargne;montant déposé par

périodes)

Nous verrons les autres paramètres de cette fonction Excel dans les placements. VA peut
par exemple être utilisé si de l'argent est actuellement déposé sur le compte (avec le signe
négatif). Dans notre cas, comme le montant des intérêts est toujours calculé en fin de mois, la
valeur peut-être omise.

• Supposons que nous avons déjà 1000 € sur notre compte d'épargne, la formule devient:
=VC(B1/12;B2*12;-B3;1000)
• Si la durée n'est pas de 3 ans, mais de 18 mois par exemple, la formule devient:
=VC(B1/12;18;-B3)
• et ainsi de suite. Cette formule ne fonctionne pas pour des durées en jours

L'exercice

Pour le plaisir, faîtes le calcul du montant épargné au bout de 3 ans, en arrêtant de fumer
et en déposant le montant économisé fin de mois (type peut donc être omis).

12.4. Emprunt
12.4.1. Remboursement à durée fixée

Analysons maintenant les emprunts bancaires.

remboursement: =VPM(taux remboursement;Nombre de remboursements;Valeur de

départ;montant final après le dernier remboursement;type de remboursement)

• Taux remboursement: taux périodique de l'emprunt. Il est généralement donné

annuellement, si les remboursements sont mensuels, il doit être divisé par 12.
• Nombre de remboursements, typiquement le nombre d'années multiplié par 12
• Valeur de départ: capital emprunté
• Valeur finale: valeur résiduelle après le dernier remboursement, typiquement 0
• Type: 1 si payement en début de période (généralement les emprunts
hypothécaires), 0 sinon (valeur par défaut)

Les 2 derniers paramètres peuvent être omis.

Pour un emprunt "normal"

 Pour un emprunt hypothécaire, comme le capital est généralement remboursé en début
de période, le montant est légèrement inférieur.

Dans le cas d'un emprunt hypothécaire avec remboursement trimestriel: le taux n'est plus
multiplié que par 4 (et le nombre de période multiplié par 4). Comme le remboursement est
anticipatif, le type est 1.
 12.4.2. Remboursements à montant fixe

Une autre formule financière permet de calculer la durée d'un remboursement lorsque le
montant est fixe, mais la durée inconnue.

durée d'un emprunt: =NPM(taux;montant du remboursement périodique;valeur de l'emprunt;valeur

finale;type)

Comme pour la formule VPM, l'argument VC (valeur restant en fin de période) est
généralement de 0. Le type est également de 0 (ou omis) si le remboursement se fin en fin de
mois, 1 si le remboursement se fait en début de mois.

13. Formation: sous-totaux EXCEL

1. Introduction.

Excel permet de travailler sur de grandes quantités de données en listes (clients,

factures, ...). Même si ses fonctionnalités sont relativement restreintes par rapport à une base
de donnée de type Access (notamment la limitation à 65.536 lignes et 256 colonnes), elles
sont suffisantes pour la majorité des utilisateurs professionnels.

Dans les précédents chapitres, nous avons déjà vu quelques méthodes de travail EXCEL
sur des listes de données, notamment le tri et le filtre automatique. Ces 2 fonctions permettent
déjà une certaine analyse des données mais sont encore relativement faible. Ce chapitre va
nous permettre de faire des analyses plus approfondies, quoique perfectibles en regroupant
des données et en créant des calculs par regroupement.
2. Principe des sous-totaux Excel

Commençons par créer une petite liste de donnée comme celle ci-dessous

Ceci est une liste de facture reprenant le nom du client, le numéro de facture, la famille
de produit, sa description et le prix. Nous souhaitons faire quelques statistiques sur cette liste,
comme le montant des ventes en ordinateur, ou la somme des ventes sur une famille donnée,
en gros créer des sous-totaux, par famille, type de produits informatiques, ... Cette
fonctionnalité est liée aux sous-totaux Excel.

La première commande à exécuter pour créer ces sous-totaux est de trier suivant le
type de regroupement que nous souhaitons par la fonction tri du menu Données. Le choix du
tri va dépendre des statistiques que nous souhaitons afficher.

Supposons que nous souhaitons faire des regroupements sur les clients, le tri devra se
faire suivant le nom du client.

Dans le menu Données d'Excel, sélectionnons la commande sous-totaux

 A chaque changement de sélectionne la colonne qui va servir à regrouper les données
(d'où l'importance de trier au préalable). La fonction représente le calcul que nous souhaitons
exécuter à chaque changement de client suivant 11 fonctions possibles (somme, moyenne,
nombre de valeurs, ...). Le choix doit être identique pour toutes les colonnes.

Le dernier choix permet de sélectionner la ou les colonnes sur les quelles nous
souhaitons les calculs. Dans notre liste, seul le montant est réellement calculable.

Le résulta obtenu affiche une somme totale pour chaque client (encadré en vert dans
l'exemple ci-dessus) et en rouge pour le total des factures.

Nous pouvons également réduire l'affichage pour ne garder que le total pour chaque
client. en cliquant sur le 2 à gauche ou même réduire les résultats pour 1 seul client en
cliquant sur le 2 (le 1 n'affiche plus que le total) ou par les + et les _ à gauche qui permettent
d'afficher ou de masquer les détails.
 Les sous-totaux permettent également de créer un graphique excel sur les données
regroupées.

3. Modification des sous-totaux

Supposons que nous voulons maintenant faire un sous-total sur les catégories de
produits. Pour rassembler les lignes de notre facture suivant les catégories, nous devons
refaire un tri. Malheureusement, à chaque nouveau tri, Excel supprime les sous-totaux
existants.
 Effectivement, Excel ne garde pas les sous-totaux existant lorsque vous refaites un tri
sur la plage de données. Il ne reste plus qu'à réexécuter la commande pour regrouper sur le
nouveau tri.

4. Sous-totaux multiples.

Jusque maintenant, nous n'avons effectuer des sous-totaux que sur 1 seul critère. Nous
souhaitons regrouper les données d'abord sur les familles de produits informatiques, ensuite
sur le produit lui-même (sa description).

Commençons par faire un tri sur 2 niveaux, d'abord sur la famille de produit
informatique, ensuite sur la description du produit vendu.

Cette opération effectuée, reprenons notre commande Sous-totaux et sélectionnons

comme "A chaque changement de" la famille de produits. Ceci crée effectivement 1 fonction
sous-total sur le premier critère comme ci-dessus. Exécutons cette même commande sous-
totaux du menu données, sélectionnons cette fois le produit et décochons la case "Remplacer
les sous-totaux existants".
 En théorie, le nombre de niveaux de sous-total n'est pas limité, mais comme le nombre
de niveau de tri sous Excel est limité à 3, la fonction sous-total() est également limitée à 3
niveaux. Le chapitre suivant de cette formation va analyser en profondeur le fonctionnement
de cette fonction sous-total() avec les filtres élaborés, ultime étape des fonctionnalités base de
données d'Excel.

L'utilisation complète de la fonction [Link] va être vue en détail dans le chapitre

suivant

14. EXCEL: Filtre élaboré

1. Introduction

Dans un précédent chapitre, nous avons vus le tri et le filtrage d'une liste sous Excel et
les sous-totaux. Chaque possibilité a ses avantages et ses défauts lorsque l'on recherche des
enregistrements particuliers:

1. le tri ne permet que de trier les enregistrements, mais ne permet de faire directement
des calculs comme critère de tris.
2. le filtre automatique permet de filtrer les enregistrements dans chaque colonne. Par
contre, le filtrage sur une même colonne est limité à 2 critères (suivant les fonctions
logiques ET, OU). Même si on peux effectivement faire un calcul (formule
éventuellement) sur les zones filtrées, la limitation des critères de tri rend l'utilisation
parfois compliquée.
3. Les Sous-totaux permettent des calculs mais suivant une seule formule (sauf en
utilisant manuellement la fonction sous-total). De plus, le filtrage suivant des critères
spécifiques nécessite de repasser par un tri et de recréer le sous-total. De plus, les
calculs intermédiaires

La solution finale reste les filtres élaborées. Même si ce n'est pas la solution parfaite
(on pourrait également utiliser les listes Excel), le filtrage va être beaucoup plus "élaboré"
mais plus complexe puisque le nombre de critères est infini. La solution idéale passe par un
mélange de ces fonctions, même si l'utilisation d'Access permettrait une solution plus facile.

2. Préparation d'un filtre élaboré.

Encodons les données suivantes:

Cette petite liste de produits va nous permettre de tester les listes élaborées sans trop
de problèmes.

Insérons quelques lignes au-dessus de cette liste, et copions les en-têtes de colonnes
sur la première ligne. Une remarque concernant la position des listes de critères. La majorité
des cours Excel imposent de positionner cette liste de tris sur la première ligne. Ce n'est pas
obligatoire, mais conseillé. Ceci facilite la mise en place des filtres.

Les critères de filtres se mettront juste en dessous de notre première ligne.

3. Filtres élaborés simples

Commençons par un petit filtre élaboré tout simple. Nous souhaitons uniquement les
produits de la famille ordinateur compatible. Commençons par copier PC compatible dans la
cellule B2 (en dessous de notre titre de critère). Le plus simple est d'utiliser le copier-coller.

Dans le menu Données, sélectionnons Filtrer ->filtre élaboré.

 La fenêtre de critère élaboré apparaît:

• Filtrer la liste sur place filtre directement dans la zone de cellules Excel où sont
placées les données de départ. Par contre "Copier vers un autre emplacement" permet
de créer la liste triée à un autre endroit de la même feuille
• Plages: représente la zone où se trouvent les données qui doivent être filtrées (ici en
vert).
• Zone de critères désigne la zones ou vous avez inséré vos critères de filtrage (ici en
bleu).
• Extraction sans doublon permet de ne pas afficher les données en doubles.

Essayons pour vérifier notre résultat.

Remarquez que les lignes de résultats du filtre élaboré sont notées en bleu. Nous
venons de faire un premier filtre, bien que le filtrage automatique aurait donné le même
résultat.

Pour réafficher l'ensemble de la liste, utilisez la commande "Afficher tout" du menu

Données -> Filtrer, même si ce n'est pas nécessaire pour créer un nouveau filtre.

Comme deuxième filtrage, nous recherchons les produits informatiques de la famille

ordinateur compatible ET dont le stock est de 10. Nous allons tout simplement compléter le
filtre en rajoutant 10 dans la cellule C2 (en dessous de l'en-tête "quantité" dans la zone de
critère). De nouveau, exécutons la même commande de filtrage en choisissant les mêmes
zones.
 Le filtre affiche les composants de la famille "Ordinateur Compatible" ET dont la
quantité = 10

A retenir: les critères sur une même ligne utilise la fonction logique ET: toutes les conditions doivent être remplies.

Supposons que nous souhaitions afficher les ordinateurs compatibles dont la quantité
en stock est >5. Nous allons modifier le critère dans la cellule C2 par >5. Vous pouvez
essayer les mêmes commandes avec comme critères: <5, <>10, ...

4. Filtre élaboré multicritères (lignes)

Jusqu'ici, nous avons utilisé 1 seul critère de filtre par colonne. Nous allons maintenant
utiliser plusieurs critères simultanés. Supposons que nous souhaitons afficher les PC à base de
Sempron 2600 et 2800. Reprenons notre zone de critères. Dans la cellule A2, tapons Sempron
2600, dans la cellule A3, tapons Sempron 2800. Reprenons notre commande Filtres élaborés,
gardons les mêmes cellules comme plage, mais sélectionnons maintenant les cellules A1 à A3.
Le filtrage fonctionne de la même manière sauf que nous affichons les lignes dont le produit
est Sempron 2600 et Sempron 2800.

A retenir: si vous utilisez plusieurs lignes de critères, la fonction logique utilisée est OU. Toutes les lignes répondant au
critère sont affichées.

Une petite remarque, nous n'avons pas dans ce cas sélectionné toutes les colonnes de
critères. Ceci n'est pas nécessaire puisque le filtre ne s'applique qu'à la colonne "Produits".

Pouvons nous mélanger des critères qui n'on rien à voire entre eux? Oui.
 Supposons que nous souhaitons afficher les Sempron 2600 et les produits dont le stock
est supérieur à 5. Le critère devient:

Remarquez que les 2 critères sont sur 2 lignes différentes du filtrage élaboré pour
utiliser entre ces 2 lignes la fonction logique OU.

5. Caractères de remplacement.

Pour créer votre filtres élaborés, vous pouvez également utiliser des caractères
génériques. Ils sont les mêmes qu'en DOS:

• * remplace un ensemble de caractères (l'* en fin n'est pas nécessaire).

• ? remplace 1 caractère quelconque dans une zone de texte
• Attention, le filtre élaboré fait la distinction entre les majuscules et les minuscules.

Supposons que nous souhaitons visualise tous les produits commençant par SEM. Le
filtre devient

Comme autres exemples de caractères génériques:

• *e: affiche tous les enregistrements contenant la lettre e

• e: filtre tous les enregistrements commençant par la lettre e
• ??e tous les enregistrements dont la troisième lettre est e
• Sempron 2?00: affiche tous les enregistrements Sempron 2600, Sempron 2800, ...

6. Champ vide, champ non vide.

Pour filtrer les champs dont le contenu est vide, tapez dans la zone de critère ="="

Pour filtrer les enregistrements non vides: tapez =""

 Remarque: Excel n'affiche pas ces caractères dans les cellules lorsque vous avez tapé la
valeur. Par contre, la barre de formule affiche la formule correcte.

7. Filtre élaboré multicritère (colonnes)

Commençons par rajouter quelques colonnes dans notre tableau de données de départ:
Date de création du code. Cette nouvelle colonne va nous permettre de définir plusieurs
critères sur un même champ dans cet exercice.

Supposons que nous souhaitons filtrer les produits inscrits entre le 01/01/2002 et le
31/12/2004

Nous allons tout simplement mettre 2 colonnes avec le même champ que le champ à
filtrer et mettre sur la même ligne (fonction logique ET) les 2 critères:

Ces critères peuvent être cumulés en ligne (OU) ou en colonne (ET).

8. Critères calculés.

C'est peut-être le réel avantage d'un filtre élaboré, l'utilisation de critères calculés,
même si son utilisation pose souvent quelques problèmes.
8.1 Filtres élaborés avec des calculs sur des dates

Supposons que nous souhaitons filtrer sur les produits datant de 2005. Nous pourrions
faire un filtre entre 2 dates comme ci-dessus mais nous allons utiliser les fonctions de dates
d'Excel pour le filtrage. La fonction qui récupère l'année est =annee(numero).

Dans une des cellules de la première ligne, tapez année. Dans la cellule en dessous,
tapez: =annee(F6)=valeur où F6 est la première cellule de la colonne de la zones de liste qui
contient les données dates. Valeur est la valeur recherchée. La réponse doit toujours être une
valeur logique (Vrai ou faux).

Créez maintenant le filtre élaboré suivant, Excel n'affiche que les produits inscrits en
2005.

Nous pouvons rajouter des critères. Nous souhaitons les produits sortis en juillet 2005.
Nous allons rajouter un autre critère: celui du mois. Comme nous souhaitons mois = 07 ET
Année = 2005, les 2 critères du filtrage doivent être sur la même ligne. Dans la cellule D1,
tapons le mot mois. Dans la cellule D2, =MOIS(F6)=7
8.2. Filtres élaborés avec des moyennes

L'utilisation de critères calculés dans les filtres élaborés va nous permettre également de
faire des filtrages sur des calculs. Prenons par exemple une moyenne: nous souhaitons
afficher tous les produits dont le prix de vente est supérieur au prix moyen. Commençons par
créer notre critère.

Dans la cellule E1, tapons le terme valeur. Dans la cellule E2, tapons la fonction logique
=E7>moyenne(E7:E11). Créez ensuite un filtre élaboré avec comme zone de critère les
cellules E1:E2. Le filtre n'affiche plus que les produits dont la valeur est supérieure à la
moyenne

Quasiment toutes les filtres calculés sont possibles, tant que le critère est une condition.
Comme autres exemples:

2 X le Minimum Comparaison avec la valeur d'une cellule

=E7>2*min(E7:E11) =ANNEE(F6)=Feuil2!A1

Remarquez que pour la comparaison, la référence de la cellule contenant l'année est

sur une autre feuille. Sinon, le filtre élaboré n'affiche rien!

8.3. Filtre élaboré avec de simple calcul

Nous pouvons également utiliser les 4 opérations comme filtrage.

=C6*E6>1000 est permis. Il vérifie si le stock d'un produit est supérieur à 1000 euro.

9. Fonction Sous-total()

Nous venons de voire comment créer des filtres, mais est-il possible de créer des
fonctions sur les résultats obtenus suivant une colonne?
 Oui et non. En premier, si vous insérez la fonction en bas de votre liste avant de faire
le filtre, la fonction ne tient pas compte du filtre. Dans l'exemple par exemple ci-dessous, la
somme des prix???

Par contre, vous pouvez insérer la fonction avec le filtre en fonction (pas toutes). Si
vous cliquez sur le bouton de fonction , Excel va modifier la fonction en sous-total. Le
premier argument (un chiffre de 1 à 11) reprend la fonction standard effective.

no_fonction Fonction Excel 2003 uniquement

1 MOYENNE 101
(nombre de cellule avec
2 NB 102
des chiffres)
(nombre de cellules avec
3 NBVAL des valeurs (textes, 103
chiffres, ...)
4 MAX 104
5 MIN 105
6 PRODUIT 106
7 ECARTYPE 107
Remarque: il est souvent 8 ECARTYPEP 108
nécessaire de corriger la 9 SOMME 109
plage de cellule de la 10 VAR 110
fonction manuellement,
sous peine d'oublier des 11 VAR.P 111
cellules.

Ceci n'est donc pas valable pour les fonctions personnelles, ... Lorsque le filtre est
enlevé, le sous-total se comporte comme la fonction standard.

La version Excel 2003 a modifié la fonction [Link]() en rajoutant des options. Les
numéros de fonctions entre 1 et 11 inclut les valeurs masquées (comme les autres versions),
les numéros de fonctions entre 101 et 111 (nouvelle fonctionnalité) ignore les lignes masquées.
Ces valeurs masquées sont liées à la commande ligne du menu Format: Masquer qui permet
de ne pas afficher des lignes de valeurs.

10. Comparaison des méthodes de filtrages.

L'utilisation des filtres élaborés permet de multiplier le nombre de critères par rapport
aux filtres automatiques. Ce type de filtrage permet également de faire des critères calculés.
Par contre, l'utilisation de fonctions Excel dans ces résultats passe par la commande sous-
total(), limitée à quelques opérations possibles (éventuellement utiliser la commande sous-
totaux du menu Donnée). Par contre, les résultats intermédiaires sont supprimés lorsque vous
modifiez le filtre.
 L'utilisation de la commande sous-totaux du menu Données permet de faire plus
d'opérations, mais la fonction doit rester la même dans l'ensemble des groupes de données.
Les résultats des calculs disparaissent avec la suppression de la commande sous-totaux.

La commande LISTE, apparue avec Excel 2003 permet de faire les sous-totaux de
manière plus facile, selon les 11 fonctions définies, différentes d'une colonne à l'autre si
nécessaire. Ici aussi les calculs disparaissent avec la suppression de la liste ou restent si vous
convertissez en plage avec les calculs affichés. Dans un sens, cette nouvelle commande n'est
que l'adaptation des commandes des anciennes versions.

Dans tous les cas, EXCEL montre ses limites lorsqu'il s'agit de faire des calculs dans des
listes de données importantes. L'utilisation d'une base de donnée (Etats Access par exemple)
est largement préférable.

Une dernière remarque: si vous souhaitez garder les calculs sur des filtres ou des sous-
totaux particuliers, vous pouvez sélectionner la zone souhaitée et copier les valeurs. Les
calculs avec fonctions Excel standards sont transposées directement. Par contre, dans un filtre
élaboré, si vous copiez des plages, les résultats des fonctions [Link]() sont transférées en
valeurs.

15. Fonctions de recherche Excel

1. Introduction

Dans les précédents chapitres, nous avons abordé les recherches sous Excel, sous forme
de tri, filtres, ... (en plus de la fonction recherche du menu Edition). Ce chapitre de la
formation Excel en ligne va nous permettre de rechercher des données dans une liste sous
forme de fonction.

Les applications sont nombreuses. Ces fonctions de recherche vont par exemple nous
permettre de taper un code client dans notre facture Excel et de reprendre automatiquement
les coordonnées de ce client. Ceci est également valable en tapant le code d'un produit, la
recherche va automatiquement récupérer la description, le prix et le taux de TVA.

2. Préparation des listes

Reprenons notre fichier facture Excel. Cette version inclus déjà quelques petites
fonctionnalités comme le non affichage des calculs dont le résultat est zéro et le calcul des
différentes bases de TVA. Nous allons modifier cette facture pour reprendre en premier les
coordonnées du client et en deuxième, rajouter une colonne dans les lignes de produits que
nous appellerons "Code". N'oubliez pas d'enlever la protection des cellules si nécessaire.
Rajoutons les 2 cases pour pouvoir encoder le code client en A11 et rajoutons une colonne à
gauche de Quantité pour insérer le code du produit par ligne.
 La deuxième étape va être de créer une lise de clients reprenant le code du client, son
nom, adresse, code postal, ville et numéro de TVA. Pour cela, utilisons une autre feuille du
classeur (même si la liste des clients peut être dans un classeur différent).

Sur la feuille (que nous appellerons clients), tapons dans la première ligne les différents
renseignements comme en-tête, les lignes suivantes nous permettrons de taper les
coordonnées effectives des clients. Nous pouvons éventuellement reprendre d'autres
renseignements, mais à ce stade, ce n'est pas nécessaire.

Sur une autre feuille (que nous appellerons produits), nous allons reprendre les
différentes caractéristiques de nos produits:

Jusqu'ici, rien de compliqué. Les fonctions de recherche vont reprendre dans les
différentes listes les renseignements souhaités pour notre facture. Nous verrons néanmoins
que cette solution a des limites et ne permet pas de reprendre une liste trop longue de produits
par exemple (de toute façon, Excel est limité à 65535 lignes).
 3. La fonction rechercheV()

Commençons par les produits. Le but est donc de taper le code en A14 et qu'Excel
recherche dans la liste des produits ce code, puis insère dans notre facture les descriptions,
prix et taux de TVA correspondants. Positionnons le curseur en C14 et dans insertions
fonction sélectionnons rechercheV pour les versions Excel 2000 et inférieures. Pour les
versions Excel XP et 2003, Microsoft nous a compliqué la tâche: il faut quasiment passer par
une recherche de fonction et la fonction recherche n'est plus utilisée (compatibilité avec les
versions antérieures).

RechercheV recherche une valeur dans la première colonne à gauche du tableau, puis
renvoie une valeur dans la même ligne à partir d'une colonne spécifié. Par défaut, le tableau
doit être trié par ordre croissant. Cette petite phrase en rouge va déjà nous poser quelques
problèmes, même si l'utilisation des tris Excel n'a plus de secret pour nous. La fonction
rechercheh() est identique sauf que les listes sont données en lignes au lien d'en colonnes.
 • La valeur recherchée est la cellule dans laquelle nous tapons le code de notre produit
sur la feuille facture (soit A14)
• La table de matrice est la liste de produits que nous avons créé, soit le contenu de la
feuille produits (sans la première ligne qui reprend les en-têtes. Premier problème de
cette fonction, si vous créez de nouveaux produits, ils ne seront pas repris dans la liste.
Une solution est de sélectionner plus de lignes que vous n'avez de produits.

• No_index_col est le numéro de colonne dans la table de matrice (liste des produits).
Comme dans notre facture, notre colonne reprend les descriptions, le numéro est 2 (le
numéro correspondant dans notre liste de produits pour la description)
• Valeur_proche (facultatif) propose de rechercher la valeur exacte ou non. Par défaut,
elle ne recherche pas la valeur exacte, mais la valeur la plus proche dans la liste. Nous
allons voire ci-dessous les conséquences de l'utilisation de cette valeur proche. Pour
l'instant, omettons cette valeur

Et voici notre fonction rechercheV() remplie. Essayons en A14 de taper le code PC

(repris dans notre liste de codes). Excel nous donne:

Ce n'est pas franchement ce que nous recherchons. Dans notre feuille produits, trions
notre liste en ordre croissant. Le résultat dans notre feuille facture est maintenant correcte.

Dans notre feuille Produits, tapons les produits suivants:

 Ces produits vont nous permettre d'y voire un peu plus claire. Trions ensuite l'ensemble
de la liste par ordre croissant. Dans notre cellule A14 de la feuille facture, tapons a comme
code, effectivement la description est correcte. Tapons maintenant aa comme code.

La valeur reprise est la description du produit dont le code est a et non un message
d'erreur nous signalant que le produit n'existe pas.

A retenir: dans une recherche verticale ou horizontale avec la valeur proche omise

1. si la liste n'est pas triée par ordre croissant, le résultat est généralement faux
2. Si un code n'existe pas dans une liste triée par ordre croissant, Excel reprend
automatiquement la ligne avant la valeur recherchée (la plus proche)

4. Valeur proche

Dans la cellule C14, modifions notre formule de recherche

Comme le code aa n'est pas repris dans la liste, la description affichée est #N/A, un
message d'erreur Excel. Cette solution est déjà beaucoup plus pratique. En fin de liste,
ajoutons justement ce code aa avec comme description produit aa (ne faites pas de tri, mais
modifiez la table de matrice dans la fonction pour reprendre tous les produits si nécessaires).
Le résultat devient correct

A retenir, dans une recherche verticale (RECHERCHEV) ou horizontale (RECHERCHEH), si

vous utilisez la valeur proche à zéro (faux):

• la liste n'a plus besoin d'être triée par ordre croissant

• si la valeur recherchée n'est pas dans la liste, Excel affiche un message d'erreur
signalant que le produit n'existe pas.
 Insérons de nouveau la fonction rechercheV() dans la cellule D1' pour rechercher le prix
unitaire. Je vous laisse le soin de remplir la fonction pour récupérer le code TVA du produit.

Il ne nous reste plus qu'à recopier les fonctions dans les différentes colonnes en utilisant
le petit carré en bas à droite.

Malheureusement, cette manière de travailler ne fonctionne pas. Excel va adapter les

références de cellules lignes par lignes. Il nous faut utiliser des références de cellules absolues
dans les cellules pour la table de matrice. La fonction devient dans la table de matrice:

Truc: utilisez la touche F4 dans la formule pour passer de relatif en absolu.

Il nous reste un dernier petit problème. Si nous ne tapons pas de code, notre facture
ressemble à ceci:

Les différents #N/A ne sont pas très esthétiques. Nous allons utiliser la fonction Si()
pour corriger ce problème d'affichage en l'imbriquant avec notre rechercheV:
=SI(A14<>"";RECHERCHEV(A14;produits!$A$2:$D$9;2;0);""). De cette manière, si
aucun code n'est rentré, rien n'est affiché. Vous pouvez directement modifier votre
fonction dans la barre de formule:
 Modifiez les autres fonctions de la même manière sur la première ligne de produits et
recopiez les cellules vers le bas.

Il nous reste maintenant à récupérer les coordonnées du client. Ceci ne va pas poser de
problèmes particulier. Le code du client est tapé en A10. Le nom du client est affiché en D6.
Dans cette cellule, la fonction Excel de recherche devient: =RECHERCHEV(A11;'adresse
client'!A2:J17;2;0), et ainsi de suite pour les autres coordonnées. Remarquez que le code de
recherche dans notre feuille facture peut être positionné n'importe où, pas seulement dans la
même ligne.

16. Macro Excel (1)

1. Introduction.

Une macro permet d'automatiser des tâches répétitives. Deux méthodes différentes sont
utilisées pour créer des macros Excel:

• enregistrer une séquence utilisateur (déplacements, textes, chiffres et formules,

commandes diverses, ...)
• programmation en VBA (Visual Basic Application, une version particulière du
langage de programmation Visual Basic).

Dans cette formation, nous ne verrons que la méthode "enregistrer une séquence
d'instructions". Excel crée automatiquement la liste des commandes en VBA. Cette méthode
pour créer une macro Excel est similaire à celle de Microsoft Word. Par contre, la création
d'une macro Access ne peut pas se faire en enregistrant une suite de déplacements, mais par
VBA ou en sélectionnant la liste des commandes disponibles.

2. Débuter en macros.

Les commandes sont reprises dans le menu Outils: Macro et Macros complémentaires.
 Pour faciliter la compréhension, nous allons tout de suite commencer par créer une
nouvelle macro. Positionnez votre curseur dans la cellule A1 avant de commencer.

4 options sont disponibles:

1. Le nom de la macro: il doit être en 1 seul mot, sans espaces, trait d'union,
underscore, ...
2. Le raccourci clavier. Ceci permet de démarrer la macro par une combinaison de
touche. N'utilisez pas de raccourcis existants comme CTRL + G (gras), CTRL + I
(italique), CTRL + U (souligné). Les raccourcis peuvent être constitués des touches
<CTRL>, <ALT>, suivi ou non de <Shift>, suivi d'une lettre ou d'un chiffre.
3. Ce classeur ou nouveau classeur ou classeur de macros personnelles. Pour l'instant,
sélectionnons ce classeur.
4. Description de la suite de commandes comme commentaire (optionnel)

Cliquez sur le bouton OK pour continuer. La fenêtre d'enregistrement disparaît et une

petite fenêtre :

Cette fenêtre va nous suivre tout au long de l'enregistrement de notre suite de commandes.
La croix ferme la fenêtre, n'arrête pas la macro. Pour récupérer cette fenêtre en cours
d'enregistrement, vous pouvez utiliser le menu Affichage -> Barre d'outils -> Arrêter
l'enregistrement.

Le mode relatif ou absolu permet d'enregistrer les déplacements du pointeur dans les
cellules ou non. Elle n'est disponible que depuis la version Excel XP et 2003. Notre premier
exemple va clarifier cette option.

Le bouton "Référence absolu / relatif" ne dois pas être enfoncé, nous travaillons donc en
absolu. Nous avons placé le curseur sur la cellule A1 avant de commencer l'enregistrement.
Tapez les chiffres ci-dessus dans votre macro
 Dans la cellule A3, faites la sommes des 2 cellules supérieures, soit =somme(A1:A2) et
arrêtez l'enregistrement de la macro. Supprimez le contenu des 3 cellules et positionnez le
curseur dans n'importe quelle cellule (sauf A1), par exemple en B2.

Nous allons exécuter notre première macro. Dans le menu Outils, sélectionnez la
commande Macro -> Macros. Vous pouvez également utiliser le raccourci clavier <ALT> +
<F8>. Ceci fait apparaître la listes des macros disponibles pour ce classeur.

Utilisez la commande Exécuter.

La cellule de départ B2 reçoit notre premier chiffre tapé durant l'enregistrement, soit 34.
La cellule A2 reçoit le deuxième chiffre, effectivement tapé durant l'enregistrement en A2. La
cellule A3 reçoit effectivement la fonction Somme.

Que c'est-il passé. Dans le mode absolu, la macro enregistre les déplacements en
mode absolu (la référence des cellules). Reprenons le cheminement de notre commande.

Enregistrement Exécution
Avant Le curseur est placé en A1 Le curseur est placé en B2
Nous tapons 34, sans déplacer La macro tape 34 dans la
Début
le pointeur cellule en cours, soit B2
Nous déplaçons le curseur en
Excel déplace le curseur en A2
A2
Excel insère 45 dans la cellule
Nous tapons 45
en cours
Nous déplaçons le curseur en
Le curseur est déplacé en A3
A3
La fonction somme des 2
Nous faisons la somme des 2
cellules supérieures est insérée
cellules supérieures (A1:A2)
dans la cellule active

En suivant ce tableau, Excel a strictement suivi les instructions de l'enregistrement.

Si nous recommençons la même macro en déplaçant le curseur vers la cellule A1 pendant

l'enregistrement, la liste de commande tapera effectivement 34 dans la cellule A1.

3. Macro relative.

Nous venons d'utiliser la méthode absolu, essayons maintenant la méthode relative.

Effacez le contenu de la feuille et positionnez le curseur en A1 comme précédemment. Créez
une nouvelle macro. Lorsque la fenêtre arrêter l'enregistrement apparaît, cliquez sur le
bouton relatif. Il doit avoir l'aspect coloré.

Tapons de nouveau 34 en A1, 45 en A2 et la somme de ces nombres en A3. Fermer

l'enregistrement, effacez le contenu et positionnez le curseur en B2 par exemple. Exécuter la
macro relative.

Le résultat est maintenant différent.34 est bien repris dans notre cellule de départ, soit
B2. Vous pouvez positionner votre curseur n'importe où dans votre feuille, la suite de chiffre
est toujours tapée à l'endroit de départ.

Effaçons le contenu de notre feuille et positionnons le curseur dans la cellule B2 par

exemple. Commençons l'enregistrement d'une nouvelle macro en mode relatif.

Comme opération, descendons le curseur de 2 lignes et tapons un chiffre. Terminez

l'enregistrement et exécutez la macro. Quel que soit la position de votre curseur avant
l'exécution, le chiffre 34 est toujours tapé 2 cellules en dessous.

Remarque: vous pouvez passer d'un mode à l'autre en cours d'enregistrement.

 4. Problèmes avec les macros
relatives.

A retenir: Lorsque vous enregistrez une macro Excel en mode absolu, l'exécution suit les
mouvements du pointeur en absolu, suivant la référence exacte de la cellule. Par contre,
enregistrer une macro en mode relatif enregistre les déplacements suivant la différence du
nombre de lignes et de colonnes.

En mode absolu, le curseur se déplace vers une référence de cellule bien définie. Par
contre, en relatif, l'enregistrement se fait sur le déplacement. En relatif, l'exécution peut
déplacer le curseur sur des cellules inexistantes.

Comme exemple, positionnons le curseur en B5 et débutons l'enregistrement d'une macro

en mode relatif. Déplacez le curseur en A1 (soit -1 colonne et - 4 ligne) et tapez par exemple
YBET informatique. Arrêtez l'enregistrement. Positionnez votre curseur en B6, le texte est
effectivement tapé en A2. Par contre, placez votre texte en A2 et exécutez la macro. La
fenêtre de débogage apparaît.

Ce type d'erreur n'apparaît jamais en absolu sauf si vous déplacez le curseur vers une
feuille préalablement effacée.

5. Exécuter une macro.

Il y a 4 méthodes possibles pour exécuter une macro Excel, c'est identique en Word.

1. Par le menu Outils, c'est la méthode que nous venons d'utiliser

2. En créant un bouton dans une barre d'outils ou même en ajoutant une commande dans
un menu.
3. En créant un raccourci clavier
4. En créant un bouton dans la feuille Excel.

5.1. Bouton dans une barre d'outils ou dans un menu.

C'est la méthode la plus courante.

Sélectionnez la commande "Barre d'outils" dans le menu Affichage et la sous commande

Personnaliser tout en dessous.
 Dans l'onglet "Commande", sélectionnez dans les catégories Macros. Glissez le "bouton
personnalisé" vers une boîte d'outils. le résultat doit être similaire à ceci:

Cliquez avec le bouton droit de la souris sur ce bouton (menu contextuel) pou faire
apparaître le menu suivant:
 Réinitialiser permet de remettre le bouton de départ en cas de modifications

Supprimer permet de supprimer ce bouton de la barre d'outils. L'autre solution est de

glisser ce bouton en bas de la barre d'outils.

Nom permet de donner un nouveau nom à ce bouton.

Copier l'image du bouton permet de copier l'icône pour l'utiliser sur un autre bouton
personnalisé.

Coller l'image du bouton permet de reprendre une image copiée.

Rétablir l'image du bouton permet de reprendre l'aspect avant modification

Editeur de bouton permet de dessiner l'icône

 Modifier l'image du bouton propose des icônes différentes

Les 4 lignes suivantes sont liées à l'aspect de cette commande dans la barre d'outils:

Par défaut (icône) - Texte seul (le texte tapé comme nom) - Masquer les images dans
les menus (options pour ne plus afficher les icônes à coté des commandes dans les menus) -
Image et texte affiche le bouton et le nom dans la barre d'outils.

Nous pouvons directement affecter une macro par ce menu, mais si vous ne le faites pas,
en cliquant sur le bouton la première fois (après avoir quitter le mode personnalisé), Excel le
propose automatiquement et affiche les macros disponibles.

Cette méthode permet également de modifier, ajouter ou supprimer des commandes dans
les menus.

Exercice: créez un bouton de même aspect que celui de l'impression standard mais avec
une ligne rouge

5.2 Exécuter une macro par un raccourci clavier.

Vous pouvez également exécuter une macro en lui associant un raccourci clavier. Pour
créer ce raccourci, reprenez votre fenêtre de macros. Sélectionnez la macro souhaitée et
cliquez sur le bouton option.
 Vous pouvez ainsi affecter le raccourci souhaité. Sont
autorisés (en combinaison)

•
les touches ALT - CTRL et ALTGR (ou 2
ensembles)
• Shift (optionnel)
• 1 lettre ou 1 chiffre - Excel distingue les
chiffres du pavé numérique de ceux du clavier standard.

5.3. Bouton sur la feuille de calcul

Cette solution passe par l'utilisation de la barre d'outils Formulaires.

Utilisez le bouton dans la barre d'outils pour créer un bouton directement sur votre
classeur Excel. La fenêtre pour affecter une macro apparaît immédiatement.

Il ne vous reste plus qu'à sélectionner la macro.

16. Macros Excel (suite et exercices)

1. Introduction.

Avec le chapitre précédant, nous avons étudier une vue d'ensemble des macros Excel.
Cette partie va expliquer d'autres spécificités des macros, mais surtout donner des exemples
de macros. Au final, nous utiliserons une macro pour automatiser certaines parties de notre
facture Excel.

2. Macro pour tous les classeurs, classeur spécifique.

Lorsque vous créez une macro Excel, la première fenêtre pose la question d'enregistres
dans? (3).

Les différentes possibilités sont:

 1. Classeur de macros personnelles: (dans votre classeur personnel [Link]) Ce choix
vous permet d'utiliser la macro créée à partir de n'importe quel classeur.
2. Nouveau classeur dans un nouveau classeur qu'Excel crée automatiquement. La
macro ne sera utilisable que dans ce classeur
3. Ce classeur, macro valable uniquement dans le classeur en cours
4.

Remarque: Vous disposez de 3 méthodes pour qu'une macro soit accessible dans tous les
classeurs:

I. l'écrire dans un module du classeur [Link].

II. l'écrire dans un module d'un classeur ouvert automatiquement avec Excel ( le classeur
doit être enregistré dans le dossier C:\program Files\Microsoft Office\Office\XLStart
ou C:\Windows\Application Data\Microsoft\Excel\xlstart selon votre version de
Windows.
III. Dans l'environnement VB, vous pouvez enregistrer le classeur en tant que macro
complémentaire avec le type Macro complémentaire.

[Link] complémentaires.

Excel intègre quelques fonctions complémentaires optionnelles. La liste se trouve en

macros complémentaires dans le menu Outils. Leur installation est optionnelle. Ces macros
sont insérées comme commandes additionnelles dans le menu Outils.

Par exemple, le solver est une fonction étendue de la valeur cible permettant d'insérer des
conditions dans les cellules à modifier pour obtenir le bon résultat.

4. Créer ses macros

Comme exemple d'utilisation des macros et comme exercice, nous allons créer différents
types de macros Excel. Nous terminerons par une macro pour notre facture (l'exercice de
toute cette formation Excel).

Avant de commencer à programmer des macros, songer que l'ordinateur est bête. Il
exécute très vite et sans erreurs des tâches simples, à condition de lui signaler tout ce qu'il doit
faire, pas à pas.

De plus, lorsque vous enregistrez une macro par cette méthode, une simple erreur peut
vous faire recommencer tout l'enregistrement de votre macro. Mieux vaut donc faire la
procédure lentement.
5. Exemples de macro "fichier"

Cette partie va reprendre des exemples de macros concernant l'ouverture, enregistrement

de classeurs Excel, impression, ...

5.1. Ouverture d'un fichier

Cette macro va nous permettre d'ouvrir un fichier spécifique à l'aide d'un bouton dans une
barre des tâches. Elle doit être créée avec l'option "Classeur de macro personnel".

Commencer par créer une nouvelle macro. Utilisez la commande Ouvrir du menu Fichier
et sélectionnez "Autre classeur" dans le volet Office(version XP et 2003)

Lorsque la fenêtre de choix Windows apparaît, sélectionnez le dossier où se trouve votre

document. Cette étape est obligatoire, sinon, la macro Excel cherchera le document dans le
dossier en cours.

Une fois le dossier sélectionné, sélectionnez le document dans la liste et arrêtez

l'enregistrement de votre macro.

Exercice: créez un bouton dans la barre d'outils standard exécutant cette commande.

L'enregistrement de cette suite de commande est exactement identique à une opération

courante d'ouverture de fichier. Une seule petite distinction est la sélection obligatoire du
dossier avant de sélectionner le fichier.

5.2. Impression du classeur sur une autre imprimante.

Nous allons créer une macro qui imprime le classeur courant sur une autre imprimante
(une imprimante réseau par exemple) et remet l'imprimante par défaut. De nouveau, nous
devons donner comme option "Classeur de macro personnel".

Une fois l'enregistrement commencé, sélectionnez Imprimer dans le menu Fichier,

sélectionnez l'imprimante sur la qu'elle vous désirez imprimer et faites OK. L'impression
démarre (y compris en mode enregistrement). Resélectionnez la commande Imprimer du
menu Fichier, sélectionnez votre imprimante par défaut et cliquez sur le bouton "Fermer", pas
OK qui démarrerais l'impression sur votre imprimante une seconde fois. Arrêter
l'enregistrement de la macro.

Exercice: utilisez le bouton imprimante créé ci-dessus.

6. Exemples de macros "Mises en page".

Ce type de macros va nous permettre de faire des mises en formes (police, alignement,
couleur de fond, ...) sur des cellules sélectionnées. Une seule difficultés pour ce type de
macros, l'enregistrement ne permet pas de sélectionner une zone de cellules. Vous devez donc
sélectionnez une zone de cellules avant de démarrer l'enregistrement. De même, vous devrez
sélectionner les cellules avant d'exécuter la macro de commande.

Commençons par créer une nouvelle macro avec une plage de cellule sélectionné.
Modifiez la mise en forme des cellules à l'aide de la commande cellule du menu Format. Une
fois terminé, arrêter l'enregistrement.

Exercice: affecter un raccourci clavier à cette macro, par exemple <CTRL> + >Shift> +
I. Pour cette opération, utilisez la commande macros du menu outils, sélectionnez la macro et
cliquez sur le bouton Options.

7. Calculs

Cette parties des exemples d'utilisation des macros en Excel va sans doutes être la plus
difficile. D'une part, elle va utiliser les notions relatives et absolues, d'autre part, elle nécessite
de connaître quelques raccourcis claviers.

• CTRL + END: positionne le curseur le plus en bas à gauche du tableau

• CTRL + Home: positionne le curseur en A1
• Shift + CTRL + END: sélectionne les cellules à droites et en bas de la cellule
courante
 • Shift + CTRL + HOME: sélectionne les cellules à gauches et en haut jusqu'à
A1
• Shift (maj) + END suivi de la flèche vers la gauche: positionne le curseur vers
la cellule utilisée la plus à gauche de la cellule active
• Shift (maj) + END (Fin) suivi de la flèche vers la droite: positionne le curseur
vers la cellule utilisée la plus à droite de la cellule active.
• Shift (maj) + END (Fin) suivi de la flèche vers le haut: positionne le curseur
vers la cellule utilisée la plus à proche en direction du haut de la cellule active.
• Shift (maj) + END (Fin) suivi de la flèche vers le bas: positionne le curseur
vers la cellule utilisée la plus à proche en direction du bas de la cellule active.

Par contre:

Si vous maintenez les 3 touches ensembles, Excel sélectionne l'ensemble des cellules:

. à droite, flèche droites (y compris les cellules vides si vous êtes à la dernière colonne de
données)

. à gauche, flèche gauche, jusqu'à la colonne A

. en haut, flèche haute jusqu'à la ligne 1

. en bas, flèche bas (jusqu'à la ligne 65635 si vous être à la dernière ligne de données).

Le mieux est d'essayer ces raccourcis claviers au préalable.

7.1. Mettre automatiquement une valeur dans une cellule donnée (par
exemple A3).

Dans ce cas, comme la référence de cellule est connue, nous devons travailler en mode
absolu (bouton grisé). Durant l'enregistrement, sélectionnez la cellule A3 (même si le curseur
est déjà positionné) et tapez la valeur souhaitée.

7.2. Copier le contenu de la cellule active dans une autre cellule.

Comme premier exercice, nous allons copier le contenu de la cellule active dans la
cellule 2 lignes plus bas. Dans ce cas, comme la référence de cellule est inconnue (où plutôt
relative par rapport à la cellule en cours), nous devons sélectionner le mode relatif durant
l'enregistrement de notre macro. Si vous souhaitez copier toute une plage de cellule, vous
devez le faire avant de débuter l'enregistrement.

Dans la cellule en cours, utilisez la commande "copier" (soit à partir du menu Edition,
soit à l'aide du raccourci clavier CTRL + C). Descendez de 2 cellules et utilisez la fonction
copier.

Par contre, si nous souhaitons copier le contenu de la cellule en cours vers la cellule A3
par exemple, vous devez utiliser le mode absolu.
7.3. Insérer une colonne avec une formule

Si nous désirons insérer une colonne entre la troisième et la quatrième par exemple, il
suffit (en mode absolu) de sélectionner la colonne 4 et d'insérer une colonne (menu
contextuel).

Par contre, voyons l'exemple ci-dessous.

A partir de ce tableau, nous souhaitons rajouter à droite de la dernière colonne le total.

Nous ne savons pas combien de lignes seront présentes et pour compliquer, ce tableau peut
comporter des colonnes avant notre colonne quantité et des colonnes à droite.. Voici comment
créer la macro.

Commencez l'enregistrement en mode relatif. Pour positionner le curseur le plus en bas à

droite, utilisez le raccourci CTRL + END. Premier problème, les colonnes sont toujours
insérées à gauche sous Excel. Déplaçons le curseur d'une case à gauche (d'où le mode relatif)
et par le menu insertion, sélectionnons colonne. Tapons maintenant notre formule en relatif:
=ref-cell-quantité*ref-cell-prix (utilisez les flèches, c'est plus facile). Il ne nous reste plus qu'à
recopier notre formule vers le haut. Déplacez le curseur vers la cellule contenant la formule.
CTRL + shift + flèche haute va sélectionner les cellules vers le haut. Il ne reste plus qu'à
utiliser la commande remplissage en haut du menu Edition.

Dernier problème. Comme notre première ligne contient du texte (les en-têtes de
colonnes), cette cellule contient un message d'erreur. CTRL + flèche haut positionne le
curseur sur cette cellule. Il ne reste plus qu'à effacer le contenu.

7.4. Copier le contenu d'une cellule contenant une formule.

Cette fonctionnalité est un peu plus compliquée. Cette fonctionnalité va nous permettre
de sauvegarder les données de notre facture sur une nouvelle feuille par exemple. Si vous
copier et coller une formule, comme Excel adapte les formules, le résultat est modifié lors du
déplacement.

La solution passe par un copier suivi d'un collage spécial "valeur".

8. Exercice final

Nous allons utiliser les notions de macros ci-dessus pour faire une superbe macro adaptée
à notre facture. Cette macro doit permettre à l'aide d'un bouton associé au classeur facture de:

1. Copier les données de facturation sur une autre feuille en colonne (à créer au
préalable). Ceci permet de conserver un résumé des factures effectuées
(éventuellement ceci peut servir pour le listing TVA).
2. Repasser sur la feuille du contenu de la facture, effacer les données à l'intérieur
de la facture, remettre "Code client" au lien du code modifié. Remettre la date avec la
fonction aujourd'hui()
3. Incrémenter le numéro de facture.

Cette dernière partie nécessite en mode absolu: de copier le numéro de facture dans une
cellule vide. Dans la cellule adjacente, additionner cette valeur avec 1. Copier le contenu en
collage spécial vers la cellule reprenant le numéro de facture et effacer le contenu des calculs
intermédiaires.