Travailler sur les cellules visibles

[Lemailler]

Bonjour,

Parmis les fonctions intégrées d'Excel, n'y a-t-il que SOUS.TOTAL qui permet de travailler sur les cellules visibles ?

Merci
Lemailler

 

[Lemailler]

Re : Travailler sur les cellules visibles

jeanpierre,

Si je comprends bien ta question :
Si je sélectionne avec le filtre les valeurs = à 3, mon filtre renvoi une série de quatre 3 (en colonne B)
Dans ma colonne C, chaque formule doit me renvoyer 3*3 soit 9, car chaque formule de C fait le produit la la cellule B qui est directement à côté d'elle (LC-1) et de la cellule visible directement au-dessus de la première cellule B ci-avant (L-1C-1).

En espérant répondre à ta question,
Lemailler

 

[Lemailler]

Re : Travailler sur les cellules visibles

Re,

Bon je vous laisse car je n'avance plus.
A ce point, je retiens la voie ouverte par Chris qui est très intéressante et peut-être cette info Microsoft avec une fonction personnalisée qui écrite en VBA serait plus simple. Cela traite de la fonction somme, mais il n'y aurait à mes yeux aucun problèmes à lui demander un produit ou autre.

J'ai déjà essayé cette voie, mais comme je découvre le VBA, je n'ai pas tout compris.

Bonne soirée à tous et encore merci,
A bientôt,
Lemailler

Voici l'info :
Si vous avez les plages de cellules qui incluent les lignes masquées ou les colonnes masquées, Microsoft Excel ne fournit pas un moyen facile à faire le somme des cellules visibles uniquement.

La fonction SOMME totalise toutes les cellules d'une plage, contenant les cellules masquées. La fonction SOUS-TOTAL ignore les lignes masquées que s'ils sont partie d'une liste filtrée été a créé en utilisant la commande Filtre automatique ou Filtre avancé.

La fonction personnalisée suivante fait le somme des cellules visibles d'une plage uniquement. Pour utiliser la fonction personnalisée, tapez les ligne suivante dans une cellule dans une feuille de calcul
=Sum_Visible_Cells(A1:A5)
où A1:A5 est la plage que vous souhaitez additionner."

 

[jeanpierre]

Re : Travailler sur les cellules visibles

Re,

Sur le fichier de chris, peut-être : =$B$2*B11 au lieu de : =B2*B11 où $ bloque la colonne pour le premier et la ligne pour le second.

On peut jouer sur l'un ou sur l'autre : $B2 : colonne boquée, ligne non,
$B$2, colonne et ligne bloquées, B$2, colonne libre et ligne bloquée...

Pour modifier en mode rapide F4, une, deux, trois fois pour obtenir ce que l'on veut....

 

[skoobi]

Re : Travailler sur les cellules visibles

Bonsoir tout le monde,
Lemailler, si tu souhaites une solution VBA, essaye ceci:

Sub test()
Dim cellule As Range, cellule1 As Range, cellule2 As Range, resultat As Range
For Each cellule In Range(Range("c5"), Range("c5").End(xlDown))
    If cellule.EntireRow.Hidden = False Then
        Set cellule1 = cellule.Offset(0, -1)
        For ligne = cellule1.Row To 5 Step -1
            If Cells(ligne, cellule1.Column).EntireRow.Hidden = False Then
                Set cellule2 = Cells(ligne - 1, cellule1.Column)
                Exit For
            End If
        Next
        cellule.Value = cellule1 * cellule2
    End If
Next
End Sub

En executant la macro, celà va mettre le résultat directement dans les cellules de la colonne C.

 

[skoobi]

Re : Travailler sur les cellules visibles

heu!!!! oupssss, il y a une erreur, c'est ça qui marche (si j'ai bien compris):

Sub test()
Dim cellule As Range, cellule1 As Range, cellule2 As Range, resultat As Range
For Each cellule In Range(Range("c5"), Range("c5").End(xlDown))
    If cellule.EntireRow.Hidden = False Then
        Set cellule1 = cellule.Offset(0, -1)
        For ligne = cellule1.Row To 5 Step -1
            If Cells(ligne - 1, cellule1.Column).EntireRow.Hidden = False Then
                Set cellule2 = Cells(ligne - 1, cellule1.Column)
                Exit For
            End If
        Next
        If Not cellule2 Is Nothing Then
            cellule.Value = cellule1 * cellule2
  [B]   Else: cellule.Value = 0[/B]
        End If
    End If
Next
End Sub

edit: en gras pour mettre 0 à la premiere cellule dans la colonne C (C4 si pas de filtre).

 

[criscris11]

Re : Travailler sur les cellules visibles

Re à tous, bonsoir Skoobi,
J'ai suivi ce fil depuis le début et je crois bien que ta macro corresponde à la demande de Lemailler : il va être content en voyant ton deuxième code car je te le confirmes : le premier n'était pas bon.
Bonne soirée.

 

[skoobi]

Re : Travailler sur les cellules visibles

Bonsoir criscris11,
tant mieux. Je tiens à préciser qu'il faut exécuter la macro après chaque changement de filtre (celà va de soit 🙄).
Peut-être que celà peut être fait en auto avec une macro évenementielle mais là je sèche.🙁

 

[Lemailler]

Re : Travailler sur les cellules visibles

Bonjour à tous !

Jeanpierre, par rapport à ton post de 21h20, j'ai approndi cette voie, celle lancée par Chris mais pas de résultat concret. J'ai imbriqué des tas de fonctions mais finalement si l'on trouve la solution, cela restera lourd et délicat à utiliser.
A rester cantonné au fonctions intégrées d'Excel, on finit par passer son temps à chercher des ruses pour contourner les limitations innérantes à chaque fonction. Dans le cas de figure de ce fil, la fonction SOUS.TOTAL.
Je me remets donc de suite à l'apprentissage du VBA, afin d'avoir plus de liberté, notamment avec les fonctions personnalisées dans un premier temps.

Merci skoobi pour ta macro. Je l'ai testé et elle fonctionne. Trop bien peut-être, car la macro me calcule toute la colonne C jusqu'à la ligne 65536 !
Est-il possible d'écrire cette macro sous forme de fonction personnalisée ?

Merci à tous
Lemailler

 

[Staple1600]

Re : Travailler sur les cellules visibles

Bonjour

Merci skoobi pour ta macro. Je l'ai testé et elle fonctionne. Trop bien peut-être, car la macro me calcule toute la colonne C jusqu'à la ligne 65536 !

Avec celle ligne à la place de l'existante
For Each cellule In Range(Range("c5"), Range("c65536").End(xlUp))

C'est mieux non?

edit: et avec

Sub Test()
Application.ScreenUpdating = False
' ici le code précédent
Application.ScreenUpdating = True
End sub

C'est plus rapide



A+

Staple
End sub



A+

Staple

 

[Lemailler]

Re : Travailler sur les cellules visibles

Bonjour Staple1600

Heuu ! non cela ne marche pas. Ma colonne C se remplit à partir de la 5ème ligne et vers le haut ???

Par contre avec :
Sub Test()
Application.ScreenUpdating = False
Application.ScreenUpdating = True
End sub

c'est très efficace sur la mouture de la macro qui traite les 65536 lignes. C'est net et sans bavures.
Cela ne serait pas plus simple avec une fonction personnalisée ?

Merci Staple1600
Lemailler

 

[Staple1600]

Re : Travailler sur les cellules visibles

Re


Chez moi la macro ci-dessous fonctionne

Sub test()
Dim cellule As Range, cellule1 As Range, cellule2 As Range, resultat As Range
Application.ScreenUpdating = False
For Each cellule In Range(Range("c5"), Range("c5").End(xlDown))
    If cellule.EntireRow.Hidden = False Then
        Set cellule1 = cellule.Offset(0, -1)
        For ligne = cellule1.Row To 5 Step -1
            If Cells(ligne - 1, cellule1.Column).EntireRow.Hidden = False Then
                Set cellule2 = Cells(ligne - 1, cellule1.Column)
                Exit For
            End If
        Next
        If Not cellule2 Is Nothing Then
            cellule.Value = cellule1 * cellule2
     Else: cellule.Value = 0
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub

Avec un filtre automatique sur la cellule C5

et des données en B6:C100

 

[skoobi]

Re : Travailler sur les cellules visibles

Re,
"Merci skoobi pour ta macro. Je l'ai testé et elle fonctionne. Trop bien peut-être, car la macro me calcule toute la colonne C jusqu'à la ligne 65536 !

celà ce produit quand tu filtre sur la valeure 1 non?

 

[Lemailler]

Re : Travailler sur les cellules visibles

Re staple, re skoobi,

Staple1600, avec un filtre automatique sur la cellule C5, les données en B6:C100 et le dernier code fourni, VBE se met en mode débogage et m'indique la ligne :
cellule.Value = cellule1 * cellule2

Skoobi, non même lorsque je lance la macro filtre inactif, elle trace sur les 65536 lignes de la colonne C.

Lemailler

 

[skoobi]

Re : Travailler sur les cellules visibles

Bizarre...

Le fichier joint marche.
Il doit y avoir une différence par rapport au tien.

A te relire.Ce lien n'existe plus

 

[Lemailler]

Re : Travailler sur les cellules visibles

Re skoobi !

Oui, ton fichier fonctionne. C'est lorsque je vide la collonne C que cela coince.

Merci
Lemailler