Τα υπολογιστικά φύλλα Excel περιλαμβάνουν συχνά αναπτυσσόμενα κυκλώματα για απλοποίηση και / ή τυποποίηση της εισαγωγής δεδομένων. Αυτά τα αναπτυσσόμενα μενού δημιουργούνται χρησιμοποιώντας τη λειτουργία επικύρωσης δεδομένων για να καθορίσετε μια λίστα επιτρεπόμενων καταχωρήσεων.
Για να ρυθμίσετε μια απλή αναπτυσσόμενη λίστα, επιλέξτε το κελί όπου θα εισαχθούν τα δεδομένα, στη συνέχεια κάντε κλικ στην επιλογή Επικύρωση δεδομένων (στην καρτέλα Δεδομένα ), επιλέξτε Επικύρωση δεδομένων, επιλέξτε Λίστα (στην περιοχή Επιτρέπεται :) και, στη συνέχεια, εισάγετε τα στοιχεία της λίστας ) στο πεδίο Πηγή : (δείτε Εικόνα 1).
Σε αυτόν τον τύπο βασικού αναπτυσσόμενου παραθύρου, ο κατάλογος των επιτρεπόμενων καταχωρήσεων καθορίζεται από την ίδια την επικύρωση δεδομένων. Επομένως, για να κάνετε αλλαγές στη λίστα, ο χρήστης πρέπει να ανοίξει και να επεξεργαστεί την επικύρωση δεδομένων. Ωστόσο, αυτό μπορεί να είναι δύσκολο για τους άπειρους χρήστες ή σε περιπτώσεις όπου η λίστα των επιλογών είναι μακρά.
Μια άλλη επιλογή είναι να τοποθετήσετε τη λίστα σε μια ονομαστική περιοχή μέσα στο υπολογιστικό φύλλο και, στη συνέχεια, να καθορίσετε αυτό το όνομα περιοχής (προγεγραμμένο με ένα ισοδύναμο σημάδι) στο πεδίο Πηγή : επικύρωση δεδομένων (όπως φαίνεται στο σχήμα 2).
Αυτή η δεύτερη μέθοδος διευκολύνει την επεξεργασία των επιλογών στη λίστα, αλλά η προσθήκη ή η αφαίρεση στοιχείων μπορεί να είναι προβληματική. Δεδομένου ότι η ονομαστική περιοχή (FruitChoices, στο παράδειγμά μας) αναφέρεται σε σταθερό εύρος κυψελών ($ H $ 3: $ H $ 10 όπως φαίνεται), εάν προστεθούν περισσότερες επιλογές στα κελιά H11 ή παρακάτω, δεν θα εμφανιστούν στο αναπτυσσόμενο μενού (δεδομένου ότι αυτά τα κύτταρα δεν αποτελούν μέρος της σειράς FruitChoices).
Παρόμοια, εάν, για παράδειγμα, οι καταχωρίσεις Αχλάδια και Φράουλες διαγράφονται, δεν θα εμφανίζονται πλέον στην αναπτυσσόμενη λίστα, αλλά το dropdown θα περιλαμβάνει δύο "άδειες" επιλογές, αφού το dropdown εξακολουθεί να αναφέρει ολόκληρη την περιοχή FruitChoices, συμπεριλαμβανομένων των κελιών H9 και H10.
Για τους λόγους αυτούς, όταν χρησιμοποιείται μια κανονική ονομαστική περιοχή ως πηγή καταλόγου για ένα αναπτυσσόμενο μενού, το ίδιο το όριο πρέπει να επεξεργαστεί ώστε να περιλαμβάνει περισσότερα ή λιγότερα κελιά αν προστεθούν ή διαγραφούν καταχωρήσεις από τη λίστα.
Μια λύση σε αυτό το πρόβλημα είναι να χρησιμοποιήσετε ένα όνομα δυναμικής εμβέλειας ως πηγή για τις επιλογές dropdown. Ένα όνομα δυναμικής περιοχής είναι αυτό που επεκτείνεται αυτόματα (ή συμβάσεις) για να ταιριάζει ακριβώς με το μέγεθος ενός μπλοκ δεδομένων, καθώς οι καταχωρήσεις προστίθενται ή αφαιρούνται. Για να το κάνετε αυτό, μπορείτε να χρησιμοποιήσετε έναν τύπο, αντί για ένα καθορισμένο εύρος διευθύνσεων κελιών, για να ορίσετε την ονομαστική περιοχή.
Πώς να ρυθμίσετε μια δυναμική περιοχή στο Excel
Ένα κανονικό (στατικό) εύρος περιοχής αναφέρεται σε ένα συγκεκριμένο εύρος κελιών ($ H $ 3: $ H $ 10 στο παράδειγμά μας, δείτε παρακάτω):
Αλλά ένα δυναμικό εύρος ορίζεται με έναν τύπο (βλ. Παρακάτω, που λαμβάνεται από ένα ξεχωριστό υπολογιστικό φύλλο που χρησιμοποιεί ονόματα δυναμικής εμβέλειας):
Πριν ξεκινήσουμε, βεβαιωθείτε ότι έχετε κατεβάσει το παράδειγμα του αρχείου Excel (οι μακροεντολές ταξινόμησης έχουν απενεργοποιηθεί).
Ας εξετάσουμε λεπτομερώς αυτόν τον τύπο. Οι επιλογές για τα φρούτα είναι σε ένα μπλοκ κελιών ακριβώς κάτω από μια επικεφαλίδα ( FRUITS ). Στην εν λόγω επικεφαλίδα δίνεται επίσης ένα όνομα: Οπωροκηπευτικά :
Ολόκληρος ο τύπος που χρησιμοποιείται για τον ορισμό του δυναμικού εύρους για τις επιλογές φρούτων είναι:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (ΑΛΗΘΙΝΟ, ΔΕΙΚΤΗΣ (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1), 0, 0), -1)
Το FruitsHeading αναφέρεται στην επικεφαλίδα που είναι μία γραμμή πάνω από την πρώτη καταχώρηση στη λίστα. Ο αριθμός 20 (που χρησιμοποιείται δύο φορές στον τύπο) είναι το μέγιστο μέγεθος (αριθμός γραμμών) για τη λίστα (αυτό μπορεί να ρυθμιστεί όπως επιθυμείτε).
Σημειώστε ότι σε αυτό το παράδειγμα, υπάρχουν μόνο 8 καταχωρήσεις στη λίστα, αλλά υπάρχουν και κενά κελιά κάτω από αυτά όπου μπορούν να προστεθούν επιπλέον καταχωρήσεις. Ο αριθμός 20 αναφέρεται σε ολόκληρο το μπλοκ όπου μπορούν να γίνουν καταχωρήσεις, όχι στον πραγματικό αριθμό εγγραφών.
Τώρα ας καταρρίψουμε τη φόρμουλα σε κομμάτια (χρωματική κωδικοποίηση για κάθε κομμάτι), για να καταλάβουμε πώς λειτουργεί:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (ΑΛΗΘΙΝΟ, ΔΕΙΚΤΗΣ (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1 ), 0, 0), -1)
Το "εσώτατο" κομμάτι είναι OFFSET (FruitsHeading, 1, 0, 20, 1) . Αυτό αναφέρει το μπλοκ των 20 κελιών (κάτω από το στοιχείο FruitsHeading) όπου μπορούν να εισαχθούν επιλογές. Αυτή η συνάρτηση OFFSET λέει βασικά: Ξεκινήστε από το στοιχείο FruitsHeading, κατεβείτε μια σειρά και πάνω από 0 στήλες και, στη συνέχεια, επιλέξτε μια περιοχή που έχει μήκος 20 σειρές και πλάτος 1 στήλη. Έτσι, αυτό μας δίνει το μπλοκ 20 γραμμών όπου εισάγονται οι επιλογές φρούτων.
Το επόμενο κομμάτι του τύπου είναι η λειτουργία ISBLANK :
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (ΑΛΗΘΙΝΟ, INDEX ( ISBLANK (παραπάνω), 0, 0), 0) -1, 20)
Εδώ, η λειτουργία OFFSET (εξηγείται παραπάνω) έχει αντικατασταθεί με "τα παραπάνω" (για να γίνει ευκολότερο να διαβαστούν τα πράγματα). Αλλά η λειτουργία ISBLANK λειτουργεί σε μια σειρά 20 κυκλωμάτων που ορίζει η συνάρτηση OFFSET.
Στη συνέχεια, το ISBLANK δημιουργεί ένα σύνολο 20 αξιών TRUE και FALSE, υποδεικνύοντας εάν κάθε ένα από τα μεμονωμένα κελιά στην περιοχή 20 γραμμών που αναφέρονται από τη λειτουργία OFFSET είναι κενό (κενό) ή όχι. Σε αυτό το παράδειγμα, οι πρώτες 8 τιμές στο σύνολο θα είναι FALSE αφού τα πρώτα 8 κελιά δεν είναι κενά και οι τελευταίες 12 τιμές θα είναι TRUE.
Το επόμενο κομμάτι του τύπου είναι η λειτουργία INDEX:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (τα παραπάνω, 0, 0), 0) -1, 20), 1)
Και πάλι, το "παραπάνω" αναφέρεται στις λειτουργίες ISBLANK και OFFSET που περιγράφηκαν παραπάνω. Η συνάρτηση INDEX επιστρέφει έναν πίνακα που περιέχει τις 20 αξίες TRUE / FALSE που δημιουργούνται από τη λειτουργία ISBLANK.
Το INDEX συνήθως χρησιμοποιείται για να επιλέξει μια συγκεκριμένη τιμή (ή εύρος τιμών) από ένα μπλοκ δεδομένων, καθορίζοντας μια συγκεκριμένη σειρά και στήλη (μέσα σε αυτό το μπλοκ). Αλλά η ρύθμιση των εισόδων γραμμής και στήλης στο μηδέν (όπως γίνεται εδώ) προκαλεί στο INDEX να επιστρέψει έναν πίνακα που περιέχει ολόκληρο το μπλοκ δεδομένων.
Το επόμενο κομμάτι του τύπου είναι η συνάρτηση MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (ΑΛΗΘΙΝΟ, τα παραπάνω, 0) -1, 20), 1)
Η συνάρτηση MATCH επιστρέφει τη θέση της πρώτης τιμής TRUE, μέσα στον πίνακα που επιστρέφεται από τη λειτουργία INDEX. Εφόσον οι πρώτες 8 καταχωρήσεις στη λίστα δεν είναι κενές, οι πρώτες 8 τιμές στη συστοιχία θα είναι FALSE και η ένατη θα είναι TRUE (δεδομένου ότι η 9η σειρά στο εύρος είναι κενή).
Έτσι, η συνάρτηση MATCH θα επαναφέρει την τιμή 9 . Σε αυτήν την περίπτωση, ωστόσο, θέλουμε πραγματικά να γνωρίζουμε πόσες καταχωρήσεις είναι στη λίστα, οπότε ο τύπος αφαιρεί 1 από την τιμή MATCH (η οποία δίνει τη θέση της τελευταίας καταχώρησης). Έτσι τελικά, το MATCH (TRUE, το παραπάνω, 0) -1 επιστρέφει την τιμή 8 .
Το επόμενο κομμάτι του τύπου είναι η λειτουργία IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (τα παραπάνω, 20), 1)
Η συνάρτηση IFERROR επιστρέφει μια εναλλακτική τιμή, αν η πρώτη τιμή που καθορίζεται προκαλεί σφάλμα. Αυτή η συνάρτηση περιλαμβάνεται αφού, εάν ολόκληρο το μπλοκ κελιών (και οι 20 σειρές) γεμίζονται με καταχωρήσεις, η λειτουργία MATCH θα επιστρέψει ένα σφάλμα.
Αυτό οφείλεται στο γεγονός ότι λέμε στη συνάρτηση MATCH την αναζήτηση της πρώτης τιμής TRUE (στη σειρά τιμών από τη συνάρτηση ISBLANK), αλλά αν δεν υπάρχει κενού, τότε ολόκληρος ο πίνακας θα γεμίσει με τιμές FALSE. Εάν το MATCH δεν μπορεί να βρει την τιμή στόχου (TRUE) σε πίνακα που αναζητά, επιστρέφει ένα σφάλμα.
Επομένως, εάν ολόκληρη η λίστα είναι πλήρης (και συνεπώς, το MATCH επιστρέφει ένα σφάλμα), η λειτουργία IFERROR θα επιστρέψει την τιμή των 20 (γνωρίζοντας ότι πρέπει να υπάρχουν 20 εγγραφές στη λίστα).
Τέλος, το OFFSET (FruitsHeading, 1, 0, το παραπάνω, 1) επιστρέφει το εύρος που πραγματικά αναζητούμε: Ξεκινήστε από το κελί FruitsHeading, κατεβείτε μια σειρά και πάνω από 0 στήλες και στη συνέχεια επιλέξτε μια περιοχή που έχει πολλές σειρές υπάρχουν καταχωρίσεις στη λίστα (και 1 στήλη ευρεία). Ολόκληρος ο τύπος μαζί θα επιστρέψει το εύρος που περιέχει μόνο τις πραγματικές καταχωρήσεις (μέχρι το πρώτο κενό κελί).
Χρησιμοποιώντας αυτόν τον τύπο για να ορίσετε την περιοχή που είναι η πηγή για το αναπτυσσόμενο μενού, μπορείτε να επεξεργαστείτε ελεύθερα τη λίστα (προσθέτοντας ή αφαιρώντας καταχωρήσεις, αρκεί οι υπόλοιπες καταχωρήσεις να ξεκινούν από το κορυφαίο κελί και να είναι συνεχόμενες) και το αναπτυσσόμενο μενού θα αντικατοπτρίζει πάντα το τρέχον (βλ. Εικόνα 6).
Το αρχείο παραδειγμάτων (δυναμικές λίστες) που χρησιμοποιήθηκε εδώ συμπεριλαμβάνεται και μπορεί να μεταφορτωθεί από αυτόν τον ιστότοπο. Οι μακροεντολές δεν λειτουργούν, ωστόσο, επειδή το WordPress δεν αρέσει τα βιβλία του Excel με μακροεντολές σε αυτά.
Ως εναλλακτική λύση για τον προσδιορισμό του αριθμού των γραμμών στο μπλοκ λίστας, το μπλοκ λίστας μπορεί να έχει το δικό του όνομα εύρους, το οποίο στη συνέχεια μπορεί να χρησιμοποιηθεί σε έναν τροποποιημένο τύπο. Στο παράδειγμα του αρχείου, μια δεύτερη λίστα (ονόματα) χρησιμοποιεί αυτή τη μέθοδο. Εδώ, ολόκληρο το μπλοκ λίστας (κάτω από την επικεφαλίδα "NAMES", 40 σειρές στο αρχείο παραδειγμάτων) έχει αντιστοιχιστεί το όνομα εύρους του NameBlock . Ο εναλλακτικός τύπος για τον ορισμό του NamesList είναι:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (ΑΛΗΘΙΝΟ, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock )
όπου το NamesBlock αντικαθιστά το OFFSET (FruitsHeading, 1, 0, 20, 1) και το ROWS (NamesBlock) αντικαθιστά το 20 (αριθμός γραμμών) στον παλαιότερο τύπο.
Έτσι, για λίστες με αναπτυσσόμενες λίστες, οι οποίες μπορούν εύκολα να επεξεργαστούν (συμπεριλαμβανομένων και άλλων χρηστών που μπορεί να είναι άπειροι), δοκιμάστε να χρησιμοποιήσετε ονόματα δυναμικής εμβέλειας! Και σημειώστε ότι, παρόλο που το άρθρο αυτό έχει επικεντρωθεί σε λίστες με αναπτυσσόμενες γραμμές, τα ονόματα δυναμικής εμβέλειας μπορούν να χρησιμοποιηθούν οπουδήποτε πρέπει να αναφέρετε ένα εύρος ή μια λίστα που μπορεί να ποικίλει σε μέγεθος. Απολαμβάνω!
