Δείτε βίντεο - Αναζητήστε τη δεύτερη, την τρίτη ή την ένατη τιμή αντιστοίχισης
Όσον αφορά την αναζήτηση δεδομένων στο Excel, υπάρχουν δύο καταπληκτικές λειτουργίες που χρησιμοποιώ συχνά - VLOOKUP και INDEX (κυρίως σε συνδυασμό με τη συνάρτηση MATCH).
Ωστόσο, αυτοί οι τύποι έχουν σχεδιαστεί για να βρίσκουν μόνο την πρώτη εμφάνιση της τιμής αναζήτησης.
Αλλά τι γίνεται αν θέλετε να αναζητήσετε τη δεύτερη, την τρίτη, την τέταρτη ή την Nη τιμή.
Λοιπόν, είναι εφικτό με λίγη επιπλέον δουλειά.
Σε αυτό το σεμινάριο, θα σας δείξω διάφορους τρόπους (με παραδείγματα) για το πώς να αναζητήσετε τη δεύτερη ή τη Nth τιμή στο Excel.
Αναζητήστε τη δεύτερη, τρίτη ή Nth τιμή στο Excel
Σε αυτό το σεμινάριο, θα καλύψω δύο τρόπους για να αναζητήσετε τη δεύτερη ή την τιμή Nth στο Excel:
- Χρήση στήλης βοηθού.
- Χρήση τύπων πίνακα.
Ας ξεκινήσουμε και βουτήξτε αμέσως.
Χρήση στήλης βοηθού
Ας υποθέσουμε ότι είστε συντονιστής εκπαίδευσης σε έναν οργανισμό και έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω. Θέλετε να αναφέρετε όλη την εκπαίδευση μπροστά από το όνομα ενός υπαλλήλου.
Στο παραπάνω σύνολο δεδομένων, οι εργαζόμενοι έχουν εκπαιδευτεί σε διάφορα εργαλεία του Microsoft Office (Excel, PowerPoint και Word).
Τώρα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση VLOOKUP ή το συνδυασμό INDEX/MATCH για να βρείτε την εκπαίδευση που έχει ολοκληρώσει ένας υπάλληλος. Ωστόσο, θα επιστρέψει μόνο την πρώτη αντίστοιχη παρουσία.
Για παράδειγμα, στην περίπτωση του John, έχει παρακολουθήσει και τις τρεις προπονήσεις, αλλά όταν αναζητώ το όνομά του με VLOOKUP ή INDEX/MATCH, θα επιστρέφει πάντα το «Excel», το οποίο είναι η πρώτη εκπαίδευση για το όνομά του στη λίστα .
Για να γίνει αυτό, μπορούμε να χρησιμοποιήσουμε μια στήλη βοηθού και να δημιουργήσουμε μοναδικές τιμές αναζήτησης σε αυτήν.
Εδώ είναι τα βήματα:
- Εισαγάγετε μια στήλη πριν από τη στήλη που παραθέτει την εκπαίδευση.
- Στο κελί Β2, πληκτρολογήστε τον ακόλουθο τύπο:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- Στο κελί F2, εισαγάγετε τον ακόλουθο τύπο και αντιγράψτε-επικολλήστε για όλα τα άλλα κελιά:
= IFNA (VLOOKUP ($ E2 & ΣΤΗΛΕΣ ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Ο παραπάνω τύπος θα επέστρεφε την εκπαίδευση για κάθε εργαζόμενο με τη σειρά που εμφανίζεται στη λίστα. Σε περίπτωση που δεν υπάρχει κατάρτιση για έναν εργαζόμενο, επιστρέφει ένα κενό.
Πώς λειτουργεί αυτός ο τύπος;
Ο τύπος COUNTIF στη στήλη βοηθού καθιστά το όνομα κάθε εργαζομένου μοναδικό προσθέτοντας έναν αριθμό σε αυτό. Για παράδειγμα, η πρώτη περίπτωση του Ιωάννη γίνεται John1, η δεύτερη περίπτωση γίνεται John2 και ούτω καθεξής.
Ο τύπος VLOOKUP χρησιμοποιεί τώρα αυτά τα μοναδικά ονόματα εργαζομένων για να βρει την αντίστοιχη εκπαίδευση.
Σημειώστε ότι $ E2 & COLUMNS ($ F $ 1: F1) είναι η τιμή αναζήτησης στον τύπο. Αυτό θα προσθέσει έναν αριθμό στο όνομα του υπαλλήλου με βάση τον αριθμό στήλης. Για παράδειγμα, όταν αυτός ο τύπος χρησιμοποιείται στο κελί F2, η τιμή αναζήτησης γίνεται "John1". Στο κελί G2, γίνεται "John2" και ούτω καθεξής.
Χρήση τύπου Array
Εάν δεν θέλετε να αλλάξετε το αρχικό σύνολο δεδομένων προσθέτοντας στήλες βοηθού, μπορείτε επίσης να χρησιμοποιήσετε έναν τύπο πίνακα για να αναζητήσετε τη δεύτερη, την τρίτη ή την nη τιμή.
Ας υποθέσουμε ότι έχετε το ίδιο σύνολο δεδομένων όπως φαίνεται παρακάτω:
Ακολουθεί ο τύπος που θα επιστρέψει τη σωστή τιμή αναζήτησης:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, "")), ΣΤΗΛΕΣ ($ E $ 1 : Ε1))), "")
Αντιγράψτε αυτόν τον τύπο και επικολλήστε τον στο κελί Ε2.
Σημειώστε ότι πρόκειται για έναν τύπο πίνακα και πρέπει να χρησιμοποιήσετε Control + Shift + Enter (κρατήστε πατημένα τα πλήκτρα Control και Shift και πατήστε το πλήκτρο Enter), αντί να πατήσετε μόνο το πλήκτρο Enter.
Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου.
Πώς λειτουργεί αυτός ο τύπος;
Ας χωρίσουμε αυτόν τον τύπο σε μέρη και να δούμε πώς λειτουργεί.
$ A $ 2: $ A $ 14 = $ D2
Το παραπάνω μέρος του τύπου συγκρίνει κάθε κελί στο A2: A14 με την τιμή στο D2. Σε αυτό το σύνολο δεδομένων, ελέγχει εάν ένα κελί περιέχει το όνομα "John" ή όχι.
Επιστρέφει μια σειρά TRUE of FALSE. Εάν το κελί έχει το όνομα "John" θα ήταν True, αλλιώς θα ήταν False.
Παρακάτω είναι ο πίνακας που θα λάβετε σε αυτό το παράδειγμα:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE} FALSE}
Σημειώστε ότι έχει TRUE στην 1η, 7η και 111η θέση, καθώς εκεί εμφανίζεται το όνομα John στο σύνολο δεδομένων.
ΑΝ ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Ο παραπάνω τύπος IF χρησιμοποιεί τον πίνακα TRUE και FALSE και αντικαθιστά το TRUE με τη θέση εμφάνισής του στη λίστα (δίνεται από ROW ($ A $ 2: $ A $ 14) -1) και FALSE με "" (κενά). Ακολουθεί ο πίνακας που προκύπτει με αυτόν τον τύπο IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Σημείωση από 1, 7 και 11 είναι η θέση εμφάνισης του Ιωάννη στη λίστα.
ΜΙΚΡΟ (ΑΝ ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ”” ”), ΣΤΗΛΕΣ ($ E $ 1: E1))
Η συνάρτηση ΜΙΚΡΟ επιλέγει τώρα τον πρώτο μικρότερο, δεύτερο μικρότερο, τρίτο μικρότερο αριθμό από αυτόν τον πίνακα. Σημειώστε ότι χρησιμοποιεί τη συνάρτηση ΣΤΗΛΕΣ για να δημιουργήσει τον αριθμό στήλης. Στο κελί E2, η συνάρτηση COLUMNS επιστρέφει 1 και η SMALL συνάρτηση 1. Στο κελί F2, η συνάρτηση COLUMNS επιστρέφει 2 και η συνάρτηση SMALL επιστρέφει 7.
INDEX ($ B $ 2: $ B $ 14, ΜΙΚΡΟ (ΑΝ ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1 ","), ΣΤΗΛΕΣ ($ E $ 1: E1) ))
Η συνάρτηση INDEX επιστρέφει τώρα την τιμή από τη λίστα στη στήλη Β με βάση τη θέση που επιστρέφει η συνάρτηση ΜΙΚΡΟΣ. Ως εκ τούτου, στο κελί Ε2, επιστρέφει το «Excel», το οποίο είναι το πρώτο στοιχείο στο Β2: Β14. Στο κελί F2, επιστρέφει το PowerPoint, το οποίο είναι το 7ο στοιχείο στη λίστα.
Δεδομένου ότι υπάρχουν περιπτώσεις όπου υπάρχει μόνο μία ή δύο εκπαίδευση για ορισμένους υπαλλήλους, η λειτουργία INDEX θα επέστρεφε ένα σφάλμα. Η συνάρτηση IFERROR χρησιμοποιείται για να επιστρέψει ένα κενό στη θέση του σφάλματος.
Σημειώστε ότι σε αυτά τα παραδείγματα, έχω χρησιμοποιήσει αναφορές εύρους. Ωστόσο, σε πρακτικά παραδείγματα, είναι ωφέλιμο να μετατρέψετε τα δεδομένα σε πίνακα Excel. Μετατρέποντας σε πίνακα Excel, μπορείτε να χρησιμοποιήσετε δομημένες αναφορές, γεγονός που διευκολύνει τη δημιουργία τύπων. Επίσης, ένας Πίνακας Excel μπορεί να λάβει αυτόματα υπόψη κάθε νέο εκπαιδευτικό στοιχείο που προστίθεται στη λίστα (έτσι δεν χρειάζεται να προσαρμόζετε τους τύπους κάθε φορά).
Τι κάνετε όταν πρέπει να αναζητήσετε τη δεύτερη, την τρίτη ή τη Nth τιμή; Είμαι σίγουρος ότι υπάρχουν περισσότεροι τρόποι για να γίνει αυτό. Εάν χρησιμοποιείτε κάτι πιο εύκολο από αυτό που αναφέρεται εδώ, μοιραστείτε το μαζί μας στην ενότητα σχολίων.
Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου.
