Πώς να δημιουργήσετε μια αναπτυσσόμενη λίστα στο Excel (ο μόνος οδηγός που χρειάζεστε)

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

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

Οι αναπτυσσόμενες λίστες είναι αρκετά συχνές σε ιστότοπους/εφαρμογές και είναι πολύ διαισθητικές για τον χρήστη.

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

Σε αυτό το σεμινάριο, θα μάθετε πώς να δημιουργείτε μια αναπτυσσόμενη λίστα στο Excel (χρειάζονται μόνο λίγα δευτερόλεπτα για να το κάνετε αυτό) μαζί με όλα τα φοβερά πράγματα που μπορείτε να κάνετε με αυτό.

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

Σε αυτήν την ενότητα, θα μάθετε τα ακριβή βήματα για να δημιουργήσετε μια αναπτυσσόμενη λίστα Excel:

1. Χρήση δεδομένων από κελιά.
2. Χειροκίνητη εισαγωγή δεδομένων.
3. Χρησιμοποιώντας τον τύπο OFFSET.

#1 Χρήση δεδομένων από κελιά

Ας υποθέσουμε ότι έχετε μια λίστα με στοιχεία όπως φαίνεται παρακάτω:

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

1. Επιλέξτε ένα κελί όπου θέλετε να δημιουργήσετε την αναπτυσσόμενη λίστα.
2. Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
3. Στο πλαίσιο διαλόγου Επικύρωση δεδομένων, στην καρτέλα Ρυθμίσεις, επιλέξτε Λίστα ως κριτήρια επικύρωσης.
   • Μόλις επιλέξετε Λίστα, εμφανίζεται το πεδίο προέλευσης.
4. Στο πεδίο προέλευσης, πληκτρολογήστε = $ A $ 2: $ A $ 6 ή απλώς κάντε κλικ στο πεδίο Πηγή και επιλέξτε τα κελιά χρησιμοποιώντας το ποντίκι και κάντε κλικ στο OK. Αυτό θα εισαγάγει μια αναπτυσσόμενη λίστα στο κελί C2.
   • Βεβαιωθείτε ότι είναι επιλεγμένη η αναπτυσσόμενη επιλογή In-cell (η οποία είναι προεπιλεγμένη). Εάν αυτή η επιλογή είναι μη επιλεγμένη, το κελί δεν εμφανίζει αναπτυσσόμενο μενού, ωστόσο, μπορείτε να εισαγάγετε μη αυτόματα τις τιμές στη λίστα.

Σημείωση: Εάν θέλετε να δημιουργήσετε αναπτυσσόμενες λίστες σε πολλά κελιά ταυτόχρονα, επιλέξτε όλα τα κελιά όπου θέλετε να το δημιουργήσετε και, στη συνέχεια, ακολουθήστε τα παραπάνω βήματα. Βεβαιωθείτε ότι οι αναφορές κελιών είναι απόλυτες (όπως $ A $ 2) και όχι σχετικές (όπως A2 ή A $ 2 ή $ A2).

#2 Με μη αυτόματη εισαγωγή δεδομένων

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

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

• Επιλέξτε ένα κελί όπου θέλετε να δημιουργήσετε την αναπτυσσόμενη λίστα (κελί C2 σε αυτό το παράδειγμα).
• Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
• Στο πλαίσιο διαλόγου Επικύρωση δεδομένων, στην καρτέλα Ρυθμίσεις, επιλέξτε Λίστα ως κριτήρια επικύρωσης.
  • Μόλις επιλέξετε Λίστα, εμφανίζεται το πεδίο προέλευσης.
• Στο πεδίο προέλευσης, εισαγάγετε Ναι, Όχι
  • Βεβαιωθείτε ότι είναι επιλεγμένη η αναπτυσσόμενη επιλογή In-cell.
• Κάντε κλικ στο OK.

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

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

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

#3 Χρήση τύπων Excel

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

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

Για παράδειγμα, ας υποθέσουμε ότι έχετε το σύνολο δεδομένων όπως φαίνεται παρακάτω:

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

• Επιλέξτε ένα κελί όπου θέλετε να δημιουργήσετε την αναπτυσσόμενη λίστα (κελί C2 σε αυτό το παράδειγμα).
• Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
• Στο πλαίσιο διαλόγου Επικύρωση δεδομένων, στην καρτέλα Ρυθμίσεις, επιλέξτε Λίστα ως κριτήρια επικύρωσης.
  • Μόλις επιλέξετε Λίστα, εμφανίζεται το πεδίο προέλευσης.
• Στο πεδίο Πηγή, πληκτρολογήστε τον ακόλουθο τύπο: = OFFSET ($ A $ 2,0,0,5)
  • Βεβαιωθείτε ότι είναι επιλεγμένη η αναπτυσσόμενη επιλογή In-cell.
• Κάντε κλικ στο OK.

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

Σημείωση: Εάν θέλετε να δημιουργήσετε μια αναπτυσσόμενη λίστα σε πολλά κελιά ταυτόχρονα, επιλέξτε όλα τα κελιά όπου θέλετε να τη δημιουργήσετε και, στη συνέχεια, ακολουθήστε τα παραπάνω βήματα. Βεβαιωθείτε ότι οι αναφορές κελιών είναι απόλυτες (όπως $ A $ 2) και όχι σχετικές (όπως A2 ή A $ 2 ή $ A2).

Πώς λειτουργεί αυτός ο τύπος ;;

Στην παραπάνω περίπτωση, χρησιμοποιήσαμε μια λειτουργία OFFSET για να δημιουργήσουμε την αναπτυσσόμενη λίστα. Επιστρέφει μια λίστα στοιχείων από το ra

Επιστρέφει μια λίστα στοιχείων από το εύρος A2: A6.

Εδώ είναι η σύνταξη της συνάρτησης OFFSET: = OFFSET (αναφορά, γραμμές, στήλες, [ύψος], [πλάτος])

Χρειάζονται πέντε ορίσματα, όπου ορίσαμε την αναφορά ως A2 (το σημείο εκκίνησης της λίστας). Οι γραμμές/στήλες ορίζονται ως 0, καθώς δεν θέλουμε να αντισταθμίσουμε το κελί αναφοράς. Το ύψος ορίζεται ως 5, καθώς υπάρχουν πέντε στοιχεία στη λίστα.

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

Δημιουργία δυναμικής αναπτυσσόμενης λίστας στο Excel (Χρήση OFFSET)

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

Εδώ είναι ένας τρόπος για να το κάνετε δυναμικό (και δεν είναι παρά μια μικρή τροποποίηση στον τύπο):

• Επιλέξτε ένα κελί όπου θέλετε να δημιουργήσετε την αναπτυσσόμενη λίστα (κελί C2 σε αυτό το παράδειγμα).
• Μεταβείτε στα Δεδομένα -> Εργαλεία δεδομένων -> Επικύρωση δεδομένων.
• Στο πλαίσιο διαλόγου Επικύρωση δεδομένων, στην καρτέλα Ρυθμίσεις, επιλέξτε Λίστα ως κριτήρια επικύρωσης. Μόλις επιλέξετε Λίστα, εμφανίζεται το πεδίο προέλευσης.
• Στο πεδίο προέλευσης, εισαγάγετε τον ακόλουθο τύπο: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Βεβαιωθείτε ότι είναι επιλεγμένη η αναπτυσσόμενη επιλογή In-cell.
• Κάντε κλικ στο OK.

Σε αυτόν τον τύπο, αντικατέστησα το όρισμα 5 με COUNTIF ($ A $ 2: $ A $ 100, ””).

Η συνάρτηση COUNTIF μετρά τα μη κενά κελιά στο εύρος A2: A100. Επομένως, η συνάρτηση OFFSET προσαρμόζεται ώστε να περιλαμβάνει όλα τα μη κενά κελιά.

Σημείωση:

• Για να λειτουργήσει αυτό, ΔΕΝ πρέπει να υπάρχουν κενά κελιά μεταξύ των κελιών που έχουν γεμίσει.
• Εάν θέλετε να δημιουργήσετε μια αναπτυσσόμενη λίστα σε πολλά κελιά ταυτόχρονα, επιλέξτε όλα τα κελιά όπου θέλετε να τη δημιουργήσετε και, στη συνέχεια, ακολουθήστε τα παραπάνω βήματα. Βεβαιωθείτε ότι οι αναφορές κελιών είναι απόλυτες (όπως $ A $ 2) και όχι σχετικές (όπως A2 ή A $ 2 ή $ A2).

Αντιγραφή επικόλλησης αναπτυσσόμενων λιστών στο Excel

Μπορείτε να αντιγράψετε επικολλήστε τα κελιά με επικύρωση δεδομένων σε άλλα κελιά και θα αντιγράψει επίσης την επικύρωση δεδομένων.

Για παράδειγμα, εάν έχετε μια αναπτυσσόμενη λίστα στο κελί C2 και θέλετε να την εφαρμόσετε και στο C3: C6, απλώς αντιγράψτε το κελί C2 και επικολλήστε το στο C3: C6. Αυτό θα αντιγράψει την αναπτυσσόμενη λίστα και θα την κάνει διαθέσιμη στο C3: C6 (μαζί με την αναπτυσσόμενη λίστα, θα αντιγράψει και τη μορφοποίηση).

Εάν θέλετε να αντιγράψετε μόνο το αναπτυσσόμενο μενού και όχι τη μορφοποίηση, ακολουθούν τα βήματα:

• Αντιγράψτε το κελί που έχει το αναπτυσσόμενο μενού.
• Επιλέξτε τα κελιά στα οποία θέλετε να αντιγράψετε το αναπτυσσόμενο μενού.
• Μεταβείτε στην Αρχική σελίδα -> Επικόλληση -> Ειδική επικόλληση.
• Στο παράθυρο διαλόγου Επικόλληση ειδικού, επιλέξτε Επικύρωση στις επιλογές Επικόλληση.
• Κάντε κλικ στο OK.

Αυτό θα αντιγράψει μόνο το αναπτυσσόμενο μενού και όχι τη μορφοποίηση του αντιγραμμένου κελιού.

Προσοχή κατά την εργασία με την αναπτυσσόμενη λίστα Excel

Πρέπει να είστε προσεκτικοί όταν εργάζεστε με αναπτυσσόμενες λίστες στο Excel.

Όταν αντιγράφετε ένα κελί (που δεν περιέχει αναπτυσσόμενη λίστα) πάνω από ένα κελί που περιέχει μια αναπτυσσόμενη λίστα, η αναπτυσσόμενη λίστα χάνεται.

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

Πώς να επιλέξετε όλα τα κελιά που έχουν μια αναπτυσσόμενη λίστα

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

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

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

• Μεταβείτε στην Αρχική σελίδα -> Εύρεση & επιλογή -> Μετάβαση στα Ειδικά.
• Στο παράθυρο διαλόγου Μετάβαση στο ειδικό, επιλέξτε Επικύρωση δεδομένων
  • Η επικύρωση δεδομένων έχει δύο επιλογές: Όλα και ίδια. Όλα θα επιλέξουν όλα τα κελιά που εφαρμόζουν έναν κανόνα επικύρωσης δεδομένων. Το ίδιο θα επέλεγε μόνο εκείνα τα κελιά που έχουν τον ίδιο κανόνα επικύρωσης δεδομένων με αυτόν του ενεργού κελιού.
• Κάντε κλικ στο OK.

Αυτό θα επέλεγε αμέσως όλα τα κελιά που εφαρμόζουν έναν κανόνα επικύρωσης δεδομένων (αυτό περιλαμβάνει επίσης αναπτυσσόμενες λίστες).

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

Εδώ είναι μια άλλη τεχνική του Jon Acampora που μπορείτε να χρησιμοποιήσετε για να διατηρείτε πάντα ορατό το εικονίδιο με το βέλος προς τα κάτω. Μπορείτε επίσης να δείτε μερικούς τρόπους για να το κάνετε σε αυτό το βίντεο του κ. Excel.

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

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

Αν προτιμάτε να διαβάζετε παρά να παρακολουθείτε βίντεο, συνεχίστε να διαβάζετε.

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

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

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

Στο παραπάνω παράδειγμα, όταν τα στοιχεία που παρατίθενται στο ‘Drop Down 2’ εξαρτώνται από την επιλογή που γίνεται στο ‘Drop Down 1’.

Τώρα ας δούμε πώς να το δημιουργήσουμε.

Ακολουθούν τα βήματα για τη δημιουργία μιας αναπτυσσόμενης λίστας εξαρτημένων / υπό όρους στο 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) και ως εκ τούτου παραθέτει όλα τα στοιχεία σε αυτήν την κατηγορία.

Σημαντική σημείωση Κατά την εργασία με αναπτυσσόμενες λίστες υπό όρους στο Excel:

• Όταν κάνετε την επιλογή και, στη συνέχεια, αλλάξετε το αναπτυσσόμενο μενού γονέα, το εξαρτώμενο αναπτυσσόμενο μενού δεν θα αλλάξει και, ως εκ τούτου, θα ήταν μια λανθασμένη καταχώριση. Για παράδειγμα, εάν επιλέξετε τις ΗΠΑ ως χώρα και στη συνέχεια επιλέξτε τη Φλόριντα ως πολιτεία και, στη συνέχεια, επιστρέψετε και αλλάξετε τη χώρα σε Ινδία, η πολιτεία θα παραμείνει ως Φλόριντα. Ακολουθεί ένα εξαιρετικό σεμινάριο της Debra για την εκκαθάριση εξαρτημένων (υπό όρους) αναπτυσσόμενων λιστών στο Excel όταν αλλάζει η επιλογή.
• Εάν η κύρια κατηγορία είναι περισσότερες από μία λέξεις (για παράδειγμα, «Εποχιακά φρούτα» αντί για «Φρούτα»), τότε πρέπει να χρησιμοποιήσετε τον τύπο = ΕΜΠΟΡΙΚΟ (ΥΠΟΘΕΣΗ (D3, "", "_")), αντί για απλή ΕΜΜΕΣΗ συνάρτηση που φαίνεται παραπάνω. Ο λόγος για αυτό είναι ότι το Excel δεν επιτρέπει διαστήματα σε εύρος ονομάτων. Έτσι, όταν δημιουργείτε ένα όνομα με περισσότερες από μία λέξεις, το Excel εισάγει αυτόματα μια υπογράμμιση μεταξύ των λέξεων. Επομένως, η σειρά που ονομάζεται ‘Seasonal Fruits’ θα είναι ‘Seasonal_Fruits’. Η χρήση της συνάρτησης SUBSTITUTE μέσα στη συνάρτηση INDIRECT διασφαλίζει ότι τα διαστήματα είναι μετατρέπεται σε υπογράμμιση.