Buzz
Hàm OFFSET trong Excel thuộc nhóm hàm tham chiếu và tìm kiếm. Trong bài viết này, chúng tôi sẽ giới thiệu về cú pháp, cách sử dụng và minh họa ví dụ về hàm OFFSET trong Excel. Hãy tham khảo để có cái nhìn rõ ràng về việc sử dụng Hàm OFFSET.
Mô tả chi tiết
Hàm Offset trả về tham chiếu đến một phạm vi cách một ô hoặc phạm vi ô với số hàng và số cột được xác định. Điều này có nghĩa là hàm Offset trả về tham chiếu đến một phạm vi, dựa vào vị trí ô hoặc phạm vi ô bạn chỉ định làm điểm mốc và di chuyển theo số hàng và số cột bạn chỉ định.
Kết quả trả về từ hàm Offset có thể là một ô đơn hoặc một phạm vi ô, chỉ cần xác định số hàng và số cột bạn muốn nhận.
Cú pháp
=OFFSET(tham_chiếu; hàng; cột; [chiều_cao]; [chiều_rộng])
Trong đó:
- Tham chiếu là tham số quan trọng, là vùng xuất phát mà bạn sử dụng làm điểm khởi đầu cho hàm Offset, sau đó di chuyển dựa vào các tham số khác để xác định tham chiếu trả về.
- Cột là tham số quan trọng, xác định số cột bạn muốn di chuyển sang trái hoặc sang phải so với tham chiếu sau khi đã di chuyển theo hàng. Nếu là số dương, hàm sẽ di chuyển sang phải số ô tương ứng, nếu là số âm, hàm sẽ di chuyển sang trái.
1. Lấy tham chiếu của ô bắt đầu từ D8, di chuyển xuống 3 hàng và sang phải 2 cột.
Sử dụng công thức hàm: =OFFSET(D8;3;2)
2. Lấy tham chiếu của 2 ô từ C10 đến C11, bắt đầu từ ô D8, di chuyển xuống 2 hàng và sang trái 1 cột.
Nếu chỉ sử dụng công thức hàm OFFSET là =OFFSET(D8;2;-1;2;1), hàm sẽ trả về lỗi #VALUE! do ví dụ này trả về tham chiếu của 2 ô, cần áp dụng công thức mảng.
Với tham chiếu trả về là 2 ô C10:C11, chọn hai ô liền kề bất kỳ trong cột. Nhập hàm =OFFSET(D8;2;-1;2;1). Sau đó, nhấn tổ hợp Ctrl + Shift + Enter để chuyển thành công thức mảng. Kết quả sẽ là:
4. Sử dụng hàm OFFSET kết hợp với hàm SUM để tính tổng phạm vi trả về.
Nếu trả về tham chiếu là nhiều ô, bạn cần viết công thức mảng như ví dụ trên. Tuy nhiên, nếu kết hợp với hàm SUM, không cần sử dụng công thức mảng. 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 với OFFSET.
Khi dữ liệu thay đổi liên tục, áp dụng hàm OFFSET là quyết định thông minh thay vì phải điều chỉnh công thức hàm SUM mỗi khi có thay đổi.
Giả sử bạn đang có bảng dữ liệu như dưới đây.
Hàng ngày, dữ liệu mới được thêm vào, vì vậy bạn cần thêm hàng mới vào bảng.
Để không phải thay đổi công thức hàm SUM mỗi khi thêm dữ liệu mới, sử dụng hàm OFFSET để tự động điều chỉnh. Trong ô tính tổng, nhập công thức:
Tổng hợp: =SUM(D7:OFFSET(D14;-1;0))
Khi thêm hoặc xóa hàng, hàm OFFSET sẽ tự cập nhật vị trí trên ô Tổng doanh thu, giúp hàm SUM tính tổng linh hoạt.
Dưới đây là cú pháp, cách sử dụng và ví dụ về hàm OFFSET trong Excel. Hi vọng bạn nắm rõ cách áp dụng hàm OFFSET vào những tình huống cụ thể. Chúc bạn thành công!
