Sous total sur moyenne
Bonjour,
Je refais un post suite à mon précédent qui était confus. (https://forum.excel-pratique.com/excel/sous-total-avec-moyenne-188452)
D’après mon fichier exemple ci-joint, que j’adapterai en conséquence à mon fichier original, je cherche à calculer en cellule B1 le sous total de la moyenne des pourcentages de la colonne B (B3:B6) qui correspondent uniquement aux cellules en colonne A (A3:A6) dont les valeurs sont « Trim » « Mens » et « Annu ». J’ai besoin que la formule s’actualise en fonction d’un filtre ou d’un masquage de ligne.
J’espère que mon exemple et mon explication sont suffisamment claires :)
Merci beaucoup.
Edit Modo : ajout du lien vers post précédent
bonjour,
Essayez ceci:
=SOUS.TOTAL(101;SI(OU($A$3:$A$6="Trim";$A$3:$A$6="Mens";$A$3:$A$6="Annu");$B$3:$B$6))Cdlt
Merci pour vos retours :)
@Arturo83, ça ne fonctionne pas, ça me calcul la moyenne avec la cellule "NA" :/
@tulipe_4, je ne peux pas intégrer de tableau sur mon fichier car il est partagé, et la fonction partage actuelle ne permet pas l'utilisation de tableaux :/
re
alors sans tablo automatique ,mais avec usage du filtre
ne fonctionne pas avec lignes masquées
Merci beaucoup @tulipe_4, mais je ne comprends pas comment remplacer dans ta formule "regime" et "pourcent", mais aussi les valeurs que tu as noté en H1:H3 (les régimes), il faut que de mon côté aussi j'aille les noter ailleurs sur mon fichier exemple?
re
pour voir comment nommer des champs
1) tu fais les touches Ctrl etF3 parfois (c'est Ctrl ,Fn,F3) en simultané ,ça va ouvrir l'editeur de noms , c'est là que dans mon modèle ,tu vas voir les 2 noms
2) tu cliques sur un nom , tu vois que ça fait reférence à une formule (afin de rendre le champ ainsi nommé : autoextensible )
nota) on est pas obligé d'utiliser une formule , tu selectionnes la plage du champ à nommer ,mais il demeurera fixe (pas de rajout)
dans ton vrai classeur ,étant donné que tu demarres de rien ,une fois l'éditeur ouvert ,tu cliques sur nouveau pour creer
3) tu peux ainsi nommer un tas de zone , c'est valable pour tout le classeur ,,, comme par exemple la plage H1:H3 (les critères) que tu peux très bien mettre dans une autre feuille , puis nommer ,tu utilises ce nom dans n'importe quelle formule
nota ; utilise des noms simples (facile à retenir et pas de faute) ,,, essaie ,comme moi de privilégier une formule (Decaler......) pour avoir du dynanisme surtout avec l'utilisation de Sommeprod (pas copain avec les tatines de cel vides si on prévoit "large"
Merci pour cette réponse détaillée.
Nommer des plages je sais faire, dans mon fichier original j'ai nommé des plages qui me servent à afficher des liste déroulantes.
Sur ta formule " =SOMMEPROD((SOUS.TOTAL(9;DECALER(B2;LIGNE(pourcent)-2;)))*(NB.SI(H1:H3;regime)>0))", si je comprends bien (et ce n'est pas garanti
Ensuite, sur la partie de formule NB.SI, "H1:H3" sont les cellules ou tu as noté les régimes qui sont dans la colonne A, de mon côté sur mon fichier original ils sont aussi sur une plage à part nommée "Régime_TVA", je dois donc remplacer sur ta formule "H1:H3" par "Régime_TVA"?
Aussi, "regime", fait référence aux cellules A3:A6, je remplace par "A3:A6"?
Sur ta formule le résultat est de 127%, alors que si je sélectionne les 3 cellules qui correspondent à "Trim" "Annu" et "Mens", la moyenne est de 42%, si je divise par 3 ta formule c'est bon, faut-il juste rajouter une division par 3 à la fin de la formule ou faut-il changer autre chose?
Je te remercie encore pour ton aide
Je pense que j'ai fini par trouver
=SOMMEPROD((SOUS.TOTAL(9;DECALER(B2;LIGNE(B3:B15)-2;)))*(NB.SI(Régime_TVA;A3:A15)>0))/(((NB.SI(A3:A10;"Trim")+NB.SI(A3:A10;"Mens")+NB.SI(A3:A10;"Annu"))))
ça semble correct?
bonjour
a vérifier en utilisant le filtre , car tu divises par une somme totale des NB.si ,,, ce qui n'est plus le cas après filtrage , je verrai d'avantage
SOMMEPROD((SOUS.TOTAL(9;DECALER(B2;LIGNE(B3:B15)-2;)))*(NB.SI(Régime_TVA;A3:A15)>0))/SOMMEPROD((SOUS.TOTAL(3
;DECALER(B2;LIGNE(B3:B15)-2;)))*(NB.SI(Régime_TVA;A3:A15)>0))sous total (9 fait la somme de ce qui apparait suite au filtre et par bonheur Sous total(3 donne le nb de valeur après filtre
Bonsoir @tulip_4
Je reviens vers toi car j'ai essayé d'adapter ta formule à ma formule mais je n'y arrive pas du tout et effectivement le problème que tu as soulevé existe bien :( la division ne prend pas en compte le nombre total de cellules qui contiennent mes valeurs après filtre.
Voici ma formule avec mes infos réelles:
=SOMMEPROD((SOUS.TOTAL(109;DECALER(Y3;LIGNE(Y3:Y550)-MIN(LIGNE(Y3:Y550));0)))*(NB.SI(E3:E550;"*TVA_trim.*")+NB.SI(E3:E550;"*TVA_mens.*")+NB.SI(E3:E550;"*TVA_annu.*")+NB.SI(E3:E550;"*TVA_ex.*")>0))/(((NB.SI(E3:E550;"*TVA_trim.*")+NB.SI(E3:E550;"*TVA_mens.*")+NB.SI(E3:E550;"*TVA_annu.*")+NB.SI(E3:E550;"*TVA_ex.*"))))
Peux-tu m'aider à la modifier?
Merci beaucoup par avance :)