Χρήση του Advanced Filter στο Excel για την επίλυση του προβλήματος του Mort. Αν και τα κανονικά φίλτρα έχουν γίνει πιο ισχυρά, υπάρχουν ακόμα φορές που το Advanced Filter μπορεί να κάνει κάποια κόλπα που άλλοι δεν μπορούν.
Δες το βίντεο
- Το Advanced Filter είναι πιο "προηγμένο" από το κανονικό φίλτρο, επειδή:
- 1) Μπορεί να αντιγράψει σε μια νέα σειρά
- 2) Μπορείτε να δημιουργήσετε πιο περίπλοκα κριτήρια, όπως Πεδίο 1 = Α ή Πεδίο 2 = Α
- 3) Είναι γρήγορο
- Ο Mort προσπαθεί να επεξεργαστεί 100K σειρές στο VBA με βρόχο μέσω εγγραφών ή χρησιμοποιώντας έναν πίνακα
- Θα είναι πάντα πιο γρήγορο να χρησιμοποιείτε ενσωματωμένες δυνατότητες του Excel από τη σύνταξη του δικού σας κώδικα.
- Χρειάζεστε εύρος εισόδου και έπειτα εύρος κριτηρίων ή / και εύρος εξόδου
- Για το εύρος εισόδου: μία σειρά επικεφαλίδων πάνω από τα δεδομένα
- Προσθέστε μια προσωρινή σειρά για επικεφαλίδες
- Για το εύρος εξόδου: μια σειρά επικεφαλίδων για τις στήλες που θέλετε να εξαγάγετε
- Για το εύρος κριτηρίων: επικεφαλίδες στη σειρά 1, τιμές που ξεκινούν από τη σειρά 2
- Επιπλοκή: Οι παλαιότερες εκδόσεις του Excel δεν θα επέτρεπαν να βρίσκεται το εύρος εξόδου σε άλλο φύλλο
- Εάν γράφετε μια μακροεντολή που θα μπορούσε να εκτελεστεί το 2003, χρησιμοποιήστε μια ονομαστική περιοχή για παράκαμψη εύρους εισόδου
Μεταγραφή βίντεο
Μάθετε το Excel από το Podcast, Episode 2060: Excel Advanced Filter
Γεια σας, καλώς ήλθατε πίσω στο netcast, είμαι ο Bill Jelen. Η σημερινή ερώτηση έστειλε ο Mort. Mort, έχει 100.000 σειρές δεδομένων και ενδιαφέρεται για τις στήλες A, B και D όπου η στήλη C ταιριάζει με κάποιο συγκεκριμένο έτος. Έτσι θέλει ένα άτομο να μπει σε ένα χρόνο και μετά να πάρει τις Στήλες Α, Β και Δ. Και ο Μόρτ έχει κάποια VBA όπου χρησιμοποιεί συστοιχίες για να το κάνει αυτό και είπα: «Περιμένετε λίγο, ξέρετε, το προηγμένο φίλτρο θα το έκανε αυτό πολύ καλύτερα. " Εντάξει, και τώρα μόνο για επανεξέταση, επέστρεψα, κοίταξα πίσω τα βίντεό μου. Δεν έχω καλύψει προηγμένο φίλτρο εδώ και πολύ καιρό, οπότε πρέπει να μιλήσουμε για αυτό.
Το προηγμένο φίλτρο απαιτεί εύρος εισόδου και έπειτα τουλάχιστον ένα από αυτά: εύρος κριτηρίων ή εύρος εξόδου. Αν και σήμερα θα χρησιμοποιήσουμε και τα δύο. Εντάξει, έτσι το εύρος εισόδου είναι τα δεδομένα σας και πρέπει να έχετε επικεφαλίδες πάνω από τα δεδομένα. Έτσι, ο Mort δεν έχει επικεφαλίδες και γι 'αυτό θα εισαγάγω προσωρινά μια σειρά εδώ και θα κάνω ακριβώς όπως το Field 1. Ο Mort ξέρει ποια είναι τα δεδομένα του και έτσι θα μπορούσε να βάλει πραγματικούς τίτλους εκεί. Και δεν χρησιμοποιούμε τίποτα - αυτά τα δεδομένα στις στήλες Ε έως Ο, οπότε δεν χρειάζεται να προσθέσω επικεφαλίδες εκεί, εντάξει; Τώρα, το A1 έως το D, 100000 γίνεται το εύρος εισόδου μου. Και μετά το εύρος εξόδου και το εύρος κριτηρίων - Λοιπόν, το εύρος εξόδου είναι απλώς μια λίστα με τους τίτλους που θέλετε. Οπότε θα βάλω το εύρος εξόδου εδώ και δεν χρειαζόμαστε το πεδίο 3, οπότε εγώΘα το βγάλω απλώς στο πλάι. Τώρα, αυτό το εύρος εδώ, το A1 έως το C1 γίνεται το εύρος εξόδου μου που λέει στο Excel ποια πεδία θέλω από το εύρος εισόδου. Και, θα μπορούσαν να είναι σε διαφορετική σειρά εάν θέλετε να αναδιατάξετε τα πράγματα, όπως εάν θέλω πρώτα το πεδίο 4 και έπειτα το πεδίο 1 και μετά το πεδίο 2. Και πάλι, αυτές θα ήταν πραγματικές επικεφαλίδες όπως ο αριθμός τιμολογίου. Απλώς δεν ξέρω πώς είναι τα δεδομένα του Mort
Και τότε, το εύρος κριτηρίων είναι μια επικεφαλίδα και ποια τιμή θέλετε. Ας πούμε λοιπόν ότι προσπαθούσα να πάρω οτιδήποτε το 2014. Αυτό γίνεται το εύρος των κριτηρίων έτσι. Εντάξει, απλώς μια προσοχή. Είμαι στο Excel 2016 και είναι πιθανό να κάνω ένα προηγμένο φίλτρο μεταξύ δύο φύλλων στο Excel 2016, αλλά αν πάμε πίσω και δεν θυμάμαι τι είναι πίσω, ίσως το 2003, δεν είμαι σίγουρος. Κάποια στιγμή στο παρελθόν, ήταν ότι δεν μπορούσατε να κάνετε ένα προηγμένο φίλτρο από ένα φύλλο σε άλλο φύλλο, οπότε θα έπρεπε να έρθετε εδώ και να ονομάσετε το εύρος εισόδου σας. Θα πρέπει να δημιουργήσετε ένα όνομα εδώ. MyName ή κάτι τέτοιο, εντάξει; Και αυτός θα ήταν ο τρόπος που θα μπορούσατε να το ξεπεράσετε, εντάξει. Όχι απαραίτητα στο Excel 2016 αλλά και πάλι, εγώΔεν είμαι σίγουρος αν ο Mort θα το εκτελεί σε παλαιότερες εκδόσεις των δεδομένων.
Εντάξει, λοιπόν εδώ στο Data, πηγαίνουμε στο Advanced Filter, εντάξει. Και πρόκειται να αντιγράψουμε σε άλλη τοποθεσία που επιτρέπει το εύρος εξόδου μας εκεί. Εντάξει, λοιπόν, το εύρος λιστών, πού είναι τα δεδομένα; Επειδή είμαι στο Excel 2016, θα πάω να επισημάνω τα Δεδομένα, αντί να χρησιμοποιήσω το εύρος ονομάτων - Αυτό είναι το εύρος εισαγωγής μου. Το εύρος κριτηρίων είναι εκείνα τα κελιά εκεί ακριβώς και στη συνέχεια, όπου θα πάμε - θα εξάγουμε, θα είναι αυτά τα τρία κελιά εκεί. Και μετά κάνουμε κλικ στο OK. Εντάξει, και BAM! Είναι τόσο γρήγορο, γρήγορο. Και αν θέλαμε ένα διαφορετικό έτος; Εάν θέλαμε ένα διαφορετικό έτος, θα διαγράψαμε τα αποτελέσματα, θα θέσαμε το 2015 και μετά θα κάνουμε ξανά ένα σύνθετο φίλτρο, Αντιγράψτε σε άλλη τοποθεσία, κάντε κλικ στο OK και υπάρχουν όλες οι εγγραφές του 2015. Αστραπές γρήγορα.
Εντάξει τώρα, ενώ είμαι οπαδός του προχωρημένου φίλτρου στο κανονικό Excel, ήμουν οπαδός του προηγμένου φίλτρου στο VBA, εντάξει, επειδή το VBA κάνει το προηγμένο φίλτρο πραγματικά, πραγματικά, πολύ απλό. Εντάξει, οπότε πρόκειται να γράψουμε έναν κωδικό εδώ για τον Mort, υποθέτοντας ότι τα δεδομένα του Mort δεν έχουν επικεφαλίδες και θα πρέπει να προσθέσουμε προσωρινά τις επικεφαλίδες, εντάξει; Έτσι, θα αλλάξω στο VBA, Alt + F11 και θα το εκτελέσουμε από το φύλλο εργασίας που περιέχει τα δεδομένα. Έτσι: Dim WS ως φύλλο εργασίας, ορίστε WS = ActiveSheet. Και μετά, εισαγάγετε τη σειρά 1 και προσθέστε απλώς μερικές επικεφαλίδες: A, B, Year και D. Βρείτε πόσες σειρές δεδομένων έχουμε σήμερα και στη συνέχεια ξεκινώντας από το κελί A1 που βγαίνει 4 στήλες μέχρι την τελευταία σειρά, ονομάστε το σε να είναι εύρος εισόδου. Εντάξει, και τότε αυτός είναι στην πραγματικότητα ο κωδικός του Mort εδώ, όπου ζήτησε το InputBox,παίρνει το έτος που θέλουν και στη συνέχεια ρωτά τι έτος ή τι θέλουν να ονομάσουν το νέο φύλλο, εντάξει. Άρα πρόκειται να εισαγάγει ένα φύλλο στο Fly και μετά I- Dimension ένα νέο φύλλο, WSN, ως το ActiveSheet. Γνωρίζω λοιπόν ότι το WS είναι το αρχικό φύλλο, το WSN είναι το νέο φύλλο που μόλις προστέθηκε. Στο νέο φύλλο, τοποθετήστε το εύρος κριτηρίων, έτσι στη στήλη Ε υπάρχει η επικεφαλίδα που ταιριάζει με αυτήν την επικεφαλίδα εδώ και, στη συνέχεια, όποια απάντηση μας έδωσαν πηγαίνει στο Ε2. Το εύρος εξόδου θα είναι οι άλλες τρεις επικεφαλίδες μου: A, B και D. Και πάλι, αν εσείς ή ο Mort τα αλλάξετε σε πραγματικές επικεφαλίδες που είναι πιθανώς καλύτερο να κάνετε από τα A, B, D, και θα θέλατε επίσης να τα αλλάξετε σε πραγματικές επικεφαλίδες, εντάξει; Επομένως, όλα αυτά είναι λίγο πριν από την προ-εργασία εδώ. Αυτή η εκπληκτική γραμμή κώδικα θα κάνει ολόκληρο το προηγμένο φίλτρο. Ετσι,από το InputRange κάνουμε ένα AdvancedFilter, θα αντιγράψουμε. Αυτό είναι το φίλτρο επιλογής μας στη θέση ή αντίγραφο. Το CriteriaRange είναι E1 έως E2, το CopyToRange είναι A έως C. Μοναδικές τιμές - Όχι, θέλουμε όλες τις τιμές. Εντάξει, ότι μια γραμμή κώδικα κάνει όλη τη μαγεία του βρόχου σε όλες τις εγγραφές ή αντικαθιστά το βρόχο σε όλες τις εγγραφές ή κάνει τις συστοιχίες. Και μετά τελειώσαμε, θα διαγράψουμε το εύρος των κριτηρίων και στη συνέχεια θα διαγράψουμε τη σειρά 1 στο αρχικό φύλλο εργασίας.Και μετά τελειώσαμε, θα διαγράψουμε το εύρος των κριτηρίων και στη συνέχεια θα διαγράψουμε τη σειρά 1 στο αρχικό φύλλο εργασίας.Και μετά τελειώσαμε, θα διαγράψουμε το εύρος των κριτηρίων και στη συνέχεια θα διαγράψουμε τη σειρά 1 στο αρχικό φύλλο εργασίας.
Εντάξει, οπότε ας επιστρέψουμε εδώ στα δεδομένα μας. Θα το κάνουμε εύκολο να το εκτελέσουμε, έτσι: Εισαγάγετε, ένα σχήμα και καλέστε αυτό το φίλτρο, Αρχική, Κέντρο, Κέντρο, Μεγαλύτερο, Μεγαλύτερο, Μεγαλύτερο, δεξί κλικ, Εκχώρηση μακροεντολής και εκχώρηση του στο MacroForMort. Εντάξει, λοιπόν, πάμε. Θα κάνουμε μια δοκιμή. Δείτε ότι είμαστε στο φύλλο δεδομένων, κάντε κλικ στο Φίλτρο, τι έτος θέλουμε; Θέλουμε το 2015. Τι θέλω να το ονομάσω; Θέλω να το ονομάσω 2015, εντάξει. Και BAM! Εκεί τελείωσε. Αυτό είναι τόσο γρήγορο, τόσο γρήγορα.
Τώρα, δεδομένου ότι τα αρχικά δεδομένα του Mort δεν είχαν επικεφαλίδες, ίσως αυτά τα δεδομένα δεν θα έπρεπε να έχουν επικεφαλίδες. Ας πάμε λοιπόν Alt + F11, εδώ θέλουμε να διαγράψουμε το εύρος των κριτηρίων. Θα εμφανίσουμε επίσης σειρές (1). Εντάξει, οπότε τώρα την επόμενη φορά που βρισκόμαστε σε αυτό, θα απαλλαγούμε από αυτές τις επικεφαλίδες. Και ας απλώς - Αντί να τρέξουμε το όλο θέμα γρήγορα, ας ρίξουμε μια ματιά εδώ με το 2014. Έτσι θα επιλέξω ένα κελί στα Δεδομένα, Alt + F11 και θέλω να τρέξω μέχρι το σημείο όπου κάνουμε το προηγμένο φίλτρο. Έτσι μπορούμε να δούμε και να δούμε τι κάνει ολόκληρη η μακροεντολή εδώ. Έτσι θα κάνουμε κλικ στο Εκτέλεση και θέλω να λάβω το 2014. 2014, εντάξει. Και έτσι, πατήστε F8, πρόκειται να κάνουμε το προηγμένο φίλτρο. Μπορούμε να επιστρέψουμε στο Excel εδώ και να δούμε τι συνέβη.
First thing that's happened- Now, first thing that’s happened is we've added a new temporary row with the headings. Inserted this worksheet, built a criteria range with a heading and what year they input, chose the fields that we want to do and then back in VBA, I'll run the next line of codes, that's F8 that does the advanced filter right there. It's incredibly fast and you'll see that that has actually now brought us all the records. From there, it's just a bit of cleanup, delete this, delete this. I'll go back to the data and delete Row 1 and we will be good to go. So I'll just let the rest of that run, remove that breakpoint, alright? So there's the VBA. For me, this is I think the fastest way, fastest way to go.
Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range. And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A. The regular auto filter can't do that and it's fast. Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code. You need to define an input range, criteria range, output range. You always need an input range in at least one of these, although today I'm using both. For the input range, single row of headings above the data. So we're going to add a temporary row of headings. For the output range, the same headings that you want to extract, alright. So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1. So this is the field I want to build a criteria on and this is the value I'm looking for. Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then. You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet. So that would allow the advanced filter to work.
Εντάξει, λοιπόν, το έχετε. Θέλω να ευχαριστήσω τον Mort για την αποστολή αυτής της ερώτησης. Θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2060.xlsm
