14/01/2021

Cours d’Informatique :
Excel

• Hafsa EL OMARI
• ENCG - SETTAT
ENCG SETTAT
[Link]

OBJECTIFS

Découvrir et utiliser les outils de base, traitement de

texte, programme de présentation et tableur. Améliorer
ses connaissances en Word. Etre plus efficace et plus
rapide dans son travail, Réalisation de diapositives,
présentations et animations. Maîtriser les techniques de
manipulation des chiffres, de réalisation des tableaux, de
calculs et de graphiques Vous apprendrez à gagner en
efficacité et à rendre vos feuilles de calculs plus souples,
plus lisibles et pérennes.

1
 14/01/2021

E XC E L

Qu’est-ce qu’un tableur

• Tableur: logiciel permettant d’effectuer des calculs sur des nombres
organisés dans un tableau (feuille de calcul)

• Avantages:
– Visualisation synthétique des données sous forme de tableau
– Bonne adaptation pour les calculs répétitifs
– Génération aisée de graphiques et de rapports
– Grande base d’utilisateurs

• Principaux tableurs
– Office Excel de Microsoft
– StarOffice Calc de Sun
– OpenCalc de OpenOffice
– Lotus 123 de IBM
– KSpread de KOffice/Linux

2
 14/01/2021

EXCEL est un logiciel de Microsoft permettant la création, la manipulation et

l’édition de données organisées sous forme de tableaux.
Notion de classeur Dans Microsoft Excel, un classeur est le fichier dans
lequel vous travaillez et stockez vos données.
Chaque classeur peut contenir de nombreuses feuilles.
Vous pouvez organiser différentes sortes
d'informations au sein d'un même fichier.

Notion de feuille de calcul

Les feuilles de calcul du classeur servent à répertorier et analyser des données.
Vous pouvez renseigner ou modifier des données dans plusieurs feuilles de calcul et
effectuer des calculs à partir de données provenant de plusieurs feuilles.
Vous pouvez placer un graphique sur la même feuille de calcul que les données qui s'y
rapportent ou sur une feuille distincte.

Click droit sur l ’onglet de la

feuille concernée

Présentation Excel

Découverte des interfaces : Onglets, Groupe,

Ruban..

3
 14/01/2021

Boutons regroupés par

menu d'accès rapide groupe groupes au sein d'onglets du
onglet ruban
ruban

barre de formule
titre (ou entête) de colonne
titre (ou entête) de ligne
quadrillage

feuille de calcul

barre d'état
7

Menu principal accessible via l’onglet Fichier

4
 14/01/2021

PRINCIPAUX RÉGLAGES POUR L'AFFICHAGE

Onglet AFFICHAGE, bouton AFFICHER/MASQUER

RÉGLAGES À VÉRIFIER LORS DE LA PREMIÈRE UTILISATION

RÉGLAGES CONSEILLÉS POUR FACILITER LA MAÎTRISE

DE L'OUTIL
Accès aux options :
Onglet Fichier

puis en bas OPTIONS EXCEL

10

5
 14/01/2021

RÉGLAGES GÉNÉRAUX
Vérification du dossier courant contenant les documents
option ENREGISTREMENT DOSSIER PAR DÉFAUT
Activation de l'enregistrement automatique
option ENREGISTREMENT ENREGISTRER LES
INFORMATIONS...
RÉGLAGES COMPLÉMENTAIRES
Désactivation des corrections automatiques gênantes
Option VÉRIFICATION OPTIONS DE CORRECTION AUTOMATIQUE:
désactiver toutes les options dans les onglets CORRECTION AUTOMATIQUE et
MISE EN FORME AUTOMATIQUE LORS DE LA FRAPPE
Choix de la police de caractère utilisée a priori dans le classeur
option STANDARD UTILISER CETTE POLICE et TAILLE DE LA POLICE

11

REFERENCE DE LA CELLULU COURANTE

numéros de colonne

cellule courante

numéros de ligne

IDENTIFICATION DE CELLULE
Deux modes possibles : A1 ou L1C1
Exemple : C6 ou L6C3 pour la cellule en ligne n°6 et colonne n°3

Mode A1 très utilisé car historiquement le premier défini

Mode L1C1 plus puissant pour les formules et présenté ici
Choix par l'option FORMULES STYLE DE RÉFÉRENCE L1C1
Taille maximale : 1 048 576 lignes et 16 384 colonnes (lettres XFD)

12

6
 14/01/2021

FEUILLE ET CLASSEUR
FEUILLE DE CALCUL : un tableau de cellules identifié avec un nom visible en
bas à gauche ; ex. : Feuil1

CLASSEUR : un groupe de feuilles de calcul, un document d'Excel enregistré

dans un fichier identifié par un nom au suffixe .xls ou .xlsx fixé lors de
l'enregistrement : ENREGISTRER SOUS

ATTENTION ! A éviter : noms trop longs, espaces, accents !

dangereux : Mon menu idé[Link] ; conseillé : [Link]

13

LA SAISIE DE DONNÉES
LA SAISIE CORRESPOND AU REMPLISSAGE MANUEL DES CELLULES

COMMENT PROCÉDER ?
SAISIE DANS UNE CELLULE VIERGE
1) cliquer dans une cellule si ce n'est pas la courante,
2) taper directement, effacer si besoin avec RETOUR-ARRIÈRE,
3) terminer par la touche ENTRÉE

EXEMPLE :

14

7
 14/01/2021

TYPES DE DONNÉE ET FORMATS DE CELLULE

TROIS GRANDES FAMILLES DE TYPE DE DONNÉE
1) des valeurs numériques, exemple : 152
2) des dates ou des heures, exemple (heure) : 15:29
3) du texte, exemple : total
RECONNAISSANCE AUTOMATIQUE DU TYPE
le tableur analyse la donnée à la fin du remplissage de la cellule

DÉTERMINATION AUTOMATIQUE DU FORMAT DE CELLULE

la valeur saisie dans une cellule fixe son type ou « format » qui est une
présentation appliquée automatiquement par le tableur notamment un cadrage
automatique de la valeur dans la cellule :
- à droite si calculable (nombre, date ou heure)
- à gauche sinon (texte)
et quelques retouches éventuelles de la valeur saisie, comme par exemple l'élimination
du signe plus (« + ») mis devant un nombre

15

TYPES DE DONNÉES

LES PRINCIPALES CARACTÉRISTIQUES DES TYPES DE DONNÉES

VALEURS NUMÉRIQUES

ordinaire (espace pour millier, virgule pour partie décimale)

scientifique (à puissance de 10 introduite par « E » ou « e »)
utiliser le symbole « - » dans le cas d'une valeur négative
fraction (notée avec « / », précédée de « 0 » et un espace)
pourcentage (terminé par le symbole « % »)
monétaire (avec le symbole monétaire « € »par exemple ou la touche ALTGR+E)
exemples : 3 -35,28 +153,5 1,535e+02 50% 8€ 0 7/11

16

8
 14/01/2021

DATE ET-OU HEURE

Date avec nom ou numéro de mois, séparation par « / » ou « - » Année
complète ( 4 chiffres) ou abrégée (2 derniers chiffres)
date partielle possible : sans l‘année ou sans le jour
heures et minutes et-ou secondes séparées par le symbole « : »
date / heure courantes en raccourci au clavier : CTRL+; / CTRL+:
exemples : 19/9 19-sept-1999 19/09/99 fév/2010 16:42

TEXTE

Toute suite de caractères (au maximum environ 32 700 signes)

pour éviter une confusion avec un autre type, préfixer par « ' » : cas
d'un libellé composé de chiffres (« 05005 ») ou débutant par un
symbole mathématique (« - », « = », etc)
pour passer à la ligne au sein d'une cellule : ALT+ENTRÉE

17

MANIPULATIONS ÉLÉMENTAIRES DE CELLULES

DÉPLACEMENT DANS LA FEUILLE
AVEC LA SOURIS
déplacer le pointeur (avec défilement si besoin) et cliquer
AVEC LE CLAVIER
cellules voisines :
écran suivant ou précédent : PAGESUIV ou PAGEPREC
début ou fin de plage de valeurs/ligne : CTRL+ ou CTRL+
début ou fin de plage de valeurs/colonne : CTRL+ ou CTRL+
première ou dernière cellule utilisées : CTRL+ ou CTRL+FIN
EVITER LES DÉPLACEMENTS PAR DÉFILEMENT RÉPÉTITIF
efficacité accrue et confort visuel maximal
exemple : PAGESUIV au lieu de multiples

18

9
 14/01/2021

SÉLECTION
CELLULE : clic à l'intérieur
LIGNE OU COLONNE : clic sur le numéro de ligne ou de colonne
MAJ+ESPACE / CTRL+ESPACE : ligne(s) / colonne(s) de la sélection
FEUILLE : clic sur le carré à l'origine des lignes et colonnes
BLOC DE CELLULES : faire glisser la souris sur les cellules, ou, clic sur la
première cellule et MAJ+CLIC sur la dernière, ou, déplacer avec les touches en
maintenant MAJ enfoncée
BLOCS NON ADJACENTS : sélectionner le premier bloc puis, les autres blocs en
maintenant CTRL enfoncée
Sélection par extension : au préalable, appuyer sur la touche F8

POUR ANNULER LA SÉLECTION, SIMPLE CLIC EN DEHORS

19

CAS PARTICULIERS DE DONNÉES : BLOC

CAS DE DONNÉES OCCUPANT UN GROUPE RECTANGULAIRE DE CELLULES

SAISIE EN BLOC

1) Sélectionner le bloc composé des cellules adjacentes

2) Taper la 1ère valeur puis ENTRÉE
3) Passage automatique à la cellule suivante dans le bloc
4) Remplir ainsi le bloc en tapant les valeurs séparées par ENTRÉE

20

10
 14/01/2021

CALCUL AUTOMATIQUE SUR LE BLOC SÉLECTIONNÉ

Calcul automatique sur les valeurs

sélectionnées
Résultat affiché en bas dans la
zone des indicateurs de la barre
d'état

Possibilité de changer le
type de calcul : clic-droit sur
la barre d'état puis choisir
dans le menu contextuel

Exemple : suppression du
calcul de la moyenne et ajout
de celui du maximum
VRAIMENT TRÈS PRATIQUE POUR MANIPULER DES BLOCS DE DONNÉES

21

CAS PARTICULIERS DE DONNÉES : SÉRIES

CAS DE DONNÉES RÉPÉTITIVES OU RÉGIES PAR UNE LOI
MATHÉMATIQUE OCCUPANT UNE SUITE DE CELLULES
VOISINES EN LIGNE OU EN COLONNE

DIFFÉRENTS TYPES DE SÉRIES

CAS DE LA RÉPÉTITION D'UNE CONSTANTE

placer le texte ou le nombre dans la cellule initiale d'amorçage

CAS D'UNE SÉRIE NUMÉRIQUE

série a priori linéaire (ou « arithmétique », à « pas » constant) placer au-moins 2

premières valeurs dans les cellules d'amorçage

22

11
 14/01/2021

CAS D'UNE SÉRIE CHRONOLOGIQUE

série de dates successives (jour, mois ou année) ou à pas constant

placer 1 ou 2 (cas à pas) dates dans la(es) cellule(s) d'amorçage

lundi Mardi mercredi jeudi vendredi

Décembre Janvier Février Mars Avril
01/01/2007 01/01/2008 01/01/2009 01/01/2010 01/01/2011
15/04/2011 30/04/2011 15/05/2011 30/05/2011 14/06/2011

23

REMPLISSAGE AUTOMATISÉ
MANIPULATION AVEC LE POINTEUR DE LA SOURIS :
a) Sélectionner une ou plusieurs cellules d'amorçage,
b) Placer le pointeur sur le coin inférieur droit de la sélection jusqu'à
l'apparition du signe « + » de la poignée de recopie
c) Faire glisser cette poignée de recopie sur les cellules à remplir
OU DANS LE GROUPE EDITION DE L'ONGLET ACCUEIL :

a) Sélectionner la(les)
cellule(s) d'amorçage et les
cellules à remplir
b) Menu du bouton
c) Choisir le sens en cas de
répétition ou sinon SÉRIE
cas de répétition cas de série
VRAIMENT TRÈS PRATIQUE POUR ÉVITER DES ERREURS DE SAISIE

24

12
 14/01/2021

EDITION
COMMENT MODIFIER UNE CELLULE ?

annulation validation valeur sur la barre de formule

valeur dans sa cellule

1) Sélectionner la cellule et cliquer sur la barre de formule ou presser la
touche F2 ou double-cliquer dans la cellule
2) Modifier directement la valeur dans la cellule ou sur la barre, se servir si
besoin des touches de déplacement ou de la souris, utiliser les touches
SUPPR ou RETOURARRIÈRE pour effacer
3) Valider avec la touche ENTRÉE ou le bouton ou annuler avec la
touche ECHAP ou le bouton

25

COMMENT DÉPLACER UNE(DES) CELLULE(S) ?

A l'aide de la souris :
1) sélectionner une ou plusieurs cellules
2) placer le pointeur de la souris sur un côté de la sélection ( )
3) faire glisser et déposer la sélection à sa nouvelle place
ou aussi, à l'aide du presse-papiers (clic-droit COUPER / COLLER)

COMMENT EFFACER DES CELLULES ?

en fait on peux effacer le contenu avec ou sans le format associé, ou bien détruire
la cellule (réorganisation par décalage des cellules voisines)
sélectionner la cellule puis respectivement :
touche SUPPR ou le bouton à l'onglet ACCUEIL et le groupe EDITION et
soit EFFACER LE CONTENU, soit EFFACER TOUT (donnée et format)
ou le bouton aussi à ACCUEIL CELLULES (réorganisation)

26

13
 14/01/2021

COMMENT INSÉRER OU SUPPRIMER UNE LIGNE OU COLONNE ?

sélectionner la ligne ou colonne à supprimer / l'endroit de l'insertion

puis à l'onglet ACCUEIL et le groupe EDITION bouton /
ou aussi, par clic droit sur le titre de ligne ou de colonne

EN CAS D'ERREUR, ANNULER IMMÉDIATEMENT AVEC LE BOUTON

(OU CTRL+Z)

27

MISE EN FORME
NE PAS OUBLIER DE SÉLECTIONNER AVANT TOUTE MISE EN FORME
ÉLÉMENTS DU FORMAT D'UNE CELLULE :
NOMBRE : type (texte, entier, décimal, monétaire, pourcentage, date etc.) et
présentation de la valeur (séparation des milliers, nombre de décimales)
POLICE : famille, style (gras, italique, souligné), taille, couleur
ALIGNEMENT : horizontal, vertical, orientation, renvoi à la ligne
automatique (en cas de débordement de colonne), fusion (des cellules
sélectionnées)
BORDURE : encadrement complet ou partiel
REMPLISSAGE : couleur ou trame de fond
PROTECTION : verrouiller et protéger les cellules, masquer les formules

MISE EN FORME VIA LA BOÎTE DE

DIALOGUE :
Utiliser Clic-droit puis commande
FORMAT CELLULE

28

14
 14/01/2021

MISE EN FORME VIA LE RUBAN

Onglet ACCUEIL

MISE EN FORME VIA LA MINI BARRE D'OUTILS

cas de clic-droit sur une cas de sélection de contenu

sélection de cellule
EN CAS D'ERREUR, ANNULER IMMÉDIATEMENT AVEC LE BOUTON (OU CTRL+Z)

29

MISE EN FORME (SUITE)

CAS PARTICULIERS DE MISE EN FORME
DUPLICATION DE FORMAT
1) sélectionner la cellule avec le format original
2) cliquer sur le bouton à la brosse ( ) dans l'onglet ACCUEIL
3) faire glisser le pointeur à la brosse sur les cellules à mettre en forme

LIGNE ET COLONNE : BOUTON DANS L'ONGLET ACCUEIL

LARGEUR / HAUTEUR DE COLONNE / LIGNE : à une valeur particulière
AJUSTER LA LARGEUR / HAUTEUR... : exactement selon le contenu le + long
Réglages par manipulation de la bordure du titre de colonne / ligne

faire glisser la bordure pour une largeur / hauteur particulière

« ####### » apparaît quand il devient impossible d'afficher la valeur

30

15
 14/01/2021

Mise en forme rapide avec des styles ou des formats

prédéfinis
⚫ Microsoft Excel propose des styles pour mettre en forme
des nombres en tant que monnaie, pourcentage ou à
l'aide de la virgule comme séparateur des milliers.
⚫ Pour mettre en forme une liste entière vous pouvez lui
appliquer un modèle de tableau prédéfini :

31

Faire des commentaires ...

• A partir de l’onglet Révision, vous pouvez insérer

des commentaires. Si un commentaire existe
déjà pour une cellule, vous pouvez effectuer des
modifications.

• Pour afficher les commentaires,

sélectionner Afficher tous les Commentaire...

32

16
 14/01/2021

TEXTE SUR PLUSIEURS LIGNES

plusieurs possibilités pour un contenu de cellule sur plusieurs lignes :

1) Renvoi à la ligne automatique

en alignement de format de cellule

2) Renvoi à la ligne manuel

frappe de ALT+ENTRÉE dans le
texte
avec la possibilité d'agrandir la barre
de formule via le bouton à droite

33

MISE EN PAGE
IMPRIMER, APERÇU AVANT IMPRESSION

Bouton MISE EN PAGE

en-tête personnalisé

34

17
 14/01/2021

RÉGLAGE DE LA MISE EN PAGE POSSIBLE

AUSSI VIA L'ONGLET MISE EN PAGE (CLIC SUR
LE BOUTON EN BAS À DROITE)

35

FORMATS DE CELLULE ET TYPES DE DONNÉE

UNE CELLULE PEUT CHANGER DE FORMAT

DÉFINITION DU FORMAT D'UNE CELLULE

Indéfini a priori (standard) puis fixé par la 1 ère valeur entrée

détermination automatique du format à partir de la valeur,
format conservé pour une nouvelle valeur si autre que standard
Possibilité de fixation du format manuellement
soit par la commande FORMAT CELLULE (Groupe NOMBRE), soit via
un bouton de la barre de mise en forme (000 etc.)

36

18
 14/01/2021

CONVERSIONS ENTRE LES PRINCIPAUX TYPES

Mécanisme de conversion s'applique automatiquement en cas de changement du format
Vers ↗ Numérique Date et heure Texte
Numérique date correspondant au texte constitué par les
nombre compté en jours symboles du nombre
écoulés depuis le 1er janvier Exemple :
1900, avec les heures -12,6 → -12,6
correspondant à la fraction
du jour égale à la partie
décimale
Exemples :5235→1/5/1914
0,5→ 12:00
Date et nombre de jours depuis texte constitué par les
heure le 1er janvier 1900 jusqu'à symboles du nombre
la date, partie décimale correspondant à la date
pour les heures et l'heure
exprimées en portion de Exemples : 12:00 →0,5
jour 1/5/1914 → 5235
Exemples : 12:00 →0,5
1/5/1914 → 5235
Texte texte texte
Cas du pourcentage correspond à un nombre entre 0 et 1 ; exemple : 50 % ↔0,5

37

MANIPULATIONS DE FEUILLES DU CLASSEUR

UN CLASSEUR DE DONNÉES EST ORGANISÉ EN FEUILLES DANS EXCEL
MENU CONTEXTUEL ASSOCIÉ À UNE FEUILLE
clic-droit sur l'onglet d'identification de la feuille en bas de la fenêtre

PRINCIPALES OPÉRATIONS

Insertion : bouton ou, via le menu contextuel,

INSÉRER... puis choisir l'onglet GÉNÉRAL et l'icône
FEUILLE
Renommage : double-clic sur l'onglet d'identification
ou, au menu contextuel, RENOMMER ; ATTENTION
aux symboles dangereux (n'utiliser que des lettres sans
accent, chiffres et traits de soulignés)
Suppression : via le menu contextuel SUPPRIMER,
puis confirmer Copie : commande EDITION
DÉPLACER OU COPIER UNE FEUILLE ou, via le
menu contextuel DÉPLACER OU COPIER, puis dans la
boîte de réglages, choisir la position d'insertion et cocher
CRÉER UNE COPIE

38

19
 14/01/2021

ATTENTION : renommer la copie créée afin d'y

éliminer espaces et parenthèses introduits
Déplacement : similaire à la copie, sans cocher
CRÉER UNE COPIE ;
autre possibilité en faisant glisser son onglet avec le
pointeur de la souris

Sélection de plusieurs feuilles : soit toutes via

SÉLECTIONNER TOUTES LES FEUILLES au
menu contextuel, soit par cumul à la feuille courante
via MAJUSCULE+CLIC sur leurs onglets;
signalement « [GROUPE DE TRAVAIL] » dans la
barre de titre de la fenêtre d'Excel

39

E XC E L : F O R M U L E S D E
C A LC U L E T F O N C T I O N S

40

20
 14/01/2021

FORMULES DE CALCUL

QU'EST-CE QU'UNE FORMULE ?

Définition d'un calcul automatisé
RÉFÉRENCES DE CELLULES

Différents modes de désignation des cellules dans une formule

Utilisation de fonctions
Mise en œuvre de calculs mathématiques ou financiers
Simulation
Quelle valeur choisir pour obtenir tel résultat à un calcul ?

41

INTRODUCTION AUX FORMULES

Comment obtenir la moyenne des note de chaque étudiant à partit des données du
tableau suivant ?
Nom Prénom Note C1 Note C2 Note C3 Moyenne
Aazmi Lamiae 12 10 11
Ghafour Mohammed 14 15 16
Maati Karim 3 7 5
Mousalim Hamid 10 9 8

Ici, la valeur est variable et dépend des notes des trois contrôles :
Moyenne= (Note C1+ Note C2 + Note C3)/3
COMMENT DÉFINIR CETTE FORMULE DANS LA CELLULE ASSOCIÉE ?
en traduisant cette formule mathématique pour Excel :
Taper le symbole = dans la cellule en L2C6 ou F2
Nom Prénom Note C1 Note C2 Note C3 Moyenne
Aazmi Lamiae 12 10 11 =
Ghafour Mohammed 14 15 16
Maati Karim 3 7 5
Mousalim Hamid 10 9 8

42

21
 14/01/2021

DÉFINITION D'UNE FORMULE

LE CALCUL EST DÉFINI PAR UNE FORMULE MATHÉMATIQUE
LA CELLULE CONTIENT LA FORMULE MAIS AFFICHE SON RÉSULTAT
LA NOTATION D'UNE FORMULE DÉBUTE TOUJOURS PAR LE SYMBOLE « = »
FORMULE COMPOSÉE DE VALEURS, RÉFÉRENCES DE CELLULES, FONCTIONS, ...
JEU DE FONCTIONS MATHÉMATIQUES, FINANCIÈRES, STATISTIQUES, ...

EXEMPLES
=A2*2 .................................. de la valeur en A2 multiplier par 2
=A4^3 .................................. valeur en A4 à la puissance 3
=PUISSANCE(A1;3) .......... valeur en A1 à la puissance 3
=MAX(A1;A2) .................... maximum des valeurs en A1 et A2
=SOMME(A1:A10) ............. somme des valeurs de A1 à A10
=MOYENNE(A2:I2) ............ moyenne des valeurs de A2 à I2
=ECARTTYPE(A2:I2) ........ écart-type des valeurs de A2 à I2
=AUJOURDHUI() ................ date du jour

UNE FORMULE DÉBUTE TOUJOURS PAR LE SYMBOLE « = »

43

Les formules - références

• Les formules font référence à une autre cellule en utilisant ses
coordonnées ou un nom défini par l’utilisateur.
• Il y a différentes façon de faire une référence:
1. Référence absolue = utilisation des coordonnées absolues par
rapport à l’origine de la feuille :
$lettre_de_colonne$numéro_de_ligne ex: $B$3
2. Référence relative = utilisation des coordonnées par rapport à la
cellule qui contient la formule (cellule de référence) : la position
relative est masquée, on visualise une référence absolue sans le
signe $ , ex: A8.
• !!! Attention: lorsqu’on copie une cellule contenant une
référence relative, c’est la position relative qui est copiée.
3. Référence mixte = mélange d’une référence absolue et relative ex:
B$3
4. Référence nommée = un nom est spécifiquement donné à une
cellule et les formules peuvent faire référence directement à ce
nom

44

22
 14/01/2021

Cellule nommée

Il existe une autre méthode de mise en absolu des

références de cellules.
Excel autorise la création d'étiquettes pour une cellule ou un
groupe de cellules. Les étiquettes utilisent des références
absolues par défaut. Par exemple, on peut définir l'étiquette
TVA pour la cellule H106 :

45

Exercice pour réaliser une facture simple

La réalisation d’une facture simple permet de comprendre aisément l’intérêt et le
fonctionnement d’Excel.
(Remarque : cette facture sera donc simple et n’intégrera pas plusieurs taux de
TVA et autres complexités inutiles pour une première approche)
Résultat final :

Réaliser la première formule et

validez : (à l’aide de la souris)

Résultats

46

23
 14/01/2021

Recopiez la formule vers le bas, jusqu’à la ligne 12 :

Ne pas s’occuper du symbole qui est apparu en bas à droite :

Nous allons ajouter les libellés des trois

cellules :

Puis nous réalisons la formule de la TVA :

Puis la formule du TTC :

47

LES FONCTIONS

48

24
 14/01/2021

Utilisation des fonctions de calcul

• Pour les calculs « complexes », on peut utiliser

une fonction automatique
• Une fonction automatique permet d’effectuer
un calcul prédéfini à partir de différents
« arguments » qui sont les paramètres
d’utilisation de cette fonction
• Chaque fonction a une syntaxe et arguments
spécifiques.
• Exemple: la fonction SOMME
=SOMME(nombre1;nombre2;...)

49

Utilisation des fonctions de calcul

• La syntaxe d’une fonction est toujours la même :

=Fonction (arg1;arg2;…)

Un nom de fonction qui

désigne la fonction à Une série d’arguments
entre parenthèses,
appliquer
séparés par des points-
virgules qui permettent
l’utilisation de la
fonction

50

25
 14/01/2021

Utilisation des fonctions de calcul

• Avec la fonction SOMME la syntaxe est :

= SOMME ( nombre1;nombre2 ;…)

Additionne tous les

nombre1, nombre2, ...
nombres contenus dans représentent de 1 à 30 arguments
une plage de cellules dont vous voulez calculer la valeur
totale ou somme

51

Utilisation des fonctions de calcul

• Les fonctions sont accessibles dans Excel de
différentes façons:
– En saisie directe au clavier
– A partir de l’onglet Formules
« Insérer une Fonctions »

– Par la barre de formule (en cliquant sur )

– Par la barre d’outils Standard Onglet Accueil

52

26
 14/01/2021

Fonctions Excel (par catégorie)

Excel propose différentes catégories de fonctions pour satisfaire un
large panel d'utilisateurs.
Fonctions mathématiques et trigonométriques
Fonctions statistiques
Fonctions logiques
Fonctions de recherche et de référence
Fonctions financières
Fonctions de base de données
Fonctions de date et d’heure
Fonctions de compatibilité
Fonctions Cube
Fonctions d’ingénierie
Fonctions d’information
Fonctions de texte
Fonctions définies par l’utilisateur installées avec les compléments
Fonctions web

53

Les fonctions les plus fréquemment utilisées

les fonctions les plus utilisées :

Fonctions Mathématiques et trigonométrie

Fonction Description
[Link].B Sur Excel, pour obtenir un nombre entier aléatoire entre deux bornes spécifiées.
ORNES Exemple : [Link](1;42) renvoie un nombre aléatoire entre 1 et 42.
Permet d’arrondir un nombre, selon le nombre de chiffres après la virugle spécifié.
Exemple : ARRONDI(42,1337;2) renvoie la valeur 42,13. Variantes : [Link] permet d’arrondir à la valeur
ARRONDI
supérieure, [Link] permet d’arrondir à la valeur inférieure, et [Link] permet d’arrondir
à un multiple spécifique (au 0,5 plus proche etc.).
Met en évidence le plus grand commun diviseur de plusieurs valeurs entières.
PGCD
Exemple : PGCD(12;30) va renvoyer 6, car c’est le plus grand diviseur commun.
Permet d’utiliser la valeur π dans une cellule sur Excel.
PI
Exemple :ARRONDI(PI();2) = 3,14.
PRODUIT Multiplie les données. Exemple : PRODUIT(2;2;2) = 8.
Calcule la puissance d’une donnée.
PUISSANCE Exemple : PUISSANCE(4;2) = 4² = 16. Vous pouvez utiliser un autre paramètre à la place 2 pour calculer des
puissance complexes.
Permet de remplacer des chiffres arabes en chiffres romain. Pas très utile, mais on ne sait jamais !
ROMAIN
Exemple : ROMAIN(2013;0) = MMXIII.
Fonction très connue, permettant de connaître la somme d’une série de données.
SOMME
Exemple : SOMME(13;29) = 42.
[Link] Additionne les cellules spécifiées si elles répondent à un critère donné.
[Link] Ajoute les cellules d’une plage qui répondent à plusieurs critères.
SOMMEPROD Multiplie les valeurs correspondantes des matrices spécifiées et calcule la somme de ces produits.
[Link]
Renvoie la somme des carrés des arguments….

54

27
 14/01/2021

Fonctions Logique
Fonction Description
OU Renvoie la valeur VRAI si l’un des critères est exact. Exemple : OU(1+1=2;1+1=3) est vrai parce que 1+1=2
Permet de tester plusieurs conditions et vérifier qu’elles sont vraies. Exemple : ET(1337-
ET
42=1295;1295+42=1337) va renvoyer la valeur VRAI, car 1337-42=1295 et 1295+42=1337.
Permet de tester une condition et renvoyer une certaine valeur si c’est vrai, une autre si c’est faux. Exemple
SI : SI(moyenne(A1:A15) »j’ai la moyenne »; »je n’ai pas la moyenne ») permet de savoir si on a la moyenne,
quand les notes sont comprises entre les cellules A1 et A15.
Fonctions Recherche et Matrices
Permet d’obtenir le numéro de colonne. Exemple : COLONNE(A42)renvoie la valeur 1, car la colonne A est la
COLONNE première colonne. Variante : COLONNE() renvoie la valeur correspondante à la colonne où se situe la formule
Excel.
Dans certains cas, les formules de recherche peuvent sauver des vies. Exemple : RECHERCHE(42;A:A;B:B) va
RECHERCHE chercher la valeur 42 dans la colonne A, puis retranscrire son équivalent dans la colonne B (sur la même
ligne). RECHERCHE permet également de chercher dans une matrice ou un vecteur.
Même principe que RECHERCHE, mais pour chercher des valeurs selon la première ligne d’une matrice.
RECHERCHEH Exemple : RECHERCHEH(« ville »;A1:D10;2;VRAI) permet de renvoyer la deuxième ligne d’une colonne dont la
première ligne est ville au sein d’un tableau.
Même principe que RECHERCHEH, mais pour chercher des valeurs selon la première colonne d’une
RECHERCHEV matrice. Exemple : RECHERCHEV(« ville »;A1:D10;2;VRAI) permet de renvoyer la deuxième colonne d’une
ligne dont la première colonne est ville au sein d’un tableau.
Permet de transposer une matrice (transforme les lignes en colonnes et vice-versa). Pensez simplement à
TRANSPOSE
valider votre formule en utilisant Ctrl+Maj+Entrée, puisqu’il s’agit d’un calcul matriciel.
La fonction EQUIV recherche un élément spécifique dans une plage de cellules, puis renvoie la position
EQUIV relative de l’élément dans la plage. Par exemple, si la plage A1:A3 contient les valeurs 5, 25 et 38, la formule
=EQUIV(25;A1:A3;0) renvoie le chiffre 2 étant donné que 25 est le deuxième élément dans la plage
Renvoie une référence décalée par rapport à une référence donnée.
Sa syntaxe : DECALER(réf, lignes, colonnes, [hauteur], [largeur]). La fonction DECALER peut être utilisée avec
DECALER toutes les fonctions exigeant une référence comme argument. Par exemple, la formule
SOMME(DECALER(C2;1;2;3;1)) calcule la valeur totale de la plage couvrant 3 lignes sur 1 colonne et se situant
1 ligne en dessous et 2 colonnes à droite de la cellule C2

55

Fonctions Date et Heure

Fonction Description
Pour obtenir l’année correspondante à une date plus précise. Exemple : ANNEE(« 09/10/2018 ») renvoie
ANNEE
l’année 2018.
MOIS Même principe, pour obtenir le mois. Exemple : JOUR(« 09/10/2018 »)renvoie la valeur 10.
JOUR Même principe, pour obtenir le jour. Exemple : JOUR(« 09/10/2018 ») renvoie la valeur 9.
Même principe, mais cette fonction permet de connaître le jour de la semaine associée à la valeur.
JOURSEM Exemple : JOURSEM(« 09/10/2013 »;2) renvoie le chiffre 3, car le mercredi est le troisième jour de la semaine.
Pour commencer la semaine le lundi, il faut utiliser le paramètre 2 (comme dans l’exemple ci-dessus).
Utilisez cette fonction pour rechercher un élément dans une plage de cellules, puis renvoyer la position
HEURE
relative Même principe, pour obtenir l’heure. Exemple : HEURE(« 12:30:40 »)renvoie 12.
MINUTE Même principe, pour obtenir les minutes : Exemple MINUTE(« 12:30:40 ») renvoie 30.
Utilisez cette fonction pour renvoyer le numéro de série séquentiel qui représente une date particulière. Cette
fonction est particulièrement utile lorsque l’année, le mois et le jour sont présentés par formules ou
DATE références de cellules. Par exemple, une feuille de calcul peut contenir des dates dans un format non reconnu
par Excel, tel que AAAAMMJJ.
Utilisez la fonction DATEDIF pour calculer le nombre de jours, de mois ou d’années qui séparent deux dates.
JOURS Utilisez cette fonction pour renvoyer le nombre de jours qui séparent deux dates.
Comme son nom l’indique, cette fonction permet d’afficher la date du jour. Exemple : AUJOURDHUI() pour
AUJOURDHUI afficher la date du jour. Vous pouvez également afficher le lendemain en utilisant AUJOURDHUI()+1 et ainsi de
suite.
MAINTENANT Encore plus précise, cette fonction permet d’obtenir la date exacte (année, mois, jour, heure, minute,
seconde). L’affichage dépend du format de la cellule. Exemple : MAINTENANT() pour afficher la date et l’heure
exactes.
[Link] Permet d’obtenir le dernier jour du mois en cours. Exemple : [Link](« 09/10/13 »;0) retourne le 31 octobre
2013. Vous pouvez modifier le 0 en +1 pour obtenir le dernier jour du mois suivant, ou -1 pour obtenir le
dernier jour du mois précédent et ainsi de suite.
[Link] Permet de connaître la fraction d’une année qui correspond à nombre de jours donnés.
NEE Exemple : [Link](« 01/01/98″; »01/01/99 »;1)va renvoyer 1, car cette durée correspond à une
année entière. La dernière variable permet de choisir comment sont comptés les jours : 1 pour la valeur réel,
2 pour une base 360, 3 pour une base 365 etc

56

28
 14/01/2021

Suites Fonctions Date et heure

Comme son nom l’indique, cette fonction permet de compter le nombre de jours ouvrés.
[Link] Exemple : [Link](« 09/10/2013″; »31/12/2013 ») renvoie le nombre 60, car il y reste 60 jours
VRES ouvrés avant la fin de l’année. Vous pouvez ajouter des jours fériés de cette façon
: [Link](« 09/10/2013″; »31/12/2013″; »25/12/2013 »).
Renvoie le numéro de semaine correspond à une date. Exemple : [Link](AUJOURDHUI();2) renvoie le
[Link] nombre 41, car nous sommes à la semaine 42. Le paramètre 2 permet d’indiquer que les semaines
commencent le lundi.
Fonctions de traitement de texte
Si une chaîne de caractère comporte un nombre et du texte, seul le nombre est retranscrit. Pratique pour
CNUM
supprimer les € ! Exemple : CNUM(« 42€ ») = 42.
Une fonction très utile permettant d’assembler plusieurs chaînes de caractères. Exemple : CONCATENER(« Le
CONCATENER blog du Modérateur »; « , » ; « c’est super ») permet d’obtenir : Le blog du Modérateur, c’est super. Vous
pouvez bien évidemment rapatrier les textes de certaines cellulles en utilisant leur code : A42, B1337 etc.
Pour convertir un texte en majuscules sur Excel. Exemple : MAJUSCULE(« Excel, c’est génial ») renvoie la
MAJUSCULE
valeur suivante : EXCEL, C’EST GÉNIAL.
Permet de compter le nombre de caractères contenus dans une chaîne de texte, espaces compris. Exemple
NBCAR : NBCAR(« Neque porro quisquam est qui dolorem ipsum ») renvoie le nombre 42, puisque cet extrait
comporte 42 caractères, espaces compris.
Transforme la première lettre de tous les mots d’une chaîne de caractères. Exemple : NOMPROPRE(« le blog
NOMPROPRE
du modérateur ») permet d’obtenir Le Blog Du Modérateur.
Permet de remplacer les caractères d’une plage de données. Exemple =REMPLACER(« coucou »;4;3;
« leuvre ») va remplacer coucou par couleuvre. Le second paramètre (4) permet de commencer le
REMPLACER
remplacement au quatrième caractère, le troisième paramètre (3) correspond au nombre de caractères
remplacés.
Souvent plus pratique que la fonction REMPLACER, elle permet de rechercher une valeur puis remplacer les
SUBSTITUE occurrences par une autre valeur. Exemple : SUBSTITUE(D1;6;10) va renvoyer le chiffre 10, si la cellule D1 est
égale à 6. Ceci fonctionne aussi avec les textes

57

Fonctions statistiques
Vous l’aurez compris, cette fonction permet de calculer l’écart-type d’une population.
ECARTYPE
Exemple : ECARTYPE(42;1337;42) renvoie la valeur 52,54.
Permet de connaître la valeur la plus grande d’une matrice de données.
[Link]
Exemple : [Link](A:B;1) renvoie la valeur la plus grande des colonnes A et B. Vous pouvez utiliser 2
UR
plutôt que 1 pour connaître la seconde valeur la plus grande et ainsi de suite.
À peu près le même principe, mais : la fonction renvoie toujours la valeur maximale, et vous pouvez ajouter
MAX un argument supplémentaire. Exemple : MAX(A:B;42) permet d’obtenir la valeur maximale des colonnes A et
B ou 42 si ce nombre est supérieur.
C’est l’inverse de [Link]. Exemple : [Link](A:B;1) va renvoyer la plus petite valeur située
[Link]
dans les colonnes A et B.
MIN C’est exactement l’inverse de MAX. Exemple : MIN(1337;42) permet d’obtenir la valeur 42.
Permet de calculer la médiane d’une série de données sur Excel.
MEDIANE
Exemple : MEDIANE(10;40;30) renvoie la valeur 30, puisqu’il s’agit de la médiane de la série.
Permet de connaître le nombre présentant le plus d’occurences dans une série de données.
MODE
Exemple : MODE(42;1337;42) renvoie le nombre 42.
Permet de calculer la moyenne d’une série de données. Exemple : MOYENNE(42;1337) renvoie le nombre
MOYENNE 689,5. Vous pouvez également calculer une [Link], une [Link], une
[Link] et une [Link] (arithmétique).
Très utilisée, cette fonction permet de compter le nombre de cellules selon certains critères. Exemple
: [Link](A:B; « Rennes ») permet de compter le nombre de cellules qui contiennent le mot « Rennes » au sein
[Link]
des colonnes A et B. [Link] permet de compter le nombre de cellules répondant favorablement à
l’ensemble des critères.
Cette fonction compte le nombre de cellules vide au sein d’une plage donnée. Exemple : [Link](A) renvoie le
[Link]
nombre 1048576 si la colonne A est entièrement vide.
Une fonction simple qui permet d’obtenir un résultat parfois complexe. Vous devez spécifier une matrice de
données indépendante, une matrice de données dépendante, et le point de donnée pour lequel vous
PREVISION souhaitez connaître la valeur prévisionnel. Exemple : si pour chaque valeur d’une série A, les valeurs
correspondantes de la série B sont égales à 10 fois la valeur de la série A, la fonction PREVISION(4,2;B;A) est
égale à 42.

58

29
 14/01/2021

Suite Fonctions statistiques

Permet de connaître le rang d’une valeur dans une série de données. Exemple : si une matrice comporte les
RANG chiffres 2,4,6,8 et 10, RANG(6;matrice;1) va renvoyer le chiffre 3, car le il s’agit du troisième chiffre dans
l’ordre croissant (compte-tenu du paramètre 1).

59

Utilisation des fonctions de calcul

• Utilisation de l’assistant (3 étapes)

– Sélection de la fonction dans la liste

– Saisie des différents arguments de la fonction

(un par ligne)
• Soit directement au clavier
• Soit par sélection d’une ou plusieurs cellules (plage)
en tant qu’argument grâce au bouton

– Validation de la formule

60

30
 14/01/2021

Utilisation des fonctions de calcul

• Utilisation de l’assistant (Exemple de la fonction MOYENNE)

61

La fonction SOMME
• Les arguments de cette fonction sont soit des valeurs
numériques, soit des références à des cellules ou à des plages
de cellules.
Exemples : Résultat
=SOMME(1;2;3) 6
=SOMME(C2;C7) 30
=SOMME(C2:C7) 72

Soit un tableau présentant les résultats de 4 personnes sur les quatre premiers mois
de l'année. Nous allons calculer la somme des résultats par personne, par mois et la
somme globale.

62

31
 14/01/2021

La fonction SOMME

Utilisation du bouton

Sur une plage sélectionnée au préalable: La

fonction SOMME est ajoutée Sur une plage NON sélectionnée: La
en bas de chaque colonne fonction SOMME est ajoutée
(valeurs sommées en colonnes) automatiquement en bas de chaque
colonne. Vous pouvez modifier la zone
sommée à la souris le cas échéant

Remarque : si les valeurs sommées sont en ligne la

somme se place sur la cellule la plus à droite de
chaque ligne

63

La fonction MOYENNE
• Cette fonction a le même type d’arguments que la
fonction SOMME. Elle permet d’obtenir la moyenne
des différentes valeurs saisies comme argument.
Exemples :
Résultat
=MOYENNE(1;2;3) 2
=MOYENNE(C2;C7) 15
=MOYENNE(C2:C7) 12

Soit un tableau présentant les notes obtenues à trois épreuves. L'objectif est de
calculer la moyenne générale de chaque personne.

64

32
 14/01/2021

Les fonctions MIN et MAX

• La fonction MIN donne la valeur numérique minimale parmi les valeurs
données en argument (valeurs numériques ou valeurs contenues dans
une ou plusieurs cellules)
• La fonction MAX donne la valeur numérique maximale parmi les valeurs
données en argument (valeurs numériques ou valeurs contenues dans
une ou plusieurs cellules)
Exemples : Résultat
=MIN(14;5;6;9) 5
=MAX(14;5;6;9) 14
=MIN(C2:C7) 7
=MAX(C2:C7) 17
=MAX(C3;C5) 9

65

La fonction NB

• Cette fonction compte le nombre de valeurs numériques

contenues dans les cellules concernées, c’est un
COMPTEUR (autre nom de la fonction)
• Elle ne tient aucun compte des cellules vides et des cellules
contenant des valeurs de type alphanumérique (texte)

Exemples : Résultat
=NB(A1;A7) 1
=NB(A1:A7) 3

66

33
 14/01/2021

Les fonctions logiques

Les fonctions logiques permettent d'introduire des tests dans les

tableaux, afin de vérifier si une condition définie par l'utilisateur a été
remplie. Par exemple vérifier si un certain seuil a été dépassé, ou
faire effectuer un calcul en fonction d'une donnée prédéfinie.

Les opérateurs des fonctions logiques disponibles sont les suivants:

SI (tests simples) et Tests imbriqués

ET et OU Tests élaborés

67

Les opérateurs de comparaison (=,<,>,<=, >=,<>)

Excel peut vérifier l’égalité ou l’inégalité entre des valeurs, les valeurs sont -elles
égales ou l’une est- elle plus élevée que l’autre ? Une formule qui contient l’ un des
opérateurs du titre de cette section renverra la valeur VRAI ou FAUX.
Par exemple, si vous tapez : =1<4 Dans une cellule, la valeur renvoyée sera VRAI.
Inversement, =4<1 Renverra la valeur FAUX.
On peut aussi appliquer ces comparaisons aux données réelles et utiliser des
cellules de référence au lieu des nombres .

= égal à
> supérieur à
< inférieur à
>= supérieur ou égal à
<= inférieur ou égal à
<> différent de

68

34
 14/01/2021

Les fonctions ET, OU

La fonction ET
renvoie la valeur «VRAI» si tous les arguments sont vérifiés, sinon
elle renvoie FAUX.

Syntaxe : OU(valeur_logique1;valeur_logique2,...)

Exemple :
Soit un tableau présentant les résultats de trois épreuves. Nous allons vérifier
que la note de chaque épreuve est supérieure ou égale à 10.

69

La fonction OU
renvoie «VRAI» si un de ses arguments est vérifié, c’est-à- dire qu’elle
renvoie FAUX quand tous les arguments sont faux.
Syntaxe : ET(valeur_logique1;valeur_logique2;...)

Exemple :
Soit un tableau présentant les résultats de trois épreuves. Nous
allons vérifier que l'une des notes est supérieure ou égale à 16 ou
que la moyenne soit supérieure ou égale à 14.

70

35
 14/01/2021

La fonction SI
Cette fonction permet de renvoyer une valeur si la condition
spécifiée (test_logique) est VRAI et une autre valeur si cette valeur
est FAUX.
Syntaxe : SI(test_logique;valeur_si_vrai;valeur_si_faux)
Exemple :
Reprenons l’exemple précédent. Nous allons maintenant utiliser la
fonction SI pour afficher le texte OK si la moyenne est supérieure
ou égale à 10, et ECHEC dans le cas contraire.

71

Les tests imbriqués:

Pour tester plusieurs conditions il est possible d'imbriquer plusieurs
tests dans une séquence logique.
Exemple :
Appliquer deux taux de remise en fonction de deux montants d'achat
différents :
(p. ex. à partir de 300 DH d'achat vous appliquez une remise de 5% et
à partir de 500 DH une remise de 8%)
La fonction contenant les deux tests logiques imbriqués aura la syntaxe
suivante:
=SI(Test_logique1;Valeur_si_vrai1;SI(Test_logique2;Valeur_si_vrai2;Valeur_si_faux))

72

36
 14/01/2021

Les Tests élaborés avec les opérateurs ET et OU

Les opérateurs logiques ET et OU permettent de créer des tests plus nuancés ou
restrictifs. Il s'agit ici de tests logiques qui permettent de tenir compte de plusieurs
conditions, qui peuvent intervenir.
Voici les quatre possibilités pour l'opérateur OU et les résultats respectifs:

Voici les quatre possibilités pour l'opérateur ET et les résultats respectifs:

Les syntaxes des opérateurs logique ET et OU:

ET=SI(ET(1ère condition;2ème condition);Valeur si vrai;Valeur si faux)
OU=SI(OU(1ère condition;2ème condition);Valeur si vrai;Valeur si faux)

73

Etude d’un cas

• L’entreprise A-Pharma commercialise des produits pharmaceutiques

• L’entreprise fait appel à 3 commerciaux (Karim, Mohammed et Jamal)

• Le salaire de ces derniers est constitué

– d’une partie fixe (10000DH)
– et une prime qui représente un pourcentage du CA du mois
• 15% du CA si celui est supérieur à 100000€
• 10% du CA si celui-ci est inférieur ou égal à 100000 €

• On dispose des CA mensuels de chaque représentant

• Question: Concevoir une feuille de calcul permettant de calculer les

salaires mensuels et annuel de chacun des 3 commerciaux

74

37
 14/01/2021

La fonction recherche
Utilisez cette fonction pour effectuer une recherche dans une ligne ou
une colonne et renvoyer une valeur à partir de la même position dans
une seconde ligne ou colonne.
La fonction RECHERCHEV
Utilisez cette fonction pour rechercher des éléments dans une table ou une
plage par ligne. Par exemple, vous pouvez rechercher le nom d’un employé à
l’aide de son matricule ou rechercher son numéro de téléphone à l’aide de son
nom (comme dans un annuaire téléphonique)
Syntaxe :
=RECHERCHEV(cellule_lue ; tableau ; numéro_de_colonne)
celulle_lue : il s'agit de la cellule où Excel lit la valeur qui entraîne la recherche
(exemple : la référence d'un produit conduit à la recherche de son prix et de sa
désignation).
tableau : il s'agit de la plage de cellules où Excel recherche les valeurs (exemple : un
tarif comportant prix et désignations des produits).
numéro_de_colonne : il s'agit du numéro de la colonne du tableau où Excel doit
trouver la valeur cherchée (exemple : les prix se trouvent dans la troisième colonne du
tarif).

75

Exemple :
L'exemple ci-dessous montre l'utilisation de cette fonction pour un
travail simple de facturation.

Voici ce que fait Excel quand il exécute la formule située en B2 :

1° : Excel consulte la cellule A2 (cellule_lue) et y lit la valeur 2.
2° : Excel consulte le tableau qui s'étend de la cellule G2 à la cellule I4. Il y trouve la
valeur 2 lue précédemment.
3° : Excel écrit dans la cellule B2 la valeur "bananes" située dans la deuxième
colonne du tableau.
La procédure est identique pour la formule située dans la cellule D2, mais cette fois
Excel renvoie la valeur trouvée dans la troisième colonne 10 DH.

76

38
 14/01/2021

Précisions
Il est préférable que la colonne qui contient les valeurs cherchées (ici la colonne G)
soit triée en ordre croissant.
• Il est nécessaire de déclarer le tableau en références absolues (G2:I4 devenant
$G$2:$I$4) faute de quoi les références seraient faussées lors de la recopie des
formules. On peut aussi nommer le tableau des références. Pour cela, sélectionnez
les cellules du tableau, puis choisissez l'onglet Formules + groupe Noms définis +
commande Définir un nom.
• On peut déterminer le degré de précision qu'utilise Excel dans la recherche des
valeurs par l'argument valeur_proche placé en fin de formule :
=RECHERCHEV(cellule_lue ; tableau ; numéro_de_colonne ; valeur_proche)

L'argument valeur_proche peut prendre deux valeurs : VRAI ou FAUX.

Si l'argument est VRAI (ou 1), Excel prend la valeur inférieure la plus proche s'il ne
trouve pas la valeur exacte : il accepte une valeur proche de la valeur recherchée.
Si l'argument est FAUX (ou 0), Excel renvoie la valeur exacte, ou un message
d'erreur s'il ne trouve pas de valeur exacte : il refuse une valeur proche de la valeur
recherchée.
Si l'argument est omis, Excel lui attribue par défaut la valeur VRAI.
Il faut donc préciser l'argument valeur-proche = FAUX si l'on désire qu'Excel trouve
la valeur exacte recherchée.

77

Utilisation de deux feuilles de calcul

L'exemple ci-dessus se prête à l'utilisation de deux feuilles de calcul.
La première comportant la facture proprement dite, et la seconde le
tarif.
Si l'on nomme le tableau de référence contenant les désignations et
les prix unitaires TARIF, on obtient alors les formules ci-dessous :

78

39
 14/01/2021

Les fonctions [Link] et [Link]

Prenons l’exemple suivant pour mieux comprendre :

79

Nommage des cellules

Ce travail se prête bien au nommage de cellules. C'est à dire au remplacement d'une
plage de cellules par un nom déterminé.

Pour ce faire, nous allons :

sélectionner la plage A3:B18,
choisir : onglet Formules + groupe Noms définis + Définir un nom,
nommer la plage tableau.

80

40
 14/01/2021

Fonction [Link]

La fonction [Link] permet de compter le nombre de cellules situées à l'intérieur

d'une plage qui répondent à un certain critère.

Observons la cellule E3 :
Il s'agit de compter, dans la plage de cellule A3:A18, nommée tableau, le nombre
de fois où apparaît la chaîne de caractères " Rabat" figurant en A3.

Nous écrivons donc en E3 =[Link](tableau;A3)

La syntaxe générale est la suivante : =[Link](plage_de_cellules;critère)

note : le critère retenu peut être une cellule, une valeur numérique ou une chaîne
de caractères (ne pas oublier les " "). Il faudra alors modifier la chaîne en cas de
recopie de la formule.

81

Fonction [Link]
La fonction [Link] permet d'additionner des cellules d'une plage si elles
répondent à un critère donné.

Observons la cellule F3 :
Il s'agit d'additionner les valeurs figurant dans la plage B3:B18, quand le
contenu d'une cellule de la plage nommée tableau est "Rabat".

Nous écrivons donc en F3 =[Link](tableau;"Rabat";$B$3:$B$18)

La syntaxe générale est la suivante

=[Link](plage_de_cellules;critère;plage_à_totaliser)

Attention : la plage à totaliser doit être déclarée en références absolues pour

que la recopie vers le bas fonctionne correctement.
! Ici également, le critère retenu peut être une cellule, une valeur numérique ou
une chaîne de caractères (ne pas oublier les " ").

82

41
 14/01/2021

La fonction SOMMEPROD

Exemple

Un restaurateur possède deux établissements de fastes foudes, situés à Orléans et à Blois.

Chaque restaurant vend des sandwichs et des salades.
Les statistiques des ventes de ces deux restaurants pour les quatre premiers mois de l'année
2009 sont présentées ci-contre.
Le restaurateur souhaite obtenir pour chaque mois le chiffre d'affaires total de ses deux
restaurants
note : les résultats ne sont donnés ici qu'à titre de vérification, puisque nous allons les
calculer.

83

Fonction SOMMEPROD
La fonction SOMMEPROD permet d'effectuer horizontalement le produit de deux
valeurs situées dans des colonnes différentes et d'en additionner les résultats.

Par exemple, pour le mois 1, le chiffre d'affaires total des deux restaurants se calcule
de la façon suivante : (850x3)+(700x2)+(880x3)+((780x2) = 8 150.
La fonction SOMMEPROD effectue donc, ligne à ligne, la somme de produits.
Nous aurons donc la formule suivante : I5 = SOMMEPROD(E2:E5;F2:F5)
Cette formule effectue le calcul suivant : (E2*F2)+(E3*F3)+(E4*F4)+(E5*F5)
Fonction SOMMEPROD et conditions
La fonction SOMMEPROD peut être assortie de conditions.
Si l'on veut la vente totale de salades à Orléans en 2009, la formule sera :
=SOMMEPROD((A2:A17=2009)*(C2:C17="Orléans")*(D2:D17="salade")*(E2:E17)*
(F2:F17))
Si l'on veut le nombre de ventes de sandwichs à Blois en 2009, la formule sera :
=SOMMEPROD((A2:A17=2009)*(C2:C17="Blois")*(D2:D17)="sandwich"))
Si l'on veut le nombre de ventes dont le montant est supérieur à 2 000 €, la
formule sera :
=SOMMEPROD((G2:G17>2000)*1))

84

42
 14/01/2021

Fonction ARRONDI

Il est important de comprendre que le formatage d’une cellule est différent de

l’arrondi d’un calcul.
Le formatage d’une cellule met en forme l’affichage de la valeur contenue dans
la cellule.
Ainsi la valeur 15,266 formatée avec deux décimales sera affichée 15,27.
Cependant, dans ses calculs, Excel conserve toutes les décimales, y compris celles
qui ne sont pas affichées.
L’arrondi d’un calcul oblige Excel à supprimer les décimales excédentaires, pour
ne retenir que le nombre de décimales spécifiées. Par exemple, du nombre
15,266 Excel ne retiendra que la valeur 15,27 dans les opérations. Ce qui peut
influer sur le résultat des calculs, en particulier en matière comptable et
commerciale.

85

Arrondir un calcul
Pour arrondir le contenu d’une cellule, utilisez la syntaxe suivante :
=ARRONDI (valeur ; nombre de décimales)
Cette syntaxe arrondi le résultat à la valeur la plus proche. Ainsi
ARRONDI(3,156789 ; 2) donne la valeur 3,16.

L’exemple suivant vous montrera l’importance de la distinction entre le

formatage d’une cellule et l’arrondi des calculs.

Saisissez le tableau ci-dessous sur une feuille de calcul Excel :

Vous devez obtenir les résultats suivants :

86

43
 14/01/2021

L'écart peut sembler faible. Il faut cependant garder à l'esprit les points suivants :
plus les sommes impliquées sont importantes, plus les différences peuvent l'être
également ;
arrondir les calculs répond à un impératif commercial : présenter des documents
exacts ;
arrondir les calculs répond à un impératif fiscal, pour la TVA en particulier.

La fonction ARRONDI propose les variantes suivantes :

87

E XC E L - L E S S O U S - TOTAU X

88

44
 14/01/2021

Les sous-totaux
Exemple

Des statistiques de vente de différents produits

ont été saisies dans Excel (tableau à gauche).
Elles ont été triées selon le pays (tableau en
bas).
La fonction sous-total d'Excel va nous
permettre de totaliser ces ventes suivant des
critères variés.
note : il est impératif de trier les statistiques
selon un champ avant d'appliquer un sous-
total.

89

Appliquer un sous total

Pour appliquer un sous total, sélectionnez les statistiques en
cliquant à l'intérieur du tableau, puis, dans l'onglet Données,
cliquez sur Sous-totaux, la boite de dialogue ci-contre apparaît
alors.

Dans la zone À chaque changement de : choisissez le champ

selon lequel les données seront regroupées pour être totalisées.
Ici, les statistiques seront calculées par pays.

Dans la zone Utiliser la fonction : sélectionnez la fonction

mathématique souhaitée. Ici les valeurs seront additionnées.

Dans la zone Ajouter un sous-total à : choisissez le champ

auquel sera appliquée la fonction mathématique sélectionnée
précédemment. Ici, ce sont les chiffres d'affaires (CA) qui seront
additionnés.
Note : le bouton Supprimer tout permet d'effacer les sous-
totaux présents dans une feuille.

90

45
 14/01/2021

visualiser des sous-totaux

À leur création, les sous-totaux apparaissent tels

qu'illustrés ci-contre à gauche, c'est à dire avec le
maximum de détails.

On observe le total général des ventes, ainsi que le

total de la France et celui de l'Italie. Pour chaque pays,
figure une ligne de statistiques par date de vente et
par produit.

Les boutons 1, 2 et 3 situés en haut à gauche de la

feuille permettent de visualiser plus ou moins de
détails sur les statistiques présentées.

Les boutons + et - de la colonne de gauche permettent

de développer un niveau de sous-total pour l'afficher
en détail, ou, au contraire, de ne montrer que son
total général.

91

Les illustrations ci-dessous montrent ces différentes possibilités.

92

46
 14/01/2021

E XC E L - M I S E E N F O R M E
CONDITIONNEL

93

Mise en forme conditionnelle

La mise en forme conditionnelle (MFC) permet de formater une formule en fonction du
contenu d'une cellule, qu'il s'agisse d'un contenu numérique, chaîne de caractères ou logique
(vrai/faux).

La MFC est donc un outil de mise en relief des données, utile dans tous les domaines.

Excel comporte un certain nombre de MFC prédéfinies repérées par une grande icône dans
l'image de droite. Ces boutons font partie du groupe Style de l'onglet Accueil.

Il est également possible de réaliser une MFC personnalisée suivant un grand nombre de
critères en cliquant sur le bouton Nouvelle règle...

Le bouton Effacer les règles... permet d'effacer les règles de MFC présentes dans les cellules
sélectionnées, ou dans l'ensemble de la feuille.

Gérer les règles... permet de modifier les règles de MFC existant dans une feuille de calcul,
d'enlever une règle ou d'en rajouter.

Bien entendu, il est nécessaire de sélectionner la cellule ou la plage de cellule concernée avant
de lui appliquer une MFC. Il peut être intéressant de nommer la plage de cellule.

94

47
 14/01/2021

95

mises en forme conditionnelles prédéfinies

Les mise en formes conditionnelles

prédéfinies sont appliquées après avoir
rempli une boîte de dialogue de ce type. Il
suffit de préciser la ou les valeurs choisies
et la mise en forme désirée.

Dans l'exemple de droite, on a choisi les

dix valeurs les plus élevées d'un tableau.
Elles sont mises en valeur par un texte
rouge foncé sur un fond rouge clair.
D'autres choix sont possibles bien
entendu.

96

48
 14/01/2021

Les mises en forme conditionnelles prédéfinies sont rapides à

appliquer et comportent les choix suivants :
Règles de mise en surbrillance des cellules

Règles des valeurs plus/moins élevées :

Barre de données :

Nuances de couleurs :

Jeux d'icônes :

97

mises en forme conditionnelles personnalisées

Pour obtenir une mise en forme conditionnelle personnalisée, choisir Mise en forme
conditionnelle + Nouvelle règle.
La boîte de dialogue ci-dessous apparaît :

La partie haute de la boîte de dialogue comporte les différents types de règles pouvant être
appliquées.
La partie basse présente les possibilité de formatage possibles en fonction du type de règle
choisi. Cette partie s'adapte à chaque type de règle déterminé.

98

49
 14/01/2021

Mettre en forme toutes les cellules d'après leur valeur. Ici, la règle va attribuer à chaque cellule
de la plage sélectionnée une mise en forme en fonction de la valeur de la cellule. Contrairement
à ce qui se passe avec les autres types de MFC, quelle que soit la valeur , une mise en forme sera
appliquée. Elle changera en fonction de cette valeur.

Appliquer une mise en forme uniquement aux cellules qui contiennent met en forme
uniquement certaines cellules, celles qui contiennent une valeur précisée, une date, un texte
spécifique, un contenu vide, une erreur etc...

Appliquer une mise en forme uniquement aux valeurs rangées parmi les premières ou les
dernières valeurs met en forme uniquement les cellules qui contiennent des valeurs extrêmes.
Le nombre de cellules à mettre en valeur peut être précisé.

Applique une mise en forme uniquement aux valeurs au-dessus ou en-dessous de la moyenne
met en forme les cellules en fonction de leur position par rapport à la moyenne. Cette règle
permet de faire intervenir les écarts-types.

Appliquer une mise en forme uniquement aux valeurs uniques ou aux doublons met en relief
les cellules contenant des doublons, ou, au contraire, des valeurs uniques.

Utiliser une formule pour déterminer pour quelles cellules le format sera appliquée donne une
mise en forme en fonction du résultat d'une formule. Selon que le résultat sera la valeur logique
VRAI ou FAUX, la mise en forme sera appliquée ou non.

99

mises en forme conditionnelles personnalisées se référant à une ou

plusieurs autres cellules
Toutes les MFC présentées ci-dessus s'appliquent à une ou plusieurs cellule en fonction du
contenu de cette/ces cellules.

Il est souvent utile d'appliquer une MFC déterminée en fonction du contenu d'une autre
cellule. Ceci implique de se servir d'une formule.

C'est l'exemple de la feuille de calcul ci-dessous :

Dans cet exemple, des MFC ont été appliquées aux
moyennes d'un groupe d'élève. Ces moyennes ont
été saisies dans la colonne B.

La colonne C contient des MFC déterminées en

fonction des notes contenues dans la deuxième
colonne (B).

Dans la colonne C, les cellules sont colorées en vert

(note supérieur ou égale à 10) ou en rouge (note
inférieure à 10).

Les formules utilisées sont détaillées ci-dessous :

100

50
 14/01/2021

Détaillons ce qui se passe pour la colonne C (notes supérieures ou inférieures à 10).

Une cellule a été sélectionnée, on lui a appliqué la formule =B10<10 et affecté un
fond rouge quand son résultat est VRAI (la note en B10 est inférieure à 10).
On a ajouté à la même cellule une deuxième règle en sélectionnant : Mise en en
forme conditionnelle + Gérer les règles + Nouvelle règle. Cette fois la formule est
=B>=10 et le fond de la cellule est vert quand le résultat est VRAI.
Ces MFC ont ensuite été étendues aux autres cellules par la poignée de recopie de
la cellule. Il est donc nécessaire que la cellule d'origine contienne une formule
dénuée de références absolues (du type $B$10).

101

E XC E L - L E S G R A P H I Q U E S

102

51
 14/01/2021

Les graphiques
Les graphiques ou les diagrammes sont des outils incroyablement pratiques et
Excel vous rend la tâche facile pour les ajouter à votre feuille de calcul pour
que vous puissiez vous aider d'un visuel. Bien que les graphiques puissent être
impressionnants, ils sont en réalité très faciles à réaliser avec Excel. En fait,
Excel rend la création de graphiques tellement efficace, que c'est aujourd'hui
considéré comme une fonctionnalité basique du programme. Il est donc
important d'apprendre à créer des graphiques si vous voulez en tirer un
maximum.
1 Entrez vos données dans la feuille de calcul Excel au format tableau

2 Avec le curseur, surlignez les cellules qui contiennent les informations qui doivent
apparaître dans votre graphique

3 Une fois le texte sélectionné, cliquez sur Insérer, puis Graphiques.

4 vous pouvez après modifier le graphique pour qu'il répond à vos besoins

103

Les graphiques
Les graphique vous permet d’afficher les données de façon lisible et attrayante.
Pour créer un graphique ou modifier le type d’un graphique existant, vous pouvez
choisir parmi les types de graphique suivants.

Histogrammes
Graphiques en courbes
Graphiques en secteurs
Graphiques à barres
Graphiques en aires
Graphiques en nuages de points (XY)
Graphiques boursiers
Graphiques en surface
Graphiques en anneaux
Graphiques en bulles
Graphiques en radar
Autres types de graphiques pouvant être créés dans Excel

104

52
 14/01/2021

Les graphiques
Sélectionnez le tableau, puis cliquez sur « Assistant Graphique ».

Choisissez d’abord un type de graphique, puis sélectionnez un sous-type de

graphique.

105

E XC E L - L A VA L E U R C I B L E

106

53
 14/01/2021

la valeur cible
Un commerçant a créé une petite feuille de calcul qui lui permet de fixer
son prix de vente en fonction de son prix d'achat et du taux de marque
qu'il souhaite.
Le contenu des cellules B2 et B3 est fixé au clavier.
La cellule B4 contient la formule suivante =B2/(1-B3).
Dans l'exemple ci-contre il a tapé un prix d'achat de 80 € et un taux de
marque de 20% ce qui lui donne un prix de vente de 100 €.

Le taux de marque indique la part de marge commerciale dans le prix de vente d'une
marchandise. Il permet d'estimer la rentabilité de la vente d'un produit ou de fixer un prix de
vente hors taxes aux marchandises achetées. Le taux de marque indique la part de marge
commerciale dans le prix de vente d'une marchandise. Il permet d'estimer la rentabilité de la
vente d'un produit ou de fixer un prix de vente hors taxes aux marchandises achetées.

107

Mais les temps sont durs. Et notre commerçant pense, en étudiant la concurrence, qu'il
doit fixer son prix de vente à 90 € pour rester compétitif.
Il a deux possibilités pour atteindre ce nouveau prix : conserver son taux de marque et
négocier un prix d'achat plus faible, ou bien conserver le prix d'achat actuel et accepter
une baisse de son taux de marque. Pour ce faire, il va s'aider de la valeur cible.
Étudions la première hypothèse. La valeur cible s'utilise en activant Analyse de scénarios
+ Valeur cible dans le groupe Outils de données de l'onglet Données. La fenêtre suivante
s'affiche alors :

• La zone Cellule à définir contient la formule

dont on veut fixer le résultat, ici la cellule B4
qui calcule le prix de vente.
• Dans la zone Valeur à atteindre, taper le
résultat souhaité, ici 90 €.
• La zone Cellule à modifier contient la donnée
de départ qui va être modifiée en fonction du
résultat souhaité, ici il s'agit du prix d'achat
situé en B2.

108

54
 14/01/2021

Note : il n'est pas nécessaire de taper les

références, on peut également utiliser à la
souris le bouton de sélection de cellule situé à
droite de chaque zone et cliquer sur la cellule
souhaitée. Excel affiche alors les coordonnées
de la cellule en références absolues ($) comme
illustré ci-contre.
La réponse est immédiate. Excel a calculé que
le commerçant doit négocier un prix d'achat de
72 € afin de pouvoir fixer son prix de vente à 90
€ en pratiquant un taux de marque de 20%.

La deuxième hypothèse est illustrée ci-contre :

Le commerçant a de nouveau fixé un prix de vente de 90 €, mais cette fois il recherche
le nouveau taux de marque (B3 est la cellule à modifier). La valeur cible d'Excel répond
par un taux de marque de 11%.

109

E XC E L - L E S O LV E U R

110

55
 14/01/2021

le solveur
Le solveur d'Excel fonctionne de manière analogue à la valeur cible, tout en offrant des
possibilités beaucoup plus importantes.
En particulier, il est possible de calculer un objectif à atteindre en fonction de plusieurs
cellules variables, et non d'une seule comme avec la valeur cible.
note 1 : le solveur est une macro complémentaire d'Excel qui n'est pas incluse dans
l'installation par défaut du logiciel. Il faut donc éventuellement l'ajouter. Pour cela,
redémarrer l'installation d'Excel et choisir Modifier, demander l'installation des macros
supplémentaires.
note 2 : si vous n'êtes pas familier avec la valeur cible d'Excel, je vous conseille de
commencer par l'étude de cette dernière.

un exemple
Un commerçant a créé une petite feuille de calcul qui lui
permet de fixer sa marge commerciale en fonction des
quantités vendues, du prix de vente unitaire et du prix
d'achat unitaire.
Le contenu des cellules B1, B2 et B3 est tapé au clavier. Ce
sont les cellules variables. La cellule B4 contient la formule
suivante =B1*(B2-B3). La cellule B4 est la cellule résultante
dont la valeur dépend des cellules B1, B2 et B3.

111

Utilisation du solveur
Le solveur se met en œuvre par la commande Solveur du groupe Analyse de l'onglet Données.
Notre commerçant souhaite atteindre une marge commerciale de 800 000 €. Mais certaines
contraintes s'imposent. La quantité vendue ne peut pas être supérieure à 600 unités compte
tenu de la zone de chalandise. La concurrence limite le prix de vente à un maximum de 3 500 €.
Enfin, le prix d'achat unitaire ne peut être inférieur à 1 800 € après négociation

La fenêtre ci-contre montre l'utilisation du solveur.

La cellule cible est celle qui contient l'objectif, c'est à dire la

marge commerciale. Sa valeur a été fixée à 800 000 €. Cette
valeur peut également être définie comme un maximum ou
un minimum.

Les cellules variables contiennent les valeurs de départ du

problème.

Les contraintes sont les valeurs imposées éventuellement

par la situation étudiée.
Les cellules peuvent facilement être choisies grâce à l'outil

112

56
 14/01/2021

Ci-contre, à gauche, la fenêtre d'ajout d'une

contrainte.
Dans l'exemple, la quantité vendue a été fixée
à un maximum de 600 unités.
à vous de jouer
Avez-vous trouvé à quelle quantité et à quels
prix correspond une marge commerciale de
800 000 € ?

113

E XC E L - L E G E S T I O N N A I R E
DES SCENARIOS

114

57
 14/01/2021

le gestionnaire de scénarios
présentation
Le gestionnaire de scénarios d'Excel permet de faire varier des valeurs (cellules variables)
pour voir comment ces variations influent sur le résultat des calculs (cellule résultante).
Ainsi, le gestionnaire de scénario agit dans le sens contraire de la valeur cible qui part du
résultat pour trouver les données.
un exemple

Un commerçant a créé une petite feuille de calcul qui lui permet de fixer son prix de vente en
fonction de son prix d'achat et du taux de marque qu'il souhaite.
Le contenu des cellules B1 et B2 est tapé au clavier. B1 et B2 sont les cellules variables. La cellule
B3 contient la formule suivante =B1/(1-B2). La cellule B3 est la cellule résultante.

115

nommer des cellules

Le gestionnaire de scénarios se prête particulièrement bien au nommage de cellules.
Vous verrez plus loin l'importance de cette pratique.
Nommer une cellule consiste à remplacer ses coordonnées (lettre de colonne, numéro
de ligne) par un nom en langage clair qui facilite grandement la compréhension des
formules utilisées.

Pour nommer une cellule : la sélectionner puis onglet Formules + groupe Noms
définis + commande Définir un nom. Dans l'exemple ci-dessus, les noms suivants
ont été retenus :
cellule B1 : PA
cellule B2 : TAUX
cellule B3 : VENTE

116

58
 14/01/2021

Ecrire des scénarios

Il faut maintenant établir différents scénarios
pour faire des simulations. Pour cela, choisir
Analyse de scénarios + Gestionnaire de
scénarios dans le groupe Outils de données du
groupe Données.
On obtient alors la boite de dialogue ci-contre à
droite.
Les cellules variables sont celles dont dépend le
résultat du calcul. Ici, le prix d'achat et le taux
de marque.
Ensuite, apparaît la boîte de dialogue Valeurs de
scénarios ci-dessous où l'on tape les valeurs des
cellules variables. On voit ici l'intérêt de
nommer les cellules.

Chaque scénario peut être affiché en choisissant Outils + Gestionnaire de scénarios + Afficher.
De même peut-on modifier un scénario en choisissant Outils + Gestionnaire de scénarios +
Modifier.

117

comparer les différents scénarios

Excel permet de comparer les différents scénarios dans un tableau.
Choisir pour cela : Analyse de scénarios + Outils + Gestionnaire de scénarios + Synthèse.
On obtient alors le tableau ci-dessous :

Dans les marges, les symboles + et - les chiffres 1 et 2 permettent de réduire ou de développer
le tableau de synthèse.

118

59
 14/01/2021

E XC E L - L A C O U R B E D E
T E N DA N C E

119

Les courbes de tendance

Exemple
Des statistiques de ventes d'un produit de
grande consommation ont été saisies dans une
petite feuille de calcul Excel. Le premier travail
à faire est de les illustrer sous forme d'un
graphique (ci-contre à droite) à l'aide de
l'assistant graphique

L'insertion de la courbe de tendance se fait ensuite de la manière suivante :

Faire un clic droit sur la courbe + Ajouter une courbe de tendance dans le menu contextuel.
La boîte de dialogue Options de la courbe de tendance apparaît alors.

120

60
 14/01/2021

Suivant la tendance observée sur les graphique, il

est possible de tracer de courbe de tendance
répondant à six types différents d'équation de
courbe.
Dan le cas qui nous concerne, les points sont
bien alignés, nous choisissons donc Linéaire.
Suivant l'apparence de la courbe formée par les
points, on peut choisir un autre type de courbe
de tendance.
Nous pouvons donner un nom personnalisé à
cette courbe de tendance.
Il est également possible de faire des prévisions,
en notant un nombre de périodes à transférer au
paragraphe Prévision. La courbe de tendance
sera alors prolongée au-delà des points existants
observés.
Il est possible d'afficher l'équation de la courbe
de tendance sur la graphique ainsi que le
coefficient de détermination en cochant les
cases concernées.
Les autres rubriques de la colonne de gauche
concernent la mise en forme de la courbe de
tendance

121

La capture d'écran à gauche illustre le résultat obtenu, avec la courbe chronologique

d'origine (en bleu), et la courbe de tendance (en rouge) qui comporte des prévisions
sur trois ans.
Bien évidemment, le choix du type de courbe de tendance influence fortement les
prévisions obtenues.
Il convient également de s'interroger sur la validité de ce type de prévisions.

122

61
 14/01/2021

E XC E L - TA B L E AU C R O I S E
DY N A M I Q U E

123

les tableaux croisés dynamiques

La feuille de calcul ci-contre présente des statistiques de ventes.
Ces statistiques sont présentées suivant leur pays de réalisation, le nom du représentant,
le montant de la commande, la date de la commande et la référence de la commande.
Nous allons maintenant exploiter ces statistiques grâce aux tableaux croisés dynamiques
d'Excel et voir comment ces tableaux croisés dynamiques peuvent les présenter de
différentes manières. Par exemple, par pays, par représentant, par date etc...

124

62
 14/01/2021

construire un tableau croisé dynamique

Cliquez à l'intérieur du tableau et choisissez Insertion + Tableaux croisé dynamique.
Cette manipulation vous permet de préciser d'où viennent les données à analyser qui
figureront dans le tableau croisé dynamique.
Ces données sont automatiquement repérées dans la feuille de calcul par Excel. Mais il est
possible d'utiliser une source de données externes comme un autre classeur Excel ou une
table Access.
Il est également possible de choisir à ce niveau l'emplacement du tableau croisé
dynamique : feuille de calcul existante ou nouvelle feuille de calcul.
Cliquez sur OK.

125

La prochaine étape montre l'outil de construction du tableau croisé dynamique :

À gauche (1) se trouve la zone de construction du tableau croisé dynamique.

À droite (2), se trouve la liste des champs pouvant figurer dans le tableau croisé dynamique.
Si l'on clique à l'extérieur de la zone 1, la zone 2 disparaît. Il suffit de recliquer dans la zone 1
pour retrouver la zone 2.
La construction du tableau croisé dynamique consiste à insérer dans la partie gauche (1) des
champs choisis dans la partie droite (2).
On peut insérer les champs de trois manières :
en cochant la case en regard de chaque champ ;
en glissant à la souris un champ de la partie 2 vers une zone de la partie 1 ;
en glissant à la souris un champ dans les zones situées en bas de la partie 2 : Filtre du rapport,
Étiquettes de colonnes, Étiquettes de lignes ou Valeurs.

126

63
 14/01/2021

Voici un exemple de tableau croisé dynamique qu'il est possible d'obtenir :

On retrouve ici un tableau à double entrée dans lequel les ventes sont ventilées par
représentant et par date, et totalisées.
L'opération effectuée ici est la somme On peut changer cette opération en cliquant sur Somme
de commande (dans la zone Valeurs en bas à droite) puis sur Paramètres des champs de
valeurs.
L'intérêt des petites flèches situées en regard des champs est qu'elles permettent de
sélectionner un ou plusieurs représentants, une ou plusieurs dates et de changer de pays.
Voici quelques exemples :

127

Voici quelques exemples :

128

64
 14/01/2021

E XC E L - P R OT E C T I O N D E S
DONNEES

129

Protection des données

Dans de très nombreuses circonstances, il est utile de pouvoir

protéger ses données, contre la curiosité, la malveillance et, le
plus souvent, les erreurs de manipulation. Cette page présente les
différentes méthode de protection de données et à quoi elles
s'appliquent.

130

65
 14/01/2021

Protéger un fichier Excel

Il est possible, lors de l'enregistrement d'un classeur, d'attribuer un mot de passe à ce
dernier pour limiter son accès en lecture (consultation) ou en écriture (modification).
Dans la boîte de dialogue Enregistrer sous, cliquez sur Outils, puis Options générales.
Saisissez un mot de passe pour la lecture et, éventuellement, un mot de passe pour
la modification. Il est préférable que les mots de passe soit différents. Ces mots de
passe sont à taper deus fois pour confirmation.

À l'ouverture du fichier, l'utilisateur est convié à donner le mot de passe de lecture,

puis d'écriture (modification). L'utilisateur qui ne dispose pas du mot de passe de
modification ne pourra pas sauvegarder les modification apportées à la feuille.

131

Protéger un classeur
Pour protéger un classeur dans son intégralité, utilisez le bouton Protéger le
classeur du groupe Modifications dans l'onglet Révision.

Vous pourrez ainsi empêcher les utilisateurs d'ajouter ou de supprimer des feuilles
de calculs et d'afficher celles qui ont été masquées. Si la case Fenêtres est cochée,
ils ne pourront pas changer les tailles ou les positions des fenêtres que vous aurez
définies pour afficher un classeur.

Si vous saisissez un mot de passe, ce mot de passe vous sera demandé si vous
désirez ultérieurement supprimer la protection du classeur en cliquant à nouveau
sur le bouton Protéger le classeur du groupe Modifications de l'onglet Révisions

132

66
 14/01/2021

Protéger une feuille et des cellules

Pour protéger une cellules, plusieurs cellules ou une plage de cellules, il faut verrouiller
ces cellules, puis protéger la feuille.
Chaque feuille possède ses paramètres de protection indépendant de ceux des autres
feuilles.
En fait, par défaut, toutes les cellules d'une feuille Excel sont verrouillées.
Il s'agit donc de définir quelles sont les cellules déverrouillées dans une feuille puis de
protéger la feuille. À la suite de cette manipulation, seules certaines cellules pourront
être sélectionnées par l'utilisateur, pour une saisie par exemple.

Définir les cellules déverrouillées (non protégées)

Il est nécessaire de sélectionner toutes les cellules ou
plages de cellules qui ne seront pas protégées.

Ensuite indiquer que ces cellules ne sont pas verrouillées

en faisant un clic droit sur ces cellules et en sélectionnant
Format de cellule. L'onglet Protection permet de définir si
les cellules doivent ou non être protégées.

Vous pouvez aussi vous servir du bouton Verrouiller la

cellule en sélectionnant le bouton Format du groupe
Cellules de l'onglet Accueil.

133

Appliquez la protection
Tout ce travail de protection des cellules ne sera opératoire que si la feuille de calcul est
protégée.
Dans l'onglet Accueil, cliquez sur le bouton Format du groupe Cellules et sélectionnez
Protéger la feuille.
Vous pouvez retrouver ce même bouton dans l'onglet Révisions, groupe Modification.
Une fois la feuilles protégée, ces boutons se transforment en Ôter la protection de la
feuille qui permettent de déprotéger votre travail.
Exemple
Cet exemple simple calcule le salaire net d'un
salariée en fonction de son salaire brut ainsi
que le coût total du travail en tenant compte
des charges salariales et des charges
patronales. L'utilisateur ne peut modifier que
la cellule C2 pour y saisir un montant, les
autres cellules étant verrouillées et la feuille
protégée.
masquer des cellules
Il peut être utile de masquer des cellules à l'utilisateur, en particulier si vous ne souhaitez pas
que l'on puisse lire vos formules de calcul.
Pour cela, il suffit de revenir à la boîte de dialogue Format de cellule ci-dessus et de cochée
Masquée dans l'l'onglet Protection

134

67