Προέχω

Ταξινόμηση κειμένου και αριθμών με τύπο

Sort Text Numbers With Formula

Τύπος Excel: Ταξινόμηση κειμένου και αριθμών με τύποΓενικός τύπος | _+_ | Περίληψη

Για δυναμική ταξινόμηση δεδομένων τόσο με αριθμούς όσο και με κείμενο με αλφαβητική σειρά, μπορείτε να χρησιμοποιήσετε έναν τύπο για να δημιουργήσετε μια αριθμητική κατάταξη σε μια στήλη βοηθού και, στη συνέχεια, χρησιμοποιήστε INDEX και MATCH για να εμφανίσετε τιμές με βάση την κατάταξη. Στο παράδειγμα που φαίνεται ο τύπος στο C5 είναι:





πώς να φτιάξετε έναν συγκεντρωτικό πίνακα στο excel
= COUNTIF (data,'<='&A1)+( COUNT (data)* ISTEXT (A1))

όπου 'δεδομένα' είναι το ονομασμένο εύρος Β5: Β13.

Εξήγηση

Αυτός ο τύπος δημιουργεί πρώτα μια τιμή κατάταξης χρησιμοποιώντας μια έκφραση που βασίζεται στο COUNTIF:





 
= COUNTIF (data,'<='&B5)+( COUNT (data)* ISTEXT (B5))

το οποίο είναι εξηγείται λεπτομερέστερα εδώ Το Εάν τα δεδομένα περιέχουν όλες τις τιμές κειμένου ή όλες τις αριθμητικές τιμές, η κατάταξη θα είναι σωστή. Ωστόσο, εάν τα δεδομένα περιλαμβάνουν τόσο κείμενο όσο και αριθμούς, πρέπει να 'μετατοπίσουμε' την κατάταξη όλων των τιμών κειμένου για να λάβουμε υπόψη τις αριθμητικές τιμές. Αυτό γίνεται με το δεύτερο μέρος του τύπου εδώ:

 
= COUNTIF (data,'<='&B5)

Εδώ, χρησιμοποιούμε τη συνάρτηση COUNT για να πάρουμε έναν αριθμό αριθμητικών τιμών στα δεδομένα και μετά πολλαπλασιάζουμε το αποτέλεσμα με το λογικό αποτέλεσμα του ISTEXT, το οποίο ελέγχει αν η τιμή είναι κείμενο και επιστρέφει είτε TRUE είτε FALSE. Αυτό ακυρώνει αποτελεσματικά το COUNT αποτέλεσμα όταν εργαζόμαστε με έναν αριθμό στην τρέχουσα σειρά.



Χειρισμός διπλότυπων

Εάν τα δεδομένα περιέχουν διπλότυπα, ο τύπος μπορεί να τροποποιηθεί όπως φαίνεται παρακάτω για να εκχωρήσει μια διαδοχική κατάταξη σε τιμές που εμφανίζονται περισσότερες από μία φορές:

 
+( COUNT (data)* ISTEXT (B7))

Αυτή η έκδοση προσαρμόζει τη λογική της αρχικής συνάρτησης COUNTIF και προσθέτει ένα άλλο COUNTIF με μια αναπτυσσόμενη αναφορά σε πρόσθετα διπλότυπα.

Εμφάνιση ταξινομημένων τιμών

Για να ανακτήσετε και να εμφανίσετε τιμές ταξινομημένες με αλφαβητική σειρά χρησιμοποιώντας την υπολογισμένη τιμή κατάταξης, το Ε5 περιέχει τα ακόλουθα Τύπος INDEX και MATCH :

 
= COUNTIF (data,'<'&B5)+( COUNT (data)* ISTEXT (B5))+ COUNTIF ($B:B5,B5)

όπου 'δεδομένα' είναι το ονομασμένο εύρος Β5: Β13, και «κατάταξη» είναι το ονομαζόμενο εύρος C5: C13.

Για περισσότερες πληροφορίες σχετικά με τον τρόπο λειτουργίας αυτού του τύπου, δείτε το παράδειγμα εδώ Το

Αντιμετώπιση κενών

Τα κενά κελιά θα δημιουργήσουν μια τάξη μηδέν. Αν υποθέσουμε ότι θέλετε να αγνοήσετε τα κενά κελιά, αυτό λειτουργεί μια χαρά επειδή ο παραπάνω τύπος INDEX και MATCH ξεκινά από το 1. Ωστόσο, θα δείτε σφάλματα #N/A στο τέλος των ταξινομημένων τιμών, ένα για κάθε κενό κελί. Ένας εύκολος τρόπος χειρισμού αυτού είναι να τυλίξετε τον τύπο INDEX και MATCH στο IFERROR ως εξής:

 
= INDEX (data, MATCH ( ROWS ($E:E5),rank,0))
Συγγραφέας Dave Bruns


^