Cours Excel ESCM

COURS EXCEL
B1-G2 ESCM

1
Cours Excel ESCM

Table des matières

I. FORMULES DE CALCULS ET FONCTIONS.........................................................................................3
1.1. Structure des formules de calcul.........................................................................3
1.2. Introduction sur les fonctions................................................................................4
1.3. Fonction somme............................................................................................................5
1.4. Fonction moyenne........................................................................................................6
II. Formules de calculs et fonctions....................................................................................................8
2.1. Fonction si........................................................................................................................8
2.2. Fonction SI et imbrications...................................................................................................20
2.3. Fonction RECHERCHEV....................................................................................................26
2.4. Autres fonctions....................................................................................................................29
III. Opérateurs de comparaison.....................................................................................................31
IV. Insertion de graphiques............................................................................................................32
V. Mises en forme conditionnelles...................................................................................................41
VI. Tableau croisé dynamique........................................................................................................45
6.1. Insérer un tcd................................................................................................................46
6.2. Modifier un tcd.............................................................................................................48
6.3. Modifier l'ordre.............................................................................................................50
6.4. Ajouter un filtre............................................................................................................51
6.5. Appliquer UN STYLE..................................................................................................51

2
Cours Excel ESCM

I. FORMULES DE CALCULS ET FONCTIONS

I.1. Structure des formules de calcul
Commencez toujours votre calcul par le signe =, ensuite sans
laisser d’espaces, placez un chiffre suivi d’un signe suivi d’un autre
chiffre, etc. Ajoutez des ( ) si cela s’avère nécessaire.

Exemples :

=45-32 (Excel affichera la réponse : 13)

=(45-32)^2/10 (Excel affichera la réponse : 16.9)

Observez ce tableau :

Les calculs peuvent également être effectués à partir des données

provenant de cellules.

Dans ce cas, commencez aussi par un =, cliquez ensuite sur une

première cellule, ajoutez un signe, cliquez sur une autre cellule, etc.
Ajoutez des ( ) si cela s’avère nécessaire.

3
Cours Excel ESCM

Observez bien les formules :

Les formules du tableau ci-dessus utilisent les valeurs de A8 et A9.

Le principal avantage de ce système est de pouvoir modifier tous les
résultats automatiquement (sans toucher aux formules) en
changeant simplement les valeurs de A8 et A9, exemple :

4
Cours Excel ESCM

I.2. Introduction sur les fonctions

Il existe une multitude de fonctions mais nous ne verrons ici que

quelques fonctions pour vous en montrer l’utilité.

I.3. Fonction somme

Sélectionnez la cellule où vous souhaitez entrer la somme. Cliquez

ensuite sur l'onglet « Formules » puis sur « Somme automatique ».

5
Cours Excel ESCM

Sélectionnez ensuite la plage de cellule dont vous souhaitez obtenir

la somme (dans le cas présent, la sélection proposée par Excel est
la bonne).

La somme des cellules D2 à D6 a bien été calculée.

6
Cours Excel ESCM

I.4. Fonction moyenne

Il existe différents raccourcis dans l'onglet « Formules » pour

insérer une fonction. Cette fois-ci, nous allons insérer la fonction
sans utiliser l'un de ces raccourcis ...

Sélectionnez la cellule où vous souhaitez insérer la moyenne et

cliquez sur « Insérer une fonction » :

7
Cours Excel ESCM

Recherchez et sélectionnez la fonction « MOYENNE » :

Appuyez sur OK. La fenêtre de la fonction s'ouvre.

Sélectionnez la plage de cellules à utiliser pour calculer la moyenne.

Vous obtenez :
8
Cours Excel ESCM

II. Formules de calculs et fonctions

II.1. Fonction si

La fonction SI permet de retourner une valeur ou une autre en

fonction d'une condition.

Cliquez sur « Insérer une fonction » et sélectionnez la fonction SI.

9
Cours Excel ESCM

Dans ce cas, le but est d’afficher « Oui » si le résultat est plus grand
que 10 et « Non » si ce n’est pas le cas.

Le test logique va permettre de savoir si la fonction doit afficher «

Oui » ou « Non ».

 Test logique : D2>10 (dans ce cas, on demande si D2 est plus

grand que 10)
 Si c’est vrai, la fonction affiche « Oui ».
 Si c’est faux, la fonction affiche « Non ».

Entrez les différentes valeurs et validez.

10
Cours Excel ESCM

Si l'une des valeurs est du texte, il est nécessaire de la mettre entre

guillemets (exemple : "Oui").

Un « Oui » est alors affiché en E2.

Maintenant faites une recopie incrémentée jusqu’à la cellule E6.

11
Cours Excel ESCM

Le résultat est correct (les formules se sont adaptées : à chaque

recopie d'une ligne vers le bas, le n° de ligne des cellules de la
formule a été augmenté de 1).

Maintenant, si l'on veut changer la valeur limite de 10 (pour

déterminer si c'est « Oui » ou « Non » qui doit être affiché), il va
falloir modifier chaque formule, ce n'est donc pas très pratique.

A la place d'entrer la valeur 10 directement dans la formule, nous

allons faire référence à une cellule contenant 10. De cette manière,
lorsqu'on voudra modifier cette valeur limite, il suffira de modifier la
valeur de la cellule.

12
Cours Excel ESCM

Pour le faire, remplacez simplement « 10 » par « A10 » :

Recopiez la formule.

13
Cours Excel ESCM

On constate qu'il y a un problème, A10 n'a pas été conservé dans les
formules ...

Pour y remédier, il va falloir "fixer" A10.

14
Cours Excel ESCM

Une solution consiste à ajouter un $ devant le n° de ligne et le n° de

colonne.

Il faut donc remplacer A10 par $A$10 (ou par A$10 puisqu'il suffit ici
de "fixer" le n° de ligne).

Après recopie, le résultat est cette fois correct :

Une autre possibilité consiste à renommer la cellule A10 :

15
Cours Excel ESCM

Et à remplacer A10 par limite qui est ici le nom de la cellule.

Après recopie, le résultat est également correct :

16
Cours Excel ESCM

Vous pouvez maintenant changer la limite de toutes les formules en

modifiant uniquement la cellule A10.

EXERCICE

Fichier : [Link]

Dans cet exemple, la fonction SI devra tester l'âge de la personne et

afficher "Oui" ou "Non" en fonction de ce test :

17
Cours Excel ESCM

Insérez la fonction SI dans la cellule C2 depuis l'onglet "Formules" :

La fenêtre de la fonction s'ouvre ensuite et vous indique quelles sont

les informations à renseigner pour cette fonction :

18
Cours Excel ESCM

Vous pouvez remarquer que cette fonction nécessite 3 arguments

(qui sont les données à fournir à la fonction).

Lorsque votre curseur est dans le champ d'un argument, un

descriptif est affiché au-dessous pour vous aider à mieux
comprendre quelle donnée vous devez lui fournir.

Ajoutez maintenant le test logique en cliquant sur la cellule B2 (ou

en entrant B2) suivi de >=18 pour obtenir le test logique B2>=18 (qui
va vérifier si la valeur de B2 est plus grande ou égale à 18) :

19
Cours Excel ESCM

Puis entrez "Oui" pour la valeur si VRAI et "Non" pour la valeur si

FAUX :

20
Cours Excel ESCM

Les valeurs textuelles doivent être placées entre " " dans les
formules comme c'est le cas ici avec "Oui" et "Non".

Recopiez ensuite la formule :

Pour obtenir :

21
Cours Excel ESCM

II.2. Fonction SI et imbrications

Au lieu d'afficher "Oui" ou "Non" en fonction de l'âge comme c'était

le cas à la page précédente, nous allons cette fois-ci retourner la
valeur d'une cellule.

La fonction SI devra maintenant afficher le tarif en fonction de

l'âge :

Insérez la fonction SI et entrez ensuite le même test logique qu'à la

page précédente B2>=18 (qui vérifie si l'âge est plus grand ou égal à
18).

22
Cours Excel ESCM

La valeur si VRAI doit être ici la référence à la cellule contenant le

tarif adulte et la valeur si FAUX, la référence au tarif enfant :

Rappelez-vous que les références sont décalées lors de la recopie, il

faut donc encore ajouter les $ aux références des tarifs :

23
Cours Excel ESCM

La formule =SI(B2>=18;$F$3;$F$2) est maintenant prête à être

recopiée :

Le tarif de chacun est alors affiché :

IMBRIQUER UNE FONCTION DANS UNE AUTRE

Pour obtenir le résultat souhaité, il sera parfois nécessaire

d'imbriquer plusieurs fonctions ...

Ajoutons un tarif supplémentaire pour cet exemple :

24
Cours Excel ESCM

Dans ce cas, si la personne a moins de 18 ans, le tarif ne change

pas. En revanche, pour un adulte 2 tarifs restent possibles (adulte ou
retraité), il faut donc ajouter un second test logique.

Pour ouvrir la fenêtre de la fonction, sélectionnez la cellule C2 et

cliquez sur "Insérer une fonction" depuis l'onglet "Formules" :

Supprimez ensuite le contenu de la valeur si VRAI (puisqu'elle

dépendra du second test) :

25
Cours Excel ESCM

Et ajoutez une nouvelle fonction SI en la sélectionnant dans la liste

à gauche :

26
Cours Excel ESCM

Entrez ensuite le second test logique B2>=65 et les tarifs

correspondants (sans oublier les $) :

27
Cours Excel ESCM

La formule =SI(B2>=18;SI(B2>=65;$F$4;$F$3);$F$2) contient 2 fonctions

SI et permet d'afficher maintenant les tarifs en fonction de 3
tranches d'âge :

Le résultat après recopie :

II.3. Fonction RECHERCHEV

La fonction Excel RECHERCHEV recherche une valeur dans la

première colonne d'un tableau puis renvoie la valeur d'une cellule
qui se situe sur la même ligne que la valeur recherchée.

Utilisation :

=RECHERCHEV(recherche; tableau; colonne; type)

EXEMPLE D'UTILISATION

28
Cours Excel ESCM

L'objectif ici est de rechercher des informations en fonction du

numéro de dossier. L'utilisateur doit pouvoir entrer le numéro de
dossier dans la partie verte et voir ensuite le résultat de sa
recherche dans la partie bleue :

Sélectionnez pour commencer la fonction RECHERCHEV :

 Dans "Valeur_cherchée", entrez la valeur à rechercher dans la première colonne du tableau

(ici, le numéro de dossier).
 Dans "Table_matrice", entrez la plage de cellules qui contient les données du tableau.
 Dans "No_index_col", entrez le numéro de colonne du tableau qui contient le résultat à
renvoyer (ici, la colonne 2 pour la ville).
 Dans "Valeur_proche", entrez FAUX pour rechercher la valeur exacte de "Valeur_cherchée"
(dans le doute, entrez FAUX pour éviter les surprises). Vous pouvez aussi choisir de
rechercher la valeur la plus proche de "Valeur_cherchée" en entrant VRAI (ou en laissant
vide).

29
Cours Excel ESCM

Le nom de la ville est alors affiché :

30
Cours Excel ESCM

Pour afficher ensuite les points, il suffit de copier la formule et de

modifier le numéro de colonne (remplacez 2 par 3) :

II.4. Autres fonctions

Vous trouverez sur cette page les fonctions les plus utilisées,

DATE ET HEURE
AUJOURDHUI Affiche la date du jour.

LOGIQUE
31
Cours Excel ESCM

ET Vérifie si tous les tests sont VRAI.

OU Vérifie si au moins un des tests est VRAI.

SI Effectue des actions en fonction d'une condition.

Renvoie une valeur s'il n'y a aucune erreur ou une autre

SIERREUR
valeur en cas d'erreur.

MATH ET TRIGO
ALEA Génère aléatoirement une valeur.

ARRONDI Arrondit une valeur.

[Link] Arrondit une valeur vers le bas.

[Link] Arrondit une valeur vers le haut.

RACINE Effectue la racine carrée.

SOMME Calcule la somme d'une série de valeurs.

Calcule la somme à partir des valeurs qui remplissent la

[Link]
condition.

SOMMEPROD Renvoie la somme des produits de plages de valeurs.

RECHERCHE ET RÉFÉRENCE
Affiche une valeur ou effectue une action en fonction d'un
CHOISIR
choix.

EQUIV Renvoie la position d'une valeur dans une liste.

Filtre une plage de cellules en fonction des critères

FILTRE
définis.

Combinaison semblable à RECHERCHEH/RECHERCHEV

INDEX + EQUIV
sans la contrainte de la première ligne/colonne.

Renvoie une valeur d'un tableau en fonction d'une valeur

RECHERCHEH
se trouvant dans la première ligne.

Renvoie une valeur d'un tableau en fonction d'une valeur

RECHERCHEV
se trouvant dans la première colonne.

Recherche une valeur dans un tableau puis renvoie la

RECHERCHEX valeur correspondante à la même position dans un second
tableau.

TRIER Trie le contenu d'une plage de cellules ou d'un tableau.

32
Cours Excel ESCM

Trie le contenu d'une plage de cellules ou d'un tableau en

TRIERPAR
fonction d'une plage de cellules ou d'un tableau.

Renvoie la liste des valeurs uniques d'une plage de

UNIQUE
cellules ou d'un tableau.

STATISTIQUES
MAX Renvoie la plus grande valeur.

MIN Renvoie la plus petite valeur.

MOYENNE Effectue la moyenne d'une série de valeurs.

NB Calcule le nombre de cellules contenant des nombres.

Calcule le nombre de cellules contenant une valeur

[Link]
déterminée.

[Link] Calcule le nombre de cellules vides.

NBVAL Calcule le nombre de cellules non vides.

PREVISION Extrapole à l'aide de données connues.

Attribue un classement à une valeur en fonction d'une série

RANG
de valeurs.

TEXTE
CONCATENER Assemble des valeurs, les unes après les autres.

DROITE Extrait des caractères en partant de la droite.

GAUCHE Extrait des caractères en partant de la gauche.

Assemble les valeurs d'une ou de plusieurs plages de

[Link]
données à l'aide du séparateur défini.

LET Affecte un nom aux résultats de calculs.

MAJUSCULE Convertit en majuscules.

MINUSCULE Convertit en minuscules.

Convertit la première lettre de chaque mot en majuscules

NOMPROPRE
et les autres lettres en minuscules.

STXT Extrait des caractères d'une chaîne.

SUBSTITUE Remplace des valeurs textuelles par d'autres.

33
Cours Excel ESCM

SUPPRESPACE Supprime les espaces inutiles du texte.

III. Opérateurs de comparaison

Dans l'exemple précédent, nous avons voulu savoir si la valeur était
« plus grande que » en utilisant le signe >. Voici les autres
opérateurs que vous pouvez utiliser :

= est égal à
< est différent de
>
< est plus petit que
< est plus petit ou
= égal à
> est plus grand que
> est plus grand ou
= égal à

Ainsi que :

ET =ET(formule_1;formule_2;formule_3
)
Toutes les conditions doivent être
vraies
O =OU(formule_1;formule_2;formule_3
U )
Au moins une des conditions doit
être vraie

Voici un exemple avec la fonction SI et l'utilisation de « ET » :

34
Cours Excel ESCM

IV. Insertion de graphiques

Le tableau suivant sera utilisé en exemple lors de cette
leçon : [Link]

Sélectionnez le tableau et choisissez un graphique dans l'onglet «

Insertion ».

35
Cours Excel ESCM

Le graphique est inséré sur la feuille.

36
Cours Excel ESCM

Lorsque vous sélectionnez le graphique des onglets supplémentaires

sont affichés.

Pour changer (si nécessaire) de type de graphique, cliquez sur «

Modifier le type de graphique ».

Et choisissez un nouveau type.

37
Cours Excel ESCM

Vous pouvez choisir une des dispositions de graphique prédéfinies.

Vous pouvez également choisir l'un des styles prédéfinis.

38
Cours Excel ESCM

A partir de l'onglet « Dispositions », vous pouvez

ajouter/retirer/personnaliser les éléments suivants : titres, légendes,
étiquettes, table de données, axe, quadrillage, paroi, etc.

Il est également possible de personnaliser les différents éléments

séparément (en les sélectionnant sur le graphique).

39
Cours Excel ESCM

Par exemple :

40
Cours Excel ESCM

Quant à l'onglet « Mise en forme », il est semblable à l'onglet «

Format » que nous avons déjà vu.

41
Cours Excel ESCM

MODIFIER LA SOURCE DU GRAPHIQUE

Pour changer la plage de données à prendre en compte pour le

graphique, cliquez sur « Sélectionner des données... ».

42
Cours Excel ESCM

Modifiez ensuite simplement la plage (en la sélectionnant sur la

feuille).

43
Cours Excel ESCM

V. Mises en forme conditionnelles

Comme son nom l'indique, la mise en forme conditionnelle (abrégée
en MFC) est une mise en forme appliquée sous certaines conditions.

Commençons avec quelques exemples de MFC "prêtes à l'emploi".

Sélectionnez les cellules cibles et choisissez la MFC « Supérieur à...

»:

44
Cours Excel ESCM

Entrez une valeur limite (ici, 50'000) et choisissez une mise en

forme.

La mise en forme est alors appliquée à toutes les valeurs

supérieures à 50'000.

En choisissant la MFC « 10 valeurs les plus élevées... », la mise en

forme sera appliquée sur les 10 valeurs les plus élevées de la plage
de cellules.

45
Cours Excel ESCM

Vous pouvez ensuite définir le nombre de valeurs à mettre en

évidence (ici, 5) ainsi que la mise en forme.

Les 5 valeurs les plus élevées sont alors mises en forme.

46
Cours Excel ESCM

Exemple avec les barres de données :

Exemple avec les nuances de couleur :

Exemple avec les jeux d'icônes :

47
Cours Excel ESCM

VI. Tableau croisé dynamique

Le tableau croisé dynamique (ou TCD) permet de synthétiser les
données d'une base de données de façon à obtenir les "croisements"
de données souhaités.

L'exemple suivant vous aidera à mieux comprendre quel est l'intérêt

d'utiliser un TCD et vous permettra de commencer à entrevoir le
potentiel de cette puissante fonctionnalité d'Excel.

La petite base de données de cet exemple est la suivante : lecon-VI-tableau-

[Link]

48
Cours Excel ESCM

VI.1. Insérer un tcd

Pour ajouter un TCD, sélectionnez la base de données (avec les en-

têtes) et cliquez sur "Tableau croisé dynamique" depuis l'onglet
"Insertion" :

49
Cours Excel ESCM

Pour insérer le TCD dans une nouvelle feuille du classeur, laissez les
valeurs par défaut et validez :

Un TCD vide est alors créé :

Cochez ensuite les champs à intégrer au TCD (dans ce cas, tous les
champs) :

50
Cours Excel ESCM

Vous pouvez remarquer que les données ont bien été synthétisées.

VI.2. Modifier un tcd

Le TCD généré par Excel n'est pas forcément celui attendu, vous
pouvez donc le modifier autant que nécessaire.

Commençons par renommer le champ "Somme de Nombre d'unités

achetées" en cliquant sur "Paramètres des champs de valeur" de ce
même champ :

51
Cours Excel ESCM

Renommez-le en "Quantité" :

Puis modifiez les paramètres du champ "Somme de Prix par unité",

sélectionnez le type de calcul "Moyenne" pour afficher le prix
moyen, renommez ce champ en "Moyenne" et modifiez le "Format de
nombres" pour afficher la moyenne au format monétaire :

52
Cours Excel ESCM

Les 2 champs de ce TCD ont bien été modifiés :

VI.3. Modifier l'ordre

Actuellement, ce TCD affiche les produits puis leur provenance :

53
Cours Excel ESCM

Pour inverser cet ordre et afficher les différents pays de provenance

puis les produits, modifiez l'ordre des lignes :

VI.4. Ajouter un filtre

Pour afficher les données uniquement en fonction d'un ou de

plusieurs pays, déplacez le champ "Provenance" dans "Filtres" pour

54
Cours Excel ESCM

ajouter un filtre vous permettant de choisir les pays dont il faut tenir
compte :

VI.5. Appliquer UN STYLE

Pour terminer, vous pouvez personnaliser le style du TCD depuis

l'onglet "Création" :

55
Cours Excel ESCM

56