Μετάβαση στο κύριο περιεχόμενο

Πώς να δημιουργήσετε δυναμική ονομαστική περιοχή στο Excel;

Κανονικά, Ονοματοθεσία είναι πολύ χρήσιμα για τους χρήστες του Excel, μπορείτε να ορίσετε μια σειρά τιμών σε μια στήλη, να δώσετε σε αυτήν τη στήλη ένα όνομα και, στη συνέχεια, μπορείτε να αναφερθείτε σε αυτό το εύρος με όνομα αντί για τις αναφορές κελιών. Αλλά τις περισσότερες φορές, πρέπει να προσθέσετε νέα δεδομένα για να επεκτείνετε τις τιμές δεδομένων του εύρους αναφοράς στο μέλλον. Σε αυτήν την περίπτωση, πρέπει να επιστρέψετε ΜΑΘΗΜΑΤΙΚΟΙ τυποι > Όνομα Διευθυντής και επαναπροσδιορίστε το εύρος ώστε να συμπεριλάβει τη νέα τιμή. Για να το αποφύγετε, μπορείτε να δημιουργήσετε μια δυναμική ονομαστική περιοχή που σημαίνει ότι δεν χρειάζεται να προσαρμόζετε τις αναφορές κελιού κάθε φορά που προσθέτετε μια νέα σειρά ή στήλη στη λίστα.

Δημιουργήστε δυναμική περιοχή με όνομα στο Excel δημιουργώντας έναν πίνακα

Δημιουργήστε δυναμική ονομαστική περιοχή στο Excel με λειτουργία

Δημιουργία δυναμικής ονομαστικής περιοχής στο Excel με κώδικα VBA


βέλος μπλε δεξιά φούσκα Δημιουργήστε δυναμική περιοχή με όνομα στο Excel δημιουργώντας έναν πίνακα

Εάν χρησιμοποιείτε εκδόσεις Excel 2007 ή νεότερες εκδόσεις, ο ευκολότερος τρόπος για να δημιουργήσετε μια δυναμική περιοχή με όνομα είναι να δημιουργήσετε έναν πίνακα με το όνομα Excel.

Ας πούμε, έχετε μια σειρά από ακόλουθα δεδομένα που πρέπει να γίνουν δυναμικά εύρη ονομασίας.

doc-δυναμικό εύρος1

1. Πρώτον, θα ορίσω ονόματα εύρους για αυτό το εύρος. Επιλέξτε το εύρος A1: A6 και εισαγάγετε το όνομα Ημερομηνία μέσα στο όνομα Box, Στη συνέχεια πατήστε εισάγετε κλειδί. Για να ορίσετε ένα όνομα για το εύρος B1: B6 ως Saleprice με τον ίδιο τρόπο. Ταυτόχρονα, δημιουργώ έναν τύπο = άθροισμα (Saleprice) σε ένα κενό κελί, δείτε το στιγμιότυπο οθόνης:

doc-δυναμικό εύρος2

2. Επιλέξτε το εύρος και κάντε κλικ Κύριο θέμα > Τραπέζι, δείτε το στιγμιότυπο οθόνης:

doc-δυναμικό εύρος3

3. Στην Δημιουργία πίνακα πλαίσιο προτροπής, επιλέξτε Ο πίνακας μου έχει κεφαλίδες (εάν το εύρος δεν έχει κεφαλίδες, αποεπιλέξτε το), κάντε κλικ στο OK κουμπί και τα δεδομένα εύρους έχουν μετατραπεί σε πίνακα. Δείτε στιγμιότυπα οθόνης:

doc-δυναμικό εύρος4 -2 doc-δυναμικό εύρος5

4. Και όταν εισαγάγετε νέες τιμές μετά τα δεδομένα, το εύρος που ονομάζεται θα προσαρμόζεται αυτόματα και ο τύπος που δημιουργήθηκε θα αλλάξει επίσης. Δείτε τα ακόλουθα στιγμιότυπα οθόνης:

doc-δυναμικό εύρος6 -2 doc-δυναμικό εύρος7

:

1. Τα νέα σας δεδομένα εισαγωγής πρέπει να είναι δίπλα στα παραπάνω δεδομένα, αυτό σημαίνει ότι δεν υπάρχουν κενές σειρές ή στήλες μεταξύ των νέων δεδομένων και των υπαρχόντων δεδομένων.

2. Στον πίνακα, μπορείτε να εισαγάγετε δεδομένα μεταξύ των υπαρχουσών τιμών.


βέλος μπλε δεξιά φούσκα Δημιουργήστε δυναμική ονομαστική περιοχή στο Excel με λειτουργία

Στο Excel 2003 ή σε παλαιότερη έκδοση, η πρώτη μέθοδος δεν θα είναι διαθέσιμη, οπότε είναι ένας άλλος τρόπος για εσάς Το ακόλουθο ΑΝΤΙΣΤΑΘΜΙΖΕΤΑΙ( ) Η λειτουργία μπορεί να κάνει αυτή τη χάρη για εσάς, αλλά είναι κάπως ενοχλητικό. Ας υποθέσουμε ότι έχω μια σειρά δεδομένων που περιέχει τα ονόματα εύρους που έχω ορίσει, για παράδειγμα, A1: A6 το όνομα εύρους είναι Ημερομηνία, να Β1: Β6 το όνομα εύρους είναι Τιμή πώλησης, ταυτόχρονα, δημιουργώ έναν τύπο για το Τιμή πώλησης. Δείτε το στιγμιότυπο οθόνης:

doc-δυναμικό εύρος2

Μπορείτε να αλλάξετε τα ονόματα εύρους σε ονόματα δυναμικής περιοχής με τα ακόλουθα βήματα:

1. Πηγαίνετε στο κλικ ΜΑΘΗΜΑΤΙΚΟΙ τυποι > Όνομα Διευθυντής, δείτε το στιγμιότυπο οθόνης:

doc-δυναμικό εύρος8

2. Στην Όνομα Διευθυντής πλαίσιο διαλόγου, επιλέξτε το στοιχείο που θέλετε να χρησιμοποιήσετε και κάντε κλικ στο Αλλαγή κουμπί.

doc-δυναμικό εύρος9

3. Στο αναδυόμενο Επεξεργασία ονόματος διαλόγου, εισαγάγετε αυτόν τον τύπο = OFFSET (Φύλλο1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1) μέσα στο Αναφέρεται σε πλαίσιο κειμένου, δείτε το στιγμιότυπο οθόνης:

doc-δυναμικό εύρος10

4. Στη συνέχεια κάντε κλικ στο κουμπί OKκαι, στη συνέχεια, επαναλάβετε τα βήματα 2 και 3 για να αντιγράψετε αυτόν τον τύπο = OFFSET (Φύλλο1! $ B $ 1, 0, 0, COUNTA ($ B: $ B), 1) στο Αναφέρεται σε πλαίσιο κειμένου για το Τιμή πώλησης όνομα εύρους.

5. Και η δυναμική ονομαστική περιοχή έχει δημιουργηθεί. Όταν εισάγετε νέες τιμές μετά τα δεδομένα, το εύρος που ονομάζεται θα προσαρμόζεται αυτόματα και ο τύπος που δημιουργήθηκε θα αλλάξει επίσης. Δείτε στιγμιότυπα οθόνης:

doc-δυναμικό εύρος6 -2 doc-δυναμικό εύρος7

Σημείωση: Εάν υπάρχουν κενά κελιά στη μέση του εύρους σας, το αποτέλεσμα του τύπου σας θα είναι λάθος. Αυτό συμβαίνει επειδή τα μη κενά κελιά δεν υπολογίζονται, επομένως το εύρος σας θα είναι μικρότερο από το κανονικό και τα τελευταία κελιά στο εύρος θα παραμείνουν εκτός.

Συμβουλή: εξήγηση για αυτόν τον τύπο:

  • = OFFSET (αναφορά, σειρές, στήλες, [ύψος], [πλάτος])
  • -1
  • = OFFSET (Φύλλο1! $ A $ 1, 0, 0, COUNTA ($ A: $ A), 1)
  • αναφορά αντιστοιχεί στην αρχική θέση κελιού, σε αυτό το παράδειγμα Φύλλο1! $ A $ 1;
  • σειρά αναφέρεται στον αριθμό σειρών που πρόκειται να μετακινηθείτε προς τα κάτω, σε σχέση με το αρχικό κελί (ή προς τα πάνω, εάν χρησιμοποιείτε αρνητική τιμή.), σε αυτό το παράδειγμα, το 0 δείχνει ότι η λίστα θα ξεκινήσει από την πρώτη σειρά προς τα κάτω
  • στήλη αντιστοιχεί στον αριθμό των στηλών που θα μετακινηθείτε προς τα δεξιά, σε σχέση με το αρχικό κελί (ή προς τα αριστερά, χρησιμοποιώντας μια αρνητική τιμή.), στον παραπάνω τύπο τύπου, το 0 δείχνει επέκταση 0 στηλών προς τα δεξιά.
  • [ύψος] αντιστοιχεί στο ύψος (ή τον αριθμό σειρών) του εύρους ξεκινώντας από τη ρυθμισμένη θέση. $ A: $ A, θα μετρήσει όλα τα στοιχεία που έχουν εισαχθεί στη στήλη A.
  • [πλάτος] αντιστοιχεί στο πλάτος (ή τον αριθμό των στηλών) του εύρους ξεκινώντας από την προσαρμοσμένη θέση. Στον παραπάνω τύπο, η λίστα θα έχει πλάτος 1 στήλης.

Μπορείτε να αλλάξετε αυτά τα επιχειρήματα ανάλογα με τις ανάγκες σας.


βέλος μπλε δεξιά φούσκα Δημιουργία δυναμικής ονομαστικής περιοχής στο Excel με κώδικα VBA

Εάν έχετε πολλές στήλες, θα μπορούσατε να επαναλάβετε και να εισαγάγετε μεμονωμένο τύπο για όλες τις υπόλοιπες στήλες, αλλά αυτή θα ήταν μια μακρά, επαναλαμβανόμενη διαδικασία. Για να διευκολύνετε τα πράγματα, μπορείτε να χρησιμοποιήσετε έναν κωδικό για να δημιουργήσετε αυτόματα τη δυναμική ονομαστική περιοχή.

1. Ενεργοποιήστε το φύλλο εργασίας σας.

2. Κρατήστε πατημένο το ALT + F11 και ανοίγει το Παράθυρο Microsoft Visual Basic for Applications.

3. Κλίκ Κύριο θέμα > Μονάδα μέτρησηςκαι επικολλήστε τον ακόλουθο κώδικα στο Παράθυρο ενότητας.

Κωδικός Vba: δημιουργία δυναμικής ονομαστικής περιοχής

Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
        wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
Next
End Sub

4. Στη συνέχεια πατήστε F5 κλειδί για την εκτέλεση του κώδικα, και θα δημιουργηθούν ορισμένες δυναμικές ονομαστικές περιοχές που ονομάζονται με τις τιμές της πρώτης σειράς και δημιουργεί επίσης μια δυναμική περιοχή που ονομάζεται Τα δεδομένα μου που καλύπτει όλα τα δεδομένα.

5. Όταν εισαγάγετε νέες τιμές μετά τις σειρές ή τις στήλες, το εύρος θα επεκταθεί επίσης. Δείτε στιγμιότυπα οθόνης:

doc-δυναμικό εύρος12
-1
doc-δυναμικό εύρος13

:

1. Με αυτόν τον κωδικό, τα ονόματα εύρους δεν εμφανίζονται στο όνομα Box, για να βλέπω και να χρησιμοποιώ τα ονόματα εύρους, έχω εγκαταστήσει Kutools για Excel, Με τους Παράθυρο περιήγησης, αναφέρονται τα ονόματα δυναμικής περιοχής.

2. Με αυτόν τον κωδικό, ολόκληρο το εύρος των δεδομένων μπορεί να επεκταθεί κάθετα ή οριζόντια, αλλά να θυμάστε ότι δεν πρέπει να υπάρχουν κενές σειρές ή στήλες μεταξύ των δεδομένων όταν εισάγετε νέες τιμές.

3. Όταν χρησιμοποιείτε αυτόν τον κωδικό, το εύρος δεδομένων σας πρέπει να ξεκινά από το κελί A1.


Σχετικό άρθρο:

Πώς να ενημερώσετε αυτόματα ένα γράφημα μετά την εισαγωγή νέων δεδομένων στο Excel;

Τα καλύτερα εργαλεία παραγωγικότητας γραφείου

🤖 Kutools AI Aide: Επανάσταση στην ανάλυση δεδομένων με βάση: Ευφυής Εκτέλεση   |  Δημιουργία κώδικα  |  Δημιουργία προσαρμοσμένων τύπων  |  Αναλύστε δεδομένα και δημιουργήστε γραφήματα  |  Επίκληση Λειτουργιών Kutools...
Δημοφιλή χαρακτηριστικά: Εύρεση, επισήμανση ή αναγνώριση διπλότυπων   |  Διαγραφή κενών γραμμών   |  Συνδυάστε στήλες ή κελιά χωρίς απώλεια δεδομένων   |   Γύρος χωρίς φόρμουλα ...
Σούπερ Αναζήτηση: VLookup πολλαπλών κριτηρίων    VLookup πολλαπλών τιμών  |   VLookup σε πολλά φύλλα   |   Ασαφής αναζήτηση ....
Σύνθετη αναπτυσσόμενη λίστα: Γρήγορη δημιουργία αναπτυσσόμενης λίστας   |  Εξαρτημένη αναπτυσσόμενη λίστα   |  Πολλαπλή αναπτυσσόμενη λίστα ....
Διαχειριστής στήλης: Προσθέστε έναν συγκεκριμένο αριθμό στηλών  |  Μετακίνηση στηλών  |  Εναλλαγή κατάστασης ορατότητας κρυφών στηλών  |  Συγκρίνετε εύρη και στήλες ...
Επιλεγμένα Χαρακτηριστικά: Εστίαση πλέγματος   |  Προβολή σχεδίου   |   Μεγάλη Formula Bar    Διαχείριση βιβλίου εργασίας & φύλλου   |  Βιβλιοθήκη πόρων (Αυτόματο κείμενο)   |  Επιλογή ημερομηνίας   |  Συνδυάστε φύλλα εργασίας   |  Κρυπτογράφηση/Αποκρυπτογράφηση κελιών    Αποστολή email ανά λίστα   |  Σούπερ φίλτρο   |   Ειδικό φίλτρο (φίλτρο με έντονη γραφή/πλάγια γραφή/διαγραφή...) ...
Κορυφαία 15 σύνολα εργαλείων12 Κείμενο Εργαλεία (Προσθήκη κειμένου, Κατάργηση χαρακτήρων, ...)   |   50 + Διάγραμμα Τύποι (Gantt διάγραμμα, ...)   |   40+ Πρακτικό ΜΑΘΗΜΑΤΙΚΟΙ τυποι (Υπολογίστε την ηλικία με βάση τα γενέθλια, ...)   |   19 Εισαγωγή Εργαλεία (Εισαγωγή κωδικού QR, Εισαγωγή εικόνας από το μονοπάτι, ...)   |   12 Μετατροπή Εργαλεία (Αριθμοί σε λέξεις, Μετατροπή Συναλλάγματος, ...)   |   7 Συγχώνευση & διαχωρισμός Εργαλεία (Σύνθετες σειρές συνδυασμού, Διαίρεση κελιών, ...)   |   ... κι αλλα

Αυξήστε τις δεξιότητές σας στο Excel με τα Kutools για Excel και απολαύστε την αποτελεσματικότητα όπως ποτέ πριν. Το Kutools για Excel προσφέρει πάνω από 300 προηγμένες δυνατότητες για την ενίσχυση της παραγωγικότητας και την εξοικονόμηση χρόνου.  Κάντε κλικ εδώ για να αποκτήσετε τη δυνατότητα που χρειάζεστε περισσότερο...

Περιγραφή


Το Office Tab φέρνει τη διεπαφή με καρτέλες στο Office και κάνει την εργασία σας πολύ πιο εύκολη

  • Ενεργοποίηση επεξεργασίας και ανάγνωσης καρτελών σε Word, Excel, PowerPoint, Publisher, Access, Visio και Project.
  • Ανοίξτε και δημιουργήστε πολλά έγγραφα σε νέες καρτέλες του ίδιου παραθύρου και όχι σε νέα παράθυρα.
  • Αυξάνει την παραγωγικότητά σας κατά 50% και μειώνει εκατοντάδες κλικ του ποντικιού για εσάς κάθε μέρα!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
really, really not helpful
This comment was minimized by the moderator on the site
please help i am trying to create a dynamic named range on excel 2016 typing "=offset(DATAENTRY!$B$6,,,counta(DATAENTRY!$B$6:$B$13))" but still it gives me an error saying it is not a formula.
This comment was minimized by the moderator on the site
You are a very good teacher: 1) step-by-step approach; 2) you do not bore the student with obvious material or conclusions; 3) yet you include all necessary material. I look forward to more tutorials from you.
This comment was minimized by the moderator on the site
Thanks for good article
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations