Cách tạo danh sách thả xuống trong Excel

Danh sách thả xuống là một công cụ vô cùng hữu ích có thể giúp làm việc với thông tin thoải mái hơn. Nó làm cho nó có thể chứa một số giá trị trong một ô cùng một lúc, mà bạn có thể làm việc với nó, giống như với bất kỳ giá trị nào khác. Để chọn cái bạn cần, chỉ cần nhấp vào biểu tượng mũi tên, sau đó danh sách các giá trị uXNUMXbuXNUMXbis hiển thị. Sau khi chọn một ô cụ thể, ô sẽ tự động được điền vào ô đó và các công thức được tính toán lại dựa trên ô đó.

Excel cung cấp nhiều phương pháp khác nhau để tạo menu thả xuống và ngoài ra, nó còn cho phép bạn tùy chỉnh chúng một cách linh hoạt. Hãy phân tích chi tiết hơn các phương pháp này.

Quá trình tạo danh sách

Để tạo menu bật lên, hãy nhấp vào các mục menu dọc theo đường dẫn “Dữ liệu” - “Xác thực dữ liệu”. Một hộp thoại sẽ mở ra nơi bạn cần tìm tab “Tham số” và nhấp vào nó nếu nó chưa được mở trước đó. Nó có rất nhiều cài đặt, nhưng mục "Loại dữ liệu" là quan trọng đối với chúng tôi. Trong tất cả các ý nghĩa, “Danh sách” là một ý nghĩa phù hợp.

Số lượng các phương pháp mà thông tin được nhập vào danh sách bật lên là khá lớn.

1. Chỉ báo độc lập của các phần tử danh sách được phân tách bằng dấu chấm phẩy trong trường “Nguồn” nằm trên cùng một tab của cùng một hộp thoại.
   

   

2. Chỉ thị sơ bộ của các giá trị. Trường Nguồn chứa phạm vi có sẵn thông tin bắt buộc.
   

   

3. Chỉ định một phạm vi được đặt tên. Phương thức lặp lại phương pháp trước đó, nhưng chỉ cần đặt tên sơ bộ cho phạm vi.
   

   

Bất kỳ phương pháp nào trong số này sẽ tạo ra kết quả mong muốn. Hãy xem xét các phương pháp tạo danh sách thả xuống trong các tình huống thực tế.

Dựa trên dữ liệu từ danh sách

Giả sử chúng ta có một bảng mô tả các loại trái cây khác nhau.

Để tạo danh sách trong menu thả xuống dựa trên tập hợp thông tin này, bạn cần thực hiện như sau:

1. Chọn ô dành riêng cho danh sách trong tương lai.
2. Tìm tab Dữ liệu trên ruy-băng. Ở đó, chúng tôi nhấp vào "Xác minh dữ liệu".
   

   

3. Tìm mục “Loại dữ liệu” và chuyển giá trị thành “Danh sách”.
   

   

4. Trong trường biểu thị tùy chọn "Nguồn", hãy nhập phạm vi mong muốn. Xin lưu ý rằng các tham chiếu tuyệt đối phải được chỉ định để khi sao chép danh sách, thông tin không thay đổi.
   

Ngoài ra, có một chức năng tạo danh sách cùng một lúc trong nhiều ô. Để đạt được điều này, bạn nên chọn tất cả chúng và thực hiện các bước tương tự như đã mô tả trước đó. Một lần nữa, bạn cần đảm bảo rằng các tham chiếu tuyệt đối được viết. Nếu địa chỉ không có ký hiệu đô la bên cạnh tên cột và hàng, thì bạn cần thêm chúng bằng cách nhấn phím F4 cho đến khi ký hiệu $ bên cạnh tên cột và hàng.

Với ghi dữ liệu thủ công

Trong tình huống trên, danh sách được viết bằng cách đánh dấu phạm vi bắt buộc. Đây là một phương pháp thuận tiện, nhưng đôi khi cần phải ghi lại dữ liệu theo cách thủ công. Điều này sẽ giúp bạn có thể tránh trùng lặp thông tin trong sổ làm việc.

Giả sử chúng ta phải đối mặt với nhiệm vụ tạo một danh sách có chứa hai lựa chọn có thể có: có và không. Để hoàn thành nhiệm vụ, cần:

1. Bấm vào ô cho danh sách.
2. Mở "Dữ liệu" và ở đó tìm thấy phần "Kiểm tra Dữ liệu" quen thuộc với chúng tôi.
   

   

3. Một lần nữa, hãy chọn loại "Danh sách".
   

   

4. Ở đây bạn cần nhập “Có; Không ”như nguồn. Chúng tôi thấy rằng thông tin được nhập theo cách thủ công bằng dấu chấm phẩy để liệt kê.

Sau khi nhấn OK, chúng ta có kết quả như sau.

Tiếp theo, chương trình sẽ tự động tạo menu thả xuống trong ô thích hợp. Tất cả thông tin mà người dùng đã chỉ định dưới dạng các mục trong danh sách bật lên. Các quy tắc để tạo danh sách trong một số ô tương tự như các quy tắc trước đó, với ngoại lệ duy nhất là bạn phải chỉ định thông tin theo cách thủ công bằng cách sử dụng dấu chấm phẩy.

Tạo danh sách thả xuống bằng chức năng OFFSET

Ngoài phương pháp cổ điển, có thể sử dụng hàm TRANH CHẤPđể tạo menu thả xuống.

Hãy mở trang tính.

Để sử dụng chức năng cho danh sách thả xuống, bạn cần làm như sau:

1. Chọn ô quan tâm nơi bạn muốn đặt danh sách trong tương lai.
2. Mở tab "Dữ liệu" và cửa sổ "Xác thực dữ liệu" theo trình tự.
   

   

3. Lập danh sách". Điều này được thực hiện theo cách tương tự như các ví dụ trước. Cuối cùng, công thức sau được sử dụng: = OFFSET (A $ 2 $; 0; 0; 5). Chúng tôi nhập nó vào nơi các ô sẽ được sử dụng làm đối số được chỉ định.

Sau đó, chương trình sẽ tạo một menu với danh sách các loại trái cây.

Cú pháp cho điều này là:

= OFFSET (tham chiếu, line_offset, column_offset, [height], [width])

Chúng ta thấy rằng hàm này có 5 đối số. Đầu tiên, địa chỉ ô đầu tiên được bù đắp được đưa ra. Hai đối số tiếp theo chỉ định số hàng và cột cần bù đắp. Nói về chúng tôi, đối số Chiều cao là 5 vì nó đại diện cho chiều cao của danh sách. 

Danh sách thả xuống trong Excel với thay thế dữ liệu (+ sử dụng hàm OFFSET)

Trong trường hợp nhất định TRANH CHẤP được phép tạo menu bật lên nằm trong một phạm vi cố định. Nhược điểm của phương pháp này là sau khi thêm mục, bạn sẽ phải tự chỉnh sửa công thức.

Để tạo danh sách động có hỗ trợ nhập thông tin mới, bạn phải:

1. Chọn ô quan tâm.
2. Mở rộng tab "Dữ liệu" và nhấp vào "Xác thực dữ liệu".
3. Trong cửa sổ mở ra, hãy chọn lại mục “Danh sách” và chỉ định công thức sau làm nguồn dữ liệu: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Nhấp vào OK.

Điều này chứa một chức năng COUNTIF, để xác định ngay có bao nhiêu ô được điền (mặc dù nó có số lượng sử dụng lớn hơn nhiều, chúng tôi chỉ viết nó ở đây cho một mục đích cụ thể).

Để công thức hoạt động bình thường, cần phải theo dõi xem có ô trống trên đường dẫn của công thức hay không. Họ không nên như vậy.

Danh sách thả xuống với dữ liệu từ một trang tính hoặc tệp Excel khác

Phương pháp cổ điển không hoạt động nếu bạn cần lấy thông tin từ một tài liệu khác hoặc thậm chí một trang tính có trong cùng một tệp. Đối với điều này, hàm được sử dụng GIÁN TIẾP, cho phép bạn nhập theo đúng định dạng liên kết đến một ô nằm trong một trang tính khác hoặc nói chung - một tệp. Bạn cần làm như sau:

1. Kích hoạt ô nơi chúng tôi đặt danh sách.
2. Chúng tôi mở cửa sổ mà chúng tôi đã biết. Ở cùng một nơi mà trước đây chúng tôi đã chỉ ra các nguồn cho các phạm vi khác, một công thức được chỉ ra ở định dạng = INDIRECT (“[List1.xlsx] Sheet1! $ A $ 1: $ A $ 9”). Đương nhiên, thay vì List1 và Sheet1, bạn có thể chèn tên sách và trang tính của mình tương ứng. 

Chú ý! Tên tệp được đặt trong dấu ngoặc vuông. Trong trường hợp này, Excel sẽ không thể sử dụng tệp hiện đang bị đóng làm nguồn thông tin.

Cũng cần lưu ý rằng bản thân tên tệp chỉ có ý nghĩa nếu tài liệu được yêu cầu nằm trong cùng thư mục với tài liệu nơi danh sách sẽ được chèn. Nếu không, thì bạn phải chỉ định đầy đủ địa chỉ của tài liệu này.

Tạo trình đơn thả xuống phụ thuộc

Danh sách phụ thuộc là danh sách có nội dung bị ảnh hưởng bởi sự lựa chọn của người dùng trong danh sách khác. Giả sử chúng ta có một bảng đang mở trước mặt chúng ta chứa ba phạm vi, mỗi phạm vi đã được đặt một tên.

Bạn cần làm theo các bước sau để tạo danh sách có kết quả bị ảnh hưởng bởi tùy chọn đã chọn trong danh sách khác.

1. Tạo danh sách đầu tiên với các tên phạm vi.
   

   

2. Tại điểm nhập nguồn, các chỉ báo bắt buộc được đánh dấu từng cái một.
   

   

3. Tạo danh sách thứ 2 tùy thuộc vào loại cây mà người đó đã chọn. Ngoài ra, nếu bạn chỉ định cây trong danh sách đầu tiên, thì thông tin trong danh sách thứ hai sẽ là “sồi, cây trăn, hạt dẻ” và hơn thế nữa. Cần phải ghi công thức ở vị trí đầu vào của nguồn dữ liệu = CHỈ ĐỊNH (E3). E3 - ô chứa tên của phạm vi 1. = INDIRECT (E3). E3 - ô có tên của danh sách 1.

Bây giờ mọi thứ đã sẵn sàng.

Làm cách nào để chọn nhiều giá trị từ danh sách thả xuống?

Đôi khi không thể chỉ ưu tiên cho một giá trị, vì vậy phải chọn nhiều hơn một giá trị. Sau đó, bạn cần thêm macro vào mã trang. Sử dụng tổ hợp phím Alt + F11 sẽ mở Trình soạn thảo Visual Basic. Và mã được chèn vào đó.

Worksheet_Change phụ riêng tư (Mục tiêu ByVal dưới dạng phạm vi)

    On Error Resume Next

    If Not Intersect (Target, Range («Е2: Е9»)) Không có gì và Target.Cells.Count = 1 Thì

        Application.EnableEvents = Sai

        Nếu Len (Target.Offset (0, 1)) = 0 Thì

            Target.Offset (0, 1) = Mục tiêu

        khác

            Target.End (xlToRight).Offset (0, 1) = Mục tiêu

        Cuối Nếu

        Target.ClearContents

        Ứng dụng.EnableEvents = True

    Cuối Nếu

End Sub 

Để nội dung của các ô được hiển thị bên dưới, chúng tôi chèn đoạn mã sau vào trình chỉnh sửa.

Worksheet_Change phụ riêng tư (Mục tiêu ByVal dưới dạng phạm vi)

    On Error Resume Next

    If Not Intersect (Target, Range («Н2: К2»)) Không có gì và Target.Cells.Count = 1 Thì

        Application.EnableEvents = Sai

        Nếu Len (Target.Offset (1, 0)) = 0 Thì

            Target.Offset (1, 0) = Mục tiêu

        khác

            Target.End (xlDown).Offset (1, 0) = Mục tiêu

        Cuối Nếu

        Target.ClearContents

        Ứng dụng.EnableEvents = True

    Cuối Nếu

End Sub

Và cuối cùng, mã này được sử dụng để viết trong một ô.

Worksheet_Change phụ riêng tư (Mục tiêu ByVal dưới dạng phạm vi)

    On Error Resume Next

    If Not Intersect (Target, Range («C2: C5»)) Không có gì và Target.Cells.Count = 1 Thì

        Application.EnableEvents = Sai

        newVal = Mục tiêu

        Ứng dụng.Hoàn tác

        oldval = Mục tiêu

        Nếu Len(oldval) <> 0 Và oldval <> newVal Thì

            Target = Target & «,» & newVal

        khác

            Mục tiêu = newVal

        Cuối Nếu

        Nếu Len (newVal) = 0 thì Target.ClearContents

        Ứng dụng.EnableEvents = True

    Cuối Nếu

End Sub

Phạm vi có thể chỉnh sửa.

Làm thế nào để tạo danh sách thả xuống với một tìm kiếm?

Trong trường hợp này, ban đầu bạn phải sử dụng một loại danh sách khác. Tab “Nhà phát triển” mở ra, sau đó bạn cần nhấp hoặc nhấn (nếu màn hình cảm ứng) vào phần tử “Chèn” - “ActiveX”. Nó có một hộp kết hợp. Bạn sẽ được nhắc vẽ danh sách này, sau đó nó sẽ được thêm vào tài liệu.

Hơn nữa, nó được cấu hình thông qua các thuộc tính, trong đó một phạm vi được chỉ định trong tùy chọn ListFillRange. Ô nơi hiển thị giá trị do người dùng xác định được định cấu hình bằng tùy chọn LinkedCell. Tiếp theo, bạn chỉ cần ghi những ký tự đầu tiên, vì chương trình sẽ tự động gợi ý các giá trị có thể.

Danh sách thả xuống với thay thế dữ liệu tự động

Ngoài ra còn có một chức năng mà dữ liệu được thay thế tự động sau khi chúng được thêm vào phạm vi. Thật dễ dàng để làm điều này:

1. Tạo một tập hợp các ô cho danh sách trong tương lai. Trong trường hợp của chúng tôi, đây là một tập hợp các màu. Chúng tôi chọn nó.
   

   

2. Tiếp theo, nó cần được định dạng dưới dạng bảng. Bạn cần nhấp vào nút cùng tên và chọn kiểu bảng.
   

   

   

Tiếp theo, bạn cần xác nhận phạm vi này bằng cách nhấn nút “OK”.

Chúng tôi chọn bảng kết quả và đặt tên cho nó thông qua trường nhập liệu nằm trên đầu cột A.

Vậy là xong, có một bảng và nó có thể được sử dụng làm cơ sở cho danh sách thả xuống mà bạn cần:

1. Chọn ô chứa danh sách.
2. Mở hộp thoại Xác thực Dữ liệu.
   

   

3. Chúng tôi đặt kiểu dữ liệu thành “Danh sách” và dưới dạng các giá trị, chúng tôi cung cấp tên của bảng thông qua dấu =.
   

   

   

Mọi thứ, ô đã sẵn sàng và tên của các màu được hiển thị trong đó, như chúng tôi cần ban đầu. Giờ đây, bạn có thể thêm các vị trí mới đơn giản bằng cách viết chúng vào ô nằm thấp hơn một chút ngay sau ô cuối cùng.

Đây là lợi thế của bảng, đó là phạm vi tự động tăng khi dữ liệu mới được thêm vào. Theo đó, đây là cách tiện lợi nhất để thêm danh sách.

Làm thế nào để sao chép danh sách thả xuống?

Để sao chép, bạn chỉ cần sử dụng tổ hợp phím Ctrl + C và Ctrl + V. Vì vậy, danh sách thả xuống sẽ được sao chép cùng với định dạng. Để xóa định dạng, bạn cần sử dụng một hình dán đặc biệt (trong menu ngữ cảnh, tùy chọn này xuất hiện sau khi sao chép danh sách), nơi tùy chọn “điều kiện về giá trị” được đặt.

Chọn tất cả các ô có chứa danh sách thả xuống

Để hoàn thành tác vụ này, bạn phải sử dụng chức năng “Chọn một nhóm ô” trong nhóm “Tìm và Chọn”.

Sau đó, một hộp thoại sẽ mở ra, tại đây bạn nên chọn các mục “Tất cả” và “Các mục này giống nhau” trong menu “Xác thực dữ liệu”. Mục đầu tiên chọn tất cả các danh sách và mục thứ hai chỉ chọn những danh sách tương tự với một số danh sách nhất định.