Cách dùng hàm Offset trong Excel để tham chiếu dữ liệu

Cách dùng hàm Offset trong Excel để tham chiếu dữ liệu mà chúng tôi muốn chia sẻ đến các bạn thường được sử dụng để tính toán dữ liệu một ô, một dãy trong bảng tính dựa vào vùng tham chiếu được cho sẵn. Ngoài ra, bạn cũng có thể kết hợp hàm này với các hàm khác để hỗ trợ cho công việc của mình hiệu quả hơn. Hãy cùng theheso tham khảo cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu của bảng tính qua bài viết dưới đây nhé.

Hàm OFFSET là gì?

Hàm OFFSET là hàm được dùng để tính toán dữ liệu một ô, một dãy trong bảng tính dựa vào một vùng tham chiếu được cho sẵn.

Hàm OFFSET được ứng dụng trong Excel để:

• Tính toán dữ liệu của một ô hay một dãy trong bảng tính nhanh hơn, tiện lợi hơn.
• Ngoài ra, hàm này còn được sử dụng kết hợp với các hàm khác để hỗ trợ hoàn thành công việc nhanh hơn.

Công thức của hàm OFFSET

Cú pháp: =OFFSET(reference, rows, cols,[height],[width])
Trong đó:

• Reference: Được lấy làm vùng cơ sở tham chiếu để tạo ra vùng tham chiếu mới.
• Rows: Số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên ở góc trên bên trái của reference.
• Cols: Là số cột bên trái hoặc bên phải của reference, tính từ ô đầu tiên ở góc trên bên trái của reference.
• Height: Là số dòng của vùng tham chiếu cần trả về.
• Width: Là số cột của vùng tham chiếu cần trả về (Width phải là số dương).

Những điều lưu ý khi sử dụng hàm OFFSET

• Khi trang tính báo về lỗi #REF tức là đối số rows và cols của bạn đã khiến cho vùng tham chiếu được trả về vượt quá phạm vi quy định của trang tính.
• Nếu bạn không nhập đối số height và width thì kết quả tham chiếu trả về sẽ được mặc định có chiều cao và độ rộng giống với vùng tham chiếu.
• Còn nếu đối số height và width của bạn lớn hơn 1 thì bạn phải sử dụng công thức mảng hàm Offset nếu không thì trang tính sẽ báo lỗi #VALUE!. Đồng thời, bạn nên lưu ý về đối số height và width đều phải là số không âm nhé.

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

Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =OFFSET(B3,3,2) vào ô tham chiếu muốn hiển thị kết quả.

Bước 2: Nhấn phím Enter để xem kết quả.

Hàm OFFSET kết hợp với những hàm khác

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

Bước 1: Trong bảng tính Excel, bạn nhập hàm =SUM(B2:OFFSET(B9, -1,0))​ vào ô muốn hiển thị kết quả để tính tổng giá trị thưởng ở cột B.

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN

Bạn có thể lồng các hàm AVERAGE, MAX, MIN cùng với hàm OFFSET

Bước 1: Để tính giá trị trung bình trong bảng, bạn hãy nhập hàm =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Bước 3: Để tính số lớn nhất trong dải, bạn nhập =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Bước 4: Nhấn phím Enter để hiển thị kết quả.

Bước 5: Để tìm số nhỏ nhất trong dải, bạn nhập =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).

Bước 6: Nhấn phím Enterđể hiển thị kết quả.

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

Bên cạnh đó, chúng ta có thể kết hợp hàm VLOOKUP với hàm OFFSET.

Bước 1: Bạn hãy nhập hàm =VLOOKUP(B1,A5:B11,2,FALSE) vào ô tham chiếu muốn hiển thị kết quả.

Bước 2: Nhấn phím Enter để hiển thị kết quả.

Trên đây là bài viết hướng dẫn cách dùng hàm Offset trong Excel để tham chiếu dữ liệu của bảng tính mà chúng tôi muốn giới thiệu đến các bạn. Hãy chia sẻ những kinh nghiệm của bạn cho mọi người cùng biết nhé. Hi vọng bài viết sẽ hữu ích với bạn. Chúc các bạn thành công.