EXCEL Perfectionnement

AVANT PROPOS

L’utilisation du matériel informatique est soumise au respect des règles de bon usage
énoncées dans la « Charte des utilisateurs du Système d’Information de la Ville et du
Département de Paris ». Toute tentative de connexion ou d’usage frauduleux ou mal
intentionné est passible de sanction.
Le matériel mis à disposition doit également être utilisé dans le respect d’un usage
écoresponsable. Il est donc important de mettre hors tension son matériel informatique
(écran compris) lors de la pause déjeuner et à la fin de la journée de travail.
Afin de réduire la consommation d'encre et de papier, l’éventuelle impression du
présent mémento pourra être paramétrée en fonction du logiciel et du matériel
d’édition mis à disposition.

2
 EXCEL Perfectionnement

Ce memento a été réalisé par les formateurs du Bureau de la formation de la Ville de Paris.

Le memento "EXCEL Perfectionnement" n’est pas un outil d’auto-formation.

Il a été conçu pour être le complément d’un stage de formation et doit s’utiliser comme un aide-mémoire.

Il reprend les objectifs du stage "EXCEL Perfectionnement" version 2010.

RAPPEL DES OBJECTIFS

 Améliorer ses connaissances afin de réaliser des tableaux complexes, prévoir leurs évolutions dans le
temps
 Mettre en œuvre les fonctions avancées d’Excel
 Concevoir des graphiques élaborés.

RAPPEL DU CONTENU
 Concevoir une organisation des données adaptée à un besoin spécifique
 Personnaliser son environnement de travail au moyen de la barre d'outils “Accès rapide”, du ruban et des
options de paramétrage
 Mettre en œuvre certaines fonctions spécifiques : statistiques, logiques, multi-conditionnelles, date,
heure, texte et savoir les imbriquer
 Contrôler la saisie et les erreurs de calcul
 Élaborer et analyser des données grâce aux filtres élaborés, au mode plan, à l'affichage personnalisé
 Procéder aux calculs sur les données : sous-totaux, fonctions bases de données…
 Comprendre et utiliser les liens avancés : consolidation, recherche
 Concevoir des graphiques élaborés : superposés et spécifiques
 Mettre en œuvre les options de protection : cellules, feuilles, classeurs
 Créer et façonner des modèles : classeur, feuille.

Les différents chapitres sont indépendants. Ils traitent chacun d’un thème précis.

Convention typographique

Signifie rubrique « Trucs et Astuces »

Pour accéder à une commande, a été utilisée la notation suivante :

Le texte en gras désigne « l’onglet », celui en italique « le groupe », suivi de « la commande ».
Exemple : Accueil/Police/Gras

3
 EXCEL Perfectionnement

SOMMAIRE

1 STOCKAGE DES INFORMATIONS ..................................................................................................8

1.1 Organisation ................................................................................................................8
1.1.1 Importance de l'organisation du stockage des informations .................................................8
1.1.2 Évolution des tableaux ..............................................................................................8
1.2 Tableaux dans une même feuille .......................................................................................8
1.3 Tableaux dans des feuilles différentes .................................................................................9
1.4 Tableaux dans des classeurs différents ................................................................................9
1.5 Feuilles groupées ........................................................................................................ 10
2 ACCÈS AUX INFORMATIONS ..................................................................................................... 11
2.1 Liens entre cellules, feuilles et classeurs ........................................................................... 11
2.1.1 Principe .............................................................................................................. 11
2.1.2 Création d'un lien .................................................................................................. 11
2.1.3 Mise à jour .......................................................................................................... 11
2.1.4 Gestion des liens ................................................................................................... 12
2.2 Fonctions de recherches et références .............................................................................. 12
2.2.1 Recherche verticale, fonction RECHERCHEV .................................................................. 12
2.2.2 Recherche horizontale, fonction RECHERCHEH ............................................................... 13
2.2.3 RECHERCHE ......................................................................................................... 13
2.2.4 Recherche indexée, fonction INDEX ............................................................................ 14
2.2.5 Equivalence, fonction EQUIV ..................................................................................... 14
2.2.6 Transposition, fonction matricielle TRANSPOSE .............................................................. 15
2.2.7 Référence indirecte, fonction INDIRECT ....................................................................... 15
2.2.8 Extension de zone paramétrée, fonction DECALER .......................................................... 15
2.3 Affichage d’un tableau sous forme d’image ........................................................................ 15
2.3.1 Principes ............................................................................................................. 15
2.3.2 Mise en œuvre ...................................................................................................... 16
2.4 Zones nommées .......................................................................................................... 16
2.4.1 Principes ............................................................................................................. 16
2.4.2 Zones nommées définies automatiquement par Excel ...................................................... 16
2.4.3 Zones nommées créées par l’utilisateur ....................................................................... 16
2.4.4 Codification des zones nommées ................................................................................ 17
2.4.5 Repérage des zones nommées par des couleurs spécifiques ............................................... 17
2.4.6 Gestion des zones nommées ..................................................................................... 18
3 CONTRÔLE ET VALIDATION DE LA SAISIE ..................................................................................... 19
3.1 Contrôle de la saisie ..................................................................................................... 19
3.2 Format de nombre personnalisé....................................................................................... 19
3.3 Mise en forme conditionnelle .......................................................................................... 20
3.4 Contrôle par formule .................................................................................................... 20
3.4.1 Alerte avec formule ............................................................................................... 21
3.4.2 Alerte avec Mise en forme conditionnelle ..................................................................... 21
3.5 Outil validation ........................................................................................................... 21
3.6 Contrôles de formulaire ................................................................................................ 23
3.7 Repérage des saisies..................................................................................................... 26

4
 EXCEL Perfectionnement

4 CONTRÔLES DES RÉSULTATS ................................................................................................... 27

4.1 Vérification des résultats ............................................................................................... 27
4.1.1 Contrôle par formule de calculs ................................................................................. 27
4.1.2 Mise en forme conditionnelle .................................................................................... 27
4.1.3 Fonctions dans la barre d'état Excel ............................................................................ 27
4.1.4 Indicateur d'erreur ................................................................................................. 28
4.2 Gestion des erreurs renvoyées ........................................................................................ 29
4.2.1 Fonction SIERREUR ................................................................................................. 29
4.2.2 Fonctions EST ....................................................................................................... 29
5 FONCTIONS AVANCÉES .......................................................................................................... 30
5.1 Fonctions logiques ....................................................................................................... 30
5.1.1 Condition simple SI ................................................................................................ 30
5.1.1 Conditions multiples ET - OU - NON ............................................................................ 30
5.2 Fonctions statistiques conditionnelles ............................................................................... 31
5.2.1 Comptage conditionnel NB.SI .................................................................................... 31
5.2.2 Somme et Moyenne conditionnelle SOMME.SI MOYENNE.SI ................................................. 31
5.2.3 Critères .............................................................................................................. 32
5.3 Tables d'hypothèses ..................................................................................................... 32
5.3.1 Principes ............................................................................................................. 32
5.3.2 Tables à 1 entrée .................................................................................................. 32
5.3.3 Tables à 2 entrées ................................................................................................. 33
5.4 Fonctions texte ........................................................................................................... 33
5.5 Fonctions dates .......................................................................................................... 35
6 LISTES DE DONNÉES .............................................................................................................. 36
6.1 Organisation des données .............................................................................................. 36
6.2 Filtres automatiques .................................................................................................... 36
6.2.1 Mise en œuvre ...................................................................................................... 36
6.2.2 Filtre selon le type de données ................................................................................. 36
6.2.3 Filtre par recherche ............................................................................................... 37
6.2.4 Filtre personnalisé ................................................................................................. 38
6.2.5 Calculs statistiques sur les valeurs filtrées .................................................................... 38
6.2.6 Annulation des filtres ............................................................................................. 38
6.2.7 Enregistrement des filtres ........................................................................................ 38
6.3 Filtre avancé .............................................................................................................. 39
6.3.1 Principes ............................................................................................................. 39
6.3.2 Création ............................................................................................................. 40
6.3.3 Critères de recherche ............................................................................................. 40
6.3.4 Fonctions "liste de données" ..................................................................................... 41
6.4 Consolidation ............................................................................................................. 41
6.4.1 Principes ............................................................................................................. 41
6.4.2 Consolidation à partir des emplacements ..................................................................... 42
6.4.3 Consolidation à partir des entêtes .............................................................................. 43
6.5 Mode "Tableau" ........................................................................................................... 44
6.5.1 Principes ............................................................................................................. 44
6.5.2 Création ............................................................................................................. 45
6.5.3 Présentation ........................................................................................................ 45
6.5.4 Dimensions du tableau ............................................................................................ 46
6.5.5 Sous-totaux ......................................................................................................... 46
6.5.6 Formule avec en-tête de colonne ............................................................................... 47
6.5.7 Convertir un tableau en mode normal ......................................................................... 47
7 GRAPHIQUES ...................................................................................................................... 48
7.1 Organiser les données d'un graphique ................................................................................ 48
7.1.1 Définir l'emplacement des données ............................................................................ 48
7.1.2 Création d'un graphique .......................................................................................... 48
7.2 Modifier la source des données ........................................................................................ 48
7.2.1 Graphique incorporé .............................................................................................. 48
7.2.2 Feuille graphique .................................................................................................. 49

5
 EXCEL Perfectionnement

7.3 Gérer les séries de données ............................................................................................ 49

7.3.1 Ajouter une nouvelle série ....................................................................................... 49
7.3.2 Modifier une série .................................................................................................. 49
7.3.3 Supprimer une série ............................................................................................... 49
7.4 Modifier l'emplacement d'un graphique .............................................................................. 50
7.4.1 Graphique incorporé en feuille graphique ..................................................................... 50
7.4.2 Feuille graphique en graphique incorporé ..................................................................... 50
7.5 Graphiques superposés.................................................................................................. 50
7.5.1 Principes ............................................................................................................. 50
7.5.2 Répartition des séries ............................................................................................. 51
7.5.3 Aligner les étiquettes ............................................................................................. 51
8 PROTECTION ...................................................................................................................... 52
8.1 Principes ................................................................................................................... 52
8.2 Protection du classeur .................................................................................................. 52
8.2.1 Enregistrement automatique .................................................................................... 52
8.2.2 Copie de sauvegarde .............................................................................................. 52
8.2.3 Mot de passe à l'ouverture ....................................................................................... 52
8.2.4 Mot de passe pour modifications ................................................................................ 53
8.2.5 Lecture seule recommandée ..................................................................................... 53
8.3 Protection contre les modifications .................................................................................. 54
8.3.1 Protection des cellules ............................................................................................ 54
8.3.2 Protection de la feuille ........................................................................................... 54
8.3.3 Déplacements ....................................................................................................... 54
8.3.4 Protection du classeur ............................................................................................ 55
9 MODÈLES ........................................................................................................................... 56
9.1 Modification des modèles par défaut ................................................................................ 56
9.1.1 Classeur par défaut ................................................................................................ 56
9.1.2 Feuille par défaut .................................................................................................. 56
9.2 Modèles spécifiques ..................................................................................................... 56
9.2.1 Création d'un modèle .............................................................................................. 56
9.2.2 Créer un classeur à partir d'un modèle ........................................................................ 56
9.2.3 Insérer les feuilles d'un classeur modèle ...................................................................... 56
9.2.4 Ouvrir un classeur au démarrage d'Excel ...................................................................... 56
10 OUTILS DE SIMULATION ......................................................................................................... 57
10.1 Scénario ................................................................................................................... 57
10.1.1 Principes ............................................................................................................. 57
10.1.2 Mise en œuvre ...................................................................................................... 57
10.2 Valeur cible ............................................................................................................... 58
10.2.1 Principes ............................................................................................................. 58
10.2.2 Mise en œuvre ...................................................................................................... 58
10.3 Solveur ..................................................................................................................... 59
10.3.1 Principes ............................................................................................................. 59
10.3.2 Mise en œuvre ...................................................................................................... 59
10.3.1 Exemple de mise en œuvre ...................................................................................... 61
11 AFFICHAGES ....................................................................................................................... 63
11.1 Mode plan ................................................................................................................. 63
11.1.1 Principes ............................................................................................................. 63
11.1.2 Mise en œuvre ...................................................................................................... 63
11.1.3 Gestion ............................................................................................................... 63
11.2 Affichages personnalisés ................................................................................................ 64
11.2.1 Principes ............................................................................................................. 64
11.2.2 Mise en œuvre ...................................................................................................... 64

6
 EXCEL Perfectionnement

12 ANNEXES ........................................................................................................................... 65
12.1 Formats personnalisés .................................................................................................. 65
12.1.1 Création ............................................................................................................. 65
12.1.2 Codes formats ...................................................................................................... 65
12.1.3 Sections .............................................................................................................. 66
12.2 Pourcentages ............................................................................................................. 66
12.2.1 Calculs ............................................................................................................... 66
12.2.2 Comparaisons ....................................................................................................... 67
12.3 Fonctions Excel ........................................................................................................... 68
12.4 Index ....................................................................................................................... 83

7
 EXCEL Perfectionnement

1 STOCKAGE DES INFORMATIONS

1.1 Organisation

1.1.1 Importance de l'organisation du stockage des informations

Une bonne organisation facilitera l'accès et la gestion des informations.

 les informations étant correctement stockées, elles seront réutilisables plus facilement dans la même
application ou à partir d'une autre application
 elle participe à l'évolutivité de l'application ainsi gérée : on pourra ajouter de nouvelles valeurs sans
modifier les formules déjà créées
 elle permet de répondre à une demande rapidement et sans remettre en cause l'existant : tout nouveau
résultat demandé pourra être créé facilement
 il est possible de répartir des informations liées entre plusieurs classeurs et/ou feuilles selon l'utilisation
qui en est faite

en fonction de l'exploitation, il est recommandé de différencier

les tableaux de saisie des tableaux de calculs

1.1.2 Évolution des tableaux

 un stockage approprié des informations facilitera l'ajout de nouvelles données

 les traitements doivent prévoir l'ajout de ces données en utilisant des sélections plus grandes que celles
prévues avec les données actuelles

penser à définir visuellement les zones de saisies possibles en mettant

en œuvre une présentation appropriée (encadrements, couleur de fond…)

1.2 Tableaux dans une même feuille

Lorsque les tableaux ont le même niveau d'intérêt, on les créera dans une même feuille car il est intéressant de
pouvoir les visualiser simultanément sans se déplacer entre feuilles.
Exemple : tableaux spécifiques pour les graphiques regroupés ensemble…

STOCKAGE DES INFORMATIONS 8

 EXCEL Perfectionnement

1.3 Tableaux dans des feuilles différentes

Lorsque l'on veut différencier des tableaux indépendants ou adopter une présentation spécifique à chacun
d’entre eux : largeurs de colonne, hauteurs de ligne, bordures…
Exemple : tableaux de saisie

1.4 Tableaux dans des classeurs différents

Cela permet de partager un tableau entre plusieurs applications ou utilisateurs : les tableaux sont stockés dans
des fichiers distincts.
 il est possible d'attribuer des droits d'accès sur les fichiers et de gérer la confidentialité
 il est possible aussi de faire un suivi des modifications…
Exemple : tableaux de données partagés

STOCKAGE DES INFORMATIONS 9

 EXCEL Perfectionnement

1.5 Feuilles groupées

La gestion de groupe permet de concevoir simultanément plusieurs tableaux identiques et de conserver ainsi la
structure et les invariants des tableaux.

limites des feuilles groupées : certains outils ne sont plus accessibles !

Exemple : tableaux de relevés mensuels

STOCKAGE DES INFORMATIONS 10

 EXCEL Perfectionnement

2 ACCÈS AUX INFORMATIONS

2.1 Liens entre cellules, feuilles et classeurs

2.1.1 Principe

Les liens permettent de faire apparaître le contenu d’une cellule à un autre emplacement dans le même
classeur ou dans un autre classeur. Par ailleurs le lien étant une formule, il est mis à jour automatiquement.

2.1.2 Création d'un lien

Pour créer un lien, saisir = puis cliquer sur la cellule source, celle-ci peut être dans la même feuille, dans une
autre feuille ou même dans un autre classeur.
 dans la même feuille
=cellule
exemple : =B3
 entre 2 feuilles différentes
=feuille!cellule
exemple : =Gestion!B3
 entre 2 classeurs différents
='chemin\[classeur]feuille'!cellule
Remarque
lorsque la source provient d'un autre classeur, les coordonnées sont en références absolues par défaut
exemple : ='C:\Documents\[Ventes.xlsx]Saisie'!$E$5

pour lier une grande plage de cellules, il est plus pratique d’utiliser le
collage spécial…
COPIER / COLLAGE SPÉCIAL… / COLLER AVEC LIAISON

2.1.3 Mise à jour

 liaison dans le même classeur ou dans la même feuille : la mise à jour s’effectue en temps réel
 liaison entre classeurs
 si les classeurs sont ouverts : la mise à jour s'effectue en direct
 sinon une demande de confirmation s’affichera par défaut à l’ouverture du classeur lié
Remarque
si le lien avec le tableau source a disparue, Excel affichera un 0 dans les cellules destinations
 modifier la mise à jour à partir des classeurs sources
Données / Connexions / Modifier les liens /

ACCÈS AUX INFORMATIONS 11

 EXCEL Perfectionnement

2.1.4 Gestion des liens

 classeurs liés stockés dans le même dossier :

les coordonnées des cellules liées seront remises à jour automatiquement si on déplace les classeurs liés
dans un autre disque/dossier
 classeurs liés à partir de dossiers différents ou déplacés dans des dossiers différents :
il faudra effectuer la modification des liens par Données / Connexions / Modifier les liens
puis indiquer l’emplacement de chacun des classeurs source

2.2 Fonctions de recherches et références

2.2.1 Recherche verticale, fonction RECHERCHEV

 recherche exacte
permet de rechercher une valeur dans la 1ère colonne d’un tableau et de récupérer le contenu d’une
colonne
=RECHERCHEV(valeur_cherchée;table_matrice;n°index_col;FAUX)
Remarque
renvoie #N/A lorsque la valeur recherchée est inconnue
A B C D
1 Po Pomme 0,50 RECHERCHEV
2 Ce Cerise 0,16 valeur_cherchée.... B5
3 Gr Groseille 0,34 table_matrice ....... B1:D3
4 n°index_col ......... 3
5 Ce 0,16 valeur_proche ...... FAUX
 recherche approchée
permet de rechercher une valeur la plus proche dans la 1 ère colonne d’un tableau et de récupérer le
contenu d’une colonne : les données de la 1ère colonne doivent être triées par ordre croissant
=RECHERCHEV(valeur_cherchée;table_matrice;n°index_col;VRAI ou omis)
Remarque
renvoie #N/A lorsque la valeur recherchée est inférieure à la 1 ère valeur
A B C
1 0 insuffisant RECHERCHEV
2 1000 résultats moyens valeur_cherchée.... B5
3 3000 objectifs atteints table_matrice ....... B1:C3
4 n°index_col ......... 2
5 1320 résultats moyens valeur_proche ...... VRAI

ACCÈS AUX INFORMATIONS 12

 EXCEL Perfectionnement

2.2.2 Recherche horizontale, fonction RECHERCHEH

 recherche exacte
permet de rechercher une valeur dans la 1ère ligne d’un tableau et de récupérer le contenu d’une ligne
=RECHERCHEH(valeur_recherchée;zone_cellules;n°ligne;FAUX)
Remarque
renvoie #N/A lorsque la valeur recherchée est inconnue
A B C D
1 Versailles Lille Fréjus Bordeaux RECHERCHEH
2 78 59 83 33 valeur_cherchée.... A5
3 Yvelines Nord Var Gironde table_matrice ....... A1:D3
4 n°index_lig .......... 2
5 Fréjus 83 valeur_proche ...... FAUX
 recherche approchée
permet de rechercher une valeur la plus proche dans la 1 ère ligne d’un tableau et de récupérer le
contenu d’une ligne : les données de la 1ère ligne doivent être triées par ordre croissant
=RECHERCHEH(valeur_cherchée;table_matrice;n°index_lig;VRAI ou omis)
Remarque
renvoie #N/A lorsque la valeur recherchée est inférieure à la 1 ère valeur
A B C D
1 01/01/2009 05/02/2010 01/01/2011 02/03/2012 RECHERCHEH
2 3000 3420 2800 2800 valeur_cherchée.... A5
3 26,5 % 22,0 % 11,5 % 12,6 % table_matrice ....... A1:C3
4 n°_index_lig......... 3
5 22/03/2011 11,5% valeur_proche ...... VRAI

2.2.3 RECHERCHE

La fonction RECHERCHE renvoie une valeur provenant soit d’une plage à une ligne ou une colonne, soit d’une
matrice, elle a donc 2 syntaxes : la forme vectorielle et la forme matricielle.
 forme matricielle
recherche la valeur spécifiée dans la première ligne ou colonne d’une matrice et renvoyer une valeur à
partir de la même position dans la dernière ligne ou colonne de la matrice
utilisez la forme matricielle lorsque votre recherche porte sur une liste de valeurs peu importante et que
les valeurs restent constantes dans le temps
=RECHERCHE(valeur_cherchée;matrice)
A B C
1 Po Pomme 0,50
2 Ab Abricot 0,65
3 Ce Cerise 0,16
4 Gr Groseille 0,34 RECHERCHE
5 valeur_cherchée ... A6
6 Ce 0,16 matrice .............. A1:C4

ACCÈS AUX INFORMATIONS 13

 EXCEL Perfectionnement

 forme vectorielle
rechercher une valeur dans une plage à une ligne ou colonne (appelée vecteur) et renvoyer une valeur à
partir de la même position dans une seconde plage à une ligne ou colonne
utilisez la forme vectorielle lorsque votre recherche porte sur une liste de valeurs importante ou lorsque
les valeurs risquent de changer dans le temps
=RECHERCHE(valeur_cherchée;matrice)
A B C
1 Po
2 Ab 0,50
3 Ce 0,65
4 Gr 0,16 RECHERCHE
5 0,34 valeur_cherchée ...... A7
6 vecteur_recherche.... A1:A4
7 Ab 0,65 vecteur_résultat ...... C2:C5

2.2.4 Recherche indexée, fonction INDEX

Utilisable lorsque l’on connaît l’indexation dans une matrice, n° de ligne et de colonne.
=INDEX(matrice;n°lig;n°col)
A B C
1 Po Pomme 0,50
2 Ab Abricot 0,65
3 Ce Cerise 0,16 INDEX
4 Gr Groseille 0,34 matrice .. A1:C4
5 n°lig ...... 3
6 Cerise n°col ..... 2

2.2.5 Equivalence, fonction EQUIV

Permet de connaître la position d’une valeur recherchée dans une liste.

=EQUIV(valeur_cherchée;tableau_recherche;type)
type : –1 trouve la plus petite valeur qui est >= à valeur_cherchée (la liste doit être croissante)
0 trouve la 1ère valeur exactement équivalente à valeur_cherchée (la liste n’a pas besoin
d’être triée)
1 (valeur par défaut) trouve la valeur la plus élevée qui <= à valeur_cherchée (la liste doit
être croissante)
A B
1 Po Pomme
2 Ab Abricot
3 Ce Cerise EQUIV
4 Gr Groseille valeur_cherchée ......A6
5 tableau_recherche ...B1:B4
6 Abricot 2 type .....................0

ACCÈS AUX INFORMATIONS 14

 EXCEL Perfectionnement

2.2.6 Transposition, fonction matricielle TRANSPOSE

Cette fonction permet de transposer un tableau en sens inverse.

 les cellules du tableau transposé sont liées vers les cellules d'origines
exemple : modifier l'orientation d'un tableau pour en faire une impression
 sélectionner une zone de cellules équivalente à celle du tableau d'origine mais en sens inverse,
exemple : tableau d'origine 4L x 2C  sélectionner 2L x 4C
puis saisir la formule dans la 1ère cellule de cette zone (cellule active)
=TRANSPOSE(tableau d'origine)
valider par (CTRL) + (MAJ) + (ENTREE) car c'est une fonction matricielle…

2.2.7 Référence indirecte, fonction INDIRECT

Permet de faire référence indirectement à une zone de cellules.

=INDIRECT(référence_texte)
permet également de toujours faire référence à la même cellule, même si la ligne au-dessus est
supprimée ou la cellule déplacée
exemple : toujours faire référence à la cellule A10  INDIRECT("A10")
A B C D 3 zones nommées :
1 Nord Sud Centre Nord B2:B5
2 studio 26 37 22 Sud C2:C5
3 appart. 46 70 16 Centre D2:D5
4 maison 112 146 38
5 terrain 31 42 9
6
7 région total INDIRECT
8 Sud 295 =SOMME(INDIRECT(A8))

2.2.8 Extension de zone paramétrée, fonction DECALER

Elle permet de paramétrer le décalage et/ou l'étendue d'une zone de cellules.

=DECALER(réf;lignes;colonnes;hauteur;largeur)
réf .......... référence par rapport à laquelle le décalage et/ou l'étendue doit être effectué
lignes ....... nombre de lignes vers le haut ou vers le bas dont la cellule supérieure gauche de la
référence renvoyée doit être décalée
colonnes ... nombre de colonnes vers la droite ou vers la gauche dont la cellule supérieure gauche de la
référence renvoyée doit être décalée
hauteur .... nombre de lignes que la référence renvoyée doit avoir (facultatif)
largeur ..... nombre de colonnes que la référence renvoyée doit avoir (facultatif)

2.3 Affichage d’un tableau sous forme d’image

2.3.1 Principes

Il permet de créer une image à partir d'une zone de cellules.

 exemple : intégrer un tableau d'une présentation différente, largeur de colonnes et hauteur de lignes,
dans un tableau n'ayant pas la même présentation : le tableau intégré n'est pas tributaire des largeurs
de colonnes et hauteurs de lignes…
 l'image est liée aux cellules d'origine et sera donc mise à jour

ACCÈS AUX INFORMATIONS 15

 EXCEL Perfectionnement

2.3.2 Mise en œuvre

 création : Accueil / Presse-papiers / Copier / / Autres options de collage / Image liée

ou sélectionner la zone voulue / Appareil photo (ajouter éventuellement l'outil) / cliquer sur
destination
 possibilité de redimensionner l’image obtenue…

2.4 Zones nommées

2.4.1 Principes

Intérêt des zones nommées : formules, paramétrage, grandes zones, sélections…

2.4.2 Zones nommées définies automatiquement par Excel

Elles résultent de certaines commandes sous Excel.

 étendue de type "Feuille" étendue de type "Classeurs"
Critères Tableaux
Extraire (créés à partir de Mettre sous forme tableaux)
Impression des titres
Zone d’impression…
 leur gestion s’effectue par Formules / Noms définis / Gestionnaires de noms

2.4.3 Zones nommées créées par l’utilisateur

 possibilité de créer des constantes nommées faisant référence à une valeur

Fait référence à : saisir =valeur

 choisir l’étendue des zones nommées (feuille ou classeur).

 Étendue classeur
par défaut les zones créées directement dans Zone nom (dans la barre de formule)
sont d’étendue de type "Classeur"

ACCÈS AUX INFORMATIONS 16

 EXCEL Perfectionnement

 Étendue feuille
pour créer des zone nommées d'étendue de type "Feuille", il faut utiliser :
Formules / Noms définis / Définir un nom / Zone : choisir la feuille

 création à partir des étiquettes de lignes ou colonnes d'un tableau

permet de créer automatiquement des zones en les nommant à partir des étiquettes de lignes/colonnes
il faut sélectionner le tableau avec les étiquettes, puis Formules / Noms définis /
Depuis sélection
A B C D
1 Terrains Maisons Appartements
2 Nord 57 33 88
3 Sud 30 11 5

zones nommées créées selon les options :

 Lignes du haut
Terrains ........... B1:B3
Maisons ............ C2:C3
Appartements .... D2:D3

 Colonne de gauche
Nord ............... B2:D2
Sud ................. B3:D3

2.4.4 Codification des zones nommées

Il est intéressant de normaliser les noms de zones afin d'identifier facilement les données afférentes à une zone
nommée.
 facilite le repérage entre les zones Excel et celles créées par l'utilisateur
 exemples :
C_libellé ...... cellule TC_libellé ...... tableau croisé
Z_libellé ...... zone de cellules MT_libellé ...... mode tableau

2.4.5 Repérage des zones nommées par des couleurs spécifiques

Appliquer des couleurs aux zones nommées en visualise les limites physiques.
 pratique en cas d'ajout de nouvelles données afin de déterminer si elles seront en ou hors zone
 permet à l'utilisateur de différencier chaque zone selon l'utilisation qui en est réservée

ACCÈS AUX INFORMATIONS 17

 EXCEL Perfectionnement

2.4.6 Gestion des zones nommées

 ATTENTION
PROBLÈME LORS DE LA COPIE D'UNE FEUILLE AVEC DES ZONES NOMMÉES : LORSQUE L'ON COPIE UNE FEUILLE CONTENANT UNE
ZONE NOMMÉE DE PORTÉE "CLASSEUR" DANS LE MÊME CLASSEUR OU DANS UN AUTRE CLASSEUR CETTE ZONE EST
TRANSFORMÉE DANS LA FEUILLE DESTINATION EN PORTÉE "FEUILLE"
 redéfinir les cellules d’une zone nommée
Formules / Noms définis / Gestionnaires de noms / Sélectionner la zone / Modifier… /
Fait référence à

 renommer une zone

Formules / Noms définis / Gestionnaires de noms / Sélectionner la zone / Modifier… / Nom
 supprimer une zone
Formules / Noms définis / Gestionnaires de noms / Sélectionner la zone / Supprimer

ACCÈS AUX INFORMATIONS 18

 EXCEL Perfectionnement

3 CONTRÔLE ET VALIDATION DE LA SAISIE

3.1 Contrôle de la saisie

Le contrôle de la saisie est primordial pour que les résultats des calculs exécutés soient valides. Il permet, en
utilisant différentes méthodes, d’alerter l’utilisateur d’une erreur potentielle dans sa feuille de calcul.
 chaque type de contrôle présente des avantages et des inconvénients dans l’ergonomie, la recherche
d’erreur et l'affichage
 exemple d'une application traitant de notes trimestrielles
HISTOIRE
ANGLAIS FRANÇAIS MATHS
GÉOGRAPHIE
7,0 12,0 16,0 13,0
20,0 17,0 10,0
12,0 19,0 11,0
8,0 8,0 10,0

3.2 Format de nombre personnalisé

À partir d’un format de nombre personnalisé, on peut afficher des valeurs numériques en couleur en fonction de
seuils (deux maximum).
 création
Accueil / Nombre / Nombre - Personnalisé / Type :
 couleurs
les couleurs sont saisies entre [ ] et avant les codes format
blanc - bleu - cyan - jaune - magenta - noir - rouge - vert
exemple : [bleu]0,0
 conditions
les conditions sont saisies entre [ ] et séparées par un ;
elles permettent d'appliquer un format uniquement lorsque la valeur répond à la condition
opérateurs de comparaison : < <= > >= = <>
exemple : [vert][>500]0,00
 exemple
dans ce tableau les notes s'affichent en rouge quand elles sont inférieures à 0 ou supérieures à 20

les deux premiers formats traitent respectivement les valeurs inférieures au premier seuil ou supérieures
au deuxième seuil, le dernier format traite toutes les autres valeurs
HISTOIRE-
ANGLAIS FRANÇAIS MATHS
GÉOGRAPHIE
7,0 12,0 23,0 - 1,0
20,0 17,0 10,0
- 1,0 19,0 11,0
21,0 1,0 10,0

CONTRÔLE ET VALIDATION DE LA SAISIE 19

 EXCEL Perfectionnement

3.3 Mise en forme conditionnelle

À partir de l'outil Mise en forme conditionnelle, on peut traiter le remplissage des cellules contenant des valeurs
numériques en couleur (plusieurs seuils gérés par des règles peuvent être évoqués pour un basculement en
couleur du fond des cellules).
 avantage .......... facile à mettre en œuvre, ergonomique de par sa visualisation
 inconvénient ..... les fonds de cellule basculent en orange quand une cellule contient du texte,
cas prévisible si un enseignant saisit le libellé "absent" dans une cellule
 dans ce tableau le remplissage des cellules basculent en orange quand la valeur numérique de la cellule
est inférieure à 0 ou supérieure à 20
HISTOIRE-
ANGLAIS FRANÇAIS MATHS
GÉOGRAPHIE
7,0 12,0 23,0 -1,0
20,0 17,0 10,0
-1,0 19,0 11,0
21,0 1,0 10,0

3.4 Contrôle par formule

Permet de trouver une erreur de saisie dans un "flot" de données et affiche un message d'erreur ou bascule en
couleur le fond d'une cellule.
 avantage .......... on peut tester un très grand nombre de cellules
 inconvénient ..... ne spécifie pas l'adresse de la ou des cellules dont la saisie est erronée
 dans cet exemple le contrôle s'effectue dans une cellule externe au tableau
HISTOIRE- ALERTE ALERTE
ANGLAIS FRANÇAIS MATHS
GÉOGRAPHIE PAR MESSAGE PAR COULEUR
7,0 12,0 23,0 -1,0 Erreur
20,0 17,0 10,0
-1,0 19,0 11,0
21,0 1,0 10,0

CONTRÔLE ET VALIDATION DE LA SAISIE 20

 EXCEL Perfectionnement

3.4.1 Alerte avec formule

 alerte avec formule affichant un message d'avertissement

 ces formules permettront des comparaisons avec des seuils prédéfinis ou avec des seuils critiques
=SI(OU(MIN(B4:E7)<0;MAX(B4:E7)>20);"Erreur";"")
fonctions imbriquées, Si, Ou, Min, Max

 ou le Min est inférieur 0, ou le Max est supérieur à 20 : déclenche l'affichage du libellé "Erreur"
ALERTE
PAR MESSAGE
Erreur

3.4.2 Alerte avec Mise en forme conditionnelle

 alerte avec formule dans Mise en forme conditionnelle modifiant la couleur de fond
 =OU(MIN(B4:E7)<0;MAX(B4:E7)>20)
Accueil / Style / Mise en forme conditionnelle / Nouvelle règle
Type de règle : utiliser une formule pour déterminer pour quelles cellules le format sera appliqué

 le fond de cellule bascule en orange dès que les conditions de la règle sont appliquées
ALERTE
PAR COULEUR

3.5 Outil validation

La validation des données permet de contrôler le type de données et les valeurs que les utilisateurs saisissent
dans une cellule.
 par exemple, possibilité de restreindre la saisie des données à une certaine plage de dates, de limiter
les choix en utilisant une liste ou de limiter la saisie à des nombres entiers…
 Données / Outils de données / Validation des données

CONTRÔLE ET VALIDATION DE LA SAISIE 21

 EXCEL Perfectionnement

 Options de validation

 Message de saisie
vous pouvez personnaliser le texte du message de saisie que les utilisateurs voient
si vous choisissez de ne pas le personnaliser, les utilisateurs verront un message par défaut

 Alerte d'erreur
vous pouvez personnaliser le texte du message d’alerte d’erreur que les utilisateurs voient
si vous choisissez de ne pas le personnaliser, les utilisateurs verront un message par défaut

CONTRÔLE ET VALIDATION DE LA SAISIE 22

 EXCEL Perfectionnement

 types d'alertes d'erreur

Empêcher les utilisateurs de saisir des données non valides dans une
Arrêter cellule. Un message d’alerte Arrêter propose deux options :
Recommencer ou Annuler

Avertir les utilisateurs que les données saisies ne sont pas valides,
sans les empêcher de les saisir. Lorsqu’un message d’alerte
Avertissement s’affiche, les utilisateurs peuvent cliquer sur :
Avertissement
Oui pour accepter l’entrée non valide
Non pour modifier l’entrée non valide
Annuler pour supprimer l’entrée non valide

Informer les utilisateurs que les données entrées ne sont pas valides,
sans les empêcher de les saisir. Ce type de message d’erreur est le
plus souple. Lorsqu’un message d’alerte Information s’affiche, les
Informations
utilisateurs peuvent cliquer sur :
OK pour accepter la valeur
Annuler pour la refuser

 affichage des messages d'alertes

les messages de saisie et les alertes d’erreur s’affichent uniquement lorsque des données sont saisies
directement dans les cellules
ils n’apparaissent pas dans les conditions suivantes :
 un utilisateur entre des données dans la cellule par copie ou remplissage
 une formule dans la cellule calcule un résultat qui n’est pas valide
 une macro entre des données non valides dans la cellule
 avantage ............. permet de conserver les formats de nombre, les formats conditionnels
et d'identifier l'erreur de saisie
 inconvénient ........ ne fonctionne pas sur des cellules déjà saisies

3.6 Contrôles de formulaire

Les contrôles formulaire permettent d'agir avec des objets graphiques pour renseigner le contenu d'une cellule :
ils utilisent des valeurs numériques ou logiques plus faciles à tester que des "chaines de caractères".
 pour accéder aux contrôles de formulaire, il faut activer l'onglet Développeur dans le Ruban
Fichier / Options / Personnaliser le ruban /  Développeur

 Pour insérer un contrôle

Développeur / Contrôles / Insérer

CONTRÔLE ET VALIDATION DE LA SAISIE 23

 EXCEL Perfectionnement

 contrôles utilisables dans une feuille de calcul

Bouton permet de déclencher une macro-commande

Zone de liste
déroulante

 renvoie la position dans la liste

Case à cocher

 renvoie la valeur Vrai / Faux

Toupie

 renvoie la valeur choisie

Zone de liste

 renvoie la position dans la liste

CONTRÔLE ET VALIDATION DE LA SAISIE 24

 EXCEL Perfectionnement

Case d'option

utilisée dans les Zones de groupe

Zone de groupe

permet de regrouper des Cases d'option

 renvoie le n° de la Case d'option dans la Zone de groupe

Étiquette Non utilisée

Barre de
défilement

 renvoie la valeur choisie

CONTRÔLE ET VALIDATION DE LA SAISIE 25

 EXCEL Perfectionnement

3.7 Repérage des saisies

Il peut être utile de repérer les différentes zones de saisie par des couleurs spécifiques.
 utiliser la présentation des cellules pour faciliter le repérage de l'utilisateur
 adopter une codification de couleur selon la saisie à effectuer
 utilisation des styles Excel
Accueil / Style / Styles de cellules

CONTRÔLE ET VALIDATION DE LA SAISIE 26

 EXCEL Perfectionnement

4 CONTRÔLES DES RÉSULTATS

4.1 Vérification des résultats

4.1.1 Contrôle par formule de calculs

Créer des formules de contrôles permettant d'effectuer des vérifications pertinentes par rapport aux données
utilisées.
 ces formules permettront des comparaisons avec des seuils prédéfinis ou avec des seuils critiques
 elles seront également mise en œuvre afin de déclencher des alertes visuelles

4.1.2 Mise en forme conditionnelle

Elle permettra d'appliquer des mises en forme répondant à une condition.

ces mises en forme sont limitées aux options qui ne remettent pas en cause les
dimensions de la cellule
exemple :. taille de la police non modifiable, bordures limitées aux traits fins…

 mettre en œuvre les mises en forme conditionnelles prédéfinies

Accueil / Style / Mise en forme conditionnelle / Règles de mise en surbrillance des cellules
 créer une règle de mise en forme spécifique
 Accueil / Style / Mise en forme conditionnelle / Nouvelle règle…
Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué
 Appliquer une mise en forme aux valeurs pour lesquelles cette formule est vraie
 saisir la formule conditionnelle correspondante (commence par =)

si la mise en forme conditionnelle s'applique sur la colonne entière,

il faut utiliser les références de cellule en 1ère ligne, sinon on observera un
décalage de la mise en forme… exemple : =mois(A1)=7 et non pas =mois(A2)=7

4.1.3 Fonctions dans la barre d'état Excel

Il est possible d'afficher des calculs statistiques dans la barre d'état d'Excel.
 sélection des fonctions à afficher :
bouton droit dans la barre d'état / valider les fonctions voulues
exemple : Moyenne et Somme

 mise en œuvre :
il faut sélectionner au moins 2 cellules pour qu'Excel affiche les fonctions statistiques
exemple : cellules avec valeurs 120 et 40

CONTRÔLES DES RÉSULTATS 27

 EXCEL Perfectionnement

4.1.4 Indicateur d'erreur

L'indicateur d'erreur, sous forme de triangle vert par défaut, permet un repérage visuel des valeurs ou des
formules qui diffèrent des autres dans une même colonne.
 paramétrage
Fichier / Options / Formules - Vérification des erreurs
 Activer la vérification des erreurs en arrière-plan
Indiquer les erreurs à l'aide de cette couleur : choisir la couleur voulue
 repérage par triangle vert en haut à gauche des cellules concernées
75010
75010
75009
75004

 en sélectionnant la cellule concernée un apparaît sur la gauche :

déplacer le curseur sur ce permettra d'afficher l'erreur rencontrée

 options de la liste déroulante

 ignorer l'erreur fera disparaître la notification d'erreur, pour réafficher les erreurs repérées :
Fichier / Options / Formules - Vérification des erreurs / [Rétablir les erreurs ignorées]
 paramétrage
Fichier / Options / Formules - Vérification des erreurs
 Activer la vérification des erreurs en arrière-plan
Indiquer les erreurs à l'aide de cette couleur : choisir couleur voulue

CONTRÔLES DES RÉSULTATS 28

 EXCEL Perfectionnement

4.2 Gestion des erreurs renvoyées

4.2.1 Fonction SIERREUR

Cette fonction permet d’intercepter et de gérer des erreurs présentes dans une formule :
elle renvoie une valeur spécifique si une formule génère une erreur, sinon elle renvoie le résultat de la formule.
 SIERREUR(valeur;valeur_si_erreur)
valeur ..................... représente l'argument vérifié (valeur, expression ou référence)
valeur_si_erreur ........ représente la valeur à renvoyer si une formule génère une erreur
les types d’erreur suivants sont évalués : #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, #NUL!
 exemple : intercepter l'erreur en cas de division par 0
A B exemple : intercepter l'erreur en cas de division par 0
1 12 21
2 0 3 SIERREUR
3 valeur ................ A1/A2
4 irréalisable 7 valeur_si_erreur ... "irréalisable"

4.2.2 Fonctions EST

Chacune de ces fonctions, regroupées sous l'appellation de fonctions EST, vérifie la valeur spécifiée et renvoie
VRAI ou FAUX, selon le cas.
 par exemple, la fonction ESTVIDE renvoie la valeur logique VRAI si l'argument valeur est une référence à
une cellule vide…
 ESTVIDE(valeur) / ESTERR(valeur) / ESTERREUR(valeur) / ESTLOGIQUE(valeur) / ESTNA(valeur) /
ESTNONTEXTE(valeur) / ESTNUM(valeur) / ESTREF(valeur) / ESTTEXTE(valeur)
valeur ............. .valeur à tester
peut être une valeur vide (cellule vide), d’erreur, une valeur logique, de texte, de nombre ou une valeur
de référence ou un nom s’y référant
 renvoient la valeur VRAI lorsque :
ESTVIDE
la valeur fait référence à une cellule vide
ESTERR
la valeur fait référence à n’importe quelle valeur d’erreur, à l’exception de #N/A
ESTERREUR
la valeur fait référence à une des valeurs d’erreur
#N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM? ou #NUL!
ESTLOGIQUE
la valeur fait référence à une valeur logique
ESTNA
la valeur fait référence à la valeur d’erreur #N/A (valeur non disponible)
ESTNONTEXTE
la valeur fait référence à tout élément qui n’est pas du texte ; notez que cette fonction renvoie la
valeur VRAI si la valeur fait référence à une cellule vide
ESTNUM
la valeur fait référence à un nombre
ESTREF
la valeur fait référence à une référence
ESTTEXTE
la valeur fait référence à du texte

les fonctions EST sont utiles pour tester le résultat de calculs dans des
formules associées à la fonction SI
elles représentent un moyen de repérer des erreurs dans les formules

CONTRÔLES DES RÉSULTATS 29

 EXCEL Perfectionnement

5 FONCTIONS AVANCÉES
5.1 Fonctions logiques

5.1.1 Condition simple SI

Permet de rendre conditionnel le contenu d’une cellule.

 =SI(test logique;valeur si vrai;valeur si faux)
test logique comparaison entre 2 valeurs : cellule, constante ou formule
valeur si vrai / faux valeur retournée lorsque le test logique est vrai / faux
 test logique
opérateurs de comparaison :
= égal <> différent
> supérieur >= supérieur ou égal
< inférieur <= inférieur ou égal
comparaisons :
valeur texte C5 = "Paris"
valeur numérique C5 <> 3
valeur date C5 <= DATE(2014;12;25) ou C5 <= "25/12/2014"
fonction C5 > MOYENNE(D3:D6)
formule C5 < 6*(B28-15)
autre cellule C5 >= B3
Remarque
pas de différence entre minuscule et majuscule mais différence entre lettres accentuées ou non
 valeur si vrai / valeur si faux
valeur texte "Paris"
valeur numérique 3
valeur date DATE(2014;12;25) ou "25/12/2014"
fonction MOYENNE(D3:D6)
formule 6*(B28-15)
autre cellule B3

5.1.1 Conditions multiples ET - OU - NON

Permet d’évaluer plusieurs conditions dans le même test logique.

 combinez les conditions entre elles avec les opérateurs booléens : ET – OU - NON
Remarque
possibilité d'utiliser les parenthèses afin de rendre prioritaire certaines conditions
 fonction ET(condition1;condition2;condition3…)
cond.1 cond.2 ET exemple :
Faux Faux Faux ET(C6<=10;D3=“Paris”)
Faux Vrai Faux
Vrai Faux Faux
Vrai Vrai Vrai
 fonction OU(condition1;condition2;condition3…)
cond.1 cond.2 OU exemple :
Faux Faux Faux OU(A4<>12;B8=0)
Faux Vrai Vrai
Vrai Faux Vrai
Vrai Vrai Vrai
 fonction NON(condition)
cond. NON exemple :
Faux Vrai NON(ET(D2<=26500;D3=200))
Vrai Faux

FONCTIONS AVANCÉES 30
 EXCEL Perfectionnement

5.2 Fonctions statistiques conditionnelles

5.2.1 Comptage conditionnel NB.SI

Comptage conditionnel dans une zone de cellules.

 =NB.SI(plage;critère)
plage zone de cellules dans laquelle s'effectuera la recherche
critère critère de recherche
A B exemple : compter le nombre de ventes supérieures
1 Bordeaux 21000 € à 20000 €
2 Cannes 18000 €
3 Rennes 23000 € NB.SI
4 plage ..... B1:B3
5 2 critère .... ">20000"

5.2.2 Somme et Moyenne conditionnelle SOMME.SI MOYENNE.SI

Somme/moyenne conditionnelle dans une zone de cellules. Deux syntaxes possibles.

 calculs et critères dans la même zone
=SOMME.SI(plage;critère) =MOYENNE.SI(plage;critère)
plage .......... zone de cellules comparées aux critères
critère ........ critère de recherche
A B exemple : additionner les ventes supérieures à 20000€
1 Bordeaux 21000 €
2 Cannes 18000 € SOMME.SI
3 Rennes 23000 € plage ............... B1:B3
4 critère ............. ">20000"
5 44000 € somme_plage
 calculs et critères dans des zones différentes
=SOMME.SI(plage;critère;somme_plage) =MOYENNE.SI(plage;critère;moyenne_plage)
plage .......... zone de cellules comparées aux critères
critère ........ critère de recherche
somme_plage zone de cellules additionnées lorsque les critères sont vrais
A B C exemple :
1 Redon Nord 15000 € additionner CA des vendeurs région Nord
2 Leroy Sud 19000 € SOMME.SI
3 Charvet Nord 24000 € plage ............... B1:B3
4 critère.............. "Nord"
5 39000 € somme_plage ..... C1:C3

FONCTIONS AVANCÉES 31
 EXCEL Perfectionnement

5.2.3 Critères

 opérateurs de comparaison
= égal <> différent
> supérieur >= supérieur ou égal
< inférieur <= inférieur ou égal
 jokers
? 1 caractère * tous les caractères
 critères : diffèrent selon le type de donnée
égalité différence comparaison
cellules vides "" "<>"&""
texte "Paris" "<>Paris" ">=Paris"
texte avec jokers "C" "<>"&"C" ">="&"C"
nombre 12 ou "12" "<>12" ">=12"
cellule C26 "<>"&C26 ">="&C26
formule MIN(B1:B15) "<>"&MIN(B1:B15) ">="&MIN(B1:B15)

5.3 Tables d'hypothèses

5.3.1 Principes

 une table d’hypothèse est une zone de cellules qui fournit un raccourci évitant de recopier plusieurs
formules en une opération
 elle permet de calculer automatiquement des résultats à partir d’une ou deux séries de données, toute
modification de valeur ou formule effectuant une mise à jour des résultats
 sélectionner zone de cellules,
puis Données / Outils de données / Analyse scénarios / Table de données…
définir ensuite les cellules d’entrées, qui doivent rester vides : elles recevront les différentes valeurs
des séries de données utilisées par les formules

5.3.2 Tables à 1 entrée

 1 série de données en ligne, plusieurs formules

A B C D
1 cellule d’entrée en ligne
2
3 formules 1 250 km 1 500 km 2 000 km saisie km mensuel
4 =A1*12 15 000 km 18 000 km 24 000 km calcul km annuel
5 =A1/30 41,6 km 50,0 km 66,6 km calcul km quotidien

FONCTIONS AVANCÉES 32
 EXCEL Perfectionnement

 1 série de données en colonne, plusieurs formules

A B C D
1 cellule d’entrée en colonne
2
3 =A1*12 =A1/30 formules
4 1 250 km 15 000 km 41,6 km
5 1 500 km 18 000 km 50,0 km
6 2 000 km 24 000 km 66,6 km
saisie km calcul km calcul km
mensuel annuel quotidien

5.3.3 Tables à 2 entrées

 2 séries de données en ligne et colonne, 1 seule formule

A B C D E
1
2 cellule d’entrée en ligne cellule d’entrée colonne
3
4 formule = A2 / 100 * D1 1 000 km 1 200 km 1 400 km saisie km
5 5,8 L/100 58,00 € 69,60 € 81,20 € mensuels
6 6,2 L/100 62,00 € 74,40 € 86,80 €
7 6,9 L/100 69,00 € 82,80 € 96,60 €
saisie calcul montant consommation
consommation mensuelle

5.4 Fonctions texte

Les fonctions de texte permettent de travailler les chaînes de caractères. L’opérateur de texte (concaténation)
est le signe &. Les constantes de texte sont délimitées par des guillemets.
 CNUM convertit un argument textuel en nombre
CTXT convertit un nombre au format texte avec un nombre de décimales spécifié
T convertit ses arguments en texte
TEXTE convertit un nombre au format texte
exemple de fonction de transformation de chaînes :
="Paris, le "&TEXTE(AUJOURDHUI();"jjjj j mmmm aaaa")
le résultat sera : Paris, le mardi 14 octobre 2014

FONCTIONS AVANCÉES 33
 EXCEL Perfectionnement

 GAUCHE renvoie des caractères situés à l’extrême gauche d’une chaîne de caractères
DROITE renvoie des caractères situés à l’extrême droite d’une chaîne de caractères
STXT renvoie un nombre déterminé de caractères d’une chaîne de texte à partir de la
position que vous indiquez
exemple de fonction d’extraction de caractères d’une chaîne :
=SI(GAUCHE(A13;2)="di";valeur_si_vrai;valeur_si_faux)
le résultat peut être 1 ou 2
 MAJUSCULE convertit le texte en majuscules
MINUSCULE convertit le texte en minuscules
NOMPROPRE met en majuscules la première lettre de chaque mot d'une chaîne textuelle
exemple de fonction de changement de casse :
=MAJUSCULE(A2)
le texte sera en MAJUSCULES
 CHERCHE trouve un texte dans un autre texte (sans respecter la casse)
EXACT vérifie si deux valeurs de texte sont identiques
TROUVE trouve un valeur textuelle dans une autre, en respectant la casse
exemple de fonction de texte :
=CHERCHE("x";"excel")
cette fonction renvoie la valeur 2, car « x » est la deuxième lettre du mot "excel"
 NBCAR renvoie le nombre de caractères contenus dans une chaîne de texte
REPT répète un texte un certain nombre de fois
exemple d’utilisation de ces fonctions combinées ensemble :

 EPURAGE supprime tous les caractères de contrôle du texte

REMPLACER remplace des caractères dans un texte
SUBSTITUE remplace l’ancien texte d’une chaîne de caractères par un nouveau
SUPPRESPACE supprime les espaces du texte
exemple de fonction de validation et correction de saisie
SUPPRESPACE() pour restructurer ces données :

FONCTIONS AVANCÉES 34
 EXCEL Perfectionnement

5.5 Fonctions dates

Ces fonctions permettent de travailler sur les dates et heures avec la possibilité d'en extraire certaines
informations ou de calculer une nouvelle date à partir de valeurs spécifiées.
 ANNEE année d'une date
MOIS mois d'une date
FIN.MOIS date du dernier jour du précédant/suivant une date du nombre de
mois indiqué
MOIS.DECALER date à partir d'une date corrigée +/- le nombre de mois indiqué
NO_SEMAINE n° de la semaine dans l'année
JOUR jour d'une date
JOURSEM n° du jour de la semaine
SERIE.JOUR.OUVRE date avant/après un nombre de jours ouvrés
HEURE heure d'une temps
MINUTE minutes d'un temps
SECONDE secondes d'un temps
 AUJOURDHUI date du jour
MAINTENANT date et heure en cours
 DATE date à partir de l'année, du mois et du jour
DATEVAL date à partir d'une chaîne de caractère
TEMPS temps à partir des heures, minutes et secondes
TEMPSVAL temps à partir d'une chaîne de caractère
 JOURS360 nombre de jours entre 2 dates sur la base d'une année de 360 j
(année comptable)
NB.JOURS.OUVRES nombre de jours ouvrés entre 2 dates
FRACTION.ANNEE fraction de l'année représentant le nombre de jours entiers entre
dates début/fin
 DATEDIF nombre de jours, de mois ou d'années entre 2 dates
DATEDIF(date_début;date_fin;unité)
unité "y" ............ nombre d'années entières dans la période
"m" ........... nombre de mois entiers dans la période
"d" ............ nombre de jours dans la période
"ym" .......... différence entre le mois de date_début et date_fin sans tenir compte des jours
ni des années
"yd" .......... différence entre le jour de date_début et date_fin sans tenir compte des années
"md" ......... différence entre le jour de date_début et date_fin sans tenir compte des mois
ni des années
exemple :
DATEDIF(05/04/2010;10/07/2012;unité)
"y" ............ 2
"m" ........... 27
"d" ............ 827
"ym" .......... 3
"yd" .......... 96
"md" ......... 5

FONCTIONS AVANCÉES 35
 EXCEL Perfectionnement

6 LISTES DE DONNÉES
6.1 Organisation des données

Quelques règles sont à respecter pour optimiser l’utilisation de Tableaux de listes de données.
 ne garder qu'une seule ligne d'entête
 saisir des entêtes pour chaque colonne
 pas de ligne ni de colonne vide dans ces tableaux
 afin de permettre la copie des formules de calculs, il faut conserver le même sens des données dans les
autres tableaux

6.2 Filtres automatiques

6.2.1 Mise en œuvre

 placer le curseur dans tableau, Données / Trier et filtrer / Filtrer :

une liste déroulante apparaît dans chaque entête permettant de filtrer les données
 les colonnes filtrées sont repérées par le symbole
les numéros de lignes correspondantes sont en bleu
le nombre d'enregistrements apparaît en barre d’état
Remarque
(Vides) apparaît dans les valeurs du filtre si la colonne contient au moins une cellule vide

6.2.2 Filtre selon le type de données

Ces filtres permettent de sélectionner des valeurs selon des comparaisons prédéfinies et dépendant du type de
données.
 cliquez sur la liste de choix de l'en-tête d'une colonne / Filtres…

 textuels ........... est égal - est différent - commence par - se termine par - contient…
 numériques ...... est égal - est différent - supérieur - supérieur ou égal - inférieur…
 chronologiques .. est égal - avant - après - entre - demain - aujourd'hui - hier…

LISTES DE DONNÉES 36
 EXCEL Perfectionnement

6.2.3 Filtre par recherche

Il permet de sélectionner des valeurs, de les combiner et d'utiliser des jokers dans les recherches.
 cliquez sur la liste de choix de l'en-tête d'une colonne / Rechercher

 valeur ............. contient valeur ri

 joker 
remplace tout caractère piet Pianellat - Pillet - Pivetot
valeur ........... commence par mal Malet - Malepart - Malevot
valeur ........... termine par les Dales - Desruelles - Sanèles
 joker ?
remplace 1 caractère a??a Airault - Alba - Allagnat
???? Huet - Thin - Viel
 ajouter une sélection

l'option "Ajouter la sélection actuelle au filtre" permet de combiner

différentes recherches entre elles

 Ajouter la sélection actuelle au filtre  Ajouter la sélection actuelle au filtre

la recherche remplace la précédente la recherche se combine avec la précédente
Cadenes Cadenes Perlat
Cadic Cadic Perring
Cadoret Cadoret Perrusset

LISTES DE DONNÉES 37
 EXCEL Perfectionnement

6.2.4 Filtre personnalisé

Le filtre personnalisé permet de combiner 2 conditions avec ET/OU.

 cliquez sur la liste de choix de l'en-tête d'une colonne / Filtres… / Filtre personnalisé…

6.2.5 Calculs statistiques sur les valeurs filtrées

Ces calculs ne concernent que les cellules affichées à travers un filtre.

 fonction SOUS.TOTAL
une seule fonction dont le calcul dépend du code fonction choisi
Remarque
lorsque l'on utilise la fonction Somme automatique dans une feuille contenant un tableau filtré
elle est modifiée par Excel en fonction Sous.Total…
 syntaxe
SOUS.TOTAL(code_fonction;référence)
code_fonction ........... 1:Moyenne - 2:Nb (compte les valeurs numériques) - 3:NbVal (compte toute
valeur) 4:Max - 5:Min - 6:Produit - 7:EcartType - 8:EcartTypeP
9:Somme - 10:Var - 11:VarP

6.2.6 Annulation des filtres

 filtre d'un champ ........ Effacer le filtre de…

 tous les filtres ........... Données / Trier et filtrer / Effacer
 filtre automatique ...... Données / Trier et filtrer / Filtrer

6.2.7 Enregistrement des filtres

 Remarque
ne fonctionne pas en mode Tableau…
 commencer par enregistrer un affichage normal…

LISTES DE DONNÉES 38
 EXCEL Perfectionnement

 poser le filtre voulu, par exemple salaires < 2500, puis créer un affichage personnalisé
Affichage / Affichages classeur / Personnalisé /
Nom : "Salaires inférieurs à 2500€
 Paramètres d’impression
 Paramètres masqués des lignes, colonnes et filtres

6.3 Filtre avancé

6.3.1 Principes

Permet de filtrer/d’extraire des données selon des critères avancés.

 il faut différentes zones de cellules pour mettre en œuvre le filtre avancé
Remarques
l'orthographe des entêtes est importante
les données d'extraction sont mises à jour à chaque exécution du filtre
 une zone de données
correspond au tableau de données
ex. A1:F9
 une zone de critères
saisir les entêtes et une ou plusieurs lignes de critères
ex. A11:B12
 une zone d’extraction
uniquement si action : Copier vers un autre emplacement
saisir une seule ligne avec les entêtes correspondantes aux données à extraire
ex. A14:D14
A B C D E F
1 Code Prénom Nom Enfant Salaire Ville
2 146 Anne HERICOURT 2 1 313,91 € PARIS
3 82 Catherine PROVOST 0 1 357,33 € NANTOUX
4 114 Jeanne TOUDON sans 1 460,16 € PARIS
5 145 Juliette BLASQUIZ 2 1 936,12 € PARIS
6 67 Pierre MICHEL 4 1 631,96 € MONSÉGUR
7 181 Sébastien BAZIN 1 749,04 € PARIS
8 193 Bertrand PARDOUX 1 1 749,90 € PASSENANS
9 186 Virginie CAZAUBON 3 1 679,87 € PARIS
10
11 Ville Enfant CRITÈRES
DONNÉES
12 PARIS 2
13
14 Code Nom Enfant Ville
15 146 HERICOURT 2 PARIS EXTRACTION
16 145 BLASQUIZ 2 PARIS

LISTES DE DONNÉES 39
 EXCEL Perfectionnement

6.3.2 Création

 cliquez dans une cellule du tableau

Données / Trier et filtrer / Avancé
 Filtrer la liste  Copier vers un autre emplacement
sur place option :  Extraction sans doublon

6.3.3 Critères de recherche

 si une valeur de critère est vide, le critère est ignoré

 ET logique entre chaque colonne de critères
 OU logique entre chaque ligne de critères
Remarque
ne pas inclure de lignes vides dans la zone de critères !
 critères
cellules vides ....................... =
cellules non vides .................. <>
égal à 999 ........................... 999
différent de 999 .................... <>999
supérieur à 999 ..................... >999
supérieur ou égal à 999 ........... >=999
inférieur à 999...................... <999
inférieur ou égal à 999 ............ <=999
égal à "xxx".......................... ="=xxx"
différent de "xxx" .................. <>xxx ou ="<>xxx"
commence par "xxx" ............... xxx ou ="=xxx*"
ne commence pas par "xxx" ...... ="<>xxx"
contient "xxx" ....................... xxx ou ="=xxx"
ne contient pas "xxx" .............. ="<>xxx"
se termine par "xxx" ............... ="=xxx"
ne se termine pas par "xxx" ...... ="<>xxx"
 combinaison de critères
commence par BO, contient RI, se termine par LES
="=BORILES"
 fonction utilisée dans les critères
=F2>MOYENNE(F$2:F$300) ou =F2>MOYENNE(F:F)
ou =F2>MOYENNE(liste_salaire)
Remarques
utiliser des références absolues dans la fonction
ne pas utiliser une entête de colonne comme entête de critère avec fonction
 saisir un nom différent
Ville Critère salaire
PARIS =F2>MOYENNE(F:F)

LISTES DE DONNÉES 40
 EXCEL Perfectionnement

6.3.4 Fonctions "liste de données"

Ces fonctions permettent d'effectuer des calculs sur les listes de données, elles nécessitent une zone de critère.
 syntaxe
NOMFONCTION(zone_tableau;n°colonne ou " nom_colonne ";zone_critères)
 fonctions statistiques
somme : BDSOMME - moyenne : BDMOYENNE - mini : BDMIN - maxi : BDMAX
=BDSOMME(A1:F9;4;A11:A12) ou =BDSOMME(A1:F9;"Enfant";A11:B12)
résultat : 7
 compter les cellules contenant des valeurs numériques
=BDNB(A1:F9;"Enfant";A11:A12)
résultat : 3
 compter les cellules contenant toute valeur
=BDNBVAL(A1:F9;"Enfant";A11:A12)
résultat : 4
 compter le nombre de lignes
 il faut omettre le 2e argument
=BDNB(A1:F9;;A11:A12)
résultat : 5
 extraire une donnée d'une ligne répondant au critère
Remarques
si plusieurs valeurs rencontrées ........ #NOMBRE
si aucune valeur rencontrée ............. #VALEUR
poser le critère sur une colonne contenant des valeurs uniques afin d'obtenir une seule réponse…
Code
145
ex. avec critère sur colonne "Code"
=BDLIRE(A1:F9;"Prénom";A11:A12)
résultat : Juliette

6.4 Consolidation

6.4.1 Principes

La consolidation permet d'effectuer des calculs statistiques à partir de plusieurs tableaux sources.
 la consolidation peut s'effectuer en se référant soit aux emplacements des cellules soit aux entêtes de
lignes et colonnes
 les tableaux sources peuvent provenir de la même feuille, de feuilles ou de classeurs différents
 Remarque
Microsoft recommande de créer les consolidations avec les tableaux croisés, qui offrent plus de
souplesse, la commande "Consolidation" étant conservée pour des raisons de compatibilités

LISTES DE DONNÉES 41
 EXCEL Perfectionnement

6.4.2 Consolidation à partir des emplacements

 il faut que les tableaux sources soient créés avec la même disposition car la consolidation s'effectuera
sur les cellules de même emplacement
 se placer dans la 1ère cellule de résultat du tableau consolidé
Données / Outils de données / Consolider
Fonction : Somme - Nombre - Moyenne - Max - Min - Produit - Chiffres - Écartype – Écartypep
Var – Varp
sélectionner la 1ère zone de cellules, puis
faire de même avec chaque zone…

A B C D E F
1 JUILLET Caroline Marc Serge Natacha Annie
2 Haies 26 10 8
3 Arbustes 15 22
4 Fleurs 15 20
5 Arbres
6
7 AOUT Caroline Marc Serge Natacha Annie
8 Haies 10
9 Arbustes 8 13 14
10 Fleurs
11 Arbres 11
12
13 TOTAL Caroline Marc Serge Natacha Annie
14 Haies 26 10 8
15 Arbustes 23 35 14
16 Fleurs 15 20
17 Arbres 11

LISTES DE DONNÉES 42
 EXCEL Perfectionnement

6.4.3 Consolidation à partir des entêtes

 les tableaux sources peuvent être de dispositions différentes mais l'orthographe des étiquettes doit être
respectée car la consolidation s'effectuera à partir de celles-ci
 se placer dans à l'emplacement où l'on veut créer le tableau consolidé
Données / Outils de données / Consolider
Fonction : Somme - Nombre - Moyenne - Max - Min - Produit - Chiffres - Écartype – Écartypep
Var – Varp
sélectionner la 1ère zone de cellules, puis
faire de même avec chaque zone…
valider les options Étiquettes dans  Ligne du haut et/ou  Colonne de gauche

A B C D E F
1 JUILLET Caroline Marc Serge Natacha
2 Arbustes 15 22
3 Fleurs 15 20
4 Haies 26 10 8
5
6 AOUT Natacha Annie Marc
7 Arbres 11
8 Arbustes 13 14 8
9
10 TOTAL Caroline Annie Marc Serge Natacha
11 Arbres 11
12 Arbustes 14 23 35
13 Fleurs 15 20
14 Haies 26 10 8

 possibilité de faire des consolidations spécifiques en ne saisissant que les étiquettes voulues
TOTAL Annie Natacha Caroline
Arbustes 14 35
Fleurs 15

LISTES DE DONNÉES 43
 EXCEL Perfectionnement

 possibilité de lier la consolidation aux données

 Lier aux données source
ATTENTION
SI IL N'Y A PAS DE VALEUR DANS LES CELLULES SOURCES LES LIENS ET LES FORMULES DE CONSOLIDATION NE SERONT
PAS CRÉÉS !

 le tableau consolidé est créé en mode Plan

6.5 Mode "Tableau"

6.5.1 Principes

Excel permet de définir une zone de cellules en "mode Tableau".

 ceci permet de gérer et d'analyser les données, de filtrer les colonnes, d'ajouter une ligne de totaux et
d'appliquer une mise en forme prédéfinie au tableau
 les noms de colonnes sont saisis sur la 1ère ligne, les données sont saisies à la ligne suivante
ATTENTION
PAS DE COLONNES SANS NOM OU DE NOM FUSIONNÉ, PAS DE LIGNES OU DE COLONNES VIDES
CODE PRENOM NOM ENFANT SALAIRE CP VILLE
1 Anne HERICOURT 2 1 313,91 € 45140 INGRÉ
2 Pierre-Jean PROVOST 0 1 357,33 € 21190 NANTOUX
3 Jeanne CADIC 2 1 460,16 € 76460 SAINT-VALÉRY-EN-CAUX
4 Juliette BLASQUIZ 1 1 936,12 € 48800 SAINT-ANDRÉ-CAPCÈZE
5 Pierre-Yves MICHEL 1 1 631,96 € 64460 MONSÉGUR
6 Sébastien BAZIN 4 1 749,04 € 87130 SAINT-GILLES-LES-FORÊTS
7 Bertrand TOUDON 0 1 749,90 € 39230 PASSENANS
8 Virginie CAZAUBON 0 1 679,87 € 79270 FRONTENAY-ROHAN-ROHAN
9 Marion RACLE 0 1 202,82 € 87130 SAINT-GILLES-LES-FORÊTS
10 Chantal REMOND 2 2 256,08 € 21220 BÉVY

le filtre est activé automatiquement en mode Tableau

possibilité de le désactiver avec Données / Trier et filtrer / Filtrer

LISTES DE DONNÉES 44
 EXCEL Perfectionnement

6.5.2 Création

Transformer un tableau existant ou créer un tableau en "mode Tableau".

 transformer un tableau existant  cliquez dans une cellule du tableau
Accueil / Style / Mettre sous forme de tableau / choisir un style
sélectionner éventuellement la zone de cellules
valider l'option  Mon tableau comporte des en-têtes selon le tableau

 créer un nouveau tableau

Insertion / Tableaux / Tableau / étendre la sélection voulue
Remarque
l'option  Mon tableau comporte des en-têtes sera validée automatiquement…

une zone nommée "Tableau" est créée avec chaque nouveau tableau
elle est mise à jour automatiquement selon les données stockées et sera très
pratique à utiliser comme source d'un graphique ou d'un tableau croisé !

6.5.3 Présentation

Utilisation de styles prédéfinis.

 Accueil / Style / Mettre sous forme de tableau
ou Outils de tableau / Création / Styles de tableau / Styles rapides

LISTES DE DONNÉES 45
 EXCEL Perfectionnement

 possibilité de définir et d'enregistrer son propre style de tableau

Accueil / Style / Mettre sous forme de tableau / Nouveau style de tableau…
ou Outils de tableau / Création / Styles de tableau / Styles rapides Nouveau style…
saisir un nom de style, puis paramétrer chaque option…

 options de style
Outils de tableau / Création / Options de styles de tableau / sélectionner les options voulues

 effacer un style de tableau

Outils de tableau / Création / Styles de tableau / Effacer

6.5.4 Dimensions du tableau

On peut à tout instant redéfinir le nombre de lignes/colonnes concernées par le mode Tableau.
 ajout de ligne automatique
se positionner sur la dernière cellule en bas à droite du tableau,
puis () Tabulation  ajoute une nouvelle ligne
 possibilité de modifier le nombre de lignes/colonnes :
cliquez-glissez sur le coin inférieur droit pour augmenter/diminuer le nombre de lignes/colonnes

6.5.5 Sous-totaux

Possibilité d'ajouter des sous-totaux à chaque colonne.

 Outils de tableau / Création / Options de styles de tableau /  Ligne des totaux
choisir la fonction voulue pour chaque colonne :
Aucun - Moyenne - Nombre - Chiffres - Max - Min - Somme - EcartType – Var - Autres fonctions…
 ceci applique la fonction SOUS.TOTAL correspondante…

LISTES DE DONNÉES 46
 EXCEL Perfectionnement

6.5.6 Formule avec en-tête de colonne

Création de formule à partir des entêtes de colonne, ceci appliquera la formule sur l'ensemble des lignes.
 ceci permet de créer une seule formule pour l'ensemble du tableau
elle est mise à jour selon les lignes ajoutées
 saisir la formule dans la 1ère cellule d'une nouvelle colonne
elle sera appliquée automatiquement sur l'ensemble des lignes actuelles ou ajoutées
saisir les entêtes entre [ ]
exemple =[Adulte]+[Enfant]

6.5.7 Convertir un tableau en mode normal

Quitter le "mode Tableau" et convertir les cellules en tableau standard Excel.

Outils de tableau / Création / Outils / Convertir en plage

LISTES DE DONNÉES 47
 EXCEL Perfectionnement

7 GRAPHIQUES
7.1 Organiser les données d'un graphique

7.1.1 Définir l'emplacement des données

Choix de l'emplacement des données utilisées dans le graphique.

 il est possible de sélectionner une zone de cellules d'un tableau existant comme source de données d'un
graphique
 il est néanmoins recommandé de créer un tableau spécifique à chaque graphique
ces tableaux spécifiques seront liés aux données existantes et permettront d'ajouter des informations
propres à chaque graphique (exemple : moyenne globale des données) sans remettre en cause les
tableaux déjà créés
 il est judicieux de regrouper ces tableaux dans une seule feuille (exemple : T_graphiques) afin d'en
faciliter l'organisation et le repérage
 lorsque le tableau source d’un graphique est en mode Tableau l’ajout de données supplémentaires se
répercutera automatiquement sur le graphique

7.1.2 Création d'un graphique

Se reporter au memento "EXCEL Prise en main".

7.2 Modifier la source des données

7.2.1 Graphique incorporé

 en sélectionnant le graphique, les cellules correspondantes apparaissent entourées de cadres :

violet pour les catégories – vert pour les séries – bleu pour les valeurs
 modifier la source des données en cliquant-glissant sur une des poignées

GRAPHIQUES 48
 EXCEL Perfectionnement

7.2.2 Feuille graphique

 modifier la source des données avec :

Outils de graphique / Création / Données / Sélectionner des données /
Plage de données du graphique (utilisable également avec les graphiques incorporés)
 possibilité de faire des sélections multiples !

7.3 Gérer les séries de données

7.3.1 Ajouter une nouvelle série

 Outils de graphique / Création / Données / Sélectionner des données / Ajouter

 Nom de la série ......... sélectionner une cellule contenant le nom voulu ou saisir ce nom
Valeurs de la série ...... sélectionner les cellules correspondantes

7.3.2 Modifier une série

 Outils de graphique / Création / Données / Sélectionner des données

sélectionner la série / Modifier
 mettre à jour le Nom de la série et/ou les Valeurs de la série

7.3.3 Supprimer une série

Outils de graphique / Création / Données / Sélectionner des données

sélectionner la série / Supprimer

GRAPHIQUES 49
 EXCEL Perfectionnement

7.4 Modifier l'emplacement d'un graphique

7.4.1 Graphique incorporé en feuille graphique

 sélectionner le graphique
Outils de graphique / Création / Emplacement / Déplacer le graphique /  Nouvelle feuille
saisir le nom voulu

7.4.2 Feuille graphique en graphique incorporé

 sélectionner le graphique
Outils de graphique / Création / Emplacement / Déplacer le graphique /  Objet dans
sélectionner la feuille voulue

7.5 Graphiques superposés

7.5.1 Principes

 les graphiques superposés permettent de combiner différents types de graphiques entre eux
exemple : ventes annuelles en histogrammes et CA en courbes

 lorsque les données à comparer sont très différentes entre elles, il est possible de représenter certaines
séries de données sur l'axe vertical de gauche et les autres sur l'axe vertical de droite
exemple : comparer des ventes annuelles (0-60) et des CA (0-450)

GRAPHIQUES 50
 EXCEL Perfectionnement

7.5.2 Répartition des séries

Affectation des séries sur les axes verticaux principal et secondaire.

 les séries du graphique principal sont affichées sur l'axe vertical principal (à gauche)
les séries du graphique secondaire sont affichées sur l'axe vertical secondaire (à droite)
chaque échelle verticale est calculée selon les données qui y sont rattachées
 Outils de graphique / Disposition / Sélection active / sélectionner la série voulue, puis
Mise en forme de la sélection / Options des séries /  Axe principal -  Axe secondaire

les graphiques étant superposés, certains points de donnés peuvent être

masqués par d'autres… afin d'éviter cela, utiliser des graphiques de type
Courbes ou combiner un graphique Courbes avec un graphique Histogramme

7.5.3 Aligner les étiquettes

Aligner les étiquettes de l'axe principal avec celles de l'axe secondaire.

 le quadrillage horizontal est aligné sur les étiquettes de l'échelle principale (à gauche)
il faut faire en sorte que les valeurs mini, maxi et les intervalles soient multiples entre les échelles
exemple :
échelle principale mini : 0 maxi : 1200 unité principale : 200
échelle secondaire mini : 0 maxi : 600 unité principale : 100
 Remarque
il n'y a pas de commande permettant de calculer automatiquement les échelles principale et secondaire
entre elles !

GRAPHIQUES 51
 EXCEL Perfectionnement

8 PROTECTION
8.1 Principes

La protection sous Excel a plusieurs niveaux : ouverture du classeur, protection des cellules, des feuilles et des
classeurs.

8.2 Protection du classeur

8.2.1 Enregistrement automatique

Paramétrage de l'enregistrement automatique d'un classeur.

 Fichier / Options / Enregistrement / Enregistrer les classeurs
 Enregistrer les informations de récupération automatique
saisir la fréquence et les emplacements

8.2.2 Copie de sauvegarde

Excel permet d'effectuer une sauvegarde du classeur dans un autre fichier.

 un fichier de version -1 sera créé à chaque ouverture réussie du classeur
nom_classeur.XLK
 Fichier / Enregistrer sous… / Outils / Options générales…
 Créer une copie de sauvegarde

8.2.3 Mot de passe à l'ouverture

Protège l'ouverture du classeur.

 Fichier / Enregistrer sous… / Outils / Options générales…
saisir un Mot de passe pour la lecture

PROTECTION 52
 EXCEL Perfectionnement

 affiche la notification suivante à l'ouverture du classeur

8.2.4 Mot de passe pour modifications

Protège la modification du classeur.

 Fichier / Enregistrer sous… / Outils / Options générales…
saisir un Mot de passe pour la modification

 affiche la notification suivante à l'ouverture du classeur

si le mot de passe est inconnu, possibilité d'ouvrir le classeur en Lecture seule

8.2.5 Lecture seule recommandée

Affiche la recommandation d'utiliser le classeur en lecture seule.

 Fichier / Enregistrer sous… / Outils / Options générales…
 Lecture seule recommandée

 affiche la recommandation suivante à l'ouverture du classeur

PROTECTION 53
 EXCEL Perfectionnement

8.3 Protection contre les modifications

8.3.1 Protection des cellules

Activer le verrouillage des cellules pour éviter d'en modifier accidentellement le contenu ou la présentation.
 par défaut toutes les cellules sont verrouillées, il faut donc commencer par déverrouiller les cellules que
l'on veut laisser libres en modification.
il faudra ensuite protéger la feuille pour activer les verrouillages des cellules
 sélectionner les cellules à déverrouiller,
Accueil / Cellules / Format / Verrouiller la cellule
cellule verrouillée / cellule déverrouillée
 possibilité de masquer les formules de calculs
Accueil / Cellules / Format / Format de cellule… / Protection / Masquée

8.3.2 Protection de la feuille

Activer la protection de la feuille pour interdire toute modification de présentation : cellules, lignes, colonnes.
 activer la protection
Accueil / Cellules / Format / Protéger la feuille…
ou Révision / Modifications / Protéger la feuille
saisir un mot de passe éventuel
 Sélectionner les cellules verrouillées
 Sélectionner les cellules déverrouillées

 désactiver la protection de la feuille

Accueil / Cellules / Format / Ôter la protection de la feuille…
ou Révision / Modifications / Ôter la protection de la feuille

8.3.3 Déplacements

Possibilité de se déplacer entre les cellules déverrouillées avec tabulation ().

il peut être intéressant de modifier la couleur de fond des cellules

déverrouillées afin de les repérer plus facilement

PROTECTION 54
 EXCEL Perfectionnement

8.3.4 Protection du classeur

Activer la protection du classeur pour interdire toute modification structurelle.

 activer la protection du classeur
Révision / Modifications / Protéger le classeur
 Structure .............. interdit toute modification de structure : gestion des feuilles
 Fenêtres ............... interdit toute modification des fenêtres : dimensions, format de la fenêtre
saisir un mot de passe éventuel

 désactiver la protection du classeur

Révision / Modifications / Protéger le classeur

PROTECTION 55
 EXCEL Perfectionnement

9 MODÈLES
9.1 Modification des modèles par défaut

9.1.1 Classeur par défaut

Modification du classeur par défaut utilisé par Excel.

 il est mis en œuvre au démarrage d'Excel ou avec : Fichier / Nouveau / Nouveau classeur
 créer un classeur, puis Fichier / Enregistrer sous…
dossier ................ C:\DOCUMENTS AND SETTINGS\nom_utilisateur\APPLICATION DATA\MICROSOFT\EXCEL\XLSTART
nom fichier ........... Classeur.XLTX  mot réservé !
type de fichier....... Modèle Excel (*.XLTX)

9.1.2 Feuille par défaut

Modification de la feuille par défaut utilisée par Excel.

 elle est mise en œuvre à chaque insertion de feuille : Insérer une feuille de calcul
 créer un classeur avec 1 seule feuille, puis Fichier / Enregistrer sous…
dossier ................ C:\DOCUMENTS AND SETTINGS\nom_utilisateur\APPLICATION DATA\MICROSOFT\EXCEL\XLSTART
nom fichier ........... Feuil.XLTX  mot réservé !
type de fichier....... Modèle Excel (*.XLTX)

9.2 Modèles spécifiques

9.2.1 Création d'un modèle

Création de modèles spécifiques utilisés comme nouveau classeur ou en insertion de feuilles.

 créer un classeur avec 1 ou plusieurs feuilles, renommer les feuilles,
puis Fichier / Enregistrer sous… (EXCEL\MODÈLES ou EXCEL\TEMPLATES)
dossier ................ C:\DOCUMENTS AND SETTINGS\nom_utilisateur\APPLICATION DATA\MICROSOFT\EXCEL\MODÈLES
nom fichier ........... nom_classeur.XLTX
type de fichier....... Modèle Excel (*.XLTX)
 le modèles sera disponible via Fichier / Nouveau / Mes modèles

9.2.2 Créer un classeur à partir d'un modèle

 Fichier / Nouveau / Mes modèles / choisir le modèle voulu

 un nouveau classeur sera créé par recopie du classeur modèle

9.2.3 Insérer les feuilles d'un classeur modèle

 bouton droit sur onglet de feuille / Insérer… / choisir le modèle voulu

 le nombre de feuilles insérées dépendra du nombre de feuilles du classeur modèle

9.2.4 Ouvrir un classeur au démarrage d'Excel

 créer ou ouvrir un classeur, puis Fichier / Enregistrer sous…

dossier ................ C:\DOCUMENTS AND SETTINGS\nom_utilisateur\APPLICATION DATA\MICROSOFT\EXCEL\XLSTART
nom fichier ........... nom_classeur.XLSX
type de fichier....... Classeur Excel (*.XLSX)
 tous les classeur stockés dans ce dossier seront automatiquement ouverts avec Excel

MODÈLES 56
 EXCEL Perfectionnement

10 OUTILS DE SIMULATION
10.1 Scénario

10.1.1 Principes

Un scénario permet d'enregistrer plusieurs ensembles de valeurs stockées dans des cellules.
 ces ensembles de valeurs peuvent être rappelés par la suite
 ils pourront aussi être comparés entre eux

10.1.2 Mise en œuvre

 créer le tableau voulu

 il est utile de nommer les cellules variables et celles de résultats
ces noms seront utilisés dans le tableau de synthèse
cellules variables ...........B5:C5 B5 : Prix_porte C5 : Prix_fenêtre
cellule résultat ..............B7 B7 : Total
A B C
1 Portes Fenêtres
2 Prix 150,00 € 120,00 €
3 Remise 15% 22,50 € 18,00 €
4 Montant 127,50 € 102,00 €
5 Quantité 5 12
6 S/total 637,50 € 1 224,00 €
7 Total 1 861,50 €

 Données / Outils de données / Analyse scénarios / Gestionnaire de scénarios… /

saisir un nom, puis définir les cellules variables

 saisir ensuite les valeurs, puis faire de même pour chaque scénario

OUTILS DE SIMULATION 57
 EXCEL Perfectionnement

 afficher un scénario
Données / Outils de données / Analyse scénarios / Gestionnaire de scénarios…
choisir un scénario, puis
 les cellules variables sont remise à jour
 synthèse
Données / Outils de données / Analyse scénarios / Gestionnaire de scénarios… /

 Synthèse de scénarios  crée une feuille "Synthèse de scénarios"

Synthèse de scénarios
Valeurs actuelles : LEFORT MARLET CARON
Cellules variables :
Prix_porte 141,00 € 150,00 € 165,00 € 141,00 €
Prix_fenêtre 105,00 € 120,00 € 98,00 € 105,00 €
Cellules résultantes :
Total 1 670,25 € 1 861,50 € 1 700,85 € 1 670,25 €
La colonne Valeurs actuelles affiche les valeurs des cellules variables
au moment de la création du rapport de synthèse. Les cellules variables
de chaque scénario se situent dans les colonnes grisées.

 Scénario du rapport de tableau croisé dynamique  crée une feuille "Tableau croisé dynamique"
Étiquettes de lignes Total
CARON 1670,25
LEFORT 1861,5
MARLET 1700,85

10.2 Valeur cible

10.2.1 Principes

Permet de calculer automatiquement une valeur d’entrée d’une formule pour atteindre un résultat voulu.
 la valeur cible ne permet de mettre à jour qu'une seule cellule
 Excel fait varier la valeur d'une cellule d’entrée déterminée jusqu'à ce que la formule, dépendant de
cette cellule, atteigne le résultat souhaité
 ATTENTION
LA CELLULE VARIABLE DOIT ÊTRE DANS LA MÊME FEUILLE QUE CELLE DE LA CELLULE À DÉFINIR…

10.2.2 Mise en œuvre

 quel chiffre d’affaire doit-on effectuer en janvier pour la région sud afin d’obtenir
un total de 4270 € ?
A B C D E
1 Janvier Février Mars S/Total
2 Nord 700 € 600 € 600 € 1 900 €
3 Sud 500 € 400 € 700 € 1 600 €
4
5 Total 3 500 €

OUTILS DE SIMULATION 58
 EXCEL Perfectionnement

 Données / Outils de données / Analyse scénarios / Valeur cible…

Cellule à définir .............contient une formule dépendant de la cellule à modifier
Valeur à atteindre ..........résultat voulu
Cellule à modifier...........contient une valeur en relation avec la formule

résultat : CA Janvier région Sud = 1270 €

la valeur trouvée peut avoir un grand nombre de décimales…

se baser sur cette valeur et saisir une valeur approchée avec le nombre
de décimales approprié (2 décimales en gestion)

10.3 Solveur

10.3.1 Principes

Permet de calculer automatiquement des valeurs d’entrées d’une formule pour atteindre un résultat voulu.
 le solveur permet de mettre à jour plusieurs cellules
 permet de déterminer une valeur résultante quand on doit modifier plusieurs cellules utilisées dans une
formule
 il ajuste les valeurs des cellules spécifiées de façon à produire le résultat souhaité
 il faudra spécifier des contraintes afin de permettre au solveur de trouver une solution
ces contraintes devront analysées !
 ATTENTION
LES CELLULES VARIABLES DOIVENT ÊTRE DANS LA MÊME FEUILLE QUE CELLE DE LA CELLULE À DÉFINIR…
 la résolution d'un problème avec le solveur est du domaine de la Recherche Opérationnelle

10.3.2 Mise en œuvre

Il va falloir modéliser le problème afin d'en permettre la résolution avec le solveur.

 créer le ou les tableaux nécessaires
il faut une formule résultante qui pourra être maximisée ou minimisée
 si Solveur n'apparaît pas, il faut activer la macro complémentaire correspondante :
Fichier / Options / Compléments / Complément Solveur / Atteindre… /  Complément Solveur

OUTILS DE SIMULATION 59
 EXCEL Perfectionnement

 mise en œuvre du solveur :

Données / Analyse / Solveur
sélectionner la concernant l'Objectif à définir
sélectionner les Cellules variables (sélection multiple possible)

 ajouter chaque contrainte

référence de la cellule
opérateur de comparaison <= = >= ent bin dif
contrainte, ce peut être une valeur, une cellule ou une formule

  Rendre les variables sans contrainte non négatives

sélectionnez une résolution :
GRG non linéaire pour des problèmes simples
Simplex PL pour des problèmes linéaires
Évolutionnaire pour des problèmes complexes
 choisir la valeur à atteindre
À :  Min -  Max -  Valeur  saisir valeur voulue

pour que le solveur puisse trouver une solution, la valeur doit être
comprise entre le mini et le maxi ; calculez d'abord le mini et le maxi puis
choisissez une valeur entre ces limites…

OUTILS DE SIMULATION 60
 EXCEL Perfectionnement

 solution trouvée

possibilité d'enregistrer
les résultats sous forme
de scénario

 solution non trouvée

10.3.1 Exemple de mise en œuvre

Les entrepôts d'une entreprise effectuent des commandes de pièces aux usines de production.
 le transport a un coût variable selon la distance séparant les usines des entrepôts, il va donc falloir
minimiser le coût total des transports en optimisant les trajets des pièces commandées…
 modélisation du problème
capacités de production
Cambrai Nantes Périgueux
quantités demandées 350 p 291 p 128 p
Quimper 450 p nombre de pièces envoyées 0p total des
Albi 300 p des Usines vers les Entrepôts 0p pièces
0p 0p 0p
total des pièces envoyées

coût /pièce Cambrai Nantes Périgueux

Quimper 7,39 € 2,32 € 5,90 €
Albi 8,70 € 6,51 € 2,97 €

frais d'envoi Cambrai Nantes Périgueux

Quimper 0,00 € 0,00 € 0,00 €
Albi 0,00 € 0,00 € 0,00 €
S/total 0,00 € 0,00 € 0,00 €
TOTAL 0,00 €

OUTILS DE SIMULATION 61
 EXCEL Perfectionnement

 minimum
capacités de production
Cambrai Nantes Périgueux
quantités demandées 350 p 291 p 128 p
Quimper 450 p 178 p 272 p 0p 450 p total des
Albi 300 p 172 p 0p 128 p 300 p pièces
350 p 272 p 128 p
total des pièces envoyées

frais d'envoi Cambrai Nantes Périgueux

Quimper 2 010,08 € 631,04 € 0,00 €
Albi 0,00 € 0,00 € 380,16 €
S/total 2 010,08 € 631,04 € 380,16 €
TOTAL 3 021,28 €

 maximum
capacités de production
Cambrai Nantes Périgueux
quantités demandées 350 p 291 p 128 p
Quimper 450 p 322 p 0p 128 p 450 p total des
Albi 300 p 9p 291 p 0p 300 p pièces
331 p 291 p 128 p
total des pièces envoyées

frais d'envoi Cambrai Nantes Périgueux

Quimper 0,00 € 0,00 € 755,20 €
Albi 2 531,70 € 1 894,41 € 0,00 €
S/total 2 531,70 € 1 894,41 € 755,20 €
TOTAL 5 181,31 €

 valeur : 4192,63 €
capacités de production
Cambrai Nantes Périgueux
quantités demandées 350 p 291 p 128 p
Quimper 450 p 313 p 101 p 36 p 450 p total des
Albi 300 p 36 p 181 p 83 p 300 p pièces
349 p 282 p 119 p
total des pièces envoyées

frais d'envoi Cambrai Nantes Périgueux

Quimper 746,39 € 234,32 € 212,40 €
Albi 1 574,70 € 1 178,31 € 246,51 €
S/total 2 321,09 € 1 412,63 € 458,91 €
TOTAL 4 192,63 €

OUTILS DE SIMULATION 62
 EXCEL Perfectionnement

11 AFFICHAGES
11.1 Mode plan

11.1.1 Principes

Le mode plan permet d'avoir plusieurs niveaux de lecture d'un tableau en faisant apparaître certaines
lignes/colonnes selon un ordre hiérarchisé… c'est le principe du pliage.
 il faut définir les niveaux de plans et commencer avec ceux de niveau le plus élevé
(niveau 1 par défaut)
 chaque niveau hérite des niveaux qui lui sont supérieurs…
penser éventuellement à réorganiser les tableaux en conséquence…

11.1.2 Mise en œuvre

 ajouter/enlever un niveau
sélectionner les lignes/colonnes de niveau 2, puis Données / Plan
Grouper ou (ALT) (MAJ) (droite)
Dissocier ou (ALT) (MAJ) (gauche)
 création plan automatique
Données / Plan / Grouper  / Plan automatique ou Données / Plan

 supprimer plan
Données / Plan / Dissocier  / Effacer le plan

11.1.3 Gestion

 modifier un niveau
sélectionner les lignes/colonnes du niveau concerné, puis Données / Plan
Grouper ou (ALT) (MAJ) (droite)
Dissocier ou (ALT) (MAJ) (gauche)
 symboles du plan
pour faire apparaître/masquer les symboles du plan : (ALT) (8)
ou ajouter l'outil Symboles du plan
ou Fichier / Options / Options avancées / Afficher les options pour cette feuille de calcul
 Afficher les symboles du plan si un plan est appliqué
 afficher un niveau de plan
cliquer sur le niveau voulu
 afficher/masquer un groupement
cliquer sur ou Données / Plan / Afficher les détails
cliquer sur ou Données / Plan / Masquer

AFFICHAGES 63
 EXCEL Perfectionnement

11.2 Affichages personnalisés

11.2.1 Principes

Les affichages personnalisés permettent d'enregistrer des paramètres d'impressions, de masquage

lignes/colonnes, de filtrage et de sélection du curseur.
Remarque
ne fonctionnent pas en mode Tableau…

11.2.2 Mise en œuvre

 commencer par créer un affichage sans paramétrage

Affichage / Affichages classeur / Personnalisé /
Nom : "Affichage normal"
 Paramètres d’impression
 Paramètres masqués des lignes, colonnes et filtres

 créer un affichage personnalisé

spécifier les paramètres d'impression voulus, masquer certaines lignes/colonnes, appliquer des filtres…
Affichage / Affichages classeur / Personnalisé /
 Paramètres d’impression
 Paramètres masqués des lignes, colonnes et filtres
 utilisation
Affichage / Affichages classeur / Personnalisé / sélectionner choisir l'affichage voulu
Remarque
ajouter l'outil Affichages personnalisés pour faciliter la sélection de l'affichage voulu

AFFICHAGES 64
 EXCEL Perfectionnement

12 ANNEXES
12.1 Formats personnalisés

12.1.1 Création

Possibilité de créer des formats permettant de répondre à des besoins spécifiques.

 Accueil / Nombre / |Nombre| - Personnalisé / Type:
 les formats sont saisis sous forme de codes de format dans différentes sections

12.1.2 Codes formats

 format texte
saisir @ (contenu de la cellule) précédé et/ou suivi de libellés entre guillemets
code Versailles
"ville de : "@ ville de : Versailles
 format numérique
saisir 0 précédé/suivi de libellés entre guillemets
0  affiche un zéro si il n’y a pas de chiffre sur le rang
#  n’affiche rien si il n’y a pas de chiffre sur le rang
code 8230,69
00000,0" km" 08230,7 km
# ##0" km" 8 231 km
"dist. : "0,00" km" dist. : 8230,69 km
 format date
saisir j pour jour, m pour mois, a pour année
j n° jour 1 chiffre
jj n° jour 2 chiffres
jjj nom jour abrégé
jjjj nom jour complet

m n° mois 1 chiffre
mm n° mois 2 chiffres
mmm nom mois abrégé
mmmm nom mois complet
mmmmm 1ère lettre du mois

aa année 2 chiffres
aaaa année 4 chiffres
 format heure
h pour heure, m pour minute, s pour secondes
pour affichage sur 12H, ajouter AM/PM après le code
pour affichage au-delà de 24h, 60' ou 60" saisir le code entre crochets [ ]
h heures 1 chiffre [h] heures >24h
hh heures 2 chiffres

m minutes 1 chiffre [m] minutes >60’

mm minutes 2 chiffres

s secondes 1 chiffre [s] secondes >60"

ss secondes 2 chiffres

ANNEXES 65
 EXCEL Perfectionnement

12.1.3 Sections

Les sections dans le code le code de format permet de créer des codes de formats spécifiques selon la valeur.
 les sections sont séparées par un ;
1 section 2 sections 3 sections 4 sections
toute valeur
>= 0 ; <0
>0 ; <0 ; =0
>0 ; <0 ; =0 ; texte

 couleurs
les couleurs sont saisies entre [ ] et avant les codes format
blanc, bleu, cyan, jaune, magenta, noir, rouge, vert
exemple : [bleu]0,0
 conditions
permet de n'appliquer le format que lorsque la valeur répond à une condition
les conditions sont saisies entre [ ] et contiennent un opérateur de comparaison et une valeur
opérateurs de comparaison
< <= > >= = <>
exemple : [rouge][>500]0000,0

12.2 Pourcentages

12.2.1 Calculs

 à partir du pourcentage
A B exemple : TVA à partir du TTC
1 25 19,6%
2 valeur  pourcentage
3 4,9 =A1B1

 à partir de la valeur
A B exemple : TVA à partir de TVA
1 4,9 19,6%
2 valeur X pourcentage
3 25 =A1/B1

 après augmentation
A B exemple : TTC à partir de HT et TVA
1 60 10%
2 valeur(1+pourcentage)
3 66 =A1(1+B1) ou A1+A1B1

 avant augmentation
A B exemple : HT à partir de TTC et TVA
1 60 10%
2 valeur/(1+pourcentage)
3 66 =A1/(1+B1)

ANNEXES 66
 EXCEL Perfectionnement

 après diminution
A B exemple : prix après remise
1 40 10%
2 valeur(1-pourcentage)
3 36 =A1(1-B1)

 après diminution
A B exemple : prix avant remise
1 36 10%
2 valeur/(1-pourcentage)
3 40 =A1/(1-B1) ou A1-A1/B1

12.2.2 Comparaisons

 différence entre deux valeurs

A B
1 50 40 nouvelle valeur/valeur référence–1
2 =A1/B1
3 25% % positif : augmentation

A B
1 40 50
2
3 -20% % négatif : diminution

 représenter une valeur par rapport à une autre

A B
1 50 75 1ère valeur / 2e valeur
2 =A1/B1
3 66,7% <100% : 1ère valeur < 2e valeur

A B
1 75 50
2
3 150% >100% : 1ère valeur > 2e valeur

 comparer plusieurs valeurs entre elles

vérification : la somme des pourcentage doit être égale à 100,0% (ou 1)
A B C
1 30 5 15
2
3 60% 10% 30%
=A1/SOMME(A1:C1) =A1/SOMME(A1:C1) =A1/SOMME(A1:C1)

ANNEXES 67
 EXCEL Perfectionnement

12.3 Fonctions Excel

 base de données
BDECARTYPE Calcule l’écart type pour un échantillon d’entrées de base de données
sélectionnées.
BDECARTYPEP Calcule l’écart type pour l’ensemble d’une population d’entrées de base
de données sélectionnées.
BDLIRE Extrait d’une base de données un enregistrement unique répondant aux
critères spécifiés.
BDMAX Renvoie la valeur maximale des entrées de base de données sélectionnées.
BDMIN Renvoie la valeur minimale des entrées de base de données sélectionnées.
BDMOYENNE Renvoie la moyenne des entrées de base de données sélectionnées.
BDNB Compte le nombre de cellules d’une base de données qui contiennent des
nombres.
BDNBVAL Compte les cellules non vides d’une base de données.
BDPRODUIT Multiplie les valeurs d’un champ particulier des enregistrements d’une
base de données, qui répondent aux critères spécifiés.
BDSOMME Ajoute les nombres dans la colonne de champ des enregistrements de la
base de données, qui répondent aux critères.
BDVAR Calcule la variance pour un échantillon d’entrées de base de données
sélectionnées.
BDVARP Calcule la variance pour l’ensemble d’une population d’entrées de base de
données sélectionnées.

 fonctions Cube
JEUCUBE Définit un ensemble calculé de membres ou de tuples en envoyant une
expression définie au cube sur le serveur qui crée l’ensemble et le renvoie
à Microsoft Excel.
MEMBRECUBE Renvoie un membre ou un tuple du cube. Utilisez cette fonction pour
valider l’existence du membre ou du tuple dans le cube.
MEMBREKPICUBE Renvoie une propriété d’indicateur de performance clé et affiche le nom
de l’indicateur dans la cellule. Un indicateur de performance clé est une
mesure quantifiable, telle que la marge bénéficiaire brute mensuelle ou la
rotation trimestrielle du personnel, utilisée pour évaluer les performances
d’une entreprise.
NBJEUCUBE Renvoie le nombre d’éléments dans un jeu.
PROPRIETEMEMBRECUBE Renvoie la valeur d’une propriété de membre du cube. Utilisez cette
fonction pour valider l’existence d’un nom de membre dans le cube et
pour renvoyer la propriété spécifiée pour ce membre.
RANGMEMBRECUBE Renvoie le nième membre ou le membre placé à un certain rang dans un
ensemble. Utilisez cette fonction pour renvoyer un ou plusieurs éléments
d’un ensemble, tels que les meilleurs vendeurs ou les 10 meilleurs
étudiants.
VALEURCUBE Renvoie une valeur d’agrégation issue du cube.

ANNEXES 68
 EXCEL Perfectionnement

 dates et heures
ANNEE Convertit un numéro de série en année.
AUJOURDHUI Renvoie le numéro de série de la date du jour.
DATE Renvoie le numéro de série d’une date précise.
DATEVAL Convertit une date représentée sous forme de texte en numéro de série.
FIN.MOIS Renvoie le numéro de série de la date du dernier jour du mois précédant
ou suivant la date_départ du nombre de mois indiqué.
FRACTION.ANNEE Renvoie la fraction de l’année représentant le nombre de jours entiers
séparant la date de début et la date de fin.
HEURE Convertit un numéro de série en heure.
JOUR Convertit un numéro de série en jour du mois.
JOURS360 Calcule le nombre de jours qui séparent deux dates sur la base d’une
année de 360 jours.
JOURSEM Convertit un numéro de série en jour de la semaine.
MAINTENANT Renvoie le numéro de série de la date et de l’heure du jour.
MINUTE Convertit un numéro de série en minute.
MOIS Convertit un numéro de série en mois.
MOIS.DECALER Renvoie le numéro de série de la date qui représente une date spécifiée
(l’argument date_départ), corrigée en plus ou en moins du nombre de mois
indiqué.
NB.JOURS.OUVRES Renvoie le nombre de jours ouvrés entiers compris entre deux dates.
NB.JOURS.OUVRES.INTL Renvoie le nombre de jours ouvrés entiers compris entre deux dates à
l’aide de paramètres identifiant les jours du week-end et leur nombre.
NO.SEMAINE Convertit un numéro de série en un numéro représentant l’ordre de la
semaine dans l’année.
SECONDE Convertit un numéro de série en seconde.
SERIE.JOUR.OUVRE Renvoie le numéro de série de la date avant ou après le nombre de jours
ouvrés spécifiés.
SERIE.JOUR.OUVRE.INTL Renvoie le numéro de série de la date avant et après un nombre spécifié
de jours ouvrés en spécifiant des paramètres qui identifient et dénombrent
les jours inclus dans le week-end.
TEMPS Renvoie le numéro de série d’une heure précise.
TEMPSVAL Convertit une date représentée sous forme de texte en numéro de série.

ANNEXES 69
 EXCEL Perfectionnement

 financières
AMORDEGRC Renvoie l’amortissement correspondant à chaque période comptable en
utilisant un coefficient d’amortissement.
AMORLIN Calcule l’amortissement linéaire d’un bien pour une période donnée.
AMORLINC Renvoie l’amortissement d’un bien à la fin d’une période fiscale donnée.
CUMUL.INTER Renvoie l’intérêt cumulé payé sur un emprunt entre deux périodes.
CUMUL.PRINCPER Renvoie le montant cumulé des remboursements du capital d’un emprunt
effectués entre deux périodes.
DATE.COUPON.PREC Renvoie la date de coupon précédant la date de règlement.
DATE.COUPON.SUIV Renvoie la première date de coupon ultérieure à la date de règlement.
DB Renvoie l’amortissement d’un bien pour une période spécifiée en utilisant
la méthode de l’amortissement dégressif à taux fixe.
DDB Renvoie l’amortissement d’un bien pour toute période spécifiée, en
utilisant la méthode de l’amortissement dégressif à taux double ou selon
un coefficient à spécifier.
DUREE Renvoie la durée, en années, d’un titre dont l’intérêt est perçu
périodiquement.
DUREE.MODIFIEE Renvoie la durée de Macauley modifiée pour un titre ayant une valeur
nominale hypothétique de 100 euros.
INTERET.ACC Renvoie l’intérêt couru non échu d’un titre dont l’intérêt est perçu
périodiquement.
INTERET.ACC.MAT Renvoie l’intérêt couru non échu d’un titre dont l’intérêt est perçu à
l’échéance.
INTPERER Calcule le montant des intérêts d’un investissement pour une période
donnée.
ISPMT Calcule le montant des intérêts d’un investissement pour une période
donnée.
NB.COUPONS Renvoie le nombre de coupons dus entre la date de règlement et la date
d’échéance.
NB.JOURS.COUPON.PREC Renvoie le nombre de jours entre le début de la période de coupon et la
date de liquidation.
NB.JOURS.COUPON.SUIV Renvoie le nombre de jours entre la date de liquidation et la date du
coupon suivant la date de liquidation.
NB.JOURS.COUPONS Renvoie le nombre de jours pour la période du coupon contenant la date
de liquidation.
NPM Renvoie le nombre de versements nécessaires pour rembourser un
emprunt.
PRINCPER Calcule, pour une période donnée, la part de remboursement du principal
d’un investissement.
PRIX.BON.TRESOR Renvoie le prix d’un bon du Trésor d’une valeur nominale de 100 euros.
PRIX.DCOUPON.IRREG Renvoie le prix par tranche de valeur nominale de 100 euros d’un titre
dont la dernière période de coupon est irrégulière.
PRIX.DEC Convertit un prix en euros, exprimé sous forme de fraction, en un prix en
euros exprimé sous forme de nombre décimal.
PRIX.FRAC Convertit un prix en euros, exprimé sous forme de nombre décimal, en un
prix en euros exprimé sous forme de fraction.
PRIX.PCOUPON.IRREG Renvoie le prix par tranche de valeur nominale de 100 euros d’un titre
dont la première période de coupon est irrégulière.

ANNEXES 70
 EXCEL Perfectionnement

PRIX.TITRE Renvoie le prix d’un titre rapportant des intérêts périodiques, pour une
valeur nominale de 100 euros.
PRIX.TITRE.ECHEANCE Renvoie le prix d’un titre dont la valeur nominale est 100 euros et qui
rapporte des intérêts à l’échéance.
REND.DCOUPON.IRREG Renvoie le taux de rendement d’un titre dont la dernière période de
coupon est irrégulière.
REND.PCOUPON.IRREG Renvoie le taux de rendement d’un titre dont la première période de
coupon est irrégulière.
RENDEMENT.BON.TRESOR Calcule le taux de rendement d’un bon du Trésor.
RENDEMENT.SIMPLE Calcule le taux de rendement d’un emprunt à intérêt simple (par exemple,
un bon du Trésor).
RENDEMENT.TITRE, fonction Calcule le rendement d’un titre rapportant des intérêts périodiquement.
RENDEMENT.TITRE.ECHEANCE Renvoie le rendement annuel d’un titre qui rapporte des intérêts à
l’échéance.
SYD Calcule l’amortissement d’un bien pour une période donnée sur la base de
la méthode américaine Sum-of-Years Digits (amortissement dégressif à
taux décroissant appliqué à une valeur constante).
TAUX Calcule le taux d’intérêt par période pour une annuité.
TAUX.EFFECTIF Renvoie le taux d’intérêt annuel effectif.
TAUX.ESCOMPTE Calcule le taux d’escompte d’une transaction.
TAUX.ESCOMPTE.R Renvoie le taux d’escompte rationnel d’un bon du Trésor.
TAUX.INTERET Affiche le taux d’intérêt d’un titre totalement investi.
TAUX.NOMINAL Calcule le taux d’intérêt nominal annuel.
TRI Calcule le taux de rentabilité interne d’un investissement pour une
succession de trésoreries.
TRI.PAIEMENTS Calcule le taux de rentabilité interne d’un ensemble de paiements non
périodiques.
TRIM Calcule le taux de rentabilité interne lorsque les paiements positifs et
négatifs sont financés à des taux différents.
VA Calcule la valeur actuelle d’un investissement.
VALEUR.ENCAISSEMENT Renvoie la valeur d’encaissement d’un escompte commercial, pour une
valeur nominale de 100 euros.
VALEUR.NOMINALE Renvoie la valeur nominale à échéance d’un effet de commerce.
VAN Calcule la valeur actuelle nette d’un investissement basé sur une série de
décaissements et un taux d’escompte.
VAN.PAIEMENTS Renvoie la valeur actuelle nette d’un ensemble de paiements non
périodiques.
VC Renvoie la valeur future d’un investissement.
VC.PAIEMENTS Calcule la valeur future d’un investissement en appliquant une série de
taux d’intérêt composites.
VDB Renvoie l’amortissement d’un bien pour une période spécifiée ou partielle
en utilisant une méthode de l’amortissement dégressif à taux fixe.
VPM Calcule le paiement périodique d’un investissement donné.

ANNEXES 71
 EXCEL Perfectionnement

 information
CELLULE Renvoie des informations sur la mise en forme, l’emplacement et le
contenu d’une cellule.
EST Renvoie VRAI si l’argument valeur fait référence à la valeur d’erreur #N/A.
EST.IMPAIR Renvoie VRAI si le chiffre est impair.
EST.PAIR Renvoie VRAI si le chiffre est pair.
ESTERR Renvoie VRAI si l’argument valeur fait référence à une valeur d’erreur,
sauf #N/A.
ESTERREUR Renvoie VRAI si l’argument valeur fait référence à une valeur d’erreur.
ESTLOGIQUE Renvoie VRAI si l’argument valeur fait référence à une valeur logique.
ESTNONTEXTE Renvoie VRAI si l’argument valeur ne se présente pas sous forme de texte.
ESTNUM Renvoie VRAI si l’argument valeur représente un nombre.
ESTREF Renvoie VRAI si l’argument valeur est une référence.
ESTTEXTE Renvoie VRAI si l’argument valeur se présente sous forme de texte.
ESTVIDE Renvoie VRAI si l’argument valeur est vide.
INFORMATIONS Renvoie des informations sur l’environnement d’exploitation actuel.
N Renvoie une valeur convertie en nombre.
NA Renvoie la valeur d’erreur #N/A.
TYPE Renvoie un nombre indiquant le type de données d’une valeur.
TYPE.ERREUR Renvoie un nombre correspondant à un type d’erreur.

 ingénierie
BESSELI Renvoie la fonction Bessel modifiée In(x).
BESSELJ Renvoie la fonction Bessel Jn(x).
BESSELK Renvoie la fonction Bessel modifiée Kn(x).
BESSELY Renvoie la fonction Bessel Yn(x).
BINDEC Convertit un nombre binaire en nombre décimal.
BINHEX Convertit un nombre binaire en nombre hexadécimal.
BINOCT Convertit un nombre binaire en nombre octal.
COMPLEXE Convertit des coefficients réel et imaginaire en un nombre complexe.
COMPLEXE.ARGUMENT Renvoie l’argument thêta, un angle exprimé en radians.
COMPLEXE.CONJUGUE Renvoie le nombre complexe conjugué d’un nombre complexe.
COMPLEXE.COS Renvoie le cosinus d’un nombre complexe.
COMPLEXE.DIFFERENCE Renvoie la différence entre deux nombres complexes.
COMPLEXE.DIV Renvoie le quotient de deux nombres complexes.
COMPLEXE.EXP Renvoie la fonction exponentielle d’un nombre complexe.
COMPLEXE.IMAGINAIRE Renvoie le coefficient imaginaire d’un nombre complexe.
COMPLEXE.LN Renvoie le logarithme népérien d’un nombre complexe.
COMPLEXE.LOG10 Calcule le logarithme en base 10 d’un nombre complexe.
COMPLEXE.LOG2 Calcule le logarithme en base 2 d’un nombre complexe.
COMPLEXE.MODULE Renvoie la valeur absolue (module) d’un nombre complexe.
COMPLEXE.PRODUIT Renvoie le produit de 2 à 255 nombres complexes.

ANNEXES 72
 EXCEL Perfectionnement

COMPLEXE.PUISSANCE Renvoie un nombre complexe élevé à une puissance entière.

COMPLEXE.RACINE Renvoie la racine carrée d’un nombre complexe.
COMPLEXE.REEL Renvoie le coefficient réel d’un nombre complexe.
COMPLEXE.SIN Renvoie le sinus d’un nombre complexe.
COMPLEXE.SOMME Renvoie la somme de plusieurs nombres complexes.
CONVERT Convertit un nombre d’une unité de mesure à une autre.
DECBIN Convertit un nombre décimal en nombre binaire.
DECHEX Convertit un nombre décimal en nombre hexadécimal.
DECOCT Convertit un nombre décimal en nombre octal.
DELTA Teste l’égalité de deux nombres.
ERF Renvoie la valeur de la fonction d’erreur.
ERF.PRECIS Renvoie la valeur de la fonction d’erreur.
ERFC Renvoie la valeur de la fonction d’erreur complémentaire.
ERFC.PRECIS Renvoie la fonction d’erreur complémentaire intégrée entre x et l’infini.
HEXBIN Convertit un nombre hexadécimal en nombre binaire.
HEXDEC Convertit un nombre hexadécimal en nombre décimal.
HEXOCT Convertit un nombre hexadécimal en nombre octal.
OCTBIN Convertit un nombre octal en nombre binaire.
OCTDEC Convertit un nombre octal en nombre décimal.
OCTHEX Convertit un nombre octal en nombre hexadécimal.
SUP.SEUIL Teste si un nombre est supérieur à une valeur de seuil.

 logiques
ET Renvoie VRAI si tous ses arguments sont VRAI.
FAUX Renvoie la valeur logique FAUX.
NON Inverse la logique de cet argument.
OU Renvoie VRAI si un des arguments est VRAI.
SI Spécifie un test logique à effectuer.
SIERREUR Renvoie une valeur que vous spécifiez si une formule génère une erreur ;
sinon, elle renvoie le résultat de la formule.
VRAI Renvoie la valeur logique VRAI.

ANNEXES 73
 EXCEL Perfectionnement

 recherche et référence
ADRESSE Renvoie une référence sous forme de texte à une seule cellule d’une
feuille de calcul.
CHOISIR Choisit une valeur dans une liste.
COLONNE Renvoie le numéro de colonne d’une référence.
COLONNES Renvoie le nombre de colonnes dans une référence.
DECALER Renvoie une référence décalée par rapport à une référence donnée.
EQUIV Recherche des valeurs dans une référence ou une matrice.
INDEX Utilise un index pour choisir une valeur provenant d’une référence ou
d’une matrice.
INDIRECT Renvoie une référence indiquée par une valeur de texte.
LIEN_HYPERTEXTE Crée un raccourci ou un renvoi qui ouvre un document stocké sur un
serveur réseau, sur un réseau Intranet ou sur Internet.
LIGNE Renvoie le numéro de ligne d’une référence.
LIGNES Renvoie le nombre de lignes dans une référence.
LIREDONNEESTABCROISDYNAMIQUE Renvoie les données stockées dans un rapport de tableau croisé
dynamique.
RECHERCHE Recherche des valeurs dans un vecteur ou une matrice.
RECHERCHEH Effectue une recherche dans la première ligne d’une matrice et renvoie la
valeur de la cellule indiquée.
RECHERCHEV Effectue une recherche dans la première colonne d’une matrice et se
déplace sur la ligne pour renvoyer la valeur d’une cellule.
RTD "Extrait les données en temps réel à partir d’un programme prenant en
charge l’automation COM (Automation : utilisation des objets d’une
application à partir d’une autre application ou d’un autre outil de
développement. Autrefois appelée OLE Automation, Automation est une
norme industrielle et une fonctionnalité du modèle d’objet COM
(Component Object Model).).
Remarque Cette fonction n’est pas disponible dans Microsoft Excel
Starter 2010
Pour plus d’informations sur les fonctionnalités disponibles dans Excel
Starter, voir Prise en charge des fonctionnalités d’Excel Starter.
TRANSPOSE Renvoie la transposition d’une matrice.
ZONES Renvoie le nombre de zones dans une référence.

ANNEXES 74
 EXCEL Perfectionnement

 mathématiques et trigonométriques
ABS Renvoie la valeur absolue d’un nombre.
ACOS Renvoie l’arccosinus d’un nombre.
ACOSH Renvoie le cosinus hyperbolique inverse d’un nombre.
AGREGAT Renvoie un agrégat dans une liste ou une base de données.
ALEA Renvoie un nombre aléatoire compris entre 0 et 1.
ALEA.ENTRE.BORNES Renvoie un nombre aléatoire entre les nombres que vous spécifiez.
ARRONDI Arrondit un nombre au nombre de chiffres indiqué.
ARRONDI.AU.MULTIPLE Donne l’arrondi d’un nombre au multiple spécifié.
ARRONDI.INF Arrondit un nombre en tendant vers 0 (zéro).
ARRONDI.SUP Arrondit un nombre à l’entier supérieur, en s’éloignant de zéro.
ASIN Renvoie l’arcsinus d’un nombre.
ASINH Renvoie le sinus hyperbolique inverse d’un nombre.
ATAN Renvoie l’arctangente d’un nombre.
ATAN2 Renvoie l’arctangente des coordonnées x et y.
ATANH Renvoie la tangente hyperbolique inverse d’un nombre.
COMBIN Renvoie le nombre de combinaisons que l’on peut former avec un nombre
donné d’objets.
COS Renvoie le cosinus d’un nombre.
COSH Renvoie le cosinus hyperbolique d’un nombre.
DEGRES Convertit des radians en degrés.
DETERMAT Renvoie le déterminant d’une matrice.
ENT Arrondit un nombre à l’entier immédiatement inférieur.
EXP Renvoie e élevé à la puissance d’un nombre donné.
FACT Renvoie la factorielle d’un nombre.
FACTDOUBLE Renvoie la factorielle double d’un nombre.
IMPAIR Renvoie le nombre, arrondi à la valeur du nombre entier impair le plus
proche en s’éloignant de zéro.
INVERSEMAT Renvoie la matrice inverse d’une matrice.
LN Renvoie le logarithme népérien d’un nombre.
LOG Renvoie le logarithme d’un nombre dans la base spécifiée.
LOG10 Calcule le logarithme en base 10 d’un nombre.
MOD Renvoie le reste d’une division.
MULTINOMIALE Calcule la multinomiale d’un ensemble de nombres.
PAIR Arrondit un nombre au nombre entier pair le plus proche en s’éloignant de
zéro.
PGCD Renvoie le plus grand commun diviseur.
PI Renvoie la valeur de pi.
PLAFOND Arrondit un nombre au nombre entier le plus proche ou au multiple le plus
proche de l’argument précision en s’éloignant de zéro.
PLAFOND.PRECIS Arrondit un nombre au nombre entier le plus proche ou au multiple le plus
proche de l’argument précision en s’éloignant de zéro. Quel que soit son
signe, ce nombre est arrondi à l’entier supérieur.

ANNEXES 75
 EXCEL Perfectionnement

PLANCHER Arrondit un nombre en tendant vers 0 (zéro).

PLANCHER.PRECIS Arrondit un nombre au nombre entier le plus proche ou au multiple le plus
proche de l’argument précision en tendant vers zéro. Quel que soit son
signe, ce nombre est arrondi à l’entier inférieur.
PPCM Renvoie le plus petit commun multiple.
PRODUIT Multiplie ses arguments.
PRODUITMAT Renvoie le produit de deux matrices.
PUISSANCE Renvoie la valeur du nombre élevé à une puissance.
QUOTIENT Renvoie la partie entière du résultat d’une division.
RACINE Renvoie la racine carrée d’un nombre.
RACINE.PI Renvoie la racine carrée de (nombre * pi).
RADIANS Convertit des degrés en radians.
ROMAIN Convertit des chiffres arabes en chiffres romains, sous forme de texte.
SIGNE Renvoie le signe d’un nombre.
SIN Renvoie le sinus d’un angle donné.
SINH Renvoie le sinus hyperbolique d’un nombre.
SOMME Calcule la somme de ses arguments.
SOMME.CARRES Renvoie la somme des carrés des arguments.
SOMME.SERIES Renvoie la somme d’une série géométrique en s’appuyant sur la formule
suivante :
SOMME.SI Additionne les cellules spécifiées si elles répondent à un critère donné.
SOMME.SI.ENS Ajoute les cellules d’une plage qui répondent à plusieurs critères.
SOMME.X2MY2 Renvoie la somme de la différence des carrés des valeurs correspondantes
de deux matrices.
SOMME.X2PY2 Renvoie la somme de la somme des carrés des valeurs correspondantes de
deux matrices.
SOMME.XMY2 Renvoie la somme des carrés des différences entre les valeurs
correspondantes de deux matrices.
SOMMEPROD Multiplie les valeurs correspondantes des matrices spécifiées et calcule la
somme de ces produits.
SOUS.TOTAL Renvoie un sous-total dans une liste ou une base de données.
TAN Renvoie la tangente d’un nombre.
TANH Renvoie la tangente hyperbolique d’un nombre.
TRONQUE Renvoie la partie entière d’un nombre.

ANNEXES 76
 EXCEL Perfectionnement

 statistiques
AVERAGEA Renvoie la moyenne de ses arguments, nombres, texte et valeurs logiques
inclus.
BETA.INVERSE.N Renvoie l’inverse de la fonction de distribution cumulée pour une
distribution bêta spécifiée.
CENTILE.EXCLURE Renvoie le k-ième centile des valeurs d’une plage, où k se trouve dans la
plage comprise entre 0 et 1, exclues.
CENTILE.INCLURE Renvoie le k-ième centile des valeurs d’une plage.
CENTREE.REDUITE Renvoie une valeur centrée réduite.
CHISQ.TEST Renvoie le test d’indépendance.
COEFFICIENT.ASYMETRIE Renvoie l’asymétrie d’une distribution.
COEFFICIENT.CORRELATION Renvoie le coefficient de corrélation entre deux séries de données.
COEFFICIENT.DETERMINATION Renvoie la valeur du coefficient de détermination R^2 d’une régression
linéaire.
COVARIANCE.PEARSON Renvoie la covariance, moyenne des produits des écarts pour chaque série
d’observations.
COVARIANCE.STANDARD Renvoie la covariance d’échantillon, moyenne des produits des écarts pour
chaque paire de points de deux jeux de données.
CROISSANCE Calcule des valeurs par rapport à une tendance exponentielle.
DROITEREG Renvoie les paramètres d’une tendance linéaire.
ECART.MOYEN Renvoie la moyenne des écarts absolus observés dans la moyenne des
points de données.
ECARTYPE.PEARSON Calcule l’écart type d’une population à partir de la population entière.
ECARTYPE.STANDARD Évalue l’écart type d’une population en se basant sur un échantillon de
cette population.
EQUATION.RANG Renvoie le rang d’un nombre contenu dans une liste.
ERREUR.TYPE.XY Renvoie l’erreur type de la valeur y prévue pour chaque x de la régression.
F.TEST Renvoie le résultat d’un test F.
FISHER Renvoie la transformation de Fisher.
FISHER.INVERSE Renvoie l’inverse de la transformation de Fisher.
FREQUENCE Calcule la fréquence d’apparition des valeurs dans une plage de valeurs,
puis renvoie des nombres sous forme de matrice verticale.
GRANDE.VALEUR Renvoie la k-ième plus grande valeur d’un jeu de données.
INTERVALLE.CONFIANCE.NORMAL Renvoie l’intervalle de confiance pour une moyenne de population.
INTERVALLE.CONFIANCE.STUDENT Renvoie l’intervalle de confiance pour la moyenne d’une population, à
l’aide d’une distribution t de Student.
INVERSE.LOI.F.DROITE Renvoie l’inverse de la distribution de probabilité F.
INVERSE.LOI.F.N Renvoie l’inverse de la distribution de probabilité F.
KURTOSIS Renvoie le kurtosis d’une série de données.
LNGAMMA Renvoie le logarithme népérien de la fonction Gamma, Γ(x)
LNGAMMA.PRECIS Renvoie le logarithme népérien de la fonction Gamma, Γ(x)
LOGREG Renvoie les paramètres d’une tendance exponentielle.
LOI.BETA.N Renvoie la fonction de distribution cumulée.

ANNEXES 77
 EXCEL Perfectionnement

LOI.BINOMIALE.INVERSE.N Renvoie la plus petite valeur pour laquelle la distribution binomiale

cumulée est inférieure ou égale à une valeur de critère.
LOI.BINOMIALE.N Renvoie la probabilité d’une variable aléatoire discrète suivant la loi
binomiale.
LOI.BINOMIALE.NEG.N Renvoie la probabilité d’une variable aléatoire discrète suivant une loi
binomiale négative.
LOI.EXPONENTIELLE.N Renvoie la distribution exponentielle.
LOI.F.DROITE Renvoie la distribution de probabilité F.
LOI.F.N Renvoie la distribution de probabilité F.
LOI.GAMMA.INVERSE.N Renvoie, pour une probabilité donnée, la valeur d’une variable aléatoire
suivant une loi Gamma.
LOI.GAMMA.N Renvoie la probabilité d’une variable aléatoire suivant une loi Gamma.
LOI.HYPERGEOMETRIQUE.N Renvoie la probabilité d’une variable aléatoire discrète suivant une loi
hypergéométrique.
LOI.KHIDEUX Renvoie la fonction de densité de distribution de la probabilité suivant une
loi bêta cumulée.
LOI.KHIDEUX.DROITE Renvoie la probabilité unilatérale de la distribution khi-deux.
LOI.KHIDEUX.INVERSE Renvoie la fonction de densité de distribution de la probabilité suivant une
loi bêta cumulée.
LOI.KHIDEUX.INVERSE.DROITE Renvoie l’inverse de la probabilité unilatérale de la distribution khi-deux.
LOI.LOGNORMALE.INVERSE.N Renvoie l’inverse de la distribution suivant une loi lognormale cumulée.
LOI.LOGNORMALE.N Renvoie la distribution suivant une loi lognormale cumulée.
LOI.NORMALE.INVERSE.N Renvoie, pour une probabilité donnée, la valeur d’une variable aléatoire
suivant une loi normale standard.
LOI.NORMALE.N Renvoie la probabilité d’une variable aléatoire continue suivant une loi
normale.
LOI.NORMALE.STANDARD.INVERSE Renvoie l’inverse de la distribution cumulée normale standard.
LOI.NORMALE.STANDARD.N Renvoie la probabilité d’une variable aléatoire continue suivant une loi
normale standard.
LOI.POISSON.N Renvoie la probabilité d’une variable aléatoire suivant une loi de Poisson.
LOI.STUDENT.BILATERALE Renvoie la probabilité d’une variable aléatoire suivant la loi de t de
Student.
LOI.STUDENT.DROITE Renvoie la probabilité d’une variable aléatoire suivant une loi T de
Student.
LOI.STUDENT.INVERSE.BILATERALE Renvoie, pour une probabilité donnée, la valeur d’une variable aléatoire
suivant une loi T de Student.
LOI.STUDENT.INVERSE.N Renvoie la valeur d’une variable aléatoire suivant la loi de t de Student, en
fonction de la probabilité et du nombre de degrés de liberté.
LOI.STUDENT.N Renvoie la probabilité d’une variable aléatoire suivant la loi de t de
Student.
LOI.WEIBULL.N Renvoie la probabilité d’une variable aléatoire suivant une loi de Weibull.
MAX Renvoie la valeur maximale contenue dans une liste d’arguments.
MAXA Renvoie la valeur maximale d’une liste d’arguments, nombres, texte et
valeurs logiques inclus.
MEDIANE Renvoie la valeur médiane des nombres donnés.

ANNEXES 78
 EXCEL Perfectionnement

MIN Renvoie la valeur minimale contenue dans une liste d’arguments.

MINA Renvoie la plus petite valeur d’une liste d’arguments, nombres, texte et
valeurs logiques inclus.
MODE.MULTIPLE Renvoie une matrice verticale des valeurs les plus fréquentes ou
répétitives dans une matrice ou une plage de données.
MODE.SIMPLE Renvoie la valeur la plus courante d’un jeu de données.
MOYENNE Renvoie la moyenne de ses arguments.
MOYENNE.GEOMETRIQUE Renvoie la moyenne géométrique.
MOYENNE.HARMONIQUE Renvoie la moyenne harmonique.
MOYENNE.RANG Renvoie le rang d’un nombre contenu dans une liste.
MOYENNE.REDUITE Renvoie la moyenne de l’intérieur d’un jeu de données.
MOYENNE.SI Renvoie la moyenne (arithmétique) de toutes les cellules d’une plage qui
répondent à des critères donnés.
MOYENNE.SI.ENS Renvoie la moyenne (arithmétique) de toutes les cellules qui répondent à
plusieurs critères.
NB Détermine les nombres compris dans la liste des arguments.
NB.SI Compte le nombre de cellules qui répondent à un critère donné dans une
plage.
NB.SI.ENS Compte le nombre de cellules à l’intérieur d’une plage qui répondent à
plusieurs critères.
NB.VIDE Compte le nombre de cellules vides dans une plage.
NBVAL Détermine le nombre de valeurs comprises dans la liste des arguments.
ORDONNEE.ORIGINE Renvoie l’ordonnée à l’origine d’une droite de régression linéaire.
PEARSON Renvoie le coefficient de corrélation d’échantillonnage de Pearson.
PENTE Renvoie la pente d’une droite de régression linéaire.
PERMUTATION Renvoie le nombre de permutations pour un nombre donné d’objets.
PETITE.VALEUR Renvoie la k-ième plus petite valeur d’une série de données.
PREVISION Calcule une valeur par rapport à une tendance linéaire.
PROBABILITE Renvoie la probabilité que des valeurs d’une plage soient comprises entre
deux limites.
QUARTILE.EXCLURE Renvoie le quartile d’un jeu de données en fonction des valeurs du centile
comprises entre 0..1, exclues.
QUARTILE.INCLURE Renvoie le quartile d’un jeu de données.
RANG.POURCENTAGE.EXCLURE Renvoie le rang d’une valeur d’un jeu de données sous forme de
pourcentage (0..1, exclues).
RANG.POURCENTAGE.INCLURE Renvoie le rang en pourcentage d’une valeur d’un jeu de données.
SOMME.CARRES.ECARTS Renvoie la somme des carrés des écarts.
STDEVA Évalue l’écart type d’une population en se basant sur un échantillon de
cette population, nombres, texte et valeurs logiques inclus.
STDEVPA Calcule l’écart type d’une population à partir de l’ensemble de la
population, nombres, texte et valeurs logiques inclus.
T.TEST Renvoie la probabilité associée à un test T de Student.
TENDANCE Renvoie des valeurs par rapport à une tendance linéaire.

ANNEXES 79
 EXCEL Perfectionnement

VAR.P Calcule la variance sur la base de l’ensemble de la population.

VAR.S Calcule la variance sur la base d’un échantillon.
VARA Estime la variance d’une population en se basant sur un échantillon de
cette population, nombres, texte et valeurs logiques incluses.
VARPA Calcule la variance d’une population en se basant sur la population
entière, nombres, texte et valeurs logiques inclus.
Z.TEST Renvoie la valeur de probabilité unilatérale d’un test z.

 texte
ASC Change les caractères anglais ou katakana à pleine chasse (codés sur deux
octets) à l’intérieur d’une chaîne de caractères en caractères à demi-
chasse (codés sur un octet).
BAHTTEXT Convertit un nombre en texte en utilisant le format monétaire ß (baht).
CAR Renvoie le caractère spécifié par le code numérique.
CHERCHE, CHERCHERB Trouve un texte dans un autre texte (sans respecter la casse).
CNUM Convertit un argument textuel en nombre.
CODE Renvoie le numéro de code du premier caractère du texte.
CONCATENER Assemble plusieurs éléments textuels de façon à n’en former qu’un seul.
CTXT Convertit un nombre au format texte avec un nombre de décimales
spécifié.
DROITE, DROITEB Renvoie les caractères situés à l’extrême droite d’une chaîne de
caractères.
EPURAGE Supprime tous les caractères de contrôle du texte.
EURO Convertit un nombre en texte en utilisant le format monétaire € (euro).
EXACT Vérifie si deux valeurs de texte sont identiques.
GAUCHE, GAUCHEB Renvoie des caractères situés à l’extrême gauche d’une chaîne de
caractères.
JIS Change les caractères anglais ou katakana à demi-chasse (codés sur un
octet) à l’intérieur d’une chaîne de caractères en caractères à pleine
chasse (codés sur deux octets).
MAJUSCULE Convertit le texte en majuscules.
MINUSCULE Convertit le texte en minuscules.
NBCAR, LENB Renvoie le nombre de caractères contenus dans une chaîne de texte.
NOMPROPRE Met en majuscules la première lettre de chaque mot dans une chaîne
textuelle.
PHONÉTIQUE Extrait les caractères phonétiques (furigana) d’une chaîne de texte.
REMPLACER, REMPLACERB Remplace des caractères dans un texte.
REPT Répète un texte un certain nombre de fois.
STXT, MIDB Renvoie un nombre déterminé de caractères d’une chaîne de texte à partir
de la position que vous indiquez.
SUBSTITUE Remplace l’ancien texte d’une chaîne de caractères par un nouveau.
SUPPRESPACE Supprime les espaces du texte.
T Convertit ses arguments en texte.
TEXTE Convertit un nombre au format texte.
TROUVE, TROUVERB Trouve une valeur textuelle dans une autre, en respectant la casse.

ANNEXES 80
 EXCEL Perfectionnement

 compatibilité
IMPORTANT Toutes ces fonctions ont été remplacées par de nouvelles fonctions à la précision accrue et dont les
noms reflètent mieux les fonctionnalités. Bien que ces fonctions soient toujours disponibles pour une compatibilité
descendante, nous vous conseillons d’utiliser les nouvelles fonctions dès maintenant car ces fonctions risquent de ne
plus être disponibles dans les versions ultérieures d’Excel.
Pour plus d’informations sur les nouvelles fonctions, voir Fonctions statistiques.
BETA.INVERSE Renvoie l’inverse de la fonction de distribution cumulée pour une
distribution bêta spécifiée.
CENTILE Renvoie le k-ième centile des valeurs d’une plage.
COVARIANCE Renvoie la covariance, moyenne des produits des écarts pour chaque série
d’observations.
CRITERE.LOI.BINOMIALE Renvoie la plus petite valeur pour laquelle la distribution binomiale
cumulée est inférieure ou égale à une valeur de critère.
ECARTYPE Évalue l’écart type d’une population en se basant sur un échantillon de
cette population.
ECARTYPEP Calcule l’écart type d’une population à partir de la population entière.
INTERVALLE.CONFIANCE Renvoie l’intervalle de confiance pour une moyenne de population.
INVERSE.LOI.F Renvoie l’inverse de la distribution de probabilité F.
KHIDEUX.INVERSE Renvoie l’inverse de la probabilité unilatérale de la distribution khi-deux.
LOI.BETA Renvoie la fonction de distribution cumulée.
LOI.BINOMIALE Renvoie la probabilité d’une variable aléatoire discrète suivant la loi
binomiale.
LOI.BINOMIALE.NEG Renvoie la probabilité d’une variable aléatoire discrète suivant une loi
binomiale négative.
LOI.EXPONENTIELLE Renvoie la distribution exponentielle.
LOI.F Renvoie la distribution de probabilité F.
LOI.GAMMA Renvoie la probabilité d’une variable aléatoire suivant une loi Gamma.
LOI.GAMMA.INVERSE Renvoie, pour une probabilité donnée, la valeur d’une variable aléatoire
suivant une loi Gamma.
LOI.HYPERGEOMETRIQUE Renvoie la probabilité d’une variable aléatoire discrète suivant une loi
hypergéométrique.
LOI.KHIDEUX Renvoie la probabilité unilatérale de la distribution khi-deux.
LOI.LOGNORMALE Renvoie la distribution suivant une loi lognormale cumulée.
LOI.LOGNORMALE.INVERSE Renvoie l’inverse de la fonction de distribution suivant une loi lognormale
cumulée.
LOI.NORMALE Renvoie la probabilité d’une variable aléatoire continue suivant une loi
normale.
LOI.NORMALE.INVERSE Renvoie, pour une probabilité donnée, la valeur d’une variable aléatoire
suivant une loi normale standard.
LOI.NORMALE.STANDARD Renvoie la probabilité d’une variable aléatoire continue suivant une loi
normale standard.
LOI.NORMALE.STANDARD.INVERSE Renvoie l’inverse de la distribution cumulée normale standard.
LOI.POISSON Renvoie la probabilité d’une variable aléatoire suivant une loi de Poisson.
LOI.STUDENT Renvoie la probabilité d’une variable aléatoire suivant une loi T de
Student.
LOI.STUDENT.INVERSE Renvoie, pour une probabilité donnée, la valeur d’une variable aléatoire
suivant une loi T de Student.
LOI.WEIBULL Renvoie la probabilité d’une variable aléatoire suivant une loi de Weibull.

ANNEXES 81
 EXCEL Perfectionnement

MODE Renvoie la valeur la plus courante d’une série de données.

QUARTILE Renvoie le quartile d’une série de données.
RANG Renvoie le rang d’un nombre contenu dans une liste.
RANG.POURCENTAGE Renvoie le rang en pourcentage d’une valeur d’une série de données.
TEST.F Renvoie le résultat d’un test F.
TEST.KHIDEUX Renvoie le test d’indépendance.
TEST.STUDENT Renvoie la probabilité associée à un test T de Student.
TEST.Z Renvoie la valeur de probabilité unilatérale d’un test z.
VAR Calcule la variance sur la base d’un échantillon.
VAR.P Calcule la variance sur la base de l’ensemble de la population.

 fonctions définies par l’utilisateur installées avec les compléments

Si les compléments que vous installez contiennent des fonctions, ces fonctions de complément ou d’automatisation
seront disponibles dans la catégorie Défini par l’utilisateur de la boîte de dialogue Insérer une fonction.
Remarques
ces fonctions ne sont pas disponibles dans Excel Starter 2010
pour plus d’informations sur les fonctionnalités disponibles dans Excel Starter, voir Prise en charge des
fonctionnalités d’Excel Starter.
EUROCONVERT Convertit un nombre en euros, convertit un nombre en euros en une devise
de la zone européenne ou convertit un nombre exprimé en une devise de
la zone européenne en une autre, en utilisant l’euro comme intermédiaire
(triangulation).
FONCTION.APPELANTE Appelle une procédure dans une bibliothèque de liens dynamiques ou une
ressource de code.
REGISTRE.NUMERO Renvoie le numéro d’identification du Registre de la bibliothèque de liens
dynamiques qui a été spécifiée ou de la ressource de code qui a été mise
en Registre précédemment.
SQL.REQUEST Se connecte à une source externe de données, exécute une requête à
partir d’une feuille de calcul, puis renvoie le résultat sous forme de
matrice, sans qu’il soit nécessaire de programmer une macro.

ANNEXES 82
 EXCEL Perfectionnement

12.4 Index

A M
Appareil photo ........................................... 15 Mise à jour des liens ..................................... 11
Mise en forme conditionnelle .......................... 20
B
S
Barre de défilement ..................................... 25
BDLIRE ..................................................... 41 SOUS.TOTAL .............................................. 46
BDMAX ..................................................... 41 Styles de cellules ........................................ 26
BDMIN ...................................................... 41 Styles rapides ............................................. 45
BDMOYENNE............................................... 41
T
BDNB ....................................................... 41
BDNB ....................................................... 41 Tableau
BDNBVAL .................................................. 41 Convertir en plage .................................... 47
BDSOMME .................................................. 41 Mettre sous forme de tableau ...................... 45
Toupie ..................................................... 24
C
V
Case à cocher............................................. 24
Case d'option ............................................. 25 Validation des données ................................. 21
Classeur.XLTX ............................................ 56
Z
D
Zone d’extraction ........................................ 39
DATEDIF ................................................... 35 Zone de critères .......................................... 39
Dossier Modèles .......................................... 56 Zone de données ......................................... 39
Dossier Xlstart ............................................ 56 Zone de groupe ........................................... 25
Zone de liste .............................................. 24
F
Zone de liste déroulante ............................... 24
Feuil.XLTX................................................. 56
Format personnalisé ..................................... 19

ANNEXES 83