Τα δεδομένα σας στο Excel αλλάζουν συχνά, επομένως είναι χρήσιμο να δημιουργήσετε ένα δυναμικό καθορισμένο εύρος που επεκτείνεται και συστέλλεται αυτόματα στο μέγεθος του εύρους δεδομένων σας. Ας δούμε πώς.
Χρησιμοποιώντας ένα δυναμικό καθορισμένο εύρος, δεν θα χρειάζεται να επεξεργάζεστε με μη αυτόματο τρόπο τις περιοχές των τύπων, των γραφημάτων και των Συγκεντρωτικών Πίνακών σας όταν αλλάζουν τα δεδομένα. Αυτό θα συμβεί αυτόματα.
Δύο τύποι χρησιμοποιούνται για τη δημιουργία δυναμικών περιοχών: OFFSET και INDEX. Αυτό το άρθρο θα επικεντρωθεί στη χρήση της συνάρτησης INDEX καθώς είναι μια πιο αποτελεσματική προσέγγιση. Το OFFSET είναι μια πτητική συνάρτηση και μπορεί να επιβραδύνει μεγάλα υπολογιστικά φύλλα.
Πίνακας περιεχομένων
Δημιουργήστε ένα δυναμικό καθορισμένο εύρος στο Excel
Για το πρώτο μας παράδειγμα, έχουμε τη λίστα δεδομένων μιας στήλης που φαίνεται παρακάτω.
Χρειαζόμαστε αυτό το στοιχείο για να είναι δυναμικό, ώστε αν προστεθούν ή καταργηθούν περισσότερες χώρες, το εύρος ενημερώνεται αυτόματα.
Για αυτό το παράδειγμα, θέλουμε να αποφύγουμε το κελί κεφαλίδας. Ως εκ τούτου, θέλουμε το εύρος $A$2:$A$6, αλλά δυναμικό. Κάντε αυτό κάνοντας κλικ στην επιλογή Τύποι > Ορισμός ονόματος.
Πληκτρολογήστε “χώρες” στο πλαίσιο “Όνομα” και, στη συνέχεια, εισαγάγετε τον παρακάτω τύπο στο πλαίσιο “Αναφέρεται σε”.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Η πληκτρολόγηση αυτής της εξίσωσης σε ένα κελί υπολογιστικού φύλλου και στη συνέχεια η αντιγραφή της στο πλαίσιο Νέο όνομα είναι μερικές φορές πιο γρήγορη και ευκολότερη.
Πως λειτουργεί αυτό?
Το πρώτο μέρος του τύπου καθορίζει το κελί έναρξης της περιοχής (A2 στην περίπτωσή μας) και στη συνέχεια ακολουθεί ο τελεστής περιοχής (:).
=$A$2:
Η χρήση του τελεστή εύρους αναγκάζει τη συνάρτηση INDEX να επιστρέψει μια περιοχή αντί για την τιμή ενός κελιού. Στη συνέχεια, η συνάρτηση INDEX χρησιμοποιείται με τη συνάρτηση COUNTA. Το COUNTA μετράει τον αριθμό των μη κενών κελιών στη στήλη Α (έξι στην περίπτωσή μας).
INDEX($A:$A,COUNTA($A:$A))
Αυτός ο τύπος ζητά από τη συνάρτηση INDEX να επιστρέψει το εύρος του τελευταίου μη κενού κελιού στη στήλη Α ($A$6).
Το τελικό αποτέλεσμα είναι $A$2:$A$6 και λόγω της συνάρτησης COUNTA, είναι δυναμικό, καθώς θα βρει την τελευταία σειρά. Τώρα μπορείτε να χρησιμοποιήσετε αυτό το καθορισμένο όνομα “χώρες” μέσα σε έναν κανόνα επικύρωσης δεδομένων, τύπο, γράφημα ή οπουδήποτε χρειάζεται να αναφέρουμε τα ονόματα όλων των χωρών.
Δημιουργήστε ένα αμφίδρομο δυναμικό καθορισμένο εύρος
Το πρώτο παράδειγμα ήταν μόνο δυναμικό σε ύψος. Ωστόσο, με μια μικρή τροποποίηση και μια άλλη συνάρτηση COUNTA, μπορείτε να δημιουργήσετε ένα εύρος που να είναι δυναμικό τόσο από ύψος όσο και από πλάτος.
Σε αυτό το παράδειγμα, θα χρησιμοποιήσουμε τα δεδομένα που φαίνονται παρακάτω.
Αυτή τη φορά, θα δημιουργήσουμε ένα δυναμικό καθορισμένο εύρος, το οποίο περιλαμβάνει τις κεφαλίδες. Κάντε κλικ στην επιλογή Τύποι > Ορισμός ονόματος.
Πληκτρολογήστε “”sales” στο πλαίσιο “Όνομα” και εισαγάγετε τον παρακάτω τύπο στο πλαίσιο “Αναφορά σε”.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Αυτός ο τύπος χρησιμοποιεί $A$1 ως κελί έναρξης. Στη συνέχεια, η συνάρτηση INDEX χρησιμοποιεί ένα εύρος ολόκληρου του φύλλου εργασίας ($1:$1048576) για αναζήτηση και επιστροφή.
Μία από τις συναρτήσεις COUNTA χρησιμοποιείται για την καταμέτρηση των μη κενών σειρών και μια άλλη χρησιμοποιείται για τις μη κενές στήλες καθιστώντας τη δυναμική και προς τις δύο κατευθύνσεις. Αν και αυτός ο τύπος ξεκίνησε από το A1, θα μπορούσατε να έχετε καθορίσει οποιοδήποτε κελί έναρξης.
Τώρα μπορείτε να χρησιμοποιήσετε αυτό το καθορισμένο όνομα (πωλήσεις) σε έναν τύπο ή ως μια σειρά δεδομένων γραφήματος για να τα κάνετε δυναμικά.