Commencez avec plusieurs années de données.
Disons que vous avez deux ans d'enregistrements détaillés. Chaque enregistrement a une date quotidienne. Lorsque vous créez un tableau croisé dynamique à partir de ce rapport, vous aurez des centaines de lignes de dates quotidiennes dans le tableau croisé dynamique. Ce n'est pas vraiment un résumé.
Choisissez l'une de ces cellules de date dans le tableau croisé dynamique. Dans l'onglet Analyser du ruban, choisissez Champ de groupe.
Étant donné que vous êtes sur un champ de date, vous obtenez cette version de la boîte de dialogue de regroupement. Dans celui-ci, désélectionnez Mois et sélectionnez Années.
Les dates quotidiennes sont cumulées sur des années. Déplacez le champ Date de ROWS vers COLUMNS.
Le résultat est presque parfait. Mais au lieu d'un total général dans la colonne D, vous voulez probablement un pourcentage de variance.
Pour se débarrasser de la colonne Total général, dans l'onglet Conception, choisissez Totaux généraux, Activé pour les colonnes uniquement. (Je suis d'accord, c'est l'un des libellés les plus maladroits d'Excel.)
Pour créer la colonne de variance, vous devez écrire une formule en dehors du tableau croisé dynamique qui pointe à l'intérieur du tableau croisé dynamique. Ne touchez pas la souris ou les touches fléchées lors de la construction de la formule, sinon la fonction GETPIVOTDATA désagréable apparaîtra. Au lieu de cela, tapez simplement =C5/B5-1et appuyez sur Entrée.
Merci à Александр Воробьев d'avoir suggéré cette astuce.
Regarder la vidéo
- Commencez avec plusieurs années de données
- Insérer, tableau croisé dynamique
- Faites glisser le champ de date vers la zone de ligne
- Excel 2016: appuyez sur Ctrl + Z pour dissocier les dates
- Faites glisser les revenus vers la zone de valeurs
- Sélectionnez une date dans la zone de ligne
- Utiliser le champ de groupe
- Choisissez les mois et les années
- Comment ajouter des sous-totaux au champ Années après le regroupement d'un tableau croisé dynamique
- Utilisez la forme tabulaire dans un tableau croisé dynamique pour attribuer à chaque champ de ligne sa propre colonne
- Répétez toutes les étiquettes de ligne pour remplir les espaces vides dans un tableau croisé dynamique
- Faites glisser les années vers la zone de la colonne
- Cliquez avec le bouton droit sur l'en-tête de colonne Total général et supprimez
- Comment éviter la fonction GetPivotData lorsque la formule pointe vers un tableau croisé dynamique
- Pour créer une variance, tapez la formule sans souris ni flèches
- Merci à Александр Воробьев d'avoir suggéré cette astuce
Transcription vidéo
Apprenez Excel à partir du podcast MrExcel, épisode 1998. Tableau croisé dynamique année après année.
Je vais podcaster tout ce livre, allez, cliquez sur ce «i» dans le coin supérieur droit, pour la liste de lecture MrExcel Excel.
Salut, bon retour sur le netcast, je suis Bill Jelen. Aujourd'hui, le premier sujet du livre sur les tableaux croisés dynamiques et la façon de créer un rapport annuel. Nous commençons donc avec l'ensemble de données, qui s'étend sur deux ans, vous voyez, nous allons de 2018 à 2019. Je vais créer un tableau croisé dynamique, une insertion, un tableau croisé dynamique, OK, c'est bon, passons à un nouvelle feuille de calcul.
Sur la nouvelle feuille de calcul, nous allons prendre le champ Date et le faire glisser vers la zone Lignes. Et puis, vous voyez, dans Excel 2016, il regroupe automatiquement les choses. Maintenant, je sais que la plupart des gens sont toujours dans Excel 2013, peut-être Excel 2010, alors je vais appuyer sur Ctrl + Z pour annuler. C'est l'expérience que vous obtiendrez en 2010 et 2013, nous allons prendre le champ Chiffre d'affaires et le faire glisser vers la zone Valeurs. Nous commençons donc avec un rapport moche, il va y avoir des centaines et des centaines de lignes, car c'est chaque jour que nous avons des ventes. Mais je vais aller au tout premier champ de date, tout premier champ de date, aller aux outils de tableau croisé dynamique, analyser, en 2013 ou 2016 et 2010, il s'appelait Options, et choisissez Champ de groupe. Assurez-vous de choisir une date avant de choisir le champ de groupe et de regrouper les éléments en mois et en années. Ou trimestres et années,ou Mois et trimestres et années, cliquez sur OK. Et ce que nous obtenons, c'est un nouveau champ virtuel appelé Years ici dans la zone Rows.
Maintenant quelques ennuis. C'est ennuyeux qu'ils ne nous donnent pas les totaux annuels, c'est assez facile de résoudre ce problème, même si dans mon cas, je n'ai pas besoin de résoudre ce problème. Nous cliquons sur Paramètres de champ et Automatique, car je ne prévois pas de laisser les années ici. Bien sûr, chaque fois que je crée un tableau croisé dynamique, je vais dans Design et dis Afficher sous forme tabulaire pour tout mettre dans sa propre colonne. Et puis Répétez toutes les étiquettes d'article pour remplir ces espaces le long du côté gauche. Encore une fois, ce ne serait pas nécessaire ici, parce que je ne prévois pas de laisser les années où elles sont, je vais prendre les années et les faire glisser vers les colonnes, et c'est le rapport pour lequel je visais.
D'accord, nous avons donc des mois sur le côté gauche, de 2018 à 2019. Dans la colonne D, qu'est-ce que je veux vraiment, je ne veux pas d'un grand total, je vais faire un clic droit et dire Supprimer le grand Total et maintenant nous sommes en dehors d'un tableau croisé dynamique. Donc,% Change, très tentant de construire cette formule en utilisant la souris ou les touches fléchées, mais vous ne pouvez pas, car vous obtiendrez la fonction… get Pivot Data, qui ne copie pas facilement. Donc = C5 / B5-1, nous allons formater cela en pourcentage et double-cliquer pour le copier vers le bas, à droite. Beau rapport montrant où nous en étions l'année dernière, où nous en étions cette année et ensuite le pourcentage de variation d'une année à l'autre.
Eh bien, cette astuce et 40 autres sont toutes dans ce livre. Allez-y, cliquez sur ce «i» dans le coin supérieur droit, à côté du livre. Très bien, donc vous commencez avec l'ensemble de données avec plusieurs années, faites Insertion, tableau croisé dynamique, faites glisser le champ de date vers la zone de ligne. Et dans Excel 2016, appuyez sur Ctrl + Z pour annuler leurs mises à jour de regroupement automatique. Pourquoi ne pas laisser ça ici? Eh bien, cela dépend en fait. Si vous avez des données du 2 janvier au 30 décembre, disons, ils ne regrouperont pas ces données correctement. Il faut passer une année entière pour que vous obteniez le champ année. Il se peut que cela fonctionne pour vous, je suis un peu un maniaque du contrôle, je préfère simplement avoir le contrôle, c'est pourquoi je défais. Il y a: faites glisser le chiffre d'affaires vers la zone des valeurs, sélectionnez n'importe quelle cellule, n'importe quelle date dans la zone de ligne, puis Champ de groupe, choisissez Mois et années,faites glisser des années sur la zone de la colonne, cliquez avec le bouton droit sur le total général pour supprimer, puis créez une variance, tapez la formule, n'utilisez pas la souris ou les touches fléchées.
Je ne sais pas comment le prononcer, mais merci à ce lecteur (Александр Воробьев) d'avoir suggéré cette astuce et merci à vous d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast1998.xlsx
