Introduction
Excel est un tableur développé par Microsoft (l'entreprise qui édite Windows).
Un tableur est un logiciel (c'est-‐à-‐dire un programme) qui vous permet de saisir et d'analyser des données sous forme de tableaux ou de graphiques. En clair, vous avez une zone de travail dans laquelle vous saisissez des données avec votre clavier et vous les analysez au moyen de tous les outils (et il y en a beaucoup) qu'offre Excel.
Rappelez vous que les fichiers créés sous Excel sont identifiés par l'extension ".xls" et sont reconnaissables grâce à l'icone suivante :
Plus généralement, Excel vous offre une multitude d'outils pour voir des informations sous différents angles et agir en conséquence.
L'interface, c'est ce qui vous voyez quand vous ouvrez Excel, comme par exemple :
Comme vous pouvez le remarquer, l'interface d'Excel est basée sur un grand tableau représentant la zone de saisie. Ce tableau est constitué d'un ensemble de colonnes et de lignes qui se croisent pour former des cellules. Le rôle des éléments de l’interface est détaillé dans la suite.
Démarrer > Tous les programmes > Microsoft Excel.
Voici l'interface sous 2003:
Et voici l'interface sous 2007 :
Il est nécessaire de s'attarder sur quelques détails de cette capture de l'interface.
Au milieu, il y a un quadrillage très vaste. C'est votre zone de travail. Cette zone est entourée de nombreux boutons qui permettent d'agir dessus.
Tout en haut de la fenêtre, il y a ce que l'on appelle la barre des menus. Ce n'est pas nouveau car on la retrouve dans beaucoup de logiciels. De là, vous pouvez en fait ouvrir et enregistrer des fichiers et accéder à beaucoup de fonctions d'Excel.
Sous 2007, c'est un ruban qui remplace la barre des menus, nous utiliserons donc ce ruban pour indiquer les manipulations à réaliser.
Juste en dessous de la barre des menus, il y a une barre d'outils (2003 seulement). Ce sont des raccourcis pour accéder à des fonctionnalités que nous verrons plus tard.
Pour les masquer, suivez le programme de clics suivant :
Affichage > Barre d'outils > Standard
Affichage > Barre d'outils > Mise en forme
En bas à droite et sur le côté droit de votre zone de travail, il y a des ascenseurs. Ils vous permettent de vous déplacer dans le quadrillage. Pour les utiliser, il suffit de cliquer sur les flèches pour bouger
Et voici ce qu'ils donnent sous 2007:
En haut de votre quadrillage, il y a un champ très important : la barre de formules.
Cette zone est très importante et sera détaillée dans la suite de ce document. Ce champ très long se nomme barre de formules. La liste déroulante à gauche du champ ne fait pas partie de la barre de formules.
Voici comment elle est représentée sur 2007 :
L’icône qui a la forme d'un trombone et qui bouge un peu, est le compagnon Office. C'est un raccourci au manuel d'Excel., c.a.d. l’aide.
Lorsque vous ouvrez Excel, vous ouvrez ce que l'on appelle un classeur.
Pourquoi un classeur ?
Regardez la zone encadrée en noir, elle correspond à ce que l’on appelle les onglets des feuilles. En effet, quand vous regardez de plus près, vous voyez des onglets :
Et sous 2007 :
Chaque onglet correspond à une feuille de votre classeur. Si vous cliquez sur les onglets, vous changez de feuille. Vous pouvez mettre autant de feuilles que vous le désirez. Dans
98 % des cas, on ne se sert que d'une seule feuille à la fois mais par défaut, il y en a trois.
Il est possible d’ajouter / de supprimer / de renommer des feuilles. Par exemple, pour renommer une feuille, vous pouvez procéder de la manière suivante :
Votre feuille est maintenant renommée. Dans le même menu, le bouton Insérer vous permet d'insérer une nouvelle feuille de calculs et le bouton Supprimer d’en supprimer une.
Chaque case de la zone de travail est appelée cellule. Nous la repérons dans le tableur grâce à ses coordonnées.
Des coordonnées ?
Regardez votre zone de travail : il y a des cellules, mais aussi des numéros de colonnes et de lignes.
Les numéros de colonnes :
Une cellule sélectionnée, ou encore nommée cellule active, est caractérisée par la lettre de sa colonne et le nombre de sa ligne.
Ainsi, si une cellule se situe dans la colonne C et à la ligne 12, elle aura pour coordonnées C12. De même pour une autre cellule située dans la colonne H et à la ligne 4 : elle aura pour coordonnées H4 (comme à la bataille navale).
Nous allons aborder ici la sélection des objets de votre zone de travail (colonnes, cellules, lignes).
Une cellule (comme nous l'avons vu plus tôt), c'est un petit rectangle repéré grâce à ses coordonnées. Nous allons voir ici comment sélectionner ces cellules.
Pour sélectionner une cellule, il faut cliquer gauche sur celle-‐ci. Un cadre noir apparaîtra alors autour de la cellule sélectionnée :
Une cellule non sélectionnée
Une cellule sélectionnée. Un cadre noir est présent autour de celle-‐ci.
Maintenez le bouton gauche de la souris enfoncé et glissez dans la zone de travail pour définir un bloc. Relâchez le bouton lorsque vous avez terminé :
Un bloc de cellules
Rq : la cellule dans le bloc qui n'est pas en bleu mais en blanc fait tout de même partie du
bloc.
Pour sélectionner des cellules éparpillées dans votre feuille de calcul, cliquez gauche sur une cellule. Maintenez la touche Ctrl enfoncée puis cliquez gauche sur les cellules que vous voulez ajouter à votre plage.
Lorsque vous sélectionnez une colonne, vous sélectionnez en fait toutes les cellules de celle-‐ci, soit 65 536 cellules (une colonne ayant ce nombre important de lignes).
Pour sélectionner une colonne, cliquez gauche sur la zone encadrée en rouge sur l'image:
De même pour les lignes, sauf que cette fois-‐ci, il faut cliquer gauche sur le numéro de
ligne !
Qu'est-‐ce qu'un tableur ?
Si je vous annonce :« Ma cellule se trouve dans la colonne B et à la ligne 4. » Quelles sont les coordonnées de ma cellule ?
Nous avons vu dans la section précédente ce qu'est un document Excel. Mais nous avons encore beaucoup à découvrir sur les tableaux et leurs cellules : comment saisir des données, choisir leur format, mettre de la couleur…
Dans cette sous-‐section, nous allons voir comment saisir des données, que nous pourrons par la suite analyser.
Lorsque vous sélectionnez une cellule, celle-‐ci sera appelée cellule active.
Lorsque vous saisissez des données, elles seront entrées dans la cellule active.
Sélectionnez une plage de cellules :
Il y a une cellule qui est dans la plage qui n'est pas bleue mais blanche. C'est par cette cellule que la sélection a été commencée. Elle est donc la cellule active : les données seront affichées dans cette dernière.
Rq : Il n'y a qu'une seule cellule active par plage.
Maintenant que le point est fait sur la cellule active, saisissons nos données.
Pour entrer une donnée, double-‐cliquez sur une cellule et écrivez ce que vous voulez.
Appuyez sur Entrée : le tour est joué !
Sélectionnez cette cellule et regardez la barre de formule : votre texte y est affiché et vous pouvez le modifier !
Rq : Vous ne pouvez saisir « que » 32 000 caractères par cellule.
Parfois, il peut être bénéfique d'allonger vos cellules, un peu comme ceci :
Pour cela, placez votre curseur en forme de croix à la limite d'une colonne. Lorsqu'il est transformé en un petit curseur noir, maintenez le bouton gauche de la souris enfoncé et élargissez votre colonne.
On peut faire de même pour les lignes.
Vous avez la possibilité de mettre de la couleur dans vos cellules, de changer la police du texte qui s'y trouve
Vous pouvez aussi changer le format de vos données. Par exemple, si vous avez une colonne où tous les nombres sont en euros, il suffit de dire à Excel que dans cette colonne, tous les nombres que vous rentrez sont des euros. Le logiciel ajoutera le signe de la monnaie européenne.
Saisissez des données numériques sur quelques cellules :
Sélectionnez la plage qui contient ces données puis suivez le programme de clics :
Clic droit > Format de cellule
Une fenêtre avec plusieurs onglets s'ouvre :
Nous sommes sur l'onglet qui va nous intéresser : l'onglet Nombre.
Si vos données sont numériques (ce qui est le cas), vous allez pouvoir définir un format, qui va s'appliquer pour toutes les cellules sélectionnées, ici. Dans notre cas, nous voulons des euros. Cliquez sur Monétaire.
Laissez les options par défaut puis cliquez sur OK : vous revenez au tableau.
Vos données sont maintenant sous un format monétaire !
Il y a un grand nombre de formats possibles. On ne va pas tous les voir un à un, mais regardez le format Date, il est particulièrement intéressant
Nous allons maintenant ajouter des couleurs.
Sélectionnez une plage de cellules puis accédez au format de cellule :
L’onlget Alignement permet d'agir sur la disposition dans l'espace des données stockées dans une cellule. Les options les plus importantes sont celles qui permettent de modifier l'alignement horizontal et vertical de votre texte. Enfin, il peut s'avérer utile de prévoir un retrait pour une bonne mise en forme du texte.
L’onglet Police définir la police de caractère à utiliser pour les cellules sélectionnées, son style ainsi que sa taille. D'autres options comme la couleur et le soulignement sont disponibles. Un aperçu vous permet de rapidement voir l'effet des modifications que vous venez d'effectuer.
L’onglet Bordure permet de définir les bordures du tableau. Par défaut, les tableaux dans Excel n'ont pas de bordure, mais il est souvent plus simple de lire un tableau avec bordures d'où l'intérêt d'en ajouter systématiquement.
En cliquant sur les boutons « contour » et « intérieur » dans l'onglet bordure, vous verrez s'ajouter des lignes noires à l'aperçu qui se trouve juste un peu plus bas. Vous pouvez modifier le style des lignes grâce aux options de style (à droite de la fenêtre) et vérifier le résultat en appuyant à nouveau sur les deux boutons « contour » et
« intérieur »:
Validez votre travail en appuyant sur OK.
L’onglet Motifs offre la possibilité de définir des motifs de remplissage des cellules. Dans cet onglet vous pourrez ainsi choisir une couleur de remplissage pour certaines cellules, ou une texture comme le montre l'image suivante :
Mise en forme automatique
Comme vous avez pu le constater, la procédure précédente peut s'avérer lourde dans certaines situations. Pour palier ce point, Excel a prévu une fonctionnalité appelée « Mise en forme automatique » qui permet de faire le travail rapidement et en une seule étape.
Après avoir sélectionné l'ensemble du tableau, allez dans le menu « format » et choisissez « Mise en forme automatique ». Dans le fenêtre qui s'affiche à l'écran, il suffit de choisir le modèle qui vous convient (en cliquant dessus) et de valider par OK.
Vous pouvez aussi gagner du temps dans la mise en forme de vos tableaux en utilisant les raccourcis de la barre d'outils qui permettent un accès plus rapide aux options de mise en forme les plus utilisées.
On ne le dira jamais assez, Excel, c'est complet et puissant. Nous allons utiliser un outil qui se trouve sur les seize millions sept cent soixante-‐douze mille deux cent seize cellules de chaque feuille.
Écrivez dans une cellule un mot, ou encore un chiffre. Par exemple,
Voyez-‐vous le petit carré noir en bas à droite de ce cadre qui montre que la cellule est sélectionnée ?
Cliquez gauche dessus ; maintenez le bouton enfoncé, descendez puis relâchez.
Le glisser-déplacer
Le mot « Salut » a été recopié.
Lorsque nous relâchons le bouton gauche de la souris, le mot « Salut ! » a été recopié. Une plage est sélectionnée.
Le cas particulier d'une liste
Excel connaît déjà des listes comme la liste des mois, des jours de la semaine
Vous voulez la preuve ? Ça tombe bien, c'est facile à faire avec une donnée, une poignée de recopie incrémentée et un glisser-‐déposer !
Choisissez au hasard une cellule et écrivez-‐y « Lundi ».
Utilisez la poignée de recopie incrémentée comme ci-‐dessus puis relâchez le bouton.
Que voyez-‐vous ? La liste a été complétée toute seule !
Cela fonctionne aussi pour les mois : il suffit d'écrire « Janvier » à la place de « Lundi » puis de suivre la même procédure.
Créez le tableau suivant en utilisant les techniques montrées précédemment (attention à bien définir le format des cellules).
Si Excel possède des fonctions de mise en forme plus que correctes, elles ne sont en aucun cas la vocation de ce logiciel. En effet, Excel se distingue surtout par ses fonctionnalités de traitement des données, c'est d'ailleurs pour cela qu'on dit que c'est un Tableur.
La première fonctionnalité offerte est la possibilité de créer des formules de calcul. En effet, Excel est basée sur une logique qui dit que seules les données de base doivent être fournies par l'utilisateur, tous les calculs devant être pris en charge par Excel.
Dans le premier chapitre, nous avons présenté la barre de formule. C’est dans cette barre de formule que nous allons écrire les formules de calcul. Elles s’appliqueront à toutes les cellules sélectionnées. Une formule commence toujours par le signe égal =.
Pour toutes les formules, on pourra utiliser les opérateurs arithmétiques classiques (p.ex. + - * /) et/ou des fonctions. Les fonctions sont des mots écrits en majuscule dans les formules et qui permettent de ne pas avoir à écrire des opérateurs (+, x, etc.). C'est donc très pratique s'il y a des formules de 3 lignes.
Une fonction est représentée par un mot dans lequel on fait passer des données (c.f. schéma ci-‐dessous). La fonction travaille sur ces données et ressort le résultat.
Mais comment fait-‐on pour passer des données à une fonction, et où sera affiché le résultat ?Le résultat de votre formule sera affiché dans la ou les cellules qui contiennent cette formule.
Voici la syntaxe d'une formule:
= DONNEE1 op1 DONNEE2 op2 DONNEE3 … avec op1, op2 … un des opérateurs arithmétique
=FONCTION(DONNEE1;DONNEE2)
Il est également possible de combiner opérateurs arithmétiques et fonctions.
À la place des données, vous allez écrire la référence des cellules qui contiennent les données à analyser.
L'addition est gérée par l’opérateur + ou la fonction SOMME.
En B2, tapez 5 ; en C2, 123 (prenez l'habitude de ne pas commencer à saisir des données à la ligne 1 et dans la colonne A ).
Vous devriez avoir ceci :
Maintenant, je veux en E2 le résultat de l'addition de ces deux valeurs. Je vais donc taper ma formule en E2, ce qui donne :
=B2+C2
ou
Sur Excel, vous obtenez ceci :
Validez par la touche Entrée : vous avez en E2 le résultat de l'addition 5 + 123 !
Pourquoi ne pas écrire directement = 5 +123 ?
Avec notre formule, changez la valeur de B2, mettez par exemple 10 Que constatez-‐
vous ? Le résultat en E2 s'adapte !
La soustraction est gérée par l’opérateur – ou par la fonction SOMME. Avec la fonction, il faut alors mettre un signe négatif (-‐) devant le second nombre (après le points-‐virgule).
=B2-C2
ou
=SOMME(B2;-C2)
La multiplication est gérée par l’opérateur * ou par la fonction PRODUIT. Sa syntaxe est la même que pour l'addition.
La division est gérée par l’opérateur / ou par la fonction QUOTIENT. Sa syntaxe est la même que pour l'addition.
Une fonction bien intéressante est la fonction MOYENNE, qui fait la moyenne d'une plage de cellules.
Elle n'est pas intéressante parce qu'elle fait la moyenne mais parce qu'il y a une manière un peu spéciale d'écrire la formule
Délimitez une plage rectangulaire de cellules et entrez une donnée numérique dans chacune d'elles. Dans une cellule en dehors de cette plage, nous allons faire la moyenne de tous les nombres que vous avez entrés.
Commençons à tapez la formule
Sélectionnez votre plage de cellules :
La plage a été générée toute seule dans la formule ! Fermez la parenthèse, validez, vous avez votre moyenne.
Cette technique est valable pour toutes les fonctions vues ci-‐dessus. En règle générale, vous serez plus souvent amenés à faire une addition de toutes les valeurs d'une grande plage de cellules qu'une addition des valeurs de deux cellules !
Outre les fonctions de base comme la MOYENNE ou la SOMME, Excel propose une multitude de fonctions plus élaborées accessibles via le menu Insertion > Fonction.
Pour insérer une fonction dans une cellule, il vous suffit de cliquer sur la cellule et d'aller dans le menu Insertion > Fonction ce qui affichera la fenêtre suivante :
Cette fenêtre vous propose l'ensemble des fonctions supportées par Excel classées en catégories accessibles par une liste déroulante. Une fois la catégorie choisie, les fonctions qui y sont proposées sont affichées dans la liste qui se trouve un peu plus bas.
Cliquez alors sur la fonction souhaitée et vous verrez s'afficher en bas de la liste des fonctions une brève description de la fonction et de son utilisation. Ceci est particulièrement pratique quand vous utilisez une fonction pour la première fois.
Utilisons cet assistant plutôt que la saisie directe de la fonction. Après avoir cliqué sur une cellule et avoir accédé à la fenêtre d'insertion des fonctions, choisissez la catégorie « tous » pour afficher toutes les fonctions. Faites dérouler jusqu'à arriver à la lettre M et choisissez la fonction MOYENNE. En appuyant sur OK vous voyez s'afficher à l'écran la fenêtre suivante :
Comme vous pouvez le voir, Excel devine automatiquement la plage de cellules la plus probable pour cette fonction. Toutefois, à des fins pédagogiques, il nous semble préférable de vous montrer la méthode à suivre car il arrive qu'Excel n'affiche pas les bons arguments.
Appuyez d'abord sur le petit bouton qui se trouve en regard de chaque argument. Cela affichera la fenêtre suivante :
Ensuite, il vous suffira de sélectionner au moyen de la souris les cellules souhaitées pour voir se modifier le texte de la fenêtre en même temps. Une fois la bonne plage sélectionnée, il ne vous reste qu'à enfoncer la touche Entrer du clavier pour valider votre travail. Vous reviendrez alors à la fenêtre précédente et verrez une simulation du résultat de la fonction, si vous êtes satisfait, appuyez sur OK et le tour sera joué.
Signalons que l'argument2 est facultatif et sert à introduire une seconde plage de cellules quand cela est nécessaire.
Vous pouvez maintenant procéder de la même manière pour toutes les autres fonctions d'Excel, nous traiterons toutefois en détails certaines fonctions dont l'utilisation s'avère délicate.
Lorsque vous saisissez des formules, ça peut parfois rater. C'est-‐à-‐dire qu'à la place du résultat souhaité, vous avez des dièses (#), des slashs (/) partout.
Nous allons traiter les cas les plus courants un à un.
#DIV/0!
Lorsque vous rencontrez cette erreur, c'est que votre formule fait une division par 0.
N'oubliez pas que pour Excel, une cellule vide a pour valeur 0 !!
#NOM?
Cela signifie que vous avez tapé une fonction que Excel ne connaît pas. Par exemple, la formule =PROD(A2;E4) provoquera une erreur, car PROD n'est pas une fonction reconnue.
#VALEUR!
Votre formule utilise dans son calcul une valeur que Excel ne peut pas utiliser. Voici l'exemple classique :
Excel ne peut pas faire 2 + 3 + Salut ! Il renvoie donc une erreur.
#######
Le nombre comprend trop de chiffre pour pouvoir être affiché avec cette largeur de colonne. Il faut alors élargir la colonne, soit en cliquant et en glissant vers la droite, soit en double cliquant sur la même zone que le cliquer-‐déplacer. La colonne se met automatiquement à la largeur minimale pour afficher le texte des cellules.
Tout comme les formules classiques, les conditions se tapent toujours dans la barre de formule et commencent toujours par le signe égal =. Néanmoins, elles permettent d’exprimer des traitements plus complexes que des formules simples. Par exemple, elles permettent d’adapter la formule en fonction des données.
Quand vous écrivez une condition, vous dites à Excel : « si telle cellule vaut tant, alors fais ceci, sinon, fais cela ». Par exemple, je veux afficher Oui ou Non dans une cellule en fonction de la valeur d'une autre cellule. Si celle-‐ci est égale à 100, j'affiche Oui, sinon, j'affiche Non.
Voici la syntaxe d'une condition :
Que doit-‐on mettre à la place de « condition » ?Voici les opérateurs qui vont vous être utiles :
| Opérateur | Description |
| = | Est égal à |
| > | Est supérieur à |
| < | Est inférieur à |
| >= | Est supérieur ou égal à |
| <= | Est inférieur ou égal à |
| <> | Est différent de |
Si votre condition est : « Si la cellule B2 est supérieure ou égale à 45, alors », vous remplacerez condition par :
Voici comment ça se passe dans Excel :
Et voilà le résultat quand la cellule contient une donnée numérique inférieure à 100 :
Et quand la donnée est égale à 100 :
Il existe deux formes de conditions multiples :
« Si cette cellule vaut tant et l'autre vaut tant, alors fais ceci, sinon, fais cela. »
« Si cette cellule vaut tant ou l'autre vaut tant, alors fais ceci, sinon, fais cela. »
Avant et après le ET ou le OU, vous mettez une condition. D'où le nom de condition multiple. La différence entre ces deux cas, c'est que dans l'un les deux conditions doivent être remplies pour effectuer une tâche quelconque alors que dans l'autre, il faut qu'une seule condition soit remplie pour effectuer une tâche.
Maintenant que la différence est faite entre ET et OU, nous allons mettre en pratique ces conditions multiples.
Voici la syntaxe :
=SI(OPERATEUR LOGIQUE(condition1;condition2);"Afficher si vrai";"Afficher si faux")
Que mettre à la place de « opérateur logique » ?Vous mettez soit ET, soit OU.
Supposons que nous souhaitions afficher soit OUI ou NON en fonction de la valeur d'une cellule. Dans ce cas, prenons cette valeur à 100.
Voici la formule (D6 étant la cellule où est stockée cette valeur) :
Dans ce cas, il affiche OUI.
Mettez la valeur à 12, par exemple, il affichera NON.
La poignée de recopie incrémentée peut également être utilisée avec les formules. Pour rappel, la poignée de recopie incrémentée est le petit carré noir, en bas à droite, qui recopie la valeur des cellules où vous voulez et qui reconnait quelques listes
Cette poignée est capable de recopier aussi vos formules et de les adapter !
Voyons avec un exemple très simple : une addition où je vais exceptionnellement ne pas utiliser une fonction mais bien un opérateur (+).
Une possibilité pour recopier la formule vers le bas serait de faire un copier/coller de la formule sur toutes les cellules, mais il y a plus simple …
A quoi correspondent les dollars $ dans l’exemple ?
Utilisons donc la poignée de recopie incrémentée sur ma formule :
Nous obtenons un résultat intéressant : Excel a compris qu'il fallait « descendre » d'une cellule à chaque fois !
Regardons la formule : alors que ma formule de départ concernait la cellule G8, la case d'en dessus utilise la cellule G9 !
Excel a donc adapté automatiquement la formule.
Les dollars servent donc à figer l'objet devant lequel il se trouve. Dans ce cas, il est devant la lettre de la colonne et le numéro de la ligne : la cellule E6 est totalement figée.
Si nous ne l’avions pas fait, Excel aurait additionné les valeurs des cellules en dessous de
E6, c'est-‐à-‐dire 0 (une cellule vide a pour valeur 0) !
Lorsqu’on utilise la poignée sur une formule, Excel incrémente les cellules qui sont impliquées dans cette formule. Les dollars me permettent d'éviter cette incrémentation, ce qui peut s'avérer utile. Notez que ce signe peut être uniquement placé devant la lettre de colonne (il figera alors la colonne), ou uniquement devant le numéro de ligne (il figera alors la ligne).
Rq : dans une formule, il y a autant de parenthèse(s) ouvrante(s) que de parenthèse(s) fermante(s).
Un classeur Excel est en fait un ensemble de feuillets. Il est possible d’en avoir autant que l’on souhaite et il est possible d’effectuer diverses opérations dessus, comme les renommer ou encore les supprimer.
Dans la pratique, il se peut que vos données soient réparties dans plusieurs feuillets différents, évidement nommés pour ne pas s'y perdre. Dans ce cas, il sera très utile de pouvoir transmettre des données d'une feuille à l'autre, et c'est justement ce que nous allons voir dans cette partie.
La transmission de données d'un feuillet à l'autre se passe dans une formule.
Ouvrez Excel puis supprimez/ajoutez des feuillets de telle sorte qu'il ne vous en reste plus que deux. Renommez le premier en "Source" et le second en "Cible". Vous l'aurez sans doute compris, nous allons transmettre une donnée du premier feuillet vers le second.
Dans le feuillet Source, tapez en B2 une donnée (numérique ou non, peu importe).
Pour récupérer cette donnée dans le feuillet Cible, il faut préciser de quel feuillet notre donnée provient. Dans ce cas, la donnée à transmettre provient de Source. La formule sera donc :
On précise de quel feuillet nous souhaitons importer les données au début de la formule avec le nom de la feuille suivi d'un point d'exclamation.
Si nous tapons cette formule en C5 du feuillet Cible, nous allons avoir le même texte qu'en B2 dans le feuillet Source.
A vous maintenant d'adapter cette méthode en fonction de vos besoins !
Modifiez le tableau de l’exercice de la section II.D de façon à ce que
« Commandes » et des « Livraisons ».
Supposons que vous disposez de la base de données suivante sur Excel :
| Employé | Ventes | Commission |
| Jean | 700 | |
| Bernard | 372 | |
| Pierre | 440 | |
| Rachid | 801 | |
| David | 975 |
Pour motiver vos vendeurs, vous avez décidé de doubler la commission que touche chaque vendeur pour toutes le ventes qui dépassent les 500 pièces. La vente est payée 7 Euros (une fois doublée elle sera donc payée 14 Euros).
Créez ce tableau de façon à ce que la commission soit calculée automatiquement en fonction des ventes.
Supposons que vous êtes chargé(e) d'effectuer un rapport sur l'activité commerciale de vos vendeurs. On vous fournit le tableau suivant (dans la première feuille du classeur Excel) que vous devez compléter :
| Vendeur | Pays | CA HT | CA TTC | Com |
| Legrand | France | 25000 | ||
| Paoli | Italie | 49500 | ||
| Chasseney | France | 22750 | ||
| De la garde | Italie | 18000 | ||
| Parlois | Italie | 120000 | ||
| Legrand | Espagne | 84300 | ||
| Paoli | Espagne | 21200 | ||
| Aulin | France | 98700 |
Le tableau est composé des informations suivantes :
On vous fourni aussi la grille suivante qui servira de base aux calculs (dans une seconde feuille du classeur appelée "grille") :
| Pays | Taux taxe | Taux Com |
| France | 19,60% | 5% |
| Espagne | 21% | 4% |
| Italie | 15% | 3,5% |
Quelle formule faut-‐il insérer dans les cellules D2 et E2 pour calculer le CA TTC et les commissions des vendeurs ?
Pour rappel, le chiffre d ‘affaires toutes taxes comprises est calculé de la manière suivante :
CA TTC = CA HT x (1 + Taux taxe). De même, la commission du vendeur est calculée de la manière suivante :
Com = CA HT x Taux Com
Réalisez cela de deux manières différentes :
Le principe est simple : vous fournissez à un chef d'entreprise un classeur Excel avec un tableau, qu'il devra compléter.
Ce tableau est une facture, c'est-‐à-‐dire qu'il y rentre tous les produits vendus au client, le total hors taxe (HT) et le total toutes taxes comprises (TTC). Il faudra préciser le taux de la TVA et l'utiliser dans vos calculs.
Le patron propose une remise de 10 % sur le total à payer TTC si celui-‐ci est supérieur à
250 euros ou si on achète 8 articles.
Prérequis :
Comme nous l’avons vu précédemment, il n’est pas conseillé de commencer un tableau dans la colonne A ni à la ligne 1. Cette section présente d’autres notions pointues qui pourront vous être utiles pour créer des tableaux plus lisibles.
Le quadrillage est utile lorsqu'on fait notre tableau mais il peut devenir peu esthétique et embêtant ensuite.
Pour l'enlever, respectez le programme suivant :
Outil > Options
Sous 2007 :
Dans le bas de la fenêtre qui vient de s'ouvrir, décochez Quadrillage.
Faites de même pour les en-‐têtes de lignes et de colonnes si vous le souhaitez.
Si votre tableau fait 659 lignes, il peut être difficile de connaître à quoi correspond chaque colonne. Nous allons donc faire en sorte d’afficher continuellement leurs étiquettes dans l’interface et ce, même si nous descendons jusqu'à la dernière ligne.
Sélectionnez une ligne en dessous de vos étiquettes et suivez le programme : Fenêtre > Figer les volets
Sous 2007, vous pouvez séparer la fenêtre en 2 pour figer les volets, voici comment procéder :
Cliquez gauche sur le bouton juste au dessus de la barre de défilement vertical, le curseur se transforme comme lorsque vous voulez agrandir une ligne. Glissez vers le bas et lâchez. Vous avez ainsi créé deux fenêtres de la même feuille de calcul et figé les valeurs dans la partie supérieure.
Pour revenir à une seule fenêtre, cliquez et glissez sur la barre qui sépare les deux parties et remontez tout en haut puis lâchez.
Voici ce qu'il fallait faire.
o Le patron doit pouvoir rentrer la TVA : il fallait donc prévoir une cellule spécialement pour ce taux et penser à la figer lors des calculs.
o Le patron accorde une remise de 10 % sur le total TTC si on achète pour plus de 250 euros ou si on achète au moins 8 articles. Il faut voir la condition multiple.
o Enfin, notre patron doit pouvoir visionner le prix HT et TTC : il fallait donc réserver deux cellules pour ces valeurs. De préférence, les mettre dans les volets que nous figeons ensuite. • Faire le tableau.
On commence donc par un peu de texte (le quadrillage et les en-‐têtes de lignes et de colonnes ont été enlevés) :
Une TVA (Taxe sur la Valeur Ajoutée) s'exprime en %. La cellule qui va donc retenir le taux de cette taxe devra être formatée pour accueillir un pourcentage (comme ça, pas besoin de taper le symbole %) :
Rq : Il faut appliquer le format de cellule avant d'entrer la valeur, sinon la valeur est multipliée par 100 et on n'obtient pas ce que l'on souhaite.
Le quadrillage a volontairement été remis pour vous montrer que le taux de la TVA n'est pas dans la même cellule que le texte de l'étiquette qui pointe vers cette cellule.
Nous cliquons donc sur Format de cellule (comme expliqué dans les chapitres précédents), nous restons sur l'onglet Nombre, nous cliquons sur Pourcentage dans la
liste puis sur Ok.
On laisse deux décimales au cas où le taux augmente. Supposons que le taux de la TVA est de 8%. Tapez juste 8 dans la cellule formatée et le chiffre est automatiquement mis en pourcent :
Nous continuons maintenant notre remplissage et on définit un tableau, qui limite donc la facture. On pensera également à formater les cellules qui contiendront les prix en Monétaire :
Nous masquons la colonne I en effectuant un clic droit sur l'en-‐tête de cette colonne puis sur Masquer. On fige ensuite les volets à partir de la ligne 7.
Maintenant, il va falloir écrire les formules.
La colonne Total va servir à faire le produit de la colonne D par la E. On le fait une fois puis on utilise ensuite la poignée de recopie incrémentée :
Nous incrémentons :
Ce qui donne ceci :
Il y a un grand nombre de valeurs nulles, ce qui est normal, car une cellule vide a pour valeur 0 (et donc 0 fois 0 = 0). On va dire à Excel de masquer les valeurs des cellules si elles valent 0, par exemple, en mettant la couleur de l'écriture de ces cellules en blanc.
Sélectionnez toutes les cellules de votre tableau et cliquez sur Format puis sur Mise en forme conditionnelle.
Sous 2007, dans l'onglet Accueil puis Style, cliquez sur Mise en forme conditionnelle. Dans le menu déroulant, cliquez sur Nouvelle règle. Puis, dans la fenêtre qui s'ouvre, cliquez sur Appliquer une mise en forme uniquement aux cellules qui contiennent.
Vous laissez "La valeur de la cellule" mais vous choisissez "égale à" dans la seconde liste déroulante :
Tapez "0" dans le troisième champ puis cliquez sur le bouton Format, qui se trouve dans la même petite fenêtre. Choisissez la couleur blanche en guise de police :
Cliquez sur Ok. Vous venez de faire une mise en forme conditionnelle : c'est-‐à-‐dire changer les propriétés d'une cellule en fonction de sa valeur. On peut faire pareil, mais en changeant la couleur de fond, par exemple.
Le prix HT (Hors Taxe) n'est qu'une vulgaire somme :
Le prix TTC demande la prise en compte du taux de la TVA :
Pour les remises, on va tout d’abord afficher le prix avec remise dans la cellule située à droite de celle qui contient le prix TTC. Il faut donc savoir si le client a droit à sa remise. Si oui, on affiche le nouveau prix, si non, on n'affiche rien du tout (inutile d'avoir deux fois le même prix l'un à côté de l'autre).
Voilà la condition à écrire (avec la remise de 10%) :
Avec les mises en formes conditionnelles, appliquez une bordure à la cellule qui contient le prix avec remise uniquement si elle contient une valeur. Vous découvrirez ainsi les possibilités de cet outil.
En plus des tableaux, Excel propose un second moyen de représenter les données de manière synthétique : les graphiques. Il existe plusieurs types de graphiques dont l'utilisation dépend des données sources et de l'objectif de l'analyse.
Excel offre un large choix de types de graphiques mais nous nous contenterons de présenter les trois principales familles de graphiques qui nous semblent les plus à même de couvrir l'essentiel de vos besoins.
Les courbes sont utilisées pour étudier l'évolution d'une ou plusieurs variables, par rapport à une autre. Par exemple, l'on est typiquement amené à rechercher l'évolution d'un ou plusieurs indicateurs dans le temps.
Voici un exemple d'analyse univariée (une seule courbe -‐ à gauche) et d'une analyse bivariée (deux courbes -‐ à droite), une infinité de variables pouvant bien sûr être étudiées grâce aux courbes :
Les courbes permettent entre autres :
Les graphiques en secteurs permettent de représenter graphiquement la part de chaque valeur d'une variable dans le total des valeurs de ladite variable. On étudiera par exemple la part de chaque appareil dans la consommation électrique d'un foyer.
Le graphique suivant montre que la variable 1 représente plus de la moitié du total, que les variables 2 et 3 ont des valeurs comparables et que la variable 4 ne représente qu'un tout petit pourcentage du total :
Les histogrammes sont à mi-‐chemin entre les courbes et les secteurs. Ils permettent d'étudier simultanément l'évolution des variables par rapport à une autre, et de comparer les variables étudiées entre elles.
Dans le graphique suivant, on peut voir l'évolution des trois variables dans le temps mais aussi comparer les trois variables entre elles à chaque période :
Toutefois, cette polyvalence des histogrammes a pour contrepartie de limiter les histogrammes à un petit nombre d'observations et de variables au delà desquelles le graphique devient illisible et sans grande utilité.
Pour réaliser un graphique, il est bien évidemment indispensable de disposer d'un certain nombre de données. Prenons donc comme exemple le tableau suivant :
| Var 1 | Var 2 | Var 3 |
| A | 100 | 80 |
| B | 150 | 90 |
| C | 115 | 101 |
| D | 138 | 57 |
Supposons, dans un premier temps, que nous voulons comparer les variables 2 et 3 sur les différentes valeurs de la variable 1. En terminologie Excel on dira que l'on souhaite représenter deux séries (var 2 et var 3).
Pour ce faire, sélectionnez les deux colonnes en question sans sélectionner la première. Cliquez ensuite sur l'icône de l'assistant graphique dans la barre d'outils ou encore allez dans le menu Insertion > Graphique Apparaitra alors l'assistant suivant :
Comme vous le voyiez, vous pouvez choisir le type de graphique et sa mise en forme.
Nous choisirons "Histogramme" (comme cela est expliqué dans la page précédente) et un effet 3D. Des options de mise en forme et des types de graphiques plus élaborés sont aussi accessibles en cliquant sur le second onglet "types personnalisés".
Cliquez sur le bouton Suivant > et vous verrez un aperçu du graphique que vous allez obtenir.
Vous remarquez que le graphique représente bien nos deux variables (nos séries sont bien en colonnes, l'option par défaut) mais que l'axe des abscisses ne reprend pas les valeurs de la variable Var 1. Ceci est dû au fait qu'Excel n'a d'informations que sur les séries et aucune sur l'étiquette de l'axe des abscisses.
Pour remédier à cela, il suffit d'accéder à plus d'options à travers l'onglet "Série" qui offre la possibilité de redéfinir les valeurs des séries (Valeurs), leurs étiquettes (Nom) ainsi que les données à mettre en abscisses comme le montre l'écran suivant :
Nous avons désormais un graphique conforme à nos attentes. Prenez toutefois le temps de tester différentes alternatives afin de mieux comprendre les différentes options car vous en aurez besoin si vous envisagez de réaliser des graphiques plus complexes.
Cliquez ensuite sur le bouton Suivant > pour valider cette étape et passer aux options de graphique. Dans la fenêtre qui s'affiche vous aurez un grand nombre de possibilités de personnalisation du graphique. Nous vous invitons à les tester une par une car vous en verrez l'effet en temps réel sur l'aperçu à droite de la fenêtre. Nous vous proposons cette présentation à titre indicatif (essayez d'obtenir le même résultat cela vous fera un bon exercice) :
Une fois que le résultat est satisfaisant, vous pouvez cliquer sur le bouton Suivant et passer à la dernière étape de l'assistant de création des graphiques.
On vous proposera de choisir entre deux possibilités :
Cliquez enfin sur Terminer et votre graphique sera inséré dans la feuille.
Reprenez l’exemple vu dans l’exemple III.E.3 et générez à partir de ces données un graphique permettant de comparer le chiffre d’affaires hors taxe des vendeur pour chaque pays (choisir un type de graphique adapté).
Il peut être intéressant d'effectuer un premier tri dans ses données avant traitement. Par exemple, si vous demandez à un utilisateur de rentrer dans une cellule une date entre le 01 Janvier 2008 et le 31 Décembre 2012, qui peut garantir que la date saisie sera bien dans cet intervalle ? Il y aura toujours quelqu'un pour écrire une date de l'année 1999 ou 2013.
Les outils que nous allons découvrir dans ce chapitre vous permettront de ne pas vous tromper dans la saisie, mais aussi de la simplifier. Nous allons plus particulièrement nous intéresser au menu Données.
Quel que soit le cas de figure, des données triées sont toujours plus faciles à exploiter et à interpréter, d'où l'importance de la fonction de tri proposée par Excel.
Excel propose donc de nombreux outils pour trier vos données. Par exemple, supposons que nous souhaitons trier les entrées d'un tableau par ordre alphabétique du prénom. Faisons donc un tableau bien structuré et sans espace entre les différentes lignes.
Sélectionnez tout ce tableau, puis cliquez sur Données (barre des menus) puis sur Trier.
Sous 2007, dans l'onglet Accueil, la rubrique Édition, cliquez sur Trier et filtrer. Vous avez une petite fenêtre qui s'ouvre qui permet d'aller plus vite dans les tris alphabétiques. Si vous voulez spécifier plus en détail le type de tri, cliquez sur Tri personnalisé.
Une fenêtre s'ouvre et vous constatez qu'Excel reconnait parfaitement votre tableau et ses étiquettes.
Puisque nous voulons trier les noms par ordre alphabétique, nous nous occupons seulement d'une zone de tri. Nous travaillons sur les noms, donc, il faut sélectionner Nom dans la liste déroulante. En cochant "Croissant", les noms seront rangés par ordre alphabétique.
Sous 2007 :
Vous pouvez ainsi trier par Nom, Age ou Score. Par défaut, Excel vous propose un seul niveau de tri, pour en ajouter, cliquer sur Ajouter un niveau.
Rq : Avec cette méthode, on aurait pu aussi trier en fonction du score mais aussi de l'âge.
Nous cliquons sur Ok, et notre tableau est désormais trié :
Dans cette sous-‐partie, nous allons continuer notre petit tour d'horizon du menu Données, qui réserve encore bien des surprises. Cette fois-‐ci, nous voulons obtenir un tableau pré-‐rempli afin de le compléter en fonction de l'âge des participants à un concours. Ce concours est ouvert aux 12 -‐ 17 ans.
Pour notre exemple, il faut dire à Excel que les valeurs des trois cellules vides doivent être comprises entre 12 et 17. Dans le cas contraire, Excel renvoie un message d'erreur et votre donnée ne sera pas saisie. Sélectionnez ces trois cellules.
Suivez le programme de clics suivant : Données > Validation
Sous 2007 : Données > Outils de données > Validation des données
Une fenêtre s'ouvre :
Vous remarquez qu'elle est constituée de trois onglets. Ouvrez l'onglet "Options".
Nous souhaitons que les cellules sélectionnées n'acceptent avant tout qu'un âge. Un âge étant un nombre entier, déroulez la liste Autoriser et choisissez Nombre entier.
Si vous déroulez le menu Données, vous constaterez que vous êtes assez libres quant aux critères de validation de données. Par défaut, le critère est "comprise entre". Et ça tombe bien puisque notre âge est compris entre 12 et 17. Inscrivez 12 dans Minimum et 17 dans Maximum. Cliquez sur Ok et essayez de taper, par exemple, 3 dans l'une de ces cellules paramétrées le compagnon Office vous renvoie un message d'erreur !
Il n'y a plus qu'à cliquer sur Annuler pour constater qu'Excel supprime toute valeur qui ne correspond pas à vos critères !
Personnaliser son message d'erreur
Nous avons vu qu’il y avait plusieurs onglets dans la fenêtre qui s'ouvrait après avoir cliqué sur Données > Validation (Sous 2007 : Données > Outils de données > Validation des données). Retournez sur cette fenêtre car c'est ici que vous allez pouvoir personnaliser votre message d'erreur correspondant à une saisie invalide ! N'oubliez pas de sélectionner les trois cellules concernées avant. Dans la fenêtre, cliquez sur l'onglet Alerte d'erreur.
Dans la liste Style, vous pouvez définir le type de boîte de dialogue qui apparaîtra. En pratique, cela ne change pas grande chose mise à part que l'icône de la boîte de dialogue sera différent selon son type. La fenêtre aura également un ou deux boutons de plus ou de moins selon son type. Laissons pour l’instant le style sur Arrêt. Vous testerez les autres styles ensuite.
Excel comprend que ce message doit apparaître quand des données non valides sont tapées grâce à la case à cocher du dessus qui est cochée
Il ne vous reste plus qu'à personnaliser le message d'erreur avec un titre et un message.
Cliquez sur Ok et essayez de taper une donnée non valide.
Dans la fenêtre ouverte après Données>Validation il reste un troisième onglet : message de saisie. Il vous permettra d'afficher un message lorsqu'une cellule aux données restreintes sera sélectionnée.
Si vous voulez afficher un pense-‐bête à la place, sélectionnez les cellules correspondantes et cliquez sur Insertion > Commentaire. Vous tapez ce que vous voulez et au passage de la souris sur la cellule concernée, le commentaire apparaîtra.
Nous nous sommes intéressés au menu Données et plus particulièrement à la validation de données. Dans cette sous-‐partie, nous resterons toujours dans la fenêtre de validation afin de créer une liste déroulante, qui facilitera la saisie. Voici ce que nous allons faire :
Tout d'abord, sélectionnez les trois mêmes cellules concernées dans notre tableau Pierre/Paul/Jacques. Cliquez sur Données puis sur Validation (Sous 2007 : Données > Outils de données > Validation des données).
Dans la liste Autoriser, choisissez Liste, qui permet de paramétrer une liste déroulante avec vos propres informations.
Pour compléter votre liste, il va falloir compléter le champ Source.
Deux solutions s'offrent à vous :
Cette méthode est tout à fait convenable pour des petites listes comme ici. Le problème, c'est que si vous décidez un jour d'agrandir votre liste, la manipulation sera un peu fastidieuse puisqu'il faudra sélectionner les cellules concernées, revenir dans cette fenêtre et enfin, modifier. Ici, cette méthode peut être utilisée puisque nous travaillons sur trois cellules.
Dans votre zone de travail, trouvez vous un coin à délimiter et dans lequel vous saisirez les données de votre liste. Avec la flèche rouge à droite du champ Source, sélectionnez ces données, revenez dans votre fenêtre et cliquez sur Ok. Vous avez de ainsi des listes déroulantes automatiquement construites à partir de cellules.
1.Qu’est-ce qu’une liste ?
Une liste, c'est une suite exploitable de données. C'est donc une liste de données.
Pourquoi une liste doit-‐elle être exploitable ?
Avec un exemple, vous allez comprendre :
Voici une liste de données. Ces dernières sont numériques mais ce n'est pas grave : elles auraient très bien pu être littérales, voire les deux à la fois :
Voici une autre liste, sous forme de tableau :
Cette liste est exploitable : elle délimite une plage rectangulaire de cellules.
Voici le même tableau mais non exploitable parce que la plage délimitée est
« mauvaise ».
La première solution est à préférer car l'analyse des données y sera plus facile.
Créons une liste de données exploitable comme dans l’exemple ci-‐dessous.
Rq : Votre tableau ne devra pas contenir de lignes intermédiaires vides, ni une ligne de fermeture !
Pour arriver à ce résultat, sélectionnez l'étiquette de colonne Prénom. Suivez le programme suivant :
Sous 2007, il va falloir faire une petite manipulation pour afficher un icône dans la barre d'outils d'accès rapide. En effet, le terme grille est devenu formulaire dans la version 2007 (depuis 2003).
Ensuite, il faut donc sélectionner l'étiquette de colonne Prénom et cliquer sur l'icône que nous venons d'insérer dans la barre d'outils d'accès rapide.
Une fenêtre s'ouvre, vous proposant de supprimer des lignes, d'en rajouter
Cliquez sur Nouvelle, vous pouvez maintenant compléter votre liste !
Choisissez un prénom, un âge, une classe puis cliquez encore une fois sur Nouvelle. Votre ligne est rajoutée !
Rq : Une liste de données, c'est en général long. Il est donc recommandé de n'avoir qu'une seule liste par feuille de calcul.
Les filtres, appliqués à une liste, permettent de visionner certains éléments de cette liste en fonction d'autres. Par exemple, vous avez un tableau qui contient les notes de 10 élèves dans 5 matières différentes. Grâce aux filtres, vous pourrez afficher uniquement les notes de tel élève, celles qui sont au-‐dessus de 10
Elles font partie de ce que nous pourrions appeler les « notions avancées d'Excel ». Peu de personnes pensent à les utiliser : leur puissance est donc négligée.
Avant de poursuivre, je vais vous demander de faire un petit tableau avec le nom de dix élèves et leurs notes dans 2 matières. Le tableau devra être de préférence assez long mais pas trop. Vous devriez avoir quelque chose comme ça :
Comme vous pouvez le constater cela peut rapidement devenir un véritable bazar ! Pourtant, il n’y a que 2 matières mais imaginez qu'on ait mis 35 élèves et 8 matières. Les filtres vont nous aider à faire un tri simple et efficace. Notre tableau est exploitable ce qui nous permet d’analyser les données qui s'y trouvent. Sélectionnez toutes les cellules qui composent ce fameux tableau puis suivez le programme de clics suivant :
Sous 2007, il faut sélectionner toutes les données et cliquer sur l'onglet Insertion et dans le cadre Tableaux sur Tableau.
À première vue, rien n'a changé mais penchez-‐vous sur les titres des colonnes :
Des listes déroulantes ! Ce sont elles qui vont filtrer vos données. Déroulez par exemple la liste de la colonne Note. Si vous sélectionnez 8, vous aurez dans votre tableau toutes les lignes dont la note est 8, en l'occurrence Mathieu ! Lorsqu'un filtre est activé, la flèche de la liste déroulante devient bleue.
Il est aussi possible de personnaliser les filtres.
Cliquez sur une des listes déroulantes et choisissez Personnalisé.
Une fenêtre s'ouvre :
À partir de cette fenêtre, vous pouvez exprimer toute sorte de conditions de filtre. Testez quelques filtres personnalisés.
Il est également possible d'analyser sa liste avec la fonction SOMMEPROD qui est d'une puissance exceptionnelle. Cette fonction permet de comptabiliser des données en multipliant des matrices entre elles. Pour être clair, elle permet de compter le nombre d'entrées d'une liste selon des
conditions mais aussi d'additionner des cellules d'une liste selon des conditions. Cette fonction s’utilise de la manière suivante :
On peut ainsi compter le nombre de ligne où la plage1 (colonne 1) est égale à critère1 et où la plage2 (colonne 2) est égale à critère2.
Prenons un exemple pour mieux comprendre.
Ce tableau représente les ventes de chaque vendeur d'un magasin sur les trois premiers mois de l'année. Il y a 4 vendeurs (Jean, Pierre, Paul, Jacques). Comment faire pour savoir le nombre de ventes de Paul au mois de Mars ? En utilisant la fonction SOMMEPROD !
Pour cela il faut entrer la formule suivante : =SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars"))
On obtient bien 3 ! Paul a bien fait 3 ventes au mois de mars. Maintenant on cherche à savoir combien d'argent a rapporté Paul au mois de Mars. Il suffit de multiplier par la colonne "Montant" de cette manière :
On obtient donc 2230. En effet la fonction a effectué le calcul suivant :
840+660+730=2230.
On peut faire plein de combinaisons avec cette formule ! Mais attention il y a quelques règles à respecter :
Toutes les plages doivent avoir la même taille et aucune colonne ne peut être prise entièrement en entrant (A:A). Cela dit, on peut la sélectionner en faisant (A1:A65535).
Etudions d'autres exemples pour bien comprendre et voir un peu ce que l'on peut faire avec cette fonction.
Exemple 1 : compter le nombre de ventes réalisées par Jean (on peut aussi réaliser cette opération avec la fonction ) :
=SOMMEPROD((A2:A31="Jean")*1)
On obtient ainsi : 8. Cet exemple n'est pas le meilleur pour montrer la puissance de la fonction mais elle montre qu'on n'est pas obligé d'avoir beaucoup de paramètres compliqués.
Exemple 2 : compter le nombre de ventes supérieures à 600€ au mois de Janvier :
=SOMMEPROD((B2:B31="Janvier")*(C2:C31>600))
On obtient ainsi : 2. On peut ainsi combiner les conditions pour prendre les valeurs comprises entre 200 et 600 par exemple.
Exemple 3 : totaliser la somme accumulée grâce à Pierre aux mois de Janvier et Mars :
=SOMMEPROD((A2:A31="Pierre")*((B2:B31="Janvier")+(B2:B31="Mars"))*(C2:C31))
On obtient ainsi : 2760.
Les tableaux croisés dynamiques représentent l'une des fonctionnalités les plus puissantes mais aussi les plus délicates à maitriser d'Excel. Une fois maitrisés, ils vous permettront de réaliser des gains considérables aussi bien en productivité qu'en qualité de travail.
1.Qu’est-ce qu’un Tableaux Croisé Dynamique (TCD) ?
Un tableau est fait de lignes et de colonnes, il permet donc typiquement de représenter de manière synthétiques un maximum de trois variables : V1 en lignes, V2 en colonnes et V3 dans les cases. Cette limitation est contournée artificiellement en ajoutant des lignes ou des colonnes supplémentaires car en pratique, il n'est pas rare d'avoir plus de 3 variables à analyser.
Autrement dit, dans un tableau croisé dynamique, on croise les données d'une liste afin de les analyser de différentes manières. Pour rappel, un tableur sert à saisir et à analyser des données. Nous avons vu comment saisir des données, nous les avons structurées sous forme de listes, analysées au moyen des formules et des filtres Les tableaux croisés dynamiques permettent d'analyser efficacement une longue liste, bien mieux que les filtres. Prenons un exemple.
Créons une liste de données :
Nous allons travailler avec cette liste dans la suite de cette section.
Faisons un Tableau Croisé Dynamique (TCD) afin de mieux comprendre son intérêt.
Sélectionnez votre liste de données, puis suivez le programme de clics suivant : Données > Rapport de tableau croisé dynamique
sous 2007 :
Laissez les options par défaut, cliquez sur Terminer (sans rien modifier).
Vous avez, sur une nouvelle feuille, le résultat suivant :
Cela fonctionne comme le cahier de notes des professeurs : à gauche, les noms, en haut, le devoir (date, coefficient) et au milieu, les données principales (autrement dit, les notes).
Dans la fenêtre, cliquez et maintenez le bouton gauche de la souris enfoncé sur Sexe et glissez-‐le jusqu'à la colonne de gauche du tableau pré-‐dessiné par Excel. Relâchez le bouton.
Suivant la même procédure, glisser-‐déposer Age sur la zone des champs de colonne.
Toujours suivant le même principe, glisser-‐déposer Score au milieu du tableau.
Vous obtenez le résultat suivant :
Vous avez des statistiques !
Le TCD est particulièrement utile et efficace sur les très longues listes (celles qui font des milliers de lignes).
Maintenant que vous avez votre TCD, il est temps de l'utiliser, de l'exploiter à 100 % !
Vous voyez que vous pouvez gérer de nombreux menus déroulants. Ils agissent comme
Supprimez la feuille sur laquelle se trouve votre TCD. Retournez à votre liste, sélectionnez-‐la et créez un TCD. Sauf que cette fois-‐ci, vous allez glisser-‐déposer Score au milieu (comme précédemment), Sexe et Prénom à gauche et Age au-‐dessus.
Excel sépare les hommes et les femmes, fait les comptes pour chacun des individus, puis en dernier lieu pour le groupe, le tout en fonction de l'âge.
Maintenant, vous savez créer un TCD, mais vous ne savez pas encore l'exploiter, ni même le modifier. En effet, quand vous fermez la fenêtre qui contient les champs à glisser-‐déposer, il semble impossible de modifier vos données depuis le TCD.
Créons une nouvelle liste (et par la suite un nouveau TCD):
Vous avez un nom, une ancienneté, une colonne salaire (appliquez-‐lui un format
Monétaire). Faites en sorte d'avoir au moins deux personnes avec la même ancienneté.
Créez le TCD de cette liste. Croisez vos données suivant cette manière :
Fermez la fenêtre qui contient les boutons à glisser-‐déposer vers le TCD. Normalement, vous obtenez le résultat suivant :
Vous avez la somme des salaires en fonction de l'ancienneté. Un total est fait.
c)Modifier le TCD
Essayons maintenant de modifier le TCD. Faites le manière suivante :
Une fenêtre s'ouvre : cliquez alors sur Disposition.
Une autre fenêtre s'ouvre avec le plan de votre TCD : au moyen de glisser-‐déposer, vous pouvez modifier ce dernier.
Mais ce n'est pas tout !
Double-‐cliquez sur Somme salaire.
Une fenêtre s'ouvre, vous proposant par exemple de modifier le nom de la série de données :
Dans la liste déroulante, vous pouvez remplacer la somme effectuée par le TCD par ce que vous voulez.
Le bouton Nombre permet de modifier le format de vos données numériques. Testez cette fonctionnalité.
Cliquez sur Moyenne puis validez toutes les fenêtres par OK.
Vous avez votre TCD avec modifications : maintenant, il fait la moyenne des données dont l'ancienneté est de 2, et de même quand elle est de 4.
La manipulation est différente sous 2007. Lorsqu'il y a un clic droit sur le tableau, une fenêtre apparait à droite de la page.
Vous pouvez modifier les données en déplaçant les en-‐têtes de colonnes dans les 4 cadres en bas à droite. Vous pouvez aussi modifier le tableau comme indiqué pour 2003 mais en cliquant sur les flèches, un menu se déroule à coté des titres.
d)Calculs de pourcentages
En général, quand on fait des statistiques, on a surtout besoin de pourcentages, surtout pour les longues listes.
Reprenez la liste avec l'ancienneté et faites une synthèse par Somme (faire la démarche inverse de ci-‐dessus).
Sélectionnez votre TCD, et suivez le programme :
Cliquez deux fois sur Somme Salaire (comme ci-‐dessus).
Cliquez sur Options. La fenêtre s'allonge. Dans la liste déroulante, choisissez % par colonne.
Validez toutes les fenêtres par OK puis Terminer.
Vous avez donc un TCD modifié dont nous allons étudié après.
Pour 2007, il suffit de faire un clic droit dans une des cases du tableau et de cliquer sur Format de nombre et ainsi de sélectionner comme on l'a déjà vu le format du nombre voulu (cf. chapitre sur la cellule).
Interprétation des résultats
La liste représente 4 salariés, classés en fonction de leur ancienneté. La troisième colonne indiquait le salaire de chacun.
Nous avons créé le TCD de cette liste, en mettant l'ancienneté à gauche et les salaires au milieu. Excel nous a donc fait un total de la somme déboursée par le patron pour la paye de ses salariés, des sous totaux étant faits par catégorie d'ancienneté.
Ensuite, nous avons appliqué un format Pourcentage à nos données des salaires. Si nous lisons le TCD, sur les 100 % d'argent débloqué par le patron, 50,6 % va aux anciens de 4 ans et 49,4 % aux anciens de 2 ans.
Si on le souhaite, l'ancienneté peut être remplacée par des champs d'âge ou tout autre champ de notre liste.
Avant de commencer, téléchargez le fichier exercice-‐.
Triez les données comme indiquées ci-‐dessous :
Que constatez-‐vous en le deuxième tri et le troisième ?
Supposons maintenant qu'on vous demande de remettre un rapport détaillé sur la région Nord, il n'y a aucun intérêt à s'encombrer des données concernant les autres régions.
Limitez les données affichées à celles relatives à la région du Nord qui vous intéresse.
Filtrez la région sur "Sud" et filtrez le vendeur sur "Jean", vous obtiendrez alors la liste des ventes effectuées par le vendeur Jean dans le Sud. Annulez les filtres et revenez à la liste initiale.
Supposons qu’on vous demande le bilan annuel des ventes (combien avons-‐nous vendu pour chacune des années ?). Mettez en place une solution permettant de fournir ces informations.
Faites de même avec :
La société PRETA commercialise des articles de prêt à porter.
On vous demande de concevoir un tableau, établi à partir des chiffres ci-‐dessous, faisant apparaître :
1-‐ le total des achats par mois pour chaque article
2-‐ le total des achats du trimestre par article
3-‐ le total des achats par mois.
Vous utiliserez, pour cela, les fonctions de consolidation (Menu « Données »,
« Consolider »). Nombres d'articles achetés au cours du trimestre (vous trouverez les tableaux suivants dans le fichier « »):
| janvier | février | mars | |
| jupes | 125 | 100 | 80 |
| pantalons | 44 | 95 | 45 |
| caleçons | . | 50 | . |
| chemises | 150 | 90 | 110 |
| janvier | février | mars | |
| jupes | 48 | 52 | 35 |
| pantalons | 25 | 35 | 30 |
| gilets | 10 | 25 | 25 |
| costumes | 18 | 11 | 12 |
| janvier | février | mars | |
| vestes | 25 | 18 | 22 |
| pantalons | 14 | 12 | 25 |
| pulls | 50 | 62 | 55 |
| chemises | 14 | 40 | 40 |
A partir de la liste de données contenue dans le fichier « », construisez des tableaux donnant les renseignements suivants :
A partir des données du fichier « » , concevez 2 graphiques :
Pour obtenir le deuxième graphique, vous devrez faire un deuxième tableau qui comportera :
-‐ une lise déroulante permettant de choisir un commercial
-‐ des fonctions de recherche pour récupérer les données de ce commercial
Le deuxième graphique sera lié à ce tableau.
Le résultat final devra ressembler à la capture d’écran suivante :
L’objectif de cet exercice est d’avoir un document Excel permettant d’analyser les informations sur les employés d’une entreprise. Nous allons exploiter les fonctionnalités des tableaux croisés dynamiques pour arriver à cette fin.
Vous pouvez retranscrire les données ci-‐dessous dans une feuille de calcul ou utiliser directement le document .
Placez le pointeur sur n'importe quelle cellule entre A1 et G16. Du menu Données, sélectionnez l'option Rapport de tableau croisé dynamique.
Excel vous demande où est située la source des données qui vont servir à composer le tableau croisé dynamique. Excel vous demande ensuite quel type de rapport que vous voulez: tableau ou graphique? Cette version d'Excel permet non seulement de générer un tableau mais aussi un graphique dynamique.
Pour les besoins de l'exercice, utilisez les mêmes options que sur le graphique ci-‐dessus
(Base de données Excel et tableau). Appuyez sur le bouton Suivant.
Excel vous demande de confirmer l'endroit où sont situées les données dont vous avez besoin pour le tableau croisé dynamique. Assurez-‐vous que les cellules sélectionnées soient bien entre A1 et G16. Appuyez sur le bouton Suivant.
Excel vous demande ensuite où vous voulez conserver le tableau croisé dynamique. Est-ce sur une nouvelle feuille de calcul ou sur la même qu'en ce moment?
Pour les besoins de l'exercice, sélectionnez l'option Nouvelle feuille. Vous pouvez appuyer sur le bouton Terminer et commencer à concevoir le tableau croisé dynamique
Excel a créé une nouvelle feuille de calcul avec la "coquille" d'un tableau croisé dynamique. Le début de la feuille démontre les quatre zones du tableau: page, ligne, colonne et données.
Il y a aussi la barre d'outils pour le tableau croisé dynamique qui devrait apparaître à côté de celui-‐ci. Voici ce que vous devez faire pour afficher la barre d'outils si vous ne la voyez pas.
-‐ Du menu Affichage, sélectionnez l'option Barre d'outils.
-‐ De la liste des barres d'outils disponibles, sélectionnez l'option Tableau croisé dynamique.
Il est possible aussi que vous ne voyiez pas la liste des champs qui compose la base de données. Pour l'afficher, placez le pointeur n'importe où à l'intérieur du tableau croisé dynamique.
A partir de maintenant, vous povez procéder de deux façons :
-‐ De la liste de champs de tableau croisé dynamique, sélectionnez le champ Salaire. De la liste des zones du tableau, sélectionnez la zone de données. Appuyez sur le bouton Ajouter à.
-‐ En gardant un doigt sur le bouton gauche de la souris, déplacez le champ dans la zone de données. Relâchez le bouton de la souris dès que le carré pour le champ Salaire est par-‐dessus la zone de données.
Le tableau indique maintenant que le total de tous les salaires de l'entreprise est de 394 400 $. La prochaine étape consiste à répartir ce montant par occupation dans l'entreprise. Encore une fois, il y a deux façons de faire :
-‐ De la barre d'outils Tableau croisé dynamique, sélectionnez le champ Titre. De la liste des zones du tableau, sélectionnez la zone de colonnes. Appuyez sur le bouton Ajouter à.
-‐ En gardant un doigt sur le bouton gauche de la souris, déplacez le champ dans la zone de colonnes. Relâchez le bouton de la souris dès que le carré pour le champ Titre est par-‐dessus la zone de colonnes.
Le tableau affiche maintenant le total des salaires par occupation (titre: Administrateur, Ouvrier ) toujours avec le total de 394 400 $. Le tableau affiche chacune des valeurs du champ Titre avec le total des salaires pour celui-‐ci. L'étape suivante consiste à répartir le total des salaires par titre et par sexe.
De la barre d'outils Tableau croisé dynamique, sélectionnez le champ Sexe. En gardant un doigt sur le bouton gauche de la souris, déplacez le champ dans la zone de colonnes. Relâchez le bouton de la souris dès que le carré pour le champ Titre est par-‐dessus la zone de colonnes.
Le champ Sexe va être automatiquement placé devant le champ Titre. À cause de la longueur du tableau, seulement une partie est affichée à l'image ci-‐dessus. Il est possible aussi de changer l'ordre de présentation des champs. La prochaine opération consiste à donner la priorité au champ Titre par-‐dessus Sexe.
Placez le pointeur par-‐dessus le champ Titre de la zone des colonnes du tableau croisé dynamique. En gardant un doigt sur le bouton gauche de la souris, déplacez le champ Titre devant le champ Sexe. Une fois devant le champ Sexe, relâchez le bouton de la souris.
Voici les mêmes informations que le tableau précédent mais affiché de manière différente. Les totaux des salaires pour les administratrices de l'entreprise sont toujours de 27 000 $ tandis que les hommes ont 126 500 $. Cependant, les informations sont maintenant regroupées par occupation et ensuite par le sexe. La prochaine opération va afficher les informations d'une manière un peu plus simple à comprendre.
Placez le pointeur par-‐dessus le champ Titre de la zone des colonnes du tableau croisé dynamique. En gardant un doigt sur le bouton gauche de la souris, déplacez le champ Titre dans la zone des lignes du tableau croisé dynamique (par-‐dessus Somme de la ligne). Une fois le champ est dans la zone des lignes, relâchez le bouton de la souris.
Bien qu'il s'agisse des mêmes montants que les deux tableaux précédents, les résultats sont plus clairs.
Excel vous permet de voir les enregistrements qui composent les résultats du tableau. La prochaine partie consiste à voir quels sont les enregistrements du total des administrateurs (153 500 $).
Placez le pointeur sur la cellule contenant le total des administrateurs (153 500$). Faites un double-‐clic sur la cellule.
Une nouvelle feuille de calcul va être créée avec les enregistrements qui correspondent au total des administrateurs. Vous pouvez refaire la même chose pour toutes les cellules du tableau croisé dynamique.
La prochaine opération est pour vous permettre de "filtrer" les valeurs dont vous avez besoin. Elle consiste à déterminer le total des salaires mais seulement pour les femmes. Le tableau croisé dynamique vous permet de "masquer" ou de cacher les valeurs dont vous n'avez pas besoin. Dans ce cas, il faut cacher les hommes.
À la droite du champ Sexe, cliquez sur le bouton avec un triangle pointant vers le bas.
Pour l'exemple, il y a seulement deux valeurs possibles: F ou M.
Le tableau croisé dynamique vous affiche une liste de valeurs qui sont dans les enregistrements.
Désélectionnez la case M parmi les valeurs possibles. Appuyez sur le bouton OK.
Ce nouveau tableau affiche le total des salaires pour toutes les femmes de l'entreprise.
Remarquez que la valeur "M" n'est pas affichée au tableau.
Réactivez la sélection M pour le champ Sexe.
Il y a une autre façon de filtrer les informations : en plaçant un champ dans la zone de pages. Pour cela, vous pouvez faire de deux façons :
Puisque le champ catégorie est la zone de pages, il vous est possible de filtrer toutes les informations du tableau. Le prochain exercice consiste à montrer les valeurs des employés qui sont de la catégorie 3.
Cliquez sur le bouton avec un triangle pointant vers le bas à la droite du champ
Catégorie.
Dans la liste des valeurs possibles, sélectionnez la valeur 3. Appuyez sur le bouton OK.
Voici le tableau du total des salaires pour tous les employés qui sont dans la catégorie 3. Ceci démontre qu'il est possible de filtrer les enregistrements qui composent le tableau croisé dynamique sur les champs qui le composent; qu'il soit placé dans la zone de ligne, la zone de colonnes ou la zone de pages.
Replacez le filtre pour le champ catégorie à Tous.
Vous avez créé un tableau croisé dynamique avec les champs et les critères dont vous avez besoin. Cette option vous permet d'améliorer la présentation de votre tableau.
Appuyez sur le bouton .
Il est possible de changer la présentation du tableau en sélectionnant l'un des formats prédéterminés. Vous pouvez changer d'avis en tout temps et prendre un format qui répond mieux à vos besoins.
Pour les besoins de l'exercice, vous pouvez changer la présentation selon votre goût.
Il y a des situations où il est préférable de représenter une masse de données sous forme de graphique. Il est avantageux d'utiliser un graphique pour:
Appuyez sur le bouton une première fois.
Excel va automatiquement générer un graphique de type histogramme. Ce graphique représente le total des salaires selon l'occupation et le sexe des employés de l'entreprise. Vous pouvez changer la présentation de ce graphique comme vous le feriez pour n'importe quel autre graphique. En plus, puisque c'est un graphique dynamique, il est possible de changer la présentation des données selon les champs qui ont été choisis.
Appuyez sur le bouton une seconde fois.
Ceci active l'assistant pour générer des graphiques. Il passe à travers les mêmes étapes que lors de la création d'un graphique avec des données de votre feuille de calcul..
Appuyez sur le bouton Terminer.
Nous allons maintenant changer la disposition de certains champs dans le tableau croisé dynamique. Cette partie va démontrer qu'il est possible de changer la présentation en ajoutant les champs Nom et Prénom à la zone des lignes.
Assurez-‐vous de placer le pointeur à l'intérieur du tableau croisé dynamique.
De la barre d'outils du tableau croisé dynamique, sélectionnez l'option Assistant tableau croisé dynamique.
L'assistant va recommencer les étapes pour créer un tableau ou graphique dynamique si le pointeur était à l'extérieur du tableau. Sinon, il va afficher immédiatement la troisième étape qui consiste à changer les options du tableau et de son emplacement dans le classeur.
Appuyez sur le bouton Disposition. Déplacez le champ Nom en dessous du champ Titre de la zone des lignes. Déplacez le champ Prénom en dessous du champ Nom de la zone des lignes.
Le résultat devrait ressembler à ceci.
Appuyez sur le bouton OK. Appuyez sur le bouton Terminer.
Voici une partie du nouveau tableau qui affiche maintenant dans la zone des lignes les champs Titre, Nom et Prénom.
Nous allons remettre à jour les données du tableau croisé dynamique après avoir fait une mise à jour dans la base de données.
Placez le pointeur dans la feuille de calcul avec la base de données. Placez le pointeur dans la cellule F11 (salaire de Karl Lalonde). Changer le salaire de 31 500 $ à 37 100 $. Retourner à la feuille de calcul ayant le tableau croisé dynamique. Appuyez sur le bouton
.
La somme partielle pour les ouvriers ainsi que le total des salaires devrait avoir changé à 79 600 $ et 400 000 $ respectivement.
Il est possible d'avoir dans une zone plusieurs champs pour mieux décrire les valeurs. Il est ainsi possible d'afficher ou de masquer les valeurs des champs qui sont à la droite du champ sélectionné. Si vous ne l'avez pas fait, ajoutez les champs Nom et Prénom à la zone des lignes.
Placez le pointeur sur le champ Nom. Appuyez sur le bouton .
Bien que le champ Prénom reste visible, les valeurs sont masquées. Elles ne sont pas affichées.
Cette option cache les valeurs des champs qui sont à la droite de cette dernière.
Pour information (à ne pas faire), il suffit d’appuyez sur le bouton pour que les valeurs du champ Prénom réapparaissent.
Cette partie va ajouter un même champ dans la même zone. Cependant, ils ne vont pas afficher la même chose. Le premier va afficher le nombre de personnes dans cette catégorie et le second va démontrer le total des salaires.
Vous pouvez faire au choix des deux façons suivantes :
Dans le tableau précédent, il y a présentement deux fois le total des salaires dans la zone des données. La prochaine partie consiste à changer les propriétés, les caractéristiques, ou les paramètres comme l'indique Excel, d'un champ pour ressortir le potentiel du tableau croisé dynamique.
Cliquez sur l'une des cases ayant le texte Somme SALAIRE.
.
Changez le nom du champ de Somme SALAIRE à Nombre. Changez l'option de synthèse à Nombre. Appuyez sur le bouton OK.
Ce champ affiche maintenant le nombre de personnes dans cette catégorie au lieu du total du salaire.
Cliquez sur l'une des cases Somme SALAIRE2. Appuyez sur le bouton .
Changez le nom du champ de Somme SALAIRE2 à Salaires. Appuyez sur le champ
Nombre.
L'option nombre vous permet de changer la présentation des valeurs du champ. C'est la même chose que les options Format, Cellule et Nombre pour une cellule du classeur. Mais ceci affecte un champ au lieu d'une cellule.
Parmi la liste des catégories, sélectionnez le champ Pourcentage. Appuyez sur le bouton OK. Appuyez sur le champ Options.
Un autre élément puissant des paramètres des champs est qu'il vous est possible d'afficher les valeurs par rapport à autre chose. Dans ce cas, nous allons demander d'afficher la valeur de champ par rapport au total des salaires.
Parmi les modes d'affichages, sélectionnez % du total. Appuyez sur le bouton OK.
Le tableau change de nouveau de forme pour montrer le nombre de personnes, par sexe, ainsi que leur pourcentage de salaire par rapport à la somme globale des salaires.
Nous allons maintenant regrouper des valeurs d'un champ. Nous allons regrouper les employés qui sont au siège social (administrateurs et secrétaires) de ceux qui sont "sur le terrain" (vendeur et ouvrier).
De la zone des lignes cliquez dans la case où il est écrit Administrateur. En gardant un doigt sur la touche CTRL, cliquez sur la case où il est écrit Secrétaire.
La touche CTRL vous permet de sélectionner plusieurs valeurs pour ensuite être capable de les regrouper.
Appuyez sur le bouton droit de la souris.
Ce menu contextuel vous montre plusieurs des options vues précédemment. Il est par moments plus facile d'utiliser le bouton droit de la souris que d'avoir à constamment retourner à la barre d'outils Tableau croisé dynamique. Il faut cependant maîtriser ces options avant de pouvoir les utiliser dans ce menu. Il y a cependant une option qui n'est pas ailleurs; celle de regrouper les valeurs d'un champ.
Du menu contextuel, sélectionnez les options Grouper et afficher le détail et Grouper.
Vous remarquerez qu'un nouveau champ s'est ajouté à la zone des lignes: Titre2.
Regroupez ensuite les valeurs ouvrier et vendeur ensemble.
Dans la zone des lignes, cliquez dans la case où il est écrit Vendeur. En gardant un doigt sur la touche CTRL, cliquez sur la case où il est écrit Ouvrier. Appuyez sur le bouton droit de la souris. Dans le menu contextuel, sélectionnez les options Grouper et afficher le détail et Grouper.
Il y a maintenant deux regroupements: groupe1 et groupe2. La prochaine partie consiste à améliorer un peu la présentation de ces groupes en changeant les noms du champ et des valeurs.
Pour renommer Groupe1 en Administration, vous pouvez faire au choix des deux manières suivantes :
Faire de même pour Groupe2 et renommer le en Terrain.
Il reste qu'à changer le nom du champ Titre2 à Regroupement.
Placez le pointeur sur le champ Regroupement. Appuyez sur le bouton . Changez le nom du champ de Titre2 à Regroupement. L'employeur a besoin d'une synthèse qui n'inclut pas les champs Titre, Nom et Prénom. On pourrait retirer les champs inutiles. Mais nous allons simplement les masquer pour l'instant.
Placez le pointeur sur la cellule ayant le texte Administration. Appuyez sur le bouton
. Placez le pointeur sur la cellule ayant le texte Terrain. Appuyez sur le bouton .
Voici un tableau intéressant ayant plusieurs données représentées de différentes manières. Il affiche le nombre de personnes qui travaillent au siège social et la proportion de la masse salariale qu'il représente. Mais il y a encore plus.
Le tableau croisé dynamique vous permet en plus d'ajouter des champs calculés. Ceci vous permet de ressortir de l'information à partir des données du tableau. Par exemple, peut-‐être que vous voudriez savoir le total des ventes des vendeurs même si on a seulement les montants par produits de l'entreprise. Il serait possible de créer un champ calculé qui additionne le montant de ces produits vendus par vendeur.
En plus des informations fournies dans le dernier tableau, l'employeur voudrait savoir à combien revient sa contribution à divers programmes tels que les assurances et le régime de retraite parmi d'autres. Cette contribution est égale à 50 % du salaire des employés. La prochaine partie consiste à ajouter un champ calculé qui calcule ce montant selon le salaire des employés.
Placez le pointeur sur le tableau croisé dynamique. Dans la barre d'outils pour le tableau croisé dynamique, sélectionnez les options Formules et Champ calculé.
Dans la case Nom, écrivez Cotisation. Dans la liste des champs, cliquez sur SALAIRE. Appuyez sur le bouton Insérer un champ. Cliquez dans la case Formule. Placez le pointeur après =SALAIRE. Ajoutez à la formule *0,5. Appuyez sur le bouton OK.
L'employeur connaît maintenant quel est sa contribution par catégorie et global. Pour votre part, vous savez maintenant comment ajouter un champ calculé à un tableau croisé dynamique.
Le dernier tableau donne les informations voulues par l'employeur. Cependant, il est possible d'améliorer la disposition des champs. En clair, faire un petit nettoyage avant de remettre le rapport. La prochaine partie consiste à placer les données des cotisations juste après le nombre de personnes par regroupement et de retirer de la zone des lignes les champs Titre, Nom et Prénom.
Placez le pointeur sur le tableau. Dans la barre d'outils du tableau croisé dynamique, sélectionnez l'option Assistant tableau croisé dynamique. Appuyez sur le bouton Disposition.
Pour changer l'ordre des données. Placez le pointeur sur le champ calculé Somme Cotisations de la zone des données. En gardant un doigt sur le bouton gauche de la souris, déplacez le champ entre Nombre et Salaires. En fois que le pointeur est entre les deux, relâchez le bouton de la souris.
Pour retirer des champs du tableau. Placez le pointeur le sur le champ Titre de la zone des lignes. En gardant un doigt sur le bouton gauche de la souris, déplacez le champ à l'extérieur des zones du tableau. Une fois que le pointeur est sorti du tableau, relâchez le bouton de la souris.
les filtres.
Répétez ces dernières opérations pour les champs Nom et Prénom.
Appuyez sur le bouton OK. Appuyez sur le bouton Terminer.
15.Ajouter des données au tableau croisé dynamique
Le tableau croisé dynamique est généré à partir de l’étendu de cellules que vous avez déterminé au moment de la création de celui-‐ci. Vous pouvez changer les données et
remettre à jour le tableau en appuyant sur le bouton . Cependant, il ne prendra pas en considération de nouvelles données que vous voudriez ajouter au tableau. Il est cependant possible de contourner cette limitation avec un peu de planification dès le début. Avant même de générer le tableau croisé dynamique, il faut donner un nom à l’étendu de cellules. Il est pratique de donnes un nom à des cellules pour pouvoir les utiliser dans vos formules mathématiques. Après tout, il est plus facile de comprendre =TotalRevenus – TotalCharges que =c285-‐g415. Excel vous permet de donner un nom à une cellule autant qu’à une étendue de cellules.
Pour cet exemple, il faut choisir l’étendu de cellules A1 à G16. Elle contient les titres ainsi que les données pour ensuite générer un tableau croisé dynamique.
Sélectionnez l’étendue de cellules A1 à G16. Dans le menu Insertion, sélectionnez les options Nom et Définir.
Dans la case Noms dans le classeur, entrez le titre Tableau1. Dans la case Fait référence à, assurez-‐vous d’avoir choisi A1 à G16. Ne prenez pas en considération les «
$ ».
Note : Le nom qu’on donne à des cellules ne peut pas contenir d’espaces. Vous pouvez utiliser le caractère « _ » (majuscule et -‐) pour relier des mots tel que Revenus_Janvier.
Appuyez sur le bouton OK.
Générez un nouveau tableau croisé dynamique. Cependant, la source est l’étendu
Tableau1 qui viens d’être déterminé. Retournez à la feuille de calcul ayant les données.
Ajoutez les données des lignes 17 et 18 au tableau. Dans le menu Insertion, sélectionnez les options Nom et Définir.
Sélectionnez le nom Tableau1. Changez l’étendu de cellules à A1:G18. Retournez au
nouveau tableau croisé dynamique. Appuyez sur le bouton pour mettre à jour le tableau qui inclura les nouvelles données.
Si vous songez ajouter des données à votre tableau, n’oubliez pas de premièrement donner un nom à votre étendu de données. Vous pourrez ainsi mettre à jour plus facilement votre tableau de cette façon.
Aussi flexible soit il, Microsoft Excel reste un logiciel destiné à une certaine utilisation; sa personnalisation est donc, comme pour n'importe quel autre logiciel, limitée. Afin de donner plus de liberté aux utilisateurs, Microsoft a doté l'ensemble de sa suite bureautique Office du langage de programmation VBA (Visual Basic for Application) seul moyen d'augmenter considérablement la flexibilité de ces outils. Malheureusement les langages de programmation sont compliqués à comprendre et à utiliser.
Pour remédier à cette situation, Microsoft a introduit une fonction appelée "Macro". Une Macro est un simple programme informatique écrit en VBA qui a la particularité de s'écrire automatiquement. En effet, Excel rédige à votre place le code VBA. Vous n'avez donc plus besoin d'apprendre le langage VBA pour accéder aux fonctionnalités offertes par la programmation.
a)Algorithme
On peut comparer un algorithme à une recette de cuisine. Les ingrédients nécessaires à la réalisation du gâteau sont les données ou entrées. L'algorithme est une suite d'actions qui produira des résultats ou sorties.
Les actions (ou instructions) qui composent un algorithme sont séquentielles, c’est-‐à-dire qu’elles se suivent et doivent être réalisées l'une après l'autre. Par exemple, on ne
peut pas faire cuire le brownie avant d'avoir mis la pâte dans le moule.
L’algorithme du brownie peut être résumé de la manière suivante:
b)Programme informatique
« Ecrire un programme revient à écrire un algorithme dans un langage compréhensible par l’ordinateur. »
Un programme informatique est une liste d'ordres indiquant à un ordinateur ce qu'il doit faire. Il se présente sous la forme d'une ou plusieurs séquences d'instructions devant être exécutées dans un certain ordre par un processeur, et comportant souvent des données d’entrées chargées dans la mémoire vive. Ces séquences d’actions/instructions sont appelées le code du programme.
Un programme informatique est la traduction d’un algorithme dans un langage compréhensible par la machine. Ce langage est appelé langage de programmation. Par exemple, le VBA est un langage de programmation.
Un même algorithme peut être écrit dans des dizaines de langages de programmations différents.
Ouvrir un nouveau classeur.
Afficher la barre d'outil Visual Basic avec le menu Afficher -> Barre d'outils -> Visual Basic.
Cette barre comporte les boutons suivants :
Permet d'exécuter une macro préenregistrée
Permet d'enregistrer une nouvelle macro
Permet de modifier les options de sécurité d'exécution des macros
Permet d'accéder à l'éditeur Visual Basic
Pour la gestion des macros sur 2007, il faut activer l'onglet Développeur. Pour cela, cliquez sur le bouton office (en haut à gauche), puis sur Option Excel. Dans la fenêtre qui s'ouvre (normalement sur l'onglet standard, sinon sélectionnez-‐le) puis cocher la case Afficher l'onglet Développeur dans le ruban.
Créez un nouveau document Excel et positionnez le curseur dans la cellule A1. Utilisez la barre d’outils VBA et cliquez sur , ou allez dans le menu Outils->Macro-
>Nouvelle macro …
Pour 2007 : Onglet Développeur > rubrique Code > Enregistrer une macro
4 options sont disponibles:
Cliquez sur le bouton OK pour continuer. La fenêtre d'enregistrement disparaît et une petite fenêtre :
Cette fenêtre va nous suivre tout au long de l'enregistrement de notre suite de commandes. La croix ferme la fenêtre, n'arrête pas la macro. Pour récupérer cette fenêtre en cours d'enregistrement, vous pouvez utiliser le menu Affichage -> Barre d'outils -> Arrêter l'enregistrement.
Le mode relatif ou absolu permet d'enregistrer les déplacements du pointeur dans les cellules ou non. Elle n'est disponible que depuis la version Excel XP et 2003. Notre premier exemple va clarifier cette option.
Le bouton "Référence absolu / relatif" ne dois pas être enfoncé, nous travaillons donc en absolu. Nous avons placé le curseur sur la cellule A1 avant de commencer l'enregistrement. Tapez les chiffres ci-‐dessus dans votre macro :
Dans la cellule A3, faites la sommes des 2 cellules supérieures, soit =somme(A1:A2) et
arrêtez l'enregistrement de la macro . Supprimez le contenu des 3 cellules et positionnez le curseur dans n'importe quelle cellule (sauf A1), par exemple en B2.
Nous allons exécuter notre première macro. Dans le menu Outils, sélectionnez la commande Macro -> Macros. Vous pouvez également utiliser le raccourci clavier + . Ceci fait apparaître la liste des macros disponibles pour ce classeur.
Utilisez la commande Exécuter. La cellule de départ B2 reçoit notre premier chiffre tapé durant l'enregistrement, soit 34. La cellule A2 reçoit le deuxième chiffre, effectivement tapé durant l'enregistrement en
A2. La cellule A3 reçoit effectivement la fonction Somme.
Que c'est-‐il passé ?
Dans le mode absolu, la macro enregistre les déplacements en mode absolu (la référence des cellules). Reprenons le cheminement de notre commande.
| Enregistrement | Exécution | |
| Avant | Le curseur est placé en A1 | Le curseur est placé en B2 |
| Début | Nous tapons 34, sans déplacer le pointeur | La macro tape 34 dans la cellule en cours, soit B2 |
| Nous déplaçons le curseur en A2 | Excel déplace le curseur en A2 | |
| Nous tapons 45 | Excel insère 45 dans la cellule en cours | |
| Nous déplaçons le curseur en A3 | Le curseur est déplacé en A3 | |
| Nous faisons la somme des 2 cellules supérieures (A1:A2) | La fonction somme des 2 cellules supérieures est insérée dans la cellule active |
En suivant ce tableau, Excel a strictement suivi les instructions de l'enregistrement.
Si nous recommençons la même macro en déplaçant le curseur vers la cellule A1 pendant l'enregistrement, la liste de commande tapera effectivement 34 dans la cellule
A1.
Nous venons d'utiliser la méthode absolue, essayons maintenant la méthode relative.
Effacez le contenu de la feuille et positionnez le curseur en A1 comme précédemment.
Créez une nouvelle macro. Lorsque la fenêtre arrêter l'enregistrement apparaît, cliquez sur le bouton relatif. Il doit avoir l'aspect coloré.
Tapons de nouveau 34 en A1, 45 en A2 et la somme de ces nombres en A3. Fermer l'enregistrement, effacez le contenu et positionnez le curseur en B2 par exemple. Exécuter la macro relative.
Le résultat est maintenant différent.
34 est bien repris dans notre cellule de départ, soit B2. Vous pouvez positionner votre curseur n'importe où dans votre feuille, la suite de chiffre est toujours tapée à l'endroit de départ.
Effaçons le contenu de notre feuille et positionnons le curseur dans la cellule B2 par exemple. Commençons l'enregistrement d'une nouvelle macro en mode relatif.
Comme opération, descendons le curseur de 2 lignes et tapons un chiffre. Terminez l'enregistrement et exécutez la macro.
Quel que soit la position de votre curseur avant l'exécution, le chiffre 34 est toujours tapé 2 cellules en dessous.
Remarque: vous pouvez passer d'un mode à l'autre en cours d'enregistrement.
A retenir: Lorsque vous enregistrez une macro Excel en mode absolu, l'exécution suit les mouvements du pointeur en absolu, suivant la référence exacte de la cellule. Par contre, enregistrer une macro en mode relatif enregistre les déplacements suivant la différence du
nombre de lignes et de colonnes.
En mode absolu, le curseur se déplace vers une référence de cellule bien définie. Par contre, en relatif, l'enregistrement se fait sur le déplacement. Par conséquent, en relatif, l'exécution peut déplacer le curseur sur des cellules inexistantes !
Comme exemple, positionnons le curseur en B5 et débutons l'enregistrement d'une macro en mode relatif.
Déplacez le curseur en A1 (soit -‐1 colonne et -‐ 4 ligne) et tapez par exemple « cours informatique ». Arrêtez l'enregistrement. Positionnez votre curseur en B6, le texte est effectivement tapé en A2. Par contre, placez votre texte en A2 et exécutez la macro. La fenêtre de débogage apparaît
.
Ce type d'erreur n'apparaît jamais en absolu sauf si vous déplacez le curseur vers une feuille préalablement effacée.
Il y a 4 méthodes possibles pour exécuter une macro Excel, c'est identique en Word.
a)Bouton dans une barre d'outils ou dans un menu
C'est la méthode la plus courante.
Allez dans le menu Affichage->Personnaliser et sélectionnez l’onglet Commande.
Sélectionnez dans les catégories Macros. Glissez le "bouton personnalisé" vers une boîte d'outils d’Excel. Le résultat doit être similaire à ceci:
Cliquez avec le bouton droit de la souris sur ce bouton (menu contextuel) pou faire apparaître le menu suivant:
o Réinitialiser permet de remettre le bouton de départ en cas de modifications
o Supprimer permet de supprimer ce bouton de la barre d'outils. L'autre solution est de glisser ce bouton en bas de la barre d'outils.
o Nom permet de donner un nouveau nom à ce bouton.
o Copier l'image du bouton permet de copier l'icône pour l'utiliser sur un autre bouton personnalisé.
o Coller l'imagedu bouton permet de reprendre une image copiée.
o Rétablir l'image du bouton permet de reprendre l'aspect avant modification o Editeur de bouton permet de dessiner l'icône
o Modifier l'image du bouton propose des icônes différentes
Les 4 lignes suivantes du menu sont liées à l'aspect de cette commande dans la barre d'outils: o Par défaut : icône
o Texte seul : le texte tapé comme nom
o Masquer les images sur les menus : options pour ne plus afficher les icônes à coté des commandes dans les menus
o Image et texte : affiche le bouton et le nom dans la barre d'outils Créez un nouveau bouton pour la macro faite dans la section précédente.
Nous pouvons directement affecter une macro par ce menu, mais si vous ne le faites pas, en cliquant sur le bouton la première fois (après avoir quitter le mode personnalisé),
Excel le propose automatiquement et affiche les macros disponibles.
Affectez au bouton la macro crée précédemment.
Cette méthode permet également de modifier, ajouter ou supprimer des commandes dans les menus.
Vous pouvez également exécuter une macro en lui associant un raccourci clavier. Pour créer ce raccourci, reprenez votre fenêtre de macros. Sélectionnez la macro souhaitée et cliquez sur le bouton Options.
Vous pouvez ainsi affecter le raccourci souhaité. Seul sont autorisés les combinaisons de touches suivantes : o les touches ALT -‐ CTRL et ALTGR (ou 2 ensembles) o Shift (optionnel)
o 1 lettre ou 1 chiffre -‐ Excel distingue les chiffres du pavé numérique de ceux du clavier standard.
Affectez le raccourci CTRL+ALT+1 à votre macro et testez-‐le.
>Barre d’outils->Dessin).
Pour 2007 : onglet Développeur, rubrique Contrôles -> Insérer -> Bouton de contrôle
Utilisez le bouton dans la barre d'outils pour créer un bouton directement sur votre classeur Excel. La fenêtre pour affecter une macro apparaît immédiatement.
Il ne vous reste plus qu'à sélectionner la macro et à tester le bouton.
Nous allons faire une macro simple qui va supprimer l'affichage du quadrillage d'Excel. Puis nous ferons évoluer cette macro en modifiant le code pour faire une commande permettant de « Afficher/ne pas afficher » le quadrillage. Enfin nous verrons comment affecter cette macro à un bouton de commande.
Cliquez sur Enregistrer une macro dans la barre d'outils VB , il apparaît alors une boite de dialogue comme suit :
Cliquez sur Ok. Une nouvelle barre flottante apparait au milieu de l'écran :
Ne pas la fermer et la laisser flottante dans l'écran, c'est l'élément visuel qui vous rappelle que vous êtes en train d'enregistrer une macro. Notez aussi que le bouton
enregistrer s'est transformé en bouton arrêt.
Effectuez les tâches Excel que vous souhaitez automatiser. Dans notre cas, on souhaite enregistrer la désactivation du quadrillage.
Pour faire cela, allez dans le menu Outils->Option et décocher l'option "Quadrillage" dans la zone fenêtre de l'onglet "Affichage" validez par Ok, le quadrillage d'Excel doit avoir disparu.
Cliquez sur le boutonArrêt d’enregistrement , la barre flottante disparaît.
Réaffichez le quadrillage en allant dans le menu outils->options. Cliquez sur le bouton exécuter de la barre d'outil VB. La fenêtre de dialogue Exécution s'affiche
Choisir la macro que vous venez d'enregistrer en cliquant sur son nom. Cliquez sur Exécuter et le quadrillage disparaît.
Cliquez sur le bouton exécuter de la barre d'outil VB. La fenêtre de dialogue
Exécution s'affiche
Choisir la macro que vous venez d'enregistrer en cliquant sur son nom. Cliquez sur Modifier. Vous basculez dans le mode "Editeur Visual Basic" : Notez que dans votre barre de tâche se trouve les 2 icones "Votre Classeur Excel" et "Microsoft Visual Basic", vous pouvez basculer de l'un à l'autre comme pour n'importe quelle application Windows en cliquant sur l'icone souhaitée.
L'explorateur ressemble à d’autres applications Windows, il comporte des barres de menus et d'outils, et différentes fenêtres affichables. Nous reviendrons sur les barres d'outils et de menus plus loin et au cours des différentes leçons en fonctions des besoins.
La zone encadrée en jaune dans la figure représentant l’Editeur Visual Basic affiche tous les composants intégrés au projet Excel:
o Le classeur (Workbook) o Les feuilles (Sheet)
o Les Modules (qui contiennent le code des macros) o Les UserForms (Formulaires utilisateur ou Interface client)
La zone encadrée en rouge dans la figure représentant l’Editeur Visual Basic est la fenêtre propriétés du module Visual Basic. Elle est utilisée pour créer des "UserForm" et insérer des contrôles dans les classeurs et leurs feuilles.
La zone encadrée en rouge dans la figure représentant l’Editeur Visual Basic permet de visualiser et de modifier le détail des procédures ou fonctions en Visual Basic.
Lorsque l’on étudie cette dernière fenêtre, on constate qu’elle contient le programme créé automatiquement lors de l’enregistrement de la macro.
Votre macro commence à SUB et se termine à End Sub c'est ce qu'on appelle en Visual Basic une procédure. Le code est exécuté de haut en bas dans l'ordre des lignes de programmation. Une seule instruction par ligne. Les lignes précédées d'une apostrophe (en vert dans mon exemple) sont les commentaires non exécutables. Le mot clé "Sub" est suivi du nom que vous avez donné à votre macro. L'instruction donnée est : ActiveWindow.DisplayGridlines = False, que l'on peut traduire de la manière suivante: « Mettre à faux la valeur de la propriété "DisplayGridlines" (le quadrillage) de la fenêtre active ».
Nous allons maintenant modifier le code de cette macro pour faire une commande permettant de « Afficher/Masquer » le quadrillage (au lieu d’uniquement masquer).
Nous allons utiliser une variable qui stockera la valeur contenue dans la propriété "DisplayGridline" de l'objet "ActiveWindow". Ensuite nous affecterons l'inverse de cette variable (donc de la valeur qu'elle contient) à la propriétés DisplayGridline.
Le code devient donc :
Sub Quadrillage()
' Commande à bascule afficher/désafficher le quadrillage d'Excel
'je stocke la valeur de DisplayGridlines dans MaVariable
MaVariable = ActiveWindow.DisplayGridline
'j'affecte l'inverse (Not) de la valeur de MaVariable dans DisplayGridlines
ActiveWindow.DisplayGridline = Not(MaVariable)
End Sub
Tester la macro qui doit à chaque exécution afficher ou masquer le quadrillage d'Excel.
L’objectif est d’insérer un bouton dans les barres d'outil d'Excel qui exécutera la macro
Quadrillage.
Faites un clic droit sur la barre d'outils Excel (pas dans l'éditeur VBA). Vous obtenez le menu "Barre d'outils".
Choisissez Personnaliser. Vous obtenez la boite de dialogue "Personnalisation"
Choisissez Macros dans la zone de gauche. Faites glisser (clic maintenu) le bouton
"smile" dans une des barres d'outils d'Excel à l'endroit ou vous le souhaitez :
Faites un clic droit sur ce bouton (sans avoir fermer la boite "Personnalisation"). Le choix "Modifier le bouton" permet de changer l'image du bouton si besoin. Le choix Nom permet d'affecter un texte sur le bouton, il suffit ensuite d'aller dans "texte seul" ou "image et texte" pour afficher ce texte avec ou sans l'icone dans le bouton.
Choisissez ensuite "Affecter une macro". Dans la boite, choisissez votre macro
Quadrillage et cliquez sur Ok.
Fermez la boite Personnalisation par "Fermer".
Testez le fonctionnement de votre bouton
En plus des dizaines de fonctions dites natives contenues en standard dans Excel et accessibles depuis le menu Insertion > Fonctions ), Microsoft Excel dispose de fonctions supplémentaires appelées macros complémentaires. Une macro complémentaire peut être définie comme étant un groupe de fonctions (ou de procédures) visant à apporter une palette de fonctionnalités additionnelles généralement cohérentes entre elles.
A titre d'exemple, Excel propose un groupe de macros complémentaires appelé "Utilitaire d'analyse" dédié à l'analyse statistique des données qui s'avère être particulièrement utile et puissant lorsque l'on a des données à analyser en profondeur et que les fonctions standards ne suffisent donc plus.
Ces macros n'étant pas standards, elles ne sont pas nativement disponibles et doivent donc d'abord être installées avant de pouvoir être utilisées.
Pour accéder au menu des Macros Complémentaires, il suffit d'utiliser Outils > Macros complémentaires Dans la fenêtre qui s'affiche, il faut cocher le groupe de macros à installer et cliquer sur OK. Excel vous avertira que la macro n'est pas installée et vous offrira la possibilité de l'ajouter, pour cela vous aurez généralement besoin du CD d'installation. Une fois installées, ces macros seront disponibles à l'utilisation.
Pour aller plus loin dans la compréhension et l'utilisation de ces macros complémentaires, je vous propose de créer notre propre fonction personnalisée qui puisse répondre à nos attentes spécifiques. Pour cela, on devra passer par la création d'une macro complémentaire.
Supposons que vous souhaitez connaitre ce que votre banque vous facturera pour chaque transaction en utilisant une fonction personnalisée appelée "FraisBanque()".
Supposons aussi que votre banque ait la politique tarifaire suivante : 3% de frais sur le montant de la transaction si le montant est inférieur à 100 Euros et 2% sinon avec une commission fixe de 2 Euros quel que soit le montant.
Il est évident qu'une simple fonction SI peut faire l'affaire mais ici l'objectif est de comprendre le fonctionnement des macros complémentaires mais l’intérêt de créer cela sous la forme d’une fonction personnalisée est de pouvoir réutiliser ce calcul directement d’autres classeurs tout comme n’importe quel autre fonction (p.ex. somme).
Nous allons procéder en quatre étapes.
a)Réaliser le programme VBA
La première étape consiste à "écrire" le programme VBA associé à cette nouvelle fonction (macro).
Pour cela il faut ouvrir un nouveau classeur Excel et se rendre dans l'éditeur Visual Basic via Outils -> Macro -> Visual Basic Editor (vous pouvez aussi utiliser le raccourci clavier Alt + F11) puis ajouter un module en utilisant Insertion -> Module.
