XL 2019 Somme des cellules visibles dans Excel

[JoBar57]

Bonjour,

La fonction =SOUS.TOTAL(109;C2:C10) ne fonctionnant que pour les colonnes, je cherche à obtenir le même résultat mais sur des lignes (dans un planning dont les colonnes de fin de mois sont masquées ou non en fonction de la durée du mois sélectionné), mais mes connaissances sont un peu insuffisantes !
Quelqu'un aurait-il une idée de solution ?

Merci

 

[Chris401]

Bonsoir et bienvenue
Merci de joindre un fichier exemple (sans données confidentielles)

 

[Phil69970]

Bonjour @JoBar57 , @Chris401

Pourtant sous-total fonctionne....

*Merci de ton retour

@Phil69970

 

[mapomme]

Bonsoir à tous,

@JoBar57 désire la somme de cellules en ligne en excluant les cellules des colonnes masquées.
Et pour ce cas Sous.total est inefficace.

Il n'y a pas de solution simple car le masquage des colonnes ou l'affichage des colonnes ne nous sont pas mis à disposition par le gestionnaire d'évènement. Il y a des palliatifs (pas jojo) par VBA.

 

[M92_]

Salut,

Une solution (sans VBA)..
J3 =SOMME.SI($A$1:$H$1;">0";$A3:$H3)

Les cellules A1, B1, D1, E1, G1 et H1 égalent à =@CELLULE("largeur";x1).
Cette formule renvoie la valeur 0 quand la colonne x1 est masquée dans la feuille active.

Sans aucune colonne masquée, J3 = 6

Avec les colonnes D et E masquées, J3 = 4

Cdlt,
M92

 

[mapomme]

Bonsoir à tous,

Une version à la sauce @mapomme (pas jojo).

Une fonction nommée Visible qui prend comme argument une plage contigüe de cellules.

• Cette fonction renvoie un tableau de même dimension que la plage:
• Chaque élément du tableau contient 1 si la cellule est visible et contient 0 si la cellule est masquée.

Cette fonction peut-être utilisée dans un SOMMEPROD (voir fichier)

Le petit os est que le gestionnaire d'évènement ne met pas à notre disposition l'interception du masquage ou de l'affichage des colonnes. Cela signifie que si vous masquez une colonne, il n'y aura pas de recalcul de la feuille. C'est très gênant 😱.

Pour cela, on lance toutes les deux secondes un recalcul de la feuille pour prendre en compte les cellules qui viennent d'être masquées ou affichées.

Le code principal se trouve dans module1.
Il y a aussi un peu de code dans le module associé au classeur lui-même (ThisWorkbook) qui se charge de lancer le premier recalcul après deux secondes et qui se charge d'inhiber ce recalcul à la fermeture du fichier.

Code dans module1:

Option Explicit

Const Feuille = "Feuil1"   'Nom de l'onglet de la feuille à recalculer
Const Delai = 86400#       'nombre de secondes dans un jour
Public DernierTop          'date et heure du prochain recalcul

Sub CalculerFeuille()            'Procédure de recalcul et programmation du prochain recalcul
   ArreterCalculAuto             'arrêt de recalcul auto
   Sheets(Feuille).Calculate     'calcul de la feuille
   LancerCalculAuto              'programmation du prochain recalcul
End Sub

Sub ArreterCalculAuto()          'procédure d'arrêt du recalcul auto
   On Error Resume Next          'au cas où il n'y a pas de prochain recalcul programmé
   Application.OnTime EarliestTime:=DernierTop, Procedure:="CalculerFeuille", Schedule:=False
   On Error GoTo 0               'on réactive l'interception des erreurs
End Sub

Sub LancerCalculAuto()              'procédure de programmation du prochain recalcul auto
   DernierTop = Now() + 2 / Delai   'date et heure du prochain recalcul (dans deux secondes)
   Application.OnTime EarliestTime:=DernierTop, Procedure:="CalculerFeuille", Schedule:=True
End Sub

Function Visible(x As Range)
Application.Volatile
Dim xcell, i&, j&
   'déclaration d'un tableau de même dimension que la plage de cellule x
   ReDim t(1 To x.Rows.Count, 1 To x.Columns.Count)
   For Each xcell In x        'pour chaque cellule de la plage
      'i est le N° de ligne (n° absolu) de la cellule xcell
      'j est le N° de colonne (n° absolu) de la cellule xcell
      i = xcell.Row - x(1, 1).Row + 1: j = xcell.Column - x(1, 1).Column + 1:
      't(i,j)=1 si la cellule est visible ou bien vaut 0 si la cellule est masquée
      t(i, j) = -1 * (xcell.EntireColumn.Hidden = False And xcell.EntireRow.Hidden = False)
   Next xcell
   Visible = t       'on retourne le tableau t
End Function

Le code dans le module de ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()
   LancerCalculAuto
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ArreterCalculAuto
End Sub

 

[mapomme]

Une solution (sans VBA)..

Oui mais pas de mise à jour automatique des calculs quand on affiche ou masque des colonnes.

 

[M92_]

Oui mais pas de mise à jour automatique des calculs quand on affiche ou masque des colonnes.

Si, car les boutons +/- forcent le recalcul systématiquement. D'où l'intérêt de grouper les colonnes. La cellule J3 se met alors à jour à chaque clic (affichage ou masquage des colonnes).

 

[mapomme]

Si, car les boutons +/- forcent le recalcul. D'où l'intérêt de groupes les colonnes.

Oui c'est sûr mais l'auteur n'a pas parler de plan. Il n'a d'ailleurs rien fourni du tout 🙁

 

[JoBar57]

Bonjour @JoBar57 , @Chris401

Pourtant sous-total fonctionne....

*Merci de ton retour

@Phil69970

Bonsoir,

Oui ça fonctionne pour les colonnes mais pas pour les lignes, d'où mon appel à l'aide.

Cdlt,

 

[mapomme]

A mon humble avis, il suffit sans doute d'adapter tout simplement les formules des sommes.
Le calendrier comporte vraisemblablement une ligne avec la date de chaque jour (c'est un peu le principe des calendriers et plannings).
Il suffirait d'utiliser un SOMMEPROD pour ne sommer que les cellules dont le mois (et l'année) des jours correspondant à la somme du mois recherché.
Point de vba, point de plan, juste des formules.
Mais comme on n'a aucun classeur à se mettre sous la dent... On va aller se coucher 😴 😴

 

[JoBar57]

A mon humble avis, il suffit sans doute d'adapter tout simplement les formules des sommes.
Le calendrier comporte vraisemblablement une ligne avec la date de chaque jour (c'est un peu le principe des calendriers et plannings).
Il suffirait d'utiliser un SOMMEPROD pour ne sommer que les cellules dont le mois (et l'année) des jours correspondant à la somme du mois recherché.
Point de vba, point de plan, juste des formules.
Mais comme on n'a aucun classeur à se mettre sous la dent... On va aller se coucher 😴 😴

Désolé, je n'ai pas vu votre message (page non rafraichie), du coup je joins le fichier !

La fonction SOMMEPROD ne fonctionne apparemment pas dans ma feuille de calcul ligne 41 (fichier joint).
De plus le dimensionnement du planning étant commandé par une macro qui se trouve déjà dans le module1, je ne sais pas où intégrer les vôtres.

Merci de prendre du temps

Cdlt,

 

[JoBar57]

Salut,

Une solution (sans VBA)..
J3 =SOMME.SI($A$1:$H$1;">0";$A3:$H3)

Les cellules A1, B1, D1, E1, G1 et H1 égalent à =@CELLULE("largeur";x1).
Cette formule renvoie la valeur 0 quand la colonne x1 est masquée dans la feuille active.

Sans aucune colonne masquée, J3 = 6
Regarde la pièce jointe 1117934

Avec les colonnes D et E masquées, J3 = 4
Regarde la pièce jointe 1117935

Cdlt,
M92

Bonjour,

La formule "=@CELLULE("largeur";x1)" me renvoie la valeur 5 et effectivement la valeur 0 une fois la ou les colonnes sont masquées, mais lorsque je la ou les affiche à nouveau la valeur reste 0 et ne reviens à 5 que si je vais dans la barre de formule et que je valide (peu importe la cellule)

Mais la formule "J3 =SOMME.SI($A$1:$H$1;">0";$A3:$H3)" adaptée à mon tableau ne fonctionne pas, les résultats restitués ne varient pas que la ou les colonnes soient masquées ou non.
Voir fichier joint.

Cdlt,

 

[mapomme]

Re,

En AG41, mettre la formule suivante puis recopier la formule vers le bas:

=SOMME(DECALER(B41:AF41;0;0;1;JOUR(FIN.MOIS($B$7;0))))

 

[JoBar57]

Re,

En AG41, mettre la formule suivante puis recopier la formule vers le bas:

=SOMME(DECALER(B41:AF41;0;0;1;JOUR(FIN.MOIS($B$7;0))))

👍👍👍👍

Merci beaucoup, c'est effectivement beaucoup plus simple que ma tentative de solution qui s'égarait dans une mauvaise direction !
C'est là qu'on voit la différence entre un pro et un débutant dans Excel !

Merci encore.