Tạo danh sách tùy chọn trong Excel dựa trên một dải ô và bảng tính

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Tạo danh sách tùy chọn trong Excel dựa trên một dải ô và bảng tính

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Danh sách tùy chọn trong 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.

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

Việc tạo ra một danh sách tùy chọn trong Excel 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 Dialogbê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 trong Excel dựa trên một dải ô

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

1.1. 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 trong Excel, 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 đó:

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

Ư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!

1.2. Chi tiết về OFFSET trong việc tạo danh sách tùy chọn trong Excel

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])

  • 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:

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

2. Tạo một danh sách tùy chọn trong Excel 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.

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

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

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

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

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

2.3. Á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.

Chúc bạn thực hiện thành công!

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ