Η ενσωματωμένη ταξινόμηση δεδομένων του Excel είναι εκπληκτική, αλλά δεν είναι δυναμική. Εάν ταξινομήσετε δεδομένα και στη συνέχεια προσθέσετε δεδομένα σε αυτά, θα πρέπει να τα ταξινομήσετε ξανά.
Ταξινόμηση δεδομένων με αλφαβητική σειρά
Σε αυτήν την ανάρτηση, θα σας δείξω διάφορους τρόπους για να ταξινομήσετε δεδομένα με αλφαβητική σειρά χρησιμοποιώντας τύπους. Αυτό σημαίνει ότι μπορείτε να προσθέσετε δεδομένα και θα τα ταξινομήσει αυτόματα για εσάς.
Όταν τα δεδομένα είναι όλα Κείμενο χωρίς Χωρίς Διπλότυπα
Ας υποθέσουμε ότι έχετε δεδομένα όπως φαίνεται παρακάτω:
Σε αυτό το παράδειγμα, όλα τα δεδομένα είναι σε μορφή κειμένου (χωρίς αριθμούς, κενά ή διπλότυπα). Για να το ταξινομήσω, θα χρησιμοποιήσω μια στήλη βοηθού. Στη στήλη δίπλα στα δεδομένα, χρησιμοποιήστε τον ακόλουθο τύπο COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Αυτός ο τύπος συγκρίνει μια τιμή κειμένου με όλες τις άλλες τιμές κειμένου και επιστρέφει τη σχετική κατάταξή της. Για παράδειγμα, στο κελί Β2, επιστρέφει 8, καθώς υπάρχουν 8 τιμές κειμένου που είναι χαμηλότερες ή ίσες με το κείμενο «ΗΠΑ» (αλφαβητική σειρά).
Τώρα για να ταξινομήσετε τις τιμές, χρησιμοποιήστε τον ακόλουθο συνδυασμό συναρτήσεων INDEX, MATCH και ROWS:
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Αυτός ο τύπος απλώς εξάγει τα ονόματα με αλφαβητική σειρά. Στο πρώτο κελί (C2), αναζητά το όνομα της χώρας που έχει τον μικρότερο αριθμό (η Αυστραλία έχει 1). Στο δεύτερο κελί, επιστρέφει τον Καναδά (που έχει τον αριθμό 2) και ούτω καθεξής…
Αλλεργικός στις στήλες βοηθών;;
Εδώ είναι ένας τύπος που θα κάνει το ίδιο χωρίς τη στήλη βοηθού.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Αυτός είναι ένας τύπος πίνακα, οπότε χρησιμοποιήστε τον Control + Shift + Enter αντί για Enter.
Θα σας αφήσω να απο-κωδικοποιήσετε.
Δοκιμάστε το μόνοι σας … Λήψη παραδείγματος αρχείου
Αυτός ο τύπος λειτουργεί καλά εάν έχετε κείμενο ή αλφαριθμητικές τιμές.
Αλλά αποτυγχάνει παταγωδώς εάν:
- Έχετε διπλότυπα στα δεδομένα (δοκιμάστε να βάλετε ΗΠΑ δύο φορές).
- Υπάρχουν κενά στα δεδομένα.
- Έχετε ένα μείγμα αριθμών και κειμένου (δοκιμάστε να βάλετε το 123 σε ένα από τα κελιά).
Όταν τα δεδομένα είναι ένα μείγμα αριθμών, κειμένου, διπλότυπων και κενών
Τώρα αυτό είναι λίγο δύσκολο. Θα χρησιμοποιήσω 4 στήλες βοηθού για να σας δείξω πώς λειτουργεί (και στη συνέχεια θα σας δώσω έναν τεράστιο τύπο που θα το κάνει χωρίς τις στήλες βοηθού). Ας υποθέσουμε ότι έχετε δεδομένα όπως φαίνεται παρακάτω:
Μπορείτε να δείτε ότι υπάρχουν διπλές τιμές, κενές και αριθμοί. Έτσι, θα χρησιμοποιήσω στήλες βοηθού για την αντιμετώπιση καθενός από αυτά τα ζητήματα.
Στήλη βοηθού 1
Εισαγάγετε τον ακόλουθο τύπο COUNTIF στη στήλη 1 βοηθού
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Αυτός ο τύπος κάνει τα εξής:
- Επιστρέφει 0 για κενά.
- Στην περίπτωση των διπλότυπων, επιστρέφει τον ίδιο αριθμό.
- Το κείμενο και οι αριθμοί υποβάλλονται σε παράλληλη επεξεργασία και αυτός ο τύπος επιστρέφει τον ίδιο αριθμό για κείμενο και αριθμό (για παράδειγμα 123 και η Ινδία παίρνουν και οι δύο 1).
Στήλη βοηθού 2
Εισαγάγετε την ακόλουθη συνάρτηση IS στη στήλη 2 βοηθού:
=-ΑΡΙΘΜΟΣ (Α2)
Στήλη βοηθού 3
Εισαγάγετε τον ακόλουθο τύπο στη στήλη 3 του βοηθού:
=-ISBLANK (A2)
Στήλη βοηθού 4
Εισαγάγετε τον ακόλουθο τύπο στη στήλη 4 βοηθού
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Η ιδέα για αυτόν τον τύπο είναι ο διαχωρισμός κενών, αριθμών και τιμών κειμένου.
- Εάν το κελί είναι κενό, επιστρέφει την τιμή στο κελί B2 (που θα ήταν πάντα 0) και προσθέτει την τιμή στο κελί D10. Με λίγα λόγια, θα επιστρέψει τον συνολικό αριθμό των κενών κελιών στα δεδομένα
- Εάν το κελί είναι μια αριθμητική τιμή, θα επιστρέψει τη συγκριτική κατάταξη και θα προσθέσει τον συνολικό αριθμό κενών. Για παράδειγμα, για το 123 επιστρέφει 2 (1 είναι ο βαθμός 123 στα δεδομένα και υπάρχει 1 κενό κελί)
- Εάν είναι κείμενο, επιστρέφει τη συγκριτική κατάταξη και προσθέτει τον συνολικό αριθμό αριθμητικών τιμών και κενών. Για παράδειγμα, για την Ινδία, προσθέτει τη συγκριτική κατάταξη του κειμένου σε κείμενο (που είναι 1) και προσθέτει τον αριθμό των κενών κελιών και τον αριθμό των αριθμητικών τιμών.
Τελικό αποτέλεσμα - Ταξινόμηση δεδομένων
Τώρα θα χρησιμοποιήσουμε αυτές τις στήλες βοηθού για να πάρουμε την ταξινομημένη λίστα. Εδώ είναι ο τύπος:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (ΜΙΚΡΟ ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Αυτή η μέθοδος ταξινόμησης γίνεται πλέον ανόητη. Σας έχω δείξει τη μέθοδο για 8 αντικείμενα, αλλά μπορείτε να την επεκτείνετε σε όσα στοιχεία θέλετε.
Δοκιμάστε το μόνοι σας … Λήψη παραδείγματος αρχείου
Ένας τύπος για να τα ταξινομήσετε όλα (χωρίς στήλες βοηθού)
Εάν μπορείτε να χειριστείτε ακραίες φόρμουλες, εδώ είναι ένας τύπος all-in-one που θα ταξινομεί τα δεδομένα με αλφαβητική σειρά (χωρίς στήλη βοηθού).
Εδώ είναι ο τύπος:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (ΜΙΚΡΟ (ΟΧΙ ($ A $ 2: $ A $ 9 = "))*IF (ΑΡΙΘΜΟΣ ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9))), ROWS ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9)))), ΟΧΙ ($ A $ 2: $ A $ 9 = "")*ΑΝ (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Εισαγάγετε αυτόν τον τύπο σε ένα κελί και σύρετέ τον προς τα κάτω για να λάβετε τη λίστα ταξινόμησης. Επίσης, δεδομένου ότι πρόκειται για έναν τύπο πίνακα, χρησιμοποιήστε Control + Shift + Enter αντί για Enter.
Αυτός ο τύπος έχει πραγματική χρησιμότητα. Τι νομίζετε; Θα ήθελα πολύ να μάθω από εσάς. Αφήστε τα ίχνη σας στην ενότητα σχολίων!
