XL 2019 Somme des cellules visibles dans Excel

JoBar57

XLDnaute Junior
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
 

mapomme

XLDnaute Barbatruc
Supporter XLD
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.
 
Dernière édition:

M92_

XLDnaute Junior
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
1633907525196.png


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


Cdlt,
M92
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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 :eek:.

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:
VB:
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:
VB:
Option Explicit

Private Sub Workbook_Open()
   LancerCalculAuto
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   ArreterCalculAuto
End Sub
 

Pièces jointes

  • JoBar57- SommeCelluleVisible- v1.xlsm
    20.5 KB · Affichages: 4
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
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

XLDnaute Junior
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,
 

Pièces jointes

  • Planning horaire Test.xlsm
    43.8 KB · Affichages: 4
Dernière édition:

JoBar57

XLDnaute Junior
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,
 

Pièces jointes

  • Planning horaire Test2.xlsm
    44.8 KB · Affichages: 2

Discussions similaires

Statistiques des forums

Discussions
311 741
Messages
2 082 055
Membres
101 882
dernier inscrit
XaK_