XL 2019 Sous Total

[Paulle]

Bonsoir,

La formule "=SOUSTOTAL() " pour une colonne n'additionne pas les lignes masquées.

Est-il possible d'obtenir le total d'une ligne ignorant les colonnes masquées.

Merci.

 

[TooFatBoy]

Merci Phil ta solution me convient parfaitement.

Donc au final le but de tes deux fils de discussion était de faire la somme des cellules visibles d'une ligne, ou de masquer les lignes visuellement vides ?

 

[Phil69970]

TFB

Donc au final le but de tes deux fils de discussion était de faire la somme des cellules visibles d'une ligne, ou de masquer les lignes visuellement vides ?

Pas tout a fait

On sélectionne les colonnes à masquer temporairement et ensuite on masque les lignes vides et on affiche le total par ligne que la colonne soit masquée ou pas.

Exemple
Tableau entier

Masquage des colonnes C, E,G et I

La somme de la ligne entourée reste à 35

 

[job75]

Bonjour à tous,

Je ne comprends vraiment pas ce que fait Phil69970, le sujet de ce fil c'est :

Est-il possible d'obtenir le total d'une ligne ignorant les colonnes masquées.

Alors c'est simple, exécutez cette macro en cliquant sur le bouton :

Sub MAJ_Somme()
Dim tablo, ub%, i&, j%
With [Tableau1] 'tableau structuré
    tablo = .Resize(, .Columns.Count + 1) 'Matrice, plus rapide, au moins 2 éléments
    ub = UBound(tablo, 2) - 1
    For i = 1 To UBound(tablo)
        tablo(i, 1) = 0
        For j = 2 To ub
            If .Rows(i).Hidden Or .Columns(j).Hidden Then tablo(i, j) = 0
        Next j
        tablo(i, 1) = Application.Sum(Application.Index(tablo, i, 0))
    Next i
    .Columns(1) = tablo 'restitution
End With
End Sub

A+

 

[TooFatBoy]

Pas tout a fait

Moi j'ai compris qu'il ne fallait faire la somme que des cellules visibles :

Est-il possible d'obtenir le total d'une ligne ignorant les colonnes masquées.

Pour moi, il faut faire la somme des cellules de la ligne du TS, mais en ignorant les nombres qui ne sont pas visibles.

Mais c'est vrai que la phrase peut être ambiguë.

 

[job75]

Notez que la macro de mon post #18 prend du temps.

Pour tester j'ai recopié le tableau A2:J19 sur "seulement" 4 500 lignes : la macro s'exécute chez moi en 16 secondes.

J'ai essayé d'améliorer en utilisant un Dictionary : ça ne change rien.

 

[TooFatBoy]

Notez que la macro de mon post #18 prend du temps.

Est-ce que ce classeur effectue le même travail ?

 

[Paulle]

Bonjour Job75, je viens de tester cela fonctionne.C'est un peu long.

Oui TooFatBoy cela fonctionne aussi.

Je suis un peu perdu dans toutes ces solutions. Je vais regarder cela de près afin de faire mon choix.

Merci encore.

 

[job75]

Est-ce que ce classeur effectue le même travail ?

Oui ta macro fait bien la somme des cellules visibles.

Et en effet c'est bien plus rapide en utilisant .SpecialCells(xlCellTypeVisible).

 

[TooFatBoy]

Oui ta macro fait bien la somme des cellules visibles.

Merci pour ta réponse. 👍

 

[job75]

Bonjour Paulle, le forum,

Voici une solution bien plus rapide que celle utilisant .SpecialCells(xlCellTypeVisible) :

Sub MAJ_Somme()
Dim t, d As Object, i%, titre$
t = Timer
Set d = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
With [Tableau1] 'tableau structuré
    .AutoFilter: .AutoFilter 'ôte le filtrage automatique
    .Rows.Hidden = False 'affiche toutes les lignes
    .Columns(1).Hidden = False
    For i = 2 To .Columns.Count
        If .Columns(i).Hidden Then
            d(i) = .Columns(i).Value
            .Columns(i) = Empty 'efface les valeurs après les avoir mémorisées
            titre = titre & "-" & Cells(1, i + .Column - 1).Address(0, 0)
        End If
    Next i
    .Columns(1) = "=SUM(RC[2]:RC[" & .Columns.Count & "])"
    .Columns(1) = .Columns(1).Value 'supprime les formules
    For i = 2 To .Columns.Count
        If d.exists(i) Then .Columns(i) = d(i) 'restitution
    Next i
End With
titre = Mid(Replace(titre, 1, ""), 2)
titre = IIf(d.Count = 0, "Aucune colonne masquée", IIf(d.Count = 1, "1 colonne masquée ", d.Count & " oolonnes masquées ")) & titre
Application.ScreenUpdating = True
MsgBox "Mise à jour de la colonne Somme en " & Format(Timer - t, "0.00 \sec"), , titre
End Sub

Les boucles se font sur les colonnes, il n'y en a pas sur les lignes.

Sur le tableau de 4 500 lignes la durée d'exécution chez moi est entre 0,02 et 0,05 seconde suivant le masquage.

A+

 

[job75]

Si l'on veut masquer les lignes dont la valeur en colonne Somme est nulle on utilisera :

    .AutoFilter 1, "<>0" 'facultatif, le filtre automatique masque les valeurs zéro en colonne Somme

 

[Paulle]

Bonjour Job,

Je regarde ça dès que possible.

Bonne journée.

 

[Paulle]

Merci Job, la version MAJ_Somme(2).xlsm est parfaite. C'est exactement ce que je voulais.

 

[Paulle]

Bonjour,

J'avais oublié qu'il est essentiel de le réinitialiser le tableau pour rajouter des valeurs en colonne ou en ligne et de refaire le calcul.

J'ai tenté une macro "Sub Afficher()", les colonnes masquées s'affichent mai les lignes masquées le restent.

J'ai encore besoin de votre aide.
Merci.

 

[sylvanu]

Bonjour Paulle,
Chez moi, la macro "Afficher" marche.
Dans votre macro la ligne 37 est en commentaires. J'ai utilisé :

Sub Afficher()
    Range("Tableau1").Select
    Application.Goto "Tableau1"
    Selection.EntireColumn.Hidden = False
    Selection.EntireRow.Hidden = False
    Range("B5").Select
End Sub

Juste retiré le commentaire.
ou peut être plus simplement :

Sub Afficher()
    With Range("Tableau1")
        .EntireColumn.Hidden = False
        .EntireRow.Hidden = False
    End With
End Sub