Γενική φόρμουλα
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Περίληψη
Για να συγκρίνετε δύο λίστες και να τραβήξετε τιμές που λείπουν από τη μία λίστα στην άλλη, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα που βασίζεται σε INDEX και MATCH. Στο παράδειγμα που εμφανίζεται, η τελευταία τιμή στη λίστα Β βρίσκεται στο κελί D11. Ο τύπος στο D12, αντιγράφεται, είναι:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
όπου "πλήρης" είναι η ονομαζόμενη περιοχή B5: B15.
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter.
Εξήγηση
Δουλεύοντας από μέσα προς τα έξω, ο πυρήνας αυτού του τύπου είναι η εσωτερική έκφραση MATCH:
ISNA(MATCH(complete,$D$5:D11,0)
Εδώ, η συνάρτηση MATCH χρησιμοποιείται για να συγκρίνει όλες τις "πλήρεις" τιμές με τη μερική λίστα. Η ονομαζόμενη περιοχή "πλήρης" χρησιμοποιείται για τιμές αναζήτησης και η μερική λίστα χρησιμοποιείται ως πίνακας αναζήτησης. Παρατηρήστε, ωστόσο, ότι η μερική λίστα καταχωρείται ως επεκτεινόμενο εύρος που τελειώνει "ένα κελί πάνω" από το κελί τύπου. Αυτό επιτρέπει στη μερική λίστα να επεκταθεί για να συμπεριλάβει νέες τιμές όπως εμφανίζονται κάτω από την αρχική λίστα.
Το αποτέλεσμα του MATCH είναι μια σειρά από αριθμούς και # N / A λάθη, όπου οι αριθμοί αντιπροσωπεύουν τιμές στην πλήρη λίστα που υπάρχουν στη μερική λίστα. και τα σφάλματα αντιπροσωπεύουν τιμές που λείπουν:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Η συνάρτηση ISNA χρησιμοποιείται για τη μετατροπή αυτών των αποτελεσμάτων σε έναν πίνακα τιμών TRUE και FALSE. Σε αυτόν τον πίνακα, το TRUE αντιστοιχεί σε τιμές που λείπουν και το FALSE αντιστοιχεί σε υπάρχουσες τιμές:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Η συνάρτηση ISNA επιστρέφει αυτόν τον πίνακα στο εξωτερικό MATCH ως πίνακα αναζήτησης. Η συνάρτηση MATCH επιστρέφει πάντα τον πρώτο αγώνα που βρέθηκε, οπότε ο αγώνας θα επιστρέψει τη θέση (σειρά) της πρώτης τιμής που λείπει. Αυτό το αποτέλεσμα επιστρέφεται στο INDEX ως αριθμός σειράς, με την ονομαζόμενη περιοχή "πλήρης" να παρέχεται ως πίνακας.
Στο κελί D12, η πρώτη τιμή που λείπει που βρέθηκε είναι το "ακτινίδιο" στη σειρά 2, οπότε έχουμε:
=INDEX(complete,2) // returns "kiwi"
Στο D13, το "ακτινίδιο" περιλαμβάνεται πλέον στην αναπτυσσόμενη αναφορά, οπότε η πρώτη τιμή που λείπει είναι "αχλάδι":
=INDEX(complete,5) // returns "pear"
Και ούτω καθεξής. Μόλις προστεθούν όλες οι τιμές που λείπουν, ο τύπος θα επιστρέψει το σφάλμα # N / A.
