Περιεχόμενα
Έχετε πίνακες με δεδομένα στο Excel που μπορούν να αλλάξουν μέγεθος, δηλαδή ο αριθμός των γραμμών (στηλών) μπορεί να αυξηθεί ή να μειωθεί στην πορεία της εργασίας; Εάν τα μεγέθη του τραπεζιού "επιπλέουν", τότε θα πρέπει να παρακολουθείτε συνεχώς αυτή τη στιγμή και να τη διορθώνετε:
- συνδέσμους σε τύπους αναφοράς που αναφέρονται στον πίνακά μας
- αρχικές περιοχές συγκεντρωτικών πινάκων που είναι κατασκευασμένες σύμφωνα με τον πίνακά μας
- αρχικά εύρη διαγραμμάτων που κατασκευάστηκαν σύμφωνα με τον πίνακά μας
- εύρη για αναπτυσσόμενα μενού που χρησιμοποιούν τον πίνακά μας ως πηγή δεδομένων
Όλα αυτά συνολικά δεν θα σας αφήσουν να βαρεθείτε 😉
Θα είναι πολύ πιο βολικό και σωστό να δημιουργήσετε μια δυναμική περιοχή "καουτσούκ", η οποία θα προσαρμόζεται αυτόματα σε μέγεθος στον πραγματικό αριθμό σειρών και στηλών δεδομένων. Για να το εφαρμόσετε αυτό, υπάρχουν διάφοροι τρόποι.
Μέθοδος 1. Έξυπνο τραπέζι
Επισημάνετε το εύρος των κελιών σας και επιλέξτε από την καρτέλα Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα – Μορφοποίηση ως πίνακα):
Εάν δεν χρειάζεστε το ριγέ σχέδιο που προστίθεται στον πίνακα ως παρενέργεια, τότε μπορείτε να το απενεργοποιήσετε στην καρτέλα που εμφανίζεται Κατασκευαστής (Σχεδιασμός). Κάθε πίνακας που δημιουργείται με αυτόν τον τρόπο λαμβάνει ένα όνομα που μπορεί να αντικατασταθεί με ένα πιο βολικό στην ίδια θέση στην καρτέλα Κατασκευαστής (Σχεδιασμός) στο πεδίο Όνομα πίνακα (Όνομα πίνακα).
Τώρα μπορούμε να χρησιμοποιήσουμε δυναμικούς συνδέσμους στο "έξυπνο τραπέζι" μας:
- Πίνακας 1 – σύνδεσμος σε ολόκληρο τον πίνακα εκτός από τη σειρά κεφαλίδας (A2:D5)
- Πίνακας 1[#Όλα] – σύνδεσμος σε ολόκληρο τον πίνακα (A1:D5)
- Πίνακας 1 [Πέτρος] – αναφορά σε στήλη-εύρος χωρίς την πρώτη κεφαλίδα κελιού (C2:C5)
- Πίνακας 1[#Headers] – σύνδεσμος στην «κεφαλίδα» με τα ονόματα των στηλών (A1:D1)
Τέτοιες αναφορές λειτουργούν εξαιρετικά σε τύπους, για παράδειγμα:
= SUM (Πίνακας 1[Μόσχα]) – υπολογισμός του αθροίσματος για τη στήλη "Μόσχα"
or
=VPR(F5;Πίνακας 1;3;0) – αναζητήστε στον πίνακα για τον μήνα από το κελί F5 και εκδώστε το άθροισμα της Αγίας Πετρούπολης για αυτόν (τι είναι το VLOOKUP;)
Τέτοιοι σύνδεσμοι μπορούν να χρησιμοποιηθούν με επιτυχία κατά τη δημιουργία συγκεντρωτικών πινάκων επιλέγοντας στην καρτέλα Εισαγωγή – Συγκεντρωτικός πίνακας (Εισαγωγή – Συγκεντρωτικός πίνακας) και εισάγοντας το όνομα του έξυπνου πίνακα ως πηγή δεδομένων:
Εάν επιλέξετε ένα τμήμα ενός τέτοιου πίνακα (για παράδειγμα, τις δύο πρώτες στήλες) και δημιουργήσετε ένα διάγραμμα οποιουδήποτε τύπου, τότε κατά την προσθήκη νέων γραμμών, θα προστεθούν αυτόματα στο διάγραμμα.
Κατά τη δημιουργία αναπτυσσόμενων λιστών, δεν μπορούν να χρησιμοποιηθούν άμεσοι σύνδεσμοι προς έξυπνα στοιχεία πίνακα, αλλά μπορείτε εύκολα να παρακάμψετε αυτόν τον περιορισμό χρησιμοποιώντας ένα τακτικό τέχνασμα - χρησιμοποιήστε τη λειτουργία ΕΜΜΕΣΟΣ (ΕΜΜΕΣΟΣ), που μετατρέπει το κείμενο σε σύνδεσμο:
Εκείνοι. ένας σύνδεσμος σε έναν έξυπνο πίνακα με τη μορφή συμβολοσειράς κειμένου (σε εισαγωγικά!) μετατρέπεται σε πλήρη σύνδεσμο και η αναπτυσσόμενη λίστα το αντιλαμβάνεται κανονικά.
Μέθοδος 2: Δυναμική περιοχή με όνομα
Εάν η μετατροπή των δεδομένων σας σε έξυπνο πίνακα δεν είναι επιθυμητή για κάποιο λόγο, τότε μπορείτε να χρησιμοποιήσετε μια ελαφρώς πιο περίπλοκη, αλλά πολύ πιο λεπτή και ευέλικτη μέθοδο – να δημιουργήσετε μια δυναμική περιοχή με όνομα στο Excel που αναφέρεται στον πίνακά μας. Στη συνέχεια, όπως στην περίπτωση ενός έξυπνου πίνακα, μπορείτε να χρησιμοποιήσετε ελεύθερα το όνομα της περιοχής που δημιουργήθηκε σε οποιουσδήποτε τύπους, αναφορές, γραφήματα κ.λπ. Ας ξεκινήσουμε με ένα απλό παράδειγμα:
Έργο: δημιουργήστε ένα δυναμικό εύρος με όνομα που θα αναφέρεται σε μια λίστα πόλεων και θα επεκτείνεται και θα συρρικνώνεται αυτόματα σε μέγεθος κατά την προσθήκη νέων πόλεων ή τη διαγραφή τους.
Θα χρειαστούμε δύο ενσωματωμένες συναρτήσεις Excel διαθέσιμες σε οποιαδήποτε έκδοση − POICPOZ (ΑΓΩΝΑΣ) για να προσδιορίσετε το τελευταίο κελί της περιοχής και ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ) για να δημιουργήσετε έναν δυναμικό σύνδεσμο.
Εύρεση του τελευταίου κελιού χρησιμοποιώντας το MATCH
MATCH(τιμή_αναζήτησης, εύρος, αντιστοίχιση_τύπος) – μια συνάρτηση που αναζητά μια δεδομένη τιμή σε μια περιοχή (γραμμή ή στήλη) και επιστρέφει τον τακτικό αριθμό του κελιού όπου βρέθηκε. Για παράδειγμα, ο τύπος MATCH("March";A1:A5;0) θα επιστρέψει τον αριθμό 4 ως αποτέλεσμα, επειδή η λέξη "March" βρίσκεται στο τέταρτο κελί της στήλης A1:A5. Το τελευταίο όρισμα συνάρτησης Match_Type = 0 σημαίνει ότι ψάχνουμε για μια ακριβή αντιστοίχιση. Εάν αυτό το όρισμα δεν έχει καθοριστεί, τότε η συνάρτηση θα μεταβεί στη λειτουργία αναζήτησης για την πλησιέστερη μικρότερη τιμή - αυτό ακριβώς μπορεί να χρησιμοποιηθεί με επιτυχία για την εύρεση του τελευταίου κατειλημμένου κελιού στον πίνακα μας.
Η ουσία του κόλπου είναι απλή. Το MATCH αναζητά κελιά στο εύρος από πάνω προς τα κάτω και, θεωρητικά, θα πρέπει να σταματήσει όταν βρει την πλησιέστερη μικρότερη τιμή στη δεδομένη. Εάν καθορίσετε μια τιμή που είναι προφανώς μεγαλύτερη από οποιαδήποτε διαθέσιμη στον πίνακα ως την επιθυμητή τιμή, τότε το MATCH θα φτάσει στο τέλος του πίνακα, θα βρει τίποτα και θα δώσει τον αριθμό σειράς του τελευταίου συμπληρωμένου κελιού. Και το χρειαζόμαστε!
Εάν υπάρχουν μόνο αριθμοί στον πίνακα μας, τότε μπορούμε να καθορίσουμε έναν αριθμό ως την επιθυμητή τιμή, ο οποίος είναι προφανώς μεγαλύτερος από οποιονδήποτε από αυτούς του πίνακα:
Για εγγύηση, μπορείτε να χρησιμοποιήσετε τον αριθμό 9E + 307 (9 επί 10 με την ισχύ του 307, δηλαδή 9 με 307 μηδενικά) – ο μέγιστος αριθμός με τον οποίο μπορεί να εργαστεί το Excel καταρχήν.
Εάν υπάρχουν τιμές κειμένου στη στήλη μας, τότε ως ισοδύναμο του μεγαλύτερου δυνατού αριθμού, μπορείτε να εισαγάγετε την κατασκευή REPEAT("i", 255) - μια συμβολοσειρά κειμένου που αποτελείται από 255 γράμματα "i" - το τελευταίο γράμμα του το αλφάβητο. Δεδομένου ότι ο Excel συγκρίνει τους κωδικούς χαρακτήρων κατά την αναζήτηση, οποιοδήποτε κείμενο στο τραπέζι μας θα είναι τεχνικά "μικρότερο" από μια τόσο μεγάλη γραμμή "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" γραμμή:
Δημιουργήστε έναν σύνδεσμο χρησιμοποιώντας το INDEX
Τώρα που γνωρίζουμε τη θέση του τελευταίου μη κενού στοιχείου στον πίνακα, μένει να σχηματίσουμε έναν σύνδεσμο προς ολόκληρο το εύρος μας. Για αυτό χρησιμοποιούμε τη συνάρτηση:
INDEX(εύρος; σειρά_αριθμός; στήλη_αριθμός)
Δίνει τα περιεχόμενα του κελιού από το εύρος ανά σειρά και αριθμό στήλης, δηλαδή, για παράδειγμα, η συνάρτηση =INDEX(A1:D5;3;4) στον πίνακά μας με πόλεις και μήνες από την προηγούμενη μέθοδο θα δώσει 1240 - το περιεχόμενο από την 3η σειρά και την 4η στήλη, δηλαδή τα κελιά D3. Εάν υπάρχει μόνο μία στήλη, τότε ο αριθμός της μπορεί να παραλειφθεί, δηλαδή ο τύπος INDEX(A2:A6;3) θα δώσει "Samara" στο τελευταίο στιγμιότυπο οθόνης.
Και υπάρχει μια όχι εντελώς προφανής απόχρωση: εάν το INDEX δεν εισαχθεί απλώς στο κελί μετά το σύμβολο =, ως συνήθως, αλλά χρησιμοποιείται ως το τελευταίο μέρος της αναφοράς στο εύρος μετά την άνω τελεία, τότε δεν εμφανίζεται πλέον το περιεχόμενο του κελιού, αλλά η διεύθυνσή του! Έτσι, ένας τύπος όπως $A$2:INDEX($A$2:$A$100;3) θα δώσει μια αναφορά στην περιοχή A2:A4 στην έξοδο.
Και εδώ μπαίνει η συνάρτηση MATCH, την οποία εισάγουμε μέσα στο INDEX για να προσδιορίσουμε δυναμικά το τέλος της λίστας:
=$A$2:INDEX($A$2:$A$100; ΑΓΩΝΑΣ (REP("I";255);A2:A100))
Δημιουργήστε ένα εύρος με όνομα
Μένει να τα συσκευάσουμε όλα σε ένα ενιαίο σύνολο. Ανοίξτε μια καρτέλα τύπος (ΜΑΘΗΜΑΤΙΚΟΙ τυποι) Και κάντε κλικ στο Όνομα Διευθυντής (Όνομα διαχειριστή). Στο παράθυρο που ανοίγει, κάντε κλικ στο κουμπί Δημιουργία (νέος), εισάγετε το όνομα και τον τύπο της περιοχής μας στο πεδίο Σειρά (Αναφορά):
Απομένει να κάνετε κλικ OK και το έτοιμο εύρος μπορεί να χρησιμοποιηθεί σε οποιουσδήποτε τύπους, αναπτυσσόμενες λίστες ή γραφήματα.
- Χρησιμοποιώντας τη συνάρτηση VLOOKUP για τη σύνδεση πινάκων και τιμών αναζήτησης
- Πώς να δημιουργήσετε μια αναπτυσσόμενη λίστα αυτόματης συμπλήρωσης
- Πώς να δημιουργήσετε έναν συγκεντρωτικό πίνακα για την ανάλυση μεγάλου όγκου δεδομένων