Γενική φόρμουλα
=$A$1:INDEX($A:$A,lastrow)
Περίληψη
Ένας τρόπος για να δημιουργήσετε μια δυναμική ονομαστική περιοχή στο Excel είναι να χρησιμοποιήσετε τη συνάρτηση INDEX. Στο παράδειγμα που εμφανίζεται, η ονομαζόμενη περιοχή "δεδομένα" ορίζεται από τον ακόλουθο τύπο:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
που φθάνει στο εύρος $ A $ 2: $ A $ 10.
Σημείωση: αυτός ο τύπος προορίζεται να ορίσει μια ονομαστική περιοχή που μπορεί να χρησιμοποιηθεί σε άλλους τύπους.
Εξήγηση
Αυτή η σελίδα δείχνει ένα παράδειγμα δυναμικής ονομαστικής περιοχής που δημιουργήθηκε με τη συνάρτηση INDEX μαζί με τη συνάρτηση COUNTA. Οι δυναμικές ονομαστικές περιοχές επεκτείνονται αυτόματα και συστέλλονται όταν προστίθενται ή καταργούνται δεδομένα Είναι μια εναλλακτική λύση στη χρήση ενός πίνακα Excel, ο οποίος αλλάζει και το μέγεθος καθώς προστίθενται ή καταργούνται δεδομένα.
Η συνάρτηση INDEX επιστρέφει την τιμή σε μια δεδομένη θέση σε ένα εύρος ή πίνακα. Μπορείτε να χρησιμοποιήσετε το INDEX για να ανακτήσετε μεμονωμένες τιμές ή ολόκληρες σειρές και στήλες σε ένα εύρος. Αυτό που κάνει το INDEX ιδιαίτερα χρήσιμο για δυναμικές ονομαστικές περιοχές είναι ότι επιστρέφει πραγματικά μια αναφορά. Αυτό σημαίνει ότι μπορείτε να χρησιμοποιήσετε το INDEX για να δημιουργήσετε μια μικτή αναφορά όπως $ A $ 1: A100.
Στο παράδειγμα που εμφανίζεται, η ονομαζόμενη περιοχή "δεδομένα" ορίζεται από τον ακόλουθο τύπο:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
που φθάνει στο εύρος $ A $ 2: $ A $ 10.
Πώς λειτουργεί αυτός ο τύπος
Σημειώστε πρώτα ότι αυτός ο τύπος αποτελείται σε δύο μέρη που βρίσκονται και στις δύο πλευρές του τελεστή εύρους (:). Στα αριστερά, έχουμε την αρχική αναφορά για το εύρος, με σκληρή κωδικοποίηση ως:
$A$2
Στα δεξιά βρίσκεται η τελική αναφορά για το εύρος, που δημιουργήθηκε με INDEX ως εξής:
INDEX($A:$A,COUNTA($A:$A))
Εδώ, τροφοδοτούμε το INDEX όλη τη στήλη Α για τον πίνακα και, στη συνέχεια, χρησιμοποιούμε τη συνάρτηση COUNTA για να καταλάβουμε την "τελευταία σειρά" στην περιοχή. Το COUNTA λειτουργεί καλά εδώ, επειδή υπάρχουν 10 τιμές στη στήλη Α, συμπεριλαμβανομένης μιας γραμμής κεφαλίδας. Επομένως, η COUNTA επιστρέφει το 10, το οποίο πηγαίνει απευθείας στο INDEX ως τον αριθμό σειράς. Στη συνέχεια, το INDEX επιστρέφει μια αναφορά σε $ A $ 10, την τελευταία γραμμή που χρησιμοποιήθηκε στο εύρος:
INDEX($A:$A,10) // resolves to $A$10
Έτσι, το τελικό αποτέλεσμα του τύπου είναι αυτό το εύρος:
$A$2:$A$10
Ένα δισδιάστατο εύρος
Το παραπάνω παράδειγμα λειτουργεί για μονοδιάστατο εύρος. Για να δημιουργήσετε ένα δισδιάστατο δυναμικό εύρος όπου ο αριθμός των στηλών είναι επίσης δυναμικός, μπορείτε να χρησιμοποιήσετε την ίδια προσέγγιση, που επεκτείνεται ως εξής:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Όπως και πριν, το COUNTA χρησιμοποιείται για να καταλάβει το "lastrow" και χρησιμοποιούμε ξανά το COUNTA για να πάρουμε την "τελευταία στήλη". Αυτά παρέχονται στο ευρετήριο ως σειρά_num και στήλη_num αντίστοιχα.
Ωστόσο, για τον πίνακα, παρέχουμε το πλήρες φύλλο εργασίας, που έχει εισαχθεί και ως 10.48576 σειρές, κάτι που επιτρέπει στο INDEX να επιστρέψει μια αναφορά σε ένα 2D διάστημα.
Σημείωση: Το Excel 2003 υποστηρίζει μόνο 65535 σειρές.
Προσδιορισμός της τελευταίας σειράς
Υπάρχουν διάφοροι τρόποι για να προσδιορίσετε την τελευταία σειρά (τελευταία σχετική θέση) σε ένα σύνολο δεδομένων, ανάλογα με τη δομή και το περιεχόμενο των δεδομένων στο φύλλο εργασίας:
- Τελευταία σειρά σε μικτά δεδομένα με κενά
- Τελευταία σειρά σε μικτά δεδομένα χωρίς κενά
- Τελευταία σειρά σε δεδομένα κειμένου
- Τελευταία σειρά σε αριθμητικά δεδομένα
