Un tableau croisé dynamique a plusieurs dispositions qui fournissent une structure prédéfinie au rapport, mais vous ne pouvez pas personnaliser ces dispositions. Si vous avez besoin de davantage 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 utilisant des fonctions Cube ou LIREDONNEESTABCROISDYNAMIQUE. La conversion de cellules en formules simplifie considérablement le processus de création, de mise à jour et de gestion de ces tableaux croisés dynamiques personnalisés.
Lorsque vous convertissez des cellules en formules, celles-ci accèdent aux mêmes données que le tableau croisé dynamique et peuvent être actualisées pour obtenir des résultats actualisés. Toutefois, à l’exception peut-être 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, vous pouvez continuer à actualiser les données pour obtenir des valeurs de mesure actualisées, mais vous ne pouvez pas mettre à jour les membres réels affichés dans le rapport.
Découvrez les scénarios les plus 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 du 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 de votre choix, supprimer les cellules qui ne sont pas nécessaires pour le rapport mensuel du personnel, puis mettre en forme les cellules et la feuille de calcul selon vos préférences.
Insérer des lignes et des colonnes
Supposons que vous souhaitiez afficher les informations de ventes des deux années précédentes ventilées par région et par groupe de produits, et que vous souhaitiez insérer un commentaire étendu 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 figure 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 vers le bas pour obtenir les résultats pour chaque ligne.
Utiliser plusieurs sources de données
Imaginons que vous souhaitez comparer les résultats d’une base de données de production et d’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 afin de comparer ces deux résultats.
Utiliser des références de cellule pour faire varier les entrées utilisateur
Supposons que vous souhaitez que l’intégralité du rapport change en fonction des entrées de l’utilisateur. Vous pouvez remplacer les arguments des formules de cube par des références de cellule dans la feuille de calcul, puis entrer différentes valeurs dans ces cellules pour obtenir des résultats différents.
Créer une disposition de ligne ou de colonne non uniforme (également appelée création de rapports asymétriques)
Supposons que vous deviez créer un rapport qui contient une colonne de 2008 intitulée Ventes réelles ou une colonne de 2009 intitulée Ventes planifiées, mais vous ne voulez pas d’autres colonnes. Vous pouvez créer un rapport qui ne contient que ces colonnes, contrairement à un tableau croisé dynamique, qui nécessite des rapports symétriques.
Créer vos propres formules de 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 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 leur création et en améliorer la précision à l’aide de la saisie semi-automatique de formule. Pour en savoir plus, voir Utiliser la saisie semi-automatique des formules.
Convertir des cellules en formules utilisant des fonctions Cube
Remarque
Vous ne pouvez convertir un tableau croisé dynamique OLAP qu’en utilisant cette procédure.
Pour enregistrer le tableau croisé dynamique afin de pouvoir l’utiliser ultérieurement, nous vous recommandons d’effectuer une copie du classeur avant de le convertir en cliquant sur Fichier>enregistrer sous. Pour plus d’informations, voir Enregistrer un fichier.
Préparez le tableau croisé dynamique de manière à pouvoir réduire la réorganisation des cellules après la conversion en procédant comme suit :
- Utilisez une disposition qui se rapproche le plus possible de la disposition souhaitée.
- Interagissez avec le rapport, par exemple en filtrant, triant et remaniant 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 comment convertir le tableau croisé dynamique :
Convertir l’intégralité du tableau croisé dynamiqueActivez la case à cocher Convertir les filtres de rapport en case activée.
Cette opération convertit toutes les cellules en formules de feuille de calcul et supprime l’intégralité du tableau croisé dynamique.
Convertissez uniquement les étiquettes de ligne, les étiquettes de colonne et la zone de valeurs des tableaux croisés dynamiques, mais conservez les filtres de rapportAssurez-vous que la zone case activée Convertir les filtres de rapport est bien désactivée. (Il s’agit de la valeur par défaut).
Cela convertit toutes les cellules d’étiquette de ligne, 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 de la version 2000-2003 ou antérieure, vous pouvez uniquement convertir le tableau croisé dynamique entier.
Cliquez sur Convertir.
L’opération de conversion actualise d’abord le tableau croisé dynamique pour garantir que des données à jour sont utilisées.
Un message s’affiche dans la barre de status pendant l’opération de conversion. Si l’opération prend beaucoup de temps et que vous préférez effectuer la conversion à un autre moment, appuyez sur Échap pour l’annuler.Remarque
- Vous ne pouvez pas convertir les cellules avec des filtres appliqués à des niveaux masqués.
- Vous ne pouvez pas convertir les cellules dans lesquelles les champs ont un calcul personnalisé créé via l’onglet Afficher les valeurs sous de la boîte de dialogue Paramètres des champs de valeurs . (Dans l’onglet Options , dans le groupe Champ actif , cliquez sur Champ actif, puis sur Paramètres des champs de valeurs.)
- Pour les cellules converties, la mise en forme des cellules est conservée, mais les styles de tableau croisé dynamique sont supprimés, car ils ne peuvent s’appliquer qu’aux tableaux croisés dynamiques.
Convertir des cellules à l’aide de la fonction LIREDONNEESTABCROISDYNAMIQUE
Vous pouvez utiliser la fonction LIREDONNEESTABCROISDYNAMIQUE dans une formule pour convertir des cellules de tableau croisé dynamique en formules de feuille de calcul lorsque vous souhaitez utiliser des sources de données non OLAP, lorsque vous préférez ne pas effectuer immédiatement de 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 LIREDONNEESTABCROISDYNAMIQUE dans le groupe Tableau croisé dynamique de l’onglet Options est activée.
Remarque
La commande Générer LIREDONNEESTABCROISDYNAMIQUE définit ou désactive l’option Utiliser les fonctions LIRETABLEAUCROISDYNAMIQUE pour les références de tableau croisé dynamique dans la catégorie Formules de la section Utilisation de formules de la boîte de dialogue Options Excel .
Dans le tableau croisé dynamique, assurez-vous 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 souhaitée jusqu’au point où vous souhaitez inclure des données du rapport.
Cliquez sur la cellule du tableau croisé dynamique que vous voulez utiliser dans votre formule du tableau croisé dynamique. Une fonction de feuille de calcul LIREDONNEESTABCROISDYNAMIQUE est ajoutée à votre formule qui récupère les données à partir du tableau croisé dynamique. Cette fonction continue à récupérer les données correctes si la mise en page du rapport change ou si vous actualisez les données.
Finissez de taper votre formule, puis appuyez sur Entrée.
Remarque
Si vous supprimez du rapport une des cellules référencées dans la formule LIREDONNEESTABCROISDYNAMIQUE, la formule renvoie #REF !.