Excel2010 - Guide Complet

EXCEL 2010FONCTIONS ET FORMULES

Copyright 2010 Micro Application20-22, rue des Petits-Htels75010 PARIS

1re Edition - Juin 2010

Auteurs PREMIUM CONSULTANTS, SARL Unipersonnelle

Toute reprsentation ou reproduction, intgrale ou partielle, faite sans leconsentement de MICRO APPLICATION est illicite (article L122-4 du code de laproprit intellectuelle).Cette reprsentation ou reproduction illicite, par quelque procd que ce soit,constituerait une contrefaon sanctionne par les articles L335-2 et suivantsdu code de la proprit intellectuelle.Le code de la proprit intellectuelle nautorise aux termes de larticle L122-5que les reproductions strictement destines lusage priv et non destines lutilisation collective dune part, et dautre part, que les analyses et courtescitations dans un but dexemple et dillustration.

Les informations contenues dans cet ouvrage sont donnes titre indicatif etnont aucun caractre exhaustif voire certain. A titre dexemple non limitatif,cet ouvrage peut vous proposer une ou plusieurs adresses de sites Web quine seront plus dactualit ou dont le contenu aura chang au moment o vousen prendrez connaissance.Aussi, ces informations ne sauraient engager la responsabilit de lEditeur. Lasocit MICRO APPLICATION ne pourra tre tenue responsable de toute omis-sion, erreur ou lacune qui aurait pu se glisser dans ce produit ainsi que desconsquences, quelles quelles soient, qui rsulteraient des informations etindications fournies ainsi que de leur utilisation.Tous les produits cits dans cet ouvrage sont protgs, et les marques dposespar leurs titulaires de droits respectifs. Cet ouvrage nest ni dit, ni produit parle(s) propritaire(s) de(s) programme(s) sur le(s)quel(s) il porte et les marquesne sont utilises qu seule fin de dsignation des produits en tant que noms deces derniers.

ISBN : 978-2-300-029318

ISSN : 1768-1812

Couverture ralise par Olo

MICRO APPLICATION Support technique20-22, rue des Petits-Htels galement disponible sur75010 PARIS www.microapp.comTl. : 01 53 34 20 20Fax : 01 53 24 20 00http://www.microapp.com

Retrouvez des informations sur cet ouvrage !

Rendez-vous sur le site Internet de Micro Applicationwww.microapp.com. Dans le module de recherche,sur la page daccueil du site, entrez la rfrence 4 chiffres indique sur le prsent livre.Vous accdez directement sa fiche produit.

2931

Avant-proposDestine aussi bien aux apprentis quaux utilisateurs chevronns,la collection Guide Complet Poche aborde lensemble du sujettrait. Privilgiant toujours laspect pratique, elle vous permet deprogresser pas pas depuis la dcouverte dun logiciel, dunlangage ou dune technologie, jusqu sa matrise avance. Com-plte, elle dlivre de nombreux exemples pratiques, des trucs etastuces et des conseils de professionnels pour tirer le meilleurparti de vos attentes.

Conventions typographiquesAfin de faciliter la comprhension de techniques dcrites, nousavons adopt les conventions typographiques suivantes :

j gras : menu, commande, bote de dialogue, bouton, onglet.

j italique : zone de texte, liste droulante, case cocher, boutonradio.

j Police bton : Instruction, listing, adresse internet, texte saisir.

j : indique un retour la ligne volontaire d aux contraintes dela mise en page.

Il sagit dinformations supplmentaires relatives au sujet trait.

Met laccent sur un point important, souvent dordre techniquequil ne faut ngliger aucun prix.

Propose conseils et trucs pratiques.

Donne en quelques lignes la dfinition dun terme techniqueou dune abrviation.

Chapitre 1 laborer des formules simples 11

1.1. Connatre les principes de conception . . . . . . . . . . . . . . . . . . . . . . . . . 13Saisir une formule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13Modifier une formule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

1.2. Utiliser des oprateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15Utiliser des oprateurs mathmatiques . . . . . . . . . . . . . . . . . . . . . . . 15Utiliser loprateur de concatnation . . . . . . . . . . . . . . . . . . . . . . . . . . 17

1.3. Matriser les rfrences relatives, absolues et mixtes . . . . . . . 17Dcouvrir les rfrences de cellules . . . . . . . . . . . . . . . . . . . . . . . . . . . 18Rfrences tridimensionnelles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27Rfrences externes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

1.4. Dcouvrir des outils et paramtres supplmentaires . . . . . . . . 30Transformer une formule en valeur . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30viter quExcel recalcule systmatiquement les formules . . 31

1.5. Ne pas afficher les formules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32viter les incohrences daffichage dues aux arrondis . . . . . . . 33Afficher des rfrences du type L1C1 . . . . . . . . . . . . . . . . . . . . . . . . . . 34

Chapitre 2 Utiliser des noms dans les formules 35

2.1. Attribuer simplement un nom une celluleou une plage de cellules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Attribuer un nom une cellule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Utiliser un nom dans une formule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Attribuer un nom une plage de cellules . . . . . . . . . . . . . . . . . . . . . 39Slectionner une cellule ou une plage nomme . . . . . . . . . . . . . . 40

2.2. Dfinir et modifier les noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Dfinir un nom . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Modifier la cible dun nom . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42Insrer un nom dans une formule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Coller la liste des noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44Crer des sries de noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Supprimer un nom . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46Remplacer systmatiquement les rfrences de cellulespar les noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47

2.3. Attribuer des noms des constantes et des formules . . . . . 49Attribuer des noms des constantes . . . . . . . . . . . . . . . . . . . . . . . . . . 49Attribuer des noms des formules . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

2.4. Noms spcifiques dune feuille de calcul . . . . . . . . . . . . . . . . . . . . . . 51

Chapitre 3 Rechercher et utiliser des fonctions 53

3.1. Comprendre la notion de fonction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55Dcouvrir la bibliothque de fonctions . . . . . . . . . . . . . . . . . . . . . . . . 56

4

Rechercher et insrer une fonction . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57Saisir une fonction connue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60Utiliser les bibliothques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62Utiliser le bouton Somme automatique . . . . . . . . . . . . . . . . . . . . . . . 62

3.2. Utiliser les diffrents types darguments . . . . . . . . . . . . . . . . . . . . . . 633.3. Connatre les diffrentes catgories de fonctions . . . . . . . . . . . . 65

Les fonctions de recherche et de rfrence . . . . . . . . . . . . . . . . . . . 65Les fonctions de texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66Les fonctions de date et dheure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66Les fonctions logiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66Les fonctions dinformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66Les fonctions de base de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67Les fonctions mathmatiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67Les fonctions statistiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67Les fonctions financires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Les fonctions dingnierie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Chapitre 4 Utiliser la fonction SI 73

4.1. Dcouvrir la fonction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 754.2. laborer des formules simples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

Afficher un message fixe si une condition est remplie . . . . . . . 76Afficher un message variable si une condition est remplie . . 77Intgrer le rsultat dune formule conditionnelledans une expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77Faire un test sur une chane de caractres . . . . . . . . . . . . . . . . . . . . 78Faire un test sur une date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79viter laffichage dun message derreur . . . . . . . . . . . . . . . . . . . . . . 80Inclure une formule dans la condition . . . . . . . . . . . . . . . . . . . . . . . . . 81Compter le nombre de valeurs diffrentesdans une plage de cellules tries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

4.3. Utiliser les oprateurs ET et OU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Loprateur ET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83Loprateur OU . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84Loprateur NON . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86Quelques informations complmentaires sur les testslogiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

4.4. Imbriquer plusieurs fonctions SI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88Limiter la taille des formules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

4.5. Dcouvrir la fonction CHOISIR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91

Chapitre 5 Calculer et dnombrer 93

5.1. Dcouvrir les fonctions indispensables . . . . . . . . . . . . . . . . . . . . . . . 95Fonctions de calcul . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

SOMMAIRE

5

Fonctions darrondi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104Fonctions de comptage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

5.2. Faire des calculs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111Calculer une somme . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111Calculer un cumul glissant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112Calculer une moyenne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113Calculer la somme de cellules respectant des critres . . . . . . 113Dterminer la valeur la plus frquente dans une plagede cellules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115Calculer un pourcentage dvolution . . . . . . . . . . . . . . . . . . . . . . . . . 116Afficher les plus grandes valeurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117Arrondir des valeurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

5.3. Dnombrer des cellules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119Compter les cellules vides . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120Compter les cellules non vides . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120Compter les cellules contenant des valeurs numriques . . 120Compter les cellules contenant du texte . . . . . . . . . . . . . . . . . . . . . 121Compter les cellules contenant une chane de caractres . . 121Compter les cellules dont le contenu est suprieur un seuil . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121

Chapitre 6 Exploiter des bases de donnes 123

6.1. Grer des donnes avec Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125laborer un tableau de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

6.2. Dcouvrir les fonctions indispensables . . . . . . . . . . . . . . . . . . . . . . 1276.3. Faire des recherches simples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Rechercher une valeur prcise dans un tableau . . . . . . . . . . . . . 136viter lapparition de messages derreur lorsdune recherche . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139Utiliser dautres techniques de recherche . . . . . . . . . . . . . . . . . . . 140Exploiter des donnes sous forme dintervalles . . . . . . . . . . . . 142Rechercher une valeur dans un tableau double entre . . . 143

6.4. Synthtiser des donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144Calculer une moyenne mobile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144Faire des synthses multicritres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146

Chapitre 7 Traiter des donnes textuelles 151

7.1. Dcouvrir les fonctions indispensables . . . . . . . . . . . . . . . . . . . . . . 1537.2. Effectuer des traitements simples . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161

Juxtaposer des chanes de caractres . . . . . . . . . . . . . . . . . . . . . . . 161Intgrer des valeurs numriques dans des chanes decaractres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161Compter les caractres dun texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162

6

Rechercher dans un texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163Remplacer un texte par un autre . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164

7.3. Combiner les fonctions pour des traitementsplus complexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165Effacer des caractres droite ou gauche . . . . . . . . . . . . . . . . . 165Complter une chane caractres pour atteindreun nombre fix de caractres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165Compter le nombre doccurrences dun caractreou dun mot dans un texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Extraire le premier mot dun texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Extraire le dernier mot dun texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166Sparer les mots dun texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167

Chapitre 8 Faire des calculs sur les dates et les heures 169

8.1. Comprendre la reprsentation des dates dans Excel . . . . . . . 171Dcouvrir la notion de numro de srie . . . . . . . . . . . . . . . . . . . . . . 171Distinguer les systmes de dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171Saisir des dates et des heures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172Paramtrer linterprtation du sicle . . . . . . . . . . . . . . . . . . . . . . . . . 173

8.2. Dcouvrir les fonctions indispensables . . . . . . . . . . . . . . . . . . . . . . 1738.3. Faire des calculs sur les dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180

Afficher la date du jour dans un texte . . . . . . . . . . . . . . . . . . . . . . . . 181crire le mois en lettres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181crire le jour de la semaine en lettres . . . . . . . . . . . . . . . . . . . . . . . . 181Dterminer le numro du trimestre . . . . . . . . . . . . . . . . . . . . . . . . . . 181Dterminer le dernier jour du mois . . . . . . . . . . . . . . . . . . . . . . . . . . . 181Dterminer le premier jour du mois . . . . . . . . . . . . . . . . . . . . . . . . . . 182Calculer le nombre de jours du mois . . . . . . . . . . . . . . . . . . . . . . . . . 182Dterminer la date du dimanche prcdent . . . . . . . . . . . . . . . . . 182Convertir depuis le format amricain . . . . . . . . . . . . . . . . . . . . . . . . 184Reprer une date anniversaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185Tester si une anne est bissextile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

8.4. Faire des calculs sur les heures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185Transformer des heures dcimales en heures et minutes . . 186Transformer des minutes en heures et minutes . . . . . . . . . . . . . 186Calculer avec des taux horaires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187Calculer le temps coul . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187

Chapitre 9 Faire des calculs financiers 189

9.1. Dcouvrir les fonctions indispensables . . . . . . . . . . . . . . . . . . . . . . 191Les systmes de dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191Liste des fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192

9.2. Comprendre les notions essentielles . . . . . . . . . . . . . . . . . . . . . . . . . 203

7

Valeur acquise et valeur actualise . . . . . . . . . . . . . . . . . . . . . . . . . . . 203Calcul damortissement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204Calculs demprunts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205

9.3. Faire des analyses dinvestissements . . . . . . . . . . . . . . . . . . . . . . . . 205Calcul relatifs lpargne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205Rentabilit dun investissement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206Taux de rentabilit dun investissement . . . . . . . . . . . . . . . . . . . . . 206Calcul de la valeur actuelle nette dun projet . . . . . . . . . . . . . . . . 207

9.4. Faire des calculs lis aux emprunts . . . . . . . . . . . . . . . . . . . . . . . . . . 207Mensualits dun emprunt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208Calcul du cot dun emprunt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208Calcul de la dette rsiduelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208Capacit dendettement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210

9.5. Utiliser les tables de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210Crer des tables de donnes une entre . . . . . . . . . . . . . . . . . . . 211Crer des tables de donnes deux entres . . . . . . . . . . . . . . . . 211Faire des simulations avec les tables de donnes . . . . . . . . . . . 212

Chapitre 10 Utiliser les formules matricielles 217

10.1. Connatre les principes de conception . . . . . . . . . . . . . . . . . . . . . . . 220Saisir une formule matricielle une dimension . . . . . . . . . . . . . 220Saisir une formule matricielle deux dimensions . . . . . . . . . . 221Saisir une formule matricielle valeur unique . . . . . . . . . . . . . . 222Caractristiques des formules matricielles . . . . . . . . . . . . . . . . . . 223Modifier une formule matricielle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224Constantes matricielles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225

10.2. Utiliser les formules matricielles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225Calculer la moyenne des trois plus grandes valeurs . . . . . . . . 225Compter des cellules contenant du texte . . . . . . . . . . . . . . . . . . . . 226Compter les doublons dans une liste . . . . . . . . . . . . . . . . . . . . . . . . . 227Faire des calculs conditionnels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227Crer une suite de nombres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228Compter le nombre de lundi dun mois . . . . . . . . . . . . . . . . . . . . . . 228Transposer une matrice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229Inverser une matrice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230Rsoudre un systme dquations . . . . . . . . . . . . . . . . . . . . . . . . . . . 231

Chapitre 11 Auditer et corriger les formules 233

11.1. Reprer des erreurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235Dtecter les erreurs lors de la saisie . . . . . . . . . . . . . . . . . . . . . . . . . . 236Vrifier les erreurs dans une feuille de calcul . . . . . . . . . . . . . . . . 236

11.2. Auditer les formules de calcul . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24011.3. Matriser les rfrences circulaires . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

8

11.4. Utiliser les fonctions dinformationet de dtection derreur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247Utiliser les fonctions dinformation . . . . . . . . . . . . . . . . . . . . . . . . . . 248Utiliser les fonctions de dtection derreur . . . . . . . . . . . . . . . . . . 249

Chapitre 12 Dcouvrir dautres utilisations des formules 251

12.1. Dfinir des mises en forme conditionnelles . . . . . . . . . . . . . . . . . 253Utiliser les mises en forme conditionnelles prdfinies . . . . 253Crer des rgles de mise en forme conditionnellespersonnalises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255Crer des mises en forme conditionnelles . . . . . . . . . . . . . . . . . . . 257

12.2. Dfinir des validations du contenu des cellules . . . . . . . . . . . . . 258Connatre le principe de la validation du contenu . . . . . . . . . . . 259Crer des validations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263

Chapitre 13 Crer des fonctions personnalises 265

13.1. Comprendre les notions de base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267Dcouvrir les objets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267Dcouvrir les procdures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269Dcouvrir les variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271Connatre les instructions fondamentales de VBA . . . . . . . . . . 275

13.2. Dcouvrir les objets et les collections dExcel . . . . . . . . . . . . . . . 288Lobjet Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288La collection Workbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290Lobjet Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291La collection Worksheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292Lobjet Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293Lobjet Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294

13.3. Dcouvrir lditeur Visual Basic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297Afficher longlet Dveloppeur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297Dcouvrir lenvironnement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297Matriser le dbogage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302

13.4. Grer les niveaux de scurit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30613.5. laborer des fonctions personnalises . . . . . . . . . . . . . . . . . . . . . . 308

Calculer la TVA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308Calculer un taux de remise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311Afficher le nom de la feuille . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312Compter le nombre de voyelles dun mot . . . . . . . . . . . . . . . . . . . 312Afficher la rfrence de la dernire cellule de la feuille . . . . . 312Rendre disponibles les fonctions personnalissdans dautres classeurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314

9

Chapitre 14 Annexes 317

14.1. Dcouvrir les nouveauts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319Changements dappellations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319Fonctions amliores . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321Nouvelles fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

14.2. Liste des fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326Les fonctions de recherche et rfrence . . . . . . . . . . . . . . . . . . . . . 326Les fonctions de texte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330Les fonctions de date et dheure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334Les fonctions logiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338Les fonctions dinformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339Les fonctions de base de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342Les fonctions mathmatiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345Les fonctions statistiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356Les fonctions financires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373Les fonctions dingnierie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383

14.3. Raccourcis clavier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390Utiliser les onglets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390Utiliser les botes de dialogue . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391Travailler avec les feuilles de calcul . . . . . . . . . . . . . . . . . . . . . . . . . . 391Se dplacer dans les feuilles de calcul . . . . . . . . . . . . . . . . . . . . . . . 392Se dplacer au sein dune plage de cellules slectionne . . 392Slectionner les cellules, lignes, colonnes ou objets . . . . . . . 393Slectionner des cellules prsentant des caractristiquesparticulires . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394tendre une slection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394Entrer des donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395Saisir et calculer des formules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396Modifier des donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397Insrer, supprimer et copier des cellules . . . . . . . . . . . . . . . . . . . . . 398Mettre en forme des donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398

Chapitre 15 Index 401

10

CHAPITRE1

LABORERDES FORMULESSIMPLESConnatre les principes de conception ........................................ 13Utiliser des oprateurs .................................................................... 15Matriser les rfrences relatives, absolues et mixtes ............ 17Dcouvrir des outils et paramtres supplmentaires .............. 30Ne pas afficher les formules ........................................................... 32

11

Que serait Excel sans les formules de calcul ? Un espace de stoc-kage et de prsentation de donnes numriques ! Les formulesconstituent une part importante de la valeur ajoute dExcel. Eneffet, grce elles, vous pourrez raliser facilement des calculs surun nombre important de donnes. De plus, ces calculs serontractualiss rapidement si vous modifiez des valeurs.

Dans ce chapitre, vous dcouvrirez et mettrez en pratique lesrgles de base relatives la conception de formules.

1.1. Connatre les principes de conceptionVoyons dabord comment saisir une formule simple puis com-ment la modifier.

Saisir une formuleUne formule de calcul dbute imprativement par le caractre =(gal). Ce dernier indique Excel quil doit considrer les lmentsqui vont tre saisis comme une formule de calcul, et non commeune simple entre numrique ou alphanumrique.

Prenons un exemple :

1. Crez un nouveau classeur.

2. En A1, saisissez =19+71.3. Appuyez sur [].

Le rsultat du calcul apparat en A1, soit 90.

La formule, quant elle, apparat dans la barre de formule.

Figure 1.1 : Une formule simple

131.1. Connatre les principes de conception

Utilisation du pav numriquePour saisir des formules simples, comme celle que nous venons de

voir, utilisez de prfrence le pav numrique : vous gagnerez du temps.Mais sur ce dernier, le signe = nest pas prsent. La solution est simple :au lieu de saisir =, entrez le caractre + (plus) si le premier nombre estpositif ou (moins) sil est ngatif. Ainsi, si vous saisissez +19+71, Excelconvertit en =19+71.

Modifier une formuleLa saisie de formule se rvle dune grande simplicit. Mais per-sonne nest labri dune erreur. Cest pourquoi, il est importantde pouvoir modifier une formule saisie.

Reprenons lexemple prcdent en supposant que la formule cor-recte soit =(19+71)/5 :1. Double-cliquez sur la cellule A1.

2. laide de la souris ou en utilisant les touches [] et [], dpla-cez le curseur aprs le caractre = et saisissez la parenthseouvrante.

3. Dplacez le curseur la fin de la formule, laide de la touche[Fin], et saisissez la fin de la modification.

4. Appuyez sur [].

Le nouveau rsultat saffiche immdiatement (18).

Au moment o vous avez saisi la parenthse fermante, les deuxparenthses sont apparues fugitivement en gras. Excel signifie decette faon quil a compris que la parenthse fermante taitassocie la parenthse ouvrante. Cela permet de se reprer plusfacilement lorsquil existe plusieurs niveaux de parenthses dansune formule.

AutresmthodesIl existe deux autres faons de modifier une formule. Le rsultat est

bien sr identique quelle que soit la mthode utilise.

La premire variante consiste slectionner la cellule contenant laformule modifier et appuyer sur la touche [F2].

14 1. laborer des formules simples

La seconde variante consiste slectionner la cellule et cliquerdans la barre de formule.

Choisissez la mthode qui convient le mieux vos habitudes de travail.

1.2. Utiliser des oprateursNous allons dcrire les diffrents oprateurs pris en charge parExcel, en commenant bien sr par les plus classiques : les op-rateurs mathmatiques. Mais nous verrons galement un opra-teur qui permet de traiter les chanes de caractres.

Utiliser des oprateurs mathmatiques

Connatre les rgles de priorit des oprateurs

Pour concevoir vos formules, vous disposez des oprateurs ma-thmatiques courants :

j ^ : la puissance ;j * : la multiplication ;j / : la division ;j + : laddition ;j

: la soustraction.

Les rgles de priorit des oprateurs sont videmment respec-tes, cest--dire que les expressions utilisant loprateur puis-sance sont values en premier, puis viennent, au mme niveau,la multiplication et la division, et enfin laddition et la soustraction(mme niveau).

Voici quelques exemples de formules mettant en jeu les opra-teurs mathmatiques :

151.2. Utiliser des oprateurs

Tableau 1.1 : Quelques exemples de formules de calcul

Formule Rsultat

=4+5*3 = 4+15 = 19=23+10/2 = 2-3+5 = 4=5*6/218/3 = 15-6 = 9=2^2*5+32 = 4*5+3-2 = 20+32 = 21

Utiliser des parenthses

Les parenthses permettent dinfluer sur les rgles de priorit desoprateurs mathmatiques. En effet, toute expression place en-tre parenthses est value de faon prioritaire. Il est videmmentpossible dimbriquer des parenthses.

titre dexemple, vous allez calculer le prix TTC dun ensemble dedeux articles dont les prix HT sont 75 euros et 100 euros, surlesquels une remise respective de 10 % et 5 % a t pralablementapplique.

Pour obtenir un rsultat correct, il faut utiliser des parenthses. Eneffet, il sagit dabord dvaluer le prix total HT, compte tenu de laremise, puis de calculer le prix TTC :

1. Slectionnez A3.

2. Saisissez =(75*(110%)+100*(15%))*(1+19,6%).3. Appuyez sur [].

Voici comment Excel value cette formule :

j =(75*0,9+100*(15%))*(1+19,6%) : tape 1 ;j =(67,5+100*(15%))*(1+19,6%) : tape 2 ;j =(67,5+100*0,95)*(1+19,6%) : tape 3 ;j =(67,5+95)*(1+19,6%) : tape 4 ;j =162,5*(1+19,6%) : tape 5 ;j =162,5*1,196 : tape 6 ;j =194,35 : tape 7.


Lors de la saisie, chaque niveau de parenthse possde une cou-leur. Cela permet de mieux visualiser la hirarchie des parenth-ses. De plus, lorsque vous refermez une parenthse, la paire(ouvrante et fermante) est mise brivement en gras.

Utiliser loprateur de concatnationLoprateur & permet de concatner des chanes de caractres.Jusqu prsent, nous avons voqu des formules dont les op-randes taient numriques. Or, il peut tre ncessaire de manipu-ler des oprandes alphanumriques avec lesquels les oprateursmathmatiques nont aucun sens (essayez de diviser Bonjour par Au revoir !).1. Slectionnez A2.

2. Saisissez =" Micro "& " "& "Application ".3. Appuyez sur [].

Il est possible de concatner des expressions numriques avecdes expressions alphanumriques.

1.3. Matriser les rfrences relatives,absolues et mixtes

Si les possibilits en matire de calcul en restaient l, Excel neserait quune super-calculatrice. Or cette application est beaucoupplus que cela. Sa puissance supplmentaire provient, entreautres, de la possibilit de faire rfrence dautres cellules dansune formule. Cette facult autorise la conception de formulescomplexes et puissantes. Nous aborderons progressivement luti-lisation des diffrents types de rfrences dans la conception desformules de calcul.

Figure 1.2 : Une formule alphanumrique

171.3. Matriser les rfrences relatives, absolues et mixtes

Dcouvrir les rfrences de cellulesDonner la rfrence dune cellule en indiquant la ligne et la co-lonne o elle se trouve permet de localiser ladite cellule dans unclasseur. Les colonnes sont identifies par des lettres de (de A XFD, soit 16384 colonnes) et les lignes par des chiffres (de 1 1 048 576). Par exemple, A1 localise la cellule situe lintersec-tion de la premire colonne et de la premire ligne, B10 la cellulesitue lintersection de la deuxime colonne et de la diximeligne.

Lorsque vous saisissez la rfrence dune cellule dans une for-mule, vous utilisez le contenu de la cellule correspondante dansvotre formule.

Utiliser des rfrences de cellules

Voici lexemple le plus simple dutilisation dune rfrence decellule dans une formule :

1. Slectionnez A4.

2. Saisissez =A3.3. Appuyez sur [].

Le contenu de la cellule A4 est maintenant gal au contenu de lacellule A3. Si ce dernier varie, celui de A4 varie galement.

Pour mesurer lintrt de ce mcanisme, reprenons lexemple decalcul du prix TTC des deux articles. En cas de changements detarif ou de taux de remise, il faut modifier la formule contenue enA3, ce qui nest pas trs ais. Vous utiliserez plutt des rfrencespour vous faciliter la tche :

1. En A6, saisissez 75.2. En B6, saisissez 10%.

Figure 1.3 : Utilisationdune rfrence


3. En A7, saisissez 75.4. En B7, saisissez 10%.5. En A8, saisissez 19,6%.6. En A10, saisissez =((A6*(1B6)+A7*(1B7))*(1+A8)).

Au fur et mesure de lcriture de la formule, les rfrences descellules quelle intgre sont affiches avec des couleurs diff-rentes. Ces couleurs sont reprises au niveau de la bordure descellules correspondantes. Vous avez ainsi une vision synthti-que des cellules impliques dans une formule ( condition,bien sr, quelles soient toutes visibles lcran).

7. Validez par [].

Le rsultat contenu dans la cellule A10 est gal celui contenudans A3 mais il est beaucoup plus facile dintgrer dventuellesmodifications de tarif, de remise ou de taux de TVA en utilisant desrfrences comme en A10.

Figure 1.4 : Utilisation de rfrences dans un calcul

Figure 1.5 : Rsultat de la formule


Saisir une formule sous forme de texte ?Dans certains cas, il peut se rvler intressant dafficher une formule

et non son rsultat (pour lgender une feuille de calcul par exemple).Pour cela, faites-la prcder dune apostrophe.

Saisir des rfrences de cellules avec la souris

La dernire formule que vous avez cre compte cinq rfrencesde cellules. Leur saisie au clavier ne pose pas de problme carelles sont peu nombreuses et toutes visibles lcran en mmetemps. Mais dans la plupart des cas, il est prfrable de slection-ner les cellules correspondantes afin dinsrer leur rfrence dansla formule en cours.

1. Slectionnez A47.

2. Saisissez =(.3. Faites dfiler, laide de la barre de dfilement verticale, la

feuille de calcul jusqu ce que la cellule A6 soit visible.

4. Slectionnez la cellule A6 laide de la souris. En raction,Excel lentoure de pointills et dune bordure de couleur. Deplus, sa rfrence apparat dans la barre de formule, qui affichele contenu de la cellule active (A47).

5. Continuez la dfinition de la formule en saisissant les opra-teurs et les parenthses au clavier et en slectionnant les rf-rences avec la souris.

Figure 1.6 : Saisie de rfrence laide de la souris


6. Validez laide de la touche [].

Distinguer les diffrents types de rfrences : relativesabsolues et mixtes

Jusqu prsent, vous avez utilis des rfrences relatives : lors-que vous avez saisi =A3 dans la cellule A4, vous avez fait unerfrence, non pas la cellule A3 en tant que telle, mais la cellulese trouvant une ligne au-dessus de la cellule en cours (en loccur-rence A4). Ainsi, lorsque vous copiez le contenu de la cellule A4 etle collez en B10, B10 contient =B9, et non =A3.

Pour faire rfrence la cellule A3, il faut utiliser une rfrenceabsolue. Elle se prsente sous la forme suivante : $A$3. Si voussaisissez =$A$3 en A4, puis copiez le contenu de la cellule A4 et lecollez en B10, B10 contient =$A$3. Le caractre $ indique que cestbien la colonne A et la ligne 3 auxquelles il est fait rfrence.

Il est possible de combiner des rfrences absolues des colon-nes avec des rfrences relatives des lignes, et vice versa. Ilsagit alors de rfrences mixtes.

Lors de la saisie dune formule, vous pouvez facilement passerdun mode de rfrence lautre laide de la touche [F4] :

1. Double-cliquez sur A10.

2. Positionnez le curseur ct de la rfrence A6 (aprs le 6, parexemple).

3. Appuyez sur [F4]. La rfrence devient $A$6.4. Appuyez une deuxime fois sur [F4]. La rfrence devient A$6.5. Appuyez une troisime fois sur [F4]. La rfrence devient $A6.6. Appuyez encore une fois sur [F4]. La rfrence redevient A6.

Utiliser des rfrences relatives et absolues

Dans un premier temps, vous allez mettre en pratique les rfren-ces absolues et relatives. En ce sens, vous allez crer une feuille decalcul pour dterminer les tarifs de diffrents produits, comptetenu dun taux de remise et dun taux de TVA.

1. En A1, saisissez TARIF.2. En E3, saisissez Taux TVA.


3. En F3, saisissez 19,6%.4. En A5, B5, C5, D5, E5, F5, saisissez respectivement Rfrence,

Libell, P.U. H.T., Remise, P.U. net H.T., P.U. T.T.C..5. Saisissez les diffrentes lignes dexemple :

Tableau 1.2 : Lignes dexemple

Rfrence Libell P.U. H.T. Remise

ABC1 Bloc notes 0,93 15 %ABC2 Enveloppes (500) 11,07 12 %ABC3 Stylo 0,66 25 %ABC4 Gomme 0,76 12 %ABC5 Marqueur 1,65 14 %ABC6 Agrafeuse 9,95 20 %ABC7 Classeur 2,57 33 %ABC8 Surligneur 0,66 25 %

6. Slectionnez A1:F1 et cliquez sur Fusionner et centrer (ongletAccueil, groupe Alignement). Appliquez une taille de police de16 et mettez le texte en gras.

7. Slectionnez E3:F3 et appliquez un contour de type quadrillage.Mettez E3 en gras.

8. Slectionnez A5:F5, centrez le texte et mettez-le en gras.

9. Slectionnez A5:F13 et appliquez un contour de type qua-drillage.

Slection de la zone couranteDans Excel, une zone est une plage de cellules spares des autres

par des cellules vides. Ainsi, dans la feuille que vous tes en traindlaborer se trouvent trois zones : A1:F1, E3:F3 et A5:F13. Pour slec-tionner rapidement la zone laquelle appartient la cellule active, ap-puyez sur [Ctrl]+[*]. Ici, slectionnez A5 (par exemple) et appuyez sur[Ctrl]+[*] pour slectionner A5:F13.

10.Slectionnez C6:C13, ainsi que E6:F13 et appliquez le formatMontaire.


Cette feuille prsente plusieurs articles avec, pour chacun deux,son prix HT ainsi quun taux de remise. Le but est de calculer, pourchaque article, le prix net HT (cest--dire compte tenu de laremise) et le prix TTC.

Pour calculer le prix net, il faut chaque ligne appliquer le taux deremise :

1. En E6, saisissez =C6*(1D6).2. tendez le contenu, laide de la poigne de recopie, jusquen

E13. Pour cela, slectionnez la cellule E6 et approchez le poin-teur de la souris de la poigne de recopie de la cellule. Lapoigne de recopie est le petit carr noir situ dans langleinfrieur droit. Le pointeur change dapparence : il se trans-forme en une petite croix noire.

3. Cliquez prsent sur la poigne de recopie et dplacez lepointeur jusquen E13. La plage E6:E13 est entour duncontour gris. Relchez le bouton de la souris. La formule decalcul de la cellule E6 a t tendue aux autres cellules de laplage.

Extension rapide dune formule dans une colonnePour tendre encore plus rapidement une formule dans une colonne,

slectionnez la cellule qui contient la formule tendre et double-cliquezsur la poigne de recopie de cette cellule. La formule est alors tendue

Figure 1.7 : La feuille de calcul initiale

Figure 1.8 : Le pointeur change dapparence


jusqu la ligne correspondant la dernire cellule non vide descolonnes immdiatement adjacentes. Ainsi, si vous double-cliquez

sur la poigne de recopie de la cellule E6, sa formule est tenduejusquen E13, puisque la colonne D contient des valeurs jusquen D13.

Vous avez ainsi mis profit les proprits des rfrences relati-ves : elles localisent les cellules par rapport la cellule active. Entendant le contenu de la cellule active celles situes dans lamme colonne, vous avez fait suivre les rfrences relativesde la formule.

Il reste maintenant calculer les prix TTC. Pour cela, il faut utiliserle taux de TVA situ dans la cellule F3 :

1. En F6, saisissez =E6*(1+F3).2. tendez le contenu, laide de la poigne de recopie, jusquen

F13.

Le rsultat nest pas trs probant. Vous avez sans doute djidentifi les causes du problme : la rfrence F3 doit treabsolue, puisque ce taux doit tre utilis de la mme faon chaque ligne.

1. Double-cliquez sur F6.

2. Positionnez le curseur ct de la rfrence F3 (aprs le 3, parexemple).

3. Appuyez sur [F4]. La rfrence devient $F$3.4. Validez par [].

Figure 1.9 : Premire tentative de calcul du prix TTC


5. tendez le contenu, laide de la poigne de recopie, jusquenF13.

Cette deuxime tentative est sans doute plus conforme vosattentes.

Lemploi de rfrences relatives ou absolues est principalementconditionn par le comportement attendu de la formulelorsquelle sera copie. En effet, une formule est rarement iso-le , elle fait souvent partie de lignes ou de colonnes prsentantdes formules semblables, obtenues par recopie dune formuleinitiale.

Utiliser des rfrences mixtes

Pour mettre en pratique les rfrences mixtes, vous allez cons-truire une feuille de calcul qui permet de dterminer la capacit deproduction dun atelier, en fonction de la capacit horaire dechaque machine et de la dure douverture journalire de latelier.

1. En A1, saisissez Capacits de production Atelier XXXX.2. En C3, saisissez Lundi et tendez le contenu de la cellule

jusquen I3.

3. En B4, saisissez Dure du travail.4. En C4, D4, E4, F4, G4, H4, I4, saisissez respectivement 8, 10, 10,

10, 8, 6, 0.5. En A6, saisissez Machine.6. En B6, saisissez Capacit.7. En C6, saisissez Lundi et tendez le contenu de la cellule

jusquen I6.

Figure 1.10 : Deuxime tentative de calcul du prix TTC


8. En J6, saisissez Total.9. En A7, saisissez Machine 1 et tendez le contenu de la cellule

jusquen A12.

10.En A13, saisissez Total / jour.11.En B7, B8, B9, B10, B11, B12, saisissez respectivement 100, 150,

75, 98, 102, 123.12.Slectionnez A1:J1 et cliquez sur Fusionner et centrer (onglet

Accueil, groupe Alignement). Appliquez une taille de police de16 et mettez le texte en gras.

13.Slectionnez C3:I3 et appliquez un contour de type quadrillage.Mettez le texte en gras.

14.Slectionnez B4:I4 et appliquez un contour de type quadrillage.Mettez B4 en gras.

15.Slectionnez A6:J6, centrez le texte et mettez-le en gras.

16.Slectionnez A6:J13 et appliquez un contour de type qua-drillage.

17.A laide du bouton Somme automatique (onglet Formules)positionnez les totaux de lignes et de colonnes.

18.Slectionnez C13:J13 et mettez le texte en gras.

19.Slectionnez J7:J12 et mettez le texte en gras.

Pour plus dinformations sur le bouton Somme automati-que, reportez-vous au chapitre Calculer et dnombrer.

Ainsi, le lundi, la capacit de production de la machine 1 est de100 8, soit 800 pices. Vous gnraliserez ce calcul lensembledes machines, pour chacun des jours de la semaine.

Figure 1.11 : La feuille de calcul initiale


1. En C7, saisissez =$B7*C$4.2. tendez le contenu, laide de la poigne de recopie, jusquen

C12.

3. tendez le contenu, laide de la poigne de recopie, jusqu lacolonne I.

En saisissant une formule, vous avez pu en crer quarante-deux(6 7) par simple copie. Il est important de rflchir, lors de laconception des formules, lintrt de figer ou non la rfrence la ligne ou la colonne. En figeant la rfrence la colonne B et enlaissant la ligne libre, vous demandez la formule daller chercherla valeur de la capacit horaire de chaque machine, quel que soitle jour de la semaine. De mme, en figeant la rfrence la ligne 4et en laissant la colonne libre, vous autorisez la formule allerchercher la dure douverture de latelier pour chacun des jours,quelle que soit la machine considre.

Rendre une formule plus lisible ?Lorsquune formule devient complexe, elle peut vite se rvler in-

comprhensible et peu lisible. Pour arer la prsentation dune formule,insrez des sauts de ligne avec [Alt]+[] pendant la saisie.

Rfrences tridimensionnellesLes rfrences employes jusqu prsent permettent de situerune cellule dans une feuille de calcul. Pour cela, deux coordon-nes sont ncessaires : la colonne et la ligne. Ce type de rep-

Figure 1.12 : Calcul des capacits

Figure 1.13 : Une formule complexe mise en forme


rage est donc bidimensionnel. Or, il peut tre utile, dans certainessituations, de faire appel des cellules dautres feuilles de calculdu mme classeur. Pour reprer ces cellules, il faut introduire une troisime dimension , en loccurrence le nom de la feuille decalcul source .

1. Dans le classeur que vous venez de crer, slectionnez uneautre feuille (ou insrez-en une).

2. Slectionnez la cellule A3.

3. Saisissez =.4. Cliquez sur longlet de la feuille o se trouve le tableau conte-

nant les donnes que vous souhaitez exploiter (dans notreexemple, il sagit de la feuille Mixtes).

5. Slectionnez par exemple la cellule J13. Vous pouvez voir lecontenu de la cellule active dans la barre de formule.

6. Saisissez /7.7. Validez par [].

Vous obtenez sur la feuille la moyenne des capacits journaliresde production.

La syntaxe dune rfrence tridimensionnelle est la suivante :Feuille!Rfrence. Si le nom de la feuille contient des espaces, ilest entour dapostrophes, par exemple : Ventes Annuelles!B8.

Il est bien entendu possible de combiner les rfrences tridimen-sionnelles avec les rfrences relatives, absolues et mixtes.

Plages de cellules tridimensionnelles

Vous pouvez faire rfrence des plages tridimensionnelles .Par exemple, la formule suivante permet de calculer la somme des

Figure 1.14 : Utilisation de rfrence tridimensionnelle


cellules des plages A1:C3 des feuilles Feuil1 Feuil5 :=SOMME(Feuil1:Feuil5!A1:C3).

Pour plus dinformations sur lutilisation de la fonctionSOMME, reportez-vous au chapitre Calculer et dnombrer.

Pour crer une telle formule :

1. Saisissez =SOMME( dans la cellule de votre choix.2. Cliquez sur longlet de la premire feuille, ici Feuil1.

3. Maintenez la touche [Maj] enfonce et cliquez sur longlet de ladernire feuille, ici Feuil5.

4. Slectionnez ensuite la plage souhaite (ici A1:C3) dans lafeuille active.

5. Fermez la parenthse et validez par [].

Rfrences externesIl peut galement tre ncessaire davoir recours des cellules setrouvant dans dautres classeurs.

Pour illustrer cette possibilit, enregistrez le classeur contenant letableau des capacits de production en lui donnant le nom Capa-cit_Prod.xlsx.

1. Crez un nouveau classeur.

2. En A3, saisissez =.3. Dans longlet Affichage, cliquez sur le bouton Changement de

fentre du groupe Fentres, puis slectionnez Capacit_Pro-d.xlsx.

4. Slectionnez la cellule J13.

5. Validez par [].

Figure 1.15 : Utilisation de rfrence externe


La syntaxe dune rfrence externe est la suivante : [Nom duclasseur]Feuille!Rfrence.

Par dfaut, il sagit dune rfrence absolue, mais il est tout faitpossible de combiner les rfrences externes avec les rfrencesrelatives et mixtes.

Si vous fermez le classeur source, vous constatez que la rfrenceexterne fait apparatre le chemin complet du classeur source. Vouspouvez afficher lensemble des rfrences externes dun classeurgrce au bouton Modifier les liens daccs du groupe Connexionsde longlet Donnes. Il provoque laffichage de la bote de dialo-gue Modifier les liaisons.

Lorsque vous ouvrez un classeur contenant des rfrences exter-nes, Excel vous demande sil doit mettre jour les liaisons.

1.4. Dcouvrir des outils et paramtressupplmentaires

Cette section aborde quelques fonctions intressantes et souventinexploites.

Transformer une formule en valeurPour transformer une formule en valeur, cest--dire remplacerdans la cellule la formule par son rsultat, slectionnez la cellule,cliquez dans la barre de formule et appuyez sur [F9].

Figure 1.16 : La bote de dialogue Modifier les liaisons


En slectionnant une partie de la formule et en appuyant sur [F9],vous transformez uniquement la partie de la formule slectionneen valeur ( condition que cette fraction de formule soit coh-rente).

viter quExcel recalcule systmatiquementles formulesPar dfaut, Excel recalcule les formules chaque modification dela feuille de calcul. Cette option peut tre gnante si, par exemple,vous saisissez un grand nombre de formules, car le calcul prendraalors un certain temps. Durant la conception de la feuille, il nestsans doute pas ncessaire davoir en temps rel la valeur desformules.

Voici la procdure pour empcher Excel de calculer systmatique-ment les formules chaque modification :

1. Cliquez sur le menu Fichier, puis sur Options.

2. Cliquez sur Formules.

3. Dans la rubrique Mode de calcul, slectionnez Manuellement.

Loption Recalculer le classeur avant de lenregistrer permet derendre systmatique le calcul des formules avant lenregistre-ment, afin de sauvegarder les donnes les plus jour.

4. Validez par OK.

Dsormais, Excel ne calculera plus les formules mais afficheraCalculer dans la barre dtat lorsquun recalcul sera ncessaire.Pour calculer les formules la demande dans tous les classeursactifs, appuyez sur la touche [F9] ou utilisez le bouton Calculermaintenant de du groupe Calcul de longlet Formules. Le boutonCalculer la feuille permet de recalculer seulement la feuille active.

Figure 1.17 : Recalcul sur ordre

311.4. Dcouvrir des outils et paramtres supplmentaires

1.5. Ne pas afficher les formulesSi, pour des raisons de confidentialit, vous ne souhaitez pas queles utilisateurs de vos feuilles de calcul puissent visualiser lesformules cres, vous pouvez faire en sorte quelles ne saffichentpas, mme lorsque les cellules qui les contiennent sont slection-nes.

1. Slectionnez la plage de cellules qui contient les formules masquer.

2. Cliquez du bouton droit sur la plage de cellules slectionne.

3. Dans le menu qui saffiche, slectionnez Format de cellule.

4. Dans la bote de dialogue Format de cellule, slectionnez lon-glet Protection.

5. Cochez la case Masque et validez par OK.

6. Dans longlet Accueil, cliquez sur le bouton Format du groupeCellules. Slectionnez ensuite Protger la feuille. Vous pouvez

Figure 1.18 : Longlet Protection de la bote de dialogue Format de cellule


galement utiliser le bouton Protger la feuille du groupeModifications de longlet Rvision.

7. Saisissez ventuellement un mot de passe et validez.

viter les incohrences daffichage duesaux arrondisLe prcepte bien connu qui dit que larrondi de la somme nestpas gal la somme des arrondis peut rendre certaines feuillesde calcul incohrentes, du moins en apparence.

Dans cette feuille de calcul, le total semble incohrent, en effet,15,2 + 12,2 = 27,4 et non 27,5. Or le calcul rel est en fait 15,24 +12,24 = 27,48. Mais le format daffichage choisi ne permet laffi-chage que dune seule dcimale, donc 15,24 devient 15,2, 12,24devient 12,2 et 27,48 devient 27,5, do lincohrence apparente.

Laffichage dun nombre restreint de dcimales na pas dimpactsur le nombre stock dans la cellule. Toutes ses dcimales sontprises en compte dans les calculs.

Pour remdier ce problme, procdez de la faon suivante :


2. Cliquez sur Options avances.

3. Dans la rubrique Lors du calcul de ce classeur, slectionnezDfinir le calcul avec la prcision du format affich.

4. Validez par OK.

Figure 1.19 : Un calculincohrenten apparence

Figure 1.20 : Un rsultat cohrent

331.5. Ne pas afficher les formules

Le rsultat est maintenant cohrent. Soyez toutefois prudent lorsde lutilisation de cette option car les dcimales non affiches sontirrmdiablement perdues.

Afficher des rfrences du type L1C1Il est possible dutiliser un autre type de rfrences de cellules quecelui employ dans ce chapitre. Dans cet autre type de rfrence,la cellule A1 est dsigne par L1C1, la cellule P12 par L12C16Une rfrence relative est reprsente par exemple par L(-1)C(2),qui correspond la cellule situe une ligne au-dessus et deuxcolonnes droite. Ce type de rfrence est hrit de tableurs plusanciens.

Pour utiliser ce type de rfrences :


2. Cliquez sur Formules.

3. Dans la rubrique Manipulation de formules, slectionnez Stylede rfrence L1C1.

4. Validez par OK.


CHAPITRE2

UTILISERDESNOMSDANSLES FORMULESAttribuer simplement un nom une cellule ou une plagede cellules ........................................................................................... 37Dfinir et modifier les noms ........................................................... 41Attribuer des noms des constantes et des formules ........ 49Noms spcifiques dune feuille de calcul ................................... 51

35

Les noms permettent de rendre vos formules plus lisibles et com-prhensibles. Cela peut savrer trs utile la fois pour vous, carvous pourrez plus facilement vous replonger dans vos formulesafin de les modifier et de les amliorer, et pour les utilisateurs devos feuilles de calcul, car ils comprendront mieux la logique devos calculs sans avoir entrer dans les arcanes de vos formules !

Il est possible, entre autres, de nommer des cellules individuellesou des plages de cellules.

Les noms sont un mode de reprage plus convivial que les rf-rences classiques . Nous allons prsent aborder les fonction-nalits qui vont permettre de crer, de modifier et de supprimerdes noms dans un classeur ou une feuille de calcul.

2.1. Attribuer simplement un nom une cellule ou une plage de cellules

Pour illustrer lattribution dun nom une cellule, nous utiliseronsune feuille de calcul qui est en fait lextrait dun tarif de diffrentsproduits.

Pour avoir plus de prcision sur la cration et les formulesde cette feuille, reportez-vous au chapitre laborer des for-mules simples.

Attribuer un nom une celluleVous allez, par exemple, attribuer le nom TauxTVA la cellule F3 :

1. Slectionnez F3.

2. Dans la zone Nom (qui contient la rfrence de la cellule F3),saisissez TauxTVA.

3. Validez par [].

Figure 2.1 : Attribution dun nom la cellule F3


Lorsque vous slectionnez la cellule F3, le nom TauxTVA apparatdans la zone Nom.

Si vous saisissez TauxTVA dans la zone Nom alors quune celluleest slectionne, la slection est dplace sur la cellule F3.

Rgles pour la saisie de nomsLe premier caractre dun nom doit tre une lettre ou un caractre de

soulignement. Les autres caractres peuvent tre des lettres, des nom-bres, des points et des caractres de soulignement.

Les noms ne peuvent tre identiques des rfrences de cellules, tellesque A10 ou $B$12.

Vous pouvez utiliser des caractres de soulignement ou des pointscomme sparateurs de mots, par exemple Taux.TVA ou Taux_TVA.

Un nom peut compter jusqu 255 caractres. Si un nom attribu uneplage contient plus de 253 caractres, vous ne pouvez le slectionnerdans la zone Nom.

Les noms peuvent contenir des majuscules et des minuscules. Excel nefait pas de distinction de casse, cest--dire quil ne distingue pas lesmajuscules des minuscules dans les noms. Par exemple, si vous avezcr le nom TAUX puis cr lautre nom Taux dans le mme classeur, lesecond nom remplace le premier.

vitez dutiliser les noms suivants, rservs par Excel : Zone_d_impres-sion, Impression_des_titres, Titre_de_la_feuille, Zone_de_consolidation,Base_de_donnes ainsi que FilterDatabase.

Utiliser un nom dans une formuleUne fois que le nom est cr, vous pouvez lutiliser dans toutesvos formules. Un nom constitue une rfrence absolue.

Vous allez recrer les formules de calcul du prix TTC.

1. En F6, saisissez =E6*(1+t. Ds que vous avez saisi la lettre t ,Excel affiche une liste droulante permettant de choisir lesfonctions dont le nom dbute par t, mais vous pouvez constaterque le nom que vous avez cr figure galement dans cetteliste. Il est prcd dun symbole diffrent pour le distinguerdes fonctions. Vous pouvez continuer la saisie du nom ou leslectionner dans la liste en effectuant un double-clic.

38 2. Utiliser des noms dans les formules

2. Validez par [].

3. tendez le contenu, laide de la poigne de recopie, jusquenF13.

Vous pouvez ainsi vrifier quun nom est une rfrence absolue,puisque sur chaque ligne, TauxTVA fait toujours rfrence lacellule F3.

Attribuer un nom une plage de cellulesNous avons vu comment attribuer un nom une cellule. De lamme faon, il est possible dattribuer un nom une plage decellules.

1. Slectionnez F6:F13.

2. Dans la zone Nom (qui contient la rfrence de la cellule F6),saisissez PU_TTC.

3. Validez par [].

Pour slectionner la plage de cellules, il est prsent possible desaisir le nom dans la zone Nom ou dutiliser la liste droulante quiapparat lorsque vous cliquez sur le bouton flch situ ct decette zone.

Figure 2.2 : Saisie de la formule avec un nom de cellule

Figure 2.3 : Liste desnoms


Slection des plages de cellules nommesSi vous ne slectionnez quune partie dune plage de cellules nom-

me, son nom napparat pas dans la zone Nom. Pour que le nomapparaisse, il faut que la plage soit slectionne dans son intgralit.

Le nom dune plage de cellules peut tre utilis dans une formule, condition videmment que la formule ncessite un argumentqui soit une plage de cellules. Par exemple, si vous saisissez=Max(PU_TTC) dans la cellule F14, vous obtenez 11,65, ce qui est lersultat correct.

Plus gnralement, un nom de plage peut tre utilis dans toutefonction qui requiert une plage de cellules comme argument.

Pour plus dinformations sur les fonctions et leurs argu-ments, reportez-vous au chapitre Rechercher et utiliser desfonctions.

Slectionner une cellule ou une plagenommePour slectionner une cellule ou une plage nomme, nous avonsvu quil tait possible dutiliser la liste droulante de la zone Nom.Il est galement envisageable dutiliser le bouton Rechercher etslectionner du groupe Edition de longlet Accueil.

1. Dans longlet Accueil, cliquez sur le bouton Rechercher etslectionner du groupe Edition et slectionnez la commandeAtteindre..

2. Dans la bote de dialogue Atteindre, slectionnez le nom dsir(voir Figure 2.4).

3. Validez par OK.


2.2. Dfinir et modifier les nomsVous avez pu mettre en pratique une mthode rapide et simplepour attribuer rapidement un nom de plage ou de cellule. Toute-fois, il existe une autre mthode qui offre davantage de possibili-ts. Pour la mettre en uvre, nous utiliserons les boutons dugroupe Noms dfinis de longlet Formules.

Dfinir un nomLavantage du bouton Dfinir un nom du groupe Noms dfinis delonglet Formules rside dans la prise en compte des cellulesadjacentes la cellule ou la plage slectionne pour proposer unnom.

1. Slectionnez la plage de cellules E6:E13.

2. Dans longlet Formules, cliquez sur le bouton Dfinir un nomdu groupe Noms dfinis.

Excel vous propose (mais vous pouvez le modifier), en guise denom de plage, ltiquette de colonne du tableau. Les espaces ont

Figure 2.4 : La bote dedialogue Atteindre

Figure 2.5 : La bote dedialogue Nouveau nom

412.2. Dfinir et modifier les noms

t remplacs par des tirets. La zone Fait rfrence contient lesrfrences (absolues) de la plage de cellules. Il est possible de lesmodifier soit en saisissant des rfrences dans cette zone, soit encliquant dans la zone puis en allant slectionner la plage dsire laide de la souris.

3. Cliquez sur OK.

Vous pouvez galement nommer des plages de cellules non conti-gus, que vous slectionnerez en utilisant la touche [Ctrl].

Noms de plages tridimensionnelles Il est possible dattribuer un nom une plage tridimensionnelle

en saisissant par exemple =Feuil1:Feuil2!$A$6:$F$13 dans lazone Fait rfrence . Cela signifie que le nom est attribu la plagecompose des plages A6 :F13 des feuilles Feuil1 et Feuil2.

Modifier la cible dun nomVous pouvez utiliser le gestionnaire de noms pour modifier lacellule ou la plage de cellules associe au nom :

1. Dans longlet Formules, cliquez sur le bouton Gestionnaire denoms du groupe Noms dfinis.

Afficher rapidement le gestionnaire de nomsPour afficher rapidement le gestionnaire de noms, utilisez la combi-

naison de touches [Ctrl]+[F3].

2. Slectionnez P.U._net_H.T.

3. Cliquez sur Modifier (voir Figure 2.6).

4. Vous pouvez prsent modifier la plage de cellules associedans la zone Fait rfrence . Saisissez par exemple $E$15 laplace de $E$13. Validez par OK.

5. Cliquez sur Fermer pour quitter le gestionnaire de noms.

Dans le gestionnaire de noms, vous pouvez crer une nouvelleplage nomme en cliquant sur le bouton Nouveau.


Impact de la suppression dune feuilleSi vous supprimez une feuille de calcul qui contient des cellules ou

des plages nommes et utilises par ailleurs, les noms demeurentprsents, mais leur rfrence nest plus correcte. En effet, le nom de lafeuille est remplac par #REF car cette dernire nexiste plus. Si vousutilisez ce nom dans une formule, le rsultat est le message derreur#REF!, car la rfrence lie au nom est introuvable.

Insrer un nom dans une formulePour insrer un nom dans une formule, la mthode la plus simpleconsiste saisir le nom au clavier, comme vous lavez fait avec lecalcul du prix TTC dans lun des exemples prcdents. Si votreclasseur contient un grand nombre de noms, vous ne les aurezpeut-tre pas tous en tte et un aide-mmoire sera sans doute lebienvenu.

1. Slectionnez la cellule F6.

Figure 2.6 : Le gestionnaire de noms


2. Saisissez =E6*(1+.3. Cliquez sur le bouton Utiliser dans la formule du groupe Noms

dfinis de longlet Formules.

4. Dans la liste, slectionnez TauxTVA.

5. Validez par OK.

6. Saisissez la parenthse fermante.

7. Appuyez sur [].

Coller la liste des nomsDans les feuilles de calcul qui contiennent un grand nombre denoms, il peut tre intressant de crer une liste des noms ainsi quedes plages auxquelles ils font rfrence.

1. Slectionnez une autre feuille du classeur.

2. Slectionnez la cellule A5.

3. Cliquez sur le bouton Utiliser dans la formule du groupe Nomsdfinis de longlet Formules.

4. Slectionnez Coller, puis cliquez sur le bouton Coller une listedans la bote de dialogue Coller un nom.

Figure 2.7 : La liste des noms

Figure 2.8 : La liste des noms


Crer des sries de nomsPour illustrer cette fonctionnalit dExcel, nous utiliserons le clas-seur Capacit_Prod.xlsx.

Pour avoir plus de prcision sur la cration de ce classeur etles formules quil contient, reportez-vous au chapitre labo-rer des formules simples.

Vous allez nommer toutes les lignes et colonnes du tableau enutilisant les tiquettes de lignes et de colonnes. Il est parfaitementenvisageable de slectionner successivement chacune des pla-ges, puis dutiliser le gestionnaire de noms. Cela risque toutefoisde savrer fastidieux. Heureusement, Excel a prvu une fonctionqui permet dautomatiser ce traitement.

1. Slectionnez A6 :J13.

2. Cliquez sur le bouton Crer partir de la slection du groupeNoms dfinis de longlet Formules.

3. Dans la bote de dialogue Crer des noms partir de la slec-tion, slectionnez Ligne du haut et Colonne de gauche.

4. Validez par OK.

Si vous cliquez sur le bouton flch situ ct de la zone Nom,vous constatez que des noms ont t crs. Chaque ligne estidentifie par ltiquette de ligne correspondante et chaque co-lonne par ltiquette de colonne correspondante (voir Figure 2.10).

Si vous slectionnez Machine_1, la slection active est dplacesur la plage de cellules B7 :J7 (voir Figure 2.11).

Figure 2.9 : La bote dedialogue Crer des noms


Reprer une cellule lintersection de plages nommesIl est possible dsigner une cellule en tant quintersection de plages

nommes. Ainsi, si vous saisissez dans une cellule =Mardi Machine_3,vous obtenez 750, ce qui correspond au contenu de la cellule D9, situe lintersection de la plage nomme Mardi et de la plage nommeMachine_3. Lespace entre Mardi et Machine_3 correspond en fait loprateur dintersection.

Supprimer un nomPour supprimer un nom, il faut utiliser nouveau le gestionnairede noms :


2. Slectionnez le nom que vous souhaitez supprimer.

3. Cliquez sur Supprimer.

4. Validez par OK.

Impact de la suppression dun nomLa prudence est requise lors de la suppression dun nom. En effet,

toutes les formules y faisant rfrence produiront le message derreur#NOM?.

Figure 2.10 : La liste des noms intgrant lesnoms crs automatiquement

Figure 2.11 : La plage Machine_1


Ainsi, si vous supprimez le nom TauxTVA dans la feuille de calculdes tarifs, la colonne contenant jusqualors les prix TTC naffiche plus

que #NOM?. Vous pouvez annuler la suppression du nom laide dubouton Annuler.

Remplacer systmatiquement les rfrencesde cellules par les nomsSupposons que, lors de la conception dune feuille de calcul, vousnayez pas utilis ds le dbut de votre travail des cellules nom-mes, mais plutt des rfrences classiques . Une fois lesnoms dfinis, vous souhaitez quils remplacent les rfrencesdans les formules dj saisies. Excel a prvu une solution.

Reprenez la feuille de calcul des tarifs :

1. Dans longlet Formules, cliquez sur le bouton flch situ ctde Dfinir un nom du groupe Noms dfinis. SlectionnezAppliquer les noms

2. Dans la zone Affecter le(s) nom(s), slectionnez TauxTVA. Vouspouvez slectionner plusieurs noms si vous le souhaitez. Pourdslectionner un nom, cliquez dessus nouveau.

Figure 2.12 : Impact dela suppression du nomTauxTVA

Figure 2.13 : La bote dedialogue Affecter un nom


3. Validez par OK.

Dans toutes les formules qui contenaient la rfrence $F$3, celle-cia t remplace par TauxTVA.

Examinons maintenant en dtail les options de cette bote dedialogue :

j Ignorer relatif/absolu : si cette case est slectionne, Excelconsidre que les rfrences $F$3, $F3, F$3 et F3 sont quiva-lentes et les remplace, dans notre exemple, par TauxTVA.

j Utiliser les noms de colonnes et de lignes : si cette case estslectionne, les rfrences classiques sont remplaces pardes noms de plages. Ainsi, dans notre exemple, la rfrence E9de la feuille de calcul des capacits des machines est remplacepar Machine_3 Mercredi.

En cliquant sur le bouton Options, vous pouvez afficher les para-mtres supplmentaires suivants :

j Ignorer nom de colonne si mme colonne : lorsque cette caseest slectionne (elle lest par dfaut), Excel prend en compteles intersections implicites pour les colonnes (mme principeque pour les tiquettes, vues prcdemment).

j Ignorer nom de ligne si mme ligne : lorsque cette case estslectionne (elle lest par dfaut), Excel prend en compte lesintersections implicites pour les lignes.

j Ordre du nom : ces boutons doption permettent de spcifierlordre des noms des lignes et des colonnes lors du remplace-ment des rfrences par des noms.

Figure 2.14 : Les optionssupplmentaires de labote de dialogue Affecterun nom


2.3. Attribuer des noms des constanteset des formules

Jusqu prsent, les noms que nous avons crs faisaient rf-rence de faon absolue des cellules ou des plages de cellules.Cest le cas dutilisation le plus frquent, mais ce nest pas le seul.Il est galement possible dattribuer des noms des constantes et des formules.

Attribuer des noms des constantesReprenez lexemple des tarifs pour dfinir le taux de TVA sans lesaisir dans une cellule.


2. Cliquez sur Nouveau. Dans la bote de dialogue Nouveau nom,saisissez Taux_TVA.dans la zone Nom.

3. Dans la zone Fait rfrence , saisissez 19,6%.

4. Cliquez sur OK.

5. Cliquez sur Fermer.

Si vous saisissez =Taux_TVA dans une cellule, la valeur 0,196 saf-fiche. Vous pouvez bien entendu utiliser ce nom dans nimportequelle formule, au mme titre que les noms que vous avez djcrs.

Figure 2.15 : Attribution dun nom une constante

492.3. Attribuer des noms des constantes et des formules

Attribution dun nom une constante textePour attribuer un nom la chane de caractres Excel 2007, saisis-

sez ="Excel 2007" dans la zone Fait rfrence .

Attribuer des noms des formulesIl est galement possible, comme nous lavons voqu, dattri-buer un nom une formule de calcul. Par exemple, nous allonsdfinir une formule de calcul permettant de calculer le prix TTC partir du prix HT laide du taux de TVA que nous avons cr sousforme de constante.

1. Slectionnez G6.


3. Cliquez sur Nouveau. Dans la bote de dialogue Nouveau nom,saisissez Calcul_prix.dans la zone Nom.

4. Dans la zone Fait rfrence , saisissez =AbsoluesRelatives!E6*(1+Taux_TVA).

5. Cliquez sur OK.

6. Cliquez sur Fermer.

7. En G6, saisissez =Calcul_prix.8. tendez le contenu, laide de la poigne de recopie, jusquen

G13.

Figure 2.16 : Attribution dun nom une formule


Le calcul seffectue correctement. Il est trs important, puisque ladfinition se fait de faon relative, de slectionner au pralable lacellule G6. En fait, la formule utilise pour le calcul la cellule situesur la mme ligne mais deux colonnes gauche. Si vous saisissez=Calcul_prix dans une autre colonne, le rsultat est faux. Pourremdier ce problme, remplacez E6 par $E6 dans la dfinition dela formule. Ainsi vous obtiendrez un rsultat correct, quelle quesoit la colonne dans laquelle vous saisirez la formule =Calcul_prix.

Liste droulante des nomsLes noms faisant rfrence des constantes ou des formules

napparaissent pas dans la liste droulante de la zone Nom.

2.4. Noms spcifiques dune feuillede calcul

Jusqu prsent, il na pas t question de la porte des noms quevous avez crs. Ceux-ci sont valides dans tout le classeur. Ainsi,si vous slectionnez le nom TauxTVA dans la liste des noms alorsque la feuille Feuil1 nest pas affiche, la feuille Feuil1 sera active.Toutefois, il est possible de dfinir des noms valables uniquementdans une feuille dfinie.

Vous allez transformer le nom TauxTVA en nom local relative-ment la feuille Feuil1.


2. Slectionnez TauxTVA. Cliquez sur Supprimer. Il nest en effetpas possible de modifier la porte dun nom existant.

3. Cliquez sur Nouveau.

4. Dans la bote de dialogue Nouveau nom, saisissez TauxTVA dansla zone Nom .

5. Slectionnez Feuil1 dans la liste droulante Zone.

6. Cliquez dans la zone Fait rfrence puis slectionnez la celluleF3 de la feuille Feuil1.

512.4. Noms spcifiques dune feuille de calcul

7. Cliquez sur OK.

Le nom TauxTVA nest dsormais disponible dans la liste drou-lante des noms que dans la feuille Feuil1.

Copie de feuilles de calculLorsque vous copiez une feuille de calcul qui contient des noms

locaux au sein dun mme classeur, la feuille rsultante contient lesmmes noms locaux. Si, dans le classeur, un nom fait rfrence unecellule ou une plage de cellules de la feuille que vous copiez, ce nomdevient un nom local dans la feuille rsultante.

De mme, lorsque vous copiez une feuille dans un autre classeur, tousles noms locaux ou globaux faisant rfrence des cellules de la feuillecopie, sont crs dans le classeur cible .

Soyez donc vigilant lorsque vous copiez des feuilles, sous peine de neplus vous y retrouver entre les noms locaux et globaux !

Figure 2.17 : Modification de la porte dun nom


CHAPITRE34

RECHERCHERETUTILISERDES FONCTIONSComprendre la notion de fonction ............................................... 55Utiliser les diffrents types darguments .................................... 63Connatre les diffrentes catgories de fonctions .................... 65

53

Nous avons abord les grands principes de conception des for-mules de calcul. Vous pouvez donc ds maintenant mettre profitces connaissances pour construire les formules de calcul adap-tes vos besoins. Une bonne dfinition du problme rsoudre,un peu de rflexion, voire dastuces, vous feront sans difficultparvenir vos fins. Dautant que Excel a peut-tre dj rsolu pourvous certaines difficults. En effet, le logiciel propose plus de troiscents fonctions de calcul.

3.1. Comprendre la notion de fonctionLes fonctions sont des formules prdfinies qui effectuent descalculs ou des traitements partir de donnes que vous leurfournissez. Elles vous vitent de rinventer la roue en cas debesoin. En effet, pour calculer la somme des cellules de C1 C10,vous pourriez trs bien crire =C1+C2+C3+C4+C5+C6+C7+C8+C9+C10.Cela fonctionne parfaitement. Mais vous trouverez sans douteplus pratique dcrire =SOMME(C1:C10) ! Et sans doute encore pluspratique si vous devez calculer la somme des cellules de C1 C1000 !

La fonction SI, sans doute parmi les plus utilises, permet de btirdes formules dites conditionnelles, cest--dire qui vont tre mo-difies en fonction dune condition. Par exemple, si le dlai depaiement dune facture est dpass, la formule conditionnelleaffiche un message dalerte. Cette formule pourrait avoir lalluresuivante (si la date de rglement se trouve dans la cellule B5) :=SI(AUJOUDHUI()>B5;"Le dlai est dpass";"Facture r-gler").

Pour plus dinformations sur la fonction SI, reportez-vous auchapitre Utiliser la fonction SI.

Au passage, remarquez lutilisation de la fonction AUJOURDHUI(),qui renvoie la date du jour.

Les fonctions dExcel ne sont pas exclusivement destines aucalcul numrique. Elles traitent de domaines larges et varis. Lelogiciel propose en effet :

j des fonctions de recherche et de rfrence ;

553.1. Comprendre la notion de fonction

j des fonctions de texte ;

j des fonctions de date et dheure ;

j des fonctions logiques ;

j des fonctions dinformation ;

j des fonctions de base de donnes ;

j des fonctions mathmatiques ;

j des fonctions statistiques ;

j des fonctions financires ;

j des fonctions dingnierie.

Pour donner des rsultats, la plupart des fonctions ncessitentque vous leur fournissiez des donnes pour travailler. Ces don-nes sont appeles des arguments. Ainsi, une fonction qui calculeune mensualit demprunt a besoin du taux de lemprunt, dumontant emprunt et de la dure de lemprunt.

Les arguments doivent figurer aprs lintitul de la fonction, entreparenthses et spars par des points-virgules. Il est impratif derespecter leur ordre, car, en rgle gnrale, chacun dentre eux aun rle spcifique. Il est galement ncessaire de veiller au typedargument demand (valeurs numriques, chanes de caractres,dates) sous peine de voir apparatre des messages derreur telsque #VALEUR!.

Les arguments peuvent tre fournis sous forme de valeur, derfrence une cellule ou plage de cellules, de plage nomme.Ils peuvent tre le rsultat dautres fonctions. Nous dcrironsultrieurement les diffrents types darguments.

Dcouvrir la bibliothque de fonctionsVous allez prsent voir comment insrer une fonction dans uneformule. Bien sr, vous ntes pas cens connatre lensemble desnoms des fonctions! Cest pourquoi nous allons dcrire une m-thode visant identifier la fonction qui rsoudra votre problme.

56 3. Rechercher et utiliser des fonctions

Rechercher et insrer une fonctionSupposons que vous souhaitiez calculer la moyenne de valeursqui se trouvent dans une mme colonne dune feuille de calcul,mais que vous ne connaissiez pas la fonction utiliser.

La faon de procder est la suivante:

1. Slectionnez la cellule dans laquelle vous souhaitez insrer unefonction (en loccurrence B13).

2. Cliquez sur le bouton Insrer une fonction du groupeBibliothque de fonctions de longlet Formules ou cliquez surle bouton Insrer une fonction de la barre de formule.

3. La bote de dialogue Insrer une fonction apparat alors : (voirFigure 3.3)

4. Plusieurs possibilits soffrent vous :

j dcrire ce que vous souhaitez faire dans la zone Recherchezune fonction ;

Figure 3.1 : Calcul dune moyenne en B13

Figure 3.2 : Le bouton Insrer une fonction


j slectionner une catgorie laide de la liste droulanteSlectionnez une catgorie ;

j cliquer dans la zone Slectionnez une fonction et saisir lespremires lettres de la fonction dsire.

Une fois la fonction affiche, cliquez sur le bouton OK.

Dans notre exemple, slectionnez la catgorie Statistiques, Excelvous propose un choix de fonctions plus restreint.

Figure 3.3 : La bote de dialogue Insrer une fonction

Figure 3.4 : Fonctionsproposes


Si vous slectionnez la fonction MOYENNE, vous constatez quExcelaffiche en bas de la bote de dialogue la syntaxe de la fonctionainsi quun bref descriptif.

Il est galement possible daccder laide sur la fonction encliquant sur le lien hypertexte correspondant (Aide sur cette fonc-tion).

Vous pouvez maintenant cliquer sur le bouton OK en bas de labote de dialogue. Excel affiche une nouvelle bote de dialogueintitule Arguments de la fonction.

Excel propose par dfaut la plage B7:B12, ce qui est correct. Encliquant dans la barre de formule, vous pouvez ventuellementcomplter la formule. Dans notre cas, cliquez sur OK pour valider.La fonction a t place dans la cellule B13.

En fait Excel propose comme plage de cellules, la plus grandeplage de cellules contenant des valeurs numriques situe au-dessus de la cellule contenant la fonction ou gauche, sil ny arien au-dessus. Dans ce cas, la plage tait correcte car lentte decolonne tait un texte. Si lentte de colonne avait t un nombre(une anne, par exemple), elle aurait t incluse dans la moyenne,ce qui aurait fauss le rsultat. Considrez donc avec circonspec-tion ce que vous propose Excel!

Figure 3.5 : Arguments de la fonction


Utilisation de la bote de dialogue Insrer une fonction dansune formule

Lorsque vous tes en train de saisir une formule qui fait intervenirplusieurs fonctions, vous pouvez faire apparatre la bote de dialogueInsrer une fonction en cliquant sur le bouton Insrer une fonction de labarre de formule. Ce dernier est en effet encore actif mme en cours desaisie ou ddition de formule.

Saisir une fonction connueAvec la pratique, vous vous apercevrez sans doute que les troiscents fonctions ne vous seront pas toutes utiles. En fait, avec unevingtaine voire une trentaine de fonctions, il est possible de faireface la majorit des situations courantes. Donc, au bout duncertain temps, vous connatrez par cur les fonctions qui voussont utiles et vous trouverez un peu lourd dutiliser la bote dedialogue Insrer une fonction. Rassurez-vous, vous pouvez saisirdirectement les fonctions dans vos formules!

Pour cela, il suffit de saisir lintitul de la fonction (en majusculesou minuscules), puis la liste des arguments entre parenthses,spars par des points virgules. Si la fonction se trouve en dbutdune formule, il faut la faire prcder du signe gal (=).

Les parenthsesMme si la fonction ne requiert pas darguments (ALEA(), AUJOURD-

HUI(),), noubliez pas les parenthses ouvrantes et fermantes. Laprsence de parenthses permet en effet Excel de dtecter que le textesaisi est une fonction et non un nom de cellule dfini par lutilisateur.

Par exemple, saisissez =10+s dans une cellule. Ds que vous avezsaisi la lettre s la liste des fonctions qui dbutent par cettelettre apparat. Slectionnez une fonction pour afficher une info-bulle qui dcrit lobjectif de la fonction (voir Figure 3.6).


Dsactiver la liste de choix des fonctions

Si vous ne souhaitez pas que la liste de choix des fonctions apparaisse,cliquez sur le menu Fichier, puis sur Options. Dans la catgorie Formu-les, dslectionnez la case Saisie semi-automatique de formules de larubrique Manipulation de formules.

Continuez la saisie du nom de la fonction ou slectionnez-la dansla liste en effectuant un double-clic. Ds que vous avez saisi laparenthse ouvrante, une info-bulle apparat, affichant lintitul dela fonction et la liste des arguments de celle-ci. Les argumentsentre crochets sont facultatifs. Si vous cliquez sur lintitul de lafonction dans linfo-bulle, laide relative la fonction sera affiche.

Dtecter les erreurs de saisieSi linfo-bulle napparat pas alors que vous avez saisi la parenthse

ouvrante, cest quExcel na pas "reconnu" la fonction. Il y a donc unetrs forte probabilit pour vous ayez fait une faute de frappe!

Figure 3.6 : La liste de choix des fonctions

Figure 3.7 : Linfo-bulle desarguments


Continuez la formule soit en saisissant les arguments, soit enallant slectionner des plages de cellules dans une feuille decalcul. Si vous cliquez sur la reprsentation du paramtre danslinfo-bulle, vous slectionnez le paramtre correspondant dans laformule.

Terminez la saisie en fermant la parenthse et validez avec [].

Une fois que vous avez valid, le rsultat apparat dans la cellule.Dans la barre de formule, Excel a converti lintitul de la fonctionen majuscules.

Utiliser les bibliothquesDans le groupe Bibliothque de fonctions de longlet Formules,vous disposez de plusieurs boutons qui vous permettent dacc-der aux fonctions classes par thmes : Financier, Texte, Date etheure

Utiliser le bouton Somme automatiqueLe bouton Somme automatique est sans doute lun des boutonsles plus utiliss lors dune sance de travail sur Excel. Le nom estun peu rducteur dans la mesure o ce bouton permet daccderrapidement cinq fonctions.

Le bouton Somme automatique a le don dubiquit !Le bouton Somme automatique est galement disponible dans lon-

glet Accueil, dans le groupe Edition.

Figure 3.8 : Navigation entre les arguments grce linfo-bulle

Figure 3.9 : Accs auxfonctions classes parthmes


Insrer un total

La premire utilisation de ce bouton consiste slectionner unecellule dans laquelle vous souhaitez positionner le total duneligne ou dune colonne, puis cliquer sur Somme automatique.

Il est possible de modifier la plage de cellules propose par dfaut,soit en cliquant dans la barre de formule et en saisissant au clavierla nouvelle plage, soit en la slectionnant laide de la souris.

Insrer dautres fonctions

Le bouton Somme automatique permet daccder rapidement dautres fonctions. Pour cela, il suffit de cliquer sur la petite flchevers le bas qui se trouve en dessous du symbole sigma, puis deslectionner la fonction souhaite.

Loption Autres fonctions ouvre la bote d

Documents

Excel2010 - Guide Complet