Discussion :

[laurent27530]

Bonjour,

J'ai besoin de récupérer la valeur liée a plusieurs champs

J'ai 3 varaiables en entrée il me faut retrouver la correspondance dans un tableau

Merci pour votre aide

Particularié mon excel n'a pas la rechercheX

[MarcelG]

Bonsoir Laurent,

Les recherches multicritères ont fait déjà l'objet de maints échanges et exposés.

Pour ma part, je pense me simplifier cette recherche en utilisant la place d'une concaténation, objet d'une colonne de tableau structuré prévue à cet effet.

Soit une table de donnée (tableau structuré) nommée "T_Données" ayant cette configuration



La formulation de la colonne "Concat" étant

=[@Nom]&" - " &[@Prénom]& " - " &[@Voiture]

Cette formulation entrée sur le 1er enregistrement (ligne) va incrémenter automatiquement la colonne
(tout comme une liste de validation ou une mise en forme conditionnelle)(Merci, Philippe )

La fonction suivante retournera l'âge du client

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Option Explicit
 
Public Function age_client(lenom As String, leprénom As String, lavoiture As String) As Byte
 
Dim laconcat As String
 
laconcat = Application.WorksheetFunction.TextJoin(" - ", True, Array(lenom, leprénom, lavoiture))
 
Dim eq As Long
 
eq = Evaluate("=IFERROR(MATCH(""" & laconcat & """,T_Données[Concat],0),0)")
 
age_client = IIf(eq = 0, 0, Range("T_Données[Age]")(eq))
 
End Function
 
Public Sub Test()
 
Debug.Print age_client(lenom:="DURAND", leprénom:="PATRICK", lavoiture:="AUDI")
 
End Sub

Résultat dans la fenêtre exécution de l'éditeur VBE (CTRL + G): 42

Explication:

La fonction va procéder à la concaténation de ses 3 paramètres, de manière identique à la formule ci-dessus.
Elle recherche l'emplacement (fonction EQUIV sur EXCEL, MATCH en VBA) dans la colonne "Concat".
Elle retournera l'index de la colonne Age correspondant à celui-ci.

Dans le tableur Excel, cette fonction peut, bien entendu, être directement utilisée.

Formulation en cellule H6:



Une remarque qui peut avoir son importance:
Par souci de présentation, la colonne "Concat" peut être masquée. La fonction restera effective.



Bon week-end à tous.

[ARTURO83]

Bonjour,

Autre proposition:
En G2, formule matricielle à valider avec CTRL + SHIFT + ENTREE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX($C$10:$F$18;EQUIV(1;($C$10:$C$18=$D$2)*($D$10:$D$18=$D$3)*($E$10:$E$18=$D$4);0);4)

Cdlt

[MarcelG]

Bonjour Arturo, Bonjour le Forum,

Je n'ai jamais eu le courage d'écrire une telle formule.
Une fois le développement VBA effectué, la formulation devient nettement plus aisée (voir image ci-dessus).

La formule proposée par Arturo n'en reste pas moins effective, bien entendu.

Au demeurant, je me demande si l'utilisation de références structurées ne serait pas opportune.
Cette précaution permettrait une gestion plus souple de la plage de données si celle-ci évolue.

C'est juste mon avis.

[MarcelG]

Bonjour à vous, Bonjour au Forum,

Quelques améliorations
1 - afin d'homogénéiser la concaténation sur la formulation Excel et celle VBA,
Une seule cellule, nommée ici "carconcat" , peut servir de jointure. Celle-ci pouvant être vide.
2 - Joindre.Texte étant, avant tout, une fonction de feuille de calcul, celle-ci peut (voire "doit") être utilisée dans la formulation.
A ce sujet, l'écriture peut s'effectuer par l'utilisation de la souris. C'est Excel qui reconnaîtra les colonnes de champs.

Soit

(Code à adapter)

Dans la table de données, colonne "Concat"

=JOINDRE.TEXTE(carconcat;VRAI;T_Données[@[Nom]:[Voiture]])

Et dans la fonction VBA

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Option Explicit
 
Public Function age_client(lenom As String, leprénom As String, lavoiture As String) As Byte
 
Dim strconc As String, laconcat As String
 
strconc = Sht_Données.Range("carconcat").Value
laconcat = Application.WorksheetFunction.TextJoin(strconc, True, Array(lenom, leprénom, lavoiture))
 
Dim eq As Long
 
eq = Evaluate("=IFERROR(MATCH(""" & laconcat & """,T_Données[Concat],0),0)")
 
age_client = IIf(eq = 0, 0, Range("T_Données[Age]")(eq))
 
End Function
 
Public Sub Test()
 
Debug.Print age_client(lenom:="DURAND", leprénom:="PATRICK", lavoiture:="AUDI")
 
End Sub

A noter:

1 - Sht_Données désigne le CodeName de la feuille contenant la table de données "T_Données"
Voir à ce sujet le billet de l'ami Philippe Tulliez
Pour ma part, un bon usage découvert récemment.

2 - Comme je le signale souvent, il est utile, parfois nécessaire par souci de clarté, de préciser le nom du(des) paramètre(s) dans un appel de fonction ou de procédure paramétrée
Ainsi,
A

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Debug.Print age_client("DURAND", "PATRICK", "AUDI")

préférer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Debug.Print age_client(lenom:="DURAND", leprénom:="PATRICK", lavoiture:="AUDI")

[anasecu]

Bonjour MarcelG,

Une seule cellule, nommée ici "carconcat" , peut servir de jointure. Celle-ci pouvant être vide.

Il me semble que en utilisant ton tableau l'on peut nettement simplifier et avoir le résultat par une simple formule

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX(Tableau1[Age];EQUIV(D2&" - "&D3&" - "&D4;Tableau1[Concat];0))

Bien entendu si l'on peut aussi utiliser une cellule pour concaténer les champs de recherche.

[MarcelG]

Bonsoir anasecu, Bonsoir le Forum,

anasecu,

Dans ta formulation, il manque un paramètre: le numéro de colonne.

Ce qui donnerait

=INDEX(T_Données;EQUIV(H3&carconcat&H4&carconcat&H5;T_Données[Concat];0);4)

Et c'est justement là que la bât blesse, à mon humble avis.

Supposons que la table de données évolue et qu'une colonne y soit intégrée.
Le résultat serait erroné.

C'est la raison pour laquelle, je préfère reporter l'index de la colonne qui correspond à mon paramètre pour pouvoir utiliser cet index au sein de la colonne comprenant le résultat.
Et cette colonne est bien définie, quelle que soit sa place, car appelée par son nom "Age".

En termes d'écriture, qui plus est, la fonction est appelée de manière très simple.

=age_client(H3;H4;H5)

C'est un aspect qui a une certaine importance, selon moi.
C'est la raison pour laquelle je privilégie ce processus.

Mais, encore une fois, c'est juste mon avis.

[anasecu]

Bonsoir MarcelG

Dans ta formulation, il manque un paramètre: le numéro de colonne.

Tu as mal regardé car le numéro de colonne est facultatif pour INDEX et je fais cette fonction sur la colonne 'Age' et donc la colonne de décalage est zéro ou inutile.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX(Tableau1[Age];EQUIV(D2&" - "&D3&" - "&D4;Tableau1[Concat];0))

Supposons que la table de données évolue et qu'une colonne y soit intégrée. Le résultat serait erroné.

Comme je n'utilises que la colonne 'Age' c'est au contraire sans aucun problème car tu peux la déplacer où tu veux et introduire ce que tu veux sans souci.
C'est d'ailleurs de même pour ta colonne 'Concat' et c'est bien l'intérêt de l'utilisation du tableau structuré qui est ainsi évolutif.

Avant de proposer cette simplification, je l'avais testée

[MarcelG]

Bonsoir anasecu, Bonsoir le Forum,

Ah, OK!

J'avais mal adapté ta formule en ne précisant pas la colonne Age au 1er paramètre de la fonction Index

Une nuance.

Comme je l'ai écrit, dans mon 2ème post, mieux vaut utiliser la fonction Joindre.Texte

=INDEX(T_Données[Age];EQUIV(JOINDRE.TEXTE(carconcat;VRAI;G3:G5);T_Données[Concat];0))

Ensuite, c'est une question de choix personnel.
Pour ma part, je n'ai pas de problème pour écrire des fonctions simples sur tableur à partir d'un modeste développement VBA.
Et ce d'autant que la fonction Evaluate que j'utilise fait partie de mon bréviaire VBA.

En espérant que nos échanges seront profitables aux lecteurs.
Merci à toi et au plaisir.

[anasecu]

Bonjour MarcelG,

J'avais mal adapté ta formule en ne précisant pas la colonne Age au 1er paramètre de la fonction Index Une nuance.

Mais une nuance essentielle qui permet de s'affranchir de la positon des colonnes et permet la maintenance sans souci.

Comme je l'ai écrit, dans mon 2ème post, mieux vaut utiliser la fonction Joindre.Texte

Sauf que la fonction que tu préconises n'est disponible que sur 2019

Remarque : Cette fonctionnalité est disponible sur Windows ou Mac si vous avez Office 2019 ou si vous avez un abonnement Microsoft 365. Si vous êtes abonné à Microsoft 365. support.microsoft joindre-texte fonction

[MarcelG]

Bonjour anasecu, Bonjour le Forum,

anasecu,

Voici ma configuration



Je travaille avec Excel 2016 sur une configuration Office 365.
Dis-moi si je me trompe.

P..S. Tu peux m'interpeller "Marcel". G est l'initial de mon nom GLASSNOSSIOVITCHMATISLAV (voir mon avatar )

[anasecu]

Bonjour Marcel,

Je travaille avec Excel 2016 sur une configuration Office 365.Dis-moi si je me trompe)

J'ai bien mis que la fonction n'était disponible que si vous avez Office 2019 ou si vous avez un abonnement Microsoft 365 et donc avec 365 tu peux l'utiliser mais elle ne fonctionne pas sur les versions précédentes sans abonnement.

J'ai un peu modifié le classeur test en rajoutant des colonnes et les fonctions excel continuent de fonctionner comme la tienne mais l'on peut chercher d'autres colonnes comme le carburant aussi facilement.

Comme dirait quelqu'un que tu connais bien : "VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA..."

Bonne soirée à toi et au plaisir.

[MarcelG]

Bonsoir ami anasecu,

et donc avec 365 tu peux l'utiliser mais elle ne fonctionne pas sur les versions précédentes sans abonnement.

C'st bien ce que j'avais compris.
Je me suis placé, tacitement certes, dans un contexte entreprise (le-mien) où toutes les versions d'Excel sont déployées simultanément.

'on peut chercher d'autres colonnes comme le carburant aussi facilement

Dans ce cas-ci, à savoir: à partir de 2 données différentes pour un même item (ici le client), j'adopte automatiquement le même processus: une fonction "Typée".
Il m'arrive même de coder une telle fonction pour une seule valeur, ce qui me permet une adaptation aisée si d'autres se rajoutent.
Et c'est souvent le cas avec mes collègues clients .

Bien Marcel!...Euh..... Au fait.... Ne pourrait-on pas avoir aussi...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Option Explicit
 
Public Type leclient
        age As Byte
        carburant As String
End Type
 
Public Function données_client(lenom As String, leprénom As String, lavoiture As String) As leclient
 
Dim strconc As String, laconcat As String
 
strconc = Sht_Multicrit.Range("carconcat").Value
laconcat = Application.WorksheetFunction.TextJoin(strconc, True, Array(lenom, leprénom, lavoiture))
 
Dim eq As Long
 
eq = Evaluate("=IFERROR(MATCH(""" & laconcat & """,T_Données[Concat],0),0)")
 
With données_client
        .age = IIf(eq = 0, 0, Range("T_Données[Age]")(eq))
        .carburant = IIf(eq = 0, "Inconnu", Range("T_Données[Carburant]")(eq))
End With
 
End Function
 
Public Function age_client(lenom_age As String, leprénom_age As String, lavoiture_age As String) As Byte
 
age_client = données_client(lenom:=lenom_age, leprénom:=leprénom_age, lavoiture:=lavoiture_age).age
 
End Function
 
Public Function carburant_client(lenom_carburant As String, leprénom_carburant As String, lavoiture_carburant As String) As String
 
carburant_client = données_client(lenom:=lenom_carburant, leprénom:=leprénom_carburant, lavoiture:=lavoiture_carburant).carburant
 
End Function
 
Public Sub test()
 
Debug.Print age_client(lenom_age:="DURAND", leprénom_age:="PATRICK", lavoiture_age:="AUDI")
Debug.Print carburant_client(lenom_carburant:="DURAND", leprénom_carburant:="PATRICK", lavoiture_carburant:="AUDI")
 
End Sub

Et l'écriture, au sein du tableur devient

=carburant_client(H3;H4;H5)

Elle est alors similaire

Comme dirait quelqu'un que tu connais bien : "VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA..."

Hein? Quoi? KESAKI? KESAKO?

Je pense que ce que souhaite signifier notre grand big chief est qu'il faut comprendre la logique du tableur Excel AVANT de se lancer dans la programmation VBA.
Principe que je partage, bien entendu.
Or, si l'on regarde bien, nous suivons tous les deux la même démarche.
- Report de la place d'une valeur dans une colonne (Equiv tableur = Match VBA)
- Report de la valeur de l'index correspondant d'une autre colonne
La seule difficulté, selon moi, est le code quant à eq Evaluate.
Mais à partir du moment où celui-ci est intégré à notre bréviaire...

On pourrait, bien entendu, épiloguer à l'envi.

Au plaisir de te revoir dans d'autres discussions.

Bonne soirée à toi, Bonne soirée au FoRhum.

[laurent27530]

merci pour vos réponses instructives