Τρόπος χρήσης της συνάρτησης Excel OFFSET -

Πίνακας περιεχομένων

Περίληψη

Η συνάρτηση Excel OFFSET επιστρέφει μια αναφορά σε ένα εύρος που έχει κατασκευαστεί με πέντε εισόδους: (1) ένα σημείο εκκίνησης, (2) μια μετατόπιση σειράς, (3) μια μετατόπιση στήλης, (4) ένα ύψος σε σειρές, (5) ένα πλάτος σε στήλες. Το OFFSET είναι πρακτικό σε τύπους που απαιτούν δυναμικό εύρος.

Σκοπός

Δημιουργήστε μια αντιστάθμιση αναφοράς από δεδομένο σημείο εκκίνησης

Τιμή επιστροφής

Μια αναφορά κελιού.

Σύνταξη

= OFFSET (αναφορά, σειρές, στήλες, (ύψος), (πλάτος))

Επιχειρήματα

  • αναφορά - Το σημείο εκκίνησης, παρέχεται ως αναφορά κυψέλης ή εύρος.
  • σειρές - Ο αριθμός των σειρών προς αντιστάθμιση κάτω από την αρχική αναφορά.
  • cols - Ο αριθμός των στηλών που αντισταθμίζονται στα δεξιά της αρχικής αναφοράς.
  • ύψος - (προαιρετικό) Το ύψος σε σειρές της επιστρεφόμενης αναφοράς.
  • πλάτος - (προαιρετικό) Το πλάτος σε στήλες της επιστρεφόμενης αναφοράς.

Εκδοχή

Excel 2003

Σημειώσεις χρήσης

Η συνάρτηση Excel OFFSET επιστρέφει ένα δυναμικό εύρος κατασκευασμένο με πέντε εισόδους: (1) σημείο εκκίνησης, (2) μετατόπιση σειράς, (3) μετατόπιση στήλης, (4) ύψος σε σειρές, (5) πλάτος σε στήλες.

Το σημείο εκκίνησης (το όρισμα αναφοράς ) μπορεί να είναι ένα κελί ή μια περιοχή κελιών. Τα ορίσματα γραμμών και κολώνων είναι ο αριθμός των κελιών που πρέπει να "αντισταθμιστούν" από το σημείο εκκίνησης. Τα ορίσματα ύψους και πλάτους είναι προαιρετικά και καθορίζουν το μέγεθος του εύρους που δημιουργείται. Όταν παραλείπεται το ύψος και το πλάτος , προεπιλεγμένα στο ύψος και το πλάτος αναφοράς .

Για παράδειγμα, για την αναφορά C5 ξεκινώντας από το A1, η αναφορά είναι A1, οι σειρές είναι 4 και οι στήλες είναι 2:

=OFFSET(A1,4,2) // returns reference to C5

Για την αναφορά C1: C5 από A1, η αναφορά είναι A1, οι σειρές είναι 0, οι στήλες είναι 2, το ύψος είναι 5 και το πλάτος είναι 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Σημείωση: το πλάτος θα μπορούσε να παραλειφθεί, καθώς θα είναι προεπιλογή στο 1.

Είναι σύνηθες να βλέπετε το OFFSET τυλιγμένο σε άλλη λειτουργία που αναμένει εύρος. Για παράδειγμα, στο SUM C1: C5, ξεκινώντας από το A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Ο κύριος σκοπός του OFFSET είναι να επιτρέψει στους τύπους να προσαρμόζονται δυναμικά στα διαθέσιμα δεδομένα ή στην εισαγωγή χρηστών. Η συνάρτηση OFFSET μπορεί να χρησιμοποιηθεί για τη δημιουργία μιας δυναμικής ονομαστικής περιοχής για γραφήματα ή συγκεντρωτικούς πίνακες, για να διασφαλιστεί ότι τα δεδομένα προέλευσης είναι πάντα ενημερωμένα.

Σημείωση: Η τεκμηρίωση του Excel δηλώνει ότι το ύψος και το πλάτος δεν μπορούν να είναι αρνητικά, αλλά οι αρνητικές τιμές φαίνεται να λειτούργησαν καλά από τις αρχές της δεκαετίας του 1990. Η συνάρτηση OFFSET στα Φύλλα Google δεν επιτρέπει αρνητική τιμή για ορίσματα ύψους ή πλάτους.

Παραδείγματα

Τα παρακάτω παραδείγματα δείχνουν πώς μπορεί να ρυθμιστεί το OFFSET ώστε να επιστρέφει διαφορετικά είδη εύρους. Αυτές οι οθόνες λήφθηκαν με το Excel 365, οπότε το OFFSET επιστρέφει μια δυναμική συστοιχία όταν το αποτέλεσμα είναι περισσότερα από ένα κελιά. Σε παλαιότερες εκδόσεις του Excel, μπορείτε να χρησιμοποιήσετε το πλήκτρο F9 για να ελέγξετε τα αποτελέσματα που επιστρέφονται από το OFFSET.

Παράδειγμα # 1

Στην παρακάτω οθόνη, χρησιμοποιούμε το OFFSET για να επιστρέψουμε την τρίτη τιμή (Μάρτιος) στη δεύτερη στήλη (Δυτικά). Ο τύπος στο H4 είναι:

=OFFSET(B3,3,2) // returns D6

Παράδειγμα # 2

Στην παρακάτω οθόνη, χρησιμοποιούμε το OFFSET για να επιστρέψουμε την τελευταία τιμή (Ιούνιος) στην τρίτη στήλη (Βόρεια). Ο τύπος στο H4 είναι:

=OFFSET(B3,6,3) // returns E9

Παράδειγμα # 3

Παρακάτω, χρησιμοποιούμε το OFFSET για να επιστρέψουμε όλες τις τιμές στην τρίτη στήλη (Βόρεια). Ο τύπος στο H4 είναι:

=OFFSET(B3,1,3,6) // returns E4:E9

Παράδειγμα # 4

Παρακάτω, χρησιμοποιούμε το OFFSET για να επιστρέψουμε όλες τις τιμές για τον Μάιο (πέμπτη σειρά). Ο τύπος στο H4 είναι:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Παράδειγμα # 5

Παρακάτω, χρησιμοποιούμε το OFFSET για να επιστρέψουμε την τιμή Απριλίου, Μαΐου και Ιουνίου για τη δυτική περιοχή. Ο τύπος στο H4 είναι:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Παράδειγμα # 6

Παρακάτω, χρησιμοποιούμε το OFFSET για να επιστρέψουμε την τιμή Απριλίου, Μαΐου και Ιουνίου για τη Δύση και το Βορρά. Ο τύπος στο H4 είναι:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Σημειώσεις

  • Το OFFSET επιστρέφει μόνο μια αναφορά, δεν κινούνται κελιά.
  • Και οι δύο σειρές και στήλες μπορούν να παρέχονται ως αριθμοί αρνητική για την αντιστροφή κανονική κατεύθυνση offset τους - αρνητική cols offset προς τα αριστερά, και αρνητική σειρές offset παραπάνω.
  • Το OFFSET είναι μια "πτητική λειτουργία" - θα υπολογίσει εκ νέου με κάθε αλλαγή φύλλου εργασίας. Οι πτητικές λειτουργίες μπορούν να κάνουν τα μεγαλύτερα και πιο περίπλοκα βιβλία εργασίας να λειτουργούν αργά.
  • Το OFFSET θα εμφανίσει το #REF! τιμή σφάλματος εάν η μετατόπιση βρίσκεται εκτός της άκρης του φύλλου εργασίας.
  • Όταν παραλείπεται το ύψος ή το πλάτος, χρησιμοποιείται το ύψος και το πλάτος αναφοράς .
  • Το OFFSET μπορεί να χρησιμοποιηθεί με οποιαδήποτε άλλη λειτουργία που αναμένει να λάβει αναφορά.
  • Η τεκμηρίωση του Excel λέει ότι το ύψος και το πλάτος δεν μπορούν να είναι αρνητικά, αλλά οι αρνητικές τιμές λειτουργούν.

Σχετικά βίντεο

Πώς να δημιουργήσετε μια δυναμική περιοχή με όνομα OFFSET Σε αυτό το βίντεο, θα δούμε πώς να δημιουργήσουμε μια δυναμική ονομαστική περιοχή χρησιμοποιώντας τη λειτουργία OFFSET, τον πιο συνηθισμένο τρόπο δημιουργίας δυναμικών ονομάτων με έναν τύπο.

ενδιαφέροντα άρθρα...