Δυναμικές Ταξινομήσεις

Συναρτήσεις φύλου για αρίθμηση αλφαβητικής ταξινόμησης.
Στη στήλη Α και στα κελιά Α1 έως Α100 έχετε ένα κατάλογο ονομάτων (αλφαριθμητικών) που δεν είναι αλφαβητικά ταξινομημένος. Μπορείτε σε μια διπλανή στήλη, με συναρτήσεις φύλλου, να δημιουργήσετε μια αρίθμηση ώστε σε κάθε αλφαριθμητικό να αντιστοιχεί ο αριθμός της σειράς που θα είχε σε μια ταξινόμηση φθίνουσα ή αύξουσα. Ας δούμε τις συναρτήσεις:
=1+SUMPRODUCT(--(A1>$A$1:$A$100))
 Αποδίδει την σειρά αύξουσας αλφαβητικής ταξινόμησης. Τα διπλότυπα αποδίδονται με τον ίδιο αριθμό. Μπορείτε να αρχίσετε την αρίθμηση, από όποιο αριθμό θέλετε, αν αλλάξετε το αρχικό 1 της συνάρτησης.
=1+SUMPRODUCT(--(A1<$A$1:$A$100))
          Το ίδιο, αλλά για φθίνουσα ταξινόμηση.
          

 =SUMPRODUCT(--(A1>$A$1:$A$100))+SUMPRODUCT(--(A1=$A$1:A1))
Αποδίδει την σειρά αύξουσας αλφαβητικής ταξινόμησης. Τα διπλότυπα αποδίδονται με διαδοχικούς αριθμούς.
 =SUMPRODUCT(--(A1<$A$1:$A$100))+SUMPRODUCT(--(A1=$A$1:A1))
 Το ίδιο, αλλά για φθίνουσα ταξινόμηση.
 =SUMPRODUCT(--(A1>=$A$1:$A$100))
 Αποδίδει την σειρά αύξουσας αλφαβητικής ταξινόμησης. Τα διπλότυπα αποδίδονται με τον ίδιο αριθμό όχι όμως τον αύξοντα της πρώτης εμφάνισης, αλλά τον αύξοντα της τελευταίας.
Οι συναρτήσεις αυτές είναι για στατικά δεδομένα στην περιοχή Α1:Α100.
Αν τα δεδομένα σας είναι δυναμικά και συνεχώς προσθέτετε νέες εγγραφές στην στήλη Α τότε τροποποιήστε τις συναρτήσεις ώστε να συμπεριλαμβάνουν τις νέες εγγραφές: αντικαταστήστε το όρισμα $A$1:$A$100 με το:   OFFSET($A$1;0;0;COUNTA(A:A);1)
Ένα πιο ρεαλιστικό παράδειγμα:
Τα δεδομένα σας είναι στη στήλη Α, με πρώτη εγγραφή στο κελί Α2 και τις επόμενες να ακολουθούν, στη δε στήλη προσθέτονται συνεχώς νέα στοιχεία. Θέλετε μια αρίθμηση αύξουσας ταξινόμησης με τα διπλότυπα να αποδίδονται με διαδοχικούς αριθμούς. Στο κελί D2 εισάγετε την συνάρτηση:
=IF(ISBLANK(A2);"";SUMPRODUCT(--(A2>OFFSET($A$2;0;0;COUNTA($A$2:$A$65000);1)))+SUMPRODUCT(--(A2=$A$2:A2)))
και την αντιγράφετε αρκετά κάτω στα κελιά της στήλης D. Τώρα κάθε φορά που εισάγετε μια νέα εγγραφή στη στήλη Α, όλοι οι αριθμοί της στήλης D αναπροσαρμόζονται και οι νέα εγγραφή αποκτά τον αριθμό ταξινόμησης της. Δέστε όλα τα παραπάνω στο excel βιβλίο:  SortFunctions
Δυναμική ταξινόμηση με συναρτήσεις.
Οι παραπάνω συναρτήσεις μπορούν να σας βοηθήσουν να ταξινομήσετε τα δεδομένα μιας στήλης δυναμικά.
Έχετε αλφαριθμητικά δεδομένα στη στήλη Α τα οποία δεν είναι ταξινομημένα και συνεχώς προσθέτετε νέες εγγραφές. Θέλετε σε μια άλλη στήλη (ας πούμε στην F) οι ίδιες οι εγγραφές της στήλης Α να εμφανίζονται ταξινομημένες και καθώς συνεχώς προσθέτετε νέες εγγραφές ή τροποποιείτε παλιές εγγραφές στην Α , η F αυτόματα να αναδιατάσσεται και να αναταξινομείται.
 

Στη στήλη Ε και στο κελί Ε2 (τα αρχικά δεδομένα αρχίζουν από το κελί Α2) εισάγετε τη συνάρτηση:

=IF(ISBLANK(A2);"";SUMPRODUCT(--(A2>OFFSET($A$2;0;0;COUNTA($A$2:$A$65000);1)))+SUMPRODUCT(--(A2=$A$2:A2)))
και την αντιγράφετε αρκετά κάτω στην Ε. Η συνάρτηση αυτή αποδίδει την σειρά αλφαβητικής ταξινόμησης των κελιών της Α, με τα διπλότυπα να αποδίδονται με διαδοχικούς αριθμούς. (βλέπε και πιο πάνω).
Στο κελί F2 εισάγετε την συνάρτηση:
=IF(ISBLANK(A2);"";VLOOKUP(ROW()-ROW($F$1);CHOOSE({1;2};OFFSET($E$2;0;0;COUNTA($E$2:$E$65000);1);OFFSET($A$2;0;0;COUNTA($A$2:$A$65000);1));2;0))
και την αντιγράφετε αρκετά κάτω στην F, (όσο κάτω αντιγράψατε και την Ε2)
Όλα είναι έτοιμα. Οι εγγραφές της στήλη Α είναι τώρα ταξινομημένες στην στήλη F, κάθε αλλαγή στα δεδομένα της Α θα απεικονίζεται άμεσα στη F και κάθε νέα εγγραφή στην Α θα βρίσκει την σωστή ταξινομημένη θέση της στην F.
Αν θέλετε φθίνουσα ταξινόμηση, δεν έχετε παρά να αλλάξετε στην τύπο της στήλης Ε, το σύμβολο του «μεγαλύτερου» (>) με το σύμβολο του «μικρότερου» (<). Τίποτε άλλο!  Η μέθοδος αυτή ταξινομεί κείμενο, αριθμούς, ημερομηνίες, λογικές τιμές, και τιμές που επιστρέφουν από συναρτήσεις˙ αποτυγχάνει μόνο σε σφάλματα.
Μπορείτε, ακόμα, να αντικαταστήσετε τα ορίσματα OFFSET(…..) με καθορισμένα ονόματα και οι 2 παραπάνω τύποι να απλοποιηθούν αρκετά. Καθορίστε τα ονόματα:
 σειρα =OFFSET($E$2;0;0;COUNTA($E$2:$E$65000);1)
χωρες =OFFSET($A$2;0;0;COUNTA($A$2:$A$65000);1)
και οι συναρτήσεις των κελιών Ε2 και F2 θα γίνουν αντίστοιχα:
=IF(ISBLANK(A2);"";SUMPRODUCT(--(A2>χωρες))+SUMPRODUCT(--(A2=$A$2:A2)))   

=IF(ISBLANK(A2);"";VLOOKUP(ROW()-ROW($F$1);CHOOSE({1;2};σειρα;χωρες);2;0))
Δέστε παραδείγματα στο excel βιβλίο:  SortFunctions
Δυναμική ταξινόμηση μιας στήλης με βάση τα ποσοτικά δεδομένα μιας άλλης.
Στα κελιά Α1: Α100 έχετε μια λίστα ονομάτων (ας πούμε σπουδαστών) και στα κελιά Β1:Β100 την βαθμολογία τους.  Θέλετε σε μια νέα στήλη να δημιουργήσετε μια δυναμική ταξινόμηση των σπουδαστών, ώστε οι αλλαγές στην βαθμολογία ή οι προσθαφαιρέσεις στον κατάλογο των σπουδαστών να αντικατοπτρίζεται άμεσα στην ταξινόμηση. Το πρόβλημα αυτό αν δεν υπάρχουν ίδιες βαθμολογίες (διπλότυπα) στη στήλη Β είναι ένα συνηθισμένο πρόβλημα «αριστερού» vlookup το οποίο μπορεί να αντιμετωπιστεί:
 =INDEX($A$1:$A$100;MATCH(LARGE($B$1:$B$100;C1);$B$1:$B$100;0)) 
ή =VLOOKUP(LARGE($B$1:$B$100;C1);CHOOSE({1;2};$B$1:$B$100;$A$1:$A$100);2;0)
όπου στα κελιά C1:C100 έχετε εισάγει μια συνεχή αρίθμηση 1,2,3,4,…100.  Αν μάλιστα η βαθμολογία είναι αριστερά της στήλης των ονομάτων, είναι ένα απλό πρόβλημα  vlookup.
Στην πράξη όμως είναι απίθανο στην στήλη των ποσοτικών δεδομένων (βαθμών) να μην υπάρχουν διπλότυπα και τότε η προηγούμενοι τύποι θα αποτύχουν. (Για κάθε  ομάδα διπλότυπων θα επιστρέψει το ίδιο όνομα).
Το πρόβλημα μπορεί να λυθεί αν πρόσκαιρα αλλοιώσετε τα ποσοτικά δεδομένα της στήλης Β, προσθέτοντας ή αφαιρώντας μια μικρή μεταβλητή ποσότητα η οποία θα εξασφαλίσει ότι τα διπλότυπα θα διαφοροποιηθούν και παράλληλα δεν θα δημιουργήσετε με την πρόσθεση η την αφαίρεση νέα διπλότυπα.
Ας το δούμε στην πράξη:

Στις στήλες Α και Β έχετε τα δεδομένα σας, ονόματα και βαθμοί, αντίστοιχα.
Στο κελί D2 εισάγετε τη συνάρτηση:
=IF(ISBLANK(A2);"";B2+ROW()/10^7)
Ο αριθμός του κελιού Β2 αυξήθηκε κατά την ποσότητα  ROW()/10^7)=2/10000000=   =0,0000002 . Όταν θα αντιγράψετε την συνάρτηση πιο κάτω στα κελιά της D θα έχετε μεταβολές: για το D3= 0,0000003, για το D4 =0,0000004 κλπ για το κελί D5000= 0,0005000 και για το D50000= 0,0050000. Οι δεκαδικοί αυτοί λοιπόν, ακόμα και αν τα δεδομένα σας φτάνουν τις 50.000, είναι διαφορετικοί για κάθε κελί και παραμένουν πέρα από το τρίτο δεκαδικό. Άρα διαφοροποιούν  όλα τα ποσοτικά δεδομένα της στήλης Β και δεν δημιουργούν νέα διπλότυπα αφού τα αρχικά δεδομένα είναι αριθμοί με 2 δεκαδικά. Αν τα αρχικά δεδομένα σας έχουν πιο πολλά δεκαδικά ή είναι εκατοντάδες χιλιάδες, απλά αυξήστε στον τύπο τον εκθέτη του 10.
Στη συνέχεια εισάγετε 2 καθορισμένα ονόματα:
ΤροποποιημενοιΒαθμοι=OFFSET($D$2;0;0;COUNTA($D$2:$D$65000);1)
Σπουδαστες=OFFSET($A$2;0;0;COUNTA($A$2:$A$65000);1)
(Τα ονόματα δεν είναι απαραίτητα, αλλά θα κάνουν πιο κατανοητούς και πιο απλούς τους τύπους που θα ακολουθήσουν)
Στο κελί Ε2 εισάγετε τον τύπο:
=IF(ISBLANK(A2);"";LARGE(ΤροποποιημενοιΒαθμοι;ROW()-ROW($E$1)))
Η συνάρτηση αυτή ταξινομεί τις τροποποιημένες βαθμολογίες σε φθίνουσα ταξινόμηση. Το όρισμα ROW()-ROW($E$1) αποδίδει στη θέση αυτή 1 και πιο κάτω θα αποδώσει 2, 3, 4,…. δημιουργεί δηλαδή μια συνεχή αρίθμηση. (Σημειώνω για όσους τυχόν ανησυχούν ότι η ROW() θα είναι ευμετάβλητη σε εισαγωγές νέων γραμμών,  ότι στη μορφή ROW()-ROW($E$1) δεν είναι! Δοκιμάστε το!)
Στο κελί G2 εισάγετε τον τύπο:
=IF(ISBLANK(A2);"";VLOOKUP(E2;CHOOSE({1;2};ΤροποποιημενοιΒαθμοι;Σπουδαστες);2;0))
Ο τύπος αυτός κάνει την τελική ταξινόμηση. Είναι ένα «αριστερό» VLOOKUP με την CHOOSE να αναλαμβάνει την δημιουργία του table_array. (**)
Στο κελί Η2 εισάγετε την συνάρτηση:
=IF(ISBLANK(A2);"";ROUND(E2;2))
η οποία επαναφέρει τους βαθμούς στην αρχική τους μορφή.
Τέλος αντιγράψτε της συναρτήσεις των κελιών D2, E2, G2, H2 αρκετά κάτω, όσο εκτιμάτε ότι θα σας χρειαστεί. Είστε έτοιμοι!  Καθώς προσθέτετε ή τροποποιείτε  στοιχεία στις στήλες Α και Β, αυτόματα αναπροσαρμόζονται οι G και Η  (και οι D, E). Προσθέστε  δεδομένα στις Α και Β ή αλλάξτε κάποιους βαθμούς και θα το δείτε.
Για μία αύξουσα ταξινόμηση απλά αλλάξτε στη στήλη Ε την συνάρτηση LARGE με τη SMALL.
Δέστε παραδείγματα στο excel βιβλίο: SortFunctions
(**) Για την ικανότητα της συνάρτησης CHOOSE  να επιστρέφει πίνακα και να «απογειώνει» κυριολεκτικά την  VLOOKUP δέστε προηγούμενο σημείωμα: Ένας πρωτότυπος τόπος χρήσης της συνάρτησης CHOOSE.
Και μια αναλυτική παρουσίαση στο PDF αρχείο: CHOOSE and VLOOKUP in excel
This entry was posted in excel, Συναρτήσεις φύλλου and tagged , , . Bookmark the permalink.

3 Responses to Δυναμικές Ταξινομήσεις

  1. Ο/Η JGPat2013 λέει:

    Καλημέρα, Καλή Χρονιά και Χρόνια Πολλά και Καλά!
    Έχω ένα πρόβλημα και ζητώ τη βοήθειά σας. Έχω διάφορες στήλες σε ένα φύλλο στις οποίες προσθέτω συνεχώς νέα στοιχειά και τις ταξινομώ. Όταν έρχεται η ώρα να δουλέψω με το φύλλο δεν θυμάμαι αν έχω κάνει την ταξινόμηση, ούτε μπορώ να το δω με το μάτι γιατί οι στήλες είναι μεγάλες. Για να βεβαιωθώ ξανακάνω τις ταξινομήσεις από την αρχή. Υπάρχει τρόπος να ξέρω αμέσως ότι μια στήλη είναι ταξινομημένη.

  2. Ο/Η vioannis λέει:

    Πάρα πολύ ωραίο ερώτημα! Ναι, μπορεί μια συνάρτηση (συνάρτηση φύλλου) να παρακολουθεί αν μία στήλη είναι ταξινομημένη. Επειδή το ερώτημα πιθανά να ενδιαφέρει και άλλους φίλους του ιστολογίου, αξίζει τον κόπο να γίνει μια χωριστή ανάρτηση. Ελπίζω πολύ σύντομα να έχεις την απάντησή σου.
    Ευχαριστώ για τις ευχές σου.

  3. Παράθεμα: Είναι η στήλη ταξινομημένη; | Excel Λύσεις

Τα σχόλια είναι απενεργοποιημένα.