[excel] comment utiliser INDEX/EQUIV avec 2 conditions

Version imprimable

28/11/2007, 10h57
zaza45

[excel] comment utiliser INDEX/EQUIV avec 2 conditions

Bonjour

J'ai un problème sans doute de synthaxe avec une formule

J'ai une table A1:E20
Je recherche une valeur dans la colonne E de ma table A1:E20
si dans la colonne D je trouve la valeur D2
et si dans la colonne B, je trouve la valeur B2

Code:

=INDEX(Feuil1!$A$1:$E$20;EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0);5)

la premiére condition fonctionne bien, mais je ne trouve pas comment associer la 2ème condition dans la fonction equiv

EXCEL me signale que j'ai une erreur avec la synthaxe suivante:

Code:

=INDEX(Feuil1!$A$1:$E$20;EQUIV(ET(Feuil2!D2;Feuil1!$D$1:$D$20;0);(Feuil2!B2;Feuil1!$B$1:$B$20;0);5)

Merci de votre aide
28/11/2007, 11h44
bab22
Bonjour,

Ton erreur de syntaxe vient du fait que la fonction ET renvoie VRAI ou FAUX et donc la sortie ne peut pas etre utilisée dans ta fonction EQUIV.

A mon avis, tu dois d'abord vérifier que
Code:

1 2 EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0) = EQUIV(Feuil2!B2;Feuil1!$B$1:$B$20;0)
car tu ne pourras pas trouver une valeur dans la colonne E si la formule renvoie deux lignes différentes (une pour Feuil2!B2 et une pour Feuil2!D2)
et après utiliser l'index sur la valeur unique trouvée.

Sur une meme ligne de code, ca donne
Code:

1 2 =INDEX(Feuil1!$A$1:$E$20;SI(SI(EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0)=EQUIV(Feuil2!B2;Feuil1!$B$1:$B$20;0);"O";"N")="O";EQUIV(Feuil2!D2;Feuil1!$D$1:$D$20;0);"N");5)
Le seul problème est que cela renvoie #VALEUR! s'il n'a pas trouvé B2 et D2 dans les colonnes B et D.

J'espère que ca pourra t'aider.
28/11/2007, 13h42
cafeine

Hello,

si tu concatènes tes 2 colonnes, tu peux revenir au cas d'une seule condition, non ?
28/11/2007, 14h01
Michel_M

bonjour à tous,
je suppose que la formule est en feuil1 et qu'il faut que les valeurs B2 et D2 soient sur la m^ligne dans le colonnes B et D

Code:

=si(equiv(feuil2!D2;D1:D20;0)=equiv(feuil2!B2:B1;0);index(E1:E20;equiv(feuil2!B2;B1:B20;0));"")

n'est pas traité le cas où D2 ou B2 ne sont pas dans les colonnes B et D

formule non testée, syntaxe à vérifier...
28/11/2007, 14h12
zaza45

1 pièce(s) jointe(s)

Bonjour à tous

merci de me donner un gros coup de pouce, car je suis complètement perdue.
La première formule me donne effectuivement un #valeur
et la seconde prend la condition à vide
je vous envoie le fichier, ce sera peut-être plus simple

En fonction du
28/11/2007, 14h45
Michel_M

re,

en regardant ton fichier, tu as plusieurs problèmes: les numéros d'ordre et le modèle apparraissent plusieurs fois: par exemple 4 et AW (de mémoire); pire tu as plusieurs lignes identiques dans table! AW et 7 par exemple. (et m^ avec les dates)
IL faudrait donc concaténer comme le proposait Cafeine et qu'il n'y ait pas de doublons dans la concaténation.
Ce n'est qu'une fois résolu ces problèmes qu'on pourra trouver une solution.
28/11/2007, 14h52
wilfried_42

re:

un autre solution mais toujours avec la meme condition que michel, pas de doublon

Code:

=index(matrice;sommeprod((plage1=critere1)*(plage2=critere2)*ligne(plage1))-1;colonne)

en rouge -1 si ta matrice commence en ligne 2, à adapter
28/11/2007, 19h14
Michel_M

Bonsoir à tous,

Mine de rien, c'est loin d'être évident ton truc! ne serait ce pas sponsorisé par doliprane?

Ci joint proposition à partir d'une concaténation comme le préconisait Caféine et en utilisant "decaler..."

J'ai pas tout vérifié, donc.. et à toi de réaménager si cette usine à gaz te convient.

Amicalement

solution macro vb

Bonjour à tous

et merci à wilfried

Après plusieurs essais wielfried à résolu le pb avec la fonction suivante si cela intéresse quelqu'un :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Function gw_recherche(val1 As Range, val2 As Range, rest As Range) As Variant
    Dim gwcel As Range, i As Long
    a = val1.Value: b = val2.Value
    i = 0
    For Each gwcel In Range("gw_code")
        If gwcel = val1.Value Then
            If Range("gw_codif")(i) = val2.Value Then
               gw_recherche = rest(i)
               Exit For
            End If
        End If
        i = i + 1
    Next
End Function

C'est super et très rapide en temps de réponse
Merci à tous

23/12/2007, 21h39
Bigalo

1 pièce(s) jointe(s)

Bonjour,

Nouvel arrivé sur le forum, j’ai parcouru les questions récentes. Celle-ci m’a paru intéressante, et j'ai voulu trouver une solution sans faire appel à VBA.

Code:

{=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;SI(D1:D20 = 'nouveau modèle'!D$2;B1:B20;"");0))}

à valider en matricielle, fonctionne parfaitement sur ton fichier Index Equiv.xls, que je remets en pièce jointe.

L'astuce, assez classique, consiste à remplacer dans la formule le contenu testé en colonne B, par une chaine vide, si on ne trouve pas la valeur cherchée en colonne D la valeur de référence.

Ainsi EQUIV() renvoie un indice où les 2 conditions sont remplies, ce qui permet d'employer ensuite INDEX().

Attention, s’il y a plusieurs lignes ayant les valeurs cherchées à la fois dans les colonnes B et D, le résultat obtenu correspondra à la première ligne remplissant les 2 conditions.

Cordialement,

Michel Gaboly

Citation:

Envoyé par zaza45

Bonjour à tous

et merci à wilfried

Après plusieurs essais wielfried à résolu le pb.

C'est super et très rapide en temps de réponse
Merci à tous
03/01/2008, 11h52
zaza45

Bonjour Bigalo et Bonne Année

L'année commence bien...

Je viens de rentrer de congès et j'ai testé ta formule. Super cela fonctionne parfaitement sur mon fichier réel.

Mais je ne comprends pas l'astuce de la chaine vide si on ne trouve pas la valeur cherchée en colonne D.

Effectivement
=SI(D1:D20 = 'nouveau modèle'!D$2;B1:B20;"")
me donne toujours une chaine vide mais ma valeur en colonne D existe bien.

Je n'ai pas assez de notion sous excel pour comprendre la subtilité. Peux-tu m'expliquer plus en détail

Merci beaucoup pour ton idée géniale
03/01/2008, 11h55
Pierre Fauconnier

Bonjour

C'est marrant. Je viens de répondre ici à une question similaire...

Zaza45... As-tu bien validé la formule comme une matricielle? (ctrl+maj+enter)
03/01/2008, 12h28
zaza45

Bonjour Pierre

Le problème n'est pas tout à fait le même dans mon fichier car je dois gérer des doublons. La solution de Bigalo fonctionne bien mais je ne comprends pas comment cela fonctionne.

=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;SI(D120 = 'nouveau modèle'!D$2;B1:B20;"");0))

Ma culture sur excel est assez succinte!

En tout cas merci pour l'info
03/01/2008, 12h29
Bigalo
Bonjour,

Merci pour le compliment dithyrambique 8-)
- Sélectionne la cellule G4 de la feuille "Table" du fichier que je t’ai renvoyé.
- Puis dans la barre de formules, sélectionne "SI(D1: D20 = 'nouveau modèle'!D$2;B1:B20;"")". (NB l’espace entre ":" et "D20" évite que ":" suivi de "D" soit transformé en smiley)
- Appuie sur la touche "F9", tu obtiens le résultat de la fraction de formule sélectionnée :
Code:

{"";"";"";"";"";"";"";"";"";"";"";"";"";"";"AW";"AK";"";"";"";""}

Tu constates alors que la fonction SI() a pour effet de remplacer pour le calcul, les valeurs de la plage B1:B20 par elles-mêmes si on a sur la même ligne, en colonne D, la valeur de la cellule D$2 de la feuille "nouveau modèle", ou par une chaîne vide dans le cas contraire.

La formule en G4, à valider en matricielle,

Code:

{=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;SI(D1:D20 = 'nouveau modèle'!D$2;B1:B20;"");0))}

équivaut donc à :

Code:

{=INDEX(E1:E20;EQUIV('nouveau modèle'!B2;{"";"";"";"";"";"";"";"";"";"";"";"";"";"";"AW";"AK";"";"";"";""};0))}

Le but du jeu est de trouver en colonne D la première ligne où 2 critères sont respectés : en colonne B, la valeur en B2 de la feuille "nouveau modèle", et en colonne D, la valeur en D2 de cette même feuille.

La substitution opérée par la fonction SI() revient à éliminer pour la recherche les valeurs de la colonne B, pour lesquelles la condition portant sur la colonne D n’est pas remplie. Cela revient donc à chercher en colonne B les lignes remplissant l’égalité avec le contenu de la cellule B2 de la feuille "nouveau modèle" parmi les seules lignes où la condition portant sur la colonne D est remplie.

Ce faisant, la première cellule trouvée avec EQUIV() remplit nécessairement les 2 conditions !

Cordialement, et bonne année à toi aussi.

Citation:

Envoyé par zaza45

Bonjour Bigalo et Bonne Année

L'année commence bien...

Je viens de rentrer de congès et j'ai testé ta formule. Super cela fonctionne parfaitement sur mon fichier réel.

Mais je ne comprends pas l'astuce de la chaine vide si on ne trouve pas la valeur cherchée en colonne D.

Effectivement
=SI(D1:D20 = 'nouveau modèle'!D$2;B1:B20;"")
me donne toujours une chaine vide mais ma valeur en colonne D existe bien.

Je n'ai pas assez de notion sous excel pour comprendre la subtilité. Peux-tu m'expliquer plus en détail

Merci beaucoup pour ton idée géniale
03/01/2008, 12h31
Bigalo

Re,

Pierre, tu sais bien que les grands esprits se rencontrent 8-)

A bientôt.

Citation:

Envoyé par Pierre Fauconnier

Bonjour

C'est marrant. Je viens de répondre ici à une question similaire...
03/01/2008, 13h20
zaza45

Ca y est, j'ai comprisl'astuce:yaisse2:.
Je pense que je vais réutiliser assez souvent cette fonction.
Moi qui ne connaissait même pas la fonction INDEX/EQUIV!
Merci beaucoup pour tes explications très simples.
03/01/2008, 13h29
Pierre Fauconnier

Citation:

Envoyé par Bigalo

Re,

Pierre, tu sais bien que les grands esprits se rencontrent 8-)

A bientôt.

Je sens que cette année commence en beauté, Michel... Content de te retrouver ici, en tout cas...!! :D
03/01/2008, 13h40
Bigalo

C’est un couple effectivement très utile.

Il y a également une fonction très puissante et sous-employée, c’est SOMMEPROD(). Tu la connais ?

Par exemple dans ton fichier,

Code:

=INDIRECT("E" & SOMMEPROD((B2:B19 = 'nouveau modèle'!B$2) * (D2:D19 = 'nouveau modèle'!D$2) * LIGNE(B2:B19)))

fournit également ce que tu cherches à condition, qu’il n’y ait qu’une ligne répondant aux 2 critères.

Cordialement,

Citation:

Envoyé par zaza45

Ca y est, j'ai comprisl'astuce:yaisse2:.
Je pense que je vais réutiliser assez souvent cette fonction.
Moi qui ne connaissait même pas la fonction INDEX/EQUIV!
Merci beaucoup pour tes explications très simples.