[XL-2007] Formule matricielle et EQUIV [Résolu]

[Triton972]

Bonjour à Tous,


J'attribue une note (A,B,C ou D) et une pondération (A,B ou C) à chaque élément d'une liste.

Parallèlement j'ai 2 tableaux de correspondance qui me permettent de valoriser les "notes" et les "pondérations" (je spécifie une valeur pour chaque lettre).

Mon problème : Je souhaite calculer la somme de toutes les notes pondérées de chaque élément dans une case unique sans passer par des calculs intermédiaires.

Je souhaite conserver la notation avec les lettres afin de pouvoir modifier simplement le système de cotation (les notes) sans remettre en cause le système d'évaluation (les lettres).

exemple :

Element Note Pondération
Elem1 A B
Elem2 C A
Elem3 B C


Correspondance :
Note Pondération
A 6 A 5
B 3 B 2
C 2 C 1
D 1

Je cherche donc à transformer la matrice (A;C;B ...) en (6;2;3;...) et la multiplier par la matrice (2;5;1; ...) issue de la matrice (B;A;C;...) et d'ajouter chacun de ces produits pour "fabriquer" mon résultat : La somme de toutes les notes pondérées de tous mes éléments.

Je pensais pouvoir utiliser EQUIV dans une formule matricielle mais je coince ... et j'ai besoin d'un peu d'aide (peut-etre que EQUIV n'est pas la bonne piste ...).

Merci de vos aides

[Michel_M]

Bonjour,

en changeant la disposition du tableau de correspondance, par exemple:notes horizontales et pondérations verticales, le pb pourrait se résoudre relativement facilement.

la disposition actuelle est elle imposée ?

Dans l'attente

[Triton972]

Bonjour Michel,

Non il n'y a pas de contrainte, je peux disposer les données comme je veux ...
Je perçois ta solution, mais je te laisse le bonheur de la décrire.
Cordialement,

[Michel_M]

En fait avec ta disposition 2 recherchev suffisent (mes termes matrice, equiv m'avaient embrouillés, excuse facile )
voir maquette jointe

[GerardCalc]

Bonsoir,

Sur la base du fichier fourni par Michel_M, tu peux obtenir le total avec :

Code :

=SOMMEPROD(RECHERCHE(B2:B5;A11:B14)*RECHERCHE(C2:C5;C11:D13))

Si les plages A11:A14 et C11:C13 ne sont pas triée en ordre croissant, remplaces RECHERCHE par RECHERCHEV avec le dernier argument à FAUX ou 0 :

Code :

=SOMMEPROD(RECHERCHEV(B2:B5;A11:B14;2;0)*RECHERCHEV(C2:C5;C11:D13;2;0))

A+

[Triton972]

Bonsoir Michel et GérardCalc,

Michel, ta solution utilise une colonne supplémentaire mais je voudrais justement l'éviter et faire la somme de toutes les lignes, directement, dans une seule cellule.

GérardCalc, ta formule ne donne pas le résultat escompté (ai-je loupé quelque chose ?), le 1° paramètre de RECHERCHE est une zone mais lors de l'évaluation de la formule, seule la 1° valeur de la zone est prise en compte (ce qui me parait normal !)

Autre élément à prendre en compte (mais que je n'avais pas mentionné dans mon post initial) est que toutes les valeurs ne sont pas obligatoirement renseignées ! C'est à dire que, dans l'exemple donné, "zaza3" peut ne pas être coté. (ni note ni pondération). Ceci engendre alors une erreur qu'il faut traiter.
Je suis un peu exigeant mais je voudrais gérer cela dans une seule cellule.

[GerardCalc]

Normalement le caractère matriciel est transmis à RECHERCHE par SOMMEPROD. C'est la cas avec Calc (LibreOffice ou OpenOffice) mais il semblerait que cela ne soit pas le cas avec Excel.
En remplaçant SOMMEPROD par SOMME et en validant par Ctrl+Maj+Entrée cela devrait fonctionner mais uniquement si toutes les valeurs sont renseignées par des données présentes dans le second tableau.

Mais puisque ce n'est pas le cas, voici une autre formule qui devrait (conditionnel car je n'ai pas Excel) gérer les vides et valeurs non prévues :

Code :

=SOMME(SI(ESTNA(RECHERCHEV(B2:B5;$A$11:$B$14;2;0));0;RECHERCHEV(B2:B5;$A$11:$B$14;2;0))*SI(ESTNA(RECHERCHEV(C2:C5;$C$11:$D$13;2;0));0;RECHERCHEV(C2:C5;$C$11:$D$13;2;0)))

Edit : Il s'agit d'une formule matricielle, donc à valider par Ctrl+Maj+Entrée.

[Triton972]

Bonsoir GérardCalc,

Ta formule prend bien en compte les cellules vides mais toujours que la première cellule de la zone !
Bon, on va finir par trouver.
@+
Cordialement,

[Triton972]

ReBonsoir,

Je crois que j'ai trouvé ...
En reprenant le classeur de Michel_M. J'ai rajouté dans la cellule A1 :

Code :

=NBVAL(A2:A10)

Qui me permet de connaitre le nombre de lignes de données. Je l'ai limité à A10 car à partir de A11, Michel_M a collé les tables de correspondance . Mais çà change rien au principe.
J'utilise la fonction DECALER un peu partout afin que l'étendue de ma zone se détermine automatiquement en fonction du nombre de lignes.

Je n'ai pas (encore) trouvé mieux mais çà à l'air de marcher même avec des cellules non remplies :

Code :

=SOMME(((((DECALER(B$1;1;0;$A$1;1))="A")*$B$11)+(((DECALER(B$1;1;0;$A$1;1))="B")*$B$12)+(((DECALER(B$1;1;0;$A$1;1))="C")*$B$13)+(((DECALER(B$1;1;0;$A$1;1))="D")*$B$14))*((((DECALER(C$1;1;0;$A$1;1))="A")*$D$11)+(((DECALER(C$1;1;0;$A$1;1))="B")*$D$12)+(((DECALER(C$1;1;0;$A$1;1))="C")*$D$13)))

C'est une formule matricielle donc à valider avec CTRL + Maj + Entrée
Qu'en pensez-vous ? Peut-on simplifier en conservant ce principe ?
Cordialement,