Λειτουργία φίλτρου Excel - Εξηγείται με παραδείγματα + βίντεο

Παρακολουθήστε βίντεο - Παραδείγματα λειτουργιών ΦΙΛΤΡΟΥ Excel

Το Office 365 φέρνει μερικές εκπληκτικές λειτουργίες - όπως XLOOKUP, SORT και FILTER.

Όσον αφορά το φιλτράρισμα δεδομένων στο Excel, στον κόσμο του pre-Office 365, εξαρτιόμασταν κυρίως από το ενσωματωμένο φίλτρο Excel ή κατ 'ανώτατο όριο από το σύνθετο φίλτρο ή τους σύνθετους τύπους SUMPRODUCT. Σε περίπτωση που έπρεπε να φιλτράρετε ένα μέρος ενός συνόλου δεδομένων, ήταν συνήθως μια πολύπλοκη λύση (κάτι που έχω καλύψει εδώ).

Αλλά με τη νέα λειτουργία FILTER, είναι πλέον πολύ εύκολο να φιλτράρετε γρήγορα μέρος του συνόλου δεδομένων βάσει μιας συνθήκης.

Και σε αυτό το σεμινάριο, θα σας δείξω πόσο υπέροχη είναι η νέα λειτουργία ΦΙΛΤΡΟΥ και μερικά χρήσιμα πράγματα που μπορείτε να κάνετε με αυτό.

Αλλά πριν μπω στα παραδείγματα, ας μάθουμε γρήγορα για τη σύνταξη της συνάρτησης FILTER.

Σε περίπτωση που θέλετε να αποκτήσετε αυτές τις νέες δυνατότητες στο Excel, μπορείτε αναβάθμιση σε Office 365 (εγγραφείτε στο πρόγραμμα εσωτερικών πληροφοριών για να αποκτήσετε πρόσβαση σε όλες τις δυνατότητες/τύπους)

Λειτουργία φίλτρου Excel - Σύνταξη

Παρακάτω είναι η σύνταξη της συνάρτησης FILTER:

= ΦΙΛΤΡΟ (πίνακας, περιλαμβάνει, [if_empty])

• πίνακας - αυτό είναι το εύρος των κελιών όπου έχετε τα δεδομένα και θέλετε να φιλτράρετε κάποια δεδομένα από αυτά
• περιλαμβάνω - αυτή είναι η συνθήκη που λέει στη συνάρτηση τι καταγράφει να φιλτράρει
• [if_empty] - αυτό είναι ένα προαιρετικό όρισμα όπου μπορείτε να καθορίσετε τι θα επιστρέψετε σε περίπτωση που δεν βρεθούν αποτελέσματα από τη συνάρτηση ΦΙΛΤΡΟ. Από προεπιλογή (όταν δεν έχει καθοριστεί), επιστρέφει το #CALC! λάθος

Τώρα ας ρίξουμε μια ματιά σε μερικά καταπληκτικά παραδείγματα λειτουργίας φίλτρου και πράγματα που μπορεί να κάνει, τα οποία ήταν αρκετά πολύπλοκα ερήμην του.

Κάντε κλικ εδώ για να κατεβάσετε το Παράδειγμα αρχείου και ακολουθήστε

Παράδειγμα 1: Φιλτράρισμα δεδομένων με βάση ένα κριτήριο (περιοχή)

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να φιλτράρετε όλες τις εγγραφές μόνο για τις ΗΠΑ.

Παρακάτω είναι ο τύπος ΦΙΛΤΡΟΥ που θα το κάνει αυτό:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "ΗΠΑ")

Ο παραπάνω τύπος χρησιμοποιεί το σύνολο δεδομένων ως πίνακα και η συνθήκη είναι $ B $ 2: $ B $ 11 = "US"

Αυτή η συνθήκη θα κάνει τη συνάρτηση ΦΙΛΤΡΟ να ελέγχει κάθε κελί στη στήλη Β (ένα που έχει την περιοχή) και θα φιλτράρονται μόνο εκείνες οι εγγραφές που ταιριάζουν με αυτό το κριτήριο.

Επίσης, σε αυτό το παράδειγμα, έχω τα αρχικά δεδομένα και τα φιλτραρισμένα δεδομένα στο ίδιο φύλλο, αλλά μπορείτε επίσης να τα έχετε σε ξεχωριστά φύλλα ή ακόμη και σε βιβλία εργασίας.

Η συνάρτηση φίλτρου επιστρέφει ένα αποτέλεσμα που είναι ένας δυναμικός πίνακας (πράγμα που σημαίνει ότι αντί να επιστρέψει μία τιμή, επιστρέφει έναν πίνακα που διαχέεται σε άλλα κελιά).

Για να λειτουργήσει αυτό, πρέπει να έχετε μια περιοχή όπου το αποτέλεσμα θα ήταν άδειο. Σε οποιοδήποτε από τα κελιά αυτής της περιοχής (E2: G5 σε αυτό το παράδειγμα) έχει ήδη κάτι σε αυτό, η συνάρτηση θα σας δώσει το σφάλμα #SPILL.

Επίσης, δεδομένου ότι πρόκειται για έναν δυναμικό πίνακα, δεν μπορείτε να αλλάξετε ένα μέρος του αποτελέσματος. Μπορείτε είτε να διαγράψετε ολόκληρο το εύρος που έχει το αποτέλεσμα είτε το κελί E2 (όπου έχει εισαχθεί ο τύπος). Και τα δύο θα διαγράψουν ολόκληρο τον πίνακα που προκύπτει. Αλλά δεν μπορείτε να αλλάξετε κανένα μεμονωμένο κελί (ή να το διαγράψετε).

Στον παραπάνω τύπο, έχω κωδικοποιήσει την τιμή της περιοχής, αλλά μπορείτε επίσης να την έχετε σε ένα κελί και στη συνέχεια να αναφέρετε αυτό το κελί που έχει την τιμή περιοχής.

Για παράδειγμα, στο παρακάτω παράδειγμα, έχω την τιμή περιοχής στο κελί I2 και στη συνέχεια αναφέρεται στον τύπο:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

Αυτό καθιστά τον τύπο ακόμη πιο χρήσιμο και τώρα μπορείτε απλά να αλλάξετε την τιμή περιοχής στο κελί I2 και το φίλτρο θα αλλάξει αυτόματα.

Μπορείτε επίσης να έχετε ένα αναπτυσσόμενο μενού στο κελί I2 όπου μπορείτε απλά να κάνετε την επιλογή και θα ενημερώσει αμέσως τα φιλτραρισμένα δεδομένα.

Παράδειγμα 2: Φιλτράρισμα δεδομένων με βάση ένα κριτήριο (περισσότερο από ή λιγότερο από)

Μπορείτε επίσης να χρησιμοποιήσετε συγκριτικούς τελεστές εντός της λειτουργίας φίλτρου και να εξαγάγετε όλες τις εγγραφές που είναι περισσότερες ή λιγότερες από μια συγκεκριμένη τιμή.

Για παράδειγμα, ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να φιλτράρετε όλες τις εγγραφές όπου η αξία πωλήσεων είναι μεγαλύτερη από 10000.

Ο παρακάτω τύπος μπορεί να το κάνει αυτό:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))

Το όρισμα πίνακα αναφέρεται σε ολόκληρο το σύνολο δεδομένων και η συνθήκη, σε αυτήν την περίπτωση, είναι ($ C $ 2: $ C $ 11> 10000).

Ο τύπος ελέγχει κάθε εγγραφή για την τιμή στη στήλη C. Εάν η τιμή είναι μεγαλύτερη από 10000, φιλτράρεται, αλλιώς αγνοείται.

Σε περίπτωση που θέλετε να λάβετε όλες τις εγγραφές μικρότερες από 10000, μπορείτε να χρησιμοποιήσετε τον παρακάτω τύπο:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))

Μπορείτε επίσης να γίνετε πιο δημιουργικοί με τον τύπο ΦΙΛΤΡΟ. Για παράδειγμα, εάν θέλετε να φιλτράρετε την πρώτη τριάδα με βάση την αξία πωλήσεων, μπορείτε να χρησιμοποιήσετε τον παρακάτω τύπο:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = ΜΕΓΑΛΟ (C2: C11,3)))

Ο παραπάνω τύπος χρησιμοποιεί τη συνάρτηση LARGE για να πάρει την τρίτη μεγαλύτερη τιμή στο σύνολο δεδομένων. Αυτή η τιμή χρησιμοποιείται στη συνέχεια στα κριτήρια συνάρτησης FILTER για να λάβει όλες τις εγγραφές όπου η αξία πωλήσεων είναι μεγαλύτερη ή ίση με την τρίτη μεγαλύτερη τιμή.

Κάντε κλικ εδώ για να κατεβάσετε το Παράδειγμα αρχείου και ακολουθήστε

Παράδειγμα 3: Φιλτράρισμα δεδομένων με πολλαπλά κριτήρια (AND)

Ας υποθέσουμε ότι έχετε το παρακάτω σύνολο δεδομένων και θέλετε να φιλτράρετε όλες τις εγγραφές για τις ΗΠΑ, όπου η αξία πώλησης είναι μεγαλύτερη από 10000.

Αυτή είναι μια συνθήκη ΚΑΙ όπου πρέπει να ελέγξετε για δύο πράγματα - η περιοχή χρειάζεται στις ΗΠΑ και οι πωλήσεις πρέπει να είναι περισσότερες από 10000. Εάν πληρούται μόνο μία προϋπόθεση, τα αποτελέσματα δεν πρέπει να φιλτράρονται.

Παρακάτω είναι ο τύπος ΦΙΛΤΡΟΥ που θα φιλτράρει αρχεία με τις ΗΠΑ ως περιοχή και πωλήσεις άνω των 10000:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000))

Σημειώστε ότι το κριτήριο (που ονομάζεται επιχείρημα συμπερίληψης) είναι ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000)

Δεδομένου ότι χρησιμοποιώ δύο συνθήκες και χρειάζομαι και τις δύο να είναι αληθινές, χρησιμοποίησα τον τελεστή πολλαπλασιασμού για να συνδυάσω αυτά τα δύο κριτήρια. Αυτό επιστρέφει έναν πίνακα 0 και 1, όπου το 1 επιστρέφει μόνο όταν πληρούνται και οι δύο προϋποθέσεις.

Σε περίπτωση που δεν υπάρχουν εγγραφές που πληρούν τα κριτήρια, η συνάρτηση θα επιστρέψει το #CALC! λάθος.

Και σε περίπτωση που θέλετε να επιστρέψετε κάτι που σημαίνει (αντί για το σφάλμα), μπορείτε να χρησιμοποιήσετε έναν τύπο όπως φαίνεται παρακάτω:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "ΗΠΑ")*($ C $ 2: $ C $ 11> 10000), "Δεν βρέθηκε τίποτα")

Εδώ, χρησιμοποίησα το "Not Found" ως τρίτο όρισμα, το οποίο χρησιμοποιείται όταν δεν βρεθούν εγγραφές που να πληρούν τα κριτήρια.

Παράδειγμα 4: Φιλτράρισμα δεδομένων με πολλαπλά κριτήρια (OR)

Μπορείτε επίσης να τροποποιήσετε το όρισμα «συμπερίληψη» στη συνάρτηση FILTER για να ελέγξετε για κριτήρια OR (όπου οποιαδήποτε από τις δεδομένες συνθήκες μπορεί να είναι αληθής).

Για παράδειγμα, ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να φιλτράρετε τις εγγραφές όπου η χώρα είναι είτε οι ΗΠΑ είτε ο Καναδάς.

Παρακάτω είναι ο τύπος που θα το κάνει αυτό:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+($ B $ 2: $ B $ 11 = "Καναδάς"))

Σημειώστε ότι στον παραπάνω τύπο, απλώς πρόσθεσα τις δύο συνθήκες χρησιμοποιώντας τον τελεστή προσθήκης. Δεδομένου ότι κάθε μία από αυτές τις συνθήκες επιστρέφει έναν πίνακα TRUE και FALSE, μπορώ να προσθέσω για να πάρω έναν συνδυασμένο πίνακα όπου είναι TRUE εάν πληρούται κάποια από τις προϋποθέσεις.

Ένα άλλο παράδειγμα θα μπορούσε να είναι όταν θέλετε να φιλτράρετε όλες τις εγγραφές όπου είτε η χώρα είναι οι ΗΠΑ είτε η αξία πώλησης είναι μεγαλύτερη από 10000.

Ο παρακάτω τύπος θα το κάνει αυτό:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10000))

Σημείωση: Όταν χρησιμοποιείτε κριτήρια AND σε συνάρτηση FILTER, χρησιμοποιήστε τον τελεστή πολλαπλασιασμού (*) και όταν χρησιμοποιείτε τα κριτήρια OR, χρησιμοποιήστε τον τελεστή προσθήκης (+).

Παράδειγμα 5: Φιλτράρισμα δεδομένων για υπέρβαση/κάτω από τον μέσο όρο εγγραφών

Μπορείτε να χρησιμοποιήσετε τύπους στη συνάρτηση FILTER για να φιλτράρετε και να εξαγάγετε εγγραφές όπου η τιμή είναι πάνω ή κάτω από το μέσο όρο.

Για παράδειγμα, ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να φιλτράρετε όλες τις εγγραφές όπου η τιμή πώλησης είναι πάνω από το μέσο όρο.

Μπορείτε να το κάνετε χρησιμοποιώντας τον ακόλουθο τύπο:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, C2: C11> ΜΕΣΟ (C2: C11))

Ομοίως, για κάτω από το μέσο όρο, μπορείτε να χρησιμοποιήσετε τον παρακάτω τύπο:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, C2: C11<>

Κάντε κλικ εδώ για να κατεβάσετε το Παράδειγμα αρχείου και ακολουθήστε

Παράδειγμα 6: Φιλτράρισμα μόνο των εγγραφών αριθμών EVEN (ords εγγραφών αριθμών ODD)

Σε περίπτωση που χρειαστεί να φιλτράρετε γρήγορα και να εξαγάγετε όλες τις εγγραφές από σειρές ζυγών αριθμών ή μονές σειρές αριθμών, μπορείτε να το κάνετε με τη συνάρτηση ΦΙΛΤΡΟ.

Για να το κάνετε αυτό, πρέπει να ελέγξετε τον αριθμό γραμμής στη συνάρτηση FILTER και να φιλτράρετε μόνο τους αριθμούς γραμμών που πληρούν τα κριτήρια αριθμού γραμμής.

Ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλω να εξαγάγω μόνο εγγραφές ζυγού αριθμού από αυτό το σύνολο δεδομένων.

Παρακάτω είναι ο τύπος που θα το κάνει αυτό:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 0)

Ο παραπάνω τύπος χρησιμοποιεί τη λειτουργία MOD για να ελέγξει τον αριθμό σειράς κάθε εγγραφής (ο οποίος δίνεται από τη συνάρτηση ROW).

Ο τύπος MOD (ROW (A2: A11) -1,2) = 0 επιστρέφει TRUE όταν ο αριθμός σειράς είναι άρτιος και FALSE όταν είναι περιττός. Σημειώστε ότι έχω αφαιρέσει 1 από το τμήμα ROW (A2: A11) καθώς η πρώτη εγγραφή βρίσκεται στη δεύτερη σειρά και αυτό προσαρμόζει τον αριθμό σειράς ώστε να θεωρείται η δεύτερη σειρά ως πρώτη εγγραφή.

Ομοίως, μπορείτε να φιλτράρετε όλες τις εγγραφές με περιττό αριθμό χρησιμοποιώντας τον παρακάτω τύπο:

= ΦΙΛΤΡΟ ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 1)

Παράδειγμα 7: Ταξινόμηση των φιλτραρισμένων δεδομένων με τύπο

Η χρήση της λειτουργίας FILTER με άλλες λειτουργίες μας επιτρέπει να κάνουμε πολλά περισσότερα.

Για παράδειγμα, εάν φιλτράρετε ένα σύνολο δεδομένων χρησιμοποιώντας τη συνάρτηση FILTER, μπορείτε να χρησιμοποιήσετε τη συνάρτηση SORT μαζί του για να έχετε το αποτέλεσμα που έχει ήδη ταξινομηθεί.

Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω και θέλετε να φιλτράρετε όλες τις εγγραφές όπου η αξία πωλήσεων είναι μεγαλύτερη από 10000. Μπορείτε να χρησιμοποιήσετε τη συνάρτηση SORT με τη συνάρτηση για να βεβαιωθείτε ότι τα δεδομένα που προκύπτουν έχουν ταξινομηθεί με βάση την αξία πωλήσεων.

Ο παρακάτω τύπος θα το κάνει αυτό:

= SORT (FILTER ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)

Η παραπάνω συνάρτηση χρησιμοποιεί τη συνάρτηση ΦΙΛΤΡΟ για να λάβει τα δεδομένα όπου η τιμή πώλησης στη στήλη C είναι μεγαλύτερη από 10000. Αυτός ο πίνακας που επιστρέφεται από τη συνάρτηση ΦΙΛΤΡΟΥ χρησιμοποιείται στη συνέχεια στη συνάρτηση SORT για να ταξινομήσει αυτά τα δεδομένα με βάση την αξία πωλήσεων.

Το δεύτερο όρισμα στη συνάρτηση SORT είναι 3, το οποίο πρέπει να ταξινομηθεί με βάση την τρίτη στήλη. Και το τέταρτο επιχείρημα είναι -1 που είναι να ταξινομήσουμε αυτά τα δεδομένα σε φθίνουσα σειρά.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου

Αυτά λοιπόν είναι 7 παραδείγματα για να χρησιμοποιήσετε τη συνάρτηση FILTER στο Excel.

Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο!

Μπορεί επίσης να σας αρέσουν τα ακόλουθα σεμινάρια Excel:

1. Πώς να φιλτράρετε κελιά με έντονη μορφοποίηση γραμματοσειράς στο Excel
2. Πλαίσιο αναζήτησης δυναμικού φίλτρου Excel
3. Πώς να φιλτράρετε δεδομένα σε έναν Συγκεντρωτικό πίνακα στο Excel