Tutoriel des Fonctions avancées

Texte- DateLogiques

RechercheIndexEquiv

Elodie Aaron, Gina Gitton

Fonction « ET » Renvoie VRAI si tous les arguments ont pour

résultat la valeur VRAI ; renvoie FAUX si au moins l’un des arguments a pour résultat la valeur FAUX.

Fonctions logiques

Formule Description Résultat

=ET(1<A2; A2<100)Affiche VRAI si le nombre de la cellule A2 est compris entre 1 et 100. Sinon, elle affiche FAUX.

VRAI

=SI(ET(1<A3;A3<100);A3;"La valeur est en dehors de la plage.")

Affiche le nombre de la cellule A3, s’il est compris entre 1 et 100. Sinon, elle affiche le message "La valeur est en dehors de la plage".

La valeur est en dehors de la plage.

=SI(ET(1<A2;A2<100);A2;"La valeur est en dehors de la plage.")

Affiche le nombre de la cellule A2, s’il est compris entre 1 et 100. Sinon, elle affiche un message.

50

Fonctions logiquesFonction « ET »

Fonction « SIERREUR »Renvoie une valeur que vous spécifiez si une formule génère une erreur ; sinon, elle renvoie le résultat de la formule. La fonction SIERREUR permet d'intercepter et de gérer des erreurs présentes dans une formule

Fonctions logiques

Quota Unités vendues  Rapport

210 35 6

55 0 IMP

  23 0Formule Description (résultat)  =SIERREUR(A2/B2, "Erreur de calcul")

Recherche une erreur dans le premier argument de la formule (divise 210 par 35), ne trouve aucune erreur, puis renvoie le résultat de la formule (6).

 

=SIERREUR(A3/B3, "Erreur de calcul")

Recherche une erreur dans le premier argument de la formule (divise 55 par 0), trouve une erreur de division par 0, puis renvoie l'argument valeur_si_erreur (Erreur de calcul).

 

=SIERREUR(A4/B4, "Erreur de calcul")

Recherche une erreur dans le premier argument de la formule (divise "" par 35), ne trouve aucune erreur, puis renvoie le résultat de la formule (0).

 

Fonctions logiquesFonction « SIERREUR »

Fonction NON

Inverse la valeur logique de l'argument. Utilisez NON lorsque vous souhaitez être certain qu'une valeur est différente d'une valeur spécifique.

Fonctions logiques

Formule Description (résultat)

=NON(FAUX) Inverse la valeur logique de l'argument FAUX (VRAI)

=NON(1+1=2) Inverse une équation dont le résultat est VRAI (FAUX)

Fonctions logiquesFonction NON

  Fonction OU Renvoie la valeur VRAI si un argument est

VRAI et FAUX si tous les arguments sont FAUX.

Fonctions logiques

Formule Description (résultat)

=OU(VRAI) Un argument est VRAI (VRAI)

=OU(1+1=1;2+2=5) Tous les arguments ont pour résultat FAUX (FAUX)

=OU(VRAI;FAUX;VRAI) Au moins un argument est VRAI (VRAI)

La fonction RECHERCHE renvoie une valeur provenant soit d’une plage (plage : deux cellules au minimum d’une feuille de calcul. Une plage peut contenir des cellules adjacentes ou non adjacentes.) à une ligne ou une colonne, soit d’une matrice (matrice : permet de créer des formules uniques permettant d’obtenir plusieurs résultats et qui agissent sur un groupe d’arguments répartis dans des lignes et des colonnes. Une plage matricielle partage une même formule tandis qu’une constante matricielle est un groupe de constantes qui sert d’argument.). La fonction RECHERCHE a deux formes syntaxiques : la forme vectorielle et la forme matricielle.

Fonctions Recherche

Fréquence Couleur  

4,14 rouge  

4,19 orange  

5,17 jaune  

5,77 vert  

6,39 bleu  

Formule Description Résultat

=RECHERCHE(4,19;A2:A6;B2:B6)Recherche 4,19 dans la colonne A, et renvoie la valeur de la colonne B qui se trouve sur la même ligne.

orange

=RECHERCHE(5;A2:A6;B2:B6)Recherche 5,00 dans la colonne A, fait correspondre à cette valeur la plus petite valeur suivante (4,19) et renvoie la valeur de la colonne B qui se trouvent sur la même ligne.

orange

=RECHERCHE(7,66;A2:A6;B2:B6)Recherche 7,66 dans la colonne A, fait correspondre à cette valeur la plus petite valeur suivante (6,39) et renvoie la valeur de la colonne B qui se trouvent sur la même ligne.

bleu

=RECHERCHE(0;A2:A6;B2:B6)Recherche 0 dans la colonne A, et renvoie une erreur car 0 est inférieur à la plus petite valeur de l’argument vecteur_recherche A2:A7.

#N/A

Fonctions Recherche

La fonction TEXTE convertit une valeur numérique en texte et vous permet de spécifier le format d’affichage en utilisant des formats de chaînes spéciaux. Cette fonction est utile pour afficher des nombres dans un format plus lisible ou combiner des nombres avec du texte ou des symboles. Par exemple, supposons que la cellule A1 contienne le nombre 23,5. Pour afficher ce nombre sous forme de valeur en euros, vous pouvez utiliser la formule suivante :

=TEXTE(A1;"€0,00") Dans notre exemple, Excel affiche €23,50. Vous pouvez également afficher le format des nombres en utilisant les

commandes du groupe Nombre de l’onglet Accueil du ruban. En revanche, ces commandes fonctionnent uniquement sur des cellules entièrement numériques. Si vos voulez afficher le format d’un nombre et le combiner avec du texte, il est préférable d’utiliser la fonction TEXTE. Par exemple, vous pouvez ajouter du texte à la formule précédente :

=TEXTE(A1;"€0,00") & " l’heure" Excel affiche €23,50 l’heure.

Fonction Texte

Vendeur Ventes Données

Duerr 2 800 39300,625

Weiler 0,4  

Formule Description Résultat

=A2&" a vendu pour "&TEXTE(B2;"0,00 F")&" d'articles."

Combine la cellule A2, la chaîne de texte « a vendu », la cellule B2 (au format monétaire) et la chaîne de texte «  d’articles. » dans une phrase.

Duerr a vendu pour € 2 800,00 d’articles.

=A3&" a vendu "&TEXTE(B3;"0%")&" du total des ventes."

Combine la cellule A3, la chaîne « a vendu », la cellule B3 (affichée sous forme de pourcentage) et la chaîne de texte « du total des ventes. » dans une phrase.

Weiler représentait 40% du total des ventes.

="Date: " & TEXTE(C2;"aaaa-mm-jj")Affiche la valeur de C2 dans un format de date qui affiche l’année sur 4 chiffres, le mois sur 2 chiffres et le jour sur 2 chiffres.

Date : 2007-08-06

="Date-heure: " & TEXTE(C2;"m/j/aaaa h:mm AM/PM") Affiche la valeur de C2 dans un format de date courte sur 12 heures.

Date-heure: 8/6/2007 3:00 PM

=TEXTE(C2;"0.00E+00") Affiche la valeur de C2 en format scientifique (exposant). 3.93E+04

TEXTE(C2;"€#,##0.00") Affiche la valeur de C2 dans un format monétaire, avec un séparateur de milliers.

€39,300.63

Fonction Texte

La fonction DATE renvoie le numéro de série séquentiel qui représente une date particulière. Par exemple, la formule

=DATE(2008;7;8) renvoie 39637, le numéro de série qui représente 8/7/2008.  Remarque   Si le format de cellule était Standard avant que la

fonction ne soit entrée, le résultat est mis en forme en tant que date, et non en tant que nombre. Pour afficher le numéro de série ou modifier la mise en forme de la date, sélectionnez un autre format de nombre dans le groupe Nombre de l’onglet Accueil.

La fonction DATE est particulièrement utile lorsque l’année, le mois et le jour sont présentés par formules ou références de cellules. Par exemple, une feuille de calcul peut contenir des dates dans un format non reconnu par Excel, tel que AAAAMMJJ. Vous pouvez utiliser la fonction DATE avec d’autres fonctions pour convertir les dates en numéro de série reconnu par Excel.

Fonction Date

Année Mois Jour

2 008 1 1

Données

20081125

Formule Description Résultat

=DATE(A2; B2; C2)

Date sérielle de la date obtenue en utilisant les cellules A2, B2 et C2 en tant qu’arguments pour la fonction DATE avec le calendrier depuis 1900.

1/1/2008 ou 39448

=DATE(ANNÉE(AUJOURDHUI());12;31)

Date sérielle du dernier jour de l’année en cours.

31/12/nnnn ou le numéro de série séquentiel équivalent (la valeur réelle dépend de l’année en cours)

=DATE(GAUCHE(A4;4);MIL(A4;5;2); DROIT(A4;2))

Formule qui convertit une date à partir du format AAAAMMJJ en date sérielle.

25/11/2008 ou 39777

Fonction Date

La fonction EQUIV recherche un élément spécifique dans une plage de cellules, puis renvoie la position relative de l’élément dans la plage. Par exemple, si la plage A1:A3 contient les valeurs 5, 25 et 38, la formule.

Utilisez la fonction EQUIV plutôt qu’une des fonctions RECHERCHE lorsque vous avez besoin de la position d’un élément dans une plage et non de l’élément en tant que tel. Par exemple, vous pouvez utiliser la fonction EQUIV pour fournir une valeur pour l’argument no_lig de la fonction INDEX.

Syntaxe : EQUIV(valeur_cherchée, matrice_recherche, [type])

Fonction Equiv

L’argument valeur_cherchée peut être une valeur (nombre, texte ou valeur logique) ou une référence de cellule à un nombre, à du texte ou à une valeur logique.

matrice_recherche    Obligatoire. Plage de cellules dans laquelle s’effectue la recherche.

type    Facultatif. Nombre -1, 0 ou 1. L’argument type indique comment Excel compare l’argument valeur_cherchée aux valeurs de l’argument matrice_recherche. Valeur par défaut de cet argument : 1.

Le tableau suivant décrit comment la fonction recherche les valeurs en fonction du paramètre de l’argument type.

Fontion equiv

La fonction EQUIV renvoie la position de la valeur équivalente dans l’argument matrice_recherche et non la valeur en elle-même. Par exemple, EQUIV("b".{"a","b","c"};0) renvoie 2, c’est-à-dire la position relative de « b » dans la matrice {"a","b","c"}.

La fonction EQUIV ne distingue pas les majuscules des minuscules lorsqu’elle donne l’équivalence de valeurs de texte.

Si la fonction EQUIV ne trouve pas de valeur équivalente, elle renvoie la valeur d’erreur #N/A.

Si la valeur de l’argument type est 0 tandis que celle de l’argument valeur_cherchée représente une chaîne de texte, vous pouvez utiliser les caractères génériques, à savoir l’astérisque (*) et le point d’interrogation (?), dans l’argument valeur_cherchée. Le point d’interrogation correspond à un caractère et l’astérisque à une séquence de caractères. Si vous voulez rechercher un véritable point d’interrogation ou astérisque, tapez un tilde (~) avant ce caractère.

Renvoie une valeur ou une référence à une valeur provenant d’un tableau ou d’une plage. La fonction INDEX existe sous deux formes : la forme matricielle et la forme référentielle.

Renvoie la valeur d’un élément d’un tableau ou d’une matrice, sélectionné à partir des index de numéros de ligne et de colonne.

Utilisez la forme matricielle si le premier argument de la fonction INDEX est une constante matricielle.

INDEX(matrice;no_lig;no_col) matrice     est une plage de cellules ou une constante matricielle. Si l’argument matrice contient une seule ligne ou colonne, l’argument no_lig ou

no_col est facultatif. Si l’argument matrice comporte plusieurs lignes et plusieurs colonnes et que seul

l’argument no_lig ou no_col est utilisé, la fonction INDEX renvoie une matrice des valeurs de la ligne ou de la colonne entière de la matrice.

no_lig     sélectionne la ligne de la matrice dont une valeur doit être renvoyée. Si l’argument no_lig n’est pas spécifié, l’argument no_col est obligatoire.

no_col     sélectionne la colonne de la matrice dont une valeur doit être renvoyée. Si l’argument no_col n’est pas spécifié, l’argument no_lig est obligatoire.

Notes

Fonction index

Si les arguments no_lig et no_col sont tous deux utilisés, la fonction INDEX renvoie la valeur de la cellule située à l’intersection des arguments no_lig et no_col.

Si vous spécifiez la valeur 0 (zéro) pour l’argument no_lig ou no_col, la fonction INDEX renvoie respectivement la matrice des valeurs de la colonne ou de la ligne entière. Pour utiliser les valeurs renvoyées sous la forme d’une matrice, entrez la fonction INDEX en tant que formule matricielle, dans une plage horizontale de cellules pour une ligne et dans une plage verticale de cellules pour une colonne. Pour entrer la formule matricielle, appuyez sur Ctrl+Maj+Entrée.

Les arguments no_lig et no_col doivent pointer sur une cellule appartenant à l’argument matrice. Sinon, la fonction INDEX renvoie la valeur d’erreur #REF!.

Fonction index

Forme référentielle Renvoie la référence de la cellule située à l’intersection d’une ligne et d’une colonne

déterminées. Si l’argument réf est constitué de sélections non adjacentes, vous pouvez choisir la sélection à consulter.

INDEX(réf;no_lig;no_col;no_zone) réf     est une référence à une ou plusieurs plages de cellules. Si vous entrez une plage non adjacente comme argument réf, mettez l’argument réf entre

parenthèses. Si chaque zone de l’argument réf contient une seule ligne ou colonne, l’argument no_lig ou

no_col, respectivement, est facultatif. Par exemple, dans le cas d’un argument réf à une seule ligne, utilisez la fonction INDEX(réf;;no_col).

no_lig     est le numéro de la ligne de référence à partir de laquelle une référence doit être renvoyée.

no_col     est le numéro de la colonne de référence à partir de laquelle une référence doit être renvoyée.

no_zone     sélectionne la plage de référence pour laquelle l’intersection de no_col et no_lig doit être renvoyée. La première zone sélectionnée ou entrée porte le numéro 1, la deuxième le numéro 2, et ainsi de suite. Si l’argument no_zone n’est pas spécifié, la fonction INDEX utilise la zone numéro 1.

Par exemple, si l’argument réf décrit les cellules (A1:B4;D1:E4;G1:H4), l’argument no_zone 1 correspond à la plage A1:B4, l’argument no_zone 2 à la plage D1:E4 et l’argument no_zone 3 à la plage G1:H4.

Fonction index

Notes Lorsque les arguments réf et no_zone ont

sélectionné une plage précise, les arguments no_lig et no_col permettent d’identifier une cellule spécifique : l’argument no_lig 1 représente la première ligne de la plage, l’argument no_col 1 la première colonne, et ainsi de suite. La référence renvoyée par la fonction INDEX est celle de l’intersection des arguments no_lig et no_col.

Si vous spécifiez la valeur 0 (zéro) pour l’argument no_lig ou no_col, la fonction INDEX renvoie respectivement la référence de la colonne ou de la ligne entière.

Fonction index

Les arguments no_lig, no_col et no_zone doivent pointer sur une cellule appartenant à l’argument réf. Sinon, la fonction INDEX renvoie la valeur d’erreur #REF!. Si les arguments no_lig et no_col ne sont pas spécifiés, la fonction INDEX renvoie la zone de l’argument réf définie par l’argument no_zone.

Le résultat de la fonction INDEX est une référence et est interprété comme tel par les autres formules. Selon la formule, la valeur de la fonction INDEX peut être utilisée comme une référence ou une valeur. Par exemple, la formule CELLULE("largeur";INDEX(A1:B2;1;2)) équivaut à la formule CELLULE("largeur";B1). La fonction CELLULE utilise la valeur renvoyée par la fonction INDEX comme une référence de cellule. Par ailleurs, une formule telle que 2*INDEX(A1:B2;1;2) traduit la valeur de la fonction INDEX en nombre dans la cellule B1.

Fonction index