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
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
bonjour 78chris
???????????????????
revois mon post c'est plus precis
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:
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, ...)
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.
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
En théorie (=> mais erreur si aucune ligne visible dans le tableau)
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 range("tableau2").SpecialCells(xlCellTypeVisible).Address
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("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
Travailler avec les listrows dans ce cas va t'obliger à une boucle sur tout le tableau.
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
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)
Ou ceci si on connait le nom d'une colonne
Code : Sélectionner tout - Visualiser dans une fenêtre à part range("tableau2").ListObject.ListColumns(1).DataBodyRange.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...
Code : Sélectionner tout - Visualiser dans une fenêtre à part range("tableau2[date]").SpecialCells(xlCellTypeVisible).Count
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
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))
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 :
qu'en pense tu ?
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
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
Dans une fonction
renvoie le nombre de lignes du tableau et non pas le nombre de lignes filtrées
Code : Sélectionner tout - Visualiser dans une fenêtre à part NbFiltrees = Range("tableau1[nom]").SpecialCells(xlVisible).Count
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
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 Subca fonctionne nikel
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
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
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.
@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é
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
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
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager