TABLEAUX CROISÉS DYNAMIQUES: 5 TYPES DE CALCULS par Sophie Marchand Si vous utilisez fréquemment Excel, vous savez que vous pouvez utiliser les tableaux croisés dynamiques pour compiler et présenter vos données sous différentes dimensions. La plupart des usagers présentent les données sous les formats déjà présents dans la base de données sous-jacente. 5 types de calculs Excel dispose de commandes pour présenter vos données selon 5 grands types de calculs: SommeCompteMoyenneMaxMin Par exemple, en partant d’une base de données comprenant 60 000 transactions de ventes pour 10 marchés différents, Excel nous permet de présenter les données comme suit: Procédure Pour afficher les 5 types de calculs précédents, vous devez d’abord ajouter 5 fois le champs numérique sous-jacent (dans ce cas-ci les ventes ou "sales amount") dans la zone "Valeurs" (ou "Values" en anglais). Ensuite, pour chacun des champs, vous devez cliquer sur l’option "Paramètres des champs de valeurs" ou "Value Field Settings" en anglais. Vous aimez cet article? Like this:
TABLEAUX CROISÉS DYNAMIQUES: CALCULER UNE VARIANCE MENSUELLE par Sophie Marchand Saviez-vous que vous pouvez présenter des variances (en $ ou en %) dans des tableaux croisés dynamiques et tout cela, sans ajouter le calcul de variance dans une colonne de votre base de données sous-jacente? Cet article vous montre comment afficher une variance annuelle en %, au niveau des ventes, dans un tableau croisé dynamique. 1 – Insérez votre champs numérique, dans ce cas-ci les ventes ("Sales Amount") dans la zone "Valeurs" ("Values") de votre liste de champs. 2 – Insérez le même champs, dans notre exemple les ventes, dans la zone "Valeurs" ("Values") de votre liste de champs. 3 – Vous obtiendrez un tableau croisé dynamique semblable à celui-ci: Cliquez sur l’image pour l’agrandir 4 – Cliquez sur sur "Sum of Sales Amount" pour afficher les paramètres de champs de valeurs. 5 – Vous obtiendrez un résultat semblable à celui-ci: 6 – Vous pouvez également choisir de retirer ou cacher les colonnes moins pertinentes pour obtenir un résultat semblable à celui-ci: Like this:
Créer un formulaire personnalisé pour saisir des données sur Excel Avec Excel, il est possible d’utiliser un formulaire de saisie disponible par défaut. Cependant, il reste quelque peu basique et ne vous permet pas, par exemple, d’utiliser des listes déroulantes. Nous allons donc, dans ce tutoriel, créer un formulaire de saisie personnalisé avec Excel tant au niveau de la saisie des données que de la présentation. Pour ce tutoriel, nous utiliserons un fichier Excel que vous retrouverez dans l’Espace membres du blog Votre Assistante. Utilisation d’un formulaire de saisie Excel de base : Pour afficher le formulaire, nous avons besoin d’un bouton qui, dans la version 2013, n’apparaît pas par défaut. Si, par la suite, vous souhaitez supprimer ce bouton, il suffira de vous placer sur ce nouveau groupe (à droite) et de cliquer sur << Supprimer. Profitez-en pour cocher l’onglet Développeur s’il est décoché (et que vous souhaitez créer un formulaire personnalisé). Pour utiliser le formulaire classique, cliquez dessus. Cliquez ensuite sur Zone de texte . Next J . .
Calendrier : ajouter un bouton de choix dans vos formulaires On m’a demandé comment ajouter dans un formulaire, un outil assez classique de choix de date, sous forme de calendrier, afin de rendre l’utilisation un peu plus user-friendly. Un outil de ce ce genre là : Alors pour cela, nous allons ajouter un contrôle qui n’est pas présent dans la liste standard, voir peut-être pas encore présent sur votre machine (si ce n’est pas le cas, je vous invite à suivre ce lien pour le rajouter). Pour commencer, nous allons aller chercher le contrôle à installer. Excel vous ouvre alors une liste où vous trouverez tous les contrôles disponibles sur votre ordinateur et que vous pourriez être amenés à intégrer dans vos documents. Dans cette liste, nous allons aller chercher « Microsoft Date and Time Picker », puis cliquer sur OK. Votre curseur se transforme alors en croix, et vous allez pouvoir dessiner la forme de votre contrôle dans votre feuille. Et voilà, le contrôle est prêt à l’emploi. Bons formulaires !
1/5: MAÎTRISER LES TABLEAUX CROISÉS DYNAMIQUES (1 de 5) par Sophie Marchand Si vous n’êtes pas encore familier avec les tableaux croisés dynamiques, je vous invite à consulter d’abord Tableaux croisés dynamiques dans Excel: la base. Dans ce premier billet d’une série de 5 billets portant sur divers trucs et astuces avec les tableaux croisés dynamiques, je vous montre comment fonctionne l’option "Grouper" (Group) et je vous explique comment cette option peut vous être utile dans l’analyse de vos données. Selon Wikipedia: Un tableau croisé dynamique (en anglais pivot table), en abrégé « TCD », est une fonctionnalité de certains tableurs qui permet de générer une synthèse d’une table de données brutes. Comme son nom l’indique, l’option "Grouper" (Group) des tableaux croisés dynamiques, sert à regrouper des données pour fin de présentation, mais également à ajouter des niveaux de présentation qui n’existent pas dans votre base de données originale. Excel: Maîtriser les tableaux croisés dynamiques (2 de 5) Sophie Marchand, M.Sc., CGA Like this:
TCD - Intégrer un champ calculé - Tutoriels & Astuces Excel > Tableaux Croisés Dyn. - Tutoriels Les tableaux croisés dynamiques disposent d'outils de calculs préétablis, mais vous pouvez également enrichir vos tableaux de vos propres formules de calculs, au moyen de "champs calculés" ou "d'éléments calculés". Cet article aborde la mise en place d'un champ calculé, c'est à dire la création d'un nouveau champ qui apparaîtra dans le TCD, en utilisant dans des formules les données des autres champs du tableau. Prenons l'exemple d'une base de données indiquant simplement les ventes d'une année sur l'autre, nous souhaitons synthétiser ces données tout en calculant l'évolution des ventes en montant et en pourcentage: Commençons par construire notre TCD, nous voulons comparer les chiffres d'affaires par représentant et type d'enseigne et obtenons ce premier résultat: Sélectionnons une cellule quelconque du TCD afin d'activer le menu "Outils de tableau croisé dynamique", choisissons l'onglet "Option", puis dans le ruban, dans le groupe "Outils" cliquons sur "Formules" puis "Champ calculé":
Excel - Tableau croisé dynamique Vous pouvez retranscrire les données ci-dessous dans une feuille de calcul. Vous pouvez aussi vous rendre à la page Web sur les Fichiers de démonstrations et prendre le document demodb.xls. Création d'un tableau croisé dynamique Placez le pointeur sur n'importe quelle cellule entre A1 et G16. Du menu Données, sélectionnez l'option Rapport de tableau croisé dynamique. Excel vous demande où est située la source des données qui vont servir à composer le tableau croisé dynamique. Excel vous demande ensuite quel type de rapport que vous voulez: tableau ou graphique? Pour les besoins de l'exercice, utilisez les mêmes options que sur le graphique ci-dessus (Base de données Excel et tableau). Appuyez sur le bouton Suivant. Excel vous demande de confirmer l'endroit où sont situées les données dont vous avez besoin pour le tableau croisé dynamique. Assurez-vous que les cellules sélectionnées soient bien entre A1 et G16. Excel vous demande ensuite où vous voulez conserver le tableau croisé dynamique. .
Les affichages personnalisés - Excel-Plus ! Parfois, vous êtes amenés à travailler en Multi-utilisateurs sur un classeur Excel. Et puis, comme vous ne suivez pas les mêmes choses, pour éviter de montrer les états de restitution à une personne qui n’est pas censée les voir, vous ne la faîtes travailler que sur un onglet de base, que vous copiez sur votre fichier principal ensuite. Cette méthode fonctionne, mais peut être source d’erreurs, demande des manipulations, alors que vous pourriez utiliser les affichages personnalisés. Les affichages personnalisés sont un outils d’Excel, qui se trouve dans l’onglet Affichage du ruban, et qui vous permet de choisir les vues que vous désirez selon le travail que vous devez réaliser. Quelles vues ? Dans ce cas, en enregistrant plusieurs vues, de la complète à la plus restreinte, vous pourriez basculer sur ces affichages en deux clics. Partons d’un fichier que vous avez créé. Excel vous ouvre alors la fenêtre suivante : Vous allez alors cliquer sur Ajouter pour créer cette première vue.
2/5: MAÎTRISER LES TABLEAUX CROISÉS DYNAMIQUES (2 de 5) par Sophie Marchand Voici la deuxième capsule d’une série de 5 capsules portant sur les tableaux croisés dynamiques dans Excel. Vous trouverez une vidéo explicative, tout en bas de cet article. Notre première capsule portait sur l’option "Grouper", qui sert à regrouper des étiquettes de lignes et des étiquettes de colonnes. Cette deuxième capsule porte sur les options disponibles pour présenter les valeurs dans un tableau croisé dynamique. D’abord, rappelez-vous que dans un tableau croisé dynamique, vous avez des filtres, des étiquettes de colonnes, des étiquettes de lignes et enfin, des valeurs. Ensuite, rappelez-vous qu’en cliquant sur une valeur dans votre "Liste de champs de tableau croisé dynamique", vous pouvez accéder au menu "Paramètres de champs de valeurs". Finalement, dans la vidéo suivante, vous observerez qu’il existe diverses options pour présenter les valeurs d’un tableau croisé dynamique. Avez-vous des questions par rapport aux tableaux croisés dynamiques? Like this:
Créer un top 5 dans des tableaux croisés dynamiques Un des intérêts des tableaux croisés dynamiques est de pouvoir traiter un nombre important de données et d’en faire des synthèses. Et parmi ces synthèses, vous avez la possibilité de demander des Tops, c’est à dire de faire afficher uniquement le top 5, 10 ou 20 de vos données sur la base d’un champ. Pour voir comment procéder, nous allons reprendre un exemple de fichier déjà utilisé pour le billet sur les tableaux croisés dynamiques, et nous allons modifier le tableau pour faire afficher un top 5. En cliquant sur la liste déroulante des étiquettes de lignes, vous obtiendrez le menu suivant où trouver comment demander l’affichage qui vous intéresse. Vous voyez que, par défaut, dans la liste de filtres disponibles, vous avez la possibilité de ne choisir que les 10 premiers. Si on regarde un peu en détails les options ici, voici ce qu’on peut faire : En sélectionnant « Haut », « 5″, « Eléments » et « Somme de Montant cotisation 2009″, vous obtiendrez le résultat suivant : Bons Tops !