Syl excel avec pg

Institut d'enseignement de promotion sociale de la Fédération Wallonie-Bruxelles Arlon - Athus - Musson - Virton

Excel 2010

Réf.: D-F04-66b (09_2014)

Dominique Lieffrig

Dominique Lieffrig

 Excel 2010

TABLE DES MATIERES EXCEL: FONCTIONS DE BASE ........................................................................................................................................ 1 1

DÉCOUVERTE D'EXCEL 2010: CRÉER VOTRE PREMIÈRE FEUILLE DE CALCUL .............................................................................. 2 1.1 Découverte de l'interface graphique ............................................................................................................. 2 1.2 Créer une feuille de calcul.............................................................................................................................. 2 1.3 Enregistrer une feuille de calcul .................................................................................................................... 3 1.4 Préparer l’impression puis imprimer ............................................................................................................. 3 1.5 Feuilles de calcul et adresses de cellules ....................................................................................................... 5 1.6 Naviguer dans une feuille de calcul ............................................................................................................... 5 1.7 Entrer des données ........................................................................................................................................ 5 1.8 Fonctions permettant d’entrer plus rapidement des données ...................................................................... 6 1.9 Modifier les données ..................................................................................................................................... 6 1.10 Renvoyer du texte à la ligne dans une cellule................................................................................................ 7 1.11 Effectuer des opérations mathématiques dans Excel .................................................................................... 7 1.12 Insérer ou supprimer des lignes et des colonnes ........................................................................................... 8 1.13 Insérer ou supprimer des feuilles de calcul .................................................................................................... 8 1.14 Renommer un onglet de la feuille de calcul .................................................................................................. 9 2 LES GRAPHIQUES ......................................................................................................................................................... 9 2.1 Découvrir les graphiques ............................................................................................................................... 9 2.1.1 2.1.2 2.1.3 2.1.4 2.1.5

2.2 2.3 2.3.1 2.3.2 2.3.3 2.3.4

2.4 2.4.1 2.4.2 2.4.3 2.4.4

3

Connaître les éléments d’un graphique ................................................................................................................... 10 Modification d’un graphique de base en fonction des besoins ................................................................................ 11 Application d’un style de graphique et d’une disposition de graphique prédéfinis pour obtenir des graphiques de qualité professionnelle ............................................................................................................................................. 11 Ajout d’une mise en forme visuellement attrayante à un graphique ....................................................................... 12 Réutilisation de graphiques en créant des modèles de graphiques ......................................................................... 12

Étape 1: créer un graphique de base ........................................................................................................... 12 Étape 2 : modifier la disposition ou le style d’un graphique ........................................................................ 13 Appliquer une disposition de graphique prédéfinie ................................................................................................. 14 Appliquer un style de graphique prédéfini ............................................................................................................... 14 Modifier manuellement la disposition des éléments du graphique ......................................................................... 14 Modifier manuellement la mise en forme des éléments du graphique ................................................................... 15

Étape 3 : ajouter ou supprimer des titres ou des étiquettes de données .................................................... 15 Ajouter un titre de graphique ................................................................................................................................... 15 Ajouter des titres d’axes ........................................................................................................................................... 16 Lier un titre à une cellule de feuille de calcul ........................................................................................................... 17 Ajouter des étiquettes de données .......................................................................................................................... 17

2.5 Étape 4: afficher ou masquer une légende .................................................................................................. 18 FORMULES, RÉFÉRENCES ET FONCTIONS ......................................................................................................................... 19 3.1 Types de références de cellules ................................................................................................................... 19 3.2 Ordre des opérateurs mathématiques ........................................................................................................ 19 3.3 Rechercher des fonctions............................................................................................................................. 19 3.4 Utiliser la fonction VPM............................................................................................................................... 21 3.5 Utiliser la fonction NOMPROPRE ................................................................................................................. 22 3.6 Copier une formule d’une cellule dans une autre ........................................................................................ 23 3.6.1 3.6.2

Utilisation de la poignée de recopie ......................................................................................................................... 23 Utilisation de la fonction Copier et coller ................................................................................................................. 23

3.7 Erreurs dans les formules ............................................................................................................................ 24 EXCEL - FONCTIONS DE BASE ET GRAPHIQUES: EXERCICES................................................................................................... 25 4.1 Exercice 1 (avec procédure détaillée) .......................................................................................................... 25 4.2 Exercice 2 ..................................................................................................................................................... 32 4.3 Exercice 3 ..................................................................................................................................................... 34 4.4 Exercice 4 (avec notes concernant les outils utilisés) .................................................................................. 35 4.5 Exercice 5 (avec notes concernant les formules utilisées) ........................................................................... 37 4.6 Exercice 6 ..................................................................................................................................................... 38 4.7 Exercice 7 (avec procédure détaillée) .......................................................................................................... 39 4.8 Exercice 8 ..................................................................................................................................................... 44 5 TRI ET FILTRAGE DE DONNÉES ...................................................................................................................................... 45 4

Réf.: D-F04-66b (09_2014)

Dominique Lieffrig

5.1 5.2 5.2.1 5.2.2

 Excel 2010

Présentation sur base de l'exercice 9 .......................................................................................................... 45 Exercices ...................................................................................................................................................... 50 Exercice 10 ............................................................................................................................................................... 50 Exercice 11 ............................................................................................................................................................... 51

6

EXERCICES DE RENFORCEMENT..................................................................................................................................... 52 6.1 Exercice 12 ................................................................................................................................................... 52 6.2 Exercice 13................................................................................................................................................... 54 6.3 Exercice 14 ................................................................................................................................................... 55 6.4 Exercice 15 ................................................................................................................................................... 55 6.5 Exercice 16 (figer les volets et gérer l'impression) ....................................................................................... 56 6.6 Exercice 17 (les formules, les références relatives, absolues et mixtes) ...................................................... 59 6.7 Exercice 18 (les formules, les références relatives, absolues et mixtes) ...................................................... 60 7 LES FORMATS ........................................................................................................................................................... 60 7.1 Exercice 19 (exercice solutionné) ................................................................................................................. 60 7.2 Exercice 20 (exercice non solutionné) .......................................................................................................... 68 8 UTILISATION DE PLUSIEURS FEUILLES AU SEIN D'UN CLASSEUR............................................................................................. 69 8.1 Exercice 21 (exercice solutionné) ................................................................................................................. 69 8.2 Exercice 22 (exercice non solutionné) .......................................................................................................... 75 EXCEL: ALLONS PLUS LOIN ......................................................................................................................................... 76 1

LES FONCTIONS......................................................................................................................................................... 77 1.1 Fonctions mathématiques ........................................................................................................................... 77 1.1.1 1.1.2

1.2 1.2.1 1.2.2

1.3 1.3.1 1.3.2

1.4 1.4.1 1.4.2

1.5 1.5.1 1.5.2 1.5.3 1.5.4 1.5.5 1.5.6

Présentation ............................................................................................................................................................. 77 Exercices ................................................................................................................................................................... 78

Fonctions statistiques .................................................................................................................................. 79 Présentation ............................................................................................................................................................. 79 Exercice(s) ................................................................................................................................................................ 80

Fonctions financières ................................................................................................................................... 81 Présentation ............................................................................................................................................................. 81 Exercice(s) ................................................................................................................................................................ 83

Fonctions logiques ....................................................................................................................................... 84 Présentation ............................................................................................................................................................. 84 Exercice(s) ................................................................................................................................................................ 85

Exercices complémentaires concernant les fonctions ................................................................................. 86 Exercice 28 (fonctions de type "date/heure") .......................................................................................................... 86 Exercice 29 (fonctions de type "bases de données") ................................................................................................ 87 Exercice 30 (fonctions de type "bases de données") ................................................................................................ 88 Exercice 31 (fonctions MAJUSCULE( ), SOMME.SI( ), NB.SI( )…) ............................................................................... 88 Exercice 32 (la fonction SI et la fonction ET)............................................................................................................. 89 Exercice 33 (la fonction RECHERCHEV( ) )................................................................................................................. 90

2

UTILISATION ET CRÉATION DE MODÈLES ......................................................................................................................... 91 2.1 Créer un classeur Excel 2010 à partir d’un modèle ..................................................................................... 91 2.2 Créer un modèle avec Excel 2010 ................................................................................................................ 92 3 GESTION AVANCÉE DE DONNÉES................................................................................................................................... 95 3.1 Les filtrages avancés ................................................................................................................................... 95 3.2 Convertir des données ...............................................................................................................................101 3.3 Supprimer des doublons ............................................................................................................................106 3.4 Validation des données .............................................................................................................................107 3.5 Consolidation des données ........................................................................................................................113 3.6 L'analyse de scénarios ...............................................................................................................................119 3.6.1 3.6.2 3.6.3

Utilisation de l’outil "Valeur cible" pour déterminer comment obtenir un résultat .............................................. 120 Utilisation de tables de données pour évaluer les effets d’une ou deux variables sur une formule ...................... 122 Gestionnaire de scénarios ...................................................................................................................................... 126

3.7 Grouper, dissocier des données et gérer des sous-totaux .........................................................................133 3.8 Les tableaux croisés dynamiques ..............................................................................................................137 3.9 Les graphiques croisés dynamiques ..........................................................................................................158 4 APPROFONDISSEMENT CONCERNANT LES DATES ET LES HEURES ........................................................................................165 4.1 Interprétation de l'heure par Excel ............................................................................................................165 4.2 Une horloge qui débute à minuit ...............................................................................................................165 Réf.: D-F04-66b (09_2014)

Dominique Lieffrig

5 6

7 8

9

 Excel 2010

4.3 Pour qu'Excel reconnaisse une valeur comme une heure ..........................................................................165 4.4 Utilisation d'heures dans les formules .......................................................................................................167 4.5 Utilisation de la fonction CONVERT( ) avec des heures .............................................................................169 EXERCICES DE RENFORCEMENT...................................................................................................................................170 LA PROTECTION DE CELLULES ET DE CLASSEURS ..............................................................................................................188 6.1 Protéger certaines cellules d'une feuille de calcul .....................................................................................188 6.2 Protéger l'intégralité d'un classeur à l'ouverture ......................................................................................191 LES MACROS ET LA PERSONNALISATION DU RUBAN .........................................................................................................193 RACCOURCIS CLAVIER DANS EXCEL ..............................................................................................................................199 8.1 Accès clavier au ruban ...............................................................................................................................199 8.2 Raccourcis clavier utilisant la touche CTRL ................................................................................................199 8.3 Touches de fonction ..................................................................................................................................202 8.4 Autres raccourcis clavier utiles ..................................................................................................................203 QUELQUES EXERCICES POUR TERMINER........................................................................................................................206

Réf.: D-F04-66b (09_2014)

Dominique Lieffrig

 Excel 2010

EXCEL: FONCTIONS DE BASE

Réf.: D-F04-66b (09_2014)

1

Dominique Lieffrig

1

 Excel 2010

Découverte d'Excel 2010: créer votre première feuille de calcul

Ressource: http://office.microsoft.com/fr-be/excel-help/get-to-know-excel-2010-create-your-firstspreadsheet-RZ101773335.aspx?redir=0 1.1

Découverte de l'interface graphique Barre d'outils "Accès rapide"

Zone "Nom"

Ruban

Onglet

Barre de formule

Groupe de commandes

Lanceur de boîte de dialogue

Zone de travail Barre d'état

1.2

Créer une feuille de calcul

Procédure pour créer une nouvelle feuille de calcul: 1°) Dans le ruban, cliquez sur "Fichier".

2°) Dans la partie gauche de la fenêtre, cliquez sur "Nouveau".

3°) Cliquez sur "Créer".

Réf.: D-F04-66b (09_2014)

2

Dominique Lieffrig

1.3

 Excel 2010

Enregistrer une feuille de calcul

Procédure pour enregistrer une feuille de calcul: 1°) 2°) 3°) 4°) 5°)

Dans le ruban, cliquez sur "Fichier".

Dans la partie gauche de la fenêtre, cliquez sur "Enregistrer" ou "Enregistrer sous".

Sélectionnez un dossier dans lequel enregistrer le fichier.

Dans la zone "Nom de fichier", encodez le nom de la feuille de calcul.

Cliquez sur "Enregistrer".

NB: le bouton suivant présent dans la barre d'Accès rapide peut également être utilisé: 1.4

Préparer l’impression puis imprimer

Procédure pour obtenir un aperçu avant impression puis imprimer: 1°) Dans le ruban, cliquez sur "Fichier".

2°) Dans la partie gauche de la fenêtre, cliquez sur "Imprimer".

3°) Dans la partie droite de la fenêtre obtenue, une vue "Aperçu avant impression" vous montre à quoi ressemblera la feuille de calcul à l’impression.

4°) Au besoin, sous "Imprimante", sélectionnez une imprimante.

5°) Indiquez le nombre de copies.

6°) Sélectionnez les options souhaitées sous "Paramètres".

7°) Sous "Paramètres", cliquez sur "Mise en page" pour sélectionner, par exemple, l’impression du quadrillage.

Réf.: D-F04-66b (09_2014)

3

Dominique Lieffrig

 Excel 2010

8°) Cliquez ensuite sur "Imprimer" en haut de la fenêtre.

Conseil: vous pouvez également sélectionner des options d’impression via l’onglet "Mise en page" dans le ruban.

L'impression et l'aperçu avant impression peuvent également être gérés via les boutons suivants si vous les avez ajoutés dans la barre d'Accès rapide.

Réf.: D-F04-66b (09_2014)

4

Dominique Lieffrig

1.5

 Excel 2010

Feuilles de calcul et adresses de cellules

Les feuilles de calcul sont constituées de colonnes, de lignes et de cellules. Les colonnes possèdent des en-têtes alphabétiques commençant à A, B, C, et ainsi de suite. Après les 26 lettres de l’alphabet, Excel associe les titres de colonnes à des paires allant de AA à ZZ, puis à des assemblages allant de AAA à ZZZ, et ainsi de suite. Une feuille de calcul Excel possède en tout 16 384 colonnes.

Les lignes possèdent des titres qui sont des nombres commençant à 1, 2, 3, et ainsi de suite.

Une feuille de calcul Excel possède plus d'un million de lignes.

Une cellule représente le point d’intersection entre une colonne et une ligne; c’est l’endroit où vous encodez des données. Lorsque vous cliquez dans une cellule, celle-ci devient la cellule active et est entourée de noir. Si vous encodez des données à ce stade, elles figureront dans cette cellule.

Utilisez la zone "Nom" dans le ruban pour voir l’adresse de la cellule dans laquelle vous encodez des données.

Si, en examinant la zone "Nom", vous voyez par exemple E5, cela signifie que le curseur se trouve dans la colonne E, à la ligne 5. Les adresses de cellules non seulement vous indiquent l’emplacement d’un élément dans une feuille de calcul, mais elles sont également utilisées dans les formules Excel.

Pour utiliser la zone "Nom" afin d’atteindre une autre cellule, encodez l’adresse de la cellule dans la zone "Nom", puis appuyez sur la touche Enter/Return. Pour revenir au début de la feuille, encodez A1 dans la zone "Nom", puis appuyez à nouveau sur la touche Enter/Return.

1.6

Naviguer dans une feuille de calcul

Pour se déplacer dans une feuille de calcul:      1.7

Cliquez dans n’importe quelle cellule pour y placer le curseur.

Appuyez sur la touche Entrée pour descendre d’une cellule.

Appuyez sur la touche Tab pour aller vers la cellule de droite.

Utilisez les flèches Haut, Bas, Droite ou Gauche de votre clavier pour déplacer le curseur dans la direction souhaitée.

Utilisez la zone "Nom" comme indiqué dans le point précédent pour atteindre une cellule donnée.

Entrer des données

Pour entrer une date: 

Utilisez une barre oblique (/) ou un signe moins (-) pour séparer les différentes composantes d’une date. Par exemple, 15/7/2012 ou 15-juillet-2012. Pour entrer la date système, formez la combinaison de touches Ctrl + ;

Pour entrer une heure: 

Lors de l'encodage d'une heure, il faut séparer le nombre d'heures, le nombre de minutes et le nombre de secondes par le symbole : Exemple: 14:25:45 Pour entrer l'heure système, formez la combinaison de touches Ctrl + :

Réf.: D-F04-66b (09_2014)

5

Dominique Lieffrig

 Excel 2010

Pour entrer des fractions:  

1.8

Laissez un espace entre le nombre entier et la fraction. Par exemple, 1 1/8 Pour entrer uniquement une fraction, entrez tout d’abord un zéro. Par exemple, 0 1/4 Sinon Excel considère qu'il s'agit d'une date.

Fonctions permettant d’entrer plus rapidement des données

Saisie semi-automatique Si les premiers caractères que vous encodez dans une cellule correspondent à ceux d’une autre entrée (dans une zone contiguë), Excel remplit les caractères restants pour vous. Appuyez simplement sur la touche Entrée ou Tab pour accepter l’entrée.

Recopie incrémentée Encodez une entrée dans une cellule, par exemple le jour de la semaine ou un mois de l’année. Déplacez le curseur vers le coin inférieur droit de la cellule jusqu’à ce que le curseur se transforme en signe plus noir. Cliquez et faites glisser la poignée de recopie (le signe plus noir) pour remplir le reste de la série. Vous pouvez faire glisser le pointeur de la souris vers le bas d’une colonne ou en travers d’une ligne. Relâchez le bouton de la souris lorsque vous avez terminé.

1.9

Modifier les données

Pour modifier, insérer, supprimer des données au sein d'une cellule, réalisez l’une des opérations suivantes: 

Double-cliquez sur la cellule contenant les données à modifier OU cliquez sur la cellule contenant les données à modifier, puis cliquez dans la barre de formule pour effectuer les modifications (la touche F2 peut également être utilisée).



Pour supprimer des caractères, appuyez sur la touche Ret. arr. , ou sélectionnez-les pour les mettre en surbrillance, puis appuyez sur la touche Suppr/Delete.

Réf.: D-F04-66b (09_2014)

6

Dominique Lieffrig

  

 Excel 2010

Pour insérer des caractères, effectuez un double-clic dans la cellule à l’endroit choisi, puis encodez les nouveaux caractères.

Pour remplacer des caractères, sélectionnez-les, puis encodez les nouveaux caractères.

Pour valider des modifications, appuyez sur la touche Entrée ou Tab après avoir effectué vos modifications.

1.10 Renvoyer du texte à la ligne dans une cellule Procédure pour renvoyer du texte à la ligne dans une cellule: Dans le groupe "Alignement" de l’onglet "Accueil", cliquez sur "Renvoyer à la ligne automatiquement".

NB: le raccourci clavier Alt + Enter/Return peut également être utilisé.

1.11 Effectuer des opérations mathématiques dans Excel Pour effectuer des opérations mathématiques dans Excel, vous devez créer des formules qui commencent toujours par le signe égal (=).    

Pour ajouter, encodez =89+23 Pour soustraire, encodez =89-23 Pour multiplier, encodez =89*23 Pour diviser, encodez =89/23

D'autre part, pour effectuer une somme par exemple, il suffit d'utiliser le bouton suivant présent dans le ruban.

Via ce bouton, vous pouvez accéder à d'autres fonctions: Moyenne, NB, Max. et Min.

Réf.: D-F04-66b (09_2014)

7

Dominique Lieffrig

 Excel 2010

1.12 Insérer ou supprimer des lignes et des colonnes Pour insérer: 



Une colonne, cliquez sur une cellule se trouvant immédiatement à droite de la nouvelle colonne que vous souhaitez insérer, ou une ligne, cliquez sur une cellule située immédiatement en dessous de l’endroit où vous souhaitez insérer une nouvelle ligne, puis: Sous l’onglet "Accueil", dans le groupe "Cellules", cliquez sur la flèche en regard du bouton "Insérer", puis effectuez l’une des actions suivantes: - Insérer des colonnes dans la feuille ou - Insérer des lignes dans la feuille.

Pour supprimer:  

Une colonne ou une ligne, placez votre curseur dans la colonne ou la ligne que vous voulez supprimer, puis Sous l’onglet "Accueil", dans le groupe "Cellules", cliquez sur la flèche en regard de "Supprimer", puis effectuez l’une des actions suivantes: - Supprimer des colonnes dans la feuille ou - Supprimer des lignes dans la feuille.

1.13 Insérer ou supprimer des feuilles de calcul Pour insérer une feuille de calcul en dernière position: En bas de l'écran, cliquez sur le bouton "Insérer une feuille de calcul".

Pour supprimer une feuille de calcul: Cliquez sur l’onglet de la feuille que vous voulez supprimer. Ensuite, cliquez avec le bouton droit et sélectionnez "Supprimer".

Réf.: D-F04-66b (09_2014)

8

Dominique Lieffrig

 Excel 2010

1.14 Renommer un onglet de la feuille de calcul Effectuez un clic droit sur l'onglet correspondant à la feuille que vous souhaitez renommer, puis sélectionnez "Renommer". Le nom de la feuille est mis en surbrillance sur l’onglet. Remplacez le texte existant, puis appuyez sur Entrée.

2

Les graphiques

Ressource: http://office.microsoft.com/fr-be/excel-help/comment-creer-un-graphique-elementairedans-excel-2010-RZ102559017.aspx 2.1

Découvrir les graphiques

Les graphiques permettent d’afficher des séries de données numériques sous forme graphique afin de faciliter la compréhension de quantités de données volumineuses et la relation entre les différentes séries de données.

Pour créer un graphique dans Excel, vous commencez par entrer les données numériques du graphique dans une feuille de calcul. Ensuite, vous pouvez tracer ces données sous la forme d’un graphique en sélectionnant le type de graphique de votre choix sous l’onglet "Insertion", dans le groupe "Graphiques".

1 2

Données d'une feuille de calcul Graphique créé à partir de données d'une feuille de calcul

Excel prend en charge de nombreux types de graphiques pour vous permettre d’afficher des données de manière à ce qu’elles soient facilement lisibles. Lorsque vous créez un graphique ou en modifiez Réf.: D-F04-66b (09_2014)

9

Dominique Lieffrig

 Excel 2010

un existant, vous pouvez choisir parmi de nombreux types de graphiques (un histogramme ou un graphique à secteurs, par exemple) et leurs sous-types (un histogramme empilé ou un graphique à secteurs 3D, par exemple). Vous pouvez également créer un graphique combiné en créant un graphique qui fait appel à plusieurs types de graphiques.

Exemple de graphique combiné dans lequel un histogramme et un graphique en courbes sont utilisés.

2.1.1 Connaître les éléments d’un graphique Un graphique possède de nombreux éléments. Certains de ces éléments apparaissent par défaut, tandis que d’autres peuvent être ajoutés en fonction des besoins. Vous pouvez modifier l’affichage des éléments du graphique en les déplaçant à l’intérieur de celui-ci, en les redimensionnant ou en modifiant leur mise en forme. Vous pouvez également supprimer les éléments de graphique que vous ne souhaitez pas afficher.

1 2 3 4 5 6 7

Zone de graphique du graphique Zone de traçage du graphique Points de données des séries de données tracées dans le graphique Axe horizontal (abscisses) et axe vertical (ordonnées) Légende du graphique Titre de graphique et d’axe utilisable dans le graphique Étiquette de données permettant d’identifier les détails d’un point de données dans une série de données

Réf.: D-F04-66b (09_2014)

10

Dominique Lieffrig

 Excel 2010

2.1.2 Modification d’un graphique de base en fonction des besoins Une fois que vous avez créé un graphique, vous pouvez modifier n’importe lequel de ses éléments.

Par exemple, vous pouvez changer la façon dont les axes sont affichés, ajouter un titre, afficher ou masquer la légende ou bien encore afficher davantage d’éléments de graphique.

Pour modifier un graphique, vous pouvez effectuer une ou plusieurs des opérations suivantes: 







Modifier l’affichage des axes du graphique Vous pouvez spécifier l’échelle des axes et ajuster l’intervalle entre les valeurs ou les catégories affichées. Pour rendre votre graphique plus lisible, vous pouvez également ajouter des graduations à un axe et spécifier l’intervalle auquel elles doivent apparaître.

Ajouter des titres et des étiquettes de données à un graphique Pour clarifier les informations présentées dans votre graphique, vous pouvez ajouter un titre, des titres d’axe et des étiquettes de données.

Ajouter une légende ou une table de données Vous pouvez afficher ou masquer une légende, la déplacer ou modifier ses éléments. Dans certains graphiques, vous pouvez également afficher une table de données qui affiche les symboles de légende et les valeurs présentés dans le graphique.

Appliquer des options spéciales à chaque type de graphique Des lignes spéciales (telles que des lignes haut-bas et des courbes de tendance), des barres (telles que des barres haut-bas et des barres d’erreur), des indicateurs de données ainsi que d’autres options sont disponibles pour les différents types de graphiques.

2.1.3 Application d’un style de graphique et d’une disposition de graphique prédéfinis pour obtenir des graphiques de qualité professionnelle Au lieu d’ajouter ou de modifier manuellement des éléments du graphique ou de le mettre en forme, vous pouvez lui appliquer rapidement une disposition de graphique et un style de graphique prédéfinis. Excel met à votre disposition toute une série de dispositions et de styles prédéfinis. Vous pouvez toutefois affiner une disposition ou un style en modifiant manuellement la disposition et la mise en forme des différents éléments du graphique, tels que la zone de graphique, la zone de traçage, les séries de données ou la légende du graphique.

Lorsque vous appliquez une disposition de graphique prédéfinie, un ensemble spécifique d’éléments de graphique (tels que des titres, une légende, une table de données ou des étiquettes de données) est affiché d’une manière spécifique dans votre graphique. Chaque type de graphique propose plusieurs dispositions parmi lesquelles vous pouvez effectuer votre sélection.

Lorsque vous appliquez un style de graphique prédéfini, le graphique est mis en forme dans le thème de document que vous avez appliqué de sorte que votre graphique soit conforme aux couleurs du thème (un ensemble de couleurs), aux polices du thème (un ensemble de polices d’entête et de corps de texte) et aux effets du thème (un ensemble de ligne et d’effets de remplissage) que vous ou votre entreprise utilisez.

Vous ne pouvez pas créer vos propres dispositions ou styles de graphiques, mais vous pouvez créer des modèles de graphiques qui incluent la disposition et la mise en forme que vous voulez.

Réf.: D-F04-66b (09_2014)

11

Dominique Lieffrig

 Excel 2010

2.1.4 Ajout d’une mise en forme visuellement attrayante à un graphique Outre l’application d’un style de graphique prédéfini, vous pouvez facilement appliquer une mise en forme à des éléments individuels d’un graphique, par exemple des indicateurs de données, la zone de graphique, la zone de traçage ainsi que les nombres et le texte des titres et les étiquettes pour donner à votre graphique un aspect à la fois attrayant et personnalisé. Vous pouvez appliquer des styles de forme ainsi que des styles WordArt et mettre en forme manuellement les formes et le texte des éléments du graphique.

Pour ajouter une mise en forme, vous pouvez effectuer une ou plusieurs des opérations suivantes:   



Remplir les éléments du graphique Vous pouvez utiliser des couleurs, des textures, des images et des dégradés pour attirer l’attention sur des éléments spécifiques du graphique.

Modifier le contour des éléments du graphique Vous pouvez utiliser des couleurs, des styles de lignes et des épaisseurs de lignes pour mettre en évidence des éléments du graphique.

Ajouter des effets spéciaux aux éléments du graphique Vous pouvez appliquer des effets spéciaux, comme une ombre, une lumière, un reflet, des bordures arrondies, un biseau ou une rotation 3D à des éléments de graphique, pour donner à votre graphique un aspect fini.

Mettre en forme les nombres et le texte Vous pouvez mettre en forme le texte et les nombres des titres, des étiquettes et des zones de texte de votre graphique tout comme vous le faites dans une feuille de calcul. Pour faire ressortir le texte et les nombres, vous pouvez appliquer des styles WordArt.

2.1.5 Réutilisation de graphiques en créant des modèles de graphiques Si vous voulez réutiliser un graphique que vous avez adapté à vos besoins, vous pouvez l’enregistrer en tant que modèle de graphique (*.crtx) dans le dossier des modèles de graphiques.

Lorsque vous créez un graphique, vous pouvez appliquer votre modèle de graphique tout comme vous le faites avec un type de graphique intégré. En fait, les modèles de graphiques sont des types de graphiques personnalisés: vous pouvez également les utiliser pour modifier le type de graphique d’un graphique existant. Si vous utilisez souvent un modèle de graphique, vous pouvez l’enregistrer en tant que modèle par défaut.

2.2

Étape 1: créer un graphique de base

Pour la plupart des graphiques, tels que les histogrammes et les graphiques en barres, vous pouvez tracer, dans un graphique, les données disposées en lignes et colonnes dans une feuille de calcul.

1°) Dans la feuille de calcul, organisez les données à reprendre dans le graphique.

Les données peuvent être organisées en lignes ou colonnes: Excel détermine automatiquement la meilleure façon de tracer les données dans le graphique. Certains types de graphiques, comme les graphiques en bulles ou en secteurs, nécessitent une disposition de données spéciale.

2°) Sélectionnez les cellules qui contiennent les données à utiliser pour le graphique.

NB: si vous sélectionnez une seule cellule, Excel trace automatiquement dans un graphique toutes les cellules adjacentes à cette cellule qui contiennent des données. Si les cellules à tracer dans un graphique n’appartiennent pas à une plage continue, vous pouvez sélectionner des cellules ou des plages non adjacentes, à condition que la sélection forme un rectangle.

Vous pouvez également masquer les lignes ou les colonnes que vous ne souhaitez pas tracer dans le graphique.

Réf.: D-F04-66b (09_2014)

12

Dominique Lieffrig

 Excel 2010

3°) Dans le groupe "Graphiques" de l’onglet "Insertion", effectuez l’une des opérations suivantes: 

Cliquez sur le type de graphique, puis cliquez sur le sous-type de graphique à utiliser.



Pour afficher tous les types de graphiques disponibles, cliquez sur pour lancer la boîte de dialogue "Insérer un graphique", puis cliquez sur les flèches pour parcourir les types de graphiques.

4°) Par défaut, le graphique est placé dans la feuille de calcul sous la forme d’un graphique incorporé. Si vous souhaitez placer le graphique dans une feuille de graphique séparée, vous pouvez modifier son emplacement comme suit: a) Cliquez n’importe où dans le graphique incorporé afin de l’activer. Cela permet d’afficher les "Outils de graphique" et les onglets "Création", "Disposition" et "Mise en forme". b) Sous l’onglet "Création", dans le groupe "Emplacement", cliquez sur "Déplacer le graphique".

c) Sous "Choisissez l’endroit où vous voulez placer le graphique", effectuez l’une des actions suivantes: 



2.3

Pour afficher le graphique dans une feuille de graphique, cliquez sur "Nouvelle feuille".

NB: si vous souhaitez remplacer le nom proposé pour le graphique, vous pouvez encoder un nouveau nom dans la zone "Nouvelle feuille".

Pour afficher le graphique en tant que graphique incorporé dans une feuille de calcul, cliquez sur "Objet dans" et sélectionnez la feuille voulue.

Étape 2 : modifier la disposition ou le style d’un graphique

Vous pouvez modifier l’aspect d’un graphique immédiatement après l’avoir créé. Au lieu d’ajouter ou de modifier manuellement des éléments du graphique ou de le mettre en forme, vous pouvez lui appliquer rapidement une disposition et un style prédéfinis. Excel met à votre disposition toute une série de dispositions et de styles prédéfinis (ou dispositions rapides et styles rapides), mais Réf.: D-F04-66b (09_2014)

13

Dominique Lieffrig

 Excel 2010

vous pouvez en personnaliser un en modifiant manuellement la disposition et la mise en forme de chaque élément du graphique.

2.3.1 Appliquer une disposition de graphique prédéfinie Pour choisir une disposition de graphique prédéfinie: 1°) Cliquez n’importe où dans le graphique que vous souhaitez mettre en forme en utilisant une disposition de graphique prédéfinie. Cela permet d’afficher les "Outils de graphique" et les onglets "Création", "Disposition" et "Mise en forme".

2°) Dans le groupe "Dispositions du graphique" de l’onglet "Création", cliquez sur la disposition que vous voulez utiliser.

2.3.2 Appliquer un style de graphique prédéfini Pour appliquer un style de graphique prédéfini: 1°) Cliquez n’importe où dans le graphique que vous souhaitez mettre en forme en utilisant un style de graphique prédéfini. Cela permet d’afficher les "Outils de graphique" et les onglets "Création", "Disposition" et "Mise en forme".

2°) Sous l’onglet "Création", dans le groupe "Styles du graphique", cliquez sur le style de graphique à utiliser.

2.3.3 Modifier manuellement la disposition des éléments du graphique Pour modifier manuellement la disposition des éléments du graphique: 1°) Sélectionnez l’élément de graphique dont vous souhaitez modifier la disposition.

2°) Sous l’onglet "Disposition", dans les groupes "Étiquettes", "Axes" ou "Arrière-plan", cliquez sur le bouton d’élément de graphique correspondant à l’élément de graphique que vous avez sélectionné, puis cliquez sur l’option de disposition de votre choix.

NB: les options de disposition choisies sont appliquées à l’élément de graphique que vous avez sélectionné. Par exemple, si vous avez sélectionné tout le graphique, les étiquettes de données sont appliquées à toutes les séries de données. Si vous n’avez sélectionné qu’une seule série de données, les étiquettes ne sont appliquées qu’à cette série.

Réf.: D-F04-66b (09_2014)

14

Dominique Lieffrig

 Excel 2010

2.3.4 Modifier manuellement la mise en forme des éléments du graphique Pour modifier manuellement la mise en forme des éléments du graphique: 1°) Sélectionnez l’élément de graphique dont vous souhaitez modifier la mise en forme.

2°) En utilisant l'onglet "Mise en forme", apportez les modifications voulues.

2.4

Étape 3 : ajouter ou supprimer des titres ou des étiquettes de données

Pour simplifier la lecture d’un graphique, vous pouvez ajouter des titres, soit au graphique luimême, soit aux axes du graphique. Les titres des axes sont généralement disponibles pour tous les axes affichables dans un graphique, y compris les axes de profondeur (axes des séries) dans les graphiques 3D. Certains types de graphiques, tels que les graphiques en radar, sont munis d’axes, mais ne peuvent pas afficher les titres des axes. C’est également le cas des graphiques sans axe, tels que les graphiques de type anneau ou les graphiques en secteurs.

Vous pouvez également relier les titres de graphique et des axes au texte correspondant aux cellules d’une feuille de calcul, en créant une référence à ces cellules. Les titres liés sont automatiquement mis à jour dans le graphique lors de la modification du texte correspondant dans la feuille de calcul.

Pour identifier rapidement une série de données dans un graphique, vous pouvez ajouter des étiquettes de données aux points de données du graphique. Par défaut, les étiquettes de données sont liées aux valeurs de la feuille de calcul et elles s’actualisent automatiquement lorsque ces valeurs sont modifiées.

2.4.1 Ajouter un titre de graphique Procédure pour ajouter un titre de graphique: 1°) Cliquez n’importe où dans le graphique auquel vous souhaitez ajouter un titre. Cela permet d’afficher les "Outils de graphique" et les onglets "Création", "Disposition" et "Mise en forme".

2°) Sous l’onglet "Disposition", cliquez sur "Titre du graphique" dans le groupe "Étiquettes".

3°) Cliquez sur "Titre de graphique superposé centré" ou "Au-dessus du graphique".

Réf.: D-F04-66b (09_2014)

15

Dominique Lieffrig

 Excel 2010

4°) Dans la zone de texte "Titre du graphique" qui s’affiche dans le graphique, encodez le texte souhaité.

NB: pour insérer un saut de ligne, cliquez pour placer le pointeur à l’emplacement du saut de ligne, puis appuyez sur Entrée.

2.4.2 Ajouter des titres d’axes Procédure pour ajouter un titre de graphique: 1°) Cliquez n’importe où dans le graphique auquel vous souhaitez ajouter un titre. Cela permet d’afficher les "Outils de graphique" et les onglets "Création", "Disposition" et "Mise en forme".

2°) Sous l’onglet "Disposition", cliquez sur "Titre des axes" dans le groupe "Étiquettes".

3°) Effectuez au moins l’une des opérations suivantes:  Pour ajouter un titre à l’axe horizontal (axe des catégories) principal, cliquez sur "Titre de l’axe horizontal principal" et sélectionnez l’option de votre choix.  Pour ajouter un titre à l’axe vertical (axe des valeurs) principal, cliquez sur "Titre de l’axe vertical principal", puis sur l’option de votre choix.

4°) Dans la zone de texte "Titre de l’axe" qui s’affiche dans le graphique, encodez le texte souhaité.

Réf.: D-F04-66b (09_2014)

16

Dominique Lieffrig

 Excel 2010

Notes:  Si vous basculez vers un autre type de graphique qui ne prend pas en charge les titres des axes, par exemple vers un graphique en secteurs, les titres des axes ne s’afficheront plus. Les titres s’afficheront de nouveau si vous basculez de nouveau vers un type de graphique qui prend en charge les titres des axes.  Les titres des axes affichés pour des axes secondaires seront perdus si vous basculez vers un type de graphique qui ne prend pas en charge l’affichage des axes secondaires.

2.4.3 Lier un titre à une cellule de feuille de calcul Procédure pour lier un titre à une cellule de feuille de calcul: 1°) Cliquez sur le titre du graphique ou de l’axe que vous souhaitez lier à une cellule de feuille de calcul.

2°) Dans la feuille de calcul, cliquez dans la barre de formule et encodez un signe égal (=).

3°) Sélectionnez la cellule de feuille de calcul contenant les données ou le texte à afficher dans le graphique.

4°) Appuyez sur Entrée.

2.4.4 Ajouter des étiquettes de données 1°) Dans un graphique, effectuez l’une des opérations suivantes:  Pour ajouter une étiquette de données à tous les points de données de toutes les séries de données, cliquez sur la zone de graphique.  Pour ajouter une étiquette de données à tous les points de données d’une série de données, cliquez n’importe où dans la série de données que vous souhaitez étiqueter.  Pour ajouter une étiquette de données à un point précis d’une série de données dans une série de données, cliquez sur la série contenant le point de données à étiqueter, puis cliquez sur le point de données à étiqueter.

Cela permet d’afficher les "Outils de graphique" et les onglets "Création", "Disposition" et "Mise en forme".

2°) Sous l’onglet "Disposition", dans le groupe "Étiquettes", cliquez sur "Étiquettes de données", puis cliquez sur l’option de votre choix.

NB: Plusieurs options d’étiquettes de données sont disponibles, en fonction du type de graphique utilisé.

Réf.: D-F04-66b (09_2014)

17

Dominique Lieffrig

2.5

 Excel 2010

Étape 4: afficher ou masquer une légende

Lorsque vous créez un graphique, la légende apparaît, mais vous pouvez la masquer ou modifier son emplacement une fois le graphique créé.

1°) Sélectionnez le graphique dans lequel vous souhaitez afficher ou masquer une légende.

Cela permet d’afficher les "Outils de graphique" et les onglets "Création", "Disposition" et "Mise en forme".

2°) Sous l’onglet "Disposition", cliquez sur "Légende" dans le groupe "Étiquettes".

3°) Effectuez le choix voulu, l'option "Aucun" par exemple, permet de masquer la légende.

Les autres options permettent de modifier l'emplacement de la légende.

Notes  Pour supprimer rapidement une légende ou une entrée de légende d’un graphique, vous pouvez la sélectionner et appuyer sur la touche Delete. Vous pouvez également cliquer dessus avec le bouton droit et cliquer sur "Supprimer".  Lorsqu’une légende est affichée sur un graphique, vous pouvez modifier les éléments de la légende en modifiant les données correspondantes dans la feuille de calcul.  La gestion de l'affichage des axes et du quadrillage se fait d'une manière analogue à celle utilisée pour les titres, légendes, …

Réf.: D-F04-66b (09_2014)

18

Dominique Lieffrig

3

 Excel 2010

Formules, références et fonctions

Ressource: http://office.microsoft.com/fr-be/excel-help/vue-densemble-RZ101862712.aspx?section=1 3.1

Types de références de cellules

Lorsque vous copiez une formule d’une cellule dans une autre, les différents types de références de cellules produisent des résultats différents.   

Relatif Absolu Mixte

Formule modifiée lors de la copie Formule non modifiée lors de la copie Contient à la fois des références de cellules relatives et absolues

Une référence de cellule relative ressemble à ceci: F2. Une référence absolue à la même cellule ressemble à ceci: $F$2. Une référence mixte pourrait être soit $F2 (une colonne absolue avec une ligne relative), soit F$2 (une colonne relative avec une ligne absolue). Appuyez sur la touche de votre clavier pour changer une référence relative en référence absolue ou mixte (vous pouvez aussi encoder vous-même le signe dollar). Chaque fois que vous appuyez sur F4, vous pouvez parcourir diverses combinaisons de références absolues et mixtes.

3.2

Ordre des opérateurs mathématiques

Si plusieurs opérateurs mathématiques sont utilisés dans une formule (ajout et multiplication, par exemple), le calcul s’effectue en fonction des règles régissant l’ordre de priorité des opérateurs.

Par exemple, dans =3+6*2, la multiplication s’effectue avant l’addition. En premier lieu 6*2=12, puis 3+12=15.

1°) 2°) 3°) 4°)

Commencez par effectuer les opérations entre parenthèses ou entre crochets.

Poursuivez par les racines et les puissances.

Multipliez ou divisez avant d’additionner ou de soustraire.

Sinon, allez de gauche à droite.

NB: moyen mnémonique: PEMDAS: Parenthèses, Exposants (puissances et racines carrées, etc.), Multiplication, Division (de gauche à droite), Addition et Soustraction (de gauche à droite).

3.3

Rechercher des fonctions

Excel propose de nombreuses formules ou fonctions prédéfinies permettant d’effectuer une foule d’opérations, telles que le calcul de paiements mensuels ou la correction d’une liste comportant des majuscules inappropriées …

Pour rechercher une fonction, dans le ruban, cliquez sur l’onglet "Formules", puis effectuez une des procédures suivantes: 

Dans le groupe "Bibliothèque de fonctions", cliquez sur la flèche en regard de l’un des livres de la bibliothèque de fonctions pour afficher une liste des fonctions. Parcourez la liste, puis pointez sur la fonction choisie afin d’afficher une info-bulle avec une description de la fonction.

Réf.: D-F04-66b (09_2014)

19

Dominique Lieffrig



 Excel 2010

Cliquez sur le bouton "Insérer une fonction" dans le groupe "Bibliothèque de fonctions" ou cliquez à gauche de la barre de formule. Ensuite, dans la zone "Rechercher une fonction", encodez ce que vous recherchez puis appuyez sur Enter/Return.

Dans la liste "Sélectionner une fonction", cliquez sur une fonction quelconque pour afficher sa description sous la liste. Lorsque vous avez trouvé la fonction, cliquez sur OK pour ouvrir la boîte de dialogue "Arguments de la fonction", dans laquelle vous pouvez compléter les arguments de la fonction. Cliquez ensuite sur OK pour insérer la formule dans votre feuille de calcul.

Réf.: D-F04-66b (09_2014)

20

Dominique Lieffrig

3.4

 Excel 2010

Utiliser la fonction VPM

Voici tout d’abord comment trouver une fonction dont vous ne connaissez pas le nom. Dans cet exemple, la fonction VPM est utilisée pour rechercher les paiements mensuels d’un emprunt sur 30 ans à un taux d’intérêt de 4,5 % correspondant à un prêt de 200000,00 €.

1°) Dans le ruban, cliquez sur l’onglet "Formules", puis sur le bouton "Insérer une fonction" ou cliquez à gauche de la barre de formule.

2°) Dans la zone "Rechercher une fonction", encodez calculer le paiement puis appuyez sur Enter/Return.

3°) Dans la liste "Sélectionner une fonction", sélectionnez VPM. Lisez la description de la fonction sous la liste, puis cliquez sur OK. La boîte de dialogue "Arguments de la fonction" s’ouvre.

Les arguments en gras sont obligatoires. Les autres arguments sont facultatifs.

Réf.: D-F04-66b (09_2014)

21

Dominique Lieffrig

 Excel 2010

4°) Dans la zone "Taux", encodez 4,5%/12 (le taux d’intérêt est divisé en 12 paiements égaux).

Dans la zone "Npm" (nombre total de paiements), tapez 30*12, correspondant au nombre d’années de l’hypothèque multiplié par 12 paiements mensuels.

Dans la zone "Va" (valeur présente du prêt), encodez 200000.

5°) Appuyez sur la touche Entrée. Le paiement mensuel correspondant au remboursement de l’emprunt (présenté en rouge et entre parenthèses) est 1013,37 €.

NB: si vous connaissez le nom d’une fonction, vous pouvez le taper directement dans la feuille de calcul et utiliser la saisie semi-automatique de formule. Il n’est pas nécessaire de sélectionner l’onglet "Formules" pour pouvoir insérer une fonction. Vous pouvez encoder une formule quel que soit l’onglet sélectionné dans le ruban.

3.5

Utiliser la fonction NOMPROPRE

La fonction NOMPROPRE convertit le texte dans la bonne casse, en mettant en majuscules la première lettre de chaque nom et en laissant les autres caractères en minuscules. Cet exemple suppose que vous disposez d’une liste de noms dans une seule colonne.

1°) Cliquez dans une cellule vide en regard du premier nom.

2°) Encodez =NOMPROPRE( 3°) Cliquez dans la cellule contenant le premier nom pour entrer la cellule dans la formule.

4°) Tapez la parenthèse fermante ), puis appuyez sur la touche Entrée.

5°) Copiez la formule dans la colonne en descendant à l’aide du pointeur de la souris (voir les étapes ci-dessous dans la rubrique intitulée « Copier une formule d’une cellule dans une autre »).

Vous disposez à présent de deux colonnes, l’une avec une casse incorrecte et l’autre avec une casse correcte et contenant des formules dont vous n’avez plus besoin.

Réf.: D-F04-66b (09_2014)

22

Dominique Lieffrig

 Excel 2010

6°) Cliquez avec le bouton droit sur la nouvelle colonne (les valeurs étant sélectionnées), puis sélectionnez "Copier".

7°) Sélectionnez la première colonne, cliquez avec le bouton droit, puis cliquez sur "Valeurs" sous "Options de collage" (pour ne coller que le contenu, pas les formules).

8°) Enfin, supprimez la colonne avec les formules.

3.6

Copier une formule d’une cellule dans une autre

3.6.1 Utilisation de la poignée de recopie Procédure pour copier une formule via la poignée de recopie: 1°) Sélectionnez la cellule contenant la formule que vous voulez copier. Déplacez le pointeur vers le coin inférieur droit de la cellule jusqu’à ce qu’il se transforme en signe plus noir .

2°) Maintenez le bouton de la souris enfoncé, puis faites-le glisser dans la direction voulue.

3°) Relâchez le bouton de la souris.

3.6.2 Utilisation de la fonction Copier et coller Procédure pour copier-coller une formule: 1°) Sélectionnez la cellule contenant la formule à copier. Sous l’onglet "Accueil", dans le groupe "Presse-papiers", cliquez sur "Copier".

2°) Cliquez sur la cellule dans laquelle vous voulez copier la formule.

Réf.: D-F04-66b (09_2014)

23

Dominique Lieffrig

 Excel 2010

3°) Pour coller la formule, sous l’onglet "Accueil", dans le groupe "Presse-papiers", cliquez sur la flèche sous le bouton "Coller", puis cliquez sur "Formules".

3.7

Erreurs dans les formules

Voici quelques codes qui peuvent apparaître dans les cellules: # # # # # La colonne n’est pas suffisamment large pour afficher le contenu. Augmentez la largeur de colonne ou diminuez la taille du contenu pour qu’il tienne dans la colonne ou appliquez un autre format numérique. #REF! La formule contient une référence de cellule non valide. Il est possible que des cellules aient été supprimées ou remplacées. #NOM?

Vous avez peut-être mal orthographié un nom de fonction.

Les cellules affichant des erreurs, telles que #NOM?, peuvent afficher un triangle de couleur.

Si vous cliquez sur la cellule, un message d’erreur s’affiche pour vous suggérer des propositions de corrections.

Réf.: D-F04-66b (09_2014)

24

Dominique Lieffrig

4

Excel - fonctions de base et graphiques: exercices

4.1

Exercice 1 (avec procédure détaillée)

 Excel 2010

Objectif: Créer le tableau et le graphique suivants.

Procédure: 1°) Démarrez Excel puis encodez les données suivantes.

Réf.: D-F04-66b (09_2014)

25

Dominique Lieffrig

 Excel 2010

2°) Sélectionnez les cellules "Mois" et "Production…" puis appliquez la mise en gras et l'alignement voulu.

3°) Sélectionnez tout le tableau puis utilisez le bouton "Bordures" pour définir les traits intérieurs et une bordure épaisse sur le contour du tableau.

Réf.: D-F04-66b (09_2014)

26

Dominique Lieffrig

 Excel 2010

Résultat obtenu:

4°) Sélectionnez les cellules "Mois" et "Production…", définissez une couleur de fond et une bordure double en-dessous des cellules.

Résultat obtenu:

Réf.: D-F04-66b (09_2014)

27

Dominique Lieffrig

 Excel 2010

5°) Sélectionnez la cellule dans laquelle le total doit apparaître.

6°) Cliquez sur le bouton "Somme".

7°) Appuyez sur Enter/Return pour valider la formule si celle-ci est correcte.

Réf.: D-F04-66b (09_2014)

28

Dominique Lieffrig

 Excel 2010

8°) Sélectionnez les cellules B2 à C14, activez l'onglet "Insertion" puis sélectionnez le type de graphique voulu.

9°) Vous obtenez le graphique suivant. Il faut déplacer et dimensionner celui-ci correctement.

10°) Pour ajouter un titre au niveau de l'axe Y (vertical) du graphique, sélectionnez le graphique, activez l'onglet "Disposition", cliquez sur "Titres des axes/Titre de l'axe vertical principal" puis "Titre pivoté".

Réf.: D-F04-66b (09_2014)

29

Dominique Lieffrig

 Excel 2010

11°) Vous obtenez une zone dans laquelle vous pouvez encoder le commentaire voulu.

Réf.: D-F04-66b (09_2014)

30

Dominique Lieffrig

 Excel 2010

12°) Pour modifier l'aspect (couleur…) de la courbe, sélectionnez le graphique puis activez l'onglet "Création" et sélectionnez le style voulu.

13°) L'onglet "Mise en forme" permet de modifier l'aspect du titre…

Réf.: D-F04-66b (09_2014)

31

Dominique Lieffrig

 Excel 2010

14°) Afin de s'assurer que l'ensemble sera imprimé sur une seule page, activez l'onglet "Mise en page" puis sélectionnez "1 page" au niveau des zones "Largeur" et "Hauteur".

4.2

Exercice 2

1°) Créez le tableau suivant:

2°) Expérimentez quelques techniques de déplacement dans ce tableau.

3°) Insérez une colonne afin d’obtenir ce qui suit. Encodez ensuite les numéros de téléphone.

Comment faire pour modifier un numéro de téléphone ?

Réf.: D-F04-66b (09_2014)

32

Dominique Lieffrig

 Excel 2010

4°) Insérez 3 lignes au-dessus du tableau et encodez le texte voulu.

5°) Réalisez la mise en forme suivante.

6°) Définissez les options de mise en page de telle manière à ce que le tableau s’imprime comme suit.

Réf.: D-F04-66b (09_2014)

33

Dominique Lieffrig

 Excel 2010

7°) Insérez les formules voulues pour calculer le nombre total d'adultes et le nombre total d'enfants. Complétez la mise en forme.

4.3

Exercice 3

1°) Créez le tableau suivant:

2°) Insérez une colonne comme suit.

Réf.: D-F04-66b (09_2014)

34

Dominique Lieffrig

4.4

 Excel 2010

Exercice 4 (avec notes concernant les outils utilisés)

Créez le tableau et le graphique suivants.

Les outils utilisés: 

Pour fusionner des cellules, sélectionnez les cellules voulues puis utilisez le bouton suivant.



Pour forcer un retour à la ligne dans une cellule, formez la combinaison de touches Alt + Return/Enter ou utilisez le bouton "Renvoyer à la ligne automatiquement" présent dans l'onglet "Accueil".



Pour obtenir le dégradé au niveau des cellules, sélectionnez les cellules voulues puis effectuez un clic droit dans la sélection et sélectionnez "Format de cellule". Il est également possible d'utiliser le bouton "Format" présent dans l'onglet "Accueil".

Réf.: D-F04-66b (09_2014)

35

Dominique Lieffrig

 Excel 2010

Au niveau de l'onglet "Remplissage", cliquez sur le bouton "Motifs et textures".

Choisissez le dégradé voulu… puis validez en cliquant sur OK. 

Pour insérer le graphique, sélectionnez les données voulues puis utilisez les options disponibles dans l'onglet "Insertion".



Pour personnaliser le graphique, utilisez les différents onglets situés sous "Outils de graphique".

Réf.: D-F04-66b (09_2014)

36

Dominique Lieffrig

4.5

 Excel 2010

Exercice 5 (avec notes concernant les formules utilisées)

Créez le tableau et le graphique suivants.

NB: le contenu des cellules E6, E7, E8 et E9 doit être obtenu automatiquement (formules)

NB: il s'agit d'un graphique de type nuage de points.

Sélectionnez les cellules B5:B9 et E5:E9

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

37

Dominique Lieffrig

4.6

 Excel 2010

Exercice 6

Créez les tableaux et les graphiques suivants.

Réf.: D-F04-66b (09_2014)

38

Dominique Lieffrig

4.7

 Excel 2010

Exercice 7 (avec procédure détaillée)

1°) En utilisant les notions vues dans le cadre des exercices précédents, créez le tableau et le graphique suivants.

Compléments: Pour insérer les pourcentages sur le graphique:

Réf.: D-F04-66b (09_2014)

39

Dominique Lieffrig

Réf.: D-F04-66b (09_2014)

 Excel 2010

40

Dominique Lieffrig

 Excel 2010

2°) Désactivez l’affichage du quadrillage de la feuille de calcul.

3°) Effectuez un copier-coller du tableau et du graphique vers Word en suivant les indications ci-après. a) Dans Excel, sélectionnez le tableau et le graphique.

NB: il faut que tout le graphique soit bien repris dans la zone de sélection.

Réf.: D-F04-66b (09_2014)

41

Dominique Lieffrig

 Excel 2010

b) Activez la fonction "Copier".

c) Basculez vers Word et placez le curseur à l'endroit voulu puis déroulez les options relatives au bouton "Coller" et sélectionnez "Collage spécial".

Réf.: D-F04-66b (09_2014)

42

Dominique Lieffrig

 Excel 2010

d) Effectuez les choix voulus puis cliquez sur OK.

Résultat obtenu:

Réf.: D-F04-66b (09_2014)

43

Dominique Lieffrig

4.8

 Excel 2010

Exercice 8

1°) Créez le tableau et le graphique suivants.

2°) Créez un document Word et insérez y le tableau et le graphique Excel.

Réf.: D-F04-66b (09_2014)

44

Dominique Lieffrig

5

 Excel 2010

Tri et filtrage de données

Bien qu'Excel ne soit pas un logiciel de gestion de bases de données (il ne faut pas l'oublier), il est possible de trier et de filtrer des ensembles de données avec Excel.

Ressources: http://office.microsoft.com/fr-ca/excel-help/video-trier-les-donnees-VA101809992.aspx?CTT=3 http://office.microsoft.com/fr-ca/excel-help/trier-les-donnees-dune-plage-ou-dun-tableauHP010342909.aspx?CTT=3 5.1

Présentation sur base de l'exercice 9

Soit le tableau suivant.

À réaliser: 1°) Trier les données en fonction du nom de famille.

Cliquez dans une cellule de la colonne NOM puis utilisez le bouton "Trier et filtrer" présent dans l'onglet "Accueil".

Réf.: D-F04-66b (09_2014)

45

Dominique Lieffrig

 Excel 2010

2°) Trier les données en fonction de la localité.

Cliquez dans une cellule de la colonne LOCALITE puis utilisez le bouton "Trier et filtrer" présent dans l'onglet "Accueil".

3°) Afficher uniquement les personnes qui habitent à Liège.

Cliquez dans une cellule du tableau. Cliquez sur le bouton "Trier et filtrer" présent dans l'onglet "Accueil" puis activez l'option "Filtrer".

Des listes déroulantes sont alors disponibles en en-tête de chaque colonne.

Réf.: D-F04-66b (09_2014)

46

Dominique Lieffrig

 Excel 2010

Pour afficher les personnes habitant à Liège, déroulez la liste relative à la colonne "LOCALITE" puis cochez la localité voulue et validez par OK.

Ensuite, pour réafficher toutes les données, déroulez la liste relative à la colonne LOCALITE puis cliquez sur "Effacer le filtre de «LOCALITE»".

Réf.: D-F04-66b (09_2014)

47

Dominique Lieffrig

 Excel 2010

4°) En suivant une procédure analogue, afficher uniquement les personnes qui habitent à Namur, à Andenne ou à Wépion.

5°) Effacer le filtre précédent.

Réf.: D-F04-66b (09_2014)

48

Dominique Lieffrig

 Excel 2010

6°) Sélectionner les personnes dont le nom de famille commence par la lettre G.

7°) Effacer le filtre précédent.

Réf.: D-F04-66b (09_2014)

49

Dominique Lieffrig

 Excel 2010

8°) En suivant une procédure analogue, sélectionner toutes les personnes habitant une localité dont le code postal commence par 5.

9°) Effacer le filtre précédent.

10°) Sélectionner toutes les personnes de sexe féminin.

11°) Effacer le filtre précédent.

12°) Sélectionner toutes les personnes habitant une rue ou une avenue… contenant le mot "Gare".

13°) Effacer le filtre précédent.

5.2

Exercices

5.2.1 Exercice 10 Le présent exercice a pour but d'utiliser quelques fonctions d'Excel qui concernent la gestion des données (tris, filtrages).

Expérimentez les opérations énoncées sous le tableau.

Notes:  Ce tableau permet de stocker des données concernant différents livres qui constituent une bibliothèque personnelle par exemple. Certaines colonnes pourraient bien évidemment être ajoutées: collection, gestion de plusieurs auteurs pour un livre, …  Il est important de noter, que pour gérer des informations relatives à un grand nombre de livres, à la gestion d'emprunts des livres, … (dans le cadre d'une bibliothèque publique par exemple), il convient d'utiliser un autre logiciel qu'Excel. Un système de gestion de bases de données tel qu'Access par exemple, serait plus adéquat et permettrait de gérer les emprunts de livres, l'état des livres au fil du temps, …

REF VB601 EXC02 GRE01 MER01 INT01 COM01 MER02 MER03 EXC01 ACC01 MAT01 MAT02 MAT03 ROU01 BON01 TOU01 REC01 PYR01 TAR01 FLE01

CODE ISBN 2-7464-0378-1 0-7897-1729-8 9-7894-4541-7 2-212-07502-2 2-585-45124-7 2-84082-377-2 2-345-05802-2 3-545-85212-7 7-285-4251-3 0-672-31506-8 2-502-09057-1 2-8041-0281-5 2-8041-0315-3 2-253-00620-3 2-253-00286-0 2-266-03352-2 2-57145-452-7 2-87901-116-7 2-42157-825-9 2-7066-0834-5

TITRE DEP. LEG. NOM AUT. VB 6 et les bases de données 2001 Lentzner Excel 2000 1999 Ulrich Guide des réseaux locaux 1998 Mourier Comprendre Merise 1996 Matheron Internet 1995 Andrieu Les composants en VB 6 1998 Eddon Approfondir Merise 1996 Matheron Exercices et cas: Merise 1997 Matheron Excel 97 1996 Huta Access 2000: programmation 1999 Garec 5° Année de mathématique 1977 Boutriau Mathématisons 55 Manuel 1991 Goossens Mathématisons 46 Corrigé 1991 Goossens Le Rouge et le Noir 1984 Stendhal Au Bonheur des Dames 1984 Zola Le Tour du Monde en 80 Jours 1990 Vernes Les 100 meilleures recettes 1994 Teubner Connaître la cuisine des Pyr. 1994 Claustres Tartes et entremets 1996 Sanner Des fleurs toute l'année 1995 Goutier

PRENOM AUT. Rémy Laurie Gérard Jean-Patrick Olivier Guy Jean-Patrick Jean-Patrick Jos Lionel Jean Freddy Freddy

Jules Christian Francine Marie Jérôme

EDITEUR Eyrolles Campus Press Marabout Eyrolles Eyrolles Microsoft Press Eyrolles Eyrolles Micro Application Campus Press Dessain De Boeck De Boeck LGF LGF Pocket Chantecler Ed. Sud Ouest EDDL Maison Rustique

CATEGORIE Informatique Informatique Informatique Informatique Informatique Informatique Informatique Informatique Informatique Informatique Mathématique Mathématique Mathématique Roman Roman Roman Cuisine Cuisine Cuisine Horticulture

Actions à expérimenter:  Trier les données en fonction de la colonne REF.  Trier les données en fonction de la colonne TITRE.  Trier les données en fonction des colonnes CATEGORIE et TITRE. Réf.: D-F04-66b (09_2014)

50

Dominique Lieffrig

 Excel 2010

   

Sélectionner les livres appartenant à la catégorie "Roman".

Sélectionner les livres dont la date de dépôt légal est 1996.

Sélectionner le livre dont le titre est "Tartes et entremets".

Sélectionner les livres appartenant à la catégorie "Informatique" et dont la date de dépôt légal est 1996.  Sélectionner les livres écrits par Matheron.  Sélectionner les livres dont le titre contient le mot "Merise".  Sélectionner les livres appartenant à la catégorie "Informatique" ou à la catégorie "Mathématique".

5.2.2 Exercice 11 Le présent exercice a pour but d'utiliser quelques fonctions d'Excel qui concernent la gestion des données (tris, filtrages).

Expérimentez les opérations énoncées sous le tableau.

Notes:  Ce tableau permet de stocker des données concernant différents articles vendus dans un magasin. Certaines colonnes pourraient bien évidemment être ajoutées, on pourrait par exemple faire la distinction entre le prix d'achat et le prix de vente, …  Il faut noter qu'une gestion complète de stock peut nécessiter l'utilisation d'un outil plus orienté vers la gestion des bases de données. On peut citer le logiciel Access qui est un système permettant la gestion des bases de données relationnelles.

Actions à expérimenter:  Trier les articles en fonction du prix.  Trier les articles en fonction de la quantité en stock.  Trier les articles en fonction de la catégorie et de la matière.      

Sélectionner les articles appartenant à la catégorie "Perles".

Sélectionner les articles en bois.

Sélectionner les perles en bois.

Sélectionner les articles dont le libellé contient le mot "service".

Sélectionner les articles pour lesquels la quantité en stock est inférieure ou égale à 5.

Sélectionner les articles fournis par la société "Duroy sprl".

Réf.: D-F04-66b (09_2014)

51

Dominique Lieffrig

6

Exercices de renforcement

6.1

Exercice 12

Créez le tableau et le graphique suivants.

 Excel 2010

N.B.: si le montant est supérieur ou égal à 750,00 €, il est mis en italique. Il faut appliquer une mise en forme conditionnelle.

Notes 

ATTENTION: il s'agit d'un graphique de type "Nuage (de points)".



Notes concernant la mise en forme conditionnelle.

Réf.: D-F04-66b (09_2014)

52

Dominique Lieffrig

Réf.: D-F04-66b (09_2014)

 Excel 2010

53

Dominique Lieffrig

6.2

 Excel 2010

Exercice 13

Créez le tableau et les graphiques suivants.

Réf.: D-F04-66b (09_2014)

54

Dominique Lieffrig

6.3

 Excel 2010

Exercice 14

Créez les tableaux suivants et insérez les formules voulues.

-------------------------------------6.4

Exercice 15

Créez les tableaux et les graphiques suivants.

Réf.: D-F04-66b (09_2014)

55

Dominique Lieffrig

6.5

 Excel 2010

Exercice 16 (figer les volets et gérer l'impression)

Soit le tableau suivant:

Actions à expérimenter: a) En utilisant la technique qui consiste à figer les volets, figez la première ligne afin que celle-ci soit toujours visible à l'écran lorsque vous faites défiler les données.

Pour ce faire, cliquez n'importe où dans le tableau, activez l'onglet "Affichage" puis cliquez sur "Figer les volets/Figer la ligne supérieure".

Réf.: D-F04-66b (09_2014)

56

Dominique Lieffrig

 Excel 2010

b) Comment libérer le volet défini ?

Pour ce faire, cliquez n'importe où dans le tableau, activez l'onglet "Affichage" puis cliquez sur "Figer les volets/Libérer les volets".

c)

Comment obtenir un aperçu des sauts de page ?

Activez l'onglet "Affichage" puis cliquez sur "AperçuSautDePg".

Le message suivant apparaît, vous pouvez cliquer sur OK.

Ensuite pour revenir à l'affichage normal, cliquez sur le bouton "Normal" présent dans l'onglet "Affichage".

d) Comment faire pour qu'à l'impression, la première ligne se répète sur les différentes pages dans le cas où le tableau devient long ?

Pour ce faire, activez l'onglet "Mise en page" et cliquez sur le bouton "Imprimer les titres".

Réf.: D-F04-66b (09_2014)

57

Dominique Lieffrig

 Excel 2010

Dans la boîte de dialogue suivante qui apparaît, cliquez dans la zone "Lignes à répéter en haut".

Sélectionnez la ligne à répéter en haut de page puis validez par OK.

Réf.: D-F04-66b (09_2014)

58

Dominique Lieffrig

6.6

 Excel 2010

Exercice 17 (les formules, les références relatives, absolues et mixtes)

Créez les tableaux suivants:

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

59

Dominique Lieffrig

6.7

 Excel 2010

Exercice 18 (les formules, les références relatives, absolues et mixtes)

Créez le tableau suivant:

Aperçu des formules:

7

Les formats

Pour rappel, un format permet de définir l'aspect que va prendre une donnée présente dans une cellule d'une feuille de calcul. Si on prend le cas d'une date par exemple, une même date peut être affichée de différentes manières: 05/02/2013 ou 5/2/2013 ou 5 février 2013 ou mardi 5 février 2013 …

7.1

Exercice 19 (exercice solutionné)

Objectif: Partant des données brutes suivantes:

Appliquer les formats adéquats afin d'obtenir la présentation suivante:

Affichage en rouge si la valeur est négative

Réf.: D-F04-66b (09_2014)

60

Dominique Lieffrig

 Excel 2010

Procédure à suivre:  Sélectionnez les trois cellules B4 à B6 comme suit, puis utilisez le bouton "Ajouter une décimale" afin d'obtenir l'affichage du nombre de décimales souhaité.

 Sélectionnez les deux cellules B8 et B9 puis cliquez sur le lanceur de boîte de dialogue relatif au groupe de commandes "Nombre".

Sélectionnez la catégorie "Nombre" puis demandez l'affichage d'une décimale, validez par OK.

NB: il est également possible d'utiliser les boutons "Ajouter une décimale" et "Réduire les décimales".

Réf.: D-F04-66b (09_2014)

61

Dominique Lieffrig

 Excel 2010

 Sélectionnez les cellules C4 à C6.

Cliquez sur le bouton "Format Nombre Comptabilité".

 Sélectionnez les cellules C8 et C9.

Cliquez sur la flèche à droite du bouton "Format Nombre Comptabilité" puis cliquez sur "$ Anglais (Etats Unis)".

 Sélectionnez la cellule D5.

Déroulez la liste "Format de nombre" puis sélectionnez "Date longue".

NB: n'oubliez pas d'élargir la colonne si nécessaire.

Réf.: D-F04-66b (09_2014)

62

Dominique Lieffrig

 Excel 2010

 Sélectionnez la cellule D6.

Cliquez sur le lanceur de boîte de dialogue relatif au groupe de commandes "Nombre".

Sélectionnez la catégorie "Date" puis le type voulu et validez par OK.

 Sélectionnez la cellule D9.

Cliquez sur le lanceur de boîte de dialogue relatif au groupe de commandes "Nombre".

Réf.: D-F04-66b (09_2014)

63

Dominique Lieffrig

 Excel 2010

Sélectionnez la catégorie "Heure" puis le type voulu et validez par OK.

 Sélectionnez les cellules E4 à E6.

Cliquez sur le bouton "Pourcentage".

 Sélectionnez les cellules F4 à F6.

Déroulez la liste "Format de nombre" puis sélectionnez "Fraction".

Réf.: D-F04-66b (09_2014)

64

Dominique Lieffrig

 Excel 2010

Vous pouvez constater un "problème" au niveau de l'affichage de la valeur 0,1 sous forme de fraction. Sélectionnez la cellule F6.

Cliquez sur le lanceur de boîte de dialogue relatif au groupe de commandes "Nombre".

Demandez d'appliquer un format de fraction de deux chiffres puis validez par OK.

Résultat obtenu:

Réf.: D-F04-66b (09_2014)

65

Dominique Lieffrig

 Excel 2010

 Sélectionnez les cellules G4 à G6.

Déroulez la liste "Format de nombre" puis sélectionnez "Scientifique".

 Sélectionnez les cellules G7 et G8.

Déroulez la liste "Format de nombre" puis sélectionnez "Scientifique".

Réduisez le nombre de décimales via le bouton "Réduire les décimales".

Réf.: D-F04-66b (09_2014)

66

Dominique Lieffrig

 Excel 2010

 Sélectionnez la cellule H5.

Cliquez sur le lanceur de boîte de dialogue relatif au groupe de commandes "Nombre".

Sélectionnez la catégorie "Personnalisée" puis définissez un format personnalisé comme repris ci-dessous puis validez par OK

 Sélectionnez la cellule H6.

Cliquez sur le lanceur de boîte de dialogue relatif au groupe de commandes "Nombre".

Sélectionnez la catégorie "Personnalisée" puis définissez un format personnalisé comme repris ci-dessous puis validez par OK.

Réf.: D-F04-66b (09_2014)

67

Dominique Lieffrig

 Excel 2010

 Sélectionnez la cellule H9.

Cliquez sur le lanceur de boîte de dialogue relatif au groupe de commandes "Nombre".

Sélectionnez la catégorie "Nombre" puis le type voulu dans la colonne "Nombres négatifs".

NB: pour ce type de formatage, les mises en forme conditionnelles peuvent s'avérer très intéressantes.

7.2

Exercice 20 (exercice non solutionné)

Toutes les notions nécessaires ont été présentées dans le cadre de l'exercice précédent.

Partant des données brutes suivantes:

Appliquez les formats adéquats afin d'obtenir la présentation suivante:

Réf.: D-F04-66b (09_2014)

68

Dominique Lieffrig

8

Utilisation de plusieurs feuilles au sein d'un classeur

8.1

Exercice 21 (exercice solutionné)

 Excel 2010

Objectif: Créer le classeur suivant. Celui-ci comporte 3 feuilles de calcul.

Procédure: 1°) Démarrez Excel puis encodez les données suivantes.

2°) Sélectionnez les cellules de B5 à F5 puis activez la fonction "Renvoyer à la ligne automatiquement".

Réf.: D-F04-66b (09_2014)

69

Dominique Lieffrig

 Excel 2010

3°) Encodez les éléments "Date", "Prix/litre"… dans les cellules voulues.

Ensuite, sélectionnez les cellules B4 à F4, puis fusionnez les cellules, appliquez la mise en gras, la couleur de remplissage…

Résultat obtenu:

4°) Sélectionnez les cellules B5 à F5 puis appliquez la mise en forme voulue.

Réf.: D-F04-66b (09_2014)

70

Dominique Lieffrig

 Excel 2010

5°) Sélectionnez la plage de cellules du tableau puis définissez les bordures voulues.

6°) Pour renommer la feuille de calcul, effectuez un clic droit sur l'onglet correspondant à celle-ci puis sélectionnez "Renommer".

Réf.: D-F04-66b (09_2014)

71

Dominique Lieffrig

 Excel 2010

Résultat obtenu:

7°) Supprimez les feuilles "Feuil2" et "Feuil3".

Effectuez un clic droit

Réf.: D-F04-66b (09_2014)

72

Dominique Lieffrig

 Excel 2010

8°) Pour recopier la feuille "2011", effectuez un clic droit sur l'onglet de celle-ci puis sélectionnez "Déplacer ou copier…" puis suivez les indications reprises ci-dessous.

NB: il est également possible de dupliquer une feuille de calcul en maintenant la touche Ctrl enfoncée puis en effectuant un glisserposer en partant de la feuille à dupliquer.

Réf.: D-F04-66b (09_2014)

73

Dominique Lieffrig

 Excel 2010

9°) Renommez la feuille obtenue

10°) Créez une feuille pour 2013 en suivant une procédure analogue.

Réf.: D-F04-66b (09_2014)

74

Dominique Lieffrig

8.2

 Excel 2010

Exercice 22 (exercice non solutionné)

Créez les feuilles de calcul suivantes.

N.B.: il faut créer une feuille de calcul type puis dupliquer celle-ci pour les différents mois de l'année.

Dans une feuille de calcul de synthèse (une treizième feuille), il faut faire la somme des recettes et la somme des dépenses des différents mois et calculer le solde pour l'année.

Réf.: D-F04-66b (09_2014)

75

Dominique Lieffrig

 Excel 2010

EXCEL: ALLONS PLUS LOIN

Réf.: D-F04-66b (09_2014)

76

Dominique Lieffrig

1

 Excel 2010

Les fonctions

Ressources: Liste des fonctions (par ordre alphabétique) avec description de celles-ci http://office.microsoft.com/fr-be/starter-help/fonctions-excel-par-ordre-alphabetiqueHA010342655.aspx?CTT=1 Liste des fonctions (par catégorie) avec description de celles-ci http://office.microsoft.com/fr-be/excel-help/fonctions-excel-par-categorie-HP010342656.aspx?CTT=1 1.1

Fonctions mathématiques

1.1.1 Présentation Ci-après sont présentées quelques-unes des fonctions mathématiques proposées dans Excel.

NE PAS OUBLIER QUE LORSQUE VOUS UTILISEZ UNE FONCTION DANS UNE FEUILLE DE CALCUL EXCEL, IL FAUT FAIRE PRECEDER CETTE FONCTION DU SIGNE =  ABS(nombre) Renvoie la valeur absolue d'un nombre.

Exemples: ABS(3)  3 ABS(-3)  3 ABS(-7,325)  7,325  ARRONDI(nombre;chiffres) Arrondit un nombre au nombre de chiffres indiqué.

Si chiffres est supérieur à 0, nombre est arrondi au nombre de décimales indiqué.

Si chiffres est égal à 0, nombre est arrondi au nombre entier le plus proche.

Si chiffres est inférieur à 0, nombre est arrondi à gauche de la virgule.

Exemples: ARRONDI(25,729;2)  25,73 ARRONDI(124,617;-1)  120  ENT(nombre) Arrondit un nombre à l'entier immédiatement inférieur.

Exemples: ENT(15,35)  15 ENT(45,97)  45 ENT(-35,9)  -36  MOD(nombre;diviseur) Renvoie le reste de la division entière de l'argument nombre par l'argument diviseur. Le résultat est du même signe que diviseur.

Si diviseur est égal à 0, MOD renvoie la valeur d'erreur #DIV/0!

La fonction MOD peut aussi s'exprimer en utilisant la fonction ENT. Exemples:

MOD(4;3)  1 MOD(14;3)  2

 PRODUIT(nombre1;nombre2;...)

Renvoie le produit de tous les nombres donnés comme arguments.

Les arguments qui correspondent à des valeurs d'erreurs ou à du texte qui ne peut pas être converti en nombres génèrent des erreurs.

Exemples: Si les cellules B5:D5 contiennent 5, 15 et 20: PRODUIT(B5:D5)  1500 PRODUIT(B5:D5; 2)  3000 Réf.: D-F04-66b (09_2014)

77

Dominique Lieffrig

 Excel 2010

 PUISSANCE(nombre;exposant) Renvoie la valeur du nombre élevé à la puissance représentée par exposant.

L'opérateur "^" peut être utilisé à la place de la fonction PUISSANCE pour indiquer la puissance à laquelle le nombre de base doit être élevé.

Exemples: PUISSANCE(7;2)  49 PUISSANCE(98,6;3)  958585,256 PUISSANCE(9;1/2)  3  RACINE(nombre) Donne la racine carrée d'un nombre.

Si nombre est négatif, la fonction RACINE renvoie la valeur d'erreur #NOMBRE! Exemples: RACINE(25)  5 RACINE(-27)  #NOMBRE!  SIGNE(nombre) Détermine le signe d'un nombre. Renvoie 1 si l'argument nombre est positif, 0 si nombre est égal à 0 et -1 si nombre est négatif.

Exemples: SIGNE(52)  1 SIGNE(0)  0 SIGNE(-78)  -1  SOMME(nombre1;nombre2;...)

Calcule la somme de tous les nombres contenus dans la liste des arguments.

Les arguments qui sont des valeurs d'erreurs ou des chaînes de texte ne pouvant pas être converties en nombres génèrent une erreur.

Exemples: SOMME(7;8)  15 Si les cellules A2:E2 contiennent 5, 15, 30, 40 et 50 : SOMME(A2:C2)  50 SOMME(B2:E2;15)  150 1.1.2 Exercices Exercice 23 Créez le tableau suivant en insérant les fonctions voulues.

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

78

Dominique Lieffrig

 Excel 2010

Exercice 24 Créez le tableau suivant.

Aperçu des formules:

1.2

Fonctions statistiques

1.2.1 Présentation Ci-après sont présentées quelques-unes des fonctions statistiques proposées dans Excel.

Certaines ont déjà été utilisées précédemment.

NE PAS OUBLIER QUE LORSQUE VOUS UTILISEZ UNE FONCTION DANS UNE FEUILLE DE CALCUL EXCEL, IL FAUT FAIRE PRECEDER CETTE FONCTION DU SIGNE = Les fonctions statistiques effectuent des opérations sur des listes de valeurs. Les cellules vides ne sont pas prises en compte par ces fonctions.

Pour les exemples, on utilise les données suivantes:

A1 = 250 A2 = 200 A3 = 400 A4 = 300 A5 est vide

 NB(valeur1;valeur2; ...)

Détermine combien de nombres sont compris dans la liste des arguments. Utilisez NB pour obtenir le nombre d'entrées numériques d'une plage.

Les arguments qui correspondent à des nombres, à des valeurs nulles, à des valeurs logiques, à des dates ou à la représentation textuelle de nombres sont comptés. Ceux qui correspondent à des valeurs d'erreurs ou à des textes ne pouvant pas être traduits en nombres ne sont pas pris en compte.

Exemple: NB(A1:A5)  4  MAX(nombre1;nombre2;...)

Donne le plus grand nombre de la liste d'arguments.

Les arguments peuvent être des nombres, des cellules vides, des valeurs logiques ou des nombres représentés sous forme de texte. Les arguments qui sont des valeurs d'erreurs ou des textes qui ne peuvent pas être traduits en nombres génèrent des erreurs.

Si les arguments ne contiennent aucun nombre, la fonction MAX renvoie 0.

Exemples: MAX(A1:A5)  400 MAX(A1:A5;500)  500

Réf.: D-F04-66b (09_2014)

79

Dominique Lieffrig

 Excel 2010

 MIN(nombre1;nombre2; ...)

Renvoie le plus petit nombre de la liste d'arguments.

Les arguments peuvent être des nombres, des cellules vides, des valeurs logiques ou des nombres représentés sous forme de texte. Les arguments qui sont des valeurs d'erreurs ou des textes ne pouvant pas être convertis en nombres génèrent des erreurs.

Si les arguments ne contiennent aucun nombre, MIN renvoie la valeur 0.

Exemples: MIN(A1:A5)  200 MIN(A1:A5;50)  50  MOYENNE(nombre1;nombre2; ...)

Renvoie la moyenne (arithmétique) des arguments.

Les arguments doivent être des nombres.

Si la plage spécifiée ne contient aucun nombre, le résultat est #DIV/0!

Exemples: MOYENNE(A1:A5)  287,5 MOYENNE(A1:A5; 350)  300  ECARTYPEP(nombre1;nombre2;...)

Calcule l'écart-type d'une population à partir de la population entière telle que la déterminent les arguments. L'écart-type est une mesure de la dispersion des valeurs par rapport à la moyenne.

La fonction ECARTYPEP part de l'hypothèse que les arguments représentent l'ensemble de la population. Si vos données ne représentent qu'un échantillon de cette population, utilisez la fonction ECARTYPE pour en calculer l'écart type.

La fonction ECARTYPEP utilise la formule suivante: Exemple: ECARTYPEP(3;10;6;8)  2,586  MODE(nombre1;nombre2; ...)

Renvoie la valeur la plus fréquente dans une plage de données.

Si la série de données ne contient aucune répétition de nombres, MODE renvoie la valeur d'erreur #N/A. Exemples: MODE(5;6;4;4;3;2;4)  4 MODE (1;2;1;2;1;2)  1 MODE (2;1;2;1;2;1)  2 1.2.2 Exercice(s) Exercice 25 Créez la feuille de calcul suivante en insérant les fonctions voulues.

Réf.: D-F04-66b (09_2014)

80

Dominique Lieffrig

 Excel 2010

Aperçu des formules:

1.3

Fonctions financières

1.3.1 Présentation Ci-après sont présentées quelques-unes des fonctions financières proposées dans Excel.

NE PAS OUBLIER QUE LORSQUE VOUS UTILISEZ UNE FONCTION DANS UNE FEUILLE DE CALCUL EXCEL, IL FAUT FAIRE PRECEDER CETTE FONCTION DU SIGNE = Pour les calculs qui se rapportent à l’argent et aux intérêts, Excel dispose de fonctions financières.

Ces fonctions couvrent un vaste champ.  AMORLIN(coût; valeur_rés; durée) Calcule l'amortissement linéaire d'un bien pour une période donnée. coût: représente le coût initial du bien. valeur_rés: représente la valeur du bien au terme de l'amortissement (aussi appelée valeur résiduelle du bien). durée: représente le nombre de périodes pendant lesquelles le bien est amorti (aussi appelée durée de vie utile du bien).

Exemple: vous avez acheté un ordinateur pour 2500 € dont la durée de vie utile est de 4 ans et la valeur résiduelle de 400 €. L'amortissement annuel du bien est donné par : AMORLIN(2500;400;4)  525 €  SYD(coût; valeur_rés; durée; période) 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).

Exemple: vous achetez un ordinateur d'une valeur de 2500 € dont la durée de vie est de 4 ans et dont la valeur résiduelle est de 400 €.

La première annuité : SYD(2500;400;4;1)  840 € La deuxième annuité : SYD(2500;400;4;2)  630 € La troisième annuité : SYD(2500;400;4;3)  420 € La dernière annuité : SYD(2500;400;4;4)  210 €  DDB(coût; valeur_rés; durée; période; facteur) Renvoie l'amortissement d'un bien durant une période spécifiée suivant la méthode de l'amortissement dégressif à taux double ou selon un coefficient à spécifier. facteur: représente le coefficient dégressif. Si l'argument facteur est omis, sa valeur par défaut est 2 (méthode d'amortissement dégressif à taux double).

Réf.: D-F04-66b (09_2014)

81

Dominique Lieffrig

 Excel 2010

La méthode d'amortissement dégressif à taux double accélère l'amortissement. Celui-ci est donc plus élevé sur la première période, puis décroît sur les périodes suivantes. La fonction DDB utilise la formule suivante pour calculer l'amortissement sur une période: coût - valeur_rés (amortissement cumulé à la fin des périodes précédentes) * facteur / durée Modifiez l'argument facteur si vous ne souhaitez pas utiliser cette méthode.

Exemples: soit une usine faisant l'acquisition d'une nouvelle machine dont le coût est de 2400 € et dont la durée de vie est de 10 ans. Sa valeur résiduelle est de 300 €. Les exemples suivants donnent la valeur de l'amortissement sur différentes périodes. Les résultats sont arrondis à deux décimales.

DDB(2400;300;3650;1)  1,32 €, le premier jour d'amortissement.

DDB(2400;300;120;1)  40,00 €, le premier mois d'amortissement.

DDB(2400;300;10;1)  480,00 €, la première année d'amortissement.

DDB(2400;300;10;2;1,5)  306,00 €, la seconde année d'amortissement calculée avec un argument facteur égal à 1,5 au lieu de 2 comme dans la méthode d'amortissement dégressif à taux double.

DDB(2400;300;10;10)  22,12 €, la dixième année d'amortissement.  VPM(taux; npm; va; vc; type) Calcule le montant total de chaque remboursement périodique d'un investissement à remboursements et taux d'intérêt constants. taux : représente le taux d'intérêt par période. npm : représente le nombre total de périodes de paiement durant l'opération.

Veillez à utiliser la même unité pour les arguments taux et npm.

Si vous effectuez des remboursements mensuels pour un emprunt sur quatre ans à un taux d'intérêt annuel de 12 %, utilisez 12 %/12 pour taux et 4*12 pour npm. Si vous effectuez des remboursements annuels pour le même emprunt, utilisez 12 % pour taux et 4 pour npm. va: représente la valeur actuelle, c'est-à-dire la valeur, à la date d'aujourd'hui, d'une série de versements futurs. vc: représente la valeur future (valeur capitalisée), c'est-à-dire la valeur à atteindre après le dernier versement. Si l'argument vc est omis, la valeur par défaut est 0 (emprunt par exemple). type: peut prendre les valeurs 0 ou 1 et indique l'échéance des paiements. Donnez à type la valeur 1 si les paiements doivent être effectués au début de la période. 0 (ou omis) si les paiements doivent être effectués à la fin de la période.

Exemples: vous envisagez de faire un emprunt de 100 000 € sur 3 ans à un taux d'intérêt annuel de 11%. La formule suivante vous indique la valeur des versements mensuels: VPM(11%/12;3*12;-100000)  3274 Supposons que vous vouliez épargner 50 000 € en 18 ans, en déposant la même somme chaque mois sur un compte. Si votre épargne est rémunérée par des intérêts de 6 %, vous pouvez utiliser la fonction VPM pour déterminer la somme à économiser mensuellement : VPM(6%/12;18*12;0;50000)  129,08  VC(taux; npm; vpm; va; type) Renvoie la valeur future d'un investissement à remboursements périodiques et constants, et à un taux d'intérêt constant.

Exemple: supposons que vous vouliez économiser de l'argent pour financer un projet spécifique qui sera mis en œuvre dans un an. Vous déposez 1000 € sur un compte d'épargne qui vous rapporte 6 % d'intérêts par an, capitalisés mensuellement, ce qui représente un intérêt mensuel de 6%/12, soit 0,5%. Vous envisagez de déposer 100 euros au début de chaque mois pendant Réf.: D-F04-66b (09_2014)

82

Dominique Lieffrig

 Excel 2010

les 12 mois à venir. Quel sera le montant de votre épargne au bout des 12 mois ?

VC(0,5%;12;-100;-1000;1)  2 301,40 €  NPM(taux; vpm; va; vc; type) Renvoie le nombre de versements nécessaires pour rembourser un emprunt à taux d'intérêt constant, sachant que ces versements doivent être constants et périodiques.

Exemples: vous déposez régulièrement 5000 € en fin d'année sur un compte bancaire, rapportant 10% par an, composés annuellement. Vous souhaitez savoir combien de temps vous sera nécessaire pour atteindre 150000 €.

La formule suivante donne le résultat : NPM(10%;-5000;0;150000)  14,545 (nombre d'années) Si vous avez déjà 50000 € sur votre compte: NPM(10%;-5000;-50000;150000)  7,27 (nombre d'années) 1.3.2 Exercice(s) Exercice 26 Créez le tableau suivant qui utilise la fonction VPM(...) pour calculer les paiements d'une série de prêts allant de 50000 € à 400000 € par pas de 25000 €. Le taux d'intérêt est de 1,5% par mois.

On veut connaître les mensualités à payer pour des remboursements en 12, 18, 24, 30, 36 et 42 mois.

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

83

Dominique Lieffrig

1.4

 Excel 2010

Fonctions logiques

1.4.1 Présentation Ci-après sont présentées quelques-unes des fonctions logiques proposées dans Excel.

NE PAS OUBLIER QUE LORSQUE VOUS UTILISEZ UNE FONCTION DANS UNE FEUILLE DE CALCUL EXCEL, IL FAUT FAIRE PRECEDER CETTE FONCTION DU SIGNE = Les fonctions logiques produisent les valeurs VRAI ou FAUX. C'est le résultat d'une expression conditionnelle ou formule logique (évaluation d'une condition qui est vérifiée ou non).

Les opérateurs logiques et les opérateurs de comparaison permettent d'écrire des formules logiques.

Liste des opérateurs de comparaison: = : égal à < : inférieur à <= : inférieur ou égal à > : supérieur à >= : supérieur ou égal à <> : pas égal à (différent) Exemples:

A15 < 185 B26 <> 0 A110 = "payé"

Liste des opérateurs logiques:  NON(valeur_logique) Inverse la valeur logique de l'argument. valeur_logique : représente une valeur ou expression qui peut prendre la valeur VRAI ou FAUX. Si valeur_logique a la valeur FAUX, NON renvoie VRAI et si valeur_logique a la valeur VRAI, NON renvoie FAUX. Exemples: NON(FAUX)  VRAI

NON(1+1=2)  FAUX

 ET(valeur_logique1; valeur_logique2; ...)

Renvoie VRAI si tous les arguments sont VRAI; renvoie FAUX si au moins un des arguments est FAUX. valeur_logique1; valeur_logique2; ... : représentent les 1 à 30 conditions que vous souhaitez tester et qui peuvent être soit VRAI, soit FAUX. Les arguments doivent être des valeurs logiques, des matrices ou des références contenant des valeurs logiques.

Si la plage spécifiée ne contient aucune valeur logique, ET renvoie la valeur d'erreur #VALEUR! Exemples: ET(VRAI;VRAI)  VRAI ET(VRAI;FAUX)  FAUX ET(2+2=4;2+3=5)  VRAI Si B1:B3 contiennent les valeurs VRAI, FAUX et VRAI : ET(B1:B3)  FAUX Si B4 contient un nombre compris entre 1 et 100 : ET(1<B4;B4<100)  VRAI  OU(valeur_logique1; valeur_logique2; ...)

Renvoie VRAI si au moins un des arguments est VRAI et FAUX si tous les arguments sont FAUX. Exemples: OU(VRAI)  VRAI OU(1+1=1;2+2=5)  FAUX Si A1:A3 contient les valeurs VRAI, FAUX et VRAI: OU(A1:A3)  VRAI Réf.: D-F04-66b (09_2014)

84

Dominique Lieffrig

 Excel 2010

Liste des fonctions logiques:  VRAI() Renvoie la valeur logique VRAI. Vous pouvez taper la valeur VRAI directement dans les cellules et les formules sans utiliser cette fonction. La fonction VRAI permet avant tout d'assurer la compatibilité avec d'autres tableurs.  FAUX() Renvoie la valeur logique FAUX. Vous pouvez également taper directement le mot FAUX dans la feuille de calcul ou la formule.

Microsoft Excel l'interprète alors comme étant la valeur logique FAUX.  SI(test_logique; valeur_si_vrai; valeur_si_faux) Renvoie une valeur si la valeur de l'argument test_logique est VRAI et une autre valeur si cette valeur est FAUX. Utilisez la fonction SI pour exécuter un test conditionnel sur des valeurs et des formules, et faire dépendre les opérations suivantes du résultat de ce test. Ce résultat détermine la valeur renvoyée par la fonction SI. test_logique : est toute valeur ou expression dont le résultat peut être VRAI ou FAUX. valeur_si_vrai : est la valeur qui est renvoyée si le test logique est VRAI. Si l'argument test_logique est VRAI et que l'argument valeur_si_vrai est omis, la fonction renvoie la valeur VRAI. valeur_si_faux : est la valeur qui est renvoyée si le test logique est FAUX. Si l'argument test_logique est FAUX et que l'argument valeur_si_faux est omis, la fonction renvoie la valeur FAUX. Remarque: il est possible d'imbriquer jusqu'à sept fonctions SI comme arguments valeur_si_vrai et valeur_si_faux pour élaborer des tests plus complexes.

Exemples: supposons que vous souhaitiez afficher le contenu de la cellule B4 si elle contient un nombre strictement compris entre 1 et 100 et afficher un message si tel n'est pas le cas.

Si B4 contient 104  valeur hors plage.

Si B4 contient 50  50 Fonction à utiliser: SI(ET(1<B4;B4<100);B4;"valeur hors plage") Supposons qu'une feuille de calcul contienne un état des dépenses réelles et prévues. Les cellules B2:B4 contiennent les "Dépenses réelles" pour janvier, février et mars, soit: 1500, 500, 500.

Les cellules C2:C4 contiennent les "Dépenses prévues" pour les mêmes périodes: 900, 900, 925.

Vous pourriez écrire une formule qui vérifie si vous êtes en dépassement de budget pour un mois donné et produit le texte d'un message à l'aide des formules suivantes: SI(B2>C2;"Dépassement budget";"OK")  "Dépassement budget" SI(B3>C3;"Dépassement budget";"OK")  "OK" …

1.4.2 Exercice(s) Exercice 27 Créez le tableau suivant. Le commentaire voulu sera automatiquement inséré dans la colonne BILAN grâce à l'utilisation de la fonction SI.

Réf.: D-F04-66b (09_2014)

85

Dominique Lieffrig

 Excel 2010

Aperçu des formules:

1.5

Exercices complémentaires concernant les fonctions

1.5.1 Exercice 28 (fonctions de type "date/heure") Créez le tableau suivant et insérez les fonctions de type date/heure voulues.

Réf.: D-F04-66b (09_2014)

86

Dominique Lieffrig

 Excel 2010

Aperçu des formules:

1.5.2 Exercice 29 (fonctions de type "bases de données") Soit la feuille de calcul suivante. L'objectif consiste à calculer la moyenne de la taille des personnes et ce par sexe. C'est la fonction BDMOYENNE( ) qui sera utilisée.

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

87

Dominique Lieffrig

 Excel 2010

1.5.3 Exercice 30 (fonctions de type "bases de données") Créez le tableau suivant en tenant compte des notes reprises ci-dessous:

Notes:  Ces valeurs doivent être calculées automatiquement (fonction BDNBVAL( ) ).  Lorsque l'on imprime ce tableau la date d'impression doit être reprise dans le coin inférieur gauche du pied de page.

1.5.4 Exercice 31 (fonctions MAJUSCULE( ), SOMME.SI( ), NB.SI( )…)

Créez les tableaux suivants en utilisant les fonctions présentées ci-après.

Réf.: D-F04-66b (09_2014)

88

Dominique Lieffrig

 Excel 2010

Aperçu des formules:

1.5.5 Exercice 32 (la fonction SI et la fonction ET) Dans le tableau suivant, indiquez en vis-à-vis de chaque personne soit NC (non concernée) soit AC (à contacter). On suppose qu'il faut contacter les personnes de sexe féminin nées avant le 1/01/1980.

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

89

Dominique Lieffrig

 Excel 2010

1.5.6 Exercice 33 (la fonction RECHERCHEV( ) ) Utilisez la fonction RECHERCHEV ( ) afin d'attribuer automatiquement les mentions obtenues par différentes personnes ayant passé une évaluation.

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

90

Dominique Lieffrig

2

Utilisation et création de modèles

2.1

Créer un classeur Excel 2010 à partir d’un modèle

 Excel 2010

Ressource: http://office.microsoft.com/fr-be/videos/video-creer-un-classeur-excel-2010-a-partirdun-modele-VA101981941.aspx?CTT=1 Exercice 34 Objectif: créer rapidement une note de frais reprenant des dépenses de différents types: hôtel, repas, déplacements…

Procédure: 1°) Cliquez sur "Fichier" puis "Nouveau".

2°) Dans la fenêtre qui apparaît, cliquez sur "Notes de frais".

3°) Sélectionnez le modèle souhaité puis cliquez sur "Télécharger".

NB: cette zone de recherche peut s'avérer très intéressante dans certains cas.

Réf.: D-F04-66b (09_2014)

91

Dominique Lieffrig

 Excel 2010

4°) Vous obtenez la feuille de calcul suivante qu'il vous suffit de compléter.

Exercice 35 En suivant une procédure analogue à celle présentée dans l'exercice précédent (c’est-à-dire en utilisant un modèle disponible au niveau d'Office.com), créez rapidement un planning de l'entretien des bureaux, des toilettes …

2.2

Créer un modèle avec Excel 2010

Ressource: http://office.microsoft.com/fr-be/videos/video-creer-un-modele-facile-a-personnaliserdans-excel-2010-VA101982020.aspx?CTT=1 Exercice 36 Supposons que vous deviez régulièrement créer des tableaux ayant tous la même structure. Il peut être intéressant de créer un modèle plutôt que d'utiliser un copier-coller ou une autre technique moins performante.

Objectif: créer le modèle de tableau suivant.

Réf.: D-F04-66b (09_2014)

92

Dominique Lieffrig

 Excel 2010

Procédure pour créer le modèle: 1°) Créez le tableau.

2°) Enregistrez ce tableau en tant que modèle.

3°) Fermez le modèle.

Réf.: D-F04-66b (09_2014)

93

Dominique Lieffrig

 Excel 2010

Pour utiliser le modèle: 1°) Cliquez sur "Fichier" puis "Nouveau".

2°) Cliquez sur 'Mes modèles".

3°) Sélectionnez le modèle que vous souhaitez utiliser puis validez par OK. Le système crée alors un classeur basé sur le modèle.

Réf.: D-F04-66b (09_2014)

94

Dominique Lieffrig

 Excel 2010

Exercice 37 Créez le modèle de feuille de calcul suivant.

3

Gestion avancée de données

3.1

Les filtrages avancés

Les filtrages ont déjà été étudiés précédemment dans ce fascicule, l'objectif est ici d'aller plus loin et d'utiliser des filtrages plus complexes.

Exercice 38 Soit les données suivantes (Excel Exercice 38.xlsx):

Réf.: D-F04-66b (09_2014)

95

Dominique Lieffrig

 Excel 2010

Objectif n°1: Sélectionner les personnes dont le solde est supérieur à la valeur moyenne de tous les soldes (cette moyenne est égale à -1275,63 €).

Procédure: 1°) Cliquez dans le tableau puis sur le bouton "Filtrer" présent dans l'onglet "Données".

2°) Déroulez la liste relative à la colonne "SOLDE" puis sélectionnez "Filtres numériques/Au-dessus de la moyenne".

Réf.: D-F04-66b (09_2014)

96

Dominique Lieffrig

 Excel 2010

3°) Vous obtenez le résultat suivant.

4°) Réaffichez toutes les données en cliquant sur le bouton suivant.

Objectif n°2: Sélectionner les personnes qui correspondent aux trois soldes les plus élevés.

Procédure: 1°) Déroulez la liste relative à la colonne "SOLDE" puis sélectionnez "Filtres numériques/10 premiers…".

2°) Dans la boîte de dialogue suivante, demandez d'afficher les 3 premières valeurs et validez par OK.

Réf.: D-F04-66b (09_2014)

97

Dominique Lieffrig

 Excel 2010

3°) Vous obtenez le résultat suivant.

4°) Réaffichez toutes les données en cliquant sur le bouton suivant.

Objectif n°3: En utilisant les filtrages avancés, sélectionner les personnes qui habitent à Andenne OU qui appartiennent à la catégorie B1.

Procédure: 1°) Définissez les critères suivants dans la feuille de calcul "Critères" (onglet dans le bas de l'écran).

2°) Placez-vous dans la feuille de calcul "Tableau", cliquez dans le tableau puis sur le bouton "Avancé" présent dans l'onglet "Données".

3°) Cliquez dans la zone de texte en vis-à-vis de "Zone de critères".

Réf.: D-F04-66b (09_2014)

98

Dominique Lieffrig

 Excel 2010

4°) Sélectionnez la zone contenant les critères de sélection.

5°) Cliquez sur OK. 6°) Vous obtenez le résultat suivant.

Objectif n°4: En utilisant les filtrages avancés, sélectionner les personnes qui habitent à Andenne OU qui appartiennent à la catégorie B1 OU qui appartiennent à la catégorie C3.

Critères à définir:

Réf.: D-F04-66b (09_2014)

99

Dominique Lieffrig

 Excel 2010

Objectif n°5: En utilisant les filtrages avancés, sélectionner les personnes dont le nom de famille commence par la lettre G ET qui habitent à Wépion.

Critères à définir:

Objectif n°6: En utilisant les filtrages avancés, sélectionner les personnes pour lesquelles la colonne SOLDE contient une valeur comprise entre 0 et 1000 (bornes incluses).

Critères à définir:

Objectif n°7: En utilisant les filtrages avancés, sélectionner les personnes qui: - habitent à Liège et qui appartiennent à la catégorie C3 - habitent à Andenne et qui appartiennent à la catégorie C1 Critères à définir:

Objectif n°8: En utilisant les filtrages avancés, sélectionner les personnes pour lesquelles la colonne SOLDE contient une valeur négative et copier ces données à partir de la cellule A21.

Critères à définir:

Réf.: D-F04-66b (09_2014)

100

Dominique Lieffrig

3.2

 Excel 2010

Convertir des données

L'outil "Convertir" est très utile notamment lorsque l'on récupère des tableaux dans lesquels trop de données ont été placées dans une seule colonne et que l'on souhaite scinder ces données.

Cet outil peut également être utilisé afin d'améliorer l'organisation des données après importation de celles-ci.

Exercice 39 Soit le tableau suivant:

Objectif: convertir ce tableau afin d'obtenir ce qui suit.

Procédure: 1°) Insérez une colonne vide entre B et C. 2°) Sélectionnez les cellules B3 à B7.

3°) Activez l'onglet "Données" puis cliquez sur le bouton "Convertir".

4°) Suivez les différentes étapes comme repris ci-après.

Réf.: D-F04-66b (09_2014)

101

Dominique Lieffrig

 Excel 2010

5°) Cliquez sur "Terminer", vous obtenez ce qui suit.

Réf.: D-F04-66b (09_2014)

102

Dominique Lieffrig

 Excel 2010

6°) Sélectionnez les cellules D3 à D7.

7°) Activez l'onglet "Données" puis cliquez sur le bouton "Convertir".

8°) Suivez les différentes étapes comme repris ci-après.

Réf.: D-F04-66b (09_2014)

103

Dominique Lieffrig

 Excel 2010

9°) Cliquez sur "Terminer".

10°) Un pictogramme apparaît car Excel "souhaite" convertir les codes postaux en valeurs numériques. Cependant, nous souhaitons conserver le format texte pour ces données, il faut donc demander à Excel d'ignorer cette "erreur".

Réf.: D-F04-66b (09_2014)

104

Dominique Lieffrig

 Excel 2010

Exercice 40 Soit le tableau suivant:

Appliquez la conversion voulue afin d'obtenir ce qui suit.

Réf.: D-F04-66b (09_2014)

105

Dominique Lieffrig

3.3

 Excel 2010

Supprimer des doublons

Il est courant que l'on importe des données dans un tableau par exemple, et que l'on constate la présence de doublons dans ce tableau. Excel nous donne la possibilité de supprimer les doublons en ne sélectionnant que les valeurs uniques.

Exercice 41 Soit le tableau suivant:

Objectif: supprimer les doublons en se basant sur les colonnes MATRICULE, NOM et PRENOM Procédure: 1°) Sélectionnez les cellules voulues puis cliquez sur le bouton "Supprimer les doublons" présent dans l'onglet "Données".

Réf.: D-F04-66b (09_2014)

106

Dominique Lieffrig

 Excel 2010

2°) Dans le cas présent, nous souhaitons que les trois colonnes interviennent dans la recherche des doublons. Cliquez sur OK.

3°) Excel vous informe du nombre de valeurs uniques trouvées …

4°) Vous obtenez le résultat suivant.

3.4

Validation des données

Ci-après sont présentées quelques-unes des options qui permettent de guider les utilisateurs lors du remplissage d'une feuille de calcul.

Excel propose une validation des données qui consiste lorsqu'une cellule, quelle qu'elle soit, est sélectionnée à:  afficher un message à travers une info-bulle par exemple,  afficher un message d'erreur après la saisie,  définir des contrôles de saisie.

Réf.: D-F04-66b (09_2014)

107

Dominique Lieffrig

 Excel 2010

Exercice 42 Objectif: Afficher un message indiquant à l'utilisateur que dans une cellule bien précise, il faut prendre soin d'encoder une date valide.

Procédure: 1°) Sélectionnez la cellule pour laquelle vous souhaitez afficher un message.

2°) Activez l'onglet "Données" puis cliquez sur le bouton "Validation des données" (partie supérieure du bouton).

3°) Une boîte de dialogue apparaît.  Activez l'onglet "Message de saisie".  Vérifiez que la case "Quand la cellule est sélectionnée" est cochée.  Dans la zone de texte "Titre", saisissez le titre du message (celui-ci apparaîtra en caractères gras dans l'info-bulle).  Dans la zone de texte "Message de saisie", saisissez votre message.

4°) Validez en cliquant sur OK.

Le titre et le message apparaissent instantanément lorsque la cellule est sélectionnée.

Réf.: D-F04-66b (09_2014)

108

Dominique Lieffrig

 Excel 2010

Exercice 43 Objectif: Vérifier la saisie des données. Dans le cas présent, au niveau de la cellule destinée à recevoir la date de naissance, on va définir une restriction telle que seule une date sera acceptée. De plus, on veut que la date soit comprise entre le 01/01/1900 et le 31/12/1999.

Procédure pour définir des restrictions: 1°) Sélectionnez la cellule concernée.

2°) Activez l'onglet "Données" puis cliquez sur le bouton "Validation des données" (partie supérieure du bouton).

3°) Une boîte de dialogue apparaît.  Activez l'onglet "Options".  Pour restreindre la saisie à des nombres, textes, dates ou heures:  Choisissez entre Nombre entier, Décimal, Date, Heure ou Longueur du texte dans la liste déroulante "Autoriser". Dans le cas présent, choisissez "Date".  Choisissez l'opérateur dans la liste déroulante "Données".  Selon l'opérateur choisi, le contenu de la boîte de dialogue est différent.

Renseignez les valeurs de comparaison.

4°) Validez en cliquant sur OK.

Réf.: D-F04-66b (09_2014)

109

Dominique Lieffrig

 Excel 2010

Aperçu d'un cas d'utilisation où une valeur incorrecte a été encodée:

N.B.: le type de message qui apparaît ci-dessus peut être personnalisé en passant par "Données/Validation des données" et en activant l'onglet "Alerte d'erreur".

Exercice 44 Objectif:

Définir une liste déroulante au niveau d'une cellule: Procédure: 1°) Sélectionnez la cellule concernée.

2°) Activez l'onglet "Données" puis cliquez sur le bouton "Validation des données" (partie supérieure du bouton).

3°) Complétez la boîte de dialogue de la manière suivante puis validez par OK.

Réf.: D-F04-66b (09_2014)

110

Dominique Lieffrig

 Excel 2010

Exercice 45 Soit les données suivantes:

On veut, à posteriori, définir une règle de validation permettant de vérifier que les données encodées dans la colonne "NB. DE ML" sont des valeurs entières comprises entre 1 et 1000 ml.

Si tel n'est pas le cas, les valeurs doivent être entourées d'une ellipse rouge.

Procédure: 1°) Sélectionnez les cellules D4 à D9.

2°) Dans l'onglet "Données", cliquez dans la partie supérieure du bouton "Validation des données".

Réf.: D-F04-66b (09_2014)

111

Dominique Lieffrig

 Excel 2010

3°) Définissez les données voulues dans la boîte de dialogue suivante puis validez par OK.

4°) Dans l'onglet "Données", cliquez dans la partie inférieure du bouton "Validation des données" et sélectionnez l'option "Entourer les données non valides".

5°) Excel entoure les données ne répondant pas aux conditions définies.

Réf.: D-F04-66b (09_2014)

112

Dominique Lieffrig

 Excel 2010

Note: Pour effacer les cercles de validation, dans l'onglet "Données", cliquez dans la partie inférieure du bouton "Validation des données" et sélectionnez l'option "Effacer les cercles de validation".

Exercice 46 Créez le tableau suivant en utilisant certains outils de validation (cf. ci-dessous).

 L'utilisateur doit disposer d'une liste déroulante au niveau de la colonne "TITRE".  Dans la colonne "TAILLE", on ne doit pouvoir encoder que des valeurs entières comprises entre 70 et 280 cm.  Dans la colonne "DN" (date de naissance), on doit être obligé d'encoder des dates antérieures au 01/01/2000.

3.5

Consolidation des données

Pour synthétiser et afficher des résultats de feuilles de calcul distinctes, vous pouvez consolider les données de chaque feuille dans une feuille maître. Ces feuilles peuvent figurer dans le même classeur que la feuille maître ou dans des classeurs différents. Lorsque vous consolidez les données, vous les regroupez de sorte à pouvoir les mettre à jour et à les agréger régulièrement ou en fonction des besoins.

Par exemple, si vous disposez d’une feuille de calcul de frais pour chaque bureau régional, vous pouvez utiliser une consolidation pour rassembler ces chiffres dans une feuille de calcul de dépenses d’entreprise.

Réf.: D-F04-66b (09_2014)

113

Dominique Lieffrig

 Excel 2010

Exercice 47 Procédure: 1°) Définissez les données à consolider dans chaque feuille de calcul. 

  

Assurez-vous que chaque plage de données est au format liste: la première ligne de chaque colonne contient une étiquette, chaque colonne contient des faits similaires, et la liste ne contient aucune ligne ou colonne vide.

Placez chaque plage dans une feuille de calcul distincte. Ne placez aucune des plages dans la feuille de calcul sur laquelle vous envisagez placer la consolidation.

Assurez-vous que toutes les plages ont la même disposition.

Nommez chaque plage: sélectionnez la totalité de la plage, puis dans le groupe "Noms définis" de l’onglet "Formules", cliquez sur la flèche à côté de "Définir un nom", puis encodez le nom de la plage dans la zone "Nom". Vous pouvez également utiliser la zone "Nom" à gauche de la barre de formule.

Cette étape n'est pas indispensable.

Onglet reprenant les ventes au Grand-Duché de Luxembourg, cet onglet est nommé "GDL":

Onglet reprenant les ventes en Belgique, cet onglet est nommé "Belgique":

Onglet reprenant les ventes en France, cet onglet est nommé "France":

Onglet dans lequel les données consolidées apparaîtront, cet onglet est nommé "Consolidation":

Réf.: D-F04-66b (09_2014)

114

Dominique Lieffrig

 Excel 2010

2°) Cliquez sur la cellule située dans l’angle supérieur gauche de la zone où vous souhaitez faire apparaître les données consolidées dans la feuille de calcul maître.

NB: prenez soin de laisser suffisamment de cellules à droite et en dessous de cette cellule pour les données consolidées.

3°) Dans l'onglet "Données", au sein du groupe "Outils de données", cliquez sur "Consolider".

4°) Dans la zone "Fonction", cliquez sur la fonction de synthèse que Microsoft Office Excel doit utiliser pour consolider les données. Cliquez dans la zone "Référence".

5°) Activez la feuille de calcul "GDL" et sélectionnez les cellules C5 à D7.

NB: dans le cas où les plages de données concernées ont été nommées, ces dénominations peuvent être utilisées dans la zone "Référence".

Réf.: D-F04-66b (09_2014)

115

Dominique Lieffrig

 Excel 2010

6°) Cliquez sur "Ajouter".

NB: si la feuille de calcul est dans un autre classeur, cliquez sur "Parcourir…" pour localiser le fichier, puis sur OK pour fermer la boîte de dialogue "Parcourir".

7°) Activez la feuille de calcul "Belgique". Excel complète automatiquement la zone "Référence".

8°) Cliquez sur "Ajouter".

Réf.: D-F04-66b (09_2014)

116

Dominique Lieffrig

 Excel 2010

9°) Activez la feuille de calcul "France". Excel complète automatiquement la zone "Référence".

10°) Cliquez sur "Ajouter".

11°) Pour que la consolidation soit mise à jour automatiquement lorsque les données source changent, activez la case à cocher "Lier aux données source" puis validez par OK 12°) Vous obtenez un résultat semblable à ce qui suit.

Réf.: D-F04-66b (09_2014)

117

Dominique Lieffrig

 Excel 2010

Exercice 48 Effectuez une consolidation des données suivantes.

Onglet reprenant le nombre de consultations du Dr Renard:

Onglet reprenant le nombre de consultations du Dr Wynartz:

Onglet reprenant le nombre de consultations du Dr Zola:

Onglet dans lequel les données consolidées apparaîtront, cet onglet est nommé "Consolidation":

Réf.: D-F04-66b (09_2014)

118

Dominique Lieffrig

3.6

 Excel 2010

L'analyse de scénarios

Avant de pouvoir utiliser certaines fonctions qui vont suivre, il faut s'assurer qu'elles sont bien installées, car celles-ci ne le sont pas automatiquement avec EXCEL. 1°) Ouvrez le menu "Fichier" puis cliquez sur "Options".

2°) Dans la fenêtre suivante, cliquez sur "Compléments", sélectionnez "Compléments Excel" dans la liste "Gérer" puis cliquez sur "Atteindre".

3°) Dans la boîte de dialogue suivante, cochez l'option "Complément Solver" puis cliquez sur OK.

4°) Pour s'assurer que le Solveur est installé, il suffit de vérifier sa présence dans l'onglet "Données".

Réf.: D-F04-66b (09_2014)

119

Dominique Lieffrig

 Excel 2010

3.6.1 Utilisation de l’outil "Valeur cible" pour déterminer comment obtenir un résultat Si vous connaissez le résultat que vous souhaitez obtenir au moyen d’une formule, mais que vous ne savez pas exactement quelles sont les valeurs nécessaires à l’obtention de ce résultat par la formule, vous pouvez utiliser l’outil "Valeur cible".

Exercice 49 Commençons par un exemple extrêmement simple pour lequel une calculatrice pourrait suffire.

Soit la feuille de calcul suivante reprenant le nombre de jours de consultation par année, le nombre de consultations par jour et le nombre de consultations réalisées par année.

=C2*C3 L'objectif est de déterminer le nombre de consultations à réaliser par jour sachant qu'il y a 250 jours de consultation par année et qu'il faut effectuer 5150 consultations par année.

Procédure: 1°) Activez l'onglet "Données", cliquez sur "Analyse scénarios" puis "Valeur cible…".

2°) Complétez la boîte de dialogue suivante puis validez par OK.

Cellule dans laquelle la valeur à atteindre doit se trouver Valeur à atteindre Cellule dont Excel doit déterminer la valeur

3°) Excel envoie un message indiquant que la valeur cible a pu être atteinte. Cliquez sur OK.

Réf.: D-F04-66b (09_2014)

120

Dominique Lieffrig

 Excel 2010

4°) Vous obtenez le résultat suivant.

Exercice 50 Soit la feuille de calcul suivante.

=VPM(C4/12;C3;-1*C2) Par exemple, supposons que vous deviez emprunter une certaine somme d’argent. Vous savez combien vous devez emprunter, vous connaissez la durée de l’emprunt et vous savez combien vous pouvez rembourser chaque mois. Vous pouvez utiliser l’outil "Valeur cible" pour déterminer le taux d’intérêt maximum que vous pouvez accepter pour respecter les conditions de l’emprunt.

Concrètement, vous souhaitez déterminer le taux d'intérêt maximum qui peut être appliqué pour que le remboursement mensuel ne dépasse pas 125,00 € Procédure: 1°) Activez l'onglet "Données", cliquez sur "Analyse scénarios" puis "Valeur cible…".

2°) Complétez la boîte de dialogue suivante puis validez par OK.

Cellule dans laquelle la valeur à atteindre doit se trouver Valeur à atteindre Cellule dont Excel doit déterminer la valeur

3°) Excel envoie un message indiquant que la valeur cible a pu être atteinte. Cliquez sur OK.

Réf.: D-F04-66b (09_2014)

121

Dominique Lieffrig

 Excel 2010

4°) Vous obtenez le résultat suivant.

Exercice 51 Soit la feuille de calcul suivante.

=B3*B4*(1+B5) Supposons que vous deviez commander des claviers dans le cadre d'un renouvellement de ceux-ci pour 157 ordinateurs. Vous avez un budget de 3000,00 €. Quel est le prix (HTVA) maximum admissible pour un clavier ? Utilisez la technique de la valeur cible pour résoudre ce problème.

3.6.2 Utilisation de tables de données pour évaluer les effets d’une ou deux variables sur une formule Une table de données est une plage de cellules qui montre comment la modification d’une ou deux variables dans vos formules peut affecter le résultat de ces formules. Les tables de données fournissent un raccourci permettant de calculer plusieurs résultats en une seule opération ainsi qu’un mode d’affichage et de comparaison des résultats de toutes les différentes variations dans votre feuille de calcul.

Les tables de données font partie d’une série de commandes qui sont appelées outils d’analyse de simulation. Lorsque vous utilisez des tables de données, vous effectuez une analyse de simulation.

L’analyse de simulation est le processus qui consiste à modifier des valeurs dans les cellules pour voir comment ces modifications affectent le résultat des formules dans la feuille de calcul. Par exemple, vous pouvez utiliser une table de données pour faire varier le taux d’intérêt et le nombre de mensualités dans un prêt afin de déterminer les montants des paiements mensuels possibles.

Exercice 52 (table de données à une variable) Utilisez une table de données à une variable si vous voulez voir comment différentes valeurs d’une variable dans une ou plusieurs formules modifiera le résultat de ces formules. Par exemple, vous pouvez utiliser une table de données à une variable pour voir comment différents taux d’intérêt affectent un remboursement mensuel à l’aide de la fonction VPM.

Réf.: D-F04-66b (09_2014)

122

Dominique Lieffrig

 Excel 2010

Soit les données suivantes:

Procédure: 1°) Sélectionnez les cellules B7 à K8, activez l'onglet "Données, cliquez sur "Analyse scénarios" puis sur "Table de données…".

2°) Désignez la cellule d'entrée en ligne. Il s'agit de la cellule dans laquelle les données de la première ligne de la table de données seront utilisées afin de générer les résultats qui seront copiés dans la table de données.

NB: il n'y a pas de cellule d'entrée en colonne car il s'agit d'une table de données à une variable.

3°) Après avoir validé par OK, vous obtenez le résultat suivant.

 

La cellule B8 fait référence à la cellule (C5) qui contient la formule permettant de calculer le montant des remboursements mensuels.

Excel injecte dans la formule les taux d'intérêt qui sont repris dans les cellules C7 à K7 et copie les montants obtenus dans la ligne 8.

Réf.: D-F04-66b (09_2014)

123

Dominique Lieffrig

 Excel 2010

4°) Il ne reste plus qu'à définir le format voulu.

Exercice 53 (table de données à deux variables) Utilisez une table de données à deux variables pour voir comment différentes valeurs de deux variables dans une formule modifieront les résultats de cette formule. Par exemple, vous pouvez utiliser une table de données à deux variables pour voir comment différentes combinaisons du taux d’intérêt et de la durée de remboursement influent sur le montant mensuel de remboursement.

Soit les données suivantes:

Procédure: 1°) Sélectionnez les cellules B7 à K10, activez l'onglet "Données, cliquez sur "Analyse scénarios" puis sur "Table de données…".

Réf.: D-F04-66b (09_2014)

124

Dominique Lieffrig

 Excel 2010

2°) Désignez la cellule d'entrée en ligne et la cellule d'entrée en colonne. 



Cellule dans laquelle les données de la première ligne de la table de données seront utilisées afin de générer les résultats qui seront copiés dans la table de données.

Cellule dans laquelle les données de la première colonne de la table de données seront utilisées afin de générer les résultats qui seront copiés dans la table de données.

3°) Après avoir validé par OK, vous obtenez le résultat suivant.

  

La cellule B7 fait référence à la cellule (C5) qui contient la formule permettant de calculer le montant des remboursements mensuels.

Excel injecte dans la formule les taux d'intérêt qui sont repris dans les cellules C7 à K7.

Excel injecte dans la formule les nombres de mensualités qui sont reprises dans les cellules B8 à B10.

4°) Il ne reste plus qu'à définir le format voulu.

Note Une table de données n’accepte pas plus de deux variables. Si vous souhaitez analyser plus de deux variables, vous pouvez utiliser des scénarios. Bien que son utilisation soit limitée à une ou deux variables, une table de données peut utiliser autant de valeurs différentes que vous le souhaitez. Un scénario peut accepter un maximum de 32 valeurs différentes, mais vous pouvez créer autant de scénarios que vous le souhaitez.

Réf.: D-F04-66b (09_2014)

125

Dominique Lieffrig

 Excel 2010

Exercice 54 (table de données à deux variables) En utilisant l'outil "Table de données", créez la feuille de calcul suivante.

Dans les cellules C8 à I8, vous trouvez des valeurs qui correspondent à différents nombres de consultations par jour par médecin et dans les cellules B9 à B13, vous trouvez des valeurs qui correspondent à différents nombres de médecins.

Le système doit calculer automatiquement le nombre total de consultations par année en fonction des deux variables.

3.6.3 Gestionnaire de scénarios Exercice 55 Dans le cadre de l'achat d'une nouvelle imprimante, le tableau suivant reprend une estimation du coût total pour la production de feuilles sur 3 ans avec différents modèles d'imprimantes.

L'objectif est d'envisager différents scénarios en faisant varier le nombre de feuilles imprimées sur 3 ans. On souhaite comparer le coût total avec une production de 10000, 30000 et 70000 feuilles (et ce en plus du cas correspondant à la production de 105000 feuilles dans le tableau ci-dessus).

Procédure: 1°) Un tableau étant créé, activez l'onglet "Données" puis cliquez sur "Analyse scénarios" et "Gestionnaire de scénarios…".

Réf.: D-F04-66b (09_2014)

126

Dominique Lieffrig

 Excel 2010

2°) Cliquez sur "Ajouter" dans la boîte de dialogue suivante.

3°) Donnez un nom au scénario puis définissez les cellules qui recevront différentes valeurs.

Cliquez ensuite sur OK.

4°) Excel demande d'encoder les valeurs pour chacune des cellules à modifier. Dans le cas présent, on définit une valeur de 10000 feuilles imprimées pour chaque modèle d'imprimante. Cliquez ensuite sur OK.

c

Réf.: D-F04-66b (09_2014)

127

Dominique Lieffrig

 Excel 2010

5°) Vous voyez le premier scénario créé. Cliquez sur "'Ajouter…" afin de créer un deuxième scénario (impression de 30000 feuilles) puis ensuite un troisième scénario (impression de 70000 feuilles).

Réf.: D-F04-66b (09_2014)

128

Dominique Lieffrig

Réf.: D-F04-66b (09_2014)

 Excel 2010

129

Dominique Lieffrig

 Excel 2010

6°) Les trois scénarios sont ainsi créés. Vous pouvez à présent cliquer sur le bouton "Synthèse…".

7°) Excel demande si vous souhaitez un rapport de type "Synthèse de scénarios" ou "Scénario du rapport de tableau croisé dynamique". Choisissez la première option puis définissez les cellules résultantes, c'est-à-dire les cellules dont on veut voir apparaître les résultats en fonction des différents scénarios.

8°) Vous obtenez le résultat suivant.

Nous pouvons constater que les données voulues sont présentes cependant les références de cellules: $C$3, $C$4, $C$5, $E$3, $E$4, $E$5 ne sont pas très parlantes. Il serait préférable de nommer certaines cellules.

Réf.: D-F04-66b (09_2014)

130

Dominique Lieffrig

 Excel 2010

Pour nommer une cellule, il suffit de sélectionner celle-ci puis d'encoder le nom dans la zone "Nom" d'Excel comme indiqué ci-après.

"

Réf.: D-F04-66b (09_2014)

131

Dominique Lieffrig

 Excel 2010

Vous pouvez ensuite supprimer la feuille "Synthèse de scénarios" précédente puis demandez une nouvelle synthèse afin d'obtenir ce qui suit.

Réf.: D-F04-66b (09_2014)

132

Dominique Lieffrig

 Excel 2010

Exercice 56 Soit les quelques données suivantes figurant dans une feuille de calcul. Celles-ci sont relatives au calcul du nombre de consultations pouvant être réalisées par année et ce en fonction du nombre de consultations par jour par médecin, du nombre de médecins et du nombre de jours de consultation par année.

En utilisant l'outil "Gestionnaire de scénarios", générez le calcul du nombre total de consultations par année en faisant varier le nombre de consultations par jour par médecin, le nombre de médecin et le nombre de jours de consultation par année comme repris ci-après.

3.7

Grouper, dissocier des données et gérer des sous-totaux

Exercice 57 Dans le présent exercice, l'objectif est d'expérimenter les fonctions "Grouper" et "Dissocier" ainsi que la fonction "Sous-total" d'Excel.

1°) Créez le tableau suivant.

Réf.: D-F04-66b (09_2014)

133

Dominique Lieffrig

 Excel 2010

2°) Cliquez dans une cellule de la colonne TYPE, activez l'onglet "Données" puis demandez un tri par ordre croissant.

3°) Sélectionnez les lignes pour lesquelles vous souhaitez créer un premier groupe puis cliquez dans la partie supérieure du bouton "Grouper".

Vous obtenez ce qui suit.

4°) En cliquant sur le symbole suivant ensuite sur le symbole

Réf.: D-F04-66b (09_2014)

, vous pouvez masquer certaines données et en cliquant

vous réaffichez le groupe.

134

Dominique Lieffrig

 Excel 2010

5°) Pour dissocier une ou plusieurs lignes d'un groupe, sélectionnez la(les) ligne(s) concernée(s) puis cliquez dans la partie supérieure du bouton "Dissocier".

6°) Dans le cas présent, le regroupement est supprimé.

Partant de l'hypothèse que les données sont triées en fonction de la colonne TYPE, nous allons créer des groupes par type de dépenses et afficher les sous-totaux et le total général.

1°) Cliquez dans une cellule du tableau.

2°) Dans l'onglet "Données", cliquez sur "Sous-total".

3°) Effectuez les choix adéquats dans la boîte de dialogue qui suit puis validez par OK.

Réf.: D-F04-66b (09_2014)

135

Dominique Lieffrig

 Excel 2010

Aperçu du résultat obtenu:

Note Si vous souhaitez supprimer les sous-totaux et les regroupements, sélectionnez une cellule dans le tableau puis cliquez sur le bouton "Sous-total" dans l'onglet "Données".

Dans la fenêtre qui s'affiche, cliquez sur "Supprimer tout".

Réf.: D-F04-66b (09_2014)

136

Dominique Lieffrig

3.8

 Excel 2010

Les tableaux croisés dynamiques

Le Tableau Croisé Dynamique est un outil pour analyser vos ensembles de données. Il est dynamique car toute modification de la source entraîne la mise à jour du rapport, lorsque l'actualisation est déclenchée. Le Tableau Croisé Dynamique permet de grouper, combiner et comparer rapidement un grand nombre d'informations.

Exercice 58 Soit le tableau suivant qui reprend des données concernant des ventes réalisées par des vendeurs dans différentes zones géographiques.

Réf.: D-F04-66b (09_2014)

137

Dominique Lieffrig

 Excel 2010

Objectif: Créer le tableau croisé dynamique suivant.

Procédure: 1°) Cliquez dans une cellule du tableau servant de source pour le tableau croisé dynamique.

2°) Activez l'onglet "Insertion" puis sélectionnez "TblCroiséDynamique".

3°) La boîte de dialogue suivante reprend la plage de données à analyser et permet de choisir l'emplacement du tableau croisé dynamique. Confirmez par OK.

Réf.: D-F04-66b (09_2014)

138

Dominique Lieffrig

 Excel 2010

4°) La fenêtre suivante apparaît.

5°) Faites glisser le champ "Zone géographique" vers "Etiquettes de lignes" et "Montant des ventes" vers "Valeurs". Vous pouvez directement voir le résultat obtenu.

Réf.: D-F04-66b (09_2014)

139

Dominique Lieffrig

 Excel 2010

6°) Via la liste déroulante "Etiquettes de lignes", il vous est possible de sélectionner les zones géographiques pour lesquelles la somme du montant des ventes doit être calculée.

7°) Faites glisser le champ "ID Vendeur" vers "Etiquettes de colonnes". Observez le résultat obtenu au niveau du tableau croisé dynamique.

Réf.: D-F04-66b (09_2014)

140

Dominique Lieffrig

 Excel 2010

8°) Vous disposez d'une liste déroulante "Etiquettes de colonnes" permettant de sélectionner les vendeurs pour lesquels les données doivent être affichées.

9°) Faites glisser le champ "Date" vers "Filtre du rapport". Une liste déroulante permet de filtrer les données en fonction de la date des ventes réalisées.

Réf.: D-F04-66b (09_2014)

141

Dominique Lieffrig

 Excel 2010

10°) Afin d'afficher les pourcentages des chiffres des ventes, faites glisser le champ "Montant des ventes" vers "Valeurs". Ensuite, déroulez la liste relative à la deuxième zone "Montant des ventes" puis sélectionnez "Paramètres des champs de valeurs…".

Réf.: D-F04-66b (09_2014)

142

Dominique Lieffrig

 Excel 2010

11°) Appliquez les modifications reprises ci-après. Confirmez par OK.

12°) Changez l'orientation du texte dans les cellules voulues afin d'obtenir ce qui suit.

Si des modifications sont appliquées au niveau des données servant de source au tableau croisé dynamique, il faut actualiser celui-ci en cliquant sur le bouton "Actualiser" présent dans l'onglet "Options" ou en formant la combinaison de touches Alt + F5.

Réf.: D-F04-66b (09_2014)

143

Dominique Lieffrig

 Excel 2010

Exercice 59 Soit le tableau suivant qui reprend des données concernant des ventes réalisées par des vendeurs dans différentes zones géographiques.

Réf.: D-F04-66b (09_2014)

144

Dominique Lieffrig

 Excel 2010

Objectif: Créer le tableau croisé dynamique suivant. La particularité de ce tableau est de contenir deux niveaux de champs en en-têtes de colonnes: les zones géographiques et les vendeurs.

Procédure: 1°) Cliquez dans une cellule du tableau servant de source pour le tableau croisé dynamique.

2°) Activez l'onglet "Insertion" puis sélectionnez "TblCroiséDynamique".

3°) La boîte de dialogue suivante reprend la plage de données à analyser et permet de choisir l'emplacement du tableau croisé dynamique. Confirmez par OK.

Réf.: D-F04-66b (09_2014)

145

Dominique Lieffrig

 Excel 2010

4°) La fenêtre suivante apparaît. Vous pouvez faire glisser les champs voulus vers "Etiquettes de colonnes", "Etiquettes de lignes" et "Valeurs".

Réf.: D-F04-66b (09_2014)

146

Dominique Lieffrig

 Excel 2010

5°) Vous obtenez le résultat souhaité. Il est possible de réduire ou de développer le premier niveau de regroupement en en-tête de colonne.

Résultat obtenu après réduction du premier niveau:

6°) Redéveloppez le niveau afin de revenir au résultat suivant.

Exercice 60 Objectif: Partant du tableau obtenu dans l'exercice précédent, regrouper les données afin d'obtenir ce qui suit.

Réf.: D-F04-66b (09_2014)

147

Dominique Lieffrig

 Excel 2010

Procédure: 1°) Cliquez dans le tableau, activez l'onglet "Options" puis cliquez sur "Grouper les champs".

2°) Dans la fenêtre suivante, demandez un regroupement par mois. Validez par OK.

3°) Vous obtenez le résultat suivant.

Si ensuite vous souhaitez dissocier les données, sélectionnez une cellule de la colonne où le regroupement a été effectué puis cliquez sur le bouton "Dissocier" présent dans l'onglet "Options".

Réf.: D-F04-66b (09_2014)

148

Dominique Lieffrig

 Excel 2010

Exercice 61 1°) Créez le tableau croisé dynamique suivant. Certains contenus de cellules ont été modifiés.

2°) Créez les deux groupes de vendeurs suivants.

Pour ce faire, il suffit de sélectionner les cellules d'un groupe puis de cliquer sur le bouton "Grouper la sélection" présent dans l'onglet "Options".

3°) Affichez les sous-totaux sous les groupes. Pour ce faire, activez l'onglet "Création", cliquez sur le bouton "Sous-totaux" puis sélectionnez "Afficher tous les sous-totaux au bas du groupe".

Réf.: D-F04-66b (09_2014)

149

Dominique Lieffrig

 Excel 2010

Vous obtenez ce qui suit.

4°) Activez le total général uniquement pour les colonnes.

5°) Réaffichez le total général pour les lignes et pour les colonnes.

Réf.: D-F04-66b (09_2014)

150

Dominique Lieffrig

 Excel 2010

6°) Insérez une ligne vide après chaque groupe.

7°) Grâce à l'option "Lignes à bandes", facilitez la lecture des données par l'application d'une trame de fond au niveau d'une ligne sur deux.

Réf.: D-F04-66b (09_2014)

151

Dominique Lieffrig

 Excel 2010

8°) Insérez un segment relatif à la zone géographique.

NB: les segments sont des contrôles de filtrage permettant de sélectionner très rapidement les données affichées dans les tableaux croisés dynamiques et les graphiques croisés dynamiques.

9°) Utilisez le segment créé pour afficher uniquement les provinces de Liège et de Namur.

Réf.: D-F04-66b (09_2014)

152

Dominique Lieffrig

 Excel 2010

10°) Effacez le précédent filtrage défini.

11°) Déconnectez le segment du tableau croisé dynamique. Pour ce faire, sélectionnez le segment puis cliquez sur le bouton "Connexions de tableau croisé dynamique" présent dans l'onglet "Outils Segment/Options".

Réf.: D-F04-66b (09_2014)

153

Dominique Lieffrig

 Excel 2010

Décochez la case voulue puis validez par OK. NB: vous pouvez constater qu'un segment peut être connecté à différents tableaux croisés dynamiques.

Cette déconnexion a pour effet que si vous sélectionnez certaines valeurs dans le segment ceci n'aura pas d'impact sur l'affichage des données dans le tableau croisé dynamique.

12°) Reconnectez le segment au tableau croisé dynamique. Pour cela, cliquez dans le tableau croisé dynamique, sélectionnez l'onglet "Options", cliquez sur "Insérer un segment" puis sur "Connexions de segments…".

Réf.: D-F04-66b (09_2014)

154

Dominique Lieffrig

 Excel 2010

Sélectionnez le segment "Zone géographique" puis cliquez sur OK.

13°) Effacez le filtre éventuellement appliqué.

14°) Supprimez le segment en effectuant un clic droit dans le segment et en sélectionnant "Supprimer « Zone géographique »".

Réf.: D-F04-66b (09_2014)

155

Dominique Lieffrig

 Excel 2010

15°) Supposons que vous souhaitiez afficher, non pas le montant total des ventes, mais le nombre de ventes. Utilisez le bouton "Calculs" figurant dans l'onglet "'Options" comme illustré ci-après.

Vous obtenez ceci:

Réf.: D-F04-66b (09_2014)

156

Dominique Lieffrig

 Excel 2010

Exercice 62 Soit le tableau suivant:

… … …

Réf.: D-F04-66b (09_2014)

157

Dominique Lieffrig

 Excel 2010

Créez le tableau croisé dynamique suivant:

3.9

Les graphiques croisés dynamiques

Un graphique croisé dynamique fournit une représentation graphique des données contenues dans un tableau croisé dynamique. Un graphique croisé dynamique est interactif, ce qui signifie que vous pouvez le trier et le filtrer pour afficher des sous-ensembles de données du tableau croisé dynamique.

Lorsque vous créez un graphique croisé dynamique, les filtres sont affichés dans la zone de graphique afin que vous puissiez trier et filtrer les données sous-jacentes. Les modifications que vous apportez à la disposition des champs et aux données dans le tableau croisé dynamique associé sont immédiatement répercutées dans le graphique croisé dynamique.

Exercice 63 Objectif: Partant du tableau figurant ci-après, créer le graphique croisé dynamique suivant.

Réf.: D-F04-66b (09_2014)

158

Dominique Lieffrig

 Excel 2010

Procédure: 1°) Cliquez dans le tableau de données, activez l'onglet "Insertion", cliquez dans la partie inférieure du bouton "TblCroiséDynamique" puis sélectionnez l'option "Graphique croisé dynamique".

Réf.: D-F04-66b (09_2014)

159

Dominique Lieffrig

 Excel 2010

2°) La boîte de dialogue suivante reprend la plage de données à analyser et permet de choisir l'emplacement du graphique croisé dynamique. Confirmez par OK.

3°) Positionnez … le graphique obtenu.

Les zones suivantes permettent d'effectuer différents filtrages.

Réf.: D-F04-66b (09_2014)

160

Dominique Lieffrig

 Excel 2010

4°) Modifiez le type de graphique obtenu. Sélectionnez le graphique, activez l'onglet "Création" puis cliquez sur le bouton "Modifier le type de graphique".

Sélectionnez le type de graphique souhaité puis validez par OK.

Réf.: D-F04-66b (09_2014)

161

Dominique Lieffrig

 Excel 2010

Résultat obtenu:

Les onglets "Création", "Disposition" et "Mise en forme" permettent de modifier la mise en forme du graphique.

5°) Créez un segment "Zone géographique" qui permettra de filtrer les données figurant dans le graphique.

Réf.: D-F04-66b (09_2014)

162

Dominique Lieffrig

 Excel 2010

Exercice 64 Soit le tableau suivant:

… … …

Réf.: D-F04-66b (09_2014)

163

Dominique Lieffrig

 Excel 2010

Créez le graphique croisé dynamique suivant.

Réf.: D-F04-66b (09_2014)

164

Dominique Lieffrig

4

Approfondissement concernant les dates et les heures

4.1

Interprétation de l'heure par Excel

 Excel 2010

Lorsque vous encodez une heure dans une feuille de calcul, Excel n'interprète pas les informations de la même manière que vous. À vos yeux, 12:00 AM (minuit) représente l'heure du jour exprimée en heures et en minutes. En revanche, Excel représente les heures sous forme de nombre décimaux; ainsi, il stocke la valeur 0 pour représenter 12:00 AM (minuit) et la valeur 0,5 en lieu et place de 12:00 PM (midi). Dans les pages qui suivent, nous allons voir comment le concept d'heure propre à Excel permet d'appliquer des calculs arithmétiques aux heures et comment entrer des valeurs horaires dans un format reconnu par Excel. Il est important de se familiariser quelque peu avec les heures avant de les introduire dans des calculs, car cela vous permettra de gagner du temps par la suite.

4.2

Une horloge qui débute à minuit

Excel stocke les heures pour chaque période de 24 heures sous forme de nombres décimaux allant de 0 à 1 (1 non compris).

Chaque période de 24 heures débute à minuit, soit 12:00 AM, heure stockée à l'aide de la valeur 0.

La valeur 0,5 est stockée pour représenter midi, 12:00 PM, car la période comprise entre minuit et midi correspond à une demi-journée. À 12:00 AM, l'horloge revient à 0 et entame un nouveau tour.

En stockant les heures sous forme de nombres décimaux, Excel permet d'effectuer des opérations arithmétiques sur les heures. Pour calculer une durée, par exemple, Excel soustrait le nombre de départ du nombre de fin. La différence entre 6:00 PM (0,75) et 12:00 PM (0,5) est 0,25, c'est-à-dire un quart de jour (0,75 – 0,5 = 0,25).

4.3

Pour qu'Excel reconnaisse une valeur comme une heure

Vous devez utiliser le signe deux-points pour séparer les différentes parties de l'heure que vous encodez dans Excel. Si vous encodez 1:30:00, Excel reconnaît l'information comme une heure et la stocke sous la forme du nombre décimal 0,0625. Si vous ne voulez pas inclure les secondes, il vous suffit de taper 1:30.

Pour entrer une heure sur le modèle 12 heures (et non plus 24 heures), tapez l'heure, puis un espace, suivi de la lettre a ou p ; par exemple, 9:00 p. Si vous omettez le « a » ou le « p », Excel stocke par défaut la valeur correspondant à l'heure avant midi (AM) si l'heure entrée est inférieure à 12:00. Excel stocke 0,375 pour représenter 9:00 AM et 0,875 pour représenter 9:00 PM. Réf.: D-F04-66b (09_2014)

165

Dominique Lieffrig

 Excel 2010

Comme cela a déjà été vu, une heure peut être présentée de nombreuses manières différentes via les formats. Si vous choisissez un format numérique vous aurez la possibilité de voir la valeur décimale qui est stockée par Excel.

Exercice 65 Créez la feuille de calcul suivante et appliquez les formats voulus.

Réf.: D-F04-66b (09_2014)

166

Dominique Lieffrig

4.4

 Excel 2010

Utilisation d'heures dans les formules

Les calculs arithmétiques dans Excel s'effectuent au moyen de formules. Avec les heures, nous pouvons utiliser les opérateurs arithmétiques +, - ainsi que différentes fonctions telles que: SOMME( ), CONVERT( ), TEMPSVAL( ) …

Exercice 66 Créez les tableaux suivants en insérant les formules voulues et en appliquant les formats adéquats.

Aperçu des formules:

Format: [h]:mm Ce format permet d'afficher un nombre d'heures supérieur à 24.

Format: [h]:mm Ce format permet d'afficher un nombre d'heures supérieur à 24.

Réf.: D-F04-66b (09_2014)

167

Dominique Lieffrig

 Excel 2010

Aperçu des formules pour le cas n°2:

Exercice 67 Créez le tableau suivant et insérez les formules voulues.

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

168

Dominique Lieffrig

4.5

 Excel 2010

Utilisation de la fonction CONVERT( ) avec des heures

La fonction CONVERT( ) permet de convertir un nombre d'une unité vers une autre unité.

Par exemple, la fonction CONVERT( ) peut convertir un tableau de distances en milles en un tableau de distances exprimées en kilomètres, ou convertir des joules en calories ou des degrés Celsius en degrés Fahrenheit ou des heures en minutes ...

Exercice 68 Créez les tableaux suivants et effectuez les conversions.

Aperçu des formules: L'utilisation de la fonction CONVERT ( ) peut être remplacée par une multiplication par 24 et par 1440

Réf.: D-F04-66b (09_2014)

169

Dominique Lieffrig

5

 Excel 2010

Exercices de renforcement

Exercice 69 Créez le tableau suivant et insérez les formules voulues.

NB: encodez les données, insérez les formules puis effectuez la mise en forme.

Les outils utilisés:

Aperçu des formules:

Réf.: D-F04-66b (09_2014)

170

Dominique Lieffrig

 Excel 2010

Exercice 70 Créez le classeur modèle suivant. Celui-ci comporte trois feuilles de calcul:  Dépenses  Recettes  Synthèse pertes - profits Feuille "Dépenses":

Réf.: D-F04-66b (09_2014)

171

Dominique Lieffrig

 Excel 2010

Feuille "Recettes":

Réf.: D-F04-66b (09_2014)

172

Dominique Lieffrig

 Excel 2010

Feuille de synthèse:

Note: Pour enregistrer un classeur en tant que modèle, utilisez "Fichier"/"Enregistrer sous" puis sélectionnez "Modèle Excel (*.xltx):

Testez l'utilisation du modèle créé.

Réf.: D-F04-66b (09_2014)

173

Dominique Lieffrig

 Excel 2010

Exercice 71 Créez le tableau et le graphique suivants.

Réf.: D-F04-66b (09_2014)

174

Dominique Lieffrig

 Excel 2010

Exercice 72 (sélection multiple de feuilles - calculs entre plusieurs feuilles) 1°) Créez un nouveau classeur dans lequel 7 feuilles de calcul sont disponibles. Renommez ces feuilles de calcul comme indiqué ci-dessous.

2°) Sélectionnez les 7 feuilles de calcul: puis créez les éléments suivants.

Grâce à la sélection multiple préalable, ce que vous réalisez dans une feuille est effectué dans toutes les feuilles.

3°) Dissociez le groupe de travail (la sélection des sept feuilles de calcul) en cliquant sur l'onglet (en bas de la fenêtre) correspondant à une des feuilles. NB: une autre solution consiste à effectuer un clic droit sur la sélection multiple d'onglets puis à sélectionner l'option "Dissocier les feuilles".

4°) Encodez quelques valeurs dans les différentes feuilles de calcul.

5°) Sélectionnez la feuille de calcul "Totaux", et insérez les formules permettant d'effectuer les totaux voulus.

Réf.: D-F04-66b (09_2014)

175

Dominique Lieffrig

 Excel 2010

Exercice 73 (reproduire une mise en forme sur l'entièreté de feuilles de calcul ayant même structure) Procédure: 1°) Créez un nouveau classeur et introduisez les données suivantes dans les feuilles de calcul voulues.

2°) Appliquez la mise en forme voulue au niveau de la feuille de calcul "Luxembourg".

Réf.: D-F04-66b (09_2014)

176

Dominique Lieffrig

 Excel 2010

3°) Sélectionnez la feuille de calcul en cliquant sur le sélecteur de la feuille.

4°) Double-cliquez sur l'outil "Reproduire la mise en forme".

5°) Activez la feuille de calcul "Namur" puis cliquez sur le sélecteur de la feuille de calcul.

6°) Vous obtenez le résultat suivant:

7°) Effectuez la même action au niveau de la feuille de calcul "Liège".

Réf.: D-F04-66b (09_2014)

177

Dominique Lieffrig

 Excel 2010

Exercice 74 (sélection multiple de feuilles - calculs entre plusieurs classeurs) Objectif: Soit les 4 classeurs suivants:

L'objectif est de synthétiser les données dans ce classeur récapitulatif (effectuer les totaux).

Procédure: 1°) Ouvrez les différents classeurs en veillant bien à rester dans la même instance d'Excel. Pour ce faire, utilisez la fonction "Ouvrir" d'Excel et n'effectuez pas un double-clic sur chacun des fichiers à partir de l'Explorateur des fichiers.

2°) Activez l'onglet "Affichage" puis cliquez sur "Réorganiser tout".

3°) Sélectionnez "Mosaïque" puis validez par OK.

Réf.: D-F04-66b (09_2014)

178

Dominique Lieffrig

 Excel 2010

4°) Vous obtenez une présentation similaire à ce qui suit. Celle-ci peut différer suivant la grandeur de l'écran …

5°) Sélectionnez la cellule B5 au niveau du classeur "RECAPITULATIF.xlsx", encodez le signe = puis cliquez dans la cellule B5 du classeur DEPARTEMENT_A.xlsx

Réf.: D-F04-66b (09_2014)

179

Dominique Lieffrig

 Excel 2010

6°) Complétez la formule puis appuyez sur Enter/Return pour valider.

7°) Introduisez de la manière la plus performante possible les formules voulues dans les autres cellules du tableau récapitulatif.

8°) Enregistrez le résultat obtenu.

Réf.: D-F04-66b (09_2014)

180

Dominique Lieffrig

 Excel 2010

Exercice 75 (importation de données, tris et filtrages) Objectif: Soit la table suivante (TBL_MEDECIN_MED) appartenant à la base de données Access BD_A_IMPORTER.accdb:

L'objectif consiste à importer les données venant de la table Access précédemment présentée puis à effectuer diverses opérations de filtrage et de tri.

Importer les données Procédure: 1°) Pour effectuer l'importation, activez l'onglet "Données" dans Excel puis cliquez sur le bouton "Depuis Access" appartenant au groupe "Données externes".

2°) Effectuez un double-clic sur le fichier voulu.

Réf.: D-F04-66b (09_2014)

181

Dominique Lieffrig

 Excel 2010

3°) Sélectionnez la table voulue puis validez par OK.

4°) Effectuez les choix adéquats puis cliquez sur OK.

Voici le résultat obtenu:

Réf.: D-F04-66b (09_2014)

182

Dominique Lieffrig

 Excel 2010

Tris et filtrages Effectuez les opérations suivantes. a) Trier les données en fonction de la colonne "MED_NOM".

b) Trier les données en fonction de la colonne SPE_ID (1re clé) puis de la colonne MED_LOCALITE (2me clé).

Réf.: D-F04-66b (09_2014)

183

Dominique Lieffrig

 Excel 2010

c) Afficher uniquement les gynécologues.

d) Afficher toutes les données.

e) Copier les données concernant les pneumologues en-dessous du tableau.

Réf.: D-F04-66b (09_2014)

184

Dominique Lieffrig

 Excel 2010

Voici un aperçu des outils à utiliser:

Exercice 76 (scinder des données) Soit les données suivantes:

Scindez les données afin d'obtenir ce qui suit.

Réf.: D-F04-66b (09_2014)

185

Dominique Lieffrig

 Excel 2010

Exercice 77 (les fonctions de recherche) En utilisant la fonction de recherche RECHERCHEV( ), lancez la recherche du nom et du prénom de la personne dont l'identifiant sera encodé dans la cellule I4.

Exercice 78 (transposition de données) Soit les données suivantes:

Transposez les données dans une autre feuille de calcul afin d'obtenir ce qui suit.

Procédure: 1°) Sélectionnez les cellules à transposer.

2°) Utilisez la fonction "Copier".

Réf.: D-F04-66b (09_2014)

186

Dominique Lieffrig

 Excel 2010

3°) Activez la feuille de calcul dans laquelle la transposition devra être réalisée.

4°) Faites apparaître les options de la fonction "Coller" puis cliquez sur le bouton "Transposer".

5°) Modifiez la mise en forme comme vous le souhaitez.

Réf.: D-F04-66b (09_2014)

187

Dominique Lieffrig

6

La protection de cellules et de classeurs

6.1

Protéger certaines cellules d'une feuille de calcul

 Excel 2010

Exercice 79 Soit le tableau suivant:

On veut que l'utilisateur(trice) du tableau puisse encoder des données dans les cellules de C4 à C15 mais ne puisse pas modifier d'autres cellules de la feuille de calcul.

Procédure: 1°) Créez le tableau repris ci-dessus.

2°) Sélectionnez toute la feuille de calcul en cliquant sur le pavé gris dans le coin supérieur gauche, effectuez un clic droit dans la feuille de calcul puis sélectionnez "Format de cellule".

Réf.: D-F04-66b (09_2014)

188

Dominique Lieffrig

 Excel 2010

3°) Activez l'onglet "Protection" puis cochez la case "Verrouillée". Validez par OK.

4°) Sélectionnez la plage de cellules dans laquelle l'utilisateur peut encoder.

Réf.: D-F04-66b (09_2014)

189

Dominique Lieffrig

 Excel 2010

5°) Cliquez avec le bouton droit de la souris dans la plage sélectionnée puis choisissez "Format de cellule".

6°) Activez l'onglet "Protection" puis décochez la case "Verrouillée". Validez par OK. 7°) Cliquez avec le bouton droit de la souris sur l'onglet de la feuille de calcul à protéger puis sélectionnez l'option "Protéger la feuille…".

Au niveau de la boîte de dialogue qui apparaît, il est possible de définir un mot de passe.

Seules les personnes connaissant ce mot de passe pourront ôter la protection.

N.B.: il n'est pas obligatoire de définir un mot de passe.

Réf.: D-F04-66b (09_2014)

190

Dominique Lieffrig

 Excel 2010

Si ensuite, vous souhaitez ôter la protection de la feuille, effectuez un clic droit sur l'onglet de celleci puis sélectionnez l'option "Ôter la protection de la feuille…".

6.2

Protéger l'intégralité d'un classeur à l'ouverture

Exercice 80 1°) Créez un classeur et encodez quelques données dans ce dernier.

2°) Pour permettre l'ouverture de ce classeur uniquement à des personnes connaissant le mot de passe, cliquez sur "Fichier" puis "Informations".

Réf.: D-F04-66b (09_2014)

191

Dominique Lieffrig

 Excel 2010

3°) Cliquez sur le bouton "Protéger le classeur" puis sélectionnez "Chiffrer avec mot de passe".

4°) Vous devez ensuite définir le mot de passe voulu puis confirmez celui-ci.

NB: utilisez la même option pour ôter la protection du classeur.

Réf.: D-F04-66b (09_2014)

192

Dominique Lieffrig

7

 Excel 2010

Les macros et la personnalisation du ruban

Exercice 81 Objectif: Créer les macros voulues et créer l'onglet suivant.

Procédure: Si ce n'est déjà fait, rendez visible l'onglet "Développeur". Pour ce faire, cliquez avec le bouton droit de la souris dans un des onglets puis choisissez "Personnaliser le Ruban" dans le menu contextuel. Dans la fenêtre qui suit, cochez "Développeur". Validez par OK.

Création des macros: 1°) Encodez les quelques éléments suivants dans une feuille de calcul.

Réf.: D-F04-66b (09_2014)

193

Dominique Lieffrig

 Excel 2010

2°) Sélectionnez la cellule B2, activez l'onglet "Développeur" et cliquez sur "Enregistrer une macro".

3°) Attribuez un nom à la macro. NB: les espaces (et quelques autres signes) sont interdits.

Indiquez où enregistrer la macro. Validez par OK.

4°) Réalisez les actions devant être reprises dans la macro.

Dans le cas présent, il s'agit de: gras + bleu + centrage horizontal.

5°) Dans l'onglet "Développeur", cliquez sur "Arrêter l'enregistrement".

6°) Sélectionnez la cellule B4 puis en suivant une procédure analogue, créez une macro nommée "Rotation_CentragesHV_Gras" permettant de faire tourner le contenu de la cellule de 90° dans le sens anti-horlogique, de centrer le contenu horizontalement et verticalement et d'appliquer une mise en gras.

7°) Sélectionnez les cellules B6 à C9 puis en suivant une procédure analogue, créez une macro nommée "Bordures_Intérieures_Minces_Extérieures_Epaisses" permettant d'appliquer des bordures minces à l'intérieur de la sélection et des bordures épaisses à l'extérieur.

8°) Sélectionnez une cellule quelconque puis en suivant une procédure analogue, créez une macro nommée "Orientation_Paysage_Marges_Etroites_Ajustement_1_page" permettant d'orienter la feuille de calcul en mode paysage, d'appliquer des marges étroites et d'ajuster le contenu de la feuille de calcul à une page en largeur et en hauteur.

Les macros sont créées.

Réf.: D-F04-66b (09_2014)

194

Dominique Lieffrig

 Excel 2010

Création d'un nouvel onglet dans le ruban et de boutons pour appeler les macros et d'autres commandes: 1°) Cliquez avec le bouton droit de la souris sur un des onglets du ruban et sélectionnez l'option "Personnaliser le Ruban" dans le menu contextuel.

2°) Dans la fenêtre suivante, sélectionnez le dernier élément dans la liste de droite puis cliquez sur le bouton "Nouvel onglet".

3°) Sélectionnez "Nouvel onglet (Personnalisé)" puis cliquez sur le bouton "Renommer".

NB: vous pouvez également utiliser le menu contextuel.

Réf.: D-F04-66b (09_2014)

195

Dominique Lieffrig

 Excel 2010

4°) Nommez l'onglet puis validez par OK.

5°) Faites un clic droit sur "Nouveau groupe (Personnalisé)" puis sélectionnez la commande "Renommer" dans le menu contextuel.

6°) Nommez l'onglet puis validez par OK

7°) Sélectionnez "Macros" dans la liste déroulante "Choisir les commandes dans les catégories suivantes". Sélectionnez la première macro à ajouter puis cliquez sur "Ajouter".

Réf.: D-F04-66b (09_2014)

196

Dominique Lieffrig

 Excel 2010

8°) Vous obtenez ceci.

9°) Ajoutez les autres macros.

10°) Renommez les boutons.

Réf.: D-F04-66b (09_2014)

197

Dominique Lieffrig

 Excel 2010

11°) Créez un nouveau groupe au sein de l'onglet puis ajoutez les commandes "Max.", "Min.", "Moyenne" et "Somme". Validez par OK.

12°) Vous obtenez ce qui suit.

Réf.: D-F04-66b (09_2014)

198

Dominique Lieffrig

8

 Excel 2010

Raccourcis clavier dans Excel

Ressources: http://office.microsoft.com/fr-be/videos/video-utiliser-le-clavier-dans-excel-2010VA101835273.aspx?CTT=1 http://office.microsoft.com/fr-be/starter-help/raccourcis-clavier-dans-excel-2010HP010342494.aspx?CTT=1 8.1

Accès clavier au ruban

Si vous découvrez le ruban, les informations de la présente section vous permettront de comprendre le modèle des raccourcis clavier du ruban. De nouveaux raccourcis, appelés touches accélératrices, sont associés au ruban. Pour afficher les touches accélératrices, appuyez sur Alt.

Pour activer un onglet au niveau du ruban, appuyez sur la touche qui correspond à l’onglet, par exemple, appuyez sur la lettre S pour l’onglet "Insertion" ou U pour l’onglet "Formules". Cela affiche tous les badges de touches accélératrices pour les boutons de cet onglet. Appuyez ensuite sur la touche du bouton concerné.

8.2

Raccourcis clavier utilisant la touche CTRL

Réf.: D-F04-66b (09_2014)

199

Dominique Lieffrig

Réf.: D-F04-66b (09_2014)

 Excel 2010

200

Dominique Lieffrig

Réf.: D-F04-66b (09_2014)

 Excel 2010

201

Dominique Lieffrig

8.3

 Excel 2010

Touches de fonction

Réf.: D-F04-66b (09_2014)

202

Dominique Lieffrig

8.4

 Excel 2010

Autres raccourcis clavier utiles

Réf.: D-F04-66b (09_2014)

203

Dominique Lieffrig

Réf.: D-F04-66b (09_2014)

 Excel 2010

204

Dominique Lieffrig

Réf.: D-F04-66b (09_2014)

 Excel 2010

205

Dominique Lieffrig

9

 Excel 2010

Quelques exercices pour terminer

Exercice 82 Créez la feuille de calcul suivante (ne pas oublier d'insérer les formules voulues).

Exercice 83  Créez le tableau suivant et insérez les différentes formes et image(s) voulues.

N.B.: Dans cette ligne, la valeur doit automatiquement se mettre en gras si elle est supérieure à 4.

Exercice 84  Créez le tableau suivant et insérez les différentes formules voulues.

Réf.: D-F04-66b (09_2014)

206

Dominique Lieffrig

 Excel 2010

Exercice 85  Soit le fichier texte suivant:

 A partir d'Excel, ouvrez ce fichier afin de l'importer dans une feuille de calcul Excel.  Ajoutez la ligne NCLI, TITRE, NOM, … puis enregistrez le classeur dans le format Excel.

 En utilisant ce tableau Excel, réalisez une opération de publipostage à partir de Word afin de créer les étiquettes suivantes (Avery L7162).

Exercice 86 Créez le tableau suivant en insérant des formules dans les cellules où cela peut s'avérer utile.

Réf.: D-F04-66b (09_2014)

207

Dominique Lieffrig

 Excel 2010

Exercice 87 Créez le tableau et le graphique suivants.

Exercice 88 Créez un graphique avec une double échelle (température / précipitations).

Réf.: D-F04-66b (09_2014)

208

Dominique Lieffrig

 Excel 2010

Exercice 89 Soit le tableau suivant reprenant diverses données concernant des randonnées.

Réalisez les actions suivantes.

1°) Trier les randonnées en fonction du nombre de kilomètres.

2°) Trier les randonnées en fonction du niveau de difficulté.

3°) Afficher les randonnées ayant un niveau de difficulté égal à 3.

4°) Réafficher toutes les randonnées.

5°) Afficher les randonnées ayant un niveau de difficulté inférieur ou égal à 3.

6°) Réafficher toutes les randonnées.

7°) Afficher les randonnées pouvant convenir à des enfants de 6 à 12 ans.

8°) Réafficher toutes les randonnées.

9°) Afficher les randonnées dont la distance à parcourir est supérieure à 15 km et dont le niveau de difficulté est égal à 2.

10°) Réafficher toutes les randonnées.

Réf.: D-F04-66b (09_2014)

209