Excel OFFSET Λειτουργία

Το Excel Λειτουργία OFFSET επιστρέφει μια αναφορά σε ένα κελί ή μια περιοχή κελιών που αντισταθμίζεται από ένα συγκεκριμένο κελί από έναν δεδομένο αριθμό σειρών και στηλών.

Σύνταξη

=OFFSET (reference, rows, cols, [height], [width])

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

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

Η συνάρτηση OFFSET θα επιστρέψει μια μετατόπιση αναφοράς κελιού από ένα δεδομένο σημείο εκκίνησης.

Σημειώσεις λειτουργίας

1. # ΑΞΙΑ! η τιμή σφάλματος θα επιστρέψει όταν η αναφορά αναφέρεται σε μια σειρά ασυνεχών κελιών.
2. # REF! η τιμή σφάλματος θα επιστρέψει όταν οι γραμμές και οι στήλες αντισταθμίζουν την αναφορά στην άκρη του φύλλου εργασίας.

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

Παράδειγμα 1: Βασική χρήση για τη λειτουργία OFFSET

Επιστρέψτε μια αναφορά σε ένα κελί με τον παρακάτω τύπο:
=OFFSET(B2,3,1)

Σε αυτήν την περίπτωση, το B2 είναι το σημείο εκκίνησης, ο αριθμός 3 και 1 σημαίνει ότι για να μετακινήσετε 3 σειρές προς τα κάτω και 1 στήλη δεξιά από το κελί B2 και να επιστρέψετε τελικά την τιμή στο C5. Δείτε το στιγμιότυπο οθόνης:

Επιστρέψτε μια αναφορά σε μια περιοχή κελιών με τον παρακάτω τύπο:
=OFFSET(B2,3,1,2,2)

Σε αυτήν την περίπτωση, θα λάβετε τα αποτελέσματα του εύρους 2 x 2 που είναι 3 σειρές παρακάτω και 1 στήλη δεξιά από το κελί B2.

Note: #ΑΞΙΑ! Θα παρουσιαστεί σφάλμα όταν επιλέγετε μόνο ένα κελί για να εφαρμόσετε τη λειτουργία OFFSET για την επιστροφή ενός εύρους κελιών. Πρέπει να επιλέξετε ένα εύρος 2 x 2 (λέει 4 κενά κελιά), εισαγάγετε τον τύπο και πατήστε το Ctrl + αλλαγή + εισάγετε κλειδιά για να λάβετε τα αποτελέσματα.

Παράδειγμα 2: Χρησιμοποιήστε τη συνάρτηση OFFSET για να αθροίσετε ένα εύρος τιμών

Όπως αναφέραμε παραπάνω, εάν προσπαθείτε να χρησιμοποιήσετε τη λειτουργία OFFSET όπως =OFFSET(B2,3,1,2,2) από μόνη της σε ένα μόνο κελί, θα επιστρέψει # ΑΞΙΑ! Λάθος. Ωστόσο, εάν συνδυάσετε τη συνάρτηση SUM και OFFSET όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης, θα επιστρέψει το άθροισμα των τιμών στην περιοχή C5: D6 απευθείας.

1. Επιλέξτε ένα κενό κελί, αντιγράψτε τον παρακάτω τύπο και πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.
=SUM(OFFSET(B2,3,1,2,2)))

Παράδειγμα 3: Αθροίστε τις στήλες με βάση ορισμένα κριτήρια

Όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης, πώς να λάβετε τις συνολικές πωλήσεις Mango από την Sun στο Tue; Δοκιμάστε όπως παρακάτω.

1. Επιλέξτε ένα κενό κελί, αντιγράψτε τον παρακάτω τύπο και πατήστε το εισάγετε κλειδί για να λάβετε το αποτέλεσμα.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Notes:

1. Στον παραπάνω τύπο, MATCH(G4,B3:B8) ψάχνει για Mango και επιστρέφει τη θέση της στο εύρος Β3: Β8. Ας δούμε, το Mango εντοπίζει 5 σειρές κάτω από το κελί B2, οπότε επιστρέφει τον αριθμό 5.
2. Δεδομένου ότι το αποτέλεσμα MATCH είναι 5 και το τελευταίο κόμμα εδώ αντιπροσωπεύει 0, η συνάρτηση OFFSET εμφανίζεται τώρα ως OFFSET(C2:E2,5,0), που σημαίνει ότι η μετατόπιση ξεκινά από το εύρος C2: E2, μετακινήστε 5 σειρές προς τα κάτω και 0 στήλη δεξιά για να λάβετε την αναφορά στην περιοχή C7: E7.
3. Στη συνέχεια, η συνάρτηση SUM εμφανίζεται ως SUM(C7:E7)και, τέλος, επιστρέψτε το άθροισμα των τιμών στο εύρος C7: E7.

Περισσότερα παραδείγματα

Πώς να αθροίσετε κάθε n σειρές κάτω στο Excel;

Πώς να δημιουργήσετε δυναμική ονομαστική περιοχή στο Excel;

Πώς να επιστρέφετε την τιμή κελιού κάθε πέντε ή ένατη σειρά στο Excel;