Exceller

Formules et fonctions Excel

Excel Formulas Functions

Les formules et les fonctions sont le pain et le beurre d'Excel. Ils pilotent presque tout ce qui est intéressant et utile que vous ferez dans une feuille de calcul. Cet article présente les concepts de base que vous devez connaître pour maîtriser les formules dans Excel. Plus d'exemples ici .



Qu'est-ce qu'une formule ?

Une formule dans Excel est une expression qui renvoie un résultat spécifique. Par exemple:

 
=1+2 // returns 3

Exemple de formule de base - 1 + 3 = 3





 
=6/3 // returns 2

Exemple de formule de base - 6 / 3 = 2

Remarque : toutes les formules dans Excel doivent commencer par un signe égal (=).



Références de cellules

Dans les exemples ci-dessus, les valeurs sont « codées en dur ». Cela signifie que les résultats ne changeront pas, sauf si vous modifiez à nouveau la formule et modifiez une valeur manuellement. Généralement, cela est considéré comme une mauvaise forme, car cela masque des informations et rend plus difficile la maintenance d'une feuille de calcul.

Utilisez plutôt des références de cellule pour que les valeurs puissent être modifiées à tout moment. Dans l'écran ci-dessous, C1 contient la formule suivante :

 
=A1+A2+A3 // returns 9

Formule avec références de cellules

Notez que parce que nous utilisons des références de cellule pour A1, A2 et A3, ces valeurs peuvent être modifiées à tout moment et C1 affichera toujours un résultat précis.

Toutes les formules renvoient un résultat

Toutes les formules dans Excel renvoient un résultat, même lorsque le résultat est une erreur. Ci-dessous, une formule est utilisée pour calculer la variation en pourcentage. La formule renvoie un résultat correct dans D2 et D3, mais renvoie un #DIV/0 ! erreur dans D4, car B4 est vide :

Le résultat de la formule peut être une erreur

Il existe différentes manières de gérer les erreurs. Dans ce cas, vous pouvez fournir la valeur manquante dans B4, ou 'attraper' l'erreur avec le Fonction SIERREUR et afficher un message plus convivial (ou rien du tout).

Copier et coller des formules

La beauté des références de cellules est qu'elles se mettent automatiquement à jour lorsqu'une formule est copiée vers un nouvel emplacement. Cela signifie que vous n'avez pas besoin d'entrer la même formule de base encore et encore. Dans l'écran ci-dessous, la formule dans E1 a été copiée dans le presse-papiers avec Ctrl + C :

Formule dans E1 copiée dans le presse-papiers

Ci-dessous : formule collée dans la cellule E2 avec Contrôle + V. Notez que les références des cellules ont changé :

Formule dans E1 collée à E2

Même formule collée à l'E3. Les adresses des cellules sont à nouveau mises à jour :

Formule dans E1 collée à E3

Références relatives et absolues

Les références de cellules ci-dessus sont appelées relatif les références. Cela signifie que la référence est relative à la cellule dans laquelle elle réside. La formule dans E1 ci-dessus est :

 
=B1+C1+D1 // formula in E1

Littéralement, cela signifie 'cellule 3 colonnes à gauche' + 'cellule 2 colonnes à gauche' + 'cellule 1 colonne à gauche'. C'est pourquoi, lorsque la formule est copiée dans la cellule E2, elle continue de fonctionner de la même manière.

Les références relatives sont extrêmement utiles, mais il y a des moments où vous ne voulez pas qu'une référence de cellule change. Une référence de cellule qui ne changera pas lors de la copie est appelée un référence absolue . Pour rendre une référence absolue, utilisez le symbole dollar ($) :

 
=A1 // relative reference =$A // absolute reference

Par exemple, dans l'écran ci-dessous, nous voulons multiplier chaque valeur de la colonne D par 10, qui est entrée dans A1. En utilisant une référence absolue pour A1, nous « verrouillons » cette référence afin qu'elle ne change pas lorsque la formule est copiée dans E2 et E3 :

Exemple de référence absolue

Voici les formules finales dans E1, E2 et E3 :

 
=D1*$A // formula in E1 =D2*$A // formula in E2 =D3*$A // formula in E3

Notez que la référence à D1 est mise à jour lorsque la formule est copiée, mais la référence à A1 ne change jamais. Maintenant, nous pouvons facilement modifier la valeur dans A1, et les trois formules recalculent. Ci-dessous, la valeur dans A1 est passée de 10 à 12 :

Exemple de référence absolue après modification de la valeur dans A1

Cet exemple simple montre également pourquoi cela n'a pas de sens de coder en dur des valeurs dans une formule. En stockant la valeur dans A1 en un seul endroit, et en se référant à A1 avec un référence absolue , la valeur peut être modifiée à tout moment et toutes les formules associées seront mises à jour instantanément.

Astuce : vous pouvez basculer entre la syntaxe relative et absolue avec le Touche F4 .

Comment saisir une formule

Pour saisir une formule :

  1. Sélectionnez une cellule
  2. Entrez un signe égal (=)
  3. Tapez la formule et appuyez sur Entrée.

Au lieu de taper des références de cellules, vous pouvez pointer et cliquer, comme indiqué ci-dessous. Les références de notes sont codées par couleur :

Saisie d'une formule avec des références pointer-cliquer

Toutes les formules dans Excel doivent commencer par un signe égal (=). Pas de signe égal, pas de formule :

Oubli d'entrer un signe égal signifie pas de formule, juste du texte

Comment changer une formule

Pour éditer une formule, vous avez 3 possibilités :

  1. Sélectionnez la cellule, modifiez dans le barre de formule
  2. Double-cliquez sur la cellule, modifiez directement
  3. Sélectionnez la cellule, appuyez sur F2 , modifier directement

Quelle que soit l'option que vous utilisez, appuyez sur Entrée pour confirmer les modifications lorsque vous avez terminé. Si vous souhaitez annuler et laisser la formule inchangée, cliquez sur la touche Échap.

Vidéo: 20 conseils pour saisir des formules

Qu'est-ce qu'une fonction ?

En travaillant dans Excel, vous entendrez les mots « formule » et « fonction » utilisés fréquemment, parfois de manière interchangeable. Ils sont étroitement liés, mais pas exactement les mêmes. Techniquement, une formule est tout expression commençant par un signe égal (=).

Une fonction, en revanche, est une formule avec un nom et un objectif particuliers. Dans la plupart des cas, les fonctions ont des noms qui reflètent leur utilisation prévue. Par exemple, vous connaissez probablement le Fonction SOMME déjà, qui renvoie la somme des références données :

 
= SUM (1,2,3) // returns 6 = SUM (A1:A3) // returns A1+A2+A3

Les Fonction MOYENNE , comme vous vous en doutez, renvoie la moyenne des références données :

 
= AVERAGE (1,2,3) // returns 2

Et les fonctions MIN et MAX renvoient respectivement des valeurs minimales et maximales :

 
= MIN (1,2,3) // returns 1 = MAX (1,2,3) // returns 3

Excel contient des centaines de fonctions spécifiques . Pour commencer, consultez 101 fonctions clés d'Excel .

Arguments de fonction

La plupart des fonctions nécessitent des entrées pour renvoyer un résultat. Ces entrées sont appelées « arguments ». Les arguments d'une fonction apparaissent après le nom de la fonction, entre parenthèses, séparés par des virgules. Toutes les fonctions nécessitent des parenthèses ouvrantes et fermantes correspondantes (). Le motif ressemble à ceci :

 
=FUNCTIONNAME(argument1,argument2,argument3)

Par exemple, le Fonction COUNTIF compte les cellules qui répondent aux critères et prend deux arguments, gamme et Critères :

 
= COUNTIF (range,criteria) // two arguments

Dans l'écran ci-dessous, la plage est A1:A5 et le critère est « rouge ». La formule en C1 est :

 
= COUNTIF (A1:A5,'red') // returns 2

COUNTIF nécessite deux arguments, une plage et des critères

Vidéo: Comment utiliser la fonction COUNTIF

Tous les arguments ne sont pas obligatoires. Les arguments indiqués entre crochets sont facultatifs. Par exemple, le Fonction ANNÉEFRAC renvoie un nombre fractionnaire d'années entre une date de début et une date de fin et prend 3 arguments :

 
= YEARFRAC (start_date,end_date,[basis])

La date de début et la date de fin sont des arguments obligatoires, la base est un argument facultatif. Voir ci-dessous pour un exemple d'utilisation de YEARFRAC pour calculer l'âge actuel en fonction de la date de naissance.

Comment saisir une fonction

Si vous connaissez le nom de la fonction, commencez simplement à taper. Voici les étapes :

1. Entrez le signe égal (=) et commencez à taper. Excel répertorie les fonctions correspondantes en fonction de la saisie :

Au fur et à mesure que vous tapez, Excel affichera les fonctions correspondantes

Lorsque vous voyez la fonction que vous voulez dans la liste, utilisez les touches fléchées pour sélectionner (ou continuez simplement à taper).

2. Tapez la touche Tab pour accepter une fonction. Excel va compléter la fonction :

Appuyez sur Tab pour entrer dans la fonction sélectionnée

3. Remplissez les arguments requis :

Entrez les arguments requis

4. Appuyez sur Entrée pour confirmer la formule :

Appuyez sur Entrée pour confirmer et entrer dans la fonction

Combinaison de fonctions (imbrication)

De nombreuses formules Excel utilisent plusieurs fonctions, et les fonctions peuvent être ' imbriqué ' l'un dans l'autre. Par exemple, ci-dessous, nous avons une date de naissance en B1 et nous voulons calculer l'âge actuel en B2 :

Besoin d'une formule pour calculer l'âge actuel en B2

Les Fonction ANNÉEFRAC calculera les années avec une date de début et une date de fin :

YEARFRAC calculera les années avec une date de début et une date de fin

Nous pouvons utiliser B1 pour la date de début, puis utiliser le Fonction AUJOURD'HUI pour fournir la date de fin :

B1 pour la date de début, la fonction AUJOURD'HUI pour fournir la date de fin

Lorsque nous appuyons sur Entrée pour confirmer, nous obtenons l'âge actuel basé sur la date d'aujourd'hui :

 
= YEARFRAC (B1, TODAY ())

Fonctions YEARFRAC et AUJOURD'HUI pour calculer l'âge actuel

Notez que nous utilisons la fonction AUJOURD'HUI pour fournir une date de fin à la fonction YEARFRAC. En d'autres termes, la fonction AUJOURD'HUI peut être imbriquée dans la fonction YEARFRAC pour fournir l'argument de date de fin. Nous pouvons pousser la formule un peu plus loin et utiliser le fonction INT pour supprimer la valeur décimale :

 
= INT ( YEARFRAC (B1, TODAY ()))

YEARFRAC et AUJOURD'HUI dans INT

Ici, la formule YEARFRAC d'origine renvoie 20,4 à la fonction INT et la fonction INT renvoie un résultat final de 20.

Remarques:

  1. La date actuelle dans les images ci-dessus est le 22 février 2019.
  2. Imbriqué SI fonctions sont un exemple classique de fonctions d'imbrication .
  3. Les Fonction AUJOURD'HUI est une fonction Excel rare sans arguments requis.

À retenir : la sortie de n'importe quelle formule ou fonction peut être directement introduite dans une autre formule ou fonction.

Opérateurs mathématiques

Le tableau ci-dessous présente les opérateurs mathématiques standard disponibles dans Excel :

symbole Opération Exemple
+ Une addition = 2 + 3 = 5
- Soustraction = 9-2 = 7
* Multiplication = 6 * 7 = 42
/ Division = 9/3 = 3
^ Exponentiation = 4 ^ 2 = 16
() Parenthèses = (2 + 4) / 3 = 2

Opérateurs logiques

Les opérateurs logiques prennent en charge les comparaisons telles que 'supérieur à', 'inférieur à', etc. Les opérateurs logiques disponibles dans Excel sont indiqués dans le tableau ci-dessous :

Opérateur Sens Exemple
= Égal à = A1 = 10
Pas égal à = A110
> Plus grand que = A1> 100
< Moins que = A1<100
> = Plus grand ou égal à = A1> = 75
<= Inférieur ou égal à = A1<=0

Vidéo: Comment construire des formules logiques

Ordre des opérations

Lors de la résolution d'une formule, Excel suit une séquence appelée « ordre des opérations ». Tout d'abord, toutes les expressions entre parenthèses sont évaluées. Ensuite, Excel résoudra tous les exposants. Après les exposants, Excel effectuera la multiplication et la division, puis l'addition et la soustraction. Si la formule implique enchaînement , cela se produira après les opérations mathématiques standard. Enfin, Excel évaluera Opérateurs logiques , si présent.

  1. Parenthèses
  2. Exposants
  3. Multiplication et division
  4. Addition et soustraction
  5. Enchaînement
  6. Opérateurs logiques

Astuce : vous pouvez utiliser le Évaluer la fonctionnalité pour regarder Excel résoudre des formules étape par étape.

Convertir des formules en valeurs

Parfois, vous voulez vous débarrasser des formules et ne laisser que les valeurs à leur place. Le moyen le plus simple de le faire dans Excel consiste à copier la formule, puis à la coller à l'aide de Collage spécial > Valeurs. Cela écrase les formules avec les valeurs qu'elles renvoient. Vous pouvez utiliser un raccourci clavier pour coller des valeurs ou utilisez le menu Coller de l'onglet Accueil du ruban.

Vidéo: Coller des raccourcis spéciaux

convertir des secondes en minutes dans Excel

Et après?

Vous trouverez ci-dessous des guides pour vous aider à en savoir plus sur les formules et les fonctions d'Excel. Nous proposons également formation vidéo en ligne .

Auteur Dave Bruns


^