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é!
1. 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.
2. Công thức của hàm OFFSET
Cú pháp:
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).
3. 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é!
4. 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ả.
5. Hàm OFFSET kết hợp với những hàm khác
5.1. 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ả.
5.2. 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ả.
5.3. 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à 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 để Admin Edu hỗ trợ bạn nhé!
