Cours complet d’initiation à Microsoft Excel pour les nuls
EXCEL
PERFECTIONNEMENT
Version 1.0 – 30/11/05
SERVICE INFORMATIQUE
TABLE DES MATIERES
1RAPPELS 3
1.1RACCOURCIS CLAVIER & SOURIS ..3
1.2NAVIGUER DANS UNE FEUILLE ET UN CLASSEUR ..3
1.3PERSONNALISER LA BARRE D’OUTILS ..3
2FORMULE & FONCTION .3
2.1FORMULE AVEC OPERATEURS DE BASE ..3
2.2REFERENCE RELATIVE & ABSOLUE .4
2.3FONCTIONS COURANTES 4
2.4FONCTIONS UTILES .4
3OUTILS DE PERSONNALISATION 5
3.1FRACTIONNER 5
3.2FIGER LES VOLETS ..6
3.3ENVIRONNEMENT DE TRAVAIL . 6
3.4MISE EN FORME CONDITIONNELLE . 6
3.5VALIDATION (LISTE DEROULANTE) .7
4LIAISON ENTRE FICHIERS OU FEUILLES . 7
4.1COLLAGE SPECIAL AVEC LIAISON .7
4.2CONSOLIDATION . 8
5TRIS, FILTRES ET SOUS TOTAUX ..9
5.1TRI . 9
5.2FILTRE AUTOMATIQUE & PERSONNALISE .. 10
5.3FILTRE ELABORE 10
5.4SOUS TOTAUX ..12
6TABLEAU CROISE DYNAMIQUE ..14
6.1VOCABULAIRE & FORME DES DONNEES .. 14
6.2CREER UN TABLEAU CROISE DYNAMIQUE 14
6.3ARRANGER & MODIFIER UN TABLEAU . 16
6.3.1Barre d’outils tableau croisé dynamique .. 16
6.3.2Voir les données .16
6.3.3Modifier le tableau, ajouter/supprimer/déplacer un champ .16
6.3.4Modifier un champ (champ dynamique) .17
6.3.5Modifier la mise en forme 18
6.3.6Utilisation de la zone PAGE .18
6.3.7Actualiser les données .. 19
6.3.8Grouper les valeurs . 19
6.3.9Masquer/Afficher un champ . 20
6.3.10Changer les paramètres d’un champ 20
6.4CREATION D’UN SECOND TABLEAU CROISE DYNAMIQUE ISSU DES MEMES DONNEES 21
6.5CREATION D’UN CHAMP CALCULE 21
7REPRESENTATION GRAPHIQUE . 22
1 RAPPELS
1.1 RACCOURCIS CLAVIER & SOURIS
• Sélectionner toute la feuille : CTRL A ou clic intersection haut gauche de feuille
• Sélectionner un tableau dans une feuille : CTRL*
• Sélectionner des cellules, des lignes ou des colonnes : CTRL maintenue appuyée + clic souris sur les cellules à sélectionner
• Ajuster la taille des colonnes : sélectionner tout + double clic entre 2 colonnes ? Annuler la frappe : CTRL Z
• Copier : CTRL C
• Coller : CTRL V
• Couper : CTRL X
• Imprimer : CTRL P
1.2 NAVIGUER DANS UNE FEUILLE ET UN CLASSEUR
• Naviguer dans une tableau : CTRL ? ? ? ou ?
• Naviguer dans un classeur (passer d’une feuille à une autre) : CTRL et CTRL
1.3 PERSONNALISER LA BARRE D’OUTILS
On peut personnaliser les barres d’outils en ajoutant des icônes à celles mises par défaut à l’ouverture d’Excel, ces icônes supplémentaires étant celles d’outils les plus utiles à l’utilisateur. Cela permet d’avoir accès directement à ces outils sans passer par les différents menus.
Pour cela, choisir l’option Personnaliser du menu Outils et l’onglet Commandes
Cliquer sur une des lignes de la fenêtre Catégories puis sur la commande à ajouter à la barre des Outils, maintenir le bouton de la souris appuyé et faire glisser l’icône dans la barre des outils, relâcher le bouton de la souris lorsque l’icône est à la place souhaitée. Recommencer l’opération en explorant toutes les catégories et les commandes.
A chaque ouverture d’Excel, les barres ainsi personnalisées seront ouvertes par défaut.
2 FORMULE & FONCTION
2.1 FORMULE AVEC OPERATEURS DE BASE
Une formule commence toujours par « = » suivi d’une opération sur des nombres ou des cellules
Les opérateurs arithmétiques de base sont : +,-,*,/
Ex : « =A1+B1-C2 » ou « =G1*G6/D4 »
On trouve aussi des opérateurs booléens (logique) : NON, ET, OU, SI …
Ex : « = ET(A1 ;B1) »
2.2 REFERENCE RELATIVE & ABSOLUE
• Référence relative : c’est le mode de référence standard d’Excel. Elle est relative à la position de la cellule. Lorsqu’on effectue la recopie d'une cellule, contenant une formule se référant à une cellule relative, la référence à cette cellule s'incrémente, par exemple : la formule "=A1+B1" devient "=A2+B2" si on la recopie vers le bas ou "=C1+D1" si on la recopie dans une cellule 2 colonnes à droite.
• Référence absolue : pour obtenir une référence absolue, il suffit dans Excel d'ajouter le signe $ dans la référence devant la ligne et/ou la colonne. Alors, en recopiant la cellule, celle-ci ne sera pas incrémentée et la formule se référera toujours à la même adresse. Ex : MaFeuille !$A$1 ou B$1
2.3 FONCTIONS COURANTES
Il existe dans Excel des listes de fonctions prédéfinies qu’on obtient par le menu Insertion/Fonction ou l’icône qui permettent des calculs plus élaborés comme des sommes, moyennes , arrondis….
Cliquer sur Tous dans la fenêtre de gauche pour avoir la liste de toutes les fonctions accessibles. Une fois qu’une fonction est sélectionnée dans la fenêtre de droite, l’aide sur la fonction est affichée en dessous avec la liste des arguments et le résultat de cette fonction.
2.4 FONCTIONS UTILES
• STXT(Texte ; n°départ ; nbcar) = extrait de « Texte » nbcar caractères à partir du n°départ Ex : STXT(Fonctions utiles ;1 ;5) = Fonct (les 5 premières lettres de « Fonctions utiles »)
• NBCAR(Texte) = nombre de caractères (espace compris) de Texte Ex : NBCAR(Fonctions utiles) = 16
• DROITE(Texte ; nbcar) = extrait les nbcar caractères de Texte à partir de la droite
• GAUCHE(Texte ; nbcar) = extrait les nbcar caractères de Texte à partir de la gauche
• CONCATENE(Texte1 ;Texte2 ;….) = Texte1Texte2… on peut aussi utiliser le symbole « & » à la place de la fonction CONCATENE : Texte1&Texte2&…
• ARRONDI .SUP(nombre, nb chiffres) = arrondi à la valeur supérieure un nombre à nb chiffres après la virgule
• RECHERCHEV ( pour recherche verticale): Il arrive très souvent que l'on doive récupérer des données d'un fichier Excel dans une autre feuille de calcul. C'est le cas, par exemple, lorsqu’on dispose d'un fichier centralisé de tarifs de produits et qu’on souhaite les réutiliser dans des devis ou des factures. La fonction RECHERCHEV permet de ne pas ressaisir les données. La feuille contenant les données doit comporter une colonne servant de référence simple par produit (dans l’exemple ci-après la 1ère)
Dans la feuille de saisie des factures ou devis, il suffit de taper la quantité et la référence du produit, le libellé et le prix unitaire s’inscrivent automatiquement car la fonction rechercheV s’appuie sur le numéro de référence et va dans la plage des données chercher la colonne 3 pour le libellé et la colonne 4 pour le prix unitaire.
Les 4 arguments de la fonction RechercheV sont dans l’ordre la Valeur cherchée ; la plage de données ; le N° Colonne et 0 pour aller chercher la valeur exacte.
Une fonction RechercheH ( pour recherche horizontale) existe et a le même effet que RechercheV mais elle utilise en 3ème argument le numéro de ligne de la plage de données.
3 OUTILS DE PERSONNALISATION
3.1 FRACTIONNER
Cette option permet de travailler sur des grands tableaux en gardant certaines parties importantes toujours visibles. Positionner le curseur sur une cellule du tableau, aller dans l’option Fractionner du menu Fenêtre. La fenêtre est alors séparée en 2 ou 4 parties à partir de la cellule sélectionnée (le fractionnement se fait à gauche et au dessus de celle –ci) par des doubles barres.
Pour enlever cette fonction, cliquer dans Supprimer le fractionnement dans le menu Fenêtre ou doublecliquer sur une des barres.
3.2 FIGER LES VOLETS
Cette option permet de travailler sur des grands tableaux en gardant, par exemple, les titres des colonnes ou des lignes toujours visibles.
Pour cela, on utilise la commande Figer les volets dans le menu Fenêtre.
Positionner le curseur dans la première cellule en haut à droite de votre tableau, hors titre.
Cliquer sur Figer les volets, deux lignes noires apparaissent, qui délimitent la partie qui restera à l'écran du reste du tableau. (Ces lignes n'apparaissent pas à l'impression)
Pour enlever cette fonction, cliquer dans le menu Fenêtre, sur Libérer les volets On peut aussi figer les volets après avoir fait un fractionnement.
3.3 ENVIRONNEMENT DE TRAVAIL
Lorsqu’on travaille régulièrement avec plusieurs feuilles de travail ouvertes simultanément et qu’on redimensionne à chaque fois, il peut être souhaitable de créer un environnement de travail qui mémorise l'ensemble des documents ouverts et leur disposition.
| Fichier 1 | Fichier 2 |
Une fois, la disposition des fichiers correcte (dans l’exemple suivant, 2 fichiers côte à côte) , aller dans l’option Menu Fichier/Enregistrer un environnement de travail, enregistrer sous le nom dans un répertoire de travail quelconque.
Par la suite, il suffit d’ouvrir le fichier pour travailler sur les 2 fichiers simultanément, ce qui n’empêche pas de travailler indépendamment sur l’un ou l’autre des fichiers.
3.4 MISE EN FORME CONDITIONNELLE
On peut faire automatiquement ressortir le contenu d’une ou plusieurs cellules de façon à accentuer la lisibilité des données.
On commence par sélectionner la plage de cellules sur laquelle on veut avoir cette mise en forme
On utilise dans le menu Format l’option Mise en Forme Conditionnelle
Dans l’exemple ci-dessus, lorsque la valeur d’une cellule sera comprise entre 0 et 10, la cellule sera colorée en mauve.
Il suffit de cliquer sur Ajouter pour spécifier une ou plusieurs conditions supplémentaires.
Astuce : Quand on doit manipuler un tableau avec beaucoup de lignes, il est plus confortable visuellement d’avoir une ligne sur 2 colorée. Pour cela, il faut sélectionner tout le tableau ou toute la feuille, utiliser la mise en forme conditionnelle et une formule mathématique…..les lignes paires seront alors automatiquement remplies en vert.
3.5 VALIDATION (LISTE DEROULANTE)
La validation est un outil utile pour faciliter la saisie de données dans un classeur, qui permet, entre autre, de créer des listes déroulantes ou de borner les possibilités de saisie en dehors d’une liste ou d’un critère.
Pour créer une liste déroulante, saisir la liste dans une partie de la feuille hors zone d’impression. Placer le curseur de la souris dans la cellule que l’utilisateur doit remplir par un des éléments de la liste, choisir dans le menu Données, l’option Validation
Choisir dans Autoriser « Liste » et sélectionner la liste de données dans Source en cliquant sur l’icône à droite. Cliquer sur OK.
Une petite flèche apparaît à droite de la cellule concernée pour signaler qu’il y a une liste, en cliquant sur la flèche, la liste apparaît, il suffit alors de choisir l’élément correct.
Toute saisie dans cette cellule hors de la liste de données est rejetée.
4 LIAISON ENTRE FICHIERS OU FEUILLES
4.1 COLLAGE SPECIAL AVEC LIAISON
Cette option fait partie du menu Edition et a plusieurs fonctionalités
Option Description
Tout Fonctionne exactement comme l'option Coller.
Formules Colle seulement la formule sans les options de présentation.
Valeur Colle seulement le chiffre qui est le résultat d'une formule.
Formats Colle juste les options de présentations telles que la couleur, la taille et le type de police de caractère, la couleur du fond de cellule et la bordure de la cellule. Commentaires Colle seulement le commentaire copié d'une autre cellule.
L’option la plus intéressante du collage spécial est Coller avec liaison qui permet de faire des liens vers d'autres cellules.
• Sélectionner une plage de cellules dans une feuille.
• Du menu Edition, sélectionner l'option Copier ou utiliser le raccourci CTRL C
• Sélectionner une seconde feuille de travail en cliquant sur l'onglet au bas de l'écran ? Placer le pointeur dans la cellule qui sera la cellule en haut à gauche de la plage recopiée.
• Du menu Edition, sélectionner l'option Collage spécial. ? Appuyez sur le bouton Coller avec liaison.
Les chiffres apparaissent comme dans la plage de cellules de la première feuille de calcul. Mais ce sont des formules qui sont dans ces cellules. Les valeurs vont changer lorsque le contenu des cellules de la première feuille de calcul va être modifié. Le collage avec liaison permet d’organiser un classeur Excel avec une feuille de données qui seule sera modifiée, les autres feuilles utilisant par liaison ces données. Il est à noter que le collage spécial fonctionne également entre 2 classeurs différents, à l’ouverture du classeur contenant les liaisons, il suffit de cliquer sur l’option Mettre à jour les liaisons de façon à tenir compte des modifications effectuées dans le fichier contenant les données.
Un autre intérêt du collage spécial est l’option Transposé qui permet de permuter les lignes et les colonnes d’un tableau.
4.2 CONSOLIDATION
La consolidation est un moyen propre d’utiliser des données réparties entre plusieurs feuilles d’un classeur voire de plusieurs classeurs pour les intégrer dans une feuille qui n’affichera que les résultats globaux (généralement la somme). Par exemple, une société disposant de plusieurs succursales en France reçoit tous les mois de chacune d'entre elles un tableau de bord. L'objectif consiste ensuite à "additionner" tous ces tableaux en un seul tableau général récapitulatif.
Les tableaux de données doivent être formatés de façon identique et il peut être plus facile d’enregistrer un environnement de travail pour ouvrir les fichiers en même temps comme dans l’exemple ci-après : les
3 tableaux de données des succursales et le tableau de consolidation (en bas à droite)
Placer le curseur sur la cellule B4 du tableau de consolidation, puis lancer l’option Consolider du Menu Données.
Choisir la fonction souhaitée, ici la somme
Sélectionner dans la feuille Montpellier la plage de données à additionner, cliquer sur Ajouter
Recommencer l’opération avec les feuilles Nantes et Marseille
L’option Lier aux données source permet de répercuter dans la feuille de consolidation toutes modifications d’un des tableaux de données Montpellier, Nantes ou Marseille
Cliquer sur OK
Le tableau de consolidation est alors automatiquement rempli.
5 TRIS, FILTRES ET SOUS TOTAUX
5.1 TRI
Il est possible de réorganiser les lignes ou colonnes d'une liste en triant les valeurs qu'elle contient, par ordre croissant (de 1 à 9, de A à Z) ou décroissant (de 9 à 1, de Z à A) et sur le contenu d'une ou plusieurs colonnes.
• Sélectionner une cellule de la liste.
• Aller dans le Menu Données et choisir l’option Trier.
• Sélectionner la colonne sur laquelle s’effectuera le tri puis l’ordre Croissant ou Décroissant. Le tri peut se faire successivement sur 3 colonnes différentes
• Cocher Oui pour Ligne de titres ce qui permet de ne pas considérer la première ligne du tableau comme étant à trier.
• Le bouton Options… permet de faire un tri suivant une liste prédéfinie (les mois, jours de la semaine etc..)
• Cliquer sur Ok.
Attention, le tri modifie irrémédiablement l’ordre des données, il est donc recommandé de faire, avant toute intervention, une copie du tableau initial.
5.2 FILTRE AUTOMATIQUE & PERSONNALISE
Pour ne visualiser qu'une partie des lignes d’un tableau de données répondant à un critère spécifique, on utilise l’option Filtre/Filtre automatique du menu Données.
La présence de filtres dans un tableau est indiquée par des petites flèches situées en bas à droite des cellules de la ligne de titre.
En cliquant sur une de ces flèches, on obtient la liste des valeurs existantes dans la colonne. Sélectionner une de ces valeurs. A l’écran ne sont maintenant visibles que les lignes contenant cette valeur. Pour indiquer qu’un filtre a été appliqué à cette colonne, la flèche devient bleue ainsi que les numéros de lignes. Les lignes qui ne correspondent pas à ce critère sont masquées. Il est possible d’appliquer un autre filtre par-dessus le précédent en cliquant sur la flèche d'une autre colonne.
On peut également choisir parmi les valeurs disponibles, les valeurs Vides et Non vides, 10 premiers et Personnalisé.
Personnalisé permet d’avoir 2 critères de filtre sur la même colonne, la boîte de dialogue suivante s’ouvre:
Sélectionner les opérateurs dans les zones situées à gauche, et taper ou sélectionner les valeurs dans les zones situées à droite.
Cliquer sur Et pour combiner deux critères (seules les lignes répondant aux 2 critères seront affichées). Cliquer sur Ou pour utiliser l'un des deux critères (une ligne sera affichée si elle répond à l'un des 2 critères).
Pour afficher de nouveau tout le tableau de données, Données/Filtre/Afficher tout.
5.3 FILTRE ELABORE
Le filtre élaboré permet de faire des recherches plus complexes qu’avec les filtres automatiques et personnalisés puisqu’il permet de spécifier les champs et les critères de tri qu’on souhaite avoir sur ces champs. On peut également extraire les données correspondantes aux critères sur une autre feuille que celles des données. A partir du tableau de données suivant, on va construire un filtre pour avoir les employées de la catégorie 2 ayant un salaire strictement supérieur à 22 500 €.
Le filtre élaboré fonctionne à partir de 3 zones à définir : la zone de données, la zone de critères de tri et la zone de destination. On va créer une nouvelle feuille dans le classeur qui va servir de zone de critère et de destination. On recopie ensuite la 1ère ligne du tableau de données
Il est à noter l’importance de l’écriture des critères : ils sont écrits sur une seule ligne ce qui correspond à des ET entre chaque condition.
Choisir dans le Menu Données l’option Filtre/Filtre élaboré, la fenêtre de dialogue suivante s’affiche
Cocher l’option Copier vers un autre emplacement, cliquer ensuite sur le bouton en fin de la ligne Plages et sélectionner la plage de données dans la feuille Données, une fois la plage sélectionnée, cliquer à nouveau sur le bouton en fin de ligne pour revenir à la fenêtre de dialogue.
Refaire la même opération sur la ligne Zone de critères (attention à ne pas sélectionner de lignes vides dans cette zone) et sur la ligne Destination, cocher l’option Extraction sans doublon.
On veut maintenant filtrer les données pour avoir les administrateurs dont le salaire est inférieur ou égal à 30 000 € ou les ouvriers de catégorie 4
La zone de critère à écrire est alors sur 2 lignes, le changement de ligne permettant de traiter la condition
OU
Les opérateurs utilisables dans la zone de critères sont :
> supérieur
>= supérieur ou égal
< inférieur
<= inférieur ou égal
<> différent de
? remplace un caractère quelconque
* remplace un nombre de caractères quelconque
Le signe = est sous entendu quand une valeur est écrite dans la zone de critère, par exemple dans l’exemple précédent, dans la cellule D2, «Administrateur » est écrit pour simplifier à la place de la formule « =Administrateur »
5.4 SOUS TOTAUX
Lorsque des données se trouvent sous forme de liste, Microsoft Excel peut calculer et insérer des soustotaux dans une feuille de calcul. En insérant des sous-totaux, Excel crée un plan dans la feuille de calcul qui permet d’afficher ou masquer les détails selon les besoins.
• Cliquer sur une cellule de la plage de données.
• Choisir l’option Sous-totaux du menu Données.
• Dans la liste A chaque changement de, sélectionner une colonne de référence pour le sous-total, ce qui implique que les données doivent être au préalable triées par rapport à cette colonne.
• Dans la liste Utiliser la fonction, choisir le type de sous total. Généralement on utilise Somme.
• Cocher tous les champs pour lesquels on veut un sous-total. Excel propose toujours, par défaut, le dernier champ.
• Cocher la case Remplacer les sous-totaux existants permet de supprimer au préalable les soustotaux existants dans la liste.
• Saut de page entre les groupes insère un saut de page après chaque changement du champ sélectionné dans la liste A chaque changement.
• Synthèse sous les données insère les lignes de sous-total et de total sous les données détaillées.
• Le bouton Supprimer tout permet de supprimer tous les sous-totaux existants dans la feuille sans en créer d'autres.
La feuille présente maintenant un plan à 3 niveaux (boutons 1 à 3 en haut à gauche de la feuille)
En cliquant sur le bouton 2 en haut à gauche de la feuille, on obtient la liste des sous totaux, les détails étant masqués.
Pour afficher les détails d’une société, il suffit de cliquer sur le bouton + devant le nom de la société. En cliquant sur le bouton 1 en haut à gauche de la feuille, on obtient la ligne du total des sous totaux, les sous totaux étant masqués.
Selon le nombre de sous-totaux demandés, les niveaux de plan seront plus ou moins nombreux. On peut utiliser les mises en forme automatiques des tableaux pour faire « ressortir » visuellement les sous totaux suivant le modèle choisi.
6 TABLEAU CROISE DYNAMIQUE
6.1 VOCABULAIRE & FORME DES DONNEES
• Champ : caractéristique sur une personne, une chose ou un événement qui doit être conservée dans une base de données.
• Enregistrement : Série de champs qui décrivent une personne une chose ou un événement
Dans le cas d’une base de données Excel (une feuille d’un classeur), chaque colonne représente un champ. Le nom du champ doit être sur la première ligne. Chaque ligne suivante représente un enregistrement. Afin qu’Excel soit capable de reconnaître tous les enregistrements qui constituent une base, il est important qu’il n’y ait pas de ligne vide.
Pour faire un tableau croisé dynamique à partir d’une feuille Excel, le tableau ne doit pas contenir de sous totaux, de regroupements de données en plan, de filtres par contre il peut y avoir des formules. Le fichier ci-après servira de base de données pour construire, dans la suite, des tableaux croisés dynamiques.
6.2 CREER UN TABLEAU CROISE DYNAMIQUE
A partir du fichier de données précédent, on va construire un tableau donnant le salaire total par titre. Pour cela, positionner le curseur sur une des cellules du tableau de données, aller dans le menu Données et choisir l’option Rapport de tableau croisé dynamique pour lancer l’assistant.
Le premier écran permet de choisir le type de la source de données, ici un fichier Excel, cliquer sur le bouton Suivant
Le tableau de données est automatiquement proposé, on peut choisir une autre plage ou cliquer sur le bouton Suivant pour garder celle proposée.
Cette étape permet de construire le tableau à partir des champs, faire glisser les boutons de champs sur le tableau sur les différentes parties PAGE, LIGNE, COLONNE et DONNEES. Faire glisser le bouton TITRE sur la zone LIGNE et le bouton SALAIRE sur la zone DONNEES. Dans la zone DONNEES, Excel calculera, par défaut, la somme des salaires car les valeurs du champ SALAIRE sont des nombres. Pour les champs dont les valeurs sont des chaînes de caractères, Excel calculera par défaut le nombre d’enregistrements correspondants à ce champ (Cf. paragraphe 6.3.10 pour changer ce paramètre par défaut)
Cliquer sur le bouton Suivant
Garder l’option Nouvelle feuille pour avoir le tableau sur une autre feuille que celle des données puis taper sur Fin. Le tableau suivant a été généré
6.3 ARRANGER & MODIFIER UN TABLEAU
6.3.1 Barre d’outils tableau croisé dynamique
Pour faire apparaître la barre d’outils spécifique au tableau croisé dynamique, il faut cliquer sur l’option Tableau croisé dynamique du menu Affichage/Barre d’outils. La barre suivante permet d’avoir accès rapidement à des fonctions permettant de modifier un tableau croisé
6.3.2 Voir les données
Excel permet de voir les enregistrements qui composent les résultats du tableau. Par exemple pour lister tous les enregistrements concernants les administrateurs, positionner le curseur sur la cellule B3, double cliquer. Une nouvelle feuille de calcul a été créée contenant les lignes du fichier de données concernant les administrateurs.
6.3.3 Modifier le tableau, ajouter/supprimer/déplacer un champ
Les modifications du tableau relative à l’ajout ou la suppression d’un ou plusieurs champs et à la disposition des champs dans le tableau se font en retournant à l’étape 3 de l’assistant. Pour cela, positionner le curseur sur une des cellules du tableau, clic droit de la souris et choisir l’option Assistant ou bien cliquer sur l’icône de la barre d’outils. Faire glisser les boutons de champs aux emplacements souhaités puis cliquer sur le bouton Fin.
6.3.4 Modifier un champ (champ dynamique)
On peut modifier les différents paramètres d’un champ comme son nom, son type, son ordre d’affichage etc…
Il suffit d’aller sur un bouton de champ, TITRE par exemple (cellule grisée en tête de colonne), double clic et la fenêtre Champ dynamique liée au champ TITRE s’ouvre
• Changer le nom d’un champ
Les noms des champs sont donnés par défaut par l’intitulé des colonnes de la feuille de données. On peut changer ces noms sans changer le fichier de données. Il suffit dans la case Nom d’écrire FONCTION à la place de TITRE par exemple, puis de valider (OK).
• Modifier la synthèse par champ
Par défaut, dans le tableau à la fin d’une valeur d’un champ (par exemple Administrateur), Excel calcule le sous total, pour obtenir une synthèse d’un autre type, par exemple la moyenne des valeurs, le minimum …, cliquer dans le menu déroulant de la partie Sous-Totaux sur Moyenne, Min…, pour supprimer cette ligne de synthèse du tableau, cocher Aucun.
• Masquer certaines valeurs d’un champ
Pour connaître les résultats des fonctions autres qu’Administrateur, il suffit de cliquer dans la fenêtre Masquer les éléments sur Administrateur. Le tableau qui s’affiche ne contient que les résultats des autres fonctions. On peut également masquer plusieurs éléments à la fois.
• Afficher les résultats suivant certains critères
Pour trier les résultats du tableau par ordre croissant sur la somme des salaires par fonction, il faut cliquer sur le bouton Avancé
Par défaut, l’option Sur ordre est cochée dans les parties basses droite et gauche de la fenêtre. Cliquer sur Croissant et choisir le champ Somme SALAIRE pour obtenir le tri des résultats souhaité, valider pour sortir (deux fois de suite OK).
La fenêtre de droite (affichage automatique) permet d’afficher les résultats suivant certains critères. Par exemple, si on coche dans la fenêtre précédente, l’option Par défaut, on va obtenir le tableau suivant (Administrateur étant la fonction ayant la somme des salaires la plus élevée), le champ sur lequel porte l’option d’affichage est écrit en bleu.
6.3.5 Modifier la mise en forme
On peut utiliser sur le tableau croisé dynamique l’option Mise en forme automatique du menu Format pour présenter le tableau sous une des formes pré-définies dans Excel.
6.3.6 Utilisation de la zone PAGE
Les tableaux faits jusqu’à présent, n’utilisent que les zones, LIGNE, COLONNE et DONNEES. La zone PAGE permet de rajouter une dimension au tableau et de filtrer les informations sur le ou les champs qui s’y trouvent.
Cliquer sur Fin pour obtenir le tableau correspondant.
En cliquant sur la liste de la cellule B1, on voit qu’on peut afficher les résultats de toutes les catégories ou une par une.
L’option Afficher les pages de la barre d’outils tableau croisé dynamique génère dans le classeur une feuille par catégories.
6.3.7 Actualiser les données
Pour ajouter des données ou modifier une erreur sur une donnée, il faut commencer par agir sur la feuille contenant la base de données. Ici, par exemple, le salaire de Karl Lalonde est de 37 100 € au lieu de 31 500 €. Pour mettre à jour le tableau croisé dynamique c’est à dire répercuter les modifications des données dans le tableau, il faut positionner le curseur sur une des cellules du tableau, clic droit de la souris, option Actualiser les données.
6.3.8 Grouper les valeurs
Cette option permet de regrouper certaines valeurs d’un champ. Dans l’exemple, on peut regrouper les employés qui sont au siège social (administrateurs et secrétaires) et ceux qui sont sur le terrain (ouvriers et commerciaux).
Pour grouper les administrateurs et les secrétaires, sélectionner les 2 cellules (cliquer sur la cellule Administrateur, en maintenant la touche CTRL appuyée cliquer sur la cellule Secrétaire), appuyer sur le bouton droit de la souris, choisir dans le menu contextuel qui apparaît l’option Grouper et créer un plan/Grouper. Un nouveau champ s’est ajouté à la zone LIGNE nommé FONCTION2. Le groupe créé s’appelle Groupe 1 par défaut, on peut changer le nom en cliquant sur la cellule et en modifiant le contenu de la cellule.
6.3.9 Masquer/Afficher un champ
Certains champs d’un tableau peuvent être superflus dans certaines conditions, plutôt que de les supprimer, on peut les masquer et les afficher suivant le besoin. Pour cela placer le curseur sur un bouton de champ par exemple FONCTION2 et appuyer sur le bouton droit de la souris choisir l’option
Grouper et créer un plan/Masquer. On peut aussi double cliquer sur la cellule Administration puis sur Terrain pour masquer les champs
Pour afficher à nouveau les champs, placer le curseur sur un bouton de champ par exemple FONCTION2 et appuyer sur le bouton droit de la souris choisir l’option Grouper et créer un plan/Afficher ou double cliquer sur la cellule Administration puis sur Terrain pour afficher les champs
6.3.10 Changer les paramètres d’un champ
Excel calcule par défaut la somme des valeurs du champ SALAIRE, on peut modifier ce paramètre et calculer par exemple la moyenne des salaires, le minimum, ou le % par rapport au total des salaires.
Pour cela, appuyer sur le bouton droit de la souris sur la cellule SALAIRE, puis cliquer sur Options
Dans le menu déroulant Afficher les données , cliquer sur l’option % du total, puis OK.
Pour pouvoir interpréter ce tableau, il manque l’information du nombre de personnes que chacun de ces pourcentages représente. Pour cela, on ajoute dans la zone DONNEES du tableau le champ SALAIRE une deuxième fois qui sera automatiquement appelé Somme SALAIRE2 et on choisi nombre dans l’option Synthèse par de la fenêtre Champ dynamique de SALAIRE2 (double cliquer sur SALAIRE2), on obtient le tableau suivant qui permet une interprétation plus fine des résultats
6.4 CREATION D’UN SECOND TABLEAU CROISE DYNAMIQUE ISSU DES MEMES DONNEES
On peut créer autant de tableaux croisés dynamiques que l’on veut à partir d’une même source de données. Pour cela, positionner le curseur sur une des cellules du tableau de données, aller dans le menu Données et choisir l’option Rapport de tableau croisé dynamique pour lancer l’assistant. La seule différence avec le § 6.2 vient de 2 écrans supplémentaires entre la 2ème et la 3ème étape de la création.
Cliquer sur Oui pour optimiser la taille mémoire.
Cliquer sur le bouton Suivant pour accéder à l’étape 3 de la création d’un tableau et le positionnement des champs.
On reprend la disposition des champs du dernier tableau croisé dynamique.
6.5 CREATION D’UN CHAMP CALCULE
Excel permet de créer des nouveaux champs, à partir de ceux existants. On souhaite ajouter au tableau un champ représentant la contribution de l’employeur à divers programmes tels que l’assurance et le régime de retraite parmi d’autres, cette contribution étant égale à 50% du salaire des employés. Placer le curseur sur une cellule du tableau croisé dynamique, appuyer sur le bouton droit de la souris et sélectionner l’option Champ calculé du menu Formules.
Changer le nom du champ (champ1 par défaut) pour un nom plus parlant, ici Cotisations. La formule de calcul du champ est Cotisations = 0,5 * SALAIRE.
Après avoir tapé dans la ligne Formule, « 0,5* », cliquer sur SALAIRE dans le menu déroulant Champs de la fenêtre de dialogue puis cliquer sur le bouton Insérer un champ. Cliquer sur Ajouter puis OK
7 REPRESENTATION GRAPHIQUE
Pour faire une représentation graphique du tableau précédent, il faut commencer par sélectionner la plage de données y compris la ligne de titre, puis choisir l’Option Graphique du menu Insertion ou cliquer sur l’icône de la barre d’outils.
La première étape pour faire un graphique est le choix du type de représentation qui dépend des données. En général, pour représenter la décomposition d’un tout entre ses parties (comme dans l’exemple précédent) les secteurs sont recommandés, pour représenter des phénomènes d’évolution et de répartition, les histogrammes ou les courbes sont plus adaptés, enfin pour représenter les phénomènes de position et de corrélation, les barres ou les nuages de points sont utilisés le plus souvent.
On peut ensuite choisir un sous-type et visualiser le résultat en maintenant la touche Maintenir appuyé pour visionner.
Appuyer sur Suivant pour passer à l’étape 2 de la création du graphique qui comporte 2 onglets
On peut à ce niveau modifier la plage des données si la sélection préalable à la création du graphique n’était pas correcte. Appuyer sur Suivant pour passer à l’étape 3 de la création du graphique.
L’exemple des secteurs choisi, comporte à cette étape 3 onglets :
- l’onglet Titre permet de modifier le titre du graphique
- l’onglet Légende permet d’afficher ou non la légende et de changer la position de la légende
- l’onglet Etiquettes des données permet d’afficher sur les secteurs la valeur ou le pourcentage de chaque partie
Appuyer sur Suivant pour passer à la dernière étape de la création du graphique qui permet de choisir d’afficher le graphique dans la même feuille que les données ou dans une nouvelle feuille.
Après avoir appuyé sur Fin, on obtient la représentation suivante
