Πώς να συνδέσετε μια αναπτυσσόμενη λίστα στο Excel

Θα θέλατε να μάθετε πώς συνδέστε μια αναπτυσσόμενη λίστα στο excel? Μια αναπτυσσόμενη λίστα του Excel είναι μια χρήσιμη δυνατότητα όταν δημιουργείτε φόρμες εισαγωγής δεδομένων ή πίνακες εργαλείων του Excel.

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

Παράδειγμα αναπτυσσόμενης λίστας στο Excel

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

Εδώ μπορείτε να διαβάσετε για: Πώς να αντιγράψετε ένα φύλλο Excel σε άλλο βιβλίο εργασίας – Οδηγός

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

Αυτές ονομάζονται εξαρτημένες αναπτυσσόμενες λίστες στο Excel.

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

Μπορείτε να δείτε ότι οι επιλογές στο αναπτυσσόμενο μενού 2 εξαρτώνται από την επιλογή που έγινε στο αναπτυσσόμενο μενού 1.

Εάν επιλέξετε 'Καρπός' Στο αναπτυσσόμενο μενού 1, εμφανίζονται τα ονόματα των φρούτων, αλλά εάν επιλέξετε Λαχανικά στο αναπτυσσόμενο μενού 1, τότε τα ονόματα των λαχανικών εμφανίζονται στο αναπτυσσόμενο μενού 2. Αυτό ονομάζεται αναπτυσσόμενη λίστα υπό όρους ή εξαρτημένη αναπτυσσόμενη λίστα στο Excel.

Δημιουργήστε μια εξαρτημένη αναπτυσσόμενη λίστα στο Excel

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

• βήμα 1: Επιλέξτε το κελί όπου θέλετε την πρώτη (κύρια) αναπτυσσόμενη λίστα.
• βήμα 2: παω σε Δεδομένα -> Επικύρωση δεδομένων. Αυτό θα ανοίξει το παράθυρο διαλόγου επικύρωσης δεδομένων.

• βήμα 3: Στο πλαίσιο διαλόγου επικύρωσης δεδομένων, στην καρτέλα διαμόρφωσης, ορίστε την επιλογή κατάλογος.

• βήμα 4: Στην εξοχή Fuente, καθορίζει το εύρος που περιέχει τα στοιχεία που θα εμφανίζονται στην πρώτη αναπτυσσόμενη λίστα.

• βήμα 5: Κάντε κλικ δέχομαι. Αυτό θα δημιουργήσει το αναπτυσσόμενο μενού 1.

• βήμα 6: Επιλέξτε ολόκληρο το σύνολο δεδομένων (A1:B6 σε αυτό το παράδειγμα).

• βήμα 7: Παω σε Τύποι -> Ορισμένα ονόματα -> Δημιουργία από επιλογή (ή μπορείτε να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου Control + Shift + F3).

• βήμα 8: Στο πλαίσιο διαλόγου «Δημιουργία ονόματος από την επιλογή', τσεκάρετε την επιλογή Επάνω σειρά και καταργήστε την επιλογή όλων των άλλων. Με αυτόν τον τρόπο δημιουργούνται 2 περιοχές ονομάτων («Φρούτα» και «Λαχανικά»). Η σειρά Named Fruit αναφέρεται σε όλα τα φρούτα της λίστας και η σειρά Named Vegetables αναφέρεται σε όλα τα λαχανικά της λίστας.

• βήμα 9: Κάντε κλικ δέχομαι.
• βήμα 10: Επιλέξτε το κελί στο οποίο θέλετε την αναπτυσσόμενη λίστα Εξαρτημένη/Υπό όρους (Ε3 σε αυτό το παράδειγμα).
• βήμα 11: Παω σε Δεδομένα -> Επικύρωση δεδομένων.

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

• βήμα 13: Στις Πεδίο πηγής, εισάγετε τον τύπο = ΕΜΜΕΣΗ (D3). Εδώ, το D3 είναι το κελί που περιέχει το κύριο αναπτυσσόμενο μενού.

• βήμα 14: Κάντε κλικ δέχομαι.

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

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

Πως λειτουργεί αυτό? – Η αναπτυσσόμενη λίστα στο Excel υπό όρους (στο κελί E3) αναφέρεται στο =INDIRECT(D3). Αυτό σημαίνει ότι όταν επιλέγετε 'Φρούτα' στο κελί D3, η αναπτυσσόμενη λίστα στο E3 αναφέρεται στην περιοχή που ονομάστηκε 'Καρπός' (μέσω του ΕΜΜΕΣΗ λειτουργία) και επομένως παραθέτει όλα τα στοιχεία αυτής της κατηγορίας.

• Σημαντική σημείωση:εάν η γονική κατηγορία είναι περισσότερες από μία λέξεις (π.χ. «Φρούτα εποχής"μάλλον"Φρούτα'), τότε πρέπει να χρησιμοποιήσετε το τύπος = ΕΜΜΕΣΗ (ΥΠΟΚΑΤΑΣΤΑΣΗ (D3,””,”_”)), αντί για την απλή συνάρτηση INDIRECT που φαίνεται παραπάνω.

Ο λόγος για αυτό είναι ότι το Excel δεν επιτρέπει κενά σε ονομασμένα εύρη. Έτσι, όταν δημιουργείτε ένα εύρος με όνομα χρησιμοποιώντας περισσότερες από μία λέξεις, το Excel εισάγει αυτόματα μια υπογράμμιση μεταξύ των λέξεων.

Π.χ.: όταν δημιουργείτε ένα εύρος με όνομα με «Φρούτα εποχής», Θα κληθεί Εποχή_Φρούτα σε backend. Η χρήση του Λειτουργία REPLACE στο πλαίσιο του ΕΜΜΕΣΗ λειτουργία διασφαλίζει ότι τα κενά γίνονται υπογράμμιση.

Επαναφορά/διαγραφή του εξαρτώμενου περιεχομένου αναπτυσσόμενης λίστας αυτόματα

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

• Π.χ.: Εάν επιλέξετε 'Καρπός' Κάντε like στην κατηγορία και μετά επιλέξτε Apple ως στοιχείο και, στη συνέχεια, επιστρέψτε και αλλάξτε την κατηγορία σε 'Λαχανικά', το εξαρτημένο αναπτυσσόμενο μενού θα συνεχίσει να εμφανίζεται Apple ως το στοιχείο.

Μπορείτε να χρησιμοποιήσετε το VBA για να διασφαλίσετε ότι τα περιεχόμενα της εξαρτημένης αναπτυσσόμενης λίστας επαναφέρονται κάθε φορά που αλλάζει η γονική αναπτυσσόμενη λίστα. Ακολουθεί ο κώδικας VBA για την εκκαθάριση των περιεχομένων μιας εξαρτημένης αναπτυσσόμενης λίστας:

Ιδιωτικό δευτερεύον φύλλο εργασίας_Αλλαγή (ByVal Στόχος ως εύρος)

Σε περίπτωση σφάλματος, συνεχίστε το επόμενο

Αν Target.Column = 4 Τότε

Αν Target.Validation.Type = 3 Τότε

Application.EnableEvents = False

Target.Offset(0, 1).ClearContents

Θα τελειώσει αν

Θα τελειώσει αν

exitHandler:

Application.EnableEvents = True

Έξοδος Sub

Sub End

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

Δείτε πώς μπορείτε να κάνετε αυτόν τον κώδικα να λειτουργεί:

• βήμα 1: Αντιγράψτε τον κωδικό VBA.
• βήμα 2: Στο βιβλίο εργασίας του Excel όπου έχετε την εξαρτημένη αναπτυσσόμενη λίστα, μεταβείτε στο Καρτέλα προγραμματιστή, και εντός της ομάδας 'Κώδικας'Κάντε κλικ Visual Basic (μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου – ALT + F11).

• βήμα 3: Στο παράθυρο του προγράμματος επεξεργασίας VB, στα αριστερά στον εξερευνητή έργου, θα δείτε όλα τα ονόματα των φύλλων εργασίας. Κάντε διπλό κλικ σε αυτό με την αναπτυσσόμενη λίστα.

• βήμα 4: Επικολλήστε τον κωδικό στο παράθυρο κώδικα στα δεξιά.

• βήμα 5: Κλείστε το πρόγραμμα επεξεργασίας VB.

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

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

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

• βήμα 1: Επιλέξτε το κελί που έχει τις εξαρτημένες αναπτυσσόμενες λίστες.
• βήμα 2: Παω σε Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Νέος κανόνας.

• βήμα 3: Στο πλαίσιο διαλόγου Νέος κανόνας μορφή, ΕπιλέξτεΧρησιμοποιήστε έναν τύπο για να προσδιορίσετε ποια κελιά μορφή'.

• βήμα 4: Στο πεδίο τύπου, εισαγάγετε τον ακόλουθο τύπο:=ESERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)), 1,0))

• βήμα 5: Ορίστε τη μορφή.
• βήμα 6: Κάντε κλικ στο OK.

Μπορεί επίσης να σας ενδιαφέρει να μάθετε για: Πώς να ομαδοποιήσετε έναν Συγκεντρωτικό Πίνακα ανά μήνες στο Excel

Ο τύπος χρησιμοποιεί το Λειτουργία VLOOKUP για να ελέγξετε αν το εξαρτημένο στοιχείο της αναπτυσσόμενης λίστας είναι αυτό από τη γονική κατηγορία ή όχι. Εάν όχι, ο τύπος επιστρέφει ένα σφάλμα. Αυτό χρησιμοποιείται από το Λειτουργία ESERROR να επιστρέψει ΠΡΑΓΜΑΤΙΚΟΣ που λέει τη μορφοποίηση υπό όρους για την επισήμανση του κελιού.

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