Hàm OFFSET trong Excel – Cách Sử Dụng

By Lê Quốc Thái

01/05/2026

0

4

Đánh giá lequocthai.com:

Voted: 0 Votes: 0

Your page rank:

OFFSET là một hàm tích hợp sẵn trong Excel, được xếp vào nhóm hàm Tra cứu/Tham chiếu. Mục đích của hàm OFFSET là trả về một tham chiếu tới một ô đơn lẻ hoặc một dải ô, dựa trên số dòng và cột được chỉ định trong các đối số của hàm, tính từ một ô hoặc một dải ô ban đầu.

Cú pháp

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

Các đối số:

reference – Đây là đối số bắt buộc, nơi bạn nhập phạm vi bắt đầu từ đó muốn áp dụng offset.
rows – Đối số bắt buộc chỉ định số dòng di chuyển lên hoặc xuống so với ô/phạm vi trong `reference`. Giá trị có thể dương hoặc âm. Giá trị dương dịch xuống, giá trị âm dịch lên. Nếu nhập giá trị lớn hơn số dòng khả dụng, hàm sẽ trả về lỗi `#REF!`.
cols – Đối số bắt buộc tương tự `rows`. Giá trị dương dịch sang phải, giá trị âm dịch sang trái. Nếu vượt quá số cột khả dụng, hàm sẽ trả về lỗi `#REF!`.
height – Đối số tùy chọn, cho phép bạn xác định số dòng của dải ô trả về. Nếu bỏ qua, mặc định là 1 dòng.
width – Đối số tùy chọn, cho phép bạn xác định số cột của dải ô trả về. Nếu bỏ qua, mặc định là 1 cột.

Khám phá: Hướng dẫn sử dụng hàm CHOOSE trong Excel

Đặc điểm quan trọng của hàm OFFSET

Hàm OFFSET trả về một tham chiếu mà không thực sự di chuyển các ô hay dải ô.
Hàm OFFSET có thể được lồng vào các hàm Excel khác, miễn là các hàm đó chấp nhận một tham chiếu ô hoặc dải ô.
Các dòng và cột trong dải ô trả về luôn dựa vào ô nằm 1 dòng lên trên và 1 cột bên trái của dải ô kết quả để làm tham chiếu.
Công thức OFFSET sẽ trả về lỗi `#VALUE!` khi đối số `reference` không chứa ô hoặc dải ô liên tiếp.
Nếu các dòng và cột được chỉ định trong OFFSET vượt ra ngoài phạm vi bảng tính, hàm sẽ trả về lỗi `#REF!`.

Ví dụ về hàm OFFSET

Hãy cùng xem một số ví dụ để hiểu rõ hơn khi thực hiện hàm OFFSET. Nếu muốn, bạn có thể thực hành ngay trên một bảng tính để nắm vững.

Ví dụ cơ bản cho hàm OFFSET

=OFFSET(B2,4,0)

Nhập ô B2 làm `reference`, 4 cho `rows`, và 0 cho `cols`. Công thức này yêu cầu Excel trả về giá trị ở ô cách B2 4 dòng xuống và cùng cột. Kết quả là ô B6 có giá trị “250”.

Khi ô trả về không có giá trị

=OFFSET(C7,1,-2)

OFFSET di chuyển từ C7 xuống 1 dòng và sang trái 2 cột, sẽ dẫn tới một ô trống, vì vậy hàm trả về 0.

Sử dụng ô làm đối số cho `rows` và `cols`

Khám phá: Cách Sắp Xếp Theo Thứ Tự Chữ Cái trong Excel – Hướng Dẫn Toàn Diện

=OFFSET(B2,A3,0)

Nếu ô A3 chứa giá trị 2, OFFSET sẽ trả về ô cách B2 2 dòng xuống – trong ví dụ là ô B4 với giá trị “150”.

Áp dụng các đối số tùy chọn `height` và `width`

=OFFSET(A1,1,1,3,1)

Ở đây chúng ta lấy dải ô bắt đầu từ A1, di chuyển 1 dòng xuống, 1 cột sang phải, với chiều cao 3 dòng và chiều rộng 1 cột, kết quả là dải B2:B4. Trong một số phiên bản Excel cũ có thể gặp lỗi `#VALUE!`; để tránh lỗi, nhấn Ctrl + Shift + Enter để nhập công thức mảng.

Lỗi `#REF!`

=OFFSET(B2,-3,-3)

Yêu cầu Excel trả về ô nằm 3 dòng lên và 3 cột sang trái của B2 – vị trí này không tồn tại, vì vậy trả về lỗi `#REF!`.

Kết hợp OFFSET với hàm SUM

=SUM((OFFSET(A1,1,1,5,1)))

OFFSET tạo ra dải B2:B6; hàm SUM cộng các giá trị 50, 100, 150, 200, 250, cho kết quả 750.

Kết hợp OFFSET với AVERAGE, MIN & MAX

Giả sử bạn muốn tính trung bình chi phí của 3 tháng gần nhất.

=AVERAGE(OFFSET(B1,COUNT(B:B)-E4+1,0,E4,1))

Trong đó `COUNT(B:B)` đếm số ô có dữ liệu số trong cột B (giả sử =5). Nếu E4 = 3, thì `COUNT(B:B)-E4+1` = 3, OFFSET sẽ trả về dải B4:B6 (có các giá trị 150, 200, 250). Hàm AVERAGE cho kết quả 200.

Để lấy giá trị MAX hoặc MIN của 3 tháng vừa qua, thay hàm AVERAGE bằng `MAX` hoặc `MIN`:

=MAX(OFFSET(B1,COUNT(B:B)-E4+1,0,E4,1))

hoặc

=MIN(OFFSET(B1,COUNT(B:B)-E4+1,0,E4,1))

Kết hợp OFFSET với hàm VLOOKUP

VLOOKUP không thể tra cứu sang phía trái. Kết hợp OFFSET, MATCH và ROWS sẽ giúp thực hiện tra cứu sang trái:

=OFFSET(table_range,
MATCH(row_lookup_value,
OFFSET(table_range,0,col_lookup_offset, ROWS(table_range),1),
0)-1,
col_return_offset,
1,1)

table_range: phạm vi bảng dữ liệu.
row_lookup_value: giá trị cần tìm.
col_lookup_offset: số cột giữa cột tra cứu và cột bắt đầu.
col_return_offset: số cột giữa cột trả về và cột bắt đầu.

Khám phá: Cách sắp xếp theo thứ tự A‑Z trong Excel – Hướng dẫn chi tiết

Ví dụ thực tế (xem hình):

Công thức trên sẽ trả về giá trị 200, là giá trị nằm bên trái của hàng có `row_lookup_value` = 4.

Những hạn chế của hàm OFFSET

Tiêu tốn tài nguyên – Khi bảng tính có nhiều công thức, việc tính lại OFFSET có thể làm Excel chậm lại.
Khó gỡ lỗi – Các công thức OFFSET lồng nhau tạo ra tham chiếu động phức tạp, gây khó khăn trong việc kiểm tra và sửa lỗi.

Các lựa chọn thay thế cho OFFSET

INDEX – Hàm không gây tải nặng (non‑volatile) và có khả năng tạo dải tham chiếu động.
INDIRECT – Hàm động (dynamic) cho phép tham chiếu tới các sheet hoặc workbook khác, và có thể xây dựng dải tham chiếu dựa trên tên vùng, giá trị ô, hoặc chuỗi văn bản.