Πώς να δημιουργήσετε ονόματα εύρους στο Excel (ένας οδηγός βήμα προς βήμα)

Τι υπάρχει στο όνομα;

Εάν εργάζεστε με υπολογιστικά φύλλα Excel, θα μπορούσε να σημαίνει μεγάλη εξοικονόμηση χρόνου και αποτελεσματικότητα.

Σε αυτό το σεμινάριο, θα μάθετε πώς να δημιουργείτε ονόματα εύρους στο Excel και πώς να τα χρησιμοποιείτε για να εξοικονομήσετε χρόνο.

Named Ranges in Excel - An Introduction

Αν κάποιος πρέπει να με καλέσει ή να με παραπέμψει, θα χρησιμοποιήσει το όνομά μου (αντί να πει ότι ένα αρσενικό μένει σε έναν τόνο τόπο με τόσο ύψος και βάρος).

Σωστά?

Ομοίως, στο Excel, μπορείτε να δώσετε ένα όνομα σε ένα κελί ή μια σειρά κελιών.

Τώρα, αντί να χρησιμοποιείτε την αναφορά κελιού (όπως A1 ή A1: A10), μπορείτε απλά να χρησιμοποιήσετε το όνομα που της δώσατε.

Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:

Σε αυτό το σύνολο δεδομένων, εάν πρέπει να ανατρέξετε στο εύρος που έχει την Ημερομηνία, θα πρέπει να χρησιμοποιήσετε το A2: A11 σε τύπους. Ομοίως, για το Sales Rep and Sales, θα πρέπει να χρησιμοποιήσετε τα B2: B11 και C2: C11.

Αν και είναι εντάξει όταν έχετε μόνο δύο σημεία δεδομένων, αλλά σε περίπτωση που έχετε τεράστια πολύπλοκα σύνολα δεδομένων, η χρήση αναφορών κυττάρων για αναφορά σε δεδομένα μπορεί να είναι χρονοβόρα.

Το Excel Named Ranges διευκολύνει την αναφορά σε σύνολα δεδομένων στο Excel.

Μπορείτε να δημιουργήσετε μια ονομαστική περιοχή στο Excel για κάθε κατηγορία δεδομένων και, στη συνέχεια, να χρησιμοποιήσετε αυτό το όνομα αντί για τις αναφορές κελιών. Για παράδειγμα, οι ημερομηνίες μπορούν να ονομαστούν «Ημερομηνία», τα στοιχεία αντιπροσώπων πωλήσεων μπορούν να ονομάζονται «SalesRep» και τα δεδομένα πωλήσεων μπορούν να ονομάζονται «Πωλήσεις».

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

Οφέλη από τη δημιουργία ονομάτων εύρους στο Excel

Ακολουθούν τα οφέλη από τη χρήση ονομάτων εύρους στο Excel.

Χρησιμοποιήστε ονόματα αντί για αναφορές κελιών

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

Για παράδειγμα, μπορείτε να χρησιμοποιήσετε = SUM (ΠΩΛΗΣΕΙΣ) αντί = SUM (C2: C11) για το παραπάνω σύνολο δεδομένων.

Ρίξτε μια ματιά στους τύπους που αναφέρονται παρακάτω. Αντί να χρησιμοποιώ αναφορές κυττάρων, έχω χρησιμοποιήσει τις ονομασμένες περιοχές.

• Αριθμός πωλήσεων με αξία μεγαλύτερη από 500: = COUNTIF (Πωλήσεις, »> 500 ″)
• Άθροισμα όλων των πωλήσεων που πραγματοποίησε ο Tom: = SUMIF (SalesRep, ”Tom”, Sales)
• Προμήθεια που κέρδισε ο Joe (οι πωλήσεις επί Joe πολλαπλασιάζονται με το ποσοστό προμήθειας):
  = SUMIF (SalesRep, ”Joe”, Sales)*Επιτροπή

Θα συμφωνήσετε ότι αυτοί οι τύποι είναι εύκολο να δημιουργηθούν και να γίνουν εύκολα κατανοητοί (ειδικά όταν τα μοιράζεστε με κάποιον άλλο ή τα ξαναεπισκεφτείτε μόνοι σας.

Δεν χρειάζεται να επιστρέψετε στο σύνολο δεδομένων για να επιλέξετε κελιά

Ένα άλλο σημαντικό όφελος από τη χρήση των ονομαστικών σειρών στο Excel είναι ότι δεν χρειάζεται να επιστρέψετε και να επιλέξετε τις περιοχές κελιών.

Μπορείτε απλώς να πληκτρολογήσετε μερικά αλφάβητα του συγκεκριμένου εύρους και το Excel θα εμφανίσει τα αντίστοιχα εύρη (όπως φαίνεται παρακάτω):

Με το όνομα Ranges Make Formulas Dynamic

Χρησιμοποιώντας τα ονόματα εύρους στο Excel, μπορείτε να κάνετε δυνατούς τους τύπους του Excel.

Για παράδειγμα, στην περίπτωση προμήθειας πωλήσεων, αντί να χρησιμοποιήσετε την τιμή 2,5%, μπορείτε να χρησιμοποιήσετε το Περιορισμένο όνομα.

Τώρα, αν η εταιρεία σας αποφασίσει αργότερα να αυξήσει την προμήθεια στο 3%, μπορείτε απλώς να ενημερώσετε το Εύρος με το όνομα και όλοι οι υπολογισμοί θα ενημερωθούν αυτόματα για να αντικατοπτρίζουν τη νέα προμήθεια.

Πώς να δημιουργήσετε ονόματα εύρους στο Excel

Ακολουθούν τρεις τρόποι για να δημιουργήσετε ονόματα εύρους στο Excel:

Μέθοδος #1 - Χρήση ορισμού ονόματος

Ακολουθούν τα βήματα για τη δημιουργία ονομάτων εύρους στο Excel χρησιμοποιώντας τον ορισμό ονόματος:

• Επιλέξτε το εύρος για το οποίο θέλετε να δημιουργήσετε μια περιοχή με όνομα στο Excel.
• Μεταβείτε στους Τύπους -> Ορισμός ονόματος.
• Στο πλαίσιο διαλόγου Νέο όνομα, πληκτρολογήστε το Όνομα που θέλετε να εκχωρήσετε στο επιλεγμένο εύρος δεδομένων. Μπορείτε να καθορίσετε το πεδίο εφαρμογής ως ολόκληρο το βιβλίο εργασίας ή ένα συγκεκριμένο φύλλο εργασίας. Εάν επιλέξετε ένα συγκεκριμένο φύλλο, το όνομα δεν θα είναι διαθέσιμο σε άλλα φύλλα.
• Κάντε κλικ στο OK.

Αυτό θα δημιουργήσει μια ονομαστική περιοχή SALESREP.

Μέθοδος #2: Χρήση του πλαισίου ονόματος

• Επιλέξτε το εύρος για το οποίο θέλετε να δημιουργήσετε ένα όνομα (μην επιλέξετε κεφαλίδες).
• Μεταβείτε στο πλαίσιο ονομάτων στα αριστερά της γραμμής τύπων και πληκτρολογήστε το όνομα με το οποίο θέλετε να δημιουργήσετε το εύρος με όνομα.
• Σημειώστε ότι το όνομα που δημιουργήθηκε εδώ θα είναι διαθέσιμο για ολόκληρο το τετράδιο εργασίας. Εάν θέλετε να το περιορίσετε σε ένα φύλλο εργασίας, χρησιμοποιήστε τη Μέθοδο 1.

Μέθοδος #3: Χρήση της επιλογής Δημιουργία από επιλογή

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

Για παράδειγμα, στο παρακάτω σύνολο δεδομένων, εάν θέλετε να δημιουργήσετε γρήγορα τρία ονόματα εύρους (Ημερομηνία, Sales_Rep και Sales), τότε μπορείτε να χρησιμοποιήσετε τη μέθοδο που φαίνεται παρακάτω.

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

• Επιλέξτε ολόκληρο το σύνολο δεδομένων (συμπεριλαμβανομένων των κεφαλίδων).
• Μεταβείτε στους Τύπους -> Δημιουργία από επιλογή (Συντόμευση πληκτρολογίου - Control + Shift + F3). Θα ανοίξει το πλαίσιο διαλόγου «Δημιουργία ονομάτων από επιλογή».
• Στο πλαίσιο διαλόγου Δημιουργία ονομάτων από επιλογή, ελέγξτε τις επιλογές όπου έχετε τις κεφαλίδες. Σε αυτήν την περίπτωση, επιλέγουμε την επάνω σειρά μόνο καθώς η κεφαλίδα βρίσκεται στην επάνω σειρά. Εάν έχετε κεφαλίδες και στην επάνω σειρά και στην αριστερή στήλη, μπορείτε να επιλέξετε και τις δύο. Ομοίως, εάν τα δεδομένα σας είναι διατεταγμένα όταν οι κεφαλίδες βρίσκονται μόνο στην αριστερή στήλη, τότε ελέγχετε μόνο την επιλογή Αριστερή στήλη.

Αυτό θα δημιουργήσει τρία ονόματα εύρους - ημερομηνία, Sales_Rep και Sales.

Σημειώστε ότι παίρνει αυτόματα ονόματα από τις κεφαλίδες. Εάν υπάρχει κενό μεταξύ των λέξεων, εισάγει μια υπογράμμιση (καθώς δεν μπορείτε να έχετε κενά σε ονόματα εύρους).

Σύμβαση ονοματοδοσίας για επώνυμα εύρη στο Excel

Υπάρχουν ορισμένοι κανόνες ονοματολογίας που πρέπει να γνωρίζετε κατά τη δημιουργία ονομάτων εύρους στο Excel:

• Ο πρώτος χαρακτήρας ενός ονόματος εύρους πρέπει να είναι ένας χαρακτήρας γράμματος και υπογράμμισης (_) ή ανάστροφη ανάφλεξη (\). Αν είναι κάτι άλλο, θα εμφανίσει σφάλμα. Οι υπόλοιποι χαρακτήρες μπορεί να είναι γράμματα, αριθμοί, ειδικοί χαρακτήρες, τελεία ή υπογράμμιση.
• Δεν μπορείτε να χρησιμοποιήσετε ονόματα που αντιπροσωπεύουν επίσης αναφορές κελιών στο Excel. Για παράδειγμα, δεν μπορείτε να χρησιμοποιήσετε το AB1, καθώς είναι επίσης μια αναφορά κελιού.
• Δεν μπορείτε να χρησιμοποιήσετε κενά κατά τη δημιουργία ονομάτων εύρους. Για παράδειγμα, δεν μπορείτε να έχετε Sales Rep ως ονομαστική περιοχή. Εάν θέλετε να συνδυάσετε δύο λέξεις και να δημιουργήσετε μια περιοχή με όνομα, χρησιμοποιήστε μια υπογράμμιση, τελεία ή κεφαλαίους χαρακτήρες για να τη δημιουργήσετε. Για παράδειγμα, μπορείτε να έχετε Sales_Rep, SalesRep ή SalesRep.
  • Κατά τη δημιουργία ονομάτων, το Excel αντιμετωπίζει τα κεφαλαία και τα πεζά με τον ίδιο τρόπο. Για παράδειγμα, εάν δημιουργήσετε μια ονομαστική περιοχή ΠΩΛΗΣΕΙΣ, τότε δεν θα μπορείτε να δημιουργήσετε μια άλλη ονομαστική περιοχή, όπως "πωλήσεις" ή "Πωλήσεις".
• Ένα εύρος με όνομα μπορεί να έχει μήκος έως 255 χαρακτήρες.

Πάρα πολλές ονομασμένες σειρές στο Excel; Μην ανησυχείτε

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

Τι γίνεται αν δεν θυμάστε το όνομα της ονομασμένης περιοχής που δημιουργήσατε;

Μην ανησυχείς - εδώ είναι μερικές χρήσιμες συμβουλές.

Λήψη των ονομάτων όλων των ονομασμένων περιοχών

Ακολουθούν τα βήματα για να λάβετε μια λίστα με όλα τα ονόματα εύρους που δημιουργήσατε:

• Μεταβείτε στην καρτέλα Τύποι.
• Στην ομάδα Defined Named, κάντε κλικ στο Use in Formula.
• Κάντε κλικ στο «Επικόλληση ονομάτων».

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

Εμφάνιση των Matching Named Ranges

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

Πώς να επεξεργαστείτε τα ονόματα εύρους στο Excel

Εάν έχετε ήδη δημιουργήσει μια περιοχή με όνομα, μπορείτε να την επεξεργαστείτε χρησιμοποιώντας τα ακόλουθα βήματα:

• Μεταβείτε στην καρτέλα Τύποι και κάντε κλικ στο Διαχειριστής ονομάτων.
• Το παράθυρο διαλόγου Διαχειριστής ονομάτων θα εμφανίσει όλα τα εύρη με το όνομα σε αυτό το βιβλίο εργασίας. Κάντε διπλό κλικ στο Εύρος με όνομα που θέλετε να επεξεργαστείτε.
• Στο παράθυρο διαλόγου Επεξεργασία ονόματος, κάντε τις αλλαγές.
• Κάντε κλικ στο OK.
• Κλείστε το παράθυρο διαλόγου Διαχείριση ονομάτων.

Χρήσιμες συντομεύσεις ονόματος εύρους (η ισχύς του F3)

Ακολουθούν μερικές χρήσιμες συντομεύσεις πληκτρολογίου που θα σας φανούν χρήσιμες όταν εργάζεστε με τις ονομασμένες σειρές στο Excel:

• Για να λάβετε μια λίστα με όλες τις ονομαστικές περιοχές και να την επικολλήσετε στον τύπο: F3
• Για να δημιουργήσετε νέο όνομα χρησιμοποιώντας το πλαίσιο διαλόγου Διαχειριστής ονομάτων: Control + F3
• Για να δημιουργήσετε ονόματα εύρους από την επιλογή: Control + Shift + F3

Δημιουργία εύρους δυναμικών ονομάτων στο Excel

Μέχρι στιγμής σε αυτό το σεμινάριο, έχουμε δημιουργήσει στατικές ονομασίες εύρους.

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

Για παράδειγμα, εάν το A1: A10 έχει ονομαστεί ως «Πωλήσεις», θα αναφέρεται πάντα στο A1: A10.

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

Στον κόσμο των συνεχώς διευρυνόμενων συνόλων δεδομένων, αυτό μπορεί να καταλήξει να αφαιρέσει πολύ από τον χρόνο σας. Κάθε φορά που λαμβάνετε νέα δεδομένα, ενδέχεται να χρειαστεί να ενημερώσετε τα ονόματα εύρους στο Excel.

Για την αντιμετώπιση αυτού του ζητήματος, μπορούμε να δημιουργήσουμε Δυναμικές ονομαστικές περιοχές στο Excel που θα λαμβάνουν αυτόματα υπόψη πρόσθετα δεδομένα και θα τα συμπεριλαμβάνουν στο υπάρχον εύρος με όνομα.

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

Αυτό το είδος δυναμικού εύρους ονομάτων μπορεί να δημιουργηθεί χρησιμοποιώντας τη συνάρτηση Excel INDEX. Αντί να καθορίσετε τις αναφορές κελιών κατά τη δημιουργία του εύρους με όνομα, καθορίζουμε τον τύπο. Ο τύπος ενημερώνεται αυτόματα όταν προστίθενται ή διαγράφονται τα δεδομένα.

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

Ας υποθέσουμε ότι έχουμε τα δεδομένα πωλήσεων στο κελί A2: A11.

Ακολουθούν τα βήματα για τη δημιουργία δυναμικών ονομάτων εύρους στο Excel:

1. 1. Μεταβείτε στην καρτέλα Τύπος και κάντε κλικ στο Ορισμός ονόματος.
   2. Στο πλαίσιο διαλόγου Νέο όνομα πληκτρολογήστε τα εξής:
      • Όνομα: Πωλήσεις
      • Πεδίο εφαρμογής: Τετράδιο εργασίας
      • Αναφέρεται σε: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))
   3. Κάντε κλικ στο OK.

Εγινε!

Τώρα έχετε ένα δυναμικό εύρος ονομάτων με το όνομα «Πωλήσεις». Αυτό θα ενημερώνεται αυτόματα κάθε φορά που προσθέτετε δεδομένα σε αυτό ή αφαιρείτε δεδομένα από αυτό.

Πώς λειτουργεί το Dynamic Named Ranges;

Για να εξηγήσετε πώς λειτουργεί αυτό, πρέπει να γνωρίζετε λίγο περισσότερα για τη λειτουργία INDEX του Excel.

Οι περισσότεροι άνθρωποι χρησιμοποιούν το INDEX για να επιστρέψουν μια τιμή από μια λίστα με βάση τον αριθμό γραμμής και στήλης.

Αλλά η συνάρτηση INDEX έχει και μια άλλη πλευρά.

Μπορεί να συνηθίσει να επιστροφή αναφοράς κελιού όταν χρησιμοποιείται ως μέρος μιας αναφοράς κελιού.

Για παράδειγμα, εδώ είναι ο τύπος που χρησιμοποιήσαμε για να δημιουργήσουμε μια δυναμική ονομαστική περιοχή:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””) -> Αυτό το μέρος του τύπου αναμένεται να επιστρέψει μια τιμή (η οποία θα ήταν η 10η τιμή από τη λίστα, λαμβάνοντας υπόψη ότι υπάρχουν δέκα στοιχεία).

Ωστόσο, όταν χρησιμοποιείται μπροστά από μια αναφορά (=$ A $ 2:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))) επιστρέφει την αναφορά στο κελί αντί για την τιμή.

Ως εκ τούτου, εδώ επιστρέφει = $ A $ 2: $ A $ 11

Εάν προσθέσουμε δύο επιπλέον τιμές στη στήλη πωλήσεων, τότε θα επιστρέψει = $ A $ 2: $ A $ 13

Όταν προσθέτετε νέα δεδομένα στη λίστα, η συνάρτηση Excel COUNTIF επιστρέφει τον αριθμό των κενών κελιών στα δεδομένα. Αυτός ο αριθμός χρησιμοποιείται από τη συνάρτηση INDEX για την ανάκτηση της αναφοράς κελιού του τελευταίου στοιχείου στη λίστα.

Σημείωση:

• Αυτό θα λειτουργήσει μόνο εάν δεν υπάρχουν κενά κελιά στα δεδομένα.
• Στο παράδειγμα που ελήφθη παραπάνω, έχω εκχωρήσει έναν μεγάλο αριθμό κελιών (A2: A100) για τον τύπο Named Range. Μπορείτε να το προσαρμόσετε με βάση το σύνολο δεδομένων σας.

Μπορείτε επίσης να χρησιμοποιήσετε τη λειτουργία OFFSET για να δημιουργήσετε ένα εύρος δυναμικών ονομάτων στο Excel, ωστόσο, επειδή η λειτουργία OFFSET είναι ασταθής, μπορεί να οδηγήσει σε ένα αργό βιβλίο εργασίας του Excel. Το INDEX, από την άλλη πλευρά, είναι ημι-πτητικό, γεγονός που καθιστά μια καλύτερη επιλογή για τη δημιουργία δυναμικών ονομάτων εύρους στο Excel.

Μπορεί επίσης να σας αρέσουν οι ακόλουθοι πόροι του Excel:

• Δωρεάν πρότυπα Excel.
• Δωρεάν διαδικτυακή εκπαίδευση στο Excel (διαδικτυακό μάθημα βίντεο 7 μερών).
• Χρήσιμα παραδείγματα κώδικα μακροεντολής Excel.
• 10 Προηγμένα παραδείγματα Excel VLOOKUP.
• Δημιουργία αναπτυσσόμενης λίστας στο Excel.
• Δημιουργία ονόματος εύρους στα Υπολογιστικά φύλλα Google.
• Πώς να αναφέρετε ένα άλλο φύλλο ή βιβλίο εργασίας στο Excel