Fonction Recherche X - Umaan

Fonction Recherche X

Fonction Recherche X

Utiliser la fonction RECHERCHEX pour rechercher des éléments dans un tableau ou une plage par ligne.

Durée : 5min
Débutant

Préambule

Ce tuto s’adresse à des personnes qui connaissent les formules RECHERCHEX, INDIRECT ou EQUIVX. D’autres formules sont utilisées, comme UNIQUE, SEQUENCE ou TRIER, elles sont secondaires car nous pouvons nous en passer mais elles simplifient notre travail.
Nous encapsuler plusieurs formules et travailler avec des références absolues ou semi-absolues.
Si vous avez des problèmes à suivre, je vous conseille de bien comprendre les cours de base sur ces formules.
La plupart de ces formules sont issues de Microsoft Excel 365 mais un résultat identique peut être obtenu avec leurs équivalents sur les versions précédentes.

Objectif

L’objectif est, à partir d’une donnée, de lister toutes les lignes d’un tableau sans lignes vides et sans VBA.

Vous pouvez ouvrir le fichier Excel joint à ce tuto.

Tableau

La 1ere colonne A contient les villes que nous retrouvons en colonne E, elle pouvait contenir des noms de techniciens, des noms de marque ou autres.

Création d’une liste déroulante

Afin d’établir un choix sur les lignes que nous souhaitons faire apparaitre, nous allons créer une liste déroulante à partir de la colonne A du tableau dans l’onglet Tableau.
Pour ce faire dans l’onglet Param nous allons rentrer en D2 la formule suivante :

=MAJUSCULE(TRIER(UNIQUE(Tableau!A2:A100)))

Explication de la formule :

UNIQUE(Tableau!A2:A100)
Permet d’avoir la liste de valeurs sans doublon de la colonne A de la ligne 2 à la ligne 100.

TRIER
Permet de trier la liste de valeurs uniques.

MAJUSCULE
Met en majuscule la liste triée.

Une fois la liste établie, nous pouvons créer la liste déroulante dans l’onglet Data en A3.
Il suffit d’aller dans l’onglet Données et dans Validation des données.

La liste déroulante est prête et dynamique.

Dans la colonne B, en B3, nous allons compter le nombre de ville correspondant à la ville choisie. Cette étape n’est pas nécessaire mais un index est créé devant chaque ligne.
=SEQUENCE(NB.SI(Tableau!A:A;A3);1)
Explication de la formule :
NB.SI(Tableau!A:A;A3)
Permet de compter le nombre de valeurs dans la colonne A de notre tableau qui correspond à la ville choisie.

SEQUENCE
Créé une séquence du nombre de ville trouvée avec un pas de 1

Trouver le numéro de la ligne de chaque ville

En colonne C nous allons utiliser 2 formules différentes, la 2ième est une variante de la 1ère.

En effet EQUIVX va nous permettre de trouver la 1ère ville choisie mais nous devons trouver la suivante en partant du numéro de ligne trouvé.
1ère formule :
=SIERREUR(EQUIVX($A$3;Tableau!$A$1:$A$35;0;1);"")
EQUIVX($A$3;Tableau!$A$1:$A$35;0;1)

4 paramètres sont possibles :

  • La valeur cherchée, La ville dans notre cas
  • Ou chercher la ville, dans notre tableau en colonne A
  • Type de correspondance, Exact dans notre cas (0 par défaut)
  • Type de recherche, du premier au dernier, (1 par défaut)

SIERREUR
En cas d’erreur, nous pouvons choisir le message à afficher.
Ce n’est pas utile sur la première ligne mais on ne sait jamais, c’est plus propre en cas de problème.

2e formule

=SIERREUR(EQUIVX($A$3;INDIRECT("Tableau!A"&C3+1):Tableau!$A$35;0;1)+C3;"")
Nous ne reviendrons pas sur SIERREUR, ce sera le même principe que sur toutes les formules de ce tuto, mais pour cette ligne et les autres, cette formule est utile.
Le seul changement avec la formule précédent concernant la matrice ou nous devons rechercher la ville. En effet nous devons construire une matrice à partir de la ligne +1 de la ville trouvée précédemment.

INDIRECT("Tableau!A"&C3+1)
En C3 (le résultat de la formule précédente) contient le numéro de ligne de la ville trouvée, et nous ajoutons 1 pour commencer à la ligne suivante. Pour Paris la 1ère ligne est 12, donc nous redémarrons à 13.
Mais pour construire le début de notre tableau, soit A13 dans notre exemple, il faut passer par INDIRECT et par CONCATENER, dans sa forme simple le & commercial ou l’esperluette.

Il faut bien sur mettre ensuite les :

Il y a également une différence avec la formule précédente car à la place de A35 ou $A$35, il faut ajouter l’onglet soit, Tableau !$A$35.

Dernier élément, il faut ajouter à nouveau C3, soit 12 pour Paris, en effet la valeur retournée par EQUIVX est la position relative dans la matrice donc je rajoute le nombre de ligne précédemment trouvé.

Récupérer les valeurs de toutes les lignes

Pour cette dernière étape nous allons utiliser la formule RECHERCHEX

=SIERREUR(RECHERCHEX($A$3;INDIRECT("Tableau!A"&C3):Tableau!$A$35;INDIRECT("Tableau!A"&C3):Tableau!$G$35;"";0;1);"")

Nous ne reviendrons pas sur SIERREUR, ce sera le même principe que sur toutes les formules de ce tuto, mais pour cette ligne et les autres, cette formule est utile.

Paramètres :

Il y a 5 paramètres avec RECHERCHEX :

  • La valeur cherchée, La ville dans notre cas
  • Ou chercher la ville, dans notre tableau en colonne A
  • La ou les colonnes à retourner sous forme de matrice
  • La valeur à retourner si non trouvé « », soit rien dans notre cas
  • Type de correspondance, Exacte dans notre cas
  • Mode de recherche, du 1er au dernier dans notre cas (valeur 1)

La construction des 2 matrices, celle ou chercher et celle à retourner, sont construites exactement selon le même modèle que EQUIVX.
En effet nous avons besoin des numéros de lignes précédemment trouvées pour retourner les matrices.

Note : Il faut vous assurer qu’il y ait suffisamment de place pour les valeurs qui seront récupérer, sinon vous aurez le message #EPARS! (message apparue avec Microsoft 365).
Dans cet exemple il faut 7 cellules de libre en incluant la formule puisque nous demandons de retourner 7 valeurs.

Conclusion

Il suffit de recopier les formules, à partir de la ligne 4 dans notre exemple car en C3 et C4 les formules sont différentes.
L’utilisation de ces formules peut paraitre déroutant mais il suffit de le faire une fois sur vos fichiers et vous aurez un modèle.
Ces formules fonctionnent même si un filtre a été utilisé sur le tableau.

Catégories : Excel
Le coach