Rechercher des valeurs dans un tableau avec deux critères

[josanche]

Bonjour le forum,

J'ai besoin de votre aide. J'ai posté récemment une question similaire à celle-ci mais je ne suis pas parvenu à trouver de réponses à mon problème par moi-meme. Dans mon fichier en pièce jointe, il y a trois colonnes à compléter sur base du tableau qui se situe sur les colonnes allant de G à K. Je voudrais une fonction qui recherche dans ce tableau la bonne cellule et qui renvoie le pourcentage qui correspond au deux critères (couple CODE et LETTRE) pour chaque colonne C,D et E. Je vous invite à voir mon fichier excel, il est très intuitif à comprendre.

Voici mon fichier excel en pièce jointe

Pourrais-je avoir la formule excel en anglais ?

merci d'avance

 

[Dugenou]

Re : Rechercher des valeurs dans un tableau avec deux critères

Bonjour Josanche,
=SOMMEPROD(($G$6:$G$29=$A6)*($H$6:$H$29=$B6)*I$6:I$29) à recopier à droite et vers le bas
je pense qu'en anglais ça donne :
=SUMPRODUCT(($G$6:$G$29=$A6)*($H$6:$H$29=$B6)*I$6:I$29)
Cordialement

 

[josanche]

Re : Rechercher des valeurs dans un tableau avec deux critères

Merci pour votre aide !!! Pas évident à comprendre la structure de la formule sumproduct.

 

[Dugenou]

Re : Rechercher des valeurs dans un tableau avec deux critères

Josanche,
Je suis bien d'accord, j'ai mis plusieurs semaines à l'assimiler. L'idéal étant de se frotter à de nouveaux problemes et de tenter des réponses puis voir les réponses des barbatrucs et autres experts.
Pour cette utilisation c'est assez simple : on écrit chaque critère dans une parenthèse, on multiplie chaque critère pour faire un ET (on additionne si on veut un OU) et on multiplie par le résultat souhaité : ici le % (1 quand on veut faire un comptage). Cela fonctionne seulement si chaque résultat est unique dans la base de données : si on avait deux lignes avec les mêmes étiquettes le résultat obtenu serait la somme des deux valeurs correspondantes.
Il faut imaginer que pour chaque critère écrit (plage = valeur) : on a une matrice composée de 0 ou de 1 (faux ou vrai) et donc quand on multiplie par la plage résultat on a des 0 ou des valeurs, ensuite on fait la somme.

Cordialement

 

[josanche]

Re : Rechercher des valeurs dans un tableau avec deux critères

merci pôur votre explication hyper détaillé. Je vais essayer de m'entrainer pour mieux maitriser la formule

Cordialement

 

[josanche]

Re : Rechercher des valeurs dans un tableau avec deux critères

bonjour,

j'ai essayé votre formule mais j'ai deux points à dire:

- Si dans le tableau de colonnes, on retrouve des celulles du type #DIV/0!, par conséquent la formule ne marche plus sur toutes les colonnes ou j'ai tiré vers le bas la formule.
- La formule est très sensible à la présence d'espace.
Par exemple, supposons que j'ai deux cases vides: lorsque je clique sur la première cellule vide, je suis sur le premier espace vide de la cellule et quand je clique sur ma deuxième cellule vide, je suis sur ma souris sur le 3 espace vide. On peut faire également l'analogie lorsqu'il y a des lettres.

Y at-il une formule de tel sorte que ces espaces vides superflus qui existe à la fin de chaque lettre disparaissent et de tel sorte que chaque mots ou chiffres commence bien sur le premier espace vide. ???

Cordialement

 

[Dugenou]

Re : Rechercher des valeurs dans un tableau avec deux critères

Bonjour,
vous pouvez essayer la fonction SUPPRESPACE (en français) qui enlève tous les espaces sauf les espaces simples entre les mots, mais pas dans la formule car elle ne s'applique pas sur une plage. Vous allez devoir créer une colonne supplémentaire pour chaque colonne d'étiquettes.
Mais il y a aussi des espaces non souhaités dans votre liste de référence. De plus la valeur renvoyée est en texte, il faut donc adapter pour la première colonne : voir pj
Cordialement

Edit : oups Merci Jocelyn : je n'étais pas allé au bout du test avant d'écrire la partie en rouge : on a donc pas besoin des colonnes supplémentaires

Edit2 : fichier corrigé avec la formule de Jocelyn : si ouvert avec excel anglais la formule devrait être traduite

 

[Jocelyn]

Re : Rechercher des valeurs dans un tableau avec deux critères

Bonjour le Forum,
Bonjour josanche, Dugenou,

si tu risque d'avoir des espaces parasites et que tu ne veuilles pas les traiter dans les colonnes (ce qui me semble être le mieux) tu peux toujours modifier la formule de Dugenou de cette façon

=SOMMEPROD((SUPPRESPACE($G$6:$G$29)=SUPPRESPACE($A6))*(SUPPRESPACE($H$6:$H$29)=SUPPRESPACE($B6))*I$6:I$29)

Cordialement

 

[josanche]

Re : Rechercher des valeurs dans un tableau avec deux critères

coucou dugenou et jocelyn,

Merci pour votre aide ! Merci pour la formule Trim (supressespace) en anglais ! elle marche bien.

 

[josanche]

Re : Rechercher des valeurs dans un tableau avec deux critères

Rebonjour Jocelyn et dugenou

Sur le fichier que vous m'avez envoyé dugenou, j'ai remarqué que votre formule renvoit des chiffres 0% au trois colonnes lorsque la formule sommeprod n'arrive pas à trouver le couple de critères dans le tableau de référence. Je désirerais, au lieu d'avoir dans mes trois colonnes 0%, que la formule renvoit 3 cellules vides si la formule sommeprod n'arrive pas à trouver le couple de critères dans le tableau de référence.

Je vous envoie en pièce jointe le fichier pour que vous puissiez y répondre intuitivement sans lire le blabla ci-haut.

cordialement

 

[Dugenou]

Re : Rechercher des valeurs dans un tableau avec deux critères

Hello George
utilisez le format personnalisé : 0.00%;0.00%;

 

[josanche]

Re : Rechercher des valeurs dans un tableau avec deux critères

Coucou Dugenou,

Le format personnalisé n'arrange pas au problème. En fait, il enlève tous les 0% contenu dans les trois colonnes, ce qui n'est pas le choix le plus optimal. Si la formule sommeprod ne retrouve pas de valeurs correspondante dans le tableau de références pour un couple de valeur spécifique, (comme dans le cas du fichier que je vous ai envoyé), il doit renvoyer des cellules vides pour montrer qu'il s'agit bien de missing values ! (=valeurs manquante)

J'ai utilisé cette formule pour pouvoir résoudre à mon problème dans le fichiermais la formule semble pas marcher, surement du à une erreur. J'ai intégré la formule dans mon fichier. il est en pièce jointe

=IF(COUNT(SUMPRODUCT((TRIM($G$6:$G$29)=TRIM($A7))*(TRIM($H$6:$H$29)=TRIM($B7))*I$6:I$29)>=1);SUMPRODUCT((TRIM($G$6:$G$29)=TRIM($A7))*(TRIM($H$6:$H$29)=TRIM($B7))*I$6:I$29);"")

Merci en tt cas pr votre temps, je vous suis très reconnaissant

cordialement

 

[Dugenou]

Re : Rechercher des valeurs dans un tableau avec deux critères

Enlever le count et multiplier par 1
If(sumproduct((trim($g$6:$g$29)=trim($a7))* (trim($h$6:$h$29)=trim($b7))*1)>=1);sumproduct((trim($g$6:$g$29)=trim($a7))*(trim($h$6:$h$29) =trim($b7))*i$6:i$29);"")

 

[josanche]

Re : Rechercher des valeurs dans un tableau avec deux critères

ok très bien merci pour la précision )) mais pourquoi vous avez fait *1 juste pour savoir ?questce que ca change ? on fait *1 pour la formule ne renvoit plus du texte c ca ?

 

[Dugenou]

Re : Rechercher des valeurs dans un tableau avec deux critères

Non, c'est pour faire un comptage : comme je le disait dans le message #4 : on a une matrice de vrai et faux : si on multiplie par 1 et qu'on fait la somme on a un nombre de cellules qui remplissent les conditions.
Bon week end !