Version en ligne

Tutoriel : Analysez des données avec Excel

Table des matières

Analysez des données avec Excel
Excel : le logiciel d'analyse de données
Présentation
Installation
Démarrage
Interface
Excel sous Mac
Créez votre premier classeur
Créons votre premier classeur
Sélectionner la cellule et saisir les données
Formats, embellissement et poignée de recopie incrémentée
Sauvegardons votre classeur
Accélérez la saisie !
Une liste personnalisable
Une vraie liste de données
Éviter les erreurs
A l'assaut des formules
Une bête de calculs
Les conditions
La poignée de recopie incrémentée
Transmettre des informations entre différents feuillets
D'où vient ma formule et où va-t-elle ?
Exercice : c'est férié aujourd'hui !
Trier ses données
Le tri
Faciliter la saisie de données
Une liste déroulante
Les listes
Les filtres, une puissance négligée
Mettre en place son filtre
Analyser sa liste avec la fonction SOMMEPROD
Les graphiques
Des données brutes
Dessinons le graphique !
Modélisez vos propres courbes !
Les tableaux croisés dynamiques 1/2
Les tableaux quoi ?
Fabriquons un TCD !
Modification du TCD
Les tableaux croisés dynamiques 2/2
Mettre en forme un TCD
Les groupes
Les macros
Une macro, c'est quoi ?
Fabriquons la macro !
Exécution de la macro
Outils d'analyses de simulation
La valeur cible
Le solveur
Premiers pas en VBA
Du VBA, pour quoi faire ?
L'interface de développement
Codez votre première macro !
Les commentaires
Le VBA : un langage orienté objet
Orienté quoi ?
La POO en pratique avec la méthode Activate
Les propriétés
La sélection
Sélectionner des cellules
Sélectionner des lignes
Sélectionner des colonnes
Exercice : faciliter la lecture dans une longue liste de données
Les variables 1/2
Qu'est ce qu'une variable ?
Comment créer une variable ?
Que contient ma variable ?
Types de variables
Les variables 2/2
Les tableaux
Portée d'une variable
Définir son type de variable
Les conditions
Qu'est ce qu'une condition ?
Créer une condition en VBA
Simplifier la comparaison multiple
Commandes conditionnelles multiples
Les boucles
La boucle simple
La boucle For Each
La boucle Do Until
La boucle While.. Wend
Sortie anticipée d'une boucle
Exercice : un problème du projet Euler sur les dates
Modules, fonctions et sous-routines
(Re)présentation
Notre première fonction
L'intérêt d'une sous-routine
Publique ou privée ?
Un peu plus loin...
Les boîtes de dialogue usuelles
Informez avec MsgBox !
Glanez des infos avec InputBox
Les fonctions d'Excel
Carte d'identité de la fonction
Les fonctions Mathématiques
Les fonctions Logiques
Les fonctions de Recherche et Référence
Les fonctions Statistiques
Les fonctions Texte
Les fonctions Date et Heure
Bonnes pratiques et débogage des formules
Bonnes pratiques
Débogage des formules
Corrections orthographiques
Vérifications grammaticales et linguistiques
Utilisation du classeur
Imprimons votre classeur

Analysez des données avec Excel

Image utilisateur
Image utilisateur

Microsoft Office Excel 2010.

Difficile de ne pas avoir vu ce nom au moins une fois quelque part. Excel est un des éléments d'une suite bureautique très complète : Office. Ce logiciel est le leader dans son domaine, sa maîtrise en est aujourd'hui plus ou moins indispensable.

Vous l'avez sur votre ordinateur et vous ne savez pas à quoi ça sert ? Vous avez une vague idée de son utilité mais ça vous paraît trop compliqué ? En clair, vous souhaitez vous lancer dans la bureautique pour vos besoins ? Et puis, c'est quoi "la bureautique" ?

Autant de questions auxquelles il faudra commencer par répondre dans un chapitre d'introduction qui vous montrera les intérêts de ce que l'on appelle plus communément les tableurs.

Évidemment, nous partons de Zér0. Chaque notion importante d'Excel va ici faire l'objet d'un chapitre. Nous abordons le thème au travers d'un ou plusieurs exemples, afin de vous fournir la méthode. Libre à vous de combiner plusieurs notions dans un même travail, c'est d'ailleurs tout l'intérêt de ce genre de logiciels. :)

Vous l'aurez compris, vous allez apprendre ici à vous servir d'un logiciel. Autrement dit, n'attendez pas d'avoir terminé la lecture du cours pour allumer votre ordinateur. L'idéal est de faire des tests pendant et après la lecture. Ce n'est que de cette manière que vous utiliserez au mieux la puissance d'Excel.

Excel : le logiciel d'analyse de données

Présentation

Excel est issu de la suite de logiciels bureautiques Office. Plutôt couteuse, elle contient notamment un logiciel de traitement de texte - le célèbre Word - qui vous permet de taper et de mettre en forme vos documents textes et images.

Excel ? Qu'est-ce que c'est ? À quoi sert-il ?

Comment l'installer ?

Comment se présente son interface ?

Voici toutes les questions auxquelles nous allons répondre dans ce chapitre.

Présentation

Excel : le logiciel d'analyse de données Installation

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

Dis donc, quel scoop ! Tu ne veux pas nous donner la définition de l'analyse de données tant que tu y 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.

Image utilisateur

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.


Excel : le logiciel d'analyse de données Installation

Installation

Présentation Démarrage

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 :

Image utilisateur

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 :

Image utilisateur

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

Ici, vous devez choisir le type d'installation :

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.


Présentation Démarrage

Démarrage

Installation Interface

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

Image utilisateur

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

Image utilisateur

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

Image utilisateur
Image utilisateur

Installation Interface

Interface

Démarrage Excel sous Mac

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 :

Image utilisateur

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.

Image utilisateur

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

Image utilisateur

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 :

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.

Image utilisateur

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

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

Des 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


Démarrage Excel sous Mac

Excel sous Mac

Interface Créez votre premier classeur

Image utilisateur

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 :

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur

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

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


Interface Créez votre premier classeur

Créez votre premier classeur

Excel sous Mac Créons votre premier classeur

La théorie du dernier chapitre vous a convaincu que vous avez besoin d'un tableur ? Vous souhaitez approfondir vos connaissances avec Excel, que vous utilisez au boulot ou chez vous ? Bienvenue dans le premier chapitre où vous allez commencer à manipuler !

Les bases d'Excel y seront abordées. Si vous estimez tout savoir, lisez ce chapitre tout de même, c'est vraiment important.

Plusieurs versions d'Excel existent. Sous Windows, sont utilisées encore aujourd'hui Excel 2003, Excel 2007 et, la dernière, Excel 2010. Mais aussi sous Mac, il existe d'autres versions d'Excel (Excel 2008 et Excel 2011 principalement).

Pour que le maximum de personnes puisse profiter de ce cours, nous traiteront de :

Dans tout le cours, nous vous proposerons de télécharger des documents Excel. Dans la dernière partie de ce cours, vous serez par ailleurs amené à télécharger des scripts VBA. Nous le signalerons grâce à l'icône :

Image utilisateur

Créons votre premier classeur

Créez votre premier classeur Sélectionner la cellule et saisir les données

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 :

Image utilisateur

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 :

Image utilisateur

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.


Créez votre premier classeur Sélectionner la cellule et saisir les données

Sélectionner la cellule et saisir les données

Créons votre premier classeur Formats, embellissement et poignée de recopie incrémentée

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 :

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur
Image utilisateur

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 :

Image utilisateur

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 :

Image utilisateur

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 !

Image utilisateur

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.

Image utilisateur

Le tour est joué !

On peut faire de même pour les lignes.


Créons votre premier classeur Formats, embellissement et poignée de recopie incrémentée

Formats, embellissement et poignée de recopie incrémentée

Sélectionner la cellule et saisir les données Sauvegardons votre classeur

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

Image utilisateur

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 :

Image utilisateur

Une fenêtre avec plusieurs onglets s'ouvre :

Image utilisateur

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

Image utilisateur

Laissez les options par défaut puis cliquez sur « OK » : vous revenez au tableau.

Vos données sont maintenant sous un format monétaire !

Image utilisateur

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

Image utilisateur

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

Image utilisateur

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

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur

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

Image utilisateur

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

Image utilisateur

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 !

Image utilisateur

Ç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 !


Sélectionner la cellule et saisir les données Sauvegardons votre classeur

Sauvegardons votre classeur

Formats, embellissement et poignée de recopie incrémentée Accélérez la saisie !

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 :

Image utilisateur

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 :

Image utilisateur

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

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 !


Formats, embellissement et poignée de recopie incrémentée Accélérez la saisie !

Accélérez la saisie !

Sauvegardons votre classeur Une liste personnalisable

Nous continuons à voir comment saisir et mettre en forme nos données dans les fameuses cellules, qui constituent la base d'une feuille :) . Grâce aux bordures de cellules que vous maîtrisez bien désormais, vous pouvez même faire un tableau et le compléter ligne par ligne ou colonne par colonne au fur et à mesure de vos besoins. Soit, c'est nécessaire au cas par cas mais long si il y a un flot monstrueux de données à saisir.

Dans ce chapitre, vous allez apprendre à augmenter votre productivité dans le tableur Excel grâce à des outils méconnus. Autrement dit, vous saisirez plus vite, ce qui peut vite devenir pratique lorsque vous avez beaucoup d'informations à saisir et à organiser (ce qui sera généralement le cas).

Une liste personnalisable

Accélérez la saisie ! Une vraie liste de données

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 :

Image utilisateur

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 :

Image utilisateur

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

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur
Image utilisateur

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

Image utilisateur

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 :

Image utilisateur

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 !

Image utilisateurTélécharger le fichier liste_personnalisable.xlsx


Accélérez la saisie ! Une vraie liste de données

Une vraie liste de données

Une liste personnalisable Éviter les erreurs

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

Pourquoi tu insistes depuis tout à l'heure sur le fait qu'une liste doit être exploitable ?

Avec un exemple, vous allez comprendre :

Voici une liste de données. Ces dernières sont numériques mais ce n'est pas grave : elles auraient très bien pu être littérales, voire les deux à la fois :

Image utilisateur

Voici une autre liste, sous forme de tableau :

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur

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

Image utilisateur

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

Image utilisateur
Image utilisateur

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

Image utilisateur

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 !


Une liste personnalisable Éviter les erreurs

Éviter les erreurs

Une vraie liste de données A l'assaut des formules

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 :

Image utilisateur

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 .

Image utilisateur

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é. ;)

Image utilisateur

Résumons

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 :) .


Une vraie liste de données A l'assaut des formules

A l'assaut des formules

Éviter les erreurs Une bête de calculs

Ce chapitre est très important ! Les formules vont en effet vous permettre de calculer à partir de données numériques. Notez que grâce aux formules, vous pourrez aussi gérer vos données alphabétiques. Les formules font le lien entre la saisie et l'analyse de données. Elles vont par exemple vous permettre de calculer le total des points de tous les joueurs en fonction des scores de chaque niveau.

Le chapitre sera un peu long, alors ne lisez pas tout d'un coup. ;)

Une bête de calculs

A l'assaut des formules Les conditions

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 :

Image utilisateur

Mais comment on lui fait passer des données, et où sera affiché le résultat ?

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

Voici la syntaxe d'une formule 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 :

Image utilisateur

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)

Image utilisateur

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

Et pourquoi on n'a pas écrit directement = 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.

Image utilisateur

Commençons à taper la formule...

Image utilisateur

Et là, attention, les choses intéressantes commencent : sélectionnez votre plage de cellules :

Image utilisateur

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.

Image utilisateur

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)


A l'assaut des formules Les conditions

Les conditions

Une bête de calculs La poignée de recopie incrémentée

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")

Je mets quoi à la place de « condition » ?

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

Opérateur

Description

=

Est égal à...

>

Est supérieur à...

<

Est inférieur à...

>=

Est supérieur ou égal à...

<=

Est inférieur ou égal à...

<>

Est différent de...

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

Voici comment ça se passe dans Excel :

Image utilisateur

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

Image utilisateur

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

Image utilisateur

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

Image utilisateur
Image utilisateur
Image utilisateur

Schémas de la condition multiple en OU

Image utilisateur
Image utilisateur
Image utilisateur

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")

Je mets quoi à la place de « opérateur logique » ?

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 :

Image utilisateur

Dans ce cas, il affiche OUI.

Mettez la valeur à 12, par exemple, il affichera NON.


Une bête de calculs La poignée de recopie incrémentée

La poignée de recopie incrémentée

Les conditions Transmettre des informations entre différents feuillets

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 :

Image utilisateur

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 (+) :

Image utilisateur

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 :

Image utilisateur

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 :

Image utilisateur

Et à quoi correspondent ces dollars $ dans les formules. Jamais vu encore... ?

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 :

Image utilisateur

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 :

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur

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.


Les conditions Transmettre des informations entre différents feuillets

Transmettre des informations entre différents feuillets

La poignée de recopie incrémentée D'où vient ma formule et où va-t-elle ?

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 :

Image utilisateur

Dans le feuillet « Source », tapez en B2 une donnée (numérique ou non, peu importe).

Image utilisateur

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


La poignée de recopie incrémentée D'où vient ma formule et où va-t-elle ?

D'où vient ma formule et où va-t-elle ?

Transmettre des informations entre différents feuillets Exercice : c'est férié aujourd'hui !

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 :

Image utilisateur

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.

Image utilisateur

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.

Image utilisateur

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.


Transmettre des informations entre différents feuillets Exercice : c'est férié aujourd'hui !

Exercice : c'est férié aujourd'hui !

D'où vient ma formule et où va-t-elle ? Trier ses données

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

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.


D'où vient ma formule et où va-t-elle ? Trier ses données

Trier ses données

Exercice : c'est férié aujourd'hui ! Le tri

Il peut être intéressant d'effectuer un premier tri dans ses données avant traitement. Par exemple, si vous demandez à un utilisateur de rentrer dans une cellule une date entre le 01 Janvier 2008 et le 31 Décembre 2012, qui peut garantir que la date saisie sera bien dans cet intervalle ? Il y aura toujours quelqu'un pour écrire une date de l'année 1999 ou 2013. Les outils que nous allons découvrir dans ce chapitre vous permettront de ne pas vous tromper dans la saisie, mais aussi de la simplifier... :)

Pour tout vous dire, nous allons nous intéresser au menu « Données ».

Nous allons nous servir ensemble d'une liste que je vous propose de télécharger :

Image utilisateurTélécharger le fichier donnees.xlsx

Le tri

Trier ses données Faciliter la saisie de données

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.

Image utilisateur

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

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur

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

Image utilisateur

Trier ses données Faciliter la saisie de données

Faciliter la saisie de données

Le tri Une liste déroulante

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

Image utilisateur

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 :

Image utilisateur

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

Image utilisateur

Une fenêtre s'ouvre alors :

Image utilisateur

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

Image utilisateur

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 !

Image utilisateur

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

Image utilisateur

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

Image utilisateur

Puis essayez de taper une donnée non valide :

Image utilisateur

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 :

Image utilisateur

Ce qui donne :

Image utilisateur

Le tri Une liste déroulante

Une liste déroulante

Faciliter la saisie de données Les listes

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 :

Image utilisateur

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.

Image utilisateur

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.

Image utilisateur

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.

Image utilisateur

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


Faciliter la saisie de données Les listes

Les listes

Une liste déroulante Les filtres, une puissance négligée

Les listes de données... vous les croyez enterrées ? Eh bien non, elles reviennent à la charge ! :pirate: Ce coup-ci, c'est pour l'analyse et non pour la saisie. Au programme : listes déroulantes à gogo et quelques formules bien mastoc ! Le tout pour vous y retrouver, filtrer et impressionner la galerie !

Pour pouvoir vous entraîner avec moi durant ce chapitre, je vous propose un nouveau classeur à télécharger :

Image utilisateurTélécharger le fichier liste.xlsx

Les filtres, une puissance négligée

Les listes Mettre en place son filtre

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 :

Image utilisateur

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. ;)


Les listes Mettre en place son filtre

Mettre en place son filtre

Les filtres, une puissance négligée Analyser sa liste avec la fonction SOMMEPROD

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

Image utilisateur

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 :

Image utilisateur

Voilà qui est fait.

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

Image utilisateur

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

Image utilisateur

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 :

Image utilisateur

Une fenêtre s'ouvre alors :

Image utilisateur

À 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. :)


Les filtres, une puissance négligée Analyser sa liste avec la fonction SOMMEPROD

Analyser sa liste avec la fonction SOMMEPROD

Mettre en place son filtre Les graphiques

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

Bon, tu nous dis du bien de cette fonction, mais on ne sait pas vraiment ce qu'elle a de si particulier juste en voyant son nom... et encore moins sans description !

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 :

Image utilisateur

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

Image utilisateurTé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 ! :-°


Mettre en place son filtre Les graphiques

Les graphiques

Analyser sa liste avec la fonction SOMMEPROD Des données brutes

Dans la suite des listes, nous continuons notre tour des fonctionnalités utiles et intéressantes d'Excel.

Puisque vous savez déclarer une liste de données propre et lisible, il est temps de l'exploiter encore plus ! :pirate:

C'est ce que nous allons voir ici avec les graphiques. Le principe est simple : vous donnez une liste à Excel et paf ! Il vous pond un joli graphique selon vos critères.

Il n'y a rien de bien difficile dans les graphiques. Ils sont d'ailleurs à terme assez peu utilisés. Un graphique, ça prend de la place et ce n'est pas forcement ce qu'il y a de mieux pour analyser une liste... Mais tout dépend de vos besoins !

Image utilisateurImage utilisateurImage utilisateurDes graphiques basiques : Excel propose de nombreux modèles de graphiques prédéfinis à adapter à une même liste de données.

Nous verrons ensuite qu'il est possible d'utiliser le graphique dans les sciences expérimentales ! Grâce à des valeurs saisies manuellement, vous pourrez dessiner une courbe de tendance, la modéliser selon des modèles mathématiques pré-définis et même afficher son équation. Que du bon ! :D

Des données brutes

Les graphiques Dessinons le graphique !

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 :

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.

Image utilisateur

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

Image utilisateurTélécharger le fichier graphique.xlsx

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


Les graphiques Dessinons le graphique !

Dessinons le graphique !

Des données brutes Modélisez vos propres courbes !

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 :

Image utilisateur

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

Image utilisateur

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

Image utilisateur

Une fenêtre s'ouvre alors :

Image utilisateur

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 :

Image utilisateur

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 :

Image utilisateur

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.

Image utilisateurLa 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.


Des données brutes Modélisez vos propres courbes !

Modélisez vos propres courbes !

Dessinons le graphique ! Les tableaux croisés dynamiques 1/2

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 :) .

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

Image utilisateur

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

Image utilisateur

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 :) .

Image utilisateur

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

Image utilisateurLa 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...


Dessinons le graphique ! Les tableaux croisés dynamiques 1/2

Les tableaux croisés dynamiques 1/2

Modélisez vos propres courbes ! Les tableaux quoi ?

Maintenant que vous savez parfaitement saisir des données, les analyser d'une manière plus que basique, il est temps de s'attaquer à l'outil d'analyse le plus puissant d'Excel : « les tableaux croisés dynamiques ».

Le nom de cette notion est déroutant, nous vous l'accordons. Cette fonctionnalité du tableur est assez peu utilisée, pourtant rudement pratique, notamment sur les longues listes, et est un peu plus compliquée que les autres.

Prérequis fondamental : les listes de données. Car comme bien souvent, on analyse une liste, il faut donc être au point une bonne fois pour toutes dessus.

Les tableaux quoi ?

Les tableaux croisés dynamiques 1/2 Fabriquons un TCD !

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

Mais alors où est le piège si c'est un simple 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.


Les tableaux croisés dynamiques 1/2 Fabriquons un TCD !

Fabriquons un TCD !

Les tableaux quoi ? Modification du TCD

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 :

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 :

Image utilisateurTé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 » :

Image utilisateur

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 :

Image utilisateur

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

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

Image utilisateur

Le module de champs de tableau croisé dynamique

Image utilisateur

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 :

Image utilisateur

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 !

Image utilisateur

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 !

Image utilisateur

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.


Les tableaux quoi ? Modification du TCD

Modification du TCD

Fabriquons un TCD ! Les tableaux croisés dynamiques 2/2

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 :

Image utilisateur

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

Image utilisateur

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.

Image utilisateur

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

Image utilisateur

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

Continuons maintenant sur notre lancée.


Fabriquons un TCD ! Les tableaux croisés dynamiques 2/2

Les tableaux croisés dynamiques 2/2

Modification du TCD Mettre en forme un TCD

Nous continuons notre tour d'exploration des TCD. Nous allons nous concentrer ici sur la mise en forme des données, afin d'obtenir les statistiques désirées dans des formes variées et intéressantes.

Mettre en forme un TCD

Les tableaux croisés dynamiques 2/2 Les groupes

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

Image utilisateur

Le tableau sur Windows

Image utilisateur

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 :

Image utilisateur

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.

Image utilisateur

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 :

Image utilisateur

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.

Image utilisateur

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

Oui mais si 3 fois plus d'âges étaient représentés ? Le TCD aurait été 3 fois plus long et difficilement lisible !

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.

Image utilisateur

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.

Image utilisateur

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 :

Image utilisateur

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.

Image utilisateur

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

Oui mais si 3 fois plus d'âges étaient représentés ? Le TCD aurait été 3 fois plus long et difficilement lisible !

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


Les tableaux croisés dynamiques 2/2 Les groupes

Les groupes

Mettre en forme un TCD Les macros

Reprenons le TCD précédent :

Image utilisateur

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.

Image utilisateur

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

Image utilisateur

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

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.


Mettre en forme un TCD Les macros

Les macros

Les groupes Une macro, c'est quoi ?

La lecture de ce chapitre est indispensable si vous souhaitez poursuivre sur la prochaine partie, ce qui n'est pas obligatoire pour maîtriser le tableur Excel.

Ce chapitre va donc introduire sur un nouvel outil très puissant : les macros.

Ne vous enfuyez pas en lisant le nom ! Même si vous ne poursuivrez pas le tutoriel sur la troisième partie, les macros apporteront une touche de modernité à vos feuilles de calculs. :)

Une macro, c'est quoi ?

Les macros Fabriquons la macro !

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)

Et comment on la fabrique, cette macro ?

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


Les macros Fabriquons la macro !

Fabriquons la macro !

Une macro, c'est quoi ? Exécution de la macro

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

Image utilisateur

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

Image utilisateur

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.

Image utilisateur

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

Image utilisateur

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

Mais mais... il ne se passe rien ! Qu'est-ce que c'est que ça ?? o_O

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


Une macro, c'est quoi ? Exécution de la macro

Exécution de la macro

Fabriquons la macro ! Outils d'analyses de simulation

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

Image utilisateur

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.

Image utilisateur

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

Image utilisateur

Oui, c'est cool ton truc mais il y a quelque chose qui me tracasse : où est-ce que je peux modifier ma macro ?

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

Image utilisateur

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 :

Image utilisateur

Mais... ouark, misère ! C'est quoi tout ça ?? :waw:

On dirait... du code, non ?

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. ;)

Et pourquoi du 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.


Fabriquons la macro ! Outils d'analyses de simulation

Outils d'analyses de simulation

Exécution de la macro La valeur cible

Excel est un outil de gestion de données mais il peut aussi faire le travail à notre place dans beaucoup de situations. C'est le cas avec les outils d'analyse de simulation. Ces outils permettent de connaître les situations possibles grâce au modèle déjà en place et donc en émettant des hypothèses et connaître les résultats de ces hypothèses. Il existe plusieurs outils sous Excel, mais les deux plus importants que nous allons étudier sont la valeur cible et le solveur.

En effet, vous avez, dans votre classeur, un grand nombre de formule dépendante des unes et des autres. Vous souhaitez faire varier le résultat en changeant les valeurs de certaines cellules. Sans ces outils, vous allez essayer en modifiant les unes après les autres les valeurs sans vous souvenir du résultat que les différentes combinaisons donnent et donc passer un temps fou à connaître la meilleur combinaison possible. Nous allons donc apprendre à créer des scénarios qui vont nous donner les combinaisons les meilleures possibles.

La valeur cible

Outils d'analyses de simulation Le solveur

Commençons doucement avec le premier outil : la valeur cible. Cet outil permet de déterminer le résultat voulu et Excel va changer lui même les valeurs affectant ce résultat pour trouver une combinaison possible et réelle. Vous aurez plus qu'à regarder si les valeurs vous conviennent. Un exemple est plus efficace qu'un long discours.

Prenons cet exemple :

Image utilisateur

Je souhaite fixer mon prix de vente en fonction du prix d'achat et du taux de marque. Mon prix de vente est calculée par rapport à ces deux valeurs de la façon suivante :

C5=C3/(1-C4)

Je souhaite maintenant baisser mon prix de vente. Pour cela, je dispose de deux possibilités : soit je baisse le taux de marque soit je baisse mon prix d'achat. Mon taux de marque, c'est moi qui le fixe, c'est assez facile de le baisser. Le prix d'achat, il va falloir négocier avec le fournisseur...

Sans la valeur cible, comment faire pour avoir 90€ pile dans la cellule C5 ?

Facile ! Je modifie les autres cellules jusqu'à ce que ça tombe jute !

Allez-y, appelez moi quand vous avez trouvé... Bon là, l'exemple est simple et c'est possible assez facilement. Mais lorsque les calculs sont plus complexes, la fonction valeur cible est là !

Hypothèse 1 : on négocie le prix d'achat que l'on va faire baisser.

Hypothèse 2 : on accepte de faire baisser notre taux de marque.

Allez c'est parti, testons. Pour ouvrir la fonction valeur cible allez dans l'onglet Données, dans le groupe Outils de données cliquez sur Analyse de scénarios puis sur Valeur cible... une fenêtre s'ouvre.

Image utilisateur

Il suffit alors de remplir les trois champs :

Dans notre cas, la cellule à définir est la cellule C5, la valeur à atteindre est de 90 et dans l'hypothèse 1 c'est le prix d'achat qui change donc la cellule C3. Essayez, et voyez le résultat ! Le résultat s'affiche dans une fenêtre comme ceci :

Image utilisateur

Cliquez sur OK si vous êtes d'accord avec le résultat ou sur annuler pour l'annuler. Si vous cliquez sur Ok, la valeur de la cellule C3 change et passe à 72€.

Maintenant, il n'est plus possible de changer le prix d'achat, il va falloir jouer sur le taux de marque. C'est l'hypothèse 2. Si besoin, remettez la valeur de la cellule C3 à 80 et recommencez la procédure. Dans la fenêtre qui s'ouvre la cellule à définir ne change pas et reste la cellule C5, sa valeur à atteindre ne varie pas non plus et est de 90. Par contre, la valeur à modifier n'est plus C3 mais C4. Cliquez sur Ok et voyez le résultat ! Excel calcul qu'il faut descendre le taux de marque à 11%.

Un outil très simple et efficace pour trouver une valeur. Seulement maintenant, je souhaiterais modifier les deux valeurs pour avoir le même résultat... Je veux bien négocier mais je veux bien aussi baisser mon taux de marque. Pour cela, Excel propose un autre outil : le solveur.


Outils d'analyses de simulation Le solveur

Le solveur

La valeur cible Premiers pas en VBA

Avant de commencer à l'utiliser, il faudrait qu'il soit disponible. On va donc commencer par l'installer. Pour cela, cliquez sur le gros bouton Office en haut à gauche du logiciel puis sur Options Excel, dans la catégorie Compléments du volet de gauche. En bas de la fenêtre, dans la liste Gérer vérifiez que Compléments Excel soit sélectionné. Cliquez sur le bouton Atteindre, dans la fenêtre qui s'ouvre cochez Complément Solver et cliquez sur Ok. Patientez le temps de l'installation.

Si tout a bien fonctionné, dans l'onglet Données est apparu un groupe tout à droite Analyse avec dans ce groupe Solver.

Image utilisateur

Voilà, nous sommes parés pour utiliser le solveur.

Les outils d'analyses de simulation sont donc très pratiques et puissants à partir du moment où on les utilise à bon escient. Il faut savoir qu'ils existent pour éviter d'avoir à tâtonner avec les valeurs influençant le résultat.

Arrivés à ce stade du cours, vous êtes capables de saisir et d'analyser des données avec Excel. Pourtant, ce n'est pas fini ! Si vous avez lu le chapitre sur les macros - qui permettent d'automatiser des tâches dans un classeur - vous vous êtes aperçus qu'on peut aller plus loin avec Excel.


La valeur cible Premiers pas en VBA

Premiers pas en VBA

Le solveur Du VBA, pour quoi faire ?

Dans cette partie, nous allons approfondir les macros : le temps est venu pour vous de personnaliser vos propres bestioles !

Du VBA, pour quoi faire ?

Premiers pas en VBA L'interface de développement

Relisez le chapitre sur les macros. Nous y avons vu qu'une macro est une série d'instructions. Lorsqu