Hàm OFFSET Trong Excel – Cách Áp Dụng Và Ví Dụ Thực Tế

Hàm OFFSET trong Excel là một thành phần quan trọng trong nhóm hàm tham chiếu và tìm kiếm. Bài viết này sẽ trình bày chi tiết về cú pháp, cách sử dụng và những ví dụ minh họa rõ ràng giúp bạn làm chủ hàm OFFSET và ứng dụng nó hiệu quả trong các bảng tính Excel.

Giới Thiệu

Hàm OFFSET trong Excel trả về một tham chiếu đến một phạm vi ô, được xác định bởi vị trí ban đầu và số hàng, cột mà bạn chỉ định. Hàm này giúp di chuyển phạm vi tham chiếu từ một ô hoặc một vùng ô đã cho, theo các bước hàng và cột mà người dùng lựa chọn.

Hàm OFFSET trả về một tham chiếu có thể là một ô đơn lẻ hoặc một dải ô, tùy thuộc vào số lượng hàng và cột bạn chỉ định.

Cú Pháp Của Hàm OFFSET

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

Cấu trúc của hàm OFFSET bao gồm:

• 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ề.
• width 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à width 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à width >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à width 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. Hàm trả về tham chiếu của một ô, bắt đầu từ ô D8, sau đó di chuyển xuống dưới 3 hàng và sang phải 2 cột.

Công thức sử dụng như sau: =OFFSET(D8;3;2)

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

Nếu bạn chỉ sử dụng công thức =OFFSET(D8;2;-1;2;1), hàm sẽ trả về lỗi #VALUE! vì ví dụ này yêu cầu tham chiếu đến hai ô, do đó, bạn cần áp dụng công thức mảng để xử lý chính xác.

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

4. Kết hợp hàm OFFSET với hàm SUM để tính tổng của phạm vi tham chiếu trả về.

Khi tham chiếu trả về là nhiều ô, bạn cần sử dụng công thức mảng như ví dụ trên. Tuy nhiên, khi kết hợp với hàm SUM, bạn không cần phải dùng công thức mảng nữa. Bạn chỉ cần nhập công thức =SUM(OFFSET(D8;2;-1;2;1)).

4. Ví dụ minh họa về việc áp dụng hàm SUM cùng hàm 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))

Khi bạn thêm hoặc xóa hàng trong bảng dữ liệu, công thức hàm OFFSET sẽ tự động điều chỉnh để cập nhật vị trí chính xác, ngay phía trên ô Tổng doanh thu, giúp hàm SUM tính tổng một cách linh hoạt hơn.

Bài viết đã cung cấp cho bạn cú pháp, cách sử dụng và ví dụ về hàm OFFSET trong Excel. Hy vọng rằng bạn đã nắm bắt được cách ứng dụng hàm OFFSET và áp dụng chúng vào các tình huống thực tế. Chúc bạn thành công trong việc sử dụng công cụ hữu ích này!