Ian à Nashville obtient des données chaque jour à partir d'un téléchargement système. La colonne de date contient Date + Heure. Cela fait que le tableau croisé dynamique a plusieurs lignes par jour au lieu d'un résumé d'une cellule par jour.
Cet article présente trois façons de résoudre le problème.
La figure ci-dessous montre la colonne Date avec Date plus heure.
Une solution consiste à le corriger dans le tableau croisé dynamique.
- Faites glisser le champ Date vers la zone Lignes. Il apparaît avec Date + Heure.
- Sélectionnez une cellule contenant une date et une heure.
- Dans l'onglet Analyse des outils de tableau croisé dynamique, choisissez Champ de groupe.
- Dans la boîte de dialogue Regroupement, sélectionnez Jours. Désélectionnez les mois. Lorsque vous cliquez sur OK, le tableau croisé dynamique affiche une ligne par jour.
Une autre solution fastidieuse consiste à ajouter une nouvelle colonne après le téléchargement. Comme le montre cette figure, la formule de la colonne J est =INT(D2).
Une date + heure est un jour entier suivi d'une heure décimale. En utilisant la fonction INT pour obtenir la partie entière de date + heure, vous isolez la date.
Cela fonctionnera bien, mais vous devez maintenant ajouter la nouvelle colonne chaque fois que vous téléchargez les données.
Ma solution préférée est d'utiliser Power Query. Power Query ne fonctionne que dans les éditions Windows d'Excel. Vous ne pouvez pas utiliser Power Query sur Android, IOS ou Mac.
Power Query est intégré à Excel 2016. Pour Excel 2010 et 2013, vous pouvez télécharger Power Query à partir de Microsoft.
Commencez par convertir les données téléchargées en une table à l'aide de Ctrl + T. Puis, sous l'onglet Données, choisissez À partir de la table / plage.
Dans la grille Power Query, choisissez la colonne Date. Accédez à l'onglet Transformer. Ouvrez la liste déroulante Type de date et choisissez Date. Si vous recevez un message concernant l'étape Remplacer ou Nouvelle étape, choisissez Remplacer l'étape.
Sous l'onglet Accueil de Power Query, choisissez Fermer et charger.
Votre résultat apparaîtra sur une nouvelle feuille. Créez le tableau croisé dynamique à partir de cette feuille.
La prochaine fois que vous obtiendrez de nouvelles données, procédez comme suit:
- Collez les nouvelles données sur l'ensemble de données d'origine.
- Accédez à la feuille avec le résultat de Power Query. Élargissez le panneau de requête pour voir Actualiser. Cliquez sur Actualiser.
- Actualisez le tableau croisé dynamique.
Regarder la vidéo
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2203: tronquez la date et l'heure à une seule date.
Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui de Ian lors de mon séminaire Nashville Power Excel. Regardez ceci: Ian reçoit un téléchargement de son système tous les jours, et il a - dans la colonne Date - il a la date et l'heure, et cela gâche les choses parce que quand Ian crée un tableau croisé dynamique et insère des dates sur le côté gauche, au lieu d'avoir juste une ligne par date, il obtient une ligne pour chaque date et heure.
Maintenant, il serait possible de venir ici et de dire, Champ de groupe, et de regrouper ceci en Jour, cliquez sur OK. Et, vous savez, c'est quelques clics supplémentaires que je ne pense pas que nous devions faire. Et, donc, pour ce podcast, les prochains podcasts, nous allons jeter un œil à Power Query. Maintenant, il est intégré à Excel 2016 ici, sous Obtenir et transformer des données. Si 2010 ou 2013 est exécuté sous Windows et non sur un Mac, vous pouvez télécharger Power Query gratuitement à partir d'Excel. Et Power Query souhaite travailler sur une table ou une plage.
Je vais donc choisir une cellule de données, appuyez sur Ctrl + T pour le tableau, parfait. Et puis, dans mon onglet Power Query, Données, À partir de la table / plage, je vais choisir la colonne Date et Transformer, et dire que je veux que ce ne soit pas une date et une heure, mais juste une date. Je vais choisir Remplacer le courant, puis Accueil, Fermer et charger, et nous obtenons un tout nouveau formulaire de feuille qui convertit les données. Maintenant, à partir d'ici, bien sûr, nous pouvons résumer avec un pivot et ce sera parfait.
L'avantage, je pense, de cette méthode, Power Query, c'est quand Ian obtient plus de données… Donc ici j'ai de nouvelles données, je vais prendre ces nouvelles données, copier les données, et venir ici à mes anciennes données et coller juste en dessous. Et notez que la fin du marqueur de tableau se trouve actuellement dans la ligne 474. Lorsque je colle, la fin du marqueur de tableau se déplace vers le bas des nouvelles données. Bien? Et cette requête est écrite en fonction de la table. Bien? Donc, à mesure que la table grandit.
Maintenant, un problème ici est que si vous utilisez Power Query pour la première fois, Power Query commence aussi large - seulement la moitié de large - et vous ne pouvez pas voir l'icône Actualiser. Donc, vous devez le faire glisser ici comme ceci et obtenir cette superbe icône Actualiser, puis cliquez simplement sur Actualiser. À l'heure actuelle, 473 lignes sont chargées, et maintenant 563 lignes chargées. Créez votre tableau croisé dynamique et la vie est belle.
Maintenant, écoutez, je sais qu'il aurait été possible d'ajouter une nouvelle colonne ici, = INT (D2) - - de la date - copiez-la, copiez et collez les valeurs spéciales. Mais vous êtes obligé de faire cela tous les jours. Bien? Donc, bien qu'il y ait au moins trois façons différentes dont nous avons parlé dans cette vidéo pour le résoudre, pour moi, Power Query et, simplement, la possibilité de cliquer sur Actualiser, est la voie à suivre.
Power Query, j'en parle dans mon nouveau livre, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Récapitulation de l'épisode d'aujourd'hui: Ian à Nashville obtient des données d'un système tous les jours, la colonne Date a de manière ennuyeuse à la fois la date et l'heure - elle bousille ses tableaux croisés dynamiques, alors il doit… Eh bien, trois choses possibles: Premièrement, utiliser la fonction INT, copier, coller des valeurs; deuxièmement, laissez-le simplement comme date et heure, puis regroupez le tableau croisé dynamique par date; ou, une meilleure solution, Power Query, transforme l'ensemble de données téléchargé dans une table avec Ctrl + T, puis dans l'onglet Données, choisissez À partir de la table, sélectionnez la colonne Date et heure, accédez à l'onglet Transformer dans Power Query, convertissez à une date, fermer et charger, construire le tableau croisé dynamique à partir de cela. La prochaine fois que vous obtiendrez des données, collez-les dans le tableau d'origine, accédez au panneau de requête, cliquez sur Actualiser et vous êtes prêt à partir.
Hé, pour télécharger le classeur à partir de la vidéo d'aujourd'hui, visitez l'URL dans la description YouTube.
Je tiens à remercier Ian d'être venu à mon séminaire à Nashville, je tiens à vous remercier d'être passé. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger le fichier Excel
Pour télécharger le fichier Excel: date-time-to-date.xlsx
Pour améliorer encore le processus Power Query:
- Assurez-vous que le classeur téléchargé est toujours enregistré dans le même dossier avec le même nom.
- Commencez à partir d'un classeur vierge. Enregistrer sous avec un nom comme DownloadedFileTransformed.xlsx
- À partir du classeur vierge, données, à partir du fichier, à partir du classeur. Pointez sur le fichier de l'étape 1.
- Choisissez Nettoyer les données. Transformez la date / heure en date. Fermer et charger.
- Cliquez avec le bouton droit sur l'Editeur de requête et choisissez Propriétés. Choisissez Actualiser les données lors de l'ouverture du fichier.
- Chaque jour, va travailler. Prends ton café. Assurez-vous qu'un nouveau fichier pour # 1 se trouve dans le dossier.
- Ouvrez votre classeur DownloadFileTransformed.xlsx. Il aura les nouvelles données et les dates seront correctes.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"La validation est votre ami."
Duane Aubin
