Aller au menu - Aller au contenu

Icône A l'assaut des formules

Avatar
Avatar
Avatar
Par Avatar bat538 et Avatar Etienne et Avatar ~Electro
Mise à jour : 05/09/2011
Difficulté : Facile Facile Creative Commons BY-NC-SA
9 728 visites depuis 7 jours, dont 304 sur ce chapitre classé 26/786
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 d'un en fonction des scores de chaque niveau.

Le chapitre sera un peu long, alors ne lisez pas tout d'un coup. ;)
Retour en haut
Sommaire du chapitre :
Icône du chapitre
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

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 :

Image utilisateur


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 :

Image utilisateur


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)
 » :

Image utilisateur


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 eux 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.

Image utilisateur


Commençons à tapez la formule...

Image utilisateur


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

Image utilisateur


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 un tutoriel y est consacré.

Exercice : des secondes 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 secondes, et dans une autre, on renvoie le nombre d'heures et de minutes correspondantes. Par exemple, pour 143 secondes, 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) ) et le reste de la division euclidienne de 143 par 60 vaut 23. Il y a donc 23 minutes et 2 heures.

La fonction MOD(a ; b) 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 = 2*6 + 0 et MOD(12 ; 5) renvoie 2 car 12 = 2*5 + 2 :) .

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.

Secret (cliquez pour afficher)

Image utilisateur


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

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 formule 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 :

Image utilisateur


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

Image utilisateur


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

Image utilisateur


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



Image utilisateur


Image utilisateur


Image utilisateur


Schémas de la condition multiple en OU



Image utilisateur


Image utilisateur


Image utilisateur


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 :

Image utilisateur


Dans ce cas, il affiche OUI.

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

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 :

Image utilisateur


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 (+) :

Image utilisateur


Une vulgaire addition que j'aimerais recopier vers le bas. Seulement voilà, il serait difficile ( :D ) et trop long pour 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 :

Image utilisateur


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. Et oui, Excel est intelligent :

Image utilisateur


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)
Retour en haut

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 :

Image utilisateur


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

Image utilisateur


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
Retour en haut
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.
Chapitre précédent Sommaire Chapitre suivant
Retour en haut

Partager

Retour en haut
15 commentaires pour "A l'assaut des formules"
Note moyenne : 3.67 / 4 (100 votes)
Pseudo Commentaire
Hors ligne pti_bibi # Posté le 19/06/2011 à 00:16:58

oups désoler je n avais pas vu qu il y avais un forum
Hors ligne cinéma # Posté le 07/01/2012 à 15:52:20

Avis : Très bon

Ville : Barembach
Pays : France métropolitaine

 Salut,

J'ai un calcule comme cela =SOMME(B3;B4;B5;B6;B7;B8)

Je voudrais faire le même mais mettre un - au B4 soit =SOMME(B3;-B4;B5;B6;B7;B8)

Le problème et que je suis a 4138 et que le B4 fait 12 donc en fessent -B4 il devrais être a 4126 mon calcule total.

Mais la cela me fait 4114 !
Hors ligne Despi313 # Posté le 21/02/2012 à 17:01:21

Citation : cinéma
Salut,

J'ai un calcule comme cela =SOMME(B3;B4;B5;B6;B7;B8)

Je voudrais faire le même mais mettre un - au B4 soit =SOMME(B3;-B4;B5;B6;B7;B8)

Le problème et que je suis a 4138 et que le B4 fait 12 donc en fessent -B4 il devrais être a 4126 mon calcule total.

Mais la cela me fait 4114 !


Bonjour,

D’après moi, mais je viens de me mettre à excel depuis aujourd'hui (donc dite moi si je me trompe), ton problème vient du fait que:

Si tu écris =SOMME(B3;-B4;B5;B6;B7;B8) ton B4 va devenir une valeur négative et donc se soustraire à la somme des autres valeurs se qui te fait 4126-12=4114
Alors que si tu fais =SOMME(B3:B8;-B4) il va annuler sa propre valeur comprise dans (B3:B8) et te faire un total de 4126 . Cela revient donc à faire ton addition sans tenir compte de B4.
Tu peux aussi faire =SOMME(B3;B5;B6;B7;B8) et tu auras aussi 4114...

Voila j’espère que cela t'aidera ;)
Hors ligne Decks # Posté le 04/04/2012 à 11:45:11
Avatar

 Bonjours, je suis en train de créer un classeur pour apprendre les Capitales Mondiales mais j'ai un souci dans une formule qui me permettrai de générer un texte aléatoirement, là en l’occurrence si la réponse est fausse. ^^

Celle-ci se régénère à chaque fois que je modifie n'importe quelles case du classeur et moi je voudrais qu'elle se génère uniquement lorsque la case à sa gauche "B2" est modifiée.
Est-ce que c'est possible?? :euh:

Voila le code :

=SI(B2=0;"V/F";SI(B2=REPONSE!B3;"Bravo!";SI(B2<>REPONSE!B3;INDEX('Réponses Aléatoires'!R81:R90;ENT(10*ALEA()+1)))))

Merci :)
Hors ligne Djego # Posté le 13/04/2012 à 19:50:25
If you have to shoot shoot...
Avatar

Ville : Tours
Pays : France métropolitaine

 Bonjour Decks,

Je pense avoir saisi ta question sans en être sûr.

Tu aimerais que la valeur retourné par ta formule soit recalculée uniquement lorsque B2 est modifiée ?

Telle que ta formule est écrite ce ne sera pas possible... Il faudrait que tu mettes toute formule dans une nouvelle condition SI() qui prendrait en compte l'ancienne valeur de B2 pour la comparer à la nouvelle et faire le calcul si différent.

Mais encore une fois je ne sais pas si ton fichier tel qu'il a été conçu permet cette comparaison.

Autre chose, il possible de rendre les calculs manuels.
Pour cela il faut Cliquez sur le bouton Microsoft Office , sur Options Excel, puis sur la catégorie Formules. Ensuite dans la section Mode de calcul, sous Calcul du classeur, cliquez sur Manuel.
Ainsi pour rafraichir les valeurs de tes formules il suffira d'appuyer sur F9.
(sur Mac Exce>Préférences>Calcul>Calculer les Feuilles>cochez Manuellement)

NB: pour ta formule, il existe la fonction ALEA.ENTRE.BORNES(min;max) qui t'évitera de passer par ENT()

En espérant t'avoir aidé.

Voir tous les commentaires