Tutoriel : Analysez des données avec Excel

Excel est un logiciel d'analyse de données !

Justement, si ! Et vous allez voir que prendre 5 minutes pour réfléchir à la manière de considérer un logiciel peut apporter beaucoup de choses.

Excel et l'analyse de données

Comme son nom l'indique, un logiciel d'analyse de données a pour fonction principale d'« analyser » des données. Autrement dit, il fait subir à des données brutes des transformations de toutes sortes (mise en forme, calculs, gestions, etc.) en vue d'une utilisation spécifique. Vous n'analysez pas une facture de la même manière que vous analysez un bulletin de paye ! Analyser des données, ce n'est donc pas simplement les rendre jolies mais c'est leur créer une association pour les rendre utilisables.

Ce genre de logiciels est une solution possible à la création de bases de données. Par exemple, durant 30 ans, vos parents ont enregistré sur leur magnétoscope des dizaines et des dizaines de cassettes. Pour s'y retrouver, chaque cassette pourrait recevoir un numéro et on pourrait les ranger 15 par 15 dans des boîtes.

Vous pouvez utiliser Excel pour "archiver" votre collection de vieilles cassettes, d'abord par numéro, mais on pourrait imaginer un champ qui vous indiquerait quels documents filmographiques sont sur chaque cassette, ainsi que le genre (théâtre, film d'action, documentaire, etc.).

Là où Excel devient intéressant, c'est que, en plus de vous offrir ce qu'il faut pour mettre en forme votre base de cassettes, on peut les exploiter. Par exemple, vous pouvez lui demander de filtrer les 254 cassettes selon leur genre ("renvoie-moi toutes les cassettes de théâtre"), de les compter selon moult critères (genre, année, etc.).

Bien entendu, ces exemples de traitement sont très basiques, mais ça peut vous donner une idée :) .

Excel n'est pas tout seul !

Pour votre culture, sachez qu'Excel est développé par Microsoft, la célèbre firme qui maintient et vend le système d'exploitation Windows. Excel reste un logiciel assez coûteux, ce cours n'a pas pour but de vous le faire acheter en le présentant comme une solution miracle. Nous travaillons selon l'hypothèse que vous possédez déjà le CD, ou que vous utilisez ce logiciel à l'école ou au travail.

Il faut savoir que d'autres entreprises éditent des logiciels d'analyse de données. Par exemple, vous pouvez regarder Apple et sa suite iWork, avec son logiciel Numbers.

Logo de la LGPLv3, licence libre de LibreOffice

Mais si vous ne savez pas quoi prendre parce que vous débutez vraiment dans la bureautique, vous pouvez télécharger une suite gratuite, qui contient un tableur (comme Excel). Vous pouvez vous tourner vers LibreOffice, téléchargeable directement sur le Net. L'avantage de cette suite, c'est qu'il s'agit d'un projet libre (c'est-à-dire que vous êtes notamment libre de récupérer, étudier, modifier et redistribuer le logiciel). Sa communauté est très présente, et un débutant peut facilement obtenir de l'aide.

Il faut enfin savoir que si vous choisissez un tableur différent d'Excel, ce cours peut vous être utile dans la mesure où les outils de base de ces logiciels sont les mêmes.

Mais continuons selon l'hypothèse que vous possédez déjà Excel et que vous voulez apprendre à tirer profit de ce logiciel. Voyons d'abord comment l'installer.

Parlons à présent du processus d'installation. Le processus d'installation est très simple, et rapide.

Une fois que vous avez cliqué sur le fichier du programme d'installation, vous arrivez sur une fenêtre comme celle-ci :

Première étape : la saisie de la clé

Pour cette première étape, vous devez entrer la clé de produit. C'est la clé que vous avez achetée dans la licence. Elle est indispensable pour continuer l'installation de la suite.

Après une seconde étape concernant l'acceptation des termes du contrat, on passe à la troisième étape, que voici :

Troisième étape : choix du type d'installation

Ici, vous devez choisir le type d'installation :

• Si vous cliquez sur « Installer maintenant », vous passerez directement à l'installation standard ;

• Si vous cliquez sur « Personnaliser », il vous sera possible de configurer certaines options sur les différents logiciels installés dans le pack, ainsi que sur votre nom d'utilisateur et vos initiales. Pour ces dernières, vous pourrez les configurer plus tard, lors d'une prochaine utilisation de l'un des logiciels de la suite. Bref, ce n'est pas du tout définitif.

Et enfin, clé du succès, la dernière étape... l'installation de la suite !

Et ensuite ? À vous l'accès à tous les logiciels de votre édition d'Office ! Vous pouvez alors vraiment commencer le tutoriel et les manipulations de documents Excel.

Nous allons ici faire une petite visite guidée de l'interface du logiciel.

L'interface, c'est ce qui vous tombe sous le nez quand vous ouvrez Excel.

Démarrer Excel

Pour démarrer Excel, vous pouvez :

Vous rendre dans le menu « Démarrer », puis dans « Tous les programmes », dans le dossier « Microsoft Office », sélectionnez « Microsoft Office Excel 2010 » :

Cliquez directement sur « Microsoft Excel 2010 » en l'ajoutant dans le menu « Démarrer » :

Cliquez directement sur « Microsoft Excel 2010 » en l'ajoutant dans la barre des tâches :

Vous venez d'ouvrir Excel, et vous êtes face à une interface ma foi plutôt agréable à regarder.

Et encore heureux, car c'est là-dessus que nous allons travailler jusqu'à la fin (ou presque) de ce cours ! :)

Voici donc, sans plus attendre, l'interface de Microsoft Office Excel 2010 :

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 ».

Le ruban

Tout en haut de la fenêtre, il y a ce que l'on appelle le « ruban ». C'est l'une des plus grosses mises à jour d'Office 2007, reprise dans Office 2010 : un ruban, graphiquement évolué, dans lequel sont disponibles beaucoup de fonctionnalités du logiciel.

Dès l'arrivée sous Excel, l'onglet « Accueil » est ouvert dans le ruban, donnant libre accès aux fonctionnalités les plus basiques du logiciel d'analyse de données.

À gauche de cet onglet se trouve le menu « Fichier », en vert - anciennement menu Office -, proposant un panel d'options diverses et variées sur les classeurs Excel, ainsi que sur le logiciel lui-même. Toutes les options, basiques ou avancées, permettant de configurer et de personnaliser Excel, se trouvent ici.

À droite se trouve un très large choix de fonctionnalités du logiciel d'analyse de données, accessibles grâce à cette fameuse structure en onglets. Ces onglets peuvent par ailleurs varier. D'autres onglets pourront apparaître au cours de l'utilisation d'Excel, notamment si vous suivez le cours, pour vous proposer un panel de fonctionnalités supplémentaires. Ce sont des onglets contextuels. Par exemple, si vous éditez une image ou un tableau, un ou plusieurs onglets contextuels réservés à cet effet seront automatiquement affichés.

Comme vous pouvez le voir, chaque onglet du ruban Office est lui-même divisé en plusieurs groupes. Ils permettent de mieux se repérer et de ne pas avoir une foule de fonctionnalités dans un espace aussi réduit. Ainsi Excel a séparé toutes les fonctionnalités selon leur utilisation : la mise en forme de la police dans un groupe, la mise en forme des paragraphes dans un autre, etc.

Mais n'oublions pas qu'Office 2010, en l'occurrence Excel, possède plusieurs centaines de fonctionnalités. Imaginez toutes ces fonctionnalités dans le ruban, ce serait tout de même un peu brouillon, ou alors vous auriez cent onglets !

C'est la raison pour laquelle certains groupes de fonctionnalités ne sont pas complètement affichés. Vous pourrez alors cliquer sur l'icône d'agrandissement en bas à droite de chaque groupe pour avoir accès à une fenêtre contenant toutes les fonctionnalités du groupe.

La barre d'outils Accès rapide

Plus haut encore se trouve la « barre d'outils Accès rapide » à partir de laquelle vous pouvez en fait ouvrir et enregistrer des fichiers et accéder à beaucoup de fonctions d'Excel (les mêmes que dans le ruban, nous y reviendrons en temps voulu).

La barre d'Accès rapide marche comme la barre de lancement rapide de Microsoft Windows. Elle permet l'accès rapide aux fonctionnalités globalement les plus utilisées d'Excel, en règle générale.

L'icône Excel ne fait pas partie de la barre d'accès rapide. Elle simule simplement le clic droit sur la barre de titre.

Par défaut, les fonctionnalités suivantes sont déjà présentes :

• Enregistrer (dont le raccourci clavier est Ctrl + S , notez-le !) : permet d'enregistrer votre document (nous allons d'ailleurs voir cette fonctionnalité vers la fin de ce chapitre ;

• Annuler la dernière action (dont le raccourci clavier est Ctrl + Z , notez-le lui aussi) ;

• Répéter la dernière action (dont le raccourci clavier est Ctrl + Y , un petit dernier à connaitre pour la route).

Ce sont en effet les trois fonctionnalités les plus utilisées, quel que soit le document que vous éditez. Néanmoins, il vous est possible d'ajouter plusieurs autres fonctionnalités. Cliquez simplement sur la flèche descendante à droite de cette barre. Elle vous permet d'ajouter (ou supprimer) plusieurs fonctionnalités telles que la création d'un nouveau fichier ( Ctrl + N ), l'ouverture d'un fichier enregistré auparavant sur votre disque dur ( Ctrl + O ), l'impression ( Ctrl + P ), la correction orthographique ( F7 ), etc.

Nous apprendrons au fur et à mesure du tutoriel à nous servir de chacune de ces fonctionnalités, vous pourrez alors adapter cette barre d'accès rapide à votre utilisation personnelle d'Excel.

Par ailleurs, inutile de retenir les raccourcis des fonctionnalités que j'ai donnés en deuxième temps. Sachez à ce sujet qu'une annexe est disponible à la fin du cours et vous donne un résumé de tous les raccourcis utiles sur Excel. Mettez-le de côté, il pourra vous servir !

La barre de formules

Entre votre zone de travail et le ruban se trouve un champ très important : la « barre de formules ».

Cette zone est très importante et mérite un chapitre pour elle toute seule (et elle en aura un). Pour le moment, ne compliquons pas les choses, retenez que ce champ très long se nomme « barre de formules ».

Notez néanmoins que la liste déroulante à gauche du champ ne fait pas partie de la barre de formules.

La barre d'état

En dessous de votre zone de travail se trouve la barre d'état.

Dans cette barre de trouvent les « ascenseurs », entourés là encore de nombreux autres boutons. Ces ascenseurs vous permettent de vous déplacer dans le quadrillage. Leur utilisation n'est pas très compliquée, il suffit de cliquer sur les flèches pour bouger. :)

Vocabulaire

Lorsque vous ouvrez Excel, vous ouvrez ce que l'on appelle « un classeur ».

Il s'agit d'un classeur pour la simple et bonne raison qu'il est lui-même composé de différentes feuilles de calculs.

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 par classeur mais sachez que par défaut, il y en a trois dores et déjà créées.

Afin de ne pas vous noyer dans les informations, je ne vais pas expliquer ici comment ajouter / supprimer / renommer des feuilles. Par contre, je l'explique dans cette annexe, à lire quand vous voulez ; à l'occasion d'une pause, par exemple.

Chaque case de la zone de travail est appelée « cellule ».

Nous la repérons dans le tableur grâce à ses « coordonnées ».

Regardez votre zone de travail : il y a des cellules, mais aussi des numéros de colonnes et de lignes. Une cellule sélectionnée, ou encore nommée « cellule active », est caractérisée par la lettre de sa colonne et le numéro 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. :D

La suite Office n'est pas uniquement dédiée à Windows. Microsoft propose pour les utilisateurs de Mac OS X une version d'Office (donc avec Excel dedans). Nous présenterons dans ce cours la version 2011, c'est-à-dire la dernière. Son interface est extrêmement ressemblante à celle d'Excel pour Windows (mis à part quelques détails).

Dans tout le cours, je me baserai sur Excel pour Windows. Je rajouterai à chaque fois que nécessaire un petit paragraphe pour travailler sous Mac. Dans le cas contraire, c'est qu'il n'y en a pas besoin, tant l'affichage sous Mac pourra ressembler à l'affichage sous Windows.

Dès que j'ai quelque chose à ajouter pour une manipulation particulière sous Mac, je le signalerai par l'icône :

. Voyons sans plus attendre comment ouvrir un classeur sous Mac :) .

Ouvrir un classeur

Il faut tout d'abord vous rendre dans votre dossier « Applications », accessible depuis le « Finder » de Mac OS X. Il ne vous reste plus qu'à trouver votre copie d'Excel et de l'ouvrir. Une fenêtre s'ouvre, vous proposant de charger plusieurs modèles prédéfinis :

Pour découvrir et apprendre, nous avons besoin d'un classeur vide, choix proposé par défaut. Cliquez donc sur « Choisir ».

Votre classeur s'ouvre :

Je ne vous ai pas menti en vous disant que l'interface était extrêmement ressemblante à celle de Windows :D . Vous êtes donc fins prêts pour continuer et n'oubliez pas que le cours se base sur la version Windows ! Si jamais il y a une manipulation différente sous Mac, vous en serez avertis.

Résumons

• Mot à mot, la bureautique vous permet par le biais de logiciels de faire votre travail de bureau (comptabilité, rédaction de courriers) sur l'ordinateur.

• Les tableurs permettent bien plus que faire de la simple comptabilité. On peut les assimiler à des machines à calculer sophistiquées, capables également de fournir des analyses de données complètes sous diverses formes (tableaux, graphiques, etc.). Excel est un tableur, c'est le logiciel que nous allons apprendre à utiliser dans ce cours.

Vous connaissez à présent le sujet du tutoriel : Excel. Vous avez installé la suite Office (la suite bureautique à laquelle appartient le logiciel d'analyse de données Excel) et vous en connaissez les principales parties de son interface.

J'espère que vous avez compris les différentes utilisations des parties qui composent son interface, puisque dès le prochain chapitre, vous allez apprendre à l'utiliser.

Vous pensiez réellement pouvoir vous tourner les pouces encore longtemps ? :p

Nous allons commencer par créer un nouveau classeur. Vous allez voir, c'est tout simple.

Rendez-vous dans le menu « Fichier. »

Je vous en avais rapidement parlé lorsque je vous avais expliqué l'utilité des différences sections de l'interface. Le menu « Fichier », c'est le petit bouton vert qui est situé à gauche de l'onglet « Accueil » (le premier « vrai » onglet du ruban, qui est accessoirement l'onglet ouvert dès le lancement d'Excel). Or, je vous avais précisé que l'onglet « Fichier » est en fait un menu qui occupe toute l'interface, lorsqu'il est ouvert.

Voyons cela en image :

Ok, vous y êtes ?

Cliquez sur « Nouveau », justement en dessous de « Récent », sélectionné en bleu dès votre arrivée dans le menu. Vous arrivez dans un nouvel onglet du menu (des onglets ? Encore ! :-° ). A partir de cette page, vous n'avez qu'à double-cliquer sur « Nouveau classeur », présélectionné par défaut :

Vous voilà avec un nouveau classeur tout beau tout blanc tout vierge.

Vous avez devant vos yeux votre première feuille divisée en une multitude de « cellules », repérées grâce à leurs coordonnées COLONNE LIGNE.

Maintenant que nous avons créé notre feuille de cellules, nous allons voir ce qu'est une cellule.

Elle est caractérisée par ses coordonnées COLONNE LIGNE. Par exemple, une cellule dans la colonne G et à la ligne 8 aura pour coordonnées G8. :D

Mais nous avons beaucoup à découvrir sur la cellule : des choses passionnantes mais surtout indispensables !

Comment sélectionner des objets de votre zone de travail (colonnes, cellules, lignes).

Comment saisir des données, choisir leur format, mettre de la couleur... ?

Croyez-moi, vous serez bien avancés quand vous maîtriserez la sélection !

Sélection des cellules

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 bestioles. :D

Sélectionner une cellule

Pour sélectionner une cellule (et attention, je dis bien une seule), il faut cliquer sur celle-ci.

Un cadre noir apparaîtra alors autour de la cellule sélectionnée :

Sélectionner un bloc de cellules

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é :

Sélectionner des cellules éparpillées

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 :

Sous Mac, la manipulation est la même. Appuyez sur la touche cmd (commande) au lieu de Ctrl pour Windows.

Sélectionner des colonnes et des lignes

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 monstrueux de lignes).

Pour sélectionner une colonne, cliquez gauche sur la zone entourée en bleue sur l'image :

De même pour les lignes, sauf que ce coup-ci, il faut cliquer gauche sur le numéro de ligne !

Nous savons désormais sélectionner une ou plusieurs cellules, occupons nous d'en saisir des données que nous pourrons par la suite analyser.

La cellule active

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.

C'est donc la cellule active : les données seront affichées dans cette dernière.

Saisir des données

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 !

Agrandir les cellules

Parfois, il peut être bénéfique d'allonger vos cellules.

En effet, il peut arriver que du texte soit inséré dans une cellule. Si ce dernier est trop long, la cellule va donc le tronquer lorsque cette dernière n'est pas sélectionné (évidemment le texte sera toujours à l’intérieur) et empiéter sur les cellules adjacentes lorsque cette dernière sera sélectionné.

Pour plus de lisibilité, il est donc utile d'allonger la cellule.

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.

Le tour est joué !

On peut faire de même pour les lignes.

Ça y est ? Vous avez saisie des données ?

Vous avez désormais la possibilité de mettre de la couleur dans vos cellules, de changer la police du texte qui s'y trouve...

Mais 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.

Définir un format

Saisissez des données numériques sur quelques cellules :

Sélectionnez la plage qui contient ces données puis faîtes un clic droit dessus puis cliquez sur « Format de cellule » dans le menu déroulant :

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 l'onglet « 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 plein de formats possibles, on ne va pas tous les voir un à un : ça ne servirait à rien.

Si je puis me permettre un conseil, regardez le format « Date », c'est intéressant...

Maintenant, on va voir ce que vous attendez depuis tout à l'heure : les couleurs, la police... :D

L'embellissement

Enfin des couleurs dans ce tableur bien moche ! :lol:

Sélectionnez une plage de cellules puis accédez au format de cellule. Je zappe l'onglet « Police » mais je m'attarde sur les bordures. Cliquez sur l'onglet « Bordure » :

Choisissez une couleur dans le menu entouré en bleu, par exemple du vert. :)

Choisissez maintenant le style de la bordure (épaisseur, etc.) dans le menu entouré en bleue sur cette nouvelle image :

Cliquez sur « Contour » afin que votre plage soit encadrée du trait que vous avez paramétré.

Vous pouvez aussi cliquer sur « Intérieur » : dans ce cas, vous aurez un tableau, ce qui est pratique pour l'impression de votre feuille de calcul mais pas pour sa lisibilité !

Validez par « Ok » : super ! Votre plage a maintenant une bordure ! :D

Vous pouvez jeter un coup d'œil dans « Motifs » : ça ajoute une couleur de fond à la cellule.

Poignée de recopie incrémentée

Mais ce n'est pas tout ! On ne le dira jamais assez, Excel est un logiciel complet et puissant. Ce que je vais vous enseigner maintenant est l'une des premières fonctionnalités inutilisées par le grand public.

Nous allons utiliser un outil qui se trouve sur toutes les cellules d'une feuille, j'ai nommé la « poignée de recopie incrémentée » !

Écrivez dans une cellule un mot, ou encore un chiffre (c'est comme vous voulez :D).

Chez moi, voici ce que ça donne :

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 mot « Salut » a été recopié.

Vous remarquerez que comme toujours, 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 pifomètre 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 !

Ça marche aussi pour les mois : il suffit d'écrire « Janvier » à la place de « Lundi » puis de suivre la même procédure. ;)

Souvenez-vous de cette technique. On s'en sert partout et nous allons la réutiliser dans le chapitre suivant !

Après avoir rédigé votre document, il vous reste un point crucial si vous ne voulez pas le perdre : le sauvegarder !

En ce qui concerne la sauvegarde du classeur, nous allons pour le moment rester très simples. Vous allez voir.

Direction le menu « Fichier » que vous commencez à connaître. Mais au lieu de vous rentre dans l'onglet « Nouveau » comme nous l'avons fait précédemment, cliquez à présent sur « Enregistrer ».

Si vous l'aviez déjà enregistré (ou qu'il était enregistré et que vous l'avez ouvert), le classeur sera automatiquement ré-enregistré avec le même nom et dans le même emplacement. Si vous l'avez vous-même créé et que vous ne l'aviez pas encore enregistré, Excel va vous demander de renseigner quelques informations dans cette fenêtre :

Cette fenêtre peut vous paraître compliquée, mais nous n'allons pour le moment pas nous servir de grand-chose.

Où enregistrer le fichier ?

Vous devez sélectionner l'emplacement de l'enregistrement sur votre disque dur. Vous pouvez le faire en sélectionnant le dossier depuis le menu de gauche. Une fois un emplacement sélectionné, son contenu apparaît dans le volet de droite. Vous pouvez alors naviguer à l'intérieur des dossiers.

Quand vous avez correctement sélectionné le dossier où enregistrer le classeur (et que son possible contenu est apparu dans le volet de droite), alors vous pouvez passer à l'étape suivante.

Nommer le fichier

Vous devez ensuite renseigner le nom de votre classeur, par rapport au thème, ou à son utilité, ou au type de classeur (facture, devis, etc.). Bref, vous avez libre-choix - tant que ce nom n'a pas déjà été utilisé pour un classeur Excel 2010 précédemment sauvegardé à l'emplacement souhaité.

Assurez-vous de donner un nom compréhensible à votre classeur, qui se rapporte au sujet des données, ou à quoi que ce soit d'autre. Bref, il faut que son nom vous mette la puce à l'oreille. Cela vous permettra de le retrouver plus facilement et de savoir ce qu'il contient (et ce par la simple lecture de son nom).

Le type de fichier

Pour ce qui est du type, il s'agit d'enregistrer le fichier dans un format spécial :

L'auteur

L'auteur, c'est vous (en l'occurrence dans le cas de l'image précédente, c'est nous ( :p ), les trois auteurs du cours).

Par défaut, le fichier sera enregistré sous votre nom d'utilisateur de Windows.

Vous pouvez bien évidemment le changer. Pour ce faire, cliquez sur le ou les noms d'auteurs déjà présents, faites un Ctrl + A, puis tapez le ou les nouveaux noms d'auteurs désirés, séparés d'un point-virgule.

Titre et mots clés

Ici, vous pouvez associer des mots-clés à n'importe quel fichier Excel. Ces mots-clés apparaissent dans toutes les fenêtres de l'explorateur Windows dans la colonne Mots-clés.

Vous pouvez aussi associer un titre à votre document (en plus du nom de fichier que vous avez déjà indiqué). Ces deux informations restent néanmoins facultatives.

Gestion des miniatures

L'explorateur Windows permet d'afficher les différents documents et dossiers en grande taille. Cette grande taille permet généralement de voir ce qu'il y a à l'intérieur du dossier, ou d'avoir un aperçu du fichier. En cochant l'option « Enregistrer la miniature », Excel générera automatiquement cet aperçu si vous utilisez Windows Vista ou Windows Seven.

Enregistrer !

Enfin votre moment de gloire !

Cliquez sur « Enregistrer ». Tadam ! Votre fichier Excel est enfin sauvegardé !

Résumons

• Un fichier Excel s'appelle un « classeur ». Après une procédure d'enregistrement, qui permet de sauvegarder votre travail et de le réutiliser plus tard, votre classeur porte un nom.

• Un classeur contient des « feuilles », divisées en une multitude de « cellules », repérées grâce à leurs coordonnées COLONNE LIGNE.

• La souris et le clavier nous permettent de sélectionner une cellule ainsi que des cellules, jointes ou non, dans la feuille. Il est également possible de sélectionner une colonne ou une ligne de cellules entière en cliquant sur l'étiquette correspondante.

• Il n'y a qu'une seule « cellule active » par plage de cellules sélectionnées. Il s'agit tout simplement de la cellule depuis laquelle la sélection a débuté.

• Avec votre clavier, vous pouvez saisir des données dans une cellule. Le contenu apparaîtra alors dans la barre de formules lorsque ladite cellule est sélectionnée. Les données peuvent être numériques, alphabétiques voire les deux à la fois.

• Le menu « Format de cellule » nous permet de mettre en forme la cellule (couleur, bordure etc.) ainsi que les données contenues dans la cellule (police etc.). Notez qu'en jouant sur les bordures, il est possible de créer des tableaux.

• La « poignée de recopie incrémentée » nous permet de recopier le contenu d'une cellule sur une sélection de notre choix par un simple glisser-déposer. Excel connaît des listes telles que les jours de la semaine ou les noms des mois qui permettent, à partir du premier élément, de retrouver les autres, toujours sur un simple glisser-déposer.

Vous avez les notions de base pour la suite.

Au chapitre suivant, nous allons découvrir comment saisir des données de plus en plus vite grâce aux outils proposés par Excel. Révisez bien vos bordures parce que nous allons bientôt délimiter des tableaux afin de vous organiser !

Dans le chapitre précédent, nous avons vu que l'utilisation de la poignée de recopie incrémentée nous permettait de copier des données sur plusieurs lignes et/ou plusieurs colonnes très simplement.

Cas particulier : les jours de la semaine et les noms des mois. Rappelez-vous : si vous écrivez « Lundi » dans une cellule et que vous l'étirez avec la poignée de recopie incrémentée, Excel complète automatiquement avec les jours de la semaine suivants :

Que diriez-vous de créer vous aussi votre petite liste ? Il suffira de saisir le premier élément dans une cellule, de l'étirer et votre liste apparaîtra complète :) ! Cette fonctionnalité peut se révéler très utile dans le cas de tableaux à compléter toujours de la même manière. Plus tard, nous verrons comment créer des listes déroulantes à partir de vos données, ce qui est encore plus pratique ! Mais pour le moment, arrêtons de saliver et retournons à notre petite liste personnalisable.

Vous souhaitez faire une liste de marques de voitures parce que vous devez les retaper à la suite sans cesse. Peugeot, Citroën, Renault, Ford, Toyota... même avec un copier-coller, ça devient vite lassant et répétitif.

Choisissez une colonne, et saisissez une marque par cellule, les unes en dessous des autres :

Nous allons donc créer des listes personnalisées. Pour cela, allez dans le menu « Fichier » puis dans « Options » :

Ensuite, rendez-vous dans l'onglet « Options avancées », catégorie « Générale », et cliquez sur le bouton « Modifier les listes personnalisées » :

Une fenêtre qui s'ouvre alors, vous permettant d'entrer une nouvelle liste personnalisées, ainsi que de voir celles qui ont été pré-conçues par Excel :

Sous Mac, il faut cliquez sur le menu « Excel » puis sur « Préférences ». Une fenêtre s'ouvre, il faut cliquer sur « Listes personnalisées ».

La manipulation est ensuite identique à celle de Excel pour Windows. Notez que vous pouvez accéder à la fenêtre des préférences grâce au raccourci clavier cmd + , .

Sur la gauche, vous reconnaissez les listes mémorisées par défaut (noms des mois, noms des jours). Cliquez sur « Nouvelle Liste » si ce n'est déjà fait et cliquez sur « Importer ». Sélectionnez désormais vos marques de voitures et revenez dans la fenêtre des options grâce à la petite flèche rouge. Votre liste apparaît à gauche. Validez enfin :

Notez, comme je l'ai précisé dans l'image ci-dessus, que vous n'avez pas forcément besoin d'importer la liste, vous pouvez tout aussi bien la rédiger vous-même puis l'ajouter.

Vous pouvez maintenant essayer ! Sélectionnez une cellule au hasard et tapez-y le premier élément de la liste (chez nous, « Peugeot »). Étirez, la liste est recopiée ! Un fichier d'exemple a été réalisé et ajusté pour que vous n'ayez plus qu'à étirer. N'hésitez pas à le télécharger et à l'essayer !

Télécharger le fichier liste_personnalisable.xlsx

Avec ce que vous savez faire (saisie de données dans une/des cellules, bordures), vous êtes capables de délimiter des tableaux, bref, de vous organiser, car ce n'est pas la place qui manque dans une feuille. Sans le savoir, vous faîtes ce que l'on appelle des « listes ». Excel vous propose de les compléter très rapidement.

Qu'est-ce qu'une liste ?

Une liste, c'est une suite exploitable de données. C'est donc une liste de données. :D

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 « sale » :

La première solution est donc à préférer. :) En effet, l'analyse des données y sera plus facile.

Bref, une liste de données propre, c'est un beau tableau que vous pourrez compléter aisément.

Compléter sa liste !

Créons une liste de données exploitable.

Voici ce que ça peut donner :

C'est un « formulaire » (une fonctionnalité autrefois appelée « grille ») que nous allons utiliser ici. Comme il s'agit d'une fonctionnalité qui n'est pas présente par défaut dans le ruban ni dans la barre d'Accès d'outil rapide, nous allons donc l'y ajouter :

Cliquez sur la flèche descendante de la barre d'outils d'Accès rapide puis, tout en bas de la liste, sur « Autres commandes » :

Sur la fenêtre qui apparait, sélectionnez « Toutes les commandes » (dans la liste déroulante de gauche), puis cherchez « Formulaire... », cliquez sur le bouton « Ajouter » puis validez en cliquant sur « OK » :

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 !

Vos listes de données peuvent parfois contenir des erreurs de frappe. Si vous n'avez pas mis en place une liste personnalisable, comme à la première sous-partie de ce chapitre ou une liste déroulante, comme nous le verrons plus tard, ces erreurs vous frapperont tôt ou tard.

Une liste de données simple avec une faute de frappe encadrée :

Pourtant, il existe des moyens simples pour se protéger de ces erreurs.

Utilisez la saisie semi-automatique !

Excel « mémorise » ce que vous avez saisi dans votre feuille de calcul. Il vous propose même de ressaisir ces données très rapidement et ailleurs dans la feuille. Par exemple, vous saisissez pour la première fois « Instituteur ». Sur la cellule d'en dessous, vous voulez saisir la même profession. Vous commencez donc par taper le « I » et Excel vous propose immédiatement « Instituteur », que vous pouvez saisir rapidement en validant par Entrée .

Bien évidemment, ce procédé suppose que vous n'ayez, pour être sûr de votre coup, qu'un seul mot commençant par « I » et que ce mot de départ soit bien orthographié. Une astuce permet de contourner la problématique de plusieurs mots commençant par la même lettre.

Une lettre avant chaque saisie

Ici, nous utiliserons toujours la saisie semi-automatique que nous venons de voir. A chaque saisie différente correspond une lettre majuscule, que vous saisissez avant la donnée. Ainsi, si plusieurs données commencent par la même lettre, il n'y aura plus à choisir lors de la saisie semi-automatique. Il n'y a pas photo, vous gagnerez énormément en productivité. ;)

Résumons

• La création d'une « liste personnalisable » permet, à partir d'un élément de votre liste et de la poignée de recopie incrémentée, de saisir automatiquement les autres composantes de la liste.

• Une « liste de données » désigne une plage rectangulaire de données ordonnées grâce à des étiquettes de colonne et d'une entrée par ligne. La plage peut être délimitée par exemple avec une bordure. Excel propose des outils pour la compléter mais dans la pratique, on préférera le faire manuellement, à la suite de la liste.

• Pour éviter les fautes de frappe, on peut utiliser la « saisie semi-automatique », proposée par Excel à la saisie d'un mot qui commence par la même lettre qu'un mot déjà saisi ailleurs dans le classeur. Pour être sûr de l'utilisation correcte de cet outil, chaque mot identique de la liste peut être précédé d'une même lettre majuscule.

Le chapitre vous a présenté divers moyens d'accélérer la saisie de données pour être plus productif. Les listes de données, vues en deuxième sous-partie, constituent une notion essentielle d'Excel, que nous réutiliserons dans tout le cours. Au chapitre suivant, nous commencerons à faire des calculs et à automatiser un peu vos tableurs :) .

Ici, nous allons découvrir en douceur les formules.

Dans le premier chapitre, je vous avais parlé de la « barre de formule », non ?

Je crois que oui. C'est ici que nous allons les écrire, ces fameuses bestioles.

Elles s'appliqueront à toutes les cellules sélectionnées.

Une formule commence toujours par le signe égal =.

Ces bébêtes sont capables premièrement de faire des calculs... de nombreux calculs !

Opérations basiques

Pour toutes les formules, on va utiliser ce que l'on appelle des « fonctions ».

Ce 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.

Il faut se faire une image de la fonction : c'est représenté par un mot dans lequel on fait passer des données.

La fonction travaille sur ces données et ressort le résultat.

Pour mieux comprendre, voici le schéma de ce que je viens de raconter :

Le résultat de votre formule sera affiché dans la ou les cellules qui contiennent cette formule.

Voici la syntaxe d'une formule avec fonction :

=FONCTION(DONNEE1;DONNEE2)

À la place des données, vous allez écrire la référence des cellules qui contiennent les données à analyser...

L'addition

L'addition est gérée par 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 : =SOMME(B2;C2)

Validez par la touche Entrée : vous avez en E2 le résultat de l'addition 5 + 123 !

Parce que c'est une méthode très mauvaise qui ne s'adapte pas aux données saisies.

Avec notre formule, changez la valeur de B2, mettez par exemple 10... Que constatez-vous ? Le résultat en E2 s'adapte ! :magicien:

=SOMME(B2;-C2)

La multiplication

La multiplication est gérée par la fonction PRODUIT. Sa syntaxe est la même que pour l'addition.

Maintenant que vous avez compris, essayez avec DIFFERENCE et QUOTIENT, qui gèrent respectivement la soustraction et la division.

Une fonction intéressante

Nous y voilà enfin. Une fonction bien intéressante est la fonction « MOYENNE », qui, comme vous vous en doutez, 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 à taper la formule...

Et là, attention, les choses intéressantes commencent : 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 ! ^^

Nous n'allons pas continuer à étudier chaque fonction : Excel en propose beaucoup (trigonométrie, etc) et un tutoriel y est consacré.

Exercice : des minutes aux heures et minutes

Pour mettre en pratique les fonctions, nous allons créer un petit convertisseur temporel. Dans une cellule vous rentrez un certain nombre de minutes, et dans une autre, on renvoie le nombre d'heures et de minutes correspondantes. Par exemple, pour 143 minutes, on devra renvoyer 2 h 23 min .

Je pense que vous avez remarqué que la conversion est aisée. 143/60 = 2 (/ étant la division entière, sous Excel la fonction QUOTIENT(x;y) ).

Le reste de la division euclidienne de 143 par 60 vaut 23. Il y a donc 23 minutes et 2 heures. Cette information, nous allons l'obtenir grâce à la fonction MOD(a ; b), qui permet de récupérer le reste de la division entière de a par b, avec b non nul évidemment. Par exemple, MOD(12 ; 6) renvoie 0 car 12 = 2*6 + 0 et MOD(12 ; 5) renvoie 2 car 12 = 2*5 + 2 :) . Ici MOD(143;60) renvoie donc 23.

Il y a donc deux calculs à faire, ce qui implique deux cellules différentes. Un artifice sur 4 cellules vous permet donc de renvoyer le résultat sous la forme x h y min.

La solution en secret, mais ça n'a pas du poser de problème.

En E3 notre nombre de minutes, c'est un entier positif, saisi par l'utilisateur. En G3, on a tout simplement = QUOTIENT(E3 ; 60) et en I3 = MOD(E3 ; 60)

Nous venons de finir avec les fonctions. Il est inutile de toutes les passer en revue. Vous connaissez les plus classiques et les plus utiles, le reste viendra en temps voulu :) .

Ici, nous sommes toujours sur les formules, donc, ça se tape toujours dans la barre de formules et ça commence toujours par le signe égal =. Toutefois, ce sont des formules un peu particulières, que l'on appelle les « conditions ».

Les conditions simples

Quand vous écrivez une condition, vous dites à Excel : « si telle cellule vaut tant, alors fais ceci, sinon, fais cela ». Vous saisissez l'intérêt du concept, maintenant ? ;)

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 :

=SI(condition;"Afficher si vrai";"Afficher si faux")

Différentes conditions sont possibles. Voici les opérateurs qui vont vous être utiles :

Si votre condition est : « Si la cellule B2 est supérieure ou égale à 45, alors... », vous remplacerez « condition » par B2>=45.

Voici comment ça se passe dans Excel :

Et voilà le résultat quand la cellule contient une donnée numérique différente de 100 :

Et quand la donnée est égale à 100 :

Les conditions multiples

Il existe deux formes de conditions multiples :

1. « Si cette cellule vaut tant et l'autre vaut tant, alors fais ceci, sinon, fais cela. »

2. « 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.

Mettons les choses au clair avec des schémas, comme nous les aimons tous.

Schémas de la condition multiple en ET

Schémas de la condition multiple en OU

Est-ce plus clair ? Si oui, la condition est respectée et vous pouvez passer à la suite. Sinon, la condition n'est pas respectée et vous devez relire les schémas. :p

Application

Maintenant que la différence est faite entre ET et OU, je propose de mettre en pratique ces fameuses conditions multiples.

Voici la syntaxe :

=SI(OPERATEUR LOGIQUE(condition1;condition2);"Afficher si vrai";"Afficher si faux")

Vous mettez soit ET, soit OU. :D

Mettons cela en pratique !

Comme avant, je veux 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.

Vous souvenez-vous de la poignée de recopie incrémentée ?

Allez, je la remets. :D

/* Va chercher dans les archives poussiéreuses... */

La voici la petite coquine :

Le petit carré noir, en bas à droite, qui recopie la valeur des cellules où vous voulez et qui reconnait quelques listes...

Ah ! Eh bien voilà ! J'étais sûr que vous vous en souviendriez !

J'ai un scoop, 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 vulgaire addition que j'aimerais recopier vers le bas. Seulement voilà, il serait difficile ( :D ) et trop long de faire un copier/coller de la formule sur toutes les cellules. J'utilise donc la poignée de recopie incrémentée sur ma formule :

Et j'obtiens un résultat spectaculaire : Excel a compris qu'il fallait « descendre » d'une cellule à chaque fois. Regardez, alors que ma formule de départ concernait la cellule G8, la case d'en dessus utilise la cellule G9. Eh oui, Excel est intelligent :

Eh bien, les dollars servent à 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 je ne l'avais pas fait, Excel aurait additionné les valeurs des cellules en dessous de E6, c'est-à-dire 0 (une cellule vide a pour valeur 0) !

Lorsque j'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).

Mises en forme conditionnelles

Il est possible d'agir sur la mise en forme de son classeur en fonction d'une condition. Imaginez que vous souhaitez faire une facture :

Pour avoir le total ligne par ligne, il faut multiplier le prix unitaire du produit par la quantité souhaitée par le client. Pour ne pas recopier la formule sur toutes les lignes, vous utilisez la poignée de recopie incrémentée que nous venons de voir. Et là, c'est le drame :

Parce qu'une cellule vide a pour valeur 0, Excel vous affiche le résultat, sur les lignes vides, du produit 0x0 = 0. C'est moche. Nous allons donc appliquer une mise en forme conditionnelle : si la valeur de la cellule vaut 0, alors je mets le texte en blanc. Sinon, je le mets en noir.

D'abord, il faut sélectionner les cellules sur lesquelles la mise en forme doit être appliquée (ça peut être comme ici une portion réduite de colonne mais vous pouvez sélectionner la colonne entière).

Dans l'onglet « Accueil », dans le groupe « Style », cliquez sur « Mise en forme conditionnelle ». Dans le menu déroulant, cliquez sur « Nouvelle règle » :

Dans la fenêtre qui s'ouvre, cliquez sur « Appliquer une mise en forme uniquement aux cellules qui contiennent ». 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 ».

Dans cette nouvelle fenêtre, choisissez la couleur blanche en guise de police. Puis validez ces deux fenêtres :

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. Notez que vous pouvez imposer une condition de supériorité, d'infériorité etc. Faites des essais en manipulant ces différents cas.

Je vous l'ai dit au début du cours, un classeur Excel est en fait un ensemble de feuillets. Vous pouvez en avoir autant que vous le souhaitez et vous pouvez effectuer diverses opérations dessus, comme les renommer ou encore les supprimer. J'explique en annexe comment effectuer toutes ces opérations.

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 maintenant.

La transmission de données d'un feuillet à l'autre se passe dans une formule. Ça tombe bien, vous savez maintenant de quoi il s'agit.

Prenons l'exemple avec deux feuillets « Source » et 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 :

=Source!B2

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 je tape cette formule en C5 de mon feuillet « Cible », je vais 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 ! :D

Je ne sais pas si vous avez remarqué, mais lorsque vous tapez une formule et qu'elle est dépendante d'autres cellules, ces cellules portent un cadre de couleur pour vous aider dans la saisie. Comme ceci :

Cette astuce n'est valable que lorsque vous éditez la formule, c'est-à-dire lorsque vous la modifiez. Il est intéressant de voir d'un seul coup d'œil quelles sont les cellules qui sont utilisées dans une formule sans avoir à éditer la formule. De plus, si vous voulez le faire pour plusieurs cellules en même temps... vous l'avez deviné, c'est impossible.

Pour voir les dépendances qu'il existe entre les cellules, Excel propose deux fonctions. La première fonction est la suivante : "Repérer les antécédents" qui permet de repérer dans la feuille quelles sont les cellules qui influent sur la cellule sélectionnée. Réciproquement, la seconde fonction "Repérer les dépendants" permet de connaître quelles sont les cellules influencées par la cellule sélectionnée.

Ces deux fonctions se trouvent dans l'onglet Formules et dans le groupe Audit de formule.

Comment les utiliser ? C'est très simple, il suffit de placer le curseur sur la cellule de votre choix et ensuite de cliquer sur la fonction qui vous convient. Apparaissent alors des flèches bleues.

Et voilà, on peut alors voir toutes les dépendances des cellules entre elles sur une même feuille ! Pour supprimer les flèches, cliquez sur la fonction "Supprimer les flèches" dans le même groupe.

Avant de passer au chapitre suivant, je vous propose un exercice d'entrainement sur les formules. En effet, les formules constituent le noyau dur du tableur Excel car c'est votre premier outil d'analyse efficace des données.

Afin d'apprendre quelque chose durant cet exercice et de ne pas appliquer bêtement ce que vous venez de voir, je vous propose de travailler avec les dates. Excel propose plusieurs fonctions pour manipuler les dates, une sous-partie annexe vous explique tout cela, je vous demande d'en prendre connaissance.

Nos objectifs

L'utilisateur de votre tableur est invité à saisir une année (par exemple : 2015).

• Vous devez renvoyer le jour du premier janvier, du premier mai, du 8 mai, du 14 juillet, du 15 août, du 11 novembre, du 25 décembre (c'est-à-dire si ça tombe un lundi, un mardi etc.) ;

• Vous devez indiquer si oui ou non le 29 février existe dans l'année donnée, c'est-à-dire si l'année est bissextile ;

• Vous devez renseigner le jour du jeudi de l'Ascension, de la Pentecôte, du lundi de Pentecôte, de Pâques et du lundi de Pâques.

Nos outils

La fonction DATE()

Bien que son utilisation soit expliquée en annexe, je vais tout de même vous apprendre à vous servir de la fonction DATE(). DATE a besoin de trois paramètres : l'année, le mois et le jour.

Comment savoir si une année est bissextile ?

Une année est bissextile si elle est multiple de 4 mais pas de 100. De plus, les années multiples de 400 sont bissextiles. Nous avons déjà vu dans notre exercice de conversion des minutes en heures et minutes que le reste d'une division d'un entier par un entier est obtenue grâce à la fonction MOD().

Par exemple, considérons l'année 2000. \frac{2000}{400} = 5. Le reste de cette division est donné par MOD(2000,400) et vaut 0. Donc, 2000 est divisible par 400, donc 2000 est bissextile.

Vous allez donc tout naturellement utiliser les conditions.

Autrement dit, il faut d'abord voir si l'année est multiple de 4. Si elle ne l'est pas, on s'arrête là, l'année n'est pas bissextile.

En revanche, si elle est multiple de 4, nous testons sa divisibilité par 100. Si elle est vérifiée, nous regardons si l'année est multiple de 400, et alors, elle est bissextile si c'est vérifié, sinon, elle ne l'est pas.

Voilà, vous venez de découvrir le premier outil d'analyse des données. C'est le plus utilisé.

On va en voir d'autres dans les chapitres suivants !

Sachez toutefois que nous proposons en annexe un (long) chapitre sur les fonctions d'Excel. C'est un index non exhaustif de fonctions rangées par catégories (Date & Heure etc.) et documentées (présentation, exemples) qui pourraient vous être utiles :) . N'hésitez pas à fouiner là-bas quand vous traitez des données.

Vous savez maintenant comment bien saisir des données et comment les analyser avec les formules. Contrairement à ce que vous pouvez penser, vous avez en main de précieux et puissants outils. Si vous avez un petit projet sur un tableur, il est fort probable que vous pouvez le réaliser maintenant. N'hésitez pas à consulter l'annexe sur les fonctions, qui présente beaucoup de fonctions selon leur catégorie (maths, dates etc.) avec un exemple concret à chaque fois.

Dans la deuxième partie, nous verrons d'autres outils d'analyse de données, tous plus puissants les uns que les autres. Nous verrons également comment faire des graphiques à partir d'une plage de données ou comment automatiser vos tableurs avec les macros.

Excel propose de nombreux outils pour trier vos données. Nous souhaitons par exemple trier les entrées d'un tableau par ordre alphabétique du prénom. Faisons donc un beau tableau bien structuré et sans espace entre les différentes lignes.

Sélectionnez tout ce tableau, puis, dans l'onglet « Accueil », dans le groupe « Édition », cliquez sur « Trier et filtrer ».

Un petit menu apparait. A partir de là, vous pouvez directement appliquer un tri de « A à Z » ou encore de « Z à A ». Si vous souhaitez personnaliser le tri, cliquez sur « Tri personnalisé » :

Vous avez alors une petite fenêtre qui s'ouvre qui permet d'aller plus vite dans les tris alphabétiques :

Une fenêtre s'ouvre et vous constatez qu'Excel reconnait parfaitement les colonnes du tableau sélectionné et vous demande ce que vous voulez tri (données, couleurs, icônes) et comment vous voulez le trier. Nous voulons ici trier les noms (la colonne « Nom ») par ordre alphabétique, nous nous occupons seulement d'une zone de tri. Appliquer ensuite un tri croissant en cochant "Croissant", les noms seront donc rangés par ordre alphabétique :

Par la même occasion, vous pouvez, si vous le désirez, trier par Age ou Score. Par défaut, Excel vous propose un seul niveau de tri, pour en ajouter, cliquer sur « Ajouter un niveau ».

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.

La validation des données

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 cellules :

Allez ensuite dans l'onglet « Données », groupe « Outils de données », bouton « Validation des données » :

Une fenêtre s'ouvre alors :

Vous remarquez qu'elle est constituée de trois onglets. L'onglet ouvert par défaut est « Options ». C'est l'onglet qui nous intéresse.

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 enfin sur « OK » :

Vous pouvez à présent essayez de taper 3 dans l'une de ces cellules paramétrées... Excel vous renvoie un petit message d'erreur !

Il n'y a plus qu'à cliquer sur « Annuler ». On constate qu'Excel supprime toute valeur qui ne correspond pas à vos critères !

La personnalisation du message d'erreur

Nous avons vu qu'il y avait plusieurs onglets dans la fenêtre des « Validation des données ». Retournez sur cette fenêtre car c'est ici que nous allons pouvoir personnaliser votre message d'erreur correspondant à une saisie invalide ! N'oubliez pas de sélectionner les cellules concernées avant.

Dans la fenêtre, cliquez sur l'onglet « Alerte d'erreur » :

Dans la liste des « Style », vous pouvez définir le type de boîte de dialogue qui apparaîtra. En fait, cela ne change pas grand-chose mis à 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. Sous les vieux Windows (98 et 95), on pouvait entendre un bruit sourd émis par l'ordinateur quand une boîte de type « Arrêt » apparaissait (quel bon souvenir d'enfance ^^ ). Bref, le style est par défaut sur « Arrêt » et je vais le laisser tel quel. Libre à vous de faire vos propres tests ensuite.

Excel comprend que ce message doit apparaître quand des données non valides sont tapées. C'est en effet ce pour quoi la case juste au-dessus est cochée.

Il ne vous reste plus qu'à personnaliser le message d'erreur avec un titre et un message. Cliquez sur « OK » :

Puis essayez de taper une donnée non valide :

Dans la fenêtre de « Validation des données », il reste un troisième onglet : « Message de saisie ». Il vous permet d'afficher un message lorsqu'une cellule aux données restreintes est sélectionnée :

Ce qui donne :

Nous nous sommes intéressé 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 :

Alors ? ^^ C'est parti, je vous explique !

Tout d'abord, sélectionnez les mêmes cellules concernées dans notre bon vieux tableau. Cliquez sur « Données » puis sur « 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 (une liste vide n'est que de peu d'utilité), il va falloir compléter le champ « Source ».

Deux solutions s'offrent à vous :

Première solution

Vous pouvez saisir le contenu de votre liste manuellement, en séparant chaque élément par un point-virgule.

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, ça va encore puisque nous travaillons sur très peu de cellules.

Deuxième solution

La deuxième solution, pour pallier à ce problème, reste de sélectionner à l'aide de la petite flèche rouge à droite du champ les informations. Cela revient à mobiliser quelques cellules de votre zone de travail et à écrire dans chacune d'elles un nombre.

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 jolies listes déroulantes.

Il est maintenant tant d'apprendre à analyser vos données avec de puissants outils d'Excel.

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, etc.

Bref, elles font partie de ce que nous pourrions appeler les « notions avancées d'Excel ». Peu de personnes pensent à les utiliser : leur puissance en est négligée.

Prenons notre liste :

Vous voyez que c'est un véritable bazar ! Encore, ça va parce qu'il n'y a que 2 matières mais imaginez qu'on ait mis 35 élèves et 8 matières. o_O

Les filtres vont nous aider à faire un tri simple, efficace et à nous y retrouver. ;)

Notre tableau est exploitable, on peut donc analyser les données qui s'y trouvent !

Sélectionnez toutes les cellules qui composent ce fameux tableau puis allez dans l'onglet « Insertion » et dans le cadre « Tableaux », cliquez sur le bouton « Tableau » :

Il vous ai ensuite demandé de sélectionner le tableau en question, attention à bien préciser que les en-têtes de votre tableau figurent dans votre sélection :

Voilà qui est fait.

À première vue, rien n'a changé mais penchez-vous sur les titres des colonnes :

:waw: 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é, c'est un mini-entonnoir qui est apparu à la place de la flèche et cette dernière est positionnée en bas à gauche de l'entonnoir :

Les filtres personnalisés

Ça y est, vous êtes heureux avec ces filtres mais saviez-vous que vous pouvez les personnaliser ?

Ah oui, non, c'est vrai, vous ne saviez pas. :p

Cliquez sur une des listes déroulantes et choisissez « Filtre numérique », puis « Filtre personnalisé... » dans la nouvelle liste qui vient d'apparaitre :

Une fenêtre s'ouvre alors :

À partir de là, vous pouvez faire ce que vous voulez !

Choisissez selon vos bons plaisirs dans les listes déroulantes, mettez des valeurs dans les champs... Je ne peux plus vous guider ici : c'est vous le patron. :)

Il est également possible d'analyser sa liste avec une fonction méconnue, aux explications généralement floues, mais d'une puissance exceptionnelle : « SOMMEPROD ».

J'y viens. Elle permet de comptabiliser des données en multipliant des matrices entre elles. :-°

Pour être claire, 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. Ce n'est toujours pas très clair ? Je vous donne la fonction et tout de suite un exemple, ça vous aidera surement à comprendre.

=SOMMEPROD((plage1="critère1")*(plage2="critère2")*...)

Alors c'est mieux ? On peut compter le nombre de lignes où la plage1 (colonne 1) est égale à critère1 et où la plage2 (colonne 2) est égale à critère2.

Nous allons utiliser un nouveau tableau (plus long) pour les exemples :

Pour me suivre durant cet exercice, vous pouvez télécharger ce nouveau tableau :

Télécharger le fichier sommeprod.xlsx

Notez néanmoins que travailler un peu votre saisie ne peut pas vous faire de mal. ;)

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 ». Pardi !

Pour cela il faut entrer la formule suivante :

=SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars"))

On obtient bien 3 ! Eh oui Paul a 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 :

=SOMMEPROD((A2:A31="Paul")*(B2:B31="Mars")*(C2:C31))

Tada ! On obtient donc 2230. En effet la fonction a effectué le calcul suivant : 840+660+730=2230. C'est top non ? On peut faire plein de combinaisons avec cette formule ! Mais attention il y a quelques règles à respecter :

Je vous propose d'autres exemples pour bien comprendre et voir un peu ce que l'on peut faire avec cette fonction.

Exemple 1

Il est possible de compter le nombre de ventes réalisées par Jean (on peut aussi réaliser cette opération avec la fonction NB.SI) :

=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

Il est aussi possible de 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

Enfin, dernier exemple, nous pouvons 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.

Je viens donc de vous montrer la puissance de cette fonction, qui peut s'avérer très utile dans des longues listes présentant beaucoup de critères.

Voilà pour les listes de données et leurs filtres.

Je vous conseille de bien appréhender ce chapitre : nous ne pourrons pas nous passer des données filtrées dans le chapitre suivant ! :-°

Avant de commencer à définir un graphique, il vous faut au moins une plage de données propre. Le principe de création d'un graphique est simple :

• Premièrement, vous saisissez des données dans le tableur, comme d'habitude ;

• Ensuite, vous sélectionnez ces données et vous définissez un graphique ;

• Enfin, vous personnalisez votre dessin.

Nous vous proposons de commencer petit et facile. Nous allons faire la courbe d'évolution du nombre de cours sur le Site du Zéro en fonction du temps. Le Site du Zér0 existe depuis 1999 mais il n'est permis aux membres de rédiger leurs propres cours que depuis 2005. Nous devrions donc voir un pic d'évolution à la date t = 2005.

Nous n'avons pas de statistiques officielles sous le coude. Nous avons inventé ces données en respectant toutefois le principe suivant : avant 2005, le nombre de cours se comptait sur les doigts de la main :p .

Voici nos valeurs, libre à vous d'inventer les vôtres ou de recopier les nôtres. Jusqu'ici, vous ne devriez pas trop être dépaysés.

Néanmoins, je vous propose de télécharger ce tableau, de manière à avoir les mêmes valeurs que le tutoriel :

Télécharger le fichier graphique.xlsx

Maintenant, la magie va opérer : voyons comment faire un graphique avec ces données.

Excel propose plusieurs modèles de graphiques : courbes, nuage de points, camemberts, histogrammes, modèles 3D...

Le but n'est pas de tous les passer en revue. Vous pourrez le faire très facilement ensuite. Non, ici, il est temps de voir comment dessiner le graphique. Nous ferons une courbe.

Sélectionnez votre plage de données sans les étiquettes de colonnes. Allez dans l'onglet « Insertion », dans la rubrique « Graphique » et cliquez sur « Ligne ». Un menu se déroule, cliquez sur la première image :

Un graphique apparaît alors ! Nous allons maintenant voir les différentes options permettant de le personnaliser.

On obtient deux courbes, l'une sur le nombre de tutoriels et l'autre sur l'évolution des dates. Cette seconde courbe ne sert pas à grand-chose, elle devrait être en abscisse d'ailleurs. L'abscisse, c'est l'axe horizontal du graphique. On va modifier tout ça.

La courbe bleue, on n'en veut plus. On va simplement cliquer dessus et appuyer sur la touche Suppr. Et hop ! Elle disparaît. C'est bien parti.

Ensuite dans l'onglet « Création » (de l'outil de graphique, il faut donc que le graphique soit sélectionné en cliquant dessus si ce n'est déjà fait) et dans la rubrique « Données », cliquez sur « Sélectionner des données ».

Une fenêtre s'ouvre alors :

Qu'est ce qu'on va faire de cette fenêtre maintenant ? On va modifier les « Étiquettes de l'axe horizontal (abscisse) ». Cliquez sur « Modifier » et ensuite, sélectionnez la colonne des années que l'on a dans le tableau. Cliquez sur « OK » deux fois et le graphique se met à jour.

On va maintenant changer le titre du graphique et les titres des axes, parce que là, on ne devine pas ce que le graphique représente (même avec cette croissance fulgurante ;) ). Pour cela on se positionne sur l'onglet « Disposition » (onglet des « Outils de graphique ». Dans la rubrique « Étiquettes », nous avons deux outils qui nous sont utiles : « Titre du graphique » et « Titre des axes ».

Pour modifier le titre du graphique, rien de plus simple. On va cliquer sur « Titre du graphique » et sélectionner la position où l'on veut qu'il soit. 3 possibilités : au dessus du graphique, sur le graphique ou aucun (notez que l'on peut changer la position même après avoir changé le texte donc rien de grave si la position ne convient pas pour l'instant).

Pour changer le texte du titre, on clique sur le titre du graphique où par défaut il est inscrit très originalement « Titre du graphique ». Nous allons donc écrire à la place : nombre de cours sur le Site du Zéro en fonction du temps. Vous pouvez modifier la police du titre comme toutes les autres polices en vous rendant dans l'onglet « Accueil ».

Et pour les titres des axes ? Même façon de procéder. On va simplement cliquer sur « Titre des axes ». Un menu se déroule, l'un pour l'axe horizontal l'autre pour l'axe vertical. Vous avez ici une façon de positionner l'axe horizontal mais 3 pour l'axe vertical, à vous de tester et choisir celle qui vous convient. Même procédé que pour le titre du graphique pour changer le texte. Voici ce que l'on obtient :

On va maintenant embellir notre graphique et le personnaliser. Pour commencer, la légende à droite ne sert pas à grand-chose, deux solutions pour l'enlever. La première, dans l'onglet « Disposition », la rubrique « Étiquettes », cliquez sur « Légende » puis « Aucune ». La seconde, cliquez droit sur la légende puis sur « Supprimer ».

Pour modifier l'apparence de la courbe, on clique droit sur la courbe (il faut être un minimum précis pour bien cliquer dessus) et dans le menu déroulant, cliquez sur « Mettre en forme une série de données... ». Une fenêtre s'ouvre avec plusieurs onglets en colonne. A vous de voir ce que vous voulez modifier, faites des tests :

Les modifications sont instantanées, alors en déplaçant la fenêtre sur le côté, on peut voir ce que ça donne avant de fermer la fenêtre.

Pour le fond du graphique, même chose : clique droit sur la zone de traçage (tout le fond du graphique). Dans le menu déroulant, cliquez sur « Mise en forme de la zone de traçage... ». Une fenêtre s'ouvre avec plusieurs options, à vous de vous amuser !

Nous nous arrêtons là dans l'aide à la personnalisation des graphiques, mais il y a encore beaucoup, mais quand je dis beaucoup, c'est beaucoup d'options. Baladez-vous dans les 3 onglets spécifiques au graphique et voyez ce qui est modifiable ! Si vous avez des questions pour modifier quelque chose de spécifique, il vous reste le forum.

La courbe sous Mac

Le programme est globalement le même, hormis un bandeau vert qui se déroulera sur la fenêtre après avoir cliqué dans le menu « Insertion » puis sur « Graphique ». C'est le seul changement majeur :) . Le bandeau permet de sélectionner le type de graphique à appliquer. Bref, on vous demande de cliquer après avoir sélectionné vos données : rien de bien compliqué. Vous pourrez modifier vos paramètres à coup de clics droits bien placés, comme ci-dessus.

Excel dispose d'autres outils pour manipuler les graphiques, ou plutôt pour les exploiter. En sciences expérimentales (d'un niveau lycée), on fait faire aux élèves des relevés sur un système chimique par exemple, et on fait comparer ces données à une théorie. Ainsi, on cherche à obtenir des courbes à partir des données expérimentales qui se rapprochent le plus des courbes théoriques. Pour effectuer ces fameuses comparaisons, Excel vous propose plusieurs outils.

La courbe de tendance

Tracer une courbe de tendance revient à tracer une courbe qui s'approchera le plus possible de tous les points du graphe.

Voyons ça sur un exemple. Peu importe le support, nous vous avons personnellement choisi les relevés d'un TP de physique de terminale. Nous vous suggérons de télécharger les données sur lesquelles nous allons travailler. A votre niveau, il n'est plus question de s'attarder sur la saisie d'informations :) .

Télécharger le fichier modélisation.xlsx

Faites un graphique de type « nuage de points » à partir des données des colonnes « d » et « u » (« u » en abscisse (X) et « d » en ordonnées (Y)). Après quelques réglages de couleurs, voici ce que nous pouvons obtenir :

Pour ajouter notre fameuse courbe de tendance, qui, on le rappelle, passera au plus près de tous les points, faites un clic droit sur un des points et cliquez sur : « Ajouter une courbe de tendance ».

Dans la fenêtre qui s'ouvre, on va vous demander selon quel modèle modéliser la courbe (linéaire, polynomiale etc.). Dans le cas des sciences expérimentales, vous pouvez parfois connaître le modèle mathématique de ce que vous mesurez. Ce n'est pas toujours le cas. Sélectionnez Linéaire pour être sûr que tout ira bien :) .

Sous Mac, le choix du type de courbe se fait dans l'onglet « Type » de la fenêtre qui s'ouvre.

Après validation, votre courbe de tendance apparaît. Vous pouvez modifier son apparence en cherchant dans le menu contextuel qui s'ouvre après un clic droit sur cette courbe. Vous êtes des grands désormais :D .

L'équation de la courbe de tendance

Vous pouvez afficher sur le graphique l'équation de la courbe de tendance (dans le cadre de notre étude quantitative de la diffraction de la lumière, c'est d'ailleurs très utile ^^ , mais passons). Faites un clic droit sur la courbe de tendance puis sur « Format de la courbe de tendance ». Dans l'onglet « Options », cochez « Afficher l'équation sur le graphique » :

La courbe de tendance de la modélisation effectuée et son équation de droite

N'hésitez pas à explorer les différents types de graphiques proposés par Excel. Vous en trouverez bien un qui vous convient. Dans le chapitre suivant, nous continuons l'analyse de nos listes, mais avec un concept bien plus puissant mais aussi un peu plus difficile...

« Tableaux croisés dynamiques ». En fait, comme son nom l'indique, nous allons « croiser » les données d'une liste.

Mettez-vous dans le contexte : une longue liste de données, très longue. Plusieurs étiquettes de colonnes, des informations alphabétiques, numériques etc. Le tableau croisé dynamique créé à partir de cette longue liste va vous permettre d'obtenir notamment des statistiques, toujours aussi simplement que d'habitude, avec un glisser-déposer.

L'embêtant n'est pas tellement de mettre debout un tableau croisé dynamique (que nous abrégerons désormais TCD), mais bien de l'organiser.

Un outil statistique puissant

Après la construction d'un TCD à partir d'une longue liste, vous devez le mettre en forme. Les TCD combinent format de cellules et listes déroulantes. Une fois la mise en forme effectuée, vous obtenez de belles statistiques.

Par exemple, les moyennes des ventes réalisées par un vendeur au mois de Janvier, puis de Février etc. Avec une liste déroulante, vous pouvez changer de vendeur et observer ses résultats. La moyenne peut être faite avec une formule, c'est vrai, mais il faudra élargir la plage d'entrée à chaque nouvelle saisie dans la liste. Bref, les TCD, c'est puissant car ils combinent plusieurs outils d'analyse en un tableau.

La notion de TCD est encore floue pour vous. Je vous propose de travailler par l'exemple et d'en construire un ensemble. Nous nous occuperons de son organisation ensuite.

Si vous avez bien suivi, vous avez parfaitement compris qu'il nous fallait une liste de données avant de commencer les manipulations, liste qui se voudra de préférence bien longue. Deux choix s'offrent à vous :

• Saisir vous-même la liste. Cela vous fera travailler la saisie mais on se retrouve dans quelques heures

• Télécharger une liste de données idéale toute prête :)

A vous de voir si vous préférez le masochisme ou saisir le plateau d'argent qui est à votre disposition. Nous allons travailler sur une liste de 51 entrées, qui récapitule l'âge, le sexe, le jeu joué et le score de chaque participant à une soirée Jeux.

Nous souhaitons obtenir diverses statistiques : qui est le vainqueur de chaque jeu, quel est le jeu préféré des femmes, quel est le jeu préféré des hommes, quel est le score moyen à chaque jeu, le score moyen des hommes, le score moyen des femmes, le cumul de points de chaque sexe, de chaque âge etc etc. Sachez que toutes ces statistiques, qu'on pouvait obtenir laborieusement à coup de formules, de graphiques etc. vont apparaître dans le TCD ! Bien évidemment pas toutes à la fois, il faudra modifier l'organisation du TCD pour obtenir chaque statistique souhaitée, mais le gain de temps et de clarté est tout de même énorme.

Pour le moment, nous devons construire notre TCD à partir de la fameuse liste :

Télécharger le fichier tcd.xlsx

La construction du TCD

Sélectionnez toute la liste de données, délimitée par une bordure rouge dans les fichiers proposés. N'oubliez pas de sélectionner les étiquettes de colonnes, c'est très important. Dans l'onglet « Insertion », cliquez sur « Tableau croisé dynamique » :

Une fenêtre demande de confirmer la plage de saisie du tableau et vous demande si vous souhaitez mettre le TCD dans une nouvelle fenêtre Excel ou dans l'actuelle :

Vérifiez la plage du tableau et confirmez la nouvelle feuille. Apparaissent alors deux onglets ainsi qu'un module à droite de l'écran :

Les deux onglets Options et Création du groupe Outils de tableau croisé dynamique

Le module de champs de tableau croisé dynamique

Sur ExcelSur la fenêtre qui s'ouvre, il faut cliquer sur « Disposition », afin de construire le TCD. S'il y a plusieurs fenêtres, cliquez sur « Suivant » jusqu'à arriver à la fenêtre ayant pour intitulé la « Disposition ».

Une fenêtre s'ouvre, vous invitant à bâtir votre TCD à coups de glissés-déposés peu ordinaires :

C'est ici que se fait la construction du TCD.

Pas si simple !

La procédure étant différente sur Windows et sur Mac, je vais vous la présenter pour ces deux systèmes.

Sur Windows

La première fois que l'on arrive ici, tout semble incompréhensible et difficile. A droite, dans le module, vous reconnaissez les étiquettes des colonnes de notre liste ainsi que les zones du TCD où seront déposées les étiquettes. Au milieu, vous avez un schéma du TCD que vous verrez une fois construit dans votre feuille de calcul. Nous croisons donc les données des lignes et des colonnes pour obtenir les résultats du milieu. Qu'attendons-nous ?

Il faut ainsi glisser les noms des étiquettes de colonne sur l'un des quatre champs du TCD.

L'étiquette « Numéro de concours » ne va servir à rien ici. Elle permet juste d'identifier chaque joueur dans la liste. Nous l'oublions donc.

Allons-y pour les autres. Nous souhaitons d'abord voir les totaux des points marqués à chaque jeu pour les hommes et les femmes, ainsi que la somme des deux. Cette première série statistique est un peu « bateau », je vous l'accorde, mais il faut commencer doucement.

Puisque nous voulons visualiser les scores, nous glissons l'étiquette de colonne « Score » sur la zone « Valeurs ». Jusqu'ici, tout devrait aller.

On veut un découpage des scores en fonction du sexe des participants et des jeux joués. Pas de problème, dans le champ « Ligne » nous glissons l'étiquette « Jeu » et dans le champ « Colonne », nous glissions « Sexe ».

Notez que nous aurions pu faire l'inverse, c'est ensuite une question de goût sur le résultat final. Nous venons d'anticiper les croisements des données de notre liste sous forme d'un tableau. Il n'y a plus qu'à fermer ce module complémentaire.

Et d'un ! Votre tableau croisé dynamique apparaît !

Ainsi, sur tous les points marqués dans la soirée, 247 ont été fournis par les joueurs d'Urban Terror. Les femmes ont marqué 33 points à ce jeu et se sont d'ailleurs faîtes bien massacrées par les hommes contre 214 points. :pirate:

Il est aussi possible de changer le filtre des champs en cliquant sur les flèches à droite de ceux-ci. Nous l'avons déjà vu dans un précédent chapitre.

Nous allons maintenant voir comment modifier simplement ce TCD, sans avoir à tout refaire.

Sur Mac

La première fois que l'on arrive ici, tout semble incompréhensible et difficile. A droite, vous reconnaissez les étiquettes des colonnes de notre liste. Au milieu, vous avez un schéma du TCD que vous verrez une fois construit dans votre feuille de calcul. Nous croisons donc les données des lignes et des colonnes pour obtenir les résultats du milieu. Qu'attendons-nous ?

Il faut ainsi glisser les noms des étiquettes de colonne sur l'un des quatre champs du TCD.

L'étiquette « Numéro de concours » ne va servir à rien ici. Elle permet juste d'identifier chaque joueur dans la liste. Nous l'oublions donc.

Allons-y pour les autres. Nous souhaitons d'abord voir les totaux des points marqués à chaque jeu pour les hommes et les femmes, ainsi que la somme des deux. Cette première série statistique est un peu « bateau », je vous l'accorde, mais il faut commencer doucement.

Puisque nous voulons visualiser les scores, nous glissons l'étiquette de colonne « Score » sur le champ « Données ». Jusqu'ici, tout devrait aller.

On veut un découpage des scores en fonction du sexe des participants et des jeux joués. Pas de problème, dans le champ « Ligne » nous glissons l'étiquette « Jeu » et dans le champ « Colonne », nous glissions « Sexe ».

Notez que nous aurions pu faire l'inverse, c'est ensuite une question de goût sur le résultat final. Nous venons d'anticiper les croisements des données de notre liste sous forme d'un tableau. Il n'y a plus qu'à cliquer sur « Ok », puis sur « Fin ».

Et d'un ! Votre tableau croisé dynamique apparaît !

Ainsi, sur tous les points marqués dans la soirée, 247 ont été fournis par les joueurs d'Urban Terror. Les femmes ont marqué 33 points à ce jeu et se sont d'ailleurs faîtes bien massacrées par les hommes contre 214 points :pirate: .

Chouette n'est-ce pas ? Reposez-vous, c'est mérité. Nous allons maintenant voir comment modifier simplement ce TCD, sans avoir à tout refaire.

Là encore, la façon de faire est différentes sur Windows et Mac. Voyons l'un après l'autre.

Sur Windows

Votre premier TCD est bien basique. Sur les 4 champs du schéma, nous n'en avons rempli que 3. Retournons dans le module permettant la conception du TCD.

Cochez la case de l'étiquette Age » et glissez-la dans la zone « Filtre du rapport ». Comme nous avons glissé l'étiquette Age, nous pourrons trier les scores de chaque jeu de chaque sexe en fonction de l'âge des participants.

Votre TCD a été modifié et vous voyez apparaître en haut du tableau un filtre. Fermez le module. Et admirez le résultat :

Un filtrage a été effectué pour ne garder que les joueuses et les joueurs de 16 ans. Les femmes de 16 ans sont accrocs à Halo.

Voilà. Les données peuvent être ainsi filtrées et interprétées de différentes manières. La liste analysée a suffisamment de champs et d'entrées pour être tournée dans tous les sens. :) Modifiez le TCD à votre sauce, faites des tests, il n'y a que comme ça que ça rentre. Pourquoi ne pas croiser les données d'âge et de sexe pour visualiser les scores, tout en permettant un filtrage par jeu ? A vous de voir !

Une fois ces manipulations maîtrisées, rendez-vous au chapitre suivant !

Sur Mac

Votre premier TCD est bien basique. Sur les 4 champs du schéma, nous n'en avons rempli que 3. Retournons dans la fenêtre permettant la conception du TCD. Cliquez au hasard dans votre TCD. Dans la barre d'outils qui vient à côté de votre tableau, cliquez sur le bouton « Assistant Tableau Croisé Dynamique ».

Une fenêtre s'ouvre, cliquez sur « Disposition », comme pour la création.

Glissez simplement l'étiquette « Age » sur le champ « Plage ». Le champ Plage permettra d'obtenir des filtres sur son TCD. Comme nous avons glissé l'étiquette Age, nous pourrons trier les scores de chaque jeu de chaque sexe en fonction de l'âge des participants.

Cliquez sur « Ok » puis sur « Fin », comme auparavant. Votre TCD a été modifié et vous voyez apparaître en haut du tableau un filtre.

Un filtrage a été effectué pour ne garder que les joueuses et les joueurs de 16 ans. Les femmes de 16 ans sont accrocs à Halo

Voilà. Les données peuvent être ainsi filtrées et interprétées de différentes manières. La liste analysée a suffisamment de champs et d'entrées pour être tournée dans tous les sens :) . Modifiez le TCD à votre sauce, faîtes des tests, il n'y a que comme ça que ça rentre. Pourquoi ne pas croiser les données d'âge et de sexe pour visualiser les scores, tout en permettant un filtrage par jeu ? A vous de voir !

Une fois ces manipulations maîtrisées, rendez-vous au chapitre suivant !

Résumons

• Un tableau croisé dynamique (TCD) permet d'analyser une liste de données. Cette notion est considérée comme la plus puissante d'Excel ;

• Le TCD fournit des statistiques détaillées sur votre liste, selon la manière dont il est construit ;

• On construit un TCD en effectuant un glisser-déposer d'étiquettes de colonnes sur l'une des quatre zones du TCD. Le champ « Plage » permet d'obtenir un filtre.

Continuons maintenant sur notre lancée.

Nous allons travailler ici sur un TCD simplifié, toujours créé à partir de notre liste de jeux.

Le tableau sur Windows

Le tableau sur Mac
Un tri (selon cette disposition) effectué sur les jeux (ici Urban Terror) nous permet de visualiser la somme des âges des participants de chaque sexe.

Et là, c'est le problème. Nous voulions savoir la moyenne d'âge des joueuses d'Urban Terror et la moyenne d'âge des joueurs d'Urban Terror. Plutôt raté puisqu'Excel fait la somme des âges des participants...

Nous allons donc corriger ce problème !

Sur Windows

Retournez dans les onglets des TCD et faites apparaitre le module. Cliquez sur l'étiquette de colonne « Somme sur Age » puis sur « Paramètres des champs de valeur ».

Une fenêtre s'ouvre. Dans la liste déroulante de gauche, cliquez sur « Moyenne », puisque nous voulons une moyenne d'âge :

Le bouton « Format de nombre » en dessous vous permet de mettre en forme vos données avec des formats que vous connaissez bien (Standard, Nombre etc.). Allez faire un tour de ce côté là si vous le souhaitez pour par exemple avoir des moyennes avec une ou deux décimales.

Cliquez sur « Ok ». Vous voyez ainsi que les joueuses et les joueurs de Urban Terror ont en moyenne 18 ans.

Avec le filtre des jeux, vous pouvez passer aux Sims 3 par exemple. Comme votre mise en forme ne bouge pas, vous voyez que les joueuses des Sims 3 ont en moyenne 18 ans et les joueurs 21 ans, la moyenne d'âge de tous les joueurs des Sims 3 étant de 19 ans.

En plaçant le filtre sur « Tous », vous avez la moyenne d'âge de tous les participants de la soirée, ainsi que les moyennes d'âge de toutes les femmes et de tous les hommes. :D

Bien, non ? Mais vous n'êtes pas encore au bout de vos surprises en ce qui concerne la mise en forme d'un TCD.

Des pourcentages !

Les pourcentages sont très pratiques dans l'analyse d'une longue liste car ils vous permettent de visualiser des données en un clin d'œil. Voici la nouvelle disposition du TCD à réaliser :

Nous voulons tout simplement savoir quel est l'âge le plus représenté parmi les joueurs. Faites un double-clic sur « NB sur Jeu ». Dans la fenêtre qui s'ouvre, cliquez sur l'onglet « Afficher les valeurs ». Dans la liste déroulante, choisissez « % du total général ». Cliquez ensuite sur « Format de nombre » puis sélectionnez « Pourcentage ». Vous savez ainsi à combien de % sont représentés les âges à sa soirée Jeux.

Les joueurs de 18 ans sont les plus représentés

Exact, d'ailleurs, tel quel, c'est difficilement lisible et les filtres auraient pu faire ce travail plus proprement et en moins de temps. Pour pallier ce problème, nous allons utiliser « les groupes ».

Sur Mac

Retournez dans la fenêtre de disposition de votre TCD et faites un double-clic sur l'étiquette de colonne « Somme sur Age ».

Une fenêtre s'ouvre. Dans la liste déroulante de gauche, cliquez sur « Moyenne », puisque nous voulons une moyenne d'âge.

Le bouton « Nombre » à droite vous permet de mettre en forme vos données avec des formats que vous connaissez bien (Standard, Nombre etc.). Allez faire un tour de ce côté là si vous le souhaitez pour par exemple avoir des moyennes avec une ou deux décimales.

Cliquez sur « Ok ». Vous voyez ainsi que les joueuses et les joueurs de Urban Terror ont en moyenne 18 ans.

Avec le filtre des jeux, vous pouvez passer aux Sims 3 par exemple. Comme votre mise en forme ne bouge pas, vous voyez que les joueuses des Sims 3 ont en moyenne 18 ans et les joueurs 21 ans, la moyenne d'âge de tous les joueurs des Sims 3 étant de 19 ans.

En plaçant le filtre sur « Tous », vous avez la moyenne d'âge de tous les participants de la soirée, ainsi que les moyennes d'âge de toutes les femmes et de tous les hommes :D .

Chouette n'est ce pas ? Mais vous n'êtes pas encore au bout de vos surprises en ce qui concerne la mise en forme d'un TCD.

Des pourcentages !

Les pourcentages sont très pratiques dans l'analyse d'une longue liste car ils vous permettent de visualiser des données en un clin d'œil. Voici la nouvelle disposition du TCD à réaliser :

Nous voulons tout simplement savoir quel est l'âge le plus représenté parmi les joueurs. Faites un double-clic sur « NB sur Jeu ». Dans la fenêtre qui s'ouvre, cliquez sur « Options ». Dans la liste déroulante, choisissez « % du total » et validez les différentes boîtes de dialogue. Vous savez ainsi à combien de % sont représentés les âges à sa soirée Jeux.

Les joueurs de 18 ans sont les plus représentés

Exact, d'ailleurs, tel quel, c'est difficilement lisible et les filtres auraient pu faire ce travail plus proprement et en moins de temps. Pour pallier ce problème, nous allons utiliser « les groupes ».

Reprenons le TCD précédent :

On souhaite faire des groupes d'âge afin que le tout soit mieux ordonné et lisible. Les 10-15 ans, les 15-20 ans, les 20-30 ans et le plus de 30 ans.

Sélectionnez tous les âges compris entre 10 et 15 ans. Dans l'onglet « Données », dans le groupe « Plan », cliquez sur le bouton « Grouper ».

Une colonne s'est créée dans le TCD, vous n'avez plus qu'à la nommer en tapant directement votre texte (10-15). Faites de même pour les autres âges.

Un double-clic sur chaque groupe vous permet de masquer les détails des âges et d'avoir des statistiques globales :

Les 15-20 ans étaient donc les plus représentés à la soirée Jeux.

Notez que les groupes peuvent également être utilisés sur des dates exactement de la même manière.

Résumons

• Un double-clic sur l'étiquette de colonne schématisée dans le menu « Disposition » permet d'accéder à des options de mises en forme (moyenne, pourcentages etc.)

• Les groupes, accessibles à partir du menu « Données » permettent de grouper des éléments du TCD en un paquet, et donc de simplifier sa lecture.

Petit exercice

Ayant pour support la liste de données sur laquelle nous avons travaillé, sauriez-vous retrouver le gagnant de chaque jeu ?

Vous savez désormais vous servir des TCD. Notez que c'est désormais à votre tour d'explorer tous les boutons que nous avons évoqués mais pas explorés. A vous de mettre en forme vos données selon vos goûts et vos besoins !

Après cela, nous allons introduire sur une toute nouvelle notion : les macros, avant d'attaquer la troisième partie.

Une macro, c'est un nom bien barbare pour désigner un concept puissant.

C'est une suite d'instructions que vous exécutez quand vous voulez. :-°

Pour exécuter ces instructions, il faut appeler la macro... par son nom !

Eh oui, une macro, ça a un nom. Maintenant, je vais vous donner un petit exemple :

Imaginez une macro qui a pour nom « modifications_du_texte ».

Jusque-là, ça va. Les instructions qu'elle doit exécuter sont : mise en gras du texte, puis mise en rouge de celui-ci.

Lorsque vous appellerez la macro « modifications_du_texte » sur une cellule qui contient du texte, ce dernier sera mis en gras et en rouge. :D

Les macros sont pratiques si vous avez régulièrement une longue suite d'actions à effectuer.

Mais tout ceci ne se limite pas qu'à une exécution d'instructions. Vous pouvez également coder une interface pour échanger avec l'utilisateur ! (via des boîtes de dialogue)

C'est l'objet de la sous-partie suivante, si vous voulez bien me suivre... :)

Maintenant que vous savez ce qu'est une macro, il est temps de la faire, cette série d'instructions, non ? :euh:

Eh bien oui, allons-y !

Pour la gestion des macros, il faut activer l'onglet « Développeur ». Pour cela, cliquez sur le menu « Fichier », puis sur « Option Excel ». Dans la fenêtre qui s'ouvre, alors dans l'onglet « Personnaliser le ruban ».

Dans la liste de droite des onglets du ruban, cochez la case devant l'onglet nommé « Développeur ». Cliquez enfin sur « OK ».

L'onglet « Développeur » vient d'alors d'apparaitre. Allez-y. Dans le groupe « Code », cliquez sur « Enregistrer une macro » :

Une fenêtre s'ouvre, vous demandant de renseigner le nom de la macro.

Notre macro mettra le texte en gras et en rouge, je l'ai donc nommée « gras_rouge ».

Cliquez sur « OK ».

Maintenant, ouvrez la fenêtre Format de cellule (voir chapitre 2 si besoin), mettez le texte en gras et en rouge.

Appuyez sur « OK » : vous revenez au tableur.

Nous allons maintenant arrêter l'enregistrement de notre macro.

Retournez dans le groupe « Code » de l'onglet « Développeur » puis cliquez sur « Arrêter l'enregistrement » :

L'enregistrement est maintenant coupé. Ouf, on respire ! :-°

Vous venez d'enregistrer une série d'instructions qui constitue une macro.

Alors forcement, pour que cette série s'exécute, il faut appeler la macro !

Naturellement, c'est l'objet de la sous-partie suivante... :D

Nous allons faire un bouton qui permettra d'exécuter notre macro. :)

Dans votre feuille de calcul, sélectionnez une cellule et tapez un texte quelconque.

Ensuite de quoi vous devez aller dans l'onglet « Développeur, dans le groupe « Contrôles » puis cliquez sur « Insérer ». Dans le menu, cliquez sur « Bouton de contrôle » :

Dessinez le rectangle correspondant au bouton de la macro. Suite à quoi Excel vous demande quelle macro vous souhaitez y assigner. Sélectionner votre nouvelle macro. Le bouton est mis en forme.

Sélectionnez la cellule qui contient du texte et appuyez sur le rectangle... magique, non ? :magicien:

Dans le groupe « Code », cliquez sur le bouton « Macros », sélectionnez votre macro et cliquez sur « Modifier ».

Vous pouvez aussi directement cliquer sur le bouton « Visual Basic ».

La fenêtre Visual Basic s'ouvre alors. Une fenêtre s'ouvre à l'intérieur, elle contient votre macro :

Bien vu, c'est un premier point.

Ce que vous avez sous les yeux, c'est le code de votre macro. En effet, lorsque vous enregistrez votre macro, sans que vous ne le sachiez, Excel génère du code qui est en fait la source de votre macro.

Quand vous appelez la macro, vous exécutez ce code. ;)

Parce qu'il faut parler à Excel, qui lui-même parlera à l'ordinateur. Microsoft a donc développé un langage de programmation : le VBA, qui vous permet de personnaliser vos macros. :D

La deuxième partie est achevée et vous êtes capables de créer de beaux tableaux. :)

Si vous êtes intéressés par les entrailles des macros (autrement dit, par la programmation), sachez que nous verrons tout cela dans la troisième partie.