EXCEL Perfectionnement · Contrôler la saisie et les erreurs de calcul ... fonction matricielle TRANSPOSE ... 2.3 Affichage d’un tableau sous forme d’image

EXCEL Perfectionnement

2

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.


3

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


4

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


5

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


6

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


7

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


STOCKAGE DES INFORMATIONS 8

1 STOCKAGE DES INFORMATIONS

Organisation 1.1

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…)

Tableaux dans une même feuille 1.2

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…



Tableaux dans des feuilles différentes 1.3

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

Tableaux dans des classeurs différents 1.4

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



Feuilles groupées 1.5

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


ACCÈS AUX INFORMATIONS 11

2 ACCÈS AUX INFORMATIONS

Liens entre cellules, feuilles et classeurs 2.1

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 /



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

Fonctions de recherches et références 2.2

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



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



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



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)

Affichage d’un tableau sous forme d’image 2.3

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



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…

Zones nommées 2.4

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"



É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



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


CONTRÔLE ET VALIDATION DE LA SAISIE 19

3 CONTRÔLE ET VALIDATION DE LA SAISIE

Contrôle de la saisie 3.1

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

ANGLAIS FRANÇAIS HISTOIRE

GÉOGRAPHIE MATHS

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

Format de nombre personnalisé 3.2

À 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

ANGLAIS FRANÇAIS HISTOIRE-

GÉOGRAPHIE MATHS

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



Mise en forme conditionnelle 3.3

À 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


GÉOGRAPHIE MATHS

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 par formule 3.4

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


GÉOGRAPHIE MATHS

ALERTE PAR MESSAGE

ALERTE 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



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

Outil validation 3.5

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



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



types d'alertes d'erreur

Arrêter

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

Avertissement

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 : Oui pour accepter l’entrée non valide Non pour modifier l’entrée non valide Annuler pour supprimer l’entrée non valide

Informations

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 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

Contrôles de formulaire 3.6

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ô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



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



Repérage des saisies 3.7

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ÔLES DES RÉSULTATS 27

4 CONTRÔLES DES RÉSULTATS

Vérification des résultats 4.1

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



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

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

75010

75010

75009

75004



Gestion des erreurs renvoyées 4.2

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


FONCTIONS AVANCÉES 30

5 FONCTIONS AVANCÉES

Fonctions logiques 5.1

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 statistiques conditionnelles 5.2

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



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)

Tables d'hypothèses 5.3

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



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

mensuel

calcul km

annuel

calcul km

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 consommation

calcul montant consommation mensuelle

Fonctions texte 5.4

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

http://office.microsoft.com/client/helppreview14.aspx?AssetId=HP010343002&lcid=1036&NS=EXCEL&Version=14&tl=2&CTT=5&origin=HP010342953






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 dates 5.5

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





LISTES DE DONNÉES 36

6 LISTES DE DONNÉES

Organisation des données 6.1

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

Filtres automatiques 6.2


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…



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 piet 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

la recherche remplace la précédente

Ajouter la sélection actuelle au filtre

la recherche se combine avec la précédente

Cadenes

Cadic

Cadoret

Cadenes Perlat

Cadic Perring

Cadoret Perrusset



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…



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

Filtre avancé 6.3

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

12 PARIS 2

13

14 Code Nom Enfant Ville

15 146 HERICOURT 2 PARIS

16 145 BLASQUIZ 2 PARIS

DONNÉES CRITÈRES

EXTRACTION



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 ="=BORILES"

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)



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

Consolidation 6.4

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



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



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



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

Mode "Tableau" 6.5

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

le filtre est activé automatiquement en mode Tableau

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

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



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



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…



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


GRAPHIQUES 48

7 GRAPHIQUES

Organiser les données d'un graphique 7.1

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".

Modifier la source des données 7.2

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 49

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 !

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

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 50

Modifier l'emplacement d'un graphique 7.4

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

Graphiques superposés 7.5

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 51

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 !


PROTECTION 52

8 PROTECTION

Principes 8.1

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

Protection du classeur 8.2

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 53

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 54

Protection contre les modifications 8.3

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 55

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


MODÈLES 56

9 MODÈLES

Modification des modèles par défaut 9.1

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…


nom fichier ........... Feuil.XLTX mot réservé !


Modèles spécifiques 9.2

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


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…


nom fichier ........... nom_classeur.XLSX

type de fichier....... Classeur Excel (*.XLSX)

tous les classeur stockés dans ce dossier seront automatiquement ouverts avec Excel


OUTILS DE SIMULATION 57

10 OUTILS DE SIMULATION

Scénario 10.1

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


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



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"

Scénario du rapport de tableau croisé dynamique crée une feuille "Tableau croisé dynamique"

Valeur cible 10.2

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…


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 €

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.

Étiquettes de lignes Total

CARON 1670,25

LEFORT 1861,5

MARLET 1700,85



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)

Solveur 10.3

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


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



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…



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

Cambrai Nantes Périgueux

350 p 291 p 128 p

Quimper 450 p 0 p

Albi 300 p 0 p

0 p 0 p 0 p

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

total des

pièces

capacités de production

0,00 €

total des pièces envoyées

quantités demandées

nombre de pièces envoyées

des Usines vers les Entrepôts



minimum

maximum

valeur : 4192,63 €


350 p 291 p 128 p

Quimper 450 p 178 p 272 p 0 p 450 p

Albi 300 p 172 p 0 p 128 p 300 p

350 p 272 p 128 p


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

total des

pièces


3 021,28 €




350 p 291 p 128 p

Quimper 450 p 322 p 0 p 128 p 450 p

Albi 300 p 9 p 291 p 0 p 300 p

331 p 291 p 128 p


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

total des

pièces


5 181,31 €




350 p 291 p 128 p

Quimper 450 p 313 p 101 p 36 p 450 p

Albi 300 p 36 p 181 p 83 p 300 p

349 p 282 p 119 p


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

total des

pièces


4 192,63 €




AFFICHAGES 63

11 AFFICHAGES

Mode plan 11.1

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…


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 64

Affichages personnalisés 11.2

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…


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


ANNEXES 65

12 ANNEXES

Formats personnalisés 12.1

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 66

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

Pourcentages 12.2

12.2.1 Calculs

à partir du pourcentage

A B exemple : TVA à partir du TTC

1 25 19,6%

2 valeur pourcentage

3 4,9 =A1B1

à 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+A1B1

avant augmentation

A B exemple : HT à partir de TTC et TVA

1 60 10%

2 valeur/(1+pourcentage)

3 66 =A1/(1+B1)


ANNEXES 67

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 68

Fonctions Excel 12.3

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.

ms-help://MS.EXCEL.14.1036/EXCEL/content/HA10342389.htm








ANNEXES 69

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 70

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 71

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 72

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 73

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 74

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 75

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 76

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 77

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 78

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 79

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 80

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 81

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.

ms-help://MS.EXCEL.14.1036/EXCEL/content/HP10342656.htm#Statistical functions


ANNEXES 82

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 83

Index 12.4

A

Appareil photo ........................................... 15

B

Barre de défilement ..................................... 25 BDLIRE ..................................................... 41 BDMAX ..................................................... 41 BDMIN ...................................................... 41 BDMOYENNE ............................................... 41 BDNB ....................................................... 41 BDNB ....................................................... 41 BDNBVAL .................................................. 41 BDSOMME .................................................. 41

C

Case à cocher ............................................. 24 Case d'option ............................................. 25 Classeur.XLTX ............................................ 56

D

DATEDIF ................................................... 35 Dossier Modèles .......................................... 56 Dossier Xlstart ............................................ 56

F

Feuil.XLTX ................................................. 56 Format personnalisé ..................................... 19

M

Mise à jour des liens ..................................... 11 Mise en forme conditionnelle .......................... 20

S

SOUS.TOTAL .............................................. 46 Styles de cellules ........................................ 26 Styles rapides ............................................. 45

T

Tableau Convertir en plage .................................... 47 Mettre sous forme de tableau ...................... 45

Toupie ..................................................... 24

V

Validation des données ................................. 21

Z

Zone d’extraction ........................................ 39 Zone de critères .......................................... 39 Zone de données ......................................... 39 Zone de groupe ........................................... 25 Zone de liste .............................................. 24 Zone de liste déroulante ............................... 24