Δημιουργία εξαρτημένης αναπτυσσόμενης λίστας στο Excel (Οδηγός βήμα προς βήμα)

Παρακολουθήστε βίντεο - Δημιουργία εξαρτημένης αναπτυσσόμενης λίστας στο Excel

Μια αναπτυσσόμενη λίστα Excel είναι μια χρήσιμη δυνατότητα όταν δημιουργείτε φόρμες εισαγωγής δεδομένων ή Πίνακες ελέγχου του Excel.

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

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

Στο παραπάνω παράδειγμα, έχω χρησιμοποιήσει τα στοιχεία στο A2: A6 για να δημιουργήσω ένα αναπτυσσόμενο μενού στο C3.

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

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

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

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

Μπορείτε να δείτε ότι οι επιλογές στο Drop Down 2 εξαρτώνται από την επιλογή που έγινε στο Drop Down 1. Εάν επιλέξω "Fruits" στο Drop Down 1, μου εμφανίζονται τα ονόματα των φρούτων, αλλά αν επιλέξω Λαχανικά στο Drop Down 1, τότε am εμφανίζονται τα ονόματα λαχανικών στο Drop Down 2.

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

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

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

• Επιλέξτε το κελί όπου θέλετε την πρώτη (κύρια) αναπτυσσόμενη λίστα.
• Μεταβείτε στα Δεδομένα -> Επικύρωση δεδομένων. Αυτό θα ανοίξει το παράθυρο διαλόγου επικύρωση δεδομένων.
• Στο παράθυρο διαλόγου επικύρωση δεδομένων, στην καρτέλα ρυθμίσεις, επιλέξτε Λίστα.
• Στο πεδίο Πηγή, καθορίστε το εύρος που περιέχει τα στοιχεία που πρόκειται να εμφανιστούν στην πρώτη αναπτυσσόμενη λίστα.
• Κάντε κλικ στο OK. Αυτό θα δημιουργήσει το Drop Down 1.
• Επιλέξτε ολόκληρο το σύνολο δεδομένων (A1: B6 σε αυτό το παράδειγμα).
• Μεταβείτε στους Τύπους -> Ορισμένα ονόματα -> Δημιουργία από επιλογή (ή μπορείτε να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου Control + Shift + F3).
• Στο παράθυρο διαλόγου «Δημιουργία ονόματος από επιλογή», ​​ελέγξτε την επιλογή Κορυφαία σειρά και καταργήστε την επιλογή όλων των άλλων. Με αυτόν τον τρόπο δημιουργούνται 2 σειρές ονομάτων («Φρούτα» και «Λαχανικά»). Η σειρά με φρούτα αναφέρεται σε όλα τα φρούτα της λίστας και η σειρά με λαχανικά αναφέρεται σε όλα τα λαχανικά της λίστας.
• Κάντε κλικ στο OK.
• Επιλέξτε το κελί όπου θέλετε τη λίστα Αναπτυσσόμενη/υπό όρους αναστολή (E3 σε αυτό το παράδειγμα).
• Μεταβείτε στα Δεδομένα -> Επικύρωση δεδομένων.
• Στο παράθυρο διαλόγου Επικύρωση δεδομένων, στην καρτέλα ρυθμίσεων, βεβαιωθείτε ότι η λίστα είναι επιλεγμένη.
• Στο πεδίο Πηγή, πληκτρολογήστε τον τύπο = INDIRECT (D3). Εδώ, το D3 είναι το κελί που περιέχει το κύριο αναπτυσσόμενο μενού.
• Κάντε κλικ στο OK.

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

Κατεβάστε το Παράδειγμα αρχείου

Πως λειτουργεί αυτό? - Η αναπτυσσόμενη λίστα υπό όρους (στο κελί E3) αναφέρεται σε = INDIRECT (D3). Αυτό σημαίνει ότι όταν επιλέγετε «Φρούτα» στο κελί D3, η αναπτυσσόμενη λίστα στο Ε3 αναφέρεται στην ονομαζόμενη περιοχή «Φρούτα» (μέσω της συνάρτησης INDIRECT) και ως εκ τούτου παραθέτει όλα τα στοιχεία σε αυτήν την κατηγορία.

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

• Ο λόγος για αυτό είναι ότι το Excel δεν επιτρέπει διαστήματα σε εύρος ονομάτων. Έτσι, όταν δημιουργείτε ένα όνομα με περισσότερες από μία λέξεις, το Excel εισάγει αυτόματα μια υπογράμμιση μεταξύ των λέξεων. Για παράδειγμα, όταν δημιουργείτε μια ονομαστική σειρά με ‘Seasonal Fruits’, θα ονομαστεί Season_Fruits στο backend. Η χρήση της συνάρτησης SUBSTITUTE μέσα στη συνάρτηση INDIRECT διασφαλίζει ότι τα διαστήματα είναι μετατρέπεται σε υπογράμμιση.

Επαναφορά/Εκκαθάριση περιεχομένου της εξαρτημένης αναπτυσσόμενης λίστας αυτόματα

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

Για παράδειγμα, εάν επιλέξετε τα «Φρούτα» ως κατηγορία και, στη συνέχεια, επιλέξτε την Apple ως αντικείμενο και, στη συνέχεια, επιστρέψετε και αλλάξετε την κατηγορία σε «Λαχανικά», το αναπτυσσόμενο μενού θα εξαρτάται από την εμφάνιση της Apple ως αντικειμένου.

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

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

Private Sub Worksheet_Change (ByVal Target As Range) On Error Resume Next If Target.Column = 4 Στη συνέχεια If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub

Η πίστωση για αυτόν τον κωδικό πηγαίνει σε αυτό το σεμινάριο από την Debra για την εκκαθάριση εξαρτημένων αναπτυσσόμενων λιστών στο Excel όταν αλλάζει η επιλογή.

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

• Αντιγράψτε τον κωδικό VBA.
• Στο βιβλίο εργασίας του Excel όπου έχετε την εξαρτώμενη αναπτυσσόμενη λίστα, μεταβείτε στην καρτέλα Προγραμματιστής και στην ομάδα «Κώδικας», κάντε κλικ στο Visual Basic (μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου - ALT + F11).
• Στο παράθυρο επεξεργαστή VB, στα αριστερά στην εξερεύνηση έργου, θα δείτε όλα τα ονόματα του φύλλου εργασίας. Κάντε διπλό κλικ σε αυτό που έχει την αναπτυσσόμενη λίστα.
• Επικολλήστε τον κώδικα στο παράθυρο κωδικών στα δεξιά.
• Κλείστε το πρόγραμμα επεξεργασίας VB.

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

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

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

• Επιλέξτε το κελί που έχει την εξαρτώμενη αναπτυσσόμενη λίστα (ες).
• Μεταβείτε στην Αρχική σελίδα -> Μορφοποίηση υπό όρους -> Νέος κανόνας.
• Στο παράθυρο διαλόγου Νέος κανόνας μορφοποίησης, επιλέξτε «Χρήση ενός τύπου για να καθορίσετε ποια κελιά θα μορφοποιήσετε».
• Στο πεδίο τύπου, εισαγάγετε τον ακόλουθο τύπο: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Ορίστε τη μορφή.
• Κάντε κλικ στο OK.

Ο τύπος χρησιμοποιεί τη συνάρτηση VLOOKUP για να ελέγξει εάν το στοιχείο στην εξαρτώμενη αναπτυσσόμενη λίστα είναι αυτό της κύριας κατηγορίας ή όχι. Εάν δεν είναι, ο τύπος επιστρέφει σφάλμα. Αυτό χρησιμοποιείται από τη συνάρτηση ISERROR για να επιστρέψει TRUE, η οποία υποδεικνύει τη μορφοποίηση υπό όρους για να επισημάνει το κελί.

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

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