Πώς να χρησιμοποιήσετε τη συνάρτηση ΦΙΛΤΡΟ του Excel

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

Κατά το φιλτράρισμα δεδομένων στο Excel, στον κόσμο του pre-Office 365, βασιζόμασταν κυρίως σε ενσωματωμένα φίλτρα του Excel ή το πολύ προηγμένα φίλτρα ή σύνθετους τύπους SUMPRODUCT.Αυτό είναι συχνά μια περίπλοκη λύση εάν πρέπει να φιλτράρετε μέρος του συνόλου δεδομένων.

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

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

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

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

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

Ακολουθεί η σύνταξη της συνάρτησης FILTER:

=FILTER(πίνακας,περιλαμβάνει,[if_empty])

• παράταξη - αυτό είναι το εύρος των κελιών όπου έχετε τα δεδομένα και θέλετε να φιλτράρετε ορισμένα δεδομένα από αυτά
• περιλαμβάνουν - Αυτή είναι η συνθήκη που λέει στη συνάρτηση ποιες εγγραφές να φιλτράρει
• [if_empty] – Αυτή είναι μια προαιρετική παράμετρος όπου μπορείτε να καθορίσετε τι θα επιστρέψετε εάν η συνάρτηση FILTER δεν βρει αποτελέσματα.Από προεπιλογή (όταν δεν καθορίζεται) επιστρέφει #CALC!λάθος

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

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

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

Εδώ είναι ο τύπος FILTER για να το κάνετε αυτό:

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

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

Αυτή η συνθήκη θα κάνει τη συνάρτηση FILTER να ελέγχει κάθε κελί στη στήλη B (κελιά με το εύρος) και να φιλτράρει μόνο τις εγγραφές που ταιριάζουν με αυτήν τη συνθήκη.

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

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

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

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

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

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

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

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

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

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

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

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

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

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

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

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

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

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

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

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

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

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

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

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

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

=FILTER($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!Λάθος.

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

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

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

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

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

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

Εδώ είναι ο τύπος για να το κάνετε αυτό:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

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

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

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

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

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

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

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

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

Μπορείτε να το κάνετε αυτό με τον ακόλουθο τύπο:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

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

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Παράδειγμα 6: Φιλτράρισμα μόνο ζυγών εγγραφών (ή περιττών εγγραφών)

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

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

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

Εδώ είναι ο τύπος για να το κάνετε αυτό:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

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

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

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

=FILTER($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)

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

Η δεύτερη παράμετρος στη συνάρτηση SORT είναι 3, η οποία πρέπει να ταξινομηθεί σύμφωνα με την τρίτη στήλη.Η τέταρτη παράμετρος είναι -1, η οποία ταξινομεί τα δεδομένα με φθίνουσα σειρά.

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

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