Combiner SOUS.TOTAL, SI et INDIRECT
Bonjour,
Un petit fichier valant mieux qu'un long discours j'ai joint un fichier exemple et j'ai mis de la cellule E2 de la feuille "Recap" la description exacte de la formule que je voudrais obtenir. Je pense qu'en regardant le fichier vous comprendrez tout de suite.
Je résume quand même:
Dans mon classeur ci-joint mes 3 premières feuilles comportent un tableau à deux colonnes.
Dans ma feuille "Recap", j'ai une liste déroulante dans laquelle je selectionne en A1 la feuille qui m'interesse et ensuite je voudrais calculer la somme des montants dont le coeficient est compris entre les bornes spécifiées. Par contre, je veux absolument utiliser la fonction SOUS.TOTAL avec le code 109 car j'applique des filtres à mes tableaux et je ne veux pas que les valeurs masquées soient prises en compte.
J'imagine que tout cela doit pouvoir se réaliser avec un mix de SOUS.TOTAL, SI et INDIRECT, voir SOMMEPROD et DECALER mais je patauge un peu...
Merci pour votre aide!
Bonjour,
J'ai une question (pour info) :
Comment sont définies tes bornes mini et maxi dans ta feuille Recap?
Cdlt.
Re,
Je t'explique ce que j'ai entrepris :
1 - consolidation des 3 feuilles en 1 (VBA)
2 - création TCD par Feuille
3 - groupement par intervalles de coefficients.
D’où ma question de savoir comment sont définis les bornes basse & haute des coefficients?
Le but étant d'éviter des formules tordues.
Cdlt.
Bonjour Jean-Eric,
J'ai créé ce classeur exemple spécialement pour illustrer ma question car mon classeur original est beaucoup plus complexe et beaucoup trop lourd pour être posté. Dans le classeur original les bornes sont calculées à partir d'une formule qui dépends d'autres cellules avec des formules, etc... Voici la formule du classeur original pour la borne basse, ça te donne une idée:
=MAX($E$14;$H$7-(($E$10-1)/2)-(($D$11/2)*(LIGNE(O2)-2));1)Je viens de voir que tu as posté un message en disant que tu envisageais de consilider les 3 première feuilles en une.
Or, c'est justement la démarche inverse que j'essaie en réalité de faire: à la base mon classeur était composé de plus d'une dizaine de feuilles similaires aux feuilles 1-3 mais où le tableau de la feuille "Recap" (ainsi que beaucoup d'autres tableaux) se repetaient sur chaque feuille, ce qui allourdissait beaucoup mon classeur et rendait les manipulation difficiles.
J'ai donc décidé de créer une feuille recap avec tous mes tableaux une seule fois et de faire une liste déroulante sur cette feuille pour choisir à partir de quelle feuille effectuer les calculs en mettant dans toutes les formules des INDIRECT(feuillechoisie)
je ne sais pas si je suis clair...
J'essaie d'adapter le fichier que tu m'as envoyé à mon classeur mais j'ai du mal... Je crois que ça ne va pas coller. Merci beaucoup quand même.
Dans un autre sujet du forum quelqu'un avait proposé pour un problème similaire une formule du style:
=SOMME(SI(SOUS.TOTAL(2;DECALER(A3;LIGNE(INDIRECT("1:"&LIGNES(A4:A56)));))*(D4:D56="Oui");C4:C56))Ca ne serais pas adaptable à mon cas?
Salut Tulipe et un très grand merci pour ton aide
Seul petit soucis dans la formule que tu m'as envoyé tu renvois à la plage A2:A239 qui correspond au nombre de lignes du tableau de la feuille1. Seulement mes tableaux ne font pas tous la même taille: dans mon classeur original certains font 300 lignes et d'autres 5000...Comment fait-on un INDIRECT vers une colonne entière?
Si j'essaie un truc comme:
INDIRECT(A$1&"!b:b"Cela ne fonctionne pas.
J'ai été un peu vite, en fait il y a un autre gros soucis: lorsque je filtre les montants le résultat ne change pas, les cellules masquées sont toujours comptabilisées...
re
1) pour faire simple ; tu remplace 329 par 5000 ou plus
2) il y a toujours moyen de "bouurrer" un critere de plus dans le sommeprod (lequel ??) parce que là on a bien la fourchette des coef mais quand tu filtres ;quel critere choisis tu ?
qque chose me dit que tu as une 3eme colonne secrete
cordialement
qque chose me dit que tu as une 3eme colonne secrete
Oui! sauf qu'elle n'est pas secrète!
Le critère que je filtre c'est le montant (je choisi par exemple seulement les montants de 100 et 50 ou bien les montants plus petits que 80, etc...). J'applique le filtre en cliquant sur la petit fleche à menu déroulant à coté de l'en-tête de mon tableau.
Il faudrait que j'arrive à combiner SOMMEPROD et SOUS-TOTAL de manière à ne prendre en compte que les lignes visibles...
re
et bien il suffit de mettre 2cel ;une pour le max et une autre pour le min ;tu saisis un montant max et un min ou meme les 2 identiques ; en rajoutant ces 2 criteres dans la formule sous forme de(.........)*(indirect(.................)>=cel du min)*(indirect(........)<=cel du max)*(........
tu n'as meme plus besoin de filtre (a la limite
cordialement
OK merci encore Tulipe c'est vraiment très cool de prendre du temps pour m'aider.
Je voudrais juste tenter un dernière chose avant de passer à cette solution, car les filtres m'arrangeaient beaucoup pour d'autres raisons. Je suit sur une piste et j'y suis presque il me manque juste un truc:
Comment faire pour afficher 1 dans une cellule si elle est visible et 0 si elle est masquée (ou VRAI/FAUX ou peu importe). Je viens de faire une recherche mais je ne trouve pas... Ca parait pourtant assez basique
Bonsoir
Essaye la matricielle :
=SOMME(SI(INDIRECT($A$1&"!B2:B5000")>=C2;SI(INDIRECT($A$1&"!B2:B5000")<=D2;SOUS.TOTAL(9;INDIRECT($A$1&"!A"&LIGNE(INDIRECT($A$1&"!A2:A5000")))))))Amicalement
Nad
re; bonsoir Nad
alors pour te repondre ......... mais c'est experimental et tout frais
=ABS(SOUS.TOTAL(3;B1:B14)-LIGNES(A1:A14))en admettant que ta plage soit b1:b14
1) tu selectionne une plage libre de meme dimension
2)tu saisis la formule et tu la valide en matricielle ;cetteplage va se tartiner de 0
ensuite qund tu filtres il y aura des autres valeurs >0 toujours la meme ;c'est pas des 1 mais tu peux utliser comme critere >1
c'est tout ce que je peux te proposer et c'est deja pas mal car tres peu de" bavards"....... sur le sujet
cordialement
=SI(ABS(SOUS.TOTAL(3;B1:B14)-LIGNES(A1:A14))>0;1;0)je viens de me reveiller
Arghhh...
Mon idée était de multiplier le montant par la formule de Tulipe qui donne 1 quand la cellule est visible et 0 quand c'est masqué...
=SI(ABS(SOUS.TOTAL(3;B1:B14)-LIGNES(A1:A14))>0;1;0)Mais ça ne veux pas pas marcher, la somme prend toujours en compte les cellules masquées.
Si quelqu'un a une autre idée...
Bonjour
JossBeaumont a écrit :Si quelqu'un a une autre idée...
As-tu essayé ma formule ?
Amicalement
Nad
bonjour
c'etait quasiment a prevoir
donc ;soit la formule de NAD les miennes ; neamoins puisque le filtre te sert pour un tas d'autre chose ;tu peux qund meme le garder
autre idée ;mais là c'est du rustique fort peu utilisé : regarde un peu ce que tu peux tirer des fonctions BD.SOMME , il y a qques fonctions de ce style que tu vas trouver dans: inserer une fonction>>>>base de données
puisque tu ne recules pas a passer des heures
cordialement
Merci infiniment Nad, très beau boulot!!!! Je suis trop content. Va falloir que je me mette à potasser le fonctionnement de ces formules matricielles rapidement.