Un tableau croisé dynamique comporte plusieurs dispositions qui fournissent une structure prédéfinie au rapport, mais vous ne pouvez pas personnaliser ces dispositions. Si vous avez besoin de plus de flexibilité dans la conception de la disposition d’un rapport de tableau croisé dynamique, vous pouvez convertir les cellules en formules de feuille de calcul, puis modifier la disposition de ces cellules en tirant pleinement parti de toutes les fonctionnalités disponibles dans une feuille de calcul. Vous pouvez convertir les cellules en formules qui utilisent des fonctions Cube ou utiliser la fonction GETPIVOTDATA. La conversion de cellules en formules simplifie considérablement le processus de création, de mise à jour et de maintenance de ces tableaux croisés dynamiques personnalisés.
Lorsque vous convertissez des cellules en formules, ces formules accèdent aux mêmes données que le tableau croisé dynamique et peuvent être actualisées pour afficher les résultats à jour. Toutefois, à l’exception possible des filtres de rapport, vous n’avez plus accès aux fonctionnalités interactives d’un tableau croisé dynamique, telles que le filtrage, le tri ou le développement et la réduction des niveaux.
Remarque : Lorsque vous convertissez un tableau croisé dynamique OLAP (Online Analytical Processing), vous pouvez continuer à actualiser les données pour obtenir des valeurs de mesure à jour, mais vous ne pouvez pas mettre à jour les membres réels affichés dans le rapport.
En savoir plus sur les scénarios courants de conversion de tableaux croisés dynamiques en formules de feuille de calcul
Voici des exemples classiques de ce que vous pouvez faire après avoir converti des cellules de tableau croisé dynamique en formules de feuille de calcul pour personnaliser la disposition des cellules converties.
Réorganiser et supprimer des cellules
Supposons que vous ayez un rapport périodique que vous devez créer chaque mois pour votre personnel. Vous n’avez besoin que d’un sous-ensemble des informations de rapport et vous préférez disposer les données de manière personnalisée. Vous pouvez simplement déplacer et organiser les cellules dans une disposition de conception souhaitée, supprimer les cellules qui ne sont pas nécessaires pour le rapport mensuel du personnel enseignant, puis mettre en forme les cellules et la feuille de calcul en fonction de vos préférences.
Insérer des lignes et des colonnes
Supposons que vous souhaitez afficher les informations de vente pour les deux années précédentes, réparties par région et groupe de produits, et que vous souhaitez insérer des commentaires étendus dans des lignes supplémentaires. Insérez simplement une ligne et entrez le texte. En outre, vous souhaitez ajouter une colonne qui affiche les ventes par région et groupe de produits qui ne se trouve pas dans le tableau croisé dynamique d’origine. Insérez simplement une colonne, ajoutez une formule pour obtenir les résultats souhaités, puis remplissez la colonne pour obtenir les résultats de chaque ligne.
Utiliser plusieurs sources de données
Supposons que vous souhaitiez comparer les résultats entre une base de données de production et une base de données de test pour vous assurer que la base de données de test produit les résultats attendus. Vous pouvez facilement copier des formules de cellule, puis modifier l’argument de connexion pour qu’il pointe vers la base de données de test pour comparer ces deux résultats.
Utiliser des références de cellule pour faire varier l’entrée utilisateur
Supposons que vous souhaitez que l’ensemble du rapport change en fonction de l’entrée utilisateur. Vous pouvez remplacer les arguments par les formules de cube par des références de cellule dans la feuille de calcul, puis entrer des valeurs différentes dans ces cellules pour dériver des résultats différents.
Create une disposition de ligne ou de colonne non unique (également appelée création de rapports asymétriques)
Supposons que vous devez créer un rapport qui contient une colonne 2008 appelée Ventes réelles, une colonne 2009 appelée Ventes projetées, mais que vous ne souhaitez pas d’autres colonnes. Vous pouvez créer un rapport qui contient uniquement ces colonnes, contrairement à un tableau croisé dynamique, qui nécessite des rapports symétriques.
Create vos propres formules cube et expressions MDX
Supposons que vous souhaitiez créer un rapport qui affiche les ventes d’un produit particulier par trois vendeurs spécifiques pour le mois de juillet. Si vous connaissez bien les expressions MDX et les requêtes OLAP, vous pouvez entrer les formules cube vous-même. Bien que ces formules puissent devenir très élaborées, vous pouvez simplifier la création et améliorer la précision de ces formules à l’aide de la saisie semi-automatique des formules. Pour en savoir plus, voir Utiliser la saisie semi-automatique des formules.
Remarque : Vous pouvez uniquement convertir un tableau croisé dynamique OLAP (Online Analytical Processing) à l’aide de cette procédure.
-
Pour enregistrer le tableau croisé dynamique en vue d’une utilisation ultérieure, nous vous recommandons d’effectuer une copie du classeur avant de convertir le tableau croisé dynamique en cliquant sur Fichier > Enregistrer sous. Pour plus d’informations, consultez Enregistrer un fichier.
-
Préparez le tableau croisé dynamique afin de réduire la réorganisation des cellules après la conversion en procédant comme suit :
-
Remplacez par une disposition qui ressemble le plus à la disposition souhaitée.
-
Interagissez avec le rapport, par exemple en filtrant, en triant et en redessinant le rapport, pour obtenir les résultats souhaités.
-
-
Cliquez sur le tableau croisé dynamique.
-
Sous l’onglet Options , dans le groupe Outils , cliquez sur Outils OLAP, puis sur Convertir en formules.
S’il n’existe aucun filtre de rapport, l’opération de conversion se termine. S’il existe un ou plusieurs filtres de rapport, la boîte de dialogue Convertir en formules s’affiche.
-
Décidez de la façon dont vous souhaitez convertir le tableau croisé dynamique :
Convertir l’intégralité du tableau croisé dynamique
-
Sélectionnez la zone Convertir les filtres de rapport case activée.
Cela convertit toutes les cellules en formules de feuille de calcul et supprime l’intégralité du tableau croisé dynamique.
Convertissez uniquement les étiquettes de lignes de tableau croisé dynamique, les étiquettes de colonne et la zone de valeurs, mais conservez les filtres de rapport
-
Vérifiez que la zone Convertir les filtres de rapport case activée est désactivée. (Il s’agit de la valeur par défaut.)
Cela convertit toutes les cellules d’étiquette de ligne, d’étiquette de colonne et de zone de valeurs en formules de feuille de calcul, et conserve le tableau croisé dynamique d’origine, mais avec uniquement les filtres de rapport afin que vous puissiez continuer à filtrer à l’aide des filtres de rapport.
Remarque : Si le format de tableau croisé dynamique est la version 2000-2003 ou antérieure, vous pouvez uniquement convertir l’intégralité du tableau croisé dynamique.
-
-
Cliquez sur Convertir.
L’opération de conversion actualise d’abord le tableau croisé dynamique pour s’assurer que les données à jour sont utilisées.
Un message s’affiche dans la barre de status pendant que l’opération de conversion a lieu. Si l’opération prend beaucoup de temps et que vous préférez effectuer une conversion à un autre moment, appuyez sur Échap pour annuler l’opération.
Remarques :
-
Vous ne pouvez pas convertir des cellules avec des filtres appliqués aux niveaux masqués.
-
Vous ne pouvez pas convertir les cellules dans lesquelles les champs ont un calcul personnalisé qui ont été créés à l’aide de l’onglet Afficher les valeurs sous de la boîte de dialogue Paramètres du champ valeurs . (Sous l’onglet Options , dans le groupe Champ actif , cliquez sur Champ actif, puis sur Valeurs Paramètres du champ.)
-
Pour les cellules converties, la mise en forme des cellules est conservée, mais les styles de tableau croisé dynamique sont supprimés, car ces styles peuvent s’appliquer uniquement aux tableaux croisés dynamiques.
-
Vous pouvez utiliser la fonction GETPIVOTDATA dans une formule pour convertir des cellules de tableau croisé dynamique en formules de feuille de calcul lorsque vous souhaitez travailler avec des sources de données non-OLAP, lorsque vous préférez ne pas effectuer immédiatement la mise à niveau vers le nouveau format de tableau croisé dynamique version 2007 ou lorsque vous souhaitez éviter la complexité de l’utilisation des fonctions cube.
-
Assurez-vous que la commande Générer GETPIVOTDATA dans le groupe Tableau croisé dynamique sous l’onglet Options est activée.
Remarque : La commande Générer GETPIVOTDATA définit ou efface l’option Utiliser les fonctions GETPIVOTTABLE pour les références de tableau croisé dynamique dans la catégorie Formules de la section Utilisation des formules de la boîte de dialogue Options Excel .
-
Dans le tableau croisé dynamique, vérifiez que la cellule que vous souhaitez utiliser dans chaque formule est visible.
-
Dans une cellule de feuille de calcul en dehors du tableau croisé dynamique, tapez la formule que vous souhaitez jusqu’au point où vous souhaitez inclure les données du rapport.
-
Cliquez sur la cellule du tableau croisé dynamique que vous souhaitez utiliser dans votre formule dans le tableau croisé dynamique. Une fonction de feuille de calcul GETPIVOTDATA est ajoutée à votre formule qui récupère les données du tableau croisé dynamique. Cette fonction continue à récupérer les données correctes si la disposition du rapport change ou si vous actualisez les données.
-
Terminez la saisie de votre formule et appuyez sur Entrée.
Remarque : Si vous supprimez l’une des cellules référencées dans la formule GETPIVOTDATA du rapport, la formule retourne #REF !.
