RechercheV et ajoût de colonnes

aldus_85

XLDnaute Nouveau
Bonjour !

J'utilise fréquemment la fonction RechercheV pour récupérer les caractéristiques d'un article à partir d'une référence par exemple et plus généralement pour remplacer une fonction base de données ou une fonction TABLE {=TABLE(X,Y)}.

Le problème se pose avec l'argument "index", qui indique à Excel quelle colonne du tableau utiliser. Etant a priori une valeur constante (et pas une référence style $X$n), l'argument n'est pas mis à jour quand on ajoute une colonne au sein du tableau (ce qui est fréquent, lors de la conception ou pire quelques temps après par quelqu'un d'autre que le concepteur). Autre cas de figure, on utilise un tableau de données fait par quelqu'un d'autre et ce tableau change de format (1-déplacement de colonnes pour faire la place à d'autres ou au contraire disparition de colonnes, dans tous les cas la colonne où trouver la correspondance n'est plus au même endroit; 2- un changement de nom, ici pour mémoire car sans impact sur le numéro de colonne)

Les solutions que j'ai trouvées sont toute basées sur l'introduction d'une référence qui sera modifiée en cas d'introduction d'une colonne. Avec un tableau nommé DataTemp dont la première colonne est la colonne où on va chercher la valeur et la quatrième colonne où on va trouver les correspondances (voir le fichier joint), cela donne concrètement :

=RECHERCHEV($H$5;DataTemp;4;FAUX) -> ne résiste pas à l'ajoût d'une colonne au sein du tableau (par exemple entre $D:$D et $E:$E)

=RECHERCHEV($H$5;DataTemp;EQUIV($E$5;$B$5:$E$5;0);FAUX) -> insensible aux ajoût de colonnes au sein du tableau mais difficile à lire

=RECHERCHEV($H$5;DataTemp;EQUIV($E$5;DataTemp $5:$5;0);FAUX) -> difficile à lire

=RECHERCHEV($H$5;DataTemp;COLONNE($E$5)-COLONNE(DataTemp)+1;FAUX) -> un peu plus long mais plus lisible voire moins consommateur de ressources

=RECHERCHEV($H$5;DataTemp;COLONNES($B$5:$E$5);FAUX) -> un peu plus court mais peu lisible ou encore =RECHERCHEV($H$5;DataTemp;COLONNES(DataTempTitres);FAUX)

=RECHERCHEV($H$5;DataTemp;EQUIV("Carré";DECALER(DataTemp;0;0;1);0);FAUX) -> a l'avantage de rendre insensible aux ajouts de colonnes, au changement d'ordre des colonnes (autres que la première), d'être relativement lisible par l'intitulé de la colonne présent dans la formule (ici "Carré")

=RECHERCHEV($H$5;DECALER(DataTemp;0;EQUIV("Double";DECALER(DataTemp;0;0;1);0)-1);EQUIV("Carré";DECALER(DataTemp;0;0;1);0)-EQUIV("Double";DECALER(DataTemp;0;0;1);0)+1;FAUX) -> usine à gaz qui permet de choisir la colonne où chercher la valeur et d'indiquer la colonne où trouver la correspondance par les seuls titres des colonnes

Des formules n'utilisant pas RECHERCHEV() mais EQUIV() :

=INDEX(DataTemp $E:$E;EQUIV($H$5;DataTemp $B:$B;0)) (sur une idée de Hoerwind cf. ci-dessous) -> simple, élégante et permet de choisir la colonne dans laquelle va se faire la recherche de la valeur (un plus par rapport à RECHERCHEV())

=INDEX(DataTemp;EQUIV($H$5;DataTemp $B:$B;0);EQUIV("Carré";DataTempTitres;0)) (sur une idée de BOISGONTIER cf. ci-dessous)-> formule assez compacte, assez lisible, permettant le choix de la colonne de recherche de la valeur et de nommer la colonne de résultat - le premier EQUIV() pour trouver la valeur, le deuxième EQUIV() pour désigner la colonne résultat


Personnellement, j'utilise :
Code:
=RECHERCHEV($H$5;DataTemp;COLONNES(DataTempTitres);FAUX)
quand j'utilise une colonne définie en position dans le tableau (4ème colonne du tableau par exemple)

Code:
=INDEX(DataTemp;EQUIV($H$5;DataTemp $B:$B;0);EQUIV("Carré";DataTempTitres;0))
quand je fait référence à une colonne par le titre de la colonne (par exemple colonne dont le titre est "Carré"). C'est en fait mon vrai choix car cette formule assure:
- une insensibilité aux ajouts (rappel)
- une bonne lisibilité quand le nombre de RechercheV est élevé et/ou avec des références à des feuilles ou des classeurs externes.
- le choix de la colonne où chercher la valeur, ce qui est un plus quand ladite colonne n'a pas été placée à l'extrême gauche du tableau voire pire est placée après la colonne résultat

Cà marche bien et çà rend les design beaucoup plus robustes.

Merci encore à BOISGONTIER et à Hoerwind.
D'autres candidats pour proposer des formules (encore) plus efficaces ?[/B] ( plus compactes et/ou plus lisibles )
 

Pièces jointes

  • AstuceExcelRecherchev.xls
    26 KB · Affichages: 213
Dernière édition:

hoerwind

XLDnaute Barbatruc
Re : RechercheV et ajoût de colonnes

Bonjour,

=RECHERCHEV($H$5;DataTemp;COLONNE()-5;FAUX)
permet l'insertion d'une colonne entre D et E, mais pas entre E et I

=DECALER($B$5;EQUIV(H5;B6:B25;0);EQUIV("Carré";C5:F5;0))
ne semble pas poser problème

=INDEX(E:E;EQUIV(H$5;$B:$B;0))
ne semble pas poser problème
 

aldus_85

XLDnaute Nouveau
Re : RechercheV et ajoût de colonnes

Bonjour !

Merci à BOISGONTIER et à Hoerwind pour leurs propositions.

J'en tire 2 enseignements principaux:
- d'abord penser plus à la fonction EQUIV qui assure une fonction de recherche proche de RECHERCHEV, en donnant toutefois plus de souplesse (on n'est plus obligé de rechercher la valeur dans la première colonne du tableau) ...
- ... complété par l'utilisation d'INDEX

Dorénavant je perdrai le réflexe RECHERCHEV( ; ; ) pour penser INDEX( ; EQUIV( ; ; ) ; )

Code:
=INDEX($E5:$E25;EQUIV(H$5;$B5:$B25;0))
de Hoerwind est particulièrement élégant

Code:
=INDEX($B5:$E$25;EQUIV($H$5;$B5:$B$25;0);EQUIV("Carré";$B5:$E5;0))
de BOISGONTIER donne souplesse et lisibilité en désignant la colonne résultat par son en-tête ("Carré" ici)

Avec leur permission, je vais enrichir la liste de formules de leurs propositions.

Aldus.
 

Bitoubi

XLDnaute Nouveau
Re : RechercheV et ajoût de colonnes

Une autre solution assez propre est d'utiliser des tables :
1 - Insertion/Tableau
2 - Outils de Tableau/Création/Nom du tableau

On peut ensuite écrire :
=RECHERCHEV(I11;A2:E21;COLONNES(A:E),0)
(Peu lisible mais rapide à écrire, et résiste maintenant aux ajoûts de colonnes)

ou même :
=RECHERCHEV(resultats[[#Cette ligne];[n]];sources;COLONNES(sources[N]:sources[Carré]))
(plus long à écrire, mais ensuite toutes les références sont relatives : on voit exactement quelles valeurs sont utilisées :cool:)


On pourrait encore améliorer :
- trouver plus court pour le numéro de colonne ?
- automatiser la saisie-semi automatique : pour l'instant j’ai déjà essayé Options Excel->Formules->Saisie semi-automatique de formule & Utiliser les noms de tableau dans les formules mais rien ne se passe...
 

PAPA WALKER

XLDnaute Junior
Re : RechercheV et ajoût de colonnes

Bonsoir à tous,

J'ai regardé le fichier joint avec tous les exemples cités.

Ma question est la suivante:
DataTemp doit-il être sur le même onglet que celui où est saisie la formule : =INDEX(DataTemp $E:$E;EQUIV($H$5;DataTemp $B:$B;0)) ?

J'ai tenté d'adapter ça à un de mes fichier ou la base est sur un autre onglet et ça ne marchait pas?

Ma Formule était sur Feuil1 et ma base sur Feuil2

Cordialement
 

Discussions similaires

Réponses
3
Affichages
149

Statistiques des forums

Discussions
311 707
Messages
2 081 746
Membres
101 812
dernier inscrit
trufu