![](https://cdn.wiki-base.com/7401116/excel_formula_filter_to_extract_matching_values__2.png.webp)
Γενική φόρμουλα
=FILTER(list1,COUNTIF(list2,list1))
Περίληψη
Για να φιλτράρετε δεδομένα για να εξαγάγετε αντίστοιχες τιμές σε δύο λίστες, μπορείτε να χρησιμοποιήσετε τη συνάρτηση FILTER και τη συνάρτηση COUNTIF ή COUNTIFS. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F5 είναι:
=FILTER(list1,COUNTIF(list2,list1))
όπου list1 (B5: B16) και list2 (D5: D14) ονομάζονται εύρη. Το αποτέλεσμα που επέστρεψε το FILTER περιλαμβάνει μόνο τις τιμές στη λίστα1 που εμφανίζονται στη λίστα2 .
Σημείωση: Το FILTER είναι μια νέα λειτουργία δυναμικής συστοιχίας στο Excel 365.
Εξήγηση
Αυτός ο τύπος βασίζεται στη συνάρτηση FILTER για την ανάκτηση δεδομένων βάσει μιας λογικής δοκιμής που έχει δημιουργηθεί με τη συνάρτηση COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
δουλεύοντας από μέσα προς τα έξω, η συνάρτηση COUNTIF χρησιμοποιείται για τη δημιουργία του πραγματικού φίλτρου:
COUNTIF(list2,list1)
Παρατηρήστε ότι χρησιμοποιούμε το list2 ως όρισμα εύρους και το list1 ως όρισμα κριτηρίων. Με άλλα λόγια, ζητάμε από τον COUNTIF να μετρήσει όλες τις τιμές στη λίστα1 που εμφανίζονται στη λίστα2. Επειδή δίνουμε COUNTIF πολλές τιμές για κριτήρια, λαμβάνουμε έναν πίνακα με πολλαπλά αποτελέσματα:
(1;1;0;1;0;1;0;0;1;0;1;1)
Σημειώστε ότι ο πίνακας περιέχει 12 μετρήσεις, μία για κάθε τιμή στη λίστα1 . Μια μηδενική τιμή δείχνει μια τιμή στη λίστα1 που δεν βρίσκεται στη λίστα2 . Οποιοσδήποτε άλλος θετικός αριθμός δηλώνει μια τιμή στη λίστα1 που βρίσκεται στη λίστα2 . Αυτός ο πίνακας επιστρέφεται απευθείας στη συνάρτηση FILTER ως όρισμα include:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Η λειτουργία φίλτρου χρησιμοποιεί τον πίνακα ως φίλτρο. Οποιαδήποτε τιμή στη λίστα1 που σχετίζεται με μηδέν καταργείται, ενώ οποιαδήποτε τιμή που σχετίζεται με θετικό αριθμό επιμένει.
Το αποτέλεσμα είναι ένας πίνακας 7 τιμών που ταιριάζουν στην περιοχή F5: F11. Εάν αλλάξουν τα δεδομένα, το FILTER θα υπολογίσει εκ νέου και θα επιστρέψει μια νέα λίστα με αντίστοιχες τιμές με βάση τα νέα δεδομένα.
Μη αντίστοιχες τιμές
Για να εξαγάγετε τιμές που δεν ταιριάζουν από τη λίστα1 (δηλ. Τιμές στη λίστα1 που δεν εμφανίζονται στη λίστα2 ), μπορείτε να προσθέσετε τη συνάρτηση NOT στον τύπο όπως:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
Η συνάρτηση NOT αντιστρέφει αποτελεσματικά το αποτέλεσμα από COUNTIF - οποιοσδήποτε μη μηδενικός αριθμός γίνεται FALSE και οποιαδήποτε μηδενική τιμή γίνεται TRUE. Το αποτέλεσμα είναι μια λίστα με τις τιμές στη λίστα1 που δεν υπάρχουν στη λίστα2 .
Με INDEX
Είναι δυνατό να δημιουργήσετε έναν τύπο για να εξαγάγετε τιμές που ταιριάζουν χωρίς τη συνάρτηση FILTER, αλλά ο τύπος είναι πιο περίπλοκος. Μία επιλογή είναι να χρησιμοποιήσετε τη συνάρτηση INDEX σε έναν τύπο όπως αυτό:
Ο τύπος στο G5, αντιγράφεται παρακάτω:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter, εκτός από το Excel 365.
Ο πυρήνας αυτού του τύπου είναι η συνάρτηση INDEX, η οποία λαμβάνει τη λίστα1 ως όρισμα πίνακα. Ο περισσότερος από τον υπόλοιπο τύπο υπολογίζει απλά τον αριθμό σειράς που θα χρησιμοποιηθεί για αντιστοίχιση τιμών. Αυτή η έκφραση δημιουργεί μια λίστα σχετικών αριθμών σειράς:
ROW(list1)-ROW(INDEX(list1,1,1))+1
που επιστρέφει έναν πίνακα 12 αριθμών που αντιπροσωπεύουν τις σειρές στη λίστα 1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Αυτά φιλτράρονται με τη συνάρτηση IF και την ίδια λογική που χρησιμοποιήθηκε παραπάνω στο ΦΙΛΤΡΟ, με βάση τη συνάρτηση COUNTIF:
COUNTIF(list2,list1) // find matching values
Ο πίνακας που προκύπτει μοιάζει με αυτό:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Αυτός ο πίνακας παραδίδεται απευθείας στη συνάρτηση SMALL, η οποία χρησιμοποιείται για τη λήψη του επόμενου αντίστοιχου αριθμού γραμμής καθώς ο τύπος αντιγράφεται στη στήλη. Η τιμή k για το SMALL (think nth) υπολογίζεται με επεκτεινόμενο εύρος:
ROWS($G$5:G5) // incrementing value for k
Η συνάρτηση IFERROR χρησιμοποιείται για την παγίδευση σφαλμάτων που προκύπτουν όταν ο τύπος αντιγράφεται και εξαντλείται οι αντίστοιχες τιμές. Για ένα άλλο παράδειγμα αυτής της ιδέας, δείτε αυτόν τον τύπο.