[XL-2007] Recherchev() avec index() et equiv() [Résolu]

[AgriPhilou]

Bonsoir,

Comment combiner les fonctions RECHERCHEV;INDEX;EQUIV

pour rechercher un client dans une matrice en tenant compte
de son dernier achat et ce qu'il a acheté ?

Exemple :

dans la matrice
Le Client 12
Dernier achat le 03/01/2011
quoi ? un vêtement.

Je tape le N° du client en G10
En H10 j'ai la date du dernier achat
Excel doit me trouver "Vêtement" en J10 ou est la formule combinée avec RECHERCHEV;INDEX; et EQUIV

Merci.

[Pierre Fauconnier]

Salut.

C'est une matricielle (ou SOMMEPROD) qui te sortira d'affaires.

[AgriPhilou]

Merci Pierre, je ferais des essais demain et je te dirais si ça va.

D'accord ?

Bonne soirée

[AgriPhilou]

Bonjour Pierre,

ça ne fonctionne pas, la colonne "achat" n'est pas prise en compte pour renvoyer le résultat du dernier achat du client N° 12 par exemple.

Encore merci.

[U. Milité]

Bonjour,

Apparemment, je n'ai pas compris la même chose que Pierre Fauconnier ... Au départ de son exemple, une proposition différente:

[Pierre Fauconnier]

U. Milité,

Je pense que le résultat est identique (à données égales).


Le but étant de chercher la date du dernier achat d'un client X, puis de chercher ce qu'il a acheté...

Tu y réponds en deux formules différentes et moi en une, mais il me semble que le résultat devrait être identique, à données identiques... Et ta façon de faire est plus simple...

[U. Milité]

Bonjour Pierre Fauconnier,

En fait je me suis d'abord centré sur le résultat affiché dans ton message et, selon ce que j'avais compris, le résultat escompté ne pouvait être celui-là: le client 12 n'ayant pas acheté de gants le 24/01/11.
En voyant ton second message, je me suis dit que nous avions compris la même chose et que, peut-être, au moment de ta capture d'écran, tu n'avais pas encore recopié ta formule en F3 ... ce qui aurait expliqué la différence de résultats.
Depuis, j'ai beau les retourner dans tous les sens, je n'arrive pas à obtenir des résultats cohérents avec nos formules (en tout cas pas dans tous les cas!)

[Pierre Fauconnier]

Bien vu!

Index pointait une ligne trop bas... Il faut en effet diminuer le résultat de SOMMEPROD de 1.




Par contre, je pense que ta formule pour récupérer le vêtement pose problème dans la mesure où min(...) renverra 0 dès que la condition du SI n'est pas respectée... C'est pourquoi j'ai remplacé ta formule et j'ai utilisé INDEX et EQUIV, en basant la recherche de EQUIV sur une concaténation matricielle.

[U. Milité]

Bonsoir,

C'est pourquoi j'ai remplacé ta formule et j'ai utilisé INDEX et EQUIV, en basant la recherche de EQUIV sur une concaténation matricielle

D'accord! (de toute façon, je n'avais pas eu le temps de vraiment m'y attacher )

P.S.: oserais-je avouer que la tienne, modifiée, me semble encore poser problème avec le client 34?

[Pierre Fauconnier]

Envoyé par U. Milité

...

P.S.: oserais-je avouer que la tienne, modifiée, me semble encore poser problème avec le client 34?

Oui et non

Oui pour la date via SOMMEPROD (je vais rechercher l'erreur)

Non pour les vêtements. En fait, les deux réponses me semblent "justes" car le client 34 a acheté deux vêtements ce jour-là (lignes 3 et 6)...

Il se fait qu'une formule renvoie le premier achat renseigné pour ce jour et que l'autre renvoie le dernier vêtement acheté ce jour...

On va y arriver ?

[U. Milité]

On va y arriver ?

Ben ... Euh ...
Cette partie de ta formule:

Code :

LIGNE($A$2:$A$10)*((($A$2:$A$10=$F$1)*$B$2:$B$10)=MAX(($A$2:$A$10=$F$1)*$B$2:$B$10))

Renvoie bien la matrice

Code :

{0;3;0;0;6;0;0;0;0}

3 et 6 sont bien les n° de ligne du client 34, mais

Code :

SOMMEPROD(LIGNE($A$2:$A$10)*((($A$2:$A$10=$F$1)*$B$2:$B$10)=MAX(($A$2:$A$10=$F$1)*$B$2:$B$10)))

donne 9! (même en soustrayant 1 au résultat ... on n'obtient jamais que 8 ... qui n'est plus du tout un n° de ligne correspondant audit client

[Pierre Fauconnier]

En fait, c'est tout à fait logique que la formule avec SOMMEPROD ne fonctionne pas lorsqu'il y a plusieurs réponses possibles...

Dans le cas illustré, les lignes 3 et 6 renvoient le bon résultat. Or (3+6)-1 = 8 ...

Donc, ma technique ne peut renvoyer un résultat correct que si une seule ligne correspond aux conditions énoncées.

Par contre, en cas d'ex equo, la deuxième formule (Index(...;Equiv(...)) avec Equiv sur deux colonnes renverra toujours la première ligne trouvée, et on loupera donc une info...

[U. Milité]

Bonjour,

Envoyé par Pierre Fauconnier

Par contre, en cas d'ex equo, la deuxième formule (Index(...;Equiv(...)) avec Equiv sur deux colonnes renverra toujours la première ligne trouvée, et on loupera donc une info...

Il ne reste qu'à espérer que cette demande ne faisait pas partie du "cahier des charges"

Merci pour cet échange et bon dimanche!

[Pierre Fauconnier]

Dans ce cas, on poussera l'utilisation de la matricielle jusqu'au bout, avec l'utilisation de GRANDE.VALEUR.

Dans l'illustration ci-dessous, je démarre la liste récupérée en ligne 13. Je peux donc utiliser (13-12)=1 pour récupérer la première grande.valeur, puis en tirant la formule vers le bas, (14-12)=2 pour récupérer la deuxième grande.valeur, et ainsi de suite jusqu'à récupérer un #VALEUR!




Mais dans ce cas, je trouve plus pertinent de récupérer les données par filtre avancé...

D'une façon générale, il serait intéressant pour la lisibilité de la formule, d'utiliser les tableaux 2007/2010 qui permettent d'obtenir des plages dynamiques dont les noms (nom du tableau puis nom de la colonne) sont plus significatifs que les adresses de plage.

Bon dimanche également...

[AgriPhilou]

Merci beaucoup à vous deux, ne vous inquiétez pas, il me faut uniquement le dernier achat, le dernier des derniers, je n'ai pas encore testé mais je pense que cela sera le bon résultat, si vous voulez refaire des tests je vous en remercie par avance.

Par contre j'ai rajouté une colonne,

En A = code client
En B = type "Achat" "Vente" "location" "autre"
En C = la date du type
En D = les vêtements

Donc pour le client 12 dont le dernier "Achat" (uniquement achat)
du 02/02/2011
EXCEL doit me renvoyer par exemple "pantalon" si c'est un pantalon acheté le 02/02/2011.

J'espère mettre bien expliqué.

Encore merci pour votre aide.

Bon Dimanche

[Pierre Fauconnier]

Alors, tu pourras "simplement" utiliser MAX qui renverra la dernière valeur saisie répondant aux conditions (au cas où il y en aurait plusieurs)...

[U. Milité]

Bonjour,

Il suffit d'ajouter la condition dans les formules ...
F8 sera ici la cellule dans laquelle j'ai indiqué "Achat"

Code :

=MAX(SI((A2:A10=F7)*(B2:B10=F8);C2:C10))

et

Code :

=INDEX(D2:D10;EQUIV(F7&F8&F9;A2:A10&B2:B10&C2:C10;0))

Ne pas oublier de valider avec Ctrl+Maj+Enter

[AgriPhilou]

Merci,

Tu peux me mettre une image comme exemple ou un petit fichier pour moi étudier les formules ?

Merci.

[Pierre Fauconnier]

Attention que, si plusieurs lignes répondent aux conditions:

• la formule de u.milité renvoie le premier vêtement acheté qui répond aux conditions
• la mienne renvoie le dernier vêtement acheté qui répond aux conditions

[AgriPhilou]

Merci Pierre,

Je qu'elle formule ?

Étant donné :

En A = code client
En B = type "Achat" "Vente" "location" "autre"
En C = la date du type
En D = les vêtements

Donc pour le client 12 dont le dernier "Achat" (uniquement achat)
du 02/02/2011
EXCEL doit me renvoyer par exemple "pantalon" si c'est un pantalon acheté le 02/02/2011.


Merci.