Hàm OFFSET và các ứng dụng

1.  Hàm OFFSET 

Hàm OFFSET trả tham chiếu đến một vùng hoặc 1 ô, được chỉ định bằng số dòng, số cột cách ô hoặc một vùng đã định trước.

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

Trong đó:

• reference: là ô/vùng tham chiếu  (điểm xuất phát)
• rows: là số dòng đi lên trên hoặc đi xuống dưới từ reference. Khi rows >0 thì các dòng trả về nằm bên dưới reference, khi rows <0 thì các dòng trả về nằm bên trên reference.
• cols: 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. Khi cols>0 thì các cột trả về nằm bên phải reference, khi cols <0 thì các cột trả về nằm bên trái reference.
• height và width: tương ứng là số dòng, số cột của vùng tham chiếu cần trả về. Height phải là số dương.

Để dễ hiểu hơn cách hoạt động của hàm OFFSET hãy xem ví dụ ở dưới (download sample file để theo dõi dễ hơn) .

Mở sheet “Offset Basic”, giả sử ta đang ở ô B3, chúng ta cần tham chiếu xuống vùng bôi vàng để tính tổng vùng này.

Tại ô bất kỳ ta gõ câu lệnh =SUM(OFFSET(B3,3,2,3,2)). Hàm OFFSET bắt đầu từ ô B3, di chuyển xuống phía dưới 3 dòng, sang phải 2 cột, lúc này ta đang ở ô D6,  câu lệnh sẽ tạo một vùng có height  = 3 và width = 2 là vùng bôi vàng ở hình trên. Tổng của vùng này sẽ bằng 39.

Nếu bạn chỉ để câu lệnh OFFSET(B3,3,2,3,2) thì sẽ báo lỗi #VALUE vì giá trị tham chiếu đến là 1 vùng. Hàm OFFSET thường được sử dụng kết hợp với các hàm khác khi đối số là tham chiếu.

2. Một số ứng dụng

Qua phần trên có lẽ các bạn chưa thấy hết được sự thú vị của hàm này, hãy cùng xem các ví dụ tiếp theo để biết hàm OFFSET có thể làm được những gì

Ví dụ 1: Tạo Dependent list

Ở bài Dependent List chúng ta đã tìm hiểu cách tạo 1 Validation list mà nội dung của nó thay đổi theo 1 list khác, bài này sẽ cung cấp 1 cách làm khác. Trong file Sample mở sheet “Dependent list, ta có danh sách món ăn như hình dưới, cần tạo ra 2 drop-down list sao cho khi chọn Food từ 1 list thì thì list kia sẽ hiển thị các Dish tương ứng.

Tại B4, tạo drop-down list với các giá trị cho “Food” là Pizza, Pancakes, Chinese (cách tạo Drop-down list)

Tại ô C4, tạo drop-down list với source là =OFFSET($C$7,MATCH($B$4,$B$8:$B$16,0),0,COUNTIF($B$8:$B$16,$B$4),1)

Ấn Ok và ta đã có được 2 drop-down list như đúng yêu cầu. Để hiểu vì sao hàm này hoạt động, ví dụ  B4 là Chinese, khi đó MATCH($B$4,$B$8:$B$16,0) (= 7) sẽ chỉ ra số dòng cần di chuyển để đến ô có món ăn Chinese đầu tiên, COUNTIF($B$8:$B$16,$B$4) (= 3) chỉ ra số món ăn nằm trong mục Chinese. Điều này đồng nghĩa là công thức sẽ trả ra các giá trị Dish tương ứng với Food là Chinese.

Ví dụ 2: Tạo dynamic named range – tên vùng tự động thêm các dữ liệu mới

Khi đặt tên cho 1 vùng dữ liệu, nếu phải thêm/bớt data,chúng ta sẽ phải update lại nguồn data cho tên đó . Điều này có thể khắc phục khi sử dụng Dynamic named range, tên vùng sẽ tự động update dữ liệu mới. Dưới đây là 1 VD

Trong sheet “Dynamic range”, chúng ta có bảng dữ liệu về số năm kinh nghiệm của các nhân viên như hình dưới. Chúng ta sẽ đặt tên vùng sao cho khi có dữ liệu mới được thêm vào, tên vùng cũng tự động cập nhật các dữ liệu đó

Chọn tab Formula, trong ô Defined Names chọn Define Name (hoặc đơn giản nhấn tổ hợp phím Alt + M + M + D) .Đặt tên vùng này là Data, tại phần “Refer to” gõ câu lệnh =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)).

Câu lệnh này bắt đầu từ ô A1, 2 câu lệnh COUNTA($A:$A),COUNTA($1:$1) sẽ đếm số dòng, số cột không trống, khi chúng ta thêm cột, dòng mới, 2 giá trị  COUNTA($A:$A),COUNTA($1:$1) cũng sẽ tăng lên, kết quả là vùng “Data” sẽ được mở rộng.

Click Ok và chúng ta đã có 1 dynamic range, đặt công thức tại ô F4 là =sum(data), khi thêm dòng vào dữ liệu chúng ta sẽ thấy ô F4 sẽ tự động tính theo vùng mới được tạo. Nếu tạo Pivot table từ vùng dữ liệu này, khi có thêm dữ liệu mới, chúng ta chỉ cần refresh là sẽ có bảng Pivot table update (xem trong file sample)

Ví dụ 3: Tạo dynamic chart

Vẫn cùng tư tưởng như ở ví dụ trên, trong sheet “Dynamic chart” ta có bảng doanh số qua các tháng của 1 công ty như hình dưới và một biểu đồ ở bên cạnh.  Điều chúng ta cần làm là tạo ra 1 Excel chart tự động update khi các dòng dữ liệu được thêm vào/bớt đi.

Để làm điều này, chúng ta sẽ tạo 2 Dynamic named range là Month và Units.

Month: =OFFSET(‘Dynamic Chart’!$B$3,0,0,COUNT(‘Dynamic Chart’!$B:$B),1)

Units:=OFFSET(‘Dynamic Chart’!$C$3,0,0,COUNT(‘Dynamic Chart’!$C:$C),1)

Ở trên Tab Insert, phần Charts, chọn 1 loại Chart bất kỳ, ở bài sẽ chọn dạng Clustered Column 2D, click chuột phải chọn Select Data, sẽ hiện ra bảng chọn như sau:

Click Add ở “Legend Entries”,  đặt  trục tung của biểu đồ theo giá trị của vùng Units

Click Ok, chọn tiếp Edit ở Horizontal Axis Labels, đặt trục hoành theo giá trị của vùng Months

Click Ok. Và bây giờ hãy thử thêm, xóa 1 số dòng, bạn sẽ thấy chart tự động update theo giá trị bạn đã thay đổi.

Ngoài các ví dụ ở trên, các bạn có thể xem thêm 1 số ví dụ trong file Sample đính kèm, hoặc các nguồn khác để hiểu thêm về hàm OFFSET và các ứng dụng của nó