Formule sommeprod(recherche) me crée une erreur
Bonjour,
J'ai une formule qui me ramène un message d'erreur et j'ignore pourquoi,
Pour expliquer le tableau, nous avons plusieurs types de prestations 'mh' 'CH' etc... et ces prestations sont redivisés en de nombreux numéros, par exemple le Mh a des données de 1 à 388 et cette prestation correspond a 0.5h, jusque là la formule ne me crée aucun soucis, elle calcule bien les données.
Cependant nous avons quelques exceptions 79 Bis, 529 Bis et 544 Bis (uniquement ces 3 là) Je les ai mises dans ma plage de données En S2:U14 avec les temps correspondants, mais cela semble fonctionner pour le 79 Bis mais pas pour le 529 et 544 Bis et j'ignore complètement pourquoi.
Je laisse ci-joint mon tableau excel si quelqu'un arrive à me sauver, j'espère que ma publication est assez claire et compréhensible c'est la première fois que je poste sur le forum.
Bonjour,
Tout est une question d'ordre dans le tableau en S:U, Sélectionnez ce tableau et faites un tri de A à Z sur la partie Num
Cdlt
Bonjour,
j'ai mis du temps à comprendre et j'ai trouvé en lisant la docu de la fonction Recherche ;
Les valeurs dans lookup_vector doivent être placées dans l’ordre croissant : ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE ; Dans le cas contraire, la fonction LOOKUP risque de ne pas retourner la valeur correcte. La fonction ne fait pas de distinction entre les majuscules et les minuscules.
lookup_vector = vecteur_recherche, donc la colonne T.
En mettant cet ordre ça marche ;
0
1
389
439
509
601
529 Bis
529B
544 Bis
544B
79 Bis
79BMais il y a des trucs bizarres : si je mets 79c en colonne L, il me le trouve alors qu'il n'y en a pas en colonne T
Bonjour à tous,
Comme indiqué dans la doc, RECHERCHE est une vieille fonction. Il serait préférable d'utiliser RECHERCHEV dans ce cas, ou INDEX+EQUIV.
@DouxReveur, le "match" est trouvé car la fonction fait une recherche approximative (c'est pour ça que les valeurs ont besoin d'être triées d'ailleurs). Fonctionnement équivalent à RECHERCHEV avec le 4e argument sur VRAI.
Merci saboh pour l'info !
Bonjour,
Merci à tous pour vos réponses, le tri porposé par Arturo83 semble fonctionner pour ce que je veux faire,
Je comprend que Recherche soit une vieille formule Saboh, mais le problème est qu'avec Recherchev on ne peut pas additionner plusieurs données,
Pour le Index Equiv je ne suis pas suffisamment maîtrisant de cette formule mais c'est effectivement peut-être une meilleure solution.
Pour l'explication un peu plus détaillée :
RECHERCHE/RECHERCHEV(par défaut) fonctionnent ainsi :
Elles parcourent le vecteur donné en 2e argument et comparent 1 par 1 les valeurs du vecteur à celle recherchée.
- Si la valeur_courante ≤ valeur_recherchée : on poursuit la recherche. Et la valeur_courante est gardée en mémoire comme "meilleur match".
- Si valeur_courante > valeur_recherchée : la recherche s'arrete, meme si le vecteur n'a pas été entièrement parcouru. Le "meilleur match" est renvoyé (càd la valeur précédente).
C'est pourquoi =RECHERCHE("C"; {"A";"B";"D";"C"}) renvoie "B" => "D">"C" donc valeur précédente.
Maintenant dans RECHERCHEV, si on met le 4e argument sur TRUE, on force Excel à parcourir l'ensemble de la liste, et donc on n'a plus besoin de la trier. La recherche ne s'arrete que si une valeur identique est trouvée, sinon #NA. C'est un peu plus couteux en ressources forcément.
C'est pourquoi =RECHERCHEV("C"; {"A";"B";"D";"C"};1; FAUX) renvoie "C", car la valeur existe bien dans la liste.
Par contre je ne trouve pas le moyen de modifier la formule pour faire une recherche exacte qui fonctionnerait même avec les données de la colonne T dans le désordre.
Avec INDEX+EQUIV on a le même souci que RECHERCHEV, à savoir une impossibilité de prendre en compte plusieurs lignes de la colonne L (ou autre entre A et N).
Pas de soucis. Si la solution de tri vous va alors tant mieux.
Pour info index+equiv : Fonctions Excel : INDEX + EQUIV
Je n'ai pas encore rencontré un cas où RECHERCHE ne pouvait etre substitué. Si c'est pour la somme effectivement on doit passer par INDEX+EQUIV.
Il me semble que ceci fonctionne :
=SOMMEPROD(INDEX($U$2:$U$13;EQUIV(L4:L100;$T$2:$T$13;0)))EDIT : apparemment c'est un fonctionnement lié aux versions plus récentes d'Excel, mybad. Dans ce cas votre solution RECHERCHE+tableau trié est à privilégier. Faites juste bien attention lors de la saisie pour n'avoir que des valeurs bien tabulées.
C'est bien la formule que j'avais testée mais elle ne tient compte que de L4, pas de L4:L100, alors que sauvée en matriciel (renvoie une erreur si pas sauvée en matriciel).
La limite vient d'EQUIV et je ne sais pas à partir de quelle version d'Excel elle peut fonctionner sur une matrice.
J'ai trouvé
=SOMMEPROD(NB.SI(B4:B100;$T$2:$T$13)*$U$2:$U$13)Et là on peut mettre la colonne T dans n'importe quel ordre.