Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε το Λειτουργία Excel OFFSET στο Excel για να δημιουργήσετε μια μετατόπιση αναφοράς από ένα αρχικό κελί.
Επισκόπηση λειτουργίας OFFSET
Η συνάρτηση OFFSET Ξεκινά με μια καθορισμένη αναφορά κελιού και επιστρέφει σε μια αναφορά κελιού έναν καθορισμένο αριθμό γραμμών και στηλών που αντισταθμίζονται από την αρχική αναφορά. Οι αναφορές μπορεί να είναι ένα κελί ή μια περιοχή κελιών. Το Offset σάς επιτρέπει επίσης να αλλάξετε το μέγεθος της αναφοράς για έναν δεδομένο αριθμό γραμμών/στηλών.
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
Σύνταξη και είσοδοι λειτουργίας IFERROR:
| 1 | = OFFSET (αναφορά, σειρές, κολώνες, ύψος, πλάτος) |
αναφορά - Η αρχική αναφορά κελιού από την οποία θέλετε να αντισταθμίσετε.
σειρές - Ο αριθμός των γραμμών προς αντιστάθμιση.
κολες - Ο αριθμός των στηλών που θα αντισταθμιστούν.
ύψος - ΠΡΟΑΙΡΕΤΙΚΑ: Προσαρμόστε τον αριθμό των γραμμών στην αναφορά.
πλάτος - ΠΡΟΑΙΡΕΤΙΚΑ: Προσαρμόστε τον αριθμό των στηλών στην αναφορά.
Τι είναι η λειτουργία OFFSET;
Η συνάρτηση OFFSET είναι μία από τις πιο ισχυρές λειτουργίες υπολογιστικών φύλλων καθώς μπορεί να είναι αρκετά ευέλικτη σε αυτό που δημιουργεί. Δίνει στον χρήστη τη δυνατότητα να ορίσει ένα κελί ή εύρος σε ποικιλία θέσεων και μεγεθών.
ΠΡΟΣΟΧΗ: Η συνάρτηση OFFSET είναι μία από τις πτητικές λειτουργίες. Τις περισσότερες φορές όταν εργάζεστε στο υπολογιστικό φύλλο σας, ο υπολογιστής θα υπολογίσει εκ νέου έναν τύπο μόνο εάν οι εισαγωγές έχουν αλλάξει τις τιμές τους. Μια πτητική συνάρτηση, ωστόσο, υπολογίζει εκ νέου κάθε ώρα να κάνετε μια αλλαγή σε οποιοδήποτε κελί. Πρέπει να χρησιμοποιείται προσοχή για να διασφαλιστεί ότι δεν προκαλείτε μεγάλο χρόνο επανυπολογισμού λόγω υπερβολικής χρήσης πτητικής λειτουργίας ή λόγω πολλών κυττάρων που εξαρτώνται από το αποτέλεσμα μιας πτητικής λειτουργίας.
Παραδείγματα βασικών σειρών
Σε κάθε χρήση της λειτουργίας OFFSET, πρέπει να δώσετε ένα σημείο εκκίνησης ή άγκυρα. Ας δούμε αυτόν τον πίνακα για να το καταλάβουμε:
Θα χρησιμοποιήσουμε το "Bob" στο κελί B3 ως σημείο αγκύρωσης. Αν θέλαμε να αρπάξουμε την τιμή ακριβώς κάτω (Charlie), θα λέγαμε ότι θέλουμε να αλλάξουμε τη σειρά κατά 1. Ο τύπος μας θα μοιάζει με
| 1 | = OFFSET (B3, 1) |
Αν θέλαμε να αλλάξουμε προς τα πάνω, αυτό θα ήταν αρνητική μετατόπιση. Μπορείτε να το σκεφτείτε καθώς ο αριθμός γραμμών μειώνεται, οπότε πρέπει να αφαιρέσουμε. Έτσι, για να πάρουμε την παραπάνω τιμή (Adam), θα γράφαμε
| 1 | = OFFSET (B2, -1) |
Βασικά παραδείγματα στηλών
Συνεχίζοντας την ιδέα από το προηγούμενο παράδειγμα, θα προσθέσουμε μια άλλη στήλη στον πίνακά μας.
Αν θέλαμε να πιάσουμε τον δάσκαλο για τον Μπομπ, θα μπορούσαμε να χρησιμοποιήσουμε τον τύπο
| 1 | = OFFSET (B2, 0, 1) |
Σε αυτήν την περίπτωση, είπαμε ότι θέλουμε να αντισταθμίσουμε μηδενικές σειρές (γνωστές και ως παραμονή στην ίδια σειρά) αλλά θέλουμε να αντισταθμίσουμε 1 στήλη. Για τις στήλες, ένας θετικός αριθμός σημαίνει μετατόπιση προς τα δεξιά και αρνητικοί αριθμοί σημαίνει μετατόπιση προς τα αριστερά.
OFFSET και MATCH
Ας υποθέσουμε ότι είχατε πολλές στήλες δεδομένων και θέλατε να δώσετε στον χρήστη τη δυνατότητα να επιλέξει από ποια στήλη θα λάβει αποτελέσματα. Θα μπορούσατε να χρησιμοποιήσετε τη λειτουργία INDEX ή μπορείτε να χρησιμοποιήσετε το OFFSET. Δεδομένου ότι το MATCH θα επιστρέψει τη σχετική θέση μιας τιμής, θα πρέπει να βεβαιωθούμε ότι το σημείο αγκύρωσης βρίσκεται στα αριστερά της πρώτης πιθανής τιμής. Εξετάστε την ακόλουθη διάταξη:
Στο Β2, θα γράψουμε αυτόν τον τύπο:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
Το MATCH θα φαίνεται «Φεβρουάριο» στην περιοχή C1: F1 και θα το βρείτε στο 2nd κύτταρο. Στη συνέχεια, το OFFSET θα μετατοπίσει 1 στήλη στα δεξιά του B2 και θα πιάσει την επιθυμητή τιμή 9. Σημειώστε ότι το OFFSET δεν έχει πρόβλημα να χρησιμοποιήσει το ίδιο κελί που περιέχει τον τύπο ως σημείο αγκύρωσης.
ΣΗΜΕΙΩΣΗ: Αυτή η τεχνική θα μπορούσε να χρησιμοποιηθεί ως αντικατάσταση του VLOOKUP ή του HLOOKUP όταν θέλετε να επιστρέψετε μια τιμή από τα αριστερά/πάνω από το εύρος αναζήτησης. Αυτό συμβαίνει επειδή το OFFSET μπορεί να κάνει αρνητικές αντισταθμίσεις.
OFFSET για λήψη εύρους
Μπορείτε να χρησιμοποιήσετε το 4ου και 5ου ορίσματα στη συνάρτηση OFFSET για επιστροφή ενός εύρους αντί για ένα μόνο κελί. Ας υποθέσουμε ότι θέλετε να αθροίσετε 3 στήλες σε αυτόν τον πίνακα.
| 1 | = ΜΕΣΗ (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
Στο F2, επιλέξαμε το όνομα ενός μαθητή για τον οποίο θέλουμε να συγκεντρώσουμε τις μέσες βαθμολογίες του τεστ. Για να γίνει αυτό, θα χρησιμοποιήσουμε τον τύπο
| 1 | = ΜΕΣΗ (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
Το MATCH θα αναζητήσει στη στήλη Α το όνομά μας και θα επιστρέψει τη σχετική θέση, η οποία είναι 3 στο παράδειγμά μας. Ας δούμε πώς θα αξιολογηθεί αυτό. Πρώτον, το OFFSET πρόκειται να φύγει κάτω 3 σειρές από το Α1 και 1 στήλη στο σωστά από το Α1. Αυτό μας τοποθετεί στο κελί Β3.
| 1 | = ΜΕΣΗ (OFFSET (A1, 3, 1, 1, 3)) |
Στη συνέχεια, θα αλλάξουμε το μέγεθος του εύρους. Η νέα σειρά θα έχει B3 ως επάνω αριστερό κελί. Θα έχει ύψος 1 σειρά και 3 στήλες, δίνοντάς μας το εύρος B4: D4.
| 1 | = ΜΕΣΗ (OFFSET (A1,3, 1, 1, 3)) |
Σημειώστε ότι ενώ μπορείτε να βάλετε νόμιμα αρνητικές τιμές στα ορίσματα μετατόπισης, μπορείτε να χρησιμοποιήσετε μόνο μη αρνητικές τιμές στα ορίσματα μεγέθους.
Στο τέλος, η συνάρτηση AVERAGE βλέπει:
| 1 | = ΜΕΣΗ (Β4: Δ4) |
Έτσι, παίρνουμε τη λύση μας του 86,67
OFFSET με δυναμικό SUM
Επειδή το OFFSET χρησιμοποιείται για να βρει μια αναφορά, αντί να δείχνει απευθείας το κελί, είναι πιο χρήσιμο όταν ασχολείστε με δεδομένα που έχουν προσθέσει ή διαγράψει σειρές. Εξετάστε τον ακόλουθο πίνακα με ένα σύνολο στο κάτω μέρος
| 1 | = SUM (B2: B4) |
Εάν είχαμε χρησιμοποιήσει έναν βασικό τύπο SUM εδώ "= SUM (B2: B4)" και στη συνέχεια εισαγάγαμε μια νέα σειρά για να προσθέσουμε μια εγγραφή για τον Bill, θα είχαμε λάθος απάντηση
Αντ 'αυτού, ας σκεφτούμε πώς να το λύσουμε αυτό από την άποψη του Total. Θέλουμε πραγματικά να πιάσουμε τα πάντα, από το κελί Β2 στο κελί ακριβώς πάνω από το σύνολο μαςΤο Ο τρόπος με τον οποίο μπορούμε να γράψουμε αυτό σε έναν τύπο είναι να κάνουμε μια αντιστάθμιση γραμμής -1. Έτσι, χρησιμοποιούμε αυτό ως τον τύπο για το σύνολο μας στο κελί Β5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Αυτός ο τύπος κάνει αυτό που μόλις περιγράψαμε: ξεκινήστε από το Β2 και μεταβείτε στο 1 κελί πάνω από το συνολικό μας κελί. Μπορείτε να δείτε πώς μετά την προσθήκη των δεδομένων του Bill, το σύνολο μας ενημερώνεται σωστά.
OFFSET για να λάβετε τα τελευταία N στοιχεία
Ας υποθέσουμε ότι καταγράφετε μηνιαίες πωλήσεις αλλά θέλετε να μπορείτε να δείτε τους τελευταίους 3 μήνες. Αντί να χρειαστεί να ενημερώσετε χειροκίνητα τους τύπους σας για να συνεχίσετε να προσαρμόζεστε καθώς προστίθενται νέα δεδομένα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση OFFSET με COUNT.
Έχουμε ήδη δείξει πώς μπορείτε να χρησιμοποιήσετε το OFFSET για να πιάσετε μια σειρά κελιών. Για να καθορίσουμε πόσα κελιά πρέπει να αλλάξουμε, θα χρησιμοποιήσουμε COUNT για να βρούμε πόσα αριθμούς βρίσκονται στη στήλη Β. Ας δούμε τον πίνακα δειγμάτων.
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Εάν ξεκινήσαμε στο Β1 και αντισταθμίσαμε 4 σειρές (ο αριθμός των αριθμών στη στήλη Β), θα καταλήγαμε στο κάτω μέρος της περιοχής μας, Β5. Ωστόσο, δεδομένου ότι το OFFSET δεν μπορεί να αλλάξει μέγεθος με αρνητική τιμή, πρέπει να κάνουμε κάποιες προσαρμογές ώστε να καταλήξουμε στο Β3. Η γενική εξίσωση για αυτό πρόκειται να γίνει
| 1 | COUNT (…) - N + 1 |
Παίρνουμε τον αριθμό ολόκληρης της στήλης, αφαιρούμε όσες θέλουμε να επιστρέψουμε (αφού αλλάξουμε το μέγεθος για να τις πιάσουμε) και μετά προσθέτουμε 1 (αφού ουσιαστικά ξεκινάμε την αντιστάθμισή μας στη θέση μηδέν).
Εδώ μπορείτε να δείτε ότι έχουμε ρυθμίσει ένα εύρος για να λάβουμε το άθροισμα, τον μέσο όρο και το μέγιστο των τελευταίων Ν μηνών. Στο Ε1, έχουμε εισαγάγει την τιμή 3. Στο Ε2, ο τύπος μας είναι
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Το υπογραμμισμένο τμήμα είναι η γενική μας εξίσωση που μόλις συζητήσαμε. Δεν χρειάζεται να αντισταθμίσουμε καμία στήλη. Στη συνέχεια, θα αλλάξουμε το μέγεθος του εύρους σε 3 κύτταρα (καθορίζεται από την τιμή στο Ε1) και 1 στήλη πλάτος. Το SUM μας παίρνει αυτό το εύρος και μας δίνει το αποτέλεσμα των 1.850 $. Έχουμε επίσης δείξει ότι μπορείτε να υπολογίσετε το μέσο όρο του μέγιστου αυτού του ίδιου εύρους, αλλάζοντας απλά την εξωτερική συνάρτηση από SUM σε ό, τι απαιτεί η κατάσταση.
OFFSET λίστες δυναμικής επικύρωσης
Χρησιμοποιώντας την τεχνική που εμφανίζεται στο τελευταίο παράδειγμα, μπορούμε επίσης να δημιουργήσουμε ονόματα εύρους που θα μπορούσαν να χρησιμοποιηθούν στην επικύρωση δεδομένων ή στα γραφήματα. Αυτό μπορεί να είναι χρήσιμο όταν θέλετε να ρυθμίσετε ένα υπολογιστικό φύλλο αλλά περιμένετε να αλλάξουν μέγεθος οι λίστες/δεδομένα μας. Ας πούμε ότι το κατάστημά μας αρχίζει να πουλάει φρούτα και αυτήν τη στιγμή έχουμε 3 επιλογές.
Για να δημιουργήσουμε ένα αναπτυσσόμενο μενού Επικύρωση δεδομένων που μπορούμε να χρησιμοποιήσουμε αλλού, θα ορίσουμε το όνομα περιοχής MyFruit ως
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Αντί για COUNT, χρησιμοποιούμε COUNTA αφού έχουμε να κάνουμε με τιμές κειμένου. Εξαιτίας αυτού όμως, το COUNTA μας θα είναι ένα υψηλότερο αφού θα μετρήσει το κελί κεφαλίδας στο A1 και θα δώσει μια τιμή 4. Αν αντισταθμίσουμε όμως 4 σειρές, θα καταλήξουμε στο κελί A5 που είναι κενό. Για να το προσαρμόσουμε, αφαιρούμε το 1.
Τώρα που έχουμε τη ρύθμιση Named Range, μπορούμε να ορίσουμε κάποια επικύρωση δεδομένων στο κελί C4 χρησιμοποιώντας έναν τύπο λίστας, με πηγή:
| 1 | = MyFruit |
Λάβετε υπόψη ότι το αναπτυσσόμενο μενού εμφανίζει μόνο τα τρία τρέχοντα στοιχεία μας. Εάν προσθέσουμε στη συνέχεια περισσότερα στοιχεία στη λίστα μας και επιστρέψουμε στο αναπτυσσόμενο μενού, η λίστα εμφανίζει όλα τα νέα στοιχεία χωρίς να χρειαστεί να αλλάξουμε κανέναν από τους τύπους.
Προφυλάξεις κατά τη χρήση του OFFSET
Όπως αναφέρθηκε στην αρχή αυτού του άρθρου, το OFFSET είναι μια πτητική λειτουργία. Δεν θα το παρατηρήσετε αν το χρησιμοποιείτε σε λίγα κελιά, αλλά αν αρχίσετε να το εμπλέκεστε σε εκατοντάδες υπολογισμούς και θα παρατηρήσετε γρήγορα ότι ο υπολογιστής σας ξοδεύει ένα αξιοσημείωτο χρονικό διάστημα επανυπολογίζοντας κάθε φορά που κάνετε οποιεσδήποτε αλλαγές Το
Επιπλέον, επειδή το OFFSET δεν ονομάζει άμεσα τα κελιά που κοιτάζει, είναι πιο δύσκολο για άλλους χρήστες να έρθουν αργότερα και να αλλάξουν τους τύπους σας, αν χρειαστεί.
Αντ 'αυτού, θα ήταν σκόπιμο να χρησιμοποιηθούν Πίνακες (που εισήχθησαν στο Office 2007) οι οποίοι επιτρέπουν δομικές αναφορές. Αυτά βοήθησαν τους χρήστες να μπορούν να δώσουν μια μόνο αναφορά που προσαρμόστηκε αυτόματα σε μέγεθος καθώς προστέθηκαν ή διαγράφηκαν νέα δεδομένα.
Η άλλη επιλογή για χρήση αντί του OFFSET είναι η ισχυρή λειτουργία INDEX. Το INDEX σάς επιτρέπει να δημιουργήσετε όλα τα δυναμικά εύρη που είδαμε σε αυτό το άρθρο χωρίς το ζήτημα της πτητικής λειτουργίας.
επιπρόσθετες σημειώσεις
Χρησιμοποιήστε τη συνάρτηση OFFSET για να επιστρέψετε μια τιμή κελιού (ή μια περιοχή κελιών) αντισταθμίζοντας έναν δεδομένο αριθμό γραμμών και στηλών από μια αρχική αναφορά. Όταν αναζητάτε μόνο ένα κελί, οι τύποι OFFSET επιτυγχάνουν τον ίδιο σκοπό με τους τύπους INDEX, χρησιμοποιώντας μια ελαφρώς διαφορετική τεχνική. Η πραγματική δύναμη της συνάρτησης OFFSET έγκειται στην ικανότητά της να επιλέγει μια σειρά κελιών που θα χρησιμοποιηθούν σε έναν άλλο τύπο.
Όταν χρησιμοποιείτε τη λειτουργία OFFSET, ορίζετε ένα αρχικό κελί εκκίνησης ή μια περιοχή κελιών. Στη συνέχεια, υποδεικνύετε τον αριθμό γραμμών και στηλών που θα αντισταθμιστούν από το αρχικό κελί. Μπορείτε επίσης να αλλάξετε το μέγεθος του εύρους. προσθήκη ή αφαίρεση γραμμών ή στηλών.
Επιστρέψτε στη λίστα όλων των λειτουργιών στο Excel
OFFSET στα Υπολογιστικά φύλλα Google
Η συνάρτηση OFFSET λειτουργεί ακριβώς το ίδιο στα Φύλλα Google όπως στο Excel:
