Bạn đang xem bài viết: Hướng dẫn sử dụng hàm OFFSET trong Excel đơn giản nhất 2022
Nội dung bài viết
Hàm OFFSET trong Excel sử dụng để làm gì?
Hàm OFFSET dùng để làm gì? Hàm này được sử dụng trong hoàn cảnh muốn được xem hoặc tính toán, phân tích dữ liệu của một ô hay một dãy trong bảng tính dựa vào một vùng tham chiếu đã có sẵn trước đó.
Cú pháp hàm OFFSET trong Excel
Cú pháp hàm OFFSET: =OFFSET(reference, rows, cols,[height],[width])
Trong đó:
- Reference: Là vùng tham chiếu làm cơ sở cho hàm để tạo vùng tham chiếu mới.
- Rows: Là số dòng bên trên hoặc bên dưới reference, tính từ ô trước 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 reference, tính từ ô trước 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ề. Height phải là số dương.
- Width: Là số cột của vùng tham chiếu cần trả về. Width phải là số dương.
Lưu ý khi sử dụng hàm OFFSET trong Excel
- nếu như các đối số rows và cols khiến cho vùng tham chiếu trả về vượt khỏi phạm vi của trang tính thì hàm sẽ báo lỗi #REF.
- nếu bỏ qua các đối số height và width thì tham chiếu trả về mặc định có cùng chiều cao và độ rộng với vùng tham chiếu.
- nếu các đối số height và width >1 nghĩa là hàm phải trả về nhiều hơn 1 ô thì các bạn phải dùng công thức mảng hàm OFFSET nếu không sẽ nhận lỗi #VALUE!.
- Đối số height và width phải là số dương.
- Hàm OFFSET không di chuyển hay làm chỉnh sửa bất kỳ phần nào được chọn vì hàm chỉ trả về một tham chiếu. Các bạn có thể trải nghiệm hàm OFFSET Kết hợp với các hàm cần đến một đối số tham chiếu.
Cách dùng hàm OFFSET trong Excel
VD 1: Hãy dùng hàm OFFSET để lấy ra thành quả ô ở vị trí cột 2 dòng 3 trong bảng sau.
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ả.
VD minh họa hàm OFFSET.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Nhấn Enter để hiển thị kết quả.
VD 2: Hãy dùng hàm OFFSET và hàm SUM để tính tổng của cột D ở bảng dưới đây.
Nếu bạn muốn hiểu rõ về cách sử dụng hàm SUM trong Excel trước khi vào các bước rèn luyện bên dưới thì chúng ta có thể đọc thêm bài viết Tại đây!!
Bước 1: Trong bảng dữ liệu tính Excel, bạn nhập hàm =SUM(OFFSET(A3,1,3,5,1)) vào ô tham chiếu mong muốn hiển thị kết quả.
trình bày hàm:
- SUM và OFFSET: Là lệnh hàm.
- A3: Là ô tham chiếu.
- 1: Là số hàng di chuyển xuống bắt đầu từ ô tham chiếu A3.
- 3: Là số cột được di chuyển sang phía phải bắt đầu từ ô tham chiếu A3.
- 5: Là tổng số hàng thu thập dữ liệu tính kết quả.
- 1: Là tổng số cột lấy dữ liệu tính kết quả.
ví dụ minh họa hàm OFFSET tích hợp hàm SUM.
Bước 2: Nhấn phím Enter để hiển thị kết quả.
Nhấn Enter để hiển thị 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 điền hàm =SUM(B2:OFFSET(B9, -1,0)) vào ô muốn hiển thị kết quả để tính tổng thành quả 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 trong tin học văn phòng
Bước 1: Để tính thành quả 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 điền =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ả.
Tổng kết
Trên đây chính là cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu chi tiết, có VD dễ hiểu. Blog Phần Mềm hy vọng bài content sẽ giúp bạn thực hiện được hàm OFFSET trong hoạt động cũng như học tập. Cảm ơn bạn đã xem bài viết.
