Αυτό το σεμινάριο θα σας διδάξει πώς να χρησιμοποιείτε τον συνδυασμό INDEX & MATCH για την εκτέλεση αναζητήσεων σε Excel και Φύλλα Google.
INDEX & MATCH, The Perfect Pair
Ας ρίξουμε μια πιο προσεκτική ματιά σε μερικούς τρόπους με τους οποίους μπορείτε να συνδυάσετε τις συναρτήσεις INDEX και MATCH. Η συνάρτηση MATCH έχει σχεδιαστεί για να επιστρέφει τη σχετική θέση ενός στοιχείου μέσα σε έναν πίνακα, ενώ η συνάρτηση INDEX μπορεί να ανακτήσει ένα στοιχείο από έναν πίνακα με συγκεκριμένη θέση. Αυτή η συνέργεια μεταξύ των δύο τους επιτρέπει να εκτελούν σχεδόν κάθε τύπο αναζήτησης που μπορεί να χρειαστείτε.
Ο συνδυασμός INDEX / MATCH έχει χρησιμοποιηθεί ιστορικά ως αντικατάσταση της συνάρτησης VLOOKUP. Ένας από τους κύριους λόγους είναι η δυνατότητα να κάνετε αναζήτηση αριστερά (δείτε την επόμενη ενότητα).
Σημείωση: η νέα λειτουργία XLOOKUP μπορεί πλέον να πραγματοποιεί αναζητήσεις με αριστερή εμφάνιση.
Αναζήτηση προς τα αριστερά
Ας χρησιμοποιήσουμε αυτόν τον πίνακα στατιστικών μπάσκετ:
Θέλουμε να βρούμε το Bob's Player #. Επειδή το Player # βρίσκεται στα αριστερά της στήλης ονόματος, δεν μπορούμε να χρησιμοποιήσουμε ένα VLOOKUP.
Αντ 'αυτού, θα μπορούσαμε να κάνουμε ένα βασικό αίτημα MATCH για τον υπολογισμό της σειράς του Bob
= ΑΓΩΝΑΣ (H2, B2: B5, 0)
Αυτό θα αναζητήσει μια ακριβή αντιστοίχιση της λέξης "Bob" και έτσι η συνάρτηση μας θα επιστρέψει τον αριθμό 2, αφού το "Bob" είναι στο 2nd θέση.
Στη συνέχεια, μπορούμε να χρησιμοποιήσουμε τη συνάρτηση INDEX για να επιστρέψουμε το Player #, που αντιστοιχεί σε μια σειρά. Προς το παρόν, ας εισάγουμε χειροκίνητα το "2" στη συνάρτηση:
= INDEX (A2: A5, 2)
Εδώ, το INDEX θα αναφέρει το A3, αφού αυτό είναι το 2nd κελί εντός της περιοχής A2: A5 και επιστρέφουμε το αποτέλεσμα του 42. Για τον γενικό μας στόχο, μπορούμε στη συνέχεια να συνδυάσουμε αυτά τα δύο σε:
= INDEX (A2: A5, MATCH (H2, B2: B5, 0))
Το όφελος εδώ είναι ότι μπορέσαμε να επιστρέψουμε ένα αποτέλεσμα από μια στήλη στα αριστερά του τόπου όπου ψάχναμε.
Αναζήτηση δύο διαστάσεων
Ας δούμε το τραπέζι μας από πριν:
Αυτή τη φορά, ωστόσο, θέλουμε να φέρουμε ένα συγκεκριμένο στατιστικό. Έχουμε φωνάξει ότι θέλουμε να αναζητήσουμε Rebounds στο κελί Η1. Αντί να χρειαστεί να γράψετε πολλές προτάσεις IF για να καθορίσετε από ποια στήλη θα λάβετε το αποτέλεσμα, μπορείτε να χρησιμοποιήσετε ξανά μια συνάρτηση MATCH. Η συνάρτηση INDEX σάς επιτρέπει να καθορίσετε την τιμή της γραμμής και την τιμή της στήλης. Θα προσθέσουμε μια άλλη συνάρτηση MATCH εδώ για να καθορίσουμε ποια στήλη θέλουμε. Αυτό θα μοιάζει
= ΑΓΩΝΑΣ (H1, A1: E1, 0)
Το κελί μας στο Η1 είναι ένα αναπτυσσόμενο μενού που μας επιτρέπει να επιλέξουμε ποια κατηγορία θέλουμε να αναζητήσουμε και, στη συνέχεια, το MATCH μας καθορίζει σε ποια στήλη ανήκει ο πίνακας. Ας συνδέσουμε αυτό το νέο κομμάτι στον προηγούμενο τύπο μας. Σημειώστε ότι πρέπει να τροποποιήσουμε το πρώτο όρισμα σε δύο διαστάσεις, καθώς δεν θέλουμε πλέον ένα αποτέλεσμα από τη στήλη Α.
= INDEX (A2: E5, MATCH (H2, B2: B5, 0), MATCH (H1, A1: E1, 0))
Στο παράδειγμά μας, θέλουμε να βρούμε Rebounds για τον Charlie. Ο τύπος μας θα το αξιολογήσει ως εξής:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Δημιουργήσαμε τώρα μια ευέλικτη ρύθμιση που επιτρέπει στον χρήστη να ανακτήσει οποιαδήποτε τιμή θέλει από τον πίνακά μας χωρίς να χρειάζεται να γράψει πολλούς τύπους ή διακλαδισμένες προτάσεις IF.
Πολλαπλά τμήματα
Δεν χρησιμοποιείται συχνά, αλλά το INDEX έχει ένα πέμπτο επιχείρημα που μπορεί να δοθεί για να προσδιοριστεί ποιο περιοχή στο επιχείρημα ένα για χρήση. Αυτό σημαίνει ότι χρειαζόμαστε έναν τρόπο για να περάσουμε πολλές περιοχές στο πρώτο όρισμα. Μπορείτε να το κάνετε αυτό χρησιμοποιώντας ένα επιπλέον σύνολο παρενθέσεων. Αυτό το παράδειγμα θα δείξει πώς θα μπορούσατε να λάβετε αποτελέσματα από διαφορετικούς πίνακες σε ένα φύλλο εργασίας χρησιμοποιώντας το INDEX.
Εδώ είναι η διάταξη που θα χρησιμοποιήσουμε. Έχουμε στατιστικά για τρία διαφορετικά τέταρτα παιχνιδιού.
Στα κελιά H1: H3, δημιουργήσαμε τις αναπτυσσόμενες λίστες επικύρωσης δεδομένων για τις διάφορες επιλογές μας. Το αναπτυσσόμενο μενού για το Quarter προέρχεται από το J2: J4. Θα το χρησιμοποιήσουμε για μια άλλη δήλωση MATCH, για να καθορίσουμε ποια περιοχή θα χρησιμοποιηθεί. Ο τύπος μας στο Η4 θα μοιάζει με αυτόν:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0))
Έχουμε ήδη συζητήσει πώς λειτουργούν οι δύο εσωτερικές συναρτήσεις MATCH, οπότε ας επικεντρωθούμε στο πρώτο και το τελευταίο επιχείρημα:
= INDEX ((A3: E6, A10: E13, A17: E20),…, MATCH (H3, J2: J4, 0))
Δώσαμε στη συνάρτηση INDEX πολλούς πίνακες στο πρώτο όρισμα, περικλείοντάς τους όλους μέσα σε παρενθέσεις. Ο άλλος τρόπος με τον οποίο μπορείτε να το κάνετε αυτό είναι χρησιμοποιώντας Τύπους - Ορισμός ονόματος. Θα μπορούσατε να ορίσετε ένα όνομα που ονομάζεται "MyTables" με έναν ορισμό του
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Ας επιστρέψουμε σε ολόκληρη τη δήλωση. Οι διάφορες συναρτήσεις MATCH μας θα δείξουν στη συνάρτηση INDEX πού ακριβώς να αναζητήσουν. Αρχικά, θα καθορίσουμε ότι το "Charlie" είναι το 3rd σειρά. Στη συνέχεια, θέλουμε το "Rebounds", το οποίο είναι το 4ου στήλη. Τέλος, καθορίσαμε ότι θέλουμε το αποτέλεσμα από το 2nd τραπέζι. Ο τύπος θα αξιολογηθεί μέσω αυτού ως εξής:
= INDEX ((A3: E6, A10: E13, A17: E20), MATCH (H2, B3: B6, 0), MATCH (H1, A2: E2, 0), MATCH (H3, J2: J4, 0)) = INDEX ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = INDEX (A10: E13, 3, 4) = D13 = 14
Όπως αναφέραμε στην αρχή αυτού του παραδείγματος, περιορίζεστε να έχετε τους πίνακες στο ίδιο φύλλο εργασίας. Εάν μπορείτε να γράψετε σωστούς τρόπους για να πείτε στο INDEX σας από ποια σειρά, στήλη και/ή περιοχή θέλετε να ανακτήσετε δεδομένα, το INDEX θα σας εξυπηρετήσει πολύ καλά.
Υπολογιστικά φύλλα Google -INDEX & MATCH
Όλα τα παραπάνω παραδείγματα λειτουργούν ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel.
