Λειτουργία Excel OFFSET (Παράδειγμα + Βίντεο)
Πότε να χρησιμοποιήσετε τη συνάρτηση Excel OFFSET
Η συνάρτηση Excel OFFSET μπορεί να χρησιμοποιηθεί όταν θέλετε να λάβετε μια αναφορά που αντισταθμίζει τον καθορισμένο αριθμό γραμμών και στηλών από το σημείο εκκίνησης.
Τι επιστρέφει
Επιστρέφει την αναφορά στην οποία δείχνει η λειτουργία OFFSET.
Σύνταξη
= OFFSET (αναφορά, σειρές, κολώνες, [ύψος], [πλάτος])
Ορίσματα εισόδου
- αναφορά - Η αναφορά από την οποία θέλετε να αντισταθμίσετε. Αυτό θα μπορούσε να είναι μια αναφορά κελιού ή μια περιοχή παρακείμενων κελιών.
- σειρές - τον αριθμό των γραμμών που πρέπει να αντισταθμιστούν. Εάν χρησιμοποιείτε έναν θετικό αριθμό αντισταθμίζεται στις παρακάτω γραμμές και εάν χρησιμοποιείται ένας αρνητικός αριθμός, τότε αντισταθμίζεται στις παραπάνω γραμμές.
- κολες - τον αριθμό των στηλών που θα αντισταθμιστούν. Εάν χρησιμοποιείτε θετικό αριθμό, μετατοπίζεται στις στήλες στα δεξιά και εάν χρησιμοποιείται αρνητικός αριθμός, τότε αντισταθμίζεται στις στήλες στα αριστερά.
- [ύψος] - αυτός είναι ένας αριθμός που αντιπροσωπεύει τον αριθμό των γραμμών στην αναφερόμενη αναφορά.
- [πλάτος] - αυτός είναι ένας αριθμός που αντιπροσωπεύει τον αριθμό των στηλών στην επιστρεφόμενη αναφορά.
Κατανόηση των βασικών λειτουργιών του Excel OFFSET
Η συνάρτηση Excel OFFSET είναι πιθανώς μία από τις πιο μπερδεμένες λειτουργίες στο Excel.
Ας πάρουμε ένα απλό παράδειγμα παιχνιδιού σκακιού. Υπάρχει ένα κομμάτι στο Σκάκι που ονομάζεται Rook (επίσης γνωστό ως πύργος, μαρκήσιος ή πρύτανης).
[Ευγενική προσφορά εικόνας: Υπέροχη Wikipedia]
Τώρα, όπως και όλα τα άλλα κομμάτια σκακιού, ένα Rook έχει μια σταθερή διαδρομή στην οποία μπορεί να κινηθεί στον πίνακα. Μπορεί να πάει ευθεία (δεξιά/αριστερά ή πάνω/κάτω από τον πίνακα), αλλά δεν μπορεί να πάει διαγώνια.
Για παράδειγμα, ας υποθέσουμε ότι έχουμε μια σκακιέρα στο Excel (όπως φαίνεται παρακάτω), σε ένα δεδομένο πλαίσιο (D5 σε αυτή την περίπτωση), το Rook μπορεί να πάει μόνο στις τέσσερις επισημασμένες κατευθύνσεις.
Τώρα, αν σας ζητήσω να πάρετε το Rook από την τρέχουσα θέση του σε αυτό που επισημαίνεται με κίτρινο χρώμα, τι θα πείτε στο rook;
Θα του ζητήσετε να κάνει δύο βήματα προς τα κάτω και δύο βήματα προς τα δεξιά … έτσι δεν είναι;
Και αυτό είναι μια κοντινή προσέγγιση του τρόπου λειτουργίας της λειτουργίας OFFSET.
Τώρα ας δούμε τι σημαίνει αυτό στο Excel. Θέλω να ξεκινήσω με το κελί D5 (όπου βρίσκεται το Rook) και μετά να πάω δύο σειρές προς τα κάτω και δύο στήλες προς τα δεξιά και να λάβω την τιμή από το κελί εκεί.
Ο τύπος θα είναι:
= OFFSET (από πού να ξεκινήσετε, πόσες γραμμές προς τα κάτω, πόσες στήλες προς τα δεξιά)
Όπως μπορείτε να δείτε, ο τύπος στο παραπάνω παράδειγμα στο κελί J1 είναι = OFFSET (D5,2,2).
Ξεκίνησε στο D5 και στη συνέχεια πήγε δύο σειρές προς τα κάτω και δύο στήλες προς τα δεξιά και έφτασε στο κελί F7. Στη συνέχεια επέστρεψε την τιμή στο κελί F7.
Στο παραπάνω παράδειγμα, εξετάσαμε τη συνάρτηση OFFSET με 3 ορίσματα. Υπάρχουν όμως δύο ακόμη προαιρετικά ορίσματα που μπορούν να χρησιμοποιηθούν.
Ας δούμε εδώ ένα απλό παράδειγμα:
Ας υποθέσουμε ότι θέλετε να χρησιμοποιήσετε την αναφορά στο κελί A1 (με κίτρινο χρώμα) και θέλετε να αναφερθείτε σε ολόκληρο το εύρος που επισημαίνεται με μπλε χρώμα (C2: E4) σε έναν τύπο.
Πώς θα το κάνατε αυτό χρησιμοποιώντας ένα πληκτρολόγιο; Πρώτα θα μεταβείτε στο κελί C2 και, στη συνέχεια, επιλέξτε όλα τα κελιά στο C2: E4.
Τώρα ας δούμε πώς να το κάνουμε αυτό χρησιμοποιώντας τον τύπο OFFSET:
= OFFSET (A1,1,2,3,3)
Εάν χρησιμοποιήσετε αυτόν τον τύπο σε ένα κελί, θα επιστρέψει #ΤΙΜΗ! σφάλμα, αλλά αν μπείτε στη λειτουργία επεξεργασίας και επιλέξετε τον τύπο και πατήσετε F9, θα δείτε ότι επιστρέφει όλες οι τιμές που επισημαίνονται με μπλε χρώμα.
Τώρα ας δούμε πώς λειτουργεί αυτός ο τύπος:
= OFFSET (A1,1,2,3,3)
- Το πρώτο όρισμα είναι το κελί από όπου πρέπει να ξεκινήσει.
- Το δεύτερο όρισμα είναι 1, το οποίο λέει στο Excel να επιστρέψει μια αναφορά που έχει OFFSET κατά 1 γραμμή.
- Το τρίτο όρισμα είναι 2, το οποίο λέει στο Excel να επιστρέψει μια αναφορά που έχει OFFSET κατά 2 στήλες.
- Το τέταρτο όρισμα είναι 3. Αυτό καθορίζει ότι η αναφορά πρέπει να καλύπτει 3 σειρές. Αυτό ονομάζεται όρισμα ύψους.
- Το πέμπτο όρισμα είναι 3. Αυτό καθορίζει ότι η αναφορά πρέπει να καλύπτει 3 στήλες. Αυτό ονομάζεται όρισμα πλάτος.
Τώρα που έχετε την αναφορά σε ένα εύρος κελιών (C2: E4), μπορείτε να το χρησιμοποιήσετε σε άλλες λειτουργίες (όπως SUM, COUNT, MAX, AVERAGE).
Ας ελπίσουμε ότι έχετε μια καλή βασική κατανόηση της χρήσης της συνάρτησης Excel OFFSET. Τώρα ας ρίξουμε μια ματιά σε μερικά πρακτικά παραδείγματα χρήσης της συνάρτησης OFFSET.
Λειτουργία Excel OFFSET - Παραδείγματα
Ακολουθούν δύο παραδείγματα χρήσης της συνάρτησης Excel OFFSET.
Παράδειγμα 1 - Εύρεση του τελευταίου γεμισμένου κελιού στη στήλη
Ας υποθέσουμε ότι έχετε κάποια δεδομένα σε μια στήλη όπως φαίνεται παρακάτω:
Για να βρείτε το τελευταίο κελί στη στήλη, χρησιμοποιήστε τον ακόλουθο τύπο:
= OFFSET (A1, COUNT (A: A) -1,0)
Αυτός ο τύπος υποθέτει ότι δεν υπάρχουν τιμές εκτός από αυτές που εμφανίζονται και αυτά τα κελιά δεν έχουν κενό κελί. Λειτουργεί μετρώντας τον συνολικό αριθμό κελιών που γεμίζουν και αντισταθμίζει ανάλογα το κελί Α1.
Για παράδειγμα, σε αυτήν την περίπτωση, υπάρχουν 8 τιμές, οπότε COUNT (A: A) επιστρέφει 8. Αντισταθμίζουμε το κελί A1 κατά 7 για να λάβουμε την τελευταία τιμή.
Παράδειγμα 2 - Δημιουργία δυναμικής πτώσης
Μπορείτε να επεκτείνετε τις εμφανίσεις εννοιών στο Παράδειγμα 1 για να δημιουργήσετε δυναμικά εύρη με εύρος τιμών. Αυτά θα μπορούσαν να είναι χρήσιμα εάν χρησιμοποιείτε τις ονομαζόμενες περιοχές σε τύπους ή στη δημιουργία αναπτυσσόμενων μενού και είναι πιθανό να προσθέσετε/διαγράψετε δεδομένα από την αναφορά που κάνει το όνομα περιοχής.
Εδώ είναι ένα παράδειγμα:
Λάβετε υπόψη ότι το αναπτυσσόμενο μενού προσαρμόζεται αυτόματα όταν προστίθεται ή αφαιρείται το έτος.
Αυτό συμβαίνει καθώς ο τύπος που χρησιμοποιείται για τη δημιουργία του αναπτυσσόμενου μενού είναι δυναμικός και προσδιορίζει οποιαδήποτε προσθήκη ή διαγραφή και προσαρμόζει ανάλογα το εύρος.
Εδώ είναι πώς να το κάνετε αυτό:
- Επιλέξτε το κελί όπου θέλετε να εισαγάγετε το αναπτυσσόμενο μενού.
- Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
- Στο παράθυρο διαλόγου Επικύρωση δεδομένων, στην καρτέλα Ρυθμίσεις επιλέξτε Λίστα από το αναπτυσσόμενο μενού.
- Στην πηγή, εισαγάγετε τον ακόλουθο τύπο: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Κάντε κλικ στο OK.
Ας δούμε πώς λειτουργεί αυτός ο τύπος:
- Τα τρία πρώτα ορίσματα της συνάρτησης OFFSET είναι τα Α1, 0 και 0. Αυτό ουσιαστικά σημαίνει ότι θα επέστρεφε το ίδιο κελί αναφοράς (που είναι Α1).
- Το τέταρτο όρισμα αφορά το ύψος και εδώ η συνάρτηση COUNT επιστρέφει τον συνολικό αριθμό στοιχείων στη λίστα. Υποθέτει ότι δεν υπάρχουν κενά στη λίστα.
- Το πέμπτο όρισμα είναι 1, το οποίο δείχνει ότι το πλάτος της στήλης πρέπει να είναι ένα.
Επιπρόσθετες σημειώσεις:
- Το OFFSET είναι μια πτητική λειτουργία (χρησιμοποιήστε με προσοχή).
- Υπολογίζει εκ νέου κάθε φορά που το βιβλίο εργασίας του Excel είναι ανοιχτό ή όταν ενεργοποιείται ένας υπολογισμός στο φύλλο εργασίας. Αυτό θα μπορούσε να προσθέσει χρόνο επεξεργασίας και να επιβραδύνει το βιβλίο εργασίας σας.
- Εάν η τιμή ύψους ή πλάτους παραλείπεται, λαμβάνεται ως εκείνη της αναφοράς.
- Αν σειρές και κολες είναι αρνητικοί αριθμοί τότε η κατεύθυνση μετατόπισης αντιστρέφεται.
Excel OFFSET Λειτουργία Εναλλακτικές λύσεις
Λόγω ορισμένων περιορισμών της λειτουργίας OFFSET του Excel, πολλοί θέλετε να εξετάσετε εναλλακτικές λύσεις σε αυτήν:
- Λειτουργία INDEX: Η συνάρτηση INDEX μπορεί επίσης να χρησιμοποιηθεί για την επιστροφή μιας αναφοράς κελιού. Κάντε κλικ εδώ για να δείτε ένα παράδειγμα σχετικά με τον τρόπο δημιουργίας ενός δυναμικού εύρους με χρήση της συνάρτησης INDEX.
- Πίνακας Excel: Εάν χρησιμοποιείτε δομημένες αναφορές στον πίνακα Excel, δεν χρειάζεται να ανησυχείτε για την προσθήκη νέων δεδομένων και την ανάγκη προσαρμογής των τύπων.
Λειτουργία Excel OFFSET - Εκπαιδευτικό βίντεο
- Λειτουργία Excel VLOOKUP.
- Λειτουργία Excel HLOOKUP.
- Λειτουργία INDEX του Excel.
- Excel INDIRECT συνάρτηση.
- Λειτουργία MATCH του Excel.
