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ả.
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é!
Siêu thị Điện máy XANH