Je reçois un tcd, je désire redistribuer ce tcd en base de données
Ex:
Bouteille lait 5
vin 6
eau 10
Flacon lait 5
vin 6
eau 10
en
Bouteille lait 5
Bouteille vin 6
Bouteille eau 10
Flacon lait 5
Flacon vin 6
Flacon eau 10
Double cliquer sur le total général (dernière cellule en bas à droite, s'il y a une totalisation) revient à générer une feuille détail qui contient ce que tu souhaites.
Frédéric Constant,
Ajouté ou modifié le 30/12/2006 (N°1794)
Ajouté ou modifié le 30/12/2006 (N°1794)
Comment gérer les changements de chemin ou de nom des sources externes (en
l'occurence une base access) d'un tableau croisé dynamique. A chaque fois il
conserve le chemin d'origine
1. Remettre le .mdb à sa place d'origine et laisser une copie au nouvel emplacement.
2.ouvrir excel, clic droit sur le tcd puis assistant, précédent, charger
les données, annuler, oui. On arrive dans microsoft query. Clic sur
l'icone SQL et là on peut changer le chemin directement (from ...) pour
mettre le nouveau...
2.ouvrir excel, clic droit sur le tcd puis assistant, précédent, charger
les données, annuler, oui. On arrive dans microsoft query. Clic sur
l'icone SQL et là on peut changer le chemin directement (from ...) pour
mettre le nouveau...
David,
Ajouté ou modifié le 30/05/2004 (N°1434)
Ajouté ou modifié le 30/05/2004 (N°1434)
J'ai créé un tableau croisé dynamique, qui va chercher ses renseignement dans un fichier Fox-pro (a l'aide d'un lien ODBC). J'aimerais rediriger ma requête vers un autre fichier source, est-ce possible ou dois-je recréer une requête?
Attention à ne pas mélanger 2 choses :
A) La source des données (définir)
B) L'Utilisation d'une source de données dans une requête spécifique pour un objet
« QueryTable» ou « PivotCache»
**********LA SOURCE DES DONNÉES**************
Dans le panneau de configuration de ton ordinateur, après un clic sur l'icône « Sources
de données (ODBC) », une fenêtre s'ouvre et à partir de celle-ci, tu peux définir 3 types
de sources de données.
Elles peuvent être définies aussi directement à partir d'Excel
(barre de menu/données/ données externes /créer une requête -
sans utiliser le panneau de configuration)
A) Sources de données utilisateur
B) Sources de données Fichier
C ) Source de données système
La seule différence entre A et B, c'est l'endroit où les informations reliées à L'ODBC
sont conservées. Pour une source
de données « Utilisateur », c'est la base de registre de l'ordinateur local qui joue le
rôle de
fichier DSN... c'est pourquoi cette source de données créée sera disponible seulement à
partir
de cet ordinateur puisque l'information est liée à la base de registre de ce dernier.
L'utilisation
d'un Fichier DSN permet une plus grande flexibilité et disponibilité au niveau d'un
réseau....
Pour ce qui est d'une source de données système, elle s'adresse à une base de données
sécurisée comme les fichiers de type MDW pour l'accès avec mot de passe et avec une autorisation particulière pour un usager ou pour un groupe d'usagers...
Que la source de données soit enregistrée dans la base de registre ou dans un fichier DSN,
elle contient essentiellement ce type d'informations :
1) définition du fichier source des données (chemin et ledit fichier)
2) pilote associé au type de fichier de ta source de données.
Le contenu d'un fichier DSN ressemble à ceci ... c'est le même type d'informations qui est
enregistré dans la base de registre lorsqu'il n'y a pas de fichier DSN.
'---------------
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admis
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Accès
DriverId=25
DefaultDir=C:\ODBC\
DBQ=C : \ODBC\\Comptoir.mdb
» -- -- ------ ------ ------
Si tu voulais, tu pourrais facilement le créer par programmation puisqu'il ne s'agit que
d'un vulgaire fichier texte éditable avec par exemple NotePad.exe ...
Évidemment, les éléments de ce fichier peuvent être modifiés le cas échéant sans aucun
problème...
Avantage de ce fichier dsn : il peut être facilement copié sur un serveur réseau
(ordinateur)
et être ainsi disponible à plusieurs utilisateurs qui à leur tour pourront définir
localement
des requêtes. Ils n'auront pas besoin de réinventer la roue chaque fois!
De plus, si une modification doit être effectuée comme le changement du chemin du fichier
source, il est possible d'éditer et de modifier directement le fichier DSN. Toutes les
NOUVELLES requêtes en tiendront compte.
**UTILISER LA SOURCE DES DONNÉES - DANS UN QUERYTABLE**
Lorsque l'on crée un « QueryTable» dans un classeur, il faut se rappeler que ce
« QueryTable» s'enregistre dans le fichier Excel au moment de la sauvegarde. Il y a deux
« Propriétés essentielles » à un objet « ObjetQuery» :
1 ) Connexion : C'est une chaîne de caractères chargée d'établir (ouvrir) la connexion
entre
la base des données à l'aide du pilote que l'on a choisi (Access, Oracle, Excel, Sql ...)
lors de
l'élaboration du QueryTable.
2 ) CommandText : C'est en fait une chaîne de caractères représentant la requête que l'on
a créée lorsque la fenêtre de Query était ouverte.
Lorsque le classeur possède des TableQuery déjà enregistrés et que l'on décide de
modifier
le nom du Classeur Source des données, il faut obligatoirement modifier les 2 chaînes de
texte nommées précédemment pour que la TableQuery retrouve sa fonctionnalité. Et comme
des données ne sont pas visibles dans une cellule, on peut les retrouver à l'aide d'une
procédure.
Voici une macro qui devrait faire le travail pour tous les "QueryTable" contenus dans un
classeur.
Évidemment, cela suppose que les données proviennent de la même base de données source.
Cette astuce est réservée aux utilisateurs Excel2000 car la propriété CommandTexte est une nouveauté de celle-ci.
-------------------------------------------
Sub Query_Et_NomFichierModifie()
Dim OldName As String, NewName As String
Dim Sh As Worksheet, Qt As QueryTable
' À saisir manuellement si nécessaire...
OldName = ThisWorkbook.FullName
NewName = "D\Toto\MonFichierQuery.xls"
For Each Sh In Worksheets
For Each Qt In Sh.QueryTables
If InStr(Qt.Connection, OldName) > 0 Then
Qt.Connection = Replace(Qt.Connection, _
OldName, NewName)
Qt.CommandText = Replace(Qt.CommandText, _
Left(OldName, Len(OldName) - 4), _
Left(NewName, Len(NewName) - 3))
Qt.Refresh False
End If
Next
Next
'Sauvegarde du fichier
ThisWorkbook.Save
Set Sh = Nothing: Set Qt = Nothing
End Sub
'-------------------------------------------
Si tu veux voir dans une cellule la chaîne de "Connexion" et la chaîne de "CommandText"
d'un objet Query dans une feuille nommée "Toto" dans mon exemple :
With Worksheets("Feuil1") 'Nom Feuille à redéfinir
.Range("A1") = Worksheets("Toto").QueryTables(1).Connection
.Range("A5") = Worksheets("Toto").QueryTables(1).CommandText
End With
Une chaîne de connexion ressemble à ceci : (une seule ligne)
ODBC;DSN=MS Access Database;DBQ=c:\Mes documents\Comptoir.mdb;
DefaultDir=C:\Mesdocuments\;DriverId=281;Exclusive=1;
FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;ReadOnly=0;UID=admin;
Une Chaîne de "CommandText" ressemble à ceci :
SELECT Fournisseurs.Société, Fournisseurs.Adresse, Fournisseurs.Ville,
Fournisseurs.Pays FROM `C:\ODBC\bernard 2\Comptoir`.Fournisseurs Fournisseurs
Observe dans les 2 cas, on retrouve le chemin complet du fichier source de la requête.
Si l'on doit changer son nom, il est normal que la mise à jour du querytable n'arrive pas
à
retrouver le fichier contenant les données sources.
La procédure donnée dans ce message n'a que pour but que de substituer la partie de la
chaîne
qui a été modifiée. C'est le même principe que de substituer une section d'une chaîne de
caractères dans une cellule avec une formule.
Que ton ODBC soit utilisé dans le cas d'un "QueryTable" ou d'un "PivotCache" (.(cube
Olap),
c'est exactement le même principe...ils possèdent tous les 2 les propriétés "Connexion"
et
"CommandText" sauf que l'objet auquel se réfère ces propriétés est différent..
Voici une procédure qui s'applique soit à un Pivotcache ou à un QueryTable pour
retrouver
les chaînes de ces 2 propriétés :
Si ton objet est un QueryTable :
Tu dois adapter le nom des feuilles et l'index de ton querytable. Si tu fais un clic droit
dans
une cellule dans la plage du résultat de ton querytable, et que tu choisis dans le menu
contextuel "Propriété", tu vas pouvoir visualiser le nom complet du querytable. Tu peux
l'utiliser en lieu et place de l'index dans le code.
'------------------------
Sub UnQueryTable()
With Worksheets("Feuil2")
.Range("A1") = Worksheets("Feuil1").QueryTables(1).Connection
.Range("A5") = Worksheets("Feuil1").QueryTables(1).CommandText
End With
End Sub
'------------------------
Si c'est un PivotCache :
Pour obtenir le nom du tableau croisé dynamique, tu peux cliquer sur le bouton "Tableau
croisé
dynamique" de la barre d'outils du même nom, et choisir dans le sous-menu :: "Option de la
table..." Le nom complet peut être utilisé à la place de l'index pour identifier
correctement le
"PivotCaches" dans la procédure.
'------------------------
Sub UnPivotCache()
With Worksheets("Feuil2")
.Range("A1") = ThisWorkbook.PivotCaches(1).Connection
.Range("A5") = ThisWorkbook.PivotCaches(1).CommandText
End With
End Sub
'------------------------
A )
Après leur création, les objets "QueryTable" ou "PivotCache" sont totalement indépendants du type de sources de données utilisées. Les sources de données seront utiles pour les
nouveaux QueryTable ou PivotCache ... mais ils sont totalement inutiles pour les queryTable
ou PivotCache déjà définis.
Toute l'information dont a besoin un "QueryTable" ou "PivotCache" est enregistré dans le
classeur au moment de la sauvegarde. Les modifications faites à un fichier DSN ou
directement dans la base de registre ne modifieront pas la donne relative à ces objets.
B )
Il faut faire attention avec la procédure visant à modifier les chaînes de "Connexion" et
"CommandText" d'un "QueryTable" ou d'un "PivotCache". C'est une présentation générale
de ce que pourrait être une procédure type. Dans le détail, vous aurez sûrement remarqué
que dans la chaîne de connection, on utilise le nom du fichier et son EXTENSION comme
ceci : DBQ=c:\Mes documents\Comptoir.mdb, l'extension du fichier dans la chaîne
"CommandText" n'est pas inscrite dans la chaîne de texte :
" FROM `C:\ODBC\bernard 2\Comptoir`.Fournisseurs Fournisseurs "
La procédure suggérée ne fait pas ce type de distinction... faudrait voir à l'adapter.
Pour ceux qui auraient tendance à trouver cela difficile, on peut simplifier le processus :
A ) Éditer la chaîne actuelle de connection dans la Cellule A1
B ) Éditer la chaîne actuelle de CommandText dans la Cellule A2
C ) Modifier parcimonieusement les informations concernant le changement de nom du
fichier source et son nouveau chemin
le cas échéant directement dans les cellules A1 et A2.
D ) Réaffecter aux propriétés "Connection" et "CommandText" , les chaînes de texte
modifiées.
Si on peut faire cela pour extraire les chaînes de connection et de CommandText :
With Worksheets("Feuil1") 'Nom Feuille à redéfinir
.Range("A1") = Worksheets("Toto").QueryTables(1).Connection
.Range("A2") = Worksheets("Toto").QueryTables(1).CommandText
End With
Après avoir modifié manuellement les chaînes de caractères on peut faire ceci :
With Worksheets("Feuil1") 'Nom Feuille à redéfinir
Worksheets("Toto").QueryTables(1).Connection = .Range("A1")
Worksheets("Toto").QueryTables(1).CommandText =.Range("A2")
.QueryTables(1).Refresh False
End With
Les modifications seront conservées après l'enregistrement du fichier.
A) La source des données (définir)
B) L'Utilisation d'une source de données dans une requête spécifique pour un objet
« QueryTable» ou « PivotCache»
**********LA SOURCE DES DONNÉES**************
Dans le panneau de configuration de ton ordinateur, après un clic sur l'icône « Sources
de données (ODBC) », une fenêtre s'ouvre et à partir de celle-ci, tu peux définir 3 types
de sources de données.
Elles peuvent être définies aussi directement à partir d'Excel
(barre de menu/données/ données externes /créer une requête -
sans utiliser le panneau de configuration)
A) Sources de données utilisateur
B) Sources de données Fichier
C ) Source de données système
La seule différence entre A et B, c'est l'endroit où les informations reliées à L'ODBC
sont conservées. Pour une source
de données « Utilisateur », c'est la base de registre de l'ordinateur local qui joue le
rôle de
fichier DSN... c'est pourquoi cette source de données créée sera disponible seulement à
partir
de cet ordinateur puisque l'information est liée à la base de registre de ce dernier.
L'utilisation
d'un Fichier DSN permet une plus grande flexibilité et disponibilité au niveau d'un
réseau....
Pour ce qui est d'une source de données système, elle s'adresse à une base de données
sécurisée comme les fichiers de type MDW pour l'accès avec mot de passe et avec une autorisation particulière pour un usager ou pour un groupe d'usagers...
Que la source de données soit enregistrée dans la base de registre ou dans un fichier DSN,
elle contient essentiellement ce type d'informations :
1) définition du fichier source des données (chemin et ledit fichier)
2) pilote associé au type de fichier de ta source de données.
Le contenu d'un fichier DSN ressemble à ceci ... c'est le même type d'informations qui est
enregistré dans la base de registre lorsqu'il n'y a pas de fichier DSN.
'---------------
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admis
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Accès
DriverId=25
DefaultDir=C:\ODBC\
DBQ=C : \ODBC\\Comptoir.mdb
» -- -- ------ ------ ------
Si tu voulais, tu pourrais facilement le créer par programmation puisqu'il ne s'agit que
d'un vulgaire fichier texte éditable avec par exemple NotePad.exe ...
Évidemment, les éléments de ce fichier peuvent être modifiés le cas échéant sans aucun
problème...
Avantage de ce fichier dsn : il peut être facilement copié sur un serveur réseau
(ordinateur)
et être ainsi disponible à plusieurs utilisateurs qui à leur tour pourront définir
localement
des requêtes. Ils n'auront pas besoin de réinventer la roue chaque fois!
De plus, si une modification doit être effectuée comme le changement du chemin du fichier
source, il est possible d'éditer et de modifier directement le fichier DSN. Toutes les
NOUVELLES requêtes en tiendront compte.
**UTILISER LA SOURCE DES DONNÉES - DANS UN QUERYTABLE**
Lorsque l'on crée un « QueryTable» dans un classeur, il faut se rappeler que ce
« QueryTable» s'enregistre dans le fichier Excel au moment de la sauvegarde. Il y a deux
« Propriétés essentielles » à un objet « ObjetQuery» :
1 ) Connexion : C'est une chaîne de caractères chargée d'établir (ouvrir) la connexion
entre
la base des données à l'aide du pilote que l'on a choisi (Access, Oracle, Excel, Sql ...)
lors de
l'élaboration du QueryTable.
2 ) CommandText : C'est en fait une chaîne de caractères représentant la requête que l'on
a créée lorsque la fenêtre de Query était ouverte.
Lorsque le classeur possède des TableQuery déjà enregistrés et que l'on décide de
modifier
le nom du Classeur Source des données, il faut obligatoirement modifier les 2 chaînes de
texte nommées précédemment pour que la TableQuery retrouve sa fonctionnalité. Et comme
des données ne sont pas visibles dans une cellule, on peut les retrouver à l'aide d'une
procédure.
Voici une macro qui devrait faire le travail pour tous les "QueryTable" contenus dans un
classeur.
Évidemment, cela suppose que les données proviennent de la même base de données source.
Cette astuce est réservée aux utilisateurs Excel2000 car la propriété CommandTexte est une nouveauté de celle-ci.
-------------------------------------------
Sub Query_Et_NomFichierModifie()
Dim OldName As String, NewName As String
Dim Sh As Worksheet, Qt As QueryTable
' À saisir manuellement si nécessaire...
OldName = ThisWorkbook.FullName
NewName = "D\Toto\MonFichierQuery.xls"
For Each Sh In Worksheets
For Each Qt In Sh.QueryTables
If InStr(Qt.Connection, OldName) > 0 Then
Qt.Connection = Replace(Qt.Connection, _
OldName, NewName)
Qt.CommandText = Replace(Qt.CommandText, _
Left(OldName, Len(OldName) - 4), _
Left(NewName, Len(NewName) - 3))
Qt.Refresh False
End If
Next
Next
'Sauvegarde du fichier
ThisWorkbook.Save
Set Sh = Nothing: Set Qt = Nothing
End Sub
'-------------------------------------------
Si tu veux voir dans une cellule la chaîne de "Connexion" et la chaîne de "CommandText"
d'un objet Query dans une feuille nommée "Toto" dans mon exemple :
With Worksheets("Feuil1") 'Nom Feuille à redéfinir
.Range("A1") = Worksheets("Toto").QueryTables(1).Connection
.Range("A5") = Worksheets("Toto").QueryTables(1).CommandText
End With
Une chaîne de connexion ressemble à ceci : (une seule ligne)
ODBC;DSN=MS Access Database;DBQ=c:\Mes documents\Comptoir.mdb;
DefaultDir=C:\Mesdocuments\;DriverId=281;Exclusive=1;
FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;ReadOnly=0;UID=admin;
Une Chaîne de "CommandText" ressemble à ceci :
SELECT Fournisseurs.Société, Fournisseurs.Adresse, Fournisseurs.Ville,
Fournisseurs.Pays FROM `C:\ODBC\bernard 2\Comptoir`.Fournisseurs Fournisseurs
Observe dans les 2 cas, on retrouve le chemin complet du fichier source de la requête.
Si l'on doit changer son nom, il est normal que la mise à jour du querytable n'arrive pas
à
retrouver le fichier contenant les données sources.
La procédure donnée dans ce message n'a que pour but que de substituer la partie de la
chaîne
qui a été modifiée. C'est le même principe que de substituer une section d'une chaîne de
caractères dans une cellule avec une formule.
Que ton ODBC soit utilisé dans le cas d'un "QueryTable" ou d'un "PivotCache" (.(cube
Olap),
c'est exactement le même principe...ils possèdent tous les 2 les propriétés "Connexion"
et
"CommandText" sauf que l'objet auquel se réfère ces propriétés est différent..
Voici une procédure qui s'applique soit à un Pivotcache ou à un QueryTable pour
retrouver
les chaînes de ces 2 propriétés :
Si ton objet est un QueryTable :
Tu dois adapter le nom des feuilles et l'index de ton querytable. Si tu fais un clic droit
dans
une cellule dans la plage du résultat de ton querytable, et que tu choisis dans le menu
contextuel "Propriété", tu vas pouvoir visualiser le nom complet du querytable. Tu peux
l'utiliser en lieu et place de l'index dans le code.
'------------------------
Sub UnQueryTable()
With Worksheets("Feuil2")
.Range("A1") = Worksheets("Feuil1").QueryTables(1).Connection
.Range("A5") = Worksheets("Feuil1").QueryTables(1).CommandText
End With
End Sub
'------------------------
Si c'est un PivotCache :
Pour obtenir le nom du tableau croisé dynamique, tu peux cliquer sur le bouton "Tableau
croisé
dynamique" de la barre d'outils du même nom, et choisir dans le sous-menu :: "Option de la
table..." Le nom complet peut être utilisé à la place de l'index pour identifier
correctement le
"PivotCaches" dans la procédure.
'------------------------
Sub UnPivotCache()
With Worksheets("Feuil2")
.Range("A1") = ThisWorkbook.PivotCaches(1).Connection
.Range("A5") = ThisWorkbook.PivotCaches(1).CommandText
End With
End Sub
'------------------------
A )
Après leur création, les objets "QueryTable" ou "PivotCache" sont totalement indépendants du type de sources de données utilisées. Les sources de données seront utiles pour les
nouveaux QueryTable ou PivotCache ... mais ils sont totalement inutiles pour les queryTable
ou PivotCache déjà définis.
Toute l'information dont a besoin un "QueryTable" ou "PivotCache" est enregistré dans le
classeur au moment de la sauvegarde. Les modifications faites à un fichier DSN ou
directement dans la base de registre ne modifieront pas la donne relative à ces objets.
B )
Il faut faire attention avec la procédure visant à modifier les chaînes de "Connexion" et
"CommandText" d'un "QueryTable" ou d'un "PivotCache". C'est une présentation générale
de ce que pourrait être une procédure type. Dans le détail, vous aurez sûrement remarqué
que dans la chaîne de connection, on utilise le nom du fichier et son EXTENSION comme
ceci : DBQ=c:\Mes documents\Comptoir.mdb, l'extension du fichier dans la chaîne
"CommandText" n'est pas inscrite dans la chaîne de texte :
" FROM `C:\ODBC\bernard 2\Comptoir`.Fournisseurs Fournisseurs "
La procédure suggérée ne fait pas ce type de distinction... faudrait voir à l'adapter.
Pour ceux qui auraient tendance à trouver cela difficile, on peut simplifier le processus :
A ) Éditer la chaîne actuelle de connection dans la Cellule A1
B ) Éditer la chaîne actuelle de CommandText dans la Cellule A2
C ) Modifier parcimonieusement les informations concernant le changement de nom du
fichier source et son nouveau chemin
le cas échéant directement dans les cellules A1 et A2.
D ) Réaffecter aux propriétés "Connection" et "CommandText" , les chaînes de texte
modifiées.
Si on peut faire cela pour extraire les chaînes de connection et de CommandText :
With Worksheets("Feuil1") 'Nom Feuille à redéfinir
.Range("A1") = Worksheets("Toto").QueryTables(1).Connection
.Range("A2") = Worksheets("Toto").QueryTables(1).CommandText
End With
Après avoir modifié manuellement les chaînes de caractères on peut faire ceci :
With Worksheets("Feuil1") 'Nom Feuille à redéfinir
Worksheets("Toto").QueryTables(1).Connection = .Range("A1")
Worksheets("Toto").QueryTables(1).CommandText =.Range("A2")
.QueryTables(1).Refresh False
End With
Les modifications seront conservées après l'enregistrement du fichier.
Denis Michon,
Ajouté ou modifié le 30/05/2004 (N°1431)
Ajouté ou modifié le 30/05/2004 (N°1431)
J'ai crée un TCD avec en ligne les noms en colonne les mois et comme résultat le nombre
d'heures. Lorsque que dans ma base je supprime toutes les lignes ayant le même NOM et que
j'actualise le TCD, le NOM n'apparaît plus, mais par contre il reste toujours en mémoire dans
la liste déroulante NOM du TCD.
Pour tenir compte des champs calculés ne pas oublier de rafraîchir le TCD avant de savoir s'il
faut détruire.
'
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
et pour excel 2002 uniquement :
'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
End Sub
'=============================
faut détruire.
'
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
et pour excel 2002 uniquement :
'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
End Sub
'=============================
Debra Dalgleish,
Ajouté ou modifié le 25/10/2003 (N°1324)
Ajouté ou modifié le 25/10/2003 (N°1324)
Je voudrais récuperer par VBA les valeurs visbles d'un TCD et seulement celles là.
Utilise ceci dans ton code VBA
With Sheets("TCD").PivotTables("MonTCD").PivotFields("NOM").DataRange
Sheets("TCD").Cells(2, c).Resize(.Count) = .Value
End With
With Sheets("TCD").PivotTables("MonTCD").PivotFields("NOM").DataRange
Sheets("TCD").Cells(2, c).Resize(.Count) = .Value
End With
Laurent Longre, (N°1323)
Comment créer un TCD en VBA ?
Pour créer un tableau croisé dyn, utilise
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="Feuil1!R1C1:R4C2", _
TableDestination:=Range("A5"), _
TableName:="MonTCD"
Tu mets ce que tu dois dans
SourceData (la plage de tes données)
TableDestination (l'endroit où tu veux que le tableau soit collé)
TableName (le nom de ton tableau).
Puis tu ajoutes tes champs
ActiveSheet.PivotTables("MonTCD").AddFields RowFields:="N"
ActiveSheet.PivotTables("MonTCD").PivotFields("P").Orientation =
xlDataField
(champs "P" et "N" dans mon exemple qui doivent correspondre aux tritre de
tes colonnes)
Le + simple est d'enregistrer la création avec l'enregisteur puis de
décortiquer le code en apportant les modifs que tu souhaites (changer le
nom, la source, la destination,...)
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="Feuil1!R1C1:R4C2", _
TableDestination:=Range("A5"), _
TableName:="MonTCD"
Tu mets ce que tu dois dans
SourceData (la plage de tes données)
TableDestination (l'endroit où tu veux que le tableau soit collé)
TableName (le nom de ton tableau).
Puis tu ajoutes tes champs
ActiveSheet.PivotTables("MonTCD").AddFields RowFields:="N"
ActiveSheet.PivotTables("MonTCD").PivotFields("P").Orientation =
xlDataField
(champs "P" et "N" dans mon exemple qui doivent correspondre aux tritre de
tes colonnes)
Le + simple est d'enregistrer la création avec l'enregisteur puis de
décortiquer le code en apportant les modifs que tu souhaites (changer le
nom, la source, la destination,...)
Iznogood, (N°1322)
Je cherche à faire un TCD mais sans calcul ! j'ai un grand tableau avec des noms en colonne A,
des questions en colonne B et les réponses en colonne C. Je voudrais voir apparaître les
réponses aux intersections entre les lignes et les colonnes.
Tu peux essayer cette macro (lancer la procédure ListeVersTableau).
Cette procédure construit sur une nouvelle feuille un tableau à partir
de la plage sélectionnée (3 colonnes). Les colonnes de cette plage
servent à fabriquer le tableau de la manière suivante:
Colonne de gauche => champ "ligne" du tableau
Colonne centrale => champ "colonne" du tableau
Colonne de droite => contenu du tableau (intersections lignes-colonnes)
'==========================================================
Dim Arr, Idx() As Long
Dim Elt, IdxTemp As Long
Dim I As Long, NElts As Long
Sub ListeVersTableau()
Dim Plage As Range
Dim ArrC, ArrL, ArrElt
Dim ArrCF, ArrLF, ArrEltF
Dim Lignes As Long, Cols As Long
On Error Resume Next
Do
Set Plage = Application.InputBox("Sélectionner la plage de " & _
"données (sans les en-têtes) à transformer en tableau.", Type:=8)
If Plage Is Nothing Then Exit Sub
If Plage.Columns.Count = 3 Then Exit Do
If MsgBox("La plage doit comporter 3 colonnes.", _
vbInformation + vbOKCancel) = vbCancel Then Exit Sub
Loop
On Error GoTo 0
ArrC = Plage.Columns(1)
ArrL = Plage.Columns(2)
NElts = UBound(ArrC)
Tri ArrC, ArrCF
Tri ArrL, ArrLF
ArrElt = Plage.Columns(3)
Lignes = UBound(ArrCF)
Cols = UBound(ArrLF)
ReDim ArrEltF(1 To Lignes, 1 To Cols)
With Application
For I = 1 To NElts
ArrEltF(.Match(ArrC(I, 1), ArrCF), _
.Match(ArrL(I, 1), ArrLF)) = ArrElt(I, 1)
Next I
End With
Application.ScreenUpdating = False
Worksheets.Add After:=Plage.Worksheet
With [A2].Resize(Lignes)
.Value = Application.Transpose(ArrCF)
.Font.Bold = True
End With
With [B1].Resize(, Cols)
.Value = ArrLF
.Font.Bold = True
End With
[B2].Resize(Lignes, Cols) = ArrEltF
With [B2].CurrentRegion
.EntireColumn.AutoFit
.Select
End With
End Sub
Private Sub Tri(NonTrié, Trié)
Dim J As Integer
ReDim Idx(1 To NElts)
For I = 1 To NElts
Idx(I) = I
Next I
Arr = NonTrié
Recurse 1, NElts
ReDim Trié(1 To NElts)
Trié(1) = Arr(Idx(1), 1)
J = 1
For I = 2 To NElts
If Arr(Idx(I), 1) <> Arr(Idx(I - 1), 1) Then
J = J + 1
Trié(J) = Arr(Idx(I), 1)
End If
Next I
Erase Arr
ReDim Preserve Trié(1 To J)
End Sub
Private Sub Recurse(ByVal B1 As Long, ByVal H1 As Long)
Dim B2 As Long
Dim H2 As Long
B2 = B1
H2 = H1
Elt = Arr(Idx((B1 + H1) \ 2), 1)
Do While B2 < H2
Do While B2 < H1 And Arr(Idx(B2), 1) < Elt
B2 = B2 + 1
Loop
Do While H2 > B1 And Arr(Idx(H2), 1) > Elt
H2 = H2 - 1
Loop
If B2 < H2 Then
IdxTemp = Idx(B2)
Idx(B2) = Idx(H2)
Idx(H2) = IdxTemp
End If
If B2 <= H2 Then
B2 = B2 + 1
H2 = H2 - 1
End If
Loop
If H2 > B1 Then Recurse B1, H2
If B2 < H1 Then Recurse B2, H1
End Sub
Et pour faire l'inverse ? C'est à dire reconstruire une liste en 3 colonnes à partir d'un
tableau style TCD :
Essaie cette macro. Elle suppose que ton tableau commence dans la cellule A1 de la feuille
active et que la 1ere colonne ait un en-tête ("Article" dans la cellule A1).
Sub TableauEnListe()
Dim TCD As PivotTable, P As Range
Dim RField, DFields, DField
Application.ScreenUpdating = False
RField = [A1]
DFields = Range("B1", [B1].End(xlToRight))
Set TCD = ThisWorkbook.PivotCaches.Add(xlDatabase, [A1]. _
CurrentRegion.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable("")
TCD.AddFields RField
For Each DField In DFields
TCD.PivotFields(DField).Orientation = xlDataField
Next DField
TCD.ColumnGrand = False
With TCD.TableRange2
.Copy
.PasteSpecial xlPasteValues
.ClearFormats
For Each P In .Columns(1).SpecialCells(xlCellTypeBlanks).Areas
P = P(0)
Next P
.Columns(2).Replace "Nom ", ""
End With
[B1:C1] = Array("Question", "Réponse")
End Sub
Cette procédure construit sur une nouvelle feuille un tableau à partir
de la plage sélectionnée (3 colonnes). Les colonnes de cette plage
servent à fabriquer le tableau de la manière suivante:
Colonne de gauche => champ "ligne" du tableau
Colonne centrale => champ "colonne" du tableau
Colonne de droite => contenu du tableau (intersections lignes-colonnes)
'==========================================================
Dim Arr, Idx() As Long
Dim Elt, IdxTemp As Long
Dim I As Long, NElts As Long
Sub ListeVersTableau()
Dim Plage As Range
Dim ArrC, ArrL, ArrElt
Dim ArrCF, ArrLF, ArrEltF
Dim Lignes As Long, Cols As Long
On Error Resume Next
Do
Set Plage = Application.InputBox("Sélectionner la plage de " & _
"données (sans les en-têtes) à transformer en tableau.", Type:=8)
If Plage Is Nothing Then Exit Sub
If Plage.Columns.Count = 3 Then Exit Do
If MsgBox("La plage doit comporter 3 colonnes.", _
vbInformation + vbOKCancel) = vbCancel Then Exit Sub
Loop
On Error GoTo 0
ArrC = Plage.Columns(1)
ArrL = Plage.Columns(2)
NElts = UBound(ArrC)
Tri ArrC, ArrCF
Tri ArrL, ArrLF
ArrElt = Plage.Columns(3)
Lignes = UBound(ArrCF)
Cols = UBound(ArrLF)
ReDim ArrEltF(1 To Lignes, 1 To Cols)
With Application
For I = 1 To NElts
ArrEltF(.Match(ArrC(I, 1), ArrCF), _
.Match(ArrL(I, 1), ArrLF)) = ArrElt(I, 1)
Next I
End With
Application.ScreenUpdating = False
Worksheets.Add After:=Plage.Worksheet
With [A2].Resize(Lignes)
.Value = Application.Transpose(ArrCF)
.Font.Bold = True
End With
With [B1].Resize(, Cols)
.Value = ArrLF
.Font.Bold = True
End With
[B2].Resize(Lignes, Cols) = ArrEltF
With [B2].CurrentRegion
.EntireColumn.AutoFit
.Select
End With
End Sub
Private Sub Tri(NonTrié, Trié)
Dim J As Integer
ReDim Idx(1 To NElts)
For I = 1 To NElts
Idx(I) = I
Next I
Arr = NonTrié
Recurse 1, NElts
ReDim Trié(1 To NElts)
Trié(1) = Arr(Idx(1), 1)
J = 1
For I = 2 To NElts
If Arr(Idx(I), 1) <> Arr(Idx(I - 1), 1) Then
J = J + 1
Trié(J) = Arr(Idx(I), 1)
End If
Next I
Erase Arr
ReDim Preserve Trié(1 To J)
End Sub
Private Sub Recurse(ByVal B1 As Long, ByVal H1 As Long)
Dim B2 As Long
Dim H2 As Long
B2 = B1
H2 = H1
Elt = Arr(Idx((B1 + H1) \ 2), 1)
Do While B2 < H2
Do While B2 < H1 And Arr(Idx(B2), 1) < Elt
B2 = B2 + 1
Loop
Do While H2 > B1 And Arr(Idx(H2), 1) > Elt
H2 = H2 - 1
Loop
If B2 < H2 Then
IdxTemp = Idx(B2)
Idx(B2) = Idx(H2)
Idx(H2) = IdxTemp
End If
If B2 <= H2 Then
B2 = B2 + 1
H2 = H2 - 1
End If
Loop
If H2 > B1 Then Recurse B1, H2
If B2 < H1 Then Recurse B2, H1
End Sub
Et pour faire l'inverse ? C'est à dire reconstruire une liste en 3 colonnes à partir d'un
tableau style TCD :
Essaie cette macro. Elle suppose que ton tableau commence dans la cellule A1 de la feuille
active et que la 1ere colonne ait un en-tête ("Article" dans la cellule A1).
Sub TableauEnListe()
Dim TCD As PivotTable, P As Range
Dim RField, DFields, DField
Application.ScreenUpdating = False
RField = [A1]
DFields = Range("B1", [B1].End(xlToRight))
Set TCD = ThisWorkbook.PivotCaches.Add(xlDatabase, [A1]. _
CurrentRegion.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable("")
TCD.AddFields RField
For Each DField In DFields
TCD.PivotFields(DField).Orientation = xlDataField
Next DField
TCD.ColumnGrand = False
With TCD.TableRange2
.Copy
.PasteSpecial xlPasteValues
.ClearFormats
For Each P In .Columns(1).SpecialCells(xlCellTypeBlanks).Areas
P = P(0)
Next P
.Columns(2).Replace "Nom ", ""
End With
[B1:C1] = Array("Question", "Réponse")
End Sub
Laurent Longre, (N°1321)
Si j'affiche un Tableau croisé dynamique sur un Intranet ou Internet, est-il encore dynamique ?
Peut-on ajouter et supprimer des champs de lignes ? Afficher tel ou tel élément dans le champ
de Page ?
Il existe 2 manières d'afficher des tableaux croisés dynamiques dans IE. Soit on ouvre
directement le fichier XLS dans IE, soit on ouvre une page HTML contenant l'Office Web
Component Pivot Table.
Dans le premier, il n'y a rien de particulier à faire. On clique sur un lien hypertexte qui
ouvre le fichier XLS contenant le tableau croisé dans IE. Toutes les manipulations sont alors
possibles.
Dans le second cas, valable seulement à partir d'Excel 2000, il faut générer une page HTML avec
interactivité à partir du classeur. Pour cela, il suffit, dans Excel,
d'enregister le classeur au format HTML et d'activer l'interactivité.
Excel va alors générer une page contenant les OWC Pivot Table, DataAccess et Spreadsheet.
Attention, l'utilisation des OWC est soumise à l'utilisation de licence Office.
Si la page HTML est ouverte sur une machine où Office est installé, les OWC seront
paramétrables. Sinon, ils seront téléchargés sur la machine en mode visionneuse seulement et il
ne sera pas possible de paramétrer le table
directement le fichier XLS dans IE, soit on ouvre une page HTML contenant l'Office Web
Component Pivot Table.
Dans le premier, il n'y a rien de particulier à faire. On clique sur un lien hypertexte qui
ouvre le fichier XLS contenant le tableau croisé dans IE. Toutes les manipulations sont alors
possibles.
Dans le second cas, valable seulement à partir d'Excel 2000, il faut générer une page HTML avec
interactivité à partir du classeur. Pour cela, il suffit, dans Excel,
d'enregister le classeur au format HTML et d'activer l'interactivité.
Excel va alors générer une page contenant les OWC Pivot Table, DataAccess et Spreadsheet.
Attention, l'utilisation des OWC est soumise à l'utilisation de licence Office.
Si la page HTML est ouverte sur une machine où Office est installé, les OWC seront
paramétrables. Sinon, ils seront téléchargés sur la machine en mode visionneuse seulement et il
ne sera pas possible de paramétrer le table
Marc, (N°1320)
J'ai une plage de cellules contenant des valeurs genre 4 4 8 32 5 8 4 4 4 27... J'ai besoin
de lister les différentes valeurs qui apparaissent
de savoir combien de fois chaque valeur apparait
Mettons que tes données soient dans la plage A1:A200.
- Tu sélectionnes cette plage. Il faut qu'elle ait une étiquette dans la
première cellule A1, "Toto" ou tout ce que tu veux.
- Tu fais Données -> Tableau croisé dynamique. Tu cliques sur "Suivant"
jusqu'à l'étape 3.
- [Excel 2000: clique sur "Disposition"]. Tu prends l'étiquette "Toto"
et tu la fais glisser dans la zone "LIGNE".
- Même chose, cette fois dans la zone "DONNEES".
- Si l'étiquette dans la zone "DONNEES" est devenue "Somme Toto", tu
double-cliques dessus et tu sélectionnes "Synthèse par" => "Nb". Et puis
tu valides tout ça.
Et voilà. Tu as un TCD avec en première colonne chacune des valeurs
différentes présentes dans ta plage et pour chacune de ces valeurs son
nombre d'occurrences dans la plage.
- Tu sélectionnes cette plage. Il faut qu'elle ait une étiquette dans la
première cellule A1, "Toto" ou tout ce que tu veux.
- Tu fais Données -> Tableau croisé dynamique. Tu cliques sur "Suivant"
jusqu'à l'étape 3.
- [Excel 2000: clique sur "Disposition"]. Tu prends l'étiquette "Toto"
et tu la fais glisser dans la zone "LIGNE".
- Même chose, cette fois dans la zone "DONNEES".
- Si l'étiquette dans la zone "DONNEES" est devenue "Somme Toto", tu
double-cliques dessus et tu sélectionnes "Synthèse par" => "Nb". Et puis
tu valides tout ça.
Et voilà. Tu as un TCD avec en première colonne chacune des valeurs
différentes présentes dans ta plage et pour chacune de ces valeurs son
nombre d'occurrences dans la plage.
Laurent Longre, (N°1319)
J'ai un tableau croisé dynamique avec l'utilisation de page pour filtrer mes données. exemple :
Tous Etablissement 1 Etablissement 2 ... Je souhaiterais pouvoir
filtrer sans me positionner dans le tcd, par un menu qui m'afficherait la liste que j'ai déjà
dans le filtre page de mon TCD et l'actualiserait en cas de modif : exemple : Menu TRAITEMENT
Choix dans ce menu : Tous Etablissement 1
Etablissement 2 ... Le menu ne devant s'activer que dans le classeur concerné.
Le menu se crée et se supprime par deux macros éventementielles à placer
dans ThisWorkBook :
Private Sub Workbook_Open()
créerMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
supprimMenu
End Sub
J'ai nommé "TCD" la feuille qui contient le tableau croisé.
D'abord création du menu :
A supposer qu'on a un seul champ de page, ou que le champ concerné est le
numéro 1, on récupère le nombre d'items dudit champ qu'on affecte à nbPages.
Puis on compte les menus de la barre d'outils et on place le nouveau menu
"Traitement " en avant-dernier, avant le menu d'aide. On ajoute en suite le
choix "Tous" puis autant de choix que d'items dans le champ de page. A chque
choix on affecte un nom égal à l'article du champ de page et la macro
"macroChoix" qui est la même pour tous (remplir la cellule B1 ou se trouve
le champ de page).
-----------------------------------------------------------
Sub créerMenu()
nbPages = Sheets("TCD").PivotTables("Tableau croisé dynamique1") _
.PageFields(1).PivotItems.Count
nbMenus = Application.CommandBars("Worksheet Menu Bar").Controls.Count
Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
msoControlPopup, before:=nbMenus
Application.CommandBars("Worksheet Menu Bar").Controls(nbMenus ).Caption =
"T&raitement"
With Application.CommandBars("Menu contextuel personnalisé 1")
.Controls.Add Type:=msoControlButton
.Controls(1).Caption = "(Tous)"
.Controls(1).OnAction = "macroChoix"
For i = 1 To nbPages
.Controls.Add Type:=msoControlButton
.Controls(i + 1).Caption = Sheets("TCD"). _
PivotTables("Tableau croisé dynamique1") _
.PageFields(1).PivotItems(i)
.Controls(i + 1).OnAction = "macroChoix"
Next
End With
End Sub
Sub supprimMenu()
Application.CommandBars("Worksheet Menu
Bar").Controls("T&raitement").Delete
End Sub
Sub macroChoix()
Sheets("TCD").Range("B1") = CommandBars.ActionControl.Caption
End Sub
dans ThisWorkBook :
Private Sub Workbook_Open()
créerMenu
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
supprimMenu
End Sub
J'ai nommé "TCD" la feuille qui contient le tableau croisé.
D'abord création du menu :
A supposer qu'on a un seul champ de page, ou que le champ concerné est le
numéro 1, on récupère le nombre d'items dudit champ qu'on affecte à nbPages.
Puis on compte les menus de la barre d'outils et on place le nouveau menu
"Traitement " en avant-dernier, avant le menu d'aide. On ajoute en suite le
choix "Tous" puis autant de choix que d'items dans le champ de page. A chque
choix on affecte un nom égal à l'article du champ de page et la macro
"macroChoix" qui est la même pour tous (remplir la cellule B1 ou se trouve
le champ de page).
-----------------------------------------------------------
Sub créerMenu()
nbPages = Sheets("TCD").PivotTables("Tableau croisé dynamique1") _
.PageFields(1).PivotItems.Count
nbMenus = Application.CommandBars("Worksheet Menu Bar").Controls.Count
Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
msoControlPopup, before:=nbMenus
Application.CommandBars("Worksheet Menu Bar").Controls(nbMenus ).Caption =
"T&raitement"
With Application.CommandBars("Menu contextuel personnalisé 1")
.Controls.Add Type:=msoControlButton
.Controls(1).Caption = "(Tous)"
.Controls(1).OnAction = "macroChoix"
For i = 1 To nbPages
.Controls.Add Type:=msoControlButton
.Controls(i + 1).Caption = Sheets("TCD"). _
PivotTables("Tableau croisé dynamique1") _
.PageFields(1).PivotItems(i)
.Controls(i + 1).OnAction = "macroChoix"
Next
End With
End Sub
Sub supprimMenu()
Application.CommandBars("Worksheet Menu
Bar").Controls("T&raitement").Delete
End Sub
Sub macroChoix()
Sheets("TCD").Range("B1") = CommandBars.ActionControl.Caption
End Sub
Eric Jeanne, (N°1318)
Quel est le nombre maximal de données que l'on peut manipuler dans un TCD ?
Limites des TCD:
Rapports de tableau croisé dynamique dans une feuille:
Limité par la quantité de mémoire disponible
Nombre maximal d'éléments par tableau croisé dynamique: 8 000
Nombre maximal de champs de lignes ou de colonnes par tableau croisé dynamique:
Limité par la quantité de mémoire disponible
Nombre maximal de champs de pages par tableau croisé dynamique:
256 (limitation possible par la quantité de mémoire disponible)
Nombre maximal de champs de données par tableau croisé dynamique: 256
Nombre maximal de formules par tableau croisé dynamique:
Limité par la quantité de mémoire disponible
Rapports de tableau croisé dynamique dans une feuille:
Limité par la quantité de mémoire disponible
Nombre maximal d'éléments par tableau croisé dynamique: 8 000
Nombre maximal de champs de lignes ou de colonnes par tableau croisé dynamique:
Limité par la quantité de mémoire disponible
Nombre maximal de champs de pages par tableau croisé dynamique:
256 (limitation possible par la quantité de mémoire disponible)
Nombre maximal de champs de données par tableau croisé dynamique: 256
Nombre maximal de formules par tableau croisé dynamique:
Limité par la quantité de mémoire disponible
ChrisV, (N°1317)
Lors de la réalisation d'un TCD dans le menu champ dynamique (clic bouton droit dans une
cellule du tableau) menu champ... clic bouton options apparaît dans la liste déroulante
afficher les données "la fonction" index. Qui peut m'expliquer quel résultat donne
cette fonction lors de son application sur un tableau croisé dynamique. J'ai réussi à
l'appliquer ;-) mais pas à l'expliquer.je ne trouve pas de logique quant aux résultat obtenus.
Tentative d'interprétation : si je lis bien la formule, cette fonction renvoie
en fait les effectifs observés dans le tableau divisés par les effectifs
théoriques (effectifs obtenus si les variables croisées dans le tableau étaient
indépendantes, non liées entre elles). Si la valeur obtenue dans une cellule
est supérieure à 1, ça signifie donc que les deux modalités de chacune des
variables qui se croisent dans cette cellule sont liées positivement entre
elles. Si elle est inférieure à 1 c'est l'inverse. Si elle est proche de 1, le
croisement entre ces deux modalités donne un résultat proche de la valeur
théorique.
Je pense donc que cette fonction Index est juste un moyen pour
visualiser rapidement les résultats d'un tri croisé: quels sont les croisements
entre modalités qui s'éloignent le plus de l'indépendance, et dans quel sens.
Mais pour savoir si cette comparaison a vraiment un sens il faudrait faire au
moins un test du Khi-2 sur l'ensemble du tableau pour voir déja si les 2
variables sont indépendantes ou non, et donc si ces "index" sont
interprétables. D'ailleurs, à mon sens, un système qui calculerait
automatiquement le Khi-2 d'un TCD, voire les contributions partielles, et
renverrait sa significativité serait au moins aussi utile que cette fonction,
sinon plus!
en fait les effectifs observés dans le tableau divisés par les effectifs
théoriques (effectifs obtenus si les variables croisées dans le tableau étaient
indépendantes, non liées entre elles). Si la valeur obtenue dans une cellule
est supérieure à 1, ça signifie donc que les deux modalités de chacune des
variables qui se croisent dans cette cellule sont liées positivement entre
elles. Si elle est inférieure à 1 c'est l'inverse. Si elle est proche de 1, le
croisement entre ces deux modalités donne un résultat proche de la valeur
théorique.
Je pense donc que cette fonction Index est juste un moyen pour
visualiser rapidement les résultats d'un tri croisé: quels sont les croisements
entre modalités qui s'éloignent le plus de l'indépendance, et dans quel sens.
Mais pour savoir si cette comparaison a vraiment un sens il faudrait faire au
moins un test du Khi-2 sur l'ensemble du tableau pour voir déja si les 2
variables sont indépendantes ou non, et donc si ces "index" sont
interprétables. D'ailleurs, à mon sens, un système qui calculerait
automatiquement le Khi-2 d'un TCD, voire les contributions partielles, et
renverrait sa significativité serait au moins aussi utile que cette fonction,
sinon plus!
Laurent Longre, (N°1316)
Je cherche à grouper un tableau dynamique avec des dates et ca marche super. Mais si j'ai une
valeur "vide" impossible de grouper automatiquement par date ?
La solution est...d'éviter d'avoir des dates vides! Si les dates sont
saisies dans une autre feuille, il suffit d'effectuer une validation
(Données/Validation ou petite macro). Si tu ne veux pas forcer l'usager
à saisir une date, il ne te reste plus qu'à combler les dates vides par
une date bidon après la saisie, disons 1-1-1900, et de les masquer dans
le tableau croisé.
En dernier recours, tu peux t'en tirer en SÉLECTIONNANT toutes les
cellules non vides AVANT de grouper, ça permet de grouper, mais Excel le
considérera comme un groupement de texte (et non de date), ce qui ne
fera pas forcément ton affaire..
saisies dans une autre feuille, il suffit d'effectuer une validation
(Données/Validation ou petite macro). Si tu ne veux pas forcer l'usager
à saisir une date, il ne te reste plus qu'à combler les dates vides par
une date bidon après la saisie, disons 1-1-1900, et de les masquer dans
le tableau croisé.
En dernier recours, tu peux t'en tirer en SÉLECTIONNANT toutes les
cellules non vides AVANT de grouper, ça permet de grouper, mais Excel le
considérera comme un groupement de texte (et non de date), ce qui ne
fera pas forcément ton affaire..
Laurent Mortézai, (N°1315)
Je voudrai savoir comment faire pour faire un filtre dans un tcd : j'ai une serie de date en
colonne a je veux demander comme filtre une certaine date dans le tableau
Adapter le nom du TCD et celui du champ :
Sub zz_TCD()
laVar = InputBox("", "")
Application.ScreenUpdating = False
With ActiveSheet.PivotTables("MonTableau").PivotFields("Dates")
Dim monPivIt As Object
For Each monPivIt In .PivotItems
monPivIt.Visible = True
Next
On Error Resume Next
For Each monPivIt In .PivotItems
If monPivIt.Name <> laVar Then monPivIt.Visible = False
Next
End With
End Sub
Sub zz_TCD()
laVar = InputBox("", "")
Application.ScreenUpdating = False
With ActiveSheet.PivotTables("MonTableau").PivotFields("Dates")
Dim monPivIt As Object
For Each monPivIt In .PivotItems
monPivIt.Visible = True
Next
On Error Resume Next
For Each monPivIt In .PivotItems
If monPivIt.Name <> laVar Then monPivIt.Visible = False
Next
End With
End Sub
Alain Vallon,
Ajouté ou modifié le 25/10/2003 (N°1312)
Ajouté ou modifié le 25/10/2003 (N°1312)
Malgré la mise à jour de mes TCDs, je n'arrive pas (sans refaire entièrement le TCD) à
supprimer dans mon tableau d'anciennes données qui ont disparu de ma base. Ces données
n'existent plus dans la source, mais apparaissent dans mes boutons champs. On dirait qu'Excel
garde en mémoire des données supprimées.
Je confirme ton problème sous Excel2000, je n'avais pas ce problème sous Excel95. Je pense
qu'on peut conlure à un bug. J'ai trouvé un truc pour ne pas refaire tout le tableau:
-tu renommes le champ problématique dans ta feuille source (disons "name" au lieu de
"nom" comme en-tête de ta colonne de noms)
-tu fais la mise à jour du TCD => un avertissement te dis que le tcd a été modifié, et le
champ "nom" disparaît
-tu renommes le champ "name" par "nom" dans la zone de données source
-tu modifies le tcd pour lui ajouter le champ manquant: les valeurs fantômes ont disparues!
Cet "électrochoc" force le tcd à recalculer les valeurs distinctes des champs. Elle
est plus rapide que de tout recréer le tableau. Quant à la suppression du champ "nom"
puis son ajout (je suppose que tu avais essayé), ça ne donne rien: Le tcd se
"souvient" des valeurs fantômes!
Si l'opération doit être répétée fréquemment, l'écriture d'une macro "électrochoc"
peut être envisagée.
(ceci semble corrigé dans excel XP)
qu'on peut conlure à un bug. J'ai trouvé un truc pour ne pas refaire tout le tableau:
-tu renommes le champ problématique dans ta feuille source (disons "name" au lieu de
"nom" comme en-tête de ta colonne de noms)
-tu fais la mise à jour du TCD => un avertissement te dis que le tcd a été modifié, et le
champ "nom" disparaît
-tu renommes le champ "name" par "nom" dans la zone de données source
-tu modifies le tcd pour lui ajouter le champ manquant: les valeurs fantômes ont disparues!
Cet "électrochoc" force le tcd à recalculer les valeurs distinctes des champs. Elle
est plus rapide que de tout recréer le tableau. Quant à la suppression du champ "nom"
puis son ajout (je suppose que tu avais essayé), ça ne donne rien: Le tcd se
"souvient" des valeurs fantômes!
Si l'opération doit être répétée fréquemment, l'écriture d'une macro "électrochoc"
peut être envisagée.
(ceci semble corrigé dans excel XP)
Laurent Mortézai, (N°1311)
Est-il possible de désactiver le clic droit du tableau croisé dynamique ?
Pour tous les tableaux croisés d'une feuille de calcul particulière :
(à mettre dans le module VBA de la feuille concernée)
Private Sub
Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
On Error Resume Next
Cancel = Not Target.PivotTable Is Nothing
End Sub
Pour tous les TCD du classeur, dans le module ThisWorkbook :
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Cancel = Not Target.PivotTable Is
Nothing
End Sub
Pour tous les TCD de n'importe quel classeur, toujours dans ThisWorkbook:
Dim WithEvents A As Application
Private Sub A_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Cancel = Not Target.PivotTable Is Nothing
End Sub
Private Sub Workbook_Open()
Set A = Application
End Sub
(à mettre dans le module VBA de la feuille concernée)
Private Sub
Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
On Error Resume Next
Cancel = Not Target.PivotTable Is Nothing
End Sub
Pour tous les TCD du classeur, dans le module ThisWorkbook :
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Cancel = Not Target.PivotTable Is
Nothing
End Sub
Pour tous les TCD de n'importe quel classeur, toujours dans ThisWorkbook:
Dim WithEvents A As Application
Private Sub A_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Cancel = Not Target.PivotTable Is Nothing
End Sub
Private Sub Workbook_Open()
Set A = Application
End Sub
Laurent Longre, (N°1310)
Mon premier TCD J'ai entendu parler des tableaux croisés dynamiques mais ça m'a l'air super compliqué ce truc
là...
Comme rien ne vaut un exemple, tu trouveras une explication détaillée pas à pas, avec
un classeur exemple à télécharger et des copies d'écran sur cette
page. Mais avant lis ceci !
Très déroutant en effet au début mais super génial dément.
Prenons un exemple très simple pour commencer: tu as des formateurs qui
interviennent dans différentes villes et tu veux savoir combien de fois
Coco est intervenu à Lyon, mais aussi si à Nantes il y a souvent des interventions.
Et puis tu devines qu'ensuite tu vas avoir envie de rentrer et d'analyser plein
d'autres trucs sur cette base. Le TCD est fait pour toi !
En gros pour commencer tu fais une feuille excel toute propre
en A1 tu écris formateur en B1 lieu
ensuite tu remplis tes deux colonnes sans t'occuper de les trier
(saisie au kilomètre)
tu sélectionnes l'ensemble de la plage
puis tu vas dans données /rapport de tableau croisé dynamique
...suivant... suivant... disposition
Tu te trouves avec un tableau et des cases intitulées page, ligne, colonne et au centre
données.
Le champ dit de page te permet de sélectionner le premier critère de
la tu saisis à la souris l'étiquette formateur et tu la fais glisser dans la case
"page". Dans la case " ligne" tu fais glisser l'étiquette lieu et à nouveau
l'étiquette
et tu le mets dans colonne et enfin tu fais aussi glisser lieu au
milieu dans données
Date va se transformer en nb de dates ; en fait il te compte le nombre de fois
ou le formateur X est intervenu à telle ou telle date.
Tu fais terminer et là tu vois un tableau avec en entete de lignes et de
colonnes des étiquettes avec des flèches. Clique dessus et ne sélectionnes
que les données que tu cherches à voir. Si tu veux modifier la façon dont
tes données sont représentées tu vas dans la barre d'outil du TCD et tu
demandes l'assistant.
ce truc est génial. Prends le temps ensuite de double cliquer sur tout ce
que tu vois pour accéder à d'autres options.
C'est super puissant, et si tu avais en plus par exemple à analyser tes
résultats en fonction d'un lieu de formation. Avec un TCD tu fais un unique
tableau de départ et ensuite tu peux n'afficher que les rubriques qui
t'intéresent ou regrouper des données pour les analyser de façon plus
globales. Par exemple tu pourrais souhaiter regrouper tous les formateurs
sur excel et puis ensuite tous les formateurs de la région Bordelaise puis
tous ceux de moins de 50 ans... sans avoir à reconstruire un tableau chaque
fois. le TCD est exactement fait pour ça. Tu as associé à ce TCD un GCD
(graphique croisé dynamique) qui te permet de visualiser immédiatement tes
résultats.
un classeur exemple à télécharger et des copies d'écran sur cette
page. Mais avant lis ceci !
Très déroutant en effet au début mais super génial dément.
Prenons un exemple très simple pour commencer: tu as des formateurs qui
interviennent dans différentes villes et tu veux savoir combien de fois
Coco est intervenu à Lyon, mais aussi si à Nantes il y a souvent des interventions.
Et puis tu devines qu'ensuite tu vas avoir envie de rentrer et d'analyser plein
d'autres trucs sur cette base. Le TCD est fait pour toi !
En gros pour commencer tu fais une feuille excel toute propre
en A1 tu écris formateur en B1 lieu
ensuite tu remplis tes deux colonnes sans t'occuper de les trier
(saisie au kilomètre)
tu sélectionnes l'ensemble de la plage
puis tu vas dans données /rapport de tableau croisé dynamique
...suivant... suivant... disposition
Tu te trouves avec un tableau et des cases intitulées page, ligne, colonne et au centre
données.
Le champ dit de page te permet de sélectionner le premier critère de
la tu saisis à la souris l'étiquette formateur et tu la fais glisser dans la case
"page". Dans la case " ligne" tu fais glisser l'étiquette lieu et à nouveau
l'étiquette
et tu le mets dans colonne et enfin tu fais aussi glisser lieu au
milieu dans données
Date va se transformer en nb de dates ; en fait il te compte le nombre de fois
ou le formateur X est intervenu à telle ou telle date.
Tu fais terminer et là tu vois un tableau avec en entete de lignes et de
colonnes des étiquettes avec des flèches. Clique dessus et ne sélectionnes
que les données que tu cherches à voir. Si tu veux modifier la façon dont
tes données sont représentées tu vas dans la barre d'outil du TCD et tu
demandes l'assistant.
ce truc est génial. Prends le temps ensuite de double cliquer sur tout ce
que tu vois pour accéder à d'autres options.
C'est super puissant, et si tu avais en plus par exemple à analyser tes
résultats en fonction d'un lieu de formation. Avec un TCD tu fais un unique
tableau de départ et ensuite tu peux n'afficher que les rubriques qui
t'intéresent ou regrouper des données pour les analyser de façon plus
globales. Par exemple tu pourrais souhaiter regrouper tous les formateurs
sur excel et puis ensuite tous les formateurs de la région Bordelaise puis
tous ceux de moins de 50 ans... sans avoir à reconstruire un tableau chaque
fois. le TCD est exactement fait pour ça. Tu as associé à ce TCD un GCD
(graphique croisé dynamique) qui te permet de visualiser immédiatement tes
résultats.
Flo Cabon, (N°1309)
Peut-on introduire un commentaire dans un TCD ? et comment faire la somme des cellules ayant le
même commentaire ?
Pour insérer un commentaire dans un TCD, il faut utiliser la méthode longue:
Insertion/Commentaire
Pour faire la somme d'une plage de cellules en fonction du contenu de
leurs commentaire, je te propose une petite fonction personnalisée:
Function SommeSiComm(plage As Range, commentaire As String) As Double
On Error Resume Next
For Each cel In plage
com = cel.Comment.Text
If com = commentaire Then SommeSiComm = SommeSiComm + cel.Value
Next
End Function
Par exemple, si tu veux totaliser les cellules de la plage A1:A10
comportant le commentaire "Salut", il faudrait entrer:
=SommeSiComm(A1:A10;"Salut")
PS: attention! sensible à la casse (majuscules/minuscules)
Insertion/Commentaire
Pour faire la somme d'une plage de cellules en fonction du contenu de
leurs commentaire, je te propose une petite fonction personnalisée:
Function SommeSiComm(plage As Range, commentaire As String) As Double
On Error Resume Next
For Each cel In plage
com = cel.Comment.Text
If com = commentaire Then SommeSiComm = SommeSiComm + cel.Value
Next
End Function
Par exemple, si tu veux totaliser les cellules de la plage A1:A10
comportant le commentaire "Salut", il faudrait entrer:
=SommeSiComm(A1:A10;"Salut")
PS: attention! sensible à la casse (majuscules/minuscules)
Laurent Mortezai, (N°1308)
J'ai créé un tableau dynamique à partir de certain nombre de données. Je souhaiterais qu'à
chaque fois que des lignes sont ajoutées, elles apparaissent automatiquement dans le tableau
croisé dynamique, sans avoir à modifier la plage de données liées à ce tableau.
Exemple avec un tableau en colonne A:B en Feuil1
Insertion > Noms > Définir >
Nom dans le classeur : tu l'appelles par ex "tablo" > Fait référence à :
=Feuil1!$1:DECALER(Feuil1!$1;0;0;NBVAL(Feuil1!:))
Ceci permet de "rendre dynamique" la plage du tableau
Ensuite tu fais ton TCD avec l'assistant .
* dans l'étape 2, plage données > tu saisis = tablo
* dans l'étape 3, disposition > tu positionnes tes champs .Options > nom >
tu saisis = monTCD
Ensuite pour l'actualiser auto à la saisie :
Dans le module attaché à la feuille (clic droit sur l'onglet > visualiser le code) :
Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
ActiveSheet.PivotTables("monTCD").RefreshTable
end if
End Sub
Insertion > Noms > Définir >
Nom dans le classeur : tu l'appelles par ex "tablo" > Fait référence à :
=Feuil1!$1:DECALER(Feuil1!$1;0;0;NBVAL(Feuil1!:))
Ceci permet de "rendre dynamique" la plage du tableau
Ensuite tu fais ton TCD avec l'assistant .
* dans l'étape 2, plage données > tu saisis = tablo
* dans l'étape 3, disposition > tu positionnes tes champs .Options > nom >
tu saisis = monTCD
Ensuite pour l'actualiser auto à la saisie :
Dans le module attaché à la feuille (clic droit sur l'onglet > visualiser le code) :
Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then
ActiveSheet.PivotTables("monTCD").RefreshTable
end if
End Sub
Alain Vallon, (N°1307)