Licence CC BY-NC-SA

A l'assaut des formules

Ce chapitre est très important ! Les formules vont en effet vous permettre de calculer à partir de données numériques. Notez que grâce aux formules, vous pourrez aussi gérer vos données alphabétiques. Les formules font le lien entre la saisie et l'analyse de données. Elles vont par exemple vous permettre de calculer le total des points de tous les joueurs en fonction des scores de chaque niveau.

Le chapitre sera un peu long, alors ne lisez pas tout d'un coup. ;)

Une bête de calculs

Ici, nous allons découvrir en douceur les formules. Dans le premier chapitre, je vous avais parlé de la « barre de formule », non ?

Je crois que oui. C'est ici que nous allons les écrire, ces fameuses bestioles. Elles s'appliqueront à toutes les cellules sélectionnées.

Une formule commence toujours par le signe égal =.

Ces bébêtes sont capables premièrement de faire des calculs… de nombreux calculs !

Opérations basiques

Pour toutes les formules, on va utiliser ce que l'on appelle des « fonctions ». Ce sont des mots écrits en majuscule dans les formules et qui permettent de ne pas avoir à écrire des opérateurs (+, x, etc.) C'est donc très pratique s'il y a des formules de 3 lignes.

Il faut se faire une image de la fonction : c'est représenté par un mot dans lequel on fait passer des données. La fonction travaille sur ces données et ressort le résultat.

Pour mieux comprendre, voici le schéma de ce que je viens de raconter :

Mais comment on lui fait passer des données, et où sera affiché le résultat ?

Le résultat de votre formule sera affiché dans la ou les cellules qui contiennent cette formule.

Voici la syntaxe d'une formule avec fonction :

=FONCTION(DONNEE1;DONNEE2)

À la place des données, vous allez écrire la référence des cellules qui contiennent les données à analyser…

L'addition

L'addition est gérée par la fonction SOMME. En B2, tapez 5 ; en C2, 123 (prenez l'habitude de ne pas commencer à saisir des données à la ligne 1 et dans la colonne A ;) ). Vous devriez avoir ceci :

Maintenant, je veux en E2 le résultat de l'addition de ces deux valeurs. Je vais donc taper ma formule en E2, ce qui donne : =SOMME(B2;C2)

Validez par la touche Entrée : vous avez en E2 le résultat de l'addition 5 + 123 !

Et pourquoi on n'a pas écrit directement = 5 +123 ?

Parce que c'est une méthode très mauvaise qui ne s'adapte pas aux données saisies. Avec notre formule, changez la valeur de B2, mettez par exemple 10… Que constatez-vous ? Le résultat en E2 s'adapte ! :magicien:

=SOMME(B2;-C2)

La multiplication

La multiplication est gérée par la fonction PRODUIT. Sa syntaxe est la même que pour l'addition.

Maintenant que vous avez compris, essayez avec DIFFERENCE et QUOTIENT, qui gèrent respectivement la soustraction et la division.

Une fonction intéressante

Nous y voilà enfin. Une fonction bien intéressante est la fonction « MOYENNE », qui, comme vous vous en doutez, fait la moyenne d'une plage de cellules. Elle n'est pas intéressante parce qu'elle fait la moyenne mais parce qu'il y a une manière un peu spéciale d'écrire la formule…

Délimitez une plage rectangulaire de cellules et entrez une donnée numérique dans chacune d'elles. Dans une cellule en dehors de cette plage, nous allons faire la moyenne de tous les nombres que vous avez entrés.

Commençons à taper la formule…

Et là, attention, les choses intéressantes commencent : sélectionnez votre plage de cellules :

La plage a été générée toute seule dans la formule ! Fermez la parenthèse, validez, vous avez votre moyenne. Cette technique est valable pour toutes les fonctions vues ci-dessus. En règle générale, vous serez plus souvent amenés à faire une addition de toutes les valeurs d'une grande plage de cellules qu'une addition des valeurs de deux cellules ! ^^

Nous n'allons pas continuer à étudier chaque fonction : Excel en propose beaucoup (trigonométrie, etc) et une annexe y est consacré.

Exercice : des minutes aux heures et minutes

Pour mettre en pratique les fonctions, nous allons créer un petit convertisseur temporel. Dans une cellule vous rentrez un certain nombre de minutes, et dans une autre, on renvoie le nombre d'heures et de minutes correspondantes. Par exemple, pour 143 minutes, on devra renvoyer 2 h 23 min .

Je pense que vous avez remarqué que la conversion est aisée. 143/60 = 2 (/ étant la division entière, sous Excel la fonction QUOTIENT(x;y) ).

Le reste de la division euclidienne de 143 par 60 vaut 23. Il y a donc 23 minutes et 2 heures. Cette information, nous allons l'obtenir grâce à la fonction MOD(a ; b), qui permet de récupérer le reste de la division entière de a par b, avec b non nul évidemment. Par exemple, MOD(12 ; 6) renvoie 0 car 12 = 26 + 0 et MOD(12 ; 5) renvoie 2 car 12 = 25 + 2 :) . Ici MOD(143;60) renvoie donc 23.

Il y a donc deux calculs à faire, ce qui implique deux cellules différentes. Un artifice sur 4 cellules vous permet donc de renvoyer le résultat sous la forme x h y min. La solution en secret, mais ça n'a pas du poser de problème.

En E3 notre nombre de minutes, c'est un entier positif, saisi par l'utilisateur. En G3, on a tout simplement = QUOTIENT(E3 ; 60) et en I3 = MOD(E3 ; 60)

Les conditions

Nous venons de finir avec les fonctions. Il est inutile de toutes les passer en revue. Vous connaissez les plus classiques et les plus utiles, le reste viendra en temps voulu :) .

Ici, nous sommes toujours sur les formules, donc, ça se tape toujours dans la barre de formules et ça commence toujours par le signe égal =. Toutefois, ce sont des formules un peu particulières, que l'on appelle les « conditions ».

Les conditions simples

Quand vous écrivez une condition, vous dites à Excel : « si telle cellule vaut tant, alors fais ceci, sinon, fais cela ». Vous saisissez l'intérêt du concept, maintenant ? ;)

Par exemple, je veux afficher « Oui » ou « Non » dans une cellule en fonction de la valeur d'une autre cellule. Si celle-ci est égale à 100, j'affiche « Oui », sinon, j'affiche « Non ».

Voici la syntaxe d'une condition :

=SI(condition;"Afficher si vrai";"Afficher si faux")

Je mets quoi à la place de « condition » ?

Différentes conditions sont possibles. Voici les opérateurs qui vont vous être utiles :

Opérateur

Description

=

Est égal à…

>

Est supérieur à…

<

Est inférieur à…

>=

Est supérieur ou égal à…

<=

Est inférieur ou égal à…

<>

Est différent de…

Si votre condition est : « Si la cellule B2 est supérieure ou égale à 45, alors… », vous remplacerez « condition » par B2>=45.

Voici comment ça se passe dans Excel :

Et voilà le résultat quand la cellule contient une donnée numérique différente de 100 :

Et quand la donnée est égale à 100 :

Les conditions multiples

Il existe deux formes de conditions multiples :

  1. « Si cette cellule vaut tant et l'autre vaut tant, alors fais ceci, sinon, fais cela. »
  2. « Si cette cellule vaut tant ou l'autre vaut tant, alors fais ceci, sinon, fais cela. »

Avant et après le ET ou le OU, vous mettez une condition. D'où le nom de condition multiple. La différence entre ces deux cas, c'est que dans l'un les deux conditions doivent être remplies pour effectuer une tâche quelconque alors que dans l'autre, il faut qu'une seule condition soit remplie pour effectuer une tâche.

Mettons les choses au clair avec des schémas, comme nous les aimons tous.

Schémas de la condition multiple en ET

Schémas de la condition multiple en OU

Est-ce plus clair ? Si oui, la condition est respectée et vous pouvez passer à la suite. Sinon, la condition n'est pas respectée et vous devez relire les schémas. :p

Application

Maintenant que la différence est faite entre ET et OU, je propose de mettre en pratique ces fameuses conditions multiples.

Voici la syntaxe :

=SI(OPERATEUR LOGIQUE(condition1;condition2);"Afficher si vrai";"Afficher si faux")

Je mets quoi à la place de « opérateur logique » ?

Vous mettez soit ET, soit OU. :D

Mettons cela en pratique !

Comme avant, je veux afficher soit OUI ou NON en fonction de la valeur d'une cellule. Dans ce cas, prenons cette valeur à 100. Voici la formule, D6 étant la cellule où est stockée cette valeur :

Dans ce cas, il affiche OUI.

Mettez la valeur à 12, par exemple, il affichera NON.

La poignée de recopie incrémentée

Vous souvenez-vous de la poignée de recopie incrémentée ? Allez, je la remets. :D

/ Va chercher dans les archives poussiéreuses… /

La voici la petite coquine :

Le petit carré noir, en bas à droite, qui recopie la valeur des cellules où vous voulez et qui reconnait quelques listes… Ah ! Eh bien voilà ! J'étais sûr que vous vous en souviendriez !

J'ai un scoop, cette poignée est capable de recopier aussi vos formules et de les adapter !

Voyons avec un exemple très simple : une addition où je vais exceptionnellement ne pas utiliser une fonction mais bien un opérateur (+) :

Une vulgaire addition que j'aimerais recopier vers le bas. Seulement voilà, il serait difficile ( :D ) et trop long de faire un copier/coller de la formule sur toutes les cellules. J'utilise donc la poignée de recopie incrémentée sur ma formule :

Et j'obtiens un résultat spectaculaire : Excel a compris qu'il fallait « descendre » d'une cellule à chaque fois. Regardez, alors que ma formule de départ concernait la cellule G8, la case d'en dessus utilise la cellule G9. Eh oui, Excel est intelligent :

Et à quoi correspondent ces dollars \$ dans les formules. Jamais vu encore… ?

Eh bien, les dollars servent à figer l'objet devant lequel il se trouve. Dans ce cas, il est devant la lettre de la colonne et le numéro de la ligne : la cellule E6 est totalement figée.

Si je ne l'avais pas fait, Excel aurait additionné les valeurs des cellules en dessous de E6, c'est-à-dire 0 (une cellule vide a pour valeur 0) !

Lorsque j'utilise la poignée sur une formule, Excel incrémente les cellules qui sont impliquées dans cette formule. Les dollars me permettent d'éviter cette incrémentation, ce qui peut s'avérer utile. Notez que ce signe peut être uniquement placé devant la lettre de colonne (il figera alors la colonne), ou uniquement devant le numéro de ligne (il figera alors la ligne).

Puis pour finir, j'ajoute que dans une formule, il y a autant de parenthèse(s) ouvrante(s) que de parenthèse(s) fermante(s)

Mises en forme conditionnelles

Il est possible d'agir sur la mise en forme de son classeur en fonction d'une condition. Imaginez que vous souhaitez faire une facture :

Pour avoir le total ligne par ligne, il faut multiplier le prix unitaire du produit par la quantité souhaitée par le client. Pour ne pas recopier la formule sur toutes les lignes, vous utilisez la poignée de recopie incrémentée que nous venons de voir. Et là, c'est le drame :

Parce qu'une cellule vide a pour valeur 0, Excel vous affiche le résultat, sur les lignes vides, du produit 0x0 = 0. C'est moche. Nous allons donc appliquer une mise en forme conditionnelle : si la valeur de la cellule vaut 0, alors je mets le texte en blanc. Sinon, je le mets en noir.

D'abord, il faut sélectionner les cellules sur lesquelles la mise en forme doit être appliquée (ça peut être comme ici une portion réduite de colonne mais vous pouvez sélectionner la colonne entière).

Dans l'onglet « Accueil », dans le groupe « Style », cliquez sur « Mise en forme conditionnelle ». Dans le menu déroulant, cliquez sur « Nouvelle règle » :

Dans la fenêtre qui s'ouvre, cliquez sur « Appliquer une mise en forme uniquement aux cellules qui contiennent ». Laissez la « Valeur de la cellule » mais vous choisissez « égale à » dans la seconde liste déroulante. Tapez « 0 » dans le troisième champ puis cliquez sur le bouton « Format ».

Dans cette nouvelle fenêtre, choisissez la couleur blanche en guise de police. Puis validez ces deux fenêtres :

Vous venez de faire une mise en forme conditionnelle : c'est-à-dire changer les propriétés d'une cellule en fonction de sa valeur. On peut faire pareil, mais en changeant la couleur de fond, par exemple. Notez que vous pouvez imposer une condition de supériorité, d'infériorité etc. Faites des essais en manipulant ces différents cas.

Transmettre des informations entre différents feuillets

Je vous l'ai dit au début du cours, un classeur Excel est en fait un ensemble de feuillets. Vous pouvez en avoir autant que vous le souhaitez et vous pouvez effectuer diverses opérations dessus, comme les renommer ou encore les supprimer. J'explique en annexe comment effectuer toutes ces opérations. Dans la pratique, il se peut que vos données soient réparties dans plusieurs feuillets différents, évidement nommés pour ne pas s'y perdre ;) . Dans ce cas, il sera très utile de pouvoir transmettre des données d'une feuille à l'autre, et c'est justement ce que nous allons voir maintenant.

La transmission de données d'un feuillet à l'autre se passe dans une formule. Ça tombe bien, vous savez maintenant de quoi il s'agit. Prenons l'exemple avec deux feuillets « Source » et en « Cible ». Vous l'aurez sans doute compris, nous allons transmettre une donnée du premier feuillet vers le second :

Dans le feuillet « Source », tapez en B2 une donnée (numérique ou non, peu importe).

Pour récupérer cette donnée dans le feuillet « Cible », il faut préciser de quel feuillet notre donnée provient. Dans ce cas, la donnée à transmettre provient de « Source ». La formule sera donc :

=Source!B2

On précise de quel feuillet nous souhaitons importer les données au début de la formule avec le nom de la feuille suivi d'un point d'exclamation. Si je tape cette formule en C5 de mon feuillet « Cible », je vais avoir le même texte qu'en B2 dans le feuillet « Source ».

A vous maintenant d'adapter cette méthode en fonction de vos besoins ! :D

D'où vient ma formule et où va-t-elle ?

Je ne sais pas si vous avez remarqué, mais lorsque vous tapez une formule et qu'elle est dépendante d'autres cellules, ces cellules portent un cadre de couleur pour vous aider dans la saisie. Comme ceci :

Cette astuce n'est valable que lorsque vous éditez la formule, c'est-à-dire lorsque vous la modifiez. Il est intéressant de voir d'un seul coup d'œil quelles sont les cellules qui sont utilisées dans une formule sans avoir à éditer la formule. De plus, si vous voulez le faire pour plusieurs cellules en même temps… vous l'avez deviné, c'est impossible.

Pour voir les dépendances qu'il existe entre les cellules, Excel propose deux fonctions. La première fonction est la suivante : "Repérer les antécédents" qui permet de repérer dans la feuille quelles sont les cellules qui influent sur la cellule sélectionnée. Réciproquement, la seconde fonction "Repérer les dépendants" permet de connaître quelles sont les cellules influencées par la cellule sélectionnée.

Ces deux fonctions se trouvent dans l'onglet Formules et dans le groupe Audit de formule.

Comment les utiliser ? C'est très simple, il suffit de placer le curseur sur la cellule de votre choix et ensuite de cliquer sur la fonction qui vous convient. Apparaissent alors des flèches bleues.

Et voilà, on peut alors voir toutes les dépendances des cellules entre elles sur une même feuille ! Pour supprimer les flèches, cliquez sur la fonction "Supprimer les flèches" dans le même groupe.

Exercice : c'est férié aujourd'hui !

Avant de passer au chapitre suivant, je vous propose un exercice d'entrainement sur les formules. En effet, les formules constituent le noyau dur du tableur Excel car c'est votre premier outil d'analyse efficace des données.

Afin d'apprendre quelque chose durant cet exercice et de ne pas appliquer bêtement ce que vous venez de voir, je vous propose de travailler avec les dates. Excel propose plusieurs fonctions pour manipuler les dates, une sous-partie annexe vous explique tout cela, je vous demande d'en prendre connaissance.

Nos objectifs

L'utilisateur de votre tableur est invité à saisir une année (par exemple : 2015).

  • Vous devez renvoyer le jour du premier janvier, du premier mai, du 8 mai, du 14 juillet, du 15 août, du 11 novembre, du 25 décembre (c'est-à-dire si ça tombe un lundi, un mardi etc.) ;
  • Vous devez indiquer si oui ou non le 29 février existe dans l'année donnée, c'est-à-dire si l'année est bissextile ;
  • Vous devez renseigner le jour du jeudi de l'Ascension, de la Pentecôte, du lundi de Pentecôte, de Pâques et du lundi de Pâques.

Nos outils

La fonction DATE()

Bien que son utilisation soit expliquée en annexe, je vais tout de même vous apprendre à vous servir de la fonction DATE(). DATE a besoin de trois paramètres : l'année, le mois et le jour.

Comment savoir si une année est bissextile ?

Une année est bissextile si elle est multiple de 4 mais pas de 100. De plus, les années multiples de 400 sont bissextiles. Nous avons déjà vu dans notre exercice de conversion des minutes en heures et minutes que le reste d'une division d'un entier par un entier est obtenue grâce à la fonction MOD(). Par exemple, considérons l'année 2000. $\frac{2000}{400} = 5$. Le reste de cette division est donné par MOD(2000,400) et vaut 0. Donc, 2000 est divisible par 400, donc 2000 est bissextile. Vous allez donc tout naturellement utiliser les conditions.

Autrement dit, il faut d'abord voir si l'année est multiple de 4. Si elle ne l'est pas, on s'arrête là, l'année n'est pas bissextile. En revanche, si elle est multiple de 4, nous testons sa divisibilité par 100. Si elle est vérifiée, nous regardons si l'année est multiple de 400, et alors, elle est bissextile si c'est vérifié, sinon, elle ne l'est pas.

Voilà, vous venez de découvrir le premier outil d'analyse des données. C'est le plus utilisé. On va en voir d'autres dans les chapitres suivants ! Sachez toutefois que nous proposons en annexe un (long) chapitre sur les fonctions d'Excel. C'est un index non exhaustif de fonctions rangées par catégories (Date & Heure etc.) et documentées (présentation, exemples) qui pourraient vous être utiles :) . N'hésitez pas à fouiner là-bas quand vous traitez des données.