[Résolu][Calc] Fonction SOUS.TOTAL pour colonnes visibles

[ThierryM]

Adieu a totes,

Je souhaiterais obtenir l'équivalent de la fonction =SOUS.TOTAL(109; zone) mais en ligne. Je voudrais ainsi afficher le total d'une ligne en ne tenant compte que des colonnes affichées.
Je pensais passer par une fonction personnalisée concernant une zone de cellule en m'appuyant sur une largeur de colonne > 0, mais je me rends compte que l'argument "arg" renvoie des coordonnées relatives (première ligne de la zone = 1, première colonne =1).
Voici la fonction (qu'on trouve aussi dans le fichier ci-joint) tirée à partir de la bible de Bernard Marcelly et Laurent Godard :

Code : Tout sélectionner

Function SOMMECOLVISIBLES(arg) As Double
 Dim colonne As Long, resultat As Double
 resultat=0
 'xray arg
 For ligne=LBound(arg) to UBound(arg)
 	For colonne=LBound(arg,2) to UBound(arg,2)
                ' si la colonne a une largeur >0 alors on fait la somme ci-dessous
 		resultat=resultat+arg(ligne,colonne)
 	Next colonne
 Next ligne
 SOMMECOLVISIBLES=resultat
End Function

Serait-il donc possible de récupérer la source de la zone concernée dans une fonction personnalisée c'est-à-dire ses coordonnées absolues ?
En espérant avoir été assez clair et vous remerciant pour vos éclairages, cordialement,

Thierry

[DLE]

Bonjour,

Une proposition à valider, la macro utilise l'adressage par getCellByPosition avec les arguments du tableau passé en paramètre puis teste si la colonne est visible ou pas.
Cdlt

[ThierryM]

Merci DLE de t'être penché sur mon problème.
Malheureusement ta proposition n'est pas universelle (elle implique que l'on commence à la colonne A et d'être toujours sur la ligne 2). Je voudrais une formule qui fonctionne pour n'importe quelle plage où l'on se place dans la feuille.
Du coup, j'ai modifié mon exemple avec un calcul sur la plage C4:G4.
J'ai aussi rajouté l'astuce avec ALEA() pour forcer le rafraîchissement de la feuille (astuce que j'ai vu passer dernièrement et très pratique) en mettant dans la cellule :

Code : Tout sélectionner

=SOMMECOLVISIBLES(C4:G4)+ALEA()*0

Je joins un nouveau fichier exemple.
Cordialement,

Thierry

[DLE]

Re,

Voici une nouvelle version, la plage de cellules est passée en tant que string ex.

Code : Tout sélectionner

=SOMMECOLVISIBLES("C4:G4")+ALEA()*0

La macro est basée sur l'énumération des cellules de la plage passée en argument et teste si la colonne est visible ou pas.
Cdlt.

[ThierryM]

Merci DLE pour cette évolution !

On se rapproche mais ... je vais faire mon pénible...
En fait, ce qui m'intéressait dans l'utilisation d'une formule personnalisée, c'était de pouvoir saisir une plage de cellules afin par exemple qu'on puisse faire de la recopie incrémentée automatiquement pour les lignes adjacentes, de voir quelles cellules étaient concernées dans la formule.
En passant par une chaîne de caractères plutôt que par une plage de cellules, on perd tous les avantages d'une formule : ça demande de ressaisir à chaque fois la nouvelle plage concernée.
Je pense qu'on a là une limitation des fonctions personnalisées.
Je vais peut-être aller voir du côté de la fonction CELLULE qui possède un paramètre WIDTH et de la fonction ADRESSE.
Cordialement,

Thierry

[OOotremer971]

Bonjour,

A tester :

Code : Tout sélectionner

Option Explicit

Private oDoc As Object
Private oFeuilActive As Object

Function SOMMECOLVISIBLES(arg1 ,arg2) As Double
Dim ref As Variant
Dim oCell As Object
Dim laPlage As Object
Dim oFeuil As Object
Dim nValue As Double
Dim i As Long
Dim j As Long

ref = split(arg2,"|") 
oDoc = ThisComponent
oFeuilActive = oDoc.CurrentController.ActiveSheet
oCell = oFeuilActive.GetCellRangeByName(ref(0))
laPlage = Disseque(ref(0))
oFeuil = oDoc.Sheets(laPlage.Sheet)
nValue = 0
For i = laPlage.StartRow to laPlage.EndRow
	For j = laPlage.StartColumn to laPlage.EndColumn
		oCell = oFeuil.GetCellByPosition(j,i)
		if oCell.Columns.IsVisible = True Then
			nValue = nValue + oCell.Value 
		end if
   next j
next i
SOMMECOLVISIBLES = nValue
End Function

Function Disseque(Tag) 'As Object
Dim plage As Object ,celluleRef As Object ,feuil As String
Dim ref() ,reff() ,refx() ,refy() ,refz()
celluleRef = oFeuilActive.GetCellRangeByName(Tag)
ref() = Join(Split(celluleRef.Formula, "$"), "")
refx() = split(ref() ,"(")
refy() = split(refx(1) ,";")
refz() = split(refy(0) ,".")
if Ubound(refz()) = 0 Then 'Nom de feuille absent !
   ref() = refz(0)
   feuil = oFeuilActive.Name
else
   ref() = refz(1)
   reff() = split(refz(0) ,"'")
   if Ubound(reff()) = 0 Then 'Le nom de feuille n'est pas numérique.
      feuil = reff(0)
   else
      feuil = reff(1)
   end if
end if
plage = oDoc.Sheets.GetByName(feuil).GetCellRangeByName(ref()).RangeAddress
Disseque = plage
End Function

Le fichier contient une expression nommée id dont le contenu est :

Code : Tout sélectionner

ADRESSE(LIGNE();COLONNE();4)&"|"&ALEA()

l'appel de la fonction se fait par sélection d'une plage, suivit d'un deuxième argument id :

Code : Tout sélectionner

=SOMMECOLVISIBLES(C4:G4;id)

ou pour calculer les valeurs d'une autre feuille :

Code : Tout sélectionner

=SOMMECOLVISIBLES($Feuille2.B3:F3;id)

id transmet à la fonction l'adresse de la cellule qui l’appelle. De cette façon, on peut disséquer (avec la fonction disseque) la formule pour en extraire la plage passée en argument.

[ThierryM]

Merci OOotremer971 et bravo !

Excellente l'astuce du 2e argument "id".
Du coup, je découvre qu'on peut créer des expressions "blanches" ou des formules dans un classeur calc sans les mettre dans une cellule, chose que je n'aurais même pas envisagé d'imaginer. Là, on passe un cap .
Je pense que tu devrais mettre ton code dans la section "Suprême de code" car ça ouvre pas mal d'horizons, en tout cas pour moi (faudra juste que j'y repense).

En tout cas, merci à vous 2, DLE et OOtremer971 d'avoir consacrer du temps à mon problème (qui je pense, pourrait en intéresser d'autres).
Cordialement,

Thierry