- Οι δομημένες αναφορές επιτρέπουν τη χρήση ονομάτων πινάκων και στηλών αντί για περιοχές κελιών, βελτιώνοντας τη σαφήνεια και τη συντηρησιμότητα των τύπων.
- Η σύνταξή του συνδυάζει το όνομα του πίνακα, τους προσδιοριστές στοιχείων (#Δεδομένα, #Σύνολα, #Επικεφαλίδες, @) και τα ονόματα στηλών σε αγκύλες.
- Ενημερώνονται αυτόματα κατά την προσθήκη, διαγραφή ή μετονομασία γραμμών, στηλών ή πινάκων, αποτρέποντας τα τυπικά σφάλματα αναφοράς A1.
- Η σωστή χρήση ονομάτων, αυτόματης συμπλήρωσης και τελεστών αναφοράς καθιστά τους πίνακες του Excel ισχυρά εργαλεία για προηγμένη ανάλυση.
Αν εργάζεστε συχνά με πίνακες στο Excel, αργά ή γρήγορα θα συναντήσετε το δομημένες αναφορέςΜπορεί να ακούγονται περίεργα ή μπερδεμένα στην αρχή, αλλά μόλις τα εξοικειωθείτε, γίνονται ένα πολύ βολικό εργαλείο για τη δημιουργία σαφών και εύκολων στη συντήρηση τύπων.
Η ιδέα είναι πολύ απλή: αντί να χρησιμοποιείτε αναφορές όπως C2:C7, το Excel σάς επιτρέπει να χρησιμοποιείτε ονόματα πινάκων και στηλών, όπως Τμήμα Πωλήσεων o Τμήμα ΜισθοδοσίαςΜε αυτόν τον τρόπο, γνωρίζετε με μια ματιά τι υπολογίζει ο τύπος και οι αναφορές προσαρμόζονται αυτόματα όταν προσθέτετε ή διαγράφετε γραμμές και στήλες από τον πίνακα. Ας το δούμε με ψυχραιμία, βήμα προς βήμα, με πολλά πρακτικά παραδείγματα.
Τι ακριβώς είναι οι δομημένες αναφορές στο Excel;
Όταν μετατρέπετε μια περιοχή δεδομένων σε έναν πίνακα, το Excel δημιουργεί αυτόματα έναν όνομα για αυτόν τον πίνακα και για κάθε κεφαλίδα στήλης τουΑπό εκείνη τη στιγμή και μετά, μπορείτε να χρησιμοποιείτε αυτά τα ονόματα σε τύπους, τόσο εντός όσο και εκτός του ίδιου του πίνακα.
Για παράδειγμα, αντί να γράψετε έναν τύπο όπως =SUM(C2:C7)Θα μπορείτε να γράψετε κάτι σαν αυτό: =SUM(Τμήμα Πωλήσεων)όπου "DeptVentas" είναι το όνομα του πίνακα και "Amount de ventas" είναι το όνομα της στήλης. Αυτός ο συνδυασμός ονόματος πίνακα συν ονόματος στήλης είναι γνωστός ως δομημένη αναφορά.
Αυτές οι αναφορές έχουν ένα σημαντικό πλεονέκτημα: ενημερώνονται μόνοι τους Όταν τροποποιείτε τον πίνακα, εάν προσθέσετε μια άλλη γραμμή πωλήσεων ή εισαγάγετε μια νέα στήλη ενδιάμεσα, η δομημένη αναφορά προσαρμόζεται, ενώ μια κλασική αναφορά C2:C7 ενδέχεται να μην καλύπτει πλέον ολόκληρο το εύρος που χρειάζεστε και να σας αναγκάσει να αναθεωρείτε τους τύπους με μη αυτόματο τρόπο.
Επιπλέον, μπορούν να χρησιμοποιηθούν δομημένες αναφορές και εκτός του πίνακα. Αυτό είναι εξαιρετικό όταν έχετε ένα βιβλίο με πολλές σελίδες και πολλούς πίνακες, επειδή Δείτε σαφή ονόματα όπως Τμήμα Πωλήσεων Είναι πολύ πιο εύχρηστο από την αποκρυπτογράφηση εύρους A2:B7 που είναι διάσπαρτα σε όλο το αρχείο.
Δημιουργήστε έναν πίνακα και χρησιμοποιήστε βασικές δομημένες αναφορές
Το πρώτο βήμα για να μπορέσετε να χρησιμοποιήσετε δομημένες αναφορές είναι Μετατρέψτε το εύρος σας σε πίνακα ExcelΕίτε πρόκειται για δεδομένα πωλήσεων, μισθούς ή οποιοδήποτε άλλο είδος πληροφορίας, η διαδικασία είναι πάντα η ίδια.
Φανταστείτε έναν πίνακα με αυτές τις στήλες: Πωλητής, Περιοχή, Ποσό Πωλήσεων, % Προμήθειας και Ποσό Προμήθειας. Αντιγράφετε τα δεδομένα σε ένα νέο φύλλο, συμπεριλαμβανομένων των κεφαλίδων, και τα επικολλάτε ξεκινώντας από το κελί A1Στη συνέχεια, επιλέξτε οποιοδήποτε κελί σε αυτό το εύρος και πατήστε Ctrl + T ώστε το Excel να μπορεί να δημιουργήσει τον πίνακα.
Στο πλαίσιο που εμφανίζεται, επιβεβαιώστε ότι η επιλογή "Ο πίνακας έχει επικεφαλίδες" Είναι ενεργοποιημένο και αποδεκτό. Το Excel θα εφαρμόσει μια σχεδίαση πίνακα και από εκείνη τη στιγμή και μετά, θα έχετε πρόσβαση σε δομημένες αναφορές με βάση αυτές τις κεφαλίδες.
Ας υποθέσουμε ότι θέλετε να υπολογίσετε την προμήθεια στη στήλη "Ποσό Προμήθειας" πολλαπλασιάζοντας το Ποσό Πωλήσεων με το ποσοστό προμήθειας. Μεταβείτε στο κελί E2, πληκτρολογήστε το σύμβολο ισότητας (=) και κάντε κλικ στο κελί C2 (Ποσό Πωλήσεων). Θα δείτε κάτι σαν αυτό να εμφανίζεται στη γραμμή τύπων: ] αντί για C2.
Στη συνέχεια, πληκτρολογήστε έναν αστερίσκο (*) και κάντε κλικ στο κελί D2 (ποσοστό προμήθειας). Το Excel θα ολοκληρώσει τον τύπο με ]Ο τελικός σας τύπος θα μοιάζει κάπως έτσι: =]*]Όταν πατήσετε Enter, το Excel θα δημιουργήσει αυτόματα ένα υπολογιζόμενη στήλη, αντιγράφοντας τον τύπο και προσαρμόζοντας εσωτερικά τις αναφορές στην αντίστοιχη γραμμή.
Διαφορές με τις κλασικές αναφορές κελιών
Μπορείτε πάντα να εκτελέσετε τον παραπάνω υπολογισμό χρησιμοποιώντας κανονικές αναφορές κελιών, για παράδειγμα γράφοντας =C2*D2 στο E2. Το Excel θα αναπαράγει επίσης τον τύπο προς τα κάτω, αλλά σε αυτήν την περίπτωση θα χρησιμοποιείτε σαφείς, όχι δομημένες, αναφορές.
Το πρόβλημα με αυτήν την προσέγγιση είναι διπλό. Από τη μία πλευρά, ο τύπος είναι λιγότερο ευανάγνωστοςΑν ανοίξετε αυτό το αρχείο σε ένα μήνα, θα πρέπει να θυμάστε τι υπήρχε στα κελιά C2 και D2. Επιπλέον, τυχόν δομικές αλλαγές (εισαγωγή νέας στήλης, μετακίνηση στήλης σε διαφορετική θέση) ενδέχεται να σας αναγκάσουν να επανεξετάσετε τους τύπους, επειδή Η αναφορά C2*D2 δεν θα έχει πλέον την ίδια σημασία.
Με δομημένες αναφορές, ισχύει το αντίθετο. Χάρη σε εκφράσεις όπως ]*]Γνωρίζετε με μια ματιά ποιες στήλες εμπλέκονται και, αν εισαγάγετε μια στήλη ενδιάμεσα, ο τύπος παραμένει έγκυρος. Δηλαδή, είναι πιο ανθεκτικό στις αλλαγές και σε γλιτώνουν από ανόητα λάθη.
Συχνά, το ίδιο το Excel προτείνει δομημένες αναφορές όταν γράφετε τύπους μέσα στον πίνακα, επομένως δεν χρειάζεται να θυμάστε τη σύνταξη. Ακόμα και εξωτερικοί βοηθοί όπως ChatGPT για Excel Μπορούν να βοηθήσουν. Απλώς ξεκινήστε να πληκτρολογείτε και αφήστε τη συνάρτηση να κάνει τη δουλειά. Αυτόματη συμπλήρωση τύπου κάντε τη βαριά δουλειά.
Ακόμα και όταν εργάζεστε εκτός πίνακα, μπορείτε να κάνετε κλικ και να σύρετε πάνω από τα κελιά του πίνακα ενώ πληκτρολογείτε τον τύπο και το Excel θα εισαγάγει απευθείας τη δομημένη αναφορά αντί για μια περιοχή όπως A2:A100.
Πώς να ονομάσετε και να αλλάξετε σωστά έναν πίνακα στο Excel
Κάθε φορά που δημιουργείτε έναν πίνακα, το Excel του εκχωρεί ένα γενικό όνομα όπως αυτό: Πίνακας 1, Πίνακας 2 και ούτω καθεξής. Αν και αυτά τα ονόματα λειτουργούν, το πιο πρακτικό είναι Μετονομάστε τον πίνακα με ένα περιγραφικό όνομα Επιτρέψτε μου να σας πω περί τίνος πρόκειται αυτά τα δεδομένα.
Για να αλλάξετε το όνομα, επιλέξτε οποιοδήποτε κελί στον πίνακα και θα εμφανιστεί η καρτέλα σχεδίασης πίνακα (Σχεδίαση πίνακα ή Σχεδίαση, ανάλογα με την έκδοση). Στο πλαίσιο με την ένδειξη "Όνομα πίνακα", πληκτρολογήστε κάτι πιο ουσιαστικό, για παράδειγμα Τμήμα Πωλήσεων, Τμήμα Μισθοδοσίας ή παρόμοιο και πατήστε Enter. Από εκείνη τη στιγμή και μετά, όλες οι δομημένες αναφορές θα χρησιμοποιούν αυτό το όνομα.
Το Excel επιβάλλει ορισμένους κανόνες για τα ονόματα πινάκων. Πρέπει να ξεκινούν με ένα γράμμα, υπογράμμιση (_) ή ανάστροφη κάθετος (\)Από εκεί, μπορείτε να χρησιμοποιήσετε γράμματα, αριθμούς, τελείες και υπογράμμιση. Ωστόσο, δεν μπορείτε να χρησιμοποιήσετε ονόματα που ταιριάζουν με αναφορές κελιών, όπως Z$100 ή R1C1, ούτε μπορείτε απλώς να χρησιμοποιήσετε "C", "c", "R" ή "r", επειδή το Excel τα διατηρεί ως συντομεύσεις για την επιλογή στηλών ή γραμμών.
Δεν επιτρέπονται επίσης κενά στο όνομα. Αντ' αυτού, μπορείτε να χρησιμοποιήσετε υπογράμμιση ή τελεία ως διαχωριστικόδημιουργώντας ονόματα όπως SalesDept, SalesTax, FirstQuarter ή PurchaseBonus. Ένας άλλος περιορισμός είναι το μήκος: το όνομα δεν μπορεί να υπερβαίνει 255 χαρακτήρες, κάτι που στην πράξη σχεδόν ποτέ δεν θα αποτελέσει πρόβλημα.
Τα ονόματα πινάκων πρέπει να είναι μοναδικά μέσα στο βιβλίο εργασίας και στο Excel. Δεν κάνει διάκριση μεταξύ κεφαλαίων και πεζώνΑυτό σημαίνει ότι εάν έχετε ήδη έναν πίνακα που ονομάζεται SALES, δεν μπορείτε να δημιουργήσετε έναν άλλον που ονομάζεται Sales. Ένα συνηθισμένο κόλπο για να διατηρείτε τα πράγματα οργανωμένα είναι να χρησιμοποιείτε προθέματα που υποδεικνύουν τον τύπο του αντικειμένου, για παράδειγμα: tbl_Πωλήσεις για ένα κανονικό τραπέζι, pt_Πωλήσεις για έναν συγκεντρωτικό πίνακα και chrt_Πωλήσεις Για ένα γράφημα. Με αυτόν τον τρόπο, στον Διαχειριστή Ονομάτων θα έχετε τα πάντα κατηγοριοποιημένα τέλεια.
Λεπτομερής σύνταξη δομημένων αναφορών
Πέρα από τα απλά παραδείγματα, οι δομημένες αναφορές έχουν πολύ ισχυρή σύνταξη Αυτό σας επιτρέπει να στοχεύσετε συγκεκριμένα μέρη του πίνακα: κεφαλίδες, γραμμές δεδομένων, σύνολα, ολόκληρες στήλες, διασταυρώσεις κ.λπ. Η κατανόηση αυτών των στοιχείων κάνει τη διαφορά κατά τη δημιουργία σύνθετων μοντέλων.
Ένας τυπικός τύπος μπορεί να μοιάζει κάπως έτσι: =SUM(Τμήμα Πωλήσεων,],Τμήμα Πωλήσεων,])Εδώ, προστίθενται δύο πράγματα: αφενός, το σύνολο της στήλης Ποσό Πωλήσεων (γραμμή συνόλων) και, αφετέρου, όλα τα δεδομένα στη στήλη Ποσό Προμήθειας.
Σε αυτόν τον τύπο μπορούμε να διακρίνουμε διάφορα στοιχεία. Το όνομα πίνακα (για παράδειγμα, SalesDept ή SalaryDept) προσδιορίζει τον πίνακα με τον οποίο εργάζεστε. Εμφανίζονται τα ακόλουθα: προσδιοριστές στοιχείωνΩς o τα οποία υποδεικνύουν εάν αναφέρεστε σε ολόκληρο τον πίνακα, μόνο στα δεδομένα, στις κεφαλίδες ή στη γραμμή συνόλων.
Στη συνέχεια, υπάρχουν τα προσδιοριστές στήληςΑυτοί οι προσδιοριστές χρησιμοποιούν τα ονόματα των κεφαλίδων. Για παράδειγμα, , , ή . Αυτοί οι προσδιοριστές αναφέρονται στα δεδομένα σε αυτήν τη στήλη, χωρίς να συμπεριλαμβάνουν αυτόματα την κεφαλίδα ή τη γραμμή συνόλων, εκτός εάν συνδυάσετε τον προσδιοριστή στήλης με έναν προσδιοριστή στοιχείου.
Τέλος, ένα σύνολο όπως ,] o ,] Κάντε σαν προσδιοριστής πίνακα, ορίζοντας ακριβώς ποιο μέρος του πίνακα θέλετε να αθροίσετε, να μετρήσετε, να υπολογίσετε τον μέσο όρο κ.λπ. Ολόκληρη αυτή η συμβολοσειρά, ξεκινώντας από το όνομα του πίνακα και τελειώνοντας με τον προσδιοριστή στήλης, αποτελεί την πλήρη δομημένη αναφορά.
Όταν χρησιμοποιείτε αυτήν τη σύνταξη, να θυμάστε ότι Όλοι οι προσδιοριστές πρέπει να περικλείονται σε αγκύλες. (). Εάν συμπεριλάβετε άλλους προσδιοριστές μέσα σε έναν προσδιοριστή, θα έχετε ένθετες αγκύλες και θα χρειαστείτε εξωτερικές αγκύλες για να περικλείσετε το σύνολο, όπως στο =Τμήμα Πωλήσεων:], το οποίο αναφέρεται σε όλα τα κελιά μεταξύ των στηλών "Εμπορικό" και "Περιοχή".
Ειδικοί χαρακτήρες και κενά στις κεφαλίδες στηλών
Οι επικεφαλίδες στηλών, εντός δομημένων αναφορών, αντιμετωπίζονται εσωτερικά ως συμβολοσειρές κειμένουΔεν χρειάζεται όμως να τα περικλείετε σε εισαγωγικά. Απλώς μπαίνουν μέσα σε αγκύλες. Ωστόσο, όταν η κεφαλίδα περιέχει ορισμένους ειδικούς χαρακτήρες (όπως σημεία στίξης, κενά ή σύμβολα), το Excel απαιτεί Ολόκληρη η επικεφαλίδα θα πρέπει να περικλείεται σε πρόσθετες παρενθέσεις..
Για παράδειγμα, αν έχετε μια στήλη με τίτλο "Συνολικό Ποσό $", θα πρέπει να γράψετε κάτι σαν [Σύνοψη του Έτους του Τμήματος Πωλήσεων]Η χρήση διπλών αγκυλών διασφαλίζει ότι το Excel ερμηνεύει όλο αυτό το κείμενο, συμπεριλαμβανομένων των κενών, των συμβόλων δολαρίου ή των σημείων στίξης, ως το όνομα της στήλης.
Οι χαρακτήρες που απαιτούν τη χρήση πρόσθετων αγκυλών περιλαμβάνουν τους tab, αλλαγές γραμμής, επιστροφή χαρακτήρα, κόμματα, άνω και κάτω τελείες, τελείες, αγκύλες, σύμβολο δίεσης (#), μονά και διπλά εισαγωγικά, αγκύλες, σύμβολο δολαρίου, σύμβολα καρέ, συμπλεκτικό σύμβολο (&), αστερίσκος, σύμβολα συν και πλην, σύμβολο ίσον, σύμβολα μεγαλύτερο και μικρότερο από, κάθετος, σύμβολο at (@), ανάστροφη κάθετος (\), θαυμαστικό, παρενθέσεις, σύμβολο ποσοστού, ερωτηματικό, έντονη προφορά, ερωτηματικό, περισπωμένη και υπογράμμιση.
Επιπλέον, ορισμένοι από αυτούς τους χαρακτήρες έχουν ιδιαίτερη σημασία για το Excel και χρειάζονται χαρακτήρας διαφυγής, συνήθως ένα μόνο εισαγωγικό ('), μέσα στην παραπομπή. Για παράδειγμα, εάν η επικεφαλίδα σας περιέχει το σύμβολο # ή ένα μόνο εισαγωγικό, ίσως χρειαστεί να γράψετε κάτι σαν =Τμήμα Χριστουγεννιάτικου Δώρου ώστε το Excel να μπορεί να το ερμηνεύσει σωστά.
Μια άλλη σημαντική σύσταση είναι ότι μπορείτε να χρησιμοποιήσετε κενά μέσα στην ίδια τη δομημένη αναφορά για να βελτιώσετε την αναγνωσιμότητα, για παράδειγμα στο =Τμήμα Πωλήσεων:] ή σε τύπους όπου παραθέτουν διάφορους προσδιοριστές όπως ,,]. Είναι σύνηθες να αφήνετε κενό μετά από μια αγκύλη ανοίγματος, πριν από μια αγκύλη κλεισίματος και μετά από ένα ερωτηματικό η οποία διαχωρίζει πολλά επιχειρήματα εντός της αναφοράς.
Τελεστές αναφοράς: εύρη, ενώσεις και τομές
Οι δομημένες αναφορές περιλαμβάνουν επίσης κλασικά έργα Τελεστές αναφοράς ExcelΑυτό σας επιτρέπει να δημιουργείτε εύρη, συνδυασμούς στηλών ή τομές μεταξύ περιοχών του πίνακα χωρίς να χρειάζεται να χρησιμοποιείτε αναφορές τύπου A1.
Όταν χρησιμοποιείτε τον τελεστή εύρους (ανώτερη τελεία :), όπως στο Τμήμα Πωλήσεων:]Αναφέρεστε σε όλα τα κελιά σε πολλές γειτονικές στήλες. Αν σκεφτείτε με όρους κελιών, θα ήταν κάτι σαν A2:B7 ή ένα παρόμοιο εύρος, αλλά με δομημένο τρόπο.
Αν αντίθετα θέλετε να δημιουργήσετε έναν συνδυασμό μη συνεχόμενων στηλών, μπορείτε να χρησιμοποιήσετε το τελεστής ένωσης (ερωτηματικό 😉. Για παράδειγμα, Τμήμα Πωλήσεων; Τμήμα Πωλήσεων Επιλέγει δύο ξεχωριστές στήλες, σαν να πληκτρολογούσατε C2:C7;E2:E7. Αυτό είναι χρήσιμο σε συναρτήσεις που δέχονται πολλαπλά εύρη ως ορίσματα.
Τέλος, η τελεστής διασταύρωσηςΟ χώρος, που αναπαρίσταται ως κενό στους τύπους, σας επιτρέπει να λάβετε την περιοχή όπου τέμνονται δύο περιοχές. Μια αναφορά όπως Τμήμα Πωλήσεων:] Τμήμα Πωλήσεων:] Δείχνει την τομή μεταξύ αυτών των μπλοκ στηλών, κάτι που ισοδυναμεί με ένα ενδιάμεσο εύρος του τύπου B2:C7 σε κλασικές αναφορές.
Χάρη σε αυτούς τους χειριστές, μπορείτε να δημιουργήσετε πολύ ευέλικτες παραπομπές και να συνεχίσετε να επωφελείστε από το αυτόματη ενημέρωση που προσφέρουν οι πίνακες του Excel κατά την εισαγωγή ή τη διαγραφή γραμμών και στηλών.
Ειδικοί προσδιοριστές στοιχείων (#Όλα, #Δεδομένα, #Επικεφαλίδες, #Σύνολα, @)
Ένα από τα βασικά στοιχεία των δομημένων αναφορών είναι η ειδικοί προσδιοριστές στοιχείωνΑυτοί οι προσδιοριστές υποδεικνύουν ποιο συγκεκριμένο μέρος του πίνακα θέλετε να χρησιμοποιήσετε στον τύπο. Γράφονται πάντα σε αγκύλες και ξεκινούν με το σύμβολο δίεσης (#), εκτός από την αναφορά στην τρέχουσα γραμμή, η οποία συνήθως αντιπροσωπεύεται από @.
Ο προσδιοριστής Αυτό αναφέρεται σε ολόκληρο τον πίνακα, συμπεριλαμβανομένων των κεφαλίδων, των γραμμών δεδομένων και της γραμμής συνόλων (εάν υπάρχει). Είναι πολύ χρήσιμο σε συναρτήσεις που πρέπει να λαμβάνουν υπόψη ολόκληρο τον πίνακα για να λειτουργούν σωστά.
Ο προσδιοριστής Περιορίζεται στις γραμμές δεδομένων, εξαιρουμένων των κεφαλίδων και των συνόλων. Εν τω μεταξύ, Δείχνει μόνο στη γραμμή κεφαλίδας και Αυτό αναφέρεται αποκλειστικά στη γραμμή συνόλων. Εάν αυτή η γραμμή συνόλων δεν είναι ενεργή στον πίνακα, η αναφορά θα επιστρέψει μια τιμή null.
Για να αναφερθώ στο τρέχουσα σειρά Σε μια υπολογιζόμενη στήλη, χρησιμοποιούνται προσδιοριστές ή, εν συντομία, το σύμβολο @. Για παράδειγμα, Τμήμα Πωλήσεων] Αυτό αναφέρεται στο κελί στη στήλη "Ποσό Προμήθειας" στην ίδια γραμμή με τον τύπο. Το Excel συνήθως το αντικαθιστά αυτόματα με @ όταν ο πίνακας έχει περισσότερες από μία γραμμές, επομένως συνήθως θα βλέπετε τη συντομευμένη έκδοση.
Πρέπει να σημειωθεί ότι #Αυτή η γραμμή και το @ δεν μπορούν να συνδυαστούν με άλλους ειδικούς προσδιοριστές στοιχείωνΕπιπλέον, εάν χρησιμοποιήσετε αυτές τις αναφορές στη γραμμή κεφαλίδας ή στη γραμμή συνόλων, πιθανότατα θα λάβετε σφάλματα #VALUE!, επειδή δεν υπάρχει "γραμμή τρεχόντων δεδομένων" εκεί με τη συνήθη έννοια.
Αναφορές σε στήλες με και χωρίς προσδιορισμό
Όταν εργάζεστε μέσα σε έναν πίνακα, το Excel σάς επιτρέπει να χρησιμοποιείτε μικρότερες δομημένες αναφορέςΟι μη προσδιορισμένες αναφορές χρησιμοποιούνται επειδή το ίδιο το περιβάλλον του πίνακα χρησιμεύει ως αναφορά. Ωστόσο, όταν γράφετε τύπους εκτός του πίνακα, πρέπει να χρησιμοποιήσετε το πλήρες όνομα ή μια προσδιορισμένη αναφορά, συμπεριλαμβανομένου του ονόματος του πίνακα.
Για παράδειγμα, σε μια υπολογιζόμενη στήλη του πίνακα "Τμήμα Πωλήσεων" θα μπορούσατε απλώς να γράψετε =*Το Excel κατανοεί ότι αυτές οι κεφαλίδες ανήκουν στον τρέχοντα πίνακα, επομένως δεν χρειάζεται να γράψετε DeptVentas μπροστά από κάθε στήλη.
Ωστόσο, αν θέλετε να εκτελέσετε τον ίδιο υπολογισμό εκτός του πίνακα, θα χρειαστεί να χρησιμοποιήσετε την πλήρη έκδοση: =Τμήμα Πωλήσεων*Τμήμα ΠωλήσεωνΟ γενικός κανόνας είναι πολύ απλός: Εντός του πίνακα, γίνονται δεκτές μη προσδιορισμένες αναφορές, ενώ εκτός αυτού, πρέπει να χρησιμοποιείται το πλήρες όνομα του πίνακα..
Αυτό βοηθά τόσο στην αναγνωσιμότητα όσο και στη συντήρηση των τύπων, αποφεύγοντας τις ασάφειες όταν έχετε πολλούς πίνακες με κεφαλίδες που θα μπορούσαν να επαναληφθούν στο ίδιο βιβλίο εργασίας του Excel.
Πρακτικά παραδείγματα χρήσης δομημένων αναφορών
Για να ενισχύσουμε τα παραπάνω, είναι χρήσιμο να δούμε μερικά τυπικά παραδείγματα και τα αντίστοιχά τους σε αναφορές κελιών. Μια αναφορά όπως Τμήμα Πωλήσεων,] Αυτό αναφέρεται σε όλα τα κελιά στη στήλη "Ποσό πωλήσεων", συμπεριλαμβανομένης της κεφαλίδας και, εάν είναι διαθέσιμη, της γραμμής συνόλων. Αυτό θα ισοδυναμούσε με κάτι σαν C1:C8 σε ένα τυπικό εύρος.
Αν θέλετε μόνο την κεφαλίδα στήλης "% Προμήθεια", θα χρησιμοποιήσετε Τμήμα Πωλήσεων,]το οποίο από άποψη εύρους αντιστοιχεί σε ένα μόνο κελί, για παράδειγμα D1. Για να αποκτήσετε πρόσβαση στο σύνολο της στήλης "Περιοχή" στη γραμμή συνόλων, η αναφορά θα είναι Τμήμα Πωλήσεων,], ισοδύναμο με κάτι σαν το B8, το οποίο θα επιστρέψει null εάν δεν έχετε ενεργοποιημένη τη γραμμή συνόλων.
Μπορείτε επίσης να συνδυάσετε διάφορους προσδιοριστές. Μια αναφορά στυλ Τμήμα Πωλήσεων,:] Περιλαμβάνει όλα τα κελιά μεταξύ των στηλών "Ποσό πωλήσεων" και "Ποσοστό προμήθειας", συμπεριλαμβανομένων των κεφαλίδων. Σε ένα τυπικό εύρος A1, αυτό θα έμοιαζε κάπως έτσι: C1:D8. Εάν θέλετε μόνο τα δεδομένα (χωρίς κεφαλίδες ή σύνολα) από αυτές τις στήλες, θα χρησιμοποιούσατε Τμήμα Πωλήσεων,:], κάτι παρόμοιο με το D2:E7.
Μια άλλη ενδιαφέρουσα περίπτωση είναι όταν συνδυάζετε κεφαλίδες και δεδομένα σε μία μόνο αναφορά, όπως π.χ. Τμήμα Πωλήσεων,,]Αυτό επιλέγει την κεφαλίδα και όλα τα δεδομένα στη στήλη "% Προμήθειας", περίπου ισοδύναμο με το D1:D7. Για να στοχεύσετε ένα συγκεκριμένο κελί στην τρέχουσα γραμμή μιας υπολογιζόμενης στήλης, θα μπορούσατε να χρησιμοποιήσετε Τμήμα Πωλήσεων]το οποίο θα ήταν, για παράδειγμα, E5 αν η τρέχουσα σειρά είναι η πέμπτη.
Είναι πολύ συνηθισμένο, κατά τη σύνταξη του προσδιοριστή #Αυτή η σειρά Σε έναν πίνακα με πολλές γραμμές, το Excel θα τον μετατρέψει αυτόματα στη σύντομη μορφή. @Και οι δύο παραστάσεις λειτουργούν το ίδιο μέσα σε μια υπολογιζόμενη στήλη, επομένως μην ανησυχείτε αν δείτε το Excel να ξαναγράφει τον τύπο σας. Αυτή είναι η αναμενόμενη συμπεριφορά.
Στρατηγικές και βέλτιστες πρακτικές κατά την εργασία με δομημένες αναφορές
Για να αξιοποιήσετε στο έπακρο τις δομημένες αναφορές, είναι καλή ιδέα να βασιστείτε σε ορισμένες Λειτουργίες του Excel και να έχετε κατά νου ορισμένες λεπτομέρειες συμπεριφοράς κατά τη μετατροπή εύρους, τη σύνδεση βιβλίων ή την τροποποίηση της δομής του πίνακα.
Ο πρώτος σύμμαχος είναι η λειτουργία του Αυτόματη συμπλήρωση τύπουΚατά την πληκτρολόγηση μέσα σε έναν πίνακα, το Excel θα προτείνει ονόματα στηλών και προσδιοριστικά, ελαχιστοποιώντας τα συντακτικά σφάλματα. Αυτό είναι ιδιαίτερα χρήσιμο με μεγάλες κεφαλίδες ή εκείνες που περιέχουν ειδικούς χαρακτήρες, όπου ένα λάθος με μια αγκύλη μπορεί να καταστρέψει τον τύπο.
Από προεπιλογή, όταν επιλέγετε ένα μπλοκ κελιών μέσα σε έναν πίνακα κατά τη σύνταξη ενός τύπου, το Excel δημιουργεί ένα αυτόματη δομημένη αναφορά αντί για ένα κλασικό εύρος. Εάν προτιμάτε να επιστρέψετε στην παλιά συμπεριφορά, μπορείτε να μεταβείτε στο Αρχείο > Επιλογές > Τύποι > Εργασία με τύπους και να επιλέξετε ή να καταργήσετε την επιλογή του πλαισίου ελέγχου "Χρήση ονομάτων πινάκων σε τύπους".
Εάν εργάζεστε με πολλά συνδεδεμένα βιβλία εργασίας, λάβετε υπόψη ότι όταν ένα αρχείο περιέχει εξωτερικούς συνδέσμους προς έναν πίνακα του Excel σε ένα άλλο βιβλίο εργασίας, το βιβλίο εργασίας προέλευσης πρέπει να είναι συνδεδεμένο. ανοιχτό Για να αποφύγετε σφάλματα #REF! στο βιβλίο εργασίας προορισμού. Εάν ανοίξετε πρώτα το βιβλίο εργασίας προορισμού και δείτε πολλά σφάλματα #REF!, αυτά συνήθως επιλύονται ανοίγοντας το αρχείο προέλευσης με τον πίνακα.
Όταν μετατρέπετε έναν πίνακα σε κανονική περιοχή, όλες οι αναφορές που σχετίζονται με αυτόν τον πίνακα μετασχηματίζονται στην περιοχή του. απόλυτα ισοδύναμα στυλ A1Ωστόσο, εάν μετατρέψετε μια περιοχή σε πίνακα, το Excel δεν τροποποιεί αυτόματα τις υπάρχουσες αναφορές σε αυτήν την περιοχή για να τις μετατρέψει σε δομημένες αναφορές. Θα πρέπει να τις προσαρμόσετε μόνοι σας εάν θέλετε να αλλάξετε το στυλ.
Μια άλλη λεπτομέρεια: από την καρτέλα σχεδίασης πίνακα μπορείτε να ενεργοποιήσετε ή να απενεργοποιήσετε το γραμμή κεφαλίδαςΕάν το αποκρύψετε, οι δομημένες αναφορές που χρησιμοποιούν ονόματα στηλών θα εξακολουθούν να λειτουργούν, αλλά οι αναφορές που οδηγούν απευθείας σε κεφαλίδες (για παράδειγμα, κάτι όπως SalesDept,]) θα επιστρέψουν ένα σφάλμα #REF.
Ένα από τα μεγάλα πλεονεκτήματα των τραπεζιών είναι ότι, όταν προσθήκη ή διαγραφή γραμμών και στηλώνΟι δομημένες αναφορές προσαρμόζονται αυτόματα. Εάν χρησιμοποιήσετε το όνομα του πίνακα σε μια συνάρτηση για να μετρήσετε γραμμές ή να αθροίσετε δεδομένα και, στη συνέχεια, προσθέσετε περισσότερες εγγραφές, η αναφορά θα ενημερωθεί για να τις συμπεριλάβει.
Επιπλέον, εάν αλλάξετε το όνομα πίνακα ή στήληςΤο Excel ενημερώνει αυτήν την αλλαγή σε όλους τους τύπους που χρησιμοποιούν αυτό το αναγνωριστικό μέσα στο βιβλίο εργασίας. Αυτό ενισχύει τη χρήση σαφών και περιγραφικών ονομάτων, επειδή γνωρίζετε ότι μπορείτε να τα μετονομάσετε χωρίς φόβο να προκληθούν προβλήματα στους τύπους.
Τέλος, όταν αντιγράφετε ή μετακινείτε τύπους που περιέχουν δομημένες αναφορές, το Οι προσδιοριστές συνήθως διατηρούνται Ακριβώς. Αν γεμίσετε προς τα πάνω ή προς τα κάτω, το Excel δεν προσαρμόζει τα ονόματα των στηλών από προεπιλογή. Αν το κάνετε ενώ κρατάτε πατημένο το Ctrl, μπορεί να τα αντιμετωπίσει ως σειρά. Κατά τη συμπλήρωση αριστερά ή δεξιά, οι στήλες μετατοπίζονται σαν να ήταν ακολουθία και αν γεμίσετε ενώ κρατάτε πατημένο το πλήκτρο Shift, αντί να αντικατασταθούν, εισάγονται νέες στήλες και οι τρέχουσες τιμές μετατοπίζονται.
Η κατανόηση όλων αυτών των κομματιών—καλά μελετημένα ονόματα πινάκων, σωστή σύνταξη, χρήση προσδιοριστών, τελεστές αναφοράς και καλές πρακτικές επεξεργασίας—καθιστά τις δομημένες αναφορές έναν πολύ ισχυρό πόρο για εσάς. Τα μοντέλα του Excel θα πρέπει να είναι πιο σαφή, πιο ευέλικτα και πιο ανθεκτικά στις αλλαγές.Αυτό είναι ιδιαίτερα πολύτιμο όταν τα αρχεία μεγαλώνουν και κοινοποιούνται μεταξύ πολλών χρηστών.