Excel INDIRECT Function - Επισκόπηση
Η συνάρτηση INDIRECT στο Excel μπορεί να χρησιμοποιηθεί όταν έχετε την αναφορά ενός κελιού ή μιας περιοχής ως συμβολοσειρά κειμένου και θέλετε να λάβετε τις τιμές από αυτές τις αναφορές.
Εν ολίγοις - μπορείτε να χρησιμοποιήσετε τον έμμεσο τύπο για να επιστρέψτε την αναφορά που καθορίζεται από τη συμβολοσειρά κειμένου.
Σε αυτό το σεμινάριο του Excel, θα σας δείξω πώς να χρησιμοποιήσετε την έμμεση συνάρτηση στο Excel χρησιμοποιώντας μερικά πρακτικά παραδείγματα.
Αλλά πριν μπω στα παραδείγματα, ας ρίξουμε πρώτα μια ματιά στη σύνταξή του.
ΕΜΠΡΟΣ ΛΕΙΤΟΥΡΓΙΑ Σύνταξη
= INDIRECT (ref_text, [a1])
Ορίσματα εισόδου
- ref_text - Μια συμβολοσειρά κειμένου που περιέχει την αναφορά σε ένα κελί ή μια ονομαστική περιοχή. Αυτό πρέπει να είναι έγκυρη αναφορά κελιού, αλλιώς η συνάρτηση θα επιστρέψει #REF! λάθος
- [Α'1] - Μια λογική τιμή που καθορίζει για ποιον τύπο αναφοράς θα χρησιμοποιηθεί ref κείμενο. Αυτό μπορεί είτε να είναι ΑΛΗΘΙΝΟ (υποδεικνύει αναφορά στυλ Α1) είτε ΛΑΘΟΣ (υποδεικνύει αναφορά στυλ R1C1). Εάν παραλειφθεί, είναι TRUE από προεπιλογή.
επιπρόσθετες σημειώσεις
- INDIRECT είναι μια πτητική συνάρτηση. Αυτό σημαίνει ότι επανυπολογίζει κάθε φορά που το βιβλίο εργασίας του Excel είναι ανοιχτό ή όταν ενεργοποιείται ένας υπολογισμός στο φύλλο εργασίας. Αυτό προσθέτει στον χρόνο επεξεργασίας και επιβραδύνει το βιβλίο εργασίας σας. Ενώ μπορείτε να χρησιμοποιήσετε τον έμμεσο τύπο με μικρά σύνολα δεδομένων με μικρό ή καθόλου αντίκτυπο στην ταχύτητα, ενδέχεται να το κάνετε πιο αργό το βιβλίο εργασίας σας όταν το χρησιμοποιείτε με μεγάλα σύνολα δεδομένων
- Το κείμενο αναφοράς (ref_text) θα μπορούσε να είναι:
- Μια αναφορά σε ένα κελί που με τη σειρά του περιέχει μια αναφορά σε μορφή αναφοράς τύπου A1 ή R1C1.
- Μια αναφορά σε ένα κελί σε διπλά εισαγωγικά.
- Μια ονομαστική περιοχή που επιστρέφει μια αναφορά
Παραδείγματα τρόπου χρήσης έμμεσης συνάρτησης στο Excel
Τώρα ας βουτήξουμε και να ρίξουμε μια ματιά σε μερικά παραδείγματα σχετικά με τον τρόπο χρήσης της συνάρτησης INDIRECT στο Excel.
Παράδειγμα 1: Χρησιμοποιήστε μια αναφορά κελιού για να λάβετε την τιμή
Λαμβάνει την αναφορά κελιού ως συμβολοσειρά κειμένου ως είσοδο και επιστρέφει την τιμή σε αυτήν την αναφορά (όπως φαίνεται στο παρακάτω παράδειγμα):
Ο τύπος στο κελί C1 είναι:
= INDIRECT ("A1")
Ο παραπάνω τύπος λαμβάνει την αναφορά κελιού A1 ως όρισμα εισόδου (εντός διπλών εισαγωγικών ως συμβολοσειρά κειμένου) και επιστρέφει την τιμή σε αυτό το κελί, η οποία είναι 123.
Τώρα αν σκέφτεστε, γιατί δεν χρησιμοποιώ απλώς = A1 αντί να χρησιμοποιήσω τη συνάρτηση INDIRECT, έχετε μια έγκυρη ερώτηση.
Ιδού γιατί…
Όταν χρησιμοποιείτε = Α1 ή = $ A $ 1, σας δίνει το ίδιο αποτέλεσμα. Αλλά όταν εισάγετε μια γραμμή πάνω από την πρώτη γραμμή, θα παρατηρήσετε ότι οι αναφορές κελιών θα αλλάξουν αυτόματα σε λογαριασμό για τη νέα σειρά.
Μπορείτε επίσης να χρησιμοποιήσετε τη συνάρτηση INDIRECT όταν θέλετε να κλειδώσετε τις αναφορές κελιών με τέτοιο τρόπο ώστε να μην αλλάζει όταν εισάγετε γραμμές/στήλες στο φύλλο εργασίας.
Παράδειγμα 2: Χρησιμοποιήστε την αναφορά κελιού σε ένα κελί για να λάβετε την τιμή
Μπορείτε επίσης να χρησιμοποιήσετε αυτήν τη συνάρτηση για να λάβετε την τιμή από ένα κελί του οποίου η αναφορά είναι αποθηκευμένη στο ίδιο το κελί.
Στο παραπάνω παράδειγμα, το κελί Α1 έχει την τιμή 123.
Το κελί C1 έχει την αναφορά στο κελί Α1 (ως συμβολοσειρά κειμένου).
Τώρα, όταν χρησιμοποιείτε τη συνάρτηση INDIRECT και χρησιμοποιείτε το C1 ως όρισμα (το οποίο με τη σειρά του έχει μια διεύθυνση κελιού ως συμβολοσειρά κειμένου), θα μετατρέψει την τιμή στο κελί A1 σε έγκυρη αναφορά κελιού.
Αυτό, με τη σειρά του, σημαίνει ότι η συνάρτηση θα αναφέρεται στο κελί Α1 και θα επιστρέφει την τιμή σε αυτό.
Λάβετε υπόψη ότι δεν χρειάζεται να χρησιμοποιήσετε διπλά εισαγωγικά εδώ, καθώς το C1 έχει την αναφορά κελιού αποθηκευμένη μόνο σε μορφή συμβολοσειράς κειμένου.
Επίσης, σε περίπτωση που η συμβολοσειρά κειμένου στο κελί C1 δεν είναι έγκυρη αναφορά κελιού, η έμμεση συνάρτηση θα επιστρέψει το #REF! λάθος.
Παράδειγμα 3: Δημιουργία αναφοράς με χρήση τιμής σε κελί
Μπορείτε επίσης να δημιουργήσετε μια αναφορά κελιού χρησιμοποιώντας έναν συνδυασμό του αλφαβήτου της στήλης και του αριθμού σειράς.
Για παράδειγμα, εάν το κελί C1 περιέχει τον αριθμό 2 και χρησιμοποιείτε τον τύπο = INDIRECT ("A" & C1) τότε θα αναφερόταν στο κελί Α2.
Μια πρακτική εφαρμογή αυτού θα μπορούσε να είναι όταν θέλετε να δημιουργήσετε δυναμική αναφορά σε κελιά με βάση την τιμή σε κάποιο άλλο κελί.
Σε περίπτωση που η συμβολοσειρά κειμένου που χρησιμοποιείτε στον τύπο δίνει μια αναφορά που το Excel δεν καταλαβαίνει, θα επιστρέψει το σφάλμα ref (#REF!).
Παράδειγμα 4: Υπολογίστε το άθροισμα ενός εύρους κελιών
Μπορείτε επίσης να αναφερθείτε σε μια περιοχή κελιών με τον ίδιο τρόπο που αναφέρεστε σε ένα κελί χρησιμοποιώντας τη συνάρτηση INDIRECT στο Excel.
Για παράδειγμα, = ΕΜΠΡΟΣ ("A1: A5") θα αναφερόταν στο εύρος A1: A5.
Στη συνέχεια, μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUM για να βρείτε το σύνολο ή τη συνάρτηση LARGE/SMALL/MIN/MAX για να κάνετε άλλους υπολογισμούς.
Ακριβώς όπως η συνάρτηση SUM, μπορείτε επίσης να χρησιμοποιήσετε λειτουργίες όπως LARGE, MAX/MIN, COUNT κ.λπ.
Παράδειγμα 5: Δημιουργία αναφοράς σε ένα φύλλο χρησιμοποιώντας τη συνάρτηση INDIRECT
Τα παραπάνω παραδείγματα κάλυψαν τον τρόπο παραπομπής ενός κελιού στο ίδιο φύλλο εργασίας. Μπορείτε επίσης να χρησιμοποιήσετε τον τύπο INDIRECT για να αναφέρετε ένα κελί σε κάποιο άλλο φύλλο εργασίας ή άλλο βιβλίο εργασίας επίσης.
Εδώ είναι κάτι που πρέπει να γνωρίζετε σχετικά με την αναφορά σε άλλα φύλλα:
- Ας υποθέσουμε ότι έχετε ένα φύλλο εργασίας με το όνομα Sheet1 και στο φύλλο στο κελί A1, έχετε την τιμή 123. Εάν μεταβείτε σε άλλο φύλλο (ας πούμε το Sheet2) και αναφέρετε στο κελί A1 στο Sheet1, ο τύπος θα είναι: = Φύλλο1! Α1
Αλλά…
- Εάν έχετε ένα φύλλο εργασίας που περιέχει δύο ή περισσότερες από δύο λέξεις (με κενό χαρακτήρα μεταξύ τους) και αναφέρεστε στο κελί A1 σε αυτό το φύλλο από άλλο φύλλο, ο τύπος θα είναι: = 'Σύνολο δεδομένων'! A1
Σε περίπτωση πολλαπλών λέξεων, το Excel εισάγει αυτόματα μεμονωμένα εισαγωγικά στην αρχή και στο τέλος του ονόματος του φύλλου.
Τώρα ας δούμε πώς να δημιουργήσουμε μια ΕΜΕΙΣ συνάρτηση για αναφορά σε ένα κελί σε άλλο φύλλο εργασίας.
Ας υποθέσουμε ότι έχετε ένα φύλλο με όνομα Dataset και το κελί A1 σε αυτό έχει την τιμή 123.
Τώρα για να αναφερθείτε σε αυτό το κελί από άλλο φύλλο εργασίας, χρησιμοποιήστε τον ακόλουθο τύπο:
= INDIRECT ("'Set Data'! A1")
Όπως μπορείτε να δείτε, η αναφορά στο κελί πρέπει επίσης να περιέχει το όνομα του φύλλου εργασίας.
Εάν έχετε το όνομα του φύλλου εργασίας σε ένα κελί (ας πούμε Α1), τότε μπορείτε να χρησιμοποιήσετε τον ακόλουθο τύπο:
= INDIRECT ("'" & A1 & "'! A1")
Εάν έχετε το όνομα του φύλλου εργασίας στο κελί Α1 και τη διεύθυνση κελιού στο κελί Α2, τότε ο τύπος θα είναι:
= INDIRECT ("'" & A1 & "'!" & A2)
Ομοίως, μπορείτε επίσης να τροποποιήσετε τον τύπο για να αναφέρεται σε ένα κελί σε άλλο βιβλίο εργασίας.
Αυτό θα μπορούσε να είναι χρήσιμο όταν προσπαθείτε να δημιουργήσετε ένα συνοπτικό φύλλο που αντλεί τα δεδομένα από πολλά διαφορετικά φύλλα.
Επίσης, θυμηθείτε, όταν χρησιμοποιείτε αυτόν τον τύπο για να αναφέρετε σε άλλο βιβλίο εργασίας, αυτό το βιβλίο εργασίας πρέπει να είναι ανοιχτό.
Παράδειγμα 6: Αναφορά σε ονομασμένο εύρος με χρήση ΤΥΠΟΥ
Εάν έχετε δημιουργήσει μια ονομαστική περιοχή στο Excel, μπορείτε να αναφερθείτε σε αυτήν την ονομαστική περιοχή χρησιμοποιώντας τη συνάρτηση INDIRECT.
Για παράδειγμα, ας υποθέσουμε ότι έχετε τους βαθμούς για 5 μαθητές σε τρία μαθήματα όπως φαίνεται παρακάτω:
Σε αυτό το παράδειγμα, ας ονομάσουμε τα κελιά:
- Β2: Β6: Μαθηματικά
- C2: C6: Φυσική
- D2: D6: Χημεία
Για να ονομάσετε μια σειρά κελιών, απλώς επιλέξτε τα κελιά και μεταβείτε στο πλαίσιο ονόματος, εισαγάγετε το όνομα και πατήστε enter.
Τώρα μπορείτε να ανατρέξετε σε αυτές τις ονομαζόμενες περιοχές χρησιμοποιώντας τον τύπο:
= INDIRECT ("Όνομα εύρους")
Για παράδειγμα, εάν θέλετε να μάθετε τον μέσο όρο των βαθμών στα Μαθηματικά, χρησιμοποιήστε τον τύπο:
= ΜΕΣΟ (ΕΜΠΡΟΣ ("Μαθηματικά"))
Εάν έχετε το όνομα εύρους σε ένα κελί (το F2 στο παρακάτω παράδειγμα έχει το όνομα Μαθηματικά), μπορείτε να το χρησιμοποιήσετε απευθείας στον τύπο.
Το παρακάτω παράδειγμα δείχνει τον τρόπο υπολογισμού του μέσου όρου χρησιμοποιώντας τα ονομαζόμενα εύρη.
Παράδειγμα 7: Δημιουργία εξαρτημένης αναπτυσσόμενης λίστας με τη χρήση του Excel INDIRECT Function
Αυτή είναι μια εξαιρετική χρήση αυτής της λειτουργίας. Μπορείτε εύκολα να δημιουργήσετε μια εξαρτημένη αναπτυσσόμενη λίστα χρησιμοποιώντας αυτήν (ονομάζεται επίσης αναπτυσσόμενη λίστα υπό όρους).
Για παράδειγμα, ας υποθέσουμε ότι έχετε μια λίστα σε μια σειρά χωρών και το όνομα των πόλεων για κάθε χώρα, όπως φαίνεται παρακάτω:
Τώρα, για να δημιουργήσετε μια εξαρτώμενη αναπτυσσόμενη λίστα, πρέπει να δημιουργήσετε δύο ονομαζόμενες περιοχές, A2: A5 με το όνομα US και B2: B5 με το όνομα India.
Τώρα επιλέξτε το κελί D2 και δημιουργήστε μια αναπτυσσόμενη λίστα για την Ινδία και τις ΗΠΑ. Αυτή θα είναι η πρώτη αναπτυσσόμενη λίστα όπου ο χρήστης έχει την επιλογή να επιλέξει μια χώρα.
Τώρα για να δημιουργήσετε μια εξαρτημένη αναπτυσσόμενη λίστα:
- Επιλέξτε το κελί Ε2 (το κελί στο οποίο θέλετε να λάβετε την εξαρτώμενη αναπτυσσόμενη λίστα).
- Κάντε κλικ στην καρτέλα Δεδομένα
- Κάντε κλικ στην επιλογή Επικύρωση δεδομένων.
- Επιλέξτε Λίστα ως κριτήρια επικύρωσης και χρησιμοποιήστε τον ακόλουθο τύπο στο πεδίο προέλευσης: = INDIRECT ($ D $ 2)
- Κάντε κλικ στο OK.
Τώρα, όταν εισάγετε τις ΗΠΑ στο κελί D2, το αναπτυσσόμενο μενού στο κελί Ε2 θα εμφανίσει τις πολιτείες στις ΗΠΑ.
Και όταν εισάγετε την Ινδία στο κελί D2, το αναπτυσσόμενο μενού στο κελί Ε2 θα εμφανίσει τις πολιτείες στην Ινδία.
Αυτά είναι μερικά παραδείγματα για τη χρήση της συνάρτησης INDIRECT στο Excel. Αυτά τα παραδείγματα θα λειτουργούσαν σε όλες τις εκδόσεις του Excel (Office 365, Excel2021-2022/2016/2013/2013)
Ελπίζω να βρήκατε χρήσιμο αυτό το σεμινάριο.
- Λειτουργία Excel VLOOKUP.
- Λειτουργία Excel HLOOKUP.
- Λειτουργία INDEX του Excel.
- Λειτουργία MATCH του Excel.
- Λειτουργία Excel OFFSET.
