excel 2000 intermédiaire - meknes.free.fr · excel permet de créer et d'insérer des...

EExxcceell 22000000

IInntteerrmmééddiiaaiirree

André GuindonTechnicien en formation

Téléphone : 4815Courriel : [email protected]

Kathleen AbbottCourriel : [email protected]

Le 26août 2002

Université de Montréal Direction des ressources humaines Développement individuel et organisationnel

mailto:[email protected]

mailto:[email protected]

Table des matières

FONCTION DE RECHERCHE.................................................................................................. 1

CONCATENER ............................................................................................................................ 4 Syntaxe ............................................................................................................................................... 4

GRAPHIQUE................................................................................................................................ 5 Sélectionner les données........................................................................................................................ 5 Créer un graphique ................................................................................................................................ 5

Composantes d'un graphique .................................................................................................. 7 Type de graphique ................................................................................................................................. 8

Choix de types de graphique et exemples .......................................................................................... 8 Sélection d'un type de graphique différent .......................................................................................... 12

Mise en forme des graphiques ............................................................................................... 12 Modifier les options du grapique......................................................................................................... 12 Modifier la sélection des séries............................................................................................................ 13 Couleurs, textures et dégradés, traits ................................................................................................... 13 Mises en forme numériques, échelle d'axe et marques de graduation ................................................. 13 Modification de l’échelle de l’axe des ordonnées................................................................................ 14

LISTE ET BASE DE DONNÉES .............................................................................................. 16

Saisie des données sur la feuille de calcul ............................................................................. 17 Saisie semi-automatique ...................................................................................................................... 17 Liste de choix....................................................................................................................................... 17

Formulaire de données ........................................................................................................... 18 Formuler des critères de recherche ...................................................................................................... 19

Correspondance de chaîne de caractères ........................................................................................ 19 Opérateurs de comparaison............................................................................................................. 19

LE TRI DANS UN TABLEAU OU UNE LISTE..................................................................... 20 Le tri avec un seul critère..................................................................................................................... 20 Le tri avec deux ou trois critères.......................................................................................................... 20

AJOUT DE SOUS-TOTAUX .................................................................................................... 21 Pour supprimer la fonction Sous-totaux .......................................................................................... 22

Ajout d’une seconde fonction au sous-total......................................................................................... 22

FILTRES AUTOMATIQUES ................................................................................................... 24 Filtre automatique............................................................................................................................ 24 Afficher l’intégralité de la liste ........................................................................................................ 25 Désactiver la fonction Filtre ............................................................................................................ 25

Université de Montréal Table des matières page i

Listes déroulantes ................................................................................................................................ 25 10 premiers…................................................................................................................................... 25 (Personnalisé…) – 1 critère ............................................................................................................. 26 (Personnalisé…) – 2 critères ........................................................................................................... 26

Filtre automatique avec sous-total créé ............................................................................................... 27 Sous-totaux sur des données filtrées ................................................................................................ 28

TABLEAUX CROISÉS DYNAMIQUES................................................................................. 30 Quand utiliser un rapport de tableau croisé dynamique? ............................................................... 30 Termes relatifs aux données sources................................................................................................ 31

L’Assistant Tableau croisé dynamique................................................................................. 32 Déterminer la source de données à utiliser ..................................................................................... 32 Emplacement des données source.................................................................................................... 33 Emplacement du tableau croisé dynamique..................................................................................... 33 Agencer les champs d’un tableau croisé dynamique ....................................................................... 33

Barre d’outils Tableau croisé dynamique ............................................................................................ 38 Calculs multiples sur un champ........................................................................................................... 39

Pour retirer un champ...................................................................................................................... 40 Masquer / Afficher les valeurs d’un champ......................................................................................... 41 Mise à jour des données....................................................................................................................... 42

Mise à jour manuelle........................................................................................................................ 42 Mise à jour automatique .................................................................................................................. 42

Supprimer un tableau croisé dynamique.............................................................................................. 42 Mise en forme du tableau croisé dynamique........................................................................ 43

Pour mettre en forme les nombres du tableau croisé dynamique .................................................... 43 Grouper et dissocier des enregistrements............................................................................. 44

Grouper dates et heures ....................................................................................................................... 44 Grouper les nombres............................................................................................................................ 45 Dissocier les groupes ........................................................................................................................... 46

Création de champs calculés .................................................................................................. 47 Graphique Tableau croisé dynamique.................................................................................. 49

Créer un graphique croisé dynamique à partir d’un tableau croisé dynamique................................... 49 Créer simultanément un graphique croisé dynamique et un tableau croisé dynamique ...................... 51

MISE EN FORME CONDITIONNELLE................................................................................ 53 Mise en évidence des cellules remplissant des conditions spécifiques ............................................ 53

Modification, ajout ou suppression de mises en forme conditionnelles .............................................. 55

CRÉATION D'UNE SÉRIE DE RECOPIE INCRÉMENTÉE ............................................. 56

CRÉATION DE ZONE DE LISTE DÉROULANTE ............................................................. 57 Propriétés de contrôle pour les zones de liste ................................................................................. 58

Université de Montréal Table des matières page ii

CRÉATION DE MODÈLE DE CLASSEUR........................................................................... 59 Création d’un modèle de document ..................................................................................................... 60 Modifier un modèle ............................................................................................................................. 61 Utiliser un modèle que vous avez créé ................................................................................................ 62

Protection de données ............................................................................................................. 63 Protection de cellule(s) ........................................................................................................................ 63 Protection de feuille(s)......................................................................................................................... 64 Protection de classeur .......................................................................................................................... 65

MANIPULATION DES BARRES D’OUTILS........................................................................ 66 Personnaliser une barre d’outils........................................................................................................... 67 Rétablir une barre d’outils personnalisée............................................................................................. 68

MACROS : AUTOMATISATION DES TÂCHES DE ROUTINE ....................................... 69 Enregistrement d'une macro ................................................................................................................ 69

Procédure de création de la macro.................................................................................................. 70 Exécution rapide d'une macro ......................................................................................................... 71 Affecter une macro à un bouton de barre d'outils............................................................................ 72 Lier une macro à un bouton ............................................................................................................. 73 Affecter une macro à un objet graphique......................................................................................... 73

Supprimer une macro........................................................................................................................... 74 Niveau de sécurité des macros............................................................................................................. 75

BIBLIOGRAPHIE / RÉFÉRENCE.......................................................................................... 76

Université de Montréal Table des matières page iii

Module 1 Fonctions Recherche, Concatener, Graphique

Fonction de RECHERCHE

Fonction CONCATENER

Graphique Sélectionner les données Créer un graphique Composantes d'un graphique Type de graphique Mise en forme des graphiques

PRATIQUE 1. RECHERCHEV : RECHERCHE1.XLS / RECHERCHE2.XLS /

RECHERCHE3.XLS 2. CONCATENER : LIBRAIRIE.XLS 3. GRAPHIQUE : GRAPH1.XLS / GRAPH2.XLS

EXCEL 2000 – INTERMÉDIAIRE

Fonction de RECHERCHE

Les fonctions de recherche permettent de consulter un tableau pour localiser une donnée inscrite dans une table de consultation.

Plus particulièrement, la fonction « RECHERCHEV », permet de localiser une information contenue dans une plage spécifique d’un classeur afin de l’utiliser dans différents calculs. Elle recherche une valeur dans la colonne la plus à gauche d’une matrice et renvoie une valeur de la même ligne, en fonction de la colonne spécifiée comme argument. Les valeurs de la première colonne peuvent être du texte, des nombres ou des valeurs logiques. S’il s’agit de texte, il n’est pas tenu compte de la casse.

Pour être en mesure d’effectuer une recherche, la feuille de calcul doit comporter deux zones distinctes :

Zone contenant les données servant à la recherche; Zone contenant les formules servant aux calculs.

PRENDRE NOTE :

La zone des données peut être créée sur la même feuille que la zone des calculs ou sur une autre feuille du même classeur que les informations à obtenir. Dans une table de recherche, les données servant de critères de recherche doivent

obligatoirement être triées en ordre alphabétique ou numérique croissant. Le tableau doit être construit verticalement pour la consultation.

La syntaxe de la fonction « RECHERCHEV » est la suivante :

RECHERCHEV(valeur_cherchée_(indice);table_matrice;no_index_colonne; valeur_proche)

Université de Montréal Formules et Graphiques page 1


SIGNIFICATION DES ARGUMENTS

VALEUR_CHERCHÉE_(INDICE) Valeur à rechercher dans la 1ère colonne de la table de consultation. Cette valeur peut être un nombre, une référence ou une chaîne de caractères.

TABLE_MATRICE Plage de cellules contenant les données à traiter.

NO_INDEX_COLONNE Numéro de colonne, dans la table de consultation, dont la valeur doit être renvoyée en guise de résultat de la fonction.

Valeur logique indiquant si la fonction doit rechercher une correspondance exacte ou approximative.

VRAI ou omis

Approximatif. La valeur supérieure la + proche du critère est renvoyée.

VALEUR_PROCHE

FAUX Correspondance exacte. Si aucune correspondance exacte n’est trouvée, elle renvoie la valeur d’erreur #N/A.

Procédure :

1. Créer la table de consultation; 2. Sélectionner la table de

consultation et lui attribuer un nom en cliquant dans la Zone de nom;

3. Touche ENTRÉE pour valider le nom de la table;

4. Dans la zone de calcul, inscrire les formules suivantes (référer à l’exemple ci-contre).



=RECHERCHEV(A12;employe;2;FAUX)



3

2

1

321

A12;employe;4;FAUX

valeur cherchée table_matrice no_index_colonne vérification

5. Résultat



CONCATENER

La fonction CONCATENER permet d’assembler plusieurs chaînes de caractères de façon à n'en former qu'une seule.

Exemple

Adapter une base de données contenant les colonnes « Prénom / Nom ». Pour une production de type « Facturation », nous pourrions vouloir insérer une colonne qui permettrait d’insérer le prénom suivi du nom sous forme de formule. Ainsi, si les données des colonnes « Prénom / Nom » sont modifiées, la formule de concaténation est directement reliée aux données liées.

Les exemples suivants démontrent les étapes et résultats à atteindre.

Syntaxe Des éléments de texte peuvent être intégrés à la formule. Ces éléments seront considérés invariables et seront positionnés entre guillemets anglais " ". Les autres éléments, de l’exemple décrit, seront des références à des cellules uniques.

Note :

L'opérateur « & » sera utilisé à la place de la fonction CONCATENER pour assembler les éléments de texte.

« & » Assembler les données

" " Texte ou données invariables entre guillemets

Procédure pour notre exemple à partir de la base de données :

1. Afficher la base de données; 2. Insérer une colonne après la colonne « Nom »; 3. Inscrire la formule suivante :

=cellule C2 (cellule du nom) ","&cellule B2 (cellule du prénom) Voir la référence ci-dessous pour la syntaxe de concaténation. La colonne D affiche la formule, la colonne E affiche le résultat de concaténation. Exemple 1

Exemple 2



Graphique

Excel permet de créer et d'insérer des graphiques dans une feuille de calcul. En faisant appel à l’« Assistant Graphique », la création s’effectue en 4 étapes.

Le secret de la création de graphique est la sélection. Cette technique permet de créer un graphique adéquat en tout temps.

SÉLECTIONNER LES DONNÉES La sélection efficace s’effectue par l’intermédiaire de la touche CTRL.

Technique de sélection :

1. Sélectionner la première plage de cellule(s) consécutive(s) (colonne 1); 2. Enfoncer et maintenir la touche CTRL pour la sélection des colonnes suivantes.

Sélection Haut vers bas, de gauche vers la droit.

CRÉER UN GRAPHIQUE Lorsque les données sont sélectionnées, il suffit d’appeler l’Assistant graphique.

Procédure :

1. Menu Insertion / Graphique ou le bouton Assistant graphique. La boîte de dialogue suivante s’affiche :

2. Suivre les étapes en cliquant sur le bouton Suivant. 3. À chaque nouvelle étape, insérer les informations requises dans les différents onglets.

La première étape consiste à décider du type de graphique



La seconde étape consiste à déterminer comment la série devra s’afficher

La troisième étape consiste à insérer les différentes informations qui seront inclues à la présentation.

La dernière étape détermine l’emplacement du graphique dans le classeur :

Nouvelle feuille

Feuille de calcul active.


Composantes d'un graphique

0

1000

2000

3000

4000

5000

6000

7000

Ventes 2001

Trimestre 1 Trimestre 2 Trimestre 3

Quadrillage principal de l'axe des abscisses

Panneaux

Titre du graphique

Zone du graphique

Légende

Échelle des valeurs

Axe des ordonnées

Plancher

Zone de traçage

Quadrillage principal de l'axe des ordonnées

Série de données 1 Série de données 2

Série de données 3

Axe des abscisses


TYPE DE GRAPHIQUE

Excel offre plusieurs types de graphiques. Il est possible de modifier le type de graphique en utilisant le menu contextuel de la Zone de graphique, le menu ci-dessous est affiché.

Choix de types de graphique et exemples

HISTOGRAMMES

Affiche les changements de données pendant une période déterminée ou effectue des comparaisons entre des éléments. Les catégories sont organisées horizontalement, les valeurs verticalement, ce qui permet de mettre en évidence les variations dans le temps.

HISTOGRAMMES EMPILÉS

Affiche la relation qui existe entre un tout et ses éléments individuels. L'histogramme 3D compare des points de données sur deux axes.

BARRES

Permet de comparer des éléments individuels. Les catégories sont organisées verticalement et les valeurs horizontalement, ce qui permet d'attirer l'attention sur la comparaison de valeurs plutôt que sur le temps.

BARRES EMPILÉES

Affiche la relation qui existe entre un tout et ses éléments individuels.

COURBES Représente les tendances des données à intervalles égaux.

SECTEURS Affiche la taille relative d'éléments dont la somme compose une série de données. Ce type de graphique représente toujours une seule série de données et est utile pour mettre en valeur un élément important.



NUAGES DE POINTS Représente les relations entre les valeurs numériques de plusieurs séries de données ou trace deux groupes de valeurs sous forme de série unique de coordonnées XY. Le graphique montre des intervalles irréguliers (ou groupes) de données et est couramment utilisé pour exploiter des données scientifiques. Lorsque vous organisez vos données, placez les valeurs x dans une ligne ou colonne, puis les valeurs y complémentaires dans les lignes ou colonnes adjacentes. Remarque Il est impossible d'utiliser un graphique de type nuages de

points pour un rapport de graphique croisé dynamique.

AIRES Souligne l'importance des changements au fil du temps. Ce type de graphique, qui affiche la somme des valeurs tracées, indique également la relation qui existe entre un tout et ses différentes parties.

ANNEAUX Similaire aux graphiques en secteurs, mais il peut contenir plusieurs séries de données. Chaque anneau du graphique représente une série de données. Ce type de graphique Max-Min-Clôture est souvent utilisé pour illustrer les cours de la Bourse, la représentation de données scientifiques (changements de température).

RADAR Chaque catégorie a son propre axe des ordonnées émanant du point central. Les lignes relient toutes les valeurs de la même série. Un graphique en radar compare les valeurs cumulées d'un certain nombre de séries de données. Exemple : Une série de données couvrant la zone la plus étendue

représente la marque ayant la plus forte teneur en vitamines.

SURFACE Permet d'obtenir une association optimale de deux séries de données. De la même manière que dans une carte topographique, les couleurs et les motifs indiquent les zones appartenant à la même plage de valeurs. Exemple : Démontrer les différentes combinaisons température-heure

aboutissant à la même mesure de résistance à la traction.

BULLES Type de graphique en nuages de points (XY). La taille de la marque de données indique la valeur d'une troisième variable. Pour organiser les données, placez les valeurs X sur une ligne ou une colonne, puis entrez les valeurs Y et les tailles de bulles correspondantes sur les lignes ou colonnes adjacentes. Exemple : Démontrer qu’une société A possède le plus grand nombre de

produits et contrôle la part de marché la plus importante. Remarque Il est impossible d'utiliser un graphique de type bulles de

points pour un rapport de graphique croisé dynamique.



BOURSIER Mesure le volume, comporte deux axes des ordonnées : l'un pour les

colonnes représentant le volume, l'autre pour les cours des valeurs boursières. Remarque Il est impossible d'utiliser un graphique de type boursier pour

un rapport de graphique croisé dynamique.

CONIQUES, CYLINDRIQUES ET PYRAMIDAUX

Les marques de données permettent de modifier l’aspect graphique des histogrammes et des graphiques en barres 3D.

HISTOGRAMME HISTOGRAMME 3D

0 $

2 000 $

4 000 $

6 000 $

8 000 $

LavalQuébec

MontréalTrois-Rivières

Ventes 2001

Laval Québec Montréal Trois-Rivières

0 $1 000 $2 000 $3 000 $4 000 $5 000 $6 000 $7 000 $


Ventes 2001


COURBES SECTEURS 3D

Ventes 2001

25%

17%

19%

39%


Ventes 2001

0 $

1 000 $

2 000 $

3 000 $

4 000 $

5 000 $

6 000 $

7 000 $

8 000 $


Laval QuébecMontréal Trois-Rivières



AIRE ANNEAUX

TYPE PERSONNALISÉ COURBE - HISTOGRAMME



SÉLECTION D'UN TYPE DE GRAPHIQUE DIFFÉRENT

Pour la plupart des graphiques 2D, Excel offre la possibilité de changer le type de graphique d'une série de données ou du graphique entier. Pour la plupart des graphiques 3D, le changement du type de graphique affecte le graphique dans son intégralité. Dans les graphiques 3D barres et histogrammes, une série de données peut être modifier en lui attribuant le type de graphique en cône, cylindre ou pyramide.

Pour modifier le type de graphique :

1. Pour modifier l'intégralité du graphique, cliquer sur le graphique; Pour modifier une série de données, cliquer sur la série;

2. Menu Graphique / Type de graphique; 3. Sous l'onglet Types standard ou Types personnalisés, cliquer sur le type de graphique souhaité.

Pour appliquer le type de graphique en cône, en cylindre ou en pyramide à une série de données en barres ou en histogrammes 3D, sous l'onglet Types standard / liste Type de graphique, sélectionner Cylindre, Cône ou Pyramide, puis activer la case à cocher Appliquer à la sélection. REMARQUES : Si la case à cocher Appliquer à la sélection est désactivée, Microsoft Excel change le type du graphique dans son intégralité, même si une seule série de données est sélectionnée.

Mise en forme des graphiques

La mise en forme des graphiques permet de mettre en évidence les différentes séries, de les adapter en fonction des différents types d’imprimante, de personnaliser les nombres, etc. Cette section démontre également comment modifier les options du graphique ainsi que de la sélection des données.

MODIFIER LES OPTIONS DU GRAPIQUE Les options du graphique permettent d’insérer la légende, afficher les différents quadrillages, titres, etc. La fenêtre de dialogue est similaire à la fenêtre affichée lors de l’élaboration initiale du graphique.

Pour modifier les options du graphique :

1. Sélectionner la Zone de graphique; 2. Cliquer avec le bouton droit de la souris;

Le menu contextuel s’affiche. 3. Commande Options des graphiques…;

Les différents onglets sont alors disponibles pour ajout et modification de l’information.



MODIFIER LA SÉLECTION DES SÉRIES

Pour modifier la sélection des séries affichées :

1. Sélectionner la Zone de graphique; 2. Cliquer avec le bouton droit de la souris;

Le menu contextuel s’affiche. 3. Commande Donnée source…; 4. Cliquer sur le bouton Plage de données; 5. Dans la feuille de calcul, sélectionner les

séries à afficher; 6. Touche ENTRÉE; 7. Valider., bouton OK

Le graphique est mis à jour selon la nouvelle sélection.

COULEURS, TEXTURES ET DÉGRADÉS, TRAITS Vous pouvez appliquer des couleurs, des bordures et des dégradés, modifier l'épaisseur d'un trait ou le style d'une bordure appliqué aux marques de données, à la zone du graphique (intégralité du graphique et des éléments qui le composent) , à la zone de traçage (zone délimitée par les axes et contenant toutes les séries de données) ainsi qu'à d'autres éléments du graphique.

1. Double-cliquer sur l'élément graphique à modifier; 2. Sélectionner l'onglet requis (Motifs, Police, etc.); 3. Sélectionner les options souhaitées.

Pour spécifier un effet de remplissage, cliquer sur Remplissage, puis sélectionner les options sous les onglets Dégradés, Texture ou Motif.

MISES EN FORME NUMÉRIQUES, ÉCHELLE D'AXE ET MARQUES DE GRADUATION Vous pouvez spécifier la plage des valeurs affichées sur l'axe des ordonnées et modifier l'intervalle entre les ordonnées le long de l'axe, spécifier si un axe possède des marques de graduation.

Vous pouvez également mettre en forme les nombres d'un graphique, de la même manière que ceux d'une feuille de calcul. Pour appliquer les mises en forme numériques les plus courantes, cliquer sur l'axe ou sur l'étiquette de données contenant le nombre à mettre en forme, puis appliquer le format monétaire, pourcentage ou décimal en cliquant sur les boutons de la barre d'outils Mise en forme.



MODIFICATION DE L’ÉCHELLE DE L’AXE DES ORDONNÉES

Chaque abscisse est identifiée par une étiquette et séparée des autres abscisses par des marques de graduation. Les intervalles entre les étiquettes et les marques de graduation peuvent être modifiés. Pour les graphiques 2D, vous pouvez spécifier le point d'intersection entre l'axe des abscisses (X) et celui des ordonnées (Y).

1. Cliquer (menu contextuel) sur l'axe des abscisses à modifier;

2. Commande Format de l’axe / onglet Échelle; 3. Modifier les paramètres Minimums / Maximum /

Unité principale (valeur de pas).

CONSEILS Pour inverser le sens des barres ou des colonnes dans un graphique en barres ou en histogrammes, activer la case à cocher Valeurs en ordre inverse.

Si les valeurs du graphique sont élevées, vous pouvez réduire la taille du texte de l'axe et le rendre plus lisible en modifiant l'unité d'affichage. Si les valeurs du graphique sont comprises entre 1 000 000 et 50 000 000, par exemple, vous pouvez afficher les nombres 1 à 50 sur l'axe et faire apparaître une étiquette indiquant qu'ils constituent les unités et que celles-ci expriment des millions.


Module 2 Liste et Base de données

Liste et base de données Saisie des données sur la feuille de calcul Formulaire de données Formuler des critères de recherche

Le tri dans un tableau ou une liste

Ajout de sous-totaux Ajout d’une seconde fonction au sous-total

Filtres automatiques

PRATIQUE

1. Professeur.xls

2. Librairie.xls


Liste et base de données

Du fait de sa structure, Excel permet de gérer des données sous forme de listes, par exemple des adresses ou des stocks. Une grille facilite ainsi la saisie, la modification et la recherche des données. Un ensemble de données est automatiquement reconnu comme étant une liste lorsque des opérations telles que la recherche et le tri des données ou le calcul de sous-totaux lui sont appliquées. Pour ce faire, la première ligne de la liste doit contenir des titres de colonnes pouvant être reconnues comme noms de champs.

Une liste est une série étiquetée de lignes contenant des données similaires. Par exemple, nous pourrions considérer une liste d’employés avec la date d’entrée en fonction, leur date de naissance et le département dans lequel il travaille. Ainsi, les colonnes représentent des champs et les lignes des enregistrements.

Critères à respecter pour la création et gestion de liste : Les étiquettes représentent les noms de champs; Les cellules d’une colonne contiennent les données du même type; Utiliser le même format de mise en forme pour toutes les cellules d’une colonne; Un enregistrement est une ligne unique de la liste; Ne pas insérer d’espace en début de cellule; Ne pas laisser de ligne vide entre les enregistrements de la liste; Ne pas laisser de colonne vide entre les champs de la liste; Éviter de créer plus d’une liste sur une même feuille de calcul; S’il y a d’autres données dans la feuille de calcul, insérer une ligne et une colonne vide entre la liste et

les autres données; Champs La liste peut contenir jusqu’à 32 champs (colonnes).

Enregistrements

Opérations possibles sur une base de données :

Tri de données; Filtrage de données; Ajout de sous-totaux; Statistiques; Tableaux croisés dynamiques.

Université de Montréal Liste et Base de données page 16


Saisie et mise à jour des données à l’aide de la grille

Suite à la création de la base de données (création des champs, structure de la liste, etc.), une liste peut être exploitée avec ou sans l’aide de la grille. Trois méthodes de saisie sont possibles :

Importation des données d’une autre application; Saisie des données sur la feuille de calcul; Utilisation du formulaire de données Excel.

Saisie des données sur la feuille de calcul

Il est possible de saisir des données directement dans la feuille de calcul. Certaines fonctionnalités d’Excel permettent d’accélérer la saisie des informations.

SAISIE SEMI-AUTOMATIQUE La saisie semi-automatique permet d’accélérer et de fiabiliser la saisie des données. La complétion automatique permet à Excel de proposer automatiquement un mot complet pouvant correspondre à une saisie antérieure. Une info-bulle s’affiche à l’écran.

La touche TAB permet d’accepter le mot proposé et déplace le curseur à la cellule suivante. Pour refuser la proposition, poursuivre la saisie de données.

LISTE DE CHOIX La liste de choix permet de garder l’intégralité de l’entrée de données dans la grille. Par exemple, les termes « Techn. » et « Technicien » seront considérés comme des enregistrements de groupes différents. Ainsi, une problématique de recherche, de tri et de filtre sera créé dans la liste.

Pour maintenir l’intégrité des données, vous pouvez utiliser les listes de choix.

Procédure :

1. Utiliser le menu contextuel (bouton droit de la souris) sur la cellule dans laquelle vous souhaitez entrer la valeur;

2. Sélectionner l’option « Liste de choix »; 3. Sélectionner la valeur requise à insérer.



Formulaire de données

La grille ou formulaire permet de visualiser, modifier et supprimer des enregistrements. Une grille est une boîte de dialogue représentant la totalité d’une fiche à l’écran. Lorsque vous utilisez la grille pour saisir des données, le nouvel enregistrement est ajouté à la fin de la liste de données.

Pour accéder à la grille :

1. Sélectionner une cellule dans la liste à laquelle vous désirez apporter une modification ou visualiser les informations;

2. Menu Données / Grille…; 3. La grille suivante s’affiche. La boîte de dialogue affiche le premier enregistrement de la liste.

La « Barre de titre » de la grille affiche le nom de la feuille de calcul contenant la liste La section de gauche de la grille affiche la liste des

noms de champs. Les champs constituant une fiche appartiennent tous à une même ligne. Les champs sont affichés dans l’ordre où ils apparaissent dans la feuille de calcul. Les noms sont créés à partir des étiquettes de colonnes. Pour modifier le nom d’un champ, vous devez le modifier directement dans la feuille de calcul.

La « Barre de défilement » permet de vous déplacer dans la base de données et d’accéder à tous les enregistrements.

Les boutons de la grille

Nouvelle : Permet d’insérer une nouvelle fiche. Excel se positionne directement à la fin de la liste et affiche la mention « Nouvel enregistrement ».

Supprimer : Supprime l’enregistrement courant.

Rétablir : Rétablit l’enregistrement (en cours) modifié dans la grille.

Précédente / Suivante : Affiche l’enregistrement précédent ou suivant.

Critères : Affiche la boîte de dialogue pour la saisie des critères de recherche.

Fermer : Ferme la grille.



FORMULER DES CRITÈRES DE RECHERCHE

Il est possible d’effectuer des recherches en utilisant une comparaison directe. Il arrive cependant que l’on soit obligé d’effectuer des recherches dans de grands ensembles de données sans qu’il ne s’agisse toujours d’une comparaison directe. Vous pouvez combiner plusieurs critères ou utiliser des critères de comparaison tels « plus grand que », « plus petit que ».

Correspondance de chaîne de caractères Procédure :

1. Sélectionner une cellule dans la liste; 2. Menu Données / Grille… / bouton Critères;

Une grille vierge s’affiche. 3. Taper les critères de recherche dans les zones de saisie correspondantes; 4. Cliquer sur Précédente / Suivante pour afficher l’occurrence suivante ou précédente remplissant les

critères spécifiés.

Opérateurs de comparaison Procédure :

1. Sélectionner une cellule dans la liste; 2. Menu Données / Grille… / bouton Critères;

Une grille vierge s’affiche. 3. Taper les critères de recherche dans les zones de saisie correspondantes en utilisant les opérateurs

suivants :

< plus grand > plus petit (*) remplace un nombre quelconque de caractères (?) remplace n’importe quel caractère

4. Cliquer sur Précédente / Suivante pour afficher l’occurrence suivante ou précédente remplissant les critères spécifiés.



Le tri dans un tableau ou une liste

LE TRI AVEC UN SEUL CRITÈRE

Excel offre la possibilité de trier les données d’un classeur selon une ou plusieurs clés de tri spécifiques.

Vous pouvez trier une liste rapidement en sélectionnant une cellule dans la colonne choisie comme référence et en cliquant sur le bouton « Trier dans l'ordre croissant » ou « Trier dans l'ordre décroissant ».

Dans l'exemple suivant, la liste sera triée par Ordre croissant de Ventes.

LE TRI AVEC DEUX OU TROIS CRITÈRES

Pour trier les données d'une liste selon deux ou trois critères :

1. Sélectionner les lignes de données à trier; 2. Menu Données / Trier; 3. En ouvrant la liste de chaque clé de tri, sélectionner

les champs à trier; 4. Indiquer l’ordre de tri (croissant / décroissant);

La section « Trier par » a priorité sur la section « Puis par ».

5. Valider.

Dans l'exemple suivant, la liste est triée par ordre croissant d’EMPLOYÉ et VENTES.



.

Ajout de sous-totaux

Les sous-totaux sont des calculs de nature statistique (somme, moyenne, minimum, maximum, etc.). La fonction « Sous-totaux » permet d’ajouter des sommes intermédiaires dans la feuille de calcul.

Pour ajouter des « Sous-totaux », la base de données doit être triée en utilisant la colonne sur laquelle porte le calcul.

Par exemple, si l’on désire obtenir la SOMME des Ventes et Commission par employé, la base de données doit avant tout être triée par référence d’employé (ajouter une clé de tri : « Mois » croissant pour efficacité d’analyse).

Procédure :

1. Trier la base de données selon la colonne où portera le calcul; 2. Sélectionner une cellule quelconque dans la plage de données; 3. Menu Données / Sous-totaux; 4. Dans la zone « À chaque changement de », sélectionner la

colonne de référence; 5. Dans la zone « Utiliser la fonction », sélectionner la fonction

requise; 6. Dans la zone « Ajouter un sous-total à », sélectionner les

colonnes où les sous-totaux doivent être calculés.

Dans l’exemple suivant :

Tri des colonnes : Employé / Mois

À chaque changement de : Employé

Utiliser la fonction : Somme

Ajouter un sous-total à : Ventes / Commission

Résultat



Pour supprimer la fonction Sous-totaux 1. Sélectionner une cellule quelconque dans la plage de données; 2. Menu Données / Sous-totaux; 3. Activer le bouton « Supprimer tout ».

AJOUT D’UNE SECONDE FONCTION AU SOUS-TOTAL

Il est possible d’ajouter plusieurs fonctions de type « Sous-totaux » à un tableur. Dans ce cas, il est important de désactiver la case à cocher « Remplacer les sous-totaux existants »

Dans l’exemple suivant, un sous-total « Employé » a été généré pour le total des Ventes / Commission. La seconde fonction statistique utilisée sera la MOYENNE des Ventes et Commission de chaque employé.

Pour fin d’analyse, les données sont triées selon les clés suivantes : Clé 1 : Employé, Clé 2 : Mois, Premier SOUS-TOTAL effectué :

SOMME des Ventes et Commission par employé



Pour ce faire :

1. Positionner le curseur dans une plage de la base de données; 2. Menu Données / Sous-totaux; 3. Dans la liste « Utiliser la fonction », sélectionner la fonction

Moyenne; 4. Vérifier que, dans la section « Ajouter un sous-total à :, Ventes

et Commission sont cochés; 5. Désactiver la case à cocher « Remplacer les sous-totaux

existants »; 6. Valider.

Résultat



Filtres automatiques

Précédemment, nous avons vu comment rechercher des enregistrements à l’aide de la grille. Vous pouvez ainsi saisir des critères dans les différents champs et, ensuite, afficher un à un les enregistrements correspondant à ces critères. Cette méthode convient parfaitement pour de petites listes.

Cette section démontrera comment filtrer des enregistrements dans les listes importantes et afficher ces enregistrements au même moment. La commande « Filtre automatique » permet de définir un critère pour chaque colonne et limiter l’affichage aux enregistrements qui correspondent à ces critères.

Filtre automatique Lors de l’application de filtre automatique, la sélection déterminera sur quelle(s) champs sera appliquée la commande.

Si une cellule est sélectionnée, tous les champs de la liste afficheront la commande de « Filtre ». Pour appliquer un « Filtre » sur une plage de cellules distinctes, sélectionner les champs concernés.

Procédure pour filtre automatique :

1. Sélectionner une cellule ou une plage dans la liste; 2. Menu Données / Filtre / Filtre automatique.

Une zone de liste s’affiche à la droite de chaque titre de colonne. 3. Cliquer sur la flèche de la colonne pour laquelle vous voulez définir un

critère; 4. Sélectionner, dans la liste, les entrées qui doivent être affichées. En sélectionnant un critère, vous

masquez instantanément les lignes ne répondant pas à la valeur sélectionnée. NOTE : Les flèches situées à la droite

des noms de champs sont de couleur lorsqu’un filtre a été appliqué sur ce champ. Cette indication permet d’indiquer que les données affichées ne représentent pas la totalité de la liste. Les numéros de ligne sont également de couleur accentuée.

Filtre appliqué sur août-01



Afficher l’intégralité de la liste Pour retrouver l’intégralité de la liste :

1. Menu Données / Filtre / Afficher tout.

Désactiver la fonction Filtre Pour désactiver la commande « Filtre automatique », il suffit de rappeler la commande.

1. Menu Données / Filtre / Filtre automatique.

LISTES DÉROULANTES Les listes déroulantes, outre les valeurs enregistrées dans les colonnes de la liste, présentent les options suivantes :

(Tous), (10 premiers…), (Personnalisé…), (Vides), (Non vides)

10 premiers… Cette option permet d’extraire de la liste un certain nombre de valeurs numériques à filtrer selon les critères spécifiés dans ce filtre.

NOTE : Ce filtre ne peut être appliqué qu’à des champs contenant des valeurs numériques.

1. Dans la liste de choix Haut / Bas, sélectionner l'option selon que vous souhaitez obtenir les valeurs les plus élevées ou les moins élevées;

2. Dans la seconde liste, indiquer le nombre de valeurs à afficher. 3. Dans la troisième liste déroulante, vous pouvez choisir les

options Éléments ou les x % des meilleures valeurs par rapport au nombre total de valeurs. Si vous sélectionnez « Pourcentage », vous affichez 10 % des valeurs les plus fortes ou les plus faibles de la liste.



(Personnalisé…) – 1 critère Il est possible de définir des filtres personnalisés lorsque la commande « Filtre automatique » est activée. Les filtres personnalisés permettent d’effectuer des recherches plus complexes en utilisant des opérateurs de comparaison et en spécifiant des plages de valeurs en guise de critères.

Procédure :

1. Sélectionner une cellule dans la liste; 2. Menu Données / Filtre / Filtre automatique; 3. Activer la flèche située à la droite du champ à filtrer afin d’accéder à la liste; 4. Sélectionner l’option (Personnalisé…).

La boîte de dialogue suivante s’affiche. 5. Sélectionner l’opérateur de comparaison dans la première liste déroulante; 6. Dans la zone de liste de droite, entrer la valeur de référence; 7. Valider avec la touche ENTRÉE.

VENTES inférieures ou égales à 50 000

(Personnalisé…) – 2 critères La boîte de dialogue (Personnalisé…) permet de combiner jusqu’à 2 critères par colonne.

Procédure :

1. Sélectionner une cellule dans la liste; 2. Menu Données / Filtre / Filtre automatique; 3. Activer la flèche située à la droite du champ à filtrer afin d’accéder à la liste; 4. Sélectionner l’option (Personnalisé…).

La boîte de dialogue suivante s’affiche. 5. Sélectionner l’opérateur de c

dans la première liste déroulante; Dans la zone

omparaison

6. de liste de droite,

7. de entrer la valeur de référence; Activer l’option ET / OU afindéfinir le second critère;



8. Définir le second critère; 9. Valider avec la touche ENTRÉE.

NOTE : Il est possible d’utiliser des caractères génériques (* ou ?) pour rechercher des valeurs approximatives.

VENTES: Supérieur à 100 000 et Inférieur ou égal 120 000

FILTRE AUTOMATIQUE AVEC SOUS-TOTAL CRÉÉ Les filtres automatiques peuvent également être appliqués à des listes de données pour lesquelles un sous-total a été créé. Les sous-totaux s’adaptent automatiquement aux données filtrées. À l’inverse, des sous-totaux peuvent être créés à partir de listes de données filtrées. La fonction « Sous-total » permet de préciser différentes méthodes de somme à utiliser parmi onze méthodes différentes comme, par exemple, moyenne, nombre d’éléments, produit, etc.

Dans l’exemple suivant, un filtre automatique a été appliqué sur-le-champ « Employé » afin de n’afficher que les données de « Lavigne, Diane ». Dans l’exemple suivant :

Tri des colonnes : Employé / Mois Filtre : Lavigne, Diane À chaque changement de : Employé Utiliser la fonction : Moyenne Ajouter un sous-total à : Ventes / Commission



Sous-totaux sur des données filtrées Si vous désirez faire apparaître rapidement un TOTAL sur des données filtrées, positionner le curseur dans la cellule où doit apparaître le résultat et cliquer sur le bouton « Somme automatique » de la barre d’outils Standard.

Excel insère la fonction « Sous-totaux » plutôt que la fonction « Somme automatique ». Excel détecte la présence de données filtrées. La fonction « Sous-total » calculera la somme en n’utilisant que les cellules visibles.

Lorsque la fonction « Sous-total » est utilisée avec une base de données, ne pas ajouter le mot « TOTAL » à côté de la formule de somme car Excel pourrait interpréter cette ligne comme un enregistrement de la base de données.


Module 3 Tableau croisé dynamique

Tableaux croisés dynamiques L’Assistant Tableau croisé dynamique Barre d’outils Tableau croisé dynamique Calculs multiples sur un champ Masquer / Afficher les valeurs d’un champ Mise en forme du tableau croisé dynamique Grouper et dissocier des enregistrements Création de champs calculés Graphique Tableau croisé dynamique

PRATIQUE

Librairie.xls


Tableaux croisés dynamiques

Un rapport de tableau croisé dynamique est un tableau interactif permettant de synthétiser et d’analyser rapidement de grandes quantités de données. Vous pouvez faire pivoter ses lignes et colonnes pour afficher diverses synthèses des données source, filtrer les données en affichant différentes pages ou afficher les détails des zones intéressantes.

Un tableau croisé dynamique est une table créée à partir d’une feuille de calcul Excel et permettant de considérer, sous différents points de vue, des ensembles importants de données. Une fois créé, un tableau croisé dynamique peut être réorganisé simplement en déplaçant les champs. Il s’agit d’un tableau qui représente, de manière synthétique, des informations issues des champs d’une liste ou d’une base de données et dans lequel vous pouvez décider vous-même sous quel angle vous désirez considérer les données.

Il est possible de créer plusieurs tableaux croisés dynamiques à partir d’une même feuille de calcul ou de plusieurs feuilles de calcul ou classeurs.

Quand utiliser un rapport de tableau croisé dynamique? Utilisez un rapport de tableau croisé dynamique afin de comparer des totaux, en particulier lorsque vous avez une longue liste de chiffres à synthétiser et que vous souhaitez comparer plusieurs faits à propos de chaque chiffre. Utilisez des rapports de tableau croisé dynamique lorsque vous désirez que Microsoft Excel effectue pour vous une opération de tri, de sous-total ou de total. Un rapport de tableau croisé dynamique étant interactif, vous pouvez, ainsi que d'autres utilisateurs, afficher les données de manière à voir davantage de détails ou calculer d'autres synthèses.

Avant de créer le rapport, il est nécessaire d’identifier et de préparer les données source. Les données sources sont constituées d’une liste ou d’une base de données qui fournit les données au rapport de tableau croisé dynamique.

NOTE : Dans un tableau croisé dynamique, il est impossible d’entrer des données, ni de les modifier directement. Le tableau croisé dynamique est lié à la source de données, les sommes qui y figurent sont en réalité préétablies. Cependant, il est possible de modifier le format et de choisir parmi un certain nombre d’options de calcul.

ATTENTION: Avant d’appeler l’Assistant Tableau croisé dynamique, retirer tous les sous-totaux et filtres afin d’utiliser la liste entière.

Université de Montréal Tableau croisé dynamique page 30


Les exemples feront référence à la liste (base de données) suivante (LIBRAIRIE.XLS).

Termes relatifs aux données sources Champs : Catégorie d’information. Par exemple, le champ EMPLOYÉ regroupe les

informations relatives aux employés Lavigne, Diane / Rémi, Georges / etc.

Items : Désigne les valeurs à l’intérieur d’un champ. Par exemple, le champ PRODUIT contient les items Papeterie, Bureau.

Champs de dimension : Les rapports de tableau croisé dynamique ont 3 dimensions, qui sont : champs de colonnes, champs de lignes, champs de page.

Zone de données : Zone contenant toutes les informations du rapport. Chaque cellule de la zone de données, à l’intersection de critères définis dans les colonnes et lignes du tableau, résume les informations répondant à ces critères dans la zone de données source.

Lorsque des champs sont ajoutés à ces zones, un nouveau nom leur est attribué et ils héritent de certains comportements.



L’Assistant Tableau croisé dynamique

Pour créer un rapport de tableau croisé dynamique, utiliser la fonction « Assistant Tableau et graphique croisés dynamiques » comme guide pour repérer et spécifier les données source à analyser et créer la structure du rapport. Vous pouvez ensuite utiliser la barre d'outils Tableau croisé dynamique pour organiser les données à l'intérieur de cette structure.

L’« Assistant Tableau et graphique croisés dynamiques » vous guide en 3 étapes lors de la création et le résultat est directement fonctionnel.

NOTE : Avant d’appeler l’Assistant Tableau croisé dynamique, retirer tous les sous-totaux et filtres afin d’utiliser la liste entière.

Procédure initiale :

1. Menu Données / Rapport de tableau croisé dynamique… Le dialogue suivant s’affiche.

Déterminer la source de données à utiliser Liste ou base de données Microsoft Excel

Données stockées dans une feuille de calcul Excel. Il peut s’agir de la feuille courante ou d’un classeur enregistré sur le disque.

Source de données externe Données stockées dans une base de données de type MS ACCESS. Lors de l’activation de cette option, MS Query est utilisé afin d’accéder à la base de données.

Plages de feuilles de calcul avec étiquettes : Données stockées dans des plages de cellules, sur une ou plusieurs feuilles de calcul. Les rapports de tableau croisé dynamique peuvent collecter des données issues de plusieurs plages de cellules et consolider les données au sein d’un seul rapport.

Vous devez, à cette étape, sélectionner le type de rapport. L’option Tableau croisé dynamique est l’option par défaut.



Emplacement des données source À la seconde étape l’utilisateur doit préciser l’emplacement des données source en utilisant la boîte de dialogue suivante.

Si une cellule est sélectionnée avant le lancement de la procédure, Excel sélectionnera automatiquement cette plage de cellules, incluant les en-têtes de colonnes.

Pour identifier un autre classeur, csur le bouton Parcourir… afin de sélectionner le classeur à ouvrir.

liquer

Emplacement du tableau croisé dynamique À la troisième étape, l’utilisateur doit préciser l’emplacement du rapport. Cette étape permet également de définir des options de table ainsi que la mise en page des champs du rapport de tableau croisé dynamique. La définition des options est facultative et peut donc être faite ultérieurement.

Dans cet exemple, la définition des options sera faite ultérieurement.

Nouvelle feuille : Nouvelle feuille de travail au sein du classeur en cours.

Feuille existante : Sélectionner une cellule du classeur en cours. La cellule sélectionnée correspond au coin supérieur gauche du rapport.

Agencer les champs d’un tableau croisé dynamique Voici l’étape décisive, celle déterminant l’organisation des données dans le rapport. Les différents champs sont représentés.

L’étape 3 de l’Assistant Tableau croisé dynamique permet de définir la disposition des champs. L’agencement peut également se faire directement dans le classeur. Nous présenterons l’étape 3 afin de définir les différentes sections du tableau croisé dynamique.

Par contre, lors de la clinique, nous définirons les champs directement dans le classeur. Cette technique est plus visuelle.



OPTION 1

Procédure :

1. Sélectionner le bouton Disposition…; La boîte de dialogue suivante s’affiche. Les zones destinées à recevoir les différents types de champs sont identifiées dans le dialogue.

2. L’utilisateur construit le tableau croisé dynamique en faisant glisser les boutons de champs localisés à la droite de la boîte de dialogue vers la zone de montage.

3. Valider (bouton OK).

DÉFINITION DES CHAMPS Champs de page Lorsqu’une valeur pour un champ de page est sélectionnée, le rapport de

tableau croisé dynamique se modifie pour ne faire apparaître que les données associées à cette valeur. Les éléments d’un champ de page sont affichés un par un dans le tableau croisé dynamique.

Exemple : EMPLOYÉ

Champs de données Ces champs contiennent les synthèses des données des éléments des champs de colonne et de ligne.

Exemple : VENTES

Champs de ligne Les éléments associés à un champ de ligne s’affichent sous la forme d’étiquettes de lignes.

Exemple : PRODUIT



Champs de colonne : Les éléments associés à un champ de colonne s’affichent sous la forme d’étiquettes de colonnes.

Exemple : MOIS

Pour supprimer des champs, glisser les champs hors de la zone représentant le rapport de tableau croisé dynamique.

OPTION 2

Afin de définir les différents champs directement dans le classeur, l’utilisateur doit omettre d’utiliser le bouton Disposition… et terminer le tableau croisé dynamique à l’étape de sélection de la destination du rapport.

Procédure :

1. Bouton Terminer.

En validant avec le bouton Terminer, l’Assistant crée la structure d’un tableau croisé dynamique vide.

Zones destinées à recevoir les différents types de champs

La barre d’outils Tableau croisé dynamique s’affiche en incluant les différents champs de la feuille de calcul.

L’utilisateur doit glisser, par l’intermédiaire de la barre d’outils Tableau croisé dynamique, les champs de données à représenter.

Les zones destinées à recevoir les différents types de champs sont marquées par des bordures bleues dans la structure du tableau croisé dynamique. Chacune porte une inscription permettant de l’identifier.



Pour notre exemple :

Champs de page : EMPLOYÉ Champs de données : VENTES

Champs de ligne : PRODUIT Champs de colonne : MOIS

LSf

1

2

3

E

45

U

Fonction SOMM

orsque des champs sont ajoutés à la section de données, une fonction de regroupement leur est associée. ’il s’agit d’un champ numérique, la fonction SOMME est associée. Pour tout autre type de données, la onction attribuée par défaut est la fonction NB.

. La fonction associée peut être modifiée en cliquant sur le champ dynamique (Champ Données) avec le bouton droit de la souris.

. Sélectionner l’option Champ… Vous pouvez également modifier la fonction associée en double cliquant sur « SOMME » du tableau croisé dynamique.

. Le dialogue « Champ Pivot Table » s’affiche, permettant de sélectionner une fonction spécifique.

. Sélectionner la nouvelle fonction de synthèse.

. Valider, bouton OK.

niversité de Montréal Tableau croisé dynamique page 36


FONCTIONS DISPONBLES EN MODE TABLEAU CROISÉ DYNAMIQUE

FONCTION RÉSULTAT

SOMME Somme des valeurs. Fonction de synthèse par défaut pour les champs de données de valeurs numériques.

NBVAL Nombre d’enregistrements. Fonction de synthèse par défaut pour les champs de données de valeurs non numériques.

MOYENNE Moyenne des valeurs MAX Donnée la plus élevée. MIN Donnée la moins élevée. PRODUIT Multiplication des valeurs. NB Nombre d’enregistrements contenant des données de valeurs numériques.

ECARTYPE Estimation de l’écart type d’un échantillon d’une population, les données sous-jacentes étant l’échantillon.

ECARTYPEP Estimation de l’écart type d’une population entière, les données sous-jacentes étant la population entière.

VAR Évaluation de la variance (manière dont un échantillon dévie de la moyenne) d’un échantillon de population.

VARP Évaluation de la variance (manière dont un échantillon dévie de la moyenne) d’une population entière.



BARRE D’OUTILS TABLEAU CROISÉ DYNAMIQUE

Lz

L

U

OUTILS
CHAMPS
a barre d’outils Tableau croisé dynamique se divise en deux zones distinctes : zone « OUTILS » et one « CHAMPS ».

a seconde zone, zone « CHAMPS », regroupe les noms de champs utilisés dans la source de données.

OUTIL NOM DESCRIPTION

Menu Tableau croisé dynamique Liste des commandes de menu Tableau croisé dynamique les plus fréquemment utilisées.

Mettre en forme le rapport Format automatique : permet de sélectionner

des formats prédéfinis de mise en forme.

Assistant graphique Permet de créer un graphique croisé

dynamique relié au rapport en cours.

Assistant Tableau croisé dynamique Permet d’accéder à la boîte de dialogue

Assistant tableau croisé dynamique.

Masquer Masque les informations relatives à un champ.

Afficher Affiche les informations relatives à un champ.

Actualiser les données Mise à jour des modifications apportées aux

données sources.

Paramètres de champs Permet d’accéder à la boîte de dialogue « Champ de tableau croisé dynamique » pour le champ sélectionné.

Masquer / Afficher les champs Permet d’Afficher / Masquer la zone Noms de champs de la barre d’outils Tableau croisé dynamique.

niversité de Montréal Tableau croisé dynamique page 38


CALCULS MULTIPLES SUR UN CHAMP

Le tableau croisé dynamique permet de synthétiser plus d’un calcul pour fin d’étude.

Pour ajouter une fonction :

1. Positionner le curseur à l’intérieur de la zone de données; La barre d’outils Assistant tableau croisé dynamique s’affiche.

2. Glisser une seconde copie du champ de données vers la zone de données;

3. Avec le bouton droit

de la souris, cliquer sur une cellule de la seconde synthèse et sélectionner la commande Champs... ou Sélectionner le bouton Champs de la barre d’outils Tableau croisé dynamique; Le dialogue Champs Pivot Table s’affiche.

4. Sélectionner une fonction; 5. Pour faciliter la lecture, renommer le

nom du champ dans la zone Nom.



Pour retirer un champ 1. Positionner le curseur à l’intérieur de la zone de données;

La barre d’outils Assistant tableau croisé dynamique s’affiche. 2. Cliquer sur le bouton Assistant Tableau croisé dynamique de la barre d’outils;

La boîte de dialogue Étape 3 s’affiche.

3. Activer le bouton Disposition…; 4. Glisser le champ à retirer

à l’extérieur de la zone de données;

5. Valider.



MASQUER / AFFICHER LES VALEURS D’UN CHAMP

Pour fin de synthèse et de lecture, la fonction Tableau croisé dynamique permet de masquer des données à l’écran et à l’impression.

Pour masquer certaines valeurs des champs de colonnes ou lignes :

1. Cliquer sur la flèche située à la droite de l’en-tête du champ; Une liste s’affiche à l’écran. Chaque élément marqué d’une croix est affiché à l’écran.

2. Désactiver la case à cocher de l’élément à masquer; 3. Valider, bouton OK.

Pour masquer certaines valeurs des champs de pages :

1. Cliquer sur la flèche située à la droite de l’en-tête du champ « Page »; Une liste s’affiche à l’écran.

2. Sélectionner le champ à afficher; 3. Valider, bouton OK.

Le champ « Page » permet d’afficher toutes les pages ou une page unique de champ. Il est donc impossible d’afficher 2 champs de page sur un total de 3.



MISE À JOUR DES DONNÉES

Les données du Tableau croisé dynamique sont directement liées aux données sources. Par contre, la modification des données source ne se répercutent pas directement dans le rapport. L’utilisateur doit forcer la mise à jour du rapport si la fonction Mise à jour automatique n’est pas activée. Ce dernier paramètre n’est pas un paramètre par défaut. Il doit être activé par l’utilisateur.

Mise à jour manuelle 1. Apporter les modifications aux données sources; 2. Activer le tableau croisé dynamique en sélectionnant une cellule quelconque du tableau; 3. Cliquer sur le bouton Actualiser de la barre d’outils Assistant Tableau croisé

dynamique.

Mise à jour automatique 1. Activer le tableau croisé dynamique en sélectionnant une cellule quelconque; 2. Cliquer sur le bouton « Tableau croisé dynamique de la barre d’outils »; 3. Sélectionner la commande « Option de la table… »; 4. Cocher la case « Actualiser lors de l’ouverture ».

SUPPRIMER UN TABLEAU CROISÉ DYNAMIQUE Lorsque le tableau croisé dynamique est supprimé, les données sources ne sont pas concernées par cette opération.

Procédure :

1. Sélectionner tout le tableau croisé dynamique en incluant les titres; 2. Menu Édition / Effacer / Tout.



Mise en forme du tableau croisé dynamique

Pour la mise en forme du rapport, l’utilisateur peut utiliser les techniques de mise en forme propres à la feuille de calcul de Microsoft Excel. Éviter toutefois de définir des mises en forme pour des cellules isolées car, à l’occasion des réorganisations du rapport ou d’une mise à jour, ces cellules peuvent être déplacées de manière imprévisible, et le résultat peut être erroné.

Le tableau croisé dynamique possède une commande de mise en forme automatique. Cette technique est simple et efficace.

Procédure :

1. Positionner le curseur dans le tableau croisé dynamique;

2. Cliquer sur le bouton « Mettre en forme le rapport » de la barre d’outils Tableau croisé dynamique. La fenêtre suivante s’affiche.

3. Sélectionner la mise en forme désirée; 4. Valider. Pour mettre en forme les nombres du tableau croisé dynamique 1. Positionner le curseur dans la colonne de champs à définir; 2. Cliquer sur le bouton « Paramètres de champ » de la barre d’outils

Tableau croisé dynamique. 3. Dans la fenêtre affichée, sélectionner le bouton

« Nombre… »; 4. Dans le dialogue « Format de cellule », onglet Nombre,

sélectionner la catégorie et le format d’affichage requis. Toutes les données qui font partie du champ de la cellule sélectionnée sont mises en forme. Lorsque plusieurs champs sont présents dans le rapport, chacun des champs doit être mis en forme séparément.



Grouper et dissocier des enregistrements

L’une des fonctions principales des rapports de tableau croisé dynamique consiste à rassembler des enregistrements traitant du même sujet et de les grouper dans un ensemble logique. L’utilisateur peut créer des groupes manuellement dans le but de faciliter la lecture.

Les données peuvent être groupées par jour, semaine, mois ou par tout autre intervalle. Les nombres peuvent également être groupés.

Les exemples seront faits à partir du tableur suivant (LIBRAIRIE.XLS) :

GROUPER DATES ET HEURES La plupart des bases de données utilisent un seul champ afin de stocker le format jour/mois/année. En groupant ce type de données, l’utilisateur peut sectionner le rapport en mois ou trimestre.

Procédure :

1. Créer un tableau croisé dynamique affichant les dates dans une colonne;

2. Positionner le pointeur sur le champ à grouper, afficher le menu contextuel;

3. Sélectionner la commande Grouper et créer un plan / Grouper…;

Le dialogue de la page suivante s’affiche.



4. Inscrire la date de DÉBUT du groupe. Par défaut, la première date s’affiche.

5. Inscrire la borne supérieure du groupe, date de FIN. Par défaut, la dernière date s’affiche dans la zone de saisie.

6. Dans la section « Par », utiliser la liste déroulante afin de déterminer l’intervalle à utiliser.

Si l’intervalle JOURS est sélectionné, préciser le nombre de jours à considérer pour l’intervalle. Pour l’intervalle SEMAINE, simuler cet intervalle en déterminant JOURS / Nombre : 7.

L’exemple suivant permettra de grouper les dates par TRIMESTRE.

GROUPER LES NOMBRES

Afin de grouper de créer un groupe en fonction des nombres, il faut avant tout créer le tableau croisé dynamique en fonction des champs.

EMPLOYÉ / MOIS / COMMISSION / VENTES

Procédure :

1. Créer un tableau croisé dynamique affichant les champs requis;



2. Positionner le pointeur sur le champ à grouper, afficher le menu contextuel; 3. Sélectionner la commande Grouper et créer un plan / Grouper…;

Le dialogue suivant s’affiche. 4. Inscrire les limites des valeurs à grouper, zone DÉBUT / FIN du

groupe. Possibilité de garder les valeurs affichées par défaut. 5. Dans la zone « par : » : déterminer la longueur de l’intervalle à

utiliser.

Exemple : 10000

DISSOCIER LES GROUPES

Procédure :

1. Pointer une cellule du rapport tableau croisé dynamique; 2. Menu contextuel : Grouper et créer un plan / Dissocier.



Création de champs calculés

Les champs calculés utilisent les fonctions et opérateurs standards disponibles dans une feuille de calcul Excel. Par contre, il est impossible d’utiliser des données extérieures à la source de données.

Exemple :

Le tableau suivant permettra de calculer les bénéfices après retrait des commissions remises aux employés. La commission a été calculée précédemment dans la base de données. Celle-ci aurait pu être intégrée à partir du tableau croisé dynamique. L’exemple suivant calculera donc une commission de 15 % des ventes et soustraira ce montant des ventes globales. Ainsi, les bénéfices seront calculés à l’intérieur du tableau croisé dynamique.

Pour construire le rapport suivant :

Champs Pages : EMPLOYé

Champs Lignes : PRODUIT

Champs Colonnes: MOIS

Zone de données : VENTES

La moyenne des ventes a également été affichée mais n’aura aucun impact sur la création de champs calculés.

Pour créer un champ calculé :

1. Sélectionner une cellule à l’intérieur du rapport; 2. Bouton Tableau croisé dynamique / Formules

/ Champ calculé;



Le dialogue Insertion d’un champ calculé apparaît.

3. Zone Nom : Saisir le nom pour le champ calculé; Exemple : APRÈS COMMISSION

4. Zone Formule : Saisir la formule. Pour

utiliser un champ, sélectionner le champ dans la section Champs et cliquer sur le bouton Insérer un champ.

Exemple : champ VENTES-(champ VENTES * 15%)

5. Bouton AJOUTER; 6. Bouton OK.

Pour supprimer un champ calculé :

1. Sélectionner une cellule à l’intérieur du rapport; 2. Bouton Tableau croisé dynamique / Formules / Champ calculé;

Le dialogue Insertion d’un champ calculé apparaît. 3. Zone Nom : dans la liste déroulante, sélectionner le

nom du champ calculé à supprimer; 4. Bouton Supprimer; 5. Valider.



Graphique Tableau croisé dynamique

Les graphiques croisés dynamiques représentent une forme particulière de graphique lié au contenu du rapport. Il offre une possibilité additionnelle d’analyse des données. Le graphique peut être créé à partir d’un rapport existant ou il peut être généré au même moment que le tableau croisé dynamique qui lui sera associé. Le graphique croisé dynamique est interactif.

Pour l’exemple, nous utiliserons le classeur : LIBRAIRIE.XLS.

CRÉER UN GRAPHIQUE CROISÉ DYNAMIQUE À PARTIR D’UN TABLEAU CROISÉ DYNAMIQUE Prenons l’exemple de tableau croisé dynamique suivant :

Procédure :

1. Positionner le curseur dans une cellule du tableau croisé dynamique;

2. Cliquer sur le bouton « Assistant graphique » de la barre d’outils Tableau croisé Dynamique;

3. Excel affiche le graphique initial sur une nouvelle feuille identifiée GRAPH1.

4. De la barre d’outils Tableau croisé dynamique, glisser les champs, si nécessaire, permettant de bien identifier les données à représenter.

5.

Il est également possible de modifier la position des champs dans le graphique en les glissant vers de nouvelles zones de champs. Ces manipulations se répercutent directement dans le tableau croisé dynamique.

Champs

4. La barre d’outils Graphique s’affiche à l’écran.



Utiliser les différentes informations disponibles pour modeler le Graphique croisé dynamique.

Le menu contextuel permet également la mise en forme des éléments et le choix de différents types de graphique.

NOTE :

La modification du type de graphique de séries de données isolées n'est pas conservée dans les rapports de graphiques croisés dynamiques lorsque l'affichage du graphique ou du rapport de tableau croisé dynamique qui lui est associé est modifié par la suite. Le même principe s’applique pour la mise en forme appliquée à une série de données. Avant de modifier le type de graphique d'une ou plusieurs séries de données ainsi que la mise en forme dans un rapport de graphique croisé dynamique, vérifier que la disposition est un produit final.



CRÉER SIMULTANÉMENT UN GRAPHIQUE CROISÉ DYNAMIQUE ET UN TABLEAU CROISÉ DYNAMIQUE

Procédure :

1. Sélectionner une cellule de la liste contenant les données sur lesquelles le rapport portera; 2. Menu Données / Rapport de tableau croisé dynamique; 3. Préciser la source de données; 4. Section « Quel type de rapport voulez-vous

créer… », sélectionner Graphique croisé (avec le tableau);

5. Préciser l’emplacement des données source. 6. Préciser l’emplacement du tableau croisé

dynamique. Le graphique lié sera automatiquement placé sur une nouvelle feuille GRAPH. Le tableau croisé dynamique est déposé à la suite de la feuille GRAPH.

7. Le tableau et le graphique se créent simultanément.

8. Il est possible de construire dynamiquement les graphiques croisés dynamiques en faisant glisser les en-têtes de champ, depuis la barre d’outils Tableau croisé dynamique vers l’axe de position nécessaire.


Module 4 Outils de rapidité - Efficacité

Mise en forme conditionnelle

Création de zone de liste déroulante

Création de modèle de classeur Création d’un modèle de document Modifier un modèle Utiliser un modèle que vous avez créé Protection de données

Manipulation des barres d’outils Personnaliser une barre d’outils

Macros : automatisation des tâches de routine Enregistrement d'une macro Exécution rapide d'une macro Exécution d'une macro à partir d'un bouton de barre d'outils Supprimer une macro Niveau de sécurité des macros

PRATIQUE

Fonctions : SI et RECHERCHE

Facture : LIBRAIRIE.XLS


Mise en f itionnelle orme cond

Si une cellule contient les résultats d'une formule ou d'autres valeurs de cellule que vous souhaitez mettre en évidence, vous pouvez identifier les cellules en appliquant

orme particulières selon des conditions établies.

vez appliquer un surlignage vert à la cellule si les notes sont supérieures à 60 % et un surlignage rouge dans le cas opposé.

Change

des mises en f

Par exemple, vous pou

ment des conditions

Si la valeur de la cellule change et qu'elle ne remplit plus les conditions spécifiques, Microsoft Excel annule surlignent cette condition. Les mises en forme

s jusqu'à ce que vous les supprimiez, même si aucune s mises en forme de cellules ne sont pas affichées.

isé dynamique

momentanément les mises en forme qui conditionnelles restent appliquées aux cellulecondition n'est remplie et que le

Classeurs et rapports de tableau cro

rme conditionnelles appliquées avant le partage du classeur sont odifier les mises en forme conditionnelles existantes ni

tez d'appliquer des mises en forme conditionnelles aux cellules d'un us risquez d'obtenir des résultats imprévisibles.

s spécifiques

Dans un classeur partagé, les mises en fotoujours valides. Toutefois, vous ne pouvez pas men appliquer de nouvelles. Si vous tenrapport de tableau croisé dynamique, vo

Mise en évidence des cellules remplissant des condition1. Sélectionner les cellules à mettre en évidence;

forme conditionnelle; . Effectuer l'une des actions suivantes :

A. Pour utiliser des valeurs situées dans les cellules sélectionnées comme critères de mise en forme :

1. Sélectionner « La valeur de la cellule est »;

2. Sélectionner un type de comparaison;

3. Saisir une valeur dans la zone appropriée. Vous pouvez taper une valeur constante ou une formule. Si vous tapez une formule, vous devez la faire précéder d'un signe égal (=).

2. Menu Format, cliquer sur Mise en 3

Université de Montréal Outils de rapidité - Efficacité page 53


B. Pour utiliser une formule comme nnées sur une condition autre que les valeurs s

1. Clique2. Saisir l

VRAI

uées. Si la valeur moyenne de la plage $A$1:$A$5 est supérieure à 3 000 et que les valeurs de la plage sont

un ombrage rouge / s cellules mises en forme peuvent contenir du texte ou nt pas utilisées dans la formule.

critère de mise en forme (pour évaluer des doituées dans les cellules sélectionnées) :

r sur « La formule est » dans la liste située à gauche; a formule dans la zone située à droite. La formule doit posséder une valeur logique

ou FAUX comme résultat.

Exemple : Utilisez une formule et des références de cellule externes

Dans l'exemple suivant, une formule considère les données situées en dehors des cellules sélectionnées auxquelles les mises en forme conditionnelles sont appliq

supérieures ou égales à 1 800, la formule renvoie la valeur VRAI et double soulignement est appliqué. Leune valeur quelconque, car elles ne so

MIN($A$1:$

LA FORMULE EST FORMAT

Condition 1 =ET(MOYENNE($A$1:$A$5)>3000; OmbragA$5)>=1800)

e de cellule rouge / double soulignement

4. Cliquer sur le bouton Format;

. Sélectionner le style et la couleur de police, le soulignement, les bordures, l'ombrage ou les motifs à appliquer. Microsoft Excel applique les mises en forme sélectionnées uniquement si la valeur de cellule remplit la condition ou si la formul

5

e renvoie une valeur VRAI.



6. . Il est possibleaucune cellules conse tes. Vous pouvez utiliser ces dernières pour identifier une quatrième condition.

Copie de formats vers d'autres cellules

Pour ajouter une autre condition, cliquer sur le bouton Ajouter, puis répéter les étapes précédentes

de spécifier jusqu'à trois conditions. Si des conditions spécifiées n'est vraie, les

rvent leurs mises en forme existan

1. Sélectionner les cellules dotées des mises en forme conditionnelles à reproduire; 2. Barre d'outils Mise en forme, cliquer sur Reproduire la mise en forme; 3. Sélectionner les cellules à mettre en forme.

Copier uniquement les mises en forme conditionnelles

ner les cellules à mettre en forme en incluant au moins une cellule dans la sélection ayant les formes conditionnelles à copier; rmat / Mise en forme conditionnelle; ur le bouton OK.

1. Sélectionmises en

2. Menu Fo3. Cliquer s

MODIFICAT N, AJOUT OU SUPPRESSION E CONDITION

IO DE MISES EN FORMNELLES

1. Sélectionner la cellule à modifier ou supprimer les mises en forme conditionnelles; 2. Menu Format / Mise en forme conditionnelle; 3. Modifier l'opérateur, les valeurs, la formule ou les

tion. Pour modifier les mises en forme, cliquer sur

le bouton Format correspondant à la condition à modifier.

Pour sélectionner à nouveau des mises en forme sous l'onglet en cours de la boîte de dialogue Format de celluleEffacer.

Pour ajouter une nouvelle condition, cliquer sur le bouton Ajouter de la boîte d

ur le bouton er (de la boîte de dialogue Mise en forme conditionnelle),

ns à supprimer.

Pour supprimer toutes les mises en forme conditionnelles ainsi que l'ensemble des autres mises en forme de cellule, menu Édition / Effacer / Formats.

mises en forme de chaque condi

, cliquer sur le bouton

e dialogue Mise en forme conditionnelle.

Pour supprimer une ou plusieurs conditions, cliquer sSupprimpuis activer la case à cocher correspondant aux conditio

NOTE :



Création d'une série de recopie incrémentée

Il est possible de créer une liste de recopie personnalisé

Procédure :

e répondant à un besoin spécifique de l’utilisateur.

1. Si la liste des éléments constituant la série est saisiecalcul;

, sélectionner les éléments dans la feuille de

2.

deux. Pour créer

Menu Outils / Options / onglet Listes pers.;

3. Pour utiliser la liste sélectionnée, cliquer sur le bouton Importer.

REMARQUE : Une liste personnalisée peut contenir du texte, des chiffres ou lesune liste personnalisée ne contenant que des chiffres, par exemple des numéros de pièces détachées, sélectionner autant de cellules vides que nécessaire. Dans le menu Format, cliquer sur Cellule, puis sur l'onglet Nombre. Appliquer le format Texte aux cellules vides puis saisir la liste des nombres dans les cellules mises en forme.

Pour saisir une liste par la commande Liste personnalisée :

1. Nou

2. Sai ent

3. App aque élément; 4.

bou

Dans la zone Listes personnalisées, sélectionnvelle liste;

sir les élém

er

s dans la zone Entrées de la liste, en commençant par le premier;

uyer sur ENTRÉE après chLorsque la liste est complétée, cliquer sur le

ton Ajouter.



Création de zone de liste déroulante

Une zone de liste modifiable est une zone de liste déroulante.

Exemple de création de zones de liste déroulante

Procédure de création :

1. Saisir les données à insérer dans la zone de liste; Cette liste doit toujours être contenue dans le classeur. L’utilisateur pourra masquer cette colonne ou infeuille du classeur afin que ces données ne sooù sera créée la zone de liste modifiable.

. Afficher la barre d’outils Formulaire (menu Affichage / Barre d’outils /

. Cliquer sur le bouton Zone de liste modifiable

sérer les données de référence dans une autre ient pas affichées dans la feuille

2Formulaire);

3 ;

tout en dessinant la zone de

e la souris (menu contextuel);

Le pointeur prend la forme d’une croix. 4. Cliquer et maintenir le bouton de la souris enfoncé

liste; 5. Cliquer sur la zone en utilisant le bouton droit d

Une liste s’affiche. 6. Sélectionner « Format de contrôle »;



r ;

Le dialogue suivant

permettant de sélectionner les données dans le classeu

8. à insérer dans la zone de liste;

9.

ropriétés de contrôle pour les zones de liste

s’affiche.

7. Dans la zone « Plage d’entrée », cliquer sur le bouton

Sélectionner les données

Valider avec la touche ENTRÉE.

P

Propriété Description

Plage d'entrée Fait référence à une liste de valeurs sur une feuille de calcul. Cette plage fournit les valeurs figurant dans la liste déroulante.

Cellule liée Cellule renvoyant une valeur représentant l'élément sélectionné dans la liste. Ce numéro peut être utilisé dans une formule pour renvoyer un résultat basé sur

iste.

ne de liste est liée à la cellule C1 et que la plage d'entrée de la plage D10:D15 en

se basant sur la sélection dans la liste :

l'élément sélectionné dans la l

Par exemple, si une zola liste est D10:D15, la formule suivante renvoie la valeur de

=INDEX(D10:D15,C1)

Lignes Spécifie le nombre de lignes à afficher dans la liste déroulante.



Création de modèle de classeur

Un modèle est un classeur contenant des feuilles, du texte p étiquettes de ligne et de colonne), des formules, des mac ar défaut (par exemple des en-têtes de page et

des ros, des styles, et d'autres mises en forme. utrement dit, un modèle est doté d’une structure particulière servant de base à la création de classeur équemment utilisé tels que facturation, calcul de notes, etc. L’emploi d’un modèle permet d’éviter ertaines opérations répétitives et d’assurer une présentation uniforme des classeurs.

xemple de modèle avec formules intégrées, mise en forme, protection de cellules.

Afrc

E

NOTE : L’affichag

Pour désactiver l’a

Liste déroulante

Formule SI et RECHERCHEV

e quadrillage a été désactivé.

ffichage du quadrillage à l’écran :

1. Menu Options… / Affichage / section Fenêtres / désactiver la case à cocher Quadrillage.



eau document en cliquant sur (Nouveau) de laLorsque vous créez un nouv barre d’outils Standard, le classeur est rattaché au modèle par défaut. Autrement dit, le document dispose de tous les éléments

RÉATION D UN MODÈLE DE DOCUMENT

(paramètres) propres au modèle de démarrage de Microsoft Excel.

Pour utiliser un autre modèle, l’utilisateur doit utiliser le menu Fichier / Nouveau… et sélectionner le modèle à utiliser.

C ’ Comme mentionné précédemment, un modèle peut contenir du texte, des paramètres de mise en forme et de mise en page, des formules, des macros, etc.

Il existe deux façons de créer un modèle :

À partir d’un document (nouveau ou existant); À partir d’un modèle existant.

Procédure :

1. Insérer les formules requises, appliquer les mises en forme de cellules et de classeur selon les besoins. Il est également possible de masquer des feuilles et/ou lignes et colonnes. Selon le but de la création du modèle, l’utilisateur devra utiliser les différentprotection de feuilles et/ou de classeur décrites dans les pages suivantes, sous la mention PROTECTION.

2. Menu Fichier / Enregistrer sous…;

3. Dans la zone « Type de fichier », sélectionner MODÈLE DE DOCUMENT (*.xlt).

. Dans la zone Enregistrer dans, sélectionner le dossier dans lequel vous souhaitez stocker le modèle. Pour créer le modèle de classeur par défaut, sélectionner le dossier XLOuvrir (XLStart) :

C:\Documents and Settings\LOGIN\Application Data\Microsoft\Excel\XLOuvrir

es commandes de

4


(XLStart)

Pour créer un modèle de classeur personnalisé, sélectionner le dossier Modèles :

C:\Documents and Settings\LOGIN\Application Data\Microsoft\Modèle


5. Dans la zone Nom de fichier :

valide.

Pour afficher une image de la première page du modèle dans la zone Aperçu de la boîte de dialogue

nregistrer l'image de

MODIFIER UN MODÈLE

Saisir le nom « classeur » pour créer un modèle de classeur par défaut. Pour créer un modèle personnalisé, saisir un nom de fichier

CONSEIL :

1. Menu Fichier / Propriétés; 2. Cliquer sur l'onglet Résumé; 3. Activer la case à cocher E

l'aperçu.

Ainsi, un aperçu partiel du modèle sera disponible dans la zone Aperçu.

Procédure :

1. Ouvrir le modèle en utilisant la commande Ouvrir; 2. Sélectionner le dossier contenant le modèle à modifier et sélectionner le modèle à modifier.

Le processus est le suivant :

C:\Documents and Settings\LOGIN\Application Data\Microsoft\Modèle

3. Apporter les modifications requises; . Enregistrer le classeur. 4



UTILISER UN MODÈLE QUE VOUS AVEZ CRÉÉ Lorsque vous avez besoin d’utiliser un modèle :

1. Sélectionner le menu Fichier / Nouveau;

2. Sélectionner lutiliser (ongle

3. Double-clique4. Saisir les informat

. Enregistrer le nouveau document.

La fenêtre suivante apparaît : ’onglet contenant le modèle à t Général);

r sur le modèle; ions;

5



Protection de données

La er que les documents soient déplacés, supprimés, alable, par exemple, si l’utilisateur crée des modèles qui

seront utilisés par plusieurs utilisateurs. Selon le type de protection, le déplacement du curseur ne sera dép ifier. Pour cette procédure, il sera important de déterminer les cellules

Pre turation et/ou inventaire pour la Librairie de l’Université de

protection de feuille et/ou classeur permet d’évitmasqués, modifiés, etc. Cette technique est v

lacé que sur les cellules à modqui ne seront pas protégées.

nons l’exemple de facMontréal.

loppée sur une feuille Excel. Les différentes formules sont générées Ce type de production demandera, entre autre, de générer des formules de et de condition (SI).

es données variables seront les numéros d’items (CODE BARRE), les quantités vendues, la date du ur. Les formules sont invariables ainsi que la mise en forme. L’utilisateur pourrait également interdire copie et l’insertion de feuille dans le même classeur. Chaque nouvelle entrée d’inventaire serait alors énérée dans un nouveau classeur. La technique du modèle sera alors à considérer.

PROTECTION DE CELLULE(S)

La structure de la facture est dévedans les cellules requises. recherche (RECHERCHEV)

Ljolag

1. Sélectionner les cellules à ne pas verrouiller; 2. Menu Format / Cellules… / onglet Protection; 3. Désactiver la case à cocher Verrouiller.

NOTE : Le verrouillage des cellules prendra effet seulement lorsque la feuille sera protégée.



PROTECTION DE FEUILLE(S) La protection de feuille ne verrouille que la feuille sélectionnée. L’utilisateur peut insérer, supprimer et copier les feuilles du classeur. Il est également possible de masquer, afficher des feuilles.

La protection du classeur ne permet pas les manipulations énumérées précédemment.

Procédure:

1. Sélectionner les feuilles à protéger; 2. Menu Outils / Protection;

confirmer le mot de passe. Inscrire de nouveau le mot de passe. Le à 15 caractères. Les caractères

péciaux.

3. Sélectionner Protéger la feuille…; La fenêtre suivante s’affiche.

4. Activer les cases à cocher requises; 5. Saisir un mot de passe. (facultatif).

Si un mot de passe est indiqué, une boîte de dialogue demande de

mot de passe peut contenir jusqu’utilisés : lettres, chiffres, espaces, caractères s

Éléments à protéger Description

Contenu Empêche les modifications des cellules des feuilles de calcul et des éléments de graphique.

Objets Empêche la suppression, le déplacement, la modification ou le redimensionnement des objets graphiques dans une feuille de calcul ou dans une feuille graphique par d’autres utilisateurs.

Scénarios Empêche la modification des définitions de scénarios dans une feuille de calcul.

Ôter la protection

1. Menu Outils / Protection / Ôter la protection… Si un mot de passe a été définit, saisir ce mot de passe.



PROTECTION DE CLASSEUR La protection de feuille ne verrouille que la feuille sélectionnée. L’utilisateur peut insérer, supprimer et copier les feuilles du classeur. Il est également possible de masquer, afficher des feuilles.

La protection du classeur ne permet pas d’insérer, supprimer et copier les feuilles du classeur. Il est donc impossible de masquer, afficher des feuilles, etc.

Procédure:

1. Ouvrir le classeur à protéger; le classeur…;

4.

e ctères

tilisés : lettres, chiffres, espaces, caractères spéciaux.

2. Menu Outils / Protection / Protéger

La fenêtre suivante s’affiche.

3. Activer les cases à cocher requises; Saisir un mot de passe. (facultatif). Si un mot de passe est indiqué, une boîte de dialogue demande de confirmer le mot de passe. Inscrire de nouveau le mot de passe. Lmot de passe peut contenir jusqu’à 15 caractères. Les carau

Éléments à protéger Description

Structure E nts soient déplacés, supprimés, affichés, masqués et renommés dans le classeur. Il est également impossible d’ajouter une n

mpêche que les docume

ouvelle feuille de calcul dans le classeur.

mpêche que les fenêtres soient déplacées, agrandiesFenêtres E ou réduites, affichées ou masquées.

Ôter la protection

1. Menu Outils / Protection / Ôter la protection du classeur. sse.

Si un mot de passe a été définit, taper ce mot de pa



Manipulation des barres d’outils

Excel fournit de nombreuses barres d'outils intégrées permettant l’accès instantané aux commandes les plus utilisées. Chacune des barres propose des boutons afin de permettre d’accéder plus rapidement à une

Par défaut, les barres d’outils Standard et Mise en forme sont ancrées côte à côte, en dessous de la

De plus, les menus affichent les commandes de base ainsi que les dernières commandes utilisées. Les autres commandes s’affichent après un cours délai ou lorsque l’utilisateur clique sur les flèches localisées au b

andard et Mise en forme l’une au-dessus de l’autre et afficher

fonction fréquemment utilisée. Initialement, Excel affiche les barres d’outils Standard, Mise en forme et Dessin.

barre des menus.

as des menus.

Afin d’ancrer les barres d’outils Stles commandes en permanence dan

e barre d’outils peut elle est représentée

s les menus :

1.

Menu Outils / Personnaliser…; . Onglet Options; . Désactiver les cases à cocher :

Afficher les barres d’outils Standard et Mise me

r en haut des mndes utilisées;

4. Bouton Fermer.

Vous pouvez afficher simultan s barres d’outils. Chaquet/ou positionnée à un endroit précis sur la fenêtre Excel. Chacune d’

nêtre ou d’une barre placée soit en haut, en bas, à gauche ou à droite de la fenêtre Word.

Pour accéder aux barres d’outils :

Menu Affichage / Barre d’outils / Personnaliser…; ou

23

en forme sur la mê ligne Affiche

commaenus les dernières

ément plusieur être modifiée sous forme de

la souris sur

fe

1. res d’outils; e

. Activer ou désactiver une barre d’outils en cliquant sur celle-ci.

Menu Affichage / Bar(Le menu contextuel offre la même possibilité : bouton droit dun espace libre d’une barre d’outils.)

2



Lorsqu’une barre d’outils e e de la fenêtre Excel, elle débute toujours par le pictog

st positionnée en haut, en bas, à gauche ou à droitramme suivant :

Déplacer une barre d’outils :

1. Cliquer sur le pictogramme et maintenir le bouton de la souris enfoncé; 2. Glisser vers la destination désirée.

tils est ancrée lorsqu’elle est placée sur un côté de la fenêtre ou flottante dans les

Si la barre d’outils est flottante, elle affiche une barre de titre qui permet de la déplacer ainsi que de la redimensionner à l’aide de ses côtés (bordures).

PER

Une barre d’ouautres cas.

SONNALISER UNE BARRE D’OUTILS

d’ajou

…;

Vous pouvez modifier une barre d’outils existante afinbou

er les barres d’outils :

ter, réorganiser, supprimer ou modifier des tons.

Pour personnalis

1. ils… / Personnaliser

déroulante, faire afficher les commandes disponibles;

5. Cliquer sur le bouton de votre choix et le glisser vers son emplacement prévu dans la barre d’outils.

Menu Affichage / Barres d’outLa fenêtre suivante s’affiche :

2. Sélectionner l’onglet Commandes; La fenêtre suivante s’affiche.

3. Dans la zone Catégorie, sélectionner la catégorie contenant les boutons que vous désirez ajouter dans la barre d’outils;

4. Dans la zone Commandes, à l’aide de la liste



Pour supprimer un bouton de la barre d’outils :

1. Cliquer sur celui-ci et le glisser à l’extérieur de la barre d’outils; Le bouton disparaît.

2.

PERSONNALISÉE

Activer le bouton Fermer.

RÉTABLIR UNE BARRE D’OUTILS

1. r…;

2. liquer sur l’onglet Barre d’outils; 3. 4.

Le dialogue suivant apparaît. 5. Valider, bouton OK.

Menu Affichage / Barres d’outils… / PersonnaliseCCliquer sur la barre d’outils à rétablir; Choisir le bouton Rétablir…



Macros : automatisation des tâches de routine

Si vous effectuez fréquemment une tâche dans Microsoft Excel, il est possible d'automatiser cette tâche ne macro est une série de commandes et de fonctions, stockées dans un

module Visual Basic, qui peut être exécutée chaque fois que l’utilisateur doit accomplir cette tâche. Lors relatives à chaque commande effectuée.

macro est ensuite exécutée afin qu'elle répète, ou « jou , les commandes. Une macro peut être bouton de commande, etc.

par la création de « Macro ». U

de l’enregistrement d’une macro, Excel stocke les informationsLa e »attribuée à un raccourci clavier, un bouton de barre d’outils, un

ENREGISTREMENT D'UNE MACRO

Avant d'enregistrer ou d'écrire une macro, planifier les étapes et les commandes que la macro doit ise pendant l'enregistrement de la macro, les corrections apportées sont

éga ’enregistrement d’une macro, Visual Basic la stocke dans un nouveau r.

xemple de macro :

effectuer. Si une erreur est commlement enregistrées. Lors de l

module attaché au classeu

E

i vous entrez fréquemment de longues chaînes de texte dans certaines cellules afin de générer des titres, ous pouvez enregistrer une macro permettant de mettre en forme ces cellules en utilisant le renvoi à la gne, centrer horizontalement et verticalement.

Pour appliquer cette mise en forme, les étapes sont les suivantes :

Svli

. Sélectionner une cellule dans laquelle vous souhaitez que le texte soit renvoyé à la ligne;

. Menu Format / Cellule / onglet Alignement;

. Sélectionner Alignement du texte : Horizontal et Vertical : Centré

4. Activer la case à cocher Renvoyer à la ligne automatiquement; 5. Valider.

123



Procédure de création de la macro 1. Sélectionner une cellule qui comportera la mise en forme désirée. 2. Menu Outils / Macro / Nouvelle Macro…;

commencer par une lettre e lettres, chiffres ou caractère de espaces ne sont pas autorisés

La fenêtre suivante s’affiche.

3. Dans la zone Nom de la macro, taper le nom de la macro. Le nom de la macro doitet peut être suivi dsoulignement. Lesdans un nom de macro. Pour exécuter la macro en appuyant sur une touche de « raccourci clavier :

4.

touche MAJ + caractère. dans : », cliquer sur l'emplacement où vous souhaitez stocker

Dans la zone « Touche de raccourci », combiner la touche CTRL et une lettre. La touche de raccourci clavier ne peut être ni un chiffre ni un caractère spécial. Pour utiliser la majuscule, combiner la

5. Dans la zone « Enregistrer la macrola macro.

NOTE :

Afin que la macro soit disponible à tout moment lors de l'utilisation d'Excel, stocker la macro dans « Classeur de macros personnelles » situé dans le dossier de démarrage d'Excel.

Pour exécuter la macro lors de l’utilisation d’un classeur en particulier, stocker la macro dans « Ce classeur ». Un exemple d’enregistrement dans un fichier spécifique serait lors de l’utilisation d’un modèle.

6. Pour ajouter une description de la macr la zone « Description ». 7. Cliquer sur le bouton OK. 8. La barre d’outils Enregistrement s’affiche.

o, taper la description dans

Universi

Arrêter l’enregistrement

té de Montréal Outils de rapidité - Efficacité

Référence relative / Référence absolue

page 70


Si vous sélectionnez des cellules pendant l'exécution d'une macro, par défaut, la macro sélectionne ces es sélectionnée, car elle enregistre les « Références de

en mémoire et qui se déroulera

pas ce déplacement, quel que soit l'emplacement de la ellule active lorsque vous exécutez la macro, configurer l'Enregistreur de macro pour qu'il nregistre les « Références de cellules relatives ». Le bouton Référence relative doit être activée. ans la barre d'outils Enregistrement, cliquer sur Référence relative. xcel continuera d'enregistrer les macros à l'aide de références relatives

cel ou que vous cliquiez à nouveau sur Référence relative ».

10. arre d'outils Enreg., cliquer sur Arrêter l'enregistrement.

cellules quelle que soit la première d'entre ellcellules absolues ». La macro enregistre alors un déplacement qui seralors de l’exécution.

Si vous souhaitez qu'une macro n’enregistreceDEjusqu'à ce que vous quittiez Ex«

9. Effectuer les actions à enregistrer.

Dans la b

Exécution rapide d'une macro s pouvez exécuter une mVou dialogue :

1. 2.

1.

est également possible d’affecter une macro à un bouton de barre d’outils. La procédure d’attribution de macro à un bouton de la barre d’outils est décrite dans la section suivan

acro en la sélectionnant dans la liste qui se trouve dans la boîte de

Menu Outils / Macro / Macros…; Sélectionner la macro à exécuter;

3. Bouton Exécuter ou Taper la combinaison de touches attribuée à la macro à exécuter.

Il

te



Affecter une macro à un bouton de barre d'outils Il est possible d’exécuter une macro à partir d'un bouton situé dans une barre d'outils prédéfinie personnalisée.

ou

Procédure :

Afficher la barre d’outils où sera affiché le bouton de la macro. 1. ocher située en regard du

2.

bouton

roit de la souris, er une macro.

5. Dans la zone Nom de la macro, sélectionner la

macro à attribuer au bouton ou taper le nom de la macro.

6. Valider.

Menu Outils / Personnaliser / onglet Barre d’outils / activer la case à cnom de la barre d'outils. Onglet Commandes / liste Catégories / Macros.

3. Dans la liste Commandes, faire glisser le

Bouton personnalisé vers une barre d'outils.

4. À l'aide du bouton d

sélectionner Affectcliquer sur le bouton de barre d'outils, puis



7. Pour modifier le bouton de la macro, cliquer avec le boutonde la souris et sélectionner « Modifier l’image du bouton

droit ».

ier une macro à un bouton

L1. Afficher la barre d'outils Formulaire et dessiner un bouton à l'aide de l'outil Bouton.

. Dès

3. . Valid

Pour

2

AffeSélec

4

Le n

2. 1. Pour

Saisi

Affecte1. Dess

. Cliqu

. Valid4. Doub

23

Universi

Bouton

que le bouton de la souris est relâché, la fenêtre

er.

renommer un bouton :

cter une macro s'affiche. tionner la macro requise.

om attribué par défaut à ce bouton est Bouton 1. ncer la touche CTRL avant de cliquer sur l'objet;

macro à un objet graphique

sélectionner l'objet afin de le modifier, enfor un nouveau nom.

r uneiner l'objet graphique; er sur l'objet avec le bouton DROIT de la souris; er l'option Affecter une macro…; le cliquer sur le nom de la macro à affecter.

té de Montréal Outils de rapidité - Efficacité page 73


SUPPRIMER UNE MACRO Afin de supprimer une macro, l’utilisateur doit récupérer le fichier Perso.xls à l’écran.

Procédure :

. Lancer Excel;

. Menu Fenêtres / Afficher…; Le dialogue suivant s’affiche.

. Sélectionner le fichier Perso.xls et valider;

4. tils Macro / Macros… 5. Sélectionner la macro à supprimer;

. Bouton Supprimer;

. Fermer le classeur Perso.xls sans enregistrer.

12

3

Menu Ou

67



NIVEAU DE SÉCURITÉ DES MACROS

Lors de transfert de fichier ou de création de macro, les macros peuvent ne pas être disponibles. L’une des premières vérifications à faire sera de vérifier le niveau de sécurité des macros afin d’activer la disp acros. onibilité des m

Pour ce faire :

1. Afficher la barre d’outils Visual Basic; 2. ;

Le dialogue suivant s’affiche. . Activer l’option « Niveau de sécurité moyen ».

Cliquer sur le bouton Sécurité…

3



Bibliographie / Référence

Excel 2000 Bible WALKENBACH, John, IDG Books, 1999

LEIERER, G. A., Micro Application, 2000

xcel 2000, Base et intermédiaire Bureautique Agathe Bisson, 2000

Excel 2000 PC Poche

E

Microsoft Excel 2000, Notions intermédiaires et avancées

VILLENEUVE, Louise, Bureautique Agathe Bisson, 2000

Tableaux et graphiques avec Excel 2000 GARDONIO, Stella, Logitell, 2000

Excel 2000, Fonctions avancées GARDONIO, Stella, Logitell, 2000

Excel 2000 Le Macmillan ULRICH, Laurie et Al, Campus Presse France, 1999

Excel 2000 Le Tout en Poche JOLIVALT, Campus Presse France, 2001

Excel 101 applications et macros, des tonnes de trucs DORSEUIL, Virginie, Campus Presse France, 2002

Le tout en poche, Excel et VBA 97-2000 BIDAULT, Michael, Campus Presse France, 2000

Université de Montréal Annexe page 76