Introduction
Le plus grand avantage des fonctions est qu'elles sont prêtes à être
utilisées immédiatement. Vous n'êtes pas obligé de les recréer ou de les
chercher dans des bouquins pour trouver celle dont vous avez besoin.
Comment écrire une formule
Il vous est possible d'écrire une formule en utilisant l'assistant des
fonctions qui est expliqué un peu plus loin sur cette page. L'assistant
vous permet aussi de voir toutes les fonctions disponibles et vous en
donne une brève description. Mais il est parfois plus simple de l'écrire.
Une formule commence toujours par les signes +, - ou = . Excel place
toujours un " = " devant les fonctions. La syntaxe d'une fonction est
toujours =NomDeLaFonction(paramère1;paramètre2 ...) .
La plupart des fonctions ont besoin d'informations supplémentaires pour
donner le résultat voulu. Par exemple, la formule =vpm() qui vous donne la
somme à payer à chaque paiement a besoin de trois informations, ou
paramètres, pour être utile. Ces informations sont le taux d'intérêt par
paiement, le nombre de paiements total et la valeur actuelle de l'emprunt.
Une hypothèque de 100 000 $ à un taux de 5% échelonné sur 25 ans vous
donne des paiements mensuels de =vpm(5%/12;25*12;100000) 584,59 $. Le
nombre de paramètres requis varie selon la fonction. Par exemple, les
fonctions =date() et =maintenant() n'ont pas besoin de paramètres.
Attention! Ceci s'applique pour toutes les fonctions.
Ne
laissez aucun espace entre le nom de la fonction et la première parenthèse
telle que: =somme (...). Elle ne donnera pas de résultat. Ceci est une
erreur commune parmi les débutants.
Excel offre plus d'une centaine des fonctions. Elles sont regroupées en
catégories que vous verrez au moment d'insérer une fonction. La prochaine
partie donne une brève explication des fonctions les plus utilisées dans
plusieurs catégories. Vous retrouverez les fonctions sur les bases de
données à la page de ce site sur les listes
des données.
Fonctions s'appliquant à des
chiffres
Pour compléter les prochaines fonctions, remplissez les cellules avec
les chiffres et le texte suivant dans les cellules appropriées.
Somme automatique 
La fonction =somme est très probablement la fonction que vous allez
utiliser la plus souvent. Excel vous offre deux manières d'utiliser cette
fonction; en écrivant la formule ou en utilisant le bouton de somme
automatique. Vous pouvez simplement écrire la formule =somme avec
l'étendue nécessaire. Il doit avoir le caractère " : " entre
l'adresse de début et l'adresse de fin. Par exemple, pour connaître la
somme de l'étendue de B1 à B3, la formule est =somme(b1:b3).
L'étendue peut inclure plusieurs lignes et plusieurs colonnes.
 Dans la cellule B5, entrez la formule suivante:
=somme(b1:b3) .
Le résultat devrait être de 600.
Changez l'une des valeurs des cellules B1 à B3.
La nouvelle somme va s'afficher dans la cellule B5.
Il y a plusieurs manières d'écrire une formule. On peut écrire une
formule en l'écrivant manuellement ou en utilisant les curseurs ou la
souris. Les trois prochains exercices vont démontrer comment utiliser ces
trois techniques pour connaître la somme des cellules de B1 jusqu'à
B3. Une fois que vous aurez maîtrisé ces techniques, vous pourrez
les utiliser pour n'importe quelle formule.
Le premier exercice consiste à utiliser les curseurs pour connaître la
somme de l'étendue des cellules B1 à B3.
Placez le pointeur dans la cellule B5.
Appuyez sur la touche +.
En utilisant le curseur pointant vers le haut pour vous rendre à
la cellule B1.
Remarquez qu'il y a un encadré autour la cellule qui clignote. C'est
pour vous assurer que vous sélectionnez la bonne cellule.
Appuyez sur la touche +.
Remarquez que le pointeur est revenu à la cellule B5; où s'écrit
la formule. Dès que vous appuyez sur l'une des opérations mathématiques (
+, -, *, /, ^ ) ou une parenthèse, le pointeur revient toujours à la
cellule ou s'écrit la formule.
En utilisant le curseur pointant vers le haut pour vous rendre à
la cellule B2.
Appuyez sur la touche +.
En utilisant le curseur pointant vers le haut pour vous rendre à
la cellule B3.
Appuyez sur la touche Entrée.
La formule est terminée. La cellule B5 va afficher le résultat
de la formule. Le prochain exercice consiste à sélectionner une étendue de
cellules en utilisant les curseurs.
Placez le pointeur dans la cellule B5.
Écrivez la formule suivante: =somme( .
En utilisant le curseur pointant vers le haut, déplacez le
pointeur à la cellule B1.
En gardant un doigt sur la touche MAJ (Shift), utilisez le
curseur pointant vers le bas pour vous rendre à la cellule B3.
L'étendue est sélectionnée.
Terminez la formule en appuyant sur la touche " ) ".
Appuyez la touche Entrée.
Il est donc possible de sélectionner une étendue de cellules en
utilisant les curseurs et la touche MAJ (Shift). Vous pouvez accomplir le
même résultat en utilisant la souris. C'est d'ailleurs le prochain
exercice.
Placez le pointeur dans la cellule B5.
Écrivez la formule suivante: =somme( .
En utilisant la souris, cliquez sur la cellule B1.
En gardant un doigt sur le bouton gauche de la souris,
sélectionnez les cellules B1 jusqu'à B3.
Terminez la formule en appuyant sur la touche " )
".
Appuyez la touche Entrée.
Ce dernier exercice démontre qu'il est possible de sélectionner une
étendue de cellules autant avec la souris qu'avec les curseurs.
Le bouton de somme automatique est plus simple. Mais, il a aussi ses
limitations.
Placez le pointeur dans la cellule B5.
Appuyez sur le bouton .
Excel va vous offrir la formule de somme de B1 jusqu'à B4
( =somme(b1:b4) ).
Pour confirmer la formule, appuyez sur la touche Entrée.
Mais comment la fonction détermine la bonne étendue? La fonction somme
regarde premièrement vers le haut pour déterminer s'il y a des chiffres.
S'il n'y a pas de chiffres dans les deux cellules directement au dessus de
la cellule active, d'où vous avez appuyé sur le bouton de somme
automatique, il va chercher pour des chiffres à la gauche de la cellule.
Dans l'exemple ci-dessus, il ne trouve pas de chiffres dans la cellule
juste au-dessus, B4, mais il trouve dans la cellule B3. La
fonction remonte la colonne jusqu'à ce qu'elle trouve une cellule vide. À
ce moment, elle s'arrête et vous suggère une étendue ( =somme(b1:b4) ).
Ce bouton est très pratique. Le problème est qu'il peut s'arrêter à la
mauvaise place. Si vous avez une cellule qui est vide parmi l'étendue, la
fonction va s'y arrêter; même si vous auriez aimé avoir des chiffres
au-dessus. Alors, assurez-vous que la fonction a sélectionné la bonne
étendue de cellules avant de confirmer en appuyant sur la touche
Entrée.
=Moyenne(étendu)
Pour trouver la moyenne d'une étendue (bloc) de cellules.
Dans la cellule B6, entrez la formule suivante:
=moyenne(b1:b3) .
=Min(étendu)
Pour trouver parmi une étendue de cellules celle qui a la plus petite
valeur.
Dans la cellule B7, entrez la formule suivante:
=min(b1:b3) .
=Max(étendu)
Pour trouver parmi une étendue cellules celle qui contient la plus
grande valeur.
Dans la cellule B8, entrez la formule suivante:
=max(b1:b3) .
Il y a plusieurs autres fonctions que vous pouvez utiliser.
Quelques-unes sont expliquées ci-dessous.
=Alea()
Fonction qui génère un chiffre entre 0 et 1. Elle est souvent utilisée
lors de simulations. Vous pouvez appuyer sur la touche F9 pour
qu'Excel génère un nouveau chiffre. On peut aussi combiner cette fonction
avec d'autres pour la rendre encore plus utile. L'exemple suivant consiste
à générer un chiffre entre 700 et 1000. La différence entre la limite
supérieure et inférieure est de 300 et le plancher est de 700. Vous pouvez
utiliser l'une de ces combinaisons de formules pour qu'Excel génère un
chiffre entre 700 et 1000: =ARRONDI((ALEA()*300)+700;0) ou
=ENT(ALEA()*300)+700 . Les prochaines lignes vont expliquer les autres
fonctions utilisées.
=Abs(cellule)
Affiche la valeur absolue, ou positive, d'un chiffre.
| Valeur |
Abs(valeur) |
| 25 |
25 |
| -43 |
43 |
=Arrondi(cellule;nombre de
décimales)
Permets d'arrondir un chiffre à la décimale de son choix. Il suffit de
le déterminer en inscrivant à quel décimal la fonction doit arrondir.
| Valeur |
Arrondi(valeur;0) |
Arrondi(valeur;1) |
| 45,15 |
45 |
45,2 |
| 45,49 |
45 |
45,5 |
| 45,54 |
46 |
45,5 |
Vous pouvez aussi arrondir sur des unités. Il faut que le nombre de
décimales soit négatif.
Ex.: =arrondi(45;-1) = 50
Ceci s'applique aussi aux deux prochaines fonctions.
=Arrondi.sup(cellule;nombre de
décimales)
Permets d'arrondir vers le haut dès qu'il y a une fraction.
| Valeur |
Arrondi.sup(valeur;0) |
Arrondi.sup(valeur;1) |
| 45,15 |
46 |
45,2 |
| 45,49 |
46 |
45,5 |
| 45,54 |
46 |
45,6 |
=Arrondi.inf(cellule;nombre de
décimales)
Permets d'arrondir vers le bas même si la fraction est supérieure ou
égale à 5.
| Valeur |
Arrondi.inf(valeur;0) |
Arrondi.inf(valeur;1) |
| 45,15 |
45 |
45,1 |
| 45,49 |
45 |
45,4 |
| 45,54 |
45 |
45,5 |
=Ent(cellule)
Affiche l'entier d'un chiffre. Elle élimine la fraction du chiffre.
| Valeur |
Ent(valeur) |
| 25,99 |
25 |
| 43,55 |
43 |
=fact(valeur)
Affiche le factoriel d'un chiffre. Utile lors de calculs de
probabilités. Ex.: =fact(3) = 6 (1 * 2 * 3)
=mod(valeur;diviseur)
Montre le montant qui reste d'une division. Ex.: =mod(13;4) = 1 13
divisé par 4 est égale à 3 et il reste 1.
=nb(étendu)
Affiche le nombre de cellules ayant des chiffres dans l'étendue
choisie.
=nbval(étentdu)
Affiche le nombre de cellules ayant un contenu, même si cela est du
texte.
=nb.si(étendu;critère)
Indique le nombre de cellules qui ont la même valeur que le critère. Le
prochain exercice démontre le nombre de cellules qui ont la valeur 100. La
formule à entrer dans la cellule B1 est =nb.si(A1:A3;100) .
Entrez les
chiffres et la formule dans les cellules appropriées.
Entrez le
chiffre 100 dans la cellule A2.
La fonction =nb.si() indique maintenant que deux cellules contiennent
la valeur 100. L'exemple précédent recherchait le nombre de cellules ayant
la valeur 100 dans la plage de cellules déterminée. Mais il est aussi
possible d'utiliser des critères autres que "égale à" et de différentes
manières. Voici quelques exemples.
Vous pouvez utiliser d'autres critères tels que >, <, >=,
<=, <> en combinaison avec du texte et des adresses de cellules.
L'exemple ci-dessus demande le nombre de cellules dont la valeur est
supérieure à 250.
Il est aussi possible d'utiliser cette fonction pour trouver des
doublons parmi une plage de données. Il faut utiliser la fonction =si() en
combinaison de nb.si() pour avoir le résultat voulu. La formule est
=si(nb.si(plage de cellules;critère)>1;"Doublon";"Unique") .
Placez le
pointeur sur la cellule B2.
Entrez la
formule suivante: =si(nb.si(A1:A3;A1)>1;"Doublon";"Unique")
.
Pour cet exemple, cette formule vérifie s'il y a une autre cellule qui
a la même valeur que la cellule A1. Si cela est le cas,
la formule va écrire "Doublon".
=somme.si(étendu;critère)
Placez le
pointeur sur la cellule B3.
Entrez la
formule suivante: =somme.si(A1:A3;100) .
Pour cet exemple, la fonction va additionner toutes les cellules de
l'étendue choisie dont la valeur est égale à 100. Puisqu'il y a deux
cellules avec la valeur 100, cela donne un résultat de 200.
=nb.vide(étendu)
Affiche le nombre de cellules vides dans l'étendue choisie.
=pair(valeur)
Affiche la prochaine valeur paire si la valeur n'est pas paire.
| Valeur |
pair(valeur) |
| 2 |
2 |
| 3 |
4 |
=impair(valeur)
Affiche la prochaine valeur impaire si la valeur n'est pas impaire.
| Valeur |
Impair(valeur) |
| 3 |
3 |
| 4 |
5 |
=racine(valeur)
Affiche la racine carrée d'un chiffre ou d'une cellule. Ex.: =racine(9)
= 3
=signe(valeur)
Affiche 1 si la valeur est positive, 0 pour 0 et -1 pour une valeur
négative. L'équivalent peut être accompli en utilisant
=si(valeur>0;1;si(valeur=0;0;-1)) . Cette fonction est utile pour
savoir si une valeur est positive ou non.
| Valeur |
=signe(valeur) |
| 45 |
1 |
| 0 |
0 |
| -45 |
-1 |
=tronque(valeur;nombre de
décimales)
Affiche le chiffre avec le nombre de décimales voulu. Est l'équivalent
de la fonction =arrondi.inf() .
Fonctions s'appliquant à du texte
Excel offre aussi des fonctions pour chercher de l'information sur du
texte. En voici quelques-unes.
=droite(texte;nombre de
caractères)
Cette fonction va afficher les derniers caractères d'une cellule ayant
du texte.
ex.: =droite("Ceci est un exemple";2) = le
=gauche(texte;nombre de
caractères)
Affiche le nombre de caractères que vous avez choisi du début du
texte.
Ex.: =gauche("Ceci est un exemple";2) = Ce
=concatener(première cellule;seconde
cellule)
Cette fonction vous permet de regrouper le contenu de plusieurs
cellules ensemble même si le contenu des cellules est différent comme du
texte, des chiffres, le résultat d'une formule ou des dates.
Ex.: =concatener("Ce";45) = Ce45
=nbcar(cellule ou texte)
Affiche le nombre de caractères contenu dans une cellule.
=romain(chiffre)
Convertis un chiffre sous forme arabe en chiffre romain. Ex.:
=romain(45) = XLV
=texte(chiffre;format texte)
Convertis un chiffre en format texte. Il prend la présentation comme le
reste des cellules ayant du texte. Il est encore possible de l'utiliser
pour les opérations mathématiques. Ex.: =texte(45;0) = 45 mais collé à la
bordure gauche de la cellule.
Fonctions s'appliquant sur des
dates
Excel offre aussi des fonctions permettant de retirer de l'information
à partir de cellules ayant des dates. Ces cellules contiennent beaucoup
d'informations. Voici les fonctions les plus utilisées.
=maintenant()
Affiche dans la cellule choisie le moment exact ou vous avez confirmé
la formule.
=date()
Affiche seulement dans la cellule la date de l'ordinateur.
=année(cellule ayant une date)
Affiche l'année contenue dans la date.
=mois(cellule ayant une date)
Affiche le mois contenu dans une date. Le résultat est toujours entre 1
et 12.
=jour(cellule ayant une date)
Affiche le chiffre du jour contenu dans une date). Le résultat est
toujours entre 1 et 31.
=joursem(cellule ayant une date)
Affiche un chiffre qui représente le jour de la semaine: 1 = dimanche,
2 = lundi ... 7 = samedi. On peut le combiner à la fonction =si() ou
=rechercheh() pour affiche en texte le jour de la semaine.
=heure()
Affiche l'heure d'une cellule ayant une date. Le résultat est toujours
entre 0 et 23.
=minute()
Affiche les minutes d'une cellule ayant une date. Le résultat est
toujours entre 0 et 59.
=seconde()
Affiche les secondes d'une cellule ayant une date. Le résultat est
toujours entre 0 et 59.
Insérer une fonction
Prenons l'exemple suivant. Vous voulez savoir combien seront vos
mensualités si vous prenez une hypothèque de 25 ans à un taux annuel de
6,5%. Il existe déjà une formule pour vous aider à trouver le montant.
Entrez le
texte, le chiffre et les formules suivantes.
Placez le pointeur dans la cellule B4.
Ces formules méritent un peu d'explications. Le taux doit être celui
pour chacune des périodes de paiement et non le taux annuel. Pour trouver
le nombre de paiements, il faut multiplier le nombre d'années par le
nombre de paiements faits durant une année.
Du menu
Insertion, sélectionnez l'option Fonctions.
OU
Appuyez
sur le bouton  .
 |
La colonne de gauche regroupe les
fonctions en catégories. La colonne de droite affiche le nom des
fonctions. En dessous de ces deux colonnes, il y a une brève
description de la fonction. Si cela ne suffit pas, il y a au bas de
la fenêtre le bouton d'aide . |
 |
Le compagnon d'Office va apparaître. Vous pouvez
lui demander plus d'information sur la fonction que vous voulez
choisir.
Pour
le moment, répondez "Non" à la question du
compagnon. |
De la
catégorie Finances, sélectionnez la fonction VPM.
Appuyez
sur le bouton OK.
La fenêtre de la fonction VPM va ensuite apparaître. Elle affiche des
cases qui ont besoin d'informations pour que la formule fonctionne
correctement. Les informations dont les titres sont en gras (Taux, Npm et
Va) sont obligatoires. Celles dont le titre est de taille normale (Vc et
Type) ne le sont pas.
Il faut donc entrer les montants dans les cases ou indiquer à la
fonction dans quelle cellule elle retrouvera les montants voulus. Pour
l'exercice, ce sera les cases B2, B3 et B1
respectivement.
Vous pouvez écrire les adresses des cellules dans les
cases ou utiliser les boutons et
pour sélectionner la cellule nécessaire.
Pour les
besoins de l'exercice, appuyez sur le bouton au bout de la case des taux.
La fenêtre de la fonction disparaît. Il ne reste que la case des taux.
Il faut maintenant choisir la cellule où se trouve de taux d'emprunt pour
la période.
Cliquez
sur la cellule B2.
Pour
terminer l'opération et revenir à la fenêtre de la fonction, appuyez sur
le bouton .
Utilisez
la même technique pour sélectionner la cellule pour le nombre de paiements
et le montant de l'hypothèque.
Appuyez
sur le bouton OK.
Le résultat de la fonction devrait être -337,60 $. Pour éviter le
montant négatif, vous pouvez mettre le signe - (moins) devant la fonction
(-VPM(...)) ou devant le montant d'hypothèque (-B1).
Excel vous offre plusieurs autres fonctions dans diverses catégories.
Expérimenter avec ceux-ci avant d'en avoir vraiment besoin pour un travail
ou au travail.
Parmi les fonctions, il y en a aussi pour vous aider à concevoir des
modèles plus proches de la réalité. La fonction SI vous permet d'adapter
le modèle selon des situations. Par exemple, y a-t-il une prime à payer? Y
a-t-il des heures supplémentaires à payer?
=Si()
La fonction =Si est très pratique dans un modèle parce qu'elle vous
offre des possibilités dans un modèle. Par exemple:
 À partir d'un
certain nombre d'heures, les employés ont droit à des heures
supplémentaires.
À partir d'un
certain salaire, le taux d'imposition fiscale augmente.
Lorsque l'entrepôt est plein, il y a des frais de surcharge pour
l'entreposage.
Lorsqu'un
client achète un certain nombre d'un produit, il a droit à un rabais.
Le nombre de possibilités est infini! Les autres fonctions, à
l'exception de =RechercheV et =RechercheH, n'offrent pas ce
que cette fonction peut offrir; un choix. Elle donne un certain résultat
si la condition est réalisée. Sinon, un autre résultat si la condition
n'est pas accomplie.
La fonction =Si a absolument besoin de trois paramètres pour
fonctionner. Il faut premièrement connaître la condition: qui a-t-il à
comparer? La seconde partie est ce que la fonction doit faire lorsque la
condition est vraie. La troisième partie est ce que la fonction doit faire
lorsque la condition est fausse. On peut résumé comme ceci:
=Si(condition;vrai;faux). Les ";" servent à séparer les différentes
parties de la fonction.
Ex.: =si(b1>=10;b3*0,1;0) Si le contenu de la cellule B1 est
supérieur ou égale à 10, affiche le résultat de B3 multiplié par 0,1.
Sinon affiche zéro.
Pour mieux vous faire comprendre le fonctionnement de la fonction =Si
et de son potentiel, la partie qui suit vous donne quelques exercices. On
se place dans le contexte d'un marchand qui veut offrir un rabais à ses
clients s'ils achètent une certaine quantité.
Avant de
commencer, ouvrez une nouvelle feuille de calcul ou un classeur.
Remplissez
les cellules suivantes avec le texte ou les formules suivantes.
Pour le premier exemple, le marchand va offrir un rabais encore
indéterminé si le client achète 10 items identiques ou plus. Cet exercice
est juste pour afficher si oui ou non le client a droit à un rabais.
Dans la
cellule B6, écrivez la formule suivante: =si(b1> =10;"Rabais
accordé";"Désolé, aucun rabais") .
Selon la quantité qui est entrée dans la cellule b1, la cellule b6 va
afficher soit "Rabais accordé" ou "Désolé, aucun rabais" (sans les
guillemets). Cette fonction a les trois parties. En premier, le
b1>=10 est la condition. Il vérifie si le contenu de la cellule
b1 est supérieur ou égale à 10. Si oui, la fonction affiche la seconde
partie de la fonction (Rabais accordé). Sinon, elle affiche la troisième
partie (Désolé, aucun rabais). Le résultat de cette fonction a affiché du
texte. La prochaine fonction aura pour résultat une formule ou un chiffre.
Le marchand s'est décidé. Il offre 10% de rabais à ceux qui achètent 10
items identiques ou plus. La prochaine formule va calculer ce rabais.
Dans la
cellule B7, entrez la formule suivante: =si(b1>=10;b3*0,1;0)
.
Pour cette formule, la condition est la même que la formule précédente.
Pourtant, le résultat est différent. Le rabais va changer selon la
quantité de la cellule b1. Si la quantité est égale ou plus grande que 10,
il y aura un rabais de 10% sur le total de la cellule b3. Sinon, il n'y a
pas de rabais. Donc, la valeur est zéro (0).
Le troisième exercice consiste à voir les fonctions =si imbriqués. Il
est possible d'avoir des fonctions si() dans des =si(). En fait, il est
possible d'avoir des fonctions dans des fonctions. Par exemple,
=abs(somme(b1:b200)) vous donne la valeur absolue de la somme des cellules
b1 à b200. Il est possible d'imbriquer jusqu'à six (6) si l'un dans
l'autre. Le prochain exercice vous en donne l'exemple.
Le marchand s'aperçoit que son rabais ne fait pas l'affaire de tous. Il
ajuste donc son tir. Il offre un rabais de 5% à ceux qui achètent 5 items
identiques ou plus. En plus, il donne toujours un rabais de 10% si un
client achète 10 items identiques ou plus.
Dans la
cellule B8, entrez la formule suivante:
=si(b1>=10;b3*0,1;si(b1>=5;b3*0,05;0)) .
La fonction vérifie premièrement si la quantité inscrite dans la
cellule est supérieure ou égale à 10. Si oui, la fonction va afficher le
rabais de 10% sur la valeur inscrite dans la cellule b3. Sinon, il y a le
second si qui vérifie si la quantité est supérieure ou égale à 5. Si oui,
elle va afficher un rabais de 5% sur la valeur de la cellule b3. Sinon, il
n'y a pas de rabais. Donc, la valeur affichée sera de zéro (0) .
Il y a une autre formulation qui aurait aussi donné le même résultat.
=si(b1>=5;si(b1>=10;b3*0,1;b3*0,05);0) . Au lieu de commencer par la
plus grande possibilité (>=10) et décent, cette formule commence par la
plus petite possibilité (>=5) et monte! Il y a certainement une ou deux
autres formulations qui sont possibles.
Il vous est possible "d'imbriquer" jusqu'à 6 niveaux de si. Mais rendu
à ce niveau, cela devient très difficile à gérer. Les fonctions
=recherchev et =rechercheh vont vous rendre la vie plus facile lorsque
vous avez plusieurs possibilités.
=ET() et =OU()
La fonction =Si() peut être amélioré lorsqu'elle est utilisée avec
d'autres fonctions telles que =ET() et =OU(). La partie qui suit démontre
le fonctionnement de ces deux fonctions suivi de comment elles peuvent
être utilisé avec =Si().
| =ET(condition1;condition2;condition3
...) |
Il faut au moins deux conditions. Mais
vous pouvez en ajouter d'autres. Toutes les conditions doivent être
remplies pour que la fonction affiche VRAI. Sinon, elle affichera
FAUX.
|
| =OU(condition1;condition2; condition3
...) |
Il faut au moins deux conditions. Vous
pouvez en ajouter d'autres. L'une des conditions doit être remplie
pour que la fonction affiche VRAI. Si aucune des conditions n?est
remplie, elle affichera FAUX. |
Il est temps de les voir en pratique.
Entrez les
chiffres et les formules dans les cellules appropriées.
Avec ces chiffres, vous devriez voir le texte "FAUX" dans les
cellules B1 et B2. Les chiffres ne répondent pas aux
conditions des formules. Pour la fonction =Et(), ni A1 et A2
est égale à 1, les conditions nécessaires pour que la fonction soit vrai.
C'est la même situation pour la fonction =OU().
Entrez le
chiffre 1 dans la cellule A1.
Cela devrait apporter un changement pour la fonction =OU() puisque
l'une des deux conditions est réalisée. La cellule B1 affiche
encore faux puisque les conditions ne sont pas encore remplies.
Entrez le
chiffre 1 dans la cellule A2.
Puisque les deux conditions demandées sont remplies, la fonction =ET()
affiche maintenant VRAI.
Entrez le
chiffre 2 dans la cellule A1.
Le résultat était prévisible. La cellule B1 affiche FAUX tandis que la
cellule B2 affiche encore vrai. Si vous voulez, vous pouvez ajouter
d'autres conditions à ces fonctions. Voici un petit exemple:
=OU(A1=1;A2=1;A3=1). Cela peut aller loin, très loin.
Il est possible d'ajouter ces fonctions, ou tout autre, dans la
fonction =Si(). Pour ces deux dernières fonctions, il est préférable de
les placer dans la partie des conditions.
=Si(et(a1=1;a2=1);1000;0)
=Si(ou(a1=1;a2=1);1000;0)
Ceci vous donne plus de potentiel pour la fonction =Si() puisque vous
pouvez couvrir plusieurs conditions en même temps.
= RechercheV(),
=RechercheH()
Les fonctions =recherchev et =rechercheh sont des formes de la fonction
=si un peu plus développé. Il est possible "d'imbriquer" plusieurs si pour
répondre à plusieurs conditions. Mais, la limitation de la fonction =si
est que vous pouvez imbriquer jusqu'à 6 niveaux de si seulement. Que faire
si vous voulez répondre à une question qui aurait besoin de plus de 6
niveaux? De plus, rendu à ce point, il devient difficile de gérer tous ces
=si imbriqués.
Il est cependant plus facile de le faire avec l'une des fonctions =
recherche. Surtout lorsque vous avez une grille ou un tableau de
comparaison. Par exemple, une grille pour déterminer la note des étudiants
ou une grille pour déterminer le taux d'imposition des employés.
Reprenons le dernier exercice de la fonction =si. Un marchant donne un
rabais de 5% sur un item si un client en achète 5 ou plus. Ce rabais monte
à 10% si le client en achète 10 ou plus.
Avec la fonction =si cela donnait la formule suivante:
=si(b1>=10;b3*0,1;si(b1>=5;b3*0,05;0)). Cette formule est
simple puisqu'il a seulement trois possibilités (10%, 5% et 0%). La
formulation devient plus difficile à concevoir lorsqu'on ajoute de plus en
plus de possibilités. La fonction =recherchev ou =rechercheh peut aider
dans cette situation.
La fonction à besoin de trois paramètres. Le premier est l'adresse de
la cellule qui sera comparée. Elle sera comparée au contenu du deuxième
paramètre qui contiendra l'adresse du bloc de cellules d'un tableau de
comparaison. Le troisième paramètre est pour indiquer qu'il faut
afficher le contenu de quelle colonne (pour =RechercheV) ou quelle ligne
(pour =RechercheH) du tableau de comparaison.
=recherchev(cellule à comparer;tableau de comparaison;index de colonne)
Avant d'essayer la fonction, il faut préparer le tableau de
comparaison.
Entrez les
chiffres suivants dans les cellules suivantes; soit dans les cellules
A13 à B15.
La première colonne du tableau de comparaison sert de grille de
comparaison. Elle doit toujours être en ordre croissant. Vous écrivez
toujours la valeur minimale,ou le plancher, pour atteindre ce niveau. Le
"plafond" est le "plancher" de la prochaine ligne du tableau. Donc, pour
la première ligne du tableau, le seuil minimal est zéro tandis que le
maximum est 5 exclusivement ou [0, 5[ pour ceux qui se souviennent de
leurs opérations mathématiques. C'est aussi pour cette raison que les
valeurs doivent toujours être en ordre croissant.
Donc, pour le tableau entre A13 à B15, le chiffre 0 de la
cellule A13 est le plancher. Son plafond est le contenu de la
cellule A14; soit 5. Donc tout ce qui est entre 0 inclusivement
jusqu'à 5 exclusivement ( [0, 5[ ) sera pour la première ligne du tableau.
C'est infiniment proche de 5 mais ce n'est pas 5. Tout ce qui est entre 5
et 10 ( [5, 10[ ) sera pour la seconde ligne. Pour les valeurs égales ou
supérieures à 10, ce sera la troisième ligne du tableau. Puisqu'il n'y a
pas d'autres lignes au-dessus du 10 de la cellule A15, son
"plafond" est l'infini.
La seconde, la troisième et les autres colonnes contiennent les
résultats que vous voulez afficher. Pour ce tableau, la seconde colonne
contient les taux de rabais selon la quantité achetée.
Dans la
cellule B9, entrez la formule suivante:
=rechercherv(b1;a13:b15;2) .
Selon la quantité qui est entrée dans la cellule b1, la fonction
=recherchev va comparer ce montant à son tableau de comparaison. Une fois
qu'elle saura sur quelle ligne s'arrêter, elle pourra afficher le contenu
de la colonne choisie. Par exemple, si la valeur est 6,5, la fonction
=recherchev va s'arrêter à la seconde ligne du tableau (entre 5 et 10).
Elle va ensuite afficher le contenu de la deuxième colonne de cette ligne;
soit 5%.
Pour vous aider à comprendre, voici un autre exercice avec une grille
pour des notes scolaires. Cet exercice consiste à afficher la bonne lettre
pour la note dans la cellule B2 selon le chiffre situé dans la
cellule B1.
Entrez le
texte et les chiffres suivants dans les cellules appropriées.
La cellule B1 va contenir le chiffre de la note. La cellule
B2 va contenir la fonction =recherchev qui va vérifier le chiffre
de la cellule B1 et affichera une lettre qui est l'équivalent de la
note. Les cellules a4 à b8 contiennent le tableau de comparaison. Le
tableau indique que la lettre sera "e" si vous avez une note en dessous de
50. Entre 50 et 60, la note sera de "d". Entre 60 et 75, la note sera de
"c". Entre 75 et 85, la note sera de "b". Et, pour une note de 85 ou plus,
la note sera "a". Pour que la fonction =recherchev fonctionne
correctement, elle a besoin de trois informations: l'adresse de la cellule
à comparer (B1), l'emplacement du tableau de comparaison (A4:b8) et de ce
tableau de quelle colonne afficher le résultat (la deuxième colonne ou 2)
Dans la
cellule B2, écrivez la formule suivante: =recherchev(b1;a4:b8;2)
.
La partie sur la fonction =recherchev va se terminer avec quelque chose
de difficile. Il consiste à utiliser la fonction =recherchev avec deux
variables. La première variable est identique à ce que vous avez vu
précédemment. La seconde variable consiste à changer dans le tableau de
comparaison colonne que la fonction va chercher l'information à afficher.
À date, vous avez toujours écrit le chiffre 2 pour le troisième
paramètre de la fonction =recherchev. La fonction va donc toujours
chercher l'information à afficher dans la deuxième colonne du tableau de
comparaison. Le prochain exercice consiste à changer dans quelle colonne
la fonction =recherchev va chercher l'information à afficher selon le type
de client. Pour cet exercice, il y a trois catégories de clients: type 1,
2 et 3. La fonction devra chercher l'information à afficher dans la
deuxième, la troisième ou la quatrième colonne du tableau; soit le type de
client + 1 puisque la première colonne du tableau est utilisée pour la
grille de comparaison pour déterminer la quantité du rabais.
Écrivez
les chiffres suivants dans les cellules appropriées.
 et
Dans la
cellule b10, écrivez la formule suivante:
=recherchev(b1;a18:d20;d1 + 1) .
Essayez les combinaisons suivantes de chiffres dans les cellules
B1 et D1.
| B1 |
D1 |
Résultat |
| 4 |
1 |
0% |
| 7 |
1 |
2,5% |
| 7 |
2 |
5% |
| 7 |
3 |
7,5% |
| 10 |
3 |
12,5% |
| -5 |
1 |
#N/A |
| 10 |
4 |
#REF! |
Toutes les valeurs fonctionnent sauf pour les deux derniers cas. Dans
le cas de la quantité négative, ce montant est plus petit que le seuil
minimal de la première ligne du tableau de comparaison (0). Donc, cette
valeur ne s'applique pas au tableau de comparaison.
Pour le dernier cas, la fonction essaie de chercher l'information à
afficher à l'extérieur du tableau de comparaison. Le tableau est composé
de quatre colonnes. Mais, la fonction =recherchev cherche l'information
dans la cinquième colonne; là où il n'y a pas d'informations.
La quatrième paramètre optionel de Recherchev()
Pour rendre la fonction =recherchev ou =rechercheh encore plus
puissante, il est aussi possible de comparer du texte. La première colonne
du tableau de comparaison peut contenir des mots au lieu de chiffres.
Comme pour les chiffres, les noms doivent être en ordre croissant.
Contrairement aux chiffres, le texte doit être exact. Par exemple, le
texte ne peut pas être entre Anjou et Avignon. Sinon, attendez-vous à des
résultats bizarres comme vous verrez ci-dessous.
Entrez le
texte, les chiffres et les formules suivantes dans les cellules
appropriées.
Dans la
cellule B1, entrez Avignon.
Le résultat va afficher 12% dans la cellule B2. Mais il faut
s'attendre à des résultats bizarres dans un cas.
Dans la
cellule B1, entrez Tokyo.
Le résultat contenu dans est de 3%! Pourquoi? Parce qu'il est au-dessus
de Bruxelles. Donc, il donne le contenu de la dernière ligne du tableau de
comparaison.
Dans la
cellule B1, entrez At .
Le résultat de la cellule B2 sera de 17% parce qu'il est entre
Anjou et Avignon. Donc, bien que très pratique pour comparer des chiffres
d'une étendue, elle l'est beaucoup mois pour du texte.
C'est exactement pour cette situation que cette fonction offre un
quatrième paramètre optionnel. Il vous permet de décider comment la
fonction doit opérer. Doit-elle chercher entre telle et telle valeur ou
chercher une valeur exacte? Ce quatrième paramètre avec l'option faux ou 0
force la fonction à chercher la valeur exacte. Si le quatrième paramètre
est égale à vrai, à 1 ou n'est pas présent, la fonction recherchev va
chercher entre tel ou tel valeur. Donc, si la fonction aurait été écrite
de cette façon =RECHERCHEV(B1;A4:B7;2;FAUX), il faudrait que le texte qui
serait dans la cellule B1 soit écrit exactement de la même façon
que l'une des valeurs du tableau de comparaison.
Dans la
cellule B2, entrez la fonction suivante:
=RECHERCHEV(B1;A4:B7;2;FAUX) .
Dans la
cellule B1, entrez At .
Le résultat sera #N/A pour indiquer qu'elle est non applicable dans ce
cas. Ceci est vrai puisque At n'est pas dans le tableau de
comparaison.
| 
A
noter:
Si CNUM() traite une chaîne de caractères qui n'est pas
reconnaissable comme une suite numérique, elle renvoie
#VALEUR!. Par exemple, si en cellule A1 nous avons 123abc, =CNUM(A1)
renvoie #VALEUR!.
Astuce