Γενική φόρμουλα
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Περίληψη
Για δυναμική ταξινόμηση και εξαγωγή μοναδικών τιμών από μια λίστα δεδομένων, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα για να δημιουργήσετε μια κατάταξη σε μια βοηθητική στήλη και, στη συνέχεια, να χρησιμοποιήσετε έναν ειδικά κατασκευασμένο τύπο INDEX και MATCH για εξαγωγή μοναδικών τιμών. Στο παράδειγμα που εμφανίζεται, ο τύπος για τον καθορισμό της κατάταξης στο C5: C13 είναι:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
όπου "δεδομένα" είναι η ονομαστική περιοχή B5: B13.
Σημείωση: αυτός είναι ένας τύπος συστοιχίας πολλαπλών κυττάρων, ο οποίος έχει εισαχθεί με τον έλεγχο + shift + enter.
Εξήγηση
Σημείωση: η βασική ιδέα αυτού του τύπου προσαρμόζεται από ένα παράδειγμα στο εξαιρετικό βιβλίο του Mike Girvin Control + Shift + Enter.
Το παράδειγμα που εμφανίζεται χρησιμοποιεί διάφορους τύπους, οι οποίοι περιγράφονται παρακάτω. Σε υψηλό επίπεδο, η συνάρτηση MMULT χρησιμοποιείται για τον υπολογισμό μιας αριθμητικής κατάταξης σε μια βοηθητική στήλη (στήλη C) και στη συνέχεια αυτή η κατάταξη χρησιμοποιείται από έναν τύπο INDEX και MATCH στη στήλη G για την εξαγωγή μοναδικών τιμών.
Τιμές δεδομένων κατάταξης
Η συνάρτηση MMULT εκτελεί πολλαπλασιασμό μήτρας και χρησιμοποιείται για την εκχώρηση αριθμητικής κατάταξης σε κάθε τιμή. Ο πρώτος πίνακας δημιουργείται με την ακόλουθη έκφραση:
--(data>TRANSPOSE(data))
Εδώ, χρησιμοποιούμε τη συνάρτηση TRANSPOSE για να δημιουργήσουμε μια οριζόντια σειρά δεδομένων και όλες οι τιμές συγκρίνονται μεταξύ τους. Στην ουσία, κάθε τιμή συγκρίνεται με κάθε άλλη τιμή για να απαντηθεί η ερώτηση "είναι αυτή η τιμή μεγαλύτερη από κάθε άλλη τιμή". Αυτό έχει ως αποτέλεσμα μια δισδιάστατη συστοιχία, 9 στήλες x 9 σειρές, γεμάτες με ΑΛΗΘΕΣ και ΑΛΗΘΕΣ τιμές. Το διπλό αρνητικό (-) χρησιμοποιείται για τον εξαναγκασμό των τιμών TRUE FALSE σε 1s και μηδενικά. Μπορείτε να απεικονίσετε τον πίνακα που προκύπτει ως εξής:
Η μήτρα των 1s και μηδενικά γίνεται πίνακας1 μέσα στη συνάρτηση MMULT. Το Array2 δημιουργείται με αυτήν την έκφραση:
ROW(data)^0
Εδώ, κάθε αριθμός σειράς στα "δεδομένα" αυξάνεται στην ισχύ του μηδέν για να δημιουργήσει μια μονοδιάστατη συστοιχία, 1 στήλη x 9 σειρές, γεμάτη με τον αριθμό 1. Το MMULT επιστρέφει στη συνέχεια το προϊόν μήτρας των δύο συστοιχιών, οι οποίες γίνονται οι τιμές που εμφανίζονται στη στήλη κατάταξης.
Παίρνουμε και τις 9 βαθμολογίες ταυτόχρονα σε έναν πίνακα, οπότε πρέπει να βάλουμε τα αποτελέσματα σε διαφορετικά κελιά ταυτόχρονα. Διαφορετικά, κάθε κελί θα εμφανίζει μόνο την πρώτη τιμή κατάταξης στον πίνακα που επιστρέφεται.
Σημείωση: αυτός είναι ένας τύπος συστοιχίας πολλαπλών κυττάρων, που έχει εισαχθεί με τον έλεγχο + shift + enter, στην περιοχή C5: C13.
Χειρισμός κενών κελιών
Τα κενά κελιά αντιμετωπίζονται με αυτό το μέρος του τύπου κατάταξης:
=IF(data="",ROWS(data)
Εδώ, προτού εκτελέσουμε το MMULT, ελέγχουμε εάν το τρέχον κελί στα "δεδομένα" είναι κενό. Εάν ναι, εκχωρούμε μια τιμή κατάταξης που ισούται με τον αριθμό γραμμής στα δεδομένα. Αυτό γίνεται για να αναγκάσει τα κενά κελιά στο κάτω μέρος της λίστας, όπου μπορούν εύκολα να αποκλειστούν αργότερα καθώς εξάγονται μοναδικές τιμές (εξηγείται παρακάτω).
Μετρώντας μοναδικές τιμές
Για να μετρήσετε μοναδικές τιμές στα δεδομένα, ο τύπος στο E5 είναι:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Δεδομένου ότι ο παραπάνω τύπος κατάταξης εκχωρεί μια αριθμητική κατάταξη σε κάθε τιμή, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση FREQUENCY με SUM για να μετρήσουμε μοναδικές τιμές. Αυτός ο τύπος εξηγείται λεπτομερώς εδώ. Στη συνέχεια αφαιρούμε το 1 από το αποτέλεσμα εάν υπάρχουν κενά κελιά στα δεδομένα:
-(blank>0)
όπου "κενό" είναι το ονομαζόμενο εύρος E8 και περιέχει αυτόν τον τύπο:
=COUNTBLANK(data)
Ουσιαστικά, μειώνουμε τον μοναδικό αριθμό κατά ένα εάν υπάρχουν κενά κελιά στα δεδομένα, καθώς δεν τα συμπεριλαμβάνουμε στα αποτελέσματα. Ο μοναδικός αριθμός στο κελί E5 ονομάζεται "μοναδικός" (για μοναδικό αριθμό) και χρησιμοποιείται από τον τύπο INDEX και MATCH για να φιλτράρει τα κενά κελιά (περιγράφονται παρακάτω).
Εξαγωγή μοναδικών τιμών
Για να εξαγάγετε μοναδικές τιμές, το G5 περιέχει τον ακόλουθο τύπο, αντιγράφεται:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Πριν εκτελέσουμε τον τύπο INDEX και MATCH, ελέγχουμε πρώτα αν ο τρέχων αριθμός σειρών στην περιοχή εξαγωγής είναι μεγαλύτερος από τον μοναδικό αριθμό που ονομάζεται εύρος "μοναδικό" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Εάν ναι, έχουμε ολοκληρώσει την εξαγωγή μοναδικών τιμών και επιστρέφουμε μια κενή συμβολοσειρά (""). Εάν όχι, εκτελούμε τον τύπο εξαγωγής:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Σημειώστε ότι υπάρχουν δύο συναρτήσεις MATCH εδώ, η μία μέσα στην άλλη. Το εσωτερικό MATCH χρησιμοποιεί ένα επεκτεινόμενο εύρος για έναν πίνακα και το ονομαζόμενο εύρος "δεδομένα" για την τιμή αναζήτησης:
MATCH(data,$G$4:G4,0)
Παρατηρήστε ότι το αναπτυσσόμενο εύρος ξεκινά από την "γραμμή πάνω", στη σειρά 4 στο παράδειγμα. Το αποτέλεσμα από το εσωτερικό MATCH είναι ένας πίνακας που, για κάθε τιμή στα δεδομένα, περιέχει είτε μια αριθμητική θέση (η τιμή έχει ήδη εξαχθεί) είτε το σφάλμα # N / A (η τιμή δεν έχει εξαχθεί ακόμη). Στη συνέχεια, χρησιμοποιούμε IF και ISNA για φιλτράρισμα αυτών των αποτελεσμάτων και επιστρέφουμε την τιμή κατάταξης για όλες τις τιμές στα "δεδομένα" που δεν έχουν εξαχθεί ακόμη:
IF(ISNA(results),rank))
Αυτή η λειτουργία οδηγεί σε έναν πίνακα, ο οποίος τροφοδοτείται στη συνάρτηση MIN για να πάρει την "ελάχιστη τιμή κατάταξης" για τιμές δεδομένων που δεν έχουν εξαχθεί ακόμη. Η συνάρτηση MIN επιστρέφει αυτήν την τιμή στο εξωτερικό MATCH ως τιμή αναζήτησης και το ονομαζόμενο εύρος "rank" ως πίνακα:
MATCH(min_not_extracted,rank)),rank,0)
Τέλος, το MATCH επιστρέφει τη θέση της χαμηλότερης τιμής κατάταξης στο INDEX ως αριθμό σειράς και το INDEX επιστρέφει την τιμή δεδομένων στην τρέχουσα σειρά του εύρους εξαγωγής.
