Microsoft Excel mang lại cho ta rất nhiều tính năng để tính toán trong nhiều lĩnh vực khác nhau. Trong đó việc trả về tham chiếu với một phạm vi có hai cách là sử dụng hàm Indirect và hàm Offset. Trong chủ đề ngày hôm nay cùng Mindovermetal tìm hiểu rõ hơn về hàm Offset là gì trong Excel.
Mục lục nội dung
Hàm Offset là gì?
Hàm Offset là hàm được sử dụng trong trường hợp muốn 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 của 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ừ ô đầu tiên của reference.
- Cols: là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên 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ề.
Lưu ý:
- Height và Width phải là số dương.
- Nếu rows và cols làm cho vùng tham chiếu vượt ra khỏi phạm vi của bảng tính. Hàm OFFSET() sẽ báo lỗi #REF!.
- Nếu đối số chiều cao và độ rộng được bỏ qua, thì nó được giả định có cùng chiều cao và độ rộng với vùng tham chiếu.
- Reference phải chỉ đến một ô hoặc một dãy ô liên tục. Nếu không hàm sẽ trả về lỗi #VALUE!.
- Hàng có thể là số dương (bên dưới) hoặc số âm (bên trên).
- Cột có thể là số dương (bên phải vùng tham chiếu bắt đầu) hoặc số âm (bên trái vùng tham chiếu bắt đầu).
Cách áp dụng hàm OFFSET trong Excel
Cùng theo dõi dữ liệu từ ô tính sau:
Tại ô D6, trả về giá trị của ô F4 thông qua ô tham chiếu cơ sở D3. Ta dùng công thức: =OFFSET(D3;1;2;1;1).
Có nghĩa là Offset phải đi lấy giá trị trong ô F4, Offset phải đi xuống 1 dòng, phải đi sang bên phải 2 cột và chỉ lấy giá trị của một hình vuông 1×1.
Kết quả thu được như sau:
Kết hợp với các hàm khác trong Excel
Hàm Offset có thể kết hợp với các hàm khác trong Excel để giúp giải quyết số liệu một cách nhanh chóng và tiện lợi. Bảng tính sau kết hợp hàm Offset với hàm Sum trong Excel.
Offset đang đứng ở ô C6 và muốn tính tổng tiền hoa hồng của nhân viên cuối cùng là Tịnh. Ta có công thức sau =SUM(OFFSET(C6,2,0,1,3)) .
Kết quả như hình bên dưới:
Lỗi #REF trong Offset
Nếu như bạn lấy giá trị vượt quá phạm vi đường biên của trang tính sẽ xảy ra lỗi #REF như hình minh họa.
Trong hình sẽ thấy rõ rằng Offset đang đứng ở ô C6 mà ta gõ lên trên 4 ô, và sang trái 4 ô, từ đó kết quả trả về sẽ bị lỗi.
Bài viết này đã định nghĩa về hàm OFFSET là gì và cách sử dụng hàm thông qua các ví dụ dễ hiểu. Bạn đọc nên chú ý vào mục lưu ý nhằm tránh trường hợp mắc phải các lỗi cơ bản trong hàm Offset. Nếu muốn biết thêm về những công thức Excel thì hãy theo dõi Mindovermetal!
