Hàm offset là gì? Hướng dẫn cụ thể cách để sử dụng hàm trong excel

Hàm offset là hàm được dùng đặc biệt phổ biến trong Excel hiện nay, giúp mang lại hiệu quả công việc cao đối với người dùng. Tuy nhiên, nếu bạn chưa hiểu rõ và chưa biết cách để dùng hàm offset trong excel thì tuyệt đối không nên bỏ qua những thông tin hữu ích sau đây mà diemmayhot.com mang tới!

Contents

1 Hàm offset là gì?
2 Cú pháp của hàm offset
3 Lưu ý quan trọng khi sử dụng hàm OFFSET
4 Ví dụ cụ thể
5 Hướng dẫn sử dụng hàm offset với các hàm khác

Hàm offset là gì?

Vậy Hàm offset là gì? Hàm offset excel trả về kết quả là một ô hoặc phạm vi ô gồm số lượng hàng, cột được chỉ định bởi một ô hoặc một phạm vi ô. Trong Excel, hàm OFFSET dùng với mục đích để nhằm:

Tiết kiệm thời gian cho việc thực hiện tính toán dữ liệu trong một ô.
Có thể kết hợp chung hàm này với hàm khác để nhằm nâng cao hiệu suất làm việc cho người dùng.

Cú pháp của hàm offset

OFFSET(reference, rows, cols, [height], [width])

Trong đó:

Reference: là vùng tham chiếu mà bạn muốn tham chiếu chúng. Vùng tham chiếu cần phải tham chiếu tới một ô hay một phạm vi các ô liền kề. Đây được xem là điểm làm gốc của công thức. Nếu không kết quả sẽ trả về giá trị lỗi #VALUE!
Rows: Đây là số hàng lên hoặc xuống từ điểm làm gốc. Nếu điền dương công thức sẽ di chuyển xuống phía dưới tham chiếu và ngược lại.
Cols: là số cột mà bạn muốn công thức di chuyển từ điểm làm gốc. Cột có thể là số dương để công thức di chuyển sang bên phải và là số ẩm để công thức di chuyển sang bên trái tham chiếu.
Height: Là chiều cao được tính bằng số hàng mà bạn muốn tham chiếu trả về. Chiều cao phải là số dương.
Width: Là độ rộng, tính bằng số cột mà bạn muốn tham chiếu trả về. Độ rộng phải là số dương.

Lưu ý quan trọng khi sử dụng hàm OFFSET

Khi sử dụng hàm OFFSET, người dùng nên đặc biệt chú ý đến những điều cơ bản sau đây:

Công thức hàm OFFSET trả về kết quả là một dải ô, với những đối số rows và cols luôn đề cập đến ô phía trên bên trái.
Hàm OFFSET không di chuyển bất kỳ ô nào hay làm thay đổi vùng chọn. Chúng sẽ chỉ trả về một tham chiếu. Bạn hoàn toàn có thể dùng hàm OFFSET với bất kỳ hàm nào cần đến đối số tham chiếu.
Đối số reference phải là một ô hoặc một dải ô được liền kề nhau, nếu không Excel sẽ trả về lỗi #VALUE!

Nếu đối số rows và cols xác định một ô tham chiếu ở rìa trang tính Excel, công thức hàm tham chiếu trong Excel sẽ xuất hiện lỗi #REF!
Nếu đối số chiều cao và độ rộng bỏ qua, thì sẽ được giả định có cùng chiều cao, cũng như độ rộng với vùng tham chiếu.
Hàm OFFSET có thể kết hợp lồng trong bất kỳ hàm Excel nào chấp nhận một tham chiếu ô hay dải trong những đối số của nó.

Ví dụ cụ thể

Ví dụ 1: Offset đang đứng ở ô C9, bạn muốn lấy giá trị của ô E10 bằng cách đi xuống dưới 1 dòng, đi sang phải 2 dòng, sau đó ta sẽ thu được kết quả như hình sau:

Ví dụ 2: Ví dụ cụ thể đối với trường hợp xảy ra lỗi: Bạn lấy giá trị vượt quá phạm vi đường biên của trang tính. Quan sát ta thấy, Offset đang đứng ở Ô C9, bạn gõ lên trên 3 ô và sang trái 4 ô. Kết quả sẽ trả về lỗi như sau:

Ví dụ 3: Kết hợp hàm Offset với những hàm khác:

Offset đang ở vị trí ô C9, bạn muốn tính tổng lương của nhân viên cuối cùng là Phùng Hữu Thiện thì thực hiện với công thức sau: =SUM(OFFSET(C9,2,0,1,3)) và kết quả trả về sẽ như sau:

Hướng dẫn sử dụng hàm offset với các hàm khác

Kết hợp giữa hàm OFFSET lồng trong hàm SUM

Hàm SUM kết hợp hàm OFFSET sẽ giúp tính tổng các ô tham chiếu trong OFFSET Excel ngay cả khi bảng tính được thay đổi, cập nhật dữ liệu liên tục. Thay vì thay thế hàm SUM thủ công, mọi việc của bạn trở nên đơn giản hơn rất nhiều.

Công thức tính hàm SUM kết hợp hàm OFFSET:

=SUM(first cell:OFFSET(cell with total, -1,0))

Lồng OFFSET với hàm AVERAGE, MAX, MIN

Với hàm này bạn có thể tìm được các giá trị lớn nhất hoặc nhỏ nhất trong bảng. Ta có công thức như sau:

Hàm OFFSET lồng hàm AVERAGE để tính giá trị trung bình: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
Hàm OFFSET lồng hàm MAX để tìm giá trị lớn nhất trong bảng: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
Hàm OFFSET lồng hàm MAX để tìm giá trị nhỏ nhất trong bảng: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

Tạo dải động với hàm OFFSET kết hợp hàm COUNTA

Hàm OFFSET kết hợp cùng với hàm COUNTA giúp tạo dải động hữu ích trong những trường hợp bạn muốn tạo danh sách tự động cập nhật. Bạn cần đặt tên cho dải, tiến hành các thao tác theo một danh sách các danh mục nhằm tạo sự thuận tiện cho việc thực hiện các thao tác.

XEM THÊM: Hướng dẫn sử dụng hàm sumifs chi tiết nhất!

Hy vọng với những chia sẻ trên đây của chúng tôi về hàm offset đã mang tới những thông tin hữu ích nhất dành cho bạn trong việc ứng dụng hàm offset trong công việc của mình!