FONCTIONS : QUELQUES EXEMPLES

Cette fonction permet de rechercher une valeur dans une liste de donnée. Cette recherche est effectuée dans la colonne de gauche de la liste, lorsqu'elle est trouvée, Excel se déplace vers la droite sur la même ligne et lit la valeur dans la colonne indiquée.
Les paramètres dont elle a besoin sont donc :

• valeur_cherchée : la valeur recherchée
• table_matrice : la plage dans laquelle la valeur est recherchée
• no_index_col : l'index de la colonne renvoyant la donnée
• valeur_proche: VRAI pour rechercher la valeur la plus proche dans la 1ère colonne de la plage et FAUX pour rechercher la valeur exacte.

Pour fonctionner correctement la fonction a besoin d'une certaine structure :

• La colonne dans laquelle doit se faire la recherche doit être la 1ère colonne de la plage.
• la plage doit contenir la colonne renvoyant la donnée.
• la 1ère colonne de la plage a l'index 1.

Par exemple dans une liste contenant les champs Date, Produit, Prix unitaire, Taux TVA, Nombre, respectivement dans les colonnes A, B, C, D, E et la ligne 1, les données s'étendant jusqu'à la ligne 100. Pour rechercher :

• le prix unitaire à partir du Produit, table_matrice doit être au moins B1:C100 et no_index_col est 2.
• le nombre à partir de la date, table_matrice doit être au moins A1:E100 et no_index_col est 5

La fonction précédente comme vous le constatez à ces limites : la colonne de recherche doit se trouver à gauche de la colonne de résultat. Pour palier à ceci vous pouvez utilisez conjointement 2 fonctions :

• EQUIV, qui va vous donner l'emplacement d'une valeur dans une colonne ou une ligne,
• INDEX, qui renvoie une donnée d'une liste (en ligne ou colonne) à partir de sa position dans la liste.

Reprenons l'exemple précédent :

Si vous voulez savoir quel est le produit vendu à 3,00 € (attention cela ne me donnera que le 1er produit à ce prix s'il y en a plusieurs tout comme RECHERCHEV), procédez ainsi :

• Dans une cellule, par exemple F1, tapez 3 (c'est à dire la valeur à rechercher)
• Dans une autre cellule, par exemple G1, tapez la formule suivante en considérant que le tableau ci-dessus se trouve en A1:E5 :
  =EQUIV(F1;C1:C5;0)
  F1 est la valeur que vous recherchez
  C2:C5 est le tableau dans lequel vous recherchez la valeur, ici je n'inclue pas les titres
  0 doit être mis pour rechercher une valeur exacte (pour les autres valeurs voir l'aide)
• Cette formule vous renverra 3 car la valeur 3 se trouve en 3ème position dans la plage C2:C5
• Dans une cellule, par exemple H1, tapez la formule suivante :
  =INDEX(B2:B5;G1)
  B2:B5 est le tableau dans lequel je veux lire le résultat (tout comme pour la recherche je n'inclue pas les titres). Ce tableau doit avoir la même dimension que celui dans lequel on a effectué la recherche.
  G1 représente la position où je dois lire la donnée dans ce tableau, ici la 3ème position. Ce qui vous donne bien bb

Vous pouvez combiner ces 2 fonctions en utilisant la formule suivante :
=INDEX(B2:B5;EQUIV(F1;C1:C5;0))

Cette fonction permet d'effectuer la somme d'une plage selon un critère. Ce critère peut s'appliquer sur la plage que vous voulez sommer ou sur une autre plage avec la seule condition que les 2 plages soient de même taille. Sa syntaxe est SOMME.SI(plage;critère;somme_plage) où

• plage représente la plage sur laquelle le critère sera appliquée, elle peut-être la même que somme_plage
• critère doit être sous la forme d'un nombre, d'un texte ou d'une expression de comparaison. Ce critère doit être au format texte (donc entre guillemet). L'opérateur & vous permettra de transformer au format texte toute expression dépendant d'une cellule. Vous pouvez avoir :
  • "10" --> = 10
  • A1 --> = le contenu de A1 (ne pas mettre =A1, mais si vous le désirez vous pouvez mettre "="&A1)
  • "un texte" --> = un texte
  • ">10"--> Supérieur à 10
  • ">=10"--> Supérieur ou égal à 10
  • "<>10"-->  Différent de 10 
  • "<10"--> Inférieur à 10
  • "<=10"--> Inférieur ou égal à 10
  • ">"&A1 (et non >A1) --> Supérieur au contenu de A1
  • ">="&A1 (et non >=A1) --> Supérieur ou égal au contenu de A1
  • "<>"&A1 (et non <>A1) -->  Différent du contenu de A1 
  • "<"&A1 (et non <A1) --> Inférieur au contenu de A1
  • "<="&A1 (et non <=A1) --> Inférieur ou égal au contenu de A1
  • "<>un texte"--> Différent de 'un texte'
  • "au*" ou "*au" ou "*au*" --> tout les mots commençant par 'au', tout les mots finissant par 'au', tous les mots contenant au.
    Une étoile permet de remplacer n'importe quel caractère et ne précise pas le nombre de caractères remplacés. au* trouvera aux et autres
    Vous pouvez également utiliser un ?, celui-ci remplacera 1 seul caractère, au? trouvera aux mais pas autres.
    Si dans cette formule vous remplacez au par &A1 ( "*"&A1&"*" par exemple), la recherche se fait sur le contenu de A1.
• somme_plage est la plage sur laquelle la somme sera effectuée.

La fonction va parcourir la plage plage et pour chaque cellule X , elle va vérifier si la cellule Y, de même position dans la plage somme_plage, répond par vrai à la condition indiqué dans critère , si oui elle ajoute la valeur de la cellule X au résultat.

Exemple 1 : vous désirez sommer uniquement les valeurs supérieures à 100 dans une plage A1:A10. La formule sera donc :
=SOMME.SI(A1:A10;">100";A1:A10)

Exemple 2 : vous avez une plage B1:B10 qui indique le nombre d'enfants, une autre plage C1:C10 qui indique la prime. Vous désirez connaître la somme des primes pour 3 enfants, la formule est :
=SOMME.SI(B1:B10;"3";C1:C10)

Cette fonction compte le nombre de cellules correspondant à un critère dans une plage. Elle fonctionne à peu près comme la fonction précédente. Sa syntaxe est nb.si(plage;critère)

• plage représente la plage qui va être comptée.
• critère doit être sous la forme d'un nombre, d'un texte ou d'une expression de comparaison. Ce critère doit être au format texte. Reportez-vous à la fonction SOMME.SI pour connaître toutes ses possibilités.

La fonction parcourt chaque cellule de la plage plage , si le contenu de cette cellule correspond à la condition indiqué dans critère , si oui elle ajoute 1 au résultat.

Exemple : Dans une plage D1:D10, vous avez une liste de Noms et vous désirez compter le nombre de Dupont :
=NB.SI(D1:D10;"Dupont")

Une année est bissextile si le 29 février de cette année existe. En conséquence la formule suivante, considérant que la cellule A1 contient une année :
=DATE(A1;2;29)
renvoie le 1er mars de l'année si celle-ci n'est pas bissextile et le 29 février si elle l'est.
Pour tester il suffit donc d'établir la formule suivante :
=SI(JOUR(DATE(A1;2;29))=29;"bissextile";"normale")

Si vous désirez faire le test sur une date, la formule doit être (A1 contenant la date sur laquelle vous désirez faire un test):
=SI(JOUR(DATE(ANNEE(A1);2;29))=29;"bissextile";"normale")

Plutôt que de créer un graphique de type histogramme, vous pouvez utiliser une fonction pour mettre dans une cellule une barre horizontale dont la taille dépend d'une valeur d'une cellule.

Tapez par exemple en A1 la valeur 10. En B1 tapez la formule suivante : =REPT("n";D119) et appliquez la police Wingdings à cette cellule B1. Vous aurez alors ce résultat.

Tout d'abord, pour travailler correctement avec les dates et les heures sous Excel, vous devez utilisez :

• / comme séparateur de dates et non un espace ou un tiret (ex. : 12/07/02 et non 12 07 02 ou 12-07-02)
• : comme séparateur d'heures (ex. : 12:37:40).
  Les premiers chiffres correspondent toujours aux heures, si vous ne devez entrer que des minutes et secondes, vous devez entrez 0: au début de votre saisie. Ex : 0:30:10

Si vous entrez correctement les dates et heures, Excel les aligne à droite car il les considère comme des valeurs numériques.
Vous pouvez maintenant établir des calculs sur ces dates et valeurs comme n'importe quelle valeur numérique (les sommer, les soustraire, les moyenner,...). Il faut tout de même savoir qu'Excel considère que :

• 1 journée est égale à 1
• 1 heure est égale à 1/24
• 1 mn est égale à 1/24/60
• 1 s est égale à 1/24/60/60

Donc pour faire des calculs sur les heures il faut en tenir compte. Si les données sont entrées comme indiquées ci-dessus, pour faire des calculs entre les heures, vous les manipulez comme des nombres quelconques. Par contre si vous devez convertir (A1 contient la donnée) :

• des heures décimales (8,5) en heures normales (8:30) : =A1/24 (faites attention à mettre le format heure)
• des heures normales (8:30) en heures décimales : =A1*24 (faites attention à remettre le format Nombre ou standard)
• des minutes (365) en heures décimales (6,08) : =A1/60
• des minutes (365) en heures normales (6:05:00) : =A1/24/60
• des heures normales (6:05:00) en minute (365): =A1*24*60 (faites attention à remettre le format Nombre ou standard)

Par ailleurs, si vous devez afficher des heures supérieures à 23:59:59, il vous faut choisir un format de nombre spécifique : dans Format/cellule, onglet Nombre, choisissez la catégorie Heure, puis dans la liste Type, choisissez la ligne affichant des heures supérieurs à 24 (dans la version 2000 : 37:30:55).

Excel ne sait pas afficher des heures négatives, il affiche des #. Si vous devez effectuer un autre calcul sur ce résultat négatif, il n'y a aucun problème, mais il ne peut les afficher. En conséquence vous devez utiliser une formule pour les afficher avec un signe -, dans l'exemple suivant nous considérons qu'en C1 nous avons un résultat d'heures :
=SI(C1>0;TEXTE(C1;"hh:mm:ss");"-"&TEXTE(ABS(C1);"hh:mm:ss"))
Bien entendu vous pouvez choisir un autre format d'heures.

Dans une date, vous avez un jour, un mois et une année, mais plusieurs façons de les afficher (01/09/02 ou 1 Septembre 2002), pour les afficher à sa convenance, il faut comprendre comment fonctionne le format de date :

• le jour est représenté par j
  • j affiche le jour avec au minimum 1 chiffre, ex. : 1 ou 12
  • jj affiche le jour avec 2 chiffres, ex. : 01 ou 12
  • jjj affiche le jour en lettre abrégé (3 lettres), ex. : dim ou ven
  • jjjj affiche le jour en lettre complet, ex. : dimanche ou vendredi
• le mois est représenté par m
  • m affiche le mois avec au minimum 1 chiffre, ex. : 1 ou 12
  • mm affiche le mois avec 2 chiffres, ex. : 01 ou 12
  • mmm affiche le mois en lettre abrégé (3 ou 4 lettres), ex. : janv ou déc
  • mmmm affiche le mois en lettre complet, ex. : janvier ou décembre
• l'année est représenté par a
  • aa affiche l'année avec 2 chiffres, ex : 02
  • aaaa affiche l'année avec 4 chiffres, ex : 2002

Maintenant pour écrire lundi 2 septembre 2002, inscrivez 2/9/02 dans une cellule, puis :

• clic-droit/Format de cellule
• onglet Nombre, catégorie Personnalisée
• dans la zone Type, tapez jjjj j mmmm aaaa

Pour les heures, h représente les heures, m représente les minutes et s représente les secondes. Vous pouvez mettre 1 ou 2 h, 1 ou 2 m, 1 ou 2 s; ex : h:m:s (1:0:0) ou h:mm (1:00).
Les crochets ont un grand intérêt dans le format des heures, il empêche les heures de passer à 0 après 23:59:59, il empêche les minutes et les secondes de passer à 0 après 59 :

• [h]:mm cumule les heures, même si vous dépassez 24 heures. Ex. : 34:00
• [m]:ss cumule les minutes, même si vous dépasser 60 mn. Ex. : 34 heures s'affichent 2040:00
• [s] cumule les secondes, même si vous dépasser 60 s. Ex. : 34 heures s'affichent 122400

Il correspond tout simplement au jour 0 du mois actuel.

Vous pouvez également écrire directement la formule suivante :
=DATE(ANNEE(A2);MOIS(A2);0)

Soit une plage de valeurs non triées, si vous désirez à partir d'une valeur 

En C1 pour que la formule fonctionne, il faut impérativement appuyez sur Ctrl+Maj+Entrée et non simplement sur Entrée. Ceci permet de transformer votre formule en formule matricielle, elle sera entourée de {}. Une formule matricielle fait ses calculs sur chaque cellule de la plage. Dans notre exemple A2:A10-D2 renvoie une matrice (un tableau) de valeurs correspondants à chaque valeur de la plage A2:A10 moins la valeur de la cellule D2. Le tableau ci-dessous vous aidera à comprendre le fonctionnement de cette formule, le tableau effectue le même calcul  mais en utilisant plusieurs formules et plusieurs plages. La formule matricielle fait le même travail que les 3 plages B2:B10, C2:C10, D2:D10, ce qui retourne un tableau de valeur, ensuite on applique la fonction MIN pour avoir la valeur minimum de ce tableau.
 

	A	B	C	D
1				12,5
2	10	=ABS(A2-$D$1)	=ABS(A2-$D$1)	=SI(A2=$G$11;A2)
3	15	=ABS(A3-$D$1)	=ABS(A3-$D$1)	=SI(A3=$G$11;A2)
4	25	=ABS(A4-$D$1)	=ABS(A4-$D$1)	=SI(A4=$G$11;A2)
5	65	=ABS(A5-$D$1)	=ABS(A5-$D$1)	=SI(A5=$G$11;A2)
6	12	=ABS(A6-$D$1)	=ABS(A6-$D$1)	=SI(A6=$G$11;A2)
7	45	=ABS(A7-$D$1)	=ABS(A7-$D$1)	=SI(A7=$G$11;A2)
8	30	=ABS(A8-$D$1)	=ABS(A8-$D$1)	=SI(A8=$G$11;A2)
9	5	=ABS(A9-$D$1)	=ABS(A9-$D$1)	=SI(A9=$G$11;A2)
10	6	=ABS(A10-$D$1)	=ABS(A10-$D$1)	=SI(A10=$G$11;A2)
11			=MIN(C2:C10)	=MIN(D2:D10)

Dans le 1er tableau la cellule C4 utilise la fonction EQUIV pour connaître la position de la valeur de C3 dans la plage A2:A10, le dernier paramètre est à 0 pour rechercher la valeur exacte.

Ensuite en C5, la fonction LIGNE renvoie les N° de ligne de la plage A2:A10, soit une matrice (un tableau) contenant les valeurs {2,3,4,5,6,7,8,9,10}. La fonction INDEX renvoie toujours la valeur d'un tableau  selon une position, ici le tableau est la matrice renvoyée par la fonction LIGNE et la position est celle indiquée dans la cellule C4, soit pour la position 5, la valeur 6.

arrondir un nombre

Vous avez 3 fonctions pour arrondir vos nombres ARRONDI, ARRONDI.INF (arrondi en tendant vers 0), ARRONDI.SUP (arrondi en s'éloignant de 0). Elles fonctionnent toutes 3 avec les mêmes paramètres, le 1er correspond à la valeur à arrondir, le 2ème correspond au nombre de décimales que vous désirez.

Mais si vous voulez l'arrondir à n'importe quelle valeur (par exemple au multiple de 100 le plus proche), vous ne pouvez utiliser directement ces fonctions mais procédez ainsi :

soit la valeur 2353 dans la cellule A2, la formule est alors : =ARRONDI(A2/100;0)*100

Il vous suffit de remplacer la valeur 100 par votre nombre.