Somme.si des lignes visibles Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
j
juicelink
Jeune membre
Jeune membre
Messages : 28
Inscrit le : 11 septembre 2014
Version d'Excel : 2010

Message par juicelink » 11 septembre 2014, 16:01

Bonjour,

je suis nouveau sur votre forum, je me présente donc ; Je m'appelle Julien (d'ou le pseudo "juicelink", vu que jus en anglais, ça fait juice et que lien ça fait link... rigolez pas...) et je suis comptable, ce qui m’amène à beaucoup travailler sur Excel

Avant de poser ma question, je précise que j'ai pas mal cherché la réponse avant de trouver, alors si vous me dites que la réponse est évidente, j'irai m'inscrire sur les forum "Apprendre à chercher sur google" lol

Voila, j'aimerai savoir si quelqu'un a une solution pour utiliser la fonction somme.si, mais uniquement avec les cellules visibles.
parce que pour faire une somme de colonne des cellules visibles, aucun souci, la fonction sous.total le fait très bien.
En revanche, je n'ai pas de solution lorsque je veux faire la somme des montants d'une colonne, en incluant un critère apparaissant sur la colonne d'à coté, mais en ne tenant pas compte des lignes masquées.

je joins un fichier, peut être que ce sera plus clair;
dans mon fichier, si aucune des lignes 2 à 7 ne sont masquées, les cellules A8 et B12 sont égales, donc je suis ok
en revanche, si je masque une ligne, le total A8 est bon (grâce à la fonction sous.total), en revanche, le total B12 n'est plus bon, puisque la fonction somme.si n'ignore pas les lignes masquées.

j'avais pensé rajouter une colonne qui renverrait le fait que la cellule de la colonne A soit masquée ou non, du style en C2 : =si(A2;estvisible=vrai;A2;0)
de cette manière, j'aurai pu demander à la fonction somme.si d'ajouter les valeurs de cette colonne.
Mais le hic, c'est que je ne trouve pas de moyen de tester le "estvisible"

avez vous une idée?

merci énormément d'avance pour votre aide
Classeur1.xls
(23 Kio) Téléchargé 272 fois
v
vba-new
Passionné d'Excel
Passionné d'Excel
Messages : 3'003
Appréciations reçues : 2
Inscrit le : 13 mai 2009
Version d'Excel : 2010 FR - 2013 FR

Message par vba-new » 11 septembre 2014, 17:09

Bonjour lien de jus,

A ce stade, il faut bien utiliser la fonction SOUS.TOTAL mais combinée à la fonction SOMMEPROD.
Une solution :
=SOMMEPROD(SOUS.TOTAL(109;DECALER($A$2;LIGNE($A$2:$A$7)-LIGNE($A$2);;1))*($B$2:$B$7="a"))
vba-new
j
juicelink
Jeune membre
Jeune membre
Messages : 28
Inscrit le : 11 septembre 2014
Version d'Excel : 2010

Message par juicelink » 11 septembre 2014, 19:21

Génial !

j'avais vu que la fonction sommeprod pouvait m'aider mais je trouvais pas comment.
et je ne connaissais pas DECALER

je comprends pas tout à la formule mais le fait est que ça marche impeccable !
je vais prendre un peu de temps pour la comprendre.

merci beaucoup !

ju
v
vba-new
Passionné d'Excel
Passionné d'Excel
Messages : 3'003
Appréciations reçues : 2
Inscrit le : 13 mai 2009
Version d'Excel : 2010 FR - 2013 FR

Message par vba-new » 11 septembre 2014, 22:26

Re,

Ça peut-être un peu compliqué à comprendre au premier abord.
Mais le principe est de transformer chaque ligne de ton tableau en sous-tableau d'un seul élément. En faisant ensuite un SOUS.TOTAL de chacun de ces sous-tableaux avec la fonction "109", on obtiendra 0 si la ligne est masquée ou la "somme" de ton unique élément si la ligne n'est pas masquée...

Ex:
Avec le tableau de chiffres suivant : {20, 50, 100}, si la 2nde ligne est masquée, la fonction SOUS.TOTAL retournera {20, 0, 100}
Si la 1ère et 3è ligne sont masquées, la fonction SOUS.TOTAL retournera {0, 50, 0}.
Etc.

La fonction SOMMEPROD s'occupe ensuite d'additionner en fonction des critères.
vba-new
j
juicelink
Jeune membre
Jeune membre
Messages : 28
Inscrit le : 11 septembre 2014
Version d'Excel : 2010

Message par juicelink » 12 septembre 2014, 18:57

Ok, je crois que je comprends.

je vais essayer de l'utiliser sur un autre tableau pour etre sur de moi...

merci !
ju
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message