Hàm OFFSET trong excel – Cấu trúc hàm, ứng dụng

Việc tìm kiếm thông tin trong một bảng dữ liệu dài và phức tạp trên Excel theo phương pháp thủ công sẽ tốn thời gian. Điều đó khiến cho công việc không được tối ưu thời gian và đạt kết quả mong muốn. Với những người thường xuyên tiếp xúc với kiểu dữ liệu tham chiếu thì sẽ quen thuộc với hàm OFFSET trong Excel. Hàm này giúp tối ưu thời gian tham chiếu dữ liệu một cách nhanh chóng và chính xác. Nếu bạn chưa hiểu rõ về hàm này thì không nên bỏ qua bài viết dưới đây.

Bài viết này, hamexcel.net sẽ chia sẻ đến bạn thông tin cơ bản về hàm. Cũng như giải nghĩa cấu trúc và các ví dụ áp dụng để giúp bạn hiểu rõ hơn. Mời bạn đọc cùng theo dõi và tham khảo.

Hàm OFFSET trong Excel là hàm gì?

Hàm OFFSET trong Excel là một hàm giúp người dùng thực hiện tham chiếu đến một vùng dữ liệu cụ thể trong bảng tính. Hàm này cho phép bạn tạo ra một tham chiếu đến một ô hoặc một vùng ô. Dựa trên vị trí của một ô gốc và các tham số về số hàng và số cột.

Cấu trúc của hàm OFFSET trong Excel như sau:

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

Trong đó:

• Tham số reference: đây là tham số bắt buộc. Nó là một ô hoặc vùng ô liền kề mà bạn muốn bắt đầu tham chiếu.
• Tham số rows: số hàng bạn muốn dịch chuyển lên, xuống từ ô tham chiếu. Nếu số dương, bạn dịch chuyển xuống. Nếu số âm, bạn dịch chuyển lên.
• Tham số cols: Số cột bạn muốn dịch chuyển sang trái,sang phải từ ô tham chiếu. Nếu số dương, bạn dịch chuyển sang phải. Nếu số âm, bạn dịch chuyển sang trái.
• Tham số height (tùy chọn): Chiều cao của vùng ô mà bạn muốn tham chiếu, tính bằng số hàng.
• Tham số width (tùy chọn): Chiều rộng của vùng ô mà bạn muốn tham chiếu, tính bằng số cột.

Ví dụ ứng dụng

Ví dụ 1: xác định doanh thu bán hàng

Giả sử bạn có bảng dữ liệu sau:

Yêu cầu: tìm doanh thu của tháng 3 và tháng 4. Ta sử dụng hàm OFFSET như sau:

• Công thức: = OFFSET(A2,3,1). Kết quả doanh thu tháng 3 là 1100
• Với doanh thu tháng 4 ta sử dụng công thức: = OFFSET(A2,4,1)

Ta hiểu rằng, ở công thức trên, tham số A2 là ô bắt đầu, ô chứa tháng 1. Tham số “3,4” là số hàng dịch xuống dưới của tháng 3 và 4 tính từ vị trí tháng 1. Tham số “1” là số hàng dịch sang bên phải 1 hàng.

Ví dụ 2: hàm OFFSET kết hợp hàm SUM

Giả sử bạn có bảng dữ liệu doanh thu theo tháng dưới đây:

Yêu cầu: tính doanh thu của sản phẩm A trong quý 1( tháng 1,2,3). Ta sẽ dùng hàm SUM kết hợp hàm OFFSET để tối ưu thời gian. Đầu tiên ta sẽ chọn 1 ô để tính tổng doanh thu sản phẩm A trong quý 1. Lấy B2 là ô tham chiếu.

• Ta có công thức:  =SUM(OFFSET(B2, 1, 0, 3, 1))

Ta hiểu rằng:

• Tham số B2 sẽ là ô bắt đầu (ô doanh thu của sản phẩm A trong tháng 1).
• Tham số “1” hiểu là di chuyển xuống 1 hàng (đến tháng 2).
• Tham số “0”: Không di chuyển cột (vẫn ở cột Sản phẩm A).
•  Tham số “3”: Lấy 3 hàng (tháng 1, 2, và 3).
•  Tham số “1”: Lấy 1 cột (doanh thu của Sản phẩm A).

Kết quả sẽ là 3300 (1000 + 1100 + 1200)

Những lưu ý khi sử dụng hàm OFFSET trong Excel

• Nếu xảy ra lỗi #REF, hãy kiểm tra lại các các đối số rows và cols. Đảm bảo 2 đối số này không làm vùng tham chiếu vượt quá giới hạn của trang tính.
• Nếu bạn không nhập các đối số height và width, kết quả sẽ mặc định có kích thước giống như vùng tham chiếu ban đầu.
• Khi tham số height và width lớn hơn 1. hãy sử dụng công thức mảng trong OFFSET để tránh lỗi #VALUE!.
• Đảm bảo rằng cả hai đối số height và width đều là số không âm. Giá trị âm sẽ dẫn đến lỗi.
• Để nhận được kết quả trích xuất dữ liệu đúng, bạn cần ảm bảo rằng ô tham chiếu ban đầu (reference) được chọn đúng. Vì nó sẽ ảnh hưởng đến toàn bộ vùng dữ liệu mà bạn đang muốn trích xuất.

Tổng kết

Hàm OFFSET trong Excel là một trong những hàm hữu ích và quan trọng với những ai thường xuyên làm việc với bảng tính. Hàm này giúp cho người dùng trích xuất dữ liệu nhanh chóng và chính xác. Với bài viết mà hamexcel.net chia sẻ bên trên, hy vọng sẽ mang đến cho bạn cái nhìn tổng quan về hàm này. Từ đó có thể áp dụng vào công việc của mình để đạt hiệu quả tốt nhất.