Mois à ce jour - Conseils Excel

Comment afficher les ventes mensuelles dans un tableau croisé dynamique. Ceci est un épisode Dueling Excel.

Regarder la vidéo

Méthode de Bill
Ajouter une cellule d'assistance avec une formule MTD
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
Ajoutez ce champ en tant que segment où = True
Conseil bonus: Dates quotidiennes du groupe jusqu'à des années
Ajouter un calcul en dehors du tableau croisé dynamique tout en évitant GetPivotData
L'approche de Mike:
Transformez les données en tableau en utilisant Ctrl + T. Cela permet d'ajouter plus de données au tableau et de mettre à jour les formules.
SUMIFS avec fonctions DATE, MOIS, JOUR
Appuyez trois fois sur F4 pour verrouiller une référence à la colonne uniquement.
Attention, si vous faites glisser une formule de tableau sur le côté, les colonnes changent. Copier et coller - aucun problème
Utilisation de TEXT (date, format. Belle astuce avec 1 pour insérer le numéro 1 dans le texte

Transcription vidéo

Bill Jelen: Salut, bon retour. Il est temps pour un autre podcast Dueling Excel. Je suis Bill Jelen. Je serai accompagné de Mike Girvin d'Excel Is Fun.

Ceci est notre Épisode 181: Tableau croisé dynamique mensuel.

Eh bien, la question d'aujourd'hui - l'idée d'aujourd'hui pour ce duel est envoyée par Mike. Il dit: "Pouvez-vous créer un rapport mensuel dans un tableau croisé dynamique?"

Bon, allons-y. Alors voici ce que nous avons, nous avons deux ans de dates à partir de janvier 2016 jusqu'en 2017. Maintenant, bien sûr, j'enregistre ceci en avril, c'est le 15 avril en ce moment où j'enregistre mon morceau du duel. Et donc ici, nous avons un tableau croisé dynamique montrant les jours sur le côté gauche, la catégorie en haut et les revenus au cœur du tableau croisé dynamique.

Maintenant, pour créer un rapport mensuel, ce que je vais faire est de dire que je vais ajouter une nouvelle colonne d'aide ici à mes données d'origine et cela va vérifier deux choses. Et comme je vérifie deux choses, je vais utiliser la fonction ET, les deux choses doivent être vraies pour que ce soit le mois en cours. Et je vais utiliser ici une fonction appelée AUJOURD'HUI. AUJOURD'HUI, d'accord, donc je veux savoir si le MOIS D'AUJOURD'HUI ()) est = au MOIS de cette date là-bas dans la colonne A. Si c'est vrai, si c'est le mois en cours, donc en d'autres termes si c'est avril, alors vérifier et voir si le jour de cette date là-bas dans A2 est <= DAY of TODAY. La belle chose est que lorsque nous ouvrons ce classeur demain ou dans une semaine, le jour d'aujourd'hui sera automatiquement mis à jour et nous double-cliquons pour le copier.

Très bien maintenant, nous devons obtenir ces données supplémentaires dans notre tableau croisé dynamique alors je viens ici Tableau croisé dynamique, Analyser et ce n'est pas si difficile de changer la source de données, cliquez simplement sur ce gros bouton et dites que nous voulons aller à la colonne D , cliquez sur OK. Très bien, donc maintenant que nous avons ce champ supplémentaire, je vais insérer un segment basé sur ce champ de mois en cours et je veux seulement voir comment notre mois à ce jour est vrai. Maintenant, avons-nous besoin que cette tranche soit aussi grande? Non, nous pouvons probablement en faire deux colonnes et l'avoir en quelque sorte discrète sur le côté droit. Alors maintenant, nous avons toutes les dates en 2016 et toutes les dates en 2017; bien que ce serait vraiment cool de comparer ces éléments côte à côte. Je vais donc prendre ce champ Date et analyser. Je vais grouper le terrain, je vais le grouper jusqu'à quelques années. Je net vraiment se soucier des jours individuels. Je veux juste connaître le mois à ce jour. Maintenant, où sommes-nous? Je vais donc le regrouper en Années et nous finirons avec ces 2 années là-bas et je vais ensuite réorganiser cela, mettre ces Années à traverser, Catégories à descendre. Et maintenant, je vois où nous en étions l'année dernière et où nous en étions cette année. Très bien maintenant, car j'ai terminé le regroupement, je ne suis plus autorisé à créer un champ calculé à l'intérieur du tableau croisé dynamique. Si je voulais avoir un montant d'une année à l'autre là-bas, je ferais un clic droit, Supprimer le total général, d'accord, et maintenant nous sommes, donc,% de changement, nous sommes en dehors d'un tableau croisé dynamique pointant à l'intérieur du tableau croisé dynamique . Nous devons nous assurer de désactiver GetPivotData ou de simplement créer une formule comme celle-ci: = J4 / I4-1 et cela crée une formule que nous pouvons copier sans aucun problème, comme ça.Très bien, Mike, voyons ce que vous avez.

Mike Girvin: Merci ,. Oui, j'ai envoyé la question à parce que je l'ai fait avec des formules et que je ne savais pas comment le faire avec un tableau croisé dynamique standard, puis je me suis souvenu avoir vu au fil des ans, faire un tas de vidéos sympas sur les colonnes d'aide et les tableaux croisés dynamiques . C'est une belle formule et une belle solution. Voilà donc comment le faire avec un tableau croisé dynamique, allons voir comment le faire avec une formule.

Maintenant, je fais ça deux jours après qu'il l'a fait. F2 J'ai la fonction AUJOURD'HUI qui sera toujours l'information de date pour la date actuelle d'aujourd'hui qui sera utilisée par les formules ici parce que nous voulons qu'elle soit mise à jour. J'ai également utilisé un tableau Excel et il s'appelle FSales. Si je Ctrl + Flèche bas, je vois que c'est le 14/04 mais je veux pouvoir ajouter les derniers enregistrements et inclure la mise à jour de nos formules lorsque nous passons au mois suivant. Ctrl + Flèche vers le haut. Très bien, j'ai des critères d'année comme en-têtes de colonne, la catégorie comme en-têtes de ligne, puis les détails du mois et du jour proviendront de cette cellule. Je vais donc simplement utiliser la fonction SUMIFS puisque nous ajoutons avec plusieurs conditions, la plage de somme ici est le chiffre d'affaires, nous allons utiliser cette excellente astuce pour un tableau Excel.Tout en haut, nous voyons cette flèche noire pointant vers le bas, BAM! Cela met dans le nom de table approprié, puis entre crochets le nom du champ, virgule. Plage de critères, nous allons devoir utiliser Date deux fois, donc je vais commencer par Date. Cliquez, il y a la colonne de date, virgule. Maintenant, je suis en avril, donc je dois créer la condition> = au 1er avril. Donc, les opérateurs comparatifs «> =» entre guillemets et je vais le rejoindre. Maintenant, je dois créer une formule de date qui regarde toujours ici et crée le premier du mois pour cette année particulière. Je vais donc utiliser la fonction DATE. Année, eh bien, j'ai l'année à droite comme en-tête de colonne et je vais appuyer sur la touche F4 une, deux fois pour verrouiller la ligne mais pas la colonne, donc quand elle se déplace ici, nous passerons à 2017, virgule, la Mois - je 'Je vais utiliser la fonction MONTH pour obtenir le numéro du mois 1 à 12. C'est le mois qui se trouve dans cette cellule, F4 pour le verrouiller dans toutes les directions, fermez les parenthèses puis la virgule, 1 ce sera toujours le 1er du mois quel que soit le mois, fermez les parenthèses.

D'accord, c'est donc le critère. Ce sera toujours> = le premier du mois, virgule, plage de critères deux Je vais obtenir ma colonne Date, virgule. Critère deux, eh bien, cela va être <= la limite supérieure, donc dans «<=» et le &. Je vais tricher, regardez ça. Je vais juste copier ceci à partir d'ici car c'est la même chose, Ctrl-C Ctrl-V sauf pour le jour, nous devons utiliser la fonction JOUR et toujours obtenir comme limite supérieure quel que soit le jour de ce mois particulier . F4 pour le verrouiller dans toutes les directions, fermez les parenthèses sur Date. Très bien, c'est donc notre deuxième critère: la virgule. La plage de critères 3, c'est la catégorie. Voilà, virgule et il y a notre en-tête de ligne. Donc celui-ci, nous devons F4 un deux trois fois, verrouiller la colonne mais pas la ligne, donc lorsque nous copions la formule vers le bas, nous passerons à Gizmo et Widget,fermer les parenthèses et c'est la formule. Faites glisser, double-cliquez et envoyez-le vers le bas. Je peux voir qu'il y a des problèmes. Je ferais mieux de venir à la dernière cellule en diagonale la plus éloignée. Appuyez sur F2. Désormais, le comportement par défaut de la nomenclature des formules de tableau est que lorsque vous copiez les formules sur le côté, les colonnes réelles se déplacent comme s'il s'agissait de références de cellules mixtes. Maintenant, nous pourrions les verrouiller mais je ne vais pas le faire cette fois. Maintenant, remarquez que lorsque vous le copiez, cela fonctionne bien, mais lorsque vous copiez sur le côté, c'est là que les colonnes réelles se déplacent. Alors regardez ça, je vais Ctrl + C et Ctrl + V et cela évite que F aux colonnes ne bouge lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.Faites glisser, double-cliquez et envoyez-le vers le bas. Je peux voir qu'il y a des problèmes. Je ferais mieux de venir à la dernière cellule en diagonale la plus éloignée. Appuyez sur F2. Désormais, le comportement par défaut de la nomenclature des formules de tableau est que lorsque vous copiez les formules sur le côté, les colonnes réelles se déplacent comme s'il s'agissait de références de cellules mixtes. Maintenant, nous pourrions les verrouiller mais je ne vais pas le faire cette fois. Maintenant, remarquez que lorsque vous le copiez, cela fonctionne bien, mais lorsque vous copiez sur le côté, c'est là que les colonnes réelles se déplacent. Alors regardez ça, je vais Ctrl + C et Ctrl + V et cela évite que F aux colonnes ne bouge lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.Faites glisser, double-cliquez et envoyez-le vers le bas. Je peux voir qu'il y a des problèmes. Je ferais mieux de venir à la dernière cellule en diagonale la plus éloignée. Appuyez sur F2. Désormais, le comportement par défaut de la nomenclature des formules de tableau est que lorsque vous copiez les formules sur le côté, les colonnes réelles se déplacent comme s'il s'agissait de références de cellules mixtes. Maintenant, nous pourrions les verrouiller mais je ne vais pas le faire cette fois. Maintenant, remarquez que lorsque vous le copiez, cela fonctionne bien, mais lorsque vous copiez sur le côté, c'est là que les colonnes réelles se déplacent. Alors regardez ça, je vais Ctrl + C et Ctrl + V et cela évite que F aux colonnes ne bouge lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.Je ferais mieux de venir à la dernière cellule en diagonale la plus éloignée. Appuyez sur F2. Désormais, le comportement par défaut de la nomenclature des formules de tableau est que lorsque vous copiez les formules sur le côté, les colonnes réelles se déplacent comme s'il s'agissait de références de cellules mixtes. Maintenant, nous pourrions les verrouiller mais je ne vais pas le faire cette fois. Maintenant, remarquez que lorsque vous le copiez, cela fonctionne bien, mais lorsque vous copiez sur le côté, c'est là que les colonnes réelles se déplacent. Alors regardez ça, je vais Ctrl + C et Ctrl + V et cela évite que F aux colonnes ne bouge lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.Je ferais mieux de venir à la dernière cellule en diagonale la plus éloignée. Appuyez sur F2. Désormais, le comportement par défaut de la nomenclature des formules de tableau est que lorsque vous copiez les formules sur le côté, les colonnes réelles se déplacent comme s'il s'agissait de références de cellules mixtes. Maintenant, nous pourrions les verrouiller mais je ne vais pas le faire cette fois. Maintenant, remarquez que lorsque vous le copiez, cela fonctionne bien, mais lorsque vous copiez sur le côté, c'est là que les colonnes réelles se déplacent. Alors regardez ça, je vais Ctrl + C et Ctrl + V et cela évite que F aux colonnes ne bouge lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.les colonnes réelles se déplacent comme s'il s'agissait de références de cellules mixtes. Maintenant, nous pourrions les verrouiller mais je ne vais pas le faire cette fois. Maintenant, remarquez que lorsque vous le copiez, cela fonctionne bien, mais lorsque vous copiez sur le côté, c'est là que les colonnes réelles se déplacent. Alors regardez ça, je vais Ctrl + C et Ctrl + V et cela évite que F aux colonnes ne bouge lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.les colonnes réelles se déplacent comme s'il s'agissait de références de cellules mixtes. Maintenant, nous pourrions les verrouiller mais je ne vais pas le faire cette fois. Maintenant, remarquez que lorsque vous le copiez, cela fonctionne bien, mais lorsque vous copiez sur le côté, c'est là que les colonnes réelles se déplacent. Alors regardez ça, je vais Ctrl + C et Ctrl + V et cela évite que F aux colonnes ne bouge lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.m aller à Ctrl + C et Ctrl + V et puis cela évite à F aux colonnes de se déplacer lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.m aller à Ctrl + C et Ctrl + V et puis cela évite à F aux colonnes de se déplacer lorsque vous le copiez sur le côté. Double-cliquez et envoyez-le. Maintenant, notre formule% Change = le montant final / le montant initial -1, Ctrl + Entrée, double-cliquez et envoyez-le vers le bas.

Maintenant, avant de le tester, ajoutez maintenant de nouveaux enregistrements. En fait, je veux créer ce label ici pour qu'il soit dynamique. Et la façon dont je vais faire c'est que je vais dire = signe et nous allons faire une formule de texte donc chaque fois que nous voulons du texte et une formule, vous devez le mettre: "et je suis va taper Ventes entre, espace »et maintenant je dois extraire de cette date unique là-bas, le premier du mois à la fin du mois. Je vais utiliser la fonction TEXT. La fonction TEXTE peut prendre un nombre, des dates ou des numéros de série, une virgule et utiliser un formatage de numéro personnalisé dans ”. Je veux toujours voir une abréviation à trois lettres pour le mois, mmm, je veux toujours qu'elle soit la première. Maintenant, si je mets un 1 ici, espace virgule yyy, cela ne fonctionnera pas. Veut voir que cela nous donne une valeur ou parce que ça n'aime pas ça 1. Mais nous 're autorisé à insérer un seul caractère si nous utilisons une barre oblique, c'est dans le formatage des nombres personnalisés. Le mm et le aa seront compris par le formatage des nombres personnalisés comme mois et année et maintenant le format des nombres personnalisés comprendra l'insertion du nombre 1. F2 et maintenant nous allons simplement: & "-" & TEXT de cette virgule et maintenant utilisera simplement le formatage des nombres simples: «mmm spaceD, yyy») Ctrl + Entrée.

Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.

That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.

Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.

And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.

Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.

D'accord, eh bien, je tiens à remercier tout le monde d'être passé. Nous vous reverrons la prochaine fois pour un autre podcast Dueling Excel de et Excel Is Fun.

Télécharger un fichier

Téléchargez l'exemple de fichier ici: Duel181.xlsm