XL 2013 [Résolu] Combinaison RECHERCHEV et SOMMEPROD

miloo

XLDnaute Junior
Bonjour à tous,

Je cherche à simplifier un tableau comparatif de solutions sur la base de plusieurs critères.
Je souhaite passer par un système de notation de ce type: ++ (le meilleur), +, 0 (neutre), -, -- (moins bon), et pas via une note entre 1 et 5 pour éviter les mauvaises interprétations (quel est le meilleur: 1 ou 5?).
Je veux aussi pouvoir attribuer un poids d'importance à chaque critère.

Merci de jeter un oeil au fichier joint: j'arrive au résultat voulu dans la case jaune, mais je voudrais me passer de la colonne H et des valeurs en orange qui ne me servent que de calcul intermédiaire. J'aimerais avoir un résultat directement dans la cellule verte en combinant le RECHERCHEV et le SOMMEPROD, mais je n'y arrive pas.
Merci d'avance!
 

Pièces jointes

  • comparatif.xlsx
    13.1 KB · Affichages: 77

Florian53

XLDnaute Impliqué
Bonjour miloo,

Essaye avec cette formule à rallonge :

Code:
=(SOMMEPROD(((EvalGrade=G2)*$E$2/$E$8)*(E10:E14))*E2+SOMMEPROD(((EvalGrade=G3)*$E$3/$E$8)*(E10:E14))*E3+SOMMEPROD(((EvalGrade=G4)*$E$4/$E$8)*(E10:E14))*E4+SOMMEPROD(((EvalGrade=G5)*$E$5/$E$8)*(E10:E14))*E5+SOMMEPROD(((EvalGrade=G6)*$E$6/$E$8)*(E10:E14)*E6))/E8

Cependant je trouve que ta formule est plus adaptée pour une meilleur compréhension de la formule, pourquoi ne masque tu pas tout simplement la colonne avec les calculs intermédiaires ?
 

Pièces jointes

  • comparatif.xlsx
    13.6 KB · Affichages: 31

miloo

XLDnaute Junior
Salut Patrice33740,
Je ne cherche pas à colorer les cellules, les couleurs sont simplement là pour illustrer mon exemple et aider à la compréhension de mon problème.
J'ai actuellement 2 formules dans des cellules différentes: 1 série avec RECHERCHEV et une avec SOMMEPROD. Je cherche à les combiner les 2 formules pour éviter les cellules de calcul intermédiaires (colonne H).
 

miloo

XLDnaute Junior
Salut Florian53,
Merci pour ta réponse, effectivement j'avais peur d'une formule à rallonge comme la tienne mais je ne peux malheureusement peut-être pas y couper.
A moins qu'une âme charitable passant par ici trouve une autre solution?!
D'autant plus que c'est un fichier simplifié et j'ai normalement bien plus de critères que les 5 du fichier exemple.
Pour l'explication, je veux éviter de masquer les colonnes car des personnes tierces seront susceptibles d'utiliser le fichier et d'ajouter des colonnes nécessitant les mêmes calculs.

Merci.
 

miloo

XLDnaute Junior
Merci Patrice33740,
Effectivement cela résout le problème mais mes collègues ne sont pas chaud pour changer les système de notation... habitudes quand tu nous tient...
Y aurait-il une autre solution en gardant le système ++,+,o,-,--? avec d'autres formules?
Je vais vérifier ma formule finale, j'ai peut être fait une erreur pour obtenir le bon pourcentage, mais le plus important pour moi pour l'instant est de savoir si le principe d'imbriquer la RECHERCHEV et le SOMMEPROD (ou remplacer par une autre formule que je ne connais pas) est possible.
Merci encore de faire avancer le schmilblick!
 

miloo

XLDnaute Junior
Merci Patrice, c'est parfait.
Bon, par contre, j'ai beau regarder les explications sur les fonctions TROUVE et MOD (surtout TROUVE je pense), mais j'ai du mal à comprendre pour adapter ou modifier ma formule, qui comme tu l'as dit sera peut-être à corriger. Si tu as quelques explications, elles sont les bienvenues.
 

Patrice33740

XLDnaute Impliqué
Re,

Dans MOD(TROUVE(","&G2:G6&":";",--:,-:,,o:,,+:,,++:,:")-1;20)*25/4
L'astuce réside dans la chaine ",--:,-:,,o:,,+:,,++:,:" dans laquelle on effectue une recherche des caractères
et dans la composition de la chaine à y rechercher : "," & G2:G6 & ":" c'est à dire que :
- pour -- on cherche ,--: qui se trouve en 1 puis -1=0 puis MOD(0; 20) = 0
- pour - on cherche ,-: qui se trouve en 5 puis -1=4 puis MOD(4; 20) = 4
- pour o on cherche ,o: qui se trouve en 9 puis -1=8 puis MOD(8; 20) = 8
- pour + on cherche ,+: qui se trouve en 13 puis -1=12 puis MOD(12; 20) = 12
- pour ++ on cherche ,++: qui se trouve en 17 puis -1=16 puis MOD(16; 20) = 16
- pour une cellule vide on cherche ,: qui se trouve en 21 puis -1=20 Le MOD sert à transformer ce 20 en zéro, c'est à dire, MOD(20; 20) = 0.

Ensuite on multiple par 25/4 pour obtenir respectivement 0, 25, 50, 75, 100 ou 0
 

miloo

XLDnaute Junior
Patrice, merci infiniment! L'explication est parfaite, par contre je me rends compte que j'ai un vrai palier à sauter pour pouvoir tenir ce genre de raisonnement d'un point de vue logique d'abord, puis de pouvoir l'appliquer via une formule Excel!
Bonne journée!
 

miloo

XLDnaute Junior
Patrice, j'ai maintenant revu ma formule que tu trouvais surprenante. En effet, je pense que le poids était appliqué 2 fois... Je vais donc corriger et continuer à vérifier les résultats au cas où je me sois encore trompé. La formule devient alors:
Code:
=SOMMEPROD(MOD(TROUVE(","&G2:G6&":";",--:,-:,,o:,,+:,,++:,:")-1;20)*25/4;E2:E6)/E8
 

Discussions similaires

Réponses
6
Affichages
217