Utiliser les formules et les fonctions - · PDF fileUtiliser les formules et les fonctions Introduction Après avoir saisi des données dans une feuille de calcul, vous voudrez ajouter

Utiliser les formules etles fonctions

Introduction

Après avoir saisi des données dans une feuille de calcul, vousvoudrez ajouter des formules pour réaliser des calculs. Micro-soft Excel peut vous aider à obtenir les résultats dont vousavez besoin. Les formules peuvent être très simples ou trèscomplexes. Leur complexité dépend du résultat que vous sou-haitez tirer des données. Par exemple, pour obtenir simple-ment le total des ventes mensuelles, la formule effectuera lasomme des chiffres de vente et produira le résultat. Enrevanche, si vous recherchez les ventes du mois supérieures à100 € effectuées auprès des mêmes clients, il vous faudra plusde temps pour concevoir la formule.

Puisque Microsoft Excel évalue automatiquement les for-mules, la feuille de calcul reste exacte et à jour, quelle que soitla fréquence des modifications des données. Les références decellule absolues permettent d’associer une formule à une cel-lule précise. Excel propose de nombreuses fonctions pourajouter des calculs à une feuille. Des fonctions commeMOYENNE ou SOMME permettent d’effectuer rapidement descalculs.

Pour faciliter la compréhension des formules les plus com-plexes, vous pouvez utiliser des plages nommées. Il est plusfacile de lire une formule qui se fonde sur des plages nomméesque de l’examiner et de tenter de la déchiffrer. L’étude du“flux” d’une formule réduit énormément les risques d’erreurs.Vous pouvez examiner la construction de la formule, un niveauà la fois, en suivant des flèches qui indiquent d’où proviennentles données utilisées. Au cours de l’écriture de la formule, vouspouvez apporter les corrections nécessaires. 3

3Dans ce chapitre

Comprendre les formules

Créer et modifier des formules

Comprendre les références de cellules

Utiliser des références absolues

Utiliser des références mixtes et 3D

Nommer des cellules et des plages

Saisir et gérer des noms

Simplifier une formule avec des plages

Afficher des calculs dans la barre d’état

Calculer des totaux avec Somme auto-matique

Effectuer des calculs une fois

Convertir des formules et des valeurs

Corriger les erreurs de calcul

Corriger des formules

Auditer une feuille de calcul

Repérer les références circulaires

Calculer avec des fonctions

Créer des fonctions et des fonctionsimbriquées

Calculer plusieurs résultats

Utiliser des noms pour des constanteset des fonctions

71

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 71

© 2012 Pearson France – Excel 2010 – Steve Johnson

72 Chapitre 3

IntroductionUne formule emploie des valeurs et retourneun résultat. Dans une feuille de calcul, uneformule peut utiliser des constantes (comme147 ou 10,00 €), des opérateurs, des réfé-rences et des fonctions. Une formule Excelcommence toujours par le signe égal (=).

Une constante est un nombre ou du textenon calculé, comme 147, “Marge nette” ou22/07/2010. À l’opposé, une expression estune valeur non constante. Les constantesrestent les mêmes tant que vous, ou lesystème, ne les modifiez pas. Un opérateur,comme + (signe plus) ou - (signe moins),effectue un calcul. Une référence de celluleest une adresse qui retourne la valeur de lacellule. Par exemple, A1 (colonne A, ligne 1)retourne la valeur de la cellule A1.

Une fonction effectue des calculs prédéfinissur ses arguments. Par exemple, la fonctionSOMME(B1:B10) retourne la somme des cel-lules B1 à B10. Un argument peut être un

nombre, du texte, une valeur logique,comme VRAI ou FAUX, un tableau, unevaleur d’erreur, comme #NA, une référencede cellule, une constante, une formule oud’autres fonctions, appelées fonctions imbri-

quées. Une fonction commence par le signeégal (=), suivi d’un nom, d’une parenthèseouvrante, des arguments séparés par despoints-virgules et une parenthèse fermante.Par exemple, la fonction MOYENNE(A1:A10;B1:B10) renvoie une valeur qui représente lamoyenne du contenu des cellules A1 à A10 etB1 à B10. Pendant la saisie d’une fonction,une info-bulle affiche sa structure et les argu-ments requis. La boîte de dialogue Insérerune fonction peut vous aider à ajouter unefonction dans une formule.

Effectuer des calculsPar défaut, Excel effectue un calcul suite à lamodification d’une valeur, d’une formule oud’un nom. Pour changer ce comportement,cliquez sur l’onglet Formules, sur Options decalcul, puis sur l’option appropriée : Automa-tique, Automatique sauf dans les tables dedonnées ou Manuel. Pour recalculer manuel-lement tous les classeurs ouverts, cliquez surCalculer maintenant (F9). Pour recalculer lafeuille de calcul active, cliquez sur Calculer lafeuille (Maj+F9).

Ordre de prioritéLes formules effectuent les calculs de gaucheà droite, selon l’ordre donné à chaque opéra-teur. Celles qui comprennent plusieurs opé-rateurs suivent un ordre de priorité :exponentiation, multiplication et division,puis addition et soustraction. Dans la formule2 + 5 * 7, Excel effectue la multiplication puisl’addition et obtient 37. Les opérations entreparenthèses sont effectuées en premier. Lerésultat de (2 + 5) * 7 est 49.

Comprendre les formules

Référence Signification

A1 La cellule en colonne A et ligne 1

A1:A10 La plage des cellules en colonne Aet lignes 1 à 10

A1:F1 La plage des cellules en ligne 1 etcolonnes A à F

1:1 Les cellules de la ligne 1

1:5 Les cellules des lignes 1 à 5

A:A Les cellules de la colonne A

A:F Les cellules des colonnes A à F

Marges!A1:A10 La plage des cellules en colonne Aet lignes 1 à 10 de la feuille decalcul nommée Marges

Références de cellule

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 72


Chapitre 3 Utiliser les formules et les fonctions 73

Opérateur Signification Exemple

Arithmétique

+ (signe plus) Addition 2+7

- (signe moins) Soustraction 7-2Opposé -2

* (astérisque) Multiplication 2*7

/ (barre oblique) Division 7/2

% (pour cent) Pourcentage 70%

^ (accent circonflexe) Exponentiation 2^7

Comparaison

= (signe égal) Égal à A2=B7

> (signe supérieur) Supérieur à A2>B7

< (signe inférieur) Inférieur à A2<B7

>= (signes supérieur et égal) Supérieur ou égal à A2>=B7

<= (signes inférieur et égal) Inférieur ou égal à A2<=B7

<> (signes inférieur et supérieur) Différent de A2<>B7

Concaténation de texte

& (esperluette) Concatène deux valeurs pour produire “Total”&”Marge”une valeur textuelle ininterrompue

Référence

: (deux-points) Opérateur de plage, qui produit une A1:A10référence à toutes les cellules qui setrouvent entre deux références

, (virgule) Opérateur d’union, qui combine SOMME(A1:A10;B1:B10)plusieurs références en une seule

˽ (espace) Opérateur d’intersection, qui produit A1:A10 B1:B10une référence aux cellules communes àdeux références

Types d’opérateurs

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 73


Une formule opère sur des valeurs pour retourner un résultat. Dans unefeuille de calcul, une formule peut se fonder sur des valeurs (comme147 ou 10,00 €), des opérateurs arithmétiques et des références de cel-lules. Une formule Excel commence toujours par le signe égal (=). Lors-qu’il est présent, la cellule prend automatiquement un format deformule. La meilleure manière de démarrer une formule est avec unargument, c’est-à-dire des références de cellules ou des valeurs quicontribuent au résultat. Chaque fonction emploie des arguments spéci-fiques, notamment des valeurs numériques, du texte, des références decellules, des plages de cellules, etc. Pour gérer les longues formulescomplexes, redimensionnez la barre de formule afin que les formules nechevauchent pas les autres données. Par défaut, seul le résultat d’uneformule s’affiche dans la cellule, ce que vous pouvez modifier de sorteque la feuille de calcul affiche à la place les formules.

74 Chapitre 3

Créer une formulesimple

Saisir une formule

Cliquez sur la cellule où saisir laformule.

Tapez = (signe égal). Sans le signeégal, Excel affiche, ne calcule pas,les informations saisies.

Saisissez le premier argument(valeur ou référence de cellule).

GAIN DE TEMPS Cliquez surune cellule pour insérer sa référencedans une formule au lieu de saisirson adresse.

Saisissez un opérateur.

Saisissez l’argument suivant.

Répétez les étapes 4 et 5 pour com-pléter la formule, si nécessaire.

Cliquez sur le bouton Entrer de labarre de formule ou appuyez surEntrée.

Le résultat de la formule apparaîtdans la cellule (si vous sélection-nez la cellule, la formule s’affichedans la barre de formule).

GAIN DE TEMPS Pour insérerun saut de ligne dans le texte d’unecellule, appuyez sur Alt+Entrée.

7

6

5

4

3

2

1

Comprendre l’ordre de prioritéLes formules qui comprennent plusieurs opérateurs se fondent surl’ordre de priorité suivant : exponentiation, multiplication et division,puis addition et soustraction. Pour la formule 5 + 2 * 3, Excel commencepar la multiplication puis l’addition et obtient 11. Les opérations entreparenthèses sont effectuées en premier. La formule (5 + 2) * 3 donnedonc 21.

Informations

Barre de formule7 1

2

3 5

4

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 74



Redimensionner la barrede formule

u Vous pouvez augmenter la barre deformule à trois lignes ou plus, ou laréduire à une ligne. Pour ce faire,cliquez sur la flèche à son extré-mité ou appuyez sur Ctrl+Maj+U.

u Pour ajuster précisément la hau-teur de la barre de formule, posi-tionnez le pointeur sur son bordinférieur, puis faites glisser vers lehaut ou le bas.

u Pour ajuster automatiquement labarre de formule au nombre delignes de texte de la cellule active,double-cliquez sur son bord infé-rieur.

Afficher les formules dansles cellules

Cliquez sur l’onglet Formules.

Cliquez sur le bouton Afficher lesformules.

GAIN DE TEMPS Appuyez surCtrl+’.

Pour désactiver l’affichage des for-mules, cliquez de nouveau sur Affi-cher les formules.

3

2

1

Développer ou réduire la barre de formule

21

AstucesRéduire les risque d’erreurs. Lors de lasaisie d’une formule, sélectionner unecellule au lieu de saisir son adressegarantit la validité de la référence.

Imprimer des formules. Cliquez sur l’on-glet Formules, sur le bouton Afficher lesformules, sur l’onglet Fichier, sur Impri-mer et enfin sur Imprimer.

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 75


76 Chapitre 3

Pour réduire l’effort de saisie et les erreurs de syntaxe, servez-vous de lasaisie semi-automatique de formule. Après que vous ayez saisi = (signeégal) et le début de la formule, Excel affiche une liste déroulante dyna-mique des fonctions valides, des arguments, des noms définis, des nomsde tableaux, des spécificateurs d’éléments spéciaux, y compris [ (crochetouvrant), , (virgule) et : (deux-points), et une chaîne de caractères qui cor-respond aux lettres saisies. Un argument peut être une référence de cel-lule ou une valeur qui contribue au résultat. Chaque fonction possède desarguments propres, qui peuvent être des nombres, du texte, des réfé-rences de cellules, des plages de cellules, etc.

Créer une formuleavec la saisiesemi-automatiquede formule

Saisir semi-automatiquementles éléments d’une formule

Cliquez sur la cellule dans laquelleentrer une formule.

Tapez = (signe égal), puis le débutde la formule pour lancer la saisiesemi-automatique de formule.

Par exemple, saisissez so pour affi-cher les éléments correspondants,comme SOMME et SOUS.TOTAL.

Le texte venant avant le point d’in-sertion fixe les éléments de la listedéroulante.

Lors de la saisie, une liste des élé-ments valides s’affiche.

Des icônes représentent les typesd’entrées, comme une fonction ouune référence de tableau, et uneinfo-bulle apparaît à côté de l’élé-ment sélectionné.

Pour insérer l’élément sélectionné,appuyez sur Tab ou double-cliquezdessus.

4

3

2

1

34

1

2

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 76



Naviguer au clavier

À l’aide du clavier, vous pouvez parcou-rir la liste de saisie semi-automatique deformule afin de trouver rapidement l’en-trée recherchée.

u Le tableau ci-contre recense lestouches disponibles pour la naviga-tion dans la liste de saisie semi-automatique de formule.

Activer la saisie semi-automatique de formule

Cliquez sur l’onglet Fichier, puis surOptions.

Dans le volet, cliquez sur Formules.

Cochez la case Saisie semi-auto-matique de formule.

Cliquez sur OK.

3

2

4

1

Touche Effet

Flèche vers la gauche Déplace le point d’insertion vers la gauche

Flèche vers la droite Déplace le point d’insertion vers la droite

Flèche vers le haut Sélectionne l’élément précédent

Flèche vers le bas Sélectionne l’élément suivant

Fin Sélectionne le dernier élément

Début Sélectionne le premier élément

Page précédente Saute une page vers le haut et sélectionneun nouvel élément

Page suivante Saute une page vers le bas et sélectionneun nouvel élément

Échap Ferme la liste déroulante

Alt+Flèche vers le bas Active ou désactive la saisie semi-automatique de formule

4

2

3

Touches pour la saisie semi-automatique

AstuceAbsence de certains éléments dans laliste déroulante. Les noms donnés auxconstantes énumérées, comme ceuxutilisés dans la fonction SOUS.TOTAL etceux des connexions des fonctionsCube, n’apparaissent pas dans la listede saisie semi-automatique de formule,mais vous pouvez néanmoins les saisir.

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 77


78 Chapitre 3

À l’instar de n’importe quel autre contenu de cellule, vous pouvez modi-fier une formule, à l’aide de la barre de formule ou dans la cellule. Vouspouvez sélectionner, couper, copier, coller, supprimer et mettre enforme des cellules qui contiennent des formules. La recopie incré-mentée permet de copier rapidement des formules dans des cellulesadjacentes. Pour les copier en différentes parties d’une feuille de calcul,utilisez la commande Coller ou le bouton Option de collage.

Modifier une formule

Utiliser la barre de formule

Sélectionnez la cellule qui contientla formule à modifier.

Appuyez sur F2 pour passer enmode Modifier.

Si nécessaire, utilisez les touchesdirectionnelles pour déplacer lepoint d’insertion dans le contenude la cellule.

Utilisez les touches Espace arrière etSuppr pour supprimer les caractèresinutiles, puis saisissez les nouveaux.

Cliquez sur le bouton Entrer de labarre de formule ou appuyez surEntrée.

Copier une formule avec la recopie incrémentée

Sélectionnez la cellule qui contientla formule à copier.

Placez le pointeur dans l’angleinférieur droit de la cellule sélec-tionnée (poignée de recopie).

Faites glisser vers le bas de manièreà sélectionner les cellules adja-centes dans lesquelles copier laformule, puis relâchez le bouton dela souris.

3

2

1

5

4

3

2

1

1

5

2 Mode Modifier

2

3

1

Poignée de recopie

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 78



Copier et coller une formule

Sélectionnez la cellule qui contientla formule à copier.

Cliquez sur l’onglet Accueil.

Cliquez sur le bouton Copier.

Sélectionnez la ou les cellules danslesquelles coller la formule.

Cliquez sur le bouton Coller, ou sursa flèche, survolez une option pourobtenir un aperçu instantané ducollage (Nouveau !), puis cliquezpour coller l’élément.

Cliquez sur le bouton Options decollage, survolez une option pourobtenir un aperçu instantané(Nouveau !), puis choisissez l’op-tion appopriée.

Si la sélection ne doit pas êtrecollée ailleurs, appuyez sur Échap.

2

6

5

4

3

1

1

2 3

4

6

5

AstucesUtiliser Collage spécial pour ne copierque les formules. Sélectionnez les cel-lules qui contiennent les formules àcopier, cliquez sur le bouton Copier del’onglet Accueil, puis à l’endroit où col-ler les données. Cliquez sur la flèche dubouton Coller, sur Collage spécial, puissur Formules et enfin sur OK.

Recalculer des formules avec les rac-courcis clavier. F9 recalcule les for-mules modifiées depuis le dernier calculdans tous les classeurs ouverts. Maj+F9recalcule les formules dans le classeuractif. Ctrl+Alt+F9 recalcule toutes lesformules dans tous les classeurs,qu’elles aient été modifiées ou non.Ctrl+Maj+Alt+F9 revérifie les formulesdépendantes dans tous les classeursouverts, qu’elles aient été modifiées ounon.

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 79


80 Chapitre 3

Dans une feuille de calcul, chaque cellule,c’est-à-dire l’intersection d’une colonne etd’une ligne, possède une adresse unique, ouréférence de cellule, composée de sa lettre decolonne et de son numéro de ligne. Parexemple, la cellule à l’intersection de lacolonne D et de la ligne 4 a pour référence D4.

Références dans les formulesLa formule la plus simple fait référence à unecellule. Pour qu’une cellule contienne lavaleur d’une autre, saisissez un signe égalsuivi de la référence de la cellule, comme=D4. La cellule qui contient la formule estune cellule dépendante car sa valeur dépendde celle d’une autre. Lorsque la cellule réfé-rencée change de valeur, la cellule dépen-dante change également.

Les références de cellules relatives font réfé-rence à des cellules relativement à la positionde la formule. Les références de cellules

absolues font toujours référence aux mêmesemplacements de cellules. Les références de

cellules mixtes combinent les référencesabsolues et relatives aux colonnes et auxlignes. Dans les macros, les références detype L1C1 facilitent le calcul des positions delignes et de colonnes.

Références relativesLors du copier-coller ou du déplacementd’une formule qui comprend des référencesrelatives, celles-ci sont modifiées pour ciblerles cellules qui se trouvent aux mêmesemplacements relativement à la formule. Laformule ne change pas, mais utilise les nou-velles cellules. Ces références évitent la créa-tion de nouvelles formules pour chaque ligneou colonne remplie des mêmes données.

Références absoluesPour qu’une référence ne change pas lors dela copie d’une formule, rendez-la absolue enajoutant un symbole dollar ($) avant chaquepartie de la référence qui ne doit pas changer.Par exemple, $A$1 désigne toujours la celluleA1. Si vous copiez ou recopiez la formuledans une autre cellule, où qu'elle soit, de lafeuille de calcul, la référence absolue nechange pas. Le dollar peut être ajouté avant lalettre de colonne ou le numéro de ligne. Pourfaciliter les mises à jour, saisissez lesconstantes, comme le taux de TVA, les tarifshoraires, etc., dans une cellule, puis utilisezdes références absolues dans les formules.

Références mixtesUne référence mixte comprend une ligneabsolue et une colonne relative, ou inverse-ment. Le dollar se place avant la lettre decolonne pour obtenir une colonne absolue,et avant le numéro de ligne pour une ligneabsolue. $A1 est absolue pour la colonne A,relative pour la ligne 1. A$1 est absolue pourla ligne 1, relative pour la colonne A. Lors dela copie ou de la recopie de la formule surdes lignes ou des colonnes, seules les réfé-rences relatives sont modifiées.

Références 3DUne référence 3D permet d’utiliser lesdonnées de la même cellule ou plage prove-nant de plusieurs feuilles de calcul. Elle inclutla référence de la cellule ou de la plage,précédée d’une plage de noms de feuilles.Par exemple, =MOYENNE(Feuil1:Feuil4!A1)retourne la moyenne des valeurs contenuesdans la cellule A1 de toutes les feuilles entreFeuil1 et Feuil4, celles-ci comprises.

Comprendre les références de cellules

C03EX10_C03EX10.qxd 29/03/2012 16:48 Page 80


Documents

Utiliser les formules et les fonctions - · PDF fileUtiliser les formules et les fonctions Introduction Après avoir saisi des données dans une feuille de calcul, vous voudrez ajouter