Η λειτουργία OFFSET του Excel θα επιβραδύνει τον υπολογισμό του βιβλίου εργασίας σας. Υπάρχει μια καλύτερη εναλλακτική λύση: μια ασυνήθιστη σύνταξη του INDEX.
Αυτή είναι μια εξειδικευμένη συμβουλή. Υπάρχει μια εκπληκτικά ευέλικτη λειτουργία που ονομάζεται OFFSET. Είναι ευέλικτο, επειδή μπορεί να δείχνει ένα εύρος διαφορετικού μεγέθους που υπολογίζεται εν κινήσει. Στην παρακάτω εικόνα, εάν κάποιος αλλάξει το αναπτυσσόμενο μενού # Qtrs στο H1 από 3 σε 4, το τέταρτο όρισμα του OFFSET θα διασφαλίσει ότι το εύρος επεκτείνεται ώστε να περιλαμβάνει τέσσερις στήλες.
Οι γκουρού του υπολογιστικού φύλλου μισούν το OFFSET επειδή είναι μια πτητική λειτουργία. Εάν μεταβείτε σε ένα εντελώς άσχετο κελί και εισαγάγετε έναν αριθμό, θα υπολογιστούν όλες οι συναρτήσεις OFFSET. Ακόμα κι αν αυτό το κελί δεν έχει καμία σχέση με το H1 ή το B2. Τις περισσότερες φορές, το Excel είναι πολύ προσεκτικό να σπαταλά μόνο χρόνο υπολογίζοντας τα κελιά που πρέπει να υπολογίσουν. Αλλά μόλις εισαγάγετε το OFFSET, όλα τα κελιά OFFSET, καθώς και όλα τα downline από το OFFSET, αρχίζουν να υπολογίζονται μετά από κάθε αλλαγή στο φύλλο εργασίας.
Κρίνω τους τελικούς του ModelOff του 2013 στη Νέα Υόρκη όταν μερικοί από τους φίλους μου από την Αυστραλία επεσήμαναν μια παράξενη λύση. Στον παρακάτω τύπο, υπάρχει άνω και κάτω τελεία πριν από τη συνάρτηση INDEX. Κανονικά, η συνάρτηση INDEX που φαίνεται παρακάτω θα επιστρέψει το 1403 από το κελί D2. Αλλά όταν τοποθετείτε ένα άνω και κάτω τελεία σε κάθε πλευρά της συνάρτησης INDEX, αρχίζει να επιστρέφει τη διεύθυνση κελιού D2 αντί για τα περιεχόμενα του D2. Αυτό είναι άγριο που λειτουργεί.
Γιατί έχει σημασία; Το INDEX δεν είναι ασταθές. Παίρνετε όλη την ευέλικτη καλοσύνη του OFFSET χωρίς τους επανειλημμένους υπολογισμούς ξανά.
Έμαθα για πρώτη φορά αυτή τη συμβουλή από τον Dan Mayoh στο Fintega. Χάρη στην Access Analytic για την πρόταση αυτής της λειτουργίας.
Δες το βίντεο
Μεταγραφή βίντεο
Μάθετε το Excel από το podcast, επεισόδιο 2048 -: Το INDEX θα αντικαταστήσει ένα πτητικό OFFSET!
Γεια, μεταδίδω όλες τις συμβουλές μου από αυτό το βιβλίο, κάντε κλικ στο "i" στην επάνω δεξιά γωνία για να μεταβείτε στη λίστα αναπαραγωγής!
Εντάξει, το OFFSET είναι μια καταπληκτική λειτουργία! Το OFFSET μας επιτρέπει να καθορίσουμε ένα κελί πάνω αριστεράς γωνίας και, στη συνέχεια, να χρησιμοποιήσουμε μεταβλητές για να καθορίσουμε από εκεί πόσες σειρές κάτω, πόσες σειρές πάνω, και στη συνέχεια να ορίσουμε ένα σχήμα, εντάξει. Εδώ λοιπόν, αν θέλω να προσθέσω, ή να κάνω ένα μέσο όρο, ας πούμε 3/4, αυτός ο τύπος εδώ θα ρίξει μια ματιά στον τύπο. Το OFFSET λέει ότι ξεκινάμε από το B2, ξεκινώντας από το Q1, κατεβαίνουμε 0, πάνω από 0, το σχήμα θα έχει ύψος 1 σειράς και θα είναι H1, με άλλα λόγια πλάτος 3 κελιών, εντάξει. Έτσι, σε αυτήν την περίπτωση, το μόνο που κάνουμε είναι να αλλάξουμε πόσα κελιά προσθέτουμε, αρχίζουμε πάντα πίσω στο B2 ή στο B3 ή στο B4 καθώς αντιγράφω και στη συνέχεια αποφασίζει πόσα κελιά έχουν πλάτος. Εντάξει, το OFFSET είναι αυτό το υπέροχο πράγμα, κάνει κάθε είδους εκπληκτικές λειτουργίες, αλλά εδώ είναι η ταλαιπωρία, είναι ασταθές!Αυτό σημαίνει ότι ακόμη και αν κάτι δεν είναι στην αλυσίδα υπολογισμού, το Excel, θα πάρει τον χρόνο να τον υπολογίσει ξανά, κάτι που θα επιβραδύνει τα πράγματα, εντάξει.
Αυτή η εκπληκτική έκδοση του INDEX, σωστά, κανονικά αν ζητήσω το INDEX από αυτά τα 4 κελιά, 3, θα επιστρέψει τον αριθμό 1403. Ωστόσο, όταν έβαλα ένα άνω και κάτω τελεία πριν ή μετά το INDEX, συμβαίνει κάτι πολύ διαφορετικό, θα ρίξουμε μια ματιά σε αυτόν τον τύπο εδώ. Έτσι, ευρετήριο των 4 κελιών, ποιο θέλω, θέλω το 3ο, αλλά βλέπετε ότι υπάρχει: ακριβώς εκεί. Έτσι πάντοτε πηγαίνουμε από το B2: E2 και θα σας δείξω αν πηγαίνουμε στους τύπους, αξιολογήστε τον τύπο, εντάξει, έτσι εδώ θα υπολογίσει τον αριθμό 3. Και εδώ, ο δείκτης με το: επόμενο σε αυτό, αντί να μας πείτε 1403, που θα υπολογίζει κανονικά το INDEX, θα επιστρέψει $ D2 και, στη συνέχεια, ο μέσος όρος θα κάνει τον μέσο όρο αυτών των 3. Απολύτως καταπληκτικός, ο τρόπος που λειτουργεί και το πρόσθετο όφελος, δεν είναι πτητικό, εντάξει,και αυτό μπορεί ακόμη και να χρησιμοποιηθεί για να αντικαταστήσει ένα απίστευτα περίπλοκο OFFSET.
Εδώ λοιπόν με αυτό το OFFSET βασίζουμε αυτές τις τιμές. Αν επιλέξω Q2 και Central, εντάξει, αυτοί οι τύποι χρησιμοποιούν MATCH και COUNTIF για να υπολογίσουν πόσες σειρές κάτω, πόσες στήλες πάνω, πόσο ψηλές, πόσο πλάτος. Και τότε το OFFSET εδώ χρησιμοποιεί όλες αυτές τις τιμές για να καταλάβει τη διάμεση τιμή. Θα κάνουμε απλώς μια δοκιμή για να βεβαιωθούμε ότι λειτουργεί, οπότε = MEDIAN αυτής της μπλε σειράς εκεί, καλύτερα να είναι 71, εντάξει, και θα επιλέξουμε κάτι άλλο εδώ, Q3 και West, έτσι. Πατήστε F2, απλώς πρόκειται να το μεταφέρω και να αλλάξω το μέγεθός του για να ξαναγράψω αυτόν τον τύπο, πατήστε Enter και λειτουργεί με το OFFSET.
Λοιπόν εδώ, χρησιμοποιώντας ένα INDEX, έχω πραγματικά ένα άνω και κάτω τελεία με ένα INDEX στην αριστερή πλευρά και ένα INDEX στη δεξιά πλευρά, παρακολουθήστε αυτό το πράγμα να υπολογίζεται στον τύπο αξιολόγησης. Ξεκινά λοιπόν, θα αξιολογήσει, θα αξιολογήσει και ακριβώς εδώ ότι το INDEX πρόκειται να το μετατρέψει σε διεύθυνση κελιού. Έτσι το H16 είναι το 1ο, West, Q3, και πάνω από τη δεξιά πλευρά θα αξιολογήσει, ζευγαρώσει περισσότερο, και στη συνέχεια δεξιά της αλλάζει σε I20. Έτσι, το δροσερό, δροσερό μη πτητικό για αντικατάσταση ενός OFFSET χρησιμοποιώντας τη λειτουργία INDEX. Εντάξει, αυτή η συμβουλή και πολλά άλλα σε αυτό το βιβλίο, κάντε κλικ στο "i" στην επάνω δεξιά γωνία για να αγοράσετε το βιβλίο.
Εντάξει ανακεφαλαίωση: OFFSET, φοβερή, ευέλικτη λειτουργία, μόλις το καταφέρετε, μπορείτε να κάνετε κάθε είδους πράγματα. Δείξτε ένα μεταβλητό επάνω αριστερό κελί, δείξτε ένα εύρος που έχει μεταβλητό σχήμα, αλλά είναι πτητικό και έτσι υπολογίζουν σε κάθε υπολογισμό. Το Excel συνήθως κάνει έναν έξυπνο υπολογισμό ή υπολογίζει εκ νέου τα κελιά που πρέπει να υπολογιστούν, αλλά με το OFFSET θα υπολογίζεται πάντα. Αντί για το OFFSET μπορείτε να χρησιμοποιήσετε το INDEX: ή: INDEX ή ακόμα και το INDEX: INDEX, όποτε το INDEX έχει άνω και κάτω τελεία δίπλα του, θα επιστρέψει μια διεύθυνση κελιού αντί για την τιμή αυτού του κελιού. Και το όφελος είναι ότι το INDEX δεν είναι ασταθές!
Εντάξει, θέλω να σας ευχαριστήσω που σταματήσατε, θα σας δούμε την επόμενη φορά για ένα άλλο netcast από!
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2048.xlsm
