ΑΜΕΣΗ συνάρτηση στο Excel
Η έμμεση συνάρτηση στο excel είναι μια ενσωματωμένη συνάρτηση που χρησιμοποιείται για αναφορά και λήψη τιμών κελιού από μια συμβολοσειρά κειμένου, αυτός ο τύπος λαμβάνει αναφορά από το αναφερόμενο κελί, έχει δύο επιχειρήματα το πρώτο όρισμα δεν είναι προαιρετικό, ενώ το δεύτερο όρισμα είναι προαιρετικό που αναφέρεται τον τύπο αντιστοίχισης, αυτή η συνάρτηση μπορεί επίσης να χρησιμοποιηθεί με έναν άλλο τύπο.
Η λειτουργία INDIRECT στο excel έχει πολλές χρήσεις. Επιστρέφει την αναφορά κελιού που καθορίζεται από μια συμβολοσειρά κειμένου. Για παράδειγμα, εάν το κελί B1 περιέχει μια συμβολοσειρά, ας πούμε ότι το εύρος c4 και c4 έχουν την τιμή "Jessica". Τώρα, εάν θα χρησιμοποιήσουμε τη συνάρτηση INDIRECT και περάσουμε το κείμενο αναφοράς ως B1, θα επιστρέψει την τιμή που περιέχεται στην περιοχή κελιών c4 που είναι "Jessica".
Η λειτουργία INDIRECT του Excel σάς επιτρέπει να καθορίσετε μια διεύθυνση κελιού ΑΜΕΣΩΣ. Όπως μπορείτε στο παραπάνω παράδειγμα INDIRECT Excel, εάν το κελί B1 περιέχει το κείμενο C4, αυτός ο τύπος έμμεσου excel επιστρέφει το περιεχόμενο του κελιού C4 ( Jessica ). Αυτή η λειτουργία είναι μια εξαιρετικά χρήσιμη λειτουργία. Μπορείτε να χρησιμοποιήσετε τη συνάρτηση INDIRECT όποτε θέλετε να αλλάξετε την αναφορά σε ένα κελί σε έναν τύπο έμμεσου excel χωρίς να αλλάξετε τον ίδιο τον τύπο.
ΑΜΕΣΗ φόρμουλα Excel
Εξήγηση
Το Ref_text είναι μια αναφορά σε ένα κελί που περιέχει μια αναφορά τύπου A1, μια αναφορά τύπου R1C1, ένα όνομα που ορίζεται ως αναφορά ή μια αναφορά σε ένα κελί ως συμβολοσειρά κειμένου.
Το όρισμα a1 είναι προαιρετικό
| Α'1 | Λάθος ή 0, όταν το ref_text είναι σε στυλ R1C1 |
| Α'1 | Παραλείπεται ή Αληθές (1) όταν το ref_text είναι σε στυλ Α1 |
Πώς να χρησιμοποιήσετε τη συνάρτηση INDIRECT στο Excel;
Ας πάρουμε ένα παράδειγμα INDIRECT excel πριν χρησιμοποιήσουμε τη λειτουργία διεύθυνσης στο βιβλίο εργασίας του Excel:
Ας υποθέσουμε, για έναν ιστότοπο www.xyz.com έχουμε τα δεδομένα ανάλυσης Google για επισκέπτες στον ιστότοπο από διαφορετικό κανάλι κοινού για πέντε διαφορετικές χώρες, όπως φαίνεται παρακάτω στον πίνακα:
Μπορούμε να χρησιμοποιήσουμε τη συνάρτηση INDIRECT στο Excel με σκοπό τη μετατροπή μιας συμβολοσειράς κειμένου σε μια αναφορά, παραπέμποντας έτσι στο κελί H4 και χρησιμοποιώντας αυτό το όνομα ως αναφορά. Η χρήση της συνάρτησης INDIRECT στο excel, σε αυτήν την περίπτωση, είναι η αναφορά εύρους ονομάτων χρησιμοποιώντας τιμές ενός κελιού, έτσι στο κελί I4, θα μπορούσαμε απλώς να χρησιμοποιήσουμε τη συνάρτηση αθροίσματος για να υπολογίσουμε τον συνολικό αριθμό επισκεπτών από μια χώρα του Καναδά.
Τώρα, μπορούμε να υπολογίσουμε άμεσα το άθροισμα των επισκεπτών από κάθε χώρα χρησιμοποιώντας τη συνάρτηση αθροίσματος, αλλά χρησιμοποιώντας τη συνάρτηση INDIRECT στο excel, μπορούμε να υπολογίσουμε το άθροισμα των επισκεπτών από διαφορετικές χώρες αλλάζοντας την αναφορά ονόματος και όχι αλλάζοντας τον ίδιο τον τύπο έμμεσου excel.
Έχουμε δημιουργήσει το εύρος ονομάτων για κάθε επισκέπτη χώρας. Αυτό θα μπορούσε να γίνει εύκολα πληκτρολογώντας το εύρος ονομάτων στο χώρο ακριβώς κάτω από το πρόχειρο, όπως φαίνεται παρακάτω.
Μια άλλη μέθοδος είναι ότι μπορείτε να μεταβείτε στους τύπους-> name manager -> επιλέξτε το εύρος που θέλετε να ονομάσετε -> κάντε κλικ στο όνομα manager -> πληκτρολογήστε το εύρος ονόματος
Ομοίως, δημιούργησε μια ονομαστική γκάμα στο excel και για επισκέπτες από άλλες χώρες.
Τώρα, στο I4, υπολογίζοντας το άθροισμα του επισκέπτη από τη χώρα Canada χρησιμοποιώντας τη συνάρτηση INDIRECT και χρησιμοποιώντας το εύρος ονομάτων στο H4 (Canada, ένα όνομα παραπάνω για F3: F7), λαμβάνουμε το συνολικό άθροισμα ενός επισκέπτη.
Εάν δημιουργήσουμε μια λίστα δεδομένων χρησιμοποιώντας την επικύρωση δεδομένων για τις χώρες και εφαρμόσουμε αυτήν τη λίστα στο H4
Και αλλάζοντας τα διαφορετικά ονόματα χωρών από τον Καναδά σε την Ινδία, τις ΗΠΑ, την Αυστραλία ή τη Σιγκαπούρη, παίρνουμε το συνολικό άθροισμα του επισκέπτη στο I4 χρησιμοποιώντας τη συνάρτηση INDIRECT στο Excel.
Έτσι, για κάθε περίπτωση, δεν αλλάζουμε τον τύπο έμμεσου excel, που βρίσκεται στο κελί I4. αλλάζουμε την αναφορά ονόματος στο H4 και υπολογίζουμε το άθροισμα των επισκεπτών για κάθε χώρα.
Μπορούμε να προχωρήσουμε ένα βήμα παραπέρα και να αναφέρουμε ονόματα σε επίπεδο φύλλου εργασίας χρησιμοποιώντας επίσης τη λειτουργία INDIRECT στο excel Για παράδειγμα, θα χρησιμοποιήσουμε τώρα τη συνάρτηση INDIRECT σε σχέση με διαφορετικά φύλλα στο βιβλίο εργασίας.
Έχουμε δημιουργήσει διαφορετικά φύλλα με τα ονόματα χωρών για τον συνολικό αριθμό πωλήσεων που πραγματοποιήθηκαν με κάθε κανάλι αναζήτησης για διαφορετικές χώρες.
Για την Ινδία,
Για τις ΗΠΑ,
Ομοίως, για άλλες χώρες.
Και πάλι, θα υπολογίσουμε τις συνολικές πωλήσεις που πραγματοποιούνται μέσω κάθε καναλιού χρησιμοποιώντας τη συνάρτηση INDIRECT στο Excel για διαφορετικά φύλλα με ονόματα χωρών ως αναφορά.
Σε αυτήν την περίπτωση, δεδομένου ότι έχουμε δεδομένα πωλήσεων σε διαφορετικά φύλλα, θα πρέπει να συνδυάσουμε διαφορετικά στοιχεία που είναι το εύρος δεδομένων, έτσι το ref_text θα ξεκινήσει με το όνομα του φύλλου, που είναι η Ινδία σε αυτήν την περίπτωση στο B4, τότε θα χρησιμοποιήστε το & χειριστή που ονομάζεται ampersand το οποίο χρησιμοποιείται για συνένωση.
Λάβουμε λοιπόν το συνολικό άθροισμα των πωλήσεων για την Ινδία. τώρα, εάν αλλάξουμε το όνομα χώρας φύλλου στο B4 από την Ινδία σε άλλες χώρες όπως οι ΗΠΑ, η Σιγκαπούρη ή ο Καναδάς, θα λάβουμε τη συνολική αξία πώλησης αυτών των χωρών.
Και πάλι, σε αυτήν την περίπτωση, απλά αλλάξαμε τις τιμές ref_text στο κελί B4 χωρίς να αλλάξουμε τον κύριο τύπο έμμεσου excel, που είναι στο C4, για να λάβουμε το άθροισμα των πωλήσεων για διαφορετικές χώρες που βρίσκονται σε διαφορετικά φύλλα χρησιμοποιώντας τη συνάρτηση INDIRECT.
Τώρα, θα χρησιμοποιήσουμε τη λειτουργία INDIRECT χρησιμοποιώντας την αναφορά R1C1 για να λάβουμε την τιμή πώλησης του τελευταίου μήνα. Έχουμε πωλήσεις για διαφορετικούς μήνες στις στήλες Β, Γ και Δ για τρεις μήνες Ιαν, Φεβ και Μαρ, οι οποίες μπορούν να αυξηθούν σε επιπλέον μήνες, όπως Απρίλιος, Μάιος, Ιούνιος κ.ο.κ Έτσι, ο πίνακας πωλήσεων θα αυξάνεται πάντα ανάλογα με την προσθήκη της επερχόμενης πώλησης μήνα. Έτσι, σε αυτό το παράδειγμα ΑΜΕΣΑ Excel, θέλουμε να υπολογίσουμε την πώληση του τελευταίου μήνα.
Λοιπόν, εδώ ο τελευταίος μήνας είναι Μάρ, και θέλουμε να υπολογίσουμε τη συνολική πώληση του μήνα Μαρτίου, η οποία είναι 249.344 $, χρησιμοποιώντας τη συνάρτηση INDIRECT.
Σε αυτό το παράδειγμα INDIRECT Excel, θα βρούμε τη χρήση του δεύτερου ορίσματος της συνάρτησης INDIRECT
ΑΜΕΣΑ (ref_text, a1)
a1: το οποίο είναι ένα προαιρετικό όρισμα, είναι μια λογική τιμή (μια τιμή Boolean) που καθορίζει τον τύπο αναφοράς (ref_text) που περιέχεται στο κελί
Εάν το a1 είναι αληθές (1) ή παραλείπεται, το ref_text θεωρείται αναφορά τύπου A1
Αν το a1 είναι ψευδές (0), το ref_text ερμηνεύεται ως στυλ R1C1
Πώς διαφέρει το στυλ A1 από το στυλ R1C1;
Κανονικά, η συνάρτηση INDIRECT στο Excel χρησιμοποιεί σημειογραφία A1. Κάθε διεύθυνση κελιού αποτελείται από ένα γράμμα στήλης και έναν αριθμό σειράς. Ωστόσο, η συνάρτηση INDIRECT στο Excel υποστηρίζει επίσης τη σημείωση R1C1. Σε αυτό το σύστημα, το κελί Α1 αναφέρεται ως κελί R1C1, κελί Α2 ως R2C1, και ούτω καθεξής.
Για να αλλάξετε σε σημείωση R1C1, επιλέξτε File-> για να ανοίξετε το πλαίσιο διαλόγου Επιλογές excel, κάντε κλικ στην καρτέλα τύπος και ένα σημάδι επιλογής σε R1C1 επιλογή στυλ αναφοράς. Τώρα, θα παρατηρήσετε ότι όλα τα γράμματα στηλών αλλάζουν σε αριθμούς. Και όλες οι αναφορές κελιού στους τύπους σας προσαρμόζονται επίσης.
Έτσι, όταν χρησιμοποιούμε το στυλ R1C1 στη συνάρτηση INDIRECT στο excel, περνάμε την τιμή a1 ως ψευδής, και αν χρησιμοποιούμε το στυλ A1, τότε μεταβιβάζουμε την τιμή true στο a1.
Τώρα, σε αυτό το παράδειγμα INDIRECT Excel, θα χρησιμοποιούμε το στυλ R1C1 λόγω της απαίτησης, καθώς οι στήλες με μήνα θα αυξάνονταν.
Χρησιμοποιώντας το στυλ R1C1, η συνολική τιμή πώλησης βρίσκεται στη σειρά 10 και η τιμή της τελευταίας στήλης, η οποία βρίσκεται στη στήλη D, θα υπολογιστεί χρησιμοποιώντας τη συνάρτηση COUNTA , η οποία θα δώσει τον τελευταίο αριθμό στήλης που περιέχει μια τιμή. Έτσι, μετρήστε (10:10) σε αυτήν την περίπτωση θα δώσει 4, το οποίο ο αριθμός στήλης σε στυλ R1C1. Λοιπόν, επικοινωνήσαμε με αυτήν την τιμή για να λάβουμε το R10C4 που περιέχει την επιθυμητή τιμή που είναι η συνολική αξία πώλησης του προηγούμενου μήνα (249.344 $).
Και, καθώς χρησιμοποιούμε το στυλ R1C1 εδώ, θα περάσουμε μια λανθασμένη τιμή για το όρισμα a1.
Τώρα, ας προσθέσουμε μια ακόμη στήλη με τον μήνα Απρίλιο.
Λάβετε λοιπόν νέες τιμές πωλήσεων του προηγούμενου μήνα που αντιστοιχούν σε αυτές του τελευταίου μήνα του Απριλίου. Ομοίως, αν προσθέσουμε περισσότερους μήνες, θα έχουμε πάντα νέα αξία πώλησης τον προηγούμενο μήνα χωρίς να αλλάζουμε τον τύπο έμμεσης excel για κάθε μήνα. Αυτή είναι η ειδικότητα της λειτουργίας INDIRECT στο excel.
Πώς να προσαρμόσετε τον πίνακα πίνακα στη λειτουργία INDIRECT Excel VLOOKUP;
Έχουμε πέντε χώρες και ένα κανάλι αναζήτησης και την πώληση που πραγματοποίησε ο ιστότοπος μέσω αυτών των διαφορετικών καναλιών από διαφορετικές χώρες. Τώρα, στο κελί B3, θέλουμε να πραγματοποιήσουμε την πώληση που πραγματοποιήθηκε μέσω οργανικών αναζητήσεων από τη χώρα Ηνωμένες Πολιτείες.
Για κάθε πίνακα για διαφορετικές χώρες, έχουμε ορίσει κάθε πίνακα με ένα σύντομο όνομα για τη χώρα, Ind για την Ινδία, Πολιτεία για τις ΗΠΑ, Can για Canada και ούτω καθεξής .
Εάν χρησιμοποιούμε άμεσα τη λειτουργία INDIRECT Excel vlookup για να λάβουμε την τιμή, όπως γίνεται παρακάτω
Παρουσιάζεται ένα σφάλμα επειδή η συνάρτηση INDIRECT Excel vlookup δεν είναι σε θέση να αναγνωρίσει τον πίνακα πίνακα που έχει περάσει ως την τιμή του κελιού B2, επομένως θα χρησιμοποιήσουμε τη συνάρτηση INDIRECT στο excel για να κάνουμε το vlookup να αναγνωρίσει το table_array ως έγκυρο όρισμα.
Εάν αλλάξουμε τις τιμές των B1 και B2, θα λάβουμε διαφορετικές τιμές πώλησης στο B3, όπως απαιτείται.
Σημείωση: Τα εύρη ονομάτων δεν είναι πεζά. Ως εκ τούτου - Ind, IND και InD είναι όλα τα ίδια με το Excel.
Στο επόμενο παράδειγμα INDIRECT Excel, θα χρησιμοποιήσουμε τη συνάρτηση INDIRECT στο excel για να δημιουργήσουμε μια εξαρτημένη λίστα. Έχουμε μια λίστα με τη θέση ενός υπαλλήλου σε μια εταιρεία. τότε, για κάθε θέση, έχουμε τα ονόματα των υπαλλήλων.
Τώρα, στο κελί I2, θα δημιουργήσουμε μια λίστα για διαφορετικές θέσεις εργαζομένων ως τις θέσεις τους, και στο I3, θα δείξουμε μια λίστα που θα εξαρτάται από την τιμή στο I2. Εάν ο I2 είναι σκηνοθέτης, τότε ο I3 θα πρέπει να αντικατοπτρίζει τα ονόματα των σκηνοθετών που ο Andrew και ο Micheal Αν το I2 είναι Manager, τότε το I3 θα πρέπει να αντικατοπτρίζει τα ονόματα του manager που είναι ο Camille, ο David, ο Davis και ο William και με τον ίδιο τρόπο για τον επόπτη.
Σε αυτήν την περίπτωση, πάλι, θα χρησιμοποιήσουμε τη συνάρτηση INDIRECT στο excel για τη δημιουργία της εξαρτώμενης λίστας. Επικύρωση δεδομένων, στις ρυθμίσεις που επιλέγουν κριτήρια επικύρωσης ως λίστα και στο πεδίο προέλευσης, θα χρησιμοποιήσουμε τη συνάρτηση INDIRECT στο Excel ως εξής.
= ΑΜΕΣΗ ($ I $ 2)
Όταν επιλέγουμε ονόματα σκηνοθέτη στο I3 αντικατοπτρίζεται ως λίστα με ονόματα Andrew και Micheal, όταν το I2 είναι Manager, τότε το I3 αντικατοπτρίζει τα ονόματα των διευθυντών Camille, David, Davis και William και παρόμοια για τον επόπτη όπως φαίνεται παρακάτω στο σχήμα.
Πράγματα που πρέπει να θυμάστε
- Το όρισμα ref_text πρέπει να είναι μια έγκυρη αναφορά κελιού, διαφορετικά το INDIRECT θα επιστρέψει το #REF! Λάθος. Το όρισμα ref_text μπορεί να αναφέρεται σε άλλο βιβλίο εργασίας.
- Εάν το ref_text αναφέρεται σε άλλο βιβλίο εργασίας (μια εξωτερική αναφορά), το άλλο βιβλίο εργασίας πρέπει να είναι ανοιχτό. Εάν το βιβλίο εργασίας προέλευσης δεν είναι ανοιχτό, το INDIRECT επιστρέφει το #REF! Τιμή σφάλματος.
- Εάν το ref_text αναφέρεται σε μια περιοχή κελιών έξω από το όριο σειράς 1.048.576 ή το όριο στήλης 16.384 (XFD), το INDIRECT επιστρέφει ένα #REF! Λάθος.
