Χρησιμοποιήστε INDEX και MATCH για απλά ερωτήματα βάσης δεδομένων στο Excel

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

Οι VLOOKUP (κάθετες) και HLOOKUP (οριζόντιες) ήταν οι μόνες διαθέσιμες λειτουργίες για την αναζήτηση μιας βάσης δεδομένων για συγκεκριμένες πληροφορίες. Για παράδειγμα, μπορείτε να αναζητήσετε και να εξαγάγετε όλες τις εγγραφές που περιείχαν πωλήσεις μεγαλύτερες από $ 1000 αλλά λιγότερες από $ 5000, αλλά μόνο σε επίπεδη αρχεία (μόνο μία μήτρα βάσης δεδομένων).

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

[Περαιτέρω ανάγνωση: Το νέο σας PC χρειάζεται αυτά τα 15 δωρεάν, εξαιρετικά προγράμματα]

Η συνάρτηση MATCH επιστρέφει μια θέση από μια συγκεκριμένη θέση (σε μια λίστα, πίνακα, βάση δεδομένων)

Στο Excel, η λειτουργία INDEX επιστρέφει τη θέση μιας τιμής (σε μια λίστα , πίνακας, βάση δεδομένων). Και οι συναρτήσεις INDEX-MATCH που χρησιμοποιούνται μαζί κάνουν την εξαγωγή δεδομένων από έναν πίνακα μια αύρα

. Η σύνταξη για τη συνάρτηση INDEX είναι: INDEX (array, row_num, [column_num]). Η συστοιχία είναι το εύρος των κελιών με τα οποία εργάζεστε. Το Row_num είναι, προφανώς, ο αριθμός σειράς στην περιοχή που περιέχει τα δεδομένα που ψάχνετε. Column_num είναι ο αριθμός στήλης στην περιοχή που περιέχει τα δεδομένα που αναζητάτε. Ο τύπος INDEX δεν αναγνωρίζει τα γράμματα στηλών, οπότε πρέπει να χρησιμοποιήσετε τους αριθμούς (μετρώντας από τα αριστερά).

Η σύνταξη για τη συνάρτηση MATCH είναι: MATCH (lookup_value, lookup_array, [match_type]). Το lookup_value είναι ο αριθμός ή το κείμενο που αναζητάτε, το οποίο μπορεί να είναι μια τιμή, μια λογική τιμή ή μια αναφορά κελί. Το lookup_array είναι το φάσμα των κελιών στα οποία εργάζεστε. Το match_type καθορίζει τη συνάρτηση MATCH - δηλαδή μια ακριβή αντιστοιχία ή τον πλησιέστερο αγώνα.

A. Λειτουργία INDEX

Στο παράδειγμά μας, ο διάσημος Commodore James Norrington έχει ένα υπολογιστικό φύλλο που παρακολουθεί όλα τα πειρατικά πλοία στην Καραϊβική. Ο κατάλογος του Norrington οργανώνεται από τους μαχητικούς σχηματισμούς των πλοίων, που ταιριάζουν με τους ναυτικούς χάρτες της περιοχής. Όταν βλέπει ένα πλοίο να προχωράει, εισέρχεται στη φόρμα του Δείκτη στο υπολογιστικό φύλλο του, έτσι ώστε να μπορεί να αναγνωρίσει το πλοίο και τις ικανότητές του. Σε αυτό το πρώτο ερώτημα, η Norrington θέλει να γνωρίζει τον τύπο του πλοίου που προχωράει.

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

2. Εισαγάγετε τη συνάρτηση INDEX (που προηγείται από την ένδειξη ισοτιμίας), συν μια ανοιχτή παρένθεση και, στη συνέχεια, επισημάνετε (ή πληκτρολογήστε) την περιοχή βάσης δεδομένων / πίνακα όπως παρακάτω: = INDEX (A2: I16

, στην περίπτωση αυτή, σημαίνει σκληρή κωδικοποίηση του τύπου, οπότε όταν / αν αντιγραφεί, το εύρος δεν είναι αλλοιωμένο), πιέστε το F4 μία φορά μετά από κάθε αναφορά κελί. , και προσθέστε τα πλήρη απόλυτα σύμβολα αναφοράς

3. Στη συνέχεια, εισάγετε ένα κόμμα για να διαχωρίσετε τα επιχειρήματα (δηλαδή, τα ξεχωριστά κομμάτια του τύπου) και στη συνέχεια πληκτρολογήστε τον αριθμό σειράς και ένα κόμμα, ακολουθούμενο από τον αριθμό της στήλης , πρέπει να είναι ένας αριθμός και όχι το συνηθισμένο γράφημα στήλης) και η σωστή παρένθεση (ή απλά πατήστε το πλήκτρο Enter και αφήστε το Excel να προσθέσει την τελική παρένθεση για εσάς.) Ο πλήρης τύπος μοιάζει με αυτόν: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

Σημείωση: Η αρίθμηση σειράς ξεκινάει με τον πρώτο αριθμό στο εύρος, όχι τον πρώτο αριθμό στο υπολογιστικό φύλλο. n αν και το πειρατικό πλοίο της Cavalleria βρίσκεται στην γραμμή 16 του Excel, είναι στην πραγματικότητα η σειρά 15 στη φόρμουλα μας, επειδή η σειρά μας ξεκινάει από το A2 και περνάει από το I16. Αν το A2 είναι σειρά 1, τότε το A16 είναι η σειρά 15).

4. Σημειώστε ότι ο τύπος του πλοίου Norrington που ψάχνετε είναι War Sloop.

JD Sartain

Χρησιμοποιήστε τη λειτουργία ευρετηρίου για να εντοπίσετε συγκεκριμένες πληροφορίες στη βάση δεδομένων σας. INDEX κυμαίνεται

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

1. Μεταβείτε στο A2 και επισημάνετε την περιοχή A2 έως I16.

2. Από την καρτέλα Φόρμες, επιλέξτε

Ορισμός ονόματος από την ομάδα Ορισμένα ονόματα 3. Στο παράθυρο διαλόγου popup, πληκτρολογήστε ένα όνομα για το εύρος σας στο πεδίο Όνομα πεδίου

4. Στη συνέχεια, εισαγάγετε το πεδίο (όπου βρίσκεται το εύρος), το οποίο είναι είτε το βιβλίο εργασίας είτε ένα από τα φύλλα εργασίας του βιβλίου εργασίας

5. Εισάγετε ένα σχόλιο, αν είναι απαραίτητο.

6. Τέλος, επαληθεύστε ότι το πεδίο Αναφορά σε εμφανίζει το σωστό όνομα και εύρος και στη συνέχεια κάντε κλικ στο κουμπί

OK . 7. Αν θέλετε να επαληθεύσετε ότι το εύρος σας αποθηκεύεται στο Excel, δοκιμάστε αυτό το μικρό τεστ: Πατήστε Ctrl + G (εντολή GoTo). Επιλέξτε

Πλοία στο παράθυρο διαλόγου GoTo και στη συνέχεια κάντε κλικ στο κουμπί OK και το Excel εκ νέου τονίζει το εύρος A2: I16. Πώς να ορίσετε και να αποθηκεύσετε ένα εύρος

C. INDEX με τύπους SUM & AVERAGE

Η Norrington αξιολογεί τις ικανότητες μάχης του στόλου. Αρχικά θέλει να μάθει πόσα κανόνια έχουν οι πειρατές, ο μέσος αριθμός κανόνιων ανά πλοίο και ο συνολικός αριθμός του πληρώματος που απασχολεί όλα αυτά τα πειρατικά πλοία. Εισάγει τους ακόλουθους τύπους:

1. = SUM (INDEX (Πλοία, 8)) ισούται με 334, ο συνολικός αριθμός των κανόνι και

2. = ΜΕΣΟ (INDEX (Πλοία, 8)) ισούται με 22,27 ή περίπου 22,27 κανόνια ανά πλοίο.

3. = SUM (INDEX (Πλοία, 7)) ισούται με 2350, ο συνολικός αριθμός όλων των πληρωμάτων σε όλα τα πλοία.

Γιατί υπάρχει ένα κόμμα, κενό, κόμμα μεταξύ των πλοίων και ο αριθμός 8, και τι σημαίνουν αυτοί οι αριθμοί; Τα πλοία είναι η Περιοχή (ακολουθούμενη από κόμμα), το όρισμα Row είναι κενό (ή ένα κενό) επειδή η Norrington θέλει όλες τις σειρές και το 8 αντιπροσωπεύει την 8η στήλη πάνω (που είναι η στήλη H, Cannons). , γιατί όχι απλώς εισάγετε τους τύπους SUM και / ή AVERAGE στο κάτω μέρος αυτών των στηλών; Σε αυτό το μικροσκοπικό υπολογιστικό φύλλο, ναι, θα ήταν εξίσου εύκολο. Αλλά αν το υπολογιστικό φύλλο έχει 5000 σειρές και 300 στήλες, θα θέλετε να χρησιμοποιήσετε τους τύπους INDEX

JD Sartain

03 INDEX χρησιμοποιώντας SUM και AVERAGE.

Μόλις το εύρος ονομάζεται, η Norrington μπορεί να ανοίξει ένα κενό υπολογιστικό φύλλο αυτό το ίδιο βιβλίο εργασίας και να γράψετε τις ερωτήσεις του στη στήλη Β (που δείχνουν τα αποτελέσματα αντί των τύπων) με μια περιγραφή που καθορίζει αυτά τα ερωτήματα στη στήλη Α. (Σημείωση: Η στήλη C δείχνει τους πραγματικούς τύπους που υπάρχουν στη στήλη Β).

Δεν χρειάζεται να δει οπτικά την τεράστια βάση δεδομένων του με 5000 εγγραφές ή να περιμένει για μερικά δευτερόλεπτα, ενώ οι τύποι υπολογίζουν. Μπορεί να πάρει όλες τις πληροφορίες που χρειάζεται από το φύλλο ερωτημάτων του. Θυμηθείτε, όσο μεγαλύτερο είναι το υπολογιστικό φύλλο, τόσο πιο αργή λειτουργεί, ειδικά αν υπάρχουν πολλοί τύποι.

JD Sartain

04 Πειρατικό πλοίο Commodore James Norrington.

D. INDEX MATCH με MAX

Τώρα, ο Norrington θέλει να μάθει πόσοι πειρατές βρίσκονται στο πιο πυκνοκατοικημένο πλοίο και ποιο είναι το πλοίο; Χρησιμοποιεί το INDEX με τον τύπο MAX για να πάρει τον μεγαλύτερο αριθμό πειρατών, αλλά πρέπει επίσης να γνωρίζει ποιο πλοίο τις μεταφέρει. Έτσι χρησιμοποιεί τον τύπο INDEX / MATCH με MAX για να διαπιστώσει ποιο πλοίο έχει τους περισσότερους πειρατές στο πλοίο.

1. = MAX (INDEX (Πλοία, 7)) ισούται με 300, ο μεγαλύτερος αριθμός πειρατών σε ένα από τα πλοία

2. = INDEX ($ A $ 2: $ A $ 16, MATCH (MAX (Ship), $ G $ 2: G $ 16, 0)) ισούται με το Royal James, το πλοίο με τους περισσότερους πειρατές στο

3. = ΔΕΙΚΤΗΣ ($ F $ 2: $ F $ 16, MATCH (MAX (Πλοία), $ G $ 2: G $ 16, 0)) ισούται με Stede Bonnet, Captain του Royal James με πειρατικό πλήρωμα 300

JD Sartain < > Χρησιμοποιήστε το INDEX-MATCH και το MAX για να ανακτήσετε συγκεκριμένες πληροφορίες από τη βάση δεδομένων σας