Danh sách xác thực Excel từ bảng

Khi bạn nhấp vào một ô được liên kết với danh sách thả xuống, bạn có thể thấy một nút mũi tên nhỏ ở bên phải. Nhấp vào nó và danh sách mở rộng, hiển thị các lựa chọn khả dụng được liệt kê, như hình bên dưới

Nếu bạn nhấp vào bất kỳ mục nào trong danh sách thả xuống, ô sẽ lấy giá trị của mục đó. Trong ví dụ trên, nhấp vào "Tây Âu" trong danh sách thả xuống và ô hiện được chọn ("B5") sẽ được điền giá trị "Tây Âu"

Tại sao bạn nên tạo danh sách thả xuống trong Microsoft Excel

Sử dụng danh sách thả xuống trong Excel có nhiều lợi ích. Những danh sách như vậy giúp giữ sổ làm việc của bạn sạch sẽ và giúp người dùng hiệu quả hơn. Thông thường có 3 lý do chính để sử dụng danh sách thả xuống trong Excel

1) Danh sách thả xuống cung cấp cho người dùng một giao diện dễ dàng để hiểu những gì đầu vào được mong đợi từ họ

Mặc dù điều này có vẻ khó hiểu đối với bạn khi bạn tạo bảng tính Excel, nhưng chắc chắn những người dùng khác không quen thuộc với tệp của bạn sẽ không hiểu những gì bạn mong đợi họ nhập làm đầu vào

Với danh sách thả xuống, họ sẽ thấy một số lựa chọn hạn chế và sẽ không cần đoán những gì được mong đợi từ họ

Chẳng hạn, bạn có thể cho phép người dùng thử nghiệm các kịch bản khác nhau bằng cách chỉ cần chọn một kịch bản từ danh sách thả xuống của bạn. Điều này là trực quan và giúp tạo ra trải nghiệm người dùng tích cực cần thiết để sổ làm việc của bạn được người khác sử dụng

2) Danh sách thả xuống ngăn lỗi chính tả

Bằng cách giới hạn đầu vào của người dùng trong danh sách các giá trị có thể được xác định trước, bạn đảm bảo rằng đầu vào của họ sẽ khớp với chính tả hoặc định dạng mong muốn. Bảng tính của bạn sẽ sạch sẽ, với dữ liệu đồng nhất và đúng chính tả

Điều này càng quan trọng hơn nếu bạn có các công thức phụ thuộc vào các giá trị trong ô. Trong trường hợp đó, bạn không thể mắc một lỗi chính tả nhỏ (chẳng hạn như một khoảng trống thừa) khiến tất cả các công thức của bạn trả về lỗi

Nếu bạn có công thức tùy thuộc vào văn bản, hãy sử dụng danh sách thả xuống để đảm bảo viết đúng chính tả

3) Danh sách thả xuống cho phép người dùng nhập đầu vào nhanh hơn

Ví dụ, bạn có thể đã trải nghiệm điều này khi điền vào các biểu mẫu trên Internet. Nhập văn bản sẽ lâu hơn nếu bạn chỉ phải chọn trong danh sách lựa chọn được xác định trước

Bằng cách sử dụng danh sách thả xuống trong sổ làm việc Excel, bạn sẽ giúp người dùng tương tác với bảng tính và nhập dữ liệu dễ dàng và nhanh hơn

Điều đó không quan trọng lắm nếu tệp không được sử dụng thường xuyên và nếu bạn cần một số lượng giới hạn đầu vào của người dùng. Nhưng nếu bạn cần một số đầu vào của người dùng, nó có thể tiết kiệm rất nhiều phút cho mỗi mục nhập của người dùng

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

Tóm lược

Bạn có thể tạo danh sách sổ xuống Excel bằng cách thực hiện theo các bước dưới đây

1. Chọn ô hoặc phạm vi mà bạn muốn thêm menu thả xuống

2. Từ ruy-băng Excel, chuyển đến tab Dữ liệu và trong menu Xác thực dữ liệu, bấm vào Xác thực dữ liệu

3. Một hộp thoại mở ra. Chọn Danh sách trong menu Cho phép

4. Trong Nguồn, hãy nhập các giá trị theo cách thủ công (có dấu phân cách) hoặc chọn một phạm vi chứa các giá trị mà bạn sẽ cho phép trong menu thả xuống

Bạn có thể xem video hướng dẫn bên dưới để xem nó hoạt động

Thêm danh sách thả xuống vào một hoặc nhiều ô cùng một lúc

Bạn thực sự có thể thêm menu thả xuống không chỉ một mà nhiều ô cùng một lúc. bạn có thể chọn

• Một ô hoặc phạm vi

• Tổ hợp các ô hoặc phạm vi không liền kề. giữ phím CTRL và nhấp vào từng ô hoặc phạm vi bạn muốn thêm vào vùng chọn

• Toàn bộ hàng hoặc cột. trong trường hợp đó, mỗi ô của hàng/cột sẽ được liên kết với danh sách thả xuống

Cách tạo danh sách thả xuống đơn giản, chứa các giá trị bị hạn chế của chính nó

Khi tạo danh sách xác thực dữ liệu, việc mã hóa cứng các lựa chọn có sẵn đôi khi đơn giản và dễ dàng hơn

• Chọn phạm vi của bạn và chuyển đến tab Dữ liệu, rồi đến Xác thực dữ liệu

• Chọn Danh sách trong menu Cho phép

• Trong Nguồn, hãy nhập thủ công từng mục sẽ điền vào danh sách của bạn, được phân tách bằng dấu tách Excel (dấu phẩy hoặc dấu chấm phẩy tùy thuộc vào cài đặt cục bộ của bạn)

• Nhấn OK để xác thực

Đó là nó. Bây giờ bạn đã tạo danh sách thả xuống trong các ô đã chọn. Giờ đây, người dùng có thể nhấp vào mũi tên nhỏ bên phải và nhấp vào mục của danh sách thay vì nhập toàn văn

Nếu bạn cố gắng chèn một giá trị bị cấm, chẳng hạn như "Có thể", thông báo lỗi bên dưới sẽ hiển thị

Danh sách thả xuống đơn giản này có lợi ích là rất dễ tạo. Nó cũng không yêu cầu "làm ô nhiễm" bảng tính của bạn với các phạm vi khác có chứa các mục danh sách. Đây cũng là một cách để giữ cho tệp của bạn đơn giản và ẩn các lựa chọn có sẵn để người dùng không sửa đổi chúng

Tuy nhiên, phương pháp này yêu cầu bạn nhập từng giá trị theo cách thủ công, điều này có thể gây cồng kềnh và tốn thời gian, đặc biệt nếu bạn muốn cho phép nhiều tùy chọn khác nhau trong danh sách xác thực

Để khắc phục điều này, bạn cần tạo một danh sách thả xuống được liên kết với một phạm vi Excel

Cách tạo danh sách thả xuống được liên kết với một phạm vi Excel

Nếu bạn không muốn nhập thủ công từng mục trong danh sách xác thực của mình, bạn cần liên kết mục đó với một phạm vi Excel

Tiếp tục như trên, nhưng thay vì nhập các giá trị vào trường Nguồn, hãy chọn một phạm vi trong bảng tính của bạn (hoặc trong bất kỳ sổ làm việc nào, đối với vấn đề đó)

Bây giờ nếu bạn quay lại các ô bạn đã chọn ban đầu, bạn sẽ thấy trình đơn thả xuống như trong ví dụ bên dưới. Bạn có thể sử dụng mũi tên nhỏ ở bên phải để hiển thị danh sách, sau đó nhấp vào một giá trị trong danh sách sẽ thay thế giá trị của ô của bạn bằng giá trị bạn đã nhấp vào mà không cần phải nhập thủ công

Có nhiều lợi thế khi sử dụng một phạm vi làm nguồn thay vì các giá trị được mã hóa cứng

• Nó giúp bạn tiết kiệm thời gian nhập từng mục trong hộp thoại Xác thực dữ liệu

• Nó cũng cho phép bạn dễ dàng thay đổi các lựa chọn có sẵn trong menu, chỉ bằng cách chỉnh sửa phạm vi

• Nó cũng cho phép bạn làm cho danh sách của mình năng động hơn, sử dụng các công thức trong phạm vi nguồn để thay đổi các lựa chọn có sẵn hoặc thậm chí là các menu xác thực lồng nhau. Chẳng hạn, bạn có thể gọi đầu vào Có/không trong danh sách thả xuống, sau đó một danh sách thả xuống khác sẽ chỉ hiển thị các mục có ý nghĩa với đầu vào của người dùng trước đó

Khi bạn có nhiều menu xác thực dữ liệu bằng cách sử dụng các chuỗi khác nhau, một phương pháp hay có thể là giữ tất cả các phạm vi danh sách trong một tab riêng biệt mà bạn sử dụng làm tham chiếu. Điều này sẽ giúp việc chỉnh sửa danh sách của bạn sau đó dễ dàng hơn, đồng thời ngăn người dùng sửa đổi menu do nhầm lẫn

Tuy nhiên, vẫn có một giới hạn đối với các menu này có thể làm phiền bạn trong một số trường hợp. Thêm các mục mới vào danh sách yêu cầu bạn thay đổi phạm vi nguồn. Điều này có thể khá khó chịu nếu bạn phải làm điều đó thường xuyên. Điều bạn muốn là danh sách tự động cập nhật khi các mục mới được thêm vào trong phạm vi nguồn của bạn, phải không?

Cách tạo nguồn danh sách thả xuống có kích thước biến với phạm vi được đặt tên

Tạo một danh sách động sẽ giúp bạn không phải cập nhật nguồn dữ liệu. Tôi cho rằng bạn đã biết về các dãy được đặt tên. Nếu đây không phải là trường hợp, bạn PHẢI xem bài đăng này sẽ cho bạn biết tất cả về nó

Khi chọn nguồn dữ liệu cho danh sách thả xuống của mình, bạn có thể nhấn F3 và chọn một dải ô được đặt tên làm nguồn dữ liệu. Vì vậy, nếu phạm vi được đặt tên của bạn có kích thước thay đổi, thì nguồn dữ liệu của bạn cũng vậy. Và về cơ bản có 2 cách để tạo tên dải ô có kích thước thay đổi

Cách dễ nhất và hiệu quả nhất là tạo một bảng. Trước tiên, hãy chọn phạm vi có danh sách mục hiện tại của bạn. Sau đó chuyển đến tab Chèn và nhấp vào Bảng (bạn cũng có thể sử dụng Ctrl + T)

Ngoài ra, thay vì tạo Bảng, bạn có thể sử dụng phạm vi đã đặt tên. Sau đó, bạn chỉ cần tạo danh sách thả xuống và trong nguồn dữ liệu, nhấn F3. Danh sách các phạm vi đã đặt tên trong bảng tính của bạn sẽ xuất hiện và bạn có thể chọn phạm vi đã đặt tên làm nguồn cho danh sách thả xuống của mình

Bây giờ bạn có một nguồn dữ liệu động sẽ tự động cập nhật khi các giá trị mới được thêm vào hoặc xóa khỏi bảng

Một giải pháp thay thế - nhưng kém thanh lịch hơn - để sử dụng Bảng Excel, là tận dụng hàm mảng hoặc hàm OFFSET để xác định phạm vi có kích thước thay đổi

Về cơ bản, công thức này sẽ trả về một phạm vi phụ thuộc vào số hàng trong cột B. Giống như với Bảng, hãy sử dụng nó làm nguồn dữ liệu cho danh sách thả xuống của bạn và nó sẽ tự động cập nhật khi các giá trị mới được thêm vào cột B

Tùy chỉnh các tùy chọn cho danh sách thả xuống của bạn

Hiển thị thông báo có chỉ báo khi người dùng nhấp vào ô có danh sách thả xuống

Bạn có thể tạo một thông báo thông tin ngắn sẽ xuất hiện khi người dùng nhấp vào một ô có danh sách thả xuống. Điều này hữu ích khi bạn muốn cung cấp một số thông tin bổ sung cho người dùng về cách anh ta nên cung cấp thông tin đầu vào chẳng hạn

Quay lại tab Dữ liệu, nhấp vào Xác thực dữ liệu và chuyển đến tab Thông báo đầu vào. Đảm bảo rằng hộp kiểm Hiển thị thông báo đầu vào khi ô được chọn được đánh dấu. Sau đó, nhập Tiêu đề và Thông báo, nhấp vào OK và thông báo đầu vào sẽ xuất hiện khi ô thả xuống được chọn, như trong ví dụ bên dưới

Cho phép người dùng thêm các giá trị không được liệt kê

Đôi khi bạn muốn danh sách thả xuống cho phép nhập nhanh, nhưng bạn không muốn giới hạn người dùng trong danh sách các mục được xác định trước

Trong trường hợp đó, hãy chuyển đến tab Cảnh báo lỗi và bỏ chọn hộp kiểm Hiển thị cảnh báo lỗi sau khi nhập dữ liệu không hợp lệ. Điều này sẽ làm cho danh sách không độc quyền, cho phép người dùng nhập các giá trị không có trong danh sách của bạn

Sau đó, bạn có thể xác định thông báo tùy chỉnh để người dùng kiểm tra lại mục nhập dữ liệu

Kiểm soát loại dữ liệu, không phải chính giá trị

Đôi khi điều bạn muốn không phải là giới hạn số lượng mục để người dùng chọn mà chỉ đơn giản là kiểm soát loại dữ liệu. Thông thường, chỉ cho phép nhập ngày, số hoặc văn bản

Để làm như vậy, thay vì Danh sách, hãy chọn loại dữ liệu bạn chọn trong danh sách Cho phép như hình bên dưới

Cách xóa danh sách thả xuống Excel

Bạn có thể xóa danh sách thả xuống để cho phép người dùng nhập bất kỳ giá trị nào. Để xóa menu xác thực trong Excel, hãy làm theo các bước sau

• Chuyển đến tab Dữ liệu

• Trong menu Xác thực dữ liệu, nhấp vào Xác thực dữ liệu

• Trong menu Cho phép, thay đổi giá trị từ Danh sách thành Giá trị bất kỳ

• Nhấn OK để xác thực

Cách thêm danh sách thả xuống vào biểu đồ để làm cho nó động

Nó tạo ấn tượng khá tốt khi bạn cung cấp một bảng tính Excel cho phép người dùng chọn chuỗi trong danh sách thả xuống sẽ được hiển thị trong biểu đồ. Chẳng hạn, người dùng có thể chọn Sản phẩm A, Sản phẩm B hoặc Sản phẩm C trong danh sách và biểu đồ sẽ tự động cập nhật để hiển thị xu hướng bán hàng cho sản phẩm cụ thể đó

Để làm như vậy, tất cả những gì bạn cần làm là sử dụng, làm nguồn dữ liệu cho biểu đồ, một phạm vi sẽ thay đổi khi ô có danh sách thả xuống nhận một giá trị khác

Trong ví dụ này, biểu đồ điều chỉnh để hiển thị các giá trị cho sản phẩm được người dùng chọn trong danh sách thả xuống

Kết quả này có thể đạt được bằng cách sử dụng làm nguồn dữ liệu cho biểu đồ, các giá trị trong cột F. Và cột F chứa công thức sau

=OFFSET([@Date];;MATCH($J$2;Table2[[# Headers];[Sản phẩm A]. [Sản phẩm C]];0))

Những gì nó làm. nó bắt đầu từ cột "Ngày" trong Bảng và đi sang 2 cột bên phải để lấy giá trị cho Sản phẩm B. Hàm MATCH trả về 2 vì "Sản phẩm B" (trong ô "J2") là giá trị của ô thứ 2 trong phạm vi [Sản phẩm A]. [Sản phẩm C]

Bạn muốn có một ví dụ?

Tải xuống tệp ví dụ

Để biết giải thích chi tiết hơn về biểu đồ động, hãy xem bài đăng chuyên dụng này tại đây

Bây giờ bạn đã trở thành bậc thầy về danh sách thả xuống của Excel. Nếu bạn có thắc mắc hoặc muốn chia sẻ một số ý tưởng, vui lòng liên hệ với chúng tôi

Nếu bạn là người sử dụng nhiều Excel, chắc chắn bạn sẽ thích phần bổ trợ Power-user. Nhận hàng chục tính năng nâng cao mới sẽ giúp bạn trở thành ông chủ của Excel

Cách tạo drop phụ thuộc

Xác thực dữ liệu có thể liệt kê từ một trang tính khác không?