Hàm OFFSET trong Excel được sử dụng để tính toán một ô hoặc 1 phạm vi dữ liệu được xác định. Bài viết này sẽ giúp bạn hiểu rõ về hàm OFFSET và cách kết hợp với các điều kiện trong Excel để tối ưu công việc.
Hàm OFFSET trong Excel để làm gì?
Hàm OFFSET trong Excel được sử dụng để trả về tham chiếu 1 ô hoặc phạm vi ô, dựa trên ô xuất phát và các tham số về số hàng, số cột và cũng như là chiều dài, chiều rộng. Hàm này đặc biệt hữu ích khi bạn muốn trích xuất dữ liệu từ các vị trí khác nhau mà không cần thay đổi công thức.
Hàm OFFSET trong Excel để làm gì?
Công thức hàm OFFSET
Công thức OFFSET trong Excel
Cú pháp: = OFFSET(reference, rows, cols, [height], [width])
Ý nghĩa các tham số
-
reference: Là vùng cơ sở để tham chiếu đến một ô hoặc phạm vi các ô liền kề, nếu không sẽ báo lỗi #VALUE
-
rows: Là số dòng bên trên hoặc bên dưới của reference, giá trị dương là di chuyển xuống dưới và giá trị âm là di chuyển lên trên
-
cols: Là cột bên trái hoặc bên phải của reference, giá trị dương là di chuyển xuống sang phải và giá trị âm là di chuyển sang trái
-
height (tùy chọn): Chiều cao, tính bằng số hàng bạn muốn tham chiếu trả về. (Chiều cao phải là số dương)
-
width (tùy chọn): Độ rộng, tính bằng số cột bạn muốn tham chiếu trả về. (Chiều cao phải là số dương)
Cú pháp hàm OFFSET
Ví dụ về hàm OFFSET
Bước 1: Trong bảng tính Excel, muốn tính doanh số hủ tiếu gói trong tháng 2, bạn nhập hàm =OFFSET(A1;2;2) vào ô bạn muốn tham chiếu
Ví dụ về hàm OFFSET
Bước 2: Nhấn phím Enter để hiện kết quả
Kết quả hàm Offset
Những lưu ý cần chú ý khi sử dụng
-
Lỗi #REF: Xảy ra khi đối số rows và cols làm cho vùng tham chiếu trả về vượt quá phạm vị quy định của trang tính
-
Lỗi #VALUE: Nếu đối số height và width > 1 thì phải dùng công thức mảng hàm OFFSET. Và 2 đối số này đều phải là số không âm.
-
Nếu không nhập đối số height và width thì nó được mặc định có chiều cao và độ rộng giống với vùng tham chiếu
Ứng dụng của OFFSET trong Excel
-
Tính toán nhanh chóng: Tính toán dữ liệu của một ô hoặc phạm vi ô liền kề căn cứ vào vùng tham chiếu trước đó.
-
Xây dựng bảng tổng hợp: OFFSET cho phép tổng hợp dữ liệu từ những phạm vi khác nhau mà không cần thay đổi công thức
-
Tạo biểu đồ tự động: Kết hợp với hàm khác tạo biểu đồ tự động cập nhật khi dữ liệu thay đổi hoặc thêm mới.
-
Kết hợp với hàm khác: Tối ưu hóa công việc và nâng cao hiệu suất.
Hàm OFFSET trong Excel có điều kiện
Hàm OFFSET kết hợp với hàm SUM
Bước 1: Muốn tính tổng doanh số của mì gói trong 3 tháng, bạn nhập hàm =SUM(OFFSET(B1;1;0;1;3)) vào ô bạn muốn hiển thị kết quả
Hàm OFFSET kết hợp với hàm SUM
Bước 2: Nhấn phím Enter để hiện kết quả
Kết quả hàm offset kết hợp hàm SUM
Hàm OFFSET kết hợp với hàm AVERAGE
Bước 1: Muốn tính doanh số trung bình của Cháo trong 3 tháng, bạn nhập hàm =AVERAGE(OFFSET(B1;3;0;1;3)) vào ô bạn muốn hiển thị kết quả
Hàm OFFSET kết hợp với hàm AVERAGE
Bước 2: Nhấn phím Enter để hiện kết quả
Kết quả
Hàm OFFSET kết hợp với hàm MAX
Bước 1: Muốn tính doanh số lớn nhất của Phở trong 3 tháng, bạn nhập hàm =MAX(OFFSET(B1;4;0;1;3)) vào ô bạn muốn hiển thị kết quả
Hàm OFFSET kết hợp với hàm MAX
Bước 2: Nhấn phím Enter để hiện kết quả
Kết quả
Hàm OFFSET kết hợp với hàm COUNTIF
Bước 1: Muốn biết số tháng có doanh số lớn hơn 200 của Phở, bạn nhập hàm =COUNTIF(OFFSET(B1;4;0;1;3); “>200”) vào ô bạn muốn hiển thị kết quả
Hàm OFFSET kết hợp với hàm COUNTIF
Bước 2: Nhấn phím Enter để hiện kết quả
kết quả
Kết luận
Cách ứng dụng hàm OFFSET trong Excel rất đơn giản và dễ thực hiện hơn nhiều khi mà bạn thực hành và ứng dụng hàm OFFSET cũng như kết hợp với các hàm khác nhiều lần. Hãy rèn luyện chăm chỉ và học thêm nhiều hàm thông dụng khác nữa nhé. Nếu bạn muốn cải thiện và nâng cao thêm kỹ năng tin học văn phòng, hãy tham khảo những cuốn sách về Excel, Word, Powerpoint của Đào tạo tin học nhé.
