Hàm OFFSET Trong Excel: Cách Sử Dụng để Tham Chiếu Dữ Liệu ...

Hàm OFFSET trong Excel 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. Sau đây bài viết sẽ hướng dẫn sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu của bảng tính nhé!

Bài hướng dẫn được thực hiện trên laptop Dell với phiên bản Microsoft Excel 365, ngoài ra bạn cũng có thể thực hiện trên các phiên bản Excel 2003, 2007, 2010, 2013, 2016, Excel 2020 với thao tác tương tự.

1Hà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.

2Cô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).

3Nhữ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é!

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

Bạn có thể truy cập vào đây để tải về các file ví dụ bên dưới.

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ả.

5Hà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ả.

Xem thêm:

• Excel là gì? Tầm quan trọng của Excel trong công việc và học tập
• Cách dùng hàm SUM trong Excel để tính tổng
• Hàm VLOOKUP trong Excel: Cách sử dụng hàm VLOOKUP, có ví dụ cụ thể 

Trên đây là cách dùng hàm Offset trong Excel để tham chiếu dữ liệu của bảng tính. Nếu bạn còn bất kỳ thắc nào cần được giải đáp, bạn vui lòng để lại bình luận bên dưới để Điện máy XANH hỗ trợ bạn nhé!