TP 3

Fonctions Excel : Recherche et référence

Fonction Excel : CHOISIR

La fonction Excel CHOISIR affiche une valeur en fonction du numéro choisi (de 1 à 29 au
maximum).

Utilisation :

=CHOISIR(numéro; valeur_si_1; valeur_si_2; etc)

EXEMPLE D'UTILISATION

L'objectif ici est de compléter la colonne "Activité" en fonction du choix du participant :

Sélectionnez la fonction CHOISIR et entrez :

 No_index : le numéro choisi (ici la cellule B2)

 Valeur1 : la valeur à afficher lorsque No_index vaut 1
 Valeur2 : la valeur à afficher lorsque No_index vaut 2
Avant d'étirer la formule vers le bas, ajoutez le symbole $ devant le numéro de ligne des
activités pour le bloquer lorsque vous recopiez vers le bas :

=CHOISIR(B2;E$2;E$3;E$4)

Vous pouvez maintenant étirer la formule vers le bas :

Fonction Excel : EQUIV

La fonction Excel EQUIV recherche la position d'une valeur dans une plage de cellules.

Utilisation :

=EQUIV(valeur_recherchée; plage; type)

EXEMPLE D'UTILISATION

L'objectif ici est de déterminer la position de la ville recherchée dans la liste :

Sélectionnez la fonction EQUIV et entrez :

 Valeur_cherchée : la valeur dont vous souhaitez obtenir la position

 Tableau_recherche : la plage de cellules dans laquelle la fonction recherchera la position
de Valeur_cherchée
 Type : 0 pour trouver la valeur exacte, 1 pour la valeur la plus élevée qui est inférieure ou
égale à Valeur_cherchée, -1 pour la plus petite valeur qui est supérieure ou égale
à Valeur_cherchée (dans le doute, entrez 0)

=EQUIV(E2;B2:B11;0)

La position de la ville dans la liste est alors affichée :

Londres est bien situé à la 3e position dans la plage de cellules B2:B11.

Fonction Excel : EQUIVX

La fonction EQUIVX renvoie la position d'un élément dans un tableau ou une plage de
cellules.

C'est une version améliorée de la fonction EQUIV.

Utilisation :

=EQUIVX(valeur_recherche; tableau_recherche)

ou

=EQUIVX(valeur_recherche; tableau_recherche; mode_correspondance;

mode_recherche)

EXEMPLE D'UTILISATION

L'objectif ici est de retourner la position de la ville recherchée dans le tableau des villes :
Entrez dans la fonction EQUIVX :

 valeur_recherche : la valeur dont il faut rechercher la position

 tableau_recherche : le tableau dans lequel il faut rechercher la position de valeur_recherche

La formule est ici :

=EQUIVX(C2;A2:A11)

Dans cet exemple, "Londres" est bien la 3e valeur de la plage A2 à A11.

ARGUMENTS FACULTATIFS

Dans l'exemple précédent, seuls les 2 arguments obligatoires ont été renseignés, mais il en
existe 2 de plus :

 mode_correspondance : la méthode pour trouver une correspondance :

o 0 : correspondance exacte (par défaut)
o 1 : correspondance exacte ou élément le plus petit suivant
 o -1 : correspondance exacte ou élément le plus grand suivant
o 2 : correspondance générique (où * remplace aucun, un ou plusieurs
caractères, ? remplace un caractère et ~ permet d'échapper l'un de ces 3
caractères *?~)
 mode_recherche : le mode de recherche :
o 1 : recherche du premier au dernier (par défaut)
o -1 : recherche du dernier au premier
o 2 : recherche binaire en supposant que la plage est triée par ordre croissant
o -2 : recherche binaire en supposant que la plage est triée par ordre décroissant

Voici un autre exemple avec mode_correspondance à 2 pour utiliser le caractère

générique * (qui remplace aucun, un ou plusieurs caractères) et mode_recherche à -1 pour
rechercher la position de la ville qui commence par "S" en partant de la fin :

=EQUIVX(C2&"*";A2:A11;2;-1)

Fonction Excel : FILTRE

La fonction Excel FILTRE permet de filtrer une plage de cellules en fonction d'un ou de
plusieurs critères.

Utilisation :

=FILTRE(tableau; critère)

ou

=FILTRE(tableau; critère; si_aucun_résultat)

EXEMPLE D'UTILISATION

Dans cet exemple, l'objectif est de filtrer les données pour obtenir la liste des pays avec un
score supérieur ou égal à 65 :

Sélectionnez la fonction FILTRE puis entrez la plage de cellules A2:B8 (pour retourner les
pays + les points) ainsi que le critère B2:B8>=65 (pour tester les points) :

=FILTRE(A2:B8;B2:B8>=65)

Pour éviter d'obtenir une erreur s'il n'y a aucun résultat, renseignez le 3e argument (avec par exemple la
valeur "").

En cas de besoin, vous pouvez utiliser la fonction TRIER pour trier le tableau retourné par la
fonction FILTRE. Par exemple, un tri par points décroissant :

=TRIER(FILTRE(A2:B8;B2:B8>=65);2;-1)
FILTRER EN FONCTION DE PLUSIEURS CRITERES

Pour obtenir la liste des pays qui ont obtenu 60 points ou plus dans les 2 colonnes
(donc B2:B8>=60 et C2:C8>=60), ajoutez ces 2 critères entre parenthèses et multipliez-les :

=FILTRE(A2:A8;(B2:B8>=60)*(C2:C8>=60))

FILTRER EN FONCTION D'AU MOINS UN CRITERE

Pour obtenir la liste des pays qui ont obtenu 100 points ou plus dans au moins une des 2
colonnes (donc B2:B8>=100 ou C2:C8>=100), ajoutez ces 2 critères entre parenthèses et
additionnez-les :

=FILTRE(A2:A8;(B2:B8>=100)+(C2:C8>=100))
Fonction Excel : INDEX
La fonction Excel INDEX recherche une valeur dans un tableau en fonction de ses
coordonnées.

Utilisation :

=INDEX(tableau; no_ligne; no_colonne)

EXEMPLE D'UTILISATION

L'objectif ici est d'afficher une valeur du tableau en fonction des numéros de ligne et de
colonne :

Sélectionnez la fonction INDEX et entrez :

 Matrice : le tableau dans lequel la fonction effectuera la recherche

 No_lig : le numéro de ligne dans Matrice
 No_col : le numéro de colonne dans Matrice
=INDEX(A2:C11;E2;F2)

La fonction affiche ensuite la valeur en fonction de sa position dans le tableau :

En combinant la fonction INDEX avec la fonction EQUIV, il est possible d'afficher le résultat souhaité
directement en fonction d'une valeur du tableau : INDEX + EQUIV
Fonctions Excel : INDEX + EQUIV
La fonction Excel INDEX utilisée avec la fonction EQUIV permet la recherche de valeur
dans un tableau.

COMBINAISON INDEX + EQUIV

Si la fonction EQUIV renvoie le numéro de ligne :

=INDEX(tableau; EQUIV(valeur_recherchée; plage_de_recherche; 0);

no_colonne)

Si la fonction EQUIV renvoie le numéro de colonne :

=INDEX(tableau; no_ligne; EQUIV(valeur_recherchée; plage_de_recherche; 0))

EXEMPLE D'UTILISATION

L'objectif ici est de trouver le numéro de dossier ainsi que le nombre de points du lieu
recherché. L'utilisateur doit pouvoir entrer la ville dans la partie verte et voir ensuite le
résultat de sa recherche dans la partie bleue :

Sélectionnez pour commencer la fonction INDEX et entrez :

 Matrice : le tableau avec les données

 No_lig : n'entrez rien pour le moment (la fonction EQUIV qui calculera le numéro de ligne en
fonction de la recherche sera insérée ici)
 No_col : la colonne du tableau qui contient les données à renvoyer (dans cet exemple, le but
est d'afficher le numéro de dossier, c'est donc la colonne 1)
Positionnez-vous dans No_lig et ajoutez la fonction EQUIV en cliquant ici :
Pour que la fonction EQUIV renvoie le numéro de ligne de la ville, entrez :

 Valeur_cherchée : la valeur dont il faut rechercher la position (ici, la ville)

 Tableau_recherche : la plage de cellules dans laquelle la fonction recherchera la position de la
ville
 Type : 0 pour trouver la valeur exacte

=INDEX(A2:C11;EQUIV(E2;B2:B11;0);1)

Le numéro de dossier de la ville est alors affiché :

Pour afficher ensuite les points, il suffit de copier la formule et de modifier le numéro de
colonne (remplacez 1 par 3) :

Fonction Excel : RECHERCHEH

La fonction Excel RECHERCHEH recherche une valeur dans la première ligne d'un tableau
puis renvoie la valeur d'une cellule qui se situe dans la même colonne que la valeur
recherchée.

Utilisation :

=RECHERCHEH(recherche; tableau; ligne; type)

EXEMPLE D'UTILISATION

L'objectif ici est de rechercher des informations en fonction du numéro de dossier.

L'utilisateur doit pouvoir entrer le numéro de dossier dans la partie verte et voir ensuite le
résultat de sa recherche dans la partie bleue :

Sélectionnez la fonction RECHERCHEH et entrez :

 Valeur_cherchée : la valeur à rechercher dans la première ligne du tableau (ici, le numéro de

dossier)
 Table_matrice : la plage de cellules qui contient les données du tableau
  No_index_col : le numéro de ligne du tableau qui contient le résultat à renvoyer (ici, la ligne 2
pour la ville)
 Valeur_proche : FAUX ou 0 pour rechercher la valeur exacte de Valeur_cherchée (dans le
doute, entrez FAUX pour éviter les surprises), VRAI ou 1 (ou en laissant vide) pour
rechercher la valeur la plus proche de Valeur_cherchée

=RECHERCHEH(B7;B1:K3;2;FAUX)

Le nom de la ville est alors affiché :

Pour afficher ensuite les points, il suffit de copier la formule et de modifier le numéro de ligne
(remplacez 2 par 3) :

Fonction Excel : RECHERCHEV

La fonction Excel RECHERCHEV recherche une valeur dans la première colonne d'un
tableau puis renvoie la valeur d'une cellule qui se situe sur la même ligne que la valeur
recherchée.

Utilisation :

=RECHERCHEV(recherche; tableau; colonne; type)

EXEMPLE D'UTILISATION

L'objectif ici est de rechercher des informations en fonction du numéro de dossier.

L'utilisateur doit pouvoir entrer le numéro de dossier dans la partie verte et voir ensuite le
résultat de sa recherche dans la partie bleue :

Sélectionnez la fonction RECHERCHEV et entrez :

 Valeur_cherchée : la valeur à rechercher dans la première colonne du tableau (ici, le numéro

de dossier)
  Table_matrice : la plage de cellules qui contient les données du tableau
 No_index_col : le numéro de colonne du tableau qui contient le résultat à renvoyer (ici, la
colonne 2 pour la ville)
 Valeur_proche : FAUX ou 0 pour rechercher la valeur exacte de Valeur_cherchée (dans le
doute, entrez FAUX pour éviter les surprises), VRAI ou 1 (ou en laissant vide) pour
rechercher la valeur la plus proche de Valeur_cherchée

=RECHERCHEV(E2;A2:C11;2;FAUX)

Le nom de la ville est alors affiché :

Pour afficher ensuite les points, il suffit de copier la formule et de modifier le numéro de
colonne (remplacez 2 par 3) :

Fonction Excel : RECHERCHEX

La fonction Excel RECHERCHEX recherche une valeur dans un tableau puis retourne la
valeur correspondante (à la même position) dans un second tableau.

Cette fonction sert généralement à rechercher une valeur dans une colonne d'un tableau et à
retourner la valeur correspondante d'une autre colonne.

La fonction RECHERCHEX se révèle bien plus fonctionnelle que la

fonction RECHERCHEV qui recherche la valeur uniquement dans la première colonne d'un
tableau, et plus simple à utiliser que la combinaison INDEX + EQUIV qui nécessite d'utiliser
2 fonctions.

Utilisation :

=RECHERCHEX(recherche; tableau_recherche; tableau_résultat)

ou

=RECHERCHEX(recherche; tableau_recherche; tableau_résultat;

si_aucun_résultat, correspondance, mode)

EXEMPLE D'UTILISATION

L'objectif ici est de rechercher des informations en fonction du nom de la ville. L'utilisateur
doit pouvoir entrer le nom de la ville dans la partie verte et voir ensuite le résultat de sa
recherche dans la partie bleue :
Il s'agit pour le moment du même exemple que celui de la page des fonctions INDEX +
EQUIV, mais cette fois-ci, réalisé à l'aide de la seule fonction RECHERCHEX.

Sélectionnez la fonction RECHERCHEX et entrez :

 Valeur_cherchée : la valeur à rechercher dans le tableau (ici, le nom de la ville)

 Tableau_recherche : le tableau où doit être recherchée la valeur (ici, la colonne des villes)
 Tableau_renvoyé : le tableau qui contient le résultat à retourner par la fonction (ici, la colonne
des numéros de dossier)

=RECHERCHEX(E2;B2:B11;A2:A11)
Le numéro de dossier est alors affiché :

Pour afficher ensuite les points, il vous suffit de copier la formule et de

modifier Tableau_renvoyé :

LES ARGUMENTS FACULTATIFS

Dans l'exemple précédent, seuls les 3 arguments obligatoires ont été renseignés dans la
fonction RECHERCHEX, mais il en existe 3 de plus qui sont facultatifs.

Le plus utile est certainement Si_non_trouvé qui permet de préciser la valeur à retourner s'il
n'y a aucun résultat et éviter ainsi l'erreur #N/A.

Par exemple :

=RECHERCHEX(E2;B2:B11;A2:A11;"-")
Le second argument facultatif Mode_correspondance permet de choisir le mode de
recherche bien que la plupart du temps, vous utiliserez uniquement le mode par défaut (0) :

 0 : correspondance exacte (si aucune correspondance : une erreur #N/A ou la

valeur Si_non_trouvé)
 -1 : correspondance exacte (si aucune correspondance : la valeur inférieure suivante)
 1 : correspondance exacte (si aucune correspondance : la valeur supérieure suivante)
 2 : correspondance générique (où * remplace aucun, un ou plusieurs caractères, ? remplace un
caractère et ~ permet d'échapper l'un de ces 3 caractères *?~)

Par exemple, avec le mode 2 et la recherche M* (ou m*), la fonction recherchera la première
ville dont le nom commence par M :

=RECHERCHEX(E2;B2:B11;A2:A11;"-";2)

Et pour terminer, le dernier argument facultatif est Mode_recherche :

  1 : recherche en commençant par le début (par défaut)
 -1 : recherche en commençant par la fin
 2 : recherche binaire croissante
 -2 : recherche binaire décroissante

VALEURS MULTIPLES

Il est possible de retourner plusieurs valeurs à la fois avec une seule fonction
RECHERCHEX.

Par exemple, pour retourner la ligne entière, entrez le tableau entier dans Tableau_renvoyé :

=RECHERCHEX(E2;B2:B11;A2:C11)

Fonction Excel : TRIER

La fonction Excel TRIER trie le contenu d'une plage de cellules ou d'un tableau et renvoie un
tableau trié en fonction des paramètres définis (ou par défaut).

Utilisation :

=TRIER(tableau)

ou

=TRIER(tableau; index_colonne; ordre; par_colonne)

TRI PAR DEFAUT

Dans cet exemple, l'objectif est de trier le tableau en fonction du nom du pays :
Sélectionnez la fonction TRIER puis entrez simplement la plage de cellules du tableau pour
effectuer le tri par défaut (tri croissant en fonction de la première colonne du tableau) :

=TRIER(A2:B8)

TRIER EN FONCTION D'UNE AUTRE COLONNE

Pour trier le tableau en fonction des points (donc en fonction de la 2e colonne), entrez 2 en
second argument :

=TRIER(A2:B8;2)
TRIER PAR ORDRE DECROISSANT

Pour obtenir cette fois-ci un tri décroissant, entrez -1 en troisième argument :

=TRIER(A2:B8;2;-1)

TRIER PAR COLONNE

Le dernier argument facultatif permet d'effectuer un tri par colonne (au lieu de trier par ligne)
en entrant la valeur 1 (ou VRAI) :

=TRIER(B1:H2;2;-1;1)

Fonction Excel : TRIERPAR

La fonction Excel TRIERPAR trie le contenu d'une plage de cellules ou d'un tableau en
fonction d'une plage de cellules ou d'un tableau.

Cette fonction permet d'effectuer des tris plus avancés qu'avec la fonction TRIER (qui ne
permet de trier un tableau qu'en fonction de données contenues dans ce même tableau).

Utilisation :

=TRIERPAR(tableau; tableau_tri)
ou

=TRIERPAR(tableau; tableau_tri_1; ordre_1; tableau_tri_2; ordre_2, ...)

EXEMPLE D'UTILISATION

Dans cet exemple, l'objectif est de classer les pays en fonction de leur nombre de points :

Sélectionnez la fonction TRIERPAR puis entrez la plage de cellules à retourner (celle des
pays) ainsi que la plage de cellules à utiliser pour le tri (celle des points) :

=TRIERPAR(A2:A8;B2:B8)

Par défaut les données sont triées par ordre croissant, pour effectuer un tri décroissant
ajoutez -1 en troisième argument :

=TRIERPAR(A2:A8;B2:B8;-1)
Fonction Excel : UNIQUE
La fonction Excel UNIQUE renvoie la liste des valeurs uniques d'une plage de cellules ou
d'un tableau.

Utilisation :

=UNIQUE(tableau)

ou

=UNIQUE(tableau; par_colonne; valeurs_uniques)

LISTE DES VALEURS UNIQUES

Dans cet exemple, l'objectif est d'obtenir la liste unique des pays de la colonne A :
Sélectionnez la fonction UNIQUE puis entrez simplement la plage de cellules des pays pour
obtenir la liste unique des pays :

=UNIQUE(A2:A13)

Pour obtenir une liste triée des pays, vous pouvez ajouter la fonction TRIER :

=TRIER(UNIQUE(A2:A13))

Si vous sélectionnez une plage contenant plusieurs colonnes, la fonction UNIQUE renverra la
liste des lignes uniques :

=UNIQUE(A2:B13)
RECHERCHER PAR COLONNE

Par défaut, la recherche des valeurs uniques est effectuée ligne par ligne.

Pour lister les colonnes uniques, entrez la valeur 1 (ou VRAI) en second argument :

=UNIQUE(B1:M2;1)

RECHERCHER LES VALEURS UNIQUES

Pour obtenir uniquement les valeurs présentes une seule fois dans le tableau, entrez la
valeur 1 (ou VRAI) en troisième argument :

=UNIQUE(A2:B13;;1)
Par exemple, la ligne France Non est présente plus d'une fois dans le tableau, elle n'est donc
pas listée ici.

Fonctions Excel : Compatibilité

Fonction Excel : CONCATENER

La fonction Excel CONCATENER assemble plusieurs chaînes de caractères pour n'en former
plus qu'une.

Utilisation :

=CONCATENER(valeur_1; valeur_2; valeur_3; etc)

EXEMPLE D'UTILISATION

Sélectionnez la fonction CONCATENER puis entrez dans l'ordre les valeurs à assembler :
Cliquez sur OK pour obtenir le résultat :

Il suffit ensuite d'étirer la formule vers le bas pour compléter les autres cellules :
Vous pouvez également utiliser des & pour remplacer la fonction CONCATENER.

Fonction Excel : PREVISION

La fonction Excel PREVISION extrapole une nouvelle donnée par rapport à d'autres déjà
connues.

Utilisation :

=PREVISION(X; valeurs_Y, valeurs_X)

EXEMPLE D'UTILISATION

L'objectif ici est d'estimer le chiffre d'affaires en fonction de l'évolution du chiffre d'affaires
des années précédentes :
Sélectionnez la fonction PREVISION et entrez :

 X : l'année dont il faut estimer le chiffre d'affaires

 Y_connus : la plage de cellules contenant les chiffres d'affaires des années précédentes
 X_connus : la plage de cellules contenant les années

=PREVISION(A11;B2:B10;A2:A10)

Cliquez sur OK.

Il ne reste plus qu'à ajouter des $ et étirer la formule vers le bas :

Fonction Excel : RANG

La fonction Excel RANG détermine le rang d'une valeur en fonction d'un ensemble de valeurs
(pour classer des valeurs par exemple).

Utilisation :

=RANG(valeur; ensemble_de_valeurs; ordre)

EXEMPLE D'UTILISATION

L'objectif ici est d'attribuer un classement aux participants en fonction de leur nombre de
points :

Sélectionnez la fonction RANG et entrez :

  Nombre : le nombre dont il faut déterminer le rang
 Référence : la plage de cellules contenant toutes les valeurs
 Ordre : laissez vide (ou entrez 0) pour un ordre décroissant, entrez une valeur différente de 0
pour un ordre croissant

=RANG(B2;B2:B9)

Cliquez sur OK.

Avant de recopier, ajoutez des $ à la plage de cellules pour éviter qu'elle ne soit décalée puis
étirez la formule vers le bas :

=RANG(B2;B$2:B$9)

Les participants "Prénom 2" et "Prénom 8" occupent normalement les places 4 et 5 mais puisqu'ils ont
obtenu le même nombre de points, ils occupent tous deux la 4e place (et le rang 5 n'a donc pas été
attribué).