UNIVERSIT IBN ZOHR

Facult des Sciences Juridiques conomiques et Sociales

Informatique

Les tableurs
Mohamed HACHIMI
FILIRE SCIENCES CONOMIQUES ET GESTION DEUXIME ANNE

Semestre 3

Table des matires

Prface

1 2 3 4 5 6 7 8

Dcouverte dun tableur

Gestion dun classeur

12

Saisir et modier des donnes

17

Selection et manipulation des cellules

25

Mise en forme des cellules

29

Crer un graphique

44

Le calcul avec un tableur

53

Fonctions logiques

72

c FSJES-Agadir 2013 Tableurs Semestre 3

Table des matires

Fonctions de comptage

81

10 Fonctions de Recherche

92

c FSJES-Agadir 2013 Tableurs Semestre 3

Prface

Ce polycopi est crit lintention des tudiants de la deuxime anne de la lire sciences
conomiques et gestion. Il est conu pour couvrir le programme dInformatique du semestre 3 : Les tableurs.

Ce document ne vous dispense pas de lassiduit au cours en ligne et ses activits. En effet,
nous ny trouverez pas les outils interactifs (QCM, Forum, Agenda, . . . ) et les explications lmes ncessaires la comprhension de lutilisation dun tableur qui, en gnral, sont donns dans la plate-forme du cours.

Pour faciliter la comprhension visuelle par le lecteur de ce document, nous avons adopt les
conventions typographiques suivantes : Saisie

Touche Bote [Bouton] Menu

: Noms de dossier, noms de chier, texte saisir. : Frappe dune touche sur le clavier : Noms des onglets et les bote de dialogues. : Boutons de commandes qui sont dans les dialogues : Noms des menus du tableur : Noms des commandes ou options dans les menus du tableur : Les commandes spares par | doivent tre appliques lune aprs lautre.

Commande

Commande : Noms des commandes dans les menus contextuels |

Par exemple, linstruction Format | Ligne | Masquer signie aller dans le menu Format et choisir loption Ligne, puis cliquer sur Masquer.

c FSJES-Agadir 2013 Tableurs Semestre 3

1
1.

Dcouverte dun tableur

Quest-ce quun tableur

Un tableur est un logiciel permettant deffectuer automatiquement des calculs sur des donnes stockes dans un tableau. Il sagit dun logiciel de cration et de manipulations interactives de tableaux numriques. Ces tableaux peuvent contenir des mots ou des chiffres mais l o un tableur devient intressant, cest que nous pouvons galement y inscrire des formules de calcul, et effectuer des calculs entre diffrentes cellules des tableaux. Le tableur peut servir aussi bien pour des activits de secrtariat an de saisir et dorganiser de nombreuses donnes, qu un niveau de prise de dcision en permettant de deffectuer des synthses, des bilans et des simulations. Il existe plusieurs tableurs. Cela dit, deux dentre eux se distinguent : Excel, de la suite bureautique Microsoft Ofce Calc, de la suite bureautique OpenOfce Le premier est le plus rpandu mais le nombre dutilisateurs du second, logiciel gratuit, ne cesse daugmenter. Notons cependant que tous les tableurs utilisent les mmes principes et que le passage des uns aux autres est gnralement trs ais. De mme, les versions de chacun de ces tableurs voluent. Les exemples illustrant ce cours ont t labors avec Excel 2003 et beaucoup des copies dcrans seraient diffrentes avec Excel 2007 et ses successeurs, par exemple. Mais, une fois de plus, les principes restent inchangs et le passage dune version lautre ne pose aucune difcult importante. Dans la suite de ce cours, nous allons considrer que Microsoft Excel est installe sur votre ordinateur.

c FSJES-Agadir 2013 Tableurs Semestre 3

1 Dcouverte dun tableur

2.
2.1.

Environnement du travail dun tableur

Dmarrage dun tableur

Sous Microsoft Windows, vous pouvez lancer un tableur de diffrentes faons : A partir du menu dmarrer : Choisir Dmarrer la suite contenant le tableur, puis le tableur. Tous les programmes, puis slectionner

A partir de la barre des tches : Cliquer sur le bouton de barre des tches associ au tableur, mais la condition que licne du tableur ait t place sur la barre des tches.

A partir du Bureau : Double-cliquer sur licne du Bureau associ au tableur, mais la condition que licne du tableur ait t place sur la barre des tches.

Dans tous ces cas, vous obtenez un document vierge, nomm Classeur1 (pour Excel) ou Sans nom 1 (pour Calc). A partir dun chier existant : Double-cliquer sur un chier dont lextension est associe au tableur (par exemple, .xls pour un chier Excel jusqu 2007, .xlsx ensuite, ou .ods pour un chier Calc). Le document concern souvre directement dans le tableur.

Les chiers crs par un programme donn possdent son extension (par exemple, les chiers Calc dOpen Ofce ont une extension .ods). Grce cette extension, Windows peut associer une application et une icne au chier document.
c FSJES-Agadir 2013 Tableurs Semestre 3

1 Dcouverte dun tableur 2.2. Prsentation de linterface

Si vous avez lanc votre tableur, il afche (un court instant) sa fentre dannonce suivie immdiatement par son cran. Voici deux prsentations, Excel 2003 et OpenOfce Calc.

On peut donc remarquer que les deux interfaces sont graphiquement trs proches et seuls quelques petits dtails changent. Lcran dun tableur a un aspect qui dpend du systme et de vos rglages antrieurs : vous pouvez le personnaliser comme vous le voulez. Ne soyez donc pas surpris si vous constatez de minimes diffrence avec ce que nous allons vous prsenter.
c FSJES-Agadir 2013 Tableurs Semestre 3

1 Dcouverte dun tableur Ainsi, lcran du tableur est compose principalement des lments suivants :

Une barre de titre : Elle afche le nom du tableur et le nom du document actif se trouvant lcran. Une barre de menus : Elle permet daccder aux diffrentes fonctions du tableur; Deux barres doutils : Elles comportent une srie dicnes, servant lancer directement les commandes les plus utilises sans avoir passer par les menus. Une barre de formule : Elle donne ladresse de la cellule active et indique son contenu. La barre de formule permet ainsi de saisir les donnes insrer dans les cellules. Feuille de calcul (avec son quadrillage) : Cest la partie blanche quadrille qui occupe lessentiel de lcran. Elle correspond la zone de travail. Une cellule est lun des petits rectangles dlimits par le quadrillage. Cellule active : La cellule active est le nom donn la cellule prte recevoir des donnes. Elle est entoure par un trait gras Le ruban remplace, depuis la version 2007, la traditionnelle barre de menus quon trouvait dans les versions prcdentes dExcel. Le ruban qui vise lefcacit dans la manire de travailler. Il rassemble les commandes les plus utilises dans des groupes prdnis partir donglets.

3.

Terminologie

Avant de pouvoir employer efcacement un tableur, il est indispensable de matriser certains termes, comme : Classeur, Feuille de calcul, Cellule, Valeur et Format dune cellule.
c FSJES-Agadir 2013 Tableurs Semestre 3

1 Dcouverte dun tableur

Le classeur :

Un chier dun tableur est nomm classeur. Il contient une ou plusieurs feuilles de calcul, prsentes sous forme donglets en bas de la page. Un nouveau classeur contient par dfaut trois feuilles de calcul. Nanmoins, il est possible de paramtrer le nombre de feuilles par dfaut, ainsi que dajouter ou de supprimer volont des feuilles dun classeur. Le nombre maximal de feuilles dun classeur nest plus limit, depuis la version 2002 dExcel, que par la quantit de mmoire disponible.

Une feuille de calcul :

Un tableur prsente les donnes et les formules sous forme dun tableau (lignes et colonnes) appel feuille de calcul. Une feuille de calcul est constitue de lignes (numrotes laide de chiffres) et de colonnes (tiquetes laide de lettres). Une feuille de calcul peut contenir un nombre variable de lignes et de colonnes. Excel propose depuis la version 2007 plus dun million de lignes sur plus de seize mille colonnes.

Une cellule :

Une cellule est lintersection entre une ligne (horizontale) et une colonne (verticale) de la feuille de calcul. Elle est identie par la combinaison de la lettre de sa colonne et du numro de sa ligne, ce qui porte le nom de coordonnes ou parfois dadresse. Par exemple, dans chaque feuille, la cellule qui se trouve lintersection de la colonne A et de la ligne 1 est donc identie par sa rfrence A1.

La valeur et le format dune cellule :

Chaque cellule peut recevoir un contenu : on parle de valeur. Cette valeur peut tre soit : saisie directement (nombre, texte, date, etc.), calcule : dans ce cas, il sagit dune formule. Une cellule possde galement un format, qui reprsente la faon dont le tableur va afcher la donne.

c FSJES-Agadir 2013 Tableurs Semestre 3

1 Dcouverte dun tableur

10

4.

Lutilisation de la souris

Pour faire fonctionner un tableur, on utilise le clavier mais aussi la souris. Rappelons, pour ceux qui nont pas une grande exprience de lordinateur, quelques mots et expressions courants qui dcrivent lusage de cette souris. Pointer sur Cliquer : Positionner lextrmit du pointeur de la souris sur llment que lon veut pointer lcran. : Appuyer une fois sur le bouton gauche de la souris : Appuyer sur le bouton de la souris et le maintenir enfonc tout en dplaant la souris, puis relcher le bouton. : Excuter une action : soit en appuyant sur la touche , soit en cliquant Entre sur un bouton marqu [OK] ou [Oui]

Double-cliquer : Appuyer deux fois, assez rapidement, sur le bouton gauche de la souris Faire glisser Valider

Sil faut appuyer sur le bouton droit de la souris, il sera indiqu : faire un clic droit. 4.1. Menu contextuel

Toutes les parties qui composent votre feuille de calcul sont des objets. Par exemple une cellule, une colonne, une ligne, un graphique, une feuille complte. Pour ouvrir un menu contextuel, pointez sur un objet (cellule, en-tte de colonne, . . . ) et faites un clic droit.

c FSJES-Agadir 2013 Tableurs Semestre 3

1 Dcouverte dun tableur

11

La force du menu contextuel est dadapter la liste des commandes disponibles au contexte de votre activit. Ainsi, lorsque vous cliquez sur une cellule, vous naccdez pas aux mmes commandes que si vous slectionnez un graphique ou une colonne. En effet, le menu propose un extrait des commandes les plus utilises concernant lobjet slectionn. 4.2. Quitter le tableur

Pour quitter le tableur, avez aussi plusieurs possibilits. Les deux quon vous conseille sont les suivantes : Avec la souris : Cliquer sur la case de fermeture de la barre de titre qui se trouve en haut droite de la fentre du tableur ou Choisir la commande Fichier | Quitter . Avec le clavier : Appuyer simultanment sur les touches Alt + . F4

c FSJES-Agadir 2013 Tableurs Semestre 3

2
1.

Gestion dun classeur

Crer un classeur

Par dfaut, un classeur vierge contenant des feuilles de calcul vierges est ouvert lcran. Si ce nest pas le cas, on peut crer un nouveau classeur avec un nom provisoire en utilisant lune des mthodes suivantes : Cliquer avec licne Nouveau de la barre doutils

Slectionner la commande Fichier | Nouveau

Pour Excel, une bote de dialogue apparat. Cliquer sur Nouveau classeur.

2.

Enregistrer un classeur
Enregistrer un nouveau classeur :

Aprs avoir cre un nouveau classeur, vous devez donc lenregistrer pour pouvoir lutiliser nouveau. Pour cela :

c FSJES-Agadir 2013 Tableurs Semestre 3

2 Gestion dun classeur

13

Cliquer dans la barre doutils sur licne Enregistrer ou cliquer sur la commande Fichier | Enregistrer . Une bote de dialogue Enregistrer sous safche. Naviguer jusqu lemplacement de stockage souhait et saisir un nom pour le chier, qui possde en principe un nom par dfaut. Le tableur propose plusieurs formats de chiers pour lenregistrement de votre travail pour faciliter des transferts entre logiciels. Vous pouvez choisir un autre format que le format par dfaut, en cliquant sur la che droulante situe ct de Type et en slectionnant le format adquat dans la longue liste qui safche. Enregistrer un classeur existant : Lorsque vous travaillez sur un classeur existant, vous apportez des modications ce classeur. Pour que ces modications soient conserves, vous devez les enregistrer. La bote de dialogue Enregistrer sous napparat pas car ce classeur a dj un nom. Dans ce cas, seules les modications seront enregistres.

3.

Ouvrir un classeur existant

Pour ouvrir un classeur existant, utiliser lune des mthodes suivantes : Utiliser le bouton Ouvrir de la barre doutils Standard, Slectionner la commande Fichier | Ouvrir . Dans les deux cas, la bote de dialogue Ouvrir safche :

Slectionner le dossier contenant le document ouvrir,

c FSJES-Agadir 2013 Tableurs Semestre 3

2 Gestion dun classeur Cliquer sur le document voulu, Cliquer sur le bouton Ouvrir.

14

4.

Fermer un classeur

Pour fermer un classeur, utiliser lune des mthodes suivantes : Cliquer dans la case de fermeture situe dans la barre de titre de la feuille.

Slectionner la commande Fichier | Fermer .

5.

Renommer les feuilles du classeur

Par dfaut, les feuilles du classeur porte un nom provisoire. Pour leur donner un nom plus explicite, on choisi lune des mthodes suivantes : Double-cliquer sur longlet dont vous voulez changer le nom et Taper le nouveau nom.

Longlet de la feuille dExcel passe alors en mode modication comme le montre la photo gauche, cependant Calc afche une bote de dialogue. Dans les deux cas, il suft de saisir le nom de la feuille est de valider par la touche . Entree Faire un clic droit sur longlet de la feuille dont lon veut changer le nom. Dans le menu contextuel qui safche, slectionner Renommer .

c FSJES-Agadir 2013 Tableurs Semestre 3

2 Gestion dun classeur

15

Slectionner la commande Format | Feuille | Renommer et taper le nouveau nom.

6.

Supprimer les feuilles du classeur

Utiliser lune des mthodes suivantes : Faire un clic droit sur longlet de la feuille que lon veut supprimer. Dans le menu contextuel qui safche, slectionner Supprimer . Slectionner la commande Edition | Supprimer la feuille .

7.

Insrer les feuilles du classeur

Utiliser lune des mthodes suivantes : Faire un clic droit sur longlet dune feuille. Dans le menu contextuel qui safche, slectionner Insrer | Feuille . Slectionner la commande Insertion | Feuille

8.

Dplacer ou copier des feuilles dans un classeur

Utiliser lune des mthodes suivantes :

c FSJES-Agadir 2013 Tableurs Semestre 3

2 Gestion dun classeur

16

A laide de la souris, on fait un cliquer-glisser sur longlet de la feuille dplacer vers sa nouvelle position. Faire un clic droit sur longlet de la feuille que lon veut dplacer. Dans le menu contextuel qui safche, slectionner Dplacer ou copier , puis slectionner la nouvelle Position. Sil sagit deffectuer une copie de la feuille, on clique sur loption Crer une copie pour lactiver.

9.

Masquer / Afcher les feuilles du classeur

Cliquer sur longlet de la feuille que vous voulez Masquer puis slectionner Format | Feuille | Masquer . Slectionner Format | Feuille | Afcher . Dans la fentre qui souvre, choisir la feuille afcher.

c FSJES-Agadir 2013 Tableurs Semestre 3

3
1.

Saisir et modier des donnes

Barre de formule

La barre de formule permet de voir et de modier le contenu des cellules. Si on commence taper quelque chose dans une cellule, la barre de formule se meuble alors et fait : apparatre deux icnes : La croix pour annuler lentre et la coche pour la valider,

dupliquer le contenu de la cellule active et afcher son adresse. Par exemple, la gure suivante montre que la cellule active est B2, quelle contient le texte "Ce quon tape safche ici" et que son contenu nest pas encore valid!

Avec la barre de formule, on peut observer exactement ce que contient une cellule. Ce point est trs important, car on constate, par la suite, que le contenu rel dune cellule et ce quelle afche lcran peuvent tre diffrents.

2.

Saisir des donnes

Introduire des donnes dans une cellule sappelle saisir ou entrer des informations. Trois oprations sont ncessaires pour cela : 1. Slectionner la cellule choisie en cliquant dessus 2. Taper les donnes que lon veut. Ces donnes sont appeles lentre de la cellule. 3. Quand on a termin, on doit conrmer la saisie pour lenregistrer dans la cellule : valider lentre.
c FSJES-Agadir 2013 Tableurs Semestre 3

3 Saisir et modier des donnes Pour valider lentre, vous avez les possibilits suivantes : Cliquer dans une autre cellule Cliquer sur licne en forme de coche

18

de la barre de formule

Appuyer sur la touche . Dans ce cas lentre est valid et le pointeur se dplace Entree vers le bas. Appuyer sur lun des ches de direction. Dans ce cas lentre est valid et le pointeur se dplace sur la cellule voisine, selon la direction choisie. Appuyer sur + . Dans ce cas lentre est valid et le pointeur ne se dplace Ctrl Entree pas. Tant quon na pas valid une entre, on peut lannuler avec la touche Echap Esc de votre clavier ou bien en cliquant sur la croix de la barre de formules. En cas derreur pensez annuler en utilisant Saisir du texte et des chiffres Il y a trois types dinformations que vous pouvez entrer dans les cellules dune feuille de calcul : du texte, des chiffres et des formules. Les autres objets, tels que les graphiques et les dessins "ottent" au-dessus des cellules. Nous allons aujourdhui vous inviter saisir des informations dans certaines cellules. Pour cela, prenez une nouvelle feuille de calcul et slectionnez la cellule A1, si elle ne lest pas dj. Tapez, par exemple : Ventes de matriels informatique au cours de lanne 2013. Ne vous souciez pas encore de la mise en forme. ou rtablir en utilisant .

Ce texte, assez long, dborde de sa cellule et envahit les voisines de droite ; cest normale tant que ces dernires restent vides. Nanmoins, le texte est contenu dans sa totalit dans la cellule o il a t tap. Tapez ensuite les informations suivantes.

c FSJES-Agadir 2013 Tableurs Semestre 3

3 Saisir et modier des donnes

19

Le tableur reconnat ce quon tape. En effet, si cest du texte, il va laligner sur le bord gauche de la cellule. En revanche, sil sagit dun chiffre, il laligne sur le bord droit. Que faites-vous pour afcher ce qui suit ?

Surtout, ne tapez pas "4 000,00 dh" la place de "4000". En effet, il est inutile de saisir le texte "dh" et les zros aprs la virgule, ils feront lobjet dun traitement spcial appel Format . Saisir des dates et des heures La manipulation des dates et des heures reprsente une part importante des calculs effectus dans le tableur. Pour saisir une date, il faut saisir les barres de obliques " / " ou un tiret " - " entre le jour, le mois et lanne ; par exemple : 18/10/2013 ou 18-10-2013. Pour saisir une heure, il faut ajouter les deux points " : " entre les heures et les minutes ; par exemple : 19:30.

Les dates et les heures salignent aussi sur le bord droit de la cellule! En ralit, le contenu de la cellule est un nombre calcul par Excel. En effet, pour Excel une date est le nombre coul depuis 1/1/1900. Saisir des pourcentages Pour entrer un pourcentage, tapez le signe " % " juste aprs le nombre. Lorsque vous saisissez un nombre suivi dun signe de pourcentage " % ", le nombre est interprt comme un centime de sa valeur. Par exemple, 5 % est interprt comme 0,05.

c FSJES-Agadir 2013 Tableurs Semestre 3

3 Saisir et modier des donnes

20

Vous pouvez afcher un nombre sous forme de pourcentage. Slectionnez la cellule concerne et cliquez sur le bouton Style de pourcentage de la barre doutils. Le bouton Style de pourcentage multiplie automatiquement les valeurs par 100 et ajoute le caractre " % ". Ainsi, 0,05 devient 5 % mais 5 devient 500 %! Encore, le format Style de pourcentage limite lafchage en arrondissant le chiffre.

3.

Crer une formule simple

La feuille de calcul suivante est ouverte, nous vous proposons de calculer le contenu de la cellule D4.

De tte, la valeur porter dans D4 est 24 000. Vous pouvez donc imaginer des calculs plus complexes. Cest pourquoi la bonne manire consiste demander au tableur deffectuer le calcul pour vous. Tout lart consiste donc bien poser une formule de calcul : 4000*6 Cela peut se rcrire ainsi : (Le contenu de B4) * (Le contenu de C4) ou simplement B4 * C4

Cette fois, vous devez informer le tableur que vous voulez saisir une formule et non pas du texte ou des chiffres. Le code appropri est de mettre le signe gal " = " au dbut de la formule. Notre formule devient : = B4 * C4 Ne vous proccuperez pas des rfrences des cellules (B4 et C4), car vous pouvez les dsigner au tableur laide de la souris. Ainsi, vous vitez tout risque derreur. Ceci est dit, pour crer la formule placer en D4 respectez bien la dmarche suivante : 1. Slectionner la cellule D4
c FSJES-Agadir 2013 Tableurs Semestre 3

3 Saisir et modier des donnes 2. Insrez le signe = 3. Pointez la cellule B4 avec la souris et cliquez dessus 4. Tapez sur lastrisque *, sur le clavier 5. Pointez la cellule C4 avec la souris et cliquez dessus

21

La cellule cible D4 restant toujours active, la cellule pointe sentoure dun cadre et sa rfrence est automatiquement reporte la fois dans la barre de formule et dans la cellule cible. 6. Validez

Aprs la validation, vous devez constater un principe important : La cellule active afche le rsultat du calcul, cependant la barre de formule indique son contenu rel, ici une la formule de calcul.

4.

Copier la formule

Vous avez appris crer une formule de calcul et vous lavez insre dans D4. Maintenant, vous devrez calculer le contenu des cellules D5 et D6. On pourrait logiquement se dire quil faut recomposer la formule dans D5 et D6 : Dans D5, composer la formule : = B5 * C5
c FSJES-Agadir 2013 Tableurs Semestre 3

3 Saisir et modier des donnes Dans D6, composer la formule : = B6 * C6

22

Mais en ralit, il est bien plus efcace de recopier une formule que de la rcrire chaque fois quelle est ncessaire. En effet, vous voyez que notre formule est rptitive, seul le numro de ligne doit sadapter. Lun des grands avantages dun tableur est dtre capable de recopier une formule aussi souvent que ncessaire pour crer un modle. Ceci offre un gain de temps important lorsque une mme formule peut tre recopie un grand nombre de fois. Pour copier dans D5 et D6 la formule se trouvant dans D4. Utilisez la dmarche suivante : 1. Slectionnez la cellule D4. Elle sentoure dun cadre gras. Notez que cadre dispose dune case spciale (dans son coin infrieur droit) appele case ou poigne de copie. 2. Pointez la case de copie de D4, de sorte que le pointeur devienne un + 3. Appuyez sur le bouton de la souris et maintenez-le enfonc. 4. Tirez vers le bas de faon slectionner les cellules D5 et D6

5. Lchez le bouton de la souris. La copie seffectue instantanment et les cellules D5 et D6 afchent le rsultat des calculs

c FSJES-Agadir 2013 Tableurs Semestre 3

3 Saisir et modier des donnes Ne pas soccuper du symbole

23 qui est apparu en bas droite de la zon slectionne.

Quand on recopie une formule, le tableur modie lui-mme les rfrences des cellules : ainsi en recopiant vers le bas, la formule =B4*C4 devient =B5*C5 puis =B6*C6. On verra plus tard dans ce cours comment ce comportement du tableur peut-tre chang, mais oubliez cela pour linstant.

5.

Modier le contenu dune cellule

Vous pouvez modier directement le contenu dune cellule dans la cellule mme ou dans la barre de formule. Pour placer le contenu dune cellule en mode ddition, utiliser lune des mthodes suivantes : Double-cliquez sur la cellule contenant les donnes modier. Cliquez sur la cellule contenant les donnes modier, puis cliquez nimporte o dans la barre de formule. cliquez sur la cellule et appuyez sur la touche F2 du clavier. Cette opration place le point dinsertion la n du contenu de la cellule. Pour remplacer compltement le contenu de la cellule, il suft de retaper directement le nouveau contenu qui va se substituer lancien.

6.

Dplacements du curseur

Pour saisir ou modier une donne dans une cellule, vous devez avant tout slectionner la cellule dans la feuille de calcul. Pour aller de cellule en cellule, le pointeur de slection : sieurs manires dans un tableur. Se dplacer avec la souris On peut utiliser la souris, toujours plus intuitive. Il suft de cliquer sur la feuille de calcul et la cellule dont vous avez besoin. Si elle nest pas apparente, utiliser les barres de dlement pour la rechercher. Se dplacer au clavier Voici une liste des touches que vous pouvez utiliser pour vous dplacer. peut tre dplac de plu-

c FSJES-Agadir 2013 Tableurs Semestre 3

3 Saisir et modier des donnes

6  ?

24

Pour vous dplacer dune cellule vers la direction choisie :

 ou la droite - , vers le haut ou le bas ? vers la gauche . 6

Entre Tab

dplace le curseur dune cellule vers le bas. Dplace le curseur une cellule vers la droite.

Home

Maj + Tab PgUp PgDn

Dplace le curseur en dbut de ligne. Dplace le curseur une cellule vers la gauche Pour se dplacer dun cran vers le haut. Pour se dplacer dun cran vers le bas.

Alt + PgUp Alt + PgDn

Pour se dplacer dun cran vers la gauche. Pour se dplacer dun cran vers la droite. Pour se dplacer la feuille de calcul prcdente. Pour se dplacer la feuille de calcul suivante.

Ctrl + PgDn Ctrl + PgDn

Ctrl + Home

Pour revenir au coin suprieur gauche de la feuille de calcul : Retour en A1. Pour se dplacer la dernire cellule contenant du texte, un chiffre ou une formule. Pour se dplacer une cellule prcise du classeur, Si vous voulez par exemple la cellule BZ445. En appuyant sur la touche F5, La bote de dialogue apparat.

+ Ctrl End

F5

c FSJES-Agadir 2013 Tableurs Semestre 3

Selection et manipulation des cellules

Avant de passer une commande devant sappliquer un objet ou plusieurs dobjets, il faut le ou les slectionner.

1.

Slectionner une plage de cellule

La slection dun ensemble de cellules permet dappliquer des oprations (de mise en forme, par exemple) lensemble, et vite ainsi de faire et refaire les mmes manipulations pour chaque cellule. Avec la souris : Il y a deux manires. Pointer sur la premire cellule qui fera partie de la slection. Puis faire glisser la souris jusqu la n de votre slection, ou Pointer sur la premire cellule de la plage slectionner, maintenir la touche Maj ap puye et cliquer sur la dernire cellule de la plage.

Avec le clavier : Pointer sur la premire cellule de la plage, appuyez sur : lindicateur tendre la F8 slection (EXT) safche dans la barre des tches. laide des touches ches, dplacer le curseur jusqu la n de la plage slectionner.
c FSJES-Agadir 2013 Tableurs Semestre 3

4 Selection et manipulation des cellules

26

Pour annuler la slection, appuyer sur la touche , ou bien cliquer un endroit quelEsc conque sur la feuille ou encore se dplacer avec une des ches de direction du clavier.

2.

Slectionner des plages disjointes

Pour faire une slection multiple Slectionner la premire plage, maintenez appuye la touche , slectionnez la seconde Ctrl plage avec la souris, et ainsi de suite . . . lorsque la dernire plage a t slectionne relchez la touche . Ctrl

, puis appuyer sur Maj + Slectionner la premire plage avec la touche F8 F8 lindicateur devient Ajouter la slection (AJT), dplacer le curseur au dbut de la deux ime plage, appuyer sur , slectionner la plage avec les touches de direction, et ainsi F8 . de suite . . . lorsque la dernire plage a t slectionne appuyez sur F8

Les plages de cellules slectionnes sont noircies (sauf la premire cellule de la dernire plage slectionne qui reste blanche

3.

Slectionner une ligne ou une colonne

Pour slectionner une ligne ou une colonne, cliquer sur son numro dans les en-ttes de ligne ou colonne.

c FSJES-Agadir 2013 Tableurs Semestre 3

4 Selection et manipulation des cellules

27

4.

Slectionner plusieurs lignes ou colonnes

Cliquer sur le numro de la premire ligne ou de la premire colonne de la slection, puis faire glisser le pointeur sur les numros suivants. Slectionnez la premire colonne ou la premire ligne, appuyez sur Maj , slectionner les lignes ou colonnes voisines avec les touches de direction.

5.

Slectionner toute la feuille de calcul

Pour slectionner toutes les cellules dune feuille, cliquer sur le petit carr gris se situant lintersection des en-ttes de ligne et de colonne de la feuille de calcul.

La combinaison + est aussi trs pratique aussi pour slectionner tout le docuCtrl A ment.

6.

Insrer et supprimer des cellules

Lorsque que lon modie les donnes dune feuille de calcul, il est possible que : certaines donnes ne soient plus valides et on souhaiterait les supprimer ; la structure du tableau ne soit pas adapte pour en accueillir de nouvelles et, dans ce cas, on souhaiterait insrer de nouvelles cellules.
c FSJES-Agadir 2013 Tableurs Semestre 3

4 Selection et manipulation des cellules

28

Pour supprimer des cellules : Pour supprimer une cellule, il suft de 1. Slectionner les cellules concernes ; 2. Choisir la commande Edition | Supprimer . La bote de dialogue suivante apparat :

Choisir le bouton Dcaler les cellules vers la droite ou Dcaler les cellules vers le bas selon la situation puis cliquer sur le bouton OK. Cette bote de dialogue permet aussi la suppression de lignes et de colonnes contenant les cellules slectionnes. Pour insrer des cellules : Pour insrer une cellule, il suft de 1. Slectionner la ou les cellules droite ou au-dessous desquelles viendront se placer les nouvelles cellules ; 2. Choisir la commande Insertion | Insrer . La bote de dialogue suivante apparat :

Comme pour la suppression de cellule, choisir loption que lon veut. Le tableur insre autant de cellules quil y en a de slectionner, en choisissant le sens de dcalage des anciennes (vers la droite ou vers le bas). Vous pouvez galement cliquer avec le bouton droit sur les cellules slectionnes, puis cliquer sur Insrer ou Supprimer dans le menu contextuel. Cette bote de dialogue permet aussi linsertion dun nombre de lignes ou colonnes identique celui des lignes ou colonnes contenant les cellules slectionnes.
c FSJES-Agadir 2013 Tableurs Semestre 3

Mise en forme des cellules

Dans la plupart des cas, les feuilles de calcul des utilisateurs sont prsentes sans aucune mise en forme particulire : les options de mise en forme par dfaut sont sufsamment pertinentes pour quil ne soit pas ncessaire de sen proccuper. Pourtant, recourir aux nombreuses options proposes par les tableurs (polices de caractres, couleurs, alignements, prsentation des titres) permet de renforcer lintrt et la lisibilit des donnes. Ceci est dit, il faut bien distinguer la mise en forme des cellules (choix des polices, couleur, bordures, alignement etc.) de la mise en forme des donnes, qui dnit la faon dont sont afchs dates et nombres de la feuille de calcul.

1.

Mise en forme rapide

La plupart des modications de mise en forme seffectuent laide de la barre doutils Mise en forme. Son aspect peut varier selon les tableurs. Nous prsentons ici celle de Excel 2003.

Elle propose toutefois gnralement des boutons permettant de jouer sur les lments suivants : Police et taille des caractres Police : En venant cliquer sur le petit dvidoir vous afchez la liste des polices disponible. Il suft de cliquer sur le nom de la police qui vous convient. Taille de la police : En venant cliquer sur le petit dvidoir vous afchez la liste des tailles de la police disponibles. Plus le nombre choisi est grand, plus les caractres seront gros.
c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

30

Style de la police Gras : Ce bouton bascule appliquant ou supprimant la mise en gras pour la plage slectionne. Italique : Ce bouton bascule appliquant ou supprimant la mise en italique pour la plage slectionne. Soulign : Ce bouton bascule appliquant ou supprimant le style soulign pour la plage slectionne. Alignements Aligner gauche : Ce bouton aligne gauche le contenu des cellules slectionnes (valeur par dfaut pour le texte). Centrer : Ce bouton centre le contenu des cellules slectionnes. Surtout employ pour les titres et les tiquettes de colonne. Aligner droite : Ce bouton aligne droite le contenu des cellules slectionnes (valeur par dfaut pour les nombres). Fusionner et centrer : Ce bouton centre les donnes sur une cellule unique stendant sur plusieurs colonnes. Sert essentiellement pour la prsentation des titres. Mise en forme des nombres Montaire : Ce bouton applique le style monnaie par dfaut aux cellules slectionnes. Pour changer le style, utiliser la commande Format | Style . Style de pourcentage : Ce bouton applique le style pourcentage aux cellules slectionnes. Vous pouvez changer ce style avec la commande Format | Style . Sparateur de milliers : Ce bouton permet de sparer les milliers dans les cellules slectionnes. Vous pouvez changer ce style avec la commande Format | Style . Euro : Ce bouton permet de formater en euros les cellules slectionnes. Ajouter une dcimale : Ce bouton permet daugmenter le nombre de chiffres afchs aprs la virgule dans les cellules slectionnes. Rduire les dcimales : Ce bouton permet de rduire le nombre de chiffres afchs aprs la virgule dans les cellules slectionnes. Retraits Diminuer le retrait : Dcaler le contenu de la cellule vers la gauche lorsquun retrait a t appliqu.
c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules Augmenter le retrait : Dcaler le contenu de la cellule vers la droite. Bordures et couleurs Bordures : Ajoute une bordure la cellule ou la plage de cellules slectionne.

31

Pour appliquer la dernire bordure utilise, il suft de cliquer sur licne lui-mme. Pour utiliser une autre bordure, cliquez sur le petit dvidoir style de bordure dans la palette qui souvre. , puis slectionnez un

Couleur de remplissage : Permet de dnir la couleur du fond de lobjet slectionn, ou modie ou supprime la couleur. Couleur de police : Permet de mettre le texte slectionn avec dernire couleur utilise, ou de modier sa couleur.

A noter que lorsquune cellule est slectionne, la barre doutils format met alors en surbrillance les attributs actifs (police, taille, type police, alignements,. . . ).

2.

Mise en forme labore

Vous pouvez disposer de beaucoup plus doptions en slectionnant une plage, puis en ouvrant la fentre Format de cellule (Excel) ou Formatage des cellules (Calc). Pour cela, il y a deux possibilits : Faire un clic droit et dans le menu contextuel, cliquez sur Formater les cellules (Calc) Format de cellule (Excel) ou

Cliquer sur la commande Format | Cellule. . . (Excel) ou Format | Cellules. . . (Calc) Nous prsentons ici la fentre Format de cellule de Excel 2003.

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

32

La bote de dialogue Format de cellule qui apparat permet de dnir aussi bien le format des donnes (comme nous lavons dit prcdemment) que celui des cellules, grce diffrents onglets. Cette boite de dialogue comprend 6 onglets : 2.1. Appliquer un format de nombre

Longlet Nombre permet dajuster le format de nombres. Par exemple, on peut slectionner le nombre de dcimales que les nombres doivent avoir. Dautres possibilits sont offertes allant des formats usuels comme les formats standards, montaire, scientique ou encore dates jusquaux formats plus spciaux comme les codes postaux. Il est aussi possible de dnir des formats personnaliss.

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules Pour modier le format de nombre : 1. Slectionnez la ou les cellules formater (mettre en forme) 2. Choisissez longlet Nombre dans la bote de dialogue Format de cellule Excel propose plusieurs formats qui sont classs en catgories.

33

3. Dans la zone Catgorie, slectionnez le format voulu (Nombre, Montaire, Date, heure, Pourcentage...) Chaque catgorie a ses propres options dafchage : nombre de dcimales ou type dafchage, etc. 4. Rglez, si ncessaire, les diffrents paramtres du format, puis validez par OK Lapplication dun format de nombre ne modie pas la valeur relle contenue dans la cellule mais uniquement la faon dont elle apparat. Ainsi, un nombre saisi avec des dcimales peut tre afch avec plus ou moins voire pas de dcimales, mais tout calcul sur la cellule contenant ce nombre se fera avec ses dcimales. 2.2. Alignement des donnes

Par dfaut Excel aligne (horizontalement) le texte sur le bord gauche de la cellule et les nombres sur le bord droit. Pour un afchage optimale des donnes sur votre feuille de calcul, vous pouvez ventuellement repositionner les donnes dans les cellules. Outre lalignement horizontal, qui peut dailleurs sajuster directement depuis la barre doutils, longlet Alignement permet aussi de rgler lalignement vertical, de changer lorientation du texte et de choisir dautres options dalignement. Pour modier lalignement des donnes dans la cellule : 1. Slectionnez la ou les cellules mettre en forme 2. Choisissez longlet Alignement dans la bote de dialogue Format de cellule 3. Slectionnez les options dsires et validez par OK Si le contenu de la cellule est plus large que la colonne, vous pouvez slectionner la case cocher Renvoyer la ligne automatiquement. Ainsi, le contenu sera afch sur plusieurs lignes. La case cocher Ajuster permet dajuster automatiquement le contenu de la cellule.

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

34

2.3.

Modier la police ou sa taille

Vous pouvez changer la police ou la taille de la police de cellules ou plages slectionnes dans une feuille de calcul. Vous pouvez aussi modier la police ou la taille de police utilise par dfaut dans les nouveaux classeurs. Pour mettre en forme une cellule ou une plage de cellule: 1. Slectionnez la ou les cellules mettre en forme 2. Ouvrez la bote de dialogue Format de cellule 3. Slectionnez longlet Police

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules 4. Choisissez la police et la taille dsires 5. Choisissez les attributs de mise en forme (Style, Couleur, soulign..), puis 6. Validez par OK. 2.4. Appliquer des bordures et des motifs

35

Pour appliquer ou modier les formats de bordures et de motifs, il faut : 1. Slectionner les cellules mettre en forme ; 2. Ouvrir la bote de dialogue Format de cellule

3. Cliquer sur longlet Bordure et construire la bordure dsire. On distingue les bordures extrieures (contour) et les bordures internes (Intrieur) auxquelles peuvent tre associes des lignes avec des styles et des couleurs diffrents. Trois boutons de prslections permettent :

dter toutes les bordures ; de mettre une bordure sur lensemble du contour du tableau ; de mettre toutes les bordures intrieures.
c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

36

4. Choisir longlet Motifs. Il est alors possible de dnir une couleur de fond et un motif avec sa couleur :

5. Valider puis cliquer en dehors de la slection. 2.5. Protection

La protection des cellules sutilise avec la commande Outils | Protection | Protger la feuille . Lorsque vous protgez la feuille, toutes les cellules dont la case verrouille est coche ne pourront tre modies. Si vous dsirer laissez une ou plusieurs cellules non protgs vous devez dcocher la case verrouille correspondante en utilisant longlet Protection de la bote de dialogue Format de cellule avant de protger la feuille.

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

37

Longlet Protection permet aussi de masquer ou de verrouiller les formules associes aux cellules.

3.

Mise en forme de caractres individuels

Gnralement, une mise en forme porte sur des cellules entires. Il est toutefois possible de mettre en forme sparment des caractres, mots, nombres, phrases, etc., au sein dune cellule. Il suft pour cela de slectionner les caractres, et dappliquer le format souhait.

Si vous augmentez la taille de caractres individuels, le tableur ajuste gnralement la hauteur de la ligne en consquence, comme si vous aviez appliqu cette modication toute la cellule.

4.

Ajuster la largeur des colonnes et la hauteur des lignes

Il y a plusieurs faons de redimensionner les lignes et les colonnes. 4.1. Ajuster la largeur des colonnes

Pour agrandir ou diminuer la largeur dune colonne ou de plusieurs colonnes, on peut :

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

38

Avec une bote de dialogue Cliquez sur une cellule de la colonne modier ou slectionnez des cellules de plusieurs colonnes. Cliquez sur la commande Format | Colonne | Largueur La bote de dialogue suivante apparat

Entrez la nouvelle largeur, puis validez par OK. Avec la souris On peut aussi diminuer ou agrandir une colonne directement avec la souris. Pour cela, il faut : 1. Pointer sur le trait se trouvant droite de la lettre de la colonne. Le pointeur change de forme en double che .

2. Faire glisser pour dplacer la branche de la colonne jusqu la taille dsire.

Pour diminuer ou agrandir plusieurs colonnes avec la souris : 1. Slectionnez les colonnes concernes. 2. Pointez sur le trait se trouvant droite dun groupe de colonnes. Le curseur se transforme en 3. Faire glisser pour dplacer la branche de la colonne se trouvant gauche du trait jusqu la taille dsire.
c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

39

Pour redimensionner la longueur de la plus longue entre dune colonne : 1. Pointer sur le trait se trouvant droite de la lettre de la colonne voulue an de faire ; apparatre le curseur form de la che deux directions 2. Double-cliquer. La colonne prend alors exactement lespace occup par lentre la plus longue. On peut aussi utiliser la commande Format | Colonne | Ajustement automatique . Le message ##### peut aussi apparatre dans une cellule lorsque sa taille nest pas sufsante pour afcher son contenu. Il convient alors de redimensionner la cellule pour voir apparatre la valeur. 4.2. Masquer ou afcher des colonnes

Pour masquer une ou plusieurs colonnes : 1. Cliquez sur une cellule de la colonne masquer ou slectionnez des cellules de plusieurs colonnes . 2. Cliquez sur la commande Format | Colonne | Masquer Pour afcher une ou plusieurs colonnes dj masques : 1. Slectionnez les deux colonnes encadrant la colonne afcher ou les colonnes encadrant les colonnes afcher. 2. Cliquez sur la commande Format | Colonne | Afcher

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules 4.3. Ajuster la hauteur des lignes

40

Pour agrandir ou diminuer la hauteur dune ligne ou de plusieurs lignes, on peut : Avec une bote de dialogue Cliquez sur une cellule de la ligne modier ou slectionnez des cellules de plusieurs lignes. Cliquez sur la commande Format | Ligne | Hauteur La bote de dialogue suivante apparat

Entrez la nouvelle hauteur, puis validez par OK. Avec la souris On peut aussi diminuer ou agrandir une lignes directement avec la souris. Pour cela, il faut : 1. Pointer sur le trait se trouvant au dessous du numro de la ligne. Le pointeur change de forme en double che .

2. Faire glisser pour dplacer la branche de la ligne jusqu la taille dsire.

Pour diminuer ou agrandir plusieurs lignes avec la souris : 1. Slectionnez les lignes concernes. 2. Pointez sur le trait se trouvant au dessous dun groupe de lignes. Le curseur se transforme en
c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

41

3. Faire glisser pour dplacer la branche de la ligne se trouvant au dessous du trait jusqu la taille dsire.

Pour redimensionner la hauteur de la plus grande entre dune ligne : 1. Pointer sur le trait se trouvant au dessous du numro de la ligne voulue an de faire apparatre le curseur sous la forme ; 2. Double-cliquer. La ligne prend alors exactement lespace occup par lentre la plus haute. On peut aussi utiliser la commande Format | Ligne | Ajustement automatique . 4.4. Masquer ou afcher des lignes

Pour masquer une ou plusieurs lignes : 1. Cliquez sur une cellule de la ligne masquer ou slectionnez des cellules de plusieurs lignes. 2. Cliquez sur la commande Format | Ligne | Masquer Pour afcher une ou plusieurs lignes dj masques : 1. Slectionnez les deux lignes encadrant la ligne afcher ou les lignes encadrant les lignes afcher. 2. Cliquez sur la commande Format | Ligne | Afcher

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules

42

5.

Mise en forme conditionnellle

La mise en forme conditionnelle permet dattribuer automatiquement un format spcique une cellule (motif, police . . . ) en fonction de son contenu (numrique, chane de caractres ou logique (vrai/faux)). La mise en forme conditionnelle est donc un outil de mise en relief des donnes, utile dans tous les domaines. 5.1. Attribuer une mise en forme conditionnelle

Pour attribuer une mise en forme conditionnelle une plage de cellules 1. Slectionner la cellule ou les cellules pour lesquelles vous souhaitez ajouter une mise en forme conditionnelle. 2. Cliquez sur la commande Format | Mise en forme conditionnelle . La bote de dialogue qui apparat vous permet de paramtrer les conditions en fonction de votre projet.

3. Indiquez les critres de mise en forme puis cliquez sur le bouton Format 4. Choisissez le format applicable aux cellules rpondant au critre indiqu :

c FSJES-Agadir 2013 Tableurs Semestre 3

5 Mise en forme des cellules 5.2. Copier une mise en forme conditionnelle

43

Pour copier les mises en forme dans dautres cellules : 1. Slectionnez les cellules qui utilisent les mises en forme conditionnelles que vous souhaitez copier. 2. cliquez sur licne Reproduire la mise en forme de la barre doutils Mise en forme.

3. Slectionnez les cellules que vous souhaitez mettre en forme. 5.3. Modier ou supprimer une mise en forme conditionnelle

Pour changer des mises en forme, lorsque la bote de dialogue est ouverte, cliquez sur le bouton [Format. . . ] correspondant la condition que vous souhaitez modier. Pour supprimer une ou plusieurs conditions, cliquez le bouton [Supprimer. . . ] lorsque la bote de dialogue est ouverte et cochez les conditions que vous ne souhaitez plus.

c FSJES-Agadir 2013 Tableurs Semestre 3

6
1.

Crer un graphique

Introduction

En plus des tableaux, les tableurs proposent un second moyen de reprsenter les donnes de manire synthtique : les graphiques. Les graphiques peuvent tre incorpors dans une feuille de travail (avec le tableau associ) ou indpendants dans leur propre feuille (feuille graphique). Un graphique est li la feuille de calcul dont ses donnes sont issues. Si la feuille de calcul est modie, le graphique sera automatiquement mis jour. Pour crer un graphique, il faut dabord avoir des donnes dans une feuille de calcul. Le long ce chapitre, nous allons considrer lexemple dune socit qui veut, laide dun graphique, analyser et comparer les ventes de ses vendeurs.

Le tableur vous permet de construire des graphiques de toutes sortes, automatiquement ou en vous vous faisant aider par un Assistant, bien que seulement quelques uns soient utilisables en pratique. Avant de vous lancer dans la cration, rchissez choisir le bon graphique selon la nature de vos donnes et le message faire passer.
c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique

45

2.

Graphique express

Pour crer rapidement un graphique de base est trs simple : Dabord, slectionnez les donnes qui devront apparatre dans votre graphique, y compris les titres (tiquettes) de lignes et de colonnes. Ensuite, appuyez sur + ou . Alt F1 F11 Pour notre exemple, retenez la plage A4:E9 en excluant les totaux.

Excel cre instantanment un graphique barres verticales (histogramme), en appliquant les paramtres par dfaut.

Notez que le graphique par dfaut (histogramme simple) est cr sur une nouvelle feuille graphique nomme, par dfaut, Graph1. Ce graphique est souvent sufsant en un premier temps. Nanmoins, vous pourrez toujours modier, dvelopper et amliorer ce graphique par la suite.

c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique

46

3.

LAssistant graphique

Le principe de lAssistant est simple. Vous passez dans 4 botes de dialogues qui reprsentent les tapes de cration du graphique. Au bout de la 4me tape, votre graphique sera cr sur base des paramtres que vous aurez dnis. A chaque tape, vous avez la possibilit de continuer, de reculer ou de quitter lAssistant. Pour cela, utilisez les boutons [Prcdent], [Suivant] ou [Annuler] situs dans le bas de la bote. Pour crer votre graphique : 1 Slectionnez les donnes (chiffres) qui devront apparatre dans votre graphique. Il convient de leur associer les titres (tiquettes) de lignes et des colonnes que vous souhaitez retrouver dans le graphique. Cliquez sur licne dans la barre doutils Standard. Suivez les diffrentes tapes de cration. Premire tape : Slectionner le type de graphique

2 3 3.1.

Cette bote, compose de deux onglets, vous propose de dnir le type de graphique que vous souhaitez crer. Le premier onglet reprend les types les plus frquemment utiliss. 1 Cliquez sur le type de graphique dans la zone de gauche de la bote de dialogue.

Chaque catgorie de graphique est compose de sous-types de graphiques. Choisissez celui qui vous convient en cliquant sur le modle propos dans la zone de droite de la bote de dialogue.

c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique

47

Chaque fois que vous cliquez sur un type de graphique, Excel vous en afche un bref descriptif dans la bote de dialogue. Le bouton [Maintenir appuy pour visionner] offre un aperu du graphique choisi en fonction des donnes qui sont slectionnes. Cela vous permet de vous faire une ide de la forme gnrale de votre futur graphique. 3 3.2. Passez ltape suivante en cliquant sur le bouton [Suivant]. Deuxime tape : Conrmer les donnes

Dans cette tape de la cration du graphique, il y a deux onglets : lun est pour dterminer la plage de donnes et lautre est pour donner des informations sur les sries. 1 Longlet Plage de donnes est pour sassurer que vous avez choisi la bonne plage de cellules pour votre graphique. Sil y a une erreur, vous pouvez la modier en cliquant sur le bouton an de minimiser bote de dialogue et slectionnez directement dans la feuille de donnes.

Vous dterminez aussi si les sries de donnes sont en colonnes ou en lignes. 2 Sous longlet srie, il est possible de changer, dajouter de supprimer ou de renommer des sries de donnes. La case " tiquette de laxe des abscisses (X) " indique la description qui sera afche sur laxe des " X " du graphique.
c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique

48

Cliquez sur le bouton [Suivant] pour passez ltape troisime tape. Chaque bote de dialogue possde une zone afchant le graphique. Au fur et mesure de la cration, vous voyez lvolution de votre graphique en fonction des caractristiques que vous avez choisies.

3.3.

Troisime tape : Choisir les options

Les six onglets de cette bote de dialogue, vous dnissent les caractristiques des diffrents lments qui constituent votre graphique.

c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique

49

Onglet Titres : Vous pouvez ajouter un titre votre graphique et/ou mettre un titre pour les diffrents axes. Onglet Quadrillage : Permet dafcher ou de cacher le quadrillage du fond du graphique. Pour chaque unit de mesure, il est possible de faire apparatre un trait pour pouvoir situer les donnes plus facilement. De plus, vous pouvez ajouter des units intermdiaires. Onglet Table de donnes : Permet dafcher ou non la table de donnes. Longlet Axes : Vous donne le choix dafcher ou de cacher les informations des diffrents axes du graphique. Par dfaut, les axes seront afchs. Onglet Lgende : vous permet de choisir dafcher ou non la lgende du graphique. Si vous lafchez, vous pouvez choisir son emplacement (en haut du graphique, en bas, gauche, droite). Onglet tiquettes de donnes : Permet dafcher des tiquettes chacun des lments de la srie. Vous pouvez afcher la valeur, le pourcentage, les deux ou mme la description de laxe des "X". Cliquez sur le bouton [Suivant]. 3.4. Dernire tape: Spcier lemplacement

Votre graphique est maintenant cr. Il vous reste cependant dnir lendroit o Excel devra linsrer. Deux possibilits soffrent vous : Soit vous dcidez de crer une nouvelle feuille qui sera entirement ddie votre graphique. Dans ce cas, Excel insre une feuille de graphique dont vous devez dnir le nom dans la zone prvue cet effet. Soit vous insrez votre graphique comme objet dans la feuille de calcul existante ou dans une autre feuille de calcul que vous choisissez en cliquant sur la che du champ.

Dans lexemple, le graphique sera insr dans la feuille appele Resultats ventes. Terminez votre graphique en cliquant sur le bouton [Terminer].
c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique Voil ! Vous avez un graphique ottant, cest--dire dont la position peut changer.

50

4.

Redimensionnement et repositionnement du graphique

Le graphique a t incorpor comme graphique ottant dans la feuille. Ainsi, vous pouvez le redimensionner et le repositionner par rapport la feuille de calcul. Utilisez les poignes de redimensionnement de la zone graphique pour en augmenter la taille jusqu masquer la zone de la feuille de calcul stendant de B2 H15. Eventuellement, repositionnez-la en glissant la souris sur le graphique. Vous devriez arriver ce rsultat :

c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique

51

5.

Modier un graphique

La prsentation par dfaut nest pas forcment celle qui vous convient. Vous pouvez donc lajuster vos besoins ou souhaits. On peut prsent modier tous les lments du graphique obtenu. Pour cela, il existe plusieurs possibilits : 1 Utiliser la barre doutils graphique, si elle nest pas prsente lcran, vous la trouverez dans le sous-menu barres doutils du menu Afchage.

La che noire situe droite de la fentre o sinscrit "zone de graphique" vous permet de slectionner la partie du graphique que vous voulez modier.

Il suft ensuite de cliquer sur licne reprsentant une main tenant une feuille pour avoir accs cette partie prcise du graphique et dy apporter toutes les modications ncessaires. 2 Faire un clic droit sur la partie du graphique que lon veut modier. Le menu contextuel, qui apparat, vous donne accs au format de la zone que vous souhaitez modier. Par exemple ici, laccs au format de la zone de traage, la zone grise du graphique.

c FSJES-Agadir 2013 Tableurs Semestre 3

6 Crer un graphique 3

52

Cliquer deux fois sur la partie du graphique que lon souhaite modier et entrer directement dans la fentre Format correspondant cette zone. Chacune de ces trois options donnent des rsultats quivalents. Vous pouvez donc choisir celle qui vous conviendra le mieux pour modier laspect de vos graphiques.

On peut galement retourner chacune des quatre tapes de la ralisation du graphique si on saperoit que lon a oubli quelque chose. Pour cela, il suft de slectionner le graphique et de cliquer sur Graphique dans la barre de menus. Il apparat un menu droulant dans lequel on trouve les quatre tapes de ralisation du graphique, il suft de cliquer sur celle o on souhaite apporter des modications.

c FSJES-Agadir 2013 Tableurs Semestre 3

Le calcul avec un tableur

Un tableur tel quExcel nest pas un logiciel que lon utilise uniquement pour organiser des informations. Le calcul est la fonction primordiale dun tableur pour lequel tout calcul est li une cellule.

1.

Formules

Aprs avoir ajout des donnes dans une feuille de calcul, il est possible de crer une formule, une expression qui effectue des calculs sur les donnes.

2.

Saisie dune formule

La structure dune formule obit une syntaxe particulire. La formule dbute par le signe gal "=" suivi des lments calculer : des oprandes spars par des oprateurs de calcul. En gnral, voici les tapes suivre pour saisir une formule : 1. Slectionnez la cellule avant de taper la formule 2. Tapez le signe gal = 3. Tapez la formule : oprandes et des oprateurs. Chaque oprande peut tre une constante, une cellule ou une plage de cellules, un nom ou une fonction de calcul. 4. Validez Structure dune formule type :

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur

54

Lorsque vous validez la saisie dune formule, le tableur calcule cette dernire et il afche immdiatement la valeur du rsultat dans la cellule. Mme si la formule semble disparatre, le tableur retient la formule. Pour voir la formule, slectionnez la cellule, puis regardez dans la barre de formule.

Application : On va crer la formule =A1+B1-B2 dans la cellule D1. Pour cela suivez les instructions suivantes : 1. Slectionner la cellule D1 2. Tapez le signe =, puis pointez sur la cellule A1 3. Tapez le signe +, puis pointez sur la cellule B1 4. Tapez le signe -, puis pointez sur la cellule B2 5. Validez par la combinaison + , pour valider sans se dplacer! Ctrl Entre

2.1.

Modier une formule

La saisie de formule se rvle dune grande simplicit. Mais personne nest labri dune erreur. Cest pourquoi, il est important de pouvoir modier une formule saisie. Pour cela, on dispose des des outils habituels de modication dun texte : Slectionnez la cellule contenant la formule modier (La formule apparat dans la barre de formule). Puis, utilisez lune des deux mthodes :

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur

55

Cliquez dans la barre de formule pour y placer le point dinsertion " | " et effectuez les modications.

Faites un double clic ou utiliser la touche , pour modier la formule directement dans F2 la cellule.

Notez que, dans le premier cas, le point dinsertion " | " est dans la barre de formule. Cependant, dans le deuxime cas, le point dinsertion " | " est dans la cellule contenant la formule. Par dfaut, un tableur recalcule automatiquement la formule chaque fois que vous modiez son expression. 2.2. Erreurs dans les formules

En cas derreur dans la composition dune formule, la cellule qui la contient afchera lun des messages suivants : ##### #DIV/0! #NOM? #N/A! #NUL! La cellule nest pas assez large pour afcher le nombre dans le format choisi. Division par zro. La formule utilise un nom inexistant ou il y a une erreur dans le nom dune fonction. La formule fait rfrence une valeur non disponible. Rfrence invalide (hors feuille par exemple). Rfrence quelque chose qui nexiste pas ou qui a t effac. Arguments incompatibles dans une fonction.

#NOMBRE! Argument invalide dans une fonction. #REF! #VALEUR!

Lorsquune cellule avec une formule errone est active, un bouton derreur apparat . Si vous cliquez sur la che droulante de ce bouton , un menu safche avec des options qui fournissent des informations sur lerreur et proposent de laide pour y remdier.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur

56

2.3.

La recopie incrmente

En rgle gnrale, dans la construction de formules, on se contente de rdiger la formule de la premire ligne (ou colonne). Pour reporter la formule sur les autres lignes (ou colonnes), le tableur propose un outil qui se nomme la recopie incrmente.

En approchant la souris vers le bord infrieur droit de la cellule contenant la premire formule, le curseur prend lapparence dun +. En maintenant le clic gauche de la souris et en tirant vers le bas (ou la droite), la formule de la premire ligne (ou colonne) va se dupliquer vers les autres lignes (ou colonnes).

3.

Oprateurs

Les oprateurs permettent de dnir le type de calcul que vous voulez effectuer sur les lments dune formule. Il existe un ordre de calcul par dfaut. Toutefois, vous pouvez le changer laide de parenthses. 3.1. Types doprateurs

Il existe quatre types doprateurs de calcul diffrents : arithmtiques, de comparaison, de concatnation de texte et de rfrence.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur Oprateurs arithmtiques

57

Pour effectuer les oprations mathmatiques de base telles que laddition, la soustraction ou la multiplication, combiner des nombres et produire des rsultats numriques, utilisez les oprateurs arithmtiques ci-dessous. Type doprateur Oprateur Signication Addition Soustraction Multiplication Division Pourcentage Puissance Exemple 2+6, A1+B1 2-6, A1-B1, -3 2*6, A1*B1 2/6, A1/B1 2% 26, A1B1

Arithmtique

* / %

Oprateurs de comparaison Vous pouvez comparer deux valeurs avec les oprateurs ci-dessous. Le rsultat obtenu est une valeur logique VRAI ou FAUX. Type doprateur Oprateur Signication Egalit Infrieur Suprieur Infrieur ou gal Suprieur ou gal Diffrent de A1=B1 A1<B1 A1>B1 A1<=B1 A1>=B1 A1<>B1 Exemple

= <
Comparaison

>
<= >= <>

Oprateurs de rfrence Combinez les plages de cellules pour effectuer des calculs laide des oprateurs suivants : Type doprateur Rfrence Oprateur Signication Plage Union Intersection C2:E5 C3:D7;F2:G6 =C2:E5 D4:F7 Exemple

: ;
espace

Oprateurs de texte Utilisez le signe "&" (et commercial) pour combiner (concatner) une ou plusieurs chanes de texte en vue dobtenir un seul lment de texte.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur Type doprateur Texte Oprateur Signication Concatnation de texte Exemple "Bon"&"homme"

58

&

Application : Si vous avez besoin de mettre bout bout la valeur de 2 cellules dans une troisime il faut utiliser la concatnation.

Pour avoir dans la cellule B1 la valeur 08, il faut faire prcder la valeur par le signe "apostrophe" : Dans B1, tapez 08. Le rsultat donne bien 2008.

Si nous voulons avoir la phrase "Bonjour Madame" dans la cellule C1 avec un espace entre les deux mots, il faut crire la formule =A1&" "&B1

3.2.

Ordre de calcul

Dans certains cas, lordre des calculs peut affecter la valeur renvoye par la formule. Les oprateurs possdent un ordre de priorit que vous pouvez modier laide de parenthses. Le tableau suivant prsente la priorit des oprateurs par ordre dcroissant :

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur No. dordre 1 2 3 4 5 6 7 8 9 Oprateurs Description Parenthses Oprateurs de rfrence Ngation (comme dans -1) Pourcentage Exposant Multiplication et division Addition et soustraction Concatnation de deux chanes Oprateurs de comparaison

59

() : ; (espace) % * et / + et & = < > <= >= <>

Si la formule contient des oprateurs de mme priorit comme la multiplication et la division, Le tableur excute les oprations de gauche droite. Application : Comprendre lordre des calculs en prsence de plusieurs oprateurs

La formule =24/3*5 produit le rsultat 40. En effet, le tableur commence par diviser 24 par 3, puis il multiplie par 5. La formule =5*2+9/3 produit le rsultat 13. En effet, le tableur commence par multiplier 5 et 2 ce qui donne 10, puis il divise 9 par 5 ce qui donne 3, enn, il fait laddition de 10 et 3.

4.

Rfrences des valeurs

Les rfrences sont des adresses permettant didentier les cellules. Les tableurs utilisent des rfrences relatives, absolues et mixtes. La force des tableurs tient leur gestion des rfrences des valeurs. Quand vous effectuez des calcul avec un tableur, vous manipulez en gnral les adresses des valeurs et non les valeurs elles-mmes. 4.1. Rfrences relatives

Les rfrences relatives se rfrent aux cellules daprs leur position par rapport la cellule contenant la formule. Par dfaut, les rfrences sont relatives.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur

60

Par exemple, si vous crivez, dans la cellule B2, la formule =A2+B1, le tableur lit la formule sous la forme (le contenu de la cellule situe gauche de cette cellule) + (le contenu de la cellule situe au-dessus de cette cellule)

Application : Soit un tableau comprenant une liste de produits, leurs prix unitaires et leurs quantits. Le but est de calculer les totaux.

Si vous crivez, dans la cellule E5, la formule =C5*D5, vous demandez alors au tableur de lire la valeur stocke deux cellules gauche de E5 et de la multiplier avec la valeur stocke une cellule gauche de E5. Lorsque vous copiez la formule de E5 vers le bas, vous constatez que le tableur adapte automatiquement les rfrences contenues dans la formule colle, en fonction de chaque cellule. Par exemple la cellule E9 contient maintenant la formule =C9*D9.

En ralit, la formule que le tableur a enregistre (et qui napparatra nulle part ressemble davantage ce qui suit : = (le contenu de la cellule situe deux cellules gauche) (le contenu de la cellule situe une cellule gauche)
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 4.2. Rfrences absolues

61

Les rfrences absolues se rfrent aux cellules daprs leur position xe dans la feuille de calcul. Si on ne veut pas que le tableur ajuste les rfrences lorsquon copie une formule dans une autre cellule, il faut utiliser une rfrence absolue. Pour obtenir une rfrence absolue, il suft dajouter le signe $ dans la rfrence. Par exemple, si vous crivez, dans la cellule B2, la formule =A2+$B$1, le tableur lit la formule sous la forme (le contenu de la cellule situe gauche de cette cellule) + (le contenu de la cellule situe lintersection de la colonne B et de la ligne 1) . Ainsi en recopiant la cellule, les rfrences de B1 ne seront pas incrmentes.

Pour mettre ce $, vous pouvez lajouter vous-mme en le tapant ou utiliser la touche , F4 comme dans le tableau suivant, avec lexemple A1 : Nombre dappuie sur la touche F4 1 2 3 4 Rsultat $A$1 A$1 $A1 A1 Aprs recopie vers la droite $A$1 B$1 $A1 B1 Aprs recopie vers le bas $A$1 A$1 $A2 A2

Application : Soit un tableau comprenant une liste de produits, leurs prix unitaires, leurs quantits et les totaux. Le but est de calculer la TVA.

Si vous crivez, dans la cellule F5, la formule =E5*$C$2, vous demandez alors au tableur de lire la valeur stocke une cellule gauche de F5 et de la multiplier avec la valeur stocke la cellule situe lintersection de la colonne C et de la ligne 2.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur

62

Lorsque vous copiez la formule de F5 vers le bas, vous constatez que le tableur a bien conserv la rfrence la cellule C2. Cependant, il a bien sr adapt les rfrences relatives contenues dans la formule colle. Par exemple la cellule F9 contient maintenant la formule =E9*$C$2.

4.3.

Rfrences nominales

Cest un type de rfrence qui est en fait une rfrence absolue. Par exemple, au lieu de dsigner la cellule par son nom gnrique ($C$2), on lui donne un nom particulier (souvent en rapport avec son contenu). Pour donner un nom une cellule ou une place de cellules, il faut la slectionner, puis cliquer sur la zone nom dans la partie gauche de la barre de formule. Tapez le nom que vous voulez donner votre cellule ou plage et validez par la touche . Entre Application : Soit un tableau comprenant une liste de produits, leurs prix unitaires, leurs quantits et les totaux. Le but est de calculer la TVA. Comme C2 contient le taux de TVA, il est tout fait possible de nommer cette cellule TauxTva.

La formule devient beaucoup plus lisible, si on remplacer =E5*$C$2 par =E5*TauxTva.

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 4.4. Rfrences mixtes

63

Une rfrence mixte contient une rfrence relative et lautre absolue. Elle est signale en plaant le signe $ devant la rfrence absolue : $B1 signie que la colonne B sera toujours utilise mais que la ligne variera selon lemplacement de la formule que vous avez copie. Par exemple, si vous crivez dans la cellule B2, la formule =A2+$B1, le tableur lit la formule sous la forme (le contenu de la cellule situe gauche de cette cellule) + (le contenu de la cellule situe situe dans la colonne B et une ligne au-dessus de cette cellule)

Application : Nous plaons une somme dargent sur un livret, intrts simples. Le but est de connatre le montant (capital et intrts cumuls) quon peut retirer la n dun certain nombre de priodes. Pour connatre le montant disponible la n de la nime priode, nous devons avoir : Montant + Montant *(Nombre de priode)*(Taux dintrt) Nous devons rentrer, dans la cellule C6, la formule suivante =C2+C2*B6*C5 Cependant, pour recopier la formule vers le bas et vers la droite, il faut signaler au tableur que nous souhaitons : que les rfrences de la cellule C2 ne changent pas lors de la copie. Pour cela, il faut utiliser la rfrence absolue $C$2 ou le nom de la cellule, ici Montant. que le numro de la ligne de la cellule B6 sadaptera la nouvelle position de la formule, mais pas la lettre de la colonne. Pour cela, il faut bloquer la lettre B : $B6. que la lettre de la colonne de la cellule C5 sadaptera la nouvelle position de la formule, mais pas la le numro de la ligne. Pour cela, il faut bloquer le numro 5 : C$5. La formule mettre dans C6, scrit : =$C$2+$C$2*$B6*C$5

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur

64

Puisque la cellule C2 contient le montant plac, il est possible de nommer cette cellule Montant. Dans ce cas la formule peut scrire =Montant + Montant*$B6*C$5

5.

Fonctions

Une fonction est une formule prdnie vous permettant dexcuter des calculs plus ou moins complexes. Mais, le plus grand avantage des fonctions est quelles sont prtes tre utilises immdiatement. La syntaxe dune fonction est toujours

NomDeLaFonction argument1 ; argument2; . . .; argumentN

Les diffrents lments dune fonction : Le nom : Chaque fonction possde un nom. Ce nom peut tre crit en majuscules ou minuscules

Les parenthses : Le nom dune fonction est toujours suivi par une parenthse ouvrante qui le spare des arguments. De mme, toutes les fonctions doivent se terminer par une parenthse fermente. Les arguments : Ce sont les diffrents paramtres attendus par la fonction. Ils sont complter et sparer par des points-virgules (;). Il peut ny avoir quun seul ou aucun argument. Les arguments peuvent tre des nombres, du texte et des valeurs logiques telles que VRAI ou FAUX, des matrices, des valeurs derreur ou des rfrences de cellules. Largument choisi doit produire une valeur valide. Les arguments peuvent aussi tre des constantes, des formules ou dautres fonctions. Si la fonction dbute une formule, tapez un signe gal (=) avant le nom de la fonction.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 5.1. Saisir au clavier une fonction

65

Il vous est possible dcrire une formule en utilisant lassistant des fonctions qui est expliqu un peu plus loin. Cependant, il est parfois plus simple de la saisir au clavier. Lorsque vous utilisez des fonctions simples il est quasiment prfrable de se laisser guider par les info-bulles qui safchent en mme temps que vous saisissez la fonction plutt que dutiliser lassistant. Par exemple, slectionner une cellule, puis commencez la saisie de la fonction par =somme(. Vous constatez lapparition de la bulle daide :

Ici, nombre1 et nombre2 pouvant correspondre une cellule ou une plage de cellules. Les trois petits points (. . . ) signient que la liste des arguments aprs le premier est facultatif. Ainsi nous pouvons nous contenter de ne slectionner quune cellule ou une plage de cellule. Ne laissez aucun espace entre le nom de la fonction et la premire parenthse telle que : =somme (A1;B2). Elle ne donnera pas de rsultat. Ceci est une erreur commune parmi les dbutants. Application : Calculez la valeur future au bout de 6 ans dun capital de 20 000,00 dh plac au taux dintrt annuel de 3 %. 1. Slectionner la cellule E4 2. Commencez la saisie de la fonction par =vc(

Vous constatez lapparition de la bulle daide :

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 3. Pour chaque argument, slectionnez alors la cellule qui convient

66

Les arguments entre crochets sont facultatifs dans cette fonction et peuvent tre omis ou remplacs par un point-vigule. Largument en gras indique largument en cours. 5.2. Insrer des fonctions prdnies

Vous pouvez vous faire assister pour saisir vous fonctions. Le tableur propose un assistant qui permet de guider la construction dune fonction laide de deux tapes. Il suft de suivre la procdure suivante : 1. Slectionnez la cellule dans laquelle vous dsirez entrer une fonction. 2. Ouvrez la fentre appele Insrer une fonction. Pour cela, vous avez deux possibilits : cliquez sur licne cliquez sur la commande Insertion | Fonction de la barre de formule ou

Vous venez ainsi douvrir une fentre appele Insrer une fonction. Trs simple dutilisation, elle est ddie en priorit aux dbutants. Utilisez cette aide pour toute fonction que vous ne connaissez pas parfaitement. Surtout, certaines fonctions possdent plusieurs arguments qui doivent tre placs dans le bon ordre.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur

67

Dans le champ Recherchez une fonction, vous pouvez taper sous la forme dune phrase ce que vous souhaitez faire avec cette fonction. Par exemple, tapez lexpression Comparer des informations ; puis cliquez sur le bouton [OK]. Le tableur dispose de nombreuses fonctions rparties en diffrentes catgories : mathmatiques, statistiques, nancires, logiques, dates, textes, etc. 3. Etape 1 : Slectionnez la catgorie qui contient Slectionnez la fonction recherche. Si vous ne savez pas quelle catgorie appartient la fonction que vous voulez, essayez la catgorie Toutes. 4. Slectionnez la fonction recherche et regardez son descriptif, si besoin est. 5. Validez en cliquant sur le bouton [OK] pour passer la deuxime tape. Une bote de dialogue safche pour vous aide entrer les arguments.

6. Etape 2 : Compltez les diffrents arguments de la fonction. Les noms des arguments de la fonction sont afchs ct des champs de saisie. Les arguments obligatoires sont afchs en gras. 6. Si un argument doit faire rfrence une cellule ou une plage de cellule, cliquez sur le . La palette de formules se rduit, utilisez la souris pour slectionner la plage. bouton

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 7. Cliquez sur le bouton pour retourner dans la palette de formules.

68

8. Cliquez sur le bouton [OK] an dinsrer la fonction. 5.3. Fonctions imbriques

Souvent lutilisation dune seule fonction est insufsante, il faut utiliser une fonction sur un rsultat dune autre fonction ou dun ensemble de fonctions. Une fonction peut utiliser une autre fonction comme argument, mais elle doit renvoyer le mme type de valeur que celui attendu par largument. Si une fonction imbrique ne renvoie pas le bon type dargument, Microsoft Excel afche la valeur derreur #VALEUR!. Une formule peut contenir jusqu sept niveaux dimbrication. Application : Soit calculer la valeur absolue de la somme des valeurs contenues dans la plage A1:A5 dans la cellule A6. Pour cela, crer la formule suivante, =ABS(SOMME(A1:A5)), dans la cellule A6. Il aussi possible dimbriquer les fonctions laide de lassistant. Prenons comme exemple la formule prcdente. 1. Slectionnez A6. 2. Cliquez sur licne .Une bote de dialogue apparat

3. Dans la bote de dialogue, vous choisissez la Catgorie Maths & Trigo, puis la fonction ABS. Cliquez sur le bouton [OK]. 4. Cliquez dans la zone du 1er argument (Nombre) de la palette Arguments de la fonction. 5. Cliquez sur le petit triangle liste des fonctions. an de drouler la

6. Choisissez la fonction SOMME. La fonction sinsre donc dans la fonction ABS. Si la fonction Somme nest pas dans la liste slectionnez Autres fonctions.
c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 7. La palette de formules vous aide remplir cette fonction SOMME.

69

5.4.

Somme automatique

Avec la fonction Somme automatique, Excel facilite laddition de valeurs qui est lun des calculs les plus frquents dans une feuille de calcul. Un bouton lui est ddi sur la barre doutils standard . Vous remarquez que ce bouton est accompagne sune che permettant daccder rapidement certaines autres fonctions mais aussi lassistant fonction Autres fonctions.

Somme automatique (normale) 1. Crez un nouveau classeur dans Excel, 2. Ralisez le tableau simple suivant

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 3. Slectionnez la premiere cellule de total (B7), 4. Cliquez sur le bouton Somme automatique,

70

La fonction SOMME safche dans la cellule avec entre parenthses, une suggestion de la liste des cellules sommer, ici B2:B6. Si la suggestion nest pas la bonne, il vous suft de cliquer et glisser la souris sur une autre plage de cellules pour changer les paramtres de la fonction. 6. Validez ce calcul en ralisant la combinaison de touches + . Ctrl Entre La fonction SOMME afche le rsultat du calcul dans la cellule ou elle est crite. Somme automatique (rapide) Il existe une autre mthode, plus rapide que celle dcrite plus haut pour raliser une somme automatique, lorsque le tableau sy prte. La ligne du total tant vide, 1. Slectionnez ensemble toutes les cellules sommer ainsi que les cellules dans lesquelles doivent apparatre les rsultats,

c FSJES-Agadir 2013 Tableurs Semestre 3

7 Le calcul avec un tableur 2. Cliquez sur le bouton Somme automatique .

71

Tous les rsultats safchent instantanment dans toutes less cellules du total.

Cette technique permet dviter ltape intermdiaire qui consiste valider la plage de slection sommer que suggre Excel. Les fonctions Moyenne, Max et Min Les fonctions Moyenne, Max et Min sutilisent exactement comme la fonction Somme. Il suft de slectionner en argument la plage de cellule sur laquelle elles doivent extraire la valeur caractristique. Fonction Moyenne Max Min Syntaxe MOYENNE(nombre1;nombre2;...) MAX(nombre1;nombre2;...) MIN(nombre1;nombre2;...) Descriptif Renvoie la moyenne dune liste de valeurs. Renvoie le plus grand nombre dune liste de valeurs. Renvoie le plus petit nombre dune liste de valeurs.

c FSJES-Agadir 2013 Tableurs Semestre 3

Fonctions logiques

Ces fonctions particulirement importantes et performantes permettent de faire dpendre le rsultat dun calcul dune ou plusieurs conditions. Il sagit plutt de rendre vos feuilles de calcul ractives certaines valeurs. La plus connue est la fonction SI, mais elle se voit complte par dautres fonctions bien pratiques. Laccs ces fonctions se fait travers la bote de dialogue :

1.

Fonction Si

La fonction SI permet deffectuer un test conditionnel sur des valeurs et des formules. Elle nest pas proprement parler une fonction de calcul : il sagit en fait dune fonction logique qui permet de faire un choix entre deux hypothses, en fonction dune expression logique. Cette expression logique peut prendre seulement deux valeurs : VRAI ou FAUX. La reprsentation graphique de la fonction Si() est :
c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques

73

En langage courant, la phrase correspondante serait : Si le test (est vrai) alors faire hyposse 1 sinon faire hyposse 2 La fonction SI() attend les arguments suivants : =SI(text_logique ; [valeur_si_vrai] ; [valeur_si_faux]) Largument Test_logique reprsente toute valeur ou expression logique qui peut prendre la valeur VRAI ou FAUX. Cet argument est obligatoire. Largument Valeur_si_vrai est la valeur renvoye si le test logique est VRAI. Cet argument est facultatif. En son absence, la fonction renvoie la valeur VRAI. Largument Valeur_si_faux est la valeur renvoye si le test logique est FAUX. Cet argument est facultatif. En son absence, la fonction renvoie la valeur FAUX. Si on fait une comparaison avec lassistant, la bote de dialogue Arguments de la fonction afche :

Le Test_logique constitue le premier argument introduire dans la formule. Il sagit souvent dune comparaison entre deux termes qui peuvent tre soit le contenu dune cellule et dune valeur, dune formule et dune valeur, de la rfrence dune cellule et celle dune autre, etc. Par exemple, D5=23 ou C4=12/B4 ou F3="Homme" ou etc.
c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques Symboles Oprateurs logiques Infrieur Suprieur Infrieur ou gal Suprieur ou gal Egal Diffrent de = = mathmatiques < > Tableur < > <= >= = <>

74

La construction dune formule utilisant la fonction SI ne prsente pas de difcult majeure. Il convient cependant de prvoir leffet attendu si la condition (lexpression logique) est remplie, mais galement leffet escompt dans le cas contraire. Dans les deux cas on va crire dans la formule le rsultat que lon voudra voir apparatre dans la cellule. Le rsultat que lon veux obtenir peut tre de natures diffrentes. Pour chacun deux, voici une liste des rsultats possibles, et la faon de les crire dans la formule pour les obtenir : Ce quon vaut afche Un nombre Un texte Un rsultat de calcul Un zro Rien (Aucun afchage) Ce quon doit saisir Le nombre Le texte entre guillemets La formule de calcul 0 "" 123,65 "Remise accorde" F4*10% F4 0 "" Exemple

Du contenu dune cellule Les rfrences de la cellule

Application : Afcher un message xe Le responsable dune entreprise dispose des commandes de quatre clients au premier trimestre. Il dcide daccorder une remise de 10 % ceux dont le total command dpasse 1000 dh. Pour cela il veut que le tableur afche "Accorde" ou rien du tout dans le cas contraire.

c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques Mise en place de la formule : Vous avez deux possibilits :

75

Saisissez la formule manuellement : Cliquez dans la cellule qui contiendra la formule et le rsultat, ici G4. Puis, tapez =SI(F4>1000;"Accorde";"") Utilisez lassistant : Suivez les instructions suivantes Cliquez dans la cellule qui contiendra la formule et le rsultat, ici G4. Ouvrez la fentre appele Insrer une fonction en cliquant sur licne de formule ou sur la commande Insertion | Fonction Compltez les diffrents arguments de la barre

Dans la Catgorie logique, slectionnez la fonction SI, puis cliquez sur [OK].

Cliquez sur [OK]. La valeur safche dans la cellule G4 Pour terminer, recopiez la formule de G4 vers le bas. Application : Afcher un message variable Le responsable de lentreprise veut, cette fois, que le tableur afche la valeur de la remise qui est de 10 % ceux dont le total command dpasse 1000 dh. Sinon, la remise est nulle.

Mise en place de la formule : Cliquez dans la cellule qui contiendra la formule et le rsultat, ici G4. Puis, tapez =SI(F4>1000;F4*10%;0) La cellule G5 contient bien la valeur 0 qui est le rsultat de la formule =SI(F5>1000;F5*10%;0) puisque la valeur de F5 nest pas suprieur 1000. Cependant, le tableur afche "- dh", car nous avons appliqu le format Montaire que nous avions personnalis.
c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques

76

2.

Imbriquer deux fonctions SI

Une fonction SI nautorise que deux rponses possibles. Si le problme rsoudre comporte trois rponses possibles, il faudra procder par limination en utilisant, successivement, deux fonctions SI. Les deux fonctions seront imbriques.

Nous avons ici affaire une imbrication deux niveaux, qui se matrialise de la faon suivante avec la syntaxe du tableur : =SI(Test 1 ; Dcision 1 ; SI(Test 2 ; Dcision 2 ; Dcision 3)) la deuxime fonction SI sexcutera si (et seulement si) la rponse au Test 1 est "FAUX". Notez que nous fermons deux parentheses car la seconde fonction SI est imbrique dans la premiere. Il faut fermer autant de parentheses quil y en a douvertes. Application : Augmentation du nombre de possibilits Imaginons que le responsable de lentreprise souhaite motiver davantage les clients dont le total command dpasse 3000 dh de sorte que : Si le total command dpasse 3000 dh, la remise est de 15 %. Si le total command est strictement entre 3000 dh et 1000 dh, la remise est de 10 %. Sinon, la remise est nulle.

c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques

77

Mise en place de la formule : Ce cas de gure envisage trois possibilits. Nous devons pour cela poser deux critres. Nous allons modier la formule contenue dans la cellule G4. Suivez les instructions suivantes : 1. Slectionner la cellule G4. Puis, cliquez sur la barre de formule 2. Mettez le point dinsertion | entre le signe = et SI 3. Tapez SI(F4>3000;F4*15% ; . Puis, dplacez le | vers la n 4. Tapez le caractre ) La cellule G4 doit contenir maintenant la formule : =SI(F4>3000;F4*15% ; SI(F4>1000;F4*10%;0)) Il faut prter une attention particulire lordre dans lequel vous imbriquez les tests, pour viter quune condition ne soit pas dj reprise au pralable par un autre test. Dans notre cas le fait de tester dabord les montants >1000 dh, rendrait un deuxime test sur des montants dpassant 3000 dh inutile car ces valeurs auraient dj t reprises par le premier test (3000 tant plus grand que 1000 !).

3.

Imbriquer plusieurs fonctions SI

Les formules conditionnelles utilises jusqu prsent ne permettent denvisager que deux possibilits ! Le rsultat est intressant mais pas vraiment satisfaisant. Cela dit, certaines problmatiques ncessitent llaboration de vritables arbres de dcisions, du type :

Il est possible dimbriquer jusqu 7 niveaux de test SI (Excel 2003) voire 64 niveaux de test SI (Excel 2010). Autant dire que certaines formules peuvent tre illisibles !
c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques

78

4.

Fonctions ET et OU

Il faut parfois combiner plusieurs tests logiques pour modliser une situation relle . Ceci est possible en utilisant la fonction ET ainsi que la fonction OU. Syntaxe =ET (valeur_logique1 ; [valeur_logique2] ; . . . ) =OU(valeur_logique1 ; [valeur_logique2] ; . . . ) La fonction ET renvoie VRAI si tous les arguments sont VRAI ; renvoie FAUX si au moins lun des arguments est FAUX. La fonction OU renvoie VRAI si au moins lun des arguments est VRAI ; renvoie FAUX si tous les arguments sont FAUX. valeur_logique1,valeur_logique2, . . . reprsentent les conditions que vous souhaitez tester et qui peuvent tre soit VRAI, soit FAUX. Application : Validation dun module dtude Soit un tableau prsentant les rsultats dun module constitu de deux units de cours (Micro et Compta). Deux rgles sont cumulatives pour obtenir la validation du module : La moyenne arithmtique des rsultats des units doit tre suprieur ou gale 10, Le rsultat dune unit de cours doit tre suprieur ou gal 5.

Mise en place de la formule : Nous allons vrier que la note de chaque unit de cours est suprieure ou gale 5 et que la moyenne est suprieure ou gale 10. Les critres seront (pour la ligne 4) : valeur_logique1 : C4>=5 an de vrier si la note de la Micro est
c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques valeur_logique2 : D4>=5 an de vrier si la note de la Compta est valeur_logique3 : E4>=5 an de vrier si la Moyenne est 10 5

79

Cliquez dans la cellule qui contiendra la formule et le rsultat, ici F4. =ET(C4>=5;D4>=5;E4>=10)

Puis, tapez

5.

Fonction NON

Loprateur NON renvoie la valeur inverse de son argument. Dans certains cas, il est plus pratique de manipuler la ngation dune condition logique que dutiliser la condition ellemme.

6.

Combiner des fonctions logiques

Les fonctions ET, OU et les oprateurs de comparaison ci-dessus sont limits aux seules valeurs VRAI ou FAUX . Il serait plus intressant sils pouvaient tre plus spciques. Cest pour cela quils sont souvent combins avec la fonction SI qui permet de prciser les valeurs quune cellule prend si une condition est vrie ou non. Syntaxe =SI(ET(Cond1;Cond2;. . . ;CondN) ; Dcision 1 ; Dcision 2) Dcision 1 : action raliser si les N conditions sont satisfaites Dcision 2 : action raliser si au moins une des conditions nest pas satisfaite

Application : Avis du jury Aprs dlibration, le jury dun module souhaite attribuer la mention "Valid" ceux dont les rgles de validation sont remplies, et la mention "Non valid" dans le cas contraire.

c FSJES-Agadir 2013 Tableurs Semestre 3

8 Fonctions logiques

80

Mise en place de la formule : Nous allons modier la formule contenue dans la cellule F4. Suivez les instructions suivantes : 1. Slectionner la cellule F4. Puis, cliquez sur la barre de formule 2. Mettez le point dinsertion | entre le signe = et ET 3. Tapez SI( . Puis, dplacez le | aprs ET(C4>=5;D4>=5;E4>=10) 4. Tapez ;"Valid" ; "Non valid") La cellule F4 doit contenir maintenant la formule : =SI(ET(C5>=5;D5>=5;E5>=10) ; "Valid" ; "Non valid") Application : Cas dbattre par le jury Imaginez que le jury souhaite faire valider le module aux tudiants qui rpondent aux critres de validation, refaire le module aux tudiants dont la moyenne est strictement infrieure a 9,5 et et dbattre pour ceux dont la note est suprieur ou gale 9,5 (y compris les tudiants ayant une moyenne suprieur ou gale 10, sans pouvoir valider leur module).

Mise en place de la formule : Modiez la formule contenue dans F4 comme suit : =SI(ET(C4>=5;D4>=5;E4>=10) ; "Valid" ; SI(E4>=9,5 ; "A tudier" ; "Non valid"))

c FSJES-Agadir 2013 Tableurs Semestre 3

9
1.

Fonctions de comptage

Comptage sans conditions

La premire phase de ltude dune population est den dnombrer les individus. Les trois fonctions NBVAL, NB, [Link] sont connexes. Elles retournent toutes les trois un nombre mais avec quelques variantes. 1.1. Fonction NBVAL

La fonction NBVAL compte le nombre de cellules qui ne sont pas vides et les valeurs comprises dans la liste des arguments. Utilisez NBVAL pour compter le nombre de cellules contenant des donnes dans une plage de cellules. Sa syntaxe : =NBVAL(valeur1 ; [valeur2] ; . . . ) valeur1, valeur2,. . . reprsentent les arguments correspondant aux valeurs compter. Seul le premier argument est obligatoire La fonction NBVAL compte tout type dinformation, y compris du texte vide, le caractre espace, des valeurs logiques ou des valeurs derreur. Notez quune cellule peut sembler vide mais elle contient en fait un espace ou un texte vide. De ce fait, cette cellule nest pas considre comme tant vide par NBVAL.

Application : Le tableau suivant reprsente une liste de personnes ayant pass un examen. Les candidats tant prsents ont une note. Les candidats absents sont nots par un ABS. Lobjectif est de compter le nombres de candidats inscrits lexamen.
c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage

82

Mise en place de la formule : La cellule H2 contient la formule =NBVAL(C3:C8) 1.2. Fonction NB

La fonction NB compte le nombre de cellules contenant des valeurs numriques et les valeurs numriques compris dans la liste des arguments. Utilisez NB pour obtenir le nombre dentres numriques dans dune plage de cellules. Sa syntaxe : =NB(valeur1 ; [valeur2] ; . . . ) valeur1, valeur2,. . . reprsentent les arguments correspondant aux valeurs compter. Seul le premier argument est obligatoire Les arguments de la fonction NB peuvent contenir ou rfrer diffrents types de donnes, mais seuls les nombres sont compts. Les arguments qui sont des nombres, des dates ou une reprsentation textuelle de nombres sont pris en compte. Application : Reprenons le tableau reprsentant la liste de personnes ayant pass un examen. Lobjectif est de compter le nombres de participants lexamen.

Mise en place de la formule : La cellule H2 contient la formule =NB(C3:C8)

c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage 1.3. Fonction [Link]

83

La fonction [Link] compte le nombre de cellules vides dune plage de cellules. Sa syntaxe : =[Link](plage) plage reprsente la plage dans laquelle vous voulez compter les cellules vides. Les cellules contenant des formules qui renvoient "" (texte vide) sont galement comptes, ce qui nest pas le cas des cellules contenant la valeur 0. Notez quune cellule contenant la formule =SI(VRAI;"";0), qui renvoie du texte vide, est considre comme tant vide par [Link], mais elle nest pas considre comme tant vide par NBVAL.

Application : Le tableau suivant reprsente une liste de personnes tant prsents lors dun examen. Certains candidats, qui ont oubli dinscrire leurs noms sur leurs copies dexamen, nont pas t nots. Lobjectif est de compter le nombres de candidats non nots.

Mise en place des formules : La cellule H2 contient la formule =[Link](C3:C8)

2.
2.1.

Comptage avec conditions

La fonction [Link]

Quand vous cherchez compter le nombre de fois o vous avez un nom, une date, une valeur dans une liste, seule la fonction [Link] doit tre utilise. La fonction ne ncessite que deux paramtres pour pouvoir fonctionner. Sa syntaxe :
c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage =[Link](plage ; critre) plage : une plage de donnes o se trouve la donnes que vous cherchez comptabiliser critre : la donne rechercher ou le critre de recherche. Application :

84

Dans le tableau suivant, vous avez une liste de vente de matriel informatique et voulez savoir combien de fois vous avez vendu de souris, et combien de fois vous avez reu de commandes dune quantit suprieure ou gale 10 units.

Mise en place des formules : La cellule D13 contient la formule =[Link](D4:D11 ; "Souris"). La cellule D14 contient la formule =[Link](E4:E11 ; ">=10"). Il est noter que la valeur recherche dans le tableau doit tre identique celles du tableau. Par exemple, les mots "Souris" et " Souris" ne sont pas identiques puisque le deuxime commence par un espace. Notez aussi quun test avec un oprateur logique, tout comme pour du texte, doit se mettre entre guillemets. 2.2. La fonction [Link]

La fonction [Link] vous permet de dnombrer combien de fois vous avez un lment dans une liste. Cependant, lorsque vous voulez faire un dnombrement sur plusieurs critres, la fonction [Link] montre ses limites.
c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage

85

La fonction [Link] (ENS pour ensemble) apporte la solution et le nombre de critres que vous pouvez utiliser peut aller jusqu 255 ! Cette fonction nest disponible que depuis la version dExcel 2007. Sa syntaxe : =[Link](plage_critres1 ; critre1 ; [plage_critres2 ; critres2] ; . . . ) Application : Supposons que vous souhaitez connatre le nombre de femmes ayant obtenu une moyenne suprieure ou gale 10, lors dun concours.

Mise en place de la formule : Pour chacun des critres, slectionnez tout dabord la plage de cellules o ce critre se trouve et ensuite votre critre lui-mme. Votre formule scrit alors : =[Link](C4:C12 ; "Femme" ; E4:E12 ; ">=10"). 2.3. La fonction [Link]

Cette fonction est base sur le mme principe que la fonction [Link]. Mais, au lieu de compter le nombre de fois quun lment est prsent dans une liste, vous pouvez ici calculer la somme dune plage correspondant votre critre. La fonction [Link] a besoin de trois paramtres pour fonctionner. Sa syntaxe : =[Link](plage ; critre ; somme_plage) plage critre : la plage de cellules correspond lendroit ou lon va aller rechercher le critre. : la donne rechercher ou le critre de recherche.

c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage

86

somme_plage : la plage de cellules correspond aux chiffres que lon va devoir additionner en fonction du critre. Application : Dans le tableau suivant, vous avez une liste de vente de matriel informatique et supposons que vous souhaitez connatre la quantit vendu de chaque produit.

Mise en place des formules : Suivez les instructions suivantes : 1. Slectionnez la cellule I5 2. Tapez, le dbut de la formule, =[Link]( 3. Slectionnez la plage qui contient vos produits C2:C11 et appuyez sur F4 4. Tapez le caractre ; puis entrez le critre de slection en slectionnant la cellule H5, puis trois fois successives appuyez sur F4 5. Slectionnez la plage qui contient les donnes que vous voulez sommer. Donc, pour dterminer la quantit vendue, slectionnez la plage D2:D11 et appuyez sur F4 Votre formule scrit alors : =[Link]($C$4:$C$11 ; $H5 ; $D$4:$D$11). Copiez la formule vers la droite, puis ditez la formule contenue dans J5 et remplacez les deux lettres D par deux lettres F. Pour terminez, Slectionnez la plage I5:J5 et copiez vers le bas.

c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage 2.4. La fonction [Link]

87

La fonction [Link] se construit comme la fonction [Link] dans le sens o vous avez la possibilit de raliser une somme sur une partie de vs donnes en slectionnant un ou plusieurs critres. Cette fonction nest disponible que depuis la version dExcel 2007. Sa syntaxe : =[Link](somme_plage ; plage_critres1 ; critre1 ; [plage_critres2 ; critres2] ; . . . ) Notez la diffrence entre [Link] et [Link]. En effet, largument somme_plage est le premier argument dans [Link], mais le dernier dans [Link]. Ainsi, si vous copiez ou modiez ces fonctions similaires, veillez placer les arguments dans lordre appropri. 2.5. La fonction SOMMEPROD

La fonction SOMMEPROD a t dveloppe pour permettre de faire la somme des produits de matrices. Do son nom, Somme Produits. Cest une des plus utiles et plus puissantes fonctions dExcel. Cependant, SOMMEPROD permet de faire beaucoup plus que la somme de produits. En effet, par une syntaxe dtourne, elle permet de raliser une somme ou un dnombrement de donnes selon plusieurs conditions. Ce qui permet bien dautres utilisations importantes qui ne sont pas documentes dans laide dexcel. Utilisation standard Dans sa forme classique, SOMMEPROD multiplie chaque valeur dans une plage par la valeur correspondante dans une autre plage de mme dimension, et renvoie la somme des produits. Syntaxe : =SOMMEPROD(matrice1 ; [matrice2] ; [matrice3] ; . . . ) matrice1, matrice2, matrice3,. . . reprsentent les matrices dont vous voulez multiplier les valeurs pour ensuite additionner leur produit. Seul le premier argument est obligatoire SOMMEPROD affecte aux entres de matrice non numriques la valeur zro. Soit A et B deux matrices de mme dimensions ou tout simplement de tableaux rectangulaires ayant le mme nombre de lignes et le mme nombre de colonnes : a11 a12 b11 b12 A = a21 a22 B = b21 b22 a31 a32 b31 b32
c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage SOMMEPROD(A; B)=a11 b11 + a12 b12 + a21 b21 + a22 b22 + a31 b31 + a32 b32 Application :

88

Le tableau suivant contient les notes obtenues par un tudiant en six matires. Les coefcients ne sont pas les mmes. Lobjectif est de calculer la moyenne obtenu par ltudiant.

Mise en place des formules : Pour calculer une moyenne pondre, nous allons calculer la somme pondre des notes avec leurs coefcients respectifs, puis diviser par la somme des coefcients : La fonction SOMMEPROD vous permet de raliser la somme pondre des notes avec leurs coefcients respectifs sans faire tous les calculs intermdiaires. La fonction SOMME vous permet de calculer la somme des coefcients. La cellule C11 contient alors la formule =SOMMEPROD(C4:C9;D4:D9)/SOMME(D4:D9) SOMMEPROD tendu La fonction SOMMEPROD est plus couramment utilise pour calculer le nombre ou la somme de valeurs dune plage en fonction de conditions testes dans diffrentes plages. Dnombrement conditionnel, avec autant de tests logiques que ncessaire. La syntaxe est : =SOMMEPROD((plage1=condition1)*(plage2=condition2)*(plage3)) Somme conditionnelle, avec autant de tests logiques que ncessaire. Souvent, on place les tests logiques avant la plage contenant les valeurs sommer.
c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage =SOMMEPROD((plage1=condition1)*(plage2=condition2)*(plage3)) qui est quivalent =SOMMEPROD((plage1=condition1)*(plage2=condition2);(plage3))

89

Les paramtres plage1=condition1, plage2=condition2,. . . doivent tre des "formules" qui renvoient VRAI ou FAUX. Il nest pas possible dutiliser les fonctions SI, ET, OU dans les formules des tests logiques. Vous exploiterez le fait que la valeur logique VRAI correspond la valeur numrique 1, et la valeur logique FAUX la valeur numrique 0, pour intgrer des plages qui seront en fait des tests sur diffrents critres. Application : Responsable dun magasin de vente de matriel informatique, vous souhaitez suivre les ventes ralises.

Imaginons que votre objectif est de connatre rapidement les informations suivantes : Compter le nombre des ventes faites par Brahim dans la rgion du Nord. Cest une formule conditionnelle qui emploie deux conditions. =SOMMEPROD((C4:C11="Brahim")*(D4:D11="Nord")) Compter le nombre de ventes de quantits comprises entre certaines valeurs spciques Par exemple, compte le nombre de fois o les quantits vendues sont comprises entre 10 et 20. La formule emploie deux conditions : la quantit suprieure ou gale 10, et la quantit infrieure ou gale 20.
c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage =SOMMEPROD((F4:F11>=10)*(F4:F11<=20)) Totaliser les montants raliss par vendeur dans une rgion donne

90

La formule suivante calcule les montants raliss, base sur deux conditions : quand le vendeur est Brahim et la rgion est Nord. =SOMMEPROD((C4:C11="Brahim")*(D4:D11="Nord")*(H4:C11)) Mais nous pouvons aussi donner des noms aux plages C4:C11, D4:D11 et H4:C11. Pour cela : Slectionnez la plage C4:C11 Cliquez sur Insertion | Nom | Dfnir . Puis entrez un Nom, par exemple, Vendeurs. Cliquez sur le bouton [OK]. Faites de mme avec les plages D4:D11 et H4:C11 que vous pouvez nommer respectivement Regions et Montants. Vous pouvez maintenant utiliser la formule : =SOMMEPROD((Vendeurs="Brahim")*(Regions="Nord")*Montants) Vous voulez, maintenant, une formule simple que vous dveloppez dans une seule cellule (coin suprieur gauche du tableau) et que vous pouvez copier/coller dans toutes les cellules du tableau de synthse. La formule qui suit se trouve dans la cellule L5 et elle scrit comme suit : =SOMMEPROD((Vendeurs=L$4)*(Regions=$K5)*Montants)

c FSJES-Agadir 2013 Tableurs Semestre 3

9 Fonctions de comptage Totaliser les ventes combines pour deux vendeurs

91

La formule ci-dessous renvoie la somme des ventes pour Brahim et Sara. Cette formule emploie un argument simple et se compose de trois expressions. =SOMMEPROD(((C4:C11="Brahim")+(C4:C11="Sara"))*(H4:C11)) Les deux premiers termes (C4:C11="Brahim") et (C4:C11="Sara") ne sont pas multiplis comme dans les exemples prcdents. Dans la mesure o nous ralisons un test logique "OU", ils sont additionns : un montant sera pris en considration si le vendeur associ est Brahim OU Sara.

c FSJES-Agadir 2013 Tableurs Semestre 3

10

Fonctions de Recherche

Certaines donnes sont structures dans des listes ou tables. Au lieu de les saisir ou de les copier/coller sans cesse, il est intressant de les rapatrier dans les tableaux rsultats qui doivent les utiliser. Les fonction de recherche telles que RECHERCHEV et INDEX constituent de puissants outils de reprage de valeurs dans des listes et des tables.

1.

La fonction RECHERCHEV

La fonction RECHERCHEV permet de rcuprer des donnes partir dune plage de cellules (matrice ou table). Elle cherche une valeur donne dans la premire colonne de la matrice, puis renvoie la valeur contenue dans la cellule situe sur la mme ligne et dans la colonne que vous spciez dans la matrice. Syntaxe : =RECHERCHEV(valeur_cherche ; table_matrice ; no_index_col ; [valeur_proche]) valeur_cherche : la valeur chercher dans la premire colonne de la matrice, elle peut tre une valeur ou une rfrence. table_matrice no_index_col valeur_proche : la plage de donnes dans laquelle est excute la recherche de la valeur. Utilisez une rfrence une plage ou un nom de plage. : est le numro de la colonne de la matrice dont la valeur correspondante doit tre renvoye. : reprsente une valeur logique VRAI/FAUX indiquant si vous souhaitez que la fonction RECHERCHEV recherche une valeur exacte ou voisine de celle que vous avez spcie.

Si valeur_proche est VRAI ou omis, la fonction RECHERCHEV renvoie une donne exacte si elle est trouve, ou la valeur immdiatement infrieure. Dans ce cas, les valeurs de la premire

c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche

93

colonne de table_matrice doivent tre classes en ordre croissant ; sans cela, la valeur renvoye ne sera pas forcment la bonne. Si valeur_proche est FAUX, il nest pas indispensable que les valeurs de la premire colonne de table_matrice soient tries. Dans ce cas, la fonction RECHERCHEV renvoie exactement la valeur recherche. Si aucune valeur ne correspond, la valeur derreur #N/A est renvoye. Application : On veut ici partir de la moyenne obtenue par chaque tudiant indiquer automatiquement sa mention en fonction du barme suivant : Intervalle [0, 10[ [10, 12[ [12, 14[ [14, 16[ [16, +[ Description De 0 compris 10 non compris De 10 compris 12 non compris De 12 compris 14 non compris De 14 compris 16 non compris De 14 compris 16 non compris Echec Passable Assez Bien Bien Trs Bien Mention

Pour cela vous disposer de deux tableaux lun pour les rsultats des tudiants et lautre pour le barme des mentions, voir la feuille de calcul suivante :

La premire colonne du tableau relatif au barme mentionne uniquement la borne infrieure de lintervalle correspondant la mention. La borne suprieure est implicitement gale la borne infrieure de lintervalle suivant. Mise en place des formules : La liste de barme des mentions sans les en-ttes de colonnes, sera nomme Mention. Nommez la plage de cellules F4:G8 par Mention. Cette plage (table) reprsentera le deuxime argument (matrice) de RECHERCHEV.
c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche

94

Slectionnez la cellule D3. Cest celle qui va contenir la mention en fonction de la moyenne de ltudiant. Si vous prfrez lassistant. Cliquez sur licne , puis slectionnez la catgorie Recherche & Matrices, slectionnez ensuite RECHERCHEV. Appuyez sur [OK] pour valider Renseignez les champs de la boite qui souvre :

La zone de saisie Synvaleur_proche peut contenir la valeur VRAI ou 0 ou tout simplement rester vide. Cliquez sur OK. Copiez la formule vers le bas. La cellule D3 doit donc contenir la formule suivante : =RECHERCHEV(C3 ; Mention ; 2) Application : Imaginez que vous grez un magasin de matriels informatique et que vous souhaitez tablir une facturation darticles. An de mette en uvre la fonction RECHERCHEV, il faut crer au pralable une liste (matrice) permettant de regrouper les donnes que vous allez devoir rcuprer par la suite lors de la recherche. La liste sans les en-ttes de colonnes, sera nomme Articles.

Nous allons crer un modle de facture et y ajouter des formules an dobtenir une saisie automatique des dsignations, des prix, des remises, aprs simple saisie du code de larticle concerne.
c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche

95

Mise en place des formules : Les deux tableaux (liste des articles et le modle de la facture) se trouvent dans deux feuilles de calcul diffrentes. Slectionnez la feuille contenant la liste des articles, puis nommer la plage de cellules B3:E8 par Articles. Cette plage (table) reprsentera le deuxime argument (matrice) de RECHERCHEV. Slectionnez la feuille de calcul content le modle de la facture que nous avons cr. Slectionnez la cellule C4. Cest celle qui va contenir la dsignation en fonction du code de larticle. Indiquez que vous allez effectuer une recherche exacte en positionnant le paramtre 0 ou Faux en quatrime argument. Tapez alors la formule =RECHERCHEV(B4 ; Articles ; 2 ; Faux) Slectionnez la cellule E4. Cest celle qui va contenir le prix HT (unitaire) en fonction du code de larticle. Tapez la formule =RECHERCHEV(B4 ; Articles ; 3 ; Faux) Slectionnez la cellule F4. Cest celle qui va contenir la remise (unitaire) en fonction du code de larticle. Tapez la formule =E4*RECHERCHEV(B4 ; Articles ; 4 ; 0) Nommer la plage la celluleDA par TxTVA. Cette cellule reprsente le taux de TVA. Tapez les formules suivantes respectivment dans G, H, I : =D4*(E4-F4), =G4*TxTVA, =G4+H4

Copiez toutes les formules que vous venez de crer vers le bas. Ne pas confondre le numro de colonne dans une liste avec le numro de colonne de la feuille de calcul qui contient cette liste. Par exemple, le prix HT se trouve dans la colonne numro 3 par rapport la liste Articles, mais cest la colonne numro 4 (colonne D) par rapport la feuille de calcul.
c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche

96

2.

La fonction RECHERCHEH

La fonction RECHERCHEH cherche une valeur donne dans la premire ligne dune matrice, puis renvoie la valeur contenue dans la cellule situe sur la mme colonne et dans la ligne que vous spciez dans la matrice. Cette fonction fonctionne exactement comme La fonction RECHERCHEV. Syntaxe : =RECHERCHEH(valeur_cherche ; table_matrice ; no_index_lig ; [valeur_proche]) Application : Reprenons lexemple de la gestion dun magasin de matriels informatique sauf que la liste des Articles est structur en lignes. An de mette en uvre la fonction RECHERCHEH, il faut crer au pralable une liste (matrice structure en lignes) permettant de regrouper les donnes que vous allez devoir rcuprer par la suite lors de la recherche. La liste sans les en-ttes de lignes, sera nomme Articles2.

Nous allons rutiliser le modle de facture prcdent et adapter ses formules.

Mise en place des formules : Les deux tableaux (liste des articles et le modle de la facture) se trouvent dans deux feuilles de calcul diffrentes. Slectionnez la feuille contenant la liste des articles, puis nommer la plage de cellules C2:H5 par Articles2.
c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche Slectionnez la feuille de calcul contenant le modle de la facture. Dans C4, modiez la formule comme suit =RECHERCHEH(B4 ; Articles2 ; 2 ; Faux) Dans E4, modiez la formule comme suit =RECHERCHEH(B4 ; Articles2 ; 3 ; Faux) Dans F4, modiez la formule comme suit =E4*RECHERCHEH(B4 ; Articles2 ; 4 ; 0) Les formules dans G, H, I restent les mmes. Copiez toutes les formules vers le bas.

97

3.

La fonction INDEX

La fonction INDEX permet la recherche dune valeur dans un tableau (matrice) en fonction de ses coordonnes. =INDEX(tableau ; no_lig ; no_col) tableau : la plage de cellules dans laquelle la fonction effectuera la recherche. no_lig : Numro de la ligne du tableau partir de laquelle une valeur doit tre renvoye. no_col : Numro de la colonne du tableau partir de laquelle une valeur doit tre renvoye.

Application : Rcuprez des donnes laide de ses coordonnes

Mise en place des formules : Tapez, dans la cellule H6, la formule =INDEX(Articles ; H4 ; H5) o Articles est le nom donn la plage B3:E8.
c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche

98

4.

La fonction EQUIV

La fonction EQUIV recherche la position dune valeur dans une matrice. Utilisez la fonction EQUIV plutt quune des fonctions RECHERCHE lorsque vous avez besoin de la position dun lment dans une plage et non de llment en tant que tel. Syntaxe =EQUIV(valeur_cherche ; matrice_recherche ; type) valeur_cherche : la valeur dont vous souhaitez obtenir la position.

matrice_recherche : la plage de cellules dans laquelle la fonction recherchera la position de valeur_cherche. type : entrez 0 pour trouver la valeur exacte, 1 pour la valeur la plus leve qui est infrieure ou gale valeur_cherche, -1 pour la plus petite valeur qui est suprieure ou gale valeur_cherche.

Application : Recherchez la position dun article saisie dans la cellule H4

Mise en place des formules : Tapez, dans la cellule H5, la formule =EQUIV(H4 ; C3:C8 ; 0) Il est essentiel de toujours prciser le dernier paramtre de EQUIV et de le mettre 0. Il nest pas ncessaire de trier le tableau dans ce cas et cest un des gros avantage de lutilisation du couple INDEX et EQUIV pour extraire des donnes dun tableau.

c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche

99

5.

Composition INDEX & EQUIV

La fonction RECHERCHEV comme vous le constatez ces limites : la recherche doit seffectuer obligatoirement dans la premire colonne. Pour palier ceci vous pouvez alors combiner les fonctions INDEX et EQUIV. La fonction EQUIV va vous donner la position dune valeur dans une colonne ou une ligne. Autrement, elle vous donne le numro de ligne no_lig ou le numro de colonne no_col de la valeur La fonction INDEX renvoie une donne dune matrice partir de ses coordonnes dans la matrice. Chaque coordonne peut tre le rsultat dune fonction EQUIV. Application : Recherchez le prix en fonction de larticle ou larticle en fonction de son prix.

Mise en place des formules : Pour amliorer la lisibilit des formules crer, nous avons donn la plage B3:E8 le nom Articles. Si vous voulez savoir quel est le prix de larticle saisie dans la cellule H3, tapez la formule =INDEX(Articles;EQUIV(H3;C3:C8;0);3) dans la cellule H4, par exemple. Si vous voulez savoir quel est le produit vendu au prix saisie dans la cellule H7, tapez la formule =INDEX(Articles;EQUIV(H7;D3:D8;0);2) dans la cellule H8, par exemple.

c FSJES-Agadir 2013 Tableurs Semestre 3

10 Fonctions de Recherche

100

Sil existe plusieurs produits ayant le mme prix, la combinaison INDEX&EQUIV ne vous donnera que le premier produit tout comme la fonction RECHERCHEV. Dans cet exemple, nous avons utilis la syntaxe suivante (une alternative RECHERCHEV) : =INDEX(tableau ; EQUIV(valeur_cherche ; colonne_rech ; 0) ; no_col) Celle-ci est galement utilisable (une alternative RECHERCHEH) : =INDEX(tableau ; no_lig ; EQUIV(valeur_cherche ; ligne_rech ; 0)) Dans ce cas, le rsultat renvoy par la fonction EQUIV sera le numro de colonne no_col au lieu du numro de ligne no_lig. Application : Recherche croise Excel On recherche le chiffre daffaires ralis par un vendeur donn dans une rgion donne.

Mise en place des formules : Pour amliorer la lisibilit de la formule crer, on cr dans le tableau ci-dessus trois plages nommes : Chiffres : la plage D4:F7, qui correspond aux donnes principales du tableau. Regions : la plage C4:C7, qui correspond aux 4 rgions. Vendeurs: la plage D3:F3, qui correspond aux 3 vendeurs. La formule mettre dans la cellule I6 est : =INDEX(Chiffres ; EQUIV(I4 ; regions ; 0) ; EQUIV(I3 ; vendeurs ; 0))

c FSJES-Agadir 2013 Tableurs Semestre 3