Hàm Offset trong Excel – Cách dùng và ví dụ

Hàm OFFSET trong Excel là một hàm trong nhóm hàm tham chiếu và tìm kiếm, bài viết dưới đây sẽ mô tả cú pháp, cách dùng và ví dụ hàm OFFSET trong Excel, mời các bạn cùng tham khảo để hiểu rõ hơn về hàm OFFSET nhé.

Mô tả

Hàm Offset trả về tham chiếu tới một phạm vi cách một ô hoặc phạm vi ô một số hàng và một số cột đã xác định. Có nghĩa là hàm Offset trả về tham chiếu tới một phạm vi, dựa vào vị trí ô hoặc phạm vi ô bạn chỉ định làm mốc và di chuyển theo số hàng và số cột mà bạn chỉ định.

Tham chiếu mà hàm Offset trả về có thể là một ô đơn hoặc một phạm vi ô mà bạn chỉ rõ số hàng và số cột cần trả về.

Cú pháp

=OFFSET(reference; rows; cols; [height]; [awidth])

Trong đó:

• reference là đối số bắt buộc, là vùng tham chiếu mà bạn muốn đây làm điểm khởi đầu (làm mốc) sau đó dựa vào các đối số khác để di chuyển tới tham chiếu trả về.
• rows là đối số bắt buộc, là số hàng mà bạn muốn di chuyển lên trên hoặc xuống dưới reference. Nếu rows là số dương thì hàm sẽ di chuyển xuống phía dưới reference, nếu rows là số âm thì di chuyển lên trên reference.
• cols là đối số bắt buộc, là số cột mà bạn muốn di chuyển sang trái hoặc sang phải reference sau khi hàm đã di chuyển theo rows. Nếu cols là số dương thì hàm sẽ di chuyển sang phải số ô bằng số cols, nếu số cols là số âm thì hàm sẽ di chuyển sang trái số ô bằng số cols.
• height là đối số tùy chọn, đây là chiều cao được tính bằng số hàng mà các bạn muốn cho tham chiếu trả về.
• awidth là đối số tùy chọn, đây là chiều rộng được tính bằng số cột mà các bạn muốn cho tham chiếu trả về.

Lưu ý

• Nếu đối số rows và cols làm cho vùng tham chiếu trả về vượt qua ngoài đường biên trang tính thì hàm sẽ trả về giá trị lỗi #REF.
• Nếu bỏ qua đối số height và awidth trong hàm OFFSET thì tham chiếu trả về mặc định có cùng chiều cao và độ rộng với vùng tham chiếu.
• Nếu height và awidth >1 nghĩa là hàm phải trả về nhiều hơn 1 ô thì các bạn phải sử dụng công thức mảng hàm OFFSET nếu không sẽ nhận lỗi #VALUE!
• Đối số height và awidth phải là số dương.
• Hàm OFFSET thực chất không di chuyển bất kỳ ô nào hoặc thay đổi vùng chọn, nó chỉ trả về một tham chiếu.

Ví dụ

1. Trả về tham chiếu của 1 ô bắt đầu từ ô D8, di chuyển xuống 3 hàng sang phải 2 cột.

Sử dụng công thức hàm như sau: =OFFSET(D8;3;2)

2. Trả về tham chiếu của 2 ô C10:C11 bắt đầu từ ô D8, di chuyển xuống 2 hàng, sang trái 1 cột.

Nếu các bạn chỉ viết công thức hàm OFFSET là =OFFSET(D8;2;-1;2;1) thì hàm sẽ trả về lỗi #VALUE! do ví dụ này trả về tham chiếu của 2 ô nên các bạn cần áp dụng công thức mảng.

Vì tham chiếu trả về là 2 ô C10:C11 nên các bạn chọn hai ô liền kề bất kỳ trong cột, sau đó nhập hàm =OFFSET(D8;2;-1;2;1) nhập xong các bạn nhấn tổ hợp Ctrl + Shift + Enter để chuyển thành công thức mảng. Các bạn sẽ được kết quả như sau:

4. Sử dụng hàm OFFSET kết hợp hàm SUM để tính tổng phạm vi được trả về.

Nếu trả về tham chiếu là nhiều ô thì các bạn cần thực hiện viết công thức mảng như ví dụ trên, nhưng nếu kết hợp với hàm SUM thì các bạn không cần sử dụng công thức mảng nữa. Các bạn nhập hàm =SUM(OFFSET(D8;2;-1;2;1))

4. Áp dụng một ví dụ cụ thể sử dụng hàm SUM kết hợp OFFSET

Khi các dữ liệu luôn thay đổi thì việc áp dụng hàm OFFSET là việc làm rất hữu ích thay vì mỗi khi thay đổi lại phải thay đổi lại công thức hàm SUM.

Giả sử các bạn có dữ liệu như hình dưới.

Mỗi ngày các bạn lại có thêm dữ liệu mới, nên các bạn phải thêm hàng mới vào bảng dữ liệu.

Các bạn lại không muốn mỗi lần thêm dữ liệu lại phải thay đổi công thức hàm SUM tính tổng. Các bạn hãy áp dụng thêm hàm OFFSET để tự động thay đổi khi thêm dữ liệu mới. Trong ô tính tổng các bạn sử dụng công thức

=SUM(D7:OFFSET(D14;-1;0))

Hàm SUM sẽ tính tổng từ ô đầu tiên trong cột Doanh thu đó là ô D7 đến tham chiếu trả về của hàm OFFSET. Hàm OFFSET trả về tham chiếu là vị trí trên ô Tổng doanh thu 1 hàng, chính là vị trí D13 như hình.

Như vậy khi các bạn thêm mới hàng hay xóa hàng trong bảng dữ liệu thì công thức hàm OFFSET sẽ tự cập nhật vị trí tương ứng là phía ngay trên ô Tổng doanh thu, và hàm SUM sẽ tính tổng linh hoạt hơn.

Trên đây bài viết đã giới thiệu đến các bạn cú pháp, cách sử dụng và ví dụ về hàm OFFSET trong Excel. Hi vọng các bạn sẽ hiểu rõ hơn về cách sử dụng hàm OFFSET và cách áp dụng hàm OFFSET vào những trường hợp cụ thể. Chúc các bạn thành công!