Συνάρτηση κατάταξης στο Excel με παραδείγματα τύπου

Το σεμινάριο εξηγεί τις ιδιαιτερότητες του λειτουργία Κατάταξη στο Excel και δείχνει πώς να ταξινομήσετε στο Excel με βάση διάφορα κριτήρια, να ταξινομήσετε δεδομένα ανά ομάδα, να υπολογίσετε την κατάταξη εκατοστημόριου και πολλά άλλα. Όταν χρειάζεστε να προσδιορίσετε τη σχετική θέση ενός αριθμού σε μια λίστα αριθμών, ο ευκολότερος τρόπος είναι να τακτοποιήσετε τους αριθμούς σε αύξουσα ή φθίνουσα σειρά. Εάν για κάποιο λόγο δεν είναι δυνατή η ταξινόμηση, ένας τύπος εύρους στο Excel είναι το τέλειο εργαλείο για να ολοκληρώσετε τη δουλειά.

Λειτουργία Εύρους Excel

La Λειτουργία RANK στο Excel επιστρέφει τη σειρά (ή την κατάταξη) μιας αριθμητικής τιμής σε σύγκριση με άλλες τιμές στην ίδια λίστα. Με άλλα λόγια, σας λέει ποια τιμή είναι η υψηλότερη, η δεύτερη υψηλότερη, μεταξύ άλλων.

Ίσως σας ενδιαφέρει: Λειτουργία Excel CHOOSE. Τύποι και Παραδείγματα

Σε μια ταξινομημένη λίστα, η κατάταξη ενός συγκεκριμένου αριθμού θα ήταν η θέση. Η συνάρτηση Εύρος στο Excel μπορεί να καθορίσει το εύρος που ξεκινά με τη μεγαλύτερη τιμή (σαν ταξινομημένη με φθίνουσα σειρά) ή τη μικρότερη τιμή (σαν ταξινομημένη με αύξουσα σειρά). Ο Σύνταξη συνάρτησης RANK του Excel είναι το εξής:

RANK (αριθμός, αναφορά, [παραγγελία])

Πού:

• Αριθμός (απαιτείται): την τιμή της οποίας την κατάταξη θα θέλατε να βρείτε.
• Αναφορά (απαιτείται): μια λίστα με αριθμητικές τιμές με τις οποίες γίνεται η ταξινόμηση. Μπορεί να παρέχεται ως πίνακας αριθμών ή ως αναφορά στη λίστα αριθμών.
• Παραγγελία (προαιρετικό): ένας αριθμός που καθορίζει τον τρόπο ταξινόμησης των τιμών:
  • Εάν το 0 ή παραλειφθεί, οι τιμές ταξινομούνται με φθίνουσα σειρά, δηλαδή από την υψηλότερη στη χαμηλότερη.
  • Εάν είναι 1 ή οποιαδήποτε άλλη μη μηδενική τιμή, οι τιμές ταξινομούνται με αύξουσα σειρά, δηλαδή από τη χαμηλότερη στην υψηλότερη.

Συνάρτηση RANK.EQ στο Excel

ΚΑΤΑΤΑΞΗ.EQ Είναι μια βελτιωμένη έκδοση της συνάρτησης RANK, που εισήχθη στο Excel 2010. Έχει την ίδια σύνταξη με το RANK και λειτουργεί με την ίδια λογική: εάν πολλαπλές τιμές κατατάσσονται εξίσου, η υψηλότερη κατάταξη εκχωρείται σε όλες αυτές τις τιμές. (EQ σημαίνει «ίσος»).

RANK.EQ (αριθμός, αναφορά, [παραγγελία])

Στο Excel 2007 και παλαιότερες εκδόσεις, θα πρέπει πάντα να χρησιμοποιείτε τη συνάρτηση RANK. Στο Excel 2010, στο Excel 2013 και στο Excel 2016, μπορείτε να επιλέξετε RANK ή RANK.EQ. Ωστόσο, θα ήταν σκόπιμο να χρησιμοποιήσετε το RANK.EQ επειδή το RANK μπορεί να ανασταλεί ανά πάσα στιγμή.

Λειτουργία Excel RANGE.AVG

Το RANK.AVG είναι μια άλλη λειτουργία για βρείτε το εύρος στο excel το οποίο είναι διαθέσιμο μόνο σε Excel 2010, Excel 2013, Excel 2016 και νεότερες εκδόσεις. Έχει την ίδια σύνταξη με τις άλλες δύο συναρτήσεις:

RANK.AVG (αριθμός, αναφορά, [παραγγελία])

Η διαφορά είναι ότι εάν περισσότεροι από ένας αριθμοί έχουν την ίδια κατάταξη, επιστρέφεται η μέση κατάταξη (AVG σημαίνει "μέσος όρος").

4 πράγματα που πρέπει να γνωρίζετε για το RANK στο Excel

Ακολουθούν ορισμένα πράγματα που πρέπει να έχετε υπόψη σας σχετικά με τη συνάρτηση Rank στο Excel:

• Οποιοσδήποτε τύπος εύρους στο Excel λειτουργεί μόνο για αριθμητικές τιμές: θετικούς και αρνητικούς αριθμούς, μηδενικά, τιμές ημερομηνίας και ώρας. Οι μη αριθμητικές τιμές στο όρισμα ref αγνοούνται.
• Όλες οι συναρτήσεις RANGE επιστρέφουν το ίδιο εύρος για διπλότυπες τιμές και παραλείπουν περαιτέρω ταξινόμηση, όπως φαίνεται στο ακόλουθο παράδειγμα.
• Στο Excel 2010 και μεταγενέστερα, η συνάρτηση RANK έχει αντικατασταθεί με RANK.EQ και RANK.AVG. Για συμβατότητα προς τα πίσω, το RANK εξακολουθεί να λειτουργεί σε όλες τις εκδόσεις του Excel, αλλά ενδέχεται να μην είναι διαθέσιμο στο μέλλον.
• Εάν ο αριθμός δεν βρεθεί μέσα στην αναφορά, οποιαδήποτε συνάρτηση εύρους του Excel θα επέστρεφε το σφάλμα #N/A.

Βασικός τύπος εύρους Excel (από το υψηλότερο έως το χαμηλότερο)

Για να μάθετε περισσότερα σχετικά με την κατάταξη με τη συνάρτηση Rank στο Excel, ρίξτε μια ματιά σε αυτό το στιγμιότυπο οθόνης:

Και οι τρεις τύποι ταξινομούν τους αριθμούς στη στήλη Β με φθίνουσα σειρά (το όρισμα σειράς παραλείπεται):

Σε όλες τις εκδόσεις του Excel 2003 – 2016:

=RANK($B2,$B$2:$B$7)

Στο Excel 2010 – 2016:

=RANK.EQ($B2,$B$2:$B$7)

=RANK.AVG($B2,$B$2:$B$7)

Η διαφορά έγκειται στον τρόπο με τον οποίο αυτοί οι τύποι επεξεργάζονται διπλές τιμές. Όπως μπορείτε να δείτε, η ίδια βαθμολογία εμφανίζεται δύο φορές, στα κελιά B5 και B6, γεγονός που επηρεάζει την επόμενη ταξινόμηση:

• Οι τύποι RANK και RANK.EQ δίνουν κατάταξη 2 και στις δύο διπλές βαθμολογίες. Η επόμενη υψηλότερη βαθμολογία (Daniela) βρίσκεται στην τέταρτη θέση. Σε κανέναν δεν δίνεται βαθμός 3.
• Ο τύπος RANK.AVG εκχωρεί διαφορετική κατάταξη σε κάθε διπλότυπο πίσω από τα παρασκήνια (2 και 3 σε αυτό το παράδειγμα) και επιστρέφει τον μέσο όρο αυτών των βαθμών (2.5). Και πάλι, η τρίτη βαθμίδα δεν αποδίδεται σε κανέναν.

Πώς να χρησιμοποιήσετε το RANK στο Excel – παραδείγματα τύπων

Ο δρόμος προς την αριστεία, λένε, είναι στρωμένος με εξάσκηση. Έτσι, για να μάθετε καλύτερα πώς να χρησιμοποιείτε τη συνάρτηση RANK στο Excel, μόνη της ή σε συνδυασμό με άλλες συναρτήσεις, ας δούμε λύσεις για ορισμένες πραγματικές εργασίες.

Πώς να ταξινομήσετε στο Excel από το μικρότερο στο μεγαλύτερο

Όπως φαίνεται στο παραπάνω παράδειγμα, για να ταξινομήσετε αριθμούς από τον μεγαλύτερο στον μικρότερο, χρησιμοποιήστε έναν από τους τύπους εύρους του Excel με το όρισμα σειράς να έχει οριστεί σε 0 ή να παραλείπεται (προεπιλογή).

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

= RANK (B2, $ B $ 2: $ B $ 7,1)

=RANK.EQ(B2,$B$2:$B$7,1)

Προσέξτε ότι κλειδώνουμε το εύρος στο όρισμα σχ χρησιμοποιώντας απόλυτες αναφορές κελιών, έτσι ώστε να μην αλλάζει όταν αντιγράφουμε τον τύπο στη στήλη.

Ως αποτέλεσμα, η χαμηλότερη τιμή (γρηγορότερος χρόνος) παίρνει την πρώτη θέση και η μεγαλύτερη τιμή (ο πιο αργός χρόνος) παίρνει τη χαμηλότερη κατάταξη 6. Ίσες φορές (Β2 και Β7) λαμβάνουν την ίδια κατάταξη.

Πώς να ταξινομήσετε μοναδικά δεδομένα στο Excel

Όπως σημειώθηκε παραπάνω, όλες οι συναρτήσεις εύρους του Excel επιστρέφουν το ίδιο εύρος για στοιχεία ίσης αξίας. Αν δεν είναι αυτό που θέλετε, χρησιμοποιήστε έναν από τους παρακάτω τύπους για να λύσετε καταστάσεις ισοπαλίας και δώστε σε κάθε αριθμό μια μοναδική κατάταξη.

Μοναδική κατάταξη από την υψηλότερη προς τη χαμηλότερη

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

=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1

Μοναδική κατάταξη από τη χαμηλότερη στην υψηλότερη

Για να ταξινομήσετε τα αποτελέσματα των αγώνων 100 μέτρων με αύξουσα σειρά χωρίς αντίγραφα, χρησιμοποιήστε αυτόν τον τύπο:

=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1

Πώς λειτουργούν αυτοί οι τύποι

Όπως έχετε παρατηρήσει, Η μόνη διαφορά μεταξύ των δύο τύπων είναι το όρισμα σειράς της συνάρτησης RANK.EQ: παραλείπεται για ταξινόμηση φθίνουσας τιμής, 1 για αύξουσα ταξινόμηση. Και στους δύο τύπους, είναι η συνάρτηση COUNTIF με την έξυπνη χρήση σχετικών και απόλυτων αναφορών κελιών που κάνει το κόλπο.

Εν ολίγοις, χρήση COUNTIF για να μάθετε πόσες εμφανίσεις του αριθμού που ταξινομείται υπάρχουν στα προηγούμενα κελιά, συμπεριλαμβανομένου του αριθμητικού κελιού. Στην επάνω σειρά όπου εισάγετε τον τύπο, η περιοχή αποτελείται από ένα μόνο κελί ($B$2:B2). Επειδή όμως κλειδώνει μόνο την πρώτη αναφορά ($B$2), η τελευταία σχετική αναφορά (B2) αλλάζει ανάλογα με τη σειρά όπου αντιγράφεται ο τύπος.

Επομένως, για τη σειρά 7, το εύρος επεκτείνεται σε $B$2:B7 και η τιμή στο B7 συγκρίνεται με καθένα από τα προηγούμενα κελιά. Συνεπώς, για όλες τις πρώτες εμφανίσεις, το COUNTIF επιστρέφει 1. και αφαιρέστε 1 από το τέλος του τύπου για να επαναφέρετε το αρχικό εύρος.

Για δεύτερες εμφανίσεις, το COUNTIF επιστρέφει 2. Αφαιρώντας 1, αυξάνετε το εύρος κατά 1 σημείο, αποφεύγοντας έτσι τα διπλότυπα. Εάν υπάρχουν 3 εμφανίσεις της ίδιας τιμής, το COUNTIF() – 1 θα προσθέσει 2 στην ταξινόμηση σας και ούτω καθεξής.

Λύση για να σπάσετε τους δεσμούς του Excel RANK

Μια άλλη μορφή ταξινόμηση αριθμών στο excel Ο μόνος τρόπος είναι να προσθέσετε δύο συναρτήσεις COUNTIF:

• Η πρώτη συνάρτηση καθορίζει πόσες τιμές είναι μεγαλύτερες ή μικρότερες από τον αριθμό που πρόκειται να ταξινομηθεί, ανάλογα με το αν ταξινομείτε φθίνουσα ή αύξουσα, αντίστοιχα.
• Η δεύτερη συνάρτηση (με το "expanding range" $B $2:B2 όπως στο προηγούμενο παράδειγμα) παίρνετε τον αριθμό των τιμών ίσο με τον αριθμό.

Για παράδειγμα, για να ταξινομήσετε αριθμούς μόνο από το υψηλότερο στο χαμηλότερο, θα χρησιμοποιήσετε αυτόν τον τύπο:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIF($B$2:B2,B2)

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

Ταξινόμηση στο Excel με βάση πολλαπλά κριτήρια

Το προηγούμενο παράδειγμα έχει δείξει δύο λειτουργικές λύσεις για μια κατάσταση του Excel RANK tiebreaker. Ωστόσο, μπορεί να φαίνεται άδικο το γεγονός ότι ίσοι αριθμοί κατατάσσονται διαφορετικά με βάση αποκλειστικά τη θέση τους στη λίστα.

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

• Αρχικά, ταξινομήστε με μαθηματική βαθμολογία (κύριο κριτήριο).
• Όταν υπάρχει ισοπαλία, σπάστε τη με τη συνολική βαθμολογία (δευτερεύον κριτήριο)

Για να το κάνουμε αυτό, θα χρησιμοποιήσουμε έναν κανονικό τύπο RANK/RANK.EQ για να βρούμε την κατάταξη και τη συνάρτηση COUNTIF για να σπάσουμε μια ισοπαλία:

=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)

Σε σύγκριση με το προηγούμενο παράδειγμα, αυτός ο τύπος κατάταξης είναι πιο αντικειμενικός: ο Timothy κατατάσσεται στη 2η θέση επειδή η συνολική του βαθμολογία είναι υψηλότερη από αυτή της Julia:

Πώς λειτουργεί αυτή η φόρμουλα

Το τμήμα RANK του τύπου είναι προφανές και η συνάρτηση COUNTIFS κάνει τα εξής:

• Το πρώτο ζεύγος κριτήρια_εύρος/κριτήριο ($B$2:$B$7,$B2) μετράει τις εμφανίσεις της τιμής που κατατάσσετε. Σημειώστε ότι διορθώνουμε το εύρος με απόλυτες αναφορές, αλλά δεν κλειδώνουμε τη σειρά κριτηρίων ($B2) έτσι ώστε ο τύπος να ελέγχει την τιμή σε κάθε σειρά ξεχωριστά.
• Το δεύτερο ζεύγος κριτηρίου_εύρος/κριτηρίου ($C$2:$C$7, ">" & $C2) ανακαλύπτει πόσες συνολικές βαθμολογίες είναι μεγαλύτερες από τη συνολική βαθμολογία της τιμής που κατατάσσεται.

Επειδή το COUNTIFS λειτουργεί με τη λογική AND, δηλαδή μετράει μόνο κελιά που πληρούν όλες τις καθορισμένες προϋποθέσεις, επιστρέφει 0 για τον Timothy, αφού κανένας άλλος μαθητής με την ίδια βαθμολογία στα μαθηματικά δεν έχει υψηλότερη συνολική βαθμολογία. Επομένως, η κατάταξη του Timothy που επιστρέφεται από το RANK.EQ δεν αλλάζει.

Για την Τζούλια, η συνάρτηση COUNTIF επιστρέφει 1 επειδή ένας μαθητής με την ίδια βαθμολογία στα μαθηματικά έχει υψηλότερη συνολική βαθμολογία, επομένως ο αριθμός της βαθμολογίας του αυξάνεται κατά 1. Εάν ένας ακόμη μαθητής είχε την ίδια βαθμολογία μαθηματικών και χαμηλότερη συνολική βαθμολογία από τον Timothy και την Julia, η κατάταξή σας θα αυξηθεί κατά 2 , και ούτω καθεξής.

Εναλλακτικές λύσεις ταξινόμησης αριθμών με πολλαπλά κριτήρια

Αντί για τη συνάρτηση RANK ή RANK.EQ, μπορείτε να χρησιμοποιήσετε COUNTIF για να ελέγξετε τα κύρια κριτήρια και COUNTIFS ή SUMPRODUCT για να επιλύσετε ένα tiebreaker:

=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)+1

=COUNTIF($B$2:$B$7,»>»&B2)+SUMPRODUCT(–($C$2:$C$7=C2),–($B$2:$B$7>B2))+1

Το αποτέλεσμα αυτών των τύπων είναι ακριβώς το ίδιο όπως φαίνεται παραπάνω.

Πώς να υπολογίσετε την ποσοστιαία κατάταξη στο Excel

Στα στατιστικά, ένα εκατοστημόριο είναι η τιμή κάτω από την οποία πέφτει ένα ορισμένο ποσοστό τιμών σε ένα δεδομένο σύνολο δεδομένων. Για παράδειγμα, εάν το 70% των μαθητών είναι στο ή κάτω από τη βαθμολογία του τεστ σας, η εκατοστιαία βαθμολογία σας είναι 70.

Για να λάβετε την εκατοστιαία κατάταξη στο Excel, χρησιμοποιήστε τη συνάρτηση RANK ή RANK.EQ με ένα όρισμα μη μηδενικής σειράς για να ταξινομήσετε τους αριθμούς από το χαμηλότερο προς το υψηλότερο και, στη συνέχεια, να διαιρέσετε την κατάταξη με τον αριθμό των αριθμών. Έτσι, ο γενικός τύπος κατάταξης εκατοστημόνων του Excel είναι ο ακόλουθος:

RANK.EQ(topmost_cell, rank, 1) / COUNT(rank)

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

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

Για να εμφανίζονται σωστά τα αποτελέσματα, βεβαιωθείτε ότι έχετε ορίσει τη μορφή ποσοστού στα κελιά του τύπου:

Ίσως θέλετε να μάθετε: Πώς να προσθέσετε ανά κατηγορία στο Excel

Πώς να ταξινομήσετε αριθμούς σε μη γειτονικά κελιά

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

=RANK(B2,($B$2,$B$4,$B$6))

Για να αποφύγετε σφάλματα σε μη ταξινομημένα κελιά, προσαρμόστε ΤΑΞΗ σε λειτουργία ΑΠΟΦΑΣΗ, Ετσι:

=IFERROR(RANK(B2,($B$2,$B$4,$B$6)),»»)

Σημειώστε ότι σε έναν διπλότυπο αριθμό εκχωρείται επίσης ένα εύρος, αν και το κελί B5 δεν περιλαμβάνεται στον τύπο:

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

=IFERROR(RANK(B2,εύρος),»»)

Πώς να ταξινομήσετε στο Excel ανά ομάδα

Όταν εργάζεστε με καταχωρήσεις οργανωμένες σε κάποιο τύπο δομής δεδομένων, τα δεδομένα μπορεί να ανήκουν σε πολλές ομάδες και μπορεί να θέλετε να ταξινομήσετε τους αριθμούς σε κάθε ομάδα ξεχωριστά. Η συνάρτηση RANK του Excel δεν μπορεί να λύσει αυτήν την πρόκληση, επομένως ας χρησιμοποιήσουμε έναν πιο περίπλοκο τύπο SUMPRODUCT:

Ταξινόμηση ανά ομάδα με φθίνουσα σειρά:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Ταξινόμηση ανά ομάδα σε αύξουσα σειρά:

=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1

Πού:

• A2: Οι Α7 είναι ομάδες που αντιστοιχίζονται σε αριθμούς.
• C2: Οι C7 είναι αριθμοί για ταξινόμηση.

Σε αυτό το παράδειγμα, χρησιμοποιούμε τον πρώτο τύπο για να ταξινομήσουμε τους αριθμούς σε κάθε ομάδα από το μεγαλύτερο στο μικρότερο:

Πώς λειτουργεί αυτή η φόρμουλα

Βασικά, ο τύπος αξιολογεί 2 συνθήκες:

• Πρώτα, ελέγξτε την ομάδα (A2=$A$2:$A$7). Αυτό το τμήμα επιστρέφει έναν πίνακα TRUE και FALSE με βάση το αν ένα στοιχείο εύρους ανήκει στην ίδια ομάδα με το A2.
• Δεύτερον, ελέγξτε τη βαθμολογία. Για να ταξινομήσετε τις τιμές από το υψηλότερο στο χαμηλότερο (φθίνουσα σειρά), χρησιμοποιήστε τη συνθήκη (C2 < $C$2:$C$11), η οποία επιστρέφει TRUE για κελιά μεγαλύτερα ή ίσα με C2, FALSE διαφορετικά.

Δεδομένου ότι με όρους του Microsoft Excel, TRUE = 1 και FALSE = 0, πολλαπλασιάζοντας τους δύο πίνακες δίνεται ένας πίνακας 1 και 0, όπου το 1 επιστρέφεται μόνο για σειρές όπου πληρούνται και οι δύο συνθήκες. Τότε, ΑΝΤΙΠΡΟΣΩΠΟΣ Προσθέτει τα στοιχεία του πίνακα 1 και 0, επομένως επιστρέφει 0 για τον μεγαλύτερο αριθμό σε κάθε ομάδα. Και προσθέστε 1 στο αποτέλεσμα για να ξεκινήσετε την ταξινόμηση με 1.

Ο τύπος που ταξινομεί τους αριθμούς μέσα σε ομάδες από τον μικρότερο προς τον μεγαλύτερο (αύξουσα σειρά) λειτουργεί με την ίδια λογική. Η διαφορά είναι ότι SUMPRODUCT επιστρέφει 0 για τον μικρότερο αριθμό σε μια συγκεκριμένη ομάδα, καθώς κανένας αριθμός σε αυτήν την ομάδα δεν πληροί τη 2η συνθήκη (C2 > $C$2:$C$7). Και πάλι, αντικαταστήστε τη μηδενική κατάταξη με την πρώτη κατάταξη προσθέτοντας 1 στο αποτέλεσμα του τύπου.

Αντί για ΑΝΤΙΠΡΟΣΩΠΟΣμπορείτε χρησιμοποιήστε τη λειτουργία ΑΘΡΟΙΣΜΑ για να προσθέσετε τα στοιχεία του πίνακα. Αλλά αυτό θα απαιτούσε τη χρήση ενός τύπου πίνακα, συμπληρωμένο μέσω Ctrl + Shift + Enter. Για παράδειγμα:

=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

Πώς να ταξινομήσετε θετικούς και αρνητικούς αριθμούς ξεχωριστά

Εάν η λίστα των αριθμών σας περιέχει θετικές και αρνητικές τιμές, η συνάρτηση RANK στο Excel θα τους κατατάξει όλους σε ελάχιστο χρόνο. Τι θα γινόταν όμως αν θέλατε να ταξινομήσετε τους θετικούς και τους αρνητικούς αριθμούς ξεχωριστά; Με αριθμούς στα κελιά A2 έως A10, χρησιμοποιήστε έναν από τους παρακάτω τύπους για να λάβετε μια ατομική κατάταξη για τις θετικές και αρνητικές τιμές:

Ταξινόμηση θετικών αριθμών φθίνουσα:

=IF($A2>0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Ταξινομήστε τους θετικούς αριθμούς σε αύξουσα σειρά:

=IF($A2>0,COUNTIF($A$2:$A$10,»>0″)-COUNTIF($A$2:$A$10,»>»&$A2),»»)

Ταξινόμηση φθίνουσες αρνητικών αριθμών:

=IF($A2<0,COUNTIF($A$2:$A$10,»<0″)-COUNTIF($A$2:$A$10,»<«&$A2),»»)

Ταξινομήστε τους αρνητικούς αριθμούς σε αύξουσα σειρά:

=IF($A2<0,COUNTIF($A$2:$A$10,»<«&$A2)+1,»»)

Τα αποτελέσματα θα μοιάζουν με αυτό:

Πώς λειτουργούν αυτοί οι τύποι

Για να ξεκινήσουμε, ας δούμε τον τύπο που ταξινομεί τους θετικούς αριθμούς με φθίνουσα σειρά:

• Στο λογικό τεστ της συνάρτησης IF, ελέγχει εάν ο αριθμός είναι μεγαλύτερος από το μηδέν.
• Εάν ο αριθμός είναι μεγαλύτερος από 0, η συνάρτηση COUNTIF επιστρέφει τον αριθμό των τιμών υψηλότερες από τον αριθμό που ταξινομείται.

Σε αυτό το παράδειγμα, το A2 περιέχει τον δεύτερο υψηλότερο θετικό αριθμό, για τον οποίο το COUNTIF επιστρέφει 1, πράγμα που σημαίνει ότι υπάρχει μόνο ένας αριθμός μεγαλύτερος από αυτόν. Για να ξεκινήσουμε την ταξινόμηση μας με 1, όχι με 0, προσθέτουμε 1 στο αποτέλεσμα του τύπου, οπότε επιστρέφει ένα είδος 2 για το A2.

Εάν ο αριθμός είναι μεγαλύτερος από 0, ο τύπος επιστρέφει μια κενή συμβολοσειρά (""). Ο τύπος που ταξινομεί τους θετικούς αριθμούς σε αύξουσα σειρά λειτουργεί λίγο διαφορετικά: Εάν ο αριθμός είναι μεγαλύτερος από 0, το πρώτο COUNTIF παίρνει το συνολικό πλήθος των θετικών αριθμών στο σύνολο δεδομένων και το δεύτερο COUNTIF Μάθετε πόσες τιμές είναι μεγαλύτερες από αυτόν τον αριθμό.

Στη συνέχεια αφαιρείτε το τελευταίο από το πρώτο και παίρνετε το επιθυμητό εύρος. Σε αυτό το παράδειγμα, υπάρχουν 5 θετικές τιμές, 1 από τις οποίες είναι μεγαλύτερη από το A2. Στη συνέχεια, αφαιρέστε το 1 από το 5, λαμβάνοντας έτσι μια κατάταξη 4 για το A2. Οι τύποι για την ταξινόμηση αρνητικών αριθμών βασίζονται σε παρόμοια λογική.

Σημείωση: Όλοι οι παραπάνω τύποι αγνοούν τις μηδενικές τιμές γιατί το 0 δεν ανήκει ούτε στο σύνολο των θετικών αριθμών ούτε στο σύνολο των αρνητικών αριθμών. Για να συμπεριλάβετε μηδενικά στην ταξινόμηση σας, αντικαταστήστε τα >0 και <0 με >=0 και <=0, αντίστοιχα, όπου το απαιτεί η λογική του τύπου. Για παράδειγμα, για να ταξινομήσετε θετικούς αριθμούς και μηδενικά από το μεγαλύτερο στο ελάχιστο, χρησιμοποιήστε αυτόν τον τύπο:

=IF($A2>=0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)

Πώς να ταξινομήσετε δεδομένα στο Excel αγνοώντας μηδενικές τιμές

Όπως ήδη γνωρίζετε, α Ο τύπος RANGE είναι το Excel χειρίζεται όλους τους αριθμούς: θετικούς, αρνητικούς και μηδενικούς. Ωστόσο, σε ορισμένες περιπτώσεις, θέλουμε να ταξινομήσουμε μόνο κελιά με δεδομένα που αγνοούν τις τιμές 0. Μπορείτε να βρείτε μερικές πιθανές λύσεις για αυτήν την εργασία, αλλά ο τύπος Excel RANK IF είναι ο πιο καθολικός.

Φθίνοντες αριθμοί εύρους αγνοώντας το μηδέν:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))

Αύξοντες αριθμοί εύρους αγνοώντας το μηδέν:

=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10,1) – COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))

Όπου Β2: Β10 είναι το εύρος των προς ταξινόμηση αριθμών. Το καλύτερο με αυτόν τον τύπο είναι ότι λειτουργεί υπέροχα τόσο για θετικούς όσο και για αρνητικούς αριθμούς, αφήνοντας μηδενικές τιμές εκτός ταξινόμησης:

Πώς λειτουργεί αυτή η φόρμουλα

Με την πρώτη ματιά, ο τύπος μπορεί να φαίνεται λίγο περίπλοκος. Ρίχνοντας μια πιο προσεκτική ματιά, η λογική είναι πολύ απλή. Αυτός είναι ο τρόπος με τον οποίο ο τύπος RANK IF του Excel κατατάσσει τους αριθμούς από το υψηλότερο στο χαμηλότερο αγνοώντας τα μηδενικά:

• Το πρώτο IF ελέγχει αν ο αριθμός είναι 0 και αν είναι, επιστρέφει μια κενή συμβολοσειρά:

IF ($B2 = 0, «»,…)

• Εάν ο αριθμός δεν είναι μηδέν, το δεύτερο IF ελέγχει αν είναι μεγαλύτερο από 0 και αν είναι, μια κανονική συνάρτηση RANK/RANK.EQ υπολογίζει την κατάταξη:

IF ($B2 > 0, RANK ($B2, $B$2: $B$10),…)

• Εάν ο αριθμός είναι μικρότερος από 0, προσαρμόστε την ταξινόμηση με το μηδέν. Σε αυτό το παράδειγμα, υπάρχουν 4 θετικοί αριθμοί και 2 μηδενικά. Έτσι, για τον μεγαλύτερο αρνητικό αριθμό στο B10, μια συνάρτηση Range στο Excel θα επέστρεφε 7. Όμως παραλείψαμε τα μηδενικά και επομένως πρέπει να προσαρμόσουμε το εύρος κατά 2 μονάδες. Για να γίνει αυτό, αφαιρούμε τον αριθμό των μηδενικών από το εύρος:

RANGE($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0))

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

Πώς να υπολογίσετε το εύρος στο Excel με απόλυτη τιμή

Όταν ασχολείστε με μια λίστα θετικών και αρνητικών τιμών, μπορεί να χρειαστεί να ταξινομήσετε τους αριθμούς με τις απόλυτες τιμές τους, αγνοώντας το πρόσημο. Η εργασία μπορεί να ολοκληρωθεί με έναν από τους παρακάτω τύπους, στην καρδιά του οποίου βρίσκεται η συνάρτηση ABS που επιστρέφει το απόλυτη τιμή ενός αριθμού:

Εύρος ABS προς τα κάτω:

=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7 )) * (A$2:A$7<>»»))+1

Ανοδική περιοχή ABS:

=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7 )) * (A$2:A$7<>»»))+1

Ως αποτέλεσμα, οι αρνητικοί αριθμοί ταξινομούνται σαν να ήταν θετικοί αριθμοί:

Πώς να αποκτήσετε N μεγαλύτερες ή μικρότερες τιμές

Αν θέλετε να λάβετε έναν πραγματικό αριθμό N από τις μεγαλύτερες ή τις μικρότερες τιμές αντί για την κατάταξή τους, χρησιμοποιήστε τη συνάρτηση LARGE ή SMALL, αντίστοιχα. Για παράδειγμα, μπορούμε να πάρουμε τις 3 κορυφαίες βαθμολογίες μαθητών με αυτόν τον τύπο:

=ΜΕΓΑΛΟ($B$2:$B$7, $D3)

Όπου B2: B7 είναι η λίστα βαθμολογίας και D3 είναι το επιθυμητό εύρος. Επιπλέον, μπορείτε να ανακτήσετε τα ονόματα των μαθητών χρησιμοποιώντας τον τύπο ΑΓΩΝΑΣ ΕΥΡΕΤΗΡΙΟΥ (εφόσον δεν υπάρχουν διπλές βαθμολογίες στους 3 πρώτους):

=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))

Ομοίως, μπορείτε να χρησιμοποιήσετε τη συνάρτηση SMALL για να εξαγάγετε τις κάτω 3 τιμές:

=ΜΙΚΡΟ($B$2:$B$7, $D3)

Ρίξτε μια ματιά στο: Πώς να συνδυάσετε το Excel με το Word: Εισαγάγετε δεδομένα από το Excel στο Word

Pensamientos finales

Αυτός είναι ο τρόπος κατάταξης χρησιμοποιώντας τη συνάρτηση Rank στο Excel. Ευχαριστούμε για την ανάγνωση και ελπίζουμε να σας δούμε στο blog μας την επόμενη εβδομάδα! Υπάρχει ένας μεγάλος αριθμός διαθέσιμων σεμιναρίων για να διευκρινιστούν οι αμφιβολίες που σχετίζονται με τον τομέα των υπολογιστών. Επιστρέφοντας στο θέμα, υπάρχουν πολλοί εναλλακτικοί τρόποι υπολογισμού του εύρους στο Excel, ο καθένας με τις δικές του ιδιαιτερότητες. Εσείς αποφασίζετε πώς θέλετε να εργαστείτε.