Discussion :

[jeje644]

Bonjour tout le monde,

Je rencontre un problème sous Excel.

J'ai deux colonnes : A et B.
Dans ma colonne A j'ai ce qu'on appelle mon type qui peut prendre pour valeur : 1,2,3,4,5,6 ou 7
Dans ma colonne B, j'ai des valeurs numériques. Pour chaque type, je peux avoir plusieurs valeurs numériques (voir pièce jointe).
Je souhaiterai sommer les 5 plus grandes valeurs pour chaque type. Pour type 1 : somme des 5 plus grosses valeurs : XXXX
Pour type 2 : somme des 5 plus grosses valeurs : YYYY
...
Savez vous si cela est possible à réaliser ?
J'ai essayer les filtres, mais le problème c'est que le numéro de ligne des 5 premiers change et donc ma formule tombe à l'eau. J'ai essayé le tableau croisé dynamique, mais je n'ai que des connaissances limitées dans ce domaine. (voir ma pièce jointe)

Merci d'avance pour votre aide.

PJ: test.xlsx

[Menhir]

Désolé je n'ouvre pas les pièces jointes (mauvaises expériences).

Tu le fais en 2 temps.

En supposant que tes données aillent de la ligne 2 à la ligne 100 , que les types sont en colonne A et les valeurs en colonne B, tu tapes "1" en C1.

Dans la cellule C2, tu mets la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(A2=C1;B2;"")

Et tu la recopie de C3 à C100.

Tu place en C101 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=GRANDE.VALEUR(C2:C100,1)+GRANDE.VALEUR(C2:C100,2)+GRANDE.VALEUR(C2:C100,3)+GRANDE.VALEUR(C2:C100,4)+GRANDE.VALEUR(C2:C100,5)

Puis tu recopies la colonne C à droite autant de fois que tu as de type, en mettant en ligne 1 de chaque colonne le type correspondant.

Il est peut-être possible de faire ça en matriciel pour éviter les intermédiaires mais je ne vois pas trop comment.

[GerardCalc]

Bonjour,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(GRANDE.VALEUR($B$2:$B$56*($A$2:$A$56=A59);LIGNE(A$1:A$5)))

$B$2:$B$56*($A$2:$A$56=A59) renvoie une matrice composée des nombres de la colonne B pour lesquels le critère en A59 est respecté pour la colonne A ainsi que de 0 lorsque la condition n'est pas remplie.
SOMMEPROD(GRANDE.VALEUR(matrice;LIGNE($1:$5)) somme les 5 plus grandes valeurs de la matrice.

Comme je l'ai dit plus haut, la matrice contient des 0 lorsque le critère n'est pas respecté.
Donc cette formule n'est valable que si les valeurs de la colonne B sont positives. (Ou au moins la 5ème valeur de chaque critère)

A+

[jeje644]

Désolé je n'ouvre pas les pièces jointes (mauvaises expériences).

Tu le fais en 2 temps.

En supposant que tes données aillent de la ligne 2 à la ligne 100 , que les types sont en colonne A et les valeurs en colonne B, tu tapes "1" en C1.

Dans la cellule C2, tu mets la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(A2=C1;B2;"")

Et tu la recopie de C3 à C100.

Tu place en C101 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=GRANDE.VALEUR(C2:C100,1)+GRANDE.VALEUR(C2:C100,2)+GRANDE.VALEUR(C2:C100,3)+GRANDE.VALEUR(C2:C100,4)+GRANDE.VALEUR(C2:C100,5)

Puis tu recopies la colonne C à droite autant de fois que tu as de type, en mettant en ligne 1 de chaque colonne le type correspondant.

Il est peut-être possible de faire ça en matriciel pour éviter les intermédiaires mais je ne vois pas trop comment.

Bonjour,

Merci pour votre aide !!!!!! La première partie fonctionne parfaitement (=SI(A2=C1;B2;""). En revanche la seconde ne fonctionne pas du tout et je n'arrive pas àa savoir pourquoi malgré la lecture d'aide d'Excel. J'ai bien ré-écrit la formule Grande.Valeur en mettant correctement les paramètres. Malgré cela, un message d'erreur apparait :

"La formule que vous avez tappé contient une erreur.
..."

La formule test que j'ai tappé est : "=grande.valeur(C2:C100,1)"
Je ne vois pas où est l'erreur. Voyez vous une erreur dans la syntaxe ou autre ?

Merci d'avance.

[rjamin]

Bonjour,
Essayez en remplaçant la , par un ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=GRANDE.VALEUR(Matrice;k)

est la syntaxe exacte.

[jeje644]

Merci beaucoup pour votre aide, c'était bien ça : ; au lieu de ,

Ca fonctionne parfaitement c'est cool

Juste une question par contre: Pourquoi avoir mis dans la formule SOMMEPROD et non SOMME tout court ?

[Menhir]

Merci beaucoup pour votre aide, c'était bien ça : ; au lieu de ,

Oups

Juste une question par contre: Pourquoi avoir mis dans la formule SOMMEPROD et non SOMME tout court ?

Parce que SOMMEPROD permet de faire dans certains cas un équivalent des fonctions matricielles et donc de faire dans une seule cellule ce qui, sinon, demande des calculs dans des cellules intermédiaires.
Typiquement, dans la solution que je t'ai proposée, j'utilise un SOMME classique, ce qui oblige à faire des calculs intermédiaires alors que le SOMMEPROD de GerardCalc fait la même chose en une seule formule.