Phổ Biến 4/2024 # Tạo Một Danh Sách Tùy Chọn Trong Excel: Tĩnh, Động, Từ Một Bảng Tính Khác # Top 6 Yêu Thích

Hướng dẫn này trình bày bốn cách nhanh chóng để tạo một danh sách tùy chọn của Excel – dựa trên một danh sách các giá trị, phạm vi của ô, dải ô được đặt tên và một danh sách tùy chọn động. Đồng thời cũng thấy cách để tạo ra một danh sách tùy chọn từ một bảng tính khác, chỉnh sửa và xóa danh sách.

Danh sách tùy chọn của Excel, hoặc danh sách tùy chọn hoặc hộp kết hợp, được sử dụng để nhập dữ liệu vào bảng tính từ danh sách các mục được xác định trước. Mục đích chính của việc sử dụng các danh sách tùy chọn trong Excel là để hạn chế số lượng các lựa chọn có sẵn cho người dùng. Ngoài ra, danh sách tùy chọn ngăn cản lỗi chính tả và làm cho dữ liệu nhập nhanh hơn.

Tạo danh sách tùy chọn có giá trị được phân tách bằng dấu phẩy

Đây là cách nhanh nhất để tạo danh sách tùy chọn chỉ trong 3 bước trên tất cả các phiên bản Excel 2013, 2010, 2007 và 2003.

Chọn một ô hoặc dãy ô cho danh sách tùy chọn của bạn

Bạn bắt đầu bằng cách chọn một ô hoặc nhiều ô mà bạn muốn danh sách tùy chọn xuất hiện. Đây có thể là một ô duy nhất, một dải ô hoặc toàn bộ cột. Nếu bạn chọn toàn bộ cột, một danh sách tùy chọn sẽ được tạo ra trong mỗi ô của cột đó, đó gọi là một trình tiết kiệm thời gian, ví dụ như khi bạn cần tạo bảng câu hỏi.

Sử dụng Excel Data Validation để tạo một danh sách tùy chọn.

Nhập vào mục danh sách và chọn các tùy chọn.

Trong cửa sổ Data Validation, trên tab , hãy thực hiện theo các bước sau:

Trong hộp Source, nhập các mục mà bạn muốn xuất hiện trong danh sách tùy chọn của bạn phân cách nhau bằng dấu phẩy (có hoặc không có dấu cách).

Đảm bảo chọn ô In-cell dropdown.

Theo tùy chọn, chọn Ignore blanks nếu bạn muốn cho phép người dùng để trống ô.

Nhấp OK và bạn đã hoàn tất!

Nếu bạn muốn chỉnh sửa danh sách tùy chọn Excel của bạn trong tương lai, bạn sẽ phải thay đổi tất cả các ô trích dẫn từ Data Validation. Đây không phải là vấn đề lớn nếu bạn chỉ có một vài ô như vậy nằm trên cùng một sheet, nhưng nếu bạn có hàng chục hoặc hàng trăm ô rải rác trên các bảng tính khác nhau, sẽ tốn kha khá thời gian để tìm và chỉnh sửa chúng.

Tạo danh sách tùy chọn của Excel dựa trên một phạm vi được đặt tên

Phương pháp tạo một danh sách tùy chọn trong Excel tốn nhiều thời gian hơn, nhưng về lâu về dài nó có thể tiết kiệm nhiều thời gian hơn.

Nhập các mục cho danh sách tùy chọn của bạn.

Chọn các mục mà bạn muốn xuất hiện trong danh sách tùy chọn của bạn trong một bảng tính hiện có hoặc gõ các mục trong một trang tính mới. Các giá trị này phải được nhập vào một cột hoặc một hàng mà không có bất kỳ ô trống nào.

Ví dụ: hãy tạo danh sách tùy chọn của các thành phần cho công thức nấu ăn yêu thích của bạn:

Tạo một phạm vi được đặt tên.

Bạn thực sự có thể bỏ qua bước này và tạo danh sách tùy chọn dựa trên một dãy ô , nhưng các dải ô được đặt tên thực sự giúp cho việc quản lý các danh sách tùy chọn của Excel dễ dàng hơn.

Chọn tất cả các mục mà bạn muốn đưa vào danh sách tùy chọn, nhấp chuột phải vào chúng, và chọn Define Name, từ danh sách tùy chọn. Ngoài ra, bạn có thể nhấp vào Name Manager trên tab hoặc nhấn .

Trong hộp thoại Name Manager, nhấp vào New…

Trong trường , đặt tên cho mục nhập của bạn, hãy chắc chắn rằng trong hộp Refers to đã hiển thị chính xác vùng địa chỉ của các mục đã nhập, và sau đó nhấp vào Ok. Đảm bảo không có dấu cách hay dấu gạch ngang trong tên của vùng phạm vi bạn vừa đặt, hãy sừ dụng dấu gạch dưới (_) thay thế.

Để thực hiện việc này, bạn có thể nhập =your_table_name [column_name] vào trường , hoặc chọn tất cả các ô trừ ô tiêu đề cột trước khi mở Name Manager và để hộp Refers to tự động xử lý.

Chọn vị trí cho danh sách tùy chọn của bạn

Đơn giản chỉ cần nhấp vào ô mà bạn muốn có danh sách tùy chọn. Điều này có thể nằm trong cùng một bảng, nơi danh sách mục của bạn được đặt hoặc trong một bảng tính khác. Bạn cũng có thể chọn một dải ô, hoặc toàn bộ cột.

Trong cửa sổ Data Validation, hãy chuyển tới tab và chọn các tùy chọn sau:

Trong hộp , nhập vào tên của vùng mà bạn đã đặt ở bước trên, đằng trước đặt thêm dấy bằng, ví dụ =Ingredients.

Đảm bảo chọn ô In-cell dropdown.

Đánh dấu vào Ignore blanks nếu người dùng được phép để ô trống.

Và cuối cùng, nhấp vào nút OK để hoàn tất việc tạo danh sách tùy chọn của bạn.

Bạn bỏ qua bước 2 – tạo một phạm vi được đặt tên .

Trong bước 5 , khi cài đặt danh sách tùy chọn của bạn, thay vì nhập tên của dải ô, hãy nhấp vào biểu tượng Collapse Dialog bên cạnh hộp Source và chọn tất cả các ô có mục nhập bạn muốn đưa vào danh sách tùy chọn của bạn. Chúng có thể ở trong cùng hoặc trong một bảng tính khác. Nếu ở bảng tính khác, bạn chỉ cần đi đến sheet đó và chọn một phạm vi bằng cách sử dụng chuột.

Tạo một danh sách tùy chọn động (tự động cập nhật)

Khóa học Excel nâng cao Hà Nội

Nếu bạn thường xuyên chỉnh sửa các mục trong danh sách tùy chọn, bạn có thể muốn tạo danh sách tùy chọn động trong Excel. Trong trường hợp này, danh sách của bạn sẽ được cập nhật tự động trong tất cả các ô có chứa nó, khi bạn xoá hoặc thêm các mục mới vào danh sách nguồn.

Cách dễ nhất để tạo danh sách tùy chọn được cập nhật tự động trong Excel là tạo một danh sách có tên dựa trên một bảng . Nếu vì lý do nào đó bạn thích một phạm vi tên thông thường, thì tham khảo cách sử dụng công thức OFFSET, như được giải thích bên dưới.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Bạn bắt đầu bằng cách tạo một danh sách tùy chọn thông thường dựa trên một phạm vi được đặt tên như mô tả ở trên.

Trong bước 2, khi tạo một tên, bạn đặt công thức sau vào hộp Refers to.

Trong đó:

Sheet1 – tên của sheet đó

A – cột nơi chứa các mục của danh sách tùy chọn của bạn

$A$1 – ô chứa mục đầu tiên trong danh sách

Như bạn thấy, công thức bao gồm 2 hàm Excel – OFFSET và COUNTA. Hàm COUNTA tính tất cả các ô có ký tự trong cột được chỉ định. OFFSET lấy số đó và trả về một tham chiếu đến một phạm vi chỉ bao gồm các ô có ký tự, bắt đầu từ ô đầu tiên bạn chỉ định trong công thức.

Nếu bạn tò mò muốn có hiểu logic của công thức trên.

Trong Microsoft Excel, hàm OFFSET trả về một tham chiếu đến một dãy bao gồm một số lượng các hàng và cột nhất định. Bạn có thể sử dụng nó trong các trường hợp khác nhau khi bạn cần phải có một phạm vi động, tức là liên tục thay đổi.

OFFSET(reference, rows, cols, [height], [width])

Cú pháp của hàm OFFSET như sau:

reference – ô đầu tiên hoặc một dãy ô liền kề mà bạn muốn căn cứ vào giá trị bù đắp.

rows – số hàng, lên hoặc xuống, mà bạn muốn mà bạn muốn ô ở góc trên bên trái tham chiếu tới

cols – tương tự như các hàng, tức là số cột, sang trái hoặc phải, cho ô phía trên bên trái tham chiếu tới.

height, Tùy chọn – số hàng trong phạm vi trả về.

width, Tùy chọn – số cột trong trong phạm vi trả về.

Vì vậy, trong công thức OFFSET của chúng ta =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1), chúng ta xác định các đối số sau:

Tạo một danh sách tùy chọn từ một bảng tính khác

reference – ô $A$1 trong Sheet1, là mục đầu tiên trong danh sách tùy chọn của bạn;

rows & cols – 0 vì bạn không muốn thay đổi phạm vi trả về theo chiều dọc hoặc chiều ngang;

height – số ô không rỗng trong cột A, được trả về bởi hàm COUNTA;

width – 1, tức là một cột.

Bạn có thể tạo một danh sách tùy chọn trong Excel bằng cách sử dụng một danh sách từ một bảng làm nguồn. Để thực hiện việc này, bạn sẽ phải tạo ra 2 phạm vi được đặt tên – một trong bảng tính nguồn và cái còn lại trong bảng tính mà bạn muốn sử dụng danh sách Data Validation.

Chú ý. Đối với danh sách tùy chọn từ một bảng tính khác để làm việc, bảng tính với danh sách nguồn phải được mở.

Danh sách tùy chọn tĩnh từ một bảng tính khác

Danh sách tùy chọn được tạo theo cách này sẽ không cập nhật tự động khi bạn thêm hoặc xóa các mục trong danh sách nguồn và bạn sẽ phải sửa đổi phần trích dẫn danh sách nguồn theo cách thủ công.

Tạo một phạm vi được đặt tên cho danh sách nguồn

Tạo một vùng được đặt tên trong bảng tính chính

Mở bảng tính mà bạn muốn danh sách tùy chọn xuất hiện và tạo một tên trích dẫn đến danh sách nguồn của bạn. Trong ví dụ này, tham chiếu hoàn thành là = SourceBook.xlsx! Source_list

Danh sách tùy chọn được tạo theo cách này sẽ được cập nhật ngay khi bạn thực hiện bất kỳ thay đổi nào đối với danh sách nguồn.

Data Validation không hoạt động

Tạo một tên phạm vi trong bảng tính Nguồn với công thức OFFSET, như được giải thích trong Tạo một danh sách tùy chọn động .

Trong bảng tính chính, áp dụng Data Validation theo cách thông thường.

Tùy chọn Data Validation có màu xám hoặc không khả dụng? Có một vài nguyên nhân có thể xảy ra:

Các tùy chọn bổ sung cho danh sách tùy chọn của Excel

Danh sách tùy chọn không thể được thêm vào bảng tính được bảo vệ hoặc chia sẻ. Loại bỏ bảo vệ hoặc ngừng chia sẻ bảng tính, và sau đó thử nhấp lại vào Data Validation một lần nữa.

Bạn đang tạo ra một danh sách tùy chọn từ một bảng Excel được liên kết đến một trang SharePoint. Hủy liên kết bảng hoặc xóa định dạng bảng và thử lại.

Nếu bạn muốn hiển thị cho người dùng một thông báo bật lên khi họ nhấp vào bất kỳ ô nào chứa danh sách tùy chọn của bạn, hãy tiếp tục theo cách này:

Hãy đảm bảo chọn Show input message when cell is selected

Nhập tiêu đề và thông báo vào các trường tương ứng (tối đa 225 ký tự).

Nhấp vào nút OK để lưu tin nhắn và đóng hộp thoại.

Cho phép người dùng nhập dữ liệu của họ vào một hộp kết hợp

Tổng hợp thủ thuật Excel hay nhất mọi thời đại

Theo mặc định, danh sách tùy chọn mà bạn tạo ra trong Excel là không thể chỉnh sửa, tức là bị giới hạn trong các giá trị trong danh sách. Tuy nhiên, bạn có thể cho phép người dùng nhập các giá trị của họ vào hộp.

Về mặt kỹ thuật, điều này sẽ biến một danh sách tùy chọn vào một hộp kết hợp Excel. Thuật ngữ “hộp kết hợp” có nghĩa là một danh sách tùy chọn có thể chỉnh sửa cho phép người dùng chọn một giá trị từ danh sách hoặc nhập một giá trị trực tiếp vào hộp.

Chọn hộp kiểm “how error alert after invalid data is entered” nếu bạn muốn hiển thị cảnh báo khi người dùng cố gắng nhập một số dữ liệu vào hộp kết hợp khác với trong danh sách tùy chọn của bạn (điều này không ngăn người nhập Dữ liệu riêng của họ). Nếu bạn không muốn một thông báo xuất hiện, hãy bỏ chọn nó.

Nếu bạn đã chọn hiển thị một thông điệp cảnh báo, hãy chọn một trong các tuỳ chọn từ hộp Stylevà nhập tiêu đề và thông báo. Chọn Hoặc Information hoặc Warning để cho phép người sử dụng nhập văn bản riêng của họ trong hộp combo. Các tùy chọn chỉ khác nhau về biểu tượng được hiển thị cùng với thông báo và nút được chọn theo mặc định.

Một lời nhắn Information sẽ được hiển thị với “i” biểu tượng và nút chọn OK. Nên chọn tùy chọn này được nếu người dùng của bạn phải nhập các lựa chọn của riêng họ khá thường xuyên.

Một lời nhắn Warningsẽ được hiển thị với “!” Biểu tượng và nút “No” được chọn trước. Đây được coi là một cách tốt để hiển thị cảnh báo nếu bạn muốn người dùng của mình chọn một mục từ danh sách tùy chọn thay vì nhập dữ liệu của riêng họ.

Stop sẽ ngăn người khác nhập bất kỳ dữ liệu nào không có trong danh sách tùy chọn Excel của bạn.

Làm thế nào để chỉnh sửa một danh sách tùy chọn Excel

Mẹo. Nếu bạn không biết nhập tiêu đề hoặc tin nhắn nào, bạn có thể để trống. Trong trường hợp này, Microsoft Excel sẽ hiển thị cảnh báo mặc định “The value you entered is not valid. A user has restricted values that can be entered into this cell.”

Nếu bạn đã tạo một danh sách tùy chọn bằng dấu phẩy, hãy tiếp tục các bước sau:

Chọn một ô hoặc các ô trích dẫn danh sách Data Validation Excel của bạn, ví dụ các ô có chứa danh sách tùy chọn mà bạn muốn chỉnh sửa.

Xóa hoặc nhập các mục mới trong hộp Source.

Nhấn OK để lưu các thay đổi và đóng cửa sổ Data Validation Excel .

Nếu bạn đã tạo ra một danh sách tùy chọn bằng cách chỉ định một dãy ô chứ không phải là tham chiếu đến một dải ô được đặt tên, thì tiến hành theo cách sau.

Đi qua bảng tính có chứa các mục xuất hiện trong danh sách tùy chọn của bạn và chỉnh sửa danh sách theo cách bạn muốn.

Chọn ô hoặc ô có chứa danh sách tùy chọn của bạn.

Trong cửa sổ Data Validation Excel , trên tab , thay đổi các tham chiếu ô trong hộp Nguồn. Bạn có thể chỉnh sửa chúng bằng tay hoặc nhấp vào biểu tượng Collapse Dialog.

Nhấp vào nút OKđể lưu các thay đổi và đóng cửa sổ.

Chỉnh sửa danh sách tùy chọn của Excel dựa trên một phạm vi được đặt tên

Mẹo. Nếu bạn muốn áp dụng các thay đổi cho tất cả các ô có chứa danh sách tùy chọn này, hãy chọn tùy chọn “Apply these changes to all other cells with the same settings

Nếu bạn đã tạo một danh sách tùy chọn được đặt tên, thì bạn chỉ có thể chỉnh sửa các mục của phạm vi của mình và sau đó thay đổi trích dẫn đến phạm vi được Đặt tên. Tất cả các danh sách tùy chọn dựa trên phạm vi được đặt tên này sẽ được cập nhật tự động.

Thêm hoặc xóa các mục trong phạm vi được đặt tên. Mở bảng tính chứa phạm vi được đặt tên của bạn, xóa hoặc nhập mới. Hãy nhớ sắp xếp các mục theo thứ tự bạn muốn chúng xuất hiện trong danh sách tùy chọn Excel của bạn.

Thay đổi tham chiếu đến phạm vi được Đặt tên.

Trong cửa sổ Name Manager, hãy chọn phạm vi đã đặt tên mà bạn muốn cập nhật.

Thay đổi tham chiếu trong hộp bằng cách nhấp vào biểu tượng Collapse Dialog và chọn tất cả các mục nhập cho danh sách tùy chọn của bạn.

Nhấp vào nút , sau đó trong thông báo xác nhận xuất hiện, nhấp vào Yesđể lưu các thay đổi của bạn.

Nếu bạn không muốn có danh sách tùy chọn trong bảng tính Excel, bạn có thể xóa chúng khỏi một hoặc tất cả các ô.

Loại bỏ một danh sách tùy chọn từ ô đã chọn

Chọn một ô hoặc vài ô mà từ đó bạn muốn bỏ các danh sách tùy chọn.

Trên tab Cài đặt, chọn nút Clear All.

Bằng cách này, bạn có thể loại bỏ một danh sách tùy chọn từ tất cả các ô liên kết trong bảng tính hiện tại. Thao tác này sẽ không xóa cùng một danh sách tùy chọn từ các ô trong các bảng tính khác, nếu có.

Chọn bất kỳ ô chứa danh sách tùy chọn của bạn.

Trong cửa sổ Data Validation, trên tab , chọn hộp kiểm “Apply these changes to all other cells with the same settings“. Khi đã chọn, tất cả các ô trích xuất danh sách Data Validation Excel này sẽ được chọn.

Nhấp vào nút để xóa danh sách tùy chọn .

Nhấp để lưu các thay đổi và đóng cửa sổ Data Validation.