Λειτουργία Excel INDEX (Παραδείγματα + Βίντεο)
Πότε να χρησιμοποιήσετε τη συνάρτηση INDEX του Excel
Η συνάρτηση Excel INDEX μπορεί να χρησιμοποιηθεί όταν θέλετε να ανακτήσετε την τιμή από δεδομένα πίνακα και έχετε τον αριθμό γραμμής και τον αριθμό στήλης του σημείου δεδομένων. Για παράδειγμα, στο παρακάτω παράδειγμα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση INDEX για να λάβετε τα σήματα του "Tom" στη Φυσική όταν γνωρίζετε τον αριθμό σειράς και τον αριθμό στήλης στο σύνολο δεδομένων.
Τι επιστρέφει
Επιστρέφει την τιμή από έναν πίνακα για τον καθορισμένο αριθμό γραμμής και αριθμό στήλης.
Σύνταξη
= INDEX (array, row_num, [col_num])
= INDEX (array, row_num, [col_num], [area_num])
Η συνάρτηση INDEX έχει 2 σύνταξη. Το πρώτο χρησιμοποιείται στις περισσότερες περιπτώσεις, ωστόσο, σε περίπτωση αναζήτησης τριών κατευθύνσεων, χρησιμοποιείται το δεύτερο (καλύπτεται στο Παράδειγμα 5).
Ορίσματα εισόδου
- πίνακας - ένα εύρος κελιών ή σταθερά πίνακα.
- row_num - τον αριθμό σειράς από τον οποίο πρέπει να ανακτηθεί η τιμή.
- [col_num] - τον αριθμό στήλης από την οποία πρέπει να ανακτηθεί η τιμή. Αν και αυτό είναι ένα προαιρετικό όρισμα, αλλά εάν δεν παρέχεται το row_num, πρέπει να δοθεί.
- [area_num] - (Προαιρετικό) Εάν το όρισμα πίνακα αποτελείται από πολλά εύρη, αυτός ο αριθμός θα χρησιμοποιηθεί για την επιλογή της αναφοράς από όλα τα εύρη.
Πρόσθετες σημειώσεις (βαρετά πράγματα … αλλά σημαντικό να γνωρίζετε)
- Εάν ο αριθμός γραμμής ή ο αριθμός στήλης είναι 0, επιστρέφει τις τιμές ολόκληρης της σειράς ή στήλης αντίστοιχα.
- Εάν η συνάρτηση INDEX χρησιμοποιείται μπροστά από μια αναφορά κελιού (όπως το Α1 :) επιστρέφει μια αναφορά κελιού αντί για μια τιμή (δείτε παρακάτω παραδείγματα).
- Χρησιμοποιείται ευρέως μαζί με τη συνάρτηση MATCH.
- Σε αντίθεση με το VLOOKUP, η συνάρτηση INDEX μπορεί να επιστρέψει μια τιμή από τα αριστερά της τιμής αναζήτησης.
- Η συνάρτηση INDEX έχει δύο μορφές - φόρμα πίνακα και φόρμα αναφοράς
- Η "φόρμα πίνακα" είναι όπου λαμβάνετε μια τιμή με βάση τον αριθμό γραμμής και στήλης από έναν δεδομένο πίνακα.
- Η "φόρμα αναφοράς" είναι όπου υπάρχουν πολλοί πίνακες και χρησιμοποιείτε το όρισμα area_num για να επιλέξετε τον πίνακα και, στη συνέχεια, να λάβετε μια τιμή μέσα σε αυτόν χρησιμοποιώντας τον αριθμό γραμμής και στήλης (δείτε ζωντανό παράδειγμα παρακάτω).
Λειτουργία INDEX του Excel - Παραδείγματα
Ακολουθούν έξι παραδείγματα χρήσης της συνάρτησης INDEX του Excel.
Παράδειγμα 1 - Εύρεση σημείων του Τομ στη Φυσική (αμφίδρομη αναζήτηση)
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:
Για να βρείτε τα σημεία του Τομ στη Φυσική, χρησιμοποιήστε τον παρακάτω τύπο:
= INDEX ($ B $ 3: $ E $ 10,3,2)
Αυτός ο τύπος INDEX καθορίζει τον πίνακα ως $ B $ 3: $ E $ 10 που έχει τα σημάδια για όλα τα θέματα. Στη συνέχεια, χρησιμοποιεί τον αριθμό σειράς (3) και τον αριθμό στήλης (2) για να πάρει τα σημεία του Τομ στη Φυσική.
Παράδειγμα 2 - Δυναμική της τιμής LOOKUP Value χρησιμοποιώντας τη συνάρτηση MATCH
Ενδέχεται να μην είναι πάντα δυνατό να καθορίσετε τον αριθμό γραμμής και τον αριθμό στήλης με μη αυτόματο τρόπο. Μπορεί να έχετε ένα τεράστιο σύνολο δεδομένων ή να θέλετε να το κάνετε δυναμικό έτσι ώστε να προσδιορίζει αυτόματα το όνομα και/ή το θέμα που καθορίζεται στα κελιά και να δίνει το σωστό αποτέλεσμα.
Κάτι όπως φαίνεται παρακάτω:
Αυτό μπορεί να γίνει χρησιμοποιώντας συνδυασμό INDEX και MATCH συνάρτησης.
Ακολουθεί ο τύπος που θα κάνει τις τιμές αναζήτησης δυναμικές:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Στον παραπάνω τύπο, αντί για τη σκληρή κωδικοποίηση του αριθμού γραμμής και του αριθμού στήλης, χρησιμοποιείται η συνάρτηση MATCH για να γίνει δυναμική.
- Ο δυναμικός αριθμός σειράς δίνεται από το ακόλουθο μέρος του τύπου - MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0). Σαρώνει το όνομα των μαθητών και προσδιορίζει την τιμή αναζήτησης ($ G $ 5 σε αυτήν την περίπτωση). Στη συνέχεια επιστρέφει τον αριθμό γραμμής της τιμής αναζήτησης στο σύνολο δεδομένων. Για παράδειγμα, εάν η τιμή αναζήτησης είναι Matt, θα επιστρέψει 1, αν είναι Bob, θα επιστρέψει 2 και ούτω καθεξής.
- Ο αριθμός δυναμικής στήλης δίνεται από το ακόλουθο μέρος του τύπου - MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0). Σαρώνει τα ονόματα των θεμάτων και προσδιορίζει την τιμή αναζήτησης ($ H $ 4 σε αυτήν την περίπτωση). Στη συνέχεια επιστρέφει τον αριθμό στήλης της τιμής αναζήτησης στο σύνολο δεδομένων. Για παράδειγμα, εάν η τιμή αναζήτησης είναι Μαθηματικά, θα επιστρέψει 1, αν είναι Φυσική, θα επιστρέψει 2 και ούτω καθεξής.
Παράδειγμα 3 - Χρήση αναπτυσσόμενων λιστών ως τιμών αναζήτησης
Στο παραπάνω παράδειγμα, πρέπει να εισαγάγουμε χειροκίνητα τα δεδομένα. Αυτό θα μπορούσε να είναι χρονοβόρο και επιρρεπές σε σφάλματα, ειδικά αν έχετε μια τεράστια λίστα τιμών αναζήτησης.
Μια καλή ιδέα σε τέτοιες περιπτώσεις είναι να δημιουργήσετε μια αναπτυσσόμενη λίστα με τις τιμές αναζήτησης (σε αυτή την περίπτωση, μπορεί να είναι ονόματα και θέματα μαθητών) και στη συνέχεια να επιλέξετε απλώς από τη λίστα. Με βάση την επιλογή, ο τύπος θα ενημερώνει αυτόματα το αποτέλεσμα.
Κάτι όπως φαίνεται παρακάτω:
Αυτό καθιστά ένα καλό στοιχείο πίνακα εργαλείων, καθώς μπορείτε να έχετε ένα τεράστιο σύνολο δεδομένων με εκατοντάδες μαθητές στο πίσω άκρο, αλλά ο τελικός χρήστης (ας πούμε δάσκαλος) μπορεί να πάρει γρήγορα τις βαθμολογίες ενός μαθητή σε ένα θέμα κάνοντας απλώς τις επιλογές από το drop down.
Πώς να το φτιάξετε αυτό:
Ο τύπος που χρησιμοποιείται σε αυτήν την περίπτωση είναι ο ίδιος που χρησιμοποιείται στο Παράδειγμα 2.
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), MATCH ($ H $ 4, $ B $ 2: $ E $ 2,0))
Οι τιμές αναζήτησης έχουν μετατραπεί σε αναπτυσσόμενες λίστες.
Ακολουθούν τα βήματα για τη δημιουργία της αναπτυσσόμενης λίστας του Excel:
- Επιλέξτε το κελί στο οποίο θέλετε την αναπτυσσόμενη λίστα. Σε αυτό το παράδειγμα, στο G4, θέλουμε τα ονόματα των μαθητών.
- Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
- Στο πλαίσιο Διάλογος επικύρωσης δεδομένων, στην καρτέλα ρυθμίσεις, επιλέξτε Λίστα από το αναπτυσσόμενο μενού Επιτρέπεται.
- Στην πηγή, επιλέξτε $ A $ 3: $ A $ 10
- Κάντε κλικ στο OK.
Τώρα θα έχετε την αναπτυσσόμενη λίστα στο κελί G5. Ομοίως, μπορείτε να δημιουργήσετε ένα στο Η4 για τα θέματα.
Παράδειγμα 4 - Επιστροφή τιμών από ολόκληρη σειρά/στήλη
Στα παραπάνω παραδείγματα, χρησιμοποιήσαμε τη συνάρτηση Excel INDEX για να πραγματοποιήσουμε αναζήτηση διπλής κατεύθυνσης και να λάβουμε μία τιμή.
Τώρα, τι γίνεται αν θέλετε να λάβετε όλες τις βαθμολογίες ενός μαθητή. Αυτό μπορεί να σας επιτρέψει να βρείτε τη μέγιστη/ελάχιστη βαθμολογία αυτού του μαθητή ή τις συνολικές βαθμολογίες σε όλα τα μαθήματα.
Σε απλά αγγλικά, θέλετε πρώτα να λάβετε ολόκληρη τη σειρά βαθμολογιών για έναν μαθητή (ας πούμε ο Bob) και στη συνέχεια μέσα σε αυτές τις τιμές προσδιορίστε την υψηλότερη βαθμολογία ή το σύνολο όλων των βαθμολογιών.
Εδώ είναι το κόλπο.
Στο Excel INDEX Function, όταν εισάγετε το αριθμός στήλης ως 0, θα επιστρέψει τις τιμές ολόκληρης της σειράς.
Έτσι, ο τύπος για αυτό θα είναι:
= INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0)
Τώρα αυτός ο τύπος. αν χρησιμοποιηθεί ως έχει, θα επέστρεφε την #ΤΙΜΗ! λάθος. Ενώ εμφανίζει το σφάλμα, στο backend, επιστρέφει έναν πίνακα που έχει όλες τις βαθμολογίες για τον Tom - {57,77,91,91}.
Εάν επιλέξετε τον τύπο στη λειτουργία επεξεργασίας και πατήσετε F9, θα μπορείτε να δείτε τον πίνακα που επιστρέφει (όπως φαίνεται παρακάτω): 
Ομοίως, με βάση την τιμή αναζήτησης, όταν ο αριθμός στήλης ορίζεται ως 0 (ή παραμένει κενός), επιστρέφει όλες τις τιμές στη σειρά για την τιμή αναζήτησης
Τώρα για να υπολογίσουμε τη συνολική βαθμολογία που έλαβε ο Tom, μπορούμε απλά να χρησιμοποιήσουμε τον παραπάνω τύπο μέσα στη συνάρτηση SUM.
= SUM (INDEX ($ B $ 3: $ E $ 10, MATCH ($ G $ 5, $ A $ 3: $ A $ 10,0), 0))
Σε παρόμοιες γραμμές, για τον υπολογισμό της υψηλότερης βαθμολογίας, μπορούμε να χρησιμοποιήσουμε MAX/LARGE και για τον ελάχιστο, μπορούμε να χρησιμοποιήσουμε MIN/SMALL.
Παράδειγμα 5 - Τριπλή αναζήτηση με χρήση INDEX/MATCH
Η συνάρτηση Excel INDEX έχει σχεδιαστεί για να χειρίζεται τριμερείς αναζητήσεις.
Τι είναι η αναζήτηση τριών κατευθύνσεων;
Στα παραπάνω παραδείγματα, χρησιμοποιήσαμε έναν πίνακα με βαθμολογίες για μαθητές σε διαφορετικά θέματα. Αυτό είναι ένα παράδειγμα αμφίδρομης αναζήτησης καθώς χρησιμοποιούμε δύο μεταβλητές για να λάβουμε τη βαθμολογία (όνομα μαθητή και θέμα).
Τώρα, ας υποθέσουμε ότι σε έναν χρόνο, ένας μαθητής έχει τρία διαφορετικά επίπεδα εξετάσεων, τεστ μονάδας, ενδιάμεση και τελική εξέταση (αυτό είχα όταν ήμουν φοιτητής).
Μια αναζήτηση τριών κατευθύνσεων θα ήταν η δυνατότητα να λάβετε βαθμολογίες μαθητή για ένα συγκεκριμένο θέμα από το καθορισμένο επίπεδο εξετάσεων. Αυτό θα το καθιστούσε μια αναζήτηση τριών κατευθύνσεων καθώς υπάρχουν τρεις μεταβλητές (όνομα μαθητή, όνομα υποκειμένου και επίπεδο εξέτασης).
Ακολουθεί ένα παράδειγμα αναζήτησης τριών κατευθύνσεων:
Στο παραπάνω παράδειγμα, εκτός από την επιλογή του ονόματος και του ονόματος του μαθητή, μπορείτε επίσης να επιλέξετε το επίπεδο των εξετάσεων. Με βάση το επίπεδο των εξετάσεων, επιστρέφει την αντίστοιχη τιμή από έναν από τους τρεις πίνακες.
Εδώ είναι ο τύπος που χρησιμοποιείται στο κελί Η4:
= INDEX (($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23), MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0), MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0), IF ($ H $ 2 = "Δοκιμή μονάδας", 1, IF ($ H $ 2 = "Ενδιάμεσο", 2,3)))
Ας σπάσουμε αυτόν τον τύπο για να καταλάβουμε πώς λειτουργεί.
Αυτός ο τύπος παίρνει τέσσερα επιχειρήματα. Το INDEX είναι μία από αυτές τις λειτουργίες στο Excel που έχει περισσότερες από μία συντακτικές.
= INDEX (array, row_num, [col_num])
= INDEX (array, row_num, [col_num], [area_num])
Μέχρι τώρα σε όλο το παραπάνω παράδειγμα, χρησιμοποιήσαμε την πρώτη σύνταξη, αλλά για να κάνουμε μια αναζήτηση τριών κατευθύνσεων, πρέπει να χρησιμοποιήσουμε τη δεύτερη σύνταξη.
Τώρα ας δούμε κάθε μέρος του τύπου με βάση τη δεύτερη σύνταξη.
- πίνακας - ($ B $ 3: $ E $ 7, $ B $ 11: $ E $ 15, $ B $ 19: $ E $ 23): Αντί να χρησιμοποιήσουμε έναν μόνο πίνακα, σε αυτήν την περίπτωση, χρησιμοποιήσαμε τρεις πίνακες εντός παρένθεσης.
- row_num - MATCH ($ G $ 4, $ A $ 3: $ A $ 7,0): Η συνάρτηση MATCH χρησιμοποιείται για τον εντοπισμό της θέσης του ονόματος του μαθητή στο κελί $ G $ 4 στη λίστα με το όνομα του μαθητή.
- col_num - MATCH ($ H $ 3, $ B $ 2: $ E $ 2,0): Η συνάρτηση MATCH χρησιμοποιείται για να βρει τη θέση του ονόματος του θέματος στο κελί $ H $ 3 στη λίστα του ονόματος του θέματος.
- [area_num] - IF ($ H $ 2 = "Δοκιμή μονάδας", 1, IF ($ H $ 2 = "Ενδιάμεσο", 2,3)): Η τιμή του αριθμού περιοχής υποδεικνύει τη συνάρτηση INDEX τον πίνακα που πρέπει να επιλέξετε. Σε αυτό το παράδειγμα, έχουμε τρεις πίνακες στο πρώτο όρισμα. Εάν επιλέξετε Unit Test από το αναπτυσσόμενο μενού, η συνάρτηση IF επιστρέφει 1 και οι συναρτήσεις INDEX επιλέγουν 1η συστοιχία από τους τρεις πίνακες (που είναι $ B $ 3: $ E $ 7).
Παράδειγμα 6 - Δημιουργία αναφοράς χρησιμοποιώντας τη συνάρτηση INDEX (Δυναμικές ονομαστικές περιοχές)
Αυτή είναι μια άγρια χρήση της συνάρτησης Excel INDEX.
Ας πάρουμε ένα απλό παράδειγμα.
Έχω μια λίστα με ονόματα όπως φαίνεται παρακάτω:
Τώρα μπορώ να χρησιμοποιήσω μια απλή συνάρτηση INDEX για να λάβω το επώνυμο στη λίστα.
Εδώ είναι ο τύπος:
= INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Αυτή η συνάρτηση μετρά απλώς τον αριθμό των κελιών που δεν είναι κενά και επιστρέφει το τελευταίο στοιχείο από αυτήν τη λίστα (λειτουργεί μόνο όταν δεν υπάρχουν κενά στη λίστα).
Τώρα, αυτό που έρχεται είναι η μαγεία.
Εάν βάλετε τον τύπο μπροστά από μια αναφορά κελιού, ο τύπος θα επιστρέψει μια αναφορά κελιού της τιμής αντιστοίχισης (αντί της ίδιας της τιμής).
= A2: INDEX ($ A $ 2: $ A $ 9, COUNTA ($ A $ 2: $ A $ 9))
Θα περιμένατε ότι ο παραπάνω τύπος θα επιστρέψει = A2: "Josh" (όπου ο Josh είναι η τελευταία τιμή στη λίστα). Ωστόσο, επιστρέφει = A2: A9 και ως εκ τούτου λαμβάνετε μια σειρά από ονόματα όπως φαίνεται παρακάτω:
Ένα πρακτικό παράδειγμα όπου αυτή η τεχνική μπορεί να είναι χρήσιμη είναι η δημιουργία δυναμικών ονομάτων εύρους.
Αυτό είναι σε αυτό το σεμινάριο. Προσπάθησα να καλύψω σημαντικά παραδείγματα χρήσης της συνάρτησης Excel INDEX. Εάν θέλετε να δείτε περισσότερα παραδείγματα που προστίθενται σε αυτήν τη λίστα, ενημερώστε με στην ενότητα σχολίων.
Σημείωση: Προσπάθησα με κάθε τρόπο να διαβάσω αυτό το σεμινάριο, αλλά σε περίπτωση που βρείτε λάθη ή ορθογραφικά λάθη, ενημερώστε με 🙂
Λειτουργία INDEX Excel - Εκπαιδευτικό βίντεο
- Λειτουργία Excel VLOOKUP.
- Λειτουργία Excel HLOOKUP.
- Excel INDIRECT συνάρτηση.
- Λειτουργία MATCH του Excel.
- Λειτουργία Excel OFFSET.
Μπορεί επίσης να σας αρέσουν τα ακόλουθα σεμινάρια Excel:
- VLOOKUP Vs. INDEX/MATCH
- Αντιστοιχία ευρετηρίου Excel
- Αναζήτηση και επιστροφή τιμών σε ολόκληρη σειρά/στήλη.
