Για τη συντριπτική πλειοψηφία των χρηστών του Excel, όταν εμφανίζεται η λέξη "φιλτράρισμα δεδομένων" στο μυαλό τους, μόνο το συνηθισμένο κλασικό φίλτρο από την καρτέλα Δεδομένα – Φίλτρο (Δεδομένα — Φίλτρο):
Ένα τέτοιο φίλτρο είναι ένα οικείο πράγμα, αναμφίβολα, και στις περισσότερες περιπτώσεις θα συμβεί. Ωστόσο, υπάρχουν περιπτώσεις κατά τις οποίες πρέπει να φιλτράρετε κατά μεγάλο αριθμό σύνθετων συνθηκών σε πολλές στήλες ταυτόχρονα. Το συνηθισμένο φίλτρο εδώ δεν είναι πολύ βολικό και θέλω κάτι πιο δυνατό. Ένα τέτοιο εργαλείο θα μπορούσε να είναι προηγμένο φίλτρο, ειδικά με λίγο «φινίρισμα με λίμα» (κατά την παράδοση).
Βάση
Για να ξεκινήσετε, εισαγάγετε μερικές κενές γραμμές πάνω από τον πίνακα δεδομένων σας και αντιγράψτε την κεφαλίδα του πίνακα εκεί – αυτό θα είναι ένα εύρος με συνθήκες (επισημαίνεται με κίτρινο χρώμα για σαφήνεια):
Πρέπει να υπάρχει τουλάχιστον μία κενή γραμμή μεταξύ των κίτρινων κελιών και του αρχικού πίνακα.
Είναι στα κίτρινα κελιά που πρέπει να εισαγάγετε τα κριτήρια (συνθήκες), σύμφωνα με τα οποία στη συνέχεια θα πραγματοποιηθεί το φιλτράρισμα. Για παράδειγμα, εάν πρέπει να επιλέξετε μπανάνες στο "Auchan" της Μόσχας στο τρίμηνο III, τότε οι συνθήκες θα μοιάζουν με αυτό:
Για να φιλτράρετε, επιλέξτε οποιοδήποτε κελί στην περιοχή με τα δεδομένα προέλευσης, ανοίξτε την καρτέλα ημερομηνία Και κάντε κλικ στο Επιπλέον (Δεδομένα — Προηγμένες). Στο παράθυρο που ανοίγει, ένα εύρος με δεδομένα θα πρέπει ήδη να εισαχθεί αυτόματα και θα πρέπει μόνο να καθορίσουμε το εύρος των συνθηκών, π.χ. A1:I2:
Λάβετε υπόψη ότι το εύρος των συνθηκών δεν μπορεί να εκχωρηθεί "με περιθώριο", δηλαδή δεν μπορείτε να επιλέξετε επιπλέον κενές κίτρινες γραμμές, επειδή ένα κενό κελί στο εύρος των συνθηκών γίνεται αντιληπτό από το Excel ως απουσία κριτηρίου και ένα ολόκληρο κενό γραμμή ως αίτημα για την αδιάκριτη εμφάνιση όλων των δεδομένων.
διακόπτης Αντιγράψτε το αποτέλεσμα σε άλλη τοποθεσία θα σας επιτρέψει να φιλτράρετε τη λίστα όχι ακριβώς εκεί σε αυτό το φύλλο (όπως με ένα κανονικό φίλτρο), αλλά να ξεφορτώσετε τις επιλεγμένες σειρές σε μια άλλη περιοχή, η οποία στη συνέχεια θα πρέπει να καθοριστεί στο πεδίο Βάλτε το αποτέλεσμα στο εύρος. Σε αυτή την περίπτωση, δεν χρησιμοποιούμε αυτή τη λειτουργία, φεύγουμε Φιλτράρετε τη λίστα στη θέση του και κάντε κλικ στο κουμπί OK. Οι επιλεγμένες σειρές θα εμφανιστούν στο φύλλο:
Προσθήκη μακροεντολής
«Λοιπόν, πού είναι η ευκολία εδώ;» ρωτάς και θα έχεις δίκιο. Όχι μόνο χρειάζεται να εισαγάγετε συνθήκες στα κίτρινα κελιά με τα χέρια σας, αλλά και να ανοίξετε ένα πλαίσιο διαλόγου, να εισαγάγετε εύρη εκεί, να πατήσετε OK. Κρίμα, συμφωνώ! Αλλά "όλα αλλάζουν όταν έρχονται ©" - μακροεντολές!
Η εργασία με ένα προηγμένο φίλτρο μπορεί να επιταχυνθεί σημαντικά και να απλοποιηθεί χρησιμοποιώντας μια απλή μακροεντολή που θα εκτελεί αυτόματα το προηγμένο φίλτρο όταν εισάγονται συνθήκες, δηλαδή αλλάζοντας οποιοδήποτε κίτρινο κελί. Κάντε δεξί κλικ στην καρτέλα του τρέχοντος φύλλου και επιλέξτε την εντολή Κείμενο πηγής (Κωδικός πηγής). Στο παράθυρο που ανοίγει, αντιγράψτε και επικολλήστε τον ακόλουθο κώδικα:
Ιδιωτικό δευτερεύον φύλλο εργασίας_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=PaceF :=Εύρος ("A1").Τρέχουσα Περιοχή Τέλος Εάν Τελος Υπο Αυτή η διαδικασία θα εκτελεστεί αυτόματα όταν αλλάξει οποιοδήποτε κελί στο τρέχον φύλλο εργασίας. Εάν η διεύθυνση του αλλαγμένου κελιού εμπίπτει στο κίτρινο εύρος (A2:I5), τότε αυτή η μακροεντολή αφαιρεί όλα τα φίλτρα (εάν υπάρχουν) και εφαρμόζει ξανά το εκτεταμένο φίλτρο στον πίνακα δεδομένων πηγής ξεκινώντας από το A7, δηλαδή όλα θα φιλτραριστούν αμέσως, αμέσως αφού εισαγάγετε την επόμενη συνθήκη:
Άρα όλα είναι πολύ καλύτερα, σωστά; 🙂
Υλοποίηση σύνθετων ερωτημάτων
Τώρα που τα πάντα φιλτράρονται εν κινήσει, μπορούμε να πάμε λίγο βαθύτερα στις αποχρώσεις και να αποσυναρμολογήσουμε τους μηχανισμούς πιο περίπλοκων ερωτημάτων στο προηγμένο φίλτρο. Εκτός από την εισαγωγή ακριβών αντιστοιχίσεων, μπορείτε να χρησιμοποιήσετε διάφορους χαρακτήρες μπαλαντέρ (* και ?) και μαθηματικά σημάδια ανισότητας σε μια σειρά συνθηκών για να πραγματοποιήσετε μια κατά προσέγγιση αναζήτηση. Η περίπτωση του χαρακτήρα δεν έχει σημασία. Για λόγους σαφήνειας, έχω συνοψίσει όλες τις πιθανές επιλογές σε έναν πίνακα:
| Κριτήριο | Αποτέλεσμα |
| gr* ή γρ | όλα τα κύτταρα ξεκινώντας από GrIe Grαυτί, Grαφρουτ, Grπέρυσι και ούτω καθεξής |
| = κρεμμύδι | όλα τα κελιά ακριβώς και μόνο με τη λέξη Τόξο, δηλαδή ακριβής αντιστοίχιση |
| *liv* ή *liv | κύτταρα που περιέχουν Liv πόσο υπογραμμίζω, δηλ ОLivότι, Livep, Σύμφωνα μεLiv και ούτω καθεξής |
| =p*v | λέξεις που ξεκινούν με П και τελειώνει με В ie Пπρώταв, Пαιθέραςв και ούτω καθεξής |
| όπως και | λέξεις που ξεκινούν με А και περαιτέρω περιέχει СIe Аpelсin, Аnanaс, Asai και ούτω καθεξής |
| =*s | λέξεις που τελειώνουν σε С |
| =???? | όλα τα κελιά με κείμενο 4 χαρακτήρων (γράμματα ή αριθμούς, συμπεριλαμβανομένων των κενών) |
| =m??????n | όλα τα κελιά με κείμενο 8 χαρακτήρων που αρχίζει με М και τελειώνει με НIe Мandariн, Мανησυχίαн και ούτω καθεξής |
| =*n??a | όλες οι λέξεις που τελειώνουν με А, όπου είναι το 4ο γράμμα από το τέλος НIe Πλάτοςнikа, Σύμφωνα μεнozа και ούτω καθεξής |
| >=ε | όλες οι λέξεις που ξεκινούν από Э, Ю or Я |
| <>*o* | όλες οι λέξεις που δεν περιέχουν γράμμα О |
| <>*βιχ | όλες οι λέξεις εκτός από αυτές που τελειώνουν σε Τον ιό HIV (για παράδειγμα, φιλτράρετε τις γυναίκες με το μεσαίο όνομα) |
| = | όλα τα άδεια κελιά |
| <> | όλα τα μη άδεια κελιά |
| > = 5000 | όλα τα κελιά με τιμή μεγαλύτερη ή ίση με 5000 |
| 5 ή =5 | όλα τα κελιά με τιμή 5 |
| > = 3/18/2013 | όλα τα κελιά με ημερομηνία μετά τις 18 Μαρτίου 2013 (συμπεριλαμβανομένου) |
Λεπτά σημεία:
- Το σύμβολο * σημαίνει οποιονδήποτε αριθμό χαρακτήρων, και ? – οποιονδήποτε χαρακτήρα.
- Η λογική στην επεξεργασία κειμένου και αριθμητικών ερωτημάτων είναι ελαφρώς διαφορετική. Έτσι, για παράδειγμα, ένα κελί συνθήκης με τον αριθμό 5 δεν σημαίνει ότι αναζητάτε όλους τους αριθμούς που ξεκινούν από πέντε, αλλά ένα κελί συνθήκης με το γράμμα Β ισούται με Β*, δηλαδή θα αναζητήσει οποιοδήποτε κείμενο που ξεκινά από το γράμμα Β.
- Εάν το ερώτημα κειμένου δεν ξεκινά με το σύμβολο =, τότε μπορείτε να βάλετε νοερά το * στο τέλος.
- Οι ημερομηνίες πρέπει να εισαχθούν σε μορφή ΗΠΑ μήνα-ημέρα-έτος και με κλάσμα (ακόμα και αν έχετε ρυθμίσεις Excel και τοπικές ρυθμίσεις).
Λογικές συνδέσεις ΚΑΙ-Ή
Οι συνθήκες που είναι γραμμένες σε διαφορετικά κελιά, αλλά στην ίδια γραμμή, θεωρούνται ότι διασυνδέονται από έναν λογικό τελεστή И (ΚΑΙ):
Εκείνοι. μπανάνες φίλτρου για μένα στο τρίτο τρίμηνο, ακριβώς στη Μόσχα και ταυτόχρονα από το Auchan.
Εάν χρειάζεται να συνδέσετε τις συνθήκες με έναν λογικό τελεστή OR (Ή), τότε απλώς πρέπει να εισαχθούν σε διαφορετικές γραμμές. Για παράδειγμα, εάν πρέπει να βρούμε όλες τις παραγγελίες του διαχειριστή Volina για ροδάκινα Μόσχας και όλες τις παραγγελίες για κρεμμύδια το τρίτο τρίμηνο στη Σαμάρα, τότε αυτό μπορεί να καθοριστεί σε μια σειρά από συνθήκες ως εξής:
Εάν πρέπει να επιβάλετε δύο ή περισσότερες προϋποθέσεις σε μία στήλη, τότε μπορείτε απλώς να αντιγράψετε την κεφαλίδα της στήλης στο εύρος κριτηρίων και να εισαγάγετε τη δεύτερη, την τρίτη κ.λπ. κάτω από αυτήν. όροι. Έτσι, για παράδειγμα, μπορείτε να επιλέξετε όλες τις συναλλαγές από τον Μάρτιο έως τον Μάιο:
Σε γενικές γραμμές, μετά το «ολοκλήρωση με ένα αρχείο», ένα προηγμένο φίλτρο αποδεικνύεται αρκετά αξιοπρεπές εργαλείο, σε ορισμένα σημεία όχι χειρότερο από ένα κλασικό αυτόματο φίλτρο.
- Υπερφίλτρο σε μακροεντολές
- Τι είναι οι μακροεντολές, πού και πώς να εισαγάγετε κώδικα μακροεντολής στη Visual Basic
- Έξυπνοι πίνακες στο Microsoft Excel