Σε αυτό το άρθρο, θα μάθουμε πώς να βρίσκουμε το nth μικρότερο με κριτήρια και nth το μεγαλύτερο με κριτήρια από τον δεδομένο πίνακα στο Excel.
Σενάριο:
Με απλά λόγια, ενώ εργαζόμαστε με αριθμούς σε αριθμούς δεδομένων, μερικές φορές δίνεται μια συνθήκη, δηλαδή όταν πρέπει να αναζητήσουμε την 5η υψηλότερη τιμή που δίνεται. Μπορείτε να εκτελέσετε τη λύση σε αυτό το πρόβλημα εύκολα χρησιμοποιώντας τις λειτουργίες excel όπως εξηγείται παρακάτω.
Το ένατο μεγαλύτερο με κριτήρια
Πώς να λύσετε το πρόβλημα;
Για αυτό το άρθρο θα πρέπει να χρησιμοποιήσουμε τη συνάρτηση LARGE. Τώρα θα φτιάξουμε έναν τύπο από αυτές τις συναρτήσεις. Εδώ μας δίνεται ένας πίνακας και πρέπει να βρούμε την ένατη μεγαλύτερη τιμή στο εύρος με δεδομένα κριτήρια.
Γενικός τύπος:
{ = LARGE (IF (c_range = "value", range), ν ) }
c_range : εύρος κριτηρίων
αξία : τα κριτήρια αντιστοιχούν στην τιμή
εύρος : πίνακας αποτελεσμάτων
ν : nth μεγαλύτερο
Σημείωση : Μην τοποθετείτε χειροκίνητα αγκύλες. Αυτός είναι ένας τύπος πίνακα, πρέπει να χρησιμοποιηθεί Ctrl + Shift + Enter στη θέση του μόνο Enter όπου επιστρέφει #ΑΡΙΘ! λάθος.
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Έτσι, ας δοκιμάσουμε αυτόν τον τύπο εκτελώντας τον στο παράδειγμα που φαίνεται παρακάτω.
Εδώ έχουμε στοιχεία παραγγελίας με ημερομηνία παραγγελίας, περιοχή και τιμή παραγγελίας.
Πρέπει να μάθουμε την 5η μεγαλύτερη τιμή παραγγελίας από την περιοχή της Ανατολής. Εδώ το εύρος δίνεται ως το εργαλείο excel που ονομάζεται range range.
περιοχή (C3: C16)
τιμή (D3: D16)
Πρώτον, πρέπει να βρούμε τις πέμπτες μεγαλύτερες τιμές χρησιμοποιώντας τη συνάρτηση LARGE και, στη συνέχεια, η άθροιση να εκτελεστεί πάνω από αυτές τις 5 τιμές. Τώρα θα χρησιμοποιήσουμε τον ακόλουθο τύπο για να πάρουμε το άθροισμα
Χρησιμοποιήστε τον τύπο:
{ = ΜΕΓΑΛΟ (ΑΝ (περιοχή = G5, τιμή), F5)}
Εξήγηση:
- Η συνάρτηση IF ελέγχει τα κριτήρια που ταιριάζουν με όλες τις τιμές στην περιοχή με δεδομένη τιμή East στο κελί G5 και επιστρέφει τις αντίστοιχες TRUE τιμές από το εύρος τιμών.
= ΜΕΓΑΛΟ ({58.41; 303.63; ALΕΥΔΟ; 153.34; 82.84; ALΕΥΔΟ; 520.01; ALΕΜΑ; 177; ALΕΜΑ; ALΕΥΔΟ; 57.97; 97.72; ALΕΜΑ}), F5)
- Η συνάρτηση LARGE παίρνει τον πίνακα που έχει όλο το εύρος τιμών όπως φαίνεται παραπάνω και επιστρέφει την 5η μεγαλύτερη τιμή από τον πίνακα όπως φαίνεται στο παρακάτω στιγμιότυπο.
Μπορείτε να δείτε τον τύπο στο πλαίσιο τύπων, όπως φαίνεται στο παραπάνω στιγμιότυπο. Χρησιμοποιήστε Ctrl + Shift + Enter για να λάβετε το αποτέλεσμα.
Όπως μπορείτε να δείτε, ο τύπος πίνακα επιστρέφει την 5η μεγαλύτερη τιμή 97,72 $ με ανατολική περιοχή.
Μπορείτε επίσης να τροφοδοτήσετε τον πίνακα ως πίνακα, κριτήρια εντός εισαγωγικών και n τιμή απευθείας στη συνάρτηση αντί να χρησιμοποιήσετε αναφορά κελιών ή εύρος ονομάτων.
Χρησιμοποιήστε τον τύπο:
{ = LARGE (IF (C3: C16 = "West", D3: D16), 3)}
Χρήση Ctrl + Shift + Enter
Μπορείτε να δείτε ότι ο τύπος λειτουργεί καλά όπου βρίσκετε την ένατη μεγαλύτερη τιμή με κριτήρια στον πίνακα.
Το ένατο χαμηλότερο με κριτήρια
Πώς να λύσετε το πρόβλημα;
Για αυτό το άρθρο θα πρέπει να χρησιμοποιήσουμε τη λειτουργία ΜΙΚΡΕΣ. Τώρα θα φτιάξουμε έναν τύπο από αυτές τις συναρτήσεις. Εδώ μας δίνεται ένας πίνακας και πρέπει να βρούμε την ένατη μικρότερη τιμή στο εύρος με δεδομένα κριτήρια.
Γενικός τύπος:
{ = ΜΙΚΡΟ (ΑΝ (c_range = "value", range), ν ) }
c_range : εύρος κριτηρίων
αξία : τα κριτήρια αντιστοιχούν στην τιμή
εύρος : πίνακας αποτελεσμάτων
ν : nth μεγαλύτερο
Σημείωση : Μην τοποθετείτε χειροκίνητα αγκύλες. Αυτός είναι ένας τύπος πίνακα, πρέπει να χρησιμοποιηθεί Ctrl + Shift + Enter στη θέση του μόνο Enter όπου επιστρέφει #ΑΡΙΘ! λάθος.
Παράδειγμα:
Όλα αυτά μπορεί να προκαλούν σύγχυση στην κατανόηση. Έτσι, ας δοκιμάσουμε αυτόν τον τύπο εκτελώντας τον στο παράδειγμα που φαίνεται παρακάτω.
Εδώ έχουμε στοιχεία παραγγελίας με ημερομηνία παραγγελίας, περιοχή και τιμή παραγγελίας.
Πρέπει να μάθουμε την ένατη μικρότερη τιμή παραγγελίας από την περιοχή της Ανατολής. Εδώ το εύρος δίνεται ως το εργαλείο excel που ονομάζεται range range.
περιοχή (C3: C16)
τιμή (D3: D16)
Πρώτον, πρέπει να βρούμε τις Πέμπτες μικρότερες ή χαμηλότερες τιμές χρησιμοποιώντας τη συνάρτηση ΜΙΚΡΕΣ και στη συνέχεια να αναζητήσουμε τη λειτουργία που εκτελείται πάνω από τις τιμές. Τώρα θα χρησιμοποιήσουμε τον ακόλουθο τύπο για να πάρουμε το άθροισμα
Χρησιμοποιήστε τον τύπο:
{ = ΜΙΚΡΟ (ΑΝ (περιοχή = G5, τιμή), F5)}
Εξήγηση:
- Η συνάρτηση IF ελέγχει τα κριτήρια που ταιριάζουν με όλες τις τιμές στην περιοχή με δεδομένη τιμή East στο κελί G5 και επιστρέφει τις αντίστοιχες TRUE τιμές από το εύρος τιμών.
= ΜΕΓΑΛΟ ({58.41; 303.63; ALΕΥΔΟ; 153.34; 82.84; ALΕΥΔΟ; 520.01; ALΕΜΑ; 177; ALΕΜΑ; ALΕΜΑ; 57.97; 97.72; ALΕΜΑ}), F5)
- Η συνάρτηση SMALL παίρνει τον πίνακα που έχει όλο το εύρος τιμών όπως φαίνεται παραπάνω και επιστρέφει την 5η μεγαλύτερη τιμή από τον πίνακα όπως φαίνεται στο παρακάτω στιγμιότυπο.
Μπορείτε να δείτε τον τύπο στο πλαίσιο τύπων, όπως φαίνεται στο παραπάνω στιγμιότυπο. Χρήση Ctrl + Shift + Enter για να πάρεις το αποτέλεσμα.
Όπως μπορείτε να δείτε, ο τύπος πίνακα επιστρέφει την 5η μικρότερη τιμή 153,34 $ με ανατολική περιοχή.
Μπορείτε επίσης να τροφοδοτήσετε τον πίνακα ως πίνακα, κριτήρια εντός εισαγωγικών και n τιμή απευθείας στη συνάρτηση αντί να χρησιμοποιήσετε αναφορά κελιών ή εύρος ονομάτων.
Χρησιμοποιήστε τον τύπο:
{ = ΜΙΚΡΟ (ΑΝ (C3: C16 = "δυτικά", D3: D16), 3)}
Χρήση Ctrl + Shift + Enter
Μπορείτε να δείτε ότι ο τύπος λειτουργεί καλά όπου βρίσκετε την ένατη μεγαλύτερη τιμή με κριτήρια στον πίνακα.
Ακολουθούν μερικές παρατηρήσεις παρατήρησης που φαίνονται παρακάτω.
Σημειώσεις:
- Ο τύπος λειτουργεί μόνο με αριθμούς.
- Μην τοποθετείτε χειροκίνητα αγκύλες. Αυτός είναι ένας τύπος πίνακα, πρέπει να χρησιμοποιηθεί Ctrl + Shift + Enter στη θέση του μόνο Enter όπου επιστρέφει #ΑΡΙΘ! λάθος.
- Η τιμή του δεν μπορεί να είναι μεγαλύτερη από τον αριθμό των κριτηρίων ή επιστρέφει #ΑΡΙΘ! Λάθος.
- Τα κριτήρια IF δεν ταιριάζουν με το σφάλμα επιστροφής τύπου.
- Ο πίνακας ορίσματος πρέπει να έχει το ίδιο μήκος αλλιώς τη συνάρτηση.
Ας ελπίσουμε ότι αυτό το άρθρο για το πώς να βρείτε το nth μεγαλύτερο με κριτήρια και το nth μικρότερο με κριτήρια στο excel είναι επεξηγηματικό. Εξερευνήστε περισσότερα άρθρα σχετικά με τους τύπους αναζήτησης στο Excel εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT στο Excel: Επιστρέφει το SUM μετά τον πολλαπλασιασμό τιμών σε πολλούς πίνακες στο excel.
SUM εάν η ημερομηνία είναι μεταξύ : Επιστρέφει το άθροισμα των τιμών μεταξύ συγκεκριμένων ημερομηνιών ή περιόδου στο excel.
Άθροισμα εάν η ημερομηνία είναι μεγαλύτερη από τη δεδομένη ημερομηνία: Επιστρέφει το άθροισμα αξιών μετά τη δεδομένη ημερομηνία ή περίοδο στο excel.
2 τρόποι για άθροισμα ανά μήνα στο Excel: Επιστρέφει το άθροισμα αξιών μέσα σε ένα συγκεκριμένο μήνα στο excel.
Πώς να αθροίσετε πολλαπλές στήλες με συνθήκη: Επιστρέφει το άθροισμα των τιμών σε πολλές στήλες με συνθήκη στο Excel
Πώς να χρησιμοποιήσετε μπαλαντέρ στο excel : Μετρήστε κελιά που ταιριάζουν φράσεις χρησιμοποιώντας τους μπαλαντέρ στο excel
Δημοφιλή άρθρα
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας : Προχωρήστε γρηγορότερα στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε το tσυνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.