Γενική φόρμουλα
=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))
Περίληψη
Ένας τρόπος για να δημιουργήσετε μια δυναμική ονομαστική περιοχή με έναν τύπο είναι να χρησιμοποιήσετε τη συνάρτηση OFFSET μαζί με τη συνάρτηση COUNTA. Τα δυναμικά εύρη είναι επίσης γνωστά ως επεκτεινόμενα εύρη - επεκτείνονται αυτόματα και συστέλλονται για να φιλοξενήσουν νέα ή διαγραμμένα δεδομένα.
Σημείωση: Το OFFSET είναι μια πτητική συνάρτηση, που σημαίνει ότι υπολογίζεται εκ νέου με κάθε αλλαγή σε ένα φύλλο εργασίας. Με ένα σύγχρονο μηχάνημα και μικρότερο σύνολο δεδομένων, αυτό δεν θα πρέπει να προκαλεί πρόβλημα, αλλά μπορεί να δείτε χαμηλότερη απόδοση σε μεγάλα σύνολα δεδομένων. Σε αυτήν την περίπτωση, σκεφτείτε να δημιουργήσετε μια δυναμική ονομαστική περιοχή με τη συνάρτηση INDEX.
Στο παράδειγμα που εμφανίζεται, ο τύπος που χρησιμοποιείται για τη δυναμική περιοχή είναι:
=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))
Εξήγηση
Αυτός ο τύπος χρησιμοποιεί τη συνάρτηση OFFSET για να δημιουργήσει ένα εύρος που επεκτείνεται και συστέλλεται ρυθμίζοντας το ύψος και το πλάτος με βάση τον αριθμό των κενών κελιών.
Το πρώτο όρισμα στο OFFSET αντιπροσωπεύει το πρώτο κελί στα δεδομένα (η προέλευση), το οποίο στην περίπτωση αυτή είναι το κελί B5. Τα επόμενα δύο ορίσματα είναι αντισταθμίσεις για σειρές και στήλες και παρέχονται ως μηδέν.
Τα δύο τελευταία ορίσματα αντιπροσωπεύουν ύψος και πλάτος. Το ύψος και το πλάτος δημιουργούνται εν κινήσει χρησιμοποιώντας το COUNTA, το οποίο καθιστά την προκύπτουσα αναφορά δυναμική.
Για ύψος, χρησιμοποιούμε τη συνάρτηση COUNTA για να μετρήσουμε μη κενές τιμές στην περιοχή B5: B100. Αυτό δεν προϋποθέτει κενές τιμές στα δεδομένα και καμία τιμή πέρα από το B100. Η COUNTA επιστρέφει 6.
Για πλάτος, χρησιμοποιούμε τη συνάρτηση COUNTA για να μετρήσουμε μη κενές τιμές στην περιοχή B5: Z5. Αυτό δεν προϋποθέτει κελιά κεφαλίδας και καμία κεφαλίδα πέρα από το Z5 Η COUNTA επιστρέφει 6.
Σε αυτό το σημείο, ο τύπος μοιάζει με αυτό:
=OFFSET(B5,0,0,6,6)
Με αυτές τις πληροφορίες, το OFFSET επιστρέφει μια αναφορά στο B5: G10, το οποίο αντιστοιχεί σε εύρος ύψους 6 σειρών κατά 6 στήλες.
Σημείωση: Τα εύρη που χρησιμοποιούνται για το ύψος και το πλάτος πρέπει να προσαρμοστούν ώστε να ταιριάζουν με τη διάταξη του φύλλου εργασίας.
Παραλλαγή με πλήρεις αναφορές στήλης / σειράς
Μπορείτε επίσης να χρησιμοποιήσετε αναφορές πλήρους στήλης και σειράς για ύψος και πλάτος όπως:
=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))
Σημειώστε ότι το ύψος προσαρμόζεται με -2 για να ληφθούν υπόψη οι τιμές κεφαλίδας και τίτλου στα κελιά B4 και B2. Το πλεονέκτημα αυτής της προσέγγισης είναι η απλότητα των περιοχών εντός της COUNTA. Το μειονέκτημα προέρχεται από τις τεράστια στήλες πλήρους μεγέθους και τις σειρές - πρέπει να ληφθεί μέριμνα για την αποφυγή λανθασμένων τιμών εκτός του εύρους, καθώς μπορούν εύκολα να ξεπεράσουν τον αριθμό.
Προσδιορισμός της τελευταίας σειράς
Υπάρχουν διάφοροι τρόποι για να προσδιορίσετε την τελευταία σειρά (τελευταία σχετική θέση) σε ένα σύνολο δεδομένων, ανάλογα με τη δομή και το περιεχόμενο των δεδομένων στο φύλλο εργασίας:
- Τελευταία σειρά σε μικτά δεδομένα με κενά
- Τελευταία σειρά σε μικτά δεδομένα χωρίς κενά
- Τελευταία σειρά σε δεδομένα κειμένου
- Τελευταία σειρά σε αριθμητικά δεδομένα
