Discussion :

[78chris]

BOnjour

Cela laisse entendre un tableau à double entrée quasi inexploitable en TCD sauf à le prétraiter par PowerQuery.

Respecter la norme table de BD : un seul champ type et autant de lignes... donne un bon retour sur investissement

[patricktoulon]

bonjour 78chris
???????????????????
revois mon post c'est plus precis

[Pierre Fauconnier]

Je plussoie Chris, et j'étais en train de répondre lorsque j'ai vu passer sa réponse.

Si tu as besoin de cela, c'est que ton tableau n'est pas organisé en table de données. C'est ce que je disais lorsque je précisais ceci:

[...]
Cela dit, il faut bien considérer le tableau structuré comme une table et pas comme une plage classique améliorée. N'importe quelle plage classique n'a pas vocation à devenir un tableau structuré. Il faut qu'elle constitue un ensemble cohérent d'informations de même nature dont chaque ligne représente un enregistrement. En respectant cette finalité du tableau structuré, tu as rarement besoin de ce genre de manipulations. Associer deux colonnes dans un range pour en parcourir les cellules à peu d'intérêt dans une table de données, me semble-t-il.

En fait, lorsque le tableau structuré est bien organisé, les outils d'analyse d'Excel, en ce compris les PowerTruc, font des miracles et suffisent très souvent ...

Avec le tableau de gauche, tu vas compliquer ton analyse, alors que celui de droite permet d'utiliser facilement les outils d'Excel (fonctions .ENS, TCD, PowerTrucs...)


Truc pour déceler une structure problématique: Les intitulés de ton tableau ne peuvent pas être des "valeurs" métier (nom d'une matière, année, Nom d'une catégorie, ...)

[Pierre Fauconnier]

[...]
Pour les champs discontinus, il y a aussi cette écriture:[...]

Autre technique d'un UNION

[Pierre Fauconnier]

[...]
question6:
quelle est la solution la plus propre pour recupérer la plage visible dans un tableau structuré je dis bien la plus propre on a vu ca dans une discussion precedente mais autant la ramener ici
[...]

Sauf pour une masturbation intellectuelle, je n'ai jamais très bien vu l'utilité de la chose, perso. Dans l'autre discussion à laquelle tu fais référence, nous avons été deux, Ryu et moi, à demander l'utilité de trouver la énième ligne visible suite à un filtre automatique => zéro réponses actuellement.

[patricktoulon]

bonjour pierre

en cas de filtre sur le tableau il faut bien pouvoir recupérer les lignes le range visible cette question n'a rien a voir avec la question initiale de l'autre discussion
moi je veut tout le tableau filtré soit par le range soit par les listrow
d'ou ma question dans le sens ou je filtre une colonne les noms par exemple et que je veux examiner d'autre colonne apres filtre sur les ligne visibles
il faut bien y avoir acces rien a voir avec la pigniole mentale
ou meme ne serait ce que pour extraire les lignes visibles voir les exporter,deplacer,supprimer,modifier

[Pierre Fauconnier]

En théorie (=> mais erreur si aucune ligne visible dans le tableau)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("tableau2").SpecialCells(xlCellTypeVisible).Address

Avec un test sur le fait que tu as au moins une ligne de données affichée pour éviter l'erreur (ici sans connaître le nom d'une colonne, et donc en passant par le listobject)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub Test1()
  If Range("tableau1").ListObject.ListColumns(1).Range.SpecialCells(xlCellTypeVisible).Count > 1 Then
    Debug.Print Range("tableau1").SpecialCells(xlCellTypeVisible).Address
  Else
    MsgBox "aucune ligne visible dans le tableau"
  End If
End Sub

Ici en utilisant le nom d'une colonne et donc sans passer par le listobject

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub Test1()
  If Range("tableau2[[#All],[Date]]").SpecialCells(xlCellTypeVisible).Count > 1 Then
    Debug.Print Range("tableau1").SpecialCells(xlCellTypeVisible).Address
  Else
    MsgBox "aucune ligne visible dans le tableau"
  End If
End Sub

Travailler avec les listrows dans ce cas va t'obliger à une boucle sur tout le tableau.

Pour connaître le nombre de lignes visibles d'un tableau structuré, on peut utiliser ceci en passant par le listobject ( si on ne connaît pas le nom d'une colonne du tableau)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("tableau2").ListObject.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count

Ou ceci si on connait le nom d'une colonne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

range("tableau2[date]").SpecialCells(xlCellTypeVisible).Count

toujours, bien sûr, en ayant testé avant que l'on a au moins une ligne visible dans la zone de données du tableau...

[boisgontierjacques]

En PJ, 2 fonctions matricielles pour récupérer dans le tableur les lignes visibles.

-La BD a 10.000 lignes et 6 colonnes
-On filtre la moitié des enregistrements
-Le mode de calcul est manuel (pour tester le temps de maj des fonctions

La fonction qui récupère directement sur le tableau dynamique les valeurs est 2 fois plus lente que celle qui utilise un Array()

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Function LignesFiltrees(NomTableau)
  Application.Volatile
  Set f = Sheets(Range(NomTableau).Parent.Name)
  decal = Range(NomTableau).Row - 1
  TblE = Range(NomTableau).Value
  Ncol = Application.Caller.Columns.Count
  Dim TblS()
  For j = 1 To Range(NomTableau).Rows.Count
     ligne = decal + j
     If Not f.Rows(ligne).Hidden Then
        n = n + 1
        ReDim Preserve TblS(1 To Ncol, 1 To n)
        For k = 1 To Ncol: TblS(k, n) = TblE(j, k): Next k
      End If
    Next j
    ReDim Preserve TblS(1 To Ncol, 1 To Application.Caller.Rows.Count)
    LignesFiltrees = Application.Transpose(TblS)
End Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Function LignesFiltrees1(NomTableau)
 Application.Volatile
 Ncol = Application.Caller.Columns.Count
 Dim TblS()
 For Each lig In Range(NomTableau).ListObject.ListRows
     If Not lig.Range.EntireRow.Hidden Then
        n = n + 1: ReDim Preserve TblS(1 To Ncol, 1 To n)
        For k = 1 To Ncol: TblS(k, n) = lig.Range.Item(1, k): Next k
     End If
 Next lig
 ReDim Preserve TblS(1 To Ncol, 1 To Application.Caller.Rows.Count)
 LignesFiltrees1 = Application.Transpose(TblS)
End Function

Boisgontier

[patricktoulon]

re
Ok pierre
autrement dit en ce qui concerne l'exploitation du tableau structuré (filtré) tu conseille de travailler avec les outils range classique (specialcells et Cpgnie)

[Pierre Fauconnier]

re
Ok pierre
autrement dit en ce qui concerne l'exploitation du tableau structuré (filtré) tu conseille de travailler avec les outils range classique (specialcells et Cpgnie)

J'ai juste parlé de specialcells(xlcelltypevisible). Je ne vois rien dans le modèle ListObject qui permet de jouer avec les lignes visibles, et donc je ne vois que le specialcells appliqué sur des plages spécifiques éventuellement déterminées à l'aide du listobject ou d'un de ses objets fils (cfr la méthode pour calculer le nombre de lignes visibles qui travaille soit sur le databodyrange d'une colonne, soit sur sa référence structurée comme plage nommée (Je mets bien le plage nommée en italique puisque nous avons vu que les names du classeur ne reprenaient pas les tableaux structurés))

[patricktoulon]

re
@boisgontierjacques
on a un moyen de ne pas créer un tableau horizontal puis transposé

le nombre de ligne visible tu l'obtiens par les visibles D'UNE!! COLONNE

exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 Range("t_Contacts[nom]").SpecialCells(xlVisible).cells.count

du coup pas besoins de redim preserve (x,1 to y) (tablo transposé horizontal )et retransposer et tout le toutim

on fait un redim tout court pour créer le tableau ET DANS LE BON SENS car redim est effectué qu'une fois
donc les deux parametres (lignes/colonnes) peuve etre du genre redim tablo(x1 to x2,y1 to y2) voir meme redim tablo(x,y)
j'utilise la double pour etre en base 1 comme la plage


exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub testx()
    Dim plage As Range, tablo(), x&, i&, col
    Set plage = Range("t_Contacts[nom]").SpecialCells(xlVisible)
    nbrecol = Range("t_Contacts").Columns.Count
    ReDim tablo(1 To plage.Cells.Count, 1 To nbrecol)
    For Each Cel In plage.Cells
        x = Cel.Row - 1: i = i + 1:
        For col = 1 To UBound(tablo, 2)
            tablo(i, col) = Range("t_Contacts").Cells(x, col)
        Next
    Next
    Cells(200, 1).Resize(UBound(tablo), UBound(tablo, 2)) = tablo
End Sub

qu'en pense tu ?

edit:

en fonction ca donne ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub test()
    montableau = LignesFiltrees_en_tableau("t_Contacts", "Nom")
    Cells(20, 1).Resize(UBound(montableau), UBound(montableau, 2)) = montableau
End Sub
'
Function LignesFiltrees_en_tableau(NT, NC)
    Dim P As Range, TBL(), x&, i&, col1, CEL As Range
    Set P = Range(NT & "[" & NC & "]").SpecialCells(xlVisible)
    nbrecol = Range(NT).Columns.Count
    ReDim TBL(1 To P.Cells.Count, 1 To nbrecol)
    For Each CEL In P.Cells
        x = CEL.Row - 1: i = i + 1:
        For col = 1 To UBound(TBL, 2)
            TBL(i, col) = Range(NT).Cells(x, col)
        Next
    Next
    LignesFiltrees_en_tableau = TBL
End Function

[boisgontierjacques]

Dans une fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

NbFiltrees = Range("tableau1[nom]").SpecialCells(xlVisible).Count

renvoie le nombre de lignes du tableau et non pas le nombre de lignes filtrées

Boisgontier

[Pierre Fauconnier]

[...]

renvoie le nombre de lignes du tableau et non pas le nombre de lignes filtrées

Boisgontier

Dans une fonction utilisée dans une feuille de calcul, pas en VBA où la fonction renvoie la bonne valeur. Ca me semble important de le préciser.

En Excel, on a la fonction SOUS.TOTAL(2;...) qui renvoie la bonne valeur, et donc pas besoin de fonction perso pour réaliser cela.

On pourrait d'ailleurs utiliser cette fonction SOUS.TOTAL en VBA si on le souhaitait, et cela renvoie une bonne valeur également dans Excel.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Function NbVisibleRows(rng As Range) As Long
  NbVisibleRows = Application.Subtotal(2, rng)
End Function

[patricktoulon]

Dans une fonction

NbFiltrees =

renvoie le nombre de lignes du tableau et non pas le nombre de lignes filtrées

Boisgontier

ca n'est pas Range("tableau1[nom]").SpecialCells(xlVisible).Count mais Range("tableau1[nom]").SpecialCells(xlVisible).cells.Count
ainsi le nombre de cellule visibles dans cette colonne c'est le nombres de lignes visible dans le tableau du coup j'ai fait sauter un argument on injecte maintenant uniquement le non du tableau en prenant automatiquement la colonne (1)

on la simplifie encore

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub test()
    montableau = LignesFiltrees_en_tableau("t_Contacts")
    Cells(20, 1).Resize(UBound(montableau), UBound(montableau, 2)) = montableau
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Function LignesFiltrees_en_tableau(NT)
    Dim P As Range, TBL(), x&, i&, col&, CEL As Range
    Set P = Range(NT).ListObject.DataBodyRange.Columns(1).SpecialCells(xlVisible)
     ReDim TBL(1 To P.Cells.Count, 1 To Range(NT).Columns.Count)
    For Each CEL In P.Cells
        x = CEL.Row - 1: i = i + 1:
        For col = 1 To UBound(TBL, 2)
            TBL(i, col) = Range(NT).Cells(x, col)
        Next
    Next
    LignesFiltrees_en_tableau = TBL
End Function

ca fonctionne nikel

[boisgontierjacques]

Je parle d'une fonction appelée du tableur.

Voir la PJ avec ta fonction.
La BD est filtrée mais dans le tableur, on a toutes les lignes (ce qui n'est pas le cas au post #28).

Boisgontier

[Pierre Fauconnier]

Je parle d'une fonction appelée du tableur.

Voir la PJ avec ta fonction.
La BD est filtrée mais dans le tableur, on a toutes les lignes.

Boisgontier

Moi aussi. SOUS.TOTAL fait très bien le job pour compter les lignes, soit directement dans Excel, soit au travers d'une fonction VBA (éventuellement utilisée en Excel, quoique je n'en vois pas l'intérêt). Si tes messages étaient moins sibyllins, ils prêteraient moins à confusion

Pour ce qui est du .specialcells(xlcelltypevisible) qui ne renvoie pas la bonne valeur dans une fonction appelée du tableur, ça a été traité en long et en large dans cette discussion, je propose qu'on ne recommence pas ici.

Cela dit, ça nous éloigne du sujet initial de la discussion qui porte sur des questions relatives aux tableaux structurés, or nous avons vu que les specialcells ne se comportaient pas différemment sur un tableau structuré que sur une plage classique. Si on n'est pas un minimum attentifs à rester dans le sujet, ça va encore partir en cacahuètes.

[patricktoulon]

@boisgontierjacques

avec ton fichier j'ai "vrai" dans toute les cellules

j'ai bien fait comme tu dis dans l'info bule a droite pourtant


ET EN PLUS IL PLOMBE EXCEL tourne tourne petit moulin
j'ai meme eu du mal a l'arrétter avec le calculate il me re re re autant que de cellule 3600 X 9 non j'ai shunté dans les taches obligé

[Pierre Fauconnier]

Patrick, vous parlez de quoi en fait?

Sujet de la discussion: diverses questions sur les tableaux structurés (rien à voir avec les lignes visibles d'un filtre, comme déjà souligné)
Fichier de Jacques: 0 tableaux structurés

On ne va pas s'y retrouver. Si tu veux résoudre un problème précis de lignes filtrées, ouvre une discussion spécifique pour cela stp

[patricktoulon]

re
@Pierre Fauconnier
dans ce post on enumere les possibilités que j'ai de manipuler un TS en apprenant les outils a dispo il est bien dit diverses questions

les filtre en font partie cela dit cette question est réglé j'ai ma petite fonction qui tourne plutot bien et trtansforme meme en variable tableau ligne jointe (sans les lignes invisibles)

je repondais simplement a boisgontierjacques concernant l'utilisation de la fonction en formule quand au fait que la sienne fonctionne en formule et la mienne non
perso je n'en vois pas trop la raison car un tableau reste un tableau ( je parle des variables) d'autant plus que la sienne ne fonctionne pas non plus chez moi

on ouvrira une discussion a ce propos ca peut m'interésser effectivement

reste encore un petit truc c'est d'adapter ta fonction getrow a une valeur une colonne

je fait le recap et je deballe

[Pierre Fauconnier]

[...]

reste encore un petit truc c'est d'adapter ta fonction getrow a une valeur une colonne[...]

Que veux-tu dire par là?