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

Η αναπτυσσόμενη λίστα είναι ένα απίστευτα χρήσιμο εργαλείο που μπορεί να σας βοηθήσει να κάνετε την εργασία με πληροφορίες πιο άνετη. Καθιστά δυνατό να περιέχει πολλές τιμές σε ένα κελί ταυτόχρονα, με τις οποίες μπορείτε να εργαστείτε, όπως και με οποιαδήποτε άλλα. Για να επιλέξετε αυτό που χρειάζεστε, απλώς κάντε κλικ στο εικονίδιο με το βέλος, μετά από το οποίο εμφανίζεται μια λίστα τιμών uXNUMXbuXNUMXbis. Αφού επιλέξετε ένα συγκεκριμένο, το κελί συμπληρώνεται αυτόματα με αυτό και οι τύποι υπολογίζονται εκ νέου με βάση αυτό.

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

Διαδικασία δημιουργίας λίστας

Για να δημιουργήσετε ένα αναδυόμενο μενού, κάντε κλικ στα στοιχεία μενού κατά μήκος της διαδρομής «Δεδομένα» – «Επικύρωση δεδομένων». Θα ανοίξει ένα παράθυρο διαλόγου όπου πρέπει να βρείτε την καρτέλα "Παράμετροι" και να κάνετε κλικ σε αυτήν εάν δεν έχει ανοιχτεί στο παρελθόν. Έχει πολλές ρυθμίσεις, αλλά το στοιχείο "Τύπος δεδομένων" είναι σημαντικό για εμάς. Από όλες τις έννοιες, το «Λίστα» είναι το σωστό.

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

1. Ανεξάρτητη ένδειξη στοιχείων λίστας που χωρίζονται με ερωτηματικό στο πεδίο «Πηγή» που βρίσκεται στην ίδια καρτέλα του ίδιου πλαισίου διαλόγου.
   

   

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

   

3. Καθορισμός ενός ονομασμένου εύρους. Μια μέθοδος που επαναλαμβάνει την προηγούμενη, αλλά είναι απαραίτητο μόνο να ονομάσετε προκαταρκτικά το εύρος.
   

   

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

Με βάση τα δεδομένα από τη λίστα

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

Για να δημιουργήσετε μια λίστα σε ένα αναπτυσσόμενο μενού με βάση αυτό το σύνολο πληροφοριών, πρέπει να κάνετε τα εξής:

1. Επιλέξτε το κελί που έχει δεσμευτεί για τη μελλοντική λίστα.
2. Βρείτε την καρτέλα Δεδομένα στην κορδέλα. Εκεί κάνουμε κλικ στο "Επαλήθευση δεδομένων".
   

   

3. Βρείτε το στοιχείο "Τύπος δεδομένων" και αλλάξτε την τιμή σε "Λίστα".
   

   

4. Στο πεδίο που υποδηλώνει την επιλογή «Πηγή», εισάγετε το επιθυμητό εύρος. Λάβετε υπόψη ότι πρέπει να προσδιορίζονται απόλυτες αναφορές, ώστε κατά την αντιγραφή της λίστας, οι πληροφορίες να μην μετατοπίζονται.
   

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

Με χειροκίνητη καταγραφή δεδομένων

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

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

1. Κάντε κλικ στο κελί για τη λίστα.
2. Ανοίξτε το "Δεδομένα" και εκεί βρείτε την ενότητα "Έλεγχος δεδομένων" που είναι γνωστή σε εμάς.
   

   

3. Και πάλι, επιλέξτε τον τύπο "Λίστα".
   

   

4. Εδώ πρέπει να εισαγάγετε «Ναι. Όχι» ως πηγή. Βλέπουμε ότι οι πληροφορίες εισάγονται χειροκίνητα χρησιμοποιώντας ένα ερωτηματικό για την απαρίθμηση.

Αφού κάνουμε κλικ στο OK, έχουμε το εξής αποτέλεσμα.

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

Δημιουργία αναπτυσσόμενης λίστας χρησιμοποιώντας τη λειτουργία OFFSET

Εκτός από την κλασική μέθοδο, είναι δυνατή η χρήση της συνάρτησης ΔΙΑΘΕΣΗγια τη δημιουργία αναπτυσσόμενων μενού.

Ας ανοίξουμε το φύλλο.

Για να χρησιμοποιήσετε τη συνάρτηση για την αναπτυσσόμενη λίστα, πρέπει να κάνετε τα εξής:

1. Επιλέξτε το κελί ενδιαφέροντος όπου θέλετε να τοποθετήσετε τη μελλοντική λίστα.
2. Ανοίξτε την καρτέλα "Δεδομένα" και το παράθυρο "Επικύρωση δεδομένων" με τη σειρά.
   

   

3. Ορίστε "Λίστα". Αυτό γίνεται με τον ίδιο τρόπο όπως τα προηγούμενα παραδείγματα. Τέλος, χρησιμοποιείται ο ακόλουθος τύπος: =OFFSET(A$2$;0;0;5). Το εισάγουμε όπου καθορίζονται τα κελιά που θα χρησιμοποιηθούν ως όρισμα.

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

Η σύνταξη για αυτό είναι:

=OFFSET(αναφορά, μετατόπιση_γραμμής, μετατόπιση_στήλης, [ύψος], [πλάτος])

Βλέπουμε ότι αυτή η συνάρτηση έχει 5 ορίσματα. Αρχικά, δίνεται η πρώτη διεύθυνση κελιού που πρέπει να μετατοπιστεί. Τα επόμενα δύο ορίσματα καθορίζουν πόσες γραμμές και στήλες θα μετατοπιστούν. Μιλώντας για εμάς, το όρισμα Height είναι 5 γιατί αντιπροσωπεύει το ύψος της λίστας. 

Αναπτυσσόμενη λίστα στο Excel με αντικατάσταση δεδομένων (+ χρησιμοποιώντας τη συνάρτηση OFFSET)

Στη δεδομένη περίπτωση ΔΙΑΘΕΣΗ επιτρέπεται η δημιουργία ενός αναδυόμενου μενού που βρίσκεται σε μια σταθερή περιοχή. Το μειονέκτημα αυτής της μεθόδου είναι ότι μετά την προσθήκη του στοιχείου, θα πρέπει να επεξεργαστείτε τον τύπο μόνοι σας.

Για να δημιουργήσετε μια δυναμική λίστα με υποστήριξη για την εισαγωγή νέων πληροφοριών, πρέπει:

1. Επιλέξτε το κελί που σας ενδιαφέρει.
2. Αναπτύξτε την καρτέλα "Δεδομένα" και κάντε κλικ στο "Επικύρωση δεδομένων".
3. Στο παράθυρο που ανοίγει, επιλέξτε ξανά το στοιχείο "Λίστα" και καθορίστε τον ακόλουθο τύπο ως πηγή δεδομένων: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Κάντε κλικ στο OK.

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

Προκειμένου ο τύπος να λειτουργεί κανονικά, είναι απαραίτητο να ανιχνευθεί εάν υπάρχουν κενά κελιά στη διαδρομή του τύπου. Δεν θα έπρεπε να είναι.

Αναπτυσσόμενη λίστα με δεδομένα από άλλο φύλλο ή αρχείο Excel

Η κλασική μέθοδος δεν λειτουργεί εάν χρειάζεται να λάβετε πληροφορίες από άλλο έγγραφο ή ακόμα και ένα φύλλο που περιέχεται στο ίδιο αρχείο. Για αυτό, χρησιμοποιείται η συνάρτηση ΕΜΜΕΣΟΣ, που σας επιτρέπει να εισάγετε στη σωστή μορφή έναν σύνδεσμο προς ένα κελί που βρίσκεται σε άλλο φύλλο ή γενικά - ένα αρχείο. Πρέπει να κάνετε τα εξής:

1. Ενεργοποιήστε το κελί όπου τοποθετούμε τη λίστα.
2. Ανοίγουμε το παράθυρο που ήδη ξέρουμε. Στο ίδιο μέρος όπου υποδείξαμε προηγουμένως πηγές για άλλες περιοχές, υποδεικνύεται ένας τύπος στη μορφή =INDIRECT("[List1.xlsx]Φύλλο1!$A$1:$A$9"). Φυσικά, αντί για List1 και Sheet1, μπορείτε να εισαγάγετε τα ονόματα των βιβλίων και των φύλλων σας, αντίστοιχα. 

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

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

Δημιουργία εξαρτημένων αναπτυσσόμενων μενού

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

Πρέπει να ακολουθήσετε αυτά τα βήματα για να δημιουργήσετε λίστες των οποίων το αποτέλεσμα επηρεάζεται από την επιλογή που έχει επιλεγεί σε άλλη λίστα.

1. Δημιουργήστε την 1η λίστα με ονόματα περιοχών.
   

   

2. Στο σημείο εισόδου της πηγής, οι απαιτούμενες ενδείξεις επισημαίνονται μία προς μία.
   

   

3. Δημιουργήστε μια 2η λίστα ανάλογα με το είδος του φυτού που έχει επιλέξει το άτομο. Εναλλακτικά, εάν καθορίσετε δέντρα στην πρώτη λίστα, τότε οι πληροφορίες στη δεύτερη λίστα θα είναι «βελανιδιά, γαύρος, καστανιά» και όχι μόνο. Είναι απαραίτητο να γράψετε τον τύπο στη θέση εισαγωγής της πηγής δεδομένων =ΕΜΜΕΣΗ(Ε3). E3 – κελί που περιέχει το όνομα της περιοχής 1.=INDIRECT(E3). E3 – κελί με το όνομα της λίστας 1.

Τώρα όλα είναι έτοιμα.

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

Μερικές φορές δεν είναι δυνατό να δοθεί προτίμηση σε μία μόνο τιμή, επομένως πρέπει να επιλεγούν περισσότερες από μία. Στη συνέχεια, πρέπει να προσθέσετε μια μακροεντολή στον κώδικα της σελίδας. Χρησιμοποιώντας το συνδυασμό πλήκτρων Alt + F11 ανοίγει ο επεξεργαστής Visual Basic. Και ο κωδικός μπαίνει εκεί.

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

    On Error Συνέχιση Επόμενη

    If Not Intersect(Target, Range(«Е2:Е9»)) Is Nothing And Target.Cells.Count = 1 Τότε

        Application.EnableEvents = False

        Αν Len (Target.Offset (0, 1)) = 0 Τότε

            Target.Offset (0, 1) = Στόχος

        αλλιώς

            Target.End (xlToRight) .Offset (0, 1) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

Sub End 

Για να εμφανίζονται τα περιεχόμενα των κελιών παρακάτω, εισάγουμε τον παρακάτω κώδικα στον επεξεργαστή.

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

    On Error Συνέχιση Επόμενη

    If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Τότε

        Application.EnableEvents = False

        Αν Len (Target.Offset (1, 0)) = 0 Τότε

            Target.Offset (1, 0) = Στόχος

        αλλιώς

            Target.End (xlDown) .Offset (1, 0) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

Sub End

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

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

    On Error Συνέχιση Επόμενη

    If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Τότε

        Application.EnableEvents = False

        newVal = Στόχος

        Εφαρμογή.Αναίρεση

        oldval = Στόχος

        Αν Len (oldval) <> 0 Και oldval <> newVal Τότε

            Target = Target & «,» & newVal

        αλλιώς

            Στόχος = newVal

        End If

        Αν Len (newVal) = 0 Τότε Target.ClearContents

        Application.EnableEvents = True

    End If

Sub End

Τα εύρη είναι επεξεργάσιμα.

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

Σε αυτήν την περίπτωση, πρέπει αρχικά να χρησιμοποιήσετε διαφορετικό τύπο λίστας. Ανοίγει η καρτέλα "Προγραμματιστής", μετά την οποία πρέπει να κάνετε κλικ ή να πατήσετε (αν η οθόνη είναι αφής) στο στοιχείο "Εισαγωγή" - "ActiveX". Διαθέτει σύνθετο κουτί. Θα σας ζητηθεί να σχεδιάσετε αυτήν τη λίστα, μετά την οποία θα προστεθεί στο έγγραφο.

Επιπλέον, διαμορφώνεται μέσω ιδιοτήτων, όπου καθορίζεται μια περιοχή στην επιλογή ListFillRange. Το κελί όπου εμφανίζεται η τιμή που ορίζεται από το χρήστη διαμορφώνεται χρησιμοποιώντας την επιλογή LinkedCell. Στη συνέχεια, πρέπει απλώς να γράψετε τους πρώτους χαρακτήρες, καθώς το πρόγραμμα θα προτείνει αυτόματα πιθανές τιμές.

Αναπτυσσόμενη λίστα με αυτόματη αντικατάσταση δεδομένων

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

1. Δημιουργήστε ένα σύνολο κελιών για τη μελλοντική λίστα. Στην περίπτωσή μας, αυτό είναι ένα σύνολο χρωμάτων. Το επιλέγουμε.
   

   

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

   

   

Στη συνέχεια, πρέπει να επιβεβαιώσετε αυτό το εύρος πατώντας το κουμπί "OK".

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

Αυτό είναι όλο, υπάρχει ένας πίνακας και μπορεί να χρησιμοποιηθεί ως βάση για μια αναπτυσσόμενη λίστα, για την οποία χρειάζεστε:

1. Επιλέξτε το κελί όπου βρίσκεται η λίστα.
2. Ανοίξτε το παράθυρο διαλόγου Επικύρωση δεδομένων.
   

   

3. Ορίζουμε τον τύπο δεδομένων σε "List" και ως τιμές δίνουμε το όνομα του πίνακα μέσω του σύμβολου =.
   

   

   

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

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

Πώς να αντιγράψετε την αναπτυσσόμενη λίστα;

Για την αντιγραφή, αρκεί να χρησιμοποιήσετε τον συνδυασμό πλήκτρων Ctrl + C και Ctrl + V. Έτσι, η αναπτυσσόμενη λίστα θα αντιγραφεί μαζί με τη μορφοποίηση. Για να καταργήσετε τη μορφοποίηση, πρέπει να χρησιμοποιήσετε μια ειδική επικόλληση (στο μενού περιβάλλοντος, αυτή η επιλογή εμφανίζεται μετά την αντιγραφή της λίστας), όπου έχει οριστεί η επιλογή "συνθήκες στις τιμές".

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

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

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