Exceller

Formules et fonctions Excel

Excel Formulas Functions

Les formules et les fonctions sont le pain et le beurre d'Excel. Ils conduisent presque tout ce qui est intéressant et utile que vous ferez jamais 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 cellule

Dans les exemples ci-dessus, les valeurs sont «codées en dur». Cela signifie que les résultats ne changeront que si vous modifiez à nouveau la formule et modifiez une valeur manuellement. En règle générale, 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 cellule



Remarquez 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 le pourcentage de changement. La formule renvoie un résultat correct en D2 et D3, mais renvoie un # DIV / 0! erreur en 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 IFERROR et afficher un message plus convivial (ou rien du tout).

Copier et coller des formules

L'intérêt des références de cellule est qu'elles sont automatiquement mises à jour lorsqu'une formule est copiée vers un nouvel emplacement. Cela signifie que vous n'avez pas besoin de saisir encore et encore la même formule de base. Dans l'écran ci-dessous, la formule de 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 Ctrl + V. Notez que les références de cellule ont changé:

Formule en E1 collée en E2

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

Formule en E1 collée en E3

Références relatives et absolues

Les références de cellule ci-dessus sont appelées relatif les références. Cela signifie que la référence est relative à la cellule dans laquelle elle vit. 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 change pas lorsqu'elle est copiée est appelée 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 saisie en 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 en 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 changer la valeur de A1 et les trois formules sont recalculées. En dessous, la valeur de A1 est passée de 10 à 12:

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

Cet exemple simple montre également pourquoi il n'est pas logique de coder en dur des valeurs dans une formule. En stockant la valeur dans A1 en un seul endroit et en faisant référence à 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 la syntaxe 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 cellule, vous pouvez pointer et cliquer, comme indiqué ci-dessous. Les références des 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 modifier une formule, vous avez 3 options:

  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 , éditez 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, par contre, est une formule avec un nom et un but spéciaux. Dans la plupart des cas, les fonctions ont des noms qui reflètent leur utilisation prévue. Par exemple, vous connaissez probablement le Fonction SUM 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

Le 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 minimum et maximum:

 
= 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 Excel clés .

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, plage et 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 YEARFRAC 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 un exemple d'utilisation de YEARFRAC pour calculer l'âge actuel en fonction de la date de naissance.

Comment entrer 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 souhaitée 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 complétera la fonction:

Appuyez sur Tab pour accéder à 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 accéder à la fonction

Combinaison de fonctions (imbrication)

De nombreuses formules Excel utilisent plus d'une fonction et les fonctions peuvent être ' imbriqué 'à l'intérieur de 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

Le Fonction YEARFRAC 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 en fonction de la date du jour:

 
= YEARFRAC (B1, TODAY ())

Fonctions YEARFRAC et TODAY 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 TODAY peut être imbriquée dans la fonction YEARFRAC pour fournir l'argument de date de fin. Nous pouvons aller plus loin dans la formule et utiliser le Fonction INT pour couper la valeur décimale:

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

YEARFRAC et TODAY à l'intérieur de l'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é Fonctions IF sont un exemple classique de fonctions d'imbrication .
  3. Le Fonction AUJOURD'HUI est une fonction Excel rare sans arguments requis.

À retenir: la sortie de toute 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 créer 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 les formules étape par étape.

Convertir les 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, en utilisant Collage spécial> Valeurs. Cela remplace les formules par 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


^