Φανταστείτε ότι έχετε πολλά πανομοιότυπα φύλλα σε ένα βιβλίο εργασίας που περιέχει πανομοιότυπους πίνακες (όπως τα αρχεία παρακολούθησης κάθε μήνα σε ξεχωριστό φύλλο). Τώρα θέλετε να δημιουργήσετε έναν πίνακα ελέγχου που σας δείχνει τη συνολική συμμετοχή για το μήνα. Το να έχετε ταυτόχρονα τα δεδομένα κάθε μήνα στο ταμπλό δεν είναι καλή επιλογή. Θέλουμε ένα αναπτυσσόμενο μενού για να επιλέξετε τον μήνα. Χρειαζόμαστε έναν τύπο VLOOKUP για να δούμε από το φύλλο του επιλεγμένου μήνα.
Με απλά λόγια, χρειαζόμαστε έναν τύπο αναζήτησης για να αναζητήσουμε από μεταβλητά φύλλα.
Όπως δείχνει το gif παραπάνω, θα χρησιμοποιούμε τη λειτουργία VLOOKUP και INDIRECT μαζί για να αναζητήσουμε πολλά φύλλα, με βάση τα ονόματά τους.
Γενικός τύπος για την αναζήτηση πολλών φύλλων
| =VLOOKUP(τιμή_αναζήτησης,ΕΜΜΕΣΟΣ(""&όνομα_φυλλου_αναφορά&"! πίνακας_αναζήτησης "), col_index, 0) |
Lookup_value: Αυτή είναι η τιμή που αναζητάτε στην αναζήτηση τραπέζι.
Αναφορά φύλλου_αναφοράς: Αυτή είναι η αναφορά του κελιού που περιέχει το όνομα του φύλλου.
Πίνακας αναζήτησης: Αυτή είναι η αναφορά του πίνακα στην οποία θέλετε να αναζητήσετε αναζήτηση_αξίας. Μπορεί να είναι μια ονομαστική περιοχή, πίνακας ή απόλυτη αναφορά. Θα πρέπει να είναι το ίδιο σε όλα τα φύλλα.
Col_Index: Αυτός είναι ο αριθμός στήλης στον πίνακα από τον οποίο θέλετε να ανακτήσετε την τιμή. Εάν είστε εξοικειωμένοι με τη λειτουργία VLOOKUP, γνωρίζετε τι είναι.
Ας πάρουμε λοιπόν ένα παράδειγμα.
Παράδειγμα: Ανάκτηση συμμετοχής για επιλεγμένο μήνα
Έχουμε λοιπόν ένα βιβλίο εργασίας που διατηρεί τη συμμετοχή των μαθητών μου στο Excel. Τα δεδομένα κάθε μήνα αποθηκεύονται ξεχωριστά σε διαφορετικά φύλλα. Το όνομα του φύλλου ορίζεται στο όνομα των μηνών. Προς το παρόν έχω δεδομένα τριών μηνών, αλλά θα υπάρχουν περισσότερα φυσικά.
Θέλω να δημιουργήσω μια αναφορά που να δείχνει τη συμμετοχή του επιλεγμένου μήνα. Ο μήνας μπορεί να επιλεγεί από μια αναπτυσσόμενη λίστα. Ο τύπος θα πρέπει να μπορεί να κοιτάζει αυτόματα από αυτό το φύλλο, ακόμη και αν προστίθεται ένα νέο φύλλο σε αυτό.
Έτσι, ετοιμάζουμε τον παραπάνω πίνακα. Το Cell G3 δημιουργήσαμε μια αναπτυσσόμενη λίστα χρησιμοποιώντας μια αναπτυσσόμενη λίστα.
Η τιμή αναζήτησης είναι in Β4Το Το sheet_name_reference είναι μέσα G3Το Ο πίνακας αναζήτησης σε όλα τα φύλλα είναι B3: AZ100. Θα θέλαμε να ανακτήσουμε τιμή από 2 στήλες. Γράφουμε λοιπόν αυτόν τον τύπο στο C4 και τον σύρουμε προς τα κάτω. Ομοίως, για τιμές που απουσιάζουν αλλάζουμε τον δείκτη στήλης.
| =VLOOKUP(Β4,ΕΜΜΕΣΟΣ("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Πώς λειτουργεί;
Ο τύπος λύνεται από μέσα προς τα έξω. Αρχικά ας δούμε πώς λύνεται βήμα προς βήμα.
Αν υποθέσουμε ότι το G3 περιέχει Ιαν.
| =VLOOKUP(Β4,ΕΜΜΕΣΟΣ("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VLOOKUP (B4, INDIRECT ("Jan! $ B $ 3: $ Az $ 100"),2,0) |
| = VLOOKUP (Β4,Jan! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Έτσι, πρώτα η δήλωση "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" επιλύεται σε μια συμβολοσειρά "Jan! $ B $ 3: $ Az $ 100". Στη συνέχεια, η συνάρτηση INDIRECT μετατρέπει αυτήν τη συμβολοσειρά σε πραγματική αναφορά. Και τελικά πήραμε τον τύπο VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). Και αυτό επιλύεται τελικά στο 7. Τώρα, αν αλλάξετε το όνομα του φύλλου στο G3, το κείμενο αναφοράς τιμής θα αλλάξει. Και με αυτόν τον τρόπο βλέπετε από μεταβλητά φύλλα στο Excel.
Ελπίζω ότι αυτό σας βοήθησε. Εάν έχετε οποιεσδήποτε ερωτήσεις ή έχετε διαφορετική αναζήτηση για εκτέλεση, ενημερώστε με στην παρακάτω ενότητα σχολίων. Θα χαρώ να σας βοηθήσω. Μέχρι τότε συνεχίστε το Excelling.
Χρησιμοποιήστε το VLOOKUP από δύο ή περισσότερους πίνακες αναζήτησης | Για αναζήτηση από πολλούς πίνακες μπορούμε να ακολουθήσουμε μια προσέγγιση IFERROR. Η αναζήτηση από πολλούς πίνακες λαμβάνει το σφάλμα ως διακόπτη για τον επόμενο πίνακα. Μια άλλη μέθοδος μπορεί να είναι η προσέγγιση If.
Πώς να κάνετε Case Sensitive Lookup στο Excel | η λειτουργία VLOOKUP του excel δεν είναι διάκριση πεζών -κεφαλαίων και θα επιστρέψει την πρώτη αντιστοιχισμένη τιμή από τη λίστα. Το INDEX-MATCH δεν αποτελεί εξαίρεση, αλλά μπορεί να τροποποιηθεί ώστε να είναι κεφαλαίο. Ας δούμε πώς…
Αναζήτηση που εμφανίζεται συχνά κείμενο με κριτήρια στο Excel | Η αναζήτηση εμφανίζεται συχνότερα σε κείμενο σε μια περιοχή που χρησιμοποιούμε τη λειτουργία INDEX-MATCH με τη λειτουργία MODE. Εδώ είναι η μέθοδος.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση Excel VLOOKUP| Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση αξίας από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε το Excel Λειτουργία COUNTIF| Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.