faire une somme que sur les colonnes visibles

**mimilll** · 21/08/2009, 14h11

Bonjour,

je suis très surpris:
j'utilise souvent la formule sous total (=teilergebnis(9;A1:A10) en allemand) pour n'afficher que la somme des éléments d'une liste filtrée.

J'ai voulu faire la meme chose pour des colonnes chachées. Mais là ca continue de me faire la somme de tout le champ!!

Quelle fonction existe?

Je vous remercie,
Emil

**tranzebou** · 21/08/2009, 14h57

J'ai trouvé ça dans l'aide Excel : "Pour les constantes no_fonction de 101 à 111, la fonction SOUS.TOTAL ignore les valeurs des lignées masquées au moyen de la commande Masquer du sous-menu Ligne du menu Format"
Donc normalement si tu remplaces sous.total(9;...) par sous.total(109;...) ça devrait marcher.

Cdlt

**mimilll** · 21/08/2009, 15h25

oui mais non...

je suis aussi tombé dessus en parrallèle (pfiouuu je ne sais pas si on peut encore appeler ca le menu d'aide quand c'est en allemand!?

), et ca ne marche pas...

cf. fichier d'exemple

ou peut etre que ca marche chez toi??

MERCI tout de meme!

**tranzebou** · 21/08/2009, 16h10

Effectivement après avoir relu l'aide et fait le test, ça ne marche qu'en masquant des lignes et pas des colonnescomme dans ton cas, peut-être as-tu la possibilité de transposer ton tableau pour remettre les années en lignes. (Il existe à ce propos la fonction matricielle transpose() je sais pas si tu l'as connais).
Si tu n'en as pas la possibilité je ne vais pas pouvoir t'aider mais peut-etre que quelqu'un maitrisant mieux Excel le fera, ne despères pas ;-)

**mimilll** · 21/08/2009, 16h38

Bon,

Premièrement, tu me rassures, car je déteste les bugs du genre: ca plante chez moi et pas chez les autres.

Deuxièmement: je suis dans la m----! => je ne peux pas changer le tableau...

Merci tranzebou de ton aide,

QUELQU'un AURAIT-IL UNE SOLUTION? lution? tion? on? ... [echo dans le vide tres tres vide]

**Fvandermeulen** · 21/08/2009, 17h48

Salut,
J'ai une solution mais elle passe par VBA...
Tu fais un click droit sur l'onglet de ta feuille, tu choisis voir code
Tu colles le code ci-dessous.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DerCol As Long
 
'Récupère le numéro de la dernière colonne remplie non masqué sur base de la ligne 24
DerCol = Cells(24, Rows(24).Cells.Count).End(xlToLeft).Column
'Affecte à la cellule A25 la somme de la plage non masquée
Cells(25, 1) = Application.Sum(Range(Cells(25, 2), Cells(25, DerCol)))
End Sub

Le principe:
Quand tu change de cellule, la macro fait le calcul sur base des colonnes visible, !!! uniquement si tu masques tout à partir d'une colonne.
Par exemple si colonne D masquée mais pas E et F le total ne sera pas OK
Si ce genre de cas arrive, dis le il faut une macro un peu plus élaboré.

A+

Edit:
Au cas ou, voici le code qui gère le fait qu'une colonne qui n'est pas la dernière soit masquée, si tu as des questions n'hésites pas...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DerCol As Long, val As Long
Dim Cel As Range
 
'Récupère le numéro de la dernière colonne remplie non masqué sur base de la ligne 24
DerCol = Cells(24, Rows(24).Cells.Count).End(xlToLeft).Column
 
For Each Cel In Range(Cells(25, 2), Cells(25, DerCol))
    If Cel.EntireColumn.Hidden = False Then
    val = val + Cel.Value
    End If
Next Cel
 
Cells(25, 1) = val
 
End Sub

**ullan** · 21/08/2009, 18h32

Bonjour,

En dehors de la solution de mon "presque" voisin, il y a une solution par formule si la ou les colonnes à ne pas additionner (masquées dans la question) se trouvent à l'extrême droite du tableau ou que l'en-tête de la dernière colonne est connue.

En précisant quelque peu le but de la procédure il y aura peut-être moyen de trouver une échappatoire.

A te lire.

**mimilll** · 24/08/2009, 12h03

Salut le Forum, j'espère que votre WE fut agréable!

Merci Fvandermeulen,
je ne pensais pas encore utiliser une macro pour quelque chose d'aussi ridicule que cela! je vais donc garder ta solution sous le coude si jamais je n'arrive a rien sans macro

MERCI !

Salut ullan,
je serais intéressé par ta solution, j'ai modifier le fichier excel , il t'explique exactement ce que fait et comment est le fichier.

Je te remercie!
Emil

**Fvandermeulen** · 25/08/2009, 08h24

Bonjour,
J'ai regardé ton fichier, voici une formule qui répond à ton problème, reste à voir comment tu définis l'année de début, j'ai utilisé pour l'exemple la fonction 'aujourdhui()' pour le début et 'aujourdhui()+3 pour la fin

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SI(SOMME(SI($C$2:$W$2>ANNEE(AUJOURDHUI());SI($C$2:$W$2<ANNEE(AJOURDHUI())+3;$C3:$W3;0)))>0;"Diagram";0)}

!! c'est une formule matricielle, je rappelle à toute fin utile qu'il faut valider par Ctrl+Shift+Enter

A+

**mimilll** · 25/08/2009, 10h59

Salut Fvandermeulen!

merci de te donner autant de peine, ca fait vraiment du bien quand on est dans la merde...

J'ai vraiment l'impression d'etre super embetant, mais les colonnes sont cachées manuellement, et suivant si on travaille a court terme ou a moyen terme, ou meme si un événement singulier à eu lieu les dernieres années, le choix des années est en réalité très imprévisible, meme trop imprévisible pour ta formule.

Ce que je cherche c'est un test (en formule excel) pour détecter si la colonne (ou plus simplement la cellule) est cachée.
Mon idée est de faire (un peu comme toi mais je ne sais pas si j'ai besoin d'une fonction matricielle) : = somme.si(champ ; critère=cellule visible).

J'ai cherché en faisant des choses avec (attention je traduis à la volée de l'Allemand): =Cellule("colonne";A2:A10)
... sans succès... ca me donne toujours le meme résultat, que je cache les premières colonne ou non.

Ensuite j'ai trouvé la variante: =cellule("largeur";A1). mais le résultat ne se met pas à jour automatiquement lorsque je change la largeur de la colonne et je n'arrive ensuite pas à l'utiliser pour faire une somme. Je vais essayer en m'inspirant de ta fonction matricielle...

Je n'arrive pas a croire qu'on puisse galérer à ce point, alors qu'excel le fait tres bien pour une colonne avec des lignes cachées!

Ca rejoint l'impossibilité de faire un tri à l'horizontal!!

Pour moi, ca devrait etre ca le travail de MS: écouter les gens grace a ce genre de forum pseudo corporate, repérer les lacunes et se casser le cul pour ne pas avoir de tel vide dans les fonctionnalités BASIQUES (il s'agit de faire la somme de ce qui est affiché!!! plus simple, on n'a pas besoin d'Excel!!!) donc pas de perdre du temps a faire de l'animation, des bandeaux, des fenetres en 3D qui bouffe toute les ressources, etc. Pardon si je m'emporte... mais bon...

**mimilll** · 25/08/2009, 11h03

je galère avec ca:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
=SUMMENPRODUKT((ZELLE("breite";E9:H9)>0)*(E9:H9))
 
en francais:
=SOMMEPRODUIT((CELLULE("largeur";E9:H9)>0)*(E9:H9))

je pense que ((CELLULE("largeur";E9:H9) n'aime pas le champ, il préfère une cellule unique... mais je pensais que ca servait justement à ca une sommeproduit.........

**ullan** · 25/08/2009, 11h38

Bonjour,

Quelque soit la solution, macro ou formule, il faut toujours faire une manipulation.
Soit masquer des colonnes, soit spécifier les bornes de la plage de calcul.

Il me semble qu'en masquant des colonnes ce n'est pas possible.
La fonction CELLULE("largeur";référence) renvoie la largeur de la cellule, même quand elle est masquée, ce n'est donc pas une solution.

En spécifiant les bornes, pas plus contraignant que de masquer des colonnes, la solution est simple, vois la pièce jointe

**Fvandermeulen** · 25/08/2009, 11h45

Re,
Sauf erreur ta piste ne fonctionne que pour les lignes, même principe que sous.total...
J'ai une autre piste via une fonction perso mais du coup autant intégrer le premier code que je t'ai donné à ton code qui masque les lignes...

Voici la fonction perso:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Function SumNotHidden(champ As Range)
Dim S As Long
Application.Volatile
S = 0
For Each c In champ
    If Not c.EntireColumn.Hidden Then
        S = S + c.Value
    End If
Next c
SumNotHidden = S
End Function

A+

**mimilll** · 25/08/2009, 13h59

Merci de vos réponses,

ullan:

La fonction CELLULE("largeur";référence) renvoie la largeur de la cellule, même quand elle est masquée, ce n'est donc pas une solution.

faux => lorsque la colonne est cachée, tape F9, et là, magie, le résultat est =0. Il "suffit" ensuite de tester la valeur 0. Chose que je n'ai pas réussi.

En spécifiant les bornes, pas plus contraignant que de masquer des colonnes, la solution est simple, vois la pièce jointe

et si JE VEUX cacher les colonnes??! En réalité j'ai 1 colonne par demi-année jusque 2040, ok?

Fvandermeulen:
Tu étais proche du but avec ta formule matricielle!

Je n’arrive pas a y insérer quelque chose qui me détecte ce qui est caché. Voie sans issue ?
Si je ne voulais pas de macro c'est parce que sur mes 645 lignes (pour etre exact) de tableau je dois avoir en réalité 400 lignes DISPERSEES à tester...... J’ai du mal a voir comment je peux commander ca efficacement/élégamment ?!

**Fvandermeulen** · 25/08/2009, 14h07

Envoyé par mimilll

...
Si je ne voulais pas de macro c'est parce que sur mes 645 lignes (pour etre exact) de tableau je dois avoir en réalité 400 lignes DISPERSEES à tester...... J’ai du mal a voir comment je peux commander ca efficacement/élégamment ?!

Re,
Qu'entends tu par dispersées, tu as des lignes vides?
Si oui, qu'en fais tu ?
Explique concrètement et on adapte le code, ça doit pas être compliqué.
Entretemps, je regardes ton histoire de largeur avec une formule matricielle...
A+

**Fvandermeulen** · 25/08/2009, 15h34

Re,
Il semble inpossible (du moins pour moi) d'intégrer la fonction Cell dans une matricielle (faut dire que l'aide indique bien que cell récupère la cellule en haut à gauche...) par contre j'ai trouvé un truc alambiqué !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SUM(INDIRECT(ADDRESS(ROW();IF(CELL("width";C3)=0;IF(CELL("width";D3)=0;IF(CELL("width";E3)=0;COLUMN(F3);COLUMN(E3));COLUMN(D3));COLUMN(C3)))):INDIRECT(ADDRESS(ROW();IF(CELL("width";W3)=0;IF(CELL("width";V3)=0;IF(CELL("width";U3)=0;COLUMN(T3);COLUMN(U3));COLUMN(V3));COLUMN(W3)))))

Pour mon test je me suis limité à quelques colonne mais ça fonctionne, par contre vu le nombre de colonne que tu utilise, la formule sera vraiment longue d'autant que tu devras certainement utiliser des & puisque (de mémoire) l'imbrication des if se limite à 7.

Je pense donc que la meilleure solution sera un code VBA

A+

PS je ne me suis pas amusé à traduire en Fr puisque tu es en All, tu n'as qu'à copier la forule telle quelle.

**mimilll** · 25/08/2009, 15h49

comme désiré, le tableau réaliste.
il n'y a quasiment plus de simplification dans cet exemple.

j'y ai pensé également en faisant le test sur autre chose, mais g immediatement abandonné

**Fvandermeulen** · 25/08/2009, 16h08

Encore deux questions:
Dans ta feuille Tabelle1,
As tu des données dans tes lignes Titres ? Lignes 8;9;15;etc
Quand tu indiques ligne vide, elle est complètement vide, n'est ce pas ?

**Fvandermeulen** · 25/08/2009, 16h39

Re,
Pour gagner du temps j'ai considéré que les lignes titres et les lignes vide ne contenaient pas de données.
Toutefois si les lignes Titres ont des données qui ne doivent pas être affichées on peut facilement adapter le code.
!! pour ce code j'ai effacé le contenu des cellule C7, AT7, AU7 et AV7
Si tu as absolument besoin de ces infos, dis le, il faut alors revoir le code.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sub SumNonHidden()
 
Dim StartCol As Long, EndCol As Long, r As Long, DerLigne As Long
 
'Affiche toutes les lignes
Cells.EntireRow.Hidden = False
 
'Défini la dernière ligne utilisée sur base de la colonne C
DerLigne = Cells(Columns(3).Cells.Count, 3).End(xlUp).Row
'Défini la première colonne non masquée, part de la colonne C vers la droite
StartCol = Cells(7, 3).End(xlToRight).Column
'Défini la dernière colonne non masquée, par de la dernière colonne vers gauche
EndCol = Cells(7, Rows(7).Cells.Count).End(xlToLeft).Column
 
'Boucle sur chaque ligne du tableau en commençant à la 8ème
For r = 8 To DerLigne
    'Si la somme de la plage définie par les colonnes non masquées = 0 on masque la ligne en question
    If Application.Subtotal(9, Range(Cells(r, StartCol), Cells(r, EndCol))) = 0 Then Rows(r).Hidden = True
'Passe à la ligne suivante
Next r
 
End Sub

A+

**mimilll** · 25/08/2009, 17h31

alors les reponses:
"As tu des données dans tes lignes Titres ? Lignes 8;9;15;etc" => il n'y a pas de données, que du texte.

"Quand tu indiques ligne vide, elle est complètement vide, n'est ce pas ?" non, il y a des donnees dans les filtres 1,2,3,4,5 (et meme des donnees des calculs dans les colonnes BB et BC)

tu ne peux pas supprimer quoi que ce soit de mon tableau!

pourquoi n'utilises-tu pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
For Each c In champ
    If Not c.EntireColumn.Hidden Then
        S = S + c.Value
    End If
Next

La longueur et la largeur du tableau ne varient pas! ce qui varie, ce sont uniquement les colonnes cachées!
Je vais donc essayer de d'abord détecter les colonnes cachees et donc le champ, puis de faire les sommes; selon le test j'ecrirais 0/diagram.

Question: Comment le code ci dessus se comporte si il tombe sur du texte, des erreur #NV, etc?

Encore un mot: je ne compte pas recevoir un code tout cuit dans l'bec, mais plutot des bribes concernant mes inquiétudes, sinon on ne va jamais s'en sortir!

je vais chercher ce soir!
je te remercie de m'avoir mis sur la piste!
A+

faire une somme que sur les colonnes visibles

Excel

Discussions similaires

Partager

Partager