Σε αυτό το άρθρο, θα μάθουμε πώς να χρησιμοποιούμε τη συνάρτηση OFFSET στο Excel.
Τι είναι OFFSET Λειτουργία?
Η λειτουργία Offset είναι μια λειτουργία ανάκτησης πληροφοριών. Εάν έχετε έναν τεράστιο πίνακα που περιέχει ορισμένα δεδομένα και θέλετε να ανακτήσετε δεδομένα από αυτόν, ο καλύτερος τρόπος για να το κάνετε είναι να εισαγάγετε έναν τύπο μετατόπισης για τον πίνακα σε αυτό το φύλλο εργασίας ή άλλο φύλλο στο ίδιο βιβλίο εργασίας. Έτσι, μόλις δημιουργήσετε τον πίνακα αντιστάθμισης ανάκτησης, πρέπει απλώς να εισαγάγετε τα δεδομένα για παράδειγμα «μήνας» και θα λάβετε τις «πωλήσεις» ή τα «έσοδα» εκείνου του μήνα από αυτόν τον πίνακα.
Η μετάβαση στον πίνακα είναι μια άλλη επιλογή, αλλά θα εξετάζατε την επιλογή εάν έχετε φύλλα και φύλλα δεδομένων που περιέχουν όλα έναν πίνακα δεδομένων 1000 στηλών; Εδώ συνδυάζεται το offset με άλλες λειτουργίες.
Η έννοια του περιστρεφόμενου πίνακα προέρχεται από την αντιστάθμιση. Ένας περιστρεφόμενος πίνακας είναι ένας γενικός πίνακας και από αυτόν τον συγκεκριμένο συνδυασμό δεδομένων και γραφημάτων λαμβάνεται όπως και όταν απαιτείται. Επίσης δυναμικοί πίνακες μπορούν να δημιουργηθούν με τον ίδιο τρόπο. Για αυτούς τους πίνακες θα πρέπει να δημιουργήσετε τον τύπο μετατόπισης excel
Σύνταξη του OFFSET
| = OFFSET (αναφορά, γραμμές, στήλες, [ύψος], [πλάτος]) |
Σειρές - ζητάτε από το excel να μετακινήσει τον αριθμό των γραμμών για να λάβετε τις πληροφορίες. Σε αυτήν την περίπτωση πρέπει να μετακινηθεί σε όλο τον πίνακα και έτσι απλά αφήνει ένα κενό που συμβολίζεται με κόμμα (,). Διαφορετικά, για να μετακινήσετε μια στήλη προς τα εμπρός δώστε τιμή ως 1 και για να μετακινήσετε μια στήλη πριν δώστε μια τιμή -1.
Στήλες - αυτό οδηγεί τη λειτουργία στον αριθμό των στηλών. Το σύστημα αξιών είναι ίδιο με αυτό της σειράς.
Υψος - το ύψος του τραπεζιού, στην περίπτωση αυτή A11.
Πλάτος - πλάτος πίνακα, στην περίπτωση αυτή D11.
Αφού ρυθμίσετε τη λειτουργία μετατόπισης, η εκτέλεση μιας δοκιμής είναι σημαντική για να εξαλειφθούν τυχόν σφάλματα.
Ας πάρουμε έναν πίνακα που περιέχει Όνομα, email id, ημερομηνία γέννησης και ηλικία. Ο πίνακας ξεκινά στο κελί A1, που περιέχει την επικεφαλίδα «Όνομα». Τα δεδομένα τρέχουν για το Α11. Και οι σειρές τρέχουν μέχρι το D1. Ολόκληρος ο πίνακας τρέχει A1: D11. Θέλετε ένα σύστημα ανάκτησης που σας δίνει το όνομα όταν εισάγετε το αναγνωριστικό ηλεκτρονικού ταχυδρομείου ή το αναγνωριστικό ηλεκτρονικού ταχυδρομείου όταν εισάγετε το όνομα μαζί με άλλες λεπτομέρειες. Δημιουργείτε έναν τύπο ικανοποιώντας τα 5 ορίσματα.
Το σημείο αναφοράς είναι το κελί από το οποίο πρέπει να ξεκινήσει η αναζήτηση. Σε αυτή την περίπτωση θα πρέπει να είναι Α2. Αυτό είναι το γενικό πρότυπο.
Παράδειγμα:
Για παράδειγμα, δίνετε αναφορά στο Β4 και στη γραμμή αντιστάθμισης 0, και στη στήλη μετατόπισης 1, τότε η τιμή από το Γ4 επιστρέφεται. Μπερδεμένο; Ας έχουμε μερικά ακόμη παραδείγματα. Το τελευταίο παράδειγμα της λειτουργίας OFFSET λέει πώς να αθροίζετε δυναμικά.
OFFSET Λειτουργία για να λάβετε αξία από δεξιά και αριστερά ενός κελιού
Έχουμε αυτόν τον πίνακα.
Τώρα αν θέλω να λάβω τιμή από 2 κύτταρα δεξιά σε B2 (που σημαίνει D2). Θα γράψω αυτόν τον τύπο OFFSET:
| = OFFSET (B2,0,2) |
Η συνάρτηση OFFSET θα μετακινήσει 2 κελιά στα δεξιά του κελιού Β4 και θα επιστρέψει την τιμή της. Δείτε την εικόνα παρακάτω
Εάν θέλω να λάβω τιμή από 1 κελί αριστερά στο Β2 (που σημαίνει Α2). Θα γράψω αυτόν τον τύπο OFFSET:
| = OFFSET (B2,0, -1) |
Το σύμβολο μείον (-) υποδεικνύει τη μετατόπιση για να μετακινηθεί αντίστροφα.
OFFSET Λειτουργία για να λάβετε αξία από κάτω και πάνω από ένα κελί
Έτσι και πάλι στον παραπάνω πίνακα, εάν θέλω να λάβω τιμή από το 2 cell cell στο B3 (που σημαίνει B5). Θα γράψω αυτόν τον τύπο OFFSET:
| = OFFSET (B2,2,0) |
Εάν θέλω να λάβω τιμή από 1 κελί αριστερά στο Β2 (που σημαίνει Α2). Θα γράψω αυτόν τον τύπο OFFSET:
| = OFFSET (Β2, -2,0) |
- Pro Συμβουλή: Αντιμετωπίστε ένα OFFSET ως δείκτη γραφήματος όπου η αναφορά είναι προέλευση και η γραμμή και η στήλη είναι άξονας x και y.
OFFSET Συνάρτηση για άθροισμα εύρους δυναμικά
Ας δούμε αυτό το παράδειγμα.
Στον παραπάνω πίνακα Συνολική σειρά στο τέλος του πίνακα. Με την πάροδο του χρόνου θα προσθέσετε σειρές σε αυτόν τον πίνακα. Στη συνέχεια, θα πρέπει να αλλάξετε το εύρος αθροίσματος στον τύπο. Εδώ μπορούμε να πάρουμε βοήθεια από τη συνάρτηση OFFSET για να αθροίσουμε δυναμικά. Αυτή τη στιγμή στο κελί C11 έχουμε = SUM (C2: C10). Αντικαταστήστε το με τον παρακάτω τύπο.
| = SUM (C2: OFFSET (C11, -1,0)) |
Αυτός ο τύπος παίρνει απλώς την πρώτη σειρά δεδομένων και στη συνέχεια αθροίζει το εύρος πάνω από τη συνολική γραμμή.
Offset For Getting Array
Η συνάρτηση OFFSET έχει δύο προαιρετικά ορίσματα, [ύψος] και [πλάτος]. Μην τα θεωρείτε δεδομένα. Όταν παρέχεται η συνάρτηση OFFSET [ύψος] και [πλάτος] ορίσματα, επιστρέφει έναν πίνακα δύο διαστάσεων. Εάν μόνο ένα από αυτά περάσει ως όρισμα, επιστρέφει σε πίνακα διαστάσεων τιμών.
Εάν πληκτρολογήσετε αυτόν τον τύπο σε οποιοδήποτε κελί:
| = SUM (OFFSET (C1,1,0,9,3)) |
Θα αθροίσει τιμές στο εύρος ξεκινώντας από C2 έως 9 γραμμές παρακάτω και 3 στήλες δεξιά.
OFFSET (C1,1,0,9,3): Αυτό μεταφράζεται σε {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4 · 6,6,5}.
Συμπερασματικά, το offset είναι πολύ χρήσιμη λειτουργία του excel που μπορεί να σας βοηθήσει να λύσετε πολλά στραγγαλισμένα νήματα. Ενημερώστε με πώς χρησιμοποιείτε τη λειτουργία OFFSET στον τύπο σας και πού σας βοήθησε περισσότερο.
Η λειτουργία αναζήτησης είναι η ίδια με τη μετατόπιση αλλά χρησιμοποιεί συναρτήσεις όπως Match, Counta και IF για να λειτουργήσει ένας δυναμικός πίνακας.
Η χρήση του offset στο Excel απαιτεί δεξιότητες και καλή γνώση των συναρτήσεων Excels και του τρόπου συνεργασίας τους. Η απόσυρσή του χωρίς κανένα σφάλμα είναι το μεγαλύτερο εμπόδιο.
Εδώ είναι όλες οι σημειώσεις παρατήρησης σχετικά με τη χρήση του τύπου.
Σημειώσεις:
- Αυτός ο τύπος λειτουργεί τόσο με κείμενο όσο και με αριθμούς.
- Υπάρχουν πέντε επιχειρήματα της αντιστάθμισης που πρέπει να ικανοποιηθούν για να λάβετε πληροφορίες χωρίς σφάλματα. Εάν γράψετε έναν λανθασμένο τύπο, εμφανίζεται ένα σφάλμα Ref.
Ελπίζω να καταλάβατε τι είναι η λειτουργία Excel Offset και πώς να τη χρησιμοποιήσετε στο Excel. Εξερευνήστε περισσότερα άρθρα σχετικά με τις τιμές αναζήτησης και αντιστοίχισης Excel χρησιμοποιώντας τύπους εδώ. Αν σας άρεσαν τα ιστολόγια μας, μοιραστείτε το με τους φίλους σας στο Facebook. Επίσης, μπορείτε να μας ακολουθήσετε στο Twitter και το Facebook. Θα θέλαμε πολύ να σας ακούσουμε, ενημερώστε μας πώς μπορούμε να βελτιώσουμε, να συμπληρώσουμε ή να καινοτομήσουμε το έργο μας και να το κάνουμε καλύτερο για εσάς. Γράψτε μας στον ιστότοπο email.
Συνάρτηση VLOOKUP για τον υπολογισμό της βαθμολογίας στο Excel : Για τον υπολογισμό των βαθμών IF και IFS δεν είναι οι μόνες συναρτήσεις που μπορείτε να χρησιμοποιήσετε. Το VLOOKUP είναι πιο αποτελεσματικό και δυναμικό για τέτοιους υπολογισμούς υπό όρους. Για να υπολογίσετε βαθμούς χρησιμοποιώντας το VLOOKUP μπορούμε να χρησιμοποιήσουμε αυτόν τον τύπο.
17 πράγματα για το Excel VLOOKUP : Το VLOOKUP χρησιμοποιείται συχνότερα για την ανάκτηση αντιστοιχισμένων τιμών, αλλά το VLOOKUP μπορεί να κάνει πολλά περισσότερα από αυτό. Ακολουθούν 17 πράγματα για το VLOOKUP που πρέπει να γνωρίζετε για να τα χρησιμοποιήσετε αποτελεσματικά.
ΔΕΙΤΕ το πρώτο κείμενο από μια λίστα στο Excel : Η λειτουργία VLOOKUP λειτουργεί καλά με χαρακτήρες μπαλαντέρ. Μπορούμε να το χρησιμοποιήσουμε για να εξαγάγουμε την πρώτη τιμή κειμένου από μια συγκεκριμένη λίστα στο excel. Εδώ είναι ο γενικός τύπος.
LOOKUP ημερομηνία με την τελευταία τιμή στη λίστα : Για να ανακτήσουμε την ημερομηνία που περιέχει την τελευταία τιμή χρησιμοποιούμε τη συνάρτηση LOOKUP. Αυτή η συνάρτηση ελέγχει για το κελί που περιέχει την τελευταία τιμή σε ένα διάνυσμα και στη συνέχεια χρησιμοποιεί αυτήν την αναφορά για να επιστρέψει την ημερομηνία.
Πώς να ανακτήσετε την πιο πρόσφατη τιμή στο Excel : Είναι συνηθισμένο να ενημερώνονται οι τιμές σε οποιαδήποτε επιχείρηση και η χρήση των πιο πρόσφατων τιμών για οποιαδήποτε αγορά ή πώληση είναι απαραίτητη. Για να ανακτήσετε την πιο πρόσφατη τιμή από μια λίστα στο Excel, χρησιμοποιούμε τη συνάρτηση LOOKUP. Η λειτουργία LOOKUP συγκεντρώνει την πιο πρόσφατη τιμή.
Δημοφιλή άρθρα:
50 Συντόμευση Excel για να αυξήσετε την παραγωγικότητά σας : Προχωρήστε γρηγορότερα στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Πώς να χρησιμοποιήσετε τη συνάρτηση IF στο Excel : Η δήλωση IF στο Excel ελέγχει την κατάσταση και επιστρέφει μια συγκεκριμένη τιμή εάν η συνθήκη είναι TRUE ή επιστρέφει μια άλλη συγκεκριμένη τιμή εάν είναι FALSE.
Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP στο Excel : Αυτή είναι μια από τις πιο χρησιμοποιούμενες και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
Πώς να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel : Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένες τιμές. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel : Αυτή είναι μια άλλη βασική λειτουργία του πίνακα ελέγχου. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.