Tableau avec résultats selon filtre
Bonjour à tous,
J'aimerais avoir vos avis et conseils pour améliorer mon tableau excel (cf. pièce jointe), et notamment en vue d'observer les résultats obtenus selon certains filtres.
Ce tableau est très simple : il doit me servir à reporter les notes d'évaluations mises pour des actions.
La première partie du tableau donne donc les détails de chaque action, et la seconde partie permet le report des notes (une colonne par critère de notation).
Le tableau en l'état permet d'avoir le résultat total pour chaque critère. Mais j'aimerais pouvoir afficher ces mêmes résultats selon les filtres que je souhaite. Je m'explique :
Par exemple, si je souhaite avoir seulement le résultat des évaluations (total pour chaque critère) pour les actions en France, il sera simple de filtrer la colonne pays, mais la partie résultats m'affichera toujours les totaux de l'ensemble des actions, et non pas les totaux par rapport à la donnée filtrée (le pays ici, mais idem si je voulais filtrer par durée ou par coût).
Ma question est donc simple : comment obtenir ces résultats (total pour chaque critère) selon les données filtrées dans la partie détaillant l'action ? Que ce soit en passant par une macro, des formules dans les cases résultats, un tableau croisé dynamique ou même en modifiant complètement mon tableau, je suis preneur de tous vos conseils !
Merci d'avance.
Bonjour,
aucune données, impossible de tester quoique ce soit...
Si tu pouvais également t'expliquer sur H10:AX13 ?!?
A tout hasard, jette un oeil sur la fonction SOUS.TOTAL(), ou Agregat()
eric
Pardon, j'ai effectivement remis à jour le document pour qu'il y ait quelques données (je le joins à cette réponse). Comme c'est un outil en construction ce sont des données fictives.
Pour les colonnes H à AX, il s'agira de mettre une note dans chaque case (1, 2, 3 ou 4) pour chaque critères. Il y a 4 évaluations différentes à reporter, d'où les 4 séries de critères.
Le total sous chaque critère permet de voir pour chacun combien il y a de note 1, de note 2, de note 3 et de note 4.
L'objectif est que, si je filtre toutes les actions en France par exemple, j'ai le total par critère selon ce filtre qui s'affiche.
Je vais regarder ces fonctions merci.
Les fonctions SOUS.TOTAL et AGREGAT vont effectivement bien dans le sens de ma demande.
Cependant, la formule pour chaque total est un NB.SI pour voir dans chaque colonne le total de 1 puis le total de 2 puis le total de 3 et enfin le total de 4 pour chaque critère. Les fonctions/options proposées par SOUS.TOTAL et AGREGAT ne semblent pas permettre d'insérer ce calcul là. A moins que j'ai mal compris le détail de ces fonctions... (je suis totalement amateur sur excel, je l'utilise ponctuellement dans le cadre de mon travail seulement)
Effectivement, pas de nb.si() dans ces fonctions, il faut donc faire autrement.
Je t'ai inséré une colonne avec =SOUS.TOTAL(3;A12) pour savoir si une ligne est filtrée (=1) ou non (=0)
Je t'ai reporté le résultat en G27:G33 pour t'aider à voir le résultat.
Ensuite en I3 :
=SOMMEPROD(--(($G$12:$G$18)*(I$12:I$18)=LIGNE()-2))Un peu d'explications comme tu dis ne pas maitriser totalement excel
LIGNE()-2) donne 1 à 4 pour les lignes 3:6. Ca évite la saisie en dur de 1 à 4, et permet donc la recopie de la même formule sur toutes les lignes.
($G$12:$G$18)*(I$12:I$18) te fait un produit de matrice : (évaluations)*(ligne filtrée ou non) donc *0 ou *1
pour t'aider à comprendre, tu peux sélectionner une partie de la formule et faire F9 dessus pour qu'excel te l'évalue.
Quitter par Echap pour ne pas valider
Par exemple, avec France filtrée, F9 sur (I$12:I$18) te donne {1;2;2;3;2;1;4}, tu vois que tu as la matrice des évaluations
F9 sur ($G$12:$G$18)*(I$12:I$18) te donne {1;0;0;0;2;0;4}, il ne te reste que celles de la france
($G$12:$G$18)*(I$12:I$18)=LIGNE()-2 retourne une matrice de VRAI/FAUX : {VRAI;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX} qui te montre soit les 1, soit les 2, etc selon la ligne où tu es. Ici les 1 de france donc.
à convertir en numérique par un 1er moins {-1;0;0;0;0;0;0}, et on en rajoute un autre pour rétablir le signe {1;0;0;0;0;0;0}. D'où le --
Pour finir Sommeprod() te fait la somme des éléments de ta matrice.
eric
Edit : je viens de voir la réponse de tulipe (salut
ce qui m'a permis de me rendre compte que tu avais mis les valeurs 4 à 1 en F2:F6
Je m'étais basé sur la couleur vert=1 (et non 4...). Si tu maintiens cet ordre décroissant il faudra changer =LIGNE()-2 en conséquence
Arff, ce sont les sommes, je n'avais lu que 2 lignes avec 4, 3...
Merci beaucoup à tous les deux, cela semble opérationnel !
Bien vu le format de cellule pour éluder les 0 tulipe, je ne connaissais pas, ça fait clairement moins surchargé !
Pour ta solution Eric, en trifouillant un peu sur les forums j'ai trouvé une variante (qui semble plus compréhensible à mon cerveau d'amateur haha).
Après avoir créé la colonne avec SOUS.TOTAL pour vérifier si la colonne est masquée ou non, j'insère les formules suivantes dans les totaux pour chaque critère :
=NB.SI.ENS(colonnefiltre;1;colonneàreporter;"1")
=NB.SI.ENS(colonnefiltre;1;colonneàreporter;"2")
=NB.SI.ENS(colonnefiltre;1;colonneàreporter;"3")
=NB.SI.ENS(colonnefiltre;1;colonneàreporter;"4")
Cela donne concrètement par exemple pour H3 :
=NB.SI.ENS($AZ$11:$AZ$19;1;H$11:H$19;"1")
Je pourrais d'ailleurs utiliser ton ligne()-2 pour davantage simplifier la formule.
Est-ce que cela vous semble tout aussi fonctionnel que vos propositions ou il y a une faille ?
Oui, tout à fait.
D'ailleurs, Nb.Si.Ens() n'est autre qu'un Sommeprod() déguisé créé pour les amateurs
Ce n'est pas péjoratif, c'est vrai que la lecture est plus claire
Ah bah voilà, on retombe bien sur nos pattes ! Merci beaucoup en tout cas !
Topic clos
Juste pour ça fasse plus propre, "1" est une chaine comme "bonjour", 1 est un nombre.
Ce n'est pas parce qu'excel convertit souvent une chaine représentant un nombre en numérique qu'il faut compter sur lui.
1) tu lui donnes du boulot en plus pour rien
2) il y a des cas où "1" est différent de 1 pour lui...
Donc un nombre c'est sans " ".
eric