Γενική φόρμουλα
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Περίληψη
Για δυναμική ταξινόμηση δεδομένων με αριθμούς και κείμενο με αλφαβητική σειρά, μπορείτε να χρησιμοποιήσετε έναν τύπο για να δημιουργήσετε μια αριθμητική κατάταξη σε μια βοηθητική στήλη και, στη συνέχεια, να χρησιμοποιήσετε το INDEX και MATCH για να εμφανίσετε τιμές με βάση την κατάταξη. Στο παράδειγμα που παρουσιάζεται ο τύπος στο C5 είναι:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
όπου "δεδομένα" είναι η ονομαστική περιοχή B5: B13.
Εξήγηση
Αυτός ο τύπος δημιουργεί πρώτα μια τιμή κατάταξης χρησιμοποιώντας μια έκφραση με βάση COUNTIF:
=COUNTIF(data,"<="&B5)
που εξηγείται με περισσότερες λεπτομέρειες εδώ. Εάν τα δεδομένα περιέχουν όλες τις τιμές κειμένου ή όλες τις αριθμητικές τιμές, η κατάταξη θα είναι σωστή. Ωστόσο, εάν τα δεδομένα περιλαμβάνουν κείμενο και αριθμούς, πρέπει να "αλλάξουμε" την κατάταξη όλων των τιμών κειμένου για να λάβουμε υπόψη τις αριθμητικές τιμές. Αυτό γίνεται με το δεύτερο μέρος του τύπου εδώ:
+(COUNT(data)*ISTEXT(B7))
Εδώ, χρησιμοποιούμε τη συνάρτηση COUNT για να λάβουμε έναν αριθμό αριθμητικών τιμών στα δεδομένα και, στη συνέχεια, πολλαπλασιάζουμε το αποτέλεσμα με το λογικό αποτέλεσμα του ISTEXT, το οποίο ελέγχει εάν η τιμή είναι κείμενο και επιστρέφει είτε ΑΛΗΘΕΙΑ ή ΛΑΘΟΣ. Αυτό ακυρώνει αποτελεσματικά το COUNT αποτέλεσμα όταν εργαζόμαστε με έναν αριθμό στην τρέχουσα σειρά.
Διαχείριση αντιγράφων
Εάν τα δεδομένα περιέχουν διπλότυπα, ο τύπος μπορεί να τροποποιηθεί όπως φαίνεται παρακάτω για να εκχωρηθεί μια διαδοχική κατάταξη σε τιμές που εμφανίζονται περισσότερες από μία φορές:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Αυτή η έκδοση προσαρμόζει τη λογική της αρχικής συνάρτησης COUNTIF και προσθέτει μια άλλη COUNTIF με μια επεκτεινόμενη αναφορά σε αντίγραφα αύξησης.
Εμφάνιση τιμών ταξινομημένων
Για να ανακτήσετε και να εμφανίσετε τιμές ταξινομημένες τιμές με αλφαβητική σειρά χρησιμοποιώντας την υπολογισμένη τιμή κατάταξης, το E5 περιέχει τον ακόλουθο τύπο INDEX και MATCH:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
όπου "δεδομένα" είναι η ονομαζόμενη περιοχή B5: B13 και "rank" είναι η ονομαζόμενη περιοχή C5: C13.
Για περισσότερες πληροφορίες σχετικά με τον τρόπο λειτουργίας αυτού του τύπου, δείτε το παράδειγμα εδώ.
Αντιμετωπίζοντας κενά
Τα κενά κελιά θα δημιουργήσουν μια βαθμολογία μηδέν. Υποθέτοντας ότι θέλετε να αγνοήσετε τα κενά κελιά, αυτό λειτουργεί καλά επειδή ο παραπάνω τύπος INDEX και MATCH ξεκινά από το 1. Ωστόσο, θα δείτε # N / A σφάλματα στο τέλος των ταξινομημένων τιμών, μία για κάθε κενό κελί. Ένας εύκολος τρόπος για να το χειριστείτε είναι να τυλίξετε τον τύπο INDEX και MATCH σε IFERROR ως εξής:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
