Probleme pour combiner EQUIV et SOMMEPROD pour effectuer une recherche.

[wamastas]

Bonjour,

Dans un laboratoire, les résultats des analyses sont consignés dans des fichiers Excel.
Plusieurs paramètres peuvent varier d'un fichier à l'autre : le nombre de colonnes, de composés et d'échantillons.

Globalement les fichiers se présentent comme suit :

Composé1
ID Aire ... Concentration

Echantillon1 Airea1_1 ... Concentration1_1
Echantillon2 Airea2_1 ... Concentration2_1
Echantillon3 Airea3_1 ... Concentration3_1
etc...
Composé2
Echantillon1 Airea1_2 ... Concentration1_2
Echantillon2 Airea2_2 ... Concentration2_2
Echantillon3 Airea3_2 ... Concentration3_2
etc...
Composé3
etc...


Mon but est de créé une formule me permettant d'afficher la concentration d'un échantillon X pour un composé y, et ce sans utiliser VBA.

Pour cela j'avais pensé utiliser la fonction EQUIV en la combinant avec SOMMEPROD.


EQUIV("Compose1";[analyse.xls]feuille1!A:A;0) comme numéro de ligne de la première cellule de ma plage de recherche, notée a.

EQUIV("Composé2";[analyse.xls]feuille1!A:A;0) comme numéro de ligne de la deuxième cellule de ma plage de recherche, notée b.

SOMMEPROD(--([analyse.xls]feuille1!Ba:Bb="Echantillon2");--([analyse.xls]feuille1!Ha:Hb))


Lorsque je précise les numéros de cellule de ma plage de recherche, la fonction SOMMEPROD me renvoie bien la valeur que je recherche.

Par contre je n'arrive pas à utiliser la fonction EQUIV pour déterminer le numéro de ligne des cellules des plages de recherche de ma formule SOMMEPROD.

Je ne sais pas si je me complique la tache ou si cela est tout bonnement impossible de procéder de la sorte mais, avec mes maigres connaissances excel, je ne vois pas d'autre solution.

Merci d'avance pour votre aide.

 

[Misange]

Re : Probleme pour combiner EQUIV et SOMMEPROD pour effectuer une recherche.

Bonjour

sans un classeur exemple avec les différentes situations que tu peux rencontrer et le résultat attendu,il est vraiment très très difficile de t'aider.
va en mode avancé, clique sur le trombone et joint un petit fichier explicatif ce sera plus simple.

 

[wamastas]

Re : Probleme pour combiner EQUIV et SOMMEPROD pour effectuer une recherche.

Bonjour,

Je vais essayer de clarifier un peu.

Chaque échantillon de la même provenance sera analysé 2 fois le même jour et ce pendant 11 jours(potlow1 et potlow2 par exemple). Afin d'augmenter la lisibilité des résultats, je cherche a récupérer, pour chaque composé (Monochloroacetic, Monobromoacetic, ), les valeurs de la concentration par échantillon et par jour d'analyse (analyse1.xls, analyse2.xls, ...) en les consignant dans un tableau récapitulatif.

Le nombre d'échantillons changeant a chaque série d'analyses, je pensais utiliser EQUIV (MATCH) pour déterminer le numéro de la ligne contenant le nom du premier composé puis le numéro de celle contenant le nom du deuxième composé (par exemple Monochoroacetic 1 en A5 et composer 2 en A44). Je voulais intégrer Equiv a sommeprod pour définir la plage ou chercher pour trouver le nom de l'échantillon (C5:C44) et afficher la concentration (H1:H44).

En gros utiliser : =SUMPRODUCT(--([Analyse1.xls]Sheet1!C5:C44="PotLow1"),([Analyse1.xls]Sheet1!H5:H44)) mais en trouvant un moyen de remplacer le 5 de C5 par une recherche de numéro de ligne avec equiv (match).

Je ne sais pas si cela est possible sans utiliser de macro... Un moyen plus simple d'arriver a mes fin existe surement...

Je m'excuse d'avance, mais je ne peux vous envoyer que des fichiers excel 2002 version US.

 

[Misange]

Re : Probleme pour combiner EQUIV et SOMMEPROD pour effectuer une recherche.

Est-ce que tes données (analyse) sont obligatoirement sous cette forme ??? le moins que l'on puisse dire c'est que ce n'est pas la plus simple à gérer.
A priori, sachant que le nombre d'échantillons peut varier, la solution la plus simple pour moi et de très loin c'est un tableau croisé dynamique mais cela impose de reformater la base autrement, en incluant une colonne ou le nom de l'échantillon est répété, idem pour la date et pour le N° de l'analyse (ne mélange pas les deux, ça ne fait que compliquer).
Autre chose : tu as forcément un classeur par analyse ? C'est bien plus simple de tout mettre dans le même. En fait le plus simple c'est une seule feuille avec tous les résultats. Ensuite c'est juste un glissé d'étiquettes pour avoir le résultat.

 

[wamastas]

Re : Probleme pour combiner EQUIV et SOMMEPROD pour effectuer une recherche.

Merci de ta réponse.
Grâce a toi je me suis aperçu que j'avais a ma dispo un autre fichier, consignant les résultats par échantillon

L'avantage c'est que le nombre de composés analysés ne varie pas, donc peu importe le nombre d'échantillons.
J'ai décider d'utiliser index+equiv(match) pour récupérer les valeurs des concentrations des composés. Mais même après avoir essayer plusieurs syntaxe, je n'obtient que des N/A dans les colonnes C E et G de mon fichier "récapitulatif".
Les colonnes D F H contenant les références directe aux cellules du fichier "analyse1" renvoie bien les valeurs recherchées.

Je joint les 2 fichiers.

Merci encore pour votre aide.