Ο Ian στο Νάσβιλ λαμβάνει δεδομένα κάθε μέρα από μια λήψη συστήματος. Η στήλη ημερομηνίας περιέχει Ημερομηνία + Ώρα. Αυτό κάνει τον συγκεντρωτικό πίνακα να έχει πολλές σειρές την ημέρα αντί για σύνοψη ενός κελιού την ημέρα.
Αυτό το άρθρο δείχνει τρεις τρόπους επίλυσης του προβλήματος.
Το παρακάτω σχήμα δείχνει τη στήλη Ημερομηνία με Ημερομηνία συν ώρα.
Μια λύση είναι να το διορθώσετε στον συγκεντρωτικό πίνακα.
- Σύρετε το πεδίο Ημερομηνία στην περιοχή Σειρές. Εμφανίζεται με ημερομηνία + ώρα.
- Επιλέξτε οποιοδήποτε κελί που περιέχει ημερομηνία και ώρα.
- Στην καρτέλα Ανάλυση εργαλείων συγκεντρωτικού πίνακα, επιλέξτε Πεδίο ομάδας.
- Στο παράθυρο διαλόγου Ομαδοποίηση, επιλέξτε Ημέρες. Αποεπιλογή μηνών. Όταν κάνετε κλικ στο OK, ο συγκεντρωτικός πίνακας θα εμφανίζει μία σειρά την ημέρα.
Μια άλλη κουραστική λύση είναι να προσθέσετε μια νέα στήλη μετά τη λήψη. Όπως φαίνεται σε αυτό το σχήμα, ο τύπος στη στήλη J είναι =INT(D2).
Η ημερομηνία + ώρα είναι μια ακέραια ημέρα ακολουθούμενη από μια δεκαδική ώρα. Χρησιμοποιώντας τη συνάρτηση INT για να λάβετε το ακέραιο τμήμα της ημερομηνίας + ώρας, απομονώνετε την ημερομηνία.
Αυτό θα λειτουργήσει καλά, αλλά τώρα πρέπει να προσθέτετε τη νέα στήλη κάθε φορά που κάνετε λήψη των δεδομένων.
Η προτιμώμενη λύση μου είναι να χρησιμοποιήσω το Power Query. Το Power Query λειτουργεί μόνο σε εκδόσεις Windows του Excel. Δεν μπορείτε να χρησιμοποιήσετε το Power Query σε Android, IOS ή Mac.
Το Power Query είναι ενσωματωμένο στο Excel 2016. Για το Excel 2010 και 2013, μπορείτε να κάνετε λήψη του Power Query από τη Microsoft.
Αρχικά, μετατρέψτε τα ληφθέντα δεδομένα σε πίνακα χρησιμοποιώντας Ctrl + T. Στη συνέχεια, στην καρτέλα Δεδομένα, επιλέξτε Από πίνακα / εύρος.
Στο πλέγμα Power Query, επιλέξτε τη στήλη Ημερομηνία. Μεταβείτε στην καρτέλα Μετασχηματισμός. Ανοίξτε το αναπτυσσόμενο μενού Τύπος ημερομηνίας και επιλέξτε Ημερομηνία. Εάν λάβετε ένα μήνυμα σχετικά με το Replace Step ή το New Step, επιλέξτε Replace Step.
Στην καρτέλα Αρχική σελίδα του Power Query, επιλέξτε Κλείσιμο και Φόρτωση.
Το αποτέλεσμα θα εμφανιστεί σε ένα νέο φύλλο. Δημιουργήστε τον συγκεντρωτικό πίνακα από αυτό το φύλλο.
Την επόμενη φορά που θα λάβετε νέα δεδομένα, ακολουθήστε τα εξής βήματα:
- Επικολλήστε τα νέα δεδομένα πάνω από το αρχικό σύνολο δεδομένων.
- Μεταβείτε στο φύλλο με το αποτέλεσμα του Power Query. Διευρύνετε τον πίνακα ερωτημάτων, ώστε να μπορείτε να δείτε Ανανέωση. Κάντε κλικ στην επιλογή Ανανέωση.
- Ανανεώστε τον συγκεντρωτικό πίνακα.
Δες το βίντεο
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, επεισόδιο 2203: Περικοπή ημερομηνίας και ώρας σε μια ημερομηνία.
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση από τον Ian στο σεμινάριο του Nashville Power Excel. Κοιτάξτε αυτό: Ο Ian λαμβάνει μια λήψη από το σύστημά του κάθε μέρα και έχει - στη στήλη Ημερομηνία - έχει Ημερομηνία και Ώρα, και αυτό βιδώνει τα πράγματα επειδή όταν ο Ian δημιουργεί έναν Συγκεντρωτικό Πίνακα και βάζει τις Ημερομηνίες μαζί στην αριστερή πλευρά, αντί να παίρνει μόνο μια σειρά ανά ημερομηνία, παίρνει μία σειρά για κάθε ημερομηνία και ώρα.
Τώρα, θα ήταν δυνατό να έρθεις εδώ και να πεις, Group Field και να το ομαδοποιήσεις σε Day, κάντε κλικ στο OK. Και, ξέρετε, αυτά είναι μερικά επιπλέον κλικ που δεν νομίζω ότι πρέπει να κάνουμε. Και, λοιπόν, για αυτό το Podcast, τα επόμενα Podcasts, θα ρίξουμε μια ματιά στο Power Query. Τώρα, είναι ενσωματωμένο στο Excel 2016 εδώ, στην ενότητα Get & Transform Data. Εάν έχετε 2010 ή 2013 σε Windows, όχι σε Mac, μπορείτε να κατεβάσετε το Power Query δωρεάν από το Excel. Και το Power Query θέλει να εργαστεί σε έναν πίνακα ή εύρος.
Θα επιλέξω λοιπόν ένα κελί δεδομένων, πατήστε Ctrl + T για Table, τέλεια. Και μετά, στην καρτέλα Power Query, Data, From Table / Range, θα επιλέξω τη στήλη Ημερομηνία και το Transform και θα πω ότι θέλω αυτό να μην είναι Ημερομηνία και Ώρα, αλλά μόνο Ημερομηνία. Θα επιλέξω Αντικατάσταση τρέχοντος και έπειτα Αρχική σελίδα, Κλείσιμο & φόρτωση και θα λάβουμε μια ολοκαίνουργια φόρμα φύλλου που έχει μετατρέψει τα δεδομένα. Τώρα, από εδώ, φυσικά, μπορούμε να συνοψίσουμε με έναν άξονα και θα είναι τέλειο.
Το πλεονέκτημα, νομίζω, από αυτήν τη μέθοδο, το Power Query, είναι όταν ο Ian παίρνει περισσότερα δεδομένα… Έτσι, έχω νέα δεδομένα, θα πάρω αυτά τα νέα δεδομένα, θα αντιγράψω τα δεδομένα και θα έρθω εδώ στα παλιά μου δεδομένα και επικολλήστε ακριβώς παρακάτω. Και, παρατηρήστε ότι το τέλος του δείκτη πίνακα είναι τώρα στη σειρά 474. Όταν επικολλήσω, το τέλος του δείκτη πίνακα μετακινείται προς τα κάτω στο κάτω μέρος των νέων δεδομένων. Καλώς? Και αυτό το ερώτημα γράφεται με βάση τον πίνακα. Καλώς? Έτσι, καθώς ο πίνακας μεγαλώνει.
Τώρα, μια ταλαιπωρία εδώ είναι, εάν χρησιμοποιείτε το Power Query για πρώτη φορά, το Power Query ξεκινά τόσο ευρύ - μόνο μισό πλάτος - και δεν μπορείτε να δείτε το εικονίδιο Ανανέωσης. Επομένως, πρέπει να το σύρετε εδώ έτσι και να αποκτήσετε αυτό το υπέροχο εικονίδιο Ανανέωσης και απλώς κάντε κλικ στην επιλογή Ανανέωση. Αυτήν τη στιγμή, έχουμε φορτωθεί 473 σειρές και τώρα έχουν φορτωθεί 563 σειρές. Δημιουργήστε τον συγκεντρωτικό σας πίνακα και η ζωή είναι υπέροχη.
Τώρα, hey, κοίτα, ξέρω ότι θα ήταν δυνατόν να προσθέσουμε μια νέα στήλη εδώ, = INT (D2) - - από την ημερομηνία - αντιγράψτε το κάτω, αντιγράψτε και επικολλήστε ειδικές τιμές. Αλλά τότε είστε στο άγκιστρο για να το κάνετε αυτό κάθε μέρα. Καλώς? Έτσι, ενώ υπάρχουν τουλάχιστον τρεις διαφορετικοί τρόποι για τους οποίους συζητήσαμε σε αυτό το βίντεο για την επίλυσή του, για μένα, το Power Query και, ακριβώς, η δυνατότητα κλικ στην επιλογή Ανανέωση, είναι ο τρόπος να πάτε.
Power Query, το μιλάω στο νέο μου βιβλίο, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.
Περίληψη του σημερινού επεισοδίου: Ο Ian στο Νάσβιλ λαμβάνει δεδομένα από ένα σύστημα κάθε μέρα, η στήλη Ημερομηνία έχει ενοχλητικά και την Ημερομηνία συν Ώρα - βιδώνει τους συγκεντρωτικούς πίνακες του, οπότε πρέπει να… Λοιπόν, τρία πιθανά πράγματα: Ένα, χρήση η συνάρτηση INT, αντιγραφή, επικόλληση τιμές Δεύτερον, απλώς αφήστε το ως Ημερομηνία και ώρα και, στη συνέχεια, ομαδοποιήστε τον περιστρεφόμενο πίνακα κατά Ημερομηνία ή, μια καλύτερη λύση, το Power Query, κάνει το σύνολο δεδομένων που έχετε λάβει σε πίνακα με Ctrl + T και, στη συνέχεια, στην καρτέλα Δεδομένα, επιλέξτε Από τον πίνακα, επιλέξτε τη στήλη Ημερομηνία και ώρα, μεταβείτε στην καρτέλα Μετασχηματισμός στο Power Query, μετατρέψτε σε ημερομηνία, κλείσιμο και φόρτωση, δημιουργήστε τον συγκεντρωτικό πίνακα από αυτό. Την επόμενη φορά που θα λάβετε δεδομένα, επικολλήστε στον αρχικό πίνακα, μεταβείτε στον πίνακα ερωτημάτων, κάντε κλικ στην επιλογή Ανανέωση και είστε έτοιμοι.
Γεια, για να κατεβάσετε το βιβλίο εργασίας από το σημερινό βίντεο, επισκεφθείτε τη διεύθυνση URL στην περιγραφή του YouTube.
Θέλω να ευχαριστήσω τον Ian που εμφανίστηκε στο σεμινάριό μου στο Νάσβιλ, θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δω την επόμενη φορά για άλλο netcast από.
Λήψη αρχείου Excel
Για να κατεβάσετε το αρχείο excel: date-time-to-date.xlsx
Για περαιτέρω βελτίωση της διαδικασίας Power Query:
- Βεβαιωθείτε ότι το βιβλίο εργασίας λήψης αποθηκεύεται πάντα στον ίδιο φάκελο με το ίδιο όνομα.
- Ξεκινήστε από ένα κενό βιβλίο εργασίας. Αποθήκευση ως με ένα όνομα όπως το DownloadedFileTransformed.xlsx
- Από το κενό βιβλίο εργασίας, Δεδομένα, από αρχείο, από βιβλίο εργασίας. Μεταβείτε στο αρχείο από το Βήμα 1.
- Επιλέξτε Καθαρά δεδομένα. Μετατρέψτε την ημερομηνία / ώρα σε ημερομηνία. Κλείσιμο & φόρτωση.
- Κάντε δεξί κλικ στον πίνακα ερωτημάτων και επιλέξτε Ιδιότητες. Επιλέξτε Ανανέωση δεδομένων κατά το άνοιγμα του αρχείου.
- Κάθε μέρα, πηγαίνετε στη δουλειά. Πάρτε τον καφέ σας. Βεβαιωθείτε ότι ένα νέο αρχείο για το # 1 βρίσκεται στο φάκελο.
- Ανοίξτε το βιβλίο εργασίας DownloadFileTransformed.xlsx. Θα έχει τα νέα δεδομένα και οι ημερομηνίες θα είναι σωστές.
Excel Thought Of the Day
Ζήτησα από τους φίλους μου στο Excel Master τις συμβουλές τους σχετικά με το Excel. Η σημερινή σκέψη να σκεφτούμε:
"Η επικύρωση είναι ο φίλος σου."
Ντουάν Ομπίν
