Comment localiser et modifier les données source d’un tableau croisé dynamique Excel. Pour les données source manquantes, étapes pour essayer de les recréer. Modifiez les options pour enregistrer les données source avec un fichier de tableau croisé dynamique.

REMARQUE: pour les macros qui aident à gérer les données source, accédez à la page des macros de données source du tableau croisé dynamique. Pour une macro permettant de décomposer les données source, accédez à la page Décompresser une macro de données Excel.

Localiser et corriger les données source

Dans cette vidéo, vous verrez comment localiser le tableau croisé dynamique source de données, puis vérifiez la source de données pour vous assurer qu’elle comprend toutes les lignes et colonnes dont vous avez besoin. Si nécessaire, ajustez la source de données, pour inclure de nouvelles lignes ou colonnes.

REMARQUE: Pendant une longue période terme solution, utilisez un tableau Excel nommé comme source de données. Il s’ajustera automatiquement si de nouvelles lignes sont ajoutées.

Les instructions écrites se trouvent sous la vidéo et vous pouvez télécharger l’exemple de fichier à suivre avec la vidéo.

Localiser les données sources

Après la création d’un tableau croisé dynamique, vous pouvez ajouter de nouveaux enregistrements dans les données source ou modifier les enregistrements existants. Dans un grand classeur, il peut être difficile de localiser la source exacte du tableau croisé dynamique, s’il existe plusieurs tableaux ou listes.

REMARQUE: Si vous avez acheté mon complément Pivot Power Premium, cliquez sur Tableau croisé dynamique Info, puis cliquez sur Accédez aux données source.

Procédez comme suit pour trouver les données source d’un tableau croisé dynamique:

1. Sélectionnez une cellule du tableau croisé dynamique.
2. Sur le ruban, sous l’onglet Outils de tableau croisé dynamique, cliquez sur l’onglet Analyser (dans Excel 2010, cliquez sur l’onglet Options).
3. Dans le groupe Données, cliquez sur la section supérieure de la commande Modifier la source de données.

La boîte de dialogue Modifier la source de données du tableau croisé dynamique s’ouvre et vous pouvez voir la table ou la plage source dans la zone Table / Plage. Cela peut être une référence à une feuille et à une plage de cellules, comme

• Commandes! $ A $ 1: $ H $ 9

ou un nom de table, tels que

• Sales_East

Derrière la boîte de dialogue, vous pouvez voir la plage source sur la feuille de calcul, entourée d’une bordure mobile.

Dans la capture d’écran ci-dessous, la plage de données source se termine à la ligne 9 et un nouvel enregistrement a été ajouté à la ligne 10. Cet enregistrement n’apparaîtra pas dans le tableau croisé dynamique, sauf si la source de données est ajustée.

Ajuster la plage de données source

Si les données source d’un tableau croisé dynamique sont une référence statique à une feuille et à une plage spécifiques, il ne s’ajuste pas automatiquement lorsque de nouvelles données sont ajoutées. Dans la capture d’écran ci-dessus, la plage de la source de données fait référence aux commandes! $ A $ 1: $ H $ 9, et elle peut être ajustée manuellement pour inclure une ligne 10.

Pour ajuster manuellement la plage de sources statiques:

Vous pouvez ajuster la plage de sources si nécessaire, afin qu’elle inclue de nouvelles données, mais une meilleure solution est de créer une source dynamique , et basez le tableau croisé dynamique sur ce point. Les instructions sont ci-dessous.

Modifier les données source

Parfois, vous devrez peut-être modifier un tableau croisé dynamique, afin qu’il utilise une source de données différente. Dans cet exemple, une nouvelle table a été créée, avec uniquement des données pour l’Est r egion. Le tableau croisé dynamique sera modifié pour utiliser cette source, puis il pourra être envoyé à un responsable de la région Est, sans révéler les données de la région Ouest.

REMARQUE: après avoir changé de table source , vous devrez peut-être renommer certains champs ou ajouter différents champs au tableau croisé dynamique.

Pour modifier les données source d’un tableau croisé dynamique Excel, procédez comme suit:

1. Sélectionnez une cellule du tableau croisé dynamique.
2. Sur le ruban, sous l’onglet Outils de tableau croisé dynamique, cliquez sur l’onglet Analyser (dans Excel 2010, cliquez sur l’onglet Options).
3. Dans les données groupe, cliquez sur la section supérieure de la commande Modifier la source de données.
4. La boîte de dialogue Modifier la source de données du tableau croisé dynamique s’ouvre et vous pouvez voir la table ou la plage source dans la zone Table / Plage. Dans cette capture d’écran ci-dessous, la source de données actuelle est la table Sales_Data, qui contient les données des deux régions.

   

5. Supprimer la source de données existante et saisissez un nouveau nom de table, ou sélectionnez une nouvelle source de données Gamme e. La table / plage est modifiée en Sales_East, où seules les données de l’Est sont répertoriées.

   

6. Cliquez sur OK.

Source dynamique pour tableau croisé dynamique

Au lieu d’utiliser une plage statique comme source de données du tableau croisé dynamique, une meilleure solution consiste à créer une plage dynamique dont la taille s’ajustera automatiquement.

Créer une source dynamique – Tableau Excel

Source dynamique – Plage nommée

Source dynamique – Tableau dynamique

Utiliser une source dynamique

Source dynamique – Tableau Excel

Au lieu d’utiliser une plage statique comme source de données du tableau croisé dynamique, une meilleure solution consiste à créer une plage dynamique, basée sur un tableau Excel nommé. Il y a des instructions écrites ici, ou regardez la vidéo ci-dessous.

Notez le nom de la table afin de pouvoir l’utiliser comme source du tableau croisé dynamique

Source dynamique – Plage nommée

Si vous ne pouvez pas utiliser un tableau Excel comme données source, vous créez une plage nommée dynamique, basée sur une formule INDEX ou OFFSET. Vous trouverez des instructions écrites ici, ou regardez la vidéo ci-dessous.

La vidéo montre comment créer un plage dynamique avec la fonction OFFSET, dans Excel 2007, et les étapes sont similaires dans les versions ultérieures.

REMARQUE: souvenez-vous du nom de la plage pour pouvoir l’utiliser comme source du tableau croisé dynamique

Dynamic Source – Dynamic Array

Si vous disposez d’une version d’Excel qui prend en charge les nouvelles fonctions, telles que SORT ou UNIQUE, vous pouvez utiliser ces fonctions pour créer des tableaux dynamiques. Cette technique utilise uniquement des formules – pas de macros .

Dans la section Télécharger, récupérez le fichier d’exemple de données source filtrées. Il montre comment configurer une plage nommée avec uniquement les lignes visibles d’un tableau Excel nommé.

Voici le filtre d données, sur une feuille différente, avec seulement les 2 répétitions et 3 catégories des lignes visibles.

Ensuite, vous pouvez créer un tableau croisé dynamique basé uniquement sur ces données filtrées.

Utiliser la source dynamique pour le tableau croisé dynamique

Après avoir créé une source dynamique contenant le tableau croisé dynamique données, procédez comme suit, pour utiliser ces données source:

1. Sélectionnez n’importe quelle cellule du tableau croisé dynamique.
2. Sur le ruban, sous l’onglet Outils de tableau croisé dynamique, cliquez sur Analyser (dans Excel 2010, cliquez sur l’onglet Options).
3. Dans le groupe Données, cliquez sur la section supérieure de la commande Modifier la source de données.
4. Dans la boîte de dialogue Modifier la source de données de tableau croisé dynamique, décochez la case Table / Plage
5. Dans la zone Table / Plage, saisissez le nom de la table ou de la plage nommée que vous avez créée. Dans cet exemple, la source dynamique est une table nommée – tblOrders.
6. Cliquez sur OK

Enregistrer les données source avec un tableau croisé dynamique

Lorsque vous créez un tableau croisé dynamique dans votre classeur, les enregistrements de données source sont enregistrés dans une zone de mémoire spéciale – un cache pivot. Ensuite, lorsque vous fermez le fichier, Excel peut enregistrer les données source dans ce cache pivot ou effacer cette mémoire.

Les deux options présentent des avantages et des inconvénients:

Si vous enregistrez les données source:

• Le fichier sera plus grand
• Le fichier peut s’ouvrir plus rapidement

Si vous n’enregistrez pas les données source:

• Le remplissage sera plus petit
• Le fichier peut s’ouvrir lentement pendant la reconstruction du cache du pivot
• Le tableau croisé dynamique doit être actualisé après l’ouverture du fichier

Comment modifier le paramètre

Pour activer la fonction Enregistrer le paramètre Données source activé ou désactivé:

• Cliquez avec le bouton droit sur une cellule du tableau croisé dynamique, puis cliquez sur Options de tableau croisé dynamique.
• Sous l’onglet Données, dans la section Données de tableau croisé dynamique , ajoutez ou supprimez la coche de Enregistrer les données source avec le fichier
• Cliquez sur OK.

Actualiser les données lors de l’ouverture

Si vous choisissez d’activer désactivez l’option Enregistrer les données source avec le fichier, vous devez activer l’option Actualiser les données lors de l’ouverture du fichier. Ce paramètre se trouve juste en dessous de la case à cocher Enregistrer les données source.

Sinon, vous verrez un message lorsque vous essaierez de filtrer les données ou d’apporter d’autres modifications de mise en page.

• « Le rapport de tableau croisé dynamique a été enregistré sans les données sous-jacentes. Utilisez la commande Actualiser les données pour mettre à jour le rapport. « 

Si vous voyez ce message, cliquez sur OK, puis actualisez manuellement le tableau croisé dynamique.

Et pour éviter les ennuyeux message, activez l’option Actualiser les données lors de l’ouverture du fichier.

Recréer les données source du tableau croisé dynamique

Si vous supprimez accidentellement la feuille de calcul contenant les données source de votre tableau croisé dynamique, ou si vous avez reçu un fichier sans les données du tableau croisé dynamique, vous pourrez peut-être utiliser la fonction Afficher les détails du tableau croisé dynamique pour le recréer.

REMARQUE: Cela ne fonctionnera pas pour tous les tableaux croisés dynamiques, mais est vaut la peine d’essayer, pour récupérer les données source.

Pour essayer de recréer les données source pour un tableau croisé dynamique, procédez comme suit pour utiliser la fonctionnalité Afficher les détails:

1. Assurez-vous qu’aucun des éléments des champs du tableau croisé dynamique n’est masqué – effacez tous les filtres et segments qui ont été appliqués.
2. Le tableau croisé dynamique n’a pas besoin de contenir tous les champs – assurez-vous simplement qu’il est au moins un champ dans le Val zone ues.
3. Afficher les totaux généraux des lignes et des colonnes. Si les totaux ne sont pas visibles, sélectionnez une cellule dans le tableau croisé dynamique et, sur le ruban, sous Outils de tableau croisé dynamique, cliquez sur l’onglet Analyser. Dans le groupe Disposition, cliquez sur Totaux généraux, puis sur Activé pour les lignes et les colonnes.
4. Double-cliquez sur la cellule de total général en bas à droite du tableau croisé dynamique. Cela devrait créer une nouvelle feuille avec les enregistrements associés à partir des données source d’origine.

Corriger l’extraction Données

Si vous récupérez les données source, vous devrez peut-être apporter des modifications supplémentaires pour les restaurer dans leur état d’origine.

• Renommez la table en utilisant le nom des données source d’origine, s’il est connu.
• Si les données sources d’origine contenaient des formules, vous devrez les recréer, car la fonction Afficher les détails exporte uniquement les données.
• Les colonnes des données extraites seront dans le même ordre que dans les données source d’origine.
• Les données extraites seront formaté avec le format de tableau par défaut du classeur. Vous pouvez appliquer un format de tableau différent ou appliquer votre propre mise en forme.
• Remarque: si vous avez apporté des modifications aux données source sans avoir mis à jour le tableau croisé dynamique, ces modifications ne figureront pas dans les données extraites.

Connectez-vous aux données extraites

Si vous renommez la table créée lors du processus Afficher les détails et utilisez le même nom que la table qui contenait à l’origine la source données, le tableau croisé dynamique peut se connecter automatiquement aux nouvelles données source.

Sinon, vous pouvez vous connecter aux données source recréées – suivez les étapes de la section ci-dessus: Utiliser la source dynamique pour le tableau croisé dynamique – utilisez le nom de table que vous avez donné à la nouvelle table.

Téléchargez le fichier exemple

Exemple de source pivot: Pour suivre les didacticiels, vous pouvez télécharger le fichier DateAmt.zip. Le fichier compressé est au format xlsx et ne contient pas de macros.

Exemple de données source filtrées: Cet exemple concerne les versions d’Excel qui ont les nouvelles fonctions, telles que SORT et UNIQUE. Dow Téléchargez cet exemple de fichier pour voir qu’un tableau croisé dynamique est créé à partir des lignes visibles uniquement, dans un tableau Excel filtré. Le fichier zippé est au format xlsx et ne contient pas de macros.

Outils de tableau croisé dynamique

Pour gagner du temps lors de la construction, du formatage et de la modification de vos tableaux croisés dynamiques, utilisez les outils de mon tableau croisé dynamique Complément Power Premium. En quelques clics, vous pouvez:

• copier la mise en forme d’un tableau croisé dynamique et l’appliquer à un autre tableau croisé dynamique.
• changer toutes les valeurs de Count en Sum
• supprimer la «somme de» de tous les en-têtes

et bien plus encore!

Ne manquez pas nos conseils Excel