[OFFICE 365] :: Recherches avancées sur les fonctionnalités de la fonction RECHERCHEX associée aux fonction EQUIV, INDEX, SOMMEPROD, INDIRECT, AGREGAT

[oguruma]

Bonjour Le Forum,

dans un post précédent dans cette rubrique j'avais annoncé qu'un chantier était ouvert sur cette fonction RECHERCHEX qui reste encore assez méconnue pour les utilisateurs de Office 365. Ils ont gardé je crois pour certains le reflexe RECHERCHEV.

C'est donc chose faite !.

Ce post va assez loin dans les exemples parfois farfelus... vous m'en excuserez.

Le but étant de démontrer comment mettre en oeuvre cette fonction avec des cas complexes totalement inventés. Cependant les formules présentées peuvent vous orienter dans vos futurs développement pour explorer des tableaux structurés avec cette fonction et celles associées présentées dans le titre de ce post.

Je suis allé assez loin dans l'exploration commerciale d'un tel tableau laissant courir mon imagination parfois débordante.

Je vous laisse fouiller cet onglet qui est bien garni en exemples.

Voici la base du tableau de données

Quelques extraits

 

[CriCri16]

Merci Oguruma pour ce travail, et l'effort pédagogique qui l'accompagne 😀
Je me permets de souligner ce qui me semble d'utiles progrès avec rechercheX par rapport à rechercheV.

• Bien sûr on n'est plus contraint d'avoir le champ de recherche en première colonne 😉

Nota : celà permet de différencier la colonne où est cherchée la correspondance, et la colonne où sera prise le retour. L'obligation est que ces 2 colonnes aient le même nombre de lignes. J'ai eu l'occasion de les décaler d'une ligne pour récupérer ce qui suivait (tableaux triés).​

• Il y a aussi le paramètre "sierreur" inclus qui peut éviter les #N/A en gérant l'erreur avec un texte de message ou même une action telle qu'une recherchex dans une autre source ...
• Et, probablement marginal, mais important pour moi : par défaut on cherche une correspondance EXACTE

Depuis quelques temps, j'ai Office 365 au boulot, et l'introduction du matriciel dynamique amène des perspectives immenses.
Et recherchex y entre.

• On peut rechercher une valeur et récupérer une réponse sur plusieurs colonnes.
• On peut aussi passer une colonne de valeurs à rechercher sans copie_colle de la formule mais on ne récupère alors qu'une colonne de correspondance.
• Pour faire mieux, je ne sais que le faire avec index(equiv) . Avec la fonction let on peut encapsuler tout cela, voire en faire une lambda.

Et si on a Office 365, la fonction filtre() ouvre d'énormes possibilités

Une question cependant sur le format .xlsm de ton fichier, alors qu'a priori il ne contient pas de vba ?

Merci.😀

 

[oguruma]

Merci Oguruma pour ce travail, et l'effort pédagogique qui l'accompagne 😀
Je me permets de souligner ce qui me semble d'utiles progrès avec rechercheX par rapport à rechercheV.

• Bien sûr on n'est plus contraint d'avoir le champ de recherche en première colonne 😉

Nota : celà permet de différencier la colonne où est cherchée la correspondance, et la colonne où sera prise le retour. L'obligation est que ces 2 colonnes aient le même nombre de lignes. J'ai eu l'occasion de les décaler d'une ligne pour récupérer ce qui suivait (tableaux triés).​

• Il y a aussi le paramètre "sierreur" inclus qui peut éviter les #N/A en gérant l'erreur avec un texte de message ou même une action telle qu'une recherchex dans une autre source ...
• Et, probablement marginal, mais important pour moi : par défaut on cherche une correspondance EXACTE

Depuis quelques temps, j'ai Office 365 au boulot, et l'introduction du matriciel dynamique amène des perspectives immenses.
Et recherchex y entre.

• On peut rechercher une valeur et récupérer une réponse sur plusieurs colonnes.
• On peut aussi passer une colonne de valeurs à rechercher sans copie_colle de la formule mais on ne récupère alors qu'une colonne de correspondance.
• Pour faire mieux, je ne sais que le faire avec index(equiv) . Avec la fonction let on peut encapsuler tout cela, voire en faire une lambda.

Et si on a Office 365, la fonction filtre() ouvre d'énormes possibilités

Une question cependant sur le format .xlsm de ton fichier, alors qu'a priori il ne contient pas de vba ?

Bonjour,
tout ce que tu évoques en introduction (les 3 points) bien entendu je les connais ! de la même manière que le SIERREUR.
Les précautions sur l'égalité des plages de données surtout avec INDEX EQUIV oui en effet pour m'être fait piégé à mes débuts avec ces fonctions!. Elles tombent sous le sens.
Oui la recherche EXACTE tombe aussi sous le sens !
J'ai surtout fait un focus pour ceux qui ne possèdent pas O365 !
Tout ce que tout mentionnes en seconde partie en effet ! Mais une nouvelle fois ! mon but n'était pas de faire de la solution 100 % O365.
FILTRE ! Oui inutile de me le rappeler !
D'ailleurs pour simuler la fonction FILTRE en V2016 via PowerQuery par une requêtes dynamique avec passage en paramètre de la valeur filtrée et pour aller plus plus un bout de code dans les événements de la feuille afin qu'elle se mette à jour automatiquement dans un filtre est posé. Cela pourrait être d'ailleurs l'objet d'un prochain post de ma part.
IDEM on peut combler les limite d'un rechercheV qui ne retourne qu'une seule ligne via une requête powerquery.
Le matricielle dynamique oui, mais O365 ! il faut penser à ceux qui n'ont pas cette chance.
En synthèse j'ai un peu de mal à comprendre ces redressements un peu cavaliers de ta part !
Quant au .xlsm c'est un choix ! Cependant, il y a avait du code VBA dans une précédente version de ce fichier je l'ai tout simplement viré. Je ne vois pas ce qu'il y a de dérangeant de laisser un .xlsm !
L'avantage c'est que ton fichier est déjà organisé pour recevoir du code sans te poser questions.
Le BUT de ce post n'était pas de faire un cours de méthodologie sur ces fonctions Excel. Aux internautes aussi de creuser le sujet. Je me suis contenté de donner des pistes et des axes de réflexions qui déjà assez loin et sache que j'y ai passé du temps dans un passé lointain . C'est aussi une compilation et reprise de certains développements (V2013, 2016) que j'ai intégré et adapté dans ce tuto pour en faire profiter la communoté.
CQFD !

 

[CriCri16]

Bonjour,
tout ce que tu évoques en introduction (les 3 points) bien entendu je les connais ! de la même manière que le SIERREUR.
Les précautions sur l'égalité des plages de données surtout avec INDEX EQUIV oui en effet pour m'être fait piégé à mes débuts avec ces fonctions!. Elles tombent sous le sens.
Oui la recherche EXACTE tombe aussi sous le sens !
J'ai surtout fait un focus pour ceux qui ne possèdent pas O365 !
Tout ce que tout mentionnes en seconde partie en effet ! Mais une nouvelle fois ! mon but n'était pas de faire de la solution 100 % O365.
FILTRE ! Oui inutile de me le rappeler !
D'ailleurs pour simuler la fonction FILTRE en V2016 via PowerQuery par une requêtes dynamique avec passage en paramètre de la valeur filtrée et pour aller plus plus un bout de code dans les événements de la feuille afin qu'elle se mette à jour automatiquement dans un filtre est posé. Cela pourrait être d'ailleurs l'objet d'un prochain post de ma part.
IDEM on peut combler les limite d'un rechercheV qui ne retourne qu'une seule ligne via une requête powerquery.
Le matricielle dynamique oui, mais O365 ! il faut penser à ceux qui n'ont pas cette chance.
En synthèse j'ai un peu de mal à comprendre ces redressements un peu cavaliers de ta part !
Quant au .xlsm c'est un choix ! Cependant, il y a avait du code VBA dans une précédente version de ce fichier je l'ai tout simplement viré. Je ne vois pas ce qu'il y a de dérangeant de laisser un .xlsm !
L'avantage c'est que ton fichier est déjà organisé pour recevoir du code sans te poser questions.
Le BUT de ce post n'était pas de faire un cours de méthodologie sur ces fonctions Excel. Aux internautes aussi de creuser le sujet. Je me suis contenté de donner des pistes et des axes de réflexions qui déjà assez loin et sache que j'y ai passé du temps dans un passé lointain . C'est aussi une compilation et reprise de certains développements (V2013, 2016) que j'ai intégré et adapté dans ce tuto pour en faire profiter la communoté.
CQFD !

Bonjour,

Désolé si tu as lu ma réponse comme un redressement. Ce n'était pas le but. Ce n'était pas vers "toi" spécifiquement; mais pour l'ensemble de tes lecteurs.
A+

 

[oguruma]

Bonjour,

Désolé si tu as lu ma réponse comme un redressement. Ce n'était pas le but. Ce n'était pas vers "toi" spécifiquement; mais pour l'ensemble de tes lecteurs.
A+

bnjr, je dirai que c'était assez ambigu ok