Δομημένες αναφορές στο Excel | Οδηγός βήμα προς βήμα με παραδείγματα

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

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

Τώρα ρίξτε μια ματιά στην παρακάτω εικόνα.

  • Βήμα 1: Είχα δώσει έναν σύνδεσμο για το κελί B3, αντί να δείξω τον σύνδεσμο ως B2 που εμφανίζεται ως Πίνακας1 [@Sales]. Εδώ ο Πίνακας 1 είναι το όνομα του πίνακα και το @Sales είναι η στήλη στην οποία αναφερόμαστε. Όλα τα κελιά σε αυτήν τη στήλη αναφέρονται με ένα όνομα πίνακα και ακολουθούν το όνομα επικεφαλίδας στήλης.
  • Βήμα 2: Τώρα θα αλλάξω το όνομα του πίνακα σε Data_Table και θα αλλάξω την επικεφαλίδα της στήλης σε Amount .
  • Βήμα 3: Για να αλλάξετε το όνομα του πίνακα τοποθετήστε έναν κέρσορα μέσα στον πίνακα> μεταβείτε στο Σχεδίαση> Όνομα πίνακα.

  • Βήμα 4: Αναφέρετε το όνομα του πίνακα ως Data_Table.

  • Βήμα 5: Τώρα αλλαγή δώστε μια αναφορά στο κελί B3.

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

Παραδείγματα

Μπορείτε να κάνετε λήψη αυτού του προτύπου Structured References Excel - Πρότυπο Structured References Excel

Παράδειγμα # 1

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

Επιτρέψτε μου να εφαρμόσω τον τύπο SUM τόσο για κανονικό εύρος όσο και για πίνακα excel

Τύπος SUM για Κανονικό εύρος.

Τύπος SUM για πίνακα Excel.

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

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

Παράδειγμα # 2

Τώρα, δείτε ένα ακόμη παράδειγμα. Έχω πληροφορίες για το όνομα προϊόντος, την ποσότητα και την τιμή. Χρησιμοποιώντας αυτές τις πληροφορίες πρέπει να φτάσω στην Αξία Πωλήσεων.

Για να αποκτήσετε την αξία των πωλήσεων, ο τύπος είναι Qty * Price . Ας εφαρμόσουμε αυτόν τον τύπο στον πίνακα.

Η φόρμουλα λέει [@QTY] * [@PRICE]. Αυτό είναι πιο κατανοητό από την κανονική αναφορά του B2 * C2. Δεν παίρνουμε το όνομα του πίνακα εάν βάζουμε τον τύπο μέσα στον πίνακα.

Προβλήματα με τις δομές του Excel

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

Πρόβλημα # 1

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

Τώρα, δείτε το παρακάτω παράδειγμα. Έχω εφαρμόσει τον τύπο SUM στο excel για το κανονικό εύρος.

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

Τώρα εφαρμόστε τον ίδιο τύπο για τον πίνακα excel για τη στήλη Ποσότητα.

Τώρα έχουμε το άθροισμα της στήλης Qty. Όπως το κανονικό εύρος, αντιγράψτε τον τρέχοντα τύπο και επικολλήστε τον στη στήλη Τιμή για να λάβετε το σύνολο της τιμής.

Ω Θεέ μου!!! Δεν δείχνει το σύνολο της στήλης Τιμή αλλά εξακολουθεί να δείχνει το σύνολο της στήλης Ποσότητα μόνο. Επομένως, δεν μπορούμε να αντιγράψουμε και να επικολλήσουμε αυτόν τον τύπο στο παρακείμενο κελί ή σε οποιοδήποτε άλλο κελί για να αναφερθούμε στη σχετική στήλη ή σειρά.

Σύρετε τον τύπο για να αλλάξετε την αναφορά

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

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

Τώρα έχουμε ενημερωμένους τύπους για να λάβουμε τα αντίστοιχα σύνολα.

Πρόβλημα # 2

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

Ας ρίξουμε μια ματιά στο παρακάτω παράδειγμα τώρα. Έχω έναν πίνακα πωλήσεων με πολλές καταχωρήσεις και θέλω να ενοποιήσω τα δεδομένα χρησιμοποιώντας τη συνάρτηση SUMIF στο excel.

Τώρα θα εφαρμόσω τη λειτουργία SUMIF για να λάβω τις συγκεντρωτικές τιμές πωλήσεων για κάθε προϊόν.

Έχω εφαρμόσει τον τύπο για τον μήνα Ιανουάριο, δεδομένου ότι πρόκειται για δομημένη αναφορά, δεν μπορούμε να αντιγράψουμε και να επικολλήσουμε τον τύπο στις υπόλοιπες δύο στήλες, δεν θα αλλάξει την αναφορά σε Φεβρουάριο και Μαρ, οπότε θα μεταφέρω τον τύπο.

Ω !! Δεν έλαβα τιμές στη στήλη Φεβρουαρίου και Μαρ. Ποιο θα ήταν το πρόβλημα ;;; Κοιτάξτε προσεκτικά τον τύπο.

Έχουμε μεταφέρει τον τύπο από τον μήνα Ιαν. Στη συνάρτηση SUMIF το πρώτο όρισμα είναι το Criteria Range Sales_Table [Product],  αφού σύραμε τον τύπο που αλλάζει σε Sales _Table [Jan].

Λοιπόν, πώς το αντιμετωπίζουμε ;; Πρέπει να κάνουμε το πρώτο όρισμα, δηλαδή τη στήλη προϊόντος ως απόλυτη και άλλες στήλες ως σχετική αναφορά. Σε αντίθεση με την κανονική αναφορά, δεν έχουμε την πολυτέλεια να χρησιμοποιήσουμε το πλήκτρο F4 για να αλλάξουμε τον τύπο αναφοράς.

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

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

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

= Πίνακας πωλήσεων [@ [Προϊόν]: [Προϊόν]]

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

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

  • Βήμα 1: Μεταβείτε στο FILE> Επιλογές.
  • Βήμα 2: Τύποι> Καταργήστε την επιλογή Χρήση ονομάτων πινάκων στους τύπους.

Πράγματα που πρέπει να θυμάστε

  • Για να κάνουμε την απόλυτη αναφορά σε δομημένη αναφορά, πρέπει να διπλασιάσουμε το όνομα της στήλης.
  • Δεν μπορούμε να αντιγράψουμε τον τύπο δομημένης αναφοράς, αλλά πρέπει να σύρουμε τον τύπο.
  • Δεν μπορούμε να δούμε ακριβώς σε ποιο κελί αναφερόμαστε σε δομημένες αναφορές.
  • Εάν δεν σας ενδιαφέρει δομημένες αναφορές, μπορείτε να τις απενεργοποιήσετε.