Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Excel INDIRECT συνάρτηση στο Excel για να δημιουργήσετε μια αναφορά κελιού από κείμενο.
ΕΜΜΕΣΗ Επισκόπηση συνάρτησης
Η συνάρτηση INDIRECT Δημιουργεί μια αναφορά κελιού από μια συμβολοσειρά κειμένου.
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
ΕΜΜΕΣΗ λειτουργία Σύνταξη και είσοδοι:
| 1 | = INDIRECT (ref_text, C1) |
ref_text - Μια συμβολοσειρά που αντιπροσωπεύει αναφορά κελιού ή αναφορά εύρους. Η συμβολοσειρά μπορεί να είναι σε μορφή R1C1 ή A1 ή μπορεί να είναι μια ονομαστική περιοχή.
Α'1 - ΠΡΟΑΙΡΕΤΙΚΟ: Υποδεικνύει εάν η αναφορά είναι σε μορφή R1C1 ή A1. ΛΑΘΟΣ για R1C1 ή TRUE / Παραλείπεται για Α1.
Τι είναι η ΕΜΜΕΣΗ συνάρτηση;
Η συνάρτηση INDIRECT σάς επιτρέπει να δώσετε μια συμβολοσειρά κειμένου και να ζητήσετε από τον υπολογιστή να ερμηνεύσει αυτήν τη συμβολοσειρά ως πραγματική αναφορά. Αυτό μπορεί να χρησιμοποιηθεί για την αναφορά ενός εύρους στο ίδιο φύλλο, διαφορετικό φύλλο ή ακόμη και διαφορετικό βιβλίο εργασίας.
ΠΡΟΣΟΧΗ: Η ΕΜΜΕΣΗ συνάρτηση είναι μία από τις πτητικές λειτουργίες. Τις περισσότερες φορές όταν εργάζεστε στο υπολογιστικό φύλλο σας, ο υπολογιστής θα υπολογίσει εκ νέου έναν τύπο μόνο εάν οι εισαγωγές έχουν αλλάξει τις τιμές τους. Μια πτητική συνάρτηση, ωστόσο, υπολογίζει εκ νέου κάθε ώρα να κάνετε μια αλλαγή σε οποιοδήποτε κελί. Πρέπει να χρησιμοποιείται προσοχή για να διασφαλιστεί ότι δεν προκαλείτε μεγάλο χρόνο επανυπολογισμού λόγω υπερβολικής χρήσης πτητικής λειτουργίας ή λόγω πολλών κυττάρων που εξαρτώνται από το αποτέλεσμα μιας πτητικής λειτουργίας.
Δημιουργήστε μια αναφορά κελιού
Πείτε ότι θέλετε να λάβετε την τιμή από το A2, αλλά θέλετε να βεβαιωθείτε ότι ο τύπος σας στήριγμα στο Α2 ανεξάρτητα από τις νέες γραμμές που εισάγονται/αφαιρούνται. Θα μπορούσατε να γράψετε έναν τύπο του
| 1 | = INDIRECT ("A2") |
Σημειώστε ότι το όρισμα μέσα στη συνάρτηση μας είναι η συμβολοσειρά κειμένου "A2" και όχι μια αναφορά κελιού. Επίσης, δεδομένου ότι πρόκειται για συμβολοσειρά κειμένου, δεν χρειάζεται να υποδείξετε μια απόλυτη αναφορά όπως $ A $ 2. Το κείμενο δεν θα αλλάξει ποτέ, και έτσι αυτός ο τύπος θα δείχνει πάντα το Α2, όπου κι αν μετακινηθεί.
ΑΡΙΣΤΟΣ αριθμός σειράς
Μπορείτε να συνδέσετε συμβολοσειρές κειμένου και τιμές από κελιά μαζί. Αντί να γράφουμε "A2" όπως κάναμε προηγουμένως, μπορούμε να πάρουμε μια αριθμητική τιμή από το κελί B2 και να τη χρησιμοποιήσουμε στον τύπο μας. Θα γράφαμε τον τύπο όπως
| 1 | = INDIRECT ("A" & B2) |
Το σύμβολο "&" χρησιμοποιείται εδώ για να συνδέσει τη συμβολοσειρά κειμένου "A" με την τιμή από το κελί B2. Έτσι, εάν η τιμή του Β2 ήταν αυτή τη στιγμή 10, τότε ο τύπος μας θα το έλεγε ως
| 123 | = INDIRECT ("A" & 10)= INDIRECT ("A10")= Α10 |
ΑΜΕΣΗ τιμή στήλης
Μπορείτε επίσης να συνδέσετε στην αναφορά στήλης. Αυτή τη φορά ας πούμε ότι γνωρίζουμε ότι θέλουμε να αρπάξουμε μια τιμή από τη σειρά 10, αλλά θέλουμε να μπορούμε να αλλάξουμε από ποια στήλη θα τραβήξουμε. Θα τοποθετήσουμε το γράμμα στήλης που θέλουμε στο κελί Β2. Ο τύπος μας θα μπορούσε να μοιάζει
| 1 | = INDIRECT (B2 & "10") |
Εάν η τιμή του Β2 είναι "G", τότε ο τύπος μας αξιολογείται έτσι
| 123 | = INDIRECT ("G" & 10)= INDIRECT ("G10")= G10 |
ΕΜΠΟΡΙΚΟ στυλ r1c1
Στο προηγούμενο παράδειγμα, έπρεπε να χρησιμοποιήσουμε ένα γράμμα για να υποδείξουμε την αναφορά στήλης. Αυτό συμβαίνει επειδή χρησιμοποιούσαμε αυτό που είναι γνωστό ως αναφορά στυλ Α1. Σε στυλ Α1, οι στήλες δίνονται με ένα γράμμα και οι σειρές δίνονται με αριθμούς. Οι απόλυτες αναφορές υποδεικνύονται χρησιμοποιώντας το "$" πριν το στοιχείο που θέλουμε να παραμείνει απόλυτο.
Στο r1c1, τόσο οι γραμμές όσο και οι στήλες ξεκινούν χρησιμοποιώντας τον αριθμό. Η απόλυτη αναφορά στο a1 θα γράφεται ως
| 1 | = R1C1 |
Μπορείτε να το διαβάσετε ως "Σειρά 1, Στήλη 1". Οι σχετικές αναφορές δίνονται χρησιμοποιώντας αγκύλες, αλλά ο αριθμός υποδεικνύει τη θέση σε σχέση με το κύτταρο με τύποΤο Έτσι, αν γράφαμε έναν τύπο στο κελί Α10 και πρέπει να αναφερθούμε στο Α1, θα γράφαμε τον τύπο
| 1 | = R [-9] C |
Μπορείτε να το διαβάσετε ως «Το κελί 9 έχει σειρά, αλλά στην ίδια στήλη.
Ο λόγος που αυτό μπορεί να είναι χρήσιμο είναι ότι το INDIRECT μπορεί να υποστηρίξει τη χρήση της σημειογραφίας r1c1. Εξετάστε το προηγούμενο παράδειγμα όπου λάβαμε μια τιμή από τη σειρά 10 αλλά θέλαμε να μπορούμε να αλλάξουμε τη στήλη. Αντί να δώσουμε ένα γράμμα, ας πούμε ότι βάζουμε έναν αριθμό στο κελί Β2. Ο τύπος μας τότε μπορεί να μοιάζει
| 1 | = INDIRECT ("R10C" & B2, FALSE) |
Παραλείψαμε το 2nd επιχειρηματολογία μέχρι τώρα. Εάν αυτό το όρισμα παραλειφθεί ή True, η συνάρτηση θα αξιολογηθεί χρησιμοποιώντας το στυλ A1. Δεδομένου ότι είναι ψευδές, θα αξιολογηθεί στο r1c1. Ας υποθέσουμε ότι η τιμή του Β2 είναι 5. Ο τύπος μας θα το αξιολογήσει έτσι
| 12 | = INDIRECT ("R10C5", FALSE)= $ E $ 10 |
ΑΜΕΣΕΣ διαφορές με A1 έναντι r1c1
Θυμηθείτε ότι προηγουμένως δείξαμε ότι επειδή το περιεχόμενο αυτού του τύπου ήταν μια συμβολοσειρά κειμένου, δεν άλλαξε ποτέ;
| 1 | = INDIRECT ("A2") |
Αυτός ο τύπος θα κοιτάζει πάντα στο κελί Α2, ανεξάρτητα από το πού μετακινείτε τον τύπο. Στο r1c1, δεδομένου ότι μπορείτε να υποδείξετε σχετική θέση χρησιμοποιώντας αγκύλες, αυτός ο κανόνας δεν παραμένει συνεπής. Εάν τοποθετήσετε αυτόν τον τύπο στο κελί Β2
| 1 | = INDIRECT ("RC [-1]") |
Θα κοιτάζει το κελί Α2 (αφού η στήλη Α είναι μία στα αριστερά της στήλης Β). Αν αντιγράψετε αυτόν τον τύπο στο κελί Β3, το κείμενο μέσα θα παραμείνει το ίδιο, αλλά το ΕΜΠΟΡΙΚΟ θα εξετάζει τώρα το κελί Α3.
ΑΜΕΣΗ με το όνομα του φύλλου
Μπορείτε επίσης να συνδυάσετε ένα όνομα φύλλου στις ΕΠΕΙΘΟΝΕΣ αναφορές σας. Ένας σημαντικός κανόνας που πρέπει να θυμάστε είναι ότι πρέπει να τοποθετήσετε μεμονωμένα εισαγωγικά γύρω από τα ονόματα και πρέπει να διαχωρίσετε το όνομα του φύλλου από την αναφορά κελιού με ένα θαυμαστικό.
Ας υποθέσουμε ότι είχαμε αυτήν τη ρύθμιση, όπου δηλώνουμε το όνομα, τη σειρά και τη στήλη του φύλλου μας.
Ο τύπος μας για να συνδυάσουμε όλα αυτά σε μια αναφορά θα μοιάζει με αυτό:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Ο τύπος μας στη συνέχεια θα αξιολογηθεί ως εξής:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" Φύλλο2'! B5 ")= 'Φύλλο2'! Β5 |
Τεχνικά, δεδομένου ότι η λέξη "Sheet2" δεν έχει κενά μέσα, δεν έχουμε χρειάζομαι τα ενιαία εισαγωγικά. Είναι απόλυτα έγκυρο να γράφεις κάτι σαν
| 1 | = Φύλλο2! Α2 |
Ωστόσο, δεν βλάπτει να τοποθετήσετε τα εισαγωγικά όταν δεν τα χρειάζεστε. Είναι καλύτερη πρακτική να τα συμπεριλάβετε έτσι ώστε ο τύπος σας να μπορεί να χειριστεί την περίπτωση όπου μπορεί να χρειαστούν.
ΑΜΕΣΑ σε άλλο βιβλίο εργασίας
Θα αναφέρουμε επίσης ότι το INDIRECT μπορεί να δημιουργήσει μια αναφορά σε ένα διαφορετικό βιβλίο εργασίας. Ο περιορισμός είναι ότι το INDIRECT δεν θα ανακτήσει τιμές από ένα κλειστό βιβλίο εργασίας, επομένως η συγκεκριμένη χρήση έχει περιορισμένη πρακτικότητα. Εάν το βιβλίο εργασίας που δείχνει το INDIRECT δεν έχει ανοίξει, η λειτουργία θα εμφανίσει ένα "#REF!" λάθος.
Η σύνταξη κατά τη συγγραφή του ονόματος του βιβλίου εργασίας είναι ότι πρέπει να είναι σε αγκύλες. Ας χρησιμοποιήσουμε αυτήν τη ρύθμιση και ας προσπαθήσουμε να πάρουμε μια τιμή από το κελί C7.
Η φόρμουλα μας θα ήταν
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Και πάλι, δώστε προσοχή στην τοποθέτηση των μεμονωμένων εισαγωγικών, των παρενθέσεων και του θαυμαστικού. Ο τύπος μας στη συνέχεια θα αξιολογηθεί ως εξής:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Summary" & "'! C7")= INDIRECT ("'[Sample.xslx] Περίληψη'! C7")= '[Sample.xlsx] Περίληψη'! C7 |
ΑΜΕΣΑ για τη δημιουργία δυναμικού εύρους
Όταν έχετε ένα μεγάλο σύνολο δεδομένων, είναι σημαντικό να προσπαθήσετε και να βελτιστοποιήσετε τους τύπους έτσι ώστε να μην κάνουν περισσότερη δουλειά από ό, τι χρειάζεται. Για παράδειγμα, αντί να αναφέρουμε όλη τη στήλη Α, ίσως θελήσουμε να αναφέρουμε απλώς τον ακριβή αριθμό κελιών στη λίστα μας. Εξετάστε την ακόλουθη διάταξη:
Στο κελί Β2, τοποθετήσαμε τον τύπο
| 1 | = COUNTA (A: A) |
Η συνάρτηση COUNTA είναι πολύ εύκολο για τον υπολογιστή να υπολογίσει, καθώς απλώς ελέγχει πόσα κελιά στο col A έχουν κάποια τιμή, σε αντίθεση με το να χρειάζεται να κάνει λογικούς ελέγχους ή μαθηματικές πράξεις.
Τώρα, ας χτίσουμε τον τύπο μας που θα αθροίζει τις τιμές στη στήλη Α, αλλά θέλουμε να βεβαιωθούμε ότι εξετάζει μόνο το ακριβές εύρος με τιμές (A2: A5). Θα γράψουμε τον τύπο μας ως
| 1 | = SUM (INDIRECT ("A2: A" & B2)) |
Το INDIRECT θα αρπάξει τον αριθμό 5 από το κελί B2 και θα δημιουργήσει μια αναφορά στο εύρος A2: A5. Το SUM μπορεί στη συνέχεια να χρησιμοποιήσει αυτό το εύρος για τον υπολογισμό του. Εάν προσθέσουμε μια άλλη τιμή στο κελί A6, τότε ο αριθμός στο B2 θα ενημερωθεί και ο τύπος SUM θα ενημερωθεί επίσης αυτόματα για να συμπεριλάβει αυτήν τη νέα τιμή.
ΠΡΟΣΟΧΗ: Με την εισαγωγή των Πίνακες στο Office 2007, είναι πολύ πιο αποτελεσματικό να αποθηκεύσετε τα δεδομένα σας σε έναν πίνακα και να χρησιμοποιήσετε μια δομική αναφορά αντί να δημιουργήσετε τον τύπο που χρησιμοποιήσαμε σε αυτό το παράδειγμα λόγω της ασταθούς φύσης του INDIRECT. Ωστόσο, μπορεί να είναι περιπτώσεις όπου πρέπει να δημιουργήσετε μια λίστα στοιχείων και δεν μπορείτε να χρησιμοποιήσετε έναν πίνακα.
Δυναμική χάραξη με ΕΜΠΡΟΣΩΠΗ
Ας πάρουμε το προηγούμενο παράδειγμα και κάνουμε ένα ακόμη βήμα. Αντί να γράψουμε έναν τύπο για να μας δώσουμε το άθροισμα των τιμών, θα δημιουργήσουμε μια περιοχή με όνομα. Θα μπορούσαμε να ονομάσουμε αυτό το εύρος "MyData" και να το αναφέρουμε
| 1 | = INDIRECT ("A2: A" & COUNTA ($ A: $ A)) |
Σημειώστε ότι δεδομένου ότι το βάζουμε σε μια περιοχή με όνομα, αλλάξαμε την αναφορά στο B2 και αντίθετα τοποθετήσαμε τη συνάρτηση COUNTA εκεί απευθείας.
Τώρα που έχουμε αυτό το ονομαστικό εύρος, θα μπορούσαμε να το χρησιμοποιήσουμε σε ένα γράφημα. Θα δημιουργήσουμε ένα γράφημα κενής γραμμής και στη συνέχεια θα προσθέσουμε μια σειρά δεδομένων. Για τις τιμές της σειράς, θα μπορούσατε να γράψετε κάτι σαν
| 1 | = Sheet1! MyData |
Το γράφημα θα χρησιμοποιεί τώρα αυτήν την αναφορά σε τιμές γραφήματος. Καθώς προστίθενται περισσότερες τιμές στη στήλη Α, το INDIRECT θα αναφέρεται σε όλο και μεγαλύτερο εύρος και το γράφημα μας θα συνεχίσει να ενημερώνεται με όλες τις τιμές που προστέθηκαν πρόσφατα.
Επαλήθευση δυναμικών δεδομένων με το INDIRECT
Κατά τη συλλογή εισροών από χρήστες, μερικές φορές υπάρχει ανάγκη να κάνετε τις επιλογές μιας επιλογής ανάλογα με την προηγούμενη επιλογή. Εξετάστε αυτήν τη διάταξη, όπου η πρώτη στήλη μας επιτρέπει στον χρήστη να επιλέξει μεταξύ φρούτων, λαχανικών και κρέατος.
Στο 2nd στήλη, δεν θέλουμε να έχουμε μια μεγάλη λίστα με κάθε πιθανή επιλογή, καθώς έχουμε ήδη περιορίσει λίγο τα πράγματα. Έτσι, δημιουργήσαμε 3 άλλες λίστες που μοιάζουν με αυτήν:
Στη συνέχεια, θα αναθέσουμε το καθένα από αυτά αυτά τα λίστες σε μια περιοχή με όνομα Δηλαδή, όλα τα φρούτα θα βρίσκονται σε μια σειρά που ονομάζεται "Φρούτα" και λαχανικά σε "Λαχανικά" κ.λπ.
Πίσω στον πίνακά μας, είμαστε έτοιμοι να ρυθμίσουμε την επικύρωση δεδομένων στο 2nd στήλη. Θα δημιουργήσουμε μια επικύρωση τύπου λίστας, με μια εισαγωγή:
| 1 | = ΕΜΠΡΟΣ (Α2) |
Το INDIRECT πρόκειται να διαβάσει στην επιλογή που έγινε στη στήλη Α και να δει το όνομα μιας κατηγορίας. Έχουμε ορίσει εύρη με αυτά τα ονόματα, οπότε το INDIRECT θα πάρει στη συνέχεια αυτό το όνομα και θα δημιουργήσει μια αναφορά στο επιθυμητό εύρος.
επιπρόσθετες σημειώσεις
Χρησιμοποιήστε τη συνάρτηση INDIRECT για να δημιουργήσετε μια αναφορά κελιού από κείμενο.
Πρώτα δημιουργήστε τη συμβολοσειρά κειμένου που αντιπροσωπεύει μια αναφορά κελιού. Η συμβολοσειρά πρέπει είτε να είναι στο συνηθισμένο γράμμα & αριθμό γραμμής στήλης τύπου A1 (M37) είτε σε στιλ R1C1 (R37C13). Μπορείτε να πληκτρολογήσετε την αναφορά απευθείας, αλλά συνήθως θα αναφέρετε κελιά που καθορίζουν τις γραμμές και τις στήλες. Τέλος, εισαγάγετε ποια μορφή αναφοράς κελιού θα επιλέξετε. ΑΛΗΘΙΝΗ ή δεν επιτρέπεται για αναφορά στυλ A1 ή FALSE για στυλ R1C1.
Ενώ εργάζεστε με INDIRECT Formulas, μπορεί να θέλετε να χρησιμοποιήσετε το Λειτουργία ROW για να λάβετε τον αριθμό σειράς μιας αναφοράς ή του ΣΤΗΛΗ Λειτουργία για να λάβετε τον αριθμό στήλης (όχι γράμμα) μιας αναφοράς.
Επιστρέψτε στη λίστα όλων των λειτουργιών στο Excel
ΕΜΜΕΣΟΣ στα Υπολογιστικά φύλλα Google
Η ΕΜΜΕΣΗ συνάρτηση λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
