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.

 

[Paulle]

Bonjour AtTheOne,
Pour réafficher les colonnes, il faut selectionner les colonnes et faire "Afficher"?
Cela fonctionne mais plus de manipulation que la solution de Job.

PS : Si un post répond à votre question, si vous le voulez, merci de le marquer comme solution (case à cocher à droite du post)
Dans une discussion, il n'est pas possible de marquer plusieurs réponses comme solution.

 

[job75]

quand tu masques ou tu affiches des colonnes je mets à jour un nom "Masque" qui vaut 0 pour les colonnes du tableau masquées et 1 pour celles qui sont visibles.
La colonne somme fait un SOMMEPROD de chaque ligne et du nom "Masque".

L'idée est intéressante et je l'ai testée avec cette macro :

Sub MAJ_Somme()
Dim a(), i%
With [Tableau1] 'tableau structuré
    .AutoFilter: .AutoFilter 'ôte le filtrage automatique
    .Rows.Hidden = False 'affiche toutes les lignes
    .Columns(1).Hidden = False
    ReDim a(1 To .Columns.Count - 1)
    For i = 2 To .Columns.Count: a(i - 1) = 1 + .Columns(i).Hidden: Next i
    ThisWorkbook.Names.Add "Masque", a 'nom défini
    .Columns(1) = "=SUMPRODUCT(Masque,RC[1]:RC[" & UBound(a) & "])"
    .Columns(1) = .Columns(1).Value 'supprime les formules
    .AutoFilter 1, "<>0" 'facultatif, le filtre automatique masque les valeurs zéro en colonne Somme
End With
End Sub

Elle s'exécute entre 0,06 et 0,07 seconde, elle est donc un peu moins rapide que celle que j'ai donnée.

 

[AtTheOne]

Re

Dans une discussion, il n'est pas possible de marquer plusieurs réponses comme solution

Ce n'est pas un problème il faut juste marquer celui qui te convient le mieux

Pour réafficher les colonnes, il faut selectionner les colonnes et faire "Afficher"?
Cela fonctionne mais plus de manipulation que la solution de Job.

Avec cette version tu as le choix :
Soit réafficher certaines colonnes seulement, alors tu passes par le menu contextuel (Sélection, clic droit, Afficher (personnalisé) )
Soit réafficher tout , alors tu cliques sur le bouton Afficher tout comme avec la solution de @job75

Par contre pour ce qui est de masquer tu gagnes une manip (pas besoin de mettre à jour la somme en cliquant sur un bouton)
@job75 : merci pour la méthode pour .autofilter 2 fois pour enlever les filtres

À bientôt

 

[AtTheOne]

Salut @job75
pas vu ta réponse (pb rafraîchissement)
Merci pour le retour
Mais pourquoi ne pas conserver les formules ? Question de rapidité pendant les recalculs ?
À plus

 

[job75]

Mais pourquoi ne pas conserver les formules ? Question de rapidité pendant les recalculs ?

Une fois calculées les formules ne servent plus à rien et alourdissent inutilement le fichier.

 

[Paulle]

La réinitialisation est plus rapide avec la méthode de Job.

 

[AtTheOne]

Re

La réinitialisation est plus rapide avec la méthode de Job.

Une fois calculées les formules ne servent plus à rien et alourdissent inutilement le fichier.

La réponse est là
J'y vois juste un inconvénient : si on modifie des valeurs il faut réactualiser, mais bon pour le temps que cela prend on peut comprendre 🤭
À plus

 

[Paulle]

Merci AtTheOne pour tes réponses.

 

[job75]

Bonjour le forum,

Pour entrer les formules j'avais écrit :

.Columns(1) = "=SUM(RC[2]:RC[" & .Columns.Count & "])"

alors qu'il faut écrire :

.Columns(1) = "=SUM(RC[2]:RC[" & .Columns.Count - 1 & "])"

Cela ne change rien au résultat ni à la durée mais j'ai quand même corrigé le fichier (3) du poste #43.

A+

 

[Paulle]

Bonjour Job,

C'est rectifié.
Merci.

 

[AtTheOne]

Bonsoir à toutes & à tous, bonsoir @Paulle et @job75

Bon juste une idée qui me passait par la tête et que je voulais tester, à vous de voir ...
Pour essayer d'améliorer l'ergonomie, j'ai détourné un segment pour afficher/masquer les colonnes en un clic :

Ici la colonne 01/09/2023 a été masquée, on peut masquer d'autres colonnes en un clic (plusieurs en une fois avec la touche CTRL) ou la réafficher en recliquant sur son bouton, ou réafficher tout en cliquant sur l'entonnoir.

Les colonnes "date" sont listées dans un TS de la feuille "Tables" (TS_ChoixColonnes) sous forme de dates, un TCD (TCD_ChoixColonnes) reprend ces dates et le segment de la feuille Test pointe sur ce TCD.

Pourquoi passer par un TCD et non pas directement sur le TS : parce qu'il n'y a pas d'événement de TS géré en VBA, alors qu'avec le TCD je m'appuis sur l'événement Worksheet_PivotTableUpdate de la feuille Tables pour détecter les actions sur le segment.

Une macro "MàJ_TS_ColDates" gère les modifications de colonnes du TS "TS_Test" (Modifications, ajouts, suppressions de colonnes "Date" dans le TS) pour ajuster le TCD et le segment.

Moi je trouve ça sympa, je n'ai pas fait de vérification sur les performances, mais ce n'était pas le sujet de cet essai. (sur ce jeu de données, ici, c'est instantané).
Je me suis obstiné à garder les formules en cas de modification de données (si on oubliait dans la version avec les valeurs de faire une mise à jour)

La macro "MàJ_TS_ColDates"

Sub MàJ_TS_ColDates()
  
     Dim tb(), col As Range, i As Long, NbCol As Long
     'Comptage des colonnes "Date" du Tableau Structuré TS_Test et constitution de leur liste
     For Each col In Sh_Test.[TS_Test].ListObject.HeaderRowRange.Cells
          If IsDate(col) Then
               i = i + 1: ReDim Preserve tb(1 To i): tb(i) = col
          End If
     Next
     NbCol = UBound(tb)
  
     'Mise à jour du TS TS_ChoixColonnes
     With Sh_Tables.[TS_ChoixColonnes]
          .ClearContents
          .ListObject.Resize .Offset(-1, 0).Resize(NbCol + 1)
     End With
     Sh_Tables.[TS_ChoixColonnes].FormulaLocal = Application.Transpose(tb)
  
     'Mise à jour du TCD TCD_ChoixColonnes et du Segment associé Seg_ChoixColonnes
     Sh_Tables.PivotTables("TCD_ChoixColonnes").PivotCache.Refresh
     With ThisWorkbook.SlicerCaches("Segment_Choix_Colonne").Slicers("Seg_ChoixColonnes")
          .DisableMoveResizeUI = False
          .NumberOfColumns = NbCol
          'Largeur en fonction du nombre de colonnes
          .Width = Application.CentimetersToPoints(1.6 + 2.3 * NbCol)
          .DisableMoveResizeUI = True
     End With
  
End Sub

l'évènement "Worksheet_PivotTableUpdate" de la feuille "Tables"

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

     Dim SlcCache As SlicerCache, SlcIT As SlicerItem, Masqué As Boolean
     Set SlcCache = ThisWorkbook.SlicerCaches("Segment_Choix_Colonne")
     Masqué = False
     For Each SlcIT In SlcCache.SlicerItems
          If Not SlcIT.Selected Then Masqué = True
          Sh_Test.[TS_Test].ListObject.ListColumns(SlcIT.Caption).Range.EntireColumn.Hidden = Not SlcIT.Selected
     Next

     Application.EnableEvents = False
     DéfMasque
     With Sh_Test.[TS_Test]
          If Masqué Then
               .AutoFilter Field:=.ListObject.ListColumns("Somme").Index, Criteria1:="<>0"
          Else
               .AutoFilter: .AutoFilter
          End If
     End With
     Application.EnableEvents = True

End Sub

Voir le fichier joint

À bientôt

 

[Paulle]

Bonjour AtTheOne et Job,

Oui ca colle bien à ce que je veux. C'est une seconde solution avec ceelle de Job.
Bonne journée.

 

[job75]

Bonjour Paulle, AtTheOne,

Avec cette macro dans le code de la feuille du fichier (4) on évite le bouton "MAJ Somme" :

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With [Tableau1] 'tableau structuré
    If Intersect(Target, .Rows(0)) Is Nothing Then Exit Sub Else Cancel = True
    Set Target = Intersect(Target, .Cells(0, 3).Resize(, .Columns.Count - 2))
End With
Afficher_Tout
If Not Target Is Nothing Then Target.EntireColumn.Hidden = True
MAJ_Somme
End Sub

C'est quand même plus simple qu'un TCD et des segments non ?

A+

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @job75

C'est quand même plus simple qu'un TCD et des segments non ?

Oui mais tu n'es pas joueur, c'était un essai pour avoir une interface sympa.
Personnellement, je pense qu'il ne faut pas regarder aux moyens mis en œuvre mais à l'aspect convivial pour l'utilisateur final :

Pour essayer d'améliorer l'ergonomie

Après chacun fait comme il préfère, ce n'est pas une compétition ! 🤣
À bientôt

 

[job75]

ce n'est pas une compétition ! 🤣

La compétition on la fait avec soi-même en recherchant la solution la plus simple pour un même résultat.