Formule incluant Sous.Total ou Agregat
Bonjour,
Je précise que je recherche une formule devant fonctionner avec Excel 2016.
Dans un onglet masqué, j'ai un tableau de données. A l'aide de segment sur un onglet "Recap", j'effectue un ou des filtres, et j'obtiens les sous-totaux.
Cependant j'aurais également besoin de "peaufiner" un peu, et obtenir d'autres résultats sans filtres supplémentaires.
J'ai mis un tableau très simplifié, avec une explication que j'espère exhaustive.
Merci par avance
Edit, je tiens à préciser que la valeur filtrée via le segment ne peut pas être utilisée en dur dans la formule (un Sommeprod tout simple ne me conviendrait pas, par exemple)
bonjour Cousinhub,
un essai avec sommeproduit et une colonne qui détermine si la ligne est cachée ou pas
Hello Bart,
Merci beaucoup pour ta participation.
Effectivement, je n'avais même pas pensé à la colonne supplémentaire pour déterminer si la ligne est masquée...
Pourtant j'utilise souvent cette option, mais avec la formule Agregat qui indique 1 également si la ligne est visible.
=AGREGAT(3;3;[@date])A voir dans mes tests (pas avant vendredi), le tableau en référence étant issu de Power Query, et à priori les formules suivent bien selon la mise à jour (plus ou moins de lignes)
Merci encore, très bonne idée
Bonjour Cousinhub,
Je pensais à la formule Somme.si.ens qui fonctionne sous excel 2016
Comme ceci --> =SOMME.SI.ENS(T_Data[nb];T_Data[date];"lundi";T_Data[couleur];"blanc")
Peut-être suis-je dans l'erreur et que je n'ai pas capté une subtilité ?
Crdlt
Salut Dan,
Effectivement, ceci fonctionne également, mais la subtilité était de ne pas utiliser la valeur du filtre (ici, "blanc") dans la formule.
Merci quand même
PS, comme il y a plusieurs segments actifs pour ce tableau, il est donc non envisageable d'utiliser ces variables.
=AGREGAT(3;3;[@date])@Cousinhub, je ne sais pas ajouter une construction avec "IF" dans le 3ème argument, cela doit être une plage et ne pas une matrice, je suppose.
Hello,
Je ne comprends pas trop ta question...
Quel 3ème argument?
La formule Agregat remplace juste ta formule en colonne "D"
=--(SOUS.TOTAL(103;T_Data[@[date]:[nb]])>0)Et je garde le principe de ta formule en G1 :
=SOMMEPROD((T_Data[date]="lundi")*(T_Data[nb]=1)*T_Data[visible])Qu'on pourrait également remplacer par le Nb.Si.Ens
=NB.SI.ENS(T_Data[date];"lundi";T_Data[nb];1;T_Data[visible];1)ou le Somme.Si.Ens de @Dan
=SOMME.SI.ENS(T_Data[nb];T_Data[date];"lundi";T_Data[visible];1)Je pense que l'ajout de cette colonne supplémentaire ("Visible") devrait faire l'affaire (à tester grandeur nature demain)
Merci encore pour ton implication, et bonne journée
Hello,
Un TCD est aussi possible, avec un segment sur la couleur. Mais cela implique à cliquer sur la couleur voulue...
Hello JB_,
Effectivement, mais la structure de mon fichier n'est pas idéale pour l'utilisation d'un TCD dans ce cas précis. (j'en ai déjà plusieurs dans ce fichier, qui me servent à autre chose), et une grosse requête PQ
Auparavant j'utilisais un modèle de données, mais le temps de mise à jour est un peu trop conséquent pour l'utilisation finale (Dashboard, et visualisation immédiate dès le choix effectué via plusieurs segments)
Merci pour ta participation
Re,
Une autre à tester --> =SOMMEPROD((T_Data[date]="lundi")*(T_Data[nb]=1)*SOUS.TOTAL(103;DECALER(A2;LIGNE(T_Data[date])-LIGNE(A2);0)))
C'est un peu dans la même direction que ce qu'a proposé BsALV mais sans ajout de colonne
Fatiguant en tout cas....
Re hi
ça semble effectivement faire le taf. (même si je ne comprends pas tout)
T_Data[date]="lundi"Vérifie que Lundi est dans la colonne date
T_Data[nb]=1Vérifie que l'on a le chiffre 1 dans la colonne nb
SOUS.TOTAL(103;DECALER(A2;LIGNE(T_Data[date])-LIGNE(A2);0))Renvoie 1 si la ligne visible et 0 si la ligne masquée
Rem : DECALER(A2;LIGNE(T_Data[date])-LIGNE(A2);0) renvoie une matrice avec le numéro de chaque ligne correspondant au jour dans la colonne Date et en commençant par 0 (on obtient 0 à 24 dans le cas présent)
SOMMEPRODRenvoie un total du nombre de valeur dont les trois conditions sont remplies.
Re-,
Merci pour ce complément d'informations (à moi de digérer, maintenant)
Bonne fin d'apm
Bonjour à tous, j'essaye mais après les maîtres de la formule :(
Peut être pas compris la demande..
=SOMMEPROD((T_Data[date]="lundi")*(T_Data[couleur]="blanc")*(T_Data[nb]=1))
P.
Re,
@Patrick1957 :
oui cela fonctionne mais vous n'avez pas lu tout le fil
Mais comme précisé précédemment par Cousinhub (https://forum.excel-pratique.com/s/goto/1269152 ) on ne peut pas utiliser le critère "Blanc" dans la formule.
Crdlt