Γενική φόρμουλα
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Περίληψη
Για να εξαγάγετε μόνο μοναδικές τιμές από μια λίστα ή στήλη, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα βάσει INDEX, MATCH και COUNTIF. Στο παράδειγμα που εμφανίζεται, ο τύπος στο D5, που αντιγράφηκε κάτω, είναι:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
όπου "list" είναι η ονομαζόμενη περιοχή B5: B11.
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί χρησιμοποιώντας το control + shift + enter.
Εξήγηση
Ο πυρήνας αυτού του τύπου είναι μια βασική αναζήτηση με το INDEX:
=INDEX(list,row)
Με άλλα λόγια, δώστε στο INDEX τη λίστα και έναν αριθμό σειράς και το INDEX θα ανακτήσει μια τιμή για προσθήκη στη μοναδική λίστα.
Η σκληρή δουλειά είναι να βρούμε τον αριθμό ROW για να δώσουμε το INDEX, έτσι ώστε να έχουμε μόνο μοναδικές τιμές. Αυτό γίνεται με MATCH και COUNTIF, και το κύριο κόλπο είναι εδώ:
COUNTIF($D$4:D4,list)
Εδώ, COUNTIF μετρά πόσες φορές τα στοιχεία που βρίσκονται ήδη στη μοναδική λίστα εμφανίζονται στην κύρια λίστα, χρησιμοποιώντας μια επεκτεινόμενη αναφορά για το εύρος, $ D $ 4: D4.
Μια επεκτεινόμενη αναφορά είναι απόλυτη από τη μία πλευρά, σε σχέση με την άλλη. Σε αυτήν την περίπτωση, καθώς ο τύπος αντιγράφεται προς τα κάτω, η αναφορά θα επεκταθεί ώστε να περιλαμβάνει περισσότερες σειρές στη μοναδική λίστα.
Σημειώστε ότι η αναφορά ξεκινά στο D4, μία σειρά πάνω από την πρώτη μοναδική καταχώριση, στη μοναδική λίστα. Αυτό είναι σκόπιμο - θέλουμε να μετρήσουμε τα στοιχεία * ήδη * στη μοναδική λίστα και δεν μπορούμε να συμπεριλάβουμε το τρέχον κελί χωρίς να δημιουργήσουμε μια κυκλική αναφορά. Ξεκινάμε λοιπόν από την παραπάνω σειρά.
Σημαντικό: βεβαιωθείτε ότι η επικεφαλίδα για τη μοναδική λίστα δεν εμφανίζεται στην κύρια λίστα.
Για τα κριτήρια στο COUNTIF, χρησιμοποιούμε την κύρια λίστα. Όταν δοθούν πολλαπλά κριτήρια, το COUNTIF θα επιστρέψει πολλά αποτελέσματα σε έναν πίνακα. Σε κάθε νέα σειρά, έχουμε έναν διαφορετικό πίνακα όπως αυτό:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Σημείωση: Το COUNTIF χειρίζεται πολλαπλά κριτήρια με σχέση "Ή" (δηλαδή COUNTIF (εύρος, ("κόκκινο", "μπλε", "πράσινο")) μετράει κόκκινο, μπλε ή πράσινο.
Τώρα έχουμε τους πίνακες που πρέπει να βρούμε θέσεις (αριθμοί σειράς). Για αυτό, χρησιμοποιούμε το MATCH, ρυθμίστηκε για ακριβή αντιστοίχιση, για να βρούμε μηδενικές τιμές. Εάν βάλουμε τους πίνακες που δημιουργήθηκαν από την COUNTIF παραπάνω στο MATCH, εδώ είναι αυτό που παίρνουμε:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
Το MATCH εντοπίζει αντικείμενα αναζητώντας ένα πλήθος μηδέν (δηλαδή αναζητώντας αντικείμενα που δεν εμφανίζονται ακόμη στη μοναδική λίστα). Αυτό λειτουργεί, επειδή το MATCH επιστρέφει πάντα τον πρώτο αγώνα όταν υπάρχουν διπλότυπα.
Τέλος, οι θέσεις τροφοδοτούνται στο INDEX ως αριθμοί σειράς και το INDEX επιστρέφει το όνομα σε αυτήν τη θέση.
Έκδοση χωρίς συστοιχία με LOOKUP
Μπορείτε να δημιουργήσετε έναν τύπο χωρίς συστοιχία για να εξαγάγετε μοναδικά στοιχεία χρησιμοποιώντας τη λειτουργία ευέλικτου LOOKUP:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Ο τύπος κατασκευής είναι παρόμοιος με τον τύπο INDEX MATCH παραπάνω, αλλά το LOOKUP μπορεί να χειριστεί τη λειτουργία του πίνακα εγγενώς.
- Η COUNTIF επιστρέφει μετρήσεις κάθε τιμής από τη "λίστα" στο αναπτυσσόμενο εύρος $ D $ 4: D4
- Η σύγκριση με το μηδέν δημιουργεί έναν πίνακα τιμών TRUE και FALSE
- Ο αριθμός 1 διαιρείται με τον πίνακα, δημιουργώντας έναν πίνακα με σφάλματα 1s και # DIV / 0
- Αυτός ο πίνακας γίνεται το lookup_vector μέσα στο LOOKUP
- Η τιμή αναζήτησης 2 είναι μεγαλύτερη από οποιαδήποτε τιμή στο lookup_vector
- Το LOOKUP θα ταιριάξει με την τελευταία τιμή μη σφάλματος στον πίνακα αναζήτησης
- Το LOOKUP επιστρέφει την αντίστοιχη τιμή στο αποτέλεσμα_vector, την ονομαζόμενη περιοχή "λίστα"
Εξαγωγή αντικειμένων που εμφανίζονται μόνο μία φορά
Ο παραπάνω τύπος LOOKUP είναι εύκολο να επεκταθεί με λογική λογικής. Για να εξαγάγετε μια λίστα με μοναδικά στοιχεία που εμφανίζονται μόνο μία φορά στα δεδομένα προέλευσης, μπορείτε να χρησιμοποιήσετε έναν τύπο όπως αυτό:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Η μόνη προσθήκη είναι η δεύτερη έκφραση COUNTIF:
COUNTIF(list,list)=1
Εδώ, το COUNTIF επιστρέφει μια σειρά μετρήσεων στοιχείων ως εξής:
(2;2;2;2;2;1;2)
που συγκρίνονται με το 1, με αποτέλεσμα έναν πίνακα τιμών TRUE / FALSE:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
που λειτουργούν ως "φίλτρο" για τον περιορισμό της παραγωγής σε στοιχεία που εμφανίζονται μόνο μία φορά στα δεδομένα προέλευσης.
