Comment faire son premier tableau croisé dynamique

Introduction

Excel est connu pour vous permettre de stocker des listes infinies de données (1'048'576 lignes et 16'384 colonnes exactement). Une fois saisies ou importées, vous pouvez les placer dans des tableaux, filtrer les résultats si nécessaire et effectuer toutes sortes de calculs. Et ce n'est pas si mal !

Excel est un très bon tableur, mais son succès n'aurait pas été possible sans les fonctions d'analyse qu'il offre aux utilisateurs, comme la fonction "tableau croisé dynamique" (connue désormais sous cette abréviation: TCD).

Une fois que vous y avez goûté, une fois que vous avez compris le pouvoir que les tableaux de données vous donnent, il n'y a rien d'autre qui puisse absorber votre intérêt aussi complètement. Alors, c'est parti !

Quels sont les avantages de l'utilisation d'un tableau croisé dynamique dans Excel ?

J'aurais aimé dire "Les données peuvent être croisées", mais cela ne fonctionnerait pas bien comme explication.

Cette fonction est utilisée pour créer un résumé de vos données. Cependant, lorsque vous produisez un résumé, il est basé sur le matériel d'origine, dans ce cas, les lignes des tableaux. Selon le contexte et l'usage que vous en faites, il peut s'agir de listes de factures ou de commandes, ou de toute autre liste.

En général, chacune des lignes de valeur comporte des informations telles que "catégorie", "lieu" et "date". En attendant, nous nous posons quelques questions sur cette masse de données. Par exemple : Quelle est la somme des factures par pays ? Combien d'articles ont été vendus par mois ?

Et vous devrez peut-être le faire pour chacun des résultats ou paramètres importants pour votre entreprise, et peut-être même plus fréquemment.

Un TCD vous donne l'information en quelques secondes, mais il est beaucoup plus facile à utiliser. Vous pouvez simplement rafraîchir le résultat pour que toutes les valeurs soient mises à jour automatiquement.

Maintenant que vous avez compris comment mettre en place un projet, passons à l'étape 1.

Étape 1 : Préparez vos données

Excel est un outil qui demande de la rigueur ! Il y a des règles à respecter pour que le logiciel fonctionne comme prévu. Je vous rassure, cette préparation ne sera pas trop difficile ; par la suite, ces bonnes habitudes deviendront une seconde nature.

Voici les actions de préparation minimales et obligatoires : Toutes les colonnes doivent avoir des titres, Des titres uniques pour chaque colonne Votre tableau ne contient pas de lignes ou de colonnes fusionnées Les données sont présentes dans toutes les lignes.

La liste suivante d'actions supplémentaires n'est pas obligatoire, mais elle vous aidera à tirer le meilleur parti de la fonction de tableau croisé dynamique d'Excel :

1. Pour les valeurs vides, laissez l'espace vide et n'écrivez pas N/A.

2. Formatez les dates de manière cohérente, et Excel les reconnaîtra correctement.

3. Donnez aux colonnes des noms qui reflètent précisément ce que contient le contenu de chaque cellule.

4. Utilisez la même orthographe pour les noms et les lieux, mais soyez cohérent ("Paris", "paris" et "PARIS" ne sont pas homogènes).

    

Étape 2 :Vous allez créer un tableau croisé dynamique.

Je vous recommande d'utiliser les données publiques pour créer une liste des emplacements et des adresses des musées en France. Une fois que vous avez téléchargé ce fichier, que vous l'avez enregistré sur votre ordinateur et que vous l'avez ouvert dans Excel, vous êtes prêt à travailler.

Dans l’onglet Insertion, cliquez sur “Tableau croisé dynamique” :

Cette fenêtre va apparaître:

Lorsque votre curseur est positionné sur l'une des valeurs d'un tableau, Excel le détecte et présélectionne toutes les colonnes et lignes. Pour que cette fonctionnalité fonctionne correctement, assurez-vous que vous n'avez pas de lignes vides dans votre tableau. Parce qu'Excel peut reconnaître qu'il s'agit d'une rangée de cellules vides, ce qui signifie la fin du tableau.

Avant de faire vos premiers pas avec TCD, assurez-vous que vous avez sélectionné l'ensemble du tableau en déplaçant les barres de défilement verticale et horizontale à leur position maximale.

Si vous avez suivi les instructions étape par étape, vous verrez qu'il y a maintenant une ligne vide dans votre fichier musée !

Cela aurait totalement faussé les résultats de l'analyse ! De plus, j'ai été surpris par le nombre de lignes dans ce qui semble être une sélection par défaut ($A$1:$O624), car il y a en fait 1254 lignes dans ce fichier. Vous devrez donc supprimer la ligne vide, puis recommencer.

Cette fois-ci, la sélection est correcte :

Attention ! C'est là que ça se joue !

Étape 3 : Petite explication de où vous en êtes

Vous avez devant vous un tableau qui va regrouper les données de vos deux tableaux et répondre à toutes vos questions.

Sur chacun des emplacements du tableau, vous pouvez placer des colonnes. Le type et le nombre de colonnes dépendent de l'emplacement où vous déposez des articles.

Déterminer les LIGNES

Commençons par LIGNES, en bas à gauche. En supprimant l'une de ces colonnes, le TCD analysera le contenu de la table initiale et présentera une liste contenant une représentation unique d'une manière ou d'une autre.

Créez des groupes à partir des données. Par exemple, pour le champ "NOUVELLES RÉGIONS", n'affichez qu'un seul nom de région, même s'il est répété sur 100 lignes différentes dans les données sources (et omettez tous les doublons).

Déterminer les VALEURS

Par exemple, lorsque vous saisissez une valeur dans la colonne "VALEUR" et que vous sélectionnez cette ligne, le TCD recherche des résultats numériques quel que soit le type de données qui y est stocké.

Pour les champs contenant des nombres, il calcule par défaut une somme. Pour tous les autres types de champs (texte, date, etc.), le nombre total de valeurs est calculé.

En combinant ces deux premiers axes, nous pouvons tracer le nombre de musées par région. Pour ce faire, glissez et déposez le champ "NOM DU MUSÉE" dans la case "VALEURS".

Déterminer les COLONNES

Ensuite, déplaçons-nous en haut à droite vers "COLONNES". C'est exactement comme LIGNES mais basé sur des colonnes. Soigneusement combinée aux LIGNES, vous pourrez diviser vos résultats selon un autre axe d'analyse (à condition que les attributs sélectionnés soient pertinents). Ici, il serait inutile de ventiler le résultat de chaque département par région, car tous les départements n'appartiennent qu'à une seule région et ne peuvent donc pas être consolidés.

Si nous avions eu accès aux tarifs d'entrée des musées de différentes régions, nous aurions pu voir combien de musées existent à un prix donné.

Déterminer les FILTRES

Enfin, en haut à gauche, nous avons "FILTRES". Déposer une colonne ici vous permettra de filtrer selon les valeurs qui étaient affichées à l'origine.

Par conséquent, si nous voulons nous assurer que certaines catégories de produits ne polluent pas les résultats lors de leur affichage initial sur notre site web, nous pouvons les laisser en place.

Étape 4 : Créez un tableau croisé dynamique pour répondre à vos besoins.

Vous devez être clair sur la question à laquelle vous essayez de répondre : Cherchez-vous le nombre de musées "par région et par département" (1) ou, "par département et par région" (2) ?

Si vous ne voyez pas la différence entre ces deux questions, voici une illustration :

Bien que le total soit le même, vous pouvez constater qu'il n'a pas été ventilé de la même manière.

Félicitations ! Vous avez franchi toutes les étapes qui mènent à la maîtrise des TCD. Dans les prochains articles, j'aborderai d'autres façons d'utiliser les TCD - et ce qu'ils signifient pour votre entreprise.

Pour aller plus loin:

Excel: formule DATEDIF pour calculer la différence entre deux dates

Excel: fonction logique SI.CONDITION

Excel: Faire un arrondi inférieur ou supérieur

Excel: Fonction DATE – ANNEE – MOIS – JOUR

Excel: premier jour, dernier jour

Excel: Faire une somme

Calcul d'une moyenne avec Excel

Excel: nommer une variable

Excel: les raccourcis claviers à connaître