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

Hướng dẫn này của Học Excel Online 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 (hay còn gọi là tạo List trong 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.

Làm thế nào để tạo ra một danh sách tùy chọn Excel

Về tổng thể, có 4 cách để thực hiện một danh sách tùy chọn trong Excel, và tất cả chúng đều có những điểm mạnh và yếu riêng. Dưới đây là sơ lược những ưu điểm và nhược điểm chính cũng như hướng dẫn chi tiết từng bước cho từng phương pháp:

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.

  1. 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.

Bạn thậm chí có thể chọn các ô không tiếp giáp bằng cách nhấn và giữ phím Ctrl trong khi chọn ô bằng chuột.

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

Trên thanh cuộn của Excel, hãy chuyển đến tab Data > Data Tools và nhấp vào Data Validation.

Use Excel Data Validation to create a drop-down list.

  1. 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 Settings, hãy thực hiện theo các bước sau:

Excel users click an arrow next to a cell containing a dropdown box, and then select the entry they want from the drop down menu.

Bây giờ, người dùng Excel chỉ cần nhấp vào mũi tên bên cạnh một ô có danh sách tùy chọn, và sau đó chọn các mục có trong đó.

Type the entries for your Excel drop-down list.Vậy là danh sách tùy chọn của bạn đã sẵn sàng trong chưa đầy một phút. Tuy nhiên, đây không phải là cách tốt nhất để tạo một danh sách tùy chọn của Excel, đặc biệt trong trường hợp bạn muốn cùng một danh sách xuất hiện trong nhiều ô, và đây là lý do tại sao …

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.

  1. 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:

Mẹo. Bạn nên 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.

Xem thêm: Khoá học Excel cơ bản

  1. 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.

The fastest way to create a named range in Excel.

Mẹo. Một cách nhanh hơn để tạo một phạm vi được đặt tên trong Excel là chọn các ô và đặt tên dải ô một cách trực tiếp ở Name Box. Khi hoàn tất, nhấp Enter để lưu phạm vi được đặt tên mới tạo.

Danh sách tùy chọn dựa trên một bảng

Thay vì một phạm vi được đặt tên thông thường, bạn có thể cân nhắc chuyển đổi danh sách của bạn thành 1 bảng Excel với đầy đủ chức năng ( Insert > Table) trước, sau đó tạo tên cho danh sách đó.

Để thực hiện việc này, bạn có thể nhập =your_table_name [column_name] vào trường Refers to, 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ý.

Select the location of your drop-down list.Tại sao lại sử dụng bảng? Trước tiên, bởi vì nó cho phép bạn tạo ra một danh sách tùy chọn động và cho bạn tự do cập nhật them những thứ cần thiết từ vùng trích dẫn được đặt tên khi bạn loại bỏ hoặc thêm các mục mới vào danh sách nguồn.

  1. 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.

Chú thích. Excel 2003 trở lên yêu cầu danh sách tùy chọn nằm trên cùng một bảng tính.

  1. Áp dụng Xác Nhận Dữ Liệu Excel

Trên thanh cuộn băng của Excel, đến tab Data và nhấp vào Data Validation. Configure your Excel drop-down list.

  1. Cài đặt danh sách tùy chọn của bạn

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

An Excel drop-down list with a scroll bar

Nếu danh sách nguồn chứa nhiều hơn 8 mục, danh sách tùy chọn của bạn sẽ có một thanh cuộn như sau:

Tạo một danh sách tùy chọn dựa trên một dải ô

Việc tạo ra một danh sách tùy chọn dựa trên một dãy ô tương tự như việc tạo một danh sách tùy chọn dựa trên phạm vi được đặt tên, chỉ có hai sự khác biệt:

  1. Bạn bỏ qua bước 2 - tạo một phạm vi được đặt tên .
  2. 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.

Creating a dynamic drop-down list in Excel using the OFFSET formula

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

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

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.

  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.
  2. 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.

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

Trong đó:

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.

Create a named list in the source workbook.Ưu điểm chính của danh sách tùy chọn động là bạn sẽ không phải thay đổi phạm vi trích dẫn đã đặt tên mỗi lần sau khi chỉnh sửa danh sách nguồn. Bạn chỉ cần xóa hoặc nhập các mục nhập mới trong danh sách nguồn và tất cả các ô có chứa danh sách Xác nhận Dữ liệu này sẽ được cập nhật tự động!

Chi tiết về OFFSET

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.

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

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

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

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.

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

Mở bảng tính có chứa danh sách nguồn trong ví dụ này là SourceBook.xlsx và tạo một phạm vi được đặt tên cho các mục mà bạn muốn đưa vào danh sách tùy chọn, ví dụ Source_list . Apply Data Validation.

  1. 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

Configuring a message to be displayed when a cell with the drop-down list is clicked.Chú ý. Bạn phải đính kèm tên của bảng tính với dấu nháy (‘) nếu nó chứa bất kỳ dấu cách nào. Ví dụ:=’Source Book.xlsx’!Source_list

  1. Áp dụng Data Validation

Trong bảng tính chính, chọn các ô cho danh sách tùy chọn của bạn, nhấp vào Data > Data Validation và nhập tên bạn đã tạo trong bước 2 trong hộp Source.

A message is displayed when a cell with the drop-down list is clicked. Danh sách tùy chọn động 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ẽ đượ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.

  1. 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 .
  2. Trong bảng tính chính, áp dụng Data Validation theo cách thông thường.

Data Validation không hoạt độ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

Trong hầu hết các trường hợp, các tùy chọn của tab Settings chúng ta đã thảo luận ở trên hoàn toàn đầy đủ. Nếu không ta cũng có thêm tùy chọn khác có sẵn trên các tab khác của cửa sổ hộp thoại Data Validation.

Hiển thị một thông báo khi nhấp vào một ô có chứa danh sách tùy chọn

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:

Kết quả trong Excel sẽ giống như sau:

Tổng hợp thủ thuật Excel hay nhất mọi thời đạ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

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.

  1. Trong hộp thoại Data Validation (Data tab > Data Validation), đến tab Error Alert
  2. Chọn hộp kiểm “Show 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ó.
  3. 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.

Và đây là cách thông báo cảnh báo tùy chỉnh của bạn xuất hiện trong 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.”

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

Sau khi bạn tạo một danh sách tùy chọn trong Excel, bạn có thể muốn thêm nhiều mục vào nó hoặc xóa một số mục hiện có. Làm thế nào bạn làm điều này phụ thuộc vào danh sách tùy chọn của bạn đã được tạo ra.

Chỉnh sửa danh sách tùy chọn bằng dấu phẩy

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:

  1. 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.
  2. Nhấp vào Data Validation (Excel ribbon > Data tab).
  3. Xóa hoặc nhập các mục mới trong hộp Source.
  4. Nhấn OK để lưu các thay đổi và đóng cửa sổ Data Validation Excel .

Chỉnh sửa danh sách tùy chọn dựa trên một dải ô

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.

  1. Đ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.
  2. Chọn ô hoặc ô có chứa danh sách tùy chọn của bạn.
  3. Nhấp Data Validation trên tab Data.
  4. Trong cửa sổ Data Validation Excel , trên tab Settings, 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.
  5. Nhấp vào nút OKđể lưu các thay đổi và đóng cửa sổ.

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

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

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.

  1. 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.
  2. Thay đổi tham chiếu đến phạm vi được Đặt tên.
    • Trên ribbon Excel, đi đến tab Formulas > Name Manager. Ngoài ra, nhấn Ctrl + F3để mở cửa sổ Formulas tab > Name Manager.
    • 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 Refers to 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 Close, 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.

Mẹo. Để tránh việc phải cập nhật các trích dẫn của phạm vi được đặt tên sau mỗi lần thay đổi danh sách nguồn, bạn có thể tạo một danh sách tùy chọn Excel động . Trong trường hợp này, danh sách tùy chọn của bạn sẽ được cập nhật tự động trong tất cả các ô liên quan ngay khi bạn xoá hoặc thêm các mục mới vào danh sách.

Làm thế nào để xóa một danh sách tùy chọ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

  1. Chọn một ô hoặc vài ô mà từ đó bạn muốn bỏ các danh sách tùy chọn.
  2. Chuyển đến tab Data và nhấp Data Validation.
  3. Trên tab Cài đặt, chọn nút Clear All.

Phương pháp này loại bỏ các danh sách tùy chọn từ các ô được chọn, nhưng giữ các giá trị hiện đang được chọn.

Nếu bạn muốn xóa cả danh sách tùy chọn và giá trị của các ô, bạn có thể chọn các ô và nhấp vào nút Clear all trên tab Home > Editing > Clear

Xóa một danh sách tùy chọn của Excel từ tất cả các ô trong sheet hiện tại

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ó.

  1. Chọn bất kỳ ô chứa danh sách tùy chọn của bạn.
  2. Nhấp Data Validation trong tab Data.
  3. Trong cửa sổ Data Validation, trên tab Settings, 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.
  4. Nhấp vào nút Clear Allđể xóa danh sách tùy chọn .
  5. Nhấp OKđể lưu các thay đổi và đóng cửa sổ Data Validation.

Phương pháp này xóa một danh sách tùy chọn từ tất cả các ô có chứa nó, giữ lại các giá trị hiện đang được chọn. Nếu bạn tạo một danh sách tùy chọn dựa trên một dãy ô hoặc dựa trên phạm vi được đặt tên, danh sách nguồn cũng sẽ vẫn còn. Để loại bỏ nó, mở bảng tính có chứa các mục của danh sách tùy chọn và xóa chúng.

Ngoài ra để nâng cao hơn kiến thức về Excel, các bạn có thể tham gia khóa học EX101 - Excel từ cơ bản tới nâng cao dành cho người đi làm của hệ thống Học Excel Online. Trong khóa học này các bạn sẽ được tìm hiểu những kiến thức đầy đủ, có hệ thống về các hàm, các công cụ của Excel, việc ứng dụng các hàm, công cụ đó vào trong công việc. Hiện nay hệ thống có rất nhiều ưu đãi cho bạn khi đăng ký khóa học này

Link nội dung: https://diendanxaydung.net.vn/cach-tao-o-lua-chon-trong-excel-a61846.html