WIXLIB

Excel 2013/2016IntermédiaireFF-M-3337-5

Excel 2013/2016IntermédiaireLangue du manuelLangue du vision techniqueCorrection orthographiqueTraductionMise en pagePierre-Edouard BrondelPierre-Edouard BrondelCarole LeclercChantal PelletierPierre-Edouard BrondelProcédure à réaliser avec lasouris.La punaise accompagne uneinformation supplémentaire.Procédure à réaliser avec lemenu.L’ampoule accompagne uneastuce.Procédure à réaliser avec leclavier.Le panneau accompagne unemise en garde.Le moniteur indique un exerciceà compléter.AvertissementCe manuel a été développé sur un système d’exploitation Windows 7 Professionnel utilisant, dans lespropriétés d’affichage, le thème Windows 7 et une apparence Style Windows 7 pour les fenêtres etboutons. En fonction du système d’exploitation utilisé, certaines différences peuvent survenir dansl’apparence des menus, des barres d’outils et des écrans.Copyright 2015 Versalys1440, rue Ste-Catherine Ouest, 10e étageMontréal (QC) H3G 1R8Téléphone : (514) 844-2300 Télécopie : (514) 844-0706Internet : www.versalys.comTous droits réservésAucune partie de ce manuel ne peut être reproduite, enregistrée ou retransmise sous aucune forme ni par aucunprocédé que ce soit sans l’autorisation préalable de Versalys Formation.Tous les logiciels et marques cités dans ce manuel sont des marques de commerce déposées et ne sont cités qu’àtitre d’exemple.Code : FF-M-3337-54 novembre 2020

Table des matièresLES CLASSEURS. 1UTILISER PLUSIEURS FEUILLES DANS UN CLASSEUR . 3Définition d’un classeur . 3Créer un classeur . 3Travailler avec plusieurs feuilles . 8Modifier le nombre de feuilles . 10Déplacer et copier des feuilles . 15Masquer ou afficher les feuilles . 18Masquer ou afficher les classeurs . 19Afficher plusieurs fenêtres. 19PROTÉGER LES CLASSEURS .24Protéger le fichier . 24Protéger le classeur . 26Protéger la feuille. 29MAÎTRISER LA DÉNOMINATION DES CELLULES .34Généralités. 34Nommer une cellule. 35Nommer une plage de cellule ou un tableau . 35Gérer la dénomination des cellules . 37CONSOLIDER LES DONNÉES .40Créer une formule en trois dimensions . 40Effectuer une somme en profondeur. 43Consolider par position . 46Consolider par catégorie. 48Modifier une consolidation . 49Supprimer une consolidation . 50LES GRAPHIQUES .51GÉNÉRALITÉS SUR LES GRAPHIQUES .53Quelques points importants pour travailler avec des graphiques . 53Les outils disponibles dans Excel . 53Sélectionner un élément du graphique. 54Étapes de conception d’un graphique . 55Personnaliser un graphique . 57Distinguer les éléments qui composent un graphique . 58La zone de graphique . 58La zone de traçage . 58Les axes . 58La légende . 59Les quadrillages. 59Les séries . 59Les autres éléments . 60CRÉER UN GRAPHIQUE .61PERSONNALISER UN GRAPHIQUE .62VersalysPage i

Microsoft Excel 2013/2016 - IntermédiaireLes modes d’utilisation des galeries. 62Les outils du ruban Création . 62Les outils du ruban Disposition . 70Les outils de l'onglet Format . 77Différencier les types de graphiques . 88Deux ou trois dimensions . 88GRAPHIQUE TYPE SECTEUR . 90Secteur avec valeur extraite . 95Utiliser les outils d’analyse . 97Courbes de tendance . 97Barres d’erreur. 98LES GRAPHIQUES SPARKLINE . 99Présentation des graphiques sparkline . 99Pourquoi utiliser des graphiques sparkline . 100CRÉER UN GRAPHIQUE SPARKLINE . 101Ajouter du texte à un sparkline . 101Personnaliser des graphiques sparkline . 102Déterminer quels points de valeur apparaissent . 102Modifier le style des graphiques sparkline ou les formater . 103Afficher ou masquer les marqueurs de données . 103Modifier les axes d’un graphique sparkline . 104Supprimer un graphique sparkline . 105CRÉER UNE BANQUE DE GRAPHIQUES PERSONNALISÉS. 106Utiliser un modèle de graphique . 106Supprimer un modèle de graphique . 107Imprimer un graphique . 108LES BASES DE DONNÉES . 109DÉFINIR UNE BASE DE DONNÉES . 111Structuration d’une base de données . 111Le vocabulaire base de données . 111Les principes . 112Les limites du logiciel . 112CRÉER UNE BASE DE DONNÉES . 112SAISIR LES DONNÉES . 113Valider des données . 113Créer une validation . 114TRAVAILLER AVEC UNE BASE DE DONNÉES . 125Les outils de modifications . 125Les outils de calcul . 126APPLIQUER UNE MISE EN FORME CONDITIONNELLE . 126Définition et utilité . 126Types de mise en forme. 126Les opérateurs logiques . 127Appliquer une règle. 128TRIER LES DONNÉES . 141Tri simple . 141Tri multiple . 142Tri personnalisé. 144Page iiVersalys

Table des matièresFILTRER LES DONNÉES . 147Appliquer un filtre automatique. 147Appliquer un filtre simple . 151Appliquer un filtre personnalisé . 154Utiliser un filtre avancé . 158Filtrer à l’aide d’un critère simple . 159EFFECTUER DES CALCULS SUR UNE BASE DE DONNÉES . 165Sous-totaux. 165Le mode plan . 169UTILISER DES FONCTIONS DE BASE DE DONNÉES . 174Définition et utilité . 174Procédure d’utilisation . 174UTILISER UN « TABLEAU » . 180Avantages du « Tableau » . 180Créer un tableau . 180Utiliser les outils disponibles . 184Utiliser un « Tableau » . 190Gérer plusieurs tableaux sur une même feuille . 191GENERER UN RAPPORT CROISE DYNAMIQUE . 193Généralités. 193Créer un tableau croisé dynamique . 193L’environnement de travail . 195Créer un rapport croisé dynamique. 197Modifier un rapport croisé dynamique . 197INSÉRER UN SEGMENT . 212Créer un segment. 213Appliquer une valeur de filtre avec un segment . 214MODIFIER LA SOURCE DE DONNEES. 215Mettre à jour le tableau croisé dynamique . 215Effacer les données . 216Mettre en forme un tableau croisé dynamique . 216Créer un graphique croisé dynamique . 222LES FONCTIONS ET OUTILS D’ANALYSE . 225CRÉER DES FORMULES COMPLEXES . 227Généralités. 227Travailler avec l’audit de formules . 228TRAVAILLER AVEC DES FONCTIONS . 234Généralités. 234Procédure d’utilisation d’une fonction . 235La bibliothèque de fonctions disponible dans Excel . 239Les fonctions de recherche . 242Les fonctions date . 244Les fonctions statistiques . 245Les fonctions texte. 246Créer une formule qui contient des fonctions imbriquées . 246UTILISER LES OUTILS D’ANALYSE . 248Scénario . 248Valeur cible. 255VersalysPage iii

Microsoft Excel 2013/2016 - IntermédiaireTable de données . 256Affichage personnalisé . 259Fenêtre espion. 262Page ivVersalys

LES CLASSEURSUtiliser plusieurs feuilles dans unclasseurProtéger les classeursMaîtriser la dénomination descellulesConsolider les données

Les classeursUtiliser plusieurs feuilles dans un classeurDéfinition d’un classeurUn classeur est un fichier unique pouvant contenir plusieurs feuilles de types différentscomme des feuilles de calcul ou des feuilles de graphiques.Cette structure particulière vous permet un regroupement des données dans un mêmefichier, tout en vous laissant beaucoup de latitude dans le choix du format de chacunedes feuilles. En effet, vous pouvez organiser chaque feuille d'une façon différente commedans des fichiers séparés.Vous pouvez également créer des liens entre les feuilles. Les classeurs vous permettent :d’utiliser des fonctions pour consolider des données figurant dans des feuillesdifférentes, de réaliser d'autres types de calcul à l’aide de fonctions mathématiques oustatistiques se basant sur les données des autres feuilles du même classeur. Parexemple, vous pourrez connaître vos résultats et statistiques à la fin d’une annéefinancière à partir des feuilles remplies durant l’année.Créer un classeurLe classeur se génère automatiquement lors de la création d'un document. Par défaut,Excel insère une feuille dans chaque nouveau classeur. Par la suite, vous pourrezdéplacer, insérer ou supprimer des feuilles.La saisie et la mise en forme sont les mêmes, que vous utilisiez une ou plusieurs feuilles.La différence majeure est le lien que vous pouvez créer entre vos feuilles.Activer une feuilleLa feuille active est celle qui est affichée à l’écran et dont l’onglet apparaît en blanc. Pouractiver une feuille, il suffit de cliquer sur son onglet.Vous pouvez vous déplacer d'une feuille à l'autre en cliquant sur les onglets de feuillessitués au bas de la fenêtre du classeur. Si l’onglet de la feuille n’est pas visible, utilisez lesquatre boutons du navigateur situé à gauche des onglets.VersalysPage 3

Microsoft Excel 2013/2016- IntermédiaireDe la gauche vers la droite chaque bouton représente les actions suivantes :BoutonActionAfficher l’onglet de la première feuilleAfficher l’onglet de la feuille précédenteAfficher l’onglet de la feuille suivanteAfficher l’onglet de la dernière feuilleActiver rapidement une feuille1.Pointez le navigateur de feuilles.2.Cliquez sur cette zone avec le bouton droit de la souris pour faire apparaître la listedes feuilles du classeur.3.Sélectionnez la feuille à afficher et cliquez sur OK.Vous pouvez vous déplacer d’une feuille à l’autre à l’aide des touches[Ctrl] [Page Préc.] et [Crtl] [Page Suiv.]Page 4Versalys

Les classeursModifier la zone d’affichage des onglets de feuille1.Pointez le curseur de fractionnement.2.Cliquez et glissez pour agrandir ou réduire la zone d’affichage des onglets de feuille.VersalysPage 5

Microsoft Excel 2013/2016- IntermédiaireRenommer une feuilleVous pouvez donner des noms significatifs à chacune des feuilles du classeur, parexemple en fonction du type de données qu’elle contient. Il ne peut y avoir deux feuillesdu même nom.1.Pointez l’onglet de la feuille à renommer.2.Cliquez sur le bouton droit de la souris pour faire apparaître le menu contextuelcorrespondant.Cliquez sur la commande« Renommer »3.Sélectionnez la commande Renommer. L’onglet de la feuille sélectionnée passe enmode édition.4.Tapez le nouveau nom ou cliquez une autre fois sur l’onglet pour modifier le nomdéjà existant.5.Appuyez sur la touche [Entrée] ou cliquez à l’extérieur de l’onglet pour valider vosmodifications.Pour renommer une feuille, double-cliquez sur l’onglet de la feuille à renommer ettapez son nom au clavier.Page 6Versalys

Les classeursUn nom de feuille ne doit pas comprendre plus de 31 caractères, espaces compris. Ilne doit pas inclure les caractères suivants : crochets droits ([ ]), deux-points (:),barre oblique (/), barre oblique inverse (\), point d'interrogation (?) et astérisque(*).Attribuer une couleur à un ongletVous pouvez organiser vos classeurs en choisissant des couleurs pour les onglets defeuilles de calcul.1.Cliquez sur un onglet de feuille avec le bouton droit de la sourisCliquez sur la commande« Couleur d’onglet »2.Choisissez la commande Couleur d’onglet pour faire apparaître la fenêtreCouleurs.3.Cliquez sur la couleur désirée pour que l’onglet sélectionné se colore en fonction devotre choix.VersalysPage 7