Réaliser un tableau croisé dynamique sous excel

Imaginez que vous ayez une base de données importante à gérer.

Ici pour notre exemple j’ai extrais quelques réponses (à 4 questions) de 71 interviews d’une enquête d’opinion sur la perception des voyages chez les seniors (enquête que j’avais réalisée lors de ma 4 ème année d’étude universitaire avec 3 camarades de promotion)

Les 4 questions étaient : quel est votre âge ? votre sexe (on n’a pas eu à poser la question je vous rassure) ? Avez-vous voyagé au cours des 3 dernières années ? Si vous deviez organiser un voyage, durant quel mois de l’année partiriez vous (personne ne voulait partir en janvier)?

Pour créez notre tableau croisé dynamique il faut saisir les cellules A1 à E72 (71 entrées dans mon tableau) et aller dans outils et cliquer sur « Rapport de tableau croisé dynamique », comme dans la figure ci-dessous :

Ensuite Il y a 3 étapes pour construire le tableau.

Etape 1

Laissez cochées les deux premières réponses comme ceci :

Etape 2

Cliquez sur « suivant » si vous aviez déjà sélectionné vos cellules.

Etape 3

Laissez la case « Nouvelle Feuille » cochée et cliquez sur « Terminer ».

Sur votre nouvelle feuille, vous avez alors deux éléments : un tableau vierge et des variables.

Tout le jeu va consister à glisser-déposer nos variables dans notre tableau pour ne retenir que l’information qui nous intéresse.

Voici ce que je vous propose : nous allons croisé l’âge de l’individu avec sa « récence » de voyageur (oui s’il a voyagé au cours des 3 dernières années, non sinon). Pour cela il faut glisser-déposer ces deux variables à deux endroits différents : une dans le champs ligne, l’autre dans le champs colonne (à vous de chosir). Glissons-déposons d’abord la variable « âge » dans le champs ligne, puis « récence » dans le champs « colonne »:

Il ne reste qu’à déposer notre variable données au milieu de tout cela. Ici la donnée sera naturellement le nombre d’individus (nos 71 interviewés). Glissez-déposez donc la variable « individus » comme ceci :

Voici notre tableau final :

Analysons le ensemble, sur les interviewés de 80 ans et + , sur 25 interviewés, 13 ne sont pas partis en voyage au cours des 3 dernières années (soit plus de 50 % des interviewés). Si on regarde les autres classes d’âges : on est à moins de 25 % des interviewés qui ne sont pas partis récemment en voyage. Conclusion, plus on vieillit, moins on part en voyage à partir d’un certain âge (seuil à 80 ans ici), entre 60 et 80 ans, on a plus tendance à voyager.

Vous n’êtes pas convaincu par l’utilité des tableaux croisés dynamiques ?

Voici une autre exploîtation possible :
Regrouper ensembles des lignes communes

Vous vous souvenez de mon précédent tutoriel pour extraire la racine d’une url ?
Ce tutoriel m’a permis de construire ceci :

Et si pour chaque racine d’url, j’avais le nombre global de pages vues. On réédite les 3 étapes qu’on a vu précédemment (en sélectionnant l’ensemble des cellules de notre tableau). Pour le champ ligne, nous mettrons la variable « racine d’url », et pour le champ données nous mettrons la variable « pages vues ».

Le Blog de Jeanviet est la partie de mon site qui génère le plus de pages vues !

cliquez ici pour télécharger le dossier excel complet


advertising


55 commentaires à propos de “Réaliser un tableau croisé dynamique sous excel

  1. Super ces tutoriaux !!

    Est ce que vous savez comment faire pour qu’Excel ignore les 0 d’un graphique, ou que les valeurs d’entrée d’un graphique soient fonction d’une variable ??

    merci

  2. Salut Sébastien,

    J’ai une idée pour ton problème… tu me diras si ça répond à ta question.

    Tu as 3 cellules (A1, A2, A3)…

    dans A1 rentre cette valleur: #N/A
    dans A2 c’est la valeur que tu rentres pour actualiser tes graphs (elle peut être égal à 0 ou non)
    dans A3 c’est la valeur qui fait référence au graph (cellule liée à ton graph), rentre ceci: =SI(A2=0;A1;A2)

    Si en A2, ta valeur est nulle #N/A s’affichera en A3 et sera ignoré dans tes graphs par excel

  3. J’aimerais ajouter un champs calculé dans mon TCD qui tient compte d’aggrégation. J’aimerais donc un champs qui soit [Moyenne(x) + Moyenne(y)] et non Moyenne(x+y). Est-ce possible?

    Merci

  4. En fait, je n’ai pas le même nombre d’enregistrement pour x et y. J’ai beaucoup de cases vides. Donc je ne peux pas faire x+y. Dans un TCD en Access, il est possible de le faire avec « Création du total calculé » Je me demandais s’il existait quelque chose de semblable en Excel.

    Merci

  5. Salut
    j’ai un problème avec mon TCD, je voudrais mettre des données non chiffrable, et ça marche pas.
    Aurrais tu une solution.
    merci

  6. Bonjour,

    J’ai deux tableaux avec une colonne commune (mais le nombre total de lignes n’est pas égal, en effet la majorité des lignes de cette colonne commune sont partagées et certaines ne le sont pas).

    Ce que j’aimerais faire c’est croiser ces tableaux grâce à cette colonne commune pour les lignes existant dans les 2 tableaux.

    Par exemple, j’ai un traleau contenant une colonne avec des numéros de tél. Un autre tableau contient aussi une colonne numéros de tél. J’aimerais avoir un tableau final contenant les lignes communes de ces deux tableaux.

    J’espère avoir été claire. Est-ce que vous pourriez m’aider sur ce problème.

    Merci d’avance,
    Estelle

  7. Bonjour Jean. Bravo et merci pour tous ces bons conseils. Sur un graphique à bulles Excel (2003), est-il possible d’obtenir que la couleur de chaque bulle soit automatiquement issue de la feuille de données?

  8. Bonjour,
    J’utilise un tableau croisé dynamique où j’ai deux variables (deux dates différentes) et pour chaque variable deux autres (X et Y). A la fin du tableau il me propose le total de X et le total de Y, mais j’aimerai qu’il me fasse l’évolution soit Y-X. Est-ce possible ?

  9. thierry,

    j’imagine qu’on doit pouvoir définir des couleurs selon les tailles des bulles…
    faudrait puiser ici (bulles):
    http://astuces.jeanviet.info/bureautique/excel-2007-realiser-un-graphique-a-bulles-en-toute-simplicite.htm
    puis ici (couleur en fonction de taille):
    http://astuces.jeanviet.info/bureautique/excel-2007-creer-des-graphiques-qui-reagissent-a-vos-donnees.htm

    Mumutu,

    ca doit être jouable… je regarde comment je m’en sors.. je reposte ensuite

  10. Bonjour

    J’ai une question pour les tableaux croisés dynamique:

    Dans le champs DONNEES,j’aimerais affiché du texte et non des valeures. Est-ce possible?

    J’aimerais simplement depuis mon onglet où j’ai toutes mes données le transformer en un tableau sans rien changer, ni de calculation

    Merci d’avance

  11. Merci beaucoup, j’ai visité un paquet de tutoriaux pour ça… et je viens de saisir grâce à vous. Tant mieux, j’ai un exam lundi où j’aurais ça à réaliser… Bien joué!

  12. bonjour,

    je voudrais savoir comment revenir en arriere aprés avoir placé un variable dans donnée.je n’arrive plus à annuler ce que j’ai placé dans la case donnée;

    merci de votre assistance,
    à bientôt

  13. re

    aprés avoir fini la disposition du tcd; peut-on déplacer les colonnes? pour une mise en forme perso des colonnes, merci
    cordialement
    reh

  14. biensûr… tu peux glisser déposer tes variables comme bon te semble (ligne, colonne)… dès que je l’ai temps, je te prépare une petite illustration video.

  15. merci de mavoir repondus
    en fait dans mon tableau jai le total qui se trouve a la fin de mon tableau ,colonne z;et as
    j’essaie de faire glisser ou inserer des lignes impossible;

    a bientôt;
    ps:ton dite est super

  16. j’aimerais savoir si on peut utiliser ce genre de tableaux avec un flux web ?
    j’explique : j’utilise un outil qui me fourni les états des test que j’effectue (validé, en cours, ouvert, fermé….) et j’aimerais reprendre ses résulats sous formes de graphiques,
    mes résultats me sont communiqué par mon outil via des liens ASP de ce type :

    urldelapplication/Default.aspx?status_id=12&assigned_by=&assigned_to=&issue_name=&priority_id=&

    merci d’avance pour votre aide et bonne fin de journée.

  17. Bonjour,

    Je souhaite mettre des variables dans mon tableau croisé dynamique, afin que l’utilisateur puisse saisir la date de début et la date de fin. le tableau affichera alors toutes les données entre cet intervalle de date.
    Cependant, le bouton parametre est grisé…pouvez vous me renseigner?

    Merci d’avance
    Cécile

  18. Bonjour , pourrais tu me faire un tableau croisé dynamique avec sur la 1 feuille

    Nom Prénom Date de naissance

    La 2 feuille

    Adresse tél. Mail Représentant légal N allocataire Tarifs

    Merci d’avance de ta réponse.

  19. Bonjour,

    je souhaite faire un tableau Excel ou quelque chose dans le genre qui consiste à calculer un effort vertical qui varie en fonction de 4 variables. Est ce que cela est possible sous Excel ?

    Merci d’avance

  20. super le tableau croisé dynamique d’excel. Cependant, je me demande s’il est possible de pouvoir modifier la valeur affichée par une cellule selon une condition particulière.
    Merci d’avance.

  21. Bonjour,

    Besoin d’aide pour réaliser un tableau kilométriques. en ordonnée NOM DE VILLE et en abscisse les mêmes NOM DE VILLE et à l’intérieur les kilomètres qui séparent 2 villes. Ex : NANTES à PORNIC : 55. j’ai 220 communes, aussi j’ai besoin de faire une recherche rapide entre les 2 villes choisies.
    Si vous avez une solution. merci d’avance

  22. Bonjour,
    je voudrais creér une même formule pour différents « éléments calculés » en même temps. J’ai essayé avec CTRL en sélectionnant les éléments que je veux mais ça ne marche pas.
    D’autre part, dans la base de données source, si l’on supprime des données, ces données restent affichées quand on clique sur les triangles à côté des champs
    Merci de votre aide

  23. Bonjour,

    J’ai un tableau excel avec des données en heure/minute, calculées avec une macro du type sexa(décim).
    Quand je mets à jour le TCD associé, les minutes apparaissent en décimales et non pas en sexadécimales.
    Comment faire pour que les données du TCD soient identiques au tableau excel ? Merci de votre aide.

  24. je ve une solution comment passer une ligne ds TCD contenant une inforamation sur une personne ds une autre feuille de calcul . svp je ve la reponse en urgent

  25. JE veux comment faire un petit logiciel en exel ;j’ai un tableau excel avec des données qui contien case (magasin);case (nature produit) et case (journée du).svp je ve la reponse en urgent

  26. Bonjour,

    Je travaille avec OPEN OFFICE et je ne trouve pas dans outils « rapport de tableau croisé dynamique ». Ou dois-je aller pour réaliser mon TCD?

    Merci

  27. bonjour,

    je voudrais savoir comment puis-je envoyer des sms gratuit apartir de mon échéancier sur excel merci de me répondre.

  28. Bonjour,
    Je voudrais savoir si on peut faire des tranches d’âges par exemple si on a une bdd avec des âges pour réaliser un TCD???
    Merci de votre aide

  29. salut jeanviet,
    je souhaiterais faire un seul TCD de plusieurs feuilles de données. Exemple, une feuille contient des données du mois de janvier (salaires,primes,nbre de tickets resto,…), une autre de février, de mars, d’avril, …..etc;Comment faire un seul TCD qui cumule les données de toutes ces feuilles (voir fasse la moyenne de certaines données de toutes ces feuilles)?
    Je viens juste de découvrir ce TCD, et a peu près réussi a en créer un d’après une seule feuille de données.
    Merci à toi l’ami.

  30. Bonjour,

    J’ai une question sur les TCD => je souhaiterai connaître le nombre de références différentes. J’utlise donc la fonction nmbre dans le tcd. Or il apparait autant de références différentes que de lignes, comme si le tcd n’arrivait pas à faire le regroupement de plusieurs lignes avec la même référence.

    Sauriez vous résoudre, SVP, mon problème

    En espérant avoir été clair,

    Merci

  31. Bonjour,

    J’aimerai concaténer des données textes dans les cellules valeur du TCD au lieu de faire un somme ou autre opération, est-ce possible ?

    Grand merci

  32. Bonjour,

    J’ai voulu faire un Tableau Croisé Dynamique (TCD), mais le message suivant est apparu « le nom du champ dynamique n’est pas valable. Pour créer un TCD,vous devez utiliser des données sous forme de liste avec des étiquettes de colonne. Si vous changer le nom d’un champ dynamique , il vous faut taper un nouveau nom pour le champ ». Quesako ??? Comment m’en sortir ? A l’aide !!!!

  33. Bonsoir Madame, je suis chargé de travaux dirigés à l’Université d’Abomey-Calavi (BENIN) et souhaiterais bénéficier de vos expertises en Excel pour améliorer ma connaissance en la matière et mieux faire face aux responsabilités qui m’incombent.
    En effet, je suis en Faculté de Sciences économiques et de gestion.
    Merci pour votre disponibilité.
    A bientôt.Mon nom HOUNSOSSOU Victor

  34. Bonjour,
    J’ai créé un tableau simple sur Excel relié a un compte de résultat et un tableau de soldes intermédiaires de gestion. J’ai fait les graphiques correspondant à mon tableau. Malheureusement quand je change les données au départ il n’y a que le graphique qui ne change pas. J’ai pensé a un tableau dynamique mais je ne sais pas si c’est la bonne solution. Quelqu’un pourrait-t’il m’aider je dois concevoir ces graphiques automatisés pour vendredi prochain pour mon patron.
    Merci d’avance
    Aline

  35. Bonjour,
    J’ai un fichier de données à partir duquel je crée plusieurs TCD.
    Ce fichier va être modifié régulièrement et on va y ajouter des lignes, de ce fait je crée mes tableaux avec $A$dernière colonne sinon lors de la mise à jour des TCD je n’ai pas les nouvelles lignes.
    Dans le TCD il crée alors des lignes et colonnes vides que je masque : mais je ne peux pas grouper les champs.
    Y-a-t-il une solution ?

  36. Bonjour et merci pour le cours sur les TCD que j’essaye de mettre en œuvre.
    Voilà mon problème, j’ai créé un Logbook sous excel (logbook = me permet de suivre rentrer mes heures de vols en avion).
    J’ai une colonne : Date, une colonne heure de vol en double commande et une dernière colonne : heure de vol en solo.
    Dans la colonne date, je peux avoir volé plusieurs fois dans le mois et bien sur dans l’année.
    Au travers d’un TCD, j’aimerai pouvoir visualiser mes heures de vols par mois sur une année et ce pour chaque année.
    Comment puis je regrouper dans ce tableau les heures de vol par moi pour une année ?
    Merci de votre aide

  37. Bonjour

    Comme Fabio, J’ai une question pour les tableaux croisés dynamique:

    Dans le champs DONNEES,j’aimerais affiché du texte et non des valeures. Est-ce possible?

    J’aimerais simplement depuis mon onglet où j’ai toutes mes données le transformer en un tableau sans rien changer, ni de calculation

    Merci d’avance

  38. Bonjour, félicitation et merci pour votre aide, c’est super comme aide..
    Je veux faire un tdc, dans les colonnes, j’ai plusieures données. juske la, pas de soucis.
    de ces colonnes, j’en ai quelques unes sur lesquelles je veux instaurer des criteres…

    ex: dans la colonne dépense X, je veux les periodes de depenses( mois et année) pour faire en sorte d’avoir les depenses qui correspondent a la periode concernée, comme un mois, un trimestre ou une année.

    il faut aussi que je puisse incorporé, des quantités.

    ex: j ai depensé 2000 pour acheté 100 produits…je veux que cela soit renseigné!

    comment faire.

    merci d’avance pour votre aide.

  39. Bonjour,

    Je voudrai savoir s’il est possible d’avoir un exercice d’application à faire chez soit pour créer un tableau dynamique croisé sur Excel (2003)?

    Merci

  40. Bonjour,
    ma base de données est une suite d’horaires de travail mensuels pour une équipe de 8 personnes. Les entêtes de colonnes représentent les tâches à accomplir; les lignes représentent les jours du mois. Dans chacune des cellules des tableaux figure le nom du travailleur affecté à la dite tâche tel jour du mois (cela change chaque jour). Je cherche une façon de comptabiliser les nombre de jours/tâches par travailleur, de façon à équilibrer la charge de travail de chacun sur le long terme. Les TCD me permettent d’obtenir les données par tâche, par travailleur, mais une seule tâche à la fois. Je fais le transfert de ces données dans un nouveau tableau, une tâche à la fois. C’est fastidieux et source d’erreur, d’autant plus que l’absence temporaire d’un travailleur modifie la suite de noms que j’utilise de façon automatique. Qu’avez-vous à me proposer?
    J Rivest

  41. Bonjour,

    J’ai créé un TCD et ce qui m’embete c’est que celui ci ne récupère pas les commentaires que l’on insere dans une cellule ( avec le petit coin rouge).
    Est ce une action possible, sinon y t’il un moyen de les récuperer

    Je vous remercie de votre réponse
    bonne journée

  42. Bonjour,

    Pour un travail universitaire je dois analyser les réponses d’un questionnaire que j’ai réalisé. Les réponses sont présentes sous forme de tableau sur Excel. Mais lorsque je veux voir les résultats sur tableau croisé dynamique cela ne fonctionne pas. Pourquoi ??

    Merci d’avance pour vos réponses..

  43. Bonjour,

    Pour un travail universitaire je dois analyser les réponses d’un questionnaire que j’ai réalisé. Les réponses sont présentes sous forme de tableau sur Excel. Mais lorsque je veux voir les résultats sur tableau croisé dynamique cela ne fonctionne pas. Pourquoi ??

    Merci d’avance pour vos réponses..

  44. Bonjour,

    Je tiens à vous remercier pour ce tutoriel … j’ai enfin réussi à faire un tableau croisé dynamique.

    Cordialement,

    Emmanuelle.