Cách sử dụng OFFSET và Data Validation để tạo liên kết List động trong Excel

Ở bài trước chúng ta đã học cách tạo drop-down list có giá trị phụ thuộc một list khác bằng INDIRECT trong Data Validation, trong bài này chúng ta có một lựa chọn khác để tạo liên kết List động trong Excel bằng hàm OFFSET thay cho hàm INDIRECT.

Trước tiên chúng ta hãy tìm hiểu về cách sử dụng hàm OFFSET

Lưu ý: Để thực hiện theo hướng dẫn bài viết các bạn có thể tải file mẫu TẠI ĐÂY

1. Cách sử dụng hàm OFFSET trong Excel

Hàm Offset có công dụng: Trả về tham chiếu đến một vùng nào đó, cách một ô hoặc một dãy ô một khoảng cách với số dòng hoặc số cột được chỉ định trước. Chúng ta có thể chỉ định số dòng, số cột của vùng tham chiếu trả về.

Cú pháp (syntax): =OFFSET(reference, rows, cols, height, width)

 Trong đó:

• reference: đối số bắt buộc, là vùng tham chiếu làm cơ sở cho hàm (làm điểm xuất phát) để tạo vùng tham chiếu mới. reference phải chỉ đến một ô hoặc một dãy ô liên tục, nếu không hàm sẽ trả về lỗi #VALUE!.
• rows:đối số bắt buộc, là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
• cols: đối số bắt buộc, là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (ô ở góc trên bên trái) của reference.
• height: đối số tự chọn, là số dòng của vùng tham chiếu cần trả về. Height phải là số dương.
• width: đối số tự chọn, là số cột của vùng tham chiếu cần trả về. Width phải là số dương.

Lưu ý:

• Nếu rows và cols làm cho tham chiếu trả về vượt ra ngoài phạm vi của một worksheet, hàm Offset sẽ báo lỗi #REF!
• Nếu bỏ qua height và width, thì height và width sẽ có kích thước mặc định là height và width của reference.

Vì Offset chỉ trả về tham chiếu nên chúng ta có thể sử dụng Offset với bất kỳ hàm nào cần đối số là một tham chiếu, dưới đây là một số ví dụ offset kết hợp với các hàm Sum, Match, Index, . . .

- Ví dụ cách dùng hàm OFFSET

Trong hình trên, OFFSET bắt đầu từ địa chỉ ô A3, di chuyển xuống dưới 2 dòng, sang phải 1 cột trả về giá trị trong ô B5

- Sử dụng OFFSET kết hợp hàm SUM

trong hình trên hàm OFFSET bắt đầu từ địa chỉ ô A3 di chuyển xuống 1 dòng, sang phải 2 cột, độ cao vùng dữ liệu (số dòng) là 6, số cột là 1, hàm SUM sẽ tính tổng vùng dữ liệu của OFFSET, kết quả là tổng của vùng dữ liệu từ C4:C9

2. Kết hợp OFFSET trong Data validation để tạo List động

Sau khi đã hiểu rõ cách sử dụng hàm OFFSET, bây giờ chúng ta sẽ sử dụng OFFSET thay cho INDIRECT trong Data validation để tạo List động trong Excel.

Ví dụ cho bảng danh sách 1 loạt hãng xe và loại xe như sau

Trường hợp hãng xe chưa sắp xếp các bạn hãy sắp xếp lại theo hãng (chức năng data -> sort), với bảng dữ liệu trên giờ yêu cầu chúng ta tạo danh sách LIST động, tức là chọn hãng xe nào thì mục loại xe sẽ hiển thị dòng xe của hãng đó.

Bước 1: Các bạn copy cột hãng xe sang 1 sheet khác và sử dụng chức năng Data -> Remove Duplicates để khử các dữ liệu trùng nhau, kết quả ta thu được tên từng hãng là duy nhất. Quét chọn vùng dữ liệu hãng xe, trong vùng Name box đặt tên là tenhangxe như hướng dẫn sau:

Bước 2: để chuột ở ô E4 chọn thẻ Data -> Data validation, trong thẻ settings, mục Allow chọn List, mục Fomular nhập:=tenhangxe.

Sau bước 2, Hãng xe đã có dạng danh sách.

Bước 3: Chọn ô F4 trong thẻ Data chọn Data validation, trong thẻ settings mục Allow chọn List, mục Fomular nhập công thức như sau: =OFFSET($A$3,MATCH(E4,$A:$A,0)-3,1,COUNTIF($A:$A,E4),1)

và nhấn OK, như hướng dẫn ở hình sau:

Giải thích hàm: =OFFSET($A$3,MATCH(E4,$A:$A,0)-3,1,COUNTIF($A:$A,E4),1)

Reference: điểm bắt đầu với tham chiếu là ô A3.

Rows: Sử dụng hàm MATCH để tìm vị trí dòng đầu tiên của tên hãng xe (ô E4) trong danh sách hãng xe ở cột A của sheet, trừ đi 3 cho 2 ô trống A1,A2 và tiêu đề A3.

Cols: Nhập giá trị là "1" để di chuyển qua bên phải 1 cột (trong ví dụ là cột B).

Height: Sử dụng COUNTIF để đếm số lần xuất hiện của tên hãng xe ở cột A, ví dụ như hãng Mercedes trả về 12, tức là vùng dữ liệu sẽ có chiều cao là 12 dòng.

Width: Vùng dữ liệu rộng 1 cột (cột B)

Sau thao tác này ta được kết quả như sau:

Kết thúc bước 3, ta đã hoàn thành việc tạo danh sách LIST động sử dụng OFFSET trong Data validation và thu được kết quả như hình trên. Đây sẽ là một lựa chọn khác để tạo danh sách động ngoài cách sử dụng INDIRECT trong Data validation. Thực tế, nếu dữ liệu lớn thì việc sử dụng indirect để tạo list trong data validation sẽ tốt hơn cho việc xử lý dữ liệu. 

Mời bạn xem video hướng dẫn chi tiết cách sử dụng OFFSET trong Data validation để tạo liên kết danh sách, LIST động trong Excel.