Το Power Query είναι ένα νέο εργαλείο από τη Microsoft για εξαγωγή, μετατροπή και φόρτωση δεδομένων. Το σημερινό άρθρο αφορά την επεξεργασία όλων των αρχείων σε ένα φάκελο.
Το Power Query είναι ενσωματωμένο στο Excel 2016 και διατίθεται ως δωρεάν λήψη σε συγκεκριμένες εκδόσεις του Excel 2010 και του Excel 2013. Το εργαλείο έχει σχεδιαστεί για την εξαγωγή, μετατροπή και φόρτωση δεδομένων στο Excel από διάφορες πηγές. Το καλύτερο μέρος: Το Power Query θυμάται τα βήματά σας και θα τα αναπαράγει όταν θέλετε να ανανεώσετε τα δεδομένα. Καθώς το βιβλίο αυτό πιέζεται, οι δυνατότητες Power Query στο Excel 2016 βρίσκονται στην καρτέλα Δεδομένα, στην ομάδα Get & Transform, στην ενότητα New Query Είναι δύσκολο να προβλεφθεί εάν η Microsoft θα μετονομάσει αναδρομικά το Power Query σε Get & Transform στο Excel 2010 και στο Excel 2013.
Αυτό το δωρεάν πρόσθετο είναι τόσο εκπληκτικό, θα μπορούσε να υπάρχει ένα ολόκληρο βιβλίο για αυτό. Αλλά ως μία από τις κορυφαίες 40 συμβουλές μου, θέλω να καλύψω κάτι πολύ απλό: φέρνοντας μια λίστα αρχείων στο Excel, μαζί με την ημερομηνία δημιουργίας αρχείων και ίσως το μέγεθος. Αυτό είναι χρήσιμο για τη δημιουργία μιας λίστας βιβλίων εργασίας προϋπολογισμού ή μιας λίστας φωτογραφιών.
Στο Excel 2016, επιλέγετε δεδομένα, νέο ερώτημα, από αρχείο, από φάκελο. Σε παλαιότερες εκδόσεις του Excel, χρησιμοποιήστε το Power Query, From File, From Folder. Καθορίστε το φάκελο:
Κατά την επεξεργασία του ερωτήματος, κάντε δεξί κλικ σε τυχόν στήλες που δεν θέλετε και επιλέξτε Κατάργηση.
Για να λάβετε το μέγεθος αρχείου, κάντε κλικ σε αυτό το εικονίδιο στη στήλη Χαρακτηριστικά:
Εμφανίζεται μια λίστα με επιπλέον χαρακτηριστικά. Επιλέξτε Μέγεθος.
Διατίθεται μια μεγάλη λίστα επιλογών Transform.
Όταν ολοκληρώσετε την επεξεργασία του ερωτήματος, κάντε κλικ στην επιλογή Κλείσιμο και φόρτωση.
Τα δεδομένα φορτώνονται στο Excel ως πίνακας.
Αργότερα, για να ενημερώσετε τον πίνακα, επιλέξτε Δεδομένα, Ανανέωση όλων. Το Excel θυμάται όλα τα βήματα και ενημερώνει τον πίνακα με μια τρέχουσα λίστα αρχείων στο φάκελο.
Για μια πλήρη περιγραφή της δυνατότητας που ήταν γνωστή ως Power Query, ελέγξτε το M is for (Data) Monkey από τους Ken Puls και Miguel Escobar.
Το M είναι για (DATA) MONKEY »
Χάρη στους Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser και Colin Michael για το διορισμό του Power Query.
Δες το βίντεο
- Τα εργαλεία Power Query βρίσκονται στην καρτέλα Δεδομένα στο Excel 2016
- Δωρεάν πρόσθετα για το 2010 και το 2013
- Λίστα όλων των αρχείων από ένα φάκελο στο πλέγμα του Excel χρησιμοποιώντας το Power Query
- Επιλέξτε Νέο ερώτημα, από αρχείο, από φάκελο
- Δεν είναι προφανές: αναπτύξτε το πεδίο χαρακτηριστικών για να αποκτήσετε μέγεθος
- Εάν τα δεδομένα σας βρίσκονται σε αρχεία CSV, μπορείτε να εισαγάγετε όλα τα αρχεία ταυτόχρονα σε ένα μόνο πλέγμα
- Προώθηση της γραμμής επικεφαλίδας
- Διαγράψτε τις υπόλοιπες σειρές κεφαλίδας
- Αντικαταστήστε το "" με μηδέν
- Συμπληρώστε για την προβολή διάρθρωσης
- Διαγράψτε τη συνολική συνολική στήλη
- Αποσυνδέστε τα δεδομένα
- Τύπος για τη μετατροπή ονομάτων μήνα σε ημερομηνίες
- Πλήρης λίστα βημάτων - η μεγαλύτερη αναίρεση στον κόσμο
- Την επόμενη μέρα - ανανεώστε το ερώτημα για να πραγματοποιήσετε ξανά όλα τα βήματα
Μεταγραφή του βίντεο
- Το Power Query είναι ενσωματωμένο στις εκδόσεις του Excel 2016 των Windows. Ανατρέξτε στην καρτέλα Δεδομένα στην ομάδα Get & Transform. Εάν έχετε 2010 ή
- 2013 για όσο διάστημα χρησιμοποιείτε Windows
- και όχι σε Mac ό, τι υπάρχει εδώ στο Get & Transform
- μπορείτε να πραγματοποιήσετε δωρεάν λήψη από τη Microsoft. Απλώς αναζητήστε
- Λήψη Power Query.
- Σήμερα, ενδιαφέρομαι να χρησιμοποιήσω το Power Query για να λάβω μια λίστα αρχείων. Εγώ
- θέλω να αναφέρω όλα τα αρχεία σε ένα φάκελο.
- Ίσως πρέπει να δω ποια αρχεία είναι
- μεγάλα αρχεία ή πρέπει να ταξινομήσω ή χρειάζομαι
- ξέρετε να πάρετε έναν συνδυασμό από εσάς
- γνωρίζετε τα αρχεία προϋπολογισμού που στείλαμε
- και στη συνέχεια έναν διαφορετικό φάκελο
- ήρθαμε πίσω.
- Για να ξεκινήσετε, μεταβείτε στο Data, Get & Tranform, From File, From Folder.
- Επικολλήστε τη διαδρομή του φακέλου ή χρησιμοποιήστε το κουμπί Αναζήτηση.
- Κάντε κλικ στο OK και μου δείχνουν αυτό
- προεπισκόπηση. Επιλέξτε Επεξεργασία.
- Μερικά πράγματα εδώ βλέπετε ότι έχουμε
- το όνομα αρχείου την επέκταση την ημερομηνία
- πρόσβαση, τροποποίηση ημερομηνίας, ημερομηνία δημιουργίας.
- Δεν είναι πραγματικά προφανές ότι αυτό το σύμβολο δίπλα στην επικεφαλίδα Χαρακτηριστικά σημαίνει Ανάπτυξη. Κάντε κλικ σε αυτό το σύμβολο και υπάρχουν περισσότερα πράγματα
- εδώ και αν κάνετε κλικ σε αυτό το σύμβολο τότε εγώ
- μπορεί να εισέλθει και να πάρει πράγματα όπως το μέγεθος του αρχείου
- ή αν είναι μόνο για ανάγνωση και πράγματα σαν
- έτσι σε αυτήν την περίπτωση θέλω απλώς αρχείο
- Μέγεθος. Επιλέξτε Μέγεθος αρχείου. Κάντε κλικ στο ΟΚ. Σας δίνουν ένα νέο πεδίο με το όνομα Attribut.Size.
- Μπορώ να δω πόσα byte είναι
- κάθε αρχείο.
- Ίσως δεν χρειάζομαι τα πάντα εδώ ίσως
- Δεν χρειάζομαι την ημερομηνία που δημιουργήθηκε για να μπορώ
- κάντε δεξί κλικ και πείτε ότι θέλω
- αφαιρέστε αυτήν τη στήλη. Αυτό
- δυαδικό δεν χρειάζομαι που θα αφαιρέσει
- αυτή τη στήλη. Από την Κορδέλα, κάντε κλικ στο Κλείσιμο & Φόρτωση.
- Σε λίγα δευτερόλεπτα, θα έχετε μια δυνατότητα ταξινόμησης
- όλα σε αυτόν τον φάκελο εάν ο φάκελος
- αλλαγές μπορώ να έρθω εδώ και μπορώ
- ανανεώστε το ερώτημα και θα επιστρέψει
- και τραβήξτε αυτά τα δεδομένα σωστά
- για μένα αυτό είναι ένα πρόβλημα που συνηθίζαμε
- έχουμε όλη την ώρα να στείλουμε 200
- αρχεία προϋπολογισμού
- και παίρνετε κάποιον πίσω όχι όλα
- πίσω πρέπει να είστε σε θέση να το συγκρίνετε
- τώρα μπορώ ουσιαστικά να κάνω μια προοπτική
- μεταξύ φακέλων.
- Είναι απλώς καταπληκτικό πώς
- είναι ωραίο αλλά κοίτα ας πάμε πέρα
- τι έχω στο βιβλίο και να σας δείξω πώς
- αυτή είναι μόνο η κορυφή του παγόβουνου.
- Θα δημιουργήσω ένα άλλο ερώτημα. Δεδομένα, νέο ερώτημα, από αρχείο, από φάκελο.
- Θα αντιγράψω αυτήν τη διαδρομή φακέλου εδώ.
- κάντε κλικ στην επεξεργασία.
- Από τον Οκτώβριο του 2016, αυτό το τέχνασμα λειτουργεί μόνο με CSV
- αρχεία, αλλά το 2017 ενημερώθηκε για να λειτουργεί με αρχεία Excel ενός φύλλου. Εγώ
- έχετε ένα φάκελο ένα σωρό αρχεία και
- Θέλω να δημιουργήσω ένα πλέγμα excel με όλα
- των δεδομένων από όλα αυτά τα αρχεία.
- Δεν είναι καθόλου διαισθητικό. Κοιτάξτε δίπλα στην επικεφαλίδα για τη δυαδική στήλη. Υπάρχει ένα εικονίδιο με δύο βέλη που δείχνουν προς τα κάτω σε μια οριζόντια γραμμή.
- Κάντε κλικ σε αυτό.
- Μπαμ! απλώς τράβηξε σε κάθε δίσκο από
- κάθε αρχείο σε αυτόν τον φάκελο!
- Δεν είναι
- αυτό το καταπληκτικό εννοώ ότι ήταν μια μακροεντολή VBA
- πριν και χρειάζεται μήνες για να μάθεις το VBA
- μακροεντολές μπορείτε να μάθετε το ερώτημα ισχύος σε δέκα
- λεπτά.
- Πρέπει να επιλέξουμε αυτήν τη στήλη και
- go to replace values say that we're
- going to
- replace nothing with the word null click
- okay
- That'll give us Nulls in place of empty cells.
- Those nulls allow us to use this amazing
- featured called Fill Down. Watch that
- column when I choose Fill Down. BAM it
- just pulled in all of that outline view
- and brought the value down.
- I don't need the Grand Total column.
- Right-click and remove.
- Now at this point you say oh yeah hey we could
- pull this in and it'd be awesome. But if
- we wanted to create a pivot table from
- this data having a repeating group going
- across Jan Feb Mar is not a good format
- for a pivot tables.
- Right now we have 47
- rows I need to have 47 times twelve rows
- and to do this in a regular Excel file
- it is horrendous using a Multiple
- Consolidation Range that I learned from
- Mike Alexander at Data Pig Technologies.
- But it is easy in Power Query. Check this out I'm going to choose the
- label columns along the left. These are the things that I don't
- want to change and then on the Transform tab, choose Unpivot Other Columns.
- We go from 47 rows to 564 rows
- that's an amazing step.
- Here you can see
- that these values are text. It is easy enough to
- change it to either currency or a whole
- number. Right click the heading and choose Rename and call it
- revenue
- How about these months? They're
- all text such as Jan, Feb, Mar. Here's an awesome way to fix
- that we go to add column add a custom
- column doesn't matter what the name you use.
- The calculation, in quotes, is " 1, 2016". Click OK.
- Now we have this new custom column I'm going to take the
- attribute column containing Month names and the new custom column. Select both columns
- and say I want to merge those columns
- with a space in between and call it date.
- Click OK. That looks enough
- like a real date that when I go to
- transform and change it to a date it
- converts it to a true Excel date.
- At this point these two temporary
- columns I can right click and remove.
- Now you could be saying to yourself:
- Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
- Αυτό το βιβλίο θα διδάξει
- εσείς τα πάντα για το ερώτημα ενέργειας
- διεπαφή είναι ένα καταπληκτικό βιβλίο το καλύτερο
- βιβλίο για το ερώτημα ισχύος ό, τι έμαθα
- Έμαθα από αυτό το βιβλίο. Πήγα σε μια πτήση από
- Ορλάντο προς Ντάλας - Διάβασα ολόκληρο το βιβλίο
- και η γνώση μου για το ερώτημα ισχύος
- αυξήθηκε σε δύο ώρες που μπορείτε να φτάσετε
- ταχύτητα και αντικατάσταση πραγμάτων που θα θέλατε
- είχα συνηθίσει να κάνω με το VBA.
Λήψη αρχείου
Κάντε λήψη του δείγματος αρχείου εδώ: Podcast2037.xlsx
