Utiliser les tableaux croisés dynamiques dans Excel

Exemple concret 

Ce tutoriel utilise un jeu de données sur les ventes de vélos dans le monde entre 2011 et 2016. Les informations démographiques sur les clients et les produits y sont accompagnées de colonnes de profit, de coût et de chiffre d’affaires. Vous allez créer un tableau croisé dynamique à partir de ce jeu de données afin de l’analyser. 

Les données sont disponibles ici pour vous permettre de suivre pas à pas. 

Passer en revue les données 

Commençons par examiner un échantillon des données de ce tutoriel. Nous avons un tableau qui contient 11 colonnes, incluant des types date, texte et numériques. À partir de ce sous-ensemble, on voit déjà de nombreuses façons d’exploiter ces données pour mener l’analyse et dégager des insights utiles. 

Créer votre premier tableau croisé dynamique 

Pour créer votre premier tableau croisé dynamique, sélectionnez le tableau source, allez dans l’onglet Insertion et choisissez Tableau croisé dynamique parmi les options ci-dessous.

Une fenêtre s’ouvre alors pour vous demander si vous souhaitez modifier la plage de données à utiliser et placer le tableau croisé dynamique dans une nouvelle feuille ou dans une feuille existante. Pour ce tutoriel, les options par défaut conviennent. Cliquez sur OK pour créer le tableau croisé dynamique dans une nouvelle feuille. 

Notre nouvelle feuille affiche l’ossature du tableau croisé dynamique qui a été générée automatiquement. À gauche de l’écran, vous voyez le repère de l’emplacement où apparaîtra le tableau une fois construit. À droite, le volet des champs du tableau croisé dynamique, dans lequel vous ferez l’essentiel du travail. 

Lorsque vos données sont déjà sous forme de tableau dans Excel, le tableau croisé dynamique inclut automatiquement l’ensemble. En revanche, si vos données ne sont pas dans un objet tableau, vous pouvez sélectionner manuellement la plage désirée en la surlignant puis en créant le tableau croisé dynamique de la même manière qu’auparavant. 

Ajouter vos données 

Nous allons supposer que chaque ligne correspond à un client unique ; il n’y a donc pas de commandes répétées. C’est une vision très simplifiée du jeu de données, mais en l’absence d’identifiant client unique, cette hypothèse facilite le tutoriel. 

Commencez par faire glisser un champ de catégorie dans la zone Lignes. Nous utiliserons Country comme catégorie : repérez-le dans la liste des champs de l’éditeur de tableau croisé dynamique puis faites-le glisser dans Lignes. Ensuite, identifiez la valeur à évaluer — ici, nous souhaitons compter les clients, mais faute d’un identifiant, nous pouvons utiliser le champ Customer_Age, à faire glisser dans la zone Valeurs. Par défaut, l’agrégation est SOMME ; il faut la remplacer par NB. Cliquez avec le bouton droit sur Customer_Age dans Valeurs, sélectionnez Paramètres du champ, changez Résumer par de Somme à Nombre, puis validez.

Bien joué ! Nous pouvons maintenant voir un décompte des clients par Country depuis lequel ils ont commandé. C’est instructif, mais organisons-le par Country ayant le plus de clients. Pour cela, cliquez avec le bouton droit sur une valeur de la colonne Count of Customer_Age, choisissez Trier, puis Trier du plus grand au plus petit. On constate que les États-Unis comptent le plus de clients, avec 39 206.

Aller plus loin 

Maintenant que vous savez configurer un tableau croisé dynamique de base, allons plus loin en ajoutant des filtres et des colonnes, et en apprenant à actualiser un tableau croisé. Supprimez d’abord les lignes et valeurs ajoutées à l’étape précédente en cliquant dessus avec le bouton droit puis en sélectionnant Supprimer le champ. Vous repartez d’une base vierge. 

Pour ce tableau croisé, nous allons répondre à la question : « En 2015, quel trimestre a généré le plus de chiffre d’affaires, et à quelle catégorie/sous-catégorie de produit correspond-il ? » 

Ajoutons d’abord les lignes selon lesquelles analyser les données : Product_Category et Sub_Category. Pour les colonnes, nous souhaitons une ventilation par Quarters. Mais nous n’avons pas de colonne Quarters… Pas de problème : Excel détecte automatiquement les dates. Lorsque vous faites glisser Date dans Colonnes, deux champs apparaissent : Years et Quarters. Comme nous n’avons pas besoin du niveau « Date », vous pouvez retirer ce champ de la zone Colonnes. 

Comme nous voulons filtrer sur 2015, déplacez le champ Years dans la zone Filtres. Votre tableau croisé devrait maintenant ressembler à ceci :

La structure étant en place, pré-sélectionnez l’année en cliquant sur la liste déroulante de Years et choisissez 2015. Votre tableau croisé dynamique se présente désormais ainsi. 

À ce stade, nos points de données sont au niveau unitaire : le prix unitaire et le coût unitaire sont associés à un seul article. Nous souhaitons synthétiser correctement l’information ; pour cela, revenons au tableau de données source.

Dans l’onglet des données, créez une nouvelle colonne calculée pour obtenir le chiffre d’affaires par ligne. Il s’agit de multiplier Order Quantity par Unit Price. 

Pour ajouter une nouvelle colonne, retournez à la feuille de données et, à côté de la colonne Unit Price, en L2, saisissez le calcul suivant :

[@[Order_Quantity]]*[@[Unit_Price]]

Nous disposons maintenant d’une nouvelle colonne à utiliser dans le tableau croisé dynamique. Revenez sur la feuille du tableau croisé : le champ n’apparaît pas encore. Pour actualiser, cliquez avec le bouton droit n’importe où dans le tableau et sélectionnez Actualiser.

La nouvelle colonne est disponible ; faites-la glisser dans la zone Valeurs. Si vous souhaitez ajouter d’autres colonnes calculées, consultez notre Excel Cheatsheet pour découvrir d’autres types de calculs utiles. 

La présentation est un peu chargée : simplifions-la en supprimant tous les totaux généraux via l’onglet Création. Ouvrez le menu Totaux généraux et sélectionnez Désactivés pour les lignes et les colonnes. Mettons ensuite les montants au format monétaire : sélectionnez les valeurs du tableau puis, dans l’onglet Accueil, appliquez le format Monétaire. 

Parfait, notre tableau croisé dynamique final est prêt. On voit que la catégorie Bikes a généré le plus de chiffre d’affaires en 2015, en particulier au T4. Parmi les vélos vendus par l’entreprise, les Road Bikes sont les plus populaires et génèrent le plus de revenus.

Allez plus loin !

Ce tutoriel constitue une bonne introduction aux tableaux croisés dynamiques avec Excel ; si vous avez pu suivre sans difficulté, bravo ! Si vous avez rencontré des blocages, vous pouvez retrouver le fichier de solution ici. 

Expérimentez avec un jeu de données plus complexe, appliquez différents attributs, testez et voyez si vous parvenez à donner du sens aux données.

Si vous souhaitez tester davantage vos compétences sur Excel, découvrez notre cours Data Analysis in Excel, si ce n’est pas déjà fait.